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.6 KB
Raw Blame
/**
 * Postgres-backed round-trip for the mood board store (issue #911).
 *
 * Like projectsDB.test.js / mediaAssetIndex/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 create/list/get/update + the item add/update/remove ops + the #1564
 * federation paths (soft-delete tombstone, LWW merge, prune, restore), cleaning
 * up its own rows after (only rows it created — no global table mutation).
 */

import { describe, it, expect, afterAll, beforeAll, beforeEach, vi } from 'vitest';
import { rmSync } from 'fs';
import { join } from 'path';
import { checkHealth, ensureSchema, query, close, withTransaction } from '../../lib/db.js';
import { requireDbOrSkip } from '../../lib/dbTestGate.js';
import { getSyncBaseHash, __resetBaseHashCacheForTests } from '../../lib/conflictJournal.js';

const testState = vi.hoisted(() => ({ dataRoot: null, writeCounter: { baseHash: 0 } }));

vi.mock('../../lib/fileUtils.js', async (importOriginal) => {
  const actual = await importOriginal();
  const { mkdtempSync } = await import('node:fs');
  const { tmpdir } = await import('node:os');
  testState.dataRoot ??= mkdtempSync(join(tmpdir(), 'mood-board-db-test-'));
  return {
    ...actual,
    PATHS: { ...actual.PATHS, data: testState.dataRoot },
    atomicWrite: async (path, data) => {
      if (typeof path === 'string' && path.endsWith('sync_base_hashes.json')) testState.writeCounter.baseHash += 1;
      return actual.atomicWrite(path, data);
    },
  };
});

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 = 'mood_boards') AS ok`,
    ).catch(() => ({ rows: [{ ok: false }] }));
    if (probe.rows?.[0]?.ok) dbReady = true;
    else skipReason = 'mood_boards table not present';
  }
}

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

afterAll(() => rmSync(testState.dataRoot, { recursive: true, force: true }));

describe.skipIf(!runDb)('mood board DB round-trip', () => {
  let db;
  const created = [];
  beforeAll(async () => {
    db = await import('./db.js');
  });
  afterAll(async () => {
    for (const id of created) {
      await query(`DELETE FROM mood_boards WHERE id = $1`, [id]).catch(() => {});
    }
    // The pool is closed once, by the federation describe's afterAll (runs last).
  });

  it('lists live boards as id + name only', async () => {
    const board = await db.createBoard({ name: 'Named board' });
    created.push(board.id);
    const row = (await db.listBoardNames()).find((b) => b.id === board.id);
    expect(row).toEqual({ id: board.id, name: 'Named board' });
  });

  it('creates, lists, and gets a board (lossless data)', async () => {
    const board = await db.createBoard({ name: 'Test board', description: 'd' });
    created.push(board.id);
    expect(board.id).toMatch(/^mb-/);
    expect(board.items).toEqual([]);

    const fetched = await db.getBoard(board.id);
    expect(fetched.name).toBe('Test board');
    expect(fetched.description).toBe('d');

    const list = await db.listBoards();
    expect(list.some((b) => b.id === board.id)).toBe(true);
  });

  it('updates board metadata', async () => {
    const board = await db.createBoard({ name: 'Rename me' });
    created.push(board.id);
    const updated = await db.updateBoard(board.id, { name: 'Renamed' });
    expect(updated.name).toBe('Renamed');
    expect((await db.getBoard(board.id)).name).toBe('Renamed');
  });

  it('adds, updates, and removes items', async () => {
    const board = await db.createBoard({ name: 'Items' });
    created.push(board.id);

    const item = await db.addBoardItem(board.id, { type: 'image', imageUrl: 'https://x/y.png' });
    expect(item.id).toMatch(/^mbi-/);
    expect((await db.getBoard(board.id)).items).toHaveLength(1);

    const patched = await db.updateBoardItem(board.id, item.id, { caption: 'cap' });
    expect(patched.caption).toBe('cap');

    const afterRemove = await db.removeBoardItem(board.id, item.id);
    expect(afterRemove.items).toHaveLength(0);
  });

  it('serializes concurrent pins of the same saved upload into one board item', async () => {
    const board = await db.createBoard({ name: 'Upload retry fixture' });
    created.push(board.id);
    const input = { type: 'video', mediaKey: 'video:upload-fixture.mp4', prompt: 'Clouds' };
    const [first, repeated] = await Promise.all([db.addBoardItem(board.id, input), db.addBoardItem(board.id, input)]);
    expect(repeated.id).toBe(first.id);
    expect((await db.getBoard(board.id)).items).toEqual([first]);
  });

  it('waits for an already-saving pin before taking the retirement ownership snapshot', async () => {
    const board = await db.createBoard({ name: 'Retirement overlap fixture' });
    created.push(board.id);
    const saved = { ...board, items: [{ type: 'video', mediaKey: 'video:retained-final.mp4' }] };
    const ready = Promise.withResolvers();
    const commit = Promise.withResolvers();
    const writer = withTransaction(async client => {
      await client.query('UPDATE mood_boards SET data = $2::jsonb WHERE id = $1', [board.id, JSON.stringify(saved)]);
      ready.resolve();
      await commit.promise;
    });
    writer.catch(ready.reject);
    await ready.promise;
    const snapshot = db.withBoardMediaReferences(boards => boards.find(item => item.id === board.id));
    try {
      await expect.poll(async () => (await query("SELECT count(*)::int AS count FROM pg_locks WHERE relation = 'mood_boards'::regclass AND mode = 'ShareLock' AND NOT granted")).rows[0].count).toBe(1);
    } finally {
      commit.resolve();
      await writer;
    }
    expect(await snapshot).toEqual(saved);
  });

  it('holds local pins and peer merges until retirement settles and releases them after failure', async () => {
    const board = await db.createBoard({ name: 'Retirement fence fixture' });
    created.push(board.id);
    const remote = { ...board, id: `${board.id}-peer`, items: [{ type: 'video', mediaKey: 'video:peer-final.mp4' }] };
    created.push(remote.id);
    const ready = Promise.withResolvers();
    const settle = Promise.withResolvers();
    const retirement = db.withBoardMediaReferences(async () => {
      ready.resolve();
      await settle.promise;
      throw new Error('Retirement failed');
    });
    retirement.catch(ready.reject);
    const failure = expect(retirement).rejects.toThrow('Retirement failed');
    await ready.promise;
    const pin = db.addBoardItem(board.id, { type: 'video', mediaKey: 'video:local-final.mp4' });
    const merge = db.mergeBoardsFromSync([remote]);
    try {
      await expect.poll(async () => (await query("SELECT count(*)::int AS count FROM pg_locks WHERE relation = 'mood_boards'::regclass AND mode = 'RowExclusiveLock' AND NOT granted")).rows[0].count).toBe(2);
      expect((await db.getBoard(board.id)).items).toEqual([]);
      expect(await db.getBoard(remote.id)).toBeNull();
    } finally {
      settle.resolve();
      await Promise.all([failure, pin, merge]);
    }
    expect((await db.getBoard(board.id)).items[0].mediaKey).toBe('video:local-final.mp4');
    expect((await db.getBoard(remote.id)).items).toEqual(remote.items);
  });

  it('removeBoardItem is a no-op for an unknown item id', async () => {
    const board = await db.createBoard({ name: 'Noop' });
    created.push(board.id);
    const result = await db.removeBoardItem(board.id, 'mbi-nope');
    expect(result.items).toEqual([]);
  });

  it('throws NOT_FOUND for a missing board on get/update/delete', async () => {
    expect(await db.getBoard('mb-missing')).toBeNull();
    await expect(db.updateBoard('mb-missing', { name: 'x' })).rejects.toThrow(/not found/i);
    await expect(db.deleteBoard('mb-missing')).rejects.toThrow(/not found/i);
  });

  it('soft-deletes a board (tombstone), hiding it from live reads but not includeDeleted', async () => {
    const board = await db.createBoard({ name: 'Delete me' });
    created.push(board.id); // soft-delete leaves the row — afterAll hard-deletes it
    const result = await db.deleteBoard(board.id);
    expect(result.ok).toBe(true);
    // Live reads hide the tombstone…
    expect(await db.getBoard(board.id)).toBeNull();
    expect((await db.listBoards()).some((b) => b.id === board.id)).toBe(false);
    expect(await db.listBoardIds()).not.toContain(board.id);
    expect((await db.listBoardNames()).some((b) => b.id === board.id)).toBe(false);
    // …but includeDeleted surfaces it with the tombstone trio set.
    const tomb = await db.getBoard(board.id, { includeDeleted: true });
    expect(tomb).toMatchObject({ deleted: true });
    expect(typeof tomb.deletedAt).toBe('string');
    expect(await db.listBoardIds({ includeDeleted: true })).toContain(board.id);
    // Deleting again 404s (idempotent tombstone), and user mutators refuse it.
    await expect(db.deleteBoard(board.id)).rejects.toThrow(/not found/i);
    await expect(db.updateBoard(board.id, { name: 'zombie' })).rejects.toThrow(/not found/i);
    await expect(db.addBoardItem(board.id, { type: 'text', text: 'z' })).rejects.toThrow(/not found/i);
  });
});

describe.skipIf(!runDb)('mood board federation (#1564)', () => {
  let db;
  const created = [];
  const remote = (id, over = {}) => ({
    id, name: `R-${id}`, description: '', items: [],
    createdAt: '2026-06-23T00:00:00.000Z', updatedAt: '2026-06-23T00:00:00.000Z',
    deleted: false, deletedAt: null, ...over,
  });
  beforeAll(async () => { db = await import('./db.js'); });
  beforeEach(() => {
    rmSync(join(testState.dataRoot, 'sharing'), { recursive: true, force: true });
    rmSync(join(testState.dataRoot, 'conflict-journal'), { recursive: true, force: true });
    __resetBaseHashCacheForTests();
    testState.writeCounter.baseHash = 0;
  });
  afterAll(async () => {
    for (const id of created) await query(`DELETE FROM mood_boards WHERE id = $1`, [id]).catch(() => {});
    await close();
  });

  it('inserts a remote board on first merge', async () => {
    const id = `mb-fed-insert-${Date.now()}`;
    created.push(id);
    const res = await db.mergeBoardsFromSync([remote(id, { name: 'Inserted' })]);
    expect(res).toEqual({ applied: true, count: 1 });
    expect((await db.getBoard(id)).name).toBe('Inserted');
  });

  it('newer remote updatedAt wins; older loses', async () => {
    const id = `mb-fed-lww-${Date.now()}`;
    created.push(id);
    await db.mergeBoardsFromSync([remote(id, { name: 'v1', updatedAt: '2026-06-23T00:00:00.000Z' })]);
    // Older remote → no change.
    const older = await db.mergeBoardsFromSync([remote(id, { name: 'stale', updatedAt: '2026-06-22T00:00:00.000Z' })]);
    expect(older.applied).toBe(false);
    expect((await db.getBoard(id)).name).toBe('v1');
    // Newer remote → wins.
    await db.mergeBoardsFromSync([remote(id, { name: 'v2', updatedAt: '2026-06-24T00:00:00.000Z' })]);
    expect((await db.getBoard(id)).name).toBe('v2');
  });

  it('applies a remote tombstone (delete federates)', async () => {
    const id = `mb-fed-tomb-${Date.now()}`;
    created.push(id);
    await db.mergeBoardsFromSync([remote(id, { updatedAt: '2026-06-23T00:00:00.000Z' })]);
    await db.mergeBoardsFromSync([remote(id, { updatedAt: '2026-06-24T00:00:00.000Z', deleted: true, deletedAt: '2026-06-24T00:00:00.000Z' })]);
    expect(await db.getBoard(id)).toBeNull();
    expect(await db.getBoard(id, { includeDeleted: true })).toMatchObject({ deleted: true });
  });

  it('restoreBoard brings back name/description/items wholesale', async () => {
    const board = await db.createBoard({ name: 'Orig' });
    created.push(board.id);
    const items = [{ id: 'mbi-r1', type: 'text', text: 'restored', mediaKey: null, imageUrl: null, caption: null, source: null, createdAt: 't0' }];
    const restored = await db.restoreBoard(board.id, { name: 'Restored', description: 'd', items });
    expect(restored.name).toBe('Restored');
    expect(restored.items).toEqual(items);
    expect((await db.getBoard(board.id)).description).toBe('d');
  });

  it('pruneTombstonedBoards batches base-hash eviction for multiple tombstones', async () => {
    await query(`DELETE FROM mood_boards WHERE id LIKE $1`, ['mb-fed-prune-%']);
    const oldId = `mb-fed-prune-old-${Date.now()}`;
    const oldIdTwo = `mb-fed-prune-old-two-${Date.now()}`;
    const newId = `mb-fed-prune-new-${Date.now()}`;
    created.push(oldId, oldIdTwo, newId);
    await db.mergeBoardsFromSync([
      remote(oldId, { updatedAt: '2000-01-02T00:00:00.000Z', deleted: true, deletedAt: '2000-01-01T00:00:00.000Z' }),
      remote(oldIdTwo, { updatedAt: '2000-01-03T00:00:00.000Z', deleted: true, deletedAt: '2000-01-02T00:00:00.000Z' }),
      remote(newId, { updatedAt: '2099-01-02T00:00:00.000Z', deleted: true, deletedAt: '2099-01-01T00:00:00.000Z' }),
    ]);
    expect(await getSyncBaseHash('moodBoard', oldId)).not.toBeNull();
    expect(await getSyncBaseHash('moodBoard', oldIdTwo)).not.toBeNull();

    testState.writeCounter.baseHash = 0;
    const res = await db.pruneTombstonedBoards(Date.parse('2025-01-01T00:00:00.000Z'));
    expect(res).toEqual({ pruned: 2 });
    expect(testState.writeCounter.baseHash).toBe(1);
    __resetBaseHashCacheForTests();
    expect(await getSyncBaseHash('moodBoard', oldId)).toBeNull();
    expect(await getSyncBaseHash('moodBoard', oldIdTwo)).toBeNull();
    expect(await db.getBoard(newId, { includeDeleted: true })).not.toBeNull();
    expect(await db.getBoard(oldId, { includeDeleted: true })).toBeNull();
  });
});