Build the accounts-owned MemberProfile schema and service foundation

Parent epic: #108

Outcome

Add the backend-only accounts.MemberProfile expand foundation: one private row for every preserved CustomUser, the exact version-1 schema and validation, stable identity/revision/completion/confirmation state, an accounts-owned optimistic-concurrency service, an owner-safe confirmed-scalar projection, and reversible synthetic reconciliation. This child does not activate MemberProfile as canonical or change a product route.

Normative authority

• _docs/PROCESS.md — role-separated lifecycle and versioned verification.
• _docs/architecture/app-boundaries.md — accounts owns MemberProfile and its service; content.Person is a separate public editorial identity; accounts has no runtime import of courses.
• _docs/specs/01-platform-architecture.md, “Member profile version 1” — exact fields, choices, completion and compatibility contract.
• _docs/specs/03-github-content-and-people.md — zero Person inference/link/write/authority side effect.
• _docs/specs/06-studio-and-admin-api.md — shared service, revision checks, private owner/support boundaries.
• _docs/specs/07-security-privacy-operations.md — verified identity, private/no-store handling, redaction, safe URLs and country suggestion rules.
• _docs/specs/09-migration-rollout-roadmap.md, “Member-profile expand and contract” — precedence, reconciliation, activation and rollback sequence.
• _docs/specs/10-verification-strategy.md — model/service/migration/security/concurrency evidence.
• _docs/specs/open-decisions.md, resolved decision 19 — one account-owned private profile and member-confirmed reuse.

Hard dependencies and start gate

Depends on accepted, integrated #224, #231, #234, and #51.

• #224 supplies the final accepted AccountLegacyIdentifier migration graph and reviewed survivor-preservation baseline. The current remote leaf is accounts.0012; neither #224’s migration number nor #247’s next migration number is authoritative until the dependency merges are accepted and integrated.
• #231 supplies the final 215-name registration country inventory used by the frozen catalog below.
• #234 supplies the accounts-owned eligible/survivor/collision-safe identity resolution contract.
• The required “most recent linked CourseRegistration” precedence consumes #51's final Course → Cohort migration shape. Migration code uses historical app models only; accounts must not gain a runtime courses import.

This issue is groomed but BLOCKED while any dependency is open. Before engineering starts, record the accepted merge SHA for each dependency and the final accounts migration leaf in the engineer handoff. On the currently planned graph the new sequence begins at accounts.0014; if an accepted dependency changes that leaf, renumber without changing this contract.

Exact model contract

Add accounts.MemberProfile with these fields and no others:

Use blank strings, not NULL, for optional scalar text/URL/choice values. Code-defined choice codes and the country catalog are migration-stable, not Studio configuration.

Database constraints enforce one row per user, revision >= 1, member_confirmed_revision IS NULL OR 1 <= member_confirmed_revision <= revision, completion version 0/1, and (completion_version = 0 AND completed_at IS NULL) OR (completion_version = 1 AND completed_at IS NOT NULL). Version 1 additionally requires every required field to be nonblank. Country/choice membership, text length, timezone and URL safety are enforced by the shared service and model validation; PostgreSQL and SQLite must agree.

Validation, revision and confirmation transitions

All mutations run in transaction.atomic() and use a conditional update keyed by profile UUID and caller-supplied expected revision. A zero-row update is the stable stale-revision result. Do not rely on SQLite's no-op select_for_update; concurrent PostgreSQL writers and SQLite tests must both demonstrate no lost update.

1. Expand/backfill: create revision 1, member_confirmed_revision=NULL, completion_version=0, completed_at=NULL. Migrated and suggested values are never confirmed.
2. Incomplete member submit: validate and trim the entire candidate row, increment revision once, and set member_confirmed_revision to the new revision. Completion remains 0 until all version-1 requirements and verified-email ownership are satisfied.
3. First complete member submit: require an active eligible durable identity, a matching verified allauth email, and every required value; increment revision, set member_confirmed_revision to the new revision, set completion_version=1, and set completed_at to the transaction time. A failed transaction changes none of these.
4. Later member edit/confirmation: cannot clear a required value. A successful owner edit or explicit confirmation increments revision and sets member_confirmed_revision to the new revision while retaining version 1 and the original completed_at.
5. Migration or support correction: increments current revision but never advances member_confirmed_revision. If the profile was complete it remains structurally version 1, but confirmed consumer reads fail closed until the member confirms the current revision. Support/UI adapters are outside this child.
6. Future schema: no silent redefinition. A later issue introduces version 2 and its rollout.

Accounts service and safe read contract

Add one accounts-owned application service for create/update/confirm and compatibility projection. It owns all validation above and returns stable result codes for not_found, identity_unavailable, unverified_email, invalid, incomplete, and stale_revision; errors contain field codes only, never stored values or identity candidates. Internally it consumes #234’s exact available, not_found, unavailable, and ambiguous resolver outcomes: unavailable and ambiguous both fail closed as identity_unavailable at the profile boundary, and no adapter may expose which condition or candidate caused that result.

The foundation exposes an immutable confirmed-scalar value object containing only:

profile_id, completion_version, revision, optional certificate_name, country, derived region, optional organization, work_status, professional_role, and seniority.

It excludes email, bio/goals/why-joined, URLs, timezone, user/profile model instances, QuerySets and mutable dictionaries. It returns values only when:

• actor and subject resolve through #234 to the same available durable survivor;
• the survivor is active at the Django level, is active or legacy (never quarantined/absorbed), and owns a matching verified allauth email;
• completion is version 1; and
• member_confirmed_revision == revision.

An absorbed source may resolve only through one valid alias to an available survivor. Broken/missing/cyclic aliases, collision/quarantine, inactive identities, multiple candidates or profile/user mismatch fail closed. This child adds no support bypass; capability-scoped support reads/corrections belong to #250. The service performs no Person query/write and emits no profile value to logs, metrics, audits or exceptions.

Frozen country and compatibility projection contract

The accepted #231 inventory is 215 unique current names: Africa 54, North America 32, South America 14, Asia 50, Europe 48, Oceania 17. The mapping below adds only the retired legacy alias United States of America=US, for 216 input rows. Canonical TSV serialization region<TAB>legacy-name<TAB>code<LF> in the listed order has SHA-256 480d30eff3a415c1ff48572df069941b6ec86579ebe7e239a5319a976a23eaad.

The checked-in accounts-owned catalog must preserve this mapping exactly. Legacy migration lookup trims surrounding whitespace and then uses exact case-sensitive names; no fuzzy/casefold/geolocation guess is allowed. Unknown/blank names and unknown codes produce no profile country, leave the legacy value untouched in Phase A, and increment only a redacted aggregate conflict category. CloudFront-Viewer-Country is never a migration source.

Reverse compatibility projection uses the one #231 name for each code, derives the exact six region labels below, and writes name-based CustomUser.country plus CustomUser.region; it never writes alpha-2 into CustomUser.country. The sole retired alias projects back as United States. XK derives Europe. Existing contradictory legacy region text never overrides the catalog.

Africa: Algeria=DZ; Angola=AO; Benin=BJ; Botswana=BW; Burkina Faso=BF; Burundi=BI; Cabo Verde=CV; Cameroon=CM; Central African Republic=CF; Chad=TD; Comoros=KM; Congo=CG; Democratic Republic of the Congo=CD; Cote d'Ivoire=CI; Djibouti=DJ; Egypt=EG; Equatorial Guinea=GQ; Eritrea=ER; Eswatini=SZ; Ethiopia=ET; Gabon=GA; Gambia=GM; Ghana=GH; Guinea=GN; Guinea-Bissau=GW; Kenya=KE; Lesotho=LS; Liberia=LR; Libya=LY; Madagascar=MG; Malawi=MW; Mali=ML; Mauritania=MR; Mauritius=MU; Morocco=MA; Mozambique=MZ; Namibia=NA; Niger=NE; Nigeria=NG; Rwanda=RW; Sao Tome and Principe=ST; Senegal=SN; Seychelles=SC; Sierra Leone=SL; Somalia=SO; South Africa=ZA; South Sudan=SS; Sudan=SD; Tanzania=TZ; Togo=TG; Tunisia=TN; Uganda=UG; Zambia=ZM; Zimbabwe=ZW
North America: Canada=CA; United States=US; Mexico=MX; Bermuda=BM; Greenland=GL; Saint Pierre and Miquelon=PM; Belize=BZ; Costa Rica=CR; El Salvador=SV; Guatemala=GT; Honduras=HN; Nicaragua=NI; Panama=PA; Cuba=CU; Dominican Republic=DO; Haiti=HT; Jamaica=JM; Trinidad and Tobago=TT; Barbados=BB; Bahamas=BS; Grenada=GD; Saint Lucia=LC; Saint Vincent and the Grenadines=VC; Dominica=DM; Antigua and Barbuda=AG; Saint Kitts and Nevis=KN; Puerto Rico=PR; Curacao=CW; Aruba=AW; Cayman Islands=KY; British Virgin Islands=VG; U.S. Virgin Islands=VI
South America: Argentina=AR; Bolivia=BO; Brazil=BR; Chile=CL; Colombia=CO; Ecuador=EC; Guyana=GY; Paraguay=PY; Peru=PE; Suriname=SR; Uruguay=UY; Venezuela=VE; French Guiana=GF; Falkland Islands=FK
Asia: Afghanistan=AF; Armenia=AM; Azerbaijan=AZ; Bahrain=BH; Bangladesh=BD; Bhutan=BT; Brunei=BN; Cambodia=KH; China=CN; Georgia=GE; Hong Kong=HK; India=IN; Indonesia=ID; Iran=IR; Iraq=IQ; Israel=IL; Japan=JP; Jordan=JO; Kazakhstan=KZ; Kuwait=KW; Kyrgyzstan=KG; Laos=LA; Lebanon=LB; Macau=MO; Malaysia=MY; Maldives=MV; Mongolia=MN; Myanmar=MM; Nepal=NP; North Korea=KP; Oman=OM; Pakistan=PK; Palestine=PS; Philippines=PH; Qatar=QA; Saudi Arabia=SA; Singapore=SG; South Korea=KR; Sri Lanka=LK; Syria=SY; Taiwan=TW; Tajikistan=TJ; Thailand=TH; Timor-Leste=TL; Turkey=TR; Turkmenistan=TM; United Arab Emirates=AE; Uzbekistan=UZ; Vietnam=VN; Yemen=YE
Europe: Albania=AL; Andorra=AD; Austria=AT; Belarus=BY; Belgium=BE; Bosnia and Herzegovina=BA; Bulgaria=BG; Croatia=HR; Cyprus=CY; Czechia=CZ; Denmark=DK; Estonia=EE; Finland=FI; France=FR; Germany=DE; Gibraltar=GI; Greece=GR; Hungary=HU; Iceland=IS; Ireland=IE; Isle of Man=IM; Italy=IT; Kosovo=XK; Latvia=LV; Liechtenstein=LI; Lithuania=LT; Luxembourg=LU; Malta=MT; Moldova=MD; Monaco=MC; Montenegro=ME; Netherlands=NL; North Macedonia=MK; Norway=NO; Poland=PL; Portugal=PT; Romania=RO; Russia=RU; San Marino=SM; Serbia=RS; Slovakia=SK; Slovenia=SI; Spain=ES; Sweden=SE; Switzerland=CH; Ukraine=UA; United Kingdom=GB; Vatican City=VA
Oceania: Australia=AU; New Zealand=NZ; Fiji=FJ; Papua New Guinea=PG; Solomon Islands=SB; Vanuatu=VU; Samoa=WS; Tonga=TO; Kiribati=KI; Tuvalu=TV; Nauru=NR; Micronesia=FM; Palau=PW; Marshall Islands=MH; New Caledonia=NC; Guam=GU; Cook Islands=CK
Retired alias only: United States of America=US (North America; canonical projection `United States`)

Expand, reconciliation, activation and rollback

Phase A — this issue

1. Create the model/catalog and constraints in the next final accounts migration sequence.
2. Create exactly one profile for every preserved CustomUser. Absorbed, quarantined and inactive rows receive reversible, unconfirmed rows but are never readable as eligible profiles.
3. Seed each target from nonblank CustomUser columns first: certificate_name, mapped country, mapped registration_role -> professional_role, github_url, linkedin_url, personal_website_url -> website_url, about_me -> about, and validated preferred_timezone. Derive region; never trust/copy stored CustomUser.region as authority.
4. For a target still blank, select exactly one most recent linked historical CourseRegistration ordered by created_at DESC, pk DESC; map name -> certificate_name, company_name -> organization, country and role. Do not map course comment, consent, email, target or enrollment fields. Do not fall back to an older registration field-by-field.
5. Resolve absorbed registration owners through the accepted alias to the survivor before applying suggestions. A nonblank survivor/account value always wins. A broken alias, invalid/unknown/overlength value, disagreement, duplicate/collision or quarantined target is reported only by safe aggregate category; never truncate, guess, overwrite a nonblank value, or print a value/account/registration ID.
6. Use historical migration models and the selected database alias only. The reconciliation function is idempotent when invoked twice and reversible without changing any pre-existing CustomUser, allauth, identity, course, enrollment, registration or Person row.
7. Leave current CustomUser/account-settings/registration writers and readers active. MemberProfile is non-canonical and no route consumes it in this phase.

Phase B — #248

Move profile and account-settings mutations through this service. In the same transaction project the legacy-covered fields back to CustomUser: certificate name, canonical name-based country and derived region, professional role, URLs, about and preferred timezone. New-only work status/seniority/ambitions/why-joined have no invented legacy column. Keep dual-read comparison active and fail tests on direct adapter writes.

Phase C — later activation/contract

Canonical read activation is allowed only after all HTML, self API, Studio, admin API, timezone, course-registration and other inventory-listed readers/writers use the shared service; the versioned inventory reports zero unknown/direct writers; one complete production-like rehearsal and one deployed development observation report zero unresolved mappings and zero dual-read mismatches; forward/reverse/backup-restore evidence passes; and rollback has been rehearsed. Activation is a reversible flag/configuration step, not a column drop.

Rollback before activation returns readers/writers to the still-current CustomUser columns; the service's Phase-B projection makes those columns current while new-only profile values remain inert. Compatibility columns may be removed only by a separate reviewed contract issue after the rollback window; this issue never removes them or claims canonical activation.

Acceptance criteria

• The final dependency SHAs and final accounts migration leaf are recorded; the issue is implemented only after #224, #231, #234 and #51 are accepted/integrated.
• The exact model, choices, lengths, URL rules, deterministic backfill UUID namespace, constraints and timestamps above are implemented with no extra product fields.
• The 216-row mapping, six derived regions, XK handling, retired US alias, exact digest, unknown behavior and name-based reverse projection match this issue.
• Every preserved CustomUser receives one stable profile; apply-twice creates no duplicate or mutation, and supported reverse restores the exact pre-expand state.
• Reconciliation obeys CustomUser-first then one-most-recent-registration precedence, survivor aliases and fail-closed conflicts without truncation, fuzzy matching or sensitive output.
• Initial, incomplete-submit, first-completion, later-edit, support/migration-correction, stale/concurrent and transaction-rollback transitions match the pinned revision/confirmation contract.
• Confirmed-scalar reads return exactly the minimized immutable fields and fail closed for incomplete, stale-unconfirmed, unverified, inactive, quarantined, absorbed/broken-alias, colliding or mismatched subjects.
• No content.Person row/relation/permission changes in signup, migration, service read/write, failure or rollback scenarios.
• No current adapter is cut over, no CustomUser field is removed, and MemberProfile is not declared canonical in this child.
• Migrations, drift, focused tests, security/PII canaries, compatibility/adoption checks, container checks, and the graph-selected versioned verification plan pass through the portable SQLite application suite and the repository’s bounded exact-image/database-aware PostgreSQL migration boundary; no separate PostgreSQL-only application semantics are introduced.

Required test scenarios

1. Model/service boundaries for every status/role/seniority code; blank, trim, max and max+1; composed/decomposed Unicode and emoji; safe HTTP/HTTPS; hostname/userinfo/control/scheme/length failures; valid/invalid IANA timezone.
2. Deterministic UUID fixtures, one-to-one/constraint violations, stale revision, two concurrent writers, rollback during first completion, repeated confirmation, support correction followed by denied read then member reconfirmation.
3. Verified active/legacy survivor, absorbed valid alias, broken/cyclic alias, quarantine, inactive, normalized collision and email-verification mismatch.
4. Synthetic migration fixtures: account-only, registration-only, conflicting account/registration, blank, legacy role, invalid/overlength/unknown country, retired US alias, Kosovo, multiple registrations with equal timestamps/PK tie-break, absorbed source/survivor, quarantine and no linked user.
5. Forward/reverse/apply-twice through portable SQLite migration tests plus the repository’s bounded exact-image/database-aware PostgreSQL migration check; exact row/count/checksum preservation; aggregate-only reports; no raw email, country, URL, free text, account/registration key or other PII in logs/artifacts.
6. Assert zero Person queries/writes/links/permissions and zero network/external-job side effects.

Browser and render disposition

Backend only. No HTML, CSS, JavaScript, route, navigation, sitemap, serializer or OpenAPI change is in scope. Screenshots are not_applicable only if the computed change graph confirms no render impact; the independent tester still runs the graph-selected browser tier. Any rendered-surface change expands the issue and requires normal desktop/mobile screenshots and PM review.

Explicit non-goals

No profile/self route, signup/social orchestration, account-settings cutover, Slack grant/secret/Relay/EmailDelivery, course mutation or snapshot, Studio/admin/support UI/API, export/deletion/retention implementation, CloudFront trust/suggestion, Person relation, production import, protected-data inspection, network side effect, canonical activation, compatibility-column removal, new geography, fuzzy country matching, public directory, avatar, organization directory, arbitrary links, ranking or recommendation.

Start by reading the listed architecture, profile, migration, security, and verification documents, then confirm accepted merge SHAs for #224, #231, #234, and #51 and the final accounts migration leaf. Implement and test the accounts.MemberProfile model, service, compatibility projection, and reversible reconciliation only after the dependencies are integrated; done means the stated validation, revision, privacy, and migration contracts pass without activating a product route.