Agentsview Database Schema for Sessions and Messages: Architecture and Migration Strategy

Agentsview stores agent sessions and messages in a local SQLite archive with an extensible schema that supports incremental migrations via idempotent ALTER TABLE statements, automatic column detection, and versioned resyncs without data loss.

Agentsview is an open-source tool that archives AI agent interactions into a queryable local database. Understanding the database schema for sessions and messages is essential for developers extending the parser or building analytics on top of the stored transcripts, as implemented in kenn-io/agentsview.

Core Database Schema Design

Agentsview persists all data to a local SQLite file defined in internal/db/schema.sql. The architecture centers on two primary tables—sessions and messages—that track session metadata and individual conversation turns with full provenance.

The Sessions Table

The sessions table stores one row per parsed agent session with a primary key of id TEXT. Key columns include project, machine, agent, started_at, ended_at, and message_count for basic metadata. The schema also captures file system details via file_path, file_size, file_mtime, file_inode, file_device, and file_hash to detect external changes. Performance and outcome metrics are stored in total_output_tokens, peak_context_tokens, is_automated, tool_failure_signal_count, tool_retry_count, edit_churn_count, and outcome. Relationship tracking is supported through parent_session_id and relationship_type, while git_branch and cwd provide repository context.

The table is designed for extensibility; new columns are added by migrations without breaking existing archives.

The Messages Table

The messages table captures every turn within a session using an autoincrementing id INTEGER primary key. Foreign key session_id TEXT links to sessions.id, while ordinal INTEGER establishes message order within the conversation. Content fields include role TEXT, content TEXT, and thinking_text. Technical metadata covers timestamp, is_system, model, token_usage, context_tokens, output_tokens, and claude_message_id. Source tracking uses source_type and source_uuid, with special flags is_sidechain and is_compact_boundary handling edge cases in transcript parsing.

The composite index on (session_id, ordinal) enables fast range scans and incremental appends essential for large session files.

Indexes and Query Performance

Auxiliary indexes defined in internal/db/schema.sql accelerate common access patterns. These include idx_sessions_project for filtering by codebase and idx_messages_session_ordinal for rapid message retrieval. Partial indexes optimize queries against sparsely populated columns, ensuring the archive remains performant even as it grows to millions of messages.

How the Migration System Works

The migration engine lives in internal/db/db.go and operates automatically when the Open function is invoked. The system distinguishes between schema changes (structural columns) and data versions (parser logic updates), handling each with different strategies to prevent data loss.

Schema Detection and Initialization

When the binary starts, probeDatabase reads PRAGMA user_version and checks for required columns via needsSchemaRebuild. If a required column is missing indicating a schema-stale archive, the system executes dropDatabase to remove the file entirely and recreates it from the embedded schema.sql. If the schema is intact but the stored user_version is older than the compiled dataVersion constant (currently 59), the database is preserved but flagged with dataStale to trigger a full resync later.

The openAndInit function then opens a writer connection, executes the embedded DDL (schemaSQL), and initializes fresh databases with PRAGMA user_version = dataVersion.

Idempotent Column Migrations

The migrateColumns method in db.go performs incremental schema updates without dropping tables. It iterates over a hard-coded slice of {table, column, ddl} structs, checking pragma_table_info to detect missing columns:

migrations := []struct {
    table  string
    column string
    ddl    string
}{
    {"sessions", "display_name", "ALTER TABLE sessions ADD COLUMN display_name TEXT"},
    {"messages", "model", "ALTER TABLE messages ADD COLUMN model TEXT"},
    // ... dozens more entries
}

for _, m := range migrations {
    var cnt int
    err := w.QueryRow(
        fmt.Sprintf("SELECT count(*) FROM pragma_table_info('%s') WHERE name = '%s'", 
            m.table, m.column),
    ).Scan(&cnt)
    if err != nil {
        return fmt.Errorf("probing %s.%s: %w", m.table, m.column, err)
    }
    if cnt == 0 {
        if _, err := w.Exec(m.ddl); err != nil {
            return fmt.Errorf("adding %s.%s: %w", m.table, m.column, err)
        }
        log.Printf("migration: added column %s.%s", m.table, m.column)
    }
}

This approach makes every migration idempotent—running the same binary against an older archive safely adds only the missing columns. After column updates, the system creates partial indexes, back-fills helper columns like is_automated, and creates auxiliary tables such as remote_skipped_files and worktree_project_mappings.

Handling Data Version Resyncs

When the dataVersion constant exceeds the database's stored version, the migration logic sets d.dataStale.Store(true) instead of modifying the schema. This flag signals the sync engine to reset file timestamps, forcing a non-destructive re-parse of all sessions with the new parser logic. This separation allows structural changes (columns) to happen immediately while logic changes (parsing rules) trigger a background resync.

The entire migration path is non-blocking for readers because the DB struct maintains separate read-only and write pools that can be swapped safely. A db.mu.Lock() ensures migrations run exactly once even under concurrent goroutine access.

Querying the Database Schema

Retrieving Sessions and Messages

To query the database schema for sessions and messages, use the reader facade provided by internal/db/db.go:

ctx := context.Background()
db := internal/db.Open("path/to/archive.db") // Returns *internal/db.DB

// Fetch session metadata
var s internal/db.Session
err := db.GetReader().QueryRowContext(
    ctx,
    "SELECT id, project, agent, message_count, started_at FROM sessions WHERE id = ?",
    sessionID,
).Scan(&s.ID, &s.Project, &s.Agent, &s.MessageCount, &s.StartedAt)

// Retrieve first 20 messages ordered by conversation flow
rows, err := db.GetReader().QueryContext(
    ctx,
    `SELECT ordinal, role, content, model, token_usage 
     FROM messages 
     WHERE session_id = ? 
     ORDER BY ordinal ASC 
     LIMIT 20`,
    sessionID,
)
if err != nil {
    log.Fatal(err)
}
defer rows.Close()

Checking Migration Status Programmatically

You can verify the current schema version and column existence using SQLite pragmas:

-- Check the data version stored by Agentsview
PRAGMA user_version;

-- Verify if a specific migration column exists
SELECT name FROM pragma_table_info('sessions') WHERE name = 'display_name';

Summary

• Agentsview uses a local SQLite archive with two primary tables: sessions (metadata and metrics) and messages (conversation turns with ordinal ordering).
• The schema is defined in internal/db/schema.sql and accessed through the DB wrapper in internal/db/db.go.
• Migrations are idempotent: migrateColumns checks pragma_table_info before executing ALTER TABLE ... ADD COLUMN, ensuring older archives automatically update without data loss.
• Data versions (tracked via PRAGMA user_version) trigger full re-syncs when parser logic changes, while schema changes add columns immediately.
• The system uses separate reader/writer pools and mutex locking to perform migrations safely without blocking concurrent reads.

Frequently Asked Questions

What happens if the database schema is outdated?

If probeDatabase detects a required column is missing, Agentsview treats the archive as schema-stale and executes dropDatabase to recreate the file from scratch using the embedded schema.sql. If only the user_version is outdated, the database is preserved and flagged for a non-destructive resync.

How does Agentsview add new columns without losing existing data?

According to the source code in internal/db/db.go, the migrateColumns function queries pragma_table_info to check column existence before running ALTER TABLE ... ADD COLUMN. This ensures migrations only add missing columns, preserving all existing rows and indexes.

What is the difference between schema version and data version in Agentsview?

The schema version refers to the physical column structure of tables like sessions and messages. The data version (currently constant 59) encodes parser-level changes that affect how transcript files are interpreted. Schema changes trigger column additions, while data version mismatches trigger a full re-parse of all session files.

Can I query the database while migrations are running?

Yes. The DB struct maintains separate connection pools for reader and writer. Migrations execute under a mutex lock on the writer pool, but read-only queries through GetReader() continue operating on the existing schema until the migration commits, ensuring the application remains responsive during startup updates.

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