Godemo/iot-base-station/migrations/000002_create_device_events_table.up.sql

CREATE TABLE IF NOT EXISTS device_events (
    id VARCHAR(255) PRIMARY KEY,
    device_id VARCHAR(255) NOT NULL,
    type VARCHAR(50) NOT NULL,
    severity VARCHAR(20) NOT NULL,
    message TEXT,
    data JSONB,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    acked BOOLEAN DEFAULT FALSE,
    acked_by VARCHAR(255),
    acked_at TIMESTAMP,
    FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE
);

CREATE INDEX idx_device_events_device_id ON device_events(device_id);
CREATE INDEX idx_device_events_type ON device_events(type);
CREATE INDEX idx_device_events_severity ON device_events(severity);
CREATE INDEX idx_device_events_timestamp ON device_events(timestamp);
CREATE INDEX idx_device_events_acked ON device_events(acked);