Préparer la notification d'une violation de données : dater la prise de connaissance et tenir les 72 heures

// lib/privacy/inventory.ts
export type Sensitivity = 'identifiant' | 'contact' | 'financier' | 'sensible';
 
export type PersonalField = {
  table: string;
  column: string;
  sensitivity: Sensitivity;
  /** Ce qui justifie sa présence — utile en cas de contrôle. */
  purpose: string;
};
 
export const PERSONAL_DATA: PersonalField[] = [
  { table: 'users', column: 'email',        sensitivity: 'contact',     purpose: 'authentification et notifications de commande' },
  { table: 'users', column: 'phone',        sensitivity: 'contact',     purpose: 'notification de livraison' },
  { table: 'users', column: 'birth_date',   sensitivity: 'identifiant', purpose: 'vérification d’âge sur les produits réglementés' },
  { table: 'orders', column: 'ship_address', sensitivity: 'contact',    purpose: 'livraison' },
  { table: 'loyalty', column: 'card_number', sensitivity: 'identifiant', purpose: 'programme de fidélité' },
];
 
/** Les tables qui contiennent au moins une donnée personnelle. */
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|adresse|birth|naissance|iban|card|carte|nom|name|cin|passport)/i;
 
test('toute colonne à allure personnelle est déclarée dans l’inventaire', 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, [], `colonnes non déclarées : ${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 '{}',
  -- Chaînage : chaque ligne scelle la précédente.
  prev_hash     text,
  hash          text NOT NULL
);
 
-- Ni modification ni suppression : un journal d'incident réécrit ne vaut rien.
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';
 
/**
 * Ajoute une entrée scellée sur la précédente.
 *
 * `signal` = quelque chose a été détecté. `confirmation` = un humain a établi
 * qu'il s'agit bien d'une compromission de données personnelles. C'est cette
 * seconde entrée qui fait courir les 72 heures, et c'est pourquoi les deux ne
 * peuvent pas partager le même type.
 */
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 };
}
 
/** Le moment qui fait courir le délai : la première confirmation humaine. */
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';
 
/** Multiplicateur au-delà duquel un volume de lecture devient un 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   -- moyenne par fenêtre sur 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) {
    // Un signal, pas une confirmation : il ne fait pas courir le délai.
    await record('signal', `Lecture massive sur ${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('Aucune confirmation enregistrée : le délai n’a pas commencé.');
 
  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,                                   // départ du délai
    deadline: new Date(awareness.getTime() + 72 * 3_600_000),
    description: `Accès non autorisé aux tables : ${affectedTables.join(', ')}.`,
    affectedCount: Number(count),
    categories,
    // Les conséquences et les mesures restent rédigées par un humain : ce sont
    // des appréciations, et une phrase générée les rendrait indéfendables.
    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', '[EXERCICE] Lecture massive détectée', 'drill');
await record('confirmation', '[EXERCICE] Compromission confirmée', 'drill');
 
const draft = await draftNotification(['users', 'orders']);
console.log(`Prise de connaissance : ${draft.awareness.toISOString()}`);
console.log(`Échéance             : ${draft.deadline.toISOString()}`);
console.log(`Personnes touchées   : ${draft.affectedCount}`);
console.log(`Catégories           : ${draft.categories.join(', ')}`);
console.log(`Brouillon produit en ${Math.round((Date.now() - started) / 1000)} s`);