Database Schema

Complete database schema documentation for Netasampark.

Overview

Netasampark uses MySQL/MariaDB as the primary database with the following characteristics:

• Engine: InnoDB
• Character Set: utf8mb4
• Collation: utf8mb4_unicode_ci
• Version: MySQL 8.0+ / MariaDB 10.5+

Core Tables

Users Table

CREATE TABLE users (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    phone VARCHAR(20) UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin', 'politician', 'team_member', 'citizen') NOT NULL,
    email_verified_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_phone (phone),
    INDEX idx_role (role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Politicians Table

CREATE TABLE politicians (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    constituency VARCHAR(255),
    party VARCHAR(255),
    designation VARCHAR(255),
    bio TEXT,
    profile_image VARCHAR(255),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Voters Table

CREATE TABLE voters (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    email VARCHAR(255),
    address TEXT,
    constituency VARCHAR(255),
    booth_number VARCHAR(50),
    voter_id VARCHAR(50),
    age INT,
    gender ENUM('male', 'female', 'other'),
    created_by BIGINT UNSIGNED,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_phone (phone),
    INDEX idx_constituency (constituency),
    INDEX idx_created_by (created_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Campaigns Table

CREATE TABLE campaigns (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    politician_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    start_date DATE,
    end_date DATE,
    status ENUM('draft', 'active', 'paused', 'completed') DEFAULT 'draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (politician_id) REFERENCES politicians(id) ON DELETE CASCADE,
    INDEX idx_politician_id (politician_id),
    INDEX idx_status (status),
    INDEX idx_dates (start_date, end_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Messages Table

CREATE TABLE messages (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    campaign_id BIGINT UNSIGNED,
    sender_id BIGINT UNSIGNED NOT NULL,
    recipient_id BIGINT UNSIGNED,
    recipient_phone VARCHAR(20),
    channel ENUM('sms', 'whatsapp', 'email', 'ivr') NOT NULL,
    content TEXT NOT NULL,
    status ENUM('pending', 'sent', 'delivered', 'failed') DEFAULT 'pending',
    sent_at TIMESTAMP NULL,
    delivered_at TIMESTAMP NULL,
    error_message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE SET NULL,
    FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_campaign_id (campaign_id),
    INDEX idx_sender_id (sender_id),
    INDEX idx_status (status),
    INDEX idx_channel (channel),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Grievances Table

CREATE TABLE grievances (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    grievance_id VARCHAR(50) UNIQUE NOT NULL,
    citizen_id BIGINT UNSIGNED,
    subject VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    category VARCHAR(100) NOT NULL,
    location TEXT,
    priority ENUM('urgent', 'high', 'normal', 'low') DEFAULT 'normal',
    status ENUM('submitted', 'in_progress', 'resolved', 'closed', 'rejected') DEFAULT 'submitted',
    assigned_to BIGINT UNSIGNED,
    sla_deadline TIMESTAMP,
    resolved_at TIMESTAMP NULL,
    closed_at TIMESTAMP NULL,
    rating INT,
    feedback TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (citizen_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_grievance_id (grievance_id),
    INDEX idx_status (status),
    INDEX idx_category (category),
    INDEX idx_priority (priority),
    INDEX idx_sla_deadline (sla_deadline)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Indexing Strategy

Primary Indexes

• All tables have primary key on id
• Foreign keys are indexed
• Unique constraints are indexed

Performance Indexes

• Frequently queried columns
• Date ranges
• Status filters
• Search columns

Composite Indexes

-- For campaign queries
CREATE INDEX idx_campaign_status_dates ON campaigns(status, start_date, end_date);

-- For message queries
CREATE INDEX idx_message_status_channel ON messages(status, channel, created_at);

-- For grievance queries
CREATE INDEX idx_grievance_status_priority ON grievances(status, priority, created_at);

Relationships

One-to-Many

• User → Politicians
• Politician → Campaigns
• Campaign → Messages
• User → Grievances

Many-to-Many

• Campaigns ↔ Voters (via campaign_voters table)
• Politicians ↔ Voters (via politician_voters table)

Data Types

Strings

• VARCHAR(255): Names, titles, short text
• TEXT: Long descriptions, content
• ENUM: Fixed value sets

Numbers

• BIGINT UNSIGNED: IDs, large numbers
• INT: Age, counts
• DECIMAL(10,2): Currency, percentages

Dates

• DATE: Dates only
• TIMESTAMP: Date and time with timezone

Constraints

Foreign Keys

• Cascade on delete for dependent records
• Set null for optional relationships
• Restrict for critical relationships

Unique Constraints

• Email addresses
• Phone numbers
• Grievance IDs
• Voter IDs

Next Steps

• Migrations
• Backup/Restore
• Performance Tuning