Mako SQLite Schema for Operational State Persistence: Complete Technical Reference

Mako uses six coordinated SQLite database schemas—workflow, runtime, session metadata, core execution, usage, and artifact—managed by migration functions in the operational-target layer to persist all operational state.

This guide explores the complete SQLite schema architecture that powers Apache Mako's operational state persistence. Understanding these database structures is essential for operators, contributors, and developers who need to debug state issues, extend functionality, or integrate with Mako's storage layer.

How Mako Structures Its SQLite Operational State

Mako's persistence layer centralizes operational state across six distinct subsystems, each with dedicated schema migration functions. Rather than a single monolithic database, the design uses targeted schemas that evolve independently through versioned migrations.

The master schema builder in packages/storage/src/operational-target-schema.ts orchestrates this assembly:

function buildOperationalTargetSchema(): ReadonlyMap<string, string> {
  const database = new DatabaseSync(':memory:');
  migrateSqliteRuntimeDatabase(database);
  migrateSqliteSessionMetadataDatabase(database);
  migrateSqliteCoreExecutionDatabase(database);
  migrateSqliteWorkflowDatabase(database);
  migrateSqliteUsageDatabase(database);
  migrateSqliteArtifactDatabase(database);
  ensureOperationalSchemaRegistry(database);
  return readSchema(database);
}

Each migration function—from migrateSqliteWorkflowDatabase to migrateSqliteArtifactDatabase—defines tables, indexes, and constraints for its domain. Version tracking uses SQLite's PRAGMA user_version, enabling safe incremental upgrades.

Workflow Schema: Session and Task Management

The workflow schema handles session lifecycles, task scheduling, and review workflows. Defined in packages/storage/src/sqlite-workflow-schema.ts, it contains the most diverse table set.

Core Workflow Tables

Table	Purpose
workflow_session_todo_documents	Per-session TODO documents tracking outstanding work
workflow_plan_events	Ordered plan events linked to sessions
workflow_deep_research_events	Deep-research event logging
workflow_scheduled_tasks	Task definitions with task_id, timestamps, and JSON payloads
workflow_scheduled_task_fires	Claim records preventing duplicate task execution
workflow_quote_companion_cleanup	Cleanup tracking for quoted companion responses
workflow_daily_review_state	Daily review configuration per session
workflow_daily_review_authority_state	Authority delegation for review workflows
workflow_daily_review_archives	Snapshots of archived review states
workflow_work_board_items	Kanban-style board items with scope, ordering, and archive flags
workflow_goal_authority	Goal-authority records with status tracking

The workflow schema includes strategic indexes for performance: ordering constraints on events and composite indexes for active-scope filtering, ensuring efficient queries against large session histories.

Runtime Schema: Events, Snapshots, and Continuations

The runtime schema in packages/storage/src/sqlite-runtime-schema.ts forms Mako's primary event log and state recovery mechanism. It captures everything happening during agent execution.

Event and Logging Tables

• runtime_events — The central append-only log of all runtime events
• tool_journal_events — Dedicated journal for tool invocation records
• tool_operations — Metadata and outcomes for individual tool operations

State Recovery and Continuation Tables

Table	Function
runtime_partial_snapshots	Point-in-time transcript snapshots for recovery
runtime_partial_segments	Incremental text segments composing snapshots
runtime_session_event_ordinals	Per-session event ordering without ROWID dependency
runtime_continuation_claims	Maps source sessions to continuation targets

Workspace Versioning Tables

Mako implements multi-versioned workspace state through three coordinated tables:

• runtime_workspace_epochs — Epoch markers for version boundaries
• runtime_workspace_versions — Complete version records with content hashes
• runtime_workspace_heads — Current head pointers per workspace

Additional runtime tables include runtime_legacy_invocation_openings for backward compatibility and runtime_capabilities for feature registry, including runtime_recovery_authority entries.

Session Metadata, Core Execution, Usage, and Artifact Schemas

Four additional schemas complete the operational state picture, each managed by dedicated migration functions.

Session Metadata Schema

The migrateSqliteSessionMetadataDatabase function creates session_metadata and related tables storing core session attributes: creation timestamps, parent relationships, termination status, and configuration snapshots.

Core Execution Schema

migrateSqliteCoreExecutionDatabase defines tables like core_agent_runs and core_agent_run_states, capturing agent execution contexts, step sequences, and checkpoint data for resumable operations.

Usage Schema

migrateSqliteUsageDatabase persists pricing telemetry through tables including usage_events and pricing_records, enabling cost tracking and quota enforcement across model providers and tool invocations.

Artifact Schema

migrateSqliteArtifactDatabase manages stored outputs via artifact_store for binary content and artifact_index for searchable metadata, supporting Mako's artifact retrieval and caching systems.

Schema Validation and Runtime Enforcement

Mako ensures schema integrity through two complementary mechanisms in the operational target layer.

Runtime Schema Registry

The ensureOperationalSchemaRegistry function runs at startup, verifying that all expected tables and indexes exist with correct definitions. It compares the actual database state against the canonical schema built by buildOperationalTargetSchema.

Schema Assertion for Testing

assertCurrentOperationalTargetSchema provides strict validation for testing and deployment scenarios, failing fast if any schema drift is detected.

Both mechanisms rely on readSchema(database), which introspects the SQLite catalog to produce a normalized representation for comparison.

Summary

• Six coordinated schemas compose Mako's complete SQLite operational state: workflow, runtime, session metadata, core execution, usage, and artifact
• Migration functions in sqlite-*-schema.ts files version each schema independently using PRAGMA user_version
• Runtime enforcement via ensureOperationalSchemaRegistry prevents production deployments against incompatible database states
• Source locations: packages/storage/src/operational-target-schema.ts (orchestration), packages/storage/src/sqlite-workflow-schema.ts, packages/storage/src/sqlite-runtime-schema.ts (primary definitions)

Frequently Asked Questions

What triggers Mako's SQLite schema migrations?

Migrations execute automatically when the operational target initializes a database connection. Each migration function checks PRAGMA user_version and applies incremental DDL statements to bring the database current. No manual intervention is required for standard deployments.

How does Mako handle schema compatibility across versions?

assertCurrentOperationalTargetSchema validates that the running code's expected schema matches the actual database structure. Mismatches produce explicit errors at startup rather than runtime failures. Downgrades are not supported; operators must restore from backup or recreate databases when rolling back versions.

Can I query Mako's operational databases directly?

Direct query access is possible but unsupported. The schemas are implementation details that change between releases. For integration needs, prefer Mako's official APIs or submit feature requests for stable observability interfaces.

Where are the SQLite database files located?

Database paths depend on Mako's configuration and deployment mode. The operational target accepts connection parameters specifying file locations or :memory: for ephemeral instances. Container deployments typically mount persistent volumes at configured paths.

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