> ## Documentation Index
> Fetch the complete documentation index at: https://omniflow-vpn.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Database changes

> The schema-first Atlas and sqlc workflow, how a migration is reviewed and applied, and which tables may never be rewritten

PostgreSQL is the only durable source of truth in Omniflow. Valkey holds cache, rate limits, locks,
and temporary state, and nothing that matters survives only there. That makes a schema change one of
the few edits in this repository that is genuinely irreversible once released, which is why the
workflow below is prescriptive rather than a suggestion.

Three artefacts move together:

| Artefact | What it is |
| - | - |
| `database/schema.sql` | The **current shape**: a schema-only dump of the database the migrations build, generated by `tools/schema-snapshot.sh`. Read it to see a table without replaying thirty files; never edit it by hand. CI fails when it is stale. |
| `database/migrations/` | The **released history**: one timestamped `.sql` file per change, plus `atlas.sum`. This is what every installation replays, in order, forever. |
| `database/queries/` | The **query layer** sqlc compiles into `internal/database/dbgen`. |

The order is always the same, and it is fixed by `AGENTS.md`: write a reviewed migration, apply it
against a real PostgreSQL, refresh the schema snapshot, then regenerate sqlc.

## The workflow

**1. Write the migration**

Add a file to `database/migrations/` with the existing `<UTC timestamp>_<slug>.sql` naming and
run `make atlas-hash`. Most changes here are written by hand — replacing a `CHECK` constraint,
adding a column with a backfill — because that states the intent a diff cannot. If you have
`atlas login`, `make atlas-diff` can draft one instead: edit `database/schema.sql` into the shape
you want and Atlas writes the difference against the history. Either way, the file you commit is
one you have read.

**2. Review the SQL**

Read every line before you keep it. What you are looking for is on
[what a review is looking for](#what-a-review-is-looking-for) below.

**3. Apply it against a real PostgreSQL**

```bash theme={null}
export APP_DATABASE_URL=postgres://omniflow:omniflow@localhost:5432/omniflow?sslmode=disable
make atlas-apply   # atlas migrate apply --env local
```

A migration that only compiles is unverified. Name collisions, constraint conflicts, and
column-order mistakes appear at apply time and nowhere else, which is why `AGENTS.md` requires
this step before the file is committed rather than after CI reports it.

**4. Refresh the schema snapshot**

```bash theme={null}
make schema-snapshot   # sh tools/schema-snapshot.sh
```

It applies every migration to a throwaway PostgreSQL 18 and dumps the schema with that server's
own `pg_dump`, so the result does not depend on your local client tools. A diff draft from the
previous step is overwritten with the real shape.

**5. Regenerate sqlc**

```bash theme={null}
go tool sqlc generate
```

Or `make generate`, which also refreshes the OpenAPI-derived Go and TypeScript bindings.

**6. Commit the whole set together**

The migration, `atlas.sum`, the schema snapshot, the regenerated `internal/database/dbgen`, the
queries, and the tests belong in one commit. Splitting them produces a revision of the
repository that cannot be built or migrated.

> [!NOTE]
>
> `make atlas-diff` needs `atlas login`: on Atlas 1.3 the desired-state loader answers
> `extensions are available to logged-in users only`, because the schema opens with
> `CREATE EXTENSION`. Hashing, validating, and applying need no login, which is why hand-authored
> migrations are the default here.

## Hand-authored migrations

A migration may be written directly when the change is clearer that way than diffed — replacing a
`CHECK` constraint rather than adding one is the usual case, because a diff expresses it as a drop
and a create with no statement of intent between them.

1. Add the file to `database/migrations/` using the existing `<UTC timestamp>_<slug>.sql` naming.
2. Run `make atlas-hash` (`atlas migrate hash --dir file://database/migrations`).
3. Review it exactly as you would a generated one, and apply it against a real database.
4. Run `make schema-snapshot` and commit the regenerated `database/schema.sql` with it.

Neither step needs the Atlas CLI on your machine. Both run from the same image the compose `migrate`
service uses, which is also how the race detector and sqlc are run when a local toolchain cannot:

```bash theme={null}
docker run --rm -v "$PWD/database/migrations:/migrations" arigaio/atlas:1.3.0 \
  migrate hash --dir file:///migrations
```

Every migration in the repository opens with a prose header explaining what it adds, what it
deliberately does not add, and which of its tables are append-only. Follow that convention: the
header is the only place a future reader learns why a column exists, and it is read far more often
than the commit message.

## The `atlas.sum` rule

`database/migrations/atlas.sum` is generated **only** by `atlas migrate hash`. Never hand-edit it,
never recompute an entry by hand, and never paste a checksum from a CI log.

A checksum mismatch is not a stale file. It means a migration's bytes changed after it was hashed —
which, for a file that has already been released, means an installation somewhere has applied
different SQL from the one you are holding. Atlas refuses to apply a directory whose sum does not
match, and that refusal is the whole point.

If Atlas is not available on your machine, stop and say so. Writing the file yourself defeats the
only mechanism that detects a rewritten migration.

## What a review is looking for

Read a migration — yours or somebody else's — against this list.

* **Destructive operations.** A `DROP COLUMN` or `DROP TABLE` cannot be undone by a later
  migration, and the data is gone from every installation that applies it.
* **Locks and rewrites.** A column added with a volatile default, a type change, or an index built
  without `CONCURRENTLY` takes a lock for the duration on a table that may be large in production.
* **Constraint names.** Name a table-level constraint explicitly and uniquely. PostgreSQL derives
  `<table>_<column>_check` from an inline column check, so an explicit constraint reusing that name
  fails to apply — and it fails at apply time on a populated database, not at diff time.
* **Backfills.** A migration that adds a `NOT NULL` column to a populated table needs a default or a
  backfill statement in the same file; the CI matrix applies every migration on top of every
  supported baseline, so an unbackfilled column fails there rather than in review.
* **Whether it belongs at all.** A table, query, or handler nothing reaches is unfinished, not
  staged. Wire the capability end to end in the same change.

Never edit a migration that has been included in a release, and never generate a diff during
production startup. Production applies committed migrations only.

## sqlc and direct queries

`database/queries/` holds the sqlc-generated data access used by the domain services. Its schema
input is `database/migrations` — not `database/schema.sql` — so sqlc type-checks every query against
the history exactly as an installation replays it. A query that references a column no migration
adds is a generation error rather than a runtime one, and that is deliberate.

Customer-surface code that reads only its own projections — the bot's catalogue, checkout, support,
and news screens — uses direct pgx queries in the same package as the surface, which is the pattern
established by the bot store. Add a query to `database/queries/` and regenerate whenever the result
belongs to a shared domain service rather than to one surface.

Generated output under `internal/database/dbgen` is committed and is never edited by hand. CI
regenerates it and fails on a diff.

## Append-only tables

Financial, audit, identity, webhook, and outbox records are append-only unless a documented
lifecycle explicitly permits mutation. In practice:

* **The wallet ledger has no update or delete path anywhere in the codebase.** A correction is a
  compensating entry, because "this was granted and then taken back, by this operator, for this
  reason" is a fact worth keeping and an edited row destroys it. See
  [Financial invariants](https://omniflow-vpn.mintlify.site/commerce/financial-invariants.md).
* **The audit trail** grows for the life of the installation. Retention removes disposable rows and
  never an audit event.
* **Entitlement history, consent records, provider events, and fulfillment attempt history** follow
  the same rule. Where a lifecycle column is allowed to move — a cart's status, a session's revoked
  timestamp — the migration's header says so on the table.

When you add a table, decide which of these it is and write the decision into the header comment.
A table whose mutability nobody stated becomes a table somebody edits.

> [!NOTE]
>
> `outbox_events` is written by three publishers — `order.paid`, `wallet.credited`, and
> `addon.purchased` — and nothing in the repository ever sets `published_at`. The retention sweep
> deletes only rows where `published_at IS NOT NULL`, so it removes nothing and the table grows
> without bound. If you are adding a publisher, this is the state you are adding to.

## River's schema ships as an Atlas migration

The durable job tables (`river_job`, `river_leader`, `river_queue`, `river_notification`,
`river_migration`) are created by `20260905130000_river_queue.sql`. That file is River v0.43.0's own
`main` migration line, versions 1 through 7, copied verbatim from `riverpgxv5` with the schema
template removed, followed by the `river_migration` rows `rivermigrate` itself would write. Its whole
body is skipped when `river_migration` already exists, so an installation that created the tables by
hand with the River CLI keeps them. See [Jobs and queues](https://omniflow-vpn.mintlify.site/operations/jobs-and-queues.md#the-queue-schema-ships-with-the-migrations).

To upgrade River:

1. Bump the River module.
2. If the new release adds schema versions, add a new Atlas migration with each new version's up SQL
   from River's own migration line, schema template removed, followed by its `river_migration` row.
3. Run `make atlas-hash`.

`TestRiverSchemaShipsWithTheMigrations` in `internal/integrationtest/harness_test.go` runs
`rivermigrate` against the migrated database and fails if it reports an unapplied River version, so
a bump without the matching migration fails CI.

## How migrations reach an installation

The compose `migrate` service runs `arigaio/atlas:1.3.0` with
`migrate apply --url postgres://omniflow:omniflow@postgres:5432/omniflow?sslmode=disable --dir file:///migrations`,
mounting `database/migrations` read-only. The API, bot, and worker all declare
`depends_on: migrate: service_completed_successfully`, so no process serves traffic against a
database behind head.

That DSN is hard-coded in `compose.yaml` and ignores `APP_DATABASE_URL`. Changing the database
credentials means editing `compose.yaml` as well as `.env`, or the services connect somewhere the
migration step never touched.

Atlas records which revisions have been applied in its own `atlas_schema_revisions` schema. A
restore into a fresh database has to carry that schema too, or Atlas will try to replay the history
from the beginning against a database that already has it — worth knowing when you follow
[Backup and restore](https://omniflow-vpn.mintlify.site/operations/backup-restore.md).

## What CI proves

The `migrations` job runs `atlas migrate validate` and then, once per supported baseline, applies
the baseline, upgrades to head, and applies head a second time to prove it is a no-op. The baselines
are the empty database plus each released prefix of the history, so the job answers the question a
review cannot: can an installation running any shipped version upgrade to this change?

The Testcontainers suite under `internal/integrationtest` applies the same committed files in order
against a real PostgreSQL before exercising the repositories, which makes it a second migration gate.
Details are on [Testing strategy](https://omniflow-vpn.mintlify.site/contributing/testing.md).


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.