Skip to main content

Database Schema

Distri uses SQLite for local storage. The database is created at .distri/distri.db when you run distri serve or execute agents.

Connecting to the Database​

sqlite3 .distri/distri.db

Or use any SQLite client (DB Browser, TablePlus, etc.).

Core Tables​

threads​

Conversation contexts that persist across messages.

CREATE TABLE threads (
    id TEXT PRIMARY KEY NOT NULL,
    agent_id TEXT NOT NULL,
    title TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,
    message_count INTEGER NOT NULL DEFAULT 0,
    last_message TEXT,
    metadata TEXT NOT NULL,      -- JSON
    attributes TEXT NOT NULL,    -- JSON
    external_id TEXT             -- For linking to your system
);

Example queries:

-- List all threads for an agent
SELECT id, title, message_count, created_at
FROM threads
WHERE agent_id = 'assistant'
ORDER BY updated_at DESC;

-- Find threads by external ID (your user/session ID)
SELECT * FROM threads WHERE external_id = 'user_123';

-- Get thread metadata
SELECT id, json_extract(metadata, '$.user_name') as user_name
FROM threads;

tasks​

Individual agent invocations within a thread.

CREATE TABLE tasks (
    id TEXT PRIMARY KEY NOT NULL,
    thread_id TEXT NOT NULL,
    parent_task_id TEXT,         -- For nested tasks
    status TEXT NOT NULL,        -- pending, running, completed, failed
    created_at BIGINT NOT NULL,  -- Unix timestamp ms
    updated_at BIGINT NOT NULL
);

Example queries:

-- Get all tasks for a thread
SELECT id, status, datetime(created_at/1000, 'unixepoch') as created
FROM tasks
WHERE thread_id = 'thread_abc123';

-- Find failed tasks
SELECT t.id, th.title, t.status
FROM tasks t
JOIN threads th ON t.thread_id = th.id
WHERE t.status = 'failed';

task_messages​

Messages exchanged during task execution (user messages, assistant responses, tool calls).

CREATE TABLE task_messages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    task_id TEXT NOT NULL,
    kind TEXT NOT NULL,          -- message, tool_call, tool_result, etc.
    payload TEXT NOT NULL,       -- JSON content
    created_at BIGINT NOT NULL   -- Unix timestamp ms
);

Example queries:

-- Get conversation history for a task
SELECT kind, payload, datetime(created_at/1000, 'unixepoch') as time
FROM task_messages
WHERE task_id = 'task_xyz'
ORDER BY created_at;

-- Extract message text from payload
SELECT
  kind,
  json_extract(payload, '$.content') as content
FROM task_messages
WHERE task_id = 'task_xyz';

-- Count messages by type
SELECT kind, COUNT(*) as count
FROM task_messages
GROUP BY kind;

session_entries​

Key-value storage scoped to a thread. Used by agents to maintain state.

CREATE TABLE session_entries (
    thread_id TEXT NOT NULL,
    key TEXT NOT NULL,
    value TEXT NOT NULL,         -- JSON
    expiry TIMESTAMP,            -- Optional TTL
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,
    PRIMARY KEY(thread_id, key)
);

Example queries:

-- Get all session data for a thread
SELECT key, value FROM session_entries
WHERE thread_id = 'thread_abc123';

-- Get specific session value
SELECT json_extract(value, '$') as data
FROM session_entries
WHERE thread_id = 'thread_abc123' AND key = 'user_preferences';

secrets​

Environment variables and API keys stored by the server.

CREATE TABLE secrets (
    id TEXT PRIMARY KEY NOT NULL,
    key TEXT NOT NULL UNIQUE,
    value TEXT NOT NULL,         -- Encrypted
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL
);

Example queries:

-- List all secret keys (values are encrypted)
SELECT key, updated_at FROM secrets;

agent_configs​

Registered agent configurations.

CREATE TABLE agent_configs (
    name TEXT PRIMARY KEY NOT NULL,
    config TEXT NOT NULL,        -- JSON agent definition
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL
);

Example queries:

-- List all registered agents
SELECT name, updated_at FROM agent_configs;

-- Get agent configuration
SELECT json_extract(config, '$.description') as description
FROM agent_configs
WHERE name = 'assistant';

prompt_templates​

Reusable prompt templates.

CREATE TABLE prompt_templates (
    id TEXT PRIMARY KEY NOT NULL,
    name TEXT NOT NULL,
    template TEXT NOT NULL,
    description TEXT,
    version TEXT,
    source TEXT NOT NULL DEFAULT 'user',
    is_system INTEGER NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL
);

Supporting Tables​

scratchpad_entries​

Intermediate agent thinking and planning data.

CREATE TABLE scratchpad_entries (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    thread_id TEXT NOT NULL,
    task_id TEXT NOT NULL,
    parent_task_id TEXT,
    entry TEXT NOT NULL,         -- JSON
    entry_type TEXT,
    timestamp BIGINT NOT NULL,
    created_at TIMESTAMP NOT NULL
);

memory_entries​

Long-term memory storage per user.

CREATE TABLE memory_entries (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id TEXT NOT NULL,
    content TEXT NOT NULL,       -- JSON
    created_at TIMESTAMP NOT NULL
);

external_tool_calls​

Tracking external tool invocations (for async tools).

CREATE TABLE external_tool_calls (
    id TEXT PRIMARY KEY NOT NULL,
    request TEXT NOT NULL,       -- JSON request payload
    response TEXT,               -- JSON response payload
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL
);

integrations​

OAuth and external service integrations.

CREATE TABLE integrations (
    id TEXT PRIMARY KEY NOT NULL,
    user_id TEXT NOT NULL,
    provider TEXT NOT NULL,
    session_data TEXT,           -- JSON
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,
    expires_at TIMESTAMP
);

Useful Queries​

Export conversation as JSON​

SELECT json_group_array(
  json_object(
    'kind', kind,
    'content', json_extract(payload, '$.content'),
    'time', datetime(created_at/1000, 'unixepoch')
  )
) as conversation
FROM task_messages
WHERE task_id = 'task_xyz'
ORDER BY created_at;

Analytics: Messages per day​

SELECT
  date(created_at/1000, 'unixepoch') as day,
  COUNT(*) as messages
FROM task_messages
GROUP BY day
ORDER BY day DESC
LIMIT 30;

Find threads with specific session data​

SELECT t.id, t.title, se.value
FROM threads t
JOIN session_entries se ON t.id = se.thread_id
WHERE se.key = 'user_tier'
AND json_extract(se.value, '$') = 'premium';

Backup & Migration​

Backup​

sqlite3 .distri/distri.db ".backup backup.db"

Export to SQL​

sqlite3 .distri/distri.db ".dump" > backup.sql

Restore​

sqlite3 new.db < backup.sql

Next Steps​