Reference

Database

All application tables live in the public schema. RLS is enabled on every table and explicit GRANTs are issued in the same migration.

Schema overview

                    ┌──────────────┐
                    │   tenants    │
                    └──────┬───────┘
                           │ tenant_id
       ┌───────────────────┼──────────────────────┐
       │                   │                      │
       ▼                   ▼                      ▼
 ┌──────────────┐   ┌────────────────┐   ┌────────────────┐
 │  user_roles  │   │ access_grants  │   │ access_reviews │
 │  user_id ◄───┼───┤  user_id       │   │  reviewer_id   │
 │  role        │   │  role          │   │  grant_id      │
 └──────────────┘   │  expires_at    │   │  outcome       │
                    └───────┬────────┘   └────────┬───────┘
                            │                     │
                            └───────┬─────────────┘
                                    ▼
                          ┌────────────────────┐
                          │ access_audit_log   │
                          │  actor_id          │
                          │  action            │
                          │  target / details  │
                          │  created_at        │
                          └────────────────────┘

                    ┌────────────────────┐    ┌────────────────────┐
                    │ sec_login_attempts │    │   gdpr_requests    │
                    │  ip_address        │    │  subject_email     │
                    │  email             │    │  status            │
                    │  outcome           │    │  due_at            │
                    │  created_at        │    │  closed_at         │
                    └────────────────────┘    └────────────────────┘

                    auth.users (Supabase) ──── referenced by user_id (cascade delete)

Table definitions

Table	Purpose	Notable columns	RLS scope
`tenants`	Multi-tenant root	`id`, `name`, `created_at`	Member of tenant
`user_roles`	Role assignments	`user_id`, `role` (app_role enum)	Read via `has_role`
`access_grants`	Time-bound entitlements	`user_id`, `role`, `expires_at`, `granted_by`	Tenant member; mutations gated by owner/admin
`access_reviews`	Periodic attestations	`cycle_id`, `grant_id`, `reviewer_id`, `outcome`	Reviewer role
`access_audit_log`	Append-only audit trail	`actor_id`, `action`, `target`, `details` (jsonb), `created_at`	`owner` / `auditor` read; service-role write
`gdpr_requests`	DSAR workflow	`subject_email`, `kind`, `status`, `due_at`	Subject + DPO
`sec_login_attempts`	Brute-force tracking	`ip_address`, `email`, `outcome`, `created_at`	service-role only

Indexes

Table	Index	Why
`user_roles`	`(user_id, role)` UNIQUE	Prevent duplicate grants; fast `has_role`
`access_grants`	`(user_id, expires_at)`	Active-grant lookups
`access_audit_log`	`(created_at desc)`	Recent activity feed
`access_audit_log`	`(actor_id, created_at desc)`	Per-actor audit
`sec_login_attempts`	`(ip_address, created_at desc)`	Rate-limit lookup

Constraints

All FK references to auth.users use ON DELETE CASCADE.
app_role enum is the canonical role list — extend via migration, never via string literals.
created_at defaults to now() on every table.
access_audit_log.details is jsonb for forward-compatibility; readers must tolerate unknown keys.

Migration history

Migrations are versioned files in supabase/migrations/. Notable entries:

Migration	Purpose
Initial schema	Tenants, profiles, base RBAC
Access governance v1	`access_grants`, `access_reviews`, `access_audit_log`
GDPR workflow	`gdpr_requests`
Security hardening	`sec_login_attempts` + service-role-only policies
MFA prep	Indices for AAL/factor lookups

Update this section in the same migration that adds or changes schema.

Schemas to never touch

auth, storage, realtime, supabase_functions, vault. These are provider-managed; do not add triggers or modify any of their objects.

Generated types

src/integrations/supabase/types.ts is auto-generated from the live schema by the platform integration. Never hand-edit it. After any schema change, regenerate.