PII, Minimisation and Retention

// Classification is only useful if code reads it. Keep it next to the schema.
export const DATA_CLASSIFICATION = {
  users: {
    id:            { class: 'internal',  retention: 'account_lifetime' },
    email:         { class: 'pii',       retention: 'account_lifetime', maskInLogs: true },
    name:          { class: 'pii',       retention: 'account_lifetime', maskInLogs: true },
    passwordHash:  { class: 'secret',    retention: 'account_lifetime', neverExpose: true },
    ipAddress:     { class: 'pii',       retention: '90d',             maskInLogs: true },
    dateOfBirth:   { class: 'sensitive', retention: 'account_lifetime',
                     encrypted: true, justification: 'age verification (regulatory)' },
    lastLoginAt:   { class: 'internal',  retention: '2y' },
  },
  audit_log: {
    actor_id:      { class: 'internal',  retention: '7y' },   // regulatory
    ip:            { class: 'pii',       retention: '1y', maskInLogs: true },
  },
};

// 1. Masking comes from the catalogue, not from a hand-maintained list
export function maskForLogs(table, row) {
  const spec = DATA_CLASSIFICATION[table] ?? {};
  return Object.fromEntries(Object.entries(row).map(([k, v]) => {
    const f = spec[k];
    if (f?.neverExpose) return [k, '[redacted]'];
    if (f?.maskInLogs) return [k, mask(k, v)];
    return [k, v];
  }));
}

function mask(field, value) {
  if (value == null) return value;
  if (/email/i.test(field)) {
    const [local, domain] = String(value).split('@');
    return `${local.slice(0, 2)}***@${domain}`;          // al***@example.com
  }
  if (/ip/i.test(field)) {
    return String(value).replace(/\.\d+$/, '.0')          // 203.0.113.0
                        .replace(/:[^:]+$/, ':0');
  }
  return String(value).slice(0, 1) + '***';
}

// 2. Retention runs from the same catalogue
export async function enforceRetention() {
  for (const [table, fields] of Object.entries(DATA_CLASSIFICATION)) {
    for (const [column, spec] of Object.entries(fields)) {
      const days = parseRetentionDays(spec.retention);
      if (!days) continue;

      const affected = await db(table)
        .where('created_at', '<', daysAgo(days))
        .whereNotNull(column)
        .update({ [column]: null });      // null the FIELD, keep the row

      if (affected) {
        logger.info({ table, column, affected, days }, 'retention enforced');
      }
    }
  }
}

// 3. And a CI check: a new column MUST be classified before it can merge.
it('every column is classified', async () => {
  for (const table of await listTables()) {
    for (const column of await listColumns(table)) {
      expect(DATA_CLASSIFICATION[table]?.[column]).toBeDefined();
    }
  }
});

// "Delete my account" has to reach every system that ever received the data.
// Map these paths BEFORE the first request arrives.
export async function eraseUser(userId, { requestId }) {
  const trace = { requestId, userId, startedAt: new Date(), steps: [] };
  const step = async (name, fn) => {
    try { await fn(); trace.steps.push({ name, status: 'done' }); }
    catch (err) { trace.steps.push({ name, status: 'failed', error: err.message }); throw err; }
  };

  // 1. Primary store — anonymise rather than delete where rows must survive
  //    for referential or regulatory reasons.
  await step('primary', async () => {
    await db('users').where({ id: userId }).update({
      email: `deleted-${userId}@invalid`,
      name: '[deleted]',
      date_of_birth_enc: null,
      ip_address: null,
      deleted_at: new Date(),
    });
  });

  // 2. Related tables the catalogue lists
  await step('related', async () => {
    await db('sessions').where({ user_id: userId }).del();
    await db('refresh_tokens').where({ user_id: userId }).del();
    await db('comments').where({ user_id: userId }).update({ author_name: '[deleted]' });
  });

  // 3. Caches — otherwise the deleted profile is served for another hour
  await step('cache', () => redis.del(`user:${userId}`, `profile:${userId}`));

  // 4. Search index
  await step('search', () => elastic.deleteByQuery({
    index: 'users', query: { term: { user_id: userId } },
  }));

  // 5. Analytics warehouse
  await step('warehouse', () => warehouse.deleteSubject(userId));

  // 6. Third-party processors — each has its own API and its own SLA
  await step('processors', () => Promise.all([
    stripe.customers.del(await stripeIdFor(userId)),
    intercom.contacts.delete(await intercomIdFor(userId)),
    sendgrid.suppressions.add(await emailFor(userId)),
  ]));

  // 7. Logs — usually handled by retention, but say so explicitly
  trace.steps.push({ name: 'logs', status: 'retention',
    note: 'PII fields expire from logs after 30 days' });

  // 8. Backups — you cannot rewrite them; document the window
  trace.steps.push({ name: 'backups', status: 'scheduled',
    note: 'backups roll off after 35 days; restores re-run erasure' });

  await db('erasure_log').insert({
    ...trace, completedAt: new Date(), trace: JSON.stringify(trace.steps),
  });
  return trace;
}

// The backup point is worth stating to your DPO in advance: you cannot
// selectively edit a backup, so the honest answer is a documented window plus
// a restore-time re-run. Discovering that during a request is worse.

// ── Tokenise: do not hold what a provider will hold for you ──────────
// ❌ Never in your database
{ cardNumber: '4242424242424242', cvv: '123', expiry: '12/28' }

// ✅ The provider holds it; you hold a reference
const pm = await stripe.paymentMethods.create({
  type: 'card',
  card: { token: req.body.stripeToken },   // collected client-side by their SDK
});
await db('customers').where({ id }).update({
  stripe_payment_method_id: pm.id,         // 'pm_1Abc...'
  card_last4: pm.card.last4,               // display only
  card_brand: pm.card.brand,
  card_exp_month: pm.card.exp_month,
});
// A breach now yields a token that is useless outside your Stripe account,
// and PCI scope shrinks dramatically.

// ── Non-production data: synthesise, do not copy ─────────────────────
// ❌ pg_dump production | psql staging
//    Every engineer, every contractor and every staging bug now has access to
//    real customer data under weaker controls.

// ✅ Generate it
import { faker } from '@faker-js/faker';

export async function seedStaging({ users = 1000 } = {}) {
  faker.seed(42);                          // deterministic, so bugs reproduce
  for (let i = 0; i < users; i++) {
    await db('users').insert({
      email: faker.internet.email(),
      name: faker.person.fullName(),
      created_at: faker.date.past({ years: 2 }),
    });
  }
}

// ✅ Or anonymise on the way out, if realistic shape genuinely matters
export async function anonymisedDump() {
  return db.raw(`
    COPY (
      SELECT
        id,
        'user-' || id || '@example.invalid'          AS email,
        'User ' || id                               AS name,
        NULL                                        AS date_of_birth_enc,
        regexp_replace(ip_address, '\\.\\d+$', '.0') AS ip_address,
        created_at,
        plan
      FROM users
    ) TO STDOUT WITH CSV HEADER`);
}

// And make it a rule with teeth: staging databases are provisioned by the
// seed script only, and a CI job asserts that no staging email matches a
// production domain.