3 blockers. Harder than it looks.

/**
 * Experiment: fk-no-cascade
 *
 * Assumption under test:
 *   The FK `orders.customer_id REFERENCES customers(id)` (no ON DELETE clause)
 *   allows a customer row to be deleted without a constraint violation even when
 *   orders exist for that customer.
 *
 * The test PASSES when the assumption HOLDS (delete succeeds).
 * The test FAILS when the assumption is BROKEN (FK violation is raised).
 *
 * Run:
 *   node --import tsx --test settle-experiments/fk-no-cascade.test.ts
 */

import { test } from "node:test";
import assert from "node:assert/strict";
import { openDb } from "../src/db.ts";

test("assumption: FK allows customer delete when orders exist (no cascade)", async () => {
  const db = await openDb();

  // Step 1 – confirm customer 1 has orders (seed data guarantees this at scale,
  // but we assert explicitly so the evidence is in the failure message).
  const { rows: countRows } = await db.query<{ cnt: number }>(
    `SELECT count(*)::int AS cnt FROM orders WHERE customer_id = 1`,
  );
  const orderCount = countRows[0].cnt;
  assert.ok(
    orderCount > 0,
    `Pre-condition failed: expected orders for customer 1, got ${orderCount}`,
  );

  // Step 2 – attempt the delete inside a transaction, then roll it back so the
  // database is left unchanged regardless of outcome.
  let deleteError: Error | null = null;
  try {
    await db.exec(`BEGIN`);
    await db.exec(`DELETE FROM customers WHERE id = 1`);
    await db.exec(`ROLLBACK`);
  } catch (err) {
    // Roll back on error so the connection is clean.
    try { await db.exec(`ROLLBACK`); } catch { /* ignore */ }
    deleteError = err as Error;
  }

  // The assumption says the delete should succeed (no FK violation).
  // If deleteError is non-null the assumption is FALSE – we surface the
  // concrete error message so it appears directly in the test output.
  assert.equal(
    deleteError,
    null,
    `FK violation raised while deleting customer 1 who has ${orderCount} orders: ${deleteError?.message}`,
  );
});

✖ assumption: FK allows customer delete when orders exist (no cascade) (4314.9711ms)
ℹ fail 1
✖ failing tests:
✖ assumption: FK allows customer delete when orders exist (no cascade) (4314.9711ms)
  AssertionError [ERR_ASSERTION]: FK violation raised while deleting customer 1 who has 33 orders: update or delete on table "customers" violates foreign key constraint "orders_customer_id_fkey" on table "orders"
  + actual - expected
  + error: update or delete on table "customers" violates foreign key constraint "orders_customer_id_fkey" on table "orders"
    generatedMessage: false,
    actual: error: update or delete on table "customers" violates foreign key constraint "orders_customer_id_fkey" on table "orders"
    expected: null,

/**
 * Experiment: are customers.name and customers.region nullable?
 *
 * PASSES  – if the assumption holds (columns accept NULL / sentinels today).
 * FAILS   – if the assumption is broken (NOT NULL constraint is in effect).
 *
 * Run:
 *   node --import tsx --test settle-experiments/name-nullable.test.ts
 */

import { test } from "node:test";
import assert from "node:assert/strict";
import { openDb } from "../src/db.ts";

// ── helpers ───────────────────────────────────────────────────────────────────

type ColumnRow = { column_name: string; is_nullable: string };

async function columnNullability(
  db: Awaited<ReturnType<typeof openDb>>,
  tableName: string,
  columnName: string,
): Promise<string> {
  const { rows } = await db.query<ColumnRow>(
    `SELECT column_name, is_nullable
       FROM information_schema.columns
      WHERE table_name   = $1
        AND column_name  = $2`,
    [tableName, columnName],
  );
  if (rows.length === 0) return "COLUMN_NOT_FOUND";
  return rows[0].is_nullable; // 'YES' or 'NO'
}

// ── test ──────────────────────────────────────────────────────────────────────

test("customers.name and customers.region are nullable (anonymisation assumption)", async (t) => {
  const db = await openDb();

  // 1. Check information_schema to read the declared nullability.
  const nameNullable   = await columnNullability(db, "customers", "name");
  const regionNullable = await columnNullability(db, "customers", "region");

  // 2. Try an actual UPDATE SET name = NULL on a real row.
  //    If the column is NOT NULL this throws; we catch and record the error.
  let updateError: string | null = null;
  try {
    await db.query(`UPDATE customers SET name = NULL WHERE id = 1`);
  } catch (err) {
    updateError = (err as Error).message;
  }

  // 3. Try an actual UPDATE SET region = NULL on a real row.
  let updateRegionError: string | null = null;
  try {
    await db.query(`UPDATE customers SET region = NULL WHERE id = 1`);
  } catch (err) {
    updateRegionError = (err as Error).message;
  }

  // 4. Assertions – the test PASSES only if both columns accept NULL.
  assert.equal(
    nameNullable,
    "YES",
    `customers.name is_nullable=${nameNullable} (expected YES). ` +
      `UPDATE error: ${updateError ?? "none"}`,
  );

  assert.equal(
    regionNullable,
    "YES",
    `customers.region is_nullable=${regionNullable} (expected YES). ` +
      `UPDATE error: ${updateRegionError ?? "none"}`,
  );

  assert.equal(
    updateError,
    null,
    `UPDATE customers SET name = NULL was rejected: ${updateError}`,
  );

  assert.equal(
    updateRegionError,
    null,
    `UPDATE customers SET region = NULL was rejected: ${updateRegionError}`,
  );
});

✖ customers.name and customers.region are nullable (anonymisation assumption) (4787.7349ms)
ℹ fail 1
✖ failing tests:
✖ customers.name and customers.region are nullable (anonymisation assumption) (4787.7349ms)
  AssertionError [ERR_ASSERTION]: customers.name is_nullable=NO (expected YES). UPDATE error: null value in column "name" of relation "customers" violates not-null constraint
    generatedMessage: false,
    actual: 'NO',
    expected: 'YES',

/**
 * Experiment: dashboard-join-survives-deletion
 *
 * Assumption under test:
 *   The /stats/top-customers revenue totals remain correct after a customer is
 *   deleted — i.e. their historical orders still count in the leaderboard.
 *
 * How:
 *   1. Boot the real server with the real DB (openDb + buildServer).
 *   2. Insert a known "whale" customer with two large paid orders dated within
 *      the last 30 days, so they definitely appear in the top-customers query.
 *   3. Record their revenue_cents from /stats/top-customers.
 *   4. Simulate deletion by:
 *        a. Dropping the FK constraint so customer_id can become NULL, then
 *        b. Setting all their orders' customer_id to NULL, then
 *        c. Deleting the customer row.
 *      This mirrors "soft-delete or CASCADE SET NULL" behaviour — the orders
 *      survive but the customer is gone.
 *   5. Re-query /stats/top-customers and check whether the sum of all returned
 *      revenue_cents equals the pre-deletion total.
 *
 * Pass condition  (assumption HOLDS):  totals are equal.
 * Fail condition  (assumption BROKEN): the whale's revenue has disappeared,
 *                                      making the post-deletion total lower.
 */

import { test } from "node:test";
import assert from "node:assert/strict";
import type { AddressInfo } from "node:net";
import type { Server } from "node:http";
import { openDb } from "../src/db.ts";
import { buildServer } from "../src/server.ts";
import type { TopCustomer } from "../src/server.ts";

// ---------------------------------------------------------------------------
// Helper — spin up a private server and return its base URL + db handle
// ---------------------------------------------------------------------------
async function startServer() {
  const db = await openDb();
  const server: Server = buildServer(db);
  await new Promise<void>((resolve) => server.listen(0, resolve));
  const base = `http://127.0.0.1:${(server.address() as AddressInfo).port}`;
  return { db, server, base };
}

// ---------------------------------------------------------------------------
// The experiment
// ---------------------------------------------------------------------------
test("dashboard JOIN survives customer deletion — revenue totals must not change", async () => {
  const { db, server, base } = await startServer();

  try {
    // -----------------------------------------------------------------------
    // 1. Insert a known whale customer (id chosen well above the 5000 seeded
    //    ones so there is no collision).
    // -----------------------------------------------------------------------
    const WHALE_ID = 99999;
    const ORDER_A   = 1_000_000; // 1 000 000 cents = $10 000
    const ORDER_B   =   500_000; // 500 000 cents   = $5 000
    const WHALE_TOTAL = ORDER_A + ORDER_B;

    await db.exec(`
      INSERT INTO customers (id, name, region) VALUES (${WHALE_ID}, 'Whale Corp', 'north');

      INSERT INTO orders (customer_id, amount_cents, status, created_at) VALUES
        (${WHALE_ID}, ${ORDER_A}, 'paid', now() - interval '1 day'),
        (${WHALE_ID}, ${ORDER_B}, 'paid', now() - interval '2 days');
    `);

    // -----------------------------------------------------------------------
    // 2. Record the pre-deletion total revenue across ALL top-customers rows.
    //    We use limit=100 to capture as many customers as possible.
    //    The whale must be present (their revenue guarantees top placement).
    // -----------------------------------------------------------------------
    const res1 = await fetch(`${base}/stats/top-customers?limit=100`);
    assert.equal(res1.status, 200, "pre-deletion fetch should succeed");
    const before: TopCustomer[] = await res1.json();

    const whaleRow = before.find((r) => r.customer_id === WHALE_ID);
    assert.ok(
      whaleRow,
      `Whale customer (id=${WHALE_ID}) must appear in top-customers before deletion`,
    );
    assert.equal(
      whaleRow.revenue_cents,
      WHALE_TOTAL,
      `Whale revenue before deletion: expected ${WHALE_TOTAL}, got ${whaleRow.revenue_cents}`,
    );

    const totalBefore = before.reduce((s, r) => s + r.revenue_cents, 0);

    // -----------------------------------------------------------------------
    // 3. Simulate deletion: drop the FK, NULL-out the orders, delete the row.
    //    This is the realistic "soft-delete leaves orphaned orders" scenario.
    // -----------------------------------------------------------------------
    await db.exec(`
      -- Drop the FK so we can set customer_id = NULL
      ALTER TABLE orders DROP CONSTRAINT orders_customer_id_fkey;

      -- Make the column nullable so NULL is accepted
      ALTER TABLE orders ALTER COLUMN customer_id DROP NOT NULL;

      -- Orphan the whale's orders (customer deleted, orders remain)
      UPDATE orders SET customer_id = NULL WHERE customer_id = ${WHALE_ID};

      -- Now delete the customer row
      DELETE FROM customers WHERE id = ${WHALE_ID};
    `);

    // -----------------------------------------------------------------------
    // 4. Re-run the dashboard query via the HTTP endpoint.
    // -----------------------------------------------------------------------
    const res2 = await fetch(`${base}/stats/top-customers?limit=100`);
    assert.equal(res2.status, 200, "post-deletion fetch should succeed");
    const after: TopCustomer[] = await res2.json();

    const totalAfter = after.reduce((s, r) => s + r.revenue_cents, 0);

    // The whale must NOT appear after deletion (they're gone from customers)
    const whaleAfter = after.find((r) => r.customer_id === WHALE_ID);
    assert.equal(
      whaleAfter,
      undefined,
      `Whale customer (id=${WHALE_ID}) must not appear in results after deletion`,
    );

    // -----------------------------------------------------------------------
    // 5. Core assertion: total revenue must be unchanged.
    //    If the INNER JOIN silently drops orphaned ord

✖ dashboard JOIN survives customer deletion — revenue totals must not change (4601.4126ms)
ℹ fail 1
✖ failing tests:
✖ dashboard JOIN survives customer deletion — revenue totals must not change (4601.4126ms)
  AssertionError [ERR_ASSERTION]: Revenue total changed after customer deletion! Before: 42275239 cents, After: 41153801 cents. Difference: 1121438 cents (whale had 1500000 cents). The INNER JOIN silently drops orders whose customer row was deleted — assumption is BROKEN.
  + actual - expected
    generatedMessage: false,
    actual: 41153801,
    expected: 42275239,