Database Design - Winnerr CRM

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.

Core Entities

Organization Management

Organization

The root entity for multi-tenant data isolation.

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.

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.

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.

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.

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.

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.

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.

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

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

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

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.

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

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

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

-- 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

-- 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

API Architecture

Learn how the API layer interacts with the database

Real-time Features

Understand WebSocket and collaboration features

Performance Optimization

Database optimization and scaling strategies

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

​Schema Overview

​Core Entities

​Organization Management

​Organization

​Organization Membership

​Organization Settings

​User Management

​User

​Contact Management

​Person

​Property Management

​Property

​Property Images

​Deal Management

​Deal

​Communication System

​Unified Communication Tracking

​Phone System Integration

​Twilio Call Records

​Call Recordings and Transcriptions

​Transcript Segments

​Task and Activity Management

​Tasks

​Activity Timeline

​Multi-tenant Security

​Row-Level Security Policies

​Database Indexes

​Next Steps

API Architecture

Real-time Features

Performance Optimization

Schema Overview

Core Entities

Organization Management

Organization

Organization Membership

Organization Settings

User Management

User

Contact Management

Person

Property Management

Property

Property Images

Deal Management

Deal

Communication System

Unified Communication Tracking

Phone System Integration

Twilio Call Records

Call Recordings and Transcriptions

Transcript Segments

Task and Activity Management

Tasks

Activity Timeline

Multi-tenant Security

Row-Level Security Policies

Database Indexes

Next Steps