Prisma Setup - ThriveSend B2B2G

Prisma Setup - ThriveSend B2B2G

Last Updated: January 26, 2026 Status: Production Developer Guide: Database Layer

---

Overview

ThriveSend B2B2G uses Prisma ORM as the database layer with PostgreSQL 16. This guide covers the complete setup, configuration, and best practices for working with the B2B2G database.

Key Features:

• 28 database models for B2B2G operations
• Multi-tenant architecture (organization-scoped)
• POPIA compliance with audit logging
• Security clearance integration
• Government and business sector support

---

Database Architecture

Technology Stack

Component	Version	Purpose
PostgreSQL	16	Primary database
Prisma Client	6.16.2	ORM and query builder
Prisma Migrate	Latest	Schema migrations
Prisma Studio	Latest	Database GUI

B2B2G Database Structure

ThriveSend B2B2G Database
├── Core Entities (4 models)
│   ├── Organization (Service Providers)
│   ├── Client (Government/Business)
│   ├── OrganizationUser (Multi-role users)
│   └── ClientAccess (Access control)
│
├── Content & Campaigns (5 models)
│   ├── Campaign
│   ├── Content
│   ├── MediaAsset
│   ├── ContentApproval
│   └── ContentVersion
│
├── POPIA Compliance (7 models)
│   ├── AuditLog
│   ├── DataProcessingRecord
│   ├── Invitation
│   ├── PopiaConsentRecord
│   ├── DataSubjectRequest
│   ├── EmailTracking
│   └── InvitationAuditLog
│
├── Analytics & Templates (3 models)
│   ├── Analytics
│   ├── Template
│   └── Project
│
├── Billing & Integrations (7 models)
│   ├── Subscription
│   ├── Invoice
│   ├── ApiKey
│   ├── Webhook
│   ├── WebhookDelivery
│   ├── IpWhitelist
│   └── NotificationPreferences
│
└── Configuration (2 models)
    ├── ComplianceConfiguration
    └── SecurityConfiguration

---

Installation and Setup

Step 1: Install Dependencies

# Install Prisma CLI and Client
pnpm add @prisma/client
pnpm add -D prisma

Step 2: Configure Database Connection

Create .env.local in the project root:

# PostgreSQL Connection String
# Format: postgresql://USER:PASSWORD@HOST:PORT/DATABASE
DATABASE_URL="postgresql://thrivesend:devpassword@localhost:5432/thrivesend_dev"

# Production Example (from production server)
# DATABASE_URL="postgresql://thrivesend:ThriveSend2025Secure@localhost:5432/thrivesend_b2b2g"

POPIA Compliance Note:

• Production database MUST be hosted in South Africa (ZA data residency)
• All data processing must remain within SA borders
• Connection strings should use SSL in production

Step 3: Verify Prisma Configuration

Check prisma/schema.prisma configuration:

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

Step 4: Generate Prisma Client

# Generate TypeScript client from schema
npx prisma generate

What This Does:

• Reads prisma/schema.prisma
• Generates type-safe TypeScript client
• Creates models in node_modules/@prisma/client
• Provides IntelliSense for all models and queries

---

Prisma Client Configuration

Database Client Singleton

The application uses a singleton pattern for the Prisma client in src/lib/db.ts:

src/lib/db.ts

import { PrismaClient } from '@prisma/client'

// Global Prisma instance for development hot reloading
const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined
}

// Enhanced Prisma client with B2B2G extensions
export const prisma =
  globalForPrisma.prisma ??
  new PrismaClient({
    log: ['query', 'error', 'warn'],
    // Enable query logging for POPIA audit compliance
    datasources: {
      db: {
        url: process.env.DATABASE_URL,
      },
    },
  })

// Prevent multiple instances in development
if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = prisma

Key Features:

• Singleton Pattern: Prevents multiple client instances (important for connection pooling)
• Query Logging: Enabled for POPIA audit compliance
• Development Hot Reload: Preserves client across Next.js hot reloads
• Type Safety: Full TypeScript support

Import Pattern

Always import the prisma client from @/lib/db:

// ✅ CORRECT - Import from lib/db
import { prisma } from '@/lib/db'

// ❌ WRONG - Never import directly
import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient() // Creates multiple instances!

---

Database Health Checks

Health Check Function

Use the built-in health check for monitoring:

import { checkDatabaseHealth } from '@/lib/db'

// In API route or monitoring script
const isHealthy = await checkDatabaseHealth()
if (!isHealthy) {
  console.error('Database connection failed')
}

Implementation:

src/lib/db.ts

export async function checkDatabaseHealth(): Promise<boolean> {
  try {
    await prisma.$queryRaw`SELECT 1`
    return true
  } catch (error) {
    console.error('Database health check failed:', error)
    return false
  }
}

Health Check Endpoint

Production health check at /api/health:

src/app/api/health/route.ts

import { NextResponse } from 'next/server'
import { checkDatabaseHealth } from '@/lib/db'

export async function GET() {
  const dbHealthy = await checkDatabaseHealth()

  if (!dbHealthy) {
    return NextResponse.json(
      { status: 'unhealthy', database: 'disconnected' },
      { status: 503 }
    )
  }

  return NextResponse.json({
    status: 'healthy',
    database: 'connected',
    timestamp: new Date().toISOString()
  })
}

Usage:

# Check production health
curl http://localhost:3005/api/health

# Expected response
{
  "status": "healthy",
  "database": "connected",
  "timestamp": "2026-01-26T10:00:00.000Z"
}

---

Prisma Studio

Prisma Studio provides a GUI for database management.

Launch Prisma Studio

# Start Prisma Studio (opens at http://localhost:5555)
npx prisma studio

Features:

• Browse all 28 models
• View and edit records
• Filter and search data
• Test queries visually
• View relationships

POPIA Warning:

• Prisma Studio shows ALL data including personal information
• Only use in secure development environments
• Never expose Prisma Studio to public internet
• Use VPN when accessing from remote servers

---

Environment Configuration

Development Environment

# .env.local (development)
DATABASE_URL="postgresql://thrivesend:devpassword@localhost:5432/thrivesend_dev"
NODE_ENV="development"

Production Environment

# .env.local (production - on server only)
DATABASE_URL="postgresql://thrivesend:ThriveSend2025Secure@localhost:5432/thrivesend_b2b2g"
NODE_ENV="production"

# POPIA Compliance
DATA_RESIDENCY="ZA"
AUDIT_LOGGING_ENABLED="true"

Security Best Practices:

• ✅ Never commit .env.local to git (already in .gitignore)
• ✅ Use different credentials for dev/staging/production
• ✅ Rotate production passwords regularly
• ✅ Use connection pooling in production (PgBouncer recommended)
• ✅ Enable SSL for production connections

---

Connection Pooling

Default Connection Pool

Prisma uses a default connection pool:

// Default connection pool (automatic)
- Min connections: 2
- Max connections: 10 (depends on DATABASE_URL query params)

Custom Pool Configuration

For production, configure connection pooling in DATABASE_URL:

# With connection pool limits
DATABASE_URL="postgresql://user:password@localhost:5432/db?connection_limit=20&pool_timeout=10"

PgBouncer (Production Recommendation)

For high-traffic production environments, use PgBouncer:

# Install PgBouncer
sudo apt install pgbouncer

# Configure pgbouncer.ini
[databases]
thrivesend_b2b2g = host=localhost port=5432 dbname=thrivesend_b2b2g

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
pool_mode = transaction
max_client_conn = 100
default_pool_size = 20

# Update DATABASE_URL to use PgBouncer
DATABASE_URL="postgresql://thrivesend:password@localhost:6432/thrivesend_b2b2g?pgbouncer=true"

---

Troubleshooting

Issue: “Can’t reach database server”

Symptoms:

Error: P1001: Can't reach database server at localhost:5432

Solutions:

1. Check PostgreSQL is running:

   # Check PostgreSQL status
   sudo systemctl status postgresql
   
   # Start PostgreSQL
   sudo systemctl start postgresql

2. Verify connection string:

   # Check DATABASE_URL is set
   echo $DATABASE_URL
   
   # Test connection with psql
   psql "postgresql://thrivesend:devpassword@localhost:5432/thrivesend_dev"

3. Check PostgreSQL is listening:

   # Check listening ports
   sudo netstat -tulpn | grep 5432

Issue: “Prisma Client not generated”

Symptoms:

Error: @prisma/client did not initialize yet

Solution:

# Regenerate Prisma Client
npx prisma generate

# Restart dev server
pnpm dev

Issue: “Schema out of sync”

Symptoms:

Error: The database schema is not in sync with your Prisma schema

Solution:

# Apply pending migrations
npx prisma migrate dev

# Or force sync (development only)
npx prisma db push

Issue: Connection Pool Exhausted

Symptoms:

Error: Timed out fetching a new connection from the connection pool

Solutions:

1. Increase connection pool limit:

   DATABASE_URL="postgresql://...?connection_limit=20"

2. Ensure proper disconnection:

   // In API routes - Prisma auto-manages connections
   // Don't manually disconnect unless in serverless
   
   // In serverless functions
   await prisma.$disconnect()

3. Check for connection leaks:

   // ❌ BAD - Creates new instance each time
   const prisma = new PrismaClient()
   
   // ✅ GOOD - Use singleton
   import { prisma } from '@/lib/db'

---

Database Utilities

Get Database Info

import { getDatabaseInfo } from '@/lib/db'

const info = getDatabaseInfo()
console.log(info)
// {
//   url: '***configured***',
//   client: 'PrismaClient',
//   version: '6.16.2',
//   features: [
//     'B2B2G Multi-tenancy',
//     'POPIA Compliance',
//     'Security Clearance',
//     'Audit Logging'
//   ]
// }

Graceful Shutdown

import { disconnectDatabase } from '@/lib/db'

// In shutdown handlers
process.on('SIGINT', async () => {
  console.log('Disconnecting database...')
  await disconnectDatabase()
  process.exit(0)
})

---

POPIA Compliance Features

Data Residency Enforcement

All models include dataResidency field defaulting to “ZA”:

model Organization {
  // ...
  country       String @default("ZA")
  dataResidency String @default("ZA") // Enforce SA data residency
}

model Client {
  // ...
  dataResidency String @default("ZA")
}

Audit Logging

All data operations are logged via AuditLog model:

model AuditLog {
  id             String   @id @default(cuid())
  organizationId String?
  userId         String?
  action         String   // CREATE, READ, UPDATE, DELETE
  resourceType   String   // Client, Campaign, Content, etc.
  resourceId     String?
  details        Json?    // Operation details
  ipAddress      String?
  userAgent      String?
  timestamp      DateTime @default(now())
  // ...
}

POPIA Consent Tracking

model PopiaConsentRecord {
  id                      String    @id @default(cuid())
  email                   String
  organizationId          String
  dataProcessingConsent   Boolean
  marketingConsent        Boolean   @default(false)
  analyticsConsent        Boolean   @default(false)
  consentTimestamp        DateTime
  withdrawnAt             DateTime?
  ipAddress               String
  userAgent               String
  // ...
}

---

Best Practices

✅ DO

1. Always use the singleton client:

   import { prisma } from '@/lib/db'

2. Use transactions for multi-step operations:

   await prisma.$transaction([
     prisma.client.create({ data: clientData }),
     prisma.auditLog.create({ data: auditData })
   ])

3. Include organization scoping in queries:

   const clients = await prisma.client.findMany({
     where: { organizationId } // Always scope by organization
   })

4. Log database errors for POPIA compliance:

   try {
     await prisma.client.create({ data })
   } catch (error) {
     console.error('[DB_ERROR]', error)
     // Log to audit trail
   }

❌ DON’T

1. Create new PrismaClient instances:

   // ❌ NEVER do this
   const prisma = new PrismaClient()

2. Forget to scope queries by organization:

   // ❌ Returns data from ALL organizations
   const allClients = await prisma.client.findMany()
   
   // ✅ Properly scoped
   const clients = await prisma.client.findMany({
     where: { organizationId }
   })

3. Expose database errors to users:

   // ❌ Exposes internal details
   return NextResponse.json({ error: error.message })
   
   // ✅ Generic user-facing error
   return NextResponse.json({ error: 'Failed to create client' })

4. Skip audit logging:

   // ❌ No audit trail
   await prisma.client.delete({ where: { id } })
   
   // ✅ With audit logging
   await prisma.client.delete({ where: { id } })
   await auditLog('CLIENT_DELETED', id, userId)

---

Next Steps

• Schema Management → - Learn about B2B2G schema design
• Migrations → - Creating and running migrations
• Querying Data → - Query patterns and best practices

---

Related Documentation:

• Service Architecture - Using Prisma in services
• API Development - Prisma in API routes

---

Last Updated: January 26, 2026