Querying Data with Prisma - ThriveSend B2B2G

Querying Data with Prisma - ThriveSend B2B2G

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

---

Overview

This guide covers database querying patterns for ThriveSend B2B2G using Prisma Client. Learn how to query B2B2G data with proper multi-tenant isolation, POPIA compliance, and type safety.

Key Principles:

• Always scope queries by organization (multi-tenant isolation)
• Include POPIA audit logging for data access
• Use TypeScript for type-safe queries
• Optimize with proper relations and indexing
• Handle errors gracefully

---

Basic Query Patterns

Finding Records

Find Many (List All)

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

// Get all clients for an organization
const clients = await prisma.client.findMany({
  where: {
    organizationId: 'org_abc123' // CRITICAL: Always scope by organization
  }
})

// With filtering
const activeClients = await prisma.client.findMany({
  where: {
    organizationId: 'org_abc123',
    status: 'ACTIVE' // Additional filter
  }
})

// With sorting
const sortedClients = await prisma.client.findMany({
  where: { organizationId: 'org_abc123' },
  orderBy: {
    createdAt: 'desc' // Newest first
  }
})

// With pagination
const paginatedClients = await prisma.client.findMany({
  where: { organizationId: 'org_abc123' },
  skip: 0, // Offset
  take: 10, // Limit
  orderBy: { createdAt: 'desc' }
})

Find Unique (Single Record)

// Find by ID
const client = await prisma.client.findUnique({
  where: { id: 'client_123' }
})

// IMPORTANT: Verify organization ownership
if (client && client.organizationId !== userOrgId) {
  throw new Error('Unauthorized: Client belongs to different organization')
}

// Find by unique field
const organization = await prisma.organization.findUnique({
  where: { clerkOrganizationId: 'org_clerk123' }
})

Find First (First Match)

// Get first matching client
const firstClient = await prisma.client.findFirst({
  where: {
    organizationId: 'org_abc123',
    type: 'GOVERNMENT' // Sector filter
  },
  orderBy: {
    createdAt: 'desc'
  }
})

Creating Records

Create Single Record

// Create a new client with POPIA compliance
const newClient = await prisma.client.create({
  data: {
    organizationId: 'org_abc123',
    name: 'City of Cape Town',
    slug: 'city-of-cape-town',
    type: 'LOCAL_MUNICIPALITY',
    status: 'ACTIVE',
    contactEmail: 'contact@capetown.gov.za',
    contactPhone: '+27-21-400-1111',
    dataResidency: 'ZA', // POPIA: South African data residency
    popiaConsent: true,
    consentDate: new Date()
  }
})

// Create with nested relations
const campaign = await prisma.campaign.create({
  data: {
    organizationId: 'org_abc123',
    clientId: 'client_123',
    name: 'Water Conservation Campaign',
    slug: 'water-conservation-2026',
    type: 'PUBLIC_AWARENESS',
    status: 'DRAFT',
    sector: 'GOVERNMENT',
    governmentLevel: 'MUNICIPAL',
    objectives: ['Reduce water consumption', 'Educate citizens'],
    budget: 50000
  }
})

Create Many Records

// Bulk insert
const clients = await prisma.client.createMany({
  data: [
    {
      organizationId: 'org_abc123',
      name: 'Client 1',
      slug: 'client-1',
      type: 'BUSINESS',
      contactEmail: 'client1@example.com',
      dataResidency: 'ZA'
    },
    {
      organizationId: 'org_abc123',
      name: 'Client 2',
      slug: 'client-2',
      type: 'BUSINESS',
      contactEmail: 'client2@example.com',
      dataResidency: 'ZA'
    }
  ],
  skipDuplicates: true // Skip if unique constraint fails
})

console.log(`Created ${clients.count} clients`)

Updating Records

Update Single Record

// Update client information
const updatedClient = await prisma.client.update({
  where: { id: 'client_123' },
  data: {
    contactEmail: 'newemail@example.com',
    contactPhone: '+27-11-555-0123',
    updatedAt: new Date()
  }
})

// Partial update (only specified fields)
const client = await prisma.client.update({
  where: { id: 'client_123' },
  data: {
    status: 'INACTIVE'
  }
})

Update Many Records

// Update multiple records matching criteria
const result = await prisma.client.updateMany({
  where: {
    organizationId: 'org_abc123',
    status: 'PENDING'
  },
  data: {
    status: 'ACTIVE',
    updatedAt: new Date()
  }
})

console.log(`Updated ${result.count} clients`)

Upsert (Update or Create)

// Update if exists, create if not
const client = await prisma.client.upsert({
  where: { id: 'client_123' },
  update: {
    name: 'Updated Name',
    contactEmail: 'updated@example.com'
  },
  create: {
    id: 'client_123',
    organizationId: 'org_abc123',
    name: 'New Client',
    slug: 'new-client',
    type: 'BUSINESS',
    contactEmail: 'new@example.com',
    dataResidency: 'ZA'
  }
})

Deleting Records

Delete Single Record

// Delete a client (soft delete recommended)
const deletedClient = await prisma.client.delete({
  where: { id: 'client_123' }
})

// Soft delete (update status instead)
const softDeleted = await prisma.client.update({
  where: { id: 'client_123' },
  data: {
    status: 'DELETED',
    updatedAt: new Date()
  }
})

Delete Many Records

// Delete multiple records
const result = await prisma.client.deleteMany({
  where: {
    organizationId: 'org_abc123',
    status: 'INACTIVE',
    createdAt: {
      lt: new Date('2025-01-01') // Before 2025
    }
  }
})

console.log(`Deleted ${result.count} clients`)

---

Advanced Query Patterns

Relations and Includes

// Include related data
const client = await prisma.client.findUnique({
  where: { id: 'client_123' },
  include: {
    organization: true, // Include organization details
    campaigns: true, // Include all campaigns
    analytics: true // Include analytics records
  }
})

// Selective include with filtering
const clientWithActiveCampaigns = await prisma.client.findUnique({
  where: { id: 'client_123' },
  include: {
    campaigns: {
      where: { status: 'ACTIVE' },
      orderBy: { createdAt: 'desc' },
      take: 5 // Only 5 most recent
    }
  }
})

// Nested includes
const organization = await prisma.organization.findUnique({
  where: { clerkOrganizationId: 'org_clerk123' },
  include: {
    clients: {
      include: {
        campaigns: {
          where: { status: 'ACTIVE' }
        },
        analytics: true
      }
    }
  }
})

Select Specific Fields

// Only fetch specific fields (performance optimization)
const clients = await prisma.client.findMany({
  where: { organizationId: 'org_abc123' },
  select: {
    id: true,
    name: true,
    contactEmail: true,
    status: true
    // Other fields excluded
  }
})

// Combine select with include
const client = await prisma.client.findUnique({
  where: { id: 'client_123' },
  select: {
    id: true,
    name: true,
    campaigns: {
      select: {
        id: true,
        name: true,
        status: true
      }
    }
  }
})

Filtering and Conditions

Basic Filters

// Equality
const clients = await prisma.client.findMany({
  where: {
    organizationId: 'org_abc123',
    type: 'GOVERNMENT',
    status: 'ACTIVE'
  }
})

// Not equal
const nonGovClients = await prisma.client.findMany({
  where: {
    organizationId: 'org_abc123',
    type: { not: 'GOVERNMENT' }
  }
})

// In array
const specificClients = await prisma.client.findMany({
  where: {
    id: {
      in: ['client_1', 'client_2', 'client_3']
    }
  }
})

// Contains (case-sensitive)
const capeTownClients = await prisma.client.findMany({
  where: {
    name: { contains: 'Cape Town' }
  }
})

// Case-insensitive contains
const searchClients = await prisma.client.findMany({
  where: {
    name: { contains: 'cape', mode: 'insensitive' }
  }
})

Comparison Operators

// Date range query
const recentCampaigns = await prisma.campaign.findMany({
  where: {
    organizationId: 'org_abc123',
    createdAt: {
      gte: new Date('2026-01-01'), // Greater than or equal
      lt: new Date('2026-02-01') // Less than
    }
  }
})

// Numeric comparison
const highBudgetCampaigns = await prisma.campaign.findMany({
  where: {
    budget: {
      gte: 100000 // Budget >= 100,000
    }
  }
})

Logical Operators (AND, OR, NOT)

// AND (default behavior)
const clients = await prisma.client.findMany({
  where: {
    AND: [
      { organizationId: 'org_abc123' },
      { status: 'ACTIVE' },
      { type: 'GOVERNMENT' }
    ]
  }
})

// OR
const clients = await prisma.client.findMany({
  where: {
    organizationId: 'org_abc123',
    OR: [
      { type: 'GOVERNMENT' },
      { type: 'BUSINESS' }
    ]
  }
})

// NOT
const clients = await prisma.client.findMany({
  where: {
    organizationId: 'org_abc123',
    NOT: {
      status: 'DELETED'
    }
  }
})

// Complex combination
const complexQuery = await prisma.client.findMany({
  where: {
    organizationId: 'org_abc123',
    AND: [
      { status: 'ACTIVE' },
      {
        OR: [
          { type: 'GOVERNMENT' },
          { type: 'BUSINESS' }
        ]
      }
    ],
    NOT: {
      popiaConsent: false // Must have POPIA consent
    }
  }
})

---

B2B2G Query Patterns

Multi-Tenant Isolation

CRITICAL: Always scope queries by organization for B2B2G multi-tenancy.

// ✅ CORRECT: Organization-scoped query
const clients = await prisma.client.findMany({
  where: {
    organizationId: userOrgId // From Clerk authentication
  }
})

// ❌ WRONG: No organization scoping (security risk!)
const allClients = await prisma.client.findMany()

Real-World Example: Organization Lookup

From src/app/api/analytics/dashboard/route.ts:

// Resolve Clerk org ID to database org ID
const organization = await prisma.organization.findUnique({
  where: { clerkOrganizationId: orgId }
})

if (!organization) {
  return NextResponse.json(
    { error: 'Organization not found' },
    { status: 404 }
  )
}

// Use the database organization ID for all queries
const effectiveOrgId = organization.id

// Now query with proper organization scoping
const clients = await prisma.client.findMany({
  where: { organizationId: effectiveOrgId }
})

Sector-Specific Queries

// Get government clients only
const govClients = await prisma.client.findMany({
  where: {
    organizationId: 'org_abc123',
    type: {
      in: [
        'LOCAL_MUNICIPALITY',
        'PROVINCIAL_GOVERNMENT',
        'NATIONAL_GOVERNMENT'
      ]
    }
  }
})

// Get government campaigns
const govCampaigns = await prisma.campaign.findMany({
  where: {
    organizationId: 'org_abc123',
    sector: 'GOVERNMENT'
  }
})

// Get business clients
const businessClients = await prisma.client.findMany({
  where: {
    organizationId: 'org_abc123',
    type: {
      in: [
        'SMALL_BUSINESS',
        'MEDIUM_BUSINESS',
        'LARGE_CORPORATION',
        'STARTUP'
      ]
    }
  }
})

Security Clearance Queries

// Get users with government security clearance
const clearanceUsers = await prisma.organizationUser.findMany({
  where: {
    organizationId: 'org_abc123',
    securityClearance: {
      in: ['BASIC', 'ENHANCED', 'CONFIDENTIAL']
    },
    clearanceExpiry: {
      gte: new Date() // Not expired
    }
  }
})

// Get campaigns requiring specific clearance
const confidentialCampaigns = await prisma.campaign.findMany({
  where: {
    organizationId: 'org_abc123',
    securityClearance: 'CONFIDENTIAL',
    sector: 'GOVERNMENT'
  }
})

---

POPIA Compliance Patterns

Audit Logging for Data Access

import { auditLog } from '@/lib/audit'

// Query data
const clients = await prisma.client.findMany({
  where: { organizationId: 'org_abc123' }
})

// POPIA audit log
await auditLog({
  userId: 'user_123',
  orgId: 'org_abc123',
  action: 'CLIENTS_ACCESSED',
  resourceType: 'CLIENT',
  resourceId: 'multiple',
  metadata: {
    count: clients.length,
    timestamp: new Date().toISOString()
  }
})

Data Subject Requests (POPIA Rights)

// Get all personal data for a data subject (POPIA right to access)
const userEmail = 'john@example.com'

const personalData = await prisma.$transaction([
  // User records
  prisma.organizationUser.findMany({
    where: { email: userEmail }
  }),

  // Consent records
  prisma.popiaConsentRecord.findMany({
    where: { email: userEmail }
  }),

  // Invitation records
  prisma.invitation.findMany({
    where: { email: userEmail }
  }),

  // Audit logs
  prisma.auditLog.findMany({
    where: {
      userId: { contains: userEmail }
    }
  })
])

// Return consolidated personal data
return {
  users: personalData[0],
  consents: personalData[1],
  invitations: personalData[2],
  auditLogs: personalData[3]
}

POPIA Consent Tracking

// Check if user has given POPIA consent
const consent = await prisma.popiaConsentRecord.findFirst({
  where: {
    email: 'user@example.com',
    organizationId: 'org_abc123',
    dataProcessingConsent: true,
    withdrawnAt: null // Not withdrawn
  }
})

if (!consent) {
  throw new Error('User has not given POPIA consent for data processing')
}

// Track consent withdrawal
await prisma.popiaConsentRecord.update({
  where: { id: consent.id },
  data: {
    withdrawnAt: new Date(),
    withdrawalReason: 'User requested data deletion'
  }
})

---

Transactions

Using Transactions

// Atomic operation: Create client + audit log
const result = await prisma.$transaction(async (tx) => {
  // Create client
  const client = await tx.client.create({
    data: {
      organizationId: 'org_abc123',
      name: 'New Client',
      slug: 'new-client',
      type: 'BUSINESS',
      contactEmail: 'client@example.com',
      dataResidency: 'ZA'
    }
  })

  // Create audit log
  await tx.auditLog.create({
    data: {
      organizationId: 'org_abc123',
      userId: 'user_123',
      action: 'CLIENT_CREATED',
      resourceType: 'Client',
      resourceId: client.id,
      details: {
        clientName: client.name,
        clientType: client.type
      },
      timestamp: new Date()
    }
  })

  return client
})

// If any operation fails, entire transaction rolls back

Transaction Array Syntax

// Simpler syntax for independent operations
const [client, campaign] = await prisma.$transaction([
  prisma.client.create({ data: clientData }),
  prisma.campaign.create({ data: campaignData })
])

---

Aggregation and Analytics

Count Records

// Count all clients
const clientCount = await prisma.client.count({
  where: { organizationId: 'org_abc123' }
})

// Count by status
const activeCount = await prisma.client.count({
  where: {
    organizationId: 'org_abc123',
    status: 'ACTIVE'
  }
})

Aggregate Functions

// Sum, avg, min, max
const campaignStats = await prisma.campaign.aggregate({
  where: { organizationId: 'org_abc123' },
  _sum: {
    budget: true,
    spent: true
  },
  _avg: {
    engagement: true
  },
  _max: {
    reach: true
  },
  _count: {
    id: true
  }
})

console.log('Total budget:', campaignStats._sum.budget)
console.log('Average engagement:', campaignStats._avg.engagement)
console.log('Max reach:', campaignStats._max.reach)
console.log('Total campaigns:', campaignStats._count.id)

Group By

// Group campaigns by sector
const sectorStats = await prisma.campaign.groupBy({
  by: ['sector'],
  where: { organizationId: 'org_abc123' },
  _count: {
    id: true
  },
  _sum: {
    budget: true,
    reach: true
  }
})

// Result:
// [
//   { sector: 'GOVERNMENT', _count: { id: 15 }, _sum: { budget: 500000, reach: 1200000 } },
//   { sector: 'BUSINESS', _count: { id: 22 }, _sum: { budget: 350000, reach: 800000 } }
// ]

---

Raw Queries

Raw SQL Queries

// Use raw SQL when Prisma query builder is insufficient
const result = await prisma.$queryRaw`
  SELECT c.*, COUNT(ca.id) as campaign_count
  FROM clients c
  LEFT JOIN campaigns ca ON ca."clientId" = c.id
  WHERE c."organizationId" = ${orgId}
  GROUP BY c.id
  ORDER BY campaign_count DESC
  LIMIT 10
`

// Execute raw SQL (no return value)
await prisma.$executeRaw`
  UPDATE campaigns
  SET status = 'ARCHIVED'
  WHERE "organizationId" = ${orgId}
    AND "endDate" < NOW() - INTERVAL '90 days'
`

Security Warning:

• Always use parameterized queries (template literals)
• Never concatenate user input into SQL strings

// ❌ DANGEROUS: SQL injection risk
const query = `SELECT * FROM clients WHERE name = '${userInput}'`

// ✅ SAFE: Parameterized query
const result = await prisma.$queryRaw`
  SELECT * FROM clients WHERE name = ${userInput}
`

---

Performance Optimization

Indexing

Indexes are defined in the Prisma schema:

model Client {
  id             String @id @default(cuid())
  organizationId String
  name           String
  contactEmail   String

  @@index([organizationId]) // Index for fast lookups
  @@index([contactEmail])
  @@unique([organizationId, slug])
}

Query Optimization Tips

// ❌ N+1 query problem
const clients = await prisma.client.findMany()
for (const client of clients) {
  const campaigns = await prisma.campaign.findMany({
    where: { clientId: client.id }
  }) // Executes N additional queries!
}

// ✅ Optimized with include
const clients = await prisma.client.findMany({
  include: {
    campaigns: true // Single query with JOIN
  }
})

// ✅ Select only needed fields
const clients = await prisma.client.findMany({
  select: {
    id: true,
    name: true,
    contactEmail: true
    // Exclude unnecessary fields for better performance
  }
})

---

Error Handling

import { Prisma } from '@prisma/client'

try {
  const client = await prisma.client.create({
    data: clientData
  })
} catch (error) {
  // Handle specific Prisma errors
  if (error instanceof Prisma.PrismaClientKnownRequestError) {
    if (error.code === 'P2002') {
      // Unique constraint violation
      console.error('Client with this email already exists')
      return NextResponse.json(
        { error: 'Client already exists' },
        { status: 409 }
      )
    }

    if (error.code === 'P2025') {
      // Record not found
      return NextResponse.json(
        { error: 'Client not found' },
        { status: 404 }
      )
    }
  }

  // Generic error
  console.error('[DB_ERROR]', error)
  return NextResponse.json(
    { error: 'Database error' },
    { status: 500 }
  )
}

Common Error Codes:

• P2002: Unique constraint failed
• P2025: Record not found
• P2003: Foreign key constraint failed
• P2014: Relation violation

---

Best Practices Summary

✅ DO

1. Always scope by organization:

   where: { organizationId }

2. Use transactions for multi-step operations:

   await prisma.$transaction([...])

3. Include POPIA audit logging:

   await auditLog({ ... })

4. Use type-safe queries:

   const client: Client = await prisma.client.findUnique(...)

5. Optimize with select and include:

   select: { id: true, name: true }

❌ DON’T

1. Query without organization scoping:

   // ❌ Security risk
   await prisma.client.findMany()

2. Use raw SQL without parameterization:

   // ❌ SQL injection risk
   `SELECT * FROM clients WHERE name = '${userInput}'`

3. Ignore POPIA compliance:

   // ❌ No audit trail
   await prisma.client.delete({ where: { id } })

---

Next Steps

• ← Migrations - Database migration patterns
• Schema Management → - B2B2G schema design
• Service Architecture - Using Prisma in services

---

Related Documentation:

• API Development - Prisma in API routes

---

Last Updated: January 26, 2026