atelier/examples/good/db-schema.md

# Good Example: Database Schema

> A SQL schema that follows Atelier's Data principles. Each aspect cites the principle it satisfies.

## The Schema

```sql
-- Users table
CREATE TABLE users (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email       VARCHAR(255) NOT NULL,
  name        VARCHAR(100) NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at  TIMESTAMPTZ,

  CONSTRAINT users_email_unique UNIQUE (email),
  CONSTRAINT users_email_format CHECK (email ~ '^[^@]+@[^@]+\.[^@]+$')
);

CREATE INDEX users_email_idx ON users (email) WHERE deleted_at IS NULL;
CREATE INDEX users_created_at_idx ON users (created_at DESC);

-- Orders table
CREATE TABLE orders (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id     UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
  status      VARCHAR(20) NOT NULL DEFAULT 'pending',
  total_cents INTEGER NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),

  CONSTRAINT orders_status_valid CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
  CONSTRAINT orders_total_positive CHECK (total_cents >= 0)
);

CREATE INDEX orders_user_id_idx ON orders (user_id);
CREATE INDEX orders_status_created_idx ON orders (status, created_at DESC);
```

## What Makes It Good

### Truth (Data P1)
- The schema reflects the domain: `users` have `email`, `name`, lifecycle timestamps. `orders` have `status`, `total`.
- No column named after a feature (`is_active_for_X`). No application state in the schema.

### Invariants in the Schema (Data P3)
- `NOT NULL` where required: `email`, `name`, `user_id`, `status`, `total_cents`.
- `UNIQUE (email)` — emails are unique. Enforced in the DB, defended in the app.
- `CHECK (status IN (...))` — status is a finite domain. Enforced in the DB.
- `CHECK (total_cents >= 0)` — totals are non-negative. Enforced in the DB.
- `REFERENCES users(id) ON DELETE RESTRICT` — you cannot delete a user with orders. Referential integrity (P9).

### Type Fidelity (Data P7)
- `id` is `UUID`, not `VARCHAR(36)`. The type matches the domain.
- `created_at` is `TIMESTAMPTZ`, not `VARCHAR` or `INTEGER`. Timezone-aware.
- `total_cents` is `INTEGER`, not `FLOAT`. Money in cents avoids floating point (P1 Truth).
- `status` is `VARCHAR(20)` with a CHECK, not a free `TEXT`. Bounded.

### Naming Consistency (Data P6)
- snake_case: `users`, `orders`, `user_id`, `created_at`.
- Foreign key: `user_id` (singular table + `_id`), not `uid` or `user`.
- Timestamps: `created_at`, `updated_at`, `deleted_at` — consistent suffix `_at`.

### Lifecycle Awareness (Data P8)
- `deleted_at` for soft delete. Lifecycle is first-class.
- The unique index on email is partial: `WHERE deleted_at IS NULL` — allows re-registration after soft delete.
- Every query must filter `deleted_at IS NULL` (a discipline, not a schema property).

### Indexing with Intent (Data P5, P10)
- `users_email_idx` — queries by email (login, lookup). Partial (excludes soft-deleted).
- `users_created_at_idx` — list users by recency. DESC matches the typical query.
- `orders_user_id_idx` — list a user's orders. FK index (join performance).
- `orders_status_created_idx` — composite for "open orders by recency" (`WHERE status = 'pending' ORDER BY created_at DESC`).
- No index on every column. Each index serves a query.

### Migration Safety (Data P4)
- This schema is created via a migration with an `up` and a `down`.
- The `down` drops the tables in reverse order (orders, then users) to respect FKs.
- Adding a column later uses expand-contract (nullable first, then constrained).

## What This Example Does NOT Do (And Why That's Good)

- Does not use `FLOAT` for money — floating point errors (P7, P1).
- Does not use `VARCHAR` for the UUID — wrong type (P7).
- Does not omit the FK on `orders.user_id` — unenforced relationship (P9).
- Does not index every column — write amplification (P10, C8).
- Does not use `is_deleted BOOLEAN` without a timestamp — loses the deletion time (P8).
- Does not allow `status` to be free text — would lose the finite domain (P3).