📅 BookIt

Multi-Vertical Appointment Booking System

Book anything — doctors, salons & turfs — with zero double-bookings. A full-stack booking platform where the database itself makes overlapping appointments impossible.

---

📖 Overview

BookIt is a full-stack, multi-vertical appointment booking platform for three kinds of business — doctors & clinics, salons & grooming, and sports turfs & courts. Customers browse providers, see real-time bookable slots computed from each provider's live schedule, and book or cancel in a few clicks. Admins manage providers, weekly schedules, services, breaks, time-off and every booking from a dedicated dashboard.

The headline feature is correctness under pressure: two people can never hold the same slot, even in a race between concurrent requests. BookIt guarantees this with three independent layers — a per-provider advisory lock, in-transaction slot re-validation, and a PostgreSQL GiST exclusion constraint that makes an overlapping booking impossible at the database level.

The one-liner: a React + Vite SPA talks to an Express + TypeScript API that validates every request with Zod, computes availability from live schedules, and leans on PostgreSQL exclusion constraints + advisory locks so double-booking is impossible by construction — not by hope.

🔑 Seeded logins  ·  admin: admin@bookit.local / admin123  ·  customer: customer@bookit.local / customer123

---

📸 Screenshots

Home — pick a category and go

A hero and three category cards (doctors / salons / turfs), plus a feature strip: live slot availability, conflict-proof booking, and email confirmations.

Admin Dashboard — the whole operation at a glance

Today's load, next-7-days pipeline, monthly revenue, 30-day cancel rate, active providers & customers, a per-provider upcoming-load table, and recent bookings.

🗓️ 3-Step Booking Flow Service → date & 14-day strip → time slots grouped Morning / Afternoon / Evening, computed live.	🔎 Browse Providers Searchable provider list per category, with services, pricing and hours.
📋 Admin Bookings Filter by status / provider / date, run complete / no-show / cancel actions, and see the audit history.	🕐 Day View A visual timeline of every booking across all providers for a chosen day.

---

✨ Features

🧑‍💻 Customer side

• Browse providers by category (doctor / salon / turf) with search, star ratings & reviews.
• Per-provider service catalog — duration, buffer time, price and payment policy (pay-at-venue / deposit / full prepay).
• Live slot availability computed from weekly schedules, breaks, time-off and existing bookings.
• Clean 3-step booking flow: service → date & slot → details, with coupon codes and loyalty-point redemption.
• Recurring series — book weekly / biweekly runs (up to 12 sessions) with skip-and-report for unavailable dates.
• Customer accounts (optional — guest checkout always works): booking history, favorites ❤, loyalty points; past guest bookings link automatically on signup.
• Self-service manage page: look up by code + email, reschedule to a new slot, cancel with automatic slot release and policy-based refunds.
• Waitlist: full day? Get an email the moment a cancellation frees a slot.
• 🌙 Dark mode across the whole app.

💳 Payments (simulated gateway, production-shaped)

• Per-service policy: pay at venue, deposit %, or full prepayment.
• pending_payment bookings hold the slot at the database level for a configurable window; expired holds release automatically (sweeper + inline expiry).
• Built-in MockPay gateway — HMAC-signed checkout mirroring Razorpay's flow, so a real adapter drops in behind the same PaymentProvider interface. Zero external accounts needed.
• Refund policy engine (full / fee / none by time-to-appointment), automatic refunds on cancellation, admin manual refunds, printable receipts.
• Coupons (percent / fixed, windows, usage caps) and loyalty points (earn on completion, redeem up to 50% of price — race-safe).

📬 Notifications

• Transactional outbox + dispatcher: every email (confirmation, cancellation, reschedule, receipt, reminders, waitlist, series) is queued in Postgres and delivered with retries + exponential backoff — restarts lose nothing.
• Automated reminders 24h and 1h before each appointment (voided/re-planned on cancel & reschedule).
• .ics calendar invites attached to confirmations (REQUEST) and cancellations (CANCEL) with stable UIDs and SEQUENCE bumps.
• Channel abstraction — SMS/WhatsApp adapters can plug in without touching the dispatcher.

🛠️ Admin panel

• Dashboard + analytics — booked value vs collected-online vs refunded, bookings & net revenue per day, weekday×hour peak-hours heatmap, top services, outcome rates, new-vs-returning customers (hand-rolled SVG, light/dark aware).
• Bookings table — filters, complete / no-show / cancel actions, CSV export (BOM + injection-safe).
• Day view & week view — visual timelines with click-through booking details.
• Customer CRM — searchable customer list with lifetime spend, no-show counts, loyalty balance, full history and private notes.
• Payments & coupons — payment ledger with refund actions; coupon CRUD.
• Reviews moderation — hide/unhide customer reviews.
• Waitlist management, provider/schedule/time-off/service CRUD as before.

⚙️ Platform

• JWT-authenticated admin + customer APIs (separate token kinds); Zod request validation everywhere.
• Email via SMTP (nodemailer); with no SMTP config, rendered emails land in server/outbox/*.html — so the flow works end-to-end with zero setup.
• Booking audit trail (booking_events): created, status changes, payments, refunds, reschedules, emails sent.

---

🎯 The flagship: zero double-booking

Double-booking is prevented with three independent layers, so even a race between concurrent requests is safe:

Layer	Mechanism	What it guarantees
1	pg_advisory_xact_lock(42, provider_id)	Serialises concurrent bookings per provider; different providers book fully in parallel. Released automatically at commit/rollback.
2	In-transaction slot re-validation	The requested start must still be a slot the availability engine would generate right now — a hand-crafted API call can't book a closed day.
3	Postgres GiST exclusion constraint	The last line of defence. Even raw SQL cannot persist an overlap.

CONSTRAINT bookings_no_overlap EXCLUDE USING gist (
  provider_id WITH =,
  tstzrange(starts_at, ends_at) WITH &&
) WHERE (status IN ('pending_payment', 'confirmed', 'completed'))

The partial WHERE means cancelled / no-show bookings automatically free their slot — and a booking that's being paid for (pending_payment) still holds its slot until it's captured or the hold expires. A conflicting insert fails with SQLSTATE 23P01, which the API maps to 409 Conflict, and the UI refreshes the slot grid.

📐 The full request-to-database walkthrough lives in docs/ARCHITECTURE.md.

---

🛠️ Tech Stack

Layer	Technology
Frontend	React 18 · TypeScript 5 · Vite 6 · React Router 6
Backend	Node.js · Express 4 · TypeScript 5
Database	PostgreSQL 13+ (GiST exclusion constraints, advisory locks, range types)
Validation	Zod
Auth	JWT (jsonwebtoken) + bcryptjs
Email	Nodemailer (SMTP, with HTML outbox fallback)
Tooling	npm workspaces · tsx · concurrently

---

🚀 Getting Started

Prerequisites

• Node.js 18+
• PostgreSQL 13+ running locally (or a hosted connection string)

Installation

# 1. Clone the repository
git clone https://github.com/bhanu87777/BookIt-Appointment-Booking-System.git
cd BookIt-Appointment-Booking-System

# 2. Install dependencies (npm workspaces installs client + server)
npm install

# 3. Configure the server environment
cp server/.env.example server/.env
#   → set DATABASE_URL to your local Postgres, and change JWT_SECRET

# 4. Create the database, apply the schema, and load demo data
npm run db:setup          # 6 providers, services, schedules, sample bookings

# 5. Run the API (:4000) and client (:5173) together
npm run dev

Open http://localhost:5173 — the admin panel is at http://localhost:5173/admin (admin@bookit.local / admin123).

---

📋 Usage

Command	Description
npm run dev	Start API (:4000) and client (:5173) together
npm run build	Production build of server + client
npm run db:migrate	Create the database (if absent) and apply the schema
npm run db:seed	Load demo providers, services, schedules & bookings
npm run db:setup	db:migrate + db:seed in one step

No mail provider? No problem. Leave SMTP unset and every confirmation / cancellation email is written to server/outbox/*.html — open them in a browser to see exactly what the customer would receive.

---

📁 Project Structure

BookIt-Appointment-Booking-System/
├── assets/
│   └── screenshots/          # README imagery
├── docs/
│   ├── ARCHITECTURE.md       # request-to-database walkthrough
│   ├── BookIt_1_Features_Walkthrough.pdf
│   └── BookIt_2_Codebase_Guide.pdf
├── client/                   # React + Vite SPA
│   └── src/
│       ├── pages/            # home, browse, booking flow, confirmation, manage
│       ├── admin/            # dashboard, bookings, day view, provider editor
│       ├── components/       # shared layout / shell
│       ├── api.ts            # typed fetch wrapper (attaches admin JWT)
│       └── App.tsx           # route table
├── server/                   # Express + TypeScript API
│   └── src/
│       ├── db/
│       │   ├── schema.sql     # schema incl. exclusion constraints
│       │   ├── migrate.ts     # creates DB + applies schema (idempotent)
│       │   └── seed.ts        # demo data
│       ├── services/
│       │   ├── slots.ts       # availability engine
│       │   ├── booking.ts     # transactional booking (locks + validation)
│       │   └── email.ts       # confirmation / cancellation emails
│       ├── routes/            # public.ts + admin.ts
│       ├── middleware/        # auth (JWT) + central error handler
│       └── config.ts          # typed env config
├── server/.env.example
├── LICENSE
└── package.json              # npm workspaces root

---

🔌 API Quick Reference

Method	Path	Description
GET	/api/providers?type=doctor	Providers (with services) in a category
GET	/api/providers/:id/slots?serviceId&date=YYYY-MM-DD	Available slots for a date
POST	/api/bookings	Create a booking (409 on conflict)
GET	/api/bookings/lookup?code&email	Look up a booking
POST	/api/bookings/:code/cancel	Customer cancellation
POST	/api/auth/login	Admin login → JWT
GET	/api/admin/stats	Dashboard metrics
GET / PATCH	/api/admin/bookings…	List / change booking status
PUT	/api/admin/providers/:id/schedule	Replace weekly schedule + breaks
POST / DELETE	/api/admin/providers/:id/time-off · /api/admin/time-off/:id	Manage time-off

---

🔭 Future Improvements

• Real payment gateway — the PaymentProvider interface is Razorpay-shaped; add razorpay.ts + a webhook route and set PAYMENT_PROVIDER=razorpay
• SMS / WhatsApp reminders — register a new channel in the notification dispatcher
• Provider self-service portal — providers manage their own schedules
• Test suite — unit tests for the slot engine + integration tests on the conflict layers

---

🤝 Contributing

Contributions, issues, and feature requests are welcome!

1. Fork the project
2. Create your feature branch (git checkout -b feature/amazing-feature)
3. Commit your changes (git commit -m 'Add amazing feature')
4. Push to the branch (git push origin feature/amazing-feature)
5. Open a Pull Request

---

📄 License

Distributed under the MIT License. See LICENSE for details.

---

👤 Author

Bhanu Prakash M

💡 If BookIt helped or impressed you, consider giving the repo a ⭐ — it genuinely helps!

_{Built with React, Express, and PostgreSQL — and a healthy fear of double-bookings.}