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

13.3 KB
Raw Blame
/** PostgreSQL owns projects, source revision lineage and import-run records. */
import { randomUUID } from 'crypto';
import { query, withTransaction } from '../../lib/db.js';
import { ServerError } from '../../lib/errorHandler.js';
import { toPgJson } from '../../lib/pgJson.js';

const columns = `id, title, data, accepted_revision_id AS "acceptedRevisionId",
  candidate_revision_id AS "candidateRevisionId", created_at AS "createdAt", updated_at AS "updatedAt"`;
const present = row => {
  if (!row) return null;
  const { data, ...metadata } = row;
  return { ...data, ...metadata };
};
const missing = () => new ServerError('Production project not found', { status: 404, code: 'NOT_FOUND' });

export async function createProjectRecord(id, settings) {
  const { rows } = await query(`INSERT INTO code_animation_projects (id, title, data)
    VALUES ($1, $2, $3) RETURNING ${columns}`, [id, settings.manifest.title, settings]);
  return present(rows[0]);
}

export async function getProjectRecord(id) {
  const { rows } = await query(`SELECT ${columns} FROM code_animation_projects WHERE id = $1`, [id]);
  return present(rows[0]) || null;
}

export async function pageProjectRecords({ limit, offset }) {
  const [page, count] = await Promise.all([
    query(`SELECT id, title, accepted_revision_id AS "acceptedRevisionId",
      candidate_revision_id AS "candidateRevisionId", created_at AS "createdAt", updated_at AS "updatedAt"
      FROM code_animation_projects ORDER BY created_at DESC, id DESC LIMIT $1 OFFSET $2`, [limit + 1, offset]),
    query('SELECT COUNT(*)::integer AS total FROM code_animation_projects'),
  ]);
  return { items: page.rows.slice(0, limit), total: count.rows[0].total,
    nextCursor: page.rows.length > limit ? String(offset + limit) : null };
}

export async function patchProjectRecord(id, patch) {
  return withTransaction(async client => {
    const { rows } = await client.query(`SELECT ${columns} FROM code_animation_projects WHERE id = $1 FOR UPDATE`, [id]);
    if (!rows[0]) throw missing();
    const stored = rows[0].data;
    // Nested objects merge field by field: a partial PATCH names only the fields it changes.
    const settings = {
      ...stored, ...patch,
      budgets: { ...stored.budgets, ...patch.budgets },
      localSettings: { ...stored.localSettings, ...patch.localSettings },
      referenceIntent: { ...stored.referenceIntent, ...patch.referenceIntent },
    };
    if (patch.budgets) {
      const used = await client.query("SELECT COALESCE(SUM((data->>'reservedBytes')::bigint), 0) AS bytes FROM code_animation_project_runs WHERE project_id = $1", [id]);
      if (Number(used.rows[0].bytes) > settings.budgets.diskBytes) {
        throw new ServerError('Disk budget cannot be smaller than retained source reservations', { status: 409, code: 'CODE_ANIMATION_DISK_BUDGET' });
      }
    }
    const result = await client.query(`UPDATE code_animation_projects SET title = $2, data = $3, updated_at = NOW()
      WHERE id = $1 RETURNING ${columns}`, [id, settings.manifest.title, settings]);
    return present(result.rows[0]);
  });
}

export async function startImportRecord(id, projectId, data) {
  return withTransaction(async client => {
    const project = await client.query('SELECT data FROM code_animation_projects WHERE id = $1 FOR UPDATE', [projectId]);
    if (!project.rows[0]) throw missing();
    const settings = project.rows[0].data;
    const used = await client.query("SELECT COALESCE(SUM((data->>'reservedBytes')::bigint), 0) AS bytes FROM code_animation_project_runs WHERE project_id = $1", [projectId]);
    const allowed = Number(used.rows[0].bytes) + data.totalBytes <= settings.budgets.diskBytes;
    await client.query(`INSERT INTO code_animation_project_runs (id, project_id, status, data, completed_at)
      VALUES ($1, $2, $3, $4, $5)`, [id, projectId, allowed ? 'staging' : 'failed',
      { ...data, requested: settings.localSettings, budgets: settings.budgets, reservedBytes: allowed ? data.totalBytes : 0 },
      allowed ? null : new Date().toISOString()]);
    return allowed;
  });
}

export async function failImportRecord(id, error, releaseReservation = false) {
  await query(`UPDATE code_animation_project_runs SET status = 'failed',
    data = data || jsonb_build_object('error', $2::text) ||
      CASE WHEN $3 THEN '{"reservedBytes":0}'::jsonb ELSE '{}'::jsonb END,
    completed_at = NOW() WHERE id = $1`, [id, error, releaseReservation]);
}

export async function commitImportRecord(projectId, revision, runId) {
  return withTransaction(async client => {
    const { rows } = await client.query(`SELECT ${columns} FROM code_animation_projects WHERE id = $1 FOR UPDATE`, [projectId]);
    if (!rows[0]) throw missing();
    await client.query(`INSERT INTO code_animation_project_revisions
      (id, project_id, package_hash, source_hash, total_bytes, data)
      VALUES ($1, $2, $3, $4, $5, $6)`,
    [revision.id, projectId, revision.packageHash, revision.sourceHash, revision.totalBytes, revision]);
    await client.query(`UPDATE code_animation_project_runs SET status = 'completed', revision_id = $2, completed_at = NOW() WHERE id = $1`, [runId, revision.id]);
    const saved = await client.query(`UPDATE code_animation_projects SET candidate_revision_id = $2, updated_at = NOW()
      WHERE id = $1 RETURNING ${columns}`, [projectId, revision.id]);
    return present(saved.rows[0]);
  });
}

export async function acceptProjectRevision(projectId, revisionId) {
  const { rows } = await query(`UPDATE code_animation_projects SET accepted_revision_id = $2,
    candidate_revision_id = NULL, updated_at = NOW() WHERE id = $1 AND candidate_revision_id = $2 RETURNING ${columns}`, [projectId, revisionId]);
  if (!rows[0]) throw new ServerError('Candidate changed; reload before accepting source', { status: 409, code: 'CODE_ANIMATION_CANDIDATE_CHANGED' });
  return present(rows[0]);
}

export async function getRevisionRecord(projectId, revisionId) {
  const { rows } = await query('SELECT data FROM code_animation_project_revisions WHERE project_id = $1 AND id = $2', [projectId, revisionId]);
  return rows[0]?.data || null;
}

export async function pageProjectHistory(projectId, { limit, offset }) {
  const { rows } = await query(`SELECT id, status, revision_id AS "revisionId", data,
    created_at AS "createdAt", completed_at AS "completedAt" FROM code_animation_project_runs
    WHERE project_id = $1 ORDER BY created_at DESC, id DESC LIMIT $2 OFFSET $3`, [projectId, limit + 1, offset]);
  return { items: rows.slice(0, limit), nextCursor: rows.length > limit ? String(offset + limit) : null };
}

export async function interruptImports(projectId, activeIds) {
  await query(`UPDATE code_animation_project_runs SET status = 'interrupted', completed_at = NOW()
    WHERE project_id = $1 AND status = 'staging' AND NOT (id = ANY($2::text[]))`, [projectId, activeIds]);
}

// ---- Stage runs (#9389): one active run per project, durable status ----

const RUN_ACTIVE = 'running';

/**
 * Insert a running stage run. Rows left `running` by a previous process (not in
 * `getActiveIds()`) are marked interrupted first, so a restart never blocks the
 * project and never silently resumes. Another live run is a conflict. Resolve
 * live IDs under the project lock; a queued snapshot can omit a newer run.
 */
export async function startStageRun(id, projectId, data, getActiveIds) {
  return withTransaction(async client => {
    const project = await client.query('SELECT data FROM code_animation_projects WHERE id = $1 FOR UPDATE', [projectId]);
    if (!project.rows[0]) throw missing();
    await client.query(`UPDATE code_animation_project_runs SET status = 'interrupted',
      data = data || '{"resumable":true}'::jsonb, completed_at = NOW()
      WHERE project_id = $1 AND status = $2 AND data->>'kind' = 'production-stages' AND NOT (id = ANY($3::text[]))`,
    [projectId, RUN_ACTIVE, getActiveIds()]);
    const live = await client.query(`SELECT id FROM code_animation_project_runs
      WHERE project_id = $1 AND status = $2 AND data->>'kind' = 'production-stages' LIMIT 1`, [projectId, RUN_ACTIVE]);
    if (live.rows[0]) {
      throw new ServerError('A production run is already active for this project', { status: 409, code: 'CODE_ANIMATION_RUN_ACTIVE' });
    }
    await client.query(`INSERT INTO code_animation_project_runs (id, project_id, revision_id, status, data)
      VALUES ($1, $2, $3, $4, $5)`, [id, projectId, data.sourceRevisionId, RUN_ACTIVE, data]);
  });
}

export async function saveStageRun(id, status, data, { revisionId = null, completed = false } = {}) {
  await query(`UPDATE code_animation_project_runs SET status = $2, data = $3,
    revision_id = COALESCE($4, revision_id), completed_at = CASE WHEN $5 THEN NOW() ELSE completed_at END WHERE id = $1`,
  [id, status, data, revisionId, completed]);
}

export async function getRunRecord(projectId, runId) {
  const { rows } = await query(`SELECT id, status, revision_id AS "revisionId", data, created_at AS "createdAt",
    completed_at AS "completedAt" FROM code_animation_project_runs WHERE project_id = $1 AND id = $2`, [projectId, runId]);
  return rows[0] || null;
}

/** Add `bytes` to a run's disk reservation unless it would exceed the project budget. */
export async function reserveRunBytes(runId, projectId, bytes) {
  return withTransaction(async client => {
    const project = await client.query('SELECT data FROM code_animation_projects WHERE id = $1 FOR UPDATE', [projectId]);
    if (!project.rows[0]) throw missing();
    const used = await client.query("SELECT COALESCE(SUM((data->>'reservedBytes')::bigint), 0) AS bytes FROM code_animation_project_runs WHERE project_id = $1", [projectId]);
    if (Number(used.rows[0].bytes) + bytes > project.rows[0].data.budgets.diskBytes) return false;
    await client.query(`UPDATE code_animation_project_runs SET data = jsonb_set(data, '{reservedBytes}',
      to_jsonb(COALESCE((data->>'reservedBytes')::bigint, 0) + $2::bigint)) WHERE id = $1`, [runId, bytes]);
    return true;
  });
}

/** A repaired source is a new immutable candidate revision; the accepted one is untouched. */
export async function commitRepairRevision(projectId, revision) {
  return withTransaction(async client => {
    const { rows } = await client.query(`SELECT ${columns} FROM code_animation_projects WHERE id = $1 FOR UPDATE`, [projectId]);
    if (!rows[0]) throw missing();
    await client.query(`INSERT INTO code_animation_project_revisions
      (id, project_id, package_hash, source_hash, total_bytes, data) VALUES ($1, $2, $3, $4, $5, $6)`,
    [revision.id, projectId, revision.packageHash, revision.sourceHash, revision.totalBytes, revision]);
    const saved = await client.query(`UPDATE code_animation_projects SET candidate_revision_id = $2, updated_at = NOW()
      WHERE id = $1 RETURNING ${columns}`, [projectId, revision.id]);
    return present(saved.rows[0]);
  });
}

export async function interruptStageRuns(projectId, getActiveIds) {
  await withTransaction(async client => {
    await client.query('SELECT id FROM code_animation_projects WHERE id = $1 FOR UPDATE', [projectId]);
    await client.query(`UPDATE code_animation_project_runs SET status = 'interrupted',
      data = data || '{"resumable":true}'::jsonb, completed_at = NOW()
      WHERE project_id = $1 AND status = 'running' AND data->>'kind' = 'production-stages' AND NOT (id = ANY($2::text[]))`, [projectId, getActiveIds()]);
  });
}

// ---- Accepted output (#9392): a frozen record beside, never inside, the run history ----

/**
 * Store the accepted output on the project. A later failed or stale run never
 * touches it. When the output's revision is the current candidate, accepting
 * the output also accepts that source in the same transaction.
 */
export async function setAcceptedOutput(projectId, accepted) {
  return withTransaction(async client => {
    const { rows } = await client.query(`SELECT ${columns} FROM code_animation_projects WHERE id = $1 FOR UPDATE`, [projectId]);
    if (!rows[0]) throw missing();
    const promoteSource = rows[0].candidateRevisionId === accepted.revisionId;
    const result = await client.query(`UPDATE code_animation_projects SET data = data || jsonb_build_object('acceptedOutput', $2::jsonb, 'acceptanceProjection', $5::jsonb),
      accepted_revision_id = CASE WHEN $4 THEN $3 ELSE accepted_revision_id END,
      candidate_revision_id = CASE WHEN $4 THEN NULL ELSE candidate_revision_id END, updated_at = NOW()
      WHERE id = $1 RETURNING ${columns}`, [projectId, toPgJson(accepted), accepted.revisionId, promoteSource,
      toPgJson({ decisionId: randomUUID(), status: 'pending' })]);
    return present(result.rows[0]);
  });
}

/** A history write only acknowledges the exact decision it projected. */
export async function acknowledgeAcceptanceProjection(projectId, decisionId) {
  const { rows } = await query(`UPDATE code_animation_projects
    SET data = jsonb_set(data, '{acceptanceProjection,status}', '"synced"'::jsonb)
    WHERE id = $1 AND data->'acceptanceProjection'->>'decisionId' = $2
    RETURNING ${columns}`, [projectId, decisionId]);
  return present(rows[0]) || getProjectRecord(projectId);
}

export async function pageAcceptedOutputs({ limit, offset }) {
  const { rows } = await query(`SELECT id, title, data->'acceptedOutput' AS accepted FROM code_animation_projects
    WHERE data ? 'acceptedOutput' ORDER BY data->'acceptedOutput'->>'acceptedAt' DESC, id DESC LIMIT $1 OFFSET $2`, [limit + 1, offset]);
  return { items: rows.slice(0, limit), nextCursor: rows.length > limit ? String(offset + limit) : null };
}