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

25.8 KB
Raw Blame
/**
 * Writers Room — PostgreSQL leaf I/O (#1017).
 *
 * Four tables replace the bespoke file layout:
 *   writers_room_folders        ← folders.json
 *   writers_room_works          ← works/<id>/manifest.json (minus drafts[])
 *   writers_room_draft_versions ← the manifest's drafts[] array (decomposed)
 *   writers_room_exercises      ← exercises.json
 *
 * The draft PROSE BODY stays on disk at works/<id>/drafts/<draftId>.md
 * (file-primary) — only its metadata (hash, word count, segment index, lineage)
 * becomes a row here. local.js still owns the .md read/write; this module is the
 * metadata index over them.
 *
 * PURE leaf I/O — no sanitizing, no path logic, no .md access. The store facade
 * (store.js) selects this backend; local.js reassembles a manifest (work row +
 * its draft rows → the manifest shape with an embedded drafts[]) on read and
 * splits it on write, so its public API is unchanged.
 *
 * The `data` JSONB on every table holds the FULL lossless record; the typed
 * columns are a queryable mirror (never read back into the record).
 *
 * Works FEDERATE across peers as of #1565 (record kind `writersRoomWork`, sync
 * category `writersRoomWorks`): `deleteWork` soft-deletes (tombstone) instead of
 * hard-deleting so the deletion propagates without an out-of-date peer
 * resurrecting it, and `mergeWorksFromSync` LWW-merges an incoming work the same
 * way creativeDirector/projectsDB.js does — per-record `withTransaction` +
 * `SELECT … FOR UPDATE`, conflict-journal base hash seeding, and journaling the
 * about-to-be-overwritten local version when remote wins. The `deleted`/`deletedAt`
 * columns (added in #1017) carry the tombstone; the same pair is mirrored into the
 * work row's `data` JSONB so the sync sanitizer round-trips it. Folders and
 * exercises do NOT federate yet (they lack soft-delete columns).
 */

import { query, withTransaction } from '../../lib/db.js';
import { mirrorTimestamp } from '../../lib/pgTimestamp.js';
import {
  maybeJournalBeforeOverwrite, setSyncBaseHash, contentHashForRecord, flushBaseHashes,
} from '../../lib/conflictJournal.js';
import {
  WRITERS_ROOM_WORK_KIND, mergeWorkRecord,
  WRITERS_ROOM_FOLDER_KIND, mergeFolderRecord,
  WRITERS_ROOM_EXERCISE_KIND, mergeExerciseRecord,
} from './syncLogic.js';
import { toPgJson } from '../../lib/pgJson.js';

/**
 * Shared per-record LWW merge for a body-less Writers Room record (folder /
 * exercise) — both federate as of #1645 (follow-up to #1565). Same posture as
 * `mergeWorksFromSync` minus the draft-row decomposition: lock the row, rebuild
 * the local record from its `data` JSONB, run the shared pure decision, journal
 * the about-to-be-overwritten local version when remote wins, and persist + seed
 * the conflict-journal base hash. `table` is a hardcoded constant (never user
 * input). Returns `{ applied, count }`.
 */
async function mergeBodylessFromSync(table, kind, mergeFn, persistFn, remoteRecords, source) {
  if (!Array.isArray(remoteRecords)) return { applied: false, count: 0 };
  let changed = 0;
  for (const remote of remoteRecords) {
    const applied = await withTransaction(async (client) => {
      const sel = await client.query(`SELECT data FROM ${table} WHERE id = $1 FOR UPDATE`, [remote?.id]);
      const local = sel.rows[0]?.data ?? null;
      const { next, inserted, remoteWins, changed: didChange } = mergeFn(local, remote);
      if (!next) return false; // malformed remote → dropped
      if (!inserted && (!remoteWins || !didChange)) return false; // local wins, or no-op
      if (!inserted) {
        await maybeJournalBeforeOverwrite({ kind, id: next.id, local, remote: next, source });
      }
      await persistFn(client, next);
      await setSyncBaseHash(kind, next.id, contentHashForRecord(kind, next));
      return true;
    });
    if (applied) changed += 1;
  }
  await flushBaseHashes();
  return changed === 0 ? { applied: false, count: 0 } : { applied: true, count: changed };
}

/** Hard-remove tombstoned rows older than the cutoff; returns their ids. */
async function pruneTombstonedRows(table, olderThanMs) {
  if (!Number.isFinite(olderThanMs)) return { pruned: 0, ids: [] };
  const cutoffIso = new Date(olderThanMs).toISOString();
  const { rows } = await query(
    `DELETE FROM ${table}
     WHERE deleted = TRUE AND deleted_at IS NOT NULL AND deleted_at < $1
     RETURNING id`,
    [cutoffIso],
  );
  const ids = rows.map((r) => r.id);
  return { pruned: ids.length, ids };
}

// ---------- folders ----------

/** Every LIVE folder row's `data` JSONB, ordered for the library tree. */
export async function listFolders() {
  const { rows } = await query(
    `SELECT data FROM writers_room_folders WHERE deleted = FALSE ORDER BY sort_order, created_at`,
  );
  return rows.map((r) => r.data);
}

/**
 * One folder's `data` JSONB, or null. Live only by default; `includeDeleted`
 * surfaces a tombstoned folder (federation reads it to compare `updatedAt` + push
 * the tombstone).
 */
export async function readFolder(id, { includeDeleted = false } = {}) {
  const { rows } = await query(
    includeDeleted
      ? `SELECT data FROM writers_room_folders WHERE id = $1`
      : `SELECT data FROM writers_room_folders WHERE id = $1 AND deleted = FALSE`,
    [id],
  );
  return rows[0]?.data ?? null;
}

/** Every folder id — live only by default, or all (incl. tombstones) when asked. */
export async function listFolderIds({ includeDeleted = false } = {}) {
  const { rows } = await query(
    includeDeleted
      ? `SELECT id FROM writers_room_folders`
      : `SELECT id FROM writers_room_folders WHERE deleted = FALSE`,
  );
  return rows.map((r) => r.id);
}

/**
 * Upsert one folder on an open transaction client. The `data` JSONB mirrors the
 * normalized soft-delete pair so `readFolder(includeDeleted)` surfaces a tombstone
 * to the sync sanitizer (the columns alone are never read back into `data`).
 */
function persistFolderTx(client, folder) {
  const now = new Date().toISOString();
  const createdAt = mirrorTimestamp(folder?.createdAt, now);
  const deleted = folder?.deleted === true;
  const deletedAt = deleted ? mirrorTimestamp(folder?.deletedAt, now) : null;
  const folderData = { ...folder, deleted, deletedAt };
  return client.query(
    `INSERT INTO writers_room_folders (id, parent_id, name, sort_order, data, created_at, updated_at, deleted, deleted_at)
     VALUES ($1, $2, $3, $4, $5::jsonb, $6, $7, $8, $9)
     ON CONFLICT (id) DO UPDATE SET
       parent_id = EXCLUDED.parent_id,
       name = EXCLUDED.name,
       sort_order = EXCLUDED.sort_order,
       data = EXCLUDED.data,
       updated_at = EXCLUDED.updated_at,
       deleted = EXCLUDED.deleted,
       deleted_at = EXCLUDED.deleted_at`,
    [
      folder.id,
      typeof folder?.parentId === 'string' && folder.parentId ? folder.parentId : null,
      String(folder?.name ?? ''),
      Number.isInteger(folder?.sortOrder) ? folder.sortOrder : 0,
      toPgJson(folderData),
      createdAt,
      mirrorTimestamp(folder?.updatedAt, createdAt),
      deleted,
      deletedAt,
    ],
  );
}

/** Upsert one folder. `created_at` is preserved on conflict. */
export async function writeFolder(folder) {
  await withTransaction((client) => persistFolderTx(client, folder));
}

/**
 * Soft-delete a folder (tombstone) so the deletion federates and an out-of-date
 * peer can't resurrect it via the LWW merge (#1645). Idempotent — a missing or
 * already-deleted folder is a no-op (the caller 404s a missing folder first).
 */
export async function deleteFolder(id) {
  await withTransaction(async (client) => {
    const sel = await client.query(`SELECT data FROM writers_room_folders WHERE id = $1 FOR UPDATE`, [id]);
    const current = sel.rows[0]?.data;
    if (!current || current.deleted === true) return;
    const now = new Date().toISOString();
    await persistFolderTx(client, { ...current, deleted: true, deletedAt: now, updatedAt: now });
  });
}

/** Merge an incoming batch of folder records from a peer (LWW, tombstone-aware). */
export async function mergeFoldersFromSync(remoteFolders, { source = { via: 'sync', peerId: null } } = {}) {
  return mergeBodylessFromSync('writers_room_folders', WRITERS_ROOM_FOLDER_KIND, mergeFolderRecord, persistFolderTx, remoteFolders, source);
}

/** Hard-remove tombstoned folders whose `deleted_at` is older than the cutoff. */
export async function pruneTombstonedFolders(olderThanMs) {
  return pruneTombstonedRows('writers_room_folders', olderThanMs);
}

// ---------- exercises ----------

/** Every LIVE exercise row's `data` JSONB, newest sprint first. */
export async function listExercises() {
  const { rows } = await query(
    `SELECT data FROM writers_room_exercises WHERE deleted = FALSE ORDER BY started_at DESC NULLS LAST`,
  );
  return rows.map((r) => r.data);
}

/** One exercise's `data` JSONB, or null (tombstone surfaced when includeDeleted). */
export async function readExercise(id, { includeDeleted = false } = {}) {
  const { rows } = await query(
    includeDeleted
      ? `SELECT data FROM writers_room_exercises WHERE id = $1`
      : `SELECT data FROM writers_room_exercises WHERE id = $1 AND deleted = FALSE`,
    [id],
  );
  return rows[0]?.data ?? null;
}

/** Every exercise id — live only by default, or all (incl. tombstones) when asked. */
export async function listExerciseIds({ includeDeleted = false } = {}) {
  const { rows } = await query(
    includeDeleted
      ? `SELECT id FROM writers_room_exercises`
      : `SELECT id FROM writers_room_exercises WHERE deleted = FALSE`,
  );
  return rows.map((r) => r.id);
}

/** Upsert one exercise on an open transaction client (mirrors the soft-delete pair into `data`). */
function persistExerciseTx(client, exercise) {
  const deleted = exercise?.deleted === true;
  const deletedAt = deleted ? mirrorTimestamp(exercise?.deletedAt, new Date().toISOString()) : null;
  const exerciseData = { ...exercise, deleted, deletedAt };
  return client.query(
    `INSERT INTO writers_room_exercises (id, work_id, status, data, started_at, finished_at, deleted, deleted_at)
     VALUES ($1, $2, $3, $4::jsonb, $5, $6, $7, $8)
     ON CONFLICT (id) DO UPDATE SET
       work_id = EXCLUDED.work_id,
       status = EXCLUDED.status,
       data = EXCLUDED.data,
       started_at = EXCLUDED.started_at,
       finished_at = EXCLUDED.finished_at,
       deleted = EXCLUDED.deleted,
       deleted_at = EXCLUDED.deleted_at`,
    [
      exercise.id,
      typeof exercise?.workId === 'string' && exercise.workId ? exercise.workId : null,
      typeof exercise?.status === 'string' ? exercise.status.slice(0, 16) : null,
      toPgJson(exerciseData),
      mirrorTimestamp(exercise?.startedAt, null),
      mirrorTimestamp(exercise?.finishedAt, null),
      deleted,
      deletedAt,
    ],
  );
}

/** Upsert one exercise session. */
export async function writeExercise(exercise) {
  await withTransaction((client) => persistExerciseTx(client, exercise));
}

/** Merge an incoming batch of exercise records from a peer (LWW, tombstone-aware). */
export async function mergeExercisesFromSync(remoteExercises, { source = { via: 'sync', peerId: null } } = {}) {
  return mergeBodylessFromSync('writers_room_exercises', WRITERS_ROOM_EXERCISE_KIND, mergeExerciseRecord, persistExerciseTx, remoteExercises, source);
}

/** Hard-remove tombstoned exercises whose `deleted_at` is older than the cutoff. */
export async function pruneTombstonedExercises(olderThanMs) {
  return pruneTombstonedRows('writers_room_exercises', olderThanMs);
}

// ---------- works + draft versions ----------

/**
 * Map a work row + its draft rows back to the manifest shape local.js expects:
 * the full work record from `data` with `drafts[]` rebuilt from the
 * draft-version rows (ordered by created_at to preserve version lineage order).
 * Returns null for a missing/soft-deleted work.
 */
function rowsToManifest(workRow, draftRows) {
  if (!workRow) return null;
  const manifest = { ...workRow.data };
  manifest.drafts = draftRows.map((d) => d.data);
  return manifest;
}

/**
 * One work's manifest (rebuilt from its row + draft rows), or null. Live works
 * only by default; `includeDeleted` surfaces a tombstoned work too (federation
 * reads it to compare `updatedAt` and to push the tombstone to peers).
 */
export async function readWork(id, { includeDeleted = false } = {}) {
  const work = await query(
    includeDeleted
      ? `SELECT data FROM writers_room_works WHERE id = $1`
      : `SELECT data FROM writers_room_works WHERE id = $1 AND deleted = FALSE`,
    [id],
  );
  if (!work.rows[0]) return null;
  const drafts = await query(
    `SELECT data FROM writers_room_draft_versions WHERE work_id = $1 ORDER BY created_at, id`,
    [id],
  );
  return rowsToManifest(work.rows[0], drafts.rows);
}

/** Every work id — live only by default, or all (incl. tombstones) when asked. */
export async function listWorkIds({ includeDeleted = false } = {}) {
  const { rows } = await query(
    includeDeleted
      ? `SELECT id FROM writers_room_works`
      : `SELECT id FROM writers_room_works WHERE deleted = FALSE`,
  );
  return rows.map((r) => r.id);
}

/** Snapshot membership only: no work bodies or draft history are hydrated. */
export async function listOrderedWorkIds() {
  const { rows } = await query(
    `SELECT id FROM writers_room_works WHERE deleted = FALSE ORDER BY updated_at DESC, id ASC`,
  );
  return rows.map(row => row.id);
}

/** Hydrate only the already bounded page, preserving snapshot order. */
export async function readWorksPage(ids) {
  if (!ids.length) return [];
  const works = await query(
    `SELECT id, data FROM writers_room_works
     WHERE deleted = FALSE AND id = ANY($1::text[])
     ORDER BY array_position($1::text[], id) LIMIT $2`, [ids, ids.length],
  );
  return hydrateWorks(works.rows);
}

/** Every live work's manifest, rebuilt with its drafts[] — one pair of queries. */
export async function listWorks() {
  const works = await query(
    `SELECT id, data FROM writers_room_works WHERE deleted = FALSE`,
  );
  return hydrateWorks(works.rows);
}

async function hydrateWorks(works) {
  if (works.length === 0) return [];
  const drafts = await query(
    `SELECT work_id, data FROM writers_room_draft_versions
     WHERE work_id = ANY($1::text[]) ORDER BY created_at, id`,
    [works.map((r) => r.id)],
  );
  const draftsByWork = new Map();
  for (const d of drafts.rows) {
    if (!draftsByWork.has(d.work_id)) draftsByWork.set(d.work_id, []);
    draftsByWork.get(d.work_id).push({ data: d.data });
  }
  return works.map((w) => rowsToManifest(w, draftsByWork.get(w.id) || []));
}

/**
 * Cheap live-work tally (#2729) — the count listWorks().length used to pay for
 * by running the drafts query and rebuilding every work's full manifest.
 *
 * `deleted` here is a real column (not a JSONB mirror), and it is the SAME
 * predicate listWorks() above filters on — so the two agree by construction.
 * Unlike universeBuilder, no sanitizer sits between the row and the caller on
 * this path, so there is no drop-on-read case to reconcile.
 */
export async function countWorks() {
  const { rows } = await query(
    `SELECT COUNT(*) AS count FROM writers_room_works WHERE deleted = FALSE`,
  );
  return parseInt(rows[0].count, 10);
}

// Bind tuple for one draft-version row from a manifest draft entry.
function draftBinds(workId, draft) {
  return [
    draft.id,
    workId,
    typeof draft?.label === 'string' ? draft.label : null,
    String(draft?.contentFile ?? ''),
    typeof draft?.contentHash === 'string' ? draft.contentHash : null,
    Number.isInteger(draft?.wordCount) ? draft.wordCount : 0,
    toPgJson(Array.isArray(draft?.segmentIndex) ? draft.segmentIndex : []),
    typeof draft?.createdFromVersionId === 'string' ? draft.createdFromVersionId : null,
    toPgJson(draft),
    mirrorTimestamp(draft?.createdAt, new Date().toISOString()),
  ];
}

/**
 * Upsert a work manifest AND its draft versions atomically. The manifest's
 * `drafts[]` array is the authoritative version set: we upsert each draft row
 * and DELETE any draft rows for this work no longer in the array (covers a
 * version delete). `created_at` is preserved on conflict for both the work and
 * each draft. Runs in a transaction so a half-written work never leaves orphan
 * draft rows or a draft set out of sync with the manifest.
 */
export async function writeWork(manifest) {
  await withTransaction((client) => persistWorkTx(client, manifest));
  return manifest;
}

/**
 * Upsert a work manifest + its draft rows on an open transaction client. Shared
 * by `writeWork` (its own transaction) and `mergeWorksFromSync` (the per-record
 * merge transaction). The work row's `data` holds the manifest WITHOUT the
 * drafts[] array — the draft rows are authoritative — but WITH the normalized
 * soft-delete pair so the sync sanitizer round-trips a tombstone. Honors
 * `manifest.deleted`/`manifest.deletedAt` (default live) so a tombstone merge
 * persists the deletion instead of resurrecting the row.
 */
async function persistWorkTx(client, manifest) {
  const now = new Date().toISOString();
  const createdAt = mirrorTimestamp(manifest?.createdAt, now);
  // `drafts` absent (not an array) means "don't touch the draft rows" — the
  // soft-delete tombstone path persists only the work row and must KEEP the
  // existing draft rows (+ .md bodies) until hard-prune. An empty array still
  // means "authoritative empty set → delete all draft rows" (the original
  // behavior writeWork relied on for a version delete).
  const hasDrafts = Array.isArray(manifest?.drafts);
  const drafts = hasDrafts ? manifest.drafts : [];
  const deleted = manifest?.deleted === true;
  const deletedAt = deleted ? mirrorTimestamp(manifest?.deletedAt, now) : null;
  const { drafts: _omit, ...rest } = manifest;
  // Mirror the tombstone trio INTO the JSONB so readWork(includeDeleted) surfaces
  // it to the sync sanitizer (the columns alone are never read back into data).
  const workData = { ...rest, deleted, deletedAt };
  await client.query(
    `INSERT INTO writers_room_works
       (id, folder_id, title, kind, status, active_draft_version_id,
        pipeline_series_id, pipeline_issue_id, cd_project_id, media_collection_id,
        data, created_at, updated_at, deleted, deleted_at)
     VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11::jsonb,$12,$13,$14,$15)
     ON CONFLICT (id) DO UPDATE SET
       folder_id = EXCLUDED.folder_id,
       title = EXCLUDED.title,
       kind = EXCLUDED.kind,
       status = EXCLUDED.status,
       active_draft_version_id = EXCLUDED.active_draft_version_id,
       pipeline_series_id = EXCLUDED.pipeline_series_id,
       pipeline_issue_id = EXCLUDED.pipeline_issue_id,
       cd_project_id = EXCLUDED.cd_project_id,
       media_collection_id = EXCLUDED.media_collection_id,
       data = EXCLUDED.data,
       updated_at = EXCLUDED.updated_at,
       deleted = EXCLUDED.deleted,
       deleted_at = EXCLUDED.deleted_at`,
    [
      manifest.id,
      typeof manifest?.folderId === 'string' && manifest.folderId ? manifest.folderId : null,
      String(manifest?.title ?? ''),
      typeof manifest?.kind === 'string' ? manifest.kind.slice(0, 32) : null,
      typeof manifest?.status === 'string' ? manifest.status.slice(0, 32) : null,
      typeof manifest?.activeDraftVersionId === 'string' ? manifest.activeDraftVersionId : null,
      typeof manifest?.pipelineSeriesId === 'string' ? manifest.pipelineSeriesId : null,
      typeof manifest?.pipelineIssueId === 'string' ? manifest.pipelineIssueId : null,
      typeof manifest?.cdProjectId === 'string' ? manifest.cdProjectId : null,
      typeof manifest?.mediaCollectionId === 'string' ? manifest.mediaCollectionId : null,
      toPgJson(workData),
      createdAt,
      mirrorTimestamp(manifest?.updatedAt, createdAt),
      deleted,
      deletedAt,
    ],
  );
  if (!hasDrafts) return; // tombstone-only persist — leave draft rows untouched
  for (const draft of drafts) {
    if (!draft || typeof draft.id !== 'string') continue;
    await client.query(
      `INSERT INTO writers_room_draft_versions
         (id, work_id, label, content_file, content_hash, word_count,
          segment_index, created_from_version_id, data, created_at)
       VALUES ($1,$2,$3,$4,$5,$6,$7::jsonb,$8,$9::jsonb,$10)
       ON CONFLICT (id) DO UPDATE SET
         work_id = EXCLUDED.work_id,
         label = EXCLUDED.label,
         content_file = EXCLUDED.content_file,
         content_hash = EXCLUDED.content_hash,
         word_count = EXCLUDED.word_count,
         segment_index = EXCLUDED.segment_index,
         created_from_version_id = EXCLUDED.created_from_version_id,
         data = EXCLUDED.data`,
      draftBinds(manifest.id, draft),
    );
  }
  // Prune draft rows that are no longer part of the manifest (version delete).
  const keepIds = drafts.map((d) => d?.id).filter((id) => typeof id === 'string');
  if (keepIds.length > 0) {
    await client.query(
      `DELETE FROM writers_room_draft_versions WHERE work_id = $1 AND id <> ALL($2::text[])`,
      [manifest.id, keepIds],
    );
  } else {
    await client.query(`DELETE FROM writers_room_draft_versions WHERE work_id = $1`, [manifest.id]);
  }
}

/**
 * Soft-delete a work (tombstone) so the deletion federates and an out-of-date
 * peer can't resurrect it via the LWW merge (#1565). The work row + its draft
 * rows + the on-disk .md bodies all stay until tombstone GC hard-prunes them
 * (`pruneTombstonedWorks`); `deleted`/`deletedAt`/`updatedAt` stamp now so the
 * tombstone wins on merge. Idempotent: a missing/already-deleted work is a no-op
 * (the caller already 404s a missing work before reaching here).
 */
export async function deleteWork(id) {
  await withTransaction(async (client) => {
    const sel = await client.query(`SELECT data FROM writers_room_works WHERE id = $1 FOR UPDATE`, [id]);
    const current = sel.rows[0]?.data;
    if (!current || current.deleted === true) return;
    const now = new Date().toISOString();
    await persistWorkTx(client, { ...current, drafts: undefined, deleted: true, deletedAt: now, updatedAt: now });
  });
}

/**
 * Merge an incoming batch of work 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 `mergeWorkRecord` decision —
 * identical to the file backend so the two can't drift. Mirrors
 * `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 mergeWorksFromSync(remoteWorks, { source = { via: 'sync', peerId: null } } = {}) {
  if (!Array.isArray(remoteWorks)) return { applied: false, count: 0 };
  let changed = 0;
  for (const remote of remoteWorks) {
    const applied = await withTransaction(async (client) => {
      // Lock the work row, then rebuild the local manifest (row data + its draft
      // rows) so the LWW comparison + journaled snapshot see the full record.
      const sel = await client.query(`SELECT data FROM writers_room_works WHERE id = $1 FOR UPDATE`, [remote?.id]);
      let local = null;
      if (sel.rows[0]) {
        const draftRows = await client.query(
          `SELECT data FROM writers_room_draft_versions WHERE work_id = $1 ORDER BY created_at, id`,
          [remote.id],
        );
        local = rowsToManifest(sel.rows[0], draftRows.rows);
      }
      const { next, inserted, remoteWins, changed: didChange } = mergeWorkRecord(local, remote);
      if (!next) return false; // malformed remote → dropped
      if (inserted) {
        await persistWorkTx(client, next);
        await setSyncBaseHash(WRITERS_ROOM_WORK_KIND, next.id, contentHashForRecord(WRITERS_ROOM_WORK_KIND, next));
        return true;
      }
      if (!remoteWins || !didChange) return false; // local wins, or no-op
      await maybeJournalBeforeOverwrite({ kind: WRITERS_ROOM_WORK_KIND, id: next.id, local, remote: next, source });
      await persistWorkTx(client, next);
      await setSyncBaseHash(WRITERS_ROOM_WORK_KIND, next.id, contentHashForRecord(WRITERS_ROOM_WORK_KIND, 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 works (+ their draft rows) whose deletedAt is older than
 * the cutoff. Called by tombstone GC once every subscribed peer has acked the
 * deletion. Returns the pruned ids so the facade (sync.js) can rm the on-disk .md
 * dirs + evict each work's conflict-journal base hash. Mirrors
 * `pruneTombstonedProjects` (the base-hash eviction lives in the facade here
 * because the .md cleanup is a facade concern too).
 */
export async function pruneTombstonedWorks(olderThanMs) {
  if (!Number.isFinite(olderThanMs)) return { pruned: 0, ids: [] };
  const cutoffIso = new Date(olderThanMs).toISOString();
  const ids = await withTransaction(async (client) => {
    const { rows } = await client.query(
      `DELETE FROM writers_room_works
       WHERE deleted = TRUE AND deleted_at IS NOT NULL AND deleted_at < $1
       RETURNING id`,
      [cutoffIso],
    );
    const pruned = rows.map((r) => r.id);
    if (pruned.length > 0) {
      await client.query(`DELETE FROM writers_room_draft_versions WHERE work_id = ANY($1::text[])`, [pruned]);
    }
    return pruned;
  });
  return { pruned: ids.length, ids };
}

/**
 * Every live draft-version row (work_id + the metadata) for the restore-coherence
 * check — every row's content_file must exist on disk + content_hash must match.
 */
export async function listDraftVersionsForCoherence() {
  const { rows } = await query(
    `SELECT v.id, v.work_id, v.content_file, v.content_hash
       FROM writers_room_draft_versions v
       JOIN writers_room_works w ON w.id = v.work_id AND w.deleted = FALSE
       ORDER BY v.work_id, v.created_at`,
  );
  return rows.map((r) => ({
    id: r.id, workId: r.work_id, contentFile: r.content_file, contentHash: r.content_hash,
  }));
}