Migrate store: atomic-JSON → SQLite (modernc, cgo-free); backup deferred to go-live #28

New issue

Closed

opened 2026-08-25 01:23:39 +00:00 by Cordy · 0 comments

Cordy commented

2026-08-25 01:23:39 +00:00

Owner

Decision record — 2026-08-25. Supersedes the "SQLite swap" open item on the architecture wiki. No code yet; this is the agreed plan.

Decision

Replace the single atomic-JSON store with SQLite via modernc.org/sqlite (pure-Go, cgo-free). Backup/replication is explicitly deferred to go-live — see below.

Why SQLite

Transactional writes — a bad write corrupts one row, not the whole store. Today one bad save() clobbers everything and the only floor is a nightly copy.
Real schema migrations instead of hand-rolled JSON-blob reshaping.
SQL for the reporting/admin queries that will come.
modernc.org/sqlite is pure Go → keeps CGO_ENABLED=0 and the cross-compile in deploy.yml. ~2× slower than the cgo mattn driver, irrelevant at this size (~0.5 KB/record, single-digit MB, a few hundred writes/day, reads from memory).
It's the substrate Litestream needs later.

Alternatives considered

bbolt (etcd-io, pure Go, buckets map 1:1 to the current four maps) — smallest migration, but solves the wrong half: transactional writes we barely need, still no streaming backup, still no SQL, still hand-rolled migrations. Runner-up only.
Keep atomic-JSON, harden — zero migration risk, but backup granularity stays at hours, no PITR, every schema change stays ad-hoc; we'd migrate eventually anyway, under worse conditions.
Badger / Pebble — LSM engines for write-heavy workloads thousands of times ours; pure operational overhead here.
PostgreSQL — a second server to run, patch and back up on the VPS for a ~5 MB single-writer dataset; only pays off if the license server ever goes HA/multi-region, which contradicts the single-binary design.

Backup — DEFERRED TO GO-LIVE (cost decision)

No object-storage spend while the store is sandbox-only; backup lights up when real customer data exists. At launch:

Target: Infomaniak Object Storage (S3, s3.pub1.infomaniak.cloud) or Swiss Backup (S3/SFTP). NOT Garage — the license server stays off the homelab WireGuard mesh by design: a public VPS must not tunnel into the home network. Same provider as the VPS, Swiss, reachable over public TLS, no mesh.
Interim at launch: nightly VACUUM INTO a timestamped snapshot → Infomaniak via aws CLI (~10-line cron). Closes the long-standing "off-box shipping still open" item.
Then: Litestream → the same bucket for ~1 s granularity + point-in-time restore. Zero code change to switch.

Migration plan

Schema: licenses, accounts, oauth_identities, orgs, org_members, invites. Sessions, reset tokens and OAuth state stay in-memory (unchanged — a restart re-prompts login, by design).
Driver: modernc.org/sqlite, database/sql. WAL mode. Keep the in-memory read model or query directly (TBD; reads are trivial either way).
Boot import: if the DB is empty and licenses.json is present, import it once, then move the JSON aside (licenses.json.migrated). Idempotent, safe to re-run.
Retention unchanged: records kept forever (load-bearing for late-renewal same-id re-sync). ~5 MB at 10 k licenses.
Ship via the usual branch → one-shot CI (vet + full suite) → PR for review → deploy. Do it pre-launch so the migration is exercised on sandbox data, not under launch pressure with live customer records.

Sequencing

SQLite swap can land any time pre-launch (recommended: soon, on sandbox data). The backup layer is a go-live task (#26-launch), tracked separately once it lands.

Decision record — 2026-08-25. Supersedes the "SQLite swap" open item on the architecture wiki. No code yet; this is the agreed plan. ## Decision Replace the single atomic-JSON store with **SQLite via `modernc.org/sqlite`** (pure-Go, cgo-free). Backup/replication is explicitly **deferred to go-live** — see below. ## Why SQLite - **Transactional writes** — a bad write corrupts one row, not the whole store. Today one bad `save()` clobbers everything and the only floor is a nightly copy. - **Real schema migrations** instead of hand-rolled JSON-blob reshaping. - **SQL** for the reporting/admin queries that will come. - `modernc.org/sqlite` is **pure Go** → keeps `CGO_ENABLED=0` and the cross-compile in `deploy.yml`. ~2× slower than the cgo `mattn` driver, irrelevant at this size (~0.5 KB/record, single-digit MB, a few hundred writes/day, reads from memory). - It's the substrate **Litestream** needs later. ## Alternatives considered - **bbolt** (etcd-io, pure Go, buckets map 1:1 to the current four maps) — smallest migration, but solves the wrong half: transactional writes we barely need, still no streaming backup, still no SQL, still hand-rolled migrations. Runner-up only. - **Keep atomic-JSON, harden** — zero migration risk, but backup granularity stays at hours, no PITR, every schema change stays ad-hoc; we'd migrate eventually anyway, under worse conditions. - **Badger / Pebble** — LSM engines for write-heavy workloads thousands of times ours; pure operational overhead here. - **PostgreSQL** — a second server to run, patch and back up on the VPS for a ~5 MB single-writer dataset; only pays off if the license server ever goes HA/multi-region, which contradicts the single-binary design. ## Backup — DEFERRED TO GO-LIVE (cost decision) No object-storage spend while the store is sandbox-only; backup lights up when real customer data exists. At launch: - **Target: Infomaniak Object Storage** (S3, `s3.pub1.infomaniak.cloud`) or **Swiss Backup** (S3/SFTP). **NOT Garage** — the license server stays **off the homelab WireGuard mesh** by design: a public VPS must not tunnel into the home network. Same provider as the VPS, Swiss, reachable over public TLS, no mesh. - **Interim at launch:** nightly `VACUUM INTO` a timestamped snapshot → Infomaniak via `aws` CLI (~10-line cron). Closes the long-standing "off-box shipping still open" item. - **Then:** Litestream → the same bucket for ~1 s granularity + point-in-time restore. Zero code change to switch. ## Migration plan 1. **Schema:** `licenses`, `accounts`, `oauth_identities`, `orgs`, `org_members`, `invites`. Sessions, reset tokens and OAuth state stay **in-memory** (unchanged — a restart re-prompts login, by design). 2. **Driver:** `modernc.org/sqlite`, `database/sql`. WAL mode. Keep the in-memory read model or query directly (TBD; reads are trivial either way). 3. **Boot import:** if the DB is empty and `licenses.json` is present, import it once, then move the JSON aside (`licenses.json.migrated`). Idempotent, safe to re-run. 4. **Retention unchanged:** records kept **forever** (load-bearing for late-renewal same-id re-sync). ~5 MB at 10 k licenses. 5. **Ship** via the usual branch → one-shot CI (`vet` + full suite) → PR for review → deploy. **Do it pre-launch** so the migration is exercised on sandbox data, not under launch pressure with live customer records. ## Sequencing SQLite swap can land any time pre-launch (recommended: soon, on sandbox data). The backup layer is a **go-live task** (#26-launch), tracked separately once it lands.