Node Embedded SQLite: When Local Coordination Beats Redis Ops Cost

// lib/local-coord.ts — better-sqlite3 on ECS/K8s worker
import Database from "better-sqlite3";

const db = new Database("coord.db");
db.pragma("journal_mode = WAL");
db.exec(`
 CREATE TABLE IF NOT EXISTS leases (
 key TEXT PRIMARY KEY,
 owner TEXT NOT NULL,
 expires_at INTEGER NOT NULL
 );
`);

const acquireStmt = db.prepare(`
 INSERT INTO leases(key, owner, expires_at)
 VALUES (@key, @owner, @expires)
 ON CONFLICT(key) DO UPDATE SET
 owner = excluded.owner,
 expires_at = excluded.expires_at
 WHERE leases.expires_at < @now OR leases.owner = @owner
`);

export function tryAcquire(key: string, owner: string, ttlMs: number) {
 const now = Date.now();
 const info = acquireStmt.run({ key, owner, expires: now + ttlMs, now });
 return info.changes === 1;
}

db.exec(`CREATE TABLE IF NOT EXISTS kv (
 k TEXT PRIMARY KEY, v TEXT NOT NULL, exp INTEGER NOT NULL
)`);

export function cacheGet(k: string): string | null {
 const row = db.prepare(`SELECT v, exp FROM kv WHERE k = ?`).get(k) as
 | { v: string; exp: number }
 | undefined;
 if (!row) return null;
 if (row.exp < Date.now()) {
 db.prepare(`DELETE FROM kv WHERE k = ?`).run(k);
 return null;
 }
 return row.v;
}