TomyS/tianai-captcha-enhanced
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
master
tianai-captcha-enhanced/tianai-captcha-platform/sql/init.sql
T
abcv7 1164b51c64
deploy-test / build-and-deploy-test (push) Successful in 2m41s
Details
feat: Phase 3-5 管理后台前端+统计监控+部署配置 
Phase 3: 管理后台前端 (Vue3+Vite+Element Plus)
- 登录页面/JWT认证/Pinia状态管理
- 首页大盘/站点管理/验证码日志/IP黑名单/套餐管理/系统设置
- 7个页面完整路由+API对接

Phase 4: 统计监控模块
- CaptchaLogAspect AOP自动记录
- RealtimeStatsService Redis实时计数
- HistoryStatsService 历史查询
- AnomalyDetectionService 异常检测

Phase 5: 部署和测试
- docker-compose.yml 5服务编排
- application-prod.yml 外部化配置
- CaptchaApiIntegrationTest 集成测试
- 前端 Dockerfile + nginx.conf
2026-08-26 13:44:08 +08:00

234 lines
12 KiB
SQL
Raw Permalink Blame History

DROP TABLE IF EXISTS verification_logs CASCADE;
DROP TABLE IF EXISTS track_samples CASCADE;
DROP TABLE IF EXISTS captcha_challenges CASCADE;
DROP TABLE IF EXISTS captcha_categories CASCADE;
DROP TABLE IF EXISTS sites CASCADE;
DROP TABLE IF EXISTS plans CASCADE;
DROP TABLE IF EXISTS users CASCADE;
DROP TABLE IF EXISTS announcements CASCADE;
DROP TABLE IF EXISTS captcha_ip_blacklist CASCADE;
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(64) NOT NULL UNIQUE,
email VARCHAR(128),
password VARCHAR(256) NOT NULL,
role VARCHAR(16) DEFAULT 'USER',
site_amount INT DEFAULT 0,
is_enabled BOOLEAN DEFAULT TRUE,
is_system BOOLEAN DEFAULT FALSE,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE plans (
id SERIAL PRIMARY KEY,
name VARCHAR(64) NOT NULL,
description VARCHAR(256),
qps INT DEFAULT 10,
daily_limit INT DEFAULT 500,
captcha_types TEXT[] DEFAULT '{SLIDER,PUZZLE,TEXT_CLICK,ICON_CLICK,ICON_UNDERSTAND}',
custom_style BOOLEAN DEFAULT FALSE,
is_enabled BOOLEAN DEFAULT TRUE,
is_system BOOLEAN DEFAULT FALSE,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE sites (
id SERIAL PRIMARY KEY,
user_id INT REFERENCES users(id),
name VARCHAR(64) NOT NULL,
domain VARCHAR(256),
favicon VARCHAR(512),
logo VARCHAR(512),
site_key UUID DEFAULT gen_random_uuid() UNIQUE,
secret_key UUID DEFAULT gen_random_uuid() UNIQUE,
verify_level VARCHAR(16) DEFAULT 'MEDIUM',
qps INT DEFAULT 10,
daily_limit INT DEFAULT 500,
captcha_types TEXT[] DEFAULT '{SLIDER,PUZZLE,TEXT_CLICK,ICON_CLICK,ICON_UNDERSTAND}',
plan_id INT REFERENCES plans(id),
plan_expire_at TIMESTAMPTZ,
is_enabled BOOLEAN DEFAULT TRUE,
rsa_public_key TEXT,
rsa_private_key TEXT,
aes_key VARCHAR(256),
signing_key VARCHAR(256),
track_validation_enabled BOOLEAN DEFAULT TRUE,
track_human_threshold FLOAT DEFAULT 0.5,
obfuscation_enabled BOOLEAN DEFAULT TRUE,
encryption_enabled BOOLEAN DEFAULT TRUE,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE captcha_categories (
id SERIAL PRIMARY KEY,
name VARCHAR(64) NOT NULL,
label VARCHAR(64) NOT NULL,
items TEXT[] NOT NULL DEFAULT '{}',
is_enabled BOOLEAN DEFAULT TRUE,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE captcha_challenges (
id SERIAL PRIMARY KEY,
category_id INT REFERENCES captcha_categories(id) ON DELETE CASCADE,
prompt TEXT NOT NULL,
correct_items TEXT[] NOT NULL DEFAULT '{}',
difficulty VARCHAR(16) DEFAULT 'MEDIUM',
is_enabled BOOLEAN DEFAULT TRUE,
use_count INT DEFAULT 0,
success_rate DOUBLE PRECISION DEFAULT 0.5,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE verification_logs (
id SERIAL PRIMARY KEY,
site_id INT REFERENCES sites(id),
site_key UUID,
captcha_type VARCHAR(32),
scene VARCHAR(64) DEFAULT 'default',
ip VARCHAR(45),
is_pass BOOLEAN,
behavior_score DOUBLE PRECISION,
risk_level VARCHAR(16),
cost_time INT,
captcha_id VARCHAR(128),
user_agent TEXT,
track_score FLOAT,
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE track_samples (
id SERIAL PRIMARY KEY,
site_id INT REFERENCES sites(id),
captcha_type VARCHAR(32),
is_human BOOLEAN,
ml_score DOUBLE PRECISION,
basic_pass BOOLEAN,
total_points INT,
total_duration BIGINT,
displacement_x FLOAT,
displacement_y FLOAT,
displacement_x_ratio FLOAT,
total_path_length DOUBLE PRECISION,
path_efficiency DOUBLE PRECISION,
avg_speed FLOAT,
max_speed FLOAT,
min_speed FLOAT,
speed_variance DOUBLE PRECISION,
speed_std_dev DOUBLE PRECISION,
speed_skewness DOUBLE PRECISION,
avg_acceleration FLOAT,
max_acceleration FLOAT,
min_acceleration FLOAT,
acceleration_variance DOUBLE PRECISION,
direction_changes INT,
y_direction_changes INT,
pauses INT,
start_offset DOUBLE PRECISION,
straightness DOUBLE PRECISION,
x_uniformity DOUBLE PRECISION,
y_uniformity DOUBLE PRECISION,
avg_point_interval FLOAT,
speed_phase_correlation DOUBLE PRECISION,
max_jump_distance DOUBLE PRECISION,
overshoot_ratio DOUBLE PRECISION,
track_json TEXT,
ip VARCHAR(45),
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE announcements (
id SERIAL PRIMARY KEY,
title VARCHAR(256) NOT NULL,
content TEXT,
is_pinned BOOLEAN DEFAULT FALSE,
is_published BOOLEAN DEFAULT TRUE,
sort_order INT DEFAULT 0,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE captcha_ip_blacklist (
id SERIAL PRIMARY KEY,
ip VARCHAR(64) NOT NULL,
reason VARCHAR(256),
ban_until TIMESTAMPTZ,
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_sites_key ON sites(site_key);
CREATE INDEX idx_sites_user ON sites(user_id);
CREATE INDEX idx_challenges_category ON captcha_challenges(category_id);
CREATE INDEX idx_challenges_difficulty ON captcha_challenges(difficulty);
CREATE INDEX idx_logs_site ON verification_logs(site_id);
CREATE INDEX idx_logs_time ON verification_logs(created_at);
CREATE INDEX idx_logs_type ON verification_logs(captcha_type);
CREATE INDEX idx_logs_pass ON verification_logs(is_pass);
CREATE INDEX idx_ip_blacklist_ip ON captcha_ip_blacklist(ip);
CREATE INDEX idx_track_human ON track_samples(is_human);
CREATE INDEX idx_track_type ON track_samples(captcha_type);
CREATE INDEX idx_track_time ON track_samples(created_at);
CREATE INDEX idx_track_score ON track_samples(ml_score);
INSERT INTO users (username, email, password, role, site_amount, is_enabled, is_system)
VALUES ('admin', 'admin@captchaforge.local', '$2a$10$85QOiy3qD5KPuxyrrN/LcuZe3ty/OrTk6yEKAJEGtJKk97ukw2yPG', 'ADMIN', 999, TRUE, TRUE);
INSERT INTO plans (name, description, qps, daily_limit, is_enabled, is_system) VALUES
('免费套餐', '默认免费套餐', 5, 500, TRUE, TRUE),
('基础套餐', '适合中小站点', 20, 5000, TRUE, TRUE),
('专业套餐', '适合大型站点', 100, 50000, TRUE, TRUE);
INSERT INTO captcha_categories (name, label, items) VALUES
('animals', '动物', '{"🐶","🐱","🐭","🐹","🐰","🦊","🐻","🐼","🐨","🐯","🦁","🐮","🐷","🐸","🐵","🐔","🐧","🐦","🦅","🦆","🦉","🐴","🦄","🐝","🐛","🦋","🐌","🐞","🐢","🐍","🦎","🐙","🦑","🦐","🦀","🐠","🐟","🐬","🐳","🐊","🐘","🦏","🐪","🦒","🐕","🐈","🐓","🦃","🦚","🦜","🐇","🦔","🐺","🐗"}'),
('food', '食物饮品', '{"🍎","🍐","🍊","🍋","🍌","🍉","🍇","🍓","🍒","🍑","🥭","🍍","🥥","🥝","🍅","🍆","🥑","🥦","🌽","🥕","🍞","🧀","🍳","🍔","🍟","🍕","🌮","🍣","🍜","🍩","🍪","🎂","🍫","🍭","☕","🍵","🧃","🥤","🍺","🍷"}'),
('vehicles', '交通工具', '{"🚗","🚕","🚙","🚌","🏎","🚓","🚑","🚒","🚐","🚚","🚛","🚜","🚲","🛵","🏍","🚨","🚔","🚡","🚠","🚃","🚋","🚄","🚅","🚂","✈️","🛩","🚀","🛸","🚁","⛵","🚤","🛳","⛴","🚢"}'),
('nature', '天气自然', '{"☀️","🌤","⛅","🌥","☁️","🌦","🌧","⛈","🌩","🌨","❄️","☃️","🌪","🌈","🌊","💧","🔥","⭐","🌟","✨","⚡","☄️","🌸","🌺","🌻","🌹","🌷","🌱","🌿","🍀","🍁","🍂","🍃","🌴","🌵"}'),
('sports', '运动娱乐', '{"⚽","🏀","🏈","⚾","🎾","🏐","🏉","🎱","🏓","🏸","🏒","🏑","🥍","🎯","🎳","🎮","🎲","♟","🧩","🪀","🪁","🎪","🤹","🎭","🎨","🎬","🎤","🎧","🎹","🥁","🎸","🎻"}'),
('buildings', '建筑地点', '{"🏠","🏡","🏢","🏣","🏤","🏥","🏦","🏨","🏩","🏪","🏫","🏬","🏭","🏯","🏰","💒","🗼","🗽","⛪","🕌","🛕","🕍","⛩","🕋","⛲","⛺","🏕"}'),
('objects', '电子物品', '{"⌚","📱","💻","⌨️","🖥","🖨","🖱","🖲","💾","💿","📷","📹","🎥","📞","📺","📻","🔋","🔌","💡","🔦","🕯","🔑","🔒","🔓","📧","📮","📦","📋","📁","✏️","🖊","🖋","✒️","🖌","📝","🔍","📎","📐","📌","✂️","🧲","🔧","🔨","⚙️","💊","💉","🩺","🧬","🔭","🔬","🧪"}'),
('gestures', '手势动作', '{"👋","🤚","🖐","✋","🖖","👌","🤌","🤏","✌","🤞","🤟","🤘","🤙","👈","👉","👆","👇","☝️","👍","👎","✊","👊","🤛","🤜","👏","🙌","👐","🤲","🤝","🙏","✍️","💅","🤳","💪"}');
INSERT INTO captcha_challenges (category_id, prompt, correct_items, difficulty) VALUES
(1, '请点击所有的猫科动物', '{"🐱","🐯","🦁","🐈"}', 'MEDIUM'),
(1, '请点击所有的犬科动物', '{"🐶","🐺","🐕"}', 'MEDIUM'),
(1, '请点击所有的鸟类', '{"🐔","🐧","🐦","🦅","🦆","🦉","🦜","🦚","🦃"}', 'MEDIUM'),
(1, '请点击所有的水生动物', '{"🐙","🦑","🦐","🦀","🐠","🐟","🐬","🐳"}', 'MEDIUM'),
(1, '请点击所有的昆虫', '{"🐝","🐛","🦋","🐌","🐞","🦟"}', 'MEDIUM'),
(2, '请点击所有的水果', '{"🍎","🍐","🍊","🍋","🍌","🍉","🍇","🍓","🍒","🍑","🥭","🍍","🥝"}', 'MEDIUM'),
(2, '请点击所有的蔬菜', '{"🍅","🍆","🥑","🥦","🌽","🥕"}', 'MEDIUM'),
(2, '请点击所有的饮品', '{"☕","🍵","🧃","🥤","🍺","🍷"}', 'MEDIUM'),
(2, '请点击所有的甜点', '{"🍩","🍪","🎂","🍫","🍭"}', 'MEDIUM'),
(3, '请点击所有的汽车', '{"🚗","🚕","🚙","🏎","🚓","🚑","🚒","🚐"}', 'MEDIUM'),
(3, '请点击所有的飞行器', '{"✈️","🛩","🚀","🛸","🚁"}', 'MEDIUM'),
(3, '请点击所有的船只', '{"⛵","🚤","🛳","⛴","🚢"}', 'MEDIUM'),
(4, '请点击所有与降水相关的', '{"🌦","🌧","⛈","🌩","🌨"}', 'MEDIUM'),
(4, '请点击所有的花卉', '{"🌸","🌺","🌻","🌹","🌷"}', 'MEDIUM'),
(4, '请点击所有的天体', '{"☀️","⭐","🌟","✨","⚡","☄️"}', 'MEDIUM'),
(5, '请点击所有的球类运动', '{"⚽","🏀","🏈","⚾","🎾","🏐","🏉"}', 'MEDIUM'),
(5, '请点击所有的音乐相关', '{"🎤","🎧","🎹","🥁","🎸","🎻"}', 'MEDIUM'),
(5, '请点击所有的棋牌游戏', '{"🎲","♟","🧩","🎮","🎯","🎱"}', 'MEDIUM'),
(1, '请点击所有的动物', '{"🐶","🐱","🐭","🐹","🐰","🦊","🐻","🐼","🐨","🐯","🦁","🐮","🐷","🐸","🐵","🐔","🐧","🐦","🦅","🦆","🦉","🐴","🦄","🐝","🐛","🦋","🐌","🐞","🐢","🐍","🦎","🐙","🦑","🦐","🦀","🐠","🐟","🐬","🐳","🐊","🐘","🦏","🐪","🦒","🐕","🐈","🐓","🦃","🦚","🦜","🐇","🦔","🐺","🐗"}', 'LOW'),
(2, '请点击所有的食物饮品', '{"🍎","🍐","🍊","🍋","🍌","🍉","🍇","🍓","🍒","🍑","🥭","🍍","🥥","🥝","🍅","🍆","🥑","🥦","🌽","🥕","🍞","🧀","🍳","🍔","🍟","🍕","🌮","🍣","🍜","🍩","🍪","🎂","🍫","🍭","☕","🍵","🧃","🥤","🍺","🍷"}', 'LOW'),
(3, '请点击所有的交通工具', '{"🚗","🚕","🚙","🚌","🏎","🚓","🚑","🚒","🚐","🚚","🚛","🚜","🚲","🛵","🏍","🚨","🚔","🚡","🚠","🚃","🚋","🚄","🚅","🚂","✈️","🛩","🚀","🛸","🚁","⛵","🚤","🛳","⛴","🚢"}', 'LOW'),
(4, '请点击所有的天气自然', '{"☀️","🌤","⛅","🌥","☁️","🌦","🌧","⛈","🌩","🌨","❄️","☃️","🌪","🌈","🌊","💧","🔥","⭐","🌟","✨","⚡","☄️","🌸","🌺","🌻","🌹","🌷","🌱","🌿","🍀","🍁","🍂","🍃","🌴","🌵"}', 'LOW');
INSERT INTO announcements (title, content, is_pinned, is_published, sort_order) VALUES
('tianai-captcha-enhanced 2.0.0 上线', '# tianai-captcha-enhanced
基于 tianai-captcha 开源版的增强版行为验证码平台
## 特性
- 16种验证码类型
- ML轨迹校验器(28维特征+14条规则)
- 对抗扰动防YOLO
- 行为风控引擎
- 端到端加密(AES-256+RSA-4096)
- 背景乱序/正弦扭曲/噪声注入
- IP黑名单+滑动窗口限流', TRUE, TRUE, 0);
Reference in New Issue View Git Blame Copy Permalink