---
title: "How to Build a Voice Agent Data Layer with PostgreSQL and Prisma"
description: "Design the schema for calls, turns, tool calls, transcripts, sentiment, and lead scoring. Real Prisma schema, indexes that matter, and query patterns that scale to 1M calls."
canonical: https://callsphere.ai/blog/vw1h-build-voice-agent-postgresql-prisma-data-layer-schema
category: "AI Infrastructure"
tags: ["Tutorial", "Build", "PostgreSQL", "Prisma", "Schema"]
author: "CallSphere Team"
published: 2026-04-05T00:00:00.000Z
updated: 2026-05-07T06:45:01.981Z
---

# How to Build a Voice Agent Data Layer with PostgreSQL and Prisma

> Design the schema for calls, turns, tool calls, transcripts, sentiment, and lead scoring. Real Prisma schema, indexes that matter, and query patterns that scale to 1M calls.

> **TL;DR** — Don't dump JSON blobs in one table. Split call → turn → tool_call → transcript → analytics into normalized tables with the right indexes. CallSphere runs 115+ tables; this post shows the 6 that matter for voice.

## What you'll build

A Prisma schema that captures every call, every turn, every tool invocation, plus post-call sentiment and lead scoring — and queries it in  `${t.role}: ${t.text}`).join("\n");

const r = await oai.chat.completions.create({
    model: "gpt-4o-mini",
    response_format: { type: "json_object" },
    messages: [{ role: "user", content: `Score this call. Return JSON
{sentiment:-1..1, lead_score:0..100, intent:string, entities:object}.

${transcript}` }],
  });
  const data = JSON.parse(r.choices[0].message.content!);
  await db.analytics.create({ data: { callId, ...data, leadScore: data.lead_score }});
}
```

## Step 5 — Query patterns that scale

```ts
// Top leads in last 7 days for a vertical
const topLeads = await db.call.findMany({
  where: {
    agent: { vertical: "healthcare" },
    startedAt: { gte: new Date(Date.now() - 7 * 86400_000) },
    analytics: { leadScore: { gte: 70 } },
  },
  include: { analytics: true },
  orderBy: { analytics: { leadScore: "desc" } },
  take: 50,
});
```

The compound index `(agentId, startedAt)` plus `(leadScore)` keeps this under 30ms at 1M rows.

## Step 6 — Partitioning at 10M+ rows

When `Call` crosses 10M rows, switch to monthly partitions:

```sql
CREATE TABLE call_2026_05 PARTITION OF call FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');
```

## Common pitfalls

- **JSONB everywhere**: indexable but slow for filters. Promote hot fields to columns.
- **No `onDelete: Cascade`**: orphan turns when calls get deleted. Always cascade child rows.
- **Single-column indexes only**: (agent_id, started_at) compound is 10x faster for vertical+time queries.
- **Computing analytics inline**: blocks the call end. Push to a queue and process async.

## How CallSphere does this in production

CallSphere runs 115+ DB tables across 6 verticals on Postgres 15. The schema above is a simplified slice — production adds tenant isolation, soft-delete, and 14 indexes per call table. Healthcare's analytics worker computes sentiment + lead_score within 60s of call end and writes back via this exact `Analytics` table. [Pricing](/pricing) covers all storage; [trial here](/trial).

## FAQ

**MongoDB instead?** Possible, but joins on call → turns → analytics are clean in Postgres and harder in Mongo. We chose Postgres.

**Why store the full transcript text?** Search, eval datasets, and BI. Disk is cheap; embeddings are derived.

**How big does this get?** ~50KB per 5-minute call (turns + tool calls + analytics). 1M calls ≈ 50GB. Manageable on a single `db.r6g.large`.

**Audio recordings in DB?** No — S3 with a URL pointer. Postgres for metadata only.

## Sources

- [Prisma schema reference](https://www.prisma.io/docs/orm/prisma-schema/overview)
- [PostgreSQL partitioning](https://www.postgresql.org/docs/current/ddl-partitioning.html)
- [Prisma + PostgreSQL quickstart](https://www.prisma.io/docs/prisma-orm/quickstart/postgresql)
- [Voice Summary open-source schema](https://github.com/DrDroidLab/voicesummary)

---

Source: https://callsphere.ai/blog/vw1h-build-voice-agent-postgresql-prisma-data-layer-schema