PostgreSQL Sync

AgentsView stores sessions locally in SQLite by default. PostgreSQL sync lets you push sessions from one or more machines into a shared PostgreSQL database, keep that database current with an optional auto-push watcher or OS service, then serve a read-only web UI from it — useful for team dashboards or multi-machine setups.

The PostgreSQL usage optimization baseline and its architecture boundary are recorded in docs/internal/pg-usage-native-optimization.md.

The sync direction is one-way: SQLite to PostgreSQL. Each machine pushes its own sessions; pg serve reads from the shared database. The resulting UI includes the session browser, analytics dashboard, search, and, as of 0.23.0, the Usage dashboard as well.

Hosted raw processing¶

For uploads that the server parses itself, provision a schema and tenant with agentsview pg hosted-provision <owner-target>, then serve with a separate restricted runtime role. The selected PG target needs raw_tenant, an explicit schema and raw_derivation = true; the server also requires authentication, a stable cursor secret and a supported Linux/cgo sandbox. Runtime startup checks schema protections and grants but never runs hosted migrations.

See Hosted Raw Sync for the configuration, least-privilege grants and platform limits. Use agentsview pg raw-reparse <runtime-target> --run-id <id> --batch-size 64 for bounded parser-version rollouts. To stop the worker, set raw_derivation = false and keep raw_tenant, authentication and the cursor secret. This preserves hosted public reads and raw custody. It does not restore pg push access to the owned schema.

Quick Start¶

1. Configure PostgreSQL¶

Add a [pg] section to ~/.agentsview/config.toml:

local_machine_name = "Laptop"

[pg]
url = "postgres://user:pass@host:5432/dbname?sslmode=require"

Sessions retain their source installation ID. local_machine_name supplies the display label after a daemon restart and the next push. The optional [pg].machine_name defaults to the installation ID and only supplies a key for legacy local rows; it does not rename recorded session keys.

For multiple PostgreSQL destinations, use named [pg.NAME] blocks and default_pg instead of the legacy single [pg] block. Named target names are normalized case-insensitively, and all, local, plus the legacy [pg] field names url, schema, machine_name, allow_insecure, projects, and exclude_projects, raw_tenant, raw_derivation, raw_poll_seconds, raw_attempt_seconds, and raw_max_attempts are unavailable as [pg.NAME] names.

2. Push Sessions¶

agentsview pg push

This one-shot command syncs all local sessions, messages, and tool calls to PostgreSQL. The schema is created automatically on first push.

To keep PostgreSQL current automatically, run the foreground watcher:

agentsview pg push --watch

Or install it as a per-user background service on macOS or Linux:

agentsview pg service install

3. Serve the Dashboard¶

agentsview pg serve

Opens the read-only web UI at http://127.0.0.1:8080, backed entirely by PostgreSQL. It does not use local SQLite or file watching, and its session UI and APIs do not accept session uploads. A writable deployment can also expose the separate hosted raw-sync control plane.

Commands¶

agentsview pg push¶

Sync sessions from the local SQLite database to PostgreSQL.

agentsview pg push [target] [flags]

Without --watch, push is on-demand — run it whenever you want to sync. With --watch, the command stays in the foreground and keeps pushing until interrupted.

When no target is passed, pg push uses the effective default target. Pass one named target explicitly to push just that destination, or use --all to fan out across every configured target. --all --watch is rejected.

What happens on push:

1. Runs a local sync to pick up any new or modified session files
2. Compares local sessions against the PostgreSQL watermark to find what changed since the last push
3. Upserts sessions, messages, tool calls, and (as of 0.30.0) curation metadata in batches of 50
4. Advances the watermark timestamp on success

Incremental pushes use a two-layer fingerprint to skip unchanged sessions: first, session metadata fields (project, agent, timestamps, message counts) are compared; then, per- session message statistics (count, content length sum/max/min, system message ordinals, tool call counts) are checked against PostgreSQL. Use --full to bypass both layers and re-push everything — for example, after a schema reset or when message content was rewritten in place.

Every push sends the most recently active sessions first, so recent activity reaches PostgreSQL before older history. A full push that stops partway, for example because the process restarted, resumes on the next push: sessions it already pushed are skipped unless PostgreSQL lost them. The summary says resuming an unfinished full push and counts the skipped sessions. Progress is saved at most every 15 seconds, so a restart repeats at most that much work. The push starts over instead when --full is passed or when the target, the local archive, or the data format changed since it began. When --full discards an unfinished full push, the summary says discarded an unfinished full push. A full push that reaches the end with session errors resumes only while something else still requires a full push. Otherwise the next push is incremental and retries the failed sessions.

After an archive rebuild, the next push sends all sessions again. Once every session publishes successfully, a full push also removes PostgreSQL sessions that this archive owns but no longer contains. Project filters limit this cleanup to the selected projects. Sessions owned by another archive, or whose owner is unknown, remain untouched. An interrupted push resumes before cleanup. Codex thread trash state remains available for restoring or permanently deleting its pages.

If any sessions fail to push, the watermark is not advanced so they are retried on the next run. The exit code is 1 when any errors occur, 0 otherwise.

When a daemon handles an incremental push against a current archive, an incomplete local ingestion pass still allows committed sessions to be copied. The daemon logs the incomplete ingestion pass, while the command returns the mirror push result, including any row errors or deferred vectors. Local sync retries failed sources independently; a completed mirror push does not trigger an immediate mirror retry just because ingestion was incomplete. A failed full resync still blocks the push. Watcher batches keep their acknowledgement rules; the startup and periodic unscoped pushes allow healthy archived sessions to catch up.

Automatic Push Watcher¶

As of 0.32.0, agentsview pg push --watch runs a long-lived auto-push daemon in the foreground:

agentsview pg push --watch
agentsview pg push --watch --debounce 1m
agentsview pg push --watch --interval 5m

The watcher performs one initial local sync plus PostgreSQL push, then pushes again after session-directory changes settle for the debounce window. Ordinary changes carry a bounded set of changed paths to the daemon, so a one-session edit does not rediscover the full local archive. Watcher overflow, lost events, and ambiguous renames promote to authoritative reconciliation of the currently available provider roots. The interval acts as a floor: even if filesystem events are missed or a root cannot be watched because of OS watch limits, the next interval push catches up.

Operational details:

• Only one watcher can run per AgentsView data directory; a runtime lock prevents competing pushes from racing watermarks.
• PostgreSQL connections are opened lazily and reset after errors, so a transiently unavailable database is retried on the next trigger instead of crashing the watcher.
• On shutdown (Ctrl+C, SIGTERM), the watcher attempts one bounded final flush.
• Logs are written to pg-watch.log under the AgentsView data directory.
• The watcher uses the selected PostgreSQL target, or the default_pg target when no name is passed, along with the same machine name, project filters, classifier settings, and result_content_blocked_categories behavior as one-shot pg push.
• The watcher publishes an identity-bound local lifecycle record. Native memory hooks can wake that exact owner with agentsview memory session-start --mode hosted-contributor; the wake enters the normal debounce loop and does not bypass its credentials or scheduling. Restart a watcher created by an older binary once before using this hook. Lifecycle wake delivery is currently available on macOS and Linux; Windows watchers continue to use their normal filesystem-event and interval cadence.

Project Filtering¶

By default, pg push syncs all projects. Use project filters to push a subset:

# Push only these projects
agentsview pg push --projects alpha,beta

# Push everything except this project
agentsview pg push --exclude-projects scratch

# Ignore config-file filters for this run
agentsview pg push --all-projects

--projects and --exclude-projects are mutually exclusive. --all-projects cannot be combined with either.

Project filters can also be set in config.toml so you don't need to pass them on every run:

[pg]
url = "postgres://..."
projects = ["alpha", "beta"]
# or: exclude_projects = ["scratch"]

CLI flags override config values. Use agentsview projects to list available project names.

Filtered pushes keep their own local push watermark for each target/filter set. For example, repeated agentsview pg push --projects alpha,beta runs use a different watermark from unfiltered pushes and from agentsview pg push --projects gamma. This keeps allow-list pushes incremental without advancing the unfiltered/global cursor.

After upgrading from an older version, the first filtered push for a given project set may still scan the matching local sessions once to seed that scoped watermark. Later pushes with the same filter set use the scoped watermark.

Curation Metadata¶

As of 0.30.0, pg push also synchronizes two pieces of per-user curation state alongside session content:

Stars are keyed by session ID and overwrite cleanly across machines. Pins are reconciled by source_uuid — a stable identifier derived from the underlying message — so a pin survives a re-parse that shifts message ordinals. Without this, pins would silently drift to the wrong message after any session resync.

A pin or note made in a PostgreSQL-served UI lives only in PostgreSQL. After a Claude.ai zip replacement or Sync branch switch, a push preserves it when the message's role, text, and occurrence rank still match. If the message can't be matched, the push drops the pin and note. SQLite's Trash copy doesn't hold them.

Curation tables are populated by the same pg push run; no separate command or flag is required. The agentsview secrets findings also push through this codepath, with the same parity guarantees as session content (secret_findings table, per-session secret_leak_count, and the has-secret list filter).

Vector Push¶

When [vector] is enabled locally, pg push runs a vector phase after the session and message phases, copying the machine's active embedding generation from vectors.db into pgvector so a shared PostgreSQL deployment can answer --semantic/--hybrid. Only changed sessions are re-sent. Skip the phase for one run with --no-vectors, or disable it persistently with push_vectors = false under [pg]. Databases without pgvector (for example CockroachDB) skip the phase and keep syncing session content. See semantic search: PostgreSQL for the full push, serve, and maintenance workflow.

pg vectors list and pg vectors drop <id> inspect and remove pushed generations.

Build and push embeddings in a container¶

pg push --embed (and pg push --watch --embed) syncs the local archive, pushes sessions, and builds pending embeddings in one process. A container that runs it needs no shell wrapper:

• With [vector] in its config.toml, it builds with that recipe.
• Without [vector], it adopts the recipe a configured workstation published to PostgreSQL. Set AGENTSVIEW_EMBEDDINGS_ENDPOINT (and AGENTSVIEW_EMBEDDINGS_API_KEY_FILE if the endpoint needs a key) under AGENTSVIEW_MODE. Adoption needs exactly one published recipe; see semantic search: PostgreSQL. Set AGENTSVIEW_EMBEDDINGS_BATCH_SIZE to a positive integer to change the number of inputs per request from the default 32.

The recipe is resolved on the first cycle that succeeds and kept until the process restarts. --embed never starts a daemon and refuses to run while a daemon owns the archive; run agentsview daemon stop to build here. Watch mode builds in the background after each sync that commits changes with a 30-second debounce and honors [vector.embed] backstop_interval to reconcile older imports. With [vector.embed] recall = true it also keeps the Recall embeddings current. Built vectors reach PostgreSQL on the next interval push. Until the recipe's own generation is built, the watcher pushes no vectors, even when an older generation is still active locally. While another process holds vectors.write.lock, such as a direct agentsview embeddings build, the watcher keeps pushing sessions, logs that embeddings have not started, and starts them on the first push after the lock frees.

A one-shot pg push --embed checks for a published recipe before it syncs, so a missing or ambiguous recipe fails before any session work. It then pushes sessions, builds embeddings with progress output, and pushes vectors in the same push. It builds only after PostgreSQL confirms it can store vectors and register the recipe's generation, so a server without pgvector, or a role that cannot create the vector tables or register a generation, skips the build. --full --embed repairs vector rows too. A build error returns after the sessions land, with their counts shown.

A watcher started before the first recipe is published adopts it on a later push, checking PostgreSQL at most once per --interval. If pg serve starts before a workstation pushes its first generation, restart pg serve after that push to enable semantic search. After changing the recipe, stop the hub's pg push --watch --embed, run agentsview pg vectors drop <old>, then start the watcher and restart pg serve to adopt the new recipe. A running watcher keeps pushing the generation it adopted and recreates the dropped row. --embed cannot be combined with --no-vectors, --all, push_vectors = false, or a usage-only archive.

To keep the database password out of the URL, mount a passfile and set PGPASSFILE; see container deployment.

agentsview pg status¶

Show the current sync state.

agentsview pg status [target] [flags]
agentsview pg status --all
agentsview pg status --projects alpha,beta

Without a target name, pg status uses the effective default target. Pass one named target explicitly to inspect that destination, or use --all to print every configured target sequentially.

Use the same --projects, --exclude-projects, or --all-projects filter flags as pg push to inspect the matching filtered or unfiltered watermark.

Output:

Machine:     my-laptop
Last push:   2026-03-24T10:30:00Z
PG sessions: 1842
PG messages: 47291

agentsview pg service¶

Install and manage the pg push --watch auto-push daemon as a per-user OS service.

agentsview pg service install
agentsview pg service status
agentsview pg service logs -f
agentsview pg service stop
agentsview pg service start
agentsview pg service uninstall

Supported service managers:

The generated unit runs agentsview pg push --watch, pins AGENTSVIEW_DATA_DIR to the data directory used at install time, and writes logs to ~/.agentsview/pg-watch.log unless you changed the data directory.

install requires a literal PostgreSQL URL in the effective default target of ~/.agentsview/config.toml, either the legacy [pg].url or the target selected by default_pg from named [pg.NAME] blocks. It intentionally rejects AGENTSVIEW_PG_URL and environment-expanded URLs such as ${PG_PASSWORD} because background services do not inherit your interactive shell environment. Other session-directory environment variables are not copied into the unit either; put persistent settings in config.toml before installing.

On headless Linux machines, systemd --user services stop at logout and do not start at boot unless user lingering is enabled. If lingering is disabled, install prints the exact loginctl enable-linger "$USER" command and offers to run it.

agentsview pg serve¶

Start the PostgreSQL web UI. Legacy mode reads pushed sessions; an explicitly provisioned hosted target can also derive sessions from accepted raw captures.

agentsview pg serve [flags]

The normal session server is read-only: session uploads, file watching, and local sync are disabled. Sessions from all machines appear in a single unified view. The same deployment also serves the analytics dashboard and the Usage page from PostgreSQL-backed queries.

When the PostgreSQL role can write the raw-sync tables and ingest-job sequence, pg serve also registers the hosted raw-sync control plane. Those routes can write raw custody metadata and local raw-sync storage, but they do not make the session APIs writable. A PostgreSQL role without the required raw-sync write privileges omits the runtime routes and logs the missing requirements. Existing least-privilege raw-sync roles now also need SELECT and UPDATE on raw_ingest_jobs so manifest commits can retire obsolete parse jobs:

GRANT SELECT, UPDATE ON agentsview.raw_ingest_jobs TO raw_sync_runtime;

Run this as the schema owner, substitute your schema and runtime role, and restart pg serve. Keep the existing INSERT and ingest-job sequence USAGE grants. This enables legacy raw-custody HTTP routes. For server-owned parsing, use the explicit hosted setup below.

In legacy mode, pg serve automatically applies any pending schema migrations to PostgreSQL, creating new tables and indexes added in newer AgentsView versions. This removes the need to run pg push before starting the server after an upgrade. If the PostgreSQL role is read-only, the migration is skipped and the server falls back to the schema compatibility check.

When require_auth is enabled, a bearer token is generated if needed and printed on startup. Pass it via Authorization: Bearer <token> on API requests. The SSE watch endpoint also accepts ?token=<token> as a query parameter since the EventSource API cannot set custom headers.

The raw-sync machine routes do not accept this shared bearer token. They use their own device credentials and operation-scoped tokens as described in the hosted raw-sync security boundary.

For LAN access, combine require_auth = true with a non-loopback bind such as agentsview pg serve --host 0.0.0.0, or keep the backend on loopback and expose it through a proxy.

pg serve does not expose the global live-refresh event stream used by normal agentsview serve, because there is no local sync engine attached to the server. The session browser, analytics, and usage views still work normally; they are just not auto-refreshed by the global SSE path.

As of 0.33.0, the web UI distinguishes a degraded backend (the server responds but PostgreSQL is temporarily unavailable, surfacing as 5xx errors) from an unreachable one. Instead of forcing a page reload — which could loop while the database was down — the app stays interactive with its current data, shows a compact warning in the status bar, and clears it once a real data read succeeds. Click the status bar to retry immediately. True network failures still use the reload-based recovery path.

CockroachDB also works as the shared database. 0.33.0 reworked the analytics and usage queries to perform well on CockroachDB (filter pushdown into the source tables, SQL-side tool-call aggregation, batched pricing writes) without changing any report semantics on PostgreSQL or SQLite.

In a multi-tenant deployment behind PostgreSQL row-level security, slow analytics or usage panels usually point at the policy shape rather than at agentsview itself. The read-side queries are already session-id-shaped, so expose child rows through a set-based predicate such as session_id IN (SELECT ...), not a per-row SECURITY DEFINER call that runs once per scanned row. If shared-dataset aggregates still need more time, raise the API deadline with --write-timeout; the Remote Access page covers the flag and the larger troubleshooting path.

When a reverse proxy reaches the backend by an internal hostname or service name, keep --public-url on the browser origin and add the proxy's upstream origin with --public-origin. The forwarded-access contract and troubleshooting details live in Remote Access.

For managed Caddy mode, keep the backend --host on loopback and use --proxy-bind-host / --public-port to expose the public listener. The pg serve and normal serve modes keep separate managed-Caddy state, so both can coexist on one host.

Examples:

# Local development — loopback, no auth required
agentsview pg serve

# Team viewer with managed Caddy and TLS
# Requires require_auth = true in config.toml
agentsview pg serve \
  --proxy caddy \
  --public-url https://viewer.example.com \
  --public-port 8443 \
  --tls-cert /path/to/cert.pem \
  --tls-key /path/to/key.pem

# Remote access on a trusted private network (no TLS)
# Requires require_auth = true; only use behind a VPN or on a
# private LAN because tokens cross the wire in cleartext.
agentsview pg serve --host 0.0.0.0 --port 8080

# External reverse proxy under a subpath; the browser origin
# and the proxy's upstream origin are different.
agentsview pg serve \
  --host 0.0.0.0 \
  --base-path /agentsview \
  --public-url https://console.example.com \
  --public-origin http://agentsview:8080

Machine Labels¶

Each session keeps its source installation ID. The web UI uses display labels for readability; set local_machine_name in the source's config.toml, restart the daemon, and push again to change the label. Use the multi-host filter in the sidebar to show sessions from specific machines.

The installation upgrade moves historical local sessions to their installation ID in SQLite. The next incremental PostgreSQL push republishes those sessions and their metadata. It also copies adopted hostname aliases, so existing machine filters and URLs keep working. If two installations publish the same old hostname alias, the latest push determines its filter target. Use installation IDs to select machines unambiguously in a shared mirror. Equal display labels do not merge machines.

Configuration¶

Single-target PostgreSQL settings can live in the legacy [pg] section of ~/.agentsview/config.toml:

[pg]
url = "postgres://user:pass@host:5432/dbname?sslmode=require"
schema = "agentsview"
allow_insecure = false

To manage more than one PostgreSQL destination, define named [pg.NAME] blocks and select the effective default target with default_pg:

default_pg = "work"

[pg.work]
url = "postgres://user:pass@work-db:5432/agentsview?sslmode=require"

[pg.archive]
url = "postgres://user:pass@archive-db:5432/agentsview?sslmode=require"
exclude_projects = ["scratch"]

agentsview pg push and agentsview pg status use the effective default target when no target name is passed, accept one target name explicitly, and also support --all for sequential multi-target runs. agentsview pg push --watch follows the effective default target unless you pass one named target explicitly. agentsview pg serve and agentsview pg service stay on the effective default target in this release, and --all --watch is rejected.

The connection string supports standard PostgreSQL parameters. Use sslmode=require or sslmode=verify-full for remote databases. Only use sslmode=disable for trusted local connections.

Environment variables in the URL are expanded using ${VAR} syntax:

[pg]
url = "postgres://${PG_USER}:${PG_PASSWORD}@host:5432/dbname?sslmode=require"

Environment Variables¶

PostgreSQL settings can also be configured via environment variables. In legacy single-target mode they override the [pg] values. In named-target mode they apply only to the effective default target:

Multi-Machine Workflow¶

A typical team setup:

1. Each developer configures [pg] in their local config.toml. The installation ID identifies their sessions; local_machine_name sets the display label
2. Each developer installs agentsview pg service or runs agentsview pg push --watch to sync their sessions
3. One server runs agentsview pg serve pointed at the shared PostgreSQL database
4. The team opens the shared dashboard to browse everyone's sessions, filtered by machine if needed

# Developer A's machine
agentsview pg service install

# Team server behind an external reverse proxy
agentsview pg serve \
  --host 0.0.0.0 \
  --base-path /agentsview \
  --public-url https://console.example.com \
  --public-origin http://agentsview:8080

Limitations¶

• One-way sync — sessions flow from SQLite to PostgreSQL only. Changes in PostgreSQL do not propagate back to local machines.
• Permanent deletes not propagated — sessions removed via agentsview prune are not deleted from PostgreSQL because the local rows no longer exist at push time. Use a direct SQL DELETE to clean up PostgreSQL if needed. Soft-deleted sessions (trash) sync correctly.
• Schema compatibility — legacy pg serve automatically applies pending schema migrations on startup. If the PostgreSQL role lacks DDL permissions, run agentsview pg push from a machine with write access to update the schema.
• Trigram index bloat on pre-0.33.0 schemas — the content search index was created with GIN fastupdate on, which let a pending-insert list grow unbounded under continuous ingest. 0.33.0 creates the index with fastupdate = off and alters existing indexes automatically, which stops further growth — but space already consumed is only reclaimed by a one-time REINDEX INDEX idx_messages_content_trgm;.