# Database Schema — Calendar Booking Platform **Database:** PostgreSQL 15+ **Conventions:** UUID primary keys, `timestamptz` for all timestamps (UTC), soft deletes where noted --- ## Entity Relationship Diagram ```mermaid erDiagram users ||--o{ calendar_connections : has users ||--|| availability_schedules : has users ||--o{ event_types : owns users ||--o{ bookings : receives event_types ||--o{ bookings : defines bookings ||--o{ calendar_event_syncs : syncs_to calendar_connections ||--o{ calendar_event_syncs : via availability_schedules ||--o{ availability_date_overrides : has users { uuid id PK varchar email UK varchar password_hash varchar full_name varchar username UK varchar timezone varchar avatar_url boolean email_verified timestamptz created_at timestamptz updated_at } availability_schedules { uuid id PK uuid user_id FK UK int min_notice_hours int max_advance_days jsonb weekly_rules timestamptz updated_at } availability_date_overrides { uuid id PK uuid schedule_id FK date override_date UK boolean available jsonb intervals } event_types { uuid id PK uuid user_id FK varchar slug varchar title int duration_minutes int buffer_before_minutes int buffer_after_minutes varchar location text description jsonb custom_questions boolean is_active varchar color timestamptz created_at } calendar_connections { uuid id PK uuid user_id FK varchar provider varchar provider_account_email text access_token_enc text refresh_token_enc timestamptz token_expires_at jsonb calendar_config varchar status timestamptz last_synced_at } bookings { uuid id PK uuid user_id FK uuid event_type_id FK varchar guest_name varchar guest_email varchar guest_timezone timestamptz start_time timestamptz end_time jsonb custom_answers varchar status varchar cancel_token_hash timestamptz cancelled_at timestamptz created_at } calendar_event_syncs { uuid id PK uuid booking_id FK uuid connection_id FK varchar external_calendar_id varchar external_event_id varchar sync_status timestamptz synced_at } ``` --- ## Tables ### `users` Hosts who register and manage booking pages. ```sql CREATE TABLE users ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), email VARCHAR(255) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, full_name VARCHAR(255) NOT NULL, username VARCHAR(64) NOT NULL UNIQUE, timezone VARCHAR(64) NOT NULL DEFAULT 'UTC', avatar_url VARCHAR(512), email_verified BOOLEAN NOT NULL DEFAULT FALSE, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), CONSTRAINT username_format CHECK (username ~ '^[a-z0-9-]+$') ); CREATE INDEX idx_users_email ON users(email); CREATE INDEX idx_users_username ON users(username); ``` | Column | Description | |--------|-------------| | `username` | URL slug, e.g. `alice-smith` | | `timezone` | IANA timezone for working hours | | `password_hash` | bcrypt or argon2 hash | --- ### `availability_schedules` One schedule per user (weekly rules + booking window settings). ```sql CREATE TABLE availability_schedules ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), user_id UUID NOT NULL UNIQUE REFERENCES users(id) ON DELETE CASCADE, min_notice_hours INT NOT NULL DEFAULT 4, max_advance_days INT NOT NULL DEFAULT 60, weekly_rules JSONB NOT NULL DEFAULT '[]', updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); ``` **`weekly_rules` JSON shape:** ```json [ { "dayOfWeek": 1, "startTime": "09:00", "endTime": "17:00" }, { "dayOfWeek": 3, "startTime": "09:00", "endTime": "12:00" }, { "dayOfWeek": 3, "startTime": "13:00", "endTime": "17:00" } ] ``` --- ### `availability_date_overrides` Date-specific exceptions (holidays, special hours). ```sql CREATE TABLE availability_date_overrides ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), schedule_id UUID NOT NULL REFERENCES availability_schedules(id) ON DELETE CASCADE, override_date DATE NOT NULL, available BOOLEAN NOT NULL DEFAULT FALSE, intervals JSONB, UNIQUE (schedule_id, override_date) ); CREATE INDEX idx_overrides_schedule_date ON availability_date_overrides(schedule_id, override_date); ``` **`intervals` JSON (when `available = true`):** ```json [ { "startTime": "10:00", "endTime": "14:00" } ] ``` When `available = false`, `intervals` is null (full day off). --- ### `event_types` Bookable meeting templates. ```sql CREATE TABLE event_types ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE, slug VARCHAR(64) NOT NULL, title VARCHAR(255) NOT NULL, duration_minutes INT NOT NULL CHECK (duration_minutes > 0), buffer_before_minutes INT NOT NULL DEFAULT 0, buffer_after_minutes INT NOT NULL DEFAULT 0, location VARCHAR(512), description TEXT, custom_questions JSONB NOT NULL DEFAULT '[]', is_active BOOLEAN NOT NULL DEFAULT TRUE, color VARCHAR(7) DEFAULT '#4F46E5', created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), UNIQUE (user_id, slug) ); CREATE INDEX idx_event_types_user ON event_types(user_id) WHERE is_active = TRUE; ``` **`custom_questions` JSON shape:** ```json [ { "id": "q1", "label": "What would you like to discuss?", "type": "text", "required": true }, { "id": "q2", "label": "Company size", "type": "select", "required": false, "options": ["1-10", "11-50", "51+"] } ] ``` --- ### `calendar_connections` OAuth or CalDAV credentials for external calendars. ```sql CREATE TABLE calendar_connections ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE, provider VARCHAR(32) NOT NULL CHECK (provider IN ('google', 'microsoft', 'apple')), provider_account_email VARCHAR(255) NOT NULL, access_token_enc TEXT, refresh_token_enc TEXT, token_expires_at TIMESTAMPTZ, calendar_config JSONB NOT NULL DEFAULT '[]', status VARCHAR(32) NOT NULL DEFAULT 'connected' CHECK (status IN ('connected', 'expired', 'error', 'disconnected')), last_synced_at TIMESTAMPTZ, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); CREATE INDEX idx_calendar_connections_user ON calendar_connections(user_id); CREATE INDEX idx_calendar_connections_status ON calendar_connections(status) WHERE status != 'disconnected'; ``` **`calendar_config` JSON shape:** ```json [ { "externalId": "primary", "name": "Alice Smith", "checkConflicts": true, "writeTarget": true } ] ``` > Only one calendar across all connections should have `writeTarget: true` (enforce in application layer). --- ### `bookings` Confirmed, pending, or cancelled appointments. ```sql CREATE TABLE bookings ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE, event_type_id UUID NOT NULL REFERENCES event_types(id) ON DELETE RESTRICT, guest_name VARCHAR(255) NOT NULL, guest_email VARCHAR(255) NOT NULL, guest_timezone VARCHAR(64) NOT NULL, start_time TIMESTAMPTZ NOT NULL, end_time TIMESTAMPTZ NOT NULL, custom_answers JSONB NOT NULL DEFAULT '{}', status VARCHAR(32) NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'confirmed', 'cancelled', 'failed')), cancel_token_hash VARCHAR(255) NOT NULL, cancelled_at TIMESTAMPTZ, cancellation_reason TEXT, idempotency_key VARCHAR(64) UNIQUE, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), CHECK (end_time > start_time) ); CREATE INDEX idx_bookings_user_time ON bookings(user_id, start_time) WHERE status IN ('pending', 'confirmed'); CREATE INDEX idx_bookings_guest_email ON bookings(guest_email); CREATE INDEX idx_bookings_status ON bookings(status); CREATE INDEX idx_bookings_time_range ON bookings(user_id, start_time, end_time) WHERE status IN ('pending', 'confirmed'); ``` | Column | Description | |--------|-------------| | `cancel_token_hash` | SHA-256 hash of JWT/opaque cancel token (never store raw token) | | `idempotency_key` | Prevents duplicate bookings on retry | | `start_time` / `end_time` | UTC; duration matches event type | **Conflict prevention query (used inside transaction):** ```sql SELECT COUNT(*) FROM bookings WHERE user_id = $1 AND status IN ('pending', 'confirmed') AND start_time < $3 -- proposed end + buffers AND end_time > $2; -- proposed start - buffers ``` --- ### `calendar_event_syncs` Maps bookings to external calendar events. ```sql CREATE TABLE calendar_event_syncs ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), booking_id UUID NOT NULL REFERENCES bookings(id) ON DELETE CASCADE, connection_id UUID NOT NULL REFERENCES calendar_connections(id) ON DELETE CASCADE, external_calendar_id VARCHAR(255) NOT NULL, external_event_id VARCHAR(512) NOT NULL, sync_status VARCHAR(32) NOT NULL DEFAULT 'pending' CHECK (sync_status IN ('pending', 'synced', 'failed', 'deleted')), last_error TEXT, synced_at TIMESTAMPTZ, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), UNIQUE (booking_id, connection_id) ); CREATE INDEX idx_calendar_syncs_booking ON calendar_event_syncs(booking_id); CREATE INDEX idx_calendar_syncs_pending ON calendar_event_syncs(sync_status) WHERE sync_status = 'pending'; ``` --- ### `busy_blocks` (Optional PostgreSQL cache) Alternative to Redis-only cache for busy intervals. ```sql CREATE TABLE busy_blocks ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE, start_time TIMESTAMPTZ NOT NULL, end_time TIMESTAMPTZ NOT NULL, source VARCHAR(32) NOT NULL CHECK (source IN ('google', 'microsoft', 'apple', 'booking')), source_ref VARCHAR(512), fetched_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), CHECK (end_time > start_time) ); CREATE INDEX idx_busy_blocks_user_range ON busy_blocks(user_id, start_time, end_time); ``` Purged and rebuilt by background sync job. Not source of truth — bookings table is authoritative for app-created events. --- ### `audit_logs` (Optional) ```sql CREATE TABLE audit_logs ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), user_id UUID REFERENCES users(id) ON DELETE SET NULL, action VARCHAR(64) NOT NULL, entity_type VARCHAR(64) NOT NULL, entity_id UUID, metadata JSONB, ip_address INET, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); CREATE INDEX idx_audit_logs_user ON audit_logs(user_id, created_at DESC); CREATE INDEX idx_audit_logs_entity ON audit_logs(entity_type, entity_id); ``` --- ## Migrations Order 1. `users` 2. `availability_schedules` 3. `availability_date_overrides` 4. `event_types` 5. `calendar_connections` 6. `bookings` 7. `calendar_event_syncs` 8. `busy_blocks` (optional) 9. `audit_logs` (optional) --- ## Sample Seed Data ```sql -- Host INSERT INTO users (email, password_hash, full_name, username, timezone, email_verified) VALUES ('alice@example.com', '$2b$12$...', 'Alice Smith', 'alice-smith', 'America/New_York', TRUE); -- Availability: Mon-Fri 9-5 INSERT INTO availability_schedules (user_id, weekly_rules) VALUES ( (SELECT id FROM users WHERE username = 'alice-smith'), '[ {"dayOfWeek":1,"startTime":"09:00","endTime":"17:00"}, {"dayOfWeek":2,"startTime":"09:00","endTime":"17:00"}, {"dayOfWeek":3,"startTime":"09:00","endTime":"17:00"}, {"dayOfWeek":4,"startTime":"09:00","endTime":"17:00"}, {"dayOfWeek":5,"startTime":"09:00","endTime":"17:00"} ]' ); -- Event type INSERT INTO event_types (user_id, slug, title, duration_minutes, buffer_before_minutes, buffer_after_minutes) VALUES ( (SELECT id FROM users WHERE username = 'alice-smith'), '30min-intro', '30 Minute Intro Call', 30, 5, 5 ); ``` Public URL: `https://book.example.com/book/alice-smith/30min-intro` --- ## Indexing Strategy | Query pattern | Index | |---------------|-------| | Lookup host by username | `users(username)` UNIQUE | | List host bookings in date range | `bookings(user_id, start_time)` partial | | Detect booking overlap | `bookings(user_id, start_time, end_time)` partial | | Active event types for host | `event_types(user_id) WHERE is_active` | | Pending calendar syncs | `calendar_event_syncs(sync_status) WHERE pending` | | Busy blocks in range | `busy_blocks(user_id, start_time, end_time)` | --- ## Data Retention | Entity | Retention policy | |--------|------------------| | Cancelled bookings | Keep 12 months, then archive | | Audit logs | 24 months | | Busy blocks cache | Rolling 90 days | | Disconnected calendar tokens | Delete immediately on disconnect | | Guest email (GDPR delete request) | Anonymize guest PII, keep booking timestamps | --- ## Related Documents - [Complete Documentation](./COMPLETE_DOCUMENTATION.md) - [API Reference](./API_REFERENCE.md) - [Calendar Integrations](./CALENDAR_INTEGRATIONS.md)