## Summary
Builds the seven governed core_education camps tables (A5's core layer,
per docs/plans/aerie-workers-to-surtr-migration.md) from one pinned
aerie-summercamps-raw-sync run:
staging_education_summercamps.raw_{camps,camp_locations,camp_weeks,registrations,registration_weeks,children,parents}
-> sp_refresh_camps(p_source_run_id) [ONE procedure, ONE transaction]
-> core_education.dim_camp_program
core_education.dim_camp_location
core_education.dim_camp_week
core_education.dim_camp_child (PII)
core_education.dim_camp_parent (PII)
core_education.fct_camp_registration
core_education.bridge_camp_registration_week
All seven raw tables come from one raw pipeline's one run sharing one
run_id/extraction_id (a partial sync fails the whole upstream pipeline
and never reaches on_pipeline_success), so this is one procedure
publishing seven tables atomically — mirroring
core_education.sp_refresh_aerie_ontology's one-procedure-many-tables
shape, per the precedent already established in this repo for multi-table
Core contracts sourced from one ledger-pinned run.
## Deliberate deviations from the migration plan doc's original sketch
Verified against Aerie's actual source, not guessed (see README.md and the
per-table DDL comments for full citations):
- No bridge_camp_child_guardian. children.parent_id is a single
scalar FK to one parent — confirmed in Aerie's Convex schema, the raw
DDL, and query code. There is no child↔guardian many-to-many
relationship anywhere in the source.
- bridge_camp_registration_week is built instead — the real missing
bridge is registration↔camp_week (one registration can span multiple
weeks, confirmed by an Aerie test fixture).
- Grain correction: fct_camp_registration's grain is one row per
*registration*, not "one row per child per week" as the plan doc's
sketch stated.
## Business logic ported
- dim_camp_week.week_column_key/week_short_label/week_sort_order/
week_number — a literal SQL port of Aerie's getWeekNumber/
buildWeekColumnKey/buildWeekShortLabel/buildWeekSortOrder.
Validated against the exact JS logic across 14 edge cases (including
"Bi-Week 3" matching and "midweek12"/"week1x" correctly *not* matching)
before being written into SQL.
- dim_camp_parent.is_internal_email — a literal port of Aerie's hardcoded
internal-domain allowlist, computed once here so no mart re-derives it.
- fct_camp_registration.payment_bucket — paid/pending/other, preserving
raw status separately (Aerie's own dashboard silently drops anything
outside paid/pending; this warehouse keeps it visible).
## PII grants
dim_camp_child and dim_camp_parent hold minors'/parents' PII (DOB,
allergies, medical conditions, email, phone, address). No existing
core_* PII grant precedent exists in this repo, so both default to the
same team_engineers-only SELECT pattern every other Core table here
uses, but *without* the MCP_user grant, plus an explicit Sensitive
data: note in the table COMMENT.
## Verification
- uv run pytest: 49/49 passing (SQL-content contracts, handler unit
tests, apply_ddl statement-splitting, pipeline.json contract).
- ruff format/ruff check: clean against both the local latest (0.16.3)
and CI-pinned (ruff==0.15.22, per .github/workflows/ci.yml) — the 4
findings under 0.16.3 alone (non-executable script shebangs, one
ValueError vs TypeError preference) are pre-existing-pattern-consistent
with core-education-academic-term-refresh and are part of the ~2,845
repo-wide findings the CI comment says aren't enforced under 0.16 yet.
- scripts/apply_ddl.py --dry-run: clean statement split, procedure body
survives as one statement.
- Beyond the required dry-run: loaded the real DDL into a scratch
local Postgres instance (Redshift-only bits like DISTSTYLE/SORTKEY
stripped, GETDATE/REGEXP_SUBSTR/REGEXP_REPLACE shimmed to match
Redshift's documented semantics), seeded a fixture matching Aerie's own
campDashboards.test.ts, and CALLed sp_refresh_camps end-to-end —
every computed column (week_column_key, payment_bucket,
is_internal_email) matched expected values exactly, including the
'other' bucket for an unrecognized status.
## Business Value
Replaces Aerie's campPrograms/campLocations/campWeeks/campChildren/
campParents/campRegistrations/campRegistrationWeeks Convex tables
with a governed warehouse layer that any Surtr/Klair consumer (MCP,
dashboards, ad-hoc SQL) can read without depending on Aerie's own compute
or Convex uptime. Corrects a real design error in the migration plan
(the wrong bridge table, the wrong fact grain) before it could be built
and shipped incorrectly — catching this now is cheaper than un-shipping a
wrong bridge_camp_child_guardian table and a mis-keyed
fct_camp_registration later. Unblocks mart-aerie-camps-refresh (see
the stacked PR) and, eventually, retiring the Aerie EC2 worker that
currently computes this camps dashboard data itself.
## Manual Effort Estimate
Proposed: ~2.5 focused days (~20 hours) — reading and cross-referencing
Aerie's Convex source (campDashboards.test.ts, campUtils.ts) against
the raw DDL, designing seven table schemas plus the corrected bridge/grain,
writing and debugging a 300+ line multi-table atomic PL/pgSQL procedure,
porting the week-key regex logic with edge-case validation, PII grant
research, and a full test suite. Keval: please confirm/adjust — this is a
proposed number, not a measured one.
## Test plan
- [x] uv sync --all-extras && uv run pytest — 49/49 passing
- [x] uv run ruff format/ruff check — clean (local + CI-pinned 0.15.22)
- [x] scripts/apply_ddl.py --dry-run — clean statement split
- [x] Full DDL applied to a scratch local Postgres; sp_refresh_camps
called end-to-end against a seeded fixture; every computed column
verified against hand-derived expected values
- [ ] Apply DDL to production Redshift (not done here — requires separate
explicit approval per repo convention)
- [ ] Deploy pipeline and enable trigger (not done here)
🤖 Generated with [Claude Code](https://claude.com/claude-code)