Run on Postgres with today's schema #20

New Issue

2026-08-08T06:06:26+07:00

sulthan commented

2026-08-08 06:06:26 +07:00

Parent

What to build

The backend runs on Postgres instead of SQLite, and nothing else changes. Every endpoint behaves identically, the wire format is untouched, and the whole existing test suite still passes. This is the foundation every later ticket sits on, and its value is precisely that it is boring: if anything observable changes, this ticket is wrong.

Chosen for future supportability, not throughput — SQLite was benchmarked and had ample headroom. See ADR-0001; do not reopen on performance grounds.

Acceptance criteria

The service starts against a Postgres connection URL and serves every existing endpoint with identical behaviour.
Schema is created and updated by a migration runner: a version table, numbered SQL embedded in the binary, one transaction per migration, run on startup and safe to re-run.
favorite becomes a real boolean; chapter numbers are double precision; timestamps stay unix-millisecond integers.
The null-safe comparison behind the updated_at ordering rule uses IS DISTINCT FROM. A Progress change advances updated_at; a Latest Chapter change or a favourite toggle does not.
All SQL stays parameterised. Only compile-time constants are concatenated into query text.
Legacy SQLite migration machinery — the column probing and the Asura key rewrite — is deleted rather than ported, along with the tests that seed legacy schemas. Confirm first that the Asura rewrite has no remaining work: it has run on every startup for months and the userscripts already strip build hashes before writing.
Each test package starts one Postgres container and gives every test its own database. No test touches the real network.
The SQLite driver no longer appears in the backend module.
Compose gains a Postgres service and volume. The connection URL is configurable; the SQLite path variable is retired.
The README states that running the test suite now requires Docker.
The existing test suite passes, unchanged in intent.

Blocked by

None — can start immediately.

## Parent #18 ## What to build The backend runs on Postgres instead of SQLite, and nothing else changes. Every endpoint behaves identically, the wire format is untouched, and the whole existing test suite still passes. This is the foundation every later ticket sits on, and its value is precisely that it is boring: if anything observable changes, this ticket is wrong. Chosen for future supportability, not throughput — SQLite was benchmarked and had ample headroom. See ADR-0001; do not reopen on performance grounds. ## Acceptance criteria - [x] The service starts against a Postgres connection URL and serves every existing endpoint with identical behaviour. - [x] Schema is created and updated by a migration runner: a version table, numbered SQL embedded in the binary, one transaction per migration, run on startup and safe to re-run. - [x] `favorite` becomes a real boolean; chapter numbers are double precision; timestamps stay unix-millisecond integers. - [x] The null-safe comparison behind the `updated_at` ordering rule uses `IS DISTINCT FROM`. A Progress change advances `updated_at`; a Latest Chapter change or a favourite toggle does not. - [x] All SQL stays parameterised. Only compile-time constants are concatenated into query text. - [x] Legacy SQLite migration machinery — the column probing and the Asura key rewrite — is deleted rather than ported, along with the tests that seed legacy schemas. Confirm first that the Asura rewrite has no remaining work: it has run on every startup for months and the userscripts already strip build hashes before writing. - [x] Each test package starts one Postgres container and gives every test its own database. No test touches the real network. - [x] The SQLite driver no longer appears in the backend module. - [x] Compose gains a Postgres service and volume. The connection URL is configurable; the SQLite path variable is retired. - [x] The README states that running the test suite now requires Docker. - [x] The existing test suite passes, unchanged in intent. ## Blocked by None — can start immediately.

sulthan added the ready-for-agent label 2026-08-08 06:06:26 +07:00

sulthan referenced this issue

2026-08-08 06:06:27 +07:00

Split Series from Bookmark, keeping the wire format flat #21

sulthan commented

2026-08-08 06:48:10 +07:00

Implemented on feat/db-change — not merged, so this stays open; 249aaca carries Closes #20 and will fire when it lands on main.

	commit
`249aaca`	feat(backend)!: run on Postgres with a migration-owned schema
`7a0c190`	docs: record the domain model and the Postgres/OAuth ADRs

How each criterion was met

Starts on a connection URL — DATABASE_URL, required, log.Fatal when empty (main.go). Kept out of the startup log line since it carries a password.
Migration runner — //go:embed migrations/*.sql, schema_migrations version table, one transaction per file via applyMigration, applied on every start. TestOpenIsIdempotent covers the re-run.
Types — favorite boolean, last_chapter_num/latest_chapter_num double precision, updated_at/latest_checked_at bigint. Confirmed live with \d bookmarks.
IS DISTINCT FROM — in both the updated_at CASE and the finished-series filter. Verified against a live server: a favourite toggle left updated_at at …5958, a progress write moved it to …6013.
Parameterised — every ? is now $N; bookmarkColumns remains the only compile-time constant concatenated. The ::text casts on $14/$15 are load-bearing: inside COALESCE/NULLIF there is no target column to infer the parameter type from and Postgres refuses to guess.
Legacy machinery deleted — migrateColumns, addedColumns, existingColumns and migrateAsuraKeys are gone, with the six tests that seeded legacy schemas. The Asura rewrite was confirmed to have no remaining work as the ticket predicted. Its regexp is not migration machinery, so it was rehomed as unexported latest.asuraBuildHash — the poller still needs it to scope chapter links for a series whose slug carries a rotating build hash.
Test containers — internal/pgtest: one postgres:17-alpine per test binary (TestMain → pgtest.Main), CREATE DATABASE test_N per test. Hand-rolled rather than testcontainers: one docker run, one docker port and a ping loop, against a module list that is otherwise stdlib.
Driver gone — grep -c sqlite go.mod go.sum → 0. The whole modernc.org/* tree went with it.
Compose — postgres service with a pg_isready healthcheck on an internal: true db network, postgres-data volume, no published port. DB_PATH retired everywhere. The pre-migration bookmarks-data volume is deliberately left undeclared so docker compose down -v cannot take it — that is user story 6 of #18 kept safe ahead of time.
README — the Develop/test section now leads with go test ./... requires Docker and explains the container-per-package model.
Suite — go test -count=1 ./... all green in 7.3s. gofmt/go vet clean (poller.go and web.go were already unformatted before this change and are untouched).

Beyond the checklist

Smoke-tested against a real Postgres, not just the suite: healthz, 401 on unauthenticated, GET/PUT/DELETE, a 204 preflight with CORS headers, and the web UI. Restarting the binary on a populated database re-runs the migration silently.

REDEPLOY.md and DEPLOY.md were rewritten off SQLite — pg_dump -Fc / pg_restore replace VACUUM INTO, and the -wal/-shm and chown 65532 advice is gone. A redeploy runbook that quietly backs up nothing would have been the worst outcome of this change.

Two-axis review clean: 0 standards violations, 11/11 spec criteria, no scope creep. The one judgement call — newTestStore repeated in three test packages — was left alone: store_test.go is in package store, so a shared pgtest.Store(t) helper would be an import cycle.

Nothing here touches the reader/series/session split, Discord OAuth, per-reader tokens or the 29-row data migration. Those stay in their own tickets under #18, as scoped.

Implemented on `feat/db-change` — **not merged, so this stays open**; `249aaca` carries `Closes #20` and will fire when it lands on `main`. | | commit | |---|---| | `249aaca` | feat(backend)!: run on Postgres with a migration-owned schema | | `7a0c190` | docs: record the domain model and the Postgres/OAuth ADRs | ### How each criterion was met 1. **Starts on a connection URL** — `DATABASE_URL`, required, `log.Fatal` when empty (`main.go`). Kept out of the startup log line since it carries a password. 2. **Migration runner** — `//go:embed migrations/*.sql`, `schema_migrations` version table, one transaction per file via `applyMigration`, applied on every start. `TestOpenIsIdempotent` covers the re-run. 3. **Types** — `favorite boolean`, `last_chapter_num`/`latest_chapter_num double precision`, `updated_at`/`latest_checked_at bigint`. Confirmed live with `\d bookmarks`. 4. **`IS DISTINCT FROM`** — in both the `updated_at` CASE and the finished-series filter. Verified against a live server: a favourite toggle left `updated_at` at `…5958`, a progress write moved it to `…6013`. 5. **Parameterised** — every `?` is now `$N`; `bookmarkColumns` remains the only compile-time constant concatenated. The `::text` casts on `$14`/`$15` are load-bearing: inside `COALESCE`/`NULLIF` there is no target column to infer the parameter type from and Postgres refuses to guess. 6. **Legacy machinery deleted** — `migrateColumns`, `addedColumns`, `existingColumns` and `migrateAsuraKeys` are gone, with the six tests that seeded legacy schemas. The Asura rewrite was confirmed to have no remaining work as the ticket predicted. Its regexp is *not* migration machinery, so it was rehomed as unexported `latest.asuraBuildHash` — the poller still needs it to scope chapter links for a series whose slug carries a rotating build hash. 7. **Test containers** — `internal/pgtest`: one `postgres:17-alpine` per test binary (`TestMain` → `pgtest.Main`), `CREATE DATABASE test_N` per test. Hand-rolled rather than testcontainers: one `docker run`, one `docker port` and a ping loop, against a module list that is otherwise stdlib. 8. **Driver gone** — `grep -c sqlite go.mod go.sum` → 0. The whole `modernc.org/*` tree went with it. 9. **Compose** — `postgres` service with a `pg_isready` healthcheck on an `internal: true` `db` network, `postgres-data` volume, no published port. `DB_PATH` retired everywhere. The pre-migration `bookmarks-data` volume is deliberately left *undeclared* so `docker compose down -v` cannot take it — that is user story 6 of #18 kept safe ahead of time. 10. **README** — the Develop/test section now leads with **`go test ./...` requires Docker** and explains the container-per-package model. 11. **Suite** — `go test -count=1 ./...` all green in 7.3s. `gofmt`/`go vet` clean (`poller.go` and `web.go` were already unformatted before this change and are untouched). ### Beyond the checklist Smoke-tested against a real Postgres, not just the suite: healthz, 401 on unauthenticated, `GET`/`PUT`/`DELETE`, a 204 preflight with CORS headers, and the web UI. Restarting the binary on a populated database re-runs the migration silently. `REDEPLOY.md` and `DEPLOY.md` were rewritten off SQLite — `pg_dump -Fc` / `pg_restore` replace `VACUUM INTO`, and the `-wal`/`-shm` and `chown 65532` advice is gone. A redeploy runbook that quietly backs up nothing would have been the worst outcome of this change. Two-axis review clean: 0 standards violations, 11/11 spec criteria, no scope creep. The one judgement call — `newTestStore` repeated in three test packages — was left alone: `store_test.go` is *in* package `store`, so a shared `pgtest.Store(t)` helper would be an import cycle. Nothing here touches the reader/series/session split, Discord OAuth, per-reader tokens or the 29-row data migration. Those stay in their own tickets under #18, as scoped.

sulthan referenced this issue from a commit

2026-08-08 06:50:15 +07:00

feat(backend)!: run on Postgres with a migration-owned schema

sulthan referenced a pull request that will close this issue

2026-08-08 06:52:04 +07:00

Run on Postgres with today's schema #28

sulthan closed this issue

2026-08-08 06:52:21 +07:00

sulthan referenced this issue from a commit

2026-08-08 06:52:22 +07:00

feat(backend)!: run on Postgres with a migration-owned schema (#28)

sulthan referenced this issue