Person–auth decoupling — design (v1)

Status: implemented (2026-06-12) — all five steps; see also the coupling found during implementation beyond the original inventory: every API feature service treated principal.id as the person id. Resolved by making AuthenticatedPrincipal.personId (from the stamped claim) a required field, with verification denying sessions that lack the claim. Decisions: ADR-0013 (person-rooted identity), ADR-0014 (erasure) Blocks: OneRoster consumer (ADR-0012), guardian modeling, rostered-without-login persons

Goal and invariants

Make person the identity root and demote Supabase auth to one identity_binding kind, with these invariants:

1. Supabase remains the only authentication authority. No parallel credential store, no new token issuer, no secrets on person. This design adds zero security-critical surfaces: the one new datum (a person_id claim) flows through the same service-role-only raw_app_meta_data channel that already carries the authorization grants claim.
2. A Person may exist with zero auth bindings (SIS-rostered learners, guardians).
3. An auth subject resolves to at most one Person, via identity_binding (provider = 'supabase-auth').
4. person.id is always minted independently (gen_random_uuid()); nothing may assume it equals the auth uid. Greenfield context: there is no production data — the dev database is dropped and the migration set regenerated to express the end state directly. No transitional migrations, backfills, or fallback reads.

Current coupling inventory (every place person.id == sub is assumed)

#	Location	Assumption
1	person.id FK → auth.users.id ON DELETE CASCADE (20260610080517_supabase_auth_hooks)	person cannot pre-exist auth; auth deletion is an unaudited shadow-erasure path bypassing scrub/LRS/receipt
2	handle_auth_user_created trigger (same migration)	mints person with id = NEW.id
3	refresh_person_authorization_claim (20260610080519_authorization_claim_projection)	UPDATE auth.users ... WHERE auth_user.id = target_person_id
4	packages/db/src/schema/claim-aware-policies.ts	reads sub via request.jwt.claim.sub and the claims-JSON channel
5	packages/db/src/schema/learning-structure.ts readEscapedUserId	custom row-filter builders read claims.sub (offering, assessment_definition)
6	@pgxsinkit/contracts buildRowFilterWhere (config.ts)	ownership filter hardwires claims.sub
7	erasure executor (packages/db/src/erasure/)	deletes person but never auth.users — the subject’s email survives erasure today

Target architecture

auth.users (Supabase, authn only)
    │  identity_binding: provider='supabase-auth',
    │  issuer=<SUPABASE_URL>, external_subject=<auth uid>
    ▼
person (root UUID) ──── person_profile / memberships / history …
    ▲
    └── other bindings: lti, oneroster-sourcedId, clr-subject, did (ADR-0013)

Resolution is claim-stamped, never queried per row. At auth-user creation (and binding changes), a SECURITY DEFINER function resolves the Person through the binding and stamps raw_app_meta_data.person_id, exactly as refresh_person_authorization_claim stamps authorization today. RLS policies and Electric/pgxsinkit shape filters read the claim; the binding table is consulted only at stamp time. app_metadata is writable only by the service role, so the claim is exactly as trustworthy as sub itself.

Upstream change: pgxsinkit claim selection

buildRowFilterWhere (packages/contracts/src/config.ts) currently emits "col" = '<claims.sub>'. Add an optional, backward-compatible claim selector:

ownership?: {
  column: string;
  /** Dot-path into the JWT claims used as the owner value. Default: "sub". */
  claim?: string;          // e.g. "app_metadata.person_id"
};

• Path lookup over the claims object with the existing escaping; absent claim ⇒ the existing 1 = 0 deny behavior.
• shared.ownerColumn composition unchanged.
• Ship as a minor version; emergent bumps @pgxsinkit/contracts/server and sets claim: "app_metadata.person_id" on the ownership filters. Per repo policy this lands upstream first — no app-layer workaround.

Trigger: resolve-or-mint

handle_auth_user_created v2 (replaces mint-always):

-- 1. resolve: existing binding wins (pre-provisioned person, invite-created binding)
SELECT person_id INTO bound_person
FROM identity_binding
WHERE provider = 'supabase-auth' AND external_subject = NEW.id::text
  AND (effective_until IS NULL OR effective_until >= now());

-- 2. mint: no binding → new person with its OWN uuid + binding + profile
IF bound_person IS NULL THEN
  INSERT INTO person (id) VALUES (gen_random_uuid()) RETURNING id INTO bound_person;
  INSERT INTO identity_binding (person_id, provider, issuer, external_subject, effective_from)
  VALUES (bound_person, 'supabase-auth', <issuer>, NEW.id::text, now());
  INSERT INTO person_profile (...) ON CONFLICT DO NOTHING;  -- as today
END IF;

-- 3. stamp claims (person_id + authorization, both resolved via bound_person)
PERFORM public.refresh_person_identity_claim(NEW.id, bound_person);

The projection function (#3 in the inventory) changes its UPDATE keying from auth_user.id = target_person_id to a join through identity_binding, and stamping moves behind one helper so person_id and authorization never disagree.

Linking rules (the one security-critical decision)

When an auth signup arrives and a Person may already exist for that human:

Path	Rule	Result
Self-signup, no binding, no candidate	—	Mint new Person
Invite (current issueSupabaseInviteUser flow)	inviter names the Person; the invite endpoint creates the binding at invite time, before first login	Resolve — deterministic, no heuristics
Pre-provisioned (SIS/guardian) + claim token	token explicitly identifies the Person	Resolve
Pre-provisioned + email match	link only if email_confirmed_at is set and the Person was provisioned with exactly that email and the Person has no active auth binding	Resolve, else fall through
Anything else	—	Mint + enqueue admin merge review

Hard rules: never link on an unverified email (pre-provisioned-account takeover); binding rows are created only by service-role paths (trigger, invite endpoint, SIS consumer); linking after a session exists requires a token refresh for the claim to appear (same property as the grants claim — invite-acceptance must force refresh). This is ADR-0013’s “import is correlation, not transfer” applied to signup.

Implementation status (2026-06-12): rows 1–2 (mint; invite-resolve via binding) are live in trigger v2. Rows 3–4 (claim token; verified-email match) and the merge-review producers are assigned to the top of the OneRoster consumer milestone — they require person-side roster/contact data that only exists once the consumer lands, and the email-match path additionally needs an email_confirmed_at UPDATE trigger (confirmation happens after the INSERT). The receiving structure for row 5, identity_merge_review, is shipped (see Deferred). Design settled (2026-06-12, later the same day): rows 3–4 and the producers are fully specified in identity-linking-v1.md — evidence lives in a new person_contact_point core entity, both paths share a pristine-guard rebind primitive, and the email-match rule generalizes to the evidence-class principle.

Claims: direct read, no fallback

Policies and the two custom row filters read app_metadata.person_id directly (both channels in claim-aware-policies.ts); pgxsinkit ownership filters set claim: "app_metadata.person_id". There is no sub fallback: a token without the claim matches nothing (1 = 0), which is the correct deny behavior for a session issued before its person existed — the client refreshes and retries.

Erasure integration

• Dropping FK #1 closes the shadow-erasure path: deleting an auth user no longer destroys learning history outside the audited executor.
• The executor gains an auth cleanup output, mirroring storageCleanup: the active supabase-auth external_subjects collected from bindings pre-delete are returned as authSubjectsToDelete; the wrapping endpoint must call the Supabase admin delete-user API for each (cannot run inside the DB transaction). Runbook gains the step; the receipt stays non-personal (subjects are NOT recorded in it).
• Until this ships, erasure leaves the subject’s email in auth.users — a known Phase-1 gap, closed here.

Implementation sequencing (greenfield — fresh migration set)

1. pgxsinkit: ownership claim path option; publish; bump in emergent.
2. Schema + generators to end state: person.id loses the FK to auth.users and gains defaultRandom() (identity-context.ts); the auth-hooks generator (generate-supabase-auth-hooks-migration.ts) emits trigger v2 (resolve-or-mint + claim stamp); the claim-projection generator re-keys its auth.users UPDATE through identity_binding and stamps person_id alongside authorization; claim-aware policies and the two custom row filters read the claim. Drop the dev database and regenerate the migration set from scratch.
3. Seeds and tests adapt to the permanent contract: anything that assumed person.id == auth uid resolves the person via the binding instead — run-seed (domain rows for admin-created auth users) and the erasure integration fixtures (which insert person rows by auth id today).
4. Apps: invite endpoint creates Person + binding explicitly; invite-acceptance forces session refresh; admin merge queue (can trail). Implementation note: the trigger mints person + binding + claims when Supabase’s invite API inserts the auth user — before any session exists — so acceptance sessions carry the claim by construction and no explicit refresh hook was needed. The endpoints resolve the invited person through the binding and the inviter through principal.personId.
5. Erasure: authSubjectsToDelete output + endpoint-side admin deletion + runbook update + integration-test assertion that auth.users is empty of the subject post-erasure.

Test plan

• Unit: policy builders emit the person_id-claim form; trigger v2 resolve-or-mint logic (pgTAP-style or via integration); pgxsinkit claim-path unit tests upstream.
• Integration (extend the erasure suite pattern): self-signup mints person.id != sub with binding; pre-provisioned person + invite-created binding resolves (no second person); RLS smoke — learner sees own rows via the person_id claim and a claimless token sees nothing; erasure removes the auth user and the binding.
• E2E: signup → learn → invite-accept flows on the existing Playwright lane.

Deferred (recorded, not blocking)

• Admin merge tooling: the receiving structure shipped 2026-06-12 (identity_merge_review — pending/merged/dismissed lifecycle, cascade from either side of the suspected-duplicate pair, erasure-classified). Producers land with the OneRoster consumer; the admin-console panel trails behind real queue traffic.
• Multiple concurrent auth bindings per person (model supports it; policy is single-active until SSO demands otherwise).

Resolved after implementation

• Issuer string (2026-06-12): supabase-auth bindings take their issuer from public.identity_issuer(), which reads the per-environment app.identity_issuer database setting and falls back to the logical constant 'supabase' where unset (dev, integration). Infra owns the per-environment value; no environment-specific issuer ever lives in the repo. Resolution keys on provider + external_subject, so issuer remains disambiguating metadata. See docs/runbooks/identity-issuer.md.