ADR-0035 · Operator identity, permissions and the admin data plane

Status: 🟡 Proposed · Raised: 2026-09-28, from the Admin Panel MVP plan the owner approved that day · Graduates: the draft platform operator control plane from one operator_role column to ADR-0006's shape · Amends: ADR-0006 (amendment A1, what gets built) · Depends on: ADR-0031 (placement), ADR-0032 (the phone is a credential), ADR-0034 (the origin) · Evidence:the Stage 0.5 spikes · Tracker:QRS-1404 (programme) · QRS-1406 (staff lifecycle design) · QRS-1407 (four role models) · QRS-1408 (platform-only scope) · QRS-1414 (staff principal collision) · QRS-1417 (password policy) · QRS-1432 (tokens without a session) · QRS-1434 (link states) · QRS-889 · QRS-900 · QRS-902

The decision, in one sentence

Staff authority is four tables in public that the tenant plane never touches, filled from one permission registry written as code, checked inside every admin function with the caller's own token, and audited in the same transaction as the act.

Context

There is no admin identity of any kind. No live migration creates an operator, role, permission or assignment table (all 118 files searched, zero hits), and no custom access-token hook exists. requireAdmin() reads profiles.role (supabase/functions/_shared/auth.ts:85-99), a table no live migration creates, so it admits nobody (QRS-889). Its only callers are its own tests (_shared/tests/auth.test.ts:16-18).

The draft proposal chose one column, and named the trigger that retires it. It proposed platform_operators with a single operator_role CHECK (proposal lines 72 and 237-240), and said to graduate to roles, permissions and assignments "when either trigger fires: a permission is needed that its role does not imply, or a role must be time-bounded" (lines 400-403).

The approved design fires both triggers. Assignments are permanent or bound with a start and an end, and show an expiring state (prototype/admin-panel/RBAC.dc.html:523-530); grants are module × action rather than per role.

Four role models disagree (QRS-1407), read from the design pulled 2026-09-28:

Where	Roles	Grant shape
The shell (admin-shell.js:152-164)	7	a list of desks per role
Access control (RBAC.dc.html:472-522)	10 system roles	19 modules × 9 actions, in 3 scopes (platform, org, workspace)
Users	none; permissions come from a demo toggle	hardcoded users.* (assessment, gap D3)
Leads (leads-core.js:918-930)	5	11 CRM actions, plus a record scope of own or all

RBAC's 19 modules contain no users module (RBAC.dc.html:476-496), so nothing in its matrix can grant the Users desk. Its org and workspace scopes and its External Partner role (:497, :508-522) belong to the enterprise track, not to platform staff (QRS-1408).

Decision

D1 · Four operator tables, disjoint from the tenant plane

Table (names illustrative until the migration passes check:naming)	Holds
platform_operators	the person is staff: user_id (→ users, on delete restrict), status, who created or deactivated them and when, a version
platform_roles	a role: key, name, description, whether it is a system role, status, version
platform_role_grants	what a role may do: role × module × action, with an optional record-scope qualifier
platform_role_assignments	who holds a role, and when: operator, role, starts_at, ends_at (null is permanent), granted by, revoked at and by, reason, version

• The platform_ prefix, never admin and never a bare roles: admin is already a tenant role_key (supabase/migrations/20260808210000_v2_production_hardening.sql:209-211), and the proposal's naming argument stands (lines 187-198).
• The proposal's invariants carry over unchanged. An operator holds no workspace membership (INV-1), no RLS policy references an operator (INV-3) (lines 218-230), and the two role namespaces never share a table, an enum or a resolver (lines 245-248).
• Future enterprise and organization RBAC never reuses these tables.

D2 · One permission registry, written as code in @qrsetu/domain

• Modules × actions, plus a record-scope qualifier on a grant: for example crm with own, where a Sales Executive sees only their own leads, filtered at the source, as the design models it (leads-core.js:927-930). The qualifier belongs to the grant; it is not a fourth scope axis.
• The registry is the only source. The database seeds for roles and grants are generated from it, and a gate checks that the registry, the seeds and the UI's desk filter agree. The UI never tests a role name; it asks the registry, as the design's own can() does (leads-core.js:913-926).
• This is ADR-0006's own recommendation, option B's capability map in the shared core (ADR-0006 lines 124-131 and 177-179), stored in the tables of option C, which ADR-0006 named as the sanctioned upgrade path (lines 167-168).

D3 · The seeds wait for the design round that makes one role model

No system role, grant or module is seeded until the design round that folds the four models into one is approved (QRS-1407; the assessment's gaps D3, D4 and D13). Until then the registry holds the shape, the types for modules, actions and qualifiers, and no role content. A seed written now would record one of four disagreeing models as fact.

D4 · Platform staff only

These tables express platform scope and nothing else (QRS-1408). The design's org and workspace scopes and the External Partner role go to the enterprise track, where ADR-0006's tenant roles and ADR-0023's subtree roles live.

D5 · Validity is evaluated when it is checked

An assignment is effective when starts_at <= now() < ends_at (a null ends_at never ends), it has not been revoked, and its operator is active. There is no scheduler (QRS-885: zero cron.schedule calls in the live migrations), so expiry is derived, never a job, and "expiring soon" is a query.

⚠ The design's audit feed contains a system row, "auto-expired time-bound role" (RBAC.dc.html:541). A derived expiry writes nothing at the moment it happens, so that row has no possible writer. It goes to the design-gap loop, not into a job.

D6 · Separation of duties

The database and the admin RPCs refuse each of these, every refusal with its own reason. They are the refusals the admin design round draws (design-system/admin-panel-round-1-prompt.md:172-177):

• A change to your own access. Nobody grants a role to themselves or edits their own assignment: a row constraint that the grantee differs from the grantor, and a refusal in the RPC.
• A grant of Super Admin or Platform Admin by anyone but a Super Admin.
• Leaving no active Super Admin: a write that would remove or demote the last one is refused in the database.
• A time-bound Super Admin assignment. Super Admin assignments are permanent, because a time-bound one could lapse to zero with no write for the database to refuse.
• Staff management by anyone but a Super Admin. Inviting, resetting and deactivating staff are Super Admin actions. ⚠ The design contradicts itself here: its Platform Administrator "Cannot manage other admins" (prototype/admin-panel/admin-shell.js:154), while its sign-in copy says to "Ask a Super Admin or Platform Administrator to reset it" (:844). The design round corrects the copy.
• An invite to an email outside the admitted work domains, or to one that already has an account (D12).

Two procedures sit beside the refusals:

• Break-glass is a runbook (the service role, two people, audited), kept in the dev portal and never in the handbook.
• The first operator is seeded per environment by runbook, never by a migration carrying a real email, as the proposal already required (line 94).

D7 · Every admin RPC checks the operator itself, with the caller's own JWT

• Every admin RPC is SECURITY DEFINER with SET search_path = public, and its first statement asks operator_can(auth.uid(), module, action). It is called with the caller's JWT, through a client that carries the caller's Authorization header (the userClient that _shared/auth.ts:28-33 and :51-59 already build), so auth.uid() is the operator. EXECUTE is revoked from PUBLIC and anon and granted to authenticated, and the function refuses everyone who is not an operator holding the permission.
• The service role is used for the Auth admin API and Cloudflare, and for nothing else. Never for a data read or write on an operator's behalf: a read over the service client would skip the check.
• ⚠ This departs from EDGE_FUNCTION_GUIDELINES.md §4.4 ("All DB writes use serviceClient") for operator functions, deliberately. The guidelines need the exception written in.
• The session is checked in the same call, and the staff time-box is part of that check (ADR-0034 D5).
  • operator_can first calls is_session_live(): the access token's session id (auth.jwt() ->> 'session_id') must still exist in auth.sessions. It is the same definer that ADR-0036 D3 puts first in every SQL helper to end a revoked session at once. A staff member who is signed out, deactivated or held is therefore refused by every admin RPC immediately, by one mechanism rather than two. It also refuses a token with no session_id (QRS-1432).
  • The staff time-box reads the same row: a live session older than the staff limit is refused too. Supabase's own time-box and inactivity settings are project-wide, so they would end merchants' and consumers' sessions as well; only staff pass this check, so no tenant session is touched.
  • The limit is a platform control (proposed: one working day), never a literal.
  • The refusal carries a distinct reason, so the app shows the designed "session ended" state rather than a permission denial.
  • Measured on the local stack (spike b, GoTrue v2.197.0): the access token carries session_id; authenticated cannot read auth.sessions, so the check must be a definer; the lookup is an index-only probe of about 30 µs a call (10,000 calls in 301.5 ms). Dev's GoTrue version is unverified.
• operator_can is the plan's name. The SQL function families (supabase/CLAUDE.md:42: five families only) may rename it before the migration, for example to is_operator_permitted.

D8 · requireOperator(module, action) replaces requireAdmin()

requireAdmin() is retired: it reads a table that does not exist (QRS-889). requireOperator(module, action) in _shared/auth.ts runs requireAuth, asks operator_can with the caller's JWT before any query, and throws ForbiddenError. It is the cheap early refusal. The RPC's own check (D7) is the authority.

D9 · The audit row commits with the act

• record_audit_event() is a definer function called inside each admin RPC, so an action and its audit row commit or roll back together, and never best-effort. The one writer that exists today swallows its own audit failure (supabase/migrations/20260817230500_v2_set_my_primary_context.sql:103-112); that pattern is forbidden for operator actions.
• It writes inside audit_log's existing contract (20260808200000_v2_audit_idempotency_touch.sql): append-only (:61-63, :136-137), an action of the form domain dot verb (:77), actor_kind = 'support' already permitted (:72-73), and the writer responsible for any PII in before and after (:121-125).
• An action that calls an outside system (the Auth admin API, Cloudflare) writes an intent row in the transaction that changes our own state, then makes the external call, then writes a completion row. A re-drive with the same idempotency key finds the intent without its completion and repeats the call. The external call must be safe to repeat: a ban and a cache purge are; an invite email is not, so an invite's re-drive reads GoTrue's state before sending again. A crash can then never leave a ban without its audit row, nor an audit row claiming a ban that never happened.

D10 · Personal data: masked by default, revealed on purpose, audited

• The directory and record RPCs return phone numbers and email addresses masked. A separate reveal returns the clear value and writes its audit row in the same transaction; the audit row never carries the revealed value. Users and Leads use the same mechanism.
• Opening a record writes a read-audit row, so a record read is a function that writes.
• Export is a permissioned and audited action, bounded per call because there are no background jobs (QRS-885). A CSV cell that begins with =, +, -, @, a tab or a carriage return is neutralised so that a spreadsheet does not execute it.
• Retention for leads and audit under the DPDP Act is open, and is decided before real data is imported.

D11 · The user directory is a read model

• One table, maintained by triggers on users and on auth.users insert and update (the precedent is on_auth_user_created, supabase/migrations/20260808230000_v2_auth_provisioning.sql:134-137). Keyset paging, never OFFSET; a trigram index for the name prefix; an exact E.164 match only, so a phone number can never be enumerated by prefix; approximate counts, never a full count(*).
• Measured, and the read model wins (spike e, 100,000 synthetic users on the local stack): the first page takes 0.10 ms against 20.3 ms over auth.users, and an exact email lookup 0.05 ms against 51.6 ms. We cannot index auth.users ourselves ("must be owner of table users"). The read model costs about 47 MB per 100,000 users and about 0.19 ms per sign-in to maintain.
• ⚠ The trigram index has a cost the plan did not list: pg_trgm is still not installed. The live migrations create only citext and pgcrypto (20260808090000_v2_extensions.sql:64-67), so the index needs the extension enabled by a migration and a Change Record on both projects. A btree prefix index needs neither; if it serves the name search, it replaces the trigram index.
• ⚠ The read model copies credentials. Email and phone live in auth.users, and public.users holds neither (proposal line 27). ADR-0032 makes the phone a credential, never an address or a key (D1, line 42). The copy is therefore revoked from anon and authenticated, masked on output, searched by exact match only, and every search is audited. That keeps the phone a lookup credential for staff, never an address; the owner confirms that reading of ADR-0032 when accepting this ADR.

D12 · Staff principals are provisioned through a pre-registration row (spike c, QRS-1414)

What is measured:

• handle_new_user gives every new auth user a public.users row whose primary_context defaults to 'business' (supabase/migrations/20260905233000_v2_every_account_has_an_address.sql:211-212), and a public address (:228-231, QRS-1078). A staff account created through the Auth admin API would therefore get a business principal and a slug.
• primary_context accepts only business or individual, and any other declared value stops the signup (:197-204).
• Signup is open (supabase/config.toml:52 on the local stack; disable_signup = false on Dev, measured 2026-08-26, proposal line 300), so authority is what is gated, never the door.
• The trigger sees the row before GoTrue marks it (spike c). GoTrue inserts into auth.users first and writes app_metadata and invited_at in later updates of the same transaction, so NEW inside handle_new_user cannot carry a staff flag, however the account is created.
• A signed-in client rewrites its own user_metadata: PUT /auth/v1/user with {data:{role:"super-admin"}} returns 200, while the same call for app_metadata returns 403 not_admin (spike c).
• An existing email is refused with 422 email_exists, by createUser and by an invite alike, in any letter case (spike c), so a staff member needs a separate work email (the proposal's C4, lines 556-560).
• An invite creates the auth user at invite time, before anyone accepts it, and a used link and an expired link return the same otp_expired code (QRS-1434).

Decision: a pre-registration row, consumed by the trigger.

• The service role writes a row keyed by the lower-cased email, with an expiry, before it calls GoTrue's createUser or invite. handle_new_user consumes a live row for the new user's email: it still inserts the public.users row, keeping primary_context = 'business' (the proposal's recorded cosmetic debt, lines 256-259), and assigns no address. A row is consumed once, and an expired row is ignored, evaluated when it is checked (D5's rule).
• Measured (spike c): a pre-registered principal gets a public.users row and no slug, through createUser and through an invite; an ordinary principal, a forged user_metadata flag and an expired row each still get an address.
• The pre-registration grants nothing. Authority is still the operator row, which the provisioning RPC creates for the user id GoTrue returns (D1). A stranger who signed up with that email inside the window would get a principal with no address and no authority, and the staff call would then fail with email_exists (expected, unverified).
• The same row is the pending-invite record. It carries the email, the role the invite grants, its expiry, who created it and when it was consumed. It backs the design's pending invite (sent, expires, resend, revoke: design-system/admin-panel-round-1-prompt.md:166-168) and the Overview's "invites about to expire". Revoking an invite deletes the row and the still-unconfirmed auth user. The table is in public (D14), revoked from anon and authenticated, has RLS and no policy, and is written only by the service role on the staff-provisioning path.
• The link's lifetime is GoTrue's email OTP expiry: 3,600 s on the local stack, where supabase/config.toml sets none and the CLI default applies; the hosted value is unverified. Proposed: raise it to one working day through a Change Record on both projects. ⚠ The setting is project-wide, so it also lengthens the email codes and links merchants use, including the six-digit email code of the merchant web sign-in (apps/web/src/tiers/merchant/features/auth/AuthAccountPane.tsx:6-7); that one setting governs both is expected, unverified, and it is weighed before the Change Record.
• Fallback only: a deferred constraint trigger that re-reads app_metadata at commit also works on the local stack, but it depends on GoTrue's internal write order inside its transaction, which GoTrue does not promise. The earlier candidates are superseded: an app_metadata marker cannot reach the trigger (above), and releasing or refusing the address after the fact is unnecessary.

The link crosses Cloudflare Access. The invite and reset emails link with {{ .ConfirmationURL }} today (supabase/email-templates/build.mjs:64, :100), which returns the session in the URL fragment, and an Access login round trip in front of admin.qrsetu.com may drop a fragment (expected, unverified). Recommended: a link to the admin origin's /set-password that carries token_hash={{ .TokenHash }}&type=invite (or type=recovery) as query parameters, which survive redirects, redeemed in the app with verifyOtp. ⚠ The reset template is shared with merchants, whose web sign-in offers "Forgot password" (AuthAccountPane.tsx:590), so the link is built from each call's redirect target rather than hard-coding the admin origin. Spike (a)'s acceptance gains one line: a fresh browser with no Access session completes the invite.

The rule: user_metadata never grants anything. No trigger, SQL function, policy, Edge Function or screen reads authority, a role, a kind or a flag from raw_user_meta_data, because its owner rewrites it at will (above). handle_new_user already refuses an unrecognised primary_context rather than trusting it (:197-204), and ADR-0006 named the same mistake in the retired client, where a self-written user_metadata.role would have rendered the admin tier (ADR-0006 lines 88-94).

One state for a dead link. The set-password page cannot tell a used link from an expired one (QRS-1434), so the design draws a single "this link no longer works" state (admin-panel-round-1-prompt.md:160-162).

The C1 guard inside provision_merchant_workspace() still ships (owner decision D1, proposal lines 407-440, QRS-900). ops and operator are still not reserved slugs (QRS-902); admin is (20260808110000_v2_reserved_slugs.sql:263).

D13 · The password policy is project-wide (QRS-1417)

• It is set in Supabase Auth, for the whole project. The reason: a signed-in client can call updateUser({ password }) directly against GoTrue (UserAttributes.password in the installed auth-js, node_modules/@supabase/auth-js/dist/module/lib/types.d.ts:415-437), and no Edge Function sees that call. A rule enforced only in our own set-password function would be decorative.
• It binds everyone who uses a password, not only staff. The merchant web console already signs in with email and password (apps/web/src/tiers/merchant/features/auth/AuthAccountPane.tsx:6, :374). On Dev, 0 of 7 accounts held a password on 2026-08-26 (proposal line 40); that count is re-measured on both projects before the Change Record.
• The rule's content comes from the design (at least ten characters, a number, mixed case and a symbol, admin-shell.js:907-908 onward). It is expected to map onto Supabase's minimum-length and character-class settings; that mapping, and leaked-password protection on the plan tier, are unverified. Leaked-password protection was off on Dev (proposal lines 458-460).
• supabase/config.toml sets no password policy (:47-52), so the local stack will not reproduce the hosted rule until the policy is declared there as well.

D14 · Every object stays in public

ADR-0031's context vocabulary is closed, and its only open context is biodata (ADR-0031 lines 149-151); the proposal chose public as well. Every operator table is revoked from anon and authenticated, has RLS enabled with no policy (INV-3), and is reached only through the definer RPCs of D7.

Alternatives rejected

Option	Why not
Keep the proposal's single operator_role column	Its own graduation trigger has fired (lines 400-403): the design has time-bound assignments and module-level grants
The operator's role as a JWT claim or an access-token hook	Revocation lags by the token's lifetime on the one plane that reads every tenant; the proposal rejected it for this reason (line 67)
Admin RPCs over the service client, with the actor passed as an argument	The check would live in each Edge Function's discipline, and any read that skipped it would be open (D7)
Staff as members of a QR setu workspace, or one roles table for both planes	Privilege escalation by data entry: membership is the persona discriminator and feeds my_workspace_ids() (proposal lines 63 and 128-135)
A staff marker in user_metadata, or in app_metadata read by the trigger	The first is written by its own owner (D12, measured); the second arrives after the trigger has run (D12, measured)
A real impersonated session for "view as user"	No user token can be minted: Dev publishes one ES256 verify key and nothing we can sign with, and a magic link redeemed on a user's behalf is a full writable session with no marker that stamps their last_sign_in_at (spike d). The owner chose a read-only support view instead (Consequences)
An editable permission matrix in the database now, ADR-0006 option C on its own	Custom-role editing is reserved by the owner (Q2), and a matrix with no registry is the drift D2 removes. These tables keep option C reachable
Expiry by a scheduled job	There is no scheduler (QRS-885)
A password rule enforced only in our Edge Function	Bypassed by updateUser (D13)

Consequences

• ADR-0006 is amended (A1, Proposed), and the proposal records the 2026-09-28 decisions in its §13. The proposal's protocol block still describes the single-table v1, so its ten steps are re-run for the graduated shape before any migration (check:arch-proposal).
• Gates owed: the registry, seeds and UI agreement check (D2); an authorization matrix generated from the registry, every role × module × action both allowed and denied, at the pgTAP and Edge Function levels; the refusal of anon and of a non-operator by every admin RPC.
• EDGE_FUNCTION_GUIDELINES.md §4.4 needs the operator exception (D7) written in.
• New operator Edge Functions take a context prefix (ADR-0031 D8). Their names are fixed with the migration, pass check:naming, and each gets its config.toml entry.
• Open, not decided here: retention (D10); whether the directory needs pg_trgm (D11); the Change Record for the email link's lifetime (D12); the password rule's mapping onto the plan tier (D13).
• Viewing as a user is a read-only support view inside the admin panel (owner decision 2, 2026-09-28, after spike d, QRS-1418). It is built from operator RPCs under this ADR's check, with no new session type. It has its own design round and ADR-0038, which is reserved and not written, and it ships last. A real impersonated session is not built (Alternatives rejected). Its design round also settles what, if anything, the account holder is told (QRS-1410). Nothing in this ADR grants an operator a session as another principal.

Evidence

Measured for this ADR, 2026-09-28:

Claim	Where
No operator, role, permission or assignment table and no access-token hook	supabase/migrations/*.sql (118 files) and supabase/config.toml, searched: zero hits
requireAdmin() reads profiles; only its tests call it	supabase/functions/_shared/auth.ts:85-99; _shared/tests/auth.test.ts:16-18
The caller-scoped client already exists	supabase/functions/_shared/auth.ts:28-33, :51-59
The proposal's single column and its graduation trigger	architecture/proposals/platform-operator-control-plane.md:72, :237-240, :400-403
The tenant role_key CHECK includes admin	supabase/migrations/20260808210000_v2_production_hardening.sql:209-211
audit_log's contract; its only writer is best-effort	20260808200000_v2_audit_idempotency_touch.sql:61-77, :121-125, :136-137; 20260817230500_v2_set_my_primary_context.sql:103-112
Extensions installed	supabase/migrations/20260808090000_v2_extensions.sql:64-67
handle_new_user's default context and address	supabase/migrations/20260905233000_v2_every_account_has_an_address.sql:197-204, :211-212, :228-231
updateUser accepts a password	node_modules/@supabase/auth-js/dist/module/lib/types.d.ts:415-437 (auth-js 2.111.0)
The merchant password sign-in	apps/web/src/tiers/merchant/features/auth/AuthAccountPane.tsx:6, :374
The four role models, the missing users module, the scopes, the time-bound assignments, the expiry row	prototype/admin-panel/admin-shell.js:152-164; RBAC.dc.html:472-541; prototype/platform/leads-core.js:913-930 (Claude Design project "QR setu prototype", pulled 2026-09-28; line numbers are that copy's)
The design's own contradiction on who resets staff	prototype/admin-panel/admin-shell.js:154, :844 (the same copy)
The refusals, the pending-invite lifecycle and the single dead-link state the design round draws	documentation/portal/design-system/admin-panel-round-1-prompt.md:160-177
The invite and reset templates link with the confirmation URL	supabase/email-templates/build.mjs:64, :100
is_session_live() and the token's session_id (spike b); the trigger's view of a new row, the pre-registration, user_metadata, email_exists and otp_expired (spike c); the signing keys and the magic link (spike d); the directory at 100,000 users (spike e)	the spike results, all on the local stack except the Dev JWKS read

Expected, unverified:

• Supabase's hosted password settings can express the design's four rules on the current plan tier.
• That one email OTP setting governs both the invite link and the merchants' six-digit email code (D12).
• That an Access login round trip drops a URL fragment (D12).
• That a stranger's signup inside a pre-registration window yields only a principal with no address and no authority (D12).
• Dev's GoTrue version and token shape (D7).
• Whether a btree prefix index serves the name search without pg_trgm (D11).