The Trilogy Times

## Summary

- select canonical/V3 mart fields in the Forecast worker and derive presentation-only stage contributions locally

- remove rounded Session 1 pipeline/community intermediates from new contracts, Convex publications, API responses, and UI helpers while retaining stored-row rollout tolerance

- recompute Physical variants with the V3 decimal returning/new-student decomposition and final floor

## Validation

- pnpm typecheck

- contracts: 60 tests

- sync worker/refresh: 87 tests

- Convex publication + public API: 132 tests

- forecast UI: 47 tests

- OpenAPI: 14 tests

- pnpm lint:test-architecture

- pnpm lint:boundaries

- Biome check on all changed files

Closes #1645

## Summary

Follow-up to Mercy's review of release PR 1641 (posted after the release was deployed). ADMISSIONS_PROGRAM_DETAIL_READ is shadow in production, so each shadow line is evidence for the Gateway cutover. This PR fixes the one finding where a line could count as clean without having checked anything, and settles the projection finding with tests.

1. A derived output with programs left out is no longer clean (admissions-program-shadow.ts, finding at line 741)

checkDerived compares a derived output (derived:enrollment-snapshot, derived:pipeline-funnel, derived:projection) only for programs whose inputs all mapped on both transports. A program whose input failed was left out, and the line could still read clean. If every program was left out, the kit compared two empty sets and reported clean.

Now, a derived line that would otherwise count as clean is degraded when:

- any program was left out because an input failed on a transport (N of M program(s) had an input that failed on a transport, so their derived output was not compared), or

- no program was compared at all (no program's derived output was compared this cycle).

A mismatch stays a mismatch. A program the cycle made no read for is still left out without failing the line, since nothing was published from it. scope gains failedPrograms beside programs and skippedPrograms.

2. Projection values were always compared; now the code says so and tests pin it (finding at line 331)

The finding reads the projection shapes' values list as the set of compared fields. It is not: a RecordShape only says how records are keyed and which values an example may show. The kit compares every field of every record. Evidence: the new Q3, community metric and app conversion tests pass on the code before this PR, and the existing Q2 test already reported fieldMismatchCounts: { currentEnrollment: 1 }.

What changes: the three projection shapes now list every field as showable (they hold forecast counts and program identifiers, no personal data), so a differing value is named on the line instead of [redacted]. The lists are checked at compile time against the record types, so a new field cannot be left out. The module header and RecordShape now state that the shape never narrows the compare.

## What changes per mode

| Mode | Change |

|---|---|

| legacy | None. No legacy code path is touched. |

| shadow | Publishes exactly what it published before (the legacy reads). Only shadow lines change: derived lines can now be degraded where they were clean, and projection examples show values. |

| gateway | None. |

## Business Value

The shadow window is the evidence the team uses to decide when the analytics worker can stop reading Redshift for per-program admissions data. A line that says clean when a program was never compared makes that evidence look stronger than it is. After this change a clean derived line means every program the cycle read was compared and matched, so the cutover decision rests on checks that actually ran.

## Manual Effort Estimate

Proposed: about 3 hours of focused work by hand, without AI. Keval, please confirm or adjust. It covers reading the finding against the kit to separate the real defect from the display-only one, the verdict rule and its scope counts, the compile-time field lists, and 9 new tests plus two updated ones.

## Testing

- sync: pnpm typecheck exit 0, pnpm lint exit 0, vitest run --maxWorkers=2 exit 0 (121 files, 2753 tests).

- New tests in admissions-program-shadow.test.ts:

- every program left out because an input failed: both derived lines degraded, with the empty compare underneath still clean: true;

- an input that fails on the Gateway only also keeps the derived line from clean;

- no program at all: degraded;

- a program the cycle made no read for: left out, line still clean;

- a mismatch stays a mismatch when another program was left out;

- Q2, Q3, community metric and app conversion: a differing value is a mismatch.

- Ran the new value-compare tests against the unchanged main code: Q3, community metric and app conversion pass there; Q2 fails only on the example text (values were redacted).

## Not covered

- Absence versus empty in the keyed Gateway readers, and the dry-run verdict: separate PRs.

- No Surtr change, no production change, no environment change.

🤖 Generated with [Claude Code](https://claude.com/claude-code)

## Summary

Follow-up to Mercy's review of release PR 1641. Three of her critical findings are one class: a per-program Gateway lookup turns a missing program key into an empty result and reports success (admissions-marketing-gateway.ts:537 and :625; admissions-program-gateway.ts:653 and the other keyed readers; the whole-table app conversion read at :782).

The underlying fact. A program with no rows in a mart has no key in it, exactly as the legacy WHERE key = $1 returns no rows for it, and several sources are sparse (transfers and pipeline deposits cover a minority of programs; most programs have no shadow-day events). A program the mart *left out* looks the same. The reader cannot tell the two apart, and a table-level row floor cannot either.

That splits the finding in two, and they need different answers.

### Shadow (live in production): the verdict was already right; now it is pinned and visible

In shadow, the same call's pg read is the arbiter of whether a program has rows. Rows on pg and none from the Gateway is a mismatch, however the Gateway came to have none. An absent key only compares clean when pg returned no rows either, which is a true match.

- New tests pin both cases for the per-program gate (a keyed source and the alias-based Q3 reader) and the marketing gate: a program the mart leaves out while pg has its rows is a mismatch; a program with nothing on either side is clean.

- Every keyed slice now carries present, and each shadow line counts the absent keys it compared as empty: scope.absentCalls (per-program) and scope.absentPrograms (marketing, whose lines gain a scope). A reader of the line can see how much of a clean result was "nothing on either side".

### Gateway (not enabled in production): real, and closed off until there is a rule

Without pg there is no arbiter: gateway mode would publish a left-out program as an empty result, and a short slice as that program's data. Closing that properly needs a completeness rule, which needs a baseline (what is published for the program now) or a manifest from the mart. That is AERIE-2683 and is a design across about a dozen sources, not a guard in the reader.

What this PR does is make sure gateway mode cannot publish in production before that rule exists:

- ADMISSIONS_MARKETING_READ=gateway was selectable. It is now unreleased, like the per-program gate: refused with AdmissionsMarketingConfigError, before the admissions run starts, unless the new ADMISSIONS_MARKETING_READ_NONPROD_OPT_IN=true is set.

- Both gates now refuse gateway on the production worker even with the opt-in. The production worker is the one pinned to DBT_TARGET=production (compose.prod.yml); isA8ProductionWorker is the same literal check the dbt-backed gates already use. Until now "never set the opt-in in production" was a convention.

I did not make gateway mode refuse every absent key. That would fail every program that legitimately has no rows, on every cycle, for every sparse source; and shadow, which maps the Gateway side as gateway mode would, would then read degraded for those sources forever and stop producing evidence. The baseline rule in AERIE-2683 is the one that can tell the cases apart.

## What changes per mode

| Mode | Per-program gate (ADMISSIONS_PROGRAM_DETAIL_READ) | Marketing gate (ADMISSIONS_MARKETING_READ) |

|---|---|---|

| legacy | None. | None. |

| shadow | Publishes exactly as before. Source lines gain scope.absentCalls. No verdict changes. | Publishes exactly as before. Lines gain scope (programs, uncomparedPrograms, absentPrograms). No verdict changes. |

| gateway | Refused on the production worker even with the opt-in. Outside production, with the opt-in, the reads behave as before. | Now refused without ADMISSIONS_MARKETING_READ_NONPROD_OPT_IN=true, and always refused on the production worker. Outside production, with the opt-in, the reads behave as before. |

Production today runs both gates in shadow, so nothing it does changes except the extra counts on the lines. An environment that has ADMISSIONS_MARKETING_READ=gateway set without the opt-in would fail its admissions cycle at start with a clear config error after this deploys; I know of none.

## Business Value

The team is collecting shadow evidence to decide when the analytics worker can read admissions data from Surtr's Gateway instead of Redshift. This change does two things for that decision. It shows, on every line, how many programs matched only because both sides had nothing, so a clean window is not over-read. And it makes it impossible to switch production to gateway mode for these two gates before the missing-program rule exists, so a mart that drops a school can never blank that school's admissions data in the dashboards.

## Manual Effort Estimate

Proposed: about 5 hours of focused work by hand, without AI. Keval, please confirm or adjust. It covers working out which half of the finding is real (reading both gates, the kit and the refresh's write path), deciding between refusing absent keys and gating release, the presence flag and counts through two shadow implementations, the production refusal for both gates, and about 20 new or changed tests.

## Testing

- sync: pnpm typecheck exit 0, pnpm lint exit 0, vitest run --maxWorkers=2 exit 0 (121 files, 2779 tests).

- New tests:

- per-program shadow: absent key with nothing on pg is clean and counted; a program the mart leaves out is a mismatch (keyed reader and Q3 aliases);

- marketing shadow: the same two cases, with scope.absentPrograms;

- marketing gate: gateway refused without the opt-in (global and per-source), opt-in value must be exactly true, refused in production with the opt-in, legacy and shadow unaffected in production;

- per-program gate: refused in production with the opt-in; legacy and shadow unaffected;

- isA8ProductionWorker; the orchestrator aborts before any run write on a refused marketing configuration.

- Existing marketing gateway-mode tests now pass the opt-in; their assertions are unchanged.

## Not covered

- The completeness rule for gateway mode (AERIE-2683): per-program baselines from Convex, or a presence manifest published by the Surtr marts. Until it lands, gateway mode outside production still publishes an absent key as empty.

- No Surtr change. If the manifest route is chosen, every keyed mart (the seven per-program marts and the four marketing marts) would need to publish which program keys it covers.

- Not run against the live Gateway.

🤖 Generated with [Claude Code](https://claude.com/claude-code)

## Summary

Incident (2026-10-02, SURTR-1590, related SURTR-1518). quickbooks-core-tables fanned out to its three marts at 06:46:25 UTC. mart-school-performance-unit-economics-refresh (Table 2) and mart-aerie-education-financials-refresh (AE) both died with deadlock detected, but not against each other. All three QuickBooks marts take quickbooks_financial_refresh_lock first, so they queue behind one another (AE got it at 06:46:29; QB waited 423 s and Table 2 436 s on it). The inversions were against the core-education-ontology-refresh producer, which rhodes-staging-sync triggered at 06:46:58 UTC and which does not take that lock, and against a plain reader. Relation 15058250 is core_education.dim_program and 15058264 is core_education.bridge_school_link.

Evidence from sys_query_history (CQL_download_OM):

| Time (UTC) | Statement | Result |

|---|---|---|

| 06:46:29 | AE sp_refresh_agg_school_pl_breakdown: LOCK bridge_school_link | waited 422 s on a holder not visible to this user, granted 06:53:32.085 |

| 06:47:02 | reader WITH alpha_school_years ... (session 1073826357; reads dim_school, bridge_school_link, dim_program) | 390 s lock wait, ends 06:53:36 |

| 06:53:32 | AE LOCK dim_program | blocked by a reader that holds dim_program and waits for bridge_school_link, which AE now holds: deadlock, AE aborted 06:53:33 (process 4582 / 5144 in the message). The reader above fits (its wait ends 06:53:36), but the message does not name it. |

| 06:53:40 | ontology retry sp_refresh_aerie_ontology: one LOCK TABLE rhodes..., bridge_school_link, dim_program, dim_school, dim_site, xref_school_source (the first attempt was a deadlock victim at 06:53:37) | takes bridge_school_link, then waits for dim_program |

| 06:58:17 | Table 2 sp_refresh_agg_school_performance_unit_economics_qtd: gets dim_program (it had dim_school already), asks for bridge_school_link | deadlock, Table 2 aborted 06:58:18 (process 4592 / 9564). The ontology LOCK completes 06:58:19.165, one second later, which identifies it as the other process. |

The ontology producer locks bridge_school_link, dim_program, dim_school, dim_site, xref_school_source. Table 2 and the Guide QTD procedure locked dim_school, dim_program, bridge_school_link: a textbook inversion. The AE procedure already follows the producer order, so the AE-versus-reader cycle is a queue pile-up behind the 422 s holder rather than an ordering bug in that procedure; it is not changed (the file is 165 KB, above the 100 KB Data API statement limit, and the live version is already newer than main).

Review follow-up (this push). Mercy's blocking finding on the first revision was that the contract left out the QuickBooks core writers. They lock xref_school_source before dim_school, the reverse of the producer, and they are not gated against it. Fixed here: the audit below covers every procedure that touches the ontology, sp_refresh_quickbooks_profit_and_loss_posting and sp_refresh_quickbooks_budget_detail now follow the canonical order, and the contract test discovers participants from the repo so a new procedure cannot silently escape it.

Lock-order map (ontology and shared relations, in acquisition order). Every participant takes these at the start of its body, before it reads them (a publication target may be locked just before its DELETE).

| Procedure | Before | After |

|---|---|---|

| sp_refresh_aerie_ontology (producer, unchanged) | bridge, program, school, site, xref_school_source | same |

| sp_refresh_agg_school_pl_breakdown (AE, unchanged) | posting fact, bridge, program, school, xref_school_source | same |

| sp_refresh_agg_school_performance_unit_economics_qtd (Table 2) | school, program, bridge, xref_school_source | bridge, program, school, xref_school_source |

| sp_refresh_agg_school_qtd_guide_staffing_program_spend | school, program, bridge, xref_school_source; posting fact after them | posting inputs first, then bridge, program, school, xref_school_source |

| sp_refresh_agg_school_performance_quickbooks_budget_qtd | xref_school_source, then school | school, then xref_school_source |

| sp_refresh_qtd_hc_posting_classification | school, xref_school_source, posting fact | posting fact, school, xref_school_source |

| sp_refresh_agg_school_qtd_all_other_headcount | Guide mart, then classification | classification, then Guide mart |

| sp_refresh_quickbooks_profit_and_loss_posting | xref_school_source, class_school, ue_model, school; posting fact ~900 lines later | posting fact, school, xref_school_source, class_school, ue_model |

| sp_refresh_quickbooks_budget_detail | xref_school_source, class_school, ue_model, school | school, xref_school_source, class_school, ue_model |

| sp_refresh_school_quickbooks_pl_reconciliation, ..._facilities_capex_campus_spend, ..._unit_economics_per_student_qtd, sp_load_q94_site_finance_entity_xref (unchanged) | already consistent | same |

sp_refresh_quickbooks_financial_contracts runs vendor identity, posting, budget, attribution, school P&L and reconciliation as children of one transaction (the handler calls only it), so its effective order is the children's locks concatenated; the first acquisition is posting fact, school, xref_school_source, which the contract checks.

Audit of every procedure that references bridge_school_link, dim_program, dim_school, dim_site, xref_school_source or a view over them (34 under pipelines/, 30 in the live catalog): the producer, 10 locking participants (the table above plus classification and q94), 4 one-off migrations that drop themselves (sp_replace_legacy_dim_school, sp_drop_retired_dim_school_next, the two sp_migrate_quickbooks_*), and 19 that take no explicit lock on these tables and are listed in UNLOCKED_READERS with a test that fails if one starts locking them: hubspot fct_admissions_deal/_event/hubspot_core_foundation, fct_enrollment, the two student-snapshot appenders, q48 publish, capex, finalsite_billing, school_calendar, aerie_admissions_program/_directory and the seven forecast procedures. The contract's participant list also holds All Other and Facilities, which lock shared marts without naming an ontology table. Readers are not changed (see below).

Canonical order. CANONICAL_LOCK_ORDER in mart-aerie-education-financials-refresh/tests/test_sibling_lock_order_contract.py: QuickBooks gate and posting inputs, then bridge_school_link, dim_program, dim_school, dim_site, xref_school_source, then the remaining inputs, then publication targets. Plain alphabetical was rejected: it would put the QuickBooks gate after dim_*. A procedure is *gated* when its first lock is the QuickBooks gate; gated procedures serialize on it and cannot deadlock each other, so the contract requires every pair that includes an *ungated* procedure (the producer, classification, All Other, Facilities, Table 3, q94) to agree on relation order, and ungated procedures to ascend through the canonical list.

Changes. Seven procedures re-ordered (lock statements and comments only; no lock mode, logic or output changes). The posting writer now locks the posting fact first instead of ~900 lines later, just before its DELETE: no lock is added or removed, it is taken earlier within a coordinator transaction that already holds it until commit (at most the writer's own ~25 s runtime earlier, per the 10:52 run). Table 2 and QB budget QTD version markers are bumped to 2026-10-02.1; the QuickBooks core markers are left alone because their tests pin them. The contract test (61 tests) now discovers participants, models gating and the coordinator transaction, and fails against the old files (11 failures) for the QuickBooks writers, the coordinator and the marts changed earlier.

Not changed, found on the way:

- Unlocked readers can stall writers for minutes. At 10:57:42 hubspot sp_refresh_fct_admissions_event opened an 11.7 minute transaction; it reads bridge_school_link, dim_program, dim_school and dim_site, so it holds AccessShare on them until commit. QB budget QTD's LOCK dim_school waited 777 s and was granted 0.6 s after that transaction's last statement; Table 2 waited 796 s behind QB; AE's classification step stalled behind QB's posting-fact lock and timed out (the 10:56 AE failure). Not a deadlock, not caused by lock order, and not fixed here; options are an up-front LOCK ... IN ACCESS SHARE MODE in canonical order or copying the inputs to a temp table first, as pl_breakdown does for fct_admissions_deal.

- Live procedures from unmerged branches differ from main: pl_breakdown 2026-10-02.1 (codex/alpha-enrollment-denominator, order already canonical), Guide and Facilities (#2124, applied after the first apply here; Guide keeps the canonical order), retention (#2049) and classification (older than main: lacks #1459).

- Live Facilities from #2124 locks xref_school_source before the posting fact, the reverse of classification. They run back to back in one AE run, so this only matters if two AE runs overlap; #2124 will fail this contract until it takes the posting fact first.

- The AE, Table 1 and Table 2 handlers do not retry a deadlock victim; the Table 3 client does. Classification, All Other, Facilities and Table 3 take no QuickBooks gate.

Live apply 1 (marts), 2026-10-02 07:54:27 to 07:54:50 UTC. Quiet window: nothing in the QuickBooks chain RUNNING or started in the last 10 min, no CALL of the five procedures, no DDL on the involved schemas in the previous 30 min (as visible to the pipeline DB user). CREATE OR REPLACE PROCEDURE as admin, one statement per procedure back to back (the Data API 100 KB statement limit rules out one combined statement): classification, All Other, Guide, QB budget QTD, Table 2. Owner, ACL, OID, SECURITY INVOKER and arguments identical before and after (owner CQL_download_OM; ACL CQL_download_OM=X/CQL_download_OM, Surtr_Service_User=X/CQL_download_OM). For the four whose live body matched main byte for byte the live body is byte-identical to this PR's file; classification live is older than main (#1459), so only the lock-block change was applied on top of the live body.

Live apply 2 (QuickBooks core writers), 2026-10-02 17:56:55 to 17:57:03 UTC. Last quickbooks-core-tables run ended 12:10; at 17:56:40 no pipeline in the QuickBooks chain was RUNNING or had started in the previous 15 min, no CALL of the chain was running, and no DDL had touched the involved schemas in the previous 30 min. quickbooks-raw-sync is scheduled once a day at 06:00 UTC (next 06:00 tomorrow); its other runs, and core-tables since #2123, are on demand, so I re-checked immediately before applying. Both writers were byte-identical to main in the live catalog before the apply. CREATE OR REPLACE PROCEDURE as admin, posting then budget detail back to back. Owner CQL_download_OM, ACL CQL_download_OM=X/CQL_download_OM ; Surtr_Service_User=X/CQL_download_OM, OIDs 16074835 and 17282951, SECURITY INVOKER and 9 arguments identical before and after; live body md5 now equals the PR file body for both (667ec6a7..., 1f3eaa44...). Catalog read of the live bodies: the coordinator's effective order is posting fact, dim_school, xref_school_source, and every pair involving an ungated live procedure agrees except the live Facilities pair noted above.

Since the first apply: no deadlock detected in pipeline_runs_prod. The 12:10 on-demand fan-out completed on all three marts and Table 3. The 10:56 fan-out's QB and Table 2 runs succeeded after the reader stall above; AE timed out on it and succeeded on re-run. The posting and budget writers have not yet run in their new order.

## Business Value

The School Performance reports (Tables 1, 2 and 3) and the Aerie school P&L marts are the finance team's view of school economics, and they all refresh off the same QuickBooks publication. Whenever that fan-out overlapped the school ontology refresh, a mart could abort on a lock deadlock, leaving its table stale until someone noticed the alert and re-ran it (on 2026-10-02 Table 2 and the AE P&L marts failed, and Table 3, which triggers off Table 2, never ran). This change puts every concurrent writer of the ontology tables, including the upstream QuickBooks core writers that feed all of those marts, on one lock order, and adds a CI check that discovers new procedures touching those tables so a future edit cannot reintroduce an inversion. That cuts alert noise, on-call triage time and the window in which leadership dashboards show stale numbers. It also documents, with evidence, the separate multi-minute stall caused by long-running readers, which is the next largest source of failed refreshes.

## Manual Effort Estimate

About 23 focused hours (roughly three working days) to do this by hand. The first revision was about 14 hours: reconstructing the deadlock timeline from the Redshift system history and mapping OIDs (3 h), reading the nine involved stored procedures for lock order and unlocked reads (3 h), working out a canonical order consistent with the producer, the QuickBooks writers and the live-versus-main drift (2 h), the five edits (1 h), the parser-based contract test (3 h), and the live apply with owner/ACL checks (2 h). The review follow-up adds about 9 hours: auditing the 34 procedures in the repo and 30 in the live catalog and classifying them (2 h), analysing the coordinator's single-transaction semantics and re-editing the two QuickBooks writers (2 h), reworking the contract test with discovery, gating and the coordinator's effective sequence (4 h), and the second live apply and verification (1 h). Proposed by Claude, Keval to confirm or adjust.

## Test plan

- [x] quickbooks-core-tables: uv run pytest 95 passed

- [x] mart-aerie-education-financials-refresh: uv run pytest 208 passed (61 are the contract tests)

- [x] mart-school-performance-quickbooks-refresh: uv run pytest 78 passed

- [x] mart-school-performance-unit-economics-refresh: uv run pytest 42 passed

- [x] mart-school-performance-unit-economics-per-student-refresh: uv run pytest 16 passed

- [x] ruff@0.15.22 check and ruff format --check clean on the touched Python file (no other Python changed)

- [x] Contract test run against the previous DDL (origin/main files in a throwaway worktree): 11 failures (50 pass), covering the QuickBooks core writers, the coordinator transaction and the marts changed earlier

- [x] Live apply 1: five marts, 07:54:27 to 07:54:50 UTC; owner/ACL/OID identical, body md5 equals the expected body

- [x] Live apply 2: two QuickBooks core writers, 17:56:55 to 17:57:03 UTC; owner/ACL/OID identical, body md5 equals the PR file body

- [x] Catalog read of live bodies: coordinator order and all ungated pairs agree, except the live Facilities version from #2124

- [x] No deadlock detected in pipeline_runs_prod since the first apply; 12:10 fan-out succeeded on all marts and Table 3

- [ ] First quickbooks-core-tables run after apply 2 completes with the new writer order

Linear: SURTR-1590

🤖 Generated with [Claude Code](https://claude.com/claude-code)

## Summary

This is a standalone production-incident fix for the resources entity failure observed in TimeBack Raw Sync run 699d8c8a-7507-4693-8ff6-8cf59da3518e.

It hardens incremental resources extraction against source-gateway response limits by using auditable, fail-closed page-size degradation while preserving keyset coverage and publication semantics. The exact PR head was deployed and validated successfully in production before this PR was opened.

Production effect: compatibility hardening.

---

## Why

resources responses vary dramatically in encoded size across later key ranges, so a fixed row limit can work for hundreds of pages and then repeatedly fail with HTTP 502. Static limits of 1,000 and 500 were both disproved by bounded production runs without publishing warehouse state. This change makes the existing incremental contract resilient to that payload variation without weakening closure, replay, or atomic publication checks.

---

## Business Value

- Restores healthy publication of the only stale TimeBack entity without re-running the other 20 healthy entities.

- Preserves exact immutable evidence for every successful page and every gateway-driven size transition.

- Keeps failures safe: exhausted retries at the minimum page size still fail before warehouse publication.

- Makes recovery reproducible from source-controlled runtime and manifest-validation behavior.

---

## How does it work

1. Incremental resources keyset extraction starts with a 500-record page limit; users retains 100 and ordinary entities retain 2,000.

2. After three retryable HTTP 5xx responses, the extractor lands the exact failed response and retries the same inclusive cursor at half the active limit.

3. The reduced limit is retained for subsequent pages, with deterministic transitions down to one record; another retryable 5xx at one record fails closed.

4. The manifest records the starting/minimum limits, every degradation receipt, and the actual limit used by each successful page.

5. Replay/transform validation verifies receipt checksums, ordered halving, cursor and offset binding, keyset continuity, count closure, and terminal confirmation before atomic raw/clean publication.

---

## Scope

### Included in this phase

- Entity-specific incremental keyset sizing for payload-heavy resources responses.

- Bounded, evidence-backed page-size degradation on exhausted retryable 5xx responses.

- Manifest and replay validation for adaptive page limits.

- Regression coverage for later-boundary failures, retained reduced limits, terminal confirmation, and the minimum-size fail-closed path.

- Exact final diff paths:

pipelines/runners/timeback-raw-sync/README.md
pipelines/runners/timeback-raw-sync/src/timeback_client.py
pipelines/runners/timeback-raw-sync/src/transform.py
pipelines/runners/timeback-raw-sync/tests/test_timeback_client.py
pipelines/runners/timeback-raw-sync/tests/test_transform.py

### Deliberately excluded for later phases

- Warehouse DDL or migration — table shape, grain, column types, lineage, and publication semantics are unchanged; existing source-controlled DDL remains sufficient to recreate the warehouse safely.

- Changes to full-snapshot partitioning, fan-out extraction, or source-limit isolation — those contracts are unchanged.

- Changes to entities other than the existing users cap and the new resources behavior.

- VALIDATION.md, generated CDK artifacts, local evidence files, and unrelated repository changes.

---

## Test plan

### Automated validation

- TimeBack full suite — 262 passed (uv run pytest -q)

- Ruff lint — passed (uv run ruff check src tests scripts)

- Ruff formatting — 28 files already formatted (uv run ruff format --check src tests scripts)

- git diff --check — passed

- exact-head diff scope — only the five authorized paths listed above

- commit trailers — no Co-authored-by trailers in any PR commit

### Time for Implementation

Approximately 2–3 engineer-days without AI assistance, including failure reproduction, test-first implementation, two bounded production falsification runs, adaptive redesign, deployment, and warehouse reconciliation.

### Manual QC

- Deployed exact commit eefd7af4d3d67fd87f0aedd75b4c9f730b8765be to Pipeline-timeback-raw-sync-prod as task definition revision 31.

- Verified image tag 863936842f9fcfd55ccb58ff7b47b2325f1b83721566d634f9accc44002d2cce and digest sha256:1ad3b3dea3715e7f648ae573cfea09ffb8d73b15132c67aff8ac210b3a13ad27.

- Ran only resources in incremental mode: Step Functions execution resources-adaptive-proof-20261002T142659Z, pipeline run da8aadc4-612c-42e1-9222-483c48a8804f.

- Observed production degradation at the same keyset traversal: 500 → 250 → 125, with two immutable HTTP 502 receipts; extraction then completed with 499,227 records across 1,830 pages.

- Verified manifest SHA-256 0443672bad9731e96b5a22decff058e03c7da312a4c921777d7fa53d9641982b, terminal confirmation, stored watermark overlap, and actual per-page limits.

- The run succeeded with 499,227 source/raw/clean delta rows, one published entity, zero failed entities, and exit code 0.

- Redshift reconciliation: raw and clean each contain 2,185,849 rows; exactly 499,227 rows in each carry the recovery run ID; null keys, duplicate keys, and raw/clean key-set differences are all zero.

- The clean watermark advanced from 2026-09-13 09:10:24.707 to 2026-10-02 14:27:22.923.

- Both failed proof runs produced zero ingestion-ledger rows, and no non-resources entity published during the successful run.

## Summary

Adds one column, dbt_invocation_id, as the last column of two marts:

- mart_admissions_pipeline_dtl

- mart_enrollment_dtl

Linear: [AERIE-2682](https://linear.app/builder-team/issue/AERIE-2682/a8-stamp-dbt-invocation-id-on-mart-admissions-pipeline-dtl-and-mart)

@vvp-trilogy, this touches your models, so it is yours to review and merge. I will not merge it.

## What invocation_id is (no source field needed)

invocation_id is a variable dbt itself provides to every model. It is the id (a UUID) of the dbt run that is building the table. dbt fills it in when it compiles the SQL, so the model line is just:

cast('{{ invocation_id }}' as varchar) as dbt_invocation_id

- It is not read from any source table. There is no ingestion change, no new staging column and no join.

- Every row of one build has the same value. The next build writes a new value.

- The hourly job is one dbt build, so both marts built in the same run carry the same id. A PR build gets its own id in its pr<N>_ tables.

- It is a run id, not personal data. dbt already writes the same id to its logs and run_results.json.

## The change

| File | Change |

|---|---|

| dbt/models/marts/admissions/mart_admissions_pipeline_dtl.sql | Final select gains dbt_invocation_id after u.*. |

| dbt/models/marts/enrollment/mart_enrollment_dtl.sql | The final UNION ALL moves into a unioned CTE; the final select is u.* plus dbt_invocation_id, so the value is written once for both arms. Rows and existing columns are unchanged. |

| _mart_admissions__models.yml, _mart_enrollment__models.yml | Column documented, with a not_null test. The pipeline mart's "Column population" table gets a row. |

The column is last, so no existing column changes position. Materialization is untouched: both marts are table (set per layer in dbt_project.yml), so there is no on_schema_change to handle. Neither model has an enforced contract.

## Why Surtr needs it

Surtr's mart-aerie-dbt-publication-refresh pipeline copies these two production marts every 10 minutes so the analytics worker can read them through the Surtr Gateway. It must know when dbt has rebuilt a mart. Today it uses the table's pg_class OID as the build marker. dbt_invocation_id is the marker it was designed to use.

## Check 1: does the Surtr copy keep working? Yes, with no Surtr change.

Read from Surtr main (pipelines/runners/mart-aerie-dbt-publication-refresh/ and pipelines/cdk/sql/mart_education/):

- Explicit column lists. Both procedures copy with Aerie's own reader SQL: 53 named columns for the pipeline detail, 6 for the SIS rollup and 21 for the SIS members. Nothing does SELECT * from the dbt relation.

- The type guard ignores extra columns. It walks the columns of the Surtr mart and looks each one up by name on the dbt relation. A column that exists only on the dbt side is never examined.

- Parity checks are safe. The reconciliation EXCEPT queries compare CTEs built from the same named lists. The == 53 test counts Aerie's select list, not the dbt relation's columns.

- The Surtr README says so: "New dbt columns that Aerie does not read ... need nothing here."

- Marker today: v_source_build_marker := 'pg_class_oid:' || v_source_oid in each procedure. Surtr does not read dbt_invocation_id yet.

- What Surtr expects of the new column: the name dbt_invocation_id. It pins no type and no position. The value lands in source_build_marker VARCHAR(128); a 36-character UUID fits.

Order. This PR goes first and is safe alone. Only after it is merged and one production build has run should Surtr switch its marker (a separate Surtr ticket): replace the one v_source_build_marker := assignment in each of the two procedures, plus the tests and reconciliation queries that pin the OID form. Doing the Surtr switch first would make its procedures fail on a missing column.

## Check 2: dbt side

- Materialization: table for both. No incremental model is touched.

- Schema yml: columns are documented, so the new column is added to both files. No enforced contract on either model.

- Downstream dbt readers select named columns: int_admissions_forecast_grade_operands, and the forecast_neutral_ec and forecast_pipeline_scope macros (used by int_admissions_forecast). No model does select * from either mart, so the column does not spread.

- Singular tests: three use select * from the pipeline mart inside a CTE and then filter on named columns (..._community_hubspot_ids, ..._deposit_paid_date_scope, ..._enrollment_date_source). An extra column does not affect them. No test pins a column list, count or order.

- Unit tests: the three unit tests on mart_admissions_pipeline_dtl assert only the columns named in expect. The unit tests that use either mart as an input (_int_admissions__models.yml, forecast-grade-operands.yml) null-fill columns they do not set.

## Check 3: Aerie readers

Every reader of the two relations, found by searching the repo for both model names:

| Reader | Columns | Row schema |

|---|---|---|

| queryAdmissionsPipelineRows (sync/src/analytics/queries/admissions-pipeline.ts) | 53 named columns | z.object, not .strict() |

| rollupsSql (sync/src/redshift/sis-enrollment.ts) | 5 named columns + COUNT | z.object |

| membersSql (same file) | 21 named columns | z.object |

No SELECT *, no .strict() schema, so an extra column cannot reach or break them. chat/ and packages/contracts only mention the marts in comments. The other mart_enrollment_dtl hits in sync/ are the EduCRM table staging_education.sales_educrm_wh_mart_enrollment_dtl, a different relation. An org-wide code search found no reader of these two relations outside Aerie and Surtr.

Not checkable from code: ad-hoc or BI queries by edu_read users that do SELECT *. They would see one more column at the end.

## Testing

- dbt parse: passes (dbt-core 1.12.0, dbt-redshift 1.11.0, placeholder profile, no warehouse).

- dbt compile --no-introspect of both models: renders offline; the final selects end with CAST('<uuid>' AS VARCHAR) AS dbt_invocation_id.

- Not run locally: dbt build and dbt test. They need warehouse credentials, which I did not use. The PR build (prefixed) job on this PR does both.

CI result (PR build (prefixed), run twice, same result both times):

- dbt build: 103 of 103 models and seeds built, including both marts with the new column.

- dbt test: 543 pass, 8 warn, 1 fail. Both new tests pass (not_null_mart_admissions_pipeline_dtl_dbt_invocation_id, not_null_mart_enrollment_dtl_dbt_invocation_id).

- The one failure is assert_sis_enrollment_lifecycle_has_arrival (1 row). It reads only int_enrollment_cohort, which is upstream of the marts and is not changed here; the dbt manifest shows neither changed mart among the test's ancestors. It is a SIS data finding (one enrollment in a lifecycle cohort with no arrival cohort in the same year), not an effect of this PR. It keeps the dbt check red until the data or the test is addressed. @vvp-trilogy, that one is yours to judge; I have not touched the test.

## Business Value

The Surtr copy of these two marts feeds Aerie's Admissions Pipeline and SIS Enrollment reports through the Gateway. A build marker that dbt itself writes makes "which dbt run is this copy from" a plain, auditable value on the row instead of a Postgres catalog number. That makes stale-copy alerts and parity checks easier to trust and to explain, and it removes the last open dependency (A8 plan §9 D4) on the way to moving the EC2 analytics worker reads onto Surtr.

## Manual Effort Estimate

About 2.5 hours of focused work by hand: reading the Surtr procedures and tests to confirm an extra column is safe (1 h), tracing dbt and Aerie readers (1 h), the change and PR (0.5 h).

_Proposed by Claude. @kevalshahtrilogy, please confirm or adjust._

## Not covered

- The Surtr switch from the OID marker to dbt_invocation_id (separate Surtr ticket, after this is in production).

🤖 Generated with [Claude Code](https://claude.com/claude-code)

## Summary

- evaluate Session 1 data-availability gates before transitioning a passed milestone to locked_actual

- keep missing historical observations unavailable with the controlled reason and null calculation/provenance

- add deterministic dbt unit coverage for missing and complete passed-milestone observations

- document the lock completeness requirement

## Validation

- poetry run dbt test --select int_admissions_forecast_resolved_session_1_lock_requires_observation

- poetry run dbt parse --no-partial-parse

Closes #1644

## Summary

- rename the SIS enrollment lifecycle assertion with the data_quality_ prefix

- configure the singular test at warning severity

- keep the lifecycle inconsistency visible without failing CI/CD

## Why

This assertion observes source-data quality and does not change production models. Running it as an error during PR CI blocks unrelated development on source data that the code change did not introduce. Warning severity preserves the signal while unblocking urgent delivery; production scheduled builds already exclude tests.

## Validation

- REDSHIFT_HOST=unused REDSHIFT_PASSWORD=unused poetry run dbt parse (with profiles.yml.docker)

- parsed manifest confirms data_quality_sis_enrollment_lifecycle_has_arrival has severity=warn

- git diff --check

## Summary

Follow-up to Mercy's review of release PR 1641 (finding at sync/src/scripts/dry-run-admissions-program-detail-shadow.ts:103).

The per-program shadow dry-run counts rejected legacy reads in failedReads, but its final verdict looked only at the shadow lines. A failed read that produced no non-clean line could still end in "Every check is clean" and exit 0.

failedReads now takes part in the verdict: any failed legacy read makes the run not clean, the script prints N legacy read(s) failed, so the run is not clean whatever the lines above say, and it exits 1.

Class audit: every dry-run that keeps a failure counter apart from its verdict. I searched all 22 dry-run-*.ts scripts for a counter (++, +=) or a swallowed rejection (.catch) and read the verdict logic of each A8 one. This was the only script with a failure counter outside its verdict. The other A8 dry-runs set their verdict to not clean inside each read catch (reference, pipeline, SIS, expenses, the generic dry-run-a8-shadow) or start from not clean and only turn clean on a result (community funnel, community deposits). The marketing dry-run had the same gap and was fixed in PR 1587.

## What changes per mode

| Mode | Change |

|---|---|

| legacy | None. |

| shadow | None on the worker. Only this developer script's verdict and exit code change. |

| gateway | None. |

## Business Value

An operator runs this script to decide whether the per-program shadow looks healthy before trusting a shadow window. If it exits 0 after reads failed, a partial run can be taken for a clean one. With this change a green run means every read the cycle makes actually ran and every check was clean.

## Manual Effort Estimate

Proposed: about 45 minutes of focused work by hand, without AI. Keval, please confirm or adjust. Most of it is checking the other 21 dry-run scripts for the same gap; the change itself is a few lines.

## Testing

- sync: pnpm typecheck exit 0, pnpm lint exit 0, vitest run --maxWorkers=2 exit 0 (121 files, 2744 tests).

- The dry-run scripts have no unit tests (each runs main() on import and needs Redshift and a Gateway key). The change is one boolean term in the verdict plus one message.

## Not covered

- The nit on admissions-program-gateway.ts:755 (use AdmissionsProgramDetailConfigError for a program with no code): not changed; reasoning is on the PR 1641 thread.

- I did not run the script against live Redshift or the Gateway.

🤖 Generated with [Claude Code](https://claude.com/claude-code)