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

> Learn when to use DuckDB mirroring in AgentsView for fast analytics on large archives, read-only sharing, or incremental sync. Understand its read-only copy and query capabilities.

- Repository: [Kenn Software/agentsview](https://github.com/kenn-io/agentsview)
- Tags: how-to-guide
- Published: 2026-06-12

---

**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)【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`](https://github.com/kenn-io/agentsview/blob/main/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`](https://github.com/kenn-io/agentsview/blob/main/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`](https://github.com/kenn-io/agentsview/blob/main/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`](https://github.com/kenn-io/agentsview/blob/main/cmd/agentsview/duckdb.go) or programmatically via the Go API.

### CLI Commands

```bash

# 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

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