microdao-daarion/migrations/046_memory_service_full_schema.sql

-- Migration 046: Memory Service Full Schema
-- Повна схема пам'яті для агентів (episodic, semantic, group identity)
-- Дата: 2026-01-17
-- Автор: DAARION Team

-- ============================================================================
-- 1. GROUPS — Групові чати
-- ============================================================================

CREATE TABLE IF NOT EXISTS groups (
    group_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    platform VARCHAR(50) NOT NULL,  -- telegram, discord, matrix, slack
    platform_group_id VARCHAR(255) NOT NULL,  -- ID групи на платформі
    name VARCHAR(255),
    description TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    metadata JSONB DEFAULT '{}',
    
    UNIQUE(platform, platform_group_id)
);

CREATE INDEX idx_groups_platform ON groups(platform);
CREATE INDEX idx_groups_platform_id ON groups(platform, platform_group_id);

-- ============================================================================
-- 2. GROUP_MEMBERS — Учасники груп
-- ============================================================================

CREATE TABLE IF NOT EXISTS group_members (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    group_id UUID NOT NULL REFERENCES groups(group_id) ON DELETE CASCADE,
    platform_user_id VARCHAR(255) NOT NULL,  -- Стабільний ID користувача на платформі
    nickname VARCHAR(255),  -- Поточний нікнейм (може змінюватись)
    first_seen_at TIMESTAMPTZ DEFAULT NOW(),
    last_seen_at TIMESTAMPTZ DEFAULT NOW(),
    last_message_at TIMESTAMPTZ,
    message_count INTEGER DEFAULT 0,
    no_memory_in_group BOOLEAN DEFAULT FALSE,  -- Opt-out flag
    status VARCHAR(20) DEFAULT 'active',  -- active, left, banned
    
    UNIQUE(group_id, platform_user_id)
);

CREATE INDEX idx_group_members_group ON group_members(group_id);
CREATE INDEX idx_group_members_user ON group_members(platform_user_id);
CREATE INDEX idx_group_members_no_memory ON group_members(no_memory_in_group) WHERE no_memory_in_group = TRUE;

-- ============================================================================
-- 3. GROUP_MEMBER_PROFILES — Профілі учасників у групах
-- ============================================================================

CREATE TABLE IF NOT EXISTS group_member_profiles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    group_id UUID NOT NULL REFERENCES groups(group_id) ON DELETE CASCADE,
    platform_user_id VARCHAR(255) NOT NULL,
    
    -- Роль/контекст (виключно для цієї групи)
    role_hint VARCHAR(100),  -- investor, engineer, moderator, newcomer, etc.
    language_preference VARCHAR(10) DEFAULT 'uk',
    communication_style VARCHAR(50),  -- formal, casual, technical
    
    -- Інтереси/теми (в контексті групи)
    topics_of_interest TEXT[],  -- ['BioMiner', 'tokenomics', 'governance']
    last_topics JSONB DEFAULT '[]',  -- Останні обговорювані теми
    
    -- Преференції спілкування
    preferences_json JSONB DEFAULT '{}',
    notes_short TEXT,  -- Короткі нотатки агента про учасника
    
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    
    UNIQUE(group_id, platform_user_id)
);

CREATE INDEX idx_group_profiles_group ON group_member_profiles(group_id);
CREATE INDEX idx_group_profiles_user ON group_member_profiles(platform_user_id);
CREATE INDEX idx_group_profiles_role ON group_member_profiles(role_hint);

-- ============================================================================
-- 4. USERS — Глобальні користувачі (для DM та cross-platform)
-- ============================================================================

CREATE TABLE IF NOT EXISTS memory_users (
    user_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    
    -- Платформенні ідентифікатори
    telegram_id VARCHAR(50),
    discord_id VARCHAR(50),
    matrix_id VARCHAR(255),
    email VARCHAR(255),
    
    -- Глобальні налаштування
    display_name VARCHAR(255),
    global_memory_enabled BOOLEAN DEFAULT TRUE,
    pii_allowed BOOLEAN DEFAULT FALSE,  -- Дозвіл на збереження PII
    
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    
    UNIQUE(telegram_id),
    UNIQUE(discord_id),
    UNIQUE(matrix_id)
);

CREATE INDEX idx_users_telegram ON memory_users(telegram_id) WHERE telegram_id IS NOT NULL;
CREATE INDEX idx_users_discord ON memory_users(discord_id) WHERE discord_id IS NOT NULL;

-- ============================================================================
-- 5. CONSENT — Згода на обробку пам'яті
-- ============================================================================

CREATE TABLE IF NOT EXISTS memory_consent (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES memory_users(user_id) ON DELETE CASCADE,
    platform_user_id VARCHAR(255),  -- Якщо user_id ще не створено
    
    -- Типи згоди
    memory_enabled BOOLEAN DEFAULT TRUE,
    pii_allowed BOOLEAN DEFAULT FALSE,
    cross_group_memory BOOLEAN DEFAULT FALSE,  -- Дозвіл на перенос між групами
    dm_memory_enabled BOOLEAN DEFAULT TRUE,
    
    -- Retention policy
    retention_policy VARCHAR(50) DEFAULT 'default',  -- default, minimal, extended, forever
    retention_days INTEGER DEFAULT 365,
    
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    updated_by VARCHAR(100) DEFAULT 'user',
    
    UNIQUE(user_id),
    UNIQUE(platform_user_id)
);

-- ============================================================================
-- 6. CONVERSATIONS — Розмови/сесії
-- ============================================================================

CREATE TABLE IF NOT EXISTS conversations (
    conversation_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    
    -- Контекст
    user_id UUID REFERENCES memory_users(user_id),
    platform_user_id VARCHAR(255),  -- Fallback якщо немає user_id
    group_id UUID REFERENCES groups(group_id),
    
    -- Метадані
    channel VARCHAR(50) NOT NULL,  -- dm, group, channel
    platform VARCHAR(50) NOT NULL,
    agent_id VARCHAR(100) DEFAULT 'helion',
    
    -- Часові межі
    started_at TIMESTAMPTZ DEFAULT NOW(),
    ended_at TIMESTAMPTZ,
    last_message_at TIMESTAMPTZ DEFAULT NOW(),
    
    -- Статистика
    message_count INTEGER DEFAULT 0,
    token_count INTEGER DEFAULT 0,
    
    -- Стан
    status VARCHAR(20) DEFAULT 'active',  -- active, ended, archived
    summary TEXT,  -- Автоматичне резюме після завершення
    
    metadata JSONB DEFAULT '{}'
);

CREATE INDEX idx_conversations_user ON conversations(user_id);
CREATE INDEX idx_conversations_platform_user ON conversations(platform_user_id);
CREATE INDEX idx_conversations_group ON conversations(group_id);
CREATE INDEX idx_conversations_status ON conversations(status);
CREATE INDEX idx_conversations_started ON conversations(started_at DESC);

-- ============================================================================
-- 7. MESSAGES — Повідомлення
-- ============================================================================

CREATE TABLE IF NOT EXISTS messages (
    message_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    conversation_id UUID NOT NULL REFERENCES conversations(conversation_id) ON DELETE CASCADE,
    
    -- Автор
    role VARCHAR(20) NOT NULL,  -- user, assistant, system, tool
    platform_user_id VARCHAR(255),  -- Для групових чатів
    
    -- Контент
    content TEXT NOT NULL,
    content_type VARCHAR(50) DEFAULT 'text',  -- text, image, audio, file
    
    -- Часова мітка
    created_at TIMESTAMPTZ DEFAULT NOW(),
    
    -- PII/Redaction
    has_pii BOOLEAN DEFAULT FALSE,
    redaction_state VARCHAR(20) DEFAULT 'none',  -- none, partial, full
    original_content_hash VARCHAR(64),  -- Для аудиту
    
    -- Токени
    token_count INTEGER,
    model_used VARCHAR(100),
    
    metadata JSONB DEFAULT '{}'
);

CREATE INDEX idx_messages_conversation ON messages(conversation_id);
CREATE INDEX idx_messages_created ON messages(created_at DESC);
CREATE INDEX idx_messages_role ON messages(role);
CREATE INDEX idx_messages_pii ON messages(has_pii) WHERE has_pii = TRUE;

-- ============================================================================
-- 8. MEMORIES — Довготривала пам'ять (episodic + semantic)
-- ============================================================================

CREATE TABLE IF NOT EXISTS memories (
    memory_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    
    -- Власник пам'яті
    user_id UUID REFERENCES memory_users(user_id),
    platform_user_id VARCHAR(255),
    group_id UUID REFERENCES groups(group_id),  -- NULL = глобальна/DM пам'ять
    
    -- Тип пам'яті
    memory_type VARCHAR(50) NOT NULL,  -- episodic, semantic, procedural
    category VARCHAR(100),  -- preference, fact, interaction, topic_interest
    
    -- Контент
    content TEXT NOT NULL,
    summary TEXT,  -- Короткий опис для швидкого retrieval
    
    -- Важливість та TTL
    importance FLOAT DEFAULT 0.5,  -- 0.0 - 1.0
    confidence FLOAT DEFAULT 0.8,
    ttl_days INTEGER,  -- NULL = безстроково
    expires_at TIMESTAMPTZ,
    
    -- Джерело
    source_message_ids UUID[],
    source_conversation_id UUID REFERENCES conversations(conversation_id),
    extraction_method VARCHAR(50) DEFAULT 'explicit',  -- explicit, inferred, llm_extracted
    
    -- Embedding (для vector search)
    embedding_id VARCHAR(255),  -- ID в Qdrant/pgvector
    embedding_model VARCHAR(100),
    
    -- Часові мітки
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    last_accessed_at TIMESTAMPTZ,
    access_count INTEGER DEFAULT 0,
    
    -- Статус
    is_active BOOLEAN DEFAULT TRUE,
    is_verified BOOLEAN DEFAULT FALSE,
    
    metadata JSONB DEFAULT '{}'
);

CREATE INDEX idx_memories_user ON memories(user_id);
CREATE INDEX idx_memories_platform_user ON memories(platform_user_id);
CREATE INDEX idx_memories_group ON memories(group_id);
CREATE INDEX idx_memories_type ON memories(memory_type);
CREATE INDEX idx_memories_category ON memories(category);
CREATE INDEX idx_memories_importance ON memories(importance DESC);
CREATE INDEX idx_memories_active ON memories(is_active) WHERE is_active = TRUE;
CREATE INDEX idx_memories_expires ON memories(expires_at) WHERE expires_at IS NOT NULL;

-- ============================================================================
-- 9. MEMORY_EVENTS — Аудит (хто/що/коли змінював пам'ять)
-- ============================================================================

CREATE TABLE IF NOT EXISTS memory_events (
    event_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    
    -- Що змінилось
    memory_id UUID REFERENCES memories(memory_id) ON DELETE SET NULL,
    user_id UUID REFERENCES memory_users(user_id),
    group_id UUID REFERENCES groups(group_id),
    
    -- Дія
    action VARCHAR(50) NOT NULL,  -- created, updated, deleted, accessed, opt_out, opt_in
    
    -- Хто зробив
    actor VARCHAR(100) NOT NULL,  -- user, agent:helion, system, admin
    actor_user_id VARCHAR(255),
    
    -- Деталі
    old_value JSONB,
    new_value JSONB,
    reason TEXT,
    
    -- Час
    created_at TIMESTAMPTZ DEFAULT NOW(),
    
    -- Контекст
    ip_address INET,
    user_agent TEXT,
    metadata JSONB DEFAULT '{}'
);

CREATE INDEX idx_memory_events_memory ON memory_events(memory_id);
CREATE INDEX idx_memory_events_user ON memory_events(user_id);
CREATE INDEX idx_memory_events_action ON memory_events(action);
CREATE INDEX idx_memory_events_created ON memory_events(created_at DESC);

-- ============================================================================
-- 10. GROUP_INTERACTIONS — Взаємодії в групах (опціонально)
-- ============================================================================

CREATE TABLE IF NOT EXISTS group_interactions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    group_id UUID NOT NULL REFERENCES groups(group_id) ON DELETE CASCADE,
    platform_user_id VARCHAR(255) NOT NULL,
    
    -- Тема/контекст взаємодії
    topic VARCHAR(255),
    interaction_type VARCHAR(50),  -- question, answer, discussion, feedback
    
    -- Часові мітки
    first_interaction_at TIMESTAMPTZ DEFAULT NOW(),
    last_interaction_at TIMESTAMPTZ DEFAULT NOW(),
    interaction_count INTEGER DEFAULT 1,
    
    -- Якість взаємодії
    sentiment_score FLOAT,  -- -1.0 до 1.0
    helpfulness_score FLOAT,  -- 0.0 до 1.0
    
    metadata JSONB DEFAULT '{}'
);

CREATE INDEX idx_group_interactions_group ON group_interactions(group_id);
CREATE INDEX idx_group_interactions_user ON group_interactions(platform_user_id);
CREATE INDEX idx_group_interactions_topic ON group_interactions(topic);

-- ============================================================================
-- 11. HELPER FUNCTIONS
-- ============================================================================

-- Функція для автоматичного оновлення updated_at
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

-- Тригери для updated_at
CREATE TRIGGER update_groups_updated_at BEFORE UPDATE ON groups
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_group_member_profiles_updated_at BEFORE UPDATE ON group_member_profiles
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_memory_users_updated_at BEFORE UPDATE ON memory_users
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_memories_updated_at BEFORE UPDATE ON memories
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Функція для opt-out користувача з групи
CREATE OR REPLACE FUNCTION memory_opt_out_group(
    p_group_id UUID,
    p_platform_user_id VARCHAR(255)
) RETURNS VOID AS $$
BEGIN
    -- Позначити учасника як no_memory
    UPDATE group_members 
    SET no_memory_in_group = TRUE 
    WHERE group_id = p_group_id AND platform_user_id = p_platform_user_id;
    
    -- Деактивувати всі пам'яті цього користувача в групі
    UPDATE memories 
    SET is_active = FALSE 
    WHERE group_id = p_group_id AND platform_user_id = p_platform_user_id;
    
    -- Видалити профіль
    DELETE FROM group_member_profiles 
    WHERE group_id = p_group_id AND platform_user_id = p_platform_user_id;
    
    -- Записати в аудит
    INSERT INTO memory_events (user_id, group_id, action, actor, actor_user_id, reason)
    VALUES (NULL, p_group_id, 'opt_out', 'user', p_platform_user_id, 'User requested opt-out from group memory');
END;
$$ LANGUAGE plpgsql;

-- Функція для повного видалення користувача з групи (forget)
CREATE OR REPLACE FUNCTION memory_forget_in_group(
    p_group_id UUID,
    p_platform_user_id VARCHAR(255)
) RETURNS VOID AS $$
BEGIN
    -- Видалити всі пам'яті
    DELETE FROM memories 
    WHERE group_id = p_group_id AND platform_user_id = p_platform_user_id;
    
    -- Видалити профіль
    DELETE FROM group_member_profiles 
    WHERE group_id = p_group_id AND platform_user_id = p_platform_user_id;
    
    -- Очистити дані учасника (але залишити запис)
    UPDATE group_members 
    SET no_memory_in_group = TRUE, nickname = NULL
    WHERE group_id = p_group_id AND platform_user_id = p_platform_user_id;
    
    -- Записати в аудит
    INSERT INTO memory_events (group_id, action, actor, actor_user_id, reason)
    VALUES (p_group_id, 'deleted', 'user', p_platform_user_id, 'User requested full memory deletion in group');
END;
$$ LANGUAGE plpgsql;

-- ============================================================================
-- 12. VIEWS для зручності
-- ============================================================================

-- View: Активні пам'яті користувача
CREATE OR REPLACE VIEW v_active_user_memories AS
SELECT 
    m.*,
    u.display_name,
    g.name as group_name
FROM memories m
LEFT JOIN memory_users u ON m.user_id = u.user_id
LEFT JOIN groups g ON m.group_id = g.group_id
WHERE m.is_active = TRUE
  AND (m.expires_at IS NULL OR m.expires_at > NOW());

-- View: Учасники групи з профілями
CREATE OR REPLACE VIEW v_group_members_with_profiles AS
SELECT 
    gm.*,
    gmp.role_hint,
    gmp.language_preference,
    gmp.topics_of_interest,
    gmp.notes_short,
    g.name as group_name,
    g.platform
FROM group_members gm
LEFT JOIN group_member_profiles gmp 
    ON gm.group_id = gmp.group_id AND gm.platform_user_id = gmp.platform_user_id
JOIN groups g ON gm.group_id = g.group_id
WHERE gm.status = 'active';

-- ============================================================================
-- Result
-- ============================================================================

SELECT 'Migration 046 completed: Memory Service full schema created' AS result;