Skip to main content

Documentation Index

Fetch the complete documentation index at: https://docs.encoreos.io/llms.txt

Use this file to discover all available pages before exploring further.

Version: 1.3.0
Last Updated: 2026-04-24
Purpose: Canonical guide so developers and AI agents know exactly when to use the Supabase CLI, how to run it, and how to test migrations locally. Reduces “we keep running into this” confusion.

​
Migration squash (2026-02-11)

On 2026-02-11 the project squashed 645 migrations into two files:
  1. Baseline DDL: 20260211182655_6aeefd1c-c5cc-4eaf-98b0-3e38abc7ba6a.sql (full schema)
  2. DML companion: 20260211222508_post_squash_dml.sql (cron jobs, storage buckets, picklist seed data)
Rollback point: git tag pre-squash-v1. After the squash, npx supabase db reset applies only these two migrations (much faster). See After a migration squash for syncing with remote.
For AI agents: Migrations and local DB operations are CLI-only. Never use Supabase MCP to create or apply migrations. If you suggest supabase start or supabase db reset, first remind the user that the Supabase CLI must be installed and on PATH; if not, link to Install the Supabase CLI.

​
When to Use CLI vs MCP

I need to…UseDo NOT use
Create a migration file (DDL)Declarative: edit supabase/schemas/**/*.sql, then CLI npx supabase stop and npx supabase db diff -f <name> (see Declarative Schema Workflow). Legacy: npx supabase migration new <name> for caveat-only SQL.MCP (no create-migration)
Apply migrations locallyCLI npx supabase start then npx supabase db reset (or supabase start / supabase db reset if on PATH)MCP
Push migrations to remote (staging/production)CLI npx supabase link --project-ref <id> then npx supabase db push (or supabase if on PATH)MCP
Generate TypeScript typesCLI npx supabase gen types typescript --local (or supabase gen types if on PATH)MCP optional
Start/stop local SupabaseCLI npx supabase start / npx supabase stop (or supabase start / supabase stop if on PATH)MCP
Read project URL, list branches, list tablesMCP (e.g. mcp_supabase_get_project_url, mcp_supabase_list_migrations)—
Search Supabase docsMCP mcp_supabase_search_docs—
Rule of thumb: If it changes schema or runs migrations → CLI. If it only reads or looks up docs → MCP.

​
Ensure Supabase CLI Is Installed

The Supabase CLI is required for local migration testing and for pushing to remotes. It is not bundled with the repo. If you prefer not to install globally, use npx supabase for all commands (see Running the CLI below).
  1. Install (optional): Follow Install the Supabase CLI (e.g. npm: npm install -g supabase, or Scoop/Homebrew on Windows/Mac).
  2. Verify: From the project root run: 
    supabase --version
    If the command is not found, the CLI is not on PATH or not installed.
For AI agents: When suggesting any supabase command, if the user reports “command not found” or similar, suggest: run with npx supabase from the project root, or install the CLI and add to PATH; link to the official install guide above. CLI version floor: Use Supabase CLI 2.77.0 or newer for branch and Edge Function deploys. Older versions or mismatched runtimes can cause:
  • Branch-action failures (e.g. lockfile parse errors).
  • Repeated deploy warnings: “Specifying decorator through flags is no longer supported. Please use deno.json instead.” (fixed in supabase/cli#4599; CLI 2.77.0+ no longer passes deprecated Deno 2 flags).
Check with npx supabase --version; update with supabase update if installed globally, or use npx supabase to get the version bundled with the project. Before bulk function deploy, run the deploy preflight to validate CLI version.

​
Running the CLI

  • Option A (no install): From the project root, run npx supabase <command> (e.g. npx supabase start, npx supabase db reset). Uses the CLI via npx; no global install or PATH change.
  • Option B (install): Install the CLI globally and add to PATH; then run supabase <command> (see install link). Prefer Option A if the team does not want global tools.

​
Edge Functions: Lockfile and Deploy Compatibility

Branch sync and Edge Function deploys run in Supabase’s cloud environment, which uses a Deno runtime that does not support lockfile version 5 (generated by newer Deno 2.x). To avoid Unsupported lockfile version '5' and Failed reading lockfile at '/app/supabase/functions/deno.lock' during branch actions or supabase functions deploy:
  1. Lockfile policy: The repo uses no committed lockfile for Edge Functions. supabase/functions/deno.json has "lock": false so the bundler does not require or parse a lockfile. Do not commit supabase/functions/deno.lock; if you regenerate it locally for development, add supabase/functions/deno.lock to .gitignore so it is not pushed.
  2. Preflight before branch sync or bulk deploy: From project root run: 
    npx supabase --version
    Ensure CLI is 2.77.0 or newer. Then confirm supabase/functions/deno.json contains "lock": false and that supabase/functions/deno.lock is either absent or gitignored.
  3. Deploying functions: Link to the target project (branch or main), then deploy. For bulk deploy with parallel jobs use --use-api --jobs 4; if you hit rate limits (429), retry after a short delay or deploy in smaller batches.
See SUPABASE_MULTI_ENV_SETUP.md for staging/production project refs and branch workflow.

​
Edge Functions deploy preflight

Before running supabase functions deploy (or bulk deploy), run the version check so deploys do not emit decorator deprecation warnings: 
npm run supabase:deploy:preflight
Or from project root: npx tsx scripts/supabase/deploy-preflight.ts. The script exits with code 1 and prints upgrade instructions if the Supabase CLI is below the required version (2.77.0). See scripts/supabase/deploy-preflight.ts.

​
Expected output during db push and functions deploy

These INFO lines are expected and not errors; no config change is required:
  • “No buckets found. Try setting [storage.buckets.name] in config.toml.” — The project creates storage buckets via migrations (e.g. storage.buckets INSERTs in supabase/migrations/), not via config.toml. You can ignore this.
  • “Seeding data is disabled in config.toml.” — Remote (staging/production) seeding is intentionally disabled; local seeding uses [db.seed] in config.toml for supabase db reset only.
If you see repeated “Specifying decorator through flags is no longer supported. Please use deno.json instead.” during supabase functions deploy, your CLI is older than 2.77.0. Run npm run supabase:deploy:preflight and upgrade the CLI (e.g. supabase update or use npx supabase).

​
Creating migrations (avoid duplicate timestamps)

  • Prefer: Use npx supabase migration new <name> (or supabase migration new <name>) so the CLI assigns a unique timestamp. Do not create two migration files with the same timestamp; the DB tracks migrations by version (timestamp) and duplicate keys cause apply failures.
  • If you must add a file manually: Use a unique YYYYMMDDHHMMSS prefix; e.g. list files in supabase/migrations/ and pick a timestamp that does not already exist.

​
Local Migration Workflow (Before Committing)

Use this sequence before committing new or changed migration files:
  1. Start local stack (if not already running): 
    npx supabase start
    (Or supabase start if CLI is on PATH. Requires Docker. First run may download images.)
  2. Apply all migrations to the local DB: 
    npx supabase db reset
    (Or supabase db reset if CLI is on PATH.) Base seeds run automatically (see supabase/config.toml [db.seed]), creating test users for E2E. Use supabase migration up only if you already have a running local DB and want to apply just pending migrations.
  3. Validate migration file(s): 
    npm run validate-migration -- --latest
    Or: npx ts-node scripts/database/validate-migration.ts --latest
  4. Run RLS tests: 
    npm run test:rls
  5. Then commit and push. Use npx supabase db push (or supabase db push) only when you intend to update a linked remote (staging/production), after local tests pass.
If you see “Remote migration versions not found in local migrations directory” when running supabase db push, see SUPABASE_MIGRATION_SYNC_ERROR.md.

​
Declarative Schema Workflow (DDL + pg-toolbelt)

Canonical DDL for the product database lives under supabase/schemas/ as version-controlled .sql (desired end state). Migrations under supabase/migrations/ remain what Supabase applies in CI and remotes; for normal DDL they are generated with the Supabase CLI diff, not hand-written. Assistive tooling: @supabase/pg-delta / @supabase/pg-topo (from pg-toolbelt) support export, optional apply-to-fresh-DB checks, and static ordering validation. They do not replace supabase db diff for migration generation.

​
Typical DDL change sequence

  1. Edit the appropriate .sql file(s) under supabase/schemas/ (or add new files; see DECLARATIVE_SCHEMA_GUIDE.md for layout and caveats).
  2. Optional: npm run db:schemas:validate — runs @supabase/pg-topo over the tree (skips if no .sql files yet).
  3. Stop the local stack so the shadow DB used by diff is clean: 
    npx supabase stop
  4. Generate a migration from the drift between declared schema and the migration-applied baseline (per Supabase CLI): 
    npx supabase db diff -f <descriptive_snake_case_name>
    Convenience (same steps wired): npm run db:schemas:diff -- -f <descriptive_snake_case_name>
  5. Review the new file in supabase/migrations/ (diffs can be large; watch for unintended drops).
  6. Start local stack and apply everything: 
    npx supabase start
npx supabase db reset
  7. Validate + test as in Local Migration Workflow: npm run validate-migration -- --latest, npm run test:rls.

​
Re-export schema from a running local DB

After npx supabase db reset (or when refreshing the declarative tree from the DB catalog): 
npm run db:schemas:export
Uses pgdelta declarative export with --integration supabase. Override URLs/paths with SUPABASE_LOCAL_DB_URL, SUPABASE_SCHEMAS_DIR if needed.

​
Known diff caveats (hand-written migrations still required)

Some changes are not reliably represented by schema diff alone (migra / CLI limitations). Keep these as explicit versioned migrations (see MIGRATION_LANES.md for DML lane rules), not only as declarative SQL:
  • DML (INSERT / UPDATE / DELETE) and one-off data repairs
  • View ownership / grants, SECURITY INVOKER on views, materialized views (and some view column type changes)
  • ALTER POLICY vs full policy recreate, column-level privileges, schema-level privileges
  • Comments (COMMENT ON), partitions, ALTER PUBLICATION … ADD TABLE, CREATE DOMAIN, duplicated GRANT noise from default privileges
Full checklist: DECLARATIVE_SCHEMA_GUIDE.md.

​
Do not

  • Hand-edit generated DDL in supabase/migrations/ for normal schema work (fix the declarative source and re-run db diff).
  • Run supabase db diff while supabase start is still running (stop first).

​
Debug Test Failures Locally (No Remote)

To debug test failures without touching the remote (no link, no push, no branches), use only the local Supabase stack. This avoids the “Remote migration versions not found in local migrations directory” error.
  1. Start the local Supabase stack (requires Docker): 
    npx supabase start
    Or supabase start if the CLI is on PATH.
  2. Apply all local migrations to the local DB: 
    npx supabase db reset
    This uses only files in supabase/migrations/ and does not contact the remote. Base seeds (organizations, sites, departments, users) run automatically per supabase/config.toml [db.seed], so test users (e.g. admin@test-org-alpha.example, password password) exist for E2E and RLS.
  3. Validate the latest migration (optional): 
    npx ts-node scripts/database/validate-migration.ts --latest
    Or add a script to package.json: "validate-migration": "ts-node scripts/database/validate-migration.ts" and run npm run validate-migration -- --latest.
  4. Run tests (RLS, unit, integration, etc.): 
    npm run test:rls
npm run test:unit
npm run test:integration
    Point the app at the local DB by using the env values printed after supabase start (e.g. DB URL, Studio URL). For RLS/unit/integration tests that use the test setup, ensure tests/setup.ts or your test env is configured to use the local Supabase URL and keys from npx supabase status if needed.
  5. Stop when done (optional): 
    npx supabase stop
Important: Do not run supabase link, supabase db push, or supabase branches when you only want to run tests locally. Those commands compare with the remote and can trigger “Remote migration versions not found in local migrations directory” if your local migration filenames don’t match the remote’s migration history.

​
Pushing to a Remote (Staging/Production)

  1. Link to the target project (e.g. staging): 
    npx supabase link --project-ref <project-ref>
    (Or supabase link if CLI is on PATH.) Refs: see SUPABASE_MULTI_ENV_SETUP.md.
  2. Push migrations: 
    npx supabase db push
    (Or supabase db push if CLI is on PATH.)
  3. If sync errors appear, resolve them using SUPABASE_MIGRATION_SYNC_ERROR.md before pushing again.

​
Connection Pooler Matrix

Use this matrix when wiring DB URLs in app/runtime config:
Connection targetTypical hostname patternPrepared statement settingNotes
Direct Postgresdb.<project-ref>.supabase.coAllowedUse for migrations, db push, and long-running admin operations.
Supabase pooler (transaction mode)aws-*.pooler.supabase.comDisable (prepared_statements=false or client pgbouncer=true)Required for pooled connections to avoid prepared statement protocol errors.
Session pooler / managed runtime poolprovider-specific pool endpointMatch provider guidanceIf uncertain, default to transaction-pooler-safe settings.
Guardrail: PR CI scans supabase/functions/** for pooled-connection anti-patterns and rejects changes that add prepare: true, bare SET (instead of SET LOCAL), LISTEN, or new pg.Pool(...) without an explicit max: cap (unless explicitly bypassed by label).

​
Pulling Dev drift into the repo (Lovable / MCP changes)

When schema changes were applied to the Dev project (zkgxozahyczcnzpwhbbf) directly via Lovable or the Supabase MCP server, capture them as a real migration in supabase/migrations/ instead of letting the repo lag. One-shot from a laptop (Docker required): 
# .env.local must export SUPABASE_DEV_PROJECT_ID, SUPABASE_DB_PASSWORD,
# and SUPABASE_ACCESS_TOKEN (use the Dev DB password and your personal token).
npm run db:pull-from-dev -- --name add_widget_table
The script:
  1. Runs the CLI version preflight.
  2. supabase link --project-ref $SUPABASE_DEV_PROJECT_ID --yes.
  3. supabase db diff --linked -s public,storage,auth -f <timestamp>_<slug> — emits a real migration when drift exists; exits cleanly when Dev is in sync.
  4. supabase db reset to verify the captured migration applies locally.
  5. Runs the same guards as CI (db:schemas:lint, check-rls-initplan, check-fk-indexes, check-migration-lane).
  6. If supabase/schemas/ is bootstrapped, runs npm run db:schemas:export so the declarative source of truth stays current (per DECLARATIVE_SCHEMA_GUIDE.md). Skipped with a console hint when the tree only has README.md.
  7. Regenerates src/integrations/supabase/types.ts from the local DB. Types are written only after a successful gen types run, so a CLI failure won’t leave the file truncated.
Review the migration file, rename if the slug doesn’t describe the change well, and commit it alongside any refreshed supabase/schemas/**/*.sql and the regenerated types file. On a schedule: supabase-sync-from-dev.yml runs daily at 06:00 UTC and on workflow_dispatch. It performs the same flow on the runner and opens a PR to dev with the captured migration.

​
Forward Push + Lineage Reconcile Ops

  • Forward push gate (dev -> production PRs): supabase-promote-pr.yml now enforces lane checks plus promotion lineage parity (scripts/database/check-promotion-lineage.ts) before release PRs merge.
  • Catalog parity monitor (prod reality drift): db-prod-catalog-parity.yml runs nightly and opens/updates an issue when committed prod catalog snapshots diverge.
  • Lineage reconcile runbook (manual, approval-gated): db-prod-lineage-reconcile.yml can repair migration history to applied up to a cutoff timestamp for prod or dev. It requires the db:approved-lineage-reconcile label on the referenced PR and enforces a post-repair pending threshold via dry-run.
  • Local mirror command: npm run db:pull-from-dev -- --name <slug> now auto-loads .env.local / .env when present, but still requires these vars:
    • SUPABASE_DEV_PROJECT_ID
    • SUPABASE_DB_PASSWORD (Dev DB password)
    • SUPABASE_ACCESS_TOKEN

​
Quick Reference for Agents

  • Creating migrations: CLI only. npx supabase migration new <name> (or supabase migration new <name> if CLI on PATH). Use the CLI so the timestamp is unique; avoid manually adding a file with a duplicate timestamp.
  • Testing migrations locally: CLI only. Run npx supabase start (or supabase start) then npx supabase db reset (or supabase db reset) → npm run validate-migration -- --latest → npm run test:rls.
  • Deploying migrations to remote: CLI only. npx supabase link then npx supabase db push (or supabase if on PATH).
  • Reading state / docs: MCP is fine (list tables, list migrations, search docs).
  • Edge Functions / branch deploy: Keep "lock": false in supabase/functions/deno.json; do not commit deno.lock (see Edge Functions: Lockfile and Deploy Compatibility).
  • If user doesn’t have CLI: Suggest running with npx supabase from the project root, or install the CLI and add to PATH; link to Supabase CLI getting started.
  • Optional: Add supabase/seed.sql to run seed data after db reset; see supabase/seeds/README.md.

​
Optional: seed data after reset

If supabase/seed.sql exists, it runs automatically after supabase db reset (or npx supabase db reset). The repo can include a seed.sql that includes the contents of supabase/seeds/ (base then modules) so local resets load test data. See supabase/seeds/README.md for order and guidelines.

​
After a migration squash

When migrations have been squashed locally, the remote (staging/production) still has the old migration history (e.g. 645 applied versions). To sync:
  1. Option A – Repair remote history (recommended when you must keep the same project):
    • Link: npx supabase link --project-ref <staging-or-prod-ref>
    • Mark all old migration versions as reverted:
      npx supabase migration repair --status reverted <timestamp> for each old timestamp.
    • Mark the squashed baseline as applied:
      npx supabase migration repair --status applied 20260211182655
    • Mark the DML companion as applied:
      npx supabase migration repair --status applied 20260211222508
    • Repeat for each remote (staging, then production). Scripting the repair loop for many timestamps is recommended; see Supabase docs for migration repair.
  2. Option B – New project / branch:
    • Create a new Supabase project or branch and run npx supabase db push so only the two squashed migrations are applied. Then point the app at the new project/branch.
  3. Team coordination (after squash lands on main):
    • Notify all developers: run git pull and then npx supabase db reset (only two migrations will apply).
    • Anyone with open feature branches that added migrations should rebase onto the new main and resolve conflicts by keeping only the new migration files (delete references to old migration filenames).
    • Optionally clean up local stashes or branches that reference the pre-squash migration set; the rollback point is git tag pre-squash-v1 if needed.

​
Going-forward: squash cadence and DML naming

  • Squash cadence: Plan to squash again every major release or roughly every 100 migrations, whichever comes first, so db reset stays fast.
  • DML in migrations: When a migration contains standalone INSERT/UPDATE/DELETE (cron, storage buckets, seed data), name it with a hint so the next squash is easier to audit (e.g. _seed_, _data_, _cron_ in the migration name).
  • Periodic audit: Run scripts/migration/lint-migrations.ts periodically to track migration count and size.