Skip to content

Database Overview

This document provides a comprehensive overview of the AngelCX database schema and data flows. The database is designed to support the core functionalities of the AngelCX platform, including customer management, AI agent configuration, conversation handling, and analytics.

Data Flow Overview

sequenceDiagram Customer->>Plan: Selects Plan->>Contract: Creates Customer->>Agent: Configures Agent->>Prompt: Attaches Default Visitor->>Session: Initiates Session->>Message: Generates Session->>Analytics: Processes Session->>Email: Triggers (Optional)

Core Tables and Relationships

Customer Management Flow

Customer Table

Stores information about AngelCX platform clients who create and manage AI agents.

Column Type Description
customer_id INTEGER Primary identifier for the customer (auto-incrementing)
cognito_id VARCHAR AWS Cognito identity for authentication
name VARCHAR Customer's business name (unique)
email VARCHAR Primary contact email (unique)
phone VARCHAR Contact phone number
stripe_customer_id TEXT Stripe customer reference for billing

Plan Table

Contains pricing plan configurations and features.

Column Type Description
plan_id INTEGER Primary identifier for the plan
name VARCHAR Plan name (unique)
description VARCHAR Plan description and features
cadence VARCHAR Billing frequency (monthly/yearly)
cost_usd FLOAT4 Plan cost in USD
plan_access_id INTEGER Reference to plan features

Plan Access Table

Defines the feature set available for each plan.

Column Type Description
plan_access_id INTEGER Primary identifier
max_call_minutes INTEGER Maximum voice call minutes allowed
max_text_messages INTEGER Maximum text messages allowed
customize_bot_color BOOLEAN Allow bot color customization
customize_bot_avatar BOOLEAN Allow bot avatar customization
customize_bot_voice BOOLEAN Allow bot voice customization
customize_bot_name BOOLEAN Allow bot name customization
send_emails BOOLEAN Allow email sending feature
max_file_uploads INTEGER Maximum file upload limit
max_web_sources INTEGER Maximum web sources allowed
call_recordings BOOLEAN Allow call recording feature

Contract Table

Links customers with their selected plans.

Column Type Description
contract_id INTEGER Primary identifier
customer_id INTEGER Reference to customer
plan_id INTEGER Reference to selected plan
plan_access_id INTEGER Reference to plan features
expiry_date TIMESTAMPTZ Contract expiration date
refreshed_date TIMESTAMPTZ Last contract renewal date

Agent Configuration Flow

Agent Table

Stores AI agent configurations created by customers.

Column Type Description
agent_id INTEGER Primary identifier
name VARCHAR Agent's display name
description VARCHAR Agent's description
brand_info VARCHAR Brand configuration
customer_id INTEGER Owner customer reference
prompt_id INTEGER Default conversation prompt
prompt_override VARCHAR Custom prompt override
temperature FLOAT8 LLM temperature setting
corpus_id VARCHAR Reference to training data
voice VARCHAR Voice configuration
ui_config JSONB UI customization settings

Prompt Template Table

Categorizes different types of prompts.

Column Type Description
id INTEGER Primary identifier
name VARCHAR Template name
description VARCHAR Template description

Prompt Table

Stores actual prompt configurations.

Column Type Description
prompt_id INTEGER Primary identifier
name VARCHAR Prompt name
description VARCHAR Prompt description
content VARCHAR Actual prompt text
prompt_template_id INTEGER Reference to template

Conversation Flow

Visitor Table

Records website visitors interacting with agents.

Column Type Description
visitor_id INTEGER Primary identifier
name VARCHAR Visitor's name
phone VARCHAR Contact phone
email VARCHAR Contact email

Session Table

Tracks conversation sessions.

Column Type Description
session_id INTEGER Primary identifier
visitor_id INTEGER Reference to visitor
ultravox_call_id VARCHAR Voice call reference
agent_id INTEGER Reference to agent
is_active BOOLEAN Session status
ended_at TIMESTAMPTZ Session end time
created_at TIMESTAMPTZ Session start time
call_minutes_used NUMERIC(5,2) Voice minutes consumed
text_messages_used INTEGER Text messages used

Message Table

Stores conversation messages.

Column Type Description
message_id INTEGER Primary identifier
session_id INTEGER Reference to session
role VARCHAR Message sender (agent/visitor)
content VARCHAR Message content
timestamp TIMESTAMPTZ Message timestamp

Post-Session Processing

Analytics Table

Stores conversation insights.

Column Type Description
analytics_id INTEGER Primary identifier
session_id INTEGER Reference to session
call_summary VARCHAR Conversation summary
intent VARCHAR Detected user intent
sentiment FLOAT8 Conversation sentiment score
sentiment_justification VARCHAR Sentiment explanation
category VARCHAR Conversation category
category_description VARCHAR Category details
action VARCHAR Recommended action
created_at TIMESTAMP Analysis timestamp
sentiment_detailed JSONB Detailed sentiment data

Email Communication Tables

Email Table

Stores email templates.

Column Type Description
email_pk INTEGER Primary identifier
email_body TEXT Email template content
sender_email VARCHAR(255) Sender address
email_subject VARCHAR(500) Email subject
customer_id INTEGER Template owner
agent_id INTEGER Associated agent
created_at TIMESTAMPTZ Creation timestamp
updated_at TIMESTAMPTZ Last update time
is_active BOOLEAN Template status
Email Log Table

Tracks sent emails.

Column Type Description
email_log_id INTEGER Primary identifier
visitor_id INTEGER Recipient visitor
agent_id INTEGER Sending agent
session_id INTEGER Associated session
recipient_email VARCHAR(255) Recipient address
sent_at TIMESTAMP Send timestamp
email_pk INTEGER Used template
customer_id INTEGER Owner customer

Integration Tables

Custom Tool Table

Stores agent integration configurations.

Column Type Description
custom_tool_id INTEGER Primary identifier
agent_id INTEGER Associated agent
name TEXT Tool name
description TEXT Tool description
http_method TEXT API method
static_params JSONB Fixed parameters
dynamic_params JSONB Variable parameters
url TEXT Integration endpoint

Third-Party App Table

Manages available third-party integrations.

Column Type Description
third_party_app_id INTEGER Primary identifier
name VARCHAR App name
description TEXT App description
icon TEXT App icon URL
slug TEXT URL-friendly identifier
prompt_id INTEGER Associated prompt
is_coming_soon BOOLEAN Release status

Third-Party OAuth Table

Stores integration authentication.

Column Type Description
third_party_oauth_id INTEGER Primary identifier
access_token VARCHAR OAuth access token
refresh_token VARCHAR OAuth refresh token
expiry TIMESTAMP Token expiry
third_party_app_id INTEGER Associated app
agent_id INTEGER Associated agent
additional_data JSONB Extra configuration

Data Flow Examples

Customer Onboarding Flow

  1. Customer signs up and is created in the customer table
  2. Customer selects a plan from the plan table
  3. A contract is automatically created in the contract table
  4. Customer configures their AI agent in the agent table
  5. Default prompt is assigned from the prompt table

Conversation Flow

  1. Visitor information is stored in the visitor table
  2. New session is created in the session table
  3. Messages are recorded in the message table
  4. Post-session analytics are generated in the analytics table
  5. If configured, emails are sent and logged in email_log table

Integration Flow

  1. Custom tools are configured in the custom_tool table
  2. Third-party apps are registered in third_party_app
  3. OAuth tokens are stored in third_party_oauth