30 lines
1.9 KiB
SQL
30 lines
1.9 KiB
SQL
CREATE TABLE IF NOT EXISTS t_analytics_event (
|
|
id VARCHAR(64) PRIMARY KEY COMMENT 'Primary key',
|
|
user_id VARCHAR(64) NULL COMMENT 'Logged-in user id',
|
|
anonymous_id VARCHAR(128) NULL COMMENT 'Anonymous client id',
|
|
session_id VARCHAR(128) NOT NULL COMMENT 'Client session id',
|
|
event_name VARCHAR(100) NOT NULL COMMENT 'Event name',
|
|
event_type VARCHAR(50) NOT NULL COMMENT 'Event category',
|
|
page_path VARCHAR(255) NULL COMMENT 'Current page path',
|
|
referrer_path VARCHAR(255) NULL COMMENT 'Referrer page path',
|
|
properties JSON NULL COMMENT 'Business metadata',
|
|
device_info JSON NULL COMMENT 'Device metadata',
|
|
duration_ms BIGINT NULL COMMENT 'Duration in milliseconds',
|
|
occurred_at DATETIME NOT NULL COMMENT 'Client occurrence time',
|
|
server_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Server receive time',
|
|
create_by VARCHAR(64) NULL COMMENT 'Creator',
|
|
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 'Create time',
|
|
update_by VARCHAR(64) NULL COMMENT 'Updater',
|
|
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update time',
|
|
is_deleted TINYINT DEFAULT 0 COMMENT 'Logic delete flag',
|
|
remarks VARCHAR(500) NULL COMMENT 'Remarks'
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Analytics event table';
|
|
|
|
CREATE INDEX idx_analytics_event_name ON t_analytics_event (event_name);
|
|
CREATE INDEX idx_analytics_event_type ON t_analytics_event (event_type);
|
|
CREATE INDEX idx_analytics_event_user_id ON t_analytics_event (user_id);
|
|
CREATE INDEX idx_analytics_event_anonymous_id ON t_analytics_event (anonymous_id);
|
|
CREATE INDEX idx_analytics_event_occurred_at ON t_analytics_event (occurred_at);
|
|
CREATE INDEX idx_analytics_event_name_time ON t_analytics_event (event_name, occurred_at);
|
|
CREATE INDEX idx_analytics_event_user_time ON t_analytics_event (user_id, occurred_at);
|