Precedent logo

VWWL

Client Table Schema

Here's how our client database is structured - I've mapped out all the relationships.

Back to Home

Schema Visualization

Clerk User Dataclerk_user_idemailfirst_namelast_nameprofile_image_urlauth_providerSharerssharer_id (PK)clerk_user_id (FK)nameemailreferral_codecreated_atDonorsdonor_id (PK)clerk_user_id (FK)nameemailcontact_infocreated_atTransactionstransaction_id (PK)donor_id (FK)sharer_id (FK)amountcurrencystatuspayment_dateTaxCertificatescertificate_id (PK)transaction_id (FK)certificate_numberissue_datefile_path

Schema Details

I've designed this database to track donations, fundraisers, and tax certificates (Section 18A). We're using Clerk for user authentication.

  • Clerk User Data: Our external auth system
  • Sharers: People who create and share donation links
  • Donors: Folks making the actual donations
  • Transactions: Each donation/payment attempt
  • TaxCertificates: The Section 18A receipts we issue

Legend

External Auth (Clerk)
Primary Tables (Sharers, Donors)
Junction Tables (Transactions)
Lookup Tables (TaxCertificates)
Foreign Key Relationship

Clerk User Integration

When someone logs in through Clerk, the following data is available to us:

User Identity Data

  • id: Unique Clerk user ID
  • firstName, lastName: Their name
  • emailAddresses: Their verified emails
  • imageUrl: Profile pic
  • username: Username (if they set one)
  • externalId: ID from Google/Apple/etc.

Authentication Data

  • sessionId: Current session ID
  • session.userId: Who's logged in
  • session.lastActiveAt: When they last did something
  • session.expireAt: When to kick them out
  • getToken(): JWT for our API calls
  • signOut(): How we log them out

User Data Flow

  1. User logs in (signup, signin, Google login, etc.)
  2. Clerk gives us their info and session
  3. We save their clerk_user_id in our database
  4. For API calls, we include their Clerk token
  5. Our backend checks the token and finds them in our DB

Implementation Note

I've set it up so one person can be both a Sharer and a Donor. The clerk_user_id foreign key in both tables lets us connect the same user to different roles.

System Architecture

Here's how I've structured the tech stack:

Frontend (Vercel)

  • Next.js/React for the UI
  • Auto-deploys when we push code
  • Our domain points to it via Cloudflare
  • All the user-facing stuff

User Management (Clerk)

  • Handles all the login stuff
  • Login/signup pages
  • "Login with Google/Apple" buttons
  • Keeps track of who's logged in

Backend (AWS)

  • Lambda functions for the heavy lifting
  • Processing payments
  • Creating tax certificates
  • Crunching numbers for reports

Database & Storage

  • RDS or DynamoDB (still deciding)
  • S3 for storing files and certificates
  • Locked-down API endpoints
  • PayFast for handling money