# Database Schema — WebCrawlers

**Database**: webcrawlers_latest  
**Last Updated**: 2026-08-21  
**Created By**: Development Team for Sysadmin Setup

---

## Table of Contents
1. [Existing Tables](#existing-tables)
2. [New Tables (Pixel Tracking System)](#new-tables-pixel-tracking-system)
3. [Modified Tables](#modified-tables)
4. [New Tables (Keyword Rank Tracker)](#new-tables-keyword-rank-tracker)
5. [New Tables (AI Content Analyzer)](#new-tables-ai-content-analyzer)
6. [Raw SQL Statements](#raw-sql-statements)
**Database**: `cms`  
**Last Updated**: 2026-08-21 (`wc_users.role`, `wc_users.invited_by`)  
**Engine**: MySQL 8.0 · InnoDB · utf8mb4_unicode_ci

---

## Table Index

| Table | ~Rows | Purpose |
|---|---|---|
| `wc_users` | 3 | User accounts + auth |
| `wc_websites` | 10 | Registered websites per user |
| `wc_automation_queue` | 2 | SEO automation job queue (NEW) |
| `wc_audits` | 33 | DataForSEO crawl audit runs |
| `wc_audit_pages` | 2,303 | Per-page crawl results |
| `wc_audit_issues` | 310 | Aggregated issues per audit |
| `wc_pixel_views` | 0 | Pixel page-view events |
| `wc_pixel_events` | 0 | Pixel conversion events |
| `wc_pixel_vitals` | 0 | Core Web Vitals from pixel |
| `wc_website_integrations` | 0 | GSC / GA4 / GBP OAuth connections |
| `wc_rank_projects` | 5 | Rank-tracking campaigns |
| `wc_tracked_keywords` | 6 | Keywords per rank project |
| `wc_keyword_rankings` | 6 | Daily position history |
| `wc_ranking_competitors` | 99 | Competitor snapshots per project |
| `wc_site_overview_cache` | 16 | Cached site-overview API responses |
| `wc_keyword_research_cache` | 0 | Keyword-ideas API cache |
| `wc_keyword_recommendations` | 0 | AI-generated keyword recommendations |
| `wc_user_saved_keywords` | 3 | User's manually saved keywords |
| `wc_gap_analysis_results` | 9 | Competitor gap analysis results |
| `wc_ai_content` | 3 | AI-generated article drafts |
| `wc_countries` | 197 | Country reference + DFS location codes |
| `wc_dataforseo_logs` | 1,531 | Every DataForSEO API call log |
| `wc_modules` | 42 | Feature-module registry |
| `wc_module_permission_mapping` | 100 | Module → subscription plan access |
| `wc_otp_codes` | 81 | OTP codes for email/2FA |
| `wc_password_resets` | 6 | Password reset tokens |
| `wc_invites_log` | 1 | Team invite log |
| `seo_keywords` | 1,358 | Ranked keyword cache (Keywords page) |

---

## Core Tables

### `wc_users`
```sql
id                INT UNSIGNED  PK AUTO_INC
full_name         VARCHAR(190)  NOT NULL
email             VARCHAR(190)  NOT NULL UNIQUE
password_hash     VARCHAR(255)  NOT NULL
email_verified_at DATETIME
status            ENUM('active','inactive','invited','suspended')  DEFAULT 'active'
is_owner          INT           DEFAULT 0
role              VARCHAR(20)   NOT NULL  DEFAULT 'member'  INDEX  -- owner | admin | member
invited_by        INT UNSIGNED  NULL  INDEX  -- wc_users.id of the inviter
last_login_at     DATETIME
created_at        DATETIME
updated_at        DATETIME
```

### `wc_websites`
```sql
id                   INT UNSIGNED  PK AUTO_INC
user_id              INT UNSIGNED  NOT NULL  INDEX
domain               VARCHAR(255)  NOT NULL
protocol             ENUM('http','https')  DEFAULT 'https'
start_url            VARCHAR(500)
sitemap_url          VARCHAR(500)
max_crawl_pages      INT UNSIGNED  DEFAULT 100
location             VARCHAR(255)
country_id           INT UNSIGNED  INDEX
verification_method  ENUM('search_console','dns_txt','html_file')
verification_status  ENUM('unverified','verifying','verified','failed')  DEFAULT 'unverified'  INDEX
verification_token   VARCHAR(64)
verified_at          DATETIME
include_subdomains   TINYINT(1)  DEFAULT 0
respect_robots_txt   TINYINT(1)  DEFAULT 1
crawl_query_strings  TINYINT(1)  DEFAULT 0
is_primary           TINYINT(1)  DEFAULT 0
pages_count          INT UNSIGNED
status               ENUM('active','inactive')  DEFAULT 'active'  INDEX
pixel_code           VARCHAR(32)
pixel_verified_at    DATETIME
automation_enabled   TINYINT(1)  DEFAULT 0
last_automation_run  DATETIME  NULL  COMMENT 'Timestamp of last automation queue processing'
business_json        TEXT
created_at           DATETIME
updated_at           DATETIME
```

**Indices:**
- PRIMARY: id
- INDEX: user_id, country_id, status, automation_enabled
- UNIQUE: (user_id, domain) composite index for duplicate check

**Foreign Keys:**
- user_id → wc_users.id (CASCADE DELETE)
- country_id → wc_countries.id

---

## Automation Queue Table (NEW - 2026-08-20)

### `wc_automation_queue`
Job queue for SEO automation processing (audit crawls, opportunity generation, rank sync).

```sql
id            BIGINT UNSIGNED  PK AUTO_INC
website_id    INT UNSIGNED     NOT NULL  INDEX  COMMENT 'FK to wc_websites'
user_id       INT UNSIGNED     NOT NULL  INDEX  COMMENT 'User who triggered the job'
job_type      VARCHAR(50)      NOT NULL  COMMENT 'audit_crawl | opportunity_gen | rank_sync'
status        ENUM('pending','processing','completed','failed')  DEFAULT 'pending'  INDEX
retry_count   INT UNSIGNED     DEFAULT 0  COMMENT 'Current retry attempt (max 3)'
last_error    LONGTEXT         NULL  COMMENT 'Error message if job failed'
created_at    DATETIME         NOT NULL  COMMENT 'When job was queued'
processed_at  DATETIME         NULL  COMMENT 'When job completed/failed'
```

**Indices:**
- PRIMARY: id
- COMPOSITE: (website_id, status) - query pending jobs per site
- COMPOSITE: (user_id, status) - query user's jobs
- INDEX: status - find pending/processing jobs
- INDEX: created_at - cleanup old jobs

**Foreign Keys:**
- website_id → wc_websites.id (CASCADE DELETE)

**Status Flow:**
```
pending → processing → completed
       ↘         ↘ failed (retry if retry_count < 3, else mark failed)
```

**Job Types:**
- `audit_crawl` - Run DataForSEO on-page audit
- `opportunity_gen` - Generate SEO opportunities (AI)
- `rank_sync` - Sync keyword rankings from DataForSEO SERP API

---

## Crawl / Audit Tables

### `wc_audits`
One row per DataForSEO crawl run.
```sql
id                INT UNSIGNED  PK AUTO_INC
website_id        INT UNSIGNED  NOT NULL  INDEX
user_id           INT UNSIGNED  NOT NULL  INDEX
dfs_task_id       VARCHAR(64)   INDEX
target            VARCHAR(255)  NOT NULL
max_crawl_pages   INT UNSIGNED  DEFAULT 10
status            ENUM('pending','queued','crawling','finished','failed','stopped')  DEFAULT 'pending'  INDEX
crawl_progress    VARCHAR(50)
pages_crawled     INT UNSIGNED  DEFAULT 0
pages_in_queue    INT UNSIGNED  DEFAULT 0
pages_added       INT
pages_removed     INT
pages_changed     INT
onpage_score      DECIMAL(5,2)
critical_count    INT UNSIGNED  DEFAULT 0
high_count        INT UNSIGNED  DEFAULT 0
medium_count      INT UNSIGNED  DEFAULT 0
low_count         INT UNSIGNED  DEFAULT 0
total_issues      INT UNSIGNED  DEFAULT 0
domain_info_json  LONGTEXT
page_metrics_json LONGTEXT
summary_json      LONGTEXT
started_at        DATETIME
finished_at       DATETIME
error_message     TEXT
created_at        DATETIME
updated_at        DATETIME
```

### `wc_audit_pages`
One row per crawled URL.
```sql
id               INT UNSIGNED   PK AUTO_INC
audit_id         INT UNSIGNED   NOT NULL  INDEX
url              VARCHAR(2048)  NOT NULL  INDEX
status_code      SMALLINT UNSIGNED
onpage_score     DECIMAL(5,2)
title            VARCHAR(512)
meta_description VARCHAR(1024)
h1               VARCHAR(512)
canonical_url    VARCHAR(2048)
is_indexable     TINYINT(1)
word_count       INT UNSIGNED
has_schema       TINYINT(1)
schema_types     VARCHAR(512)
inbound_links    INT UNSIGNED
outbound_links   INT UNSIGNED
internal_links   INT UNSIGNED
external_links   INT UNSIGNED
issues_count     SMALLINT UNSIGNED
meta_json        LONGTEXT
checks_json      LONGTEXT
created_at       DATETIME
updated_at       DATETIME
```

### `wc_audit_issues`
Aggregated issue summary per audit (one row per check_name).
```sql
id               INT UNSIGNED  PK AUTO_INC
audit_id         INT UNSIGNED  NOT NULL  INDEX
check_name       VARCHAR(100)  NOT NULL
title            VARCHAR(255)  NOT NULL
category         VARCHAR(100)  NOT NULL
issue_group      VARCHAR(100)
severity         ENUM('critical','high','medium','low')  DEFAULT 'medium'
affected_pages   INT UNSIGNED  DEFAULT 0
status           ENUM('open','resolved','ignored')  DEFAULT 'open'
sample_urls_json LONGTEXT
created_at       DATETIME
updated_at       DATETIME
```

---

## Pixel Tracking Tables

### `wc_pixel_views`
Every page view tracked by the WebCrawlers pixel snippet.
```sql
id           BIGINT UNSIGNED  PK AUTO_INC
website_id   BIGINT UNSIGNED  NOT NULL  INDEX(website_id, created_at)
url          VARCHAR(2048)    NOT NULL
title        VARCHAR(512)
referrer     VARCHAR(2048)
scroll_pct   TINYINT UNSIGNED  DEFAULT 0
duration_sec SMALLINT UNSIGNED DEFAULT 0
device       ENUM('desktop','mobile','tablet')  DEFAULT 'desktop'
created_at   DATETIME
```

### `wc_pixel_events`
Conversion events (form submits, clicks, custom JS events).
```sql
id          BIGINT UNSIGNED  PK AUTO_INC
website_id  BIGINT UNSIGNED  NOT NULL  INDEX
event_type  VARCHAR(64)      NOT NULL
url         VARCHAR(2048)    NOT NULL
data_json   TEXT
created_at  DATETIME
```

### `wc_pixel_vitals`
Core Web Vitals measurements from the pixel.
```sql
id          BIGINT UNSIGNED  PK AUTO_INC
website_id  BIGINT UNSIGNED  NOT NULL  INDEX
url         VARCHAR(2048)    NOT NULL
lcp         FLOAT
inp         FLOAT
cls         FLOAT
fcp         FLOAT
ttfb        FLOAT
created_at  DATETIME
```

---

## New Tables (AI Content Analyzer)

These tables are created via migration `2026-08-20-000001_CreateAiPageAnalyzerTables`. They persist page analyses, suggestion decisions, and the audit trail for **AI Content Analyzer**. Do not reuse `wc_ai_content` (that table is generated articles).

### `wc_ai_page_analyses` (NEW)
One analysis run per page (current vs proposed copy, DFS research payload, submit status).

```sql
CREATE TABLE `wc_ai_page_analyses` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED NOT NULL,
  `website_id` INT UNSIGNED NOT NULL,
  `audit_id` INT UNSIGNED NULL,
  `audit_page_id` INT UNSIGNED NULL,
  `page_url` VARCHAR(2048) NOT NULL,
  `page_path` VARCHAR(1024) NULL,
  `keyword` VARCHAR(255) NULL,
  `status` VARCHAR(32) DEFAULT 'ready',
  `current_json` LONGTEXT NULL,
  `proposed_json` LONGTEXT NULL,
  `research_json` LONGTEXT NULL,
  `provider` VARCHAR(32) NULL,
  `model` VARCHAR(64) NULL,
  `error_message` TEXT NULL,
  `submitted_at` DATETIME NULL,
  `created_at` DATETIME NULL,
  `updated_at` DATETIME NULL,
  KEY `user_website_created` (`user_id`, `website_id`, `created_at`),
  KEY `audit_page_id` (`audit_page_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
```

**Columns**:
- `audit_id` / `audit_page_id`: last crawl used for the Current snapshot (nullable)
- `status`: `ready` | `draft` | `submitted` | `failed`
- `current_json` / `proposed_json`: keys `metaTitle`, `metaDesc`, `h1`, `paragraph`, `faq`, `schema`
- `research_json`: SERP titles, PAA, volumes, on-page checks (evidence only)
- `submitted_at`: set when accepted changes are sent to publishing workflow (no CMS push in v1)

**Indexes**: user + website + created_at; audit_page_id

### `wc_ai_page_suggestions` (NEW)
Per-field suggestion cards (accept / reject / edit).

```sql
CREATE TABLE `wc_ai_page_suggestions` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `analysis_id` INT UNSIGNED NOT NULL,
  `field` VARCHAR(32) NULL,
  `type_label` VARCHAR(64) NOT NULL,
  `impact` VARCHAR(16) DEFAULT 'medium',
  `confidence` TINYINT UNSIGNED DEFAULT 0,
  `reason` TEXT NULL,
  `evidence` TEXT NULL,
  `proposed_text` TEXT NULL,
  `status` VARCHAR(16) DEFAULT 'pending',
  `sort_order` SMALLINT UNSIGNED DEFAULT 0,
  `created_at` DATETIME NULL,
  `updated_at` DATETIME NULL,
  KEY `analysis_status` (`analysis_id`, `status`),
  KEY `analysis_sort` (`analysis_id`, `sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
```

**Columns**:
- `field`: `metaTitle` | `metaDesc` | `h1` | `paragraph` | `faq` | `schema` | NULL (site-level, e.g. internal links)
- `impact`: `high` | `medium` | `low`
- `confidence`: 0–100
- `status`: `pending` | `accepted` | `rejected`
- `proposed_text`: LLM copy, or user edit after Accept/Edit

**Indexes**: analysis_id + status; analysis_id + sort_order

### `wc_ai_page_analysis_events` (NEW)
Audit trail for an analysis (analyzed, accepted, rejected, edited, saved_draft, submitted).

```sql
CREATE TABLE `wc_ai_page_analysis_events` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `analysis_id` INT UNSIGNED NOT NULL,
  `user_id` INT UNSIGNED NULL,
  `suggestion_id` INT UNSIGNED NULL,
  `action` VARCHAR(32) NOT NULL,
  `message` VARCHAR(512) NULL,
  `created_at` DATETIME NULL,
  KEY `analysis_created` (`analysis_id`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
```

**Columns**:
- `action`: `analyzed` | `accepted` | `rejected` | `edited` | `undid` | `accepted_all` | `saved_draft` | `submitted` | `failed`
- `suggestion_id`: optional pointer to the suggestion that changed

**Indexes**: analysis_id + created_at

---

## Raw SQL Statements
## Integration Tables

### `wc_website_integrations`
OAuth connections: Google Search Console, GA4, Google Business Profile.
```sql
id             BIGINT UNSIGNED  PK AUTO_INC
website_id     BIGINT UNSIGNED  NOT NULL  INDEX
user_id        BIGINT UNSIGNED  NOT NULL  INDEX
type           ENUM('gsc','ga4','gbp')  NOT NULL
status         ENUM('connected','disconnected','pending')  DEFAULT 'pending'
property_id    VARCHAR(255)
property_name  VARCHAR(255)
data           TEXT
connected_at   DATETIME
created_at     DATETIME
updated_at     DATETIME
UNIQUE(website_id, type)
```

#### 10. Create `wc_ai_page_analyses` table
```sql
CREATE TABLE `wc_ai_page_analyses` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED NOT NULL,
  `website_id` INT UNSIGNED NOT NULL,
  `audit_id` INT UNSIGNED NULL,
  `audit_page_id` INT UNSIGNED NULL,
  `page_url` VARCHAR(2048) NOT NULL,
  `page_path` VARCHAR(1024) NULL,
  `keyword` VARCHAR(255) NULL,
  `status` VARCHAR(32) DEFAULT 'ready',
  `current_json` LONGTEXT NULL,
  `proposed_json` LONGTEXT NULL,
  `research_json` LONGTEXT NULL,
  `provider` VARCHAR(32) NULL,
  `model` VARCHAR(64) NULL,
  `error_message` TEXT NULL,
  `submitted_at` DATETIME NULL,
  `created_at` DATETIME NULL,
  `updated_at` DATETIME NULL,
  KEY `user_website_created` (`user_id`, `website_id`, `created_at`),
  KEY `audit_page_id` (`audit_page_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

#### 11. Create `wc_ai_page_suggestions` table
```sql
CREATE TABLE `wc_ai_page_suggestions` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `analysis_id` INT UNSIGNED NOT NULL,
  `field` VARCHAR(32) NULL,
  `type_label` VARCHAR(64) NOT NULL,
  `impact` VARCHAR(16) DEFAULT 'medium',
  `confidence` TINYINT UNSIGNED DEFAULT 0,
  `reason` TEXT NULL,
  `evidence` TEXT NULL,
  `proposed_text` TEXT NULL,
  `status` VARCHAR(16) DEFAULT 'pending',
  `sort_order` SMALLINT UNSIGNED DEFAULT 0,
  `created_at` DATETIME NULL,
  `updated_at` DATETIME NULL,
  KEY `analysis_status` (`analysis_id`, `status`),
  KEY `analysis_sort` (`analysis_id`, `sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

#### 12. Create `wc_ai_page_analysis_events` table
```sql
CREATE TABLE `wc_ai_page_analysis_events` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `analysis_id` INT UNSIGNED NOT NULL,
  `user_id` INT UNSIGNED NULL,
  `suggestion_id` INT UNSIGNED NULL,
  `action` VARCHAR(32) NOT NULL,
  `message` VARCHAR(512) NULL,
  `created_at` DATETIME NULL,
  KEY `analysis_created` (`analysis_id`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

---

## Rank Tracking Tables

### `wc_rank_projects`
One campaign = one domain + location + target type.
```sql
id               BIGINT UNSIGNED  PK AUTO_INC
user_id          BIGINT UNSIGNED  NOT NULL  INDEX
website_id       BIGINT UNSIGNED  NOT NULL  INDEX
domain           VARCHAR(255)     NOT NULL
target_type      ENUM('domain','subdomain','url')  DEFAULT 'domain'
target_value     VARCHAR(512)     NOT NULL
name             VARCHAR(255)
description      TEXT
country_id       BIGINT UNSIGNED
location_code    INT UNSIGNED     DEFAULT 2840
language_code    VARCHAR(8)       DEFAULT 'en'
serp_depth       SMALLINT UNSIGNED DEFAULT 100
track_local_pack TINYINT(1)       DEFAULT 0
last_checked_at  DATETIME
next_refresh_at  DATETIME
status           ENUM('active','paused')  DEFAULT 'active'
created_at       DATETIME
updated_at       DATETIME
UNIQUE(user_id, website_id, location_code, target_type)
```

### `wc_tracked_keywords`
One row per keyword × device for a rank project.
```sql
id                  BIGINT UNSIGNED  PK AUTO_INC
project_id          BIGINT UNSIGNED  NOT NULL  INDEX
user_id             BIGINT UNSIGNED  NOT NULL  INDEX
website_id          BIGINT UNSIGNED  NOT NULL  INDEX
keyword             VARCHAR(255)     NOT NULL
keyword_hash        CHAR(32)         NOT NULL
device              ENUM('desktop','mobile')  DEFAULT 'desktop'
location_code       INT UNSIGNED     NOT NULL
search_volume       INT UNSIGNED
search_intent       VARCHAR(32)
position            SMALLINT UNSIGNED
rank_url            VARCHAR(2048)
prev_position       SMALLINT UNSIGNED
local_pack_position TINYINT UNSIGNED
serp_features_json  TEXT
etv                 FLOAT
last_checked_at     DATETIME
dfs_task_id         VARCHAR(64)
check_status        ENUM('idle','queued','running','ready','failed')  DEFAULT 'idle'  INDEX
is_active           TINYINT(1)  DEFAULT 1
status              ENUM('active','paused','archived')  DEFAULT 'active'
created_at          DATETIME
updated_at          DATETIME
UNIQUE(project_id, keyword_hash, device)
```

### `wc_keyword_rankings`
Historical position log — appended on every daily check.
```sql
id                  BIGINT UNSIGNED  PK AUTO_INC
tracked_keyword_id  BIGINT UNSIGNED  NOT NULL  INDEX
project_id          BIGINT UNSIGNED  NOT NULL  INDEX
check_date          DATE             NOT NULL
device              ENUM('desktop','mobile')  DEFAULT 'desktop'
position            SMALLINT UNSIGNED
previous_position   SMALLINT UNSIGNED
change              SMALLINT
rank_absolute       SMALLINT UNSIGNED
rank_url            VARCHAR(2048)
local_pack_position TINYINT UNSIGNED
serp_features_json  TEXT
etv                 FLOAT
crawled_at          DATETIME
created_at          DATETIME
```

### `wc_ranking_competitors`
Competitor domain snapshots per project.
```sql
id                BIGINT UNSIGNED  PK AUTO_INC
project_id        BIGINT UNSIGNED  NOT NULL  INDEX
domain            VARCHAR(255)     NOT NULL
avg_position      FLOAT
keywords_in_top10 INT UNSIGNED     DEFAULT 0
visibility        FLOAT
etv               FLOAT
keywords_count    INT UNSIGNED     DEFAULT 0
payload_json      TEXT
fetched_at        DATETIME
created_at        DATETIME
updated_at        DATETIME
```

---

## Keyword Research Tables

### `seo_keywords`
DataForSEO ranked-keywords cache — populated by the Keywords page search.  
Replaced atomically per domain+location+language+type on each DataForSEO fetch.
```sql
id                  BIGINT UNSIGNED  PK AUTO_INC
domain              VARCHAR(255)     NOT NULL
location_code       INT UNSIGNED     NOT NULL
language_code       VARCHAR(20)      NOT NULL
type                ENUM('organic','paid')  DEFAULT 'organic'
keyword             VARCHAR(500)     NOT NULL
position            INT UNSIGNED     DEFAULT 0
previous_position   INT UNSIGNED
position_difference INT
absolute_position   INT UNSIGNED     DEFAULT 0
traffic             DECIMAL(15,4)    DEFAULT 0
search_volume       BIGINT UNSIGNED  DEFAULT 0
cpc                 DECIMAL(15,4)    DEFAULT 0
keyword_difficulty  DECIMAL(8,4)     DEFAULT 0
intent              VARCHAR(50)
url                 TEXT
relative_url        TEXT
serp_features       TEXT
created_at          DATETIME         NOT NULL
updated_at          DATETIME         NOT NULL
UNIQUE(domain, location_code, language_code, type, keyword(191))
INDEX(position), INDEX(search_volume), INDEX(traffic), INDEX(cpc), INDEX(keyword_difficulty)
```

### `wc_keyword_research_cache`
Cached keyword-ideas API responses (expires after TTL).
```sql
id            BIGINT UNSIGNED  PK AUTO_INC
user_id       BIGINT UNSIGNED  NOT NULL  INDEX
keyword       VARCHAR(255)     NOT NULL  INDEX
location_code INT              NOT NULL
language_code VARCHAR(10)      NOT NULL
data_json     LONGTEXT         NOT NULL
api_cost      DECIMAL(10,4)    DEFAULT 0
created_at    DATETIME         NOT NULL
expires_at    DATETIME         NOT NULL  INDEX
```

### `wc_keyword_recommendations`
AI-generated keyword opportunities.
```sql
id                    BIGINT UNSIGNED  PK AUTO_INC
user_id               BIGINT UNSIGNED  NOT NULL  INDEX
website_id            BIGINT UNSIGNED  NOT NULL  INDEX
recommendation_id     VARCHAR(64)      NOT NULL  UNIQUE
keyword               VARCHAR(255)     NOT NULL  INDEX
location_code         INT              NOT NULL
language_code         VARCHAR(10)      NOT NULL
search_volume         INT              DEFAULT 0
keyword_difficulty    INT              DEFAULT 0
cpc                   DECIMAL(10,2)    DEFAULT 0
search_intent         ENUM('informational','navigational','commercial','transactional','unknown')
opportunity_score     DECIMAL(5,2)     DEFAULT 0
recommendation_type   ENUM('high_volume','low_difficulty','quick_win','long_tail','new_page')
recommended_page_url  VARCHAR(2048)
recommended_page_title VARCHAR(255)
status                ENUM('pending','approved','rejected','in_progress')  DEFAULT 'pending'  INDEX
created_at            DATETIME         NOT NULL  INDEX
updated_at            DATETIME
```

### `wc_user_saved_keywords`
Keywords manually saved from any research screen.
```sql
id                  BIGINT UNSIGNED  PK AUTO_INC
user_id             BIGINT UNSIGNED  NOT NULL  INDEX
website_id          BIGINT UNSIGNED  INDEX
keyword             VARCHAR(255)     NOT NULL
location_code       INT              DEFAULT 0
language_code       VARCHAR(10)      DEFAULT 'en'
search_volume       INT              DEFAULT 0
keyword_difficulty  INT              DEFAULT 0
cpc                 DECIMAL(10,2)    DEFAULT 0
search_intent       ENUM('informational','navigational','commercial','transactional','unknown')
competition_level   ENUM('high','medium','low','unknown')
traffic_potential   INT              DEFAULT 0
notes               TEXT
created_at          DATETIME         NOT NULL
updated_at          DATETIME
```

### `wc_gap_analysis_results`
Stored competitor keyword gap analysis results.
```sql
id                     BIGINT UNSIGNED  PK AUTO_INC
user_id                BIGINT UNSIGNED  NOT NULL  INDEX
website_id             BIGINT UNSIGNED  NOT NULL  INDEX
analysis_id            VARCHAR(64)      NOT NULL  UNIQUE
user_domain            VARCHAR(255)     NOT NULL
competitor_domains     JSON             NOT NULL
missing_keywords_json  LONGTEXT
shared_keywords_json   LONGTEXT
unique_keywords_json   LONGTEXT
total_missing          INT              DEFAULT 0
total_shared           INT              DEFAULT 0
total_unique           INT              DEFAULT 0
opportunity_score      DECIMAL(5,2)     DEFAULT 0
api_cost               DECIMAL(10,4)    DEFAULT 0
status                 ENUM('processing','completed','failed')  DEFAULT 'processing'  INDEX
error_message          TEXT
created_at             DATETIME         NOT NULL  INDEX
updated_at             DATETIME
```

---

## Cache Tables

### `wc_site_overview_cache`
Cached DataForSEO site-overview responses (traffic, domain metrics).
```sql
id            INT UNSIGNED  PK AUTO_INC
user_id       INT UNSIGNED  NOT NULL  INDEX
website_id    INT UNSIGNED  NOT NULL  INDEX
country_id    INT UNSIGNED  DEFAULT 0
domain        VARCHAR(255)  NOT NULL
location_code INT           DEFAULT 2840
language_code VARCHAR(16)   DEFAULT 'en'
section       VARCHAR(64)   NOT NULL
payload_json  LONGTEXT
fetched_at    DATETIME
expires_at    DATETIME      INDEX
created_at    DATETIME
updated_at    DATETIME
```

---

## AI Content Table

### `wc_ai_content`
```sql
id                  BIGINT UNSIGNED  PK AUTO_INC
user_id             BIGINT UNSIGNED  NOT NULL  INDEX
website_id          BIGINT UNSIGNED  NOT NULL  INDEX
title               VARCHAR(512)     NOT NULL
prompt              TEXT
primary_keyword     VARCHAR(255)     NOT NULL  INDEX
secondary_keywords  JSON
headings            JSON
include_json        JSON
faqs                JSON
tone                ENUM('professional','friendly','conversational')  DEFAULT 'professional'
word_count_target   SMALLINT UNSIGNED  DEFAULT 1200
article_source      VARCHAR(64)      DEFAULT 'ai_generation'
content_json        LONGTEXT         NOT NULL
content_html        LONGTEXT
word_count_actual   SMALLINT UNSIGNED  DEFAULT 0
seo_score           TINYINT UNSIGNED   DEFAULT 0
status              ENUM('draft','generating','in_progress','needs_review','complete','archived','not_started')
provider            ENUM('openai','gemini')
model               VARCHAR(64)
error_message       VARCHAR(512)
accepted_at         DATETIME
deleted_at          DATETIME
created_at          DATETIME
updated_at          DATETIME
```

---

## System / Auth Tables

### `wc_countries`
```sql
id                INT UNSIGNED  PK AUTO_INC
name              VARCHAR(150)  NOT NULL
iso2              CHAR(2)       NOT NULL  UNIQUE
iso3              CHAR(3)       NOT NULL  UNIQUE
dfs_location_code INT UNSIGNED  INDEX
phone_code        VARCHAR(10)
flag_emoji        VARCHAR(10)
flag_url          VARCHAR(255)
status            ENUM('active','inactive')  DEFAULT 'active'
created_at        DATETIME      DEFAULT CURRENT_TIMESTAMP
```

### `wc_dataforseo_logs`
```sql
id              INT UNSIGNED  PK AUTO_INC
user_id         INT UNSIGNED  INDEX
audit_id        INT UNSIGNED  INDEX
endpoint        VARCHAR(255)  NOT NULL  INDEX
http_method     VARCHAR(10)   DEFAULT 'POST'
request_json    LONGTEXT
response_json   LONGTEXT
http_status     SMALLINT UNSIGNED
dfs_status_code INT
duration_ms     INT UNSIGNED
created_at      DATETIME      INDEX
updated_at      DATETIME
```

### `wc_modules`
```sql
id          INT UNSIGNED  PK AUTO_INC
name        VARCHAR(255)  NOT NULL
code        VARCHAR(100)  NOT NULL  UNIQUE
parent_id   INT UNSIGNED  INDEX
sort_order  INT UNSIGNED  DEFAULT 1
status      ENUM('0','1') DEFAULT '1'  INDEX
created_at  DATETIME
updated_at  DATETIME
deleted_at  DATETIME      INDEX
```

### `wc_module_permission_mapping`
```sql
id         INT UNSIGNED  PK AUTO_INC
module_id  INT UNSIGNED  NOT NULL  INDEX
plan_code  VARCHAR(50)   NOT NULL  INDEX
is_allowed TINYINT(1)    NOT NULL  DEFAULT 1
created_at DATETIME
updated_at DATETIME
```

### `wc_otp_codes`
```sql
id          INT UNSIGNED  PK AUTO_INC
user_id     INT UNSIGNED  NOT NULL  INDEX
purpose     VARCHAR(20)   NOT NULL
channel     VARCHAR(10)   DEFAULT 'email'
code_hash   VARCHAR(255)  NOT NULL
attempts    TINYINT UNSIGNED  DEFAULT 0
expires_at  DATETIME      NOT NULL
used_at     DATETIME
created_at  DATETIME
```

### `wc_password_resets`
```sql
id          INT UNSIGNED  PK AUTO_INC
user_id     INT UNSIGNED  NOT NULL
token_hash  VARCHAR(255)  NOT NULL  INDEX
expires_at  DATETIME      NOT NULL
used_at     DATETIME
created_at  DATETIME
```

### `wc_invites_log`
```sql
id               INT UNSIGNED  PK AUTO_INC
invited_user_id  INT UNSIGNED  INDEX
email            VARCHAR(190)  NOT NULL  INDEX
full_name        VARCHAR(190)  NOT NULL
role             VARCHAR(50)   DEFAULT 'member'
token_hash       VARCHAR(64)   NOT NULL  UNIQUE
invited_by       INT UNSIGNED  NOT NULL  INDEX
is_accepted      TINYINT(1)    NOT NULL  INDEX  DEFAULT 0
sent_at          DATETIME      NOT NULL
accepted_at      DATETIME
message          TEXT
created_at       DATETIME
updated_at       DATETIME
```

---

## Model → Table Reference

| Model | Table | Primary use |
|---|---|---|
| 2026-08-11-000001 | Add pixel & automation columns to `wc_websites` | ✅ Applied |
| 2026-08-11-000002 | Create `wc_website_integrations` table | ✅ Applied |
| 2026-08-11-000003 | Create pixel tracking tables (`wc_pixel_*`) | ✅ Applied |
| 2026-08-18-000001 | Create rank tracking tables (`wc_rank_projects`, `wc_tracked_keywords`, `wc_keyword_rankings`, `wc_ranking_competitors`) | ✅ Applied |
| 2026-08-20-000001 | Create AI Content Analyzer tables (`wc_ai_page_analyses`, `wc_ai_page_suggestions`, `wc_ai_page_analysis_events`) | ✅ Applied |
| 2026-08-21-000001 | Add `role` + `invited_by` to `wc_users`; backfill owners | ✅ Applied |

All migrations have been applied to the development database. Run the SQL statements above to apply to production.

Existing databases can apply the role columns with:

```sql
ALTER TABLE `wc_users`
  ADD COLUMN `role` varchar(20) NOT NULL DEFAULT 'member' AFTER `is_owner`,
  ADD COLUMN `invited_by` int(10) unsigned DEFAULT NULL AFTER `role`,
  ADD KEY `idx_role` (`role`),
  ADD KEY `idx_invited_by` (`invited_by`);
UPDATE `wc_users` SET `role` = 'owner' WHERE `is_owner` = 1 AND `role` <> 'owner';
```

| `UserModel` | `wc_users` | Auth, session |
| `WebsiteModel` | `wc_websites` | Website CRUD, pixel, verification |
| `AuditModel` | `wc_audits` | Crawl runs, coverage, onpage score |
| `AuditPageModel` | `wc_audit_pages` | Per-URL crawl data |
| `AuditIssueModel` | `wc_audit_issues` | Issue aggregates |
| `RankProjectModel` | `wc_rank_projects` | Rank tracking campaigns |
| `TrackedKeywordModel` | `wc_tracked_keywords` | Keywords under tracking |
| `KeywordRankingModel` | `wc_keyword_rankings` | Daily position history |
| `RankingCompetitorModel` | `wc_ranking_competitors` | Competitor data |
| `KeywordModel` | `seo_keywords` | Keywords page search/cache |
| `SiteOverviewCacheModel` | `wc_site_overview_cache` | DFS site-overview cache |
| `CountryModel` | `wc_countries` | Country/location reference |
| `DataForSeoLogModel` | `wc_dataforseo_logs` | API call audit log |
| `ModuleModel` | `wc_modules` | Feature module registry |
| `ModulePermissionMappingModel` | `wc_module_permission_mapping` | Plan → module access |
| `OtpCodeModel` | `wc_otp_codes` | OTP verification |
| `PasswordResetModel` | `wc_password_resets` | Password reset tokens |
| `InviteLogModel` | `wc_invites_log` | Team invitations |

---

## Raw CREATE TABLE Statements

> **Production deployment reference.**  
> Run these in order on the production database if `php spark migrate` is unavailable.  
> Database: `webcrawlers` (or whichever name is used in production — update `Database.php` accordingly).  
> Charset: `utf8mb4`, collation: `utf8mb4_unicode_ci` recommended for all tables.

```sql
-- ============================================================
-- Run as: mysql -u root -p webcrawlers < this_file.sql
-- ============================================================

-- Verify new tables exist
SHOW TABLES LIKE 'wc_website_integrations';
SHOW TABLES LIKE 'wc_pixel_views';
SHOW TABLES LIKE 'wc_pixel_events';
SHOW TABLES LIKE 'wc_pixel_vitals';
SHOW TABLES LIKE 'wc_rank_projects';
SHOW TABLES LIKE 'wc_tracked_keywords';
SHOW TABLES LIKE 'wc_keyword_rankings';
SHOW TABLES LIKE 'wc_ranking_competitors';
SHOW TABLES LIKE 'wc_ai_page_analyses';
SHOW TABLES LIKE 'wc_ai_page_suggestions';
SHOW TABLES LIKE 'wc_ai_page_analysis_events';

-- Verify indexes
SHOW INDEXES FROM wc_website_integrations;
SHOW INDEXES FROM wc_pixel_views;
SHOW INDEXES FROM wc_pixel_events;
SHOW INDEXES FROM wc_pixel_vitals;
CREATE TABLE `seo_keywords` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `domain` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `location_code` int(10) unsigned NOT NULL,
  `language_code` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `type` enum('organic','paid') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'organic',
  `keyword` varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL,
  `position` int(10) unsigned NOT NULL DEFAULT '0',
  `previous_position` int(10) unsigned DEFAULT NULL,
  `position_difference` int(11) DEFAULT NULL,
  `absolute_position` int(10) unsigned NOT NULL DEFAULT '0',
  `traffic` decimal(15,4) NOT NULL DEFAULT '0.0000',
  `search_volume` bigint(20) unsigned NOT NULL DEFAULT '0',
  `cpc` decimal(15,4) NOT NULL DEFAULT '0.0000',
  `keyword_difficulty` decimal(8,4) NOT NULL DEFAULT '0.0000',
  `intent` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `url` text COLLATE utf8mb4_unicode_ci,
  `relative_url` text COLLATE utf8mb4_unicode_ci,
  `serp_features` text COLLATE utf8mb4_unicode_ci,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_seo_keywords` (`domain`,`location_code`,`language_code`,`type`,`keyword`(191)),
  KEY `idx_domain_location_language_type` (`domain`,`location_code`,`language_code`,`type`),
  KEY `idx_position` (`position`),
  KEY `idx_search_volume` (`search_volume`),
  KEY `idx_traffic` (`traffic`),
  KEY `idx_keyword_difficulty` (`keyword_difficulty`),
  KEY `idx_cpc` (`cpc`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `wc_ai_content` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `website_id` bigint(20) unsigned NOT NULL,
  `title` varchar(512) NOT NULL DEFAULT '',
  `prompt` text,
  `primary_keyword` varchar(255) NOT NULL DEFAULT '',
  `secondary_keywords` json DEFAULT NULL,
  `headings` json DEFAULT NULL,
  `include_json` json DEFAULT NULL,
  `faqs` json DEFAULT NULL,
  `tone` enum('professional','friendly','conversational') NOT NULL DEFAULT 'professional',
  `word_count_target` smallint(5) unsigned NOT NULL DEFAULT '1200',
  `article_source` varchar(64) NOT NULL DEFAULT 'ai_generation',
  `content_json` longtext NOT NULL,
  `content_html` longtext,
  `word_count_actual` smallint(5) unsigned NOT NULL DEFAULT '0',
  `seo_score` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `status` enum('draft','generating','in_progress','needs_review','complete','archived','not_started') NOT NULL DEFAULT 'generating',
  `provider` enum('openai','gemini') DEFAULT NULL,
  `model` varchar(64) DEFAULT NULL,
  `error_message` varchar(512) DEFAULT NULL,
  `accepted_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_status` (`user_id`,`status`,`deleted_at`),
  KEY `idx_website` (`website_id`),
  KEY `idx_created` (`user_id`,`created_at`),
  KEY `idx_keyword` (`primary_keyword`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `wc_audits` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `website_id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `dfs_task_id` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `target` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `max_crawl_pages` int(10) unsigned NOT NULL DEFAULT '10',
  `status` enum('pending','queued','crawling','finished','failed','stopped') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending',
  `crawl_progress` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `pages_crawled` int(10) unsigned NOT NULL DEFAULT '0',
  `pages_in_queue` int(10) unsigned NOT NULL DEFAULT '0',
  `pages_added` int(11) DEFAULT NULL,
  `pages_removed` int(11) DEFAULT NULL,
  `pages_changed` int(11) DEFAULT NULL,
  `onpage_score` decimal(5,2) DEFAULT NULL,
  `critical_count` int(10) unsigned NOT NULL DEFAULT '0',
  `high_count` int(10) unsigned NOT NULL DEFAULT '0',
  `medium_count` int(10) unsigned NOT NULL DEFAULT '0',
  `low_count` int(10) unsigned NOT NULL DEFAULT '0',
  `total_issues` int(10) unsigned NOT NULL DEFAULT '0',
  `domain_info_json` longtext COLLATE utf8mb4_unicode_ci,
  `page_metrics_json` longtext COLLATE utf8mb4_unicode_ci,
  `summary_json` longtext COLLATE utf8mb4_unicode_ci,
  `started_at` datetime DEFAULT NULL,
  `finished_at` datetime DEFAULT NULL,
  `error_message` text COLLATE utf8mb4_unicode_ci,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_website_id` (`website_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_dfs_task_id` (`dfs_task_id`),
  KEY `idx_status` (`status`),
  KEY `idx_website_created` (`website_id`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `wc_audit_issues` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `audit_id` int(10) unsigned NOT NULL,
  `check_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `category` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `issue_group` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `severity` enum('critical','high','medium','low') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'medium',
  `affected_pages` int(10) unsigned NOT NULL DEFAULT '0',
  `status` enum('open','resolved','ignored') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',
  `sample_urls_json` longtext COLLATE utf8mb4_unicode_ci,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_audit_check` (`audit_id`,`check_name`),
  KEY `idx_audit_id` (`audit_id`),
  KEY `idx_category` (`audit_id`,`category`),
  KEY `idx_severity` (`audit_id`,`severity`),
  KEY `idx_status` (`audit_id`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `wc_audit_pages` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `audit_id` int(10) unsigned NOT NULL,
  `url` varchar(2048) COLLATE utf8mb4_unicode_ci NOT NULL,
  `status_code` smallint(5) unsigned DEFAULT NULL,
  `onpage_score` decimal(5,2) DEFAULT NULL,
  `title` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `meta_description` varchar(1024) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `h1` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `canonical_url` varchar(2048) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_indexable` tinyint(1) DEFAULT NULL,
  `word_count` int(10) unsigned DEFAULT NULL,
  `has_schema` tinyint(1) DEFAULT NULL,
  `schema_types` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `inbound_links` int(10) unsigned DEFAULT NULL,
  `outbound_links` int(10) unsigned DEFAULT NULL,
  `internal_links` int(10) unsigned DEFAULT NULL,
  `external_links` int(10) unsigned DEFAULT NULL,
  `issues_count` smallint(5) unsigned DEFAULT NULL,
  `meta_json` longtext COLLATE utf8mb4_unicode_ci,
  `checks_json` longtext COLLATE utf8mb4_unicode_ci,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_audit_id` (`audit_id`),
  KEY `idx_audit_status` (`audit_id`,`status_code`),
  KEY `idx_url_prefix` (`url`(255)),
  KEY `idx_ap_fingerprint` (`audit_id`,`id`,`status_code`,`has_schema`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `wc_countries` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `iso2` char(2) COLLATE utf8mb4_unicode_ci NOT NULL,
  `iso3` char(3) COLLATE utf8mb4_unicode_ci NOT NULL,
  `dfs_location_code` int(10) unsigned DEFAULT NULL,
  `phone_code` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `flag_emoji` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `flag_url` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` enum('active','inactive') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'active',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_country_iso2` (`iso2`),
  UNIQUE KEY `uk_country_iso3` (`iso3`),
  KEY `idx_wc_countries_dfs_loc` (`dfs_location_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `wc_dataforseo_logs` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned DEFAULT NULL,
  `audit_id` int(10) unsigned DEFAULT NULL,
  `endpoint` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `http_method` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'POST',
  `request_json` longtext COLLATE utf8mb4_unicode_ci,
  `response_json` longtext COLLATE utf8mb4_unicode_ci,
  `http_status` smallint(5) unsigned DEFAULT NULL,
  `dfs_status_code` int(11) DEFAULT NULL,
  `duration_ms` int(10) unsigned DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_audit_id` (`audit_id`),
  KEY `idx_endpoint` (`endpoint`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `wc_gap_analysis_results` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `website_id` bigint(20) unsigned NOT NULL,
  `analysis_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_domain` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `competitor_domains` json NOT NULL,
  `missing_keywords_json` longtext COLLATE utf8mb4_unicode_ci,
  `shared_keywords_json` longtext COLLATE utf8mb4_unicode_ci,
  `unique_keywords_json` longtext COLLATE utf8mb4_unicode_ci,
  `total_missing` int(11) DEFAULT '0',
  `total_shared` int(11) DEFAULT '0',
  `total_unique` int(11) DEFAULT '0',
  `opportunity_score` decimal(5,2) DEFAULT '0.00',
  `api_cost` decimal(10,4) DEFAULT '0.0000',
  `status` enum('processing','completed','failed') COLLATE utf8mb4_unicode_ci DEFAULT 'processing',
  `error_message` text COLLATE utf8mb4_unicode_ci,
  `created_at` datetime NOT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `analysis_id` (`analysis_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_website_id` (`website_id`),
  KEY `idx_status` (`status`),
  KEY `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `wc_invites_log` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `invited_user_id` int(10) unsigned DEFAULT NULL,
  `email` varchar(190) NOT NULL,
  `full_name` varchar(190) NOT NULL,
  `role` varchar(50) NOT NULL DEFAULT 'member',
  `token_hash` varchar(64) NOT NULL,
  `invited_by` int(10) unsigned NOT NULL,
  `is_accepted` tinyint(1) NOT NULL DEFAULT '0',
  `sent_at` datetime NOT NULL,
  `accepted_at` datetime DEFAULT NULL,
  `message` text,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_invite_token` (`token_hash`),
  KEY `idx_invite_email` (`email`),
  KEY `idx_invite_by` (`invited_by`),
  KEY `idx_invite_user` (`invited_user_id`),
  KEY `idx_invite_accepted` (`is_accepted`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `wc_keyword_rankings` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tracked_keyword_id` bigint(20) unsigned NOT NULL,
  `project_id` bigint(20) unsigned NOT NULL,
  `check_date` date NOT NULL,
  `device` enum('desktop','mobile') NOT NULL DEFAULT 'desktop',
  `position` smallint(5) unsigned DEFAULT NULL,
  `previous_position` smallint(5) unsigned DEFAULT NULL,
  `change` smallint(6) DEFAULT NULL,
  `rank_absolute` smallint(5) unsigned DEFAULT NULL,
  `rank_url` varchar(2048) DEFAULT NULL,
  `local_pack_position` tinyint(3) unsigned DEFAULT NULL,
  `serp_features_json` text,
  `etv` float DEFAULT NULL,
  `crawled_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tracked_keyword_id_check_date` (`tracked_keyword_id`,`check_date`),
  KEY `project_id_check_date` (`project_id`,`check_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `wc_keyword_recommendations` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `website_id` bigint(20) unsigned NOT NULL,
  `recommendation_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `keyword` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `location_code` int(11) NOT NULL,
  `language_code` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
  `search_volume` int(11) DEFAULT '0',
  `keyword_difficulty` int(11) DEFAULT '0',
  `cpc` decimal(10,2) DEFAULT '0.00',
  `search_intent` enum('informational','navigational','commercial','transactional','unknown') COLLATE utf8mb4_unicode_ci DEFAULT 'unknown',
  `opportunity_score` decimal(5,2) DEFAULT '0.00',
  `recommendation_type` enum('high_volume','low_difficulty','quick_win','long_tail','new_page') COLLATE utf8mb4_unicode_ci DEFAULT 'new_page',
  `recommended_page_url` varchar(2048) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `recommended_page_title` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` enum('pending','approved','rejected','in_progress') COLLATE utf8mb4_unicode_ci DEFAULT 'pending',
  `created_at` datetime NOT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `recommendation_id` (`recommendation_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_website_id` (`website_id`),
  KEY `idx_keyword` (`keyword`),
  KEY `idx_status` (`status`),
  KEY `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `wc_keyword_research_cache` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `keyword` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `location_code` int(11) NOT NULL,
  `language_code` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
  `data_json` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `api_cost` decimal(10,4) DEFAULT '0.0000',
  `created_at` datetime NOT NULL,
  `expires_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_keyword_location_lang` (`keyword`,`location_code`,`language_code`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_expires` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `wc_modules` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `code` varchar(100) NOT NULL,
  `parent_id` int(10) unsigned DEFAULT NULL,
  `sort_order` int(10) unsigned NOT NULL DEFAULT '1',
  `status` enum('0','1') NOT NULL DEFAULT '1',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_module_code` (`code`),
  KEY `idx_module_parent` (`parent_id`),
  KEY `idx_module_parent_order` (`parent_id`,`sort_order`),
  KEY `idx_module_status` (`status`),
  KEY `idx_module_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `wc_module_permission_mapping` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `module_id` int(10) unsigned NOT NULL,
  `plan_code` varchar(50) NOT NULL,
  `is_allowed` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_module_plan` (`module_id`,`plan_code`),
  KEY `idx_plan_code` (`plan_code`),
  KEY `idx_module_id` (`module_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `wc_otp_codes` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) unsigned NOT NULL,
  `purpose` varchar(20) NOT NULL,
  `channel` varchar(10) NOT NULL DEFAULT 'email',
  `code_hash` varchar(255) NOT NULL,
  `attempts` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `expires_at` datetime NOT NULL,
  `used_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id_purpose` (`user_id`,`purpose`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `wc_password_resets` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) unsigned NOT NULL,
  `token_hash` varchar(255) NOT NULL,
  `expires_at` datetime NOT NULL,
  `used_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `token_hash` (`token_hash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `wc_pixel_events` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `website_id` bigint(20) unsigned NOT NULL,
  `event_type` varchar(64) NOT NULL,
  `url` varchar(2048) NOT NULL,
  `data_json` text,
  `created_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `website_id_event_type_created_at` (`website_id`,`event_type`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `wc_pixel_views` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `website_id` bigint(20) unsigned NOT NULL,
  `url` varchar(2048) NOT NULL,
  `title` varchar(512) DEFAULT NULL,
  `referrer` varchar(2048) DEFAULT NULL,
  `scroll_pct` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `duration_sec` smallint(5) unsigned NOT NULL DEFAULT '0',
  `device` enum('desktop','mobile','tablet') NOT NULL DEFAULT 'desktop',
  `created_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `website_id_created_at` (`website_id`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `wc_pixel_vitals` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `website_id` bigint(20) unsigned NOT NULL,
  `url` varchar(2048) NOT NULL,
  `lcp` float DEFAULT NULL,
  `inp` float DEFAULT NULL,
  `cls` float DEFAULT NULL,
  `fcp` float DEFAULT NULL,
  `ttfb` float DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `website_id_created_at` (`website_id`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `wc_ranking_competitors` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `project_id` bigint(20) unsigned NOT NULL,
  `domain` varchar(255) NOT NULL,
  `avg_position` float DEFAULT NULL,
  `keywords_in_top10` int(10) unsigned NOT NULL DEFAULT '0',
  `visibility` float DEFAULT NULL,
  `etv` float DEFAULT NULL,
  `keywords_count` int(10) unsigned NOT NULL DEFAULT '0',
  `payload_json` text,
  `fetched_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `project_id_domain` (`project_id`,`domain`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `wc_rank_projects` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `website_id` bigint(20) unsigned NOT NULL,
  `domain` varchar(255) NOT NULL,
  `target_type` enum('domain','subdomain','url') NOT NULL DEFAULT 'domain',
  `target_value` varchar(512) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `description` text,
  `country_id` bigint(20) unsigned DEFAULT NULL,
  `location_code` int(10) unsigned NOT NULL DEFAULT '2840',
  `language_code` varchar(8) NOT NULL DEFAULT 'en',
  `serp_depth` smallint(5) unsigned NOT NULL DEFAULT '100',
  `track_local_pack` tinyint(1) NOT NULL DEFAULT '0',
  `last_checked_at` datetime DEFAULT NULL,
  `next_refresh_at` datetime DEFAULT NULL,
  `status` enum('active','paused') NOT NULL DEFAULT 'active',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id_website_id_location_code_target_type` (`user_id`,`website_id`,`location_code`,`target_type`),
  KEY `user_id` (`user_id`),
  KEY `website_id` (`website_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `wc_site_overview_cache` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `website_id` int(10) unsigned NOT NULL,
  `country_id` int(10) unsigned NOT NULL DEFAULT '0',
  `domain` varchar(255) NOT NULL,
  `location_code` int(11) NOT NULL DEFAULT '2840',
  `language_code` varchar(16) NOT NULL DEFAULT 'en',
  `section` varchar(64) NOT NULL,
  `payload_json` longtext,
  `fetched_at` datetime DEFAULT NULL,
  `expires_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_so_cache` (`website_id`,`country_id`,`language_code`,`section`),
  KEY `idx_so_user` (`user_id`),
  KEY `idx_so_expires` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `wc_tracked_keywords` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `project_id` bigint(20) unsigned NOT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  `website_id` bigint(20) unsigned NOT NULL,
  `keyword` varchar(255) NOT NULL,
  `keyword_hash` char(32) NOT NULL,
  `device` enum('desktop','mobile') NOT NULL DEFAULT 'desktop',
  `location_code` int(10) unsigned NOT NULL,
  `search_volume` int(10) unsigned DEFAULT NULL,
  `search_intent` varchar(32) DEFAULT NULL,
  `position` smallint(5) unsigned DEFAULT NULL,
  `rank_url` varchar(2048) DEFAULT NULL,
  `prev_position` smallint(5) unsigned DEFAULT NULL,
  `local_pack_position` tinyint(3) unsigned DEFAULT NULL,
  `serp_features_json` text,
  `etv` float DEFAULT NULL,
  `last_checked_at` datetime DEFAULT NULL,
  `dfs_task_id` varchar(64) DEFAULT NULL,
  `check_status` enum('idle','queued','running','ready','failed') NOT NULL DEFAULT 'idle',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `status` enum('active','paused','archived') NOT NULL DEFAULT 'active',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `project_id_keyword_hash_device` (`project_id`,`keyword_hash`,`device`),
  KEY `website_id_device` (`website_id`,`device`),
  KEY `project_id_is_active` (`project_id`,`is_active`),
  KEY `check_status` (`check_status`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `wc_users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `full_name` varchar(190) NOT NULL,
  `email` varchar(190) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `email_verified_at` datetime DEFAULT NULL,
  `status` enum('active','inactive','invited','suspended') NOT NULL DEFAULT 'active',
  `is_owner` int(11) DEFAULT '0',
  `role` varchar(20) NOT NULL DEFAULT 'member',
  `invited_by` int(10) unsigned DEFAULT NULL,
  `last_login_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`),
  KEY `idx_role` (`role`),
  KEY `idx_invited_by` (`invited_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `wc_user_saved_keywords` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `website_id` bigint(20) unsigned DEFAULT NULL,
  `keyword` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `location_code` int(11) DEFAULT '0',
  `language_code` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT 'en',
  `search_volume` int(11) DEFAULT '0',
  `keyword_difficulty` int(11) DEFAULT '0',
  `cpc` decimal(10,2) DEFAULT '0.00',
  `search_intent` enum('informational','navigational','commercial','transactional','unknown') COLLATE utf8mb4_unicode_ci DEFAULT 'unknown',
  `competition_level` enum('high','medium','low','unknown') COLLATE utf8mb4_unicode_ci DEFAULT 'unknown',
  `traffic_potential` int(11) DEFAULT '0',
  `notes` text COLLATE utf8mb4_unicode_ci,
  `created_at` datetime NOT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_website_id` (`website_id`),
  KEY `idx_user_keyword` (`user_id`,`keyword`),
  KEY `idx_user_created` (`user_id`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `wc_websites` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `domain` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `protocol` enum('http','https') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'https',
  `start_url` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sitemap_url` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `max_crawl_pages` int(10) unsigned NOT NULL DEFAULT '100',
  `location` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `country_id` int(10) unsigned DEFAULT NULL,
  `verification_method` enum('search_console','dns_txt','html_file') COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `verification_status` enum('unverified','verifying','verified','failed') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'unverified',
  `verification_token` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `verified_at` datetime DEFAULT NULL,
  `include_subdomains` tinyint(1) NOT NULL DEFAULT '0',
  `respect_robots_txt` tinyint(1) NOT NULL DEFAULT '1',
  `crawl_query_strings` tinyint(1) NOT NULL DEFAULT '0',
  `is_primary` tinyint(1) NOT NULL DEFAULT '0',
  `pages_count` int(10) unsigned DEFAULT NULL,
  `status` enum('active','inactive') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'active',
  `pixel_code` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `pixel_verified_at` datetime DEFAULT NULL,
  `automation_enabled` tinyint(1) DEFAULT '0',
  `business_json` text COLLATE utf8mb4_unicode_ci,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user_domain` (`user_id`,`domain`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_status` (`status`),
  KEY `idx_country_id` (`country_id`),
  KEY `idx_verification_status` (`verification_status`),
  KEY `idx_is_primary` (`user_id`,`is_primary`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `wc_website_integrations` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `website_id` bigint(20) unsigned NOT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  `type` enum('gsc','ga4','gbp') NOT NULL,
  `status` enum('connected','disconnected','pending') NOT NULL DEFAULT 'pending',
  `property_id` varchar(255) DEFAULT NULL,
  `property_name` varchar(255) DEFAULT NULL,
  `data` text,
  `connected_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `website_id_type` (`website_id`,`type`),
  KEY `website_id` (`website_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
```

---

## MVP Tech Stack & Data Flow

**Current Implementation (2026-08-20):**

```
User Dashboard (Browser)
  ↓
API Endpoints (CodeIgniter 4)
  ├─ WebsitesController
  ├─ AutomationController (automation job processing)
  └─ ... (other endpoints)
  ↓
Database Layer (MySQL)
  ├─ wc_websites (projects)
  ├─ wc_automation_queue (job queue)
  ├─ wc_audits (DataForSEO crawls)
  └─ ... (other tables)
  ↓
External Services (Phase 1 - MVP)
  ├─ DataForSEO API (keywords, rank tracking, audits)
  ├─ OpenAI GPT-4 (AI content)
  └─ CRM (lead management)

NOT NEEDED FOR MVP:
  ❌ Google Search Console API
  ❌ Google Analytics 4 API
  ❌ Google Business Profile API
  ❌ Google OAuth2
```

**Rationale:** DataForSEO provides complete SEO automation features (technical audit, keyword research, rank tracking) without external Google APIs. Google integrations can be added post-MVP if users request real analytics/search data.

See [DATAFORSEO_VS_GOOGLE_APIS_ANALYSIS.md](../DATAFORSEO_VS_GOOGLE_APIS_ANALYSIS.md) for detailed analysis.

---

## Schema Notes

**Last Updated:** 2026-08-20  
**Version:** 1.0-MVP

**Recent Additions:**
- `wc_automation_queue` table for SEO automation job queueing
- `last_automation_run` column in `wc_websites` for rate-limiting
- Automation-focused indices on queue table

**Removed/Deprecated:**
- `wc_website_integrations` table (GSC/GA4/GBP) — not used in MVP, available for Phase 2