pgvector Migration Plan¶

-- Enable pgvector extension
CREATE EXTENSION IF NOT EXISTS vector;

-- Core memories with vector embedding
CREATE TABLE memories (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    text TEXT NOT NULL,
    embedding vector(1024),  -- BGE-Large dimension
    source_type VARCHAR(50) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    -- Trust Engine fields
    trust_score FLOAT DEFAULT 0.5 CHECK (trust_score >= 0 AND trust_score <= 1),
    retrieval_count INT DEFAULT 0,
    is_archived BOOLEAN DEFAULT FALSE,
    last_accessed_at TIMESTAMP WITH TIME ZONE,

    -- Multi-peer
    peer_id UUID REFERENCES peers(id) ON DELETE SET NULL,

    -- Indexing
    content_hash VARCHAR(64),  -- SHA-256 for deduplication
    source_path TEXT,

    -- Metadata as JSONB for flexibility
    metadata JSONB DEFAULT '{}'::jsonb
);

-- Index for vector similarity search
CREATE INDEX idx_memories_embedding ON memories USING ivfflat (embedding vector_cosine_ops);

-- Index for trust engine queries
CREATE INDEX idx_memories_trust ON memories(trust_score) WHERE NOT is_archived;
CREATE INDEX idx_memories_last_accessed ON memories(last_accessed_at) WHERE NOT is_archived;

-- Memory relationships (replaces JSON field)
CREATE TABLE memory_relationships (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    source_id UUID NOT NULL REFERENCES memories(id) ON DELETE CASCADE,
    target_id UUID NOT NULL REFERENCES memories(id) ON DELETE CASCADE,
    relationship_type VARCHAR(50) NOT NULL CHECK (
        relationship_type IN ('SUPERSEDES', 'RELATED_TO', 'CONTRADICTS', 'DERIVED_FROM')
    ),
    confidence FLOAT DEFAULT 1.0 CHECK (confidence >= 0 AND confidence <= 1),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    metadata JSONB DEFAULT '{}'::jsonb,

    -- Prevent duplicate relationships
    UNIQUE(source_id, target_id, relationship_type)
);

-- Index for relationship queries
CREATE INDEX idx_mem_rel_source ON memory_relationships(source_id);
CREATE INDEX idx_mem_rel_target ON memory_relationships(target_id);
CREATE INDEX idx_mem_rel_type ON memory_relationships(relationship_type);

-- Entities from knowledge graph extraction
CREATE TABLE entities (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(500) NOT NULL,
    entity_type VARCHAR(50) NOT NULL CHECK (
        entity_type IN ('PERSON', 'ORGANIZATION', 'CONCEPT', 'CODE', 'PROJECT', 'LOCATION', 'UNKNOWN')
    ),
    canonical_name VARCHAR(500),
    confidence FLOAT DEFAULT 1.0 CHECK (confidence >= 0 AND confidence <= 1),

    -- Vector embedding for entity similarity
    embedding vector(1024),

    -- Ownership
    peer_id UUID REFERENCES peers(id) ON DELETE SET NULL,

    -- Occurrences
    mention_count INT DEFAULT 1,
    first_seen_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    last_seen_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    UNIQUE(name, entity_type, peer_id)
);

-- Index for entity similarity
CREATE INDEX idx_entities_embedding ON entities USING ivfflat (embedding vector_cosine_ops);

-- Entity relationships (from knowledge graph)
CREATE TABLE entity_relationships (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    source_entity_id UUID NOT NULL REFERENCES entities(id) ON DELETE CASCADE,
    target_entity_id UUID NOT NULL REFERENCES entities(id) ON DELETE CASCADE,
    relationship_type VARCHAR(50) NOT NULL,
    confidence FLOAT DEFAULT 1.0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    UNIQUE(source_entity_id, target_entity_id, relationship_type)
);

CREATE INDEX idx_ent_rel_source ON entity_relationships(source_entity_id);
CREATE INDEX idx_ent_rel_target ON entity_relationships(target_entity_id);

-- Peers for multi-user support
CREATE TABLE peers (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    role VARCHAR(50) NOT NULL DEFAULT 'COLLABORATOR' CHECK (
        role IN ('OWNER', 'COLLABORATOR', 'READONLY', 'GUEST')
    ),
    trust_level FLOAT DEFAULT 1.0 CHECK (trust_level >= 0 AND trust_level <= 1),
    preferences JSONB DEFAULT '{}'::jsonb,

    -- Status
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    last_active_at TIMESTAMP WITH TIME ZONE
);

-- Memory sharing permissions
CREATE TABLE memory_sharing (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    memory_id UUID NOT NULL REFERENCES memories(id) ON DELETE CASCADE,
    peer_id UUID NOT NULL REFERENCES peers(id) ON DELETE CASCADE,
    permission VARCHAR(50) NOT NULL DEFAULT 'SHARED' CHECK (
        permission IN ('PRIVATE', 'SHARED', 'INHERITED')
    ),
    granted_by UUID REFERENCES peers(id) ON DELETE SET NULL,
    granted_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    UNIQUE(memory_id, peer_id)
);

-- User profiles (from user modeling)
CREATE TABLE user_profiles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    peer_id UUID UNIQUE REFERENCES peers(id) ON DELETE CASCADE,

    -- Profile data
    preferences JSONB DEFAULT '{}'::jsonb,
    communication_style VARCHAR(100) DEFAULT 'neutral',
    expertise_level VARCHAR(50) DEFAULT 'intermediate',

    -- Dialectic notes
    dialectic_notes JSONB DEFAULT '[]'::jsonb,

    -- Status
    warmed_up BOOLEAN DEFAULT FALSE,
    session_count INT DEFAULT 0,

    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Trust score adjustments history
CREATE TABLE trust_adjustments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    memory_id UUID NOT NULL REFERENCES memories(id) ON DELETE CASCADE,
    old_score FLOAT NOT NULL,
    new_score FLOAT NOT NULL,
    signal VARCHAR(50) NOT NULL,  -- AGENT_USED, USER_CORRECTED, etc.
    adjustment_amount FLOAT NOT NULL,
    reason TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_trust_adj_memory ON trust_adjustments(memory_id);
CREATE INDEX idx_trust_adj_created ON trust_adjustments(created_at);

-- Decay memories that haven't been accessed in 7 days
-- Formula: score * 0.5^(days*decay_rate/half_life)
UPDATE memories
SET trust_score = trust_score * POWER(0.5, (
    EXTRACT(EPOCH FROM (NOW() - last_accessed_at)) / 86400.0 * decay_rate / 90
  ))
WHERE
    is_archived = FALSE
    AND trust_score > 0.1
    AND last_accessed_at < NOW() - INTERVAL '7 days'
    AND decay_rate IS NOT NULL;

-- Find contradicting memories where both have low trust
SELECT
    m1.id AS memory_a_id,
    m1.text AS memory_a_text,
    m1.trust_score AS trust_a,
    m2.id AS memory_b_id,
    m2.text AS memory_b_text,
    m2.trust_score AS trust_b
FROM memory_relationships r
JOIN memories m1 ON r.source_id = m1.id
JOIN memories m2 ON r.target_id = m2.id
WHERE
    r.relationship_type = 'CONTRADICTS'
    AND m1.is_archived = FALSE
    AND m2.is_archived = FALSE
    AND m1.trust_score < 0.5
    AND m2.trust_score < 0.5
ORDER BY (m1.trust_score + m2.trust_score) ASC;

-- Archive memories below threshold
UPDATE memories
SET is_archived = TRUE, updated_at = NOW()
WHERE trust_score < 0.2 AND is_archived = FALSE;

-- Find memories shared with a specific peer that relate to a query
SELECT DISTINCT m.*
FROM memories m
JOIN memory_sharing ms ON m.id = ms.memory_id
JOIN memory_relationships mr ON m.id = mr.source_id
WHERE
    ms.peer_id = 'target-peer-uuid'
    AND ms.permission IN ('SHARED', 'INHERITED')
    AND mr.relationship_type = 'RELATED_TO'
    AND m.is_archived = FALSE
ORDER BY m.retrieval_count DESC
LIMIT 20;

-- Find all entities connected to a source entity within N hops
WITH RECURSIVE entity_chain AS (
    -- Base case: direct relationships
    SELECT
        source_entity_id,
        target_entity_id,
        1 AS depth,
        ARRAY[source_entity_id, target_entity_id] AS path
    FROM entity_relationships

    UNION ALL

    -- Recursive case: extend path
    SELECT
        er.source_entity_id,
        er.target_entity_id,
        ec.depth + 1,
        ec.path || er.target_entity_id
    FROM entity_relationships er
    JOIN entity_chain ec ON er.source_entity_id = ec.target_entity_id
    WHERE
        er.target_entity_id != ALL(ec.path)
        AND ec.depth < 3  -- Max inference depth
)
SELECT DISTINCT e.*, ec.depth, ec.path
FROM entity_chain ec
JOIN entities e ON ec.target_entity_id = e.id
WHERE ec.source_entity_id = 'source-entity-uuid'
ORDER BY ec.depth;