bvandeusenandClaude Opus 4.8
58b88d2622
The delta-sync substrate for the local-first native clients (Tauri, Android). No API behavior change — pure infrastructure; triggers are operator-verified on deploy (no Postgres CI lane). Migration 0015: - CREATE SEQUENCE sync_revision_seq. - notes + labels gain sync_revision (bigint) + purged_at (tombstone), with existing rows backfilled to distinct increasing revisions. - Trigger ts_set_sync_revision() BEFORE INSERT OR UPDATE on notes+labels stamps a fresh monotonic revision from the sequence, so no mutation site can forget to bump it (robustness over app-level bumps). - Trigger ts_bump_parent_note_revision() AFTER INS/UPD/DEL on note_items, note_attachments, note_labels re-bumps the parent note, since a note syncs as a whole (items/labels/attachments travel inline). - Indexes (owner_id, sync_revision) on notes + labels for the delta pull WHERE sync_revision > cursor. purged_at is the hard-delete tombstone (distinct from deleted_at = trash) so an offline client learns a row is gone instead of resurrecting it. Model columns added to Note + Label (nullable; trigger populates them). Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_01FRgehjoz7Yv8LkUfADxACm
93 lines
3.7 KiB
Python
93 lines
3.7 KiB
Python
"""sync revision + tombstone infrastructure (M8 sync hub, step 1)
|
|
|
|
Revision ID: 0015
|
|
Revises: 0014
|
|
Create Date: 2026-07-23
|
|
|
|
The delta-sync substrate for the local-first native clients. A shared sequence
|
|
(sync_revision_seq) feeds a per-row monotonic `sync_revision` on notes + labels,
|
|
assigned by a DB TRIGGER on every insert/update so no mutation site can ever
|
|
forget to bump it. Child-row changes (items / attachments / label membership) bump
|
|
their parent note's revision, since a note syncs as a whole. `purged_at` is the
|
|
hard-delete tombstone (distinct from `deleted_at` = recoverable trash) so an
|
|
offline client learns a row is gone instead of resurrecting it.
|
|
|
|
No API behavior changes here — this is pure infrastructure. Triggers/behavior are
|
|
operator-verified on deploy (no Postgres CI lane).
|
|
"""
|
|
from alembic import op
|
|
import sqlalchemy as sa
|
|
|
|
revision = "0015"
|
|
down_revision = "0014"
|
|
branch_labels = None
|
|
depends_on = None
|
|
|
|
_REVISIONED = ("notes", "labels")
|
|
_CHILD_TABLES = ("note_items", "note_attachments", "note_labels")
|
|
|
|
|
|
def upgrade() -> None:
|
|
op.execute("CREATE SEQUENCE IF NOT EXISTS sync_revision_seq")
|
|
|
|
for table in _REVISIONED:
|
|
op.add_column(table, sa.Column("sync_revision", sa.BigInteger(), nullable=True))
|
|
op.add_column(table, sa.Column("purged_at", sa.DateTime(timezone=True), nullable=True))
|
|
# Backfill: give every existing row a distinct, increasing revision so it's
|
|
# pullable from a since=0 initial sync.
|
|
op.execute(f"UPDATE {table} SET sync_revision = nextval('sync_revision_seq')")
|
|
|
|
op.create_index("ix_notes_owner_sync_revision", "notes", ["owner_id", "sync_revision"])
|
|
op.create_index("ix_labels_owner_sync_revision", "labels", ["owner_id", "sync_revision"])
|
|
|
|
# A row stamps itself with a fresh revision on every insert/update.
|
|
op.execute(
|
|
"""
|
|
CREATE OR REPLACE FUNCTION ts_set_sync_revision() RETURNS trigger AS $$
|
|
BEGIN
|
|
NEW.sync_revision := nextval('sync_revision_seq');
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql
|
|
"""
|
|
)
|
|
# A child-row change re-bumps its parent note (the parent's own BEFORE trigger then
|
|
# assigns the final, higher revision — a harmless double-bump; gaps are fine).
|
|
op.execute(
|
|
"""
|
|
CREATE OR REPLACE FUNCTION ts_bump_parent_note_revision() RETURNS trigger AS $$
|
|
BEGIN
|
|
UPDATE notes SET sync_revision = nextval('sync_revision_seq')
|
|
WHERE id = COALESCE(NEW.note_id, OLD.note_id);
|
|
RETURN NULL;
|
|
END;
|
|
$$ LANGUAGE plpgsql
|
|
"""
|
|
)
|
|
|
|
for table in _REVISIONED:
|
|
op.execute(
|
|
f"CREATE TRIGGER trg_{table}_sync_revision BEFORE INSERT OR UPDATE ON {table} "
|
|
"FOR EACH ROW EXECUTE PROCEDURE ts_set_sync_revision()"
|
|
)
|
|
for child in _CHILD_TABLES:
|
|
op.execute(
|
|
f"CREATE TRIGGER trg_{child}_bump_note AFTER INSERT OR UPDATE OR DELETE ON {child} "
|
|
"FOR EACH ROW EXECUTE PROCEDURE ts_bump_parent_note_revision()"
|
|
)
|
|
|
|
|
|
def downgrade() -> None:
|
|
for child in _CHILD_TABLES:
|
|
op.execute(f"DROP TRIGGER IF EXISTS trg_{child}_bump_note ON {child}")
|
|
for table in _REVISIONED:
|
|
op.execute(f"DROP TRIGGER IF EXISTS trg_{table}_sync_revision ON {table}")
|
|
op.execute("DROP FUNCTION IF EXISTS ts_bump_parent_note_revision()")
|
|
op.execute("DROP FUNCTION IF EXISTS ts_set_sync_revision()")
|
|
op.drop_index("ix_labels_owner_sync_revision", table_name="labels")
|
|
op.drop_index("ix_notes_owner_sync_revision", table_name="notes")
|
|
for table in _REVISIONED:
|
|
op.drop_column(table, "purged_at")
|
|
op.drop_column(table, "sync_revision")
|
|
op.execute("DROP SEQUENCE IF EXISTS sync_revision_seq")
|