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

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. 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 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.

Prerequisites

Before creating the mirror, install the DuckDB CLI:

# 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:

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

This command, implemented in 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:

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:

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

Python with Pandas:

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:

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 and internal/db/messages.go, you can safely overwrite the existing mirror without downtime.

Summary

• AgentsView stores operational data in SQLite (internal/db/db.go), but analytical queries require a columnar format.
• The duckdb sub-command (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 and copied using the query helpers found in internal/db/sessions.go and 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.

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