# Schema harvest (TW-297)

Pulls **INFORMATION_SCHEMA only** (tables / columns / keys) into `catalogs/schemas/{env}/{target}.json`.

Never selects business row data. Never commits DSNs or passwords.

## Phase 1 targets (real DB names)

| Target id (`--target`) | MySQL schema | Prod host (hint) | Status |
| --- | --- | --- | --- |
| `admin` | `Admin` | `192.168.30.7` | **First** — MNGT / Admin (TW-309 aligned) |
| `shorty` | `Shorty` | `192.168.30.2` | Allowlisted |
| `adcenter` | `adcenter` | `192.168.30.4` | Allowlisted |
| `whale` | `whale` | `192.168.30.10` | Allowlisted |
| `keywords` | `keywords` | `192.168.30.13` | Allowlisted |

Catalog format: **JSON** (not YAML).

Both **staging** and **prod** live harvests are supported. Prefer staging first; gate prod with `SCHEMA_HARVEST_ALLOW_PROD=true`.

Put host/user/password only in env/vault (never git). Staging hosts may differ from the prod hints above — use the correct staging DSN for each target.

## Allowlist

`jobs/schema_harvest/allowlist.json` — fail closed if a non-listed schema appears.

Do not add new schemas without ticket + DBA/security review.

## Run (fixture — no DB)

Useful for CI / local without VPN:

```bash
npm run harvest:schemas -- --env staging --target admin \
  --fixture tests/fixtures/schema-harvest-admin.json
```

Writes a **fixture** catalog (not a live DB snapshot). For a real staging catalog, omit `--fixture` and set the staging DSN.

## Run (live staging)

1. Put the RO password in your shell only (never git).
2. Set the staging DSN for the target, run harvest.

**Admin (start here):**

```bash
export SCHEMA_HARVEST_ADMIN_DSN_STAGING='mysql://aireadonly:YOUR_PASSWORD@STAGING_HOST:3306/Admin'
npm run harvest:schemas -- --env staging --target admin
```

Writes `catalogs/schemas/staging/admin.json`.

**Other targets (same pattern):**

```bash
export SCHEMA_HARVEST_SHORTY_DSN_STAGING='mysql://aireadonly:YOUR_PASSWORD@STAGING_HOST:3306/Shorty'
npm run harvest:schemas -- --env staging --target shorty

export SCHEMA_HARVEST_ADCENTER_DSN_STAGING='mysql://aireadonly:YOUR_PASSWORD@STAGING_HOST:3306/adcenter'
npm run harvest:schemas -- --env staging --target adcenter

export SCHEMA_HARVEST_WHALE_DSN_STAGING='mysql://aireadonly:YOUR_PASSWORD@STAGING_HOST:3306/whale'
npm run harvest:schemas -- --env staging --target whale

export SCHEMA_HARVEST_KEYWORDS_DSN_STAGING='mysql://aireadonly:YOUR_PASSWORD@STAGING_HOST:3306/keywords'
npm run harvest:schemas -- --env staging --target keywords
```

## Run (live prod)

Prod requires an explicit gate:

```bash
export SCHEMA_HARVEST_ALLOW_PROD=true
export SCHEMA_HARVEST_ADMIN_DSN_PROD='mysql://aireadonly:YOUR_PASSWORD@192.168.30.7/Admin'
npm run harvest:schemas -- --env prod --target admin
```

Writes `catalogs/schemas/prod/admin.json`.

```bash
export SCHEMA_HARVEST_SHORTY_DSN_PROD='mysql://aireadonly:YOUR_PASSWORD@192.168.30.2/Shorty'
npm run harvest:schemas -- --env prod --target shorty

export SCHEMA_HARVEST_ADCENTER_DSN_PROD='mysql://aireadonly:YOUR_PASSWORD@192.168.30.4/adcenter'
npm run harvest:schemas -- --env prod --target adcenter

export SCHEMA_HARVEST_WHALE_DSN_PROD='mysql://aireadonly:YOUR_PASSWORD@192.168.30.10/whale'
npm run harvest:schemas -- --env prod --target whale

export SCHEMA_HARVEST_KEYWORDS_DSN_PROD='mysql://aireadonly:YOUR_PASSWORD@192.168.30.13/keywords'
npm run harvest:schemas -- --env prod --target keywords
```

Use a **read-only** user. Prefer privileges limited to `INFORMATION_SCHEMA` + the allowlisted schema.

If MySQL says the schema name case is wrong (`Admin` vs `admin`), fix `allowed_schemas` in `allowlist.json` to match what MySQL returns and re-run.

## Scheduler

Phase 1: **manual / CI on demand**. Do not auto-commit catalogs without review (TW-298 before Qdrant / TW-299).

## Out of scope

- `catalog_search` tool (TW-300)

After harvest, run sensitivity review before indexing: `jobs/sensitivity_review/README.md` (TW-298).
After review, index into Qdrant: `jobs/catalog_index/README.md` (TW-299).