docs/sync-and-op-log/sqlite-migration-followup.md
Companion to sqlite-migration.md. That doc holds the
architecture and the per-phase design; this one is the actionable backlog
of what remains after the work on branch claude/issue-7892-root-cause-KY1ED,
ordered so each item is independently shippable and reviewable.
OpLogDbAdapter / OpLogTx port + declarative OP_LOG_DB_SCHEMA.IndexedDbOpLogAdapter (faithful idb backend) — the live backend.OperationLogStoreService + ArchiveStoreService fully routed through the
port (no direct this.db), behind a DI factory token
(OP_LOG_DB_ADAPTER_FACTORY), IndexedDB-backed on every platform today.SqliteOpLogAdapter fully implemented against a minimal SqliteDb port.
Not wired to any platform; no native plugin dependency.sql.js is served into
Karma (dev-only; never in the app bundle) and the adapter's behavioral
contract runs against both the in-memory fake and real SQLite
(sqlite-op-log-adapter.spec.ts), plus a store-level second pass through
OperationLogStoreService (remote-apply-store-port.integration.spec.ts).
Confirms the real UNIQUE constraint failed → ConstraintError mapping,
AUTOINCREMENT-after-clear(), compound-index + NULL ranges, and real
BEGIN IMMEDIATE rollback. (B2 translation-layer + store-port passes; the
on-device real-engine run still remains.)op-log-backend-migration.ts):
whole-DB copy from any source adapter to any dest adapter in one dest
transaction with verify-before-commit (op count + last seq + vector clock;
mismatch → rollback). Validated real-Chrome-IDB → sql.js. Not wired into
startup — B3/C2 decide when to run it.OperationLogStoreService.init()
/ ArchiveStoreService._init() now call adapter.init() and skip the IDB open
for self-managing backends (no adoptConnection); the IndexedDB path is
unchanged. Dead in production until the native token flip. Only the device-gated
token override + native SqliteDb wrapper remain.LocalBackupService writes a
JSON snapshot every 5 min on Android (KeyValStore rows backup /
backup_prev) and iOS (Directory.Data super-productivity-backup.json /
.prev.json), with an empty-state write guard and a two-generation ring so
one bad/evicted write cycle can't erase the only good copy. Fresh-launch
restore prompt is informed (summarizeBackupStr shows task / project
counts). Electron continues to use its own rotated backup folder.Nothing in the SQLite tracks below changes runtime behavior for existing users until step B3 flips a platform to the SQLite backend. The #7924 local-backup work is already live on Android/iOS.
These directly reduce the data-loss blast radius and do not depend on the SQLite work. Highest user value per unit effort; do these first.
navigator.storage.persist() observable on nativestartup.service._requestPersistence() suppresses the not-granted branch on
native and logs nothing when persist() resolves false. On Android WebView
the grant is often not honored, so today a report like #7892 carries no signal.
Log.log({ persisted, granted }) on every branch (incl. native), so
exported logs always carry the durability state of the WebView store.
Optionally surface in About diagnostics as a follow-up.✅ Shipped in #7925: LocalBackupService._triggerBackupSave$ merges a
LOCAL_ACTIONS-driven trigger with the existing 5-min interval — any local
action settles into a backup after a 30s quiet period. LOCAL_ACTIONS
already filters out remote/hydration replays, and the existing empty-state
guard in _backup() prevents writing a degraded post-eviction snapshot
over a good backup, so the trigger strictly adds frequency without spam.
✅ Shipped in #7925: LocalBackupService._backupAndroid() and _backupIOS()
each read the existing primary slot before promoting/overwriting, and bail
when a near-empty snapshot (< 3 tasks) would clobber a substantial existing
backup (≥ 10 tasks). Counts include active + young-archived + old-archived
tasks via the shared countAllTasks helper, so the threshold is the same
on the read side (summarizeBackupStr) and the write side. Electron is
unchanged — its rotated, timestamped backup chain isn't a single-slot
overwrite. Fail-safe: skipping never loses data; the guard self-clears
once the store grows back past 3 tasks, so a legitimate bulk-delete is
captured on the next tick.
A1, A2, and A3 have shipped — Track A is complete. SQLite (Track B) is the durable architectural fix and is tracked in #7931.
@capacitor-community/sqlite + a SqliteDb wrapperSQLiteDBConnection to the SqliteDb port
(run/query). Open one DB named SUP_OPS in Directory.Data.IS_NATIVE_PLATFORM; web/PWA/Electron stay on IndexedDB.getAll/count) are already one query = one crossing.
Single-op append is negligible. The one cliff is bulk write:
OperationLogStoreService.appendBatch() loops await tx.add() once per op, so
N ops = N crossings. Mitigations (only matter on the bridge; can't be measured
with in-process sql.js, so validate on-device):
lastId from the plugin's own run response (it provides it) —
never issue a separate SELECT last_insert_rowid(), which would double
every insert to two crossings.runBatch(statements) on
SqliteDb + an addBatch on OpLogTx) so appendBatch collapses to one
crossing via the plugin's executeSet. Per-op seq recovery from a batched
insert needs RETURNING seq (SQLite ≥ 3.35) or last_insert_rowid()
arithmetic over the consecutive AUTOINCREMENT range — pick after confirming
the plugin's SQLite version on-device.SqliteOpLogAdapter against a real engine — ✅ done (CI), on-device remainssql.js is a devDependency, served into Karma as a
global script + a proxied .wasm (src/karma.conf.js), so the webpack node:
import problem is sidestepped (loaded as a script, not bundled). A ~25-line
SqlJsDb wrapper (sql-js-db.test-helper.ts) satisfies the SqliteDb port.sqlite-op-log-adapter.spec.ts runs the
behavioral contract against both the fake and real sql.js; SQL-emission specs
stay fake-only. Confirmed the real UNIQUE constraint failed → ConstraintError
mapping, AUTOINCREMENT-after-clear(), compound-index + NULL ranges, real
BEGIN IMMEDIATE rollback. No surprises surfaced.remote-apply-store-port.integration.spec.ts
runs the store's composed flows (apply/mark/merge-clock, partial failures,
full-state clearing) through OperationLogStoreService on both backends.operation-log-stress.benchmark.ts harness is the lever for the on-device
perf + behavior pass (see B1 perf note).OperationLogStoreService.init() and ArchiveStoreService._init() were
IDB-shaped — they unconditionally opened+adopted a WebView IndexedDB connection
and never called adapter.init(), so on SQLite the tables were never created
and the evictable store was still touched. Now: when the adapter exposes no
adoptConnection (self-managing, e.g. SQLite), init() calls
await this._adapter.init() and skips the IDB open; the IndexedDB path is
unchanged. Two unit tests cover both branches; the store-port integration spec
now drives the store fully on SQLite (no pre-init workaround). The new branch is
dead in production until the token flip below, so it shipped risk-free.OP_LOG_DB_ADAPTER_FACTORY to return
SqliteOpLogAdapter when IS_NATIVE_PLATFORM, behind a feature flag defaulting
off. The factory must hand both services' adapters the same
SqliteDb (one SQLite file, all tables) — mirroring how they share one IDB
connection today. Needs B1 (the native SqliteDb wrapper) first.migrateOpLogBackend(source, dest) in
op-log-backend-migration.ts copies all stores in one dest transaction
with verify-before-commit (op count + last seq + vector clock; mismatch →
rollback, source untouched). Generic iterate→put: preserves ops seq
(incl. gaps) via put-honors-seq, writes singletons at their out-of-line key,
no per-store special-casing. Adapter-agnostic, so validated real-Chrome-IDB →
sql.js in CI; the native plugin dest behaves identically through the port.SUP_OPS present" on first launch and call migrateOpLogBackend. Set a
migration-complete marker. Keep the IDB copy untouched for ≥1 release as a
fallback. Note: the copy uses the adapter port's read side, independent of the
store-init IDB-open fix in B3.Beta/dogfood on real Android devices → staged enable → remove the IDB fallback
and the adoptConnection bridge once SQLite is the sole native backend.
adoptConnection bridge from the port and the
two services once no backend relies on a borrowed connection.OP_LOG_DB_SCHEMA so
runDbUpgrade only carries deltas (the schema spec already guards against
drift; this removes the remaining hand-maintained duplication).SUPThemes, sup-sync, sup-plugin-oauth) only if fully
evacuating WebView storage is desired.Tracks A and B/C/D are independent — A shipped while B/C/D moves at its own device-gated cadence.
These don't belong to a single track but were surfaced by the #7924 review and should land alongside the next time the area is touched.
JavaScriptInterface.kt JS-literal injection (Android bridge). The
loadFromDbCallback(...) call is built by raw single-quote interpolation of
the stored value into evaluateJavascript. Beyond the security smell, it is
a real functional bug: JSON.stringify does not escape ', so a backup
blob containing an apostrophe terminates the JS string literal and
load-from-DB returns garbage. Fix is to use JSONObject.quote() for the
arguments (the same primitive already used by
emitForegroundServiceStartFailed).Filesystem.stat.mtime for free; Android needs a
bridge change to surface the (now-real) KEY_CREATED_AT —
loadFromDbWrapped(key) returns only the value, so add a meta-aware reader
(e.g. loadFromDbWithMeta → { value, createdAt }). This gives
KEY_CREATED_AT its first reader; the column is behaviorally inert today._initBackups() only offers restore when there is no stateCache at all.
Extend the trigger to also fire when the loaded state is degraded per
hasMeaningfulStateData. Needs a decision on auto-restore vs prompt and a
guard against resurrecting an intentional wipe (the informed-restore prompt
shipped in #7924 already lets the user decline knowingly).