How PostgreSQL Push Sync (`pg push`) Operates in AgentsView: Incremental Replication Deep Dive

PostgreSQL push sync in AgentsView replicates local SQLite session data to a remote PostgreSQL database using an incremental, fingerprint-driven, transactional algorithm that only transmits changed sessions while preventing conflicts through marker-based ownership semantics.

AgentsView maintains a local SQLite database for AI-agent sessions, and the PostgreSQL push sync feature enables reliable replication to a remote PostgreSQL instance. According to the kenn-io/agentsview source code, the pg push command implements an incremental synchronization protocol that minimizes network traffic and handles concurrent access through fingerprinting and ownership markers.

Core Architecture of the Push Sync Algorithm

The push sync implementation lives primarily in internal/postgres/push.go. The Sync.Push method orchestrates a six-stage pipeline that ensures only modified data leaves the local machine while maintaining consistency with the remote PostgreSQL schema.

Initialization and Reset Detection

The operation begins in Sync.Push (lines 66–72) where the system normalizes timestamps and reads the last successful push watermark (last_push_at) to establish a baseline. When the --full flag is passed or the remote PostgreSQL instance has lost its marker (indicating a schema drop or reset), the local watermark is cleared and the schema-initialization flag is reset. This forces a complete re-push of all sessions rather than an incremental update.

Determining the Change Window

The algorithm establishes a new cut-off timestamp using the current UTC time (line 55). It then queries the local SQLite database via ListSessionsModifiedBetween to retrieve all sessions that have been modified since the last watermark. This creates a candidate set of sessions that potentially need synchronization.

Content Fingerprinting for Deduplication

Before transmitting data, the system computes a session fingerprint using sessionPushFingerprint (lines 891–966). This function hashes every column relevant to PostgreSQL synchronization along with the owner-marker ID. Previously stored fingerprints are loaded via readBoundaryAndFingerprints (lines 554–571). Any session whose fingerprint matches the stored value is removed from the push set, eliminating redundant network traffic for unchanged records.

Transactional Batch Processing

Sessions are processed in batches of 50 (lines 283–286) to balance memory usage and transaction efficiency. Each batch is sent to PostgreSQL inside a single transaction via pushBatch (lines 629–650).

Within each batch, pushSession performs an atomic upsert using INSERT … ON CONFLICT … UPDATE syntax (lines 512–563). The operation respects ownership semantics through sameSessionOwner and pushMarkerLegacyMachines functions to handle concurrent modifications from different machines.

For message data, pushMessages replaces the session's messages, tool calls, and usage events. It first verifies whether the PostgreSQL side already contains an identical message set by comparing content length sums, role/time fingerprints, flags, and token counts. If all fingerprints match, the function returns early (lines 224–272), skipping unnecessary writes.

State Finalization and Marker Management

After all batches succeed, finalizePushState (lines 1313–1329) persists the new watermark timestamp and the updated fingerprint map to local storage. The system then writes a push marker—a key/value pair stored in the PostgreSQL sync_metadata table—using writePushMarker (lines 445–488). This marker allows future runs to detect if the remote PostgreSQL database has been reset or restored from backup.

Progress Reporting

An optional onProgress callback can be supplied to Sync.Push (lines 20–24). When provided, the callback invoked after each batch reports totals for sessions processed, messages synchronized, conflicts encountered, and errors.

Key Implementation Files

The PostgreSQL push sync spans several files in the kenn-io/agentsview repository:

• internal/postgres/push.go – Core implementation containing Sync.Push, pushBatch, pushSession, pushMessages, and marker handling logic.
• internal/postgres/schema.go – Defines the PostgreSQL schema including tables sessions, messages, tool_calls, usage_events, and sync_metadata.
• cmd/agentsview/pg.go – CLI command wiring that exposes agentsview pg push with flags --full and --progress.
• internal/db/db.go – SQLite abstraction providing ListSessionsModifiedBetween and fingerprint helpers consumed by the push pipeline.

Command-Line Usage and Programmatic API

CLI Examples:

# Push only sessions changed since the last successful push

agentsview pg push

# Force a full re-push after schema changes or PG reset

agentsview pg push --full

# Display live progress during synchronization

agentsview pg push --progress

Programmatic Usage:

ctx := context.Background()
pgSync, err := agentsview.NewPostgresSync(cfg) // cfg contains DB connection info
if err != nil {
    log.Fatal(err)
}

result, err := pgSync.Push(ctx, false, func(p agentsview.PushProgress) {
    fmt.Printf("Pushed %d/%d sessions, %d msgs, %d errors\n",
        p.SessionsDone, p.SessionsTotal, p.MessagesDone, p.Errors)
})
if err != nil {
    log.Fatalf("push failed: %v", err)
}
fmt.Printf("Push completed: %+v\n", result)

Summary

• PostgreSQL push sync operates incrementally by comparing fingerprints of local SQLite sessions against previously pushed state stored in the sync_metadata table.
• The algorithm processes data in batches of 50 within atomic PostgreSQL transactions, ensuring consistency even during network interruptions.
• Fingerprint-based deduplication occurs at both the session level (sessionPushFingerprint) and message level (content hashes, role/time checks) to minimize redundant writes.
• Ownership markers prevent conflicts when multiple machines push to the same PostgreSQL database, with legacy machine handling for backward compatibility.
• The --full flag forces a complete re-synchronization by clearing local watermarks and reinitializing the remote schema, useful after database resets or schema migrations.

Frequently Asked Questions

What happens if the PostgreSQL database is reset or the schema is dropped?

When the remote PostgreSQL database loses its push marker (stored in the sync_metadata table), the Sync.Push method detects this condition during initialization. It clears the local last_push_at watermark and resets the schema-initialization flag, effectively forcing a full re-push of all sessions on the next run. This behavior is also triggered manually via the --full CLI flag.

How does the push sync determine which sessions have actually changed?

The system computes a session fingerprint using sessionPushFingerprint in internal/postgres/push.go (lines 891–966), which hashes all relevant columns plus the owner marker. This fingerprint is compared against values stored from the previous push (loaded via readBoundaryAndFingerprints). Only sessions with mismatched fingerprints are included in the transmission batch, ensuring unchanged data never leaves the local machine.

What is the purpose of the push marker stored in the sync_metadata table?

The push marker is a small key/value pair written by writePushMarker (lines 445–488) that records the unique identity of the machine or dataset that last synchronized with PostgreSQL. Future push operations verify this marker; if it is missing or mismatched, the system assumes the PostgreSQL database was restored from backup or recreated, triggering protective reset logic to prevent data inconsistency.

Can multiple machines push to the same PostgreSQL database simultaneously?

Yes, the implementation supports concurrent access through ownership semantics checked via sameSessionOwner and pushMarkerLegacyMachines. When pushSession executes its upsert (lines 512–563), it validates that the incoming session data matches the expected owner marker before updating. Conflicting modifications from different machines are handled through the marker system, preventing one machine from overwriting another's data unintentionally.

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