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

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

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:

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

Similar assertions exist in internal/postgres/store.go and 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, 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:

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

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

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, 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 simply calls:

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

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