ThothII/backend/test/catalog-table-introspector.test.ts

import { mkdtempSync, rmSync } from "node:fs";
import { tmpdir } from "node:os";
import { join } from "node:path";
import { afterEach, expect, test, vi } from "vitest";
import type {
  CatalogDatabaseClient,
  CatalogPostgresAccess,
} from "../src/catalog/postgres-access.js";
import { CATALOG_SECRET_IDS } from "../src/catalog/secrets.js";
import { ConcreteCatalogTableIntrospector } from "../src/catalog/table-introspector.js";
import type { WorkspaceDatabase } from "../src/catalog/types.js";
import { WorkspaceSecretStore } from "../src/workspaces/secret-store.js";

const roots: string[] = [];

afterEach(() => {
  vi.unstubAllGlobals();
  for (const root of roots.splice(0)) rmSync(root, { recursive: true, force: true });
});

function secretStore() {
  const root = mkdtempSync(join(tmpdir(), "catalog-table-introspection-secrets-"));
  const runtimeRoot = mkdtempSync(join(tmpdir(), "catalog-table-introspection-runtime-"));
  roots.push(root, runtimeRoot);
  return new WorkspaceSecretStore({ root, runtimeRoot, installationId: "test" });
}

function database(binding: WorkspaceDatabase["binding"]): WorkspaceDatabase {
  return {
    id: "11111111-1111-4111-8111-111111111111",
    workspaceId: "psd-clinical",
    engine: "postgres",
    databaseName: "warehouse",
    schema: "datawarehouse",
    version: 4,
    createdAt: "2026-08-27T08:00:00Z",
    updatedAt: "2026-08-27T09:00:00Z",
    connectionStatus: "reachable",
    binding,
  };
}

test("reads only ordinary and partitioned PostgreSQL tables from the configured schema", async () => {
  const query = vi.fn()
    .mockResolvedValueOnce({ rows: [{ present: true }] })
    .mockResolvedValueOnce({ rows: [
      { name: "visits", source_comment: null },
      { name: "patients", source_comment: "Clinical patients" },
    ] });
  const end = vi.fn(async () => undefined);
  const client: CatalogDatabaseClient = { query, end };
  const postgres: CatalogPostgresAccess = { connect: vi.fn(async () => client) };
  const introspector = new ConcreteCatalogTableIntrospector(postgres, secretStore());

  const tables = await introspector.scan(database({
    transport: "postgres_direct",
    host: "db.internal",
    port: 5432,
    username: "reader",
  }), new AbortController().signal);

  expect(tables).toEqual([
    { name: "patients", sourceComment: "Clinical patients" },
    { name: "visits", sourceComment: null },
  ]);
  expect(query.mock.calls[1][0]).toContain("c.relkind IN ('r', 'p')");
  expect(query.mock.calls[1][0]).not.toContain("'v'");
  expect(query.mock.calls[1][1]).toEqual(["datawarehouse"]);
  expect(end).toHaveBeenCalledOnce();
});

test("uses the typed REST table RPC and ignores non-table objects", async () => {
  const store = secretStore();
  store.put("psd-clinical", CATALOG_SECRET_IDS.apiKey, "rest-secret");
  const fetchMock = vi.fn(async () => new Response(JSON.stringify([
    { type: "VIEW", table: "patient_view", comment: "Not a table" },
    { type: "TABLE", table: "visits", comment: null },
    { type: "TABLE", table: "patients", comment: "Clinical patients" },
  ]), { status: 200, headers: { "content-type": "application/json" } }));
  vi.stubGlobal("fetch", fetchMock);
  const postgres: CatalogPostgresAccess = {
    connect: vi.fn(async () => { throw new Error("PostgreSQL wire access must not be used"); }),
  };
  const introspector = new ConcreteCatalogTableIntrospector(postgres, store);

  const tables = await introspector.scan(database({
    transport: "rest_api",
    baseUrl: "https://connector.internal/api/",
    restPath: "/health",
    restAuth: "x-api-key",
  }), new AbortController().signal);

  expect(tables).toEqual([
    { name: "patients", sourceComment: "Clinical patients" },
    { name: "visits", sourceComment: null },
  ]);
  expect(fetchMock).toHaveBeenCalledWith(
    "https://connector.internal/api/rpc/list_tables",
    expect.objectContaining({
      method: "POST",
      headers: expect.objectContaining({ "x-api-key": "rest-secret" }),
      body: JSON.stringify({ schema_name: "datawarehouse" }),
    }),
  );
});

test("fails closed when a REST table row violates the typed contract", async () => {
  const store = secretStore();
  const fetchMock = vi.fn(async () => new Response(JSON.stringify([
    { type: "TABLE", table_name: "patients", comment: "Wrong field name" },
  ]), { status: 200, headers: { "content-type": "application/json" } }));
  vi.stubGlobal("fetch", fetchMock);
  const postgres: CatalogPostgresAccess = {
    connect: vi.fn(async () => { throw new Error("PostgreSQL wire access must not be used"); }),
  };
  const introspector = new ConcreteCatalogTableIntrospector(postgres, store);

  await expect(introspector.scan(database({
    transport: "rest_api",
    baseUrl: "https://connector.internal/api",
    restPath: "/health",
    restAuth: "none",
  }), new AbortController().signal)).rejects.toThrow("REST schema response is invalid");
});