Database & RLS

Rewritten 2026-08-08. The previous version's entity map was the retired Prod baseline — 58 tables, profiles as the identity hub, plus bio_pages/bio_links, studio_*, the 12 digital_menu_* tables, subscription_tiers/user_subscriptions and the public_page_ops_* family. All of it is gone from Dev. It carried a STALE banner but still rendered a wrong ER diagram, which is the version a reader actually copies. Index of truth: current architecture state · ADR-0020.

STATE — the baseline is APPLIED and VERIFIED on Dev (2026-08-08)

Measured after the owner ran v2_apply_all.sql, then re-measured after one hardening migration: 26 tables · 16 functions · 22 policies · 11 triggers · 77 indexes · 15 migration rows, RLS enabled on every table, citext + pgcrypto present, and zero tables directly readable by anon or authenticated — every public read goes through a SECURITY DEFINER function.

Migration versions match their filenames exactly with no orphan rows (QRS-267's hazard, checked rather than assumed). Seeds: 753 reserved slugs · 11 primitives · 3 archetypes · 14 industries · 3 plans · 18 features · 8 grant scopes · 34 grants.

⚠ 16 functions, not 17 — corrected here. The authoring-time count came from counting create or replace function statements, and get_public_setu_card is defined twice (created in the cards layer, redefined in the hardening layer to project the SEO columns). 17 statements, 16 distinct functions. A count taken from statements rather than from the catalog is exactly the "expected, unverified" shape, and the live catalog is the authority.

Design principles that shaped every table

Principle	Consequence in the schema
The principal is a USER; the workspace is the BUSINESS tenant	users is separate from workspaces. A consumer has zero memberships and is still a first-class user. Any resolver that requires a workspace cannot serve them.
The public surface is its own table	setu_cards — not a view, not a projection of workspaces. anon reads a table whose every column is intended to be public, which removes the entire projection/allow-list defect class rather than testing it harder.
Text keys, never promoted integers	industries.key, business_archetypes.key, platform_plans.key, features.key. Integer ids promoted across environments caused QRS-249; a text key makes that hazard class unrepresentable.
Domain-specific table names	setu_cards not cards (vCards / invitation cards / event cards are coming), platform_plans not plans (a dairy has meal plans), process_primitives not primitives (collides with apps/mobile/src/ui).
Restrictive default privileges	public was recreated with anon/authenticated holding USAGE only and no ALTER DEFAULT PRIVILEGES. This is the root-cause fix for QRS-214: Supabase grants those roles explicitly, so REVOKE ALL FROM PUBLIC never removed it. The failure mode inverts from "leaks unless you remember" to "inaccessible unless you intend".
Every object carries a COMMENT ON	Tables, functions, policies and triggers are at 100%, gated by npm run check:sql. A header comment is readable only by whoever finds the migration; COMMENT ON travels with the live object into Studio and pg_description.

Entity map — the v2 baseline

outbox intentionally has no foreign keys — it is a transactional work queue keyed by topic + payload, and an FK would couple retry behaviour to the lifetime of the row that enqueued it.

Table groups — 26 tables

Group	Tables
Identity & tenancy	users · organizations · workspaces · workspace_members
Public surface	setu_cards · setu_card_links · setu_card_hours · reserved_slugs
Taxonomy	process_primitives · business_archetypes · industries
Feature control	platform_plans · features · feature_grant_scopes · feature_grants · workspace_groups · workspace_group_members
Catalogue	catalog_categories · catalog_items · catalog_item_variants · catalog_item_media
Infrastructure	locations · media · outbox · audit_log · idempotency_keys

Not here, and that is deliberate: orders · payments · payouts · parties/CRM · schedules · recurrence · ledger · balances · assets · campaigns · RBAC roles · billing accounts · subscriptions · seats · analytics events. All 🟠 Wave 2 — designed, no table. See current state §10.

Functions — 16, in five naming families

Family	Functions
get_* — retrieve	get_industries · get_platform_plans · get_my_features · get_public_setu_card · get_public_catalogue
resolve_* — derive	resolve_features · resolve_workspace_plan
is_* — predicate	is_slug_reserved
my_* — RLS helper	my_workspace_ids · my_oversight_workspace_ids · my_shared_org_ids
{table}_{action} — trigger	workspaces_maintain_path · setu_cards_slug_write_once · setu_cards_slug_not_reserved · industries_validate_primitives · touch_updated_at

The executable surface, measured on Dev 2026-08-08 — not the "exactly six" this page first claimed:

Callable by	Functions	Why
anon + authenticated (5)	get_industries · get_platform_plans · get_public_setu_card · get_public_catalogue · is_slug_reserved	The public surface: onboarding pickers, the Setu Card, its catalogue, and slug availability during signup.
authenticated only (4)	get_my_features · my_workspace_ids · my_oversight_workspace_ids · my_shared_org_ids	The resolver plus the three RLS helpers. Each is self-scoped to auth.uid(), so calling one directly returns only the caller's own ids.
Nobody (7)	resolve_features · resolve_workspace_plan · the 5 trigger functions	Internal. resolve_features is reached through get_my_features, which supplies auth.uid().

Supabase's linter reports the first two groups as anon_security_definer_function_executable / authenticated_security_definer_function_executable WARNs. Those are expected and intended — they are the deliberate API surface of ADR-0014, and every one of them enforces its own filter (status = 'published', auth.uid() scoping). Do not "fix" them by revoking; that would take the public card offline.

⚠ get_my_features exists precisely because auth.uid() is NULL under the service-role client. A self-scoped-only resolver would answer "no" to every merchant from every Edge Function — an outage dressed as a gate. See ADR-0014.

RLS model — relationship-scoped, never role-scoped

The reference policy, showing all three directions at once:

create policy catalog_items_select on public.catalog_items
  for select to authenticated
  using (
    workspace_id = any (public.my_workspace_ids())                          -- OWN
    or workspace_id = any (public.my_oversight_workspace_ids())             -- OVERSIGHT (up)
    or owner_organization_id = any (public.my_shared_org_ids('catalogue'))  -- SHARING (down)
  );

The rules [ENFORCED]

• RLS on every user-facing table, and revoke all ... from public, anon, authenticated — by name, because Supabase's grant to those roles is explicit and REVOKE ALL FROM PUBLIC does not reach it (QRS-214).
• ⚠ No policy may grant access by ROLE alone. using (true) TO authenticated is banned. Category-3 consumers make authenticated the logged-in general public, and it will be the largest population on the platform by orders of magnitude. Nothing failed when that became true — which is exactly why it is a written rule with 29 negative pgTAP assertions behind it.
• Append-only tables carry no UPDATE or DELETE policy, and none may be added. audit_log is the case that matters: an editable audit log is worse than none, because it converts "we don't know" into "we have a record" while the record is unreliable.
• (select auth.uid()), never bare auth.uid() — the subquery form forces InitPlan caching so it evaluates once per statement rather than once per row (QRS-382). Every RLS predicate must also be index-backed.
• Oversight is gated on ownership_model = 'org_owned'. An employee's personal member_owned business and their consumer history must never be visible to their employer.

Migration rules [ENFORCED]

• 14-digit YYYYMMDDHHMMSS_name.sql (prefer npx supabase migration new; never the old NNN_ form — it collides on the version parser).
• Never edit a merged migration — add a new one.
• Expand-contract always: add-nullable → backfill → switch → drop. Never a breaking change in one step. Contraction is bounded by the oldest live app build (requires_min_app_build).
• Every table and function ships a COMMENT ON; policies and triggers too. Gated by check:sql.
• Capture cron (pg_cron) schedules and storage buckets as migrations, not just live DB state.
• ⚠ MCP apply_migration stamps its own timestamp, not the filename version. Call list_migrations immediately after the first write to catch orphan-version drift (QRS-267).

Testing

supabase/tests/database/v2_isolation_test.sql — 36 assertions, 29 of them negative. The fixture is a dealership tree (HQ → Thane/Andheri → two agents, all org_owned) plus one agent's personal member_owned boutique plus a consumer with zero memberships. It asserts, among others:

• a consumer reads zero rows of everything merchant-owned;
• an agent cannot read a sibling showroom's stock, nor their own parent;
• org stock is invisible until share_catalogue is on, and then still not writable;
• the regional manager cannot read the agent's personal business, or even see that it exists;
• oversight cannot UPDATE; cycles and self-parenting are refused; slug is write-once;
• is_slug_reserved('QRSETU') normalises case.

See Migrations guide · Supabase integration · ADR-0020 · ADR-0023.