Each stack runs its own private Postgres, migrated before the app is replaced #369

New issue

Closed

opened 2026-10-11 16:26:06 +00:00 by root · 10 comments

root commented

2026-10-11 16:26:06 +00:00

(Migrated from gitlab.core-project.space)

Imported from GitHub amaurycoudr/chess-project#369, opened by @amaurycoudr on 2026-10-11 16:26 UTC.

Parent

#354

What to build

Stories 1–6, 10 and 11 of #354. 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.
It 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.
The image carries the committed migrations beside the API's build output.

Every preview gets a caps overlay that the up script layers on, independent of the noindex flag: a small shared_buffers, about 20 connections, and a container memory limit. Production keeps Postgres's defaults.

The up script brings the database up and waits for it to be healthy. It then runs the migrate step (the same image with the migrate entry point, on the stack's internal network) 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 app role owns the schema and is used by the migrate step and the app. Production's password is a GitHub secret passed to the production deploy. A preview's is a fixed throwaway value in the preview overlay.

Password delivery:

Try a Compose secret sourced from a runner environment variable first. Postgres reads it through its password-file variable, and the API through a new optional password-file setting in its config that overrides the URL's password.
If that does not work against the remote daemon, fall back to plain environment variables and record why in the PR.

The verify script's "exactly one container" check becomes "a healthy app and a healthy database". It also checks:

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;
the caps are in effect on a preview and absent on production;
no password shows in the container's metadata.

The removal, cap and reaper paths already remove and check volumes; confirm they cover the database's.

Create the owner wizard with the step that sets production's database secrets. See #354, Database per stack, Migrations and Roles and secrets.

Seam: the deploy verification scripts, run by the workflows against the VPS; loadConfig unit tests for the password-file setting.

Acceptance criteria

loadConfig lets the password-file setting override the URL's password, and leaves the URL alone without it.
A preview deploy passes the extended verify script: healthy app and database, nothing else, migrate exited 0, every committed migration listed, no published port, volume at the Postgres 18 path, caps in effect.
Production's verify finds no caps.
No password appears in the inspected metadata of any container in the stack, or the PR records why the environment-variable fallback was taken.
Closing a preview, reaping it and pushing it out by the cap each leave none of its database behind.
Drill, recorded in the PR: a deliberately failing migration pushed to a preview fails the deploy while the previous app keeps serving; the revert redeploys green.
A redeploy of an unchanged stack reruns the migrate step and changes nothing.
No production credential is committed; the owner has run the wizard's production-secrets step before the spec reaches main.
The gate is green, and e2e test:slow is green.

> _Imported from GitHub [amaurycoudr/chess-project#369](https://github.com/amaurycoudr/chess-project/issues/369), opened by [@amaurycoudr](https://github.com/amaurycoudr) on 2026-10-11 16:26 UTC._  --- ## Parent #354 ## What to build Stories 1–6, 10 and 11 of #354. 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. - It 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. - The image carries the committed migrations beside the API's build output. Every preview gets a caps overlay that the up script layers on, independent of the noindex flag: a small `shared_buffers`, about 20 connections, and a container memory limit. Production keeps Postgres's defaults. The up script brings the database up and waits for it to be healthy. It then runs the migrate step (the same image with the migrate entry point, on the stack's internal network) 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 app role owns the schema and is used by the migrate step and the app. Production's password is a GitHub secret passed to the production deploy. A preview's is a fixed throwaway value in the preview overlay. Password delivery: - Try a Compose secret sourced from a runner environment variable first. Postgres reads it through its password-file variable, and the API through a new optional password-file setting in its config that overrides the URL's password. - If that does not work against the remote daemon, fall back to plain environment variables and record why in the PR. The verify script's "exactly one container" check becomes "a healthy app and a healthy database". It also checks: - 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; - the caps are in effect on a preview and absent on production; - no password shows in the container's metadata. The removal, cap and reaper paths already remove and check volumes; confirm they cover the database's. Create the owner `wizard` with the step that sets production's database secrets. See #354, *Database per stack*, *Migrations* and *Roles and secrets*. Seam: the deploy verification scripts, run by the workflows against the VPS; `loadConfig` unit tests for the password-file setting. ## Acceptance criteria - [ ] `loadConfig` lets the password-file setting override the URL's password, and leaves the URL alone without it. - [ ] A preview deploy passes the extended verify script: healthy app and database, nothing else, migrate exited 0, every committed migration listed, no published port, volume at the Postgres 18 path, caps in effect. - [ ] Production's verify finds no caps. - [ ] No password appears in the inspected metadata of any container in the stack, or the PR records why the environment-variable fallback was taken. - [ ] Closing a preview, reaping it and pushing it out by the cap each leave none of its database behind. - [ ] Drill, recorded in the PR: a deliberately failing migration pushed to a preview fails the deploy while the previous app keeps serving; the revert redeploys green. - [ ] A redeploy of an unchanged stack reruns the migrate step and changes nothing. - [ ] No production credential is committed; the owner has run the wizard's production-secrets step before the spec reaches `main`. - [ ] The gate is green, and e2e `test:slow` is green.

root commented

2026-10-11 16:26:06 +00:00

(Migrated from gitlab.core-project.space)

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

Parent (sub-issue of): #354
Blocked by: #368
Blocking: #371, #370

> _GitHub issue relations, imported as "related" links (GitLab CE has no sub-issues or blocking links)._  - Parent (sub-issue of): #354 - Blocked by: #368 - Blocking: #371, #370

root commented

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

(Migrated from gitlab.core-project.space)

mentioned in issue #370