PostgreSQL

Category: Infrastructure
Integration type: Platform-level (sidecar service or managed RDS)
External SDK: @prisma/client

Purpose

PostgreSQL is the primary relational store for all structured platform data. Every tenant record, user, activity, credential, billing record, and audit log lives here. It is the source of truth for:

All operational data (tenants, users, activities, deliverables, campaigns)
All financial data (credit balances, ledger, topup orders, billing events)
All configuration (agent configs, notification providers, integrations, plans)
All audit data (HITL approvals, LLM call logs, dispatch logs)

Config Structure

Platform config (env vars)


DATABASE_URL=postgresql://user:password@postgres:5432/leadmetrics
DATABASE_POOL_MIN=2
DATABASE_POOL_MAX=20
DATABASE_SSL=false      # true in production / Coolify

Integration Pattern

Prisma ORM setup (`packages/db/src/client.ts`)


// packages/db/src/client.ts
import { PrismaClient } from '@prisma/client';
 
const globalForPrisma = globalThis as unknown as { prisma: PrismaClient };
export const db = globalForPrisma.prisma || new PrismaClient();
if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = db;

Schema package structure

All table schemas are defined in prisma/schema.prisma:


packages/db/
├── prisma/
│   └── schema.prisma   # Source of truth — all models defined here
└── src/
    └── client.ts       # PrismaClient singleton

Base record fields

All models include the standard base fields as a Prisma model block:


// prisma/schema.prisma
model BaseRecord {
  id        String   @id @default(cuid())
  refId     String   @unique
  tenantId  String
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  deletedAt DateTime?
}

Multi-tenant isolation

Every table includes tenant_id. All application queries include a WHERE tenant_id = $1 clause via the Prisma query builder. There is no row-level security (RLS) in PostgreSQL — tenant isolation is enforced at the application layer:


// Always include tenantId in queries
const activities = await db.activity.findMany({
  where: {
    tenantId,   // Always present
    status: 'pending',
  },
});

Migrations

Migrations are managed with Prisma:


packages/db/
├── prisma/
│   ├── schema.prisma     Source of truth
│   └── migrations/       Generated SQL migration files
│       ├── 0001_init/
│       ├── 0002_add_credits/
│       └── ...
└── src/client.ts

Run migrations:


prisma migrate dev    # Generate and apply migration in development
prisma db push        # Push schema changes without a migration file (prototyping)

Migrations are applied on startup in production (via prisma migrate deploy in the entrypoint script).

Test Cases

Integration tests (real PostgreSQL)

Per the Testing Strategy, database tests use a real PostgreSQL instance — no mocking:


// In test setup
beforeAll(async () => {
  await runMigrations(testDb);
});
 
afterEach(async () => {
  await testDb.execute(sql`TRUNCATE TABLE activities RESTART IDENTITY CASCADE`);
});

Each test gets a clean database state. A test-specific tenant_id is seeded to ensure isolation between concurrent test runs.

Test	Approach
Row-level tenant isolation	Insert rows for two tenants; query with `tenantId = A`; assert B’s rows not returned
Prisma upsert works for conflict handling	Insert same `refId` twice; assert single row with updated fields
Credit ledger balance reconciliation	Insert multiple ledger events; assert balance matches
`is_deleted = true` soft delete pattern	Soft-delete a row; assert not returned in standard queries

Database Overview — which data goes to PostgreSQL vs MongoDB
PostgreSQL Schema — full table definitions
SQL Entity Relations — ER diagrams
Multi-Tenancy — tenant isolation approach