[Feature] JSONL-backed session persistence with Store interface

[is-Xiaoen]

🎯 The Goal / Use Case

The current session system (pkg/session/manager.go) stores everything in an in-memory map and serializes full JSON files to disk on every Save(). This works for simple cases, but breaks down in a few real scenarios:

1. Race condition with summarization — maybeSummarize() launches a background goroutine that calls TruncateHistory() + Save() while new messages are still being appended. This can corrupt persisted history and cause 400 errors on the next LLM call (see Race condition in session history causes "tool_call_ids did not have response messages" (HTTP 400) #704).
2. Full serialization is expensive — every Save() marshals the entire session to JSON and does a temp-file-rename dance. On embedded devices with limited I/O bandwidth, this adds latency that users can feel.
3. No session lifecycle management — there's no way to expire old sessions, no size limits, no cleanup. On a long-running gateway, sessions/ just grows forever.
4. No shared memory across agents — the multi-agent work (Feature: Base Multi-agent Collaboration Framework & Shared Context #294, WIP: feat: base multi-agent collaboration framework & shared context #423) will need some form of shared context pool. The current per-session JSON files can't support that.

I ran into the race condition issue (#704) myself while testing on a Pi, and when I dug into the session code I realized the problems go deeper than just adding a mutex.

💡 Proposed Solution

Replace the JSON file backend with SQLite, using a pure-Go driver (modernc.org/sqlite or crawshaw.io/sqlite) so we keep zero-CGo cross-compilation.

The idea is to keep SessionManager's public API mostly the same so nothing upstream breaks, but swap the storage layer underneath.

Rough schema I'm thinking:

CREATE TABLE sessions (
    key TEXT PRIMARY KEY,
    summary TEXT DEFAULT '',
    created_at DATETIME,
    updated_at DATETIME
);

CREATE TABLE messages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    session_key TEXT NOT NULL REFERENCES sessions(key),
    role TEXT NOT NULL,
    content TEXT,
    tool_calls TEXT,      -- JSON, nullable
    tool_call_id TEXT,    -- nullable
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_messages_session ON messages(session_key, id);

Key benefits:

• AddMessage becomes an INSERT instead of full-file rewrite
• TruncateHistory + Save can run in a transaction — no more race condition
• Session expiry is just DELETE FROM sessions WHERE updated_at < ?
• Later, a memory table can hold long-term facts for multi-agent shared context

🛠 Potential Implementation

I'd break this into a few PRs:

PR 1: New pkg/memory/ package with a Store interface + SQLite implementation + tests. Doesn't touch anything existing yet.

PR 2: Wire it into AgentLoop — replace SessionManager.Save() calls with the new store. Keep the JSON path as fallback (auto-detect: if sessions/*.json exists, migrate on first load).

PR 3: Add lifecycle management — TTL-based session expiry, configurable max message count per session, /session stats command or similar.

I want to keep the interface clean enough that someone could later swap in a different backend (Postgres for a server deployment, etc.) without touching agent code.

🚦 Impact & Roadmap Alignment

• This is a Core Feature
• This is a Nice-to-Have / Enhancement
• This aligns with the current Roadmap

This was specifically mentioned in the 26M2W3 community meeting notes under the Agent board: "记忆系统：引入 SQLite". It also directly addresses #704 (race condition) and provides the foundation for #294 (multi-agent shared context).

🔄 Alternatives Considered

• Just fix the race condition with a mutex: Solves Race condition in session history causes "tool_call_ids did not have response messages" (HTTP 400) #704 but doesn't address the full-serialization cost or lifecycle management. Band-aid.
• bbolt / badger (embedded KV stores): More overhead than SQLite for structured data. We need relational queries (messages by session, ordered by time) and SQLite is purpose-built for this.
• Keep JSON but write incrementally (append-only log): Considered this. It handles the write performance, but makes truncation/expiry complicated and doesn't help with the shared memory use case.

💬 Additional Context

I've already had two PRs merged (#173, #186) and I'm familiar with the session/agent code paths. Happy to take this on if the direction looks right.

Would appreciate feedback from the agent board maintainers on:

1. Is modernc.org/sqlite acceptable, or is there a preference for another driver? (It adds ~3-4MB to the binary — need to check if that fits the 20M target from the meeting notes)
2. Should the migration from JSON be automatic, or a separate picoclaw migrate subcommand?
3. Any concerns about SQLite on the target embedded platforms?

[is-Xiaoen]

Update on direction: after the review discussion on #719, I'm revising the approach.

@yinwm raised a solid point — for this use case, JSONL append-only is a better fit than SQLite:

• Append is already atomic at the OS level, no transaction machinery needed
• Agent tools (read_file, tail, grep) can directly operate on session history — pretty important for an AI agent framework
• Zero new dependencies
• Last N messages = last N lines of the file

The tricky part was TruncateHistory — physically deleting lines from a JSONL breaks append-only semantics. The solution is logical truncation: store a skip offset in a separate .meta.json file. GetHistory reads from that offset onward. This way truncation is just an O(1) metadata write, and the JSONL file stays append-only.

Revised file layout:

sessions/
├── telegram_123456.jsonl       # one message per line, append-only
├── telegram_123456.meta.json   # summary, skip offset, timestamps

The Store interface from #719 remains the same — it's backend-agnostic, so JSONL is just another implementation behind it. SQLite can still be added later if multi-agent shared context or complex queries actually need it.

Will follow up with a revised PR.

[Zhaoyikaiii]

This SQLite design makes sense. One request: please keep persistence (session + memory) provider-agnostic, and add a small abstraction in the prompt builder to separate static vs dynamic context.

Static: agent identity/rules, tool catalog, project rules (rarely changes)

Dynamic: current time/timezone, session summary, retrieved memories, recent turns (changes often)

This matters for provider-specific caching:

Anthropic supports system blocks + explicit cache breakpoints (cache_control), so we can cache the static block while still keeping dynamic facts (e.g. time) at system priority.

OpenAI is mostly “longest common prefix” caching, so we need deterministic serialization (stable ordering/whitespace, sorted tools) and push dynamic bits later.

If we store structured inputs (messages/summary/memory items) rather than a fully-rendered prompt, we can optimize both providers without leaking vendor details into the storage layer.

[nikolasdehor]

The pivot from SQLite to JSONL (per @is-Xiaoen's update) makes sense for picoclaw's constraints. A few thoughts on the JSONL + meta approach:

Logical truncation via skip offset is clever, but watch for the file growth problem. An append-only JSONL with only logical truncation means the file grows unbounded. You will need periodic physical compaction -- rewrite the file keeping only messages from the skip offset onward. This could run on a threshold (e.g., when skipped bytes > active bytes) or on a schedule. The compaction should use the same temp-file-rename pattern that the current JSON Save() uses.

Concurrent access: JSONL append is OS-atomic only for writes <= PIPE_BUF (4096 bytes on Linux, varies on macOS). A single message JSON line could exceed this for large tool results (e.g., file content in a read_file response). For correctness, the writer should still use flock() or a mutex to serialize appends. This is much lighter than the current full-marshal-on-every-save but not zero-cost.

On @Zhaoyikaiii's static vs dynamic context split: This is important and orthogonal to the storage backend. The key insight is that provider-specific prompt caching (Anthropic's cache_control, OpenAI's prefix caching) requires the prompt builder to know which parts are stable across turns. If the Store interface returns structured data (summary, memory items, messages) rather than a pre-rendered string, the prompt builder can optimize caching per-provider. This should be a design constraint on the Store interface from the start.

Migration: Auto-migration from JSON to JSONL on first load is the right call. A separate 'picoclaw migrate' command adds friction. The migration path is straightforward: read JSON session, write each message as a JSONL line, create .meta.json with skip=0 and existing summary, rename .json to .json.bak.

Looking forward to the revised PR.

[is-Xiaoen]

Thanks for the feedback @Zhaoyikaiii @nikolasdehor — both really helpful.

On provider-agnostic persistence + structured data (@Zhaoyikaiii): Fully agree. The Store interface in #732 returns structured data — GetHistory gives []Message and GetSummary gives a separate string, not a pre-rendered prompt. This keeps the storage layer clean and lets the prompt builder handle the static/dynamic split and provider-specific caching (Anthropic cache_control vs OpenAI prefix caching) without any vendor details leaking into storage. That optimization can be layered on top in a follow-up.

On file growth / compaction (@nikolasdehor): Good catch — logical truncation alone does leave dead lines on disk. I've added a Compact(ctx, sessionKey) method to the Store interface (just pushed to #732). It rewrites the JSONL keeping only active messages, using temp+rename for crash safety. No-op when there's nothing to compact. The caller decides when to trigger it — threshold-based (e.g. skipped > active) or schedule-based. Keeping the policy out of the store itself.

On PIPE_BUF / concurrent writes: The implementation uses a per-session sync.Mutex to serialize all operations on the same session, so even messages exceeding PIPE_BUF are safe. Different sessions can be written concurrently without contention.

The full implementation is up in #732 — 30 tests, 0 lint issues, zero new dependencies. Would appreciate a look when you get a chance.