Next.js and Supabase

This pack describes a Next.js App Router app in web/ (TypeScript, npm) and a Supabase back end (Postgres, row level security, PostgREST, Edge Functions, Auth and Storage). The database is built from hand-authored migration files, applied and proved through the Supabase MCP server, and proved again offline on PGlite before anything touches the project.

The pack is opt-in. It applies only when PROJECT.md § Stack pack names stack-nextjs-supabase, and the stack-dependent roles read it by path at step 1: .claude/skills/stack-nextjs-supabase/SKILL.md. It is never preloaded, so a project on another stack never carries it.

Two third-party skills carry the general craft and are worth reading beside this one, if installed: supabase for the platform's products and client libraries, and supabase-postgres-best-practices for schema design, indexes, locks and query plans. docs/SKILLS.md says where to get them. Both also teach the Supabase CLI and a declarative supabase/schemas/ workflow, which this pack does not use. Where either disagrees with this file, this file wins, because the toolchain below is the only one the team can run and evidence.

When to use this pack

Read the sections your role needs, at the step shown. Every other file cites these section names, so they do not change.

Toolchain

This section is the canonical statement of how the team does database work on this stack. Every other file cites it rather than restating it.

Present on the machine: git, node, npm, npx, and the Supabase MCP server (supabase in .mcp.json, scoped to one project). PROJECT.md § Toolchain records the versions. Nothing else may be assumed. The Playwright MCP server (playwright in .mcp.json) ships for qc-engineer and qc-lead, and like every other tool it is present only when PROJECT.md § Toolchain lists it.

The team never uses Docker, the Supabase CLI, Deno, the Vercel CLI, pnpm, psql, jq or python on this stack. None of them is a required step, a gate criterion, an evidence source or an allowed permission. A tool that is missing is reported as blocked, never faked.

The MCP workflow

Database work goes through the Supabase MCP. The tools named here are the server's own.

execute_sql connects as a privileged role that owns the tables, and a table owner skips row level security. That is why every role check switches role inside the transaction, and why a query plan read without the switch says nothing about the policy.

Evidence

Every call above that produces output is saved under the run's evidence/, named so a reader knows where it ran. These names replace the generic ones in team-architecture; anything this table does not name keeps the name the brief gives it.

A key, a token or a password never appears in evidence. Redact the value and keep the name.

An empty result is not a pass. A pgTAP file's evidence shows the plan line, every ok and not ok line and the final count. If execute_sql returns less than that, record exactly what came back, treat the file as not run, and raise a machinery_findings entry naming this pack, because the route to the project proof is then the thing that is broken.

When the MCP does not answer

If the Supabase MCP is not connected (its tools are missing, or a call returns an auth error), nothing is faked. Run the offline proof, set status to blocked with the reason supabase MCP not authorised in the handoff, and the orchestrator escalates to the Product Lead, who authorises the server with /mcp. Generated types are never written by hand and a key is never invented while the server is down.

What the team does not do, and who does

Kickoff setup

The orchestrator runs this once, at kickoff, when the Product Lead confirms this pack in PROJECT.md § Stack pack. Ask before installing anything.

1. The MCP server. Add the supabase entry from .claude/skills/stack-nextjs-supabase/templates/mcp.json to mcpServers in the root .mcp.json, beside the team's playwright entry where the project kept it. Create the file from the template only when there is none. Never replace the file, and never change or remove another server's entry. Replace <project-ref> with the project ref the Product Lead gives you: the short id in the project's dashboard URL. The file holds no key and no token; the server authenticates through the browser. Tell the Product Lead to authorise it with /mcp, and that it loads in the next session. The template turns on the docs, account, database, debugging, development, functions and branching tool groups, and the project ref scopes every call to one project.

2. The offline proof. It needs PGlite in the project root, the folder the proof runs from. If the root has no package.json, create one first, so npm does not install into a parent folder:

   npm init -y
   npm pkg set private=true --json
   npm i -D @electric-sql/pglite

   Run this once. The root package.json and package-lock.json are committed, and npm install at the root restores PGlite on a fresh clone.

3. Ignore rules. The root .gitignore covers node_modules/, .env and .env.*, with !.env.example so the example file is still committed.

4. The profile. Offer these as the defaults for the PROJECT.md sections, for the Product Lead to confirm or change:

   Section	Default
   § Stack	Front end: Next.js, App Router, TypeScript, in web/. Back end: Supabase (PostgREST, database functions, Edge Functions, Auth). Database: Supabase Postgres with row level security on every table. Hosting: not chosen yet
   § Toolchain	Present: git, node, npm, npx, the Supabase MCP server scoped to one project, and the Playwright MCP server for qc-engineer and qc-lead. Never assumed: Docker, the Supabase CLI, Deno, the Vercel CLI, pnpm, psql, jq, python
   § Release	Target: deferred: no target chosen

   And for § Commands:

   Purpose	Command
   install	npm install && cd web && npm install
   dev	cd web && npm run dev
   build	cd web && npm run build
   lint	cd web && npm run lint
   typecheck	cd web && npm run typecheck
   test	cd web && npm test
   e2e	cd web && npm run e2e
   db test	node .claude/skills/stack-nextjs-supabase/scripts/db-test.mjs

   Until web/ exists, the install command runs at the root only.

Client configuration comes later, once the server answers and web/ exists, and it is frontend-engineer's job, because frontend-engineer owns web/: get_project_url and get_publishable_keys written to web/.env.local, and the variable names with empty values in the committed web/.env.example. See The app in web/.

Pre-flight at run open

The orchestrator runs these checks after the run opened ledger line and before the first dispatch, and writes each one, with its command or call and its answer or error, to evidence/toolchain-preflight.log.

That one list_tables call is the orchestrator's only MCP call. It never applies, queries or changes anything through the server.

Entries that need the server, for the list in orchestrator/plan.md: backend-engineer, frontend-engineer (types and client keys), code-analyst and security-analyst (read-only checks and probes), engineering-lead, qc-engineer, qc-lead, and release-engineer when the run ships. bug-historian, tech-architect, ux-designer, ux-auditor, ux-writer, peer-reviewer and code-steward do not need it.

Repository layout

web/                                     the Next.js App Router app, TypeScript, npm;
                                         only frontend-engineer writes here
web/src/app/                             routes
web/src/lib/database.types.ts            generated by generate_typescript_types, never edited
web/.env.local                           project URL and publishable key, gitignored
web/.env.example                         the same variable names, empty values, committed
supabase/migrations/<yyyymmddhhmmss>_<slug>.sql
                                         the database source of record: hand-authored,
                                         forward-only, one concern per file
supabase/tests/*.test.sql                pgTAP, run offline and on the project
supabase/seed.sql                        seed rows, applied offline only
supabase/functions/<name>/index.ts       Edge Functions, one folder each
content/strings/<locale>.json            the shipped string catalogue, written by ux-writer,
                                         one file per locale in PROJECT.md § Locales
package.json, package-lock.json          the root: private, holds @electric-sql/pglite
.mcp.json                                the MCP servers: the team's playwright, and supabase,
                                         added from templates/mcp.json

Migrations are the source of record, and they are hand-authored. There is no declarative supabase/schemas/ workflow: generating a migration from it needs supabase db diff, which needs the Supabase CLI and Docker, and neither is used. Schema files, if any exist, are not a source of record. Nothing applies them and nothing tests them, so they are never read as the current schema.

The app in web/

frontend-engineer owns this section and everything under web/.

The app never holds a rule. It may hide a control a reader cannot use; the database decides whether they can reach the data. See RLS first.

The offline proof

node .claude/skills/stack-nextjs-supabase/scripts/db-test.mjs

Run it from the project root. PGlite is real Postgres compiled to WebAssembly, so the proof needs nothing but node and the one dev dependency from Kickoff setup, step 2. In order, it:

1. Starts PGlite in memory. Nothing is written to disk.
2. Applies a Supabase-shaped bootstrap: the anon, authenticated and service_role roles, Supabase's default grants on public, the extensions schema with pgcrypto and uuid-ossp, and an auth schema with auth.users, auth.uid(), auth.jwt(), auth.role() and auth.email() reading request.jwt.claims.
3. Applies supabase/migrations/*.sql in filename order, one statement at a time, so a failure names the file and the line. A migration's create extension pgtap is skipped and reported, because a shim stands in for pgTAP offline.
4. Applies supabase/seed.sql, if there is one.
5. Installs the pgTAP-compatible shim from this pack's scripts/db-test-shim.sql, and runs every supabase/tests/*.test.sql, each inside a transaction that is rolled back, printing TAP.

Exit code 0 when every test passes, 1 on any failure or SQL error (including PGlite not being installed), 2 when there are no migrations to prove.

Save the run with its first line intact, so the file opens with the PGlite label:

node .claude/skills/stack-nextjs-supabase/scripts/db-test.mjs > .devteam/runs/<run-id>/evidence/backend/db-test-pglite.tap

The shim implements plan, no_plan, finish, ok, is, isnt, pass, fail, diag, is_empty, isnt_empty, throws_ok (every text and integer form), lives_ok, results_eq, set_eq and bag_eq. Any other pgTAP function fails loudly offline, and the output names it. Extend the shim, or prove that test file on the project only and say so in the handoff.

What the offline proof cannot prove

It proves that the migrations, the seed and the tests hold on real Postgres. It is not the project, so it never replaces pgTAP through the MCP:

• No PostgREST, so no status code, header or error mapping is proved.
• No real pgTAP. The shim implements part of pgTAP's interface and compares rows by their text form, so a column type mismatch that pgTAP would report passes offline.
• No Supabase platform: no Auth service, Storage, Realtime or Edge Functions, no platform extensions such as pg_net, pg_cron or pg_graphql, and no advisors.
• Migrations run as a superuser offline, which the project's connecting role is not, so a privilege error can pass offline and fail on the project.
• PGlite may run a newer Postgres than the project. Check the project's with select version() through execute_sql before relying on a recent feature.
• It applies a migration one statement at a time. apply_migration sends the whole file, so a statement that cannot run inside a transaction block, such as create index concurrently, can pass offline and fail on the project.

Migrations

Where the team applies migrations: backend-engineer applies each one to the project while it builds, so the tests run there. At release, release-engineer proves the project's history matches the folder exactly, applies any file not yet applied, and proves it again.

RLS first

The product's invariants (PROJECT.md § Product invariants) are enforced in the database, because only the database holds against every caller: a leaked publishable key, a direct connection, a mistaken client query, an Edge Function nobody reviewed, and a PostgREST call the front end was never meant to make.

One enforcement point per invariant. team-architecture calls two enforcement points that can disagree a defect, and that rule holds here.

The mechanism for each kind of rule in team-architecture, on this stack:

The examples below use a small invoicing product: accounts, their members, projects, invoices and time entries. Each pattern ran green under the offline proof, with tests for the positive and the negative case, before it was written here.

Row visibility by membership

A member reads and writes only the rows of accounts they belong to. The membership lookup lives in a security-definer function in a schema PostgREST does not expose, so a policy can call it without recursing into the membership table's own policy, and it returns a set the planner evaluates once per statement.

-- supabase/migrations/<yyyymmddhhmmss>_account_membership.sql (extract)
create type public.member_role as enum ('owner', 'member');

create table public.account_members (
  account_id uuid not null references public.accounts (id) on delete cascade,
  user_id    uuid not null references auth.users (id) on delete cascade,
  role       public.member_role not null,
  primary key (account_id, user_id)
);

create index account_members_user_id_idx on public.account_members (user_id);

create schema private;
grant usage on schema private to authenticated;

create function private.my_account_ids()
returns setof uuid
language sql
stable
security definer
set search_path = ''
as $$
  select m.account_id
    from public.account_members m
   where m.user_id = (select auth.uid());
$$;

revoke all on function private.my_account_ids() from public;
grant execute on function private.my_account_ids() to authenticated;

A sibling, private.my_owner_account_ids(), adds and m.role = 'owner'. Then every tenant table takes all four command policies in the file that creates it:

create index projects_account_id_idx on public.projects (account_id);

alter table public.projects enable row level security;
revoke all on public.projects from anon, authenticated;
grant select, insert, update, delete on public.projects to authenticated;

create policy projects_select on public.projects
  for select to authenticated
  using (account_id in (select private.my_account_ids()));

create policy projects_insert on public.projects
  for insert to authenticated
  with check (account_id in (select private.my_account_ids()));

create policy projects_update on public.projects
  for update to authenticated
  using (account_id in (select private.my_account_ids()))
  with check (account_id in (select private.my_account_ids()));

create policy projects_delete on public.projects
  for delete to authenticated
  using (account_id in (select private.my_owner_account_ids()));

The update policy carries both using and with check, so a member cannot move a project into an account they do not belong to. The grants are explicit because Supabase's default privileges hand anon and authenticated every privilege on a new table, truncate, references and trigger included, and row level security governs none of those three.

Field exposure

An invoice's internal note never reaches a client. Revoke the table, then grant select on the columns that may cross the boundary. The note has no grant, so there is no path to it, and the row policy still decides which rows.

revoke all on public.invoices from anon, authenticated;
grant select (id, account_id, number, status, total_minor, currency)
  on public.invoices to authenticated;

create policy invoices_select on public.invoices
  for select to authenticated
  using (account_id in (select private.my_owner_account_ids()));

-- writes go through definer functions; the policies say so
create policy invoices_insert on public.invoices
  for insert to authenticated with check (false);
create policy invoices_update on public.invoices
  for update to authenticated using (false) with check (false);
create policy invoices_delete on public.invoices
  for delete to authenticated using (false);

create view public.invoice_summaries
with (security_invoker = on) as
  select id, account_id, number, status, total_minor, currency
    from public.invoices;

grant select on public.invoice_summaries to authenticated;

A view with security_invoker = on checks the caller's own privileges on the table beneath it, and the caller needs a grant on every column the view reads. Over a table whose grants are fully revoked it refuses every caller with 42501, so field exposure is done with column grants, and the view only shapes the read. That works because the view reads nothing the caller cannot. When the view has to transform a column the caller must never read, such as redacting free text, column grants cannot help: a grant on that column lets the caller read it from the table directly. Route clients through a security-definer function instead, and give no client grant on the view. A view without security_invoker runs as its owner and skips every policy, which is a trap, not a fix.

An aggregate that never reports a small group

Some invariants are facts about a set, not a row: a report never shows a group smaller than a minimum size, so no one person's figures can be read off an average. A row policy cannot express that. Revoke the base table entirely, and expose the report only through a security-definer function that applies the rule before it returns anything.

-- supabase/migrations/<yyyymmddhhmmss>_team_hours_report.sql (extract)
revoke all on public.time_entries from anon, authenticated;

create function public.team_hours_report(p_account uuid, p_from date, p_to date)
returns table (project_id uuid, avg_hours numeric, members integer)
language plpgsql
stable
security definer
set search_path = ''
as $$
declare
  c_min_group constant integer := 10;   -- the minimum, from PROJECT.md § Product invariants
  v_members integer;
begin
  if p_account not in (select private.my_owner_account_ids()) then
    raise exception 'not_found' using errcode = 'P0001';
  end if;

  select count(distinct t.user_id) into v_members
    from public.time_entries t
   where t.account_id = p_account
     and t.worked_on between p_from and p_to;

  if v_members < c_min_group then
    raise exception 'below_minimum_group' using errcode = 'P0001';
  end if;

  return query
    select t.project_id, round(avg(t.hours), 2), count(distinct t.user_id)::integer
      from public.time_entries t
     where t.account_id = p_account
       and t.worked_on between p_from and p_to
     group by t.project_id
    having count(distinct t.user_id) >= c_min_group;
end;
$$;

revoke all on function public.team_hours_report(uuid, date, date) from public, anon;
grant execute on function public.team_hours_report(uuid, date, date) to authenticated;

Four things are not negotiable here:

1. set search_path = '' on every security-definer function, with every reference schema-qualified. Without it a caller can create a shadowing object in a schema they control and run their own code as the definer.
2. The refusal names the invariant, never the input. below_minimum_group, never the date range or the filter that tripped it, because naming the input lets a caller narrow one query at a time towards exactly what the rule protects. The same holds for not_found: an account that exists and one that does not answer alike.
3. Every group returned is at or above the minimum (the having), and so is the whole set the filters select. Test the difference attack too: two permitted queries whose results subtract to a group below the minimum.
4. The base table is revoked. If authenticated can read time_entries directly, the function is decoration.

Guarded transitions

A rule about what a row may become is a trigger, not a policy, because a policy decides whether a row is visible or writable and cannot say what a valid transition is. The trigger holds whatever wrote the row, the table owner included.

create function public.guard_invoice_transition()
returns trigger
language plpgsql
set search_path = ''
as $$
begin
  if old.status <> 'draft'
     and (new.total_minor, new.currency, new.number)
         is distinct from (old.total_minor, old.currency, old.number) then
    raise exception 'invoice_issued_is_immutable' using errcode = 'P0001';
  end if;

  if (old.status, new.status) not in (
       ('draft', 'draft'), ('draft', 'issued'), ('draft', 'void'),
       ('issued', 'issued'), ('issued', 'paid'), ('issued', 'void'),
       ('paid', 'paid'), ('void', 'void')) then
    raise exception 'invoice_transition_not_allowed' using errcode = 'P0001';
  end if;

  return new;
end;
$$;

create trigger invoice_transition_guard
  before update on public.invoices
  for each row execute function public.guard_invoice_transition();

revoke execute on function public.guard_invoice_transition() from public, anon, authenticated;

The transition function that moves an invoice to issued or paid is a security-definer function that reads the actor from auth.uid(), never from an argument, locks the row with select ... for update, and writes its audit row in the same transaction.

Separation

A class of record that must never appear in another workflow lives in its own schema with its own grant, never behind a flag on a shared table, because a flag can be forgotten in a where clause and a missing grant cannot. PostgREST exposes only the schemas the project's settings list, so a new schema stays unreachable over the API until the Product Lead exposes it, which is a decision for the handoff.

Auth

• Authorisation on the server reads the verified user: supabase.auth.getUser() or supabase.auth.getClaims(). getSession() reads the cookie without verifying it, so it never decides access.
• Membership and role come from a table keyed by auth.uid(), such as account_members. Never from user_metadata (raw_user_meta_data), which the signed-in user can change about themselves. A policy that reads auth.jwt() -> 'user_metadata' is a privilege escalation. app_metadata is writable only with a privileged key, and still second best to a table the policies can index.
• Sign-out clears the session cookies and any local state, so the next person on a shared device sees nothing.
• The service_role key never leaves a server context. It bypasses every policy; in a browser bundle it is a full breach.

RLS traps

These are the ones that bite. code-analyst and security-analyst check every one, and a hit is a security finding, not a style point.

get_advisors for type security catches several of these on the project. It does not replace the table: a clean advisor run with a bare auth.uid() in a policy is still a finding.

PostgREST conventions

The API contract lives in team-architecture and docs/architecture/contracts/. PostgREST is the transport.

Security probes

security-analyst runs these, and qc-engineer and qc-lead reuse the probe form for invariant cases. Every tool here reads or probes; nothing is applied, deployed or changed.

Keys

The privileged key names for the history pattern are service_role, SERVICE_ROLE, SUPABASE_SERVICE_ROLE_KEY, sb_secret_ and SUPABASE_JWT_SECRET. A legacy key is a JWT (it starts eyJ), so decode the payload of any committed one and treat "role":"service_role" as a leak.

# a privileged key name or value in anything the browser downloads: must print nothing
git grep -nE 'service_role|SERVICE_ROLE|sb_secret_|SUPABASE_JWT_SECRET' -- web/

# a secret key value anywhere in the tree, then in every commit on every branch
git grep -nE 'sb_secret_[A-Za-z0-9_-]{16,}'
git log -p --all | grep -nE 'sb_secret_[A-Za-z0-9_-]{16,}|SUPABASE_SERVICE_ROLE_KEY\s*=\s*\S+'

# a browser variable named like a secret: must print nothing
git grep -nE 'NEXT_PUBLIC_[A-Z0-9_]*(SECRET|SERVICE|PRIVATE)'

A hit in web/ stops the gate and is a rotation event: the fix is a new key, not a deleted line.

Catalogue queries

Run each in its own execute_sql call, add every schema the migrations create to the list, and save the call and its rows as evidence/security/catalogue-<query>.json. Every query here ran under the offline proof except the Storage one, which needs the platform.

-- tables with row level security off in a project schema: must return nothing
select n.nspname as schema_name, c.relname as table_name
  from pg_class c
  join pg_namespace n on n.oid = c.relnamespace
 where c.relkind in ('r', 'p')
   and n.nspname in ('public', 'private')
   and not c.relrowsecurity;

-- row level security on with no policy: must return nothing
select c.oid::regclass as table_name
  from pg_class c
  join pg_namespace n on n.oid = c.relnamespace
 where c.relkind in ('r', 'p')
   and n.nspname in ('public', 'private')
   and c.relrowsecurity
   and not exists (select 1 from pg_policy p where p.polrelid = c.oid);

-- what the client roles hold on each table: read against the architecture
select table_schema, table_name, grantee,
       string_agg(privilege_type, ', ' order by privilege_type) as privileges
  from information_schema.role_table_grants
 where grantee in ('anon', 'authenticated')
   and table_schema in ('public', 'private')
 group by table_schema, table_name, grantee
 order by table_schema, table_name, grantee;

-- security definer functions without a pinned search path: must return nothing
select p.oid::regprocedure as function_name
  from pg_proc p
  join pg_namespace n on n.oid = p.pronamespace
 where p.prosecdef
   and n.nspname in ('public', 'private')
   and not exists (
     select 1 from unnest(coalesce(p.proconfig, '{}')) setting
      where setting like 'search_path=%');

-- views that run as their owner: must return nothing
select c.oid::regclass as view_name
  from pg_class c
  join pg_namespace n on n.oid = c.relnamespace
 where c.relkind = 'v'
   and n.nspname in ('public', 'private')
   and not exists (
     select 1 from unnest(coalesce(c.reloptions, '{}')) opt
      where opt ~* '^security_invoker=(on|true|yes|1)$');

-- functions anon may execute: each one must be meant for an anonymous caller
select p.oid::regprocedure as function_name
  from pg_proc p
  join pg_namespace n on n.oid = p.pronamespace
 where n.nspname in ('public', 'private')
   and has_function_privilege('anon', p.oid, 'execute');

-- every policy, for the review against the permission matrix
select schemaname, tablename, policyname, cmd, roles, qual, with_check
  from pg_policies
 where schemaname in ('public', 'private')
 order by schemaname, tablename, cmd;

-- Storage buckets: a public one must hold only public files
select id, public from storage.buckets;

Role-switched probes

The catalogue shows the setting; a probe as the caller shows the effect. One probe per execute_sql call, because the first error aborts the transaction, and every probe inside begin; ... rollback; so nothing it does persists.

begin;
set local role anon;
select count(*) from public.invoices;   -- expected: 42501, no grant
rollback;

begin;
set local role authenticated;
set local request.jwt.claims = '{"sub":"<member of account A>","role":"authenticated"}';
select count(*) from public.projects
 where account_id = '<account B>';      -- expected: 0
rollback;

begin;
set local role authenticated;
set local request.jwt.claims = '{"sub":"<member of account A>","role":"authenticated"}';
update public.projects set name = 'probe'
 where account_id = '<account B>'
returning id;                            -- expected: no rows
rollback;

Probe every command, not only select, and every role in the permission matrix.

The same cases from outside

Then send the same case to the REST URL with the publishable key, as a real client would, with curl or a node fetch script. The base comes from get_project_url; the keys and a test user's access token come from the environment, never from the repository or the evidence.

# anonymous: expected 401 with code 42501, never a 200
curl -s -w '\n%{http_code}\n' "$SUPABASE_URL/rest/v1/invoices?select=id" \
  -H "apikey: $SUPABASE_PUBLISHABLE_KEY"

# a signed-in member of account A asking for account B: expected 200 and []
curl -s -w '\n%{http_code}\n' "$SUPABASE_URL/rest/v1/projects?select=id&account_id=eq.<account B>" \
  -H "apikey: $SUPABASE_PUBLISHABLE_KEY" \
  -H "Authorization: Bearer $MEMBER_A_ACCESS_TOKEN"

A test user's access token comes from signing a test account in through $SUPABASE_URL/auth/v1/token?grant_type=password. The Product Lead sets up the test accounts; their passwords live in the environment of the machine that runs the probe.

Query plans

A plan read as the connecting role skips every policy, so it says nothing about the policy's cost. Read it as the role the policy applies to, inside a transaction that rolls back, because EXPLAIN ANALYZE runs the statement:

begin;
set local role authenticated;
set local request.jwt.claims = '{"sub":"<member of account A>","role":"authenticated"}';
explain (analyze, buffers)
select id, name from public.projects
 order by created_at desc, id desc
 limit 50;
rollback;

Save it as evidence/backend/explain-<query>.txt, labelled with where it ran and as which role. A sequential scan on a large table under a policy, or a policy function called once per row, is a finding. The offline proof can produce a plan too, labelled PGlite, but only at the row counts the seed provides.

pgTAP

The invariant suite reads as a specification, and it is the product's claim in executable form. Every change that touches a policy, a grant, a view or a security-definer function touches this suite, or it is a finding, because the surface moved and nobody re-proved the claim.

Every test file opens with begin; and ends with rollback;, and inserts any rows it needs inside that transaction, so the same file proves the same thing in both places and leaves nothing behind.

The suites live at fixed paths, so every role finds them: supabase/tests/invariants.test.sql for the rules in PROJECT.md § Product invariants, supabase/tests/rls.test.sql for every table and every role, and supabase/tests/transitions.test.sql for every legal and illegal transition. More files may sit beside them.

execute_sql returns one result set, and for a query of many statements that is, in practice, the last one that returned rows. A plain pgTAP file therefore sends back a single line, and the plan and every earlier result are lost. Write each test file in the collector form, which gathers every TAP line into a temporary table and returns them all from the final select. It runs unchanged offline, where the proof reads the same rows, and it still works if a server returns every result set.

-- supabase/tests/invariants.test.sql
begin;

-- Collects every TAP line, so the last statement before rollback returns the whole run.
create temp table tap_out (n integer generated always as identity, line text) on commit drop;
grant insert, select on tap_out to public;

insert into tap_out (line) select plan(4);

-- I1: a member reads only the rows of accounts they belong to
set local role authenticated;
set local request.jwt.claims = '{"sub":"<member of account A>","role":"authenticated"}';

insert into tap_out (line) select results_eq(
  $$ select name from public.accounts $$,
  array['Account A'],
  'member_of_other_account_reads_nothing'
);

insert into tap_out (line) select throws_ok(
  $$ select internal_note from public.invoices $$,
  '42501',
  null,
  'the internal note has no grant, so it never crosses the boundary'
);

insert into tap_out (line) select throws_ok(
  $$ select * from public.team_hours_report('<account B>', '2026-09-01', '2026-09-30') $$,
  'P0001',
  'not_found',
  'another account''s report is refused as not found'
);

reset role;

-- the guarded transition holds whatever wrote the row
insert into tap_out (line) select throws_ok(
  $$ update public.invoices set total_minor = 1 where status = 'issued' $$,
  'P0001',
  'invoice_issued_is_immutable',
  'issued_invoice_refuses_edit'
);

insert into tap_out (line) select * from finish();
select line from tap_out order by n;
rollback;

Role checks switch role inside the transaction with set local, and switch back with reset role before anything that must run as the owner. The grant on tap_out is what lets the switched role write its results.

Name each test after the rule it proves, as the architecture names it. Cover every table and every role, positive and negative, including a direct read of each revoked base table that must be refused with 42501.

Edge Functions

Edge Functions carry what the database should not do: outbound email and messages, provider webhooks, payment calls, and anything that reads a secret.

• Each lives at supabase/functions/<name>/index.ts. Deploy it with deploy_edge_function, with the files exactly as they are in the repository, after the schema it relies on.
• Prove it by calling <project url>/functions/v1/<name> with curl or a node fetch script, with and without a valid session, and read get_logs for the edge-function service straight after, because it returns recent entries only. Save both.
• JWT verification stays on. A provider webhook is the exception: it turns verification off for that function only and verifies the provider's signature instead.
• The function creates its Supabase client with the caller's Authorization header, so row level security applies to what it reads and writes. A privileged client is used only where the ADR says the function must bypass a policy, and then it takes the caller's identity from the verified JWT, never from the request body.
• Every outbound side effect carries an idempotency key under a unique constraint in the database. An idempotency proof sends the same request twice to the deployed function and counts the rows through execute_sql.
• No personal data, token or secret in a log line, no personal data in a URL, and a dead-letter row rather than a silent drop.
• Imports are pinned to exact versions (npm: or jsr: specifiers with a version), because they resolve at deploy time.
• Type checking needs Deno, which is not used. engineering-lead records it as deferred: no local Deno, never as clean. The deploy succeeding and the calls answering are the proof.
• Every secret the function reads is named in decisions_for_product_lead, never valued. release-engineer verifies it by calling the function and reading the logs for a missing-variable error.

Generated types

generate_typescript_types returns the database's types. Write them to web/src/lib/database.types.ts exactly as returned, after every migration applied in the run, and never edit the file. Keep it out of the lint and format passes, so nothing rewrites it.

engineering-lead proves the committed file is current: generate again, save the output as evidence/engineering-lead/database.types.generated.ts, and compare byte for byte:

git diff --no-index --exit-code web/src/lib/database.types.ts .devteam/runs/<run-id>/evidence/engineering-lead/database.types.generated.ts

A difference means a migration was applied after the types were written, or the file was edited. Either way it goes back to frontend-engineer.

Release on this stack

The target environment is the Supabase project whose ref is in .mcp.json. release-engineer runs this sequence, with the pre-flight in team-release first. The data layer goes first and stays backward compatible for one release, so the running client never talks to a schema it does not know.

1. Prove offline     the db test command, output saved to evidence/release/
2. Data layer        apply_migration once per file list_migrations does not show, in
                     filename order, name = the file's slug, query = its exact contents
3. Verify            list_migrations matches supabase/migrations/ exactly, by name and order;
                     list_tables shows row level security on every table
4. Prove on project  every supabase/tests/*.test.sql through execute_sql
5. Platform checks   get_advisors for security and performance, clean or accepted in writing;
                     the key scan in Security probes, clean
6. Functions         deploy_edge_function for each function in the diff, after the schema;
                     call each URL, read get_logs, and confirm the API serves the new shape
                     and still serves the old one
7. Front end         npm ci at the root and in web/, then the build command, green.
                     Front-end hosting: deferred: no target chosen
8. Smoke             a local production build of web/ (npm run build, then npm run start)
                     pointed at the project, driven with Playwright through npx playwright
9. Tag and push      as PROJECT.md § Release and team-release set out

Rollback on this stack:

1. The release commits are reverted with git revert and the revert is pushed. Never force-push.
2. Edge Functions are redeployed from the previous release tag's source with deploy_edge_function.
3. A backward-compatible migration stays. One that is not ships its written reverse as a new migration file, applied with apply_migration in the order rollback.md states. Never apply SQL that is not in a migration file.
4. The previous version is verified with list_migrations, the pgTAP suite through execute_sql, get_advisors and the smoke paths.

Before handing off

backend-engineer's step 4 checklist on this stack. Each line has an evidence path.

• Every new table enables row level security in the file that creates it, with all four command policies, the default grants revoked and only the covered commands granted
• Every base table an invariant protects is revoked from anon and authenticated, and reached only through a function or through column grants
• Every security-definer function pins search_path = '' and schema-qualifies every reference
• Every view in an exposed schema sets security_invoker = on
• Every policy wraps auth.uid() in a scalar subquery, and every column a policy filters on is indexed
• Every update policy has both using and with check
• No function that is not part of the API is executable by anon
• No policy or function reads user_metadata
• Every refusal names the invariant, never the input
• The invariant suite covers every invariant this change touched, in the collector form, and passes offline (db-test-pglite.tap) and on the project (pgtap-project-<test file>.tap)
• No secret or service_role key value anywhere outside Edge Function secrets and server environments, and no reference to one under web/
• Every migration is hand-authored in supabase/migrations/, has its reverse in the rollback notes proved offline and on the project, and was proved in a rolled-back transaction on the project before apply_migration
• list_migrations shows every migration file by name and in order, and list_tables output is saved
• get_advisors is clean for security and performance, or every finding is accepted in writing, with the output saved
• web/src/lib/database.types.ts was regenerated with generate_typescript_types
• Every Edge Function touched was deployed with deploy_edge_function and proved by a call to its URL, with the get_logs lines saved
• Every Edge Function secret and project setting the change needs is named in decisions_for_product_lead, with no value written anywhere
• If the MCP did not answer, status is blocked with the reason supabase MCP not authorised, and the offline proof is attached