Skip to content

Data Model

Jump to bottom
Shane Rounce edited this page May 12, 2026 · 2 revisions

Data model

The plugin creates its own tables using the active WordPress table prefix.

Core tables

ace_sessions

Stores the session spine:

  • session UUID and visitor UUID
  • first and last seen timestamps
  • landing path and referrer
  • UTM values
  • IP and browser hashes
  • raw IP with expiry where retained
  • location/provider enrichment fields
  • linked company ID
  • bot and ignored flags

ace_events

Stores tracked events linked back to sessions:

  • event UUID
  • event type and name
  • URL, path, and page title
  • post and taxonomy context
  • commerce-related context fields
  • linked number ID
  • metadata
  • occurrence timestamps

ace_companies

Stores company-level enrichment and roll-up data:

  • company name and domain
  • provider/source information
  • confidence state
  • first and last seen
  • total sessions, events, and calls
  • notes and ignore state

ace_numbers

Stores number-routing rules:

  • label
  • display number
  • E.164 number
  • source and page matching rules
  • campaign matching
  • Amazon Connect identifiers
  • default and active flags
  • priority

ace_calls

Stores call-level data:

  • call UUID
  • Amazon contact ID
  • number relationship
  • called number
  • caller hash and raw value with expiry
  • start, end, and duration
  • direction and status
  • queue and agent names
  • matched session and company IDs
  • match confidence
  • attributes and notes

ace_enrichment_cache

Stores cached enrichment responses:

  • hashed IP
  • provider
  • provider response payload
  • company details
  • ASN and ISP
  • confidence
  • expiry

ace_chat_conversations

Stores the commercial and workflow shell for frontend assistant chats:

  • conversation UUID, session UUID, and visitor UUID
  • linked company ID
  • provider and model
  • message counts by role
  • chat status and handover state
  • commercial workflow status, outcome, priority, owner, and notes
  • follow-up and contact fields
  • company/contact prompt timestamps
  • start, last-message, and end timestamps

ace_chat_messages

Stores the transcript itself:

  • linked conversation, session, and company IDs
  • message role
  • message text
  • source payloads
  • model
  • optional operator user ID
  • stored author name and avatar URL
  • created timestamp

ace_ip_company_memory

Stores lightweight local memory for repeat visitors from the same network:

  • hashed IP
  • linked or inferred company
  • remembered contact name, email, phone, and role
  • evidence/source metadata
  • first and last seen timestamps

WordPress options

Alongside the custom tables, the plugin stores settings and saved reporting data in options, including:

  • ace_settings
  • ace_hash_salt
  • ace_reporting_segments
  • ace_schema_version

Design intent

I want the tables to be direct and operational.

The schema is meant to support:

  • first-party tracking
  • reporting drilldowns
  • future call matching
  • enrichment reuse
  • chat reporting, handover, and workflow state
  • lightweight lead-memory reuse for repeat visitors
  • saved reporting workflows

It is not meant to become a generic event lake with no clear reporting purpose.

Clone this wiki locally