missing_indices.sql

-- Missing indexes identified in code review

-- 1. Feed queries need this index
CREATE INDEX IF NOT EXISTS idx_posts_feed_optimized 
ON posts(queue_level, visibility_score DESC, created_at DESC) 
WHERE deleted_at IS NULL;

-- 2. Token transaction target lookup
CREATE INDEX IF NOT EXISTS idx_transactions_target_lookup 
ON token_transactions(target_type, target_id, spender_account_id)
WHERE unliked_at IS NULL;

-- 3. Comment cost checks need this
CREATE INDEX IF NOT EXISTS idx_accounts_comment_reset 
ON accounts(account_id, daily_comments_reset_at);

-- 4. Unlike window queries
CREATE INDEX IF NOT EXISTS idx_transactions_unlikeable_window
ON token_transactions(spender_account_id, can_unlike_until, transaction_id)
WHERE unliked_at IS NULL;

-- 5. Page lookup by account (for auto-creation)
CREATE INDEX IF NOT EXISTS idx_pages_account_primary 
ON pages(account_id, is_primary, is_active);

-- 6. Search optimization (consider adding pg_trgm for fuzzy search)
CREATE INDEX IF NOT EXISTS idx_pages_search_username 
ON pages(username text_pattern_ops) 
WHERE is_active = true;

CREATE INDEX IF NOT EXISTS idx_pages_search_display 
ON pages(LOWER(display_name)) 
WHERE is_active = true;

-- 7. Token balance for tax collection
CREATE INDEX IF NOT EXISTS idx_accounts_tax_eligible 
ON accounts(last_tax_collected_at, token_balance) 
WHERE token_balance >= 200;

-- 8. Stipend distribution eligibility
CREATE INDEX IF NOT EXISTS idx_accounts_stipend_eligible 
ON accounts(last_stipend_received_at, last_active_at);

-- 9. Post author lookup (for feed)
CREATE INDEX IF NOT EXISTS idx_posts_page_created 
ON posts(page_id, created_at DESC) 
WHERE deleted_at IS NULL;

-- 10. Comment thread queries
CREATE INDEX IF NOT EXISTS idx_comments_thread 
ON comments(parent_comment_id, created_at ASC) 
WHERE deleted_at IS NULL;

-- Analyze tables after adding indexes
ANALYZE accounts;
ANALYZE pages;
ANALYZE posts;
ANALYZE comments;
ANALYZE token_transactions;

-- Verify indexes were created
SELECT 
    schemaname,
    tablename,
    indexname,
    indexdef
FROM pg_indexes
WHERE schemaname = 'public'
    AND indexname LIKE 'idx_%'
ORDER BY tablename, indexname;