ttrpg-initiative-tracker/server/db.js

// server/db.js — generic KV document store on SQLite.
// Mirrors Firestore doc-tree model: every doc lives at a string path.
// Collections are implicit = all docs whose parent path equals the collection path.
//
// Path examples (canonical, prefix already stripped by adapter):
//   campaigns/{id}                       doc
//   campaigns/{cid}/encounters/{eid}     doc
//   campaigns/{cid}/encounters           collection (parent of encounter docs)
//   activeDisplay/status                 doc
//   logs/{id}                            doc
//
// No shape-specific tables. Data is opaque JSON. This is the firebase mirror:
// the adapter (src/storage/ws.js) is a thin passthrough, app logic unchanged.

'use strict';

const Database = require('better-sqlite3');
const path = require('path');
const fs = require('fs');

const SCHEMA = `
CREATE TABLE IF NOT EXISTS docs (
  path TEXT PRIMARY KEY,
  parent TEXT,
  data TEXT NOT NULL,
  updated_at INTEGER NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_docs_parent ON docs(parent);
`;

function openDb(dbPath) {
  const dir = path.dirname(dbPath);
  if (!fs.existsSync(dir)) fs.mkdirSync(dir, { recursive: true });
  const db = new Database(dbPath);
  db.pragma('journal_mode = WAL');
  db.exec(SCHEMA);
  return db;
}

// parentOf('campaigns/abc/encounters/xyz') => 'campaigns/abc/encounters'
// parentOf('campaigns') => null (root-level doc, no parent collection tracked)
function parentOf(p) {
  const i = p.lastIndexOf('/');
  return i === -1 ? null : p.slice(0, i);
}

function makeStore(db, broadcast) {
  const stmtGet = db.prepare('SELECT data FROM docs WHERE path = ?');
  const stmtUpsert = db.prepare(`
    INSERT INTO docs (path, parent, data, updated_at) VALUES (@path, @parent, @data, @ts)
    ON CONFLICT(path) DO UPDATE SET data = @data, updated_at = @ts
  `);
  const stmtDelete = db.prepare('DELETE FROM docs WHERE path = ?');
  const stmtColl = db.prepare('SELECT path, data FROM docs WHERE parent = ? ORDER BY path ASC');

  function getDoc(p) {
    const row = stmtGet.get(p);
    return row ? JSON.parse(row.data) : null;
  }

  function setDoc(p, data) {
    const ts = Date.now();
    stmtUpsert.run({ path: p, parent: parentOf(p), data: JSON.stringify(data), ts });
    if (broadcast) broadcast({ path: p, parent: parentOf(p) });
    return data;
  }

  // shallow merge; if doc missing, patch becomes the doc (matches firebase updateDoc create-on-miss)
  function updateDoc(p, patch) {
    const existing = getDoc(p) || {};
    const merged = { ...existing, ...patch };
    setDoc(p, merged);
    return merged;
  }

  function deleteDoc(p) {
    stmtDelete.run(p);
    if (broadcast) broadcast({ path: p, parent: parentOf(p), deleted: true });
  }

  function getCollection(collPath) {
    return stmtColl.all(collPath).map(row => ({ id: row.path.split('/').pop(), path: row.path, ...JSON.parse(row.data) }));
  }

  function batchWrite(ops) {
    const run = db.transaction((items) => {
      const changed = [];
      for (const op of items) {
        if (op.type === 'set') {
          setDoc(op.path, op.data);
          changed.push({ path: op.path, parent: parentOf(op.path) });
        } else if (op.type === 'delete') {
          deleteDoc(op.path);
          changed.push({ path: op.path, parent: parentOf(op.path), deleted: true });
        } else if (op.type === 'update') {
          updateDoc(op.path, op.data);
          changed.push({ path: op.path, parent: parentOf(op.path) });
        }
      }
      return changed;
    });
    const changed = run(ops);
    if (broadcast) changed.forEach(c => broadcast(c));
  }

  return { getDoc, setDoc, updateDoc, deleteDoc, getCollection, batchWrite };
}

module.exports = { openDb, parentOf, makeStore };