# Understanding the Data Directory Structure and Database Schema in Agentsview

> Explore the Agentsview data directory and database schema. Learn how Agentsview uses a SQLite file to store sessions, messages, tool calls, and usage analytics in normalized tables.

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

---

**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`](https://github.com/kenn-io/agentsview/blob/main/internal/config/config.go), the resolution logic is:

```go
// 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`](https://github.com/kenn-io/agentsview/blob/main/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`](https://github.com/kenn-io/agentsview/blob/main/internal/postgres/schema.go), with SQLite-specific implementations in [`internal/db/sqlite_schema.go`](https://github.com/kenn-io/agentsview/blob/main/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

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

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

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