# Database

The gateway uses PostgreSQL for request logs, user accounts, API keys, and every
admin/runtime setting that outlives a restart. This page covers what is stored,
how the schema comes into existence, and how to back it up, restore it, and reset
it.

## Is a database required?

No. `DB_ENABLED` defaults to `true`, but a gateway started with `DB_ENABLED=false`
routes requests normally — it simply has no request history, no user accounts, no
API-key issuance, and no admin surfaces backed by stored settings.

The distinction matters when reading `/health`, because the two "no database"
states are not the same answer:

```json
{"status": "healthy",   "routes_configured": 3, "database_configured": false, "database_connected": false}
{"status": "unhealthy", "reason": "database_unavailable_at_startup", "database_configured": true, "database_connected": false}
```

The first is a deployment that asked for no database. The second asked for one
and did not get it, and reports unhealthy so a load balancer takes it out of
rotation.

## Connection settings

Read by `Settings` in `apps/backend/serving/config/settings.py`, from `.env` or
the process environment.

| Variable | Default | Notes |
|---|---|---|
| `DB_ENABLED` | `true` | Any of `false` / `0` / `no` disables the database entirely. |
| `DB_HOST` | `localhost` | The Compose stack overrides this to `postgres` inside the backend container. |
| `DB_PORT` | `5432` | In Compose this is the **host** port mapping; the container always talks to 5432. |
| `DB_NAME` | `hybridinference` | Required by Compose (`DB_NAME must be set in .env file`). |
| `DB_USER` | `postgres` | Required by Compose. |
| `DB_PASSWORD` | *(empty)* | Required by Compose. |
| `DB_STORE_FULL_CONTENT` | `false` | Whether prompts and responses are stored verbatim. See [Request logging and privacy](#request-logging-and-privacy). |
| `ERASURE_FENCE_SECRET` | *(empty)* | Secret for the records that keep deleted accounts deleted. Set it once and never change it; see [Secrets for deleting accounts](#secrets-for-deleting-accounts). |
| `ERASURE_FENCE_PROTOCOL_READY` | `false` | Turns on permanent account deletion. See [Secrets for deleting accounts](#secrets-for-deleting-accounts). |

The bundled stack (`deploy/docker/docker-compose.yml`) runs `postgres:16`,
initialised with `-E UTF8 --locale=C.UTF-8`, and publishes it on
`127.0.0.1:${DB_PORT:-5432}` — loopback only. Reach it from another machine with
an SSH tunnel, not by widening that binding.

## How the schema is created

The application creates and migrates its own schema **at startup**, and a
restart leaves in place whatever already exists; there is no separate
migration tool to run. Every statement is `CREATE TABLE IF NOT EXISTS`, so the builders
below overlap harmlessly where two of them define the same table:

| Code | Creates |
|---|---|
| `ensure_api_logs_schema` in `apps/backend/serving/storage/log_schema.py` | `api_logs` and `api_stats_hourly`, with their columns and indexes |
| `DatabaseLogger._create_tables` in `apps/backend/serving/storage/database.py` | Calls the above, then the auth/admin tables |
| `PostgresOperationalStore.initialize` in `apps/backend/serving/storage/postgres_operational.py` | The operational tables (settings, overrides, provider registry) |
| `ResponseStore.initialize` in `apps/backend/serving/storage/responses_store.py` | `openai_responses` |
| `apps/backend/serving/grants.py` and `apps/backend/serving/admin/geo_demand_rollup.py` | `agent_grants`; the `geo_hourly_*` rollup tables |

Pointing a gateway at an empty database is therefore all the "migration" there
is: start it and the tables appear.

Two properties are worth knowing before you operate this:

**A restart only changes what is missing.** Each startup first checks which
columns and indexes already exist and issues only the `ALTER`/`CREATE INDEX`
statements that are actually missing, so a steady-state restart takes no
strong table locks. This matters
because `ALTER TABLE` acquires `ACCESS EXCLUSIVE` *before* Postgres evaluates
`IF NOT EXISTS`, and a queued exclusive lock parks every reader behind it.

**A migration that cannot get its lock is deferred, not fatal.** The DDL phase
runs under a 3-second `lock_timeout` and raises `SchemaLockUnavailable` rather
than waiting; the caller retries it in the background and startup proceeds. The
usual lock holder is a long-running `pg_dump`, which can hold `ACCESS SHARE` over
`api_logs` for hours. If you take backups on a schedule, expect an occasional
deferred-migration line in the log after a deploy that adds a column.

If you are contributing a column to `api_logs`, add it in `log_schema.py` only;
that is the one place the request log's schema is defined.

### Secrets for deleting accounts

When an admin permanently deletes a user, the gateway also records a keyed
fingerprint of that account in the `erasure_fence` table, so a request-log
write that was already on its way cannot put the user's rows back. Two
settings govern this:

- `ERASURE_FENCE_SECRET` is the key for those fingerprints. The first time
  the gateway starts against a database, it records a fingerprint of this
  secret — even before anything has been deleted — and from then on it refuses
  to start if the secret differs. So set it to its own random value before
  that first start, keep it across restarts and replicas, and never change it.
  Left empty, it uses `API_KEY_SECRET` instead, and that value is the one
  recorded; changing `API_KEY_SECRET` later then stops the backend too. If you
  hit a mismatch, restore the original value; never delete fence rows or their
  metadata to get past it.
- `ERASURE_FENCE_PROTOCOL_READY=true` turns permanent deletion on. Leave it
  `false` until every process that writes `api_logs` runs a version that checks
  the fence.

## What the tables hold

| Group | Tables | Holds |
|---|---|---|
| Request history | `api_logs`, `api_stats_hourly`, `provider_hourly_stats` | One row per request (model, provider, tokens, latency, TTFT, status, cost) plus hourly rollups used by the dashboards |
| Accounts and auth | `users`, `api_keys`, `auth_sessions`, `login_events`, `email_verification_tokens`, `password_reset_tokens`, `identity_auth_codes` | User records, hashed API keys and their quotas, refresh sessions, sign-in history |
| Admin actions | `admin_audit_log`, `signup_allowed_domains`, `site_settings`, `site_updates`, `email_broadcasts`, `email_broadcast_recipients` | Audited admin changes, signup policy, runtime settings, announcements, broadcast delivery state |
| Runtime routing overrides | `provider_definitions`, `provider_api_keys`, `provider_route_configs`, `provider_route_candidates`, `provider_weight_overrides`, `disabled_providers`, `disabled_provider_env_keys`, `provider_env_key_min_roles`, `model_visibility_overrides`, `model_concurrency_exemptions` | Everything the admin console can change about routing without editing YAML — see [Runtime configuration from the admin console](configuration.md#runtime-configuration-from-the-admin-console) |
| Cost and quota | `user_daily_cost` | Per-user daily spend used for quota enforcement |
| RouteWise | `routewise_probe_samples`, `routewise_probe_leases` | Latency probe samples and the lease that stops two workers probing at once |
| Responses API | `openai_responses` | Stored `/v1/responses` state, when content storage is enabled |
| Geo analytics | `geo_hourly_coverage`, `geo_hourly_demand` | Hourly per-country request and token counts; aggregate only, no IP addresses stored |
| Agent grants | `agent_grants` | Short-lived, model-scoped capabilities this gateway mints for an external agent control plane |
| Account deletion | `erasure_fence` | Keyed fingerprints of permanently deleted accounts; see [Secrets for deleting accounts](#secrets-for-deleting-accounts) |

`\dt` on a live database lists every table.

## Request logging and privacy

`DB_STORE_FULL_CONTENT` defaults to `false`, and that default is not a redaction
of the stored text — the text is never written. With it off, `api_logs.prompt`,
`api_logs.response` and `api_logs.request_payload` are inserted as `NULL`, and
`/v1/responses` state is not persisted.

Derived, non-content columns are recorded either way, because the dashboards read
them instead of de-TOASTing payloads: token counts, cost, latency and TTFT, the
conversation shape (`num_turns`, `num_user_turns`, `num_tool_calls`), and a
fingerprint of the newest user message (`last_user_msg_chars`,
`last_user_msg_entropy`, `last_user_msg_hash`).

Turning it on stores full prompts and responses. Weigh that against your users'
expectations before you do.

### Which session a request belongs to

`api_logs.session_id` groups one conversation's requests together. A client can
set it with the gateway's `X-Session-ID` header, but coding agents do not send
that header. Each carries a session id of its own instead, and the gateway
reads whichever one the request has:

| Read from | Sent by |
|---|---|
| `X-Session-ID` header | anything speaking the gateway's own contract; wins whenever present |
| `session-id` / `thread-id` headers (the `session_id` / `conversation_id` spellings too) | Codex CLI, which stamps its run on every request |
| `x-session-affinity` / `x-opencode-session` headers | OpenCode and its Kilo Code fork, which send the affinity header beside `X-Session-ID` on any provider they do not recognise as their own, and `x-opencode-session` on one they do |
| `metadata.session_id` or `client_metadata.session_id` in the request body | a client that labels the session where it labels everything else; Codex uses `client_metadata` |
| `x-claude-code-session-id` header | Claude Code, on every request |
| `metadata.user_id` in the request body | Claude Code, which packs the device, account and run into Anthropic's one `user_id` field. Two shapes — see below |

Since version 2.1.78, Claude Code sends `metadata.user_id` as a JSON object —
`{"device_id": …, "account_uuid": …, "session_id": …}` — and before that as a
`user_<hash>_account_<uuid>_session_<uuid>` string. The gateway reads both.
It does not use `parent_session_id`, which a subagent run carries: that names
the session that started the subagent, not this one.

Some OpenCode versions and the Kilo Code fork send no session id on their newer
request path. OpenCode fixed this upstream (`sst/opencode#43188`); until Kilo
Code takes the same fix, its requests are recorded without a session.

The admin console's Recent Requests view shows the session under each row's
client, and clicking it filters the list to that one conversation.

`metadata.session_id_source` on the same row names which of those sources the
session came from. Every source is client-supplied, and
nothing is authorized, billed or rate-limited by a session id; a declaration
over 128 characters, or one carrying control characters, is dropped rather than
recorded. Like the derived columns above, the session is recorded whether or not
`DB_STORE_FULL_CONTENT` is on: it is a label the client put on the request, not
part of the conversation.

## Backup

`pg_dump` in custom format, straight out of the container:

```bash
docker exec hybridinference-postgres \
  pg_dump -U "$DB_USER" -d "$DB_NAME" -Fc > hybridinference-$(date +%F).dump
```

Do not add `-t` to `docker exec`: allocating a TTY corrupts the binary stream.

Restore into an existing, running database:

```bash
docker exec -i hybridinference-postgres \
  pg_restore -U "$DB_USER" -d "$DB_NAME" --clean --if-exists < hybridinference-2026-01-01.dump
```

Stop the backend with `docker stop hybridinference-backend` before restoring
over a live database; `make down` would stop Postgres too. The backup contains
hashed API keys and, if `DB_STORE_FULL_CONTENT` was ever on, user prompt
content, so store it accordingly.

## Reset

Deleting the database is more awkward than it looks, because the Postgres volume
is declared `external:` in `deploy/docker/docker-compose.yml`:

```yaml
volumes:
  postgres_data:
    external: true
    name: hybridinference_postgres_data
```

**`docker compose down --volumes` does not delete an external volume.** Nor does
`make down`, which passes no `--volumes` at all. A reset that relies on either
silently leaves every row in place. Delete the volume by name:

```bash
make down
docker volume rm hybridinference_postgres_data    # destroys all data
make up                                           # recreates an empty volume
```

`make up` depends on the `docker-volumes` target, which recreates the named
volume if it is missing, so the stack comes back on an empty database and the
startup initialisers rebuild the schema. Take a dump first if there is any chance
you want the data back.

The runnable example (`make demo-reset DISTRIBUTION=example`) uses its own,
example-scoped volume and cannot delete `hybridinference_postgres_data`.

## Inspecting the database

```bash
docker exec -it hybridinference-postgres psql -U "$DB_USER" -d "$DB_NAME"
```

Useful starting points: `\dt` for the table list and `\d api_logs` for the request
log's columns. `make ps` shows the published bindings — Postgres and pgAdmin
should both read `127.0.0.1:...`; anything else means the database is listening
beyond this host.

## Optional: pgAdmin

The Compose stack ships a pgAdmin service for people who prefer a GUI. It is
**profile-gated** — nothing starts it unless the `admin` profile is named — and
it is entirely optional; `psql` above does everything.

### Starting and restarting it

The profile must be on *every* Compose command, not just the first:

```bash
make up   COMPOSE_PROFILES=admin
make down COMPOSE_PROFILES=admin
```

Omitting it does not fail loudly, it just does the wrong thing in both
directions: a plain `make up` starts every other service and skips pgAdmin, and a
plain `make down` leaves the pgAdmin container running while removing everything
around it (Compose then reports the network as still in use). Pass
`COMPOSE_PROFILES=admin` on both halves of a restart.

### Authentication

Two separate gates can protect pgAdmin, and by default only the console's is on:

- `PGADMIN_CONFIG_SERVER_MODE` defaults to `False`, which serves pgAdmin with **no
  login of its own**. Set it to `True` in `.env` and pgAdmin asks for
  `PGADMIN_EMAIL` / `PGADMIN_PASSWORD` (which themselves default to
  `admin@local.dev` / `admin` — change them before enabling this).
- Reached through the console at `/pgadmin/`, the request is gated on an admin
  session by a Next.js route handler
  (`apps/frontend/src/app/pgadmin/[[...path]]/route.ts`), which asks the backend
  to verify the caller. Reached directly on its published port
  (`127.0.0.1:${PGADMIN_PORT:-5050}`, loopback only), that gate does not apply —
  use an SSH tunnel:

  ```bash
  ssh -L 5050:127.0.0.1:5050 <user>@<your-gateway-host>
  ```

pgAdmin's *master password* prompt never appears:
`PGADMIN_CONFIG_MASTER_PASSWORD_REQUIRED` is pinned to `"False"` in the Compose
file with no environment variable to change it.

### Registering the database

1. `Servers` → right-click → `Register` → `Server`.
2. **General**: any name.
3. **Connection**: host `postgres`, port `5432`, maintenance database `DB_NAME`,
   username `DB_USER`, password `DB_PASSWORD` — the container-internal values,
   not the host port mapping.

### Resetting pgAdmin

Its saved connections live in `hybridinference_pgadmin_data`, an ordinary
project-local volume (not external, unlike the Postgres one):

```bash
make down COMPOSE_PROFILES=admin
docker volume rm hybridinference_pgadmin_data     # destroys saved connections only
make up   COMPOSE_PROFILES=admin
```