Security

This document describes the current, actual trust boundaries of the ai-marketing platform: which Supabase client bypasses Row Level Security (RLS) and where, how the API authenticates requests, how admin access is enforced, which routes are public, and the tenancy model that RLS relies on.

It exists because docs/migration/phase-6-production-hardening.md — the only doc that read as a security description — was actually an unimplemented aspirational plan from before Phase 0 shipped, and had drifted so far from reality that it claimed RLS wasn't implemented at all. See Review history below.

For behavioural rules AI coding agents must follow when touching this surface (which Supabase client to pass into a service, when it's safe to move a query onto request.supabase), see CLAUDE.md → Code Conventions. This document is the narrative explanation of why those rules exist; CLAUDE.md is the enforced checklist.

Supabase client trust model

Three client constructors exist in api/src/utils/supabase.ts:

SUPABASE_KEY holds the service-role key in production, despite the name. That means createSupabaseClient() — not just createAdminSupabaseClient() — also bypasses RLS there. A route that reaches the database any way other than request.supabase has no tenant boundary except the code you write.

Two patterns coexist in api/src/services/ and api/src/modules/. User-facing services — ones a route can reach on behalf of a signed-in customer — take the client as a required first parameter (db: SupabaseClient) rather than holding a module-level singleton, so the call site has to say which client it wants and a reach for the admin client (bypassing RLS) is visible in code review. But roughly as many files instead hold a module-level createAdminSupabaseClient() singleton with no parameter at all — legitimate for code with no user-session path (the bot, cron jobs, webhook handlers), but also present in some user-facing services (e.g. content.service.ts's publishContent(), called from a user-initiated /publish request) where the RLS bypass is baked into the service rather than chosen visibly at the call site. When adding a new service, prefer the parameter form unless the module is genuinely session-less; when reading an existing one, check which pattern it actually uses rather than assuming the parameter form.

Before moving a query onto request.supabase, confirm a policy exists for the verb you need. RLS only helps where a policy covers that operation. Some tables are read-enforceable but not write-enforceable:

• integration_log has INSERT and SELECT policies but no UPDATE policy — publishContent's upsert must stay on the admin client.
• post_generation_plan is SELECT-only.

Check before converting a call site:

psql -c "select tablename, cmd, roles from pg_policies where schemaname='public'"

Request authentication (api/src/index.ts)

Every request except GET / and routes marked config: { skipAuth: true } goes through a single preHandler auth hook:

1. x-api-key header equal to SUPABASE_SERVICE_ROLE_KEY → treated as a trusted internal worker. request.user = { role: 'worker' }, request.supabase is the admin client. This is how the GoClaw bot and cron scheduler authenticate — they have no Supabase session to hold a user JWT.
2. Authorization: Bearer <token> where the token is a service_role JWT (detected via local signature verification against the JWT secret / JWKS) → same worker short-circuit as above.
3. Authorization: Bearer <token>, any other valid token → validated server-side via supabase.auth.getUser(token). On success, request.user is the Supabase user and request.supabase is createUserSupabaseClient(token) — the RLS-constrained client.
4. Anything else (missing header, malformed header, invalid/expired token) → 401.

After step 3, allowPendingUser() (api/src/utils/pending-approval.ts) enforces the signup approval gate: every new auth.users row gets a user_approval row via a DB trigger, and a real user session is rejected with 403 (PENDING_APPROVAL) unless that row's status = 'approved'. Worker tokens never reach this check. A missing user_approval row fails closed (treated as pending), since its absence means the trigger didn't run rather than that the user is exempt. The only route reachable while pending is POST /invites/* (accepting a team invite is itself part of the approval path).

Admin enforcement (api/src/utils/assert-admin.ts)

There is no RLS concept of "admin" baked into most policies — admin access is a code-level check, not a database-level one. assertAdmin(request, reply):

• Rejects worker tokens (x-api-key) outright — a service credential is never treated as an admin user.
• Looks up user_roles for a role = 'admin' row matching request.user.id, using the admin client (a real user session can't be trusted to self-report admin status, and no RLS policy needs to allow a non-admin to read this table).
• Fails closed: a lookup error is logged and still returns 403, indistinguishable from "not an admin" to the caller.

Some tables do carry RLS policies that grant admins cross-tenant reads directly (e.g. customer_customer, customer_posts have Admins can read all … policies keyed off the same user_roles check). [email protected] in seed data holds this role, which is why it's the wrong account to use for testing tenant isolation — use a plain non-admin user that owns nothing instead.

Tenancy model

Multi-tenancy is expressed through three tables and a chain of security definer SQL functions (front-end/supabase/migrations/20260804120051_brand_membership.sql):

customer_customer.user_id  ──┐
customer_member.user_id    ──┼── user_customer_ids(uuid) ── my_customer_ids() ── RLS policies
company_member.user_id     ──┘         (SECURITY DEFINER)      (wraps auth.uid())

• user_customer_ids(p_user_id uuid) returns every customer_customer.id a given user can reach: brands they created directly (customer_customer.user_id), brands they're an explicit member of (customer_member), and brands owned by a company they belong to (customer_customer.company_id → company_member).
• my_customer_ids() wraps it with auth.uid(), so it's the version almost every RLS policy actually calls: ... WHERE customer_id IN (SELECT public.my_customer_ids()).
• Both are SECURITY DEFINER, which is required, not an optimization — customer_customer's own SELECT policy calls my_customer_ids(), which reads customer_customer; without definer rights Postgres raises "infinite recursion detected in policy for relation".
• Postgres grants EXECUTE on new functions to PUBLIC by default. Left in place, that would let any signed-in user pass an arbitrary UUID into user_customer_ids(uuid) and enumerate another account's brands. The migration explicitly revokes the default grant and re-grants only: user_customer_ids(uuid) to service_role alone (the admin client can ask about any user); my_customer_ids() / my_company_ids() / can_manage_customer() to anon, authenticated, service_role (everyone gets the auth.uid()-scoped wrapper, which cannot be pointed at another account — anon is included because a couple of policies are granted to public and reach these functions with no auth.uid(), correctly returning nothing).
• can_manage_customer(p_customer_id) is the owner-level check (brand creator, or an owner-role member of the brand's company) used by policies that need write/delete rather than read scoping.

RLS coverage

As of this writing, every table in the public schema has RLS enabled — a live snapshot from the local instance:

$ psql -c "select count(distinct tablename) from pg_tables where schemaname='public' and rowsecurity=true;"
 67
$ psql -c "select count(*) from pg_tables where schemaname='public' and rowsecurity=false;"
 0
$ psql -c "select count(*) from pg_policies where schemaname='public';"
154

These numbers move as migrations land — re-run the queries above rather than trusting this snapshot for anything except "RLS is universally enabled, not absent."

A table's migration can be missing the grants its RLS policy assumes

Production carries a project-level ALTER DEFAULT PRIVILEGES ... GRANT ALL ON TABLES (a persistent, role-level Postgres setting) that lives only in front-end/supabase/migrations/_archive/, which is excluded from replay. A fresh local reset therefore doesn't inherit it, and a table's SELECT policy can silently become dead code — the policy exists but the role never had table-level SELECT to begin with, so every query returns nothing (or errors permission denied for table X) regardless of the policy. Fix in the table's own migration going forward: grant select on table ... to anon/authenticated, grant all ... to service_role, plus grant usage, select on sequence ... for identity columns.

Public (unauthenticated) routes

Routes opt into skipping auth explicitly via config: { skipAuth: true } on the route definition — an allowlist that lives next to the route, not a URL-shape match, so a new sibling route is authenticated by default. Every current one:

CORS (api/src/index.ts) rejects any browser Origin not in the production allowlist/pattern set with a 403; non-browser callers that send no Origin header (webhook senders, curl, internal services) are allowed through, which is expected — those routes authenticate via signature, not origin. In development all origins are allowed.

Known gaps

• docs/migration/phase-6-production-hardening.md is a pre-Phase-0 planning doc, not documentation of the current system — several things it lists as unbuilt shipped, just not the way it sketched (per-route rate limiting via @fastify/rate-limit instead of the Redis scheme in §6.1; Pino structured logging instead of the config in §6.3; Sentry is wired via Sentry.setupFastifyErrorHandler). Treat it as historical, not current.

Review history

2026-08-21 — Initial security documentation pass (#902, parent #760)

First recorded review. Findings:

• No SECURITY.md existed anywhere in the repo, and no prior review had been recorded.
• docs/migration/phase-6-production-hardening.md claimed RLS was not implemented (❌ Row Level Security (RLS) policies). In fact RLS is enabled on every table in public (67 tables, 154 policies at time of writing) via the my_customer_ids() tenancy chain described above.
• The only accurate, current security guidance in the repo lived in CLAUDE.md, undiscoverable to anyone not reading AI coding instructions.

This document was created to fix both, using CLAUDE.md's existing material as the starting point rather than re-deriving it, and verified against the running schema (pg_policies, pg_tables) and the actual auth hook / admin-check code rather than the planning doc.