77 lines
3.5 KiB
SQL
77 lines
3.5 KiB
SQL
-- 中心服务端库（school_assistant_hub）。桌面端那套表在 school_assistant 库里，两者不共用。
|
||
|
||
-- 注册的学校实例。桌面端带 token 上报，服务端据此认人。
|
||
CREATE TABLE IF NOT EXISTS schools (
|
||
id VARCHAR(64) PRIMARY KEY,
|
||
name VARCHAR(128) NOT NULL DEFAULT '',
|
||
token_hash VARCHAR(64) NOT NULL,
|
||
note TEXT NOT NULL DEFAULT '',
|
||
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
||
last_seen_at TIMESTAMP NULL,
|
||
last_report_at TIMESTAMP NULL,
|
||
last_pull_at TIMESTAMP NULL
|
||
);
|
||
|
||
-- 中心的知识条目：既是审核队列，也是发布源。
|
||
-- 同一条知识可能被多所学校各报一次，这里按内容合并成一条，observations 是汇总观察次数。
|
||
CREATE TABLE IF NOT EXISTS hub_entries (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
knowledge_id VARCHAR(64) NOT NULL,
|
||
business_id VARCHAR(64) NOT NULL DEFAULT '',
|
||
"key" VARCHAR(128) NOT NULL,
|
||
"value" TEXT NOT NULL,
|
||
category VARCHAR(16) NOT NULL DEFAULT 'note',
|
||
status VARCHAR(16) NOT NULL DEFAULT 'draft', -- draft / verified / rejected
|
||
scope VARCHAR(16) NOT NULL DEFAULT 'origin', -- origin=只发回来源学校，all=发给所有学校
|
||
observations INT NOT NULL DEFAULT 1, -- 各校观察次数之和
|
||
school_count INT NOT NULL DEFAULT 1, -- 有多少所学校报过这条
|
||
origin_school_id VARCHAR(64) NOT NULL DEFAULT '',
|
||
-- 发布版本号：桌面端记住上次拉到哪个版本，之后只拉增量
|
||
version BIGINT NOT NULL DEFAULT 0,
|
||
-- 智能预审：模型给的建议和理由，只是建议，不自动生效
|
||
suggestion VARCHAR(16) NULL, -- adopt / reject / review
|
||
suggestion_note TEXT NOT NULL DEFAULT '',
|
||
suggested_at TIMESTAMP NULL,
|
||
reviewed_by VARCHAR(32) NULL,
|
||
reviewed_at TIMESTAMP NULL,
|
||
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_hub_entries_queue ON hub_entries (status, knowledge_id, business_id);
|
||
CREATE INDEX IF NOT EXISTS idx_hub_entries_version ON hub_entries (version) WHERE status = 'verified';
|
||
|
||
-- 哪所学校报过哪条（用于 school_count 和溯源）
|
||
CREATE TABLE IF NOT EXISTS hub_entry_sources (
|
||
entry_id BIGINT NOT NULL REFERENCES hub_entries(id) ON DELETE CASCADE,
|
||
school_id VARCHAR(64) NOT NULL,
|
||
source_task_id VARCHAR(64) NOT NULL DEFAULT '',
|
||
observations INT NOT NULL DEFAULT 1,
|
||
reported_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
||
PRIMARY KEY (entry_id, school_id)
|
||
);
|
||
|
||
-- 同步流水，用于后台的运行概览
|
||
CREATE TABLE IF NOT EXISTS sync_log (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
school_id VARCHAR(64) NOT NULL,
|
||
direction VARCHAR(16) NOT NULL, -- report / pull
|
||
count INT NOT NULL DEFAULT 0,
|
||
detail TEXT NOT NULL DEFAULT '',
|
||
at TIMESTAMP NOT NULL DEFAULT NOW()
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_sync_log_at ON sync_log (at DESC);
|
||
|
||
-- 全局递增的发布版本号
|
||
CREATE TABLE IF NOT EXISTS hub_meta (
|
||
"key" VARCHAR(64) PRIMARY KEY,
|
||
"value" TEXT NOT NULL DEFAULT ''
|
||
);
|
||
|
||
-- 试用证据（D19）：各校 Agent 在真实页面上验证的次数，比观察次数硬得多
|
||
ALTER TABLE hub_entries ADD COLUMN IF NOT EXISTS confirmed INT NOT NULL DEFAULT 0;
|
||
ALTER TABLE hub_entries ADD COLUMN IF NOT EXISTS contradicted INT NOT NULL DEFAULT 0;
|
||
ALTER TABLE hub_entry_sources ADD COLUMN IF NOT EXISTS confirmed INT NOT NULL DEFAULT 0;
|
||
ALTER TABLE hub_entry_sources ADD COLUMN IF NOT EXISTS contradicted INT NOT NULL DEFAULT 0;
|