DocTypes

Property

• property_id: Primary Key
• property_name: String
• property_type: Enum (Industrial, Office, Land)
• address: String
• city: String
• state: String
• postal_code: String
• country: String
• description: Text
• owner_id: Foreign Key (Contact or Lead)
• businessentityid: Foreign Key (Business_Entity)

Property_Image

• image_id: Primary Key
• property_id: Foreign Key (Property)
• image_url: String
• description: Text

Inquiry

• inquiry_id: Primary Key
• property_id: Foreign Key (Property)
• lead_id: Foreign Key (Lead)
• inquiry_date: Date
• user_id: Foreign Key (User)
• inquiry_status: Enum (New, In Progress, Closed)

Viewing

• viewing_id: Primary Key
• property_id: Foreign Key (Property)
• lead_id: Foreign Key (Lead)
• viewing_date: Date
• user_id: Foreign Key (User)

Lease_Agreement

• leaseagreementid: Primary Key
• property_id: Foreign Key (Property)
• lead_id: Foreign Key (Lead)
• start_date: Date
• end_date: Date
• rent_amount: Currency
• currency: String
• user_id: Foreign Key (User)

Sale_Transaction

• saletransactionid: Primary Key
• property_id: Foreign Key (Property)
• lead_id: Foreign Key (Lead)
• transaction_date: Date
• sale_amount: Currency
• currency: String
• user_id: Foreign Key (User)

User

• user_id: Primary Key
• first_name: String
• last_name: String
• email: String
• phone: String
• role: Enum (Admin, Broker, Agent)
• team_id: Foreign Key (Team)

Contact

• contact_id: Primary Key
• first_name: String
• last_name: String
• email: String
• phone: String
• company: String
• lead_id: Foreign Key (Lead)

Lead

• lead_id: Primary Key
• first_name: String
• last_name: String
• email: String
• phone: String
• company: String
• source: Enum (Cold Call, Referral, Advertisement, etc.)
• initialcontactdate: Date
• user_id: Foreign Key (User)

Opportunity

• opportunity_id: Primary Key
• lead_id: Foreign Key (Lead)
• property_id: Foreign Key (Property)
• opportunity_type: Enum (Sale, Lease)
• stage: Enum (Customizable)
• probability: Float
• user_id: Foreign Key (User)

Task

• task_id: Primary Key
• assignedtoid: Foreign Key (User)
• property_id: Foreign Key (Property)
• description: Text
• due_date: Date
• status: Enum (Not Started, In Progress, Completed, Deferred)
• priority: Enum (Low, Medium, High)

Meeting

• meeting_id: Primary Key
• property_id: Foreign Key (Property)
• organizer_id: Foreign Key (User)
• title: String
• description: Text
• start_time: DateTime
• end_time: DateTime
• location: String
• attendees: Many-to-Many Relationship (User)

Email

• email_id: Primary Key
• sender_id: Foreign Key (User)
• recipient_id: Foreign Key (User, Contact or Lead)
• subject: String
• content: Text
• timestamp: DateTime
• status: Enum (Sent, Received, Opened, Clicked)

SMS

• sms_id: Primary Key
• sender_id: Foreign Key (User)
• recipient_id: Foreign Key (User, Contact or Lead)
• content: Text
• timestamp: DateTime
• status: Enum (Sent, Received, Delivered)

Reminder

• reminder_id: Primary Key
• user_id: Foreign Key (User)
• title: String
• description: Text
• timestamp: DateTime
• status: Enum (Pending, Completed, Dismissed)

Commission

• commission_id: Primary Key
• user_id: Foreign Key (User)
• opportunity_id: Foreign Key (Opportunity)
• commission_amount: Currency
• currency: String
• commission_date: Date

Document_Template

• template_id: Primary Key
• template_name: String
• template_type: Enum (Proposal, Presentation, Lease Agreement, Sale Agreement, etc.)
• content: Text (or use a separate table for storing template sections)

Email_Campaign

• campaign_id: Primary Key
• campaign_name: String
• target_segment: Text
• templateid: Foreign Key (DocumentTemplate)
• start_date: Date
• end_date: Date
• user_id: Foreign Key (User)

Notification

• notification_id: Primary Key
• user_id: Foreign Key (User)
• notification_type: Enum (Email, SMS, Reminder, Event, Task)
• relatedentityid: Foreign Key (Lead, Contact, Property, Opportunity, etc.)
• relatedentitytype: Enum (Lead, Contact, Property, Opportunity, etc.)
• message: Text
• timestamp: DateTime
• status: Enum (Unread, Read, Archived)

Team

• team_id: Primary Key
• team_name: String
• members: Many-to-Many Relationship (User)

Business_Entity

• entity_id: Primary Key
• entity_name: String
• entity_type: Enum (LLC, Corporation, Partnership, etc.)
• address: String
• city: String
• state: String
• postal_code: String
• country: String

Property_Valuation

• valuation_id: Primary Key
• property_id: Foreign Key (Property)
• valuation_date: Date
• estimated_value: Currency
• currency: String
• valuation_method: Enum (Comparable Sales, Income Capitalization, Cost Approach, etc.)
• user_id: Foreign Key (User)

LeadPropertyAssociation

• leadpropertyassociation_id: Primary Key
• lead_id: Foreign Key (Lead)
• property_id: Foreign Key (Property)

LeadDealAssociation

• leaddealassociation_id: Primary Key
• lead_id: Foreign Key (Lead)
• deal_id: Foreign Key (Opportunity)

Bulk_Email

• bulkemailid: Primary Key
• emailtemplateid: Foreign Key (Document_Template)
• recipient_list: Text (consider using a separate table for storing recipient email addresses)
• sent_date: Date
• user_id: Foreign Key (User)

Email_Thread

• thread_id: Primary Key
• subject: String
• relatedentityid: Foreign Key (Lead, Contact, Property, Opportunity, etc.)
• relatedentitytype: Enum (Lead, Contact, Property, Opportunity, etc.)

EmailThreadParticipant

• participant_id: Primary Key
• threadid: Foreign Key (EmailThread)
• participant_email: String

Call

• call_id: Primary Key
• caller_id: Foreign Key (User)
• recipient_id: Foreign Key (User, Contact or Lead)
• start_time: DateTime
• end_time: DateTime
• call_notes: Text
• call_outcome: Enum (Successful, Unsuccessful, No Answer, etc.)

Metrics

• metric_id: Primary Key
• user_id: Foreign Key (User)
• metric_type: Enum (Calls, Meetings, Emails, SMS, Opportunities, Closed Deals, etc.)
• metric_value: Integer
• timestamp: DateTime

Inbox_Item

• inboxitemid: Primary Key
• user_id: Foreign Key (User)
• item_type: Enum (Email, Meeting, Call, SMS, Reminder, Event, Task)
• relatedentityid: Foreign Key (Lead, Contact, Property, Opportunity, etc.)
• relatedentitytype: Enum (Lead, Contact, Property, Opportunity, etc.)
• status: Enum (Active, Open, Completed, Archived)