Database Schema Documentation

Overview

JobHive uses PostgreSQL as its primary database with a carefully designed schema that supports AI-powered interview analysis, user management, billing, and comprehensive analytics. The database is optimized for performance with strategic indexing and efficient relationships.

Database Architecture

Core Design Principles

Normalization: Follows 3NF with strategic denormalization for performance
Scalability: Designed to handle millions of interview sessions
Performance: Strategic indexing and query optimization
Flexibility: JSON fields for dynamic data while maintaining structure
Audit Trail: Comprehensive tracking of changes and history

Core Database Models

1. User Management Models

User Model

CREATE TABLE users_user (
    id SERIAL PRIMARY KEY,
    username VARCHAR(255) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    first_name VARCHAR(255),
    last_name VARCHAR(255),
    is_verified BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    is_staff BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    secret_key VARCHAR(32),
    enable_2fa BOOLEAN DEFAULT FALSE,
    role VARCHAR(20) DEFAULT 'user',
    resume VARCHAR(100),
    avatar VARCHAR(100),
    auth_provider VARCHAR(255) DEFAULT 'email',
    social_links JSONB DEFAULT '{}'
);

-- Indexes for performance
CREATE INDEX idx_users_user_email ON users_user(email);
CREATE INDEX idx_users_user_username ON users_user(username);
CREATE INDEX idx_users_user_role ON users_user(role);
CREATE INDEX idx_users_user_created_at ON users_user(created_at);

Resume Model

CREATE TABLE users_resume (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users_user(id) ON DELETE CASCADE,
    file VARCHAR(100),
    uploaded_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    first_name VARCHAR(255),
    last_name VARCHAR(255),
    email VARCHAR(255),
    phone VARCHAR(50),
    linkedin VARCHAR(200),
    github VARCHAR(200),
    skills TEXT,
    work_experience TEXT,
    education TEXT,
    summary TEXT,
    projects_worked TEXT,
    achievements TEXT
);

-- Indexes
CREATE INDEX idx_users_resume_user_id ON users_resume(user_id);
CREATE INDEX idx_users_resume_uploaded_at ON users_resume(uploaded_at);

OAuth Tokens Model

CREATE TABLE users_useroauthtoken (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users_user(id) ON DELETE CASCADE,
    provider VARCHAR(50) NOT NULL,
    access_token TEXT NOT NULL,
    refresh_token TEXT,
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
    scope TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(user_id, provider)
);

-- Indexes
CREATE INDEX idx_users_oauth_user_provider ON users_useroauthtoken(user_id, provider);
CREATE INDEX idx_users_oauth_expires_at ON users_useroauthtoken(expires_at);

2. Company Management Models

Company Model

CREATE TABLE company_company (
    id SERIAL PRIMARY KEY,
    user_id INTEGER UNIQUE REFERENCES users_user(id) ON DELETE CASCADE,
    company_name VARCHAR(255) NOT NULL,
    contact_email VARCHAR(254),
    phone_number VARCHAR(20),
    address VARCHAR(255),
    industry VARCHAR(100),
    company_logo VARCHAR(100),
    website VARCHAR(200),
    description TEXT,
    is_recruiting_agency BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    team_member_count INTEGER DEFAULT 0,
    
    -- Interview timing settings
    custom_interview_timing BOOLEAN DEFAULT FALSE,
    interview_duration_minutes INTEGER DEFAULT 30,
    interview_first_warning_seconds INTEGER DEFAULT 300,
    interview_final_warning_seconds INTEGER DEFAULT 120,
    interview_force_end_seconds INTEGER DEFAULT 10,
    allow_interview_extensions BOOLEAN DEFAULT TRUE,
    max_interview_extensions INTEGER DEFAULT 2,
    interview_extension_minutes INTEGER DEFAULT 10
);

-- Indexes
CREATE INDEX idx_company_company_user_id ON company_company(user_id);
CREATE INDEX idx_company_company_industry ON company_company(industry);
CREATE INDEX idx_company_company_created_at ON company_company(created_at);

Job Model

CREATE TABLE company_job (
    id SERIAL PRIMARY KEY,
    company_id INTEGER REFERENCES company_company(id) ON DELETE CASCADE,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    requirements TEXT,
    job_focus VARCHAR(50) DEFAULT 'balanced',
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_company_job_company_id ON company_job(company_id);
CREATE INDEX idx_company_job_is_active ON company_job(is_active);
CREATE INDEX idx_company_job_created_at ON company_job(created_at);
CREATE INDEX idx_company_job_job_focus ON company_job(job_focus);

3. Interview System Models

Interview Session Model (Core)

CREATE TABLE interview_interviewsession (
    id SERIAL PRIMARY KEY,
    session_id VARCHAR(255) UNIQUE NOT NULL,
    user_id INTEGER REFERENCES users_user(id) ON DELETE CASCADE,
    job_id INTEGER REFERENCES company_job(id) ON DELETE CASCADE,
    
    -- Timing fields
    start_time TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    end_time TIMESTAMP WITH TIME ZONE,
    scheduled_time TIMESTAMP WITH TIME ZONE,
    completion_time TIMESTAMP WITH TIME ZONE,
    duration DOUBLE PRECISION,
    max_duration_minutes INTEGER DEFAULT 15,
    time_extension_count INTEGER DEFAULT 0,
    
    -- Status and completion
    status VARCHAR(20) DEFAULT 'scheduled',
    is_active BOOLEAN DEFAULT TRUE,
    completion_percentage INTEGER DEFAULT 0,
    
    -- Scoring fields
    technical_accuracy DECIMAL(6,2),
    behavioral_score DECIMAL(5,2),
    
    -- Settings
    is_practice BOOLEAN DEFAULT FALSE,
    enable_behavioral_analysis BOOLEAN DEFAULT FALSE,
    
    -- Metadata
    interviewer_notes TEXT,
    technical_difficulty_reported BOOLEAN DEFAULT FALSE,
    reminder_sent BOOLEAN DEFAULT FALSE,
    reminder_time INTEGER DEFAULT 30,
    calendar_event_id VARCHAR(255),
    calendar_link VARCHAR(200),
    
    -- Performance tracking
    response_times JSONB DEFAULT '[]',
    attention_metrics JSONB DEFAULT '{}',
    employer_metrics JSONB DEFAULT '{}',
    interviewer_growth_metrics JSONB DEFAULT '{}',
    knowledge_gaps JSONB DEFAULT '[]',
    suggested_resources JSONB DEFAULT '[]',
    warning_sent_at JSONB DEFAULT '[]',
    
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Critical indexes for performance
CREATE INDEX idx_interview_session_user_status ON interview_interviewsession(user_id, status, start_time);
CREATE INDEX idx_interview_session_job_id ON interview_interviewsession(job_id);
CREATE INDEX idx_interview_session_session_id ON interview_interviewsession(session_id);
CREATE INDEX idx_interview_session_completion ON interview_interviewsession(completion_percentage);
CREATE INDEX idx_interview_session_status_time ON interview_interviewsession(status, start_time);

Sentiment Analysis Models

CREATE TABLE interview_sentimentsession (
    id SERIAL PRIMARY KEY,
    session_id VARCHAR(255) UNIQUE NOT NULL,
    interview_session_id INTEGER REFERENCES interview_interviewsession(id) ON DELETE CASCADE,
    user_id INTEGER REFERENCES users_user(id) ON DELETE CASCADE,
    
    -- Core sentiment scores
    positive_score DECIMAL(5,2) DEFAULT 0,
    negative_score DECIMAL(5,2) DEFAULT 0,
    neutral_score DECIMAL(5,2) DEFAULT 0,
    mixed_score DECIMAL(5,2) DEFAULT 0,
    overall_sentiment VARCHAR(20),
    
    -- Additional metrics
    confidence_level DECIMAL(5,2) DEFAULT 0,
    engagement_score DECIMAL(5,2) DEFAULT 0,
    professionalism_score DECIMAL(5,2) DEFAULT 0,
    clarity_score DECIMAL(5,2) DEFAULT 0,
    
    -- Visibility settings
    employer_visible BOOLEAN DEFAULT TRUE,
    interviewer_visible BOOLEAN DEFAULT TRUE,
    
    -- Practice session features
    is_practice BOOLEAN DEFAULT FALSE,
    detailed_feedback JSONB DEFAULT '{}',
    improvement_suggestions JSONB DEFAULT '[]',
    practice_focus_areas JSONB DEFAULT '[]',
    
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_sentiment_session_interview ON interview_sentimentsession(interview_session_id, created_at);
CREATE INDEX idx_sentiment_session_user ON interview_sentimentsession(user_id);
CREATE INDEX idx_sentiment_session_overall ON interview_sentimentsession(overall_sentiment);

Sentiment History and Context

CREATE TABLE interview_sentimenthistory (
    id SERIAL PRIMARY KEY,
    sentiment_session_id INTEGER REFERENCES interview_sentimentsession(id) ON DELETE CASCADE,
    interview_session_id INTEGER REFERENCES interview_interviewsession(id) ON DELETE CASCADE,
    
    positive_score DECIMAL(5,2) DEFAULT 0,
    negative_score DECIMAL(5,2) DEFAULT 0,
    neutral_score DECIMAL(5,2) DEFAULT 0,
    mixed_score DECIMAL(5,2) DEFAULT 0,
    overall_sentiment VARCHAR(20),
    confidence DECIMAL(5,2) DEFAULT 0,
    timestamp TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    detail TEXT
);

CREATE TABLE interview_sentimentcontextfactors (
    id SERIAL PRIMARY KEY,
    sentiment_history_id INTEGER UNIQUE REFERENCES interview_sentimenthistory(id) ON DELETE CASCADE,
    
    value_alignment_score DECIMAL(5,2) DEFAULT 0,
    requirement_addressing_score DECIMAL(5,2) DEFAULT 0,
    response_relevance_score DECIMAL(5,2) DEFAULT 0,
    confidence_score DECIMAL(5,2) DEFAULT 0,
    enthusiasm_score DECIMAL(5,2) DEFAULT 0,
    authenticity_score DECIMAL(5,2) DEFAULT 0,
    
    key_themes JSONB DEFAULT '[]',
    flags JSONB DEFAULT '[]'
);

-- Indexes
CREATE INDEX idx_sentiment_history_session ON interview_sentimenthistory(interview_session_id, timestamp);
CREATE INDEX idx_sentiment_context_history ON interview_sentimentcontextfactors(sentiment_history_id);

Skill Assessment Models

CREATE TABLE interview_skillcategory (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE interview_skill (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    category_id INTEGER REFERENCES interview_skillcategory(id) ON DELETE SET NULL
);

CREATE TABLE interview_skillassessment (
    id SERIAL PRIMARY KEY,
    interview_session_id INTEGER REFERENCES interview_interviewsession(id) ON DELETE CASCADE,
    sentiment_session_id INTEGER REFERENCES interview_sentimentsession(id) ON DELETE CASCADE,
    skill_id INTEGER REFERENCES interview_skill(id) ON DELETE CASCADE,
    
    accumulated_score DECIMAL(10,2) DEFAULT 0,
    update_count INTEGER DEFAULT 0,
    
    UNIQUE(interview_session_id, skill_id)
);

CREATE TABLE interview_skillassessmenthistory (
    id SERIAL PRIMARY KEY,
    skill_assessment_id INTEGER REFERENCES interview_skillassessment(id) ON DELETE CASCADE,
    score DECIMAL(5,2) NOT NULL,
    timestamp TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    details JSONB
);

-- Indexes
CREATE INDEX idx_skill_assessment_session ON interview_skillassessment(interview_session_id);
CREATE INDEX idx_skill_assessment_skill ON interview_skillassessment(skill_id);
CREATE INDEX idx_skill_assessment_history_timestamp ON interview_skillassessmenthistory(timestamp);

4. Assessment and Scoring Models

Assessment Framework

CREATE TABLE interview_assessmenttype (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    description TEXT
);

CREATE TABLE interview_assessmentcategory (
    id SERIAL PRIMARY KEY,
    assessment_type_id INTEGER REFERENCES interview_assessmenttype(id) ON DELETE CASCADE,
    name VARCHAR(100) NOT NULL,
    UNIQUE(assessment_type_id, name)
);

CREATE TABLE interview_interviewassessment (
    id SERIAL PRIMARY KEY,
    interview_session_id INTEGER REFERENCES interview_interviewsession(id) ON DELETE CASCADE,
    assessment_type_id INTEGER REFERENCES interview_assessmenttype(id) ON DELETE CASCADE,
    category_id INTEGER REFERENCES interview_assessmentcategory(id) ON DELETE SET NULL,
    
    score DECIMAL(5,2) NOT NULL CHECK (score >= 0 AND score <= 100),
    details JSONB,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    UNIQUE(interview_session_id, assessment_type_id, category_id)
);

-- Indexes
CREATE INDEX idx_interview_assessment_session ON interview_interviewassessment(interview_session_id);
CREATE INDEX idx_interview_assessment_type ON interview_interviewassessment(assessment_type_id);
CREATE INDEX idx_interview_assessment_created ON interview_interviewassessment(created_at);

Cultural Fit and Behavioral Analysis

CREATE TABLE interview_culturalfit (
    id SERIAL PRIMARY KEY,
    interview_session_id INTEGER UNIQUE REFERENCES interview_interviewsession(id) ON DELETE CASCADE,
    score DECIMAL(5,2) DEFAULT 0,
    comments TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE interview_behavioralanalysis (
    id SERIAL PRIMARY KEY,
    interview_session_id INTEGER UNIQUE REFERENCES interview_interviewsession(id) ON DELETE CASCADE,
    
    confidence_score DECIMAL(5,2) NOT NULL CHECK (confidence_score >= 0 AND confidence_score <= 100),
    fear_score DECIMAL(5,2) NOT NULL CHECK (fear_score >= 0 AND fear_score <= 100),
    sincerity_score DECIMAL(5,2) NOT NULL CHECK (sincerity_score >= 0 AND sincerity_score <= 100),
    nervousness_score DECIMAL(5,2) NOT NULL CHECK (nervousness_score >= 0 AND nervousness_score <= 100),
    excitement_score DECIMAL(5,2) NOT NULL CHECK (excitement_score >= 0 AND excitement_score <= 100),
    curiosity_score DECIMAL(5,2) NOT NULL CHECK (curiosity_score >= 0 AND curiosity_score <= 100),
    adaptability_score DECIMAL(5,2) NOT NULL CHECK (adaptability_score >= 0 AND adaptability_score <= 100),
    
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    raw_data JSONB DEFAULT '{}'
);

5. Learning and Development Models

Learning Resources

CREATE TABLE interview_learningresource (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    resource_type VARCHAR(50) NOT NULL,
    url VARCHAR(200),
    estimated_hours INTEGER DEFAULT 1,
    skill_id INTEGER REFERENCES interview_skill(id) ON DELETE CASCADE
);

CREATE TABLE interview_learningpath (
    id SERIAL PRIMARY KEY,
    interview_session_id INTEGER REFERENCES interview_interviewsession(id) ON DELETE CASCADE,
    status VARCHAR(20) DEFAULT 'draft',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE interview_learningpathresource (
    id SERIAL PRIMARY KEY,
    learning_path_id INTEGER REFERENCES interview_learningpath(id) ON DELETE CASCADE,
    resource_id INTEGER REFERENCES interview_learningresource(id) ON DELETE CASCADE,
    order_position INTEGER NOT NULL,
    completed BOOLEAN DEFAULT FALSE,
    completion_date TIMESTAMP WITH TIME ZONE,
    
    UNIQUE(learning_path_id, order_position)
);

-- Indexes
CREATE INDEX idx_learning_path_session ON interview_learningpath(interview_session_id);
CREATE INDEX idx_learning_path_resource_path ON interview_learningpathresource(learning_path_id, order_position);

6. Billing and Subscription Models

Subscription Plans

CREATE TABLE billing_subscriptionplan (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    plan_type VARCHAR(20) NOT NULL, -- 'candidate' or 'employer'
    interval VARCHAR(20) DEFAULT 'month', -- 'month' or 'year'
    
    -- Pricing
    price DECIMAL(10,2) NOT NULL,
    stripe_price_id VARCHAR(100),
    stripe_product_id VARCHAR(100),
    
    -- Feature limits
    interview_limit INTEGER DEFAULT 0, -- 0 = unlimited
    job_posting_limit INTEGER DEFAULT 0,
    application_limit INTEGER DEFAULT 0,
    
    -- Additional features
    features JSONB DEFAULT '[]',
    sort_order INTEGER DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_billing_plan_type ON billing_subscriptionplan(plan_type, is_active);
CREATE INDEX idx_billing_plan_sort ON billing_subscriptionplan(sort_order);

Customer Subscriptions

CREATE TABLE billing_customersubscription (
    id SERIAL PRIMARY KEY,
    user_id INTEGER UNIQUE REFERENCES users_user(id) ON DELETE CASCADE,
    plan_id INTEGER REFERENCES billing_subscriptionplan(id) ON DELETE PROTECT,
    
    status VARCHAR(20) DEFAULT 'active',
    stripe_customer_id VARCHAR(100),
    stripe_subscription_id VARCHAR(100),
    stripe_subscription_item_id VARCHAR(100) DEFAULT '',
    
    -- Subscription timing
    start_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    end_date TIMESTAMP WITH TIME ZONE,
    trial_end TIMESTAMP WITH TIME ZONE,
    current_period_start TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    current_period_end TIMESTAMP WITH TIME ZONE,
    cancel_at_period_end BOOLEAN DEFAULT FALSE,
    
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_billing_subscription_user ON billing_customersubscription(user_id, status);
CREATE INDEX idx_billing_subscription_stripe ON billing_customersubscription(stripe_subscription_id);
CREATE INDEX idx_billing_subscription_status ON billing_customersubscription(status);

Usage Tracking

CREATE TABLE billing_usagerecord (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users_user(id) ON DELETE CASCADE,
    subscription_id INTEGER REFERENCES billing_customersubscription(id) ON DELETE CASCADE,
    usage_type VARCHAR(20) NOT NULL, -- 'interview', 'job_posting', 'application'
    quantity INTEGER DEFAULT 1,
    resource_id VARCHAR(100),
    stripe_usage_record_id VARCHAR(100),
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_usage_record_user_type ON billing_usagerecord(user_id, usage_type, created_at);
CREATE INDEX idx_usage_record_subscription ON billing_usagerecord(subscription_id, created_at);

7. Analytics and Reporting Models

Interview Statistics

CREATE TABLE interview_interviewstatistics (
    id SERIAL PRIMARY KEY,
    company_id INTEGER REFERENCES company_company(id) ON DELETE CASCADE,
    date DATE NOT NULL,
    period VARCHAR(20) DEFAULT 'daily',
    
    -- Basic metrics
    total_interviews INTEGER DEFAULT 0,
    completed_interviews INTEGER DEFAULT 0,
    cancelled_interviews INTEGER DEFAULT 0,
    rescheduled_interviews INTEGER DEFAULT 0,
    
    -- Performance metrics
    average_duration INTERVAL,
    average_score DECIMAL(5,2) DEFAULT 0,
    technical_score_avg DECIMAL(5,2) DEFAULT 0,
    communication_score_avg DECIMAL(5,2) DEFAULT 0,
    
    -- Quality metrics
    no_show_rate DECIMAL(5,2) DEFAULT 0,
    conversion_rate DECIMAL(5,2) DEFAULT 0,
    time_to_hire INTERVAL,
    cost_per_hire DECIMAL(10,2),
    offer_acceptance_rate DECIMAL(5,2) DEFAULT 0,
    candidate_satisfaction DECIMAL(5,2) DEFAULT 0,
    skill_match_rate DECIMAL(5,2) DEFAULT 0,
    
    -- Advanced analytics
    diversity_metrics JSONB DEFAULT '{}',
    source_effectiveness JSONB DEFAULT '{}',
    
    UNIQUE(company_id, date)
);

-- Indexes
CREATE INDEX idx_interview_stats_company_date ON interview_interviewstatistics(company_id, date);
CREATE INDEX idx_interview_stats_period ON interview_interviewstatistics(period, date);

Candidate Rankings

CREATE TABLE interview_candidateranking (
    id SERIAL PRIMARY KEY,
    job_id INTEGER REFERENCES company_job(id) ON DELETE CASCADE,
    candidate_id INTEGER REFERENCES users_user(id) ON DELETE CASCADE,
    
    overall_score DECIMAL(5,2) NOT NULL CHECK (overall_score >= 0 AND overall_score <= 100),
    technical_score DECIMAL(5,2) NOT NULL CHECK (technical_score >= 0 AND technical_score <= 100),
    cultural_fit_score DECIMAL(5,2) NOT NULL CHECK (cultural_fit_score >= 0 AND cultural_fit_score <= 100),
    communication_score DECIMAL(5,2) NOT NULL CHECK (communication_score >= 0 AND communication_score <= 100),
    ai_confidence DECIMAL(5,2) NOT NULL CHECK (ai_confidence >= 0 AND ai_confidence <= 100),
    
    ranking_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    UNIQUE(job_id, candidate_id)
);

-- Indexes
CREATE INDEX idx_candidate_ranking_job_score ON interview_candidateranking(job_id, overall_score DESC);
CREATE INDEX idx_candidate_ranking_candidate ON interview_candidateranking(candidate_id);

8. Scoring and Weighting Models

Score Weights

CREATE TABLE interview_scoreweight (
    id SERIAL PRIMARY KEY,
    job_focus VARCHAR(50) UNIQUE NOT NULL, -- 'technical', 'behavioral', 'balanced'
    technical_weight DECIMAL(3,2) DEFAULT 0.30,
    skills_weight DECIMAL(3,2) DEFAULT 0.25,
    cultural_fit_weight DECIMAL(3,2) DEFAULT 0.25,
    sentiment_weight DECIMAL(3,2) DEFAULT 0.20
);

CREATE TABLE interview_dynamicscoreweight (
    id SERIAL PRIMARY KEY,
    job_id INTEGER UNIQUE REFERENCES company_job(id) ON DELETE CASCADE,
    
    -- Base weights
    base_technical_weight DECIMAL(3,2) NOT NULL,
    base_skills_weight DECIMAL(3,2) NOT NULL,
    base_cultural_fit_weight DECIMAL(3,2) NOT NULL,
    base_sentiment_weight DECIMAL(3,2) NOT NULL,
    
    -- Adjustments
    technical_adjustment DECIMAL(3,2) DEFAULT 0.00,
    skills_adjustment DECIMAL(3,2) DEFAULT 0.00,
    cultural_fit_adjustment DECIMAL(3,2) DEFAULT 0.00,
    sentiment_adjustment DECIMAL(3,2) DEFAULT 0.00,
    
    -- Sentiment-specific weights
    value_alignment_weight DECIMAL(3,2) DEFAULT 0.40,
    confidence_weight DECIMAL(3,2) DEFAULT 0.20,
    enthusiasm_weight DECIMAL(3,2) DEFAULT 0.20,
    authenticity_weight DECIMAL(3,2) DEFAULT 0.20,
    
    last_updated TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

Database Relationships

Entity Relationship Overview

Performance Optimization

Critical Indexes

-- Most frequently used queries
CREATE INDEX CONCURRENTLY idx_interview_user_status_time 
ON interview_interviewsession(user_id, status, start_time DESC);

CREATE INDEX CONCURRENTLY idx_sentiment_interview_created 
ON interview_sentimentsession(interview_session_id, created_at DESC);

CREATE INDEX CONCURRENTLY idx_skill_assessment_session_skill 
ON interview_skillassessment(interview_session_id, skill_id);

-- Analytics queries
CREATE INDEX CONCURRENTLY idx_interview_stats_company_period 
ON interview_interviewstatistics(company_id, period, date DESC);

-- Billing queries
CREATE INDEX CONCURRENTLY idx_usage_user_type_date 
ON billing_usagerecord(user_id, usage_type, created_at DESC);

-- Search and filtering
CREATE INDEX CONCURRENTLY idx_job_company_active 
ON company_job(company_id, is_active, created_at DESC);

Partitioning Strategy

-- Partition large tables by date for better performance
CREATE TABLE interview_sentimenthistory_y2024m01 
PARTITION OF interview_sentimenthistory 
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE interview_sentimenthistory_y2024m02 
PARTITION OF interview_sentimenthistory 
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- Automatic partition creation
CREATE OR REPLACE FUNCTION create_monthly_partition()
RETURNS void AS $$
DECLARE
    start_date date;
    end_date date;
    table_name text;
BEGIN
    start_date := date_trunc('month', CURRENT_DATE + interval '1 month');
    end_date := start_date + interval '1 month';
    table_name := 'interview_sentimenthistory_y' || to_char(start_date, 'YYYY') || 'm' || to_char(start_date, 'MM');
    
    EXECUTE format('CREATE TABLE %I PARTITION OF interview_sentimenthistory FOR VALUES FROM (%L) TO (%L)', 
                   table_name, start_date, end_date);
END;
$$ LANGUAGE plpgsql;

Data Integrity and Constraints

Business Logic Constraints

-- Ensure sentiment scores are valid
ALTER TABLE interview_sentimentsession 
ADD CONSTRAINT chk_sentiment_scores_valid 
CHECK (positive_score + negative_score + neutral_score + mixed_score <= 100);

-- Ensure subscription dates are logical
ALTER TABLE billing_customersubscription 
ADD CONSTRAINT chk_subscription_dates 
CHECK (end_date IS NULL OR end_date > start_date);

-- Ensure interview durations are reasonable
ALTER TABLE interview_interviewsession 
ADD CONSTRAINT chk_interview_duration 
CHECK (duration IS NULL OR (duration > 0 AND duration < 14400)); -- Max 4 hours

-- Ensure scores are within valid ranges
ALTER TABLE interview_skillassessment 
ADD CONSTRAINT chk_skill_score_range 
CHECK (accumulated_score >= 0 AND accumulated_score <= 100);

Foreign Key Relationships

-- Cascade deletes for dependent data
ALTER TABLE interview_sentimentsession 
ADD CONSTRAINT fk_sentiment_interview 
FOREIGN KEY (interview_session_id) 
REFERENCES interview_interviewsession(id) 
ON DELETE CASCADE;

-- Protect critical references
ALTER TABLE billing_customersubscription 
ADD CONSTRAINT fk_subscription_plan 
FOREIGN KEY (plan_id) 
REFERENCES billing_subscriptionplan(id) 
ON DELETE RESTRICT;

Database Maintenance

Regular Maintenance Tasks

-- Analyze table statistics (run weekly)
ANALYZE interview_interviewsession;
ANALYZE interview_sentimentsession;
ANALYZE billing_usagerecord;

-- Vacuum large tables (run monthly)
VACUUM ANALYZE interview_sentimenthistory;
VACUUM ANALYZE billing_usagerecord;

-- Reindex frequently updated tables (run monthly)
REINDEX INDEX idx_interview_user_status_time;
REINDEX INDEX idx_sentiment_interview_created;

Automated Cleanup

-- Clean up old sentiment history (keep 2 years)
DELETE FROM interview_sentimenthistory 
WHERE timestamp < NOW() - INTERVAL '2 years';

-- Archive completed interviews older than 1 year
CREATE TABLE interview_interviewsession_archive AS
SELECT * FROM interview_interviewsession 
WHERE status = 'completed' AND start_time < NOW() - INTERVAL '1 year';

-- Clean up expired OAuth tokens
DELETE FROM users_useroauthtoken 
WHERE expires_at < NOW() - INTERVAL '1 day';

Backup and Recovery

Backup Strategy

-- Full database backup (daily)
pg_dump -h localhost -U jobhive_user -d jobhive_production > backup_$(date +%Y%m%d).sql

-- Selective table backups for critical data
pg_dump -h localhost -U jobhive_user -d jobhive_production \
  -t interview_interviewsession \
  -t interview_sentimentsession \
  -t billing_customersubscription > critical_data_backup.sql

-- Point-in-time recovery setup
SELECT pg_start_backup('daily_backup');
-- Copy data files
SELECT pg_stop_backup();

Recovery Procedures

-- Restore full database
psql -h localhost -U jobhive_user -d jobhive_production < backup_20240215.sql

-- Restore specific tables
psql -h localhost -U jobhive_user -d jobhive_production < critical_data_backup.sql

-- Point-in-time recovery
pg_ctl stop -D /var/lib/postgresql/data
# Restore base backup and WAL files
pg_ctl start -D /var/lib/postgresql/data

Query Optimization Examples

Common Query Patterns

-- Get user's recent interviews with scores
SELECT 
    i.id,
    i.session_id,
    i.start_time,
    i.completion_percentage,
    i.technical_accuracy,
    i.behavioral_score,
    j.title as job_title,
    c.company_name
FROM interview_interviewsession i
JOIN company_job j ON i.job_id = j.id
JOIN company_company c ON j.company_id = c.id
WHERE i.user_id = $1 
  AND i.status = 'completed'
ORDER BY i.start_time DESC
LIMIT 10;

-- Get interview analytics for company
SELECT 
    DATE(i.start_time) as interview_date,
    COUNT(*) as total_interviews,
    COUNT(CASE WHEN i.status = 'completed' THEN 1 END) as completed,
    AVG(i.completion_percentage) as avg_score,
    AVG(EXTRACT(EPOCH FROM (i.end_time - i.start_time))/60) as avg_duration_minutes
FROM interview_interviewsession i
JOIN company_job j ON i.job_id = j.id
WHERE j.company_id = $1
  AND i.start_time >= $2
  AND i.start_time < $3
GROUP BY DATE(i.start_time)
ORDER BY interview_date DESC;

-- Get skill assessment summary for interview
SELECT 
    s.name as skill_name,
    sa.accumulated_score,
    sa.update_count,
    sc.name as category_name
FROM interview_skillassessment sa
JOIN interview_skill s ON sa.skill_id = s.id
LEFT JOIN interview_skillcategory sc ON s.category_id = sc.id
WHERE sa.interview_session_id = $1
ORDER BY sa.accumulated_score DESC;

This database schema provides a robust foundation for JobHive’s AI-powered interview platform, with careful attention to performance, scalability, and data integrity.

​Database Schema Documentation

​Overview

​Database Architecture

​Core Design Principles

​Core Database Models

​1. User Management Models

​User Model

​Resume Model

​OAuth Tokens Model

​2. Company Management Models

​Company Model

​Job Model

​3. Interview System Models

​Interview Session Model (Core)

​Sentiment Analysis Models

​Sentiment History and Context

​Skill Assessment Models

​4. Assessment and Scoring Models

​Assessment Framework

​Cultural Fit and Behavioral Analysis

​5. Learning and Development Models

​Learning Resources

​6. Billing and Subscription Models

​Subscription Plans

​Customer Subscriptions

​Usage Tracking

​7. Analytics and Reporting Models

​Interview Statistics

​Candidate Rankings

​8. Scoring and Weighting Models

​Score Weights

​Database Relationships

​Entity Relationship Overview

​Performance Optimization

​Critical Indexes

​Partitioning Strategy

​Data Integrity and Constraints

​Business Logic Constraints

​Foreign Key Relationships

​Database Maintenance

​Regular Maintenance Tasks

​Automated Cleanup

​Backup and Recovery

​Backup Strategy

​Recovery Procedures

​Query Optimization Examples

​Common Query Patterns