freedakgmail
109 lines
4.3 KiB
SQL
109 lines
4.3 KiB
SQL
-- 000005: 模型配额、提供商、知识库、审计日志
|
|
|
|
-- 模型提供商表
|
|
CREATE TABLE model_providers (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
name VARCHAR(100) NOT NULL,
|
|
base_url TEXT NOT NULL,
|
|
api_key_encrypted TEXT NOT NULL,
|
|
models JSONB NOT NULL DEFAULT '[]',
|
|
is_active BOOLEAN NOT NULL DEFAULT true,
|
|
priority INTEGER NOT NULL DEFAULT 0,
|
|
config JSONB NOT NULL DEFAULT '{}',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TRIGGER update_model_providers_updated_at
|
|
BEFORE UPDATE ON model_providers
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
-- 模型配额表
|
|
CREATE TABLE model_quotas (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
target_type VARCHAR(20) NOT NULL
|
|
CHECK (target_type IN ('global', 'department', 'user')),
|
|
target_id UUID,
|
|
model_name VARCHAR(100),
|
|
daily_token_limit BIGINT,
|
|
monthly_token_limit BIGINT,
|
|
daily_request_limit INTEGER,
|
|
is_active BOOLEAN NOT NULL DEFAULT true,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_quotas_target ON model_quotas(target_type, target_id);
|
|
|
|
CREATE TRIGGER update_model_quotas_updated_at
|
|
BEFORE UPDATE ON model_quotas
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
-- 知识库表
|
|
CREATE TABLE knowledge_bases (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
name VARCHAR(100) NOT NULL,
|
|
description TEXT,
|
|
owner_id UUID NOT NULL REFERENCES users(id),
|
|
dept_id UUID REFERENCES departments(id),
|
|
dify_dataset_id VARCHAR(255),
|
|
visibility VARCHAR(20) NOT NULL DEFAULT 'private'
|
|
CHECK (visibility IN ('private', 'department', 'public')),
|
|
doc_count INTEGER NOT NULL DEFAULT 0,
|
|
total_chars BIGINT NOT NULL DEFAULT 0,
|
|
status VARCHAR(20) NOT NULL DEFAULT 'active'
|
|
CHECK (status IN ('active', 'inactive')),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_kb_owner ON knowledge_bases(owner_id);
|
|
CREATE INDEX idx_kb_dept ON knowledge_bases(dept_id);
|
|
|
|
CREATE TRIGGER update_knowledge_bases_updated_at
|
|
BEFORE UPDATE ON knowledge_bases
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
-- 知识库文档表
|
|
CREATE TABLE knowledge_documents (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
kb_id UUID NOT NULL REFERENCES knowledge_bases(id) ON DELETE CASCADE,
|
|
name VARCHAR(255) NOT NULL,
|
|
file_url TEXT,
|
|
file_type VARCHAR(20)
|
|
CHECK (file_type IN ('pdf', 'docx', 'txt', 'md', 'csv', 'xlsx')),
|
|
file_size BIGINT NOT NULL DEFAULT 0,
|
|
char_count BIGINT NOT NULL DEFAULT 0,
|
|
dify_doc_id VARCHAR(255),
|
|
indexing_status VARCHAR(20) NOT NULL DEFAULT 'pending'
|
|
CHECK (indexing_status IN ('pending', 'indexing', 'completed', 'failed')),
|
|
uploader_id UUID NOT NULL REFERENCES users(id),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_docs_kb ON knowledge_documents(kb_id);
|
|
CREATE INDEX idx_docs_status ON knowledge_documents(indexing_status);
|
|
|
|
CREATE TRIGGER update_knowledge_documents_updated_at
|
|
BEFORE UPDATE ON knowledge_documents
|
|
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
-- 审计日志表
|
|
CREATE TABLE audit_logs (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id UUID REFERENCES users(id),
|
|
action VARCHAR(50) NOT NULL,
|
|
resource_type VARCHAR(50) NOT NULL,
|
|
resource_id UUID,
|
|
details JSONB NOT NULL DEFAULT '{}',
|
|
ip_address INET,
|
|
user_agent TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_audit_user ON audit_logs(user_id);
|
|
CREATE INDEX idx_audit_action ON audit_logs(action);
|
|
CREATE INDEX idx_audit_resource ON audit_logs(resource_type, resource_id);
|
|
CREATE INDEX idx_audit_created ON audit_logs(created_at);
|