# SQLite Storage Schema for Maka Sessions, Runs, and Events: Complete Technical Reference

> Explore the Maka SQLite storage schema for sessions, runs, and events. Understand the two versioned schema files for session metadata and the event ledger.

- Repository: [The Apache Software Foundation/maka](https://github.com/apache/maka)
- Tags: api-reference
- Published: 2026-08-31

---

**Maka persists all session metadata and runtime events in a versioned SQLite database split across two schema files: [`sqlite-session-metadata-schema.ts`](https://github.com/apache/maka/blob/main/sqlite-session-metadata-schema.ts) for session-level data and [`sqlite-runtime-schema.ts`](https://github.com/apache/maka/blob/main/sqlite-runtime-schema.ts) for the immutable event ledger.**

The **SQLite storage schema** in Apache Maka organizes all conversational state into a single database file (`~/.local/share/maka/maka.db` by default). This architecture separates static session metadata from the append-only runtime event stream, enabling fast UI queries while guaranteeing reproducible execution histories.

---

## Schema Architecture Overview

Maka's storage layer divides into two logical groups defined in separate source files:

| Group | Purpose | Source File |
|-------|---------|-------------|
| **Session Metadata** | Static session attributes, labels, tombstones, and sub-agent relationships | [`packages/storage/src/sqlite-session-metadata-schema.ts`](https://github.com/apache/maka/blob/main/packages/storage/src/sqlite-session-metadata-schema.ts) |
| **Runtime** | Immutable event ledger, tool journals, partial snapshots, and workspace versioning | [`packages/storage/src/sqlite-runtime-schema.ts`](https://github.com/apache/maka/blob/main/packages/storage/src/sqlite-runtime-schema.ts) |

Both schemas use numbered migrations within the same file to evolve the database over time.

---

## Session Metadata Tables

### Core Session Storage

The **`session_metadata`** table stores the primary description of every session with searchable fields for UI filtering:

- `session_id TEXT PRIMARY KEY` — unique session identifier
- `payload_json TEXT NOT NULL` — serialized session configuration
- `name TEXT NOT NULL` — human-readable session name
- `status TEXT NOT NULL` — current session state
- `is_flagged INTEGER` — boolean flag for important sessions
- `is_archived INTEGER` — soft-archive marker
- `parent_session_id TEXT` — hierarchical session linking
- `revision_root_session_id TEXT` and `revision_index INTEGER` — version control for session forks
- `backend TEXT NOT NULL`, `llm_connection_slug TEXT NOT NULL`, `model TEXT NOT NULL` — LLM configuration
- `metadata_version INTEGER` — schema version for migrations
- `created_at INTEGER`, `last_used_at INTEGER`, `last_message_at INTEGER`, `committed_at INTEGER` — timestamps

This definition resides in **migration #1** of [`sqlite-session-metadata-schema.ts`](https://github.com/apache/maka/blob/main/sqlite-session-metadata-schema.ts) (lines 43-55).

### Label and Deletion Support

| Table | Structure | Purpose |
|-------|-----------|---------|
| **`session_metadata_labels`** | Composite PK (`session_id`, `label_index`), FK to `session_metadata` | One-to-many tag storage for query flexibility |
| **`session_metadata_tombstones`** | `session_id` PRIMARY KEY, `deleted_at INTEGER` | Soft-delete markers retained for historical consistency |

### Sub-Agent Spawn Tracking

The **`subagent_spawns`** table (added in migrations 4-5) records when tool calls create child sessions:

```sql
CREATE TABLE subagent_spawns (
  parent_session_id TEXT NOT NULL,
  parent_run_id TEXT NOT NULL,
  tool_call_id TEXT NOT NULL,
  swarm_id TEXT NOT NULL,
  item_id TEXT NOT NULL,
  request_fingerprint TEXT NOT NULL,
  child_session_id TEXT UNIQUE,
  initial_turn_id TEXT NOT NULL,
  initial_run_id TEXT NOT NULL,
  claimed_at INTEGER NOT NULL
);

```

This enables Maka to trace the complete provenance tree from parent sessions through nested tool invocations.

### Performance Indexes

The session metadata migration creates targeted indexes for common UI queries (lines 65-78):

- Recency: `last_used_at DESC`
- Status filtering: `status`, `is_archived`
- Hierarchical queries: `parent_session_id`, `revision_root_session_id`
- Label searches: `session_metadata_labels(label)`

---

## Runtime Tables: Events, Tools, and Snapshots

### Immutable Event Ledger

The **`runtime_events`** table forms the append-only foundation of Maka's execution history:

| Column | Constraint | Description |
|--------|-----------|-------------|
| `event_id TEXT` | PRIMARY KEY | Globally unique event identifier |
| `session_id TEXT` | NOT NULL | Parent session reference |
| `invocation_id TEXT` | NOT NULL | Execution scope identifier |
| `run_id TEXT` | NOT NULL | Specific run within session |
| `turn_id TEXT` | NOT NULL | Conversation turn |
| `event_seq INTEGER` | CHECK (event_seq > 0) | Strict ordering within invocation |
| `event_kind TEXT` | NOT NULL | Event type discriminator |
| `payload_json TEXT` | NOT NULL | Serialized event data |
| `committed_at INTEGER` | NOT NULL | Commit timestamp |

A UNIQUE constraint on `(invocation_id, event_seq)` guarantees monotonic, gap-free ordering essential for deterministic replay.

### Tool Call Lifecycle

Maka tracks external tool execution through two coordinated tables defined in **migration #1** of [`sqlite-runtime-schema.ts`](https://github.com/apache/maka/blob/main/sqlite-runtime-schema.ts):

**`tool_operations`** — high-level tool call state:

- `operation_id TEXT PRIMARY KEY`
- `invocation_id`, `run_id`, `turn_id` — execution context
- `provider_tool_call_id TEXT` — external system identifier
- `tool_name TEXT NOT NULL` — invoked tool
- `canonical_args_hash TEXT` — deterministic input fingerprint
- `recovery_mode TEXT` — failure handling strategy
- `current_state TEXT` — lifecycle state
- `call_event_id`, `result_event_id` — FK references to `runtime_events`
- `version INTEGER` — optimistic locking

**`tool_journal_events`** — detailed operation log:

- `journal_seq INTEGER PRIMARY KEY AUTOINCREMENT`
- `journal_event_id TEXT UNIQUE`
- `operation_id`, `invocation_id`, `run_id`, `turn_id` — context
- `state TEXT` — granular state transitions
- `runtime_event_id` — FK to parent event
- `canonical_args_hash`, `recovery_mode`, `external_handle` — execution details
- `metadata_json` — extensible metadata

### Partial Snapshots and Continuation

| Table | Purpose |
|-------|---------|
| **`runtime_partial_snapshots`** | Incremental state for fast "continue from here" UI operations |
| **`runtime_continuation_claims`** | Provenance records linking new runs to predecessor executions |
| **`runtime_capabilities`** | Feature flags declaring runtime authority levels |
| **`runtime_workspace_epochs`** | Git workspace snapshots ensuring reproducible execution environments |

The `runtime_partial_snapshots` table stores `stream_key`, `payload_json`, `text_content`, and `updated_at` to enable instant UI resumption without full event replay.

---

## Run Identification and Linking

While `runtime_events` contains the detailed execution log, the canonical run record lives in **`core_agent_runs`**:

```typescript
// Essential columns (defined in later migration of sqlite-runtime-schema.ts)
{
  session_id: string;      // FK → session_metadata.session_id
  run_id: string;          // Run primary key
  turn_id: string;         // Current conversation turn
  invocation_id: string;   // Execution correlation ID
  created_at: number;      // Run start (Unix ms)
  committed_at: number;    // Last event commit
}

```

Indexes on `(session_id, run_id)` and `(session_id, turn_id)` optimize the UI's primary navigation paths.

---

## Query Examples

### List Active Sessions

```typescript
import { DatabaseSync } from 'node:sqlite';

function listActiveSessions(db: DatabaseSync) {
  return db.prepare(`
    SELECT session_id, name, status, last_used_at
    FROM session_metadata
    WHERE is_archived = 0
    ORDER BY last_used_at DESC
  `).all();
}

```

Source: [`sqlite-session-metadata-schema.ts`](https://github.com/apache/maka/blob/main/sqlite-session-metadata-schema.ts), lines 43-55.

### Retrieve Full Event Ledger

```typescript
function getRunEvents(
  db: DatabaseSync,
  sessionId: string,
  runId: string
) {
  return db.prepare(`
    SELECT event_id, event_seq, event_kind, payload_json, committed_at
    FROM runtime_events
    WHERE session_id = ? AND run_id = ?
    ORDER BY event_seq ASC
  `).all(sessionId, runId);
}

```

Source: [`sqlite-runtime-schema.ts`](https://github.com/apache/maka/blob/main/sqlite-runtime-schema.ts), lines 36-48.

### Record Sub-Agent Spawn

```sql
INSERT INTO subagent_spawns (
  parent_session_id,
  parent_run_id,
  tool_call_id,
  swarm_id,
  item_id,
  request_fingerprint,
  child_session_id,
  initial_turn_id,
  initial_run_id,
  claimed_at
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?);

```

Source: [`sqlite-session-metadata-schema.ts`](https://github.com/apache/maka/blob/main/sqlite-session-metadata-schema.ts), lines 161-176.

---

## Key Design Decisions

**WAL Journal Mode**: Both schema files configure `PRAGMA journal_mode=WAL` to allow concurrent readers during event writes—critical for responsive UI while preserving durability.

**Immutable Events**: `runtime_events` uses an append-only design with `event_seq` ordering. No updates or deletes; corrections append new events.

**JSON Extensibility**: Both `payload_json` and `metadata_json` columns allow schema evolution without migration, while structured columns enable indexed queries.

**Hierarchical Sessions**: `parent_session_id`, revision tracking, and `subagent_spawns` support complex multi-agent workflows with full provenance.

---

## Summary

- **Two-file schema split**: [`sqlite-session-metadata-schema.ts`](https://github.com/apache/maka/blob/main/sqlite-session-metadata-schema.ts) for sessions/labels/tombstones, [`sqlite-runtime-schema.ts`](https://github.com/apache/maka/blob/main/sqlite-runtime-schema.ts) for events/tools/snapshots
- **Session metadata** centers on `session_metadata` with satellite tables for labels, soft-deletes, and sub-agent tracking
- **Runtime ledger** uses `runtime_events` with strict `(invocation_id, event_seq)` ordering for reproducibility
- **Tool execution** splits between `tool_operations` (state machine) and `tool_journal_events` (activity log)
- **Advanced features** include partial snapshots, continuation claims, and workspace epochs for reproducible runs
- **WAL mode** enables concurrent read/write for responsive UI performance

---

## Frequently Asked Questions

### Where is the Maka SQLite database file located?

By default, Maka stores its database at `~/.local/share/maka/maka.db`. This path follows the XDG Base Directory specification. The location is configurable through Maka's storage initialization options.

### How does Maka guarantee event ordering within a run?

The `runtime_events` table enforces a UNIQUE constraint on `(invocation_id, event_seq)` with a CHECK constraint requiring `event_seq > 0`. This guarantees that events within a single execution context are strictly ordered without gaps, enabling deterministic replay and audit trails.

### What is the difference between `run_id` and `invocation_id`?

`run_id` identifies a specific run instance within a session, while `invocation_id` correlates all events belonging to the same logical execution—including retries and resumed continuations. Multiple runs may share an `invocation_id` when using the continuation feature.

### How does Maka handle session deletion without losing history?

Sessions use soft deletion via the `session_metadata_tombstones` table, which records `deleted_at` timestamps. The main `session_metadata` row may be removed or archived, but the tombstone preserves historical reference integrity for runs and events that remain in the database.