Identity & Access

Plain-language summary. This domain answers two questions: who is this player? and what are they allowed to do? Everything orbits one table, playroll_users — it is the hub of the entire database, and almost every other table points back to it. Around it sit the Steam sign-in machinery, two separate permission systems (one for app players, one for staff), the desktop-app login handshake, and an append-only record of what each player consented to.

Map

FK targets in this diagram

playroll_users is 1:1 with auth.users (same UUID). For readability the edges above are drawn to PLAYROLL_USERS, but two of them physically reference auth.users(id), not playroll_users: playroll_user_consents.user_id and contributor_status_projection.app_user_id. player_roles.user_id, by contrast, really does FK playroll_users. The distinction matters only when you write SQL joins or reason about cascade deletes.

playroll_users — the hub

One row per player, keyed by user_id (the Supabase auth UUID). At 40 columns it is the widest and most-referenced table in the schema. The columns fall into natural groups:

Group	Columns	What it's for
Identity	display_name, avatar_url, steam_id, discord_id, discord_username, steam_username, steam_avatar_url	The player's public identity across Steam and Discord.
Steam enrichment	steam_hours, steam_games_owned, steam_level, steam_profile_public, steam_account_created, steam_account_age_days, steam_vac_banned, steam_vac_bans_count, steam_game_bans_count, steam_days_since_last_ban, steam_enriched_at	Pulled from the Steam Web API to qualify the account (anti-fraud + value signals).
Network	steam_friend_count, steam_friends_public, friends_fetched_at, network_value, network_whale_count	Drives the growth / headhunter view.
Attribution	country, language, utm_source, utm_medium, utm_campaign, referrer, device_type	Where the player came from.
Referral & priority	referral_code, referred_by, referral_count, priority_score	The referral loop and onboarding prioritization.
Program	contributor_tier, credits_earned, credits_claimed	The player's current tier and reward ledger totals.

contributor_tier is a string today

Tiering currently lives as a single text column on the user (default player). The quests model proposes promoting this to a dedicated player_tier_state table with the metrics that drive it (responsiveness, validated hours, completion). Until that lands, tier is a value, not a table — see quests-data-model design notes.

A few columns are trigger-maintained — don't write them directly: steam_account_age_days is computed from steam_account_created by the playroll_users_fill_steam_age trigger, and updated_at is stamped on every update. user_id is an FK to auth.users.id (ON DELETE CASCADE); steam_id and discord_id are UNIQUE.

The canonical read projection is the playroll_users_view view, which joins playroll_users with auth.users. It is a slim 6-column slice — user_id, steam_id, display_name (aliased as steam_username), avatar_url, email, discord_username — ordered by created_at. Read it when you need the auth email alongside the profile (note: created_at is used for ordering but is not a projected column).

Steam sign-in

Three small tables back the Steam OpenID login flow run by the steam-login edge function:

• steam_id_allowlist — pre-authorized Steam IDs. The login fn always creates the auth.users + playroll_users row first (via the on_auth_user_created trigger), then the allowlist decides whether to issue a token. Two important nuances: (1) the gate is fail-open while the allowlist is empty — everyone gets through until the first row is inserted; (2) once it is non-empty, an absent steam_id keeps its captured playroll_users row but gets no session. Carries a role (FK to player_role_catalog, default pioneer) and an enabled flag.
• auth_user_steam_lookup — a steam_id → auth user UUID index, UNIQUE on user_id, maintained by the auth_user_steam_lookup_sync_trg trigger on auth.users. It exists to replace an auth.admin.listUsers({perPage:1000}).find() scan in the edge function (M6) — a pure performance index.
• steam_openid_nonces — single-use nonces (nonce, seen_at) that make the OpenID callback replay-proof.

When a new auth user is created, the on_auth_user_created trigger (SECURITY DEFINER) does two things: it inserts the playroll_users row, and if the steam_id is allowlisted it also seeds player_roles from the allowlist role (granted_by = 'on_auth_user_created:allowlist', ON CONFLICT DO NOTHING). The role catalog ranks tester (50) < pioneer (100) < staff (200).

Two RBAC systems (don't mix them up)

There are two independent permission systems in this schema. They look similar but serve different populations:

	Player RBAC	Staff RBAC
Tables	player_roles + player_role_catalog	crm_user_roles + role_permissions + permissions
Subject	App players (user_id UUID)	EC staff (email)
Used by	The Playroll app / API	The network-CRM (control.extrinsiccognition.com)
Verified by	Supabase auth (JWT)	Cloudflare Access, then Express middleware
Shape	A player may hold many roles; role is an FK into the catalog, which ranks the hierarchy. New roles = a catalog INSERT, no migration.	crm_user_roles maps an email to one of admin / cm / vc / sales (vc is defined but not yet seeded); role_permissions expands that role into a bundle of (resource, action) permissions where action ∈ (read, write). The ACCESS & ROLES admin UI edits role_permissions; the authorize() middleware reads it.

The link from crm_user_roles.role to role_permissions.role is by value (a shared role string), not a database foreign key — which is why the map draws it as a soft relationship.

The staff authorize() path resolves through two 60-second caches, not one: ROLE_CACHE_TTL_MS (email → role) and PERMISSION_CACHE_TTL_MS (role → permissions), chained by getUserPermissions. There is also a wiki:write-scoped X-Control-Review-Token service bypass for automated review.

Staff RBAC resources → CRM pages

A resource is a logical surface of the CRM, not a URL. It's the unit of a grant: each resource has a read and a write action, and a role is a bundle of (resource, action) rows. The canonical inventory lives in the network-crm repo at docs/rbac/RESOURCES.md; the table below maps each resource to the page(s) it actually gates so the ACCESS & ROLES matrix is unambiguous.

One resource can gate several differently-named pages (aliasing)

A matrix row does not map 1:1 to a nav label. Editing one row can change access to more than the page that shares its name — see the "also gates" column.

Resource	Primary page	Also gates (aliasing)
testers	Testers roster (/p2e/testers)	—
recordings	Recordings → Lobbies (/p2e/recordings)	Hardware, Games Supply
players	Players (/p2e/people/players)	Supply (V3), Referrals
contributors	Contributors (/p2e/contributors)	Pioneer Verification
awareness	P2E Funnel (/p2e/funnel)	all funnel stages
notifications	Notifications	—
products.releases	Playroll Releases	—
payments	Payment Review	—
people.access_and_roles	Role Matrix	Operators, Admins
b2b / d2c / wiki	the B2B / D2C / Wiki modes	—
agents.fleet	Agents (Claw Fleet)	—
lineage	Substrate	Substrate on B2B & D2C
dashboard	Situation Room (/p2e)	default for unmapped P2E paths

Backend-only resources gate an endpoint but have no nav entry — they're reached via inline API calls, the command palette, or admin chrome, and are hidden behind the matrix's "show advanced" toggle:

Resource	What it protects
agents.triggers	Run buttons inside Agents (synthesis, belief factory, causal-wraps) — write-only
payments.draft	CM sandbox drafts inside Payment Review — write-only
swarms	The /api/v1/agents catalog (distinct from agents.fleet)
terminal	The Cmd+J SQL terminal — admin, write-only
tags	Tagging, used inline (mainly B2B)
search	Omnisearch (Cmd+K)
patterns	Buyer-patterns aggregate

Whether to give the aliased pages their own resources (e.g. split pioneers from contributors, or supply/referrals from players) is tracked as a cleanup decision in network-crm docs/rbac/MATRIX-CLEANUP-PROPOSAL.md — deferred until a role genuinely needs the split audiences granted independently.

Device login & upload credentials (both legacy)

These two tables are superseded — 0 rows, deny-all RLS

Neither is on the current path. They survive in the schema but nothing writes them today.

device_auth_requests was a device-code login handshake (pending → completed, carrying tokens + a user_profile snapshot). The live Steam flow no longer uses it — steam-login states "no polling, no device_code, no DB for auth handshake"; it has 0 rows and only a deny-all RLS policy, replaced by the redirect flow (playroll://auth/callback).

upload_credentials was a per-machine credential keying a hashed MAC (mac_hash, the PK) to a user_id. It has 0 rows and no writer anywhere in the core; RLS is two deny-all policies (service-key only). Note user_id here is text with a UNIQUE constraint — not a uuid FK to playroll_users.

Consent

playroll_user_consents is an append-only log — one row per acceptance, never updated or deleted (there is no revoked column). consent_type is a closed five-value enum:

consent_type	What the user accepted
age_confirmation	Confirmation of minimum age.
tos	Terms of Service.
privacy	Privacy policy.
eula	End-user licence agreement.
edc_gameplay	Consent to gameplay data collection (the one users can later withdraw).

Each row also records the version accepted, accepted_at, and the country, ip_address, user_agent at the time, plus a metadata JSON blob.

Two service-role edge functions write here:

• consents-accept appends an acceptance.
• consents-revoke appends a withdrawal — a new row with metadata.action = 'withdrawal' (pilot: only edc_gameplay is withdrawable).

Because it's append-only, effective state is "latest accept vs latest withdrawal" per (user, consent_type). Only the privacy document is shared with the contributor-portal consents table; the consents-status edge function aggregates both logs across the two databases for that one type. See Consent Versioning for the full lifecycle.

The contributor-portal bridge

contributor_status_projection is the only table that crosses into the contributor portal's world — but note the direction carefully. The portal owns the source table (contributor_app_links); this projection is written app-side by the redeem-activation-code edge function (service role), which upserts it with link_status hard-coded to 'verified' (the column's only allowed value). The Playroll app reads its own row.

It is deliberately minimal — app_user_id (uuid, PK, FK → auth.users.id ON DELETE CASCADE), contributor_id (uuid), source_link_id (uuid), steam_id, linked_at, and sync timestamps, all NOT NULL except steam_id — and must never contain legal-document evidence, IBAN data, or any portal PII. The full identity-link design lives in the contributor-portal's Identity Link page.

Access model — RLS is on everywhere

Row-level security is enabled on all 13 tables in this domain, so the map above shows logical relationships, not what a browser client can read. The posture splits three ways:

• Own-row readable by the user: playroll_users (own row R/W), playroll_user_consents (own SELECT), contributor_status_projection (own SELECT). Plus the public-ish catalogs player_role_catalog, steam_openid_nonces, auth_user_steam_lookup.
• Service-role / deny-all (no client access — reached only via edge functions or the service key): crm_user_roles, device_auth_requests, player_roles, steam_id_allowlist, permissions, role_permissions, upload_credentials.

So when the map implies "the app reads this", assume it goes through an edge function unless the table is in the own-row list above.

---

See also: full column reference for these tables.