# Database Schema — WebCrawlers

**Database**: `webcrawlerdb`  
**Last Updated**: 2026-08-25 (migrated off the shared `cms` database)  
**Engine**: MySQL 5.7.44 · InnoDB · `ROW_FORMAT=DYNAMIC`  
**Charset**: `utf8mb4` / `utf8mb4_unicode_ci` — uniform across **every table and every column**

> **Migrated from `cms`.** WebCrawlers previously shared the `cms` database with
> other MSA apps, including its `migrations` table. It now owns `webcrawlerdb`
> and the whole schema is built by the migrations in
> `app/Database/Migrations/` — see [Migration Reference](#migration-reference).
>
> The one remaining `cms` dependency is the legacy `\cms_model` used by
> `App\Controllers\Blog` for article content. It connects separately via the
> `MSACOMMON_DB_*_CMS` constants and is unaffected by this move.

---

## Table Index

39 tables, all created by migration. "Seeded" marks rows loaded by
`php spark db:seed DatabaseSeeder`; everything else starts empty on a fresh
install.

**Why `utf8mb4_unicode_ci`:** the server is MySQL 5.7.44, so
`utf8mb4_0900_ai_ci` (8.0-only) is unavailable — confirmed absent from
`information_schema.collations`. That leaves `utf8mb4_general_ci` (the server
default) and `utf8mb4_unicode_ci`. `utf8mb4_unicode_ci` is chosen for correct
multilingual sorting, and is applied to every table and column so no join can
ever raise "illegal mix of collations". Each table sets this explicitly, so the
*database* default charset is irrelevant and need not be changed — see
[step 1 of the runbook](#1-confirm-the-database-you-do-not-need-to-create-or-alter-it).

| Table | Rows on fresh install | Purpose |
|---|---|---|
| `wc_users` | **3 (seeded)** | User accounts + auth — the three owner logins |
| `wc_websites` | 0 | Registered websites (projects) per user |
| `wc_countries` | **197 (seeded)** | Country reference + DFS location codes |
| `wc_automation_queue` | 0 | SEO automation job queue |
| `wc_audits` | 0 | DataForSEO crawl audit runs |
| `wc_audit_pages` | 0 | Per-page crawl results |
| `wc_audit_issues` | 0 | 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` | 0 | Rank-tracking campaigns |
| `wc_tracked_keywords` | 0 | Keywords per rank project |
| `wc_keyword_rankings` | 0 | Daily position history |
| `wc_ranking_competitors` | 0 | Competitor snapshots per project |
| `wc_seo_keywords` | 0 | Ranked + researched keyword cache (Keywords page) |
| `wc_keyword_research_cache` | 0 | Keyword-ideas API cache |
| `wc_keyword_recommendations` | 0 | AI-generated keyword recommendations |
| `wc_user_saved_keywords` | 0 | User's manually saved keywords |
| `wc_keyword_lists` | 0 | Named keyword lists |
| `wc_keyword_list_items` | 0 | Keywords belonging to a list |
| `wc_gap_analysis_results` | 0 | Competitor gap analysis results |
| `wc_site_overview_cache` | 0 | Cached site-overview API responses |
| `wc_ai_content` | 0 | AI-generated article drafts |
| `wc_ai_page_analyses` | 0 | AI page analyzer runs |
| `wc_ai_page_suggestions` | 0 | Individual suggestions per analysis |
| `wc_ai_page_analysis_events` | 0 | Audit trail for analyses |
| `wc_dataforseo_logs` | 0 | Every DataForSEO API call log |
| `wc_modules` | **46 (seeded)** | Feature-module registry |
| `wc_module_permission_mapping` | **94 (seeded)** | Module → subscription plan access |
| `wc_otp_codes` | 0 | OTP codes for email/2FA |
| `wc_password_resets` | 0 | Password reset tokens |
| `wc_invites_log` | 0 | Team invite log |
| `wc_leads` | 0 | Contact + demo-booking leads |
| `wc_subscription` | 0 | Active subscriptions |
| `wc_subscription_history` | 0 | Billing transaction history |
| `wc_subscription_plans` | **4 (seeded)** | Plan catalogue |
| `wc_cancel_subscriptions` | 0 | Subscription cancellation log (reason + Sticky subscribe id) |

---

## Core Tables

Identity and project tables. `wc_countries` is reference data loaded by
`CountrySeeder`; the rest start empty.

> **On foreign keys:** the schema has exactly one DB-level foreign key —
> `wc_automation_queue.website_id → wc_websites.id (ON DELETE CASCADE)`.
> Every other relationship listed below is enforced in application code only,
> and is annotated "logical only". Referential integrity for those is the
> model layer's job, so deletes must clean up children explicitly.

### `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
invited_by        INT UNSIGNED  INDEX DEFAULT NULL
last_login_at     DATETIME
created_at        DATETIME
updated_at        DATETIME
```

**Indices:**
- PRIMARY: `id`
- UNIQUE: `email`
- INDEX: `idx_role` (`role`)
- INDEX: `idx_invited_by` (`invited_by`)


### `wc_websites`
```sql
id                   INT UNSIGNED  PK AUTO_INCREMENT
user_id              INT UNSIGNED  NOT NULL
domain               VARCHAR(255)  NOT NULL
protocol             ENUM('http','https')  NOT NULL  DEFAULT 'https'
start_url            VARCHAR(500)  NULL
sitemap_url          VARCHAR(500)  NULL
max_crawl_pages      INT UNSIGNED  NOT NULL  DEFAULT 100
location             VARCHAR(255)  NULL
country_id           INT UNSIGNED  NULL
verification_method  ENUM('search_console','dns_txt','html_file')  NULL
verification_status  ENUM('unverified','verifying','verified','failed')  NOT NULL  DEFAULT 'unverified'
verification_token   VARCHAR(64)  NULL
verified_at          DATETIME  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 UNSIGNED  NULL
status               ENUM('active','inactive')  NOT NULL  DEFAULT 'active'
pixel_code           VARCHAR(32)  NULL
pixel_verified_at    DATETIME  NULL
automation_enabled   TINYINT(1)  NULL  DEFAULT 0
is_seo_project       TINYINT(1)  NULL  DEFAULT 0  COMMENT 'Flag: Project created via SEO Automation wizard (1) vs regular website registration (0)'
business_json        TEXT  NULL
created_at           DATETIME  NULL
updated_at           DATETIME  NULL
last_automation_run  DATETIME  NULL  COMMENT 'Timestamp of last automation queue processing run'
```

**Indices:**
- PRIMARY: `id`
- UNIQUE: `uk_user_domain` (user_id, domain)
- INDEX: `idx_user_id` (user_id)
- INDEX: `idx_status` (status)
- INDEX: `idx_country_id` (country_id)
- INDEX: `idx_verification_status` (verification_status)
- INDEX: `idx_is_primary` (user_id, is_primary) — composite

**Foreign Keys:**
- user_id → wc_users.id (logical only — no DB-level constraint)
- country_id → wc_countries.id (logical only — no DB-level constraint)

---

## Automation Queue Table

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

```sql
id                   BIGINT UNSIGNED  PK AUTO_INCREMENT
website_id           INT UNSIGNED     NOT NULL  COMMENT 'FK to wc_websites'
user_id              INT UNSIGNED     NOT NULL  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')  NULL  DEFAULT 'pending'
retry_count          INT(11)          NULL  DEFAULT 0  COMMENT 'Current retry attempt'
last_error           LONGTEXT         NULL  COMMENT 'Error message if job failed'
frequency            VARCHAR(32)      NOT NULL  DEFAULT 'daily'
start_time           CHAR(5)          NOT NULL  DEFAULT '08:00'
respect_robots_txt   TINYINT(1)       NOT NULL  DEFAULT 1
created_at           DATETIME         NOT NULL  COMMENT 'When job was queued'
processed_at         DATETIME         NULL  COMMENT 'When job completed/failed'
updated_at           DATETIME         NULL
```

**Indices:**
- PRIMARY: `id`
- UNIQUE: `uniq_wc_auto_user_website` (user_id, website_id)
- INDEX: `idx_website_status` (website_id, status) — query pending jobs per site
- INDEX: `idx_user_status` (user_id, status) — query user's jobs
- INDEX: `idx_status` (status) — find pending/processing 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_INCREMENT
website_id         INT UNSIGNED  NOT NULL
user_id            INT UNSIGNED  NOT NULL
dfs_task_id        VARCHAR(64)   NULL
target             VARCHAR(255)  NOT NULL
max_crawl_pages    INT UNSIGNED  NOT NULL  DEFAULT 10
status             ENUM('pending','queued','crawling','finished','failed','stopped')  NOT NULL  DEFAULT 'pending'
crawl_progress     VARCHAR(50)   NULL
pages_crawled      INT UNSIGNED  NOT NULL  DEFAULT 0
pages_in_queue     INT UNSIGNED  NOT NULL  DEFAULT 0
pages_added        INT(11)       NULL
pages_removed      INT(11)       NULL
pages_changed      INT(11)       NULL
onpage_score       DECIMAL(5,2)  NULL
critical_count     INT UNSIGNED  NOT NULL  DEFAULT 0
high_count         INT UNSIGNED  NOT NULL  DEFAULT 0
medium_count       INT UNSIGNED  NOT NULL  DEFAULT 0
low_count          INT UNSIGNED  NOT NULL  DEFAULT 0
total_issues       INT UNSIGNED  NOT NULL  DEFAULT 0
domain_info_json   LONGTEXT      NULL
page_metrics_json  LONGTEXT      NULL
summary_json       LONGTEXT      NULL
started_at         DATETIME      NULL
finished_at        DATETIME      NULL
error_message      TEXT          NULL
created_at         DATETIME      NULL
updated_at         DATETIME      NULL
```

**Indices:**
- PRIMARY: `id`
- INDEX: `idx_website_id` (website_id)
- INDEX: `idx_user_id` (user_id)
- INDEX: `idx_dfs_task_id` (dfs_task_id)
- INDEX: `idx_status` (status)
- INDEX: `idx_website_created` (website_id, created_at) — composite

**Foreign Keys:**
- website_id → wc_websites.id (logical only — no DB-level constraint)
- user_id → wc_users.id (logical only — no DB-level constraint)


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

**Indices:**
- PRIMARY: `id`
- INDEX: `idx_audit_id` (audit_id)
- INDEX: `idx_audit_status` (audit_id, status_code) — composite
- INDEX: `idx_url_prefix` (url(255)) — **prefix index**, only first 255 chars of url are indexed
- INDEX: `idx_ap_fingerprint` (audit_id, status_code, has_schema) — composite

**Foreign Keys:**
- audit_id → wc_audits.id (logical only — no DB-level constraint)


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

**Indices:**
- PRIMARY: `id`
- UNIQUE: `uk_audit_check` (audit_id, check_name) — prevents duplicate issue rows per audit/check combo
- INDEX: `idx_audit_id` (audit_id)
- INDEX: `idx_category` (audit_id, category) — composite
- INDEX: `idx_severity` (audit_id, severity) — composite
- INDEX: `idx_status` (audit_id, status) — composite

**Foreign Keys:**
- audit_id → wc_audits.id (logical only — no DB-level constraint)

---

## Pixel Tracking Tables

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

**Indices:**
- PRIMARY: `id`
- INDEX: `website_id_created_at` (website_id, created_at) — composite, no single-column index on `website_id` alone

**Foreign Keys:**
- website_id → wc_websites.id (logical only — no DB-level constraint)


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

**Indices:**
- PRIMARY: `id`
- INDEX: `website_id_event_type_created_at` (website_id, event_type, created_at) — composite, no single-column index on `website_id` alone

**Foreign Keys:**
- website_id → wc_websites.id (logical only — no DB-level constraint)

---

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

**Indices:**
- PRIMARY: `id`
- INDEX: `website_id_created_at` (website_id, created_at) — composite, no single-column index on `website_id` alone

**Foreign Keys:**
- website_id → wc_websites.id (logical only — no DB-level constraint)

---

## Integration Tables

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

**Indices:**
- PRIMARY: `id`
- UNIQUE: `website_id_type` (website_id, type) — one integration of each type per website
- INDEX: `website_id` (website_id)
- INDEX: `user_id` (user_id)

**Foreign Keys:**
- website_id → wc_websites.id (logical only — no DB-level constraint)
- user_id → wc_users.id (logical only — no DB-level constraint)

---

## Rank Tracking Tables

### `wc_rank_projects`
One campaign = one domain + location + target type.
```sql
id                BIGINT UNSIGNED  PK AUTO_INCREMENT
user_id           BIGINT UNSIGNED  NOT NULL
website_id        BIGINT 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)     NULL
description       TEXT             NULL
country_id        BIGINT UNSIGNED  NULL
location_code     INT UNSIGNED     NOT NULL  DEFAULT 2840
language_code     VARCHAR(8)       NOT NULL  DEFAULT 'en'
serp_depth        SMALLINT UNSIGNED  NOT NULL  DEFAULT 100
track_local_pack  TINYINT(1)       NOT NULL  DEFAULT 0
last_checked_at   DATETIME         NULL
next_refresh_at   DATETIME         NULL
status            ENUM('active','paused')  NOT NULL  DEFAULT 'active'
created_at        DATETIME         NULL
updated_at        DATETIME         NULL
```

**Indices:**
- PRIMARY: `id`
- UNIQUE: `user_id_website_id_location_code_target_type` (user_id, website_id, location_code, target_type)
- INDEX: `user_id` (user_id)
- INDEX: `website_id` (website_id)

**Foreign Keys:**
- user_id → wc_users.id (logical only — no DB-level constraint)
- website_id → wc_websites.id (logical only — no DB-level constraint)
- country_id → wc_countries.id (logical only — no DB-level constraint)

---

### `wc_tracked_keywords`
One row per keyword × device for a rank project.
```sql
id                   BIGINT UNSIGNED  PK AUTO_INCREMENT
project_id           BIGINT UNSIGNED  NOT NULL
user_id              BIGINT UNSIGNED  NOT NULL
website_id           BIGINT 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 UNSIGNED     NOT NULL
search_volume        INT UNSIGNED     NULL
search_intent        VARCHAR(32)      NULL
position             SMALLINT UNSIGNED  NULL
rank_url             VARCHAR(2048)    NULL
prev_position        SMALLINT UNSIGNED  NULL
local_pack_position  TINYINT UNSIGNED  NULL
serp_features_json   TEXT             NULL
etv                  FLOAT            NULL
last_checked_at      DATETIME         NULL
dfs_task_id          VARCHAR(64)      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         NULL
updated_at           DATETIME         NULL
```

**Indices:**
- PRIMARY: `id`
- UNIQUE: `project_id_keyword_hash_device` (project_id, keyword_hash, device)
- INDEX: `website_id_device` (website_id, device) — composite, **not** a standalone `website_id` index
- INDEX: `project_id_is_active` (project_id, is_active) — composite, **not** a standalone `project_id` index
- INDEX: `check_status` (check_status) — single column
- INDEX: `user_id` (user_id) — single column

**Foreign Keys:**
- project_id → wc_rank_projects.id (logical only — no DB-level constraint)
- user_id → wc_users.id (logical only — no DB-level constraint)
- website_id → wc_websites.id (logical only — no DB-level constraint)


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

**Indices:**
- PRIMARY: `id`
- UNIQUE: `tracked_keyword_id_check_date` (tracked_keyword_id, check_date) — one ranking row per keyword per day
- INDEX: `project_id_check_date` (project_id, check_date) — composite, **not** a standalone `project_id` index

**Foreign Keys:**
- tracked_keyword_id → wc_tracked_keywords.id (logical only — no DB-level constraint)
- project_id → wc_rank_projects.id (logical only — no DB-level constraint)

---

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

**Indices:**
- PRIMARY: `id`
- UNIQUE: `project_id_domain` (project_id, domain) — one row per competitor domain per project; **not** a standalone `project_id` index

**Foreign Keys:**
- project_id → wc_rank_projects.id (logical only — no DB-level constraint)
---

## Keyword Research Tables

### `wc_seo_keywords`
Serves two callers, distinguished by `type`:

- **Keywords page** (`App\Models\KeywordModel`) — DataForSEO *ranked keywords*
  for a domain, `type` = `organic` / `paid`. Replaced atomically per
  domain+location+language+type on each fetch.
- **Keyword research** (`App\Libraries\KeywordResearchService`) — keyword
  *ideas*, stored with `type` = `research` and an empty `domain`. The research
  columns below (`country_code`, `search_intent`, `keyword_category`,
  `opportunity_score`, …) exist for this path.

```sql
id                    BIGINT UNSIGNED  PK AUTO_INCREMENT
domain                VARCHAR(255)     NOT NULL          -- '' for research rows
country_code          VARCHAR(2)       NULL
location_code         INT UNSIGNED     NOT NULL
language_code         VARCHAR(20)      NOT NULL
type                  ENUM('organic','paid','research')  NOT NULL  DEFAULT 'organic'
keyword               VARCHAR(500)     NOT NULL
position              INT UNSIGNED     NOT NULL  DEFAULT 0
previous_position     INT UNSIGNED     NULL
position_difference   INT(11)          NULL
absolute_position     INT UNSIGNED     NOT NULL  DEFAULT 0
traffic               DECIMAL(15,4)    NOT NULL  DEFAULT 0.0000
search_volume         BIGINT UNSIGNED  NOT NULL  DEFAULT 0
cpc                   DECIMAL(15,4)    NOT NULL  DEFAULT 0.0000
keyword_difficulty    DECIMAL(8,4)     NOT NULL  DEFAULT 0.0000
competition           DECIMAL(8,4)     NULL
intent                VARCHAR(50)      NULL              -- Keywords page
search_intent         VARCHAR(32)      NULL              -- research path
keyword_category      VARCHAR(32)      NULL
monthly_searches      LONGTEXT         NULL              -- JSON
trend_data            LONGTEXT         NULL              -- JSON
serp_features         TEXT             NULL              -- JSON
source                VARCHAR(32)      NULL
opportunity_score     DECIMAL(5,2)     NOT NULL  DEFAULT 0.00
url                   TEXT             NULL
relative_url          TEXT             NULL
created_at            DATETIME         NOT NULL
updated_at            DATETIME         NOT NULL
```

**Indices:**
- PRIMARY: `id`
- UNIQUE: `uq_seo_keywords` (domain, location_code, language_code, type, keyword(191)) — prefix index on keyword
- INDEX: `idx_domain_location_language_type` (domain, location_code, language_code, type) — composite lookup index for the Keywords page fetch/replace pattern
- INDEX: `idx_position` (position)
- INDEX: `idx_search_volume` (search_volume)
- INDEX: `idx_traffic` (traffic)
- INDEX: `idx_keyword_difficulty` (keyword_difficulty)
- INDEX: `idx_cpc` (cpc)
- INDEX: `idx_wc_seo_keywords_keyword` (keyword(191))
- INDEX: `idx_wc_seo_keywords_country` (country_code)
- INDEX: `idx_wc_seo_keywords_language` (language_code)
- INDEX: `idx_wc_seo_keywords_intent` (search_intent)
- INDEX: `idx_wc_seo_keywords_opportunity` (opportunity_score)

**Notes:**
- The research columns and the `research` enum value were **not** present in the
  old `cms` database — the alter that added them was written but never applied.
  `KeywordResearchService` guards every column access with a `safeFieldMap()`
  check, so on `cms` the research filters silently returned unfiltered results
  and research rows were dropped on insert. They are part of the base table here.

---

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

**Indices:**
- PRIMARY: `id`
- UNIQUE: `idx_keyword_location_lang` (keyword, location_code, language_code) — one cached entry per keyword/location/language combo; **not** a standalone `keyword` index
- INDEX: `idx_user_id` (user_id)
- INDEX: `idx_expires` (expires_at)


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

**Indices:**
- PRIMARY: `id`
- UNIQUE: `recommendation_id` (recommendation_id)
- INDEX: `idx_user_id` (user_id)
- INDEX: `idx_website_id` (website_id)
- INDEX: `idx_keyword` (keyword)
- INDEX: `idx_status` (status)
- INDEX: `idx_created` (created_at)

*(all single-column — no composites here, unlike several earlier tables)*

---

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

**Indices:**
- PRIMARY: `id`
- INDEX: `idx_user_id` (user_id)
- INDEX: `idx_website_id` (website_id)
- INDEX: `idx_user_keyword` (user_id, keyword) — composite
- INDEX: `idx_user_created` (user_id, created_at) — composite

**Foreign Keys:**
- user_id → wc_users.id (logical only — no DB-level constraint)
- website_id → wc_websites.id (logical only — no DB-level constraint)

### `wc_keyword_lists`
Named keyword lists, scoped to a user. Read/written by
`App\Controllers\Api\KeywordListsController`.
```sql
id          BIGINT UNSIGNED  PK AUTO_INCREMENT
user_id     BIGINT UNSIGNED  NOT NULL
name        VARCHAR(160)     NOT NULL
created_at  DATETIME         NULL
updated_at  DATETIME         NULL
```

**Indices:**
- PRIMARY: `id`
- UNIQUE: `uk_keyword_list_user_name` (user_id, name) — one list name per user
- INDEX: `idx_user_id` (user_id)

**Foreign Keys:**
- user_id → wc_users.id (logical only — no DB-level constraint)

---

### `wc_keyword_list_items`
Join table: which keywords belong to which list.
```sql
id          BIGINT UNSIGNED  PK AUTO_INCREMENT
list_id     BIGINT UNSIGNED  NOT NULL
keyword_id  BIGINT UNSIGNED  NOT NULL  COMMENT 'FK to wc_seo_keywords.id'
created_at  DATETIME         NULL
```

**Indices:**
- PRIMARY: `id`
- UNIQUE: `uk_keyword_list_item` (list_id, keyword_id) — a keyword appears once per list
- INDEX: `idx_list_id` (list_id)
- INDEX: `idx_keyword_id` (keyword_id)

**Foreign Keys:**
- list_id → wc_keyword_lists.id (logical only — no DB-level constraint)
- keyword_id → wc_seo_keywords.id (logical only — no DB-level constraint)

**Notes:**
- Neither of these tables existed in the old `cms` database. The controller
  guards every access with `tableExists()`, so the saved-lists feature was
  dormant there; it becomes live on `webcrawlerdb`.
- `DELETE /keyword-lists/{id}` removes the items then the list — there is no
  cascade at the DB level.

---

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

**Indices:**
- PRIMARY: `id`
- UNIQUE: `analysis_id` (analysis_id)
- INDEX: `idx_user_id` (user_id)
- INDEX: `idx_website_id` (website_id)
- INDEX: `idx_status` (status)
- INDEX: `idx_created` (created_at)

*(all single-column)*

**Foreign Keys:**
- user_id → wc_users.id (logical only — no DB-level constraint)
- website_id → wc_websites.id (logical only — no DB-level constraint)

---

### `wc_leads`
Contact and demo-booking leads captured from website forms.
```sql
id             BIGINT UNSIGNED  PK AUTO_INCREMENT
user_id        BIGINT UNSIGNED  NULL
lead_type      ENUM('contact','book_demo')  NULL  DEFAULT 'contact'
name           VARCHAR(150)     NOT NULL
email          VARCHAR(190)     NOT NULL
phone          VARCHAR(32)      NULL
website        VARCHAR(2048)    NULL
customer_type  VARCHAR(50)      NULL
message        TEXT             NULL
demo_given     ENUM('Y','N')    NULL  DEFAULT 'N'
is_paid        ENUM('Y','N')    NULL  DEFAULT 'N'
created_at     DATETIME         NULL
updated_at     DATETIME         NULL
```

**Indices:**
- PRIMARY: `id`
- INDEX: `user_id` (user_id)
- INDEX: `lead_type` (lead_type)
- INDEX: `email` (email)
- INDEX: `demo_given` (demo_given)
- INDEX: `is_paid` (is_paid)
- INDEX: `created_at` (created_at)

*(all single-column)*

**Foreign Keys:**
- user_id → wc_users.id (logical only — no DB-level constraint; nullable so guest/anonymous leads are still recorded)

**Notes:**
- `lead_type`: distinguishes contact-form submissions from demo-booking requests
- `demo_given`: tracks whether a demo has been completed for `book_demo` leads
- `is_paid`: tracks conversion to paying customer

---

## Cache Tables

### `wc_site_overview_cache`
Cached DataForSEO site-overview responses (traffic, domain metrics).
```sql
id             INT UNSIGNED  PK AUTO_INCREMENT
user_id        INT UNSIGNED  NOT NULL
website_id     INT UNSIGNED  NOT NULL
country_id     INT 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      NULL
fetched_at     DATETIME      NULL
expires_at     DATETIME      NULL
created_at     DATETIME      NULL
updated_at     DATETIME      NULL
```

**Indices:**
- PRIMARY: `id`
- UNIQUE: `uq_so_cache` (website_id, country_id, language_code, section) — one cached entry per site/country/language/section combo
- INDEX: `idx_so_user` (user_id)
- INDEX: `idx_so_expires` (expires_at)

**Foreign Keys:**
- user_id → wc_users.id (logical only — no DB-level constraint)
- website_id → wc_websites.id (logical only — no DB-level constraint)
- country_id → wc_countries.id (logical only — no DB-level constraint)

---

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

**Indices:**
- PRIMARY: `id`
- INDEX: `idx_user_status` (user_id, status, deleted_at) — composite, **not** a standalone `user_id` index
- INDEX: `idx_website` (website_id)
- INDEX: `idx_created` (user_id, created_at) — composite
- INDEX: `idx_keyword` (primary_keyword)

**Foreign Keys:**
- user_id → wc_users.id (logical only — no DB-level constraint)
- website_id → wc_websites.id (logical only — no DB-level constraint)

---

## System / Auth Tables

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

**Indices:**
- PRIMARY: `id`
- UNIQUE: `uk_country_iso2` (iso2)
- UNIQUE: `uk_country_iso3` (iso3)
- INDEX: `idx_wc_countries_dfs_loc` (dfs_location_code)

*(all single-column)*

---

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

**Indices:**
- PRIMARY: `id`
- INDEX: `idx_user_id` (user_id)
- INDEX: `idx_audit_id` (audit_id)
- INDEX: `idx_endpoint` (endpoint)
- INDEX: `idx_created_at` (created_at)

*(all single-column)*

**Foreign Keys:**
- user_id → wc_users.id (logical only — no DB-level constraint)
- audit_id → wc_audits.id (logical only — no DB-level constraint)


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

**Indices:**
- PRIMARY: `id`
- UNIQUE: `uk_module_code` (code)
- INDEX: `idx_module_parent` (parent_id)
- INDEX: `idx_module_parent_order` (parent_id, sort_order) — composite
- INDEX: `idx_module_status` (status)
- INDEX: `idx_module_deleted_at` (deleted_at)

---

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

**Indices:**
- PRIMARY: `id`
- UNIQUE: `uk_module_plan` (module_id, plan_code) — one permission row per module/plan pair; **not** a standalone `module_id` or `plan_code` index
- INDEX: `idx_plan_code` (plan_code)
- INDEX: `idx_module_id` (module_id)

**Foreign Keys:**
- module_id → wc_modules.id (logical only — no DB-level constraint)

---

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

**Indices:**
- PRIMARY: `id`
- INDEX: `user_id_purpose` (user_id, purpose) — composite; **not** a standalone `user_id` index

**Foreign Keys:**
- user_id → wc_users.id (logical only — no DB-level constraint)


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

**Indices:**
- PRIMARY: `id`
- INDEX: `token_hash` (token_hash) — **not unique**, despite being a lookup token

**Foreign Keys:**
- user_id → wc_users.id (logical only — no DB-level constraint)

---

### `wc_invites_log`
```sql
id                INT UNSIGNED  PK AUTO_INCREMENT
invited_user_id   INT UNSIGNED  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 UNSIGNED  NOT NULL
is_accepted       TINYINT(1)    NOT NULL  DEFAULT 0
sent_at           DATETIME      NOT NULL
accepted_at       DATETIME      NULL
message           TEXT          NULL
created_at        DATETIME      NULL
updated_at        DATETIME      NULL
```

**Indices:**
- PRIMARY: `id`
- UNIQUE: `uk_invite_token` (token_hash)
- INDEX: `idx_invite_email` (email)
- INDEX: `idx_invite_by` (invited_by)
- INDEX: `idx_invite_user` (invited_user_id)
- INDEX: `idx_invite_accepted` (is_accepted)

*(all single-column)*

**Foreign Keys:**
- invited_user_id → wc_users.id (logical only — no DB-level constraint)
- invited_by → wc_users.id (logical only — no DB-level constraint)

---

### `wc_ai_page_analyses`
```sql
id              INT UNSIGNED  PK AUTO_INCREMENT
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)    NOT NULL  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
```

**Indices:**
- PRIMARY: `id`
- INDEX: `user_id_website_id_created_at` (user_id, website_id, created_at) — composite
- INDEX: `audit_page_id` (audit_page_id)

**Foreign Keys:**
- user_id → wc_users.id (logical only — no DB-level constraint)
- website_id → wc_websites.id (logical only — no DB-level constraint)
- audit_id → wc_audits.id (logical only — no DB-level constraint)
- audit_page_id → wc_audit_pages.id (logical only — no DB-level constraint)

---

### `wc_ai_page_suggestions`
Individual proposed changes belonging to an AI page analysis.
```sql
id             INT UNSIGNED  PK AUTO_INCREMENT
analysis_id    INT UNSIGNED  NOT NULL
field          VARCHAR(32)   NULL
type_label     VARCHAR(64)   NOT NULL
impact         VARCHAR(16)   NOT NULL  DEFAULT 'medium'
confidence     TINYINT UNSIGNED  NOT NULL  DEFAULT 0
reason         TEXT          NULL
evidence       TEXT          NULL
proposed_text  TEXT          NULL
status         VARCHAR(16)   NOT NULL  DEFAULT 'pending'
sort_order     SMALLINT UNSIGNED  NOT NULL  DEFAULT 0
created_at     DATETIME      NULL
updated_at     DATETIME      NULL
```

**Indices:**
- PRIMARY: `id`
- INDEX: `analysis_id_status` (analysis_id, status) — composite; **not** a standalone `analysis_id` index
- INDEX: `analysis_id_sort_order` (analysis_id, sort_order) — composite

**Foreign Keys:**
- analysis_id → wc_ai_page_analyses.id (logical only — no DB-level constraint)

---

### `wc_ai_page_analysis_events`
Audit trail of actions taken on an AI page analysis (and optionally a specific suggestion).
```sql
id             INT UNSIGNED  PK AUTO_INCREMENT
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
```

**Indices:**
- PRIMARY: `id`
- INDEX: `analysis_id_created_at` (analysis_id, created_at) — composite; **not** a standalone `analysis_id` index

**Foreign Keys:**
- analysis_id → wc_ai_page_analyses.id (logical only — no DB-level constraint)
- user_id → wc_users.id (logical only — no DB-level constraint)
- suggestion_id → wc_ai_page_suggestions.id (logical only — no DB-level constraint)

### `wc_subscription`
```sql
id                        BIGINT UNSIGNED  PK AUTO_INCREMENT
user_id                   INT(11)          NOT NULL  COMMENT 'id from wc_users table'
plan_id                   INT(11)          NOT NULL  COMMENT 'subscription_plan table id'
payment_type              TINYINT(1)       NULL  DEFAULT 0  COMMENT '5=>Sticky'
subscribe_id              VARCHAR(40)      NULL  COMMENT 'subscribe id given by Sticky'
ref_id                    VARCHAR(255)     NOT NULL  COMMENT 'ref id of unique subscriber'
customer_profile_id       VARCHAR(40)      NOT NULL  COMMENT 'customer profile id given by Sticky'
status                    TINYINT(4)       NOT NULL  DEFAULT 0  COMMENT '0-Inactive,1-Active,2-Subscription end'
pg_response               LONGTEXT         NULL  COMMENT 'raw response of last request'
subscription_start_date   DATETIME         NULL
subscription_end_date     DATETIME         NULL
created_at                TIMESTAMP        NOT NULL  DEFAULT CURRENT_TIMESTAMP
updated_at                TIMESTAMP        NOT NULL  DEFAULT CURRENT_TIMESTAMP  ON UPDATE CURRENT_TIMESTAMP
```

**Indices:**
- PRIMARY: `id`
- INDEX: `idx_userid_status` (user_id, status) — composite; **not** a standalone index on either column

**Foreign Keys:**
- user_id → wc_users.id (logical only — no DB-level constraint)
- plan_id → wc_subscription_plans.plan_id (logical only — no DB-level constraint)

**Collation note:** `latin1_swedish_ci` — differs from the `utf8mb4_unicode_ci` used across most other tables in this schema.

---

### `wc_subscription_history`
```sql
id              BIGINT UNSIGNED  PK AUTO_INCREMENT
date            TIMESTAMP        NOT NULL  DEFAULT CURRENT_TIMESTAMP
user_id         INT(11)          NOT NULL
transaction_id  VARCHAR(40)      NULL
subscribe_id    VARCHAR(40)      NULL
type            VARCHAR(255)     NOT NULL  COMMENT 'Trial/Recurring/Refund'
billing_amount  FLOAT            NOT NULL
domain          VARCHAR(255)     NOT NULL
extra           MEDIUMTEXT       NOT NULL
```

**Indices:**
- PRIMARY: `id`
- INDEX: `idx_user_domain` (user_id, domain) — composite; **not** a standalone index on either column

**Foreign Keys:**
- user_id → wc_users.id (logical only — no DB-level constraint)

**Collation note:** `latin1_swedish_ci`, same as `wc_subscription`.

---

### `wc_subscription_plans`
```sql
plan_id       INT(11)      PK AUTO_INCREMENT
plan_name     VARCHAR(255)  NOT NULL
plan_amount   SMALLINT(6)   NOT NULL  COMMENT 'integer number like 1,2,3 etc'
plan_period   VARCHAR(255)  NOT NULL  COMMENT 'like days/months/year etc'
plan_price    FLOAT         NOT NULL  COMMENT 'plan price in $'
trail_amount  SMALLINT(6)   NOT NULL  COMMENT 'integer number like 1,2,3 etc'
trail_period  VARCHAR(255)  NOT NULL  COMMENT 'like day/month/year etc'
trail_price   FLOAT         NOT NULL  DEFAULT 0  COMMENT 'trail price in $'
status        TINYINT(4)    NOT NULL  COMMENT '0-inactive,1-active'
plan_domain   VARCHAR(255)  NOT NULL  COMMENT 'domain name for which web plan exist'
created_at    TIMESTAMP     NOT NULL  DEFAULT CURRENT_TIMESTAMP
updated_at    TIMESTAMP     NOT NULL  DEFAULT CURRENT_TIMESTAMP  ON UPDATE CURRENT_TIMESTAMP
```

**Indices:**
- PRIMARY: `plan_id`

---

## Migration Reference

The `webcrawlerdb` schema is built entirely by the 14 migrations below. They are
**consolidated first-run migrations**: the historical create+alter chain from the
`cms` era was collapsed into one create per table, so a fresh database is one
`php spark migrate` away. There is nothing to replay from `cms`.

| Version | Class | Tables |
|---|---|---|
| 2026-08-25-000001 | `CreateCoreReferenceTables` | `wc_countries`, `wc_users` |
| 2026-08-25-000002 | `CreateWebsitesTable` | `wc_websites` |
| 2026-08-25-000003 | `CreateAuditTables` | `wc_audits`, `wc_audit_pages`, `wc_audit_issues` |
| 2026-08-25-000004 | `CreateAutomationQueueTable` | `wc_automation_queue` |
| 2026-08-25-000005 | `CreatePixelTables` | `wc_pixel_views`, `wc_pixel_events`, `wc_pixel_vitals` |
| 2026-08-25-000006 | `CreateWebsiteIntegrationsTable` | `wc_website_integrations` |
| 2026-08-25-000007 | `CreateRankTrackingTables` | `wc_rank_projects`, `wc_tracked_keywords`, `wc_keyword_rankings`, `wc_ranking_competitors` |
| 2026-08-25-000008 | `CreateKeywordTables` | `wc_seo_keywords`, `wc_keyword_research_cache`, `wc_keyword_recommendations`, `wc_user_saved_keywords`, `wc_gap_analysis_results`, `wc_keyword_lists`, `wc_keyword_list_items` |
| 2026-08-25-000009 | `CreateCacheAndAiContentTables` | `wc_site_overview_cache`, `wc_ai_content` |
| 2026-08-25-000010 | `CreateAiPageAnalyzerTables` | `wc_ai_page_analyses`, `wc_ai_page_suggestions`, `wc_ai_page_analysis_events` |
| 2026-08-25-000011 | `CreateSystemTables` | `wc_modules`, `wc_module_permission_mapping`, `wc_dataforseo_logs`, `wc_otp_codes`, `wc_password_resets`, `wc_invites_log` |
| 2026-08-25-000012 | `CreateSubscriptionTables` | `wc_subscription_plans`, `wc_subscription`, `wc_subscription_history` |
| 2026-08-25-000013 | `CreateLeadsTable` | `wc_leads` |
| 2026-08-25-000014 | `CreateCancelSubscriptionsTable` | `wc_cancel_subscriptions` |

Order matters in two places: `wc_websites` must exist before
`wc_automation_queue` (its foreign key), and `wc_users` / `wc_countries` come
first as everything keys off them.

Each migration issues raw `CREATE TABLE` rather than using `Forge`, because the
schema needs prefix indexes (`url(255)`, `keyword(191)`), column comments,
`ON UPDATE CURRENT_TIMESTAMP` and explicit `ROW_FORMAT` — none of which Forge
expresses. `down()` drops the tables in reverse dependency order and has been
verified to leave the database empty.

### What changed from `cms`

Consolidating exposed real drift. For the record:

- **19 of 35 tables had no create-migration at all** (`wc_users`, `wc_websites`,
  `wc_audits`, `wc_countries`, `wc_modules`, `wc_seo_keywords`,
  `wc_subscription*`, …) — they had only ever been created by hand.
- **5 applied migrations had no file in the repo**: `CreateRankTrackingTables`,
  `CreateWcAiContentTable`, `CreateAiPageAnalyzerTables`,
  `AddRoleAndInvitedByToUsers`, `AddScheduleFieldsToAutomationQueue`. Their
  tables were reverse-engineered from the live schema.
- **7 committed migrations had never been applied**, including two that mattered:
  `CreateKeywordListsTables` (its tables did not exist) and
  `EnsureSeoKeywordsResearchColumns` (its columns did not exist).
- `cms.migrations` was **shared with other MSA apps** (`CreateNewsWatchlist`,
  group `cms`), so version numbers could collide across products.
- **Charset was inconsistent**: `utf8mb3`, `utf8mb4_unicode_ci`,
  `utf8mb4_general_ci` and `latin1_swedish_ci` (the three `wc_subscription*`
  tables) all coexisted. Everything is `utf8mb4_unicode_ci` here.

- **`cms` kept moving during the migration.** Two changes landed after the first
  pass and were folded in: the `wc_cancel_subscriptions` table (created
  2026-08-25) and the `wc_subscription.card_type` / `card_last4` / `card_expiry`
  columns. Both were caught by the dev-dump generator, which refuses to run when
  `cms` has a table it does not know about and comments any column it cannot
  copy. **If more subscription/billing work lands, re-check before release** —
  `git log` on the feature branches is the place to look, since the code for both
  of these is not on `release/develop` yet.

Verified: every one of the 36 `cms` tables is reproduced column-for-column and
index-for-index. The only intentional differences are the two `wc_keyword_lists*`
tables, the `wc_seo_keywords` research columns, and two extra indexes on
`wc_cancel_subscriptions` (`user_id`, `subscribe_id`) that `cms` lacks — all
documented above.

---

## Sysadmin Runbook — Staging & Production

Deploying WebCrawlers to a new environment. **This is the first migration ever
run for this project**, so on a new server there is nothing to replay and nothing
to copy out of the old `cms` database — steps 1–5 build the schema from zero.

### 0. Before you start

| Requirement | Value |
|---|---|
| PHP | 8.2 (`/usr/bin/php8.2`) — `composer.json` requires `^8.2` |
| PHP extensions | `intl`, `mbstring`, `mysqli` |
| MySQL | 5.7.44 or newer, InnoDB |
| Database | already provisioned and empty; its default charset does not matter (see step 1) |
| DB privileges | `CREATE`, `ALTER`, `INDEX`, `DROP`, `INSERT`, `SELECT`, `REFERENCES` **on the existing schema only** — no `CREATE DATABASE` needed |
| Repo | `https://bit.admedia.com/scm/ad/webcrawlers_latest.com.git` |
| Writable | `writable/` must be writable by the web/CLI user |

Run every command from the **project root** (the directory holding `spark`).

> **⚠ Read this before running `migrate` — silent-failure risk**
>
> `spark` does **not** go through `public/index.php`, so the host/path detection
> that picks the environment never runs on the CLI. CodeIgniter falls back to
> `production`, which you can confirm with `php8.2 spark env`.
>
> That matters because `app/Config/Database.php` sets
> `'DBDebug' => (ENVIRONMENT !== 'production')`. On the CLI that evaluates to
> **`false`**, and with `DBDebug` off a failing DDL statement does **not** throw —
> `query()` just returns `false`. `MigrationRunner` then records the migration as
> applied anyway. **A migration can report "complete" while its tables were never
> created.**
>
> Verified on this codebase:
>
> ```
> ENVIRONMENT = production
> config DBDebug = false
> DBDebug=true   threw CodeIgniter\Database\Exceptions\DatabaseException
> DBDebug=false  NO EXCEPTION — query() returned false
> ```
>
> **So always prefix migration and seed commands with
> `CI_ENVIRONMENT=development`**, as every command below does. It turns `DBDebug`
> on so real failures abort loudly. It affects only that one CLI process — it does
> not touch the running web app or its environment. Then still run the
> step 6 verification.

### 1. Confirm the database (you do not need to create or alter it)

The database is provisioned by the DBA and **its default charset does not matter**
— you do not need permission to change it.

Every table sets its own charset explicitly, so nothing inherits the database
default:

- The 39 application tables are created by raw SQL ending
  `... DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC`.
- CodeIgniter's own `migrations` table gets `DEFAULT CHARACTER SET` / `COLLATE`
  from the **connection config**, not the schema default — see
  `_createTableAttributes()` in `system/Database/MySQLi/Forge.php`, which appends
  `$this->db->charset` and `$this->db->DBCollat`. Those are set to `utf8mb4` /
  `utf8mb4_unicode_ci` in `app/Config/Database.php`.

A MySQL database's default charset applies **only** to tables created without an
explicit one. Proof from this very deployment: the old `cms` database is
`latin1 / latin1_swedish_ci` at the schema level, yet holds 11
`utf8mb4_unicode_ci` tables, and `cms.wc_countries.flag_emoji` stores correct
four-byte emoji (`🇦🇫` = `0xF09F87A6F09F87AB`). The utf8mb4 reference data in this
schema was read straight out of that latin1-defaulted database.

So just confirm you can reach the schema and that it is empty:

```sql
SELECT schema_name, default_character_set_name
  FROM information_schema.schemata WHERE schema_name = 'webcrawlerdb';

-- Should be 0 on a first deploy
SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'webcrawlerdb';
```

What **does** matter is the **client connection** charset, which the app already
sets (`'charset' => 'utf8mb4'` in `app/Config/Database.php`). Keep it that way,
and pass `--default-character-set=utf8mb4` when using the `mysql` CLI, or emoji
will be mangled in transit regardless of how the tables are defined.

If you are creating the schema yourself and *do* have the rights, utf8mb4 is
still the tidier default — but it changes nothing about the result:

```sql
CREATE DATABASE webcrawlerdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
```

`utf8mb4_0900_ai_ci` is MySQL 8.0-only and **not** available on 5.7.

### 2. Point the app at it

`app/Config/Database.php` holds the committed defaults. **Do not edit it per
environment** — override in `.env` at the project root:

```ini
CI_ENVIRONMENT = production

database.default.hostname = 127.0.0.1
database.default.database = webcrawlerdb
database.default.username = <staging-or-prod-user>
database.default.password = <staging-or-prod-password>
database.default.DBDriver = MySQLi
database.default.port     = 3306
```

Setting `CI_ENVIRONMENT` in `.env` also fixes the CLI-defaults-to-production
issue for normal operation. Confirm what the app resolves:

```bash
php8.2 spark env
```

> `.env` is **not** in this repo's `.gitignore` — only `.env.shell` is. Make sure
> a real `.env` containing production credentials is never committed.

Install dependencies (production omits dev packages):

```bash
composer install --no-dev --optimize-autoloader
```

### 3. Preflight — confirm connectivity and what will run

```bash
# Which database am I actually talking to?
CI_ENVIRONMENT=development php8.2 spark db:table --show

# 14 migrations, all with an empty "Migrated On" on a fresh database
CI_ENVIRONMENT=development php8.2 spark migrate:status
```

If `migrate:status` errors here, the credentials or grants are wrong — fix that
before going further. Do not proceed if the "Migrated On" column already has
values on a database you believe to be new.

### 4. Run the migrations

```bash
CI_ENVIRONMENT=development php8.2 spark migrate
```

Expected — 14 lines, then `Migrations complete.`:

```
	Running: (App) 2026-08-25-000001_App\Database\Migrations\CreateCoreReferenceTables
	Running: (App) 2026-08-25-000002_App\Database\Migrations\CreateWebsitesTable
	...
	Running: (App) 2026-08-25-000013_App\Database\Migrations\CreateLeadsTable
Migrations complete.
```

Order is load-bearing in two places: `wc_websites` must exist before
`wc_automation_queue` (its foreign key), and `wc_users` / `wc_countries` come
first because everything keys off them. Never run these out of order or
individually.

### 5. Load the seed data

```bash
CI_ENVIRONMENT=development php8.2 spark db:seed DatabaseSeeder
```

This runs four seeders — countries, modules, module permissions, plans, then the
owner accounts. All use `INSERT IGNORE`, so **the command is safe to re-run**: it
will not duplicate rows, reset a password, or overwrite reference data.

> **Dev boxes only:** if you want realistic data instead of empty tables —
> real websites, audits, crawl pages, keywords — load the filtered `cms` snapshot
> in `dev-data/` **after** seeding. It leaves the seeded reference data and the
> three owner accounts alone, and excludes inactive/orphaned sites, expired OTP
> and reset tokens, and the API logs. See `dev-data/README.md`. Never run it on
> staging or production.

### Seeded accounts (created by step 5)

`UserSeeder` creates the three owner logins the team signs in with:

| Email | Display name | role / is_owner |
|---|---|---|
| `koushik.basu+wc@admedia.com` | Koushik Basu | `owner` / `1` |
| `mehak.sharma+wc@admedia.com` | Mehak Sharma | `owner` / `1` |
| `manoj.bagwari+wc@admedia.com` | Manoj Bagwari | `owner` / `1` |

Each reuses the bcrypt password hash of that operator's existing non-`+wc`
`@admedia.com` account, so **the password is unchanged from the account without
the `+wc` suffix**. Hashes are `$2y$10$` bcrypt, consumed by
`password_verify()` in `App\Controllers\Auth`, so they port across databases
untouched. Display names are set explicitly in the seeder rather than copied
from the source rows, one of which stored an email address in `full_name`.

Two details that are deliberate, not oversights:

- `email_verified_at` is set at seed time. `Auth::login()` rejects any account
  with a null verification timestamp, so without it these users could not sign
  in until they completed an OTP round-trip.
- Both `role = 'owner'` and `is_owner = 1` are set.
  `TeamAccess::role()` reads `role` first but falls back to `is_owner`, so the
  two must agree or the effective role depends on which code path asks.

Because the seeder uses `INSERT IGNORE` on the unique `email` key, re-running it
will **not** reset a password that has since been changed in the app. The same
applies to `full_name`: editing a name in the seeder does not propagate to a
database that already has the row, so change it in both places (or update the
row directly) when correcting a display name.

### 6. Verify — do not skip this

Because of the `DBDebug` behaviour above, treat verification as part of the
deploy, not an optional extra.

```bash
CI_ENVIRONMENT=development php8.2 spark migrate:status   # 14 rows, all with a date + batch 1
```

```sql
-- Expect exactly 39
SELECT COUNT(*) AS wc_tables FROM information_schema.tables
 WHERE table_schema = 'webcrawlerdb' AND table_name LIKE 'wc\_%';

-- Expect 197 / 46 / 94 / 4 / 3
SELECT (SELECT COUNT(*) FROM wc_countries)                 AS countries,
       (SELECT COUNT(*) FROM wc_modules)                   AS modules,
       (SELECT COUNT(*) FROM wc_module_permission_mapping) AS module_perms,
       (SELECT COUNT(*) FROM wc_subscription_plans)        AS plans,
       (SELECT COUNT(*) FROM wc_users)                     AS owners;

-- All three owners: active, verified, role=owner
SELECT email, full_name, role, is_owner, status,
       email_verified_at IS NOT NULL AS verified
  FROM wc_users ORDER BY id;

-- Must return ZERO rows — no table may deviate from the collation
SELECT table_name, table_collation FROM information_schema.tables
 WHERE table_schema = 'webcrawlerdb' AND table_collation <> 'utf8mb4_unicode_ci';

-- Must return ZERO rows — nor any column
SELECT table_name, column_name, collation_name FROM information_schema.columns
 WHERE table_schema = 'webcrawlerdb'
   AND collation_name IS NOT NULL AND collation_name <> 'utf8mb4_unicode_ci';

-- utf8mb4 round-trip: must print the Afghan flag, not '??'
SELECT iso2, flag_emoji FROM wc_countries WHERE iso2 = 'AF';

-- The one foreign key that should exist
SELECT constraint_name, table_name, referenced_table_name
  FROM information_schema.key_column_usage
 WHERE table_schema = 'webcrawlerdb' AND referenced_table_name IS NOT NULL;
```

Finally, confirm the app can sign in with one of the seeded owner accounts.

### 7. Staging vs production

The procedure is identical; only these differ.

| | Staging | Production |
|---|---|---|
| `CI_ENVIRONMENT` in `.env` | `development` | `production` |
| Detected env if `.env` is absent | `development` for `staging.webcrawlers.com` (see `public/index.php`) | `production` |
| Error display | on | off |
| `DBDebug` (web) | on | off |
| Reseeding / resetting | permitted | **only with a backup and sign-off** |
| Composer | `composer install` | `composer install --no-dev --optimize-autoloader` |

Do a full staging run first and diff the verification output against production's.
The two schemas should be identical.

### 8. Resetting an environment

**Never `DROP DATABASE`.** To rebuild a **staging** environment, truncate instead
— this also clears the migration history so `migrate` will re-run from scratch:

```sql
SET FOREIGN_KEY_CHECKS = 0;
-- wc_automation_queue has an FK to wc_websites, hence the flag above
TRUNCATE TABLE wc_automation_queue;
-- ... truncate the remaining wc_* tables ...
TRUNCATE TABLE migrations;
SET FOREIGN_KEY_CHECKS = 1;
```

Then re-run steps 4–6. To generate the statements rather than typing 37 of them:

```sql
SELECT CONCAT('TRUNCATE TABLE `', table_name, '`;')
  FROM information_schema.tables
 WHERE table_schema = 'webcrawlerdb'
 ORDER BY table_name;
```

On **production**, take a backup first:

```bash
mysqldump --single-transaction --routines --default-character-set=utf8mb4 \
  -u <user> -p webcrawlerdb > webcrawlerdb-$(date +%F-%H%M).sql
```

`spark migrate:rollback` runs each migration's `down()`, dropping tables in
reverse dependency order. It has been verified to leave the schema empty. It is
**destructive** — never run it on production to "retry" something.

### 9. Troubleshooting

| Symptom | Cause | Fix |
|---|---|---|
| `Migrations complete.` but tables are missing | `DBDebug` off swallowed a DDL error | Re-run with `CI_ENVIRONMENT=development`; clear the bogus `migrations` rows first |
| `Unable to connect to the database` | wrong `.env` values, or user lacks grants | Check with `spark db:table --show` |
| `Specified key was too long` | database or table not `ROW_FORMAT=DYNAMIC` / not utf8mb4 | Confirm step 1; four unique indexes exceed the 767-byte `COMPACT` limit |
| `wc_countries.flag_emoji` shows `?` | **client** connection charset is not utf8mb4 (the *schema* default is irrelevant) | Confirm `'charset' => 'utf8mb4'` in `app/Config/Database.php`; for the `mysql` CLI add `--default-character-set=utf8mb4` |
| `Illegal mix of collations` | a table or column drifted from `utf8mb4_unicode_ci` | Run the two collation queries in step 6 |
| `Class ... not found` on migrate | dependencies missing | `composer install` (then `composer dump-autoload`) |
| Seeder appears to do nothing | already-present rows, `INSERT IGNORE` | Expected — verify counts per step 6 |
| Owner cannot sign in | `email_verified_at` is null | `Auth::login()` requires it; re-run `UserSeeder` on an empty `wc_users`, or set it manually |

### 10. After the deploy

The automation queue is driven by cron, not by the web app. Install it once the
schema is live (see `webcrawlers_cron.sh` and `AUTOMATION_PHASE_2_CRON_SETUP.md`):

```cron
*/5 * * * * /path/to/webcrawlers_cron.sh
```

Point the script's `cd` at the deployed path and confirm
`writable/logs/automation_queue.log` is being written.

---

## 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)
  └─ Google APIs (Search Console, GA4, Business Profile OAuth connections)
```

**Rationale:** DataForSEO provides the core SEO automation data for technical audit, keyword research, and rank tracking. Google integrations add first-party Search Console, GA4, and Business Profile connections for accounts that authorize OAuth access.

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

---

## Schema Notes

**Last Updated:** 2026-08-25  
**Version:** 2.0 — first release on `webcrawlerdb`

**This revision:**
- Database moved from the shared `cms` schema to a dedicated `webcrawlerdb`
- Migration history consolidated into 13 first-run migrations covering all 37 tables
- Reference data moved into seeders (`CountrySeeder`, `ModuleSeeder`, `SubscriptionPlanSeeder`)
- `UserSeeder` adds the three `+wc` owner accounts, reusing each operator's existing name and password hash
- Collation standardised to `utf8mb4_unicode_ci` across every table and column, set explicitly per table so it does not depend on the database default
- `wc_keyword_lists` / `wc_keyword_list_items` created for the first time
- `wc_seo_keywords` gained its research columns and the `research` type value

**Carried over from the `cms` era:**
- `wc_users.role` (`owner` / `admin` / `member`) and `wc_users.invited_by` for team invites and plan scoping
- `wc_automation_queue` for SEO automation job queueing, with `frequency` / `start_time` scheduling fields
- `last_automation_run` on `wc_websites` for rate-limiting
- `wc_website_integrations` stores GSC / GA4 / GBP OAuth metadata and encrypted token payloads

---

## Operational Notes

Step-by-step deployment lives in
[Sysadmin Runbook — Staging & Production](#sysadmin-runbook--staging--production).
These are the standing facts worth knowing when operating the database.

1. **`cms` is still in use.** The Blog controller reads article content from it
   via the legacy `\cms_model`, on a separate connection using the
   `MSACOMMON_DB_*_CMS` constants. **Do not decommission `cms`** as part of this
   move. WebCrawlers' own tables are entirely in `webcrawlerdb`.
2. **Never `DROP DATABASE`, and you never need to `ALTER` it either.** The schema
   is provisioned externally; every table sets its own charset, so the database
   default is irrelevant. Truncate to reset, and only on staging — see
   [Resetting an environment](#8-resetting-an-environment).
3. **The CLI defaults to `production`, which turns `DBDebug` off** and lets failed
   DDL pass silently. Always prefix migration and seed commands with
   `CI_ENVIRONMENT=development`, and always run the step 6 verification. This is
   the single most important thing on this page.
4. **`ROW_FORMAT=DYNAMIC` is required, not cosmetic.** Four unique indexes exceed
   the 767-byte limit that applies under a `COMPACT` default —
   `wc_seo_keywords.uq_seo_keywords` is ~1,869 bytes. The migrations set it
   explicitly on every table so the schema does not depend on
   `innodb_default_row_format`.
5. **Referential integrity is mostly in application code.** There is exactly one
   DB-level foreign key (`wc_automation_queue.website_id → wc_websites.id`,
   `ON DELETE CASCADE`). Deleting a user or website will **not** cascade — the
   model layer must clean up children. Budget for orphan rows when writing
   ad-hoc deletes.
6. **Credentials belong in `.env`**, not in `app/Config/Database.php`. Note that
   `.env` is not currently in `.gitignore` — only `.env.shell` is.
7. **Seeders are re-runnable** (`INSERT IGNORE`) and will not clobber reference
   data or reset a changed password. The flip side: editing a name or password in
   a seeder does **not** propagate to a database that already has the row.
8. **Indexes to leave alone.** The composite `(website_id, created_at)` indexes
   carry the time-series dashboard queries, and `idx_ap_fingerprint` on
   `wc_audit_pages` backs the crawl-diff comparison. Both matter under load.
9. **Growth to watch.** `wc_audit_pages`, `wc_dataforseo_logs` and
   `wc_seo_keywords` are the tables that grow without bound — the logs table had
   3,565 rows against only 84 audits on the dev database. `wc_dataforseo_logs`
   stores full request/response JSON in `LONGTEXT` and is the first candidate for
   a retention policy.
10. **Caches are safe to clear.** `wc_keyword_research_cache` and
    `wc_site_overview_cache` are TTL-based (`expires_at`) and can be pruned
    without data loss; the app refetches from DataForSEO.

---

**Created**: 2026-08-12  
**Migrated to `webcrawlerdb`**: 2026-08-25  
**Reviewed By**: Development Team