Your Permit System Files Documents. The Hazard Sits on the Equipment.

On the night of 6 July 1988, the working condensate pump on Piper Alpha tripped. Condensate is the light liquid separated out of the gas stream. Without a pump to move it, the gas plant was heading for shutdown. The night shift wanted the standby, pump A, back.

Pump A was out for maintenance. Its only relief valve, PSV 504, had been removed during the day and the open pipe closed with a blind flange, a solid cap bolted over the end. That fact was on one permit, written against the valve's own tag number and location, one level above the pump. The control room filed active permits by where the equipment sat, so the pump's permits and the valve's were never filed together. The contractor handed the permit back at about 18:00, during shift changeover. The maintenance handover at 17:30 did not mention the valve.

The inquiry concluded, on a balance of probabilities, that condensate was let into pump A's discharge line and leaked from that flange, and that the night shift would not have tried had it known the valve was missing. The condensate vaporised into a cloud and ignited at about 22:00. 167 people died.

The removal was on a permit that nothing linked to pump A.

Permit software built as a digital copy of the paper permit repeats that filing decision. It also adds a failure the paper never had: it overwrites history.

What the permit rack taught the software

A paper permit system is a rack of sheets in the control room, filed by permit number or by area. Before issuing a new permit, the issuer checks the rack for conflicts. That check runs on memory and on knowing where to look.

Permit software specified by copying that rack—often built on generic form engines or SharePoint lists—treats a permit as a document with an approval workflow and a status: draft, issued, suspended, closed. The checks inside one permit can be thorough: isolations listed, gas tests recorded, every signature in place. Nothing checks the combination of permits on one piece of equipment.

Picture the supervisor at a quarter to ten at night, with a tripped pump and production waiting. They open the permit for the pump. The screen shows that permit's status. To learn that a valve on the same pump was removed under another permit, they would need to know to look for it. The system cannot answer the question that matters: what is wrong with this pump right now? Nothing in it stores the pump as the thing records attach to.

Some systems do check for conflicts. Area-based checks compare permits by location and flag overlapping work in the same zone. They miss a conflict between two pieces of equipment that sit in different places but work as one system. A relief valve on the level above a pump is in a different area. It is not a different hazard. The link between them is on the piping and instrumentation diagram (P&ID): the valve sits on the pump's discharge line. A zone check does not read the P&ID, so it treats the pump and the valve on the deck above it as unrelated.

If a system claims to link isolations to equipment tags, test it: when you open the next permit for that pump, does the software actively warn you that an isolation is still open, or is the tag number just dead text printed at the top of the certificate?

Two rules for a record keyed to the equipment

Rule 1: Every record links to the equipment it touches, and the equipment list knows what belongs to what.

The relief valve has its own tag number. If its records attach only to that tag, a search for pump A still misses them. The equipment list must record that the valve protects pump A, so a search for the pump includes its valve.

That list usually lives in the maintenance system (the CMMS, computerised maintenance management system), not in the permit system. Your EHS system must not try to become the master equipment ledger; its job is strictly to consume the engineering reality and attach risk conditions to it. The permit software must query the master CMMS hierarchy at the moment of issuance, because an imported copy will drift from reality before the end of the shift. The two must share tag numbers. If permit users type equipment names as free text, the link does not exist. This is a procurement and integration requirement. No database setting fixes it later.

Rule 2: A removed barrier stays open until something closes it.

"Relief valve removed" is not a status for the next record to replace. It is a condition that stays on the pump until a "relief valve refitted and tested" record closes it.

So the pump's current state is the list of open conditions, not its most recent record. A screen showing the latest activity on the pump shows whatever happened last, a gas test or an inspection. It says nothing about the valve removed hours earlier.

Why the history must not be rewritten

The paper permit kept every signature. Issued, suspended, revalidated: each was signed on the same sheet, and the sheet kept them all.

A permit database built like the rack keeps one row per permit with a status field. When the status changes, the field is overwritten. After a permit goes from Suspended back to Active, the row cannot show that it was ever suspended, who suspended it, or why.

The usual answer is "we have an audit log." Check what that log actually does:

• The audit log only records what happens on the screen. If an IT technician fixes a stuck permit directly in the database, or uploads a spreadsheet of records from the back end, they bypass the screen. The permit changes, but the software never writes an audit line.
• It records that someone typed in a box, not what was happening on site. "Status: Suspended → Active" is just a line in an edit history. To find out what the permit said at 10:15, someone has to piece those edits together by hand. A live screen cannot rebuild that history for fifty permits every time a supervisor refreshes the page—it only shows whatever is typed in the status box right now.
• The history is locked in an archive, not on the live screen. Vendors often save change histories to a separate reporting system for legal audits. That helps an investigator six months later, but the supervisor's live screen in the control room cannot check that archive during shift change.
• "Delete" usually just hides the permit. Most software doesn't erase a deleted record; it marks a hidden box to take it off the screen. Anyone with database access can unmark that box to bring the record back. If that change wasn't logged, a permit can vanish and reappear with no paper trail.

This costs you in the investigation. Investigators, insurers and lawyers ask when a record was written, when it was changed, and whether it was changed after the event. In a US court, a business record is normally admissible, but the other side can argue that the way it was kept shows it cannot be trusted (Federal Rule of Evidence 803(6)(E)). A record anyone could have changed without a trace gives them that argument. Timestamped Safety Records: Testimony, Not Proof covers the same problem for the time on the record.

The fix is an event log: every change is stored as a new line, and no line is ever edited or deleted. The current state is worked out from the lines. Record the change and its reason, not just the result. "Suspended at 10:15 after a gas alarm" and "restarted at 11:00 after a retest at 10:50" are different facts from "Active."

An inspector asks whether the controls were in place before hot work restarted. The log returns:

Time	Event	By
08:00	Permit issued, risk High, hot work authorised	Supervisor A
10:15	Gas alarm. Permit suspended	Safety Officer B
10:50	Gas retest: 0% of the lower explosive limit	Technician C
11:00	Hot work restarted	Supervisor A

With a single status field, the 11:00 restart overwrites the 10:15 suspension. The record shows a permit authorised for hot work, and nothing else. The gas alarm, the suspension and the 45 minutes between them are gone. The event log keeps them, and shows that the retest came before the restart.

One pump, two permits

The two rules work together. Here is an illustrative case. It is not a reconstruction of Piper Alpha's records. Pump P-101 has a relief valve, PSV-12, which the equipment list records as belonging to P-101.

Search by permit, as the paper rack did, and the 21:40 request finds PTW-114: an overhaul not yet started.

Search by equipment, including what belongs to it, and the system returns two open conditions on P-101: isolated under PTW-114, and relief valve missing under PTW-117. The second one blocks the start.

This is a specification requirement, not a report. The open conditions must appear on the screen where the start is requested, at the moment it is requested. Overriding them must name the permit being overridden and record who did it. A report someone has to remember to run does not help the supervisor at 21:40. If the maintenance records have the wrong equipment links, this block stops work that should go ahead. That delay will frustrate operations. It also makes each wrong link visible: a blocked start gets someone to fix the record, where a wrong link that blocks nothing stays wrong until the day it hides a real condition.

The Permit Equipment Sandbox runs the same check on Piper Alpha's day, on in-browser PostgreSQL. It puts two systems side by side: System A files permits by location, like the paper rack; System B keys every record to equipment.

Questions for your vendor

1. Show me every open condition on this pump and the equipment that belongs to it, on one screen, at the moment someone requests a start. If the answer is a search across permits, the records are filed by document.
2. Where does your equipment list come from, and does it record what belongs to what? If equipment is typed as free text on the permit, Rule 1 is not met.
3. Can any user, administrator, support engineer or connected system edit or delete a past record? If yes, the audit trail depends on nobody doing it.
4. Show me this permit's state at 10:15 last Tuesday. If someone has to rebuild it by hand from an audit log export, the system does not store history.

Conclusion

Piper Alpha's night shift did not lack paperwork. They lacked an answer to one question: what is wrong with this pump right now? A system that files by document and keeps only the latest status cannot answer it either. Connected isolation locks can report that a lock is on. They cannot report that a relief valve was taken off on the deck above. That fact reaches the control room through the records. Key every record to the equipment and never rewrite it, and the answer is there for the investigator afterwards. More importantly, it is there for the supervisor before the start.

---

For your implementation team

The rest of this article is for the people who build or configure the system. The core schema and triggers are below.

A complete reference implementation—including the PostgreSQL schema, automated tests, Docker configuration, and the in-browser PGlite sandbox—is available on GitHub at srhtdmrkl/permit-records-keyed-to-equipment.

What an overwrite destroys

UPDATE permits
SET status = 'Active', updated_at = NOW()
WHERE permit_id = 'PTW-117';

After this runs, the table cannot tell you what the status was before, who set it, or whether it was set after an incident. An in-place update destroys the previous row. Whether any record of the prior state survives depends entirely on application code writing a separate entry to an audit table—a step that direct database fixes and bulk scripts completely bypass.

The equipment list

This table mirrors the maintenance system's (CMMS) hierarchy so the database can link events to equipment and walk the tree. It is not a standalone register. As Rule 1 requires, refresh it from the CMMS when a permit is issued or a start is requested, not on a schedule.

CREATE TABLE assets (
    asset_id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tag              VARCHAR(50) NOT NULL UNIQUE,      -- 'P-101', 'PSV-12'; must match the maintenance system
    parent_asset_id  UUID REFERENCES assets(asset_id)  -- PSV-12 points to P-101
);

CREATE INDEX ON assets (parent_asset_id);

If one valve protects several pieces of equipment, replace parent_asset_id with a link table. A single parent cannot represent that.

The event log

CREATE SEQUENCE asset_events_seq;

CREATE TABLE asset_events (
    event_id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    asset_id          UUID NOT NULL REFERENCES assets(asset_id),
    permit_id         VARCHAR(50),
    event_type        VARCHAR(50) NOT NULL,  -- 'isolated', 'psv_removed', 'psv_refitted', 'permit_suspended', 'gas_test'
    opens_condition   BOOLEAN NOT NULL DEFAULT false,               -- true: this event leaves something open on the asset
    closes_event_id   UUID REFERENCES asset_events(event_id),       -- the event whose condition this one closes
    detail            JSONB NOT NULL DEFAULT '{}',
    reason            TEXT,
    recorded_by       VARCHAR(255) NOT NULL,
    device_timestamp  TIMESTAMPTZ NOT NULL,  -- when the worker's device says it happened
    server_ingest_ts  TIMESTAMPTZ NOT NULL,  -- when the server received it; set by trigger
    ingest_seq        BIGINT NOT NULL UNIQUE,-- arrival order; set by trigger
    prev_hash         BYTEA,
    event_hash        BYTEA NOT NULL
);

CREATE INDEX ON asset_events (asset_id) WHERE opens_condition;
CREATE UNIQUE INDEX ON asset_events (closes_event_id) WHERE closes_event_id IS NOT NULL;
CREATE INDEX ON asset_events (asset_id, device_timestamp);
CREATE INDEX ON asset_events (permit_id, ingest_seq);

What each part does:

1. opens_condition and closes_event_id implement Rule 2. "Relief valve removed" opens a condition. "Relief valve refitted and tested" closes it by pointing at the removal event.
2. device_timestamp and server_ingest_ts are both stored. A large gap between them flags a late sync or a changed device clock. The server value comes from the trigger below, so the device cannot set it.
3. ingest_seq is the order events reached the server, which is not the order they happened. An event recorded offline at 10:15 and synced at 13:00 arrives after events from 11:00. Use device_timestamp for what happened, server_ingest_ts for what the system knew, and the gap between them to decide how far to trust the first.
4. prev_hash and event_hash chain each event to the one before it. Each row carries a fingerprint of its own contents plus the previous row's fingerprint. Editing or deleting a row breaks the chain at that point.

Building the chain

-- One function computes the hash, for both writing and checking.
-- Fixed column list, so adding a column later does not change old hashes.
-- Fixed timezone, so the JSON text is identical in every session.
CREATE FUNCTION compute_event_hash(e asset_events) RETURNS BYTEA
LANGUAGE plpgsql
SET timezone = 'UTC'
AS $$
BEGIN
    RETURN sha256(coalesce(e.prev_hash, ''::bytea) || convert_to(json_build_array(
        e.event_id, e.asset_id, e.permit_id, e.event_type, e.opens_condition,
        e.closes_event_id, e.detail, e.reason, e.recorded_by,
        e.device_timestamp, e.server_ingest_ts, e.ingest_seq
    )::text, 'UTF8'));
END $$;

CREATE FUNCTION chain_event() RETURNS trigger
LANGUAGE plpgsql
-- Fixed search_path: a session cannot swap in its own temporary sequence or table.
SET search_path = public, pg_temp
AS $$
BEGIN
    -- The lock only works if the SELECT below sees rows committed while we waited.
    IF current_setting('transaction_isolation') <> 'read committed' THEN
        RAISE EXCEPTION 'asset_events inserts must run under READ COMMITTED';
    END IF;
    -- One writer at a time, so the chain stays a single line.
    -- Permit events arrive at human speed; serialising them is cheap.
    PERFORM pg_advisory_xact_lock(hashtext('asset_events_chain'));
    NEW.ingest_seq       := nextval('asset_events_seq');
    NEW.server_ingest_ts := clock_timestamp();
    SELECT event_hash INTO NEW.prev_hash
      FROM asset_events
     ORDER BY ingest_seq DESC
     LIMIT 1;
    NEW.event_hash := compute_event_hash(NEW);
    RETURN NEW;
END $$;

CREATE TRIGGER chain_before_insert
BEFORE INSERT ON asset_events
FOR EACH ROW EXECUTE FUNCTION chain_event();

The sequence number is taken after the lock, so arrival order and chain order always match. A rolled-back insert still uses up a number, so gaps in ingest_seq prove nothing. A row missing from the middle is caught by the chain, not by the counter. A row deleted from the end is caught only by the outside copies described below.

The fixed search_path matters. Without it, the trigger looks up asset_events_seq and asset_events in the inserting session's path, where temporary objects come first. The application role could then create its own temporary sequence and choose ingest_seq, the arrival order the device is not supposed to control.

Check the chain:

-- Returns every row where the chain is broken. An empty result means intact.
SELECT ingest_seq
FROM (
    SELECT e.ingest_seq,
           e.event_hash,
           e.prev_hash,
           compute_event_hash(e)                          AS recomputed,
           lag(e.event_hash) OVER (ORDER BY e.ingest_seq) AS expected_prev
    FROM asset_events e
) t
WHERE event_hash IS DISTINCT FROM recomputed
   OR prev_hash  IS DISTINCT FROM expected_prev;

The chain alone does not stop someone with full database access. They can edit a row and recompute every hash after it. Tampering becomes detectable only when the latest event_hash is regularly copied somewhere they cannot reach: the daily shift report email, write-once cloud storage, or a system run by a different team. Check the chain against those copies.

Making the log append-only in the database

-- The table and sequence belong to a role nobody logs in as
CREATE ROLE ledger_owner NOLOGIN;
ALTER TABLE asset_events OWNER TO ledger_owner;
ALTER SEQUENCE asset_events_seq OWNER TO ledger_owner;

-- The application can add and read, nothing else (assumes the permit_app login role already exists)
GRANT SELECT, INSERT ON asset_events TO permit_app;
GRANT USAGE ON SEQUENCE asset_events_seq TO permit_app;

-- Reject edits and deletes even from roles that hold the privilege
CREATE FUNCTION reject_change() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
    RAISE EXCEPTION 'asset_events is append-only';
END $$;

CREATE TRIGGER no_update_delete
BEFORE UPDATE OR DELETE ON asset_events
FOR EACH ROW EXECUTE FUNCTION reject_change();

-- TRUNCATE skips row triggers, so block it separately
CREATE TRIGGER no_truncate
BEFORE TRUNCATE ON asset_events
FOR EACH STATEMENT EXECUTE FUNCTION reject_change();

-- Ensure triggers fire even if a session sets session_replication_role = 'replica'
ALTER TABLE asset_events ENABLE ALWAYS TRIGGER chain_before_insert;
ALTER TABLE asset_events ENABLE ALWAYS TRIGGER no_update_delete;
ALTER TABLE asset_events ENABLE ALWAYS TRIGGER no_truncate;

REVOKE UPDATE, DELETE ... FROM PUBLIC is not enough on its own. PUBLIC holds no such rights by default, and the table owner keeps them regardless. ENABLE ALWAYS blocks the common session_replication_role = 'replica' bypass used by DBAs, but a superuser, or anyone who can act as ledger_owner, can still disable them. That is why the hash chain and its outside copies exist. If you copy this table to another database with logical replication, these triggers also fire there and recompute the sequence numbers, timestamps and hashes.

Open conditions on one piece of equipment

CREATE VIEW open_conditions AS
SELECT o.*
FROM asset_events o
WHERE o.opens_condition
  AND NOT EXISTS (
        SELECT 1 FROM asset_events c
        WHERE c.closes_event_id = o.event_id
  );

-- Everything open on P-101 and the equipment that belongs to it
WITH RECURSIVE tree AS (
    SELECT asset_id, tag FROM assets WHERE tag = 'P-101'
    UNION ALL
    SELECT a.asset_id, a.tag
    FROM assets a
    JOIN tree t ON a.parent_asset_id = t.asset_id
)
SELECT t.tag, oc.event_type, oc.reason, oc.permit_id, oc.recorded_by,
       oc.device_timestamp, oc.server_ingest_ts
FROM open_conditions oc
JOIN tree t USING (asset_id)
ORDER BY oc.device_timestamp;

Do not turn open_conditions into a materialised view, a stored copy refreshed on a schedule, for the start check. A copy refreshed every ten minutes can miss a valve removed five minutes ago. The indexes above keep the live query fast.

A "latest event per asset" view does not answer this question. If a gas test on P-101 was recorded after its isolation, the view shows the gas test and hides the isolation that is still open.

The core schema does not check closures. closes_event_id can point at any event: a gas test can close a relief valve removal, and so can a record on a different pump. The unique index stops only a second closure of the same event. Before go-live, refuse a closing record unless it is the right type for the condition, is on the same equipment, points at a condition that is still open, and is not dated before it. The reference implementation does this in app/sql/12_rules.sql.

State at a past moment

The investigator's question has two versions, and they give different answers.

-- What the system knew about P-101 and its equipment at 10:15, under any permit:
-- only events that had reached the server
WITH RECURSIVE tree AS (
    SELECT asset_id, tag FROM assets WHERE tag = 'P-101'
    UNION ALL
    SELECT a.asset_id, a.tag
    FROM assets a
    JOIN tree t ON a.parent_asset_id = t.asset_id
)
SELECT t.tag, e.event_type, e.permit_id, e.reason, e.recorded_by,
       e.device_timestamp, e.server_ingest_ts
FROM asset_events e
JOIN tree t USING (asset_id)
WHERE e.server_ingest_ts <= TIMESTAMPTZ '2026-09-22 10:15:00+00'
ORDER BY e.ingest_seq;

-- What had happened to P-101 and its equipment by 10:15, under any permit,
-- including events synced later. Check the gap column.
WITH RECURSIVE tree AS (
    SELECT asset_id, tag FROM assets WHERE tag = 'P-101'
    UNION ALL
    SELECT a.asset_id, a.tag
    FROM assets a
    JOIN tree t ON a.parent_asset_id = t.asset_id
)
SELECT t.tag, e.event_type, e.permit_id, e.reason, e.recorded_by,
       e.device_timestamp, e.server_ingest_ts,
       e.server_ingest_ts - e.device_timestamp AS sync_gap
FROM asset_events e
JOIN tree t USING (asset_id)
WHERE e.device_timestamp <= TIMESTAMPTZ '2026-09-22 10:15:00+00'
ORDER BY e.device_timestamp;

Both queries walk today's equipment list. If a link has changed since 10:15, they use the new one, and nothing in the log shows the difference. To answer with the equipment list as it stood, store each link with the dates it was valid, or record link changes as events in the log.

If you cannot rebuild the platform

Some databases keep history for you. SQL Server and MariaDB have system-versioned temporal tables: every update or delete keeps the old row with start and end times. SQL Server moves it to a separate history table, while MariaDB keeps it in the same table unless you partition by SYSTEM_TIME.

-- SQL Server
CREATE TABLE permits (
    permit_id        VARCHAR(50) PRIMARY KEY,
    equipment_tag    VARCHAR(50) NOT NULL,
    status           VARCHAR(50) NOT NULL,
    valid_from       DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    valid_to         DATETIME2 GENERATED ALWAYS AS ROW END   NOT NULL,
    PERIOD FOR SYSTEM_TIME (valid_from, valid_to)
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.permits_history));

PostgreSQL has no built-in equivalent. It needs an extension or hand-written triggers.

Know the limits. Temporal tables stop the application from rewriting history. A privileged administrator can switch versioning off and edit the history rows. SQL Server 2022 and Azure SQL also offer ledger tables, which can be append-only and can store their hash digests outside the database. That makes administrator tampering detectable, provided someone checks the database against those outside digests. Neither option changes what records are keyed to. History is kept per table, not per piece of equipment, so the problem at the top of this article remains. Treat these as a minimum, not the fix.