Storage backends: L1 shared memory + L2 SQL Anywhere

Askr already replaces Redis for a single box: an in-process, shared-memory cache (crates/askr/src/cache.rs), job queue (crates/askr/src/squeue.rs), response cache (rcache.rs) and SSE/Pusher broadcasting — no external broker.

This document describes how those primitives gain a durable, replicated, multi-box tier by layering over SQL Anywhere, so the "Redis-free" story holds across a fleet and survives restarts. It is the runtime half of epic elyra-2; the substrate half is the SQL Anywhere contracts, which are now conformance-tested (executable specs, not just prose — see sql-anywhere/sqlanywhere/tests/contract_conformance.rs), so these drivers build against a proven substrate:

• Queue → sql-anywhere/docs/contracts/QUEUE_CONTRACT.md (elyra-5) ✓ conformance-tested
• Cache → sql-anywhere/docs/contracts/CACHE_CONTRACT.md (elyra-7) ✓ conformance-tested
• Pub/sub → sql-anywhere/docs/contracts/PUBSUB_CONTRACT.md (elyra-6) ✓ conformance-tested

The two tiers

The API PHP sees (askr_cache_*, askr_queue_*, askr_broadcast()) and the Laravel drivers (elyra-11, elyra-12) are unchanged; only the backend differs. L1 and L2 mirror the same semantics on purpose — that is why the contracts are written to match squeue.rs / cache.rs.

One application per L2 database

With one difference that matters: L1 is separated per application; L2 is separated only for an application with an app_id. Shared memory keys every entry and job by the application it belongs to, named by its docroot. A docroot is a local path, which another host sharing the database need not have, so the SQL tables are keyed by the name PHP chose — unless the application has a name of its own ([server] app_id, [[site]] app_id). Then its cache keys and queue names carry that name, the same on every host, and its flush takes only its own rows.

Without one, two applications that both use the queue default, the cache key config or the channel orders are using the same rows:

• cache — one application reads another's cached values, and a flush in any of them empties the table for all;
• queue — a queue worker takes jobs another application pushed, and runs them in the wrong application;
• broadcast — an event reaches the other application's subscribers on that channel.

So give each application an app_id, or its own Askr instance and its own database files. Broadcasting is not separated either way — it is not namespaced in shared memory either, because one instance has one Pusher secret and so serves one application's realtime traffic — so two applications on one ASKR_BROADCAST_DB still share channels.

Askr warns at startup, and askr config-check says the same, when an L2 variable is set and the instance serves more than one application ([[site]], or a [queue] root / [scheduler] root that differs from [server] root) that it does not keep apart: the cache while any of them has no app_id (its flush empties every row), the queue while two have none, broadcasting always. It cannot see the other way to get there — two separate instances pointed at the same file — so give those an app_id each, or don't.

Adding an app_id to an application that already uses L2 moves its rows to a new name: its cached values start cold, and jobs already queued under the bare name are not seen by its workers — drain the queue before adding one.

Queue (elyra-9) — implemented

The durable-queue driver (crates/askr/src/squeue_sql.rs, feature sql-backend) claims against askr_jobs exactly as squeue.rs::pop reserves a slot: UPDATE … RETURNING with a subselect, a visibility timeout, attempts incremented at claim, ack = delete, release = re-arm with backoff. It implements the conformance-tested QUEUE_CONTRACT.md SQL verbatim and exposes the same push/pop/delete/release/size bridge as L1, so askr_queue_* and the Laravel driver are unchanged — only the backend differs.

Enable it (build + run). Easiest is the published -full image/tarball (features compiled in); otherwise build with --features sql-backend:

docker pull ghcr.io/kwhorne/askr:1.8-full               # or: cargo build --release -p askr --features sql-backend
ASKR_QUEUE_DB=/var/lib/askr/queue.db askr serve ...     # unset => L1 fallback

ASKR_QUEUE_DB points at an embedded SQL Anywhere file, an embedded replica, or a sqld-managed database. Each worker process opens its own WAL connection (safe multi-process access via SQLite file locking), so the pre-fork model needs no shared state; queue.rs dispatches L1 vs L2 once, at bridge registration.

Still open (follow-ups): connection pooling knobs, visibility renewal/heartbeat for long jobs, dead-letter move wired into the worker loop (attempts >= max_attempts), and the Laravel QUEUE_CONNECTION=sqlanywhere surface (elyra-12).

Autoscaling (elyra-8) — implemented

The backlog-driven autoscaler (--queue … --queue-max) now reads its backlog via queue::stats(), which dispatches to the L2 contract's FILTER backlog query (squeue_sql::stats()) when ASKR_QUEUE_DB is set, or the shared-memory ring otherwise. So the same balance=auto worker scaling and the askr_queue_ready/total/oldest_seconds metrics work against the durable L2 queue with no code change at the call sites — the master process reads the backlog from the database and forks/drains the worker pool exactly as before.

Cache (elyra-10) — implemented

The durable cache driver (crates/askr/src/cache_sql.rs, feature sql-backend) implements the conformance-tested CACHE_CONTRACT.md verbatim and exposes the same get/set/add/delete/increment/touch/flush/forget_tag bridge as L1, so askr_cache_*, the Laravel cache store and Cache::lock() are unchanged — only the backend differs. Locks use the contract's atomic add (SETNX with expired-lock steal); counters use the atomic increment (a counter stored as INTEGER still reads back as bytes, so Cache::get after increment behaves as PHP expects); tag invalidation removes every key carrying the tag.

Enable it with ASKR_CACHE_DB=/path/to.db (unset => L1 fallback), building with --features sql-backend. Each worker process opens its own WAL connection.

Write-through L1→L2 (implemented): when the L1 shared-memory cache is also enabled (--cache-slots N), it becomes a fast local read tier in front of the durable L2. Reads hit L1 first and lazily populate it (with the remaining TTL) on a miss; writes go to L2 (the source of truth) and warm or invalidate L1. Because L1 is shared memory, every worker process on a box sees writes immediately — coherent within a box. Cross-box staleness is bounded by the entry TTL; instant cross-node tag invalidation over the pub/sub topic is a follow-up.

Broadcasting / pub-sub (elyra-13) — implemented

The durable pub/sub driver (crates/askr/src/broadcast_sql.rs, feature sql-backend) implements PUBSUB_CONTRACT.md: publish = INSERT into the append-only askr_events topic, subscribe = tail rows past a cursor. It exposes the same publish/current_seq/read_from surface and askr_broadcast() bridge as the L1 ring, so the SSE fan-out task and the Pusher endpoint are unchanged — only the backend differs. Askr tails its local copy of askr_events and fans out to connected SSE / Pusher-compatible WebSocket clients on that node, so a publish on the primary reaches Echo clients on any node via the replication log — no Redis pub/sub.

Enable it with ASKR_BROADCAST_DB=/path/to.db (unset => L1 ring), building with --features sql-backend.

The tail query is prepare_cached (compiled once per connection) and returns a cheap indexed 0-row scan when nothing is new, so the 50 ms poll is inexpensive. Note a true SQLite update_hook wakeup only fires for writes on the same connection, so it cannot wake the reader on a cross-process publish — polling the local copy (woken naturally by replication apply on a replica) is the correct transport. Durable subscriber cursors (askr_subscribers) for resume-after-restart remain a follow-up.

Status

Contracts (elyra-5/6/7) are stable and conformance-tested on the substrate side (sql-anywhere/sqlanywhere/tests/contract_conformance.rs). All three L2 drivers are implemented behind the sql-backend feature — queue (elyra-9, squeue_sql.rs), cache (elyra-10, cache_sql.rs) and broadcast (elyra-13, broadcast_sql.rs) — plus backlog-driven autoscaling on L2 (elyra-8, via queue::stats). The Laravel surface (elyra-11/12/13) ships in packages/laravel: session, cache, queue and now broadcasting (BROADCAST_CONNECTION=askr) drivers, transparent across L1/L2. Epic elyra-2 is functionally complete; remaining work is optimization (update-hook wakeups, write-through L1→L2 cache) and hardening.