PostgreSQL Schema 参考

完整规格见 00-product-spec.md。PostgreSQL 是索引和活动事实层，OKF Markdown 是 Memory / Knowledge / Entity 的真源。

1. 设计原则

原则	说明
OKF 是真源	长期知识和实体正文写入 OKF，PostgreSQL 可从中重建
活动写入 PostgreSQL	高频观察事件、屏幕分段、UI 状态直接写入 PostgreSQL
Evidence 优先	结晶结果必须能追溯到某个 observation、source_file 或 OKF 记录
结构优先	UI 结构、布局、行为事件独立建模；OCR 产出的是文本框
时间统一	所有时间戳统一使用 TIMESTAMPTZ
向量统一	本地 embedding 默认 vector(1024)，由当前生效的 embedder 决定

2. 实体关系概览

erDiagram
  activity_sessions ||--o{ activity_events : has
  activity_sessions ||--o{ activity_screen_segments : has
  activity_sessions ||--o{ ui_snapshots : has
  activity_sessions ||--o{ ui_behavior_events : has
  ui_snapshots ||--o{ ui_nodes : has
  ui_snapshots ||--o{ ui_regions : has
  ui_snapshots ||--o{ ui_text_boxes : has
  ai_tool_sessions }o--o{ projects : links
  ai_tool_sessions }o--o{ tasks : links
  ai_tool_sessions }o--o{ activity_sessions : links
  entities ||--o{ entity_sightings : has
  entities ||--o{ entity_links : has
  entities ||--o{ entity_revisions : has
  entities ||--o{ entity_overrides : has

3. 现有 Activity 表

CREATE TABLE activity_sessions (
    id               TEXT PRIMARY KEY,
    start_time       TIMESTAMPTZ NOT NULL,
    end_time         TIMESTAMPTZ NOT NULL,
    trigger_kind     TEXT NOT NULL DEFAULT 'auto',
    app_names        TEXT[] NOT NULL DEFAULT '{}',
    event_count      INTEGER NOT NULL DEFAULT 0,
    segment_count    INTEGER NOT NULL DEFAULT 0,
    snapshot_count   INTEGER NOT NULL DEFAULT 0,
    total_size_bytes BIGINT NOT NULL DEFAULT 0,
    triage_status    TEXT NOT NULL DEFAULT 'pending',
    ocr_status       TEXT NOT NULL DEFAULT 'pending',
    semantic_status  TEXT NOT NULL DEFAULT 'pending',
    created_at       TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE activity_screen_segments (
    id            BIGSERIAL PRIMARY KEY,
    session_id    TEXT REFERENCES activity_sessions(id) ON DELETE SET NULL,
    segment_path  TEXT NOT NULL,
    started_at    TIMESTAMPTZ NOT NULL,
    duration_ms   INTEGER NOT NULL DEFAULT 0,
    fps           REAL NOT NULL DEFAULT 0,
    width         INTEGER NOT NULL DEFAULT 0,
    height        INTEGER NOT NULL DEFAULT 0,
    codec         TEXT NOT NULL DEFAULT '',
    ocr_status    TEXT NOT NULL DEFAULT 'pending',
    ocr_text      TEXT,
    redacted_text TEXT,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

4. UI State 目标表

4.1 ui_snapshots

CREATE TABLE ui_snapshots (
    id              BIGSERIAL PRIMARY KEY,
    session_id      TEXT REFERENCES activity_sessions(id) ON DELETE CASCADE,
    captured_at     TIMESTAMPTZ NOT NULL,
    app_name        TEXT NOT NULL DEFAULT '',
    bundle_id       TEXT NOT NULL DEFAULT '',
    pid             INTEGER NOT NULL DEFAULT 0,
    window_title    TEXT NOT NULL DEFAULT '',
    window_id       BIGINT NOT NULL DEFAULT 0,
    window_bounds   JSONB NOT NULL DEFAULT '{}',
    browser_url     TEXT NOT NULL DEFAULT '',
    repo_path       TEXT NOT NULL DEFAULT '',
    document_path   TEXT NOT NULL DEFAULT '',
    focused_node_id TEXT NOT NULL DEFAULT '',
    source_mask     TEXT[] NOT NULL DEFAULT '{}',
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

4.2 ui_nodes

CREATE TABLE ui_nodes (
    id           TEXT PRIMARY KEY,
    snapshot_id  BIGINT NOT NULL REFERENCES ui_snapshots(id) ON DELETE CASCADE,
    parent_id    TEXT NOT NULL DEFAULT '',
    role         TEXT NOT NULL DEFAULT '',
    subrole      TEXT NOT NULL DEFAULT '',
    label        TEXT NOT NULL DEFAULT '',
    value        TEXT NOT NULL DEFAULT '',
    description  TEXT NOT NULL DEFAULT '',
    bounds       JSONB NOT NULL DEFAULT '{}',
    state        JSONB NOT NULL DEFAULT '{}',
    source       TEXT NOT NULL,
    confidence   REAL NOT NULL DEFAULT 1.0
);

4.3 ui_regions

CREATE TABLE ui_regions (
    id           BIGSERIAL PRIMARY KEY,
    snapshot_id  BIGINT NOT NULL REFERENCES ui_snapshots(id) ON DELETE CASCADE,
    kind         TEXT NOT NULL,
    bounds       JSONB NOT NULL DEFAULT '{}',
    text         TEXT NOT NULL DEFAULT '',
    source       TEXT NOT NULL,
    confidence   REAL NOT NULL DEFAULT 0,
    metadata     JSONB NOT NULL DEFAULT '{}'
);

kind 包括 editor、terminal、sidebar、toolbar、input、chat_list、file_tree、table、canvas、modal、browser_content、unknown。

4.4 ui_text_boxes

CREATE TABLE ui_text_boxes (
    id            BIGSERIAL PRIMARY KEY,
    snapshot_id   BIGINT NOT NULL REFERENCES ui_snapshots(id) ON DELETE CASCADE,
    region_id     BIGINT REFERENCES ui_regions(id) ON DELETE SET NULL,
    text          TEXT NOT NULL,
    redacted_text TEXT NOT NULL DEFAULT '',
    bounds        JSONB NOT NULL DEFAULT '{}',
    confidence    REAL NOT NULL DEFAULT 0,
    reading_order INTEGER NOT NULL DEFAULT 0,
    source        TEXT NOT NULL,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

4.5 ui_behavior_events

CREATE TABLE ui_behavior_events (
    id               BIGSERIAL PRIMARY KEY,
    session_id       TEXT REFERENCES activity_sessions(id) ON DELETE CASCADE,
    from_snapshot_id BIGINT REFERENCES ui_snapshots(id) ON DELETE SET NULL,
    to_snapshot_id   BIGINT REFERENCES ui_snapshots(id) ON DELETE SET NULL,
    occurred_at      TIMESTAMPTZ NOT NULL,
    action_kind      TEXT NOT NULL,
    app_name         TEXT NOT NULL DEFAULT '',
    target_region_id BIGINT REFERENCES ui_regions(id) ON DELETE SET NULL,
    confidence       REAL NOT NULL DEFAULT 0,
    evidence         JSONB NOT NULL DEFAULT '{}',
    created_at       TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

action_kind 包括 read、edit、run_command、copy、paste、send_message、select_file、switch_tab、switch_context、design_adjust、review。

5. Evidence JSON

{
  "sources": [
    {
      "kind": "ui_behavior_event",
      "id": "123",
      "field": "action_kind"
    }
  ],
  "confidence": 0.86,
  "notes": "editor region text changed and active file path stayed stable"
}

自动生成的结论必须保存 evidence。禁止只保存无法追溯来源的 LLM 输出。