Local SQLite WAL Spool for Offline Progress

1. The real problem: progress must survive network loss

If a worker keeps progress only in memory, a crash can erase evidence. If it blindly retries everything after reconnection, it can create duplicates or reorder updates. This lesson produces Offline Progress WAL Spool & Replay Ledger v1: a single-node spool that makes local append, event identity, replay, remote acknowledgement, retry, and retention explicit. The goal is not magical exactly-once delivery; it is an auditable and recoverable path.

2. LOC-012 owns only the local spool boundary

LOC-012 owns the local SQLite spool plus replay and ACK reconciliation. Lease ownership and renew/cancel semantics belong to LOC-011; node action authorization belongs to LOC-013. WAL is not a distributed queue or network filesystem. When the coordinator is offline, a local commit proves only that the event was recorded under the local contract; it does not prove remote acceptance or job completion.

3. What WAL gives you—and what it does not

In WAL mode, a writer appends changes to a write-ahead log and checkpointing later transfers committed content into the main database file. Readers can coexist with a writer, but there is only one WAL writer at a time. Those mechanics are useful for a local workload, but SQLite does not design your queue schema, idempotency identity, replay order, acknowledgement, or retention policy.

4. WAL is a single-machine boundary

WAL relies on shared-memory coordination, so do not design it as a queue file on a network share. The -wal file is part of persistent database state and the database boundary must stay intact when copying or moving it. This lesson is intentionally single-node: each node owns its local spool, and the coordinator interacts only through the replay/ACK protocol.

5. Schema must separate event identity from row identity

Record at least event_id, job_id, lease_generation, event_sequence, payload_digest, spool_state, retry_count, next_retry_at, remote_ack, and core timestamps. event_id or an idempotency key must remain stable across retries. A local database row id is not enough for cross-node deduplication because semantic identity must be shared with the coordinator.

6. Append must be transactional

Create the event and its initial state in one local transaction: validate payload → BEGIN → insert PENDING event → persist digest/sequence/generation → COMMIT. Only after COMMIT may the event be considered durable-local. If SQLITE_BUSY or another failure happens before COMMIT, do not claim the event was committed; preserve BUSY/DEFER and apply bounded retry.

7. State machine: PENDING, SENT, ACKED, FAILED

PENDING means locally committed with no proven remote acceptance. SENT means at least one transmission attempt occurred with the same identity but no conclusive ACK is recorded. ACKED requires explicit coordinator evidence. FAILED is a policy-defined terminal state after exhaustion, not every transient error. Restart must not convert SENT-without-ACK into ACKED; it must recompute replay eligibility.

8. Replay must be deterministic and ordered

Within one logical stream, order replay by job_id + lease_generation + event_sequence and select only eligible unacknowledged events. A cursor is an optimization; row state and ACK evidence remain authoritative. If event 103 lost its response, retry the same event_id and payload digest. Creating a new event for the same logical progress bypasses deduplication.

9. Idempotency prevents duplicate logical progress

The coordinator may have accepted event 103 even though the response was lost. The node therefore retries 103, and the remote side must recognize the stable identity rather than apply a second logical update. The node records duplicate ACK reconciliation instead of creating a new sequence. “Send once” is not the contract; “retry with stable identity plus ACK reconciliation” is.

10. Remote ACK is the retirement boundary

A local COMMIT, a successful socket write, or an unbound transport success is not enough to retire an event. Retirement or compaction becomes eligible only after valid ACK evidence for the same event/sequence/generation and after the retention policy permits it. Keeping ACKED rows for a bounded retention window can preserve auditability. Unacknowledged rows are never deleted just to reduce storage pressure.

11. Retry and backoff must be bounded and auditable

Record retry_count, last_attempt_at, next_retry_at, and a reason for transient failure. Backoff must come from policy and observed device conditions rather than an unbounded loop. SQLITE_BUSY, network unavailable, and remote 5xx are different failure classes with different triggers and budgets. When a retry budget is exhausted, emit FAILED or DEFER with an explicit recheck/escalation path.

12. Checkpoint health is not spool health

Checkpointing transfers committed WAL frames into the main database, but a long-lived reader can prevent completion and allow the WAL to grow. Monitor WAL size, checkpoint result, and BUSY state. If checkpointing cannot complete, use monitoring/backoff and remove reader pressure; never manually delete the WAL file or drop PENDING/SENT rows to make the file smaller.

13. Crash recovery starts from database truth

Suppose event 104 was locally committed and the process crashed before upload. After reopen, 104 must be recovered from the committed row and become replay-eligible if no ACK exists. Events 101–102 already have ACK evidence and need not replay. Event 103 was accepted remotely but lost its response, so it is retried with the same identity. Recovery does not start from an in-memory cursor or a guess about the last send.

14. Decisive failure probes

Reject four shortcuts: (1) local COMMIT followed by crash before upload → the event must survive; (2) lost response after remote acceptance → retry with the same identity and reconcile the duplicate; (3) long reader blocks checkpoint → preserve DEGRADED/BUSY and never delete unacknowledged rows; (4) event=SENT with no ACK followed by restart → make it replay-eligible according to policy, not compaction-eligible. Learner materials do not expose hidden expected labels.

15. Transfer to a slower edge device

Move the same contract from a workstation SSD to a slower single-node edge device with a longer offline window. Identity, ordering, ACK, and retirement invariants stay fixed, but checkpoint cadence, retry backoff, and retention bounds must be recomputed from observed storage constraints. Do not copy baseline thresholds. The result may be DEGRADED or DEFER, but storage pressure never licenses deleting unacknowledged events or inventing ACK evidence.