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
/**
* Pipeline series — PostgreSQL leaf I/O (#1015).
*
* One row per series in `pipeline_series`: the full sanitized record
* (arc, seasons[], locks, covers, style notes) in `data` JSONB, with
* name / universe_id / writers_room_work_id / ephemeral / updated_at /
* deleted / deleted_at mirrored into columns for the queries the service +
* federation actually run (rename cascade, universe delete-guard, "series in
* this universe" lists, snapshot ephemeral-filter, LWW staleness, the
* writers-room promote back-link).
*
* PURE leaf I/O — no in-process serialization, no sanitizing. The store facade
* (store.js) owns the per-id write queue + the mutation epoch and applies
* `sanitizeSeries` on read, so reads here return `data` verbatim (the columns
* are a queryable mirror, never read back).
*
* The series' `manuscript-review.json` sibling doc is `file-primary` and does
* NOT live here — it stays on disk under data/pipeline-series/{id}/ (see
* manuscriptReview.js); only the series record itself moved to PG.
*/
import { query } from '../../../lib/db.js';
import { mirrorTimestamp } from '../../../lib/pgTimestamp.js';
import { toPgJson } from '../../../lib/pgJson.js';
/** Raw on-disk-equivalent record (the `data` JSONB), or null. No sanitize. */
export async function readRaw(id) {
const { rows } = await query(`SELECT data FROM pipeline_series WHERE id = $1`, [id]);
return rows[0]?.data ?? null;
}
/** Every series id (live, ephemeral, AND tombstones) — the service filters. */
export async function listIds() {
const { rows } = await query(`SELECT id FROM pipeline_series`);
return rows.map((r) => r.id);
}
/**
* Live (non-deleted) series ids only — id-only projection. 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 series' JSONB body.
*/
export async function listLiveIds() {
const { rows } = await query(`SELECT id FROM pipeline_series WHERE deleted = FALSE`);
return rows.map((r) => r.id);
}
/**
* Tombstoned series ids whose deletedAt is older than `beforeMs` (epoch ms) —
* the GC candidate scan for `pruneTombstonedSeries`. An id-only projection:
* the sweep almost never finds a tombstone, so the common case must not pay
* for a single series' 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 pipeline_series WHERE deleted = TRUE AND deleted_at IS NOT NULL AND deleted_at < $1`,
[cutoffIso],
);
return rows.map((r) => r.id);
}
/** Every record's raw `data` JSONB in one query (live/ephemeral/tombstones). */
export async function listRaw() {
const { rows } = await query(`SELECT data FROM pipeline_series`);
return rows.map((r) => r.data);
}
/** Names and ordering only: no JSONB body is read for sidebar navigation. */
export async function listNames() {
const { rows } = await query(
`SELECT id, name, to_char(updated_at AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"') AS "updatedAt"
FROM pipeline_series WHERE deleted = FALSE`,
);
return rows;
}
/** Fields used by index rows and universe-scoped pickers, without arc/seasons. */
export async function listSummaries() {
const { rows } = await query(
`SELECT id, name, universe_id AS "universeId",
data->>'logline' AS logline, data->>'coverImage' AS "coverImage",
data->'origin' AS origin, data->'issueCountTarget' AS "issueCountTarget",
jsonb_build_object('shape', data->'arc'->'shape') AS arc,
data->>'createdAt' AS "createdAt", data->>'updatedAt' AS "updatedAt"
FROM pipeline_series WHERE deleted = FALSE`,
);
return rows;
}
/**
* 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 or missing field can't make the write throw (which, during the boot
* warm/import, would block the whole backend). `created_at` is preserved on
* conflict — only the first INSERT sets it.
*/
export async function writeRaw(id, record) {
const now = new Date().toISOString();
const createdAt = mirrorTimestamp(record?.createdAt, now);
await query(
`INSERT INTO pipeline_series (id, name, universe_id, writers_room_work_id, data, ephemeral, created_at, updated_at, deleted, deleted_at)
VALUES ($1, $2, $3, $4, $5::jsonb, $6, $7, $8, $9, $10)
ON CONFLICT (id) DO UPDATE SET
name = EXCLUDED.name,
universe_id = EXCLUDED.universe_id,
writers_room_work_id = EXCLUDED.writers_room_work_id,
data = EXCLUDED.data,
ephemeral = EXCLUDED.ephemeral,
updated_at = EXCLUDED.updated_at,
deleted = EXCLUDED.deleted,
deleted_at = EXCLUDED.deleted_at`,
[
id,
typeof record?.name === 'string' ? record.name : '',
typeof record?.universeId === 'string' && record.universeId ? record.universeId : null,
typeof record?.writersRoomWorkId === 'string' && record.writersRoomWorkId ? record.writersRoomWorkId : null,
toPgJson(record),
record?.ephemeral === true,
createdAt,
mirrorTimestamp(record?.updatedAt, createdAt),
record?.deleted === true,
mirrorTimestamp(record?.deletedAt, null),
],
);
return record;
}
/** Hard-delete a record (tombstone GC). Idempotent — missing row is a no-op. */
export async function deleteRaw(id) {
await query(`DELETE FROM pipeline_series WHERE id = $1`, [id]);
}