Database schema and invariants

All PolicyCodex tables live in the Postgres schema codex (never public) in the Supabase project gcfhhymqqlbmomkxrxuj. Direct reads via PostgREST require the headers Accept-Profile: codex (reads) / Content-Profile: codex (writes).

The four invariants

These are enforced by the database itself. Support procedures never work around them.

1. The audit log is append-only and hash-chained

codex.audit_log rejects UPDATE, DELETE and TRUNCATE via trigger. Each row’s entry_hash = sha256(prev_hash || canonical_json(entry)), computed by a before-insert trigger with a per-org advisory lock. Deleting an org would cascade into the log, so orgs are never truly deleted — they are retired (children removed, husk row + audit rows kept). Verify any export with pnpm verify:audit.

2. RFC versions are immutable

codex.rfc_versions rejects UPDATE. A new revision is a new row. Rows can be deleted (the demo reset uses this), but anything referencing them — statements.extracted_from_version — must be handled first. body_md stores the full composed document, YAML frontmatter block included; the editor re-parses frontmatter from it on save, so a version seeded without the --- block breaks saving.

3. Statement slugs are stable

codex.statements slugs never change once assigned. Re-extraction reuses slugs for materially unchanged statements and marks removed ones status='retired' rather than deleting them. Agents, CI findings and hrefs all key on the slug.

4. Every tenant table carries org_id + RLS

No tenant table ships without an org_id column and a row-level-security policy. The web app reads through RLS with the user’s Clerk JWT; Edge Functions and the worker use the service role but must filter by org explicitly.

Table inventory

Governance core

Table	What it is	Written by
`orgs`	Tenant registry (`id` UUID, `slug`, `clerk_org_id`)	`bootstrap_org` RPC only (sign-up); name synced by clerk-webhook
`members`	`org_id` + Clerk `user_id` (text) + role (`author` \| `domain_owner` \| `approver` \| `viewer`)	clerk-webhook (roles are Clerk custom org roles — the DB is the synced copy)
`user_profiles`	Clerk user id → email/display name directory	clerk-webhook
`domains`	Governance domains per org (`api-design`, …) with owner member	Web app RPCs
`rfcs`	RFC registry: number, slug, title, status (`draft`→`proposed`→`approved`→`enforced`→`deprecated`), workflow fields (reviewer/approver group, reviewed_by/at)	`save_revision` / `transition_rfc` / workflow RPCs
`rfc_versions`	Immutable revisions: frontmatter JSONB + full `body_md`	`save_revision`
`statements`	Machine-readable rules: stable slug, level (MUST/MUST NOT/SHOULD/SHOULD NOT/MAY), section, `sdlc_stage`, status (`active`/`retired`), `extracted_from_version`	`extract-statements` Edge Function (LLM, on approval)
`audit_log`	Append-only, hash-chained event trail; every lifecycle transition writes a row	Triggers/RPCs everywhere; never edited
`rfc_comments`	Review notes on RFCs	Web app
`role_groups` / `role_group_members`	Reviewer/approver groups for the RFC workflow	Web app (Settings → Groups)

Enforcement and integrations

Table	What it is	Written by
`api_keys`	Hashed org API keys (`pcx_…`, sha256 in `key_hash`, 12-char `key_prefix`)	Settings → API keys; `bootstrap_org` mints “GitHub Actions” key
`github_installations`	GitHub App installation ↔ org link. Unique per org AND per installation — one installation can never be claimed by two orgs	Connect flow (`connect_github_installation` RPC); uninstall webhook deletes
`service_checks`	One row per completed check or scan (status, blocking/warning counts, source: `github-app` \| `action` \| `full-scan`)	Worker
`pending_checks`	Queue: PR checks awaiting the cron sweep	Worker webhook
`pending_scans`	Queue: full-repo scans (has a cooldown per repo)	`/v1/scan`, `request_full_scan` RPC
`repo_coverage`	Repo inventory per installation (which repos exist, Action detected?)	`github-scan` Edge Function
`violations`	Findings: statement, repo, PR, severity, status (`open`/`resolved`/`superseded`)	Worker + Action REST
`llm_calls`	Token/latency log per LLM call (Reporting → LLM cost prices client-side)	Worker + functions
`org_llm_config`	Per-org LLM override: base_url, api_key, model — wins over env fallbacks	Settings → LLM
`org_subscriptions`	Clerk Billing plan + feature slugs; `has_entitlement(org, feature)` is the single server-side gate	clerk-webhook only

Growth

Table	What it is	Written by
`website_leads`	Leads: marketing-site contact form (RPC with honeypot + rate limit) and Cal.com bookings (`cal-webhook`)	Both sources above
`email_subscribers`	People in Resend sequences	`resend-webhook` (lazy upsert)
`email_sends`	Individual sequence emails with status, opens, clicks	`resend-webhook`
`email_events`	Raw event stream (no dedup by design — Resend retries and re-opens are legitimate)	`resend-webhook`

Identity in SQL

codex.current_user_id() returns the Clerk user id (user_…) from the JWT sub. auth.uid() is retired — it errors on Clerk tokens (Supabase Auth was fully removed 2026-08-12/13).
Plan gating: codex.has_entitlement(org_id, feature) reads org_subscriptions (features like github_scanning, llm_extraction). Feature slugs come from Clerk Billing at webhook time — there is deliberately no hardcoded plan→feature map.

Ad-hoc queries (support)

Use PostgREST with the secret key from .env.local, always filtering by an explicit org id (a bare limit 1 probe once picked the wrong org and caused a zero-row backfill):

curl -s "https://gcfhhymqqlbmomkxrxuj.supabase.co/rest/v1/rfcs?org_id=eq.<ORG_UUID>&select=slug,status" \
  -H "apikey: $SUPABASE_SECRET_KEY" -H "Authorization: Bearer $SUPABASE_SECRET_KEY" \
  -H "Accept-Profile: codex"

Migrations are additive only and applied with supabase db push --db-url "$SUPABASE_DB_URL" — there is no local Docker stack.

Start here

Architecture (C4)

Database

The four invariants

Table inventory

Governance core

Enforcement and integrations

Growth

Identity in SQL

Ad-hoc queries (support)

​The four invariants

​Table inventory

​Governance core

​Enforcement and integrations

​Growth

​Identity in SQL

​Ad-hoc queries (support)

The four invariants

Table inventory

Governance core

Enforcement and integrations

Growth

Identity in SQL

Ad-hoc queries (support)