Skip to content

Self-hosted, Self-Improving, Federated Everything App — dev management, agent orchestration, persistent mind, digital twin, second brain, and creative pipeline from a single dashboard over Tailscale to port the user into the OS

15.9 KB
Raw Blame
/**
 * Mood Board — PostgreSQL-backed store (issue #911).
 *
 * One row per board in `mood_boards`: id / name / created_at / updated_at as
 * columns, the full record (name/description/items[]) in `data` JSONB. Mood
 * boards are db-primary, modeled on the creativeDirector projectsDB store. As of
 * #1564 they FEDERATE across peers via the per-record peer-sync push pipeline
 * (record kind `moodBoard`, sync category `moodBoards`), so a delete is a
 * soft-delete tombstone (deleted/deleted_at + LWW updated_at) the merge keeps an
 * out-of-date peer from resurrecting — mirroring creative_director_projects.
 *
 * Concurrency: the trust model is single-user, but a board has more than one
 * write path that can touch the SAME board (board PATCH + item add/update/remove
 * fired from different UI affordances), and a read-modify-write spanning two
 * pool round-trips would lose updates. So every mutator runs inside
 * withTransaction + `SELECT … FOR UPDATE` — the row lock serializes concurrent
 * writes to one board without blocking writes to other boards.
 *
 * All mutation semantics live in logic.js so the transforms stay unit-testable
 * without a live DB; this module only does row I/O + locking.
 */

import { randomUUID } from 'crypto';
import { query, withTransaction } from '../../lib/db.js';
import { ServerError } from '../../lib/errorHandler.js';
import { mirrorTimestamp } from '../../lib/pgTimestamp.js';
import {
  buildBoardRecord,
  applyBoardPatch,
  applyBoardRestore,
  addItem,
  updateItem,
  removeItem,
  claimItemRender,
  settleItemRender,
  applyRenderedItem,
  projectInterruptedNoteRenders,
  mergeBoardRecord,
  applyPinterestLink,
  healPinterestFeedRecord,
  clearPinterestLinkRecord,
  appendPinterestPins,
  appendImportedItems as appendImportedItemsRecord,
  applyLocalizedImageUrls,
} from './logic.js';
import {
  maybeJournalBeforeOverwrite, setSyncBaseHash, contentHashForRecord, flushBaseHashes, deleteSyncBaseHash,
  withBaseHashFlushBatch,
} from '../../lib/conflictJournal.js';
import { toPgJson } from '../../lib/pgJson.js';

// `data` JSONB is the whole record; name/created_at/updated_at mirror into
// columns (kept in lockstep on every write) for the live-list sort. Reads
// always return `data` verbatim so consumers see the exact stored shape.
function rowToBoard(row) {
  if (!row) return null;
  return row.data;
}

async function persist(exec, board) {
  const now = new Date().toISOString();
  const createdAt = mirrorTimestamp(board.createdAt, now);
  await exec(
    `INSERT INTO mood_boards (id, name, data, created_at, updated_at, deleted, deleted_at)
     VALUES ($1, $2, $3::jsonb, $4, $5, $6, $7)
     ON CONFLICT (id) DO UPDATE SET
       name = EXCLUDED.name,
       data = EXCLUDED.data,
       updated_at = EXCLUDED.updated_at,
       deleted = EXCLUDED.deleted,
       deleted_at = EXCLUDED.deleted_at`,
    [
      board.id,
      board.name,
      toPgJson(board),
      createdAt,
      mirrorTimestamp(board.updatedAt, createdAt),
      board.deleted === true,
      mirrorTimestamp(board.deletedAt, null),
    ],
  );
  return board;
}

export async function listBoards({ includeDeleted = false } = {}) {
  // updated_at DESC — most-recently-touched first (the board grid's default).
  const result = includeDeleted
    ? await query(`SELECT data FROM mood_boards ORDER BY updated_at DESC`)
    : await query(`SELECT data FROM mood_boards WHERE deleted = FALSE ORDER BY updated_at DESC`);
  return result.rows.map(rowToBoard);
}

/** Keep saved board ownership stable until a media retirement has settled. */
export async function withBoardMediaReferences(work) {
  return withTransaction(async client => {
    // Includes every writer, including peer merges: a prior writer commits
    // before this snapshot; later writes wait until retirement releases it.
    await client.query('LOCK TABLE mood_boards IN SHARE MODE');
    const { rows } = await client.query('SELECT data FROM mood_boards WHERE deleted = FALSE');
    return work(rows.map(rowToBoard));
  });
}

/**
 * Live boards as `{ id, name }`, newest first — for pickers that need only the
 * label, without reading and shipping every board's items (up to 500 each).
 */
export async function listBoardNames() {
  const result = await query(`SELECT id, data->>'name' AS name FROM mood_boards WHERE deleted = FALSE ORDER BY updated_at DESC`);
  return result.rows.map((row) => ({ id: row.id, name: row.name || '' }));
}

export async function getBoard(id, { includeDeleted = false } = {}) {
  const result = await query(`SELECT data FROM mood_boards WHERE id = $1`, [id]);
  const board = rowToBoard(result.rows[0]);
  if (!board) return null;
  return includeDeleted || !board.deleted ? board : null;
}

/** Live board ids (or all when includeDeleted) — used by tombstone GC sweeps. */
export async function listBoardIds({ includeDeleted = false } = {}) {
  const result = includeDeleted
    ? await query(`SELECT id FROM mood_boards`)
    : await query(`SELECT id FROM mood_boards WHERE deleted = FALSE`);
  return result.rows.map((r) => r.id);
}

export async function createBoard(input) {
  const id = `mb-${randomUUID()}`;
  const board = buildBoardRecord(input, { id });
  await persist(query, board);
  console.log(`🎨 Created mood board: ${id} (${input.name})`);
  return board;
}

// Lock the row, apply `mutate(board)`, persist (unless skipPersist), and return
// the mutator's continuation. Throws NOT_FOUND when the row is absent.
async function withLockedBoard(id, mutate) {
  return withTransaction(async (client) => {
    const sel = await client.query(
      `SELECT data FROM mood_boards WHERE id = $1 FOR UPDATE`,
      [id],
    );
    const board = rowToBoard(sel.rows[0]);
    // A tombstoned board (#1564 soft-delete) is treated as gone for every
    // user-facing mutator — without this, an item add/update/remove or a PATCH
    // after deletion would resurrect the row, bump updatedAt, and re-push a
    // modified tombstone to peers while getBoard 404s the same id.
    // mergeBoardsFromSync (peer tombstone apply) bypasses this path via its own
    // persist, so receiving a tombstone still works.
    if (!board || board.deleted) {
      throw new ServerError('Mood board not found', { status: 404, code: 'NOT_FOUND' });
    }
    const { board: next, result, skipPersist } = mutate(board);
    if (!skipPersist) await persist(client.query.bind(client), next);
    return { board: next, result };
  });
}

export async function updateBoard(id, patch) {
  const { board } = await withLockedBoard(id, (b) => ({ board: applyBoardPatch(b, patch) }));
  return board;
}

/**
 * Faithful conflict-restore (RESTORABLE_FIELDS.moodBoard = name/description/items/style/posterImageRef)
 * wired into conflictJournalResolver. Goes through the row lock + tombstone guard
 * like every other mutator, but uses applyBoardRestore so a "restore my whole
 * version" also brings back items[] (applyBoardPatch only touches name/description).
 */
export async function restoreBoard(id, patch) {
  const { board } = await withLockedBoard(id, (b) => ({ board: applyBoardRestore(b, patch) }));
  return board;
}

export async function deleteBoard(id) {
  // Soft-delete tombstone (#1564) so the deletion federates and an out-of-date
  // peer can't resurrect the board via the LWW merge. The row stays; `deleted`
  // flips and `updatedAt`/`deletedAt` stamp now so the tombstone wins on merge.
  return withTransaction(async (client) => {
    const sel = await client.query(
      `SELECT data FROM mood_boards WHERE id = $1 FOR UPDATE`,
      [id],
    );
    const current = rowToBoard(sel.rows[0]);
    if (!current || current.deleted) throw new ServerError('Mood board not found', { status: 404, code: 'NOT_FOUND' });
    const now = new Date().toISOString();
    const next = { ...current, deleted: true, deletedAt: now, updatedAt: now };
    await persist(client.query.bind(client), next);
    return { ok: true };
  });
}

/**
 * Merge an incoming batch of board records from a peer (per-record push). Each
 * record's read-modify-write runs inside `withTransaction` + `SELECT … FOR
 * UPDATE` so a concurrent local edit can't lose to (or clobber) the merge. LWW
 * on `updatedAt` (tombstone-aware) via the shared `mergeBoardRecord` decision —
 * identical contract to mergeProjectsFromSync: seeds/advances the conflict-journal
 * base hash and journals the about-to-be-overwritten local version when remote
 * wins (best-effort, never throws into the merge). Returns `{ applied, count }`.
 */
export async function mergeBoardsFromSync(remoteBoards, { source = { via: 'sync', peerId: null } } = {}) {
  if (!Array.isArray(remoteBoards)) return { applied: false, count: 0 };
  let changed = 0;
  for (const remote of remoteBoards) {
    const applied = await withTransaction(async (client) => {
      const sel = await client.query(`SELECT data FROM mood_boards WHERE id = $1 FOR UPDATE`, [remote?.id]);
      const local = rowToBoard(sel.rows[0]);
      const { next, inserted, remoteWins, changed: didChange } = mergeBoardRecord(local, remote);
      if (!next) return false; // malformed remote → dropped
      if (inserted) {
        await persist(client.query.bind(client), next);
        await setSyncBaseHash('moodBoard', next.id, contentHashForRecord('moodBoard', next));
        return true;
      }
      // local wins, OR remote won but is byte-identical to local (already agree).
      if (!remoteWins || !didChange) return false;
      await maybeJournalBeforeOverwrite({ kind: 'moodBoard', id: next.id, local, remote: next, source });
      await persist(client.query.bind(client), next);
      await setSyncBaseHash('moodBoard', next.id, contentHashForRecord('moodBoard', next));
      return true;
    });
    if (applied) changed += 1;
  }
  await flushBaseHashes();
  if (changed === 0) return { applied: false, count: 0 };
  return { applied: true, count: changed };
}

/**
 * Hard-remove tombstoned boards whose deletedAt is older than the cutoff. Called
 * by tombstoneGc once every subscribed peer has acked the deletion. Evicts each
 * pruned board's conflict-journal base hash (mirrors pruneTombstonedProjects).
 */
export async function pruneTombstonedBoards(olderThanMs) {
  if (!Number.isFinite(olderThanMs)) return { pruned: 0 };
  const cutoffIso = new Date(olderThanMs).toISOString();
  const { rows } = await query(
    `DELETE FROM mood_boards
     WHERE deleted = TRUE AND deleted_at IS NOT NULL AND deleted_at < $1
     RETURNING id`,
    [cutoffIso],
  );
  await withBaseHashFlushBatch(async () => {
    for (const r of rows) await deleteSyncBaseHash('moodBoard', r.id);
  });
  return { pruned: rows.length };
}

export async function addBoardItem(id, itemInput) {
  const { result } = await withLockedBoard(id, (b) => {
    const { board, item } = addItem(b, itemInput);
    return { board, result: item, skipPersist: board === b };
  });
  return result;
}

export async function updateBoardItem(id, itemId, patch) {
  const { result } = await withLockedBoard(id, (b) => {
    const { board, item } = updateItem(b, itemId, patch);
    return { board, result: item };
  });
  return result;
}

// Text note render lifecycle (#10531) — see the logic.js transforms. The two
// settle steps skip the write when the note moved on (stale job).
// Re-check liveness under the row lock. A pre-lock snapshot can mark a render
// that was claimed while this read was in flight. Returns null when the board
// was deleted between the read and the lock.
export async function healInterruptedNoteRenders(id, { isLive = () => false, now } = {}) {
  try {
    let changed = false;
    const locked = await withLockedBoard(id, (board) => {
      const projected = projectInterruptedNoteRenders(board, { isLive, ...(now ? { now } : {}) });
      changed = projected.changed;
      return { board: projected.board, skipPersist: !projected.changed };
    });
    return { board: locked.board, changed };
  } catch (err) {
    if (err?.code === 'NOT_FOUND') return null;
    throw err;
  }
}

export async function claimBoardItemRender(id, itemId, opts) {
  const { result } = await withLockedBoard(id, (b) => {
    const { board, item } = claimItemRender(b, itemId, opts);
    return { board, result: item };
  });
  return result;
}

export async function settleBoardItemRender(id, itemId, patch) {
  const { result } = await withLockedBoard(id, (b) => {
    const { board, item, changed } = settleItemRender(b, itemId, patch);
    return { board, result: changed ? item : null, skipPersist: !changed };
  });
  return result;
}

export async function applyBoardItemRender(id, itemId, rendered) {
  const { result } = await withLockedBoard(id, (b) => {
    const { board, item, changed } = applyRenderedItem(b, itemId, rendered);
    return { board, result: changed ? item : null, skipPersist: !changed };
  });
  return result;
}

// ─── Pinterest board link (mood-board importer) ──────────────────────────────

// Link (or re-link) the board to a Pinterest RSS feed.
export async function setPinterestLink(id, link) {
  const { board } = await withLockedBoard(id, (b) => ({ board: applyPinterestLink(b, link) }));
  return board;
}

// Self-heal a stale feed URL under lock, skipping the write (and peer push) when
// the user unlinked/repointed concurrently or it's already corrected (see
// healPinterestFeedRecord). Returns { board, changed }.
export async function healPinterestFeed(id, link) {
  let changed = false;
  const { board } = await withLockedBoard(id, (b) => {
    const { board: next, changed: didChange } = healPinterestFeedRecord(b, link);
    changed = didChange;
    return { board: next, skipPersist: !didChange };
  });
  return { board, changed };
}

// Unlink. Skips the write (and peer push) when the board wasn't linked.
export async function clearPinterestLink(id) {
  const { board } = await withLockedBoard(id, (b) => {
    const { board: next, changed } = clearPinterestLinkRecord(b);
    return { board: next, skipPersist: !changed };
  });
  return board;
}

// Append the freshly-downloaded pins in ONE locked write (rather than N
// addBoardItem round-trips) and stamp lastSyncedAt. Persists on every real sync
// so a zero-new sync still records the check — but skips the write entirely when
// appendPinterestPins aborts (the link changed mid-sync, see opts.expectedFeedUrl).
// Returns { board, added, aborted }.
export async function appendPinterestItems(id, imported, opts = {}) {
  const { board, result } = await withLockedBoard(id, (b) => {
    const { board: next, added, aborted } = appendPinterestPins(b, imported, opts);
    return { board: next, result: { added, aborted }, skipPersist: aborted };
  });
  return { board, ...result };
}

// Append already-downloaded one-shot import items (X post importer) in ONE
// locked write. Unlike appendPinterestItems, there's no persisted link to
// stamp or race against — skips the write entirely when nothing new landed.
export async function appendImportedItems(id, imported) {
  const { board, result } = await withLockedBoard(id, (b) => {
    const { board: next, added } = appendImportedItemsRecord(b, imported);
    return { board: next, result: { added }, skipPersist: added === 0 };
  });
  return { board, ...result };
}

// Swap external image URLs for their re-hosted local copies in ONE locked write.
// Skips the write when nothing still matches. Returns { board, changed }.
export async function applyLocalizedItemImages(id, replacements) {
  const { board, result } = await withLockedBoard(id, (b) => {
    const { board: next, changed } = applyLocalizedImageUrls(b, replacements);
    return { board: next, result: changed, skipPersist: changed === 0 };
  });
  return { board, changed: result };
}

export async function removeBoardItem(id, itemId) {
  // Unknown itemId → skip the write entirely (no-op, no updated_at bump). The
  // route still 200s — removing an already-gone item is idempotent.
  const { board } = await withLockedBoard(id, (b) => {
    const { board: next, removed } = removeItem(b, itemId);
    return { board: next, skipPersist: !removed };
  });
  return board;
}