20 lines
872 B
MySQL
20 lines
872 B
MySQL
|
-- Migration: Create realtime_events table
|
||
|
|
-- Description: Ring buffer untuk realtime stream & SSE events
|
||
|
|
-- Date: 2024-12-28
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS realtime_events (
|
||
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
location_code VARCHAR(64) NOT NULL,
|
||
|
|
gate_code VARCHAR(64) NOT NULL,
|
||
|
|
category VARCHAR(64) NOT NULL,
|
||
|
|
event_time INT UNSIGNED NOT NULL COMMENT 'Unix timestamp',
|
||
|
|
total_count_delta INT NOT NULL DEFAULT 1 COMMENT 'Delta count untuk event ini (biasanya 1)',
|
||
|
|
INDEX idx_created_at (created_at),
|
||
|
|
INDEX idx_location_gate (location_code, gate_code)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
|
|
||
|
|
-- Optional: Cleanup old events (older than 7 days) via cron or manual
|
||
|
|
-- DELETE FROM realtime_events WHERE created_at < DATE_SUB(NOW(), INTERVAL 7 DAY);
|
||
|
|
|