# 知信图谱 — 数据库 Schema 参考

## 概览

7 表设计，渐进式复杂度：
- **个人版**：entities + relations + facts 即够用
- **团队版**：+ sources + annotations + branches + change_log

数据库路径：`~/.zhixin/zhixin.db`

---

## 核心 3 表（个体版）

### entities

```sql
CREATE TABLE entities (
    id TEXT PRIMARY KEY,          -- UUID, 自动生成
    name TEXT NOT NULL UNIQUE,    -- 实体名称（唯一）
    type TEXT NOT NULL,           -- person | project | concept | tool | event | document | org
    description TEXT,             -- 描述
    tags TEXT,                    -- JSON array, e.g. '["python","backend"]'
    branch_id TEXT DEFAULT 'main',-- 所属分支
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now'))
);
```

**实体类型说明**：
| type | 用途 | 示例 |
|------|------|------|
| person | 人类成员 | 张三 |
| project | 项目/任务 | Q3营销计划 |
| concept | 抽象概念/方法论 | 验证链模型 |
| tool | 工具/软件 | kg.py |
| event | 事件/会议 | 2026-05-10 启动会 |
| document | 文档/资料 | 需求规格说明书 v2 |
| org | 组织/团队 | 产品部 |

### relations

```sql
CREATE TABLE relations (
    id TEXT PRIMARY KEY,
    from_id TEXT NOT NULL REFERENCES entities(id) ON DELETE CASCADE,
    to_id TEXT NOT NULL REFERENCES entities(id) ON DELETE CASCADE,
    relation_type TEXT NOT NULL,
    strength REAL DEFAULT 1.0,    -- 0-1 关系强度
    source_ref TEXT,              -- "为什么认为有这个关系"
    branch_id TEXT DEFAULT 'main',
    created_at TEXT DEFAULT (datetime('now')),
    UNIQUE(from_id, to_id, relation_type)
);
```

**关系类型**：
| relation_type | 含义 | 方向 |
|--------------|------|------|
| works_on | 参与/负责 | person → project |
| knows | 了解/掌握 | person → concept/tool |
| depends_on | 依赖于 | project/tool → project/tool |
| contains | 包含 | project/concept → * |
| created_by | 创建者 | * → person |
| related_to | 一般关联 | * → * |
| before | 在...之前 | event/decision → event/decision |
| after | 在...之后 | event/decision → event/decision |

### facts

```sql
CREATE TABLE facts (
    id TEXT PRIMARY KEY,
    entity_id TEXT NOT NULL REFERENCES entities(id) ON DELETE CASCADE,
    content TEXT NOT NULL,        -- 事实陈述，一句话
    fact_type TEXT DEFAULT 'knowledge', -- knowledge|opinion|decision|status|constraint
    -- 验证链字段
    source_id TEXT REFERENCES sources(id),
    verification_state TEXT DEFAULT 'unverified',
        -- unverified | self_checked | peer_reviewed | external_confirmed | disputed | outdated | consensus
    fresh_until TEXT,             -- ISO date, 过期后自动变为 outdated
    consensus_count INTEGER DEFAULT 0,
    dispute_count INTEGER DEFAULT 0,
    -- 元数据
    branch_id TEXT DEFAULT 'main',
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now'))
);
```

**事实类型**：
| fact_type | 含义 | 典型 fresh_until |
|-----------|------|-----------------|
| knowledge | 客观知识 | NULL（不过期） |
| opinion | 个人观点 | 90 天 |
| decision | 决策记录 | NULL（历史记录） |
| status | 状态信息 | 30 天 |
| constraint | 约束条件 | NULL 或解除日期 |

---

## 扩展 4 表（团队版）

### sources

```sql
CREATE TABLE sources (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    type TEXT NOT NULL,           -- person | document | observation | external | team_member
    credibility_score REAL DEFAULT 0.5, -- 0-1, 人工评定
    reliability_notes TEXT,       -- 评定依据
    created_at TEXT DEFAULT (datetime('now'))
);
```

### annotations

```sql
CREATE TABLE annotations (
    id TEXT PRIMARY KEY,
    fact_id TEXT NOT NULL REFERENCES facts(id) ON DELETE CASCADE,
    annotator TEXT NOT NULL,      -- 标注人姓名
    annotation_type TEXT NOT NULL, -- confirm | dispute | clarify | update
    content TEXT,                 -- 标注内容
    created_at TEXT DEFAULT (datetime('now'))
);
```

**annotation_type 触发状态变更**：
| 类型 | 状态影响 |
|------|---------|
| confirm | consensus_count+1; 若 >=3 → verification_state='consensus' |
| dispute | dispute_count+1; verification_state='disputed' |
| clarify | 无状态变更，仅追加说明 |
| update | 无状态变更，建议随后 update-fact |

### branches

```sql
CREATE TABLE branches (
    id TEXT PRIMARY KEY DEFAULT 'main',
    name TEXT NOT NULL,
    description TEXT,
    created_at TEXT DEFAULT (datetime('now'))
);
```

### change_log

```sql
CREATE TABLE change_log (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    table_name TEXT NOT NULL,
    record_id TEXT NOT NULL,
    action TEXT NOT NULL,         -- insert | update | delete
    old_values TEXT,              -- JSON
    new_values TEXT,              -- JSON
    changed_by TEXT,
    changed_at TEXT DEFAULT (datetime('now'))
);
```

---

## 视图

### v_fact_verifiability

自动计算每条事实的可信任度：

```sql
CREATE VIEW v_fact_verifiability AS
SELECT
    f.id,
    f.content,
    f.verification_state,
    f.consensus_count,
    f.dispute_count,
    COALESCE(s.credibility_score, 0.5) AS source_score,
    CASE f.verification_state
        WHEN 'unverified' THEN 0.0
        WHEN 'self_checked' THEN 0.3
        WHEN 'peer_reviewed' THEN 0.7
        WHEN 'external_confirmed' THEN 1.0
        WHEN 'disputed' THEN 0.2
        WHEN 'consensus' THEN 0.9
        WHEN 'outdated' THEN 0.1
        ELSE 0.0
    END AS verification_score,
    CASE
        WHEN f.fresh_until IS NULL THEN 1.0
        WHEN date(f.fresh_until) >= date('now') THEN 1.0
        ELSE MAX(0.0, 1.0 - (julianday('now') - julianday(f.fresh_until)) / 300.0)
    END AS freshness_score,
    CASE
        WHEN f.consensus_count + f.dispute_count = 0 THEN 0.5
        ELSE CAST(f.consensus_count AS REAL) / (f.consensus_count + f.dispute_count)
    END AS consensus_ratio,
    ROUND(
        0.30 * COALESCE(s.credibility_score, 0.5)
        + 0.35 * CASE f.verification_state
            WHEN 'unverified' THEN 0.0
            WHEN 'self_checked' THEN 0.3
            WHEN 'peer_reviewed' THEN 0.7
            WHEN 'external_confirmed' THEN 1.0
            WHEN 'disputed' THEN 0.2
            WHEN 'consensus' THEN 0.9
            WHEN 'outdated' THEN 0.1
            ELSE 0.0
        END
        + 0.20 * CASE
            WHEN f.fresh_until IS NULL THEN 1.0
            WHEN date(f.fresh_until) >= date('now') THEN 1.0
            ELSE MAX(0.0, 1.0 - (julianday('now') - julianday(f.fresh_until)) / 300.0)
        END
        + 0.15 * CASE
            WHEN f.consensus_count + f.dispute_count = 0 THEN 0.5
            ELSE CAST(f.consensus_count AS REAL) / (f.consensus_count + f.dispute_count)
        END,
    2) AS verifiability
FROM facts f
LEFT JOIN sources s ON f.source_id = s.id;
```