@kido/async-syncCopy Anchor Link

Architecture Paradigm: Dual-Write Elimination via Native PostgreSQL Logical Replication WAL Slot Polling + Two-Phase Peek-then-Ack + Litestream SQLite Continuous PITR + RFC 9562 Monotonic UUIDv7 Outbox + WAL Disk Safety Circuit Breaker.

💥 The Real-World BottleneckCopy Anchor Link

Data synchronization across offline POS terminals, search indices, read replicas, and microservices fails catastrophically when relying on naive patterns:

1. The Synchronous Dual-Write Trap (Data Drift):

   Copy Text

   // The Naive Disaster: Writing to PostgreSQL and Search Index simultaneously
   await db.orders.create({ data: orderData });
   await elasticsearch.index({ index: 'orders', document: orderData }); // If this throws, DB and Search drift permanently!

   Network glitches, timeout spikes, or deployment restarts cause permanent divergence between primary databases and downstream search indexes or caches.
2. PostgreSQL WAL Slot Disk Exhaustion: When downstream consumers crash, PostgreSQL logical replication slots continue holding unconsumed WAL segments on disk. Within hours, the database server runs out of disk space (pg_wal 100% full), triggering a complete database emergency shutdown.
3. Offline POS Network Outages & Unordered Keys: Traditional UUIDv4 IDs create severe B-Tree page fragmentation in SQLite/PostgreSQL. During store internet outages, offline sales either fail completely or trigger update conflicts upon reconnection.

🏛️ 3-Tier Production Evolution LadderCopy Anchor Link

📦 Architecture & Core API ExportsCopy Anchor Link

@kido/async-sync contains 6 cohesive, decoupled slices:

1. Monotonic UUIDv7 & Outbox Queue (src/uuidv7.ts, src/outbox-queue.ts)Copy Anchor Link

• uuidv7(): RFC 9562 compliant 128-bit timestamp-ordered UUID generator. Eliminates B-Tree page splits and provides natural chronological sorting.
• OutboxQueue: Transactional outbox buffer with exponential backoff and jitter. Guarantees At-Least-Once delivery to remote APIs.

2. Conflict Resolver & Offline POS (src/conflict-resolver.ts, src/offline-pos-store.ts)Copy Anchor Link

• ConflictResolver: Multi-master conflict resolution supporting Last-Write-Wins (LWW), Vector Clocks, and append-only ledger merging.
• OfflinePOSStore: Complete local retail engine generating K80 thermal receipts and VietQR payment strings even when fully disconnected from the internet.

3. Litestream Continuous PITR (src/litestream-pitr/)Copy Anchor Link

• LitestreamPitrEngine: Continuous SQLite WAL replication engine streaming 16KB frame chunks to Cloudflare R2 / AWS S3 for zero-loss Point-In-Time-Recovery.

4. PostgreSQL Logical CDC Stream (src/pg-cdc-stream/)Copy Anchor Link

• Tier1NativeWalSlotPoller: Lightweight TypeScript worker polling native PostgreSQL replication slots without needing Java, JVM, or Kafka containers (< 15 MB RAM).
• coalesceCdcBatch(events): Compresses rapid bursts of updates on matching keys into a single final state update.
• evaluateCdcSlotDiskSafety(retainedBytes, thresholdBytes): Circuit breaker monitoring retained WAL bytes to protect database storage.

5. Partition Archiving & Cold Tier Offload (src/pg-partition-archiver/)Copy Anchor Link

• ColdTierPartitionArchiver: Offloads historical monthly partitions to S3/R2 compressed as NDJSON Gzip.
• buildDetachPartitionSql(table, partition): Emits non-blocking ALTER TABLE ... DETACH PARTITION CONCURRENTLY queries.

💻 Production Implementation & Code SnippetCopy Anchor Link

Native PostgreSQL WAL CDC Stream with Two-Phase Peek-then-AckCopy Anchor Link

import { 
  Tier1NativeWalSlotPoller, 
  coalesceCdcBatch,
  evaluateCdcSlotDiskSafety,
  uuidv7 
} from '@kido/async-sync';

// 1. Initialize Lightweight Native WAL Slot Poller (< 15 MB RAM)
const cdcPoller = new Tier1NativeWalSlotPoller({
  slotName: 'kido_cdc_slot',
  plugin: 'wal2json',
  batchSize: 500,
  pollIntervalMs: 200,
  async executeSql(query, params) {
    return await pgPool.query(query, params);
  },
});

// 2. Start Two-Phase Polling Loop (Peek -> Process -> Ack)
export async function startCdcWorker() {
  await cdcPoller.start(async (rawChanges) => {
    // Step A: Disk Safety Check (Prevents pg_wal 100% full crisis)
    const safety = evaluateCdcSlotDiskSafety({
      retainedWalBytes: cdcPoller.getRetainedWalBytes(),
      maxAllowedBytes: 10 * 1024 * 1024 * 1024, // 10 GB threshold
    });

    if (!safety.isSafe) {
      console.error('CRITICAL: Dropping slot to save database storage!');
      return;
    }

    // Step B: Coalesce rapid price/inventory bursts
    const coalesced = coalesceCdcBatch(rawChanges);

    // Step C: Update Search Index & Evict Cache
    for (const change of coalesced) {
      if (change.table === 'products') {
        await updateSearchIndex(change.rowId, change.data);
        await redis.del(`product:${change.rowId}`);
      }
    }

    // Returning successfully triggers Phase 2: confirmed_flush_lsn advances in PostgreSQL
  });
}

// 3. Offline Outbox Generation Example
export function createOrderRecord(items: any[]) {
  const orderId = uuidv7(); // Guaranteed timestamp-ordered monotonic ID
  return {
    id: orderId,
    items,
    createdAt: new Date().toISOString(),
  };
}

⚡ Benchmark & Verified k6 PerformanceCopy Anchor Link

Benchmarked against heavyweight Debezium Kafka connector on a 4-vCPU database instance:

🚀 Surgical Extraction & CLI InstallationCopy Anchor Link

# Install directly via Kido CLI
npx @kido/cli add @kido/async-sync