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

# Database Design

> Comprehensive database schema for multi-tenant real estate CRM with Prisma ORM

Winnerr's database is architected for real estate workflows with complete multi-tenant isolation, optimized for performance and scalability. Built with Prisma ORM and PostgreSQL, it ensures data integrity while supporting complex real estate business logic.

## Schema Overview

The database centers around four core entities: **Organizations**, **People**, **Properties**, and **Deals**, with extensive support for communications, AI features, and integrations.

```mermaid theme={null}
erDiagram
    Organization ||--o{ User : "has members"
    Organization ||--o{ Person : "manages contacts"
    Organization ||--o{ Property : "lists properties"
    Organization ||--o{ Deal : "tracks deals"
    Organization ||--o{ TwilioAccount : "owns"
    
    User ||--o{ TwilioPhoneNumber : "assigned"
    User ||--o{ Communication : "sends"
    User ||--o{ CallRecording : "records"
    
    Person ||--o{ Deal : "involved in"
    Person ||--o{ Communication : "receives"
    Person ||--o{ PersonTag : "tagged with"
    
    Property ||--o{ Deal : "associated with"
    Property ||--o{ PropertyImage : "has images"
    Property ||--o{ MLSData : "synced from"
    
    Deal ||--o{ DealTask : "generates tasks"
    Deal ||--o{ DealNote : "has notes"
    Deal ||--o{ DealStage : "progresses through"
    
    TwilioCall ||--|| CallRecording : "recorded as"
    CallRecording ||--o{ TranscriptSegment : "contains segments"
    
    Communication ||--o{ CommunicationAttachment : "has attachments"
```

## Core Entities

### Organization Management

#### Organization

The root entity for multi-tenant data isolation.

```prisma theme={null}
model Organization {
  id        String   @id @default(cuid())
  name      String
  slug      String   @unique
  logo      String?
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  // Relationships
  members           OrganizationMembership[]
  settings          OrganizationSettings?
  promptSettings    OrganizationPromptSettings?
  people            Person[]
  properties        Property[]
  deals             Deal[]
  twilioAccount     TwilioAccount?
  communications    Communication[]
  notes             Note[]
  tasks             Task[]
  
  @@map("Organization")
}
```

#### Organization Membership

Junction table linking users to organizations with roles.

```prisma theme={null}
model OrganizationMembership {
  id             String           @id @default(cuid())
  userId         String
  organizationId String
  role           OrganizationRole @default(AGENT)
  joinedAt       DateTime         @default(now())
  
  // Relationships
  user         User         @relation(fields: [userId], references: [id], onDelete: Cascade)
  organization Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)
  
  @@unique([userId, organizationId])
  @@map("OrganizationMembership")
}

enum OrganizationRole {
  ADMIN
  AGENT
  VIEWER
}
```

#### Organization Settings

Configuration and preferences for each organization.

```prisma theme={null}
model OrganizationSettings {
  id             String @id @default(cuid())
  organizationId String @unique
  
  // Business Configuration
  timezone       String  @default("America/New_York")
  businessHours  Json?   // Store business hours as JSON
  
  // Feature Flags
  enableAIFeatures      Boolean @default(true)
  enableVoiceSearch     Boolean @default(true)
  enableSentimentAnalysis Boolean @default(true)
  
  // Communication Settings
  defaultEmailSignature String?
  smsNotifications      Boolean @default(true)
  emailNotifications    Boolean @default(true)
  
  // Lead Management
  autoAssignLeads       Boolean @default(false)
  leadScoringEnabled    Boolean @default(true)
  
  // Integration Settings
  mlsIntegrationEnabled Boolean @default(false)
  mlsCredentials        Json?   // Encrypted MLS credentials
  
  // Relationships
  organization Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)
  
  @@map("OrganizationSettings")
}
```

### User Management

#### User

Synchronized with Clerk authentication system.

```prisma theme={null}
model User {
  id        String   @id // Clerk user ID
  clerkId   String   @unique
  email     String   @unique
  firstName String?
  lastName  String?
  imageUrl  String?
  
  // User Preferences
  timezone           String?
  phoneSettings      Json?    // Phone preferences as JSON
  notificationSettings Json?  // Notification preferences
  
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  
  // Relationships
  organizationMemberships OrganizationMembership[]
  phoneNumbers           TwilioPhoneNumber[]
  communications         Communication[]
  callRecordings         CallRecording[]
  notes                  Note[]
  tasks                  Task[]
  createdDeals           Deal[]
  
  @@map("User")
}
```

### Contact Management

#### Person

Central entity for all contacts and leads.

```prisma theme={null}
model Person {
  id             String  @id @default(cuid())
  organizationId String
  
  // Basic Information
  firstName      String?
  lastName       String?
  email          String?
  phone          String?
  
  // Contact Details
  address        String?
  city           String?
  state          String?
  zipCode        String?
  country        String? @default("US")
  
  // Lead Information
  source         LeadSource?
  status         PersonStatus @default(PROSPECT)
  leadScore      Float?       @default(0)
  lastContactAt  DateTime?
  
  // Preferences
  preferredContactMethod ContactMethod @default(EMAIL)
  timezone              String?       @default("America/New_York")
  
  // AI Insights
  sentimentScore        Float?
  engagementScore       Float?
  conversionProbability Float?
  
  // Metadata
  notes          String?
  tags           String[] // Array of tag names
  customFields   Json?    // Flexible custom fields
  
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  
  // Relationships
  organization   Organization    @relation(fields: [organizationId], references: [id], onDelete: Cascade)
  deals          Deal[]
  communications Communication[]
  tasks          Task[]
  activities     Activity[]
  
  @@map("Person")
}

enum LeadSource {
  WEBSITE
  REFERRAL
  SOCIAL_MEDIA
  COLD_CALL
  EMAIL_CAMPAIGN
  EVENT
  PARTNER
  MLS
  ZILLOW
  CHROME_EXTENSION
  OTHER
}

enum PersonStatus {
  PROSPECT
  LEAD
  QUALIFIED
  ACTIVE_CLIENT
  PAST_CLIENT
  CLOSED_LOST
}

enum ContactMethod {
  EMAIL
  PHONE
  SMS
  MAIL
}
```

### Property Management

#### Property

Real estate listings and property information.

```prisma theme={null}
model Property {
  id             String @id @default(cuid())
  organizationId String
  
  // Basic Information
  address        String
  city           String
  state          String
  zipCode        String
  country        String @default("US")
  
  // Property Details
  propertyType   PropertyType
  bedrooms       Int?
  bathrooms      Float?
  squareFeet     Int?
  lotSize        Float?
  yearBuilt      Int?
  
  // Listing Information
  listingStatus  ListingStatus @default(ACTIVE)
  listPrice      Decimal?      @db.Decimal(15, 2)
  soldPrice      Decimal?      @db.Decimal(15, 2)
  listDate       DateTime?
  soldDate       DateTime?
  
  // MLS Integration
  mlsNumber      String?       @unique
  mlsStatus      String?
  mlsLastSync    DateTime?
  
  // Marketing
  description    String?
  features       String[]      // Array of property features
  virtualTourUrl String?
  
  // Coordinates for mapping
  latitude       Float?
  longitude      Float?
  
  // Metadata
  customFields   Json?
  
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  
  // Relationships
  organization Organization     @relation(fields: [organizationId], references: [id], onDelete: Cascade)
  deals        Deal[]
  images       PropertyImage[]
  mlsData      MLSData?
  activities   Activity[]
  
  @@map("Property")
}

enum PropertyType {
  SINGLE_FAMILY
  CONDO
  TOWNHOUSE
  MULTI_FAMILY
  LAND
  COMMERCIAL
  OTHER
}

enum ListingStatus {
  ACTIVE
  PENDING
  SOLD
  EXPIRED
  WITHDRAWN
  OFF_MARKET
}
```

#### Property Images

Property photos and media management.

```prisma theme={null}
model PropertyImage {
  id         String @id @default(cuid())
  propertyId String
  
  imageUrl   String
  caption    String?
  isPrimary  Boolean @default(false)
  sortOrder  Int     @default(0)
  
  createdAt DateTime @default(now())
  
  // Relationships
  property Property @relation(fields: [propertyId], references: [id], onDelete: Cascade)
  
  @@map("PropertyImage")
}
```

### Deal Management

#### Deal

Sales pipeline and transaction tracking.

```prisma theme={null}
model Deal {
  id             String @id @default(cuid())
  organizationId String
  
  // Basic Information
  title          String
  description    String?
  dealType       DealType
  
  // Financial Information
  amount         Decimal?      @db.Decimal(15, 2)
  commission     Decimal?      @db.Decimal(15, 2)
  commissionRate Float?        // Percentage
  
  // Timeline
  expectedCloseDate DateTime?
  actualCloseDate   DateTime?
  
  // Status and Stage
  status         DealStatus    @default(OPEN)
  stage          String        // Current pipeline stage
  probability    Float?        @default(50) // Close probability percentage
  
  // Assignments
  primaryAgentId String?
  teamMembers    String[]      // Array of user IDs
  
  // Relationships
  personId       String?       // Primary contact
  propertyId     String?       // Associated property
  
  // Metadata
  source         LeadSource?
  tags           String[]
  customFields   Json?
  
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  createdBy String
  
  // Relationships
  organization   Organization    @relation(fields: [organizationId], references: [id], onDelete: Cascade)
  person         Person?         @relation(fields: [personId], references: [id])
  property       Property?       @relation(fields: [propertyId], references: [id])
  creator        User            @relation(fields: [createdBy], references: [id])
  tasks          Task[]
  notes          Note[]
  communications Communication[]
  activities     Activity[]
  
  @@map("Deal")
}

enum DealType {
  LISTING
  BUYER_REPRESENTATION
  RENTAL
  COMMERCIAL
  OTHER
}

enum DealStatus {
  OPEN
  WON
  LOST
  ON_HOLD
}
```

## Communication System

### Unified Communication Tracking

```prisma theme={null}
model Communication {
  id             String @id @default(cuid())
  organizationId String
  
  // Communication Details
  type           CommunicationType
  direction      CommunicationDirection
  subject        String?
  body           String?
  
  // Participants
  fromUserId     String?           // Internal user who sent
  toPersonId     String?           // External contact who received
  ccEmails       String[]          // CC recipients
  bccEmails      String[]          // BCC recipients
  
  // External Integration IDs
  twilioCallSid      String?       @unique
  twilioMessageSid   String?       @unique
  nylasMessageId     String?       @unique
  nylasThreadId      String?
  
  // Status and Metadata
  status         CommunicationStatus @default(SENT)
  readAt         DateTime?
  deliveredAt    DateTime?
  sentAt         DateTime?
  
  // AI Analysis
  sentimentScore Float?
  keyTopics      String[]
  actionItems    String[]
  
  // Context
  dealId         String?
  propertyId     String?
  tags           String[]
  
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  
  // Relationships
  organization   Organization                  @relation(fields: [organizationId], references: [id], onDelete: Cascade)
  fromUser       User?                        @relation(fields: [fromUserId], references: [id])
  toPerson       Person?                      @relation(fields: [toPersonId], references: [id])
  deal           Deal?                        @relation(fields: [dealId], references: [id])
  attachments    CommunicationAttachment[]
  twilioCall     TwilioCall?
  
  @@map("Communication")
}

enum CommunicationType {
  EMAIL
  SMS
  PHONE_CALL
  VOICEMAIL
  VIDEO_CALL
  INTERNAL_NOTE
}

enum CommunicationDirection {
  INBOUND
  OUTBOUND
}

enum CommunicationStatus {
  DRAFT
  SENT
  DELIVERED
  READ
  FAILED
  BOUNCED
}
```

### Phone System Integration

#### Twilio Call Records

```prisma theme={null}
model TwilioCall {
  id             String @id @default(cuid())
  organizationId String
  
  // Twilio Data
  callSid        String  @unique
  parentCallSid  String?
  accountSid     String
  
  // Call Details
  from           String
  to             String
  direction      CallDirection
  status         CallStatus
  duration       Int?            // Duration in seconds
  price          String?
  priceUnit      String?
  
  // Participants
  fromUserId     String?
  toPersonId     String?
  
  // Recording and Transcription
  recordingUrl   String?
  recordingSid   String?
  transcriptionText String?
  transcriptionStatus TranscriptionStatus @default(PENDING)
  
  // AI Analysis
  sentimentScore Float?
  keyTopics      String[]
  callSummary    String?
  
  // Timestamps
  startTime      DateTime?
  endTime        DateTime?
  createdAt      DateTime @default(now())
  updatedAt      DateTime @updatedAt
  
  // Relationships
  organization   Organization    @relation(fields: [organizationId], references: [id], onDelete: Cascade)
  fromUser       User?          @relation(fields: [fromUserId], references: [id])
  toPerson       Person?        @relation(fields: [toPersonId], references: [id])
  communication  Communication? @relation(fields: [communicationId], references: [id])
  recording      CallRecording?
  
  @@map("TwilioCall")
}

enum CallDirection {
  INBOUND
  OUTBOUND
}

enum CallStatus {
  RINGING
  IN_PROGRESS
  COMPLETED
  BUSY
  FAILED
  NO_ANSWER
  CANCELED
}
```

#### Call Recordings and Transcriptions

```prisma theme={null}
model CallRecording {
  id         String @id @default(cuid())
  callSid    String @unique
  
  // Recording Details
  recordingUrl    String
  recordingSid    String  @unique
  duration        Int?    // Duration in seconds
  fileSize        Int?    // File size in bytes
  
  // Transcription
  transcriptionText   String?
  transcriptionStatus TranscriptionStatus @default(PENDING)
  retryCount         Int                 @default(0)
  lastError          String?
  lastRetryAt        DateTime?
  nextRetryAt        DateTime?
  
  // AI Processing
  sentimentAnalyzed  Boolean @default(false)
  keywordsExtracted  Boolean @default(false)
  
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  
  // Relationships
  call              TwilioCall           @relation(fields: [callSid], references: [callSid], onDelete: Cascade)
  transcriptSegments TranscriptSegment[]
  
  @@map("CallRecording")
}

enum TranscriptionStatus {
  PENDING
  PROCESSING
  COMPLETED
  FAILED
  RETRY_SCHEDULED
}
```

#### Transcript Segments

Detailed transcript data with speaker diarization.

```prisma theme={null}
model TranscriptSegment {
  id               String @id @default(cuid())
  callRecordingId  String
  
  // Segment Details
  speaker          String        // "agent" or "customer" or "unknown"
  text             String
  confidence       Float?        // Transcription confidence score
  startTime        Float         // Start time in seconds
  endTime          Float         // End time in seconds
  
  // AI Analysis
  sentiment        Float?        // Sentiment score for this segment
  emotions         String[]      // Detected emotions
  keywords         String[]      // Extracted keywords
  
  createdAt DateTime @default(now())
  
  // Relationships
  recording CallRecording @relation(fields: [callRecordingId], references: [id], onDelete: Cascade)
  
  @@map("TranscriptSegment")
}
```

## Task and Activity Management

### Tasks

```prisma theme={null}
model Task {
  id             String @id @default(cuid())
  organizationId String
  
  // Task Details
  title          String
  description    String?
  priority       TaskPriority @default(MEDIUM)
  status         TaskStatus   @default(PENDING)
  
  // Assignment and Context
  assignedToId   String?
  createdById    String
  personId       String?      // Related contact
  dealId         String?      // Related deal
  propertyId     String?      // Related property
  
  // Timeline
  dueDate        DateTime?
  completedAt    DateTime?
  
  // Metadata
  tags           String[]
  customFields   Json?
  
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  
  // Relationships
  organization Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)
  assignedTo   User?        @relation(fields: [assignedToId], references: [id])
  createdBy    User         @relation(fields: [createdById], references: [id])
  person       Person?      @relation(fields: [personId], references: [id])
  deal         Deal?        @relation(fields: [dealId], references: [id])
  
  @@map("Task")
}

enum TaskPriority {
  LOW
  MEDIUM
  HIGH
  URGENT
}

enum TaskStatus {
  PENDING
  IN_PROGRESS
  COMPLETED
  CANCELLED
}
```

### Activity Timeline

```prisma theme={null}
model Activity {
  id             String @id @default(cuid())
  organizationId String
  
  // Activity Details
  type           ActivityType
  title          String
  description    String?
  
  // Actor
  userId         String?      // Who performed the activity
  
  // Context
  personId       String?
  dealId         String?
  propertyId     String?
  
  // Metadata
  metadata       Json?        // Flexible activity data
  
  createdAt DateTime @default(now())
  
  // Relationships
  organization Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)
  user         User?        @relation(fields: [userId], references: [id])
  person       Person?      @relation(fields: [personId], references: [id])
  deal         Deal?        @relation(fields: [dealId], references: [id])
  property     Property?    @relation(fields: [propertyId], references: [id])
  
  @@map("Activity")
}

enum ActivityType {
  CONTACT_CREATED
  CONTACT_UPDATED
  DEAL_CREATED
  DEAL_STAGE_CHANGED
  DEAL_WON
  DEAL_LOST
  PROPERTY_LISTED
  PROPERTY_SOLD
  CALL_MADE
  EMAIL_SENT
  SMS_SENT
  MEETING_SCHEDULED
  TASK_COMPLETED
  NOTE_ADDED
  CHROME_ACTIVITY
}
```

## Multi-tenant Security

### Row-Level Security Policies

```sql theme={null}
-- Enable RLS on all organization-scoped tables
ALTER TABLE "Organization" ENABLE ROW LEVEL SECURITY;
ALTER TABLE "Person" ENABLE ROW LEVEL SECURITY;
ALTER TABLE "Property" ENABLE ROW LEVEL SECURITY;
ALTER TABLE "Deal" ENABLE ROW LEVEL SECURITY;
ALTER TABLE "Communication" ENABLE ROW LEVEL SECURITY;
ALTER TABLE "Task" ENABLE ROW LEVEL SECURITY;

-- Example policy for Person table
CREATE POLICY person_organization_access ON "Person"
  FOR ALL USING (
    "organizationId" = current_setting('app.current_organization', true)::text
  );

-- Example policy for Deal table
CREATE POLICY deal_organization_access ON "Deal"
  FOR ALL USING (
    "organizationId" = current_setting('app.current_organization', true)::text
  );
```

### Database Indexes

```sql theme={null}
-- Performance indexes for common queries
CREATE INDEX idx_person_organization_id ON "Person"("organizationId");
CREATE INDEX idx_person_email ON "Person"("email") WHERE "email" IS NOT NULL;
CREATE INDEX idx_person_phone ON "Person"("phone") WHERE "phone" IS NOT NULL;
CREATE INDEX idx_person_status ON "Person"("organizationId", "status");
CREATE INDEX idx_person_lead_score ON "Person"("organizationId", "leadScore" DESC);

CREATE INDEX idx_deal_organization_id ON "Deal"("organizationId");
CREATE INDEX idx_deal_status ON "Deal"("organizationId", "status");
CREATE INDEX idx_deal_stage ON "Deal"("organizationId", "stage");
CREATE INDEX idx_deal_amount ON "Deal"("organizationId", "amount" DESC);
CREATE INDEX idx_deal_close_date ON "Deal"("organizationId", "expectedCloseDate");

CREATE INDEX idx_property_organization_id ON "Property"("organizationId");
CREATE INDEX idx_property_status ON "Property"("organizationId", "listingStatus");
CREATE INDEX idx_property_location ON "Property"("city", "state", "zipCode");
CREATE INDEX idx_property_price ON "Property"("organizationId", "listPrice" DESC);

CREATE INDEX idx_communication_organization_id ON "Communication"("organizationId");
CREATE INDEX idx_communication_type ON "Communication"("organizationId", "type");
CREATE INDEX idx_communication_person ON "Communication"("organizationId", "toPersonId");
CREATE INDEX idx_communication_date ON "Communication"("organizationId", "createdAt" DESC);

-- Full-text search indexes
CREATE INDEX idx_person_search ON "Person" USING gin(
  to_tsvector('english', 
    coalesce("firstName", '') || ' ' || 
    coalesce("lastName", '') || ' ' || 
    coalesce("email", '')
  )
);

CREATE INDEX idx_property_search ON "Property" USING gin(
  to_tsvector('english', 
    coalesce("address", '') || ' ' || 
    coalesce("city", '') || ' ' || 
    coalesce("description", '')
  )
);
```

## Next Steps

<CardGroup cols={3}>
  <Card title="API Architecture" icon="code" href="/architecture/api-architecture">
    Learn how the API layer interacts with the database
  </Card>

  <Card title="Real-time Features" icon="bolt" href="/architecture/real-time-features">
    Understand WebSocket and collaboration features
  </Card>

  <Card title="Performance Optimization" icon="gauge" href="/development">
    Database optimization and scaling strategies
  </Card>
</CardGroup>

***

<Note>
  This database design is optimized for real estate workflows while maintaining flexibility for customization and future feature additions.
</Note>