ADR-0009: SQLite State Layer for Core Data Model¶

Status: Accepted Date: 2026-05-20

Related update: This ADR establishes SQLite as the current persistent state layer. ADR-0033 introduces NormalizedState (lifecycle × health × delivery) as the contract for entity state; the SQLite schema here is the current materialization of that contract. ADR-0034 §"Backend-frontend sync contract" specifies how this layer's reads flow to the frontend. The schema extensions for the new dimensions are in ADR-0033 Appendix A. SQLite-specific details remain authoritative; future stores (Postgres, distributed) will materialize the same NormalizedState model differently.

Context¶

Lion Studio's filesystem-only backend (ADR-0004) works for post-hoc review but cannot support live monitoring. A li agent run doesn't appear in the dashboard until after completion because run.json is written at the end. Polling the filesystem every 5 seconds gives no task/agent context for in-progress runs.

Separately, lionagi's Session / Branch / Message / Progression data model needs a persistent representation that mirrors the runtime exactly, to support:

1. Instantaneous monitoring via hooks + SSE push (not filesystem polling).
2. Cross-session message exchange (replacing li team's markdown-file coordination with structured inbox/outbox).
3. Branch forking and message sharing without content duplication.
4. Vector similarity search on message embeddings.

aiosqlite is a mandatory dependency (lightweight, ~15KB).

Decision¶

Introduce ~/.lionagi/state.db (SQLite, WAL mode) with four core tables that map 1:1 to lionagi's runtime data model. The schema lives at lionagi/state/schema.sql.

Data model — four tables¶

messages — Atomic content. Independent entities referenced by progressions, not owned by a branch or session. Fields match Message.model_dump():

message_types — lookup table mapping integer to full Python class path:

Type ID 0 sentinel: the __unknown__ row is a typed sentinel returned by StateDB._resolve_lion_class("") when an incoming message dict carries no lion_class string in its node_metadata. This keeps insert_message() total (it never raises on missing class info) at the cost of an opaque row that downstream readers cannot map back to a runtime class. New type strings continue to be allocated their own auto-incremented IDs on first insert.

progressions — Progression[Message]. An ordered sequence of message IDs stored as a JSON array in collection. Both sessions and branches own a progression.

sessions — The scope boundary. A session owns a progression (the session-level message pool) and zero or more branches. Maps 1:1 to lionagi.session.Session.

Provenance columns: playbook_name, agent_name, invocation_kind, show_topic, show_play_name, artifacts_path, and source_kind are first-class columns on sessions. See ADR-0012 for the enrichment decision. These columns are nullable lightweight hints for display and filtering — they are not authoritative execution state. invocation_kind and source_kind carry schema-level CHECK constraints mirrored by Python validators in db.py (closed vocabularies, ADR-0012).

Session is the substrate, not the invocation. Heavy run-level concerns (full manifest, cwd, provider, model, play-level lifecycle) are NOT on the session table. Minimal lifecycle columns (status, started_at, ended_at) are first-class on sessions for dashboard and runs-list queries (see ADR-0017). However, minimal provenance columns (playbook_name, invocation_kind, show_topic, show_play_name, agent_name, artifacts_path, source_kind) are also on sessions for execution lineage queries (see ADR-0012). These are lightweight hints for display and filtering, not authoritative execution state.

branches — A progression with identity. A branch IS a progression (an ordered cursor over the session's messages) with attached agent configuration. Branch config (provider, model, system_prompt, tools, effort) lives in node_metadata JSON.

Fork columns: parent_branch_id (TEXT) and forked_at_ord (INTEGER) are stored in node_metadata JSON on the branch row, not as top-level columns. A dedicated branch fork protocol may promote these to first-class columns in a future migration if query patterns require it.

system_msg_id is first-class. Unlike fork pointers, the system prompt reference is promoted to a top-level column because (a) every live persist path writes it during branch initialization, (b) the Studio inbox/branch view needs a constant-time lookup of the system message without parsing node_metadata, and © sessions that resume should be able to detect "system message already persisted" without round-tripping the JSON blob. The actual message body remains in messages; this column is just an O(1) pointer.

Key design decisions¶

1. Fork model: tree pointers, no message duplication. A fork records parent_branch_id + forked_at_ord on the new branch. Full history is reconstructed by walking up the ancestry tree. Fork cost is one INSERT. Messages are never copied.

2. No join table for branch→message ordering. The progression's collection JSON array holds the ordered list of message IDs. At our scale (< 500 messages per branch), JSON append is fast and avoids an extra table. The trade-off: no SQL-level reverse index ("which progressions contain message X?"). If needed later, a join table can be derived from the JSON arrays.

3. node_metadata instead of metadata. The runtime field is metadata but METADATA is reserved in some SQL contexts. Element.to_dict(mode="db") renames it to node_metadata automatically (commit 7ac31109).

4. lion_class as integer enum. Full class paths like lionagi.protocols.messages.instruction.Instruction are ~60 bytes per row. The message_types lookup table maps them to 4-byte integers. New message types get the next ID on INSERT.

5. Embedding as BLOB, not TEXT. Packed float32 vectors are compact and ready for sqlite-vec virtual table indexing. The vec extension is optional — the core schema is plain SQLite.

6. Agent and playbook definitions stay as files. ~/.lionagi/agents/*.md and ~/.lionagi/playbooks/*.yaml remain the source of truth. They are the contract with external tools (Claude Code, codex CLI, vim, grep, git, symlinks). ADR-0004 remains valid for these.

Supporting code changes (same session)¶

Branch.to_dict() streamlined (commits 7ac31109, a36b5542):

• Added mode parameter: "python" (default), "json", "db".
• mode="db" renames metadata → node_metadata.
• Optional flags: include_logs, include_log_config, include_processor_config, include_request_options.
• Metadata serializer handles clone_from branch references.
• parse_model only included when it differs from chat_model.

create_message() extracted (commit 8f6c6d94):

• MessageManager.create_message() is now a @staticmethod — can be used without a MessageManager instance.
• Standalone create_message() function exported from lionagi.protocols.messages and lionagi.__init__.
• add_message() delegates to create_message() then handles progression insertion and system message replacement.

iModel.to_dict() slimmed (commits 7ac31109, a36b5542):

• request_options excluded by default (bulk, not useful for persistence).
• processor_config excluded by default.

Operational commands (li state)¶

The state.db is a long-lived file that grows monotonically without intervention. To keep the file manageable and to give operators a single introspection surface, the CLI ships maintenance subcommands under li state:

The DB layer sets PRAGMA wal_autocheckpoint = 1000 explicitly so the policy is visible (matches the SQLite default of 1000 frames). Operators who hit unbounded WAL growth (long-lived readers, never-closed connections on legacy versions) can drop the autocheckpoint setting or run li state checkpoint --mode TRUNCATE manually.

Consequences¶

Positive

• Session/branch/message model has a persistent representation that mirrors the runtime 1:1. Round-trip to_dict(mode="db") → INSERT → SELECT → from_dict() is lossless.
• Foundation for live monitoring (hooks → DB INSERT → SSE push) without filesystem polling.
• Foundation for cross-session message exchange (structured inbox/outbox via recipient/sender fields + progression cursors).
• Branch forking is O(1) with zero message duplication.
• sqlite-vec ready for semantic search on message embeddings.
• Agent/playbook file-based contract preserved.

Negative

• aiosqlite is now a mandatory dependency (promoted from lionagi[sqlite] optional).
• JSON array for progression ordering limits query-side operations (no WHERE message_id IN progression without JSON parsing).
• Schema migrations will be needed as the model evolves. Migration protocol: this PR ships v1 as the collapsed initial release schema (no v1→v2 migration runner yet). Forward-only column reconciliation is handled in StateDB._reconcile_columns() — it PRAGMA table_infos every table named in _MIGRATION_COLUMNS and ALTER TABLE ... ADD COLUMNs anything that exists in the current schema but not on disk. This safely upgrades pre-release state.db files that were written before late provenance / lifecycle columns landed without requiring a numbered migration step. When the schema needs a true v2 (column removal, type change, table split), the numbered _migrate() runner described above will be added and schema_meta will bump.

Alternatives Considered¶

References¶

• lionagi/state/schema.sql — the schema
• lionagi/protocols/generic/element.py — to_dict(mode="db") rename
• lionagi/session/branch.py — to_dict() streamlined serialization
• lionagi/protocols/messages/manager.py — create_message() extraction
• ADR-0004 — predecessor (still valid for agent/playbook files)