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
