# Pilot Plant hierarchy reads

The hierarchy endpoints retain the existing routes/controllers/services/validators layout. All readers require authentication; only ADMIN/SUPERVISOR create or rename records. Metadata schemas reject unknown fields, trim names to 1-150 characters, and require an integer version for edits. Scoped uniqueness uses the database collation and reserves archived names.

## Navigation contract

- GET /api/v1/plant-units returns active categories with totalEquipment and matchingEquipment.
- GET /api/v1/plant-units/:id/equipment and GET /api/v1/assets/:id/sub-items return bounded asset summaries.
- GET /api/v1/assets/:id returns one active asset summary.
- GET /api/v1/pilot-plant/summary counts distinct active equipment globally, independently of filters.
- Lists accept page (default 1), pageSize (default 20, maximum 100), q (trimmed, maximum 150), and condition (ALL/NOTED/CLEAN/UNKNOWN). Unknown query keys are rejected with 422.
- Ordering is name ascending, then ID ascending. Unit/equipment searches include active descendant names; unit names also match their equipment.
- Empty categories remain visible for ALL/UNKNOWN. NOTED counts OPEN/ACCEPTED/IN_REPAIR reports on equipment or active children. Otherwise equipment without active children is UNKNOWN, and equipment with children is CLEAN (not a health certification).
- nextMaintenanceDueAt is the earliest non-null due date of released OPEN/IN_PROGRESS work on the asset or active children. maintenanceTiming uses SystemSetting.timezone (Asia/Jakarta fallback) and preserves DUE_TODAY even when the timestamp is earlier than now.

POST plant-units, plant-units/:id/equipment and assets/:id/sub-items accept only name and return 201 with a generated immutable code. PATCH plant-units/:id and assets/:id accept name/version; success increments the version, and stale edits return STATE_CONFLICT (409). Audit writes share the mutation transaction. No move/archive/delete or photo mutation is exposed by this hierarchy slice.

## Query bounds and plan inspection

Asset navigation performs bounded parent lookups, a paged row query and count, then two grouped SQL aggregates (report count and earliest due date), one grouped child count, and one timezone lookup. The number of queries is independent of page length. No report descriptions or unbounded history are loaded. Unit navigation uses paged units with active-equipment counts plus one grouped matching-equipment count. Global summary uses three count queries in a transaction.

The isolated MySQL test compares projection query counts with pageSize=1 and pageSize=100: both execute two raw aggregate queries and one child group query. Report and due-date aggregation now joins equipment and items through equipmentId/itemId. Indexes cover scoped names, active children, report status/date, and work-order target/date. The integration test also runs EXPLAIN on the item-report join; fixture cardinalities are not a production benchmark.

Run from apps/api:
`node --env-file=.env prisma/verify-pilot-plant-api.cjs`

The runner creates a random cmms_pilot_api_* database, applies all three migrations there, executes authenticated API/service checks, disconnects, and removes only the database it successfully created. It never migrates the configured application database. Regular tests skip these database cases unless the guarded isolated environment is supplied.

OpenAPI schemas, permissions, pagination, examples, and versioned requests are in the @openapi blocks in src/routes/pilot-plant.routes.ts and included in source and compiled Swagger.