Understanding ai-memory Workstream Storage: The workstream_events Table and raw/workstreams JSONL Segments

The workstream_events table provides a queryable, append-only ledger of transcript events with full-text search capabilities, while the raw/workstreams/ JSONL segments serve as an immutable archive of sanitized hook payloads for audit and recovery purposes.

The ai-memory project implements a dual-layer storage architecture for managing AI workstream transcripts from language-model harnesses like Claude, Codex, and OpenCode. This design combines a normalized SQLite ledger with immutable filesystem archives to balance query performance with data durability. Understanding the relationship between the workstream_events table and the raw/workstreams/ JSONL segments is essential for developers integrating with the memory store or troubleshooting transcript persistence.

The Dual-Layer Storage Architecture

ai-memory maintains two complementary representations of every managed workstream, each optimized for different access patterns and durability guarantees.

The workstream_events SQLite Table

The workstream_events table lives in <data_dir>/db/memory.sqlite and serves as the primary query interface. Defined in the migration [V31__managed_workstreams.sql](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/migrations/V31__managed_workstreams.sql), this table stores one row per visible transcript event—including messages, tool calls, tool results, compactions, checkpoints, and annotations.

The table enforces append-only semantics through a composite primary key of (workstream_id, sequence), ensuring that events are immutable once written. For full-text search capabilities, the schema includes a virtual table workstream_events_fts that indexes the content field. Triggers defined in the migration automatically synchronize the FTS index when rows are inserted into the main table.

This structure powers the MCP tools memory_query and memory_search_workstream_events, providing fast, indexed access for UI features like "show the latest assistant replies."

The raw/workstreams/ JSONL Archive

Alongside the SQLite ledger, sanitized hook payloads are stored as immutable JSONL segments under <data_dir>/raw/workstreams/<workstream-id>/segments/*.jsonl. Each segment file contains newline-delimited HookEnvelope records representing the raw observations as they arrived from various harnesses.

According to [docs/managed-workstreams.md](https://github.com/akitaonrails/ai-memory/blob/main/docs/managed-workstreams.md), these files act as a canonical, immutable archive used for privacy-preserving fallback reads, ledger rebuilding, and audit purposes. Because they are immutable, they can be safely retained even when the SQLite ledger is compacted or pruned. The server-side sanitizer processes payloads before they reach either storage layer, ensuring no untrusted data touches the filesystem without first being bounded.

How the Components Interact

The storage layers operate in tandem during the workstream lifecycle:

1. Hook Processing: When a hook arrives (e.g., a model-generated message), the ai-memory-hooks crate sanitizes the payload and writes a JSONL record into the appropriate raw/workstreams/<id>/segments/ file.

2. Ledger Insertion: The same sanitized data is inserted into the workstream_events table via the store layer in crates/ai-memory-store/src/workstream.rs. The insertion automatically updates the workstream_events_fts virtual table through triggers defined in V31__managed_workstreams.sql.

3. Query Execution: Applications query the workstream_events table (or the FTS view) for fast, indexed access via Rust APIs or MCP tools.

4. Fallback Access: For debugging, privacy-preserving replay, or catastrophic recovery, administrators can read the original JSONL segment files directly from the filesystem.

Practical Implementation Examples

Inserting Events into workstream_events

The store layer handles insertion through parameterized queries in crates/ai-memory-store/src/workstream.rs:

tx.execute(
    "INSERT INTO workstream_events(
        workstream_id, sequence, event_id, agent_kind,
        native_session_id, kind, role, content,
        occurred_at, metadata_json, segment_path, created_at
    ) VALUES (
        ?1, ?2, ?3, ?4,
        ?5, ?6, ?7, ?8,
        ?9, ?10, ?11, ?12
    )",
    params![
        workstream_id.as_bytes(),
        next_sequence,
        event_id,
        agent,
        native_session,
        kind,
        role,
        content,
        timestamp,
        metadata_json,
        segment_path,
        now,
    ],
)?;

This operation triggers the workstream_events_fts_ai trigger to populate the full-text index.

Querying with Full-Text Search

Applications can search transcript content using the reader pool:

use ai_memory_store::ReaderPool;

let results = reader_pool
    .search_workstream_events(
        workstream_id,                 // the UUID of the workstream
        "important keyword".into(),   // FTS query string
        10,                           // limit
    )
    .await?;

Under the hood, this joins workstream_events_fts with the main table to return matched rows sorted by relevance.

Accessing Raw JSONL Segments

To inspect the original sanitized payloads outside the database:

# Show the raw segment for a given workstream ID (replace <ws-id>)

cat "$(ai-memory config --data-dir)/raw/workstreams/<ws-id>/segments/0000000000000001.jsonl"

Each line represents a HookEnvelope object. Segment filenames match the segment_path column stored in the corresponding SQLite row.

Cleanup and Deletion Operations

When purging a workstream, the store performs coordinated deletion across both layers. As implemented in crates/ai-memory-store/src/ops.rs, the system first removes rows from workstream_events (which cascades to the FTS table), commits the transaction, then deletes the corresponding directory under raw/workstreams/:

let raw_dir = data_dir.join("raw/workstreams").join(workstream_id.to_string());
// after tx.commit():
std::fs::remove_dir_all(raw_dir)?;

Key Source Files

understanding the implementation requires examining these specific files:

• crates/ai-memory-store/migrations/V31__managed_workstreams.sql — Defines the workstream_events schema, FTS virtual table, and synchronization triggers.
• crates/ai-memory-store/src/workstream.rs — Contains the core store logic for inserting and selecting workstream events.
• crates/ai-memory-store/src/ops.rs — Handles post-commit cleanup of raw JSONL directories during deletion.
• docs/ARCHITECTURE.md — High-level documentation of the storage layers and privacy boundaries.
• docs/managed-workstreams.md — Detailed specification of the JSONL segment format and privacy guarantees.

Summary

• The workstream_events table provides an append-only, normalized ledger with full-text search via SQLite, serving as the primary query interface for applications.
• The raw/workstreams/ JSONL segments act as an immutable, sanitized archive of original hook payloads, enabling audit trails and disaster recovery.
• Data flows from harness hooks through the sanitizer into both storage layers simultaneously, ensuring consistency between the queryable ledger and the canonical archive.
• The architecture separates concerns: SQLite handles performance and searchability, while the filesystem guarantees immutability and complete historical preservation.
• Deletion operations cascade from the database to the filesystem, with raw directory cleanup occurring only after successful transaction commits.

Frequently Asked Questions

What is the difference between workstream_events and raw/workstreams/?

The workstream_events table is a normalized, queryable SQLite ledger optimized for fast retrieval and full-text search, while the raw/workstreams/ directory contains immutable JSONL files that serve as the canonical archive of sanitized hook payloads. The SQLite table provides indexed access for applications, whereas the JSONL segments ensure data durability and audit capability even if the database is compacted or corrupted.

How does full-text search work in ai-memory?

Full-text search operates through the workstream_events_fts virtual table, which is automatically synchronized with the main workstream_events table via triggers defined in V31__managed_workstreams.sql. When content is inserted into the main table, the trigger updates the FTS index, allowing queries to perform efficient text matching over transcript content using the search_workstream_events method in the reader pool.

Are the raw JSONL files mutable?

No, the JSONL segment files under raw/workstreams/ are strictly immutable once written. They serve as a permanent, append-only archive of sanitized observations. This immutability ensures that the original data can be retained for audit purposes even when the SQLite ledger undergoes compaction or pruning operations. Files are only removed when an administrator explicitly deletes the entire workstream.

How does ai-memory ensure data consistency between the database and raw files?

Consistency is maintained by writing to both layers during the same hook processing operation. The sanitizer first writes the JSONL record to the filesystem, then the store layer inserts the corresponding row into workstream_events within a transaction. During deletion, the system removes database rows first, commits the transaction, then removes the raw directory, ensuring that the database never references non-existent segment files.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}