Data model

The schema lives in a single file, prisma/schema.prisma, against Supabase PostgreSQL. The generated client is emitted to prisma/generated and imported as @/prisma/generated/client.

generator client {
  provider = "prisma-client-js"
  output   = "generated"
}

datasource db {
  provider  = "postgresql"
  url       = env("DATABASE_URL")   // pooled connection, used at runtime
  directUrl = env("DIRECT_URL")     // direct connection, used by migrations
}

Conventions

Convention Detail

Primary keys

String @id @default(uuid()) @db.Uuid on every model

Timestamps

createdAt @default(now()) and updatedAt @updatedAt on every model

Scheduling columns

@db.Timestamptz(6) where timezone correctness matters (dueAt, nextEarliestAttemptAt, leaseExpiresAt, nextStepAt, completedAt)

Table naming

Newer models use @@map("snake_case"); older ones keep the Prisma default PascalCase table name

Field naming

camelCase in Prisma, snake_case on the wire, camelCase again in client TypeScript

Soft deletes

Boolean flags (isDeleted, isReleased, isActive) plus a timestamp, never a hard delete

Flexible data

Json columns for payloads and metadata (payloadJson, configJson, customFields, metadata, response, gateConditions, extraConfigs)

Tenancy

subscriptionId on every tenant-scoped model; see Multi-tenancy

Table naming is inconsistent. Campaign, CampaignTarget, Call, WebhookEvent, PhoneNumbers, Profile, Subscription, SubscriptionUser and Activity use the default PascalCase table names, while everything added later uses @@map to a snake_case name. Raw SQL must quote the PascalCase names: FROM "PhoneNumbers", not FROM phone_numbers.

Model inventory

Model Table Page

Subscription

Subscription

Tenancy and identity models

SubscriptionUser

SubscriptionUser

Tenancy and identity models

Profile

Profile

Tenancy and identity models

Campaign

Campaign

CampaignTarget

CampaignTarget

Call

Call

CallAiAgentAnalysisResult

call_ai_agent_analysis_results

PhoneNumbers

PhoneNumbers

BlockedPhoneNumbers

blocked_phone_numbers

WebhookEvent

WebhookEvent

AiAgent

ai_agents

AI agent and config models

AiAgentVersion

ai_agent_versions

AI agent and config models

AgentTestCase

agent_test_cases

AI agent and config models

Configs

configs

AI agent and config models

Seller

sellers

Contact and registry models

Contact

contacts

Contact and registry models

TargetPhoneNumbers

target_phone_numbers

Contact and registry models

TargetEmails

target_emails

Contact and registry models

Activity

Activity

Contact and registry models

EmailCampaignTarget

email_campaign_targets

EmailVariation

email_variations

Domain

domains

EmailSendingDomain

email_sending_domains

Emails

emails

SequenceTemplate

sequence_templates

Sequence and task models

SequenceTemplateStep

sequence_template_steps

Sequence and task models

ContactSequence

contact_sequences

Sequence and task models

ContactSequenceStep

contact_sequence_steps

Sequence and task models

SalesRep

sales_reps

Sequence and task models

FollowUpTask

follow_up_tasks

Sequence and task models

Segment

segments

Segmentation models

SegmentMembership

segment_memberships

Segmentation models

SegmentStrategy

segment_strategies

Segmentation models

SegmentRun

segment_runs

Segmentation models

ReengagementDailyListEntry

reengagement_daily_list_entries

Segmentation models

Enums are catalogued on Enums.

High-level relationships

erDiagram
  Subscription ||--o{ SubscriptionUser : members
  Profile ||--o{ SubscriptionUser : memberships
  Subscription ||--o{ Campaign : owns
  Subscription ||--o{ Contact : owns

  Campaign ||--o{ CampaignTarget : "call targets"
  Campaign ||--o{ EmailCampaignTarget : "email recipients"
  Campaign ||--o{ EmailVariation : variations
  CampaignTarget ||--o{ Call : attempts
  Call ||--o{ CallAiAgentAnalysisResult : analyses
  Call ||--o{ WebhookEvent : events
  PhoneNumbers ||--o{ Call : "dialed from"

  AiAgent ||--o{ AiAgentVersion : versions
  AiAgentVersion ||--o{ Call : "spoke on"
  AiAgentVersion ||--o{ CallAiAgentAnalysisResult : "graded by"

  Seller ||--o{ Contact : employs
  Contact ||--o{ TargetPhoneNumbers : "phone endpoints"
  Contact ||--o{ TargetEmails : "email endpoints"
  Contact ||--o{ CampaignTarget : "dialer enrollment"
  Contact ||--o{ EmailCampaignTarget : "email enrollment"
  Contact ||--o{ ContactSequence : enrollments
  Contact ||--o{ Activity : interactions
  Contact ||--o{ FollowUpTask : tasks

  Domain ||--o{ EmailSendingDomain : accounts
  EmailSendingDomain ||--o{ Emails : "sent/received"
  EmailCampaignTarget ||--o{ Emails : messages

  SequenceTemplate ||--o{ SequenceTemplateStep : steps
  SequenceTemplate ||--o{ ContactSequence : enrollments
  ContactSequence ||--o{ ContactSequenceStep : "step log"
  SalesRep ||--o{ FollowUpTask : assigned

  Subscription ||--o{ Segment : owns
  Segment ||--o{ SegmentMembership : members
  Segment ||--o{ SegmentStrategy : methodologies
  Segment ||--o{ SegmentRun : runs
  SegmentStrategy ||--o{ SegmentRun : "applied by"
  SegmentStrategy ||--o{ ReengagementDailyListEntry : "picked under"
  SegmentRun ||--o{ ReengagementDailyListEntry : produced
  Segment ||--o{ ReengagementDailyListEntry : sources
  Contact ||--o{ SegmentMembership : "segment membership"
  Contact ||--o{ ReengagementDailyListEntry : "daily picks"
  SalesRep ||--o{ ReengagementDailyListEntry : "assigned picks"

Migrations

pnpm prisma migrate dev --name descriptive_name   # create + apply locally
pnpm prisma migrate deploy                        # apply in an environment
pnpm prisma generate                              # regenerate the client
pnpm prisma format                                # format the schema
pnpm prisma studio                                # browse data

The repository also defines composite scripts: pnpm prisma:migrate runs format && db push && generate (schema-push, no migration file — development only), and pnpm prisma:migrate:dev runs format && migrate dev && generate.

Migrations live in prisma/migrations/, named <timestamp>_<slug>, with migration_lock.toml pinning the provider. Do not edit an applied migration; add a new one.

Checklist for adding a field

Edit prisma/schema.prisma.
pnpm prisma migrate dev --name descriptive_name.
Update the Zod schema in lib/schema/<feature>.ts.
Export the type from lib/types.ts via z.infer<>.
Update the API serialiser (camelCase → snake_case).
Invalidate the affected React Query caches.
Update this module and the relevant domain page.

One-off SQL

prisma/sql/ holds scripts that were run manually and are not part of the migration chain:

Script Purpose

auth_hook_before_user_created_payability.sql

Supabase auth hook enforcing the Payability email domain at signup

backfill_contacts_from_campaign_targets.sql, backfill_contact_ids.sql, backfill_contacts_from_target_phone_numbers_batch.sql, backfill_contacts_tpn_te_from_campaign_targets.sql

Populate Contact and link it from existing targets

backfill_target_emails.sql, backfill_target_emails_from_campaign_targets_batch.sql, dedupe_target_emails.sql, cleanup_target_emails_and_link_email_campaign_targets.sql

Build and clean the email registry

backfill_target_phone_numbers_batch.sql

Build the phone registry

backfill_emails_from_activity_sent.sql

Reconstruct Emails rows from historical activity

segmentation_constraints.sql

Partial unique index and treatmentPercent range check that Prisma cannot express — see Segmentation constraints

Reporting views (vm_main_campaign_reports, main_required_summaries, main_sankey_chart_view, vm_main_daily_agent_performance_report, phone_number_pickup_rate, v_email_campaign_daily_engagement) and the get_and_lease_due_targets RPC are managed in Supabase, not in this repository.

vm_main_campaign_reports and vm_main_daily_agent_performance_report are no longer materialised. Each is now a plain view over an incrementally maintained rollup table — call_daily_stats and agent_daily_performance_stats respectively — defined in lib/supabase/sql/. See Daily report rollups. The remaining vm_-prefixed views are still materialised, so check their refresh schedule before treating stale numbers as a bug.

Database triggers

handle_justcall_call_initiated(), wired to trg_webhookevent_call_initiated, watches for JustCall call.initiated rows in WebhookEvent, extracts the JustCall user id, and marks the matching profile unavailable while incrementing its active-call count — availability tracking that predates the application-level dialer.