Appearance

Admin Panel MVP: Stage 0.5 spike results ​

Measured on 2026-09-28, on the local Supabase stack and with one read of Dev. These are the Stage 0.5 spikes of the Admin Panel MVP assessment (§9.3):

  • (b) the revocation primitive;
  • (c) staff principal creation;
  • (d) the JWT signing mode;
  • (e) the Users directory at 100,000 principals.

Spike (a), the ZeptoMail invite, was not executed (§6). The owner decided on these results the same day (§8.1). Programme row: QRS-1404. Every QRS-### here is a row in the tracker.

How to read the evidence. Each claim carries one of these labels, and this page never upgrades one:

LabelMeans
measureda command run on the local stack (or, where it says so, the one read of Dev), with its trimmed output
repo-readread from a repository file, with file:line. It says what the repo declares, not what a live project runs
cache-readread from the local Deno module cache, which holds the Edge Functions' pinned client (not a repo file)
expected, unverifiedan inference that nothing here checked
unknowncannot be answered by anything that was run

1 · Status and environment ​

StatusSpikes (b), (c), (d) and (e) are measured, each with a verdict below. Spike (a) was not executed: it needs a real inbox and the owner's go-ahead (§6).
What was touchedThe local Supabase stack only. It was already running and was not started, stopped or reset. One hosted call: an anonymous GET of Dev's public JWKS (§4). Nothing reached Prod (ikkwqowfnbhdasfejojg) or the backup (ygmqxyrbnemhwkiyoboc). No repository file other than this page was written.
Local stackThe supabase_*_qrsetu containers, checked with docker ps. This is not the digious-portal stack that shares the ports.
VersionsGoTrue v2.197.0 · PostgreSQL 17.6 · Supabase CLI 2.118.0 · supabase-js and auth-js 2.111.0 at the repo root. The Edge Functions pin supabase-js 2.39.7; its cached esm.sh bundle imports gotrue-js 2.62.2 (cache-read).
Migrations118 of 118 applied locally, max 20260928130000, the same as the repo (measured).
Auth configurationsupabase/config.toml:51-52: jwt_expiry = 3600, enable_signup = true (repo-read). Local GoTrue (measured):
  • JWT lifetime 3600 s;
  • mailer autoconfirm on;
  • password minimum 6, with no required character classes;
  • refresh-token rotation on, 10 s reuse interval;
  • mail goes to Mailpit, UI on port 54324.
Local signing modeES256, one EC P-256 key in the local JWKS (measured).
ArtefactsThe scripts and raw outputs are in the session scratchpad, which is not in the repo and not durable: b_revocation.mjs, b8_session_liveness.sql, c_staff.mjs, c9_insert_visibility.mjs, c10_trigger_branch.sql, d_impersonation.mjs, e_directory.sql, e_directory_b.sql and z_cleanup_proof.mjs, with their .out files.
SecretsNone on this page. A read of the local GoTrue environment printed the local CLI stack's demo signing key. It is local-only and is not reproduced.

2 · Spike (b): the revocation primitive ​

Verdict: measured. It feeds QRS-1415 (the primitive) and QRS-1413 (the manage-account fix, F2); F3 is QRS-1433.

Commands.

  • node b_revocation.mjs: one GoTrue user, spike-admin-b@example.test, signed in twice as two devices.
  • docker exec -i supabase_db_qrsetu psql -U postgres -d postgres -X < b8_session_liveness.sql: rolled back.

Setup.

  • The RPC under test is public.get_my_context(), last defined at 20260909150000_v2_context_projects_avatar.sql:45 and granted to authenticated (repo-read).
  • The access token has header {"alg":"ES256","kid":"b81269f1-…"}, exp - iat = 3600, a session_id claim and amr: password (measured).
#StepResult (measured, trimmed)
1The access token on PostgREST rpc/get_my_context200
1The access token on GoTrue GET /auth/v1/user. requireAuth makes this call (supabase/functions/_shared/auth.ts:61-66, repo-read; getUser is GET /user in the pinned gotrue-js 2.62.2, cache-read)200
2Ban: PUT /auth/v1/admin/users/{id} with {"ban_duration":"24h"}200. Sessions before and after: 2 and 2. Live refresh tokens: 2 and 2. The ban deletes nothing.
3Refresh grant while banned400 user_banned "Invalid Refresh Token: User Banned". Password sign-in: 400 user_banned "User is banned"
4The same access token on PostgREST, while banned200: it still works
4The same access token on GoTrue /user, while banned403 user_banned
5auth.admin.signOut(<user id>, "global"), the shape at manage-account/index.ts:138403 bad_jwt "invalid JWT: unable to parse or verify signature, token is malformed: token contains an invalid number of segments". Sessions unchanged.
5Raw POST /auth/v1/logout?scope=global with the user id as the bearerThe same 403 bad_jwt
5auth.admin.signOut(<the user's access token>, "local") while banned403 user_banned: refused
6SQL as postgres: delete the user's auth.refresh_tokens rows, then their auth.sessions rows2 + 2 rows deleted. Refresh: 400 refresh_token_not_found. The access token on PostgREST: 200. On GoTrue /user: 403 session_not_found "Session from session_id claim in JWT does not exist"
7Unban: {"ban_duration":"none"}200, banned_until: null. Password sign-in returns 200 and the new token works. The old refresh tokens stay refresh_token_not_found.
I1auth.admin.signOut(<device 3's token>, "global"), not banned, two deviceserror=null; sessions 2, then 0. Device 4's refresh: refresh_token_not_found. Its access token: 200 on PostgREST, 403 session_not_found on GoTrue.
I2Step 6's SQL delete on an unbanned userRefresh refresh_token_not_found, PostgREST 200, GoTrue 403 session_not_found: the same as I1
I3Ban alone, then unbanRefresh while banned: 400 user_banned. After the unban, the pre-ban refresh token works again: 200, new tokens.
I4Ban, then the SQL delete, then unbanRefresh refresh_token_not_found: no revival. The last access token still gets 200 on PostgREST, with exp - now = 3598 s.
b.8Prototype, rolled back: a definer check that auth.sessions still holds the token's session_idLive session t, deleted session f. 10,000 calls in 301.5 ms, about 30 µs each (Index Only Scan using sessions_pkey). authenticated reading auth.sessions directly gets permission denied, so the check must live in a definer.

Conclusions.

  1. auth.admin.signOut takes the user's own access token, never a user id.

    • auth-js 2.111.0 declares signOut(jwt: string, scope?) (node_modules/@supabase/auth-js/dist/module/GoTrueAdminApi.d.ts:63) and sends that string as the bearer (lib/fetch.js:91-92). The Edge Functions' gotrue-js 2.62.2 does the same: signOut(e, t="global") sends jwt: e (cache-read).
    • GoTrue also refuses it for a banned user (step 5).
    • manage-account/index.ts:138 passes user.id, so it fails on every call and only logs a warning (F2, QRS-1413).

  2. The primitive for "sign out all devices" by user id is a service-role-only definer function:

    • delete from auth.sessions where user_id = $1. The refresh tokens follow through refresh_tokens_session_id_fkey ON DELETE CASCADE, measured on the local catalogue.
    • delete from auth.refresh_tokens where user_id = $1::text, for any row without a session.

    It has the same effect as GoTrue's own global logout (I1 and I2 agree). postgres holds DELETE on both tables locally (measured); on Dev it is expected, unverified.

  3. A ban is not revocation, and an unban revives every pre-ban device (I3, F3, QRS-1433). Suspend must be a ban plus the session delete; lifting it is an unban alone (I4).

  4. The residual window after revocation.

    • Edge Functions: 0 s. requireAuth asks GoTrue, which answers user_banned or session_not_found immediately. This was measured at the GoTrue endpoint; no Edge Function was run. Moving the kit to local JWT verification would lose it (expected, unverified).
    • PostgREST: up to the access-token lifetime, 3600 s locally (jwt_expiry, and the tokens' exp - iat). Every RPC granted to authenticated sits inside that window, the writing ones included. Dev's jwt_expiry was not read: expected 3600, unverified.

  5. Closing the PostgREST window. Either add b.8's liveness check to the definer my_* helpers (about 30 µs per call, prototype only), or shorten jwt_expiry project-wide, which costs every merchant more refreshes.

  6. A correction to the assessment's B2 (§7). "Live access tokens keep working for up to 1 h" holds for PostgREST only. Edge Functions refuse at once.

3 · Spike (c): staff principal creation ​

Verdict: measured. It feeds QRS-1414 (staff provisioning); F4, F5 and F11 are QRS-1434.

Commands.

  • node c_staff.mjs.
  • node c9_insert_visibility.mjs. It uses a temporary, session-scoped test_decoding replication slot, dropped at the end.
  • docker exec -i supabase_db_qrsetu psql -U postgres -d postgres -X < c10_trigger_branch.sql: rolled back.

The trigger (repo-read, confirmed live).

  • Definition. The latest handle_new_user is 20260905233000_v2_every_account_has_an_address.sql:187-235. The only other definitions, in 20260808230000 and 20260904124512, are earlier.
  • Firing. It runs from on_auth_user_created AFTER INSERT ON auth.users (measured on the local catalogue).
  • Accepted primary_context values:
    • the key absent gives business;
    • business;
    • individual;
    • anything else raises 22023, and users_primary_context_check allows only those two values.
  • Measured with 'staff':
    • public signUp: 500 unexpected_failure "Database error saving new user";
    • admin createUser: supabase-js returned AuthRetryableFetchError, status 500, message {} (F9);
    • no row persisted either time.
Call (measured)public.users.primary_contextAddress (public.slugs)
createUser with email_confirm: true and a full_namebusiness, the display name taken from the metadataqr-11ea27e3, opaque and active
createUser with app_metadata: {principal_kind: "staff"}businessqr-c1172d5c. The metadata is stored, but the address already exists.
inviteUserByEmail with redirectTo http://localhost:3000/admin/set-passwordbusiness, created at invite time (invited_at set, no password yet)qr-5e4eecb8, also at invite time (F11)
Public POST /auth/v1/signup with data: {primary_context: "individual", principal_kind: "staff", full_name}, and app_metadata in the bodyindividualspike-signup, seeded from the name

What the AFTER INSERT trigger sees (measured by logical decoding). Each GoTrue call is one transaction:

  • createUser (xid 5583).
    1. The auth.users INSERT carries raw_app_meta_data = {"provider":"email","providers":["email"]}, with invited_at null and role empty.
    2. The trigger inserts public.users and the address qr-d1e66ac0.
    3. Only then does GoTrue run UPDATE role, UPDATE raw_app_meta_data (which adds "principal_kind":"staff"), UPDATE email_confirmed_at and UPDATE raw_user_meta_data, and commit.
  • inviteUserByEmail (xid 5585).
    1. The INSERT has invited_at null.
    2. The trigger inserts public.users and qr-f2ae00c2.
    3. Then UPDATE role, UPDATE invited_at and a one_time_tokens insert, and commit.
  • Corroboration. pg_stat_statements shows one extra UPDATE "users" … SET "raw_app_meta_data" when app_metadata is passed, and none without it.

What a normal client can set (measured).

  • signUp's data lands verbatim in raw_user_meta_data.
  • app_metadata in the signup body is ignored, and so is app_metadata in a raw /invite body.
  • After signing up, PUT /auth/v1/user with data: {principal_kind: "staff", role: "super-admin"} returns 200, and it is stored (F8).
  • PUT /auth/v1/user with app_metadata returns 403 not_admin "Updating app_metadata requires admin privileges".

The staff branch, prototyped. Everything was rolled back: the live body's md5 stayed 457ceef53a3490c60b25dc69c8259a32 and no object was left. The test rows are shaped like GoTrue's INSERT as measured above.

  • S4 · a pre-registration row keyed by the INSERT's email, which only the service role can write and which the trigger consumes.
    • The pre-registered email gets a public.users row and no address.
    • A forged user_metadata.principal_kind still gets an address (qr-4c470fa6).
    • An expired pre-registration still gets one (qr-b200dab9).
    • The row is single-use.
  • S2b · a DEFERRABLE INITIALLY DEFERRED constraint trigger that re-reads app_metadata at commit.
    • Its notice showed NEW.raw_app_meta_data as {"provider": "email", "providers": ["email"]}, and the re-read at commit as {"provider": "email", "providers": ["email"], "principal_kind": "staff"}.
    • The staff row got no address and an ordinary row got qr-f24aaea1.
    • NEW alone is not enough: the row has to be re-read.
  • Unsafe: raw_user_meta_data, which any client writes, at signup and afterwards.
  • No effect in today's AFTER INSERT trigger: raw_app_meta_data and invited_at, because both arrive by a later UPDATE.

Email uniqueness (measured).

  • createUser with an existing email: 422 email_exists "A user with this email address has already been registered".
  • The same email upper-cased: the same 422. The match is case-insensitive.
  • inviteUserByEmail to a confirmed email: the same 422.
  • A re-invite of a still-invited email succeeds and resends the mail.
  • Public signUp with an existing email: 422 user_already_exists "User already registered". That is the local stack with autoconfirm on. On Dev, with email confirmations on, the answer may differ (expected, unverified).

The invite mail (local Mailpit, measured).

  • Arrival. Both invites arrived, from admin@email.com with the subject "You've been invited". These are GoTrue's defaults, because config.toml wires none of the repo's 13 templates (F10).
  • The link is http://127.0.0.1:54321/auth/v1/verify?token=…&type=invite&redirect_to=….
  • An unlisted redirect. A redirect_to of https://admin-devv.qrsetu.com/admin/set-password, which is not on the local allow-list, still returned 200, and the mail's link carried the site URL http://localhost:3000 instead (F4).
  • First use: 303 to …/admin/set-password#access_token=…&refresh_token=…&type=invite, with amr: otp and 3600 s.
  • Second use: 303 with error=access_denied&error_code=otp_expired&error_description=Email link is invalid or has expired. A used link and an expired one give one code (F5).
  • Setting a password. PUT /auth/v1/user with a password, using the invite session, returns 200, and password sign-in then works.
  • A weak password. abc123 was accepted: the local policy is 6 characters with no classes (F7).

4 · Spike (d): the JWT signing mode ​

Verdict: measured, from one hosted read plus the local mechanics. It feeds QRS-1418 (view as user); F6 is QRS-1432.

The one hosted call, with no auth header (/auth/v1/jwks was not needed):

$ curl -sS -D d_jwks_dev.headers -o d_jwks_dev.body https://dyhjofjjuazhyqcvlrkx.supabase.co/auth/v1/.well-known/jwks.json
HTTP/1.1 200 OK
Cache-Control: public, max-age=600
key count = 1
{"kid":"47d99656-560f-49b3-99c0-db2b8befc870","kty":"EC","alg":"ES256","crv":"P-256","use":"sig","key_ops":["verify"],"has_private_d":false}

Local comparison (measured). The local stack runs the same mode: one EC P-256 ES256 key in its JWKS, and ES256 user tokens. The local anon and service keys are legacy HS256 tokens, and local GoTrue accepts HS256, RS256 and ES256.

The local mechanics (node d_impersonation.mjs, measured).

  • The magic link. auth.admin.generateLink({type: "magiclink"}) returns action_link, email_otp, hashed_token, redirect_to and verification_type, and sends no email (Mailpit's count stayed at 4).
  • Redeeming it server-side. POST /auth/v1/verify with {type: "magiclink", token_hash} returns 200 with an access token and a refresh token:
    • ES256, sub equal to the target, amr: otp, a real session_id, 3600 s, and no impersonation claim;
    • the target's last_sign_in_at went from null to stamped;
    • one session row and two GoTrue audit rows appeared.
  • It is not read-only. With that session, rpc/set_my_display_name returned 200 and the display name changed. PUT /auth/v1/user returned 200, and refresh returned 200. It is a full, writable, renewable session.
  • Self-minted tokens. A HS256 token minted with the local stack's published demo secret and no session_id got 200 from PostgREST and from GoTrue /user. With an unknown session_id it got 403 session_not_found (F6).

What this means.

  • We cannot mint a user token on Dev. Dev signs with ES256 and publishes a verify-only key, so the private key stays with Supabase (measured: the JWKS carries no private component). The only route would be importing a signing key we hold into the project (expected, unverified).
  • The legacy HS256 secret: unknown. A JWKS never lists symmetric keys, so whether Dev still trusts its old secret cannot be read from it. If it does, whoever holds that secret can mint any user's token with no session, and deleting sessions cannot revoke it (the mechanism is measured locally). QRS-1432 tracks reading this in the dashboard.
  • The only supported "session for another user" is generateLink of type magiclink plus a server-side verify, and it is a full session (measured). Read-only access, a 25-minute cap, a marker and "do not stamp last sign-in" would all be ours to build:
    1. a custom access-token hook that adds a claim (expected, unverified);
    2. write refusal in requireAuth and in every writing RPC granted to authenticated. The assessment's §7.1 lists five (repo-read); this spike measured only set_my_display_name accepting a write;
    3. the session delete from §2 for the cap, plus the PostgREST window;
    4. a way round the last_sign_in_at stamp, which corrupts get_my_context's is_returning and any lifecycle metric.
  • Verdict: a read-only "view as user" is infeasible with supported primitives alone. The owner's decision is in §8.1.

5 · Spike (e): the Users directory at 100,000 principals ​

Verdict: measured, on the local stack inside BEGIN … ROLLBACK. It feeds the directory decision (the assessment's I5, increment 2; QRS-1404).

Commands.

  • Pass 1, for A and C: docker exec -i supabase_db_qrsetu psql -U postgres -d postgres -X -v N=100000 < e_directory.sql. It ran 08:22:18 to 08:24:44 UTC and exited 0.
  • Pass 2, for B and the signup-trigger cost: … < e_directory_b.sql. It ran 08:47:27 to 08:48:58 UTC and exited 0.

Data (measured).

  • Principals. 100,000 auth.users rows, inserted as postgres through the real signup trigger, plus 200 more. 80% have an email, all have a phone and 70% have a last_sign_in_at.
  • Users. public.users holds 66,667 business and 33,333 individual principals.
  • Addresses. 110,008 rows: 66,674 opaque and 33,334 name-seeded, 2,445 of those suffixed.
  • Workspaces. 10,000, each with an owner membership and a workspace address.
  • Sizes. auth.users 58 MB, public.users 16 MB, public.slugs 18 MB.

Path used: the trigger path for every row, with one stated deviation.

  • The first 1,000 signups ran on the repo's address functions: 8.39 s.
  • The rest ran with is_slug_reserved and resolve_slug_status patched inside the transaction to compare with OPERATOR(extensions.=) (F1): 1,000 in 0.45 s, then 98,000 in 70.1 s, or 0.72 ms per signup.
  • At full scale, with the repo definitions restored, 200 signups took 37.8 s, 189 ms each.
Query, 100k principalsA · direct join, as is (pass 1)B · direct join plus indexes on our own users columns (pass 2 †)C · read model (pass 1)
Page 1, created_at desc, id desc, 25 rows20.3 ms, page-first, as a Parallel Seq Scan with a top-N sort. 48.7 ms when the enrichment runs before the LIMIT (it sorts to disk)0.73 ms0.10 ms, Index Scan
Deep keyset page (cursor at row 50,100)15.8 ms: a Seq Scan removing 50,101 rows0.25 ms0.10 ms
Faceted page: business, owns a workspace, signed in within 30 days21.5 ms12.7 ms. The sign-in facet still scans auth.users4.9 ms
Exact phone0.10 ms, on GoTrue's users_phone_keythe same index0.06 ms
Exact email, case-insensitive51.6 ms, a Seq Scan of auth.users179.6 ms †, the same Seq Scan0.05 ms
Exact email, byte-equal11.1 ms Seq Scan. It is 0.10 ms only with is_sso_user = false written out, because users_email_partial_key is a partial indexnot runnot applicable
Name prefix pri (3,304 matches)70.6 ms, Seq Scan1.7 ms0.93 ms
Rare prefix prinav (184 matches)70.4 ms2.2 ms0.86 ms, trigram
One search box over name, email and handle49.9 ms14.6 ms1.27 ms
count(*), facet on the users columns (67% of rows)14.7 ms44.2 ms †18.1 ms, a Seq Scan, which is correct at 67%
count(*), business and signed in within 30 days30.2 ms125.0 ms †2.5 ms, a bitmap on the sign-in index
count(*) of the prefix matches70.4 ms7.3 ms4.1 ms
Facet panel: context by "owns a workspace"39.4 msnot run34.0 ms, Seq Scan
Approximate totalnot applicablenot applicablereltuples = 100,200, no scan

† Pass 2 ran the same fully cached sequential scans 3 to 3.5 times slower than pass 1: the same email scan took 179.6 ms against 51.6 ms. The cause was not measured. Read column B by the rows its indexes serve.

What the read model costs (measured).

  • Build. The join-and-insert of 100,200 rows took 3.41 s, then 7 indexes at 0.08 to 0.75 s each. Size: an 18 MB heap plus 29 MB of indexes, 47 MB in all.
  • Keeping last_sign_in_at current. Trigger spike_dir_sync_signin: time=384.605 calls=2000, or 0.19 ms per sign-in.
  • Keeping it current on signup. Four alternating legs of 1,000 signups took 1.36, 1.77, 1.76 and 1.38 s (without, with, without, with): no measurable overhead at this noise level.
  • We cannot index auth.users. create index … on auth.users returns ERROR: must be owner of table users.

Method notes.

  1. Pass 1's B numbers are discarded. Its three indexes on public.users were used by no B plan. Two reproductions did not repeat that (the index was used, with indcheckxmin = f), and the cause is not established.
  2. Pass 1's signup-trigger comparison is discarded as noise: the leg without the trigger was the slower one. It was re-measured in pass 2 as A/B/A/B.
  3. Pass 2 stalled three times and was cancelled, with pg_cancel_backend on this spike's own backend; each rollback was verified.
    • WAL fell to 608 and 728 kB per 8 s, against 11 MB per 8 s once fixed.
    • The likely cause (inferred, not captured) is query plans cached on the first calls against freshly vacuumed, near-empty tables, which stayed sequential scans while the tables grew.
    • The fix was SET LOCAL enable_seqscan = off for the load only, switched back on before any measured query: 98,000 rows then loaded in 58.1 s.
    • It is an artefact of a single-transaction load. It matters to any backfill that calls these functions row by row inside one transaction.
  4. The † rows are the contended pass described under the table.

Recommendation: the read model (C).

  • Indexes on our own public.users (B) fix paging and name search.
  • But email search, the sign-in facet and any count that touches sign-in activity still scan auth.users, which we cannot index. Those took 11 to 52 ms at 100k in pass 1, and 12.7 to 180 ms in the contended pass 2. They grow linearly: about ten times at 1M by extrapolation (expected, unverified).
  • The read model serves 10 of the 12 measured shapes in 5 ms or less.
  • Its two full-population facet counts take 18 and 34 ms as sequential scans, and want approximate counts or a counts table before 1M.

6 · Spike (a): the ZeptoMail invite, not executed ​

It needs a real inbox and the owner's go-ahead. The row is QRS-285.

What the owner must provide or check on Dev (dyhjofjjuazhyqcvlrkx):

  1. Auth → SMTP settings. Custom SMTP on, host smtp.zeptomail.in, port 587 (STARTTLS) or 465, sender no-reply@qrsetu.com, a sender name, and the ZeptoMail SMTP username and password. The host, port and username conventions are expected, unverified; the record's 2026-08-01 defect was smtp.hostinger.com.
  2. Auth → Rate limits. The email-send limit and the per-user resend interval; they must cover invites, resends and admin resets. The limit is expected to be 30 per hour with custom SMTP (unverified).
  3. Auth → URL configuration. Dev's site URL, because an unlisted redirect silently falls back to it (measured locally, F4). Allow-list entries for the set-password route on each admin origin:
    • https://admin-devv.qrsetu.com/** on Dev;
    • https://admin-uatt.qrsetu.com/** on whichever project serves UAT;
    • https://admin.qrsetu.com/** on Prod, not Dev.
  4. A test inbox that shows raw headers. Accept only on ZeptoMail's Sent counter moving from 0 to 1, plus dkim=pass for qrsetu.com and a DMARC pass.
  5. Auth → Email templates. Whether the branded invite and reset-password templates are pasted into Dev; the dashboard holds the live copy.

What the repo holds (repo-read).

  • Templates. supabase/email-templates/rendered/ holds 13 templates and a manifest.json. None is wired into config.toml.
    • Six action templates: confirm-signup, sign-in-otp (the magic link), reset-password (recovery), change-email, reauthentication and invite.
    • Seven notifications.
  • Their links. invite.html and reset-password.html both use {{ .ConfirmationURL }}. The invite copy is written for merchants ("You've been invited to create a QR setu account").
  • Who uses them. A search of apps/, packages/ and supabase/functions finds no caller of resetPasswordForEmail, inviteUserByEmail or generateLink, and no type: 'recovery' or 'invite'. The 'invite' hits are the consumer invitation card kind. So no merchant or consumer flow uses the invite or recovery templates today, and staff can own both.
  • The caveats.
    • The merchant web console calls signInWithOtp({ email }), which shares the magic-link template, and signInWithPassword (apps/web/src/tiers/merchant/features/auth/AuthAccountPane.tsx:347,374).
    • manage-account has change_password (updateUser({ password }), supabase/functions/manage-account/index.ts:80).
    • Merchants can therefore hold passwords, and a merchant reset flow, if one is ever built, would share recovery.

7 · Findings ​

Every finding is measured on the local stack unless its row says otherwise.

#FindingTracker
F1A citext comparison inside a SET search_path = public function compares as text. The citext = operator lives in schema extensions, so the citext index is unusable and the comparison becomes case-sensitive. Details below.QRS-1431
F2manage-account/index.ts:138 fails on every call (403 bad_jwt), so deleting an account never revokes a session.QRS-1413 (existing)
F3An unban revives every pre-ban session (§2, I3).QRS-1433
F4An unlisted redirect_to is silently replaced by the site URL, and the invite still returns 200.QRS-1434
F5A used invite link and an expired one return the same otp_expired code, so the page cannot tell them apart.QRS-1434
F6GoTrue accepts a token with no session_id: a self-minted HS256 token passed locally, and session deletion cannot revoke it. Whether Dev still trusts its legacy HS256 secret is unknown.QRS-1432
F7The local password policy accepted abc123 through updateUser (the assessment's I1). Dev's policy was not read.QRS-1435
F8A signed-in user can write anything into their own user_metadata, including role: "super-admin", so it must never authorize anything.none assigned; it is why the §8.2 branch never reads user_metadata (QRS-1414)
F9supabase-js reports a createUser 500 as AuthRetryableFetchError with the message {}, so the reason is lost.QRS-1435
F10The repo's email templates are not wired into the local config.toml, so local tests never exercise the branded mail.QRS-1435
F11An invite creates the principal and its address at once. An abandoned invite leaves a principal behind, and deleting it leaves a released address.QRS-1434

F1 in detail (QRS-1431).

  • Measured:
    • under search_path = public, 'ABC'::extensions.citext = 'abc'::extensions.citext returns f, and the plan is Seq Scan … Filter: ((slug)::text = 'qr-abc'::text);
    • at 110,008 addresses, one lookup costs 13.9 ms, against 0.044 ms (Index Only Scan) with OPERATOR(extensions.=);
    • one repo resolve_slug_status call costs 104 ms, against 0.097 ms patched;
    • one signup costs 189 ms, against 0.72 ms patched.
  • Correctness holds today only because the inputs are lower()ed and stored slugs are lowercase.
  • The call sites, found by a heuristic match of a citext column compared with lower(btrim(p_slug)). The first two are measured, the rest are not:
    • is_slug_reserved, resolve_slug_status;
    • resolve_slug_owner_kind, get_public_setu_card, get_public_catalogue, get_consumer_vendor;
    • resolve_setu_card_payment_readiness, setu_cards_register_slug, verify_qr_lookups;
    • get_public_biodata, record_biodata_open, get_biodata_photo_keys.
  • The citext columns are public.reserved_slugs.slug, public.slugs.slug, public.setu_cards.slug, public.setu_cards.public_email and public.workspaces.contact_email.
  • The fix, measured to give an index scan: OPERATOR(extensions.=), which keeps ADR-0014's SET search_path = public.

8 · Decisions this enables ​

8.1 The owner's decisions on these results (2026-09-28) ​

  1. Holds. Suspend and Block are both a ban plus ending every session: the primitive of §2. The account holder sees one generic state, with no reason and no kind. At the GoTrue boundary this already holds: a ban answers user_banned whichever kind it is (measured, §2). The full text, with the workspace hold, is item 17 of the owner's decision list.
  2. View as user becomes a read-only support view inside the admin panel, with its own design round and ADR-0038, shipping last. A real impersonated session is not built. This is item 18 of the same list.

8.2 ADR-0035: the staff provisioning branch (QRS-1414) ​

ADR-0035 is the place for the branch:

  • Branch on a pre-registration row (S4).
    • The staff Edge Function writes it with the service role, keyed by lower(email) with an expiry, before calling GoTrue.
    • handle_new_user consumes it at INSERT and gives the principal its public.users row, which is an FK target (for example audit_log.actor_user_id), but no address.
    • It is measured to work for createUser and for inviteUserByEmail, to be single-use, and to ignore a forged user_metadata.
  • Never branch on raw_user_meta_data: any client can write it (measured, F8).
  • raw_app_meta_data and invited_at are not in the INSERT (measured). A deferred trigger that re-reads app_metadata at commit works (measured), but it leans on GoTrue v2.197's internal statement order, so it is a fallback only.
  • Keep primary_context = 'business' for staff unless a core-entity proposal widens the CHECK. 'staff' is refused today with 22023, which surfaces as a 500.
  • Staff need a work email that no account holds. Any existing email returns 422 email_exists, case-insensitively, and the flow must design that state.
  • Facts the set-password flow (G2) needs:
    • one "link invalid or expired" state, because otp_expired covers both;
    • the redirect allow-list is mandatory (F4);
    • the principal exists from the moment of the invite (F11).

8.3 ADR-0036: the revocation primitive and its residual window (QRS-1415, QRS-1413) ​

ADR-0036 is the place for the primitive:

  • The primitive is a service-role-only definer function that deletes auth.sessions (and any orphan auth.refresh_tokens) for a user id. It is equivalent to GoTrue's global logout (measured) and replaces manage-account/index.ts:138 (F2).

  • Suspend and Block are each a ban plus the primitive (owner decision 1). Lifting either is an unban only. A hold that is only a ban must never ship (F3).

  • Order and atomicity (the assessment's B6):

    1. write the intent row;
    2. ban;
    3. delete the sessions;
    4. write the completion row.

    A lift must re-drive an incomplete suspend before it unbans, or it revives sessions.

  • The residual window, to be stated in the ADR:

    • Edge Functions: 0 s, as long as the kit keeps asking GoTrue;
    • PostgREST: up to 3600 s, for reads and for the writing RPCs granted to authenticated.

    The session-liveness check (about 30 µs per call, prototyped) in the definer helpers takes the PostgREST window to 0 s. A shorter jwt_expiry also narrows it, but it is project-wide, needs a Change Record and costs merchants more refreshes. Dev's jwt_expiry still needs a read (expected 3600, unverified).

8.4 View as user (QRS-1418) ​

  • Infeasible as a platform primitive (measured, §4):
    • Dev signs with ES256, so we cannot mint user tokens;
    • the supported path is a full, writable, refreshable session that stamps last_sign_in_at and carries no marker.
  • The owner decided on the read-only support view inside admin (§8.1, decision 2), with its own design round and ADR-0038. The support view reads through operator-checked RPCs. It never creates a session for the target, so none of the four controls in §4 is needed and the target's last_sign_in_at is left alone.
  • Separately, and whatever the support view becomes, QRS-1432 reads Dev's legacy JWT secret status and revokes the secret if it is still trusted (F6).

8.5 The Users directory: the read model ​

  • Keeping it current. Triggers on public.users insert and update, and on auth.users insert and update of email, phone and last_sign_in_at (0.19 ms per sign-in, measured).
  • Indexes:
    • keyset on (created_at desc, user_id desc);
    • trigram on the lowercased name;
    • exact E.164 phone;
    • a lower(email) pattern index;
    • facet and sign-in indexes.
  • Counts. Approximate totals come from reltuples; exact facet counts take 18 to 34 ms at 100k.
  • Budget: about 47 MB per 100k principals.
  • Why not the direct join: the shapes bound to auth.users cannot be made fast, because we cannot index auth.users (measured).
  • Not measured here: the masked phone and email projections that the assessment's I4 requires. They are a design point.

9 · Cleanup proof ​

node z_cleanup_proof.mjs, pasted:

[proof] spike-admin-* principals in auth.users = 0
[proof] table counts = {"auth.users":0,"public.users":0,"auth.sessions":0,"auth.refresh_tokens":0,"auth.identities":0,
  "auth.one_time_tokens":0,"public.workspaces":0,"public.workspace_members":0,"public.slugs":9,"auth.audit_log_entries":51}
[proof] prototype objects (spike_* relations/functions/triggers, pg_trgm) = {"relations":0,"functions":0,"triggers":0,"pg_trgm_installed":0}
[proof] function bodies = repo: {"handle_new_user_md5":"457ceef53a3490c60b25dc69c8259a32","resolve_slug_status_has_patch":false,"is_slug_reserved_has_patch":false}
[proof] replication slots = [{"slot":"cainophile_u65wcmz3","temporary":true}]
[proof] open transactions from other backends = 0
[proof] planner stats (reltuples) = {"auth.users":0,"users":0,"workspaces":0,"workspace_members":0,"slugs":9}
[proof] migrations applied = 118 / max 20260928130000
[mail] Mailpit total before = 4 ; addressed only to spike-admin-* = 4
[mail] DELETE /api/v1/messages (spike ids only) -> 200
[mail] Mailpit total after = 0
  • Principals. GoTrue persisted 9 spike principals, and all 9 were deleted through DELETE /auth/v1/admin/users/{id} (each 200). Two more signups were refused by the trigger and never persisted.
  • The replication slot that remains is Realtime's own. This spike's temporary slot was dropped.
  • Transactions. Every SQL prototype and both 100k passes ran inside BEGIN … ROLLBACK, or were cancelled and rolled back by the server. The zero counts, the unchanged md5 and the zero spike_* objects above prove it.
  • The only committed SQL writes are the brief's step 6 and isolations I2 and I4: deleting spike-admin-b's own auth.sessions and auth.refresh_tokens rows, because GoTrue cannot see a rolled-back delete. Apart from those there is only non-destructive maintenance: ANALYZE, to restore the planner statistics that a rollback leaves inflated in place, and a VACUUM of this spike's dead tuples.
  • Residue left on purpose, because the brief keeps every SQL change inside a rollback:
    • 9 released addresses: qr-11ea27e3, qr-5e4eecb8, qr-b5385785, qr-c1172d5c, qr-cbeb5811, qr-d1e66ac0, qr-d63abcf4, qr-f2ae00c2, spike-signup. Keeping a released name is the registry's designed behaviour.
    • 51 rows in GoTrue's auth.audit_log_entries.
    • None of this can break npm run test:db: its three slug counts are scoped to their own fixtures (slug_registry_test.sql:198,241, biodata_projection_test.sql:78, repo-read).
    • The addresses are local-only and removable with one scoped delete … where state = 'released' and slug in (…).
  • Lock windows.
    • Pass 1 held a lock on public.users from its first index build to ROLLBACK, and on auth.users for its last seconds (the sign-in trigger prototype). GoTrue's log for 08:22 to 08:26 UTC shows only this spike's own 8 requests, each served in 47 to 236 ms, so no other sign-in waited.
    • Pass 2 kept DDL on shared tables to its final seconds: the four signup legs, 6.3 s together, and the probe index.