PostgreSQL Schema Reference

Esta página aún no está disponible en tu idioma.

Full specification: 00-product-spec.md. PostgreSQL is the index and activity-fact layer; OKF Markdown is the source of truth for Memory, Knowledge, and Entities.

1. Design Principles

Principle	Description
OKF is the source of truth	Long-lived knowledge and entity content is written to OKF; PostgreSQL can be rebuilt from it
Activity is written to PostgreSQL	High-frequency observation events, screen segments, and UI state are written directly to PostgreSQL
Evidence first	Every crystallized result must trace back to an observation, a source file, or an OKF record
Structure first	UI structure, layout, and behavior events are modeled independently; OCR produces text boxes
Unified time	All timestamps use `TIMESTAMPTZ`
Unified vectors	Local embeddings default to `vector(1024)`, determined by the active embedder

2. Entity-Relationship Overview

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. Existing Activity Tables

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 Target Tables

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 includes editor, terminal, sidebar, toolbar, input, chat_list, file_tree, table, canvas, modal, browser_content, and 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 includes read, edit, run_command, copy, paste, send_message, select_file, switch_tab, switch_context, design_adjust, and 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"
}

Automated conclusions must persist their evidence. Storing an LLM output with no traceable source is not allowed.