-- ============================================================================ -- Health Therapies Clinics — Website Chatbot -- MySQL 8.x / MariaDB 10.4+ schema -- Two audiences: patients (treatment Q&A) + practitioners (room rental). -- Every column nullable so partial saves never fail. -- ============================================================================ -- Shared database — also hosts the intake form (intake_*) and the Rainbow -- Medicine bot (chatbot_*). Our tables use the `htc_` prefix. CREATE DATABASE IF NOT EXISTS clinics_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE clinics_db; -- ---------------------------------------------------------------------------- -- Conversations / sessions — one row per browser visitor. -- `intent` lets us route the conversation: patient | practitioner | unknown -- ---------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS htc_sessions ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, token CHAR(36) NULL, intent VARCHAR(20) NULL DEFAULT 'unknown', -- patient | practitioner | unknown page_url VARCHAR(500) NULL, referrer VARCHAR(500) NULL, ip_address VARCHAR(64) NULL, user_agent VARCHAR(255) NULL, language VARCHAR(10) NULL, -- Lead capture (patients) lead_name VARCHAR(160) NULL, lead_email VARCHAR(160) NULL, lead_phone VARCHAR(40) NULL, lead_topic VARCHAR(120) NULL, lead_captured_at DATETIME NULL, message_count INT UNSIGNED NULL DEFAULT 0, total_tokens INT UNSIGNED NULL DEFAULT 0, status VARCHAR(20) NULL DEFAULT 'open', started_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP, last_message_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_token (token), KEY idx_intent (intent), KEY idx_lead_email (lead_email) ) ENGINE=InnoDB; -- ---------------------------------------------------------------------------- -- Messages — every turn for transcript / audit. -- ---------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS htc_messages ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, session_id BIGINT UNSIGNED NULL, role VARCHAR(20) NULL, content MEDIUMTEXT NULL, tokens_in INT UNSIGNED NULL, tokens_out INT UNSIGNED NULL, model VARCHAR(60) NULL, latency_ms INT UNSIGNED NULL, retrieved_chunk_ids VARCHAR(255) NULL, created_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_session (session_id, created_at), CONSTRAINT fk_htcmsg_session FOREIGN KEY (session_id) REFERENCES htc_sessions(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- ---------------------------------------------------------------------------- -- Knowledge base — source documents (site pages or curated PDFs) -- ---------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS htc_kb_documents ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, source_type VARCHAR(40) NULL, -- 'site' | 'pdf' | 'docx' | 'manual' source_url VARCHAR(500) NULL, title VARCHAR(255) NULL, language VARCHAR(10) NULL DEFAULT 'en', audience VARCHAR(20) NULL DEFAULT 'all', -- patient | practitioner | all content_hash CHAR(64) NULL, raw_content MEDIUMTEXT NULL, word_count INT UNSIGNED NULL, is_active TINYINT(1) NULL DEFAULT 1, ingested_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_hash (content_hash), KEY idx_active (is_active), KEY idx_audience (audience) ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS htc_kb_chunks ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, document_id BIGINT UNSIGNED NULL, chunk_index INT UNSIGNED NULL, chunk_text TEXT NULL, word_count INT UNSIGNED NULL, PRIMARY KEY (id), KEY idx_doc (document_id, chunk_index), FULLTEXT KEY ft_chunk (chunk_text), CONSTRAINT fk_htcchunk_doc FOREIGN KEY (document_id) REFERENCES htc_kb_documents(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- ---------------------------------------------------------------------------- -- Curated FAQ that wins over generated answers. -- ---------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS htc_faq ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, question VARCHAR(500) NULL, answer TEXT NULL, keywords VARCHAR(500) NULL, audience VARCHAR(20) NULL DEFAULT 'all', language VARCHAR(10) NULL DEFAULT 'en', is_active TINYINT(1) NULL DEFAULT 1, created_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), FULLTEXT KEY ft_faq (question, keywords), KEY idx_audience (audience) ) ENGINE=InnoDB; -- ---------------------------------------------------------------------------- -- Patient lead outbox (mirror of rainbow chatbot) -- ---------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS htc_lead_outbox ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, session_id BIGINT UNSIGNED NULL, payload TEXT NULL, sent_at DATETIME NULL, created_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_unsent (sent_at) ) ENGINE=InnoDB; -- ---------------------------------------------------------------------------- -- ROOM RENTAL APPLICATIONS — practitioner side of the bot. -- All fields nullable so partial saves work; status drives the workflow. -- ---------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS htc_rental_applications ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, session_id BIGINT UNSIGNED NULL, -- Identity practitioner_name VARCHAR(160) NULL, email VARCHAR(160) NULL, phone VARCHAR(40) NULL, business_name VARCHAR(160) NULL, abn VARCHAR(40) NULL, -- Practice profile primary_modality VARCHAR(120) NULL, -- e.g. "Naturopathy" years_experience INT UNSIGNED NULL, qualifications TEXT NULL, -- free text registration_body VARCHAR(160) NULL, -- AHPRA, ATMS, ANTA, etc. registration_number VARCHAR(80) NULL, insurance_provider VARCHAR(160) NULL, insurance_expiry DATE NULL, hicaps_provider_no VARCHAR(40) NULL, has_hicaps TINYINT(1) NULL, -- 0/1 -- Rental needs preferred_location VARCHAR(80) NULL, -- 'Lane Cove' | 'Freshwater' | 'Either' rental_type VARCHAR(40) NULL, -- 'casual' | 'monthly' | 'either' preferred_days VARCHAR(160) NULL, -- e.g. "Mon AM, Wed PM" estimated_hours_week INT UNSIGNED NULL, start_date DATE NULL, sells_retail_products TINYINT(1) NULL, bring_own_equipment TINYINT(1) NULL, needs_storage TINYINT(1) NULL, references_available TINYINT(1) NULL, tour_requested TINYINT(1) NULL, preferred_tour_time VARCHAR(120) NULL, -- "Tue afternoon, Thu morning" -- Marketing how_heard VARCHAR(120) NULL, notes TEXT NULL, -- Workflow status VARCHAR(30) NULL DEFAULT 'submitted', -- submitted | reviewing | tour_booked | offered | declined | active internal_notes TEXT NULL, created_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, reviewed_at DATETIME NULL, PRIMARY KEY (id), KEY idx_session (session_id), KEY idx_status (status), KEY idx_email (email), CONSTRAINT fk_app_session FOREIGN KEY (session_id) REFERENCES htc_sessions(id) ON DELETE SET NULL ) ENGINE=InnoDB; -- ---------------------------------------------------------------------------- -- Multi-select for additional modalities the practitioner offers. -- ---------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS htc_rental_app_modalities ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, application_id BIGINT UNSIGNED NULL, modality VARCHAR(80) NULL, -- Bowen | Chiropractic | Diet | Kinesiology | Naturopathy -- Massage | Nutrition | Reiki | Psychology | Counselling | Other modality_other VARCHAR(120) NULL, created_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_app (application_id), CONSTRAINT fk_appmod_app FOREIGN KEY (application_id) REFERENCES htc_rental_applications(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- ---------------------------------------------------------------------------- -- Rental application notification queue (cron picks these up to email) -- ---------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS htc_rental_app_outbox ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, application_id BIGINT UNSIGNED NULL, payload TEXT NULL, sent_at DATETIME NULL, created_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_unsent (sent_at) ) ENGINE=InnoDB; -- ---------------------------------------------------------------------------- -- Helpful views -- ---------------------------------------------------------------------------- CREATE OR REPLACE VIEW v_htc_recent_sessions AS SELECT s.id, s.token, s.intent, s.lead_name, s.lead_email, s.lead_topic, s.message_count, s.total_tokens, s.status, s.started_at, s.last_message_at, s.page_url FROM htc_sessions s ORDER BY s.started_at DESC; CREATE OR REPLACE VIEW v_htc_open_applications AS SELECT a.id, a.practitioner_name, a.email, a.phone, a.primary_modality, a.preferred_location, a.rental_type, a.tour_requested, a.status, a.created_at, (SELECT GROUP_CONCAT(m.modality SEPARATOR ', ') FROM htc_rental_app_modalities m WHERE m.application_id = a.id) AS modalities FROM htc_rental_applications a WHERE a.status NOT IN ('declined','active') ORDER BY a.created_at DESC; CREATE OR REPLACE VIEW v_htc_kb_status AS SELECT d.id, d.title, d.source_url, d.audience, d.source_type, d.word_count, d.is_active, d.ingested_at, COUNT(c.id) AS chunk_count FROM htc_kb_documents d LEFT JOIN htc_kb_chunks c ON c.document_id = d.id GROUP BY d.id, d.title, d.source_url, d.audience, d.source_type, d.word_count, d.is_active, d.ingested_at;