Add Persistence Backend (SQLite) for Long-Term Record History

[lxsaah]

Add an optional persistence layer to AimDB that allows records to survive process restarts and supports historical queries. Persistence is implemented as a buffer subscriber — consistent with the existing producer–consumer architecture — rather than a change to the core write path.

Motivation

In-memory ring buffers are lost on restart. This is a problem for low-frequency records (e.g. forecast validations arriving ~1/hour per city): after a restart or page load, consumers see no data until the next write cycle. The record.drain API (doc-019) cannot solve this.

Concrete impact: The AccuracyPanel in the weather demo is empty on page load, after restarts, and for new visitors.

Proposed Solution

Introduce two new crates:

• aimdb-persistence — defines the PersistenceBackend trait and the .persist() extension on RecordRegistrar
• aimdb-persistence-sqlite — concrete SQLite implementation using a dedicated writer thread + channel-based actor pattern (no Mutex, no spawn_blocking)

Key Design Decisions

• .persist() is a subscriber, implemented via tap_raw() — no changes to the core write path
• aimdb-core stays blind to PersistenceBackend; the backend is stored as Arc<dyn Any> and downcast by aimdb-persistence
• T: Serialize required, with_remote_access() is NOT required
• SQLite actor pattern: Connection owned by a dedicated OS thread; async callers communicate via mpsc + oneshot channels
• Retention is managed by a periodic cleanup task registered during with_persistence()
• Uses runtime.spawn() via tap_raw — no tokio::spawn hardcoding

API Overview

Configuration

let backend = Arc::new(SqliteBackend::new("./data/validations.db")?);

let mut builder = AimDbBuilder::new()
    .runtime(TokioAdapter::new())
    .with_persistence(backend, Duration::from_secs(7 * 24 * 3600));

builder.configure::<ForecastValidation>(accuracy_key, |reg| {
    reg.buffer(BufferCfg::SpmcRing { capacity: 500 })
        .tap(...)
        .persist(accuracy_key.to_string())
        .transform::<Temperature, _>(...);
});

Querying

// Latest value per matching record
let by_city: Vec<ForecastValidation> = db.query_latest("accuracy::*", 1).await?;

// Last N values for one record
let history: Vec<ForecastValidation> = db.query_latest("accuracy::vienna", 10).await?;

// Time range
let range: Vec<ForecastValidation> = db.query_range("accuracy::vienna", start_ts, end_ts).await?;

AimX Protocol Extension

Adds record.query method to the AimX protocol for remote clients, supporting wildcard patterns, per-record limits, and time range filters.

Schema

CREATE TABLE record_history (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    record_name TEXT    NOT NULL,
    value_json  TEXT    NOT NULL,
    stored_at   INTEGER NOT NULL
);
CREATE INDEX idx_record_time ON record_history(record_name, stored_at DESC);

Implementation Plan

Phase	Tasks	Est.
1	aimdb-persistence crate: trait + .persist() extension	3 days
2	aimdb-persistence-sqlite: SQLite actor backend	2 days
3	query_latest / query_range on AimDb<R>, builder hooks	2 days
4	record.query in AimX protocol handler	1 day
5	Integrate into weather-hub-streaming, seed AccuracyPanel from history	1 day