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

> Explore the Agentsview database schema for sessions and messages. Learn about its architecture, migration strategy, and how it ensures data integrity during updates.

- Repository: [Kenn Software/agentsview](https://github.com/kenn-io/agentsview)
- Tags: architecture
- Published: 2026-07-06

---

**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](https://github.com/kenn-io/agentsview).

## Core Database Schema Design

Agentsview persists all data to a local SQLite file defined in [`internal/db/schema.sql`](https://github.com/kenn-io/agentsview/blob/main/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`](https://github.com/kenn-io/agentsview/blob/main/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`](https://github.com/kenn-io/agentsview/blob/main/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`](https://github.com/kenn-io/agentsview/blob/main/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`](https://github.com/kenn-io/agentsview/blob/main/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:

```go
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`](https://github.com/kenn-io/agentsview/blob/main/internal/db/db.go):

```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:

```sql
-- 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`](https://github.com/kenn-io/agentsview/blob/main/internal/db/schema.sql) and accessed through the `DB` wrapper in [`internal/db/db.go`](https://github.com/kenn-io/agentsview/blob/main/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`](https://github.com/kenn-io/agentsview/blob/main/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`](https://github.com/kenn-io/agentsview/blob/main/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.