Repository Public
1import { z } from "zod";2import { j, sql } from "../db";3import { ApiError, body, clientIp, uuid } from "../http";4import { mailConfigured, nowMs } from "../env";5import { randomToken, sha256 } from "../crypto";6import { audit } from "../audit";7import { passkeyCount, revokeSession, type AdminContext } from "../auth";8import { checkSlot, computeSlots, openWindows } from "../engine/slots";9import { engineInput, externalFreshness, getEventType, getSettings, loadExceptions, loadRules, type EventTypeRow } from "../services/state";10import { approveRequest, cancelBooking, createBookingByAdmin, declineRequest, proposeTime, rescheduleBooking, setInternalNote } from "../services/booking";11import { enqueue } from "../services/notify";12import { requeue } from "../services/outbox";13import { addIcsCalendar, removeCalendar, syncCalendar } from "../services/external";14import { eraseContact } from "../services/maintenance";15import { email, eventTypeInput, exceptionInput, isoInstant, localDate, rulesInput, settingsInput, toMs } from "./schemas";16import type { RouteDef } from "./router";1718type R = RouteDef<AdminContext>;19const MIN = 60_000;20const iso = (d: Date | null | undefined) => (d ? d.toISOString() : null);2122async function ownType(userId: string, id: string): Promise<EventTypeRow> {23 const et = await getEventType({ id: uuid.parse(id) });24 if (!et || et.user_id !== userId) throw new ApiError(404, "not_found", "Appointment type not found");25 return et;26}2728// ── Requests ────────────────────────────────────────────────────────────────────────────────────────2930interface RequestListRow {31 id: string; status: string; starts_at: Date; ends_at: Date; visitor_tz: string; locale: string; hold_expires_at: Date | null; reason: string; internal_note: string;32 decline_message: string; proposed_starts_at: Date | null; proposed_ends_at: Date | null; proposal_expires_at: Date | null; proposal_message: string;33 reschedule_of_booking_id: string | null; booking_id: string | null; conflict_flag: string; created_at: Date; decided_at: Date | null;34 event_type_id: string; type_title: string; type_color: string; location_kind: string; contact_id: string | null; contact_name: string | null; contact_email: string | null; organization: string | null;35 current_starts_at: Date | null;36}3738async function listRequests(userId: string, where: { statuses?: string[]; id?: string; contactId?: string; limit?: number }) {39 const rows = await sql<RequestListRow[]>`40 SELECT r.id, r.status, r.starts_at, r.ends_at, r.visitor_tz, r.locale, r.hold_expires_at, r.reason, r.internal_note, r.decline_message, r.proposed_starts_at, r.proposed_ends_at,41 r.proposal_expires_at, r.proposal_message, r.reschedule_of_booking_id, r.booking_id, r.conflict_flag, r.created_at, r.decided_at,42 t.id AS event_type_id, t.title AS type_title, t.color AS type_color, t.location_kind, c.id AS contact_id, c.name AS contact_name, c.email::text AS contact_email, c.organization,43 ob.starts_at AS current_starts_at44 FROM booking_requests r JOIN event_types t ON t.id = r.event_type_id LEFT JOIN contacts c ON c.id = r.contact_id LEFT JOIN bookings ob ON ob.id = r.reschedule_of_booking_id45 WHERE r.user_id = ${userId}46 AND (${where.id ?? null}::uuid IS NULL OR r.id = ${where.id ?? null})47 AND (${where.contactId ?? null}::uuid IS NULL OR r.contact_id = ${where.contactId ?? null})48 AND (${where.statuses ?? null}::text[] IS NULL OR r.status = ANY(${where.statuses ?? null}))49 ORDER BY CASE WHEN r.status IN ('pending', 'reschedule_requested') THEN 0 ELSE 1 END, CASE WHEN r.status IN ('pending', 'reschedule_requested') THEN r.starts_at END, r.created_at DESC50 LIMIT ${where.limit ?? 200}`;51 const answers = rows.length ? await sql<{ request_id: string; label: string; value: string }[]>`SELECT request_id, label, value FROM booking_answers WHERE request_id = ANY(${rows.map((r) => r.id)})` : [];52 const settings = await getSettings(userId);53 const now = nowMs();54 const out = [];55 for (const r of rows) {56 // Live conflict check for open requests: is the requested time still free right now?57 let conflict: string | null = null;58 if (r.status === "pending") {59 const et = await getEventType({ id: r.event_type_id });60 if (et) {61 const st = await engineInput(et, settings.timezone, r.starts_at.getTime(), r.ends_at.getTime(), { now, ignoreRequestId: r.id, ignoreBookingId: r.reschedule_of_booking_id });62 st.params.durationMin = Math.round((r.ends_at.getTime() - r.starts_at.getTime()) / MIN);63 conflict = checkSlot(st, r.starts_at.getTime(), { enforceNotice: false, enforceLimits: false });64 }65 }66 out.push({67 id: r.id, status: r.status, startsAt: iso(r.starts_at), endsAt: iso(r.ends_at), visitorTz: r.visitor_tz, locale: r.locale, holdExpiresAt: iso(r.hold_expires_at),68 holds: r.status === "pending" && !!r.hold_expires_at && r.hold_expires_at.getTime() > now,69 reason: r.reason, internalNote: r.internal_note, declineMessage: r.decline_message,70 proposal: r.proposed_starts_at ? { startsAt: iso(r.proposed_starts_at), endsAt: iso(r.proposed_ends_at), expiresAt: iso(r.proposal_expires_at), message: r.proposal_message } : null,71 rescheduleOf: r.reschedule_of_booking_id ? { bookingId: r.reschedule_of_booking_id, currentStartsAt: iso(r.current_starts_at) } : null,72 bookingId: r.booking_id, conflictFlag: r.conflict_flag, conflict, createdAt: iso(r.created_at), decidedAt: iso(r.decided_at),73 type: { id: r.event_type_id, title: r.type_title, color: r.type_color, locationKind: r.location_kind },74 contact: r.contact_id ? { id: r.contact_id, name: r.contact_name, email: r.contact_email, organization: r.organization } : null,75 answers: answers.filter((a) => a.request_id === r.id).map((a) => ({ label: a.label, value: a.value })),76 });77 }78 return out;79}8081// ── Bookings ────────────────────────────────────────────────────────────────────────────────────────8283async function listBookings(userId: string, where: { from?: Date; to?: Date; id?: string; contactId?: string; statuses?: string[]; limit?: number; order?: "asc" | "desc" }) {84 const rows = await sql<{ id: string; title: string; starts_at: Date; ends_at: Date; status: string; location_kind: string; location_text: string; cancelled_at: Date | null; cancelled_by: string | null; cancel_reason: string; ics_sequence: number; created_at: Date;85 event_type_id: string | null; type_title: string | null; type_color: string | null; contact_id: string | null; contact_name: string | null; contact_email: string | null; reason: string | null; request_id: string | null; internal_note: string | null }[]>`86 SELECT b.id, b.title, b.starts_at, b.ends_at, b.status, b.location_kind, b.location_text, b.cancelled_at, b.cancelled_by, b.cancel_reason, b.ics_sequence, b.created_at,87 t.id AS event_type_id, t.title AS type_title, t.color AS type_color, c.id AS contact_id, c.name AS contact_name, c.email::text AS contact_email,88 q.reason, q.id AS request_id, q.internal_note89 FROM bookings b LEFT JOIN event_types t ON t.id = b.event_type_id LEFT JOIN contacts c ON c.id = b.contact_id90 LEFT JOIN LATERAL (SELECT id, reason, internal_note FROM booking_requests WHERE booking_id = b.id AND reschedule_of_booking_id IS NULL ORDER BY created_at LIMIT 1) q ON true91 WHERE b.user_id = ${userId}92 AND (${where.id ?? null}::uuid IS NULL OR b.id = ${where.id ?? null})93 AND (${where.contactId ?? null}::uuid IS NULL OR b.contact_id = ${where.contactId ?? null})94 AND (${where.from ?? null}::timestamptz IS NULL OR b.ends_at > ${where.from ?? null})95 AND (${where.to ?? null}::timestamptz IS NULL OR b.starts_at < ${where.to ?? null})96 AND (${where.statuses ?? null}::text[] IS NULL OR b.status = ANY(${where.statuses ?? null}))97 ORDER BY CASE WHEN ${where.order ?? "asc"} = 'asc' THEN b.starts_at END ASC, CASE WHEN ${where.order ?? "asc"} = 'desc' THEN b.starts_at END DESC98 LIMIT ${where.limit ?? 500}`;99 return rows.map((b) => ({100 id: b.id, title: b.title, startsAt: iso(b.starts_at), endsAt: iso(b.ends_at), status: b.status, locationKind: b.location_kind, locationText: b.location_text,101 cancelledAt: iso(b.cancelled_at), cancelledBy: b.cancelled_by, cancelReason: b.cancel_reason, sequence: b.ics_sequence, createdAt: iso(b.created_at),102 type: b.event_type_id ? { id: b.event_type_id, title: b.type_title, color: b.type_color } : null,103 contact: b.contact_id ? { id: b.contact_id, name: b.contact_name, email: b.contact_email } : null,104 reason: b.reason ?? "", requestId: b.request_id, internalNote: b.internal_note ?? "",105 }));106}107108const rangeQuery = z.object({ from: isoInstant, to: isoInstant });109function parseRange(url: URL, maxDays = 100) {110 const q = rangeQuery.parse({ from: url.searchParams.get("from"), to: url.searchParams.get("to") });111 const from = toMs(q.from), to = toMs(q.to);112 if (to <= from || to - from > maxDays * 86_400_000) throw new ApiError(400, "invalid_range");113 return { from: new Date(from), to: new Date(to) };114}115116const exceptionRow = (e: { id: string; event_type_id: string | null; kind: string; mode: string; starts_local: string | null; ends_local: string | null; all_day: boolean; weekdays: number[] | null; start_minute: number | null; duration_min: number | null; valid_from: string | null; valid_until: string | null; note: string }) => ({117 id: e.id, eventTypeId: e.event_type_id, kind: e.kind, mode: e.mode, startsLocal: e.starts_local?.replace(" ", "T").slice(0, 16) ?? null, endsLocal: e.ends_local?.replace(" ", "T").slice(0, 16) ?? null,118 allDay: e.all_day, weekdays: e.weekdays, startMinute: e.start_minute, durationMin: e.duration_min, validFrom: e.valid_from, validUntil: e.valid_until, note: e.note,119});120121export const adminRoutes: R[] = [122 // ── Session, dashboard ──123 ["GET", "me", async ({ ctx }) => {124 const s = await getSettings(ctx.userId);125 return { user: { email: ctx.email, displayName: ctx.displayName }, csrfToken: ctx.csrfToken, settings: { timezone: s.timezone, locale: s.locale, publicName: s.public_name, brandColor: s.brand_color } };126 }],127 ["GET", "dashboard", async ({ ctx }) => {128 const now = new Date(nowMs());129 const settings = await getSettings(ctx.userId);130 const [upcoming, open, cancelled, fresh] = [131 await listBookings(ctx.userId, { from: now, statuses: ["confirmed"], limit: 8 }),132 await listRequests(ctx.userId, { statuses: ["pending", "reschedule_requested"], limit: 50 }),133 (await listBookings(ctx.userId, { statuses: ["cancelled"], limit: 50, order: "desc" })).filter((b) => b.cancelledAt && new Date(b.cancelledAt).getTime() > now.getTime() - 14 * 86_400_000).slice(0, 6),134 await externalFreshness(ctx.userId, settings),135 ];136 const [counts] = await sql<{ week: number; month: number; approved: number; declined: number }[]>`137 SELECT (SELECT count(*)::int FROM bookings WHERE user_id = ${ctx.userId} AND status = 'confirmed' AND starts_at >= ${now} AND starts_at < ${new Date(now.getTime() + 7 * 86_400_000)}) AS week,138 (SELECT count(*)::int FROM bookings WHERE user_id = ${ctx.userId} AND status = 'confirmed' AND starts_at >= ${now} AND starts_at < ${new Date(now.getTime() + 30 * 86_400_000)}) AS month,139 (SELECT count(*)::int FROM booking_requests WHERE user_id = ${ctx.userId} AND status = 'approved' AND decided_at > now() - interval '90 days') AS approved,140 (SELECT count(*)::int FROM booking_requests WHERE user_id = ${ctx.userId} AND status = 'declined' AND decided_at > now() - interval '90 days') AS declined`;141 const [mail] = await sql<{ unconfigured: number; dead: number; retrying: number }[]>`142 SELECT count(*) FILTER (WHERE status = 'unconfigured')::int AS unconfigured, count(*) FILTER (WHERE status = 'dead')::int AS dead,143 count(*) FILTER (WHERE status = 'queued' AND attempts > 0)::int AS retrying FROM notification_jobs WHERE user_id = ${ctx.userId}`;144 const [hb] = await sql<{ age: number }[]>`SELECT extract(epoch FROM now() - beat_at)::int AS age FROM worker_heartbeat WHERE name = 'worker'`;145 const mc = mailConfigured();146 const alerts: { level: "warn" | "danger"; code: string; detail?: string }[] = [];147 const conflicts = open.filter((r) => r.conflict === "taken" || r.conflict === "past");148 if (conflicts.length) alerts.push({ level: "danger", code: "request_conflict", detail: String(conflicts.length) });149 if (!mc.ok) alerts.push({ level: "warn", code: "mail_unconfigured", detail: mc.reason });150 if (mail.unconfigured) alerts.push({ level: "warn", code: "mail_parked", detail: String(mail.unconfigured) });151 if (mail.dead) alerts.push({ level: "danger", code: "mail_dead", detail: String(mail.dead) });152 if (fresh.stale) alerts.push({ level: "warn", code: "external_stale", detail: fresh.staleNames.join(", ") });153 if (!hb || hb.age > 120) alerts.push({ level: "danger", code: "worker_down", detail: hb ? `${hb.age}s` : "never" });154 if ((await passkeyCount(ctx.userId)) < 2) alerts.push({ level: "warn", code: "single_passkey" });155 return { upcoming, open, cancelled, alerts, indicators: { next7: counts.week, next30: counts.month, approved90: counts.approved, declined90: counts.declined, openRequests: open.length } };156 }],157158 // ── Requests ──159 ["GET", "requests", async ({ ctx, url }) => {160 const f = url.searchParams.get("filter") ?? "open";161 const statuses = f === "open" ? ["pending", "reschedule_requested"] : f === "all" ? undefined : z.array(z.enum(["pending", "approved", "declined", "expired", "cancelled", "reschedule_requested"])).parse(f.split(","));162 return { requests: await listRequests(ctx.userId, { statuses }) };163 }],164 ["GET", "requests/:id", async ({ ctx, params }) => {165 const [r] = await listRequests(ctx.userId, { id: uuid.parse(params.id) });166 if (!r) throw new ApiError(404, "not_found");167 const history = await sql<{ action: string; actor: string; created_at: Date }[]>`SELECT action, actor, created_at FROM audit_events WHERE user_id = ${ctx.userId} AND entity = 'request' AND entity_id = ${r.id} ORDER BY created_at`;168 return { request: r, history: history.map((h) => ({ action: h.action, actor: h.actor, at: iso(h.created_at) })) };169 }],170 ["POST", "requests/:id/approve", async ({ ctx, params, req }) => {171 const b = await body(req, z.object({ allowOutsideHours: z.boolean().default(false) }));172 const booking = await approveRequest(ctx.userId, uuid.parse(params.id), { allowOutsideHours: b.allowOutsideHours, ip: clientIp(req) });173 return { bookingId: booking.id };174 }],175 ["POST", "requests/:id/decline", async ({ ctx, params, req }) => {176 const b = await body(req, z.object({ message: z.string().trim().max(1000).default("") }));177 await declineRequest(ctx.userId, uuid.parse(params.id), b.message, clientIp(req));178 }],179 ["POST", "requests/:id/propose", async ({ ctx, params, req }) => {180 const b = await body(req, z.object({ start: isoInstant, message: z.string().trim().max(1000).default("") }));181 await proposeTime(ctx.userId, uuid.parse(params.id), toMs(b.start), b.message, clientIp(req));182 }],183 ["POST", "requests/:id/note", async ({ ctx, params, req }) => {184 const b = await body(req, z.object({ note: z.string().trim().max(4000) }));185 await setInternalNote(ctx.userId, uuid.parse(params.id), b.note);186 }],187 // Batch: only declining is offered in bulk. Approving in bulk is not "safe": each approval must be looked at.188 ["POST", "requests/bulk-decline", async ({ ctx, req }) => {189 const b = await body(req, z.object({ ids: z.array(uuid).min(1).max(50), message: z.string().trim().max(1000).default("") }));190 const results: { id: string; ok: boolean; code?: string }[] = [];191 for (const id of b.ids) {192 try { await declineRequest(ctx.userId, id, b.message, clientIp(req)); results.push({ id, ok: true }); }193 catch (e) { results.push({ id, ok: false, code: e instanceof ApiError ? e.code : "internal" }); }194 }195 return { results };196 }],197 ["POST", "requests/:id/revoke-links", async ({ ctx, params }) => {198 const rows = await sql`UPDATE booking_tokens t SET revoked_at = now() FROM booking_requests r WHERE r.id = t.request_id AND r.id = ${uuid.parse(params.id)} AND r.user_id = ${ctx.userId} AND t.revoked_at IS NULL RETURNING t.id`;199 await audit({ userId: ctx.userId, actor: "admin", action: "links.revoked", entity: "request", entityId: params.id, meta: { count: rows.length } });200 return { revoked: rows.length };201 }],202203 // ── Agenda, bookings, private events ──204 ["GET", "agenda", async ({ ctx, url }) => {205 const { from, to } = parseRange(url);206 const s = await getSettings(ctx.userId);207 const blocks = await sql<{ id: string; starts_at: Date; ends_at: Date; title: string; note: string; source: string; calendar: string | null; blocks: boolean | null }[]>`208 SELECT b.id, b.starts_at, b.ends_at, b.title, b.note, b.source, c.name AS calendar, c.blocks_availability AS blocks209 FROM busy_blocks b LEFT JOIN external_calendars c ON c.id = b.external_calendar_id210 WHERE b.user_id = ${ctx.userId} AND b.ends_at > ${from} AND b.starts_at < ${to} ORDER BY b.starts_at`;211 const requests = (await listRequests(ctx.userId, { statuses: ["pending", "reschedule_requested"] }))212 .filter((r) => { const st = new Date(r.proposal?.startsAt ?? r.startsAt!).getTime(); return st < to.getTime() && st > from.getTime() - 86_400_000; });213 const d0 = Temporal.Instant.fromEpochMilliseconds(from.getTime()).toZonedDateTimeISO(s.timezone).toPlainDate();214 const d1 = Temporal.Instant.fromEpochMilliseconds(to.getTime() - 1).toZonedDateTimeISO(s.timezone).toPlainDate();215 const open = openWindows(s.timezone, await loadRules(ctx.userId, null), await loadExceptions(ctx.userId, null), d0, d1);216 return {217 timezone: s.timezone,218 bookings: await listBookings(ctx.userId, { from, to }),219 blocks: blocks.map((b) => ({ id: b.id, startsAt: iso(b.starts_at), endsAt: iso(b.ends_at), title: b.title, note: b.note, source: b.source, calendar: b.calendar, blocksAvailability: b.source === "manual" ? true : !!b.blocks })),220 requests,221 open: open.map((w) => ({ startsAt: new Date(w.start).toISOString(), endsAt: new Date(w.end).toISOString() })),222 };223 }],224 ["GET", "bookings", async ({ ctx, url }) => {225 const f = url.searchParams.get("filter") ?? "upcoming";226 const now = new Date(nowMs());227 if (f === "upcoming") return { bookings: await listBookings(ctx.userId, { from: now, statuses: ["confirmed"], limit: 200 }) };228 if (f === "past") return { bookings: await listBookings(ctx.userId, { to: now, statuses: ["confirmed"], limit: 200, order: "desc" }) };229 if (f === "cancelled") return { bookings: await listBookings(ctx.userId, { statuses: ["cancelled"], limit: 200, order: "desc" }) };230 throw new ApiError(400, "invalid_input");231 }],232 ["GET", "bookings/:id", async ({ ctx, params }) => {233 const [b] = await listBookings(ctx.userId, { id: uuid.parse(params.id) });234 if (!b) throw new ApiError(404, "not_found");235 const history = await sql<{ action: string; actor: string; created_at: Date }[]>`SELECT action, actor, created_at FROM audit_events WHERE user_id = ${ctx.userId} AND ((entity = 'booking' AND entity_id = ${b.id}) OR (entity = 'request' AND entity_id = ${b.requestId ?? ""})) ORDER BY created_at`;236 const mails = await sql<{ kind: string; status: string; sent_at: Date | null; last_error: string | null }[]>`SELECT kind, status, sent_at, last_error FROM notification_jobs WHERE booking_id = ${b.id} OR request_id = ${b.requestId} ORDER BY created_at`;237 return { booking: b, history: history.map((h) => ({ action: h.action, actor: h.actor, at: iso(h.created_at) })), notifications: mails.map((m) => ({ kind: m.kind, status: m.status, sentAt: iso(m.sent_at), error: m.last_error })) };238 }],239 ["POST", "bookings", async ({ ctx, req }) => {240 const b = await body(req, z.object({ eventTypeId: uuid, start: isoInstant, name: z.string().trim().min(2).max(120), email, notify: z.boolean().default(true) }));241 const booking = await createBookingByAdmin(ctx.userId, { eventTypeId: b.eventTypeId, startMs: toMs(b.start), name: b.name, email: b.email, notify: b.notify, ip: clientIp(req) });242 return { bookingId: booking.id };243 }],244 ["POST", "bookings/:id/cancel", async ({ ctx, params, req }) => {245 const b = await body(req, z.object({ reason: z.string().trim().max(500).default("") }));246 await cancelBooking(ctx.userId, uuid.parse(params.id), b.reason, clientIp(req));247 }],248 ["POST", "bookings/:id/reschedule", async ({ ctx, params, req }) => {249 const b = await body(req, z.object({ start: isoInstant }));250 await rescheduleBooking(ctx.userId, uuid.parse(params.id), toMs(b.start), clientIp(req));251 }],252 // Free slots as the engine sees them (used to propose a time or to move an appointment).253 ["GET", "slots", async ({ ctx, url }) => {254 const q = z.object({ eventTypeId: uuid, from: localDate, to: localDate, ignoreRequestId: uuid.optional(), ignoreBookingId: uuid.optional() })255 .parse(Object.fromEntries([...url.searchParams].filter(([, v]) => v !== "")));256 const et = await ownType(ctx.userId, q.eventTypeId);257 const s = await getSettings(ctx.userId);258 const f = Temporal.PlainDate.from(q.from), t = Temporal.PlainDate.from(q.to);259 if (Temporal.PlainDate.compare(t, f) < 0 || f.until(t).days > 62) throw new ApiError(400, "invalid_range");260 const st = await engineInput(et, s.timezone, f.toZonedDateTime(s.timezone).epochMilliseconds, t.add({ days: 1 }).toZonedDateTime(s.timezone).epochMilliseconds, { now: nowMs(), ignoreRequestId: q.ignoreRequestId, ignoreBookingId: q.ignoreBookingId });261 // The owner is not bound by the public notice period, the horizon or the limits.262 st.params.minNoticeMin = 0; st.params.horizonDays = 365; st.params.dailyLimit = null; st.params.weeklyLimit = null;263 return { timezone: s.timezone, durationMin: et.duration_min, slots: computeSlots({ ...st, fromDate: q.from, toDate: q.to }).map((x) => new Date(x.start).toISOString()) };264 }],265 ["POST", "busy-blocks", async ({ ctx, req }) => {266 const b = await body(req, z.object({ start: isoInstant, end: isoInstant, title: z.string().trim().max(120).default(""), note: z.string().trim().max(1000).default("") }));267 const s = toMs(b.start), e = toMs(b.end);268 if (e <= s || e - s > 366 * 86_400_000) throw new ApiError(400, "invalid_range", "End must be after start");269 const [row] = await sql<{ id: string }[]>`INSERT INTO busy_blocks (user_id, starts_at, ends_at, title, note) VALUES (${ctx.userId}, ${new Date(s)}, ${new Date(e)}, ${b.title}, ${b.note}) RETURNING id`;270 // A block never cancels anything by itself: the owner is told which confirmed appointments it now overlaps.271 const overlaps = await sql<{ id: string; title: string; starts_at: Date }[]>`SELECT id, title, starts_at FROM bookings WHERE user_id = ${ctx.userId} AND status = 'confirmed' AND starts_at < ${new Date(e)} AND ends_at > ${new Date(s)}`;272 await audit({ userId: ctx.userId, actor: "admin", action: "block.created", entity: "busy_block", entityId: row.id });273 return { id: row.id, overlaps: overlaps.map((o) => ({ id: o.id, title: o.title, startsAt: iso(o.starts_at) })) };274 }],275 ["PATCH", "busy-blocks/:id", async ({ ctx, params, req }) => {276 const b = await body(req, z.object({ start: isoInstant, end: isoInstant, title: z.string().trim().max(120), note: z.string().trim().max(1000) }));277 const s = toMs(b.start), e = toMs(b.end);278 if (e <= s) throw new ApiError(400, "invalid_range", "End must be after start");279 const rows = await sql`UPDATE busy_blocks SET starts_at = ${new Date(s)}, ends_at = ${new Date(e)}, title = ${b.title}, note = ${b.note} WHERE id = ${uuid.parse(params.id)} AND user_id = ${ctx.userId} AND source = 'manual' RETURNING id`;280 if (!rows.length) throw new ApiError(404, "not_found");281 await audit({ userId: ctx.userId, actor: "admin", action: "block.updated", entity: "busy_block", entityId: params.id });282 }],283 ["DELETE", "busy-blocks/:id", async ({ ctx, params }) => {284 const rows = await sql`DELETE FROM busy_blocks WHERE id = ${uuid.parse(params.id)} AND user_id = ${ctx.userId} AND source = 'manual' RETURNING id`;285 if (!rows.length) throw new ApiError(404, "not_found");286 await audit({ userId: ctx.userId, actor: "admin", action: "block.deleted", entity: "busy_block", entityId: params.id });287 }],288289 // ── Availability ──290 ["GET", "availability", async ({ ctx }) => {291 const rules = await sql<{ event_type_id: string | null; weekday: number; start_minute: number; end_minute: number }[]>`SELECT event_type_id, weekday, start_minute, end_minute FROM availability_rules WHERE user_id = ${ctx.userId} ORDER BY weekday, start_minute`;292 const ex = await sql<Parameters<typeof exceptionRow>[0][]>`SELECT id, event_type_id, kind, mode, starts_local::text, ends_local::text, all_day, weekdays, start_minute, duration_min, valid_from::text, valid_until::text, note293 FROM availability_exceptions WHERE user_id = ${ctx.userId} ORDER BY COALESCE(starts_local, valid_from::timestamp)`;294 return { timezone: (await getSettings(ctx.userId)).timezone, rules: rules.map((r) => ({ eventTypeId: r.event_type_id, weekday: r.weekday, startMinute: r.start_minute, endMinute: r.end_minute })), exceptions: ex.map(exceptionRow) };295 }],296 ["PUT", "availability/rules", async ({ ctx, req }) => {297 const b = await body(req, rulesInput);298 if (b.eventTypeId) await ownType(ctx.userId, b.eventTypeId);299 for (const day of [1, 2, 3, 4, 5, 6, 7]) {300 const d = b.rules.filter((r) => r.weekday === day).sort((x, y) => x.startMinute - y.startMinute);301 for (let k = 1; k < d.length; k++) if (d[k].startMinute < d[k - 1].endMinute) throw new ApiError(400, "invalid_input", "Time ranges of the same day overlap");302 }303 await sql.begin(async (tx) => {304 if (b.eventTypeId) await tx`DELETE FROM availability_rules WHERE user_id = ${ctx.userId} AND event_type_id = ${b.eventTypeId}`;305 else await tx`DELETE FROM availability_rules WHERE user_id = ${ctx.userId} AND event_type_id IS NULL`;306 for (const r of b.rules) await tx`INSERT INTO availability_rules (user_id, event_type_id, weekday, start_minute, end_minute) VALUES (${ctx.userId}, ${b.eventTypeId}, ${r.weekday}, ${r.startMinute}, ${r.endMinute})`;307 await audit({ userId: ctx.userId, actor: "admin", action: "availability.rules_saved", meta: { eventTypeId: b.eventTypeId, count: b.rules.length } }, tx);308 });309 }],310 ["POST", "availability/exceptions", async ({ ctx, req }) => {311 const b = await body(req, exceptionInput);312 if (b.eventTypeId) await ownType(ctx.userId, b.eventTypeId);313 const [row] = b.mode === "range"314 ? await sql<{ id: string }[]>`INSERT INTO availability_exceptions (user_id, event_type_id, kind, mode, starts_local, ends_local, all_day, note) VALUES (${ctx.userId}, ${b.eventTypeId}, ${b.kind}, 'range', ${b.startsLocal}::timestamp, ${b.endsLocal}::timestamp, ${b.allDay}, ${b.note}) RETURNING id`315 : await sql<{ id: string }[]>`INSERT INTO availability_exceptions (user_id, event_type_id, kind, mode, weekdays, start_minute, duration_min, valid_from, valid_until, note) VALUES (${ctx.userId}, ${b.eventTypeId}, ${b.kind}, 'weekly', ${b.weekdays}, ${b.startMinute}, ${b.durationMin}, ${b.validFrom}::date, ${b.validUntil}::date, ${b.note}) RETURNING id`;316 await audit({ userId: ctx.userId, actor: "admin", action: "availability.exception_created", entity: "exception", entityId: row.id, meta: { kind: b.kind, mode: b.mode } });317 return { id: row.id };318 }],319 ["DELETE", "availability/exceptions/:id", async ({ ctx, params }) => {320 const rows = await sql`DELETE FROM availability_exceptions WHERE id = ${uuid.parse(params.id)} AND user_id = ${ctx.userId} RETURNING id`;321 if (!rows.length) throw new ApiError(404, "not_found");322 await audit({ userId: ctx.userId, actor: "admin", action: "availability.exception_deleted", entity: "exception", entityId: params.id });323 }],324325 // ── Appointment types ──326 ["GET", "event-types", async ({ ctx }) => ({ types: await sql`SELECT * FROM event_types WHERE user_id = ${ctx.userId} ORDER BY position, created_at` })],327 ["POST", "event-types", async ({ ctx, req }) => {328 const b = await body(req, eventTypeInput);329 const taken = await sql`SELECT 1 FROM event_types WHERE slug = ${b.slug}`;330 if (taken.length) throw new ApiError(409, "slug_taken", "This link name is already used");331 const [row] = await sql<{ id: string }[]>`INSERT INTO event_types ${sql({ ...b, questions: j(b.questions), user_id: ctx.userId } as never)} RETURNING id`;332 await audit({ userId: ctx.userId, actor: "admin", action: "event_type.created", entity: "event_type", entityId: row.id });333 return { id: row.id };334 }],335 ["PATCH", "event-types/:id", async ({ ctx, params, req }) => {336 const et = await ownType(ctx.userId, params.id);337 const b = await body(req, eventTypeInput);338 if (b.slug !== et.slug && (await sql`SELECT 1 FROM event_types WHERE slug = ${b.slug}`).length) throw new ApiError(409, "slug_taken", "This link name is already used");339 await sql`UPDATE event_types SET ${sql({ ...b, questions: j(b.questions) } as never)}, updated_at = now() WHERE id = ${et.id}`;340 await audit({ userId: ctx.userId, actor: "admin", action: "event_type.updated", entity: "event_type", entityId: et.id });341 }],342 ["DELETE", "event-types/:id", async ({ ctx, params }) => {343 const et = await ownType(ctx.userId, params.id);344 const live = await sql`SELECT 1 FROM bookings WHERE event_type_id = ${et.id} AND status = 'confirmed' AND ends_at > now() UNION ALL SELECT 1 FROM booking_requests WHERE event_type_id = ${et.id} AND status IN ('pending', 'reschedule_requested') LIMIT 1`;345 if (live.length) throw new ApiError(409, "in_use", "This type still has open requests or upcoming appointments; deactivate it instead");346 await sql`DELETE FROM event_types WHERE id = ${et.id}`;347 await audit({ userId: ctx.userId, actor: "admin", action: "event_type.deleted", entity: "event_type", entityId: et.id });348 }],349350 // ── Contacts ──351 ["GET", "contacts", async ({ ctx, url }) => {352 const q = (url.searchParams.get("q") ?? "").trim().slice(0, 100);353 const rows = await sql<{ id: string; name: string; email: string; organization: string; created_at: Date; bookings: number; requests: number; last_at: Date | null }[]>`354 SELECT c.id, c.name, c.email::text, c.organization, c.created_at,355 (SELECT count(*)::int FROM bookings b WHERE b.contact_id = c.id AND b.status = 'confirmed') AS bookings,356 (SELECT count(*)::int FROM booking_requests r WHERE r.contact_id = c.id) AS requests,357 (SELECT max(starts_at) FROM booking_requests r WHERE r.contact_id = c.id) AS last_at358 FROM contacts c WHERE c.user_id = ${ctx.userId} AND (${q} = '' OR c.name ILIKE ${"%" + q + "%"} OR c.email::text ILIKE ${"%" + q + "%"} OR c.organization ILIKE ${"%" + q + "%"})359 ORDER BY c.updated_at DESC LIMIT 300`;360 return { contacts: rows.map((c) => ({ id: c.id, name: c.name, email: c.email, organization: c.organization, createdAt: iso(c.created_at), bookings: c.bookings, requests: c.requests, lastAt: iso(c.last_at) })) };361 }],362 ["GET", "contacts/:id", async ({ ctx, params, url }) => {363 const id = uuid.parse(params.id);364 const [c] = await sql<{ id: string; name: string; email: string; organization: string; locale: string; notes: string; created_at: Date }[]>`SELECT id, name, email::text, organization, locale, notes, created_at FROM contacts WHERE id = ${id} AND user_id = ${ctx.userId}`;365 if (!c) throw new ApiError(404, "not_found");366 const data = { contact: { ...c, created_at: undefined, createdAt: iso(c.created_at) }, requests: await listRequests(ctx.userId, { contactId: id }), bookings: await listBookings(ctx.userId, { contactId: id, order: "desc" }) };367 if (url.searchParams.get("export") === "1") {368 await audit({ userId: ctx.userId, actor: "admin", action: "contact.exported", entity: "contact", entityId: id });369 return new Response(JSON.stringify({ exportedAt: new Date().toISOString(), ...data }, null, 2), { headers: { "Content-Type": "application/json; charset=utf-8", "Content-Disposition": `attachment; filename="contact-${id}.json"`, "Cache-Control": "no-store" } });370 }371 return data;372 }],373 ["PATCH", "contacts/:id", async ({ ctx, params, req }) => {374 const b = await body(req, z.object({ name: z.string().trim().min(1).max(120), organization: z.string().trim().max(160), notes: z.string().trim().max(4000) }));375 const rows = await sql`UPDATE contacts SET name = ${b.name}, organization = ${b.organization}, notes = ${b.notes}, updated_at = now() WHERE id = ${uuid.parse(params.id)} AND user_id = ${ctx.userId} RETURNING id`;376 if (!rows.length) throw new ApiError(404, "not_found");377 }],378 ["DELETE", "contacts/:id", async ({ ctx, params }) => {379 try { await eraseContact(ctx.userId, uuid.parse(params.id)); }380 catch (e) {381 const st = (e as { status?: number }).status;382 if (st === 404) throw new ApiError(404, "not_found");383 if (st === 409) throw new ApiError(409, "future_booking", "Cancel this person's upcoming appointments first");384 throw e;385 }386 }],387388 // ── Settings ──389 ["GET", "settings", async ({ ctx }) => {390 const s = await getSettings(ctx.userId);391 const mc = mailConfigured();392 return { settings: s, mail: { configured: mc.ok, transport: mc.transport, reason: mc.reason ?? null, from: process.env.MAIL_FROM ?? null } };393 }],394 ["PUT", "settings", async ({ ctx, req }) => {395 const b = await body(req, settingsInput);396 await sql`UPDATE settings SET ${sql(b as never)}, updated_at = now() WHERE user_id = ${ctx.userId}`;397 await audit({ userId: ctx.userId, actor: "admin", action: "settings.updated" });398 }],399 ["PUT", "settings/avatar", async ({ ctx, req }) => {400 const type = (req.headers.get("content-type") ?? "").split(";")[0];401 if (!["image/png", "image/jpeg", "image/webp"].includes(type)) throw new ApiError(400, "invalid_input", "PNG, JPEG or WebP only");402 const buf = Buffer.from(await req.arrayBuffer());403 if (buf.byteLength === 0 || buf.byteLength > 1_000_000) throw new ApiError(400, "invalid_input", "Image must be under 1 MB");404 const magic = type === "image/png" ? buf.subarray(0, 4).toString("hex") === "89504e47" : type === "image/jpeg" ? buf.subarray(0, 2).toString("hex") === "ffd8" : buf.subarray(8, 12).toString() === "WEBP";405 if (!magic) throw new ApiError(400, "invalid_input", "The file is not a valid image");406 await sql`UPDATE settings SET avatar = ${buf}, avatar_mime = ${type}, updated_at = now() WHERE user_id = ${ctx.userId}`;407 await audit({ userId: ctx.userId, actor: "admin", action: "settings.avatar_updated" });408 }],409 ["DELETE", "settings/avatar", async ({ ctx }) => { await sql`UPDATE settings SET avatar = NULL, avatar_mime = NULL WHERE user_id = ${ctx.userId}`; }],410411 // ── Integrations ──412 ["GET", "calendars", async ({ ctx }) => {413 const s = await getSettings(ctx.userId);414 const rows = await sql<{ id: string; name: string; provider: string; status: string; blocks_availability: boolean; last_success_at: Date | null; last_attempt_at: Date | null; last_error: string | null; events_count: number | null }[]>`415 SELECT c.id, c.name, a.provider, a.status, c.blocks_availability, s.last_success_at, s.last_attempt_at, s.last_error, s.events_count416 FROM external_calendars c JOIN external_calendar_accounts a ON a.id = c.account_id LEFT JOIN sync_state s ON s.calendar_id = c.id WHERE c.user_id = ${ctx.userId} ORDER BY c.created_at`;417 const limit = Date.now() - s.external_stale_minutes * 60_000;418 return {419 calendars: rows.map((c) => ({ id: c.id, name: c.name, provider: c.provider, status: c.status, blocksAvailability: c.blocks_availability, lastSuccessAt: iso(c.last_success_at), lastAttemptAt: iso(c.last_attempt_at), lastError: c.last_error, events: c.events_count ?? 0, stale: !c.last_success_at || c.last_success_at.getTime() < limit })),420 feedEnabled: s.has_feed, staleMinutes: s.external_stale_minutes, stalePolicy: s.external_stale_policy,421 connectors: { ics: "available", google: "not_implemented", microsoft: "not_implemented", caldav: "not_implemented" },422 };423 }],424 ["POST", "calendars", async ({ ctx, req }) => {425 const b = await body(req, z.object({ label: z.string().trim().min(1).max(80), url: z.string().trim().min(8).max(2000), blocksAvailability: z.boolean().default(true) }));426 const id = await addIcsCalendar(ctx.userId, b);427 const result = await syncCalendar(id);428 return { id, sync: result };429 }],430 ["PATCH", "calendars/:id", async ({ ctx, params, req }) => {431 const b = await body(req, z.object({ name: z.string().trim().min(1).max(80), blocksAvailability: z.boolean() }));432 const rows = await sql`UPDATE external_calendars SET name = ${b.name}, blocks_availability = ${b.blocksAvailability} WHERE id = ${uuid.parse(params.id)} AND user_id = ${ctx.userId} RETURNING id`;433 if (!rows.length) throw new ApiError(404, "not_found");434 }],435 ["POST", "calendars/:id/sync", async ({ ctx, params }) => {436 const own = await sql`SELECT 1 FROM external_calendars WHERE id = ${uuid.parse(params.id)} AND user_id = ${ctx.userId}`;437 if (!own.length) throw new ApiError(404, "not_found");438 return { sync: await syncCalendar(params.id) };439 }],440 ["DELETE", "calendars/:id", async ({ ctx, params }) => { await removeCalendar(ctx.userId, uuid.parse(params.id)); }],441 ["POST", "feed", async ({ ctx }) => {442 const token = randomToken(32);443 await sql`UPDATE settings SET feed_token_hash = ${sha256(token)} WHERE user_id = ${ctx.userId}`;444 await audit({ userId: ctx.userId, actor: "admin", action: "feed.rotated" });445 return { token }; // shown once446 }],447 ["DELETE", "feed", async ({ ctx }) => { await sql`UPDATE settings SET feed_token_hash = NULL WHERE user_id = ${ctx.userId}`; await audit({ userId: ctx.userId, actor: "admin", action: "feed.disabled" }); }],448449 // ── Notifications log ──450 ["GET", "notifications", async ({ ctx }) => {451 const rows = await sql<{ id: string; kind: string; status: string; to_email: string; attempts: number; run_at: Date; sent_at: Date | null; last_error: string | null; transport: string | null; provider_message_id: string | null; created_at: Date }[]>`452 SELECT id, kind, status, to_email::text, attempts, run_at, sent_at, last_error, transport, provider_message_id, created_at FROM notification_jobs WHERE user_id = ${ctx.userId} ORDER BY created_at DESC LIMIT 200`;453 const mc = mailConfigured();454 return { mail: { configured: mc.ok, transport: mc.transport, reason: mc.reason ?? null }, jobs: rows.map((n) => ({ id: n.id, kind: n.kind, status: n.status, to: n.to_email, attempts: n.attempts, runAt: iso(n.run_at), sentAt: iso(n.sent_at), error: n.last_error, transport: n.transport, messageId: n.provider_message_id, createdAt: iso(n.created_at) })) };455 }],456 ["POST", "notifications/requeue", async ({ ctx, req }) => {457 const b = await body(req, z.object({ ids: z.array(uuid).max(200).optional() }));458 return { requeued: await requeue(ctx.userId, b.ids) };459 }],460 ["POST", "notifications/test", async ({ ctx, req }) => {461 const b = await body(req, z.object({ to: email }));462 const s = await getSettings(ctx.userId);463 await enqueue(sql, { userId: ctx.userId, kind: "test", dedupKey: `test:${Date.now()}`, to: b.to, locale: s.locale });464 return { queued: true }; // the result (sent / failed / unconfigured) appears in the log, from the transport465 }],466467 // ── Security ──468 ["GET", "security", async ({ ctx }) => {469 const sessions = await sql<{ id: string; method: string; user_agent: string | null; ip: string | null; created_at: Date; last_seen_at: Date; expires_at: Date }[]>`SELECT id, method, user_agent, ip, created_at, last_seen_at, expires_at FROM sessions WHERE user_id = ${ctx.userId} AND revoked_at IS NULL AND expires_at > now() ORDER BY last_seen_at DESC`;470 const keys = await sql<{ id: string; name: string; device_type: string | null; backed_up: boolean; created_at: Date; last_used_at: Date | null }[]>`SELECT id, name, device_type, backed_up, created_at, last_used_at FROM authenticators WHERE user_id = ${ctx.userId} ORDER BY created_at`;471 const [codes] = await sql<{ n: number }[]>`SELECT count(*)::int AS n FROM recovery_codes WHERE user_id = ${ctx.userId} AND used_at IS NULL`;472 return {473 sessions: sessions.map((s) => ({ id: s.id, current: s.id === ctx.sessionId, method: s.method, userAgent: s.user_agent, ip: s.ip, createdAt: iso(s.created_at), lastSeenAt: iso(s.last_seen_at), expiresAt: iso(s.expires_at) })),474 passkeys: keys.map((k) => ({ id: k.id, name: k.name, deviceType: k.device_type, backedUp: k.backed_up, createdAt: iso(k.created_at), lastUsedAt: iso(k.last_used_at) })),475 recoveryCodesLeft: codes.n,476 };477 }],478 ["DELETE", "security/sessions/:id", async ({ ctx, params }) => { await revokeSession(uuid.parse(params.id), ctx.userId); await audit({ userId: ctx.userId, actor: "admin", action: "session.revoked" }); }],479 ["POST", "security/sessions/revoke-others", async ({ ctx }) => {480 const rows = await sql`UPDATE sessions SET revoked_at = now() WHERE user_id = ${ctx.userId} AND id <> ${ctx.sessionId} AND revoked_at IS NULL RETURNING id`;481 await audit({ userId: ctx.userId, actor: "admin", action: "session.revoked_others", meta: { count: rows.length } });482 return { revoked: rows.length };483 }],484 ["DELETE", "security/passkeys/:id", async ({ ctx, params }) => {485 if ((await passkeyCount(ctx.userId)) <= 1) throw new ApiError(409, "last_passkey", "The last passkey cannot be removed");486 const rows = await sql`DELETE FROM authenticators WHERE id = ${uuid.parse(params.id)} AND user_id = ${ctx.userId} RETURNING id`;487 if (!rows.length) throw new ApiError(404, "not_found");488 await audit({ userId: ctx.userId, actor: "admin", action: "passkey.removed" });489 }],490 ["POST", "security/recovery-codes", async ({ ctx }) => {491 const { randomBytes } = await import("node:crypto");492 const codes = Array.from({ length: 8 }, () => randomBytes(6).toString("hex").replace(/(.{4})(.{4})(.{4})/, "$1-$2-$3"));493 await sql.begin(async (tx) => {494 await tx`DELETE FROM recovery_codes WHERE user_id = ${ctx.userId}`;495 for (const c of codes) await tx`INSERT INTO recovery_codes (user_id, code_hash) VALUES (${ctx.userId}, ${sha256(c)})`;496 });497 await audit({ userId: ctx.userId, actor: "admin", action: "recovery_codes.regenerated" });498 return { codes }; // shown once499 }],500 ["GET", "audit", async ({ ctx, url }) => {501 const before = url.searchParams.get("before");502 const rows = await sql<{ id: string; actor: string; action: string; entity: string; entity_id: string; meta: unknown; ip: string | null; created_at: Date }[]>`503 SELECT id::text, actor, action, entity, entity_id, meta, ip, created_at FROM audit_events WHERE user_id = ${ctx.userId} AND (${before}::bigint IS NULL OR id < ${before}::bigint) ORDER BY id DESC LIMIT 100`;504 return { events: rows.map((e) => ({ id: e.id, actor: e.actor, action: e.action, entity: e.entity, entityId: e.entity_id, meta: e.meta, ip: e.ip, at: iso(e.created_at) })) };505 }],506];507