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

> Understand PostgreSQL push sync pg push in AgentsView. Learn how incremental replication efficiently syncs SQLite data to PostgreSQL using a transactional, conflict-free algorithm.

- Repository: [Kenn Software/agentsview](https://github.com/kenn-io/agentsview)
- Tags: deep-dive
- Published: 2026-06-20

---

**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`](https://github.com/kenn-io/agentsview/blob/main/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`](https://github.com/kenn-io/agentsview/blob/main/internal/postgres/push.go)** – Core implementation containing `Sync.Push`, `pushBatch`, `pushSession`, `pushMessages`, and marker handling logic.
- **[`internal/postgres/schema.go`](https://github.com/kenn-io/agentsview/blob/main/internal/postgres/schema.go)** – Defines the PostgreSQL schema including tables `sessions`, `messages`, `tool_calls`, `usage_events`, and `sync_metadata`.
- **[`cmd/agentsview/pg.go`](https://github.com/kenn-io/agentsview/blob/main/cmd/agentsview/pg.go)** – CLI command wiring that exposes `agentsview pg push` with flags `--full` and `--progress`.
- **[`internal/db/db.go`](https://github.com/kenn-io/agentsview/blob/main/internal/db/db.go)** – SQLite abstraction providing `ListSessionsModifiedBetween` and fingerprint helpers consumed by the push pipeline.

## Command-Line Usage and Programmatic API

**CLI Examples:**

```bash

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

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