QRSETU Dev Portal

Appearance

ADR-0006 · RBAC & authorization model ​

Status: 🟢 Accepted (recommendation B; D1–D3 signed off 2026-07-19) · Depends on: ADR-0001 · Related: ADR-0003, ADR-0005

Context ​

Schema reality check (2026-07-19)

A direct read of the baseline (20260710134136_baseline_schema_from_prod.sql:1328) refined the picture below. profiles already carries three authorization-relevant columns, not one: role varchar DEFAULT 'user', an unused permissions jsonb column, and is_demo_account boolean. So the capability store this ADR recommends (permissions) and the demo-account concept the ops-role decision needs (is_demo_account) already exist in the schema — this is largely formalizing columns that are present but unwired, not adding new ones.

Authorization in the real app today is driven by one flat string column, profiles.role (the sibling profiles.permissions jsonb column exists but is read nowhere), resolved in three places:

  • Client, UX gating — src/contexts/SupabaseAuthContext.jsx fetches profiles.role and exposes userRole; src/components/AdminRoute.jsx allows 'admin' | 'super_admin', src/components/ProtectedRoute.jsx gates /dashboard/* and bypasses onboarding for admins.
  • Server, the real boundary — the Edge Function kit's requireAdmin (supabase/functions/_shared/auth.ts) re-reads profiles.role with the service client and rejects anything not in ['admin','super_admin']. This is the actual enforcement point; the React guards are convenience only.
  • RLS — every user-facing table isolates by auth.uid() = user_id, i.e. ownership, not role. There is no role-aware RLS anywhere in the 58-table baseline.

So the live model has exactly three platform roles — user, admin, super_admin — on one axis: "is this person QRSETU staff, and how much." That is the whole system.

The Claude Designs prototype has designed a second, different axis. CONTRACTS.md §2 defines a workspace-scoped membership rank:

ROLE_RANK { viewer < staff < accountant < manager < owner }

These are tenant roles — what a member can do inside one business, unrelated to whether they are QRSETU staff. The desktop merchant console already renders nav and actions against these ranks; the real backend has no workspace_members, no per-tenant role, and no way to express "this accountant can see invoices but not edit the menu." ADR-0001 already sequenced the tenancy side of this (build the organizations wrapper now, defer the full Workspace → Member layer behind real multi-seat demand). This ADR decides the authorization side that rides on top of it.

A third pressure comes from ADR-0005: affiliates and (Tier 1) agencies. The temptation is to add 'affiliate' / 'agency' as profiles.role values. That would be a category error — being an affiliate is a commercial relationship (an affiliates row exists for that profile), not an access level. Conflating the two is exactly how a flat role column rots.

Two problems in the current implementation, independent of any future model ​

  1. Client-side privilege-escalation smell. SupabaseAuthContext.jsx resolves the role as DB → then user_metadata.role → then app_metadata.role → then 'user'. user_metadata is self-writable by the end user (supabase.auth.updateUser). A user with no DB role row could set user_metadata.role = 'admin' and the client would render the entire admin tier for them. The real damage is bounded — every admin EF still calls requireAdmin, which is DB-only, so their actions 403 — but it still ships and mounts admin screens to a non-admin (information disclosure + broken UX), and it means the client and server disagree on who is an admin. The fix is small: trust only the DB (or app_metadata, which is service-role-only), never user_metadata.
  2. Guards read tables directly. Both SupabaseAuthContext.jsx and ProtectedRoute.jsx call supabase.from('profiles') — violating the [ENFORCED] "no from() in src/" rule. Role/onboarding lookup should go through a get_my_auth_context RPC (see Consequences).

Decision drivers ​

  • The three platform roles are real and in production — this ADR must formalize them, not replace them.
  • The five tenant roles are real in the prototype but backed by nothing — they must be defined as the target and sequenced with ADR-0001's Workspace/Member phase, not built speculatively now.
  • Two orthogonal axes (platform staff level vs. in-tenant membership rank) must not collapse into one column, or every future permission question becomes an if role === 'super_admin' || role === 'manager' || … tangle.
  • The security boundary must stay server-side (RLS + EF requireAdmin); client guards are UX. Any new model must not tempt anyone to move enforcement into React.
  • Individuals (dual-audience decision, locked 2026-07-14) have no tenant — they must have a working authorization story with zero workspace/role rows.
  • Don't over-build: an admin-configurable, DB-driven permission matrix is the RBAC equivalent of building full workspaces before a paying customer — attractive, and premature.

Options considered ​

A — Keep the flat profiles.role, just harden it ​

Fix the metadata fallback, route lookups through an RPC, document the three roles. Add no second axis; treat the prototype's viewer…owner ranks as prototype-only until much later.

  • For: almost zero work; matches what's shipped.
  • Against: leaves the prototype/backend divergence unaddressed and gives the Workspace/Member phase (ADR-0001 option A) no authorization model to land against — it would have to invent one under deadline pressure. Also offers no home for affiliate/agency access that isn't the same category error.

B — Two explicit axes: platform role (now) + tenant role (defined now, built with the Workspace phase), capabilities-as-code ​

Formalize platform role as-is (user / admin / super_admin) and harden it immediately. Define the tenant role axis (viewer < staff < accountant < manager < owner, matching CONTRACTS.md) as the named target that ships with ADR-0001's workspace_members table — not before. Express permissions as a centralized capability map in the shared TS core (role → set of capabilities, e.g. menu:edit, invoice:read, billing:manage), consumed identically by React guards (UX) and asserted server-side in RLS/EFs (enforcement), rather than scattering role-string comparisons. Affiliate/agency access is modeled as its own concern (an affiliates row + a future agency→sub-account delegation), never as a profiles.role value.

  • For: formalizes what's real, sequences what isn't, and gives the deferred Workspace phase a ready authorization contract; capability-map keeps role logic in one testable place in the portable core (reused by the Expo app); cleanly separates "QRSETU staff" from "member of a business" from "commercial affiliate."
  • Against: capability map is a new abstraction to maintain; requires discipline to keep enforcement server-side.

C — Full DB-driven RBAC now (roles, permissions, role_permissions, admin-editable matrix) ​

Model roles and granular permissions as data, editable by super-admins, checked dynamically everywhere.

  • For: maximally flexible; custom roles per enterprise customer with no code change.
  • Against: large build (tables + admin UI + RLS integration + caching) for flexibility no customer has asked for; dynamic permission checks in RLS are hard to write correctly and audit; same premature-generalization trap ADR-0001 avoided. Nothing about B blocks a later promotion to C — the capability map can graduate into a table when a customer actually needs custom roles.

Recommendation ​

B, sequenced to mirror ADR-0001 (formalize-now / defer-the-heavy-layer / leave C as a future promotion). Concretely:

  1. Now (no new tables — the columns already exist): treat user | admin | super_admin as the canonical, closed platform-role enum, with super_admin a real superset per D1; fix the user_metadata fallback so the client trusts only DB/app_metadata; move role + onboarding lookups behind a get_my_auth_context RPC; introduce the capability map in the shared core and have the React guards consume it instead of raw string checks. Persist per-user capability grants in the existing profiles.permissions jsonb column (present in the baseline, currently unread) rather than adding a table — this is the natural home for the templates:demo-all ops grant (D2) and any per-user overrides. requireAdmin stays the server boundary and is already correct; add a requireCapability(req, cap) helper beside it that reads role + permissions.
  2. With ADR-0001's Workspace phase (deferred, demand-gated): add workspace_members(workspace_id, profile_id, role) with the viewer…owner enum; extend the capability map with tenant capabilities; move the relevant RLS from ownership-only to membership-aware. Not before a real multi-seat customer.
  3. Affiliate/agency (ADR-0005): never a profiles.role. Affiliate = presence of an affiliates row; agency sub-account management = a delegation that reuses the tenant-member mechanism when it lands, scoped to affiliate resources only.
  4. C is explicitly not adopted now but is the sanctioned upgrade path if an enterprise customer needs custom roles — the capability map is designed to graduate into role_permissions data without a rewrite.

Consequences ​

  • Security fix (do first, independent of the rest): stop trusting user_metadata.role in SupabaseAuthContext.jsx. Log as a real QRS-### — it's a shipped defense-in-depth gap, not just future work.
  • A new get_my_auth_context RPC returns { role, onboarding_completed, … } in one call, removing the two from('profiles') violations in the guards and collapsing the current two-round-trip (session then role) into one. Standard get_* RPC shape.
  • The capability map lives in the platform-agnostic core (no DOM imports) so web guards, future Expo guards, and EF authorization all import the same source of truth. Server code still enforces — it does not trust a capability decision made on the client.
  • The prototype's ROLE_RANK and the desktop console's rank-gated nav become "designed ahead of backend, ships with Workspace phase," consistent with how ADR-0001 labeled the workspace switcher.
  • No existing RLS policy changes in step 1. Membership-aware RLS is a step-2 (deferred) concern.
  • The EF kit gains, in step 2, a requireCapability(req, cap) / requireWorkspaceRole(req, ws, rank) helper alongside requireAdmin, following the same pattern.

Decisions (product sign-off 2026-07-19) ​

The three open questions were answered by product. Recorded here as decisions:

D1 — super_admin vs admin: make it a real distinction (industry-standard two-level staff model) ​

Answer: cosmetic today; product wants the industry-best approach adopted for the long term.

Decision: adopt the standard two-tier internal-staff model. admin = day-to-day operator (support, content moderation, viewing tenants, managing campaigns/ads, the ops/demo capability in D2). super_admin = platform owner: everything admin can do plus the "manage the platform itself" capabilities that must not be one-mistake-away for every operator — managing other admins' roles, billing/subscription-tier configuration, feature-flags / kill-switches, RBAC/capability changes, and destructive/global data actions. This maps cleanly onto the capability map (admin gets the operator capability set; super_admin additionally gets platform:manage-admins, platform:billing-config, platform:kill-switch, rbac:manage). No third internal tier is introduced now — two levels is the proven default; finer granularity graduates into option C later if a real need appears.

D2 — Ops/sales "demo everything" capability (built on the existing is_demo_account) ​

Answer (reframed from the impersonation question): product's real need is that internal team members can show a prospect every template's Service Card live — an ops/sales person meeting a business should be able to pull up any template-based demo Service Card regardless of plan gating, to give a flavour of the product.

Decision: this is not tenant impersonation and not a new top-level role — it is a capability, templates:demo-all (name TBD), granted to admin/super_admin (and grantable to a future ops capability bundle) that unlocks preview/demo of all templates and their Service Cards, bypassing the plan/entitlement gate for preview only (no publish, no write to a real tenant). It rides on the capability map from Option B, and it reuses the schema that already exists: the is_demo_account boolean on profiles and the templates / template_selections tables (see ADR-0003). Cross-links ADR-0003 because "demo any template's Service Card" is a template-system surface as much as an authz one — ADR-0003's portable TemplateDocument is what a demo render consumes. Open sub-question: should demo Service Cards be seeded as real is_demo_account=true profiles (so they behave exactly like a live card) or rendered ephemerally from the template with sample data? Recommend the former (seeded demo accounts) so ops shows the real thing; flag for confirmation.

Mobile field-demo (confirmed 2026-07-20): templates:demo-all is non-negotiable on mobile. Ops/sales meet prospects in the field without a laptop, so the capability must have a mobile surface — unlock and show sample Service Cards for any business domain live, on a phone, to pitch QRSETU on the spot. Per the surface-matched frontend decision (ADR-0011), this is delivered as mobile web: the public Service Cards are DOM-web-rendered (Stack 1), so the field-demo is the ops user opening those real cards with sample data via the admin PWA (capability-gated) — no native admin binary required. A hard requirement of the R1 mobile scope, not a desktop-only or later feature.

D3 — Workspace identity = business name, fallback to member name (see ADR-0001) ​

Answer: a workspace should be identified by the business name, falling back to the member (individual) name for individual accounts.

Decision: this is primarily an ADR-0001 concern and is recorded there in detail. For RBAC it means: the owner tenant-role (step 2) is whoever the workspace's business/brand is registered to; individuals remain a single-member "workspace of one" identified by full_name, never forced into an org. It maps to existing columns — brand_name (business) with full_name (individual) fallback — not a new business_name/display_name column. The trigger for building the full Workspace/Member layer is unchanged (demand-gated, step 2); what D3 settles is the naming/identity contract that layer will use. See the Profile/Settings/Onboarding reconciliation for how this surfaces in the UI.

Remaining open sub-questions ​

  • D2's seeded-vs-ephemeral demo Service Card question (recommendation: seeded is_demo_account profiles).
  • Exact capability-slug vocabulary (templates:demo-all, platform:*, etc.) is illustrative until the capability map is authored in the shared core.
  • ADR-0001 — the tenancy layer step 2 depends on; carries the D3 naming decision
  • ADR-0003 — the templates:demo-all (D2) capability renders TemplateDocuments
  • ADR-0005 — why affiliate/agency is not a role
  • Profile/Settings/Onboarding reconciliation — real field set + D3 in the UI
  • profiles.role + profiles.permissions jsonb + profiles.is_demo_account (baseline …:1328) — the columns this ADR wires
  • src/contexts/SupabaseAuthContext.jsx, src/components/{AdminRoute,ProtectedRoute}.jsx — current guards
  • supabase/functions/_shared/auth.ts — requireAuth / requireAdmin / optionalAuth (the real boundary)
  • ui_kits/merchant-console-v2/CONTRACTS.md §2 (ROLE_RANK) — the tenant-role target (Claude Designs project)