# Build a scheduling app (replacing Calendly)

You are building a self-hosted scheduling app for one host, extensible to a small team. It replaces Calendly for people who need a public booking page tied to their Google Calendar with reminders and reschedule/cancel links. Build it end to end. Correctness around timezones and double-booking matters more than features.

## Stack

- One Node 22 process running Next.js (App Router) with TypeScript and Tailwind. Nothing runs outside that process except the optional outside services below.
- SQLite via `better-sqlite3` with Drizzle, WAL mode, file at `./data/app.db`. Migrations are checked in and run on startup. Keep the schema portable so `DATABASE_URL=postgres://...` works later, but don't build for it now.
- Files on local disk under `./data/files/<table>/<id>/`, served through an authenticated route with short-lived signed URLs the app mints itself.
- `croner` inside the app process for reminders and retention. A row in a `job_locks` table stops two instances from running the same job.
- Sessions the app issues itself: signed httpOnly cookies. First run creates the admin from `ADMIN_EMAIL` and `ADMIN_PASSWORD`, or prompts in the browser if unset. Teammates join through invite links.
- Server-sent events from an in-memory bus for anything live in the UI.
- Email with `nodemailer` through `SMTP_URL`. Without it, mail is written to `./data/outbox/*.eml` and printed to the log. Generate `.ics` attachments with the `ics` package.
- Google Calendar API via `googleapis` (OAuth 2.0, offline access). This is the one outside service the tool can't avoid for reading real free/busy and writing real events. Behind the same `lib/calendar/` interface, ship a `local` provider backed by a `busy_blocks` table so the whole booking flow, slot algorithm, and emails run with no Google account. `local` is the default until `GOOGLE_CLIENT_ID` is set.
- Any key the app needs (session signing, refresh token encryption) is generated on first run and stored in `./data/keys.json` if not given in env.
- UTC in the database, local time in the UI, `date-fns` and `date-fns-tz` for every conversion. Never do timezone math by hand.
- `zod` for every request body and every JSON column.

Full profile: https://buildyourown.software/deploy.md

## Running it

- **Laptop:** `pnpm install && pnpm dev`, open `http://localhost:3000`. No env vars. The first visit redirects to `/setup` to create the admin. The local calendar provider and the outbox mean no Google account and no mail account are needed to try every flow.
- **VPS:** 1 CPU, 1 GB RAM, one A record. Put `APP_URL`, and optionally `GOOGLE_CLIENT_ID`, `GOOGLE_CLIENT_SECRET`, and `SMTP_URL`, in `.env`, then `docker compose up -d`. Compose runs `app`, `caddy` (TLS on its own), and `backup`. `./data` is a named volume.
- **Backups:** `pnpm backup` runs `sqlite3 .backup` into `./data/backups/<date>.db` and prunes to the last 14. The `backup` service runs it nightly. Restore by copying one file over `./data/app.db` and restarting.
- **Updates:** `git pull && docker compose up -d --build`. Migrations run on boot.

## Data model

All tables have `id` (text, uuid v4), `created_at`, `updated_at`. Every timestamp is text in ISO 8601 UTC with milliseconds (`2026-09-15T14:00:00.000Z`) so string comparison equals time comparison. Booleans are integer 0/1. JSON is text, parsed and validated with zod on read. There is no `timestamptz` and no `jsonb`: the app formats every date before it hits the database and never queries inside JSON. The partial unique index below is a normal SQLite partial index.

- `hosts`: `email` (unique), `name`, `slug` (unique, url-safe), `timezone` (IANA), `password_hash` (argon2), `role` (`admin` | `member`), `calendar_provider` (`local` | `google`, default `local`), `google_refresh_token` (encrypted, nullable), `write_calendar_id`, `avatar_path` (relative to `./data/files`, nullable).
- `sessions`: `host_id`, `token_hash`, `expires_at`, `last_seen_at`.
- `invites`: `email`, `token_hash` (unique), `invited_by`, `expires_at`, `accepted_at` (nullable).
- `calendar_connections`: `host_id`, `provider` (`local` | `google`), `calendar_id`, `summary`, `check_busy` (bool).
- `busy_blocks`: `host_id`, `calendar_id`, `title`, `start_at`, `end_at`, `source` (`manual` | `booking`), `booking_id` (nullable). The local provider's whole calendar. Index on (`host_id`, `start_at`).
- `event_types`: `host_id`, `slug`, `name`, `description`, `duration_min`, `location_kind` (`meet` | `phone` | `in_person` | `custom`), `location_detail`, `color`, `questions` (json text: array of `{ id, label, type: text|textarea|phone|select, required, options? }`), `active` (bool). Unique on (`host_id`, `slug`).
- `availability`: one row per host. `weekly_hours` (json text: `{ mon: [["09:00","12:00"],["13:00","17:00"]], ... }` in the host's timezone), `date_overrides` (json text: `{ "2026-12-24": [] , "2026-12-26": [["10:00","14:00"]] }`), `buffer_before_min`, `buffer_after_min`, `min_notice_hours`, `max_per_day`, `slot_interval_min` (default 30), `booking_window_days` (default 60).
- `bookings`: `event_type_id`, `host_id`, `guest_name`, `guest_email`, `guest_timezone`, `start_at`, `end_at`, `answers` (json text), `status` (`confirmed` | `cancelled` | `rescheduled`), `rescheduled_to_id` (nullable), `calendar_event_id`, `meet_url` (nullable), `manage_token` (32 random bytes, base64url, unique), `cancel_reason`. Partial unique index on (`host_id`, `start_at`) where `status = 'confirmed'`.
- `notifications`: `booking_id`, `kind` (`confirmation_guest` | `confirmation_host` | `reminder_24h` | `reminder_1h` | `cancelled` | `rescheduled`), `send_at`, `claimed_at` (nullable), `sent_at` (nullable), `attempts` (int, default 0), `error` (nullable).
- `job_locks`: `name` (primary key), `locked_by`, `locked_at`, `expires_at`.

## Calendar providers (`lib/calendar/`)

One interface: `listCalendars()`, `freeBusy(calendarIds, from, to)`, `createEvent(input)`, `patchEvent(id, input)`, `deleteEvent(id)`. Two implementations:

- `google.ts`: `googleapis` with the host's refresh token. `createEvent` requests a Meet link through `conferenceData` when `location_kind = meet` and returns `hangoutLink`.
- `local.ts`: reads and writes `busy_blocks`. `listCalendars` returns one calendar named "Local". `createEvent` inserts a `busy_blocks` row with `source = booking` and returns its id; `meet_url` is null and the confirmation shows `location_detail` instead.

Pick the provider from `hosts.calendar_provider`. A host can switch to `google` only after connecting it on the Calendars screen.

## Screens

### Host (authenticated)

1. **Setup and sign in**: `/setup` runs once when there are no hosts. It creates the admin from `ADMIN_EMAIL` and `ADMIN_PASSWORD` if both are set, otherwise shows a form for name, email, password, and timezone. `/login` is email and password. `/invite/<token>` lets a teammate set a name and password and become a host with a slug from their email local part. Admins create invites from Settings.
2. **Dashboard (`/app`)**: upcoming bookings (next 14 days) with guest, event type, time in host timezone, and links to the calendar event and the manage page. Past bookings below. Subscribes to `/api/stream` (SSE) so a new booking appears without a refresh.
3. **Event types (`/app/event-types`)**: list, create, edit, toggle active, copy link. Editor has name, slug, duration, description, location, color, and a question builder.
4. **Availability (`/app/availability`)**: weekly hours editor (per weekday, multiple ranges), date overrides with a calendar picker, buffers, minimum notice, max per day, slot interval, booking window.
5. **Calendars (`/app/calendars`)**: with the local provider, a busy block editor (title, start, end) and the single "Local" calendar. A "Connect Google Calendar" button (shown only when `GOOGLE_CLIENT_ID` is set) starts OAuth for `calendar.readonly` and `calendar.events`, stores the refresh token encrypted, and switches the provider. Once connected, list Google calendars, tick which to check for busy time, and pick one to write events to.
6. **Settings**: name, slug, timezone (searchable IANA list), password, avatar upload (stored at `./data/files/hosts/<id>/`, served through the signed-URL route), invite teammates (admin only), sign out, delete account.

### Guest (public)

7. **Host page (`/<slug>`)**: name, avatar, list of active event types with duration.
8. **Booking page (`/<slug>/<event-slug>`)**: month calendar on the left showing days with availability, time slots for the selected day on the right, in the guest's timezone (auto-detected, changeable via a dropdown). Also readable as `?date=2026-09-15&month=2026-09`. Show a 12h/24h toggle.
9. **Booking form**: name, email, custom questions, then "Confirm". On success show a confirmation with the time in both timezones, the Meet link or location, "add to calendar" links (Google, Outlook, .ics), and a link to manage the booking.
10. **Manage page (`/manage/<token>`)**: booking details with Reschedule and Cancel. Reschedule opens the slot picker; cancel asks for an optional reason.

## Slot algorithm (put this in `lib/slots.ts`, pure, fully unit-tested)

Input: event type, availability, list of busy intervals (UTC), existing confirmed bookings, `from`/`to` range (UTC), `now`.

1. For each day in the range in the host timezone, take `date_overrides[date]` if present, else `weekly_hours[weekday]`. Convert each range to UTC intervals. Handle days where DST changes.
2. Build the busy set: calendar busy intervals plus confirmed bookings, each expanded by `buffer_before_min` before and `buffer_after_min` after.
3. Subtract busy from available.
4. Remove anything starting before `now + min_notice_hours`.
5. Slice each remaining window into candidate starts every `slot_interval_min`, keeping only starts where `start + duration_min` is still inside the window.
6. Drop days that already have `max_per_day` confirmed bookings.
7. Return slots as UTC ISO strings. The UI converts to the guest timezone.

## Booking flow (`POST /api/book`)

1. Validate input with zod. Reject unknown event type, inactive event type, past start, and answers missing required questions.
2. Recompute available slots for that single day and confirm the requested start is in the list. Use a fresh `freeBusy` call through the host's provider.
3. In one transaction: insert the booking with `status = confirmed`. If the unique index fails, return 409 with "That time was just taken".
4. Call `createEvent` on the host's provider for `write_calendar_id`: title `"<event type name> with <guest name>"`, description with answers and the manage link, attendees host and guest, a Meet link when `location_kind = meet` and the provider is Google. Store `calendar_event_id` and `meet_url`.
5. Insert notifications: confirmation to guest and host (send_at now), `reminder_24h` and `reminder_1h` (skip any that are already in the past).
6. Publish `booking.created` on the event bus and return the booking id and manage token.

If step 4 fails, mark the booking cancelled with reason "calendar error" and return 502. Never leave a booking without a calendar event.

## Cancel and reschedule

- Cancel: set `status = cancelled`, call `deleteEvent`, insert a `cancelled` notification, null out `send_at` on unsent reminders.
- Reschedule: run the booking flow for the new time creating a new booking; on success set the old booking to `rescheduled` with `rescheduled_to_id`, call `patchEvent` on the existing calendar event's start/end instead of creating a second one, and move `calendar_event_id` to the new booking. Send a `rescheduled` notification.

## Jobs (`lib/jobs/`, started once in `instrumentation.ts`)

- Every job takes the `job_locks` row for its name first: insert, or update where `expires_at < now`, with a 10-minute expiry and a heartbeat every 30 seconds. If the row is held, skip this tick. Release it when done.
- `notifications`, every minute: select up to 100 notifications with `sent_at IS NULL AND send_at <= now AND (claimed_at IS NULL OR claimed_at < now - 10 min) AND attempts < 5` whose booking is `confirmed` (or whose kind is `cancelled` / `rescheduled`). Stamp `claimed_at` and bump `attempts` in one update before sending anything.
- Render an email per kind. Confirmation emails attach an `.ics` file. Every email shows the time in the recipient's timezone and includes the manage link (guest) or calendar link (host).
- Send with `lib/mail.ts` (nodemailer, or the outbox when `SMTP_URL` is unset). On success stamp `sent_at`; on failure store `error` and clear `claimed_at` so it retries next tick.
- `retention`, nightly: delete sessions past `expires_at` and invites past `expires_at` with no `accepted_at`.

## Non-goals

No team accounts, round-robin, collective events, routing forms, payments, SMS, Outlook, Zoom, or CRM sync in this build. No hosted services beyond a Google Cloud OAuth app for Calendar and an SMTP relay. Design the schema so a `team_id` on hosts and a `hosts[]` on event types can be added later.

## Acceptance criteria

1. The admin signs in, connects Google from the Calendars screen (or stays on the local provider), sees their calendars, and can pick busy-check calendars and a write calendar.
2. With weekly hours Mon–Fri 09:00–17:00 in `America/New_York`, a 30-minute event, and no busy time, the booking page shows 16 slots on a weekday and none on Saturday.
3. A busy event 10:00–11:00 with 15-minute buffers removes the 09:30, 10:00, 10:30, and 11:00 slots.
4. `min_notice_hours = 4` hides slots within the next four hours.
5. A date override to `[]` hides every slot that day; an override to a single range shows only that range.
6. Slots render correctly for a guest in `Asia/Kolkata` (half-hour offset) and for a host week that crosses a DST change.
7. Two concurrent `POST /api/book` for the same slot produce exactly one confirmed booking and one 409.
8. A booking creates a calendar event (a `busy_blocks` row on the local provider, a Google event with a Meet link on Google), and both guest and host receive a confirmation email with a valid `.ics`.
9. Cancelling via the manage link deletes the calendar event and prevents pending reminders from sending.
10. Rescheduling updates the existing calendar event instead of creating a new one and sends a rescheduled email.
11. The notifications job sends due notifications exactly once, even when two ticks overlap.
12. The public booking page works with JavaScript disabled for browsing days (links) and with JS for the slot picker.
13. A fresh clone runs with `pnpm install && pnpm dev` and no env vars, and the first browser visit creates the admin.
14. `docker compose up -d` on a clean Ubuntu VPS with one A record serves the app over HTTPS with no other setup.
15. Killing the process mid-job and restarting it doesn't double-run or lose the job.
16. `pnpm backup` then deleting `./data/app.db` and restoring from the backup brings every record back.
17. Every outbound email in dev shows up in `./data/outbox`.

## Deliverables

- The app, migrations, a seed script with one host, three event types, a few busy blocks, and a week of sample bookings.
- `lib/slots.ts` with a table-driven test suite covering criteria 2 through 6.
- `docker-compose.yml` (`app`, `caddy`, `backup`), `Caddyfile`, and `scripts/backup.sh` wired to `pnpm backup`.
- README covering the laptop run, the VPS run, restore from backup, Google Cloud OAuth setup (consent screen, scopes, redirect URI), and `SMTP_URL`.

Build the slot algorithm and its tests first. Then the local calendar provider and the booking flow. Then the screens and jobs. Then the Google provider. Run the app in the browser after each step.