Understanding the Data Directory Structure and Database Schema in Agentsview

Agentsview stores all persistent data under a configurable .agentsview directory with a SQLite database file containing multiple normalized tables for sessions, messages, tool calls, and usage analytics.

The data directory structure and database schema in Agentsview are designed to support both local SQLite and optional PostgreSQL backends with identical schemas. This architecture allows the sync engine, HTTP server, and CLI to share the same session data and analytics through a unified storage layer.

Data Directory Layout

Agentsview creates a data directory on first startup. The default location is $HOME/.agentsview, but you can override this with the AGENTSVIEW_DATA_DIR environment variable (or the legacy AGENT_VIEWER_DATA_DIR).

According to the source code in internal/config/config.go, the resolution logic is:

// internal/config/config.go (excerpt)
if v := os.Getenv("AGENTSVIEW_DATA_DIR"); v != "" {
    dataDir = v
} else {
    dataDir = filepath.Join(os.UserHomeDir(), ".agentsview")
}

Directory Contents

Path	Purpose
agentsview.db	Primary SQLite database holding all tables and indexes
config.yaml	JSON-compatible configuration persisted from command-line flags
agents/	Per-agent subdirectories (e.g., openai/, anthropic/, claude/) containing raw session files for the sync engine
sessions/	Legacy flat session storage (optional, newer releases use per-agent folders)
tmp/	Temporary files for the file-watcher and sync engine (cleaned on shutdown)

All components read from the same AGENTSVIEW_DATA_DIR location, ensuring consistency across processes.

Database Schema Overview

Agentsview uses SQLite with FTS5 for full-text search by default, with an optional PostgreSQL backend for shared deployments. The core schema is identical for both backends. The canonical DDL is defined in internal/postgres/schema.go, with SQLite-specific implementations in internal/db/sqlite_schema.go.

Core Tables

The schema tracks AI conversations, token usage, tool calls, and security findings across ten primary tables.

sessions

Stores one row per AI conversation with comprehensive metadata for analytics and filtering.

Key columns: id (TEXT PRIMARY KEY), machine, project, agent, display_name, session_name, created_at, started_at, ended_at, deleted_at, message_count, user_message_count, parent_session_id, relationship_type, total_output_tokens, peak_context_tokens, is_automated, outcome, termination_status, cwd, git_branch, source_session_id, parser_malformed_lines, is_truncated

messages

Individual messages within a session, supporting thinking blocks, tool use, and token tracking.

Key columns: session_id (FK), ordinal (INT, composite PK with session_id), role, content, thinking_text, timestamp, has_thinking, has_tool_use, content_length, is_system, model, token_usage, context_tokens, output_tokens, source_type, source_uuid, is_sidechain, is_compact_boundary

usage_events

Token consumption records for cost tracking and analytics.

Key columns: id (BIGSERIAL/BIGINT), session_id (FK), message_ordinal, source, model, input_tokens, output_tokens, cost_usd, occurred_at, dedup_key

starred_sessions

User-curated quick-access sessions.

Key columns: session_id (PRIMARY KEY), created_at

pinned_messages

User annotations on specific messages with optional notes.

Key columns: id (BIGSERIAL), session_id (FK), message_id, ordinal, source_uuid, note, created_at

tool_calls

Records of function calls and external API invocations during conversations.

Key columns: id (BIGSERIAL), session_id (FK), tool_name, category, call_index, tool_use_id, input_json, skill_name, result_content_length, result_content, subagent_session_id, message_ordinal

tool_result_events

Granular events comprising tool call outputs (streaming results, status changes).

Key columns: id (BIGSERIAL), session_id (FK), tool_call_message_ordinal, call_index, tool_use_id, agent_id, subagent_session_id, source, status, content, timestamp, event_index

secret_findings

Security scan results for detected credentials or sensitive data.

Key columns: id (BIGINT GENERATED ALWAYS AS IDENTITY), session_id (FK), rule_name, confidence, location_kind, message_ordinal, call_index, event_index, match_start, match_end, match_index, redacted_match, rules_version, created_at

sync_metadata

Key-value store for schema migrations and classifier versioning.

Key columns: key (PRIMARY KEY), value (NOT NULL)

Key Indexes

The schema includes optimized indexes for common query patterns:

• idx_sessions_parent – on sessions(parent_session_id) (partial, non-null)
• idx_messages_velocity – composite on (session_id, ordinal, role, timestamp, content_length)
• idx_messages_content_trgm – trigram GIN index on messages.content (PostgreSQL only)
• idx_usage_events_session – on usage_events(session_id)
• idx_tool_calls_session_category – on tool_calls(session_id, category)
• idx_secret_findings_session – on secret_findings(session_id)

SQLite uses FTS5 and B-tree indexes to achieve parity with PostgreSQL's query capabilities.

Working with the Database

Opening the SQLite Store

import "go.kenn.io/agentsview/internal/db"

func openDB() (*sql.DB, error) {
    cfg, err := config.Load()
    if err != nil { return nil, err }
    return db.OpenSQLite(cfg.DataDir) // points to agentsview.db
}

Querying Sessions and Messages

func printSession(sessionID string) error {
    db, err := openDB()
    if err != nil { return err }

    var s db.Session
    err = db.QueryRow(
        `SELECT id, display_name, created_at FROM sessions WHERE id = ?`, 
        sessionID,
    ).Scan(&s.ID, &s.DisplayName, &s.CreatedAt)
    if err != nil { return err }

    fmt.Printf("Session %s – %s\n", s.ID, s.DisplayName)

    rows, err := db.Query(
        `SELECT ordinal, role, content FROM messages 
         WHERE session_id = ? ORDER BY ordinal`, 
        sessionID,
    )
    if err != nil { return err }
    defer rows.Close()

    for rows.Next() {
        var ord int
        var role, content string
        rows.Scan(&ord, &role, &content)
        fmt.Printf("%d [%s] %s\n", ord, role, content)
    }
    return rows.Err()
}

Recording Tool Calls

func addToolCall(tx *sql.Tx, sessionID string, ordinal int, name, category string) error {
    _, err := tx.Exec(`
        INSERT INTO tool_calls (session_id, tool_name, category, call_index,
                                message_ordinal, tool_use_id)
        VALUES (?, ?, ?, 0, ?, '')
    `, sessionID, name, category, ordinal)
    return err
}

Schema Migrations and Extensibility

The schema is idempotent and auto-migrating. New columns (such as is_automated and has_total_output_tokens in recent versions) are added automatically on startup without breaking existing data. The sync_metadata table tracks migration state and classifier hashes.

Summary

• Data directory defaults to $HOME/.agentsview, override with AGENTSVIEW_DATA_DIR
• SQLite file agentsview.db contains all tables with FTS5 search support
• Schema is identical across SQLite and PostgreSQL backends
• Ten core tables track sessions, messages, usage, tool calls, starred items, pinned notes, security findings, and sync metadata
• Comprehensive indexes support fast filtering, full-text search, and analytics queries
• Auto-migration ensures safe schema evolution across versions

Frequently Asked Questions

How do I change the data directory location in Agentsview?

Set the AGENTSVIEW_DATA_DIR environment variable before starting the application. The legacy AGENT_VIEWER_DATA_DIR variable is also supported for backward compatibility. The directory is created automatically on first startup if it does not exist.

Can I use PostgreSQL instead of SQLite with Agentsview?

Yes. Agentsview supports PostgreSQL as an optional shared backend. The schema in internal/postgres/schema.go is identical to the SQLite implementation, ensuring consistent behavior. Both backends use the same table structures, indexes, and migration logic.

Where are raw session files stored before import?

Raw session files are stored in the agents/ subdirectory, organized by agent type (e.g., agents/openai/, agents/anthropic/). The sync engine parses these files and imports them into the database. The tmp/ directory holds transient files during processing and is cleaned on shutdown.

How does Agentsview handle schema upgrades?

Schema migrations run automatically on startup. The sync_metadata table tracks migration state. The schema is designed to be idempotent, meaning new columns can be added safely without affecting existing data. Tests in internal/postgres/schema_test.go verify schema consistency.

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