# `database/migrations/`

Raw SQL migrations for the **SQL Server** companion deployment. Postgres migrations live under `prisma/migrations/` and are managed by `prisma migrate dev` (the canonical source of truth).

| File                      | Purpose                                                                    |
| ------------------------- | -------------------------------------------------------------------------- |
| `0001_initial_schema.sql` | Mirrors `prisma/schema.prisma` — tenants, identity/RBAC, portal, audit log |

## Applying

Either via sqlcmd (preferred for CI):

```bash
sqlcmd -S "localhost,1433" -U sa -P "$SQLSERVER_PASSWORD" -d "$SQLSERVER_DB" \
  -i database/migrations/0001_initial_schema.sql
```

Or from VS Code with the **mssql** extension: open the file → right-click → _Execute Query_.

## Conventions

- Each file is wrapped in `BEGIN TRANSACTION` / `COMMIT TRANSACTION` so a failure leaves no half-applied DDL.
- Every `CREATE TABLE` is guarded by `IF OBJECT_ID(...) IS NULL` → safe to re-run on a partially populated DB.
- Numeric prefix sorts in apply order (`0001_`, `0002_`, ...).
- Snake_case identifiers to match Postgres + Prisma's `@map`.

## When to add a migration

Whenever `prisma migrate dev` lands a non-trivial schema change. Mirror the change here so SQL Server-only deployments stay in sync. See [`docs/runbooks/db-migrations.md`](../../docs/runbooks/db-migrations.md).
