-- ============================================
-- MP Verification System Migration
-- ============================================
-- Enables Members of Parliament to verify their identity
-- Hybrid approach: @parl.gc.ca email auto-verify + manual admin approval
-- Version: 1.0.0
-- Date: 2025-01-17
-- ============================================
-- 1. UPDATE USER_PROFILES TABLE
-- ============================================
-- Add MP verification fields to existing user_profiles table
ALTER TABLE public.user_profiles ADD COLUMN IF NOT EXISTS is_verified_mp BOOLEAN DEFAULT FALSE;
ALTER TABLE public.user_profiles ADD COLUMN IF NOT EXISTS verified_at TIMESTAMPTZ;
ALTER TABLE public.user_profiles ADD COLUMN IF NOT EXISTS verification_method VARCHAR(20) CHECK (verification_method IN ('email', 'manual', 'admin'));
ALTER TABLE public.user_profiles ADD COLUMN IF NOT EXISTS parl_mp_id INT; -- Links to Neo4j MP node
ALTER TABLE public.user_profiles ADD COLUMN IF NOT EXISTS mp_name TEXT;
ALTER TABLE public.user_profiles ADD COLUMN IF NOT EXISTS mp_riding TEXT;
ALTER TABLE public.user_profiles ADD COLUMN IF NOT EXISTS mp_party TEXT;
-- Index for verified MPs (efficient querying)
CREATE INDEX IF NOT EXISTS idx_user_profiles_verified_mp ON public.user_profiles(is_verified_mp, parl_mp_id)
WHERE is_verified_mp = TRUE;
-- ============================================
-- 2. MP VERIFICATION REQUESTS TABLE
-- ============================================
CREATE TABLE IF NOT EXISTS public.mp_verification_requests (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE NOT NULL,
email TEXT NOT NULL,
parl_mp_id INT,
mp_name TEXT NOT NULL,
mp_riding TEXT NOT NULL,
mp_party TEXT,
-- Supporting documents
proof_type VARCHAR(50), -- 'parliament_email', 'office_phone', 'photo_id', 'other'
proof_notes TEXT,
attachments JSONB, -- URLs to uploaded proof documents
-- Request status
status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'rejected', 'cancelled')),
reviewed_by UUID REFERENCES auth.users(id),
reviewed_at TIMESTAMPTZ,
admin_notes TEXT,
created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL
);
-- Only one pending request per user (partial unique index)
CREATE UNIQUE INDEX IF NOT EXISTS idx_unique_pending_request
ON public.mp_verification_requests(user_id)
WHERE status = 'pending';
-- Indexes
CREATE INDEX IF NOT EXISTS idx_mp_verification_user ON public.mp_verification_requests(user_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_mp_verification_status ON public.mp_verification_requests(status, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_mp_verification_mp ON public.mp_verification_requests(parl_mp_id);
-- RLS policies
ALTER TABLE public.mp_verification_requests ENABLE ROW LEVEL SECURITY;
-- Users can view their own requests
CREATE POLICY mp_verification_own ON public.mp_verification_requests
FOR SELECT USING (user_id = auth.uid());
-- Users can insert their own requests
CREATE POLICY mp_verification_insert ON public.mp_verification_requests
FOR INSERT WITH CHECK (user_id = auth.uid());
-- Users can update their own pending requests (to cancel)
CREATE POLICY mp_verification_update_own ON public.mp_verification_requests
FOR UPDATE USING (user_id = auth.uid() AND status = 'pending')
WITH CHECK (user_id = auth.uid() AND status IN ('pending', 'cancelled'));
-- Admins can view all requests (checked in application logic)
CREATE POLICY mp_verification_admin_select ON public.mp_verification_requests
FOR SELECT USING (
EXISTS (
SELECT 1 FROM public.user_profiles
WHERE id = auth.uid() AND is_admin = TRUE
)
);
-- Admins can update any request (checked in application logic)
CREATE POLICY mp_verification_admin_update ON public.mp_verification_requests
FOR UPDATE USING (
EXISTS (
SELECT 1 FROM public.user_profiles
WHERE id = auth.uid() AND is_admin = TRUE
)
);
-- ============================================
-- 3. EMAIL VERIFICATION TOKENS TABLE
-- ============================================
CREATE TABLE IF NOT EXISTS public.mp_email_verification_tokens (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE NOT NULL,
email TEXT NOT NULL,
token TEXT UNIQUE NOT NULL,
parl_mp_id INT NOT NULL,
mp_name TEXT NOT NULL,
mp_riding TEXT NOT NULL,
mp_party TEXT,
expires_at TIMESTAMPTZ NOT NULL,
used_at TIMESTAMPTZ,
created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL
);
-- Index for token lookup and expiry cleanup
CREATE INDEX IF NOT EXISTS idx_mp_email_tokens_token ON public.mp_email_verification_tokens(token)
WHERE used_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_mp_email_tokens_expiry ON public.mp_email_verification_tokens(expires_at)
WHERE used_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_mp_email_tokens_user ON public.mp_email_verification_tokens(user_id);
-- RLS policies
ALTER TABLE public.mp_email_verification_tokens ENABLE ROW LEVEL SECURITY;
-- Users can only view their own tokens
CREATE POLICY mp_email_tokens_own ON public.mp_email_verification_tokens
FOR SELECT USING (user_id = auth.uid());
-- ============================================
-- 4. HELPER FUNCTIONS
-- ============================================
-- Function to check if a user is a verified MP
CREATE OR REPLACE FUNCTION public.is_verified_mp(p_user_id UUID)
RETURNS BOOLEAN AS $$
DECLARE
v_is_verified BOOLEAN;
BEGIN
SELECT is_verified_mp INTO v_is_verified
FROM public.user_profiles
WHERE id = p_user_id;
RETURN COALESCE(v_is_verified, FALSE);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- Function to get MP verification status
CREATE OR REPLACE FUNCTION public.get_mp_verification_status(p_user_id UUID DEFAULT auth.uid())
RETURNS TABLE(
is_verified BOOLEAN,
mp_name TEXT,
mp_riding TEXT,
mp_party TEXT,
verified_at TIMESTAMPTZ,
verification_method TEXT,
has_pending_request BOOLEAN
) AS $$
BEGIN
RETURN QUERY
SELECT
up.is_verified_mp,
up.mp_name,
up.mp_riding,
up.mp_party,
up.verified_at,
up.verification_method,
EXISTS(
SELECT 1 FROM public.mp_verification_requests
WHERE user_id = p_user_id AND status = 'pending'
) AS has_pending_request
FROM public.user_profiles up
WHERE up.id = p_user_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- Function to approve MP verification request
CREATE OR REPLACE FUNCTION public.approve_mp_verification_request(
p_request_id UUID,
p_admin_id UUID,
p_admin_notes TEXT DEFAULT NULL
)
RETURNS BOOLEAN AS $$
DECLARE
v_request RECORD;
BEGIN
-- Check if admin
IF NOT EXISTS (SELECT 1 FROM public.user_profiles WHERE id = p_admin_id AND is_admin = TRUE) THEN
RAISE EXCEPTION 'Only admins can approve verification requests';
END IF;
-- Get request details
SELECT * INTO v_request
FROM public.mp_verification_requests
WHERE id = p_request_id AND status = 'pending';
IF v_request IS NULL THEN
RAISE EXCEPTION 'Request not found or already processed';
END IF;
-- Update user profile
UPDATE public.user_profiles
SET
is_verified_mp = TRUE,
verified_at = NOW(),
verification_method = 'manual',
parl_mp_id = v_request.parl_mp_id,
mp_name = v_request.mp_name,
mp_riding = v_request.mp_riding,
mp_party = v_request.mp_party
WHERE id = v_request.user_id;
-- Update request status
UPDATE public.mp_verification_requests
SET
status = 'approved',
reviewed_by = p_admin_id,
reviewed_at = NOW(),
admin_notes = p_admin_notes,
updated_at = NOW()
WHERE id = p_request_id;
RETURN TRUE;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- Function to reject MP verification request
CREATE OR REPLACE FUNCTION public.reject_mp_verification_request(
p_request_id UUID,
p_admin_id UUID,
p_admin_notes TEXT
)
RETURNS BOOLEAN AS $$
BEGIN
-- Check if admin
IF NOT EXISTS (SELECT 1 FROM public.user_profiles WHERE id = p_admin_id AND is_admin = TRUE) THEN
RAISE EXCEPTION 'Only admins can reject verification requests';
END IF;
-- Update request status
UPDATE public.mp_verification_requests
SET
status = 'rejected',
reviewed_by = p_admin_id,
reviewed_at = NOW(),
admin_notes = p_admin_notes,
updated_at = NOW()
WHERE id = p_request_id AND status = 'pending';
IF NOT FOUND THEN
RAISE EXCEPTION 'Request not found or already processed';
END IF;
RETURN TRUE;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- Function to revoke MP verification (for fraud cases)
CREATE OR REPLACE FUNCTION public.revoke_mp_verification(
p_user_id UUID,
p_admin_id UUID,
p_reason TEXT
)
RETURNS BOOLEAN AS $$
BEGIN
-- Check if admin
IF NOT EXISTS (SELECT 1 FROM public.user_profiles WHERE id = p_admin_id AND is_admin = TRUE) THEN
RAISE EXCEPTION 'Only admins can revoke verification';
END IF;
-- Update user profile
UPDATE public.user_profiles
SET
is_verified_mp = FALSE,
verified_at = NULL,
verification_method = NULL,
parl_mp_id = NULL,
mp_name = NULL,
mp_riding = NULL,
mp_party = NULL
WHERE id = p_user_id;
-- Log the revocation (insert a cancelled request with admin notes)
INSERT INTO public.mp_verification_requests (
user_id,
email,
mp_name,
mp_riding,
status,
reviewed_by,
reviewed_at,
admin_notes
) VALUES (
p_user_id,
'',
'REVOKED',
'REVOKED',
'cancelled',
p_admin_id,
NOW(),
'Verification revoked: ' || p_reason
);
RETURN TRUE;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- ============================================
-- 5. CLEANUP FUNCTION (Optional)
-- ============================================
-- Function to clean up expired/used tokens (>7 days)
CREATE OR REPLACE FUNCTION public.cleanup_expired_mp_tokens()
RETURNS INT AS $$
DECLARE
v_deleted_count INT;
BEGIN
DELETE FROM public.mp_email_verification_tokens
WHERE (used_at IS NOT NULL AND used_at < NOW() - INTERVAL '7 days')
OR (expires_at < NOW() - INTERVAL '7 days');
GET DIAGNOSTICS v_deleted_count = ROW_COUNT;
RETURN v_deleted_count;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- ============================================
-- SUCCESS MESSAGE
-- ============================================
DO $$
BEGIN
RAISE NOTICE 'MP verification system migration completed successfully!';
RAISE NOTICE 'Updated user_profiles with MP verification fields';
RAISE NOTICE 'Created mp_verification_requests table for manual verification';
RAISE NOTICE 'Created mp_email_verification_tokens table for @parl.gc.ca email verification';
RAISE NOTICE 'Created helper functions: is_verified_mp(), get_mp_verification_status(), approve/reject/revoke functions';
RAISE NOTICE 'Hybrid verification: @parl.gc.ca auto-verify OR manual admin approval';
END $$;
