CongressMCP-full

-- Supabase Schema for Congressional MCP Server -- Run this in your Supabase SQL Editor to create the required tables -- Enable UUID extension CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; -- Users table CREATE TABLE IF NOT EXISTS users ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), email VARCHAR(255) UNIQUE NOT NULL, stripe_customer_id VARCHAR(255) UNIQUE, subscription_tier VARCHAR(20) NOT NULL DEFAULT 'free' CHECK (subscription_tier IN ('free', 'pro', 'enterprise')), created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, is_active BOOLEAN DEFAULT true ); -- API Keys table CREATE TABLE IF NOT EXISTS api_keys ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE, key_hash VARCHAR(64) NOT NULL UNIQUE, -- SHA-256 hash of the API key tier VARCHAR(20) NOT NULL CHECK (tier IN ('free', 'pro', 'enterprise')), created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, expires_at TIMESTAMP WITH TIME ZONE, is_active BOOLEAN DEFAULT true, last_used_at TIMESTAMP WITH TIME ZONE ); -- Usage tracking table CREATE TABLE IF NOT EXISTS usage_tracking ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE, date DATE NOT NULL, request_count INTEGER NOT NULL DEFAULT 0, feature VARCHAR(100) NOT NULL, endpoint VARCHAR(255) NOT NULL, UNIQUE(user_id, date, feature) -- One record per user per day per feature ); -- Magic links table for passwordless authentication CREATE TABLE IF NOT EXISTS magic_links ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), user_id UUID REFERENCES users(id) ON DELETE CASCADE, email VARCHAR(255) NOT NULL, token VARCHAR(255) NOT NULL UNIQUE, expires_at TIMESTAMP WITH TIME ZONE NOT NULL, used_at TIMESTAMP WITH TIME ZONE, is_used BOOLEAN DEFAULT false, created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, purpose VARCHAR(50) DEFAULT 'key_management' CHECK (purpose IN ('key_management', 'registration')), metadata JSONB DEFAULT '{}'::jsonb ); -- Indexes for performance CREATE INDEX IF NOT EXISTS idx_users_email ON users(email); CREATE INDEX IF NOT EXISTS idx_users_stripe_customer_id ON users(stripe_customer_id); CREATE INDEX IF NOT EXISTS idx_api_keys_user_id ON api_keys(user_id); CREATE INDEX IF NOT EXISTS idx_api_keys_key_hash ON api_keys(key_hash); CREATE INDEX IF NOT EXISTS idx_api_keys_active ON api_keys(is_active) WHERE is_active = true; CREATE INDEX IF NOT EXISTS idx_usage_tracking_user_date ON usage_tracking(user_id, date); CREATE INDEX IF NOT EXISTS idx_usage_tracking_feature ON usage_tracking(feature); CREATE INDEX IF NOT EXISTS idx_magic_links_token ON magic_links(token); CREATE INDEX IF NOT EXISTS idx_magic_links_email ON magic_links(email); CREATE INDEX IF NOT EXISTS idx_magic_links_expires ON magic_links(expires_at); CREATE INDEX IF NOT EXISTS idx_magic_links_user_id ON magic_links(user_id); -- Updated_at trigger function CREATE OR REPLACE FUNCTION update_updated_at_column() RETURNS TRIGGER AS $$ BEGIN NEW.updated_at = CURRENT_TIMESTAMP; RETURN NEW; END; $$ language 'plpgsql'; -- Apply updated_at trigger to users table CREATE TRIGGER update_users_updated_at BEFORE UPDATE ON users FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); -- Row Level Security (RLS) policies ALTER TABLE users ENABLE ROW LEVEL SECURITY; ALTER TABLE api_keys ENABLE ROW LEVEL SECURITY; ALTER TABLE usage_tracking ENABLE ROW LEVEL SECURITY; ALTER TABLE magic_links ENABLE ROW LEVEL SECURITY; -- Service role can access all data CREATE POLICY "Service role can access all users" ON users FOR ALL USING (auth.role() = 'service_role'); CREATE POLICY "Service role can access all api_keys" ON api_keys FOR ALL USING (auth.role() = 'service_role'); CREATE POLICY "Service role can access all usage_tracking" ON usage_tracking FOR ALL USING (auth.role() = 'service_role'); CREATE POLICY "Service role can access all magic_links" ON magic_links FOR ALL USING (auth.role() = 'service_role'); -- Insert some test data (optional) -- Uncomment if you want sample data for testing -- INSERT INTO users (email, subscription_tier) VALUES -- ('test@example.com', 'free'), -- ('pro@example.com', 'pro'), -- ('enterprise@example.com', 'enterprise'); -- Sample API keys (these are hashed versions of test keys) -- Test keys would be: lawgiver_free_test_abc123, etc. -- INSERT INTO api_keys (user_id, key_hash, tier) -- SELECT -- u.id, -- encode(sha256('test_key_' || u.subscription_tier::text), 'hex'), -- u.subscription_tier -- FROM users u;