When and How to Use DuckDB Mirroring in AgentsView: A Complete Guide

Use DuckDB mirroring in AgentsView when you need fast analytical queries on large session archives, read-only sharing with external tools, or incremental synchronization of specific projects. The feature creates a high-performance, read-only copy of your SQLite archive that supports columnar querying and can be served via HTTP or DuckDB Quack protocols.

AgentsView stores all session data in a local SQLite database by default. While this works for daily operations, DuckDB mirroring in AgentsView provides a specialized read-only replica optimized for analytical workloads and external consumption. This architecture lets you maintain the primary SQLite store while leveraging DuckDB's columnar engine for complex reporting and data sharing.

When to Use DuckDB Mirroring in AgentsView

You should enable DuckDB mirroring when you encounter these specific scenarios:

• Fast analytical queries – DuckDB’s columnar engine and native support for large-scale scans make it far quicker than SQLite for ad-hoc reporting or "export-my-sessions" use cases.
• Read-only sharing – The mirror can be served as a plain DuckDB file via agentsview duckdb serve or through a DuckDB Quack server (agentsview duckdb quack serve), allowing other tools to open the file in read-only mode without affecting the primary SQLite store.
• Incremental sync with project filtering – You can push only a subset of projects using --projects or --exclude-projects. The sync logic maintains a fingerprint-based watermark so subsequent pushes are incremental, avoiding full re-indexing every time.

How DuckDB Mirroring Functions

Push-Only Architecture

The mirroring workflow is push-only: a background job or explicit CLI command copies new or changed sessions from SQLite into DuckDB, never the reverse. This unidirectional flow ensures data integrity in the primary SQLite store while allowing the DuckDB replica to be treated as a disposable analytical cache.

The core implementation lives in internal/duckdb/sync.go【https://github.com/kenn-io/agentsview/blob/main/internal/duckdb/sync.go#L29-L34】, where the Sync struct manages the connection between the local SQLite database and the DuckDB mirror.

Fingerprint-Based Incremental Sync

To avoid transferring unchanged data, the system computes SHA-256 fingerprints for each session. In internal/duckdb/sync.go, the code calculates checksums of serialized fields, messages, usage events, secret findings, and pinned messages【https://github.com/kenn-io/agentsview/blob/main/internal/duckdb/sync.go#L101-108】【https://github.com/kenn-io/agentsview/blob/main/internal/duckdb/sync.go#L34-L38】.

These fingerprints are compared against the duckdb_last_push_boundary_state watermark stored in SQLite. If a session's fingerprint matches the previous state, the sync skips it, making subsequent runs significantly faster than the initial full push.

Project-Level Filtering

You can scope the mirror to specific projects using the SyncOptions struct, which supports Projects and ExcludeProjects fields. During synchronization, ListSessionsModifiedBetween consults these filters on the SQLite side, ensuring only relevant sessions enter the DuckDB pipeline【https://github.com/kenn-io/agentsview/blob/main/internal/duckdb/sync.go#L90-L92】.

The Synchronization Workflow

The push operation follows a precise eight-step sequence defined in internal/duckdb/sync.go:

1. Open the DuckDB file – duckdbsync.New(path, localDB, machine, opts) creates a Sync object that holds the DuckDB connection (s.duck) and the local SQLite DB (s.local)【https://github.com/kenn-io/agentsview/blob/main/internal/duckdb/sync.go#L73-L91】.

2. Ensure schema alignment – s.EnsureSchema(ctx) runs migrations defined in internal/duckdb/schema.go to guarantee that tables (sessions, messages, etc.) match the SQLite layout【https://github.com/kenn-io/agentsview/blob/main/internal/duckdb/schema.go】.

3. Determine push mode – The code checks for lastPushStateKey. If no watermark exists or the user forces --full, every session is transferred. Otherwise, it fetches only sessions modified after the last successful timestamp【https://github.com/kenn-io/agentsview/blob/main/internal/duckdb/sync.go#L71-L77】.

4. Filter by project – The SyncOptions struct filters sessions during the SQLite query phase, reducing the candidate set before fingerprint calculation.

5. Compute fingerprints – Each candidate session gets a SHA-256 checksum to detect changes since the last push.

6. Delete hard-deleted sessions – Rows removed from SQLite are purged from DuckDB via deleteHardDeletedMirrorSessions to maintain consistency.

7. Insert or replace data – Each session is written with dependent rows in a single transaction via pushSession. Afterward, pinned messages, starred sessions, and cross-session data are reconciled.

8. Persist the watermark – On success, the sync writes duckdb_last_push_at (global cutoff) and duckdb_last_push_boundary_state (fingerprints) back into SQLite, enabling the next incremental run【https://github.com/kenn-io/agentsview/blob/main/internal/duckdb/sync.go#L61-L63】【https://github.com/kenn-io/agentsview/blob/main/internal/duckdb/sync.go#L66-L73】.

CLI Commands and Programmatic API

You can interact with the mirror through the CLI implemented in cmd/agentsview/duckdb.go or programmatically via the Go API.

CLI Commands

# Push all sessions (initial full mirror)

agentsview duckdb push --full

# Incremental push of only changed sessions

agentsview duckdb push

# Push specific projects only

agentsview duckdb push --projects project-a,project-b

# Check mirror status and row counts

agentsview duckdb status

# Serve mirror as read-only HTTP API

agentsview duckdb serve

# Start DuckDB Quack server for external clients

agentsview duckdb quack serve --bind 0.0.0.0:8810

Go API Example

// Programmatic push from Go (mirrors SQLite → DuckDB)
import (
    "context"
    "log"
    duckdbsync "go.kenn.io/agentsview/internal/duckdb"
    "go.kenn.io/agentsview/internal/db"
)

func mirrorSQLiteToDuckDB(sqlitePath, duckPath, machine string) {
    // Open the primary SQLite DB
    localDB, err := db.Open(sqlitePath)
    if err != nil { log.Fatalf("open sqlite: %v", err) }
    defer localDB.Close()

    // Create a Sync instance (no project filter)
    syncer, err := duckdbsync.New(duckPath, localDB, machine, duckdbsync.SyncOptions{})
    if err != nil { log.Fatalf("new sync: %v", err) }
    defer syncer.Close()

    ctx := context.Background()
    if err := syncer.EnsureSchema(ctx); err != nil {
        log.Fatalf("ensure schema: %v", err)
    }

    // Full push (use false for incremental)
    result, err := syncer.Push(ctx, true, func(p duckdbsync.PushProgress) {
        log.Printf("pushed %d/%d sessions", p.SessionsDone, p.SessionsTotal)
    })
    if err != nil { log.Fatalf("push: %v", err) }
    log.Printf("finished: %d sessions, %d messages", result.SessionsPushed, result.MessagesPushed)
}

Key Source Files

File	Role
internal/duckdb/sync.go	Core push-only mirroring logic, fingerprint handling, and incremental sync implementation【https://github.com/kenn-io/agentsview/blob/main/internal/duckdb/sync.go】
internal/duckdb/store.go	Helper for opening DuckDB connections and configuring custom pricing/cursor secrets【https://github.com/kenn-io/agentsview/blob/main/internal/duckdb/store.go】
internal/duckdb/schema.go	DDL definitions for DuckDB tables and migration code used by EnsureSchema【https://github.com/kenn-io/agentsview/blob/main/internal/duckdb/schema.go】
cmd/agentsview/duckdb.go	CLI implementation of duckdb push, duckdb status, duckdb serve, and duckdb quack serve commands【https://github.com/kenn-io/agentsview/blob/main/cmd/agentsview/duckdb.go】
internal/config/config.go	Holds the DuckDBConfig struct supplying path, machine name, and project filters to the sync layer【https://github.com/kenn-io/agentsview/blob/main/internal/config/config.go】

Summary

• DuckDB mirroring in AgentsView creates a read-only, high-performance analytical replica of your SQLite session archive.
• The synchronization is push-only and unidirectional, ensuring the primary SQLite store remains the source of truth.
• Fingerprint-based incremental sync eliminates redundant data transfers by comparing SHA-256 checksums against stored watermarks.
• Project filtering via --projects and --exclude-projects flags lets you maintain lean mirrors containing only relevant data.
• The mirror can be served via HTTP (agentsview duckdb serve) or DuckDB Quack protocol (agentsview duckdb quack serve) for external tool integration.

Frequently Asked Questions

Is DuckDB mirroring bidirectional?

No. The mirroring workflow is strictly push-only, copying data from SQLite to DuckDB but never in reverse. This design protects the integrity of your primary SQLite store while allowing the DuckDB file to be treated as a disposable analytical cache that can be regenerated at any time.

How does incremental synchronization work?

The system calculates SHA-256 fingerprints for each session based on its serialized fields, messages, and metadata. These fingerprints are stored in SQLite as duckdb_last_push_boundary_state. During subsequent pushes, the code compares current fingerprints against this watermark, skipping unchanged sessions and only transferring new or modified data【https://github.com/kenn-io/agentsview/blob/main/internal/duckdb/sync.go#L34-L38】.

Can I filter which projects get mirrored?

Yes. Use the --projects or --exclude-projects flags with the duckview duckdb push command. The SyncOptions struct passes these filters to ListSessionsModifiedBetween, which scopes the SQLite query to only relevant projects before any fingerprint calculation occurs【https://github.com/kenn-io/agentsview/blob/main/internal/duckdb/sync.go#L90-L92】.

How do I serve the DuckDB mirror to external tools?

AgentsView provides two serving modes. The agentsview duckdb serve command exposes the mirror through the standard AgentsView HTTP API, while agentsview duckdb quack serve starts a DuckDB Quack server that allows external DuckDB clients to connect directly via the DuckDB wire protocol. Both methods provide read-only access, ensuring the primary SQLite store remains unaffected.

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