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
JavaScript ai-agentsdashboarddeveloper-toolsdigital-twinquantified-selfsecond-brainself-hostedtailscale
/**
* Creative Commission — PostgreSQL leaf I/O (#2657, Autonomous Creation Engine).
*
* One row per commission in `creative_commissions`: the full sanitized record
* (brief / schedule / generation / feedback / runs[]) in `data` JSONB, with
* id / name / enabled / created_at / updated_at mirrored into columns for the
* scheduler's "arm every enabled commission" query. This is the DEFAULT backend
* for real installs; the file backend (collectionStore) is a dev/test escape
* hatch only (see store.js).
*
* INTENTIONALLY MACHINE-LOCAL — never federated (a synced schedule would
* double-run on every peer). So there is NO sync_sequence and NO
* deleted/deleted_at tombstone: `deleteRaw` is a hard delete, mirroring
* tribe_people (ADR docs/decisions/2026-06-26-tribe-and-universe-runs-local.md).
*
* This module is PURE leaf I/O — no sanitizing, no serialization. The store
* facade (store.js) owns the sanitizer + merge semantics AND serializes the
* scheduler-vs-request read-modify-write on a shared per-id write queue
* (`createRecordWriteQueue`, identical to universeBuilder/storyBuilder), 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 (#2686). */
export async function readRaw(id, { includeDeleted = false } = {}) {
const { rows } = await query(
includeDeleted
? `SELECT data FROM creative_commissions WHERE id = $1`
: `SELECT data FROM creative_commissions WHERE id = $1 AND deleted = FALSE`,
[id],
);
return rows[0]?.data ?? null;
}
/** Every commission's raw `data` JSONB, oldest first. Live only unless includeDeleted. */
export async function listRaw({ includeDeleted = false } = {}) {
const { rows } = await query(
includeDeleted
? `SELECT data FROM creative_commissions ORDER BY created_at ASC, id ASC`
: `SELECT data FROM creative_commissions WHERE deleted = FALSE ORDER BY created_at ASC, id ASC`,
);
return rows.map((r) => r.data);
}
/** Every commission id — live only by default, or all (incl. tombstones) for the sweep. */
export async function listIds({ includeDeleted = false } = {}) {
const { rows } = await query(
includeDeleted
? `SELECT id FROM creative_commissions`
: `SELECT id FROM creative_commissions WHERE deleted = FALSE`,
);
return rows.map((r) => r.id);
}
/**
* Upsert one record. `data` is written verbatim (lossless); the typed mirror
* columns are bind-sanitized so a hand-edited/legacy record with a malformed
* timestamp can't make the write throw. `created_at` is preserved on conflict
* (only the first INSERT sets it). The soft-delete pair (#2686) is mirrored into
* columns for the sweep AND stays in `data` (the sanitizer round-trips it).
*/
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 creative_commissions (id, name, enabled, data, created_at, updated_at, deleted, deleted_at)
VALUES ($1, $2, $3, $4::jsonb, $5, $6, $7, $8)
ON CONFLICT (id) DO UPDATE SET
name = EXCLUDED.name,
enabled = EXCLUDED.enabled,
data = EXCLUDED.data,
updated_at = EXCLUDED.updated_at,
deleted = EXCLUDED.deleted,
deleted_at = EXCLUDED.deleted_at`,
[
id,
typeof record?.name === 'string' ? record.name : '',
record?.enabled !== false,
toPgJson(record),
createdAt,
mirrorTimestamp(record?.updatedAt, createdAt),
deleted,
deletedAt,
],
);
return record;
}
/** Hard-delete a record (used by the tombstone sweep). Idempotent — a missing row is a no-op. */
export async function deleteRaw(id) {
await query(`DELETE FROM creative_commissions WHERE id = $1`, [id]);
}
/**
* Ids eligible for pruneTombstoned, by the SAME `deleted_at` column predicate
* the DELETE uses. The column is the backend's truth — writeRaw normalizes
* malformed/out-of-range timestamps into it while preserving the JSON verbatim,
* so an eligibility check against the raw JSON `deletedAt` can diverge from
* what the DELETE would actually remove.
*/
export async function listPrunable(olderThanMs) {
if (!Number.isFinite(olderThanMs)) return [];
const cutoffIso = new Date(olderThanMs).toISOString();
const { rows } = await query(
`SELECT id FROM creative_commissions
WHERE deleted = TRUE AND deleted_at IS NOT NULL AND deleted_at < $1`,
[cutoffIso],
);
return rows.map((r) => r.id);
}
/** Whether ONE id currently satisfies the prune predicate (same column truth as listPrunable). */
export async function isPrunable(id, olderThanMs) {
if (!Number.isFinite(olderThanMs)) return false;
const cutoffIso = new Date(olderThanMs).toISOString();
const { rows } = await query(
`SELECT 1 FROM creative_commissions
WHERE id = $1 AND deleted = TRUE AND deleted_at IS NOT NULL AND deleted_at < $2`,
[id, cutoffIso],
);
return rows.length > 0;
}
/** Hard-remove tombstoned commissions 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 creative_commissions
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 };
}