# Database

PostgreSQL holds durable domain state only. Every ephemeral value lives in
Redis, so no request writes a per-request row to Postgres.

## Purpose

This guide describes the schema, the indexes and constraints, the migration
lifecycle, and the operational rules for applying migrations. It is the
reference for anyone changing a table or a query.

## The six tables

Migrations create exactly six domain tables plus sqlx's `_sqlx_migrations`
bookkeeping table. They create schema only and seed zero rows, so a fresh
install starts empty by design.

### oauth_clients

One row per registered client (RFC 7591 / RFC 7592).

| Column | Type | Notes |
| --- | --- | --- |
| `id` | `uuid` | Primary key. |
| `client_id` | `text` | Unique table constraint, and the FK target for `client_redirect_uris`. |
| `client_secret_hash` | `text` | Argon2id hash, never a raw secret. |
| `client_secret_expires_at` | `timestamptz` | Optional expiry. |
| `client_id_issued_at` | `timestamptz` | Issue time. |
| `client_name` | `text` | Display name. |
| `grant_types` | `text[]` | Registered grant types. |
| `response_types` | `text[]` | Registered response types. |
| `scope` | `text` | Registered scope string. |
| `token_endpoint_auth_method` | `text` | One of the supported methods. |
| `jwks` | `jsonb` | Optional inline JWKS. |
| `jwks_uri` | `text` | Optional JWKS URL (fetched with the SSRF guard). |
| `tls_client_auth_subject_dn` | `text` | RFC 8705 subject DN binding. |
| `tls_client_auth_cert_thumbprint` | `text` | RFC 8705 `x5t#S256` binding. |
| `request_uris` | `jsonb` | Pre-registered JAR request URIs. |
| `contacts` | `jsonb` | Metadata only. |
| `software_id` | `text` | Metadata only. |
| `active` | `bool` | Soft disable, default true. |
| `registration_access_token_hash` | `text` | Keyed hash of the RFC 7592 management token. |
| `created_at`, `updated_at` | `timestamptz` | Audit timestamps. |

Two partial unique indexes enforce that a client certificate authenticates at
most one client:

- `ux_oauth_clients_tls_subject_dn` on `tls_client_auth_subject_dn` where it is
  not null.
- `ux_oauth_clients_tls_cert_thumbprint` on `tls_client_auth_cert_thumbprint`
  where it is not null.

The `UNIQUE (client_id)` is a table constraint, not merely an index, because
Postgres accepts only a unique constraint as a foreign-key target.

### users

One row per resource owner.

| Column | Type | Notes |
| --- | --- | --- |
| `id` | `uuid` | Primary key. |
| `email` | `citext` | Case-insensitive email-as-username, unique. |
| `password_hash` | `text` | Argon2id PHC string. |
| `totp_secret_enc` | `bytea` | AES-256-GCM encrypted TOTP secret, never plaintext. |
| `totp_enabled` | `bool` | Default false. |
| `totp_recovery_code_hashes` | `jsonb` | Hashes of recovery codes. |
| `is_admin` | `bool` | Default false. Gates privileged actions. |
| `must_change_password` | `bool` | Default false; true for a bootstrapped admin. |
| `password_changed_at` | `timestamptz` | Stamped on password change. |
| `disabled` | `bool` | Default false. |
| `created_at`, `updated_at` | `timestamptz` | Audit timestamps. |

There are deliberately no failed-login or lockout columns. Those counters are
security-ephemeral and live in Redis.

### scopes

The runtime-managed scope catalog.

| Column | Type | Notes |
| --- | --- | --- |
| `name` | `text` | Primary key. |
| `description` | `text` | Default empty. |
| `enabled` | `bool` | Default true. |
| `created_at`, `updated_at` | `timestamptz` | Audit timestamps. |

The enabled rows are the single source of truth for `scopes_supported` in the
RFC 8414 metadata document, read on each discovery request.

### user_scopes

Per-user scope entitlements. New users start with none.

| Column | Type | Notes |
| --- | --- | --- |
| `user_id` | `uuid` | FK to `users(id)`, cascade delete. |
| `scope_name` | `text` | FK to `scopes(name)`. |
| `granted_at` | `timestamptz` | Grant time. |
| `granted_by` | `uuid` | Optional granting admin. |

Composite primary key `(user_id, scope_name)`.

### redirect_uris

The runtime-managed redirect-URI catalog. `uri` is stored normalized-exact and
is unique per `(uri, kind)`.

| Column | Type | Notes |
| --- | --- | --- |
| `id` | `uuid` | Primary key. |
| `uri` | `text` | Unique per `kind`. |
| `kind` | `text` | `authorize` (default) or `post_logout`. |
| `enabled` | `bool` | Default true. |
| `description` | `text` | Default empty. |
| `created_by` | `uuid` | Optional creator. |
| `created_at`, `updated_at` | `timestamptz` | Audit timestamps. |

`kind` separates the two browser landings: `authorize` URIs are valid
authorization-response callbacks (they carry `code`/`state`); `post_logout` URIs
are bare navigation targets used only by `GET /logout`. Both kinds are matched
with the same exact-string + RFC 8252 loopback rule.

### client_redirect_uris

The join between a client and a catalog redirect URI.

| Column | Type | Notes |
| --- | --- | --- |
| `client_id` | `text` | FK to `oauth_clients(client_id)`, cascade delete. |
| `redirect_uri_id` | `uuid` | FK to `redirect_uris(id)`, cascade delete. |
| `attached_at` | `timestamptz` | Attachment time. |
| `attached_by` | `uuid` | Optional attacher. |

Composite primary key `(client_id, redirect_uri_id)`.

## The citext extension

The `users` migration runs `CREATE EXTENSION IF NOT EXISTS citext`. This makes
email comparison case-insensitive, so `A@x.com` and `a@x.com` collide and there
is one account per email regardless of case. Installing `citext` requires
superuser or trusted-extension privileges, so the migration role must have
them, or an operator must install the extension out of band before migrating.

## Migration lifecycle

- Migrations are versioned, forward-only sqlx migrations compiled into the
  binary by `sqlx::migrate!("./migrations")`. They are tracked in
  `_sqlx_migrations`.
- They are applied by the `migrate` subcommand, which runs
  `sqlx::Migrator::run` under sqlx's Postgres advisory lock. Concurrent
  migration runs serialize on that lock, so a cluster Job cannot double-apply.
- `serve` never migrates. Startup only reads the schema. Readiness returns 503
  until every embedded migration version is present in `_sqlx_migrations`.
- A checksum mismatch or a dirty migration fails fast rather than guessing.
- Each file is deterministic with no down path. There is no `DROP` and no
  rollback.

In a cluster, run migrations from a dedicated Job, never from every replica.

## Expand and contract

Rolling upgrades keep version N-1 and N replicas serving at once. A migration
must be backward compatible:

1. Expand: add the new column or table in a way the old code ignores.
2. Deploy: roll out the new code that uses it.
3. Contract: remove the old column or table only after no old replica remains.

A migration that drops or renames a column in one step breaks the old replicas
during the rollout. Do not do it.

## Security properties

- Only hashed or encrypted secrets are stored: client secrets in
  `oauth_clients`, passwords in `users`, TOTP secrets encrypted in `users`.
  Raw tokens are never rows here.
- The `registration_access_token_hash` column is the one token hash in
  Postgres, so RFC 7592 client management survives a Redis loss.
- Foreign keys with `ON DELETE CASCADE` keep the join tables consistent, and a
  scope referenced by `user_scopes` is disabled rather than deleted.

## Edge cases

- Deleting a scope or redirect URI that is still referenced does not cascade
  away the reference. The CLI disables the catalog row instead, and
  `scope delete` falls back to disable when `user_scopes` references it.
- A client with a `tls_client_auth_subject_dn` or a thumbprint that another
  client already uses is rejected by the partial unique index.
- `citext` must exist before the `users` migration runs.

## Operational runbook

Apply migrations once, before rolling out replicas:

```bash
D_OAUTH_DATABASE__URL='postgres://oauth@db.example.com/oauth?sslmode=verify-full' \
  cargo run --bin d-oauth-server -- migrate
```

The command prints `applied N migration(s)`. Run it as a cluster Job and let
readiness gate the Deployment. To confirm a fresh install, check that the six
tables exist and are empty:

```sql
SELECT table_name FROM information_schema.tables
WHERE table_schema = 'public' ORDER BY table_name;
SELECT version, success FROM _sqlx_migrations ORDER BY version;
```

## File and directory references

- `migrations/1791072000_oauth_clients.sql` for `oauth_clients`.
- `migrations/1791158400_users.sql` for the other five tables and `citext`.
- `src/db/` for the repositories.
- `src/state.rs` for `MIGRATOR`.
- `src/cli/ops.rs` for the `migrate` command.
- `src/state/health.rs` for the readiness schema check.