SQL = SSOT

What's the truth for your DB schema? ORM models? live DB? migration files? Choosing "declarative SQL files" keeps things simple and reproducible.

1. Three options

Approach	Truth	Feel
ORM-first	Entity classes	Rails/Django. Auto-sync with `synchronize:true`
Migration-first	Sequential files (Flyway/Liquibase)	Great history
Declarative SQL-first	`CREATE TABLE IF NOT EXISTS` files	Reproducible, manual ALTER

2. An example workspace picks Declarative SQL-first

CREATE TABLE IF NOT EXISTS public.posts (
  id BIGSERIAL PRIMARY KEY,
  slug TEXT NOT NULL,
  category_slug TEXT NULL,
  language CHAR(2) NOT NULL DEFAULT 'ko',
  content_kind VARCHAR(10) NOT NULL DEFAULT 'note'
    CHECK (content_kind IN ('note', 'blog', 'edu')),
  title TEXT NOT NULL,
  content_md TEXT NOT NULL DEFAULT '',
  published BOOLEAN NOT NULL DEFAULT FALSE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_posts_published
  ON posts (language, content_kind, published);

3. Rules

CREATE TABLE IF NOT EXISTS only
CREATE INDEX IF NOT EXISTS only
No DROP TABLE
No persistent ALTER.sql files

4. Adding a column to a live table

ALTER the live DB:

docker exec prod-postgres psql -U ... \
  -c "ALTER TABLE posts ADD COLUMN IF NOT EXISTS subtitle TEXT;"

Add the column inside the CREATE TABLE in the SSOT file.
Do NOT keep an ALTER TABLE line in the file.

5. Exception — forward-ref FK

CREATE TABLE A (..., b_id BIGINT);
CREATE TABLE B (id BIGSERIAL PRIMARY KEY);
DO $
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'a_b_fk') THEN
    ALTER TABLE A ADD CONSTRAINT a_b_fk FOREIGN KEY (b_id) REFERENCES B(id);
  END IF;
END $;

Only case DO $ block is allowed.

6. Why SQL-first

Reproducible
Reviewable in git diffs
ORM-independent (Node, Java, Python can share)
Manual psql checks match expectations

7. Why not ORM-first

ORM → SQL is a black-box translation. Across languages, it's hard to reconcile. Manual \d table results may diverge from entities.

8. Why not Flyway-style migration-first

Great in many teams. But live schema = cumulative migrations. Checking a column means reading many files. Fresh installs replay all migrations.

This structure prefers "see the current schema at a glance + fresh installs fast".

9. Update four places together

1. admin/sql/<domain>/NN.sql     (SSOT)
2. seed.ts
3. public site types
4. admin types

Check in PR. Freeze a count test in CI.

10. Seeds

Seed from code with idempotent INSERTs.

for (const c of CATEGORIES_SEED) {
  await query(`INSERT INTO categories (...) VALUES (...) ON CONFLICT ... DO NOTHING`, [...]);
}

11. Gotchas

Permanent ALTER files
CREATE vs live DB drift
New UNIQUE → existing duplicate conflicts
FK order errors → use forward-ref exception

Closing

SSOT is a promise "only this one place is the truth". Declarative SQL files express it in the simplest file.

05-progressive-refactor