Manage the PostgreSQL database schema. Add tables, columns, indexes, and foreign keys to the declarative schema.sql file. Use when modifying the database structure.
You are managing the declarative PostgreSQL schema. All DDL lives in a single backend/schema.sql file.
backend/schema.sql to understand existing schemaThe schema file is the single source of truth. It uses idempotent DDL:
CREATE TABLE IF NOT EXISTSALTER TABLE ... ADD COLUMN IF NOT EXISTSCREATE INDEX IF NOT EXISTSNOT migration files. One file. Always represents the current desired state.
-- ============================================================
-- [Name]s
-- ============================================================
CREATE TABLE IF NOT EXISTS [name]s (
id TEXT PRIMARY KEY DEFAULT gen_random_uuid()::text,
-- domain fields here
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_[name]s_created_at ON [name]s(created_at DESC);
ALTER TABLE [name]s ADD COLUMN IF NOT EXISTS [column] [TYPE] [CONSTRAINTS];
ALTER TABLE [name]s ADD COLUMN IF NOT EXISTS [ref]_id TEXT REFERENCES [ref]s(id);
CREATE INDEX IF NOT EXISTS idx_[name]s_[ref]_id ON [name]s([ref]_id);
TEXT with UUID defaultTIMESTAMPTZ alwaysBIGINT (cents) or NUMERIC(12,2) — never floatTEXT with CHECK constraints, not PostgreSQL enumsBOOLEAN NOT NULL DEFAULT falseJSONB only when truly schemalesspsql -h localhost -p 5731 -U postgres -d [dbname] -f backend/schema.sql
Or via Makefile: make db-schema