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
/**
* Unit tests for the catalog DB layer's media-attachment functions:
* - attachMedia upserts a typed (ingredient_id, media_key, kind) row and
* revives a soft-deleted row on conflict;
* - detachMedia soft-deletes (tombstone) rather than hard-DELETE;
* - setPortraitMedia demotes any other live portrait, then attaches;
* - getMissingMediaForIngredient reports only keys that don't resolve against
* the local media library (the metadata-missing integrity surface);
* - upsertMediaFromPeer distinguishes "tombstone fields present" from absent;
* - generation metadata round-trips and stays intact when an older peer omits it.
*
* Postgres is mocked — we capture the SQL/params and assert on them, so the
* suite runs without a live database. `resolveImageInputPath` is mocked so the
* integrity helper's library-resolution branch is deterministic.
*/
import { describe, it, expect, vi, beforeEach } from 'vitest';
const calls = [];
vi.mock('../lib/db.js', () => ({
query: vi.fn(async (sql, params) => {
if (sql.includes('catalog_identity_operations') || sql.includes('set_config(')) return { rows: [] };
calls.push({ sql: sql.replace(/\s+/g, ' ').trim(), params });
// Echo a media row back so rowToMedia works for the RETURNING * paths.
return {
rows: [{
ingredient_id: params?.[0] ?? 'i1',
media_key: params?.[1] ?? 'k.png',
kind: params?.[2] ?? 'portrait',
role: null,
caption: null,
created_at: new Date('2026-01-01T00:00:00Z'),
deleted: false,
deleted_at: null,
sync_sequence: 1,
}],
};
}),
withTransaction: vi.fn(),
pgvectorToArray: vi.fn(),
arrayToPgvector: vi.fn(),
}));
// resolveImageInputPath: 'present.png' resolves; everything else is missing.
vi.mock('../lib/fileUtils.js', async (importOriginal) => {
const actual = await importOriginal();
return {
...actual,
resolveImageInputPath: vi.fn((key) => (key === 'present.png' ? `/data/images/${key}` : null)),
};
});
// instanceIdentity.getInstanceId is pulled in transitively by catalogDB — stub it.
vi.mock('./instanceIdentity.js', () => ({ getInstanceId: vi.fn(async () => 'inst-1') }));
const catalogDB = await import('./catalogDB.js');
const db = await import('../lib/db.js');
beforeEach(() => {
calls.length = 0;
vi.clearAllMocks();
db.withTransaction.mockImplementation((fn) => fn({ query: db.query }));
});
describe('attachMedia', () => {
it('upserts the tuple and revives a soft-deleted row on conflict', async () => {
await catalogDB.attachMedia('i1', 'hero.png', 'portrait', { role: 'hero', caption: 'cap' });
const { sql, params } = calls.at(-1);
expect(sql).toMatch(/INSERT INTO catalog_ingredient_media/i);
expect(sql).toMatch(/ON CONFLICT \(ingredient_id, media_key, kind\) DO UPDATE/i);
expect(sql).toMatch(/deleted = false, deleted_at = NULL/i);
expect(params).toEqual(['i1', 'hero.png', 'portrait', 'hero', 'cap']);
});
it('defaults role/caption to null when omitted', async () => {
await catalogDB.attachMedia('i1', 'hero.png', 'reference');
expect(calls[0].params).toEqual(['i1', 'hero.png', 'reference', null, null]);
});
it('stores generation provenance alongside the attachment', async () => {
const metadata = { format: 'a1111', prompt: 'a paper boat', steps: 24, seed: 42 };
await catalogDB.attachMedia('i1', 'render.png', 'reference', { metadata });
expect(calls[0].sql).toMatch(/metadata\)/i);
expect(calls[0].params).toEqual(['i1', 'render.png', 'reference', null, null, JSON.stringify(metadata)]);
});
});
describe('detachMedia', () => {
it('soft-deletes (tombstone) — never a hard DELETE', async () => {
await catalogDB.detachMedia('i1', 'hero.png', 'portrait');
const { sql, params } = calls[0];
expect(sql).toMatch(/UPDATE catalog_ingredient_media SET deleted = true/i);
expect(sql).not.toMatch(/DELETE FROM/i);
expect(sql).toMatch(/AND deleted = false/i); // re-detach stays a no-op
expect(params).toEqual(['i1', 'hero.png', 'portrait']);
});
});
describe('setPortraitMedia', () => {
it('demotes other live portraits then attaches the new one', async () => {
await catalogDB.setPortraitMedia('i1', 'new.png', { caption: 'now' });
expect(calls.shift().sql).toMatch(/SELECT id FROM catalog_ingredients WHERE id = catalog_resolve_ingredient_id\(\$1\) FOR UPDATE/);
// Demote only after locking the owning ingredient.
expect(calls[0].sql).toMatch(/UPDATE catalog_ingredient_media SET deleted = true/i);
expect(calls[0].sql).toMatch(/kind = 'portrait'/i);
expect(calls[0].sql).toMatch(/media_key <> \$2/);
expect(calls[0].params).toEqual(['i1', 'new.png']);
// Second statement: attach as portrait.
expect(calls[1].sql).toMatch(/INSERT INTO catalog_ingredient_media/i);
expect(calls[1].params).toEqual(['i1', 'new.png', 'portrait', null, 'now']);
});
});
describe('getMissingMediaForIngredient', () => {
it('returns only the IMAGE keys that do not resolve against the local library', async () => {
// listMediaForIngredient runs the SELECT; override the mock to return a mix.
const db = await import('../lib/db.js');
db.query.mockResolvedValueOnce({
rows: [
{ ingredient_id: 'i1', media_key: 'present.png', kind: 'portrait', role: null, caption: null, created_at: new Date(), deleted: false, deleted_at: null, sync_sequence: 1 },
{ ingredient_id: 'i1', media_key: 'gone.png', kind: 'reference', role: null, caption: null, created_at: new Date(), deleted: false, deleted_at: null, sync_sequence: 2 },
// A non-image kind never has a library resolver yet — it must NOT be
// reported missing just because resolveImageInputPath returns null.
{ ingredient_id: 'i1', media_key: 'memo.wav', kind: 'audio', role: null, caption: null, created_at: new Date(), deleted: false, deleted_at: null, sync_sequence: 3 },
],
});
const missing = await catalogDB.getMissingMediaForIngredient('i1');
expect(missing).toEqual([{ mediaKey: 'gone.png', kind: 'reference' }]);
});
});
describe('upsertMediaFromPeer', () => {
it('uses the tombstone-aware INSERT when deleted/deletedAt are present', async () => {
await catalogDB.upsertMediaFromPeer({
ingredientId: 'i1', mediaKey: 'a.png', kind: 'portrait',
role: 'r', caption: 'c', createdAt: 't', deleted: true, deletedAt: 't2',
});
const { sql, params } = calls[0];
expect(sql).toMatch(/deleted, deleted_at, updated_at/i);
expect(sql).toMatch(/SET role = EXCLUDED.role, caption = EXCLUDED.caption, deleted = EXCLUDED.deleted/i);
expect(sql).toMatch(/WHERE EXCLUDED\.updated_at > catalog_ingredient_media\.updated_at/i);
// #8347: the 9th param is the revival-guard change-clock — falls back to
// deletedAt when the peer omits updatedAt.
expect(params).toEqual(['i1', 'a.png', 'portrait', 'r', 'c', 't', true, 't2', 't2']);
});
it('includes generation provenance when a peer sends the additive field', async () => {
const metadata = { prompt: 'a lighthouse', negativePrompt: 'blur', seed: 9 };
await catalogDB.upsertMediaFromPeer({
ingredientId: 'i1', mediaKey: 'a.png', kind: 'portrait',
role: 'r', caption: 'c', createdAt: 't', deleted: false, deletedAt: null, metadata,
});
const { sql, params } = calls[0];
expect(sql).toMatch(/metadata\)/i);
expect(sql).toMatch(/metadata = EXCLUDED\.metadata/i);
// deletedAt is null here, so the change-clock falls back to createdAt.
expect(params).toEqual(['i1', 'a.png', 'portrait', 'r', 'c', 't', false, null, 't', JSON.stringify(metadata)]);
});
it('binds the Casting library JSONB as a $n parameter, not an integer literal', async () => {
const library = { guide: true };
await catalogDB.upsertMediaFromPeer({
ingredientId: 'i1', mediaKey: 'a.png', kind: 'reference',
createdAt: 't', deleted: false, deletedAt: null, library,
});
const { sql, params } = calls[0];
expect(sql.replace(/\s+/g, ' ')).toMatch(/\$9, \$10\)/);
expect(params).toHaveLength(10);
expect(JSON.parse(params[9])).toMatchObject(library);
calls.length = 0;
await catalogDB.upsertMediaFromPeer({
ingredientId: 'i1', mediaKey: 'b.png', kind: 'reference', createdAt: 't', library,
});
expect(calls[0].sql.replace(/\s+/g, ' ')).toMatch(/\$6, \$7\)/);
expect(calls[0].params).toHaveLength(7);
});
it('uses the tombstone-less INSERT (preserve local state) when the keys are absent', async () => {
await catalogDB.upsertMediaFromPeer({
ingredientId: 'i1', mediaKey: 'a.png', kind: 'portrait', createdAt: 't',
});
const { sql, params } = calls[0];
expect(sql).not.toMatch(/deleted = EXCLUDED.deleted/i);
expect(sql).toMatch(/SET role = EXCLUDED.role, caption = EXCLUDED.caption/i);
expect(params).toEqual(['i1', 'a.png', 'portrait', null, null, 't']);
});
it('does not erase provenance when a peer has only an empty metadata object', async () => {
await catalogDB.upsertMediaFromPeer({
ingredientId: 'i1', mediaKey: 'a.png', kind: 'portrait', createdAt: 't', metadata: {},
});
const { sql, params } = calls[0];
expect(sql).not.toMatch(/metadata = EXCLUDED\.metadata/i);
expect(params).toEqual(['i1', 'a.png', 'portrait', null, null, 't']);
});
});
describe('listIngredients card thumbnail', () => {
const ingredientRow = (overrides = {}) => ({
id: 'cat-chr-1', type: 'character', name: 'Echo', payload: {}, tags: [],
embedding_model: null, origin_instance_id: null,
created_at: new Date('2026-01-01T00:00:00Z'), updated_at: new Date('2026-01-01T00:00:00Z'),
deleted: false, deleted_at: null, sync_sequence: 1,
...overrides,
});
// The DB suites pin the decoration SQL; this pins the id join that attaches
// the one page-level decoration query back onto the page rows (#12989).
it('decorates the returned page by id, defaulting to a null thumbnail and empty usage', async () => {
const db = await import('../lib/db.js');
db.query
.mockResolvedValueOnce({ rows: [ingredientRow({ id: 'cat-chr-a' }), ingredientRow({ id: 'cat-chr-b' })] })
.mockResolvedValueOnce({ rows: [
{ id: 'cat-chr-b', thumbnail_key: null, ref_usage: null },
{ id: 'cat-chr-a', thumbnail_key: 'hero.png', ref_usage: { universe: 1, 'creative-director': 2 } },
] });
const { items } = await catalogDB.listIngredients({ limit: 10 });
expect(db.query.mock.calls.at(-1)[1]).toEqual([['cat-chr-a', 'cat-chr-b']]);
expect(items.map(({ id, thumbnailKey, usage }) => ({ id, thumbnailKey, usage }))).toEqual([
{ id: 'cat-chr-a', thumbnailKey: 'hero.png', usage: { universe: 1, 'creative-director': 2 } },
{ id: 'cat-chr-b', thumbnailKey: null, usage: {} },
]);
});
it('skips the decoration query for an empty page and the embedding admin path', async () => {
const db = await import('../lib/db.js');
db.query.mockResolvedValueOnce({ rows: [] });
const before = db.query.mock.calls.length;
expect((await catalogDB.listIngredients({ limit: 10 })).items).toEqual([]);
db.query.mockResolvedValueOnce({ rows: [ingredientRow()] });
const { items } = await catalogDB.listIngredients({ limit: 10, includeEmbedding: true });
expect(db.query.mock.calls.length - before).toBe(2);
expect(items[0]).toMatchObject({ thumbnailKey: null, usage: {} });
});
});
describe('getMaxSequences includes the media cursor', () => {
it('reads the catalog_ingredient_media feed stream AS media', async () => {
const db = await import('../lib/db.js');
db.query.mockResolvedValueOnce({ rows: [{ media: '0' }] });
await catalogDB.getMaxSequences();
const sql = db.query.mock.calls.at(-1)[0].replace(/\s+/g, ' ');
expect(sql).toMatch(/stream = 'catalog_ingredient_media'\), 0\)::text AS media/i);
});
});