sqlite_shadow_sync 1.0.0
sqlite_shadow_sync: ^1.0.0 copied to clipboard
Zero-touch, trigger-driven, column-level CRDT offline-first synchronization engine for SQLite (drift, sqflite, sqlite3).
sqlite_shadow_sync #
A zero-touch, trigger-driven, column-level CRDT (Last-Write-Wins Element-Set via Hybrid Logical Clocks) offline-first synchronization engine for SQLite (drift, sqflite, and sqlite3).
Why This Architecture is Unique #
- Zero User Schema Pollution: User tables are never altered with
hlc,node_id, oris_deletedcolumns. All synchronization state lives in isolated_sync_*shadow tables, keeping nativedriftandsqflitemigrations 100% intact. - Dynamic SQLite Trigger Capture: On initialization, the engine inspects registered user tables via
PRAGMA table_info(table_name)and dynamically generatesAFTER INSERT,AFTER UPDATE, andAFTER DELETESQLite triggers. - Loop-Safe Remote Pulls (Trigger Suppression): Every generated trigger checks:
When applying remote deltas from the server,WHEN (SELECT is_pulling FROM _sync_node_state WHERE id = 1) = 0is_pullingis set to1inside an exclusive transaction so incoming server updates never re-fire local outbox triggers. - Column-Level Conflict Resolution & Outbox Compaction: Updates are tracked per-column (
OLD.col IS NOT NEW.col). Repeated offline edits to the same(table_name, row_id, column_name)compact in-place viaON CONFLICT DO UPDATEin the outbox. - 100% WebAssembly (Wasm) Compatible: Pure Dart with zero
dart:ioordart:htmlimports inlib/. Full multi-platform compatibility across Android, iOS, Web/Wasm, macOS, Windows, and Linux.
Architecture Overview #
flowchart TD
subgraph Client Application
App["User App Code / Drift / Sqflite"]
UserTable[("User SQLite Table\n(e.g., crm_contacts)")]
end
subgraph SQLite Trigger Layer
TrgIns["AFTER INSERT Trigger"]
TrgUpd["AFTER UPDATE Trigger\n(OLD.col IS NOT NEW.col)"]
TrgDel["AFTER DELETE Trigger"]
SuppressionCheck{"is_pulling == 0?"}
end
subgraph Shadow Tables
Outbox[("_sync_outbox\n(In-Place Compaction)")]
ColumnClock[("_sync_column_clock\n(HLC per column)")]
Tombstones[("_sync_tombstones\n(Causal deletions)")]
NodeState[("_sync_node_state\n(is_pulling, node_id)")]
end
subgraph Sync Engine
Orchestrator["SyncOrchestrator\n(Chunking, Backoff, State Machine)"]
MergeEng["MergeEngine\n(Column-level LWW, Trigger Suppression)"]
end
subgraph Remote
Transport["SyncTransport\n(HTTP / WebSocket / In-Memory)"]
Server[("Central Sync Server\n(e.g., PostgreSQL / ASP.NET)")]
end
App -->|"Regular SQL INSERT/UPDATE/DELETE"| UserTable
UserTable --> TrgIns & TrgUpd & TrgDel
TrgIns & TrgUpd & TrgDel --> SuppressionCheck
SuppressionCheck -->|Yes| Outbox & ColumnClock & Tombstones
SuppressionCheck -->|No (Remote Pull)| NodeState
Orchestrator -->|"Push chunked outbox deltas"| Transport
Transport -->|"Pull paginated server deltas"| Orchestrator
Orchestrator -->|"Set is_pulling=1 & Merge"| MergeEng
MergeEng -->|"Update winning columns"| UserTable
Transport <--> Server
Synchronization Protocol Flow #
sequenceDiagram
autonumber
participant App as Flutter / Dart Client
participant LocalDB as SQLite (User + Shadow Tables)
participant Engine as SyncOrchestrator & MergeEngine
participant Server as Central Sync Server (PostgreSQL)
Note over App,LocalDB: Offline Local Edits
App->>LocalDB: UPDATE crm_contacts SET company = 'Antigravity' WHERE id = 'c1';
LocalDB->>LocalDB: Trigger: OLD.company IS NOT NEW.company
LocalDB->>LocalDB: Upsert into _sync_outbox (Compacts offline edits)
LocalDB->>LocalDB: Upsert into _sync_column_clock
Note over Engine,Server: Synchronization Cycle
Engine->>LocalDB: Query _sync_outbox (LIMIT pushBatchSize)
Engine->>Server: POST /api/sync/push (SyncBatch)
Server->>Server: PL/pgSQL fn_merge_column_delta (LWW)
Server-->>Engine: PushResult (Success: true, ServerHlc)
Engine->>LocalDB: DELETE FROM _sync_outbox WHERE hlc = delta.hlc
Engine->>LocalDB: Query last_pulled_hlc watermark
Engine->>Server: GET /api/sync/pull?sinceHlc=watermark
Server-->>Engine: SyncBatch (remote deltas)
rect rgb(240, 248, 255)
Note over Engine,LocalDB: Trigger-Suppressed Merge Pass
Engine->>LocalDB: BEGIN IMMEDIATE; UPDATE _sync_node_state SET is_pulling = 1;
Engine->>LocalDB: Compare remote HLC vs _sync_column_clock (LWW)
Engine->>LocalDB: UPDATE user table for winning columns
Engine->>LocalDB: UPDATE _sync_node_state SET is_pulling = 0; COMMIT;
end
Installation #
Add sqlite_shadow_sync to your pubspec.yaml:
dependencies:
sqlite_shadow_sync: ^1.0.0
sqlite3: ^3.8.0 # or sqflite / drift
Or run:
dart pub add sqlite_shadow_sync
Quick Start #
import 'package:sqlite3/sqlite3.dart';
import 'package:sqlite_shadow_sync/sqlite_shadow_sync.dart';
Future<void> main() async {
// 1. Wrap your SQLite database (native or wasm)
final rawDb = sqlite3.openInMemory();
final db = Sqlite3Executor(rawDb);
// 2. Initialize sync shadow tables and register this device's unique node ID
await ShadowTables.createSchema(db);
await ShadowTables.initNodeState(db, 'client-device-uuid-123');
// 3. Create your user tables normally (no extra columns needed!)
await db.execute('''
CREATE TABLE contacts (
id TEXT PRIMARY KEY,
name TEXT NOT NULL,
email TEXT NOT NULL,
company TEXT
);
''');
// 4. Attach dynamic SQLite capture triggers
await TriggerGenerator.attachTriggers(db, ['contacts']);
// 5. Setup your transport (e.g. HTTP, WebSocket, or InMemory for testing)
final transport = InMemorySyncTransport(serverNodeId: 'cloud-server');
// 6. Instantiate the Sync Orchestrator
final orchestrator = SyncOrchestrator(
db: db,
transport: transport,
pushBatchSize: 100,
pullBatchSize: 500,
initialRetryDelay: const Duration(seconds: 1),
maxRetryDelay: const Duration(seconds: 30),
);
// Listen to state changes
orchestrator.statusStream.listen((status) {
print('Sync Status: $status');
});
// 7. Make local mutations using standard SQL or Drift/Sqflite
await db.execute('''
INSERT INTO contacts (id, name, email, company)
VALUES ('c1', 'Jane Doe', 'jane@example.com', 'Acme Corp');
''');
// 8. Execute synchronization
final stats = await orchestrator.synchronizeWithRetry();
print('Sync completed: pushed ${stats.deltasPushed}, pulled ${stats.deltasPulled} in ${stats.duration.inMilliseconds}ms');
// 9. Cleanup when done
await orchestrator.dispose();
await db.close();
}
Column-Level Conflict Resolution (LWW) #
When two offline devices edit the same row simultaneously:
- Client A edits
nameto"Jane Smith"atT1. - Client B edits
companyto"Antigravity"atT2.
Because sqlite_shadow_sync tracks Hybrid Logical Clocks per column:
- Both edits survive!
- When synchronized, the merged row becomes:
{ "id": "c1", "name": "Jane Smith", "email": "jane@example.com", "company": "Antigravity" } - If both clients edit the same column, the change with the higher
Hlctimestamp deterministically wins, and aSyncConflictrecord is logged.
Backend Reference Architecture (.NET & PostgreSQL) #
The repository includes a complete, production-ready backend reference implementation under backend_examples/dotnet_postgres/:
schema_and_merge.sql: PostgreSQL shadow schema + PL/pgSQLfn_merge_column_deltafunction implementing column-level LWW merge directly in the database.SyncEndpoints.cs: ASP.NET Core (.NET 8/10) Minimal API controller usingNpgsqlandDapperwith endpoints:POST /api/sync/push: Receives clientSyncBatch, executes transactional merge, and returnsPushResult.GET /api/sync/pull: Paginated change feed queryable viasinceHlcwatermark.
Verification & Testing #
Run static analysis and the comprehensive test suite:
# Verify 0 lints, 0 warnings
dart analyze
# Run unit and end-to-end multi-node convergence tests
dart test
# Run the multi-node sync demonstration example
dart run example/main.dart
License #
MIT License. See LICENSE for details.