How to Set Up a PostgreSQL Backend for Team Sharing with AgentsView Using `pg push` and `pg serve`

AgentsView provides two CLI commands—pg push to migrate local SQLite session data to PostgreSQL and pg serve to run a read-only HTTP server—enabling teams to share AI interaction history from a centralized database.

The kenn-io/agentsview repository ships with native PostgreSQL support that moves beyond local SQLite storage. By leveraging the pg push and pg serve commands implemented in cmd/agentsview/pg.go, you can synchronize session data to a shared database and expose it through a secure, read-only API. This guide walks through the complete configuration using the actual source implementation.

Prerequisites: Provision the PostgreSQL Instance

Before running AgentsView commands, create a dedicated database and user. The connection logic in internal/postgres/connect.go expects a standard PostgreSQL connection string with TLS support.

Run these SQL commands as a superuser:

CREATE DATABASE agentsview;
CREATE USER av_user WITH PASSWORD 'strong-password';
GRANT ALL PRIVILEGES ON DATABASE agentsview TO av_user;

For local testing, use Docker:

docker run -d --name av-pg \
  -e POSTGRES_DB=agentsview \
  -e POSTGRES_USER=av_user \
  -e POSTGRES_PASSWORD=strong-password \
  -p 5432:5432 postgres:16

Configure the AgentsView PostgreSQL Connection

The configuration system in internal/config/config.go loads settings via Config.LoadMinimal() from environment variables or the TOML file at ~/.agentsview/config.toml.

Using the Config File (TOML)

Create or edit ~/.agentsview/config.toml:

[pg]
url = "postgres://av_user:strong-password@localhost:5432/agentsview"
schema = "agentsview"
machine_name = "host-A"

Using Environment Variables

Environment variables override file settings. The parsing logic in Config.loadEnv() (lines 98–104) maps these prefixes:

export AGENTSVIEW_PG_URL="postgres://av_user:strong-password@localhost:5432/agentsview"
export AGENTSVIEW_PG_SCHEMA="agentsview"
export AGENTSVIEW_PG_MACHINE="host-A"

Initialize the Schema with pg push

The pg push command orchestrates migration from local SQLite to PostgreSQL. In cmd/agentsview/pg.go, the runPGPush function (lines 32–40) loads the configuration, opens the local SQLite database, and initializes the PostgreSQL schema via postgres.New().

Execute a full push to create tables and migrate all data:

agentsview pg push --full

This calls ps.EnsureSchema(ctx) defined in internal/postgres/schema.go, which creates the sessions, messages, and analytics tables. Subsequent pushes use incremental sync based on per-machine watermarks tracked in internal/postgres/push.go:

agentsview pg push

Serve the Shared Database with pg serve

Once data resides in PostgreSQL, use pg serve to start a read-only HTTP server. The runPGServe function (lines 24–36 in cmd/agentsview/pg.go) validates schema compatibility using postgres.CheckSchemaCompat, then constructs a read-only store using postgres.NewStore() from internal/postgres/store.go.

Start the server with authentication:

agentsview pg serve --port 8080 --require-auth

The server launches via server.New in internal/server/server.go with the ReadOnly: true flag, disabling all write endpoints. The --require-auth flag triggers Config.EnsureAuthToken to generate a bearer token displayed on startup.

Query the API using the token:

curl -H "Authorization: Bearer <token>" http://localhost:8080/api/v1/sessions

Architecture Overview

The AgentsView PostgreSQL backend follows a clear separation between write and read paths:

• Data Ingestion: Local SQLite → pg push → PostgreSQL (internal/postgres/push.go)
• Data Serving: PostgreSQL → pg serve → HTTP API (internal/server/server.go)

Both commands share the PGConfig struct defined in internal/config/config.go, ensuring consistent connection parameters. The postgres.New() constructor unifies schema management, while EnsureSchema guarantees idempotent table creation across both push and serve operations.

Summary

• Provision a PostgreSQL database and user with appropriate privileges.
• Configure the connection via ~/.agentsview/config.toml or AGENTSVIEW_PG_* environment variables handled by internal/config/config.go.
• Push local SQLite data using agentsview pg push, which triggers ps.EnsureSchema in internal/postgres/schema.go and incremental sync via internal/postgres/push.go.
• Serve the shared database using agentsview pg serve, which creates a read-only store in internal/postgres/store.go and exposes it through the HTTP server in internal/server/server.go.
• Secure access using --require-auth to generate bearer tokens via Config.EnsureAuthToken.

Frequently Asked Questions

What is the difference between pg push and pg serve?

pg push is a write operation that migrates data from the local SQLite database to PostgreSQL using the logic in internal/postgres/push.go. It creates or updates the schema via ps.EnsureSchema and supports incremental updates. pg serve is a read-only HTTP server that connects to the same PostgreSQL database using postgres.NewStore(), allowing team members to query shared session data without write access.

Does pg serve automatically create the database schema?

No. While pg serve validates schema compatibility using postgres.CheckSchemaCompat, it expects the tables to exist. You must run agentsview pg push --full at least once to execute ps.EnsureSchema from internal/postgres/schema.go and create the necessary tables.

How does the incremental push mechanism work?

The ps.Push function in internal/postgres/push.go tracks a per-machine watermark stored in PostgreSQL. When you run agentsview pg push without the --full flag, it only synchronizes sessions and messages newer than the last recorded timestamp for that specific machine_name. Use --full to force a complete re-synchronization after schema changes.

Can multiple team members run pg serve simultaneously?

Yes. Because pg serve operates in read-only mode with the ReadOnly: true flag passed to server.New in internal/server/server.go, multiple instances can safely connect to the same PostgreSQL database concurrently without write conflicts. Each instance independently authenticates via the bearer token generated by Config.EnsureAuthToken.

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