Context

Operational data — tasks, cost records, messages, audit logs — needs durable storage that survives restarts. This is separate from agent memory (§7.1–7.4, handled by the MemoryBackend protocol / Mem0 — see ADR-001). Operational persistence follows the same pluggable protocol pattern used throughout the codebase: application code depends only on repository protocols, the storage engine is an implementation detail swappable via config.

App code → Repository protocols → SQLitePersistenceBackend (now)
                                → PostgresPersistenceBackend (future)
                                → MariaDBPersistenceBackend (future)

Adding a new backend requires implementing the protocols — no changes to consumers.

Acceptance Criteria

PersistenceBackend Protocol

• PersistenceBackend protocol with @runtime_checkable: connect(), disconnect(), health_check(), migrate(), is_connected property, backend_name property
• Follows the established protocol pattern (see CompletionProvider, MessageBus, ExecutionLoop, etc.)
• Backend selection driven by config (persistence.backend: "sqlite")

Repository Protocols

• TaskRepository protocol: save(task), get(task_id), list_tasks(*, status, assigned_to, project), delete(task_id)
• CostRecordRepository protocol: save(record), query(*, agent_id, task_id), aggregate(*, agent_id)
• MessageRepository protocol: save(message), get_history(channel, *, limit)
• AuditRepository protocol: save(entry), query(*, agent_id, action_type, time_range)
• All methods async, all accept/return existing frozen Pydantic models (Task, CostRecord, Message) — no ORM models or DTOs
• Repository protocols are @runtime_checkable

PersistenceConfig

• PersistenceConfig frozen Pydantic model with backend name, per-backend sub-configs
• SqliteConfig: path (database file), wal_mode (bool, default true), journal_size_limit
• Validated via _VALID_BACKENDS ClassVar pattern (matching TaskAssignmentConfig style)
• Integrated into RootConfig in config/schema.py

SQLite Backend Implementation

• SQLitePersistenceBackend implementing PersistenceBackend + all repository protocols
• Async database access using aiosqlite
• WAL mode enabled by default for concurrent read performance
• Database file path configurable (mounted Docker volume in production)
• Connection lifecycle: connect on startup, disconnect on shutdown, health_check for liveness

Migration Strategy

• Migrations run programmatically at startup via migrate()
• Initial migration creates all tables (tasks, cost_records, messages, audit_log)
• SQLite user_version pragma for version tracking
• Versioned migration scripts in persistence/migrations/

Error Hierarchy

• PersistenceError base, ConnectionError, MigrationError, RecordNotFoundError, DuplicateRecordError
• Follows existing error hierarchy patterns (see providers/errors.py, communication/errors.py)

Module Structure

persistence/
  __init__.py
  protocol.py         # PersistenceBackend protocol
  repositories.py     # Repository protocols (TaskRepository, CostRecordRepository, etc.)
  config.py           # PersistenceConfig model
  errors.py           # Persistence error hierarchy
  sqlite/
    __init__.py
    backend.py         # SQLitePersistenceBackend
    repositories.py    # SQLite repository implementations
    migrations.py      # Schema migrations

Testing

• Unit tests using in-memory SQLite (:memory:)
• Tests for all CRUD operations per repository (>80% coverage)
• Tests verify WAL mode is active (on-disk tests)
• Tests verify migration runs correctly
• Tests verify protocol compliance (isinstance checks with @runtime_checkable)
• Tests use test-provider, test-small-001 etc. (vendor-agnostic)

Dependencies

None — this is the foundational persistence layer.

Design Spec Reference

• §7.5 — Operational Data Persistence (NEW — PersistenceBackend protocol, repository protocols, config schema, migration strategy)
• §15.2 — Technology Stack (updated: pluggable backend, SQLite initial, PostgreSQL/MariaDB future)

Updated 2026-03-08: Scope expanded from "add SQLite" to pluggable PersistenceBackend protocol + SQLite as first backend. Same pattern as every other pluggable subsystem in the codebase. Added §7.5 to DESIGN_SPEC.