fix(sync): change-feed trigger guard + remaining sync spec amendments (F8, PR-4) #1369

Merged
chris merged 1 commit from fix/sync-trigger-guard-amendments into main 2026-10-25 00:49:56 +02:00
Owner

PR-4 (last implementation lane) of the oracle sync-hardening plan — F8 + the remaining sync spec amendments. Base: origin/main a8d132d4. F1–F7 already merged (#1364–#1367). No Android changes, no changes to SyncIntentService/SyncPullApplier/history code.

A. F8 — change-feed trigger maintenance point (V180)

Ruling: WIDEN (oracle preference ratified). V180__sync_trigger_fire_on_every_update.sql re-creates trg_media_items_change_seq as BEFORE INSERT OR UPDATE (unqualified). V176 is untouched (applied in production). The failure mode: the V176 UPDATE OF <15 columns> list was a silent second schema-maintenance point — a future writer or migration updating only unlisted columns would skip allocation AND the feed; the committed change would never be delivered (exactly the ADR 0117 invisible-gap mode, entering through the schema instead of the writer).

Investigation that decided it:

  1. Writer inventory (exhaustive regex over apps/api/src/main/java, incl. CTE forms): exactly 5 mutating call sites in 4 classes —
    • UploadCommitService.commit — INSERT (fires regardless);
    • TrashService trash/restore/permanentlyDelete/emptyTrash — lifecycle cols + updated_at (all watched);
    • SyncIntentService executeTrash/executeRestore/executeReinstateFromTrash — lifecycle cols + updated_at (all watched);
    • WorkerResultService metadata acceptance — watched cols + metadata_status + updated_at in ONE statement (the only unwatched column any writer sets — and it never fires alone);
    • MediaMaintenanceService.clearOwnerLibrary — hard DELETE + epoch bump (epoch is the signal).
      No JPA/Hibernate entity exists (spring-data-jdbc, raw JdbcClient everywhere; verified: no @Entity/@Table for media_items, no @Version). The only never-watched columns in the entire schema are metadata_status and preview_status — no writer touches ONLY those, so widening changes zero behavior on every existing path and purely removes the future trap.
  2. Noise/lock churn: payload fields (S2) are what clients consume — an update to unwatched columns emits an entry with unchanged watched fields, which clients absorb harmlessly (verified in the Android sources: ChangeBinder rule 1 re-binds bound rows to the fresh seq — a refresh, not a misbind; SyncPullApplier/first-sync pre-binder re-derive the same verdicts). The sync_seq counter-row lock is already taken by every watched write (every upload commit, every metadata result, every trash/restore/purge); ADR 0117 explicitly accepts row-lock serialization at household scale. Correctness (never silently skip) outranks minor noise.
  3. Pass-through guard correctness under widening: a plain UPDATE carries NEW.change_seq = OLD.change_seq → NOT DISTINCT FROM OLD → falls through to allocation (fresh seq); an explicitly assigned seq passes through verbatim (the V176 backfill contract). No code path assigns change_seq manually — re-verified: the only assignment in the tree is the V176 backfill, which ran before any trigger existed.

Tests (SyncChangeFeedIntegrationTest 11 → 14, real Flyway chain):

  • updateOfOnlyUnwatchedColumnStillAllocatesAndEmits — metadata_status-only UPDATE allocates + emits after V180 (the V176-shaped column list silently skipped exactly this); payload fields unchanged.
  • changeFeedTriggerFiresForEveryInsertAndUpdateWithoutColumnList — schema-level guard: pg_get_triggerdef must be column-unrestricted; a future re-narrowing fails loudly instead of silently skipping.
  • carriedOverSeqAllocatesButExplicitAssignmentPassesThrough — pins the guard's two branches under the widened event.

B. Spec amendments (spec-query + official exporter; byte-identical check vs clean-main export apart from 065/044 — same pattern as #1367)

Item Verdict Where
1. S2 first-sync pre-bind (PR #1366 text) APPLIED (was missing) 065 S2, appended to the "First sync" bullet verbatim
2. INV-2 first-sync/resync extension (PR #1366 text) APPLIED (was missing) 065 S6, INV-2 tail
3. 044 S3 reconcile-order pre-bind step APPLIED (was missing) 044 S3, new bullet between delta pull and local diff
4. Oracle (b): reinstate-on-purged = re-authorization WAS MISSING → APPLIED 065 S11, new AMENDED block (accepted reinstate leaves the row permanently_deleted; entry stays listed with explicit re-upload copy, no revert button; ISSUE-190 noted)
5. Oracle (f): epoch-only cursor invalidation WAS MISSING (worse: the old text was WRONG) 065 S2: the previous bullet read "invalid or unknown cursor values … ProblemDetail"; the server (SyncChangeQueryService) actually 400s only negatives and answers unknown/large cursors with an empty 200 — spec corrected to match code: negative = 400; unknown/large = valid past-the-end, empty 200; epoch is the sole invalidation signal

Note: PR #1366's body/comments are empty on Forgejo (report not retrievable), so items 1–3 use the exact drafted texts as carried in the lane briefing (which quotes the PR-4 task). spec-md/specs/065/spec.md + spec-md/specs/044/spec.md regenerated with the official exporter and verified byte-identical to a fresh export; the 14 other drifted export files on clean main are pre-existing and left to CI (as in #1365/#1367).

Validation

  • :apps:api:test --rerun-tasks — 198 suites / 2315 tests, 0 failures, 0 errors (9 pre-existing skips). The first run hit the known ISSUE-169 flake (PersonRelationServicePostgresTest.theChosenRepresentativeWinsOverTheLatestOccurrence) — green on isolated rerun (26/26) and on the clean --rerun-tasks pass.
  • No Android changes; Android suites unaffected.

KNOWN_ISSUES

ISSUE-201 (high/mitigated/area:api, ref [fix/sync-trigger-guard-amendments]) — trigger column-list maintenance point, fixed (widened + guarded).

PR-4 (last implementation lane) of the oracle sync-hardening plan — **F8** + the remaining sync spec amendments. Base: origin/main a8d132d4. F1–F7 already merged (#1364–#1367). No Android changes, no changes to SyncIntentService/SyncPullApplier/history code. ## A. F8 — change-feed trigger maintenance point (V180) **Ruling: WIDEN (oracle preference ratified).** `V180__sync_trigger_fire_on_every_update.sql` re-creates `trg_media_items_change_seq` as `BEFORE INSERT OR UPDATE` (unqualified). V176 is untouched (applied in production). The failure mode: the V176 `UPDATE OF <15 columns>` list was a silent **second** schema-maintenance point — a future writer or migration updating only unlisted columns would skip allocation AND the feed; the committed change would never be delivered (exactly the ADR 0117 invisible-gap mode, entering through the schema instead of the writer). **Investigation that decided it:** 1. **Writer inventory** (exhaustive regex over `apps/api/src/main/java`, incl. CTE forms): exactly 5 mutating call sites in 4 classes — - `UploadCommitService.commit` — INSERT (fires regardless); - `TrashService` trash/restore/permanentlyDelete/emptyTrash — lifecycle cols + `updated_at` (all watched); - `SyncIntentService` executeTrash/executeRestore/executeReinstateFromTrash — lifecycle cols + `updated_at` (all watched); - `WorkerResultService` metadata acceptance — watched cols + **`metadata_status`** + `updated_at` in ONE statement (the only unwatched column any writer sets — and it never fires alone); - `MediaMaintenanceService.clearOwnerLibrary` — hard DELETE + epoch bump (epoch is the signal). **No JPA/Hibernate entity exists** (spring-data-jdbc, raw JdbcClient everywhere; verified: no `@Entity`/`@Table` for media_items, no `@Version`). The only never-watched columns in the entire schema are `metadata_status` and `preview_status` — **no writer touches ONLY those**, so widening changes **zero** behavior on every existing path and purely removes the future trap. 2. **Noise/lock churn**: payload fields (S2) are what clients consume — an update to unwatched columns emits an entry with unchanged watched fields, which clients absorb harmlessly (verified in the Android sources: `ChangeBinder` rule 1 re-binds bound rows to the fresh seq — a refresh, not a misbind; `SyncPullApplier`/first-sync pre-binder re-derive the same verdicts). The `sync_seq` counter-row lock is **already taken by every watched write** (every upload commit, every metadata result, every trash/restore/purge); ADR 0117 explicitly accepts row-lock serialization at household scale. Correctness (never silently skip) outranks minor noise. 3. **Pass-through guard correctness under widening**: a plain UPDATE carries `NEW.change_seq = OLD.change_seq` → NOT DISTINCT FROM OLD → falls through to allocation (fresh seq); an explicitly assigned seq passes through verbatim (the V176 backfill contract). **No code path assigns `change_seq` manually** — re-verified: the only assignment in the tree is the V176 backfill, which ran before any trigger existed. **Tests** (`SyncChangeFeedIntegrationTest` 11 → 14, real Flyway chain): - `updateOfOnlyUnwatchedColumnStillAllocatesAndEmits` — `metadata_status`-only UPDATE allocates + emits after V180 (the V176-shaped column list silently skipped exactly this); payload fields unchanged. - `changeFeedTriggerFiresForEveryInsertAndUpdateWithoutColumnList` — schema-level guard: `pg_get_triggerdef` must be column-unrestricted; a future re-narrowing fails loudly instead of silently skipping. - `carriedOverSeqAllocatesButExplicitAssignmentPassesThrough` — pins the guard's two branches under the widened event. ## B. Spec amendments (spec-query + official exporter; byte-identical check vs clean-main export apart from 065/044 — same pattern as #1367) | Item | Verdict | Where | |---|---|---| | 1. S2 first-sync pre-bind (PR #1366 text) | **APPLIED** (was missing) | 065 S2, appended to the "First sync" bullet verbatim | | 2. INV-2 first-sync/resync extension (PR #1366 text) | **APPLIED** (was missing) | 065 S6, INV-2 tail | | 3. 044 S3 reconcile-order pre-bind step | **APPLIED** (was missing) | 044 S3, new bullet between delta pull and local diff | | 4. Oracle (b): reinstate-on-purged = re-authorization | **WAS MISSING → APPLIED** | 065 S11, new AMENDED block (accepted reinstate leaves the row `permanently_deleted`; entry stays listed with explicit re-upload copy, no revert button; ISSUE-190 noted) | | 5. Oracle (f): epoch-only cursor invalidation | **WAS MISSING (worse: the old text was WRONG)** | 065 S2: the previous bullet read "invalid **or unknown** cursor values … ProblemDetail"; the server (`SyncChangeQueryService`) actually 400s only negatives and answers unknown/large cursors with an empty 200 — spec corrected to match code: negative = 400; unknown/large = valid past-the-end, empty 200; epoch is the sole invalidation signal | Note: PR #1366's body/comments are empty on Forgejo (report not retrievable), so items 1–3 use the exact drafted texts as carried in the lane briefing (which quotes the PR-4 task). `spec-md/specs/065/spec.md` + `spec-md/specs/044/spec.md` regenerated with the official exporter and verified byte-identical to a fresh export; the 14 other drifted export files on clean main are pre-existing and left to CI (as in #1365/#1367). ## Validation - `:apps:api:test --rerun-tasks` — **198 suites / 2315 tests, 0 failures, 0 errors** (9 pre-existing skips). The first run hit the known **ISSUE-169** flake (`PersonRelationServicePostgresTest.theChosenRepresentativeWinsOverTheLatestOccurrence`) — green on isolated rerun (26/26) and on the clean `--rerun-tasks` pass. - No Android changes; Android suites unaffected. ## KNOWN_ISSUES **ISSUE-201** (`high`/`mitigated`/`area:api`, ref `[fix/sync-trigger-guard-amendments]`) — trigger column-list maintenance point, fixed (widened + guarded).
fix(sync): change-feed trigger guard + remaining sync spec amendments (F8, PR-4)
All checks were successful
Wiki Sync / Sync wiki from docs (push) Has been skipped
Build and Deploy / Detect changed services and test scopes (push) Successful in 3s
Build and Deploy / Smoke API and media worker (push) Has been skipped
Build and Deploy / Smoke web (push) Has been skipped
Build and Deploy / Smoke ML worker (push) Has been skipped
Build and Deploy / Smoke people worker (push) Has been skipped
Build and Deploy / Build postgres image (push) Has been skipped
Build and Deploy / Build Android CI image (push) Has been skipped
Build and Deploy / Build plants worker image (push) Has been skipped
Build and Deploy / Build converter worker image (push) Has been skipped
Build and Deploy / Android unit tests and debug APK (push) Has been skipped
Build and Deploy / Deploy postgres (push) Has been skipped
Build and Deploy / Build API image (push) Has been skipped
Build and Deploy / Build media worker image (push) Has been skipped
Build and Deploy / Build ML worker image (push) Has been skipped
Build and Deploy / Build people worker image (push) Has been skipped
Build and Deploy / Build web image (push) Has been skipped
Build and Deploy / Deploy API (push) Has been skipped
Build and Deploy / Deploy web (push) Has been skipped
Build and Deploy / Deploy media worker (push) Has been skipped
Build and Deploy / Deploy ML worker (push) Has been skipped
Build and Deploy / Deploy plants worker (push) Has been skipped
Build and Deploy / Deploy people worker (push) Has been skipped
Build and Deploy / Deploy converter worker (push) Has been skipped
Build and Deploy / Deploy review instance (push) Has been skipped
Build and Deploy / Clean Docker build artifacts (push) Has been skipped
Build and Deploy / Run scoped PR tests (push) Successful in 2m36s
606fc862ed
F8 (oracle): V176 created trg_media_items_change_seq as BEFORE INSERT OR
UPDATE OF <15 watched columns> — that column list was a silent second
schema-maintenance point: a future writer or migration updating ONLY
unlisted media_items columns (the schema already carries metadata_status
and preview_status) would skip allocation AND the feed silently, and the
committed change would never reach any client — the invisible-gap failure
mode ADR 0117's trigger design exists to prevent, entering through the
schema instead of the writer.

Ruling (oracle preference ratified: widen): V180 re-creates the trigger
unqualified (BEFORE INSERT OR UPDATE). V176 itself is untouched (applied
in production). Writer inventory (exhaustive over apps/api main sources):
UploadCommitService INSERT, TrashService x4, SyncIntentService x3,
WorkerResultService metadata UPDATE (watched cols + metadata_status +
updated_at in ONE statement), MediaMaintenanceService DELETE (epoch
signal) — no JPA/Hibernate entity exists; NO writer touches only
unwatched columns today, so widening changes zero behavior on existing
paths and only removes the future trap. Lock churn is a non-issue at
ADR 0117 household scale: the sync_seq counter-row lock is already taken
by every watched write (every upload commit, every metadata result); the
only extra firings a future bookkeeping writer would cause emit entries
with UNCHANGED payload fields, which clients absorb harmlessly (the
Android applier's direct rule re-binds to the fresh seq; ChangeBinder
verified). The allocator's pass-through guard is correct when fired on
every update: a plain UPDATE carries NEW.change_seq = OLD.change_seq
(NOT DISTINCT FROM OLD -> allocates), an explicit assignment passes
through verbatim; no code path assigns change_seq manually (only the
pre-trigger V176 backfill) — now pinned by test.

Tests (SyncChangeFeedIntegrationTest, real migration chain): unwatched-
column update (metadata_status only) allocates + emits after V180 — the
pre-V180 shape silently skipped it; schema-level guard pins the trigger
definition column-unrestricted via pg_get_triggerdef (a future
re-narrowing fails loudly); carried-over seq allocates vs explicit
assignment passes through. 11 -> 14 tests in the suite.

Spec amendments (via spec-query + official exporter, byte-identical
check vs clean-main export apart from 065/044):
- 065 S2: first-sync pre-bind MAY on completed full listings only
  (contentHash + byteSize, elected per hash; incremental deltas MUST NOT
  pre-bind) — PR #1366's drafted text; cursor-validation correction
  (oracle (f)): ONLY negative cursors are a 400, unknown/large cursors
  are a valid past-the-end position answered with an empty 200 (epoch
  is the sole invalidation signal).
- 065 INV-2: blocks re-upload incl. first sync and epoch/cursor resync
  (PR #1366's drafted text).
- 065 S11 (oracle (b)): accepted reinstate-on-purged = re-authorization,
  not resurrection; entry stays listed with explicit re-upload copy.
- 044 S3: pre-bind step inserted between delta pull and local diff
  (full listings only).

KNOWN_ISSUES: ISSUE-201.

Validation: :apps:api:test --rerun-tasks — 198 suites / 2315 tests,
0 failures, 0 errors (9 pre-existing skips; the first run hit the known
ISSUE-169 flake in PersonRelationServicePostgresTest
.theChosenRepresentativeWinsOverTheLatestOccurrence, green on isolated
rerun and on the clean --rerun-tasks pass).
chris merged commit edd3094e45 into main 2026-10-25 00:49:56 +02:00
Sign in to join this conversation.
No reviewers
No labels
No milestone
No project
No assignees
1 participant
Notifications
Due date
The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set

Reference
chris/memories!1369
No description provided.