PostgreSQL Database Schema
Documentation
Trustt AI Management Portal
🔗 Core Entity Relationships
Users ───┐
├── Campaigns ── Campaign Configs
│ │
Templates┘ │ ┌── Metrics
│ │
├───────────────┤
│ │
│ └── Alerts
│
│ ┌── Voice Personas
├─────┤
│ └── Languages
│
│
Customers ──────┼── Campaign Audiences ── Segments
│ │
│ │
└───────────┼── Calls
│
│
Documents ──────┘
🧩 Table Descriptions
1. users
Purpose: Stores user accounts (admins, managers, agents).
● Tracks login activity (last_login)
● Defines permissions via role
● Used for authentication and audit trails
2. templates
Purpose: Stores reusable campaign templates.
● config contains campaign configuration (JSONB)
● usage_count tracks analytics
● Basis for new campaign creation
3. campaigns
Purpose: Central campaign entity.
● status: draft / active / paused / completed / failed
● template_id: (optional) source template
● tags: campaign categorization
● Parent for calls, metrics, documents, etc.
4. campaign_configs
Purpose: Structured configuration extracted from JSON.
● Holds call behavior, scheduling, escalation logic
● Improves query performance by normalization
5. customers
Purpose: Contact master data.
● Integrates with external CRMs (external_id)
● attributes: custom JSONB fields
● tags: dynamic segmentation
6. segments
Purpose: Groups of customers for targeting.
● criteria: JSONB filter logic
● Reused across multiple campaigns
7. customer_segments
Purpose: Many-to-many link between customers and segments.
● Efficient segment membership tracking
8. campaign_audiences
Purpose: Targeting layer for campaigns.
● Links campaigns to customers or segments
● Supports granular and group-level targeting
9. calls
Purpose: Complete call records.
● Call lifecycle, transcript, AI summaries
● Sentiment/confidence scores
● Escalation tracking (to humans)
10. documents
Purpose: Reference materials, scripts, and KBs.
● Attachments linked to campaigns
● Searchable content field for NLP
11. voice_personas
Purpose: Voice options for AI agents.
● Includes description and sample URL
12. languages
Purpose: Supported campaign languages.
● Used to validate configurations
13. escalation_rules
Purpose: Logic for human escalation.
● Based on keywords, sentiment, confidence
● Customizable per campaign
14. metrics
Purpose: Daily aggregates for reporting.
● Optimized for dashboards
● Avoids real-time joins on calls
15. alerts
Purpose: Campaign-based notifications.
● Categories: warning, error, info, success
● Severity tracking for urgent issues
16. crm_integrations
Purpose: External CRM connection data.
● Stores encrypted credentials
● Allows customer sync
17. webhooks
Purpose: Push-based integration points.
● Triggered on campaign/call events
● Configurable by event type
🧭 Key Database Relationships
1. Campaign Management Flow
● templates → campaigns → campaign_configs
● campaigns → documents (reference)
● campaigns → escalation_rules (escalation logic)
2. Customer Targeting Flow
● customers → customer_segments → segments
● campaigns → campaign_audiences → segments/customers
3. Call Execution Flow
● campaigns → calls → customers
● calls → users (for escalations)
4. Analytics Flow
● campaigns → calls → metrics
● campaigns → alerts
🔍 Common Query Patterns
📊 Campaign Dashboard
SELECT c.id, c.name, c.status,
m.total_calls, m.completed_calls, m.avg_call_duration,
COUNT(DISTINCT ca.customer_id) as targeted_customers
FROM campaigns c
LEFT JOIN metrics m ON m.campaign_id = c.id AND m.date = CURRENT_DATE
LEFT JOIN campaign_audiences ca ON ca.campaign_id = c.id
WHERE c.created_by = [user_id] AND c.is_deleted = FALSE
GROUP BY c.id, c.name, c.status, m.total_calls, m.completed_calls, m.avg_call_duration;
📡 Live Campaign Monitoring
SELECT c.name as campaign_name,
COUNT(CASE WHEN calls.status = 'active' THEN 1 END) as active_calls,
COUNT(CASE WHEN calls.status = 'queued' THEN 1 END) as queued_calls,
COUNT(CASE WHEN calls.status = 'completed' THEN 1 END) as completed_calls,
COUNT(CASE WHEN calls.escalated = TRUE THEN 1 END) as escalated_calls
FROM campaigns c
JOIN calls ON calls.campaign_id = c.id
WHERE c.id = [campaign_id] AND calls.start_time > (NOW() - INTERVAL '24 hours')
GROUP BY c.name;
🧾 Customer Call History
SELECT c.name as campaign_name,
calls.start_time, calls.duration, calls.status,
calls.summary, calls.sentiment_score
FROM calls
JOIN campaigns c ON calls.campaign_id = c.id
WHERE calls.customer_id = [customer_id]
ORDER BY calls.start_time DESC;
🔄 Data Flow Examples
📥 Campaign Creation Process
1. User creates campaign from template
→ Copy template.config → campaign_configs
→ Insert new campaign with status = 'draft'
2. User configures audience
→ Insert into campaign_audiences
→ Link to segments or customers
3. User uploads documents
→ Insert into documents with campaign_id
4. User starts campaign
→ Update status = 'active'
→ Insert call records (queued)
☎️ Call Processing Flow
1. System starts call
→ Set status = 'active', start_time
2. AI engages
→ Updates transcript_segments, scores
3. Call ends
→ Set end_time, duration, summary
→ Update status = 'completed' or 'escalated'
4. Metrics updated
→ Increment metrics for that campaign
