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

11.6 KB
Raw Blame
/**
 * Postgres-backed round-trip for the Universe Builder DB adapter (#1014).
 *
 * Like catalogUserTypes/db.test.js, this needs a live PostgreSQL with the schema
 * applied. If no DB is reachable (CI, fresh checkout) it SKIPS cleanly rather
 * than failing red. When a DB IS reachable it exercises the leaf I/O: verbatim
 * record readback, listIds across live/tombstone/ephemeral, malformed-timestamp
 * tolerance, the runs cap + cascade-remove, and hard delete. It snapshots and
 * restores both tables so a developer's real universes survive the run.
 */

import { describe, it, expect, afterAll, beforeAll, beforeEach } from 'vitest';
import { checkHealth, ensureSchema, query, close } from '../../lib/db.js';
import { requireDbOrSkip } from '../../lib/dbTestGate.js';

let dbReady = false;
let skipReason = '';
{
  const health = await checkHealth().catch((e) => ({ connected: false, error: e?.message }));
  if (!health.connected) {
    skipReason = `Postgres not reachable (${health.error || 'no connection'})`;
  } else {
    await ensureSchema().catch(() => {});
    const probe = await query(
      `SELECT EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'universes') AS ok`,
    ).catch(() => ({ rows: [{ ok: false }] }));
    if (probe.rows?.[0]?.ok) dbReady = true;
    else skipReason = 'universes table not present';
  }
}

const runDb = requireDbOrSkip('services/universeBuilder/db.test', dbReady, skipReason);

const U = (id, extra = {}) => ({
  id, name: id, schemaVersion: 4,
  createdAt: '2026-01-01T00:00:00.000Z', updatedAt: '2026-01-02T00:00:00.000Z',
  deleted: false, deletedAt: null, ...extra,
});

describe.skipIf(!runDb)('universeBuilder DB adapter round-trip', () => {
  let db;
  let uSnap = [];
  let rSnap = [];
  beforeAll(async () => {
    db = await import('./db.js');
    uSnap = (await query(`SELECT * FROM universes`)).rows;
    rSnap = (await query(`SELECT * FROM universe_runs`)).rows;
  });

  beforeEach(async () => {
    await query(`DELETE FROM universe_runs`);
    await query(`DELETE FROM universes`);
  });

  afterAll(async () => {
    await query(`DELETE FROM universe_runs`).catch(() => {});
    await query(`DELETE FROM universes`).catch(() => {});
    for (const r of uSnap) {
      await query(
        `INSERT INTO universes (id, name, data, schema_version, ephemeral, created_at, updated_at, deleted, deleted_at)
         VALUES ($1,$2,$3::jsonb,$4,$5,$6,$7,$8,$9) ON CONFLICT (id) DO NOTHING`,
        [r.id, r.name, JSON.stringify(r.data), r.schema_version, r.ephemeral, r.created_at, r.updated_at, r.deleted, r.deleted_at],
      ).catch(() => {});
    }
    for (const r of rSnap) {
      await query(
        `INSERT INTO universe_runs (id, universe_id, collection_id, data, created_at)
         VALUES ($1,$2,$3,$4::jsonb,$5) ON CONFLICT (id) DO NOTHING`,
        [r.id, r.universe_id, r.collection_id, JSON.stringify(r.data), r.created_at],
      ).catch(() => {});
    }
    await close();
  });


  it('projects names and index counts, with a compatibility fallback only for legacy canon', async () => {
    await db.writeRaw('u-live', U('u-live', {
      logline: 'A city', starterPrompt: 'Treatment '.repeat(1000), characters: [{ id: 'c1', name: 'Ada' }], places: [], objects: [],
      styleImageRefs: ['old.png', 'last.png'], premise: 'Not a summary field',
    }));
    await db.writeRaw('u-legacy', U('u-legacy', { schemaVersion: 3, categories: {}, starterPrompt: 'Legacy treatment '.repeat(1000) }));
    await db.writeRaw('u-deleted', U('u-deleted', { deleted: true }));
    const names = await db.listNames();
    expect(names.map((u) => u.id).sort()).toEqual(['u-legacy', 'u-live']);
    expect(names.find((u) => u.id === 'u-live')).toEqual({
      id: 'u-live', name: 'u-live', createdAt: '2026-01-01T00:00:00.000Z',
    });
    const summaries = await db.listSummaries();
    expect(summaries).toHaveLength(2);
    const live = summaries.find((u) => u.id === 'u-live');
    expect(live).toMatchObject({ canonCount: 1, styleImageRef: 'last.png', logline: 'A city', legacyRecord: null });
    expect(live.starterPrompt).toHaveLength(500);
    expect(live).not.toHaveProperty('characters');
    expect(live).not.toHaveProperty('premise');
    expect(summaries.find((u) => u.id === 'u-legacy').legacyRecord).toMatchObject({
      schemaVersion: 3, starterPrompt: 'Legacy treatment '.repeat(1000).slice(0, 500),
    });
  });

  it('writes a record and reads it back verbatim', async () => {
    const rec = U('u-1', { logline: 'a city', characters: [{ id: 'c1', name: 'Ada' }] });
    await db.writeRaw('u-1', rec);
    const back = await db.readRaw('u-1');
    expect(back).toEqual(rec);
  });

  it('upsert updates the record and the mirror columns', async () => {
    await db.writeRaw('u-1', U('u-1', { name: 'First' }));
    await db.writeRaw('u-1', U('u-1', { name: 'Renamed', updatedAt: '2026-03-03T00:00:00.000Z' }));
    const back = await db.readRaw('u-1');
    expect(back.name).toBe('Renamed');
    const col = (await query(`SELECT name, updated_at FROM universes WHERE id = 'u-1'`)).rows[0];
    expect(col.name).toBe('Renamed');
    expect(new Date(col.updated_at).toISOString()).toBe('2026-03-03T00:00:00.000Z');
  });

  it('listIds returns live, tombstoned, and ephemeral ids alike', async () => {
    await db.writeRaw('live', U('live'));
    await db.writeRaw('dead', U('dead', { deleted: true, deletedAt: '2026-02-02T00:00:00.000Z' }));
    await db.writeRaw('ghost', U('ghost', { ephemeral: true }));
    const ids = (await db.listIds()).sort();
    expect(ids).toEqual(['dead', 'ghost', 'live']);
  });

  it('listLiveIds returns only non-deleted ids', async () => {
    await db.writeRaw('live', U('live'));
    await db.writeRaw('dead', U('dead', { deleted: true, deletedAt: '2026-02-02T00:00:00.000Z' }));
    await db.writeRaw('ghost', U('ghost', { ephemeral: true }));
    expect((await db.listLiveIds()).sort()).toEqual(['ghost', 'live']);
  });

  it('listTombstoneIdsBefore returns only tombstones older than the cutoff, keeping a NULL deleted_at', async () => {
    await db.writeRaw('live', U('live'));
    await db.writeRaw('old', U('old', { deleted: true, deletedAt: '2026-01-01T00:00:00.000Z' }));
    await db.writeRaw('new', U('new', { deleted: true, deletedAt: '2026-06-01T00:00:00.000Z' }));
    // writeRaw's mirrorTimestamp(record.deletedAt, null) falls back to NULL
    // (not NOW()) for an unparseable deletedAt — `deleted_at < $1` is never
    // true against NULL, so this row is conservatively excluded from every
    // cutoff, matching the JS filter's "unparseable → kept" rule.
    await db.writeRaw('bad', U('bad', { deleted: true, deletedAt: 'not-a-date' }));
    const cutoff = Date.parse('2026-03-01T00:00:00.000Z');
    expect(await db.listTombstoneIdsBefore(cutoff)).toEqual(['old']);
  });

  it('listRaw returns every record body verbatim in one query', async () => {
    await db.writeRaw('u-1', U('u-1', { logline: 'x' }));
    await db.writeRaw('u-2', U('u-2'));
    const all = await db.listRaw();
    expect(all.map((r) => r.id).sort()).toEqual(['u-1', 'u-2']);
    expect(all.find((r) => r.id === 'u-1').logline).toBe('x');
  });

  it('listStyles projects the live set down to name + style fields', async () => {
    await db.writeRaw('live', U('live', {
      logline: 'a logline that must not ship',
      influences: { embrace: ['inky linework'], avoid: ['lowres'] },
    }));
    await db.writeRaw('dead', U('dead', { deleted: true, deletedAt: '2026-02-02T00:00:00.000Z' }));
    await db.writeRaw('legacy', U('legacy', { stylePrompt: 'ink wash', negativePrompt: 'blurry' }));

    const rows = await db.listStyles();
    expect(rows.map((r) => r.id).sort()).toEqual(['legacy', 'live']);

    const live = rows.find((r) => r.id === 'live');
    expect(live.name).toBe('live');
    expect(live.influences).toEqual({ embrace: ['inky linework'], avoid: ['lowres'] });
    // The projection is the whole point — nothing else comes back. Keys must
    // also match the file backend's row exactly (store.test.js pins that side).
    expect(Object.keys(live).sort())
      .toEqual(['createdAt', 'id', 'influences', 'name', 'negativePrompt', 'stylePrompt']);
    expect(live.logline).toBeUndefined();

    // Legacy v2 prose fields ride along so the service can fold them into tokens.
    const legacy = rows.find((r) => r.id === 'legacy');
    expect(legacy.stylePrompt).toBe('ink wash');
    expect(legacy.negativePrompt).toBe('blurry');
    expect(legacy.influences).toBeNull();
  });

  it('countUniverses counts the live set without materializing it', async () => {
    // The cheap tally the character skill registry reads (#2729). It counts the
    // `deleted` MIRROR COLUMN, while the service filters listUniverses() on the
    // JSONB flag — so this pins that the two can't drift: writeRaw derives both
    // from the same record.deleted in one statement.
    expect(await db.countUniverses()).toBe(0);

    await db.writeRaw('live', U('live'));
    await db.writeRaw('dead', U('dead', { deleted: true, deletedAt: '2026-02-02T00:00:00.000Z' }));
    await db.writeRaw('ghost', U('ghost', { ephemeral: true }));

    // listIds() sees all three; the tombstone must NOT be counted (the exact bug a
    // naive listIds().length would introduce). Ephemeral universes DO count —
    // listUniverses filters only on `deleted`, so filtering them here would undercount.
    expect((await db.listIds())).toHaveLength(3);
    expect(await db.countUniverses()).toBe(2);
    // Agrees with the same filter the service applies to the JSONB flag on read.
    expect(await db.countUniverses())
      .toBe((await db.listRaw()).filter((r) => r?.deleted !== true).length);

    expect(await db.countUniverses({ includeDeleted: true })).toBe(3);
    expect(await db.countUniverses({ includeDeleted: true })).toBe((await db.listRaw()).length);

    // Un-deleting through the normal write path rewrites both the column and the
    // JSONB flag, so the count follows.
    await db.writeRaw('dead', U('dead'));
    expect(await db.countUniverses()).toBe(3);
  });

  it('tolerates a malformed timestamp without throwing (falls back)', async () => {
    await db.writeRaw('u-bad', U('u-bad', { updatedAt: 'not-a-date', createdAt: 'nope' }));
    const back = await db.readRaw('u-bad');
    expect(back.id).toBe('u-bad'); // data stored verbatim
    const col = (await query(`SELECT created_at, updated_at FROM universes WHERE id = 'u-bad'`)).rows[0];
    expect(col.created_at).toBeInstanceOf(Date); // fell back to NOW(), not null/throw
    expect(col.updated_at).toBeInstanceOf(Date);
  });

  it('deleteRaw removes the row (idempotent)', async () => {
    await db.writeRaw('u-1', U('u-1'));
    await db.deleteRaw('u-1');
    expect(await db.readRaw('u-1')).toBeNull();
    await db.deleteRaw('u-1'); // no throw on missing
  });

  it('appendRun stores runs and caps the global log at 200', async () => {
    for (let i = 0; i < 205; i += 1) {
      await db.appendRun({ id: `run-${String(i).padStart(3, '0')}`, universeId: 'u-1', jobIds: [], promptCount: i, createdAt: `2026-01-01T00:00:${String(i % 60).padStart(2, '0')}.${String(i).padStart(3, '0')}Z` });
    }
    const count = (await query(`SELECT COUNT(*)::int AS n FROM universe_runs`)).rows[0].n;
    expect(count).toBe(200);
  });

  it('removeRunsForUniverses drops only the named universes runs', async () => {
    await db.appendRun({ id: 'r-a', universeId: 'u-A', jobIds: [], promptCount: 1, createdAt: '2026-01-01T00:00:00.000Z' });
    await db.appendRun({ id: 'r-b', universeId: 'u-B', jobIds: [], promptCount: 1, createdAt: '2026-01-01T00:00:01.000Z' });
    await db.removeRunsForUniverses(['u-A']);
    const remaining = await db.loadRuns();
    expect(remaining.map((r) => r.universeId)).toEqual(['u-B']);
  });
});