> ## Documentation Index
> Fetch the complete documentation index at: https://docs.hr-easy.nlead.ch/llms.txt
> Use this file to discover all available pages before exploring further.

# Database

> Prisma conventions, the migration workflow, transactions, and the CI guards.

PostgreSQL through Prisma. **`prisma/schema.prisma` is the single source of truth** —
not a migration file, not a diagram, not a document.

## Model conventions

| Convention   | Rule                                                       |
| ------------ | ---------------------------------------------------------- |
| Primary keys | `cuid()`.                                                  |
| Timestamps   | Every model has `createdAt` and `updatedAt`.               |
| Indexes      | Index foreign keys and anything used in a `WHERE`.         |
| Money        | `Decimal`, never `Float`. See [ADR-007](/development/adr). |
| Enums        | Prefer an enum over a free-text status column.             |

## The migration workflow

Development uses `db push` for speed; staging and production use
`prisma migrate deploy`, which only applies migration files. **Both `schema.prisma` and
a migration file must be committed for every schema change.**

<Steps>
  <Step title="Edit the schema">
    `prisma/schema.prisma`.
  </Step>

  <Step title="Sync your local database">
    ```bash theme={null}
    docker compose exec app npx prisma db push
    ```
  </Step>

  <Step title="Develop and test against it" />

  <Step title="Generate the migration before committing">
    ```bash theme={null}
    docker compose exec app npm run db:migrate:create -- describe_your_change
    ```
  </Step>

  <Step title="Review the generated SQL">
    In `prisma/migrations/<timestamp>_describe_your_change/migration.sql`.
  </Step>

  <Step title="Split enum additions">
    If the migration contains `ALTER TYPE … ADD VALUE`, put the enum change in its own
    **preceding** migration. PostgreSQL will not let a new enum value be used in the same
    transaction that adds it.
  </Step>

  <Step title="Check for duplicates">
    If the column or table already exists in a prior migration, do not create a second
    one. Look in `prisma/migrations/` first.
  </Step>

  <Step title="Commit both" />
</Steps>

CI verifies that migrations are in sync with the schema and blocks a PR with drift.

<Warning>
  Never run `prisma migrate dev` without `--create-only` in Docker — it can drop your development
  database.
</Warning>

## Transactions

Conditional logic requires the **interactive callback** form:

```typescript theme={null}
// WRONG — the batch form cannot branch
await db.$transaction([db.a.create(...), db.b.update(...)]);

// CORRECT
await db.$transaction(async (tx) => {
  const a = await tx.a.create(...);
  if (a.requiresB) await tx.b.update(...);
});
```

The batch array form evaluates its arguments before the transaction opens, so a
condition depending on an earlier statement's result is evaluated against stale data.

## Never do these

| Anti-pattern                            | Why                                                            |
| --------------------------------------- | -------------------------------------------------------------- |
| `as any` to silence a Prisma type error | The query is wrong; the cast hides it until runtime.           |
| `JSON.parse()` without try/catch        | Malformed JSON from the database crashes the process.          |
| Raw SQL with string interpolation       | Injection. Use parameterised Prisma queries.                   |
| Unbounded `findMany` in an API route    | Blocked in CI. Every one needs `take` or `cursor`.             |
| A plaintext credential column           | Encrypt at the application layer with the Key Vault utilities. |

## Encryption coverage

A CI guard checks that fields holding credentials, tokens or secrets go through the
application-layer encryption utilities. A field storing a raw token as `String?` fails
the guard unless it is explicitly approved and documented as a known limitation with a
follow-up ticket.

## The guards

| Command                     | Enforces                                     |
| --------------------------- | -------------------------------------------- |
| `check:findmany-bounds`     | Every `findMany` in an API route is bounded. |
| `check:encryption-coverage` | Sensitive fields are encrypted.              |
| `check:audit-coverage`      | State-changing routes write an audit entry.  |
| `db:migrate:check`          | The database matches the schema.             |

All of them run in `npm run check:all`, which the [quality gate](/development/quality-gate)
runs.

## Seeding

```bash theme={null}
docker compose exec app npm run db:seed        # baseline reference data
docker compose exec app npm run db:seed-demo   # demo content
docker compose exec app npm run db:unseed-demo # remove it again
```

Demo seeding is reversible by design — it is used on real installations for training,
so it has to come back out cleanly.