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_providerUsersid (clerk_user_id)created_atupdated_atOrganizationsid (PK)namedescriptioncreated_atupdated_atUserOrganizationid (PK)user_id (FK)organization_id (FK)rolecreated_atupdated_atTransactionsid (PK)user_id (FK)organization_id (FK)amountdescriptionstatuscreated_atTaxCertificatesid (PK)transaction_id (FK)certificate_numberissue_datefile_path

Schema Details

I've designed this database with a hybrid approach - using Clerk for authentication and our own database for business data.

  • Clerk User Data: External auth system (managed by Clerk)
  • Users: Our internal reference to Clerk users
  • Organizations: Groups that users can belong to
  • UserOrganization: Many-to-many relationship table
  • Transactions: Financial records and activities
  • TaxCertificates: The Section 18A receipts we issue

Legend

External Auth (Clerk)
User Reference Table
Primary Entity Tables
Junction & Transaction Tables
Document Tables
Foreign Key Relationship

Hybrid Authentication Approach

We're using a hybrid approach that combines Clerk for authentication with our own database for business data:

Clerk Handles

  • Authentication: Login, signup, sessions
  • Identity: User profiles, emails, names
  • Security: MFA, session management
  • Social Login: Google, Apple, etc.
  • User Management: Admin tools, blocking

Our Database Handles

  • Business Data: Organizations, transactions
  • Relationships: User-to-organization mappings
  • Financial Records: Donations, payments
  • Documents: Tax certificates, receipts
  • Business Logic: Complex queries, reporting

Integration Flow

  1. User authenticates through Clerk
  2. We create/reference a User record in our database with Clerk's userId
  3. All business data references this User record
  4. For user profile info, we query Clerk's API
  5. For business data and relationships, we query our database

Implementation Note

This approach gives us the best of both worlds - we don't have to build authentication, but we maintain full control over our business data and relationships. The User table serves as a bridge between Clerk's authentication and our business data.

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

  • PostgreSQL for relational data
  • S3 for storing files and certificates
  • Locked-down API endpoints
  • PayFast for handling money