Database Schema & Data Models

Comprehensive documentation of the Neptie platform's database schema, including table structures, relationships, constraints, and data flow patterns.

Database Overview

High-level overview of the database architecture and design principles

Database Technology

Database: PostgreSQL 15+ (via Supabase)
Security: Row Level Security (RLS)
Performance: Optimized indexes and queries
Backup: Automated daily backups

Design Principles

Normalized schema for data integrity
Multi-tenant architecture with data isolation
Audit trails for compliance and debugging
Scalable design for high-volume operations

Core Tables

Primary tables that form the foundation of the platform

profiles

User profile information and account details

Columns:

id•UUID•Primary key, references auth.users

email•TEXT•User email address

full_name•TEXT•User full name

company•TEXT•Company name

role•TEXT•User role (Admin, User, Caller, Manager)

created_at•TIMESTAMP•Account creation timestamp

Relationships:

• One-to-many with leads
• One-to-many with campaigns
• One-to-many with subscriptions

leads

Lead information and contact details

Columns:

id•UUID•Primary key

user_id•UUID•References profiles.id

full_name•TEXT•Lead full name

email•TEXT•Lead email address

company•TEXT•Company name

job_title•TEXT•Job title/position

industry•TEXT•Industry classification

status•TEXT•Lead status (new, contacted, qualified, converted)

score•INTEGER•AI lead score (0-100)

created_at•TIMESTAMP•Lead creation timestamp

Relationships:

• Many-to-one with profiles
• One-to-many with campaign_assignments
• One-to-many with calls

campaigns

Campaign configuration and settings

Columns:

id•UUID•Primary key

user_id•UUID•References profiles.id

name•TEXT•Campaign name

description•TEXT•Campaign description

type•TEXT•Campaign type (email, voice, mixed)

status•TEXT•Campaign status (draft, active, paused, completed)

created_at•TIMESTAMP•Campaign creation timestamp

Relationships:

• Many-to-one with profiles
• One-to-many with campaign_assignments
• One-to-many with calls

campaign_assignments

Many-to-many relationship between campaigns and leads

Columns:

id•UUID•Primary key

campaign_id•UUID•References campaigns.id

lead_id•UUID•References leads.id

status•TEXT•Assignment status

email_sent•BOOLEAN•Email sent flag

email_sent_at•TIMESTAMP•Email sent timestamp

created_at•TIMESTAMP•Assignment creation timestamp

Relationships:

• Many-to-one with campaigns
• Many-to-one with leads

AI & Machine Learning Tables

Tables that support AI-powered features and analytics

lead_interactions

AI-powered lead interaction tracking

Columns:

id•UUID•Primary key

user_id•UUID•References profiles.id

lead_id•UUID•References leads.id

interaction_type•TEXT•Type (call, email, meeting)

interested_level•INTEGER•Interest level (1-5)

outcome_notes•TEXT•Interaction outcome

created_at•TIMESTAMP•Interaction timestamp

user_profile_insights

AI-learned user preferences and patterns

Columns:

id•UUID•Primary key

user_id•UUID•References profiles.id

preferred_industries•TEXT[]•Preferred target industries

successful_company_sizes•TEXT[]•Successful company sizes

optimal_contact_times•JSONB•Optimal contact timing

ai_insights•JSONB•AI analysis and patterns

last_analysis_at•TIMESTAMP•Last AI analysis timestamp

lead_ai_rankings

AI-generated lead rankings and scores

Columns:

id•UUID•Primary key

user_id•UUID•References profiles.id

lead_id•UUID•References leads.id

ai_score•DECIMAL(5,2)•AI confidence score (0-100)

ai_reasoning•TEXT•AI explanation for score

selected_for_outreach•BOOLEAN•Selected for outreach flag

created_at•TIMESTAMP•Ranking creation timestamp

Email System Tables

Tables that manage email infrastructure and tracking

email_accounts

Email account configuration and OAuth settings

Columns:

id•UUID•Primary key

user_id•UUID•References profiles.id

email•TEXT•Email address

provider•TEXT•Provider (gmail, outlook)

oauth_token•TEXT•Encrypted OAuth token

status•TEXT•Account status (active, inactive)

created_at•TIMESTAMP•Account creation timestamp

email_sends

Email sending history and tracking

Columns:

id•UUID•Primary key

campaign_id•UUID•References campaigns.id

lead_id•UUID•References leads.id

email_account_id•UUID•References email_accounts.id

subject•TEXT•Email subject line

content•TEXT•Email content

sent_at•TIMESTAMP•Email sent timestamp

status•TEXT•Send status (sent, delivered, bounced)

email_responses

Email response tracking and analysis

Columns:

id•UUID•Primary key

email_send_id•UUID•References email_sends.id

response_content•TEXT•Response content

sentiment•TEXT•AI sentiment analysis

interest_level•INTEGER•Interest level (1-5)

responded_at•TIMESTAMP•Response timestamp

Calling System Tables

Tables that manage voice calling and caller operations

callers

Human caller information and management

Columns:

id•UUID•Primary key

user_id•UUID•References profiles.id

full_name•TEXT•Caller full name

email•TEXT•Caller email

phone•TEXT•Caller phone number

status•TEXT•Status (Available, Busy, Offline)

rating•NUMERIC•Performance rating

calls_completed•INTEGER•Total calls completed

calls

Call history and outcomes

Columns:

id•UUID•Primary key

campaign_id•UUID•References campaigns.id

lead_id•UUID•References leads.id

caller_id•UUID•References callers.id

status•TEXT•Call status (scheduled, completed, failed)

outcome•TEXT•Call outcome (interested, not_interested, meeting_scheduled)

duration•INTEGER•Call duration in seconds

scheduled_at•TIMESTAMP•Scheduled call time

started_at•TIMESTAMP•Call start time

ended_at•TIMESTAMP•Call end time

call_dispositions

Detailed call outcome tracking

Columns:

id•UUID•Primary key

call_id•UUID•References calls.id

disposition•TEXT•Call disposition (answered, no_answer, voicemail)

interested•BOOLEAN•Lead interest flag

interest_level•INTEGER•Interest level (1-5)

meeting_scheduled•BOOLEAN•Meeting scheduled flag

callback_requested•BOOLEAN•Callback requested flag

notes•TEXT•Call notes and observations

Database Features

Advanced database features and capabilities

Row Level Security (RLS)

Advanced security policies for data protection

User-based data access control
Organization-level data isolation
Campaign-specific data permissions
Automatic data filtering by user context
Secure multi-tenant architecture
Audit logging for security events

Performance Optimization

Database optimization for high performance

Strategic indexing for common queries
Query optimization and caching
Connection pooling for scalability
Partitioning for large datasets
Real-time analytics optimization
Automated performance monitoring

Data Integrity

Ensuring data consistency and reliability

Foreign key constraints and relationships
Check constraints for data validation
Unique constraints for data uniqueness
Cascade delete and update rules
Transaction management and rollback
Data validation triggers and functions

Backup & Recovery

Comprehensive data protection and recovery

Automated daily backups
Point-in-time recovery capabilities
Cross-region backup replication
Disaster recovery procedures
Data retention policies
Backup integrity verification

Table Relationships

How tables are connected and related to each other

User Management

User and profile relationships

profiles (1) → leads (many)
profiles (1) → campaigns (many)
profiles (1) → subscriptions (many)
profiles (1) → email_accounts (many)
profiles (1) → callers (many)

Campaign Management

Campaign and lead relationships

campaigns (1) → campaign_assignments (many)
campaign_assignments (many) → leads (1)
campaigns (1) → email_sends (many)
campaigns (1) → calls (many)

AI & Analytics

AI and analytics relationships

leads (1) → lead_interactions (many)
leads (1) → lead_ai_rankings (many)
profiles (1) → user_profile_insights (1)
email_sends (1) → email_responses (many)

Communication

Email and calling relationships

email_accounts (1) → email_sends (many)
callers (1) → calls (many)
calls (1) → call_dispositions (1)
leads (1) → calls (many)

Common SQL Queries

Example queries for common database operations

Get User's Active Campaigns

SELECT c.*, COUNT(ca.lead_id) as total_leads
FROM campaigns c
LEFT JOIN campaign_assignments ca ON c.id = ca.campaign_id
WHERE c.user_id = $1 AND c.status = 'active'
GROUP BY c.id
ORDER BY c.created_at DESC;

Get High-Scoring Leads

SELECT l.*, lar.ai_score, lar.ai_reasoning
FROM leads l
JOIN lead_ai_rankings lar ON l.id = lar.lead_id
WHERE l.user_id = $1 
  AND lar.ai_score >= 80
  AND l.status = 'new'
ORDER BY lar.ai_score DESC
LIMIT 50;

Campaign Performance Analytics

SELECT 
  c.name,
  COUNT(ca.lead_id) as total_leads,
  COUNT(es.id) as emails_sent,
  COUNT(er.id) as responses_received,
  ROUND(COUNT(er.id)::decimal / COUNT(es.id) * 100, 2) as response_rate
FROM campaigns c
LEFT JOIN campaign_assignments ca ON c.id = ca.campaign_id
LEFT JOIN email_sends es ON c.id = es.campaign_id
LEFT JOIN email_responses er ON es.id = er.email_send_id
WHERE c.user_id = $1
GROUP BY c.id, c.name
ORDER BY response_rate DESC;

Explore More

Learn more about the technical aspects of the Neptie platform

System Architecture AI System API Reference

Documentation

Database Schema & Data Models

Database Overview

Database Technology

Design Principles

Core Tables

profiles

Columns:

Relationships:

leads

Columns:

Relationships:

campaigns

Columns:

Relationships:

campaign_assignments

Columns:

Relationships:

AI & Machine Learning Tables

lead_interactions

Columns:

user_profile_insights

Columns:

lead_ai_rankings

Columns:

Email System Tables

email_accounts

Columns:

email_sends

Columns:

email_responses

Columns:

Calling System Tables

callers

Columns:

calls

Columns:

call_dispositions

Columns:

Database Features

Row Level Security (RLS)

Performance Optimization

Data Integrity

Backup & Recovery

Table Relationships

User Management

Campaign Management

AI & Analytics

Communication

Common SQL Queries

Get User's Active Campaigns

Get High-Scoring Leads

Campaign Performance Analytics

Explore More