forked from fa/breadcrumb-the-shire
87 lines
2.9 KiB
SQL
87 lines
2.9 KiB
SQL
-- Idempotent update: introduce system audit log, permissions and defaults.
|
|
|
|
CREATE TABLE IF NOT EXISTS `system_audit_log` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`event_uuid` CHAR(36) NOT NULL,
|
|
`request_id` CHAR(36) NULL,
|
|
`channel` VARCHAR(16) NOT NULL,
|
|
`event_type` VARCHAR(64) NOT NULL,
|
|
`outcome` VARCHAR(16) NOT NULL,
|
|
`error_code` VARCHAR(100) NULL,
|
|
`actor_user_id` INT UNSIGNED NULL,
|
|
`actor_tenant_id` INT UNSIGNED NULL,
|
|
`target_type` VARCHAR(64) NULL,
|
|
`target_id` INT UNSIGNED NULL,
|
|
`target_uuid` CHAR(36) NULL,
|
|
`method` VARCHAR(8) NULL,
|
|
`path` VARCHAR(255) NULL,
|
|
`ip_hash` CHAR(64) NULL,
|
|
`user_agent_hash` CHAR(64) NULL,
|
|
`metadata_json` TEXT NULL,
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uniq_system_audit_event_uuid` (`event_uuid`),
|
|
KEY `idx_system_audit_created` (`created_at`),
|
|
KEY `idx_system_audit_event_created` (`event_type`, `created_at`),
|
|
KEY `idx_system_audit_actor_created` (`actor_user_id`, `created_at`),
|
|
KEY `idx_system_audit_target_created` (`target_type`, `target_uuid`, `created_at`),
|
|
KEY `idx_system_audit_request_created` (`request_id`, `created_at`),
|
|
KEY `idx_system_audit_outcome_created` (`outcome`, `created_at`),
|
|
CONSTRAINT `fk_system_audit_actor_user` FOREIGN KEY (`actor_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
|
|
CONSTRAINT `fk_system_audit_actor_tenant` FOREIGN KEY (`actor_tenant_id`) REFERENCES `tenants` (`id`) ON DELETE SET NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
INSERT INTO `permissions` (`key`, `description`, `active`, `is_system`)
|
|
VALUES
|
|
('system_audit.view', 'Can view system audit logs', 1, 1),
|
|
('system_audit.purge', 'Can purge system audit logs', 1, 1)
|
|
ON DUPLICATE KEY UPDATE
|
|
`description` = VALUES(`description`),
|
|
`active` = VALUES(`active`),
|
|
`is_system` = VALUES(`is_system`);
|
|
|
|
INSERT INTO `role_permissions` (`role_id`, `permission_id`, `created`)
|
|
SELECT r.id, p.id, NOW()
|
|
FROM roles r
|
|
JOIN permissions p ON p.`key` IN ('system_audit.view', 'system_audit.purge')
|
|
WHERE r.description IN ('Admin', 'Administrator') OR r.id = 1
|
|
ON DUPLICATE KEY UPDATE role_id = role_id;
|
|
|
|
INSERT INTO `settings` (`key`, `value`, `description`)
|
|
VALUES
|
|
('system_audit_enabled', '1', 'setting.system_audit_enabled'),
|
|
('system_audit_retention_days', '365', 'setting.system_audit_retention_days')
|
|
ON DUPLICATE KEY UPDATE
|
|
`value` = VALUES(`value`),
|
|
`description` = VALUES(`description`);
|
|
|
|
INSERT INTO `scheduled_jobs` (
|
|
`job_key`,
|
|
`label`,
|
|
`description`,
|
|
`enabled`,
|
|
`timezone`,
|
|
`schedule_type`,
|
|
`schedule_interval`,
|
|
`schedule_time`,
|
|
`schedule_weekdays_csv`,
|
|
`catchup_once`,
|
|
`next_run_at`
|
|
)
|
|
VALUES (
|
|
'system_audit_purge',
|
|
'System audit purge',
|
|
'Purges system audit entries by retention policy',
|
|
1,
|
|
'Europe/Berlin',
|
|
'daily',
|
|
1,
|
|
'03:00',
|
|
NULL,
|
|
1,
|
|
NULL
|
|
)
|
|
ON DUPLICATE KEY UPDATE
|
|
`label` = VALUES(`label`),
|
|
`description` = VALUES(`description`);
|