Admin Portal — backend readiness assessment (measured against Dev)

⚠ SUPERSEDED IN PART — the Admin Panel MVP has its own assessment (2026-09-28)

The Admin Panel MVP assessment is the current plan for staff sign-in, Access control, Users, Leads & CRM and the Overview. This page stays the record of what Dev held on 2026-08-26.

Every number, table, column, policy, grant, function and Edge Function on this page was read from the live qr-setu-dev project (dyhjofjjuazhyqcvlrkx) on 2026-08-26, not from a migration file and not from any other document. Where a claim could not be measured it is marked ❓ and says so. Where this page contradicts an older portal page or CLAUDE.md, the measurement wins and the drift is listed in §11.

Method: list_projects · list_migrations · list_edge_functions · get_advisors · plus direct catalogue reads (pg_class, pg_policy, pg_constraint, pg_proc, pg_index, pg_indexes, information_schema.role_table_grants, auth.*, storage.*) and pg_get_functiondef for live function bodies. Repo-side counts came from npm run check:claims (green) and a comm diff of migration versions.

The one-line finding

The tenant backend is real and well built. The ADMIN backend does not exist. There is no platform-admin identity, no admin role, no admin RPC, no admin Edge Function, not one RLS policy that mentions an administrator, and audit_log — the table every admin action is supposed to write to — has zero rows and no admin writer: its one writer is set_my_primary_context, a principal changing their own context. The single admin primitive that does exist, requireAdmin() in the shared Edge Function kit, reads public.profiles.role; public.profiles was dropped by the ADR-0020 baseline, so that function cannot succeed for anybody and its only test passes without ever reaching the query.

✅ INDEPENDENTLY RE-VERIFIED 2026-08-26 (second pass, by a DIFFERENT access route)

Re-measured against live qr-setu-dev over a second, independent route — direct psql through the ap-south-1 pooler (aws-1-ap-south-1.pooler.supabase.com, user postgres.dyhjofjjuazhyqcvlrkx, session forced default_transaction_read_only = on) rather than the MCP tooling the first pass used. Eleven of the fourteen headline numbers reproduced exactly, including the two that carry the most weight:

• 🧮 anon = 0 rows and authenticated = 0 rows in information_schema.role_table_grants. The RPC-and-Edge-Function-only client surface is real, not aspirational.
• 🧮 to_regclass('public.profiles') IS NULL. requireAdmin() genuinely cannot admit anybody.

Also reproduced exactly: 55 tables · 55 RLS-enabled · 0 forced · 29 with zero policies · 45 policies · 34 non-internal triggers · 0 views · 0 realtime publication tables · 69/69 migrations set-equal in both directions · 1 public bucket / 0 objects · pg_cron absent · 7 Google-only auth users · 0 users with a password · 0 banned · auth.mfa_factors 0 · auth.audit_log_entries 0 · auth.sessions 19 · raw_app_meta_data keys provider, providers only · the three legacy tombstones.

Four function counts are corrected in place in §1.2, and one of the corrections changes a design input:

This page said	Measured 2026-08-26 (2nd pass)	Reading
public functions 69	68	Same day, and no migration ran between the two passes (69/69 set-equal at both), so this is a counting difference, not drift
"34 of them trigger functions"	17 return trigger; 16 are actually attached	⚠ 34 is the TRIGGER count, not the trigger-FUNCTION count. They differ because trigger functions are reused across tables
SECURITY DEFINER 62 ("all but 7")	61 of 68
search_path pinned 67 of 69	66 of 68	Still exactly 2 mutable — QRS-895 stands unchanged

🔎 The number that matters most for the admin build is none of those: it is 51. Subtracting the 17 trigger functions leaves 51 callable functions as the entire API surface an admin plane could reach today. That is the real denominator for §10's A4 "one narrow admin RPC per screen" pattern, and it was not stated anywhere.

⚠ One live figure moved, and the movement is expected rather than a defect: payment_events read 40 in the first pass and 44 now. Test payments are still arriving. Read every row count on this page as a timestamped sample, not a constant.

✅ And the proposal's four unverified Auth prerequisites are now MEASURED — see platform-operator-control-plane §8. The short version: email+password IS enabled, so operator sign-in works today; signup is open, so it cannot be used to protect the admin plane; MFA and passkeys are off.

Status vocabulary used throughout

Mark	Means
🟢 deployed	Measured present and exercised on Dev
🟡 partial	Structure exists; a required column, writer, reader or grant is missing
📘 documented	Decided in an ADR or spec; nothing in the database
🔴 missing	No table, column, function or policy anywhere
❓ unknown	Could not be measured from Dev or from the repo

---

1. Actual Supabase architecture (measured)

1.1 Projects reachable from this session

ref	name	role
dyhjofjjuazhyqcvlrkx	qr-setu-dev	ap-south-1, Postgres 17.6. The subject of this assessment.
ygmqxyrbnemhwkiyoboc	qr-setu-legacy-bkp	ap-southeast-2. Pre-ADR-0020 backup. Not an environment.

Production (ikkwqowfnbhdasfejojg) is on a separate Supabase account and was not visible to this session's token, so nothing on this page describes Production. That is the intended posture.

1.2 Shape, in numbers

Thing	Measured	Note
public tables	55	all relkind = 'r', no partitioned tables
Tables with RLS enabled	55 of 55	none forced (relforcerowsecurity false everywhere)
Tables with zero policies	29	deny-all to anon/authenticated; reachable only by service_role
RLS policies	45	across 26 tables
public functions	68	17 return trigger (16 attached), leaving 51 callable — corrected 2nd pass
SECURITY DEFINER functions	61 of 68	corrected 2nd pass
Functions with search_path pinned	66 of 68	still exactly 2 mutable (catalog_item_orderable, consumer_attributes_match)
Non-internal triggers	34
Views	0	no read models, no admin views
Migrations applied	69
Edge Functions deployed	10, all ACTIVE
Extensions	citext, pgcrypto, uuid-ossp, pg_stat_statements, supabase_vault, plpgsql	pg_cron and pg_net are NOT installed
Storage buckets	1 — profile-pictures, public = true	0 objects
Storage policies	4
Realtime publication tables	0	chat realtime is broadcast-based, not postgres_changes
Auth users	7, all confirmed	provider: google only
Non-standard schemas	auth, storage, realtime, vault, graphql, extensions, supabase_migrations, legacy

legacy schema holds three renamed tombstones — profile_items_dummy_pre_launch_20260804, setu_pages_dead_builder_20260804, templates_rejected_engine_20260804. Deliberate, named, dated. Not a leak.

1.3 Repo ↔ Dev convergence — both directions, measured

This is the check nothing in CI performs (the QRS-696 gap), so it was done by hand.

• Migrations: 69 in supabase/migrations ↔ 69 applied on Dev. Set-equal. comm -23 and comm -13 against the applied version list both return empty — no unapplied migration, no orphan version. The QRS-693 defect class is currently absent.
• Edge Functions: the deployed slug set equals the repo's live folder set exactly — manage-account, manage-item, manage-media, manage-order, manage-reminder, manage-setu-card, place-public-order, provision-workspace, razorpay-webhook, reconcile-payments. No extra function is deployed, so QRS-694 (four archived pre-v2 functions still ACTIVE, two on the money path) is closed in substance — manage-profile, manage-settings and create-payment-link are gone. ⚠ The tracker still carries that row as OPEN.
• config.toml declares all 10, and manage-reminder is now deployed verify_jwt = true, which QRS-643 recorded as false. The gate itself is still broken; the symptom is fixed.
• ⚠ Three deployed bundles are older than their repo source: manage-item, manage-media and manage-setu-card were last deployed 2026-08-22 and last committed 2026-08-25. Same-day deploy-then-commit ordering accounts for the others; these three are a real 3-day lag (QRS-894).

1.4 The grant posture — and why it governs every admin decision

information_schema.role_table_grants, schema = public
  service_role  → SELECT/INSERT/UPDATE/DELETE/TRUNCATE/REFERENCES/TRIGGER on all 55 tables
  authenticated → (no rows)
  anon          → (no rows)

anon and authenticated hold ZERO table privileges. ADR-0014's least-privilege intent is not aspirational here, it is realised: the client surface is RPC-and-Edge-Function-only by grant, and the 45 RLS policies are genuine defence-in-depth sitting behind a door that is already shut.

The consequence for the Admin Portal is total and non-negotiable:

An admin client holding a publishable key can read nothing. Every admin read must be either a SECURITY DEFINER RPC with an explicit GRANT EXECUTE, or an Edge Function using service_role. There is no third option, and "just add an admin RLS policy" does not work — a policy cannot grant a privilege the role does not have.

1.5 Security advisors (79 findings, all explicable)

Level	Finding	Count	Reading
WARN	authenticated_security_definer_function_executable	32	By design — this is the architecture
WARN	anon_security_definer_function_executable	15	By design — public card + reference data
INFO	rls_enabled_no_policy	29	Fail-closed by design
WARN	function_search_path_mutable	2	Real, small: catalog_item_orderable, consumer_attributes_match (QRS-895)
WARN	auth_leaked_password_protection	1	Auth setting, off. Moot while Google is the only provider

No "table exposed without RLS" finding exists. That is the important negative.

---

2. User architecture (measured)

2.1 What a user is

public.users is 9 columns, and the list is short enough to be worth reading in full:

id uuid PK  →  auth.users(id) ON DELETE CASCADE
display_name text NULL
avatar_media_id uuid NULL → media(id)
locale text NOT NULL default 'en'
timezone text NOT NULL default 'Asia/Kolkata'
primary_context text NOT NULL default 'business'  CHECK IN ('business','individual')
status text NOT NULL default 'active'             CHECK IN ('active','suspended','deleted')
created_at / updated_at timestamptz NOT NULL

Four facts follow, and each one costs the Admin Portal something:

1. There is no email and no phone on public.users. Contact identity lives only in auth.users, which is service_role-only. So "search a user by email" is an Edge Function operation, never an RPC over public.
2. No user is reachable by phone at all. auth.users.phone is null for all 7 users, because the only identity provider in use is google. Phone data exists only as business contact (workspaces.contact_phone) and buyer contact (orders.buyer_phone).
3. users.status has three values. The in-progress Users desk models six (active, pending, suspended, blocked, deactivated, erased). pending, blocked and deactivated are unrepresentable — the CLAUDE.md fourth-rule defect class (a designed enum narrowed in the contract, where no amount of screen work can produce the missing state).
4. primary_context is the persona axis, and it is persisted server-side. set_my_primary_context(text) exists and get_my_context() projects it (measured from pg_get_functiondef). CLAUDE.md's claim that account type is "device-local only" and "not projected" is stale.

2.2 Provisioning

handle_new_user() (SECURITY DEFINER, on auth.users) inserts one public.users row per principal, taking display_name from Google's full_name/name and primary_context from raw_user_meta_data when it is business or individual, else 'business'. ON CONFLICT (id) DO NOTHING.

Measured: 0 auth users lack a public.users row. The one-row-per-principal invariant holds.

⚠ The default is 'business'. A consumer who signs up through a card is provisioned as a business principal unless the client passes metadata. Combined with zero consumers existing today, the consumer population is not merely empty — the default writes the wrong value.

2.3 Personas, as they actually exist

Persona	How it is represented	Live count
Business owner (solo)	users.primary_context='business' + exactly 1 workspace_members row, role_key='owner'	5
Individual / consumer	primary_context='individual' or 0 memberships	0 by context; 2 by membership
Enterprise employee	1 membership on an org_owned workspace	0
Platform admin / staff	🔴 nothing — no table, no column, no claim	0
Unconverted lead	🔴 nothing — no leads table	0

All 7 users are active / business. Two users have zero workspace memberships, which is structurally the consumer shape, but their primary_context still reads business.

---

3. Workspace architecture (measured)

public.workspaces — 35 columns. The load-bearing ones:

Column	Constraint	Live values
organization_id	→ organizations(id) ON DELETE RESTRICT, nullable	all NULL
parent_id	self-FK, RESTRICT; CHECK (parent_id <> id); CHECK (parent_id IS NULL OR organization_id IS NOT NULL)	all NULL
path uuid[] + depth int	CHECK (depth BETWEEN 1 AND 6), maintained by workspaces_maintain_path() trigger	all depth 1
kind	CHECK IN ('solo','organization')	solo × 5
ownership_model	CHECK IN ('member_owned','org_owned')	member_owned × 5
status	CHECK IN ('draft','active','suspended','closed'), default 'draft'	active × 5
suspended_at / suspension_reason / suspended_by	CHECK (status <> 'suspended' OR (suspended_at IS NOT NULL AND suspension_reason IS NOT NULL))	all NULL
archetype_key / industry_key	FK to business_archetypes / industries, RESTRICT	set
state_key / city_key	FK to states / cities, RESTRICT	the marketplace discoverability switch
gstin	format CHECK, NOT VALID

The enterprise architecture is schema-complete and entirely unexercised. The tree, the materialised path, the depth cap, the sharing flags on organizations, workspace_groups, oversight — all present, all with zero rows. It is 📘 documented + structurally built, never run. Nothing has proved the tree works.

3.1 Suspension is modelled properly, and this matters for Admin

workspaces and organizations both carry suspended_at + suspension_reason, both enforce "suspension is explained" with a CHECK, and workspaces additionally carries suspended_by → users(id). setu_cards.status accepts 'suspended'.

🟡 But three gaps make it unusable as-is: setu_cards has no suspended_at/reason/by (so a suspended card has no provenance), nothing links a workspace suspension to unpublishing its card, and there is no RPC or Edge Function that performs a suspension — only a raw service_role UPDATE. Tracked as QRS-892.

---

4. User ↔ workspace relationships (the answers, measured)

workspace_members is 7 columns, PRIMARY KEY (workspace_id, user_id):

workspace_id uuid → workspaces(id) ON DELETE CASCADE
user_id      uuid → users(id)      ON DELETE CASCADE
role_key     text NOT NULL default 'owner'
             CHECK IN ('owner','admin','manager','member','viewer')
status       text NOT NULL default 'active'
             CHECK IN ('invited','active','suspended','removed')
invited_by   uuid → users(id) ON DELETE NO ACTION
joined_at, created_at timestamptz

Question	Measured answer
What is a user?	A login principal. public.users 1:1 with auth.users. Owns nothing business-side by itself.
What is a workspace?	The business tenant. Every business-scoped table hangs off workspace_id.
Can one user belong to many workspaces?	Yes — the composite PK permits it and get_my_context() returns an array. Live: every user has 0 or 1.
Ownership model	Two independent axes. workspaces.ownership_model = who owns the tenant; workspace_members.role_key='owner' = who holds the owner seat.
How are members represented?	One workspace_members row per (workspace, user). One role per membership — no multi-role.
How are roles represented?	A 5-value text CHECK constraint. There is no roles table and no permissions table.
How are permissions enforced?	Three layers: (a) zero table grants to anon/authenticated; (b) RLS via my_workspace_ids() / my_oversight_workspace_ids() / my_shared_org_ids() / my_conversation_ids(); (c) the Edge Function as primary write-enforcement. ⚠ role_key is read by NO policy and by exactly ONE function: set_workspace_location refuses unless the caller's active membership holds owner or admin (20260822140000_v2_set_workspace_location.sql:76-83). get_my_context only projects it; nothing else enforces it.
Merchant ↔ workspace	The merchant is the workspace. No separate business entity.
Consumers	Same users table, distinguished by primary_context='individual' and/or zero memberships, plus anonymous buyers via orders.buyer_user_id NULL + buyer_name/buyer_phone/buyer_email.
Suspend / deactivate / remove	users.status, workspaces.status, workspace_members.status all support it. No cascade, no RPC, no audit row, no session revocation. Setting users.status='suspended' today stops nothing — no policy reads it and nothing enforces it (two functions read it: get_my_context projects it, and soft_delete_account checks it for idempotency, 20260904150210_v2_soft_delete_cascade.sql:70).

4.1 The relationship map that Admin has to navigate

4.2 Three hard constraints the Admin designs must be built around

1. One Setu Card per workspace. setu_cards_workspace_id_key is a plain UNIQUE on workspace_id. A second card requires dropping that index — a schema decision, not a feature. Any admin screen showing a "cards" count above 1 is showing something the database forbids.
2. One subscription row per workspace, current state only. workspace_subscriptions PK is (workspace_id) and there is no id. There is no subscription history and nowhere to put one. Plan changes overwrite. The Subscriptions desk's "History — who, when, at which scope, what it overrode" has no storage (QRS-893).
3. "Area" is city granularity. get_consumer_item_feed's area_key is setu_cards.city_key (measured in 20260818120000). cities is 178 city rows. The neighbourhood-level areas the designs use (Tulshibaug, Aundh, Baner…) do not exist as data (QRS-898).

---

5. Roles & permissions

5.1 What exists

Artifact	Status
Tenant role on a membership (role_key, 5 values)	🟡 stored, enforced by one function (set_workspace_location)
roles table	🔴 missing
permissions / permission-catalogue table	🔴 missing
Role assignment, time-bounded	🔴 missing
Platform-admin role, in any form	🔴 missing
is_admin() or equivalent	🔴 no such function among the 69
Admin claim in auth.users.raw_app_meta_data	🔴 keys present are only provider, providers
Custom access-token hook	❓ not visible from SQL; no supporting function exists
Any RLS policy referencing an admin	🔴 zero of 45

role-like columns in the entire public schema, measured: workspace_members.role_key and audit_log.actor_role (free text, on an empty table). That is the complete list.

5.2 requireAdmin() — the admin primitive, and it is dead

supabase/functions/_shared/auth.ts:

const { data: profile, error } = await result.serviceClient
  .from('profiles')            // ← dropped by the ADR-0020 baseline
  .select('role')              // ← a column that no longer exists anywhere
  .eq('id', result.user.id)
  .maybeSingle();
if (error || !profile || !['admin','super_admin'].includes(profile.role)) {
  throw new ForbiddenError('Admin privileges required');
}

Measured: to_regclass('public.profiles') is null. So the query always errors, the guard always throws, and no caller could ever be admitted. It has zero production callers — only a test, and that test asserts the missing-header case, which returns before the query runs.

Tracked as QRS-889. This is the QRS-013 green-no-op and the QRS-246 documented-but-unimplemented standard, in the same eleven lines — sitting in the one function whose job is to decide who is an administrator. It fails closed, which is the correct direction, and it must be treated as absent rather than broken: the fix is designing a platform-role model, not repairing a query.

5.3 What the RBAC design asks for versus what could back it

RBAC.dc.html specifies a roles library, a permission matrix of every module × 9 actions, inheritance, reusable permission groups, dependency/conflict validation, per-role members, time-bound assignments, an approval queue and an exportable audit log. Backing store today: none of it. The design itself already records that role templates and contracts "do NOT exist yet" — that half is honest.

---

6. Core platform entities and relationships

Domain	Tables	Live rows	Status
Identity	users	7	🟢
Tenancy	workspaces, workspace_members	5 / 5	🟢 solo only
Enterprise	organizations, workspace_groups, workspace_group_members	0 / 0 / 0	🟡 built, unexercised
Platform model	business_archetypes 3, industries 14, process_primitives 11, features 22, feature_grant_scopes 8, feature_grants 56	seeded	🟢
Plans	platform_plans 4 (free, pro, business ₹9,999, enterprise)	4	🟢 registry only
Subscriptions	workspace_subscriptions	1	🟡 current state, no history
Setu Card	setu_cards 5 (2 published), setu_card_hours, setu_card_links, setu_card_templates 1 (default/v1)		🟢
Catalogue	catalog_items 7, catalog_categories, catalog_item_variants, catalog_item_media		🟢
Commerce	orders 19, order_items 23	9 unpaid / 10 paid, all pending	🟢
Payments	payments 18, payment_events 40, payout_accounts 1, payout_transfers 2, platform_settlements 2, payment_disputes 0, payment_reconciliation_runs 2, payment_reconciliation_exceptions 6, platform_tax_identity 1		🟢 the deepest domain
Chat	conversations, messages, message_states, conversation_states, conversation_labels, conversation_label_members, quick_replies	all 0	🟢 built, never used
Moderation	conversation_reports	0	🟡 see below
Reminders	reminders 4, reminder_occurrences 3, reminder_categories 4		🟢
Media	media 1 row · storage 0 objects		🟡
Location	states 36, cities 178, locations 0		🟢 reference data
Infra	idempotency_keys 74, outbox 0, resource_holds 0, reserved_slugs 2572, workspace_tax_identity_history 5, merchant_payment_alert_reads 0		🟡 outbox has no drain
Audit	audit_log	0	🔴 no admin writer (one user-side writer)

6.1 audit_log — the shape is right and nothing uses it

id bigint PK · actor_user_id → users · actor_role text · actor_kind CHECK IN ('user','system','support','webhook')
action text CHECK (~ '^[a-z_]+\.[a-z_]+$') · scope_kind · scope_id · target_table · target_id
before jsonb · after jsonb · reason text · request_id · ip_hash · occurred_at

This is a good design: before/after, a typed actor kind, a reason field, a request id, a hashed IP. It has 0 rows and 0 policies, and exactly one writer: set_my_primary_context inserts a row, best-effort, when a principal changes their own primary context (20260817230500_v2_set_my_primary_context.sql:104). No admin action, trigger or Edge Function writes it. Every admin action in every design carries "and the reason you type is kept forever on the audit row"; there is no such row today. Tracked as QRS-890.

6.2 Moderation — one primitive, three gaps

conversation_reports: reason (non-empty CHECK), reported_by CHECK IN (consumer,merchant), reviewed_at, resolution CHECK IN (no_action,warned,suspended), plus CHECK (reviewed_at IS NOT NULL OR resolution IS NULL).

🟡 The review half is modelled but: no reviewed_by (a resolution has no actor), the two policies are reporter-scoped only (no moderator can read the queue), and only chat is reportable — cards, catalogue items and media have no report path at all.

6.3 Two orphaned mechanisms worth knowing before designing around them

• outbox (12 columns, topic/payload/attempts/dedupe) has 0 rows and no drain — no pg_cron, no pg_net, no worker. ADR-0027's purge-by-tag and ADR-0025's campaign scheduling both assume it. Already tracked as QRS-885 (the platform has no unattended execution of any kind), so it is not re-logged here.
• resource_holds.holder_user_id references auth.users(id), while every other user FK in the schema references public.users(id). A one-table inconsistency (QRS-896).

---

7. What is actually deployed vs documented vs missing

7.1 Deployed and exercised 🟢

Solo-merchant tenancy · Google sign-in · onboarding + workspace provisioning (provision_merchant_workspace) · Setu Card publish and public read (get_public_setu_card, get_public_catalogue) · catalogue with variants, stock commitment and unique-item claiming · public order placement (place-public-order) · Razorpay marketplace payments with the four-step correlation fallback, monotonic status ranking, refund-aware collection predicate, settlements, transfers and detect-only reconciliation · reminders · consumer discovery feeds · least-privilege grants · idempotency.

7.2 Built but never exercised 🟡

Enterprise (organizations, tree, groups, sharing, oversight) — 0 rows · chat and media messaging — 0 rows · moderation reports — 0 rows · outbox — no drain · storage — 0 objects, 1 bucket · disputes — 0 rows.

7.3 Documented, decided, nothing in the database 📘

Platform RBAC (ADR-0006, self-described "still governing, still 0% built" — confirmed still true 2026-08-26) · analytics_events and the whole ADR-0010 read model · vendor verification (ADR-0026, Proposed) · campaigns (ADR-0025) · WhatsApp communications (ADR-0029/0030) · platform-subscription collection (ADR-0002 Model A) · feature flags (QRS-296).

7.4 Missing entirely 🔴

No table anywhere for: platform admins/staff · roles · permissions · leads/CRM · analytics or any event stream · notifications · WhatsApp campaigns, consent or inbound messages (the template registry and the outbound message ledger came later, in 20260901120000_v2_communications_whatsapp.sql) · verification/KYC · invoices or billing documents · subscription history · affiliates/referrals · ad campaigns or advertisers · curation shelves · system configuration/controls · impersonation sessions · data-export requests.

A grep of information_schema.tables for %lead%, %analytic%, %scan%, %event%, %activity%, %notification%, %campaign%, %verif%, %invoice%, %template%, %message%, %subscription% returns exactly: setu_card_templates, payment_events, messages, message_states, workspace_subscriptions. Nothing else matched.

7.5 Two absences with outsized consequences

(a) There is no activity or event stream. No table records "a merchant did something meaningful". The in-progress Users desk derives lifecycle (new/engaged/slipping/dormant/churned), stuck(), cohort retention, the conversion funnel, the 12-month trend and every intervention row from lastAction + lastActionKind. Not one of those is computable today, and the deficit is not a query — it is a table that has never been collecting. Every day without it is a day of history that cannot be reconstructed. Tracked as QRS-891.

(b) There is no login history. auth.audit_log_entries has 0 rows. auth.users.last_sign_in_at is populated for all 7 and auth.sessions holds 19 rows, so "last seen" and "active sessions" are answerable — but logins30, sign-in trends and "signed in 11 times and did nothing" are not.

---

8. Admin Portal feature feasibility

Verdicts are about the backend, not the screens. Users.dc.html, Leads.dc.html and Campaigns.dc.html are in progress per the owner (2026-08-26) and are read here as requirements input, never as a frozen contract — their data demands are listed so the backend can be built to the confirmed shape rather than retrofitted to it.

Admin capability	Verdict	What exists	What is required
Admin authentication	🔴 not supported	Google sign-in for everyone; nothing distinguishes staff	Platform-principal model + admin gate. requireAdmin() is dead (§5.2)
Admin identity	🔴 not supported	—	platform_admins table or a claim; MFA decision
Roles & permissions	🔴 not supported	role_key text CHECK, enforced by one function (set_workspace_location)	Roles + permissions store; ADR-0006 is 0% built
User management	🟡 partially	users (7 rows), status enum ×3, get_my_context shape, auth admin API via service role	Email/phone search path; 3 missing statuses; verification; source/attribution; activity stream; an admin audit writer
Workspace management	🟡 partially	35 columns, suspension with provenance, tree, groups	Admin read path (0 policies admit an admin); suspension RPC + cascade to card; transfer-ownership RPC
Merchant/business management	🟡 partially	workspace + card + catalogue + orders all joinable by workspace_id	Same admin read path; verification state; no activity signal
Consumer management	🔴 effectively unsupported	users with 0 memberships; anonymous buyer columns on orders	0 consumers exist and handle_new_user defaults to 'business'; no consumer tier, no consumer entry route
Subscription management	🟡 partially	platform_plans 4, workspace_subscriptions 1, feature_grants 56, resolve_features with per-axis provenance	No history table (PK is workspace_id); no approvals; no add-ons; no promotions; no ACV/deal model
Setu Card management	🟡 partially	5 cards, status ×4, one template default/v1, retire guard, slug write-once	One card per workspace (UNIQUE); card suspension has no provenance; template library is 1 row
Lead/CRM management	🔴 not supported	no leads table	Entire domain: leads, contacts+consent, stage-event log, lost reasons, assignment, field registry, segments
Content management	🟡 partially	setu_card_templates registry (1 row), media (1 row)	0 storage objects, no media bucket; no curation/shelf tables
Marketplace/commerce	🟢 supported	orders, order_items, catalogue, variants, stock commitment, consumer feeds	Admin-scoped read RPCs only
Payments	🟢 supported (best-built domain)	9 payment tables, reconciliation runs + exceptions, tax identity, settlements	Admin read path; never expose provider_fee_* outside platform scope
Analytics	🔴 not supported	0 views, 0 event tables, pg_stat_statements only	ADR-0010 read model from scratch
Notifications	🔴 not supported	—	Entire domain
Communication (WhatsApp)	🟡 partially (OTP only)	Added after this measurement by 20260901120000_v2_communications_whatsapp.sql: the WABA, phone-number and template registries and the communication_messages ledger with its events, service-role only; the only APPROVED templates are three qrsetu_otp rows	The rest of ADR-0029/0030: consent ledger, suppression, campaigns, inbound, template-status sync, cost
Moderation	🟡 partially	conversation_reports with resolution enum	reviewed_by; moderator read policy; report paths for cards/items/media
Audit logs	🟡 table + one user-side writer	15 well-designed columns; set_my_primary_context writes it	An admin writer. 0 rows, 0 policies, no admin action writes it
System configuration	🔴 not supported	—	The 28 controls in controls.js are design-side JS; no table
Affiliates & growth	🔴 not supported	—	Entire domain
Ad Manager	🔴 not supported	promo_slot is inert by design (ADR-0004)	Deliberately deferred; do not build ahead of compliance_profile
Mission Control	🔴 not supported	—	Depends on the configuration store + every desk's aggregate
Overview / command centre	🔴 not supported	—	It aggregates the desks above; "scans" has no source table at all

8.1 The in-progress Users desk, field by field, against Dev

Read as a requirements checklist, not a verdict.

Field the desk uses	Backed by	Status
kind person/account	users vs workspaces	🟢 derivable
name, ownerName	display_name + owner membership	🟢
handle (email or slug)	auth.users.email (service role) / setu_cards.slug	🟡 auth-schema only
phone	—	🔴 null for all 7; no column on public.users
population ×5	primary_context + membership count	🟡 3 of 5; lead and staff have no table
status ×6	users.status ×3	🟡 3 of 6 unrepresentable
verified none/phone/kyc	—	🔴 no column (ADR-0026 Proposed)
joined	users.created_at	🟢
lastLogin	auth.users.last_sign_in_at	🟢 service role
logins30	auth.audit_log_entries = 0 rows	🔴
lastAction / lastActionKind	—	🔴 no activity stream — this breaks lifecycle, cohorts, funnel, trend and every intervention row
plan, mrr	workspace_subscriptions + platform_plans.price_minor	🟡 nominal; no collection for Model A
source ×6	—	🔴 no attribution column
cards	setu_cards	🟡 capped at 1 by UNIQUE
leads	—	🔴 no table
customers, orders	orders / order_items	🟢 derivable
devices	auth.sessions (19)	🟡 approximation
areaId	city_key	🟡 city, not neighbourhood
industry	workspaces.industry_key	🟢

Actions: plan 🟡 (grants + subscription writable, no audit) · reset/signout 🟢 (auth admin API) · transfer 🟡 (raw UPDATE, no RPC/audit) · suspend 🟡 (columns exist, no cascade/RPC) · verify 🔴 · message 🔴 · block 🔴 (no state) · export 🔴 · erase 🔴 (no cooling-off, no two-key, no stub) · impersonate 🔴 and it needs a design, not a build — read-only, time-boxed, user-notified, per-page logged impersonation has no Supabase primitive, and every one of these eleven actions is specified to write an audit row that nothing can currently write.

---

9. Security & RLS considerations for Admin

1. An admin cannot be an RLS role here. With zero table grants to authenticated, adding TO authenticated USING (is_admin()) grants nothing. Admin access is RPC + Edge Function or nothing.
2. Never ship a broad is_admin() bypass policy. CLAUDE.md's category-3 rule — no policy may grant access by role alone — was written for consumers and applies with more force to a role that can read every tenant. Prefer narrow, purpose-built SECURITY DEFINER admin RPCs, each projecting only the columns one screen needs, each granted to nothing by default.
3. service_role in an Edge Function is a total-authority key. An admin EF must gate on a real platform-principal check before any query, and that check must not be requireAdmin().
4. The disclosure boundary is already load-bearing. provider_fee_minor / provider_fee_tax_minor are the platform margin and a merchant must never see them. An admin projection that reuses a merchant projection will leak the other way round; keep them separate functions.
5. Auditability is a precondition, not a phase. Eleven of eleven designed user actions specify a stored reason. Shipping any of them before audit_log has an admin writer produces irreversible, unattributable changes to real merchant data.
6. users.status is decorative today. Suspension must actually revoke: RLS helpers (my_workspace_ids() and friends) do not check it, and neither does any Edge Function.
7. Impersonation is the highest-risk item in the whole portal and has no primitive. Treat it as an ADR, not a ticket.
8. Two search_path-mutable functions should be pinned before the surface widens.

---

10. Recommended Admin foundation, and the sequence

The foundation is five things, and the first four are backend. None of the sixteen designed desks can be trusted until they exist, because every one of them reads tenant data and writes an audited action.

Phase A — the trust boundary (nothing else can start)

#	Deliverable	Why first
A1	Platform-principal model — a platform_admins (or equivalent) store, an is_platform_admin() helper, and the decision on where the claim lives (table vs JWT claim vs both)	Everything else needs a subject. ADR-0006 is the governing decision and is 0% built
A2	Retire requireAdmin() and replace it with a gate that reads A1	It currently cannot admit anyone and reads as if it works
A3	audit_log writer — one SECURITY DEFINER record_audit_event(...), called by every admin mutation, plus an admin-only read path	No admin action may ship before its audit row can exist
A4	Admin read seam — the pattern: one narrow admin RPC per screen, SECURITY DEFINER, REVOKE ALL FROM PUBLIC, granted to nothing, called only from an admin Edge Function that has already passed A1	Fixes the shape once instead of 16 times

Phase B — the foundation desks the backend can nearly serve

#	Deliverable	Depends on
B1	Workspace/merchant management — list, detail, suspend/reactivate with cascade to setu_cards, transfer ownership, all as RPCs with audit	A1–A4; card suspension provenance
B2	User management (read + safe actions) — directory, detail, reset, sign-out-all, using the auth admin API	A1–A4; accept that lifecycle, verification, source and phone are absent and show them as absent rather than fabricated
B3	Subscription & entitlement visibility — resolve_features already returns per-axis provenance, which is most of the Subscriptions record	A1–A4
B4	Payments & reconciliation oversight — the exceptions queue has 6 real rows and a resolution path	A1–A4; margin-disclosure separation

Phase C — the enabling tables (each unblocks several desks)

activity_events (unblocks lifecycle, cohorts, funnel, interventions, Overview, and starts collecting history that cannot be backfilled) · verification state (ADR-0026) · the missing users.status values · subscription history · a configuration/controls store · reviewed_by + a moderator read path.

Phase D — new domains, each its own ADR and wave

Leads/CRM · communications · analytics read model · affiliates · curation · ad manager. These are not admin screens with missing queries; they are absent product domains. Building the screen first produces a UI over fabricated data, which is worse than no screen.

The sequencing judgement, stated plainly

Do not start Admin Portal UI implementation with Phase A incomplete. Not because of process, but because the first admin write to real merchant data with no audit row and no admin identity is unattributable and irreversible, and because a screen built against absent data teaches everyone that the data exists. The cheapest correct move is A1–A4 first (they are small — one table, one helper, one function, one pattern), then B1/B2 against them. Phase C's activity_events should land as early as possible for a reason unrelated to Admin: it is the only item on this page whose delay destroys information permanently.

---

11. Documentation vs implementation

11.1 Where documentation matches implementation ✅

ADR-0014 (least-privilege grants — realised exactly: zero grants to anon/authenticated) · ADR-0020/0021 (the four-layer platform model and the 8 × 3 grant table, built as specified, including feature_grants_live_unique_idx covering every scope target through COALESCE) · ADR-0006's own banner ("still governing, still 0% built" — confirmed true) · ADR-0010's banner (analytics_events does not exist) · the payments SSOT pages · config.toml ↔ deployed function set.

11.2 Where implementation has moved ahead of documentation ⬆

Claim	Measured reality
QRS-694 "four archived pre-v2 EFs still ACTIVE, two on the money path" — recorded OPEN	Closed. Deployed slug set == repo live set; the three archived slugs are absent
QRS-643 "manage-reminder has a folder and no config.toml entry; live function deployed verify_jwt=false"	Entry present and deployed verify_jwt = true. The gate is still broken; the symptom is not
CLAUDE.md: "sessionStore.accountType is device-local only; get_my_auth_context does not project it"	The function is get_my_context, it does project primary_context, and set_my_primary_context persists it server-side
CLAUDE.md ADR enumeration "0001-0012, 0014-0017, 0019-0028"	0029 and 0030 exist. The count (29 incl. index) is right, so check:claims stays green — the enumeration is stale, not the arithmetic. Already tracked as QRS-887

11.3 Where documentation is ahead of implementation ⬇

ADR-0006 RBAC · ADR-0010 analytics · ADR-0025 campaigns (and the outbox they need has no drain) · ADR-0026 verification · ADR-0029/0030 communications · ADR-0002 Model A subscription collection · ADR-0027 purge (wired, but Cloudflare credentials are unset on Dev, so only the failure branch is ever exercised).

11.4 Where the designs assume capabilities that do not exist ⚠

Activity/lifecycle · login counts · verification · lead records · WhatsApp beyond the OTP ledger · analytics/scans · neighbourhood areas · multiple cards per account · subscription history · a configuration store · impersonation · six user statuses · phone-number search.

11.5 Where the backend has capabilities the Admin designs do not represent 🔎

Worth reading, because these are paid-for capabilities with no operator surface:

• feature_grants supports 8 scopes × 3 axes with effect, limit_value, limit_period, on_exceed, grace_until, effective_from/until, source precedence and a partial-unique live constraint. The Subscriptions desk exposes a fraction of this. It is the most powerful thing in the schema.
• resolve_features already returns applicability_source, entitlement_source, availability_source separately — the exact provenance the entitlement console wants, available today.
• The workspace tree, path[], groups, and the five organizations sharing flags are fully built and no admin screen manages them.
• payment_reconciliation_exceptions has 6 real rows, a severity, a subject, local-vs-provider jsonb and a resolved_by — a complete operator workflow with no operator screen.
• workspace_tax_identity_history (5 rows) is a real, working, time-keyed history table — and the pattern workspace_subscriptions needs.
• idempotency_keys (74 rows) records scope, request hash, status, response and status code: a ready-made support/debug surface.

---

12. Answers to the twelve questions, in one place

1. Actual Supabase architecture — §1. 55 tables, 45 policies, 69 functions, 10 EFs, 0 views, 1 empty bucket, no pg_cron, RPC-only client surface.
2. User architecture — §2. 9 columns, no email/phone, 3 statuses, 2 contexts, 7 rows, Google-only.
3. Workspace architecture — §3. 35 columns, tree-capable, suspension-capable, 5 solo rows, 0 orgs.
4. User ↔ workspace — §4. (workspace_id, user_id) PK, many-to-many capable, one role per membership, role_key enforced by one function only (set_workspace_location).
5. Roles & permissions — §5. A text CHECK constraint and a dead requireAdmin(). Nothing else.
6. Core entities — §6. Payments deepest; chat/enterprise built-but-empty; audit empty.
7. Actually deployed — §7.1. Repo and Dev are set-equal on migrations and functions; three EF bundles lag by 3 days.
8. Documented/envisioned — §7.3, §11.3.
9. Missing/incomplete — §7.4, §7.5. The two that hurt: no activity stream, no login history.
10. Admin feasibility — §8. Of 17 capability areas: 2 supported, 7 partial, 8 not supported.
11. Backend gaps — §13 (the gap register).
12. Security/RLS — §9. Eight considerations; the first is that admin cannot be an RLS role here.
13. Recommended foundation — §10, Phase A (four items).
14. Implementation sequence — §10, A → B → C → D, with the explicit instruction not to start UI before A.

---

13. Backend gap register

Priority: P0 blocks any admin work · P1 blocks a foundation desk · P2 blocks an advanced desk.

#	Gap	Existing entity	Required change	Audit?	Depends on	Pri
G1	No platform-admin identity	—	New store + is_platform_admin(); decide table vs JWT claim	yes	ADR-0006 · QRS-889	P0
G2	requireAdmin() reads dropped profiles	_shared/auth.ts	Replace with G1 gate; keep fail-closed	—	G1 · QRS-889	P0
G3	audit_log has no admin writer and no read policy	audit_log (15 cols, 0 rows, one user-side writer)	record_audit_event(...) + admin read RPC; call from every admin mutation	itself	G1 · QRS-890	P0
G4	No admin read path (0 grants, 0 admin policies)	45 policies, 0 grants	Per-screen narrow SECURITY DEFINER RPC + admin EF pattern	yes	G1	P0
G5	No roles/permissions store	role_key CHECK	roles + permissions + assignments (time-bound)	yes	G1	P1
G6	No activity/event stream	—	activity_events append-only, workspace+user scoped, typed kind	—	ADR-0010 · QRS-891	P1 — starts losing history now
G7	users.status missing pending/blocked/deactivated	users.status CHECK ×3	Widen the CHECK; define semantics per state	yes	QRS-892	P1
G8	Suspension enforces nothing	users/workspaces/members.status	Suspend/reactivate RPC; cascade to setu_cards; make RLS helpers status-aware; revoke sessions	yes	G1,G3 · QRS-892	P1
G9	setu_cards suspension has no provenance	setu_cards.status	Add suspended_at/reason/by (mirror workspaces)	yes	G8 · QRS-892	P1
G10	No verification state	—	verification_status + evidence trail per ADR-0026	yes	ADR-0026	P1
G11	No subscription history	workspace_subscriptions PK (workspace_id)	History table (pattern: workspace_tax_identity_history)	yes	QRS-893	P1
G12	No email/phone search path	auth.users only	Admin EF search over auth + public; consider users.phone	yes	G1,G4	P1
G13	Moderation queue unreadable + no actor	conversation_reports	reviewed_by; moderator read RPC; report paths for cards/items/media	yes	G1,G4	P1
G14	No login history	auth.audit_log_entries = 0	Decide: enable auth audit retention, or derive from G6	—	—	P2
G15	No attribution/source	—	source on users/workspaces, captured at signup	—	G6	P2
G16	One card per workspace	setu_cards_workspace_id_key	Product decision first, then drop the UNIQUE	—	—	P2
G17	No neighbourhood/area dimension	cities (178), area_key = city_key	areas table + FK, or accept city granularity	—	—	P2
G18	No configuration/controls store	design-side controls.js	platform_controls with high-risk flag + change log	yes	G1,G3	P2
G19	outbox has no drain; no scheduler at all	outbox (0 rows)	Worker/cron; pg_cron and pg_net are not installed. Tracked: QRS-885	—	—	P2
G20	No leads/CRM domain	—	Leads, contacts+consent, stage events, reasons, assignment, fields	yes	ADR needed	P2
G21	No communications domain	—	All of ADR-0029/0030	yes	ADR-0029/0030	P2
G22	No analytics read model	0 views	ADR-0010; depends on G6	—	G6	P2
G23	No impersonation primitive	—	ADR first — read-only, time-boxed, notified, per-page logged	yes	G1,G3	P2
G24	No DPDP export/erasure workflow	users.status='deleted'	Export job, 7-day cooling-off, two-key approval, audit stub	yes	G1,G3	P2
G25	resource_holds FK points at auth.users	resource_holds	Re-point to public.users for consistency	—	—	P2
G26	2 functions with mutable search_path	catalog_item_orderable, consumer_attributes_match	Pin search_path	—	—	P2
G27	Media has no public URL and 0 objects	media (1 row), 1 bucket	Bucket/R2 provisioning + setPublicMediaBaseUrl at boot	—	—	P1 (product)
G28	Consumers default to 'business'	handle_new_user	Consumer signup must set primary_context='individual'; consumer entry route	—	—	P1 (product)

Separation for planning: G1–G4 are the only items that block starting. They are four small artifacts — one store, one helper, one function, one repeatable RPC pattern — and everything else in this register is either a foundation desk's prerequisite or a new product domain with its own ADR.

---

14. What this assessment did NOT verify

Stated so a green reading is not over-read:

• Production. Not visible to this session's token. Nothing here describes it.
• Whether an RLS policy is correct. Policies were read, not executed. catalog_item_media_select and catalog_item_variants_select use item_id IN (SELECT i.id FROM catalog_items i), which is scoped only because catalog_items' own SELECT policy applies inside the subquery. That is structurally sound and was not proved by pgTAP (QRS-897).
• Edge Function runtime behaviour. The deployed set, verify_jwt and bundle timestamps were measured; no function was invoked.
• Auth provider configuration (SMTP, OTP, custom access-token hooks) beyond what SQL shows. The measured facts are: 7 users, all confirmed, provider google only, no admin claim, 0 auth audit rows.
• Whether the Admin designs are complete. Users, Leads and Campaigns are in progress per the owner. Their fields were read as requirements input, not as a parity contract.
• Storage policy contents. 4 policies counted, not read.
• Row-level data quality. Counts and distributions only; no records inspected beyond aggregates.