AeThex-OS/supabase/migrations/005_linked_accounts.sql

-- Migration 005: User Linked Accounts (OAuth providers)
-- Supports Discord, GitHub, Google, and other OAuth providers

-- ============================================================================
-- USER LINKED ACCOUNTS
-- ============================================================================

CREATE TABLE IF NOT EXISTS user_linked_accounts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  provider VARCHAR(50) NOT NULL CHECK (provider IN ('discord', 'github', 'google', 'twitter', 'twitch', 'roblox')),
  provider_user_id VARCHAR(200) NOT NULL,
  provider_username VARCHAR(200),
  provider_email VARCHAR(300),
  provider_avatar VARCHAR(500),
  access_token TEXT,
  refresh_token TEXT,
  token_expires_at TIMESTAMP WITH TIME ZONE,
  scopes TEXT[],
  metadata JSONB,
  linked_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  UNIQUE(user_id, provider),
  UNIQUE(provider, provider_user_id)
);

CREATE INDEX IF NOT EXISTS idx_linked_accounts_user ON user_linked_accounts(user_id);
CREATE INDEX IF NOT EXISTS idx_linked_accounts_provider ON user_linked_accounts(provider);
CREATE INDEX IF NOT EXISTS idx_linked_accounts_provider_user ON user_linked_accounts(provider, provider_user_id);

-- ============================================================================
-- DISCORD PENDING LINKS (for bot-based verification)
-- ============================================================================

CREATE TABLE IF NOT EXISTS discord_pending_links (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  discord_id VARCHAR(100) NOT NULL,
  discord_username VARCHAR(100),
  verification_code VARCHAR(20) NOT NULL,
  expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  UNIQUE(discord_id)
);

CREATE INDEX IF NOT EXISTS idx_pending_links_code ON discord_pending_links(verification_code);
CREATE INDEX IF NOT EXISTS idx_pending_links_expires ON discord_pending_links(expires_at);

-- ============================================================================
-- USER FOLLOWED ARMS (for activity feed filtering)
-- ============================================================================

CREATE TABLE IF NOT EXISTS user_followed_arms (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  arm_id VARCHAR(50) NOT NULL CHECK (arm_id IN ('labs', 'gameforge', 'corp', 'foundation', 'devlink', 'nexus', 'ethos')),
  followed_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  UNIQUE(user_id, arm_id)
);

CREATE INDEX IF NOT EXISTS idx_followed_arms_user ON user_followed_arms(user_id);
CREATE INDEX IF NOT EXISTS idx_followed_arms_arm ON user_followed_arms(arm_id);

-- ============================================================================
-- ENABLE ROW LEVEL SECURITY
-- ============================================================================

ALTER TABLE user_linked_accounts ENABLE ROW LEVEL SECURITY;
ALTER TABLE discord_pending_links ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_followed_arms ENABLE ROW LEVEL SECURITY;

-- ============================================================================
-- RLS POLICIES
-- ============================================================================

-- Linked Accounts: Users can only see their own
CREATE POLICY "linked_accounts_select" ON user_linked_accounts
  FOR SELECT USING (user_id = auth.uid());

CREATE POLICY "linked_accounts_insert" ON user_linked_accounts
  FOR INSERT WITH CHECK (user_id = auth.uid());

CREATE POLICY "linked_accounts_update" ON user_linked_accounts
  FOR UPDATE USING (user_id = auth.uid());

CREATE POLICY "linked_accounts_delete" ON user_linked_accounts
  FOR DELETE USING (user_id = auth.uid());

-- Pending Links: Service role only (Discord bot)
CREATE POLICY "pending_links_service" ON discord_pending_links
  FOR ALL USING (auth.role() = 'service_role');

-- Followed Arms: Users can manage their own
CREATE POLICY "followed_arms_select" ON user_followed_arms
  FOR SELECT USING (user_id = auth.uid());

CREATE POLICY "followed_arms_insert" ON user_followed_arms
  FOR INSERT WITH CHECK (user_id = auth.uid());

CREATE POLICY "followed_arms_delete" ON user_followed_arms
  FOR DELETE USING (user_id = auth.uid());

-- ============================================================================
-- TRIGGER FOR updated_at
-- ============================================================================

CREATE OR REPLACE FUNCTION update_linked_accounts_timestamp()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trigger_linked_accounts_updated ON user_linked_accounts;
CREATE TRIGGER trigger_linked_accounts_updated
  BEFORE UPDATE ON user_linked_accounts
  FOR EACH ROW EXECUTE FUNCTION update_linked_accounts_timestamp();

-- ============================================================================
-- CLEANUP EXPIRED PENDING LINKS
-- ============================================================================

CREATE OR REPLACE FUNCTION cleanup_expired_pending_links()
RETURNS void AS $$
BEGIN
  DELETE FROM discord_pending_links
  WHERE expires_at < NOW();
END;
$$ LANGUAGE plpgsql;