39 lines
1.4 KiB
SQL
39 lines
1.4 KiB
SQL
CREATE TABLE unit (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
code TEXT NOT NULL,
|
|
name TEXT NOT NULL,
|
|
description TEXT,
|
|
enabled BOOLEAN NOT NULL DEFAULT TRUE,
|
|
run_time_sec INTEGER NOT NULL DEFAULT 0 CHECK (run_time_sec >= 0),
|
|
stop_time_sec INTEGER NOT NULL DEFAULT 0 CHECK (stop_time_sec >= 0),
|
|
acc_time_sec INTEGER NOT NULL DEFAULT 0 CHECK (acc_time_sec >= 0),
|
|
bl_time_sec INTEGER NOT NULL DEFAULT 0 CHECK (bl_time_sec >= 0),
|
|
require_manual_ack_after_fault BOOLEAN NOT NULL DEFAULT TRUE,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW(),
|
|
UNIQUE (code)
|
|
);
|
|
|
|
ALTER TABLE equipment
|
|
ADD COLUMN unit_id UUID REFERENCES unit(id) ON DELETE SET NULL;
|
|
|
|
CREATE INDEX idx_equipment_unit_id ON equipment(unit_id);
|
|
|
|
CREATE TABLE event (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
event_type TEXT NOT NULL,
|
|
level TEXT NOT NULL DEFAULT 'info',
|
|
unit_id UUID REFERENCES unit(id) ON DELETE SET NULL,
|
|
equipment_id UUID REFERENCES equipment(id) ON DELETE SET NULL,
|
|
source_id UUID REFERENCES source(id) ON DELETE SET NULL,
|
|
message TEXT NOT NULL,
|
|
payload JSONB,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_event_created_at ON event(created_at DESC);
|
|
CREATE INDEX idx_event_type ON event(event_type);
|
|
CREATE INDEX idx_event_unit_id ON event(unit_id);
|
|
CREATE INDEX idx_event_equipment_id ON event(equipment_id);
|
|
CREATE INDEX idx_event_source_id ON event(source_id);
|