Database Schema

All PostgreSQL tables, columns, relationships, and useful queries.

uns_state

Written by: uns-state — completed machine state durations.

Column	Type	Description
machine	TEXT	Machine identifier
state	TEXT	ACTIVE, IDLE, ALARM, etc.
started_at / ended_at	TIMESTAMPTZ	Duration boundaries
duration_s	NUMERIC	Seconds
next_state	TEXT	Transitioned to

uns_stoppage

Written by: uns-stoppage — links 1:1 to uns_state via state_id.

Column	Type	Description
state_id	BIGINT UNIQUE	References uns_state.id
reason_code	TEXT	FAULT, NO_WORK, etc.
category	TEXT	planned / unplanned
auto_classified	BOOLEAN	Auto vs operator override

uns_productivity

Written by: uns-productivity — completed production runs.

Column	Type	Description
program_id / program_name	TEXT	CNC program
parts_completed / parts_target	INT	Actual vs target
parts_per_hour	NUMERIC	Throughput rate
reason	TEXT	completed, changed, stopped

uns_input

Written by: uns-input — flexible JSONB data.

Column	Type
input_type	TEXT (scrap, note, quality...)
operator	TEXT
data	JSONB

uns_log

Written by: uns-log — change snapshots.

Column	Type
values	JSONB (complete snapshot)
changed	TEXT[] (changed fields)

Useful Queries

-- Utilisation
SELECT machine, ROUND(SUM(CASE WHEN state='ACTIVE' THEN duration_s ELSE 0 END)/SUM(duration_s)*100,1) AS pct
FROM uns_state WHERE started_at >= NOW()-INTERVAL '24 hours' GROUP BY machine;

-- Stoppage pareto
SELECT reason_code, category, COUNT(*), ROUND(SUM(duration_s)/60,1) AS mins
FROM uns_stoppage WHERE started_at >= NOW()-INTERVAL '24 hours'
GROUP BY reason_code, category ORDER BY mins DESC;

-- MTBF / MTTR
SELECT machine, ROUND(AVG(duration_s)/60,1) AS mtbf FROM uns_state
WHERE state='ACTIVE' AND next_state='ALARM' GROUP BY machine;
SELECT machine, ROUND(AVG(duration_s)/60,1) AS mttr FROM uns_state
WHERE state='ALARM' GROUP BY machine;

PreviousExtending