OpenChair Booking

FastAPI and PostgreSQL booking system with a same-origin owner dashboard. A business owner can create an account, edit business details, add personnel and services, define weekly working hours and breaks for each person, and manage the daily appointment list.

The dashboard is deliberately plain HTML, CSS, and JavaScript. It is served by the API itself, so there is no Node build or separate frontend deployment to configure.

Run it

The simplest complete setup is Docker Compose:

docker compose up --build

Compose waits for PostgreSQL, applies every missing Alembic migration, and starts the API. Open:

• Owner dashboard: http://localhost:8000/dashboard/
• Interactive API docs: http://localhost:8000/docs
• Health endpoint: http://localhost:8000/health

For local Python development instead:

venv/bin/python -m pip install -r requirements.txt
docker compose up -d postgres
venv/bin/alembic upgrade head
venv/bin/uvicorn app.main:app --reload

The API intentionally does not create or alter tables when an application worker starts. alembic upgrade head is the explicit schema deployment step.

Owner portal flow

1. Visit /dashboard/ and create an account. Registration atomically creates the user, their first business, an owner membership, and a login session.
2. Add active team members under Team.
3. Add duration and optional price information under Services.
4. Choose each team member under Working hours and save their weekly hours and optional break.
5. Use Appointments to review a date and move confirmed bookings to completed, no-show, or cancelled.

Passwords are salted with scrypt. The browser receives a random opaque session in an HttpOnly, SameSite=Lax cookie; only its SHA-256 digest is stored in PostgreSQL. Sessions are revocable and expire after SESSION_DAYS. Every owner API query checks the authenticated user's business membership. Roles currently supported by the schema are owner, manager, and staff; owner and manager roles may change configuration.

The old unauthenticated business/staff/service configuration routers are not mounted. Customer-facing availability and appointment creation remain public; appointment listing and cancellation now require a business membership.

WhatsApp chatbot: complete setup

The included chatbot uses Meta's official WhatsApp Cloud API. A customer can send BOOK, choose a numbered service, enter a date, choose a live slot, and receive an appointment confirmation. The business is inferred from Meta's phone_number_id; it is never accepted from the customer's message.

1. Prepare the booking business

In the owner dashboard:

1. Create at least one active team member.
2. Create at least one active service with a duration.
3. Set working hours for that team member. Days without working hours produce no slots.
4. Under Business → WhatsApp chatbot, enter Meta's Phone number ID, not the visible telephone number. The display number is optional.

The MVP assumes every active team member can perform every active service. Service-to-staff capability mapping would be a separate domain feature.

2. Create the Meta application

In Meta for Developers:

1. Create or select a business app and add the WhatsApp product.
2. Connect the WhatsApp Business Account and register a sending phone number.
3. Record the WhatsApp Business Account ID (WABA_ID) and the numeric Phone number ID shown under API Setup.
4. Copy the Meta App Secret from the app settings.
5. Create a production system-user access token. Sending requires whatsapp_business_messaging; subscribing the app to the WABA requires whatsapp_business_management. Temporary dashboard tokens are suitable only for short tests.
6. Choose your own long random webhook verification token. It is not supplied by Meta and must be different from the access token and App Secret.

Generate a verification token, for example:

openssl rand -hex 32

Official references:

• https://developers.facebook.com/docs/whatsapp/cloud-api
• https://developers.facebook.com/docs/graph-api/webhooks/getting-started/webhooks-for-whatsapp
• https://www.postman.com/meta/whatsapp-business-platform/request/8gvd47s/send-text-message

3. Configure server-side secrets

Create the ignored local configuration file:

cp .env.example .env.local

Set these values in .env.local:

WHATSAPP_VERIFY_TOKEN=the-random-value-you-created
WHATSAPP_APP_SECRET=the-meta-app-secret
WHATSAPP_ACCESS_TOKEN=the-system-user-access-token
WHATSAPP_GRAPH_API_VERSION=v25.0
WHATSAPP_BOOKING_HORIZON_DAYS=90
WHATSAPP_CONVERSATION_TTL_MINUTES=60

.env.local is ignored by Git and excluded from Docker builds. Never paste the App Secret or access token into the owner dashboard, source code, logs, or database. In production, inject them from the hosting platform's secret manager instead of a file.

Restart the API after changing secrets:

venv/bin/alembic upgrade head
venv/bin/uvicorn app.main:app --reload

For Docker:

docker compose up --build

Compose reads .env.local into the backend container and runs the migration before starting the API.

4. Expose the HTTPS callback

Meta must reach the API over public HTTPS with a valid certificate. The callback path is:

https://your-api.example.com/webhooks/whatsapp

For local development, expose http://localhost:8000 through a trusted HTTPS tunnel and use the tunnel's HTTPS hostname. A plain localhost URL or self-signed certificate cannot receive Meta webhooks.

In the Meta app's WhatsApp webhook configuration:

1. Set the callback URL above.
2. Enter exactly the same WHATSAPP_VERIFY_TOKEN value.
3. Subscribe the whatsapp_business_account object to the messages field.

The verification GET request is accepted only when hub.mode=subscribe and the token matches; the API returns Meta's raw hub.challenge. Every POST is then verified using X-Hub-Signature-256, an HMAC-SHA256 over the exact raw body with the Meta App Secret. Invalid or unsigned payloads are rejected before database access.

You can test the verification endpoint yourself:

curl "http://localhost:8000/webhooks/whatsapp?hub.mode=subscribe&hub.verify_token=YOUR_VERIFY_TOKEN&hub.challenge=123456"

The response should be exactly:

123456

5. Subscribe the app to the WABA

The webhook field configuration and WABA subscription are separate. Subscribe the app once with a system-user token:

curl -X POST \
  "https://graph.facebook.com/v25.0/YOUR_WABA_ID/subscribed_apps" \
  -H "Authorization: Bearer YOUR_SYSTEM_USER_ACCESS_TOKEN"

A successful response is:

{"success": true}

6. Test the customer conversation

Send BOOK from a WhatsApp account to the connected business number. The conversation is deterministic and does not require an AI model:

Customer: BOOK
Bot:      1. Haircut (30 min) — €25.00

Customer: 1
Bot:      Which date? Reply with YYYY-MM-DD, TODAY, or TOMORROW.

Customer: 2026-08-12
Bot:      1. 09:00 — Giulia
          2. 09:30 — Giulia

Customer: 1
Bot:      Your appointment is confirmed! Reference: 42 ...

TODAY/TOMORROW and OGGI/DOMANI are accepted. CANCEL or ANNULLA resets an unfinished conversation; it does not cancel an already-created appointment. BOOK, MENU, START, HELLO, HI, CIAO, or PRENOTA starts a new booking flow.

Immediately before booking, the bot recomputes availability and exact-matches the selected staff/time. PostgreSQL's exclusion constraint is still the final race-safe guard. Meta retries are deduplicated by inbound wamid, and the booking uses the same message ID as its idempotency key, so a retry cannot create a second appointment.

7. Verify stored data

Inspect configured channels:

docker compose exec postgres psql -U booking_user -d booking_db \
  -c "SELECT c.id, c.business_id, b.name, c.phone_number_id, c.active FROM whatsapp_channels c JOIN businesses b ON b.id = c.business_id ORDER BY c.id;"

Inspect conversation state:

docker compose exec postgres psql -U booking_user -d booking_db \
  -c "SELECT channel_id, whatsapp_id, state, updated_at FROM whatsapp_conversations ORDER BY updated_at DESC;"

Inspect appointments created by the chatbot:

docker compose exec postgres psql -U booking_user -d booking_db \
  -c "SELECT id, business_id, staff_member_id, service_id, start_time, status FROM appointments WHERE source = 'whatsapp' ORDER BY created_at DESC;"

Troubleshooting

• Meta cannot verify the callback: confirm public HTTPS, the exact callback path, hub.mode=subscribe, and matching verify-token values.
• Webhook returns 403: the POST signature does not match the raw body; verify WHATSAPP_APP_SECRET and ensure a proxy is not rewriting the body.
• Webhook returns 502: check the access token, its permissions, the pinned Graph API version, and docker compose logs -f backend.
• Messages are ignored: the inbound metadata.phone_number_id does not match an active channel saved under the business dashboard.
• No slots appear: verify active staff/service records, the business IANA timezone, working hours for the selected date, breaks, and existing active appointments.
• Replies work only after the customer writes: free-form WhatsApp replies are restricted to the rolling 24-hour customer-service window. Future reminders require an approved WhatsApp template and customer consent.
• Changed an environment value: restart the API/container; settings are read at process startup.

This implementation sends replies synchronously after persisting the reply. For high traffic, move outbound delivery to a durable worker/outbox so webhook acknowledgements remain fast during Graph API outages.

Connecting another website

Using the included same-origin dashboard is recommended. If a separate browser application needs the same API, configure its exact origins as a comma-separated list:

FRONTEND_ORIGINS=https://owner.example.com,http://localhost:5173
SECURE_COOKIES=true
SESSION_DAYS=7

Send JSON requests with credentials enabled:

const login = await fetch("https://api.example.com/auth/login", {
  method: "POST",
  credentials: "include",
  headers: { "Content-Type": "application/json" },
  body: JSON.stringify({
    email: "owner@example.com",
    password: "a-long-owner-password"
  })
});

const businesses = await fetch("https://api.example.com/owner/businesses", {
  credentials: "include"
}).then(response => response.json());

For production, use HTTPS, set SECURE_COOKIES=true, list only trusted CORS origins, and replace the demonstration database password in Compose. Keeping the API and owner frontend on the same site avoids third-party-cookie policy differences between browsers.

Owner API map

POST   /auth/register
POST   /auth/login
POST   /auth/logout
GET    /auth/me

GET    /owner/businesses
POST   /owner/businesses
GET    /owner/businesses/{business_id}
PATCH  /owner/businesses/{business_id}

GET    /owner/businesses/{business_id}/whatsapp
PUT    /owner/businesses/{business_id}/whatsapp

GET    /owner/businesses/{business_id}/staff
POST   /owner/businesses/{business_id}/staff
PATCH  /owner/businesses/{business_id}/staff/{staff_id}

GET    /owner/businesses/{business_id}/services
POST   /owner/businesses/{business_id}/services
PATCH  /owner/businesses/{business_id}/services/{service_id}

GET    /owner/businesses/{business_id}/staff/{staff_id}/working-hours
PUT    /owner/businesses/{business_id}/staff/{staff_id}/working-hours

GET    /owner/businesses/{business_id}/appointments?target_date=YYYY-MM-DD
PATCH  /owner/businesses/{business_id}/appointments/{appointment_id}/status

GET    /webhooks/whatsapp
POST   /webhooks/whatsapp

The OpenAPI page at /docs contains the exact request and response schemas.

Public booking API

Customer websites can request availability and create a booking through the existing endpoints:

GET  /availability?business_id=1&service_id=1&target_date=2035-01-15
POST /appointments

Every POST /appointments request must include a stable, unique Idempotency-Key header. Retrying the same payload with the same key returns the original booking. PostgreSQL also rejects overlapping active appointments, so two customers racing for one slot cannot both win.

Migration handling

An ORM model describes the schema the current application expects. An Alembic migration is an ordered, reviewable operation that moves an existing database from one known version to the next while preserving its data.

Alembic stores the installed revision in the database's alembic_version table. upgrade head reads that value and runs each missing revision once, in order. This project currently has:

base
└── 0001_initial_schema
    └── 0002_atomic_bookings
        └── 0003_owner_portal
            └── 0004_whatsapp_chatbot (head)

• 0001_initial_schema creates businesses, staff, services, weekly schedules, customers, and appointments.
• 0002_atomic_bookings adds idempotency records, positive-duration checks, and PostgreSQL GiST overlap protection.
• 0003_owner_portal backfills a stable slug for every existing business and adds users, memberships, and revocable authentication sessions.
• 0004_whatsapp_chatbot adds business phone-number channels, persistent conversation state, inbound-message deduplication, and reply-delivery state.

Useful commands:

# Show the database's installed revision
venv/bin/alembic current

# Show the complete revision chain
venv/bin/alembic history --verbose

# Apply every missing migration
venv/bin/alembic upgrade head

# Compare the migrated schema with ORM metadata
venv/bin/alembic check

# Roll back one revision in a disposable development/test database
venv/bin/alembic downgrade -1

Downgrades are not automatically safe in production. Downgrading 0004_whatsapp_chatbot removes channel configuration and conversation history; it does not delete appointments already booked through WhatsApp. Downgrading 0003_owner_portal removes all account, membership, and session records; downgrading 0002_atomic_bookings removes the database-level double-booking guard. Prefer a new forward migration when data or availability could be lost.

Creating the next migration

After changing app/db/models.py:

venv/bin/alembic revision --autogenerate -m "describe the schema change"

Review the generated revision under alembic/versions/. Autogeneration is a starting point: data backfills, PostgreSQL-specific constraints, locking, and safe rollout checks often require handwritten operations. Test both upgrade and downgrade. Never edit a migration that has already run on a shared database; add a new forward revision instead.

alembic stamp records a version without running its DDL. Use it only after manually proving an unversioned legacy schema already matches that exact revision. It is not a repair for a partially applied migration.

Tests

Install the project dependencies and run against PostgreSQL:

venv/bin/python -m pip install -r requirements.txt
TEST_DATABASE_URL=postgresql://user:password@localhost/database \
  venv/bin/python -m unittest discover -s tests -v

Tests create randomly named schemas and remove them afterward. Coverage includes migration upgrade/downgrade/re-upgrade and drift detection, booking rollback and concurrency, idempotent retries, registration and cookies, cross-business authorization, owner setup and weekly hours, logout, and static dashboard delivery. WhatsApp coverage includes verification challenges, raw body signatures, status callbacks, channel configuration, a complete booking dialogue, duplicate webhook delivery, and creation of exactly one appointment.