Supabase / Postgres

Two ways to clean up test users in Supabase. Recommended: deploy a small Edge Function in your own project, so Flowproof never holds your database password. Or give Flowproof a connection string. Either way, a run’s test users are deleted from the tables you name, then from auth.users.

Cleanup

Which to use

Edge Function (recommended). The function runs inside your Supabase project and connects to your database the way your other functions do. Flowproof sends it a signed request naming the addresses to remove; it deletes them and replies with a receipt. Flowproof holds only a signing secret, and even that secret can only ever remove Flowproof test users.
Connection string. Flowproof connects to your database from its own servers and runs the deletes itself. Nothing to deploy, but Flowproof holds your database password (encrypted), and it currently fails to connect in some setups.

Both run the same statements and produce the same receipt.

Recommended: the Edge Function

Five steps, about ten minutes. You need the Supabase CLI, or a coding agent with the Supabase connector (it can do steps 2, 3 and 5 for you: execute_sql and deploy_edge_function).

Get the function and set what it cleans
The function is two small files, index.ts and teardown.ts, plus a config.ts that says which Flowproof project it serves and which tables hold your test users:
config.ts
```
export const CONFIG: EdgeConfig = {
  project: 'acme',          // your Flowproof project name
  rootDomain: 'flowproof.dev',
  layout: 'flat',
  tables: [{ schema: 'public', table: 'profiles', emailColumn: 'email' }],
  ownedTables: [],                  // e.g. { schema: 'public', table: 'projects', column: 'gc_user_id' }
  includeAuthUsers: true,
};
```
Use the same tables you would give the connection string (which ones), and add owned tables for rows that point at the user by id. We can also build it for you as one self-contained file with your settings in place.
Where to get the function
It isn’t published yet. Email support@flowproof.dev with your project name and tables and we’ll send it.

Create its receipts table

Run the included setup.sql once in the SQL editor. It creates one small table where the function keeps each completed delete’s receipt for 7 days, so a retried request gets the original receipt instead of “not found” for users it already removed. Nobody but the postgres role can read it.

SQL (the gist of setup.sql)

create schema if not exists flowproof_edge;
create table if not exists flowproof_edge.receipts (
  request_id text not null,
  mode text not null check (mode in ('preview', 'delete')),
  session text not null,
  receipt jsonb not null,
  created_at timestamptz not null default now(),
  primary key (request_id, mode)
);
alter table flowproof_edge.receipts enable row level security;

Deploy it with JWT verification off
Put the files in supabase/functions/flowproof-teardown/ and deploy. Flowproof signs every request with its own HMAC signature instead of a Supabase JWT, so the platform’s JWT check has to be off for this function; the function refuses anything unsigned itself.
Terminal
```
supabase functions deploy flowproof-teardown --no-verify-jwt
```
With the Supabase connector, ask your agent to deploy it as flowproof-teardown with verify_jwt set to false.

Add it to Flowproof

To Flowproof it’s a webhook. Replace <project-ref> with your project’s reference (the subdomain of its API URL):

Terminal

read -rs FLOWPROOF_API_KEY   # paste your fp_ key, press Enter (nothing is shown)
export FLOWPROOF_API_KEY
curl -s -X POST https://mcp.flowproof.dev/api/projects/acme/adapters \
  -H "Authorization: Bearer $FLOWPROOF_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{"label": "Supabase (Edge Function)", "config": {"system": "webhook", "endpointUrl": "https://<project-ref>.supabase.co/functions/v1/flowproof-teardown"}}'

The reply includes a signing secret, shown once. Keep it for the next step.

Store the secret in Vault, then test, preview and arm
Paste the secret into this, and run it in the SQL editor (it’s encrypted at rest):
SQL
```
select vault.create_secret('<paste the signing secret>', 'flowproof_signing_secret', 'Flowproof teardown signing secret');
```
Then test, preview and arm with the requests on the Cleanup page. Until the secret is stored, the function answers every request with 503 and deletes nothing.

Exactly what it runs

For each table you name, in the order you list them, and then for auth.users:

SQL

select <id>, <email> from <schema>.<table> where lower(<email>) = any($1);
delete from <schema>.<table> where lower(<email>) = any($1) returning <id>, <email>;

For an owned table, which points at the user by id, it runs these first, before everything else:

SQL

select o.<pk>, u.email from <schema>.<table> o, auth.users u
 where u.id = o.<column> and lower(u.email) = any($1);
delete from <schema>.<table> o using auth.users u
 where u.id = o.<column> and lower(u.email) = any($1) returning o.<pk>, u.email;

$1 is the run’s list of addresses, passed as a parameter, and the only parameter. A preview runs only the select. There is no other statement: no pattern, no domain match, no “is test user” flag.

Alternative: a connection string

Flowproof connects to your database and runs the deletes itself. Works with any Supabase project, and any Postgres database Flowproof can reach over the internet with TLS. Nothing has to be installed in your database.

Get the pooler connection string
In the Supabase dashboard, click Connect and copy the Transaction pooler string (port 6543, user postgres.<project-ref>). Replace [YOUR-PASSWORD] with your database password. If the password contains #, /, ?, @ or %, URL-encode those characters.
Use the pooler, not the direct string
The direct string (db.<ref>.supabase.co) only works over IPv6 unless you’ve bought Supabase’s IPv4 add-on, and Flowproof may not be able to reach it. The Session pooler (same host, port 5432) works too. Leave off extra parameters like ?pgbouncer=true.
Decide which tables to clean
List every table that stores a user’s email in its own column, such as public.profiles with an email column. Tables that only point at the user by id don’t need listing if their foreign key cascades; if it doesn’t, add them as owned tables. The connection test tells you which.
auth.users is included by default, so the test account can’t sign in after cleanup. To keep logins, send "includeAuthUsers": false; you then have to name at least one table.

Add the database to Flowproof

Load your API key and the connection string without either landing in your shell history, then create the connection. Edit the tables list first.

Terminal

read -rs FLOWPROOF_API_KEY   # paste your fp_ key, press Enter (nothing is shown)
export FLOWPROOF_API_KEY
read -rs SUPABASE_DB_URL   # paste the Transaction pooler connection string, press Enter

Terminal

curl -s -X POST https://mcp.flowproof.dev/api/projects/acme/adapters \
  -H "Authorization: Bearer $FLOWPROOF_API_KEY" \
  -H "Content-Type: application/json" \
  -d "{\"label\": \"Production DB\", \"config\": {\"system\": \"supabase\", \"connectionString\": \"$SUPABASE_DB_URL\", \"tables\": [{\"schema\": \"public\", \"table\": \"profiles\", \"column\": \"email\"}]}}"

The same request, laid out so it’s easier to read:

Request body

{
  "label": "Production DB",
  "config": {
    "system": "supabase",
    "connectionString": "postgresql://postgres.<project-ref>:<password>@aws-0-<region>.pooler.supabase.com:6543/postgres",
    "tables": [
      { "schema": "public", "table": "profiles", "column": "email" }
    ],
    "includeAuthUsers": true
  }
}

Test, preview and arm
Use the requests on the Cleanup page. The test checks everything below and lists any problems as warnings. Fix them before you arm.

Permissions and row level security

The Edge Function connects as postgres through the SUPABASE_DB_URL Supabase gives every function, so it already has everything below. For a connection string: the role needs, on every table it cleans (including auth.users): SELECT on the id and email columns, DELETE on the table, and the ability to see every row.

The postgres role in Supabase has all of that: it has BYPASSRLS and owns your public tables. If you use a dedicated role, grant it those permissions and alter role … bypassrls.

The foreign-key warnings

Deleting a user from auth.users fails if another table points at it, directly or through a table that cascades from it, with on delete restrict or no action, and the test user owns a row there. The whole delete is then rolled back: nothing is removed, and the receipt shows the error.

The connection test follows your foreign keys down every on delete cascade (up to six levels) and lists each one that would block, with its path and a fix. A preview shows the same warnings; it can’t hit the error itself, because it only reads. Fix them before you arm, in one of three ways: make the key cascade, list that table before auth.users if it has its own email column, or make it an owned table if it holds the user’s id.

The Edge Function doesn’t produce these warnings yet. If one of its deletes hits such a key, the receipt shows an fk_violation error naming the table, lists that test user’s rows as skipped, and nothing of that user is removed; other test users in the run are unaffected. The fixes are the same.

SQL

-- Example: let a test user's profile go when the user does.
alter table public.profiles
  drop constraint profiles_id_fkey,
  add constraint profiles_id_fkey
    foreign key (id) references auth.users (id) on delete cascade;

Between your own tables, list child tables before parent tables. A wrong order fails with a foreign-key error and rolls back; it never half-deletes.

The warnings don’t stop you arming: a test user may never own a row in the table concerned. If a real cleanup does hit one, the receipt shows the error, names the constraint, and repeats the path.

Rows that point at the user by id

Some rows belong to a user without holding their email. A common shape: profiles.id cascades from auth.users, and every new user gets a sample project whose gc_user_id references profiles(id) with on delete restrict. Deleting the test user cascades to the profile, the restrict refuses it, and the test user stays. The connection test says so:

Connection test

warning: auth.users → public.profiles (cascade)
  ← public.projects.gc_user_id (restrict: projects_gc_user_id_fkey):
  a test user with rows in public.projects cannot be deleted ...
  Fix: add --owned public.projects:gc_user_id (ownedTables in the API) ...

Add the table to ownedTables, with the column that holds the owner’s auth user id. Its rows are deleted first, so nothing is left to block the user:

Add to config

"ownedTables": [
  { "schema": "public", "table": "projects", "column": "gc_user_id" }
]

The id is never something you send. Flowproof joins it from the auth.users row whose email is one of the run’s addresses, in the same statement. A row owned by anyone else doesn’t match and can’t be touched.
The column must have the same type as auth.users.id (uuid); the connection test checks. It doesn’t need a foreign key to auth.users.
Owned tables need includeAuthUsers, because that’s where the ids come from.
The receipt lists each row as public.projects #<id>. A table without a single-column primary key is reported as a count per user instead. Rows that cascade from an owned row (a project’s line items) are removed by your database and aren’t listed.

“May fail depending on cascade order”

If two tables are both removed by the same cascade and one restricts the other (say draw_items.line_item_id → line_items, both under projects), whether the delete works depends on which cascade Postgres runs first. That’s true for no action as well as restrict. Your app’s own “delete project” runs the same cascade, so if that works, this will too. To make it order-proof, make the child key cascade:

SQL

-- Example: make a child key order-proof inside a cascade.
alter table public.draw_items
  drop constraint draw_items_line_item_id_fkey,
  add constraint draw_items_line_item_id_fkey
    foreign key (line_item_id) references public.line_items (id) on delete cascade;

What Flowproof holds

With the Edge Function: a signing secret, encrypted, used only to sign requests. Your database password never leaves Supabase.

With a connection string: it is encrypted as soon as it arrives and decrypted only by the one Flowproof service that runs cleanup. It never appears in the dashboard, logs or error messages. Remove the connection and it’s deleted. More in Security.

Get the function and set what it cleans

Create its receipts table

Deploy it with JWT verification off

Add it to Flowproof

Store the secret in Vault, then test, preview and arm

Get the pooler connection string

Decide which tables to clean

Add the database to Flowproof

Test, preview and arm