Sync Write Model V1

Intent

Define the first implementation write model for sync clients, aligned to:

• pgxsinkit append-only operation logging
• deterministic, low-complexity conflict behavior
• strong mutation traceability

Confirmed V1 Direction

1. pgxsinkit is external and versioned via npm registry releases.
2. The team controls pgxsinkit evolution and can ship breaking major versions when required.
3. pgxsinkit must keep an append-only log of all client-submitted operations.
4. V1 conflict resolution defaults to simple last-write-wins (LWW) with deterministic server apply ordering.
5. All syncable domain-table mutations must flow through the same pgxsinkit batch mutation ingress.
6. The only approved write backend mode for syncable domain tables is bulk-plpgsql-artifact.

Single Ingress Policy

For syncable domain tables, all writes must use one ingress path:

• POST /api/mutations
• write backend bulk-plpgsql-artifact
• shared operation log and apply ordering semantics

Non-client actors (admin tools, imports, batch jobs, system workflows) are still allowed, but they must submit through the same batch ingress contract.

pgxsinkit Integration Policy

• Keep pgxsinkit as an external dependency, not vendored into app packages.
• Pin explicit npm versions in workspace dependencies.
• Use semver-major bumps for protocol/storage breaking changes.
• Do not block v1 correctness for long-term backward compatibility guarantees.
• Enforce WRITE_API_BACKEND=bulk-plpgsql-artifact for syncable domain workloads in every environment.

Operation Log Requirements

The append-only operation log must capture at least:

• operation_id (monotonic/ordered unique ID)
• client_id
• client_operation_id (idempotency key from client)
• person_id (nullable for pre-auth flows)
• target_table
• target_record_id (nullable for inserts before server ID assignment)
• operation_type (insert, update, delete, state-transition)
• payload_before (nullable)
• payload_after (nullable)
• submitted_at
• applied_at
• apply_status (applied, rejected, ignored, superseded)
• rejection_reason (nullable)

LWW Conflict Baseline

Rule

For mutable sync tables, concurrent writes resolve by latest authoritative server apply order.

Determinism

Use server apply order (operation_id or equivalent ordered apply cursor), not client clock time.

Because all syncable writes use one ingress path, every mutation participates in the same authoritative apply-order space.

Why

• Low implementation complexity in v1
• Deterministic replay behavior
• Compatible with append-only operation logging

LWW Exceptions

Even in v1, avoid destructive LWW for append-oriented records:

• assessment_attempt
• feedback_entry
• person_competency_evidence
• activity_event
• change_audit_entry

These should remain append-friendly with idempotency controls.

Client Mutation Envelope

Each client mutation should include:

• client_id
• client_operation_id
• operation_time_client (informational only)
• target
• patch or full record payload
• expected_record_version (optional in v1, required for diagnostics)

Apply Pipeline

1. Validate envelope and authorization
2. Append operation to pgxsinkit operation log
3. Apply mutation under LWW baseline policy
4. Update target row updated_at_us, last_operation_id, and last_apply_sequence
5. Emit audit/event records with source operation link
6. Acknowledge client with applied/superseded/rejected outcome

Admin/import/batch/system workflows targeting syncable domain tables must use the same ingress envelope and pipeline.

Observability Requirements

Track at minimum:

• Operation ingestion rate
• Rejected/superseded ratio
• Median and p95 apply latency
• Replay/retry counts by client
• Per-table LWW supersession counts

Correctness Escalation Policy

Only replace LWW for a data class when all are true:

1. Correctness property is explicitly documented.
2. Proposed merge strategy is deterministic under replay.
3. Complexity and operational cost are acceptable for early stage.
4. Conformance tests prove behavior in disconnected and concurrent scenarios.

Until then, keep LWW baseline.

Implementation Anchors

• pgxsinkit protocol and operation log: external pgxsinkit project
• Sync integration and collection behavior: packages/offline-data
• Row-level operation linkage in schema: packages/db
• Mutation services and policy checks: apps/api

Operational enforcement guidance is defined in Sync Write Enforcement V1 (Lite).

Open Decisions

1. Final operation-log ordering primitive (bigint sequence vs sortable UUID)
2. Scope of v1 expected_record_version enforcement
3. Whether rejected operations are replayable or terminal by default
4. Whether mutable syncable rows should carry enforced FK to operations_log.id or a validated opaque operation identifier