# Understanding the Storage Backend Abstraction in agentsview: SQLite, PostgreSQL, and DuckDB

> Explore the agentsview storage backend abstraction, unifying SQLite PostgreSQL and DuckDB with a single Go interface. Run identical code across local remote and analytical storage.

- Repository: [Kenn Software/agentsview](https://github.com/kenn-io/agentsview)
- Tags: deep-dive
- Published: 2026-07-04

---

**The agentsview project unifies SQLite, PostgreSQL, and DuckDB behind a single `db.Store` Go interface, allowing the HTTP server to run identical code paths regardless of whether the underlying storage is local, remote, or analytical.**

The storage backend abstraction in `kenn-io/agentsview` enables seamless swapping between three distinct database engines without modifying application logic. By defining a common contract in [`internal/db/store.go`](https://github.com/kenn-io/agentsview/blob/main/internal/db/store.go), the codebase supports local development with SQLite, multi-node deployments with PostgreSQL, and analytical querying with DuckDB through a single, compile-time verified interface.

## The db.Store Interface Contract

At the heart of the abstraction lies the **`db.Store`** interface defined in [`internal/db/store.go`](https://github.com/kenn-io/agentsview/blob/main/internal/db/store.go). This contract specifies every query and mutation method the HTTP server requires, ensuring that SQLite, PostgreSQL, and DuckDB implementations expose an identical API surface.

### Interface Definition

The interface methods cover pagination helpers, session retrieval, full-text search, and analytics:

```go
type Store interface {
    // Pagination helpers
    SetCursorSecret(secret []byte)
    EncodeCursor(c SessionCursor) string
    DecodeCursor(s string) (SessionCursor, error)

    // Session retrieval & search
    ListSessions(ctx context.Context, f SessionFilter) (SessionPage, error)
    GetSession(ctx context.Context, id string) (*Session, error)
    Search(ctx context.Context, f SearchFilter) (SearchPage, error)

    // Write-only operations – only SQLite implements them.
    UpsertSession(s Session) error
    ReplaceSessionMessages(sessionID string, msgs []Message) error
    WriteSessionBatchAtomic(...)

    // ReadOnly tells the server whether writes are allowed.
    ReadOnly() bool
}

```

The comment block above the interface (lines 17-22) explicitly states that *"Any new server-visible query or mutation belongs here, not only on the SQLite DB type, so PostgreSQL and DuckDB fail compilation until they implement the same capability surface."*

### Compile-Time Safety Guarantees

To enforce this contract, each implementation includes a compile-time assertion at the bottom of its respective file. For example, in [`internal/db/store.go`](https://github.com/kenn-io/agentsview/blob/main/internal/db/store.go):

```go
var _ Store = (*DB)(nil) // compile-time check

```

Similar assertions exist in [`internal/postgres/store.go`](https://github.com/kenn-io/agentsview/blob/main/internal/postgres/store.go) and [`internal/duckdb/store.go`](https://github.com/kenn-io/agentsview/blob/main/internal/duckdb/store.go). These lines guarantee that any method added to `Store` must be implemented by all three backends before the code will compile, preventing runtime errors due to missing functionality.

## SQLite Implementation (Read-Write)

The **SQLite** backend serves as the default on-disk store and provides full read-write capabilities. Located in [`internal/db/db.go`](https://github.com/kenn-io/agentsview/blob/main/internal/db/db.go), the concrete `*DB` type maintains both a writer and a reader pool using atomic pointers to support concurrent reads during background synchronization.

### Architecture with Dual Connection Pools

The `DB` struct manages separate atomic pointers for read and write operations:

```go
type DB struct {
    path      string
    writer    atomic.Pointer[sql.DB]
    reader    atomic.Pointer[sql.DB]
    readOnly  bool   // false for local SQLite
}

```

This design allows the server to serve concurrent reads while a background process replaces the writer connection during sync operations. The `ReadOnly()` method returns `false`, signaling to the HTTP layer that mutation operations are permitted.

### Write Operations Support

Unlike the other backends, SQLite implements the full interface including write methods such as `UpsertSession()`, `ReplaceSessionMessages()`, and `WriteSessionBatchAtomic()`. These methods execute against the writer pool, enabling the server to persist session data, messages, and analytics directly to the local database file.

## PostgreSQL Implementation (Read-Only)

The **PostgreSQL** backend in [`internal/postgres/store.go`](https://github.com/kenn-io/agentsview/blob/main/internal/postgres/store.go) provides a read-only view of a shared remote database. This implementation enables multi-machine deployments where the HTTP server queries a central PostgreSQL instance while writes occur through a separate CLI push command.

### Read-Only Design Pattern

The `Store` struct wraps a standard `*sql.DB` connection pool configured for read-only access:

```go
type Store struct {
    db   *sql.DB
    // …
}

func (s *Store) ReadOnly() bool { return true }

func (s *Store) UpsertSession(s Session) error { 
    return db.ErrReadOnly 
}

```

All query methods (`ListSessions`, `Search`, `GetAnalyticsSummary`) translate requests to PostgreSQL-specific SQL and execute via the read-only connection. However, any call to write methods immediately returns the sentinel error `db.ErrReadOnly`, protecting the remote database from unintended mutations through the HTTP API.

### Push Synchronization Layer

Although the HTTP server cannot write directly to PostgreSQL, the repository includes a push synchronization layer in [`internal/postgres/push.go`](https://github.com/kenn-io/agentsview/blob/main/internal/postgres/push.go). This code runs exclusively within the CLI `pg push` command, reading data from a local SQLite instance and writing it to the remote PostgreSQL database. This architecture separates read-heavy serving workloads from write-heavy ingestion tasks.

## DuckDB Implementation (Local Mirror)

The **DuckDB** backend in [`internal/duckdb/store.go`](https://github.com/kenn-io/agentsview/blob/main/internal/duckdb/store.go) functions as a local analytical mirror of a remote PostgreSQL instance. Designed specifically for the `/api/v1/push/duckdb` endpoint, this implementation leverages DuckDB's columnar storage for fast analytical queries on mirrored data.

The `Store` struct holds a single `*sql.DB` connection to a local DuckDB file:

```go
type Store struct {
    duck *sql.DB
    // …
}

func (s *Store) ReadOnly() bool { return true }

```

Like PostgreSQL, this backend returns `db.ErrReadOnly` for all write operations and delegates read queries to the underlying DuckDB engine. The implementation also supports a Quack client for remote execution scenarios, abstracted behind the `queryDuckDBContext` helper function.

## Runtime Backend Selection

When the server starts in [`cmd/agentsview/main.go`](https://github.com/kenn-io/agentsview/blob/main/cmd/agentsview/main.go), it selects the concrete implementation based on configuration flags:

* **`--data-dir`** (default) → Instantiates SQLite via `db.NewDB()`
* **`--pg-url`** → Instantiates PostgreSQL via `postgres.NewStore()`
* **`--duckdb`** → Instantiates DuckDB via `duckdb.NewStore()`

Regardless of the selection, the startup code returns a value satisfying `db.Store`, which it injects into HTTP handlers. For example, the session list handler in [`internal/server/sessions.go`](https://github.com/kenn-io/agentsview/blob/main/internal/server/sessions.go) simply calls:

```go
page, err := store.ListSessions(ctx, filter)

```

The underlying implementation determines whether the query executes against SQLite, PostgreSQL, or DuckDB, allowing the handler to remain agnostic to storage specifics.

## Summary

- **Single interface, multiple backends**: The `db.Store` interface in [`internal/db/store.go`](https://github.com/kenn-io/agentsview/blob/main/internal/db/store.go) unifies SQLite, PostgreSQL, and DuckDB behind a common contract, ensuring compile-time parity across all implementations.
- **Capability-based routing**: SQLite provides full read-write access for local development, while PostgreSQL and DuckDB operate as read-only stores for distributed and analytical workloads respectively.
- **Runtime flexibility**: Configuration flags select the backend at startup, with the HTTP layer consuming only the abstract interface rather than concrete types.
- **Safety mechanisms**: Compile-time assertions and the `ReadOnly()` method prevent runtime mismatches between write operations and read-only backends.

## Frequently Asked Questions

### How does agentsview decide which storage backend to use?

The application checks command-line flags during startup in [`cmd/agentsview/main.go`](https://github.com/kenn-io/agentsview/blob/main/cmd/agentsview/main.go). If `--pg-url` is provided, it instantiates the PostgreSQL store; if `--duckdb` is provided, it uses DuckDB; otherwise, it defaults to SQLite using the path specified by `--data-dir` or a default location. This selection happens once at initialization, with the chosen store injected into all HTTP handlers.

### Why is the PostgreSQL backend read-only in the server?

The PostgreSQL implementation intentionally restricts writes to prevent race conditions in multi-node deployments. Writes occur through the dedicated `pg push` CLI command rather than the HTTP server, ensuring that data ingestion happens through a controlled, single-writer path. This design separates read-heavy serving workloads from write-heavy synchronization tasks, improving scalability and data consistency.

### What happens if I call a write method on a read-only backend?

The method returns `db.ErrReadOnly`, a sentinel error defined in [`internal/db/store.go`](https://github.com/kenn-io/agentsview/blob/main/internal/db/store.go). Your application code should check for this error using `errors.Is(err, db.ErrReadOnly)` to handle read-only scenarios gracefully. This pattern allows the same handler code to run against any backend, with the specific implementation determining whether the operation succeeds or returns the read-only restriction.

### Can I use DuckDB as a primary writeable backend?

No, the DuckDB implementation in [`internal/duckdb/store.go`](https://github.com/kenn-io/agentsview/blob/main/internal/duckdb/store.go) is designed exclusively as a read-only mirror for analytical queries. Its `ReadOnly()` method returns `true`, and all write methods return `db.ErrReadOnly`. The intended use case involves mirroring data from a remote PostgreSQL instance via the `/api/v1/push/duckdb` endpoint, then running fast analytical queries against the local DuckDB file.