# MySQL Developer Guide — Calendar Booking Platform **Database:** MySQL 8.0+ **ORM (recommended):** TypeORM or Prisma **Charset:** `utf8mb4` / `utf8mb4_unicode_ci` **Conventions:** `CHAR(36)` UUIDs, `DATETIME(3)` stored in UTC, JSON columns for flexible fields --- ## Table of Contents 1. [Overview](#1-overview) 2. [Setup and Configuration](#2-setup-and-configuration) 3. [Naming Conventions](#3-naming-conventions) 4. [Entity Relationship Diagram](#4-entity-relationship-diagram) 5. [Table Definitions](#5-table-definitions) 6. [Embed and API Key Tables](#6-embed-and-api-key-tables) 7. [Indexes and Performance](#7-indexes-and-performance) 8. [Migrations](#8-migrations) 9. [Sample Queries](#9-sample-queries) 10. [TypeORM Entity Examples](#10-typeorm-entity-examples) 11. [Backup and Operations](#11-backup-and-operations) --- ## 1. Overview MySQL is the primary data store for the Node.js backend. All application timestamps are written in **UTC**; timezone conversion happens in the application layer (Node + Angular). ### Design Principles | Principle | Implementation | |-----------|----------------| | Primary keys | UUID v4 as `CHAR(36)` | | Timestamps | `DATETIME(3)` — millisecond precision, UTC | | Flexible config | MySQL `JSON` type (validated in app layer) | | Referential integrity | Foreign keys with `ON DELETE CASCADE` or `RESTRICT` | | Soft delete | `is_active` / `deleted_at` where needed | | Embeddable widget | Separate `embed_configs` and `api_keys` tables | --- ## 2. Setup and Configuration ### Create Database ```sql CREATE DATABASE calendar_booking CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE USER 'calendar_app'@'%' IDENTIFIED BY 'your_secure_password'; GRANT SELECT, INSERT, UPDATE, DELETE ON calendar_booking.* TO 'calendar_app'@'%'; FLUSH PRIVILEGES; ``` ### Node.js Connection (TypeORM) ```typescript // src/config/database.ts export const databaseConfig = { type: 'mysql' as const, host: process.env.DB_HOST ?? 'localhost', port: parseInt(process.env.DB_PORT ?? '3306', 10), username: process.env.DB_USER ?? 'calendar_app', password: process.env.DB_PASSWORD, database: process.env.DB_NAME ?? 'calendar_booking', charset: 'utf8mb4', timezone: 'Z', // Store/read as UTC synchronize: false, // Always use migrations in production logging: process.env.NODE_ENV === 'development', }; ``` ### Environment Variables | Variable | Example | Description | |----------|---------|-------------| | `DB_HOST` | `localhost` | MySQL host | | `DB_PORT` | `3306` | MySQL port | | `DB_USER` | `calendar_app` | Application user | | `DB_PASSWORD` | `***` | Password | | `DB_NAME` | `calendar_booking` | Database name | | `DB_POOL_SIZE` | `10` | Connection pool max | ### Docker Compose (local dev) ```yaml services: mysql: image: mysql:8.0 environment: MYSQL_ROOT_PASSWORD: root MYSQL_DATABASE: calendar_booking MYSQL_USER: calendar_app MYSQL_PASSWORD: dev_password ports: - "3306:3306" volumes: - mysql_data:/var/lib/mysql command: --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci volumes: mysql_data: ``` --- ## 3. Naming Conventions | Element | Convention | Example | |---------|------------|---------| | Tables | snake_case, plural | `event_types` | | Columns | snake_case | `created_at` | | Primary key | `id` CHAR(36) | UUID | | Foreign key | `{entity}_id` | `user_id` | | Indexes | `idx_{table}_{columns}` | `idx_bookings_user_time` | | Unique | `uk_{table}_{columns}` | `uk_users_username` | --- ## 4. Entity Relationship Diagram ```mermaid erDiagram users ||--o{ calendar_connections : has users ||--|| availability_schedules : has users ||--o{ event_types : owns users ||--o{ bookings : receives users ||--o{ embed_configs : configures users ||--o{ api_keys : owns 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 { char id PK varchar email UK varchar password_hash varchar full_name varchar username UK varchar timezone tinyint email_verified datetime created_at } embed_configs { char id PK char user_id FK char event_type_id FK varchar embed_key UK json theme json allowed_origins tinyint is_active } api_keys { char id PK char user_id FK varchar key_hash UK varchar name json scopes datetime expires_at } ``` --- ## 5. Table Definitions ### 5.1 `users` Hosts who register and manage booking pages. ```sql CREATE TABLE users ( id CHAR(36) NOT NULL PRIMARY KEY, email VARCHAR(255) NOT NULL, password_hash VARCHAR(255) NOT NULL, full_name VARCHAR(255) NOT NULL, username VARCHAR(64) NOT NULL, timezone VARCHAR(64) NOT NULL DEFAULT 'UTC', avatar_url VARCHAR(512) NULL, email_verified TINYINT(1) NOT NULL DEFAULT 0, created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3), CONSTRAINT uk_users_email UNIQUE (email), CONSTRAINT uk_users_username UNIQUE (username), CONSTRAINT chk_users_username_format CHECK (username REGEXP '^[a-z0-9-]+$') ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE INDEX idx_users_email ON users (email); ``` --- ### 5.2 `availability_schedules` One row per host — weekly rules and booking window settings. ```sql CREATE TABLE availability_schedules ( id CHAR(36) NOT NULL PRIMARY KEY, user_id CHAR(36) NOT NULL, min_notice_hours INT NOT NULL DEFAULT 4, max_advance_days INT NOT NULL DEFAULT 60, weekly_rules JSON NOT NULL, updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3), CONSTRAINT uk_availability_user UNIQUE (user_id), CONSTRAINT fk_availability_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; ``` **`weekly_rules` JSON example:** ```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" } ] ``` --- ### 5.3 `availability_date_overrides` Holidays and special hours. ```sql CREATE TABLE availability_date_overrides ( id CHAR(36) NOT NULL PRIMARY KEY, schedule_id CHAR(36) NOT NULL, override_date DATE NOT NULL, available TINYINT(1) NOT NULL DEFAULT 0, intervals JSON NULL, CONSTRAINT uk_override_schedule_date UNIQUE (schedule_id, override_date), CONSTRAINT fk_override_schedule FOREIGN KEY (schedule_id) REFERENCES availability_schedules (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE INDEX idx_overrides_date ON availability_date_overrides (schedule_id, override_date); ``` --- ### 5.4 `event_types` Bookable meeting templates. ```sql CREATE TABLE event_types ( id CHAR(36) NOT NULL PRIMARY KEY, user_id CHAR(36) NOT NULL, slug VARCHAR(64) NOT NULL, title VARCHAR(255) NOT NULL, duration_minutes INT NOT NULL, buffer_before_minutes INT NOT NULL DEFAULT 0, buffer_after_minutes INT NOT NULL DEFAULT 0, location VARCHAR(512) NULL, description TEXT NULL, custom_questions JSON NOT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, color VARCHAR(7) NULL DEFAULT '#4F46E5', created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3), CONSTRAINT uk_event_type_user_slug UNIQUE (user_id, slug), CONSTRAINT fk_event_type_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE, CONSTRAINT chk_event_duration CHECK (duration_minutes > 0) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE INDEX idx_event_types_user_active ON event_types (user_id, is_active); ``` --- ### 5.5 `calendar_connections` OAuth / CalDAV credentials for Google, Microsoft, and Apple. ```sql CREATE TABLE calendar_connections ( id CHAR(36) NOT NULL PRIMARY KEY, user_id CHAR(36) NOT NULL, provider ENUM('google', 'microsoft', 'apple') NOT NULL, provider_account_email VARCHAR(255) NOT NULL, access_token_enc TEXT NULL, refresh_token_enc TEXT NULL, token_expires_at DATETIME(3) NULL, calendar_config JSON NOT NULL, status ENUM('connected', 'expired', 'error', 'disconnected') NOT NULL DEFAULT 'connected', last_synced_at DATETIME(3) NULL, created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3), CONSTRAINT fk_calendar_conn_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE INDEX idx_calendar_conn_user ON calendar_connections (user_id); CREATE INDEX idx_calendar_conn_status ON calendar_connections (status); ``` **`calendar_config` JSON example:** ```json [ { "externalId": "primary", "name": "Alice Smith", "checkConflicts": true, "writeTarget": true } ] ``` --- ### 5.6 `bookings` Appointments created by guests or hosts. ```sql CREATE TABLE bookings ( id CHAR(36) NOT NULL PRIMARY KEY, user_id CHAR(36) NOT NULL, event_type_id CHAR(36) NOT NULL, guest_name VARCHAR(255) NOT NULL, guest_email VARCHAR(255) NOT NULL, guest_timezone VARCHAR(64) NOT NULL, start_time DATETIME(3) NOT NULL, end_time DATETIME(3) NOT NULL, custom_answers JSON NOT NULL, status ENUM('pending', 'confirmed', 'cancelled', 'failed') NOT NULL DEFAULT 'pending', cancel_token_hash VARCHAR(255) NOT NULL, cancelled_at DATETIME(3) NULL, cancellation_reason TEXT NULL, idempotency_key VARCHAR(64) NULL, embed_source VARCHAR(255) NULL, created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3), CONSTRAINT uk_bookings_idempotency UNIQUE (idempotency_key), CONSTRAINT fk_booking_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE, CONSTRAINT fk_booking_event_type FOREIGN KEY (event_type_id) REFERENCES event_types (id) ON DELETE RESTRICT, CONSTRAINT chk_booking_times CHECK (end_time > start_time) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE INDEX idx_bookings_user_time ON bookings (user_id, start_time); CREATE INDEX idx_bookings_overlap ON bookings (user_id, start_time, end_time, status); CREATE INDEX idx_bookings_guest_email ON bookings (guest_email); CREATE INDEX idx_bookings_status ON bookings (status); ``` > `embed_source` stores the referring origin or embed key when a booking comes from an embedded widget. --- ### 5.7 `calendar_event_syncs` Maps bookings to external calendar event IDs. ```sql CREATE TABLE calendar_event_syncs ( id CHAR(36) NOT NULL PRIMARY KEY, booking_id CHAR(36) NOT NULL, connection_id CHAR(36) NOT NULL, external_calendar_id VARCHAR(255) NOT NULL, external_event_id VARCHAR(512) NOT NULL, sync_status ENUM('pending', 'synced', 'failed', 'deleted') NOT NULL DEFAULT 'pending', last_error TEXT NULL, synced_at DATETIME(3) NULL, created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), CONSTRAINT uk_sync_booking_connection UNIQUE (booking_id, connection_id), CONSTRAINT fk_sync_booking FOREIGN KEY (booking_id) REFERENCES bookings (id) ON DELETE CASCADE, CONSTRAINT fk_sync_connection FOREIGN KEY (connection_id) REFERENCES calendar_connections (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE INDEX idx_sync_pending ON calendar_event_syncs (sync_status); ``` --- ### 5.8 `busy_blocks` (optional cache) Persisted busy intervals when not using Redis-only cache. ```sql CREATE TABLE busy_blocks ( id CHAR(36) NOT NULL PRIMARY KEY, user_id CHAR(36) NOT NULL, start_time DATETIME(3) NOT NULL, end_time DATETIME(3) NOT NULL, source ENUM('google', 'microsoft', 'apple', 'booking') NOT NULL, source_ref VARCHAR(512) NULL, fetched_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), CONSTRAINT fk_busy_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE, CONSTRAINT chk_busy_times CHECK (end_time > start_time) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE INDEX idx_busy_user_range ON busy_blocks (user_id, start_time, end_time); ``` --- ### 5.9 `audit_logs` Security and compliance audit trail. ```sql CREATE TABLE audit_logs ( id CHAR(36) NOT NULL PRIMARY KEY, user_id CHAR(36) NULL, action VARCHAR(64) NOT NULL, entity_type VARCHAR(64) NOT NULL, entity_id CHAR(36) NULL, metadata JSON NULL, ip_address VARCHAR(45) NULL, created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE INDEX idx_audit_user ON audit_logs (user_id, created_at); CREATE INDEX idx_audit_entity ON audit_logs (entity_type, entity_id); ``` --- ## 6. Embed and API Key Tables These tables support **embedding the booking widget** into third-party websites and server-to-server integrations. ### 6.1 `embed_configs` Per-event-type embed settings for the JavaScript widget. ```sql CREATE TABLE embed_configs ( id CHAR(36) NOT NULL PRIMARY KEY, user_id CHAR(36) NOT NULL, event_type_id CHAR(36) NOT NULL, embed_key VARCHAR(64) NOT NULL, display_mode ENUM('inline', 'popup', 'modal') NOT NULL DEFAULT 'inline', theme JSON NOT NULL, allowed_origins JSON NOT NULL, hide_branding TINYINT(1) NOT NULL DEFAULT 0, is_active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3), CONSTRAINT uk_embed_key UNIQUE (embed_key), CONSTRAINT fk_embed_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE, CONSTRAINT fk_embed_event_type FOREIGN KEY (event_type_id) REFERENCES event_types (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE INDEX idx_embed_user ON embed_configs (user_id); ``` **`theme` JSON example:** ```json { "primaryColor": "#4F46E5", "backgroundColor": "#FFFFFF", "fontFamily": "Inter, sans-serif", "borderRadius": "8px" } ``` **`allowed_origins` JSON example:** ```json ["https://partner-site.com", "https://www.partner-site.com", "http://localhost:4200"] ``` --- ### 6.2 `api_keys` For third-party apps embedding or calling the API server-side. ```sql CREATE TABLE api_keys ( id CHAR(36) NOT NULL PRIMARY KEY, user_id CHAR(36) NOT NULL, name VARCHAR(128) NOT NULL, key_prefix VARCHAR(12) NOT NULL, key_hash VARCHAR(255) NOT NULL, scopes JSON NOT NULL, expires_at DATETIME(3) NULL, last_used_at DATETIME(3) NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), CONSTRAINT uk_api_key_hash UNIQUE (key_hash), CONSTRAINT fk_api_key_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE INDEX idx_api_keys_user ON api_keys (user_id); CREATE INDEX idx_api_keys_prefix ON api_keys (key_prefix); ``` **`scopes` JSON example:** ```json ["embed:read", "bookings:read", "public:book"] ``` > Store only `key_hash` (SHA-256). Display `key_prefix` (first 8 chars) in the dashboard for identification. --- ### 6.3 `timezones` Reference list of supported IANA timezones for dropdowns (registration, settings, public booking). ```sql CREATE TABLE timezones ( iana_id VARCHAR(64) NOT NULL PRIMARY KEY, label VARCHAR(128) NOT NULL, region VARCHAR(64) NOT NULL, sort_order INT NOT NULL DEFAULT 0, is_active TINYINT(1) NOT NULL DEFAULT 1 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE INDEX idx_timezones_region ON timezones (region, sort_order); ``` Seed data is applied automatically on API startup, or manually: ```bash cd node && npm run seed:timezones ``` API: `GET /v1/timezones` returns `{ data, groups }` for grouped dropdown options. --- ## 7. Indexes and Performance ### Critical Query Patterns | Query | Index | |-------|-------| | Host lookup by username | `uk_users_username` | | Active event types | `idx_event_types_user_active` | | Bookings in date range | `idx_bookings_user_time` | | Overlap detection | `idx_bookings_overlap` | | Pending calendar sync | `idx_sync_pending` | | Embed config by key | `uk_embed_key` | ### Overlap Detection Query ```sql SELECT COUNT(*) AS conflict_count FROM bookings WHERE user_id = ? AND status IN ('pending', 'confirmed') AND start_time < ? -- proposed end (with buffer) AND end_time > ?; -- proposed start (with buffer) ``` Run inside a transaction with `SELECT ... FOR UPDATE` on the host's booking rows for the date range to prevent race conditions. ### JSON Column Tips (MySQL 8) ```sql -- Validate weekly rules contain dayOfWeek SELECT id FROM availability_schedules WHERE JSON_CONTAINS(weekly_rules, '{"dayOfWeek": 1}', '$'); -- Extract custom answer SELECT guest_name, JSON_UNQUOTE(JSON_EXTRACT(custom_answers, '$.q1')) AS answer FROM bookings WHERE user_id = ?; ``` ### Connection Pooling | Environment | Pool size | |-------------|-----------| | Development | 5 | | Production (single instance) | 10–20 | | Production (scaled) | 10 per Node instance | Use **Redis** for hot busy-block cache; MySQL `busy_blocks` is optional fallback. --- ## 8. Migrations ### Migration Order 1. `001_create_users` 2. `002_create_availability_schedules` 3. `003_create_availability_date_overrides` 4. `004_create_event_types` 5. `005_create_calendar_connections` 6. `006_create_bookings` 7. `007_create_calendar_event_syncs` 8. `008_create_busy_blocks` 9. `009_create_audit_logs` 10. `010_create_embed_configs` 11. `011_create_api_keys` ### TypeORM Migration Example ```typescript // src/migrations/001CreateUsers.ts import { MigrationInterface, QueryRunner } from 'typeorm'; export class CreateUsers001 implements MigrationInterface { public async up(queryRunner: QueryRunner): Promise { await queryRunner.query(` CREATE TABLE users ( id CHAR(36) NOT NULL PRIMARY KEY, email VARCHAR(255) NOT NULL, ... ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci `); } public async down(queryRunner: QueryRunner): Promise { await queryRunner.query('DROP TABLE users'); } } ``` ### Commands ```bash npm run migration:generate -- src/migrations/AddEmbedConfigs npm run migration:run npm run migration:revert ``` --- ## 9. Sample Queries ### Public booking page — host + event type ```sql SELECT u.id AS user_id, u.full_name, u.username, u.timezone, u.avatar_url, et.id AS event_type_id, et.slug, et.title, et.duration_minutes, et.location, et.description, et.custom_questions FROM users u INNER JOIN event_types et ON et.user_id = u.id WHERE u.username = ? AND et.slug = ? AND et.is_active = 1; ``` ### Upcoming confirmed bookings ```sql SELECT b.*, et.title AS event_title FROM bookings b INNER JOIN event_types et ON et.id = b.event_type_id WHERE b.user_id = ? AND b.status = 'confirmed' AND b.start_time >= UTC_TIMESTAMP(3) ORDER BY b.start_time ASC LIMIT 50; ``` ### Embed config validation ```sql SELECT ec.*, et.slug, u.username FROM embed_configs ec INNER JOIN event_types et ON et.id = ec.event_type_id INNER JOIN users u ON u.id = ec.user_id WHERE ec.embed_key = ? AND ec.is_active = 1 AND et.is_active = 1; ``` Origin validation is performed in the Node.js layer against `allowed_origins` JSON. --- ## 10. TypeORM Entity Examples ```typescript // src/models/entities/User.entity.ts import { Entity, PrimaryColumn, Column, CreateDateColumn, UpdateDateColumn, OneToMany, OneToOne, } from 'typeorm'; import { EventType } from './EventType.entity'; import { AvailabilitySchedule } from './AvailabilitySchedule.entity'; @Entity('users') export class User { @PrimaryColumn('char', { length: 36 }) id!: string; @Column({ length: 255, unique: true }) email!: string; @Column({ name: 'password_hash', length: 255 }) passwordHash!: string; @Column({ name: 'full_name', length: 255 }) fullName!: string; @Column({ length: 64, unique: true }) username!: string; @Column({ length: 64, default: 'UTC' }) timezone!: string; @Column({ name: 'email_verified', type: 'tinyint', width: 1, default: 0 }) emailVerified!: boolean; @CreateDateColumn({ name: 'created_at', type: 'datetime', precision: 3 }) createdAt!: Date; @UpdateDateColumn({ name: 'updated_at', type: 'datetime', precision: 3 }) updatedAt!: Date; @OneToMany(() => EventType, (et) => et.user) eventTypes!: EventType[]; @OneToOne(() => AvailabilitySchedule, (s) => s.user) availabilitySchedule!: AvailabilitySchedule; } ``` ```typescript // src/models/entities/Booking.entity.ts @Entity('bookings') export class Booking { @PrimaryColumn('char', { length: 36 }) id!: string; @Column({ name: 'user_id', type: 'char', length: 36 }) userId!: string; @Column({ name: 'start_time', type: 'datetime', precision: 3 }) startTime!: Date; @Column({ name: 'end_time', type: 'datetime', precision: 3 }) endTime!: Date; @Column({ type: 'enum', enum: ['pending', 'confirmed', 'cancelled', 'failed'] }) status!: string; @Column({ name: 'embed_source', length: 255, nullable: true }) embedSource?: string; } ``` --- ## 11. Backup and Operations ### Daily Backup ```bash mysqldump -u calendar_app -p \ --single-transaction \ --routines \ --triggers \ calendar_booking > backup_$(date +%Y%m%d).sql ``` ### Retention | Data | Retention | |------|-----------| | Cancelled bookings | 12 months | | Audit logs | 24 months | | Busy blocks cache | Rolling 90 days | | Revoked API keys | Delete row immediately | ### Monitoring - Slow query log: queries > 1s - Alert on connection pool exhaustion - Replication lag < 5s (if using read replicas) --- ## Related Documents - [Node.js Developer Guide](./node.md) - [Angular Developer Guide](./angular.md) - [API Reference](./API_REFERENCE.md) - [Calendar Integrations](./CALENDAR_INTEGRATIONS.md)