leaflet
a tool for shared writing and social publishing
Star
Fork
Watch
leaflet scripts schema-drift README.md
4.1 kB · 77 lines
Markdown
at main

schema-drift #

Compare the catalog of production, the long-lived local Supabase stack, and a throwaway database built only from supabase/migrations.

# prod (read-only) vs local stack; writes scripts/schema-drift/snapshots/{prod,local}.json
node --env-file=.env.local scripts/schema-drift/drift.mjs check

# what the migrations alone produce (docker; same image as the local stack)
node scripts/schema-drift/fresh.mjs up
node scripts/schema-drift/drift.mjs snapshot fresh /tmp/fresh.json
node scripts/schema-drift/drift.mjs diff scripts/schema-drift/snapshots/prod.json /tmp/fresh.json
node scripts/schema-drift/fresh.mjs down

# print prod's definition of an object, for writing a catch-up migration
node --env-file=.env.local scripts/schema-drift/drift.mjs defs prod index public.facts_entity_idx
#   kinds: function | index | view | trigger | policy | constraint (keys as printed in the diff)

Targets: prod = DB_URL, local = LOCAL_DB_URL or 127.0.0.1:54322, fresh = FRESH_DB_URL or 127.0.0.1:54399, or the name of any env var that holds a connection string. In a diff, - is only in the first snapshot, + only in the second, ~ in both but different. Exit code is 1 when there are differences.

Safety #

Every session sets default_transaction_read_only = on and runs inside BEGIN READ ONLY, and only reads pg_catalog / information_schema, pg_settings, role settings, storage.buckets metadata, cron.job metadata and supabase_migrations.schema_migrations. No app rows are read. Connection strings are never printed. Role/database settings whose name looks secret (*secret*, *key*, *token*, *password*, *jwt*) are stored as <masked:hash> so a change still shows up. Function bodies, view definitions and cron commands are stored as hashes; use defs to see a body.

What's captured #

Postgres version; extensions; plan-relevant pg_settings; role and database settings (pgrst.*, statement_timeout, ...); app-schema tables, columns (type/default/nullability/identity/generated/collation/statistics target), column order, indexes (incl. partial/expression, validity), constraints (incl. NOT VALID), functions (signature, result, language, volatility, SECURITY DEFINER, proconfig, body hash, owner), triggers, views, RLS flags, policies, grants to anon/authenticated/service_role/authenticator/postgres/PUBLIC, default privileges, sequences, enums/domains/composite types, extended statistics, publications, event triggers, storage buckets, pg_cron jobs and migration history. Table sizes/row estimates and index scan counts go under info and aren't diffed.

Ignored as noise #

  • Tables/functions in Supabase-managed schemas (auth, storage, realtime, extensions, graphql*, vault, pgsodium*, net, cron, supabase_*, _analytics, _realtime, _supavisor, ...): they follow the platform/CLI image version. Policies on storage.*, app triggers on platform tables, buckets and cron jobs are still captured.
  • Objects owned by an extension (e.g. pg_trgm's functions in public); the extension itself and its version are captured.
  • Publication membership of platform tables (realtime.messages_YYYY_MM_DD partitions rotate daily).

Expected residue between prod and a correct local stack: platform schemas and extensions that only the CLI stack has (pg_graphql, pgjwt, pg_net), instance sizing settings, platform role settings, newer hosted storage tables, the local dev: upload user assets policy and pgrst.db_max_rows on the local authenticator role. See REPORT-2026-10-28.md.

Generated types #

scripts/generate-db-types.mjs regenerates supabase/database.types.ts and drizzle/ from one database (local by default, --prod for production, DB_TARGET=fresh for the throwaway db) and canonicalizes the output (public schema only, extension functions and non-public enums removed, drizzle output sorted by name), so the same schema always yields byte-identical files. --check fails when the result differs from the committed files.