# How to Set Up a DuckDB Mirror for Read-Only Analytics Serving with AgentsView

> Learn to set up a DuckDB mirror for read-only analytics. Export SQLite data to DuckDB and serve remotely for BI tools. Follow our easy guide.

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

---

**To set up a DuckDB mirror for read-only analytics serving with AgentsView, use the `duckdb` sub-command to export your SQLite session data to a columnar DuckDB file, then optionally serve it via HTTP for remote BI tools.**

AgentsView persists all session, message, and usage data in a local SQLite database defined in [`internal/db/db.go`](https://github.com/kenn-io/agentsview/blob/main/internal/db/db.go). While SQLite excels at transactional workloads, analytical queries with heavy aggregations perform better in a columnar format. The `duckdb` sub-command implemented in [`cmd/agentsview/duckdb.go`](https://github.com/kenn-io/agentsview/blob/main/cmd/agentsview/duckdb.go) creates a read-only mirror of your production data, enabling high-performance analytics without risking your primary store.

## Why Create a DuckDB Analytics Mirror?

### Analytical Performance

**DuckDB** is an in-process column-store database optimized for OLAP workloads. Complex `GROUP BY` operations and window functions run significantly faster on DuckDB's compressed columnar format than on SQLite's row-oriented storage.

### Read-Only Safety Guarantee

The mirror operates in **read-only mode** using `PRAGMA query_only = true;`, ensuring that ad-hoc exploration and third-party BI tools cannot accidentally modify or corrupt your source data in [`internal/db/db.go`](https://github.com/kenn-io/agentsview/blob/main/internal/db/db.go).

## Prerequisites

Before creating the mirror, install the DuckDB CLI:

```bash

# Linux/macOS example

curl -L https://github.com/duckdb/duckdb/releases/download/v1.1.2/duckdb_cli-linux-amd64.zip -o duckdb.zip
unzip duckdb.zip && sudo mv duckdb /usr/local/bin/

```

## Generating the DuckDB Mirror

Execute the `duckdb` sub-command to clone your SQLite data into a DuckDB file:

```bash
agentsview duckdb \
  --sqlite-path "$AGENTSVIEW_DATA_DIR/agentsview.sqlite" \
  --duckdb-path "/var/agentsview/analytics.duckdb"

```

This command, implemented in [`cmd/agentsview/duckdb.go`](https://github.com/kenn-io/agentsview/blob/main/cmd/agentsview/duckdb.go), performs the following:

- Opens the source SQLite database (the same file used by the AgentsView server)
- Creates a new DuckDB file at the specified path
- Copies the **sessions**, **messages**, **insights**, **pricing**, and **usage** tables using efficient `INSERT ... SELECT` statements
- Applies `PRAGMA query_only = true;` to enforce read-only access

## Serving the Mirror for Remote Access

To expose the DuckDB file over HTTP for containerized pipelines or remote teams, append the `--serve` flag:

```bash
agentsview duckdb \
  --duckdb-path "/var/agentsview/analytics.duckdb" \
  --serve --listen 0.0.0.0:8081

```

The embedded server streams the file with `Content-Type: application/octet-stream`, allowing clients to download the latest analytics snapshot without direct filesystem access.

## Querying Your Analytics Data

Once created, query the mirror using standard DuckDB syntax.

**Command-line analysis:**

```bash
duckdb /var/agentsview/analytics.duckdb \
  "SELECT agent, COUNT(*) as session_count 
   FROM sessions 
   GROUP BY agent 
   ORDER BY session_count DESC;"

```

**Python with Pandas:**

```python
import duckdb
import pandas as pd

con = duckdb.connect("/var/agentsview/analytics.duckdb")
df = con.sql("""
    SELECT m.role, COUNT(*) as message_count
    FROM messages m
    GROUP BY m.role
    ORDER BY message_count DESC
""").df()
print(df)

```

**BI Integration:**

Tools like Metabase or Apache Superset can connect directly to the local DuckDB file as a data source, enabling real-time dashboards without additional ETL pipelines.

## Automating Mirror Refreshes

The mirror represents a point-in-time snapshot. To keep analytics current, schedule periodic rebuilds via cron:

```cron
0 */6 * * * /usr/local/bin/agentsview duckdb \
  --sqlite-path "$AGENTSVIEW_DATA_DIR/agentsview.sqlite" \
  --duckdb-path "/var/agentsview/analytics.duckdb"

```

Since the process rebuilds the file from scratch using the schema definitions in [`internal/db/sessions.go`](https://github.com/kenn-io/agentsview/blob/main/internal/db/sessions.go) and [`internal/db/messages.go`](https://github.com/kenn-io/agentsview/blob/main/internal/db/messages.go), you can safely overwrite the existing mirror without downtime.

## Summary

- **AgentsView** stores operational data in SQLite ([`internal/db/db.go`](https://github.com/kenn-io/agentsview/blob/main/internal/db/db.go)), but analytical queries require a columnar format.
- The **`duckdb` sub-command** ([`cmd/agentsview/duckdb.go`](https://github.com/kenn-io/agentsview/blob/main/cmd/agentsview/duckdb.go)) creates a read-only mirror containing sessions, messages, insights, pricing, and usage tables.
- **Read-only enforcement** via `PRAGMA query_only = true;` protects source data from accidental modifications.
- Optional **HTTP serving** (`--serve --listen`) enables remote access for distributed teams.
- **Automated refresh** via cron ensures analytics reflect current production state without manual intervention.

## Frequently Asked Questions

### What tables are included in the DuckDB mirror?

The mirror includes all analytical tables: **sessions**, **messages**, **insights**, **pricing**, and **usage**. These are defined in [`internal/db/db.go`](https://github.com/kenn-io/agentsview/blob/main/internal/db/db.go) and copied using the query helpers found in [`internal/db/sessions.go`](https://github.com/kenn-io/agentsview/blob/main/internal/db/sessions.go) and [`internal/db/messages.go`](https://github.com/kenn-io/agentsview/blob/main/internal/db/messages.go) to ensure schema parity.

### Can I write data back to the DuckDB mirror?

No. The mirror is explicitly configured as read-only using `PRAGMA query_only = true;` immediately after creation. This safety measure prevents analytical tools from corrupting the data or creating schema drift from the source SQLite database.

### How do I update the mirror with new data?

The mirror is a static snapshot. To update it, re-run the `agentsview duckdb` command with the same `--duckdb-path` argument. The command will overwrite the existing file with a fresh copy of the current SQLite data. Schedule this via cron (e.g., every 6 hours) for automated synchronization.

### Is the HTTP server suitable for production use?

The built-in server (`--serve`) is designed for internal analytics distribution and development environments. It serves the raw DuckDB file as a static binary stream. For high-traffic production scenarios, consider placing the file on object storage (S3) or a dedicated file server behind a reverse proxy.