Plan: PostgreSQL and object storage as options

Status: draft, 2026-10-03

Goal

UX Testbench stores each study in its own SQLite file and every uploaded file on local disk. That is the right default for a laptop or a single small server, and it stays the default. A team that runs the hosted version on a platform such as Dokploy wants two things it cannot get today:

• A managed database. Backups, replicas and point-in-time recovery handled by the platform, and data that does not live inside one container's volume.
• Files outside the container. Prototypes, screenshots and exports in S3, Cloudflare R2 or MinIO, so a redeploy or a lost volume does not take them with it.

This plan adds both as opt-in backends. With no new environment variables, nothing changes: the same SQLite files, the same folders, the same tests.

What the code does today

Three functions open a database, and everything else goes through them:

The SQL is hand-written (156 execute calls in 18 files, no ORM). The parts that differ between SQLite and PostgreSQL are few and known:

Files are tied to a study's folder: a study is a folder of YAML plus prototypes/ (ADR 004), and prototypes are served from the app's own origin so the task runner can instrument them (ADR 001). Uploads go through studio.py; prototypes and task images are served by ab_test.py and first_click.py; the study export is a ZIP built in memory.

Design

1. A database layer (testbench/db.py)

One module owns connections, and the rest of the code stops importing sqlite3:

• db.study(slug) and db.system() return a connection for the current request, cached on g and closed at teardown, as today.
• The connection object exposes the interface the code already uses (execute, executemany, commit, fetchone, fetchall) and rows that support both row["name"] and row[0], so the 156 call sites keep working.
• On PostgreSQL it rewrites ? to %s before sending the query. A test fails the build if any SQL string contains a literal ? inside quotes, so the rewrite can never touch data.
• db.insert(conn, sql, params) returns the new row's id on both backends (lastrowid or RETURNING id). The 8 lastrowid sites move to it.
• db.greatest(a, b) returns MAX(a, b) or GREATEST(a, b) for the one upsert that needs it.
• db.IntegrityError is the one exception type callers catch.
• Each schema is written once, in SQLite form, and db substitutes the identity column for PostgreSQL. Schemas stay additive (CREATE TABLE IF NOT EXISTS), as they are now.

Per-study isolation on PostgreSQL: each study gets its own schema, study_<slug>, and its connection runs with SET search_path TO study_<slug>. This keeps the guarantee ADR 003 was written to protect: no query can reach another study's rows by forgetting a WHERE project_id. System tables live in a testbench schema. Connections come from a psycopg_pool pool.

2. A file store (testbench/filestore.py)

One client (boto3) serves all three providers. S3_ENDPOINT_URL points it at R2 (https://<account>.r2.cloudflarestorage.com) or MinIO (http://minio:9000); unset means AWS.

What moves to the bucket:

• Prototypes and uploads: everything under a study's prototypes/ and anything uploaded in the Studio, including first-click screenshots. The YAML stays on disk (see open questions).
• Exports: the study ZIP and large CSV exports are written to exports/<study>/<timestamp> and the browser is redirected to a presigned URL that expires after 15 minutes. A lifecycle rule on the bucket deletes exports after a day.

Serving stays same-origin. Prototypes and task images are streamed through the app, not linked to the bucket, because ADR 001 depends on the prototype running on the app's origin. The app sets ETag and Cache-Control so a participant's browser does not fetch a prototype twice. Only exports, which are downloads and never framed, use direct presigned links.

3. Dependencies stay optional

psycopg[binary,pool] and boto3 go in requirements-postgres.txt and requirements-s3.txt, imported only when their backend is selected. A plain pip install -r requirements.txt and the SQLite path are unchanged. The Dockerfile installs both, so one image serves either setup.

4. Moving an existing instance

python -m testbench migrate-to-postgres copies _system.db and every <slug>.db into PostgreSQL in one transaction per study, keeping ids, and checks row counts before reporting success. It never deletes the SQLite files.

python -m testbench migrate-files-to-s3 uploads each study's prototypes/ and uploads, checks each object's size and checksum, and leaves the local copies in place.

5. Testing

• The whole suite runs on both backends. pytest uses SQLite and local files as now; TEST_DATABASE_URL=postgres://… TEST_S3_ENDPOINT=http://localhost:9000 pytest runs the same tests against PostgreSQL and MinIO in Docker.
• New tests cover the layer itself: placeholder rewriting, insert ids, per-study schema isolation (a query on one study cannot see another's rows), reset and delete, file store round trips, streaming with ETag, presigned export links, and both migration commands.
• CI runs the suite twice, once per backend.

6. Deployment on Dokploy

• Add a PostgreSQL service in Dokploy and set DATABASE_URL on the app.
• Create an R2 bucket (or MinIO service) and set STORAGE_BACKEND=s3, S3_BUCKET, S3_ENDPOINT_URL, S3_ACCESS_KEY_ID, S3_SECRET_ACCESS_KEY, S3_REGION (auto for R2).
• Keep the /data volume: the Studio's YAML still lives there.
• docs/operations.md gets a section for this setup, and ADR 005 records the decision and amends ADR 003.

Phases

About two and a half weeks in total. Phase 1 can merge on its own: it changes no behaviour and makes every later phase smaller.

Open questions

1. YAML on disk. Studies are folders of YAML (ADR 004), so the container still needs the /data volume even with PostgreSQL and S3. Moving YAML into the database or the bucket would make the container fully stateless, but it touches the Studio's editing, history and validation. Leave it for a later plan?
2. The study export ZIP currently can include the study's .db file. On PostgreSQL, should the export contain a SQLite file generated from the schema (portable, re-importable) or CSV files only?
3. One pool or one per worker. Two gunicorn workers each holding a small pool (for example 5 connections) is the simple default. Is a connection limit set on the Dokploy PostgreSQL service that this has to stay under?
4. Proxy headers. Separate from this plan but needed on the same deploy: behind Traefik the app sees the proxy's IP for every visitor, so rate limits and audit logs are shared by everyone. ProxyFix fixes it and should land first.