Clarification: Each user has both a unique public username and a displayed name (display_name). Both fields are shown throughout the UI (leaderboards, donor lists, profiles). For account records, display_name is required; guest donations may provide an optional donor_display_name that is shown where applicable.
Concise architecture and dev workflow for Assist (Nigerian crowdfunding platform).
Goals:
- One-time NGN donations via Paystack (guest donations allowed).
- Verified creators (Dojah), media via Mux and Supabase Storage.
- Supabase for Auth, Postgres, Storage, SMTP.
- Deployed frontend/serverless on Vercel; monitoring via Sentry.
Deliverables in this document:
- System architecture diagram
- Supabase SQL schema (migration)
- API surface, scheduled jobs, webhooks
- Roles, RLS examples
- Sequence diagrams (donation, campaign creation, payout)
- Frontend components mapped to pages
- Testing plan
- Dev & deployment plan
- Security checklist
- Resource sizing & cost guidance
All filenames referenced are clickable: plan.md
graph LR
A[Browser Client] -->|SSG/ISR| VercelFrontend
A -->|WebShare / Copy Link| Share[Share Actions]
VercelFrontend[Frontend (Next.js)] -->|Serverless API| VercelAPI
VercelAPI -->|REST/Edge| Supabase[Supabase Postgres & Auth]
VercelAPI -->|Webhook endpoints| Paystack[Paystack]
VercelAPI -->|Webhook endpoints| Dojah[Dojah]
VercelAPI -->|Upload signed URL| Mux[Mux (video)]
VercelAPI -->|Store images| SupabaseStorage[Supabase Storage]
Supabase -->|Storage| SupabaseStorage
Mux -->|Playback| CDN[CDN]
VercelFrontend -->|CDN assets| CDN
VercelAPI -->|Send SMS| Twilio[Twilio]
VercelAPI -->|Send Emails| SMTP[Supabase SMTP]
AdminDash[Admin Dashboard] --> VercelAPI
Monitoring[Sentry] --> VercelAPI
Logging[Logs & Alerts] -->|Vercel + Supabase| Ops
Notes:
- Static pages SSG/ISR on Vercel; serverless edge functions for donation init and webhook handlers.
- Media: images store in Supabase Storage; videos upload to Mux via signed upload URLs; Mux webhooks validated.
Migration filename reference: migrations/001_init.sql
-- migration: 001_init.sql
-- Supabase Postgres schema for Assist
-- Roles table (application-level)
CREATE TYPE account_type AS ENUM ('individual','ngo','brand');
CREATE TABLE users (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
email text UNIQUE,
phone text,
username text UNIQUE NOT NULL,
display_name text,
account_type account_type NOT NULL DEFAULT 'individual',
is_verified boolean DEFAULT false,
dojah_id text,
bvn_mask text,
bvn_verified boolean DEFAULT false,
created_at timestamptz DEFAULT now()
);
CREATE INDEX idx_users_username ON users(username);
CREATE TABLE campaigns (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
creator_id uuid REFERENCES users(id) ON DELETE CASCADE,
title text NOT NULL,
short_description text,
full_description text,
goal_amount numeric(14,2) NOT NULL,
currency text NOT NULL DEFAULT 'NGN',
video_mux_asset_id text,
video_duration_seconds int,
images text[], -- list of Supabase Storage paths
is_published boolean DEFAULT false,
is_verified boolean DEFAULT false, -- Dojah + admin approval
created_at timestamptz DEFAULT now(),
updated_at timestamptz DEFAULT now()
);
CREATE INDEX idx_campaigns_creator ON campaigns(creator_id);
CREATE INDEX idx_campaigns_published ON campaigns(is_published, is_verified);
CREATE TABLE donations (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
campaign_id uuid REFERENCES campaigns(id) ON DELETE CASCADE,
donor_user_id uuid REFERENCES users(id), -- nullable for anonymous (guest) donors
donor_username text NOT NULL, -- public username for leaderboards
donor_display_name text,
amount numeric(14,2) NOT NULL,
currency text NOT NULL DEFAULT 'NGN',
paystack_reference text UNIQUE,
platform_fee numeric(14,2) NOT NULL DEFAULT 0,
net_amount numeric(14,2) NOT NULL DEFAULT 0,
status text NOT NULL DEFAULT 'initiated', -- initiated, success, failed, refunded
created_at timestamptz DEFAULT now(),
confirmed_at timestamptz
);
CREATE INDEX idx_donations_campaign ON donations(campaign_id);
CREATE INDEX idx_donations_created_at ON donations(created_at);
CREATE TABLE campaign_updates (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
campaign_id uuid REFERENCES campaigns(id) ON DELETE CASCADE,
author_id uuid REFERENCES users(id),
title text,
body text,
created_at timestamptz DEFAULT now()
);
CREATE TABLE comments (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
campaign_id uuid REFERENCES campaigns(id) ON DELETE CASCADE,
parent_id uuid REFERENCES comments(id) ON DELETE CASCADE,
author_id uuid REFERENCES users(id),
body text NOT NULL,
created_at timestamptz DEFAULT now()
);
CREATE TABLE withdrawals (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
campaign_id uuid REFERENCES campaigns(id),
owner_id uuid REFERENCES users(id) NOT NULL,
amount numeric(14,2) NOT NULL,
currency text NOT NULL DEFAULT 'NGN',
status text NOT NULL DEFAULT 'requested', -- requested, approved, rejected, paid
admin_note text,
paystack_transfer_id text,
created_at timestamptz DEFAULT now(),
processed_at timestamptz
);
CREATE TABLE admin_settings (
id serial PRIMARY KEY,
platform_fee_percent numeric(5,2) DEFAULT 5.00, -- percentage taken from donation
withdrawal_fee_percent numeric(5,2) DEFAULT 1.50,
withdrawal_fee_fixed numeric(14,2) DEFAULT 0.00,
min_payout_amount numeric(14,2) DEFAULT 5000.00,
data_retention_days int DEFAULT 365,
updated_at timestamptz DEFAULT now()
);
INSERT INTO admin_settings(platform_fee_percent, withdrawal_fee_percent) VALUES (5.00,1.50);
CREATE TABLE vendor_verifications (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
user_id uuid REFERENCES users(id) UNIQUE,
dojah_check_id text,
status text DEFAULT 'pending', -- pending, passed, failed
last_checked timestamptz,
created_at timestamptz DEFAULT now()
);
-- Leaderboard cache
CREATE TABLE leaderboards_cache (
id serial PRIMARY KEY,
timeframe text NOT NULL, -- daily, weekly, monthly
scope text NOT NULL, -- site, campaign:{campaign_id}
computed_at timestamptz DEFAULT now(),
data jsonb NOT NULL
);
-- Retention and audit
CREATE TABLE events_audit (
id serial PRIMARY KEY,
event_type text NOT NULL,
payload jsonb,
created_at timestamptz DEFAULT now()
);
-- Useful view: campaign totals (materialized can be created later)
CREATE VIEW campaign_totals AS
SELECT
c.id as campaign_id,
c.creator_id,
COALESCE(SUM(d.amount),0) as gross_amount,
COALESCE(SUM(d.platform_fee),0) as platform_fees,
COALESCE(SUM(d.net_amount),0) 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.creator_id;Notes:
- Sensitive fields like BVN stored masked in users.bvn_mask; full values should not be stored. Consider encrypting with Postgres pgcrypto or store only hashes.
Base: https://staging.assist.ng/api/
-
POST /api/donations/initiate
- Purpose: create donation record, calculate fees, return Paystack auth flow URL/token.
- Auth: optional (guest allowed)
- Input: { campaign_id, donor_username, donor_display_name?, amount, currency=NGN, anonymous=false }
- Output: { donation_id, paystack_auth_url, paystack_reference }
- Errors: 400 validation, 404 campaign, 429 rate limit, 500 server
-
POST /api/donations/webhook/paystack
- Purpose: receive Paystack event, verify signature, update donation status.
- Auth: webhook verification (signature)
- Input: Paystack event payload
- Output: 200 OK
- Errors: 400 invalid signature, 404 donation, 500 db error
-
GET /api/campaigns/:id
- Purpose: get public campaign data (only published & verified)
- Auth: public
- Output: campaign metadata, recent donors, updates, leaderboard snippet
- Errors: 404 not found, 403 not published
-
POST /api/campaigns
- Purpose: create draft campaign (creator must be verified before publish)
- Auth: creator (JWT via Supabase)
- Input: { title, short_description, full_description, goal_amount, currency, images[], video_metadata }
- Output: { campaign_id }
- Errors: 401, 400 validation
-
POST /api/campaigns/:id/upload-video
- Purpose: create Mux signed upload URL and register expected asset metadata
- Auth: creator
- Input: { campaign_id, file_name, file_size_bytes, mime_type }
- Output: { upload_url, mux_upload_id }
- Errors: 413 file too large, 400 invalid
-
POST /api/campaigns/:id/publish
- Purpose: request campaign publish -> trigger Dojah verification flow (if not already)
- Auth: creator
- Input: { campaign_id }
- Output: { status: 'verification_requested' }
- Errors: 400 not verified, 401
-
POST /api/dojah/webhook
- Purpose: receive Dojah verification results
- Auth: verify signature
- Input: Dojah payload
- Output: 200 OK
-
POST /api/users/username
- Purpose: change username (rate-limited)
- Auth: user
- Input: { new_username }
- Output: { ok:true }
- Errors: 429 too many changes, 409 taken
-
POST /api/withdrawals/request
- Purpose: request payout; validates available balance (older than 24h) and min payout
- Auth: creator
- Input: { amount, bank_account_id? }
- Output: { withdrawal_id, next_step: 'await_admin_review' }
- Errors: 400 insufficient funds, 422 min payout, 401
-
POST /api/admin/withdrawals/:id/approve
- Purpose: admin approves and triggers Paystack transfer
- Auth: admin
- Input: { withdrawal_id, note }
- Output: { paystack_transfer_id, status }
- Errors: 403, 422 insufficient_funds, 500 transfer_failed
-
GET /api/leaderboards?timeframe=daily|weekly|monthly&scope=site|campaign:{id}
- Purpose: read cached leaderboard
- Auth: public
- Output: { computed_at, entries: [ { donor_username, amount, rank } ] }
-
Webhook endpoints: /api/webhooks/paystack, /api/webhooks/dojah, /api/webhooks/mux
-
Auth:
- Use Supabase JWT for authenticated endpoints.
- Webhooks use provider signatures and shared secrets kept in env variables.
Scheduled jobs:
- Daily leaderboard compute (edge function / cron) -> writes to leaderboards_cache
- Hourly analytics ETL to produce retention-windowed reports (respect data_retention_days)
- Daily retention job to purge / archive data older than configured retention
Roles: admin, moderator, creator, donor, anonymous_guest
Permissions:
- admin: full read/write on all tables, manage settings, approve payouts, manage users
- moderator: read all, manage comments, flag campaigns, limited user actions
- creator: create campaigns, request withdrawals, view own campaign metrics
- donor: create donations, edit own profile
- anonymous_guest: initiate donations, supply donor_username (no account)
RLS policy examples (Supabase)
- Example: donations insert (allow insert if authenticated OR anonymous with captcha token)
-- Allow insert for donations for authenticated users or when provided guest_token
CREATE POLICY donations_insert ON donations
FOR INSERT
USING ( true )
WITH CHECK (
auth.role() != 'anonymous' OR (auth.role() = 'anonymous' AND current_setting('request.jwt.claims', true) IS NOT NULL)
);- Example: campaigns update (only creator or admin)
CREATE POLICY campaigns_update ON campaigns
FOR UPDATE
USING ( creator_id = auth.uid() OR auth.role() = 'admin' )
WITH CHECK ( creator_id = auth.uid() OR auth.role() = 'admin' );- Example: withdrawals select/insert (only owner or admin)
CREATE POLICY withdrawals_owner ON withdrawals
FOR SELECT USING ( owner_id = auth.uid() OR auth.role() = 'admin' );
CREATE POLICY withdrawals_insert ON withdrawals
FOR INSERT WITH CHECK ( owner_id = auth.uid() );Notes:
- Implement row-level encryption for sensitive columns using pgcrypto or Supabase-managed encryption.
Donation flow:
sequenceDiagram
participant U as User (browser)
participant FE as Frontend (Vercel)
participant API as Serverless API
participant PS as Paystack
participant DB as Supabase
U->>FE: Fill donation form (username, amount)
FE->>API: POST /donations/initiate
API->>DB: create donation record status=initiated (platform_fee calc)
API->>PS: create transaction (callback_url -> /api/webhooks/paystack)
PS-->>U: Paystack payment page (hosted)
PS-->>API: webhook payment.success
API->>PS: verify signature and reference
API->>DB: update donation status=success, confirmed_at, net_amount
API->>DB: write event to events_audit
API->>FE: optionally send real-time update (via Realtime or websocket)
Campaign creation & verification:
sequenceDiagram
participant C as Creator
participant FE as Frontend
participant API as Serverless API
participant Mux as Mux
participant Dojah as Dojah
participant DB as Supabase
C->>FE: Create campaign draft + upload images
FE->>API: POST /campaigns (stores draft, image paths)
FE->>API: POST /campaigns/:id/upload-video -> API requests Mux signed upload
FE->>Mux: Upload video directly to Mux using signed URL
Mux-->>API: webhook asset.ready
API->>DB: store mux_asset_id and metadata
C->>FE: Request publish
FE->>API: POST /campaigns/:id/publish -> triggers Dojah verification
API->>Dojah: submit KYC
Dojah-->>API: webhook verification result
API->>DB: update campaign is_verified
Admin->>API: review and set is_published=true
Payout flow:
sequenceDiagram
participant Owner
participant FE
participant API
participant DB
participant PS
participant Admin
Owner->>FE: Request withdrawal
FE->>API: POST /withdrawals/request
API->>DB: validate available_balance (donations older than 24h)
API->>DB: create withdrawal request status=requested
Admin->>API: review (checks BVN match via Paystack/DoJah)
Admin->>PS: perform BVN/account verification (or use Paystack endpoints)
Admin->>API: POST /admin/withdrawals/:id/approve
API->>PS: initiate transfer
PS-->>API: transfer result webhook
API->>DB: update withdrawal status=paid and processed_at
Pages:
- Home / campaigns feed (SSG/ISR)
- Campaign detail (SSR/ISR for freshness)
- Create campaign (authenticated creator)
- Dashboard (unified for donors & creators)
- Admin dashboard
- Auth / phone OTP flows
- Static pages (about, terms, privacy)
Key UI components (mapped to data bindings):
- CampaignCard: shows title, short_description, progress, thumbnail; binds to campaign metadata and campaign_totals
- CampaignHero: large video player (Mux Playback), images carousel (Supabase Storage URLs)
- DonationForm: input donor_username, amount, anonymous toggle -> calls /api/donations/initiate
- DonorList: recent donors (from donations table where status=success)
- Leaderboard: reads cached leaderboards via /api/leaderboards
- CreatorPanel (in Dashboard): create/edit campaigns, request withdrawals, upload media
- VerificationStatus: shows Dojah status and required actions
- AdminPanel: manage users, campaigns, payouts, settings (reads admin_settings)
Data binding notes:
- Use Supabase Realtime for live updates of donations and comments where low-latency desired.
- Use cached leaderboards endpoint for leaderboard widget.
Unit tests:
- API input validation, fee calculation, payout eligibility logic.
- RLS policy unit tests via Supabase test harness.
Integration tests:
- Donation flow with Paystack test keys; simulate webhook.
- Video upload & Mux webhook flow (mux dev keys).
- Dojah verification flows and Dojah sandbox (or mocked webhook).
- Twilio SMS OTP flow using test credentials.
End-to-end tests:
- Guest donation successful: create -> paystack hosted payment -> webhook -> DB confirmed.
- Edge cases: paystack fails, webhook missing, duplicate webhook, refund.
- Username change rate limit enforcement.
Test data and sandboxes:
- Use Paystack test mode; validate currency NGN flows.
- Use Twilio test credentials for OTP.
- Use Dojah sandbox or mocked responses.
Monitoring tests:
- Payment failure alerts to Slack/Sentry.
Environment variables (examples):
- NEXT_PUBLIC_SUPABASE_URL, NEXT_PUBLIC_SUPABASE_ANON_KEY (limited)
- SUPABASE_SERVICE_ROLE_KEY (server only)
- PAYSTACK_SECRET_KEY, PAYSTACK_PUBLIC_KEY
- DOJAH_SECRET, DOJAH_WEBHOOK_SECRET
- MUX_TOKEN_ID, MUX_TOKEN_SECRET
- TWILIO_ACCOUNT_SID, TWILIO_AUTH_TOKEN
- SENTRY_DSN
- ADMIN_EMAILS, SFTP_BACKUP_CREDENTIALS (if used)
CI/CD (Vercel):
- Branch rules: main -> production; staging branch -> staging deployment to https://staging.assist.ng
- Secrets stored in Vercel environment settings.
- Deploy preview for PRs; run unit tests and lint on PR.
Staging -> Production checklist:
- Verify webhooks configured in Paystack/Dojah/Mux with staging URLs.
- Test donation flow in Paystack test mode.
- Confirm Dojah verification succeeds in sandbox.
- Confirm Sentry and logging are active.
Backups:
- Use Supabase automated backups + periodic export of critical tables (donations, users, campaigns).
- Store backups encrypted in external storage (S3-compatible).
- Never collect or store raw card data; use Paystack hosted checkout.
- Store only masked BVN and hashed bank account numbers; prefer provider tokens.
- Encrypt sensitive data at rest using pgcrypto or managed encryption.
- Verify webhooks (signature header) for Paystack, Dojah, Mux.
- Use Supabase RLS policies; least privilege service role for server operations.
- Rate-limit OTP, donation initiation, username change, comments endpoints.
- Rotate secrets periodically; store in Vercel secret store.
- Enforce HTTPS everywhere and HSTS.
- Audit logs for admin actions and payouts.
Low-to-medium traffic assumptions:
- Concurrent users: 500 - 2,000 daily active users
- Donations/day: 200 - 1,000
- Average media: images per campaign 3, one 1–2 min video
Suggested resources:
- Vercel Pro for serverless performance and ISR.
- Supabase dedicated cluster for Postgres (start with db size 10–50GB)
- Mux starter plan for encoding and streaming.
- Twilio pay-as-you-go for SMS.
- Paystack standard merchant account.
Cost ballpark (monthly):
- Vercel: $20–$100
- Supabase: $25–$400 depending on DB plan
- Mux: $10–$200
- Twilio: depends on SMS volume
- Paystack: per-transaction fees as per their pricing
Scaling notes:
- Cache leaderboards in Supabase table and CDN; recompute by cron.
- Move heavy analytics to a separate warehouse if retention grows beyond budget.
- Donation flow verifies in Paystack test mode and DB reflects platform_fee/net_amount.
- Campaigns not public until Dojah + admin approval.
- Withdrawal eligibility (24h hold) enforced.
- Leaderboards computed daily and cached.
- RLS prevents unauthorized access.
- All secrets in server env vars only.
CREATE OR REPLACE FUNCTION calculate_fees()
RETURNS trigger AS $$
DECLARE
fee_percent numeric := (SELECT platform_fee_percent FROM admin_settings ORDER BY id DESC LIMIT 1);
BEGIN
NEW.platform_fee := round((NEW.amount * fee_percent / 100)::numeric,2);
NEW.net_amount := NEW.amount - NEW.platform_fee;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_calculate_fees
BEFORE INSERT ON donations
FOR EACH ROW EXECUTE FUNCTION calculate_fees();Deliverable saved to plan.md.
Next steps:
- Review plan and request approval to switch to code mode to create migrations and API stubs.
End of plan.