Database Schema

Resonance uses PostgreSQL with Prisma ORM. This guide covers the database schema, model relationships, and common query patterns.

Schema Overview

The database schema is defined in prisma/schema.prisma and consists of two core models:

Voice

System and custom voice profiles with audio references

Generation

Text-to-speech generation history with parameters

Prisma Configuration

generator client {
  provider = "prisma-client"
  output   = "../src/generated/prisma"
}

datasource db {
  provider = "postgresql"
}

The Prisma client is generated to src/generated/prisma to separate generated code from application code.

Models

Voice Model

Stores both system-provided and user-uploaded custom voices.

model Voice {
  id String @id @default(cuid())

  orgId String?  // null for SYSTEM voices, populated for CUSTOM

  name        String
  description String?
  category    VoiceCategory @default(GENERAL)
  language    String        @default("en-US")
  variant     VoiceVariant
  r2ObjectKey String?       // Cloudflare R2 object key

  generations Generation[]

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@index([variant])
  @@index([orgId])
}

Voice Fields

id (String)

Primary key using CUID (Collision-resistant Unique Identifier).

id: "clx1a2b3c4d5e6f7g8h9"

orgId (String | null)

Organization identifier from Clerk. null for system voices, populated for custom voices.

orgId: "org_2aBC3dEF4gHI5jKL6mNO"  // Custom voice
orgId: null                         // System voice

Index: Optimized for queries filtering by organization.

name (String)

Display name for the voice.

name: "Aaron"              // System voice
name: "CEO Announcements"  // Custom voice

description (String | null)

Optional description of voice characteristics.

description: "Soothing and calm, like a self-help audiobook narrator"

category (VoiceCategory)

Voice categorization for filtering and organization. See Voice Categories.

category: "AUDIOBOOK" | "CONVERSATIONAL" | "CUSTOMER_SERVICE" | ...

language (String)

Language code (defaults to “en-US”).

language: "en-US" | "en-GB" | "en-IN" | "en-AU" | "ru-RU"

variant (VoiceVariant)

Distinguishes system-provided from user-uploaded voices. See Voice Variants.

variant: "SYSTEM" | "CUSTOM"

Index: Optimized for queries separating system and custom voices.

r2ObjectKey (String | null)

Cloudflare R2 object storage key for the audio file.

r2ObjectKey: "voices/system/clx123"          // System voice
r2ObjectKey: "voices/orgs/org_456/clx789"   // Custom voice

null until audio is uploaded successfully.

generations (Generation[])

One-to-many relationship with generations using this voice.

generations Generation[]

Generation Model

Stores TTS generation history with full parameters for reproducibility.

model Generation {
  id String @id @default(cuid())

  orgId String

  voiceId String?
  voice   Voice?  @relation(fields: [voiceId], references: [id], onDelete: SetNull)

  text              String
  voiceName         String  // Denormalized for history
  r2ObjectKey       String?
  temperature       Float
  topP              Float
  topK              Int
  repetitionPenalty Float

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@index([orgId])
  @@index([voiceId])
}

Generation Fields

id (String)

Primary key using CUID.

orgId (String)

Organization that created the generation. Always required.Index: Optimized for fetching user’s generation history.

voiceId (String | null) & voice (Voice | null)

Foreign key to Voice model with onDelete: SetNull.

voice Voice? @relation(fields: [voiceId], references: [id], onDelete: SetNull)

If the voice is deleted, voiceId becomes null but the generation is preserved.Index: Optimized for voice-specific queries.

text (String)

The input text that was converted to speech.

text: "Welcome to our platform. We're excited to have you here."

Max length enforced at application layer: TEXT_MAX_LENGTH = 5000 characters.

voiceName (String)

Denormalized voice name for historical reference.

voiceName: "Professional Male"

Preserved even if the original voice is deleted.

r2ObjectKey (String | null)

Cloudflare R2 storage key for generated audio.

r2ObjectKey: "generations/orgs/org_456/clx987xyz"

TTS Parameters

Generation parameters for reproducibility:

temperature: 0.8        // Range: 0-2, controls randomness
topP: 0.95             // Range: 0-1, nucleus sampling
topK: 1000             // Range: 1-10000, top-k sampling
repetitionPenalty: 1.2 // Range: 1-2, prevents repetition

These are stored to allow users to reproduce or adjust previous generations.

Enums

Voice Variants

enum VoiceVariant {
  SYSTEM   // Pre-loaded voices available to all users
  CUSTOM   // User-uploaded voices (org-specific)
}

// Get all system voices
const systemVoices = await prisma.voice.findMany({
  where: { variant: "SYSTEM" },
});

// Get user's custom voices
const customVoices = await prisma.voice.findMany({
  where: {
    variant: "CUSTOM",
    orgId: ctx.orgId,
  },
});

Voice Categories

enum VoiceCategory {
  AUDIOBOOK
  CONVERSATIONAL
  CUSTOMER_SERVICE
  GENERAL
  NARRATIVE
  CHARACTERS
  MEDITATION
  MOTIVATIONAL
  PODCAST
  ADVERTISING
  VOICEOVER
  CORPORATE
}

Category labels are defined in src/features/voices/data/voice-categories.ts:

export const VOICE_CATEGORY_LABELS: Record<VoiceCategory, string> = {
  AUDIOBOOK: "Audiobook",
  CONVERSATIONAL: "Conversational",
  CUSTOMER_SERVICE: "Customer Service",
  // ...
};

Relationships

Voice → Generation (One-to-Many)

// Voice model
generations Generation[]

// Generation model
voiceId String?
voice   Voice?  @relation(fields: [voiceId], references: [id], onDelete: SetNull)

Deletion Behavior: onDelete: SetNull

When a voice is deleted, related generations are preserved
voiceId is set to null
voiceName retains the historical voice name
Audio files remain accessible via r2ObjectKey

// Fetch generation with voice details
const generation = await prisma.generation.findUnique({
  where: { id: generationId },
  include: {
    voice: {
      select: {
        name: true,
        category: true,
      },
    },
  },
});

// Result:
// {
//   id: "clx987",
//   text: "Hello world",
//   voice: { name: "Aaron", category: "AUDIOBOOK" }
// }

Common Query Patterns

Filtering Voices

const voices = await prisma.voice.findMany({
  where: {
    OR: [
      { name: { contains: query, mode: "insensitive" } },
      { description: { contains: query, mode: "insensitive" } },
    ],
    variant: "SYSTEM",
  },
});

Safe Deletion with Cleanup

import { prisma } from "@/lib/db";
import { deleteAudio } from "@/lib/r2";

const voice = await prisma.voice.findUnique({
  where: {
    id: input.id,
    variant: "CUSTOM",
    orgId: ctx.orgId,
  },
  select: { id: true, r2ObjectKey: true },
});

if (!voice) {
  throw new TRPCError({
    code: "NOT_FOUND",
    message: "Voice not found",
  });
}

// Delete from database first
await prisma.voice.delete({ where: { id: voice.id } });

// Clean up storage (fire-and-forget)
if (voice.r2ObjectKey) {
  await deleteAudio(voice.r2ObjectKey).catch(() => {});
}

Always delete from the database first, then clean up storage. This prevents orphaned database records if storage deletion fails.

Pagination

const generations = await prisma.generation.findMany({
  where: { orgId: ctx.orgId },
  take: 20,
  skip: 1, // Skip the cursor
  cursor: cursor ? { id: cursor } : undefined,
  orderBy: { createdAt: "desc" },
});

Omit Sensitive Fields

const generations = await prisma.generation.findMany({
  where: { orgId: ctx.orgId },
  omit: {
    orgId: true,      // Don't expose to client
    r2ObjectKey: true, // Use signed URLs instead
  },
});

Data Migrations

Creating Migrations

Edit Schema

Modify prisma/schema.prisma

Create Migration

npx prisma migrate dev --name add_voice_gender

Review SQL

Check generated SQL in prisma/migrations/[timestamp]_add_voice_gender/migration.sql

Generate Client

npx prisma generate

TypeScript types are automatically updated.

Seeding System Voices

System voices are seeded using scripts/seed-system-voices.ts:

npm run db:seed

// scripts/seed-system-voices.ts
import { prisma } from "../src/lib/db";

async function seedSystemVoice(name: string) {
  const voice = await prisma.voice.create({
    data: {
      name,
      variant: "SYSTEM",
      orgId: null,
      description: systemVoiceMetadata[name].description,
      category: systemVoiceMetadata[name].category,
      language: systemVoiceMetadata[name].language,
    },
  });

  const r2ObjectKey = `voices/system/${voice.id}`;
  
  await uploadSystemVoiceAudio({ key: r2ObjectKey, buffer, contentType });
  
  await prisma.voice.update({
    where: { id: voice.id },
    data: { r2ObjectKey },
  });
}

Database Client Usage

Initialization

// src/lib/db.ts
import { PrismaClient } from "@/generated/prisma/client";
import { PrismaPg } from "@prisma/adapter-pg";

const adapter = new PrismaPg({
  connectionString: env.DATABASE_URL,
});

const prisma = new PrismaClient({ adapter });

export { prisma };

The Prisma PostgreSQL adapter (@prisma/adapter-pg) is used for better connection pooling and edge runtime support.

Transaction Pattern

await prisma.$transaction(async (tx) => {
  const voice = await tx.voice.create({
    data: { name, variant: "CUSTOM", orgId },
  });

  const generation = await tx.generation.create({
    data: {
      text: "Test",
      voiceId: voice.id,
      voiceName: voice.name,
      orgId,
    },
  });

  return { voice, generation };
});

Best Practices

Index by Query Patterns

Add indexes for frequently filtered fields:

@@index([variant])
@@index([orgId])

Omit Sensitive Data

Use omit to exclude fields from client responses:

omit: { r2ObjectKey: true }

Denormalize for History

Store voiceName to preserve history even if voice is deleted.

Soft Delete vs SetNull

Use onDelete: SetNull for historical records that should persist.

Next Steps

Project Structure

Learn about codebase organization

Extending Features

Add new models, fields, and relationships

Usage

Development

Schema Overview

Voice

Generation

Prisma Configuration

Models

Voice Model

Voice Fields

Generation Model

Generation Fields

Enums

Voice Variants

Voice Categories

Relationships

Voice → Generation (One-to-Many)

Common Query Patterns

Filtering Voices

Safe Deletion with Cleanup

Omit Sensitive Fields

Data Migrations

Creating Migrations

Seeding System Voices

Database Client Usage

Initialization

Transaction Pattern

Best Practices

Index by Query Patterns

Omit Sensitive Data

Denormalize for History

Soft Delete vs SetNull

Next Steps

Project Structure

Extending Features

Build docs developers (and LLMs) love

Usage

Development

​Schema Overview

Voice

Generation

​Prisma Configuration

​Models

​Voice Model

​Voice Fields

​Generation Model

​Generation Fields

​Enums

​Voice Variants

​Voice Categories

​Relationships

​Voice → Generation (One-to-Many)

​Common Query Patterns

​Filtering Voices

​Safe Deletion with Cleanup

​Pagination

​Omit Sensitive Fields

​Data Migrations

​Creating Migrations

​Seeding System Voices

​Database Client Usage

​Initialization

​Transaction Pattern

​Best Practices

Index by Query Patterns

Omit Sensitive Data

Denormalize for History

Soft Delete vs SetNull

​Next Steps

Project Structure

Extending Features

Build docs developers (and LLMs) love

Schema Overview

Prisma Configuration

Models

Voice Model

Voice Fields

Generation Model

Generation Fields

Enums

Voice Variants

Voice Categories

Relationships

Voice → Generation (One-to-Many)

Common Query Patterns

Filtering Voices

Safe Deletion with Cleanup

Pagination

Omit Sensitive Fields

Data Migrations

Creating Migrations

Seeding System Voices

Database Client Usage

Initialization

Transaction Pattern

Best Practices

Next Steps