Schema changes and migrations

The schema files in apps/web/supabase/schemas/ describe what the database should look like. Migrations in apps/web/supabase/migrations/ are what actually runs against a database. Change both together. The steps below come from apps/web/supabase/AGENTS.md, which your coding agent also follows.

Add a new table

# 1. Write the table, its grants and its policies in a new schema file
touch apps/web/supabase/schemas/20-projects.sql

# 2. Create an empty migration and copy the same SQL into it
pnpm --filter web supabase migrations new projects

# 3. Apply it locally and regenerate the types
pnpm --filter web supabase migrations up
pnpm supabase:web:typegen

Change an existing table

# 1. Edit the schema file, then let the CLI write the migration from the diff
pnpm --filter web supabase:db:diff -f update_projects

# 2. Apply and regenerate
pnpm --filter web supabase migrations up
pnpm supabase:web:typegen

The diff tool does not write column-level grant update (...) statements. Add those to the migration by hand (see the next section).

Rules every new table follows

From apps/web/supabase/AGENTS.md:

• Turn row-level security on.
• revoke all from anon, authenticated and service_role first. Supabase's defaults grant TRUNCATE and other privileges to all three, and TRUNCATE ignores row-level security.
• Never grant table-wide UPDATE to authenticated. Grant it on the columns users may edit, and leave out id and account_id, so a user cannot move a row into another account.
• Prefer generated always as identity over serial, so there is no sequence privilege to get wrong.
• Write policies with the existing helper functions (has_role_on_account, has_permission and others; see Row-level security).

apps/web/supabase/AGENTS.md has a full table template with these lines in place.

Check the change

pnpm supabase:web:reset   # rebuild the local database from all migrations
pnpm supabase:web:test    # run the pgTAP tests

schema-conditions.test.sql fails if any table in public has row-level security off, so a forgotten enable row level security shows up here. Add your own test for the new table too: see Database tests. If your agent made the change, the root AGENTS.md also requires the /rls-review skill.

Deploy

SUPABASE_PROJECT_REF=<your-project-ref> pnpm --filter web supabase:deploy

This links the hosted project and runs supabase db push, which applies the migrations that are not there yet. It does not push seed.sql. Do not add --include-seed: the seed contains a published super-admin login.

The development seed also creates the database webhook that calls /api/db/webhook when a subscription row is deleted. In production, create that webhook yourself in the Supabase dashboard (see Webhooks).