> ## Documentation Index
> Fetch the complete documentation index at: https://brokeros.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Database Architecture & Prisma ORM

> PostgreSQL schema design, Prisma 7 ORM, the isCpProject invariant, transactional consistency, and migration workflows.

## Overview

BrokerOS relies on **PostgreSQL** managed through **Prisma 7**. The schema located at [`packages/prisma/schema.prisma`](https://github.com/trybrokeros/BrokerOS/blob/main/packages/prisma/schema.prisma) serves as the single source of truth for the entire database architecture.

All database queries across `apps/api` and `apps/workers` use the strongly typed Prisma client generated to `packages/prisma/generated/client` and re-exported via `@brokeros/prisma`. Raw SQL strings are strictly prohibited in application code.

***

## The Core Invariant (`Project.isCpProject`)

The BrokerOS platform hosts two completely distinct business models inside a single database instance:

1. **Internal Brokerage**: Internal sales team sells developer inventory directly to prospective homebuyers. Projects are flagged with `isCpProject = false`.
2. **Channel Partner (CP)**: External broker network recruited by Sourcing Managers and closed by Closing Managers. Projects are flagged with `isCpProject = true`.

```prisma theme={null}
model Project {
  id          String   @id @default(cuid())
  name        String
  slug        String   @unique
  isCpProject Boolean  @default(false)
  // ...
}
```

> [!WARNING]
>
> **The Law of Business Separation**: Exactly one boolean flag (`Project.isCpProject`) separates these two enterprise worlds. Never mix leads, units, site visits, or commission records across this boundary. All queries, listing endpoints, and role scopes must filter explicitly on `isCpProject`.

***

## Entity Relationship Overview

```mermaid theme={null}
erDiagram
    Project ||--o{ Tower : "has"
    Tower ||--o{ Unit : "contains"
    Project ||--o{ ProjectAssignment : "scoped to"
    User ||--o{ ProjectAssignment : "assigned"
    
    Lead ||--o{ SiteVisit : "schedules"
    Lead ||--o{ FollowUp : "tracks"
    Lead ||--o{ Negotiation : "records"
    Lead ||--o| Booking : "finalizes"
    Lead ||--o{ CallRecord : "logs"
    User ||--o{ CallRecord : "places"
    
    Booking ||--|| Customer : "converts to"
    Booking ||--|| Unit : "locks"
    Booking ||--o| BrokerageRecord : "creates (if CP)"
    Broker ||--o{ BrokerageRecord : "earns"
```

***

## Telephony & Softphone Architecture Models

The database tracks both company-wide carrier infrastructure and individual softphone call sessions:

### 1. `CrmTelephonyConfig`

Manages the organization's active carrier connection configured at `Dashboard -> Admin -> Settings -> CRM Telephony & WebRTC`:

```prisma theme={null}
model CrmTelephonyConfig {
  id             String             @id @default(uuid())
  provider       VoiceTelephonyType // TWILIO, TELNYX, EXOTEL, VOBIZ
  name           String
  isActive       Boolean            @default(true)

  accountSid     String?
  authToken      String?
  apiKey         String?
  apiToken       String?
  subdomain      String? // e.g. api.exotel.com
  sipDomain      String?
  fromNumbers    String[] // Array of caller IDs / virtual numbers
  applicationSid String? // TwiML App SID or Telnyx App ID for browser WebRTC

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

  @@index([provider])
  @@index([isActive])
  @@map("crm_telephony_config")
}
```

> [!NOTE]
>
> **Single Active Carrier Rule**: Only one `CrmTelephonyConfig` record may have `isActive = true` at any given time. Connecting a new carrier automatically marks prior configurations inactive.

### 2. `CallRecord`

Stores every inbound and outbound telecalling interaction, carrier identifiers, audio recording URLs, and AI intelligence:

```prisma theme={null}
model CallRecord {
  id                    String              @id @default(uuid())
  leadId                String?
  brokerId              String?
  userId                String?
  phoneNumber           String
  direction             CallDirection       // INBOUND, OUTBOUND
  status                CallStatus          // CONNECTED, MISSED, BUSY, FAILED
  startedAt             DateTime
  endedAt               DateTime?
  duration              Int?                // Duration in seconds
  recordingUrl          String?
  recordingSize         Int?

  aiTranscript          String?
  aiSummary             String?
  aiSentiment           String?
  aiExtractedData       Json?

  provider              VoiceTelephonyType?
  providerCallId        String?             // Carrier Call SID or UUID
  agentBrowserSessionId String?

  createdAt             DateTime            @default(now())

  @@index([leadId])
  @@index([brokerId])
  @@index([userId])
  @@index([startedAt])
  @@index([direction])
  @@index([providerCallId])
  @@map("call_record")
}
```

***

## Transaction Law (`prisma.$transaction`)

Multi-model writes must always execute inside an interactive transaction (`prisma.$transaction`) to guarantee atomicity. For example, converting a lead into a confirmed booking:

```typescript theme={null}
await prisma.$transaction(async (tx) => {
  // 1. Mark Unit as SOLD to prevent double-booking
  await tx.unit.update({
    where: { id: unitId },
    data: { status: 'SOLD' },
  });

  // 2. Advance Lead status to BOOKED
  await tx.lead.update({
    where: { id: leadId },
    data: { status: 'BOOKED' },
  });

  // 3. Create permanent post-sales Customer entity
  const customer = await tx.customer.create({
    data: {
      name: lead.name,
      phone: lead.phone,
      email: lead.email,
      bookingId: booking.id,
    },
  });

  // 4. Create BrokerageRecord if Channel Partner deal
  if (isCpDeal) {
    await tx.brokerageRecord.create({
      data: {
        bookingId: booking.id,
        brokerId: broker.id,
        commissionAmount: calculatedCommission,
        status: 'PENDING_VERIFICATION',
      },
    });
  }

  return customer;
});
```

If any step fails (e.g., duplicate customer constraint or broker commission calculation error), the entire transaction rolls back, leaving inventory units unlocked.

***

## Soft-Delete Pattern

To comply with real estate regulatory requirements and maintain financial audit trails, entity records are never hard-deleted from the database:

```prisma theme={null}
model Lead {
  id        String    @id @default(cuid())
  name      String
  phone     String
  deletedAt DateTime?
}
```

All query services in `apps/api/` must explicitly append the filter:

```typescript theme={null}
where: {
  deletedAt: null,
}
```

Records with non-null `deletedAt` timestamps are excluded from active dashboards, search indexes, and campaign recipient pools while remaining intact for historical audit reporting.

***

## Indexing & Performance Guidelines

The BrokerOS schema enforces indexes on frequently filtered fields:

* Composite index on `[projectId, status, deletedAt]` for fast lead pipeline queries
* Index on `[phoneNumberId, createdAt]` for incoming WhatsApp message lookups
* Unique index on `[email, deletedAt]` for customer deduplication
* Indexes on foreign keys (`brokerId`, `assignedUserId`, `unitId`) to optimize relational joins
* Index on `providerCallId` for fast lookup during carrier recording and status webhook callbacks
* Composite index on `[status, scheduledAt]` across `WhatsAppMessage`, `EmailMessage`, and `SmsMessage` models for sub-millisecond worker polling of queued messages

***

## Omnichannel Scheduled Messaging Models

To support time-delayed 1-on-1 team inbox messaging across WhatsApp, SMS, and Email, each messaging table incorporates dedicated scheduling attributes:

| Field | Type | Description |
| :- | :- | :- |
| `scheduledAt` | `DateTime?` | Target execution timestamp for message dispatch |
| `scheduledBy` | `String?` | ID of the sales executive or manager who scheduled the message |
| `isScheduled` | `Boolean` | Flag indicating whether the message originated as a scheduled delivery |
| `status` | `Enum` / `String` | Transitions: `SCHEDULED` -> `PROCESSING` -> `SENT` / `FAILED` / `CANCELLED` |

All three models define `@@index([status, scheduledAt])` to ensure the background scanner worker performs non-blocking index scans every 10 seconds.

***

## Migration & Seed Workflows

Database schema changes and migrations must be managed through Turborepo from the repository root:

```bash theme={null}
# 1. Update packages/prisma/schema.prisma

# 2. Re-generate Prisma Client to packages/prisma/generated/client
pnpm --filter @brokeros/prisma db:generate

# 3. Create and apply a migration in local development
pnpm --filter @brokeros/prisma db:migrate

# 4. Deploy migrations in staging or production environments
pnpm --filter @brokeros/prisma db:deploy

# 5. Populate local database with seed data (users, roles, projects)
pnpm --filter @brokeros/prisma db:seed
```

> [!WARNING]
>
> Never manually edit or delete applied migration files in `packages/prisma/migrations/`. Always generate forward-only migrations.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.