samsour.de
eny.space Landingpage
1-- Create subscriptions table to track user subscriptions
2CREATE TABLE IF NOT EXISTS subscriptions (
3 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
4 user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
5 stripe_customer_id TEXT,
6 stripe_subscription_id TEXT UNIQUE,
7 stripe_price_id TEXT,
8 status TEXT NOT NULL,
9 current_period_start TIMESTAMPTZ,
10 current_period_end TIMESTAMPTZ,
11 cancel_at_period_end BOOLEAN DEFAULT false,
12 created_at TIMESTAMPTZ DEFAULT NOW(),
13 updated_at TIMESTAMPTZ DEFAULT NOW()
14);
15
16-- Create index for faster lookups
17CREATE INDEX IF NOT EXISTS subscriptions_user_id_idx ON subscriptions(user_id);
18CREATE INDEX IF NOT EXISTS subscriptions_stripe_customer_id_idx ON subscriptions(stripe_customer_id);
19CREATE INDEX IF NOT EXISTS subscriptions_stripe_subscription_id_idx ON subscriptions(stripe_subscription_id);
20
21-- Enable Row Level Security
22ALTER TABLE subscriptions ENABLE ROW LEVEL SECURITY;
23
24-- Policy: Users can only see their own subscriptions
25DROP POLICY IF EXISTS "Users can view own subscriptions" ON subscriptions;
26CREATE POLICY "Users can view own subscriptions"
27 ON subscriptions
28 FOR SELECT
29 USING (auth.uid() = user_id);
30
31-- Policy: Users can insert their own subscription records (for initial customer creation)
32-- But they can ONLY insert records with status='incomplete' to prevent fraud
33DROP POLICY IF EXISTS "Users can insert own subscriptions" ON subscriptions;
34CREATE POLICY "Users can insert own subscriptions"
35 ON subscriptions
36 FOR INSERT
37 WITH CHECK (
38 auth.uid() = user_id
39 AND status = 'incomplete'
40 );
41
42-- Users CANNOT update their own subscriptions directly
43-- All updates must come from webhooks (using service role) or validated server actions
44-- This prevents users from setting status='active' without paying
45DROP POLICY IF EXISTS "Users can update own subscriptions" ON subscriptions;
46
47-- Note: Service role (used by webhooks via admin client) bypasses RLS entirely
48-- The admin client uses SUPABASE_SERVICE_ROLE_KEY which automatically bypasses all RLS policies.
49-- No policy needed for service role since it bypasses RLS.
50
51-- Function to update updated_at timestamp
52CREATE OR REPLACE FUNCTION update_updated_at_column()
53RETURNS TRIGGER AS $$
54BEGIN
55 NEW.updated_at = NOW();
56 RETURN NEW;
57END;
58$$ language 'plpgsql';
59
60-- Trigger to automatically update updated_at
61DROP TRIGGER IF EXISTS update_subscriptions_updated_at ON subscriptions;
62CREATE TRIGGER update_subscriptions_updated_at
63 BEFORE UPDATE ON subscriptions
64 FOR EACH ROW
65 EXECUTE FUNCTION update_updated_at_column();