Repository Public
1import { randomUUID } from "node:crypto";2import { sql, withOwnerLock, type Tx } from "../db";3import { ApiError } from "../http";4import { nowMs } from "../env";5import { manageTokenFor, sha256 } from "../crypto";6import { audit } from "../audit";7import { checkSlot, type SlotRefusal } from "../engine/slots";8import { engineInput, externalFreshness, getEventType, getSettings, type EventTypeRow, type SettingsRow } from "./state";9import { enqueue, ownerEmail } from "./notify";1011const MIN = 60_000;12const TOKEN_GRACE_DAYS = 30;1314export interface RequestRow {15 id: string; user_id: string; event_type_id: string; contact_id: string | null; starts_at: Date; ends_at: Date; visitor_tz: string; locale: "fr" | "en";16 status: "pending" | "approved" | "declined" | "expired" | "cancelled" | "reschedule_requested"; hold_expires_at: Date | null; idempotency_key: string;17 reason: string; internal_note: string; decline_message: string; proposed_starts_at: Date | null; proposed_ends_at: Date | null; proposal_expires_at: Date | null;18 proposal_message: string; reschedule_of_booking_id: string | null; booking_id: string | null; conflict_flag: string; created_at: Date; decided_at: Date | null;19}20export interface BookingRow {21 id: string; user_id: string; event_type_id: string | null; contact_id: string | null; title: string; starts_at: Date; ends_at: Date;22 buffer_before_min: number; buffer_after_min: number; status: "confirmed" | "cancelled"; location_kind: string; location_text: string;23 visitor_tz: string; locale: "fr" | "en"; ics_uid: string; ics_sequence: number; cancelled_at: Date | null; cancelled_by: string | null; cancel_reason: string;24}2526const refusalError = (r: SlotRefusal): ApiError => {27 switch (r) {28 case "past": return new ApiError(409, "slot_past", "This time is in the past");29 case "taken": return new ApiError(409, "slot_taken", "This time is no longer available");30 case "limit": return new ApiError(409, "slot_limit", "The limit for this day or week is reached");31 case "closed": return new ApiError(409, "outside_hours", "This time is outside opening hours");32 default: return new ApiError(409, "slot_unavailable", "This time is not available");33 }34};3536/** The exclusion constraint fired: two confirmed bookings would overlap. Never swallowed, always a 409. */37function mapOverlap(e: unknown): never {38 if (typeof e === "object" && e !== null && (e as { code?: string }).code === "23P01") throw new ApiError(409, "slot_taken", "This time is no longer available");39 throw e;40}4142async function upsertContact(tx: Tx, userId: string, c: { email: string; name: string; organization?: string; locale: string }): Promise<string> {43 const [row] = await tx<{ id: string }[]>`44 INSERT INTO contacts (user_id, email, name, organization, locale) VALUES (${userId}, ${c.email}, ${c.name}, ${c.organization ?? ""}, ${c.locale})45 ON CONFLICT (user_id, email) DO UPDATE SET name = EXCLUDED.name, locale = EXCLUDED.locale, updated_at = now(),46 organization = CASE WHEN EXCLUDED.organization <> '' THEN EXCLUDED.organization ELSE contacts.organization END47 RETURNING id`;48 return row.id;49}5051async function mintToken(tx: Tx, requestId: string, endsAt: Date): Promise<string> {52 const id = randomUUID();53 const raw = manageTokenFor(id);54 await tx`INSERT INTO booking_tokens (id, request_id, token_hash, expires_at) VALUES (${id}, ${requestId}, ${sha256(raw)}, ${new Date(endsAt.getTime() + TOKEN_GRACE_DAYS * 86_400_000)})`;55 return raw;56}5758async function insertBooking(tx: Tx, r: { userId: string; et: EventTypeRow; contactId: string | null; contactName: string; start: Date; end: Date; visitorTz: string; locale: string }): Promise<BookingRow> {59 const id = randomUUID();60 const [b] = await tx<BookingRow[]>`61 INSERT INTO bookings (id, user_id, event_type_id, contact_id, title, starts_at, ends_at, buffer_before_min, buffer_after_min, location_kind, location_text, visitor_tz, locale, ics_uid)62 VALUES (${id}, ${r.userId}, ${r.et.id}, ${r.contactId}, ${`${r.et.title} — ${r.contactName}`}, ${r.start}, ${r.end}, ${r.et.buffer_before_min}, ${r.et.buffer_after_min},63 ${r.et.location_kind}, ${r.et.location_detail}, ${r.visitorTz}, ${r.locale}, ${`${id}@sp-calendar`})64 RETURNING *`;65 return b;66}6768async function scheduleReminder(tx: Tx, b: BookingRow, et: EventTypeRow, contactEmail: string | null, requestId: string | null) {69 if (!et.reminder_hours || !contactEmail) return;70 const runAt = new Date(b.starts_at.getTime() - et.reminder_hours * 3_600_000);71 if (runAt.getTime() <= nowMs()) return;72 await enqueue(tx, { userId: b.user_id, kind: "reminder", dedupKey: `reminder:${b.id}:${b.ics_sequence}`, to: contactEmail, locale: b.locale, bookingId: b.id, requestId, runAt, payload: { sequence: b.ics_sequence } });73}7475const contactOf = async (tx: Tx, id: string | null) => id ? (await tx<{ id: string; email: string; name: string }[]>`SELECT id, email, name FROM contacts WHERE id = ${id}`)[0] ?? null : null;7677// ── Visitor: submit ─────────────────────────────────────────────────────────────────────────────────7879export interface SubmitInput {80 slug: string; startMs: number; name: string; email: string; organization?: string; reason?: string; answers?: Record<string, string>;81 visitorTz: string; locale: "fr" | "en"; idempotencyKey: string; ip?: string;82}83export interface SubmitResult { requestId: string; status: "pending" | "approved"; manageToken: string; startsAt: string; endsAt: string; replay: boolean }8485function domainAllowed(email: string, s: SettingsRow): boolean {86 const domain = email.split("@")[1]?.toLowerCase() ?? "";87 const match = (list: string[]) => list.some((d) => domain === d.toLowerCase() || domain.endsWith("." + d.toLowerCase()));88 if (match(s.blocked_email_domains)) return false;89 return s.allowed_email_domains.length === 0 || match(s.allowed_email_domains);90}9192export async function submitRequest(input: SubmitInput): Promise<SubmitResult> {93 const et = await getEventType({ slug: input.slug });94 if (!et || !et.active) throw new ApiError(404, "not_found", "This appointment type does not exist");95 const settings = await getSettings(et.user_id);96 if (!domainAllowed(input.email, settings)) throw new ApiError(403, "email_domain_not_allowed", "This e-mail domain cannot be used to book");9798 for (const q of et.questions) if (q.required && !(input.answers?.[q.key] ?? "").trim()) throw new ApiError(400, "invalid_input", `Missing answer: ${q.key}`);99 if (et.require_reason && !(input.reason ?? "").trim()) throw new ApiError(400, "invalid_input", "Missing reason");100101 try {102 return await withOwnerLock(et.user_id, async (tx) => {103 // Idempotency: the same key always returns the first outcome, never a second request.104 const [dup] = await tx<RequestRow[]>`SELECT * FROM booking_requests WHERE idempotency_key = ${input.idempotencyKey}`;105 if (dup) {106 const [t] = await tx<{ id: string }[]>`SELECT id FROM booking_tokens WHERE request_id = ${dup.id} AND revoked_at IS NULL ORDER BY created_at LIMIT 1`;107 return { requestId: dup.id, status: dup.status === "approved" ? "approved" as const : "pending" as const, manageToken: t ? manageTokenFor(t.id) : "", startsAt: dup.starts_at.toISOString(), endsAt: dup.ends_at.toISOString(), replay: true };108 }109 const now = nowMs();110 const start = input.startMs, end = start + et.duration_min * MIN;111 const state = await engineInput(et, settings.timezone, start, end, { now }, tx);112 const refusal = checkSlot(state, start);113 if (refusal) throw refusalError(refusal === "closed" || refusal === "misaligned" || refusal === "too_soon" || refusal === "too_far" ? "misaligned" : refusal);114115 const contactId = await upsertContact(tx, et.user_id, { email: input.email, name: input.name, organization: input.organization, locale: input.locale });116 const [{ n: open }] = await tx<{ n: number }[]>`SELECT count(*)::int AS n FROM booking_requests WHERE contact_id = ${contactId} AND status IN ('pending', 'reschedule_requested') AND starts_at > ${new Date(now)}`;117 if (open >= 3) throw new ApiError(429, "too_many_pending", "You already have several requests awaiting an answer");118119 const fresh = await externalFreshness(et.user_id, settings, tx);120 const withheld = et.approval === "auto" && fresh.stale && settings.external_stale_policy === "conservative";121 const auto = et.approval === "auto" && !withheld;122 const hold = !auto && et.hold_policy === "hold" ? new Date(Math.min(now + et.hold_ttl_hours * 3_600_000, start)) : null;123124 const [r] = await tx<RequestRow[]>`125 INSERT INTO booking_requests (user_id, event_type_id, contact_id, starts_at, ends_at, visitor_tz, locale, status, hold_expires_at, idempotency_key, reason, conflict_flag, consent_at, ip)126 VALUES (${et.user_id}, ${et.id}, ${contactId}, ${new Date(start)}, ${new Date(end)}, ${input.visitorTz}, ${input.locale}, 'pending', ${hold}, ${input.idempotencyKey},127 ${(input.reason ?? "").trim()}, ${withheld ? "external_stale" : ""}, ${new Date(now)}, ${input.ip ?? null})128 RETURNING *`;129 for (const q of et.questions) {130 const v = (input.answers?.[q.key] ?? "").trim();131 if (v) await tx`INSERT INTO booking_answers (request_id, question_key, label, value) VALUES (${r.id}, ${q.key}, ${q.label}, ${v})`;132 }133 const manageToken = await mintToken(tx, r.id, r.ends_at);134 const owner = await ownerEmail(tx, et.user_id);135136 if (auto) {137 const b = await insertBooking(tx, { userId: et.user_id, et, contactId, contactName: input.name, start: r.starts_at, end: r.ends_at, visitorTz: input.visitorTz, locale: input.locale });138 await tx`UPDATE booking_requests SET status = 'approved', booking_id = ${b.id}, decided_at = now(), updated_at = now() WHERE id = ${r.id}`;139 await enqueue(tx, { userId: et.user_id, kind: "booking_confirmed", dedupKey: `confirmed:${b.id}:0`, to: input.email, locale: input.locale, requestId: r.id, bookingId: b.id });140 if (owner) await enqueue(tx, { userId: et.user_id, kind: "admin_new_booking", dedupKey: `admin_new_booking:${b.id}`, to: owner, locale: settings.locale, requestId: r.id, bookingId: b.id });141 await scheduleReminder(tx, b, et, input.email, r.id);142 await audit({ userId: et.user_id, actor: "visitor", action: "booking.auto_confirmed", entity: "booking", entityId: b.id, ip: input.ip }, tx);143 return { requestId: r.id, status: "approved" as const, manageToken, startsAt: r.starts_at.toISOString(), endsAt: r.ends_at.toISOString(), replay: false };144 }145 await enqueue(tx, { userId: et.user_id, kind: "request_received", dedupKey: `received:${r.id}`, to: input.email, locale: input.locale, requestId: r.id });146 if (owner) await enqueue(tx, { userId: et.user_id, kind: "admin_new_request", dedupKey: `admin_new_request:${r.id}`, to: owner, locale: settings.locale, requestId: r.id });147 await audit({ userId: et.user_id, actor: "visitor", action: "request.submitted", entity: "request", entityId: r.id, meta: { hold: !!hold, withheld }, ip: input.ip }, tx);148 return { requestId: r.id, status: "pending" as const, manageToken, startsAt: r.starts_at.toISOString(), endsAt: r.ends_at.toISOString(), replay: false };149 });150 } catch (e) { mapOverlap(e); }151}152153// ── Admin: approve / decline / propose ──────────────────────────────────────────────────────────────154155async function lockRequest(tx: Tx, userId: string, id: string): Promise<RequestRow> {156 const [r] = await tx<RequestRow[]>`SELECT * FROM booking_requests WHERE id = ${id} AND user_id = ${userId} FOR UPDATE`;157 if (!r) throw new ApiError(404, "not_found", "Request not found");158 return r;159}160161/**162 * Turns a request into a confirmed booking (or moves the booking it asks to reschedule) at the given time.163 * Runs under the owner lock; the slot is re-checked against everything taken at this very moment.164 */165async function confirmAt(tx: Tx, r: RequestRow, start: Date, end: Date, opts: { allowOutsideHours: boolean; actor: "admin" | "visitor" }): Promise<BookingRow> {166 const et = await getEventType({ id: r.event_type_id }, tx);167 if (!et) throw new ApiError(409, "invalid_state", "The appointment type no longer exists");168 const settings = await getSettings(r.user_id, tx);169 const state = await engineInput(et, settings.timezone, start.getTime(), end.getTime(), { now: nowMs(), ignoreRequestId: r.id, ignoreBookingId: r.reschedule_of_booking_id }, tx);170 state.params.durationMin = Math.round((end.getTime() - start.getTime()) / MIN);171 const refusal = checkSlot(state, start.getTime(), { enforceNotice: false, enforceLimits: false, allowOutsideHours: opts.allowOutsideHours });172 if (refusal) throw refusalError(refusal);173 const contact = await contactOf(tx, r.contact_id);174175 let b: BookingRow;176 if (r.reschedule_of_booking_id) {177 const [moved] = await tx<BookingRow[]>`178 UPDATE bookings SET starts_at = ${start}, ends_at = ${end}, ics_sequence = ics_sequence + 1, updated_at = now(), reminder_job_done = false179 WHERE id = ${r.reschedule_of_booking_id} AND status = 'confirmed' RETURNING *`;180 if (!moved) throw new ApiError(409, "invalid_state", "The appointment to move is no longer confirmed");181 b = moved;182 await tx`UPDATE booking_requests SET starts_at = ${start}, ends_at = ${end}, updated_at = now() WHERE booking_id = ${b.id} AND id <> ${r.id}`;183 if (contact) await enqueue(tx, { userId: r.user_id, kind: "booking_rescheduled", dedupKey: `rescheduled:${b.id}:${b.ics_sequence}`, to: contact.email, locale: r.locale, requestId: r.id, bookingId: b.id });184 } else {185 b = await insertBooking(tx, { userId: r.user_id, et, contactId: r.contact_id, contactName: contact?.name ?? "", start, end, visitorTz: r.visitor_tz, locale: r.locale });186 if (contact) await enqueue(tx, { userId: r.user_id, kind: "booking_confirmed", dedupKey: `confirmed:${b.id}:0`, to: contact.email, locale: r.locale, requestId: r.id, bookingId: b.id });187 }188 await tx`UPDATE booking_requests SET status = 'approved', booking_id = ${b.id}, starts_at = ${start}, ends_at = ${end}, hold_expires_at = NULL,189 proposed_starts_at = NULL, proposed_ends_at = NULL, proposal_expires_at = NULL, decided_at = now(), updated_at = now() WHERE id = ${r.id}`;190 await scheduleReminder(tx, b, et, contact?.email ?? null, r.id);191 return b;192}193194export async function approveRequest(userId: string, requestId: string, opts: { allowOutsideHours?: boolean; ip?: string } = {}): Promise<BookingRow> {195 try {196 return await withOwnerLock(userId, async (tx) => {197 const r = await lockRequest(tx, userId, requestId);198 if (r.status !== "pending") throw new ApiError(409, "invalid_state", "Only a pending request can be approved");199 const b = await confirmAt(tx, r, r.starts_at, r.ends_at, { allowOutsideHours: !!opts.allowOutsideHours, actor: "admin" });200 await audit({ userId, actor: "admin", action: "request.approved", entity: "request", entityId: r.id, meta: { bookingId: b.id }, ip: opts.ip }, tx);201 return b;202 });203 } catch (e) { mapOverlap(e); }204}205206export async function declineRequest(userId: string, requestId: string, message: string, ip?: string): Promise<void> {207 await withOwnerLock(userId, async (tx) => {208 const r = await lockRequest(tx, userId, requestId);209 if (r.status !== "pending" && r.status !== "reschedule_requested") throw new ApiError(409, "invalid_state", "This request was already answered");210 await tx`UPDATE booking_requests SET status = 'declined', decline_message = ${message}, hold_expires_at = NULL, proposal_expires_at = NULL, decided_at = now(), updated_at = now() WHERE id = ${r.id}`;211 const contact = await contactOf(tx, r.contact_id);212 if (contact) await enqueue(tx, { userId, kind: r.reschedule_of_booking_id ? "reschedule_declined" : "request_declined", dedupKey: `declined:${r.id}`, to: contact.email, locale: r.locale, requestId: r.id, bookingId: r.reschedule_of_booking_id });213 await audit({ userId, actor: "admin", action: "request.declined", entity: "request", entityId: r.id, ip }, tx);214 });215}216217export async function proposeTime(userId: string, requestId: string, startMs: number, message: string, ip?: string): Promise<void> {218 await withOwnerLock(userId, async (tx) => {219 const r = await lockRequest(tx, userId, requestId);220 if (r.status !== "pending" && r.status !== "reschedule_requested") throw new ApiError(409, "invalid_state", "This request was already answered");221 const et = await getEventType({ id: r.event_type_id }, tx);222 if (!et) throw new ApiError(409, "invalid_state");223 const settings = await getSettings(userId, tx);224 const now = nowMs();225 const dur = r.ends_at.getTime() - r.starts_at.getTime();226 const state = await engineInput(et, settings.timezone, startMs, startMs + dur, { now, ignoreRequestId: r.id, ignoreBookingId: r.reschedule_of_booking_id }, tx);227 state.params.durationMin = Math.round(dur / MIN);228 const refusal = checkSlot(state, startMs, { enforceNotice: false, enforceLimits: false, allowOutsideHours: true });229 if (refusal) throw refusalError(refusal);230 const expires = new Date(Math.min(now + 72 * 3_600_000, startMs));231 await tx`UPDATE booking_requests SET status = 'reschedule_requested', proposed_starts_at = ${new Date(startMs)}, proposed_ends_at = ${new Date(startMs + dur)},232 proposal_expires_at = ${expires}, proposal_message = ${message}, hold_expires_at = NULL, updated_at = now() WHERE id = ${r.id}`;233 const contact = await contactOf(tx, r.contact_id);234 if (contact) await enqueue(tx, { userId, kind: "proposal", dedupKey: `proposal:${r.id}:${startMs}`, to: contact.email, locale: r.locale, requestId: r.id });235 await audit({ userId, actor: "admin", action: "request.proposed", entity: "request", entityId: r.id, ip }, tx);236 });237}238239export async function setInternalNote(userId: string, requestId: string, note: string): Promise<void> {240 const res = await sql`UPDATE booking_requests SET internal_note = ${note}, updated_at = now() WHERE id = ${requestId} AND user_id = ${userId} RETURNING id`;241 if (res.length === 0) throw new ApiError(404, "not_found");242 await audit({ userId, actor: "admin", action: "request.note", entity: "request", entityId: requestId });243}244245// ── Admin: bookings ─────────────────────────────────────────────────────────────────────────────────246247async function lockBooking(tx: Tx, userId: string, id: string): Promise<BookingRow> {248 const [b] = await tx<BookingRow[]>`SELECT * FROM bookings WHERE id = ${id} AND user_id = ${userId} FOR UPDATE`;249 if (!b) throw new ApiError(404, "not_found", "Appointment not found");250 return b;251}252253async function cancelBookingTx(tx: Tx, b: BookingRow, by: "admin" | "visitor", reason: string) {254 await tx`UPDATE bookings SET status = 'cancelled', cancelled_at = now(), cancelled_by = ${by}, cancel_reason = ${reason}, ics_sequence = ics_sequence + 1, updated_at = now() WHERE id = ${b.id}`;255 await tx`UPDATE booking_requests SET status = 'cancelled', hold_expires_at = NULL, proposal_expires_at = NULL, updated_at = now()256 WHERE (booking_id = ${b.id} AND status = 'approved') OR (reschedule_of_booking_id = ${b.id} AND status IN ('pending', 'reschedule_requested'))`;257 await tx`UPDATE notification_jobs SET status = 'cancelled' WHERE booking_id = ${b.id} AND kind = 'reminder' AND status = 'queued'`;258 const contact = await contactOf(tx, b.contact_id);259 const [req] = await tx<{ id: string }[]>`SELECT id FROM booking_requests WHERE booking_id = ${b.id} ORDER BY created_at LIMIT 1`;260 if (contact) await enqueue(tx, { userId: b.user_id, kind: "booking_cancelled", dedupKey: `cancelled:${b.id}`, to: contact.email, locale: b.locale, bookingId: b.id, requestId: req?.id ?? null, payload: { by } });261 if (by === "visitor") {262 const owner = await ownerEmail(tx, b.user_id);263 const s = await getSettings(b.user_id, tx);264 if (owner) await enqueue(tx, { userId: b.user_id, kind: "admin_visitor_cancelled", dedupKey: `admin_cancelled:${b.id}`, to: owner, locale: s.locale, bookingId: b.id, requestId: req?.id ?? null });265 }266}267268export async function cancelBooking(userId: string, bookingId: string, reason: string, ip?: string): Promise<void> {269 await withOwnerLock(userId, async (tx) => {270 const b = await lockBooking(tx, userId, bookingId);271 if (b.status !== "confirmed") throw new ApiError(409, "invalid_state", "This appointment is already cancelled");272 await cancelBookingTx(tx, b, "admin", reason);273 await audit({ userId, actor: "admin", action: "booking.cancelled", entity: "booking", entityId: b.id, ip }, tx);274 });275}276277export async function rescheduleBooking(userId: string, bookingId: string, startMs: number, ip?: string): Promise<BookingRow> {278 try {279 return await withOwnerLock(userId, async (tx) => {280 const b = await lockBooking(tx, userId, bookingId);281 if (b.status !== "confirmed") throw new ApiError(409, "invalid_state", "Only a confirmed appointment can be moved");282 const dur = b.ends_at.getTime() - b.starts_at.getTime();283 const settings = await getSettings(userId, tx);284 const now = nowMs();285 if (startMs <= now) throw refusalError("past");286 const et = b.event_type_id ? await getEventType({ id: b.event_type_id }, tx) : null;287 if (et) {288 const state = await engineInput(et, settings.timezone, startMs, startMs + dur, { now, ignoreBookingId: b.id }, tx);289 state.params.durationMin = Math.round(dur / MIN);290 const refusal = checkSlot(state, startMs, { enforceNotice: false, enforceLimits: false, allowOutsideHours: true });291 if (refusal) throw refusalError(refusal);292 }293 const [moved] = await tx<BookingRow[]>`UPDATE bookings SET starts_at = ${new Date(startMs)}, ends_at = ${new Date(startMs + dur)}, ics_sequence = ics_sequence + 1, updated_at = now() WHERE id = ${b.id} RETURNING *`;294 await tx`UPDATE booking_requests SET starts_at = ${moved.starts_at}, ends_at = ${moved.ends_at}, updated_at = now() WHERE booking_id = ${b.id} AND status = 'approved'`;295 await tx`UPDATE notification_jobs SET status = 'cancelled' WHERE booking_id = ${b.id} AND kind = 'reminder' AND status = 'queued'`;296 const contact = await contactOf(tx, b.contact_id);297 const [req] = await tx<{ id: string }[]>`SELECT id FROM booking_requests WHERE booking_id = ${b.id} ORDER BY created_at LIMIT 1`;298 if (contact) await enqueue(tx, { userId, kind: "booking_rescheduled", dedupKey: `rescheduled:${b.id}:${moved.ics_sequence}`, to: contact.email, locale: b.locale, bookingId: b.id, requestId: req?.id ?? null });299 if (et) await scheduleReminder(tx, moved, et, contact?.email ?? null, req?.id ?? null);300 await audit({ userId, actor: "admin", action: "booking.rescheduled", entity: "booking", entityId: b.id, ip }, tx);301 return moved;302 });303 } catch (e) { mapOverlap(e); }304}305306/** Appointment entered by the owner for someone (phone call, in person…). Confirmed at once, after the same checks. */307export async function createBookingByAdmin(userId: string, input: { eventTypeId: string; startMs: number; name: string; email: string; notify: boolean; ip?: string }): Promise<BookingRow> {308 try {309 return await withOwnerLock(userId, async (tx) => {310 const et = await getEventType({ id: input.eventTypeId }, tx);311 if (!et || et.user_id !== userId) throw new ApiError(404, "not_found", "Appointment type not found");312 const settings = await getSettings(userId, tx);313 const now = nowMs();314 const start = new Date(input.startMs), end = new Date(input.startMs + et.duration_min * MIN);315 const state = await engineInput(et, settings.timezone, start.getTime(), end.getTime(), { now }, tx);316 const refusal = checkSlot(state, input.startMs, { enforceNotice: false, enforceLimits: false, allowOutsideHours: true });317 if (refusal) throw refusalError(refusal);318 const contactId = await upsertContact(tx, userId, { email: input.email, name: input.name, locale: settings.locale });319 const b = await insertBooking(tx, { userId, et, contactId, contactName: input.name, start, end, visitorTz: settings.timezone, locale: settings.locale });320 const [r] = await tx<{ id: string }[]>`321 INSERT INTO booking_requests (user_id, event_type_id, contact_id, starts_at, ends_at, visitor_tz, locale, status, idempotency_key, booking_id, consent_at, decided_at)322 VALUES (${userId}, ${et.id}, ${contactId}, ${start}, ${end}, ${settings.timezone}, ${settings.locale}, 'approved', ${"admin:" + randomUUID()}, ${b.id}, now(), now()) RETURNING id`;323 await mintToken(tx, r.id, end);324 if (input.notify) {325 await enqueue(tx, { userId, kind: "booking_confirmed", dedupKey: `confirmed:${b.id}:0`, to: input.email, locale: settings.locale, requestId: r.id, bookingId: b.id });326 await scheduleReminder(tx, b, et, input.email, r.id);327 }328 await audit({ userId, actor: "admin", action: "booking.created", entity: "booking", entityId: b.id, meta: { notify: input.notify }, ip: input.ip }, tx);329 return b;330 });331 } catch (e) { mapOverlap(e); }332}333334// ── Visitor: management link ────────────────────────────────────────────────────────────────────────335336export async function resolveToken(raw: string, q: Tx | typeof sql = sql): Promise<{ tokenId: string; requestId: string }> {337 const [t] = await q<{ id: string; request_id: string; expires_at: Date; revoked_at: Date | null }[]>`SELECT id, request_id, expires_at, revoked_at FROM booking_tokens WHERE token_hash = ${sha256(raw)}`;338 if (!t) throw new ApiError(404, "link_invalid", "This link is not valid");339 if (t.revoked_at) throw new ApiError(410, "link_revoked", "This link is no longer active");340 if (t.expires_at.getTime() <= nowMs()) throw new ApiError(410, "link_expired", "This link has expired");341 return { tokenId: t.id, requestId: t.request_id };342}343344async function visitorContext(tx: Tx, raw: string) {345 const { requestId } = await resolveToken(raw, tx);346 const [r] = await tx<RequestRow[]>`SELECT * FROM booking_requests WHERE id = ${requestId} FOR UPDATE`;347 if (!r) throw new ApiError(404, "link_invalid", "This link is not valid");348 const et = await getEventType({ id: r.event_type_id }, tx);349 if (!et) throw new ApiError(404, "link_invalid", "This link is not valid");350 const booking = r.booking_id ? (await tx<BookingRow[]>`SELECT * FROM bookings WHERE id = ${r.booking_id} FOR UPDATE`)[0] ?? null : null;351 return { r, et, booking };352}353354/** The request row is found through the token first (no lock), then everything is re-read under the owner lock. */355async function withVisitor<T>(raw: string, fn: (tx: Tx, c: Awaited<ReturnType<typeof visitorContext>>) => Promise<T>): Promise<T> {356 const { requestId } = await resolveToken(raw);357 const [own] = await sql<{ user_id: string }[]>`SELECT user_id FROM booking_requests WHERE id = ${requestId}`;358 if (!own) throw new ApiError(404, "link_invalid", "This link is not valid");359 try { return await withOwnerLock(own.user_id, async (tx) => fn(tx, await visitorContext(tx, raw))); } catch (e) { mapOverlap(e); }360}361362export async function visitorCancel(raw: string, reason: string, ip?: string): Promise<void> {363 await withVisitor(raw, async (tx, { r, et, booking }) => {364 const now = nowMs();365 if (r.status === "pending" || r.status === "reschedule_requested") {366 await tx`UPDATE booking_requests SET status = 'cancelled', hold_expires_at = NULL, proposal_expires_at = NULL, updated_at = now() WHERE id = ${r.id}`;367 const owner = await ownerEmail(tx, r.user_id);368 const s = await getSettings(r.user_id, tx);369 if (owner) await enqueue(tx, { userId: r.user_id, kind: "admin_request_withdrawn", dedupKey: `admin_withdrawn:${r.id}`, to: owner, locale: s.locale, requestId: r.id });370 } else if (r.status === "approved" && booking?.status === "confirmed") {371 if (!et.allow_visitor_cancel) throw new ApiError(403, "not_allowed", "This appointment cannot be cancelled online");372 if (booking.starts_at.getTime() <= now) throw new ApiError(409, "slot_past", "This appointment has already started");373 await cancelBookingTx(tx, booking, "visitor", reason);374 } else throw new ApiError(409, "invalid_state", "There is nothing to cancel");375 await audit({ userId: r.user_id, actor: "visitor", action: "visitor.cancelled", entity: "request", entityId: r.id, ip }, tx);376 });377}378379export async function visitorAcceptProposal(raw: string, ip?: string): Promise<void> {380 await withVisitor(raw, async (tx, { r }) => {381 if (r.status !== "reschedule_requested" || !r.proposed_starts_at || !r.proposed_ends_at) throw new ApiError(409, "invalid_state", "There is no proposal to accept");382 if (!r.proposal_expires_at || r.proposal_expires_at.getTime() <= nowMs()) throw new ApiError(410, "proposal_expired", "This proposal has expired");383 const b = await confirmAt(tx, r, r.proposed_starts_at, r.proposed_ends_at, { allowOutsideHours: true, actor: "visitor" });384 const owner = await ownerEmail(tx, r.user_id);385 const s = await getSettings(r.user_id, tx);386 if (owner) await enqueue(tx, { userId: r.user_id, kind: "admin_proposal_accepted", dedupKey: `admin_accepted:${r.id}:${b.ics_sequence}`, to: owner, locale: s.locale, requestId: r.id, bookingId: b.id });387 await audit({ userId: r.user_id, actor: "visitor", action: "visitor.accepted_proposal", entity: "request", entityId: r.id, ip }, tx);388 });389}390391/**392 * The visitor picks another time.393 * - request not yet confirmed: the request itself moves and waits again for an answer;394 * - confirmed booking: with automatic approval the booking moves at once; otherwise a linked request is created395 * and the booking stays where it is until the owner approves.396 */397export async function visitorReschedule(raw: string, startMs: number, ip?: string): Promise<{ status: "pending" | "approved" }> {398 return withVisitor(raw, async (tx, { r, et, booking }) => {399 const now = nowMs();400 const settings = await getSettings(r.user_id, tx);401 const end = startMs + et.duration_min * MIN;402 const confirmed = r.status === "approved" && booking?.status === "confirmed";403 if (!confirmed && r.status !== "pending" && r.status !== "reschedule_requested") throw new ApiError(409, "invalid_state", "This request can no longer be changed");404 if (confirmed) {405 if (!et.allow_visitor_reschedule) throw new ApiError(403, "not_allowed", "This appointment cannot be moved online");406 if (booking!.starts_at.getTime() <= now) throw new ApiError(409, "slot_past", "This appointment has already started");407 }408 const state = await engineInput(et, settings.timezone, startMs, end, { now, ignoreRequestId: r.id, ignoreBookingId: confirmed ? booking!.id : null }, tx);409 const refusal = checkSlot(state, startMs);410 if (refusal) throw refusalError(refusal === "taken" || refusal === "past" || refusal === "limit" ? refusal : "misaligned");411 const fresh = await externalFreshness(r.user_id, settings, tx);412 const auto = et.approval === "auto" && !(fresh.stale && settings.external_stale_policy === "conservative");413 const hold = et.hold_policy === "hold" ? new Date(Math.min(now + et.hold_ttl_hours * 3_600_000, startMs)) : null;414 const owner = await ownerEmail(tx, r.user_id);415 await audit({ userId: r.user_id, actor: "visitor", action: "visitor.reschedule", entity: "request", entityId: r.id, meta: { confirmed, auto }, ip }, tx);416417 if (!confirmed) {418 await tx`UPDATE booking_requests SET starts_at = ${new Date(startMs)}, ends_at = ${new Date(end)}, status = 'pending', hold_expires_at = ${hold},419 proposed_starts_at = NULL, proposed_ends_at = NULL, proposal_expires_at = NULL, updated_at = now() WHERE id = ${r.id}`;420 if (auto) {421 const [again] = await tx<RequestRow[]>`SELECT * FROM booking_requests WHERE id = ${r.id}`;422 await confirmAt(tx, again, again.starts_at, again.ends_at, { allowOutsideHours: false, actor: "visitor" });423 return { status: "approved" as const };424 }425 if (owner) await enqueue(tx, { userId: r.user_id, kind: "admin_new_request", dedupKey: `admin_new_request:${r.id}:${startMs}`, to: owner, locale: settings.locale, requestId: r.id });426 return { status: "pending" as const };427 }428 const [open] = await tx<{ id: string }[]>`SELECT id FROM booking_requests WHERE reschedule_of_booking_id = ${booking!.id} AND status IN ('pending', 'reschedule_requested')`;429 if (open) throw new ApiError(409, "reschedule_pending", "A change is already awaiting an answer");430 const [child] = await tx<RequestRow[]>`431 INSERT INTO booking_requests (user_id, event_type_id, contact_id, starts_at, ends_at, visitor_tz, locale, status, hold_expires_at, idempotency_key, reschedule_of_booking_id, consent_at, ip)432 VALUES (${r.user_id}, ${et.id}, ${r.contact_id}, ${new Date(startMs)}, ${new Date(end)}, ${r.visitor_tz}, ${r.locale}, 'pending', ${auto ? null : hold}, ${"resched:" + randomUUID()}, ${booking!.id}, now(), ${ip ?? null})433 RETURNING *`;434 if (auto) { await confirmAt(tx, child, child.starts_at, child.ends_at, { allowOutsideHours: false, actor: "visitor" }); return { status: "approved" as const }; }435 if (owner) await enqueue(tx, { userId: r.user_id, kind: "admin_reschedule_request", dedupKey: `admin_resched:${child.id}`, to: owner, locale: settings.locale, requestId: child.id, bookingId: booking!.id });436 return { status: "pending" as const };437 });438}439440// ── System ──────────────────────────────────────────────────────────────────────────────────────────441442/** Requests whose time has come without an answer, and proposals left unanswered, expire. Idempotent. */443export async function expireStale(): Promise<number> {444 const now = new Date(nowMs());445 const rows = await sql<{ id: string; user_id: string; locale: string; email: string | null; child: boolean }[]>`446 UPDATE booking_requests r SET status = 'expired', hold_expires_at = NULL, proposal_expires_at = NULL, updated_at = now()447 FROM (SELECT q.id, c.email FROM booking_requests q LEFT JOIN contacts c ON c.id = q.contact_id448 WHERE (q.status = 'pending' AND q.starts_at <= ${now}) OR (q.status = 'reschedule_requested' AND q.proposal_expires_at <= ${now})449 FOR UPDATE OF q SKIP LOCKED) x450 WHERE r.id = x.id451 RETURNING r.id, r.user_id, r.locale, x.email, (r.reschedule_of_booking_id IS NOT NULL) AS child`;452 for (const r of rows) {453 if (r.email && !r.child) await enqueue(sql, { userId: r.user_id, kind: "request_expired", dedupKey: `expired:${r.id}`, to: r.email, locale: r.locale, requestId: r.id });454 await audit({ userId: r.user_id, actor: "system", action: "request.expired", entity: "request", entityId: r.id });455 }456 return rows.length;457}458