-- ============================================================================= -- 001_schema.sql — pg-ai reference data and document store. -- -- psql -h pg-ai -U postgres -d plant -f 001_schema.sql -- -- What pg-ai is FOR: pgvector document chunks, Cube pre-aggregations, and the -- equipment/tag reference data including the alias lists. -- -- What pg-ai is NOT for: historian data. There is no replication job and no -- mirror table. imh is already an isolated copy of the raw SCADA historian, so -- Cube queries imh directly over TDS/1433 with a read-only login. The only -- exception is 002_fixtures.sql, which stands in for imh until it exists. -- -- Storage is UTC everywhere. Conversion to SITE_TIMEZONE happens exactly once, -- in Cube. Never in SQL here and never in a prompt. -- ============================================================================= CREATE EXTENSION IF NOT EXISTS vector; -- ----------------------------------------------------------------------------- -- equipment — what the operator says. -- -- "Pump 02" is equipment; its data lives on tags. Without this table every -- equipment-level question fails, because no historian point is called -- "Pump 02". Aliases are the whole point: operators do not type tag numbers. -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS equipment ( equipment_id TEXT PRIMARY KEY, -- PU-302 display_name TEXT, -- Pump 02 aliases TEXT[], -- {'Pump 02','pump2','P2','PU-302'} equipment_type TEXT, -- pump | vessel | station | piping | ... unit_name TEXT, -- WRPS description TEXT ); CREATE INDEX IF NOT EXISTS equipment_aliases_gin ON equipment USING gin (aliases); -- ----------------------------------------------------------------------------- -- tags — the historian points and field instruments, and how to read them. -- -- engineering_unit, range and setpoints are recorded in the units the HISTORIAN -- stores, which are not always the units the PLC works in. Wet well level is -- the trap: the PLC works in mm, CI Server historises percent of the spill weir -- crest (mm / 60). The description field carries the conversion for every tag -- where the two differ. Read it before interpreting a number. -- -- The description also records whether a tag IS HISTORISED. Field inputs to the -- PLC (%IW / %IX) are not published to SCADA and have no history at all. An -- answer that trends PU-301 vibration is fabricating data. -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS tags ( tag_id TEXT PRIMARY KEY, -- AID.WRPS.STN.LEVEL, LIT-101 equipment_id TEXT REFERENCES equipment(equipment_id), display_name TEXT, aliases TEXT[], signal_type TEXT, -- level|flow|pressure|status|state|... engineering_unit TEXT, range_low DOUBLE PRECISION, range_high DOUBLE PRECISION, alarm_setpoint_hi DOUBLE PRECISION, alarm_setpoint_lo DOUBLE PRECISION, trip_setpoint DOUBLE PRECISION, description TEXT ); CREATE INDEX IF NOT EXISTS tags_aliases_gin ON tags USING gin (aliases); CREATE INDEX IF NOT EXISTS tags_equipment_ix ON tags (equipment_id); -- ----------------------------------------------------------------------------- -- historian_items — the CI Server item dictionary, and the ONLY place an -- item name is joined to a tag. -- -- FOUR NAMESPACES DESCRIBE THE SAME MEASUREMENT and only the last is what the -- historian is keyed on: -- -- LIT-101 instrument tag WRPS/01-design-doc -- %QW0 PLC symbol WRPS/04-plc/register-map.csv -- AID.WRPS.STN.LEVEL CI Server point WRPS/05-scada/modbus/scada-points.csv -- AID.WRPS.STN.LEVEL CI Server ITEM WRPS/05-scada/modbus/wrps_item_df.qli -- -- The history was previously keyed on the third of those while the tag seed -- carried it only as an alias of the first, so a tag-level lookup for the wet -- well matched zero rows and reported "no records found" — indistinguishable -- from an absence of data. That was Phase 5 finding (a). -- -- Two rules keep it from recurring, and both are enforced rather than reviewed: -- -- 1. tag_id is NULLABLE, but a NULL one must carry an exclusion_reason. -- The CHECK below is the enforcement. scripts/gen_historian_items.py -- refuses to write the seed at all if a historised item has neither. -- 2. NOTHING IN THE HISTORY CARRIES AN equipment_id. Equipment is asserted -- once, in tags.equipment_id, and reached from history through this -- table. Finding (c) was a denormalised equipment column in the history -- disagreeing with the tag seed; there is now only one assertion to -- disagree with. -- -- Regenerate with: python scripts/gen_historian_items.py --wrps -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS historian_items ( item_name TEXT PRIMARY KEY, -- AID.WRPS.STN.LEVEL tag_id TEXT REFERENCES tags(tag_id), exclusion_reason TEXT, -- why this item is unanswerable section_path TEXT, -- AID.WRPS.STN section TEXT, -- STN, PU301, SP, SIM attribute TEXT, -- LEVEL, RUNNING, TRIPPED section_description TEXT, description TEXT, eng_unit TEXT, -- what CI Server presents, not PLC units value_format TEXT, conv_type TEXT, -- Linear | Digital has_sign BOOLEAN, phys_low DOUBLE PRECISION, phys_high DOUBLE PRECISION, eng_gain DOUBLE PRECISION, -- raw register x gain, offset always 0 raw_to_eng TEXT, his_group TEXT, -- WRPS_ONE_SEC | WRPS_THIRTY_SEC | WRPS_EVENT scan_interval_seconds INT, -- NULL for the on-change group life_time TEXT, -- retention, "1 weeks" on every WRPS group ci_station TEXT, -- Modbus station, WRPS_PLC ci_point TEXT, -- CI Server point name, STN_LEVEL poll_group TEXT, -- PS_PUBLISHED | PS_STATUS_BITS | PS_SETPOINTS | PS_SIM_CONTROL iec_address TEXT, modbus_kind TEXT, modbus_address INT, data_type TEXT, point_time_zone TEXT, -- "Date+time GMT" on all 49 points CONSTRAINT historian_items_resolvable CHECK ( tag_id IS NOT NULL OR exclusion_reason IS NOT NULL OR his_group IS NULL ) ); CREATE INDEX IF NOT EXISTS historian_items_tag_ix ON historian_items (tag_id); CREATE INDEX IF NOT EXISTS historian_items_group_ix ON historian_items (his_group); -- ----------------------------------------------------------------------------- -- alarm_bits — how the PLC alarm word decomposes. -- -- Every alarm at this station is a bit of %QW17, historised as the item -- AID.WRPS.STN.ALARM_WORD. The discrete items (STN.HIGH_LEVEL, -- STN.SPILL_ACTIVE, PU30x.TRIPPED) mirror bits 0, 3 and 4-6 rather than -- being separate sources, so decomposing the word is the single derivation -- that produces every alarm — see cube/model/alarms.yml. -- -- WHY THIS IS REFERENCE DATA AND NOT A CUBE CONSTANT: the bit map is a -- property of the PLC program and it survives the cutover to imh unchanged. -- Putting it here means the equipment behind "PU-303 seal leak" is reached by -- bit -> tag_id -> tags.equipment_id, the same single assertion as everything -- else, instead of being a string inside a model file. -- -- READ THE WORD AS UNSIGNED. Bit 15 does not fit a signed INT, so a signed -- read turns the alarm word negative exactly when the most severe alarm sets. -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS alarm_bits ( bit INT PRIMARY KEY CHECK (bit BETWEEN 0 AND 15), alarm_type TEXT NOT NULL, priority INT NOT NULL CHECK (priority BETWEEN 1 AND 3), tag_id TEXT NOT NULL REFERENCES tags(tag_id), alarm_text TEXT, description TEXT ); -- ----------------------------------------------------------------------------- -- doc_chunks — controlled documents, chunked and embedded. -- -- superseded and effective_date matter more than they look. Citing a withdrawn -- revision of a procedure is worse than finding nothing at all, so retrieval -- filters superseded = FALSE by default and the citation always carries the -- revision and effective date. -- ----------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS doc_chunks ( id BIGSERIAL PRIMARY KEY, source_file TEXT NOT NULL, doc_type TEXT NOT NULL, -- procedure|manual|rationalisation|design doc_number TEXT, -- WRPS-CTL-002 revision TEXT, effective_date DATE, superseded BOOLEAN DEFAULT FALSE, equipment_id TEXT, page INT, section_title TEXT, chunk_text TEXT NOT NULL, embedding VECTOR(1536), -- text-embedding-3-small created_at TIMESTAMPTZ DEFAULT now(), CONSTRAINT doc_chunks_type_ck CHECK (doc_type IN ('procedure','manual','rationalisation','design')) ); CREATE INDEX IF NOT EXISTS doc_chunks_embedding_hnsw ON doc_chunks USING hnsw (embedding vector_cosine_ops); CREATE INDEX IF NOT EXISTS doc_chunks_live_type ON doc_chunks (doc_type) WHERE superseded = FALSE; CREATE INDEX IF NOT EXISTS doc_chunks_source ON doc_chunks (source_file); CREATE INDEX IF NOT EXISTS doc_chunks_equipment ON doc_chunks (equipment_id); -- Re-ingesting a file replaces its chunks; it must never duplicate them. -- ingest.py deletes by source_file inside the same transaction as the insert. -- ----------------------------------------------------------------------------- -- Cube writes its pre-aggregations into their own schema, with its own role. -- ----------------------------------------------------------------------------- CREATE SCHEMA IF NOT EXISTS cube_preagg; -- ============================================================================= -- Seed load. Aliases are pipe-separated in the CSVs because a Postgres array -- literal inside CSV is unreadable and unmergeable in review. Load via a -- staging table and split on load. -- -- psql -h pg-ai -U postgres -d plant -v ON_ERROR_STOP=1 <<'PSQL' -- \i 001_schema.sql -- CREATE TEMP TABLE eq_stage (LIKE equipment INCLUDING ALL); -- ALTER TABLE eq_stage ALTER COLUMN aliases TYPE TEXT; -- \copy eq_stage FROM 'seed/equipment.csv' WITH (FORMAT csv, HEADER true) -- INSERT INTO equipment -- SELECT equipment_id, display_name, string_to_array(aliases, '|'), -- equipment_type, unit_name, description -- FROM eq_stage -- ON CONFLICT (equipment_id) DO UPDATE SET -- display_name = EXCLUDED.display_name, aliases = EXCLUDED.aliases, -- equipment_type = EXCLUDED.equipment_type, unit_name = EXCLUDED.unit_name, -- description = EXCLUDED.description; -- PSQL -- -- Same shape for tags. scripts/deploy.sh does this for you. -- -- Phase 1 gate: every equipment item and every tag has at least one -- human-friendly alias. Check it, do not assume it: -- -- SELECT equipment_id FROM equipment WHERE coalesce(array_length(aliases,1),0) = 0; -- SELECT tag_id FROM tags WHERE coalesce(array_length(aliases,1),0) = 0; -- =============================================================================