Migrate course registrations to immutable cohort-owned evidence

Parent epic: #54.

PM disposition

GROOMED / SOURCE-ONLY / DEPENDENCY-BLOCKED.

This issue is narrowed to the additive registration-schema and reconciliation source that current specifications authorize. The unresolved pre-cohort interest contract moved to #286. Destructive legacy-column contraction moved to #287. Neither split outcome is approved or implemented here.

Outcome

Add the courses-owned target fields, immutable evidence components, protected mappings/quarantine state, and deterministic reconciliation tooling needed to migrate legacy CourseRegistration rows toward one durable-account-owned immutable Cohort registration. Preserve every historical row, primary key, timestamp, consent fact, and #133 aggregate boundary. Do not change a public route/form, enable a new writer, enforce final non-null constraints against unresolved data, or remove any legacy compatibility field.

This is an additive source slice. It can be independently engineered, tested on synthetic data, deployed safely, and closed without pretending that profile/public-flow activation, protected-data reconciliation, CourseInterest, or destructive contraction has occurred.

Normative authority

• _docs/PROCESS.md
• _docs/specs/04-courses-and-cohorts.md, Registration and Expand-and-contract model migration
• _docs/specs/07-security-privacy-operations.md
• _docs/specs/09-migration-rollout-roadmap.md, Data migration controls and Member-profile expand and contract
• _docs/specs/10-verification-strategy.md
• _docs/specs/open-decisions.md, resolved decisions 6, 12, and 19
• _docs/architecture/app-boundaries.md
• Parent #54, profile foundation #247, aggregate-count contract #133
• Decision-only CourseInterest child #286 and later legacy-contract child #287

Hard dependencies and exact start gate

Do not engineer until accepted and integrated #247 is present on the base. Its own exact chain is:

#224 -> #51
(#51 + #231 + #234) -> #247

#247 supplies the final Course/Cohort migration shape, accounts-owned eligible/survivor/collision-safe identity resolution, MemberProfile schema, immutable confirmed-scalar projection, country/role mapping, and final accounts migration leaf. Record every prerequisite merge SHA, the adopted source pin, final accounts and courses migration leaves, and #133 schema/native-boundary fingerprint in the engineer handoff.

#248 is not a source dependency: this issue does not activate profile routes/read authority or public registration. #49/#50 are not dependencies because it creates no delivery intent, job, callback, sender, or transport work. #230 is peer-review behavior and belongs to #244, not this schema slice. #46 is event-only.

Ownership and additive target contract

accounts remains sole owner/writer of account identity, verification, survivor aliases, normalization, and MemberProfile. courses owns CourseRegistration, immutable registration snapshots/evidence, reconciliation/quarantine state, and legacy mappings. content.Person is unrelated editorial identity and is never inferred or linked.

Add the target-native registration representation without removing or repurposing existing fields:

• preserve the legacy primary key and provide one stable opaque registration UUID when #51 does not already supply one; retain a one-to-one legacy mapping;
• nullable-at-expand account, Cohort, and campaign relations plus immutable Cohort UUID/legacy ID/slug, parent Course UUID/slug, and campaign UUID/slug snapshots;
• an immutable normalized verified-email snapshot produced only by the accepted accounts normalization contract;
• preserved created_at and existing primary key;
• one immutable minimized shared-profile component containing exactly profile UUID, completion schema version, profile revision, snapshot timestamp, optional certificate/display name, member-confirmed country code, derived region, organization, work status, professional role, and seniority;
• separate optional trimmed comment (maximum 1,000 characters), privacy-notice version and acknowledgement timestamp, and optional explicit marketing-consent evidence with version/timestamp when evidence exists;
• preserved historical accepted_newsletter as legacy evidence only—never current consent;
• bounded non-PII migration status/reason and protected legacy mapping/quarantine support.

Exclude bio/About, ambitions, why-joined, social/profile URLs, Slack state/secrets, mutable campaign pointers, and any inferred consent. Ordinary code cannot mutate populated target snapshots/evidence. Legal correction/deletion/anonymization remains a separately approved service boundary.

The eventual target invariant is one logical registration per (Cohort, normalized verified-email snapshot), with later Cohorts independent. This issue may define and test that target rule and enforce it for complete target-native synthetic rows, but it must not make legacy columns non-null, discard/quarantine data, or activate a database constraint while unresolved deployed rows would violate it.

Deterministic preflight and reconciliation source

Provide a read-only, repeatable preflight and an execution-disabled/idempotent reconciliation service or command suitable for later authorized operation. It classifies, with aggregate counts and safe checksums only:

• unavailable/null/inactive/quarantined/absorbed/broken/colliding account identity;
• null/missing/mismatched Cohort, campaign without target, campaign repoint drift, or invalid #51 mapping;
• duplicate (Cohort, normalized verified email) across campaigns;
• blank/invalid/mismatched legacy and account email identity;
• missing/incomplete/unconfirmed/stale profile revision or invalid profile choice;
• missing/invalid privacy evidence, legacy newsletter state, conflicting consent evidence, comment overflow, or invalid timestamps;
• #133 campaign/Cohort/native-boundary, replacement, pointer, or checksum drift.

Deterministic rules are narrow:

• resolve identity only through #247/#234 services;
• use an already persisted valid #51 Cohort mapping as historical target; never infer from the campaign's current pointer, title, year stripping, or current Course;
• copy only current member-confirmed #247 scalar profile data;
• retain legacy newsletter evidence without creating consent;
• never synthesize verification, privacy acknowledgement, profile confirmation, target, or missing evidence;
• retain ambiguous rows and classify them; never select a duplicate by time, campaign priority, or arbitrary key.

The same exact source/schema identity produces the same plan digest. Apply is bounded, transactional, resumable and exact-replay-safe on synthetic/test data. Source drift, checksum mismatch, concurrency, unknown reasons, or crash fails closed. No signal, migration, command, fixture, or test dispatches a job or network side effect.

Migration and rollback phases owned here

1. Freeze exact accepted prerequisite identities and characterize legacy schema/fixtures.
2. Add nullable target fields/components, mappings, quarantine/status support, portable indexes, and only constraints safe for the additive state.
3. Produce deterministic read-only preflight and plan digests.
4. Exercise bounded dry-run/apply-twice/crash/restart/reverse/forward reconciliation on synthetic and production-like synthetic fixtures.
5. Validate preserved IDs/timestamps, relation and reason counts, safe checksums, target identity, evidence separation, and #133 invariants.
6. Prove old code remains runnable and the additive migration reverses without deleting or rewriting legacy rows.

Not owned here:

• profile/account-settings activation (#248);
• public target-native registration and its target writer/delivery intent (#243);
• pre-cohort interest (#286);
• production/protected-data execution or reconciliation approval;
• final non-null/unique activation over deployed legacy rows;
• legacy writer/read removal or column/constraint contraction (#287).

Security and privacy

Use synthetic .invalid fixtures only. No production/CMP registration or profile row, real email, protected export, secret, reversible PII digest, or source locator enters repository or issue evidence. Output is bounded to counts, reason codes, schema/source identities, and non-reversible safe checksums. No raw email/name/comment/profile/consent value appears in logs, metrics, audit text, URLs, screenshots, fixtures, or errors.

Existing exports/deletion paths must either remain compatible with additive fields or fail closed. Foreign keys protect history; no cascade is introduced as an erasure substitute. All outbound website/Relay/Datamailer/SES/provider behavior remains disabled and uncalled.

Acceptance criteria

• Exact accepted #247 and transitive prerequisite SHAs, source pin, migration leaves, and #133 fingerprint are recorded.
• Additive target fields/components, mapping and quarantine support match the exact ownership, inclusion, exclusion, immutability, and portability contract above.
• Existing IDs, timestamps, relations, legacy fields, accepted_newsletter, and #133 provenance remain unchanged and readable.
• Preflight classifies the full matrix deterministically with bounded aggregate output and safe checksums only.
• Reconciliation uses only accepted identity, Cohort mapping, normalization, and confirmed-profile services; ambiguity is retained/classified, never guessed or silently merged.
• Dry-run/apply twice/crash/restart/concurrent/source-drift/reverse/forward scenarios preserve exact counts/checksums and fail closed.
• The eventual (Cohort, normalized verified-email snapshot) invariant and later-Cohort independence are proved on complete synthetic rows without prematurely enforcing target completeness on unresolved legacy rows.
• No public/auth/learner/Studio/admin/API behavior or canonical profile/registration writer is activated; no legacy column/writer/reader is removed.
• No CourseInterest model or behavior and no #287 contract cleanup is introduced.
• Security/PII canaries prove zero sensitive output and zero email/job/network/workflow/provider side effect.
• Migration drift, focused model/service/migration/security/adoption tests, backend portability, container checks, and the graph-selected versioned verification plan pass.

Required engineer/tester scenarios

1. Synthetic active/legacy/inactive/quarantined/absorbed/broken/colliding identities; blank/malformed/case/whitespace email; complete/incomplete/unconfirmed/stale profiles.
2. Stable target, null target, campaign repoint, mismatch, missing/duplicate mapping, multiple campaigns per Cohort, sibling Cohorts, and exact duplicate races.
3. Missing/valid/version-drift privacy evidence; absent/false/true historical newsletter; absent/explicit optional marketing evidence; comment length and timestamp failures.
4. Empty/one/many rows; deterministic plan; batch boundaries; apply twice; crash/restart; concurrent run; changed schema/source/plan; quarantine; reverse/forward.
5. #133 baseline/native/replacement/rollback states and exact public-total invariance.
6. SQLite and production-like PostgreSQL migration behavior, safe aggregate reports, direct-writer guards, and zero side effects.

Render disposition

No HTML, CSS, JavaScript, route, form, serializer, OpenAPI, navigation, or copy change is in scope. Screenshots are not_applicable only if the computed graph confirms no render impact; the independent tester still runs the graph-selected browser tier. Any render change requires re-grooming and normal screenshot evidence.

Explicit non-goals

No CourseInterest; no public registration/confirmation or Enrollment conversion; no profile activation; no email intent/job/sender; no Studio/admin/export; no production/protected-data operation; no target-reader/writer cutover; no non-null activation over unresolved rows; no legacy field/mapping/evidence removal; no destructive contract migration; no source-pin advance; no copied numeric-ID/staff-token API; no direct Datamailer/SES/Relay/provider call.

Delivery

Follow _docs/PROCESS.md. This issue is engineer-ready only after its hard dependency is accepted/integrated. Engineering leaves one frozen uncommitted source candidate; an independent tester verifies its exact graph and synthetic migration evidence; PM accepts; only then is a focused commit with Closes #242 and Refs #54 locally merged/pushed and observed. Closing #242 proves only this additive source slice, not #286, #287, public activation, protected-data reconciliation, or completion of #54.

Start by verifying that #247 and its prerequisite chain are integrated, then read _docs/specs/04-courses-and-cohorts.md, _docs/specs/07-security-privacy-operations.md, _docs/specs/09-migration-rollout-roadmap.md, and _docs/specs/10-verification-strategy.md. Locate the courses migration, registration model, reconciliation entry point, and focused migration/security tests from the repository. Done means the additive schema and deterministic synthetic-data reconciliation satisfy the listed acceptance scenarios without public-flow activation, side effects, or legacy-data loss.