How agentsview Uses Fingerprinting for Efficient PostgreSQL Push Sync

Agentsview's PostgreSQL push sync eliminates unnecessary database writes by generating SHA-256 fingerprints for local session data and comparing them against stored PostgreSQL hashes, skipping unchanged sessions entirely during incremental synchronizations.

Agentsview synchronizes local session data to remote PostgreSQL stores using the Sync.Push method implemented in [internal/postgres/push.go](https://github.com/kenn-io/agentsview/blob/main/internal/postgres/push.go). Because sessions can contain thousands of messages, transmitting unchanged rows would waste bandwidth and database resources. The implementation solves this through fingerprinting—a deterministic hashing strategy that reduces each session's state to comparable checksums.

How Session Fingerprints Are Generated

The fingerprinting process begins with sessionPushFingerprint in [push.go](https://github.com/kenn-io/agentsview/blob/main/internal/postgres/push.go#L2121-L2197), which concatenates critical session fields—ID, project, machine, timestamps, token counts, and outcome—into a deterministic "length:string" format. This string is then hashed using SHA-256 to produce a single fingerprint value.

For message-level data, the [push_fingerprint.go](https://github.com/kenn-io/agentsview/blob/main/internal/postgres/push_fingerprint.go) file provides specialized aggregators that build multiple per-session fingerprints:

• MessageAggregates: Count, sum, min, and max of content_length
• MessageContentHash: A hash of concatenated ordinal|contentLength|sha256(content) for every message
• MessageRoleTime: A deterministic string built from message role and timestamp
• MessageFlags, MessageSystemOrdinals, MessageTokenFingerprint: Hashed textual representations of their respective rows
• ToolCallFingerprint, ToolResultFingerprint, UsageEventFingerprint: Specialized hashes for tool-related data

The helper functions like loadPushMessageContentHashFingerprints (lines 179-212) scan relevant rows and compute these hashes before any database comparison occurs.

Comparing Local and Remote State

Before pushing data, agentsview reads existing fingerprints from PostgreSQL using readPushSessionMessageComparisons (lines 69-84). This function queries the destination database for all aggregate values and fingerprint strings associated with the target session IDs.

The core skip decision logic resides in shouldSkipSessionMessages (lines 447-471). This function returns true only when every fingerprint component matches between local and remote state:

func shouldSkipSessionMessages(
    sessionID string,
    localCount int,
    localFP pushLocalMessageFingerprint,
    full bool,
    comparisons *pushMessageComparison,
) bool {
    if full || localCount == 0 || comparisons == nil {
        return false
    }
    pgAgg := comparisons.MessageAggregates[sessionID]
    if pgAgg.Count != localCount || pgAgg.Count == 0 {
        return false
    }
    return localFP.Sum == pgAgg.Sum &&
        localFP.Max == pgAgg.Max &&
        localFP.Min == pgAgg.Min &&
        localFP.ContentHashFP == comparisons.MessageContentHash[sessionID] &&
        localFP.RoleTimeFP == comparisons.MessageRoleTime[sessionID] &&
        localFP.FlagsFP == comparisons.MessageFlags[sessionID] &&
        localFP.SystemFP == comparisons.MessageSystemOrdinals[sessionID] &&
        localFP.ToolCallCount == comparisons.ToolCallAggregates[sessionID].Count &&
        localFP.ToolCallSum == comparisons.ToolCallAggregates[sessionID].Sum &&
        localFP.ToolCallFP == comparisons.ToolCallFingerprint[sessionID] &&
        localFP.ToolResultFP == comparisons.ToolResultFingerprint[sessionID] &&
        localFP.TokenFP == comparisons.MessageTokenFingerprint[sessionID] &&
        localFP.UsageEventFP == comparisons.UsageEventFingerprint[sessionID]
}

The Delta Detection Workflow

The pushMessages function (called from pushBatch) implements the actual delta detection workflow:

1. Preload fingerprints: pushBatchAttempt loads all required PostgreSQL fingerprints in a single query to minimize database round-trips
2. Local computation: Generate fresh fingerprints for the current session batch
3. Comparison: Pass local and remote fingerprints to shouldSkipSessionMessages
4. Conditional skip: If all fingerprints match, return 0 messages without issuing INSERT/UPDATE statements
5. Delta upsert: If any fingerprint mismatches, push only the changed rows

This approach ensures that unchanged sessions generate zero database traffic, while modified sessions receive precise incremental updates.

Persisting State for Incremental Syncs

After a successful push, agentsview persists fingerprint state to enable future optimizations. The sessionFingerprints map (built in Push around lines 296-302) stores newly computed fingerprints for each processed session.

The writePushBoundaryState function records these fingerprints alongside the push watermark (last_push_boundary_state_key). During the next synchronization cycle, readBoundaryAndFingerprints (lines 555-564) retrieves this stored state, allowing the system to skip sessions whose fingerprints remain unchanged since the last successful push.

// In push.go, after all batches are applied
if err := persistPushTargetFingerprint(state, s.targetFingerprint); err != nil {
    return result, err
}
if err := writePushBoundaryState(state, cutoff, pushed, priorFingerprints, sessionFingerprints); err != nil {
    return result, err
}

Summary

• SHA-256 fingerprinting reduces session state to comparable hashes, avoiding expensive row-by-row comparisons
• Multi-dimensional fingerprints cover messages, tool calls, usage events, and session metadata through specialized aggregators in push_fingerprint.go
• Batch preloading minimizes database queries by fetching all remote fingerprints in a single operation via readPushSessionMessageComparisons
• Deterministic skip logic in shouldSkipSessionMessages ensures zero writes for unchanged sessions while guaranteeing consistency through comprehensive hash comparisons
• Boundary state persistence enables incremental pushes across application restarts by storing fingerprints in PostgreSQL alongside watermark metadata

Frequently Asked Questions

What hashing algorithm does agentsview use for PostgreSQL push fingerprinting?

Agentsview uses SHA-256 for all fingerprint generation. The sessionPushFingerprint function and various message fingerprint helpers in push_fingerprint.go concatenate relevant fields into deterministic strings and compute SHA-256 hashes to produce fixed-length comparison values.

How does agentsview detect if a session needs updating or can be skipped?

The system calls shouldSkipSessionMessages to compare twelve different fingerprint components—including message aggregates, content hashes, role-time strings, and tool-related fingerprints—between local state and PostgreSQL. If all components match exactly, the session is skipped entirely; otherwise, the delta is pushed.

What happens to fingerprints after a successful PostgreSQL push?

The writePushBoundaryState function persists the fingerprint map to PostgreSQL alongside the push watermark. The next invocation of Sync.Push retrieves these stored fingerprints via readBoundaryAndFingerprints, allowing the system to identify unchanged sessions without querying message tables.

Does fingerprinting work for sessions with tool calls and usage events?

Yes. The fingerprinting system includes specialized aggregators for tool calls (ToolCallFingerprint), tool results (ToolResultFingerprint), and usage events (UsageEventFingerprint). These are compared alongside message fingerprints in shouldSkipSessionMessages to ensure comprehensive change detection across all session data types.

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