DOCUMENTATION / ARCHITECTURE
Architecture
SyncCoordinator consists of Web, Worker, and a management database. Web manages configuration, Worker synchronizes business databases, and the management database keeps configuration and processing state.
OVERVIEW
System topology
Deploy SyncCoordinator separately from the business systems. Web manages configuration, Worker executes synchronization, and the management database stores configuration and processing state.
Component responsibilities
Coordinator Web
Provides the management console and administrator authentication. It edits connections, rules, and mappings, and generates and verifies database deployment SQL. It does not execute synchronization.
Coordinator Worker
Loads configuration from the management database, reads changes, evaluates conflicts, and applies results. It also delivers webhooks and cleans retained management data.
Coordinator management database
Stores connections, rules, mappings, Checkpoints, Inbox state, Snapshots, conflict history, and the other state required to resume processing.
COMPATIBILITY
Supported databases
These versions apply to business databases synchronized by SyncCoordinator. Each database requires permissions to create and remove the synchronization support tables and change-detection triggers.
| Database | Supported version | Deployment requirement |
|---|---|---|
| SQL Server | 2016 SP1 or later | Permissions to create triggers on the target table and create or remove synchronization support tables |
| MySQL | 8.0 or later | The TRIGGER privilege and permissions to create or remove synchronization support tables |
| PostgreSQL | 13 or later | The TRIGGER privilege on the target table and permissions to create or remove synchronization support objects |
SYNC RULES AND MAPPING
Synchronization rules and mappings
A rule defines source, destination, direction, and conflict behavior. Mappings attached to the rule describe differences in tables, columns, and values.
DIRECTION
One-way and bidirectional
- One-wayProcesses changes from the fixed source to the fixed destination.
- BidirectionalReturns edits made at the destination to the original source for records first synchronized from that source.
- Loop preventionTracks the origin and applied message IDs so SynCo does not resend its own updates.
MAPPING
Tables, columns, and values
- Tables and keysSelect the schema, table, and key columns that identify a record at each end.
- Columns and typesStore column pairs, nullability, string length, precision, and scale.
- Value conversionConfigure code mappings, UTC normalization, explicit rounding or truncation, and direction-specific fixed values.
When configuration changes take effect
- Start cycleWorker creates a processing scope.
- Load connectionsIt reads enabled systems and protected connection data.
- SynchronizeConnections stay stable for that cycle.
- Next cycleSaved changes take effect without restarting Worker.
CONFLICTS AND NOTIFICATIONS
Conflicts and notifications
Worker evaluates conflicts and delivers external notifications. Decisions and delivery state remain in the management database and are visible in the console.
CONFLICT
When a conflict occurs
- DetectCompare the previous Snapshot, incoming value, and current destination value. A conflict exists when both sides changed the same field to different values.
- Apply policyUse the incoming value, keep the destination, hold for review, or invoke a configured merge policy.
- Resolve when neededHeld conflicts appear in the console. Worker verifies the destination again before applying a manual decision.
NOTIFICATION
Notification flow
- Store the eventConflict and failure events are written to the management database Outbox.
- Send a webhookWorker asynchronously delivers the event to configured webhook endpoints.
- Retry failuresFailed deliveries are retried with delay. Notification failure does not stop synchronization.
BUSINESS DATABASE BOUNDARY
Business database deployment
Add helper objects for change detection and duplicate-apply prevention. Existing application code, business tables, and business columns remain unchanged.
DEPLOYED OBJECTS
- SyncChangeQueue
- SyncAppliedMessage
- SyncEntityOrigin
- SyncDeleteTombstone
- SyncCoordinatorDeployment
- Change-detection triggers for mapped tables
UNCHANGED
- Existing business applications
- Existing business tables
- Existing business columns
Saving configuration does not execute DDL. A DBA can review and run generated SQL, then verify the deployed definitions before enabling the rule.
Initial data: SyncCoordinator synchronizes changes made after deployment. Align existing data by another method before enabling synchronization.
STATE AND RELIABILITY
State and reliable execution
Business data remains in each business database. Synchronization configuration and processing state live in the Coordinator management database. Worker keeps no durable local state and resumes from saved state after a restart.
| Location | Main state | Purpose |
|---|---|---|
| Coordinator management DB | Systems, rules, mappings, Checkpoints, Inbox, Snapshots, conflicts, audit, and operational history | The source of truth for configuration and processing state. |
| Business DB | Business rows, change Queue, applied messages, origin, delete tombstones, and deployment hash | Current business data plus the helper state used for detection and idempotency. |
Restart and redelivery
Stores the last processed QueueId per source. A failed source does not advance.
Tracks Processing, Completed, Held, and Failed. Work interrupted by shutdown can be acquired again after its lease expires.
Records a deterministic delivery ID in SyncAppliedMessage so retrying the same delivery does not apply it twice.
SyncChangeQueue records that a row changed, not the changed value itself. On resume, Worker reads the row again and synchronizes its latest state.
SECURITY AND OPERATIONS
Security and operational access
Coordinator management DB holds business-database connection data and synchronization configuration. In production, protect secrets, console access, and database privileges independently.
Business database connection strings and webhook signing secrets are encrypted with ASP.NET Core Data Protection before they are stored in the management database.
Publish the management console through HTTPS. When Web and Worker are separate, provide a protected, backed-up Key Ring both can read.
Administrator setup and password reset are available only when both the client and requested host are localhost or a loopback address.
Separate Worker read/write access from the DDL permission used to create helper tables and triggers. A DBA can review and run generated SQL.
NEXT