Multi-Tenant Access Verification Middleware

Version: 1.0.0 Created: 2025-12-13 Status: Production Ready Related: P2-8: Add Prisma middleware for multi-tenant access verification

Overview

The multi-tenant middleware is a Prisma Client Extension that automatically enforces tenant isolation by injecting portalId filters into all database queries. This ensures that users can only access data belonging to their authorized portals, preventing cross-tenant data leaks.

Features

• Automatic Portal ID Injection: Transparently adds portalId filters to all read operations
• Write Operation Verification: Validates that create/update operations target the correct portal
• Platform Admin Bypass: Allows platform admins to access all portals
• Request Context Integration: Uses AsyncLocalStorage for request-scoped portal tracking
• Zero Code Changes: Works transparently with existing Prisma queries
• Comprehensive Coverage: Handles all Prisma operations (find, create, update, delete, aggregate, etc.)

Architecture

How It Works

1. Request Context Setup: Portal ID is stored in RequestContext (AsyncLocalStorage) by API middleware
2. Query Interception: Prisma extension intercepts all queries to portal-scoped models
3. Filter Injection: Middleware automatically adds portalId filter to query arguments
4. Verification: For write operations, ensures data belongs to the correct portal
5. Bypass Logic: Platform admins can bypass filtering by setting isPlatformAdmin in request baggage

Request Flow

API Request
    ↓
Request Middleware (sets RequestContext with portalId)
    ↓
Route Handler
    ↓
Database Query (prisma.lead.findMany(...))
    ↓
Multi-Tenant Middleware
    ├─ Extract portalId from RequestContext
    ├─ Check if model is portal-scoped
    ├─ Check if user is platform admin
    └─ Inject portalId filter OR verify portal ownership
    ↓
Execute Query
    ↓
Return Results (filtered by portal)

Portal-Scoped Models

The following models have automatic portal filtering enabled:

Core Entities

• Lead - Customer leads
• Conversation - Message conversations
• Message - Individual messages
• CallRecord - Phone call records
• PhoneNumberMapping - Phone number assignments

Analytics

• AnalyticsRollup - Time-series analytics data
• LeadAnalyticsRollup - Lead-specific analytics
• PortalAnalyticsSummary - Portal-wide analytics summaries
• LeadAnalyticsSummary - Lead pipeline analytics

Portal Management

• PortalLocation - Portal locations
• UserPresence - Real-time user presence
• UserPortalAccess - Portal access grants

Autoresponder

• AutoresponderExperiment - A/B testing experiments
• AutoresponderGuide - Response guides
• AutoresponderSequence - Message sequences
• AutoresponderTouchpoint - Customer touchpoints

Other

• KnowledgeBaseEntry - Knowledge base articles
• Business - Business profiles (1:1 with Portal)
• UserMilestone - TTFV tracking
• PortalTTFVSummary - Time-to-first-value summaries
• CompetitorAnalysis - Competitor data
• DemoTelemetry - Demo mode telemetry

Usage

Automatic (Default Behavior)

The middleware is automatically applied to all database queries. No code changes needed:

import prisma from '@/lib/database';

// Portal ID is automatically injected from RequestContext
const leads = await prisma.lead.findMany({
  where: { status: 'new' }
});
// Executes: SELECT * FROM Lead WHERE status = 'new' AND portalId = 'current-portal-id'

Setting Request Context

In API Routes (Next.js App Router)

Request context is automatically set by the request context middleware:

// app/api/leads/route.ts
import { NextRequest } from 'next/server';
import prisma from '@/lib/database';

export async function GET(request: NextRequest) {
  // RequestContext is already set by middleware
  // portalId is extracted from headers or session

  const leads = await prisma.lead.findMany();
  // Automatically filtered by current portal

  return Response.json(leads);
}

Manually Setting Context

For server-side operations outside API routes:

import RequestContext from '@/lib/utils/requestContext.server';
import prisma from '@/lib/database';

const portalId = 'portal-123';
const context = RequestContext.create({
  portalId,
  requestId: 'operation-id',
});

await RequestContext.runAsync(context, async () => {
  // All queries in this block use portal-123
  const leads = await prisma.lead.findMany();
  const conversations = await prisma.conversation.findMany();
});

Platform Admin Access

Platform admins can bypass portal filtering:

const context = RequestContext.create({
  portalId: 'any-portal',
  requestId: 'admin-operation',
  baggage: {
    isPlatformAdmin: true, // Bypass portal filtering
  },
});

await RequestContext.runAsync(context, async () => {
  // Returns leads from ALL portals
  const allLeads = await prisma.lead.findMany();
});

Explicit Portal Override

You can explicitly specify a different portal (requires proper authorization):

// Only works if user has access to 'other-portal-id'
const leads = await prisma.lead.findMany({
  where: {
    portalId: 'other-portal-id', // Explicit override
    status: 'new'
  }
});

Operation-Specific Behavior

Read Operations (findMany, findFirst, findUnique, count, aggregate)

Behavior: Automatically inject portalId filter

// Before middleware
await prisma.lead.findMany({ where: { status: 'new' } });

// After middleware (automatic)
await prisma.lead.findMany({
  where: {
    status: 'new',
    portalId: 'current-portal-id' // Injected
  }
});

Create Operations

Behavior: Inject portalId if missing, verify if provided

// Create without portalId - automatically injected
await prisma.lead.create({
  data: { name: 'New Lead' }
});
// Becomes: { name: 'New Lead', portalId: 'current-portal-id' }

// Create with wrong portalId - throws error
await prisma.lead.create({
  data: {
    name: 'New Lead',
    portalId: 'different-portal' // ❌ Error: Portal ID mismatch
  }
});

Update Operations

Behavior: Inject portalId filter, prevent changing portal

// Update - automatically filtered
await prisma.lead.update({
  where: { id: 'lead-1' },
  data: { status: 'contacted' }
});
// Executes: UPDATE Lead SET status = 'contacted'
//           WHERE id = 'lead-1' AND portalId = 'current-portal-id'

// Attempt to change portal - throws error
await prisma.lead.update({
  where: { id: 'lead-1' },
  data: { portalId: 'different-portal' } // ❌ Error: Cannot change portalId
});

Delete Operations

Behavior: Inject portalId filter to prevent cross-tenant deletion

// Delete - automatically filtered
await prisma.lead.delete({
  where: { id: 'lead-1' }
});
// Executes: DELETE FROM Lead
//           WHERE id = 'lead-1' AND portalId = 'current-portal-id'

Testing

Unit Tests

import { describe, it, expect } from 'vitest';
import RequestContext from '@/lib/utils/requestContext.server';
import prisma from '@/lib/database';

describe('Multi-Tenant Middleware', () => {
  it('should inject portalId on findMany', async () => {
    const context = RequestContext.create({
      portalId: 'test-portal',
      requestId: 'test',
    });

    await RequestContext.runAsync(context, async () => {
      const leads = await prisma.lead.findMany();
      // All leads belong to 'test-portal'
      expect(leads.every(l => l.portalId === 'test-portal')).toBe(true);
    });
  });
});

Integration Tests

See /tests/lib/database/multi-tenant-middleware.test.ts for comprehensive test suite.

Security Considerations

✅ Protection Against

1. Cross-Tenant Data Leaks: Users cannot query data from other portals
2. Unauthorized Updates: Users cannot modify data in other portals
3. Unauthorized Deletions: Users cannot delete data from other portals
4. Portal ID Tampering: Attempts to change portal ownership are blocked

⚠️ Important Notes

1. Context Required: Portal ID must be set in RequestContext
2. No Context = No Filtering: If no context is set, queries execute without filtering (for system operations)
3. Admin Bypass: Platform admins can access all portals (use isPlatformAdmin: true in baggage)
4. Explicit Overrides: Explicitly specified portalId in queries bypasses injection (not verification)

Troubleshooting

Issue: Queries return empty results

Cause: Portal ID not set in RequestContext

Solution: Ensure request middleware is setting context:

// Check if context is set
const context = RequestContext.get();
console.log('Portal ID:', context?.portalId);

// Manually set if needed
const ctx = RequestContext.create({ portalId: 'your-portal-id' });
await RequestContext.runAsync(ctx, async () => {
  // Your queries here
});

Issue: "Portal ID mismatch" error

Cause: Attempting to create/update with wrong portal ID

Solution: Remove portalId from data (it will be auto-injected):

// ❌ Wrong
await prisma.lead.create({
  data: { name: 'Lead', portalId: 'portal-1' }
});

// ✅ Correct
await prisma.lead.create({
  data: { name: 'Lead' } // portalId auto-injected
});

Issue: Platform admin sees filtered results

Cause: isPlatformAdmin not set in request baggage

Solution: Set admin flag in request context:

const context = RequestContext.create({
  portalId: 'any',
  requestId: 'admin-op',
  baggage: { isPlatformAdmin: true }
});

await RequestContext.runAsync(context, async () => {
  // Sees all portals
});

Performance

Impact

• Negligible: Filter injection adds ~0.1ms per query
• Database Indexes: Ensure portalId columns are indexed (already done in schema)
• Query Plan: Database uses index on portalId for efficient filtering

Optimization Tips

1. Composite Indexes: Most portal-scoped models have composite indexes on (portalId, <other-fields>)
2. Batch Operations: Use findMany with filters instead of multiple findUnique calls
3. Caching: Consider caching frequently accessed portal data

Migration Guide

From Manual Portal Filtering

Before (manual filtering):

const portalId = await getPortalId();
const leads = await prisma.lead.findMany({
  where: {
    portalId,
    status: 'new'
  }
});

After (automatic):

// Just set context once (usually in middleware)
const leads = await prisma.lead.findMany({
  where: {
    status: 'new'
    // portalId automatically injected
  }
});

Related Documentation

• Request Context Documentation
• Portal Access Utilities
• Database Module
• Testing Guide

Changelog

1.0.0 (2025-12-13)

• Initial implementation
• Support for all CRUD operations
• Platform admin bypass
• Comprehensive test coverage
• Production deployment