kknongchang_2506/app/Module/OAuth/Database/oauth_tables.sql

-- OAuth客户端表
CREATE TABLE `oauth_clients` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL COMMENT '客户端名称',
    `client_id` varchar(80) NOT NULL COMMENT '客户端ID',
    `client_secret` varchar(80) NOT NULL COMMENT '客户端密钥',
    `redirect_uri` text NOT NULL COMMENT '回调地址',
    `grant_types` json DEFAULT NULL COMMENT '授权类型',
    `scope` json DEFAULT NULL COMMENT '权限范围',
    `user_id` bigint unsigned DEFAULT NULL COMMENT '关联用户ID',
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `oauth_clients_client_id_unique` (`client_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='OAuth客户端表';

-- OAuth访问令牌表
CREATE TABLE `oauth_access_tokens` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `client_id` varchar(80) NOT NULL COMMENT '客户端ID',
    `user_id` bigint unsigned DEFAULT NULL COMMENT '用户ID',
    `access_token` varchar(255) NOT NULL COMMENT '访问令牌',
    `expires_at` timestamp NULL DEFAULT NULL COMMENT '过期时间',
    `scope` json DEFAULT NULL COMMENT '权限范围',
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `oauth_access_tokens_token_unique` (`access_token`),
    KEY `oauth_access_tokens_client_id_index` (`client_id`),
    KEY `oauth_access_tokens_user_id_index` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='OAuth访问令牌表';

-- OAuth刷新令牌表
CREATE TABLE `oauth_refresh_tokens` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `access_token_id` bigint unsigned NOT NULL COMMENT '关联的访问令牌ID',
    `refresh_token` varchar(255) NOT NULL COMMENT '刷新令牌',
    `expires_at` timestamp NULL DEFAULT NULL COMMENT '过期时间',
    `revoked` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否已撤销',
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `oauth_refresh_tokens_token_unique` (`refresh_token`),
    KEY `oauth_refresh_tokens_access_token_id_index` (`access_token_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='OAuth刷新令牌表';

-- OAuth授权码表
CREATE TABLE `oauth_auth_codes` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `client_id` varchar(80) NOT NULL COMMENT '客户端ID',
    `user_id` bigint unsigned NOT NULL COMMENT '用户ID',
    `auth_code` varchar(255) NOT NULL COMMENT '授权码',
    `redirect_uri` text NOT NULL COMMENT '重定向URI',
    `expires_at` timestamp NULL DEFAULT NULL COMMENT '过期时间',
    `scope` json DEFAULT NULL COMMENT '权限范围',
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `oauth_auth_codes_auth_code_unique` (`auth_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='OAuth授权码表';