sulthan/mangaBookmark
1
0
Fork 0
Code Issues 4 Pull Requests Actions Packages Projects Releases Wiki Activity

Migrate to Postgres and a Reader-owned schema (Step 1: foundation, single Reader) #18

New Issue
Closed
opened 2026-08-08 05:58:29 +07:00 by sulthan · 1 comment
sulthan commented 2026-08-08 05:58:29 +07:00
Owner
Copy Link
Copy Source

Problem Statement

The bookmark manager works, and it works for exactly one person. That is not an accident of the code — it is built into every layer: a single shared API_TOKEN baked as a literal into both userscripts, a single shared WEB_PASSWORD gating the browser UI, a session cookie whose signing key is derived from those two secrets, and one bookmarks table with no notion of who a row belongs to.

The owner wants to publish this to their Discord community. Before anyone else can be let in, the foundation has to change: rows need an owner, people need real identities, and the poller needs to stop being something that would collapse under a second reader.

Cutting across all of that is the thing the owner actually cares about most: 29 rows of real reading history — 18 reading, 11 archived, all manga — must survive intact and end up attached to the owner's own identity. A migration that loses or orphans that history is a failure regardless of how clean the resulting architecture is.

This spec covers the foundation only. When it lands, everything works exactly as it does today from the outside, but the owner logs in with Discord instead of a password, their history is intact, and the system is structurally ready for other Readers. Nobody else can register yet — that is Step 2, deliberately kept separate so the data migration is proven while the owner is still the only person who can be broken by it.

Solution

Replace SQLite with Postgres, split the single flat table into readers, series, bookmarks and sessions, and replace both shared secrets with Discord OAuth for the web UI and a per-Reader bearer token for the userscripts. Seed exactly one Reader — the owner, identified by their Discord user ID — and attach all 29 existing rows to them.

Two things stay deliberately unchanged, and both are load-bearing:

The wire format does not change. GET /bookmarks and PUT /bookmarks/{key} keep emitting and accepting the same flat JSON object they do today, with title, cover, last_chapter and latest_chapter as siblings. The two-table split is invisible outside the store. This is what makes the 14-day grace window real rather than theoretical: already-installed userscripts keep working while Violentmonkey pushes updates out device by device.

The updated_at ordering rule survives the dialect change. Only a change in Progress reorders a Reader's list. SQLite's null-safe IS NOT becomes Postgres's IS DISTINCT FROM; the behaviour must be identical.

User Stories

  1. As the owner, I want my 29 existing bookmarks to appear under my account after the migration, so that years of reading progress are not lost to an infrastructure change.
  2. As the owner, I want my archived series to still be archived and my reading series to still be reading, so that my lifecycle buckets survive the move.
  3. As the owner, I want my favourites to still be favourites, so that my manual curation is not silently discarded.
  4. As the owner, I want my read position in each series preserved exactly, so that I do not have to find my place again in eighteen different series.
  5. As the owner, I want to verify the migrated data before the old database is discarded, so that I can abort if something looks wrong.
  6. As the owner, I want the old SQLite volume kept for a month after cutover, so that a problem discovered late is still recoverable.
  7. As the owner, I want to log into the web UI with Discord, so that I do not have to remember a shared password.
  8. As the owner, I want to be the only person who can register during this step, so that the foundation is proven before strangers can reach it.
  9. As the owner, I want my identity tied to my Discord user ID from the moment of migration, so that there is no race to claim my own data on first login.
  10. As a Reader, I want the app to never ask me to type or paste a token, so that installing the userscript is not a technical chore.
  11. As a Reader, I want to click one button in the web UI and have the userscript install with my credential already inside it, so that setup is two clicks total.
  12. As a Reader, I want my userscript to keep auto-updating after install, so that I get fixes without reinstalling.
  13. As a Reader, I want to rotate my userscript token if I think it has leaked, so that a compromised device does not mean permanent exposure.
  14. As a Reader, I want to be warned that rotating my token means reinstalling on every device, so that I am not surprised when a device silently stops syncing.
  15. As a Reader, I want my bookmarks to be mine alone, so that no other Reader can see or change my reading progress.
  16. As a Reader, I want my session to be revocable, so that losing a device does not mean sixty days of exposure.
  17. As a Reader, I want to be rejected at login if I am not in the configured Discord guild, so that the app is genuinely limited to the community.
  18. As a Reader, I want the app to ask Discord only whether I am in one specific server, so that authenticating does not hand over a list of every server I belong to.
  19. As a Reader with an already-installed userscript, I want it to keep working for two weeks after the cutover, so that I am not broken by an update I did not initiate.
  20. As a Reader, I want my panel to keep showing titles, covers and chapter numbers after the migration, so that the storage change is invisible to me.
  21. As a Reader, I want a New Chapter to still be signalled the moment the site publishes past my position, so that the feature I use the app for keeps working.
  22. As a Reader, I want recording a newly published chapter to not reorder my list, so that the list still reflects what I have actually been reading.
  23. As a Reader, I want favouriting a series to not reorder my list, so that curation and activity stay separate signals.
  24. As a Reader, I want my list order to change only when I actually make reading progress, so that the ordering means something.
  25. As the owner, I want a series that fifty people track to be polled once rather than fifty times, so that the backend does not multiply its outbound traffic by its user count.
  26. As the owner, I want popular series checked before neglected ones, so that the poll budget is spent where readers actually are.
  27. As the owner, I want the poller's outbound request rate to stay exactly where it is, so that the VPS does not get bot-scored by the manga sites.
  28. As the owner, I want a series that no Reader bookmarks any more to stop consuming poll budget, so that abandoned series do not crowd out ones people are actually reading.
  29. As a Reader, I want re-bookmarking a series I previously removed to show its title and cover immediately, so that removing and re-adding is not a downgrade.
  30. As a Reader, I want a hostile or compromised manga site to be unable to change the title or cover that other Readers see, so that one person's browser is not a write path into everyone else's UI.
  31. As a Reader, I want a series' title and cover to come from the backend's own fetch rather than from whatever a scraped page claimed, so that shared data has a single trustworthy source.
  32. As the owner, I want the first bookmark of a brand-new series to still capture its title and cover from the client, so that a series nobody has polled yet is not blank.
  33. As a maintainer, I want the test suite to exercise the same SQL dialect that production runs, so that a dialect bug cannot pass tests and fail in production.
  34. As a maintainer, I want each test to get an isolated database, so that tests cannot interfere with each other or leak state.
  35. As a maintainer, I want no test to touch the real network, so that the suite is deterministic and runnable offline.
  36. As a maintainer, I want the OAuth token exchange itself covered by tests, so that a malformed request to Discord is caught before deploy rather than after.
  37. As a maintainer, I want migrations to run automatically on startup and be safe to re-run, so that deploying is not a manual database chore.
  38. As a maintainer, I want each migration applied in a transaction, so that a failed migration leaves no half-applied schema.
  39. As a maintainer, I want run-once data migrations to be distinguishable from idempotent schema migrations, so that a backfill cannot silently run twice.
  40. As a maintainer, I want the documented backup procedure updated in the same change, so that the runbook is not describing a database that no longer exists.
  41. As a maintainer, I want the Docker prerequisite for running tests documented, so that a fresh clone failing go test is self-explanatory.
  42. As a maintainer, I want secrets kept out of logs and error responses, so that the existing security posture is not regressed by the rewrite.
  43. As a maintainer, I want the userscript token stored hashed rather than in plaintext, so that a database leak does not hand over every Reader's library.
  44. As a maintainer, I want retired environment variables removed rather than left dangling, so that the deployment contract stays honest.
  45. As the owner, I want the app to keep running if Discord is briefly unavailable for existing sessions, so that an outage at Discord does not log everyone out.
  46. As the owner, I want to know that a Reader who leaves the guild keeps their session until it is deleted, so that revocation is a deliberate action rather than an assumption.

Implementation Decisions

Recorded as ADR-0001 (Postgres), ADR-0002 (Discord OAuth), ADR-0003 (shared, Poll-owned Series), ADR-0004 (flat wire format). Domain vocabulary — Reader, Series, Bookmark, Progress, Latest Chapter, Poll, Library, Lifecycle bucket — is defined in the repo's CONTEXT.md and should be used throughout the implementation, including in test names.

Datastore. Postgres via jackc/pgx/v5, chosen because it is pure Go and preserves CGO_ENABLED=0 and the distroless image. modernc.org/sqlite is removed from the backend module entirely. The decision was made for future supportability, not concurrency — SQLite was benchmarked and had roughly four and a half orders of magnitude of headroom at the projected load. Do not re-litigate this on performance grounds; see ADR-0001.

Schema. Four tables:

readers (
  id            bigserial PRIMARY KEY,
  discord_id    text NOT NULL UNIQUE,
  token_sha256  bytea NOT NULL UNIQUE,
  created_at    timestamptz NOT NULL DEFAULT now()
)

series (
  site               text NOT NULL,
  series_id          text NOT NULL,
  title              text NOT NULL DEFAULT '',
  series_url         text NOT NULL DEFAULT '',
  cover              text NOT NULL DEFAULT '',
  kind               text NOT NULL DEFAULT 'manga',
  latest_chapter     text NOT NULL DEFAULT '',
  latest_chapter_num double precision,
  latest_checked_at  bigint NOT NULL DEFAULT 0,
  PRIMARY KEY (site, series_id)
)

bookmarks (
  reader_id        bigint NOT NULL REFERENCES readers(id) ON DELETE CASCADE,
  site             text NOT NULL,
  series_id        text NOT NULL,
  last_chapter     text NOT NULL DEFAULT '',
  last_chapter_num double precision NOT NULL DEFAULT 0,
  last_chapter_url text NOT NULL DEFAULT '',
  favorite         boolean NOT NULL DEFAULT false,
  status           text NOT NULL DEFAULT 'reading',
  updated_at       bigint NOT NULL,
  PRIMARY KEY (reader_id, site, series_id),
  FOREIGN KEY (site, series_id) REFERENCES series(site, series_id)
)

sessions (
  id         bytea PRIMARY KEY,
  reader_id  bigint NOT NULL REFERENCES readers(id) ON DELETE CASCADE,
  expires_at timestamptz NOT NULL
)

The bookmarks primary key is composite and carries (site, series_id) because a Series is keyed by that pair — a Series on two Sites is two Series. No surrogate id: nothing references a Bookmark, so a surrogate would add a permanently unused index plus a second unique index to enforce the same rule the composite key enforces structurally.

Series ownership. Facts true regardless of who is reading — title, cover, canonical URL, kind, Latest Chapter — live on series. A Bookmark holds only Progress, Favourite, Lifecycle bucket and updated_at. A client may supply title, cover and series URL only when creating a Series that does not yet exist; afterwards those values are ignored and only the backend's own Poll updates them. This is a security boundary: scraped page content is attacker-controlled, and after the split a single client's write would otherwise land on a row every Reader sees.

Wire format. Unchanged and flat. The store joins on read and decomposes on write. The server continues to own the truth and return the row as stored; clients adopt the response rather than their own payload. Changing storage must not change this contract.

SQL dialect. ? becomes $N. The null-safe comparison in the upsert's updated_at clause becomes IS DISTINCT FROM. favorite becomes a real boolean. Column probing via pragma_table_info is replaced by the migration runner. All SQL stays parameterised; only compile-time constants may be concatenated into query text.

Migrations. A hand-rolled runner: a schema_migrations table keyed by version, numbered SQL files embedded into the binary, each applied inside a transaction, run on startup. No migration library — Postgres has transactional DDL and the runner is roughly forty lines. Idempotent schema migrations and run-once data migrations are both expressed as numbered versions, so the version table is what prevents a backfill running twice.

Identity. Discord OAuth2 authorization code grant, scopes identify and guilds.members.read. Guild membership is checked at login against a single configured guild, using the endpoint that answers membership in that one guild rather than the one that returns the Reader's entire server list. A configurable required-role ID exists but is empty by default, meaning any guild member passes. During this step, registration is additionally restricted to the configured owner Discord ID.

Sessions. A sessions row with an opaque random ID as the cookie value, looked up per request. This replaces the HMAC-signed stateless cookie entirely — the signing, the expiry-before-signature ordering, and the derived key all go away, and no new signing secret is introduced. Revocation becomes a delete. Cookies keep HttpOnly, SameSite, and Secure-when-HTTPS.

Userscript credential. Each Reader has a random high-entropy token, stored as a SHA-256 hash. SHA-256 rather than a password hash is deliberate: these are random tokens with nothing to brute-force, so a slow hash would only add per-request cost. The token authenticates both the userscript download path and the API bearer header — one secret, not two, because anyone who can read the download URL can fetch the script and read the token, making two secrets identical in blast radius at twice the code. The backend renders each script at serve time with the requesting Reader's token substituted in, so no token literal is ever committed and the Reader never sees one. The web UI offers install links and a rotate action, with rotation clearly warning that installed copies must be reinstalled.

Grace window. The retired global API token continues to resolve to the owner's Reader for 14 days after cutover, then stops. This is what the unchanged wire format is protecting.

Poller. Walks Series rather than Bookmarks, so one Series is polled once regardless of how many Readers hold it. The due-check queue is ordered by Reader count descending, then least-recently-checked first, and only includes Series held by at least one Reader. Batch size, stagger and interval are unchanged — outbound request rate must not increase. Sites behind a JavaScript challenge remain browser-only via CDP and remain skipped when that endpoint is unset.

Orphaned Series. Deleting the last Bookmark for a Series leaves the Series row behind. It is deliberately not deleted: it becomes inert — excluded from the poll queue by the rule above — and acts as a warm cache so a Reader who re-bookmarks it sees the title and cover immediately rather than a blank row until the next Poll. This is a behaviour change from the single-table design, where deleting a bookmark removed everything, and it must be explicit rather than incidental. No reaping job; an unreferenced Series costs one small row and consumes no fetch budget.

Discord test seam. The Discord API base URL is a configuration value, so tests point it at a local stub and drive the entire login through the real router. Deliberately not an injected client interface: an interface with one implementation would stub out the request construction, which is exactly where OAuth bugs live — Discord's token endpoint rejects JSON and requires form encoding.

Data migration. A throwaway generator, not committed to the repository, reads a freshly exported SQLite snapshot at cutover time and emits plain SQL: the distinct Series rows first, then the Bookmarks referencing them, all attached to the seeded owner Reader. The emitted SQL is reviewed by eye — 29 rows is small enough — and applied with psql. Nothing about the owner's personal reading history enters version control, and no SQLite dependency survives in the backend. There is no dual-write period; the cutover is one-way.

Configuration. Added: a Postgres connection URL, Discord client ID, client secret, redirect URI, guild ID, an empty-by-default required role ID, and the owner's Discord user ID. Retired: the SQLite database path, the web password, and — after the grace window — the global API token. Compose gains a Postgres service and volume.

Testing Decisions

What makes a good test here. Tests assert externally observable behaviour: the HTTP status, the response body, what a subsequent request sees. They do not assert on internal function calls, private structure, or SQL text. Every test must be capable of failing on a plausible bug — a test that only confirms a field round-trips through a struct defends nothing. Tests stay deterministic, isolated, and safe to run as part of the full suite. The existing prohibition holds absolutely: no test may touch the real network.

Primary seam — the HTTP router. The overwhelming majority of coverage belongs at the existing root-package seam, where tests build the full router and drive it over an in-process HTTP server. This is where today's API and web-UI integration tests already live and it is the highest seam available. It should carry: the OAuth login flow end to end against a stubbed Discord, rejection of a non-guild-member, rejection of a non-owner during this step, session issue and revocation, per-Reader isolation (one Reader must not be able to read or write another's Bookmarks), the flat wire format on both GET and PUT, decomposition of a flat PUT across Series and Bookmark, refusal to let a client overwrite an existing Series' title or cover, acceptance of client-supplied metadata when the Series is new, per-Reader userscript token authentication, token rotation invalidating the old token, the rendered userscript carrying the requesting Reader's token, the grace-window token resolving to the owner, and the existing validation rules — empty key rejected, unknown status and kind rejected, finished refused over the API, empty status and kind meaning "keep what is stored".

Secondary seam — the store's public API. Only for invariants not observable over HTTP: that the composite key makes a duplicate Bookmark for the same Reader and Series impossible, that the updated_at rule holds under IS DISTINCT FROM (a Progress change advances it; a Latest Chapter change or a favourite toggle does not), that the poll queue orders by Reader count then least-recently-checked, and that a Series with zero Bookmarks never appears in the queue at all while its row survives for re-bookmarking. These have direct prior art in the existing store tests.

Third seam — the poller's fetcher interface. Already exists and already carries the no-network rule. Reused unchanged to prove one Series is fetched once no matter how many Readers hold it, that the row is stamped before the fetch so a broken series waits out its full cooldown, and that a failed fetch is logged and skipped.

Fourth seam — the Discord API base URL. The one new seam, and it is configuration rather than an abstraction. A local stub stands in for Discord so the real OAuth client code — including the form-encoded token exchange — is exercised rather than bypassed.

Test infrastructure. Each test package starts a real Postgres container once and gives every individual test its own database cloned from a template, preserving the isolation the current temp-directory approach provides. This makes Docker a hard prerequisite for the suite, which must be documented. Keeping SQLite for tests while shipping Postgres is explicitly rejected — it would mean the dialect rewrite is never actually tested.

Prior art to follow. The root-package integration tests for router-level coverage and their server-construction helpers; the store tests for schema and ordering invariants, including the ones that seed a legacy schema before opening the store; the poller tests for the fake-fetcher pattern and frozen clock.

Migration verification. Not a unit test — a checklist run against production at cutover, before the old volume is released: 29 Bookmarks total, split 18 reading and 11 archived, Series count equal to the number of distinct site and series-ID pairs, and every Bookmark pointing at the owner's Reader.

Out of Scope

  • Letting anyone other than the owner register. That is Step 2 and is deliberately separated so this migration is proven while the owner is the only person who can be broken by it.
  • Raising the poller's throughput. Sweeping the projected Series count hourly would require roughly halving the stagger, doubling request rate against sites that already bot-score the single VPS IP. Prioritisation within the existing budget is in scope; raising the budget is not.
  • Per-device userscript tokens. One token per Reader. Per-device revocation is a real feature and not one anybody needs at this size; mark the deferral in code rather than building it.
  • Per-Reader title or cover overrides. Rejected — it would reintroduce the duplication the Series split removes.
  • Email in any form. No addresses stored, no messages sent, no verification, no password reset. Identity comes from Discord.
  • Passwords. None are stored. There is no local credential to reset.
  • Continuous guild-membership enforcement. Membership is checked at login. Someone who leaves keeps their session until it is deleted.
  • Automated backups. The runbook's manual procedure is updated for Postgres; automating it is separate work.
  • Rewriting PRODUCT.md. Its single-user claim becomes false, but it is corrected in Step 2 when the claim is actually false in practice.
  • Any change to the userscripts' site adapters, retry queue, or panel UI. The wire format is unchanged precisely so none of that has to move.

Further Notes

The concurrency question was settled by measurement rather than argument, and the numbers are worth keeping in view because they will otherwise be re-argued: against the real store at 1,500 rows, single-writer throughput was ~9,700 upserts/sec, plateauing at ~780/sec under 8 to 50 concurrent writers, with list queries holding 90–103/sec under continuous write load. Projected real load at 50 Readers is around 0.02 writes/sec. An apparent read collapse during benchmarking traced to row count and per-row scanning, not lock contention — the wrong diagnosis would have justified the right decision for a false reason.

The real scaling limit is the poller's outbound fetch budget: at the configured batch and interval it checks 84 Series per hour, and no datastore choice moves that number. Deduplicating polls per Series is what addresses it, which is why ADR-0003 is a scaling decision as much as a modelling one.

One fact was verified from Discord's documentation but not from a live flow: that membership in a single guild can be checked without the application being present in that guild as a bot. If that endpoint misbehaves when wired up, the fallback is the broader scope that returns the Reader's full guild list and scanning it locally — same enforcement, worse privacy, and it should be treated as a fallback rather than a first choice.

The backup story genuinely regresses. Today disaster recovery is one self-contained file produced by a single command. Afterwards it is a dump, a role, and a restore procedure. This was accepted with open eyes; the runbook must be updated in this change rather than after it.

## Problem Statement The bookmark manager works, and it works for exactly one person. That is not an accident of the code — it is built into every layer: a single shared `API_TOKEN` baked as a literal into both userscripts, a single shared `WEB_PASSWORD` gating the browser UI, a session cookie whose signing key is derived from those two secrets, and one `bookmarks` table with no notion of who a row belongs to. The owner wants to publish this to their Discord community. Before anyone else can be let in, the foundation has to change: rows need an owner, people need real identities, and the poller needs to stop being something that would collapse under a second reader. Cutting across all of that is the thing the owner actually cares about most: **29 rows of real reading history — 18 reading, 11 archived, all manga — must survive intact and end up attached to the owner's own identity.** A migration that loses or orphans that history is a failure regardless of how clean the resulting architecture is. This spec covers the foundation only. When it lands, everything works exactly as it does today from the outside, but the owner logs in with Discord instead of a password, their history is intact, and the system is structurally ready for other Readers. Nobody else can register yet — that is Step 2, deliberately kept separate so the data migration is proven while the owner is still the only person who can be broken by it. ## Solution Replace SQLite with Postgres, split the single flat table into `readers`, `series`, `bookmarks` and `sessions`, and replace both shared secrets with Discord OAuth for the web UI and a per-Reader bearer token for the userscripts. Seed exactly one Reader — the owner, identified by their Discord user ID — and attach all 29 existing rows to them. Two things stay deliberately unchanged, and both are load-bearing: **The wire format does not change.** `GET /bookmarks` and `PUT /bookmarks/{key}` keep emitting and accepting the same flat JSON object they do today, with `title`, `cover`, `last_chapter` and `latest_chapter` as siblings. The two-table split is invisible outside the store. This is what makes the 14-day grace window real rather than theoretical: already-installed userscripts keep working while Violentmonkey pushes updates out device by device. **The `updated_at` ordering rule survives the dialect change.** Only a change in Progress reorders a Reader's list. SQLite's null-safe `IS NOT` becomes Postgres's `IS DISTINCT FROM`; the behaviour must be identical. ## User Stories 1. As the owner, I want my 29 existing bookmarks to appear under my account after the migration, so that years of reading progress are not lost to an infrastructure change. 2. As the owner, I want my archived series to still be archived and my reading series to still be reading, so that my lifecycle buckets survive the move. 3. As the owner, I want my favourites to still be favourites, so that my manual curation is not silently discarded. 4. As the owner, I want my read position in each series preserved exactly, so that I do not have to find my place again in eighteen different series. 5. As the owner, I want to verify the migrated data before the old database is discarded, so that I can abort if something looks wrong. 6. As the owner, I want the old SQLite volume kept for a month after cutover, so that a problem discovered late is still recoverable. 7. As the owner, I want to log into the web UI with Discord, so that I do not have to remember a shared password. 8. As the owner, I want to be the only person who can register during this step, so that the foundation is proven before strangers can reach it. 9. As the owner, I want my identity tied to my Discord user ID from the moment of migration, so that there is no race to claim my own data on first login. 10. As a Reader, I want the app to never ask me to type or paste a token, so that installing the userscript is not a technical chore. 11. As a Reader, I want to click one button in the web UI and have the userscript install with my credential already inside it, so that setup is two clicks total. 12. As a Reader, I want my userscript to keep auto-updating after install, so that I get fixes without reinstalling. 13. As a Reader, I want to rotate my userscript token if I think it has leaked, so that a compromised device does not mean permanent exposure. 14. As a Reader, I want to be warned that rotating my token means reinstalling on every device, so that I am not surprised when a device silently stops syncing. 15. As a Reader, I want my bookmarks to be mine alone, so that no other Reader can see or change my reading progress. 16. As a Reader, I want my session to be revocable, so that losing a device does not mean sixty days of exposure. 17. As a Reader, I want to be rejected at login if I am not in the configured Discord guild, so that the app is genuinely limited to the community. 18. As a Reader, I want the app to ask Discord only whether I am in one specific server, so that authenticating does not hand over a list of every server I belong to. 19. As a Reader with an already-installed userscript, I want it to keep working for two weeks after the cutover, so that I am not broken by an update I did not initiate. 20. As a Reader, I want my panel to keep showing titles, covers and chapter numbers after the migration, so that the storage change is invisible to me. 21. As a Reader, I want a New Chapter to still be signalled the moment the site publishes past my position, so that the feature I use the app for keeps working. 22. As a Reader, I want recording a newly published chapter to not reorder my list, so that the list still reflects what I have actually been reading. 23. As a Reader, I want favouriting a series to not reorder my list, so that curation and activity stay separate signals. 24. As a Reader, I want my list order to change only when I actually make reading progress, so that the ordering means something. 25. As the owner, I want a series that fifty people track to be polled once rather than fifty times, so that the backend does not multiply its outbound traffic by its user count. 26. As the owner, I want popular series checked before neglected ones, so that the poll budget is spent where readers actually are. 27. As the owner, I want the poller's outbound request rate to stay exactly where it is, so that the VPS does not get bot-scored by the manga sites. 28. As the owner, I want a series that no Reader bookmarks any more to stop consuming poll budget, so that abandoned series do not crowd out ones people are actually reading. 29. As a Reader, I want re-bookmarking a series I previously removed to show its title and cover immediately, so that removing and re-adding is not a downgrade. 30. As a Reader, I want a hostile or compromised manga site to be unable to change the title or cover that other Readers see, so that one person's browser is not a write path into everyone else's UI. 31. As a Reader, I want a series' title and cover to come from the backend's own fetch rather than from whatever a scraped page claimed, so that shared data has a single trustworthy source. 32. As the owner, I want the first bookmark of a brand-new series to still capture its title and cover from the client, so that a series nobody has polled yet is not blank. 33. As a maintainer, I want the test suite to exercise the same SQL dialect that production runs, so that a dialect bug cannot pass tests and fail in production. 34. As a maintainer, I want each test to get an isolated database, so that tests cannot interfere with each other or leak state. 35. As a maintainer, I want no test to touch the real network, so that the suite is deterministic and runnable offline. 36. As a maintainer, I want the OAuth token exchange itself covered by tests, so that a malformed request to Discord is caught before deploy rather than after. 37. As a maintainer, I want migrations to run automatically on startup and be safe to re-run, so that deploying is not a manual database chore. 38. As a maintainer, I want each migration applied in a transaction, so that a failed migration leaves no half-applied schema. 39. As a maintainer, I want run-once data migrations to be distinguishable from idempotent schema migrations, so that a backfill cannot silently run twice. 40. As a maintainer, I want the documented backup procedure updated in the same change, so that the runbook is not describing a database that no longer exists. 41. As a maintainer, I want the Docker prerequisite for running tests documented, so that a fresh clone failing `go test` is self-explanatory. 42. As a maintainer, I want secrets kept out of logs and error responses, so that the existing security posture is not regressed by the rewrite. 43. As a maintainer, I want the userscript token stored hashed rather than in plaintext, so that a database leak does not hand over every Reader's library. 44. As a maintainer, I want retired environment variables removed rather than left dangling, so that the deployment contract stays honest. 45. As the owner, I want the app to keep running if Discord is briefly unavailable for existing sessions, so that an outage at Discord does not log everyone out. 46. As the owner, I want to know that a Reader who leaves the guild keeps their session until it is deleted, so that revocation is a deliberate action rather than an assumption. ## Implementation Decisions Recorded as ADR-0001 (Postgres), ADR-0002 (Discord OAuth), ADR-0003 (shared, Poll-owned Series), ADR-0004 (flat wire format). Domain vocabulary — Reader, Series, Bookmark, Progress, Latest Chapter, Poll, Library, Lifecycle bucket — is defined in the repo's `CONTEXT.md` and should be used throughout the implementation, including in test names. **Datastore.** Postgres via `jackc/pgx/v5`, chosen because it is pure Go and preserves `CGO_ENABLED=0` and the distroless image. `modernc.org/sqlite` is removed from the backend module entirely. The decision was made for future supportability, **not** concurrency — SQLite was benchmarked and had roughly four and a half orders of magnitude of headroom at the projected load. Do not re-litigate this on performance grounds; see ADR-0001. **Schema.** Four tables: ```sql readers ( id bigserial PRIMARY KEY, discord_id text NOT NULL UNIQUE, token_sha256 bytea NOT NULL UNIQUE, created_at timestamptz NOT NULL DEFAULT now() ) series ( site text NOT NULL, series_id text NOT NULL, title text NOT NULL DEFAULT '', series_url text NOT NULL DEFAULT '', cover text NOT NULL DEFAULT '', kind text NOT NULL DEFAULT 'manga', latest_chapter text NOT NULL DEFAULT '', latest_chapter_num double precision, latest_checked_at bigint NOT NULL DEFAULT 0, PRIMARY KEY (site, series_id) ) bookmarks ( reader_id bigint NOT NULL REFERENCES readers(id) ON DELETE CASCADE, site text NOT NULL, series_id text NOT NULL, last_chapter text NOT NULL DEFAULT '', last_chapter_num double precision NOT NULL DEFAULT 0, last_chapter_url text NOT NULL DEFAULT '', favorite boolean NOT NULL DEFAULT false, status text NOT NULL DEFAULT 'reading', updated_at bigint NOT NULL, PRIMARY KEY (reader_id, site, series_id), FOREIGN KEY (site, series_id) REFERENCES series(site, series_id) ) sessions ( id bytea PRIMARY KEY, reader_id bigint NOT NULL REFERENCES readers(id) ON DELETE CASCADE, expires_at timestamptz NOT NULL ) ``` The `bookmarks` primary key is composite and carries `(site, series_id)` because a Series is keyed by that pair — a Series on two Sites is two Series. No surrogate `id`: nothing references a Bookmark, so a surrogate would add a permanently unused index plus a second unique index to enforce the same rule the composite key enforces structurally. **Series ownership.** Facts true regardless of who is reading — title, cover, canonical URL, kind, Latest Chapter — live on `series`. A Bookmark holds only Progress, Favourite, Lifecycle bucket and `updated_at`. A client may supply title, cover and series URL **only when creating a Series that does not yet exist**; afterwards those values are ignored and only the backend's own Poll updates them. This is a security boundary: scraped page content is attacker-controlled, and after the split a single client's write would otherwise land on a row every Reader sees. **Wire format.** Unchanged and flat. The store joins on read and decomposes on write. The server continues to own the truth and return the row as stored; clients adopt the response rather than their own payload. Changing storage must not change this contract. **SQL dialect.** `?` becomes `$N`. The null-safe comparison in the upsert's `updated_at` clause becomes `IS DISTINCT FROM`. `favorite` becomes a real `boolean`. Column probing via `pragma_table_info` is replaced by the migration runner. All SQL stays parameterised; only compile-time constants may be concatenated into query text. **Migrations.** A hand-rolled runner: a `schema_migrations` table keyed by version, numbered SQL files embedded into the binary, each applied inside a transaction, run on startup. No migration library — Postgres has transactional DDL and the runner is roughly forty lines. Idempotent schema migrations and run-once data migrations are both expressed as numbered versions, so the version table is what prevents a backfill running twice. **Identity.** Discord OAuth2 authorization code grant, scopes `identify` and `guilds.members.read`. Guild membership is checked at login against a single configured guild, using the endpoint that answers membership in that one guild rather than the one that returns the Reader's entire server list. A configurable required-role ID exists but is empty by default, meaning any guild member passes. During this step, registration is additionally restricted to the configured owner Discord ID. **Sessions.** A `sessions` row with an opaque random ID as the cookie value, looked up per request. This replaces the HMAC-signed stateless cookie entirely — the signing, the expiry-before-signature ordering, and the derived key all go away, and no new signing secret is introduced. Revocation becomes a delete. Cookies keep `HttpOnly`, `SameSite`, and `Secure`-when-HTTPS. **Userscript credential.** Each Reader has a random high-entropy token, stored as a SHA-256 hash. SHA-256 rather than a password hash is deliberate: these are random tokens with nothing to brute-force, so a slow hash would only add per-request cost. The token authenticates both the userscript download path and the API bearer header — one secret, not two, because anyone who can read the download URL can fetch the script and read the token, making two secrets identical in blast radius at twice the code. The backend renders each script at serve time with the requesting Reader's token substituted in, so no token literal is ever committed and the Reader never sees one. The web UI offers install links and a rotate action, with rotation clearly warning that installed copies must be reinstalled. **Grace window.** The retired global API token continues to resolve to the owner's Reader for 14 days after cutover, then stops. This is what the unchanged wire format is protecting. **Poller.** Walks Series rather than Bookmarks, so one Series is polled once regardless of how many Readers hold it. The due-check queue is ordered by Reader count descending, then least-recently-checked first, and **only includes Series held by at least one Reader**. Batch size, stagger and interval are unchanged — outbound request rate must not increase. Sites behind a JavaScript challenge remain browser-only via CDP and remain skipped when that endpoint is unset. **Orphaned Series.** Deleting the last Bookmark for a Series leaves the Series row behind. It is deliberately **not** deleted: it becomes inert — excluded from the poll queue by the rule above — and acts as a warm cache so a Reader who re-bookmarks it sees the title and cover immediately rather than a blank row until the next Poll. This is a behaviour change from the single-table design, where deleting a bookmark removed everything, and it must be explicit rather than incidental. No reaping job; an unreferenced Series costs one small row and consumes no fetch budget. **Discord test seam.** The Discord API base URL is a configuration value, so tests point it at a local stub and drive the entire login through the real router. Deliberately not an injected client interface: an interface with one implementation would stub out the request construction, which is exactly where OAuth bugs live — Discord's token endpoint rejects JSON and requires form encoding. **Data migration.** A throwaway generator, not committed to the repository, reads a **freshly exported** SQLite snapshot at cutover time and emits plain SQL: the distinct Series rows first, then the Bookmarks referencing them, all attached to the seeded owner Reader. The emitted SQL is reviewed by eye — 29 rows is small enough — and applied with `psql`. Nothing about the owner's personal reading history enters version control, and no SQLite dependency survives in the backend. There is no dual-write period; the cutover is one-way. **Configuration.** Added: a Postgres connection URL, Discord client ID, client secret, redirect URI, guild ID, an empty-by-default required role ID, and the owner's Discord user ID. Retired: the SQLite database path, the web password, and — after the grace window — the global API token. Compose gains a Postgres service and volume. ## Testing Decisions **What makes a good test here.** Tests assert externally observable behaviour: the HTTP status, the response body, what a subsequent request sees. They do not assert on internal function calls, private structure, or SQL text. Every test must be capable of failing on a plausible bug — a test that only confirms a field round-trips through a struct defends nothing. Tests stay deterministic, isolated, and safe to run as part of the full suite. The existing prohibition holds absolutely: **no test may touch the real network.** **Primary seam — the HTTP router.** The overwhelming majority of coverage belongs at the existing root-package seam, where tests build the full router and drive it over an in-process HTTP server. This is where today's API and web-UI integration tests already live and it is the highest seam available. It should carry: the OAuth login flow end to end against a stubbed Discord, rejection of a non-guild-member, rejection of a non-owner during this step, session issue and revocation, per-Reader isolation (one Reader must not be able to read or write another's Bookmarks), the flat wire format on both `GET` and `PUT`, decomposition of a flat `PUT` across Series and Bookmark, refusal to let a client overwrite an existing Series' title or cover, acceptance of client-supplied metadata when the Series is new, per-Reader userscript token authentication, token rotation invalidating the old token, the rendered userscript carrying the requesting Reader's token, the grace-window token resolving to the owner, and the existing validation rules — empty key rejected, unknown status and kind rejected, `finished` refused over the API, empty status and kind meaning "keep what is stored". **Secondary seam — the store's public API.** Only for invariants not observable over HTTP: that the composite key makes a duplicate Bookmark for the same Reader and Series impossible, that the `updated_at` rule holds under `IS DISTINCT FROM` (a Progress change advances it; a Latest Chapter change or a favourite toggle does not), that the poll queue orders by Reader count then least-recently-checked, and that a Series with zero Bookmarks never appears in the queue at all while its row survives for re-bookmarking. These have direct prior art in the existing store tests. **Third seam — the poller's fetcher interface.** Already exists and already carries the no-network rule. Reused unchanged to prove one Series is fetched once no matter how many Readers hold it, that the row is stamped before the fetch so a broken series waits out its full cooldown, and that a failed fetch is logged and skipped. **Fourth seam — the Discord API base URL.** The one new seam, and it is configuration rather than an abstraction. A local stub stands in for Discord so the real OAuth client code — including the form-encoded token exchange — is exercised rather than bypassed. **Test infrastructure.** Each test package starts a real Postgres container once and gives every individual test its own database cloned from a template, preserving the isolation the current temp-directory approach provides. This makes Docker a hard prerequisite for the suite, which must be documented. Keeping SQLite for tests while shipping Postgres is explicitly rejected — it would mean the dialect rewrite is never actually tested. **Prior art to follow.** The root-package integration tests for router-level coverage and their server-construction helpers; the store tests for schema and ordering invariants, including the ones that seed a legacy schema before opening the store; the poller tests for the fake-fetcher pattern and frozen clock. **Migration verification.** Not a unit test — a checklist run against production at cutover, before the old volume is released: 29 Bookmarks total, split 18 reading and 11 archived, Series count equal to the number of distinct site and series-ID pairs, and every Bookmark pointing at the owner's Reader. ## Out of Scope - **Letting anyone other than the owner register.** That is Step 2 and is deliberately separated so this migration is proven while the owner is the only person who can be broken by it. - **Raising the poller's throughput.** Sweeping the projected Series count hourly would require roughly halving the stagger, doubling request rate against sites that already bot-score the single VPS IP. Prioritisation within the existing budget is in scope; raising the budget is not. - **Per-device userscript tokens.** One token per Reader. Per-device revocation is a real feature and not one anybody needs at this size; mark the deferral in code rather than building it. - **Per-Reader title or cover overrides.** Rejected — it would reintroduce the duplication the Series split removes. - **Email in any form.** No addresses stored, no messages sent, no verification, no password reset. Identity comes from Discord. - **Passwords.** None are stored. There is no local credential to reset. - **Continuous guild-membership enforcement.** Membership is checked at login. Someone who leaves keeps their session until it is deleted. - **Automated backups.** The runbook's manual procedure is updated for Postgres; automating it is separate work. - **Rewriting `PRODUCT.md`.** Its single-user claim becomes false, but it is corrected in Step 2 when the claim is actually false in practice. - **Any change to the userscripts' site adapters, retry queue, or panel UI.** The wire format is unchanged precisely so none of that has to move. ## Further Notes The concurrency question was settled by measurement rather than argument, and the numbers are worth keeping in view because they will otherwise be re-argued: against the real store at 1,500 rows, single-writer throughput was ~9,700 upserts/sec, plateauing at ~780/sec under 8 to 50 concurrent writers, with list queries holding 90–103/sec under continuous write load. Projected real load at 50 Readers is around 0.02 writes/sec. An apparent read collapse during benchmarking traced to row count and per-row scanning, not lock contention — the wrong diagnosis would have justified the right decision for a false reason. The real scaling limit is the poller's outbound fetch budget: at the configured batch and interval it checks 84 Series per hour, and no datastore choice moves that number. Deduplicating polls per Series is what addresses it, which is why ADR-0003 is a scaling decision as much as a modelling one. One fact was verified from Discord's documentation but **not** from a live flow: that membership in a single guild can be checked without the application being present in that guild as a bot. If that endpoint misbehaves when wired up, the fallback is the broader scope that returns the Reader's full guild list and scanning it locally — same enforcement, worse privacy, and it should be treated as a fallback rather than a first choice. The backup story genuinely regresses. Today disaster recovery is one self-contained file produced by a single command. Afterwards it is a dump, a role, and a restore procedure. This was accepted with open eyes; the runbook must be updated in this change rather than after it.
sulthan added the ready-for-agent label 2026-08-08 05:58:29 +07:00
sulthan commented 2026-08-08 20:25:16 +07:00
Author
Owner
Copy Link
Copy Source

Every child is closed: #20 (Postgres), #21 (Series/Bookmark split), #22 (Bookmark ownership), #23 (Discord login), #24 (per-Reader credential), #25 (import rehearsal), #26 (production cutover). Step 1 is done and has been running in production since the cutover. Closing.

Every child is closed: #20 (Postgres), #21 (Series/Bookmark split), #22 (Bookmark ownership), #23 (Discord login), #24 (per-Reader credential), #25 (import rehearsal), #26 (production cutover). Step 1 is done and has been running in production since the cutover. Closing.
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
bug
Something is broken
 enhancement
New feature or improvement
 needs-info
Waiting on reporter for more information
 needs-triage
Maintainer needs to evaluate
 ready-for-agent
Fully specified, ready for an AFK agent
 ready-for-human
Needs human implementation
 wayfinder:grilling
wayfinder:map
wayfinder:prototype
wayfinder:research
wontfix
Will not be actioned
No Label ready-for-agent
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
sulthan
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: sulthan/mangaBookmark#18
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?