Security model and practices

How Complied keeps tenants apart and protects client data, and the rules every contributor follows when adding tables, functions, edge functions, buckets or secrets. For engineers and reviewers. The permission catalog, presets and policy pattern live in permissions; this page is the checklist that sits on top of them.

The model in one paragraph

Complied is multi-tenant: Secure Environmental Group and Abated NYC (and any future firm) share one Supabase project, and each tenant's data is invisible to the others. Row-level security in Postgres is the boundary. Every other layer (edge-function checks, the UI's permission gates, the map service's overlay) is defence in depth or user experience; none replaces a policy. A staff user acts inside one tenant through named permission keys; a client-portal user acts inside one client; a platform operator can read broadly and can impersonate a tenant with a reason and an audit trail; collaborator tenants see only the projects shared with them and never financials.

Data classes

Class	Examples	Rule
Tenant-owned	projects, inspections, readings, documents, proposals, invoices, licences, rate cards	Policies require tenant_id = current_tenant_id() and a permission key. Collaborators get role-scoped branches on project data only. Financial tables never have a collaborator branch.
Client-scoped	What the portal shows	Portal users hold no permission keys. They are scoped by current_client_id() and only while their client_users row is active.
Public reference	buildings, units (select), public_events, taxonomy and catalog tables	Readable without a key; USING (true) is allowed only here. Written only by ingestion under the service role.
Personal	saved_views, notification_preferences, your own profiles row	Scoped to the user.
Platform-level	Platform documents, platform_operators, impersonation sessions, audit_log	Operators only, or the specific tenant a platform-to-tenant document targets.

Row-level security

• Enable RLS on every table you add to public. A new table without enable row level security is a defect.
• A tenant table's policies check a permission key, not just tenancy: tenant_id = (select public.current_tenant_id()) and (select public.has_permission('key')). Wrap every helper in (select ...) so Postgres evaluates it once per statement rather than per row. No policy reads a role's name; role names are labels.
• USING (true) is for public reference data only. CI warns on it in a new migration; a reviewer must justify each use. Tenant, user and financial data never qualify.
• A tenant table is not complete until its policy lint passes. db-tests/tests/permissions-matrix.test.ts fails a new tenant table whose policies do not check a key, and fails a catalog key that nothing enforces. Adding a key or table: permissions.
• The anon role holds nothing. Table and sequence privileges on public are revoked from anon, including through default privileges for future tables. Do not grant to anon to make something work.
• Views are security_invoker (for example map_event_source) so they inherit the caller's policies instead of the view owner's.
• Guard shared writes at the row. Anything writing citywide data that every tenant reads (the quarantine table, ingestion state) must not be reachable with a mere tenant permission. resolve_unlinked_event() and manual sync-orchestrator runs require a platform operator.
• Impersonation is audited. start_impersonation() and stop_impersonation() are SECURITY DEFINER, require a reason, are time-bounded, and write to the append-only audit_log the impersonated tenant can read. has_permission() honours the active session, so an impersonating operator sees exactly what the tenant sees.
• Deactivation revokes. Portal policies must include the active check (client_users.is_active); a deactivated portal login loses read access at once.
• Before dropping or reshaping a table, grep migrations for REFERENCES public.<table> and for triggers writing into it; a table with no UI reference can still have a live database-side writer.

The regression suite is db-tests/ (conventions): a two-tenant leakage matrix, one block per table or feature, plus targeted tests for security fixes.

SECURITY DEFINER functions

A SECURITY DEFINER function runs with its owner's rights, so it is a privilege boundary you write by hand. Every one in this repo follows these rules:

1. Pin the search path: set search_path = public (or = '' with fully qualified names). All existing definer functions do.
2. Revoke from anon and public in the same migration, then grant to the roles that need it. Executing to authenticated is a decision, not a default. CI blocks a new definer function without a paired REVOKE ... FROM anon/PUBLIC in the same change (scripts/ci/guardrails.sh).
3. Authorize inside the body when the function is callable by users: compare the target tenant with current_tenant_id() using is distinct from (never <>, which is NULL, not true, for a caller with no tenant) and admit platform operators explicitly. allocate_invoice_number() is the model.
4. Functions only edge functions call (for example enqueue_email()) are revoked from authenticated entirely.
5. Prefer SECURITY INVOKER when RLS already gives the right answer, as map_tenant_overlay() does.

Edge functions

Functions run on Deno with the service-role key available, which bypasses RLS. The rules exist so that power is only used after a caller has been checked.

• Authorize first. Browser-invoked functions call authorize(supabase, req, 'permission_key') from _shared/auth.ts. It accepts either the service-role key as bearer (server to server) or a real user's token, verifies the token, and evaluates has_permission() through a client scoped to the caller's own JWT, never through the service-role client (which has no auth.uid()). Platform operators pass any permission check.

• Probe ownership through the caller's client. Read the target row with createCallerClient(req) so RLS decides visibility, and answer 404, not 403, when it returns nothing, so existence is never leaked. Only then use the service-role client for the work.

• Money needs the owner tenant. A project's select policy also admits collaborators and client users, so an RLS probe is not an ownership check. Anything touching an owner's financials also calls callerIsOwnerTenant().

• Never trust a client-supplied tenant, path or decision. Derive the tenant from the caller or the row. Generators re-read project_order_decisions themselves rather than accepting a chosen path.

• Use typed keys. authorize() takes a PermissionKey; a typo fails the Deno type-check.

• verify_jwt = false needs an internal gate. The platform's JWT check is on by default. Turn it off only for server-to-server or pre-login callers, list the function in config.toml, and authenticate inside it:

  Function	Internal gate
  sync-orchestrator	Cron calls present CRON_SECRET and may only run citywide mode for one feed; manual calls need manage_tenant plus platform operator (or the service-role bearer)
  process-email-outbox, process-notification-digest	Service-role bearer required
  handle-email-suppression	Svix HMAC signature verified against RESEND_WEBHOOK_SECRET; fails closed (503) when the secret is unset
  handle-email-unsubscribe	Lookup of an unguessable per-email token; an unknown token is a 404
  request-password-reset	Unauthenticated by necessity (the caller cannot sign in); it mints the link itself and sends through the outbox

  CI reports (does not fail) any verify_jwt = false function that shows no recognised gate near the top of its handler; read that list on every change.

• CORS is an allowlist, not *. _shared/cors.ts echoes the origin only for the apex, tenant subdomains, and local dev hosts, plus origins named in CORS_ALLOWED_ORIGINS. It answers a preflight without running the handler and adds Vary: Origin. Wrap every browser-invoked handler in withCors; never use a *.pages.dev wildcard.

• Errors carry a cause, not internals. Return the real reason for expected failures (a blocked report lists its blockers); log details server-side; do not echo stack traces or secrets.

• Report generation sends client data to the renderer. The HTML carries site addresses, licence numbers and readings to the rendering service. Use a hosted endpoint only with that in mind, or self-host (PDF reports).

Storage

• Buckets are private by default. The only public bucket is report-fonts, which holds open-licence font files and nothing tenant-scoped; anything tenant-scoped goes in a private bucket (report-assets, branding, documents, field-photos).
• Set a size limit and an allowed MIME list on every bucket in its migration.
• documents object access delegates to the row. An object is readable when a documents row pointing at its storage_path is readable, and writable only while that row is a draft the caller may write; deletion also requires the document to be unshared. Do not write path-prefix policies that bypass the row rules.
• Sharing is per document and stamped at share time; the portal sees a document only when it is client-visible and shared with the viewer's own client. See documents.
• Service-role reads for generation are intentional but must follow an authorize() check as above.

Secrets

• Only publishable values reach the browser: VITE_* variables are the Supabase URL, the anon key and public tokens. The service-role key never appears in src/; src/data/supabaseClient.ts is the only client and takes the anon key.
• Server secrets live in the platform: edge-function secrets via supabase secrets set, CI secrets in repository settings, AWS access through a GitHub OIDC role rather than long-lived keys, the cron secret in Supabase Vault. Nothing secret is committed; .env* is gitignored. Deployment lists the names, never the values.
• The map service holds no service key and no JWT secret; it forwards the caller's token. Smart-search keys (MAP_SEARCH_API_KEY) stay on the service and are never VITE_-prefixed.
• Passwords never travel in URLs: use PGPASSWORD or a secret store for database logins (a URL shows up in ps).
• Seed and maintenance scripts that write real data print their target first and require an explicit SUPABASE_URL and service key; the seed scripts refuse a non-local target without SEED_CONFIRM=<project-ref>. Check the target before running anything that writes.
• A committed secret is compromised: rotate it, then remove it. The gitleaks CI job is advisory, so do not rely on it to catch one.

Portal isolation

A client-portal login is a separate identity class. It holds no permission keys, is scoped by current_client_id() while its client_users row is active, sees only documents shared with its own client (never financial class, never voided or draft), and sees an invoice only when it is sent and billed to that client. The building's current client is never consulted; sharing is decided when the document is shared. The portal has its own shell and routes (RequirePortalAuth) but those are UX; the policies enforce it. Portal-facing queries should be written in src/data/ so they can be reviewed against the policies.

Map service and the tenant line

Tenant-specific parts of a map query (scope, contested, buildingIds) are answered from a per-tenant overlay fetched from PostgREST with the caller's own token, keyed by the tenant id Postgres returns, with no shared result cache. Never accept a tenant id from the request body, fetch the overlay with a service key, or cache a result that already folded in tenant predicates. Details and tests: map.

Ingestion data

buildings and public_events hold citywide data that takes days to reload. Triggers block DELETE and TRUNCATE, and an event trigger blocks dropping the tables or their columns, unless a transaction sets complied.allow_ingestion_table_deletes = 'on'. Ingestion only upserts. Treat that override, supabase db reset, and any bulk delete as owner-approved actions (ingestion).

Gates that protect production

• Pre-tool hook (scripts/hooks/pre-tool-gate.mjs, wired in .claude/settings.json for the Bash tool): blocks Claude Code from writing to the production Supabase database (supabase db push, migration up|repair or db reset aimed at the linked or remote project, direct psql/pg_dump/pg_restore writes and non-GET curl against the production host). Reads are allowed. It also blocks git commit unless a type-check and the tests pass, and blocks a function deploy unless a production build passes. It governs agent-run commands, not a human's terminal.
• CI guardrails (deployment): type-check, build, tests, function type-check, and the SECURITY DEFINER revoke check are blocking.
• Owner-only production writes. Production migrations arrive by merge to main; hand-run production writes belong to the owner.

Dependencies

• Install with npm ci from the lockfile; review lockfile diffs in PRs and add packages deliberately. There is no automated update bot, so updates are a manual, scheduled task.
• The Deno version is pinned to the edge runtime's; the map service's native DuckDB binary ships in its npm package.
• Prefer the platform's own primitives (Supabase Auth, Storage, Vault) to hand-rolled equivalents.

Reporting a vulnerability

Do not open a public issue for a security problem. Tell the repository owner privately (a direct message or email) with what you found, how to reproduce it, and which tenant data is reachable. If you hold a credential that was exposed, rotate it first. Fixes that close a hole reachable by a portal user, an anonymous caller or another tenant ship ahead of feature work, as in 20260928100000_security_hotfixes.sql, with a regression test in db-tests/tests/security-hotfixes.test.ts.

Review checklist

• New table: RLS on, permission-key policies, (select ...) helpers, tenant_id and its integrity, policy lint passes, leakage test added.
• New SECURITY DEFINER function: pinned search_path, in-body authorization, REVOKE from anon/public.
• New edge function: authorize() with a typed key, caller-scoped ownership probe, withCors, no client-supplied tenant; if verify_jwt = false, an internal gate and a config.toml entry.
• New bucket: private unless it holds only public open-licence files, size and MIME limits, policies delegating to rows.
• No secret in code, docs, logs or commit messages; only VITE_* publishable values in the browser.
• Any change to portal-visible data reviewed against the portal rules above.