template-react/docs/DATABASE.md

# Database

One PostgreSQL database, `app`, exposed as the package `appdb`. Managed with Drizzle. There is no
second database and no cache database.

```
src/databases/app_db/
├── drizzle.config.ts
├── package.json          exports ".", "./db", "./schema", "./*"
└── src/
    ├── db.ts             the connection
    ├── env.ts            which database this process may reach
    ├── index.ts          public surface: one `export * from './<feature>'` per line
    ├── schema.ts         what db:push creates — see below
    └── <feature>/
        ├── index.ts      what this feature exports, declared beside the code it describes
        ├── schema.ts     its tables
        └── queries.ts    everything that reads or writes them
```

## A feature owns its own public surface

`src/index.ts` is one `export *` per feature and nothing else. What a feature exports is declared in
its own `index.ts`.

Adding a query is then one file in one directory, rather than that file plus a hand-written list of
every symbol in the package. Those lists drift: the platform's reached 297 lines with twelve features
listed twice before it was replaced.

## One directory per feature, holding both halves

Not parallel `schema/` and `queries/` trees. Those drift too — the platform ended up with four
features named differently on each side, one with no query file and one with no schema file, and
nothing structural said so.

A directory that is lopsided says so by its contents.

## `src/schema.ts` is drizzle-kit's view, not the runtime's

`drizzle.config.ts` points at it, so **commenting one line there removes a table from the DATABASE
without removing a line of code**. Every query imports its tables from `./schema` inside its own
feature directory, so the code still compiles.

That is what lets a fresh install create only the tables it needs, with optional ones commented out
until something wants them.

## Schema changes use `push`, not migrations

```bash
bun run db:push                      # -> app_dev
APP_ENV=production bun run db:push # -> app, deliberately
```

`db:push` diffs the schema code against the live database and alters it directly. **Treat the schema
code as the source of truth.** `db:gen` writes files to `migrations/`, but nothing applies them — do
not start reading those files as though they were the record.

## The schema is the source of truth for what the database may CONTAIN

Not just for its shape. Where a column has a known set of legal values, say so with a `check()` rather
than leaving it free `text` — otherwise a value the code stopped supporting sits there unnoticed, and
nothing structural says it became illegal.

```ts
export const ORDER_STATUSES = ["pending", "paid", "shipped"] as const;

status: text("status").notNull().$type<(typeof ORDER_STATUSES)[number]>(),
// in the table's second argument:
check("orders_status_check", sql`${table.status} in ('pending', 'paid', 'shipped')`),
```

**But `text` without a check where the set must stay open.** A category, a provider name, anything a
third party can add: an enum cannot contain a value nobody has compiled yet, and adding one must not
be a schema change. The test is whether the set is closed by us or open to the world.

## Which database a process reaches

Decided by `APP_ENV` and **enforced** in `env.ts` before a connection opens:

| `APP_ENV` | Database |
|---|---|
| `production` | `app` (`POSTGRES_URL`, from `.env.production.local`) |
| `development` | `app_dev` (`POSTGRES_URL_DEV`, from `.env`) |

Any other pairing throws. The check is on the database **name**, because host and credentials are
usually shared and one word at the end of a long URL is all that separates them.

## Things that bite, already handled here

**The client is a per-process singleton on `globalThis`.** `bun --hot` re-evaluates modules on every
edit; without it each save builds a fresh pool and abandons the last, queries in flight during the
swap never resolve or release their connection, and the pool eventually wedges — every route that
reads hangs while stateless ones stay fast.

**`waitForDatabase()` before startup work.** `postgres()` is lazy, so nothing fails at import when the
database is not up. Startup work that queries otherwise fires once, fails, and is swallowed by
whatever `.catch()` it was written with.

**Money is `numeric`, never a float**, and it comes back as a string. Sums of binary floating point
are wrong in ways nobody notices until an invoice.

**Denormalise what is historical fact.** A row pointing at configuration — a provider, a price list —
should also record what it actually used, as text. The configuration can be renamed, repointed or
deleted; what happened cannot.

## Tests

Tests run against `app_dev`; the `APP_ENV` guard makes production unreachable from them.

Two rules learned expensively: **clean up what you write**, and **restore what you disturb**. A test
that sets a "default" flag clears it on every other row, so deleting only your own rows afterwards is
not enough — snapshot the previous value and put it back.