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:
Relationships:
- • One-to-many with leads
- • One-to-many with campaigns
- • One-to-many with subscriptions
leads
Lead information and contact details
Columns:
Relationships:
- • Many-to-one with profiles
- • One-to-many with campaign_assignments
- • One-to-many with calls
campaigns
Campaign configuration and settings
Columns:
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:
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:
user_profile_insights
AI-learned user preferences and patterns
Columns:
lead_ai_rankings
AI-generated lead rankings and scores
Columns:
Email System Tables
Tables that manage email infrastructure and tracking
email_accounts
Email account configuration and OAuth settings
Columns:
email_sends
Email sending history and tracking
Columns:
email_responses
Email response tracking and analysis
Columns:
Calling System Tables
Tables that manage voice calling and caller operations
callers
Human caller information and management
Columns:
calls
Call history and outcomes
Columns:
call_dispositions
Detailed call outcome tracking
Columns:
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