freedakgmail
55 lines
2.0 KiB
SQL
55 lines
2.0 KiB
SQL
-- 000004: 使用记录表
|
|
|
|
-- 使用明细表
|
|
CREATE TABLE app_usage_logs (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
app_id UUID NOT NULL REFERENCES applications(id),
|
|
user_id UUID NOT NULL REFERENCES users(id),
|
|
dept_id UUID REFERENCES departments(id),
|
|
|
|
conversation_id VARCHAR(255),
|
|
message_count INTEGER NOT NULL DEFAULT 1,
|
|
|
|
prompt_tokens INTEGER NOT NULL DEFAULT 0,
|
|
completion_tokens INTEGER NOT NULL DEFAULT 0,
|
|
total_tokens INTEGER NOT NULL DEFAULT 0,
|
|
model_name VARCHAR(100),
|
|
estimated_cost DECIMAL(10, 6) NOT NULL DEFAULT 0,
|
|
|
|
duration_ms INTEGER,
|
|
is_successful BOOLEAN NOT NULL DEFAULT true,
|
|
error_message TEXT,
|
|
client_type VARCHAR(20) DEFAULT 'web'
|
|
CHECK (client_type IN ('web', 'wechat', 'dingtalk', 'feishu', 'api')),
|
|
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_usage_app ON app_usage_logs(app_id);
|
|
CREATE INDEX idx_usage_user ON app_usage_logs(user_id);
|
|
CREATE INDEX idx_usage_dept ON app_usage_logs(dept_id);
|
|
CREATE INDEX idx_usage_created ON app_usage_logs(created_at);
|
|
CREATE INDEX idx_usage_model ON app_usage_logs(model_name);
|
|
|
|
-- 日汇总表（定时任务聚合）
|
|
CREATE TABLE app_usage_daily (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
date DATE NOT NULL,
|
|
app_id UUID NOT NULL REFERENCES applications(id),
|
|
dept_id UUID REFERENCES departments(id),
|
|
|
|
total_requests BIGINT NOT NULL DEFAULT 0,
|
|
unique_users INTEGER NOT NULL DEFAULT 0,
|
|
total_tokens BIGINT NOT NULL DEFAULT 0,
|
|
total_cost DECIMAL(12, 6) NOT NULL DEFAULT 0,
|
|
avg_duration_ms INTEGER NOT NULL DEFAULT 0,
|
|
error_count INTEGER NOT NULL DEFAULT 0,
|
|
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
UNIQUE(date, app_id, dept_id)
|
|
);
|
|
|
|
CREATE INDEX idx_daily_date ON app_usage_daily(date);
|
|
CREATE INDEX idx_daily_app ON app_usage_daily(app_id);
|
|
CREATE INDEX idx_daily_dept ON app_usage_daily(dept_id);
|