Repository Public
1-- SP Calendar — initial schema.2-- Instants are timestamptz (UTC). Wall-clock values (rules, exceptions) are minutes of the day or3-- "timestamp without time zone", always interpreted in the owner's IANA time zone (settings.timezone).45CREATE EXTENSION IF NOT EXISTS btree_gist;6CREATE EXTENSION IF NOT EXISTS citext;78-- ── Owner, authentication ────────────────────────────────────────────────────────────────────────────9CREATE TABLE users (10 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),11 email citext NOT NULL UNIQUE,12 display_name text NOT NULL,13 created_at timestamptz NOT NULL DEFAULT now()14);1516CREATE TABLE authenticators (17 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),18 user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,19 credential_id text NOT NULL UNIQUE, -- base64url20 public_key bytea NOT NULL,21 counter bigint NOT NULL DEFAULT 0,22 transports text[] NOT NULL DEFAULT '{}',23 device_type text,24 backed_up boolean NOT NULL DEFAULT false,25 name text NOT NULL DEFAULT 'Passkey',26 created_at timestamptz NOT NULL DEFAULT now(),27 last_used_at timestamptz28);2930-- One-time enrolment tokens (first setup and recovery), issued only from the server's shell.31CREATE TABLE setup_tokens (32 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),33 user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,34 token_hash text NOT NULL UNIQUE,35 purpose text NOT NULL CHECK (purpose IN ('setup', 'recovery')),36 expires_at timestamptz NOT NULL,37 used_at timestamptz,38 created_at timestamptz NOT NULL DEFAULT now()39);4041-- Recovery codes shown once when the first passkey is enrolled (stored hashed).42CREATE TABLE recovery_codes (43 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),44 user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,45 code_hash text NOT NULL UNIQUE,46 used_at timestamptz,47 created_at timestamptz NOT NULL DEFAULT now()48);4950CREATE TABLE webauthn_challenges (51 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),52 challenge text NOT NULL,53 kind text NOT NULL CHECK (kind IN ('registration', 'authentication')),54 user_id uuid REFERENCES users(id) ON DELETE CASCADE,55 expires_at timestamptz NOT NULL56);5758CREATE TABLE sessions (59 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),60 user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,61 token_hash text NOT NULL UNIQUE,62 csrf_token text NOT NULL,63 method text NOT NULL, -- passkey | recovery_code | setup64 user_agent text,65 ip text,66 created_at timestamptz NOT NULL DEFAULT now(),67 last_seen_at timestamptz NOT NULL DEFAULT now(),68 expires_at timestamptz NOT NULL,69 revoked_at timestamptz70);71CREATE INDEX sessions_user_idx ON sessions (user_id, expires_at);7273-- ── Settings (one row per owner) ─────────────────────────────────────────────────────────────────────74CREATE TABLE settings (75 user_id uuid PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,76 timezone text NOT NULL DEFAULT 'America/Toronto',77 locale text NOT NULL DEFAULT 'fr' CHECK (locale IN ('fr', 'en')),78 public_name text NOT NULL DEFAULT '',79 public_headline text NOT NULL DEFAULT '',80 public_headline_en text NOT NULL DEFAULT '',81 public_bio text NOT NULL DEFAULT '',82 public_bio_en text NOT NULL DEFAULT '',83 avatar bytea,84 avatar_mime text,85 brand_color text NOT NULL DEFAULT '#2257e0',86 notify_email citext, -- where the owner receives alerts87 reply_to citext,88 retention_days integer NOT NULL DEFAULT 730 CHECK (retention_days BETWEEN 30 AND 3650),89 allowed_email_domains text[] NOT NULL DEFAULT '{}', -- empty = any domain may book90 blocked_email_domains text[] NOT NULL DEFAULT '{}',91 external_stale_minutes integer NOT NULL DEFAULT 60 CHECK (external_stale_minutes BETWEEN 5 AND 10080),92 external_stale_policy text NOT NULL DEFAULT 'conservative' CHECK (external_stale_policy IN ('conservative', 'ignore')),93 feed_token_hash text, -- private ICS feed of confirmed bookings94 updated_at timestamptz NOT NULL DEFAULT now()95);9697-- ── Appointment types ────────────────────────────────────────────────────────────────────────────────98CREATE TABLE event_types (99 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),100 user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,101 slug text NOT NULL UNIQUE CHECK (slug ~ '^[a-z0-9](?:[a-z0-9-]{0,62}[a-z0-9])?$'),102 title text NOT NULL,103 title_en text NOT NULL DEFAULT '',104 description text NOT NULL DEFAULT '',105 description_en text NOT NULL DEFAULT '',106 duration_min integer NOT NULL CHECK (duration_min BETWEEN 5 AND 720),107 color text NOT NULL DEFAULT '#2257e0',108 location_kind text NOT NULL DEFAULT 'video' CHECK (location_kind IN ('video', 'phone', 'in_person')),109 location_detail text NOT NULL DEFAULT '', -- link / number / address, sent only once confirmed110 instructions text NOT NULL DEFAULT '',111 instructions_en text NOT NULL DEFAULT '',112 languages text[] NOT NULL DEFAULT '{fr}',113 approval text NOT NULL DEFAULT 'manual' CHECK (approval IN ('manual', 'auto')),114 hold_policy text NOT NULL DEFAULT 'hold' CHECK (hold_policy IN ('hold', 'none')),115 hold_ttl_hours integer NOT NULL DEFAULT 48 CHECK (hold_ttl_hours BETWEEN 1 AND 720),116 buffer_before_min integer NOT NULL DEFAULT 0 CHECK (buffer_before_min BETWEEN 0 AND 240),117 buffer_after_min integer NOT NULL DEFAULT 0 CHECK (buffer_after_min BETWEEN 0 AND 240),118 slot_step_min integer NOT NULL DEFAULT 30 CHECK (slot_step_min BETWEEN 5 AND 240),119 min_notice_min integer NOT NULL DEFAULT 240 CHECK (min_notice_min BETWEEN 0 AND 43200),120 horizon_days integer NOT NULL DEFAULT 60 CHECK (horizon_days BETWEEN 1 AND 365),121 daily_limit integer CHECK (daily_limit IS NULL OR daily_limit > 0),122 weekly_limit integer CHECK (weekly_limit IS NULL OR weekly_limit > 0),123 questions jsonb NOT NULL DEFAULT '[]', -- [{key,label,label_en,kind,required}]124 ask_organization boolean NOT NULL DEFAULT true,125 require_reason boolean NOT NULL DEFAULT true,126 allow_visitor_cancel boolean NOT NULL DEFAULT true,127 allow_visitor_reschedule boolean NOT NULL DEFAULT true,128 reminder_hours integer CHECK (reminder_hours IS NULL OR reminder_hours BETWEEN 1 AND 336),129 custom_availability boolean NOT NULL DEFAULT false, -- true: uses its own weekly rules130 active boolean NOT NULL DEFAULT true,131 position integer NOT NULL DEFAULT 0,132 created_at timestamptz NOT NULL DEFAULT now(),133 updated_at timestamptz NOT NULL DEFAULT now()134);135136-- ── Availability ─────────────────────────────────────────────────────────────────────────────────────137-- Weekly opening hours. event_type_id NULL = the default schedule.138CREATE TABLE availability_rules (139 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),140 user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,141 event_type_id uuid REFERENCES event_types(id) ON DELETE CASCADE,142 weekday smallint NOT NULL CHECK (weekday BETWEEN 1 AND 7), -- ISO: 1 = Monday143 start_minute smallint NOT NULL CHECK (start_minute BETWEEN 0 AND 1439),144 end_minute smallint NOT NULL CHECK (end_minute BETWEEN 1 AND 1440),145 CHECK (end_minute > start_minute)146);147CREATE INDEX availability_rules_idx ON availability_rules (user_id, event_type_id, weekday);148149-- Exceptions to the weekly hours, in wall-clock time: extra openings and closures (leave, holidays,150-- recurring absences). A 'range' may span several days and cross midnight.151CREATE TABLE availability_exceptions (152 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),153 user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,154 event_type_id uuid REFERENCES event_types(id) ON DELETE CASCADE, -- NULL = every type155 kind text NOT NULL CHECK (kind IN ('open', 'closed')),156 mode text NOT NULL CHECK (mode IN ('range', 'weekly')),157 starts_local timestamp, -- mode = range158 ends_local timestamp,159 all_day boolean NOT NULL DEFAULT false,160 weekdays smallint[], -- mode = weekly161 start_minute smallint CHECK (start_minute BETWEEN 0 AND 1439),162 duration_min integer CHECK (duration_min BETWEEN 1 AND 1440),163 valid_from date,164 valid_until date,165 note text NOT NULL DEFAULT '',166 created_at timestamptz NOT NULL DEFAULT now(),167 CHECK (mode <> 'range' OR (starts_local IS NOT NULL AND ends_local IS NOT NULL AND ends_local > starts_local)),168 CHECK (mode <> 'weekly' OR (weekdays IS NOT NULL AND cardinality(weekdays) > 0 AND start_minute IS NOT NULL AND duration_min IS NOT NULL AND valid_from IS NOT NULL))169);170CREATE INDEX availability_exceptions_idx ON availability_exceptions (user_id);171172-- ── External calendars (declared before busy_blocks, which references them) ──────────────────────────173CREATE TABLE external_calendar_accounts (174 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),175 user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,176 provider text NOT NULL CHECK (provider IN ('ics', 'google', 'microsoft')),177 label text NOT NULL,178 secret_enc bytea NOT NULL, -- AES-256-GCM: ICS URL, or OAuth refresh token179 status text NOT NULL DEFAULT 'connected' CHECK (status IN ('connected', 'error', 'disconnected')),180 created_at timestamptz NOT NULL DEFAULT now()181);182183CREATE TABLE external_calendars (184 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),185 account_id uuid NOT NULL REFERENCES external_calendar_accounts(id) ON DELETE CASCADE,186 user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,187 external_id text NOT NULL DEFAULT 'default',188 name text NOT NULL,189 blocks_availability boolean NOT NULL DEFAULT true,190 write_events boolean NOT NULL DEFAULT false, -- never true for ICS subscriptions (read-only)191 created_at timestamptz NOT NULL DEFAULT now(),192 UNIQUE (account_id, external_id)193);194195CREATE TABLE sync_state (196 calendar_id uuid PRIMARY KEY REFERENCES external_calendars(id) ON DELETE CASCADE,197 last_attempt_at timestamptz,198 last_success_at timestamptz,199 last_error text,200 etag text,201 events_count integer NOT NULL DEFAULT 0,202 consecutive_failures integer NOT NULL DEFAULT 0203);204205-- ── Time that is taken ───────────────────────────────────────────────────────────────────────────────206-- Private events created by the owner, and busy periods read from external calendars.207CREATE TABLE busy_blocks (208 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),209 user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,210 starts_at timestamptz NOT NULL,211 ends_at timestamptz NOT NULL,212 title text NOT NULL DEFAULT '',213 note text NOT NULL DEFAULT '',214 source text NOT NULL DEFAULT 'manual' CHECK (source IN ('manual', 'external')),215 external_calendar_id uuid REFERENCES external_calendars(id) ON DELETE CASCADE,216 external_uid text,217 created_at timestamptz NOT NULL DEFAULT now(),218 CHECK (ends_at > starts_at)219);220CREATE INDEX busy_blocks_range_idx ON busy_blocks USING gist (user_id, tstzrange(starts_at, ends_at));221222CREATE TABLE contacts (223 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),224 user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,225 email citext NOT NULL,226 name text NOT NULL,227 organization text NOT NULL DEFAULT '',228 locale text NOT NULL DEFAULT 'fr',229 notes text NOT NULL DEFAULT '',230 created_at timestamptz NOT NULL DEFAULT now(),231 updated_at timestamptz NOT NULL DEFAULT now(),232 UNIQUE (user_id, email)233);234235-- Confirmed appointments. The exclusion constraint is the last line of defence against double booking:236-- two confirmed bookings of the same owner can never overlap, whatever the application does.237CREATE TABLE bookings (238 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),239 user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,240 event_type_id uuid REFERENCES event_types(id) ON DELETE SET NULL,241 contact_id uuid REFERENCES contacts(id) ON DELETE SET NULL,242 title text NOT NULL,243 starts_at timestamptz NOT NULL,244 ends_at timestamptz NOT NULL,245 buffer_before_min integer NOT NULL DEFAULT 0,246 buffer_after_min integer NOT NULL DEFAULT 0,247 status text NOT NULL DEFAULT 'confirmed' CHECK (status IN ('confirmed', 'cancelled')),248 location_kind text NOT NULL DEFAULT 'video',249 location_text text NOT NULL DEFAULT '',250 visitor_tz text NOT NULL DEFAULT 'America/Toronto',251 locale text NOT NULL DEFAULT 'fr',252 ics_uid text NOT NULL UNIQUE,253 ics_sequence integer NOT NULL DEFAULT 0,254 cancelled_at timestamptz,255 cancelled_by text CHECK (cancelled_by IN ('admin', 'visitor')),256 cancel_reason text NOT NULL DEFAULT '',257 reminder_job_done boolean NOT NULL DEFAULT false,258 created_at timestamptz NOT NULL DEFAULT now(),259 updated_at timestamptz NOT NULL DEFAULT now(),260 CHECK (ends_at > starts_at),261 CONSTRAINT bookings_no_overlap EXCLUDE USING gist (user_id WITH =, tstzrange(starts_at, ends_at) WITH &&) WHERE (status = 'confirmed')262);263CREATE INDEX bookings_time_idx ON bookings (user_id, starts_at);264CREATE INDEX bookings_contact_idx ON bookings (contact_id);265266-- Requests made by visitors. A pending request is NOT an appointment.267-- pending → approved | declined | cancelled | expired | reschedule_requested268-- reschedule_requested → approved (visitor accepts the proposal, re-checked) | cancelled | declined | expired269-- approved → cancelled (when its booking is cancelled)270-- declined, cancelled, expired are final.271CREATE TABLE booking_requests (272 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),273 user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,274 event_type_id uuid NOT NULL REFERENCES event_types(id) ON DELETE CASCADE,275 contact_id uuid REFERENCES contacts(id) ON DELETE SET NULL,276 starts_at timestamptz NOT NULL,277 ends_at timestamptz NOT NULL,278 visitor_tz text NOT NULL,279 locale text NOT NULL DEFAULT 'fr',280 status text NOT NULL DEFAULT 'pending'281 CHECK (status IN ('pending', 'approved', 'declined', 'expired', 'cancelled', 'reschedule_requested')),282 hold_expires_at timestamptz, -- NULL = this request does not hold its slot283 idempotency_key text NOT NULL UNIQUE,284 reason text NOT NULL DEFAULT '', -- confidential: never in URLs, logs or ICS285 internal_note text NOT NULL DEFAULT '',286 decline_message text NOT NULL DEFAULT '',287 proposed_starts_at timestamptz,288 proposed_ends_at timestamptz,289 proposal_expires_at timestamptz,290 proposal_message text NOT NULL DEFAULT '',291 reschedule_of_booking_id uuid REFERENCES bookings(id) ON DELETE CASCADE, -- visitor asked to move a confirmed booking292 booking_id uuid REFERENCES bookings(id) ON DELETE SET NULL,293 conflict_flag text NOT NULL DEFAULT '', -- e.g. 'external_stale': auto-confirmation was withheld294 consent_at timestamptz NOT NULL,295 ip text,296 created_at timestamptz NOT NULL DEFAULT now(),297 decided_at timestamptz,298 updated_at timestamptz NOT NULL DEFAULT now(),299 CHECK (ends_at > starts_at)300);301CREATE INDEX booking_requests_status_idx ON booking_requests (user_id, status, starts_at);302CREATE INDEX booking_requests_contact_idx ON booking_requests (contact_id);303304CREATE TABLE booking_answers (305 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),306 request_id uuid NOT NULL REFERENCES booking_requests(id) ON DELETE CASCADE,307 question_key text NOT NULL,308 label text NOT NULL,309 value text NOT NULL310);311CREATE INDEX booking_answers_request_idx ON booking_answers (request_id);312313-- Visitor management links: opaque random tokens, stored hashed.314CREATE TABLE booking_tokens (315 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),316 request_id uuid NOT NULL REFERENCES booking_requests(id) ON DELETE CASCADE,317 token_hash text NOT NULL UNIQUE,318 expires_at timestamptz NOT NULL,319 revoked_at timestamptz,320 created_at timestamptz NOT NULL DEFAULT now()321);322CREATE INDEX booking_tokens_request_idx ON booking_tokens (request_id);323324-- ── Outbox, audit, rate limits ───────────────────────────────────────────────────────────────────────325CREATE TABLE notification_jobs (326 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),327 user_id uuid REFERENCES users(id) ON DELETE CASCADE,328 kind text NOT NULL,329 dedup_key text NOT NULL UNIQUE,330 to_email citext NOT NULL,331 locale text NOT NULL DEFAULT 'fr',332 request_id uuid REFERENCES booking_requests(id) ON DELETE CASCADE,333 booking_id uuid REFERENCES bookings(id) ON DELETE CASCADE,334 payload jsonb NOT NULL DEFAULT '{}',335 status text NOT NULL DEFAULT 'queued'336 CHECK (status IN ('queued', 'sending', 'sent', 'failed', 'dead', 'unconfigured', 'cancelled')),337 attempts integer NOT NULL DEFAULT 0,338 run_at timestamptz NOT NULL DEFAULT now(),339 locked_at timestamptz,340 last_error text,341 transport text,342 provider_message_id text,343 sent_at timestamptz,344 created_at timestamptz NOT NULL DEFAULT now()345);346CREATE INDEX notification_jobs_due_idx ON notification_jobs (status, run_at);347348CREATE TABLE audit_events (349 id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,350 user_id uuid REFERENCES users(id) ON DELETE CASCADE,351 actor text NOT NULL CHECK (actor IN ('admin', 'visitor', 'system')),352 action text NOT NULL,353 entity text NOT NULL DEFAULT '',354 entity_id text NOT NULL DEFAULT '',355 meta jsonb NOT NULL DEFAULT '{}',356 ip text,357 created_at timestamptz NOT NULL DEFAULT now()358);359CREATE INDEX audit_events_time_idx ON audit_events (created_at DESC);360CREATE INDEX audit_events_entity_idx ON audit_events (entity, entity_id);361362CREATE TABLE rate_limits (363 key text NOT NULL,364 window_start timestamptz NOT NULL,365 count integer NOT NULL DEFAULT 0,366 PRIMARY KEY (key, window_start)367);368369CREATE TABLE worker_heartbeat (370 name text PRIMARY KEY,371 beat_at timestamptz NOT NULL DEFAULT now(),372 detail jsonb NOT NULL DEFAULT '{}'373);374