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

> Learn to set up a PostgreSQL backend for team sharing with AgentsView using pg push and pg serve. Migrate SQLite data and serve interactions via HTTP for seamless AI history sharing.

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

---

**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`](https://github.com/kenn-io/agentsview/blob/main/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`](https://github.com/kenn-io/agentsview/blob/main/internal/postgres/connect.go) expects a standard PostgreSQL connection string with TLS support.

Run these SQL commands as a superuser:

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

```bash
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`](https://github.com/kenn-io/agentsview/blob/main/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`:

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

```bash
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`](https://github.com/kenn-io/agentsview/blob/main/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:

```bash
agentsview pg push --full

```

This calls `ps.EnsureSchema(ctx)` defined in [`internal/postgres/schema.go`](https://github.com/kenn-io/agentsview/blob/main/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`](https://github.com/kenn-io/agentsview/blob/main/internal/postgres/push.go):

```bash
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`](https://github.com/kenn-io/agentsview/blob/main/cmd/agentsview/pg.go)) validates schema compatibility using `postgres.CheckSchemaCompat`, then constructs a read-only store using `postgres.NewStore()` from [`internal/postgres/store.go`](https://github.com/kenn-io/agentsview/blob/main/internal/postgres/store.go).

Start the server with authentication:

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

```

The server launches via `server.New` in [`internal/server/server.go`](https://github.com/kenn-io/agentsview/blob/main/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:

```bash
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`](https://github.com/kenn-io/agentsview/blob/main/internal/postgres/push.go))
- **Data Serving**: PostgreSQL → `pg serve` → HTTP API ([`internal/server/server.go`](https://github.com/kenn-io/agentsview/blob/main/internal/server/server.go))

Both commands share the `PGConfig` struct defined in [`internal/config/config.go`](https://github.com/kenn-io/agentsview/blob/main/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`](https://github.com/kenn-io/agentsview/blob/main/internal/config/config.go).
- **Push** local SQLite data using `agentsview pg push`, which triggers `ps.EnsureSchema` in [`internal/postgres/schema.go`](https://github.com/kenn-io/agentsview/blob/main/internal/postgres/schema.go) and incremental sync via [`internal/postgres/push.go`](https://github.com/kenn-io/agentsview/blob/main/internal/postgres/push.go).
- **Serve** the shared database using `agentsview pg serve`, which creates a read-only store in [`internal/postgres/store.go`](https://github.com/kenn-io/agentsview/blob/main/internal/postgres/store.go) and exposes it through the HTTP server in [`internal/server/server.go`](https://github.com/kenn-io/agentsview/blob/main/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`](https://github.com/kenn-io/agentsview/blob/main/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`](https://github.com/kenn-io/agentsview/blob/main/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`](https://github.com/kenn-io/agentsview/blob/main/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`](https://github.com/kenn-io/agentsview/blob/main/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`.