The Trilogy Times

## Summary

- Use dedicated deployment roles and private asset stores instead of the shared administrator bootstrap.

- Bound ECS runtime permissions and give standalone infrastructure predictable names.

## Why

The shared CDK execution role has administrator access. The standalone needs a constrained deployment path before it can be safely released.

## Business Value

Enables deployment and validation without granting this repository account-wide administrative authority.

## Test plan

- [x] 86 tests, build, offline synthesis and GitHub CI pass.

- [x] AWS Access Analyzer reports no policy findings; IAM simulation denies shared admin-role delegation and boundary removal/modification.

- [x] CloudFormation CREATE_COMPLETE, ECS healthy, verified TLS.

- [x] Existing-key authentication and Redshift identity/catalog reads across all six profiles.

- [x] DynamoDB feedback submission, retry, privacy, attribution, operator closure and revision conflict checks; temporary operator revoked.

## Summary

- Prepare Shipyard 0.6.9 with the approved public release notes.

- Change only the authoritative app version and versioned release notes.

## Business Value

- Delivers the approved Pi image attachment and Pomodoro capabilities alongside companion, diagnostics, trace, and image-layout improvements.

- Provides the metadata that enables the verified Apple Silicon release workflow to build and publish the update.

## Implementation Effort

- Metadata-only release change: one version bump and one public release-notes file.

- CI performs the build, signing, audit, and publication after merge.

## Test Plan

- pnpm test:release

- git diff --check

- Verify the exact metadata diff before merge and monitor the matching Desktop release workflow after merge.

## Summary

Artifact integrity, retention, and audit lineage for the capacity pipeline (AERIE-2580: Yibin #4, #6, #8, #10 on Aerie #1439, plus Mercy's two follow-ups from Aerie #1554). This completes the ticket.

- Content checks (Yibin #4). Each copied file is hashed (SHA-256) and checked before it is stored (new capacityAutomation/artifacts.ts).

- Room table: application/json, valid UTF-8, and room rows: an array of objects, or an object holding one.

- Floorplan: image/svg+xml, a whole SVG document, with no script, foreignObject, event handlers, or XML entities. References are allowlisted, not blocklisted: every href, xlink:href, src, and CSS url() must be a #fragment inside the drawing, checked after decoding references the way a browser does. CSS may not contain escapes or @import, and no animation may target an href. The copy is served from Aerie storage and opened in a browser, so anything else is refused rather than sanitized.

- Bad content or an over-budget run can't be fixed by retrying, so it raises CapacityArtifactRejectedError and the run ends unresolved (cause artifactRejected) instead of polling until the 30-minute timeout. A truncated download or a Sindri error still retries.

- Transfer manifest (Yibin #6).

- Each copy is staged on the run (artifactTransfers, via _stageCapacityArtifact) as soon as it is stored. A retried poll reuses staged copies instead of downloading them again.

- _recordCapacityRunOutput now reads the manifest instead of taking storedArtifacts as an argument. It records the copies the output names and deletes every other staged copy. A run that ends without recording them (failed, unresolved, timed out, rejected artifact) deletes them all.

- A copy staged on a run that is no longer running is deleted at once. A copy that can't be deleted stays referenced in artifactTransfers, matching #1550's rule for artifactRefs. If staging throws after the copy is stored, the runner deletes it; if that delete also fails, the copy goes to a capacityArtifactOrphans log that the sweep keeps trying to delete (_purgeCapacityArtifactOrphans).

- Cumulative budget: CAPACITY_MAX_ARTIFACT_BYTES = 25 MB across a run's files. It is checked against Sindri's declared sizes before anything is downloaded, so it also bounds action memory.

- Retention and operator path (rest of Yibin #8).

- Retention rule: a copy lives only while a document can point at it. It is deleted on reject (as before), on supersede (at enqueue as before, and now also at publication, after retracting anything an earlier attempt registered), on rollback (proposal, and record-mode once the request is confirmed), on exhausted publication, and when a DD request ends unapproved.

- Held runs nobody reviews expire 14 days after they enter review (heldAt; _expireHeldCapacityRuns, run by the sweep): they become unresolved with cause reviewExpired, and their copies are deleted.

- New unresolvedCause on the run: doctrineMissing, evidence, sindri, timeout, agent, artifactRejected, reviewRejected, reviewExpired, ddRequest. Missing required doctrine (the assembler's unavailable status) is now doctrineMissing, distinct from every other unresolved run.

- Publication while disallowed no longer retries every 15 s. The run is parked (publicationParkedAt, nextAttemptAt unset, and the sweep's due query now skips it) with an error naming the operator step. _resumeParkedCapacityPublications (one run by runId, or all parked runs) hands runs whose site is now allowed back to the sweep and reports the rest as stillBlocked. A parked run still counts as in flight, so the site gets no new run until it is resumed. That matches today's behavior, where the retrying run also blocked the site.

- Access and audit lineage (Yibin #10).

- Every file sent to Sindri (the evidence Markdown) and every file copied back writes an audit entry (capacity.evidence.uploaded, capacity.artifact.copied). Copies carry a Sindri sourceExecution whose acceptedOutputHash is the file hash.

- artifactLineage on the run keeps each file's hash, size, and audit log ID, and is never pruned when copies are deleted. Registered documents also carry the SHA-256 in their notes.

- Narrowing access: declined, with the reason recorded on the ticket. The room table and floorplan are derived from the site's own floorplans, which are already ordinary site documents with the same audience. A narrower rule would need a document-level access model that Aerie doesn't have, and would hide the analysis from the people who review it.

- Stuck partial requests (Mercy, #1554). Part of the write already applied, so retracting would misstate the card. The run records ddRequestPartialSince. After 24 hours it gets a Needs engineering: error, logs once, and polls hourly instead of every 5 minutes. It is never compensated automatically. The flag clears when the request settles.

- Unset-status rollback, end to end (Mercy, #1554). The test now approves and confirms the rollback request and asserts rolledBack, the restored capacities, the kept status, the removed documents, and the deleted copies.

## Test plan

- [x] chat: vitest run convex/capacityAutomation.test.ts convex/capacityAutomation/config.test.ts (150 passed)

- Content: rejected room tables (not JSON, no rows, non-object rows, wrong type, invalid UTF-8) and floorplans (not SVG, wrong type, script, event handler, javascript: link, entities); accepted variants.

- Manifest: copies are staged with their hash; a retry after a partial transfer downloads only the missing file; the budget rejects before any download; bad content is never staged; a rejected artifact ends the run artifactRejected without polling.

- Staging writes lineage plus an audit entry with the Sindri sourceExecution; a replacement deletes the older copy; a non-running run refuses and deletes the copy.

- Recording keeps only the named copies and deletes a stray one; rejected and timed-out runs delete staged copies; a redelivery leaves recorded copies alone.

- The evidence upload hash and audit entry are kept with the dispatch inputs.

- unresolvedCause values for agent, evidence, doctrine, review rejection and expiry, and timeout; 14-day expiry of held runs.

- Parked publication: not due, stays parked while disabled, resumes and publishes once enabled.

- partial request: first seen, flagged after 24 hours with hourly polling and documents kept, cleared and published on approval.

- Unset-status rollback through approval and confirmation.

- [x] tsc --noEmit (chat), Biome, and the pre-commit hook.

## Summary

- add the dynamic next-year Start-of-Year forecast column across desktop, mobile, sorting, and coverage-safe totals

- publish and render V3 replacement operands, rate lineage, and End-of-Year provenance in redesigned January/Next Year details

- accept coherent aerie_milestone_v2 and aerie_milestone_v3 snapshots across ingestion and consumers while rejecting mixed generations

- reveal the Next Year tab, including controlled unavailable states and accessible source tracing

## Validation

- pnpm typecheck

- pnpm lint:test-architecture

- pnpm --filter @bran/contracts exec vitest run src/admissions-forecast-v2.test.ts --maxWorkers=1

- pnpm --filter @bran/sync exec vitest run src/analytics/admissions-forecast-refresh.test.ts src/redshift/admissions-forecast.test.ts --maxWorkers=1

- pnpm --dir chat exec vitest run --project edge convex/admissions/forecastV2.test.ts --maxWorkers=1

- pnpm --dir chat exec vitest run --project browser components/dashboards/admissions/forecast/v2/__tests__/forecast-v2-report.test.tsx --maxWorkers=1

Closes #1598

## Summary

A8 plan unit U19 (SURTR-1549). This PR publishes Surtr copies of Aerie's two SIS enrollment reads, so the SIS report can move onto the Surtr Gateway. Aerie's gate for them is U23.

Aerie reads the rollups and their student members from one dbt relation inside one transaction (querySisEnrollmentSnapshot, sync/src/redshift/sis-enrollment.ts:280-296). That makes both reads one snapshot. Two separately published copies would lose that guarantee, so one procedure publishes both marts in one transaction under one source_run_id.

| Mart | Gateway slug (U04) | Aerie SQL, verbatim at e366e27d0 | Rows today |

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

| mart_education.aerie_sis_enrollment_rollup_input | aerie-sis-enrollment-rollup-input | rollupsSql, l.72-81: GROUP BY program, name, year, cohort, x_pipeline | 1,741 |

| mart_education.aerie_sis_enrollment_member (minors' PII) | aerie-sis-enrollment-member | membersSql, l.198-222, plus mart_row_id | 5,303 |

How the copy works

- Source. Both marts read the production relation sandbox_education.mart_enrollment_dtl, never a DBT_TARGET pr<N>_ build.

- SQL changes. The only substitutions are the two Aerie's own template makes: ${relation}, and ${reportCohortList()}, which becomes SIS_ENROLLMENT_REQUIRED_COHORT_IDS. Neither query has an ORDER BY to drop.

- Procedure. sp_refresh_aerie_sis_enrollment follows U16's pattern:

- it locks the shared mutex;

- it no-ops when both marts already carry the current pg_class_oid build marker from one run;

- it builds both candidates in the CALL transaction;

- it publishes both with DELETE + INSERT in that same transaction.

- Fails closed when:

- the raw columns drift (coupling guard);

- a candidate is empty;

- a row of Aerie's query is lost;

- a mart_row_id is duplicated;

- dbt swaps the build mid-copy;

- the members do not roll up to the rollup input. For each group, the members' distinct student_id count must equal student_count, and every rollup group that counts a student must have members. This is the single-snapshot invariant.

- Runner. CONTRACT_MARTS in src/handler.py lets one procedure publish several marts. The runner verifies each mart, then requires one source_run_id and build marker across them. If that check or the CALL fails, both marts are reported failed.

- Checks that stay in Aerie. Aerie's own checks (cohort coverage, the first-day partition, the members' strict parse) run on the Gateway rows unchanged. A build that fails them is published as-is and fails in Aerie exactly as on the legacy read.

- Keys.

- Rollup: MD5 of the 5 group columns. The GROUP BY makes them unique.

- Member: MD5 of (enrollment_id, cohort_id) plus the occurrence number. That pair is unique today (5,303 of 5,303) but dbt doesn't enforce it.

- PII. The member table has a PII: COMMENT on student_id, full_name, first_name, last_name, email, withdrawal_reason and transfer_reason, and owner-only grants (SELECT revoked too). The rollup input is owner-only too, because it counts minors and some of the counts are small.

- DDL. pipelines/cdk/sql/mart_education/090-092 (U19 owns 090-099). They are applied by this runner's scripts/apply_ddl.py.

- U04 registration. Checked against Surtr/src/seed-gateway-aerie-a8.ts. The slugs, table names and orderBy: mart_row_id match; no fix needed. A new contract test pins this.

Coupling to Vladimir's SIS work (collision MEDIUM)

SIS is Vladimir's active area (AI-Builder-Team/Aerie#1299, AI-Builder-Team/Aerie#1303, AI-Builder-Team/Aerie#1307 and AI-Builder-Team/Aerie#1445). A change to any of these alters what Aerie reads:

- a column in rollupsSql or membersSql;

- the cohort list;

- the has_fact filter;

- the type of the raw cohort_id or x_pipeline.

Any such change needs a lockstep change here:

1. Update the candidate SQL in 092_sp_refresh_aerie_sis_enrollment.sql.

2. Update the pinned SQL in tests/test_sql_contracts_sis_enrollment.py.

3. Migrate the marts.

If this is missed, it fails loudly, not silently:

- A dropped or renamed column breaks the copy's SELECT.

- A type change on a raw column trips the coupling guard. The run goes PARTIAL and the last publication stays.

- A pure SQL change on the Aerie side shows up as differences in the U23 shadow.

New dbt columns that Aerie doesn't read, and changed values, need nothing.

## Business Value

- One G6 read off the legacy path. Aerie's SIS enrollment report is one of the reads A8 moves off the analytics worker's direct Redshift connection and onto governed, lineage-stamped Surtr marts. That is a step towards retiring the EC2 worker's warehouse reads.

- The dashboard stays consistent. The copy keeps the report's key guarantee: the rollup counts and the student drill-down always describe the same dbt build. Otherwise the SIS dashboard could show a count that disagrees with its student list.

- A stable row key. It adds the row key and run id that mart_enrollment_dtl lacks. The Gateway can page it, and the shadow compare can tell a stale copy from a real mismatch.

## Manual Effort Estimate

Proposed: about 10 focused hours. Keval, please confirm or adjust.

- Tracing Aerie's SIS reader, contract and dbt model: 1.5h.

- The two tables and the one-transaction procedure, with the roll-up invariant: 3.5h.

- The runner multi-mart contract and its tests: 1.5h.

- The reconciliation SQL, running it and the negative control: 1.5h.

- SQL contract tests and README: 2h.

## Testing / evidence

- pytest (mart-aerie-dbt-publication-refresh): 142 passed. This includes the new test_sql_contracts_sis_enrollment.py and the TestOneTransactionContract handler tests. mart-aerie-admissions-refresh: 199 passed, unchanged.

- ruff 0.15.22: check and format --check are clean.

- scripts/apply_ddl.py --dry-run exits 0 and lists this runner's files in numbered order:

  -- 070_aerie_dbt_publication_refresh_writer_mutex.sql: 5 statement(s)
-- 071_aerie_admissions_pipeline_detail.sql: 26 statement(s)
-- 072_sp_refresh_aerie_admissions_pipeline_detail.sql: 4 statement(s)
-- 090_aerie_sis_enrollment_rollup_input.sql: 14 statement(s)
-- 091_aerie_sis_enrollment_member.sql: 26 statement(s)
-- 092_sp_refresh_aerie_sis_enrollment.sql: 4 statement(s)

- Read-only reconciliation. reconciliation/aerie_sis_enrollment_vs_aerie_sql.sql Queries 1-4 were run against prod Redshift as CQL_download_OM. They were SELECTs only, and printed counts only. The dbt build was pg_class_oid:20602444.

| Check | Aerie | Candidate | Aerie minus candidate | Candidate minus Aerie | Verdict |

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

| Rollup input (Q1) | 1,741 | 1,741 | 0 | 0 | PASS |

| Members (Q2) | 5,303 | 5,303 | 0 | 0 | PASS |

| Roll-up, one statement (Q3) | Rollup rows | Member rows | Members not in rollups | Rollups not in members | Verdict |

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

| Candidate | 1,741 | 5,303 | 0 | 0 | PASS |

- mart_row_id. The procedure's exact expressions, run as a SELECT, are unique: rollup 1,741 of 1,741, members 5,303 of 5,303.

- Negative control. Dropping the withdraw cohort's members makes the roll-up check report 48 unmatched rollup groups, so the check does catch a member set from a different snapshot.

- Not yet run. Queries 5-7 compare against the published marts and need the DDL applied and one run. No DDL was applied and nothing was deployed.

## Keval steps

1. PII sign-off (minors) before the two marts are exposed over the Gateway (A8 plan §9 D2). The member mart holds students' names, emails, SIS ids, enrollment history and free-text reasons.

2. Apply the DDL (090-092). apply_ddl.py applies all of this runner's files, which is idempotent:

REDSHIFT_CLUSTER_IDENTIFIER=redshift-cluster-1 REDSHIFT_DATABASE=finance_dw REDSHIFT_DB_USER=CQL_download_OM uv run python scripts/apply_ddl.py

If the dbt publication schedule (#2096) has been enabled by the time this merges, apply the DDL before merging. Otherwise every run calls a missing procedure and goes PARTIAL.

3. Run on demand with {"procedures": ["mart_education.sp_refresh_aerie_sis_enrollment"]}. Then run reconciliation Queries 5-7: expect parity = PASS twice and one_publication = PASS.

## Stack note

Depends on #2096 (U16, SURTR-1547), which adds the mart-aerie-dbt-publication-refresh runner this unit extends. The PR's base is feat/a8-u16-dbt-publication-runner. If #2096 merges first, this will be rebased onto main and retargeted.

## Not covered

- The Aerie gate SIS_ENROLLMENT_READ, its shadow compare and the dry-run. That is U23.

- The Gateway key mint and seed run. Those belong to U04.

- The unresolved upstream writer of the SIS model's staging_education_ai_horizons.raw_* inputs (A8 plan §8).

- dbt_invocation_id as the build marker (§9 D4). The OID fallback is used; switching is a one-line change in the procedure.

- Enabling the dbt publication schedule. That is #2096's follow-up.

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

## Summary

- Correct the dedicated CloudFormation role permission for ECS task-definition deregistration, limited to us-east-1.

## Why

The production rollout succeeded but replacement cleanup failed because ECS evaluates this action against resource *, not task-definition ARNs. The live role was corrected and the provisioning source must retain that correction.

## Business Value

Allows subsequent releases to finish cleanup without manual intervention.

## Test plan

- [x] pnpm check (86 tests), pnpm build, pnpm synth --no-lookups

- [x] Verified the live policy change contains only this permission correction

- [x] IAM simulation allows us-east-1 and denies us-west-2

- [x] CloudFormation UPDATE_COMPLETE and production deployment workflow succeeded

## Demo


## Summary

- Add a session-local Pomodoro control to the persistent Shipyard top bar.

- Support 25-minute focus and 5-minute break intervals with timestamp-based countdowns, pause/resume, reset, and automatic phase transitions.

- Reuse the accessible Popover/Button primitives with responsive semantic-theme styling.

- Add deterministic arithmetic and JSDOM tray coverage for transitions, accessibility, keyboard close/focus restoration, and lifecycle cleanup.

## Linear

https://linear.app/builder-team/issue/AI-937/add-a-pomodoro-timer-to-the-top-bar

## Test plan

- pnpm test:pomodoro

- pnpm test:notepad

- pnpm test:companion

- pnpm build

- pnpm theme:check

## Scope notes

- Timer state is intentionally session-local.

- Sound, desktop notifications, and backend/Tauri persistence are out of scope.

## Summary

- Prepare Shipyard 0.6.9 with the approved public release notes.

- Change only the authoritative app version and versioned release notes.

## Business Value

- Delivers the approved Pi image attachment and Pomodoro capabilities alongside companion, diagnostics, trace, and image-layout improvements.

- Provides the metadata that enables the verified Apple Silicon release workflow to build and publish the update.

## Implementation Effort

- Metadata-only release change: one version bump and one public release-notes file.

- CI performs the build, signing, audit, and publication after merge.

## Test Plan

- pnpm test:release

- git diff --check

- Verify the exact metadata diff before merge and monitor the matching Desktop release workflow after merge.

## Summary

- publish the four End-of-Year historical observation provenance fields in mart_admissions_forecast

- carry provenance beside the expected-arrival operand through live forecasts and persisted lock snapshots

- document null/zero semantics and reconcile every published field to the same usable observation

## Alpha Austin example

The mart contract can now return the explanation without an int_* join:

| field | value |

|---|---:|

| end_of_year_expected_enrollment_source_period_start | 2025-09-30 |

| end_of_year_expected_enrollment_source_period_end | 2026-06-05 |

| end_of_year_expected_enrollment_source_application_count | 36 |

| end_of_year_expected_enrollment_source_start_count | 16 |

| end_of_year_expected_enrollment_to_arrive | 16 |

## Validation

- git diff --check

- pre-commit hooks passed

- focused dbt fixture covers positive (36 applications / 16 starts), measured zero, and missing-observation reconciliation

- dbt execution deferred to PR CI per request

Closes #1597

## Summary

This is A8 plan unit U16 (SURTR-1547). It adds the third A8 runner, for copies of Aerie's dbt marts, and both reads behind Aerie's Admissions Pipeline report. That lets Aerie's queryAdmissionsPipelineRows and queryAdmissionsPipelineCrosswalk move onto the Surtr Gateway; the Aerie gate is U20.

- New runner pipelines/runners/mart-aerie-dbt-publication-refresh (Lambda, bundling: true, src/requirements.txt).

- Schedule: cron(0/10 * * * ? *). Aerie's dbt build is not a Surtr pipeline, so there is no success event to trigger on. Instead, each procedure detects a new build itself.

- The schedule ships disabled (Mercy round 1). Its objects are out-of-band DDL, so it is enabled in a one-line follow-up once the DDL is applied and an on-demand run is verified.

- What it runs: it CALLs each procedure in REFRESH_PROCEDURES with (run_id, force), then checks the mart read-only: non-empty, unique mart_row_id, one source_run_id and one source_build_marker. The run_id must be the platform UUID before it is inlined. U19 (SIS) and U24 (Forecast V2) will append their procedures here.

- Result per mart: published, unchanged (the build was already copied, which is most runs) or failed.

- Failure handling: the same as U03. A failed procedure makes the run partial_failure, and the run fails if every procedure fails or a commit outcome is unknown.

- Freshness: a copy whose dbt build is older than SOURCE_MAX_AGE_MINUTES (180) makes the run partial_failure. The copy is still exact, but dbt has stopped publishing (PIPELINE §5.4).

- 071/072 aerie_admissions_pipeline_detail (slug aerie-admissions-pipeline-detail, PII) is a style-C dbt publication copy.

- The candidate is Aerie's SQL. It is copied verbatim from admissions-pipeline.ts:154-173 (Aerie e366e27d0, unchanged since 92fd47992), bound to the production relation sandbox_education.mart_admissions_pipeline_dtl. The one other edit drops the trailing ORDER BY: a table has no row order, the Gateway pages by mart_row_id, and U20 re-sorts.

- The build marker. source_build_marker = pg_class_oid:<oid>. dbt's table materialization swaps in a new relation on every build, so the OID changes. When the published marker is current, the procedure no-ops unless p_force.

- Switching to dbt_invocation_id (plan §9 D4) changes only the one v_source_build_marker := assignment.

- Lineage: source_run_id is this copy's own run; source_published_at is the relation's creation time (pg_class_info.relcreationtime).

- Coupling guard (the lockstep rule). Before copying, the procedure compares the dbt relation's column types, by OID, with the mart's pinned types. Only the six ::text columns are exempt, and varchar width is ignored. On any drift it raises and keeps the previous publication.

- A mid-copy dbt swap is detected by re-reading the OID after the copy. In that case the procedure publishes nothing, and the next tick copies the new build.

- Other guards: it fails closed on a missing relation, an empty candidate, a candidate count different from the source's, or a duplicate mart_row_id. mart_row_id = MD5 of MD5(pipeline_key) and its occurrence number, ordered by every other column.

- PII: SELECT is revoked as well as writes, and the 11 PII columns carry PII: comments.

- 073/074 aerie_admissions_pipeline_tenant_crosswalk (slug aerie-admissions-pipeline-tenant-crosswalk).

- Aerie's SQL is copied verbatim from admissions-pipeline.ts:201-208.

- It is EduCRM-backed, so it is appended to U03's mart-aerie-admissions-refresh REFRESH_PROCEDURES, and it uses the observed sales-educrm-mart-sync provenance for mart_pipeline_dtl.

- Aerie's 1:1-per-tenant assertion stays in Aerie.

- DDL: pipelines/cdk/sql/mart_education/070-074; U16 owns 070-079. 070-072 are applied by the new runner's scripts/apply_ddl.py, and 073-074 by U03's.

- The U03 DDL test now requires every aerie_admissions file to be applied by exactly one of the two runners.

- Both apply_ddl.py scripts now have no default target (Mercy round 1). They refuse to send a statement unless REDSHIFT_CLUSTER_IDENTIFIER, REDSHIFT_DATABASE and REDSHIFT_DB_USER are all set.

- README: it documents the dbt-copy PIPELINE §13 exception (WAREHOUSE §2.2 and §2.5, and PIPELINE §4 for the external dbt writer; §7 is met through the explicit build marker), the coupling rule and the lockstep steps, the marker, and PII.

- Gateway registration (U04, #2082) checked: both slugs map to exactly these table names, ordered by mart_row_id. No change was needed.

## Business Value

- The G2 Admissions Pipeline report can leave the EC2 analytics worker. It is Aerie's widest PII read, at 30,929 rows and 53 columns. Aerie can read it through the Surtr Gateway with its unchanged row mapper. That is the SURTR-735 quarterly commitment, and a step toward tearing the worker down.

- dbt stays with Vladimir, with no fork. The copy follows each hourly dbt build within 10 minutes and carries lineage to the exact build. A dbt column change that would break Aerie's parity now fails loudly in Surtr, instead of silently drifting.

- U19 (SIS enrollment) and U24 (Forecast V2) reuse this runner, adding only a procedure and one REFRESH_PROCEDURES entry.

## Manual Effort Estimate

About 14 focused hours (roughly 2 days) to build by hand without AI. That covers:

- reading the Aerie reader, the dbt materialization and the catalog to design the build marker, the swap guard and the coupling guard;

- two procedures, the runner, and its freshness reporting;

- tests, reconciliation, and the read-only probes.

Keval: please confirm or adjust.

## Testing / evidence

- uv run pytest: 91 passed (new runner) and 105 passed (mart-aerie-admissions-refresh, including 14 new crosswalk contract tests and the explicit-target apply_ddl tests).

- The SQL contracts pin both Aerie queries. They assert:

- each candidate is exactly that SQL plus the allowed edits;

- the reconciliation uses the same candidate;

- the guards come before the DELETE;

- the coupling guard exempts only the ::text and lineage columns;

- the marker is a single assignment.

- Ruff 0.15.22: ruff check pipelines and ruff format --check pipelines are clean.

- CDK: real-pipeline-configs.test.ts passed (590). The app also synthesized with Docker bundling skipped (CDK_CONTEXT_JSON aws:cdk:bundling-stacks=[]). Pipeline-mart-aerie-dbt-publication-refresh-prod contains:

- one Lambda (handler.handler, python3.11, 900 s);

- one Step Functions state machine;

- the rule pipeline-mart-aerie-dbt-publication-refresh-schedule-prod cron(0/10 * * * ? *) ENABLED;

- 4 alarms.

- Read-only reconciliation was run with psql as CQL_download_OM, SELECT only, reading counts only:

| mart | aerie rows | candidate rows | aerie − candidate | candidate − aerie | parity |

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

| aerie_admissions_pipeline_detail | 30,929 | 30,929 | 0 | 0 | PASS |

| aerie_admissions_pipeline_tenant_crosswalk | 57 | 57 | 0 | 0 | PASS |

- The candidate as the mart stores it (every column CAST to the mart's declared type) is also EXCEPT 0/0 against Aerie's SQL: 30,929 and 57 rows. So the INSERT changes no value.

- Negative controls on the detail compare: dropping a row gives 1 / 0, and duplicating a row gives 1 / 1.

- The procedures' exact mart_row_id expressions give 30,929 and 57 distinct values. pipeline_key is unique and non-null (30,929).

- The source today: sandbox_education.mart_admissions_pipeline_dtl is a table (relkind r) with OID 20602348, created 2026-09-29 11:39:17 UTC (the 11:30 dbt build), owned by vladimir.pikalov. It is readable by CQL_download_OM.

- The coupling guard's pinned columns are 39 character varying, 5 boolean and 3 numeric(18,2). The guard's catalog query returns 0 against the source itself.

- EduCRM mart_pipeline_dtl: the observed run ed781ef8… is the latest run (SUCCESS), and rows_loaded 33,585 = snapshot 33,585.

- Not yet run: Query 3 (detail) and Query 2 (crosswalk) compare against the published marts, and need the DDL.

- scripts/apply_ddl.py --dry-run passes. The statements are the committed SQL files verbatim, in this order:

- new runner: 070_aerie_dbt_publication_refresh_writer_mutex.sql (5 statements), 071_aerie_admissions_pipeline_detail.sql (26), 072_sp_refresh_aerie_admissions_pipeline_detail.sql (4);

- mart-aerie-admissions-refresh: 006-011 unchanged, then 073_aerie_admissions_pipeline_tenant_crosswalk.sql (10) and 074_sp_refresh_aerie_admissions_pipeline_tenant_crosswalk.sql (4).

## Keval steps

1. Crosswalk DDL before merging. A merge reaches production within the hour, and the EduCRM trigger then CALLs the crosswalk every 30 minutes. Until 073-074 exist, that CALL would make each run PARTIAL (amber, throttled); nothing wrong is published. Run:

cd pipelines/runners/mart-aerie-admissions-refresh && REDSHIFT_CLUSTER_IDENTIFIER=redshift-cluster-1 REDSHIFT_DATABASE=finance_dw REDSHIFT_DB_USER=CQL_download_OM uv run python scripts/apply_ddl.py

It applies 006-011 (idempotent) and 073-074.

2. PII sign-off (A8 plan §9 D2) for exposing aerie_admissions_pipeline_detail over the Gateway. It holds parent and child names, emails and phones, and child date of birth and gender.

3. Merge. Mercy withholds auto-approve on pipelines/cdk/ paths. The new runner deploys with its schedule disabled.

4. Apply the detail DDL (070-072):

cd pipelines/runners/mart-aerie-dbt-publication-refresh && REDSHIFT_CLUSTER_IDENTIFIER=redshift-cluster-1 REDSHIFT_DATABASE=finance_dw REDSHIFT_DB_USER=CQL_download_OM uv run python scripts/apply_ddl.py

5. Run mart-aerie-dbt-publication-refresh on demand.

- The first run should report published with about 30,929 rows.

- A second run should report unchanged.

6. Run both reconciliation files. Each comparison against the mart must report parity = PASS. For the detail, first check that the mart's marker equals Query 2's current marker.

7. Enable the schedule: a one-line follow-up PR setting "enabled": true in the new pipeline.json.

8. Optional: ask Vladimir to add {{ invocation_id }} AS dbt_invocation_id to mart_admissions_pipeline_dtl (plan §9 D4). The switch is then one assignment per procedure.

## Not covered

- The Aerie gate and shadow compare (U20), SIS (U19) and Forecast V2 (U24).

- The procedure bodies have not been executed in Redshift, because no DDL was applied. Their candidate SELECTs, the type-projected candidate, the mart_row_id expressions, the catalog queries (source OID, creation time, the coupling guard's shape) and the EduCRM observation were run read-only instead.

- A procedure-only change does not republish by itself. With an unchanged dbt marker, the procedure no-ops. After a lockstep migration or procedure fix, run on demand with force: true, as the README says.

- OID reuse. The marker assumes Redshift does not reuse the relation's OID across builds. OIDs are 32-bit and only wrap after about 4 billion allocations.

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

## Why

A Docs edit correctly invalidates the current add-on review, but today it also destroys the only prior addressed/dismissed snapshot. The next run therefore reopens previously triaged findings, unlike the native app.

## Change

- Keep an ID-free, bounded private disposition snapshot when Doc content changes. review_results remains None: stale cards are not served and old finding IDs remain unusable.

- Carry only persisted addressed/dismissed identities to matching new findings on the next review, inside the existing CAS retry, then clear the snapshot.

- Fail closed rather than silently truncate if the ledger exceeds its bound.

## Limits

This does not infer addressed status from Claire tool resolution or recover failed finding-status PATCHes. Sidebar batch handling is separate PR #3834 (already merged). No production Doc/session mutation or deployment.

## Tests

177 focused tests passed locally; Ruff check/format and diff check passed. CI/review required before merge.