Migrations

Migrations are plain SQL files in supabase/migrations/, applied in filename order. The Supabase CLI is a dev dependency; scripts/supa wraps it.

Write and apply a migration

pnpm db:new add_something     # creates supabase/migrations/<timestamp>_add_something.sql
# edit the file
./scripts/supa db push        # applies it to the shared dev project

./scripts/supa db push changes the database everyone develops against, immediately (see The shared dev database).

When you change the schema, update Data model in the same PR.

Conventions

• Wrap the migration in begin; … commit;. supabase db push doesn’t add a transaction, so without one a failure part-way leaves the schema half-changed.
• Make it safe for the app version already running. In production, the old code runs against the new schema until the new app finishes deploying. Add things first (expand), and remove what the old code reads only in a later release (contract). A migration that drops or renames something the running code reads breaks production for the length of the deploy.
• Restrict service-role-only functions explicitly. The schema’s default privileges make every new function executable by anon and authenticated. Revoke that (see Data model).
• Keep to the hard rules: no foreign keys from network into app, and every network row carries map_id.

Who applies migrations where

Project	Who	When
movement-mapping-dev	Developers, with ./scripts/supa db push from any branch	Whenever you push
movement-mapping-dev	CI (.github/workflows/db.yml, apply-dev)	Every push to main (a no-op if already applied)
movement-mapping-prod	CI only (promote-prod)	When a release is published

Check a migration locally

There’s no local Supabase. To test migrations against a throwaway Postgres (for example in Docker), first create stand-ins for what Supabase provides:

• the roles anon, authenticated and service_role
• an auth schema with auth.users (id uuid primary key, email text, email_confirmed_at timestamptz) and an auth.uid() function
• a storage schema with a storage.buckets table.

Then apply the files in order with psql -v ON_ERROR_STOP=1.