Breach notification readiness: dating awareness and holding the 72 hours

// lib/privacy/inventory.ts
export type Sensitivity = 'identifier' | 'contact' | 'financial' | 'special';
 
export type PersonalField = {
  table: string;
  column: string;
  sensitivity: Sensitivity;
  /** Why it exists at all — the thing you get asked under audit. */
  purpose: string;
};
 
export const PERSONAL_DATA: PersonalField[] = [
  { table: 'users', column: 'email',         sensitivity: 'contact',    purpose: 'authentication and order notifications' },
  { table: 'users', column: 'phone',         sensitivity: 'contact',    purpose: 'delivery notification' },
  { table: 'users', column: 'birth_date',    sensitivity: 'identifier', purpose: 'age check on regulated products' },
  { table: 'orders', column: 'ship_address', sensitivity: 'contact',    purpose: 'delivery' },
  { table: 'loyalty', column: 'card_number', sensitivity: 'identifier', purpose: 'loyalty programme' },
];
 
/** Tables holding at least one personal field. */
export const PERSONAL_TABLES = [...new Set(PERSONAL_DATA.map((f) => f.table))];

// lib/privacy/inventory.test.ts
import { test } from 'node:test';
import assert from 'node:assert/strict';
import { PERSONAL_DATA } from './inventory';
import { query } from '@/lib/db/pool';
 
const SUSPECT = /(email|phone|tel|address|birth|dob|iban|card|name|passport|national_id)/i;
 
test('every personal-looking column is declared in the inventory', async () => {
  const { rows } = await query<{ table_name: string; column_name: string }>(
    `SELECT table_name, column_name FROM information_schema.columns
      WHERE table_schema = 'public'`,
  );
  const declared = new Set(PERSONAL_DATA.map((f) => `${f.table}.${f.column}`));
  const undeclared = rows
    .filter((r) => SUSPECT.test(r.column_name))
    .map((r) => `${r.table_name}.${r.column_name}`)
    .filter((k) => !declared.has(k));
 
  assert.deepEqual(undeclared, [], `undeclared columns: ${undeclared.join(', ')}`);
});

-- migrations/incident_log.sql
CREATE TABLE incident_log (
  id            bigserial PRIMARY KEY,
  occurred_at   timestamptz NOT NULL DEFAULT now(),
  kind          text NOT NULL CHECK (kind IN ('signal', 'confirmation', 'notification', 'note')),
  summary       text NOT NULL,
  actor         text NOT NULL,
  detail        jsonb NOT NULL DEFAULT '{}',
  -- Chaining: each row seals the one before it.
  prev_hash     text,
  hash          text NOT NULL
);
 
-- No updates, no deletes: a rewritten incident log is worth nothing.
CREATE RULE incident_log_no_update AS ON UPDATE TO incident_log DO INSTEAD NOTHING;
CREATE RULE incident_log_no_delete AS ON DELETE TO incident_log DO INSTEAD NOTHING;

// lib/privacy/incident-log.ts
import { createHash } from 'node:crypto';
import { query } from '@/lib/db/pool';
 
export type IncidentKind = 'signal' | 'confirmation' | 'notification' | 'note';
 
/**
 * Append an entry sealed against the previous one.
 *
 * `signal` = something was detected. `confirmation` = a human established that
 * personal data really was compromised. It is the second that starts the 72
 * hours, which is why the two cannot share a kind.
 */
export async function record(
  kind: IncidentKind,
  summary: string,
  actor: string,
  detail: Record<string, unknown> = {},
) {
  const { rows } = await query<{ hash: string }>(
    `SELECT hash FROM incident_log ORDER BY id DESC LIMIT 1`,
  );
  const prev = rows[0]?.hash ?? null;
  const at = new Date().toISOString();
  const hash = createHash('sha256')
    .update(`${prev ?? ''}|${at}|${kind}|${summary}|${actor}|${JSON.stringify(detail)}`)
    .digest('hex');
 
  await query(
    `INSERT INTO incident_log (occurred_at, kind, summary, actor, detail, prev_hash, hash)
     VALUES ($1, $2, $3, $4, $5, $6, $7)`,
    [at, kind, summary, actor, JSON.stringify(detail), prev, hash],
  );
  return { at, hash };
}
 
/** The moment that starts the clock: the first human confirmation. */
export async function awarenessMoment(): Promise<Date | null> {
  const { rows } = await query<{ occurred_at: Date }>(
    `SELECT occurred_at FROM incident_log
      WHERE kind = 'confirmation' ORDER BY id ASC LIMIT 1`,
  );
  return rows[0]?.occurred_at ?? null;
}

// lib/privacy/detect.ts
import { query } from '@/lib/db/pool';
import { PERSONAL_TABLES } from './inventory';
import { record } from './incident-log';
 
/** How far above baseline a read volume becomes a signal. */
const ANOMALY_FACTOR = 10;
 
export async function scanForBulkReads(windowMinutes = 15) {
  const { rows } = await query<{ actor: string; table_name: string; reads: number; baseline: number }>(
    `WITH recent AS (
       SELECT actor, table_name, count(*)::int AS reads
         FROM data_access_log
        WHERE at > now() - ($1 || ' minutes')::interval
          AND table_name = ANY($2)
        GROUP BY actor, table_name
     ), norm AS (
       SELECT actor, table_name,
              (count(*) / 96.0) AS baseline   -- mean per window across 24 h
         FROM data_access_log
        WHERE at > now() - interval '24 hours'
          AND table_name = ANY($2)
        GROUP BY actor, table_name
     )
     SELECT r.actor, r.table_name, r.reads, COALESCE(n.baseline, 0) AS baseline
       FROM recent r LEFT JOIN norm n USING (actor, table_name)
      WHERE r.reads > GREATEST(COALESCE(n.baseline, 0) * $3, 500)`,
    [windowMinutes, PERSONAL_TABLES, ANOMALY_FACTOR],
  );
 
  for (const r of rows) {
    // A signal, not a confirmation: it does not start the clock.
    await record('signal', `Bulk read on ${r.table_name}`, 'detector', {
      actor: r.actor, reads: r.reads, baseline: Math.round(r.baseline),
    });
  }
  return rows;
}

// lib/privacy/report.ts
import { query } from '@/lib/db/pool';
import { awarenessMoment } from './incident-log';
import { PERSONAL_DATA } from './inventory';
 
export async function draftNotification(affectedTables: string[]) {
  const awareness = await awarenessMoment();
  if (!awareness) throw new Error('No confirmation recorded: the clock has not started.');
 
  const { rows: [{ count }] } = await query<{ count: string }>(
    `SELECT count(DISTINCT user_id)::text AS count FROM data_access_log
      WHERE table_name = ANY($1) AND at >= $2`,
    [affectedTables, awareness],
  );
 
  const categories = PERSONAL_DATA
    .filter((f) => affectedTables.includes(f.table))
    .map((f) => `${f.column} (${f.sensitivity})`);
 
  return {
    awareness,                                   // when the clock started
    deadline: new Date(awareness.getTime() + 72 * 3_600_000),
    description: `Unauthorised access to tables: ${affectedTables.join(', ')}.`,
    affectedCount: Number(count),
    categories,
    // Consequences and measures stay human-written: they are judgements, and a
    // generated sentence would make them indefensible.
    consequences: null,
    measures: null,
  };
}

// scripts/breach-drill.ts
import { record, awarenessMoment } from '@/lib/privacy/incident-log';
import { draftNotification } from '@/lib/privacy/report';
 
const started = Date.now();
await record('signal', '[DRILL] Bulk read detected', 'drill');
await record('confirmation', '[DRILL] Compromise confirmed', 'drill');
 
const draft = await draftNotification(['users', 'orders']);
console.log(`Awareness : ${draft.awareness.toISOString()}`);
console.log(`Deadline  : ${draft.deadline.toISOString()}`);
console.log(`Affected  : ${draft.affectedCount}`);
console.log(`Categories: ${draft.categories.join(', ')}`);
console.log(`Draft produced in ${Math.round((Date.now() - started) / 1000)} s`);