descrybe/docs/schema-map.md

# Legacy MySQL → PostgreSQL schema map

Descrybe v2 replaces Clerk-backed MySQL identity with first-party auth on PostgreSQL. The migrator (`packages/migrator` / `apps/api/cmd/migrator`) reads legacy rows, remaps IDs, and loads into the goose schema under `apps/api/sql/schema`.

## Identity remapping

| Legacy (MySQL) | v2 (PostgreSQL) | Notes |
|---|---|---|
| Clerk `user_id` (text, e.g. `user_…`) | `users.legacy_user_id` + new `users.id` UUID | Every FK that pointed at Clerk user ids is rewritten to the new UUID via the ID map |
| `companies.id` (text) | `companies.legacy_company_id` + new `companies.id` UUID | Preserve the old text id in `legacy_company_id` for audit / re-runs; all new FKs use the UUID PK |
| `profiles` (user ↔ company + role/status) | `memberships` | One row per `(company_id, user_id)`; roles `admin` \| `member`; status `active` \| `inactive` |
| `admin_users` | `users.is_platform_admin` | Platform-wide flag on the user row (not a separate table) |
| Clerk org membership | `memberships` | No Clerk org ids in v2 |
| Clerk sessions / passwords | `sessions` + `users.password_hash` | No Clerk hashes to import; set `must_set_password = true` and email set-password links |

### Users

1. For each distinct Clerk `user_id` (from `users` / `profiles` / related tables), allocate a new UUID.
2. Store the Clerk id in `users.legacy_user_id` (unique).
3. Copy email / name; leave `password_hash` null; set `must_set_password = true`.
4. Rewrite all user FKs through the user ID map.

### Companies

1. For each legacy company row, allocate a new UUID PK.
2. Store the legacy text id in `companies.legacy_company_id` (unique).
3. Copy name, language, merge-by-GTIN and related settings into `companies` / `company_settings`.
4. Rewrite all `company_id` text FKs through the company ID map.

### Profiles → memberships

Legacy `profiles` rows become `memberships`:

- Map `profiles.user_id` → `memberships.user_id` (new UUID)
- Map `profiles.company_id` → `memberships.company_id` (new UUID)
- Map role/status enums to the v2 check constraints
- Skip duplicate `(company_id, user_id)` after remapping; prefer the active/admin row if conflict

### Admin users

Legacy `admin_users` emails/ids set `users.is_platform_admin = true` on the matching remapped user. Do not create orphan admin rows without a user.

## Products index cleanup (`raw_products` / `processed_products`)

Legacy Drizzle schemas declare overlapping single-column indexes (same column indexed twice under different names). Do **not** recreate those duplicates in Postgres.

### Legacy `raw_products` — drop / consolidate

| Legacy index | Columns | v2 action |
|---|---|---|
| `idx_raw_products_feed_id` | `feed_id` | Keep **one** feed index (or rely on composite) |
| `idx_raw_products_feed` | `feed_id` | **Drop** — duplicate of above |
| `idx_raw_products_company_id` | `company_id` | Prefer composite `(company_id, …)` instead of bare duplicate |
| `idx_raw_products_company` | `company_id` | **Drop** — duplicate |
| `idx_raw_products_processed` | `is_processed` | Re-evaluate; often redundant with `processing_status` |
| `idx_raw_products_processing_status` | `processing_status` | Keep if status filters are hot |
| `idx_raw_products_company_feed` | `(company_id, feed_id)` | **Keep** — primary list/filter path |
| `idx_raw_products_gtin` | `gtin` | Keep; consider `(company_id, gtin)` if lookups are tenant-scoped |
| `idx_raw_products_sync_job` | `sync_job_id` | Keep if sync cleanup queries need it |
| `idx_raw_products_file` | `file_id` | Keep if upload paths filter by file |
| `raw_products_gtin_feed_unique` | `(gtin, feed_id)` | Preserve uniqueness (or company-scoped unique after design) |

Post-load: create remaining indexes with `CREATE INDEX CONCURRENTLY`, then `ANALYZE`.

### Legacy `processed_products` — redesign notes

| Legacy index | Columns | v2 action |
|---|---|---|
| `idx_products_company` | `company_id` | Prefer `(company_id, status)` or `(company_id, feed_id)` composites from real list queries |
| `idx_products_feed` | `feed_id` | Keep if feed-scoped exports need it; else fold into composite |
| `idx_processed_products_raw` | `raw_product_id` | **Keep** — join back to raw |
| `idx_products_product_id` | `product_id` | Keep only if external product ids are looked up; else drop |

Also: remapped `user_id` / `company_id` text → UUID FKs; drop loose profile-based company links; unique constraints only where the product model requires them (e.g. company + gtin when merge-by-GTIN is on). High-volume tables may use `BIGSERIAL` PKs instead of UUIDs.

## ID map JSON format

Migrator writes (and accepts) a JSON document used for dry-run reports, resume, and FK rewrite.

```json
{
  "version": 1,
  "generated_at": "2026-08-03T18:00:00Z",
  "source": "mysql",
  "target": "postgres",
  "users": {
    "user_2abcClerkId": "550e8400-e29b-41d4-a716-446655440000"
  },
  "companies": {
    "org_or_legacy_company_text_id": "6ba7b810-9dad-11d1-80b4-00c04fd430c8"
  },
  "meta": {
    "user_count": 1,
    "company_count": 1,
    "dry_run": false
  }
}
```

Rules:

- Keys are **legacy** string ids; values are **new** UUID strings.
- File is the single source of truth for a migration run; re-runs must reuse the same map to stay idempotent.
- Optional per-entity sections (e.g. `categories`, `feeds`) may be added later with the same `{ legacy_id: new_uuid }` shape.
- Store next to migration artifacts (e.g. `artifacts/id-map.json`); do not commit production maps with PII beyond opaque ids.

## Load order (FK-safe)

1. companies  
2. users  
3. memberships / invites / api_keys / company_settings  
4. plans → company_plans → billing_cycles → credit_balances → processing_costs  
5. catalog (categories, attributes, variables, files, products)  
6. feeds / export / jobs (skip ephemeral queues)

See [cutover.md](cutover.md) for the operational runbook.