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

5.5 KB
Raw Blame
/**
 * Postgres-backed regression coverage for the boot-schema-DDL race (#5977):
 * two processes calling `ensureSchema()` at once used to interleave their
 * `DROP TRIGGER IF EXISTS` / `CREATE TRIGGER` pairs and throw "already
 * exists". `ensureSchemaImpl()` now wraps the whole DDL block in a
 * session-level `pg_advisory_lock`, held on a dedicated client for the
 * duration of the block and released in `finally`.
 *
 * These tests hold/probe the SAME advisory lock key from a second, raw `pg`
 * client to prove the lock is actually cross-session (not just the existing
 * in-process `ensureSchemaInFlight` dedup, which a second process doesn't
 * share) and that it is released on both the success and throw path.
 *
 * `*.db.test.js`-style suite (named `db.test.js`, matched by the
 * `**\/db.test.js` glob in `vitest.config.db.js`) → runs ONLY via
 * `npm run test:db` against `portos_test`, never the real `portos` DB (the
 * db.js runner guard + the skip below enforce this).
 */
import { afterAll, beforeAll, describe, expect, it, vi } from 'vitest';
import pg from 'pg';
import { requireDbOrSkip } from './dbTestGate.js';

const injectionState = vi.hoisted(() => ({ badUpgradeDdl: false }));

vi.mock('./db/schema/index.js', async () => {
  const actual = await vi.importActual('./db/schema/index.js');
  return {
    ...actual,
    buildUpgradeDdl: () =>
      injectionState.badUpgradeDdl
        ? ['SELECT * FROM this_table_does_not_exist_5977']
        : actual.buildUpgradeDdl(),
  };
});

const { checkHealth, ensureSchema, close, query, withTransaction, POOL_CONFIG, SCHEMA_DDL_ADVISORY_LOCK_KEY } = await import('./db.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 {
    dbReady = true;
  }
}
const runDb = requireDbOrSkip('lib/db.test', dbReady, skipReason);

// A second, independent connection to the same database — reuses db.js's own
// POOL_CONFIG so it can never drift from what the pool under test connects to.
function makeRawClient() {
  return new pg.Client(POOL_CONFIG);
}

// Always unlock-and-close the holder client, even when an assertion in
// `run` throws — an unreleased holder leaves the advisory lock held forever,
// which would hang every later ensureSchema() call (including afterAll's
// close()) rather than just failing this one test.
async function withLockHolder(run) {
  const holder = makeRawClient();
  await holder.connect();
  try {
    return await run(holder);
  } finally {
    await holder.query('SELECT pg_advisory_unlock($1)', [SCHEMA_DDL_ADVISORY_LOCK_KEY]).catch(() => {});
    await holder.end();
  }
}

afterAll(async () => {
  if (dbReady) await close();
});

describe.skipIf(!runDb)('ensureSchema() boot DDL advisory lock (#5977)', () => {
  beforeAll(async () => {
    // Baseline: schema already applied once so later assertions aren't
    // measuring first-install DDL cost.
    await ensureSchema();
  });

  it('blocks behind a lock held by another session, then completes once it frees', async () => {
    let resolved = false;
    let schemaPromise;

    await withLockHolder(async (holder) => {
      await holder.query('SELECT pg_advisory_lock($1)', [SCHEMA_DDL_ADVISORY_LOCK_KEY]);

      schemaPromise = ensureSchema({ force: true }).then(() => { resolved = true; });

      await new Promise((resolve) => setTimeout(resolve, 300));
      expect(resolved).toBe(false);
    });

    await schemaPromise;
    expect(resolved).toBe(true);
  });

  it('releases the lock (and rejects) when the DDL block throws', async () => {
    injectionState.badUpgradeDdl = true;
    await expect(ensureSchema({ force: true })).rejects.toThrow();
    injectionState.badUpgradeDdl = false;

    await withLockHolder(async (holder) => {
      const { rows } = await holder.query('SELECT pg_try_advisory_lock($1) AS locked', [SCHEMA_DDL_ADVISORY_LOCK_KEY]);
      expect(rows[0].locked).toBe(true);
    });

    // A failed forced repair clears readiness, so a later valid call retries.
    await expect(ensureSchema()).resolves.toBeUndefined();
  });
});

// PostgreSQL refuses a lone surrogate in jsonb and NUL in jsonb/text; the pool
// and the withTransaction client repair bound parameters in one place (#12815).
describe.skipIf(!runDb)('bound parameter repair', () => {
  const split = '😀'.slice(0, 1);
  const runners = {
    'query()': (sql, params) => query(sql, params),
    'withTransaction client': (sql, params) => withTransaction((client) => client.query(sql, params)),
  };

  describe.each(Object.entries(runners))('%s', (_name, run) => {
    it('repairs a string parameter and leaves the characters \\^@ alone', async () => {
      const { rows: [row] } = await run('SELECT $1::text AS bad, $2::text AS literal', [`x${split}y\0z`, 'a\\^@b']);
      expect(row.bad).toBe('x\uFFFDy\uFFFDz');
      expect(row.literal).toBe('a\\^@b');
    });

    it('repairs every string in an array parameter', async () => {
      const { rows: [row] } = await run('SELECT $1::text[] AS items', [['ok', `a${split}`, 'b\0']]);
      expect(row.items).toEqual(['ok', 'a\uFFFD', 'b\uFFFD']);
    });

    it('serializes a plain-object parameter as repaired jsonb, keys included', async () => {
      const { rows: [row] } = await run('SELECT $1::jsonb AS doc', [{ [`k${split}`]: [`v${split}`, 'n\0'], nested: { ok: 1 } }]);
      expect(row.doc).toEqual({ 'k\uFFFD': ['v\uFFFD', 'n\uFFFD'], nested: { ok: 1 } });
    });
  });
});