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

14.9 KB
Raw Blame
/**
 * Postgres-backed tests for the Catalog facet + album filters (#1762):
 *   - listIngredients({ refKind, refId })  — universe/series membership
 *   - listIngredients({ unlinked: true })  — the "Raw" album (no u/s ref)
 *   - listIngredients({ orphaned: true })  — refs whose target is gone
 *   - getCatalogFacets()                    — type/universe/series/tag facets +
 *                                             unlinked / orphaned bucket counts
 *
 * `*.db.test.js` → runs ONLY via `npm run test:db` against `portos_test`, never
 * the real `portos` DB (the db.js runner guard + the suite skip below enforce
 * this). The DB is shared across worktrees, so assertions are RELATIVE
 * (membership + `>=`) and every row created here is torn down in afterAll.
 */

import { describe, it, expect, afterAll, beforeAll } from 'vitest';
import { checkHealth, ensureSchema, close, query } from '../lib/db.js';
import { requireDbOrSkip } from '../lib/dbTestGate.js';
import * as catalogDB from './catalogDB.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 recheck = await checkHealth().catch(() => ({ hasCatalogSchema: false }));
    if (recheck.hasCatalogSchema) dbReady = true;
    else skipReason = 'catalog schema not present';
  }
}
const runDb = requireDbOrSkip('services/catalogDB.facets.db.test', dbReady, skipReason);

const nonce = `f${Date.now()}`;
const UNI_ID = `uni-${nonce}`;
const SER_ID = `ser-${nonce}`;
const DEAD_UNI_ID = `dead-uni-${nonce}`; // never inserted → orphaned target
const TAG = `tag-${nonce}`;
const createdIngredientIds = new Set();

beforeAll(async () => {
  if (!dbReady) return;
  await query('INSERT INTO universes (id, name) VALUES ($1, $2)', [UNI_ID, `Universe ${nonce}`]);
  await query('INSERT INTO pipeline_series (id, name, universe_id) VALUES ($1, $2, $3)', [SER_ID, `Series ${nonce}`, UNI_ID]);
});

afterAll(async () => {
  if (!dbReady) return;
  for (const id of createdIngredientIds) {
    await catalogDB.deleteIngredient(id, { hard: true }).catch(() => {});
  }
  await query('DELETE FROM catalog_ingredient_refs WHERE ref_id = ANY($1)', [[UNI_ID, SER_ID, DEAD_UNI_ID]]).catch(() => {});
  await query('DELETE FROM pipeline_series WHERE id = $1', [SER_ID]).catch(() => {});
  await query('DELETE FROM universes WHERE id = $1', [UNI_ID]).catch(() => {});
  await close();
});

describe.skipIf(!runDb)('catalogDB facets + album filters (#1762)', () => {
  it('lists ingredients by universe ref (membership), composing with type', async () => {
    const linked = await catalogDB.createIngredient({ type: 'character', name: `Linked ${nonce}`, tags: [TAG] });
    const other = await catalogDB.createIngredient({ type: 'place', name: `Other ${nonce}` });
    createdIngredientIds.add(linked.id);
    createdIngredientIds.add(other.id);
    await catalogDB.linkIngredientToRef(linked.id, 'universe', UNI_ID, 'cast-character');

    const { items } = await catalogDB.listIngredients({ refKind: 'universe', refId: UNI_ID, limit: 200 });
    const ids = items.map((i) => i.id);
    expect(ids).toContain(linked.id);
    expect(ids).not.toContain(other.id);

    // Composes with a type filter (still matches, since linked is a character).
    const typed = await catalogDB.listIngredients({ refKind: 'universe', refId: UNI_ID, type: 'character', limit: 200 });
    expect(typed.items.map((i) => i.id)).toContain(linked.id);
    // …and excludes when the type doesn't match.
    const typedMiss = await catalogDB.listIngredients({ refKind: 'universe', refId: UNI_ID, type: 'object', limit: 200 });
    expect(typedMiss.items.map((i) => i.id)).not.toContain(linked.id);
  });

  it('rolls a series-only ingredient up under its parent universe (decision #1)', async () => {
    const seriesOnly = await catalogDB.createIngredient({ type: 'character', name: `SeriesOnly ${nonce}` });
    createdIngredientIds.add(seriesOnly.id);
    // Linked ONLY to the series (which belongs to UNI) — no direct universe ref.
    await catalogDB.linkIngredientToRef(seriesOnly.id, 'series', SER_ID, 'cast-character');

    const { items } = await catalogDB.listIngredients({ refKind: 'universe', refId: UNI_ID, limit: 200 });
    expect(items.map((i) => i.id)).toContain(seriesOnly.id);

    // And the universe facet count reflects the rolled-up series member.
    const facets = await catalogDB.getCatalogFacets();
    const uni = facets.universes.find((u) => u.refId === UNI_ID);
    expect(uni.count).toBeGreaterThanOrEqual(1);
  });

  it('does not duplicate a row linked under multiple roles', async () => {
    const multi = await catalogDB.createIngredient({ type: 'character', name: `Multi ${nonce}` });
    createdIngredientIds.add(multi.id);
    await catalogDB.linkIngredientToRef(multi.id, 'universe', UNI_ID, 'cast-character');
    await catalogDB.linkIngredientToRef(multi.id, 'universe', UNI_ID, 'reference');

    const { items } = await catalogDB.listIngredients({ refKind: 'universe', refId: UNI_ID, limit: 200 });
    const occurrences = items.filter((i) => i.id === multi.id).length;
    expect(occurrences).toBe(1);
  });

  it('lists unlinked ("Raw") ingredients and excludes linked ones', async () => {
    const raw = await catalogDB.createIngredient({ type: 'idea', name: `Raw ${nonce}` });
    const linked = await catalogDB.createIngredient({ type: 'character', name: `RawLinked ${nonce}` });
    createdIngredientIds.add(raw.id);
    createdIngredientIds.add(linked.id);
    await catalogDB.linkIngredientToRef(linked.id, 'series', SER_ID, 'cast-character');

    const { items } = await catalogDB.listIngredients({ unlinked: true, limit: 1000 });
    const ids = items.map((i) => i.id);
    expect(ids).toContain(raw.id);
    expect(ids).not.toContain(linked.id);
  });

  it('lists orphaned ingredients (ref present, target gone) and excludes live-linked', async () => {
    const orphan = await catalogDB.createIngredient({ type: 'object', name: `Orphan ${nonce}` });
    const live = await catalogDB.createIngredient({ type: 'object', name: `Live ${nonce}` });
    createdIngredientIds.add(orphan.id);
    createdIngredientIds.add(live.id);
    // orphan → points at a universe id that has no row; live → the real universe.
    await catalogDB.linkIngredientToRef(orphan.id, 'universe', DEAD_UNI_ID, 'reference');
    await catalogDB.linkIngredientToRef(live.id, 'universe', UNI_ID, 'reference');

    const { items } = await catalogDB.listIngredients({ orphaned: true, limit: 1000 });
    const ids = items.map((i) => i.id);
    expect(ids).toContain(orphan.id);
    expect(ids).not.toContain(live.id);
    // An orphan is, by definition, NOT in the unlinked set (it has a ref).
    const raw = await catalogDB.listIngredients({ unlinked: true, limit: 1000 });
    expect(raw.items.map((i) => i.id)).not.toContain(orphan.id);
  });

  it('classifies creative-director refs as homing: live → linked, soft-deleted → orphaned (#1812)', async () => {
    const cdId = `cd-${nonce}`;
    await query('INSERT INTO creative_director_projects (id, data) VALUES ($1, $2::jsonb)', [cdId, '{}']);
    const cdLinked = await catalogDB.createIngredient({ type: 'object', name: `CDLinked ${nonce}` });
    createdIngredientIds.add(cdLinked.id);
    await catalogDB.linkIngredientToRef(cdLinked.id, 'creative-director', cdId, 'reference');

    // While the CD project is live, the ingredient is LINKED — not unlinked (it
    // has a homing ref) and not orphaned (the target resolves).
    let unlinked = await catalogDB.listIngredients({ unlinked: true, limit: 1000 });
    expect(unlinked.items.map((i) => i.id)).not.toContain(cdLinked.id);
    let orphaned = await catalogDB.listIngredients({ orphaned: true, limit: 1000 });
    expect(orphaned.items.map((i) => i.id)).not.toContain(cdLinked.id);

    // Soft-delete the CD project → the ref dangles → the ingredient is ORPHANED
    // (surfaces in the Orphaned album for re-linking), still not unlinked.
    await query('UPDATE creative_director_projects SET deleted = TRUE, deleted_at = NOW() WHERE id = $1', [cdId]);
    orphaned = await catalogDB.listIngredients({ orphaned: true, limit: 1000 });
    expect(orphaned.items.map((i) => i.id)).toContain(cdLinked.id);
    unlinked = await catalogDB.listIngredients({ unlinked: true, limit: 1000 });
    expect(unlinked.items.map((i) => i.id)).not.toContain(cdLinked.id);

    await query('DELETE FROM catalog_ingredient_refs WHERE ref_id = $1', [cdId]).catch(() => {});
    await query('DELETE FROM creative_director_projects WHERE id = $1', [cdId]).catch(() => {});
  });

  it('counts usage per kind from live targets only, so a deleted universe stops counting', async () => {
    const ing = await catalogDB.createIngredient({ type: 'character', name: `Usage ${nonce}` });
    createdIngredientIds.add(ing.id);
    await catalogDB.linkIngredientToRef(ing.id, 'universe', UNI_ID, 'cast-character');
    await catalogDB.linkIngredientToRef(ing.id, 'universe', UNI_ID, 'mentioned');
    await catalogDB.linkIngredientToRef(ing.id, 'universe', DEAD_UNI_ID, 'cast-character');
    await catalogDB.linkIngredientToRef(ing.id, 'series', SER_ID, 'cast');

    const { items } = await catalogDB.listIngredients({ ids: [ing.id], limit: 5 });
    expect(items[0].usage).toEqual({ universe: 1, series: 1 });
  });

  // Uniquely catches a newer reference sheet / look / plate becoming the card
  // cover over an older plain reference image.
  it('never picks a sheet, look or plate image as the card thumbnail', async () => {
    const ing = await catalogDB.createIngredient({ type: 'place', name: `Thumb ${nonce}` });
    createdIngredientIds.add(ing.id);
    await catalogDB.attachMedia(ing.id, `plain-${nonce}.png`, 'reference');
    await catalogDB.attachMedia(ing.id, `sheet-${nonce}.png`, 'reference', { role: 'sheet:environment' });
    await catalogDB.attachMedia(ing.id, `look-${nonce}.png`, 'reference', { role: 'look:night' });
    await catalogDB.attachMedia(ing.id, `plate-${nonce}.png`, 'reference', { role: 'plate' });
    const { items } = await catalogDB.listIngredients({ ids: [ing.id], limit: 5 });
    expect(items[0].thumbnailKey).toBe(`plain-${nonce}.png`);

    await catalogDB.detachMedia(ing.id, `plain-${nonce}.png`, 'reference');
    const after = await catalogDB.listIngredients({ ids: [ing.id], limit: 5 });
    expect(after.items[0].thumbnailKey).toBeNull();
  });

  it('getCatalogFacets aggregates type/universe/series/tag facets + bucket counts', async () => {
    const linked = await catalogDB.createIngredient({ type: 'character', name: `FacetLinked ${nonce}`, tags: [TAG] });
    const seriesLinked = await catalogDB.createIngredient({ type: 'character', name: `FacetSeries ${nonce}` });
    const raw = await catalogDB.createIngredient({ type: 'idea', name: `FacetRaw ${nonce}` });
    const orphan = await catalogDB.createIngredient({ type: 'object', name: `FacetOrphan ${nonce}` });
    [linked, seriesLinked, raw, orphan].forEach((i) => createdIngredientIds.add(i.id));
    await catalogDB.linkIngredientToRef(linked.id, 'universe', UNI_ID, 'cast-character');
    await catalogDB.linkIngredientToRef(seriesLinked.id, 'series', SER_ID, 'cast-character');
    await catalogDB.linkIngredientToRef(orphan.id, 'universe', DEAD_UNI_ID, 'reference');

    const facets = await catalogDB.getCatalogFacets();

    // Live universe + series appear with their resolved names + counts.
    const uni = facets.universes.find((u) => u.refId === UNI_ID);
    expect(uni).toBeTruthy();
    expect(uni.name).toBe(`Universe ${nonce}`);
    expect(uni.count).toBeGreaterThanOrEqual(1);

    const ser = facets.series.find((s) => s.refId === SER_ID);
    expect(ser).toBeTruthy();
    expect(ser.name).toBe(`Series ${nonce}`);
    expect(ser.universeId).toBe(UNI_ID);
    expect(ser.count).toBeGreaterThanOrEqual(1);

    // A dangling-target universe never appears in the live facet array.
    expect(facets.universes.find((u) => u.refId === DEAD_UNI_ID)).toBeUndefined();

    // Tag + type facets surface our nonce tag and the character type.
    expect(facets.tags.find((t) => t.tag === TAG)?.count).toBeGreaterThanOrEqual(1);
    expect(facets.types.find((t) => t.type === 'character')?.count).toBeGreaterThanOrEqual(2);

    // Bucket counts: our raw + orphan each push their bucket up by >=1.
    expect(facets.unlinkedCount).toBeGreaterThanOrEqual(1);
    expect(facets.orphanedCount).toBeGreaterThanOrEqual(1);
    expect(facets.total).toBeGreaterThanOrEqual(4);
  });
});

describe.skipIf(!runDb)('catalogDB.findIngredientsByName', () => {
  it('matches a live record of the type by trimmed, case-insensitive name or alias, oldest first', async () => {
    const make = async (type, name, payload = {}) => {
      const row = await catalogDB.createIngredient({ type, name, payload });
      createdIngredientIds.add(row.id);
      return row;
    };
    const first = await make('character', `  Juno Example ${nonce} `);
    const aliased = await make('character', `The Drummer ${nonce}`, { aliases: [`juno example ${nonce}`] });
    const otherType = await make('place', `Juno Example ${nonce}`);
    const gone = await make('character', `Juno Example ${nonce}`);
    await catalogDB.deleteIngredient(gone.id);

    const found = await catalogDB.findIngredientsByName('character', [`JUNO EXAMPLE ${nonce}`]);
    expect(found.map((r) => r.id)).toEqual([first.id, aliased.id]);
    expect(found.map((r) => r.id)).not.toContain(otherType.id);
    expect(await catalogDB.findIngredientsByName('character', ['  '])).toEqual([]);
  });

  it('reports which ingredients exist locally and which already carry a live or tombstoned ref to a record (#12466)', async () => {
    const make = async (name) => {
      const row = await catalogDB.createIngredient({ type: 'character', name: `${name} ${nonce}` });
      createdIngredientIds.add(row.id);
      return row;
    };
    const live = await make('Ownership live');
    const tombstoned = await make('Ownership tombstoned');
    const foreign = await make('Ownership foreign');
    const absentId = `absent-${nonce}`;
    const insertRef = (ingredientId, refId, deleted = false) => query(
      `INSERT INTO catalog_ingredient_refs (ingredient_id, ref_kind, ref_id, role, deleted, deleted_at)
       VALUES ($1, 'universe', $2, 'cast', $3, CASE WHEN $3 THEN NOW() END)`,
      [ingredientId, refId, deleted],
    );
    await insertRef(live.id, UNI_ID);
    await insertRef(tombstoned.id, UNI_ID, true);
    await insertRef(foreign.id, DEAD_UNI_ID);

    const { existing, linked } = await catalogDB.getLocalIngredientRefOwnership(
      [live.id, tombstoned.id, foreign.id, absentId], 'universe', UNI_ID,
    );
    expect([...existing].sort()).toEqual([live.id, tombstoned.id, foreign.id].sort());
    expect([...linked].sort()).toEqual([live.id, tombstoned.id].sort());
    expect((await catalogDB.getLocalIngredientRefOwnership([], 'universe', UNI_ID)).existing.size).toBe(0);
  });
});