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

Maka persists all session metadata and runtime events in a versioned SQLite database split across two schema files: sqlite-session-metadata-schema.ts for session-level data and 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
Runtime	Immutable event ledger, tool journals, partial snapshots, and workspace versioning	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 (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:

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:

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:

// 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

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, lines 43-55.

Retrieve Full Event Ledger

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, lines 36-48.

Record Sub-Agent Spawn

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, 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 for sessions/labels/tombstones, 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.

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