## Summary
Closes the Surtr side of AERIE_HUBSPOT_V3_GAP_LIST.md: every surface Aerie reads from the dead sales_alpha_hubspot_* mirror (source extraction died 2026-02-27) now has a fresh, Surtr-owned replacement built on the HubSpot v3 sync. The Aerie-side swap guide is at Aerie/HUBSPOT_V3_QUERY_MIGRATION.md — all 9 query migrations, most of them table-name-only changes.
Access model established with this PR: Aerie reads mart_education.aerie_* (and live eduCRM marts) only — never staging_education.*_raw.
## hubspot-sync: 4 new entities
- invoices (/crm/v3/objects/invoices + contact associations) → hubspot_v3_invoices_raw, hubspot_v3_invoice_contacts_raw. Replaces frozen sales_alpha_hubspot_invoices (12,235 live vs 10,014 frozen). Full source-presence support.
- forms + form_submissions (marketing v3 forms list + legacy form-integrations v1 submissions — no CRM v3 bulk submissions object exists) → hubspot_v3_forms_raw, hubspot_v3_form_submissions_raw. Submissions return newest-first; delta runs stop paging each form at the stored cursor. Raw values payload preserved as JSON for Aerie's existing parser.
- property_options (/crm/v3/properties/{object}/{property} option lists) → hubspot_v3_property_options_raw. Live value→label mapping for enum properties whose custom values are bare numeric IDs (lifecyclestage='1015817249'). Replaces the dead stg_lifecycle_stages lookup; unblocks retiring hubspot-admissions-funnel's frozen LIFECYCLE_STAGE_ID_MAP (follow-up).
- 12 new contact properties in CONTACT_PROPERTIES (tour/showcase status, unqualified_reason, admissions notes, preferred name, loss reason notes, entering_grade, application/offer/deposit dates) — all verified against the live HubSpot schema.
## hubspot-core-tables: widened admissions fcts
- fct_admissions_deal +19 columns: session_school_year (program-session assoc), student + parent display fields (deal_contacts ranked by contact_type), shadow/offer/deposit/withdraw dates, closed_lost_reason. Projection stays single-sourced in V3_PROJECTION_SQL.
- fct_admissions_contact +16 columns: identity, normalized lead source/channel, priority, and the newly collected properties.
## New pipeline: mart-aerie-hubspot-refresh (Lambda, 6-hourly)
Six mart_education tables, column-compatible with the retired Athena dims (Aerie swap = table-name change). Atomic DELETE+INSERT per mart; refuses to publish an empty mart; dependency-ordered (parent-child → contacts/conversion-rates; sessions → year-classification).
- aerie_parent_child_links — Parent-Child contact associations anchored on the child side, denormalized parent fields
- aerie_academic_sessions — program_code = program name, program_name = display_name (mapping verified against frozen data; the misses are HubSpot-side renames post-freeze)
- aerie_year_classification — reverse-engineered from frozen output, validated
- aerie_deals — old mt_dim_deal shape from the widened core fct
- aerie_contacts — old mt_dim_contact shape: lifecycle labels live from property options, derived x_is_active, coarse pipeline_stage rollup (eduCRM lead-pipeline source is gone; ~79% NULL before and now), enrichment + geo fields, primary parent
- aerie_program_conversion_rates — QS forecast inputs from the live eduCRM marts (not v3): trailing 6-month cohort lagged one month; registered_to_enrolled_rate bridges parent registrants → child enrollments via the parent-child mart (validated ~5.2% vs frozen 5.1% avg). SQL executed against prod Redshift: 85 programs, plausible rates.
## DDL (already applied to prod, out-of-band per house convention)
All files under pipelines/runners/*/ddl/2026-07-08_*.sql, verified applied: 5 new raw tables, contact raw +12 columns, both core fcts widened. Note: original v3 raw tables are owned by db user admin; the ALTERs ran as admin, new tables created as CQL_download_OM.
## Tests
- hubspot-sync: 185 passed (was 157)
- hubspot-core-tables: 9 passed (incl. extended projection-shape contract)
- mart-aerie-hubspot-refresh: 22 passed (incl. Aerie column-contract tests that fail CI if a mart edit breaks the query shapes Aerie depends on)
- CDK: 595 passed; cdk list shows Pipeline-mart-aerie-hubspot-refresh-prod
- ruff check + format clean
## After merge (operational runbook)
1. Deploy (cdk deploy — picks up hubspot-sync image, core-tables Lambda, new mart stack)
2. Full contacts run: {mode: full, entities: [contacts], contact_association_mode: full_refresh} — backfills new property columns + parent-child associations
3. Full deals run: {mode: full, entities: [deals]}
4. First invoices/forms/property_options syncs (next scheduled run or explicit trigger)
5. First mart refresh after 2–4 (contacts + conversion-rates marts guard-fail until parent-child is populated — expected)
6. Fact-diff report vs frozen alpha for sign-off, then Aerie cutover per Aerie/HUBSPOT_V3_QUERY_MIGRATION.md
Related: #632 (v3 enable + consumer rewire), #635 (educrm split / alpha sync retirement)
🐦⬛ Generated by a very good bot