Open-source manufacturing ERP, MES and QMS. Quoting, MRP, inventory, shop floor, quality and lot/serial traceability on one Postgres schema, with a REST API and MCP server. Self-host or use Carbon Cloud.
paths: ["packages/database/supabase/migrations/**"]
Database Migration Patterns
How migrations are written and structured in Carbon. The full workflow (checklist,
.models.ts updates) is in workflow-database-migration.md — follow that. The table
template lives in conventions-database.md. This file captures the real conventions in
the SQL itself, grounded in the newest migrations. Don't repeat those two files.
Migrations live in packages/database/supabase/migrations/, timestamp-prefixed
(YYYYMMDDHHMMSS_descriptive-name.sql), applied in order by the Supabase CLI. Read the
NEWEST relevant migration for current truth — tables get renamed and functions get revised;
never trust the first match or this doc over the live SQL.
Commands (verified in root package.json)
| Command | What it does |
|---|---|
pnpm db:migrate:new <name> |
Create a new migration file (supabase migration new). |
pnpm db:migrate |
Apply pending migrations to the local worktree DB (crbn migrate); also regenerates types + swagger unless --no-regen. |
pnpm db:types |
Regenerate generated DB types after migrations. |
There is no db:build script — older docs/cache referenced it; ignore that. Use
pnpm db:migrate to apply locally. Never rebuild the DB to test; let the user do it.
Timestamp warning: never use 000000 for the HHMMSS portion (e.g. …000000_foo.sql).
The timestamp is the migration's primary key; randomize HHMMSS (e.g. 20260619142853_…) to
avoid cross-branch collisions.
Table creation (canonical, from 20260609143732_document-template.sql)
CREATE TABLE "documentTemplate" (
"id" TEXT NOT NULL DEFAULT id(),
"companyId" TEXT NOT NULL,
"documentType" TEXT NOT NULL,
"blocks" JSONB NOT NULL DEFAULT '[]',
"createdBy" TEXT NOT NULL REFERENCES "user"("id"),
"createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
"updatedBy" TEXT REFERENCES "user"("id"),
"updatedAt" TIMESTAMP WITH TIME ZONE,
PRIMARY KEY ("id", "companyId"),
FOREIGN KEY ("companyId") REFERENCES "company"("id") ON DELETE CASCADE
);
CREATE INDEX "documentTemplate_companyId_idx" ON "documentTemplate" ("companyId");
CREATE INDEX "documentTemplate_createdBy_idx" ON "documentTemplate" ("createdBy");
-- Named, company-scoped uniqueness via ALTER (not inline)
ALTER TABLE "documentTemplate" ADD CONSTRAINT "documentTemplate_companyId_documentType_key"
UNIQUE ("companyId", "documentType");
id()default — both bareid()and prefixedid('prefix')are current and common (prefixes are short:id('pr'),id('dim'),id('je'),id('tce')). Either is fine. Never use a raw UUID.- Composite PK
("id", "companyId")+companyIdFK tocompanywithON DELETE CASCADE. - Audit columns reference
userinline (TEXT NOT NULL REFERENCES "user"("id")) — no separate named FK constraints. - MANDATORY: every table with
createdByMUST also haveupdatedBy— a nullable"updatedBy" TEXT REFERENCES "user"("id"). The shared audit-injection path (MCP the Carbon API dispatch, service writes) stampsupdatedByon every write, so a table that hascreatedBybut omitsupdatedByfails those callers withcolumn "updatedBy" does not exist. Include it even on append-only ledger tables (it just stays NULL there). When authoring a migration that addscreatedBy, addupdatedByin the same statement. updatedAtis set by the app on write, not by a DB trigger. Don't add a generic timestamp trigger.- Index
companyIdand every FK (e.g.createdBy). - Never add an
itemReadableIdcolumn, and never specify decimal places in aNUMERIC— this applies everywhere: column definitions,RETURNS TABLEdeclarations, and::NUMERICcasts. Always use bareNUMERIC, neverNUMERIC(19,4)or any precision form.
RLS
Policies are not written in migrations. Add the table's rule to
packages/database/src/authz/manifest.ts — usually one line, entityName: company("<module>"),
which renders the four standard policies (SELECT any employee via
get_companies_with_employee_role(), writes via
get_companies_with_employee_permission('<module>_<action>')) — then pnpm db:migrate syncs it
locally and pnpm --filter @carbon/database authz migration <name> ships it to production.
Full guide, including tables without a companyId: authz-manifest.md.
Gate writes on the write permission, not just visibility — a SELECT-only predicate on
INSERT/UPDATE/DELETE is a privilege-escalation bug (20260614092317_picking-tracked-entity-rls.sql
exists because of one). parent(table, fk, module) gets this right for child tables.
Views
Always SECURITY_INVOKER=true so the underlying tables' RLS applies to the querying user:
CREATE OR REPLACE VIEW "module_entityView" WITH(SECURITY_INVOKER=true) AS
SELECT e.*, u."fullName" AS "createdByFullName"
FROM "entityName" e
LEFT JOIN "user" u ON u."id" = e."createdBy";
CREATE OR REPLACE VIEW replaces the view's options, so recreating a view without the WITH
clause silently turns it back into an owner-rights view that bypasses RLS for every PostgREST
caller, anon included (openJobMaterialLines, fixed in 20260926093417). The
no-view-without-invoker check (@carbon/checks) fails any CREATE VIEW that does not state
security_invoker; the view-without-security-invoker invariant checks the live catalog.
Materialized views cannot run as the caller — revoke SELECT from anon, authenticated instead.
Triggers
There is no generic boilerplate trigger to add per table. Triggers in this codebase are
purpose-built (status recomputation, search-index sync, the event-system dispatch) and live
in their own migrations — e.g. CREATE TRIGGER update_picking_list_status_trigger …
(20260601143527_picking-lists.sql). Don't manufacture an updatedAt/companyId trigger;
add a trigger only when there's real derived state to maintain. For async/event-driven side
effects use the event system (see event-system.md), not ad-hoc triggers.
Redefining a function: start from the newest definition
A CREATE OR REPLACE FUNCTION replaces the whole body, so copy it from the NEWEST
migration that defines the function, never from the one you remember or the one a
comment names. 20261014171619_receive-consumed-serial-units forked
complete_job_to_inventory from 20261006221601 and undid the two migrations between
them (always-post job costing, the production-event lock); 20261015161847_job-receipt-takes-issued-serial-units
restored them. The function-redefinition-keeps-changes check (@carbon/checks) fails a new
redefinition that drops lines its predecessor added.
Restricting a function: guard inside, never REVOKE EXECUTE
Every function in public is a PostgREST RPC. On supabase/postgres 15.14.1.112, calling a function
the caller lacks EXECUTE on, as anon/authenticated, segfaults the backend and restarts every
connection. So a REVOKE EXECUTE … FROM anon, authenticated turns "not allowed" into an
unauthenticated one-request DoS. (A missing TABLE privilege is an ordinary error; table REVOKEs are fine.)
Service-role-only function called directly by a service client — first statement of the body:
IF current_setting('role', true) IN ('anon','authenticated') THEN RAISE EXCEPTION … USING ERRCODE = 'insufficient_privilege'; END IF;PostgREST'sSET ROLEstays visible inside SECURITY DEFINER, and service role / direct connections (none) pass. Examples:get_integration_secret,assert_audit_log_access(company, NULL).Tenant-scoped function (SECURITY DEFINER, takes a company id from the caller) — first statement
PERFORM assert_company_access(company_id[, '<module>_<action>'])(in aLANGUAGE sqlbody: a leadingSELECT assert_company_access(company_id);). It raises; never write the check inline asIF NOT (x = ANY(helper()) OR …): when a lookup finds nothing that expression is NULL,IF NOT NULLdoes not raise, and the guard fails open. That is howget_next_sequenceand the storage-requirement RPCs let any caller through until20260925121735. A user id from the caller needs the same care (get_claims,groups_for_user).Internal helper of a SECURITY DEFINER function — the role GUC still says
authenticatedinside the nested call, so the guard above would refuse legitimate callers. Make the helperSECURITY INVOKER: from its SECURITY DEFINER caller it runs as the owner, and from the API it runs under the caller's RLS (terminal_job_operations,complete_job_remaining_quantities). Or put it inutil(no APIUSAGE) — seeevent-system.md.Event interceptor — SECURITY INVOKER. The dispatchers are SECURITY DEFINER, so it runs as the owner on every write; called directly it runs under the caller's RLS (
event-system.md).
Every public table has RLS (including the per-company searchIndex_* / auditLog_* tables), so a
SECURITY INVOKER function can do nothing through the API that REST could not. Default to it; reach
for SECURITY DEFINER only when the function must see across RLS, and then guard it as above. The
public-definer-function-authorizes-caller invariant (pnpm --filter @carbon/checks invariants)
fails on any SECURITY DEFINER function with no recognized guard.
Migration 20260924192316_api-function-guards-not-revokes.sql converted the six functions that used
REVOKE; 20260925121735_rpc-function-guards.sql closed the other 98. Test a new guard in a
throwaway container (docker run --rm supabase/postgres:<tag>), never a shared database — and call
the function as anon, don't just check has_function_privilege: the segfault only shows on the
call. packages/database/supabase/tests/rpc-privileges.test.sql is the tenant-isolation test.
Enums
CREATE TYPE "warehouseTransferStatus" AS ENUM ('Draft', 'To Ship', 'To Receive', 'Completed');
Add new values with ALTER TYPE "<enum>" ADD VALUE '<v>' in a later migration (cannot run
inside a transaction block with other statements that use the value).
Gotchas
- Read the newest migration touching a table/function — renames (
shelf→storageUnit,customRule→storageRule→enforcementRule) and revised RPCs are common. - Schema-qualify (
"public"."t") on RLS statements; cast helper results::text[]. - After adding a migration, regenerate types (
pnpm db:types) or typecheck breaks withSelectQueryError/ "excessively deep" errors. - For DB access patterns (clients, services, Kysely transactions) see
database-patterns.md; for the table template + checklist seeconventions-database.md.