Postgres in place: a database per stack, migrations, backup and Drizzle Gateway

root commented

2026-10-11 13:42:22 +00:00

(Migrated from gitlab.core-project.space)

Imported from GitHub amaurycoudr/chess-project#354, opened by @amaurycoudr on 2026-10-11 13:42 UTC.

Problem Statement

Nothing the app knows outlives a redeploy: Rooms live in memory (ADR 0029), and no stack has a database. Every account feature on the map needs one, starting with Guest history, which stores decided Online games. Production and up to five previews share one VPS, so a database must fit the deploy scripts as they are, keep preview data apart from production, survive the VPS being lost, and be inspectable and editable by the owner without exposing a port. Contributors, and the agents working in parallel worktrees, need the same database locally without stepping on each other.

Solution

Every stack (production and each preview) gets its own Postgres 18 container, private to that stack, its data in a volume the existing preview teardown already removes (ADR 0032). A one-shot migrate step applies the committed Drizzle migrations (ADR 0031) before the API container is replaced; a failed migration fails the deploy and leaves the old API serving. The API refuses to start without a reachable database. Production is dumped nightly, checked by restoring it, encrypted, and kept off the VPS. A self-hosted Drizzle Gateway on the VPS joins every stack's network and is reached only through an SSH tunnel; it edits production through a role that reads and writes rows but cannot change the schema. Locally, the API's tests run on an in-process Postgres; dev and the browser suite use a real Postgres container, one database per worktree.

This spec ships the tooling and an empty schema. No table: Guest history adds the first one. Nothing a Player sees changes.

User Stories

As the owner, I want each stack to run its own Postgres, so that a preview's migrations or load never touch production's data.
As the owner, I want no Postgres port published, not even on the VPS loopback, so that a stack's database is reachable only from that stack and the Gateway.
As the owner, I want a preview's database removed with its stack when the PR closes, is reaped or is pushed out by the preview cap, so that no preview data outlives its preview.
As the owner, I want preview databases capped in memory and connections, so that five previews never crowd out production on the one VPS.
As the owner, I want each stack's data in a volume at the path Postgres 18 expects, so that a redeploy or a minor image upgrade keeps it.
As the owner, I want migrations applied to completion before the API container is replaced, so that a failed migration fails the deploy while the previous API keeps serving.
As the owner, I want a rerun of the migrations to change nothing, so that every redeploy can run them safely.
As a contributor, I want migrations to be committed SQL that drizzle-kit generates from the schema, so that a schema change is reviewed as a diff and the same files run in tests, previews and production.
As a contributor, I want the gate to fail when the schema has a change no committed migration captures, so that a forgotten generate never reaches a deploy.
As the owner, I want the database passwords of production to come from GitHub secrets and never be committed, so that the repo holds no production credential.
As the owner, I want a password delivered to its container without showing in the container's metadata, where the mechanism allows it, so that inspecting a container leaks no credential.
As the owner, I want a nightly dump of production, checked by a restore and encrypted to a key only I hold, kept off the VPS, so that losing the VPS loses at most a day of data.
As the owner, I want a written restore procedure, so that a backup can actually be used under pressure.
As the owner, I want one Drizzle Gateway on the VPS, its version pinned and its port published on the loopback only, so that I can browse every stack's data through an SSH tunnel with no public route.
As the owner, I want the Gateway to join a stack's network when the stack deploys and leave it before the stack is removed, so that every running stack is browsable and teardown still leaves nothing behind.
As the owner, I want the Gateway to connect with a role that reads and writes rows but cannot change the schema, so that I can fix data by hand without bypassing migrations.
As the owner, I want the Gateway's licence terms checked for the pinned version before it is deployed, so that the setup is used within its terms.
As a contributor, I want the API's tests to run against an in-process Postgres with the real migrations, so that the gate and the pre-commit hook need no Docker and stay parallel.
As a contributor, I want the API to refuse to start when its database URL is missing, so that a setup mistake fails loudly at boot rather than at the first query.
As the owner, I want the API to refuse to start when its database does not answer, so that a stack that cannot reach its database never turns healthy and its deploy fails.
As a contributor, I want pnpm dev and the local browser suite to run against a real Postgres, with one database per worktree, so that several worktrees run side by side without sharing data.
As the owner, I want CI's browser suite to run the built API against a real, migrated Postgres, so that the production driver is exercised before a merge.

Seams

The API's database module (connect and migrate), integration tests against an in-process Postgres: an empty database reaches the latest migration; a second run changes nothing; the schema generates no migration that is not committed; an unreachable database is refused with a message naming the setting. Covers 7, 8, 9, 18, 20.
loadConfig, unit tests beside the existing config tests: the database URL is required and parsed, and the password-file setting overrides the URL's password. Covers 10, 19.
The deploy and backup verification scripts, run by the workflows against the VPS, as the existing verify, verify-gone, verify-reaped, verify-cap and verify-kept scripts are today. After a deploy: the stack runs a healthy app and a healthy database and nothing else, the migrate step exited 0 and the migration table lists every committed migration, no database port is published, the volume is mounted at the Postgres 18 path, preview caps are in effect on a preview and absent on production, the Gateway (when running) is on the stack's network, the Gateway role can read but cannot create a table, and no password shows in the container's metadata. After a removal: nothing of the stack is left, the Gateway's attachment included. After a backup: the dump restored into a throwaway Postgres. Covers 1–6, 11–16. Story 17 is a written check in the Gateway PR; story 6's failure path is a one-time drill on a preview, recorded in its PR.
The existing browser suite, unchanged, no new test: CI runs it against the built API and a real migrated Postgres, and locally it runs against the worktree's database. Covers 21, 22.

No new e2e test: a Player perceives nothing of this.

Implementation Decisions

Governing ADRs. ADR 0031 (Drizzle v1 RC pinned exactly, committed SQL migrations, the migrator in production, no repository abstraction) and ADR 0032 (a Postgres container per stack, no published port, a one-shot migrate step, one Gateway joining every stack, a no-DDL Gateway role) are accepted and govern this work. ADR 0029 stands. No new ADR is needed. One is worth writing if the owner agrees: "API tests run on an in-process Postgres, dev, CI and stacks on a real one", since every later spec inherits the choice.

Database per stack

The stack's Compose file gains a database service: Postgres 18 pinned to an exact minor, a named volume at the path Postgres 18 uses, a pg_isready healthcheck, and the shared-memory size the image's docs recommend.
The database sits on a new internal network of the stack, never on the proxy network, and publishes no port. The app joins that network beside the proxy network.
Preview caps live in an overlay the up script layers on every preview stack (not tied to the noindex flag): a small shared_buffers, about 20 connections and a container memory limit. Production keeps Postgres's defaults.
The verify script's "exactly one container" check becomes "a healthy app and a healthy database". The removal, cap and reaper scripts already remove and check volumes; only the Gateway detach is new.

Migrations

drizzle-orm and drizzle-kit at the same exact RC (ADR 0031); production driver node-postgres. drizzle-kit is a dev dependency.
Migrations are generated into a folder of the API package and committed. The image copies that folder beside the API's build output. This spec ships one baseline migration with no table, so the whole path runs end to end.
The migrate step runs the same image with a migrate entry point instead of the server, on the stack's internal network. The up script brings the database up and waits for it healthy, runs the migrate step to completion, and only then replaces the app. A failure stops the script before the app is touched, so the old container keeps serving without relying on Compose's dependency ordering.
The migrate entry point also ensures the Gateway role, idempotently: it creates the role if missing, sets its password from the stack's secret, and grants row privileges on the app role's tables, plus default privileges so future tables follow. It runs on every stack, so previews exercise production's path. No password goes into committed SQL, and no init script is mounted, since Compose runs on the runner and a bind mount would name a path on the VPS.
Locally, the API's dev script and the browser suite's API command run the migrate entry point before the server. The production server never migrates on boot (ADR 0032).

API wiring (within the API's existing layer order and framework-import rules, so no lint change)

A framework-free database module in the API's shared folder: it builds the Drizzle handle from the config, checks the database answers, migrates from the committed folder, and closes the pool.
The app module receives the database handle as an option and provides it under a token, as it does the allowed origins; the room module does not use it yet. The boot entry point connects, checks, then listens; a stopped server closes the pool.
The migrate entry point is a second root entry beside the boot one.
The in-process app fixture used by the wire tests passes an in-process database, so the existing wire tests keep their one-line setup.

Roles and secrets

Two roles per stack: the app role, which owns the schema and is used by the migrate step and the app; and the Gateway role, row privileges only.
Production passwords for both roles are GitHub secrets passed to the production deploy. Preview passwords are fixed throwaway values in the preview overlay, since a preview database is reachable only from its own network and the Gateway.
Delivery: try first a Compose secret sourced from a runner environment variable; Postgres reads it through its password-file variable, and the API through a password-file setting that overrides the URL's password. Verify on a preview that it arrives and stays out of the container's metadata. If it does not work against a remote daemon, fall back to plain environment variables and record why in the PR: anyone with Docker on the VPS already has the data.
The API config gains a required database URL and an optional password file. A missing URL throws at startup, as a missing port does.

Backup

A nightly workflow, scheduled like the reaper, dumps production in Postgres's custom format through the SSH Docker connection.
It restores the dump into a throwaway Postgres on the runner to prove it is usable, encrypts it with age to a public key committed in the repo (the owner alone holds the private key), and uploads it as an artifact kept 30 days. A failed dump, restore check or upload fails the run.
The deploy README gains the restore procedure: download, decrypt, restore into production's database.

Drizzle Gateway

Its own Compose project on the VPS, applied by hand like the proxy, with a README and a "check it matches" command: the Gateway image pinned to an exact version, its port on the loopback only, a volume for its saved connections, its master password set at apply time and never committed.
Before it is deployed, read the licence terms of the pinned version and record them in the PR. If they do not allow this use, ship without the Gateway, use drizzle-kit studio through the same tunnel, and say so on the map.
Network attachment: the up script connects the Gateway to the stack's internal network after the app is up, idempotently, and warns without failing when the Gateway is not running. The removal script disconnects it before tearing the stack down, or the network cannot be removed; the cap and the reaper go through the removal script and inherit it. The Gateway README names the command that reattaches every running stack after the Gateway is recreated.
The owner adds connections in the Gateway's UI by the stack's database host name; the scripts do not register them.

Local dev, tests and CI

API tests: an in-process Postgres (PGlite) built per test file with the real migrations, a test-only dependency pinned exactly.
ADR this spec needs (written in its PR, agreed with the owner): API tests run on an in-process Postgres (PGlite) with the real migrations; dev, CI and every stack run real Postgres. Every later account spec inherits it.
Dev and the local browser suite: one shared Postgres container for the machine, from a dev Compose project, with one database per worktree. The worktree env sync writes the worktree's database URL into the API's local env file, next to its port. A db script starts the container and creates the worktree's database if it is missing; the migrate step, when the database does not answer, names that script, as the missing-port error names the env sync.
drizzle-kit studio stays available for local inspection.
CI's browser-suite job runs a Postgres service container; the slow test task passes the database URL through.

Owner setup. The production secrets, the backup key pair and the Gateway apply are steps only the owner can take; the implementer ships them as a wizard.

Proposed rule changes (for the PR body, not edited here): the API's coding standards describe its layout as having one feature module and name the wire-test fixture; both sentences should mention the database module and the in-process database.

Testing Decisions

Prior art:
- The config tests for loadConfig.
- The wire tests' in-process app fixture, which gains the in-process database.
- The deploy verify scripts and their check-everything-then-report style (verify-gone).
- The preview reap workflow, for a scheduled workflow over the SSH Docker connection.
Edge and unhappy paths:
- Migrating an already-migrated database is a no-op, and ensuring the Gateway role twice changes nothing.
- A schema change with no generated migration fails the gate.
- The API refuses to start without a database URL, and refuses when the database does not answer.
- A failing migration leaves the old app serving and fails the deploy: a drill on a preview, not in the gate.
- The Gateway role cannot create, alter or drop a table.
- Removing a stack whose network the Gateway joined leaves nothing behind.
- Deploying while the Gateway is down still succeeds.
- A dump that cannot be restored fails the backup run.

Out of Scope

Any table, and any change a Player sees: Game and Guest storage and retiring the Seat token belong to Guest history.
The Account, Session, mail transport, Mailpit and rate limiting: Accounts v1.
Point-in-time recovery and WAL archiving.
Running two API replicas, or a migration that must not lock.
Rooms surviving a redeploy.
Registering stacks inside the Gateway's UI automatically.

Further Notes

Map: Wayfinder: accounts. Decisions: Postgres on the VPS: a database per preview, or a schema per preview on a shared server? (its resolution comment), recorded in ADRs 0031 and 0032; Last product decisions before the account specs closes the product side. The research notes sit on the research/postgres-on-vps branch.
Revised in place after ADRs 0031 and 0032 and the split of data between the five account specs; the comment on this issue lists what changed.
Likely more than one PR: the API's database module and local setup; the stack's Compose with migrate and secrets; the Gateway; the backup. Split with to-tickets.

> _Imported from GitHub [amaurycoudr/chess-project#354](https://github.com/amaurycoudr/chess-project/issues/354), opened by [@amaurycoudr](https://github.com/amaurycoudr) on 2026-10-11 13:42 UTC._  --- ## Problem Statement Nothing the app knows outlives a redeploy: **Rooms** live in memory (ADR 0029), and no stack has a database. Every account feature on the map needs one, starting with Guest history, which stores decided **Online games**. Production and up to five previews share one VPS, so a database must fit the deploy scripts as they are, keep preview data apart from production, survive the VPS being lost, and be inspectable and editable by the owner without exposing a port. Contributors, and the agents working in parallel worktrees, need the same database locally without stepping on each other. ## Solution Every stack (production and each preview) gets its own Postgres 18 container, private to that stack, its data in a volume the existing preview teardown already removes (ADR 0032). A one-shot migrate step applies the committed Drizzle migrations (ADR 0031) before the API container is replaced; a failed migration fails the deploy and leaves the old API serving. The API refuses to start without a reachable database. Production is dumped nightly, checked by restoring it, encrypted, and kept off the VPS. A self-hosted Drizzle Gateway on the VPS joins every stack's network and is reached only through an SSH tunnel; it edits production through a role that reads and writes rows but cannot change the schema. Locally, the API's tests run on an in-process Postgres; dev and the browser suite use a real Postgres container, one database per worktree. This spec ships the tooling and an empty schema. No table: Guest history adds the first one. Nothing a **Player** sees changes. ## User Stories 1. As the owner, I want each stack to run its own Postgres, so that a preview's migrations or load never touch production's data. 2. As the owner, I want no Postgres port published, not even on the VPS loopback, so that a stack's database is reachable only from that stack and the Gateway. 3. As the owner, I want a preview's database removed with its stack when the PR closes, is reaped or is pushed out by the preview cap, so that no preview data outlives its preview. 4. As the owner, I want preview databases capped in memory and connections, so that five previews never crowd out production on the one VPS. 5. As the owner, I want each stack's data in a volume at the path Postgres 18 expects, so that a redeploy or a minor image upgrade keeps it. 6. As the owner, I want migrations applied to completion before the API container is replaced, so that a failed migration fails the deploy while the previous API keeps serving. 7. As the owner, I want a rerun of the migrations to change nothing, so that every redeploy can run them safely. 8. As a contributor, I want migrations to be committed SQL that drizzle-kit generates from the schema, so that a schema change is reviewed as a diff and the same files run in tests, previews and production. 9. As a contributor, I want the gate to fail when the schema has a change no committed migration captures, so that a forgotten `generate` never reaches a deploy. 10. As the owner, I want the database passwords of production to come from GitHub secrets and never be committed, so that the repo holds no production credential. 11. As the owner, I want a password delivered to its container without showing in the container's metadata, where the mechanism allows it, so that inspecting a container leaks no credential. 12. As the owner, I want a nightly dump of production, checked by a restore and encrypted to a key only I hold, kept off the VPS, so that losing the VPS loses at most a day of data. 13. As the owner, I want a written restore procedure, so that a backup can actually be used under pressure. 14. As the owner, I want one Drizzle Gateway on the VPS, its version pinned and its port published on the loopback only, so that I can browse every stack's data through an SSH tunnel with no public route. 15. As the owner, I want the Gateway to join a stack's network when the stack deploys and leave it before the stack is removed, so that every running stack is browsable and teardown still leaves nothing behind. 16. As the owner, I want the Gateway to connect with a role that reads and writes rows but cannot change the schema, so that I can fix data by hand without bypassing migrations. 17. As the owner, I want the Gateway's licence terms checked for the pinned version before it is deployed, so that the setup is used within its terms. 18. As a contributor, I want the API's tests to run against an in-process Postgres with the real migrations, so that the gate and the pre-commit hook need no Docker and stay parallel. 19. As a contributor, I want the API to refuse to start when its database URL is missing, so that a setup mistake fails loudly at boot rather than at the first query. 20. As the owner, I want the API to refuse to start when its database does not answer, so that a stack that cannot reach its database never turns healthy and its deploy fails. 21. As a contributor, I want `pnpm dev` and the local browser suite to run against a real Postgres, with one database per worktree, so that several worktrees run side by side without sharing data. 22. As the owner, I want CI's browser suite to run the built API against a real, migrated Postgres, so that the production driver is exercised before a merge. ## Seams - **The API's database module** (connect and migrate), integration tests against an in-process Postgres: an empty database reaches the latest migration; a second run changes nothing; the schema generates no migration that is not committed; an unreachable database is refused with a message naming the setting. Covers 7, 8, 9, 18, 20. - **`loadConfig`**, unit tests beside the existing config tests: the database URL is required and parsed, and the password-file setting overrides the URL's password. Covers 10, 19. - **The deploy and backup verification scripts**, run by the workflows against the VPS, as the existing verify, verify-gone, verify-reaped, verify-cap and verify-kept scripts are today. After a deploy: the stack runs a healthy app and a healthy database and nothing else, the migrate step exited 0 and the migration table lists every committed migration, no database port is published, the volume is mounted at the Postgres 18 path, preview caps are in effect on a preview and absent on production, the Gateway (when running) is on the stack's network, the Gateway role can read but cannot create a table, and no password shows in the container's metadata. After a removal: nothing of the stack is left, the Gateway's attachment included. After a backup: the dump restored into a throwaway Postgres. Covers 1–6, 11–16. Story 17 is a written check in the Gateway PR; story 6's failure path is a one-time drill on a preview, recorded in its PR. - **The existing browser suite**, unchanged, no new test: CI runs it against the built API and a real migrated Postgres, and locally it runs against the worktree's database. Covers 21, 22. No new e2e test: a **Player** perceives nothing of this. ## Implementation Decisions **Governing ADRs.** ADR 0031 (Drizzle v1 RC pinned exactly, committed SQL migrations, the migrator in production, no repository abstraction) and ADR 0032 (a Postgres container per stack, no published port, a one-shot migrate step, one Gateway joining every stack, a no-DDL Gateway role) are accepted and govern this work. ADR 0029 stands. No new ADR is needed. One is worth writing if the owner agrees: "API tests run on an in-process Postgres, dev, CI and stacks on a real one", since every later spec inherits the choice. **Database per stack** - The stack's Compose file gains a database service: Postgres 18 pinned to an exact minor, a named volume at the path Postgres 18 uses, a `pg_isready` healthcheck, and the shared-memory size the image's docs recommend. - The database sits on a new internal network of the stack, never on the proxy network, and publishes no port. The app joins that network beside the proxy network. - Preview caps live in an overlay the up script layers on every preview stack (not tied to the noindex flag): a small `shared_buffers`, about 20 connections and a container memory limit. Production keeps Postgres's defaults. - The verify script's "exactly one container" check becomes "a healthy app and a healthy database". The removal, cap and reaper scripts already remove and check volumes; only the Gateway detach is new. **Migrations** - `drizzle-orm` and `drizzle-kit` at the same exact RC (ADR 0031); production driver `node-postgres`. drizzle-kit is a dev dependency. - Migrations are generated into a folder of the API package and committed. The image copies that folder beside the API's build output. This spec ships one baseline migration with no table, so the whole path runs end to end. - The migrate step runs the same image with a migrate entry point instead of the server, on the stack's internal network. The up script brings the database up and waits for it healthy, runs the migrate step to completion, and only then replaces the app. A failure stops the script before the app is touched, so the old container keeps serving without relying on Compose's dependency ordering. - The migrate entry point also ensures the Gateway role, idempotently: it creates the role if missing, sets its password from the stack's secret, and grants row privileges on the app role's tables, plus default privileges so future tables follow. It runs on every stack, so previews exercise production's path. No password goes into committed SQL, and no init script is mounted, since Compose runs on the runner and a bind mount would name a path on the VPS. - Locally, the API's dev script and the browser suite's API command run the migrate entry point before the server. The production server never migrates on boot (ADR 0032). **API wiring** (within the API's existing layer order and framework-import rules, so no lint change) - A framework-free database module in the API's shared folder: it builds the Drizzle handle from the config, checks the database answers, migrates from the committed folder, and closes the pool. - The app module receives the database handle as an option and provides it under a token, as it does the allowed origins; the room module does not use it yet. The boot entry point connects, checks, then listens; a stopped server closes the pool. - The migrate entry point is a second root entry beside the boot one. - The in-process app fixture used by the wire tests passes an in-process database, so the existing wire tests keep their one-line setup. **Roles and secrets** - Two roles per stack: the app role, which owns the schema and is used by the migrate step and the app; and the Gateway role, row privileges only. - Production passwords for both roles are GitHub secrets passed to the production deploy. Preview passwords are fixed throwaway values in the preview overlay, since a preview database is reachable only from its own network and the Gateway. - Delivery: try first a Compose secret sourced from a runner environment variable; Postgres reads it through its password-file variable, and the API through a password-file setting that overrides the URL's password. Verify on a preview that it arrives and stays out of the container's metadata. If it does not work against a remote daemon, fall back to plain environment variables and record why in the PR: anyone with Docker on the VPS already has the data. - The API config gains a required database URL and an optional password file. A missing URL throws at startup, as a missing port does. **Backup** - A nightly workflow, scheduled like the reaper, dumps production in Postgres's custom format through the SSH Docker connection. - It restores the dump into a throwaway Postgres on the runner to prove it is usable, encrypts it with `age` to a public key committed in the repo (the owner alone holds the private key), and uploads it as an artifact kept 30 days. A failed dump, restore check or upload fails the run. - The deploy README gains the restore procedure: download, decrypt, restore into production's database. **Drizzle Gateway** - Its own Compose project on the VPS, applied by hand like the proxy, with a README and a "check it matches" command: the Gateway image pinned to an exact version, its port on the loopback only, a volume for its saved connections, its master password set at apply time and never committed. - Before it is deployed, read the licence terms of the pinned version and record them in the PR. If they do not allow this use, ship without the Gateway, use `drizzle-kit studio` through the same tunnel, and say so on the map. - Network attachment: the up script connects the Gateway to the stack's internal network after the app is up, idempotently, and warns without failing when the Gateway is not running. The removal script disconnects it before tearing the stack down, or the network cannot be removed; the cap and the reaper go through the removal script and inherit it. The Gateway README names the command that reattaches every running stack after the Gateway is recreated. - The owner adds connections in the Gateway's UI by the stack's database host name; the scripts do not register them. **Local dev, tests and CI** - API tests: an in-process Postgres (PGlite) built per test file with the real migrations, a test-only dependency pinned exactly. - **ADR this spec needs** (written in its PR, agreed with the owner): API tests run on an in-process Postgres (PGlite) with the real migrations; dev, CI and every stack run real Postgres. Every later account spec inherits it. - Dev and the local browser suite: one shared Postgres container for the machine, from a dev Compose project, with one database per worktree. The worktree env sync writes the worktree's database URL into the API's local env file, next to its port. A `db` script starts the container and creates the worktree's database if it is missing; the migrate step, when the database does not answer, names that script, as the missing-port error names the env sync. - `drizzle-kit studio` stays available for local inspection. - CI's browser-suite job runs a Postgres service container; the slow test task passes the database URL through. **Owner setup.** The production secrets, the backup key pair and the Gateway apply are steps only the owner can take; the implementer ships them as a `wizard`. **Proposed rule changes** (for the PR body, not edited here): the API's coding standards describe its layout as having one feature module and name the wire-test fixture; both sentences should mention the database module and the in-process database. ## Testing Decisions - Prior art: - The config tests for `loadConfig`. - The wire tests' in-process app fixture, which gains the in-process database. - The deploy verify scripts and their check-everything-then-report style (verify-gone). - The preview reap workflow, for a scheduled workflow over the SSH Docker connection. - Edge and unhappy paths: - Migrating an already-migrated database is a no-op, and ensuring the Gateway role twice changes nothing. - A schema change with no generated migration fails the gate. - The API refuses to start without a database URL, and refuses when the database does not answer. - A failing migration leaves the old app serving and fails the deploy: a drill on a preview, not in the gate. - The Gateway role cannot create, alter or drop a table. - Removing a stack whose network the Gateway joined leaves nothing behind. - Deploying while the Gateway is down still succeeds. - A dump that cannot be restored fails the backup run. ## Out of Scope - Any table, and any change a **Player** sees: Game and Guest storage and retiring the Seat token belong to Guest history. - The Account, Session, mail transport, Mailpit and rate limiting: Accounts v1. - Point-in-time recovery and WAL archiving. - Running two API replicas, or a migration that must not lock. - **Rooms** surviving a redeploy. - Registering stacks inside the Gateway's UI automatically. ## Further Notes - Map: [Wayfinder: accounts](https://github.com/amaurycoudr/chess-project/issues/346). Decisions: [Postgres on the VPS: a database per preview, or a schema per preview on a shared server?](https://github.com/amaurycoudr/chess-project/issues/347) (its resolution comment), recorded in ADRs 0031 and 0032; [Last product decisions before the account specs](https://github.com/amaurycoudr/chess-project/issues/362) closes the product side. The research notes sit on the `research/postgres-on-vps` branch. - Revised in place after ADRs 0031 and 0032 and the split of data between the five account specs; the comment on this issue lists what changed. - Likely more than one PR: the API's database module and local setup; the stack's Compose with migrate and secrets; the Gateway; the backup. Split with `to-tickets`.

root commented

2026-10-11 13:42:22 +00:00

(Migrated from gitlab.core-project.space)

GitHub issue relations, imported as "related" links (GitLab CE has no sub-issues or blocking links).

Sub-issues: #367, #368, #369, #370, #371
Blocking: #355

> _GitHub issue relations, imported as "related" links (GitLab CE has no sub-issues or blocking links)._  - Sub-issues: #367, #368, #369, #370, #371 - Blocking: #355

root commented

2026-10-11 16:10:24 +00:00

(Migrated from gitlab.core-project.space)

Imported from a GitHub comment by @amaurycoudr on 2026-10-11 16:10 UTC.

Revised in place after ADR 0031 and ADR 0032 landed, and after data was split between the five account specs. What changed:

ADRs: the "ADRs this spec needs" section is gone, since 0031 and 0032 now exist. The change to ADR 0029's Governs column is dropped too. One new ADR is called for: API tests run on PGlite, while dev, CI and every stack run real Postgres.
Gateway role: the migrate entry point now creates it, idempotently, instead of an init script. Compose runs on the runner, so a mounted init file would name a path that isn't on the VPS.
Deploy order: the database comes up first, migrations run to completion, and only then is the app replaced. The old API keeps serving if a migration fails, without relying on Compose's depends_on.
New stories: the gate fails on schema drift, and the API refuses to boot without a reachable database.
Seams: the local and CI browser suite stories are now covered by the existing browser suite. The migrate seam became the API's database module seam.
API wiring: a framework-free database module that fits the current lint rules, so no rule edit is needed.
Smaller changes: the preview caps apply to every preview, owner setup ships as a wizard, and Out of Scope names what the later account specs own.
Unchanged: a container per stack, the nightly encrypted dump, the Gateway attachment rules, and a database per worktree locally.

> _Imported from a [GitHub comment](https://github.com/amaurycoudr/chess-project/issues/354#issuecomment-6110982061) by [@amaurycoudr](https://github.com/amaurycoudr) on 2026-10-11 16:10 UTC._  Revised in place after [ADR 0031](../blob/main/docs/adr/0031-drizzle-v1-rc-pinned-exactly.md) and [ADR 0032](../blob/main/docs/adr/0032-postgres-container-per-stack-editable-through-gateway.md) landed, and after data was split between the five account specs. What changed: - **ADRs:** the "ADRs this spec needs" section is gone, since 0031 and 0032 now exist. The change to ADR 0029's Governs column is dropped too. One new ADR is called for: API tests run on PGlite, while dev, CI and every stack run real Postgres. - **Gateway role:** the migrate entry point now creates it, idempotently, instead of an init script. Compose runs on the runner, so a mounted init file would name a path that isn't on the VPS. - **Deploy order:** the database comes up first, migrations run to completion, and only then is the app replaced. The old API keeps serving if a migration fails, without relying on Compose's `depends_on`. - **New stories:** the gate fails on schema drift, and the API refuses to boot without a reachable database. - **Seams:** the local and CI browser suite stories are now covered by the existing browser suite. The migrate seam became the API's database module seam. - **API wiring:** a framework-free database module that fits the current lint rules, so no rule edit is needed. - **Smaller changes:** the preview caps apply to every preview, owner setup ships as a `wizard`, and Out of Scope names what the later account specs own. - **Unchanged:** a container per stack, the nightly encrypted dump, the Gateway attachment rules, and a database per worktree locally.

root commented

2026-10-13 08:39:43 +00:00

(Migrated from gitlab.core-project.space)

mentioned in issue #367

root commented

2026-10-13 08:39:43 +00:00

(Migrated from gitlab.core-project.space)

mentioned in issue #368

root commented

2026-10-13 08:39:43 +00:00

(Migrated from gitlab.core-project.space)

mentioned in issue #369

root commented

2026-10-13 08:39:44 +00:00

(Migrated from gitlab.core-project.space)

mentioned in issue #370

root commented

2026-10-13 08:39:44 +00:00

(Migrated from gitlab.core-project.space)

mentioned in issue #371

root commented

2026-10-13 08:39:45 +00:00

(Migrated from gitlab.core-project.space)

mentioned in issue #373

root commented

2026-10-13 08:41:21 +00:00

(Migrated from gitlab.core-project.space)

mentioned in issue #355

root commented

2026-10-13 08:42:24 +00:00

(Migrated from gitlab.core-project.space)

marked this issue as related to #367

root commented

2026-10-13 08:42:25 +00:00

(Migrated from gitlab.core-project.space)

marked this issue as related to #368

root commented

2026-10-13 08:42:25 +00:00

(Migrated from gitlab.core-project.space)

marked this issue as related to #369

root commented

2026-10-13 08:42:25 +00:00

(Migrated from gitlab.core-project.space)

marked this issue as related to #370

root commented

2026-10-13 08:42:26 +00:00

(Migrated from gitlab.core-project.space)

marked this issue as related to #371

root commented

2026-10-13 08:42:26 +00:00

(Migrated from gitlab.core-project.space)

marked this issue as related to #355

root commented

2026-10-13 10:17:37 +00:00

(Migrated from gitlab.core-project.space)

mentioned in merge request !506

root commented

2026-10-13 10:19:24 +00:00

(Migrated from gitlab.core-project.space)

mentioned in commit 6ba8bbcfa7

mentioned in commit 6ba8bbcfa7d87b07619ab4837e686e74ce3440df

root commented

2026-10-13 11:16:39 +00:00

(Migrated from gitlab.core-project.space)

mentioned in merge request !507

root (Migrated from gitlab.core-project.space) closed this issue

2026-10-13 14:10:08 +00:00

root commented

2026-10-13 14:17:28 +00:00

(Migrated from gitlab.core-project.space)

First production backup run, by hand after merge (#371): pipeline 43, job backup-production green — https://gitlab.core-project.space/amaurycoudr/chess-project/-/jobs/389

dumped chess-db-1: 4083 bytes
restored into a fresh postgres:18.4-alpine, 1 migration listed
encrypted with age to the committed key: chess-db-2026-10-13T1416Z.dump.age, 4283 bytes, kept 30 days

Nightly runs follow from schedule 2 (41 2 * * * UTC, BACKUP=1). Production's first deploy (pipeline 41) passed its verify, including no preview caps.

First production backup run, by hand after merge (#371): pipeline 43, job `backup-production` green — https://gitlab.core-project.space/amaurycoudr/chess-project/-/jobs/389 - dumped `chess-db-1`: 4083 bytes - restored into a fresh postgres:18.4-alpine, 1 migration listed - encrypted with age to the committed key: `chess-db-2026-10-13T1416Z.dump.age`, 4283 bytes, kept 30 days Nightly runs follow from schedule 2 (`41 2 * * *` UTC, `BACKUP=1`). Production's first deploy (pipeline 41) passed its verify, including no preview caps.