PR previews: a database cloned from dev per preview, not the shared one

[Devski]

What happened

The preview of #112 (pr-112.dev.architektow3d.pl) answered every profile page with a server error: column "r360_set_id" does not exist. The pull request ships migrations 0014–0016; the preview runs the pull request's image against the shared dev database (SPEC §8), which is on main's schema, and deploy/preview-up.sh starts the container without migrating — only the dev deployment migrates (deploy/remote-deploy.sh, #53). Every pull request that adds a column the page reads will preview as a server error. Found 09.09.2026.

Decision (Dawid, 09.09.2026)

A preview gets its own database, cloned from dev when the preview starts and dropped with the container. Not "the preview migrates the shared database": a pull request revised after its preview ran would leave a migration in dev's journal whose hash no longer matches the file, and the dev deployment after the merge would fail on it.

Shape

• preview-up.sh: CREATE DATABASE platform_pr_<n> TEMPLATE platform_devski (the template clone needs no other connection to the source at that moment — retry briefly, or use pg_dump | psql if the instance is busy), then run /app/migrate.mjs from the pull request's image against the clone, then start the container with DATABASE_URL pointing at it. Everything else stays shared (bucket under S3_PREFIX=pr-<n>/, signing key).
• preview-down.sh: DROP DATABASE platform_pr_<n> after the container is gone. Also on replace (a new push to the same pull request): drop and clone again, so the preview always reflects dev's data plus the pull request's migrations.
• Memory: the instance has 1.9 GB and no swap (preview-up.sh header). A clone of dev is small today; the cap of two previews stays.
• The preview's own accounts and uploads die with it — say so in the CI summary line.
• SPEC §8: the "PR preview" row changes from "shared dev" to "a clone of dev at start, dropped with the preview". Note the limitation in docs/dev-environment.md.

Verification

• Open a pull request with an expand-only migration: its preview serves the page that reads the new column; dev keeps serving on main's schema.
• Close it: the database is gone (\l on the instance).
• deploy-config in CI keeps its checks on the preview scripts (import site), plus one that preview-up.sh names the clone.

Depends on: nothing. Sequenced after the R360 trial on dev.

[Devski]

What #169 changes here — cheaper to build, and one collision to settle first

deploy/backup-db.sh landed on main on 12.09.2026 (#168 / #169). It solves, and has drilled, the
two things the shape above was going to have to invent:

• superuser access to the cluster from the host — PG_CONTAINER=${PG_CONTAINER:-postgres}
  (deploy/backup-db.sh:26) and psql() { docker exec "$PG_CONTAINER" psql --username postgres … }
  (:108);
• a pg_dump-based copy that is known to work against a live database (:140), which is the
  fallback this issue already names for the moment CREATE DATABASE … TEMPLATE cannot get an
  exclusive look at platform_devski. Dev's connection pool holds a permanent connection, so that
  moment is every moment — the fallback is likely to be the main path, not the exception.

The collision. The nightly copy selects its databases by name:

select datname from pg_database
 where datallowconn and datname like 'platform\_%' and datname not like '%\_test\_%'

(deploy/backup-db.sh:123). A preview database called platform_pr_<n> matches that pattern, so
the backup would sweep in a database that exists for a few hours and is thrown away when the pull
request closes. Two consequences, and the second is the bad one:

1. Wasted bytes — a full copy of dev's data, per open preview, uploaded every night.
2. A failed backup on a night when nothing is wrong. The script runs under set -euo pipefail
   (:22) and verifies that the number of finished dumps equals the number of databases it chose
   (:152). A preview torn down between the select and its turn in the loop makes pg_dump fail
   → the count no longer matches → exit 1, no copy that night. And the shrink guard (:168)
   compares tonight's bytes against the last success and refuses anything under half: two previews
   up one night and gone the next is exactly the swing that trips it.

What to do when this is built — one of the two, not both:

• name the clone outside the pattern (preview_pr_<n>), or
• add and datname not like 'platform\_pr\_%' to the select at :123.

The exclusion is the safer of the two: it survives someone naming a preview database by hand, and it
states the intent in the place where the rule lives. Whichever is chosen, deploy-config should
check it, next to the check that preview-up.sh names the clone at all.

[Devski]

Second time, and this one cost a review cycle: PR #170 (#66) added a position column, so on pr-170 every page listing works answered 500 — column works.position does not exist — while register, the homepage and dev itself were fine. Same mechanism as #112: the preview runs the pull request's image against dev's database, and previews never migrate.

What makes it worse than a one-off: it is not a flake, it is a rule. Any pull request carrying a migration will look broken on its preview, exactly at the moment the preview exists to be looked at — and the more careful the change, the more likely it carries one. The workaround used on 12.09.2026 was to apply the migration to dev's database by hand before the merge, which works only because the migration was expand-only (G6) and someone was there to reason about it.

Raised in priority on Dawid's word, 12.09.2026 — not started.