Based on plan.md and spec.md.
-- Enable extensions
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
-- Enums
CREATE TYPE account_type AS ENUM ('individual','ngo','brand');
CREATE TYPE campaign_status AS ENUM ('draft','verification_requested','verified','published','archived');
CREATE TYPE donation_status AS ENUM ('initiated','success','failed','refunded');
CREATE TYPE verification_source AS ENUM ('dojah','paystack');
CREATE TYPE verification_status AS ENUM ('pending','passed','failed');
CREATE TYPE withdrawal_status AS ENUM ('requested','approved','rejected','failed','paid');
-- Users table
CREATE TABLE users (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
username text UNIQUE NOT NULL,
name text NOT NULL,
account_type account_type NOT NULL DEFAULT 'individual',
email text UNIQUE,
phone text,
verified_id boolean DEFAULT false,
verified_bvn boolean DEFAULT false,
bvn_last4 char(4),
bank_name text,
account_number_mask text,
role text DEFAULT 'donor',
created_at timestamptz DEFAULT now()
);
CREATE INDEX idx_users_username ON users(username);
CREATE INDEX idx_users_role ON users(role);
-- Campaigns
CREATE TABLE campaigns (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
title text NOT NULL,
goal numeric(14,2) NOT NULL,
raised numeric(14,2) NOT NULL DEFAULT 0,
story text,
category text,
video_url text,
images jsonb,
status campaign_status NOT NULL DEFAULT 'draft',
created_at timestamptz DEFAULT now()
);
CREATE INDEX idx_campaigns_user_id ON campaigns(user_id);
CREATE INDEX idx_campaigns_status ON campaigns(status);
-- Donations
CREATE TABLE donations (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
campaign_id uuid NOT NULL REFERENCES campaigns(id) ON DELETE CASCADE,
donor_id uuid REFERENCES users(id),
donor_username text NOT NULL,
amount numeric(14,2) NOT NULL CHECK (amount > 0),
platform_fee numeric(14,2) NOT NULL DEFAULT 0,
net_amount numeric(14,2) NOT NULL DEFAULT 0,
paystack_reference text UNIQUE,
is_anonymous boolean DEFAULT false,
status donation_status NOT NULL DEFAULT 'initiated',
created_at timestamptz DEFAULT now(),
confirmed_at timestamptz
);
CREATE INDEX idx_donations_campaign_id ON donations(campaign_id);
CREATE INDEX idx_donations_donor_id ON donations(donor_id);
CREATE INDEX idx_donations_created_at ON donations(created_at);
CREATE INDEX idx_donations_paystack_reference ON donations(paystack_reference);
-- Withdrawals
CREATE TABLE withdrawals (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
amount numeric(14,2) NOT NULL CHECK (amount > 0),
fee numeric(14,2) NOT NULL DEFAULT 0,
status withdrawal_status NOT NULL DEFAULT 'requested',
requested_at timestamptz DEFAULT now(),
approved_at timestamptz
);
CREATE INDEX idx_withdrawals_user_id ON withdrawals(user_id);
CREATE INDEX idx_withdrawals_status ON withdrawals(status);
-- Updates (campaign updates)
CREATE TABLE updates (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
campaign_id uuid NOT NULL REFERENCES campaigns(id) ON DELETE CASCADE,
content text NOT NULL,
created_at timestamptz DEFAULT now()
);
CREATE INDEX idx_updates_campaign_id ON updates(campaign_id);
-- Comments
CREATE TABLE comments (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
campaign_id uuid NOT NULL REFERENCES campaigns(id) ON DELETE CASCADE,
user_id uuid REFERENCES users(id),
parent_id uuid REFERENCES comments(id) ON DELETE CASCADE,
content text NOT NULL,
created_at timestamptz DEFAULT now()
);
CREATE INDEX idx_comments_campaign_id ON comments(campaign_id);
CREATE INDEX idx_comments_parent_id ON comments(parent_id);
-- Leaderboards
CREATE TABLE leaderboards (
id serial PRIMARY KEY,
donor_id uuid REFERENCES users(id),
total_donated numeric(18,2) NOT NULL DEFAULT 0,
period text NOT NULL, -- e.g., daily, weekly, monthly or ISO range
generated_at timestamptz DEFAULT now()
);
CREATE INDEX idx_leaderboards_period ON leaderboards(period);
CREATE INDEX idx_leaderboards_donor_id ON leaderboards(donor_id);
-- Fees (single-row config)
CREATE TABLE fees (
id smallint PRIMARY KEY DEFAULT 1 CHECK (id = 1),
platform_fee_percent numeric(5,2) NOT NULL DEFAULT 5.00,
withdrawal_fee_percent numeric(5,2) NOT NULL DEFAULT 1.50,
withdrawal_fee_fixed numeric(14,2) NOT NULL DEFAULT 0.00,
updated_at timestamptz DEFAULT now()
);
INSERT INTO fees (id, platform_fee_percent, withdrawal_fee_percent, withdrawal_fee_fixed)
VALUES (1, 5.00, 1.50, 0.00)
ON CONFLICT (id) DO UPDATE SET platform_fee_percent = EXCLUDED.platform_fee_percent, withdrawal_fee_percent = EXCLUDED.withdrawal_fee_percent, withdrawal_fee_fixed = EXCLUDED.withdrawal_fee_fixed, updated_at = now();
-- Verifications
CREATE TABLE verifications (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
source verification_source NOT NULL,
status verification_status NOT NULL DEFAULT 'pending',
metadata jsonb,
created_at timestamptz DEFAULT now(),
updated_at timestamptz DEFAULT now()
);
CREATE INDEX idx_verifications_user_id ON verifications(user_id);
-- Notifications
CREATE TABLE notifications (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
type text NOT NULL,
content jsonb,
is_read boolean DEFAULT false,
created_at timestamptz DEFAULT now()
);
CREATE INDEX idx_notifications_user_id ON notifications(user_id);
CREATE INDEX idx_notifications_is_read ON notifications(is_read);
-- Views
CREATE VIEW campaign_totals AS
SELECT
c.id as campaign_id,
c.user_id as creator_id,
COALESCE(SUM(d.amount),0)::numeric(18,2) as gross_amount,
COALESCE(SUM(d.platform_fee),0)::numeric(18,2) as platform_fees,
COALESCE(SUM(d.net_amount),0)::numeric(18,2) as net_amount
FROM campaigns c
LEFT JOIN donations d ON d.campaign_id = c.id AND d.status = 'success'
GROUP BY c.id, c.user_id;
-- Trigger: calculate fees on donations
CREATE OR REPLACE FUNCTION calculate_donation_fees()
RETURNS trigger AS $$
DECLARE
fee_percent numeric := (SELECT platform_fee_percent FROM fees WHERE id = 1);
BEGIN
IF NEW.platform_fee IS NULL OR NEW.platform_fee = 0 THEN
NEW.platform_fee := round((NEW.amount * fee_percent / 100)::numeric,2);
END IF;
NEW.net_amount := round((NEW.amount - NEW.platform_fee)::numeric,2);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_calculate_donation_fees
BEFORE INSERT ON donations
FOR EACH ROW EXECUTE FUNCTION calculate_donation_fees();
-- RLS policies
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE campaigns ENABLE ROW LEVEL SECURITY;
ALTER TABLE donations ENABLE ROW LEVEL SECURITY;
ALTER TABLE withdrawals ENABLE ROW LEVEL SECURITY;
ALTER TABLE updates ENABLE ROW LEVEL SECURITY;
ALTER TABLE comments ENABLE ROW LEVEL SECURITY;
ALTER TABLE verifications ENABLE ROW LEVEL SECURITY;
ALTER TABLE notifications ENABLE ROW LEVEL SECURITY;
-- Helper: admin role check using JWT claim "role"
-- current_setting('jwt.claims.role', true) returns the role claim when present
-- users: allow users to update own profile and admins full access
CREATE POLICY users_update_owner ON users
FOR UPDATE
USING ( auth.uid() = id OR current_setting('jwt.claims.role', true) = 'admin' )
WITH CHECK ( auth.uid() = id OR current_setting('jwt.claims.role', true) = 'admin' );
-- campaigns: creators and admins can insert/select/update/delete as appropriate
CREATE POLICY campaigns_insert_authenticated ON campaigns
FOR INSERT
WITH CHECK ( auth.uid() = user_id OR current_setting('jwt.claims.role', true) = 'admin' );
CREATE POLICY campaigns_select_public ON campaigns
FOR SELECT
USING ( status = 'published' OR auth.uid() = user_id OR current_setting('jwt.claims.role', true) = 'admin' );
CREATE POLICY campaigns_update_owner ON campaigns
FOR UPDATE
USING ( auth.uid() = user_id OR current_setting('jwt.claims.role', true) = 'admin' )
WITH CHECK ( auth.uid() = user_id OR current_setting('jwt.claims.role', true) = 'admin' );
CREATE POLICY campaigns_delete_owner ON campaigns
FOR DELETE
USING ( auth.uid() = user_id OR current_setting('jwt.claims.role', true) = 'admin' );
-- donations: allow insert by authenticated users or guests (donor_id null), allow owners and admins to view all, public can view success donations for published campaigns
CREATE POLICY donations_insert ON donations
FOR INSERT
WITH CHECK (
(auth.uid() IS NOT NULL AND donor_id = auth.uid())
OR donor_id IS NULL
OR current_setting('jwt.claims.role', true) = 'admin'
);
CREATE POLICY donations_select ON donations
FOR SELECT
USING (
donor_id = auth.uid()
OR current_setting('jwt.claims.role', true) = 'admin'
OR EXISTS (SELECT 1 FROM campaigns c WHERE c.id = donations.campaign_id AND c.status = 'published')
);
CREATE POLICY donations_update_owner ON donations
FOR UPDATE
USING ( donor_id = auth.uid() OR current_setting('jwt.claims.role', true) = 'admin' )
WITH CHECK ( donor_id = auth.uid() OR current_setting('jwt.claims.role', true) = 'admin' );
-- withdrawals: only owner or admin
CREATE POLICY withdrawals_select_owner ON withdrawals
FOR SELECT
USING ( user_id = auth.uid() OR current_setting('jwt.claims.role', true) = 'admin' );
CREATE POLICY withdrawals_insert_owner ON withdrawals
FOR INSERT
WITH CHECK ( user_id = auth.uid() OR current_setting('jwt.claims.role', true) = 'admin' );
CREATE POLICY withdrawals_update_owner ON withdrawals
FOR UPDATE
USING ( user_id = auth.uid() OR current_setting('jwt.claims.role', true) = 'admin' )
WITH CHECK ( user_id = auth.uid() OR current_setting('jwt.claims.role', true) = 'admin' );
-- updates (campaign updates): only campaign creator or admin can insert; public can read if campaign published
CREATE POLICY updates_insert_owner ON updates
FOR INSERT
WITH CHECK (
current_setting('jwt.claims.role', true) = 'admin'
OR EXISTS (SELECT 1 FROM campaigns c WHERE c.id = NEW.campaign_id AND c.user_id = auth.uid())
);
CREATE POLICY updates_select ON updates
FOR SELECT
USING (
EXISTS (SELECT 1 FROM campaigns c WHERE c.id = updates.campaign_id AND c.status = 'published')
OR current_setting('jwt.claims.role', true) = 'admin'
OR EXISTS (SELECT 1 FROM campaigns c WHERE c.id = updates.campaign_id AND c.user_id = auth.uid())
);
-- comments: allow insert by authenticated users or anonymous (user_id null); read public comments if campaign published or owner/admin
CREATE POLICY comments_insert ON comments
FOR INSERT
WITH CHECK (
(auth.uid() IS NOT NULL AND user_id = auth.uid())
OR user_id IS NULL
OR current_setting('jwt.claims.role', true) = 'admin'
);
CREATE POLICY comments_select ON comments
FOR SELECT
USING (
current_setting('jwt.claims.role', true) = 'admin'
OR user_id = auth.uid()
OR EXISTS (SELECT 1 FROM campaigns c WHERE c.id = comments.campaign_id AND c.status = 'published')
);
-- verifications: readable by owner and admin; insert by owner or admin
CREATE POLICY verifications_select ON verifications
FOR SELECT
USING ( user_id = auth.uid() OR current_setting('jwt.claims.role', true) = 'admin' );
CREATE POLICY verifications_insert ON verifications
FOR INSERT
WITH CHECK ( user_id = auth.uid() OR current_setting('jwt.claims.role', true) = 'admin' );
CREATE POLICY verifications_update ON verifications
FOR UPDATE
USING ( user_id = auth.uid() OR current_setting('jwt.claims.role', true) = 'admin' )
WITH CHECK ( user_id = auth.uid() OR current_setting('jwt.claims.role', true) = 'admin' );
-- notifications: owner or admin
CREATE POLICY notifications_select ON notifications
FOR SELECT
USING ( user_id = auth.uid() OR current_setting('jwt.claims.role', true) = 'admin' );
CREATE POLICY notifications_insert ON notifications
FOR INSERT
WITH CHECK ( user_id = auth.uid() OR current_setting('jwt.claims.role', true) = 'admin' );
CREATE POLICY notifications_update ON notifications
FOR UPDATE
USING ( user_id = auth.uid() OR current_setting('jwt.claims.role', true) = 'admin' )
WITH CHECK ( user_id = auth.uid() OR current_setting('jwt.claims.role', true) = 'admin' );
-- Grant explicit privileges to anon/select where appropriate
GRANT SELECT ON campaigns TO anon;
GRANT SELECT ON campaign_totals TO anon;
GRANT SELECT ON leaderboards TO anon;
GRANT INSERT ON donations TO anon;
GRANT SELECT ON updates TO anon;
GRANT SELECT ON comments TO anon;
-- Useful helper functions / views can be added by server processes using the service_role key