93 lines
4.7 KiB
SQL
93 lines
4.7 KiB
SQL
CREATE TABLE api_keys (
|
|
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
user_id BIGINT UNSIGNED NOT NULL,
|
|
name VARCHAR(80) NOT NULL,
|
|
public_id VARCHAR(24) NOT NULL,
|
|
key_prefix VARCHAR(32) NOT NULL,
|
|
secret_hash BINARY(32) NOT NULL,
|
|
expires_at DATETIME(6) NULL,
|
|
last_used_at DATETIME(6) NULL,
|
|
revoked_at DATETIME(6) NULL,
|
|
created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
|
|
updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
|
|
PRIMARY KEY (id),
|
|
UNIQUE KEY uq_api_keys_public_id (public_id),
|
|
KEY idx_api_keys_user_status (user_id, revoked_at, created_at),
|
|
KEY idx_api_keys_last_used (last_used_at),
|
|
CONSTRAINT fk_api_keys_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE RESTRICT,
|
|
CONSTRAINT chk_api_keys_name CHECK (CHAR_LENGTH(TRIM(name)) > 0),
|
|
CONSTRAINT chk_api_keys_public_id CHECK (CHAR_LENGTH(public_id) = 24),
|
|
CONSTRAINT chk_api_keys_prefix CHECK (CHAR_LENGTH(key_prefix) = 32)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE api_audit_events (
|
|
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
user_id BIGINT UNSIGNED NULL,
|
|
api_key_id BIGINT UNSIGNED NULL,
|
|
generation_id BIGINT UNSIGNED NULL,
|
|
action VARCHAR(128) NOT NULL,
|
|
result VARCHAR(16) NOT NULL,
|
|
request_id VARCHAR(128) NOT NULL,
|
|
status_code SMALLINT UNSIGNED NULL,
|
|
error_code VARCHAR(64) NULL,
|
|
summary JSON NOT NULL,
|
|
created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
|
|
PRIMARY KEY (id),
|
|
KEY idx_api_audit_events_user (user_id, created_at),
|
|
KEY idx_api_audit_events_key (api_key_id, created_at),
|
|
KEY idx_api_audit_events_generation (generation_id, created_at),
|
|
KEY idx_api_audit_events_request (request_id, created_at),
|
|
CONSTRAINT fk_api_audit_events_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE RESTRICT,
|
|
CONSTRAINT fk_api_audit_events_key FOREIGN KEY (api_key_id) REFERENCES api_keys (id) ON DELETE RESTRICT,
|
|
CONSTRAINT fk_api_audit_events_generation FOREIGN KEY (generation_id) REFERENCES generations (id) ON DELETE RESTRICT,
|
|
CONSTRAINT chk_api_audit_events_result CHECK (result IN ('succeeded', 'failed', 'denied')),
|
|
CONSTRAINT chk_api_audit_events_summary CHECK (JSON_TYPE(summary) = 'OBJECT')
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
ALTER TABLE generations
|
|
DROP INDEX idx_generations_queue,
|
|
ADD COLUMN available_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) AFTER lease_until,
|
|
ADD KEY idx_generations_queue (status, available_at, lease_until, created_at);
|
|
|
|
INSERT INTO sys_menu
|
|
(menu_name, title, icon, path, paths, menu_type, action, permission, parent_id, no_cache, breadcrumb, component, sort, visible, is_frame)
|
|
SELECT 'chorus-api-keys', 'API Keys', 'ri:key-2-line', '/chorus/api-keys', '', 'C', '', 'chorus:api-keys:view',
|
|
parent.menu_id, FALSE, 'Chorus / API Keys', 'chorus/api-keys/index', 70, '0', '0'
|
|
FROM sys_menu parent
|
|
WHERE parent.path = '/chorus'
|
|
ON DUPLICATE KEY UPDATE
|
|
menu_name = VALUES(menu_name), title = VALUES(title), icon = VALUES(icon), menu_type = VALUES(menu_type),
|
|
action = VALUES(action), permission = VALUES(permission), parent_id = VALUES(parent_id), no_cache = VALUES(no_cache),
|
|
breadcrumb = VALUES(breadcrumb), component = VALUES(component), sort = VALUES(sort), visible = VALUES(visible),
|
|
is_frame = VALUES(is_frame), deleted_at = NULL;
|
|
|
|
UPDATE sys_menu child
|
|
JOIN sys_menu parent ON parent.path = '/chorus'
|
|
SET child.paths = CONCAT(parent.paths, '/', child.menu_id)
|
|
WHERE child.path = '/chorus/api-keys';
|
|
|
|
INSERT INTO sys_api (handle, title, path, type, action)
|
|
VALUES
|
|
('chorus.api-keys.list', 'List API keys', '/api/v1/chorus/api-keys', 'BUS', 'GET'),
|
|
('chorus.api-keys.get', 'Get API key metadata', '/api/v1/chorus/api-keys/:id', 'BUS', 'GET'),
|
|
('chorus.api-keys.revoke', 'Revoke API key', '/api/v1/chorus/api-keys/:id/revoke', 'BUS', 'POST')
|
|
ON DUPLICATE KEY UPDATE
|
|
title = VALUES(title), type = VALUES(type), deleted_at = NULL;
|
|
|
|
INSERT IGNORE INTO sys_role_menu (role_id, menu_id)
|
|
SELECT role_record.role_id, menu.menu_id
|
|
FROM sys_role role_record
|
|
JOIN sys_menu menu ON menu.path = '/chorus/api-keys'
|
|
WHERE role_record.role_key = 'chorus_operator';
|
|
|
|
INSERT IGNORE INTO sys_menu_api_rule (menu_id, sys_api_id)
|
|
SELECT menu.menu_id, api.id
|
|
FROM sys_menu menu
|
|
JOIN sys_api api ON api.handle IN ('chorus.api-keys.list', 'chorus.api-keys.get', 'chorus.api-keys.revoke')
|
|
WHERE menu.path = '/chorus/api-keys';
|
|
|
|
INSERT IGNORE INTO sys_casbin_rule (ptype, v0, v1, v2, v3, v4, v5)
|
|
SELECT 'p', 'chorus_operator', path, action, '', '', ''
|
|
FROM sys_api
|
|
WHERE handle IN ('chorus.api-keys.list', 'chorus.api-keys.get', 'chorus.api-keys.revoke');
|