-- Migration 014: July 2026 feature expansion. -- MySQL syntax (not MariaDB). Adds: room fees and deposits, renter lease -- fields, maintenance ticket type + photos (renter and owner sides), routine -- maintenance plans (reminders, not an engine), property walkthrough videos, -- per-property emergency procedures, insurance policy tracking, admin -- notifications (bell + alert modal + page), applicant screening fields, -- billing schedules for approved renters, and a small key-value settings -- table for integration links. Every WHERE/JOIN column is indexed. -- Rooms: recurring monthly fees and split deposits. ALTER TABLE rooms ADD COLUMN cleaning_fee DECIMAL(10,2) NULL AFTER square_feet, ADD COLUMN utilities_fee DECIMAL(10,2) NULL AFTER cleaning_fee, ADD COLUMN room_deposit DECIMAL(10,2) NULL AFTER utilities_fee, ADD COLUMN utilities_deposit DECIMAL(10,2) NULL AFTER room_deposit; -- Renters: lease window and free-form lease details. ALTER TABLE renters ADD COLUMN lease_start DATE NULL AFTER status, ADD COLUMN lease_end DATE NULL AFTER lease_start, ADD COLUMN lease_notes TEXT NULL AFTER lease_end, ADD KEY idx_renters_lease_end (lease_end); -- Maintenance tickets: a work type alongside priority. ALTER TABLE maintenance_tickets ADD COLUMN type VARCHAR(30) NOT NULL DEFAULT 'general' AFTER title, ADD KEY idx_tickets_type (type); -- Photos on a maintenance ticket. uploaded_by records which side added the -- photo ('renter' or 'owner'); an owner photo can be hidden from the renter. CREATE TABLE ticket_photos ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, ticket_id INT UNSIGNED NOT NULL, file_path VARCHAR(255) NOT NULL, caption VARCHAR(200) NULL, uploaded_by VARCHAR(10) NOT NULL DEFAULT 'renter', visible_to_renter TINYINT(1) NOT NULL DEFAULT 1, sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_ticket_photos_ticket (ticket_id, sort_order), KEY idx_ticket_photos_visible (ticket_id, visible_to_renter), CONSTRAINT fk_ticket_photos_ticket FOREIGN KEY (ticket_id) REFERENCES maintenance_tickets (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Routine maintenance plans: repeat every N months (3/6/9/12 or custom). -- These are reminders; the owner reviews what is due and creates the ticket -- with one click. Nothing runs unattended. CREATE TABLE maintenance_plans ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, property_id INT UNSIGNED NOT NULL, room_id INT UNSIGNED NULL, vendor_id INT UNSIGNED NULL, title VARCHAR(150) NOT NULL, description TEXT NULL, type VARCHAR(30) NOT NULL DEFAULT 'general', frequency_months TINYINT UNSIGNED NOT NULL DEFAULT 6, next_due DATE NOT NULL, last_completed DATE NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_maintenance_plans_property (property_id), KEY idx_maintenance_plans_due (is_active, next_due), KEY idx_maintenance_plans_vendor (vendor_id), KEY idx_maintenance_plans_room (room_id), CONSTRAINT fk_maintenance_plans_property FOREIGN KEY (property_id) REFERENCES properties (id) ON DELETE CASCADE, CONSTRAINT fk_maintenance_plans_room FOREIGN KEY (room_id) REFERENCES rooms (id) ON DELETE SET NULL, CONSTRAINT fk_maintenance_plans_vendor FOREIGN KEY (vendor_id) REFERENCES vendors (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Property walkthrough videos (hosted URL: YouTube, Vimeo, or a direct file). CREATE TABLE property_videos ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, property_id INT UNSIGNED NOT NULL, room_id INT UNSIGNED NULL, title VARCHAR(150) NULL, video_url VARCHAR(500) NOT NULL, sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_property_videos_property (property_id, sort_order), KEY idx_property_videos_room (room_id), CONSTRAINT fk_property_videos_property FOREIGN KEY (property_id) REFERENCES properties (id) ON DELETE CASCADE, CONSTRAINT fk_property_videos_room FOREIGN KEY (room_id) REFERENCES rooms (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Per-property emergency procedures, edited in the admin and shown to the -- renters of that property (whole home or any room in it). ALTER TABLE properties ADD COLUMN emergency_procedures TEXT NULL AFTER description; -- Insurance tracking per property. Old policies stay as history rows. CREATE TABLE insurance_policies ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, property_id INT UNSIGNED NOT NULL, company VARCHAR(160) NOT NULL, policy_number VARCHAR(100) NULL, phone VARCHAR(30) NULL, email VARCHAR(190) NULL, coverage_type VARCHAR(60) NULL, premium DECIMAL(10,2) NULL, start_date DATE NULL, end_date DATE NULL, status VARCHAR(20) NOT NULL DEFAULT 'active', notes TEXT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_insurance_property (property_id), KEY idx_insurance_status (status), KEY idx_insurance_end (end_date), CONSTRAINT fk_insurance_property FOREIGN KEY (property_id) REFERENCES properties (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Admin notifications: the bell counter, the alert modal, and the -- notifications page all read this one table. dedupe_key stops repeat -- reminders (e.g. the same policy expiring) from inserting twice. CREATE TABLE notifications ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, type VARCHAR(40) NOT NULL DEFAULT 'general', title VARCHAR(200) NOT NULL, body VARCHAR(600) NULL, link VARCHAR(255) NULL, dedupe_key VARCHAR(120) NULL, is_alert TINYINT(1) NOT NULL DEFAULT 0, is_read TINYINT(1) NOT NULL DEFAULT 0, alerted_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_notifications_dedupe (dedupe_key), KEY idx_notifications_read (is_read, created_at), KEY idx_notifications_alert (is_alert, alerted_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Applicant screening progress and the generated lease link. ALTER TABLE applications ADD COLUMN screening_background VARCHAR(20) NULL AFTER status, ADD COLUMN screening_background_ref VARCHAR(190) NULL AFTER screening_background, ADD COLUMN screening_credit VARCHAR(20) NULL AFTER screening_background_ref, ADD COLUMN screening_credit_ref VARCHAR(190) NULL AFTER screening_credit, ADD COLUMN lease_url VARCHAR(500) NULL AFTER screening_credit_ref; -- Billing schedules: invoice an approved renter automatically on a set day -- of the month. The run creates provider invoices through the payments -- chokepoint and records pending payments; references only, never card data. CREATE TABLE billing_schedules ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, renter_id INT UNSIGNED NOT NULL, amount DECIMAL(10,2) NOT NULL, day_of_month TINYINT UNSIGNED NOT NULL DEFAULT 1, start_date DATE NOT NULL, end_date DATE NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, last_run_ym CHAR(7) NULL, notes VARCHAR(300) NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_billing_renter (renter_id), KEY idx_billing_active (is_active, day_of_month), CONSTRAINT fk_billing_renter FOREIGN KEY (renter_id) REFERENCES renters (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Small key-value store for owner-editable settings (integration links for -- background checks, credit checks, people search, and the lease template). CREATE TABLE app_settings ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, setting_key VARCHAR(80) NOT NULL, setting_value TEXT NULL, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_app_settings_key (setting_key) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;