# Mako SQLite Schema for Operational State Persistence: Complete Technical Reference

> Explore the Mako SQLite schema for operational state persistence. This technical reference details the six coordinated schemas managing workflow, runtime, session metadata, core execution, usage, and artifact data.

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

---

**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`](https://github.com/apache/maka/blob/main/packages/storage/src/operational-target-schema.ts) orchestrates this assembly:

```typescript
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`](https://github.com/apache/maka/blob/main/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`](https://github.com/apache/maka/blob/main/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`](https://github.com/apache/maka/blob/main/packages/storage/src/operational-target-schema.ts) (orchestration), [`packages/storage/src/sqlite-workflow-schema.ts`](https://github.com/apache/maka/blob/main/packages/storage/src/sqlite-workflow-schema.ts), [`packages/storage/src/sqlite-runtime-schema.ts`](https://github.com/apache/maka/blob/main/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.