schema.sql

-- Schema: a minimal, general-purpose property graph in plain Postgres.
--
-- Two tables. That's the point: for graphs that fit in one database
-- (millions of nodes, tens of millions of edges), Postgres with the right
-- indexes handles traversal, similarity, and ranking without a dedicated
-- graph database.

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS vector;

-- ---------------------------------------------------------------------------
-- Nodes: one row per entity. `kind` discriminates (person, org, venue, work…).
-- `name_embedding` holds a vector for semantic similarity (dimension 384 to
-- match small sentence-transformer models; use whatever your model emits).
-- ---------------------------------------------------------------------------
CREATE TABLE nodes (
    id             uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    kind           text NOT NULL,
    name           text NOT NULL,
    name_embedding vector(384),
    created_at     timestamptz NOT NULL DEFAULT now()
);

-- Trigram index: fuzzy name matching (pattern 04).
CREATE INDEX idx_nodes_name_trgm ON nodes USING gin (name gin_trgm_ops);

-- HNSW index: approximate nearest-neighbor cosine search (pattern 05).
-- HNSW over ivfflat: no training step, better recall at low k, and inserts
-- degrade gracefully. m/ef_construction are the common starting values.
CREATE INDEX idx_nodes_embedding_hnsw ON nodes
    USING hnsw (name_embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

-- ---------------------------------------------------------------------------
-- Edges: typed, directed-or-bidirectional, confidence-scored relationships.
--
-- `direction` semantics:
--   'directed'      — src → tgt means something src-to-tgt (manages, endorsed)
--   'bidirectional' — the relationship is symmetric (collaborated_with);
--                     stored once, in whichever (src, tgt) order it arrived
-- ---------------------------------------------------------------------------
CREATE TABLE edges (
    id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    src        uuid NOT NULL REFERENCES nodes(id),
    tgt        uuid NOT NULL REFERENCES nodes(id),
    kind       text NOT NULL,
    direction  text NOT NULL DEFAULT 'directed'
                    CHECK (direction IN ('directed', 'bidirectional')),
    confidence real NOT NULL DEFAULT 0.5
                    CHECK (confidence BETWEEN 0 AND 1),
    created_at timestamptz NOT NULL DEFAULT now(),
    CHECK (src <> tgt)
);

-- Directional indexes. Traversals probe both (see pattern 01's LATERAL trick)
-- instead of using OR, which would force a seq scan.
CREATE INDEX idx_edges_src ON edges (src, tgt);
CREATE INDEX idx_edges_tgt ON edges (tgt, src);

-- Canonical-pair uniqueness (pattern 03 explains these in depth):
-- a bidirectional edge between A and B must be unique regardless of storage
-- order, so the index normalizes the pair with LEAST/GREATEST. Directed edges
-- are unique on the ordered triple. Partial indexes let one table carry both
-- semantics.
CREATE UNIQUE INDEX uq_edges_bidirectional
    ON edges (LEAST(src, tgt), GREATEST(src, tgt), kind)
    WHERE direction = 'bidirectional';

CREATE UNIQUE INDEX uq_edges_directed
    ON edges (src, tgt, kind)
    WHERE direction = 'directed';

-- ---------------------------------------------------------------------------
-- Events: timestamped activity attached to nodes. Feeds the momentum matview
-- (pattern 06) and the keyset-pagination feed (pattern 07).
-- ---------------------------------------------------------------------------
CREATE TABLE events (
    id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    node_id    uuid NOT NULL REFERENCES nodes(id),
    kind       text NOT NULL,
    weight     real NOT NULL DEFAULT 1.0,
    created_at timestamptz NOT NULL
);

-- Composite index in feed order: makes keyset pagination an index-only walk.
CREATE INDEX idx_events_feed ON events (created_at DESC, id DESC);
CREATE INDEX idx_events_node ON events (node_id, created_at DESC);

-- ---------------------------------------------------------------------------
-- Review queue for fuzzy-match candidates (pattern 04).
-- The unique constraint normalizes the pair the same way uq_edges_bidirectional
-- does, so (A,B) and (B,A) hit the same row.
-- ---------------------------------------------------------------------------
CREATE TABLE match_review_queue (
    id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    node_a     uuid NOT NULL REFERENCES nodes(id),
    node_b     uuid NOT NULL REFERENCES nodes(id),
    score      real NOT NULL,
    status     text NOT NULL DEFAULT 'pending'
                    CHECK (status IN ('pending', 'confirmed', 'rejected')),
    updated_at timestamptz NOT NULL DEFAULT now(),
    CHECK (node_a <> node_b)
);

CREATE UNIQUE INDEX uq_review_pair
    ON match_review_queue (LEAST(node_a, node_b), GREATEST(node_a, node_b));

-- ---------------------------------------------------------------------------
-- Momentum snapshots (pattern 08 serializes writers to this table).
-- ---------------------------------------------------------------------------
CREATE TABLE momentum_snapshots (
    node_id     uuid NOT NULL REFERENCES nodes(id),
    signal      text NOT NULL,
    value       real NOT NULL,
    computed_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (node_id, signal)
);