feat: ProcessUnit hierarchy for structured sampling locations in process facilities

[jeandavidt]

Problem Statement

Sampling locations in the current data model are described using free-text fields (SamplingLocation, SamplingPoint) on the SamplingPoint table. For simple field campaigns — river monitoring, grab sampling, discrete sensor deployments — this is adequate. However, for process facilities such as wastewater treatment plants, pilot plants, and industrial sites, this approach breaks down in several ways:

1. No stable identity for pipes. If a pipe is named by its endpoints (e.g., "R210-to-R220"), that name becomes incorrect the moment a pump or T-junction is added in between. Renaming cascades through all linked data.
2. No queryable hierarchy. There is no way to ask "give me all sensors in biological line 2" or "what is measured inside the aeration zone?" because location is unstructured text.
3. No alignment with industry standards. P&ID line numbers, KKS tags, and ISA-S88 functional location codes cannot be recorded in a structured way.
4. SamplingPoint proliferation for co-located sensors. Users may create multiple SamplingPoints at the same physical location just to distinguish sensors measuring different parameters, when that distinction already belongs in Channel.Parameter_ID.

Solution

Introduce a ProcessUnit table — a self-referential, site-scoped hierarchy of functional locations. Each unit has a stable Tag (e.g., R-210, 10-PL-102, BioLine1) tied to its function, not its physical topology. SamplingPoint gains a nullable ProcessUnit_ID foreign key.

This means:

• Simple campaigns (field, river, grab) continue using free-text SamplingLocation as today — no breaking change.
• Process-facility campaigns can link each SamplingPoint to a ProcessUnit, enabling hierarchy queries and stable tagging even as physical topology evolves.
• Pipes receive a P&ID-style functional tag. When a pump is spliced into a pipe, the pump becomes a child ProcessUnit; the pipe's tag and all linked SamplingPoint records are unchanged.
• Co-located sensors share one SamplingPoint linked to one ProcessUnit. Differentiation between what each sensor measures lives in Channel.Parameter_ID, not in separate SamplingPoint rows.

The hierarchy is intentionally generic — not limited to WWTPs — and uses a simple parent-child tree rather than enforcing strict ISA-S88 level names.

User Stories

1. As a WWTP operator, I want to define a hierarchy of process units (reactors, clarifiers, pipes, valves) for my site, so that all sensor data is queryable by process zone.
2. As a WWTP operator, I want to assign a stable P&ID-style tag to each process unit, so that the identifier survives topology changes like adding a pump to a pipe.
3. As a data manager, I want to link a sampling point to a process unit, so that I can express where within the process a sensor is installed without duplicating location information.
4. As a data manager, I want the process unit link on a sampling point to be optional, so that existing field campaigns using free-text location fields continue to work without modification.
5. As a researcher, I want to query all channels associated with a given process unit (and its descendants), so that I can extract all measurements for a zone (e.g., biological line 2) in a single call.
6. As a researcher, I want to distinguish between two sensors measuring at different spatial positions within the same process unit (e.g., DO at 0 m and 20 m along an oxidation ditch), so that I can analyse spatial gradients.
7. As a researcher, I want sensors measuring different parameters at the same physical location to share one sampling point, so that location data is not duplicated and remains consistent.
8. As a WWTP operator, I want to nest process units under parent units (e.g., R-240 under BioLine1, BioLine1 under Site), so that I can navigate the process hierarchy from coarse to fine resolution.
9. As a data manager, I want a controlled vocabulary of process unit types (Tank, Reactor, Pipe, Pump, Valve, Clarifier, Zone, Area, etc.), so that units are categorised consistently across sites.
10. As a data manager, I want to create, update, and delete process units via the API, so that the hierarchy can be maintained as the plant evolves.
11. As a data manager, I want the API to prevent deletion of a process unit that still has child units or linked sampling points, so that orphaned references are not created.
12. As a UI user, I want to browse and manage the process unit tree for a site, so that I can visualise and edit the functional location hierarchy.
13. As a UI user, I want to assign a process unit to a sampling point from a dropdown filtered by site, so that only valid units for that site are selectable.
14. As a data manager, I want each schema change to have a forward migration and a rollback script, so that deployments can be safely reversed.
15. As a data manager, I want each new table to have a corresponding schema dictionary YAML entry, so that the data dictionary remains complete.
16. As an importer user, I want to reference process units by their tag when importing data, so that I can map SCADA exports to structured locations without knowing internal IDs.
17. As a site administrator, I want to scope process unit tags to a site (unique per site), so that the same tag (e.g., R-210) can exist at two different sites without conflict.
18. As a researcher, I want to retrieve a process unit tree (parent with nested children) in a single API call, so that I can render a hierarchy view without N+1 queries.

Implementation Decisions

New Tables

ProcessUnitType (lookup)

• Controlled vocabulary for unit categories: Area, Zone, Tank, Reactor, Pipe, Pump, Valve, Clarifier, Basin, Blower, Other.
• Seeded at migration time; extensible via API.

ProcessUnit

• Scoped to a Site via a required Site_ID FK.
• Carries a Tag (stable functional identifier, e.g. R-210, 10-PL-102). Unique per (Site_ID, Tag).
• Human-readable Name and optional Description.
• ProcessUnitType_ID FK to the lookup table.
• Nullable Parent_ID self-reference for the tree. No enforced depth limit.
• No ValidFrom/ValidTo on the unit itself — temporal tracking remains on SamplingPoint and SignalPortLocationHistory.

Modified Tables

SamplingPoint

• Add nullable ProcessUnit_ID FK to ProcessUnit.
• Existing SamplingLocation and SamplingPoint free-text columns are retained — no data migration required.

Schema Migration

• Forward migration creates ProcessUnitType, seeds lookup rows, creates ProcessUnit, then alters SamplingPoint to add the FK column.
• Rollback drops the FK column, then drops ProcessUnit and ProcessUnitType.
• Both scripts follow the existing migrations/ naming convention and are included in sql/init.sql.

API

New resource prefix: /api/v1/process-units

Endpoints:

• GET /process-unit-types — list lookup values
• GET /process-units?site_id= — list units for a site, optionally as a nested tree
• GET /process-units/{id} — single unit with children
• POST /process-units — create
• PUT /process-units/{id} — update tag, name, type, parent
• DELETE /process-units/{id} — guard: reject if child units or linked sampling points exist

Follows the existing sync FastAPI + pyodbc pattern (plain def endpoints, Depends(get_db)).

Design Rule: SamplingPoint vs. ProcessUnit vs. Channel

This rule must be documented and enforced in reviews:

• ProcessUnit answers which functional unit in the process.
• SamplingPoint answers where within that unit (only needed when spatial position within the unit varies, e.g., depth profiles, longitudinal gradients).
• Channel.Parameter_ID answers what is being measured.

Co-located sensors measuring different parameters → one SamplingPoint, multiple Channels. Sensors at distinct positions within the same unit → one ProcessUnit, multiple SamplingPoints.

Testing Decisions

What makes a good test here: Test observable API and database behaviour — not internal repository logic. A good test POSTs a process unit, retrieves it, confirms the tree structure, and confirms that a dependent deletion is rejected. Tests should not mock the database.

Modules to test:

• ProcessUnit CRUD API (contract tests, no DB required for schema validation; integration tests against real DB for hierarchy and guard logic)
• SamplingPoint with ProcessUnit_ID set — confirm FK is stored and returned
• Tree retrieval — confirm nested children are returned correctly for a 3-level hierarchy
• Deletion guard — confirm 422/409 when child units exist; confirm 422/409 when linked sampling points exist

Prior art: Follow the pattern of existing integration tests in tests/integration/ using the live MSSQL Docker container (pytest -m db).

Out of Scope

• Strict enforcement of ISA-S88 hierarchy levels (Area / Unit / EquipmentModule / ControlModule) — the simple parent-child tree is intentional.
• Import of process unit hierarchies from external P&ID or CMMS systems.
• Visualisation of the process unit tree in a diagram (Excalidraw export is a separate tool, not part of the data model).
• Any changes to Value, Observation, or Channel tables.
• Parallel biological line 2 (3xx numbering) — identical structure, separate data entry.

Further Notes

• The SCADA screen for "Réacteur biologique Docs/documentation v1 #1" was used as a reference example during design. The expected ProcessUnit tree for that screen includes: area BR1, five reactors (R-210 through R-250), one clarifier (D-260), one cooling zone (COOL-1), valves, pumps, agitators, and air feed pipes — with 12 sampling locations mapped to SCADA instrument tags (AIT-241, TIT-241, AIT-251, AIT-252, AIT-260, AIT-271/272/273, FIC-260, TIC-252b, TIT-341).
• A parallel line 2 (3xx tags) and additional SCADA screens exist; they would follow the same pattern under a BR2 area unit.
• The Excalidraw diagram docs/BR1_process_diagram.excalidraw captures the full BR1 topology and serves as a reference for implementers.