Database Schema & Setup

ClawPlate uses PostgreSQL via Supabase with a complete schema for user management, subscriptions, and analytics. Here's how to set it up and understand the structure.

Preview DB Schema Include

Complete Schema

1. Plans Table

sql

CREATE TABLE plans (
  id TEXT PRIMARY KEY,                    -- 'free', 'pro', 'enterprise'
  name TEXT NOT NULL,                     -- 'Free', 'Pro', 'Enterprise'
  description TEXT,
  price INTEGER NOT NULL,                 -- Price in cents (0, 2900, 9900)
  interval TEXT NOT NULL,                 -- 'month' or 'year'
  features JSONB,                         -- ["Feature 1", "Feature 2"]
  limits JSONB,                           -- {"requests": 1000, "storage": "1GB"}
  stripe_product_id TEXT,
  stripe_price_id TEXT,
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- Indexes for performance
CREATE INDEX idx_plans_active ON plans(is_active) WHERE is_active = true;
CREATE INDEX idx_plans_stripe_price ON plans(stripe_price_id) WHERE stripe_price_id IS NOT NULL;

2. User Profiles Table

sql

CREATE TABLE user_profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  email TEXT NOT NULL UNIQUE,
  full_name TEXT,
  avatar_url TEXT,
  subscription_status TEXT DEFAULT 'inactive' CHECK (subscription_status IN ('inactive', 'active', 'trialing', 'past_due', 'canceled', 'expired')),
  has_access BOOLEAN DEFAULT false,       -- Access to premium features
  stripe_customer_id TEXT UNIQUE,
  purchase_id UUID,                       -- Unique purchase ID
  last_login TIMESTAMP,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_user_profiles_email ON user_profiles(email);
CREATE INDEX idx_user_profiles_stripe_customer ON user_profiles(stripe_customer_id) WHERE stripe_customer_id IS NOT NULL;
CREATE INDEX idx_user_profiles_subscription_status ON user_profiles(subscription_status);

3. User Subscriptions Table

sql

CREATE TABLE user_subscriptions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES user_profiles(id) ON DELETE CASCADE,
  plan_id TEXT NOT NULL REFERENCES plans(id) ON DELETE RESTRICT,
  stripe_subscription_id TEXT UNIQUE,
  stripe_customer_id TEXT,
  status TEXT NOT NULL CHECK (status IN ('incomplete', 'incomplete_expired', 'trialing', 'active', 'past_due', 'canceled', 'unpaid')),
  current_period_start TIMESTAMP,
  current_period_end TIMESTAMP,
  cancel_at_period_end BOOLEAN DEFAULT false,
  canceled_at TIMESTAMP,
  trial_start TIMESTAMP,
  trial_end TIMESTAMP,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_user_subscriptions_user_id ON user_subscriptions(user_id);
CREATE INDEX idx_user_subscriptions_stripe_subscription ON user_subscriptions(stripe_subscription_id) WHERE stripe_subscription_id IS NOT NULL;
CREATE INDEX idx_user_subscriptions_status ON user_subscriptions(status);
CREATE INDEX idx_user_subscriptions_active ON user_subscriptions(user_id, status) WHERE status IN ('active', 'trialing');

4. Purchases Table (One-time payments)

sql

CREATE TABLE purchases (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES user_profiles(id) ON DELETE CASCADE,
  stripe_session_id TEXT UNIQUE,
  product_purchased TEXT NOT NULL,
  amount INTEGER NOT NULL,              -- Amount in cents
  currency TEXT DEFAULT 'usd',
  status TEXT DEFAULT 'pending' CHECK (status IN ('pending', 'completed', 'failed', 'canceled')),
  github_access_granted BOOLEAN DEFAULT false,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_purchases_user_id ON purchases(user_id);
CREATE INDEX idx_purchases_status ON purchases(status);
CREATE INDEX idx_purchases_stripe_session ON purchases(stripe_session_id) WHERE stripe_session_id IS NOT NULL;

5. Activity Logs Table

sql

CREATE TABLE activity_logs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES user_profiles(id) ON DELETE SET NULL,
  action TEXT NOT NULL,                  -- 'login', 'subscription_created', 'payment_succeeded'
  details JSONB,                         -- Action details
  ip_address INET,
  user_agent TEXT,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_activity_logs_user_id ON activity_logs(user_id);
CREATE INDEX idx_activity_logs_action ON activity_logs(action);
CREATE INDEX idx_activity_logs_created_at ON activity_logs(created_at);

Row Level Security (RLS)

User Profiles Security

sql

-- Enable RLS
ALTER TABLE user_profiles ENABLE ROW LEVEL SECURITY;

-- Users can view their own profile
CREATE POLICY "Users can view own profile" ON user_profiles
  FOR SELECT USING (auth.uid() = id);

-- Users can update their own profile
CREATE POLICY "Users can update own profile" ON user_profiles
  FOR UPDATE USING (auth.uid() = id);

-- Admins can view all profiles
CREATE POLICY "Admins can view all profiles" ON user_profiles
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM user_profiles 
      WHERE id = auth.uid() 
      AND email IN ('admin@example.com', 'milan.herbe@gmail.com')
    )
  );

Subscriptions Security

sql

-- Enable RLS
ALTER TABLE user_subscriptions ENABLE ROW LEVEL SECURITY;

-- Users can view their own subscriptions
CREATE POLICY "Users can view own subscriptions" ON user_subscriptions
  FOR SELECT USING (auth.uid() = user_id);

-- Admins can view all subscriptions
CREATE POLICY "Admins can view all subscriptions" ON user_subscriptions
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM user_profiles 
      WHERE id = auth.uid() 
      AND email IN ('admin@example.com', 'milan.herbe@gmail.com')
    )
  );

Automatic Triggers

Auto-create User Profile

sql

-- Function to automatically create user profile
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO public.user_profiles (
    id, email, full_name, subscription_status, has_access, created_at, updated_at
  )
  VALUES (
    NEW.id,
    NEW.email,
    COALESCE(NEW.raw_user_meta_data->>'full_name', NEW.email),
    'inactive',
    false,
    NOW(),
    NOW()
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Trigger on new user creation
DROP TRIGGER IF EXISTS on_auth_user_created ON auth.users;
CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();

Auto-update Timestamps

sql

-- Function to automatically update updated_at
CREATE OR REPLACE FUNCTION public.update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Apply to all tables
CREATE TRIGGER update_user_profiles_updated_at
  BEFORE UPDATE ON user_profiles
  FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();

CREATE TRIGGER update_user_subscriptions_updated_at
  BEFORE UPDATE ON user_subscriptions
  FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();

Business Logic Constraints

Single Active Subscription

sql

-- Ensure user can only have one active subscription
CREATE OR REPLACE FUNCTION check_single_active_subscription()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.status IN ('active', 'trialing') THEN
    IF EXISTS (
      SELECT 1 FROM user_subscriptions 
      WHERE user_id = NEW.user_id 
      AND status IN ('active', 'trialing') 
      AND id != NEW.id
    ) THEN
      RAISE EXCEPTION 'User can only have one active subscription';
    END IF;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER check_single_active_subscription_trigger
  BEFORE INSERT OR UPDATE ON user_subscriptions
  FOR EACH ROW EXECUTE FUNCTION check_single_active_subscription();

Sample Data

Insert Default Plans

sql

INSERT INTO plans (id, name, description, price, interval, features, limits, is_active) VALUES
('free', 'Free', 'Get started with basic features', 0, 'month', 
 '["Basic dashboard", "5 projects", "Community support"]'::jsonb,
 '{"projects": 5, "storage": "1GB", "api_calls": 1000}'::jsonb,
 true),
('pro', 'Pro', 'Perfect for growing businesses', 2900, 'month',
 '["Advanced dashboard", "Unlimited projects", "Priority support", "Analytics"]'::jsonb,
 '{"projects": -1, "storage": "100GB", "api_calls": 50000}'::jsonb,
 true),
('enterprise', 'Enterprise', 'For large organizations', 9900, 'month',
 '["Full dashboard", "Unlimited everything", "24/7 support", "Custom integrations"]'::jsonb,
 '{"projects": -1, "storage": "1TB", "api_calls": -1}'::jsonb,
 true);

Performance Optimizations

Composite Indexes

sql

-- Analytics queries
CREATE INDEX idx_user_subscriptions_analytics ON user_subscriptions(status, created_at, current_period_end);

-- Billing queries
CREATE INDEX idx_user_subscriptions_billing ON user_subscriptions(user_id, status, current_period_end);

Materialized Views for Analytics

sql

-- Revenue statistics view
CREATE MATERIALIZED VIEW revenue_stats AS
SELECT 
  DATE_TRUNC('month', created_at) as month,
  COUNT(*) as new_subscriptions,
  SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) as active_subscriptions,
  SUM(CASE WHEN status = 'canceled' THEN 1 ELSE 0 END) as canceled_subscriptions
FROM user_subscriptions
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;

-- Function to refresh the view
CREATE OR REPLACE FUNCTION refresh_revenue_stats()
RETURNS void AS $$
BEGIN
  REFRESH MATERIALIZED VIEW revenue_stats;
END;
$$ LANGUAGE plpgsql;

Database Relationships

auth.users (Supabase Auth)
    ↓ (1:1)
user_profiles
    ↓ (1:N)
user_subscriptions → plans
    ↓ (1:N)
purchases
    ↓ (1:N)
activity_logs

Setup Instructions

1. Run the Schema

Copy and paste the SQL commands above in your Supabase SQL editor in this order:

Create tables (plans, user_profiles, user_subscriptions, purchases, activity_logs)
Create indexes
Enable RLS and create policies
Create triggers and functions
Insert sample data

2. Environment Variables

bash

SUPABASE_URL=https://your-project.supabase.co
SUPABASE_ANON_KEY=eyJ...
SUPABASE_SERVICE_ROLE_KEY=eyJ...

3. Test the Setup

javascript

// Test basic queries
const { data: plans } = await supabase
  .from('plans')
  .select('*')
  .eq('is_active', true)

const { data: profile } = await supabase
  .from('user_profiles')
  .select('*, user_subscriptions(*)')
  .eq('id', user.id)
  .single()

Best Practices

Security

Always use RLS policies
Never expose service role key to client
Validate data at database level with constraints
Use proper foreign key relationships

Performance

Add indexes on frequently queried columns
Use composite indexes for complex queries
Consider materialized views for heavy analytics
Monitor query performance regularly

Maintenance

Regular backups
Monitor database size and performance
Update statistics regularly
Clean up old logs periodically

Extending the Schema

Always use migrations for schema changes
Test RLS policies after adding new tables
Update triggers when adding new tables
Document all custom functions and triggers

The schema handles user management, subscriptions, payments, and analytics while maintaining security and performance. Everything is set up to work automatically with Stripe webhooks and Supabase Auth.

Database Schema & Setup ​

Preview DB Schema Include ​

Complete Schema ​

1. Plans Table ​

2. User Profiles Table ​

3. User Subscriptions Table ​

4. Purchases Table (One-time payments) ​

5. Activity Logs Table ​

Row Level Security (RLS) ​

User Profiles Security ​

Subscriptions Security ​

Automatic Triggers ​

Auto-create User Profile ​

Auto-update Timestamps ​

Business Logic Constraints ​

Single Active Subscription ​

Sample Data ​

Insert Default Plans ​

Performance Optimizations ​

Composite Indexes ​

Materialized Views for Analytics ​

Database Relationships ​

Setup Instructions ​

1. Run the Schema ​

2. Environment Variables ​

3. Test the Setup ​

Best Practices ​

Security ​

Performance ​

Maintenance ​

Extending the Schema ​