-- ===================================================================== -- AI Front Desk Platform — Initial Schema -- Separate database from the existing Telnyx/OpenSIP calling-card -- platform. Runs on the same MySQL server. No cross-DB foreign keys — -- any linkage to the calling-card platform (e.g. shared DID inventory) -- happens at the application layer via API/service calls, not JOINs, -- so the two systems stay decoupled and independently deployable. -- ===================================================================== SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- --------------------------------------------------------------------- -- tenants -- --------------------------------------------------------------------- CREATE TABLE tenants ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, uuid CHAR(36) NOT NULL, business_name VARCHAR(191) NOT NULL, industry VARCHAR(64) NULL, -- hvac, plumbing, dental, legal, etc. timezone VARCHAR(64) NOT NULL DEFAULT 'America/New_York', status ENUM('trial','active','suspended','cancelled') NOT NULL DEFAULT 'trial', plan VARCHAR(64) NOT NULL DEFAULT 'starter', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_tenants_uuid (uuid), KEY idx_tenants_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- users (admin portal logins — tenant staff + platform superadmins) -- --------------------------------------------------------------------- CREATE TABLE users ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tenant_id BIGINT UNSIGNED NULL, -- NULL = platform superadmin name VARCHAR(191) NOT NULL, email VARCHAR(191) NOT NULL, password_hash VARCHAR(255) NOT NULL, role ENUM('superadmin','owner','manager','agent') NOT NULL DEFAULT 'owner', is_active TINYINT(1) NOT NULL DEFAULT 1, last_login_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_users_email (email), KEY idx_users_tenant (tenant_id), CONSTRAINT fk_users_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- phone_numbers (DIDs assigned to a tenant, provisioned via Telnyx) -- --------------------------------------------------------------------- CREATE TABLE phone_numbers ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tenant_id BIGINT UNSIGNED NOT NULL, e164_number VARCHAR(20) NOT NULL, -- +14045551234 provider VARCHAR(32) NOT NULL DEFAULT 'telnyx', provider_number_id VARCHAR(128) NULL, -- Telnyx phone number resource id connection_id VARCHAR(128) NULL, -- Telnyx Call Control connection id label VARCHAR(191) NULL, status ENUM('active','porting','disabled') NOT NULL DEFAULT 'active', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_phone_numbers_e164 (e164_number), KEY idx_phone_numbers_tenant (tenant_id), CONSTRAINT fk_phone_numbers_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- calls -- --------------------------------------------------------------------- CREATE TABLE calls ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tenant_id BIGINT UNSIGNED NOT NULL, phone_number_id BIGINT UNSIGNED NOT NULL, provider VARCHAR(32) NOT NULL DEFAULT 'telnyx', provider_call_id VARCHAR(191) NOT NULL, -- Telnyx call_control_id direction ENUM('inbound','outbound') NOT NULL DEFAULT 'inbound', from_number VARCHAR(20) NOT NULL, to_number VARCHAR(20) NOT NULL, status ENUM('ringing','in_progress','completed','failed','no_answer','transferred') NOT NULL DEFAULT 'ringing', intent VARCHAR(64) NULL, -- appointment, pricing, emergency, lead, etc. started_at DATETIME NULL, answered_at DATETIME NULL, ended_at DATETIME NULL, duration_seconds INT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_calls_provider_call_id (provider_call_id), KEY idx_calls_tenant (tenant_id), KEY idx_calls_phone_number (phone_number_id), KEY idx_calls_started_at (started_at), CONSTRAINT fk_calls_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE, CONSTRAINT fk_calls_phone_number FOREIGN KEY (phone_number_id) REFERENCES phone_numbers(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- call_recordings -- --------------------------------------------------------------------- CREATE TABLE call_recordings ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tenant_id BIGINT UNSIGNED NOT NULL, call_id BIGINT UNSIGNED NOT NULL, provider_recording_id VARCHAR(191) NULL, storage_url VARCHAR(512) NULL, duration_seconds INT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_recordings_tenant (tenant_id), KEY idx_recordings_call (call_id), CONSTRAINT fk_recordings_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE, CONSTRAINT fk_recordings_call FOREIGN KEY (call_id) REFERENCES calls(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- call_transcripts (turn-by-turn conversation log) -- --------------------------------------------------------------------- CREATE TABLE call_transcripts ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tenant_id BIGINT UNSIGNED NOT NULL, call_id BIGINT UNSIGNED NOT NULL, turn_index INT UNSIGNED NOT NULL, speaker ENUM('caller','ai','system') NOT NULL, message TEXT NOT NULL, tool_call JSON NULL, -- tool invoked on this turn, if any created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_transcripts_tenant (tenant_id), KEY idx_transcripts_call (call_id), CONSTRAINT fk_transcripts_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE, CONSTRAINT fk_transcripts_call FOREIGN KEY (call_id) REFERENCES calls(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- call_summaries -- --------------------------------------------------------------------- CREATE TABLE call_summaries ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tenant_id BIGINT UNSIGNED NOT NULL, call_id BIGINT UNSIGNED NOT NULL, summary TEXT NOT NULL, sentiment VARCHAR(32) NULL, action_items JSON NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_summaries_call (call_id), KEY idx_summaries_tenant (tenant_id), CONSTRAINT fk_summaries_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE, CONSTRAINT fk_summaries_call FOREIGN KEY (call_id) REFERENCES calls(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- contacts -- --------------------------------------------------------------------- CREATE TABLE contacts ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tenant_id BIGINT UNSIGNED NOT NULL, name VARCHAR(191) NULL, phone VARCHAR(20) NOT NULL, email VARCHAR(191) NULL, notes TEXT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_contacts_tenant_phone (tenant_id, phone), KEY idx_contacts_tenant (tenant_id), CONSTRAINT fk_contacts_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- leads -- --------------------------------------------------------------------- CREATE TABLE leads ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tenant_id BIGINT UNSIGNED NOT NULL, contact_id BIGINT UNSIGNED NOT NULL, call_id BIGINT UNSIGNED NULL, source VARCHAR(64) NOT NULL DEFAULT 'inbound_call', status ENUM('new','contacted','qualified','won','lost') NOT NULL DEFAULT 'new', interest VARCHAR(191) NULL, notes TEXT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_leads_tenant (tenant_id), KEY idx_leads_contact (contact_id), KEY idx_leads_call (call_id), CONSTRAINT fk_leads_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE, CONSTRAINT fk_leads_contact FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE, CONSTRAINT fk_leads_call FOREIGN KEY (call_id) REFERENCES calls(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- appointments (Phase 2) -- --------------------------------------------------------------------- CREATE TABLE appointments ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tenant_id BIGINT UNSIGNED NOT NULL, contact_id BIGINT UNSIGNED NOT NULL, call_id BIGINT UNSIGNED NULL, scheduled_at DATETIME NOT NULL, duration_minutes INT UNSIGNED NOT NULL DEFAULT 60, status ENUM('scheduled','confirmed','cancelled','completed','no_show') NOT NULL DEFAULT 'scheduled', notes TEXT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_appointments_tenant (tenant_id), KEY idx_appointments_contact (contact_id), KEY idx_appointments_scheduled_at (scheduled_at), CONSTRAINT fk_appointments_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE, CONSTRAINT fk_appointments_contact FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE, CONSTRAINT fk_appointments_call FOREIGN KEY (call_id) REFERENCES calls(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- sms_messages (Phase 2) -- --------------------------------------------------------------------- CREATE TABLE sms_messages ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tenant_id BIGINT UNSIGNED NOT NULL, contact_id BIGINT UNSIGNED NULL, call_id BIGINT UNSIGNED NULL, provider VARCHAR(32) NOT NULL DEFAULT 'telnyx', provider_message_id VARCHAR(191) NULL, direction ENUM('outbound','inbound') NOT NULL, from_number VARCHAR(20) NOT NULL, to_number VARCHAR(20) NOT NULL, body TEXT NOT NULL, status ENUM('queued','sent','delivered','failed','received') NOT NULL DEFAULT 'queued', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_sms_tenant (tenant_id), KEY idx_sms_contact (contact_id), KEY idx_sms_call (call_id), CONSTRAINT fk_sms_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE, CONSTRAINT fk_sms_contact FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE SET NULL, CONSTRAINT fk_sms_call FOREIGN KEY (call_id) REFERENCES calls(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- knowledge_base (Phase 2) -- --------------------------------------------------------------------- CREATE TABLE knowledge_base ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tenant_id BIGINT UNSIGNED NOT NULL, category VARCHAR(64) NULL, -- hours, pricing, coverage_area, policy, etc. question VARCHAR(512) NOT NULL, answer TEXT NOT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_kb_tenant (tenant_id), KEY idx_kb_category (category), FULLTEXT KEY ft_kb_question_answer (question, answer), CONSTRAINT fk_kb_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- business_rules (Phase 2) -- --------------------------------------------------------------------- CREATE TABLE business_rules ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tenant_id BIGINT UNSIGNED NOT NULL, name VARCHAR(191) NOT NULL, trigger_type VARCHAR(64) NOT NULL, -- intent_detected, keyword, office_hours, etc. trigger_value JSON NOT NULL, action_type VARCHAR(64) NOT NULL, -- transfer_call, send_sms, create_ticket, etc. action_value JSON NOT NULL, priority INT UNSIGNED NOT NULL DEFAULT 100, is_active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_rules_tenant (tenant_id), KEY idx_rules_priority (priority), CONSTRAINT fk_rules_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------------- -- audit_logs -- --------------------------------------------------------------------- CREATE TABLE audit_logs ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tenant_id BIGINT UNSIGNED NULL, user_id BIGINT UNSIGNED NULL, action VARCHAR(128) NOT NULL, entity_type VARCHAR(64) NULL, entity_id BIGINT UNSIGNED NULL, metadata JSON NULL, ip_address VARCHAR(45) NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_audit_tenant (tenant_id), KEY idx_audit_user (user_id), KEY idx_audit_created_at (created_at), CONSTRAINT fk_audit_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE SET NULL, CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; SET FOREIGN_KEY_CHECKS = 1;