# Postgres Table Conventions — Starter Rules

> Drop this into your repo as `.claude/rules/tables.md`. An AI coding agent
> (e.g. Claude Code) loads it as a project instruction and creates tables to
> convention from the next session on.
>
> Companion download to: **Postgres Table Conventions — Naming, Keys and Audit
> Columns for a Consistent Schema**
> → https://sql.marcus-belz.de/postgres-tabellen-konventionen/

These are conventions, not a PostgreSQL standard. Where a choice is style
rather than a technical must, it is marked.

## Naming

- snake_case, **singular** table names (`project`, `user`, `task` — not `projects`).
- Foreign-key columns: `<entity>_id` (`parent_id`, `user_id`).
- Timestamp columns end in **`_on`**, never `_at` (`created_on`, `modified_on`).
- Constraints and indexes are **named**: `pk_<table>`, `uq_<table>_<column>`,
  `fk_<table>_<column>`, `chk_<table>_<column>`, `idx_<table>_<column>`.

## Keys

- Most tables get a surrogate primary key `id` with a **named** constraint:
  `CONSTRAINT pk_<table> PRIMARY KEY (id)` — never inline `id ... PRIMARY KEY`.
- `id bigserial`, or — SQL-standard and preferred for new projects —
  `id bigint GENERATED ALWAYS AS IDENTITY`. Pick one, use it everywhere.
- Natural / business keys become `UNIQUE` constraints, not the PK.
- Exception: pure **junction tables** use the composite natural key as the PK
  (`PRIMARY KEY (user_id, role_id)`), no extra `id`.

## Data types

- Character columns: `varchar` (with a length where a business limit exists,
  without one otherwise). NOTE: in Postgres `varchar` without a length is
  internally identical to `text` — this is **style, not performance**. Pick one,
  don't mix.
- Audit columns on mutable tables: `created_on timestamptz NOT NULL DEFAULT now()`,
  `created_by`, `modified_on` (NULL until the first UPDATE), `modified_by`.
  Lookup / junction / immutable tables don't need them.

## Constraints, comments, seed

- Inline in `CREATE TABLE`: only `PRIMARY KEY` and `CHECK`.
- `UNIQUE` and `FOREIGN KEY` as **separate, idempotent** `ALTER` statements after
  `OWNER`: `DROP CONSTRAINT IF EXISTS <name>;` then `ADD CONSTRAINT <name> ...;`
  (Postgres has no `ADD CONSTRAINT IF NOT EXISTS`). This is a deployment choice —
  inline FKs are equally valid. On very large tables, `DROP`+`ADD` is expensive
  (lock, index rebuild, FK re-validation): create once, or `ADD ... NOT VALID` +
  a separate `VALIDATE CONSTRAINT`.
- Indexes: `CREATE INDEX IF NOT EXISTS idx_<table>_<column> ...`, mainly on
  frequently filtered non-unique columns like foreign keys.
- Choose `ON DELETE` deliberately (`SET NULL` / `CASCADE` / default Restrict).
- `COMMENT ON TABLE` for every table; `COMMENT ON COLUMN` for non-obvious columns
  (codes/flags: list the allowed values). Or document in a data catalogue — this
  part is team policy.
- Seed with leading commas and `ON CONFLICT (...) DO UPDATE` for idempotency.

## Skeleton

```sql
CREATE TABLE IF NOT EXISTS app.project
(
    id          bigserial     NOT NULL
   ,slug        varchar(100)  NOT NULL
   ,name        varchar(200)  NOT NULL
   ,parent_id   bigint            NULL

   ,CONSTRAINT pk_project        PRIMARY KEY (id)
   ,CONSTRAINT chk_project_name  CHECK (length(trim(name)) > 0)
);
ALTER TABLE app.project OWNER TO app_owner;

-- Unique constraints
ALTER TABLE app.project DROP CONSTRAINT IF EXISTS uq_project_slug;
ALTER TABLE app.project ADD  CONSTRAINT uq_project_slug UNIQUE (slug);

-- Foreign keys
ALTER TABLE app.project DROP CONSTRAINT IF EXISTS fk_project_parent_id;
ALTER TABLE app.project ADD  CONSTRAINT fk_project_parent_id FOREIGN KEY (parent_id) REFERENCES app.project(id) ON DELETE SET NULL;

-- Indexes
CREATE INDEX IF NOT EXISTS idx_project_parent_id ON app.project (parent_id);

-- Comments
COMMENT ON TABLE  app.project      IS 'Master data: named projects.';
COMMENT ON COLUMN app.project.slug IS 'Unique project slug (natural key, UNIQUE).';
```