likwid/backend/migrations/20260126150000_liquid_delegation.sql

-- Liquid Delegation System
-- Implements fluid, reversible, topic-based vote delegation

-- Delegation scopes (what the delegation applies to)
CREATE TYPE delegation_scope AS ENUM ('global', 'community', 'topic', 'proposal');

-- Topic categories for delegation
CREATE TABLE topics (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    community_id UUID NOT NULL REFERENCES communities(id) ON DELETE CASCADE,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL,
    description TEXT,
    parent_id UUID REFERENCES topics(id) ON DELETE SET NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(community_id, slug)
);

-- Vote delegations
CREATE TABLE delegations (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    delegator_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    delegate_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    scope delegation_scope NOT NULL DEFAULT 'global',
    community_id UUID REFERENCES communities(id) ON DELETE CASCADE,
    topic_id UUID REFERENCES topics(id) ON DELETE CASCADE,
    proposal_id UUID REFERENCES proposals(id) ON DELETE CASCADE,
    weight DECIMAL(5,4) NOT NULL DEFAULT 1.0, -- 0.0001 to 1.0 (for fractional delegation)
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    revoked_at TIMESTAMPTZ,
    -- Prevent self-delegation
    CONSTRAINT no_self_delegation CHECK (delegator_id != delegate_id),
    -- Ensure scope matches reference
    CONSTRAINT scope_community_match CHECK (
        (scope = 'global') OR
        (scope = 'community' AND community_id IS NOT NULL) OR
        (scope = 'topic' AND topic_id IS NOT NULL) OR
        (scope = 'proposal' AND proposal_id IS NOT NULL)
    )
);

-- Delegation chain cache (for transitive delegations)
CREATE TABLE delegation_chains (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    original_delegator_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    final_delegate_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    chain_path UUID[] NOT NULL, -- Array of user IDs in the chain
    chain_length INT NOT NULL,
    effective_weight DECIMAL(5,4) NOT NULL,
    scope delegation_scope NOT NULL,
    community_id UUID REFERENCES communities(id) ON DELETE CASCADE,
    topic_id UUID REFERENCES topics(id) ON DELETE CASCADE,
    computed_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Delegation activity log (for transparency)
CREATE TABLE delegation_log (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    delegation_id UUID REFERENCES delegations(id) ON DELETE SET NULL,
    delegator_id UUID NOT NULL REFERENCES users(id),
    delegate_id UUID NOT NULL REFERENCES users(id),
    action VARCHAR(20) NOT NULL, -- 'created', 'updated', 'revoked', 'used'
    scope delegation_scope NOT NULL,
    community_id UUID REFERENCES communities(id),
    topic_id UUID REFERENCES topics(id),
    proposal_id UUID REFERENCES proposals(id),
    metadata JSONB,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Delegated votes cast (track when delegations are used)
CREATE TABLE delegated_votes (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    proposal_id UUID NOT NULL REFERENCES proposals(id) ON DELETE CASCADE,
    original_voter_id UUID NOT NULL REFERENCES voting_identities(id),
    delegate_id UUID NOT NULL REFERENCES users(id),
    delegation_chain UUID[] NOT NULL,
    vote_data JSONB NOT NULL, -- The actual vote (option_ids, scores, etc.)
    cast_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(proposal_id, original_voter_id)
);

-- Delegate profiles (public info about delegates)
CREATE TABLE delegate_profiles (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE UNIQUE,
    display_name VARCHAR(100),
    bio TEXT,
    expertise_topics UUID[], -- Array of topic IDs
    accepting_delegations BOOLEAN NOT NULL DEFAULT TRUE,
    delegation_policy TEXT, -- How they decide votes
    total_delegators INT NOT NULL DEFAULT 0,
    total_votes_cast INT NOT NULL DEFAULT 0,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Add topic reference to proposals
ALTER TABLE proposals ADD COLUMN IF NOT EXISTS topic_id UUID REFERENCES topics(id);

-- Indexes
CREATE INDEX idx_topics_community ON topics(community_id);
CREATE INDEX idx_topics_parent ON topics(parent_id);
CREATE INDEX idx_delegations_delegator ON delegations(delegator_id);
CREATE INDEX idx_delegations_delegate ON delegations(delegate_id);
CREATE INDEX idx_delegations_active ON delegations(is_active) WHERE is_active = TRUE;
CREATE INDEX idx_delegations_scope ON delegations(scope);
CREATE INDEX idx_delegation_chains_original ON delegation_chains(original_delegator_id);
CREATE INDEX idx_delegation_chains_final ON delegation_chains(final_delegate_id);
CREATE INDEX idx_delegation_log_delegator ON delegation_log(delegator_id);
CREATE INDEX idx_delegation_log_delegate ON delegation_log(delegate_id);
CREATE INDEX idx_delegated_votes_proposal ON delegated_votes(proposal_id);
CREATE INDEX idx_delegate_profiles_accepting ON delegate_profiles(accepting_delegations) WHERE accepting_delegations = TRUE;

-- Triggers
CREATE TRIGGER delegations_updated_at BEFORE UPDATE ON delegations
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER delegate_profiles_updated_at BEFORE UPDATE ON delegate_profiles
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- Function to detect delegation cycles
CREATE OR REPLACE FUNCTION check_delegation_cycle()
RETURNS TRIGGER AS $$
DECLARE
    current_delegate UUID;
    visited UUID[];
    max_depth INT := 20;
    depth INT := 0;
BEGIN
    -- Start from the new delegate
    current_delegate := NEW.delegate_id;
    visited := ARRAY[NEW.delegator_id];
    
    WHILE depth < max_depth LOOP
        -- Check if we've reached a cycle
        IF current_delegate = ANY(visited) THEN
            RAISE EXCEPTION 'Delegation would create a cycle';
        END IF;
        
        -- Add to visited
        visited := visited || current_delegate;
        
        -- Find next delegate in chain (matching scope)
        SELECT d.delegate_id INTO current_delegate
        FROM delegations d
        WHERE d.delegator_id = current_delegate
          AND d.is_active = TRUE
          AND d.scope = NEW.scope
          AND (NEW.scope = 'global' OR 
               (NEW.scope = 'community' AND d.community_id = NEW.community_id) OR
               (NEW.scope = 'topic' AND d.topic_id = NEW.topic_id) OR
               (NEW.scope = 'proposal' AND d.proposal_id = NEW.proposal_id))
        LIMIT 1;
        
        -- No more delegations in chain
        IF current_delegate IS NULL THEN
            EXIT;
        END IF;
        
        depth := depth + 1;
    END LOOP;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER check_delegation_cycle_trigger
    BEFORE INSERT OR UPDATE ON delegations
    FOR EACH ROW EXECUTE FUNCTION check_delegation_cycle();