-- Bridge MCP Server - Supabase Schema
-- Run this in your Supabase SQL Editor
-- Create customers table
CREATE TABLE IF NOT EXISTS customers (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
email TEXT NOT NULL UNIQUE,
hubspot_contact_id TEXT,
access_token TEXT NOT NULL UNIQUE,
subscription_status TEXT NOT NULL DEFAULT 'active' CHECK (subscription_status IN ('active', 'cancelled', 'past_due', 'trialing')),
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
-- Create index on access_token for fast lookups
CREATE INDEX IF NOT EXISTS idx_customers_access_token ON customers(access_token);
-- Create index on email for lookups
CREATE INDEX IF NOT EXISTS idx_customers_email ON customers(email);
-- Create index on hubspot_contact_id
CREATE INDEX IF NOT EXISTS idx_customers_hubspot_contact_id ON customers(hubspot_contact_id);
-- Create updated_at trigger
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER update_customers_updated_at
BEFORE UPDATE ON customers
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();
-- Create webhook_logs table for debugging
CREATE TABLE IF NOT EXISTS webhook_logs (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
source TEXT NOT NULL, -- 'hubspot', etc.
event_type TEXT,
payload JSONB,
processed BOOLEAN DEFAULT false,
error TEXT,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
-- Create index on webhook_logs
CREATE INDEX IF NOT EXISTS idx_webhook_logs_created_at ON webhook_logs(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_webhook_logs_processed ON webhook_logs(processed);
