PostgreSQL Push Sync Process for Team Sharing in agentsview

The PostgreSQL push sync in agentsview is a one-way synchronization mechanism that lets multiple developers push local SQLite session data to a shared PostgreSQL hub while enforcing strict ownership rules and deduplicating unchanged sessions through cryptographic fingerprinting.

agentsview enables teams to share AI agent session data across multiple machines. The PostgreSQL push sync process allows developers to consolidate local SQLite databases into a central PostgreSQL instance using a push-only architecture that guarantees each session owner maintains exclusive write access to their data.

Phase 1: Preparation and Schema Validation

Before transmitting data, the sync engine validates local state against the PostgreSQL target. In internal/postgres/push.go, the pushMarkerID function (lines 66-84) generates a stable identifier unique to the local database that survives machine renames.

The system fetches the target fingerprint (pgTargetFingerprint) to detect schema changes. If the stored fingerprint differs from the current PostgreSQL schema, or if the marker is missing from the hub, clearPushState (lines 93-103) resets the local push state and forces a full sync. This prevents silent data loss when the hub is recreated.

The current sync state—including the watermark last_push_at and boundary state—is loaded via pushTargetState (lines 78-97). If fingerprints mismatch, the system clears local state and prepares for a complete re-push.

Phase 2: Batch Push and Conflict Detection

The Push function in push.go (lines 63-79) orchestrates transmission. First, it queries all sessions modified after the watermark using ListSessionsModifiedBetween. For each session, it computes a sessionPushFingerprint (lines 65-140) that incorporates session data, machine name, usage-event fingerprints, and the marker ID.

If the fingerprint matches the boundary state, the session is skipped—this cheap deduplication prevents redundant uploads. Remaining sessions are processed in batches of 50 via pushBatch (lines 155-185), each executing within a single PostgreSQL transaction.

Inside each batch, the system performs three critical operations:

1. Ownership Verification (lines 147-156): Before writing, the code checks if the row's owner_marker or machine field is empty, local, or matches the current marker ID. If another machine owns the session, errSessionOwnershipConflict is returned and the session is counted as skipped.

2. Session Upsert (lines 165-190): The pushSession function executes an INSERT … ON CONFLICT DO UPDATE that only writes when columns differ, minimizing write amplification.

3. Related Data Upsert: pushMessages and pushSecretFindings sync associated messages and security findings within the same transaction.

If a batch fails, the system falls back to individual session retries, ensuring one corrupted session cannot block the entire sync.

Phase 3: Finalization and State Persistence

After successful batch completion, finalizePushState (lines 215-226) advances the push watermark to the new cutoff timestamp and updates the boundary state with fingerprints of pushed sessions.

The target fingerprint is persisted via persistPushTargetFingerprint (lines 236-242) to track schema versions for future incremental pushes. Finally, writePushMarker (lines 244-252) records the current machine name and a JSON list of historic aliases to the PostgreSQL hub, enabling reset detection across host renames.

If an alias backfill is required (first push after schema initialization), a full push is forced and the marker only persists after successful completion.

Team Sharing Guarantees

The PostgreSQL push sync provides four critical guarantees for multi-team deployments:

• Strict Ownership: Session rows can only be modified by the host that originally pushed them. The owner_marker field must match the current marker ID; otherwise, the write is rejected as a conflict.

• Reset Detection: If the marker key disappears from PostgreSQL (indicating hub recreation), the next push clears local state and performs a full sync, preventing data inconsistency.

• Fingerprint-Based Deduplication: Unchanged sessions are skipped based on cryptographic fingerprints, keeping bandwidth usage minimal even with frequent pushes from multiple team members.

• Scoped Sync State: Optional SyncStateTarget and project filters allow independent teams to share a single PostgreSQL instance without interfering with each other's watermarks.

Implementation Example

To initialize a sync connection for team sharing:

import (
    "log"
    "github.com/kenn-io/agentsview/internal/postgres"
    "github.com/kenn-io/agentsview/internal/db"
)

func newTeamSync(local *db.DB) *postgres.Sync {
    sync, err := postgres.New(
        "postgres://pguser:secret@pg-host:5432/agentsview?sslmode=disable",
        "public",            // PG schema name
        local,
        "ci-runner-01",      // unique machine identifier
        false,               // allow insecure connections?
        postgres.SyncOptions{
            SyncStateTarget: "team-beta", // each team gets its own watermark
        },
    )
    if err != nil {
        log.Fatalf("cannot create PG sync: %v", err)
    }
    return sync
}

Performing an incremental push:

ctx := context.Background()
sync := newTeamSync(localDB)

res, err := sync.Push(ctx, false, func(p postgres.PushProgress) {
    fmt.Printf("Progress: %d/%d sessions, %d msgs\n",
        p.SessionsDone, p.SessionsTotal, p.MessagesDone)
})
if err != nil {
    log.Fatalf("push error: %v", err)
}
fmt.Printf("Push complete – %d sessions, %d msgs in %s\n",
    res.SessionsPushed, res.MessagesPushed, res.Duration)

Forcing a full re-push after schema changes:

_, err := sync.Push(context.Background(), true, nil) // full = true
if err != nil {
    log.Fatalf("full push failed: %v", err)
}

Summary

• The PostgreSQL push sync uses a push-only architecture that prevents data pulls while enforcing strict ownership validation in pushSession.
• Cryptographic fingerprinting in sessionPushFingerprint (lines 65-140) enables cheap deduplication of unchanged sessions.
• Batch processing with individual fallback retries ensures robust transmission even with partial failures.
• Schema fingerprinting and marker persistence automatically detect hub resets and force full re-syncs when necessary.
• Scoped sync targets allow multiple teams to share one PostgreSQL instance without watermark conflicts.

Frequently Asked Questions

How does agentsview prevent one team member from overwriting another's sessions?

The system enforces ownership through the owner_marker field. During pushSession (lines 147-156), the code checks if the existing row's owner_marker or machine is empty, local, or matches the current marker ID. If another machine owns the session, the push returns errSessionOwnershipConflict and counts the session as skipped, preserving the original owner's data.

What happens if the PostgreSQL hub is recreated or reset?

If the marker key disappears from PostgreSQL, the pushMarkerID logic detects the missing marker on the next push. The system then calls clearPushState (lines 93-103) to reset local watermarks and forces a full push, ensuring no data loss occurs due to stale state references.

Can multiple teams share the same PostgreSQL database without interference?

Yes. By specifying different SyncStateTarget values in postgres.SyncOptions, each team maintains independent watermarks and boundary states. This scoping prevents teams from affecting each other's incremental sync positions while sharing the same underlying tables in internal/postgres/schema.go.

Why does the push sync use fingerprinting instead of simple timestamps?

The sessionPushFingerprint function (lines 65-140) generates a hash incorporating session content, machine name, usage events, and marker ID. This approach detects actual content changes rather than just modification times, preventing unnecessary uploads when sessions are touched but not substantively changed, significantly reducing bandwidth usage in CI environments.

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