Repository Public
1import { sql } from "../db";2import { audit } from "../audit";34/** Retention: personal data older than the owner's retention period is deleted, not archived. */5export async function purgeExpiredData(): Promise<Record<string, number>> {6 const out: Record<string, number> = {};7 const owners = await sql<{ user_id: string; retention_days: number }[]>`SELECT user_id, retention_days FROM settings`;8 for (const o of owners) {9 const cutoff = new Date(Date.now() - o.retention_days * 86_400_000);10 const r = await sql`DELETE FROM booking_requests WHERE user_id = ${o.user_id} AND ends_at < ${cutoff} RETURNING id`;11 const b = await sql`DELETE FROM bookings WHERE user_id = ${o.user_id} AND ends_at < ${cutoff} RETURNING id`;12 const c = await sql`DELETE FROM contacts c WHERE c.user_id = ${o.user_id} AND c.updated_at < ${cutoff}13 AND NOT EXISTS (SELECT 1 FROM booking_requests r WHERE r.contact_id = c.id) AND NOT EXISTS (SELECT 1 FROM bookings k WHERE k.contact_id = c.id) RETURNING id`;14 const a = await sql`DELETE FROM audit_events WHERE user_id = ${o.user_id} AND created_at < ${cutoff} RETURNING id`;15 const n = await sql`DELETE FROM notification_jobs WHERE user_id = ${o.user_id} AND created_at < ${cutoff} RETURNING id`;16 out.requests = (out.requests ?? 0) + r.length; out.bookings = (out.bookings ?? 0) + b.length; out.contacts = (out.contacts ?? 0) + c.length;17 out.audit = (out.audit ?? 0) + a.length; out.notifications = (out.notifications ?? 0) + n.length;18 if (r.length + b.length + c.length > 0) await audit({ userId: o.user_id, actor: "system", action: "retention.purged", meta: { requests: r.length, bookings: b.length, contacts: c.length } });19 }20 await sql`DELETE FROM rate_limits WHERE window_start < now() - interval '1 day'`;21 await sql`DELETE FROM webauthn_challenges WHERE expires_at < now()`;22 await sql`DELETE FROM setup_tokens WHERE expires_at < now() - interval '7 days'`;23 await sql`DELETE FROM sessions WHERE expires_at < now() - interval '30 days' OR revoked_at < now() - interval '30 days'`;24 await sql`DELETE FROM busy_blocks WHERE source = 'manual' AND ends_at < now() - interval '400 days'`;25 return out;26}2728/**29 * Erasure of one person (Québec Law 25 / right to deletion): the contact, their requests, answers, links,30 * notifications and past appointments. Refused while a confirmed appointment is still to come.31 */32export async function eraseContact(userId: string, contactId: string): Promise<void> {33 await sql.begin(async (tx) => {34 const [c] = await tx`SELECT id FROM contacts WHERE id = ${contactId} AND user_id = ${userId} FOR UPDATE`;35 if (!c) throw Object.assign(new Error("not_found"), { status: 404 });36 const future = await tx`SELECT 1 FROM bookings WHERE contact_id = ${contactId} AND status = 'confirmed' AND ends_at > now() LIMIT 1`;37 if (future.length) throw Object.assign(new Error("future_booking"), { status: 409 });38 await tx`DELETE FROM notification_jobs WHERE request_id IN (SELECT id FROM booking_requests WHERE contact_id = ${contactId}) OR booking_id IN (SELECT id FROM bookings WHERE contact_id = ${contactId})`;39 await tx`DELETE FROM booking_requests WHERE contact_id = ${contactId}`;40 await tx`DELETE FROM bookings WHERE contact_id = ${contactId}`;41 await tx`DELETE FROM contacts WHERE id = ${contactId}`;42 await audit({ userId, actor: "admin", action: "contact.erased", entity: "contact", entityId: contactId }, tx);43 });44}45