Repository navigation
Expand file tree
/
Copy pathdatabase-schema.sql
More file actions
87 lines (72 loc) · 2.9 KB
/
Copy pathdatabase-schema.sql
File metadata and controls
87 lines (72 loc) · 2.9 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
-- Cryptic Solutions Database Schema
-- Run this SQL in your Supabase SQL Editor after creating your project
-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
-- Users table (extends Supabase auth.users)
CREATE TABLE IF NOT EXISTS users (
id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
email TEXT NOT NULL,
name TEXT NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
-- Purchases table
CREATE TABLE IF NOT EXISTS purchases (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
product_id TEXT NOT NULL,
transaction_id TEXT NOT NULL UNIQUE,
email TEXT NOT NULL,
name TEXT NOT NULL,
status TEXT NOT NULL CHECK (status IN ('pending', 'completed', 'failed')),
amount DECIMAL(10, 2) NOT NULL,
currency TEXT NOT NULL DEFAULT 'NGN',
referral_code TEXT,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
-- Make user_id nullable to allow purchases before user confirms email
ALTER TABLE purchases ALTER COLUMN user_id DROP NOT NULL;
-- Create index for faster lookups
CREATE INDEX IF NOT EXISTS idx_purchases_user_id ON purchases(user_id);
CREATE INDEX IF NOT EXISTS idx_purchases_transaction_id ON purchases(transaction_id);
CREATE INDEX IF NOT EXISTS idx_purchases_email ON purchases(email);
-- RLS Policies
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE purchases ENABLE ROW LEVEL SECURITY;
-- Users policies: Users can read their own data
CREATE POLICY "Users can view own data" ON users
FOR SELECT USING (auth.uid() = id);
CREATE POLICY "Users can update own data" ON users
FOR UPDATE USING (auth.uid() = id);
-- Purchases policies: Users can only see their own purchases
CREATE POLICY "Users can view own purchases" ON purchases
FOR SELECT USING (auth.uid() = user_id);
-- Function to update updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- Triggers to auto-update updated_at
CREATE TRIGGER update_users_updated_at BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_purchases_updated_at BEFORE UPDATE ON purchases
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
-- Products table (for future products like Quickland)
CREATE TABLE IF NOT EXISTS products (
id TEXT PRIMARY KEY,
name TEXT NOT NULL,
description TEXT,
price DECIMAL(10, 2) NOT NULL,
currency TEXT NOT NULL DEFAULT 'NGN',
is_active BOOLEAN DEFAULT true,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
-- Insert default products
INSERT INTO products (id, name, description, price, currency, is_active) VALUES
('ielts-manual', 'IELTS Preparation Manual', 'Comprehensive IELTS preparation guide', 5000.00, 'NGN', true)
ON CONFLICT (id) DO NOTHING;