thermograph/infra/deploy/POSTGRES-MIGRATION.md

# Cutover: SQLite → PostgreSQL 18 (containerized stack)

The app now runs as a docker-compose stack (`app` + `db`) and standardizes on
PostgreSQL. Locally you need nothing but Docker; `make up` builds and starts both.
This doc is the **one-time production cutover** from the old bare-systemd/SQLite
deploy to the compose stack, migrating the authoritative accounts data.

## What moved

- **accounts** (users/subscriptions/notifications/…): SQLite → Postgres **durable**
  tables. Managed by Alembic (`backend/alembic/`); `create_all` on a fresh DB.
- **derived cache** (`store.py`) and **metrics** (`metrics.py`): now Postgres
  **UNLOGGED** tables (fast, non-durable — same throwaway semantics). No data to
  migrate — the cache rebuilds, metrics start empty.
- **climate record** (`climate.py`): the raw daily archive + recent/forecast bundle
  moved from per-cell **parquet** files into **TimescaleDB hypertables**
  (`climate_history` / `climate_recent` / `climate_sync`, managed by Alembic). The
  app reads/writes them via `data/climate_store.py`. Backfilled from the existing
  parquet cache with `migrate_cache_to_pg.py` (see below).
- The app selects Postgres when `THERMOGRAPH_DATABASE_URL` is a `postgresql+asyncpg`
  URL; unset ⇒ the old SQLite/parquet behavior (this is how the **test suite** stays
  Postgres-free — no Postgres needed in CI, and climate falls back to parquet).
- `db` runs the stock **`timescale/timescaledb:latest-pg18`** image (genuine PG18 +
  TimescaleDB). The earlier pg_duckdb / `read_parquet('/parquet/…')` capability is
  gone — the climate record is real tables now, queryable directly (see
  `deploy/db/README.md`).

## Connection model

Per worker (4 workers): a read-write asyncpg engine + a read-only asyncpg engine
(`default_transaction_read_only=on`, used by the pure-GET endpoints), plus one sync
psycopg engine for the notifier thread. Single Postgres primary — the RO engine is
a guardrail, not a replica.

## Prod cutover steps (maintenance window)

Prereq: the deploy user can run `docker`. Secrets in `/etc/thermograph.env` must
include `POSTGRES_PASSWORD`, and pinned `THERMOGRAPH_AUTH_SECRET`,
`THERMOGRAPH_VAPID_PRIVATE_KEY`/`_PUBLIC_KEY`, `THERMOGRAPH_COOKIE_SECURE=1`.

1. **Ship the stack, don't cut over yet.** Deploy the branch; `docker compose build`.
   Bring up only the DB: `docker compose up -d db`. Add a nightly `pg_dump` backup.
2. **Freeze + back up.** Stop the old app (hard stop — no writes). Back up the live
   `data/accounts.sqlite` + `-wal`/`-shm` (this is the rollback artifact).
3. **Build the schema.** `docker compose run --rm backend alembic upgrade head`
   (or let the `app` entrypoint do it on first start — but do the data copy before
   real traffic).
4. **Copy the accounts data:**
   ```
   docker compose run --rm \
     -e THERMOGRAPH_DATABASE_URL="postgresql+asyncpg://thermograph:$POSTGRES_PASSWORD@db:5432/thermograph" \
     -v /opt/thermograph/data/accounts.sqlite:/src.sqlite:ro \
     backend python migrate_accounts_to_pg.py --sqlite /src.sqlite
   ```
   It copies user → subscription/push_subscription/pending_digest → notification,
   **preserving PKs**, **skips access_token** (everyone re-logins once), and
   **resets the integer-PK sequences** (skipping that = a runtime PK collision).
   Verify the printed per-table counts against the old DB.
5. **Start serving:** `docker compose up -d` (Caddy already proxies `127.0.0.1:8137`).
6. **Smoke test:** login, list/create/delete a subscription, one notifier tick, a
   cached calendar/day request (derived store on PG), the metrics dashboard.
7. Keep the frozen `accounts.sqlite` as rollback for a few days; keep PG backed up.
   Enable pg_duckdb on the (already-initialized) volume once, by hand:
   `docker compose exec db psql -U thermograph -d thermograph -c 'CREATE EXTENSION IF NOT EXISTS pg_duckdb;'`

## Rollback

Redeploy the previous SQLite release (or point `THERMOGRAPH_DATABASE_URL` back to
unset/SQLite and restart). **Clean only during/immediately after the window** — once
real users write to Postgres, those writes are lost on rollback (the copy is
one-way), so keep the window short and writes frozen during the copy.

## TimescaleDB cutover (from the earlier pg_duckdb PG18 stack)

Swapping the `db` image from `pgduckdb/pgduckdb:18` to
`timescale/timescaledb:latest-pg18` means a **fresh PGDATA volume** — and that
volume also holds the durable **accounts** data. So this cutover is not a plain
`docker compose pull`; it re-seeds both accounts and the climate record.

1. **Back up first.** `pg_dump` the accounts tables from the *old* db container
   (`docker compose exec db pg_dump -U thermograph -t user -t subscription \
   -t notification -t push_subscription -t pending_digest thermograph > accounts.sql`),
   or keep the pre-Postgres `accounts.sqlite` as the source of truth.
2. **Replace the DB.** Deploy this branch (compose now points at the stock
   TimescaleDB image, no `build:`). Bring down the stack and remove the old volume
   so PGDATA re-inits on the new image: `docker compose down && docker volume rm
   <project>_pgdata`.
3. **Build the schema.** `docker compose up -d db`, then
   `docker compose run --rm backend alembic upgrade head` — this creates the accounts
   tables *and* the climate hypertables, and `CREATE EXTENSION timescaledb`.
4. **Restore accounts.** Either `psql < accounts.sql`, or re-run
   `migrate_accounts_to_pg.py --sqlite <accounts.sqlite>` (§ the SQLite cutover
   above). Verify per-table counts.
5. **Backfill the climate record** from the parquet cache (mount it and run the
   backfill; idempotent, resumable):
   ```
   docker compose run --rm \
     -v /opt/thermograph/data/cache:/app/data/cache:ro \
     backend python migrate_cache_to_pg.py
   ```
   It loads `climate_history` + `climate_recent` and sets each cell's
   `climate_sync` to the parquet **file mtime**, so `recent_stamp` (hence every
   derived-payload token) is unchanged across the cutover. Cells without a
   schema-complete parquet record are skipped and refetch lazily on first request.
6. **Start serving:** `docker compose up -d`. Smoke test: login, a subscription,
   a cached calendar/day request (served from `climate_history`), the metrics
   dashboard.
7. Keep the pre-cutover DB backup (accounts dump + parquet cache) for a few days.
   The climate record is now **durable** — make sure `pg_dump`/PITR covers it.

## Notes

- The two `deploy/migrations/*.sql` files are superseded by Alembic on Postgres
  (they were SQLite-specific `ALTER TABLE` column-adds for the old prod DB).
- The TimescaleDB image's data dir is `/var/lib/postgresql/data`; the compose
  volume is mounted at the parent `/var/lib/postgresql` so it persists without the
  initdb permission issue that pinning `PGDATA` to the mountpoint can trigger.
- Climate falls back to the parquet cache whenever `THERMOGRAPH_DATABASE_URL` is
  not a Postgres URL (dev, tests, offline tooling) — the same dialect switch as the
  accounts DB, so a DB outage in prod degrades to upstream refetch, never a 500.