Skip to content

Database migrations

Every schema and seed change to the Supabase Postgres database lives as a SQL file in supabase/migrations/. This runbook covers how to author, apply, push, and undo migrations.

Conventions

From CLAUDE.md:

  • Filename: YYYYMMDDHHMMSS_description.sql (UTC timestamp prefix, snake_case description). The timestamp determines apply order.
  • Idempotent — every statement must be safe to run more than once:
    • CREATE TABLE IF NOT EXISTS, CREATE INDEX IF NOT EXISTS, ALTER TABLE ... ADD COLUMN IF NOT EXISTS
    • CREATE OR REPLACE FUNCTION, CREATE OR REPLACE VIEW
    • INSERT ... ON CONFLICT DO NOTHING or INSERT ... WHERE NOT EXISTS (...)
    • Drops use DROP ... IF EXISTS
  • RLS policies on any table gated by permissions must call auth_user_has_permission('x.y'). Never hardcode role names (CEO, GM, etc.) in RLS — that bypasses the permission matrix and silently breaks when roles are reshuffled.

Danger

Once a migration is merged to main and applied to production, it is immutable. Do not edit the file, re-timestamp it, or delete it. If the change is wrong, write a new forward migration that fixes it.

Creating a new migration

Option A — Supabase CLI

supabase migration new add_airline_capacity_column

This stamps the current UTC timestamp and creates an empty file at supabase/migrations/YYYYMMDDHHMMSS_add_airline_capacity_column.sql.

Option B — hand-written

Copy an existing file as a template, rename with today's UTC timestamp. Useful when you want to match formatting conventions in nearby migrations.

Fill in the SQL. Prefer small, focused migrations (one feature per file) over omnibus scripts.

Applying locally

Two modes, depending on how wrecked your local DB is:

  1. Full reset (fastest when local DB is out of sync): 

    supabase db reset
    Drops the local Postgres, replays every migration from scratch, then runs seeds. Safe on localhost, catastrophic on production — the CLI guards this by reading supabase/config.toml, but double-check your shell is not pointed at a linked remote.

  2. Incremental apply (preserves local data): 

    supabase migration up
    Applies only migrations newer than what the local DB has already run.

Warning

If you write a non-idempotent migration, db reset will catch it (clean DB → success) but migration up on a partially-applied DB will fail with relation already exists or duplicate key. Always exercise both paths before merging.

Pushing to remote

The project is linked to Supabase project yzpfwdxpwalmfuodkxni (see supabase/config.toml).

  1. Confirm the link: 
    supabase link --project-ref yzpfwdxpwalmfuodkxni

  2. Preview what will be applied: 

    supabase db diff --linked

  3. Push: 

    supabase db push
    For bulk/first-time catch-up (many migrations behind): 
    supabase db push --include-all --yes
    --include-all ignores the schema_migrations bookkeeping table; --yes skips the interactive confirm. Use this only when you know every pending migration is safe.

Danger

supabase db push runs every pending migration against production. There is no dry-run that actually proves idempotence — if one statement fails halfway, later statements in the same file may or may not have run. Always:

  1. Apply the migration to your local Supabase (supabase db reset) first.
  2. Run the test suite against it (npm test).
  3. Then push to remote during a low-traffic window.

Seeding the permission matrix

When you add a new permission (see CLAUDE.md → Permissions), the same PR must include a migration that:

  1. Inserts the permission row into the permissions table.
  2. Grants it to the appropriate roles.

Pattern (adapted from existing seed migrations):

-- Seed the permission
INSERT INTO permissions (name, description, category)
VALUES ('finance.journals.approve', 'Approve manual journal entries', 'finance')
ON CONFLICT (name) DO NOTHING;

-- Grant it to roles
INSERT INTO role_permissions (role, permission_name)
SELECT r.role, 'finance.journals.approve'
FROM (VALUES ('CEO'), ('GM'), ('IT_ADMIN'), ('FINANCE_MANAGER')) AS r(role)
ON CONFLICT DO NOTHING;

The permissions matrix test (src/test/permissions.matrix.test.ts) will fail the build if the code references a permission that was never seeded, or vice versa.

RLS conventions

Every table touched by a gated feature needs RLS enabled and policies that delegate to auth_user_has_permission:

ALTER TABLE bookings ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS bookings_read ON bookings;
CREATE POLICY bookings_read
  ON bookings
  FOR SELECT
  USING (auth_user_has_permission('bookings.view'));

DROP POLICY IF EXISTS bookings_write ON bookings;
CREATE POLICY bookings_write
  ON bookings
  FOR ALL
  USING (auth_user_has_permission('bookings.edit'))
  WITH CHECK (auth_user_has_permission('bookings.edit'));

Warning

Do not write USING (role = 'CEO'). This is the same anti-pattern CLAUDE.md bans in application code. Grant a permission to the role via role_permissions instead. See docs/PERMISSIONS.md and the reference migration 20260416020000_remove_permission_bypass.sql.

Re-run safety

A well-written migration should be re-runnable without errors because (a) the CI / local dev loop re-runs everything after db reset, and (b) if production apply partially fails, the retry must not double-insert.

Checklist before merging:

  • [ ] Every CREATE uses IF NOT EXISTS.
  • [ ] Every ALTER TABLE ... ADD COLUMN uses IF NOT EXISTS.
  • [ ] Every INSERT has an ON CONFLICT DO NOTHING or a WHERE NOT EXISTS guard.
  • [ ] Every DROP uses IF EXISTS.
  • [ ] Functions and policies use CREATE OR REPLACE / DROP POLICY IF EXISTS ... CREATE POLICY.
  • [ ] Running supabase db reset locally succeeds.
  • [ ] npm test passes, including the permissions matrix test.

Renaming or deleting a permission

From CLAUDE.md: update PERMISSIONS.md, every code reference, and ship a rename/drop migration in the same PR.

-- Rename: preserve grants by updating by primary key
UPDATE permissions
SET name = 'finance.journals.review'
WHERE name = 'finance.journals.approve';

UPDATE role_permissions
SET permission_name = 'finance.journals.review'
WHERE permission_name = 'finance.journals.approve';

-- Drop (only after every code reference is gone)
DELETE FROM role_permissions WHERE permission_name = 'legacy.flag';
DELETE FROM permissions      WHERE name            = 'legacy.flag';

After pushing the migration, run npm test — the permissions matrix test validates drift against docs/PERMISSIONS.md and src/lib/api.ts / src/App.tsx.

When a migration fails in production

  1. Read the error from supabase db push output carefully. The failing statement and line are in the stderr.
  2. If the error is recoverable (e.g. you added a column with a non-nullable default on a huge table and it timed out):
    • Write a new migration that completes the job in smaller steps.
    • Do not edit the original file.
  3. If the migration partially applied and the DB is in a broken state:
    • Connect directly with psql using SUPABASE_DB_URL (same secret the backup workflow uses).
    • Inspect schema_migrations to see what recorded as applied.
    • Manually apply the remaining statements, then write a follow-up no-op migration so main and prod stay in sync.
  4. If the failure is catastrophic and you need to restore data, see backup-restore.md.

A transferred booking is read-only

A booking whose travellers were all transferred out is TRANSFERRED and the database refuses every change to it, its travellers and its payments (LC-031). A maintenance script that genuinely has to change one (a data correction agreed with finance) switches the lock off for its own transaction only, and says why in the same transaction's audit note:

BEGIN;
SELECT set_config('app.transferred_booking_maintenance', 'on', true);  -- this transaction only
-- … the correction …
COMMIT;

Never set it for a session or in a function a screen calls. A browser session cannot set it.

Bookings transferred before 2 Oct 2026 — a finance task

20261002090100_a_transfer_keeps_its_records.sql closes every booking already emptied by transfers as TRANSFERRED (bkg_backfill_transferred_bookings()), but it does not move their money: such a booking keeps what it received (paid > 0, total 0), and the booking the travellers went to shows nothing paid. The list shows it as Transferred, not paid.

To find them (read-only):

SELECT b."bookingNo", b."paidAmount", b."transferredToBookingNos"
  FROM "Booking" b
 WHERE b.status = 'TRANSFERRED' AND COALESCE(b."paidAmount", 0) > 0
   AND NOT EXISTS (SELECT 1 FROM "BookingPaymentCarry" c WHERE c."fromBookingId" = b.id);

Carrying that money is a finance decision per booking (which booking, how much). Nothing carries it automatically; there is no screen for it yet. Until one is built, finance records the decision and an engineer writes it as a BookingPaymentCarry row in a reviewed maintenance transaction as above, then refreshes both bookings with bkg_refresh_paid(id); a carry between two different party ledgers also needs its pending voucher (as transfer_passenger_to_booking() posts it).

A staff role changes only by request

Since 20261003165000_a_role_changes_with_two_approvals.sql a staff role — every role but CUSTOMER, AGENT and TOUR_LEADER — is added or removed only when it is requested, a CEO approves it and an admin applies it (ACC-077 … ACC-080, ACC-082). The trigger UserRole_staff_role_lock refuses every other write, from the browser, from an edge function with the service key and from any other database function. Use the Role requests page (/admin/role-requests) for every ordinary change.

Maintenance. A migration, a backfill or a restore repair that genuinely has to write UserRole does it from a direct database session (the migration runner, or psql with the database URL), switching the lock off for that one transaction:

BEGIN;
SET LOCAL app.role_change_maintenance = 'on';   -- this transaction only
-- … the change, e.g. INSERT INTO public."UserRole" …
COMMIT;

The switch does nothing through the API: a session that came through PostgREST (anon, authenticated, service_role) is refused with it on, so an edge function cannot use it. Every write is still recorded in the audit trail by audit_userrole. The database tests switch it on for their fixtures, the same way. scripts/seed-test-users.mjs uses the service key, so it can no longer give the test logins their staff roles: give them in a direct session as above, or through role requests.

Emergency — both CEOs away. The super admin holds roles.approve (it holds every permission), so it approves in place of a CEO on the Role requests page; an IT admin then applies as usual. The super admin cannot also apply a request it approved (ACC-078). If no admin who may apply is available either, a second super-admin login can apply, or the owner makes the change in a direct database session with the maintenance switch and records why in the same transaction:

BEGIN;
SET LOCAL app.role_change_maintenance = 'on';
INSERT INTO public."AuditLog" (id, "actorId", action, entity, "entityId", metadata, source)
VALUES (gen_random_uuid()::text, 'system:owner', 'role_change.emergency', 'User', '<user id>',
        jsonb_build_object('role', '<ROLE>', 'why', '<reason>', 'by', '<who ran this>'), 'maintenance');
-- … the UserRole change …
COMMIT;

Afterwards file the same change as a role request and reject it with a note pointing at the emergency, so the Role requests history shows it.

A role change that would leave nobody able to approve or apply a role change is refused (role_change_admin_decide). If that ever happens after a restore, give CEO or IT_ADMIN to the right login in a direct session as above.