Supabase Setup

1# Supabase Configuration
2NEXT_PUBLIC_SUPABASE_URL=https://your-project.supabase.co
3NEXT_PUBLIC_SUPABASE_ANON_KEY=your_anon_key
4SUPABASE_SERVICE_ROLE_KEY=your_service_role_key

1-- Create end_customers table (end_ prefix = founder's end-user data)
2CREATE TABLE end_customers (
3  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
4  stripe_customer_id TEXT UNIQUE NOT NULL,
5  email TEXT NOT NULL,
6  name TEXT,
7  phone TEXT,
8  metadata JSONB DEFAULT '{}'::jsonb,
9  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
10  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
11);
12
13-- Create index for faster lookups
14CREATE INDEX idx_end_customers_stripe_id ON end_customers(stripe_customer_id);
15CREATE INDEX idx_end_customers_email ON end_customers(email);
16
17-- Enable Row Level Security
18ALTER TABLE end_customers ENABLE ROW LEVEL SECURITY;

1-- Create users table (links to Supabase Auth)
2CREATE TABLE users (
3  id UUID PRIMARY KEY REFERENCES auth.users(id),
4  customer_id UUID REFERENCES end_customers(id),
5  full_name TEXT,
6  avatar_url TEXT,
7  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
8  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
9);
10
11-- Enable Row Level Security
12ALTER TABLE users ENABLE ROW LEVEL SECURITY;
13
14-- Create policy to allow users to view their own data
15CREATE POLICY "Users can view own data" ON users
16  FOR SELECT
17  USING (auth.uid() = id);
18
19-- Create policy to allow users to update their own data
20CREATE POLICY "Users can update own data" ON users
21  FOR UPDATE
22  USING (auth.uid() = id);

1-- Create payments table
2CREATE TABLE payments (
3  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
4  customer_id UUID REFERENCES end_customers(id) ON DELETE CASCADE,
5  stripe_payment_intent_id TEXT UNIQUE NOT NULL,
6  amount INTEGER NOT NULL,
7  currency TEXT NOT NULL DEFAULT 'usd',
8  status TEXT NOT NULL,
9  description TEXT,
10  metadata JSONB DEFAULT '{}'::jsonb,
11  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
12);
13
14-- Create indexes
15CREATE INDEX idx_payments_customer_id ON payments(customer_id);
16CREATE INDEX idx_payments_stripe_id ON payments(stripe_payment_intent_id);
17
18-- Enable Row Level Security
19ALTER TABLE payments ENABLE ROW LEVEL SECURITY;

1-- Create subscriptions table
2CREATE TABLE subscriptions (
3  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
4  customer_id UUID REFERENCES end_customers(id) ON DELETE CASCADE,
5  stripe_subscription_id TEXT UNIQUE NOT NULL,
6  status TEXT NOT NULL,
7  price_id TEXT NOT NULL,
8  quantity INTEGER DEFAULT 1,
9  cancel_at_period_end BOOLEAN DEFAULT FALSE,
10  current_period_start TIMESTAMP WITH TIME ZONE,
11  current_period_end TIMESTAMP WITH TIME ZONE,
12  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
13  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
14);
15
16-- Create indexes
17CREATE INDEX idx_subscriptions_customer_id ON subscriptions(customer_id);
18CREATE INDEX idx_subscriptions_stripe_id ON subscriptions(stripe_subscription_id);
19CREATE INDEX idx_subscriptions_status ON subscriptions(status);
20
21-- Enable Row Level Security
22ALTER TABLE subscriptions ENABLE ROW LEVEL SECURITY;

1-- Allow service role to do everything
2CREATE POLICY "Service role has full access" ON end_customers
3  FOR ALL
4  USING (auth.jwt()->>'role' = 'service_role');
5
6-- Allow authenticated users to view their own customer data
7CREATE POLICY "Users can view own customer data" ON end_customers
8  FOR SELECT
9  USING (
10    auth.uid() IN (
11      SELECT id FROM users WHERE customer_id = end_customers.id
12    )
13  );
14
15-- Similar policies for payments
16CREATE POLICY "Service role has full access" ON payments
17  FOR ALL
18  USING (auth.jwt()->>'role' = 'service_role');
19
20CREATE POLICY "Users can view own payments" ON payments
21  FOR SELECT
22  USING (
23    customer_id IN (
24      SELECT customer_id FROM users WHERE id = auth.uid()
25    )
26  );
27
28-- Similar policies for subscriptions
29CREATE POLICY "Service role has full access" ON subscriptions
30  FOR ALL
31  USING (auth.jwt()->>'role' = 'service_role');
32
33CREATE POLICY "Users can view own subscriptions" ON subscriptions
34  FOR SELECT
35  USING (
36    customer_id IN (
37      SELECT customer_id FROM users WHERE id = auth.uid()
38    )
39  );

1-- Function to update updated_at timestamp
2CREATE OR REPLACE FUNCTION update_updated_at_column()
3RETURNS TRIGGER AS $$
4BEGIN
5  NEW.updated_at = NOW();
6  RETURN NEW;
7END;
8$$ LANGUAGE plpgsql;
9
10-- Create triggers
11CREATE TRIGGER update_end_customers_updated_at
12  BEFORE UPDATE ON end_customers
13  FOR EACH ROW
14  EXECUTE FUNCTION update_updated_at_column();
15
16CREATE TRIGGER update_users_updated_at
17  BEFORE UPDATE ON users
18  FOR EACH ROW
19  EXECUTE FUNCTION update_updated_at_column();
20
21CREATE TRIGGER update_subscriptions_updated_at
22  BEFORE UPDATE ON subscriptions
23  FOR EACH ROW
24  EXECUTE FUNCTION update_updated_at_column();

1import { supabase } from '@/lib/supabase/client';
2
3async function testSupabaseConnection() {
4  try {
5    const { data, error } = await supabase
6      .from('end_customers')
7      .select('count')
8      .limit(1);
9    
10    if (error) throw error;
11    
12    console.log('✓ Supabase connection successful');
13    return true;
14  } catch (error) {
15    console.error('✗ Supabase connection failed:', error);
16    return false;
17  }
18}

1-- Enable realtime for end_customers table
2ALTER PUBLICATION supabase_realtime ADD TABLE end_customers;
3
4-- Enable realtime for payments table
5ALTER PUBLICATION supabase_realtime ADD TABLE payments;
6
7-- Enable realtime for subscriptions table
8ALTER PUBLICATION supabase_realtime ADD TABLE subscriptions;