Tables now locked on close
11
All critical records
Previously missing lock
6
Now remediated
Edit attempts blocked
0
Since lock implementation
Audit standard
IATF · ISO 45001
Immutable evidence required
Immutability coverage — all critical tablesPostgreSQL trigger-enforced
| Table | Lock trigger | Locked when | Was covered | Now covered | Compliance |
|---|---|---|---|---|---|
Job_Cards | status = 'Closed' | Technician closes job card | No | Added | IATF 8.5.1.5 |
RCA_Reports | status = 'Closed' | Root cause confirmed & CAs raised | No | Added | IATF 8.5.1.5 |
Corrective_Actions | status = 'Complete' | CA marked complete by owner | No | Added | IATF 8.5.1.5 |
Safety_Verification | On insert | PPE confirmed — immediately locked | Partial | Added | ISO 45001 |
Toolbox_Audit | On submit | Pre-shift audit submitted | No | Added | ISO 45001 |
Asset_Timeline | On insert | Event logged — immediately immutable | No | Added | ISO 22163 |
Permits | status = 'Closed' | PTW closed & co-signed | Yes | Confirmed | ISO 45001 |
Permit_Approvals | On insert | Each signature — immediately locked | Yes | Confirmed | ISO 45001 |
Shift_Handovers | signed_in_at NOT NULL | Incoming supervisor signs off | Yes | Confirmed | Alstom req. |
Notification_Log | On insert | Every notification — born locked | Yes | Confirmed | ISO 45001 |
Handover_Items | Parent handover locked | When parent handover is signed | No | Added | Alstom req. |
Trigger implementation — PostgreSQL
-- Universal immutability trigger function
CREATE OR REPLACE FUNCTION enforce_immutability()
RETURNS TRIGGER AS $$
BEGIN
IF OLD.is_immutable = TRUE THEN
RAISE EXCEPTION
'Record % in table % is immutable'
'. Modification denied. Ref: %',
OLD.id, TG_TABLE_NAME,
OLD.record_hash;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- Apply to Job_Cards (your suggestion — now implemented)
CREATE TRIGGER trg_lock_job_cards
BEFORE UPDATE OR DELETE ON Job_Cards
FOR EACH ROW EXECUTE FUNCTION
enforce_immutability();
-- Lock job card on closure
CREATE OR REPLACE FUNCTION lock_on_close()
RETURNS TRIGGER AS $$
BEGIN
IF NEW.status = 'Closed'
AND OLD.status != 'Closed' THEN
NEW.is_immutable := TRUE;
NEW.closed_at := NOW();
NEW.record_hash := compute_hash(NEW);
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- Same pattern applied to RCA_Reports,
-- Corrective_Actions, Safety_Verification,
-- Toolbox_Audit, Asset_Timeline
What immutability prevents
Technician editing a closed job cardBlocked
Manager amending a signed PTWBlocked
Admin deleting an RCA reportBlocked
Direct DB UPDATE bypassing appBlocked via trigger
Safety sign-off timestamp alterationBlocked
Audit trail deletionBlocked
Backdating a closed shift handoverBlocked
Re-opening a closed corrective actionBlocked — new CA required
IATF 16949 / Alstom audit implication: An auditor can run
SELECT * FROM Job_Cards WHERE is_immutable = FALSE AND status = 'Closed' and confirm zero results — proving every closed record is tamper-proof. This is a direct audit artefact.
SHA-256 record integrity hashingTamper detection
When a record is locked, the system computes a SHA-256 hash of all critical fields and stores it in
record_hash. At any time — including during an IATF audit — the hash can be recomputed and compared. Any discrepancy, even a direct database edit, is instantly detectable.Live hash verification — Job Card #1042
Job ID#1042
AssetBogie Press 3
TechnicianT.Nkosi · UUID:a7f3...
Closed at2026-04-01T14:23:11Z
MTTR (min)252
Safety sign-off GPS-25.7593, 28.1841
Stored record_hash (SHA-256)
a3f9d2c81e4b7605f2a9e3d18c7b2640f5e1a9c3b2d7e8f4a1c6b3e2d9f7a4b1
Recomputed hash (on-demand verification)
— Click verify to compute —
Tampered record simulation — what a DB edit looks like
a3f9d2c81e4b7605f2a9e3d18c7b2640f5e1a9c3b2d7e8f4a1c6b3e2d9f7a4b1
b7e2a9f3c1d4e8607a1f2e9d48c3b51029d3e8c7b4d6f1a8c5b9e3d2f8a7b6c2
✗ Hash mismatch — record tampered. Security alert raised.
Hash schema additions
-- Column added to all lockable tables
ALTER TABLE Job_Cards
ADD COLUMN record_hash TEXT,
ADD COLUMN hash_algorithm
VARCHAR(10) DEFAULT 'SHA-256',
ADD COLUMN hash_verified_at
TIMESTAMP NULL;
-- Hash computation function
CREATE OR REPLACE FUNCTION
compute_hash(rec Job_Cards)
RETURNS TEXT AS $$
SELECT encode(
sha256(
concat_ws('|',
rec.job_id::text,
rec.asset_id::text,
rec.assigned_to::text,
rec.closed_at::text,
rec.work_performed,
rec.mttr_minutes::text,
rec.safety_confirmed_at::text,
rec.gps_lat::text,
rec.gps_lng::text
)::bytea
), 'hex'
);
$$ LANGUAGE sql IMMUTABLE;
-- Applied identically to:
-- RCA_Reports, Safety_Verification,
-- Permits, Shift_Handovers, Toolbox_Audit
Fields included in hash per table
Job_Cardsid, asset, tech, closed_at, MTTR, GPS, safety_ts
RCA_Reportsid, asset, failure_mode, root_cause, closed_at
Permitsid, type, asset, raised_by, valid_until, loto
Safety_Verificationjob_id, ppe_items, GPS, device_id, confirmed_at
Shift_Handoversid, out_sup, in_sup, signed_out_at, signed_in_at
Toolbox_Auditid, user_id, timestamp, missing_items
Field-level change audit log — live feedAll changes captured
Job_Cards · #1042 — field
work_performed updated. Old: "HV isolation complete…" → New: "HV isolation complete. Replaced terminal block."Job_Cards · #1042 — field
status changed: 'In-Progress' → 'Closed'. is_immutable set TRUE. record_hash computed.Job_Cards · #1042 — UPDATE attempt on locked record. Field:
mttr_minutes. BLOCKED by trigger. Security alert raised to Admin.RCA_Reports · #009 — field
root_cause updated. Old: NULL → New: "NBR seal incompatible at 80°C operating temp."Users · T. Nkosi — field
is_active changed: TRUE → FALSE (kill-switch). All sessions revoked.Security_Policy — field
enforce_2fa_login changed: FALSE → TRUE by J. du Plessis. All active sessions will require 2FA on next request.Audit_Changes schema
CREATE TABLE Audit_Changes (
change_id BIGSERIAL PRIMARY KEY,
table_name VARCHAR(50) NOT NULL,
record_id TEXT NOT NULL,
field_name VARCHAR(100) NOT NULL,
old_value TEXT,
new_value TEXT,
changed_by UUID REFERENCES Users,
changed_at TIMESTAMP DEFAULT NOW(),
session_id UUID REFERENCES Sessions,
device_id VARCHAR(100),
ip_address INET,
was_blocked BOOLEAN DEFAULT FALSE,
-- TRUE = attempt on locked record
is_immutable BOOLEAN DEFAULT TRUE
-- The log itself is never editable
);
-- Generic audit trigger applied to all tables
CREATE OR REPLACE FUNCTION log_changes()
RETURNS TRIGGER AS $$
DECLARE
col TEXT;
BEGIN
FOREACH col IN ARRAY
hstore_to_matrix(hstore(NEW)
- hstore(OLD))
LOOP
INSERT INTO Audit_Changes
VALUES(DEFAULT,TG_TABLE_NAME,
NEW.id::text, col,
(OLD::hstore)->col,
(NEW::hstore)->col,
current_setting('app.user_id')::uuid,
NOW(), NULL, NULL, NULL, FALSE, TRUE);
END LOOP;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
Tables with audit trigger
Job_CardsAll fields
RCA_Reports + CAsAll fields
Permits + ApprovalsAll fields
Users + RolesAll fields
Security_PolicyAll fields
Critical_SparesStock + thresholds
Audit_Changes itselfNo UPDATE/DELETE allowed
Row-level security — DB-enforced access matrixPostgreSQL RLS policies
RLS policies sit below the application layer — even if a bug in app code fails to filter results, the database itself enforces what each role can see and modify. Technicians can only touch their own assigned records. The database validates
current_setting('app.user_id') and current_setting('app.role') on every query.Table · Operation
Admin
Manager
Technician
Auditor
Job_Cards — SELECT
All
Plant
Own
All
Job_Cards — UPDATE
—
Plant, open
Own, open
—
Job_Cards — DELETE
Never
Never
Never
Never
RCA_Reports — SELECT
All
Plant
Own
All
Users — SELECT
All
Own plant
Self only
All (read)
Safety_Verification — SELECT
All
Plant
Own
All
Critical_Spares — UPDATE
All
Own plant
—
—
Audit_Changes — SELECT
All
—
—
All
Audit_Changes — UPDATE/DELETE
Never
Never
Never
Never
All
Full access to all rows
Plt
Own plant / facility only
Own
Only rows assigned to self
—
No access / operation not permitted
RLS policy implementation
-- Enable RLS on Job_Cards
ALTER TABLE Job_Cards
ENABLE ROW LEVEL SECURITY;
-- Technicians: own assigned records only
CREATE POLICY tech_own_jobs
ON Job_Cards
FOR ALL TO technician_role
USING (
assigned_to = current_setting(
'app.user_id')::uuid
);
-- Managers: all records in their plant
CREATE POLICY mgr_plant_jobs
ON Job_Cards
FOR SELECT TO manager_role
USING (
asset_id IN (
SELECT asset_id FROM Assets
WHERE plant_id = current_setting(
'app.plant_id')::int
)
);
-- Auditors & Admins: bypass RLS
ALTER TABLE Job_Cards
FORCE ROW LEVEL SECURITY;
-- Admin role set BYPASSRLS = FALSE
-- prevents accidental full access
-- Prevent DELETE on ALL roles (belt + braces)
CREATE POLICY no_delete
ON Job_Cards
AS RESTRICTIVE
FOR DELETE TO PUBLIC
USING (FALSE);
-- Same applied to all critical tables
Offline data signing — cryptographic field proofECDSA P-256
When a technician completes a job card in a dead zone (inside a train shell, blast pit, or shielded area), the device signs the entire record payload with a private key stored in the device's secure enclave — the same key used for biometric login. On sync, the server verifies the signature. This makes it cryptographically impossible to fabricate or backdated an offline record.
1
Job card completed offline
Technician closes job card in a dead zone. Device captures all fields + current device clock timestamp + last known GPS.
2
Payload serialised & signed
Device serialises all fields to canonical JSON, then signs the SHA-256 hash of that JSON with the user's ECDSA P-256 private key stored in the secure enclave. The private key never leaves the device.
3
Signature stored locally
The
offline_signature and offline_signed_at are stored alongside the job card in local SQLite. The record is marked pending_sync.4
On reconnection — server verifies
Server receives the payload + signature. It fetches the user's stored public key from
Biometric_Tokens and verifies the ECDSA signature. If verification fails, the record is rejected and a security alert is raised.5
Timestamp cross-validation
Server checks
offline_signed_at against server time. If the device clock diverges by more than ±5 minutes at the time of signing, the record is flagged for admin review — preventing clock manipulation attacks.Offline signing schema
-- Added to Job_Cards and Safety_Verification
ALTER TABLE Job_Cards
ADD COLUMN offline_signature
TEXT NULL,
-- ECDSA P-256 signature (base64)
ADD COLUMN offline_signed_at
TIMESTAMP NULL,
-- Device clock at signing time
ADD COLUMN signature_verified
BOOLEAN DEFAULT NULL,
-- NULL=pending, TRUE=ok, FALSE=failed
ADD COLUMN clock_drift_seconds
INT NULL;
-- |device_time - server_time| on sync
-- Verification function (server-side)
CREATE OR REPLACE FUNCTION
verify_offline_signature(
p_job_id INT,
p_user_id UUID
) RETURNS BOOLEAN AS $$
DECLARE
pub_key TEXT;
payload TEXT;
sig TEXT;
BEGIN
-- Fetch public key from biometric token
SELECT public_key INTO pub_key
FROM Biometric_Tokens
WHERE user_id = p_user_id
AND is_active = TRUE LIMIT 1;
-- Reconstruct canonical payload
SELECT row_to_json(jc)::text,
offline_signature
INTO payload, sig
FROM Job_Cards jc
WHERE job_id = p_job_id;
-- ECDSA verify (via pgcrypto)
RETURN verify(
sha256(payload::bytea),
decode(sig, 'base64'),
decode(pub_key, 'base64'),
'ecdsa-with-SHA256'
);
END;
$$ LANGUAGE plpgsql;
Threats detected this week
3
2 auto-blocked
Unrecognised devices blocked
2
Challenge required
Off-hours logins flagged
1
Admin notified
Geofence violations
0
This week
Active threat feed3 events this week
Unrecognised device — T. NkosiHigh
Login attempted from device
MOBILE-UNKNOWN-44f2 — not in the user's registered device list. Location: 4.2km outside plant geofence. Time: 02:14 (outside shift hours).Off-hours login — R. AdamsMedium
Manager login at 23:47 — 5.8 hours outside normal session window (06:00–18:00). Device is registered. Location is within plant boundary. Could be legitimate late shift.
Rapid credential cycling — unknown IPMedium
4 failed login attempts from
196.23.41.87 in 90 seconds targeting different usernames. Pattern consistent with credential stuffing. IP auto-blocked after attempt 3.Detection rules — admin configurable
Unrecognised device
Block and challenge login from any device not in the user's registered device list
Outside plant geofence
Flag logins from GPS coordinates more than 500m outside the defined plant boundary
Off-hours login
Alert when a user logs in more than 2 hours outside their registered shift pattern
Credential stuffing pattern
Auto-block IP after 3 failed attempts across any accounts within 120 seconds
Impossible travel detection
Flag if two sessions for the same user are created from locations >100km apart within 30 minutes
Concurrent session from multiple continents
Auto-revoke all sessions and require admin unlock if sessions detected from different countries simultaneously
Suspicious_Logins schema
CREATE TABLE Suspicious_Logins (
threat_id BIGSERIAL PRIMARY KEY,
user_id UUID REFERENCES Users,
threat_type VARCHAR(50),
-- UnknownDevice/Geofence/OffHours
-- CredentialStuffing/ImpossibleTravel
severity VARCHAR(10),
-- High/Medium/Low
device_id VARCHAR(100),
ip_address INET,
gps_lat DECIMAL NULL,
gps_lng DECIMAL NULL,
detected_at TIMESTAMP DEFAULT NOW(),
auto_blocked BOOLEAN DEFAULT FALSE,
resolution VARCHAR(30) NULL,
-- Blocked/Legitimate/Investigating
resolved_by UUID REFERENCES Users,
is_immutable BOOLEAN DEFAULT TRUE
);
Admin action audit log — immutable insider threat protectionNever editable — even by admins
Every admin action is permanently logged in a table that no role — including Admin — can UPDATE or DELETE. A separate Super_Auditor role (held externally, e.g. by a compliance officer) has read-only SELECT access. This protects against insider threat and satisfies IATF 16949 clause 5.3 (organisational roles and responsibilities).
🔒
Module disabled — J. du Plessis toggled
HSE_Tracker module OFF for Plant C. Reason field: "Plant C does not require HSE module." Previous state: ON.Today 09:12
👤
User disabled (kill-switch) — J. du Plessis set T. Nkosi
is_active = FALSE. All 1 active sessions revoked. Reason: "Device reported lost on factory floor."Today 09:44
🔐
Security policy changed — J. du Plessis set
enforce_2fa_login = TRUE. Was: FALSE. All sessions will require 2FA on next API call. Notified: all users via push.Yesterday 16:02
👥
Role changed — J. du Plessis changed K. Mokoena role from
Technician → Manager. RBAC permissions updated. Session invalidated — re-login required.Yesterday 14:30
📧
Notification trigger modified — J. du Plessis added
[email protected] to breakdown trigger recipients. Previous recipients logged for audit comparison.2 days ago 11:15
📤
Data export — J. du Plessis exported full Job_Cards report (Jan–Mar 2026). 2,847 records. Export watermarked with user ID + timestamp. Delivery: email to [email protected].
2 days ago 09:55
Admin_Action_Log schema
CREATE TABLE Admin_Action_Log (
log_id BIGSERIAL PRIMARY KEY,
admin_id UUID REFERENCES Users,
action_type VARCHAR(50),
-- ModuleToggle/UserDisable/RoleChange
-- PolicyChange/Export/UserCreate
target_table VARCHAR(50),
target_id TEXT,
old_value JSONB,
new_value JSONB,
reason TEXT,
-- Mandatory free-text justification
ip_address INET,
session_id UUID,
logged_at TIMESTAMP DEFAULT NOW(),
is_immutable BOOLEAN DEFAULT TRUE
);
-- NO role can delete from this table
CREATE POLICY no_delete_admin_log
ON Admin_Action_Log
AS RESTRICTIVE FOR DELETE
TO PUBLIC USING (FALSE);
-- NO role can update this table
CREATE POLICY no_update_admin_log
ON Admin_Action_Log
AS RESTRICTIVE FOR UPDATE
TO PUBLIC USING (FALSE);
-- Super_Auditor: SELECT only (external role)
GRANT SELECT ON Admin_Action_Log
TO super_auditor_role;
Export watermarking — chain of custodyEvery PDF & report
Every report, job card PDF, audit export, or data download is watermarked with the requesting user's ID, name, timestamp, and a unique export token. This creates a traceable chain of custody — if a document is leaked or misused, the source user is identifiable.
Preview — Job Card Closure Report
SIEBENCMMS
Job Card #1042 — Closure Report
Asset: Bogie Press 3 · Plant B · 1 Apr 2026
Technician: T. Nkosi · Work performed: HV transformer terminal block replaced. LOTO removed. Area reinstated. Safety sign-off confirmed at 09:43 via GPS -25.7593, 28.1841. MTTR: 4.2h. Job closed at 14:23.
Record hash: a3f9d2c81e4b...b1 · Verified ✓
Exported by: J. du Plessis (Admin)
User ID: uuid:a7f3-c2d1-...
Export token: EXP-2026-04-01-7742
Timestamp: 2026-04-01T15:44:22Z
System: SiebenCMMS v2.4.1
User ID: uuid:a7f3-c2d1-...
Export token: EXP-2026-04-01-7742
Timestamp: 2026-04-01T15:44:22Z
System: SiebenCMMS v2.4.1
How it works: The watermark is embedded as both visible metadata (footer) and invisible PDF metadata (XMP fields). The diagonal text is rendered at low opacity in the page background. The export token is logged in
Admin_Action_Log — cross-referencing the token to the log confirms who exported what and when.
Data_Exports schema & watermark fields
CREATE TABLE Data_Exports (
export_id BIGSERIAL PRIMARY KEY,
export_token VARCHAR(30) UNIQUE,
-- e.g. EXP-2026-04-01-7742
requested_by UUID REFERENCES Users,
export_type VARCHAR(30),
-- JobCards/RCA/PTW/Audit/Full
filter_params JSONB,
-- Date range, plant, asset filters
record_count INT,
file_hash TEXT,
-- SHA-256 of the exported file
watermark_token TEXT,
exported_at TIMESTAMP DEFAULT NOW(),
delivered_to TEXT,
-- Email or download IP
is_immutable BOOLEAN DEFAULT TRUE
);
-- Watermark fields embedded in every PDF
-- via PDF metadata (XMP) + visible footer:
-- dc:creator = user display name
-- dc:identifier = export_token
-- xmp:CreateDate = exported_at
-- custom:SiebenUserID = user UUID
-- custom:SiebenVersion = app version
Plant geofence — live device positionsAdmin configurable boundary
Plant B boundary (500m radius)
T. Nkosi ✓
M. Pieterse ✓
R. Ndlovu ✓
S. Dlamini ⚠ boundary
UNKNOWN-44f2 ✗ 4.2km out
● Inside
● Boundary
● Outside (blocked)
Geofence enforcement: Safety sign-offs, job card closures, and PTW approvals are blocked if the device GPS is outside the defined plant boundary. The boundary is configurable per plant in the Admin console. Devices near the boundary (within 50m) receive a warning before action is blocked.
Geofence rules — admin configurable
Block safety sign-offs outside boundary
Prevents PPE confirmation from being submitted outside the plant
Block job card closure outside boundary
Technician must be on-site to close a job card
Block PTW approval outside boundary
All PTW signatories must be on-site
Alert admin on boundary violation
Instant notification when any user attempts an action outside the geofence
Warn at 50m from boundary
Users receive an in-app warning before reaching the hard boundary
Plant_Boundaries schema
CREATE TABLE Plant_Boundaries (
boundary_id SERIAL PRIMARY KEY,
plant_name VARCHAR(100),
center_lat DECIMAL(10,7),
center_lng DECIMAL(10,7),
radius_meters INT DEFAULT 500,
warn_at_meters INT DEFAULT 450,
block_safety_signoff BOOLEAN DEFAULT TRUE,
block_job_close BOOLEAN DEFAULT TRUE,
block_ptw_approval BOOLEAN DEFAULT FALSE,
alert_on_violation BOOLEAN DEFAULT TRUE
);
-- Geofence check function
CREATE OR REPLACE FUNCTION
within_plant_boundary(
p_lat DECIMAL, p_lng DECIMAL,
p_plant_id INT
) RETURNS BOOLEAN AS $$
SELECT (
earth_distance(
ll_to_earth(center_lat, center_lng),
ll_to_earth(p_lat, p_lng)
) <= radius_meters
)
FROM Plant_Boundaries
WHERE boundary_id = p_plant_id;
$$ LANGUAGE sql;
API rate limiting — endpoint controlsDDoS & brute-force defence
Rate limits are enforced at the API gateway layer — before requests reach the database. Limits are set per endpoint category and per authentication state (unauthenticated requests get stricter limits). All limit breaches are logged and trigger alerts.
Implementation: Rate limits are enforced using a Redis sliding window counter at the API gateway (e.g. Nginx + Lua, Kong, or AWS API Gateway). Limits use a
X-RateLimit-* header response. On breach, HTTP 429 is returned with Retry-After header. The breach is logged to Suspicious_Logins with threat_type = 'RateLimit'.