Decisions
The database is declared one table per file in domain folders, generated by pg-delta, and redesigned in a single breaking reset.
Supabase Declarative Schemas 2.0 generates migrations with pg-delta, which orders statements by dependency and can fail on schema drift (--strict-coverage). Our schema files still carried a numbering scheme from the time when file order set the run order, and policies, triggers and audit registrations for one table were spread over several layer files. Earlier migrations had also accumulated data-model debt: bigint keys on a few tables, text status columns with check constraints, mixed cancelled and canceled spellings, plaintext bearer tokens, money in several scales, jsonb lists that hid relations, and about 19 seller and buyer columns copied onto each document table.
The product has no production data that must be kept, so a breaking change with one reset costs less than a sequence of compatible migrations.
supabase/schemas/ has one folder per domain with one file per table, named after the table. A table file holds the table, its indexes, comments, triggers, bs_audit trigger, RLS and policies. Enums go in <domain>/types.sql, functions in <domain>/functions/<area>.sql. No file is numbered, and config.toml has no schema_paths.pnpm supabase:diff runs pg-delta with --strict-coverage. CI runs it and the unscoped sync that covers event triggers, and fails on drift.pnpm supabase:squash): extensions, init, event triggers, module data, the named data migrations and the PermDock seeds. Hosted develop and production are reset once with docs/operations/database-reset.mdx. Repairing the migration history is rejected, because the old schema does not match the new init.uuid keys. The {customer_id} invoice number token is removed.canceled spelling. Tables written by better-supabase module functions keep text columns._at and dates in _on.document_parties, with a document_party_role enum and a check that each row belongs to exactly one quote, quote version, invoice or order. Sending a quote or invoice writes the snapshot, the status and the history in one security invoker function.chamber_of_commerce_number, vat_number and iban in the database. Public contract fields keep their names (kvk, btw, vatId, sellerKvk, buyerBtw), and the services map between them, so API clients do not break for a column rename. The webhook and file contracts are the exception: they follow the renamed date columns.position: quote_accepted_optional_lines and organization_social_links.Kept on purpose:
organization_invitations.prefill stays jsonb. The better-supabase invitations block owns it in its RPCs and client, and a jsonb-schemas check validates its shape.quotes.lifecycle_workflow_key, invoices.lifecycle_workflow_key, templates.lifecycle_workflow_key and the organization defaults) stay validated text with no foreign key. A key matches several versioned workflow_definitions rows, so it has no unique target.chat_messages.dispatch_id, quote_version_lines.source_line_id and product_scan_requests.workflow_run_id stay correlation ids without a foreign key. The rows they point at can be deleted or cleaned up by retention while the referencing row must stay.pnpm supabase:diff -f <name>.organizations versus organization_settings, and the status history tables that overlap with workflow lifecycle events.Last updated on