Supabase Workflow¶

End-to-end procedures for Rose developers and coding agents. Read the relevant section before acting; agent-specific permissions remain in supabase/AGENTS.md.

Production boundary: test, staging, and production environment labels share Supabase rows. Only local databases and explicitly provisioned preview branches isolate writes. Verify the database target before applying migrations or seeding.

Pick your environment¶

flowchart TD A[What are you doing?] --> B{Frontend-only change against stable schema?} B -->|Yes| C[Local Supabase <code>just dev</code>] B -->|No| D{Touches migrations, RLS, RPCs, MVs?} D -->|No| E{Reproducing a real client bug?} D -->|Yes| F[Preview branch <code>./bootstrap.py --branch</code>] E -->|No| C E -->|Yes| G[Local or branch + <code>just *-copy</code> seed]

Target	Command to start	When
Local Supabase (Docker)	`cd supabase && just dev`	Frontend dev, isolated tests, no prod credentials needed.
Preview branch (remote)	`./bootstrap.py --branch`	Migrations / RLS / RPC / MV work. Real Postgres, isolated DB, auto-deleted on PR close.
Staging	Already wired via `frontend/.env.staging`	Final integration testing before merge.

You never need to develop against production directly.

Git ↔ Supabase branch mapping¶

Git branch	Supabase branch	Project ref	Role
`main`	`main` (default)	`drtzxyuvppalvgczwhne`	Production. Git-triggered migration deployment depends on the Supabase GitHub integration.
`develop`	`develop` (persistent)	`ziptmcxknuouujrjzanz`	Test database. Has its own migration history; persistent branches are never reaped. Never delete and recreate it: keep its project ref and rebuild it in place (see IX-5331). No running service connects to it; the test backend and R4A test services use production.
`feature/*`	per-PR preview	per branch	Isolated scratch DB, deleted when the PR closes.

Automatic migration deployment on pushes to main or develop is configured outside this repository, through the Supabase GitHub integration. Verify its branch mapping, deployment status, and the target database's migration history before relying on it. Its Working directory must be . (the folder that contains supabase/): set to supabase, it finds no migration files, logs All migrations are up to date. and applies nothing from git (IX-5333). A run's workdir is in GET /v1/projects/<ref>/actions/<id>.

Two gates stop a broken migration. PR Checks - Supabase applies every file in supabase/migrations/ to a fresh local database and runs the SQL tests (supabase/scripts/test_sql.sh) on every develop PR that touches supabase/; the release workflow reruns it. The main ruleset requires the integration's Supabase Preview check, so a release PR cannot merge while the latest migration run on develop failed (repository admins can bypass it for an emergency). Neither applies migrations to production. A successful GitHub release alone is not evidence that the schema was deployed.

A develop merge never writes production: .github/workflows/supabase-branch-cleanup.yml only deletes the PR's preview. Production receives migrations with the main release, or through a deliberate operator push. IX-5148 adds a gated release step in front of it.

supabase db push records successfully applied versions in the target database's supabase_migrations.schema_migrations; subsequent pushes skip those versions. SQL run directly through the SQL editor or psql does not record that history. See operator production pushes for the supported direct-push procedure.

Supabase initially provisions a preview from production's recorded migration SQL. Our scripts/supabase_branch.py ensure helper then rebuilds fresh previews from the checkout's supabase/migrations/ files, replacing that initial schema and history. Production-only changes absent from those files do not survive this rebuild. Keep applied migration files unchanged and commit them; use new migrations for corrections.

Standard workflows¶

A. Pure frontend or backend change (no schema work)¶

# Start once
cd supabase && just dev      # Boots local Postgres + Auth + Studio at 127.0.0.1:54323
just seed-local   # Synthetic data (or seed-local-copy for real)

# Develop
cd ../frontend && just dev
cd ../backend
just dev test

Login as admin@admin.com / admin. See Supabase Seeding for the data set you'll see.

B. Schema change (migration, RLS, RPC, materialized view)¶

# 1. Spin up an isolated branch DB
./bootstrap.py --branch

# 2. Write the migration
date +%Y%m%d%H%M%S       # generate timestamp
$EDITOR supabase/migrations/20260520143000_my_change.sql

# 3. Apply it to the branch DB
cd supabase
set -a; . ../backend/.env.local; set +a
DB_URL="postgresql://${POSTGRES_USER}:${POSTGRES_PASSWORD}@${POSTGRES_HOST}:${POSTGRES_PORT}/${POSTGRES_DB}"
supabase db push --db-url "$DB_URL" --include-all --yes
# OR shortcut that also re-seeds + refreshes MVs:
just seed-branch

# 4. Regenerate types from this preview's project ref
# Follow "Generated types after migrations" below, then commit the generated file.

# 5. Type-check + test
cd ../backend && just mypy <changed-files>
cd ../frontend && just lint

After merging into develop, verify the test database's migration deployment and history through the configured Supabase integration. The PR's preview branch is deleted; production receives the migration only with the main release (see Git ↔ Supabase branch mapping). Do not infer database deployment from the Git merge or application release alone.

After a main release, confirm production applied it: the Supabase integration's run log for branch main (GET /v1/projects/drtzxyuvppalvgczwhne/actions, then /actions/<id>/logs) shows Applying migration <version> or All migrations are up to date., and production's supabase_migrations.schema_migrations records the version.

C. Reproducing a real client bug¶

The synthetic data won't match real production shapes. Use the *-copy seed path:

# On a preview branch (recommended — isolated, won't pollute local DB)
./bootstrap.py --branch
cd supabase && just seed-branch-copy --clear

# OR against local Supabase
cd supabase && just seed-local-copy --clear

Requires backend/.env.production (downloaded by just download-env all). The copy is ~60 s and contains real visitor IPs / emails / chat content — treat the resulting DB as sensitive.

D. Editing a materialized view or RPC¶

Before replacing an existing object, inspect its live definition:

-- Inspect the CURRENT shape on develop (the source of truth):
SELECT pg_get_viewdef('public.mv_client_stats_30d'::regclass);
SELECT pg_get_functiondef('public.get_account_stats_with_intent'::regproc);

Then write a migration that drops + recreates (or uses CREATE OR REPLACE only if the signature is identical — Postgres rejects return-type changes via CREATE OR REPLACE alone, see 20260318103855 for the fix pattern).

MVs are populated by triggers / cron in production. On a fresh branch, the synthetic seed refreshes them in dependency order (mv_widget_visitors → mv_conversation_visitors → mv_client_stats_30d). If you add a new MV, append its REFRESH to supabase/justfile in the seed-local and seed-branch targets.

E. Operator production push¶

A deliberate human supabase db push from local migration files is compatible with later automated pushes: the same versions are already recorded in production and are skipped there. This is an operator exception to the usual preview-first workflow; it does not authorize an agent to apply production migrations.

Verify that the linked project is production (drtzxyuvppalvgczwhne) and that the migration works with the application currently running there. Avoid overlapping production migration deployments.
From the repository root, inspect supabase migration list --linked and supabase db push --linked --dry-run. The pending list must contain only the intended changes. Reconcile missing local files before proceeding; do not rewrite production history to bypass a mismatch.
The operator runs supabase db push --linked, then checks supabase migration list --linked and verifies the affected database objects.
Commit the exact files, preserving their timestamp versions and SQL, and merge them into develop and main before subsequent automated deployments. Verify they apply successfully to test and fresh previews: a production push does not update those databases. Include generated types when needed.
Make any correction in a new migration. Editing or renaming an applied file makes production history diverge from what test and previews execute.

For versions already in production but missing locally, follow externally applied migration history.

Don'ts¶

Don't make untracked production schema changes

Use the preview workflow by default. Deliberate human production pushes must follow operator production pushes; agents remain bound by supabase/AGENTS.md.
Do not substitute SQL editor or psql execution for supabase db push: they do not record migration history.
Never supabase migration repair against prod from your laptop.
Never edit an existing migration without permission, including changes intended only to fix fresh replays. Prefer a new migration. Edits to a migration production has run are refused in CI (see Baseline and archived migrations).

Don't seed against the wrong target

The 5 seed targets are easy to confuse. Quick reference:

Command	Hits
`just seed-local`	Local Supabase :54322
`just seed-local-fresh`	Local Supabase :54322 (`--clear` first)
`just seed-local-copy`	Local Supabase :54322
`just seed-branch`	Whatever's in `backend/.env.local` (your preview branch)
`just seed-branch-copy`	Whatever's in `backend/.env.local` (your preview branch)

Debugging¶

seed.sql didn't run on this DB. Either you skipped just seed / just seed-branch, or the branch is stuck in CREATING_PROJECT / MIGRATIONS_FAILED. See Common errors.

Magic link emails are rate-limited or not delivered¶

For hosted Supabase Auth, Rose uses Cloudflare Email SMTP, not Supabase's built-in email service. Verify Supabase Dashboard → Authentication → SMTP has custom SMTP enabled with the Cloudflare credentials, and Authentication → Rate Limits has Emails sent set to 30/hour.

For local Docker Supabase, emails go to Inbucket at http://localhost:54324. Local Inbucket delivery does not prove that hosted Cloudflare SMTP is configured correctly.

"Access Restricted: admin@admin.com does not have access"¶

Browser holds a stale JWT signed by a previous (deleted) branch's secret. Clear site data for the backoffice origin and log in again.

Backoffice pages show zero data¶

Conversations / Visitors / Accounts empty → check the environment filter (default production) and workspace selector. Synthetic data is on the 4 fake domains (acme.com, contoso.com, fabrikam.com, demo.local).
Home dashboard tiles show 0 → materialized views aren't refreshed. Re-run just seed-branch (or REFRESH MATERIALIZED VIEW manually via Studio in the dependency order documented in seeding).

RLS denying queries you expect to work¶

Most tables gate on has_domain_access(site_domain). Verify:

SELECT has_domain_access('acme.com');   -- should be true for admin@admin.com

Admins bypass via is_backoffice_admin(). Non-admins need a row in backoffice_user_domains. The synthetic seed grants user@user.com access to all 4 fake domains.

Cheat sheet¶

# Create / refresh isolated branch DB
./bootstrap.py --branch

# Seed (all idempotent)
cd supabase
just seed-local                 # local synthetic
just seed-local-fresh           # local synthetic + --clear
just seed-local-copy            # local prod copy
just seed-branch                # branch synthetic (also pushes missing migrations, refreshes MVs)
just seed-branch --clear        # branch synthetic + --clear
just seed-branch-copy           # branch prod copy

# Branch lifecycle CLI
python3.12 scripts/supabase_branch.py ensure
python3.12 scripts/supabase_branch.py delete <git-branch>
python3.12 scripts/supabase_branch.py reap --dry-run

# Inspect current branch DB
set -a; . backend/.env.local; set +a
DB_URL="postgresql://${POSTGRES_USER}:${POSTGRES_PASSWORD}@${POSTGRES_HOST}:${POSTGRES_PORT}/${POSTGRES_DB}"
psql "$DB_URL"

# After any migration, follow "Generated types after migrations" below.

Inspect existing objects¶

When recreating an existing object (materialized view, function, table), the live remote schema is the only source of truth — query it via the Supabase MCP before drafting the new definition. Migration files lie: an object can be reshaped by ALTER migrations that don't mention the original name, by hotfixes applied directly, by migrations not yet merged, or by ones that drop+recreate it across several files. Rolling back to a stale shape silently breaks dependent functions/views (e.g. dropping a column the function still references). MCP project: drtzxyuvppalvgczwhne.
Inspect applied migration history: mcp__supabase__list_migrations.
Function source: SELECT pg_get_functiondef(oid) FROM pg_proc WHERE proname = '<name>';
Table/view/MV columns: SELECT attname, format_type(atttypid, atttypmod) FROM pg_attribute WHERE attrelid = '<schema.name>'::regclass AND attnum > 0 AND NOT attisdropped ORDER BY attnum;
Materialized view body: SELECT pg_get_viewdef('<schema.name>'::regclass);
Trigger / index / constraint: pg_trigger, pg_indexes, pg_constraint.
No MCP access? Block — don't reconstruct from migration files. Authenticate first (mcp__supabase__authenticate) or ask the operator to paste the live definition. A name grep over supabase/migrations is an unreliable last resort only: it misses ALTER reshapes, direct prod hotfixes, and unmerged migrations, and the file named after the object is usually its first, stalest definition (this rolled back a current experiments-table-sourced RPC to a config-sourced one whose source row had been deleted).

Use migration files as templates after you know the current shape from the live DB — never as the authority on it.

Don't hand-retype the body either: hand-reproduction strips historical comments and invites transcription errors. Verify the latest migration file matches live (md5(pg_get_functiondef(oid)) vs the file's function block), then assemble the new migration as targeted string replacements on that verified text — the diff against source is then exactly your intended change.

Migration authoring and replay¶

Before editing SQL, inspect existing objects above and the security rules. Write migration files locally; apply/test only on an isolated local or preview database. Run the supabase-security skill on every changed migration, fix P1/P2 findings, and record any remaining P3/info findings in the review summary or authorized PR.

Do not delete production data in migrations. Avoid DELETE, destructive UPDATE, TRUNCATE, DROP TABLE, and DROP COLUMN against tables that can contain customer or analytics data. If data removal is genuinely required, put the destructive SQL in a separate operator-run script or PR note for a human to apply deliberately, not in the automatic migration path.
Back up rows before any unavoidable destructive SQL. If a migration must move, dedupe, or remove rows to satisfy a constraint, first copy the affected rows into a durable backup table in the same migration, then operate from that backup. Use a specific table name that includes the migration/version and source table, for example:
```
CREATE SCHEMA IF NOT EXISTS migration_backups;

CREATE TABLE IF NOT EXISTS migration_backups.m20260528_visitors_before_dedupe AS
SELECT v.*, NOW() AS backed_up_at
FROM public.visitors v
WHERE <exact destructive predicate>;
```
The backup predicate must match the destructive predicate exactly, and the migration/PR must state how to restore from the backup. Prefer reversible UPDATE ... FROM repairs over deleting rows.
Migrations must be idempotent. Re-running the same migration on a DB where it already applied must succeed without errors. Required patterns:
Tables: CREATE TABLE IF NOT EXISTS … (or DROP TABLE IF EXISTS first when shape changed — only when wiping data is acceptable).
Functions: CREATE OR REPLACE FUNCTION …. If RETURNS TABLE signature changes, prefix with DROP FUNCTION IF EXISTS <name>(<args>);.
Indexes: CREATE INDEX IF NOT EXISTS ….
Materialized views: DROP MATERIALIZED VIEW IF EXISTS … CASCADE; CREATE MATERIALIZED VIEW ….
Triggers: DROP TRIGGER IF EXISTS … ON …; CREATE TRIGGER ….
Policies: DROP POLICY IF EXISTS … ON …; CREATE POLICY ….
Cron jobs: wrap in DO $$ BEGIN IF EXISTS (SELECT 1 FROM cron.job WHERE jobname='…') THEN PERFORM cron.unschedule('…'); END IF; PERFORM cron.schedule(…); END $$;.
Constraints / columns: guard with DO + information_schema check or ALTER TABLE … ADD COLUMN IF NOT EXISTS ….
Idempotency is also what makes branch DBs + nightly seeding tractable; non-idempotent migrations break just seed-branch reruns.
A migration that uses an extension's functions must enable the extension itself — create extension if not exists <ext> with schema extensions; (or public when a function's search_path excludes extensions), before first use. Fresh DBs — preview branches, CI, supabase db reset — replay every migration on an empty Postgres, so an extension only enabled out-of-band on prod (a migration just assuming it exists) aborts mid-migration with function … does not exist and fails the whole seed. A later-timestamped migration can't fix it: language sql bodies validate at CREATE FUNCTION, so the create extension must sit in an earlier-named migration. (IX-3551: pg_trgm/similarity() in …4244_add_resolve_brand_function broke every factory bootstrap.py --branch run until …4243_enable_pg_trgm was added.)
A migration that CREATE OR REPLACEs an existing object must sort AFTER every committed migration touching that object. Fresh DBs (preview branches, CI, db reset) replay in timestamp order — a fix file named earlier than an already-committed redefinition of the same function is applied, then silently overwritten by it, so prod (incremental) and preview (full replay) end up with different definitions and preview "validation" tests the reverted code. Before finalizing, grep supabase/migrations for other files defining the same object and confirm yours has the latest timestamp (date +%Y%m%d%H%M%S); re-check after rebases. (IX-4055: fix stamped 08:31 was overwritten on replay by the pre-existing 09:21 perf migration redefining the same function.)
Dropping or replacing a named constraint/index? Grep earlier migrations for that name first. Fresh-DB replay aborts when their ON CONFLICT (cols) can no longer name it, or their unique index no longer holds against the data your change permits — invisible on prod, which never re-runs them. Guard the historical file; worked example 20260729171531_add_website_rescan_source_control_plane.sql.
A migration that reads a materialized view breaks every fresh DB. Matviews replay WITH NO DATA on preview branches / CI / db reset, so a baseline capture (CREATE TABLE … AS SELECT * FROM mv_*) errors and aborts the whole seed — guard on relispopulated. Worked example: section 2 of 20260828165954_ix4498_incremental_mv_widget_visitors.sql.
Statements that depend on an out-of-band role or superuser privilege can fail fresh replay. Supabase's initial provisioning replays production's stored supabase_migrations.schema_migrations.statements; editing a git file does not change those recorded statements. A bare GRANT … TO n8n_service, for example, can fail there if the role exists only in production (IX-5081). Our preview helper subsequently resets the application schemas and replays the git files as the branch's Postgres role, even if initial provisioning reported MIGRATIONS_FAILED. Its final schema therefore follows Git, not the stored production SQL. Guard role-specific statements from the start with DO $$ BEGIN IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname='…') THEN … END IF; END $$;. Keep applied files unchanged; any exceptional historical replay edit still requires permission and does not repair production's recorded SQL.

Generated types after migrations¶

Generate frontend/shared/src/types/database.generated.ts from the database where the migration was applied, then commit it alongside the migration. Missing schema types can make Supabase client calls resolve to never.

Production is the default. From frontend/, use the usual command or select an isolated database containing your unapplied migration. For a preview, obtain that preview's project ref from the Supabase dashboard:

# Usual workflow: production schema
just gen-types

# Docker-local database
just gen-types local

# Hosted preview database (replace the placeholder)
just gen-types '<preview-project-ref>'

The default project is drtzxyuvppalvgczwhne; .env.local does not override the selected target. The recipe generates and formats into a temporary directory; failed or empty output leaves the existing types intact. Only successful, nonempty formatted output replaces the checked-in file.

Stale branch: a database ahead of your checkout can introduce unrelated schema drift. If the diff dwarfs your migration, update from develop and regenerate against the intended target. If generation must be deferred, record that explicitly.
Preview behind production: a preview branch replays migrations only, so objects that exist in production out of band are absent and regenerating from it deletes their types. Keep the checked-in file and copy in only your migration's new entries from the preview output (IX-5063).
Unapplied migration: generation cannot see a change absent from the target. Prefer applying it to an isolated local/preview database first. For a small exact addition such as an enum member, a temporary hand-patch must match the migration and be reconciled with the next regeneration.
After rebasing a hand-patch: check for duplicate schema keys even if Git reports no conflict. Reconcile with develop's generated version while preserving your migration's additions; regenerate from the correct database when available.

Alias views over renamed concepts¶

When a product term replaces a physical name that live code depends on, do not rename the table. Add the new name as a view and move readers to it one at a time (public.workspaces / public.workspace_roots over public.sites / public.site_roots, 20260917181817_ix5063_workspace_alias_views.sql). The expand step is then purely additive, and a later physical rename is invisible to application code.

WITH (security_invoker = true), or the view bypasses the base table's RLS.
One base table, no aggregates or DISTINCT: the view stays auto-updatable, so inserts, updates and deletes reach the base table and fire its triggers.
REVOKE ALL … FROM PUBLIC, anon, authenticated, then grant what the base table grants.
A view's column list is fixed at creation. A migration that adds a column to the base table must CREATE OR REPLACE VIEW the alias, appending the column last. supabase/tests/ix5063_workspace_alias_views.test.sql fails on drift.
A view cannot share its table's name, so a renamed column on a table whose own name is fine (knowledge.ingestion_operations.site_id) is aliased in the application model.
Generated types mark every view column nullable. Restore the base table's NOT NULLs at the call site with .overrideTypes<Row[], { merge: false }>().
PostgREST embeds resolve through the views using the base tables' foreign-key names (workspaces?select=…,clients!sites_client_id_fkey(…),workspace_roots(…)).

Baseline and archived migrations¶

Branches replay production's recorded schema_migrations.statements for every version production has, not the git files. IX-5159 collapsed everything up to 20260928172748 into supabase/migrations/20260928180000_baseline.sql, a dump of production's schema (roles, schema, default privileges, cron jobs) plus the reference rows the old migrations seeded (config definitions, the testfeatures fixture sites). The superseded files live in supabase/migrations_archive/ as history only: neither the CLI nor any test reads them. Plan and evidence: docs/plans/2026-09-29-ix-5159-baseline-migration.md.

Never edit or delete a migration production has run. Branches would keep replaying the recorded SQL while git says otherwise. supabase/scripts/check_applied_migrations.sh (in just lint-migrations and PR checks) refuses such diffs against origin/main.
Never stub a missing version. A stub pushed anywhere becomes the SQL every future branch replays. When production has a version with no local file (pushed from a worktree that has not merged yet), just push borrows its recorded SQL for that push and deletes it afterwards. The worktree that pushed it still lands the file, unchanged.
An existing local volume keeps its old schema. When just dev prints Starting database from backup, it reused the Docker volume and applied no pending migrations, the baseline included. Check with select max(version) from supabase_migrations.schema_migrations;. To try one new migration without losing local data, apply that file alone: psql "postgresql://postgres:postgres@127.0.0.1:54322/postgres" -v ON_ERROR_STOP=1 -f <file>. supabase db reset rebuilds from the baseline but deletes local data.

Compare a database's schema with production¶

Run supabase/scripts/schema_fingerprint.sql on both databases and diff the sorted kind|name|md5 lines. Branch or local: psql "$DB_URL" -At -F '|' -f supabase/scripts/schema_fingerprint.sql. Production (read-only): POST the file's SQL to the Management API /v1/projects/drtzxyuvppalvgczwhne/database/query with "read_only": true. Expected differences: comments inside function bodies, platform-managed extension versions, and Supabase's own event triggers. Anything else is drift.

Recover externally applied migration history¶

When production records a version with no file on any branch, recover the real SQL rather than creating a stub: read its name and statements from supabase_migrations.schema_migrations with mcp__supabase__execute_sql (project drtzxyuvppalvgczwhne), save it as <version>_<name>.sql, and land it on develop. If MCP is unavailable, a read-only metadata dump works: supabase db dump --schema supabase_migrations --data-only --file .context/supabase-debug/supabase_migrations_dump.sql.

Do not run production migration repair to hide an orphaned version: the schema change remains, and rewriting the ledger loses its history. Resolve metadata access through the sanctioned MCP or the operator; the metadata dump above is not permission to acquire direct production credentials.

Heavy refreshes and index builds¶

These are deliberate operator procedures on the identified database. Coding agents must not use direct production credentials as a fallback for unavailable MCP access. For an already-open psql session, provide SQL and \i / \timing / \set ON_ERROR_STOP on, not another shell connection command. Give absolute \i paths or state the directory psql was started from.

Inline full-table rebuilds in migrations: bound lock_timeout + statement_timeout, never disable them. A migration that calls a mart-rebuild inline (SELECT analytics.refresh_experiment_results();, REFRESH MATERIALIZED VIEW …, any CREATE TABLE AS scanning session_events) exceeds the connection's statement_timeout on prod-sized data → SQLSTATE 57014. Do NOT fix it with statement_timeout = 0 — the rebuild's blue-green swap takes ACCESS EXCLUSIVE on the live experiment_* marts, and unbounded means it can queue behind a live reader and block everything behind it. Instead, at the top of the file (plain SET, not SET LOCAL — must survive whether db push uses one transaction or one per statement): SET lock_timeout = '5s'; (swap fails fast under contention, migration aborts cleanly, re-run when idle) + SET statement_timeout = '15min'; (generous ceiling for the scan, not infinite). A SET inside the function's own CREATE FUNCTION … SET … clause does not help — the timer is armed by the caller's statement, so the guard must precede the SELECT. Better still: keep the heavy rebuild OUT of the migration — DDL only (sub-second locks), rebuild via the cron / out-of-band — unless a same-migration RPC depends on the new mart columns (IX-3534's get_experiment_sanity() does, so it stays inline but bounded).
PostgREST runs every request under authenticator (statement_timeout=8s; anon 3s) — service_role carries no override. A 57014 from a job at volume is usually contention, not an oversized batch: the mart refresh crons starve a normally 1-3s request past the cap. Current worst offenders (7d, cron.job_run_details): refresh-mv-device-stats-daily at 04:15 (avg 443s, max 7m41s) and refresh-mv-conversation-visitors at :00 (avg 46s). refresh_mv_widget_visitors_safe used to be the big one — a full REFRESH measured at 248s in IX-4199 and 17min by IX-4498 — but it is incremental since IX-4498 and now runs ~12s, so a :30 collision is no longer a plausible cause. Check what else was running before shrinking the batch — a job whose chunk sizes are constants gets a shorter run from a lower batch limit, not a shorter statement. (IX-4199 replay: passes died at :00, survived off the hour.)
CREATE INDEX CONCURRENTLY cannot be built through the Supabase SQL editor / API on a large table — the ~60s platform gateway kills it mid-build and leaves an INVALID index shell. Run the scripts/backfill/*_concurrently.sql operator script via psql over the Direct connection string (Dashboard → Project Settings → Database → Connection string → Direct, db.<ref>.supabase.co:5432, reveal/reset the password there) — raw Postgres, no HTTP gateway, statement_timeout='60min'. The pooler creds in backend/.env.production don't authenticate from a dev box. The scripts already drop any INVALID shell before rebuilding. Hand the operator the expected wall-clock and pg_stat_progress_create_index with the script.
A materialized view created WITH NO DATA reads as empty (all-zero cards, blank charts) until its first REFRESH — and that first full build cannot run through the Supabase SQL editor / MCP (~60s gateway cap kills it). A migration that ships CREATE MATERIALIZED VIEW … WITH NO DATA (lock-light DDL) leaves the populate as an operator step; merging the migration to production does not fill the mart. The 4h refresh cron does not self-heal it fast either: the first build is a full scan, and CONCURRENTLY is invalid on a never-populated MV (falls back to a plain REFRESH bounded by the cron's statement_timeout, e.g. 15min — if the build exceeds that it fails silently every cycle and the mart stays empty forever). Symptom seen: IX-3593 — mv_conversation_facts (#1416) shipped empty, Home Conversion funnel + journey returned zeros. Operator runs the first build via psql over the Direct connection (same string as the CONCURRENTLY note above — db.<ref>.supabase.co:5432, password from Dashboard → Project Settings → Database → Connection string → Direct):
```
PGPASSWORD='<direct-db-password>' psql \
 "host=db.<ref>.supabase.co port=5432 dbname=postgres user=postgres sslmode=require" \
 -c "SET statement_timeout = '60min';" \
 -c "REFRESH MATERIALIZED VIEW public.<mv_name>;"
```
Verify with SELECT count(*) FROM public.<mv_name>; (non-zero = populated). Then confirm the recurring cron's first auto-refresh finishes under its own statement_timeout; if not, raise that timeout or move to an incremental refresh.
To trigger a heavy cron-managed refresh function on demand (analytics.refresh_experiment_results(), mart rebuilds, etc.), run it directly via psql over the Direct connection — NOT the SQL editor, NOT an every-minute cron. The function is SECURITY DEFINER with EXECUTE revoked from anon/authenticated/PUBLIC, so only the owner runs it; the multi-minute run also blows the SQL editor / MCP ~60s gateway. Two wrong instincts to avoid: (a) a plain SELECT analytics.refresh_experiment_results(); in the SQL editor → gateway kills it mid-run; (b) a cron.schedule('oneshot','* * * * *', …) to "fire it once" → pg_cron does not guard against overlap, and a job that runs minutes will have the next minute's tick stack a second concurrent rebuild → ACCESS EXCLUSIVE swap contention / lock pile-up (the exact storm a rebuild causes). Right way — operator runs as owner over the Direct connection (db.<ref>.supabase.co:5432, password from Dashboard → Project Settings → Database → Connection string → Direct), synchronous, full budget, no gateway, no overlap:
```
PGPASSWORD='<direct-db-password>' psql \
 "host=db.<ref>.supabase.co port=5432 dbname=postgres user=postgres sslmode=require" \
 -c "SET statement_timeout = '45min';" \
 -c "SELECT analytics.refresh_experiment_results();"
```
psql returning the result row with no error = the run finished; the wall-clock it blocked is the real run time. If a one-shot cron is genuinely the only option (no Direct access), schedule a SINGLE specific upcoming minute (MM HH * * *), let it fire once, then cron.unschedule it before it can repeat — never * * * * *.