# Pilot Plant: category → equipment → items

The database now uses three physical tables:
- `category` (Prisma `Category`): category name, code, version and photo.
- `equipment` (Prisma `Equipment`): belongs to one category via `categoryId`.
- `items` (Prisma `Item`): belongs to one equipment via `equipmentId`.

Equipment and item names are unique within their respective parents, including
archived names. Item category is derived through equipment; it cannot disagree
with its parent. The recursive hierarchy, level and nameScopeId are removed.

Related tables have equipmentId/itemId foreign keys instead of assetId.
Required-target records use exactly one; optional notification targets use at
most one. The API and seeder supply the appropriate level. Existing legacy
equipment reports remain readable, while new reports are submitted only on items.
PartAssignment maps to part_assignment and OperatingLog maps to operating_log.

## Migration

`20261013000100_split_pilot_hierarchy` follows the two existing migrations.
Do not edit/delete applied migration files or use a database reset.

1. Back up the database and stop API/background writers.
2. From the workspace root, run:
   ```powershell
   pnpm --filter express-api-starter-ts prisma:migrate:deploy
   pnpm --filter express-api-starter-ts prisma:generate
   pnpm --filter express-api-starter-ts prisma:seed:pilot-plant
   ```
3. Restart the API and verify the Pilot Plant lists.

The migration checks the legacy hierarchy first, creates the new tables, copies
all metadata (IDs, timestamps, archives, versions, private photo keys, legacy
photo URLs, manufacturer and serial numbers), backfills dependent references,
checks copied counts/references, and installs foreign keys before dropping the
legacy tables. Report reviews, work-order status/history, plans, calibration,
failure events, operating logs and part assignments retain their targets.
Part assignments receive deterministic IDs for their new primary key.

MySQL DDL is not transactional: a failed migration can leave intermediate tables.
If it fails, inspect the reported statement and restore the backup or complete a
reviewed recovery before retrying; do not reset or mark it applied blindly.
The old preflight command documents checks for the first Pilot Plant migration
and is intended for the legacy schema.

## HTTP compatibility

The existing /plant-units and /assets HTTP paths and shared DTO property names
remain compatible with the app. They are projections of Category/Equipment/Item,
not physical database models or tables. Swagger remains beside each route.

## Verification

`test:migration:pilot-plant` rehearses on clean and populated temporary databases,
including copies of report reviews, photos, work orders, plans, part assignments,
calibration, failures, notifications, and operating logs. It checks foreign keys,
scoped uniqueness, and removal of legacy tables.

`test:pilot-plant` exercises navigation, photos, report review/concurrency and
repeatable seeding on isolated databases. Neither runner migrates the application
database.