125 lines
4.7 KiB
SQL
125 lines
4.7 KiB
SQL
-- PostgreSQL 17 schema. 语句以分号分隔，由 database._apply_schema 逐条执行，注释里不要出现分号
|
||
-- 多校隔离：knowledge / tasks / system_accounts / learned_entries / skills 都带 school_id，settings 是实例级配置不带
|
||
-- updated_at 由应用在 UPDATE 时显式写 NOW()
|
||
|
||
CREATE TABLE IF NOT EXISTS knowledge (
|
||
school_id VARCHAR(64) NOT NULL DEFAULT 'default',
|
||
id VARCHAR(64) NOT NULL,
|
||
name VARCHAR(255) NOT NULL,
|
||
content JSONB NOT NULL,
|
||
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
||
PRIMARY KEY (school_id, id)
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS skills (
|
||
school_id VARCHAR(64) NOT NULL DEFAULT 'default',
|
||
id VARCHAR(64) NOT NULL,
|
||
version VARCHAR(32) NOT NULL,
|
||
knowledge_id VARCHAR(64) NOT NULL,
|
||
status VARCHAR(32) DEFAULT 'draft',
|
||
content JSONB NOT NULL,
|
||
created_from_task_id VARCHAR(64),
|
||
verified_at TIMESTAMP NULL,
|
||
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
||
PRIMARY KEY (id, version)
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_skills_knowledge ON skills (knowledge_id);
|
||
|
||
CREATE TABLE IF NOT EXISTS tasks (
|
||
id VARCHAR(64) PRIMARY KEY,
|
||
school_id VARCHAR(64) NOT NULL DEFAULT 'default',
|
||
student_id VARCHAR(64) DEFAULT 'default',
|
||
knowledge_id VARCHAR(64) NOT NULL,
|
||
status VARCHAR(32) DEFAULT 'collecting',
|
||
current_step VARCHAR(128),
|
||
inputs JSONB,
|
||
artifacts JSONB,
|
||
skill_version VARCHAR(32),
|
||
browser_profile_path VARCHAR(512),
|
||
pause_reason TEXT,
|
||
next_check_at TIMESTAMP NULL,
|
||
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_tasks_school ON tasks (school_id, updated_at);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_tasks_student ON tasks (student_id);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_tasks_status ON tasks (status);
|
||
|
||
CREATE TABLE IF NOT EXISTS run_logs (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
task_id VARCHAR(64) NOT NULL,
|
||
step_id VARCHAR(128),
|
||
agent_history JSONB,
|
||
screenshots JSONB,
|
||
verdict VARCHAR(32),
|
||
started_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
||
finished_at TIMESTAMP NULL
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_run_logs_task ON run_logs (task_id);
|
||
|
||
CREATE TABLE IF NOT EXISTS settings (
|
||
"key" VARCHAR(128) PRIMARY KEY,
|
||
"value" TEXT NOT NULL
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS system_accounts (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
school_id VARCHAR(64) NOT NULL DEFAULT 'default',
|
||
student_id VARCHAR(64) NOT NULL,
|
||
system_id VARCHAR(64) NOT NULL,
|
||
role VARCHAR(32) NOT NULL DEFAULT '',
|
||
username VARCHAR(128) NOT NULL,
|
||
password_enc TEXT NOT NULL,
|
||
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
||
UNIQUE (school_id, student_id, system_id)
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS learned_entries (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
school_id VARCHAR(64) NOT NULL DEFAULT 'default',
|
||
knowledge_id VARCHAR(64) NOT NULL,
|
||
business_id VARCHAR(64) NOT NULL DEFAULT '',
|
||
"key" VARCHAR(128) NOT NULL,
|
||
"value" TEXT NOT NULL,
|
||
status VARCHAR(16) NOT NULL DEFAULT 'draft',
|
||
category VARCHAR(16) NOT NULL DEFAULT 'note',
|
||
observations INT NOT NULL DEFAULT 1,
|
||
source_task_id VARCHAR(64),
|
||
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
||
verified_at TIMESTAMP NULL,
|
||
verified_by VARCHAR(16) NULL,
|
||
UNIQUE (school_id, knowledge_id, business_id, "key", status)
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_learned_task ON learned_entries (source_task_id);
|
||
|
||
-- 试用制（D19）：草稿不再压着等人审，直接作为「待验证线索」注入提示词，用运行结果当裁判。
|
||
-- 每次任务结束时 Agent 逐条回答哪条与页面相符、哪条不符，证据记在这里。
|
||
ALTER TABLE learned_entries ADD COLUMN IF NOT EXISTS trials INT NOT NULL DEFAULT 0;
|
||
ALTER TABLE learned_entries ADD COLUMN IF NOT EXISTS confirmed INT NOT NULL DEFAULT 0;
|
||
ALTER TABLE learned_entries ADD COLUMN IF NOT EXISTS contradicted INT NOT NULL DEFAULT 0;
|
||
ALTER TABLE learned_entries ADD COLUMN IF NOT EXISTS last_trial_at TIMESTAMP NULL;
|
||
ALTER TABLE learned_entries ADD COLUMN IF NOT EXISTS last_outcome TEXT NOT NULL DEFAULT '';
|
||
|
||
-- 每次注入与回执的流水，用于追溯某条知识是被哪次任务证实或证伪的
|
||
CREATE TABLE IF NOT EXISTS learned_trials (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
school_id VARCHAR(64) NOT NULL DEFAULT 'default',
|
||
entry_id BIGINT NOT NULL REFERENCES learned_entries(id) ON DELETE CASCADE,
|
||
task_id VARCHAR(64) NOT NULL,
|
||
outcome VARCHAR(16) NOT NULL DEFAULT 'unused',
|
||
note TEXT NOT NULL DEFAULT '',
|
||
at TIMESTAMP NOT NULL DEFAULT NOW(),
|
||
UNIQUE (entry_id, task_id)
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_learned_trials_entry ON learned_trials (entry_id);
|