Shadow-write infrastructure — branch review

1. Why this branch exists

Today the Partners DB and Reference DB are the source of truth. We want them replaced by the unified RDB you've been building behind /api/v1/scd/* and /api/v1/crud/*. Schema parity has been audited and the one-shot migration script works. But schema parity is not enough. We need to know that under real, live, sustained traffic the new database ends up with the same data as the old one — including fan-out (one source row becoming several versioned target rows), junction-table mutations, and the trickier edge cases like TRUNCATE.

So the plan is to run the two databases in parallel for ~2 weeks: writers keep hitting the old Partners DB, and a streaming pipeline mirrors every change into the unified RDB through your service. An out-of-band verifier compares the two databases continuously. Once it has been clean for a long enough window, an operator runs a single script that pauses writers, drains the pipeline, runs one last comparison, and stamps a 30-minute "permit" if everything matches. The operator then repoints writers at the unified RDB. The whole shadow apparatus then gets physically removed from the codebase.

2. Cast of characters

Five new actors. They communicate through three RDB tables and two HTTP endpoints — nothing else.

3. One write, end-to-end

Concrete example: someone updates an entity name in the Partners DB.

That's the whole loop. Everything else in this branch is either: (a) extra robustness for when something goes wrong, (b) the choreography to start this loop from an empty database, or (c) the choreography to stop it cleanly.

4. The four questions the system has to keep answering

The runbook calls these SC1, SC2, SC3, SC4 ("success criterion"). In plain English:

Roughly: SC1 says we didn't miss anything, SC2 says what we have is right, SC3 says we've been right long enough to believe it, and SC4 says we can recover from blips without corrupting state.

5. Architecture diagram

Two replication slots on the same Partners DB feed two independent Python processes. They coordinate through three RDB tables and two HTTP endpoints — never through shared Python state.

The two slots use the same wal2json options (same table list, same options hash). If they drift, both readers halt loudly — comparing apples to oranges would make SC1 meaningless.

6. Quick glossary

LSN (Log Sequence Number)

A monotonically increasing position in Postgres's write-ahead log. Every change has one. We use LSNs as "point in time" markers everywhere — "drain to this LSN", "read RDB as-of this LSN".

Replication slot

A Postgres construct that tracks how far a downstream reader has consumed. While a slot exists, Postgres refuses to recycle WAL the slot hasn't acknowledged. Two slots means two independent readers.

wal2json

A Postgres output plugin that emits each WAL change as a JSON object. We pin it to format-version=2 with include-pk=true and a specific add-tables list.

Exported snapshot

When you create a replication slot with EXPORT_SNAPSHOT, Postgres also gives you a snapshot token. Other transactions can attach to that exact MVCC view via SET TRANSACTION SNAPSHOT '...'. This is how the backfill loads consistent data into the empty RDB.

Holder session

The libpq connection that created the slot and is keeping the exported snapshot alive. If this session dies or runs any SQL, the snapshot evaporates and the backfill is corrupt.

Channel cdc_shadow

The provenance tag stamped on every Transaction row written by the shadow pipeline. Lets us distinguish shadow-pipeline writes from any other writer.

shadow_pk_map

An RDB table mapping (source_table, source_pk, target_model) to the canonical primary key in the unified RDB. Every shadow write upserts here. It's how we look up "where did source row X end up?" later.

SCD2

"Slowly Changing Dimension Type 2." A versioning pattern where each update creates a new row with a new valid_from and closes out the previous row's valid_to. The unified RDB uses this for 12 core entities.

Race window

The small slice of time between the verifier capturing an LSN and the verifier finishing its read. If a write commits inside that window the diff might see it on one side but not the other — we reclassify those as "race window" rather than failure, then escalate if the same row keeps showing up.

SC1 / SC2 / SC3 / SC4

The four invariants. See § 4.

R5 classes

The six possible verdicts the verifier can give a single row diff: missing_in_rdb, extra_in_rdb, value_mismatch, duplicate_current_version, unmapped_source_row, representation_only_difference. First four are blocking; last two are allowlisted.

R-18

The constraint "one reader per replication slot at a time." Postgres allows only one consumer per slot anyway, but R-18 also means "don't accidentally run a second consumer Fly Machine and have them fight."

The cutover gate

The one-shot Python script that decides "permit" or "block" for the writer repointing. The decision is stamped into shadow_cutover_audit as the system's audit trail.

7. Diff scope — where the 82k lines went

Roughly half the diff is the Python consumer + its tests. The next biggest chunk is the migration runner's new "snapshot mode" that opens the validation window. The TypeScript shadow module is comparatively small — about as big as one route in your existing service.

8. New tables in the unified RDB

Five additive tables. Nothing in the existing schema changed shape.

9. TS shadow module — what got added to your service

Skip ahead if you're already familiar with the /shadow/* routes from the PR description.

9.1 Where it plugs in

src/app.ts got refactored into a buildApp(target, options) that the rest of the codebase calls. The shadow registration is one block:

// SHADOW-REGISTRATION-BEGIN
const { registerShadowModule } = await import('./modules/shadow/index.js');
await registerShadowModule(target, { ... });
// SHADOW-REGISTRATION-END

Two ways to disable it:

• Runtime: SHADOW_DISABLED=true skips the dynamic import. A counter-based defensive check throws if the flag was set but the registration code somehow ran anyway.
• Build-time: ci/strip-shadow-registration.sh is an awk script that finds the marker comments and physically removes the block. CI runs it in the shadow-cutover-smoke job to prove the post-cutover state compiles.

9.2 The new routes

9.3 What makes shadow writes safe to retry

Each shadow write is one Prisma $transaction that writes four things atomically:

1. The target row (or SCD2 version archival + new version).
2. A provenance Transaction row with channel='cdc_shadow' and a unique idempotency_key.
3. A shadow_pk_map upsert.
4. (Sometimes) a shadow_event_ledger row.

A retry with the same idempotency key collides with the partial unique index Transaction(channel_id, model_type, operation, idempotency_key) WHERE idempotency_key IS NOT NULL and the SCD engine's replay-detection short-circuit returns the prior result instead of writing again. SCD2 writes also take a per-UUID advisory lock so two concurrent updates to the same entity can't interleave.

9.4 Auth isolation

Shadow routes use a separate bearer token (RDS_CDC_SHADOW_TOKEN), checked with constant-time comparison. Missing env var returns 503 rather than 401 so an operator can tell "misconfigured" from "wrong token". The public API_KEY auth was modified to skip /shadow/*; tests/unit/auth-non-overlap.test.ts asserts the two layers don't collide.

10. The Python consumer service

Skip if you don't intend to review the Python side directly.

10.1 Why two Postgres drivers?

Hybrid by necessity: psycopg2-binary owns the replication-stream paths because it still has the high-level LogicalReplicationConnection API that psycopg3 lacks. psycopg[binary] >= 3.2 owns everything else (verifier reads, raw RDB fetches, ledger SQL). The split is at the file boundary, not interleaved within a single function.

10.2 The five entrypoints

10.3 The verifier (this is the load-bearing piece)

Three modes, each a different read strategy:

Six possible verdicts on each row diff (the "R5 classes"):

• missing_in_rdb, extra_in_rdb, value_mismatch, duplicate_current_version — blocking (count toward SC3 reset).
• unmapped_source_row, representation_only_difference — allowlisted, no action.

And the SC1 attestation: aggregate shadow_event_ledger over (last_attested_lsn, confirmed_flush_lsn] by (side, source_table, source_operation) and fail-close on any per-bucket delta.

11. Backfill orchestration — how the window opens

The backfill is what loads the empty unified RDB from a consistent snapshot of the Partners DB at the same instant the replication slots start collecting changes. The runner is the existing migrate.ts, extended with a --snapshot-mode flag.

11.1 The choreography

1. Create the primary slot with EXPORT_SNAPSHOT. The session that creates the slot is "the holder" — we keep this connection idle and alive for the whole backfill.
2. Create the verifier slot on a separate connection. Enforce that its baseline LSN is ≥ the primary's.
3. Attach N reader sessions (default 8) with BEGIN ISOLATION LEVEL REPEATABLE READ; SET TRANSACTION SNAPSHOT '...';
4. Run the migration phases. The whole thing wraps in one Prisma $transaction whose first statement is the same SET TRANSACTION SNAPSHOT, so Prisma-driven reads see the same consistent view as the raw pg.Client readers.
5. Run a post-backfill verifier against the snapshot.
6. Flip verifier_done = true in shadow_backfill_state. The holder's polling loop sees this and exits cleanly.

11.2 Why the holder can't run a heartbeat

11.3 The shared contract between TS and Python

The migration map (scripts/migrate/reference-to-unified/migration-map.ts) is the source of truth for every source table's metadata: PK columns, PK kind, replica-identity kind, source filter, FK translations. dump-shadow-contract.ts renders this to JSON, committed at shadow-write-consumer/contracts/shadow-contract.json. The Python consumer loads it at import and version-checks. CI's parity-check.yml re-runs the dumper and git diff --exit-codes — so a TS migration-map edit that changes something behavior-affecting is a one-line lint failure on the PR, not a runtime surprise in Python.

12. Infra & CI — what gets deployed and gated

12.1 The Python service is deployed to Fly

infra/fly/shadow-consumer.toml declares two always-on process groups (consumer, slot_reader). The hourly verifier is intentionally not a process group — it's a scheduled Fly Machine because fly deploy would strip the schedule = "hourly" property and turn it into a tight-loop worker if it were in [processes]. The deploy workflow runs fly scale count consumer=1 slot_reader=1 and then asserts via fly machines list --json jq that the counts are right (no Fly-native enforcement exists for non-service workers).

12.2 Observability is Terraform-managed

Two parallel Axiom surfaces, deliberately separated:

• Shadow validation: Terraform-managed dataset rwa-shadow-{stg,prd} with 17 monitors (lag, DLQ depth, retained-WAL thresholds, verifier divergence, SC1 failures, skipped runs, write failure rate, schema fingerprint mismatch, cutover blocked, permit expirations) + 2 dashboards + Slack + email notifiers. Lifecycle: torn down when the validation window is decommissioned.
• Public rwa-db request logs: a tsx script (scripts/ops/setup-axiom-monitors.ts) provisions 3 monitors against rwa-db-{stg,prd} (route p95 latency, 5xx rate, 401 spike). Lifecycle: outlives the validation window.

12.3 CI workflows

13. Canonicalization — how two languages stay byte-identical

The consumer (Python) builds JSON bodies for your service (TypeScript). Both sides must produce exactly the same wire bytes for the same logical value, or SC1 dies. Five rules pinned in a frozen contract:

14. The race window — why "blocking" sometimes means "wait one tick"

The verifier captures l_before = pg_current_wal_lsn() on the Partners DB, then opens a snapshot. Between those two steps another transaction can commit. If that transaction's row shows up in the snapshot but the consumer hasn't applied it yet, the verifier sees missing_in_rdb — even though everything is correct and a moment later the consumer will catch up.

The classifier handles this by issuing pk_map_lookup_by_target for each blocking finding. If max(last_event_lsn) > l_before, the finding gets reclassified to verifier_race_window and is allowed to clear on the next tick. But — if the same (source_table, source_pk, target_model) + signature recurs for 2+ consecutive runs, the persistence tracker escalates it back to the original blocking class. So a true bug eventually surfaces; a transient race doesn't.

Two classes never get race-window relief: duplicate_current_version (SCD2 must have exactly one current version per UUID) and unmapped_source_row (the table is out of scope by construction).

15. The five lifecycle phases

The full operator runbook lives in docs/shadow-write-runbook.md (1,607 lines, 23 sections + glossary). The shape:

16. The decommission path is real today

Three artifacts make the post-cutover world a tested state in CI, not a future cleanup ticket:

1. ci/strip-shadow-registration.sh — awk script. Finds the // SHADOW-REGISTRATION-BEGIN/END markers in src/app.ts, fails noisily if they aren't both present exactly once, and emits a stripped version.
2. ci/shadow-deleted-stub/modules/shadow/index.ts — no-op stub exporting registerShadowModule as async () => {} so the rest of the codebase still typechecks after the real src/modules/shadow/ tree is deleted.
3. tsconfig.shadow-deleted.json — typecheck config that swaps in the stub and excludes src/modules/shadow/**. Used by the shadow-deleted-smoke (9b) CI job.

And scripts/check-shadow-residue.sh is the residue lint: it grep-fails if any of as_of_lsn, _shadow_update, _shadow_delete, shadow_metadata, or bare idempotency_key leaks into src/modules/scd, src/modules/crud, or src/shared/schemas. A defensive boundary check.

17. Test surface

18. Suggested reading order (~1h)

If you only have an hour, hit these in order:

1. The runbook table of contents. docs/shadow-write-runbook.md lines 17–42. Skim §3 (backfill open) and §5 (cutover) for the happy-path lifecycle in operator language.
2. The schema. db/unified/schema.prisma lines 2527–2705. Read the five table definitions and their comments.
3. The shadow contract. shadow-write-consumer/contracts/shadow-contract.json — one entry per source table. This is the cross-language source of truth.
4. Trace one write end-to-end. Pick parity-fixtures/fixtures/scd2_entity_insert.json and walk it through: src/consumer/transform.py → src/consumer/rds_client.py → src/modules/shadow/shadow-write.routes.ts → src/modules/shadow/shadow-scd.engine.ts → the resulting shadow_pk_map + shadow_event_ledger rows.
5. The verifier's hourly tick. src/verifier/main.py — run_hourly_tick is short enough to read in one sitting. Then sc1_attestation.py and race_window.py.
6. The cutover gate. src/cutover/main.py: drain_complete, classify_outcome, build_audit_payload, format_permit_expires_at. Pure helpers, individually unit-tested.
7. The holder. scripts/migrate/reference-to-unified/snapshot-holder.ts lines 30–46 (the rationale comment for why there's no heartbeat).
8. The decommission story. ci/strip-shadow-registration.sh, tsconfig.shadow-deleted.json, and .github/workflows/shadow-smoke.yml together prove the post-cutover state compiles today.

19. Risks & carve-outs worth weighing

Things I'd push back on or ask follow-up questions about if I were doing this review cold:

• Holder-session opacity. The strongest correctness invariant in the system — "no SQL on the holder session, ever" — is enforced by code-review discipline plus a TCP-keepalive config. There's no Postgres-side guard. A future contributor adding a "harmless" SELECT 1 heartbeat would silently invalidate the snapshot. Worth a code comment review and possibly a lint rule.
• R-18 enforcement is operational. Fly's process-group caps don't apply to worker (non-service) machines. The check is post-deploy jq + the runbook's manual command. If an operator scales consumer to 2 (or a deploy regression duplicates the verifier machine), the system would happily double-consume from the slot.
• Two Axiom surfaces, two lifecycles. Terraform manages the shadow surface (window-scoped); a tsx script manages the public surface. After decommission, the Terraform stack is torn down; the script-managed monitors persist. The takedown ordering deserves an explicit step in §6 of the runbook.
• Empty-RDB precondition is a footgun. Pre-flight step 7 says RDB must be empty for in-scope models at slot creation. There is a cleanup.ts escape hatch, but a forgotten test row would surface as persistent extra_in_rdb on every verifier run until cleaned. Consider hardening the preflight to refuse-to-open on a non-empty in-scope target.
• Decimal canonicalization mid-PR fixes. Several commits in branch history are edge-case fixes that surfaced from parity fixtures (51456cb fix primary market decimal comparisons, 8304640 ignore numeric representation diffs, 3eacd96 fix shadow verifier PK canonicalization, 1be6d1d preserve null archived values). The contract version (1.0.0) has not been bumped. If we expect the cross-language contract to be stable, consider whether these warrant a version bump or a CHANGELOG.
• Backfill suppresses the ledger. applyBackfillExtension sets shadow_metadata.suppress_ledger = true; the post-backfill verifier explicitly asserts SC1 attestation is N/A for the backfill window. The first hour of streaming after backfill is therefore the first real SC1 window. Worth confirming the suppress-ledger expectation in the relevant tests.
• UUIDv7 over Prisma v4 default. shadow_cutover_audit.id is minted explicitly in TS rather than relying on Prisma's default; the Python cutover code expects v7 so collisions on identical-state re-runs sort correctly. Easy to regress if someone "simplifies" by removing the explicit mint.
• R-24 cutover-drain mitigation. The consumer's apply loop advances confirmed_flush_lsn via the PrimaryKeepalive.wal_end field so cutover drain succeeds on a quiesced DB. Without this, the drain could time out on a DB that has no new commits to push, because send_feedback would otherwise need a data event to advance. Subtle and worth a callout in the apply-loop review.
• Mid-PR history is untidy. Several commits collapse migrations or restore files (30a7c18, dd087e3, e3989ec, 98771a7). The migration tree compared to your branch is small and clean in its final state (1 init + 5 new tables in one go), but the history has churn.

20. Top files — the load-bearing reading list

If anything here contradicts docs/shadow-write-runbook.md, trust the runbook. It is the single source of truth and is enforced by the audit table.