microdao-daarion/migrations/052_account_linking_schema.sql

-- Migration 052: Account Linking Schema for Energy Union Platform
-- Version: 2.7
-- Date: 2026-01-18
-- Purpose: Enable Telegram ↔ Energy Union account linking for cross-channel memory

-- ============================================================================
-- 1. ACCOUNT LINKS - Core binding between Telegram and Platform accounts
-- ============================================================================

CREATE TABLE IF NOT EXISTS account_links (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    
    -- Energy Union platform account
    account_id UUID NOT NULL,
    
    -- Telegram identifiers
    telegram_user_id BIGINT NOT NULL UNIQUE,
    telegram_username VARCHAR(255),
    telegram_first_name VARCHAR(255),
    telegram_last_name VARCHAR(255),
    
    -- Linking metadata
    linked_at TIMESTAMPTZ DEFAULT NOW(),
    linked_via VARCHAR(50) DEFAULT 'bot_command', -- bot_command, web_dashboard, api
    link_code_used VARCHAR(64),
    
    -- Status
    status VARCHAR(20) DEFAULT 'active', -- active, suspended, revoked
    revoked_at TIMESTAMPTZ,
    revoked_reason TEXT,
    
    -- Audit
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    
    CONSTRAINT unique_account_telegram UNIQUE (account_id, telegram_user_id)
);

CREATE INDEX idx_account_links_account_id ON account_links(account_id);
CREATE INDEX idx_account_links_telegram_user_id ON account_links(telegram_user_id);
CREATE INDEX idx_account_links_status ON account_links(status);

-- ============================================================================
-- 2. LINK CODES - One-time codes for account linking
-- ============================================================================

CREATE TABLE IF NOT EXISTS link_codes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    
    -- The code itself
    code VARCHAR(64) NOT NULL UNIQUE,
    
    -- Who generated it
    account_id UUID NOT NULL,
    generated_via VARCHAR(50) DEFAULT 'web_dashboard', -- web_dashboard, api, admin
    
    -- Expiration
    expires_at TIMESTAMPTZ NOT NULL,
    
    -- Usage tracking
    used BOOLEAN DEFAULT FALSE,
    used_at TIMESTAMPTZ,
    used_by_telegram_id BIGINT,
    
    -- Audit
    created_at TIMESTAMPTZ DEFAULT NOW(),
    
    CONSTRAINT link_code_valid CHECK (expires_at > created_at)
);

CREATE INDEX idx_link_codes_code ON link_codes(code);
CREATE INDEX idx_link_codes_account_id ON link_codes(account_id);
CREATE INDEX idx_link_codes_expires ON link_codes(expires_at) WHERE NOT used;

-- ============================================================================
-- 3. USER TIMELINE - Cross-channel interaction history
-- ============================================================================

CREATE TABLE IF NOT EXISTS user_timeline (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    
    -- Account reference (linked user)
    account_id UUID NOT NULL,
    
    -- Source channel
    channel VARCHAR(50) NOT NULL, -- telegram_dm, telegram_group, web_chat, api
    channel_id VARCHAR(255), -- specific chat_id or session_id
    
    -- Event type
    event_type VARCHAR(50) NOT NULL, -- message, command, action, milestone
    
    -- Content
    summary TEXT NOT NULL, -- Short summary, not raw content
    content_hash VARCHAR(64), -- For deduplication
    
    -- Metadata
    metadata JSONB DEFAULT '{}',
    
    -- Importance scoring (for retrieval)
    importance_score FLOAT DEFAULT 0.5,
    
    -- Timestamps
    event_at TIMESTAMPTZ NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    
    -- Retention
    expires_at TIMESTAMPTZ, -- NULL = permanent
    
    CONSTRAINT valid_importance CHECK (importance_score >= 0 AND importance_score <= 1)
);

CREATE INDEX idx_user_timeline_account_id ON user_timeline(account_id);
CREATE INDEX idx_user_timeline_event_at ON user_timeline(event_at DESC);
CREATE INDEX idx_user_timeline_channel ON user_timeline(channel);
CREATE INDEX idx_user_timeline_importance ON user_timeline(importance_score DESC);

-- ============================================================================
-- 4. ORG CHAT MESSAGES - Official chat logging
-- ============================================================================

CREATE TABLE IF NOT EXISTS org_chat_messages (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    
    -- Chat identification
    chat_id BIGINT NOT NULL,
    chat_type VARCHAR(50) NOT NULL, -- official_ops, mentor_room, public_community
    chat_title VARCHAR(255),
    
    -- Message
    message_id BIGINT NOT NULL,
    sender_telegram_id BIGINT,
    sender_account_id UUID, -- Linked account if exists
    sender_username VARCHAR(255),
    sender_display_name VARCHAR(255),
    
    -- Content
    text TEXT,
    has_media BOOLEAN DEFAULT FALSE,
    media_type VARCHAR(50), -- photo, video, document, voice
    attachments_ref JSONB DEFAULT '[]',
    
    -- Reply tracking
    reply_to_message_id BIGINT,
    
    -- Processing status
    processed_for_decisions BOOLEAN DEFAULT FALSE,
    processed_at TIMESTAMPTZ,
    
    -- Timestamps
    message_at TIMESTAMPTZ NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    
    CONSTRAINT unique_chat_message UNIQUE (chat_id, message_id)
);

CREATE INDEX idx_org_chat_messages_chat ON org_chat_messages(chat_id);
CREATE INDEX idx_org_chat_messages_sender ON org_chat_messages(sender_telegram_id);
CREATE INDEX idx_org_chat_messages_at ON org_chat_messages(message_at DESC);
CREATE INDEX idx_org_chat_messages_unprocessed ON org_chat_messages(chat_id) 
    WHERE NOT processed_for_decisions;

-- ============================================================================
-- 5. DECISION RECORDS - Extracted decisions from chats
-- ============================================================================

CREATE TABLE IF NOT EXISTS decision_records (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    
    -- Source
    chat_id BIGINT NOT NULL,
    source_message_id BIGINT NOT NULL,
    
    -- Decision content
    decision TEXT NOT NULL,
    action TEXT,
    owner VARCHAR(255), -- @username or role
    due_date DATE,
    canon_change BOOLEAN DEFAULT FALSE,
    
    -- Status tracking
    status VARCHAR(50) DEFAULT 'pending', -- pending, in_progress, completed, cancelled
    status_updated_at TIMESTAMPTZ,
    status_updated_by VARCHAR(255),
    
    -- Extraction metadata
    extracted_at TIMESTAMPTZ DEFAULT NOW(),
    extraction_method VARCHAR(50) DEFAULT 'regex', -- regex, llm, manual
    confidence_score FLOAT DEFAULT 1.0,
    
    -- Verification
    verified BOOLEAN DEFAULT FALSE,
    verified_at TIMESTAMPTZ,
    verified_by VARCHAR(255),
    
    -- Timestamps
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    
    CONSTRAINT fk_source_message FOREIGN KEY (chat_id, source_message_id) 
        REFERENCES org_chat_messages(chat_id, message_id) ON DELETE CASCADE
);

CREATE INDEX idx_decision_records_chat ON decision_records(chat_id);
CREATE INDEX idx_decision_records_status ON decision_records(status);
CREATE INDEX idx_decision_records_due ON decision_records(due_date) WHERE status NOT IN ('completed', 'cancelled');
CREATE INDEX idx_decision_records_canon ON decision_records(canon_change) WHERE canon_change = TRUE;

-- ============================================================================
-- 6. KYC ATTESTATIONS - Status without PII
-- ============================================================================

CREATE TABLE IF NOT EXISTS kyc_attestations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    
    -- Account reference
    account_id UUID NOT NULL UNIQUE,
    
    -- Attestation fields (NO RAW PII)
    kyc_status VARCHAR(20) NOT NULL DEFAULT 'unverified', -- unverified, pending, passed, failed
    kyc_provider VARCHAR(100),
    jurisdiction VARCHAR(10), -- ISO country code
    risk_tier VARCHAR(20) DEFAULT 'unknown', -- low, medium, high, unknown
    pep_sanctions_flag BOOLEAN DEFAULT FALSE,
    wallet_verified BOOLEAN DEFAULT FALSE,
    
    -- Timestamps
    attested_at TIMESTAMPTZ,
    expires_at TIMESTAMPTZ, -- Some KYC needs periodic renewal
    
    -- Audit
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    
    CONSTRAINT valid_kyc_status CHECK (kyc_status IN ('unverified', 'pending', 'passed', 'failed')),
    CONSTRAINT valid_risk_tier CHECK (risk_tier IN ('low', 'medium', 'high', 'unknown'))
);

CREATE INDEX idx_kyc_attestations_account ON kyc_attestations(account_id);
CREATE INDEX idx_kyc_attestations_status ON kyc_attestations(kyc_status);

-- ============================================================================
-- 7. HELPER FUNCTIONS
-- ============================================================================

-- Function to resolve Telegram user to account
CREATE OR REPLACE FUNCTION resolve_telegram_account(p_telegram_user_id BIGINT)
RETURNS UUID AS $$
DECLARE
    v_account_id UUID;
BEGIN
    SELECT account_id INTO v_account_id
    FROM account_links
    WHERE telegram_user_id = p_telegram_user_id
      AND status = 'active';
    
    RETURN v_account_id;
END;
$$ LANGUAGE plpgsql;

-- Function to check if link code is valid
CREATE OR REPLACE FUNCTION is_link_code_valid(p_code VARCHAR)
RETURNS TABLE(valid BOOLEAN, account_id UUID, error_message TEXT) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        CASE 
            WHEN lc.id IS NULL THEN FALSE
            WHEN lc.used THEN FALSE
            WHEN lc.expires_at < NOW() THEN FALSE
            ELSE TRUE
        END as valid,
        lc.account_id,
        CASE 
            WHEN lc.id IS NULL THEN 'Code not found'
            WHEN lc.used THEN 'Code already used'
            WHEN lc.expires_at < NOW() THEN 'Code expired'
            ELSE NULL
        END as error_message
    FROM link_codes lc
    WHERE lc.code = p_code;
    
    -- If no rows returned, code doesn't exist
    IF NOT FOUND THEN
        RETURN QUERY SELECT FALSE, NULL::UUID, 'Code not found'::TEXT;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- Function to complete linking
CREATE OR REPLACE FUNCTION complete_account_link(
    p_code VARCHAR,
    p_telegram_user_id BIGINT,
    p_telegram_username VARCHAR DEFAULT NULL,
    p_telegram_first_name VARCHAR DEFAULT NULL,
    p_telegram_last_name VARCHAR DEFAULT NULL
)
RETURNS TABLE(success BOOLEAN, account_id UUID, error_message TEXT) AS $$
DECLARE
    v_account_id UUID;
    v_link_id UUID;
BEGIN
    -- Check code validity
    SELECT lc.account_id INTO v_account_id
    FROM link_codes lc
    WHERE lc.code = p_code
      AND NOT lc.used
      AND lc.expires_at > NOW();
    
    IF v_account_id IS NULL THEN
        RETURN QUERY SELECT FALSE, NULL::UUID, 'Invalid or expired code'::TEXT;
        RETURN;
    END IF;
    
    -- Check if already linked
    IF EXISTS (SELECT 1 FROM account_links WHERE telegram_user_id = p_telegram_user_id AND status = 'active') THEN
        RETURN QUERY SELECT FALSE, NULL::UUID, 'Telegram account already linked'::TEXT;
        RETURN;
    END IF;
    
    -- Create link
    INSERT INTO account_links (
        account_id, telegram_user_id, telegram_username, 
        telegram_first_name, telegram_last_name, link_code_used
    ) VALUES (
        v_account_id, p_telegram_user_id, p_telegram_username,
        p_telegram_first_name, p_telegram_last_name, p_code
    )
    RETURNING id INTO v_link_id;
    
    -- Mark code as used
    UPDATE link_codes SET 
        used = TRUE, 
        used_at = NOW(), 
        used_by_telegram_id = p_telegram_user_id
    WHERE code = p_code;
    
    RETURN QUERY SELECT TRUE, v_account_id, NULL::TEXT;
END;
$$ LANGUAGE plpgsql;

-- Function to add timeline event
CREATE OR REPLACE FUNCTION add_timeline_event(
    p_account_id UUID,
    p_channel VARCHAR,
    p_channel_id VARCHAR,
    p_event_type VARCHAR,
    p_summary TEXT,
    p_metadata JSONB DEFAULT '{}',
    p_importance FLOAT DEFAULT 0.5
)
RETURNS UUID AS $$
DECLARE
    v_event_id UUID;
BEGIN
    INSERT INTO user_timeline (
        account_id, channel, channel_id, event_type, 
        summary, metadata, importance_score, event_at
    ) VALUES (
        p_account_id, p_channel, p_channel_id, p_event_type,
        p_summary, p_metadata, p_importance, NOW()
    )
    RETURNING id INTO v_event_id;
    
    RETURN v_event_id;
END;
$$ LANGUAGE plpgsql;

-- ============================================================================
-- 8. TRIGGERS
-- ============================================================================

-- Update timestamp trigger
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_account_links_timestamp
    BEFORE UPDATE ON account_links
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER update_decision_records_timestamp
    BEFORE UPDATE ON decision_records
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER update_kyc_attestations_timestamp
    BEFORE UPDATE ON kyc_attestations
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- ============================================================================
-- MIGRATION COMPLETE
-- ============================================================================

-- Insert migration record
INSERT INTO helion_session_state (session_id, state_type, state_data)
VALUES (
    'migration_052',
    'migration',
    jsonb_build_object(
        'version', '052',
        'name', 'account_linking_schema',
        'applied_at', NOW(),
        'tables_created', ARRAY[
            'account_links',
            'link_codes', 
            'user_timeline',
            'org_chat_messages',
            'decision_records',
            'kyc_attestations'
        ]
    )
) ON CONFLICT (session_id) DO UPDATE SET
    state_data = EXCLUDED.state_data,
    updated_at = NOW();