-- ========================================================= -- Online Event Invitation Management System - DB Schema -- MySQL 8+ / MariaDB 10.4+ -- ========================================================= SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- --------------------------------------------------------- -- users (Super Administrator, Event Organizer) -- --------------------------------------------------------- CREATE TABLE IF NOT EXISTS users ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(150) NOT NULL, email VARCHAR(190) NOT NULL UNIQUE, phone VARCHAR(30) DEFAULT NULL, password VARCHAR(255) NOT NULL, role ENUM('super_admin','organizer') NOT NULL DEFAULT 'organizer', status ENUM('active','inactive','suspended') NOT NULL DEFAULT 'active', avatar VARCHAR(255) DEFAULT NULL, failed_login_attempts INT UNSIGNED NOT NULL DEFAULT 0, locked_until DATETIME DEFAULT NULL, remember_token VARCHAR(100) DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------- -- password_resets -- --------------------------------------------------------- CREATE TABLE IF NOT EXISTS password_resets ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, email VARCHAR(190) NOT NULL, token VARCHAR(100) NOT NULL, expires_at DATETIME NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX (email), INDEX (token) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------- -- events -- --------------------------------------------------------- CREATE TABLE IF NOT EXISTS events ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, organizer_id INT UNSIGNED NOT NULL, name VARCHAR(190) NOT NULL, slug VARCHAR(220) NOT NULL UNIQUE, type ENUM('Wedding','Birthday','Conference','Seminar','Church Event','Graduation','Corporate Event','Meeting','Party','Other') NOT NULL DEFAULT 'Other', description TEXT, event_date DATE NOT NULL, start_time TIME DEFAULT NULL, end_time TIME DEFAULT NULL, venue VARCHAR(190) DEFAULT NULL, address VARCHAR(255) DEFAULT NULL, map_url VARCHAR(500) DEFAULT NULL, contact_phone VARCHAR(30) DEFAULT NULL, contact_email VARCHAR(190) DEFAULT NULL, dress_code VARCHAR(150) DEFAULT NULL, cover_image VARCHAR(255) DEFAULT NULL, logo VARCHAR(255) DEFAULT NULL, max_guests INT UNSIGNED DEFAULT NULL, rsvp_deadline DATE DEFAULT NULL, status ENUM('draft','published','unpublished','archived') NOT NULL DEFAULT 'draft', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (organizer_id) REFERENCES users(id) ON DELETE CASCADE, INDEX (status), INDEX (event_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------- -- guests -- --------------------------------------------------------- CREATE TABLE IF NOT EXISTS guests ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, event_id INT UNSIGNED NOT NULL, first_name VARCHAR(100) NOT NULL, last_name VARCHAR(100) DEFAULT '', gender ENUM('Male','Female','Other','Unspecified') DEFAULT 'Unspecified', email VARCHAR(190) DEFAULT NULL, phone VARCHAR(30) DEFAULT NULL, address VARCHAR(255) DEFAULT NULL, category ENUM('VIP','Family','Friend','Staff','Client','Church Member','Sponsor','General Guest','Other') NOT NULL DEFAULT 'General Guest', allowed_guests INT UNSIGNED NOT NULL DEFAULT 1, table_number VARCHAR(20) DEFAULT NULL, dietary_requirement VARCHAR(255) DEFAULT NULL, notes TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE, INDEX (event_id), INDEX (category), FULLTEXT KEY ft_guest_name (first_name, last_name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------- -- invitation_templates -- --------------------------------------------------------- CREATE TABLE IF NOT EXISTS invitation_templates ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(150) NOT NULL, style ENUM('Elegant','Modern','Wedding','Corporate','Church/Seminar') NOT NULL DEFAULT 'Modern', description VARCHAR(255) DEFAULT NULL, preview_image VARCHAR(255) DEFAULT NULL, template_data JSON DEFAULT NULL, -- colors, fonts, layout, background status ENUM('active','inactive') NOT NULL DEFAULT 'active', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------- -- event_programmes -- --------------------------------------------------------- CREATE TABLE IF NOT EXISTS event_programmes ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, event_id INT UNSIGNED NOT NULL, activity VARCHAR(190) NOT NULL, start_time TIME DEFAULT NULL, end_time TIME DEFAULT NULL, description VARCHAR(255) DEFAULT NULL, sort_order INT UNSIGNED NOT NULL DEFAULT 0, FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE, INDEX (event_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------- -- invitations -- --------------------------------------------------------- CREATE TABLE IF NOT EXISTS invitations ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, event_id INT UNSIGNED NOT NULL, guest_id INT UNSIGNED NOT NULL, template_id INT UNSIGNED DEFAULT NULL, token VARCHAR(40) NOT NULL UNIQUE, qr_code VARCHAR(255) DEFAULT NULL, status ENUM('draft','ready','sent','delivered','opened','confirmed','declined','expired') NOT NULL DEFAULT 'draft', sent_at DATETIME DEFAULT NULL, opened_at DATETIME DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE, FOREIGN KEY (guest_id) REFERENCES guests(id) ON DELETE CASCADE, FOREIGN KEY (template_id) REFERENCES invitation_templates(id) ON DELETE SET NULL, UNIQUE KEY uniq_event_guest (event_id, guest_id), INDEX (token), INDEX (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------- -- rsvps -- --------------------------------------------------------- CREATE TABLE IF NOT EXISTS rsvps ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, invitation_id INT UNSIGNED NOT NULL UNIQUE, response ENUM('yes','no','maybe') NOT NULL, number_of_guests INT UNSIGNED NOT NULL DEFAULT 1, guest_names TEXT, phone VARCHAR(30) DEFAULT NULL, dietary_requirement VARCHAR(255) DEFAULT NULL, message TEXT, confirmation_number VARCHAR(20) DEFAULT NULL, responded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (invitation_id) REFERENCES invitations(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------- -- attendance -- --------------------------------------------------------- CREATE TABLE IF NOT EXISTS attendance ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, event_id INT UNSIGNED NOT NULL, guest_id INT UNSIGNED NOT NULL, invitation_id INT UNSIGNED DEFAULT NULL, checked_in TINYINT(1) NOT NULL DEFAULT 0, check_in_time DATETIME DEFAULT NULL, checked_in_by INT UNSIGNED DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE, FOREIGN KEY (guest_id) REFERENCES guests(id) ON DELETE CASCADE, FOREIGN KEY (invitation_id) REFERENCES invitations(id) ON DELETE SET NULL, FOREIGN KEY (checked_in_by) REFERENCES users(id) ON DELETE SET NULL, UNIQUE KEY uniq_event_guest_att (event_id, guest_id), INDEX (checked_in) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------- -- messages (email / sms / whatsapp log) -- --------------------------------------------------------- CREATE TABLE IF NOT EXISTS messages ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, event_id INT UNSIGNED NOT NULL, guest_id INT UNSIGNED DEFAULT NULL, channel ENUM('email','sms','whatsapp') NOT NULL DEFAULT 'email', subject VARCHAR(190) DEFAULT NULL, message TEXT, status ENUM('queued','sent','failed') NOT NULL DEFAULT 'queued', sent_at DATETIME DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE, FOREIGN KEY (guest_id) REFERENCES guests(id) ON DELETE CASCADE, INDEX (event_id), INDEX (channel), INDEX (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------- -- reminders (scheduling config per event) -- --------------------------------------------------------- CREATE TABLE IF NOT EXISTS reminders ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, event_id INT UNSIGNED NOT NULL, days_before INT NOT NULL, -- 0 = event morning enabled TINYINT(1) NOT NULL DEFAULT 1, last_run_at DATETIME DEFAULT NULL, FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------- -- activity_logs -- --------------------------------------------------------- CREATE TABLE IF NOT EXISTS activity_logs ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED DEFAULT NULL, action VARCHAR(100) NOT NULL, description VARCHAR(255) DEFAULT NULL, ip_address VARCHAR(45) DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL, INDEX (user_id), INDEX (action) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- --------------------------------------------------------- -- settings (key/value) -- --------------------------------------------------------- CREATE TABLE IF NOT EXISTS settings ( setting_key VARCHAR(100) PRIMARY KEY, setting_value TEXT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO settings (setting_key, setting_value) VALUES ('system_name', 'EventInvite Pro'), ('admin_email', 'admin@example.com'), ('contact_phone', ''), ('sms_provider', ''), ('sms_api_key', ''), ('smtp_host', ''), ('smtp_port', '587'), ('smtp_username', ''), ('smtp_password', ''), ('smtp_from_email', 'no-reply@example.com'), ('smtp_from_name', 'EventInvite Pro'), ('whatsapp_enabled', '1'), ('default_template_id', '1'), ('default_timezone', 'Africa/Dar_es_Salaam'), ('date_format', 'd M Y'), ('currency', 'TZS'), ('notifications_enabled', '1'), ('logo', ''), ('favicon', '') ON DUPLICATE KEY UPDATE setting_key = setting_key; -- --------------------------------------------------------- -- Seed: default invitation templates -- --------------------------------------------------------- INSERT INTO invitation_templates (name, style, description, template_data, status) VALUES ('Elegant Classic', 'Elegant', 'Refined serif type on soft cream background', '{"background":"#FBF7F0","font":"Playfair Display","font_size":"16","text_color":"#3B2F2F","button_color":"#B8860B","layout":"centered"}', 'active'), ('Modern Minimal', 'Modern', 'Clean sans-serif with bold accent color', '{"background":"#FFFFFF","font":"Inter","font_size":"16","text_color":"#1A1A2E","button_color":"#4F46E5","layout":"split"}', 'active'), ('Wedding Bliss', 'Wedding', 'Romantic florals with script accents', '{"background":"#FFF5F7","font":"Great Vibes","font_size":"18","text_color":"#5C374C","button_color":"#D96C8C","layout":"centered"}', 'active'), ('Corporate Blue', 'Corporate', 'Professional layout for conferences & meetings', '{"background":"#F4F7FB","font":"Roboto","font_size":"15","text_color":"#12263A","button_color":"#0B5ED7","layout":"banner"}', 'active'), ('Church & Seminar', 'Church/Seminar', 'Warm, welcoming design for church & seminar events', '{"background":"#FFFDF6","font":"Merriweather","font_size":"16","text_color":"#2E2A1F","button_color":"#8C6D1F","layout":"centered"}', 'active') ON DUPLICATE KEY UPDATE name = VALUES(name); SET FOREIGN_KEY_CHECKS = 1;