Graph + Vector Intelligence (Mega-Cube 17) -- Stage 1, Path A: The Edge Table

CREATE TABLE memory_edges (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    source_id TEXT NOT NULL REFERENCES memory_turns(id),
    target_id TEXT NOT NULL REFERENCES memory_turns(id),
    edge_type TEXT NOT NULL,        -- 'temporal', 'referential', 'causal', 'categorical'
    weight FLOAT DEFAULT 1.0,       -- strength of connection (KARL reward, similarity, etc.)
    metadata JSONB DEFAULT '{}',    -- edge-specific data (file path, inscription type, etc.)
    created_at TIMESTAMPTZ DEFAULT now(),

    CONSTRAINT no_self_loops CHECK (source_id != target_id)
);

CREATE INDEX idx_edges_source ON memory_edges(source_id);
CREATE INDEX idx_edges_target ON memory_edges(target_id);
CREATE INDEX idx_edges_type ON memory_edges(edge_type);
CREATE INDEX idx_edges_source_type ON memory_edges(source_id, edge_type);

# Extract session boundaries from memory_turns
# Group by session_id, order by created_at
# Connect consecutive turns within each session
def build_temporal_edges(conn):
    query = """
    WITH ordered_turns AS (
        SELECT id, session_id, created_at,
               LAG(id) OVER (PARTITION BY session_id ORDER BY created_at) as prev_id,
               LAG(created_at) OVER (PARTITION BY session_id ORDER BY created_at) as prev_ts
        FROM memory_turns
        WHERE session_id IS NOT NULL
    )
    INSERT INTO memory_edges (source_id, target_id, edge_type, weight, metadata)
    SELECT prev_id, id, 'temporal',
           1.0 / (1.0 + EXTRACT(EPOCH FROM (created_at - prev_ts)) / 3600.0),
           jsonb_build_object('session_id', session_id, 'gap_seconds',
                              EXTRACT(EPOCH FROM (created_at - prev_ts)))
    FROM ordered_turns
    WHERE prev_id IS NOT NULL
    """
    conn.execute(query)

def build_referential_edges(conn):
    # Extract file paths from metadata JSONB
    # For each file, find all turns that mention it
    # Create edges between turns that share a file
    # Weight = cosine similarity of their embeddings
    query = """
    WITH file_mentions AS (
        SELECT id, embedding,
               jsonb_array_elements_text(metadata->'files') as file_path
        FROM memory_turns
        WHERE metadata ? 'files'
    )
    SELECT a.id as source, b.id as target,
           1.0 - (a.embedding <=> b.embedding) as similarity,
           a.file_path
    FROM file_mentions a
    JOIN file_mentions b ON a.file_path = b.file_path
         AND a.id < b.id  -- avoid duplicates
    WHERE 1.0 - (a.embedding <=> b.embedding) > 0.3  -- minimum similarity threshold
    """

def build_causal_edges(karl_path, conn):
    for traj in load_jsonl(karl_path):
        if traj.get('correction_of'):
            insert_edge(
                source=traj['correction_of'],
                target=traj['session_id'],
                edge_type='causal',
                weight=traj.get('reward', 0.5),
                metadata={'correction_type': 'redo', 'skill': traj.get('skill')}
            )

def graph_reranked_search(query_text, k=10, graph_boost=0.3):
    # Step 1: Standard vector search (top 3*k candidates)
    candidates = vector_search(query_text, limit=k*3)

    # Step 2: For each candidate, compute graph score
    for c in candidates:
        # How many edges connect this candidate to other candidates?
        graph_score = count_edges_to_set(c.id, [x.id for x in candidates])
        # Boost candidates that are well-connected in the result set
        c.final_score = (1 - graph_boost) * c.similarity + graph_boost * graph_score

    # Step 3: Return top-k by final score
    return sorted(candidates, key=lambda x: x.final_score, reverse=True)[:k]

WITH RECURSIVE reachable AS (
    -- Seed: start from a specific turn
    SELECT target_id as id, 1 as depth, weight
    FROM memory_edges
    WHERE source_id = $start_turn AND edge_type = ANY($edge_types)

    UNION ALL

    -- Recurse: follow edges up to N hops
    SELECT e.target_id, r.depth + 1, r.weight * e.weight
    FROM reachable r
    JOIN memory_edges e ON e.source_id = r.id
    WHERE r.depth < $max_hops
)
SELECT DISTINCT mt.id, mt.content, r.weight as graph_weight,
       1.0 - (mt.embedding <=> $query_embedding) as vector_sim
FROM reachable r
JOIN memory_turns mt ON mt.id = r.id
ORDER BY (r.weight * 0.4 + (1.0 - (mt.embedding <=> $query_embedding)) * 0.6) DESC
LIMIT $k;

WITH RECURSIVE session_chain AS (
    SELECT id, content, created_at, 0 as position
    FROM memory_turns WHERE id = $seed_turn

    UNION ALL

    -- Follow temporal edges backward
    SELECT mt.id, mt.content, mt.created_at, sc.position - 1
    FROM session_chain sc
    JOIN memory_edges e ON e.target_id = sc.id AND e.edge_type = 'temporal'
    JOIN memory_turns mt ON mt.id = e.source_id
    WHERE sc.position > -20  -- max 20 turns back
)
SELECT * FROM session_chain ORDER BY position;

def inscription_search(query_text, inscription_category, k=10):
    # Use categorical edges to find turns in the target inscription cluster
    cluster_ids = get_cluster_members(inscription_category, limit=1000)
    # Vector search within that cluster only
    return vector_search_within(query_text, cluster_ids, limit=k)

# Existing: mac3_pgvector_search() -> race all nodes
# New: add graph_rerank parameter

@mcp.tool()
async def rag_search(query: str, graph_rerank: bool = True,
                     max_hops: int = 0, inscription: str = None, ...):
    # Phase 1: Vector search (existing)
    results = await mac3_pgvector_search(query, limit=k*3 if graph_rerank else k)

    # Phase 2: Graph rerank (new)
    if graph_rerank and results:
        results = await graph_rerank_results(results, query_embedding)

    # Phase 3: Multi-hop expansion (new, optional)
    if max_hops > 0 and results:
        results = await expand_via_graph(results[0].id, max_hops, query_embedding)

    return results

@app.get("/api/graph/neighbors/{turn_id}")
async def get_neighbors(turn_id: str, edge_type: str = None, max_hops: int = 1):
    ...

@app.get("/api/graph/path/{source_id}/{target_id}")
async def find_path(source_id: str, target_id: str, max_hops: int = 5):
    ...

@app.post("/api/graph/search")
async def graph_augmented_search(query: str, graph_boost: float = 0.3, ...):
    ...

def flush_session_with_edges(session_data, conn):
    # Existing: write to trajectories.jsonl
    flush_session(session_data)

    # New: create trajectory edge
    if session_data.get('turns'):
        first_turn = session_data['turns'][0]['id']
        last_turn = session_data['turns'][-1]['id']
        create_edge(first_turn, last_turn, 'trajectory',
                    weight=session_data['reward'],
                    metadata={'skill': session_data.get('skill'),
                              'domain': session_data.get('domain')})