Replace proprietary OSIsoft / AVEVA PI System per-tag and per-seat licensing with a mathematically lossless, 4-Pillar industrial time-series historian powered by ClickHouse 24.8 LTS and PostgreSQL 16—complete with cryptographic SHA-256 WORM audit trails, §11.200 electronic signatures, and visual wiring playbooks for 35+ GMP instruments.
# 1. Launch all 5 GxP services (Edge WAL, ClickHouse, PG16, FastAPI, Grafana)
$ cp .env.example .env && docker compose up -d --build
# 2. Verify cryptographic SHA-256 WORM audit hash chain from GENESIS
$ curl -s http://localhost:8020/api/v1/audit-trail/verify | jq .
{
"chain_valid": true,
"verified_records": 220,
"genesis_anchor": "GENESIS_0000000000000000",
"broken_links": []
}
# 3. Execute 15-test GAMP 5 / FDA CSA IQ & OQ qualification suite
$ .venv/bin/pytest -v
15 passed in 2.41s — Evidence written to oq_execution_results.json
Legacy industrial historians were designed in the 1980s around scarcity of disk space, relying on lossy "Swinging Door" compression and fragile Windows DCOM plumbing. GxPHistorian modernizes every layer for ALCOA+ data integrity.
Traditional historians discard raw sensor readings using lossy CompDev swinging-door straight-line approximations—risking concealed thermal spikes or blunted chromatography UV280 peaks during GMP batch review. GxPHistorian applies Stage 1 Exception Deadband filtering (ExcDev/ExcMax) at the edge and archives every emitted sample losslessly in ClickHouse using DoubleDelta + Gorilla + ZSTD(1) at 1.5–2.5 bytes/sample.
Application-only audit logs can be bypassed by direct SQL access or software bugs. GxPHistorian enforces 21 CFR Part 11 directly inside PostgreSQL 16 PL/pgSQL triggers: every mutation on assets, tags, or event_frames requires app.actor_id and app.change_reason, appends a SHA-256 hash-chained record to part11_audit_log, and unconditionally blocks UPDATE, DELETE, and TRUNCATE—even by database superusers.
quality ≥ 192) & Step Interpolation
Sensor wire breaks and CIP maintenance cycles routinely emit sentinel values (such as -999.0 with OPC Bad quality 0). GxPHistorian archives raw diagnostic states while strictly filtering WHERE quality ≥ 192 across all 1-minute rollups, LTTB visual trends, Event Frame KPIs, and Excel DataLink queries—and respects LINEAR vs. discrete LOCF step tags so agitator states never interpolate to 1.5.
GxPHistorian decouples high-frequency append-only sensor ingestion (ClickHouse 24.8 LTS) from transactional ISA-95 asset state, ISA-88 Event Frames, and 21 CFR Part 11 WORM audit trails (PostgreSQL 16)—bridged in real time via an in-memory ClickHouse FLAT dictionary.
Normalizes OPC UA (:4840), Classic Windows OPC DA 2.05a (Zero-DCOM bridge), BACnet/IP (:47808 COV) & MS/TP, Siemens S7comm (:102), Modbus TCP/RTU, 4–20 mA HART, RS-232 MT-SICS balances, and SMB Watch-Folders with Stage 1 Exception/Deadband filtering (ExcDev/ExcMax) and a 1,000,000-sample disk Write-Ahead Log.
Explore 10 Protocol Connectors →Replaces lossy Swinging Door compression with 100% mathematically lossless DoubleDelta + Gorilla + T64 + ZSTD(1) codecs (1.5–2.5 bytes/sample). Pre-computes real-time 1-minute AggregatingMergeTree rollups filtered strictly on Good OPC quality (WHERE quality >= 192) and serves LTTB peak-preserving visual trends.
Inspect ClickHouse Schema & Codecs →Manages ISA-95 ltree asset paths, GxP Tag Catalog, and ISA-88 Batch/Excursion Event Frames. Kernel-level PL/pgSQL triggers enforce mandatory X-Actor-Id & X-Change-Reason attribution, an immutable SHA-256 hash-chained WORM audit log, and §11.200 dual-challenge HMAC-SHA256 electronic signatures.
Review 21 CFR Part 11 Controls →Delivers a pre-provisioned asset-relative Bioreactor & Cleanroom Operator HMI with Event Frame overlays, drop-in Excel DataLink formulas (=GXPHISTORIAN_SAMPLED, =GXPHISTORIAN_PLOT_VALUES, =GXPHISTORIAN_EVENT_SUMMARY) + zero-macro Power Query CSV streams (LINEAR vs. LOCF), and Snappy Apache Parquet exports.
View HMI & Excel DataLink Guides →
Every familiar PI System capability—from PI Asset Framework and PI Event Frames to PI Vision LTTB trends and Excel PI DataLink formulas—maps cleanly to an open-source, auditable GxPHistorian subsystem.
| Legacy OSIsoft / AVEVA PI Component | GxPHistorian Open-Source Equivalent | Key Technical & Regulatory Capabilities |
|---|---|---|
| PI Data Archive (Swinging Door CompDev) | ClickHouse 24.8 LTS (pi_historian.sensor_data) | 100% mathematically lossless Delta, DoubleDelta, Gorilla & T64 + ZSTD(1) compression (1.5–2.5 B/sample) + 1m AggregatingMergeTree rollups (WHERE quality >= 192). |
| PI Asset Framework (PI AF) | PostgreSQL 16 ltree + ClickHouse FLAT Dictionary | ISA-95 Enterprise.Site.Area.Unit paths indexed via GiST and synced into ClickHouse RAM (pg_asset_tags) for O(1) metadata enrichment. |
| PI Event Frames (EF) | PostgreSQL event_frames + FastAPI Engine | Automated ISA-88 batch & excursion capture (peak_val, avg_val, min_val over quality >= 192) with §11.200 dual-challenge HMAC-SHA256 electronic signatures. |
| PI Interface Node & PI Buffer Subsystem | Telegraf 1.32 Disk WAL + Python Edge Workers | In-memory Stage 1 Exception/Deadband filtering (ExcDev & ExcMax) + 1,000,000-metric disk store-and-forward WAL across 10 industrial protocols. |
| PI Vision (PlotValues Trends & Overlays) | Grafana OSS 11.2 + ClickHouse LTTB | Asset-relative (${asset_path}) operator HMI with native largestTriangleThreeBuckets peak preservation, Event Frame bands, and WORM audit table. |
| PI DataLink (Excel PISampDat / PIAdvCalcDat) | FastAPI DataLink CSV/JSON + xlwings UDFs | Zero-macro Excel Power Query CSV streams & =GXPHISTORIAN_SAMPLED() UDFs enforcing LINEAR analog vs. LOCF discrete step interpolation. |
| PI Integrator for Business Analytics | FastAPI + PyArrow Zero-ETL Exporter | Single-call POST /api/v1/lakehouse/export-parquet generating Snappy-compressed Apache Parquet files enriched with ISA-95 asset context. |
Auditors no longer accept "trust the web application" for electronic batch records and excursion investigations. GxPHistorian enforces attribution, immutability, and non-repudiable electronic signatures inside PostgreSQL 16 and ClickHouse.
§11.10(e)): Every DML change on asset_templates, assets, tags, and event_frames requires app.actor_id and app.change_reason or raises an immediate kernel exception.
trg_worm_part11_audit_log_stmt and trg_worm_part11_audit_log_row block any UPDATE, DELETE, or TRUNCATE on part11_audit_log.
prev_hash || recorded_at || actor_id || operation || new_payload from GENESIS_0000000000000000, verifiable via GET /api/v1/audit-trail/verify.
§11.50, §11.70, §11.200): Verifies bcrypt credentials in users and binds an HMAC-SHA256 digest over (event_id, user_id, signature_meaning, signed_at).
-- Enforce mandatory 21 CFR Part 11 attribution context
IF v_actor IS NULL OR v_reason IS NULL THEN
RAISE EXCEPTION '21 CFR Part 11 Violation: app.actor_id and app.change_reason are mandatory';
END IF;
-- Compute cryptographic SHA-256 hash chain link
v_row_hash := encode(
digest(
v_prev_hash || v_recorded_at::text || v_actor || TG_OP ||
COALESCE(v_new_payload::text, ''),
'sha256'
),
'hex'
);
-- Unconditional WORM immutability (blocks even superusers)
CREATE TRIGGER trg_worm_part11_audit_log_stmt
BEFORE UPDATE OR DELETE OR TRUNCATE ON part11_audit_log
FOR EACH STATEMENT EXECUTE FUNCTION fn_prevent_audit_tampering();
Every integration guide in gxphistorian.com/docs includes photorealistic hardware identification, vector rear-panel port callouts (highlighting internal PLC I/O ports never to unplug), exact gateway part numbers, menu click-paths, and copy-paste tag provisioning commands.
OPC UA (:4840) & Zero-DCOM OPC DA XDR-50 to XDR-2000 • Emerson DeltaV™ • Rockwell PlantPAx®
Open Visual Integration Guide →
OPC UA (:4840) & Siemens S7-1500 BioPAT® MFCS • Ambr® 15/250 • TruBio® DV • BioFlo® / DASGIP®
Open Visual Integration Guide →
OPC UA, Modbus RTU/TCP & Watchfolder (exc_dev=0) Nova BioProfile® FLEX2 • Vi-CELL™ BLU • Cedex® Bio HT • Raman Rxn2/4 • Hamilton Arc®
Open Visual Integration Guide →
OPC UA / OPC DA + LTTB UV280 Peak Preservation ÄKTA process™ / ready™ / readyflux™ • Sartoflow® • Mobius® / Cogent®
Open Visual Integration Guide →
OPC UA (500 ms), RS-232 TCP & COI_ID Event Frames CliniMACS Prodigy® • Cytiva Sefia™ & Sepax™ C-Pro • NanoAssemblr™ • Planer Kryo 560
Open Visual Integration Guide →
BACnet/IP (:47808 COV), Modbus TCP (:502) & S7comm SP Scientific / Telstar Lyo • SKAN Isolators • PMS LasAir® III • Veolia Sievers® M9
Open Visual Integration Guide →
Modbus TCP, Adam-6015 RTD + ClickHouse MKT & F₀ SQL BINDER KBF • Infors HT / Heracell™ • PHCbi / Thermo TSX • Fedegari / Getinge
Open Visual Integration Guide →
Moxa NPort TCP (:4001 MT-SICS) & SHA-256 Watchfolders Mettler XPR • Sartorius Cubis® II • OsmoTECH® XT • Sartocheck® 5 • Palltronic®
Open Visual Integration Guide →
Moxa NPort • Advantech ADAM • LOYTEC BACnet • Helmholz S7 RJ45 T568B • M12 D/X/A-Coded • DB9 RS-232/MPI • RS-485 • 4–20 mA HART
Open Visual Integration Guide →
Generated automatically from urs_risk_catalog.yaml, live Docker container @sha256 digests, and executed pytest IQ/OQ evidence logs (oq_execution_results.json). Browse online in /docs or export the single-file print-ready HTML/PDF binder for QA sign-off.