Skip to main content

Manage database migrations

Supabase owns migration history. Ordered SQL files live in supabase/migrations/. The running API uses DATABASE_URL through pg and never applies migrations when it starts.

Prepare a local database​

Point DATABASE_URL at the Supabase Postgres database the API should use. DIRECT_URL is reserved for Supabase CLI migration commands and is not read by the API.

Change the schema​

  1. Add a new ordered SQL file under supabase/migrations/. Keep later changes in new files; do not edit a migration that has already reached a shared database.

  2. Apply that file with the Supabase CLI against the intended database.

  3. Run the API tests:

    npm test --workspace api

Work with the initial core schema​

The history begins with 20261009111000_users.sql and 20261009123505_media_library.sql. Together they establish application users, profiles, upload attempts, and durable media items. Review Core user and media schema before changing these tables.

20261015160000_realtime_user_channel.sql adds a select policy on realtime.messages so an authenticated session can read only its own user topic. It does not change the core tables. See Realtime event channels.

Work with the booking schema​

Apply the booking files in timestamp order after the earlier migrations:

  1. 20261016120000_booking_core.sql
  2. 20261016171000_booking_feature_scaffold.sql
  3. 20261016173000_notification_event_preferences.sql

The event-preference file is a forward migration because the scaffold was already applied locally. Do not fold its columns back into the earlier file. After applying all three, run the database integration tests against that database:

DATABASE_INTEGRATION=1 npm test --workspace api -- src/shared/database/core-schema.integration.test.ts src/shared/database/booking-feature-schema.integration.test.ts

The tests use DATABASE_URL, create fixture rows, and roll back their transactions. They do not apply migrations. See Booking persistence schema for the tables and constraints these migrations establish.

Work with the channel authentication schema​

Two files add the channel-authentication storage and the identity changes for Telegram sign-in. Apply them in timestamp order:

  1. 20261021120000_channel_authentication_identities.sql creates the channel_auth schema, owned by the API's channel-authentication slice: principals, provider identities, exchange contexts, consumed-proof hashes, verified channel contacts and permissions with their source and applied versions, processed Telegram update IDs, migration checkpoints, and the channel_auth.purge_expired retention function.
  2. 20261021120100_client_identity_and_enrollment.sql adds the client supabase_user_id mapping, allows incomplete contact and optional Telegram routing, and creates professional_enrollments. It enrolls every existing telegram: user.

Existing account, user and claim IDs are not rewritten. Apply them to the local database:

node_modules/.bin/supabase migration up --local

These two files replaced an earlier, unreleased set of 2026-10-21 migrations that created an auth_gateway schema and role. A local database that applied the earlier set needs a reset before applying them:

node_modules/.bin/supabase db reset --local

A reset deletes all local data. Then run the integration suite, which reads DATABASE_URL from apps/api/.env:

npm run test:database --workspace api

The repository tests commit rows across connections to exercise real races, then delete exactly the rows they created. See Channel authentication for the ownership rules these migrations enforce.