Skip to content

offline-sync-patterns.md

Latest commit

 

History

History
151 lines (107 loc) · 5.37 KB

offline-sync-patterns.md

File metadata and controls

151 lines (107 loc) · 5.37 KB

Offline Draft Sync Patterns

NodeDB-Lite provides full engine capabilities embedded in phones, browsers (WASM), and desktops. CRDT sync via WebSocket keeps local data converged with Origin. This guide covers patterns for handling provisional numbering, state transitions, and offline-first workflows.

Provisional → Official Numbering Handoff

Problem: Official document numbers (invoices, receipts, POs) must be sequential and gap-free per legal requirements. But offline devices can't reserve numbers from the Origin server.

Pattern:

  1. Offline (NodeDB-Lite): Create documents with provisional ULID-based identifiers:
INSERT INTO invoices (invoice_number, customer_id, amount, status)
VALUES ('DRAFT-01JXQ3...', 'cust-005', 750.00, 'draft');

The DRAFT- prefix signals this is not an externally-issued number. The app enforces: print/email blocked while invoice_number LIKE 'DRAFT-%'.

  1. Sync to Origin: When the device comes online, CRDT sync pushes the draft to Origin.

  2. Origin assigns official number: A BEFORE INSERT trigger or application logic on Origin:

    • Validates the customer exists, credit limit is OK
    • Calls nextval('invoice_seq') to get the gap-free sequential number
    • Replaces the provisional DRAFT-* identifier with the official number (e.g., INV-26-04-00024)
    • Posts GL entries atomically

  3. Sync back to device: The official number propagates back via CRDT sync. The rep can now issue the invoice to the customer.

Key safeguard: A state constraint prevents issuing drafts:

ALTER COLLECTION invoices ADD CONSTRAINT no_draft_issue
    ON COLUMN status TRANSITIONS (
        'draft' -> 'issued'
    );

-- Plus a TRANSITION CHECK ensuring the number is official:
ALTER COLLECTION invoices ADD TRANSITION CHECK official_number_required (
    OLD.status != 'draft' OR NEW.invoice_number NOT LIKE 'DRAFT-%'
);

This ensures draft → issued only succeeds when the invoice number has been replaced with an official one by Origin.

State Constraint: draft → issued

The state machine for invoices in an offline-first system:

ALTER COLLECTION invoices ADD CONSTRAINT invoice_flow
    ON COLUMN status TRANSITIONS (
        'draft'     -> 'submitted',
        'submitted' -> 'approved'    BY ROLE 'manager',
        'submitted' -> 'draft',
        'approved'  -> 'issued'      BY ROLE 'accountant',
        'issued'    -> 'voided'      BY ROLE 'controller'
    );

On the device (NodeDB-Lite):

  • Reps create drafts and submit them
  • draft → submitted is allowed without a role guard
  • submitted → approved requires the manager role — only available on Origin

On Origin:

  • Managers approve, accountants issue, controllers void
  • Each transition is role-guarded and audited
  • The state constraint prevents skipping steps (e.g., draft → issued is not in the list)

Offline Tax Rate Sync

Problem: Tax rates change by jurisdiction and effective date. Offline devices need current rates but can't query Origin in real-time.

Pattern:

  1. Origin maintains the rate table:
CREATE COLLECTION tax_rates (
    id          STRING PRIMARY KEY,
    jurisdiction STRING NOT NULL,
    rate        DECIMAL NOT NULL,
    effective_from STRING NOT NULL
) WITH (engine='document_strict');
  1. Sync subset to Lite: Use CRDT shape subscriptions to sync only relevant jurisdictions:
-- On the device:
SUBSCRIBE SHAPE tax_rates WHERE jurisdiction IN ('US-CA', 'US-NY');
  1. Lookup at invoice time: Use TEMPORAL_LOOKUP (or equivalent local query) to find the rate effective at the invoice date:
SELECT * FROM tax_rates
WHERE jurisdiction = 'US-CA'
  AND effective_from <= '2026-04-01'
ORDER BY effective_from DESC
LIMIT 1;
  1. Reconcile on sync: If a rate changed between offline creation and sync, Origin can flag invoices for review. The app decides whether to recalculate or accept the offline-computed tax.

Key principle: Tax computation logic lives in the application, not the database. NodeDB provides the primitives (temporal lookup, CRDT sync, decimal arithmetic) — the app decides which rate to apply and when to recalculate.

NodeDB-Lite Usage

Creating Offline Invoices (Swift/Kotlin via FFI)

// iOS — via nodedb-lite-ffi
let db = NodeDbLite.open(path: "invoices.db")

// Insert draft with provisional number
db.execute("""
    INSERT INTO invoices (invoice_number, customer_id, amount, status)
    VALUES ('DRAFT-\(ULID())', 'cust-005', 750.00, 'draft')
""")

// Sync when online
db.sync(url: "wss://origin.example.com/sync", token: authToken)

WASM (Browser)

import { NodeDbLite } from "nodedb-lite-wasm";

const db = await NodeDbLite.open("invoices");

await db.execute(`
    INSERT INTO invoices (invoice_number, customer_id, amount, status)
    VALUES ('DRAFT-${crypto.randomUUID()}', 'cust-005', 750.00, 'draft')
`);

// Sync via WebSocket
await db.sync("wss://origin.example.com/sync");

Key Points

  • All eight engines work locally on Lite (document, KV, columnar, timeseries, spatial, vector, array, plus graph and FTS overlays)
  • CRDT sync is transparent — local writes are optimistic, validated at Origin
  • Constraint violations on Origin produce compensation hints in the dead-letter queue
  • The same Decimal type, query engine, and data model work on both Origin and Lite
  • Sub-millisecond local reads with no network dependency