--- name: forge-schema-design description: Relational schema design. UUIDv7 primary keys, foreign-key enforcement at the DB, audit columns, soft delete with partial unique indexes, enums vs lookup tables, multi-tenancy patterns, money as NUMERIC, TIMESTAMPTZ always. Contains worked schema for a multi-tenant orders + customers system. Use when designing a new schema or auditing one before it grows beyond fixing. license: MIT --- # forge-schema-design You are designing a relational schema that will hold real data for years. Default agent-written schemas use `id SERIAL PRIMARY KEY`, no audit columns, no foreign keys, and a single `data JSONB` column "for flexibility." Each compounds into pain at scale. This skill exists to set the foundation right. The mental model: a schema is a contract about what data is true. The constraints in the schema are what stop bugs from corrupting state. Permissiveness in the schema becomes complexity in every consumer. ## Quick reference (the things you must never ship) 1. `id SERIAL PRIMARY KEY` exposed externally. 2. A table without `created_at` and `updated_at`. 3. A column without explicit `NOT NULL` or explicit `NULL`. 4. `TIMESTAMP` (without time zone) on any timestamp column. 5. `FLOAT` / `REAL` on anything that represents money. 6. `VARCHAR(255)` as a catch-all length. 7. A foreign-key column without an index on it (Postgres does not auto-index FKs). 8. `data JSONB` as the only column on a critical table. 9. A `status` column without an enum or CHECK constraint. 10. MySQL `utf8` instead of `utf8mb4`. ## Hard rules ### Primary keys **1. UUIDv7 or ULID over autoincrement integers.** Both are time-sortable, opaque externally, generated client-side (no round-trip), and do not leak row counts. ```sql -- BAD id SERIAL PRIMARY KEY -- GOOD (Postgres 17+ has native uuidv7()) id UUID PRIMARY KEY DEFAULT uuidv7() ``` Application-generated UUIDv7 (via the `uuidv7` library) is also fine; just stop passing autoincrement IDs to clients. **2. Never expose autoincrement IDs publicly.** They invite enumeration, leak business metrics ("they have 4000 customers"), and require a separate public ID anyway. **3. Compound primary keys are fine when natural.** `user_role` keyed by `(user_id, role_id)` is correct. Forcing a synthetic surrogate adds complexity. ### Foreign keys **4. Foreign keys enforced at the database.** Application-only referential integrity erodes the moment a script runs ad-hoc. ```sql CONSTRAINT orders_customer_fk FOREIGN KEY (customer_id) REFERENCES customers (id) ``` **5. `ON DELETE` policy chosen explicitly.** `RESTRICT`, `CASCADE`, `SET NULL` - pick per relationship. Defaulting silently leads to surprise. **6. Soft delete prevents most cascade questions.** A `deleted_at TIMESTAMPTZ NULL` lets you keep history; filter at query time. ### Naming **7. Tables are plural, snake_case. Columns are snake_case.** `users`, `order_items`. Not `User`, not `OrderItems`. **8. Foreign-key column: `_id`.** `user_id`, not `users_id`. **9. Boolean columns named as positive predicates.** `is_active`, `has_paid`. Not `not_archived`. **10. Timestamps use `_at` suffix.** `created_at`, `updated_at`, `deleted_at`, `confirmed_at`. ### Audit columns **11. Every table has `created_at` and `updated_at`.** Tables without these become forensic nightmares. **12. `updated_at` maintained via trigger, not application code.** ```sql CREATE OR REPLACE FUNCTION touch_updated_at() RETURNS TRIGGER AS $$ BEGIN NEW.updated_at = now(); RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER users_touch_updated_at BEFORE UPDATE ON users FOR EACH ROW EXECUTE FUNCTION touch_updated_at(); ``` **13. High-stakes tables maintain a separate `audit_log`.** Append-only. Who changed what, when, from what to what. ```sql CREATE TABLE audit_events ( id UUID PRIMARY KEY, actor_id UUID NULL, entity_type TEXT NOT NULL, entity_id UUID NOT NULL, action TEXT NOT NULL, changes JSONB NULL, ip_address INET NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE INDEX CONCURRENTLY audit_events_entity_idx ON audit_events (entity_type, entity_id, created_at DESC); ``` ### Nullability **14. Default to `NOT NULL`.** Nullability is a strong claim. If you cannot articulate when null applies, the column should be `NOT NULL`. **15. `NULL` and empty string are not interchangeable.** Pick one for "absence." **16. `NULL` and `0` are not interchangeable for numbers.** `score = 0` means "scored, got nothing." `NULL` means "not scored yet." ### Types **17. Pick the narrowest type that works.** `SMALLINT` over `INT` for small enums; `INT` over `BIGINT` unless you genuinely expect 2B+ rows; `TIMESTAMPTZ` over `TIMESTAMP`. **18. `TIMESTAMPTZ` always (Postgres).** Store UTC, render in user's tz. `TIMESTAMP WITHOUT TIME ZONE` invites bugs during DST. **19. `TEXT` over `VARCHAR(N)` in Postgres.** Same storage, no length lock-in. Add a CHECK for length if needed: ```sql email TEXT NOT NULL CHECK (length(email) <= 320 AND email LIKE '%@%') ``` **20. `NUMERIC(p, s)` for money. Never `FLOAT` or `REAL`.** ```sql total_cents NUMERIC(15, 0) NOT NULL CHECK (total_cents >= 0) ``` `NUMERIC(15, 0)` covers a quadrillion units. Store cents (the smallest unit) so arithmetic is integer. **21. `UUID` column type, not `TEXT` storing a UUID.** Postgres has native type; faster and validates shape. **22. `JSONB` over `JSON`. Avoid both unless schema is truly variable.** A "JSONB blob for the flexible parts" is the developer's escape valve; it becomes everyone else's archeology project. ### Enums **23. Use database enums for stable small sets.** ```sql CREATE TYPE order_status AS ENUM ('pending', 'paid', 'fulfilled', 'cancelled', 'refunded'); CREATE TABLE orders ( ... status order_status NOT NULL DEFAULT 'pending' ); ``` The DB enforces the value set. Adding a new value: `ALTER TYPE order_status ADD VALUE 'returned';` (safe, non-blocking). **24. Use a lookup table for sets that change frequently or carry metadata.** ```sql CREATE TABLE plans ( id UUID PRIMARY KEY, code TEXT NOT NULL UNIQUE, name TEXT NOT NULL, price_cents NUMERIC(15, 0) NOT NULL ); ALTER TABLE customers ADD COLUMN plan_id UUID REFERENCES plans (id); ``` Adding a plan/changing price = a row insert, not a migration. **25. No free-text `status` columns.** A `status TEXT` with no constraint is inevitable bug source. ### Indexes **26. Primary key indexed automatically. Foreign keys also indexed (Postgres does NOT do this automatically).** ```sql CREATE INDEX CONCURRENTLY orders_customer_id_idx ON orders (customer_id); ``` **27. Index columns you filter or sort by frequently. Stop adding indexes when query plans show no improvement.** Every index slows writes. **28. Composite indexes follow leftmost-prefix.** `(user_id, status, created_at)` covers queries on `user_id`, on `(user_id, status)`, and on `(user_id, status, created_at)`. Not on `status` alone. **29. Partial indexes for common-filter cases.** Smaller, faster, narrower. ```sql CREATE INDEX CONCURRENTLY orders_active_idx ON orders (created_at DESC) WHERE status IN ('pending', 'paid'); ``` **30. `UNIQUE` constraint is also an index. Use `UNIQUE` instead of a unique index when the constraint is semantic.** ### Soft delete **31. `deleted_at TIMESTAMPTZ NULL` on tables where history matters.** Query with `WHERE deleted_at IS NULL`. **32. Unique constraints respect soft-delete via partial index.** ```sql CREATE UNIQUE INDEX CONCURRENTLY customers_email_active_uq ON customers (lower(email)) WHERE deleted_at IS NULL; ``` A deleted user can be re-signed-up with the same email. ### Multi-tenancy **33. `tenant_id` (or `org_id`) on every table that holds tenant data. Index it as the first column of most composite indexes.** Postgres row-level security can enforce isolation. **34. Schema-per-tenant only at hundreds-of-tenants, not thousands.** Per-tenant schemas mean per-tenant migrations. **35. Composite FK includes tenant_id when relevant.** ```sql FOREIGN KEY (tenant_id, user_id) REFERENCES users (tenant_id, id) ``` Prevents cross-tenant references. ### Normalization vs denormalization **36. 3NF as the starting point. Denormalize only when measurement shows the cost of joins.** Over-eager denormalization is the most common cause of "we should have used a relational DB" regret. **37. Materialized views or summary tables for read-heavy aggregations. Maintain via triggers or scheduled refresh.** ### Constraints **38. `CHECK` constraints for invariants.** ```sql CHECK (price_cents >= 0) CHECK (currency IN ('USD', 'EUR', 'KZT')) CHECK (length(email) <= 320 AND email LIKE '%@%') ``` **39. `UNIQUE` for natural uniqueness.** Email per user, slug per tenant. **40. Foreign-key cycles are a code smell.** If A references B and B references A, the schema is probably wrong. ## Common AI-output patterns to reject | Pattern | Why wrong | Fix | | --- | --- | --- | | `id SERIAL PRIMARY KEY` exposed externally | Leaks counts, invites enumeration | UUIDv7 / ULID | | Table without `created_at`/`updated_at` | Forensic nightmare | Audit columns on every table | | `VARCHAR(255)` everywhere | Arbitrary length cap | `TEXT` + `CHECK length` | | `TIMESTAMP` (no TZ) | DST bugs | `TIMESTAMPTZ` | | `FLOAT` on price/amount/total | Rounding errors | `NUMERIC(15, 0)` for cents | | FK without index | Slow joins, slow cascades | `CREATE INDEX CONCURRENTLY` on FK column | | `status TEXT` with no constraint | Invalid statuses sneak in | Enum or CHECK | | `data JSONB` as primary content | Unstructured archeology | Real columns; JSONB only for genuinely variable fields | | `email VARCHAR(50)` | Real emails go up to 320 chars | `TEXT CHECK (length(email) <= 320)` | | Single `name TEXT` for first+last | Internationalization breaks | Pick one or three; not "name = 'Anna van der Berg, Esq.'" | | Naming `id`, `name`, `data` everywhere | Generic | Domain-specific (forge-naming) | ## Worked example: full multi-tenant orders schema ```sql -- migrations/20260522_143000_init.sql BEGIN; CREATE TYPE order_status AS ENUM ('pending', 'paid', 'fulfilled', 'cancelled', 'refunded'); CREATE TABLE tenants ( id UUID PRIMARY KEY, name TEXT NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE TABLE customers ( id UUID PRIMARY KEY, tenant_id UUID NOT NULL REFERENCES tenants (id), email TEXT NOT NULL, name TEXT NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now(), deleted_at TIMESTAMPTZ NULL, CONSTRAINT customers_email_check CHECK (length(email) <= 320 AND email LIKE '%@%'), CONSTRAINT customers_name_check CHECK (length(name) BETWEEN 1 AND 200) ); CREATE TABLE orders ( id UUID PRIMARY KEY, tenant_id UUID NOT NULL REFERENCES tenants (id), customer_id UUID NOT NULL, status order_status NOT NULL DEFAULT 'pending', currency CHAR(3) NOT NULL, total_cents NUMERIC(15, 0) NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now(), -- composite FK ensures cross-tenant references are impossible CONSTRAINT orders_customer_fk FOREIGN KEY (tenant_id, customer_id) REFERENCES customers (tenant_id, id), CONSTRAINT orders_currency_check CHECK (currency IN ('USD', 'EUR', 'KZT')), CONSTRAINT orders_total_nonneg CHECK (total_cents >= 0) ); CREATE OR REPLACE FUNCTION touch_updated_at() RETURNS TRIGGER AS $$ BEGIN NEW.updated_at = now(); RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER tenants_touch_updated_at BEFORE UPDATE ON tenants FOR EACH ROW EXECUTE FUNCTION touch_updated_at(); CREATE TRIGGER customers_touch_updated_at BEFORE UPDATE ON customers FOR EACH ROW EXECUTE FUNCTION touch_updated_at(); CREATE TRIGGER orders_touch_updated_at BEFORE UPDATE ON orders FOR EACH ROW EXECUTE FUNCTION touch_updated_at(); COMMIT; -- Indexes outside the transaction (CREATE INDEX CONCURRENTLY). -- forge-migrations rule 3. CREATE UNIQUE INDEX CONCURRENTLY customers_email_active_uq ON customers (tenant_id, lower(email)) WHERE deleted_at IS NULL; -- Composite FK requires (tenant_id, id) to be unique on customers. -- Combined with PRIMARY KEY (id), this means we need a unique on (tenant_id, id) too. CREATE UNIQUE INDEX CONCURRENTLY customers_tenant_id_id_uq ON customers (tenant_id, id); CREATE INDEX CONCURRENTLY orders_tenant_customer_idx ON orders (tenant_id, customer_id); CREATE INDEX CONCURRENTLY orders_tenant_created_at_idx ON orders (tenant_id, created_at DESC, id DESC); -- Partial index for the common active-order query. CREATE INDEX CONCURRENTLY orders_active_idx ON orders (tenant_id, created_at DESC) WHERE status IN ('pending', 'paid'); ``` What this demonstrates: - UUID PKs everywhere (rule 1) - `tenant_id` on every tenant-bound table + composite FK to prevent cross-tenant refs (rule 33, 35) - `created_at`, `updated_at` on every table with a trigger (rule 11-12) - Soft delete via `deleted_at` with a partial unique index (rule 31-32) - `NUMERIC(15, 0)` for money (rule 20) - `TIMESTAMPTZ` always (rule 18) - DB enum for `order_status` (rule 23) - `TEXT` + `CHECK length` instead of `VARCHAR(N)` (rule 19) - `CHECK` constraints for invariants (rule 38) - Index DDL outside the transaction with `CONCURRENTLY` (forge-migrations rule 3) - Composite indexes follow leftmost prefix (rule 28) - Partial index for the hot query (rule 29) ## Workflow When designing a new schema: 1. **List entities and relationships in prose first.** Just a paragraph; do not start with SQL. 2. **Sketch tables: name, columns, types, nullability, foreign keys.** 3. **Identify access patterns.** Which queries run often? 4. **Add indexes based on patterns, not guesses.** 5. **Add CHECK and UNIQUE constraints for known invariants.** 6. **Add audit columns to every table.** 7. **Write the migration with forge-migrations safety patterns.** 8. **Have someone else review before the first row is inserted.** ## Verification ```bash bash skills/data/forge-schema-design/verify/check_schema.sh path/to/schema.sql ``` Flags: tables without `created_at`, `VARCHAR(255)` catch-alls, `SERIAL PRIMARY KEY`, `TIMESTAMP` without TZ, `FLOAT/REAL` on money-shaped columns. ## When to skip this skill - Caching layers, search indexes, and other non-source-of-truth stores (Redis, Elastic). - Document/NoSQL designs - different schema model entirely. - Tiny prototype schemas that will not survive beyond the demo. ## Related skills - [`forge-migrations`](../forge-migrations/SKILL.md) - how to deploy schema changes safely. - [`forge-api-design`](../../backend/forge-api-design/SKILL.md) - APIs over the schema.