Operations
Reset the hosted develop and production databases after a migration squash.
The move to Declarative Schemas 2.0 replaced every migration with one squashed set: the extensions, one init, the event triggers, the module data, the data migrations and the PermDock seeds. The schema also changed in ways that cannot be migrated in place: uuid keys for customers, assets and teams, renamed columns, enum status columns and the document_parties table.
The hosted databases still record the old migration versions. When the squashed set reaches them, the Supabase GitHub integration runs the new init against the old schema and fails with relation already exists. Repairing the migration history is not an option either, because the old schema does not match the new init. Both hosted databases are therefore reset once. CI does not catch this, because it always starts from an empty database.
The product owner approved a reset of production that drops its data. Take the dumps in step 2 regardless.
pnpm supabase:start).supabase branches get never returns a connection string for main.--linked. The local link points at production, and a reset with it would run seeds there.Freeze deploys. Pause merges to develop and main until step 9 is done.
Dump both databases. The develop URL comes from supabase branches get; the production URL is the session-mode connection string with the password filled in.
DEVELOP_DB_URL="$(pnpm exec supabase branches get develop --project-ref gunugimgrrnxqynjghqe -o env | grep '^POSTGRES_URL=' | cut -d= -f2- | tr -d '"')"
PRODUCTION_DB_URL="postgresql://postgres.gunugimgrrnxqynjghqe:<password>@<session-pooler-host>:5432/postgres"
pnpm exec supabase db dump --db-url "$DEVELOP_DB_URL" -f develop-schema.sql
pnpm exec supabase db dump --db-url "$DEVELOP_DB_URL" --data-only --use-copy -f develop-data.sql
pnpm exec supabase db dump --db-url "$PRODUCTION_DB_URL" -f production-schema.sql
pnpm exec supabase db dump --db-url "$PRODUCTION_DB_URL" --data-only --use-copy -f production-data.sql
pnpm exec supabase db dump --db-url "$PRODUCTION_DB_URL" --data-only --use-copy -s auth,storage -f production-auth-storage.sqlKeep the dumps outside the repository. They hold personal data and secrets.
Raise the lock limit on production. A remote reset drops every object in one transaction and fails with out of shared memory at the default lock limit. Develop already runs with max_locks_per_transaction=1024. This restarts the production database:
pnpm exec supabase postgres-config update --experimental --project-ref gunugimgrrnxqynjghqe --config max_locks_per_transaction=1024Reset develop from the squash branch, before merging it. A remote reset keeps the job queue tables in the pgmq schema but leaves them without their msg_id column, and better_supabase.ensure_job_queue then skips them, so the seeds fail with column "msg_id" does not exist. Drop the queues first with this drop-job-queues.sql, then reset. The reset applies the squashed migrations and the demo seeds:
do $$
declare
r record;
begin
for r in select queue_name from pgmq.meta loop
perform pgmq.drop_queue(r.queue_name);
end loop;
for r in
select c.relname
from pg_class as c
join pg_namespace as n on n.oid = c.relnamespace
where n.nspname = 'pgmq'
and c.relkind in ('r', 'p')
and (c.relname like 'q\_%' or c.relname like 'a\_%')
loop
execute format('drop table pgmq.%I', r.relname);
end loop;
end $$;psql "$DEVELOP_DB_URL" -f drop-job-queues.sql
pnpm exec supabase db reset --db-url "$DEVELOP_DB_URL"
pnpm stripe:sync:migrate --db-url "$DEVELOP_DB_URL"The migration history now lists the squashed versions, so the integration has nothing left to apply when the pull request merges.
Merge the pull request into develop. Confirm that the Supabase Preview check passes and that it reports no pending migrations. Sign in on app.centrakit.dev with a seeded login and open a quote, an invoice and a calendar.
Reset production without seeds. Production keeps seeding disabled, and --db-url reads the base [db.seed] setting, so pass --no-seed. Drop the job queues first, as in step 4:
psql "$PRODUCTION_DB_URL" -f drop-job-queues.sql
pnpm exec supabase db reset --db-url "$PRODUCTION_DB_URL" --no-seed
pnpm stripe:sync:migrate --db-url "$PRODUCTION_DB_URL"
pnpm stripe:sync:backfillRun the backfill as described in Stripe Billing.
Check production before release. In the SQL editor:
select count(*) from supabase_migrations.schema_migrations;
select count(*) from public.roles where system;
select count(*) from public.billing_plans;
select count(*) from storage.buckets;
select count(*) from auth.users;The first four must match a fresh local reset (pnpm supabase:reset). If auth.users still has rows, their profiles and memberships are gone: delete those users in the dashboard, or recreate the owners and invite the rest. Remove Storage objects whose rows no longer exist with the bucket browser or the Storage API.
Release to main. Merge the develop to main pull request. The integration applies config.toml and finds no pending migrations.
Create the platform admin again. Sign up on app.centrakit.com, then grant the system role in the SQL editor and sign in again so the token carries it:
insert into public.user_roles (user_id, role_id)
select users.id, roles.id
from auth.users as users
cross join public.roles as roles
where users.email = '<admin email>'
and roles.scope = 'system'
and roles.key = 'platform-admin';Last updated on