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

3.4 KB
Raw Blame
/**
 * PostgreSQL leaf I/O for FableLoom records. The full sanitized record lives
 * in JSONB; name/universe_id/series_id/updated_at are mirrored for the common
 * list/filter paths; delete markers are mirrored for federated tombstone GC.
 */

import { query, withTransaction } from '../../lib/db.js';
import { mirrorTimestamp } from '../../lib/pgTimestamp.js';
import { toPgJson } from '../../lib/pgJson.js';

export async function readRaw(id) {
  const { rows } = await query('SELECT data FROM fableloom_stories WHERE id = $1', [id]);
  return rows[0]?.data ?? null;
}

export async function listRaw() {
  const { rows } = await query('SELECT data FROM fableloom_stories ORDER BY updated_at DESC, id ASC');
  return rows.map((row) => row.data);
}

/** Keep gallery-video ownership stable until a media retirement settles. */
export function withMediaReferences(work) {
  return withTransaction(async client => {
    await client.query('LOCK TABLE fableloom_stories IN SHARE MODE');
    const { rows } = await client.query('SELECT data FROM fableloom_stories WHERE deleted = FALSE');
    return work(rows.map(row => row.data));
  });
}

/** Every loom id (live AND tombstones) — the service filters. */
export async function listIds() {
  const { rows } = await query('SELECT id FROM fableloom_stories');
  return rows.map((row) => row.id);
}

/**
 * Live (non-deleted) loom ids only — id-only projection hitting
 * idx_fableloom_live. Backs tombstoneGc's LIVE_ID_LISTERS (the base-hash
 * orphan sweep), so a record stops protecting its base hash the moment it's
 * tombstoned without the sweep ever reading a loom's JSONB body.
 */
export async function listLiveIds() {
  const { rows } = await query('SELECT id FROM fableloom_stories WHERE deleted = FALSE');
  return rows.map((row) => row.id);
}

/**
 * Tombstoned loom ids whose deletedAt is older than `beforeMs` (epoch ms) —
 * the GC candidate scan for `pruneTombstonedLooms`. An id-only projection:
 * the sweep almost never finds a tombstone, so the common case must not pay
 * for a single loom's JSONB body, let alone every one of them.
 */
export async function listTombstoneIdsBefore(beforeMs) {
  const cutoffIso = new Date(beforeMs).toISOString();
  const { rows } = await query(
    'SELECT id FROM fableloom_stories WHERE deleted = TRUE AND deleted_at IS NOT NULL AND deleted_at < $1',
    [cutoffIso],
  );
  return rows.map((row) => row.id);
}

export async function writeRaw(id, record) {
  const now = new Date().toISOString();
  const createdAt = mirrorTimestamp(record?.createdAt, now);
  const updatedAt = mirrorTimestamp(record?.updatedAt, createdAt);
  await query(
    `INSERT INTO fableloom_stories (id, name, universe_id, series_id, 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
       name = EXCLUDED.name,
       universe_id = EXCLUDED.universe_id,
       series_id = EXCLUDED.series_id,
       data = EXCLUDED.data,
       updated_at = EXCLUDED.updated_at,
       deleted = EXCLUDED.deleted,
       deleted_at = EXCLUDED.deleted_at`,
    [
      id,
      record.name,
      record.universeId ?? null,
      record.seriesId ?? null,
      toPgJson(record),
      createdAt,
      updatedAt,
      record.deleted === true,
      mirrorTimestamp(record.deletedAt, null),
    ],
  );
  return record;
}

export async function deleteRaw(id) {
  await query('DELETE FROM fableloom_stories WHERE id = $1', [id]);
}