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/server.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);
-- Hot path: logs page subscribes to latest logs ordered by JSON ts.
-- Without this, every log write makes UI re-fetch + sort all logs.
CREATE INDEX IF NOT EXISTS idx_docs_parent_ts ON docs(parent, CAST(json_extract(data, '$.ts') AS NUMERIC));
CREATE INDEX IF NOT EXISTS idx_docs_parent_encounter_ts ON docs(parent, json_extract(data, '$.encounterPath'), CAST(json_extract(data, '$.ts') AS NUMERIC));
`;

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 = ?');

  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, { where, orderBy, dir = 'asc', limit, offset } = {}) {
    let sql = 'SELECT path, data FROM docs WHERE parent = ?';
    const params = [collPath];
    if (where) {
      if (!/^[a-zA-Z_][a-zA-Z0-9_]*$/.test(where.field)) throw new Error(`bad where field: ${where.field}`);
      if (where.op !== '==') throw new Error(`unsupported where op: ${where.op}`);
      sql += ` AND json_extract(data, '$.${where.field}') = ?`;
      params.push(where.value);
    }
    if (orderBy) {
      // whitelist field name to avoid injection (alphanumeric + underscore only)
      if (!/^[a-zA-Z_][a-zA-Z0-9_]*$/.test(orderBy)) throw new Error(`bad orderBy field: ${orderBy}`);
      sql += ` ORDER BY CAST(json_extract(data, '$.${orderBy}') AS NUMERIC) ${dir === 'desc' ? 'DESC' : 'ASC'}`;
    } else {
      sql += ' ORDER BY path ASC';
    }
    if (limit && Number.isInteger(+limit) && +limit > 0) {
      sql += ' LIMIT ?';
      params.push(+limit);
      if (offset && Number.isInteger(+offset) && +offset > 0) {
        sql += ' OFFSET ?';
        params.push(+offset);
      }
    }
    return db.prepare(sql).all(...params).map(row => ({ id: row.path.split('/').pop(), path: row.path, ...JSON.parse(row.data) }));
  }

  function countCollection(collPath) {
    return db.prepare('SELECT COUNT(*) AS n FROM docs WHERE parent = ?').get(collPath).n;
  }

  // Bulk delete whole collection or by where-filter. No fetch. SQL knows paths.
  // DEV ONLY — guarded at HTTP layer; db fn itself unguarded (server-internal trust).
  function deleteCollection(collPath, { where } = {}) {
    let sql = 'DELETE FROM docs WHERE parent = ?';
    const params = [collPath];
    const changed = [];
    if (where) {
      if (!/^[a-zA-Z_][a-zA-Z0-9_]*$/.test(where.field)) throw new Error(`bad where field: ${where.field}`);
      if (where.op !== '==') throw new Error(`unsupported where op: ${where.op}`);
      sql += ` AND json_extract(data, '$.${where.field}') = ?`;
      params.push(where.value);
    }
    // collect paths+parents for broadcast before delete
    const rows = db.prepare('SELECT path, parent FROM docs WHERE parent = ?' + (where ? ` AND json_extract(data, '$.${where.field}') = ?` : '')).all(...params);
    const info = db.prepare(sql).run(...params);
    if (broadcast) rows.forEach(r => broadcast({ path: r.path, parent: r.parent, deleted: true }));
    return info.changes;
  }

  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));
  }

  // Transactional undo: apply encounter updates + flip log `undone` in one tx.
  // No stale clobber, atomic. Returns { undone: true } or throws.
  function transactionalUndo({ logPath, encounterPath, updates, undoneFlag }) {
    const run = db.transaction(() => {
      const merged = updateDoc(encounterPath, updates);
      const log = getDoc(logPath) || {};
      setDoc(logPath, { ...log, undone: undoneFlag });
      return { encounter: merged, undone: undoneFlag };
    });
    const result = run();
    if (broadcast) {
      broadcast({ path: encounterPath, parent: parentOf(encounterPath) });
      broadcast({ path: logPath, parent: parentOf(logPath) });
    }
    return result;
  }

  return { getDoc, setDoc, updateDoc, deleteDoc, getCollection, countCollection, deleteCollection, batchWrite, transactionalUndo };
}

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