public
imalexnord
read
Slot
Calendar-first scheduling powered by Cloudflare Workers and Google Calendar.
Languages
Repository composition by tracked source files.
TypeScript
79%
CSS
19%
SQL
2%
HTML
0%
Create file
Wiki Documentation
Clone
https://nobgit.com/user/imalexnord/slot.git
ssh://[email protected]:2222/user/imalexnord/slot.git
import {
clearSessionCookie,
getCookie,
randomUrlToken,
sessionCookie,
sha256Base64Url,
signSession,
verifySession,
} from './crypto';
import {
GoogleCalendarError,
completeGoogleOAuth,
confirmCalendarHold,
createCalendarEvent,
createCalendarHold,
createGoogleAuthUrl,
deleteCalendarEvent,
disconnectGoogle,
hasGoogleConnection,
queryBusy,
rescheduleCalendarEvent,
sendRescheduleSuggestion,
} from './google';
import { bookingAmountMinor } from './money';
import { DEFAULT_SETTINGS, getSettings, saveSettings } from './settings';
import {
createStripeCheckoutSession,
createStripeRefund,
expireStripeCheckoutSession,
retrieveStripeCheckoutSession,
stripeCheckoutSessionMode,
stripeConfigured,
stripeMode,
stripePaymentIntentId,
stripeReceiptUrl,
StripeError,
verifyStripeWebhook,
} from './stripe';
import type { StripeCheckoutSession, StripeRefund } from './stripe';
import {
addDaysToDateKey,
addMinutes,
dateKeyInZone,
overlaps,
slotKeys,
timeToMinutes,
weekdayForDate,
zonedDateTimeToUtc,
} from './time';
import type {
AppSettings,
AvailabilityBlock,
BookingRequest,
BusyRange,
Env,
MeetingMode,
RecurringUnavailablePeriod,
} from './types';
const JSON_HEADERS = {
'Content-Type': 'application/json; charset=utf-8',
'Cache-Control': 'no-store',
};
function json(data: unknown, status = 200, headers: HeadersInit = {}): Response {
return new Response(JSON.stringify(data), {
status,
headers: { ...JSON_HEADERS, ...headers },
});
}
function redirect(location: string, headers: HeadersInit = {}): Response {
return new Response(null, { status: 302, headers: { Location: location, ...headers } });
}
function errorMessage(error: unknown): string {
if (error instanceof GoogleCalendarError) return error.publicMessage;
return error instanceof Error ? error.message : 'Unexpected error';
}
function isEmail(value: string): boolean {
return /^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(value);
}
function isTime(value: string): boolean {
return /^\d{2}:\d{2}$/.test(value) && timeToMinutes(value) >= 0 && timeToMinutes(value) <= 24 * 60;
}
function privateCalendarError(error: unknown): string {
if (error instanceof GoogleCalendarError) return error.publicMessage;
return 'Calendar sync failed. Check the calendar connection and try again.';
}
async function enforceBookingRateLimit(request: Request, env: Env): Promise<boolean> {
const ip = request.headers.get('CF-Connecting-IP');
if (!ip) return true; // Local development.
const hourBucket = Math.floor(Date.now() / 3_600_000);
const material = new TextEncoder().encode(`${ip}:${hourBucket}:${env.SESSION_SECRET}`);
const digest = new Uint8Array(await crypto.subtle.digest('SHA-256', material));
const key = Array.from(digest, (byte) => byte.toString(16).padStart(2, '0')).join('');
const resetAt = (hourBucket + 1) * 3_600_000;
await env.DB.prepare('DELETE FROM rate_limits WHERE reset_at < ?1').bind(Date.now()).run();
await env.DB.prepare(`
INSERT INTO rate_limits (key, count, reset_at)
VALUES (?1, 1, ?2)
ON CONFLICT(key) DO UPDATE SET count = count + 1
`).bind(key, resetAt).run();
const row = await env.DB.prepare('SELECT count FROM rate_limits WHERE key = ?1')
.bind(key)
.first<{ count: number }>();
return (row?.count ?? 1) <= 12;
}
async function enforceCancellationRateLimit(request: Request, env: Env): Promise<boolean> {
const ip = request.headers.get('CF-Connecting-IP');
if (!ip) return true;
const hourBucket = Math.floor(Date.now() / 3_600_000);
const material = new TextEncoder().encode(`cancel:${ip}:${hourBucket}:${env.SESSION_SECRET}`);
const digest = new Uint8Array(await crypto.subtle.digest('SHA-256', material));
const key = Array.from(digest, (byte) => byte.toString(16).padStart(2, '0')).join('');
const resetAt = (hourBucket + 1) * 3_600_000;
await env.DB.prepare('DELETE FROM rate_limits WHERE reset_at < ?1').bind(Date.now()).run();
await env.DB.prepare(`
INSERT INTO rate_limits (key, count, reset_at)
VALUES (?1, 1, ?2)
ON CONFLICT(key) DO UPDATE SET count = count + 1
`).bind(key, resetAt).run();
const row = await env.DB.prepare('SELECT count FROM rate_limits WHERE key = ?1')
.bind(key)
.first<{ count: number }>();
return (row?.count ?? 1) <= 10;
}
async function enforceManageRateLimit(request: Request, env: Env): Promise<boolean> {
const ip = request.headers.get('CF-Connecting-IP');
if (!ip) return true;
const hourBucket = Math.floor(Date.now() / 3_600_000);
const material = new TextEncoder().encode(`manage:${ip}:${hourBucket}:${env.SESSION_SECRET}`);
const digest = new Uint8Array(await crypto.subtle.digest('SHA-256', material));
const key = Array.from(digest, (byte) => byte.toString(16).padStart(2, '0')).join('');
const resetAt = (hourBucket + 1) * 3_600_000;
await env.DB.prepare('DELETE FROM rate_limits WHERE reset_at < ?1').bind(Date.now()).run();
await env.DB.prepare(`
INSERT INTO rate_limits (key, count, reset_at)
VALUES (?1, 1, ?2)
ON CONFLICT(key) DO UPDATE SET count = count + 1
`).bind(key, resetAt).run();
const row = await env.DB.prepare('SELECT count FROM rate_limits WHERE key = ?1')
.bind(key)
.first<{ count: number }>();
return (row?.count ?? 1) <= 30;
}
function newPublicBookingId(): string {
const alphabet = '23456789ABCDEFGHJKLMNPQRSTUVWXYZ';
const bytes = crypto.getRandomValues(new Uint8Array(20));
const chars = Array.from(bytes, (byte) => alphabet[byte % alphabet.length]).join('');
return `SLT-${chars.slice(0, 4)}-${chars.slice(4, 8)}-${chars.slice(8, 12)}-${chars.slice(12, 16)}-${chars.slice(16, 20)}`;
}
function normalizePublicBookingId(value: unknown): string | null {
const compact = String(value ?? '').trim().toUpperCase().replace(/[^A-Z0-9]/g, '');
if (!compact.startsWith('SLT') || compact.length !== 23) return null;
const chars = compact.slice(3);
if (!/^[A-Z0-9]{20}$/.test(chars)) return null;
return `SLT-${chars.slice(0, 4)}-${chars.slice(4, 8)}-${chars.slice(8, 12)}-${chars.slice(12, 16)}-${chars.slice(16, 20)}`;
}
async function requireAdmin(request: Request, env: Env): Promise<string | null> {
const email = await verifySession(env, getCookie(request, 'meet_session'));
if (!email || email.toLowerCase() !== env.ADMIN_EMAIL.toLowerCase()) return null;
return email;
}
function validateAppSettings(input: unknown): AppSettings {
if (!input || typeof input !== 'object') throw new Error('Invalid settings payload');
const raw = input as Partial<AppSettings>;
const allowedModes: MeetingMode[] = ['google_meet', 'phone', 'in_person', 'decide_later'];
const durations = Array.isArray(raw.durations)
? [...new Set(raw.durations.map(Number).filter((value) => value >= 10 && value <= 240))].sort((a, b) => a - b)
: DEFAULT_SETTINGS.durations;
if (!durations.length) throw new Error('At least one duration is required');
const availability: AppSettings['availability'] = {};
for (let day = 0; day <= 6; day += 1) {
const intervals = raw.availability?.[String(day)] ?? [];
availability[String(day)] = intervals
.filter((interval) => /^\d{2}:\d{2}$/.test(interval.start) && /^\d{2}:\d{2}$/.test(interval.end))
.filter((interval) => timeToMinutes(interval.end) > timeToMinutes(interval.start))
.sort((a, b) => timeToMinutes(a.start) - timeToMinutes(b.start))
.slice(0, 3);
}
const allowMeetingModes = Array.isArray(raw.allowMeetingModes)
? raw.allowMeetingModes.filter((mode): mode is MeetingMode => allowedModes.includes(mode as MeetingMode))
: DEFAULT_SETTINGS.allowMeetingModes;
const normalizedModes = allowMeetingModes.length ? allowMeetingModes : ['google_meet'] as MeetingMode[];
const defaultMeetingMode = normalizedModes.includes(raw.defaultMeetingMode as MeetingMode)
? (raw.defaultMeetingMode as MeetingMode)
: normalizedModes[0];
const paymentEnabled = Boolean(raw.paymentEnabled);
const paymentRatePerMinute = Math.min(
1_000_000,
Math.max(0, Math.round(Number(raw.paymentRatePerMinute ?? DEFAULT_SETTINGS.paymentRatePerMinute) * 10000) / 10000),
);
const paymentFreeDurations = Array.isArray(raw.paymentFreeDurations)
? [...new Set(raw.paymentFreeDurations.map(Number).filter((value) => durations.includes(value)))].sort((a, b) => a - b)
: DEFAULT_SETTINGS.paymentFreeDurations;
const hasPaidDuration = durations.some((duration) => !paymentFreeDurations.includes(duration));
if (paymentEnabled && hasPaidDuration && paymentRatePerMinute <= 0) {
throw new Error('Set a price per minute greater than zero for paid meeting lengths.');
}
return {
title: String(raw.title ?? DEFAULT_SETTINGS.title).trim().slice(0, 100) || DEFAULT_SETTINGS.title,
subtitle: String(raw.subtitle ?? DEFAULT_SETTINGS.subtitle).trim().slice(0, 240),
timezone: String(raw.timezone ?? DEFAULT_SETTINGS.timezone).trim() || DEFAULT_SETTINGS.timezone,
bookingWindowDays: Math.min(365, Math.max(1, Number(raw.bookingWindowDays ?? DEFAULT_SETTINGS.bookingWindowDays))),
minimumNoticeMinutes: Math.min(10080, Math.max(0, Number(raw.minimumNoticeMinutes ?? 120))),
bufferBeforeMinutes: Math.min(180, Math.max(0, Number(raw.bufferBeforeMinutes ?? 0))),
bufferAfterMinutes: Math.min(180, Math.max(0, Number(raw.bufferAfterMinutes ?? 15))),
slotIncrementMinutes: [5, 10, 15, 20, 30, 60].includes(Number(raw.slotIncrementMinutes))
? Number(raw.slotIncrementMinutes)
: 15,
durations,
calendarId: String(raw.calendarId ?? 'primary').trim() || 'primary',
busyCalendarIds: Array.isArray(raw.busyCalendarIds)
? raw.busyCalendarIds.map(String).map((value) => value.trim()).filter(Boolean).slice(0, 20)
: ['primary'],
defaultMeetingMode,
allowMeetingModes: normalizedModes,
inPersonLocation: String(raw.inPersonLocation ?? '').trim().slice(0, 240),
paymentEnabled,
paymentRatePerMinute,
paymentFreeDurations,
paymentCurrency: /^[a-zA-Z]{3}$/.test(String(raw.paymentCurrency ?? ''))
? String(raw.paymentCurrency).toLowerCase()
: DEFAULT_SETTINGS.paymentCurrency,
paymentLabel: String(raw.paymentLabel ?? DEFAULT_SETTINGS.paymentLabel).trim().slice(0, 120)
|| DEFAULT_SETTINGS.paymentLabel,
availability,
};
}
async function d1BusyRanges(env: Env, start: number, end: number): Promise<BusyRange[]> {
await env.DB.prepare("DELETE FROM slot_locks WHERE expires_at < ?1").bind(Date.now()).run();
const result = await env.DB.prepare(`
SELECT start_time, end_time
FROM bookings
WHERE (
status = 'pending'
OR status = 'confirmed'
)
AND start_time < ?1
AND end_time > ?2
`).bind(new Date(end).toISOString(), new Date(start).toISOString()).all<{
start_time: string;
end_time: string;
}>();
return (result.results ?? []).map((row) => ({
start: Date.parse(row.start_time),
end: Date.parse(row.end_time),
}));
}
async function recurringUnavailableForDay(env: Env, weekday: number): Promise<RecurringUnavailablePeriod[]> {
const result = await env.DB.prepare(`
SELECT id, weekdays, start_time, end_time, label
FROM recurring_unavailable_periods
ORDER BY start_time
`).all<{ id: string; weekdays: string; start_time: string; end_time: string; label: string | null }>();
return (result.results ?? [])
.map((row) => ({
id: row.id,
weekdays: row.weekdays.split(',').map(Number).filter((day) => day >= 0 && day <= 6),
start: row.start_time,
end: row.end_time,
label: row.label ?? undefined,
}))
.filter((period) => period.weekdays.includes(weekday));
}
async function oneOffBlocksForDate(env: Env, dateKey: string): Promise<AvailabilityBlock[]> {
const result = await env.DB.prepare(`
SELECT id, date, start_time, end_time, all_day, label
FROM availability_blocks
WHERE date = ?1
ORDER BY all_day DESC, start_time
`).bind(dateKey).all<{
id: string;
date: string;
start_time: string | null;
end_time: string | null;
all_day: number;
label: string | null;
}>();
return (result.results ?? []).map((row) => ({
id: row.id,
date: row.date,
start: row.start_time ?? undefined,
end: row.end_time ?? undefined,
allDay: row.all_day === 1,
label: row.label ?? undefined,
}));
}
async function slotUnavailableRanges(env: Env, dateKey: string, timezone: string): Promise<BusyRange[]> {
const weekday = weekdayForDate(dateKey, timezone);
const [recurring, oneOff] = await Promise.all([
recurringUnavailableForDay(env, weekday),
oneOffBlocksForDate(env, dateKey),
]);
const ranges: BusyRange[] = [];
for (const period of recurring) {
ranges.push({
start: zonedDateTimeToUtc(dateKey, period.start, timezone).getTime(),
end: zonedDateTimeToUtc(dateKey, period.end, timezone).getTime(),
});
}
for (const block of oneOff) {
if (block.allDay) {
ranges.push({
start: zonedDateTimeToUtc(dateKey, '00:00', timezone).getTime(),
end: zonedDateTimeToUtc(addDaysToDateKey(dateKey, 1), '00:00', timezone).getTime(),
});
} else if (block.start && block.end) {
ranges.push({
start: zonedDateTimeToUtc(dateKey, block.start, timezone).getTime(),
end: zonedDateTimeToUtc(dateKey, block.end, timezone).getTime(),
});
}
}
return ranges;
}
type DurationBlockReason = 'outside_hours' | 'unavailable';
type AvailabilitySlot = {
start: string;
time: string;
durations: number[];
blockedDurations: Record<string, DurationBlockReason>;
};
async function computeAvailability(env: Env, dateKey: string) {
const settings = await getSettings(env);
await cleanupStalePendingBookings(env, settings);
const connected = await hasGoogleConnection(env);
if (!connected) {
return { settings, connected, date: dateKey, slots: [] as AvailabilitySlot[] };
}
if (!/^\d{4}-\d{2}-\d{2}$/.test(dateKey)) throw new Error('Invalid date');
const today = dateKeyInZone(new Date(), settings.timezone);
const maxDate = addDaysToDateKey(today, settings.bookingWindowDays);
if (dateKey < today || dateKey > maxDate) {
return { settings, connected, date: dateKey, slots: [] as AvailabilitySlot[] };
}
const day = weekdayForDate(dateKey, settings.timezone);
const intervals = settings.availability[String(day)] ?? [];
if (!intervals.length) {
return { settings, connected, date: dateKey, slots: [] as AvailabilitySlot[] };
}
const queryStart = zonedDateTimeToUtc(dateKey, intervals[0].start, settings.timezone).getTime() - 6 * 3600_000;
const queryEnd = zonedDateTimeToUtc(dateKey, intervals[intervals.length - 1].end, settings.timezone).getTime() + 6 * 3600_000;
const [googleBusy, storedBusy, slotBusy] = await Promise.all([
queryBusy(env, settings, new Date(queryStart).toISOString(), new Date(queryEnd).toISOString()),
d1BusyRanges(env, queryStart, queryEnd),
slotUnavailableRanges(env, dateKey, settings.timezone),
]);
const busy: BusyRange[] = [
...googleBusy.map((range) => ({ start: Date.parse(range.start), end: Date.parse(range.end) })),
...storedBusy,
...slotBusy,
];
const earliestAllowed = Date.now() + settings.minimumNoticeMinutes * 60_000;
const slots: AvailabilitySlot[] = [];
for (const interval of intervals) {
const intervalStartMinutes = timeToMinutes(interval.start);
const intervalEndMinutes = timeToMinutes(interval.end);
for (
let minute = intervalStartMinutes;
minute < intervalEndMinutes;
minute += settings.slotIncrementMinutes
) {
const hh = String(Math.floor(minute / 60)).padStart(2, '0');
const mm = String(minute % 60).padStart(2, '0');
const time = `${hh}:${mm}`;
const start = zonedDateTimeToUtc(dateKey, time, settings.timezone).getTime();
if (start < earliestAllowed) continue;
const blockedDurations: Record<string, DurationBlockReason> = {};
const availableDurations = settings.durations.filter((duration) => {
const endMinute = minute + duration;
if (endMinute > intervalEndMinutes) {
blockedDurations[String(duration)] = 'outside_hours';
return false;
}
const bufferedStart = addMinutes(start, -settings.bufferBeforeMinutes);
const bufferedEnd = addMinutes(start, duration + settings.bufferAfterMinutes);
if (overlaps(bufferedStart, bufferedEnd, busy)) {
blockedDurations[String(duration)] = 'unavailable';
return false;
}
return true;
});
if (availableDurations.length) {
slots.push({
start: new Date(start).toISOString(),
time,
durations: availableDurations,
blockedDurations,
});
}
}
}
return { settings, connected, date: dateKey, slots };
}
type PaymentBookingRow = {
id: string;
public_booking_id: string | null;
name: string;
email: string;
phone: string | null;
message: string | null;
start_time: string;
end_time: string;
duration_minutes: number;
timezone: string;
meeting_mode: MeetingMode;
status: string;
google_event_id: string | null;
meet_url: string | null;
payment_status: string;
payment_amount: number | null;
payment_currency: string | null;
stripe_checkout_session_id: string | null;
stripe_payment_intent_id: string | null;
paid_at: string | null;
rescheduled_at: string | null;
reschedule_count: number;
proposed_start_time: string | null;
proposed_end_time: string | null;
proposed_at: string | null;
proposal_status: string | null;
proposal_token_hash: string | null;
refund_status: string;
stripe_refund_id: string | null;
refunded_amount: number | null;
refunded_at: string | null;
updated_at: string | null;
};
function publicConfirmation(booking: PaymentBookingRow, receiptUrl: string | null = null) {
return {
id: booking.id,
bookingId: booking.public_booking_id,
start: booking.start_time,
end: booking.end_time,
duration: booking.duration_minutes,
timezone: booking.timezone,
meetingMode: booking.meeting_mode,
meetUrl: booking.meet_url,
receiptUrl,
};
}
function publicManagePayload(booking: PaymentBookingRow) {
const future = Date.parse(booking.end_time) > Date.now();
const confirmed = booking.status === 'confirmed';
return {
bookingId: booking.public_booking_id,
name: booking.name,
start: booking.start_time,
end: booking.end_time,
duration: booking.duration_minutes,
timezone: booking.timezone,
meetingMode: booking.meeting_mode,
status: booking.status,
paymentStatus: booking.payment_status,
refundStatus: booking.refund_status,
canCancel: confirmed && future,
canReschedule: confirmed && future,
};
}
async function bookingByPublicId(env: Env, value: unknown): Promise<PaymentBookingRow | null> {
const publicBookingId = normalizePublicBookingId(value);
if (!publicBookingId) return null;
const row = await env.DB.prepare('SELECT id FROM bookings WHERE public_booking_id = ?1')
.bind(publicBookingId)
.first<{ id: string }>();
return row ? paymentBookingById(env, row.id) : null;
}
type ActivityMetadata = Record<string, string | number | boolean | null | undefined>;
async function logActivity(
env: Env,
bookingId: string | null,
type: string,
metadata: ActivityMetadata = {},
idempotencyKey: string | null = null,
): Promise<void> {
try {
const cleanMetadata = Object.fromEntries(
Object.entries(metadata).filter(([, value]) => value !== undefined),
);
await env.DB.prepare(`
INSERT OR IGNORE INTO activity_events (id, booking_id, type, metadata, idempotency_key)
VALUES (?1, ?2, ?3, ?4, ?5)
`).bind(
crypto.randomUUID(),
bookingId,
type,
Object.keys(cleanMetadata).length ? JSON.stringify(cleanMetadata) : null,
idempotencyKey,
).run();
} catch {
// Activity is observational. A missing/new migration or a feed write must
// never be allowed to break booking, payment, refund, or calendar flows.
}
}
async function adminActivity(env: Env): Promise<Response> {
const settings = await getSettings(env);
await cleanupStalePendingBookings(env, settings);
const stats = await env.DB.prepare(`
WITH facts AS (
SELECT
*,
CASE
WHEN status = 'confirmed' THEN 1
WHEN status = 'cancelled' AND (payment_status <> 'cancelled' OR paid_at IS NOT NULL) THEN 1
ELSE 0
END AS is_booking
FROM bookings
)
SELECT
COALESCE(SUM(is_booking), 0) AS total_bookings,
COALESCE(SUM(CASE WHEN status = 'confirmed' AND julianday(end_time) >= julianday('now') THEN 1 ELSE 0 END), 0) AS upcoming,
COALESCE(SUM(CASE WHEN status = 'confirmed' AND julianday(end_time) < julianday('now') THEN 1 ELSE 0 END), 0) AS completed,
COALESCE(SUM(CASE WHEN status = 'cancelled' AND is_booking = 1 THEN 1 ELSE 0 END), 0) AS cancelled,
COALESCE(SUM(CASE WHEN is_booking = 1 THEN reschedule_count ELSE 0 END), 0) AS reschedules,
COALESCE(SUM(CASE WHEN paid_at IS NOT NULL THEN 1 ELSE 0 END), 0) AS paid_bookings,
COALESCE(ROUND(AVG(CASE WHEN status = 'confirmed' THEN duration_minutes END), 1), 0) AS average_minutes,
COALESCE(SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END), 0) AS open_payment_holds
FROM facts
`).first<{
total_bookings: number;
upcoming: number;
completed: number;
cancelled: number;
reschedules: number;
paid_bookings: number;
average_minutes: number;
open_payment_holds: number;
}>();
const money = await env.DB.prepare(`
SELECT
lower(payment_currency) AS currency,
COALESCE(SUM(CASE WHEN paid_at IS NOT NULL THEN payment_amount ELSE 0 END), 0) AS revenue,
COALESCE(SUM(CASE WHEN refund_status IN ('succeeded', 'pending') THEN refunded_amount ELSE 0 END), 0) AS refunds
FROM bookings
WHERE payment_currency IS NOT NULL
GROUP BY lower(payment_currency)
ORDER BY lower(payment_currency)
`).all<{ currency: string; revenue: number; refunds: number }>();
const feed = await env.DB.prepare(`
SELECT
a.id,
a.booking_id,
a.type,
a.occurred_at,
a.metadata,
b.name,
b.email,
b.start_time,
b.end_time,
b.duration_minutes,
b.payment_amount,
b.payment_currency
FROM activity_events a
LEFT JOIN bookings b ON b.id = a.booking_id
ORDER BY datetime(a.occurred_at) DESC, a.rowid DESC
LIMIT 100
`).all<{
id: string;
booking_id: string | null;
type: string;
occurred_at: string;
metadata: string | null;
name: string | null;
email: string | null;
start_time: string | null;
end_time: string | null;
duration_minutes: number | null;
payment_amount: number | null;
payment_currency: string | null;
}>();
return json({
paymentsEnabled: settings.paymentEnabled,
stats: stats ?? {
total_bookings: 0,
upcoming: 0,
completed: 0,
cancelled: 0,
reschedules: 0,
paid_bookings: 0,
average_minutes: 0,
open_payment_holds: 0,
},
money: settings.paymentEnabled ? (money.results ?? []).map((row) => ({
currency: row.currency,
revenue: Number(row.revenue ?? 0),
refunds: Number(row.refunds ?? 0),
net: Number(row.revenue ?? 0) - Number(row.refunds ?? 0),
})) : [],
events: (feed.results ?? []).map((row) => {
let metadata: Record<string, unknown> = {};
if (row.metadata) {
try { metadata = JSON.parse(row.metadata) as Record<string, unknown>; } catch { metadata = {}; }
}
return { ...row, metadata };
}),
});
}
async function adminAttendees(env: Env): Promise<Response> {
const settings = await getSettings(env);
await cleanupStalePendingBookings(env, settings);
const result = await env.DB.prepare(`
WITH facts AS (
SELECT
lower(trim(email)) AS email_key,
status,
start_time,
end_time,
duration_minutes,
payment_status,
paid_at,
refund_status,
refunded_at,
reschedule_count,
created_at,
CASE
WHEN status = 'confirmed' THEN 1
WHEN status = 'cancelled' AND (payment_status <> 'cancelled' OR paid_at IS NOT NULL) THEN 1
ELSE 0
END AS is_booking
FROM bookings
WHERE trim(email) <> ''
)
SELECT
f.email_key,
(
SELECT b2.email FROM bookings b2
WHERE lower(trim(b2.email)) = f.email_key
AND (b2.status = 'confirmed' OR (b2.status = 'cancelled' AND (b2.payment_status <> 'cancelled' OR b2.paid_at IS NOT NULL)))
ORDER BY datetime(b2.created_at) DESC, b2.rowid DESC
LIMIT 1
) AS email,
(
SELECT b2.name FROM bookings b2
WHERE lower(trim(b2.email)) = f.email_key
AND (b2.status = 'confirmed' OR (b2.status = 'cancelled' AND (b2.payment_status <> 'cancelled' OR b2.paid_at IS NOT NULL)))
ORDER BY datetime(b2.created_at) DESC, b2.rowid DESC
LIMIT 1
) AS name,
COALESCE(SUM(f.is_booking), 0) AS total_bookings,
COALESCE(SUM(CASE WHEN f.status = 'confirmed' AND julianday(f.end_time) >= julianday('now') THEN 1 ELSE 0 END), 0) AS upcoming,
COALESCE(SUM(CASE WHEN f.status = 'confirmed' AND julianday(f.end_time) < julianday('now') THEN 1 ELSE 0 END), 0) AS completed,
COALESCE(SUM(CASE WHEN f.status = 'cancelled' AND f.is_booking = 1 THEN 1 ELSE 0 END), 0) AS cancelled,
COALESCE(SUM(CASE WHEN f.is_booking = 1 THEN f.reschedule_count ELSE 0 END), 0) AS reschedules,
COALESCE(SUM(CASE WHEN f.is_booking = 1 AND f.paid_at IS NOT NULL THEN 1 ELSE 0 END), 0) AS paid_bookings,
COALESCE(SUM(CASE WHEN f.is_booking = 1 AND (f.refunded_at IS NOT NULL OR f.refund_status = 'succeeded') THEN 1 ELSE 0 END), 0) AS refunded_bookings,
COALESCE(SUM(CASE WHEN f.is_booking = 1 THEN f.duration_minutes ELSE 0 END), 0) AS total_minutes,
COALESCE(ROUND(AVG(CASE WHEN f.is_booking = 1 THEN f.duration_minutes END), 1), 0) AS average_minutes,
MIN(CASE WHEN f.is_booking = 1 THEN f.created_at END) AS first_booked_at,
MAX(CASE WHEN f.is_booking = 1 THEN f.created_at END) AS last_booked_at
FROM facts f
GROUP BY f.email_key
HAVING SUM(f.is_booking) > 0
ORDER BY MAX(CASE WHEN f.is_booking = 1 THEN datetime(f.created_at) END) DESC
LIMIT 500
`).all<{
email_key: string;
email: string;
name: string;
total_bookings: number;
upcoming: number;
completed: number;
cancelled: number;
reschedules: number;
paid_bookings: number;
refunded_bookings: number;
total_minutes: number;
average_minutes: number;
first_booked_at: string | null;
last_booked_at: string | null;
}>();
return json({
paymentsEnabled: settings.paymentEnabled,
attendees: (result.results ?? []).map((row) => ({
email: row.email,
name: row.name,
total_bookings: Number(row.total_bookings ?? 0),
upcoming: Number(row.upcoming ?? 0),
completed: Number(row.completed ?? 0),
cancelled: Number(row.cancelled ?? 0),
reschedules: Number(row.reschedules ?? 0),
paid_bookings: Number(row.paid_bookings ?? 0),
refunded_bookings: Number(row.refunded_bookings ?? 0),
total_minutes: Number(row.total_minutes ?? 0),
average_minutes: Number(row.average_minutes ?? 0),
first_booked_at: row.first_booked_at,
last_booked_at: row.last_booked_at,
})),
});
}
async function adminAttendeeBookings(env: Env, email: string): Promise<Response> {
const normalizedEmail = email.trim().toLowerCase();
if (!normalizedEmail || normalizedEmail.length > 320 || !normalizedEmail.includes('@')) {
return json({ error: 'Invalid attendee email.' }, 400);
}
const result = await env.DB.prepare(`
SELECT id, public_booking_id, name, email, start_time, end_time, duration_minutes,
meeting_mode, status, meet_url, created_at,
cancelled_at, calendar_provider, calendar_sync_status,
calendar_sync_error, calendar_sync_updated_at,
payment_status, payment_amount, payment_currency,
stripe_checkout_session_id, stripe_payment_intent_id, paid_at,
rescheduled_at, reschedule_count, proposed_start_time, proposed_end_time,
proposed_at, proposal_status, refund_status, stripe_refund_id,
refunded_amount, refunded_at
FROM bookings
WHERE lower(trim(email)) = ?1
AND (
status = 'confirmed'
OR (status = 'cancelled' AND (payment_status <> 'cancelled' OR paid_at IS NOT NULL))
)
ORDER BY datetime(created_at) DESC, start_time DESC
LIMIT 100
`).bind(normalizedEmail).all();
return json({ bookings: result.results ?? [] });
}
async function paymentBookingById(env: Env, bookingId: string): Promise<PaymentBookingRow | null> {
return env.DB.prepare(`
SELECT id, public_booking_id, name, email, phone, message, start_time, end_time, duration_minutes,
timezone, meeting_mode, status, google_event_id, meet_url, payment_status,
payment_amount, payment_currency, stripe_checkout_session_id, stripe_payment_intent_id, paid_at,
rescheduled_at, reschedule_count, proposed_start_time, proposed_end_time, proposed_at,
proposal_status, proposal_token_hash, refund_status, stripe_refund_id, refunded_amount,
refunded_at, updated_at
FROM bookings
WHERE id = ?1
`).bind(bookingId).first<PaymentBookingRow>();
}
async function releasePendingBooking(env: Env, settings: AppSettings, bookingId: string): Promise<void> {
const booking = await paymentBookingById(env, bookingId);
if (!booking || booking.status !== 'pending') return;
if (booking.google_event_id) {
let lastError: unknown = null;
for (let attempt = 0; attempt < 3; attempt += 1) {
try {
await deleteCalendarEvent(env, settings, booking.google_event_id);
lastError = null;
break;
} catch (error) {
lastError = error;
if (attempt < 2) await new Promise((resolve) => setTimeout(resolve, 150 * (attempt + 1)));
}
}
if (lastError) throw lastError;
}
// Keep a cancelled audit row instead of deleting the booking. Besides making
// released holds visible in Admin history, this preserves the Stripe session id
// if a stale Checkout from another Stripe mode ever reports back later.
await env.DB.batch([
env.DB.prepare('DELETE FROM slot_locks WHERE booking_id = ?1').bind(bookingId),
env.DB.prepare(`
UPDATE bookings
SET status = 'cancelled',
payment_status = CASE WHEN payment_status = 'paid' THEN 'paid' ELSE 'cancelled' END,
google_event_id = NULL,
meet_url = NULL,
cancelled_at = COALESCE(cancelled_at, datetime('now')),
calendar_sync_status = 'cancelled',
calendar_sync_error = NULL,
calendar_sync_updated_at = datetime('now'),
updated_at = datetime('now')
WHERE id = ?1 AND status = 'pending'
`).bind(bookingId),
]);
await logActivity(env, bookingId, 'payment_hold_released', {
start: booking.start_time,
end: booking.end_time,
amount: booking.payment_amount,
currency: booking.payment_currency,
}, `booking:${bookingId}:hold-released`);
}
async function recordPaidSession(
env: Env,
bookingId: string,
session: StripeCheckoutSession,
): Promise<void> {
await env.DB.prepare(`
UPDATE bookings
SET payment_status = 'paid',
payment_amount = COALESCE(?1, payment_amount),
payment_currency = COALESCE(?2, payment_currency),
stripe_checkout_session_id = COALESCE(stripe_checkout_session_id, ?3),
stripe_payment_intent_id = ?4,
paid_at = COALESCE(paid_at, datetime('now'))
WHERE id = ?5
AND (stripe_checkout_session_id IS NULL OR stripe_checkout_session_id = ?3)
`).bind(
session.amount_total ?? null,
session.currency ?? null,
session.id,
stripePaymentIntentId(session),
bookingId,
).run();
await logActivity(env, bookingId, 'payment_received', {
amount: session.amount_total ?? null,
currency: session.currency ?? null,
}, `booking:${bookingId}:paid`);
}
async function fulfillPaidBooking(env: Env, bookingId: string): Promise<PaymentBookingRow> {
let booking = await paymentBookingById(env, bookingId);
if (!booking) throw new Error('Paid booking record was not found.');
if (booking.status === 'confirmed') return booking;
if (booking.status === 'confirming') {
await env.DB.prepare(`
UPDATE bookings
SET status = 'pending', updated_at = datetime('now')
WHERE id = ?1
AND status = 'confirming'
AND updated_at < datetime('now', '-2 minutes')
`).bind(booking.id).run();
booking = await paymentBookingById(env, booking.id);
if (booking?.status === 'confirmed') return booking;
if (booking?.status === 'confirming') {
throw new Error('Paid booking confirmation is already in progress.');
}
if (!booking) throw new Error('Paid booking record was not found.');
}
if (booking.status !== 'pending' || booking.payment_status !== 'paid') {
throw new Error('Booking is not ready for paid confirmation.');
}
// Claim fulfillment before touching Google. This keeps the Stripe webhook and
// the browser return-page fallback from both sending the attendee invitation.
const claim = await env.DB.prepare(`
UPDATE bookings
SET status = 'confirming',
calendar_sync_status = 'confirming',
calendar_sync_error = NULL,
calendar_sync_updated_at = datetime('now'),
updated_at = datetime('now')
WHERE id = ?1
AND status = 'pending'
AND payment_status = 'paid'
`).bind(booking.id).run();
const claimed = ((claim as unknown as { meta?: { changes?: number } }).meta?.changes ?? 0) > 0;
if (!claimed) {
booking = await paymentBookingById(env, booking.id);
if (booking?.status === 'confirmed') return booking;
throw new Error('Paid booking confirmation is already in progress.');
}
const settings = await getSettings(env);
try {
const created = booking.google_event_id
? await confirmCalendarHold(env, settings, booking.google_event_id, {
id: booking.id,
publicBookingId: booking.public_booking_id ?? booking.id,
name: booking.name,
email: booking.email,
phone: booking.phone ?? undefined,
message: booking.message ?? undefined,
start: booking.start_time,
end: booking.end_time,
meetingMode: booking.meeting_mode,
})
: await createCalendarEvent(env, settings, {
id: booking.id,
publicBookingId: booking.public_booking_id ?? booking.id,
name: booking.name,
email: booking.email,
phone: booking.phone ?? undefined,
message: booking.message ?? undefined,
start: booking.start_time,
end: booking.end_time,
meetingMode: booking.meeting_mode,
});
await env.DB.batch([
env.DB.prepare(`
UPDATE bookings
SET status = 'confirmed',
google_event_id = ?1,
meet_url = ?2,
calendar_sync_status = 'synced',
calendar_sync_error = NULL,
calendar_sync_updated_at = datetime('now'),
updated_at = datetime('now')
WHERE id = ?3
`).bind(created.eventId, created.meetUrl, booking.id),
env.DB.prepare('UPDATE slot_locks SET expires_at = ?1 WHERE booking_id = ?2')
.bind(Date.now() + 10 * 60_000, booking.id),
]);
await logActivity(env, booking.id, 'booking_confirmed', {
start: booking.start_time,
end: booking.end_time,
duration: booking.duration_minutes,
paid: true,
}, `booking:${booking.id}:confirmed`);
} catch (error) {
await env.DB.prepare(`
UPDATE bookings
SET status = 'pending',
calendar_sync_status = 'failed',
calendar_sync_error = ?1,
calendar_sync_updated_at = datetime('now'),
updated_at = datetime('now')
WHERE id = ?2
AND status = 'confirming'
`).bind(privateCalendarError(error), booking.id).run().catch(() => undefined);
throw error;
}
const confirmed = await paymentBookingById(env, booking.id);
if (!confirmed) throw new Error('Confirmed booking record disappeared.');
return confirmed;
}
async function cleanupStalePendingBookings(env: Env, settings: AppSettings): Promise<void> {
const stale = await env.DB.prepare(`
SELECT id, stripe_checkout_session_id
FROM bookings
WHERE status = 'pending'
AND created_at < datetime('now', '-35 minutes')
ORDER BY created_at
LIMIT 5
`).all<{ id: string; stripe_checkout_session_id: string | null }>();
for (const row of stale.results ?? []) {
if (row.stripe_checkout_session_id && stripeConfigured(env)) {
const sessionMode = stripeCheckoutSessionMode(row.stripe_checkout_session_id);
const configuredMode = stripeMode(env);
// These rows are already older than 35 minutes and Slot creates Checkout
// sessions with a 30-minute expires_at. If the Worker has since switched
// Stripe modes, the current key cannot retrieve the old session, but it is
// safe to release the expired local hold instead of keeping it forever.
const modeMismatch = sessionMode !== 'unknown'
&& configuredMode !== 'unknown'
&& sessionMode !== configuredMode;
if (!modeMismatch) {
try {
const session = await retrieveStripeCheckoutSession(env, row.stripe_checkout_session_id);
if (session.payment_status === 'paid') {
await recordPaidSession(env, row.id, session);
await fulfillPaidBooking(env, row.id);
continue;
}
if (session.status === 'open') continue;
} catch {
// Do not destroy a reservation when Stripe cannot be checked safely.
continue;
}
}
}
await releasePendingBooking(env, settings, row.id).catch(() => undefined);
}
}
async function createBooking(env: Env, httpRequest: Request, request: BookingRequest): Promise<Response> {
if (request.website) return json({ ok: true, message: 'Booked' }); // honeypot
if (!(await enforceBookingRateLimit(httpRequest, env))) {
return json({ error: 'Too many booking attempts from this connection. Try again later.' }, 429);
}
const name = String(request.name ?? '').trim().slice(0, 120);
const email = String(request.email ?? '').trim().toLowerCase().slice(0, 254);
const phone = String(request.phone ?? '').trim().slice(0, 80);
const message = String(request.message ?? '').trim().slice(0, 2000);
const duration = Number(request.duration);
const startMs = Date.parse(request.start);
if (!name || !isEmail(email) || !Number.isFinite(startMs)) {
return json({ error: 'Name, a valid email, and a start time are required.' }, 400);
}
const settings = await getSettings(env);
if (!settings.durations.includes(duration)) return json({ error: 'That duration is not available.' }, 400);
if (!settings.allowMeetingModes.includes(request.meetingMode)) {
return json({ error: 'That meeting type is not available.' }, 400);
}
if (request.meetingMode === 'phone' && !phone) {
return json({ error: 'A phone number is required for a phone meeting.' }, 400);
}
const paymentRequired = settings.paymentEnabled && !settings.paymentFreeDurations.includes(duration);
if (paymentRequired && settings.paymentRatePerMinute <= 0) {
return json({ error: 'Payment is required, but the owner has not configured a per-minute price.' }, 503);
}
if (paymentRequired && !stripeConfigured(env)) {
return json({ error: 'Payment is required, but Stripe is not configured. The owner needs to fix the Stripe setup.' }, 503);
}
const amountMinor = paymentRequired
? bookingAmountMinor(settings.paymentRatePerMinute, duration, settings.paymentCurrency)
: 0;
if (paymentRequired && amountMinor <= 0) {
return json({ error: 'The configured per-minute price is too small for this currency.' }, 409);
}
const dateKey = dateKeyInZone(new Date(startMs), settings.timezone);
const availability = await computeAvailability(env, dateKey);
const matchingSlot = availability.slots.find((slot) => slot.start === new Date(startMs).toISOString());
if (!matchingSlot || !matchingSlot.durations.includes(duration)) {
return json({ error: 'That slot is no longer available. Pick another time.' }, 409);
}
const endMs = addMinutes(startMs, duration);
const lockStart = addMinutes(startMs, -settings.bufferBeforeMinutes);
const lockEnd = addMinutes(endMs, settings.bufferAfterMinutes);
const id = crypto.randomUUID();
const publicBookingId = newPublicBookingId();
const lockExpiry = Date.now() + (paymentRequired ? 35 : 3) * 60_000;
const keys = slotKeys(lockStart, lockEnd, settings.slotIncrementMinutes);
await env.DB.prepare('DELETE FROM slot_locks WHERE expires_at < ?1').bind(Date.now()).run();
const statements: D1PreparedStatement[] = [
env.DB.prepare(`
INSERT INTO bookings (
id, public_booking_id, name, email, phone, message, start_time, end_time,
duration_minutes, timezone, meeting_mode, status,
payment_status, payment_amount, payment_currency
) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, 'pending', ?12, ?13, ?14)
`).bind(
id,
publicBookingId,
name,
email,
phone || null,
message || null,
new Date(startMs).toISOString(),
new Date(endMs).toISOString(),
duration,
settings.timezone,
request.meetingMode,
paymentRequired ? 'awaiting_checkout' : 'not_requested',
paymentRequired ? amountMinor : null,
paymentRequired ? settings.paymentCurrency : null,
),
...keys.map((key) =>
env.DB.prepare('INSERT INTO slot_locks (slot_key, booking_id, expires_at) VALUES (?1, ?2, ?3)')
.bind(key, id, lockExpiry),
),
];
try {
await env.DB.batch(statements);
} catch {
return json({ error: 'Someone just took that time. Please choose another slot.' }, 409);
}
let holdEventId: string | null = null;
try {
// Recheck Google after the D1 reservation. Paid bookings then get a private
// owner-only calendar hold, so the visitor receives no invitation or Meet link
// until Stripe says the payment succeeded.
const fresh = await queryBusy(
env,
settings,
new Date(lockStart).toISOString(),
new Date(lockEnd).toISOString(),
);
const conflicts = fresh.some((range) =>
lockStart < Date.parse(range.end) && lockEnd > Date.parse(range.start),
);
if (conflicts) throw new Error('That slot became busy in Google Calendar.');
if (paymentRequired) {
const hold = await createCalendarHold(env, settings, {
id,
start: new Date(startMs).toISOString(),
end: new Date(endMs).toISOString(),
meetingMode: request.meetingMode,
});
holdEventId = hold.eventId;
await env.DB.prepare(`
UPDATE bookings
SET google_event_id = ?1,
meet_url = ?2,
calendar_sync_status = 'held',
calendar_sync_updated_at = datetime('now'),
updated_at = datetime('now')
WHERE id = ?3
`).bind(hold.eventId, hold.meetUrl, id).run();
const session = await createStripeCheckoutSession(env, {
id,
email,
durationMinutes: duration,
amountMinor,
currency: settings.paymentCurrency,
label: settings.paymentLabel,
});
if (!session.id || !session.url) throw new StripeError('Stripe did not return a Checkout URL.');
await env.DB.prepare(`
UPDATE bookings
SET payment_status = 'checkout_open',
stripe_checkout_session_id = ?1,
updated_at = datetime('now')
WHERE id = ?2
`).bind(session.id, id).run();
await logActivity(env, id, 'payment_checkout_started', {
start: new Date(startMs).toISOString(),
end: new Date(endMs).toISOString(),
duration,
amount: amountMinor,
currency: settings.paymentCurrency,
}, `checkout:${session.id}:opened`);
return json({
ok: true,
requiresPayment: true,
checkoutUrl: session.url,
bookingId: id,
amountMinor,
currency: settings.paymentCurrency,
}, 201);
}
const created = await createCalendarEvent(env, settings, {
id,
publicBookingId,
name,
email,
phone: phone || undefined,
message: message || undefined,
start: new Date(startMs).toISOString(),
end: new Date(endMs).toISOString(),
meetingMode: request.meetingMode,
});
await env.DB.batch([
env.DB.prepare(`
UPDATE bookings
SET status = 'confirmed', google_event_id = ?1, meet_url = ?2,
calendar_sync_status = 'synced', calendar_sync_updated_at = datetime('now'),
updated_at = datetime('now')
WHERE id = ?3
`).bind(created.eventId, created.meetUrl, id),
env.DB.prepare('UPDATE slot_locks SET expires_at = ?1 WHERE booking_id = ?2')
.bind(Date.now() + 10 * 60_000, id),
]);
await logActivity(env, id, 'booking_confirmed', {
start: new Date(startMs).toISOString(),
end: new Date(endMs).toISOString(),
duration,
paid: false,
}, `booking:${id}:confirmed`);
return json({
ok: true,
requiresPayment: false,
booking: {
id,
bookingId: publicBookingId,
start: new Date(startMs).toISOString(),
end: new Date(endMs).toISOString(),
duration,
timezone: settings.timezone,
meetingMode: request.meetingMode,
meetUrl: created.meetUrl,
},
}, 201);
} catch (error) {
if (holdEventId) {
await deleteCalendarEvent(env, settings, holdEventId).catch(() => undefined);
}
await env.DB.batch([
env.DB.prepare('DELETE FROM slot_locks WHERE booking_id = ?1').bind(id),
env.DB.prepare('DELETE FROM bookings WHERE id = ?1').bind(id),
]).catch(() => undefined);
if (error instanceof StripeError) return json({ error: error.message }, error.status);
const message = errorMessage(error);
const status = message.includes('became busy') ? 409 : 502;
return json({ error: message }, status);
}
}
async function createPaymentCheckout(env: Env, bookingId: string): Promise<Response> {
if (!/^[0-9a-f-]{36}$/i.test(bookingId)) return json({ error: 'Invalid booking id.' }, 400);
if (!stripeConfigured(env)) return json({ error: 'Stripe is not configured for this deployment.' }, 503);
const booking = await paymentBookingById(env, bookingId);
if (!booking) return json({ error: 'Booking not found.' }, 404);
if (booking.status === 'confirmed' && booking.payment_status === 'paid') return json({ ok: true, paid: true, booking: publicConfirmation(booking) });
if (booking.status !== 'pending' || !booking.stripe_checkout_session_id) {
return json({ error: 'This booking does not have an active payment checkout.' }, 409);
}
try {
const session = await retrieveStripeCheckoutSession(env, booking.stripe_checkout_session_id);
if (session.payment_status === 'paid') {
await recordPaidSession(env, booking.id, session);
const confirmed = await fulfillPaidBooking(env, booking.id);
return json({ ok: true, paid: true, booking: publicConfirmation(confirmed) });
}
if (session.status === 'open' && session.url) return json({ ok: true, url: session.url });
if (session.status === 'complete') return json({ ok: true, processing: true });
return json({ error: 'That Stripe checkout is no longer active. Please book the slot again.' }, 409);
} catch (error) {
if (error instanceof StripeError) return json({ error: error.message }, error.status);
throw error;
}
}
async function paymentStatus(env: Env, sessionId: string): Promise<Response> {
if (!stripeConfigured(env)) return json({ error: 'Stripe is not configured for this deployment.' }, 503);
try {
const session = await retrieveStripeCheckoutSession(env, sessionId);
const bookingId = session.metadata?.booking_id || session.client_reference_id;
if (!bookingId || !/^[0-9a-f-]{36}$/i.test(bookingId)) {
return json({ state: 'missing' }, 404);
}
const existing = await paymentBookingById(env, bookingId);
if (existing?.status === 'confirmed') {
const receiptUrl = session.payment_status === 'paid'
? await stripeReceiptUrl(env, session).catch(() => null)
: null;
return json({ state: 'confirmed', booking: publicConfirmation(existing, receiptUrl) });
}
if (session.payment_status === 'paid') {
await recordPaidSession(env, bookingId, session);
try {
const confirmed = await fulfillPaidBooking(env, bookingId);
const receiptUrl = await stripeReceiptUrl(env, session).catch(() => null);
return json({ state: 'confirmed', booking: publicConfirmation(confirmed, receiptUrl) });
} catch {
return json({ state: 'confirming', message: 'Payment received. Finalizing your calendar invitation…' });
}
}
if (session.status === 'complete') {
return json({ state: 'processing', message: 'Stripe is still processing the payment.' });
}
if (session.status === 'expired') {
const settings = await getSettings(env);
await releasePendingBooking(env, settings, bookingId).catch(() => undefined);
return json({ state: 'expired', message: 'Payment expired. No booking was confirmed.' });
}
return json({ state: 'awaiting_payment', message: 'Payment has not completed yet.' });
} catch (error) {
if (error instanceof StripeError) return json({ error: error.message }, error.status);
throw error;
}
}
async function cancelPendingPayment(
env: Env,
bookingId: string,
options: { allowStaleStripeRelease?: boolean } = {},
): Promise<Response> {
if (!/^[0-9a-f-]{36}$/i.test(bookingId)) return json({ error: 'Invalid booking id.' }, 400);
const booking = await paymentBookingById(env, bookingId);
if (!booking) return json({ ok: true, state: 'cancelled' });
if (booking.status === 'confirmed') return json({ ok: true, state: 'confirmed', booking: publicConfirmation(booking) });
if (booking.status === 'confirming') return json({ ok: true, state: 'processing' });
if (booking.status !== 'pending') return json({ ok: true, state: booking.status });
const settings = await getSettings(env);
let releaseWarning: string | null = null;
const sessionId = booking.stripe_checkout_session_id;
if (sessionId && stripeConfigured(env)) {
const sessionMode = stripeCheckoutSessionMode(sessionId);
const configuredMode = stripeMode(env);
const modeMismatch = sessionMode !== 'unknown'
&& configuredMode !== 'unknown'
&& sessionMode !== configuredMode;
if (modeMismatch) {
if (!options.allowStaleStripeRelease) {
return json({
error: `This payment hold was created in Stripe ${sessionMode} mode, but this deployment is using ${configuredMode} mode. Switch back to the ${sessionMode} key to expire that Checkout session, or release it from Admin.`,
code: 'stripe_mode_mismatch',
sessionMode,
configuredMode,
}, 409);
}
releaseWarning = `Released locally. The hold belongs to Stripe ${sessionMode} mode while this Worker uses ${configuredMode} mode, so Slot could not expire the old Stripe Checkout session.`;
} else {
try {
const session = await retrieveStripeCheckoutSession(env, sessionId);
if (session.payment_status === 'paid') {
await recordPaidSession(env, booking.id, session);
const confirmed = await fulfillPaidBooking(env, booking.id);
return json({ ok: true, state: 'confirmed', booking: publicConfirmation(confirmed) });
}
if (session.status === 'complete') {
return json({ ok: true, state: 'processing' });
}
if (session.status === 'open') await expireStripeCheckoutSession(env, session.id);
} catch (error) {
if (error instanceof StripeError) {
// A resource_missing error is what Stripe returns when a Checkout
// session belongs to another account/mode. Admin has already made an
// explicit release decision, so let it clean up the local/calendar hold
// instead of trapping the slot behind a stale Stripe id.
if (options.allowStaleStripeRelease && (error.code === 'resource_missing' || /No such checkout\.session/i.test(error.message))) {
releaseWarning = 'Released locally because Stripe could not find the stored Checkout session with the currently configured key.';
} else {
return json({ error: error.message }, error.status);
}
} else {
throw error;
}
}
}
} else if (sessionId && options.allowStaleStripeRelease) {
releaseWarning = 'Released locally. Stripe is not configured on this deployment, so Slot could not expire the stored Checkout session.';
}
await releasePendingBooking(env, settings, booking.id);
return json({ ok: true, state: 'cancelled', warning: releaseWarning });
}
async function paymentCancelReturn(env: Env, bookingId: string): Promise<Response> {
const appUrl = env.APP_URL.replace(/\/+$/, '');
if (!/^[0-9a-f-]{36}$/i.test(bookingId)) {
return redirect(`${appUrl}/?payment=cancelled&release_error=invalid_booking`);
}
const before = await paymentBookingById(env, bookingId);
const sessionId = before?.stripe_checkout_session_id ?? null;
const response = await cancelPendingPayment(env, bookingId);
const payload = await response.clone().json().catch(() => ({})) as { state?: string };
if (response.ok && (payload.state === 'confirmed' || payload.state === 'processing') && sessionId) {
return redirect(`${appUrl}/?payment=success&session_id=${encodeURIComponent(sessionId)}`);
}
if (response.ok && payload.state === 'cancelled') {
return redirect(`${appUrl}/?payment=cancelled&released=1`);
}
// Keep the booking id only on the error fallback so the browser can retry the
// release through the normal POST endpoint and show a useful error if needed.
return redirect(`${appUrl}/?payment=cancelled&booking_id=${encodeURIComponent(bookingId)}&release_error=1`);
}
async function handleStripeWebhook(request: Request, env: Env): Promise<Response> {
const rawBody = await request.text();
try {
const event = await verifyStripeWebhook(env, rawBody, request.headers.get('Stripe-Signature'));
const object = event.data?.object;
if (!object) return json({ received: true });
if (object.object === 'refund') {
const refund = object as StripeRefund;
const bookingId = refund.metadata?.booking_id;
if (bookingId && /^[0-9a-f-]{36}$/i.test(bookingId)) {
const status = event.type === 'refund.failed' ? 'failed' : (refund.status ?? 'pending');
await env.DB.prepare(`
UPDATE bookings
SET refund_status = ?1,
stripe_refund_id = COALESCE(stripe_refund_id, ?2),
refunded_amount = COALESCE(?3, refunded_amount),
refunded_at = CASE WHEN ?1 = 'succeeded' THEN COALESCE(refunded_at, datetime('now')) ELSE refunded_at END,
updated_at = datetime('now')
WHERE id = ?4
`).bind(status, refund.id, refund.amount ?? null, bookingId).run();
await logActivity(env, bookingId,
status === 'succeeded' ? 'refund_succeeded' : status === 'failed' ? 'refund_failed' : 'refund_started',
{ amount: refund.amount ?? null, currency: refund.currency ?? null, refundStatus: status },
`refund:${refund.id}:${status}`,
);
}
return json({ received: true });
}
if (object.object !== 'checkout.session') return json({ received: true });
const session = object;
const bookingId = session.metadata?.booking_id || session.client_reference_id;
if (!bookingId || !/^[0-9a-f-]{36}$/i.test(bookingId)) return json({ received: true });
if (event.type === 'checkout.session.completed' || event.type === 'checkout.session.async_payment_succeeded') {
const paid = session.payment_status === 'paid' || event.type === 'checkout.session.async_payment_succeeded';
if (paid) {
await recordPaidSession(env, bookingId, session);
await fulfillPaidBooking(env, bookingId);
} else {
await env.DB.prepare(`
UPDATE bookings
SET payment_status = CASE WHEN payment_status = 'paid' THEN 'paid' ELSE 'processing' END,
payment_amount = COALESCE(?1, payment_amount),
payment_currency = COALESCE(?2, payment_currency),
stripe_payment_intent_id = COALESCE(?3, stripe_payment_intent_id),
updated_at = datetime('now')
WHERE id = ?4
AND stripe_checkout_session_id = ?5
`).bind(
session.amount_total ?? null,
session.currency ?? null,
stripePaymentIntentId(session),
bookingId,
session.id,
).run();
}
} else if (event.type === 'checkout.session.async_payment_failed' || event.type === 'checkout.session.expired') {
const settings = await getSettings(env);
await releasePendingBooking(env, settings, bookingId);
}
return json({ received: true });
} catch (error) {
if (error instanceof StripeError) return json({ error: error.message }, error.status);
throw error;
}
}
async function assertRescheduleSlotAvailable(
env: Env,
booking: PaymentBookingRow,
startIso: string,
): Promise<{ settings: AppSettings; startMs: number; endMs: number }> {
const startMs = Date.parse(startIso);
if (!Number.isFinite(startMs) || startMs <= Date.now()) throw new Error('Choose a future time.');
const settings = await getSettings(env);
const dateKey = dateKeyInZone(new Date(startMs), settings.timezone);
const availability = await computeAvailability(env, dateKey);
const matching = availability.slots.find((slot) => slot.start === new Date(startMs).toISOString());
if (!matching || !matching.durations.includes(booking.duration_minutes)) {
throw new Error('That suggested time is no longer available. Choose another time.');
}
return { settings, startMs, endMs: addMinutes(startMs, booking.duration_minutes) };
}
async function rescheduleConfirmedBooking(
env: Env,
booking: PaymentBookingRow,
startIso: string,
source: 'admin' | 'guest_suggestion' | 'guest_self' = 'admin',
): Promise<PaymentBookingRow> {
if (booking.status !== 'confirmed') throw new Error('Only confirmed bookings can be rescheduled.');
if (!booking.google_event_id) throw new Error('This booking is missing its Google Calendar event.');
const { settings, startMs, endMs } = await assertRescheduleSlotAvailable(env, booking, startIso);
const lockStart = addMinutes(startMs, -settings.bufferBeforeMinutes);
const lockEnd = addMinutes(endMs, settings.bufferAfterMinutes);
const keys = slotKeys(lockStart, lockEnd, settings.slotIncrementMinutes);
await env.DB.prepare('DELETE FROM slot_locks WHERE expires_at < ?1').bind(Date.now()).run();
await env.DB.prepare('DELETE FROM slot_locks WHERE booking_id = ?1').bind(booking.id).run();
try {
await env.DB.batch(keys.map((key) =>
env.DB.prepare('INSERT INTO slot_locks (slot_key, booking_id, expires_at) VALUES (?1, ?2, ?3)')
.bind(key, booking.id, Date.now() + 3 * 60_000),
));
} catch {
throw new Error('Someone just took that time. Choose another slot.');
}
try {
const fresh = await queryBusy(env, settings, new Date(lockStart).toISOString(), new Date(lockEnd).toISOString());
const conflicts = fresh.some((range) => lockStart < Date.parse(range.end) && lockEnd > Date.parse(range.start));
if (conflicts) throw new Error('That time became busy in Google Calendar. Choose another slot.');
const updated = await rescheduleCalendarEvent(env, settings, booking.google_event_id, {
phone: booking.phone ?? undefined,
message: booking.message ?? undefined,
paid: booking.payment_status === 'paid',
publicBookingId: booking.public_booking_id ?? undefined,
start: new Date(startMs).toISOString(),
end: new Date(endMs).toISOString(),
});
try {
await env.DB.batch([
env.DB.prepare(`
UPDATE bookings
SET start_time = ?1,
end_time = ?2,
meet_url = COALESCE(?3, meet_url),
rescheduled_at = datetime('now'),
reschedule_count = reschedule_count + 1,
proposed_start_time = NULL,
proposed_end_time = NULL,
proposed_at = NULL,
proposal_status = NULL,
proposal_token_hash = NULL,
calendar_sync_status = 'synced',
calendar_sync_error = NULL,
calendar_sync_updated_at = datetime('now'),
updated_at = datetime('now')
WHERE id = ?4 AND status = 'confirmed'
`).bind(new Date(startMs).toISOString(), new Date(endMs).toISOString(), updated.meetUrl, booking.id),
env.DB.prepare('UPDATE slot_locks SET expires_at = ?1 WHERE booking_id = ?2')
.bind(Date.now() + 10 * 60_000, booking.id),
]);
} catch (error) {
await rescheduleCalendarEvent(env, settings, booking.google_event_id, {
phone: booking.phone ?? undefined,
message: booking.message ?? undefined,
paid: booking.payment_status === 'paid',
publicBookingId: booking.public_booking_id ?? undefined,
start: booking.start_time,
end: booking.end_time,
}).catch(() => undefined);
throw error;
}
} catch (error) {
await env.DB.prepare('DELETE FROM slot_locks WHERE booking_id = ?1').bind(booking.id).run().catch(() => undefined);
throw error;
}
const result = await paymentBookingById(env, booking.id);
if (!result) throw new Error('Rescheduled booking record was not found.');
await logActivity(env, booking.id, source === 'guest_suggestion' ? 'time_suggestion_accepted' : 'booking_rescheduled', {
source,
oldStart: booking.start_time,
oldEnd: booking.end_time,
newStart: result.start_time,
newEnd: result.end_time,
duration: booking.duration_minutes,
}, `booking:${booking.id}:reschedule:${result.reschedule_count}`);
return result;
}
async function adminRescheduleBooking(env: Env, bookingId: string, start: string): Promise<Response> {
if (!/^[0-9a-f-]{36}$/i.test(bookingId)) return json({ error: 'Invalid booking id.' }, 400);
const booking = await paymentBookingById(env, bookingId);
if (!booking) return json({ error: 'Booking not found.' }, 404);
try {
const updated = await rescheduleConfirmedBooking(env, booking, start, 'admin');
return json({ ok: true, booking: publicConfirmation(updated) });
} catch (error) {
if (error instanceof GoogleCalendarError) return json({ error: error.publicMessage }, 502);
return json({ error: errorMessage(error) }, /available|busy|future|took/i.test(errorMessage(error)) ? 409 : 400);
}
}
async function adminSuggestNewTime(env: Env, bookingId: string, start: string): Promise<Response> {
if (!/^[0-9a-f-]{36}$/i.test(bookingId)) return json({ error: 'Invalid booking id.' }, 400);
const booking = await paymentBookingById(env, bookingId);
if (!booking) return json({ error: 'Booking not found.' }, 404);
if (booking.status !== 'confirmed') return json({ error: 'Only confirmed bookings can receive a new-time suggestion.' }, 409);
if (!booking.google_event_id) return json({ error: 'This booking is missing its Google Calendar event.' }, 409);
try {
const { settings, startMs, endMs } = await assertRescheduleSlotAvailable(env, booking, start);
const token = randomUrlToken();
const tokenHash = await sha256Base64Url(token);
const suggestionUrl = `${env.APP_URL.replace(/\/+$/, '')}/reschedule/${encodeURIComponent(token)}`;
await env.DB.prepare(`
UPDATE bookings
SET proposed_start_time = ?1,
proposed_end_time = ?2,
proposed_at = datetime('now'),
proposal_status = 'pending',
proposal_token_hash = ?3,
updated_at = datetime('now')
WHERE id = ?4 AND status = 'confirmed'
`).bind(new Date(startMs).toISOString(), new Date(endMs).toISOString(), tokenHash, booking.id).run();
try {
await sendRescheduleSuggestion(env, settings, booking.google_event_id, {
phone: booking.phone ?? undefined,
message: booking.message ?? undefined,
paid: booking.payment_status === 'paid',
publicBookingId: booking.public_booking_id ?? undefined,
}, {
start: new Date(startMs).toISOString(),
end: new Date(endMs).toISOString(),
url: suggestionUrl,
});
} catch (error) {
await env.DB.prepare(`
UPDATE bookings
SET proposed_start_time = NULL, proposed_end_time = NULL, proposed_at = NULL,
proposal_status = NULL, proposal_token_hash = NULL, updated_at = datetime('now')
WHERE id = ?1
`).bind(booking.id).run().catch(() => undefined);
throw error;
}
await logActivity(env, booking.id, 'time_suggested', {
currentStart: booking.start_time,
currentEnd: booking.end_time,
proposedStart: new Date(startMs).toISOString(),
proposedEnd: new Date(endMs).toISOString(),
duration: booking.duration_minutes,
}, `suggest:${tokenHash}`);
return json({
ok: true,
suggestionUrl,
proposedStart: new Date(startMs).toISOString(),
proposedEnd: new Date(endMs).toISOString(),
});
} catch (error) {
if (error instanceof GoogleCalendarError) return json({ error: error.publicMessage }, 502);
return json({ error: errorMessage(error) }, /available|busy|future/i.test(errorMessage(error)) ? 409 : 400);
}
}
async function proposalBookingByToken(env: Env, token: string): Promise<PaymentBookingRow | null> {
if (!/^[A-Za-z0-9_-]{20,200}$/.test(token)) return null;
const hash = await sha256Base64Url(token);
return env.DB.prepare(`
SELECT id, public_booking_id, name, email, phone, message, start_time, end_time, duration_minutes,
timezone, meeting_mode, status, google_event_id, meet_url, payment_status,
payment_amount, payment_currency, stripe_checkout_session_id, stripe_payment_intent_id, paid_at,
rescheduled_at, reschedule_count, proposed_start_time, proposed_end_time, proposed_at,
proposal_status, proposal_token_hash, refund_status, stripe_refund_id, refunded_amount,
refunded_at, updated_at
FROM bookings
WHERE proposal_token_hash = ?1
AND proposal_status = 'pending'
`).bind(hash).first<PaymentBookingRow>();
}
async function getRescheduleProposal(env: Env, token: string): Promise<Response> {
const booking = await proposalBookingByToken(env, token);
if (!booking || !booking.proposed_start_time || !booking.proposed_end_time) {
return json({ error: 'This reschedule suggestion is invalid or no longer active.' }, 404);
}
const settings = await getSettings(env);
return json({
title: settings.title,
name: booking.name,
currentStart: booking.start_time,
currentEnd: booking.end_time,
proposedStart: booking.proposed_start_time,
proposedEnd: booking.proposed_end_time,
duration: booking.duration_minutes,
timezone: booking.timezone,
meetingMode: booking.meeting_mode,
});
}
async function acceptRescheduleProposal(env: Env, token: string): Promise<Response> {
const booking = await proposalBookingByToken(env, token);
if (!booking || !booking.proposed_start_time) {
return json({ error: 'This reschedule suggestion is invalid or no longer active.' }, 404);
}
try {
const updated = await rescheduleConfirmedBooking(env, booking, booking.proposed_start_time, 'guest_suggestion');
return json({ ok: true, booking: publicConfirmation(updated) });
} catch (error) {
if (error instanceof GoogleCalendarError) return json({ error: error.publicMessage }, 502);
return json({ error: errorMessage(error) }, /available|busy|future|took/i.test(errorMessage(error)) ? 409 : 400);
}
}
async function cancelBooking(env: Env, bookingId: string, allowStaleStripeRelease = false): Promise<Response> {
if (!/^[0-9a-f-]{36}$/i.test(bookingId)) return json({ error: 'Invalid booking id.' }, 400);
const booking = await paymentBookingById(env, bookingId);
if (!booking) return json({ error: 'Booking not found.' }, 404);
if (booking.status === 'cancelled') {
return json({
ok: true,
refunded: booking.refund_status === 'succeeded' || booking.refund_status === 'pending',
refundStatus: booking.refund_status,
});
}
if (booking.status === 'pending' && booking.payment_status !== 'not_requested') {
return cancelPendingPayment(env, bookingId, { allowStaleStripeRelease });
}
if (booking.status !== 'confirmed') return json({ error: 'Only confirmed bookings or pending payment holds can be cancelled.' }, 409);
let refundStatus = booking.refund_status;
let refunded = refundStatus === 'succeeded' || refundStatus === 'pending';
let refundWarning: string | null = null;
if (booking.payment_status === 'paid' && !refunded) {
if (!stripeConfigured(env)) {
return json({ error: 'This booking was paid, but Stripe is not configured. Slot will not cancel it without attempting the refund.' }, 503);
}
const sessionMode = stripeCheckoutSessionMode(booking.stripe_checkout_session_id);
const configuredMode = stripeMode(env);
if (sessionMode !== 'unknown' && configuredMode !== 'unknown' && sessionMode !== configuredMode) {
return json({
error: `This booking was paid in Stripe ${sessionMode} mode, but Slot is currently using ${configuredMode} mode. Switch to the ${sessionMode} Stripe key before cancelling so Slot can refund the payment.`,
code: 'stripe_mode_mismatch',
}, 409);
}
let paymentIntentId = booking.stripe_payment_intent_id;
if (!paymentIntentId && booking.stripe_checkout_session_id) {
try {
const session = await retrieveStripeCheckoutSession(env, booking.stripe_checkout_session_id);
paymentIntentId = stripePaymentIntentId(session);
if (paymentIntentId) {
await env.DB.prepare(`
UPDATE bookings
SET stripe_payment_intent_id = ?1, updated_at = datetime('now')
WHERE id = ?2
`).bind(paymentIntentId, booking.id).run();
}
} catch (error) {
if (error instanceof StripeError) return json({ error: error.message }, error.status);
throw error;
}
}
if (!paymentIntentId) {
return json({ error: 'This paid booking is missing its Stripe PaymentIntent, so Slot cannot safely refund it.' }, 409);
}
try {
const refund = await createStripeRefund(env, paymentIntentId, booking.id);
refundStatus = refund.status ?? 'pending';
if (refundStatus === 'failed' || refundStatus === 'canceled' || refundStatus === 'requires_action') {
await env.DB.prepare(`
UPDATE bookings
SET refund_status = ?1,
stripe_refund_id = ?2,
refunded_amount = ?3,
updated_at = datetime('now')
WHERE id = ?4
`).bind(refundStatus, refund.id, refund.amount, booking.id).run();
await logActivity(env, booking.id, 'refund_failed', {
amount: refund.amount,
currency: booking.payment_currency,
refundStatus,
}, `refund:${refund.id}:${refundStatus}`);
return json({ error: `Stripe created refund ${refund.id}, but its status is ${refundStatus}. The booking has not been cancelled.` }, 502);
}
refunded = true;
await env.DB.prepare(`
UPDATE bookings
SET refund_status = ?1,
stripe_refund_id = ?2,
refunded_amount = ?3,
refunded_at = CASE WHEN ?1 = 'succeeded' THEN datetime('now') ELSE refunded_at END,
updated_at = datetime('now')
WHERE id = ?4
`).bind(refundStatus, refund.id, refund.amount, booking.id).run();
await logActivity(env, booking.id, refundStatus === 'succeeded' ? 'refund_succeeded' : 'refund_started', {
amount: refund.amount,
currency: booking.payment_currency,
refundStatus,
}, `refund:${refund.id}:${refundStatus}`);
if (refundStatus === 'pending') {
refundWarning = 'Stripe accepted the refund, but it is still pending.';
}
} catch (error) {
if (error instanceof StripeError) return json({ error: `Stripe refund failed: ${error.message}` }, error.status);
throw error;
}
}
const settings = await getSettings(env);
let calendarWarning: string | null = null;
try {
if (booking.google_event_id) await deleteCalendarEvent(env, settings, booking.google_event_id);
} catch (error) {
const message = privateCalendarError(error);
if (!refunded) {
await env.DB.prepare(`
UPDATE bookings
SET calendar_sync_status = 'failed',
calendar_sync_error = ?1,
calendar_sync_updated_at = datetime('now'),
updated_at = datetime('now')
WHERE id = ?2
`).bind(message, bookingId).run();
return json({ error: 'Slot could not cancel the Google Calendar event. The booking has not been cancelled.' }, 502);
}
calendarWarning = 'The Stripe refund was created, but Google Calendar could not be updated. Remove the calendar event manually.';
}
await env.DB.batch([
env.DB.prepare(`
UPDATE bookings
SET status = 'cancelled',
cancelled_at = datetime('now'),
proposed_start_time = NULL,
proposed_end_time = NULL,
proposed_at = NULL,
proposal_status = NULL,
proposal_token_hash = NULL,
calendar_sync_status = ?1,
calendar_sync_error = ?2,
calendar_sync_updated_at = datetime('now'),
updated_at = datetime('now')
WHERE id = ?3
`).bind(calendarWarning ? 'failed' : 'synced', calendarWarning, bookingId),
env.DB.prepare('DELETE FROM slot_locks WHERE booking_id = ?1').bind(bookingId),
]);
await logActivity(env, bookingId, 'booking_cancelled', {
start: booking.start_time,
end: booking.end_time,
duration: booking.duration_minutes,
refunded,
refundStatus,
}, `booking:${bookingId}:cancelled`);
return json({
ok: true,
refunded,
refundStatus,
warning: [refundWarning, calendarWarning].filter(Boolean).join(' ') || undefined,
});
}
async function publicCancelBooking(request: Request, env: Env, value: unknown): Promise<Response> {
if (!(await enforceCancellationRateLimit(request, env))) {
return json({ error: 'Too many cancellation attempts. Try again later.' }, 429);
}
const publicBookingId = normalizePublicBookingId(value);
if (!publicBookingId) return json({ error: 'Enter a valid Slot booking ID.' }, 400);
const row = await env.DB.prepare(`
SELECT id, status, end_time
FROM bookings
WHERE public_booking_id = ?1
`).bind(publicBookingId).first<{ id: string; status: string; end_time: string }>();
if (!row) return json({ error: 'Booking ID not found.' }, 404);
if (row.status === 'cancelled') return json({ ok: true, alreadyCancelled: true });
if (row.status !== 'confirmed') return json({ error: 'This booking is not currently confirmed.' }, 409);
if (Date.parse(row.end_time) <= Date.now()) return json({ error: 'Past bookings cannot be cancelled.' }, 409);
return cancelBooking(env, row.id);
}
async function publicManageBooking(request: Request, env: Env, value: unknown): Promise<Response> {
if (!(await enforceManageRateLimit(request, env))) {
return json({ error: 'Too many booking management attempts. Try again later.' }, 429);
}
const publicBookingId = normalizePublicBookingId(value);
if (!publicBookingId) return json({ error: 'Enter a valid Slot booking ID.' }, 400);
const booking = await bookingByPublicId(env, publicBookingId);
if (!booking) return json({ error: 'Booking ID not found.' }, 404);
return json({ booking: publicManagePayload(booking) });
}
async function publicManageAvailability(
request: Request,
env: Env,
value: unknown,
date: unknown,
): Promise<Response> {
if (!(await enforceManageRateLimit(request, env))) {
return json({ error: 'Too many booking management attempts. Try again later.' }, 429);
}
const publicBookingId = normalizePublicBookingId(value);
if (!publicBookingId) return json({ error: 'Enter a valid Slot booking ID.' }, 400);
const booking = await bookingByPublicId(env, publicBookingId);
if (!booking) return json({ error: 'Booking ID not found.' }, 404);
if (booking.status !== 'confirmed' || Date.parse(booking.end_time) <= Date.now()) {
return json({ error: 'Only upcoming confirmed bookings can be rescheduled.' }, 409);
}
const dateKey = String(date ?? '').trim();
try {
const availability = await computeAvailability(env, dateKey);
const slots = availability.slots
.filter((slot) => slot.durations.includes(booking.duration_minutes))
.map((slot) => ({ start: slot.start, time: slot.time }));
return json({ date: dateKey, timezone: availability.settings.timezone, duration: booking.duration_minutes, slots });
} catch (error) {
if (error instanceof GoogleCalendarError) return json({ error: error.publicMessage }, 502);
return json({ error: errorMessage(error) }, 400);
}
}
async function publicRescheduleBooking(
request: Request,
env: Env,
value: unknown,
start: unknown,
): Promise<Response> {
if (!(await enforceManageRateLimit(request, env))) {
return json({ error: 'Too many booking management attempts. Try again later.' }, 429);
}
const publicBookingId = normalizePublicBookingId(value);
if (!publicBookingId) return json({ error: 'Enter a valid Slot booking ID.' }, 400);
const booking = await bookingByPublicId(env, publicBookingId);
if (!booking) return json({ error: 'Booking ID not found.' }, 404);
if (booking.status !== 'confirmed' || Date.parse(booking.end_time) <= Date.now()) {
return json({ error: 'Only upcoming confirmed bookings can be rescheduled.' }, 409);
}
try {
const updated = await rescheduleConfirmedBooking(env, booking, String(start ?? ''), 'guest_self');
return json({ ok: true, booking: publicManagePayload(updated) });
} catch (error) {
if (error instanceof GoogleCalendarError) return json({ error: error.publicMessage }, 502);
const message = errorMessage(error);
return json({ error: message }, /available|busy|future|took/i.test(message) ? 409 : 400);
}
}
function cleanLabel(value: unknown): string | null {
const label = String(value ?? '').trim().slice(0, 80);
return label || null;
}
async function listAvailabilityRules(env: Env): Promise<Response> {
const recurring = await env.DB.prepare(`
SELECT id, weekdays, start_time, end_time, label
FROM recurring_unavailable_periods
ORDER BY start_time
`).all<{ id: string; weekdays: string; start_time: string; end_time: string; label: string | null }>();
const blocks = await env.DB.prepare(`
SELECT id, date, start_time, end_time, all_day, label
FROM availability_blocks
ORDER BY date DESC, all_day DESC, start_time
LIMIT 100
`).all<{ id: string; date: string; start_time: string | null; end_time: string | null; all_day: number; label: string | null }>();
return json({
recurring: (recurring.results ?? []).map((row) => ({
id: row.id,
weekdays: row.weekdays.split(',').map(Number).filter((day) => day >= 0 && day <= 6),
start: row.start_time,
end: row.end_time,
label: row.label,
})),
blocks: (blocks.results ?? []).map((row) => ({
id: row.id,
date: row.date,
start: row.start_time,
end: row.end_time,
allDay: row.all_day === 1,
label: row.label,
})),
});
}
async function createRecurringUnavailable(env: Env, input: unknown): Promise<Response> {
const raw = input as Partial<{ weekdays: unknown[]; start: string; end: string; label: string }>;
const weekdays = Array.isArray(raw.weekdays)
? [...new Set(raw.weekdays.map(Number).filter((day) => Number.isInteger(day) && day >= 0 && day <= 6))].sort((a, b) => a - b)
: [];
const start = String(raw.start ?? '');
const end = String(raw.end ?? '');
if (!weekdays.length || !isTime(start) || !isTime(end) || timeToMinutes(end) <= timeToMinutes(start)) {
return json({ error: 'Choose weekdays plus a valid start and end time.' }, 400);
}
const id = crypto.randomUUID();
await env.DB.prepare(`
INSERT INTO recurring_unavailable_periods (id, weekdays, start_time, end_time, label)
VALUES (?1, ?2, ?3, ?4, ?5)
`).bind(id, weekdays.join(','), start, end, cleanLabel(raw.label)).run();
return json({ ok: true, id }, 201);
}
async function createAvailabilityBlock(env: Env, input: unknown): Promise<Response> {
const raw = input as Partial<{ date: string; start: string; end: string; allDay: boolean; label: string }>;
const date = String(raw.date ?? '');
const allDay = Boolean(raw.allDay);
const start = String(raw.start ?? '');
const end = String(raw.end ?? '');
if (!/^\d{4}-\d{2}-\d{2}$/.test(date)) return json({ error: 'Choose a valid date.' }, 400);
if (!allDay && (!isTime(start) || !isTime(end) || timeToMinutes(end) <= timeToMinutes(start))) {
return json({ error: 'Choose a valid start and end time, or mark the block all day.' }, 400);
}
const id = crypto.randomUUID();
await env.DB.prepare(`
INSERT INTO availability_blocks (id, date, start_time, end_time, all_day, label)
VALUES (?1, ?2, ?3, ?4, ?5, ?6)
`).bind(id, date, allDay ? null : start, allDay ? null : end, allDay ? 1 : 0, cleanLabel(raw.label)).run();
return json({ ok: true, id }, 201);
}
async function deleteAvailabilityRule(env: Env, kind: string, id: string): Promise<Response> {
if (!/^[0-9a-f-]{36}$/i.test(id)) return json({ error: 'Invalid rule id.' }, 400);
if (kind === 'recurring') {
await env.DB.prepare('DELETE FROM recurring_unavailable_periods WHERE id = ?1').bind(id).run();
return json({ ok: true });
}
if (kind === 'block') {
await env.DB.prepare('DELETE FROM availability_blocks WHERE id = ?1').bind(id).run();
return json({ ok: true });
}
return json({ error: 'Invalid availability rule type.' }, 400);
}
async function handleApi(request: Request, env: Env, url: URL): Promise<Response> {
if (request.method === 'GET' && url.pathname === '/api/config') {
const settings = await getSettings(env);
const connected = await hasGoogleConnection(env);
const paymentRequired = settings.paymentEnabled;
return json({
title: settings.title,
subtitle: settings.subtitle,
timezone: settings.timezone,
bookingWindowDays: settings.bookingWindowDays,
durations: settings.durations,
defaultMeetingMode: settings.defaultMeetingMode,
allowMeetingModes: settings.allowMeetingModes,
availableWeekdays: Object.entries(settings.availability)
.filter(([, intervals]) => intervals.length > 0)
.map(([day]) => Number(day)),
connected,
payment: paymentRequired ? {
enabled: true,
required: settings.paymentFreeDurations.length === 0,
ready: stripeConfigured(env) && settings.paymentRatePerMinute > 0,
ratePerMinute: settings.paymentRatePerMinute,
freeDurations: settings.paymentFreeDurations,
currency: settings.paymentCurrency,
label: settings.paymentLabel,
} : undefined,
});
}
if (request.method === 'GET' && url.pathname === '/api/availability') {
const date = url.searchParams.get('date') ?? '';
try {
const result = await computeAvailability(env, date);
return json({
date: result.date,
timezone: result.settings.timezone,
connected: result.connected,
slots: result.slots,
});
} catch (error) {
if (error instanceof GoogleCalendarError) {
const settings = await getSettings(env);
return json({
date,
timezone: settings.timezone,
connected: true,
slots: [],
calendarError: error.publicMessage,
});
}
throw error;
}
}
if (request.method === 'POST' && url.pathname === '/api/book') {
const body = (await request.json()) as BookingRequest;
return createBooking(env, request, body);
}
if (request.method === 'POST' && url.pathname === '/api/bookings/cancel') {
const body = await request.json().catch(() => ({})) as { bookingId?: string };
return publicCancelBooking(request, env, body.bookingId);
}
if (request.method === 'POST' && url.pathname === '/api/bookings/manage') {
const body = await request.json().catch(() => ({})) as { bookingId?: string };
return publicManageBooking(request, env, body.bookingId);
}
if (request.method === 'POST' && url.pathname === '/api/bookings/manage/availability') {
const body = await request.json().catch(() => ({})) as { bookingId?: string; date?: string };
return publicManageAvailability(request, env, body.bookingId, body.date);
}
if (request.method === 'POST' && url.pathname === '/api/bookings/reschedule') {
const body = await request.json().catch(() => ({})) as { bookingId?: string; start?: string };
return publicRescheduleBooking(request, env, body.bookingId, body.start);
}
if (request.method === 'POST' && url.pathname === '/api/payments/checkout') {
const body = (await request.json()) as { bookingId?: string };
return createPaymentCheckout(env, String(body.bookingId ?? ''));
}
if (request.method === 'GET' && url.pathname === '/api/payments/status') {
return paymentStatus(env, String(url.searchParams.get('session_id') ?? ''));
}
if (request.method === 'GET' && url.pathname === '/api/payments/cancel-return') {
return paymentCancelReturn(env, String(url.searchParams.get('booking_id') ?? ''));
}
if (request.method === 'POST' && url.pathname === '/api/payments/cancel') {
const body = (await request.json()) as { bookingId?: string };
return cancelPendingPayment(env, String(body.bookingId ?? ''));
}
if (request.method === 'POST' && url.pathname === '/api/stripe/webhook') {
return handleStripeWebhook(request, env);
}
const proposalMatch = url.pathname.match(/^\/api\/reschedule-suggestions\/([^/]+)$/);
if (request.method === 'GET' && proposalMatch) {
return getRescheduleProposal(env, decodeURIComponent(proposalMatch[1]));
}
const proposalAcceptMatch = url.pathname.match(/^\/api\/reschedule-suggestions\/([^/]+)\/accept$/);
if (request.method === 'POST' && proposalAcceptMatch) {
return acceptRescheduleProposal(env, decodeURIComponent(proposalAcceptMatch[1]));
}
if (url.pathname.startsWith('/api/admin/')) {
const admin = await requireAdmin(request, env);
if (!admin) return json({ error: 'Unauthorized' }, 401);
if (request.method === 'GET' && url.pathname === '/api/admin/session') {
const connected = await hasGoogleConnection(env);
return json({ authenticated: true, email: admin, connected });
}
if (request.method === 'GET' && url.pathname === '/api/admin/settings') {
return json({
settings: await getSettings(env),
stripeConfigured: stripeConfigured(env),
stripeMode: stripeMode(env),
});
}
if (request.method === 'PUT' && url.pathname === '/api/admin/settings') {
const settings = validateAppSettings(await request.json());
await saveSettings(env, settings);
return json({ ok: true, settings });
}
if (request.method === 'GET' && url.pathname === '/api/admin/activity') {
return adminActivity(env);
}
if (request.method === 'GET' && url.pathname === '/api/admin/attendees') {
return adminAttendees(env);
}
if (request.method === 'GET' && url.pathname === '/api/admin/attendee-bookings') {
return adminAttendeeBookings(env, url.searchParams.get('email') ?? '');
}
if (request.method === 'GET' && url.pathname === '/api/admin/bookings') {
const settings = await getSettings(env);
await cleanupStalePendingBookings(env, settings);
const query = (url.searchParams.get('q') ?? '').trim().toLowerCase();
const select = `
SELECT id, public_booking_id, name, email, start_time, end_time, duration_minutes,
meeting_mode, status, meet_url, created_at,
cancelled_at, calendar_provider, calendar_sync_status,
calendar_sync_error, calendar_sync_updated_at,
payment_status, payment_amount, payment_currency,
stripe_checkout_session_id, stripe_payment_intent_id, paid_at,
rescheduled_at, reschedule_count, proposed_start_time, proposed_end_time,
proposed_at, proposal_status, refund_status, stripe_refund_id,
refunded_amount, refunded_at
FROM bookings
`;
const result = query
? await env.DB.prepare(`${select}
WHERE lower(COALESCE(public_booking_id, '')) LIKE ?1
OR lower(id) LIKE ?1
OR lower(name) LIKE ?1
OR lower(email) LIKE ?1
ORDER BY start_time DESC
LIMIT 100
`).bind(`%${query}%`).all()
: await env.DB.prepare(`${select}
ORDER BY start_time DESC
LIMIT 100
`).all();
return json({ bookings: result.results ?? [] });
}
const cancelMatch = url.pathname.match(/^\/api\/admin\/bookings\/([^/]+)\/cancel$/);
if (request.method === 'POST' && cancelMatch) {
const body = await request.json().catch(() => ({})) as { allowStaleStripeRelease?: boolean };
return cancelBooking(env, cancelMatch[1], body.allowStaleStripeRelease === true);
}
const bookingAvailabilityMatch = url.pathname.match(/^\/api\/admin\/bookings\/([^/]+)\/availability$/);
if (request.method === 'GET' && bookingAvailabilityMatch) {
const booking = await paymentBookingById(env, bookingAvailabilityMatch[1]);
if (!booking) return json({ error: 'Booking not found.' }, 404);
if (booking.status !== 'confirmed') return json({ error: 'Only confirmed bookings can be rescheduled.' }, 409);
const date = url.searchParams.get('date') ?? '';
try {
const result = await computeAvailability(env, date);
return json({
date: result.date,
timezone: result.settings.timezone,
duration: booking.duration_minutes,
slots: result.slots.filter((slot) => slot.durations.includes(booking.duration_minutes)),
});
} catch (error) {
return json({ error: errorMessage(error) }, 400);
}
}
const rescheduleMatch = url.pathname.match(/^\/api\/admin\/bookings\/([^/]+)\/reschedule$/);
if (request.method === 'POST' && rescheduleMatch) {
const body = await request.json().catch(() => ({})) as { start?: string };
return adminRescheduleBooking(env, rescheduleMatch[1], String(body.start ?? ''));
}
const suggestMatch = url.pathname.match(/^\/api\/admin\/bookings\/([^/]+)\/suggest$/);
if (request.method === 'POST' && suggestMatch) {
const body = await request.json().catch(() => ({})) as { start?: string };
return adminSuggestNewTime(env, suggestMatch[1], String(body.start ?? ''));
}
if (request.method === 'GET' && url.pathname === '/api/admin/availability-rules') {
return listAvailabilityRules(env);
}
if (request.method === 'POST' && url.pathname === '/api/admin/availability-rules/recurring') {
return createRecurringUnavailable(env, await request.json());
}
if (request.method === 'POST' && url.pathname === '/api/admin/availability-rules/blocks') {
return createAvailabilityBlock(env, await request.json());
}
const availabilityDeleteMatch = url.pathname.match(/^\/api\/admin\/availability-rules\/(recurring|block)\/([^/]+)$/);
if (request.method === 'DELETE' && availabilityDeleteMatch) {
return deleteAvailabilityRule(env, availabilityDeleteMatch[1], availabilityDeleteMatch[2]);
}
if (request.method === 'POST' && url.pathname === '/api/admin/disconnect-google') {
await disconnectGoogle(env);
return json({ ok: true });
}
if (request.method === 'POST' && url.pathname === '/api/admin/logout') {
return json({ ok: true }, 200, { 'Set-Cookie': clearSessionCookie(env.APP_URL.startsWith('https://')) });
}
}
return json({ error: 'Not found' }, 404);
}
async function handleAuth(request: Request, env: Env, url: URL): Promise<Response> {
if (request.method === 'GET' && url.pathname === '/auth/google/start') {
return redirect(await createGoogleAuthUrl(env, url.searchParams.get('force') === '1'));
}
if (request.method === 'GET' && url.pathname === '/auth/google/callback') {
const error = url.searchParams.get('error');
if (error) return redirect(`/admin?oauth_error=${encodeURIComponent(error)}`);
const state = url.searchParams.get('state');
const code = url.searchParams.get('code');
if (!state || !code) return redirect('/admin?oauth_error=missing_callback_parameters');
try {
const email = await completeGoogleOAuth(env, state, code);
const token = await signSession(env, email);
return redirect('/admin?connected=1', {
'Set-Cookie': sessionCookie(token, 7 * 86400, env.APP_URL.startsWith('https://')),
});
} catch (error) {
return redirect(`/admin?oauth_error=${encodeURIComponent(errorMessage(error))}`);
}
}
return new Response('Not found', { status: 404 });
}
export default {
async fetch(request: Request, env: Env): Promise<Response> {
const url = new URL(request.url);
try {
if (url.pathname.startsWith('/api/')) return await handleApi(request, env, url);
if (url.pathname.startsWith('/auth/')) return await handleAuth(request, env, url);
return new Response('Not found', { status: 404 });
} catch (error) {
console.error(error);
if (url.pathname.startsWith('/api/')) return json({ error: errorMessage(error) }, 500);
return new Response(errorMessage(error), { status: 500 });
}
},
} satisfies ExportedHandler<Env>;