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.8 KB
Raw Blame
/**
 * Persistence for the CoS task queues (`cos_tasks`): one row per task, per
 * queue ('user' = the human's task list, 'internal' = the system's).
 *
 * PostgreSQL is authoritative. The in-memory backend exists only for the
 * documented development/test escape hatch (it persists to `data/cos-tasks.json`
 * outside the test runner so a database-less dev run survives a restart).
 *
 * Callers work with the WORKING SET: every open task plus recently completed
 * ones (see lib/cosTaskRows.js), which is what the old task files held. Older
 * completed tasks are reached by id or history paging, never loaded wholesale.
 * `version` bumps on every write so a cached working set can be checked with a
 * single-row read.
 */

import { join } from 'node:path';
import { atomicWrite, PATHS, readJSONFileStrict } from '../lib/fileUtils.js';
import { createPgFileFacade, resolvePgBackend } from '../lib/pgFileFacade.js';
import { isTestRunner } from '../lib/runtimeEnv.js';
import {
  WORKING_SET_COMPLETED_WINDOW_MS, WORKING_SET_KEEP_COMPLETED, completionStamp, rowColumns, selectWorkingSet,
} from '../lib/cosTaskRows.js';
import { toPgJson } from '../lib/pgJson.js';

const SOURCES = ['user', 'internal'];
// Bounds a single statement's parameter payload (prompts can be MBs).
const UPSERT_BATCH = 100;
const conflict = () => Object.assign(new Error('Task queue changed; reload and retry the operation'), {
  code: 'COS_TASK_LIST_CONFLICT', status: 409,
});

function checkExpectedVersions(source, expectedVersions, versions) {
  if (!Number.isSafeInteger(expectedVersions?.[source]) || expectedVersions[source] < 0
    || Object.entries(expectedVersions).some(([key, value]) => !SOURCES.includes(key) || !Number.isSafeInteger(value) || value < 0)) {
    throw new Error('Task saves require the versions of the snapshots they read');
  }
  if (Object.entries(expectedVersions).some(([key, value]) => versions[key] !== value)) throw conflict();
}

// Every writer locks the same version rows before touching tasks. The fixed
// order also makes a save conditional on both queues safe for cross-queue dedup.
async function lockVersions(client) {
  await client.query(`INSERT INTO cos_task_lists (source, version) VALUES ('user', 0), ('internal', 0) ON CONFLICT DO NOTHING`);
  const { rows } = await client.query('SELECT source, version FROM cos_task_lists ORDER BY source FOR UPDATE');
  return Object.fromEntries(rows.map((row) => [row.source, Number(row.version)]));
}

const tasksFile = () => join(PATHS.data, 'cos-tasks.json');
const byIdDesc = (a, b) => (a.id < b.id ? 1 : a.id > b.id ? -1 : 0);

function makePgBackend(db) {
  const { query, withTransaction } = db;
  return {
    name: 'postgres',
    async readVersion(source) {
      const { rows } = await query('SELECT version FROM cos_task_lists WHERE source = $1', [source]);
      return rows[0] ? Number(rows[0].version) : 0;
    },
    async loadWorkingSet(source, { now = Date.now() } = {}) {
      // One MVCC snapshot binds the version to the rows, including an empty list.
      const { rows } = await query(
        `WITH newest AS (
           SELECT task_id FROM cos_tasks WHERE source = $1 AND status = 'completed'
           ORDER BY completed_at DESC NULLS LAST, task_id DESC LIMIT $3
         ), working AS (
         SELECT task, position FROM cos_tasks
         WHERE source = $1 AND (status <> 'completed' OR completed_at >= $2 OR task_id IN (SELECT task_id FROM newest))
         ORDER BY CASE status WHEN 'pending' THEN 0 WHEN 'in_progress' THEN 1 WHEN 'challenged' THEN 2
                              WHEN 'blocked' THEN 3 ELSE 4 END,
                  priority_value DESC, position, task_id
         )
         SELECT COALESCE((SELECT version FROM cos_task_lists WHERE source = $1), 0) AS version,
                COALESCE(json_agg(working), '[]'::json) AS rows FROM working`,
        [source, new Date(now - WORKING_SET_COMPLETED_WINDOW_MS).toISOString(), WORKING_SET_KEEP_COMPLETED],
      );
      return { version: Number(rows[0].version), rows: rows[0].rows };
    },
    async apply(source, { upserts, deletes, expectedVersions }) {
      return withTransaction(async (client) => {
        checkExpectedVersions(source, expectedVersions, await lockVersions(client));
        if (deletes.length > 0) await client.query('DELETE FROM cos_tasks WHERE source = $1 AND task_id = ANY($2::text[])', [source, deletes]);
        for (let start = 0; start < upserts.length; start += UPSERT_BATCH) {
          const columns = upserts.slice(start, start + UPSERT_BATCH).map(({ task, position }) => ({ task, position, ...rowColumns(task) }));
          await client.query(
            `INSERT INTO cos_tasks (source, task_id, status, priority_value, position, completed_at, description, task)
             SELECT $1, u.task_id, u.status, u.priority_value, u.position, u.completed_at, u.description, u.task::jsonb
             FROM unnest($2::text[], $3::text[], $4::int[], $5::float8[], $6::timestamptz[], $7::text[], $8::text[])
               AS u(task_id, status, priority_value, position, completed_at, description, task)
             ON CONFLICT (source, task_id) DO UPDATE SET status = EXCLUDED.status, priority_value = EXCLUDED.priority_value,
               position = EXCLUDED.position, completed_at = EXCLUDED.completed_at, description = EXCLUDED.description, task = EXCLUDED.task`,
            [source, columns.map((c) => c.task.id), columns.map((c) => c.status), columns.map((c) => c.priorityValue),
              columns.map((c) => c.position), columns.map((c) => c.completedAt), columns.map((c) => c.description),
              columns.map((c) => toPgJson(c.task))],
          );
        }
        return bumpVersions(client, [source]);
      });
    },
    async getTask(taskId, source) {
      const { rows } = await query(
        `SELECT source, task, position FROM cos_tasks WHERE task_id = $1 AND ($2::text IS NULL OR source = $2)
         ORDER BY (source = 'user') DESC LIMIT 1`,
        [taskId, source],
      );
      return rows[0] ? { source: rows[0].source, task: rows[0].task, position: rows[0].position } : null;
    },
    async completedIds(source, ids) {
      if (ids.length === 0) return new Set();
      const { rows } = await query(
        `SELECT task_id FROM cos_tasks WHERE source = $1 AND status = 'completed' AND task_id = ANY($2::text[])`,
        [source, ids],
      );
      return new Set(rows.map((row) => row.task_id));
    },
    async pageCompleted(source, { before, offset, limit }) {
      const { rows } = await query(
        `SELECT task FROM cos_tasks WHERE source = $1 AND status = 'completed' AND ($2::text IS NULL OR task_id < $2)
         ORDER BY task_id DESC LIMIT $3 OFFSET $4`,
        [source, before ?? null, limit, offset],
      );
      return rows.map((row) => row.task);
    },
    async countCompleted(source) {
      const { rows } = await query(`SELECT count(*)::int AS n FROM cos_tasks WHERE source = $1 AND status = 'completed'`, [source]);
      return rows[0].n;
    },
    async completedSummaries() {
      const { rows } = await query(`SELECT source, task_id, description FROM cos_tasks WHERE status = 'completed'`);
      return rows.map((row) => ({ source: row.source, id: row.task_id, description: row.description }));
    },
    async deleteById(taskId, source) {
      return withTransaction(async (client) => {
        await lockVersions(client);
        const { rows } = await client.query(
          'DELETE FROM cos_tasks WHERE task_id = $1 AND ($2::text IS NULL OR source = $2) RETURNING source',
          [taskId, source],
        );
        if (rows.length > 0) await bumpVersions(client, [...new Set(rows.map((row) => row.source))]);
        return rows.length;
      });
    },
  };
}

// Bumps each source's version and returns the last one (callers pass one source for a save).
async function bumpVersions(client, sources) {
  let version = 0;
  for (const source of sources) {
    const { rows } = await client.query(
      `INSERT INTO cos_task_lists (source, version) VALUES ($1, 1)
       ON CONFLICT (source) DO UPDATE SET version = cos_task_lists.version + 1 RETURNING version`,
      [source],
    );
    version = Number(rows[0].version);
  }
  return version;
}

async function makeMemoryBackend({ persist }) {
  const lists = Object.fromEntries(SOURCES.map((source) => [source, new Map()]));
  const versions = Object.fromEntries(SOURCES.map((source) => [source, 0]));
  if (persist) {
    // readJSONFileStrict returns `{ ok, value }`; never start empty over an unreadable file.
    const { ok, value: saved } = await readJSONFileStrict(tasksFile(), {}, { logError: false });
    if (!ok) throw new Error(`Unreadable ${tasksFile()} — repair or remove it before starting the dev file backend`);
    for (const source of SOURCES) {
      for (const row of saved?.[source]?.rows ?? []) lists[source].set(row.task.id, row);
      versions[source] = saved?.[source]?.version ?? 0;
    }
  }
  const flush = () => (persist
    ? atomicWrite(tasksFile(), Object.fromEntries(SOURCES.map((source) => [source, { version: versions[source], rows: [...lists[source].values()] }])))
    : Promise.resolve());
  // Plain JSON, like a database round trip; also keeps structuredClone spies in tests measuring only the store.
  const clone = (value) => JSON.parse(JSON.stringify(value));
  const completed = (source) => [...lists[source].values()].map((row) => row.task).filter((task) => task.status === 'completed');

  return {
    name: 'memory',
    async readVersion(source) { return versions[source]; },
    async loadWorkingSet(source, { now = Date.now() } = {}) {
      return { version: versions[source], rows: clone(selectWorkingSet([...lists[source].values()], { now })) };
    },
    async apply(source, { upserts, deletes, expectedVersions }) {
      checkExpectedVersions(source, expectedVersions, versions);
      for (const id of deletes) lists[source].delete(id);
      for (const { task, position } of upserts) lists[source].set(task.id, clone({ task, position }));
      versions[source] += 1;
      await flush();
      return versions[source];
    },
    async getTask(taskId, source) {
      for (const candidate of source ? [source] : SOURCES) {
        const row = lists[candidate].get(taskId);
        if (row) return { source: candidate, ...clone(row) };
      }
      return null;
    },
    async completedIds(source, ids) {
      return new Set(completed(source).map((task) => task.id).filter((id) => ids.includes(id)));
    },
    async pageCompleted(source, { before, offset, limit }) {
      return clone(completed(source).filter((task) => before == null || task.id < before).sort(byIdDesc).slice(offset, offset + limit));
    },
    async countCompleted(source) { return completed(source).length; },
    async completedSummaries() {
      return SOURCES.flatMap((source) => completed(source).map((task) => ({ source, id: task.id, description: rowColumns(task).description })));
    },
    async deleteById(taskId, source) {
      let removed = 0;
      for (const candidate of source ? [source] : SOURCES) {
        if (lists[candidate].delete(taskId)) { removed += 1; versions[candidate] += 1; }
      }
      if (removed > 0) await flush();
      return removed;
    },
  };
}

const backendFacade = createPgFileFacade({
  makeFile: () => makeMemoryBackend({ persist: !isTestRunner() }),
  makePg: () => resolvePgBackend({
    requirement: 'The CoS task queues require PostgreSQL — run `npm run setup:db` (dev/test only: set MEMORY_BACKEND=file for the file escape hatch)',
    loadDb: () => import('../lib/db.js'),
    makePg: makePgBackend,
  }),
});
const backend = async () => backendFacade.getBackend();

/** Queue version: changes whenever a write lands, so a cached working set can be revalidated cheaply. */
export async function readTaskListVersion(source) { return (await backend()).readVersion(source); }

/** `{ version, rows: [{ task, position }] }` in queue order for one queue's working set. */
export async function loadTaskWorkingSet(source) { return (await backend()).loadWorkingSet(source); }

/** Persist a plan with expectedVersions in one transaction; stale snapshots fail with COS_TASK_LIST_CONFLICT. */
export async function applyTaskListSave(source, plan) { return (await backend()).apply(source, plan); }

/** One task as `{ source, task, position }` whatever its status or age, user queue first. Omit `source` to search both. */
export async function getStoredTask(taskId, source = null) { return (await backend()).getTask(taskId, source); }

/** The subset of `ids` already stored as completed in `source`, including ones outside the working set. */
export async function getCompletedTaskIds(source, ids) { return (await backend()).completedIds(source, ids); }

/** Completed history, newest id first: strictly before `before`, skipping `offset`, at most `limit`. */
export async function pageCompletedTasks(source, { before = null, offset = 0, limit = 25 } = {}) {
  return (await backend()).pageCompleted(source, { before, offset, limit });
}

export async function countCompletedTasks(source) { return (await backend()).countCompleted(source); }

/** `{ source, id, description }` for every completed task, without prompt bodies. */
export async function listCompletedTaskSummaries() { return (await backend()).completedSummaries(); }

/** Remove a task wherever it is stored; returns how many rows went. */
export async function deleteStoredTask(taskId, source = null) { return (await backend()).deleteById(taskId, source); }

/** Test seam: drop the selected backend (and, for the in-memory one, everything in it). */
export function __resetCosTaskRepository() { backendFacade.reset(); }

/** Test seam: the PostgreSQL backend over a `{ query, withTransaction }` handle, for the DB suite. */
export const __makePgCosTaskBackend = makePgBackend;
/** Test seam: a fresh in-memory backend; it touches disk only with `persist: true`. */
export const __makeMemoryCosTaskBackend = ({ persist = false } = {}) => makeMemoryBackend({ persist });