webscheduler-database

# WebScheduler — Database Schema & Migration Rules ## 1. Runtime DB Conventions - **Prefix:** `xs_` - **Runtime DB:** MySQL/MariaDB only - **SQLite is not supported** as runtime DB - Runtime schema is authoritative - Preferred charset/collation for installs: `utf8mb4` / `utf8mb4_unicode_ci` - **Total application tables: 23** ## 2. Table Catalog by Domain ### 2.1 Identity Tables #### `xs_users` Columns: `id`, `name`, `title` (nullable, added 2026-04-23), `email`, `phone`, `password_hash`, `role`, `created_at`, `updated_at`, `color`, `reset_token`, `reset_expires`, `status`, `profile_image`, `bio` (text, nullable, added 2026-04-23), `education` (text, nullable, added 2026-04-23), `qualifications` (text, nullable, added 2026-04-23), `slug` (unique, nullable, added 2026-04-23), `last_login`, `notify_on_appointments` Notes: - `status` is canonical active-state field - `notify_on_appointments` controls provider/staff appointment notices - `slug` is unique per provider; used in public booking URL `/booking/{serviceSlug}/{providerSlug}` - `title`, `bio`, `education`, `qualifications` are public profile fields for provider profile pages #### `xs_user_roles` Columns: `id`, `user_id`, `role`, `created_at` Notes: - **Authoritative role membership table** - Backfilled from `xs_users.role` in migration `2026-04-08-000001_CreateUserRolesTable.php` #### `xs_customers` Columns: `id`, `hash`, `first_name`, `last_name`, `email`, `phone`, `address`, `notes`, `created_at`, `updated_at`, `custom_fields` Notes: - `hash` is 64-char unique slug for public routes - Index `idx_customers_hash` is present - `custom_fields` stores JSON map in text field (legacy; per-appointment custom fields now also stored in `xs_appointment_custom_fields`) - `email` has unique index `idx_customers_email_unique` (added 2026-04-30) ### 2.2 Scheduling Tables #### `xs_services` Columns: `id`, `name`, `slug` (unique, nullable, added 2026-04-23), `description`, `duration_min`, `buffer_before`, `buffer_after`, `price`, `created_at`, `updated_at`, `category_id`, `active` Notes: - `slug` used in public booking URL `/booking/{serviceSlug}` and `/booking/{serviceSlug}/{providerSlug}` #### `xs_categories` Columns: `id`, `name`, `description`, `color`, `created_at`, `updated_at`, `active` #### `xs_appointments` Columns: `id`, `provider_id`, `service_id`, `start_at`, `end_at`, `stored_timezone`, `status`, `notes`, `hash`, `public_token`, `public_token_expires_at`, `created_at`, `updated_at`, `reminder_sent`, `customer_id`, `location_id`, `location_name`, `location_address`, `location_contact`, `delivery_mode`, `video_link`, `payment_status`, `payment_amount`, `payment_reference` Notes: - Customer linkage is `customer_id → xs_customers.id` - **Do not use deprecated appointment `user_id` linkage in new logic** — caught by grep check - `delivery_mode` (`onsite`/`online_zoom`/`online_jitsi`, nullable→treated as `onsite`) — see `scheduling` skill §13 - `payment_status` ENUM (`none`/`pending`/`paid`/`failed`/`refunded`), `payment_amount` DECIMAL(10,2), `payment_reference` — written by `PaymentService` #### `xs_blocked_times` Columns: `id`, `provider_id`, `start_at`, `end_at`, `reason`, `created_at`, `updated_at` ### 2.3 Availability and Location Tables #### `xs_business_hours` Columns: `id`, `provider_id`, `weekday`, `start_time`, `end_time`, `breaks_json`, `created_at`, `updated_at` Notes: - Runtime uses **`weekday`**, not `day_of_week` - **NO global-only rows.** Every row has a `provider_id`. Querying without a `provider_id` filter returns an arbitrary provider's row — never a "global" hour. See `scheduling` skill §8. #### `xs_provider_schedules` Columns: `id`, `provider_id`, `day_of_week`, `start_time`, `end_time`, `break_start`, `break_end`, `is_active`, `created_at`, `updated_at` Notes: - Runtime does **not** include `location_id` #### `xs_locations` Columns: `id`, `provider_id`, `name`, `address`, `city` (nullable, added 2026-04-23), `area` (nullable, added 2026-04-23), `contact_number`, `is_primary`, `is_active`, `created_at`, `updated_at` #### `xs_location_days` Columns: `id`, `location_id`, `day_of_week` Notes: - Runtime currently exposes **only these 3 columns** - Treat as schema-incomplete for location-hours redesign work #### `xs_providers_services` Columns: `provider_id`, `service_id`, `created_at` #### `xs_provider_staff_assignments` Columns: `id`, `provider_id`, `staff_id`, `assigned_at`, `assigned_by`, `status` ### 2.4 Settings Table #### `xs_settings` Columns: `id`, `setting_key`, `setting_value`, `setting_type`, `created_at`, `updated_at` Notes: - Typed key-value store - Key namespaces: `general.*`, `localization.*`, `booking.*`, `calendar.*`, `notifications.*`, `branding.*`, `security.*` - Runtime may omit `updated_by` in some environments - **Read via `SettingModel::getByKeys(['key'])`** — `getValue()` does not exist ### 2.5 Notification Tables #### `xs_business_notification_rules` Columns: `id`, `business_id`, `event_type`, `channel`, `is_enabled`, `reminder_offset_minutes`, `created_at`, `updated_at` #### `xs_business_integrations` Columns: `id`, `business_id`, `channel`, `provider_name`, `encrypted_config`, `metadata`, `is_active`, `health_status`, `last_tested_at`, `created_at`, `updated_at` Notes: - `channel` ENUM: `email`, `sms`, `whatsapp`, `webhook`, `google_calendar`, `stripe`, `zoom`, `slack`, `jitsi`, `payfast` (expanded 2026-05-20) - `metadata` — nullable TEXT, added 2026-05-20 (webhook last-delivery tracking) - Unique index: `uniq_integration_business_channel_provider` on `(business_id, channel, provider_name)` — replaced original `uniq_integration_business_channel` to allow multiple webhook endpoints per business - `encrypted_config` — AES-encrypted JSON. For Google Calendar includes `client_id`, `client_secret`, `access_token`, `refresh_token`, `token_expiry`. Credentials are admin-configured via UI, never read from .env. #### `xs_message_templates` Columns: `id`, `business_id`, `event_type`, `channel`, `provider`, `provider_template_id`, `locale`, `recipient_class`, `subject`, `body`, `is_active`, `created_at`, `updated_at` Notes: - `recipient_class` separates customer vs internal templates #### `xs_notification_queue` Columns: `id`, `business_id`, `channel`, `event_type`, `appointment_id`, `recipient_type`, `recipient_user_id`, `status`, `attempts`, `max_attempts`, `run_after`, `locked_at`, `lock_token`, `last_error`, `sent_at`, `idempotency_key`, `correlation_id`, `created_at`, `updated_at`, `reminder_offset_minutes`, `schedule_fingerprint` Notes: - Uses `attempts`/`max_attempts` (not legacy `attempt_count`) - Includes `locked_at`, `lock_token`, `correlation_id` - Internal rows resolve recipient from `xs_users` at dispatch - Reminder rows include `reminder_offset_minutes` and `schedule_fingerprint` (added 2026-04-21 migration) - Dispatcher cancels a reminder row if `schedule_fingerprint` no longer matches the live appointment #### `xs_notification_delivery_logs` Columns: `id`, `business_id`, `queue_id`, `correlation_id`, `channel`, `event_type`, `appointment_id`, `recipient`, `provider`, `status`, `attempt`, `error_message`, `created_at`, `updated_at` #### `xs_notification_opt_outs` Columns: `id`, `business_id`, `channel`, `recipient`, `reason`, `created_at`, `updated_at` ### 2.6 Custom Fields Table #### `xs_appointment_custom_fields` Columns: `id`, `appointment_id`, `field_key`, `value` (text, nullable), `created_at`, `updated_at` Notes: - Added 2026-04-30 (`CreateAppointmentCustomFieldsTable` migration) - Unique index on `(appointment_id, field_key)` — `idx_appt_custom_field_unique` - FK: `appointment_id → xs_appointments.id` ON DELETE CASCADE - Stores per-appointment custom field values; replaces reliance on `xs_customers.custom_fields` for appointment-scoped data - Backfilled from legacy customer custom fields on migration - Model: `App\Models\AppointmentCustomFieldModel` ### 2.7 Audit Table #### `xs_audit_logs` Columns: `id`, `user_id`, `action`, `target_type`, `target_id`, `old_value`, `new_value`, `ip_address`, `user_agent`, `created_at` Notes: - Verify table name as `xs_audit_logs` in mixed environments #### `xs_api_keys` Columns: `id`, `business_id` (default `1`), `user_id`, `name`, `key_prefix` (UNIQUE), `key_hash`, `scopes` (JSON text, nullable), `last_used_at`, `last_used_ip`, `expires_at`, `revoked_at`, `created_by`, `created_at`, `updated_at` Notes: - Model: `App\Models\ApiKeyModel` (`generate()`, `verify()`, `findActiveByPrefix()`, `touch()`, `revoke()`) - External API bearer tokens. **Only `key_prefix` and `password_hash()` of the secret are stored** — never the plaintext. Verification is an indexed lookup on `key_prefix` followed by `password_verify()`. - Every key is bound to an `xs_users` row; that user supplies the roles the token request runs under (see `api-contract` §Authentication). - `revoked_at` is a soft revocation; `expires_at` NULL means no expiry. `scopes` NULL means "inherit the bound user's role permissions". - `business_id` is **forward-compatibility only** — there is no businesses table and every row resolves to `1`. - Indexes: UNIQUE on `key_prefix`; composite `(user_id, revoked_at)` ### 2.8 Payment Table #### `xs_payment_transactions`