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

4.6 KB
Raw Blame
/**
 * Creative Commission feedback — PostgreSQL leaf I/O (#2686, split-record
 * federation).
 *
 * One row per reaction in `commission_feedback`: the full sanitized record
 * (commissionId / runId / rating / note / tags / at) in `data` JSONB, with
 * commission_id / run_id / created_at / updated_at / deleted / deleted_at
 * mirrored into columns for the per-commission hydration query and the
 * federation sweep. This is the DEFAULT backend for real installs; the file
 * backend (collectionStore) is a dev/test escape hatch only (see feedbackStore.js).
 *
 * FEDERATED (unlike the parent commission, which stays machine-local): the
 * `deleted`/`deleted_at` tombstone columns carry a soft-delete so a removal
 * propagates without an out-of-date peer resurrecting it (the LWW merge never
 * propagates a hard delete). `data` mirrors the soft-delete pair so a tombstone
 * round-trips through the sanitizer.
 *
 * PURE leaf I/O — no sanitizing, no serialization, no LWW policy. The store
 * facade (feedbackStore.js) owns the sanitizer + merge decision (via the pure
 * feedbackLogic.js) and serializes writes on a per-id queue, so reads here return
 * `data` verbatim (the columns are a queryable mirror, never read back) and
 * writes are plain upserts.
 */

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

/** Raw stored record (the `data` JSONB), or null. Live only unless includeDeleted. */
export async function readRaw(id, { includeDeleted = false } = {}) {
  const { rows } = await query(
    includeDeleted
      ? `SELECT data FROM commission_feedback WHERE id = $1`
      : `SELECT data FROM commission_feedback WHERE id = $1 AND deleted = FALSE`,
    [id],
  );
  return rows[0]?.data ?? null;
}

/** Every LIVE feedback record's raw `data` JSONB, oldest reaction first. */
export async function listRaw() {
  const { rows } = await query(
    `SELECT data FROM commission_feedback WHERE deleted = FALSE ORDER BY created_at ASC, id ASC`,
  );
  return rows.map((r) => r.data);
}

/** Every LIVE feedback record for one commission, oldest reaction first. */
export async function listRawByCommission(commissionId) {
  const { rows } = await query(
    `SELECT data FROM commission_feedback
     WHERE commission_id = $1 AND deleted = FALSE
     ORDER BY created_at ASC, id ASC`,
    [commissionId],
  );
  return rows.map((r) => r.data);
}

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

/**
 * Upsert one feedback record. `data` is written verbatim (lossless); the typed
 * mirror columns are bind-sanitized. `created_at` is preserved on conflict (only
 * the first INSERT sets it), so a re-rating (deterministic id) keeps the original
 * creation moment while `updated_at`/`data` reflect the new reaction.
 */
export async function writeRaw(id, record) {
  const now = new Date().toISOString();
  const createdAt = mirrorTimestamp(record?.createdAt, now);
  const deleted = record?.deleted === true;
  const deletedAt = deleted ? mirrorTimestamp(record?.deletedAt, now) : null;
  await query(
    `INSERT INTO commission_feedback (id, commission_id, run_id, data, created_at, updated_at, deleted, deleted_at)
     VALUES ($1, $2, $3, $4::jsonb, $5, $6, $7, $8)
     ON CONFLICT (id) DO UPDATE SET
       commission_id = EXCLUDED.commission_id,
       run_id = EXCLUDED.run_id,
       data = EXCLUDED.data,
       updated_at = EXCLUDED.updated_at,
       deleted = EXCLUDED.deleted,
       deleted_at = EXCLUDED.deleted_at`,
    [
      id,
      typeof record?.commissionId === 'string' && record.commissionId ? record.commissionId : null,
      typeof record?.runId === 'string' && record.runId ? record.runId : null,
      toPgJson(record),
      createdAt,
      mirrorTimestamp(record?.updatedAt, createdAt),
      deleted,
      deletedAt,
    ],
  );
  return record;
}

/** Hard-remove tombstoned rows whose `deleted_at` is older than the cutoff; returns their ids. */
export async function pruneTombstoned(olderThanMs) {
  if (!Number.isFinite(olderThanMs)) return { pruned: 0, ids: [] };
  const cutoffIso = new Date(olderThanMs).toISOString();
  const { rows } = await query(
    `DELETE FROM commission_feedback
     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 };
}