Build an appointment-booking bot with conflict-safe Google Calendar writes

{
  "calendarId": "appointments@example.com",
  "businessTimeZone": "Europe/Warsaw",
  "allowedTimeZones": [
    "Europe/Warsaw", "Europe/London", "America/New_York",
    "America/Los_Angeles", "Asia/Singapore", "Australia/Sydney"
  ],
  "businessHours": {
    "1": [["09:00", "17:00"]],
    "2": [["09:00", "17:00"]],
    "3": [["09:00", "17:00"]],
    "4": [["09:00", "17:00"]],
    "5": [["09:00", "15:00"]]
  },
  "allowedDurationsMinutes": [30, 45, 60],
  "minimumLeadMinutes": 120,
  "maximumDaysAhead": 60,
  "bufferBeforeMinutes": 10,
  "bufferAfterMinutes": 10,
  "proposalTtlMinutes": 10,
  "maximumSlotsReturned": 8,
  "maximumRequestsPerIpPerMinute": 20,
  "sendUpdates": "all",
  "eventVisibility": "private",
  "retentionDays": 30
}

\set ON_ERROR_STOP on
CREATE TABLE IF NOT EXISTS booking_proposals (
  id uuid PRIMARY KEY,
  idempotency_key text NOT NULL UNIQUE,
  calendar_id text NOT NULL,
  attendee_email text NOT NULL,
  title text NOT NULL,
  start_utc timestamptz NOT NULL,
  end_utc timestamptz NOT NULL,
  attendee_time_zone text NOT NULL,
  local_start_text text NOT NULL,
  confirmation_token_hash text NOT NULL,
  status text NOT NULL CHECK (status IN (
    'proposed', 'confirmed', 'expired', 'cancel-proposed', 'cancelled', 'failed'
  )),
  google_event_id text UNIQUE,
  google_event_etag text,
  created_at timestamptz NOT NULL DEFAULT now(),
  expires_at timestamptz NOT NULL,
  confirmed_at timestamptz,
  cancelled_at timestamptz,
  reviewer_id text,
  last_error_code text,
  CHECK (end_utc > start_utc)
);

CREATE INDEX IF NOT EXISTS booking_proposals_calendar_time_idx
  ON booking_proposals (calendar_id, start_utc, end_utc);
CREATE INDEX IF NOT EXISTS booking_proposals_expiry_idx
  ON booking_proposals (status, expires_at);

CREATE TABLE IF NOT EXISTS booking_audit (
  sequence bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  occurred_at timestamptz NOT NULL DEFAULT now(),
  event_type text NOT NULL,
  proposal_id uuid,
  actor_id text,
  idempotency_key_hash text,
  calendar_event_id text,
  start_utc timestamptz,
  end_utc timestamptz,
  outcome text NOT NULL,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb
);

REVOKE ALL ON booking_proposals, booking_audit FROM PUBLIC;
REVOKE ALL ON ALL SEQUENCES IN SCHEMA public FROM PUBLIC;

{
  "name": "reviewed-appointment-booking-bot",
  "private": true,
  "type": "module",
  "engines": { "node": ">=22.13.0" },
  "scripts": {
    "start": "node src/server.mjs",
    "test": "node --test test/*.test.mjs"
  },
  "dependencies": {
    "@js-temporal/polyfill": "0.5.1",
    "express": "5.2.1",
    "googleapis": "173.0.0",
    "pg": "8.22.0",
    "zod": "4.4.3"
  }
}

import crypto from "node:crypto";
import fs from "node:fs/promises";
import express from "express";
import { google } from "googleapis";
import { Pool } from "pg";
import { Temporal } from "@js-temporal/polyfill";
import { z } from "zod";

const POLICY = JSON.parse(
  await fs.readFile(process.env.BOOKING_POLICY_PATH || "./config/booking-policy.json", "utf8")
);
for (const name of [
  "DATABASE_URL", "GOOGLE_CLIENT_ID", "GOOGLE_CLIENT_SECRET",
  "GOOGLE_REFRESH_TOKEN", "CONFIRMATION_HMAC_SECRET"
]) {
  if (!process.env[name]) throw new Error("Missing required secret: " + name);
}
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,
  connectionTimeoutMillis: 3000,
  statement_timeout: 5000,
  application_name: "appointment-booking-bot"
});
const oauth = new google.auth.OAuth2(
  process.env.GOOGLE_CLIENT_ID,
  process.env.GOOGLE_CLIENT_SECRET
);
oauth.setCredentials({ refresh_token: process.env.GOOGLE_REFRESH_TOKEN });
const calendar = google.calendar({ version: "v3", auth: oauth });
const app = express();
app.set("trust proxy", false);
app.use(express.json({ limit: "24kb", strict: true }));

const requestSchema = z.object({
  attendeeEmail: z.string().email().max(254),
  title: z.string().min(3).max(120),
  date: z.string().regex(/^\d{4}-\d{2}-\d{2}$/),
  timeZone: z.string().max(64),
  durationMinutes: z.number().int(),
  idempotencyKey: z.string().regex(/^[A-Za-z0-9_-]{16,100}$/)
}).strict();
const confirmSchema = z.object({
  proposalId: z.string().uuid(),
  confirmationToken: z.string().min(32).max(200),
  actorId: z.string().min(3).max(120),
  idempotencyKey: z.string().regex(/^[A-Za-z0-9_-]{16,100}$/)
}).strict();
const cancelSchema = z.object({
  proposalId: z.string().uuid(),
  actorId: z.string().min(3).max(120),
  cancellationToken: z.string().min(32).max(200)
}).strict();

const tokenHash = (token) => crypto
  .createHmac("sha256", process.env.CONFIRMATION_HMAC_SECRET)
  .update(token).digest("hex");
const stableEventId = (proposalId) => crypto
  .createHash("sha256").update("booking:" + proposalId).digest("hex").slice(0, 32);
const sleep = (ms) => new Promise((resolve) => setTimeout(resolve, ms));
async function withRetry(operation) {
  for (let attempt = 0; attempt < 4; attempt += 1) {
    try { return await operation(); }
    catch (error) {
      if (![403, 429, 500, 502, 503, 504].includes(error?.code) || attempt === 3) throw error;
      await sleep(Math.min(32000, 1000 * 2 ** attempt) + crypto.randomInt(0, 1000));
    }
  }
}
function localToInstant(date, time, timeZone) {
  if (!POLICY.allowedTimeZones.includes(timeZone)) throw new Error("UNSUPPORTED_TIME_ZONE");
  const plain = Temporal.PlainDateTime.from(date + "T" + time);
  return plain.toZonedDateTime(timeZone, { disambiguation: "reject" });
}
function slotsForDay(date, timeZone, duration) {
  if (!POLICY.allowedDurationsMinutes.includes(duration)) throw new Error("UNSUPPORTED_DURATION");
  const businessDate = Temporal.PlainDate.from(date);
  const weekday = String(businessDate.dayOfWeek);
  const windows = POLICY.businessHours[weekday] || [];
  const slots = [];
  for (const [from, to] of windows) {
    let cursor = localToInstant(date, from, POLICY.businessTimeZone);
    const endWindow = localToInstant(date, to, POLICY.businessTimeZone);
    while (Temporal.ZonedDateTime.compare(
      cursor.add({ minutes: duration }),
      endWindow
    ) <= 0) {
      const attendee = cursor.withTimeZone(timeZone);
      slots.push({
        localStart: attendee.toPlainDateTime().toString(),
        timeZone,
        startUtc: cursor.toInstant().toString(),
        endUtc: cursor.add({ minutes: duration }).toInstant().toString()
      });
      cursor = cursor.add({ minutes: 15 });
    }
  }
  return slots;
}
async function busyRanges(slots) {
  if (!slots.length) return [];
  const response = await withRetry(() => calendar.freebusy.query({
    requestBody: {
      timeMin: slots[0].startUtc,
      timeMax: slots.at(-1).endUtc,
      timeZone: "UTC",
      items: [{ id: POLICY.calendarId }]
    }
  }));
  const entry = response.data.calendars?.[POLICY.calendarId];
  if (!entry || entry.errors?.length) throw new Error("FREEBUSY_INCOMPLETE");
  return entry.busy || [];
}
function overlaps(slot, busy) {
  const start = Temporal.Instant.from(slot.startUtc).subtract({
    minutes: POLICY.bufferBeforeMinutes
  });
  const end = Temporal.Instant.from(slot.endUtc).add({
    minutes: POLICY.bufferAfterMinutes
  });
  return busy.some((range) =>
    Temporal.Instant.compare(start, Temporal.Instant.from(range.end)) < 0 &&
    Temporal.Instant.compare(end, Temporal.Instant.from(range.start)) > 0
  );
}

app.post("/v1/availability", async (req, res) => {
  try {
    const input = requestSchema.parse(req.body);
    const today = Temporal.Now.plainDateISO(POLICY.businessTimeZone);
    const requested = Temporal.PlainDate.from(input.date);
    const days = requested.since(today).days;
    if (days < 0 || days > POLICY.maximumDaysAhead) throw new Error("DATE_OUTSIDE_POLICY");
    const candidates = slotsForDay(input.date, input.timeZone, input.durationMinutes);
    const busy = await busyRanges(candidates);
    const nowPlusLead = Temporal.Now.instant().add({ minutes: POLICY.minimumLeadMinutes });
    const slots = candidates.filter((slot) =>
      Temporal.Instant.compare(Temporal.Instant.from(slot.startUtc), nowPlusLead) >= 0 &&
      !overlaps(slot, busy)
    ).slice(0, POLICY.maximumSlotsReturned);
    res.json({ slots, zone: input.timeZone, notReserved: true });
  } catch (error) {
    res.status(400).json({ error: error.message || "INVALID_REQUEST" });
  }
});

app.post("/v1/proposals", async (req, res) => {
  const client = await pool.connect();
  try {
    const input = requestSchema.extend({
      localStart: z.string().min(16).max(40)
    }).parse(req.body);
    const start = localToInstant(
      input.localStart.slice(0, 10),
      input.localStart.slice(11, 16),
      input.timeZone
    );
    const end = start.add({ minutes: input.durationMinutes });
    const businessDate = start.withTimeZone(POLICY.businessTimeZone)
      .toPlainDate().toString();
    const allowedSlot = slotsForDay(
      businessDate,
      input.timeZone,
      input.durationMinutes
    ).some((slot) => slot.startUtc === start.toInstant().toString());
    if (!allowedSlot) throw new Error("SLOT_OUTSIDE_BUSINESS_POLICY");
    const token = crypto.randomBytes(32).toString("base64url");
    const id = crypto.randomUUID();
    await client.query("BEGIN");
    const existing = await client.query(
      "SELECT id, status, expires_at FROM booking_proposals WHERE idempotency_key=$1",
      [input.idempotencyKey]
    );
    if (existing.rows[0]) {
      await client.query("COMMIT");
      res.status(200).json({ proposalId: existing.rows[0].id, replayed: true });
      return;
    }
    await client.query(
      "INSERT INTO booking_proposals " +
      "(id,idempotency_key,calendar_id,attendee_email,title,start_utc,end_utc," +
      "attendee_time_zone,local_start_text,confirmation_token_hash,status,expires_at) " +
      "VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,'proposed'," +
      "now() + ($11 || ' minutes')::interval)",
      [
        id, input.idempotencyKey, POLICY.calendarId,
        input.attendeeEmail.toLowerCase(), input.title,
        start.toInstant().toString(), end.toInstant().toString(),
        input.timeZone, input.localStart, tokenHash(token), POLICY.proposalTtlMinutes
      ]
    );
    await client.query("COMMIT");
    res.status(201).json({
      proposalId: id,
      confirmationToken: token,
      expiresInMinutes: POLICY.proposalTtlMinutes,
      exactAppointment: {
        localStart: input.localStart,
        timeZone: input.timeZone,
        utcStart: start.toInstant().toString(),
        utcEnd: end.toInstant().toString(),
        attendeeEmail: input.attendeeEmail.toLowerCase()
      },
      calendarWritten: false
    });
  } catch (error) {
    await client.query("ROLLBACK").catch(() => {});
    res.status(400).json({ error: error.message || "PROPOSAL_FAILED" });
  } finally { client.release(); }
});

app.post("/v1/confirm", async (req, res) => {
  const client = await pool.connect();
  try {
    const input = confirmSchema.parse(req.body);
    await client.query("BEGIN");
    await client.query("SELECT pg_advisory_xact_lock(hashtextextended($1,0))", [POLICY.calendarId]);
    const found = await client.query(
      "SELECT * FROM booking_proposals WHERE id=$1 FOR UPDATE",
      [input.proposalId]
    );
    const proposal = found.rows[0];
    if (!proposal) throw new Error("PROPOSAL_NOT_FOUND");
    if (proposal.idempotency_key !== input.idempotencyKey) throw new Error("IDEMPOTENCY_MISMATCH");
    if (proposal.status === "confirmed") {
      await client.query("COMMIT");
      res.json({ eventId: proposal.google_event_id, replayed: true });
      return;
    }
    if (proposal.status !== "proposed" || Date.now() >= Date.parse(proposal.expires_at)) {
      throw new Error("PROPOSAL_EXPIRED_OR_INVALID");
    }
    const supplied = Buffer.from(tokenHash(input.confirmationToken));
    const expected = Buffer.from(proposal.confirmation_token_hash);
    if (supplied.length !== expected.length || !crypto.timingSafeEqual(supplied, expected)) {
      throw new Error("INVALID_CONFIRMATION");
    }
    const eventId = stableEventId(proposal.id);
    let inserted;
    try {
      inserted = await calendar.events.get({
        calendarId: POLICY.calendarId, eventId
      });
      if (inserted.data.extendedProperties?.private?.bookingProposalId !== proposal.id) {
        throw new Error("EVENT_ID_COLLISION");
      }
    } catch (lookupError) {
      if (lookupError?.code !== 404) throw lookupError;
      const busy = await busyRanges([{
        startUtc: proposal.start_utc.toISOString(),
        endUtc: proposal.end_utc.toISOString()
      }]);
      if (overlaps({
        startUtc: proposal.start_utc.toISOString(),
        endUtc: proposal.end_utc.toISOString()
      }, busy)) throw new Error("SLOT_NO_LONGER_AVAILABLE");
      try {
        inserted = await withRetry(() => calendar.events.insert({
          calendarId: POLICY.calendarId,
          sendUpdates: POLICY.sendUpdates,
          requestBody: {
            id: eventId,
            summary: proposal.title,
            visibility: POLICY.eventVisibility,
            attendees: [{ email: proposal.attendee_email }],
            start: {
              dateTime: proposal.start_utc.toISOString(),
              timeZone: proposal.attendee_time_zone
            },
            end: {
              dateTime: proposal.end_utc.toISOString(),
              timeZone: proposal.attendee_time_zone
            },
            extendedProperties: { private: { bookingProposalId: proposal.id } }
          }
        }));
      } catch (insertError) {
        if (insertError?.code !== 409) throw insertError;
        inserted = await calendar.events.get({
          calendarId: POLICY.calendarId, eventId
        });
        if (inserted.data.extendedProperties?.private?.bookingProposalId !== proposal.id) {
          throw new Error("EVENT_ID_COLLISION");
        }
      }
    }
    await client.query(
      "UPDATE booking_proposals " +
      "SET status='confirmed',google_event_id=$2,google_event_etag=$3," +
      "confirmed_at=now(),reviewer_id=$4 WHERE id=$1",
      [proposal.id, inserted.data.id, inserted.data.etag, input.actorId]
    );
    await client.query(
      "INSERT INTO booking_audit " +
      "(event_type,proposal_id,actor_id,idempotency_key_hash,calendar_event_id," +
      "start_utc,end_utc,outcome) " +
      "VALUES ('appointment-confirmed',$1,$2,$3,$4,$5,$6,'created')",
      [
        proposal.id, input.actorId,
        crypto.createHash("sha256").update(input.idempotencyKey).digest("hex"),
        inserted.data.id, proposal.start_utc, proposal.end_utc
      ]
    );
    await client.query("COMMIT");
    res.status(201).json({
      eventId: inserted.data.id,
      status: inserted.data.status,
      htmlLink: inserted.data.htmlLink,
      startUtc: proposal.start_utc,
      attendeeTimeZone: proposal.attendee_time_zone
    });
  } catch (error) {
    await client.query("ROLLBACK").catch(() => {});
    res.status(error.message === "SLOT_NO_LONGER_AVAILABLE" ? 409 : 400)
      .json({ error: error.message || "CONFIRM_FAILED" });
  } finally { client.release(); }
});

app.post("/v1/cancel/proposals", async (req, res) => {
  const client = await pool.connect();
  try {
    const input = z.object({
      proposalId: z.string().uuid(),
      actorId: z.string().min(3).max(120)
    }).strict().parse(req.body);
    const token = crypto.randomBytes(32).toString("base64url");
    await client.query("BEGIN");
    const updated = await client.query(
      "UPDATE booking_proposals " +
      "SET status='cancel-proposed',confirmation_token_hash=$2,reviewer_id=$3," +
      "expires_at=now() + ($4 || ' minutes')::interval " +
      "WHERE id=$1 AND status='confirmed' " +
      "RETURNING id,google_event_id,start_utc,end_utc,attendee_time_zone",
      [input.proposalId, tokenHash(token), input.actorId, POLICY.proposalTtlMinutes]
    );
    if (!updated.rows[0]) throw new Error("CONFIRMED_BOOKING_NOT_FOUND");
    await client.query("COMMIT");
    res.status(201).json({
      proposalId: input.proposalId,
      cancellationToken: token,
      exactCancellation: updated.rows[0],
      calendarWritten: false
    });
  } catch (error) {
    await client.query("ROLLBACK").catch(() => {});
    res.status(400).json({ error: error.message || "CANCEL_PROPOSAL_FAILED" });
  } finally { client.release(); }
});

app.post("/v1/cancel/confirm", async (req, res) => {
  const client = await pool.connect();
  try {
    const input = cancelSchema.parse(req.body);
    await client.query("BEGIN");
    const found = await client.query(
      "SELECT * FROM booking_proposals WHERE id=$1 FOR UPDATE",
      [input.proposalId]
    );
    const proposal = found.rows[0];
    if (!proposal || proposal.status !== "cancel-proposed" ||
        Date.now() >= Date.parse(proposal.expires_at)) {
      throw new Error("CANCEL_NOT_PROPOSED_OR_EXPIRED");
    }
    const suppliedCancel = Buffer.from(tokenHash(input.cancellationToken));
    const expectedCancel = Buffer.from(proposal.confirmation_token_hash);
    if (suppliedCancel.length !== expectedCancel.length ||
        !crypto.timingSafeEqual(suppliedCancel, expectedCancel)) {
      throw new Error("INVALID_CANCELLATION");
    }
    const current = await calendar.events.get({
      calendarId: POLICY.calendarId,
      eventId: proposal.google_event_id
    });
    if (proposal.google_event_etag && current.data.etag !== proposal.google_event_etag) {
      throw new Error("EVENT_CHANGED_REVIEW_AGAIN");
    }
    await withRetry(() => calendar.events.delete({
      calendarId: POLICY.calendarId,
      eventId: proposal.google_event_id,
      sendUpdates: POLICY.sendUpdates
    }));
    await client.query(
      "UPDATE booking_proposals SET status='cancelled',cancelled_at=now(),reviewer_id=$2 WHERE id=$1",
      [proposal.id, input.actorId]
    );
    await client.query(
      "INSERT INTO booking_audit " +
      "(event_type,proposal_id,actor_id,calendar_event_id,start_utc,end_utc,outcome) " +
      "VALUES ('appointment-cancelled',$1,$2,$3,$4,$5,'deleted')",
      [proposal.id, input.actorId, proposal.google_event_id, proposal.start_utc, proposal.end_utc]
    );
    await client.query("COMMIT");
    res.json({ cancelled: true, eventId: proposal.google_event_id });
  } catch (error) {
    await client.query("ROLLBACK").catch(() => {});
    res.status(400).json({ error: error.message || "CANCEL_FAILED" });
  } finally { client.release(); }
});

app.get("/healthz", (_req, res) => res.json({ ok: true }));
app.listen(Number(process.env.PORT || 8080), "127.0.0.1", () =>
  console.log("BOOKING_BOT_READY")
);

import assert from "node:assert/strict";
import fs from "node:fs/promises";
import test from "node:test";

const source = await fs.readFile("./src/server.mjs", "utf8");
test("uses Temporal with reject disambiguation for DST gaps and overlaps", () => {
  assert.match(source, /disambiguation: "reject"/);
  assert.match(source, /allowedTimeZones/);
});
test("confirmation is distinct from proposal and checks current freebusy", () => {
  assert.match(source, /\/v1\/proposals/);
  assert.match(source, /\/v1\/confirm/);
  assert.match(source, /SLOT_NO_LONGER_AVAILABLE/);
  assert.match(source, /timingSafeEqual/);
});
test("serializes confirmations and provides stable provider idempotency", () => {
  assert.match(source, /pg_advisory_xact_lock/);
  assert.match(source, /stableEventId/);
  assert.match(source, /error\?\.code !== 409/);
});
test("calendar writes notify attendees and remain private", () => {
  assert.match(source, /sendUpdates: POLICY\.sendUpdates/);
  assert.match(source, /visibility: POLICY\.eventVisibility/);
});
test("cancellation is proposed, refetched, etag checked, then confirmed", () => {
  assert.match(source, /\/v1\/cancel\/proposals/);
  assert.match(source, /EVENT_CHANGED_REVIEW_AGAIN/);
  assert.match(source, /calendar\.events\.delete/);
});
test("does not expose a generic calendar update or arbitrary calendar id", () => {
  assert.doesNotMatch(source, /calendar\.events\.(patch|update|move|import)/);
  assert.doesNotMatch(source, /calendarId:\s*req\./);
});
test("bounds JSON, database waits, and Google retries", () => {
  assert.match(source, /limit: "24kb"/);
  assert.match(source, /statement_timeout: 5000/);
  assert.match(source, /attempt < 4/);
});

[Unit]
Description=Reviewed appointment booking bot
After=network-online.target postgresql.service
Wants=network-online.target

[Service]
Type=simple
User=appointment-bot
Group=appointment-bot
WorkingDirectory=/opt/appointment-bot
EnvironmentFile=/etc/appointment-bot/runtime.env
ExecStart=/usr/bin/node /opt/appointment-bot/src/server.mjs
Restart=on-failure
RestartSec=5s
NoNewPrivileges=true
PrivateTmp=true
ProtectSystem=strict
ProtectHome=true
ReadWritePaths=/var/lib/appointment-bot
RestrictSUIDSGID=true

[Install]
WantedBy=multi-user.target