95 lines
4.9 KiB
SQL
95 lines
4.9 KiB
SQL
-- Phase G5: Local Agent / Privacy Mode — Database Migration
|
|
-- Tables: privacy_configs, offline_licenses, kb_sync_states, usage_reports
|
|
-- Also: ALTER distributions ADD COLUMN privacy_mode
|
|
|
|
-- 1. Privacy Configurations (per-user privacy settings)
|
|
CREATE TABLE IF NOT EXISTS privacy_configs (
|
|
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
|
user_id VARCHAR(64) NOT NULL,
|
|
agent_id VARCHAR(64) DEFAULT NULL COMMENT 'FK to market_apps.id',
|
|
privacy_mode ENUM('local', 'cloud', 'hybrid') NOT NULL DEFAULT 'cloud',
|
|
local_llm_provider VARCHAR(64) DEFAULT NULL COMMENT 'ollama/vllm/localai',
|
|
local_llm_model VARCHAR(128) DEFAULT NULL,
|
|
local_llm_endpoint VARCHAR(256) DEFAULT NULL COMMENT 'http://localhost:11434',
|
|
data_retention_days INT NOT NULL DEFAULT 30 COMMENT 'Local data retention period',
|
|
anonymize_usage TINYINT(1) NOT NULL DEFAULT 1,
|
|
sync_knowledge_bases JSON DEFAULT NULL COMMENT 'KB IDs to sync locally',
|
|
sync_frequency ENUM('realtime', 'hourly', 'daily', 'manual') NOT NULL DEFAULT 'daily',
|
|
last_sync_at DATETIME DEFAULT NULL,
|
|
status ENUM('active', 'inactive', 'error') NOT NULL DEFAULT 'active',
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
INDEX idx_privacy_user (user_id),
|
|
INDEX idx_privacy_agent (agent_id),
|
|
INDEX idx_privacy_mode (privacy_mode)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 2. Offline Licenses
|
|
CREATE TABLE IF NOT EXISTS offline_licenses (
|
|
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
|
license_key VARCHAR(256) NOT NULL UNIQUE COMMENT 'JWT token',
|
|
user_id VARCHAR(64) NOT NULL,
|
|
agent_id BIGINT DEFAULT NULL,
|
|
device_fingerprint VARCHAR(256) DEFAULT NULL COMMENT 'SHA256 of machine-id + MAC',
|
|
issued_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
expires_at DATETIME NOT NULL,
|
|
last_validated_at DATETIME DEFAULT NULL,
|
|
max_offline_days INT NOT NULL DEFAULT 7,
|
|
is_active TINYINT(1) NOT NULL DEFAULT 1,
|
|
revoked_reason VARCHAR(512) DEFAULT NULL,
|
|
status ENUM('active', 'expired', 'revoked', 'pending') NOT NULL DEFAULT 'active',
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
INDEX idx_ol_user (user_id),
|
|
INDEX idx_ol_license (license_key),
|
|
INDEX idx_ol_status (status),
|
|
INDEX idx_ol_expires (expires_at)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 3. Knowledge Base Sync States
|
|
CREATE TABLE IF NOT EXISTS kb_sync_states (
|
|
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
|
user_id VARCHAR(64) NOT NULL,
|
|
knowledge_base_id BIGINT NOT NULL COMMENT 'FK to knowledge_bases.id',
|
|
local_version INT NOT NULL DEFAULT 0,
|
|
remote_version INT NOT NULL DEFAULT 0,
|
|
last_sync_at DATETIME DEFAULT NULL,
|
|
sync_status ENUM('synced', 'syncing', 'pending', 'conflict', 'failed') NOT NULL DEFAULT 'pending',
|
|
total_docs INT NOT NULL DEFAULT 0,
|
|
synced_docs INT NOT NULL DEFAULT 0,
|
|
sync_direction ENUM('push', 'pull', 'bidirectional') NOT NULL DEFAULT 'pull',
|
|
error_message VARCHAR(1024) DEFAULT NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
INDEX idx_kbs_user (user_id),
|
|
INDEX idx_kbs_kb (knowledge_base_id),
|
|
INDEX idx_kbs_status (sync_status),
|
|
UNIQUE KEY uk_user_kb (user_id, knowledge_base_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 4. Usage Reports (anonymized)
|
|
CREATE TABLE IF NOT EXISTS usage_reports (
|
|
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
|
user_hash VARCHAR(64) NOT NULL COMMENT 'SHA256 anonymized user ID',
|
|
report_type ENUM('daily', 'weekly', 'monthly', 'realtime') NOT NULL DEFAULT 'daily',
|
|
metrics JSON NOT NULL COMMENT 'Aggregated usage metrics',
|
|
period_start DATETIME NOT NULL,
|
|
period_end DATETIME NOT NULL,
|
|
is_anonymized TINYINT(1) NOT NULL DEFAULT 1,
|
|
submitted_at DATETIME DEFAULT NULL COMMENT 'When local agent submitted',
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
INDEX idx_ur_hash (user_hash),
|
|
INDEX idx_ur_type (report_type),
|
|
INDEX idx_ur_period (period_start, period_end)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 5. Add privacy_mode to distributions table
|
|
-- MariaDB 11.8 不支持 ADD COLUMN IF NOT EXISTS 语法，改用 information_schema 预检 + 动态 PREPARE 实现幂等
|
|
SET @exist_pm := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'aiflow' AND TABLE_NAME = 'distributions' AND COLUMN_NAME = 'privacy_mode');
|
|
SET @sql_pm := IF(@exist_pm = 0,
|
|
"ALTER TABLE distributions ADD COLUMN privacy_mode ENUM('local', 'cloud', 'hybrid') NOT NULL DEFAULT 'cloud' COMMENT 'Deployment mode for distribution' AFTER status",
|
|
'SELECT 1');
|
|
PREPARE stmt_pm FROM @sql_pm;
|
|
EXECUTE stmt_pm;
|
|
DEALLOCATE PREPARE stmt_pm;
|