73e90cf710
Seven mentions remain across the repository and none of them is data. Each is a sentence recording that these names were wrong, what it cost, and that they must not come back: the namespace tables in CLAUDE.md and the build spec, the generator's docstring, the fixtures rewrite note, the Phase 5 narrative in REBUILD.md and current-state.html, and the delivered configuration's own README. The build spec's namespace table carried the same error CLAUDE.md did - PS_STN_WET_WELL_LEVEL listed as the CI Server point layer, sourced from a file that was a PLC-side proposal. Both now name the real point layer, WRPS_PLC:STN_LEVEL from wrps_modbus_point_df.qli, and both point at db/seed/scada-source/ rather than at a path on one person's laptop. The name-collision entry in the build spec is marked closed, and records that it bit a second time during the rename itself. These notes explain; they do not enforce. What actually caught the collision was check_mapping() refusing to write, and what stops a retired name being queried is the guard in equipment.resolve(). The prose is here so the next person does not have to re-derive the story - not as a control. Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
204 lines
13 KiB
Markdown
204 lines
13 KiB
Markdown
# The stand-in historian — what it is, and how to remove it
|
||
|
||
`imh` (`yau-sls-poc-imh`) does not exist yet. Everything the assistant says about
|
||
plant history today comes from generated data in the `fixture` schema, built by
|
||
[`002_fixtures.sql`](002_fixtures.sql).
|
||
|
||
This file exists so that removing it later is a checklist rather than an
|
||
archaeology exercise. **Read it before connecting `imh`,** not after.
|
||
|
||
> [!IMPORTANT]
|
||
> **The real historian will not be identical to this.** The stand-in was built
|
||
> from the SCADA configuration in `WRPS/05-scada/modbus`, so the *item names*,
|
||
> *sample rates*, *retention* and *timestamp semantics* are taken from the
|
||
> machine rather than invented. **Everything about the SQL Server side — table
|
||
> names, column names, column types, how a value is stored, how quality is
|
||
> expressed — is a guess.** Section 4 lists every one of those guesses with a
|
||
> way to test it. Work through that table against the real thing before you
|
||
> trust a single number.
|
||
|
||
---
|
||
|
||
## 1. Why there is a stand-in at all
|
||
|
||
Phase 4 (`imh` access) is the only true blocker in the build. Cube, the
|
||
contracts, the agent and the UI can all be built and tested without it. The
|
||
stand-in exists so that work is not idle, and so that the *shape* of the data
|
||
contract is settled and reviewed before the real connection lands.
|
||
|
||
It is switched on by `USE_FIXTURES=true` in `~/ai/api.env`.
|
||
|
||
Every row carries `is_fixture = TRUE`, and the flag rides all the way to a
|
||
banner on the operator's screen. **Do not remove that flag as tidying-up.**
|
||
|
||
---
|
||
|
||
## 2. The seam — where fixture ends and contract begins
|
||
|
||
This is the single most important thing in this document.
|
||
|
||
```
|
||
FIXTURE SCAFFOLDING (delete at cutover)
|
||
┌──────────────────────────────────────────────────────────────┐
|
||
│ fixture.build_meta the build clock │
|
||
│ fixture.f_peak / f_level / f_pumps / f_inflow / │
|
||
│ fixture.f_discharge / f_duty / f_alarm_word │
|
||
│ fixture.injected_condition the scripted trips and faults │
|
||
│ fixture.item_history the generated samples │
|
||
└──────────────────────────────────────────────────────────────┘
|
||
│
|
||
════════════════════╪═══════════ THE SEAM ═══════════
|
||
│
|
||
┌──────────────────────────────────────────────────────────────┐
|
||
│ fixture.process_value_history VIEW │
|
||
│ fixture.alarm_history VIEW │
|
||
│ fixture.operation_history MATERIALIZED VIEW │
|
||
└──────────────────────────────────────────────────────────────┘
|
||
CONTRACT (repoint, do not delete)
|
||
│
|
||
Cube models read only these
|
||
```
|
||
|
||
**Above the seam is generation. Below it is contract.** The three views are the
|
||
only things any Cube model names. Cutover means giving those three views a new
|
||
source; it does not mean touching a Cube model, and if you find yourself
|
||
editing one, stop and ask why the view could not absorb the difference.
|
||
|
||
`public.historian_items`, `public.alarm_bits`, `public.tags` and
|
||
`public.equipment` are **reference data, not fixtures.** They live in `public`,
|
||
they are loaded by `deploy.sh` regardless of `USE_FIXTURES`, and they survive
|
||
cutover unchanged. That placement is deliberate: the item-to-tag mapping and
|
||
the alarm bit map are properties of the SCADA and PLC configuration, not of the
|
||
stand-in.
|
||
|
||
---
|
||
|
||
## 3. Inventory
|
||
|
||
### Delete at cutover
|
||
|
||
| Object | What it is |
|
||
|---|---|
|
||
| `fixture.build_meta` | Origin and horizon of the generated window |
|
||
| `fixture.f_peak`, `f_level`, `f_pumps`, `f_inflow`, `f_discharge`, `f_duty`, `f_alarm_word` | The prescribed plant |
|
||
| `fixture.injected_condition` | Scripted trips, seal leak, vibration, frozen transmitter, rejected setpoint |
|
||
| `fixture.item_history` | ~685,000 generated samples |
|
||
| The `DO $$ … $$` assertion block at the foot of `002_fixtures.sql` | Asserts counts that are facts about generated data only |
|
||
|
||
### Repoint, do not delete
|
||
|
||
| Object | Becomes |
|
||
|---|---|
|
||
| `fixture.process_value_history` | A view over the real `imh` analogue history |
|
||
| `fixture.alarm_history` | The same bit-decomposition, over the real alarm word — **or** a view over CI Server's `ALARM_HISTORY` if anyone ever configures item alarm limits |
|
||
| `fixture.operation_history` | The same derivation from `PUMPS_RUNNING`, over real data |
|
||
|
||
Consider renaming the schema from `fixture` to something honest (`historian`)
|
||
at that point, and updating `sql_table:` in the four Cube models. That is a
|
||
rename, not a redesign — do it in its own commit.
|
||
|
||
### Keep unchanged
|
||
|
||
`public.historian_items`, `public.alarm_bits`, `public.tags`,
|
||
`public.equipment`, `scripts/gen_historian_items.py`, `db/seed/*.csv`, and all
|
||
four Cube model files.
|
||
|
||
---
|
||
|
||
## 4. Assumptions that may not hold — test every one
|
||
|
||
The shape below was reasoned from the SCADA configuration. **The SQL Server
|
||
side was not available and none of it is confirmed.**
|
||
|
||
| # | Assumption | Where it is encoded | How to test it | If it is wrong |
|
||
|---|---|---|---|---|
|
||
| **A1** | `imh` exposes **one** item-keyed history table | `fixture.item_history` shape; all three views | List the tables. Look for one row per (item, time, value) | If history is split per group or per data type, the three views absorb it with a `UNION ALL`. No Cube change |
|
||
| **A2** | Item names appear **verbatim** as `AID.WRPS.STN.LEVEL` | Join `history.item_name = historian_items.item_name` | `SELECT DISTINCT` the name column and compare against `db/seed/historian_items.csv` | Case, separators (`.` vs `\`), a node prefix, or a numeric item id with a lookup table. Normalise **in the view**, never by editing the seed |
|
||
| **A3** | Timestamps are **UTC** | Every view; `alarms.yml` converts once | Confirmed from config (`TIME_ZONE "Date+time GMT"`, `CORRECT_DAYLIGHT=0`) but **verify against data**: take a known event and check it against wall-clock | SQL Server has no `timestamptz`. A `datetime2` holding UTC must be cast with `AT TIME ZONE 'UTC'` in the view, or every answer shifts by ten hours |
|
||
| **A4** | Values are in **engineering units**, gain already applied | All measures; `86.7` and `91.7` thresholds in `process_values.yml` | Read `AID.WRPS.STN.LEVEL` and check it is ~0–100, not ~0–7000 | If raw registers, apply `historian_items.eng_gain` **in the view**. The thresholds in the Cube model assume percent |
|
||
| **A5** | Quality is text `GOOD` / `BAD` / `UNCERTAIN` | Every measure filters `quality = 'GOOD'` | Inspect the column | CI Server may use numeric OPC quality codes. Map to the three strings in the view. **A missing quality column is not "all good"** — decide explicitly and write down which |
|
||
| **A6** | The alarm word is retained as an item we can decompose | `fixture.alarm_history` | Check `AID.WRPS.STN.ALARM_WORD` has history | If it is not retained, derive alarms from the discrete items instead (`STN.HIGH_LEVEL`, `PU30x.TRIPPED`, …). `public.alarm_bits.tag_id` already names them |
|
||
| **A7** | Sampling is **regular** at the declared interval | `time_weighted_avg`, `seconds_above_*` sum `scan_interval_seconds` | Compare consecutive `sample_time` gaps against `historian_items.scan_interval_seconds`. **This is on the Phase 4 gate** | If deadband compression is ever enabled, replace `scan_interval_seconds` with a `LEAD` window in the view. Every duration measure is wrong until you do |
|
||
| **A8** | Retention is **7 days** | `metrics.HISTORY_RETENTION_DAYS`; pre-aggregation build ranges | `SELECT min(sample_time)` | Update the constant, and widen `build_range_start` in all three pre-aggregations. **Note the constant is also the query window** — every builder asks for a rolling week whatever the question names, and that is by design. Longer retention alone is inert until the window is made variable |
|
||
| **A9** | There is **no** operations concept — pump-downs must be derived | `fixture.operation_history` | Look for any batch/campaign table | If one exists, prefer it, and re-verify the 5-minute noise threshold and the 10-minute look-back against it |
|
||
| **A10** | The `32767` sentinel survives into engineering units | Every measure excludes `value <> 32767` | Check `TIME_TO_SPILL` for the sentinel | If a gain is applied to it, the sentinel is no longer 32767 and every average silently includes it. **This one fails quietly** |
|
||
| **A11** | The alarm word can be read **unsigned** | `fixture.alarm_history` normalises with `((v % 65536) + 65536) % 65536` | Check whether the word ever goes negative | SQL Server `SMALLINT` is signed, so bit 15 makes the whole word negative and a right-shift sign-extends — reporting every higher bit as active at once. The normalisation already handles it; **do not remove it** |
|
||
| **A12** | One row per item per timestamp (the view's primary key) | `process_values.id` = `item_name \|\| '@' \|\| sample_time` | Check for duplicates | Duplicate timestamps break Cube's primary key. Deduplicate in the view and find out why they exist |
|
||
|
||
**A10 and A5 are the two that fail silently.** A wrong sentinel or a
|
||
misinterpreted quality code does not raise an error; it produces a plausible
|
||
number. Test those two with data, not by reading a schema.
|
||
|
||
---
|
||
|
||
## 5. Removal procedure
|
||
|
||
1. **Do not delete anything yet.** Stand the real source up beside the fixtures
|
||
and work through section 4 with real data. Write the answers into
|
||
`BUILD-AI-CONTAINERS.md` §10.
|
||
|
||
2. **Rewrite the three views** against `imh`, absorbing every difference found
|
||
in step 1. The views change; the Cube models must not.
|
||
|
||
3. **Re-verify the Phase 5 gate by hand, without the LLM.** Prove Cube returns
|
||
the right number by querying it directly and checking against `imh` with
|
||
your own SQL. The Phase 5 gate exists for exactly this moment.
|
||
|
||
4. **Re-derive the pinned eval expectations.** `eval/testset.jsonl` case `H31`
|
||
pins a high level alarm count of **14**. That is a fact about generated data
|
||
and nothing else. Replace it with a real figure an engineer has verified, or
|
||
remove the pin.
|
||
|
||
5. **Set `USE_FIXTURES=false`** and repoint `CUBEJS_DB_*` at `imh`.
|
||
|
||
6. **Drop the scaffolding** listed in section 3, and delete the assertion block
|
||
from `002_fixtures.sql`.
|
||
|
||
7. **Run `scripts/verify.sh`.** The historian-item-mapping and no-equipment-
|
||
column checks are not fixture-specific and must still pass. The fixture
|
||
banner should disappear.
|
||
|
||
8. **Check the fixture banner is actually gone** from an answer on the operator
|
||
screen, not just from the database. It is driven by `used_fixture_data` off
|
||
`USE_FIXTURES`, so it should follow — confirm it rather than assume it.
|
||
|
||
---
|
||
|
||
## 6. What the assertions do and do not prove
|
||
|
||
`002_fixtures.sql` ends with a block that **fails the load** on any of:
|
||
|
||
- 685,440 analogue rows; 32 alarm activations; 14 high level; 2 spills;
|
||
15 priority-1; 72 pump-downs; 720 BAD level samples
|
||
- every historised, answerable item having history
|
||
- alarm-word **bit 0** agreeing with the independent discrete item
|
||
`AID.WRPS.STN.HIGH_LEVEL`, and **bit 3** with `AID.WRPS.STN.SPILL_ACTIVE`
|
||
- the operations derivation and the alarm derivation agreeing on spill count
|
||
- volume-remaining-to-spill reconciling with level through the plant geometry
|
||
at every sample
|
||
|
||
Those are real tests of **the derivation logic**, and the cross-checks in
|
||
particular compare two independent paths through the data. They are worth
|
||
keeping in mind when rewriting the views, because the same cross-checks can be
|
||
run against `imh` and should still hold.
|
||
|
||
**They prove nothing about the plant.** Every number above is a fact about
|
||
generated data. The moment `imh` is connected they are meaningless, and step 6
|
||
deletes them.
|
||
|
||
---
|
||
|
||
## 7. History, so this is not relearned
|
||
|
||
The stand-in was rebuilt on 2026-08-31. The version before it was keyed on CI
|
||
Server **point** names (`AID.WRPS.STN.LEVEL`) when the historian is keyed on
|
||
CI Server **item** names (`AID.WRPS.STN.LEVEL`) — two layers apart. That single
|
||
substitution produced all three open Phase 5 findings, and each looked like an
|
||
independent bug: a tag that would not join, alarm times in the wrong timezone,
|
||
an alarm filed against the wrong equipment.
|
||
|
||
The lesson worth carrying into cutover: **a stand-in that is shaped wrongly is
|
||
worse than no stand-in**, because it produces confident answers and the defects
|
||
present as unrelated. When something here disagrees with `imh`, the first
|
||
question is not "which value is right" but "are these two things even the same
|
||
thing".
|