Engineering

How we reconstructed a production Supabase database with 277 tables, 190 functions and 261 triggers

We rebuilt the full catalog of a production Supabase Postgres database on a different PostgreSQL server, copied 4.2 million rows, and verified schema, data and application behavior. This is an engineering case study of a staging reconstruction: production cutover has not happened.

What this is, and what it is not

This is a write-up of a migration rehearsal. The source was a real, multi-tenant production Supabase project on PostgreSQL 17.6. The destination was a local PostgreSQL 16 server. The source was only ever read. Everything below was done on disposable copies, and no production cutover has taken place. Customer data, tenant identities and credentials are deliberately absent from this page. What follows is the method, the numbers and the mistakes, because most Supabase migration advice stops at pg_dump and the interesting problems start after it.

Why the migration files were not enough

The obvious plan is to replay the project's migration folder against a new database. It does not work, and the reason is general. In this project the SQL files in the repository created 177 tables and 50 functions. The live database had 277 tables and 190 functions. Core tables, and 76 of the 90 stored procedures the application called, had no SQL file anywhere in the repository. A project that has been edited by hand for a year may look the same.

So the live catalog, the database's own description of itself, has to be the source of truth. Reconstructing from it means reading it, and reading it safely.

Reading the catalog without touching the source

Capture sends only single SELECT or WITH statements on a session with default_transaction_read_only on. It reads relations, columns with exact types, generated and identity columns, defaults, constraints, indexes, sequences, functions with their bodies and attributes, triggers, views and materialized views, policies, privileges including column-level ones, extensions, enums and other types, comments, roles and role settings. Supabase-managed schemas (auth, storage, realtime, vault, graphql) are not reconstructed; their data and behavior belong to those services.

Dependency order

Each stage may depend only on earlier ones, and a failing object is a hard failure, never skipped silently. The order that loaded with zero errors:

The Supabase-shaped parts

A Supabase database is a PostgreSQL database plus conventions the platform supplies. Anything that depends on them breaks quietly on a plain server. Four mattered here.

• JWT helper functions. Policies and triggers call auth.jwt(), auth.role() and auth.uid(). We copied their definitions verbatim from the source and replayed the schema USAGE and function EXECUTE they need. They read request.jwt.claims, so the destination's PostgREST must set it, and the keys the application presents must be JWTs it accepts. We tested this under real PostgREST, not by calling the functions by hand.
• Roles. anon, authenticated, service_role and authenticator are created without passwords. Login roles are recreated but never with the source's secrets.
• Extensions in a non-default schema. Finance functions called extensions.digest from pgcrypto. Without USAGE on the extensions schema they fail only when a particular code path runs.
• Vault. supabase_vault is deliberately not recreated. The catalog report lists it as excluded, with the reason.

PostgreSQL 17 to 16: the MAINTAIN privilege

PostgreSQL 17 added a MAINTAIN table privilege, and Supabase's API roles hold it. PostgreSQL 16 has no such privilege, so a straight replay of the grants is a syntax error. The planner omits it and records each affected object as transformed with the reason, and the parity check treats those as expected differences. We found this the unpleasant way: the first generator left the privilege letter m unmapped. The parity run caught it. We did not upgrade the destination to make the problem disappear.

A privileges trap

In this database, 97 of the 190 functions, 66 of them SECURITY DEFINER, deny EXECUTE to anon and to PUBLIC on purpose. A common bootstrap step is grant execute on all functions in schema public to the API roles. Run after a faithful reconstruction, it silently reopens every one of those functions through PostgREST's /rpc endpoint. We wrote a test that proves the hazard and documented that such a shim must never run after a catalog migration. If you migrate a Supabase project, compare function privileges before and after, not just that the functions exist.

Verifying the schema: catalog parity

After applying, the destination is captured again and compared with the source object by object across 15 categories: relations, columns, constraints, indexes, sequences, functions, triggers, views, materialized views, policies, privileges, types and extensions, among them. Each difference is classified as equivalent, normalized (identical once parentheses and whitespace are ignored) or expected (transformed or excluded in the ledger). On the final PostgreSQL 16 run every category came out equivalent, normalized or expected, with none unexplained. One check constraint differed only in how the engine parenthesized an AND chain, which is the normalized case. Every object in the ledger is classed migrated, transformed, excluded, unsupported or failed, with a reason. Nothing is dropped without a line saying so.

Verifying the data: row parity

Rows are copied page by page through a read-only query interface, with a checksum computed over each page on the source and again on the destination. The copy is resumable, throttles itself, and honors Retry-After when the source rate-limits it: our first attempt stopped at 48 of 277 tables on an HTTP 429 and the tool was changed to wait and resume.

The three drift tables are append-only logs and click streams that were being written during the read. Each differs from its source count by at most nine rows out of 156,181 to 398,506, and every page that was copied matched its source checksum, so we report them as drift and not as exact. Beyond tables, 2 of 2 sequences were set safely, 582 of 582 foreign keys validated clean, and 3 of 3 materialized views refreshed.

The checksum bug that looked like a data bug

One table first came back as a mismatch. The data was identical; our checksum was wrong. We had hashed each row's text form, and the quoting of values in that text form can vary with locale settings. Hashing a JSON rendering of the row (to_jsonb) removed the false mismatch. The lesson is general: a verifier must be tested against known-equal data before it is trusted against unknown data, because a false mismatch costs hours and a false match costs the migration.

Verifying behavior: does the application still work?

Matching schemas and rows does not prove the application works. We ran the application's own 6,905-test suite as a baseline, then ran the application against the reconstructed database with every outbound provider deliberately unconfigured, using synthetic tenants. The lifecycle from lead through quote, booking, job, invoice, payment, ledger and profitability worked; tenant isolation held (a second tenant saw none of the first tenant's rows); the database refused an unbalanced ledger entry; and 26 scheduled jobs ran with no external sends. No migration regression was found.

It did find four environment differences, which are exactly what a catalog copy tends to miss: the destination's default timezone was not UTC, every object was owned by the loader role instead of the source owners, a postgres role that one service's own migrations expect did not exist, and database-level settings such as the timezone are not part of the catalog we capture at all. It also found bugs that exist identically in the source, which is a useful by-product: a migration rehearsal audits the source too.

What we deliberately did not migrate or test

• Production cutover, traffic, and anything that needs production-only state.
• Storage object bytes. The bucket configuration was reconstructed; the files were not copied.
• Supabase Auth data, vault secrets, realtime, and role passwords.
• Source-to-destination role ownership mapping and database-level settings. Both are open items.
• Aggregates, operators, casts, rules, partitioned tables and foreign servers are reported as unsupported, not recreated.
• 47 of the 73 scheduled jobs, and everything that depends on a payment, SMS, email or search provider.

Lessons

1. Treat the live catalog as the source of truth, and the migration folder as a hint.
2. Classify every object. A migration report that cannot say "excluded, because" is hiding something.
3. Test privileges and policies as the roles that will use them, under the real API layer, not as a superuser.
4. Verify the verifier. Prove it can detect a difference and can accept equality.
5. Rehearse on a copy with the providers switched off, so a test cannot send a real message or charge a real card.
6. Database-level settings, ownership and roles are part of the migration even though they are not "the schema".

The tooling is part of Stackap and is not publicly available yet. If you are weighing a similar move, the practical guides are migrating from Supabase and the Supabase comparison, and the platform side is described under databases and backups.

Questions

Last updated 2026-10-04.