-- grit-core schema, version 5.
-- This file is the truth for the on-disk layout (see AGENTS.md "Schema sketch").
-- Migrations are forward-only via PRAGMA user_version; this file always creates
-- the *latest* version from scratch. Never edit historical migrations.
--
-- v2 (grit 0.2.0): node_updates — per-field LWW records for UpdateNode.
-- v3 (grit 0.2.2): episodes.kind — opaque source-kind tag.
-- v4 (grit 0.2.3): trigram FTS mirrors — CJK substring recall.
-- v5 (grit 0.2.4): vec_nodes/vec_edges rebuilt with a group_id PARTITION KEY
--   (lazily created tables — see the note at the bottom of this file).

-- Entities. `expired_at` / `merged_into` are set by MergeNodes / never mutated
-- otherwise; nodes carry only the system timeline (facts live on edges).
CREATE TABLE nodes (
    id          TEXT PRIMARY KEY,            -- UUIDv7
    kind        TEXT NOT NULL,
    name        TEXT NOT NULL,
    summary     TEXT NOT NULL DEFAULT '',
    attrs       TEXT NOT NULL DEFAULT '{}',  -- JSON object
    group_id    TEXT NOT NULL DEFAULT '',
    created_at  INTEGER NOT NULL,            -- ms since epoch, from op HLC wall time
    expired_at  INTEGER,                     -- system time: when we stopped believing
    merged_into TEXT,                        -- set by MergeNodes; audit trail
    hlc         TEXT NOT NULL
) STRICT;

CREATE INDEX idx_nodes_group ON nodes (group_id);
CREATE INDEX idx_nodes_name  ON nodes (name);
CREATE INDEX idx_nodes_kind  ON nodes (kind);
-- Reverse merge lookups ("which nodes folded into X?") power the alias CTEs
-- in provenance reads and merge re-pointing; without this they scan the
-- whole nodes table per hit.
CREATE INDEX idx_nodes_merged ON nodes (merged_into) WHERE merged_into IS NOT NULL;

-- The "fat edge": the fact sentence lives here (Graphiti's core trick).
-- Two timelines: event time (valid_at/invalid_at — when true in the world) and
-- system time (created_at/expired_at — when we believed it).
CREATE TABLE edges (
    id         TEXT PRIMARY KEY,             -- UUIDv7
    src        TEXT NOT NULL,
    dst        TEXT NOT NULL,
    rel        TEXT NOT NULL,
    fact       TEXT NOT NULL DEFAULT '',
    attrs      TEXT NOT NULL DEFAULT '{}',
    group_id   TEXT NOT NULL DEFAULT '',
    valid_at   INTEGER,                      -- event time: fact became true (NULL = unknown/always)
    invalid_at INTEGER,                      -- event time: fact stopped being true
    created_at INTEGER NOT NULL,             -- system time: first believed
    expired_at INTEGER,                      -- system time: belief retracted
    hlc        TEXT NOT NULL
) STRICT;

CREATE INDEX idx_edges_src   ON edges (src);
CREATE INDEX idx_edges_dst   ON edges (dst);
CREATE INDEX idx_edges_group ON edges (group_id);

-- InvalidateEdge ops are recorded append-only here, even if the edge row has
-- not arrived yet (op-order independence); edges.invalid_at is derived as MIN
-- over these rows. recorded_at is the system time the invalidation was
-- learned, which makes invalidations belief-versioned: a query as_at March
-- ignores invalidations recorded in April.
CREATE TABLE edge_invalidations (
    edge_id     TEXT NOT NULL,
    invalid_at  INTEGER NOT NULL,
    recorded_at INTEGER NOT NULL,
    hlc         TEXT NOT NULL,
    PRIMARY KEY (edge_id, invalid_at)
) STRICT, WITHOUT ROWID;

-- UpdateNode ops are recorded per (node, field) here, even if the node row
-- has not arrived yet (op-order independence, same pattern as
-- edge_invalidations); node fields are derived by folding the winning value
-- over the AddNode base content. Each row holds the current per-field
-- winner — highest HLC wins; full history lives in the oplog.
CREATE TABLE node_updates (
    node_id TEXT NOT NULL,
    field   TEXT NOT NULL,               -- 'name' | 'summary' | 'kind' | 'attrs'
    value   TEXT NOT NULL,               -- attrs stored as JSON text
    hlc     TEXT NOT NULL,
    PRIMARY KEY (node_id, field)
) STRICT, WITHOUT ROWID;

-- Right-to-forget tombstones. A purged id is dead forever: applying an Add*
-- op for a tombstoned id is a no-op, so Purge commutes with concurrent adds
-- (the oplog merge laws hold in every interleaving). merged_into preserves
-- the merge pointer of a purged node — content is forgotten, but canonical
-- resolution must stay order-independent.
CREATE TABLE purged (
    id          TEXT PRIMARY KEY,
    merged_into TEXT
) STRICT, WITHOUT ROWID;

CREATE INDEX idx_purged_merged ON purged (merged_into) WHERE merged_into IS NOT NULL;

-- Raw provenance: a chat turn, a document chunk, a sensor-event summary.
CREATE TABLE episodes (
    id          TEXT PRIMARY KEY,            -- UUIDv7
    source      TEXT NOT NULL,
    kind        TEXT NOT NULL DEFAULT '',    -- source-kind tag, opaque (v3)
    content     TEXT NOT NULL,
    occurred_at INTEGER NOT NULL,            -- event time
    group_id    TEXT NOT NULL DEFAULT '',
    created_at  INTEGER NOT NULL,
    hlc         TEXT NOT NULL
) STRICT;

CREATE INDEX idx_episodes_group ON episodes (group_id);

-- Every fact traces to its sources. target_id is a node or edge id.
CREATE TABLE mentions (
    episode_id TEXT NOT NULL,
    target_id  TEXT NOT NULL,
    PRIMARY KEY (episode_id, target_id)
) STRICT, WITHOUT ROWID;

CREATE INDEX idx_mentions_target ON mentions (target_id);

-- Append-only op-log: the sync unit. Graph tables above are derived state
-- applied in the same transaction. seq is local order only; merge order across
-- devices is by hlc.
CREATE TABLE oplog (
    seq        INTEGER PRIMARY KEY,          -- local rowid, never shipped
    id         TEXT NOT NULL UNIQUE,         -- UUIDv7, global identity of the op
    hlc        TEXT NOT NULL,
    device_id  TEXT NOT NULL,
    op         TEXT NOT NULL,                -- GraphOp as JSON
    applied_at INTEGER NOT NULL              -- local wall time when applied here
) STRICT;

CREATE INDEX idx_oplog_hlc ON oplog (hlc);

-- Embeddings are recomputable local state, NOT in the oplog. One active model.
CREATE TABLE embedding_meta (
    id            INTEGER PRIMARY KEY CHECK (id = 1),
    model_id      TEXT NOT NULL,
    dim           INTEGER NOT NULL,
    model_version TEXT NOT NULL
) STRICT;

-- Full-text mirrors, maintained by triggers.
CREATE VIRTUAL TABLE nodes_fts USING fts5(
    name, summary,
    content='nodes', content_rowid='rowid'
);

CREATE TRIGGER nodes_fts_ai AFTER INSERT ON nodes BEGIN
    INSERT INTO nodes_fts(rowid, name, summary)
    VALUES (new.rowid, new.name, new.summary);
END;

CREATE TRIGGER nodes_fts_ad AFTER DELETE ON nodes BEGIN
    INSERT INTO nodes_fts(nodes_fts, rowid, name, summary)
    VALUES ('delete', old.rowid, old.name, old.summary);
END;

-- UPDATE OF: merges touch merged_into/expired_at and must not churn the FTS
-- index for unchanged text.
CREATE TRIGGER nodes_fts_au AFTER UPDATE OF name, summary ON nodes BEGIN
    INSERT INTO nodes_fts(nodes_fts, rowid, name, summary)
    VALUES ('delete', old.rowid, old.name, old.summary);
    INSERT INTO nodes_fts(rowid, name, summary)
    VALUES (new.rowid, new.name, new.summary);
END;

CREATE VIRTUAL TABLE edges_fts USING fts5(
    fact,
    content='edges', content_rowid='rowid'
);

CREATE TRIGGER edges_fts_ai AFTER INSERT ON edges BEGIN
    INSERT INTO edges_fts(rowid, fact) VALUES (new.rowid, new.fact);
END;

CREATE TRIGGER edges_fts_ad AFTER DELETE ON edges BEGIN
    INSERT INTO edges_fts(edges_fts, rowid, fact) VALUES ('delete', old.rowid, old.fact);
END;

-- UPDATE OF: merge re-pointing (src/dst) and invalidations must not rewrite
-- FTS entries whose fact text did not change.
CREATE TRIGGER edges_fts_au AFTER UPDATE OF fact ON edges BEGIN
    INSERT INTO edges_fts(edges_fts, rowid, fact) VALUES ('delete', old.rowid, old.fact);
    INSERT INTO edges_fts(rowid, fact) VALUES (new.rowid, new.fact);
END;

CREATE VIRTUAL TABLE episodes_fts USING fts5(
    content,
    content='episodes', content_rowid='rowid'
);

CREATE TRIGGER episodes_fts_ai AFTER INSERT ON episodes BEGIN
    INSERT INTO episodes_fts(rowid, content) VALUES (new.rowid, new.content);
END;

CREATE TRIGGER episodes_fts_ad AFTER DELETE ON episodes BEGIN
    INSERT INTO episodes_fts(episodes_fts, rowid, content)
    VALUES ('delete', old.rowid, old.content);
END;

CREATE TRIGGER episodes_fts_au AFTER UPDATE OF content ON episodes BEGIN
    INSERT INTO episodes_fts(episodes_fts, rowid, content)
    VALUES ('delete', old.rowid, old.content);
    INSERT INTO episodes_fts(rowid, content) VALUES (new.rowid, new.content);
END;

-- Trigram mirrors (schema v4): substring matching for scripts unicode61
-- cannot segment (Han/CJK text has no word boundaries — a word query
-- against unicode61 matches nothing). Same external-content pattern; the
-- search layer feeds these as an ADDITIONAL ranked list into RRF fusion
-- for queries with a >= 3-char token (the trigram tokenizer's floor).
-- Cost: measured ~1.7x the unicode61 index size on mixed EN/CJK text
-- (2k rows) — the price of substring recall, paid only in local disk.
CREATE VIRTUAL TABLE nodes_fts_tri USING fts5(
    name, summary,
    content='nodes', content_rowid='rowid',
    tokenize='trigram'
);

CREATE TRIGGER nodes_fts_tri_ai AFTER INSERT ON nodes BEGIN
    INSERT INTO nodes_fts_tri(rowid, name, summary)
    VALUES (new.rowid, new.name, new.summary);
END;

CREATE TRIGGER nodes_fts_tri_ad AFTER DELETE ON nodes BEGIN
    INSERT INTO nodes_fts_tri(nodes_fts_tri, rowid, name, summary)
    VALUES ('delete', old.rowid, old.name, old.summary);
END;

CREATE TRIGGER nodes_fts_tri_au AFTER UPDATE OF name, summary ON nodes BEGIN
    INSERT INTO nodes_fts_tri(nodes_fts_tri, rowid, name, summary)
    VALUES ('delete', old.rowid, old.name, old.summary);
    INSERT INTO nodes_fts_tri(rowid, name, summary)
    VALUES (new.rowid, new.name, new.summary);
END;

CREATE VIRTUAL TABLE edges_fts_tri USING fts5(
    fact,
    content='edges', content_rowid='rowid',
    tokenize='trigram'
);

CREATE TRIGGER edges_fts_tri_ai AFTER INSERT ON edges BEGIN
    INSERT INTO edges_fts_tri(rowid, fact) VALUES (new.rowid, new.fact);
END;

CREATE TRIGGER edges_fts_tri_ad AFTER DELETE ON edges BEGIN
    INSERT INTO edges_fts_tri(edges_fts_tri, rowid, fact)
    VALUES ('delete', old.rowid, old.fact);
END;

CREATE TRIGGER edges_fts_tri_au AFTER UPDATE OF fact ON edges BEGIN
    INSERT INTO edges_fts_tri(edges_fts_tri, rowid, fact)
    VALUES ('delete', old.rowid, old.fact);
    INSERT INTO edges_fts_tri(rowid, fact) VALUES (new.rowid, new.fact);
END;

CREATE VIRTUAL TABLE episodes_fts_tri USING fts5(
    content,
    content='episodes', content_rowid='rowid',
    tokenize='trigram'
);

CREATE TRIGGER episodes_fts_tri_ai AFTER INSERT ON episodes BEGIN
    INSERT INTO episodes_fts_tri(rowid, content) VALUES (new.rowid, new.content);
END;

CREATE TRIGGER episodes_fts_tri_ad AFTER DELETE ON episodes BEGIN
    INSERT INTO episodes_fts_tri(episodes_fts_tri, rowid, content)
    VALUES ('delete', old.rowid, old.content);
END;

CREATE TRIGGER episodes_fts_tri_au AFTER UPDATE OF content ON episodes BEGIN
    INSERT INTO episodes_fts_tri(episodes_fts_tri, rowid, content)
    VALUES ('delete', old.rowid, old.content);
    INSERT INTO episodes_fts_tri(rowid, content) VALUES (new.rowid, new.content);
END;

-- vec_nodes / vec_edges (sqlite-vec) are created lazily by
-- register_embedding_model() because the dimension is model-dependent:
--   CREATE VIRTUAL TABLE vec_nodes USING vec0(
--       id TEXT PRIMARY KEY, group_id TEXT PARTITION KEY,
--       embedding FLOAT[<dim>] distance_metric=cosine);
-- group_id (v5) is a PARTITION KEY: KNN legs scan only the query's group,
-- so small groups are never crowded out of the top-k by large ones. The
-- partition value is copied from the base row when a vector is stored —
-- group_id is immutable on nodes/edges, so it can never go stale.
