**LOE: 13 (XL) | Rationale:** Epic 7 is the architectural core of the entire data-sync system, built from the ground up with no prior implementation. It implements five stateless consumers, a three-way merge algorithm with per-field conflict policies, six circular update prevention layers, a materialized `current_state` projection with optimistic locking, a Redis-backed coalescing buffer, a governance-aware NetSuite Writer with feature flag bypass, a reconciliation service, and a retry/DLQ handler with six error classes. The scope spans 7 service classes, 3 job classes, 5 model classes, 5 table DDL migrations, integration contracts with Epics 1, 4, and 8, and comprehensive test coverage across merge, conflict, deduplication, and circular suppression scenarios. --- ## Context Epic 7 is the architectural core of the bidirectional data-sync system between SuiteX and NetSuite. It builds the runtime engine that consumes raw change events, merges them against the target system's current state, resolves conflicts using tenant-configured per-field policies, suppresses circular update loops, and delivers resolved changes to the appropriate writer for application. ### Prerequisites | Epic | Dependency | Status Assumption | |------|-----------|-------------------| | Epic 1 | Canonical Event Envelope schema, `field_metadata` DDL and seeding | In-flight or complete | | Epic 3 + [7.2](tech-spec-7.2.md) | Node.js Mock Server — **must be extended** per [Epic 7.2](tech-spec-7.2.md) with six capabilities before Stage 3 | In-flight; extensions required before Stage 3 | | Epic 4 | SuiteX Emitter with shadow event support and `sourceSystem: "suitex"` attribution | In-flight or complete | | Epic 5 | NetSuite UE Emitter publishing to `events.raw` — **not required for Epic 7 development**; UE events are simulated via Epic 3 mock server | In-flight (simulated via mock) | | Epic 6 | NetSuite Polling Emitter publishing to `events.raw` — **not required for Epic 7 development**; polling events are simulated via Epic 3 mock server | In-flight (simulated via mock) | --- ## Description Build the complete Merge Orchestrator subsystem from the ground up. The system comprises five stateless Laravel background consumers, seven service classes, five database tables, and a Redis-backed coalescing buffer. The implementation follows a six-stage delivery plan aligned with the [design authority](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook). ### Architecture Overview **Five Consumers:** 1. **Merge Service** (`events.raw` → `events.merged`) — Validates, deduplicates, coalesces, suppresses circular updates, and emits canonical `MergedEvent` records. 2. **NetSuite Writer** (`events.merged` → NetSuite) — Filters for SuiteX-originated changes, fetches `RemoteCurrent` from NetSuite, performs three-way merge via `ConflictResolver`, applies governance-aware throttling, writes via RESTlet with idempotency keys. Includes Feature Flag bypass for progressive per-account rollout. 3. **SuiteX Writer** (`events.merged` → tenant DB) — Applies NetSuite-originated changes to SuiteX tenant databases, updates `current_state` projection. 4. **Reconciliation Service** (scheduled + on-demand) — Detects drift between `current_state` and actual NetSuite state, emits corrective events with `source = 'reconciliation'`. 5. **Error/DLQ Handler** (`events.error` + `events.dlq`) — Manages retries with per-class backoff schedules, routes exhausted events to `sync_error_queue` for Epic 8's dashboard. **Critical Design Decisions:** - **`conflict_policy` is a conflict resolution strategy, not a field ownership gate.** It activates only when both systems have changed the same field concurrently (since the shared base version). A SuiteX event updating a field with `conflict_policy = 'netsuite-wins'` succeeds normally if NetSuite hasn't also changed that field since the base version. - **The Merge Service does NOT fetch `RemoteCurrent` or perform the three-way merge.** That responsibility belongs to the Writers, per the [consumer map](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook#appendix-e--topic--consumer-map) and [F1/F2 pseudocode](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook#f1-merge-orchestrator). - **The coalescing buffer is Redis-backed.** In-memory buffers are explicitly prohibited (statelessness requirement). - **The Feature Flag bypass enables the Strangler Fig migration.** When an account is in bypass mode, `current_state` is built from shadow events (Epic 4) without NetSuite writes. When graduated, writes begin from a warm, consistent projection. - **Idempotency keys use the six-segment format** `:::::` — the `accountId` prefix is required because NetSuite `internalId` values are per-account, not globally unique. - **All field names use `operation`** (not `eventType`) per the [canonical schema](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook#c1-changeevent-schema). ### Mock Server Extensions (Epic 3 Dependency) All Epic 7 development and integration testing targets the Epic 3 Node.js Mock Server — not live NetSuite. The mock server requires six extensions (record read, marker fields, concurrent modification simulation, UE/polling event simulation, error injection) before Epic 7's Stage 3 can begin. These extensions are tracked as a separate task: **[Epic 7.2 — Mock Server Extensions](tech-spec-7.2.md)**. --- ### Areas to Review - [Coalescing / ordering](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook#event-priority-and-collisions-across-sources) - [Storage schema alignment (SuiteX side)](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook#storage-schema-alignment-suitex-side) - [Feature Flag bypass](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook#suitex-sync-pipeline-to-netsuite) - [Backfill & reconciliation](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook#backfill--reconciliation) - [Appendix E — Topic & Consumer Map](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook#appendix-e--topic--consumer-map) - [Appendix D — ERD](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook#appendix-d--erd-mermaid-format) - [F1. Merge Orchestrator](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook#f1-merge-orchestrator) and [F2. NetSuite Writer](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook#f2-netsuite-writer) - [O6. SuiteX Retry Policy](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook#o6-suitex-retry-policy) - [P2.1 Idempotency Key Schema](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook#p21-idempotency-key-schema) --- ## Staged Deliverables ### Stage 1 — Database Infrastructure 1. `current_state` table migration (tenant database) 2. `write_ledger` table migration (tenant database) 3. `idempotency_keys` table migration (placement TBD — see Technical Notes) 4. `record_lock` table migration (tenant database) 5. `conflicts` table migration (tenant database) 6. Eloquent model classes for all five tables under `src/Domain/Sync/Models/` ### Stage 2 — Core Service Classes 7. `MergeService.php` — Orchestrates validation, deduplication, coalescing, circular update suppression, `MergedEvent` emission 8. `ConflictResolver.php` — Per-field conflict policy engine; accepts `(base, remote, local)` inputs, returns `MergedPayload` or `ConflictResult`; shared by both Writers 9. `WriteLedgerService.php` — Write ledger reads/writes for circular update suppression 10. `IdempotencyService.php` — Key generation using six-segment format `:::::`, enforcement via `idempotency_keys` table 11. `FingerprintService.php` — UE/polling deduplication via SHA-256 fingerprint ### Stage 3 — Consumers: Merge Service + Writers 12. `ProcessRawEvent.php` job — Subscribes to `events.raw`, invokes `MergeService` pipeline 13. `ApplyMergedEvent.php` job — Subscribes to `events.merged`, dispatches to appropriate Writer based on `source` 14. `NetSuiteWriter.php` — Governance-aware outbound writer with Feature Flag bypass, fetches `RemoteCurrent`, performs three-way merge via `ConflictResolver` 15. `SuiteXWriter.php` — Applies inbound NetSuite changes to tenant DB, updates `current_state` 16. Feature Flag configuration — `netsuite_writer_enabled` per `accountId`, checked after idempotency verification but before outbound HTTP call 17. Redis-backed coalescing buffer with configurable window (default 5s), composite key `(accountId, recordType, recordId)`, three flush conditions ### Stage 4 — Error Handling + Reconciliation 18. `ProcessErrorEvent.php` job — Subscribes to `events.error`, applies retry policy, routes to DLQ or `sync_error_queue` 19. Retry policy implementation with six error classes and per-class backoff schedules 20. `sync_error_queue` persistence on all error routing events 21. Reconciliation Service — Nightly schedule per account per record type, full fetch + diff against `current_state`, manual admin endpoint `POST /admin/sync/reconcile` 22. Conflict escalation policy — 24-hour SLA, configurable escalation actions ### Stage 5 — Integration Tests 23. Circular update suppression (design spec Scenario 1) 24. Three-way merge with concurrent non-conflicting changes (design spec Scenario 3) 25. Conflict resolution with per-field policies (design spec Scenario 2) 26. Unilateral change succeeds regardless of `conflict_policy` value 27. UE/polling deduplication (design spec Scenario 4) 28. Conflict queueing for manual policy (design spec Scenario 5) 29. Feature Flag bypass ON vs OFF paths 30. Coalescing buffer flush conditions and crash recovery 31. Error routing for all six error classes 32. Idempotency key uniqueness across tenants ### Stage 6 — Observability & Cutover 33. Dashboards: events/sec, backlog depth, 429 rate, conflicts, error rates per account 34. Alerts and runbooks 35. Shadow event emission integration with Epic 4 --- ## Acceptance Criteria **Database Infrastructure** - [ ] `current_state` table exists with `PRIMARY KEY (account_id, record_type, record_id)`, `version BIGINT UNSIGNED NOT NULL`, `state JSON NOT NULL`, `last_event_id CHAR(36) NULL` - [ ] Optimistic locking enforced via `WHERE version = ?` with monotonic increment; `state` column stores a full JSON field-value map (not deltas) - [ ] `write_ledger` table exists with `UNIQUE KEY uk_record_field (account_id, record_type, record_id, field)` and `INDEX idx_write_id (last_write_id)` - [ ] `idempotency_keys` table exists with `UNIQUE KEY uk_target_key (target, idempotency_key)`, minimum 30-day retention policy, `VARCHAR(500)` key column to accommodate `accountId` prefix - [ ] `record_lock` table exists with `UNIQUE KEY uk_record (account_id, record_type, record_id)` and `conflict_id` FK reference - [ ] `conflicts` table exists with columns matching Epic 8's expected API contract - [ ] All five Eloquent model classes exist under `src/Domain/Sync/Models/` with correct table mappings **Merge Service** - [ ] Consumes from `events.raw` Pub/Sub subscription - [ ] Validates incoming payloads against the `ChangeEvent` JSON Schema (Epic 1) - [ ] Queries `field_metadata` for tenant field mappings and sync configuration - [ ] Records every validated event in the `events` table (immutable audit log) before any merge logic - [ ] Deduplicates UE vs polling events using fingerprint `hash(recordType, recordId, lastModifiedDate, operation)` - [ ] Suppresses circular updates by comparing incoming `writeId`/`sourceSystem` against `write_ledger`; suppressed events are still recorded in `event_audit_log` - [ ] Publishes resolved `MergedEvent` to `events.merged` - [ ] Zero occurrences of `eventType` in any service class — all references use `operation` **Coalescing Buffer** - [ ] Redis-backed (in-memory buffers explicitly prohibited) - [ ] Buffer key is composite `(accountId, recordType, recordId)` - [ ] Default window duration of 5 seconds, configurable via environment variable - [ ] Three flush conditions: idle timer expiry, count threshold (≥ 10 events), explicit flush flag - [ ] On flush: all constituent Pub/Sub messages are ACK'd and a single `MergedEvent` is emitted to `events.merged` - [ ] Buffer persists across worker restarts (Redis durability) **Three-Way Merge & Conflict Resolution** - [ ] `ConflictResolver` is a stateless service class (not a job) that accepts `(base, remote, local)` and returns `MergedPayload` or `ConflictResult` - [ ] `ConflictResolver` is invoked by both `NetSuiteWriter` and `SuiteXWriter`, not by the Merge Service - [ ] `conflict_policy` values (`netsuite-wins`, `suitex-wins`, `last-write-wins`, `manual`) are applied **only when both systems have changed the same field since the base version** — a unilateral change always succeeds regardless of policy - [ ] `manual` policy conflicts create a record in `conflicts` table, lock the record via `record_lock`, and block automated writes until human resolution - [ ] `current_state` is updated atomically with version increment after successful merge **NetSuite Writer** - [ ] Fetches `RemoteCurrent` from NetSuite via a live `GET` call (RESTlet or SuiteTalk REST API) immediately before applying writes — no SOAP, no cached/projected snapshots - [ ] The `RemoteCurrent` fetch follows a read-verify-update pattern: GET record → compare version against `baseVersion` → three-way merge if concurrent changes detected → write merged result - [ ] Governance budget accounts for the read-verify-update cost: each write operation consumes at minimum 2 API calls (1 GET + 1 PUT/POST) - [ ] Performs three-way merge via `ConflictResolver` - [ ] Feature Flag bypass: checks `netsuite_writer_enabled` per `accountId` after idempotency verification but before outbound HTTP call - [ ] Bypass ON (flag disabled): updates `current_state`, records `writeId` in `write_ledger`, ACKs Pub/Sub message, skips outbound HTTP call - [ ] Bypass OFF (flag enabled): full write path including NetSuite RESTlet call with idempotency key and marker fields (`custbody_suitex_write_id`, `custbody_suitex_write_source`) - [ ] Feature Flag is a Stage 3 deliverable, not buried in Stage 6 - [ ] Governance-aware: adaptive per-account concurrency ≤ 50% of concurrent request limit; circuit breaker at 50% error rate, 5-minute pause - [ ] Uses idempotency keys in six-segment format `:::::` **SuiteX Writer** - [ ] Applies NetSuite-originated changes to tenant database - [ ] Updates `current_state` projection with version increment - [ ] Records write in `write_ledger` for circular update tracking **Error Handling** - [ ] Retry policy table implemented with all six error classes (see Technical Notes for full table) - [ ] After max retries exhausted: event moves to `events.dlq` and persists to `sync_error_queue` with `status = 'exhausted'` - [ ] Every error routing event persists to `sync_error_queue` with columns: `tenant_id`, `record_type`, `record_id`, `error_class`, `reason`, `details`, `status`, `record_lock_id` - [ ] `record_lock_id` FK to `record_lock` is set for lock-creating errors - [ ] `status` lifecycle: `pending` (retry-eligible) → `exhausted` (DLQ) → `resolved` (manually cleared by Epic 8 dashboard) - [ ] Conflict errors route to `conflicts` table (not a Pub/Sub topic); Validation errors route to `sync_error_queue` **Conflict Escalation** - [ ] 24-hour SLA window (configurable via environment variable) for unresolved conflicts - [ ] Escalation actions: notify senior admin, pause automated writes, mark conflicted fields read-only - [ ] New events arriving on locked records: accepted and appended to event stream, blocked from automated apply until lock is released - [ ] Granular field-level locking option is documented and implementable **Reconciliation Service** - [ ] Nightly schedule per account per record type, managed via `sync_watermark` - [ ] Full fetch from NetSuite + diff against `current_state` - [ ] Output events have `source = 'reconciliation'` and are highest-priority in the pipeline - [ ] Manual admin API endpoint: `POST /admin/sync/reconcile` for on-demand resync - [ ] Bulk backfill support for onboarding accounts mid-migration **Mock Server Integration ([Epic 7.2](tech-spec-7.2.md))** - [ ] All integration tests run against the Epic 3 mock server — zero tests require live NetSuite connectivity - [ ] All six mock server extensions defined in [Epic 7.2](tech-spec-7.2.md) are delivered and pass their acceptance criteria before Stage 3 begins **Cross-Epic Contracts** - [ ] Stage 1 producers are owned by Epics 4 (SuiteX emitter), 5 (UE emitter), and 6 (polling emitter) — Epic 7 consumes their output, does not implement producers. During development, UE and polling events are simulated via the mock server ([Epic 7.2](tech-spec-7.2.md)). - [ ] Stage 5 Conflict UI frontend is owned by Epic 8 — Epic 7 owns the backend APIs and conflict table management - [ ] Stage 6 shadow events are owned by Epic 4 — Epic 7 provides the Feature Flag bypass they depend on - [ ] Mock server extensions are defined in [Epic 7.2](tech-spec-7.2.md) and must be complete before Stage 3 --- ## Validation & Testing 1. **Circular update suppression:** Given SuiteX wrote Project A's name (recorded in `write_ledger` with `source_system = 'suitex'`), when a UE event arrives with matching `writeId` or within the configured time threshold, then the event is suppressed and recorded in audit log. 2. **Three-way merge (no conflict):** Given `current_state` at version 5 with `{name: "Alpha", status: "Active"}`, when a SuiteX event (baseVersion=5) changes `name` to "Beta" and RemoteCurrent is at version 6 with `{name: "Alpha", status: "Closed"}`, then merge produces `{name: "Beta", status: "Closed"}` at version 7. 3. **Three-way merge (conflict with per-field policies):** Given `conflict_policy = 'suitex-wins'` for Name and `conflict_policy = 'netsuite-wins'` for Status, when SuiteX changes both Name and Status and NetSuite has concurrently changed Status since the base version, then Name update succeeds (no conflict on Name) and Status resolves to NetSuite's value (conflict resolved by policy). 4. **Unilateral change ignores conflict_policy:** Given `conflict_policy = 'netsuite-wins'` for Status, when SuiteX changes Status and NetSuite has NOT changed Status since the base version, then the SuiteX Status change succeeds — there is no conflict to resolve. 5. **UE/polling deduplication:** Given a UE event was already recorded with a specific fingerprint, when a polling event arrives with the same fingerprint, then it is treated as confirmation (no duplicate `MergedEvent`). 6. **Conflict queueing:** Given a field with `conflict_policy = 'manual'`, when both systems change that field concurrently, then a `conflicts` record is created, `record_lock` is set, and automated writes are blocked. 7. **Feature Flag bypass:** Verify both paths — bypass ON skips HTTP call but updates state; bypass OFF performs full write including mock RESTlet call. 8. **Coalescing buffer:** Verify all three flush conditions. Verify Redis persistence across simulated worker restart. Verify all constituent Pub/Sub messages are ACK'd on flush, not just the last one. 9. **Error routing:** Use mock server error injection to trigger each of the six error classes. Confirm Conflict and Validation errors bypass Pub/Sub and route directly to database tables. Confirm DLQ overflow persists to `sync_error_queue`. 10. **Idempotency key multi-tenancy:** Construct keys for two different `accountId` values with identical remaining segments — confirm keys are distinct. Construct without `accountId` — confirm collision. 11. **Terminology sweep:** Search all new service classes, job classes, and model classes for `eventType` — expect zero matches. 12. **Mock server end-to-end:** Full lifecycle test per [Epic 7.2 Validation #9](tech-spec-7.2.md) — UE event injection → Merge Service → RemoteCurrent fetch → three-way merge → write with marker fields → polling event injection → deduplication. All against the mock server with zero live NetSuite calls. --- ## Related Files - [Design document](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook) — Authoritative source; [Appendix E](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook#appendix-e--topic--consumer-map) defines the consumer responsibility map; [Appendix F1–F2](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook#f1-merge-orchestrator) contains the merge orchestrator and NetSuite Writer pseudocode; [Appendix O6](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook#o6-suitex-retry-policy) contains the retry policy table - [Epic 7 design-spec](design-spec.md) — Architecture, functional requirements, and acceptance scenarios for this epic - [Epic 7 gap analysis](design-spec-gap-analysis.md) — 13 discrepancies against the design document, all incorporated as requirements in this ticket - [Epic 1 tech-spec](../epic1/tech-spec.md) — Canonical event envelope schema and `field_metadata` DDL ([Appendix C](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook#appendix-c--canonical-data-models-json-schema)); `ConflictResolver` reads `field_metadata.conflict_policy` - [Epic 4 design-spec](../epic4/design-spec.md) — Shadow event emission strategy; the Feature Flag bypass is a direct dependency for Epic 4's Strangler Fig migration - [Epic 7.2 — Mock Server Extensions](tech-spec-7.2.md) — Standalone task defining six mock server extensions required for Epic 7 development; blocks Stage 3 - [Epic 3 design-spec](../epic3/design-spec.md) — Node.js Mock Server base scope (write endpoints, poller, chaos middleware) - [Epic 8 design-spec](../epic8/design-spec.md) — Conflict UI backend API shapes and `sync_error_queue` consumer ([Appendix D](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook#appendix-d--erd-mermaid-format)); Epic 7 owns the backend contract that Epic 8's dashboard reads --- ## Technical Notes ### Class Inventory | Directory | Class | Responsibility | |---|---|---| | `src/App/Services/Sync/` | `MergeService.php` | Orchestrates validation, deduplication, coalescing, circular update suppression, `MergedEvent` emission | | `src/App/Services/Sync/` | `ConflictResolver.php` | Per-field conflict policy engine; accepts `(base, remote, local)`, returns `MergedPayload` or `ConflictResult` | | `src/App/Services/Sync/` | `WriteLedgerService.php` | Write ledger reads/writes for circular update suppression | | `src/App/Services/Sync/` | `IdempotencyService.php` | Key generation (six-segment format with `accountId`) and enforcement | | `src/App/Services/Sync/` | `FingerprintService.php` | UE/polling deduplication via SHA-256 fingerprint of `(recordType, recordId, operation, changes)` | | `src/App/Services/Sync/Writers/` | `NetSuiteWriter.php` | Governance-aware outbound writer; Feature Flag bypass; fetches `RemoteCurrent`, performs three-way merge | | `src/App/Services/Sync/Writers/` | `SuiteXWriter.php` | Applies NetSuite-originated changes to tenant DB; updates `current_state` | | `src/App/Jobs/Sync/` | `ProcessRawEvent.php` | Subscribes to `events.raw`; invokes `MergeService` pipeline | | `src/App/Jobs/Sync/` | `ApplyMergedEvent.php` | Subscribes to `events.merged`; dispatches to `NetSuiteWriter` or `SuiteXWriter` based on `source` | | `src/App/Jobs/Sync/` | `ProcessErrorEvent.php` | Subscribes to `events.error`; applies retry policy table; routes to DLQ or `sync_error_queue` | | `src/Domain/Sync/Models/` | `CurrentState.php` | Maps to `current_state` table | | `src/Domain/Sync/Models/` | `Conflict.php` | Maps to `conflicts` table | | `src/Domain/Sync/Models/` | `WriteLedgerEntry.php` | Maps to `write_ledger` table | | `src/Domain/Sync/Models/` | `IdempotencyKey.php` | Maps to `idempotency_keys` table | | `src/Domain/Sync/Models/` | `RecordLock.php` | Maps to `record_lock` table | ### `current_state` DDL (tenant database) ```sql CREATE TABLE current_state ( account_id BIGINT UNSIGNED NOT NULL, record_type VARCHAR(100) NOT NULL, record_id VARCHAR(255) NOT NULL, version BIGINT UNSIGNED NOT NULL DEFAULT 1, state JSON NOT NULL COMMENT 'Full current field-value map for this record', last_modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, last_event_id CHAR(36) NULL COMMENT 'UUID of the last event that updated this projection', PRIMARY KEY (account_id, record_type, record_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; ``` Optimistic locking pattern — Writers must use: ```sql UPDATE current_state SET version = version + 1, state = ?, last_event_id = ? WHERE account_id = ? AND record_type = ? AND record_id = ? AND version = ?; -- Zero rows affected = concurrent write conflict; re-read and retry ``` ### Supporting Table DDLs (tenant database) ```sql CREATE TABLE write_ledger ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, account_id BIGINT UNSIGNED NOT NULL, record_type VARCHAR(100) NOT NULL, record_id VARCHAR(255) NOT NULL, field VARCHAR(255) NOT NULL, last_write_id CHAR(36) NOT NULL, last_write_source VARCHAR(50) NOT NULL COMMENT 'suitex or netsuite', last_write_timestamp TIMESTAMP(6) NOT NULL, UNIQUE KEY uk_record_field (account_id, record_type, record_id, field), INDEX idx_write_id (last_write_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE idempotency_keys ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, target VARCHAR(50) NOT NULL COMMENT 'suitex or netsuite', idempotency_key VARCHAR(500) NOT NULL, event_id CHAR(36) NOT NULL, first_seen_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, last_seen_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, status ENUM('pending','completed','failed_permanent') NOT NULL DEFAULT 'pending', last_result JSON NULL, UNIQUE KEY uk_target_key (target, idempotency_key), INDEX idx_event_id (event_id), INDEX idx_first_seen (first_seen_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Retention: minimum 30 days. Add a scheduled pruning job or soft-delete sweep. CREATE TABLE record_lock ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, account_id BIGINT UNSIGNED NOT NULL, record_type VARCHAR(100) NOT NULL, record_id VARCHAR(255) NOT NULL, locked BOOLEAN NOT NULL DEFAULT TRUE, locked_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, reason VARCHAR(255) NOT NULL, conflict_id CHAR(36) NULL COMMENT 'FK to conflicts table if lock is due to unresolved conflict', UNIQUE KEY uk_record (account_id, record_type, record_id), INDEX idx_locked (locked) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; ``` ### Retry Policy Table | Error Class | Max Retries | Backoff Schedule | Routing Destination | |---|---|---|---| | Transient (network, timeout) | 10 | Exponential: 1s → 2s → 4s → … → 5m cap | `events.error` (retry) | | 429 Rate Limit | 5 | Fixed: 60s, 5m, 15m, 1h, 4h | `events.error` (throttle) | | Governance Concurrency Exceeded | 1 | Wait until next calendar day | `events.error` (governance) | | Validation Error | 0 | None | `sync_error_queue` table (human review) | | Auth Error (token expiry) | 3 | Fixed: 5m (allow token refresh cycle) | `events.error` (auth) | | Conflict | 0 | None | `conflicts` table + `record_lock` (human review) | After max retries exhausted: move to `events.dlq` and persist to `sync_error_queue` with `status = 'exhausted'`. ### Implementation Notes **`ConflictResolver` is a shared service, not a Worker.** Both `NetSuiteWriter` and `SuiteXWriter` invoke `ConflictResolver`. It must be a stateless service class (not a job) that accepts a three-way merge input (`base`, `remote`, `local`) and returns a `MergedPayload` or a `ConflictResult`. Its interface must be defined before either Writer is implemented. --- **`conflict_policy` semantics.** The policy determines how to resolve a field when both the local event and the remote system have changed it since the base version. It does NOT act as a write permission gate. If only one side changed a field, that change is applied regardless of the policy value. The policies are: - `netsuite-wins`: On conflict, accept the NetSuite value. - `suitex-wins`: On conflict, accept the SuiteX value. - `last-write-wins`: On conflict, accept whichever change has the later timestamp. - `manual`: On conflict, queue for human review; lock the record via `record_lock`. --- **Feature Flag and Shadow Events interaction.** The Feature Flag bypass is the mechanism that makes the Strangler Fig migration safe. When an account is in bypass mode, `current_state` is built from shadow events (Epic 4) without any NetSuite writes. When the account is graduated (flag enabled), the `current_state` projection is already warm and writes begin from a consistent state. Document this interaction explicitly. --- **Idempotency key `accountId` prefix.** The [V10 idempotency key format](https://github.com/SuiteDynamics/SuiteX/wiki/Design-%7C-NetSuite-Sync-%E2%80%94-Design,-Requirements,-Deliverables,-and-Runbook#p21-idempotency-key-schema) defines `::::` without `accountId`. This is a V10 design gap — NetSuite `internalId` values are scoped per-account, not globally unique. The corrected six-segment format `:::::` has been adopted in the design spec. --- **RemoteCurrent fetch mechanism.** `RemoteCurrent` is obtained via a live API read from NetSuite — not a stored snapshot or custom record. The NetSuite Writer performs a **read-verify-update** pattern: before writing, it issues a RESTlet `record.load()` or SuiteTalk REST `GET /record/v1/{recordType}/{recordId}` call to retrieve the target record's current field values. The response is the authoritative `RemoteCurrent`. This read must fetch only the fields tracked in `field_metadata` (where `is_synced = true`) to minimize governance cost and payload size. Each write effectively costs 2 API calls (1 GET + 1 PUT/POST), which must be factored into the adaptive concurrency budget. There is a small atomicity gap between the read and the subsequent write — another actor could modify the record in between. NetSuite's built-in `lastModifiedDate` concurrency check on update will reject the write if this occurs, and the Writer must catch this as a transient error and retry. **SOAP is not used** — NetSuite is sunsetting SOAP Web Services; all new integration code must use RESTlet or SuiteTalk REST API endpoints exclusively. --- **`idempotency_keys` database placement.** Depending on the database architecture, `idempotency_keys` may belong in the landlord database (shared across tenants, keyed by `target`) rather than per-tenant. Confirm the target database connection and add the appropriate comment to the DDL migration file. --- **Mock server development strategy.** All Epic 7 consumers and integration tests target the Epic 3 mock server — no live NetSuite calls are made during development. The `NetSuiteWriter` must accept an injectable HTTP client (or base URL configuration) so it can be pointed at the mock server in development/test and at the real NetSuite RESTlet in production. Mock server extension requirements, technical specifications, and acceptance criteria are defined in [Epic 7.2](tech-spec-7.2.md).