Persistent Storage and Workspace Memory Layer

Status: Open — design proposal (Phase 3, Agent Orchestration Program)

Partial implementation note: the Capsule usage overlay now creates a Capsule-local Turso store at /jackin/state/usage/telemetry.db with the account_usage_snapshots schema subset. That store persists normalized account quota buckets for the in-container Capsule daemon only, and the Capsule control channel can read focused/account quota rows from it. The removed local-log usage samples, runtime-stream samples, scan offsets, workspace summaries, and session summaries are deliberately not part of the shipped overlay. The host jackin usage <instance> accounts --sync-host-cache command can explicitly seed the Turso host-global account cache at ~/.jackin/data/daemon/accounts.db from those daemon-cached rows. The full per-instance ~/.jackin/data/<container>/jackin.db, automatic host-global account refresh daemon, migrations, memory tables, provider-specific runtime pricing, and richer runtime event contracts described below remain open.

Problem

jackin❯ currently has zero structured persistence. Per-instance state is filesystem-shaped (~/.jackin/data/<container>/.claude/, .config/gh/, .jackin/isolation.json), and the operator config is a single TOML file. There's no way to store:

• A history of agent status transitions ("when did this agent last go busy?")
• Cached GitHub link state (refresh windows, last-fetched, last-error)
• Token / cost samples over time
• Provider account quota snapshots and stale/error metadata
• Tool fire history (audit log)
• The autonomous queue's pending and completed tasks
• Cross-agent workspace memory: durable operator preferences, workflow rules, project facts, review feedback, and run handoff summaries that Claude Code, Codex, Kimi, OpenCode, and future runtimes can all read through one surface owned by jackin❯

Every Phase 2 and Phase 4 item that needs any of the above either does its own ad-hoc JSON file (which gets messy) or skips the feature.

This is the load-bearing Phase 3 item — the substrate for half of the program. It is also the right home for memory owned by jackin❯. Runtime-native memories are useful inside one agent, but they become the wrong abstraction when the operator mixes Claude Code, Codex, Kimi, OpenCode, and future agents inside the same workspace and expects each new session to inherit the same review policy, project facts, and workflow history.

Why It Matters

• Designing one storage layer once is dramatically cheaper than five ad-hoc state files. The schema is small (a handful of tables); the operational story is simple (one DB per instance, alongside existing state).
• Several leaves explicitly reference this item: GitHub link tracking, Token & Cost Telemetry, autonomous task queue, agent runtime status (status_log table), completed-instance retention metadata for queued work, and agent workflow orchestration run handoffs.
• A cross-agent memory layer lets jackin❯ promote durable operator preferences out of agent-private storage. A Claude Code memory such as "during PR review, fix every confirmed finding instead of returning report-only buckets" should become a workspace or workflow memory that every selected reviewer/implementer receives, not a file trapped under one Claude project path.
• Adding it after the consumers exist is much more painful than adding it first — that's the case for landing it early in Phase 3 even though no consumer in isolation justifies it.

Memory research

Several adjacent systems validate the need for a tool-neutral memory surface, but none maps directly onto jackin❯ container and host-mutation rules.

The key product distinction is packaged context vs mutable memory. Native APM support should own installable skills, prompts, instructions, plugins, and MCP server dependencies. This persistent storage layer should own mutable state: remembered operator preferences, facts learned during a workspace, workflow/run summaries, review outcomes, and memory audit events. The two can work together: APM can install the memory client skill or MCP server; the storage layer remains the authority for what a workspace/run remembers.

Inspiration in multicode

Sources:

• No dedicated README section (implementation-only feature)

• Source — lib/src/database.rs (Diesel + SQLite setup, migrations)

• Source — lib/src/schema.rs (full table definitions)

• Source — lib/src/services/persistent_storage.rs (read/write helpers)

• Source — lib/diesel.toml (migration toolchain config)

• Per-workspace SQLite at .multicode/cache.sqlite (one DB per workspace).

• Schema is small: primarily github_link_statuses (URL-keyed cache), plus a handful of metadata tables for workspace and task state.

• Migrations via Diesel's embedded migrations (build-time SQL files → embedded into the binary).

• Concurrency via single-writer SQLite, with a brief upsert-retry helper (5 attempts, exponential backoff base 100ms) for the rare contention case.

multicode also keeps a JSON snapshot file (~/.multicode/workspaces/<key>.json) alongside the SQLite — the JSON holds persistent operator-facing state (description and agent-provided links) while the SQLite holds the cached / queryable parts. We should not split this way; it duplicates state. SQLite alone is the right choice for jackin❯.

Recommended Shape

One SQLite database per instance, at ~/.jackin/data/<container>/jackin.db. Schema versioned via embedded migrations. "SQLite-first" names the on-disk format, not the client: all SQLite access uses turso (the workspace's single, async DB stack, already used by the Capsule-local cache) — never rusqlite, Diesel-on-SQLite, or any other binding. A sync caller makes its path async rather than reaching for a sync client. Document the production rationale in an ADR before promoting the current in-container cache pattern.

Tables (V1)

-- Status transitions. Rolling N=10000 entries, then trim oldest.
CREATE TABLE status_log (
    id INTEGER PRIMARY KEY,
    occurred_at INTEGER NOT NULL,            -- epoch seconds
    new_status TEXT NOT NULL,                -- 'idle' | 'busy' | 'question' | ...
    metadata TEXT                            -- optional JSON blob (e.g. prompt for 'question')
);

-- Captured agent tag emissions. Idempotent on (kind, value).
CREATE TABLE agent_tags (
    kind TEXT NOT NULL,                      -- 'repo' | 'issue' | 'pr' | 'link'
    value TEXT NOT NULL,
    first_seen INTEGER NOT NULL,
    last_seen INTEGER NOT NULL,
    PRIMARY KEY (kind, value)
);

-- GitHub link cache. Owned by github-link-tracking; defined here so
-- the schema is in one place.
CREATE TABLE github_link_statuses (
    url TEXT PRIMARY KEY,
    kind TEXT NOT NULL,
    host TEXT NOT NULL,
    owner TEXT NOT NULL,
    repo TEXT NOT NULL,
    resource_number INTEGER NOT NULL,
    issue_state TEXT,
    pr_state TEXT,
    build_state TEXT,
    review_state TEXT,
    pr_is_draft INTEGER,                     -- 0/1
    fetched_at INTEGER NOT NULL,
    refresh_after INTEGER NOT NULL,
    last_error TEXT
);

-- Token / cost samples. Rolling N=86400 entries (~1/sec for 24h).
-- `instance` is implicit in the per-instance DB file path; not stored per-row.
-- `session_id` is the Capsule session ID assigned over the in-container control
-- channel (`session.create` reply, also visible as the leftmost column in
-- `tmux list-sessions` analogues). There is no `sessions` table in V1 so this
-- column has no FOREIGN KEY; sessions are owned by the running jackin-capsule
-- daemon, not by the SQLite schema.
CREATE TABLE usage_samples (
    id INTEGER PRIMARY KEY,
    occurred_at INTEGER NOT NULL,            -- epoch seconds
    session_id INTEGER,                      -- Capsule session id; null when sample is not session-attributed
    workspace TEXT,
    provider TEXT NOT NULL,                  -- Agent::slug() from src/agent/mod.rs
    model TEXT NOT NULL,
    token_input INTEGER,
    token_output INTEGER,
    token_cache_read INTEGER,
    token_cache_write INTEGER,
    cost_usd_micros INTEGER,                 -- $cost * 1e6, integer-safe
    source_hash TEXT                         -- optional scanner/stream event hash for dedupe; never raw prompt text
);
CREATE INDEX usage_samples_by_time ON usage_samples (occurred_at);
CREATE INDEX usage_samples_by_session ON usage_samples (session_id, occurred_at);
CREATE INDEX usage_samples_by_workspace ON usage_samples (workspace, occurred_at);
CREATE UNIQUE INDEX usage_samples_by_source_hash
    ON usage_samples (source_hash)
    WHERE source_hash IS NOT NULL;

-- Tool fire audit. Rolling N=1000 entries.
CREATE TABLE tool_history (
    id INTEGER PRIMARY KEY,
    occurred_at INTEGER NOT NULL,
    tool_key TEXT NOT NULL,
    exit_code INTEGER,
    duration_ms INTEGER
);

-- Workspace/run memory. V1 stores text plus metadata; semantic indexes
-- and external memory backends come after the write/read policy is proven.
CREATE TABLE memory_items (
    id TEXT PRIMARY KEY,
    scope_kind TEXT NOT NULL,                -- 'operator' | 'workspace' | 'role' | 'run' | 'workflow'
    scope_key TEXT NOT NULL,
    kind TEXT NOT NULL,                      -- 'preference' | 'project_fact' | 'workflow_rule' | 'review_feedback' | 'run_summary'
    title TEXT NOT NULL,
    body TEXT NOT NULL,
    source TEXT NOT NULL,                    -- 'operator' | 'agent' | 'workflow' | 'import'
    source_session_id INTEGER,
    source_run_id TEXT,
    confidence TEXT NOT NULL,                -- 'operator_asserted' | 'agent_inferred' | 'verified' | 'stale'
    read_policy TEXT NOT NULL,               -- 'always' | 'workflow' | 'agent' | 'manual'
    write_policy TEXT NOT NULL,              -- 'operator_only' | 'agent_append' | 'workflow_append'
    created_at INTEGER NOT NULL,
    updated_at INTEGER NOT NULL,
    expires_at INTEGER,
    supersedes_id TEXT
);
CREATE INDEX memory_items_by_scope ON memory_items (scope_kind, scope_key, updated_at);
CREATE INDEX memory_items_by_kind ON memory_items (kind, updated_at);

-- Memory access audit. This is deliberately separate from memory_items so
-- retrieval/injection can be inspected without mutating the memory row.
CREATE TABLE memory_events (
    id INTEGER PRIMARY KEY,
    occurred_at INTEGER NOT NULL,
    memory_id TEXT,
    event_kind TEXT NOT NULL,                -- 'created' | 'read' | 'served_to_agent' | 'updated' | 'superseded' | 'hidden'
    actor_kind TEXT NOT NULL,                -- 'operator' | 'agent' | 'workflow' | 'system'
    actor_id TEXT,
    session_id INTEGER,
    run_id TEXT,
    metadata TEXT
);
CREATE INDEX memory_events_by_memory ON memory_events (memory_id, occurred_at);
CREATE INDEX memory_events_by_run ON memory_events (run_id, occurred_at);

-- Schema version. One row.
CREATE TABLE _meta (
    key TEXT PRIMARY KEY,
    value TEXT NOT NULL
);

Future leaves add more tables; the migration framework handles upgrades.

Cross-agent memory model

Memory is not a replacement for role packages, docs, or AGENTS files. It is the durable, queryable layer for context that is discovered or refined while work happens.

The V1 reader should be conservative. It should assemble a small "memory brief" before launching a session, ordered by scope specificity and provenance: workflow/run memory, workspace memory, role memory, then operator memory. The brief is delivered through runtime adapters: native memory/skill directories where safe, prompt preamble for runtimes without a stable memory API, and an MCP memory server once the host bridge and MCP surfaces exist. The agent should see the memory content and provenance, not the raw SQLite path.

The V1 writer should be even stricter. Agents may propose memory items or append run summaries, but automatic mutation of durable operator/workspace memory should require either an explicit operator command or a workflow-controlled append policy. This avoids the failure mode where one confused reviewer teaches every future agent a bad rule. Contradictions should create a new item that supersedes the old one instead of editing history in place; the audit table records the supersession.

Memory delivery and external backends

jackin❯ should expose the memory layer through a narrow internal API first:

memory.resolve(scope, workflow, role, agent, run) -> MemoryBrief
memory.propose(scope, kind, title, body, provenance) -> pending item
memory.append_run_summary(run, session, body) -> memory item
memory.hide/supersede(id, reason) -> operator action

Later, jackin❯ can expose an MCP server with equivalent operations so agents can query or propose memories during a session. The MCP server must enforce the same scope, read policy, write policy, and audit rules as the host CLI; it must not become arbitrary file access to the operator's host. External systems such as OpenMemory/Mem0, Letta, or Zep/Graphiti can become optional backends behind the same API only after V1 proves the local policy model.

Daemon-global account cache

Provider account quota snapshots do not belong in each per-instance database. The Token & Cost Telemetry plan makes the host daemon the single owner of provider refresh, then fans read-only account state out to Desktop, jackin console, Capsule, and CLI surfaces. The daemon-global cache keeps that state from diverging across role containers.

Location and lifecycle. The DB lives at ~/.jackin/data/daemon/accounts.db — outside any ~/.jackin/data/<container>/ tree so it survives jackin purge on individual instances. In the final architecture, the daemon creates and migrates this file on first start; CLI / jackin console / Capsule do not write to it, and the daemon process is the single writer. CLI tools and the console open the file read-only. WAL mode handles the daemon-write / multi-reader-process pattern (this table's concurrency model is different from the per-instance tables described in the "Concurrency model" section below — those assume single-process single-writer; the daemon-global cache assumes single-writer across all host processes, enforced by the daemon's per-operator-user singleton).

Current bridge: before the long-running host daemon exists, jackin usage <instance> accounts --sync-host-cache can explicitly create and upsert this same table from one selected Capsule daemon's cached rows. That manual path is operator-triggered and exists only to seed the host-global shape; automatic provider refresh remains daemon-owned future work.

CREATE TABLE account_usage_snapshots (
    id INTEGER PRIMARY KEY,
    provider TEXT NOT NULL,
    account_key_hash TEXT NOT NULL,
    account_label TEXT NOT NULL,
    source TEXT NOT NULL,
    confidence TEXT NOT NULL,
    window_kind TEXT NOT NULL,
    used_amount INTEGER,
    used_unit TEXT,
    limit_amount INTEGER,
    limit_unit TEXT,
    resets_at INTEGER,
    fetched_at INTEGER NOT NULL,
    expires_at INTEGER,                  -- nullable: not every provider returns an expiry
    status TEXT NOT NULL,
    last_error TEXT,
    UNIQUE(provider, account_key_hash, source, window_kind)
);

Lifecycle

• Created on first instance load. If the file doesn't exist, run the migration suite and set _meta.schema_version.
• Migrated on subsequent loads. If _meta.schema_version < current, run pending migrations.
• Kept with the data dir. The DB lives under the same ~/.jackin/data/<container>/ tree as the instance manifest and durable agent home, so normal restore and purge flows treat it as part of the instance.
• Deleted with purge. Standard data-dir teardown.

Concurrency model

Single writer (the running jackin process for that instance), many readers. Console subscribers may read concurrently. SQLite's WAL mode handles this; turn it on at DB creation time. No multi-writer scenarios exist in V1 because each instance has at most one running jackin process.

Memory introduces a second concurrency question: multiple agents inside one Capsule-managed container may read the same memory brief and propose new memory during the same run. V1 should serialize writes through the workflow runner or daemon-owned storage handle, not let each in-container agent open the database directly. Agents submit proposals through the control/MCP surface; the host-side storage owner stamps provenance and writes the row. This keeps the database private to jackin❯ and prevents an agent from rewriting durable memory with ordinary filesystem access.

Trimming

Tables with rolling-window semantics (status_log, usage_samples, tool_history) get trimmed on every Nth insert (cheap, bounded), not on a background timer. Counts above are starting points; tune after observing real footprint.

Scope (V1)

• One SQLite DB per ~/.jackin/data/<container>/, at jackin.db.
• One daemon-global cache for account quota snapshots once the host daemon owns provider refresh; per-instance databases remain the source for workspace/session token attribution.
• Schema above; migrations embedded in the binary via the chosen toolkit.
• WAL mode enabled at creation.
• Async access via the chosen sqlite client.
• Rolling-window trim per table.
• Workspace/run memory tables for text memories, provenance, scope, read/write policy, supersession, and access audit.
• Runtime-neutral memory resolution that can build a compact memory brief for Claude Code, Codex, Kimi, OpenCode, and future runtimes.
• Agent memory writes are proposals or workflow-scoped append events by default; durable operator/workspace memory changes require explicit operator action or a configured workflow write policy.
• DB creation failure is a hard error at instance load (don't silently start without persistence — would diverge silently).
• Migration failure halts load with a clear message; operator decides whether to purge or wait for a fix.

Defer

• Cross-instance shared SQLite (one operator-global DB). Per-instance is the right boundary for V1 — independent lifecycle, purge-friendly, no cross-instance state to leak.
• Encrypted-at-rest. SQLite has SEE; not in V1.
• Replication / sync. Out of scope for V1, but the memory schema should not assume local-only forever; future cloud or team memory sync needs stable ids, provenance, supersession, and conflict metadata from the start.
• Semantic vector/graph retrieval. V1 stores text and metadata; optional OpenMemory/Mem0, Letta, or Zep/Graphiti backends wait until the local policy model is proven.
• Automatic promotion of every agent observation into workspace memory. Agents can propose; operator/workflow policy decides what sticks.
• Read-only export / dump command (jackin debug dump-db <name>). Useful but small; defer.

Open Questions

• Turso production posture. Capsule-local usage telemetry now uses Turso's SQLite-compatible Rust engine, but the upstream crate is still a pre-1.0 release and the Turso project labels the engine beta. Before the host-global persistence layer ships, write an ADR covering build cost, migration tooling, multi-process behaviour, backup/export shape, and why Turso remains the right default for operator data.
• Schema in code vs schema in .sql files. Diesel-style embedded migrations are clean but add a new toolchain dependency. A small in-code migration list works fine for V1's table count. Recommended: in-code for V1; revisit if the schema explodes.
• Per-instance vs single global DB. Confirmed in the master: per-instance. This open question exists only as a checkpoint for the alternate path in case operator feedback shifts the call.
• Workspace memory across many instances. Per-instance DBs are purge-friendly, but workspace memory wants to survive individual instance purge and serve new containers for the same workspace. Should V1 add a workspace-level DB at ~/.jackin/data/workspaces/<workspace>/jackin.db, or keep memory in each instance DB until daemon/global storage lands? Recommended: design the memory API as workspace-scoped now, then decide the physical home in the storage ADR before implementation.
• Memory retrieval semantics. Is V1 only explicit scope filtering, or should it include keyword search, embeddings, or graph lookup? Recommended: explicit scope filtering plus operator-curated titles/kinds first; semantic retrieval follows only after there are enough real memories to evaluate quality.
• Agent write authority. Which workflows may auto-append memory? Recommended: run summaries and review findings may be workflow-append; operator preferences and project facts are operator-only until the operator accepts a proposal.

Related Files

• New module (e.g. src/storage/mod.rs or a new crate-internal storage/) — DB pool, migrations, query helpers
• New module (e.g. src/memory/mod.rs) — memory resolution, proposals, policy checks, and runtime-neutral MemoryBrief construction
• crates/jackin-runtime/src/runtime/launch.rs — DB creation on first load
• crates/jackin-runtime/src/runtime/cleanup.rs — DB teardown on purge
• Future control/MCP surface — agents query/propose memory through jackin, never by opening host-side storage directly
• Future ADR file under docs/content/docs/reference/developer-reference/adrs/ — Turso production posture plus physical home for workspace memory

See Also

• Agent Orchestration Program
• Token & Cost Telemetry — primary V1 consumer
• GitHub link tracking — schema home for the link cache
• Autonomous task queue — Phase 4 consumer
• Agent workflow orchestration — consumes memory briefs for brainstorm, implementation, review, and merge-gate phases
• Native APM support — package/install layer for skills, prompts, and MCP clients; complementary to mutable workspace memory
• Architecture Decision Records — the right home for the Turso production-posture decision