38 lines
2.2 KiB
MySQL
38 lines
2.2 KiB
MySQL
|
CREATE TABLE IF NOT EXISTS system_event_log (
|
||
|
|
id BIGSERIAL PRIMARY KEY,
|
||
|
|
source VARCHAR(40) NOT NULL,
|
||
|
|
event_type VARCHAR(80) NOT NULL,
|
||
|
|
status VARCHAR(30) NOT NULL DEFAULT 'info',
|
||
|
|
summary TEXT NOT NULL,
|
||
|
|
correlation_id VARCHAR(255),
|
||
|
|
user_id INTEGER REFERENCES users(user_id) ON DELETE SET NULL,
|
||
|
|
details JSONB NOT NULL DEFAULT '{}'::jsonb,
|
||
|
|
occurred_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
|
|
);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_system_event_log_time ON system_event_log(occurred_at DESC);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_system_event_log_source ON system_event_log(source, event_type, occurred_at DESC);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_system_event_log_correlation ON system_event_log(correlation_id);
|
||
|
|
|
||
|
|
ALTER TABLE telefoni_opkald ADD COLUMN IF NOT EXISTS lifecycle_status VARCHAR(30) NOT NULL DEFAULT 'active';
|
||
|
|
ALTER TABLE telefoni_opkald ADD COLUMN IF NOT EXISTS last_event_at TIMESTAMPTZ NOT NULL DEFAULT NOW();
|
||
|
|
ALTER TABLE telefoni_opkald ADD COLUMN IF NOT EXISTS termination_source VARCHAR(40);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_telefoni_open_calls ON telefoni_opkald(started_at) WHERE ended_at IS NULL;
|
||
|
|
|
||
|
|
INSERT INTO system_event_log(source,event_type,status,summary,correlation_id,user_id,details,occurred_at)
|
||
|
|
SELECT 'voip','voip.stale_call','warning','Opkald mangler hangup-event',callid,bruger_id,
|
||
|
|
jsonb_build_object('call_id',id,'extension',intern_extension,'external_number',ekstern_nummer),started_at
|
||
|
|
FROM telefoni_opkald
|
||
|
|
WHERE ended_at IS NULL AND started_at < NOW()-INTERVAL '12 hours'
|
||
|
|
AND NOT EXISTS (SELECT 1 FROM system_event_log l WHERE l.event_type='voip.stale_call' AND l.correlation_id=telefoni_opkald.callid);
|
||
|
|
|
||
|
|
UPDATE telefoni_opkald SET lifecycle_status='missing_hangup'
|
||
|
|
WHERE ended_at IS NULL AND started_at < NOW()-INTERVAL '12 hours';
|
||
|
|
|
||
|
|
INSERT INTO permissions(code,description,category) VALUES
|
||
|
|
('system.event_log.view','Se samlet systemhændelseslog','system') ON CONFLICT(code) DO NOTHING;
|
||
|
|
INSERT INTO group_permissions(group_id,permission_id)
|
||
|
|
SELECT g.id,p.id FROM groups g CROSS JOIN permissions p
|
||
|
|
WHERE g.name IN ('Administrators','Managers') AND p.code='system.event_log.view'
|
||
|
|
ON CONFLICT DO NOTHING;
|