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:

idUUIDPrimary key, references auth.users
emailTEXTUser email address
full_nameTEXTUser full name
companyTEXTCompany name
roleTEXTUser role (Admin, User, Caller, Manager)
created_atTIMESTAMPAccount 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:

idUUIDPrimary key
user_idUUIDReferences profiles.id
full_nameTEXTLead full name
emailTEXTLead email address
companyTEXTCompany name
job_titleTEXTJob title/position
industryTEXTIndustry classification
statusTEXTLead status (new, contacted, qualified, converted)
scoreINTEGERAI lead score (0-100)
created_atTIMESTAMPLead 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:

idUUIDPrimary key
user_idUUIDReferences profiles.id
nameTEXTCampaign name
descriptionTEXTCampaign description
typeTEXTCampaign type (email, voice, mixed)
statusTEXTCampaign status (draft, active, paused, completed)
created_atTIMESTAMPCampaign 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:

idUUIDPrimary key
campaign_idUUIDReferences campaigns.id
lead_idUUIDReferences leads.id
statusTEXTAssignment status
email_sentBOOLEANEmail sent flag
email_sent_atTIMESTAMPEmail sent timestamp
created_atTIMESTAMPAssignment 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:

idUUIDPrimary key
user_idUUIDReferences profiles.id
lead_idUUIDReferences leads.id
interaction_typeTEXTType (call, email, meeting)
interested_levelINTEGERInterest level (1-5)
outcome_notesTEXTInteraction outcome
created_atTIMESTAMPInteraction timestamp

user_profile_insights

AI-learned user preferences and patterns

Columns:

idUUIDPrimary key
user_idUUIDReferences profiles.id
preferred_industriesTEXT[]Preferred target industries
successful_company_sizesTEXT[]Successful company sizes
optimal_contact_timesJSONBOptimal contact timing
ai_insightsJSONBAI analysis and patterns
last_analysis_atTIMESTAMPLast AI analysis timestamp

lead_ai_rankings

AI-generated lead rankings and scores

Columns:

idUUIDPrimary key
user_idUUIDReferences profiles.id
lead_idUUIDReferences leads.id
ai_scoreDECIMAL(5,2)AI confidence score (0-100)
ai_reasoningTEXTAI explanation for score
selected_for_outreachBOOLEANSelected for outreach flag
created_atTIMESTAMPRanking creation timestamp

Email System Tables

Tables that manage email infrastructure and tracking

email_accounts

Email account configuration and OAuth settings

Columns:

idUUIDPrimary key
user_idUUIDReferences profiles.id
emailTEXTEmail address
providerTEXTProvider (gmail, outlook)
oauth_tokenTEXTEncrypted OAuth token
statusTEXTAccount status (active, inactive)
created_atTIMESTAMPAccount creation timestamp

email_sends

Email sending history and tracking

Columns:

idUUIDPrimary key
campaign_idUUIDReferences campaigns.id
lead_idUUIDReferences leads.id
email_account_idUUIDReferences email_accounts.id
subjectTEXTEmail subject line
contentTEXTEmail content
sent_atTIMESTAMPEmail sent timestamp
statusTEXTSend status (sent, delivered, bounced)

email_responses

Email response tracking and analysis

Columns:

idUUIDPrimary key
email_send_idUUIDReferences email_sends.id
response_contentTEXTResponse content
sentimentTEXTAI sentiment analysis
interest_levelINTEGERInterest level (1-5)
responded_atTIMESTAMPResponse timestamp

Calling System Tables

Tables that manage voice calling and caller operations

callers

Human caller information and management

Columns:

idUUIDPrimary key
user_idUUIDReferences profiles.id
full_nameTEXTCaller full name
emailTEXTCaller email
phoneTEXTCaller phone number
statusTEXTStatus (Available, Busy, Offline)
ratingNUMERICPerformance rating
calls_completedINTEGERTotal calls completed

calls

Call history and outcomes

Columns:

idUUIDPrimary key
campaign_idUUIDReferences campaigns.id
lead_idUUIDReferences leads.id
caller_idUUIDReferences callers.id
statusTEXTCall status (scheduled, completed, failed)
outcomeTEXTCall outcome (interested, not_interested, meeting_scheduled)
durationINTEGERCall duration in seconds
scheduled_atTIMESTAMPScheduled call time
started_atTIMESTAMPCall start time
ended_atTIMESTAMPCall end time

call_dispositions

Detailed call outcome tracking

Columns:

idUUIDPrimary key
call_idUUIDReferences calls.id
dispositionTEXTCall disposition (answered, no_answer, voicemail)
interestedBOOLEANLead interest flag
interest_levelINTEGERInterest level (1-5)
meeting_scheduledBOOLEANMeeting scheduled flag
callback_requestedBOOLEANCallback requested flag
notesTEXTCall 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