IDENTITY-SURFACE-AUDIT-01¶

Brief: IDENTITY-MODEL-RATIONALISATION-01 Phase 1 deliverable. Filed: 2026-05-27 by Alex. Status: Phase 1 complete; ready for Tim review at the Phase 1 → Phase 2 boundary gate. Scope: Substrate audit of identity-bearing tables, code paths, and authentication mechanics. Read-only; no schema changes.

1. Executive summary¶

The substrate has six identity surfaces, four key conventions, four cross-DB duplicate tables (not three as memory suggested), and six parallel L3-source-of-truth mechanisms that no single canonical authority reconciles. The "two-DB" framing (ops-db + workspace-db) holds — engine-db-oc is a wrangler-config display name only; the underlying D1 is rto-workspace-db on the RTOpacks account.

Phase 2 design must answer: 1. Which DB owns canonical users — ops-db, workspace-db, or a new identity-dedicated DB? 2. How does ADR-020's T3/T4/T4A map onto the current L1/L2/L3/L4/L4A UCCA-lineage tier system? 3. Which of the six L3-truth sources becomes canonical; what happens to the others? 4. How do the four conventions consolidate into one canonical key convention? 5. How does the schema accommodate ADR-022's already-present impersonation_tokens and ADR-007's user/credential separation?

The schema-design answers belong in Phase 2; this audit grounds those decisions in substrate state.

2. D1 inventory — corrected scope¶

2.1 The engine-db-oc red herring¶

Memory entries from 11-22 days ago treated engine-db-oc as a separate identity-bearing D1 alongside ops-db. The audit confirmed this is wrong.

Empirical finding: all 7 wrangler configs that reference database_name: "engine-db-oc" use database_id: 81e2919a-6587-40a1-b749-0a65103d95f0. That UUID maps to rto-workspace-db on the RTOpacks CF account.

engine-db-oc is a wrangler-config display name only. The underlying D1 is rto-workspace-db. There is no separate D1 named engine-db-oc.

The 7 wrangler configs that carry this stale display name: - apps/workspace/wrangler.jsonc - apps/admin/wrangler.jsonc - apps/site/wrangler.jsonc - workers/session-archiver/wrangler.jsonc - workers/studio-collab-do/wrangler.jsonc - workers/internal-api/wrangler.jsonc - workers/mcp/wrangler.toml

DB-NAMING-LEGACY-oc-suffix-01 was filed as a brief envisioning a D1-side rename. The actual work is a 7-string find-and-replace in wrangler configs — no D1 migration needed. Substantial scope reduction.

2.2 RTOpacks account D1 inventory (17 total, identity-relevant)¶

D1 name	UUID	Role	Identity-bearing?
`rto-ops-db`	0692049c-1bf1-49e7-9229-3773eeba1a45	Operator-side ops + UCCA-era identity tangle	YES — 17 tables
`rto-workspace-db`	81e2919a-6587-40a1-b749-0a65103d95f0	Workspace identity + workspace state	YES — 21 tables
15 others	various	Customer-data, ingest substrates, dev	NO (verified)

rto-licensing-db was checked per AS3 — holds regulatory licensing data only, no user/identity surface. rto-intake-db holds public form submissions (contact-shaped, not user-shaped). No identity surface in any of the other 13 D1s.

2.3 Cross-account D1 question — resolved¶

The MCP credential I have lists only one account (f95d45376ebeeeaf011a4f0ec0fb7b38 = RTOpacks). Memory references UCCA (e5a983…) and UCCO (aed3398a…) as separate accounts. Given engine-db-oc is on RTOpacks (not on those other accounts as memory implied), no cross-account identity surface affects this brief. UCCA Online product runs on a separate account with its own identity; RTOpacks substrate is self-contained.

3. Identity-bearing tables inventory¶

3.1 rto-ops-db identity tables¶

Table	Key convention	Rows	Schema notes
`users`	Prefix-keyed (`usr_*`)	2	`usr_tim`, `usr_jimmy` — vestigial pre-RTOpacks seeds. Both `@ucca.online` emails.
`clients`	Randomblob hex	1	`e521d5e352e40dbda5511a4a46bec320` — UCCA Pty Ltd (rto_code 45329). The RTOpacks self-client.
`orgs`	Prefix-keyed (`org_*`)	2	`org_rtopacks_ops`, `org_ucca_45329` — two records for same RTO entity (cross-prefix duplicate).
`org_memberships`	Prefix-keyed (no DEFAULT)	1	Links user → org.
`operator_roles`	Randomblob hex	1	`69a80e54dc9e6023ff5629d9b7a1ae3d` — admin@rtopacks.com.au, is_super=1. The sole operator record.
`admin_profiles`	Randomblob hex	1	Linked to `00000000-0000-0000-0000-000000000001` (zero-UUID admin).
`admin_nav_prefs`	Email-keyed	1	Admin UI prefs keyed on email directly.
`passkey_credentials`	UUID (id)	2	Both rows for `user_id = 00000000-0000-0000-0000-000000000001`. Both `sign_count=0` (never verified).
`magic_tokens`	Custom TEXT id; email-bound	0	Empty.
`user_prefs`	(id format TBD)	2	Per-user UI prefs.
`billing_customers`	Org-keyed (org_id as PK)	1	Links org to Stripe/QB billing.
`accounts` (ADR-008 orphan)	Prefix-keyed (`acct_*`)	1	`acct_ucca` — UCCA platform-tier seed. Has data, not strictly orphan.
`account_worlds` (orphan)	Prefix-keyed (`aw_*`)	1	`aw_ucca_rtopacks` — maps UCCA platform → RTOpacks world. Has data.
`account_memberships` (orphan)	Prefix-keyed	0	Empty.
`custom_roles` (orphan)	Prefix-keyed	0	Empty.
`customers` (orphan)	Custom (`test-customer-001`)	1	Test data; email tim@ucca.online. Has data.
`access_allowlist` (NEW finding — not in memory)	Randomblob hex	6	5 active entries gate admin access; separate from operator_roles.

3.2 rto-workspace-db identity tables¶

Table	Key convention	Rows	Schema notes
`users`	MIXED UUID + prefix (`user_*`)	4	`00000000-...-0001` (Tim/admin), `89fe66e7-...` (UUID), `user_ucca_tim`, `user_ucca_john` (prefix).
`tenants`	UUID	1	`00000000-0000-0000-0000-000000000100` — slug `rtopacks`. Pre-ADR-008 platform tier; still referenced by 3 tables.
`user_tenant_roles`	UUID PK	1	`00000000-...-0001` with `role='L1'`, `tenant_id=NULL`. L1-L4 enum (UCCA lineage).
`user_groups`	composite (user_id, group_id)	0	Empty.
`groups`	UUID + team_id + org_id	0	Empty.
`teams`	UUID + org_id	0	Empty.
`admin_sessions`	Email-keyed	1	`admin@rtopacks.com.au, tier='L3'`. Session log with hardcoded L3 on insert.
`partner_accounts`	UUID, user_id FK	0	Empty.
`partner_applications`	UUID	0	Empty.
`magic_tokens`	INTEGER AUTOINCREMENT, user_id FK	0	Different schema from ops-db.magic_tokens. Empty.
`passkey_credentials`	UUID, contact_hash (not user_id)	0	Different from ops-db schema — refs contacts, not users. Empty.
`impersonation_tokens`	UUID; actor_id+target_user_id+tenant_id FKs	0	Already implements ADR-022 pattern. Never used.
`portal_invites`	UUID, tenant_id FK	0	Invitation flow; tenant-scoped. Empty.
`otp_codes`	UUID, contact_hash	0	Contact-hash convention. Empty.
`device_tokens`	UUID, contact_hash	0	Contact-hash convention. Empty.
`developer_keys`	UUID, partner_account_id FK	0	Partner API keys. Empty.
`contacts`	UUID + hash	5	Lead/visitor-style identity surface.
`contact_keys`	contact_hash PK	10	Contact-keyed.
`people`	UUID	10	RTO People register (per RTOpacks People app). Domain identity, not platform user.
`pc_persons`	UUID + org_id	2	Professional-competence module identity.
`products`	UUID	5	Cross-DB dup with ops-db.products (workspace canonical).

4. The four key conventions — empirically confirmed¶

Convention	Format	Example	Tables using it
Email-keyed	RFC 5321 email as PK	`admin@rtopacks.com.au`	`admin_nav_prefs`, `admin_sessions`, `operator_roles.email UNIQUE` (id is randomblob), `magic_tokens.email` (id is separate)
UUID-keyed	RFC 4122 (36 chars, hyphens)	`00000000-0000-0000-0000-000000000001`	workspace-db.users (partial), `passkey_credentials.id`, `tenants.id`, `impersonation_tokens.id`, most workspace-db tables
Prefix-keyed	`<prefix>_<token>` or `<prefix>-<token>`	`usr_tim`, `org_rtopacks_ops`, `acct_ucca`, `user_ucca_tim`	ops-db.users (all), ops-db.orgs (all), workspace-db.users (partial), orphan tables
Randomblob hex	`lower(hex(randomblob(16)))` — 32 chars no hyphens	`e521d5e352e40dbda5511a4a46bec320`	ops-db.clients, ops-db.operator_roles, ops-db.admin_profiles, ops-db.access_allowlist

Sub-finding — workspace-db.users mixes UUID + prefix within the same table¶

workspace-db.users has 4 rows: - 2 prefix-keyed (user_ucca_tim, user_ucca_john) — manually seeded - 2 UUID-keyed (00000000-...-0001, 89fe66e7-...) — one explicit seed (zero-UUID admin), one created via magic-link signup (crypto.randomUUID() in app/auth/verify/route.ts)

The four conventions are not separated by table — they're mixed within the same identity surface, depending on how each row was created (manual seed vs runtime signup).

Sub-finding — the "zero-UUID admin" pattern is canonical¶

00000000-0000-0000-0000-000000000001 appears as: - workspace-db.users.id (Tim Rignold, admin@rtopacks.com.au, email_verified=1, org_id=org_rtopacks_ops) - ops-db.passkey_credentials.user_id (both rows; never verified) - ops-db.admin_profiles.user_id - workspace-db.user_tenant_roles.user_id (role=L1, tenant_id=NULL)

This is the single platform-admin identity. Phase 2 design must preserve or canonically migrate this identity reference.

5. The six L3-source-of-truth surfaces (auth-flow analysis)¶

The substrate has six parallel mechanisms for declaring or detecting L3 (operator) tier. No single canonical source.

#	Source	Convention	How declared	Used by
1	CF Access JWT	DNS-gated; `CF-Access-Authenticated-User-Email` header	CF Access policies on `admin.rtopacks.com.au` / `.dev`	`apps/admin/app/api/admin/me/route.ts` line 22
2	operator_roles (ops-db)	Email + `is_super` flag	INSERT via `apps/admin/app/api/admin/users/operators/route.ts` POST	`apps/workspace/app/auth/verify/route.ts` — explicit L3 promotion
3	access_allowlist (ops-db)	Email or domain, `is_active=1`	INSERT via `apps/admin/app/api/admin/access/route.ts`	`apps/admin/app/api/admin/administrators/route.ts` GET — list view; magic-link gate (via `apps/workspace/app/api/auth/magic-link/route.ts`)
4	user_tenant_roles (workspace-db)	UUID user, role enum `L1/L2/L3/L4`	INSERT (path unverified — empty schema convention)	(declared but tier-resolution.ts does not read it)
5	admin_sessions.tier (workspace-db)	Email-keyed, `tier='L3'` hardcoded on insert	`apps/admin/app/api/admin/me/route.ts` line 47 (ON CONFLICT … VALUES … 'L3')	Read by `apps/admin/app/api/admin/sessions/route.ts` for session list
6	orgs.billing_tier='internal' (ops-db)	Org-keyed promotion	INSERT via admin UI org management	`apps/workspace/lib/tier-resolution.ts` line 67 — "internal" billing tier → L3

Sub-finding — these six mechanisms are not synchronised¶

Concrete demonstration: a hypothetical email could appear in operator_roles (active) but not in access_allowlist (allowing magic-link issuance) — or vice versa. CF Access JWT could authenticate an email that has no operator_roles row. The substrate has no constraint or reconciliation pass that ensures consistency.

In current data: - admin@rtopacks.com.au appears in: CF Access (DNS gate), operator_roles (is_super=1), access_allowlist (email type, active), admin_sessions (tier=L3). NOT in user_tenant_roles (where it would be UUID-FK'd via workspace-db.users 00000000-...-0001, which itself has role=L1 not L3). NOT explicitly in orgs.billing_tier='internal' (org_rtopacks_ops has billing_tier='essential' not 'internal'). - tim@ucca.edu.au appears in: access_allowlist (active). NOT in operator_roles. The discrepancy is operationally consequential — this email gets magic-link issuance but NOT L3 promotion at session-build time.

Five of the six sources can be empty for an actual operator without breaking that operator's access. Only CF Access JWT is load-bearing for admin surface access. The rest are decorative or partially-wired.

6. Cross-DB duplicate tables — four, not three¶

Memory had three (users, products, passkey_credentials). Audit found a fourth: magic_tokens.

Table	ops-db	workspace-db	Schema difference	Disposition (provisional)
`users`	2 rows, prefix-keyed (`usr_*`), pre-RTOpacks emails	4 rows, mixed UUID+prefix, current canonical	Different created_at (TEXT vs INTEGER), workspace adds `email_verified`, `org_id`	workspace canonical; ops vestigial → drop
`products`	1 row	5 rows (canonical)	ops has `version`+`status`; workspace simpler	workspace canonical; ops vestigial → drop
`passkey_credentials`	2 rows (canonical-by-data), `user_id` FK	0 rows, `contact_hash` instead	Different identity model entirely. ops refs users; workspace refs contacts.	TBD — depends on credential-provider decision
`magic_tokens` (NEW)	0 rows, `id TEXT, email NOT NULL`	0 rows, `id INTEGER AUTOINCREMENT, user_id FK`	Different keying (email vs user FK), different id type	Both empty; consolidate at design time; magic-link flow uses KV not D1 (per `app/auth/verify/route.ts`)

Sub-finding — magic_tokens is dead schema¶

Both copies have 0 rows. The magic-link flow in apps/workspace/app/auth/verify/route.ts uses KV-stored magic tokens (SESSION_KV with MAGIC_PREFIX), not D1. The two magic_tokens tables are dead schema. Drop candidates in Phase 2.

7. Orphan table verification (ADR-008 cleanup candidates)¶

The ADR-008 five-orphan list verified at row level:

Table	Rows	Content	Drop safety
`accounts`	1	`acct_ucca` (UCCA platform seed, type='platform')	Safe drop — pre-RTOpacks seed, no live consumer per code grep
`account_worlds`	1	`aw_ucca_rtopacks` (UCCA → rtopacks world mapping)	Safe drop — pre-RTOpacks seed
`account_memberships`	0	empty	Safe drop — no data, no live consumer
`custom_roles`	0	empty	Safe drop — no data, no live consumer
`customers`	1	`test-customer-001` (test data; email tim@ucca.online)	Safe drop — test seed

OPS-DB-IDENTITY-ORPHAN-CLEANUP-01 brief (queued per ADR-008 consequence #1) can proceed; the three tables with data hold legacy seed rows that don't need preservation. Audit confirms the brief's premise.

8. CF Access integration (brief §6 Q3 answered)¶

Q3 answer: CF Access JWT claims are NOT persisted to substrate as a fifth convention. They are used transiently per request via the CF-Access-Authenticated-User-Email header (and related headers).

Evidence: - apps/admin/app/api/admin/me/route.ts line 22: request.headers.get("CF-Access-Authenticated-User-Email") ?? "admin@rtopacks.com.au" — header is read per request, NOT looked up from substrate - No worker code writes CF-Access claims into any identity table - admin_sessions records the email after CF Access JWT auth (the session log post-dates the auth), but the claim itself isn't persisted as identity-of-record

CF Access is the DNS-gated boundary. Inside the gate, identity resolution falls back to the six L3-truth sources documented in §5. The architectural commitment in ADR-019 (Mandarin enforcement at credential-surface layer) is wired through CF Access at the DNS layer — verified.

9. ADR-020 T3/T4/T4A — composition with current substrate¶

The current substrate uses L1/L2/L3/L4/L4A (UCCA lineage) per: - user_tenant_roles.role CHECK IN ('L1','L2','L3','L4') schema - admin_sessions.tier DEFAULT 'L3' schema - apps/workspace/lib/tier-resolution.ts returns ucca_layer: 1|2|3|4|4.5 - Magic-link session builds ucca_layer field

ADR-020 introduces T3/T4/T4A as the canonical model. No current substrate uses T3/T4/T4A markers.

Proposed mapping (for Phase 2 to ratify or revise)¶

ADR-020	UCCA lineage	Current meaning	Sample data
T3 — Operator	L3	Three-person team operating substrate	admin@rtopacks.com.au (CF Access + operator_roles + admin_sessions tier=L3)
T4 — Client administrator	L4 (with admin/owner group membership in workspace-db.groups)	Client RTO admin user	(none in current data — workspace-db has 0 group rows)
T4A — Client user	L4.5 (workspace `ucca_layer: 4.5` fallback default)	Client RTO non-admin user	(none in current data)
(none)	L1	UCCA-internal super-tier	zero-UUID admin in user_tenant_roles
(none)	L2	UCCA-internal mid-tier	(no data)

Phase 2 design questions surfaced¶

L1 / L2 retirement. ADR-020 has no T0/T1/T2/T3+. The L1 record in user_tenant_roles for zero-UUID admin needs migration to T3, and L1/L2 enum values should disappear.
ucca_layer field rename. The session field ucca_layer (per lib/session-types.ts) carries the UCCA name. Should it rename to tier (carrying T3/T4/T4A values) or stay UCCA-named with a numeric translation? Phase 2 schema design decides.
billing_tier='internal' shortcut. lib/tier-resolution.ts step 2.5 promotes orgs with billing_tier='internal' to L3. This is convenient but couples billing config to access control. Phase 2 design should decide whether to keep this shortcut or move it to an explicit operator-roles-only path.

10. Audit findings summary — decisions Phase 2 must resolve¶

Major design decisions¶

DB ownership of canonical users. Three options:
A — workspace-db is canonical (current direction; tier-resolution.ts reads workspace-db.users first)
B — ops-db is canonical (matches the OPS-DB-CONTENT-AUDIT-01 audit cluster naming)
C — new identity-dedicated DB (rto-identity-db or similar)
My read for Phase 2: Option A is the current direction; Phase 2 commits to schema shape only (per Tim's Phase 2 boundary decision), defers DB placement to OPS-DB-SPLIT-SHAPE-DECISION-01.
L3-truth canonicalisation. Six current mechanisms; canonical model needs one (or two — perhaps operator_roles as the explicit grant + CF Access as the runtime gate). Phase 2 design picks.
Tier vocabulary. L1-L4 (current) vs T3/T4/T4A (canonical ADR-020). Migration path: schema rename plus enum value mapping.
Key convention canonicalisation. Four conventions in current substrate; canonical model needs one. UUID is the strongest candidate (universal, machine-generated, no semantic baggage). Prefix-keyed has Q1 readability (per ADR-018 framing) but doesn't generalise to runtime-generated rows. Phase 2 design picks.
Cross-DB duplicate disposition. users/products: drop ops-db copies. passkey_credentials: depends on credential-provider decision (cross-references ADMIN-AUTH-MODEL-RECONCILIATION-01 + CREDENTIAL-PROVIDER-DECISION-01). magic_tokens: drop both (dead schema; flow uses KV).
Tenants table retire. tenants table referenced by 3 workspace-db tables (user_tenant_roles, impersonation_tokens, portal_invites). Per ADR-008 no-platform-tier, this is the historical UCCA tenant tier. Phase 2 design: rename to clients-shaped FK or retire entirely.
access_allowlist vs operator_roles consolidation. Two parallel admin-grant mechanisms with overlapping purpose. Phase 2 should pick one or articulate the split (allowlist = gate for magic-link issuance; operator_roles = post-auth role assignment).
Cross-prefix duplicate orgs. org_rtopacks_ops vs org_ucca_45329 — same RTO, two records. Phase 2 design or downstream brief picks canonical.

Memory corrections to file (post-audit)¶

project_cross_db_duplicate_consolidation_01.md — 4 duplicates, not 3 (add magic_tokens).
project_db_naming_legacy_oc_suffix_01.md — scope reduction; wrangler-config-only fix, no D1 migration.
project_admin_auth_model_reconciliation_01.md — add finding that admin identity has 6 parallel L3-truth sources, not just the passkey vs CF Access tension.
reference_cf_account_ids.md — current memory says engine-db-oc is on a UCCA legacy account; substrate-state confirms it's on RTOpacks (just under a different display name).

11. Audit completeness¶

Brief §4 Phase 1 scope checklist¶

#	Scope item	Status
1	Every D1 with user/credential data	✓ — 2 identity-bearing DBs confirmed; 15 others verified as not identity-bearing
2	Per identity table: columns, key convention, row count, readers/writers, FKs	✓ — §3, §4, §5
3	Four conventions mapped to tables	✓ — §4
4	Code paths producing/consuming each convention	✓ — §5 (key auth flows)
5	Orphan inventory verified	✓ — §7
6	CF Access integration audited	✓ — §8
AS1	Include engine-db-oc	✓ — confirmed = rto-workspace-db (§2.1)
AS2	Include both workspace-db AND engine-db-oc if distinct	✓ — they're the same D1
AS3	licensing-db-oc identity check	✓ — no identity surface
AS4	CF Access claims persistence	✓ — §8, transient not persisted
AS5	Cross-DB duplicate sub-briefs cross-referenced	✓ — §6
Q1 (composition)	Substrate-faithful four-convention framing?	✓ — four conventions confirmed; six identity surfaces; six L3-truth sources
Q2 (composition)	Cross-DB identity surface?	✓ — both rto-ops-db and rto-workspace-db identity-bearing
Q3 (composition)	CF Access claims as fifth surface?	✓ — transient, not persisted
Audit-shallow credentials inventory	✓ — magic_tokens (KV-based runtime), passkey_credentials (D1; never verified), CF Access JWT (DNS gate), access_allowlist (issuance gate)

12. Phase 1 → Phase 2 boundary¶

Audit complete. Phase 2 (canonical model design) is unblocked.

Phase 2 design deliverable per brief §4 Phase 2 scope: - Canonical user identity model - Role-attachment model (T3/T4/T4A schema-level) - Credential reference model (per ADR-006) - Migration strategy (schema-shape only per Tim's Phase 2 boundary decision; DB placement deferred to OPS-DB-SPLIT-SHAPE-DECISION-01) - Composition notes with OPS-DB-SPLIT-SHAPE-DECISION-01, ADMIN-AUTH-MODEL-RECONCILIATION-01, CREDENTIAL-PROVIDER-DECISION-01, CROSS-DB-DUPLICATE-* sub-briefs

Pending Tim sign-off at the Phase 1 → Phase 2 boundary gate. Substrate observations above either inform Phase 2 design (most) or recommend memory updates (the four flagged in §10) or recommend remediation (none — no actively-broken substrate found).

End of audit. Phase 2 design begins on Tim's signal.