IDENTITY-MODEL-CANONICAL-01

Brief: IDENTITY-MODEL-RATIONALISATION-01 Phase 2 deliverable. Filed: 2026-05-27 by Alex. Status: Phase 2 design draft; awaiting Gate 3 review. Scope: Canonical user/identity schema model. Schema shape only; DB placement deferred to OPS-DB-SPLIT-SHAPE-DECISION-01 per Tim's Gate 1 decision. Anchors against: ADR-007 (user/credential separation), ADR-008 (no platform tier), ADR-012 (signup/admin-authority decoupling), ADR-020 (T3/T4/T4A access-control), ADR-021 (plan/entitlement/metering), ADR-022 (operator impersonation pattern), ADR-023 (graceful degradation).

---

1. Executive summary

The canonical identity model resolves four key conventions, six L3-truth sources, four cross-DB duplicates, and a tenants-tier residue into four substrate tables:

• users — single canonical user identity, UUID-keyed, status lifecycle per ADR-023
• tier_grants — T3/T4/T4A role attachment (replaces operator_roles + user_tenant_roles + admin_sessions tier + orgs.billing_tier shortcut + access_allowlist's L3-grant function)
• credentials — generic credential reference per ADR-007 separation (subsumes passkey_credentials; accommodates CREDENTIAL-PROVIDER-DECISION-01's provider mix)
• magic_link_allowlist — issuance gate (renamed from access_allowlist; semantically narrowed)

Plus reshapes of two existing tables (impersonation_tokens, portal_invites) to drop tenant_id → client_id.

Plus retirements: tenants, user_tenant_roles, operator_roles, admin_sessions, both copies of users cross-DB duplicate, both copies of magic_tokens, both copies of passkey_credentials, both copies of products (workspace canonical wins).

Plus the ADR-008 five-orphan drop (separate brief OPS-DB-IDENTITY-ORPHAN-CLEANUP-01).

The migration is per-row, not per-table — within today's users table, both UUID-keyed and prefix-keyed rows coexist; the migration normalises each row to the canonical UUID convention while preserving identity continuity.

This design is schema shape only. Phase 2 commits to the table shapes; OPS-DB-SPLIT-SHAPE-DECISION-01 commits to which DB hosts each table.

---

2. Canonical user identity model

2.1 The users table

CREATE TABLE users (
  id                 TEXT PRIMARY KEY,
  email              TEXT UNIQUE NOT NULL,
  display_name       TEXT,
  email_verified     INTEGER NOT NULL DEFAULT 0,
  client_id          TEXT REFERENCES clients(id),
  status             TEXT NOT NULL DEFAULT 'active'
                       CHECK (status IN ('active','deactivated')),
  created_at         INTEGER NOT NULL DEFAULT (unixepoch()),
  last_seen_at       INTEGER,
  deactivated_at     INTEGER
);
CREATE INDEX idx_users_client_id ON users(client_id);
CREATE INDEX idx_users_status ON users(status);

Notes: - id is UUID (RFC 4122 36-char hyphenated). Generated via crypto.randomUUID() at signup. The zero-UUID admin (00000000-0000-0000-0000-000000000001) remains valid under this format — see §10 on the zero-UUID disposition. - email UNIQUE — one user per email. Email changes are explicit operations (UPDATE), not new rows. - client_id FK — T4 administrators and T4A users attach to a single client file (per ADR-020 + ADR-008). T3 operators have client_id = NULL (operators do not belong to clients). - status enum — active (default) and deactivated (per ADR-023 deactivate-not-delete). Deactivation preserves identity; reactivation restores access. - INTEGER timestamps via unixepoch() — matches current workspace-db.users convention (the canonical-by-content side). Phase 2 standardises on this for new schema. - No org_id column. The historical org_id field in workspace-db.users is replaced by client_id (orgs collapse into clients per ADR-008).

2.2 Why UUID-keyed

Four conventions in current substrate; one survives:

Convention	Why not canonical
Email-keyed	Email changes break references; multiple users sharing an email impossible to encode
Prefix-keyed (usr_*)	Cannot be runtime-generated by crypto.randomUUID() — would force manual ID assignment at signup
Randomblob hex	Equivalent to UUID without the hyphen format — minor cosmetic difference, no advantage
UUID (RFC 4122)	Universal standard; runtime-generatable; renames don't break references; matches zero-UUID admin pattern; broad library support

The mixed-conventions-within-table finding from audit §4 directly motivates this choice — the current crypto.randomUUID() calls in apps/workspace/app/auth/verify/route.ts already produce canonical UUIDs for new signups. Only the seeded rows (usr_tim, usr_jimmy, user_ucca_tim, user_ucca_john) need per-row migration.

---

3. Role-attachment model (T3/T4/T4A)

3.1 The tier_grants table

CREATE TABLE tier_grants (
  id              TEXT PRIMARY KEY,
  user_id         TEXT NOT NULL REFERENCES users(id),
  tier            TEXT NOT NULL CHECK (tier IN ('T3','T4','T4A')),
  client_id       TEXT REFERENCES clients(id),
  granted_by      TEXT REFERENCES users(id),
  granted_at      INTEGER NOT NULL DEFAULT (unixepoch()),
  revoked_at      INTEGER,
  notes           TEXT,
  UNIQUE(user_id, tier, client_id)
);
CREATE INDEX idx_tier_grants_user ON tier_grants(user_id);
CREATE INDEX idx_tier_grants_active ON tier_grants(user_id, tier, client_id) WHERE revoked_at IS NULL;

Notes: - tier enum is T3/T4/T4A per ADR-020. No L1/L2 — those values do not exist in the canonical model. - client_id scopes the grant. T3 grants have client_id = NULL (operators are not scoped to any client). T4 and T4A grants have client_id = <some client> (administrator role or user identity is per-client). - UNIQUE(user_id, tier, client_id) — a user has at most one active grant per (tier, client) pair. Multi-administrator extension naturally accommodated: same user can hold T4 grants on multiple client_ids. - revoked_at for soft revocation — grants are deactivated, not deleted, preserving audit trail (per ADR-023 + ADR-022 audit-trail discipline). - granted_by FK to users — accountability for who promoted whom. T3 grants are typically granted_by = NULL (substrate-seeded, not granted by another user).

3.2 The T4 / T4A separability

Per ADR-020: T4 administrator role and T4A user identity are separable concepts not separable humans. The same human typically holds both.

Schema expression: The same user_id can have two grants for the same client_id: - One grant with tier='T4' (administrator authority) - One grant with tier='T4A' (the countable user identity that consumes plan seats per ADR-021)

The UNIQUE(user_id, tier, client_id) constraint permits this because tier values differ.

For a one-person RTO, the same human holds both T4 and T4A grants on the same client. The administrative authority position (T4) and the operational seat (T4A) are distinct grants; the human happens to hold both.

For a multi-person RTO with one administrator and three users, the administrator has both T4 + T4A grants; the three users have T4A grants only. Plan-seat counting (per ADR-021) counts T4A grants, not humans.

3.3 Multi-administrator extension (designed-in, not built)

ADR-020 says multi-administrator is designed-in but not built initially. The schema accommodates it without redesign:

A client can have multiple T4 grants (different user_id, same client_id, both tier='T4'). The UNIQUE constraint permits this. Role-differentiated administrators (Finance Admin, Education Admin, Compliance Admin) are not encoded as a CHECK constraint at this layer — that's an admin-permission-model design downstream. The schema supports multiple T4 grants; the UI initially exposes only one.

3.4 What this replaces (six L3-truth sources → one)

Per audit §5, the substrate has six parallel L3-truth sources. Canonicalisation:

Audit source	Canonical replacement	Rationale
(1) CF Access JWT	Authentication mechanism, not tier source. CF Access JWT's email is looked up against tier_grants to determine tier.	CF Access remains the DNS-level gate per ADR-019. Identity ↔ authentication separation per ADR-007.
(2) operator_roles (ops-db)	Replaced by tier_grants with tier='T3'.	Generalises beyond operators to all tier assignments.
(3) access_allowlist (ops-db)	Repurposed as magic_link_allowlist (issuance gate only).	Different concern from tier grant; see §5.
(4) user_tenant_roles (workspace-db)	Replaced by tier_grants. L1-L4 enum retires per §9 mapping.	Single tier-grant source.
(5) admin_sessions.tier hardcoded	Replaced by tier-resolution at session-build time (read from tier_grants).	Tier is data, not a session-build constant.
(6) orgs.billing_tier='internal' shortcut	Retired. Billing config decoupled from access control.	Tim's audit §10 reading: do not couple commercial config to authority.

One canonical source for "what tier is this user?": tier_grants. One authentication mechanism: provider-specific credential verification (CF Access at admin surface; magic-link or future passkey at workspace surface).

---

4. Credential reference model

4.1 The credentials table

CREATE TABLE credentials (
  id                 TEXT PRIMARY KEY,
  user_id            TEXT NOT NULL REFERENCES users(id),
  provider           TEXT NOT NULL,
  external_id        TEXT NOT NULL,
  metadata           TEXT,
  enrolled_at        INTEGER NOT NULL DEFAULT (unixepoch()),
  last_used_at       INTEGER,
  revoked_at         INTEGER,
  status             TEXT NOT NULL DEFAULT 'active'
                       CHECK (status IN ('active','revoked','expired')),
  UNIQUE(provider, external_id)
);
CREATE INDEX idx_credentials_user ON credentials(user_id);
CREATE INDEX idx_credentials_provider ON credentials(provider, status) WHERE revoked_at IS NULL;

Notes: - provider is opaque text — accommodates future providers per ADR-006 (CF Access, passkey, magic-link, OIDC, federation, whatever lands). Initial values: 'cf-access', 'passkey', 'magic-link'. - external_id is provider-specific. For CF Access this is the verified email; for passkey this is the WebAuthn credential_id; for magic-link this is the KV token hash (when migrated from KV-only to D1-mirrored). - metadata TEXT (JSON) — provider-specific properties (passkey aaguid, magic-link expiry, CF Access policy id). Schema-flexible; not load-bearing for queries. - UNIQUE(provider, external_id) — prevents duplicate enrolment of the same credential for the same provider. - status enum — active / revoked / expired. Composes with ADR-023 lifecycle (credentials revoke when user deactivates; reactivate on user reactivation per Phase 3 migration).

4.2 What this replaces

• ops-db.passkey_credentials → migrate to credentials with provider='passkey', external_id=credential_id, metadata={aaguid, public_key, sign_count}. The two existing rows (zero-UUID admin's never-verified passkeys) preserve as historical credential records with status='active' or status='revoked' depending on ADMIN-AUTH-MODEL-RECONCILIATION-01 outcome.
• workspace-db.passkey_credentials → empty in current substrate; no migration. Drop.
• Both magic_tokens tables → dead schema (audit §6 finding); the magic-link flow uses KV (SESSION_KV with MAGIC_PREFIX). The credentials table does NOT replace magic-token KV storage — KV is the right tool for short-lived single-use tokens. The credentials row pattern for magic-link is issuance audit: one credential row per magic-link issued, marking the email-link binding for audit. This is optional and depends on whether ADMIN-AUTH-MODEL-RECONCILIATION-01 wants magic-link issuance audit-trailed.

4.3 ADR-007 compliance

ADR-007 separates user from credential at the schema level. This design honours it:

• A user has zero or more credentials (credentials.user_id FK)
• A credential authenticates exactly one user (FK is single-valued)
• Adding a new credential type (federation, OIDC, etc.) is a new provider value, not a schema migration
• Credential rotation is an UPDATE on credential status; user identity unchanged

---

5. Auxiliary tables

5.1 magic_link_allowlist (renamed from access_allowlist)

CREATE TABLE magic_link_allowlist (
  id              TEXT PRIMARY KEY,
  type            TEXT NOT NULL CHECK (type IN ('email', 'domain')),
  value           TEXT NOT NULL UNIQUE,
  label           TEXT,
  added_by        TEXT NOT NULL REFERENCES users(id),
  added_at        INTEGER NOT NULL DEFAULT (unixepoch()),
  is_active       INTEGER NOT NULL DEFAULT 1
);

Purpose (semantically narrowed from current access_allowlist): gates magic-link issuance only. Determines whether apps/workspace/app/api/auth/magic-link/route.ts will send a magic-link email to a given address. Does NOT grant any tier.

Flow change: Today, access_allowlist membership implicitly conferred L3 — see audit §5. The canonical model decouples: 1. Issuance: magic-link request → check magic_link_allowlist → issue token to KV. (No tier consequence.) 2. Verification: magic-link verify → look up tier_grants for tier. (Issuance allowlist not consulted at this stage.)

This means an email can be on the issuance allowlist but have no tier grant — they'd authenticate but have no privileges. Or an email can have a tier grant but not be on the issuance allowlist — they'd need to authenticate via a different provider (CF Access for admin domain).

Schema changes from current access_allowlist: added_by becomes FK to users; added_at and is_active retain. The renamed table semantically reflects the narrowed role.

5.2 impersonation_tokens (reshape — drop tenant_id, add client_id)

Current schema (workspace-db, audit §3.2):

-- CURRENT
CREATE TABLE impersonation_tokens (
  id              TEXT PRIMARY KEY,
  actor_id        TEXT NOT NULL REFERENCES users(id),
  target_user_id  TEXT NOT NULL REFERENCES users(id),
  tenant_id       TEXT NOT NULL REFERENCES tenants(id),
  target_role     TEXT NOT NULL,
  expires_at      INTEGER NOT NULL,
  created_at      INTEGER NOT NULL DEFAULT (unixepoch()),
  used_at         INTEGER
);

Canonical reshape:

CREATE TABLE impersonation_tokens (
  id              TEXT PRIMARY KEY,
  actor_id        TEXT NOT NULL REFERENCES users(id),
  target_user_id  TEXT NOT NULL REFERENCES users(id),
  client_id       TEXT NOT NULL REFERENCES clients(id),
  target_tier     TEXT NOT NULL CHECK (target_tier IN ('T4','T4A')),
  expires_at      INTEGER NOT NULL,
  created_at      INTEGER NOT NULL DEFAULT (unixepoch()),
  used_at         INTEGER,
  reason          TEXT
);

Changes: - tenant_id → client_id — per ADR-008 tenants retire to clients. - target_role → target_tier with CHECK enum — narrows to T4/T4A (a T3 operator impersonating into a client substrate impersonates a T4 admin or T4A user; impersonating another T3 is meaningless). - New reason column — per ADR-022 "every T3 access to a T4 client substrate is logged with reason, actor, duration, and scope." reason captures the support-ticket or incident context.

The table is the canonical implementation of ADR-022 (already present in current substrate; this design ratifies the shape and adjusts terminology).

5.3 portal_invites (reshape — drop tenant_id, add client_id)

Current schema (audit §3.2): tenant_id TEXT NOT NULL. Canonical reshape: client_id TEXT NOT NULL REFERENCES clients(id). Role enum stays ('read_only' default, expanded by future admin-permission design).

Composes with ADR-012 signup flow: invitations are scoped to a client; the invited user becomes T4A in that client when accepted.

---

6. Migration strategy — per-row, not per-table

6.1 Rationale

Audit §4 finding: the four conventions are mixed within identity surfaces. workspace-db.users has both UUID-keyed rows (e.g. 00000000-...-0001, 89fe66e7-...) and prefix-keyed rows (user_ucca_tim, user_ucca_john). A per-table migration approach (copy whole table to canonical shape) would still leave mixed conventions; a per-row approach normalises each row.

6.2 Per-row migration sequence

For each existing identity row, the migration applies:

1. Generate canonical UUID if the current id is not already UUID format. The mapping is captured in a temporary id_migration_map table:

   CREATE TABLE id_migration_map (
     old_id           TEXT NOT NULL,
     new_id           TEXT NOT NULL,
     source_table     TEXT NOT NULL,
     migrated_at      INTEGER NOT NULL DEFAULT (unixepoch()),
     PRIMARY KEY (source_table, old_id)
   );
   

2. Resolve cross-DB duplicates row-by-row. For users: workspace-db is canonical-by-content (4 rows vs ops-db's 2 vestigial rows). The ops-db rows (usr_tim, usr_jimmy with @ucca.online emails) get either migrated as new rows (if a corresponding workspace-db entry doesn't exist for that human) or merged (if it does — match by current-known-good email mapping).
3. Rewrite FKs. Every table referencing the migrated id (FK references to users.id, passkey_credentials.user_id, account_memberships.user_id, etc.) gets updated via the id_migration_map. This is staged per FK source table.
4. Populate tier_grants from the six L3-truth sources:
5. One T3 grant per active operator_roles row (rewriting user_id via map)
6. One L3-equivalent T3 grant for any user_tenant_roles.role='L1' OR 'L3' row
7. Implicit T3 grants are NOT created for access_allowlist entries — those become magic_link_allowlist (issuance gate only)
8. admin_sessions.tier is not consulted (it's a session log, not authority)
9. orgs.billing_tier='internal' is not consulted (decoupled)
10. Migrate credentials. Each ops-db.passkey_credentials row → credentials row with provider='passkey'. The zero-UUID admin's two passkey rows preserve as historical credentials with status='revoked' (since sign_count=0 means never used; ADMIN-AUTH-MODEL-RECONCILIATION-01 confirms final disposition).
11. Update FKs in impersonation_tokens and portal_invites to use client_id from the tenant→client mapping. Current substrate has 0 rows in both; trivial.
12. Drop the eight retired tables (in dependency order): user_tenant_roles, tenants, account_memberships, account_worlds, accounts, custom_roles, customers (the five orphans, per OPS-DB-IDENTITY-ORPHAN-CLEANUP-01), operator_roles, admin_sessions, ops-db.passkey_credentials, both magic_tokens, both products copies (workspace canonical wins). And both cross-DB users copies are consolidated to one canonical users table.

6.3 Migration phase split (Phase 3 work, not Phase 2)

Phase 3 (separate brief, IDENTITY-MODEL-MIGRATION-01) executes the steps above. Phase 2 commits to the schema shape. The migration is non-trivial and earns its own brief and gate discipline.

6.4 Mapping the small concrete dataset

Today's identity rows total roughly: - ops-db: 2 users + 1 client + 2 orgs + 1 operator_role + 1 admin_profile + 1 admin_nav_pref + 2 passkey_credentials + 1 billing_customer + 3 orphan-with-data + 6 access_allowlist + 1 each user_prefs/products = ~22 rows touched - workspace-db: 4 users + 1 tenant + 1 user_tenant_role + 1 admin_session + 5 contacts + 10 contact_keys + 10 people + 2 pc_persons + 5 products = ~39 rows touched (some out of identity scope — people/pc_persons are domain identity, not platform identity, but FK-relevant)

Total migration footprint < 70 rows. The migration is small in data terms; the complexity is in FK-graph navigation and code-path updates, not row volume.

---

7. Cross-DB duplicate disposition (four duplicates)

Per audit §6:

Table	Disposition
users	Consolidate to one canonical table. Per-row migration per §6.2 step 2. Final location: TBD (OPS-DB-SPLIT-SHAPE-DECISION-01).
products	Drop ops-db copy. Workspace canonical (5 rows). Out of identity scope per brief §3 — folded into composition note for CROSS-DB-DUPLICATE-PRODUCTS-01.
passkey_credentials	Both retire. Migrate ops-db rows to new credentials table with provider='passkey'. workspace-db copy is empty; drop.
magic_tokens	Both drop (dead schema; flow uses KV per audit §6). Folded into composition note for CROSS-DB-DUPLICATE-MAGIC-TOKENS-01 (the new fourth duplicate brief; previously not filed).

---

8. Tenants table retirement

Per audit §3.2 / ADR-008: tenants is the historical UCCA platform tier. Retire entirely.

Three references migrate: - user_tenant_roles — entire table retired (subsumed by tier_grants) - impersonation_tokens.tenant_id — rename to client_id per §5.2 - portal_invites.tenant_id — rename to client_id per §5.3

Then DROP tenants table. Single tenant row (00000000-...-0100, slug rtopacks) is implicit in being-the-only-tenant; the rename to clients-shape is semantically equivalent.

---

9. Tier vocabulary mapping — L1-L4 → T3/T4/T4A

9.1 Direct mapping table

UCCA-lineage value	Canonical ADR-020 value	Substrate presence today	Migration action
L1	T3 (operator)	1 row: zero-UUID admin in user_tenant_roles	Migrate to tier_grants(tier='T3', client_id=NULL)
L2	(retired, no equivalent)	0 rows	No migration; remove from enum
L3	T3 (operator)	admin_sessions.tier='L3' (1 row); apps/workspace/lib/tier-resolution.ts returns 3 for operator_roles hits	Migrate to tier_grants(tier='T3'); retire admin_sessions tier column entirely
L4	T4 (administrator)	0 rows in substrate (groups empty); lib/tier-resolution.ts would return 4 if admin/owner group membership existed	Tier-resolution.ts updated to check tier_grants(tier='T4') directly
L4.5 (workspace ucca_layer: 4.5)	T4A (user)	0 explicit rows; tier-resolution.ts default	tier_grants(tier='T4A') becomes the explicit default for client-attached users

9.2 What ucca_layer becomes in session shape

apps/workspace/lib/session-types.ts defines ucca_layer: 1 | 2 | 3 | 4 | 4.5. Canonical session shape replaces:

// CURRENT
interface RTPSession {
  ucca_layer: 1 | 2 | 3 | 4 | 4.5;
  // ...
}

// CANONICAL
interface RTPSession {
  tier: 'T3' | 'T4' | 'T4A';
  client_id: string | null;  // NULL for T3; required for T4/T4A
  // ...
}

The numeric ucca_layer field becomes textual tier; the implicit org_id-vs-tenant_id muddle resolves to explicit client_id matching the FK shape. Phase 3 migration includes session-shape update; consumers using the ucca_layer field need code updates.

9.3 Why retire L1/L2 entirely

Audit §9 sub-finding: L1 and L2 only exist as schema enum values (user_tenant_roles.role CHECK ('L1','L2','L3','L4')) plus 1 historical row (zero-UUID admin with L1). They have no operational semantics in current code beyond "even more senior than L3" — meaning in practice they decorate the platform-operator identity.

Per ADR-020, T3 is the top tier; there is no T2/T1/T0. The platform operator is T3, period. The zero-UUID admin's L1 row migrates to T3.

---

10. Zero-UUID admin pattern — preserve the row, retire the special-casing

The value 00000000-0000-0000-0000-000000000001 appears across 4 tables in current substrate (per audit §4 sub-finding). Phase 2 disposition:

Preserve the row; retire the special-casing.

• The UUID is a valid RFC 4122 UUID. It happens to be all-zeros-except-the-last-octet; nothing in the spec or in canonical convention prohibits or privileges this. As a users.id value it works identically to any other UUID.
• Any code path that depends on the literal value 00000000-...-0001 (e.g. if (user_id === '00000000-...-0001') { ... } would be a hidden special-case) migrates to instead query tier_grants(tier='T3') for the operator identity.
• The convention of "zero-UUID = platform-anchor identity" survives as a substrate observation, not a runtime invariant. Future operators added to the substrate get standard crypto.randomUUID() values; the zero-UUID admin is the historically-first row, not a structurally-privileged record.

Phase 3 migration action: scan worker code for literal references to the zero-UUID. If any exist, migrate to tier_grants lookup. Audit §5 grep already surfaced apps/admin/app/api/admin/me/route.ts line 22 fallback — that fallback is a different concern (default email for dev mode); not zero-UUID dependency.

---

11. Composition notes with downstream briefs

11.1 OPS-DB-SPLIT-SHAPE-DECISION-01

Phase 2 commits to schema shape only; DB placement is OPS-DB-SPLIT-SHAPE-DECISION-01's territory. Three placement candidates the split-shape decision will pick from:

• Option A — All identity tables in workspace-db. Continues current direction (workspace-db is the canonical-by-content side for users). Splits the ops-db ↔ workspace-db identity tangle by collapsing to workspace-db.
• Option B — All identity tables in ops-db. Inverts current direction; brings identity under ops-side governance (ops-db is currently operator-facing per HARD SEPARATION RULE; identity authority arguably belongs there).
• Option C — New identity-dedicated DB (rto-identity-db or similar). Separates identity from both ops and workspace concerns. New D1.

Phase 2 design works in any of the three placements. The schema shape is portable.

11.2 ADMIN-AUTH-MODEL-RECONCILIATION-01

This design's credentials table accommodates passkey, magic-link, CF Access, and future providers (federation, OIDC). ADMIN-AUTH-MODEL-RECONCILIATION-01 decides: - Whether to preserve current passkey scaffolding (sign_count=0 on both rows — never verified) - Whether to wire actual passkey verification at admin session-start - Whether to drop passkey entirely in favour of CF Access + future OAuth/OIDC

The schema accommodates all three outcomes. Phase 3 migration preserves the two passkey rows as historical credentials; ADMIN-AUTH-MODEL-RECONCILIATION-01 decides their final status.

11.3 CREDENTIAL-PROVIDER-DECISION-01

The provider column in credentials is opaque text. CREDENTIAL-PROVIDER-DECISION-01 picks which providers fill which gaps; new provider values land without schema change. The brief's outcome may add an enum constraint at that point if the provider list stabilises, but Phase 2 design keeps it opaque to avoid premature constraint.

11.4 Cross-DB duplicate sub-briefs (four)

Brief	Disposition under this design
CROSS-DB-DUPLICATE-USERS-01	Subsumed — handled by §6.2 per-row consolidation
CROSS-DB-DUPLICATE-PRODUCTS-01	Out of identity scope; remains as standalone brief (drop ops-db.products)
CROSS-DB-DUPLICATE-PASSKEY-CREDENTIALS-01	Subsumed — handled by §4.2 migration to credentials table
CROSS-DB-DUPLICATE-MAGIC-TOKENS-01 (new fourth — per audit §6)	File new sub-brief: drop both magic_tokens tables (dead schema). Tiny.

11.5 OPS-DB-IDENTITY-ORPHAN-CLEANUP-01

Per ADR-008 consequence #1. Drops the five orphan tables (accounts, account_worlds, account_memberships, custom_roles, customers). Audit §7 confirms safe drop. This design composes cleanly — none of the canonical tables FK to the orphans.

11.6 ORGS-DUPLICATE-CLEANUP-01 (out of scope, flagged)

Audit surfaced org_rtopacks_ops and org_ucca_45329 as duplicate orgs for the same RTO. Out of identity-rationalisation scope (it's an orgs problem, not a users problem). Flagged for separate brief or fold into OPS-DB-SPLIT-SHAPE-DECISION-01.

---

12. ADR-024 — Canonical user-identity schema model (decision at Gate 3)

The audit found that ADR-007 (separation principle) + ADR-020 (tier model) + ADR-021 (plan attachment) compose into the design above, but do not themselves specify:

• The schema-level shape (users / tier_grants / credentials tables)
• The UUID-keyed canonical key convention
• The tier_grants consolidation of six L3-truth sources
• The provider-opaque credential reference model
• The L1-L4 → T3/T4/T4A migration mapping
• The zero-UUID admin disposition principle (preserve row, retire special-casing)

These are substrate commitments beyond what ADR-007/020/021 say. Recommendation: file ADR-024 — Canonical user-identity schema model.

ADR-024 draft scope: 1. Context. Four conventions, six L3-truth sources, two parallel cross-DB user surfaces, L1-L4 lineage. The substrate accreted identity per-feature; canonical model needed. 2. Decision. Three-table canonical model: users (UUID-keyed) + tier_grants (T3/T4/T4A) + credentials (provider-opaque). Plus magic_link_allowlist for issuance, plus impersonation_tokens / portal_invites reshape. 3. Consequences. Per-row migration; six L3-truth sources collapse to one (tier_grants); ADR-007 honoured at schema level; multi-administrator extension designed-in; zero-UUID admin preserved-row-retire-special-casing.

Tim's call at Gate 3. My provisional recommendation: file. The substrate commitments are substantial enough to warrant their own ADR; future briefs benefit from citing "per ADR-024" instead of re-deriving the rationale.

---

13. Open questions for Tim — Gate 3

1. ADR-024 — file or fold into Phase 2 design doc only? My read: file. Composes with ADR-007/020/021 as a downstream-of-them substrate commitment.

2. tier_grants table name. Three candidates surfaced during drafting:

3. tier_grants (current — neutral, broadly applicable)
4. role_grants (matches ADR-020's "role" terminology for T4 admin role)
5. access_grants (matches CANONICAL access vocabulary)

6. Default: tier_grants for consistency with ADR-020's tier terminology. Tim's call if a different name reads better.

7. magic_link_allowlist retention vs subsume-into-tier_grants. Currently designed as a separate issuance-gate table. Alternative: fold the gate function into tier_grants (issuance allowed iff a non-revoked grant exists). Pro of separate table: pre-tier-grant onboarding (allowlist someone before granting them a tier). Pro of subsume: one fewer table. Default: separate table — the two concerns are different stages of the auth flow (issue vs verify).

8. metadata TEXT (JSON) on credentials. Default is opaque JSON. Alternative: explicit columns for known fields (aaguid, expiry, last_rotated). Pro of opaque: provider-agnostic; future providers add fields without migration. Pro of explicit: queryable, indexable. Default: opaque, since the metadata is typically read in provider-specific code paths that already know what to look for.

9. Zero-UUID admin disposition. §10 recommends "preserve the row, retire the special-casing." Alternative: migrate the zero-UUID admin row to a fresh crypto.randomUUID() value, treating the historical zero-UUID as a fully-deprecated value. Pro of preserve: stable substrate reference; matches "rediscovery beats archaeology" feedback. Pro of fresh: clean migration; no historical privilege. Default: preserve, per audit context.

10. portal_invites and impersonation_tokens reshape — separate brief or fold here? Currently in §5.2 + §5.3. The reshape is small (column rename + CHECK constraint). Either:

11. Fold into Phase 3 migration brief (the schema change is part of the tenants retirement)
12. File standalone tiny brief for the reshape if it earns its own commit Default: fold into Phase 3. Tim's call if the reshape wants to land sooner.

---

14. Phase 2 → Phase 3 boundary

Phase 2 design committed. Phase 3 brief (IDENTITY-MODEL-MIGRATION-01) executes:

1. Create canonical schema (3 tables + reshapes)
2. Per-row migration with id_migration_map
3. FK rewrite across consumer tables
4. Code-path update (apps/workspace/lib/tier-resolution.ts, apps/admin/app/api/admin/me/route.ts, magic-link verify route, session-shape types, all six L3-truth-source consumers)
5. Retire 8+ tables in dependency order
6. Verify with smoke tests on actual auth flows

Phase 3 is itself substantial; deserves its own gate discipline and likely multiple phases. Out of scope for IMR-01.

---

End of Phase 2 design. Gate 3 review next: Tim verifies bytes against audit + decides on §13 open questions + ADR-024 filing.