24 lines
1.1 KiB
MySQL
24 lines
1.1 KiB
MySQL
|
-- Migration: Create audit_logs table
|
||
|
|
-- Description: Audit log untuk tracking semua perubahan data (create/update/delete)
|
||
|
|
-- Date: 2024-12-28
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS audit_logs (
|
||
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
||
|
|
actor_user_id INT UNSIGNED NOT NULL,
|
||
|
|
actor_username VARCHAR(100) NOT NULL,
|
||
|
|
actor_role VARCHAR(50) NOT NULL,
|
||
|
|
action VARCHAR(20) NOT NULL COMMENT 'create|update|delete',
|
||
|
|
entity VARCHAR(50) NOT NULL COMMENT 'locations|gates|tariffs',
|
||
|
|
entity_key VARCHAR(255) NOT NULL COMMENT 'Primary key atau composite key (e.g., "kerkof_01" or "kerkof_01:gate01")',
|
||
|
|
before_json JSON NULL COMMENT 'Data sebelum perubahan (untuk update/delete)',
|
||
|
|
after_json JSON NULL COMMENT 'Data sesudah perubahan (untuk create/update)',
|
||
|
|
ip_address VARCHAR(45) NOT NULL COMMENT 'IPv4 atau IPv6',
|
||
|
|
user_agent VARCHAR(500) NULL,
|
||
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
INDEX idx_actor_user_id (actor_user_id),
|
||
|
|
INDEX idx_entity (entity, entity_key),
|
||
|
|
INDEX idx_action (action),
|
||
|
|
INDEX idx_created_at (created_at)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
|
|