41 lines
1.9 KiB
Plaintext
41 lines
1.9 KiB
Plaintext
-- Anomaly Events Table for Manufacturing Anomaly Detection
|
|
-- Stores all detected anomalies with full context for analysis
|
|
|
|
CREATE TABLE IF NOT EXISTS anomaly_events (
|
|
event_id INT AUTO_INCREMENT PRIMARY KEY,
|
|
timestamp BIGINT NOT NULL COMMENT 'Unix timestamp in milliseconds',
|
|
work_order_id VARCHAR(50) COMMENT 'Associated work order if applicable',
|
|
|
|
-- Anomaly Classification
|
|
anomaly_type VARCHAR(50) NOT NULL COMMENT 'Type: slow-cycle, production-stopped, high-scrap, etc.',
|
|
severity ENUM('info', 'warning', 'critical') NOT NULL DEFAULT 'warning',
|
|
|
|
-- Event Details
|
|
title VARCHAR(200) NOT NULL COMMENT 'Short human-readable title',
|
|
description TEXT COMMENT 'Detailed description of the anomaly',
|
|
|
|
-- Context Data (JSON stored as TEXT)
|
|
data_json TEXT COMMENT 'Anomaly-specific data: actual_value, expected_value, delta, etc.',
|
|
kpi_snapshot_json TEXT COMMENT 'KPI values at time of event: OEE, Availability, Performance, Quality',
|
|
|
|
-- Status Tracking
|
|
status ENUM('active', 'acknowledged', 'resolved') DEFAULT 'active',
|
|
acknowledged_at BIGINT COMMENT 'When user acknowledged',
|
|
resolved_at BIGINT COMMENT 'When anomaly was resolved',
|
|
auto_resolved BOOLEAN DEFAULT FALSE COMMENT 'True if system auto-resolved',
|
|
|
|
-- Additional Metadata
|
|
cycle_count INT COMMENT 'Cycle count at time of event',
|
|
occurrence_count INT DEFAULT 1 COMMENT 'How many times this occurred (for deduplication)',
|
|
last_occurrence BIGINT COMMENT 'Last time this anomaly re-occurred',
|
|
notes TEXT COMMENT 'User notes',
|
|
|
|
-- Indexes for fast queries
|
|
INDEX idx_timestamp (timestamp),
|
|
INDEX idx_work_order (work_order_id),
|
|
INDEX idx_status (status),
|
|
INDEX idx_type (anomaly_type),
|
|
INDEX idx_severity (severity),
|
|
INDEX idx_type_status (anomaly_type, status)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Stores detected anomalies and events';
|