Home / / WPResidence CRM Database — Developer Reference

WPResidence CRM Database — Developer Reference

2 views

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