Prove the import against a copy of the real library (#25) #33

2026-08-08T15:11:20+07:00

sulthan commented

2026-08-08 15:11:20 +07:00

Closes #25.

Retires the biggest risk in #18 — losing the owner's reading history — on a copy, before production is anywhere near it.

What was run

A throwaway generator (python3 stdlib sqlite3, ~20 lines, not committed) read a copy of bookmarks-20260807-213515.db and emitted plain SQL: 29 distinct Series first, then 29 Bookmarks referencing them, each INSERT ... SELECT id FROM owner so the reader id is resolved rather than hardcoded. The target was a scratch Postgres whose schema and owner Reader were built by the real binary (go run . against a throwaway container), not by hand-written DDL. Production was not touched.

Verified

check	result
Bookmarks total	29
reading / archived / other	18 / 11 / 0
Series	29, equal to the distinct `(site, series_id)` count in the source
Readers	1; Bookmarks not owned by the owner: 0
Field-by-field diff, all 29 rows x 15 columns	0 differences
`GET /bookmarks` over the real read path	29 rows, values match source
`TRUNCATE bookmarks, series;` then re-apply	clean, 29 again

The spot-check the ticket asked for was widened to a full row-by-row comparison — 29 rows is small enough that sampling was the more expensive option.

What is committed

CUTOVER.md only, plus two cross-links from REDEPLOY.md. The generator stays out of the repository: its output is the owner's reading history, and it reads SQLite, which the backend module dropped in ADR-0001. So the runbook specifies the transformation — column mapping, ordering, nullability, quoting, the temp-table ownership trick — rather than shipping a script. backend/go.mod gains nothing.

Review

Two-axis review ran on the diff; six findings applied, all in the runbook:

Six source columns (title, series_url, cover, last_chapter, last_chapter_url, last_chapter_num) are nullable in SQLite but NOT NULL in Postgres and must be coalesced — the opposite of latest_chapter_num, the one column where NULL is meaningful. The 2026-08-07 export had none; a fresh one is not promised the same.
CREATE TEMP TABLE ... ON COMMIT DROP must sit inside the transaction, or psql's autocommit drops it instantly.
The ownership check now resolves the Reader by Discord id; comparing against ORDER BY id LIMIT 1 was true by construction and could never fail.
The spot-check now samples archived and favourite rows explicitly instead of hoping they fall inside ORDER BY updated_at DESC LIMIT 5.
git pull --ff-only before up -d --build, or a pre-cutover server rebuilds the SQLite image.
python3 and jq named as prerequisites; column count corrected to sixteen.

Every query in the runbook was executed against the scratch database as written.

go vet, CGO_ENABLED=0 go build ./... and go test ./... all pass — no Go code changed.

Closes #25. Retires the biggest risk in #18 — losing the owner's reading history — on a copy, before production is anywhere near it. ## What was run A throwaway generator (python3 stdlib `sqlite3`, ~20 lines, **not committed**) read a copy of `bookmarks-20260807-213515.db` and emitted plain SQL: 29 distinct Series first, then 29 Bookmarks referencing them, each `INSERT ... SELECT id FROM owner` so the reader id is resolved rather than hardcoded. The target was a scratch Postgres whose schema and owner Reader were built by the real binary (`go run .` against a throwaway container), not by hand-written DDL. Production was not touched. ## Verified | check | result | |---|---| | Bookmarks total | 29 | | reading / archived / other | 18 / 11 / 0 | | Series | 29, equal to the distinct `(site, series_id)` count in the source | | Readers | 1; Bookmarks not owned by the owner: 0 | | Field-by-field diff, all 29 rows x 15 columns | 0 differences | | `GET /bookmarks` over the real read path | 29 rows, values match source | | `TRUNCATE bookmarks, series;` then re-apply | clean, 29 again | The spot-check the ticket asked for was widened to a full row-by-row comparison — 29 rows is small enough that sampling was the more expensive option. ## What is committed `CUTOVER.md` only, plus two cross-links from `REDEPLOY.md`. The generator stays out of the repository: its output is the owner's reading history, and it reads SQLite, which the backend module dropped in ADR-0001. So the runbook specifies the transformation — column mapping, ordering, nullability, quoting, the temp-table ownership trick — rather than shipping a script. `backend/go.mod` gains nothing. ## Review Two-axis review ran on the diff; six findings applied, all in the runbook: - Six source columns (`title`, `series_url`, `cover`, `last_chapter`, `last_chapter_url`, `last_chapter_num`) are nullable in SQLite but `NOT NULL` in Postgres and must be coalesced — the opposite of `latest_chapter_num`, the one column where `NULL` is meaningful. The 2026-08-07 export had none; a fresh one is not promised the same. - `CREATE TEMP TABLE ... ON COMMIT DROP` must sit *inside* the transaction, or psql's autocommit drops it instantly. - The ownership check now resolves the Reader by Discord id; comparing against `ORDER BY id LIMIT 1` was true by construction and could never fail. - The spot-check now samples archived and favourite rows explicitly instead of hoping they fall inside `ORDER BY updated_at DESC LIMIT 5`. - `git pull --ff-only` before `up -d --build`, or a pre-cutover server rebuilds the SQLite image. - `python3` and `jq` named as prerequisites; column count corrected to sixteen. Every query in the runbook was executed against the scratch database as written. `go vet`, `CGO_ENABLED=0 go build ./...` and `go test ./...` all pass — no Go code changed.

sulthan added 1 commit 2026-08-08 15:11:21 +07:00

docs: SQLite to Postgres cutover runbook (#25 ) 056989ea1d

Proves the import against a copy of the real library before the real one is
at risk, and writes the procedure down so it can be repeated at cutover
against a fresh export.

Verified on 2026-08-08: a throwaway generator read a copy of
bookmarks-20260807-213515.db and emitted Series-then-Bookmarks SQL against a
scratch Postgres whose schema and owner Reader were built by the real binary.
Result: 29 Bookmarks (18 reading, 11 archived, 7 favourites), 29 Series
matching the distinct (site, series_id) count, every Bookmark owned by the
seeded Reader, and a field-by-field diff of all 29 rows against the source
showing zero differences. GET /bookmarks over the real read path returned the
same 29. Production was not touched.

The generator itself stays out of the repository: its output is the owner's
reading history, and it reads SQLite, which the backend module dropped in
ADR-0001. CUTOVER.md specifies the transformation instead of shipping a
script.

sulthan referenced this pull request

2026-08-08 15:11:52 +07:00

Prove the import against a copy of the real library #25

sulthan merged commit 2cc1e69f5d into main

2026-08-08 15:15:34 +07:00

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: sulthan/mangaBookmark#33