offline: rewrite the durable queue on Lightweight instead of the hand-rolled sqlite3 C API

[Yaraslaut]

Summary

SqliteOfflineQueue is the only place in morph that talks to a database, and it does it by hand: raw sqlite3 C API, SQL as string literals, manual bind/step/finalize, a hand-written RAII statement guard, and ~112 lines of CMake working around FindSQLite3. Meanwhile the ladder examples (examples/common, examples/bank) already standardised on the LASTRADA Lightweight ORM for exactly this job — DataMapper, Field<>, LIGHTWEIGHT_SQL_MIGRATION. The library's own durable queue should use the same tool the framework recommends to its users.

What is hand-rolled today

include/morph/offline/sqlite_offline_queue.hpp (433 lines) contains:

• detail::kSqliteTransient — SQLITE_TRANSIENT re-expressed via reinterpret_cast so the macro's C-style cast doesn't trip -Wold-style-cast (sqlite_offline_queue.hpp:28-34).
• detail::StatementGuard — a bespoke sqlite3_finalize RAII wrapper (:36-56).
• Schema DDL as string literals: CREATE TABLE IF NOT EXISTS morph_offline_queue (...) plus a partial unique index ix_queue_idem (:129-141).
• Eight hand-written statements — INSERT ... ON CONFLICT DO NOTHING, SELECT id ..., SELECT id, payload, idempotency_key, attempts ... ORDER BY id, DELETE ... WHERE id = ?, UPDATE ... SET attempts = ?, UPDATE ... SET idempotency_key = ?1 ... WHERE NOT EXISTS (SELECT 1 ...), SELECT COUNT(*), PRAGMA journal_mode=WAL.
• Hand-written bindText / bindInt64 / stepOrThrow / textColumn marshalling, each with its own sqlite3_errmsg error path.

CMakeLists.txt:343-454 is the support cost of the raw dependency: a find_package(SQLite3 REQUIRED), a macOS-specific _morph_strip_catchall_includes hack that drops the SDK's bare /usr/include from the imported target (morph#172), and a manual add_library(SQLite3::SQLite3 UNKNOWN IMPORTED) fallback for when FindSQLite3 reports success but leaves no target behind.

Why Lightweight instead

• One persistence story. examples/common/testkit/db_fixture.hpp, the bank example, and every ladder rung use Lightweight's DataMapper + migrations. A user who reads morph's own durable queue currently finds an idiom the rest of the repo tells them not to write.
• The schema becomes a type. morph_offline_queue is a five-column table with one index — a textbook Field<> record. The DDL, the migration, and the CRUD collapse into a declaration plus DataMapper calls.
• Deletes the marshalling layer. bindText, bindInt64, stepOrThrow, textColumn, StatementGuard, kSqliteTransient and the reinterpret_cast all disappear.
• Backends beyond SQLite come for free. Lightweight speaks ODBC, so the same record works against PostgreSQL/SQL Server for hosts that want a shared server-side queue.

Scope

• Rewrite SqliteOfflineQueue (or add LightweightOfflineQueue and retire the raw one) over DataMapper.
• Port the semantics the spec pins, not just the SQL: durable attempts, insert-time dedup on a non-empty idempotencyKey with empty keys exempt, drain() never deleting, maxDepth enforcement, ids stable across reopen, and the mutex that lets the enqueue path and SyncWorker's replay path share one queue.
• Keep tests/offline_queue_conformance.hpp as the acceptance gate — it already declares per-implementation dedup policy, so a correct port passes the existing suite unchanged. tests/offline_sqlite/test_sqlite_offline_queue.cpp (501 lines) covers the backend-specific behaviour.
• Update docs/spec/offline/offline.md (the SqliteOfflineQueue section, and the dedup table at :154).
• Update consumers: examples/kanban/CMakeLists.txt:92,99, examples/crm/tests/test_offline_sync.cpp, examples/lims, examples/bank/tests/test_offline.cpp.
• Replace the MORPH_BUILD_OFFLINE_SQLITE CMake block with the Lightweight fetch that examples/common/CMakeLists.txt:119-153 already has working, and delete the FindSQLite3 workarounds.

Costs to settle before implementing

These are real and should be decided in the issue thread, not discovered mid-PR:

1. ODBC becomes a runtime requirement. Today the opt-in queue links libsqlite3 directly. Lightweight reaches SQLite through ODBC, so a host needs unixODBC plus a SQLite ODBC driver installed. That is a heavier ask for a library feature than it is for an example. Does MORPH_BUILD_OFFLINE_SQLITE stay opt-in with a renamed option, or does the durable-queue story move out of the core install set entirely?
2. Dependency weight in a core header. Lightweight resolves reflection-cpp and stdexec via CPM. examples/common/CMakeLists.txt handles this, but it has never applied to anything under include/morph/.
3. Warning cleanliness. Lightweight's headers are not -Werror clean; examples/common/CMakeLists.txt:194 and examples/bank/CMakeLists.txt both carry suppressions. A public morph header including <Lightweight/DataMapper/DataMapper.hpp> propagates that to every consumer's translation unit. This is the strongest argument for a .cpp-backed target rather than the header-only shape the queue has today.
4. drain() returns every pending row ordered by id. Worth confirming DataMapper's query surface expresses this cleanly for the record shape chosen — the repo has already been bitten by HasMany FK resolution and by fluent Query/Update not accepting HasMany-bearing records. The queue record has no relations, so this should be clear, but it needs a spike rather than an assumption.
5. WASM. examples/bank/CMakeLists.txt:20-30 skips the entire Lightweight stack under Emscripten because ODBC does not exist in a browser. Whatever shape this takes must keep FileOfflineQueue as the WASM-side durable option.

Suggested first step

A spike that implements the queue record + DataMapper CRUD behind the existing IOfflineQueue interface and runs tests/offline_queue_conformance.hpp against it. That answers (4) empirically and gives a concrete diff to weigh (1)-(3) against.

[Yaraslaut]

Sequencing note, from the backlog compression pass.

#549 (rewrite the durable queue on Lightweight) and #566 (a kanban board whose write threw was observed with the write applied, under 32-way SQLite contention) were considered for consolidation and deliberately kept separate.

The reason is that merging them would bury a live correctness bug behind a rewrite. #566 is a reproduced double-apply — the exact failure its test exists to catch — and it is confirmed against the CI log for a72787ed (run 35274333814), not just against the issue's quote.

But they are coupled, and the coupling runs one way:

1. offline: a kanban board whose write threw was observed with the write applied, under 32-way SQLite contention #566 must be root-caused before offline: rewrite the durable queue on Lightweight instead of the hand-rolled sqlite3 C API #549 starts. A rewrite onto DataMapper that does not know why the timeout-then-committed path double-applies will either reproduce the bug in new code or mask it behind different timing — and the second is worse, because the regression test would then pass for the wrong reason.
2. offline: rewrite the durable queue on Lightweight instead of the hand-rolled sqlite3 C API #549 must preserve offline: a kanban board whose write threw was observed with the write applied, under 32-way SQLite contention #566's fix and its regression test. Whatever guarantees the fix establishes about the timeout/commit boundary have to survive the change of database layer, and the contention test must still be able to observe a violation.

Neither should be dispatched to a lane that does not hold the other.