Designing a SaaS Database: Schema and Queries for Users, Billing, and Subscriptions in Supabase

Here’s a schema and SQL query set for creating a Supabase database to manage your SaaS software. It includes the required tables for users with roles, orders, billing history, and a subscription-based usage model where minutes/hours are tracked and deducted.

Database Schema in Table Format

The provided SQL script sets up a fully functional SaaS schema, including a utility function to handle minutes deduction during tool usage.

-- Create schema for SaaS Software

-- 1. Users table
CREATE TABLE users (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash TEXT NOT NULL,
    role VARCHAR(50) NOT NULL CHECK (role IN ('admin', 'customer')),
    created_at TIMESTAMP DEFAULT NOW()
);

-- 2. Orders table
CREATE TABLE orders (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    order_type VARCHAR(50) NOT NULL CHECK (order_type IN ('one_time', 'subscription')),
    amount NUMERIC(10, 2) NOT NULL,
    minutes_purchased INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 3. Billing history table
CREATE TABLE billing_history (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    order_id UUID REFERENCES orders(id) ON DELETE SET NULL,
    amount NUMERIC(10, 2) NOT NULL,
    transaction_date TIMESTAMP DEFAULT NOW()
);

-- 4. Subscriptions table
CREATE TABLE subscriptions (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    minutes_remaining INTEGER NOT NULL,
    start_date TIMESTAMP DEFAULT NOW(),
    end_date TIMESTAMP NOT NULL
);

-- 5. Tool usage logs table
CREATE TABLE tool_usage_logs (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    minutes_used INTEGER NOT NULL,
    usage_date TIMESTAMP DEFAULT NOW()
);

-- 6. Function to deduct minutes upon tool usage
CREATE OR REPLACE FUNCTION deduct_minutes(user_uuid UUID, minutes_used INTEGER) RETURNS VOID AS $$
BEGIN
    UPDATE subscriptions
    SET minutes_remaining = minutes_remaining - minutes_used
    WHERE user_id = user_uuid AND minutes_remaining >= minutes_used;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Insufficient minutes for user %', user_uuid;
    END IF;

    INSERT INTO tool_usage_logs (user_id, minutes_used)
    VALUES (user_uuid, minutes_used);
END;
$$ LANGUAGE plpgsql;

-- Example Query to Add a Subscription
INSERT INTO subscriptions (user_id, minutes_remaining, end_date)
VALUES ('<user_id>', 300, NOW() + INTERVAL '30 days');

-- Example Query to Deduct Minutes
SELECT deduct_minutes('<user_id>', 15);

-- Query to Fetch User Full Details
SELECT 
    u.id AS user_id,
    u.email,
    u.role,
    bh.amount AS last_billed_amount,
    bh.transaction_date AS last_billed_date,
    s.minutes_remaining,
    s.end_date AS next_billing_date
FROM users u
LEFT JOIN billing_history bh ON u.id = bh.user_id
LEFT JOIN subscriptions s ON u.id = s.user_id
WHERE u.id = '<user_id>'
ORDER BY bh.transaction_date DESC
LIMIT 1;