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

> Explore the workstream_events table for searchable transcript logs and raw/workstreams JSONL for immutable payload archives in akitaonrails/ai-memory. Understand your data flow.

- Repository: [Fabio Akita/ai-memory](https://github.com/akitaonrails/ai-memory)
- Tags: internals
- Published: 2026-08-22

---

**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/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)](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`](https://github.com/akitaonrails/ai-memory/blob/main/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`](https://github.com/akitaonrails/ai-memory/blob/main/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`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/workstream.rs):

```rust
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:

```rust
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:

```bash

# 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`](https://github.com/akitaonrails/ai-memory/blob/main/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/`:

```rust
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`](https://github.com/akitaonrails/ai-memory/blob/main/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`](https://github.com/akitaonrails/ai-memory/blob/main/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`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/ops.rs)** — Handles post-commit cleanup of raw JSONL directories during deletion.
- **[`docs/ARCHITECTURE.md`](https://github.com/akitaonrails/ai-memory/blob/main/docs/ARCHITECTURE.md)** — High-level documentation of the storage layers and privacy boundaries.
- **[`docs/managed-workstreams.md`](https://github.com/akitaonrails/ai-memory/blob/main/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`](https://github.com/akitaonrails/ai-memory/blob/main/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.