Complete schema reference for the WPResidence real estate CRM. All tables are created by libs/db-setup.php via dbDelta. The plugin intentionally uses custom tables rather than CPTs to keep CRM queries fast at scale.
The Nine Tables
| Table | Column count | Primary purpose |
|---|---|---|
| wp_wpresidence_crm_contacts | 44 | Master contact entity |
| wp_wpresidence_crm_leads | 14 | Per-touchpoint lead rows |
| wp_wpresidence_crm_enquiries | 28 | Raw form submissions with property filters |
| wp_wpresidence_crm_deals | 33 | Pipeline deals (dual-name columns) |
| wp_wpresidence_crm_tasks | 19 | Todo items (dual-name columns) |
| wp_wpresidence_crm_activities | 14 | Append-only audit log |
| wp_wpresidence_crm_notes | 6 | Polymorphic notes |
| wp_wpresidence_crm_viewed_listings | 4 | Contact viewing history |
| wp_wpresidence_crm_automations | 12 | Automation rules |
Foreign-Key Graph
No physical FK constraints (WordPress convention); logical relationships enforced in code:
- contacts.assigned_agent_id → wp_users.ID
- contacts.user_id → wp_users.ID (creator)
- contacts.wp_user_id → wp_users.ID (if the contact has a WP login)
- leads.contact_id → contacts.contact_id
- leads.handler_agent_id → wp_users.ID
- enquiries.contact_id → contacts.contact_id
- enquiries.lead_id → leads.lead_id
- enquiries.property_id → wp_posts.ID (estate_property)
- deals.contact_id → contacts.contact_id
- deals.listing_id → wp_posts.ID
- deals.assigned_agent_id → wp_users.ID
- tasks.contact_id → contacts.contact_id
- tasks.deal_id → deals.deal_id
- activities.contact_id / deal_id / task_id / listing_id → respective tables
- notes.entity_type + entity_id → polymorphic reference
- viewed_listings.contact_id → contacts.contact_id
- viewed_listings.listing_id → wp_posts.ID
Indexing Strategy
- Email columns are indexed on contacts, leads, enquiries for duplicate detection.
- user_id indexed for per-agent scope queries.
- Status and stage columns indexed for filter performance.
- Date columns (due_date, created_at, viewed_at) indexed for range queries.
- Composite indexes where justified: (entity_type, entity_id) on activities and notes; (contact_id, viewed_at) on viewed_listings.
Dual Column Names
Deals, tasks, and activities all have legacy column names preserved alongside new planned names:
| Table | Legacy | New |
|---|---|---|
| deals | deal_title | title |
| deals | deal_stage | stage |
| deals | deal_status | status |
| deals | deal_probability | probability |
| tasks | task_title | title |
| tasks | task_status | status |
| tasks | task_type | type |
| tasks | task_priority | priority |
| activities | entity_id | contact_id |
| activities | action | type |
Writes populate both columns. Reads can use either. The old columns will be dropped in a future major version once all code paths are migrated.
Legacy CPTs
Two post types are registered but hidden from UI:
- wpestate_crm_contact – post-types/contacts.php. Registers a status taxonomy wpestate-crm-contact-status.
- wpestate_crm_lead – post-types/leads.php. Registers wpestate-crm-lead-status.
Both set show_in_menu: false. Admin metaboxes are defined in libs/metaboxes.php and render contact/lead field arrays using the field renderer. New CRM data is NOT written to these CPTs; they exist only to hold migrated legacy data that the activation routine imports into the custom tables.
Options Used by the CRM
- wpestate_crm_settings – full plugin configuration (serialized array).
- wpestate_crm_db_version – current schema version.
- wpestate_crm_permissions – role-based matrix.
- wpestate_crm_webhooks – structured webhook config (alternative to the simple URL setting).
- wpestate_crm_cpt_migrated – migration flag.
- wpestate_crm_schema_v2_migrated – schema migration flag.
- wpestate_crm_hubspot_migrated – HubSpot settings migration flag.
Query Patterns
- $wpdb->get_results() with $wpdb->prepare().
- Ownership WHERE from wpestate_crm_get_ownership_where() concatenated into prepared queries.
- Bulk ops use IN (%d, %d, %d, …) placeholders generated from the sanitized integer array.
- Pipeline summary uses GROUP BY stage, SUM(deal_value), COUNT(*) – one round trip to build the board totals.
Recommendations for Customization
- Do not add columns to existing CRM tables from outside the plugin. Instead, store structured extra data as JSON in the existing meta/enquiry_meta/action_config/conditions longtext columns.
- If you must add tables, use your own prefix (not wp_wpresidence_crm_) and handle dbDelta in your own plugin’s activation hook.
- Do not modify the post-types/ legacy CPTs unless you are absolutely sure you are not on a system with pending migration.
Uninstall
uninstall.php DROPs every CRM table and deletes every CRM option – irreversible. Only triggered when the plugin is deleted (not on deactivation).