-- ============================================================================= -- AI SEO Manager — 001_core -- MySQL 8.0+ / MariaDB 10.6+ utf8mb4_0900_ai_ci falls back to utf8mb4_unicode_ci -- Idempotent: every statement is CREATE TABLE IF NOT EXISTS. -- ============================================================================= SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 1; -- ---------------------------------------------------------------- registry --- CREATE TABLE IF NOT EXISTS site ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(120) NOT NULL, primary_domain VARCHAR(255) NOT NULL, division VARCHAR(60) NULL, default_country CHAR(2) NOT NULL DEFAULT 'AE', default_language CHAR(2) NOT NULL DEFAULT 'en', cms VARCHAR(40) NULL, gsc_property VARCHAR(255) NULL, ga4_property_id VARCHAR(40) NULL, crawl_max_urls INT UNSIGNED NOT NULL DEFAULT 5000, crawl_concurrency TINYINT UNSIGNED NOT NULL DEFAULT 3, respect_crawl_delay TINYINT(1) NOT NULL DEFAULT 1, is_active TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_site_domain (primary_domain), KEY idx_site_active (is_active) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- www / non-www / m-dot / ccTLD variants. Needed by T091, T123. CREATE TABLE IF NOT EXISTS site_host ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, site_id INT UNSIGNED NOT NULL, host VARCHAR(255) NOT NULL, scheme ENUM('http','https') NOT NULL DEFAULT 'https', is_canonical_host TINYINT(1) NOT NULL DEFAULT 0, PRIMARY KEY (id), UNIQUE KEY uq_host (site_id, scheme, host), CONSTRAINT fk_host_site FOREIGN KEY (site_id) REFERENCES site (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ------------------------------------------------------------------- runs --- CREATE TABLE IF NOT EXISTS audit_run ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, site_id INT UNSIGNED NOT NULL, run_type ENUM('full','canary','render_sample','ai_seo','offpage','site_analyze') NOT NULL DEFAULT 'full', status ENUM('queued','running','completed','failed','cancelled') NOT NULL DEFAULT 'queued', started_at DATETIME NULL, finished_at DATETIME NULL, -- provenance: without these, every trend chart is a lie the first time a weight is tuned scoring_model_version VARCHAR(20) NOT NULL DEFAULT 'v1', crawler_version VARCHAR(20) NOT NULL DEFAULT 'v1', urls_discovered INT UNSIGNED NOT NULL DEFAULT 0, urls_crawled INT UNSIGNED NOT NULL DEFAULT 0, urls_rendered INT UNSIGNED NOT NULL DEFAULT 0, api_cost_usd DECIMAL(10,4) NOT NULL DEFAULT 0, config_json JSON NULL, error_message TEXT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_run_site_created (site_id, created_at), KEY idx_run_status (status, run_type), CONSTRAINT fk_run_site FOREIGN KEY (site_id) REFERENCES site (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- -------------------------------------------------------- crawl artefacts --- CREATE TABLE IF NOT EXISTS crawled_url ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, run_id BIGINT UNSIGNED NOT NULL, url VARCHAR(2048) NOT NULL, url_hash BINARY(20) NOT NULL, -- sha1(normalised url), the join key status_code SMALLINT NULL, redirect_target VARCHAR(2048) NULL, redirect_hops TINYINT UNSIGNED NOT NULL DEFAULT 0, content_type VARCHAR(120) NULL, bytes INT UNSIGNED NULL, ttfb_ms INT UNSIGNED NULL, depth SMALLINT UNSIGNED NOT NULL DEFAULT 0, indexable TINYINT(1) NULL, robots_allowed TINYINT(1) NULL, canonical_url VARCHAR(2048) NULL, google_canonical VARCHAR(2048) NULL, robots_directives VARCHAR(255) NULL, title VARCHAR(1024) NULL, meta_description VARCHAR(1024) NULL, h1 VARCHAR(1024) NULL, word_count_main MEDIUMINT UNSIGNED NULL, word_count_raw MEDIUMINT UNSIGNED NULL, content_hash BINARY(32) NULL, -- sha256 of normalised main content simhash BIGINT UNSIGNED NULL, -- near-duplicate bucketing (T119) template_fingerprint BINARY(20) NULL, -- drives render sampling rendered TINYINT(1) NOT NULL DEFAULT 0, render_delta_pct DECIMAL(5,2) NULL, -- B01 retrievability gap raw_html_path VARCHAR(500) NULL, crawled_at DATETIME NULL, PRIMARY KEY (id), UNIQUE KEY uq_run_url (run_id, url_hash), KEY idx_url_status (run_id, status_code), KEY idx_url_indexable (run_id, indexable), KEY idx_url_template (run_id, template_fingerprint), KEY idx_url_content_hash (run_id, content_hash), CONSTRAINT fk_url_run FOREIGN KEY (run_id) REFERENCES audit_run (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS url_link ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, run_id BIGINT UNSIGNED NOT NULL, from_url_id BIGINT UNSIGNED NOT NULL, to_url_hash BINARY(20) NOT NULL, -- hash, so an edge can exist before the target is crawled to_url VARCHAR(2048) NOT NULL, anchor_text VARCHAR(512) NULL, rel VARCHAR(120) NULL, is_internal TINYINT(1) NOT NULL DEFAULT 1, is_boilerplate TINYINT(1) NOT NULL DEFAULT 0, -- appears on >70% of pages (T033) PRIMARY KEY (id), KEY idx_link_from (run_id, from_url_id), KEY idx_link_to (run_id, to_url_hash), CONSTRAINT fk_link_run FOREIGN KEY (run_id) REFERENCES audit_run (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS url_metric ( run_id BIGINT UNSIGNED NOT NULL, url_id BIGINT UNSIGNED NOT NULL, metric_key VARCHAR(60) NOT NULL, -- lcp_p75, inp_p75, cls_p75, pagerank, entity_density… value DOUBLE NULL, source VARCHAR(30) NULL, -- crux | psi | lighthouse | computed PRIMARY KEY (run_id, url_id, metric_key), KEY idx_metric_key (run_id, metric_key) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Metric registry so a fourth Core Web Vital is a config row, not a migration. CREATE TABLE IF NOT EXISTS metric_definition ( metric_key VARCHAR(60) NOT NULL, label VARCHAR(80) NOT NULL, unit VARCHAR(20) NULL, threshold_good DOUBLE NULL, threshold_poor DOUBLE NULL, higher_is_better TINYINT(1) NOT NULL DEFAULT 0, is_core TINYINT(1) NOT NULL DEFAULT 0, effective_from DATE NULL, PRIMARY KEY (metric_key) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------------- findings --- -- check_definition is CONFIG, not code: severity, weight, confidence and -- thresholds are tunable without a deploy. CREATE TABLE IF NOT EXISTS check_definition ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, code VARCHAR(12) NOT NULL, -- T001, O031, F014, A17, B01, C06, D02, E01 category VARCHAR(40) NOT NULL, -- scoring category key subcategory VARCHAR(60) NULL, title VARCHAR(200) NOT NULL, description TEXT NULL, remediation TEXT NULL, severity ENUM('critical','high','medium','low','info') NOT NULL DEFAULT 'medium', weight DECIMAL(4,1) NOT NULL DEFAULT 2.0, -- critical 10 · high 5 · medium 2 · low 0.5 confidence DECIMAL(3,2) NOT NULL DEFAULT 1.00, -- 1.0 deterministic · 0.5-0.8 heuristic plane ENUM('crawl','render','api','log') NOT NULL DEFAULT 'crawl', eligible_unit VARCHAR(40) NOT NULL DEFAULT 'indexable_url', evidence_tier ENUM('primary','third_party','weak') NOT NULL DEFAULT 'primary', doc_url VARCHAR(500) NULL, threshold_json JSON NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, PRIMARY KEY (id), UNIQUE KEY uq_check_code (code), KEY idx_check_cat (category, is_active) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- One row per check per affected unit. CREATE TABLE IF NOT EXISTS finding ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, run_id BIGINT UNSIGNED NOT NULL, site_id INT UNSIGNED NOT NULL, check_id SMALLINT UNSIGNED NOT NULL, url_id BIGINT UNSIGNED NULL, -- null = site-level finding entity_ref VARCHAR(255) NULL, -- crawler token, keyword, referring domain… severity_effective ENUM('critical','high','medium','low','info') NOT NULL, detail_json JSON NULL, evidence_json JSON NULL, -- field value + threshold crossed + provider affected_clicks INT UNSIGNED NOT NULL DEFAULT 0, -- GSC 28-day clicks, drives priority priority DECIMAL(8,3) NOT NULL DEFAULT 0, first_seen_run_id BIGINT UNSIGNED NULL, status ENUM('new','persisting','fixed','ignored','wont_fix') NOT NULL DEFAULT 'new', assigned_to VARCHAR(120) NULL, resolution_note TEXT NULL, resolved_at DATETIME NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_finding_run (run_id, severity_effective, priority), KEY idx_finding_site_status (site_id, status), KEY idx_finding_check (run_id, check_id), KEY idx_finding_url (url_id), CONSTRAINT fk_finding_run FOREIGN KEY (run_id) REFERENCES audit_run (id) ON DELETE CASCADE, CONSTRAINT fk_finding_check FOREIGN KEY (check_id) REFERENCES check_definition (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- What the scorer reads. Storing eligible/affected per run makes every -- historical score reproducible and re-scorable. CREATE TABLE IF NOT EXISTS check_result ( run_id BIGINT UNSIGNED NOT NULL, check_id SMALLINT UNSIGNED NOT NULL, eligible_count INT UNSIGNED NOT NULL DEFAULT 0, affected_count INT UNSIGNED NOT NULL DEFAULT 0, prevalence DECIMAL(6,5) NOT NULL DEFAULT 0, penalty DECIMAL(8,4) NOT NULL DEFAULT 0, confidence DECIMAL(3,2) NOT NULL DEFAULT 1.00, PRIMARY KEY (run_id, check_id), CONSTRAINT fk_cr_run FOREIGN KEY (run_id) REFERENCES audit_run (id) ON DELETE CASCADE, CONSTRAINT fk_cr_check FOREIGN KEY (check_id) REFERENCES check_definition (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS score ( run_id BIGINT UNSIGNED NOT NULL, scope ENUM('overall','category','risk','strength','opportunity') NOT NULL, scope_key VARCHAR(40) NOT NULL DEFAULT '', value DECIMAL(5,2) NOT NULL, ceiling_applied VARCHAR(120) NULL, -- which hard gate capped it model_version VARCHAR(20) NOT NULL DEFAULT 'v1', PRIMARY KEY (run_id, scope, scope_key), CONSTRAINT fk_score_run FOREIGN KEY (run_id) REFERENCES audit_run (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------------- AI SEO --- CREATE TABLE IF NOT EXISTS ai_crawler ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, token VARCHAR(60) NOT NULL, -- robots.txt product token vendor VARCHAR(40) NOT NULL, bot_class ENUM('training','retrieval','agent','policy_token') NOT NULL, blocking_effect VARCHAR(255) NULL, ip_ranges_url VARCHAR(255) NULL, doc_url VARCHAR(255) NULL, is_verifiable TINYINT(1) NOT NULL DEFAULT 1, respects_robots ENUM('yes','partial','no','unknown') NOT NULL DEFAULT 'yes', severity_if_blocked ENUM('critical','high','medium','low','info') NOT NULL DEFAULT 'info', notes TEXT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, PRIMARY KEY (id), UNIQUE KEY uq_crawler_token (token) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS ai_access_result ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, run_id BIGINT UNSIGNED NOT NULL, site_id INT UNSIGNED NOT NULL, crawler_id SMALLINT UNSIGNED NOT NULL, path_pattern VARCHAR(255) NOT NULL, layer ENUM('robots','meta','header','waf','cdn_402','content_signal','aipref') NOT NULL, robots_verdict ENUM('allowed','disallowed','not_applicable') NULL, probe_status SMALLINT NULL, probe_body_len INT UNSIGNED NULL, baseline_status SMALLINT NULL, baseline_body_len INT UNSIGNED NULL, verdict ENUM('allowed','blocked','policy','paywall','unknown') NOT NULL DEFAULT 'unknown', detail_json JSON NULL, checked_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_aar_run (run_id, crawler_id), CONSTRAINT fk_aar_run FOREIGN KEY (run_id) REFERENCES audit_run (id) ON DELETE CASCADE, CONSTRAINT fk_aar_crawler FOREIGN KEY (crawler_id) REFERENCES ai_crawler (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Share-of-voice harness. Prompt sets are versioned because comparing runs -- across a changed prompt set is meaningless. CREATE TABLE IF NOT EXISTS ai_prompt_set ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, site_id INT UNSIGNED NOT NULL, version SMALLINT UNSIGNED NOT NULL DEFAULT 1, language CHAR(2) NOT NULL DEFAULT 'en', label VARCHAR(120) NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_prompt_set (site_id, version, language), CONSTRAINT fk_ps_site FOREIGN KEY (site_id) REFERENCES site (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS ai_prompt ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, prompt_set_id INT UNSIGNED NOT NULL, archetype ENUM('generic_best','budget','niche_usecase','head_to_head','enterprise_b2b') NOT NULL, prompt_text TEXT NOT NULL, PRIMARY KEY (id), KEY idx_prompt_set (prompt_set_id), CONSTRAINT fk_prompt_set FOREIGN KEY (prompt_set_id) REFERENCES ai_prompt_set (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS ai_visibility_run ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, site_id INT UNSIGNED NOT NULL, prompt_set_id INT UNSIGNED NOT NULL, platform VARCHAR(40) NOT NULL, -- never aggregate across platforms repetitions TINYINT UNSIGNED NOT NULL DEFAULT 3, run_at DATETIME NOT NULL, cost_usd DECIMAL(8,4) NOT NULL DEFAULT 0, PRIMARY KEY (id), KEY idx_avr_site (site_id, platform, run_at), CONSTRAINT fk_avr_site FOREIGN KEY (site_id) REFERENCES site (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS ai_visibility_obs ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, visibility_run_id BIGINT UNSIGNED NOT NULL, prompt_id INT UNSIGNED NOT NULL, repetition TINYINT UNSIGNED NOT NULL, brand_mentioned TINYINT(1) NOT NULL DEFAULT 0, first_mention_rank SMALLINT NULL, our_urls_cited SMALLINT UNSIGNED NOT NULL DEFAULT 0, cited_urls_json JSON NULL, -- intermediary domains = the target list response_hash BINARY(32) NULL, PRIMARY KEY (id), KEY idx_avo_run (visibility_run_id, prompt_id), CONSTRAINT fk_avo_run FOREIGN KEY (visibility_run_id) REFERENCES ai_visibility_run (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Server-log rollup: the only ground truth for AI crawlers, because they do -- not execute JS and client-side analytics never sees them. CREATE TABLE IF NOT EXISTS bot_log_daily ( site_id INT UNSIGNED NOT NULL, log_date DATE NOT NULL, crawler_id SMALLINT UNSIGNED NULL, ua_class VARCHAR(40) NOT NULL DEFAULT 'other', verified TINYINT(1) NOT NULL DEFAULT 0, -- IP-verified, not UA-trusted requests INT UNSIGNED NOT NULL DEFAULT 0, unique_urls INT UNSIGNED NOT NULL DEFAULT 0, bytes BIGINT UNSIGNED NOT NULL DEFAULT 0, status_2xx INT UNSIGNED NOT NULL DEFAULT 0, status_3xx INT UNSIGNED NOT NULL DEFAULT 0, status_4xx INT UNSIGNED NOT NULL DEFAULT 0, status_402 INT UNSIGNED NOT NULL DEFAULT 0, status_5xx INT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (site_id, log_date, ua_class, verified), KEY idx_bld_crawler (site_id, crawler_id, log_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ----------------------------------------------------------- site analyze --- CREATE TABLE IF NOT EXISTS country_config ( country_code CHAR(2) NOT NULL, label VARCHAR(80) NOT NULL, default_language CHAR(2) NOT NULL DEFAULT 'en', secondary_language CHAR(2) NULL, language_weights_json JSON NULL, -- {"en":0.72,"ar":0.28} location_code INT UNSIGNED NULL, -- DataForSEO location_code device_default ENUM('mobile','desktop') NOT NULL DEFAULT 'mobile', currency CHAR(3) NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, PRIMARY KEY (country_code) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS domain_blocklist ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, domain VARCHAR(255) NOT NULL, class ENUM('marketplace','directory','aggregator_leadgen','publisher_media','oem_principal','gov_assoc') NOT NULL, region VARCHAR(20) NULL, vertical VARCHAR(40) NULL, note VARCHAR(255) NULL, PRIMARY KEY (id), UNIQUE KEY uq_bl_domain (domain) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS competitor ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, site_id INT UNSIGNED NOT NULL, country_code CHAR(2) NOT NULL, language CHAR(2) NOT NULL DEFAULT 'en', domain VARCHAR(255) NOT NULL, class ENUM('direct_competitor','marketplace','directory','aggregator_leadgen','publisher_media','oem_principal','gov_assoc','own') NOT NULL DEFAULT 'direct_competitor', is_partner TINYINT(1) NOT NULL DEFAULT 0, -- our own principal, not a rival competitor_score DECIMAL(6,4) NOT NULL DEFAULT 0, visibility DECIMAL(12,2) NOT NULL DEFAULT 0, intersections INT UNSIGNED NOT NULL DEFAULT 0, avg_position DECIMAL(5,2) NULL, etv DECIMAL(12,2) NULL, classified_by ENUM('blocklist','classifier','manual') NOT NULL DEFAULT 'classifier', classifier_confidence DECIMAL(3,2) NULL, refreshed_at DATETIME NULL, PRIMARY KEY (id), UNIQUE KEY uq_comp (site_id, country_code, language, domain), KEY idx_comp_rank (site_id, country_code, class, competitor_score), CONSTRAINT fk_comp_site FOREIGN KEY (site_id) REFERENCES site (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS keyword ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, country_code CHAR(2) NOT NULL, language CHAR(2) NOT NULL, keyword VARCHAR(700) NOT NULL, keyword_hash BINARY(20) NOT NULL, volume_country INT UNSIGNED NULL, -- NULL means unknown, NOT zero volume_gcc INT UNSIGNED NULL, volume_global_en INT UNSIGNED NULL, volume_confidence ENUM('high','medium','low','unknown') NOT NULL DEFAULT 'unknown', cpc_usd DECIMAL(8,2) NULL, kd_dfs TINYINT UNSIGNED NULL, kd_semrush TINYINT UNSIGNED NULL, achievability DECIMAL(4,3) NULL, intent_primary ENUM('informational','commercial','transactional','navigational') NULL, intent_probability DECIMAL(3,2) NULL, intent_source ENUM('lexicon','model','serp') NULL, serp_features INT UNSIGNED NOT NULL DEFAULT 0, -- bitmask has_aio TINYINT(1) NOT NULL DEFAULT 0, directory_saturation DECIMAL(3,2) NULL, priority DECIMAL(4,3) NULL, last_refreshed_at DATETIME NULL, PRIMARY KEY (id), UNIQUE KEY uq_kw (country_code, language, keyword_hash), KEY idx_kw_priority (country_code, language, priority), KEY idx_kw_intent (country_code, intent_primary) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS keyword_rank ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, keyword_id BIGINT UNSIGNED NOT NULL, site_id INT UNSIGNED NOT NULL, url VARCHAR(2048) NULL, rank_date DATE NOT NULL, position SMALLINT NULL, is_aio_cited TINYINT(1) NOT NULL DEFAULT 0, device ENUM('mobile','desktop') NOT NULL DEFAULT 'mobile', PRIMARY KEY (id), UNIQUE KEY uq_kr (keyword_id, site_id, rank_date, device), KEY idx_kr_site_date (site_id, rank_date), CONSTRAINT fk_kr_kw FOREIGN KEY (keyword_id) REFERENCES keyword (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS keyword_cluster ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, site_id INT UNSIGNED NOT NULL, country_code CHAR(2) NOT NULL, language CHAR(2) NOT NULL, label VARCHAR(255) NOT NULL, page_type ENUM('pillar','cluster_article','landing','comparison','glossary','tool_asset','brand','trust') NULL, cluster_method ENUM('embedding','serp_overlap','hybrid') NOT NULL DEFAULT 'hybrid', head_keyword_id BIGINT UNSIGNED NULL, PRIMARY KEY (id), KEY idx_cluster_site (site_id, country_code, language), CONSTRAINT fk_cluster_site FOREIGN KEY (site_id) REFERENCES site (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS keyword_cluster_member ( cluster_id BIGINT UNSIGNED NOT NULL, keyword_id BIGINT UNSIGNED NOT NULL, is_head TINYINT(1) NOT NULL DEFAULT 0, PRIMARY KEY (cluster_id, keyword_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS content_recommendation ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, site_id INT UNSIGNED NOT NULL, cluster_id BIGINT UNSIGNED NULL, country_code CHAR(2) NOT NULL, language CHAR(2) NOT NULL, action ENUM('new','update','consolidate','children') NOT NULL, target_url VARCHAR(2048) NULL, url_suggestion VARCHAR(500) NULL, page_type VARCHAR(30) NULL, brief_json JSON NULL, priority DECIMAL(4,3) NOT NULL DEFAULT 0, effort_hours DECIMAL(5,1) NULL, expected_clicks_12mo INT UNSIGNED NULL, publish_window VARCHAR(40) NULL, -- GCC cooling seasonality status ENUM('proposed','accepted','in_progress','published','rejected') NOT NULL DEFAULT 'proposed', created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_rec_site (site_id, country_code, priority), CONSTRAINT fk_rec_site FOREIGN KEY (site_id) REFERENCES site (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Hard mapping that prevents two pages targeting the same (keyword, country, -- language). Blocks cross-country cannibalisation by construction. CREATE TABLE IF NOT EXISTS keyword_allocation ( country_code CHAR(2) NOT NULL, language CHAR(2) NOT NULL, keyword_id BIGINT UNSIGNED NOT NULL, site_id INT UNSIGNED NOT NULL, canonical_url VARCHAR(2048) NOT NULL, assigned_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (country_code, language, keyword_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ------------------------------------------------------ infra: jobs, cache --- CREATE TABLE IF NOT EXISTS job ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, queue VARCHAR(40) NOT NULL DEFAULT 'default', type VARCHAR(60) NOT NULL, payload_json JSON NULL, site_id INT UNSIGNED NULL, run_id BIGINT UNSIGNED NULL, status ENUM('pending','reserved','done','failed') NOT NULL DEFAULT 'pending', attempts TINYINT UNSIGNED NOT NULL DEFAULT 0, max_attempts TINYINT UNSIGNED NOT NULL DEFAULT 3, available_at DATETIME NOT NULL, reserved_at DATETIME NULL, reserved_by VARCHAR(64) NULL, last_error TEXT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_job_claim (queue, status, available_at), KEY idx_job_run (run_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Per-host politeness lives in the DB so concurrent workers share it. CREATE TABLE IF NOT EXISTS host_state ( host VARCHAR(255) NOT NULL, next_allowed_at DATETIME(3) NOT NULL, crawl_delay_ms INT UNSIGNED NOT NULL DEFAULT 400, consecutive_5xx TINYINT UNSIGNED NOT NULL DEFAULT 0, paused_until DATETIME NULL, robots_body MEDIUMTEXT NULL, robots_status SMALLINT NULL, robots_hash BINARY(20) NULL, -- T138 drift detection robots_fetched_at DATETIME NULL, PRIMARY KEY (host) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS api_cache ( cache_key BINARY(32) NOT NULL, provider VARCHAR(40) NOT NULL, endpoint VARCHAR(120) NOT NULL, response LONGTEXT NULL, cost_usd DECIMAL(8,5) NOT NULL DEFAULT 0, expires_at DATETIME NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (cache_key), KEY idx_cache_expiry (expires_at), KEY idx_cache_provider (provider, endpoint) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS api_usage ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, provider VARCHAR(40) NOT NULL, endpoint VARCHAR(120) NOT NULL, site_id INT UNSIGNED NULL, run_id BIGINT UNSIGNED NULL, units INT UNSIGNED NOT NULL DEFAULT 1, cost_usd DECIMAL(8,5) NOT NULL DEFAULT 0, cache_hit TINYINT(1) NOT NULL DEFAULT 0, status SMALLINT NULL, duration_ms INT UNSIGNED NULL, called_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_usage_day (provider, called_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS oauth_token ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, provider VARCHAR(40) NOT NULL, -- google_search_console, ga4 account_ref VARCHAR(255) NOT NULL, access_token TEXT NULL, refresh_token TEXT NULL, scope TEXT NULL, expires_at DATETIME NULL, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_oauth (provider, account_ref) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS integration ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, site_id INT UNSIGNED NULL, -- null = account-wide provider VARCHAR(40) NOT NULL, status ENUM('connected','disconnected','error') NOT NULL DEFAULT 'disconnected', config_json JSON NULL, -- never store secrets here; use env/oauth_token last_ok_at DATETIME NULL, last_error TEXT NULL, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_integration (site_id, provider) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS migration ( filename VARCHAR(190) NOT NULL, applied_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (filename) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;