Agent notes
Declarative schema files, layout, migrations and audit triggers.
Change supabase/schemas/**/*.sql, then run pnpm supabase:diff -f <name>. It runs pg-delta (supabase db schema declarative sync --no-apply --strict-coverage -s public,api,permdock,better_supabase,pgmq,billing,storage,realtime,auth), which needs [experimental.pgdelta] enabled = true in supabase/config.toml and works while the stack runs. --strict-coverage fails when an object in a listed schema has no schema file. Do not write migration SQL by hand, except for data migrations and the caveats below. A clean tree reports No schema changes found, and CI fails on any drift. Add a schema to the script in packages/supabase-runtime/package.json when schema files start managing objects in it.
supabase/config.toml sets [experimental] stack = true. Do not add port keys back to the config, and do not hardcode 54321, 54322 or 54324 in scripts or live tests; read endpoints from readLocalSupabaseEnv() in @workspace/foundation/local-supabase or from supabase status --env --output-format text. Local start and status have no -o flag (use --output-format json or --env), and supabase stop --no-backup is supabase stack destroy. The engine refuses to start with Auth email templates that use content_path, a custom edge_runtime.deno_version, api.tls or the OrioleDB experimental.s3_* keys, so email templates go under [remotes.main] and [remotes.develop] only. Never pass realtime to supabase start --exclude: without the service there is no realtime.messages table, and the init migration fails on its topic policies. Services are lazy, so keeping one in costs nothing until it is requested.
supabase/schemas/ has one folder per domain and no numbered files:
extensions.sql at the root, and shared/ for the cross-domain types, the organization number sequences and the schema-level grants in shared/grants.sql.tenant, identity, crm, catalog, work, people, commerce, expenses, conversations, ai, automation, platform, portal).commerce/document_parties.sql). The table file holds the table, its indexes and comments, its track_updated_at and track_actor calls, its triggers including bs_audit, alter table ... enable row level security and its policies.<domain>/types.sql holds the domain's enums, <domain>/functions/<area>.sql its functions, and <domain>/storage.sql its bucket policies.better_supabase/module_*.sql is written by pnpm supabase:blocks, and identity/permdock/helpers.sql and hook.sql by permdock:generate. identity/permdock/grants.sql holds CentraKit's own service_role grants on the PermDock functions.pg-delta orders every statement by dependency, so supabase/config.toml has no schema_paths list and file names do not set the run order. Define each function in one file only. After any move or rename, pnpm supabase:diff must report no schema changes.
The modules CentraKit uses are listed in sql.modules of better-supabase.config.ts. better-supabase sql add <module> and sql sync write them into supabase/schemas/better_supabase/ and their pgTAP files into supabase/tests/database/. Never edit those files; change the module's entry in the config and run pnpm supabase:blocks, which syncs the files and writes the modules' rows (better_supabase.modules, reserved slugs, role settings) into a data migration with sql data. The order for a change is: config, pnpm supabase:blocks (files), pnpm supabase:diff -f <name> (schema migration), pnpm supabase:blocks again (data migration stamped after it). CI runs supabase:blocks:check, which fails on a stale module file or a module behind its version.
A table with updated_at gets select better_supabase.track_updated_at('public.<table>'); in its table file, and a table with created_by or updated_by gets select better_supabase.track_actor('public.<table>'); (pass created_by => null or updated_by => null for a missing column). The actor trigger stamps both from auth.uid(), so services do not send createdBy or updatedBy on writes through the caller's connection; writes through the service role keep the values they send. Keep these calls static: pg-delta records the triggers they create.
better-supabase.config.ts passes authorizationProvider() from permdock/better-supabase as authorization, built from packages/identity/permdock.manifest.json and packages/identity/permissions.catalog.json; the modules call the PermDock helpers through it (sql.modules.access.model: "provider") and check their permission keys against the catalog. pnpm --filter @workspace/identity permdock:generate rewrites the manifest and permdock:check fails when it is stale.
Private Realtime topics are defined once with defineTopic in packages/core/src/realtime.ts (an access policy with the receive permission key at scope: "tenant", so the policy calls better_supabase.tenant_ids_with, and event payload schemas). Their realtime.messages policies live in supabase/schemas/conversations/realtime.sql, which realtime.policies in better-supabase.config.ts has pnpm supabase:blocks write from every topic packages/core/src/realtime.ts exports (supabase:blocks:check fails on drift): change a topic, run pnpm supabase:blocks, then pnpm supabase:diff. Never write a realtime.messages policy by hand. The inbox and chat broadcasts are realtime.triggers entries in the same config, which pnpm supabase:blocks writes into that file as one better_supabase.bs_broadcast_* function and trigger per table: each names its topic template, event and payload columns, with lookups for the channel thread (org:<id>:inbox, org:<id>:thread:<id>, org:<id>:chats). Never write a broadcast trigger function by hand; supabase/tests/database/inbox_broadcast.test.sql checks the topics, events and payloads the clients read. The notifications module sends org:<id>:notifications:<user>. The SaaS app subscribes through useTopic.
jsonb columns with a stable shape get a json entry in better-supabase.config.ts with an inline type and a JSON Schema schema; the jsonb-schemas module turns the schema into a pg_jsonschema check. tests/integration/cross-package/data/jsonb-schemas.test.ts fails when an entry drifts from the Zod schema the services parse with, so update both together. Prefer typed columns or a child table with position over a new jsonb column.
pg-delta does not plan objects in the extensions schema and does not write create extension for an extension that installs into its own schema, such as pgmq. better-supabase sql sync (and sql add and sql upgrade) writes every module extension that no migration creates into <stamp>_better_supabase_extensions.sql, stamped before the next schema migration, and sql data refuses to run while one is missing. Create the schema migration with pnpm supabase:diff -f <name> after it. CentraKit's extensions migration (pgmq, pg_jsonschema, pgcrypto, vector and citext) is stamped before the init, so a fresh reset has them before any schema migration needs them. Queues (pgmq.q_* and pgmq.a_*) are created at runtime by better_supabase.enqueue_job, never in schema files.
pgvector lives in extensions, where the vector-search module expects it. Qualify pgvector types and operators as extensions.halfvec and operator(extensions.<=>) in schema files.
An event trigger belongs to no schema, so pnpm supabase:diff (which passes a schema list) never plans one. When a module adds an event trigger, such as the audit module's bs_audit_forget_dropped, create that migration with pnpm exec supabase db schema declarative sync --no-apply -f <name> (no -s), check that it holds only the expected statements, and apply it as postgres. The <stamp>_event_triggers.sql migration was written that way, and CI runs that unscoped sync too.
A SQL function or trigger queues a job with perform better_supabase.enqueue_job('<queue>', <payload jsonb>, <delay>, <max attempts>, '<dedupe key>'); the function must be security definer because the jobs functions are granted to service_role only. The queue must be listed in packages/core/src/jobs.ts with its payload schema and have a handler in apps/api/lib/jobs/handlers.ts.
stripe schema belongs to the Stripe Sync EngineThe Stripe Sync Engine installs and upgrades the stripe schema with its own migrations (pnpm stripe:sync:migrate; supabase:reset and supabase:sync run it with demo rows). Never define stripe objects in schema files and never add stripe to the supabase:diff schema list. A function that reads it is plpgsql and returns nothing when to_regclass('stripe.<table>') is null, because the schema can be missing when migrations run; a language sql function or a view over stripe tables would fail to apply. platform/functions/billing_subscriptions.sql holds those readers, and billing.organization_plans (an unexposed schema) is the plan of every organization. public.organization_subscription reads the tenant's subscription through the block's owner-only better_supabase.billing_tenant_subscription(tenant). Platform-wide lists read the billing block's better_supabase.billing_platform_subscriptions(), billing_platform_invoices() and billing_platform_customers(), which carry the customer email, price metadata, finalized_at and updated_at, and join the plan catalog on stripe_price_id, falling back to the price metadata plan_key (platform/functions/billing.sql); the demo fixture in packages/stripe/fixtures/sync-engine-demo.sql links the catalog rows of its prices. pgTAP and the CI database job install the schema first.
pg-delta carries schema, table, column and function grants and revokes, comments and security_invoker on views into migrations, including the supabase_auth_admin schema usage and the grants on permdock.custom_access_token_hook(jsonb), which permdock:generate writes inline. Declare every grant in the file that owns the object or in shared/grants.sql. Never append grants to a generated migration.
Never write grant ... on all tables or on all routines in schema public. pg-delta has no dependency that places such a statement after the per-file revokes, so it re-widened 229 functions and two secret tables to anon and authenticated in an earlier init. The default privileges in Supabase projects already grant the API roles on new objects, and each file revokes what it must.
The default privileges give anon and authenticated their own EXECUTE grant on every new function, so revoke ... from public or from public, anon leaves the function callable by every signed-in user. Write revoke all on function public.f(<args>) from public, anon, authenticated; and then grant only the roles that call it: service_role for jobs and internal helpers, authenticated only when the app, a policy or a security invoker function calls it as the user. A trigger function gets the revoke and no grant. A security definer function that the API can reach checks the caller with the PermDock helpers before it reads or locks anything (permitted_organization_ids('<grant>'), member_organization_ids() for active staff, the row helpers for resource grants), and guards only signed-in callers ((select auth.uid()) is not null and ...) when triggers or service-role jobs also call it. Never match a storage path, customer id or other caller-supplied id without checking it belongs to the caller's organization. A table with RLS and no policies keeps the default grants, so give it an expose entry with authenticated: [].
PostgREST runs each request in its own transaction. When a service action writes several rows that must agree, such as a status change with its party snapshot, published version and status history, put the writes in one security invoker function and call it once (send_quote, send_invoice, accept_quote). RLS still applies to every statement inside, and the function locks the parent row with for update before it checks the status. A helper granted to authenticated that changes a document guards the document status itself, because the app is not its only caller.
pg-delta writes a column grant (grant update (first_name) on public.profiles to authenticated) before the table-level revoke all on table ... from authenticated that recreates the table privileges. Postgres drops the column privileges with that table-level revoke. The grants module writes column privileges as expose entries, so the generated init keeps them. When a migration rewrites table grants for profiles or organization_billing_customers, run pnpm supabase:diff once more and keep what it writes. supabase/tests/database/grants.test.sql checks the column grants.
alter default privileges in shared/grants.sql does not reach migrations. Hosted Supabase projects already grant the API roles on new objects in public, and the schema files grant every table and routine explicitly, so nothing depends on it.
pg-delta refuses a schema file that leaves rows in a table. Rows the database needs go in data migrations after the schema migration:
<stamp>_better_supabase_module_data.sql, written by better-supabase sql data: module versions, reserved slugs and role settings.<stamp>_reference_data.sql: the built-in system and organization roles and the currencies.<stamp>_integration_catalog.sql, _ai_model_settings.sql, _billing_plans.sql, _platform_settings.sql and _system_workflows.sql: the integration catalog, the AI model allowlist and defaults, the billing plans with their usage quotas, the platform settings and the published system workflows. Production runs no seeds, so it gets these only from migrations.<stamp>_permdock_seeds.sql, written by pnpm --filter @workspace/identity permdock:generate (--seeds-out supabase/migrations/). Run it after pnpm supabase:diff so the seeds migration sorts after the schema migration.Add new reference rows in a new data migration named after the set it extends (<stamp>_billing_plans.sql) with idempotent on conflict statements. Tests read these files with readMigration("<name>") from @workspace/test-fixtures/supabase-files, which concatenates every migration with that name oldest first, so never reference a migration by its timestamp. Seeds hold demo data only.
pnpm supabase:squash (packages/supabase-runtime/scripts/squash-migrations.sh) deletes supabase/migrations/ and writes it again from the schema files: the extensions, one init, the event triggers, the module data, the data migrations (each merged with its later increments) and the PermDock seeds. Run pnpm supabase:blocks first, then pnpm supabase:reset, pnpm supabase:types, pnpm supabase:test and pnpm supabase:diff, which must report no schema changes.
A squash replaces migration versions that hosted databases have already applied, and the GitHub integration then fails with relation already exists because it starts the new init on a database that has the old schema. Squash only together with a reset of every hosted database, in the order of docs/operations/database-reset.mdx. Never squash for convenience.
pg-delta orders statements by dependency and cannot see what a do block reads, so a block that loops over information_schema runs before the tables exist and creates nothing. Write generated objects out statically, such as one create trigger bs_audit per table file.
Define each function in exactly one file. With two definitions, the last one applied wins without any error.
A new table with organization_id and id gets a static bs_audit trigger in its table file whose JSON argument is its registration: create trigger bs_audit after insert or update or delete on public.<table> for each row execute function better_supabase.audit_row_change('{"ignore": ["updated_at"], "redact": [], "category": "data", "key_columns": ["id"], "target_type": "<table>", "event_prefix": "<table-with-hyphens>"}');. better_supabase.audit_settings() reads the argument, so no data migration is needed. Ignore timestamps, leases and search vectors; redact tokens, secrets and credentials. A table that must not be audited goes in sql.modules.audit.options.exempt in better-supabase.config.ts, which doctor BS315 reads. pnpm supabase:blocks writes one pgTAP file per audited table.
Every table the schema files create gets the restrictive bs_session_active policy from sql.modules.sessions.options.policies in better-supabase.config.ts, so a signed-out, banned or deleted user's token stops working at once. pnpm supabase:blocks writes the policies into the sessions module file; list a table in options.exclude only when it must stay readable without a session. rls.test.sql fails on a table without the policy.
The grants module writes every table's complete privilege set. sql.modules.grants.options.fromPolicies derives it from the table's permissive policies in the schema files (a role gets a privilege only when a policy for that command names it; to public counts for authenticated), so a new table needs no grant statement. A table whose privileges differ from its policies, such as an append-only log or a service-only table, gets an entry in expose in better-supabase.config.ts. Never write grant ... on table in a schema file; column privileges are expose entries too, such as "update(username, first_name)" for profiles. A client role without a privilege gets 42501 instead of an empty result, so a pgTAP check for a table it cannot reach uses throws_ok(..., '42501', ...).
Status columns are enums from the domain's types.sql, and keys are uuid. Document amounts are numeric(14, 2) and unit prices numeric(14, 4); amounts that mirror a payment provider are bigint columns ending in _cents. Dates end in _on (a date) or _at (a timestamptz).
The better-supabase ORM rejects an object whose keys are all unknown, but accepts a stale key next to a valid one ({ employeeId, retentionUntil: null }). After renaming a column, search the services for the old camelCase name and add a request test for each write that touched it.
pnpm supabase:types runs better-supabase gen with better-supabase.config.ts at the repo root. It writes packages/supabase-runtime/src/generated/ for both public and graphql_public, so keep both in the config's schemas. Never edit the generated files.
Files 012 to 048 in supabase/seeds/ hold the seeded logins, their profiles and the seeded system staff roles. Catalogs, plans, settings and system workflows are data migrations. Files 100 to 170 hold the demo tenants, one domain per file, and each is one do block that starts with a -- Covers: comment. The header of 100_organizations.sql is the registry: the twelve organizations, every persona and the table codes used in seeded ids.
{TT}{O}00000-0000-4000-8000-{NNNNNNNNNNNN}. Pick a table code that the registry does not list yet and add it there. demo-seeds-static.test.ts fails on an unregistered code.'TT_00000-0000-4000-8000-%'. A bare 'TT%' also matches rows that triggers created with random uuids.TASK-1, Q-8, INV-2026-4), never uuids.111_catalog.sql.set_config('better_supabase.audit_purge', 'on', true) and set it back to off.quote_accepted_optional_lines and document_parties directly instead of calling the RPCs that guard the document status.170_integrity.sql runs last. It recomputes allocations, order totals and number sequences, so add new numbered documents before it.supabase/seed-buckets/<bucket>/ and load on pnpm supabase:reset. Columns store the object path only.begin; ... rollback; into psql "$(pnpm exec supabase status --output-format json | jq -r .env.DB_URL)".The Supabase integration records every seed file it runs in supabase_migrations.seed_files. On a push it runs only seed files it has not recorded yet. It skips a file that already ran, even when the file changed. Adding new seed files on top of an older seed set therefore runs them against data they did not expect, and edits to 012 or other earlier files never reach develop.
The seeds must still survive a rerun locally. 012_auth_users.sql deletes the seeded organizations by slug and by id before it deletes the seeded users, with app.skip_allocation_release and app.shipment_transition switched on. Team ids are pinned in 101_teams.sql because later files reference them.
When a seed restructure breaks the Supabase Preview check, reset the develop database. Reset branch in the dashboard does not drop data; it only redeploys.
Reset develop from the CLI. branches get returns the develop connection string with its password, and never returns one for main:
pnpm exec supabase db reset --db-url "$(pnpm exec supabase branches get develop --project-ref gunugimgrrnxqynjghqe -o env | grep '^POSTGRES_URL=' | cut -d= -f2- | tr -d '"')"Never use --linked for this. The local link points at production.
A remote reset drops every object in one transaction and fails with out of shared memory at the default lock limit. Develop runs with max_locks_per_transaction=1024. If a new branch needs it, set it with pnpm exec supabase postgres-config update --experimental --project-ref <ref> --config max_locks_per_transaction=1024, which restarts that database.
Production keeps seeding disabled, so seed changes never need a production reset. A migration squash does; see docs/operations/database-reset.mdx.
Last updated on