Database Schema

Clientra CRM uses MySQL/MariaDB with the database name invejrek_crmmain. All tables use utf8mb4 character set and utf8mb4_unicode_ci collation for full Unicode support.

ℹ Soft Deletes

Most entity tables (properties, leads, contacts, deals, owners) use soft deletes — records are marked with a deleted_at timestamp rather than being physically removed. Soft-deleted records are moved to the crm_trash table for recovery.

Core Tables

properties

The main property listings table. Each row represents a real estate property in the CRM.

Column	Type	Null	Default	Description
`id`	INT UNSIGNED	NO	—	Primary key (auto-increment)
`ref`	VARCHAR(20)	NO	—	Unique reference number (e.g., PROP-001)
`status`	ENUM	NO	'Available'	Available / Reserved / Sold / Rented / Off Market
`purpose`	ENUM	NO	'Sale'	Sale / Rent
`type`	VARCHAR(50)	YES	NULL	Apartment, Villa, Office, Townhouse, etc.
`location_id`	INT UNSIGNED	YES	NULL	FK → locations.id
`community_id`	INT UNSIGNED	YES	NULL	FK → communities.id
`unit_number`	VARCHAR(20)	YES	NULL	Unit/flat number
`bedrooms`	TINYINT	YES	NULL	Bedroom count (0 = Studio)
`bathrooms`	TINYINT	YES	NULL	Bathroom count
`size_sqft`	DECIMAL(10,2)	YES	NULL	Built-up area in sq. ft.
`plot_area`	DECIMAL(10,2)	YES	NULL	Plot area in sq. ft. (villas)
`price`	DECIMAL(15,2)	YES	NULL	Asking price
`owner_id`	INT UNSIGNED	YES	NULL	FK → owners.id
`project_id`	INT UNSIGNED	YES	NULL	FK → real_estate_projects.id
`cover_image_id`	INT UNSIGNED	YES	NULL	FK → documents.id (cover photo)
`created_by`	INT UNSIGNED	YES	NULL	FK → users.id
`created_at`	TIMESTAMP	NO	CURRENT_TIMESTAMP	Record creation time
`updated_at`	TIMESTAMP	YES	NULL	Last update time
`deleted_at`	TIMESTAMP	YES	NULL	Soft delete timestamp

leads

Column	Type	Null	Default	Description
`id`	INT UNSIGNED	NO	—	Primary key
`ref`	VARCHAR(20)	NO	—	Reference (e.g., LEAD-001)
`name`	VARCHAR(150)	NO	—	Lead's full name
`phone`	VARCHAR(30)	YES	NULL	Primary phone number
`email`	VARCHAR(150)	YES	NULL	Email address
`status`	ENUM	NO	'New'	New / Contacted / Viewing / Offer / Won / Lost
`source`	VARCHAR(50)	YES	NULL	Lead source (Website, Meta Ads, Referral, etc.)
`campaign`	VARCHAR(100)	YES	NULL	Marketing campaign name
`priority`	ENUM	NO	'Normal'	Low / Normal / High / Urgent
`budget_min`	DECIMAL(15,2)	YES	NULL	Minimum budget
`budget_max`	DECIMAL(15,2)	YES	NULL	Maximum budget
`property_type`	VARCHAR(50)	YES	NULL	Preferred property type
`bedrooms`	VARCHAR(20)	YES	NULL	Preferred bedroom count
`location_id`	INT UNSIGNED	YES	NULL	Preferred location FK
`assigned_to`	INT UNSIGNED	YES	NULL	FK → users.id (assigned agent)
`followup_date`	DATE	YES	NULL	Next follow-up date
`created_at`	TIMESTAMP	NO	CURRENT_TIMESTAMP
`deleted_at`	TIMESTAMP	YES	NULL	Soft delete timestamp

contacts

Column	Type	Description
`id`	INT UNSIGNED	Primary key
`ref`	VARCHAR(20)	Reference number (e.g., CON-001)
`name`	VARCHAR(150)	Full name
`phone`	VARCHAR(30)	Primary phone
`phone2`	VARCHAR(30)	Secondary phone
`email`	VARCHAR(150)	Email address
`type`	ENUM	Agent / Client / Developer / Other
`status`	ENUM	Active / Inactive / VIP
`nationality`	VARCHAR(80)	Country of nationality
`passport_number`	VARCHAR(50)	Passport number
`emirates_id`	VARCHAR(20)	UAE Emirates ID (UAE Edition)
`dob`	DATE	Date of birth
`language`	VARCHAR(30)	Preferred language
`created_at`	TIMESTAMP	Creation timestamp
`deleted_at`	TIMESTAMP	Soft delete timestamp

deals

Column	Type	Description
`id`	INT UNSIGNED	Primary key
`ref`	VARCHAR(20)	Reference (e.g., DEAL-001)
`title`	VARCHAR(200)	Deal title/description
`stage`	ENUM	new / contacted / viewing / offer / negotiation / won / lost
`value`	DECIMAL(15,2)	Deal value (sale/rent amount)
`commission_amount`	DECIMAL(15,2)	Expected commission
`lead_id`	INT UNSIGNED	FK → leads.id (source lead)
`property_id`	INT UNSIGNED	FK → properties.id
`contact_id`	INT UNSIGNED	FK → contacts.id
`assigned_to`	INT UNSIGNED	FK → users.id
`created_at`	TIMESTAMP	Creation timestamp
`closed_at`	TIMESTAMP	When deal was won/lost
`deleted_at`	TIMESTAMP	Soft delete timestamp

owners

Column	Type	Description
`id`	INT UNSIGNED	Primary key
`OwnerRef`	VARCHAR(50)	Reference (e.g., OWN-001)
`Name`	VARCHAR(100)	Owner full name
`Name2`	VARCHAR(100)	Secondary name (e.g., Arabic name)
`Phone`	VARCHAR(50)	Primary phone number
`Mobile2`	VARCHAR(50)	Secondary phone number
`Email`	VARCHAR(100)	Primary email address
`Email2`	VARCHAR(100)	Secondary email address
`Location`	VARCHAR(100)	Location (from lookups)
`Community`	VARCHAR(100)	Community (from lookups, filtered by location)
`UnitNumber`	VARCHAR(50)	Unit number reference
`ProjectName`	VARCHAR(255)	Linked project name
`ProjectRef`	VARCHAR(50)	Linked project reference
`Price`	DECIMAL(15,2)	Property price
`Status`	VARCHAR(50)	Owner status (Active, Inactive, Prospect, VIP, Blacklisted)
`Notes`	TEXT	Owner notes (preserves line breaks)
`WhatsappSent`	TINYINT(1)	WhatsApp status (0=Pending, 1=Sent)
`created_at`	TIMESTAMP	Creation timestamp
`updated_at`	TIMESTAMP	Last modification timestamp

units

⚠ Table Name: units (not real_estate_units)

The unit inventory table is named units. There is no real_estate_units table. The global search and all queries must reference units.

Column	Type	Description
`id`	INT UNSIGNED	Primary key
`ref`	VARCHAR(20)	Unit reference number
`project_id`	INT UNSIGNED	FK → real_estate_projects.id
`unit_number`	VARCHAR(20)	Unit identifier (e.g., 1201)
`type`	VARCHAR(50)	Apartment, Studio, Penthouse, etc.
`bedrooms`	TINYINT	Bedroom count
`bathrooms`	TINYINT	Bathroom count
`size_sqft`	DECIMAL(10,2)	Size in square feet
`floor`	SMALLINT	Floor number
`view`	VARCHAR(50)	View type
`price`	DECIMAL(15,2)	Unit price
`status`	ENUM	Available / Reserved / Sold / Rented
`furnished`	ENUM	Furnished / Unfurnished / Semi

Project Tables

real_estate_projects

Column	Type	Description
`id`	INT UNSIGNED	Primary key
`ref`	VARCHAR(20)	Project reference
`name`	VARCHAR(200)	Project name
`developer_id`	INT UNSIGNED	FK → developers.id
`location_id`	INT UNSIGNED	FK → locations.id
`community_id`	INT UNSIGNED	FK → communities.id
`total_units`	INT	Total unit count in project
`available_units`	INT	Currently available units
`starting_price`	DECIMAL(15,2)	Lowest unit price
`handover_date`	DATE	Projected completion date
`broker_commission`	DECIMAL(5,2)	Commission % for brokers
`latitude`	DECIMAL(10,7)	GPS latitude
`longitude`	DECIMAL(10,7)	GPS longitude
`amenities`	JSON	Amenity list as JSON array
`layouts_data`	JSON	Unit type breakdown data

Lookup Tables

The following tables provide dynamic dropdown data throughout the application:

Table	Description	Key Columns
`developers`	Real estate developers/builders	id, name, country, website
`locations`	Emirates and areas	id, name, emirate
`communities`	Sub-communities and buildings	id, name, location_id
`project_names`	Linked project name lookup	id, name, developer_id
`phases`	Project phase identifiers	id, name, project_id

Communication Tables

communications

The unified communications table stores all interaction records across all channels and entity types.

Column	Type	Description
`id`	INT UNSIGNED	Primary key
`type`	ENUM	email / call / whatsapp / sms / meeting / note
`status`	ENUM	draft / sent / delivered / read / failed / logged
`entity_type`	VARCHAR(30)	Entity type: lead, deal, contact, property, owner
`entity_id`	INT UNSIGNED	ID of the linked entity
`user_id`	INT UNSIGNED	FK → users.id (sender/logger)
`contact_id`	INT UNSIGNED	FK → contacts.id (recipient)
`subject`	VARCHAR(255)	Email subject or call summary
`body`	TEXT	Message content (use body — NOT comment)
`direction`	ENUM	inbound / outbound
`duration`	SMALLINT	Call duration in seconds
`created_at`	TIMESTAMP	Record creation time
`updated_at`	TIMESTAMP	Last update time

ℹ Legacy Table: lead_communications

The old lead_communications table still exists during migration. All new code must use the unified communications table. The migration to a single table is in progress.

Other Communication Tables

Table	Description
`email_accounts`	IMAP/SMTP account configurations (passwords encrypted with app_key)
`email_account_permissions`	Per-user access to email accounts
`email_cache`	Cached email headers for performance
`email_templates`	Reusable email templates
`chat_messages`	Internal team chat messages
`notifications`	In-app notification records per user

Financial Tables

Table	Key Columns	Description
`contracts`	id, ref, type, status, deal_id, property_id, value, ejari_number, tawtheeq_number	Contract records with UAE-specific regulatory fields
`invoices`	id, ref, status, deal_id, contact_id, subtotal, vat_amount, total, due_date	Financial invoices with VAT calculation
`payments`	id, invoice_id, amount, payment_date, method, reference	Payment records against invoices
`commissions`	id, deal_id, agent_id, amount, agent_share, company_share, status, approved_at, paid_at	Commission records with approval workflow

HR Tables

Table	Key Columns	Description
`hr_employees`	id, user_id, employee_id, department_id, job_title, salary, join_date, status	Employee records linked to CRM users
`hr_attendance`	id, employee_id, date, status (present/absent/leave/holiday), check_in, check_out	Daily attendance records
`hr_leaves`	id, employee_id, type (annual/sick/emergency), from_date, to_date, status, approved_by	Leave requests and approvals
`hr_payroll`	id, employee_id, month, year, basic_salary, allowances, deductions, net_salary, status	Monthly payroll records
`departments`	id, name, head_user_id, description	Department structure (Listing, Sales, HR, etc.)
`teams`	id, name, department_id, team_lead_id	Teams within departments
`team_members`	id, team_id, user_id, role	Team membership records

System Tables

Table	Description
`users`	CRM user accounts (id, username, email, full_name, role, password_hash, active, created_at)
`app_settings`	Key-value store for application settings (key, value, updated_at)
`crm_features`	Feature flag toggles (feature_key, enabled, edition)
`crm_modules`	Module registry for navigation and routing
`crm_trash`	Soft-deleted records staging (entity_type, entity_id, data JSON, deleted_at, deleted_by)
`crm_audit_changes`	Field-level change tracking (entity_type, entity_id, field, old_value, new_value, changed_by, changed_at)
`activity_log`	High-level activity log (user_id, action, entity_type, entity_id, description, ip_address, created_at)
`tasks`	Task records (id, title, status, priority, event_type, entity_type, entity_id, assigned_to, due_at)
`documents`	File attachment records (id, entity_type, entity_id, filename, original_name, mime_type, size, storage_path)
`pdf_templates`	PDF document template definitions
`pdf_letterheads`	Company letterhead configurations
`pdf_documents`	Generated PDF document records

RBAC Tables

The Role-Based Access Control system uses multiple tables to provide granular permission management at both role and user levels.

Table	Description
`role_permissions`	Permissions granted to each role (role, module, action, allowed)
`user_permissions`	Per-user permission overrides that supersede role-based permissions
`user_feature_overrides`	Per-user feature flag overrides
`user_module_visibility`	Controls which sidebar modules a user can see
`entity_responsibilities`	Primary/secondary agent assignment per entity

Addon Tables

Table	Description
`addons`	Installed addon registry (slug, name, version, enabled, installed_at)
`meta_accounts`	Meta (Facebook) ad account configurations
`meta_form_mappings`	Field mappings from Meta lead forms to CRM lead fields
`meta_lead_queue`	Incoming leads from Meta webhook pending processing
`meta_settings`	Meta integration global settings
`meta_webhook_logs`	Raw webhook event logs from Meta
`cold_call_imports`	Cold call list import batches
`cold_call_records`	Individual cold call contact records with assignment and status
`aw_api_keys`	API key authentication records
`aw_api_logs`	API request access logs
`aw_oauth_clients`	OAuth 2.0 client registrations
`aw_rate_limits`	Rate limiting tracking per API key/IP
`aw_webhooks`	Outbound webhook subscriptions
`automation_rules`	Automation rule definitions (trigger, conditions, actions JSON)
`automation_log`	Automation rule execution history