# dev-data — local development data

> **DEV / LOCAL ONLY.** Nothing in this folder may be run against staging or
> production. Those environments are populated by the seeders:
> `CI_ENVIRONMENT=development php8.2 spark db:seed DatabaseSeeder`

Copies the *useful* working data out of the legacy `cms` database into
`webcrawlerdb`, so a developer gets a realistic environment — real websites,
audits, crawl pages, keywords, rank projects — instead of empty tables.

| File | Purpose |
|---|---|
| `generate_dev_dump.php` | Regenerates the dump from a live `cms`. **Source of truth.** |
| `webcrawlerdb_dev_data.sql` | ~21 MB filtered, data-only snapshot. Stale the moment it is taken. |

## Loading it

The dump does **not** stand alone. It contains no `CREATE TABLE` (the schema
belongs to the migrations) and no reference data (the seeders own that), so both
steps below are prerequisites, not suggestions:

```bash
CI_ENVIRONMENT=development php8.2 spark migrate
CI_ENVIRONMENT=development php8.2 spark db:seed DatabaseSeeder
mysql --default-character-set=utf8mb4 -u dev -p webcrawlerdb < dev-data/webcrawlerdb_dev_data.sql
```

`--default-character-set=utf8mb4` is not optional — without it the emoji in
`wc_countries.flag_emoji` are mangled in transit.

Loading twice is safe. The whole file runs in one transaction with
`FOREIGN_KEY_CHECKS = 0`: either all of it lands or none of it does.

## What it does and does not touch

**`wc_users` is the one table that is neither truncated nor skipped.** It loads
with `INSERT IGNORE`, because it pulls double duty: the copied websites, leads and
subscriptions belong to `cms` users 15–23 and no seeder provides those, while the
three seeded `+wc` owner accounts at ids 1–3 must survive. Not truncating gets
both.

Everything else is `TRUNCATE`d before its `INSERT`s, so the result is repeatable.

**Skipped entirely:**

| Table | Why |
|---|---|
| `wc_countries` | Seeded by `CountrySeeder` (197 rows) |
| `wc_modules` | Seeded by `ModuleSeeder` (46 rows) |
| `wc_module_permission_mapping` | Seeded by `ModuleSeeder` (94 rows) |
| `wc_subscription_plans` | Seeded by `SubscriptionPlanSeeder` (4 rows) |
| `wc_dataforseo_logs` | ~292 MB of the ~327 MB in `cms` — raw API request/response JSON, append-only, never read by the app. Excluding it is what keeps this dump at 21 MB. Query `cms` directly if you need past API traffic. |
| `wc_otp_codes` | Single-use sign-in codes, all long expired |
| `wc_password_resets` | Single-use reset tokens, all consumed or expired |

The four reference tables are skipped on purpose. The seeders were generated from
those exact `cms` rows, so copying them here would be an `INSERT IGNORE` no-op on
any seeded database — and a second, silently-diverging source of truth the moment
someone edits a seeder. The seeders own reference data; this dump owns the
per-tenant working data. That split is also why seeding is a hard prerequisite:
`wc_websites.country_id` and `wc_subscription.plan_id` resolve against
seeder-provided rows.

**Websites excluded**, along with every audit, crawl page, issue, pixel row, rank
project, AI draft and cache entry belonging to them — 7 of 18:

| Site | Reason |
|---|---|
| `test.com`, `admedia.com`, `example.com`, `google.com`, `webcrawlers.in`, `test2.webcrawlers.com` | `status = inactive` |
| `webcrawlers.com` (id 14) | Orphaned — owner user no longer exists in `cms` |

That orphan is the one to understand. In `cms` it sits on `user_id = 1`, and user
1 does not exist there. But in `webcrawlerdb` id 1 **is** the seeded Koushik owner
account — so copying the row would silently hand a dead test site to a real user.

Soft-deleted `wc_ai_content` drafts (`deleted_at IS NOT NULL`) are dropped too.

Net effect: **9,467 rows** instead of the 11,326 a full mirror would carry, and
**zero orphans** — a plain `mysqldump` would bring 27 rows pointing at parents
that no longer exist.

## Regenerating

```bash
php8.2 dev-data/generate_dev_dump.php
```

Override the connection if you are not on the shared dev box:

```bash
DB_HOST=... DB_USER=... DB_PASS=... SRC_DB=cms DST_DB=webcrawlerdb \
  php8.2 dev-data/generate_dev_dump.php
```

Prefer regenerating over hand-editing the SQL. `cms` is still being written to by
the team — row counts moved twice while this was first being built.

To change what gets copied, edit the `$policy` map in the generator. Each table
gets `skip`, `ignore` (no truncate, `INSERT IGNORE`) or `replace` (truncate +
insert), plus an optional row filter. The filters derive from a computed
"keep set" of live website ids rather than hardcoded ids, so they stay correct as
the data changes.

## Two guards worth keeping

The generator **aborts** rather than silently omitting anything:

- A table in `cms` that is missing from `$order` or `$policy` is a hard error.
  This is how `wc_cancel_subscriptions` was found after it appeared mid-migration.
- Only columns present in *both* schemas are copied, and any it had to skip are
  named in a comment in the dump. This is how the missing
  `wc_subscription.card_type` / `card_last4` / `card_expiry` columns were found.

If either fires, the schema has drifted — fix the migrations, don't work around
the generator.

## Notes

- `wc_keyword_lists` / `wc_keyword_list_items` have no data — they never existed
  in `cms`, so there is nothing to copy.
- `wc_seo_keywords` is copied whole (3,653 rows) rather than filtered to the kept
  websites. It is a domain-keyed DataForSEO cache that also holds researched and
  competitor domains never registered as websites, and having it populated saves
  burning API credits in dev.
- The `.sql` is a 21 MB generated artifact. If you would rather not carry it in
  git, add `dev-data/*.sql` to `.gitignore` and let developers run the generator.