The Trilogy Times

## Summary

- Bump Shipyard to 0.6.11.

- Add the approved public release notes.

## Business Value

- Delivers the approved branch workflow, Pi conversation controls, repository history pagination, task timing visibility, and Pi image diagnostics.

## Implementation Effort

- Low: metadata-only release change; CI performs the signed build and publication.

## Test Plan

- [x] pnpm test:release

- [x] git diff --check

- [x] Verified the diff contains only package.json and releases/0.6.11.md.

Linear: [AERIE-2612](https://linear.app/builder-team/issue/AERIE-2612/a8-u02-a8-read-gate-kit-read-mode-gateway-table-reader-with-type) (related: AERIE-445)

## Summary

A8 unit U02: the shared plumbing every later A8 Aerie unit uses to move one runRefreshCycle Redshift read onto a Surtr Gateway parity mart. New files only, under sync/src/analytics/a8/, plus a dry-run template. It doesn't touch A4 or A5 files or any refresh path, and nothing reads the kit until a group unit (U05, U09–U11, U14, U17, U20–U23, U25) wires it in.

Generalised from A4 (school-source-directory-shadow-compare.ts, school-source-directories-gateway.ts, the per-source override in PR 1531) and A5's purge bound (PR 1530).

### Kit API (what later units copy)

read-mode.ts: the gate

- resolveA8ReadModes({ gate, sources, dbtBackedSources? }, env?, warn?) → Record<source, "legacy" | "shadow" | "gateway">. Call once per cycle.

- <GATE> sets every source; <GATE>_<SOURCE> (a8SourceOverrideVar) overrides one.

- Unset/blank: legacy. pg is accepted as a spelling of legacy (A4/A5 muscle memory).

- Unrecognized global → legacy + WARN. Unrecognized override → ignored + WARN, and capped at shadow, so a typo never selects gateway. Case-sensitive, like A4.

- dbt-backed sources stay legacy (with a WARN) unless DBT_TARGET is exactly production.

- readsGateway(mode), publishesFromGateway(mode).

gateway-table-reader.ts: the reader and the type-parity layer

- readA8GatewayTable({ source, columns, minRows, maxSourceAgeMs?, rowIdColumn?, buildMarkerColumn?, pageSize?, maxPages? }, { client?, now? }) → { rows, lineage: { source, sourceRunId, sourcePublishedAt, buildMarker }, pages }.

- columns maps each legacy SQL output column to its Redshift type (varchar | super | smallint | integer | bigint | numeric | real | double | boolean | date | timestamp | timestamptz). Only these columns are returned, in pg shape, so the legacy query's own mapRows runs unchanged.

- Type parity: each Gateway (Data API) value is rebuilt as the text Redshift sends over the pg wire and run through the pg driver's own text parser (pg.types.getTypeParser). BIGINT → string, NUMERIC → string, DATE/TIMESTAMP → local-time Date, TIMESTAMPTZ → Date, SUPER → its JSON text as returned, REAL/DOUBLE → the 6/15-significant-digit number pg parses. Parity holds by construction, including pg's local-time DATE handling.

- Rules, each a thrown A8GatewayReadError: Zod on every row; an absent column fails; integers must be within their Redshift range; date/time text must name a real calendar day; population floor; one non-empty source_run_id and one source_published_at across all pages; max age (default 6h); unique non-empty mart_row_id (default; rowIdColumn: null opts out); optional single build marker for dbt copies.

- The default client is built per call, not at import, so a script that loads dotenv first sees the key.

- assertSharedLineage(lineages): one source_run_id across several reads (SIS rollups + members, Forecast V2 + operands).

- gatewayValueToPg(type, value): the parity function on its own.

keyed-shadow-compare.ts: the shadow check

- compareA8Keyed({ source, keyFields, keyOf?, duplicateKeys?, valueAllowlist? }, pgRecords, gatewayRecords): keyed multiset compare of mapped records, order-free and type-strict ("5" ≠ 5, Date ≠ ISO string, absent ≠ undefined).

- duplicateKeys: "never_clean" (default) or "multiset" for pre-dedupe row sets (Q5 without #n, D2 before last-row-wins, vendors).

- Output: counts, per-field mismatch counts, value *types*, and up to 10 examples. Values appear only for valueAllowlist fields; a key appears only when every key field is allowlisted, and always as those fields' values (a custom keyOf's output is never shown). Everything else is "[redacted]".

- runA8ShadowCheck({ spec, pgRecords, readGateway, skew?, log? }): never throws, never publishes, logs exactly one a8_shadow_check line (info when it counts as clean, WARN otherwise). Outcomes:

- clean;

- source_advanced: EDUCRM rule, skew: { rule: "source_advanced", rereadPg }. After a mismatch, pg is re-read once; if the re-read equals the Gateway, the result counts as clean;

- stale_copy: dbt rule, skew: { rule: "stale_copy", pgBuildMarker }. Different build markers mean the check is skipped, not a mismatch;

- mismatch;

- degraded: the Gateway read failed (swallowed).

- countsAsClean and compared fields drive the shadow-window count. A check whose log line couldn't be written is degraded.

purge-guard.ts

- resolveA8MaxPurgeFraction("<GATE>_MAX_PURGE_PCT") (default 5%, invalid → 5% + WARN), countA8WouldPurge(existingIds, incomingIds), checkA8PurgeBound(label, counts, fraction), and assertA8PurgeBound (throws A8PurgeGuardError). Counts only in messages. U05 uses it for purgeStalePrograms.

operator-safe-error.ts

- describeA8Error(error): the kit's and the Gateway client's own messages as they are (trusted by instanceof, never by error.name); Zod reduced to codes and paths; anything else to its name and code, each shown only if it looks like an identifier. A kit-local copy of PR 1531's describeErrorForOperator, which isn't on main yet.

sync/src/scripts/dry-run-a8-shadow.ts: the template

- Loads dotenv, then await import()s every app module.

- Ships with one live canary: A4's SIS organization directory read through the kit (varchar + TIMESTAMPTZ parity).

- Exit 0 only when every check counts as clean.

- Run: cd sync && pnpm exec tsx src/scripts/dry-run-a8-shadow.ts. No package.json script is added, to keep this PR new-files-only; each group's copy can add one.

### One deliberate reading of the recipe

The rule "only "" and null map to null" is applied to typed columns (numbers, dates, booleans), where a real value can never be empty. Text columns (varchar, super) keep "": pg returns "" for an empty string, and the shared mapper must see the same input on both transports. Mapping it to null would make the Gateway path diverge from legacy (e.g. a non-nullable z.string() in a legacy mapper). Legacy mappers that already turn "" into null keep doing so on both sides.

## Business Value

A8 is the largest slice of the Aerie EC2 → Surtr migration: about 20 Redshift reads in runRefreshCycle. This PR is the foundation for the 11 Aerie units that follow. Building it once:

- makes each group unit smaller and consistent, so Mercy reviews less and every cutover behaves the same way;

- puts the plan's biggest technical risk (transport type parity, §8 risk 1) in one tested place instead of 11 hand-rolled readers;

- builds in the safety rules (fail-closed reads, PII redaction, purge bound, never-throw shadow) once, so a later unit can't forget one.

Together these move the worker's direct Redshift credential and analytics-worker toward deletion.

## Manual Effort Estimate

Proposed: ~12 focused hours for Keval by hand, without AI. Keval, please confirm or adjust.

- Type-parity research (Data API field union, pg-types/postgres-date behaviour, Redshift float text): ~2.5h

- Reader and parity layer: ~2h

- Keyed multiset compare, redaction, classifiers, orchestrator: ~2.5h

- Read-mode gate and purge guard: ~1.5h

- Tests (158): ~3h

- Dry-run template and PR write-up: ~0.5h

## Testing / evidence

All commands ran in the worktree on this branch, rebased on origin/main 3d4fe1a97 (after Mercy round 3).

| Check | Result |

|---|---|

| cd sync && pnpm typecheck | pass |

| pnpm lint (repo root: boundaries, convex-paths, read-bounds, test-architecture, knowledge, biome) | exit 0. The 2 warnings are pre-existing, in unrelated chat/skill/forge-api/scripts/sindri.mjs |

| cd sync && pnpm test --maxWorkers=2 | 86 files, 1,521 tests passed |

| New kit tests alone, under TZ=Asia/Kolkata, TZ=UTC and TZ=America/Los_Angeles | 5 files, 158 tests, pass in all three |

| lefthook pre-commit (biome + typecheck-sync) | pass |

Type parity (the §8 risk 1 fixtures). One mart row is defined in both transport shapes:

- Data API: BIGINT and INTEGER as numbers; NUMERIC, DATE, TIMESTAMP, TIMESTAMPTZ and SUPER as strings; DOUBLE as the full double.

- node pg: BIGINT and NUMERIC as strings; DATE and TIMESTAMP as local Dates; TIMESTAMPTZ as an absolute Date; SUPER as JSON text.

The tests assert that:

- the reader turns the Gateway shape into exactly the pg shape;

- the pg fixture is what pg's own parsers return, so the fixture itself is pinned;

- a legacy-style mapper built from Aerie's superString / dateToDateString helpers gives identical records from either transport, and the shadow compare is clean;

- every type has accept and reject cases, and no reject reason echoes the value.

Other coverage:

- Reader: every rule above, with messages checked for the absence of a PII fixture value.

- Compare: redaction of values and keys, multiset mode, both classifiers and their edge cases, a throwing key function or log sink, and frozen pg records left unchanged.

- Gate: every global and override value, typos, and the DBT_TARGET precondition.

- Purge guard: bounds, and the "60 of 90 programs from one run" case.

Dry-run template (local only, no prod):

- With no env file: prints the missing vars, exit 1.

- With a scratch env pointing Redshift and the Gateway at 127.0.0.1:1: config loads before the app modules, the pg read fails as Error (ECONNREFUSED) (details withheld), exit 1.

Mercy round 1 (both findings fixed):

- The canonical encoding is now collision-free: every value at every depth is a type-tagged [tag, payload] array, object keys sit inside the payload, and the absent-field marker uses a tag no value produces. A test pins that marker-shaped data ({"$u":1}, {"$absent":1}, ["u"], nested too) never equals undefined, an absent field, a Date or a bigint.

- The dry-run's pg canary no longer has a LIMIT, so both sides see the same snapshot boundary. The template notes now tell each group copy to keep it that way.

Mercy round 2 (all three findings fixed):

- A key from a custom keyOf is never displayed. Examples show the group's keyFields values instead, and only when every key field is allowlisted, so shown data is always allowlisted data.

- describeA8Error trusts kit and Gateway errors by instanceof, not by the spoofable error.name. A name, error code or Zod path segment is shown only when it looks like an identifier. Spoofed-name regression tests are added.

- If the log sink throws, the check becomes degraded (never counted clean) and a best-effort line goes to the default sink.

Mercy round 3:

- Fixed: date, timestamp and timestamptz text must name a real calendar day (a UTC round-trip, so the TZ can't affect it). The pg parser would otherwise roll 2026-02-31 over into a different real date.

- Fixed (nit): SMALLINT/INTEGER/BIGINT values must be within Redshift's ranges, and BIGINT text must be canonical.

- Answered in-thread: a read capped by maxPages can't reach the reader. SurtrGatewayClient.listAll throws when has_more is still true after maxPages, and a new test pins that with the real client.

Throughput (synthetic): 100k rows × 40 columns parse in about 4.6s and compare in about 2s. The largest A8 source is expected to be a few hundred thousand rows, read hourly.

## Not covered

- No live Gateway run. No prod, no keys in this unit. The canary dry-run needs Keval's key and runs as X5. G1 (U05) remains the first live proof of SUPER/DATE/BIGINT parity against real marts (plan §8).

- The float rule is modelled, not yet observed live. REAL/DOUBLE are rounded to 6/15 significant digits, matching Redshift's text output at extra_float_digits=0. The first A8 mart with a float column should confirm it in its shadow run. If it's wrong, the per-field mismatch counts will name the column.

- No wiring, .env.example gate lines or package.json script. Those arrive with each group unit.

- describeA8Error duplicates PR 1531's helper. Fold the two together once PR 1531 merges.

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

Linear: AERIE-2617 (A8 unit U11; related AERIE-445)

## Summary

A8 group G4: the analytics worker's three expense reads can now come from Surtr Gateway parity marts instead of Redshift, behind a gate that defaults to today's behaviour.

- Split, no behaviour change. queryExpenseTransactions, queryExpenseMetadata and queryVendorClassifications now issue exported SQL and map through exported mappers:

- SQL: EXPENSE_TRANSACTIONS_SQL (built by expenseTransactionsUpdatedAtSql), EXPENSE_METADATA_SCHOOLS_SQL, EXPENSE_METADATA_ACCOUNTS_SQL, VENDOR_CLASSIFICATIONS_SQL. The text is byte-identical.

- Mappers: mapExpenseTransactionRows, mapExpenseMetadataRows, mapVendorClassificationRows.

- The updated-time read still falls back to the transaction-date read only when the query itself fails.

- Gate EXPENSES_READ=legacy|shadow|gateway (default legacy), with per-source overrides EXPENSES_READ_TRANSACTIONS and EXPENSES_READ_VENDORS. The rules come from the U02 kit: for example, an unrecognized override never selects gateway.

- New queries/expenses-gateway.ts reads aerie-expense-transaction and aerie-expense-vendor-classification through the kit.

- The specs type each column as the Surtr DDL stores it.

- Rows go through the kit's type-parity layer, then the unchanged legacy mappers.

- Metadata has no mart. It follows the transactions mode.

- In gateway mode, it is queryExpenseMetadata's two DISTINCTs evaluated over the same transactions snapshot, then passed to the legacy mapper:

- NULLs are excluded;

- DISTINCT is exact-string;

- sorting follows Redshift's UTF-8 byte order, with NULL names last.

- Those three semantics were probed read-only against Redshift.

- One transactions read per cycle serves both the transactions and the metadata. If that read fails, the metadata fails too; it never falls back to pg.

- Shadow publishes the pg reads and logs one a8_shadow_check line per source:

- transactions, compared by sourceRowId;

- vendors, compared by vendorName as a multiset, because Convex upserts vendors by name;

- metadata, compared as sets.

It uses the kit's source_advanced skew rule. vendor_name, memo and line_amount values are on no allowlist, so they read [redacted] in every example.

- Gateway failures are value-free. They reach refresh.ts's per-domain catch as an A8GatewayReadError built from describeA8Error, so neither the FAILED log line nor the DomainResult can carry a row value.

- Freshness bounds replace the kit's 6h default (see the evidence below):

- 36h for transactions. mart-education-quickbooks-refresh is daily. Over the last 60 days its median gap was 24.0h and every gap but one was at most 34.8h, so 36h is the daily cadence plus 12h. Only the one missed-days gap (71.8h, 08-07 to 08-10) would have been refused.

- 8 days for vendors. quickbooks-expense-ai-generation is weekly (Sundays 08:00 UTC, 3-15 minutes), so 8 days is the cadence plus one day. It refuses the two windows where a failed Sunday run was recovered by hand 1-3 days late (10.3 days at worst); that surfaces the upstream outage instead of treating last week's classifications as current.

- Refusing is safe: each expense domain fails on its own, and nothing is deleted.

- Floors: 10,000 transactions and 500 vendors, about 12% and 14% of today's counts. There is no purge, so no purge guard. A short transactions read would still overwrite the one metadata row with shorter dropdown lists.

- refresh.ts changes stay inside interlude block 8: one import, four lines to resolve the gate once per cycle, and three changed calls.

- Dry-run: sync/src/scripts/dry-run-expenses-shadow.ts runs the worker's own shadow path, read-only.

## Business Value

- Moves the expense dashboard off the worker's direct Redshift reads. Its transactions, vendor classifications and filter metadata can now come through the governed Surtr Gateway. This is one of the A8 steps toward retiring the EC2 worker's warehouse access (AERIE-445).

- Low-risk cutover. Every expense write is an upsert, so nothing is purged. Each domain fails on its own. The metadata needs no new mart: one read serves both it and the transactions.

- Parity is provable before switching. The shadow compares every row and redacts personal free text. A test pins the SQL the Surtr marts copy.

- Zero risk until it is switched on. The default stays legacy, and rollback is one env var.

## Manual Effort Estimate

About 10 hours of focused work by hand, without AI. Keval, please confirm or adjust this number. It covers:

- reading the kit and the two Surtr mart contracts;

- the split and the gate module;

- the metadata derivation, including probing Redshift's DISTINCT and ORDER BY semantics;

- measuring both upstream cadences for the freshness bounds;

- about 45 tests with dual-transport fixtures;

- the read-only pg proof and the dry-run.

## Testing / evidence

- Split and gate parity on real rows (read-only). A throwaway script ran the pre-split expenses.ts (from d6f595810) and the post-split one against Redshift, reporting counts and booleans only. Everything matched on the first attempt.

| | rows | old = new | old = mapRows(sql) |

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

| transactions | 80,709 | yes, including watermarkMode/watermarks | yes |

| vendor classifications | 3,479 | yes | yes |

| metadata (68 schools, 2 accounts) | — | yes | — |

- Metadata derived from the 80,709 transaction rows equals the legacy metadata: school order is exact, account-name order is exact, and the account set is the same.

- The gate in all three modes was run over the same real rows. The "Gateway" was an in-process fake serving them in Data API shape plus lineage columns, so no Gateway traffic was generated.

- legacy, shadow and gateway each published exactly the old transactions, vendors and metadata.

- Shadow logged clean for all three sources: 80,709, 3,479 and 70 matched keys.

- Source shape (counts only): source_row_id is unique (80,709 of 80,709) and vendor_name is unique today (3,479 of 3,479). There are no trailing-blank or NULL-name edge cases in today's data.

- Redshift semantics probed read-only with literal-only queries, no table reads:

- VARCHAR DISTINCT keeps 'a' and 'a ' apart;

- ORDER BY is UTF-8 byte order ('' < '1' < 'B' < 'Z' < '_x' < 'a' < 'á' < '€' < '😀');

- NULLs sort last.

- Upstream cadence, from staging_other.pipeline_runs_prod over the last 60 days, read-only:

- mart-education-quickbooks-refresh: 68 successes, median gap 24.0h, gaps at most 34.8h except one of 71.8h.

- quickbooks-expense-ai-generation: weekly. Today's vendor snapshot is from 09-27 08:03.

- New unit tests:

- queries/expenses-gateway.test.ts (29 tests):

- fixtures in both transport shapes: NUMERIC as text, the ::text dates, '' versus NULL;

- fail-closed cases: NUMERIC delivered as a JSON number, an absent column, the legacy all-or-nothing parse, the freshness bounds at 35h/37h and 7.9/8.1 days, and the floors;

- metadata derivation: DISTINCT, NULL exclusion, byte order including a character where JS UTF-16 order differs, trailing blanks, and account ties;

- every mode and override: one transactions read shared by transactions and metadata; gateway failures that are value-free and never fall back; shadow degraded on Gateway failure;

- compare: field mismatches with vendor, memo and amount redacted, source_advanced, duplicate and one-sided keys, the vendor multiset, and the metadata sets.

- expenses-gate-refresh.test.ts (4 tests) runs a whole runRefreshCycle, using a stub orchestrator that runs the interlude:

- gateway mode publishes the Gateway snapshot and never reads the expense tables;

- a transactions-mart failure fails only transactions and metadata, with a value-free DomainResult error;

- shadow publishes exactly the pg reads and logs three clean lines;

- unset stays legacy.

- queries/expenses.test.ts (+4):

- pins the SQL text the Surtr marts copy;

- checks that each function equals mapRows applied to its exported SQL.

- Checks:

- sync: tsc --noEmit clean; vitest run --maxWorkers=2: 88 files, 1558 tests passed.

- pnpm lint (boundaries, convex paths, read bounds, test architecture, knowledge, biome) is clean. Its two warnings are pre-existing ones in chat/skill/forge-api/scripts/sindri.mjs.

- The chat suite wasn't run: this PR changes no chat code.

- Live Gateway dry-run: not run. The marts aren't deployed yet: AI-Builder-Team/Surtr#2088 is open and its DDL isn't applied. The aerie-a8 Gateway sources aren't registered or granted yet either (U04). Once they are, run cd sync && pnpm exec tsx src/scripts/dry-run-expenses-shadow.ts with the A8 key (Keval's step X5). With no key set, the script exits 1 and names the missing variable (checked).

## Stack

- Base: #1566 (the U02 read-gate kit, AERIE-2612). If #1566 merges first, this PR is rebased onto main and retargeted.

- AI-Builder-Team/Surtr#2088 (U07, SURTR-1539): the mart-aerie-expenses-refresh runner plus aerie_expense_transaction and aerie_expense_vendor_classification. The column names, types, lineage columns and mart_row_id here match those DDLs. The pinned SQL test here matches that PR's test_sql_contracts.py.

- No Convex change and no deploy-order constraint.

## Not covered

- The live Gateway dry-run and the G4 shadow window. Both wait for U07 to be deployed, U04 to be seeded and the key to be minted.

- A copy that lags its source reads as mismatch, not skipped. The kit's source_advanced rule covers a republish *between* the pg read and the Gateway read (the same rule as G1's directory). It doesn't cover the reverse: the upstream republishes and the A8 runner hasn't copied it yet. That window is normally a minute or two after each daily or weekly upstream run, so an hourly cycle rarely lands in it. If the runner is broken, the mismatch persists until the 36h / 8-day bound turns it into degraded.

- Transactions category freshness. It comes from the vendor classification joined at the mart's refresh, but the mart's lineage is the transactions run. If the runner fails on the weekly vendor trigger, categories lag until the next daily transactions republish re-joins them (at most about a day). The freshness bound can't see this; shadow shows it as category mismatches.

- No gateway-mode env precheck in the worker scheduler (the A5 pattern). A missing key fails each expense read with the client's value-free error.

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

## Summary

- AERIE-2341: remove the capacity-outlook portfolioStatus OpenAPI regex that rejected malformed filters before the existing handler could explain the allowed values.

- Retain a bounded string schema and the handler's status validation. No valid filter or capacity calculation changes.

- Add an authenticated HTTP regression test for unknown tokens, a trailing comma, and embedded whitespace; each receives a 400 with portfolioStatus and the allowed statuses.

## Validation

- Focused convex/publicApi/v2/admissions.test.ts invalid-filter test — passed.

- pnpm --dir chat typecheck — passed.

- Biome on changed files and git diff --check — passed.

- Pre-commit Convex paths, Biome, and chat typecheck — passed.

Closes AERIE-2341.

Linear: AERIE-2679 (fixes a blocking Mercy finding on the release PR, against code from AERIE-2677; related AERIE-445)

## Summary

The finding. In ADMISSIONS_COMMUNITY_FUNNEL_READ=gateway, the legacy mapper drops a Gateway row with a null deal_id or one its schema rejects. Gateway mode only logged the count and then ran the population check on the reduced count. One dropped row in 5,403 is far inside the 5% allowance, so a partial snapshot would publish as a success.

The rule this PR applies to every Gateway read: if the mapper returns fewer records than the Gateway rows it was given, the read is refused.

- gateway mode: the read throws before the population check and before any write. The last published data stays. The message carries counts only, no row values.

- shadow mode: the same refusal makes the check degraded, never clean, so the shadow window shows it before gateway would refuse it. This matters because the mart is a verbatim copy: pg drops the same row, so the keyed compare alone would have called the cycle clean.

- Legacy (pg) paths are unchanged. Production publishes exactly what it publishes today.

What changed.

- Kit, sync/src/analytics/a8/gateway-table-reader.ts: assertNoA8RowsDropped(source, { rowsRead, recordsMapped }, reason). It throws the reader's own A8GatewayReadError, which is on the kit's value-free list, so the message reaches the logs as written.

- Community funnel, admissions-community-funnel-gateway.ts:

- readCommunityConversionFromGateway refuses any dropped row.

- Removed: the droppedRows and sourceRows fields (always 0 and N now), the warn-and-publish branch, and the second floor on the mapped count (dead once any drop throws).

- The module doc and the floor comment now describe the new behaviour.

- Expense vendor classifications, expenses-gateway.ts: the same refusal. It replaces the second floor on the mapped count, which only refused a read once it fell below 500 of about 3,479 rows.

- XO contractor identity, xo-contractor-identity-gateway.ts: a Gateway row whose aliases all trim away now fails the read. Before, it was filtered out and the rest published.

Every Gateway reader under sync/src, and what I found.

| Lane | Reader (Gateway source) | Can it drop a Gateway row? | Change |

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

| A1 | site-operational-metadata-gateway.ts | No. z.array(...).safeParse, then throw. Shadow only, never published. | None |

| A2 | schools-data-sheet-gateway.ts | No. Any invalid row, duplicate cell or partial grid refuses the snapshot; every record is published. | None |

| A3 | school-calendar-gateway.ts | No. Any invalid row or duplicate slug refuses the read; every row is sent. Convex skips a row whose site slug Aerie doesn't have, and the worker records that as an error on the run. | None |

| A4 | school-source-directories-gateway.ts (QuickBooks, SIS, Finalsite) | No. Strict parse; the snapshot builders map one to one and throw. | None |

| A5 | camps-full-gateway.ts (7 entities), camps-gateway.ts (parity checks) | No. Strict parse; loadCampSourceSnapshotViaGateway refuses the whole snapshot on a blank or duplicated id, mixed lineage or a dangling reference. | None |

| A6 | rebl3-sites-gateway.ts | No. Any invalid row, repeated site or mixed lineage refuses the snapshot. | None |

| A7 | matterport-discovery-shadow.ts (xref) | No. Strict; shadow only, never published. | None |

| F1 | xo-contractor-identity-gateway.ts | Yes: filtered out rows with no usable alias. | Fixed |

| F2 | xo-contractor-package-gateway.ts | No. Strict parse, one record per row. | None |

| A8 G1 | admissions-reference-gateway.ts (programs, Program directory) | No. mapProgramRows and mapHubspotProgramRows throw on any bad row. | None |

| A8 G2 | admissions-community-funnel-gateway.ts | Yes: null deal_id or schema-rejected. | Fixed |

| A8 G3 | community-deposits-gateway.ts | No. mapCommunityDepositRows maps every row or refuses the read. | None |

| A8 G4 | expenses-gateway.ts, transactions | No. Strict array parse; a conflicting repeated key is refused. An exact repeated row is published twice, as legacy does. | None |

| A8 G4 | expenses-gateway.ts, vendor classifications | Yes: safeParseRows skips a row it cannot parse. | Fixed |

| A8 G4 | expenses-gateway.ts, metadata | Not a row mapper. It derives two DISTINCT lists and leaves out NULLs exactly as the legacy SQL's WHERE ... IS NOT NULL does. | None |

## Business Value

- Unblocks the Aerie production release. Mercy will not approve the release while this finding is open on main.

- A snapshot that lost rows can no longer be published as a clean success on the admissions funnels, the expense vendor categories or the contractor identity index. Each of those would otherwise go quietly stale or short for the affected families, vendors or contractors.

- The shadow window now means what it says. A source that would be refused in gateway mode can no longer pass its shadow window clean.

## Manual Effort Estimate

About 4 hours of focused work by hand, without AI. Keval, please confirm or adjust this number. It covers:

- reading every Gateway reader and its publish path (13 reader files, plus the refresh code each one feeds) for drops;

- the kit helper and the three fixes;

- reworking the tests that pinned the old drop-and-publish behaviour, plus 18 net new tests.

## Testing

- a8/gateway-table-reader.test.ts (+4): equal counts pass; one dropped row of 5,403 is refused with the exact count-only message; the error carries its source; more records than rows is refused too.

- queries/admissions-community-funnel-gateway.test.ts (55 to 61):

- reader: one null-deal_id row and one schema-rejected row each refuse a 1,201-row read, with the exact message and no PII; 250 dropped rows are refused the same way; a clean read returns one record per row;

- gateway mode: one dropped row fails the read with A8GatewayReadError, the population preview is never called, nothing is logged as read, and pg is never called;

- shadow mode: pg and the Gateway both carry the same null-deal row. Legacy publishes as today, and the check is degraded, countsAsClean: false, with the count-only reason;

- end to end through refreshCommunityFunnel: a dropped Gateway row fails the refresh with no Convex call at all; legacy mode still publishes the pg read minus the row its mapper drops.

- Two tests that pinned the old behaviour (drop, warn, publish; floor after drops) were rewritten.

- queries/expenses-gateway.test.ts (31 to 34): the legacy mapper still drops a bad pg row; the Gateway reader refuses the same row; gateway mode fails only the vendors read with a value-free message while transactions still publish; shadow mode is degraded for vendors and clean for the other two sources.

- tests/redshift/xo-contractor-identity-gateway.test.ts (16 to 19): three shapes of an alias-less row each refuse the read with a count-only message and no name; blank entries between delimiters are still trimmed without refusing the row.

- tests/analytics/xo-contractor-identity-refresh.test.ts (+2): through the real Gateway reader. gateway mode returns an error and sends nothing; shadow mode publishes from pg and reports check failed.

- Checks:

- sync: tsc --noEmit clean; vitest run --maxWorkers=2: 113 files, 2515 tests passed.

- pnpm lint (boundaries, convex paths, read bounds, test architecture, knowledge, biome) is clean. Its two warnings are pre-existing ones in chat/skill/forge-api/scripts/sindri.mjs.

- The chat suite wasn't run: this PR changes no chat code.

## Does this refuse anything on today's data?

- Community funnel: no. The production mart was checked today (by Keval's session, not re-queried here): 5,403 rows, 0 null deal_id, 5,403 distinct.

- Vendor classifications: no, on the last evidence I have. The read-only check for the expenses gate on 2026-09-29 found 3,479 rows and none with a NULL name.

- XO contractor identity: not verified against production. The mart's SQL only emits aliases of 6 or more characters, so a row with none needs an all-whitespace contractor name. XO_CONTRACTOR_READ defaults to pg, and shadow mode would report such a row as a failed check before any flip.

- All three gates default to the legacy read, so nothing changes in production until a gate is set.

## Not covered

- The legacy mappers still drop rows on the pg path. That is deliberate (production behaviour is unchanged), but it means pg and the Gateway now differ on a bad row: pg publishes without it, the Gateway refuses.

- A persistent bad row in a mart keeps its shadow line degraded every cycle until Surtr fixes the row, since the Gateway read is refused before the compare runs.

- The held A8 PRs are not on main and were not audited: the per-program readers, the admissions pipeline gate, marketing and SIS enrollment. The pipeline refresh already counts droppedNoColumn and droppedNoProgram, so its gate needs this same rule when it lands.

- No live Gateway run. Gateway mode cannot be exercised end to end until the Surtr marts and the key grant are live.

- safeParseRows still logs a Zod message for the first three rows it skips. Unchanged here.

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

## Demo


## Summary

- Adds a nullable Task.created_at (Unix seconds) via an idempotent migration. Existing rows stay null, with no backfill. Both task-creation paths set it to now().

- Task payloads now include createdAt, activeAgentSeconds, runningAgentTurns, and agentRuntimeMeasuredAt. Agent runtime is the sum of TaskTraceTurn durations. A turn that is still open counts up to now, or up to completed_at if the task is complete.

- Task detail header: the agent label row shows, right-aligned, a clock icon with the wall-clock duration and a bot icon with the agent runtime. Tooltips and accessible names give the full labels (Took 2h 14m or Running 45m, and Agent runtime 38m). For in-progress tasks these refresh every 30s, and the count includes turns that are still running.

- Task context sidebar: shows Created 3 days ago in a <time> element under the linked item/repository summary. Hovering shows the exact local date and time.

- Legacy tasks (createdAt null) show none of these values.

## Linear

https://linear.app/builder-team/issue/AI-954/show-task-creation-time-and-time-taken-on-task-detail

## Tests

- cargo test --lib: 340 passed. New tests cover the idempotent migration, legacy rows staying null, created_at on new tasks, and agent-runtime aggregation with open turns.

- node --test scripts/test-task-timing.mjs (new, pnpm test:task-timing): duration and relative-time formatting, completed/running/legacy summaries, and the UI hiding values for legacy tasks.

- node --test for task-workspace, workflow, task-trace, pr-review, and github-reviews: all pass.

- pnpm theme:check passes. tsc --noEmit reports only an existing, unrelated missing @tauri-apps/plugin-notification type in a worktree that has no local install.

## Summary

- Bump Shipyard to 0.6.11.

- Add the approved public release notes.

## Business Value

- Delivers the approved branch workflow, Pi conversation controls, repository history pagination, task timing visibility, and Pi image diagnostics.

## Implementation Effort

- Low: metadata-only release change; CI performs the signed build and publication.

## Test Plan

- [x] pnpm test:release

- [x] git diff --check

- [x] Verified the diff contains only package.json and releases/0.6.11.md.

Linear: AERIE-2625 (A8 unit U21; related AERIE-445, blocked by AERIE-2621)

## Summary

A8 group G2, per-program reads, part 3: the shadow compare for ADMISSIONS_PROGRAM_DETAIL_READ. shadow is now released for this gate. gateway stays behind the existing ADMISSIONS_PROGRAM_DETAIL_READ_NONPROD_OPT_IN convention.

- Shadow publishes exactly legacy (queries/admissions-program-shadow.ts).

- Each shadowed seam member runs the legacy query's SQL and binds once, through the legacy mapper, and returns (or throws) that result. It also keeps what it read.

- Nothing is read from the Gateway during the cycle.

- Checks run once the cycle has published. The orchestrator calls finishShadowChecks after the retention sweeps, before the non-admissions interlude. It never throws.

- Each shadowed mart is read once, through gateway mode's own row feed. createAdmissionsProgramGatewayRows is the Gateway path up to the mapper, extracted from U17's readers, so shadow checks exactly what gateway would publish.

- Results land as one a8_shadow_check line per source per cycle, via the kit's runA8ShadowCheck / logA8ShadowCheck.

- Compared per program, before any order-dependent step. programKey is the value the legacy predicate bound.

| Source | Records compared | Grouped by |

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

| Q5 pipeline students | mapped, without the #n recordKey suffix | recordKey base, as a multiset |

| Q6 enrollment cohort, Q7 deposits, Q8 transfers | the mapper's own parse, before pickBetterCohortRow | natural key, as a multiset |

| Q3 coming-year projections | the mapper's own parse, before first-row-per-year | year, code, version, as a multiset |

| Q4 community metrics, Q2 projections, app conversion | mapped | natural key (duplicates never clean) |

A failed call is recorded as a readFailed record: alone if its read or parse failed, beside the parsed rows if only the mapper refused them. Failing alike on both sides, on the same rows, matches; anything else does not.

- Derived outputs that publish are also compared, computed with the refresh's own functions from each side's mapped reads:

- derived:enrollment-snapshot;

- derived:pipeline-funnel (year-aware);

- derived:projection (enrollment and coming-year).

This proves the TS derivations get identical inputs. Programs whose inputs failed are skipped and counted.

- EduCRM skew rule. On a mismatch, only the calls whose records differ are re-read from pg, once, after the Gateway read. A match then is source_advanced, which counts as clean. Derived outputs recompute from those re-reads without further queries.

- PII. Values appear only for non-PII fields: program keys, cohorts, stages, school years and aggregate counts. Names, emails, ids and dates are redacted. Lines carry field names, value types, counts and lineage.

- Minimal core-file edits, no behaviour change:

- educrm.ts exports the four strict parsers its mappers already ran inline.

- enrollment.ts moves the cohort/deposit/transfer assembly into assembleEnrollmentCohortStudents (same order and errors) and exports deriveYearAwarePipelineFunnel.

- The cycle resolver moves to the shadow module, because it needs both transports.

- Dry-run: sync/src/scripts/dry-run-admissions-program-detail-shadow.ts. It loads dotenv first, then await import(). It runs every per-program read the refresh makes, for every program, plus app conversion, in shadow, then the checks. The refresh's Convex writes are not run.

## Business Value

- Starts the clean-shadow window for A8's longest chain (U03 → U08 → U14 → U17 → U21). That window is the gate before the worker's roughly 450 per-cycle EduCRM queries for these sources collapse into 8 Gateway reads (AERIE-445). The sources include enrollmentProjections, which feeds public API v2.

- Proves parity where it can actually break. Comparing before the order-dependent dedupes rules out false alarms from ties that legacy already leaves unspecified. The derived compares show the published snapshots, funnel and projections would be byte-identical.

- Zero publish risk. shadow publishes exactly the legacy reads (tested, and mutation-checked). The default stays legacy. gateway still refuses to start in production.

## Manual Effort Estimate

About 14 hours of focused work by hand, without AI. Keval, please confirm or adjust. It covers:

- reading the kit, U14's seam and U17's readers;

- deciding, per source, which step is order-dependent and what to compare before it;

- the per-call skew re-read and the derived-output compares;

- extracting the Gateway row feed without changing gateway mode;

- 13 new tests on a two-transport fixture;

- the dry-run;

- the read-only proof on real rows.

## Testing / evidence

- Read-only shadow proof on real rows. A throwaway script, not committed. default_transaction_read_only=on was set and checked. Output is counts and field names only: no row values, no program names.

- It runs this PR's shadow path end to end: resolveAdmissionsProgramSources in shadow, every per-program read for all 90 programs the worker refreshes, then app conversion, then finishShadowChecks.

- The Gateway side is a fake client whose marts are Surtr #2090/#2092/#2094's candidate SQL, run read-only against EduCRM on first read (after the pg reads, as in a real cycle). Values are reshaped as the Data API returns them (TIMESTAMP/DATE as text, BIGINT as numbers) and rows are reversed.

| line | outcome | pg / Gateway records | keys matched | re-read calls |

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

| aerie-admissions-pipeline-student (Q5) | clean | 33,585 / 33,585 | 33,585 | 0 of 90 |

| aerie-admissions-community-metric (Q4) | clean | 900 / 900 | 900 | 0 of 90 |

| aerie-admissions-enrollment-cohort (Q6, pre-dedupe) | clean | 9,238 / 9,238 | 9,238 | 0 of 90 |

| aerie-admissions-pipeline-deposit (Q7, pre-dedupe) | clean | 113 / 113 | 113 | 0 of 90 |

| aerie-admissions-enrollment-transfer (Q8, pre-dedupe) | clean | 36 / 36 | 36 | 0 of 90 |

| aerie-admissions-program-projection (Q2) | clean | 180 / 180 | 180 | 0 of 90 |

| aerie-admissions-coming-year-projection (Q3, pre-first-row) | clean | 180 / 180 | 180 | 0 of 90 |

| aerie-admissions-app-conversion | clean | 4,295 / 4,295 | 4,295 | 0 of 1 |

| derived:enrollment-snapshot | clean | 339 / 339 | 339 | 90 programs, 0 skipped |

| derived:pipeline-funnel | clean | 1,101 / 1,101 | 1,101 | 90 programs, 0 skipped |

| derived:projection | clean | 360 / 360 | 360 | 90 programs, 0 skipped |

There were 0 failed legacy reads, 8 mart reads (one per mart) and no mismatched, one-sided or duplicate keys. The pg reads took 290s, and the whole run 416s.

- New tests: queries/admissions-program-shadow.test.ts (15).

- The fixture is written once as pg rows and converted to Data API rows by each mart's column types. The real reader, parity layer and slicer then run.

- Publish invariance:

- each shadow member issues the legacy query once and returns or throws exactly its result;

- a whole cycle through refreshEnrollmentPipelineDetailed + refreshAppConversionFacts makes identical results, queries and Convex writes to legacy, while the Gateway serves different rows and one mart fails;

- the checks make no Convex write.

- One line per source: a matching cycle gives 8 source lines plus 3 derived lines, all clean. Each mart is read once and nothing is re-read. A per-source override shadows one source, and a derived output needs all its inputs.

- Compare granularity:

- Q5 order is ignored but a lost duplicate is not;

- dropping Q6's losing duplicate or Q3's second row per year is a mismatch even though the published output is identical.

- Skew rule:

- a republish between reads gives source_advanced for the source and its derived snapshot, re-reading only the differing programs;

- a pg read that failed during the cycle gives source_advanced;

- a Gateway-only difference stays mismatch.

- Failures and PII:

- a mapper failure on both sides matches only on the same rows, and its program is skipped for derived outputs;

- a check that throws outside the kit logs its own degraded line, and later checks still run;

- lines hold field names and non-PII keys, and no fixture PII value appears anywhere.

- Mutation-checked: comparing Q6 after dedupe, re-reading every call, or publishing from other rows each fails the suite.

- Updated tests:

- admissions-program-gateway.test.ts: shadow is released with no opt-in; gateway (alone or mixed with shadow) is still refused.

- refresh-orchestrator.test.ts (+1): the checks run once, with the cycle's programs, after the retention sweeps.

- Checks:

- sync tsc --noEmit clean;

- vitest run --maxWorkers=2: 90 files, 1,591 tests passed;

- pnpm lint clean apart from 2 existing warnings in chat/skill/forge-api.

The full chat suite was not run (no chat changes).

- Live Gateway dry-run: not run. The U12/U13 marts aren't deployed, and the slugs aren't registered or granted (U04). The dry-run script is ready for Keval's X5 step.

## Stack

- Base: #1578 (U17, AERIE-2621), which sits on #1576 (U14) and #1566 (kit). If #1578 merges first, this is rebased onto its base and retargeted.

- Mart contracts: Surtr #2090 (Q5/Q4), #2092 (Q6-Q8), #2094 (Q2/Q3/app conversion).

- Heads-up needed: this touches Vladimir's core files (queries/educrm.ts, queries/enrollment.ts, admissions/refresh-orchestrator.ts). The edits are extractions with no behaviour change. Keval to give Vladimir a heads-up before merge.

## Not covered

- gateway release: it stays behind the non-production opt-in until a clean shadow window. The plan asks for 48 cycles here, because enrollmentProjections feeds API v2.

- Published cohort-student rows and pipeline-student #n keys are not compared after dedupe. Legacy is itself nondeterministic on full ties there, so they're compared before it, per the plan.

- The Gateway-side mapper runs as the legacy one does, so its existing warnings (for example the Q6 duplicate-collapse line) print once per side in shadow.

- Q1 (pipeline_agg) isn't in the seam; U01 retires it.

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

Linear: AERIE-2626 (A8 unit U22; related AERIE-445)

## Summary

This is A8 group G3, marketing. It gates the four per-program EduCRM marketing reads behind ADMISSIONS_MARKETING_READ, using U14's seam pattern:

- D1 event aggregates;

- D2 event contacts;

- D3 shadow-day events;

- D4 weekly deposits.

It adds Gateway readers for all four, and a shadow compare that follows the EduCRM skew rule. The default is today's behaviour.

- Split, no behaviour change. Each of the four reads is now a SQL constant plus a mapper. The legacy query calls the mapper unchanged.

- SQL constants, byte-identical to the old text: MARKETING_EVENT_AGG_SQL, MARKETING_EVENT_CONTACTS_SQL, SHADOW_EVENT_AGG_SQL, DEPOSIT_AGG_SQL.

- Mappers: mapMarketingEventAggRows, mapMarketingEventContactRows, mapShadowEventAggRows, mapDepositAggRows.

- D1 and D2 keep their strict throws. D3 and D4 keep safeParseRows.

- Seam AdmissionsMarketingSources (queries/admissions-marketing-sources.ts). Each member has the legacy query's exact signature. PG_ADMISSIONS_MARKETING_SOURCES is the default everywhere.

- The core-file edits are threading only.

- admissions/per-program-refresh.ts: an optional marketingSources argument, passed to the four helpers. That is 4 call-site lines plus the argument.

- admissions/marketing-events-refresh.ts: one defaulted sources parameter per helper.

- admissions/refresh-orchestrator.ts: one injectable dep, resolved once per cycle. Its runShadowChecks() runs right after the per-program loop.

- The D1→D2 identity-manifest dependency is untouched: D2 still runs only after that program's D1 publishes.

- Gate ADMISSIONS_MARKETING_READ=legacy|shadow|gateway (default legacy), on the U02 kit. The overrides are …_MARKETING_EVENT, …_MARKETING_EVENT_CONTACT, …_SHADOW_DAY_EVENT and …_WEEKLY_DEPOSIT. If D1 and D2 would publish from different transports, the gate warns.

- Gateway mode (queries/admissions-marketing-gateway.ts). Sources are typed exactly as Surtr #2095 and #2099 store them.

- Each mart is read once per cycle and sliced by filter_program_name, with the exact legacy predicate.

- Each slice is re-sorted to the legacy ORDER BY before the unchanged mapper. Order decides what gets published:

- D2's (eventId, contactId) dedupe keeps the last row it sees;

- D4's Convex upsert is keyed by weekLabel, and 9,450 (program, week label) pairs collapse more than one SQL row today.

- SUPER strings sort by string value, as Redshift sorts them. This was checked on 146,830 rows: 0 order violations by value, 6 by JSON text.

- A failed read fails that source for every program this cycle.

- Floors are about a fifth of today's counts. The freshness bound is 24h, as for G2.

- Shadow mode publishes the legacy reads untouched and records each program's mapped output. After the loop it logs one a8_shadow_check line per source for the whole cycle, reading each mart once:

- D1 and D3 by (program, event name). Event names are free text, so they stay redacted.

- D2 as a multiset before the dedupe, with no allowlist: every value and key is [redacted]. The line carries field names, types and counts only.

- D4 at the full SQL grain (program, week label, week start, week end, school year), before the Convex collapse.

- EduCRM skew rule: on a mismatch, only the programs whose records differ are re-read from pg, once. If they now match, the result is source_advanced.

- A program whose legacy read failed is left out of the compare. If no program was read, the result is degraded, never clean.

- Dry-run sync/src/scripts/dry-run-admissions-marketing-shadow.ts. It loads dotenv first, then uses await import(). It runs the worker's shadow path for every listed program and exits 0 only if all four sources are clean.

## Business Value

- About 360 Redshift queries per cycle become 4 reads. Once cut over, 4 marketing queries × 90 programs collapse to one paged Gateway read per mart. That moves G3 off the worker's direct EduCRM access (AERIE-445).

- The cutover is provable and PII-safe. Shadow mode compares what would be published, before the order-dependent steps, and tolerates EduCRM's 30-minute republishes. The contact data carries children's dates of birth, and none of it reaches a log line.

- No behaviour change or prod risk now. The default path runs the same SQL text, the same mappers and the same Convex writes, as proved below.

## Manual Effort Estimate

About 14 hours of focused work by hand, without AI. Keval, please confirm or adjust. It covers:

- tracing the marketing refresh path and the D1/D2 manifest coupling;

- reading the U15 and U18 mart contracts;

- empirically checking Redshift's SUPER and ORDER BY semantics;

- the split, seam, gate, readers and cycle-level shadow compare;

- about 30 tests;

- the read-only proofs.

## Testing / evidence

- Read-only proof on real rows, for all 90 programs queryPrograms lists. The script was a throwaway and is not committed. It printed counts and hash prefixes only: no row values or program names. It compares three paths:

- OLD: this branch's base educrm.ts, a temporary copy.

- PG: the new split functions.

- GW: the new Gateway path. It was fed by the U15/U18 procedures' candidate SQL, run read-only against EduCRM through a raw-text pg client and reshaped into Data API values (BIGINT as numbers, DATE/TIMESTAMP as text, booleans), then passed through the kit's reader, the slicing, the re-sort and the mappers.

- The EduCRM run did not change during any source's reads.

| check | D1 events | D2 contacts | D3 shadow days | D4 deposits |

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

| mart SQL rows (NULL program dropped) | 1,041 (0) | 146,830 (0) | 75 (0) | 56,700 (0) |

| SQL text, OLD vs new constant | identical | identical | identical | identical |

| OLD = PG, per program | 90/90 | 90/90 as a multiset; 86/90 in order (see note) | 90/90 | 90/90 |

| OLD = GW, identical including order | 84/90 | 40/90 | 87/90 | 90/90 |

| the rest: same multiset, and the ORDER BY key sequence is identical, so they differ only among ties | 6 | 50 | 3 | 0 |

| what publishes (D2 after its dedupe; D4 after the Convex upsert by weekLabel) | n/a | 90/90 identical | n/a | 90/90 identical |

| whole-cycle shadow check (GW fed as above) | clean, 866 keys | clean, 63,931 records / 30,118 keys (22,648 repeated pairs, compared as a multiset) | clean, 73 | clean, 56,700 |

- Note on D2's 86/90. Redshift returns rows that tie on D2's ORDER BY event_id, funnel_state_code, last_name in a different order from run to run. So two back-to-back runs of the identical SQL can differ in order only, which is why the multiset result is 90/90. The ties never decide what publishes: 0 (event, contact) groups have a dedupe winner that ties on the full ORDER BY with a differing row.

- SUPER ordering, checked separately. All 146,830 D2 source rows were ordered by Redshift. Comparing adjacent rows with the reader's comparator found 0 violations. Comparing them as JSON text found 6.

- New tests (about 30):

- queries/admissions-marketing-gateway.test.ts (21):

- gate modes and overrides;

- the mixed D1/D2 transport warning;

- read-once, and exact slicing, including a trailing blank and a NULL key;

- D2 type parity against the pg shape;

- D2's strict throw kept;

- D1 and D4 legacy order;

- the D2 SUPER and D4 NULL-ordering comparators;

- failure fan-out without a retry.

- Shadow tests in the same file:

- publish-invariance: it returns the legacy object untouched and reads no Gateway in the loop;

- D3 clean by (program, event);

- the skew rule re-reads only the differing program, giving source_advanced;

- a persistent mismatch;

- failed legacy reads give degraded;

- D2 multiset with full redaction: no email, program name or id in the result;

- D4 at full grain with allowlisted examples.

- queries/educrm.test.ts (+4): each split read runs its constant with [programName] and returns its mapper's output.

- admissions/marketing-events-refresh.test.ts (+2), per-program-refresh.test.ts (+1), refresh-orchestrator.test.ts (+1): the sources are threaded, and the shadow checks run right after the loop and change no result.

- Checks:

- sync tsc --noEmit is clean.

- vitest run --maxWorkers=2: 89 files, 1,591 tests passed.

- pnpm lint (biome) is clean.

- The full chat suite was not run, because nothing in chat changed.

- Live Gateway dry-run: not run. U15/U18's marts aren't deployed, and the aerie-a8 sources aren't registered or granted.

## Stack

- Base: #1576 (U14 seam, AERIE-2619), which sits on #1566 (U02 kit). If those merge first, this is rebased and retargeted.

- AI-Builder-Team/Surtr#2095 (U15, D1/D2 marts) and AI-Builder-Team/Surtr#2099 (U18, D3/D4 marts), both open. Column names, types, lifted keys and lineage match their DDL, and their candidate SQL fed the evidence above.

- Touches admissions/per-program-refresh.ts, where collision is MEDIUM (U21 and Vladimir), with threading-only edits.

## Not covered

- A live Gateway run. The marts aren't deployed, and the aerie-a8 sources aren't registered or granted (U04, key).

- Mart refresh lag. The skew rule re-reads pg, so it covers pg being *older* than the mart. If EduCRM republishes mid-loop and the mart hasn't refreshed yet when the check runs, the programs read after the republish can show a mismatch. The line carries the mart's source_run_id and source_published_at to diagnose this. Judge the shadow window on a run of cycles, not a single line.

- D2 is the largest read, about 147K rows. In gateway and shadow modes the worker holds one D2 snapshot, plus the recorded per-program outputs in shadow mode, for the cycle.

- Out of scope here:

- D5 DOP weekly (U01 retires it);

- the AERIE-2353 behaviour, which is kept for parity;

- the G2 per-program shadow (U21);

- no Convex changes.

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

## Summary

Add dated backup site need periods for each Site. Each period records need dates, progress (Requested, Searching, Signing Contract, or Acquired), and the selected backup location and contract term when acquired. Dated DSS questions use these periods; the existing operating confirmation continues to answer where a Site is actually operating now.

## Screenshots

<img width="1288" height="883" alt="Screenshot 2026-10-01 at 4 21 03 PM" src="https://github.com/user-attachments/assets/81331a05-224e-492b-b2e4-7188828ebc3f" />

## Changes

- Add the need period schema, validation, Convex mutations, read model, and shared contract. Need periods can have gaps or an open end, but cannot overlap for one Site.

- Add period editing and status controls to the Site backup card, with guidance when today's plan and the confirmed operating location differ. Clarify the operating control in Backup Sites administration.

- Include need periods in the existing per-Site public API, agent, and Rhodes MCP read paths, and update the DSS instructions for dated questions and operating handoffs.

- Add focused tests for the period rules, UI, public API, agent, and Rhodes reads.

## Design decisions

- Need dates describe planned coverage; contract dates describe a location's agreement; the operating checkbox confirms actual current operation. These remain distinct so a planned handoff does not silently change the operating answer.

- Existing backup records continue to work without a backfill. This PR does not add an API write endpoint or a portfolio-wide aggregate.

## Business value

Teams can see and report which upcoming stretches have secured backup space and which still need work, including a return to an earlier location.

## Estimated manual effort

Approximately 3–4 focused engineering days without AI.

## Test plan

- [x] Focused Convex tests: 199 passed across four files.

- [x] Shared contract tests: 4 passed.

- [x] Rhodes worker backup site tests: 9 passed.

- [x] UI and public API tests passed during implementation.

- [x] Chat typecheck and pre-commit checks passed.

- [ ] Manual: add multiple periods with a gap and an open end; verify overlap rejection, acquired location and term selection, operating handoff guidance, and dated DSS answers.

## Business Value

Enable financial mapping repairs to rebuild from an accepted immutable QuickBooks snapshot without repeating the roughly 45-minute extraction of 68 companies. Live validation measured Core at 4m 9s and the successful financial report stage at 2m 26s. Today's full elapsed validation was 23m 11s because an unrelated scheduled HubSpot admissions transaction blocked competing financial writers and the first report attempt timed out.

## Change

Enable quickbooks-core-tables.on_demand_enabled and remove only Core from the application query layer's matching legacy hold. Explicit production registry metadata already enables the live route. Empty runner parameters, the disabled schedule, raw-success subscriptions, run records, runner/image, IAM, timeouts and financial contract checks are preserved.

The candidate was deployed before merge only to the isolated Core production stack and its required registry stack, each exclusively with [1/1]; both reached UPDATE_COMPLETE. Registry parity confirms the other 170 pipelines' controls are unchanged. No other pipeline stack was deployed.

## Validation

- All required CI checks, all 15 existing on-demand control tests, scoped Biome and whitespace checks pass at c0ca0a073ecbf50a11e26b955c7b29373d6bcd2c.

- Supported Core ON_DEMAND execution core-fast-financial-validation-20261002-105212 succeeded, using {} parameters and accepted raw run 6cad5602-4634-4681-9b31-d926b29b33d4; new Core generation is fe6f4160-39f5-4116-b2cd-4b9b052a9e65:posting.

- Core success automatically triggered the financial mart. That first mart confirmed cancellation after a 520-second lock wait. After HubSpot and both queued School Performance writers succeeded and fresh source/lineage/lock preflight passed, report-only ON_DEMAND retry financial-validation-lock-retry-20261002-111257 succeeded; no extra Core or raw run was needed.

- All 24 Core audits pass. Coordinated reconciliation preserves all 539 posting IDs, dates, signed amounts and categories; verifies nine exact School assignments and 30 existing School scopes; confirms 59 Schools, complete QTD shapes, zero GL/lineage/revenue/Timeback mismatches, $270,595.89 dedicated-book QTD Facilities expenses and the separate −$9,900 workshop credit. This validation includes separately applied Finance policies and Facilities/Guide procedure fixes; they are not changes in this configuration PR.

Future validation must wait for relevant financial and shared-School writers to be idle. The successful-stage total is not a promise of contention-free end-to-end timing.

## Implementation Effort

Approximately 2–3 hours for an engineer to inspect controls, deploy the isolated configuration/registry changes and perform live validation; source coordination and pipeline waiting time are additional.

## Linear

[SURTR-1593](https://linear.app/builder-team/issue/SURTR-1593/enable-on-demand-quickbooks-core-validation-from-accepted-snapshots), a sub-issue of Finance escalation SURTR-1496.