Sync Topology

Intent

Define how transactional data moves and converges between backend PostgreSQL and client-side PGlite while preserving correctness and user experience.

Topology Overview

Primary components:

• PostgreSQL as durable system of record
• ElectricSQL for shape-based replication
• PGlite for local/offline query and mutation workflows
• pgxsinkit integration logic in packages/offline-data

pgxsinkit is treated as an external dependency (npm versioned) that the team can evolve with major-version breaking changes when needed.

Responsibility Boundaries

Backend PostgreSQL

• Authoritative persistence
• Cross-tenant integrity and policy enforcement
• Final conflict adjudication rules where server authority is required

Client PGlite

• Local-first UX and offline continuity
• Pending mutation queue and optimistic projection
• Fast local reads for active learning workflows

Sync Layer

• Shape subscription and projection
• Change transport and merge behavior
• Conflict classification and reconciliation strategies

Write Ingress Policy

For syncable domain tables:

• all writes go through pgxsinkit batch ingress (POST /api/mutations)
• backend mode is bulk-plpgsql-artifact
• admin/import/batch/system writes use the same batch mutation contract

Sync Data Categories

Use category-specific sync behavior:

1. Identity/context records Mostly server-authoritative; low-frequency updates.

2. Membership and schedule records Server-authoritative with local cache and optimistic reads.

3. Learner progress and drafts Client-first capture with eventual server merge.

4. Assessment attempts and submissions Strong integrity requirements; local capture with deterministic commit semantics.

5. Event logs Append-friendly with idempotency keys.

Conflict Strategy Baseline

V1 baseline is deterministic server-apply-order last-write-wins (LWW) for mutable records.

Supporting rules:

• Every client-submitted mutation is appended to pgxsinkit operation log
• LWW ordering uses authoritative server apply sequence, not client clocks
• Every syncable-table mutation (including admin/import/batch) is ordered in the same sequence space
• Append-oriented tables remain append-oriented with idempotency controls
• More advanced merge logic is deferred until correctness can be guaranteed without excessive complexity

Offline Mutation Principles

• Mutations should carry idempotency keys
• Local write intent should be auditable
• Retry logic must be deterministic
• User-visible state should distinguish local pending vs confirmed

Convergence and Observability

The system must provide:

• Eventual convergence guarantees for supported mutation classes
• Replay-safe mutation handling
• Sync diagnostics for failed merges
• Metrics for lag, retry counts, and reconciliation outcomes

Standards Mapping Interaction

Sync events should be mappable to standards outputs:

• Membership changes to roster exchange flows
• Attempt/finalization changes to xAPI/LRS and grade exchange
• Qualification grants to CLR/Open Badge projections

See integration docs for event-level mappings.

Implementation Anchors

• Collection names, shape logic, and local conflict handling: packages/offline-data
• Durable schema and constraints: packages/db
• Event and payload contracts: packages/domain
• Mutation orchestration and policy: apps/api

Open Decisions

1. Per-table list of LWW exceptions for v1.0 and v1.1
2. Required offline guarantees for teacher workflows vs learner workflows
3. Minimum telemetry set for sync health gates in CI/staging