-- ================================================================ -- FIELDLY — COMPLETE DATABASE SETUP -- Paste this entire file into phpMyAdmin > SQL tab and click Go -- Creates all tables + demo data in one shot -- ================================================================ -- STEP 1: In phpMyAdmin, create a new database called "fieldly" -- (utf8mb4 / utf8mb4_unicode_ci) then select it -- STEP 2: Click the SQL tab -- STEP 3: Paste everything below this line and click Go -- ================================================================ -- ================================================================ -- FIELDLY — PRODUCTION DATABASE SCHEMA -- Version: 2.0 -- Engine: MySQL 8.0+ / MariaDB 10.6+ -- All tables: InnoDB, utf8mb4_unicode_ci -- Multi-tenant: every table has company_id, every query filters by it -- ================================================================ -- Design principles: -- • BIGINT PKs on high-volume tables (activity, payments, time, files, notes) -- • INT UNSIGNED everywhere else -- • deleted_at for soft-delete on main entities -- • deleted_items for full snapshots (recycle bin) -- • JSON columns for flexible metadata, never for queried data -- • GENERATED STORED columns for computed values (cost, duration) -- • FULLTEXT indexes for search -- • No FK on activity_log.user_id (audit must survive user deletion) -- • created_at on every table, updated_at where records mutate -- • Composite indexes always lead with company_id -- ================================================================ SET NAMES utf8mb4; SET time_zone = '+00:00'; SET foreign_key_checks = 0; -- ================================================================ -- SECTION 1: TENANCY & COMPANY -- ================================================================ CREATE TABLE companies ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, uuid CHAR(36) NOT NULL, name VARCHAR(150) NOT NULL, slug VARCHAR(100) NOT NULL, plan ENUM('trial','starter','pro','enterprise') NOT NULL DEFAULT 'trial', trial_ends_at DATETIME NULL, logo_path VARCHAR(500) NULL, primary_colour VARCHAR(7) NOT NULL DEFAULT '#4f46e5', status ENUM('active','suspended','cancelled') NOT NULL DEFAULT 'active', timezone VARCHAR(60) NOT NULL DEFAULT 'Europe/London', country_code CHAR(2) NOT NULL DEFAULT 'GB', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_uuid (uuid), UNIQUE KEY uq_slug (slug), INDEX idx_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Key-value settings store, grouped for efficient partial reads -- Groups: branding | regional | numbering | invoice_defaults | -- quote_defaults | job_defaults | tax | stripe | notifications | billing CREATE TABLE company_settings ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, company_id INT UNSIGNED NOT NULL, setting_group VARCHAR(60) NOT NULL, setting_key VARCHAR(100) NOT NULL, setting_value TEXT NULL, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_company_setting (company_id, setting_group, setting_key), INDEX idx_company_group (company_id, setting_group), CONSTRAINT fk_cs_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Company offices, depots or branches CREATE TABLE company_locations ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, company_id INT UNSIGNED NOT NULL, name VARCHAR(120) NOT NULL, is_primary TINYINT(1) NOT NULL DEFAULT 0, line1 VARCHAR(150) NOT NULL DEFAULT '', line2 VARCHAR(150) NOT NULL DEFAULT '', town VARCHAR(100) NOT NULL DEFAULT '', county VARCHAR(100) NOT NULL DEFAULT '', postcode VARCHAR(12) NOT NULL DEFAULT '', country VARCHAR(80) NOT NULL DEFAULT 'United Kingdom', phone VARCHAR(30) NULL, email VARCHAR(180) NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, deleted_at DATETIME NULL, PRIMARY KEY (id), INDEX idx_company (company_id, deleted_at), CONSTRAINT fk_cl_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ================================================================ -- SECTION 2: ROLES & PERMISSIONS -- ================================================================ CREATE TABLE roles ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, company_id INT UNSIGNED NOT NULL, name VARCHAR(80) NOT NULL, description VARCHAR(300) NULL, color VARCHAR(7) NOT NULL DEFAULT '#6b7280', is_default TINYINT(1) NOT NULL DEFAULT 0, is_system TINYINT(1) NOT NULL DEFAULT 0, -- system roles cannot be deleted sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), INDEX idx_company (company_id), CONSTRAINT fk_role_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- One row per permission per role -- Permissions include: view_dashboard, view_customers, edit_customers, -- view_jobs, edit_jobs, complete_jobs, assign_jobs, -- view_quotes, edit_quotes, view_invoices, edit_invoices, -- record_payments, view_time, edit_time, log_time, -- view_reports, view_calendar, edit_calendar, -- view_settings, manage_users, manage_roles, -- view_global_settings, edit_global_settings, -- view_activity, manage_files, manage_notes CREATE TABLE role_permissions ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, role_id INT UNSIGNED NOT NULL, permission VARCHAR(80) NOT NULL, PRIMARY KEY (id), UNIQUE KEY uq_role_perm (role_id, permission), INDEX idx_role (role_id), CONSTRAINT fk_rp_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ================================================================ -- SECTION 3: USERS -- ================================================================ CREATE TABLE users ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, company_id INT UNSIGNED NOT NULL, role_id INT UNSIGNED NOT NULL, location_id INT UNSIGNED NULL, -- home branch/office first_name VARCHAR(80) NOT NULL, last_name VARCHAR(80) NOT NULL, email VARCHAR(180) NOT NULL, password_hash VARCHAR(255) NOT NULL, phone VARCHAR(30) NULL, photo_path VARCHAR(500) NULL, color VARCHAR(7) NOT NULL DEFAULT '#4f46e5', initials VARCHAR(4) NOT NULL DEFAULT '', hourly_rate DECIMAL(8,2) NOT NULL DEFAULT 0.00, status ENUM('active','inactive') NOT NULL DEFAULT 'active', last_login_at DATETIME NULL, password_reset_token VARCHAR(100) NULL, password_reset_at DATETIME NULL, notif_prefs JSON NULL, -- per-user notification preference overrides created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at DATETIME NULL, PRIMARY KEY (id), UNIQUE KEY uq_company_email (company_id, email), INDEX idx_company_status (company_id, status, deleted_at), INDEX idx_company_role (company_id, role_id), INDEX idx_company_location (company_id, location_id), CONSTRAINT fk_user_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_user_role FOREIGN KEY (role_id) REFERENCES roles(id), CONSTRAINT fk_user_location FOREIGN KEY (location_id) REFERENCES company_locations(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ================================================================ -- SECTION 4: CUSTOMERS -- ================================================================ CREATE TABLE customers ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, company_id INT UNSIGNED NOT NULL, ref VARCHAR(30) NOT NULL, -- CUST-0001 type ENUM('individual','business') NOT NULL DEFAULT 'individual', first_name VARCHAR(80) NOT NULL, last_name VARCHAR(80) NOT NULL, company_name VARCHAR(150) NULL, vat_number VARCHAR(30) NULL, email VARCHAR(180) NULL, status ENUM('active','inactive') NOT NULL DEFAULT 'active', source VARCHAR(60) NULL, -- how they came to you notes TEXT NULL, created_by INT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at DATETIME NULL, PRIMARY KEY (id), UNIQUE KEY uq_company_ref (company_id, ref), INDEX idx_company_status (company_id, status, deleted_at), INDEX idx_company_type (company_id, type), INDEX idx_company_email (company_id, email), FULLTEXT idx_ft_search (first_name, last_name, company_name, email), CONSTRAINT fk_cust_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_cust_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE customer_phones ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, customer_id INT UNSIGNED NOT NULL, type ENUM('Mobile','Work','Home','Fax','Other') NOT NULL DEFAULT 'Mobile', number VARCHAR(30) NOT NULL, is_primary TINYINT(1) NOT NULL DEFAULT 0, sort_order TINYINT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (id), INDEX idx_customer (customer_id), CONSTRAINT fk_cp_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Handles both billing address and multiple named site addresses CREATE TABLE customer_addresses ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, customer_id INT UNSIGNED NOT NULL, type ENUM('billing','site') NOT NULL DEFAULT 'billing', label VARCHAR(80) NULL, -- "Head Office", "Site A", "Warehouse" line1 VARCHAR(150) NOT NULL DEFAULT '', line2 VARCHAR(150) NOT NULL DEFAULT '', line3 VARCHAR(150) NOT NULL DEFAULT '', town VARCHAR(100) NOT NULL DEFAULT '', county VARCHAR(100) NOT NULL DEFAULT '', postcode VARCHAR(12) NOT NULL DEFAULT '', country VARCHAR(80) NOT NULL DEFAULT 'United Kingdom', what3words VARCHAR(60) NULL, -- future: what3words location access_notes VARCHAR(500) NULL, -- gate codes, parking, hazards is_primary TINYINT(1) NOT NULL DEFAULT 0, is_default TINYINT(1) NOT NULL DEFAULT 0, -- default site for new jobs created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, deleted_at DATETIME NULL, PRIMARY KEY (id), INDEX idx_customer_type (customer_id, type, deleted_at), CONSTRAINT fk_ca_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Business customers only — multiple named contacts CREATE TABLE customer_contacts ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, customer_id INT UNSIGNED NOT NULL, name VARCHAR(120) NOT NULL, role_title VARCHAR(80) NULL, email VARCHAR(180) NULL, phone VARCHAR(30) NULL, is_primary TINYINT(1) NOT NULL DEFAULT 0, is_billing TINYINT(1) NOT NULL DEFAULT 0, sort_order TINYINT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (id), INDEX idx_customer (customer_id), CONSTRAINT fk_cc_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE customer_tags ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, company_id INT UNSIGNED NOT NULL, customer_id INT UNSIGNED NOT NULL, tag VARCHAR(60) NOT NULL, PRIMARY KEY (id), UNIQUE KEY uq_customer_tag (customer_id, tag), INDEX idx_company_tag (company_id, tag), CONSTRAINT fk_ct_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE, CONSTRAINT fk_ct_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ================================================================ -- SECTION 5: CUSTOM FIELDS -- Covers: customer | job | quote | invoice -- ================================================================ CREATE TABLE custom_field_defs ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, company_id INT UNSIGNED NOT NULL, entity_type ENUM('customer','job','quote','invoice') NOT NULL, name VARCHAR(100) NOT NULL, field_key VARCHAR(60) NOT NULL, -- snake_case identifier used in code field_type ENUM('text','number','date','dropdown','boolean','textarea','url','email','phone') NOT NULL DEFAULT 'text', dropdown_options TEXT NULL, -- comma-separated option values placeholder VARCHAR(200) NULL, help_text VARCHAR(300) NULL, required TINYINT(1) NOT NULL DEFAULT 0, applies_to ENUM('all','business','individual') NOT NULL DEFAULT 'all', sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0, active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_company_entity_key (company_id, entity_type, field_key), INDEX idx_company_entity (company_id, entity_type, active, sort_order), CONSTRAINT fk_cfd_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE custom_field_values ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, company_id INT UNSIGNED NOT NULL, entity_type ENUM('customer','job','quote','invoice') NOT NULL, entity_id INT UNSIGNED NOT NULL, field_def_id INT UNSIGNED NOT NULL, value TEXT NULL, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_entity_field (entity_type, entity_id, field_def_id), INDEX idx_company_entity (company_id, entity_type, entity_id), INDEX idx_field_def (field_def_id), CONSTRAINT fk_cfv_def FOREIGN KEY (field_def_id) REFERENCES custom_field_defs(id) ON DELETE CASCADE, CONSTRAINT fk_cfv_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ================================================================ -- SECTION 6: JOB CONFIGURATION -- ================================================================ CREATE TABLE job_types ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, company_id INT UNSIGNED NOT NULL, name VARCHAR(80) NOT NULL, color VARCHAR(7) NOT NULL DEFAULT '#4f46e5', icon VARCHAR(30) NULL, -- future: icon identifier description VARCHAR(300) NULL, active 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), INDEX idx_company_active (company_id, active, sort_order), CONSTRAINT fk_jt_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE job_statuses ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, company_id INT UNSIGNED NOT NULL, name VARCHAR(80) NOT NULL, color VARCHAR(7) NOT NULL DEFAULT '#6b7280', is_default TINYINT(1) NOT NULL DEFAULT 0, is_complete TINYINT(1) NOT NULL DEFAULT 0, -- reaching this status marks job done is_cancelled TINYINT(1) NOT NULL DEFAULT 0, triggers_invoice TINYINT(1) NOT NULL DEFAULT 0, -- future: auto-create invoice active 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), INDEX idx_company_active (company_id, active, sort_order), INDEX idx_company_default (company_id, is_default), CONSTRAINT fk_js_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Pricing types: basis determines how job is priced and invoiced -- basis: hourly | fixed_amount | estimated | quoted | no_charge | custom CREATE TABLE pricing_types ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, company_id INT UNSIGNED NOT NULL, name VARCHAR(80) NOT NULL, basis ENUM('hourly','fixed_amount','estimated','quoted','no_charge','custom') NOT NULL DEFAULT 'fixed_amount', chargeable TINYINT(1) NOT NULL DEFAULT 1, description VARCHAR(300) NULL, is_default TINYINT(1) NOT NULL DEFAULT 0, active 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), INDEX idx_company_active (company_id, active, sort_order), INDEX idx_company_default (company_id, is_default), CONSTRAINT fk_pt_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ================================================================ -- SECTION 7: JOBS -- ================================================================ CREATE TABLE jobs ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, company_id INT UNSIGNED NOT NULL, customer_id INT UNSIGNED NOT NULL, type_id INT UNSIGNED NULL, -- job_types.id status_id INT UNSIGNED NOT NULL, -- job_statuses.id pricing_type_id INT UNSIGNED NULL, -- pricing_types.id location_id INT UNSIGNED NULL, -- company_locations.id (dispatching branch) site_address_id INT UNSIGNED NULL, -- customer_addresses.id (type=site) source_quote_id INT UNSIGNED NULL, -- quotes.id if created from quote number VARCHAR(20) NOT NULL, -- JOB-0162 title VARCHAR(200) NOT NULL, description TEXT NULL, internal_notes TEXT NULL, customer_notes TEXT NULL, -- visible to customer on job sheet priority ENUM('low','normal','high','urgent') NOT NULL DEFAULT 'normal', estimated_mins SMALLINT UNSIGNED NULL, scheduled_date DATE NULL, scheduled_time TIME NULL, pricing_rate DECIMAL(10,2) NULL, -- hourly rate OR fixed price source ENUM('manual','quote','recurring','duplicate','api') NOT NULL DEFAULT 'manual', completion_note TEXT NULL, completion_sig_path VARCHAR(500) NULL, -- path to signature image completed_at DATETIME NULL, created_by INT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at DATETIME NULL, PRIMARY KEY (id), UNIQUE KEY uq_company_number (company_id, number), INDEX idx_company_status (company_id, status_id, deleted_at), INDEX idx_company_customer (company_id, customer_id), INDEX idx_company_scheduled (company_id, scheduled_date, deleted_at), INDEX idx_company_created (company_id, created_at), INDEX idx_company_priority (company_id, priority, deleted_at), INDEX idx_company_type (company_id, type_id), INDEX idx_source_quote (source_quote_id), CONSTRAINT fk_job_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_job_customer FOREIGN KEY (customer_id) REFERENCES customers(id), CONSTRAINT fk_job_type FOREIGN KEY (type_id) REFERENCES job_types(id) ON DELETE SET NULL, CONSTRAINT fk_job_status FOREIGN KEY (status_id) REFERENCES job_statuses(id), CONSTRAINT fk_job_pricing_type FOREIGN KEY (pricing_type_id) REFERENCES pricing_types(id) ON DELETE SET NULL, CONSTRAINT fk_job_location FOREIGN KEY (location_id) REFERENCES company_locations(id) ON DELETE SET NULL, CONSTRAINT fk_job_site_address FOREIGN KEY (site_address_id) REFERENCES customer_addresses(id) ON DELETE SET NULL, CONSTRAINT fk_job_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE job_assignees ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, job_id INT UNSIGNED NOT NULL, user_id INT UNSIGNED NOT NULL, is_lead TINYINT(1) NOT NULL DEFAULT 0, -- lead engineer on the job assigned_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, assigned_by INT UNSIGNED NULL, PRIMARY KEY (id), UNIQUE KEY uq_job_user (job_id, user_id), INDEX idx_user_job (user_id), CONSTRAINT fk_ja_job FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE, CONSTRAINT fk_ja_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, CONSTRAINT fk_ja_assigned FOREIGN KEY (assigned_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ================================================================ -- SECTION 8: VISITS & CHECK-INS -- ================================================================ CREATE TABLE visits ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, company_id INT UNSIGNED NOT NULL, job_id INT UNSIGNED NOT NULL, title VARCHAR(200) NULL, status ENUM('scheduled','in_progress','completed','cancelled','no_show') NOT NULL DEFAULT 'scheduled', scheduled_start DATETIME NULL, scheduled_end DATETIME NULL, actual_start DATETIME NULL, actual_end DATETIME NULL, duration_mins SMALLINT UNSIGNED NULL, -- actual duration notes TEXT NULL, travel_mins SMALLINT UNSIGNED NULL, -- future: travel time tracking created_by INT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), INDEX idx_company_job (company_id, job_id), INDEX idx_company_start (company_id, scheduled_start), INDEX idx_company_status (company_id, status), CONSTRAINT fk_visit_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_visit_job FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE, CONSTRAINT fk_visit_created FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE visit_assignees ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, visit_id INT UNSIGNED NOT NULL, user_id INT UNSIGNED NOT NULL, PRIMARY KEY (id), UNIQUE KEY uq_visit_user (visit_id, user_id), INDEX idx_user (user_id), CONSTRAINT fk_va_visit FOREIGN KEY (visit_id) REFERENCES visits(id) ON DELETE CASCADE, CONSTRAINT fk_va_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE check_ins ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, company_id INT UNSIGNED NOT NULL, job_id INT UNSIGNED NOT NULL, visit_id INT UNSIGNED NULL, user_id INT UNSIGNED NOT NULL, checked_in_at DATETIME NOT NULL, checked_out_at DATETIME NULL, duration_mins SMALLINT UNSIGNED NULL, lat_in DECIMAL(10,7) NULL, lng_in DECIMAL(10,7) NULL, lat_out DECIMAL(10,7) NULL, -- future: location at checkout lng_out DECIMAL(10,7) NULL, notes VARCHAR(500) NULL, auto_time_entry TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), INDEX idx_company_job (company_id, job_id), INDEX idx_company_user (company_id, user_id, checked_in_at), INDEX idx_active_checkin (company_id, user_id, checked_out_at), -- find open check-ins CONSTRAINT fk_ci_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_ci_job FOREIGN KEY (job_id) REFERENCES jobs(id), CONSTRAINT fk_ci_visit FOREIGN KEY (visit_id) REFERENCES visits(id) ON DELETE SET NULL, CONSTRAINT fk_ci_user FOREIGN KEY (user_id) REFERENCES users(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ================================================================ -- SECTION 9: CHECKLISTS -- ================================================================ CREATE TABLE checklist_templates ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, company_id INT UNSIGNED NOT NULL, name VARCHAR(100) NOT NULL, job_type_id INT UNSIGNED NULL, -- auto-apply when this job type selected description 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), INDEX idx_company (company_id), INDEX idx_job_type (company_id, job_type_id), CONSTRAINT fk_clt_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_clt_job_type FOREIGN KEY (job_type_id) REFERENCES job_types(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE checklist_template_items ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, template_id INT UNSIGNED NOT NULL, text VARCHAR(500) NOT NULL, is_required TINYINT(1) NOT NULL DEFAULT 0, -- future: must complete before status change sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (id), INDEX idx_template (template_id), CONSTRAINT fk_clti_template FOREIGN KEY (template_id) REFERENCES checklist_templates(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE job_checklists ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, job_id INT UNSIGNED NOT NULL, company_id INT UNSIGNED NOT NULL, template_id INT UNSIGNED NULL, -- source template (for reporting) text VARCHAR(500) NOT NULL, completed TINYINT(1) NOT NULL DEFAULT 0, completed_by INT UNSIGNED NULL, completed_at DATETIME NULL, photo_path VARCHAR(500) NULL, -- future: photo evidence for checklist item notes VARCHAR(500) NULL, -- future: notes per item sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (id), INDEX idx_job (job_id), INDEX idx_company_job (company_id, job_id), CONSTRAINT fk_jcl_job FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE, CONSTRAINT fk_jcl_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_jcl_user FOREIGN KEY (completed_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ================================================================ -- SECTION 10: QUOTES -- ================================================================ CREATE TABLE quotes ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, company_id INT UNSIGNED NOT NULL, customer_id INT UNSIGNED NOT NULL, job_id INT UNSIGNED NULL, number VARCHAR(20) NOT NULL, -- QUO-0051 title VARCHAR(200) NOT NULL DEFAULT '', status ENUM('draft','sent','accepted','declined','expired','converted') NOT NULL DEFAULT 'draft', valid_until DATE NULL, subtotal DECIMAL(10,2) NOT NULL DEFAULT 0.00, discount_total DECIMAL(10,2) NOT NULL DEFAULT 0.00, tax_total DECIMAL(10,2) NOT NULL DEFAULT 0.00, total DECIMAL(10,2) NOT NULL DEFAULT 0.00, notes TEXT NULL, terms TEXT NULL, footer_text TEXT NULL, sent_at DATETIME NULL, accepted_at DATETIME NULL, declined_at DATETIME NULL, created_by INT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at DATETIME NULL, PRIMARY KEY (id), UNIQUE KEY uq_company_number (company_id, number), INDEX idx_company_status (company_id, status, deleted_at), INDEX idx_company_customer (company_id, customer_id), INDEX idx_company_created (company_id, created_at), INDEX idx_company_valid (company_id, valid_until), CONSTRAINT fk_quote_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_quote_customer FOREIGN KEY (customer_id) REFERENCES customers(id), CONSTRAINT fk_quote_job FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE SET NULL, CONSTRAINT fk_quote_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE quote_items ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, quote_id INT UNSIGNED NOT NULL, type ENUM('labour','material','expense','other') NOT NULL DEFAULT 'other', -- future: categorised line items description VARCHAR(500) NOT NULL, qty DECIMAL(10,3) NOT NULL DEFAULT 1.000, unit VARCHAR(20) NULL, -- "hrs", "m²", "each" unit_price DECIMAL(10,2) NOT NULL DEFAULT 0.00, discount DECIMAL(5,2) NOT NULL DEFAULT 0.00, -- percentage tax_rate_id INT UNSIGNED NULL, -- tax_rates.id tax_rate DECIMAL(5,2) NOT NULL DEFAULT 20.00, line_total DECIMAL(10,2) NOT NULL DEFAULT 0.00, sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (id), INDEX idx_quote (quote_id), CONSTRAINT fk_qi_quote FOREIGN KEY (quote_id) REFERENCES quotes(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ================================================================ -- SECTION 11: INVOICES & PAYMENTS -- ================================================================ CREATE TABLE invoices ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, company_id INT UNSIGNED NOT NULL, customer_id INT UNSIGNED NOT NULL, job_id INT UNSIGNED NULL, quote_id INT UNSIGNED NULL, number VARCHAR(20) NOT NULL, -- INV-0089 title VARCHAR(200) NOT NULL DEFAULT '', status ENUM('draft','sent','part_paid','paid','overdue','void') NOT NULL DEFAULT 'draft', due_date DATE NULL, subtotal DECIMAL(10,2) NOT NULL DEFAULT 0.00, discount_total DECIMAL(10,2) NOT NULL DEFAULT 0.00, tax_total DECIMAL(10,2) NOT NULL DEFAULT 0.00, total DECIMAL(10,2) NOT NULL DEFAULT 0.00, amount_paid DECIMAL(10,2) NOT NULL DEFAULT 0.00, balance_due DECIMAL(10,2) NOT NULL DEFAULT 0.00, notes TEXT NULL, footer_text TEXT NULL, payment_terms VARCHAR(300) NULL, bank_details TEXT NULL, purchase_order_no VARCHAR(60) NULL, -- customer PO number stripe_payment_intent VARCHAR(100) NULL, stripe_payment_link VARCHAR(500) NULL, -- future: hosted payment page sent_at DATETIME NULL, paid_at DATETIME NULL, voided_at DATETIME NULL, voided_by INT UNSIGNED NULL, created_by INT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at DATETIME NULL, PRIMARY KEY (id), UNIQUE KEY uq_company_number (company_id, number), INDEX idx_company_status (company_id, status, deleted_at), INDEX idx_company_customer (company_id, customer_id), INDEX idx_company_due (company_id, due_date, status), INDEX idx_company_created (company_id, created_at), INDEX idx_stripe_intent (stripe_payment_intent), CONSTRAINT fk_inv_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_inv_customer FOREIGN KEY (customer_id) REFERENCES customers(id), CONSTRAINT fk_inv_job FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE SET NULL, CONSTRAINT fk_inv_quote FOREIGN KEY (quote_id) REFERENCES quotes(id) ON DELETE SET NULL, CONSTRAINT fk_inv_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL, CONSTRAINT fk_inv_voided_by FOREIGN KEY (voided_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE invoice_items ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, invoice_id INT UNSIGNED NOT NULL, type ENUM('labour','material','expense','other') NOT NULL DEFAULT 'other', description VARCHAR(500) NOT NULL, qty DECIMAL(10,3) NOT NULL DEFAULT 1.000, unit VARCHAR(20) NULL, unit_price DECIMAL(10,2) NOT NULL DEFAULT 0.00, discount DECIMAL(5,2) NOT NULL DEFAULT 0.00, tax_rate_id INT UNSIGNED NULL, tax_rate DECIMAL(5,2) NOT NULL DEFAULT 20.00, line_total DECIMAL(10,2) NOT NULL DEFAULT 0.00, sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (id), INDEX idx_invoice (invoice_id), CONSTRAINT fk_ii_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE tax_rates ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, company_id INT UNSIGNED NOT NULL, name VARCHAR(60) NOT NULL, rate DECIMAL(5,2) NOT NULL, is_default TINYINT(1) NOT NULL DEFAULT 0, active TINYINT(1) NOT NULL DEFAULT 1, country_code CHAR(2) NULL, -- future: per-country tax rates created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), INDEX idx_company (company_id, active), CONSTRAINT fk_tr_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE payments ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, company_id INT UNSIGNED NOT NULL, invoice_id INT UNSIGNED NOT NULL, amount DECIMAL(10,2) NOT NULL, method ENUM('cash','bank_transfer','card','cheque','direct_debit','stripe','bacs','paypal','crypto','other') NOT NULL, reference VARCHAR(100) NULL, stripe_charge_id VARCHAR(100) NULL, stripe_refund_id VARCHAR(100) NULL, -- future: refund tracking paid_at DATETIME NOT NULL, recorded_by INT UNSIGNED NULL, notes VARCHAR(500) NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, deleted_at DATETIME NULL, PRIMARY KEY (id), INDEX idx_invoice (invoice_id, deleted_at), INDEX idx_company_paid_at (company_id, paid_at, deleted_at), INDEX idx_company_method (company_id, method), INDEX idx_stripe (stripe_charge_id), CONSTRAINT fk_pay_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_pay_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id), CONSTRAINT fk_pay_recorded FOREIGN KEY (recorded_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ================================================================ -- SECTION 12: TIME TRACKING -- ================================================================ CREATE TABLE time_entries ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, company_id INT UNSIGNED NOT NULL, job_id INT UNSIGNED NULL, visit_id INT UNSIGNED NULL, check_in_id BIGINT UNSIGNED NULL, user_id INT UNSIGNED NOT NULL, date DATE NOT NULL, start_time TIME NULL, end_time TIME NULL, duration_mins SMALLINT UNSIGNED NOT NULL, billable TINYINT(1) NOT NULL DEFAULT 1, rate DECIMAL(8,2) NOT NULL DEFAULT 0.00, -- Stored computed column: avoids calculating in PHP on every invoice cost DECIMAL(10,2) GENERATED ALWAYS AS (ROUND((duration_mins / 60) * rate, 2)) STORED, approved TINYINT(1) NOT NULL DEFAULT 0, -- future: time approval workflow approved_by INT UNSIGNED NULL, notes VARCHAR(500) NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, deleted_at DATETIME NULL, PRIMARY KEY (id), INDEX idx_company_user_date (company_id, user_id, date, deleted_at), INDEX idx_company_job (company_id, job_id, deleted_at), INDEX idx_company_date (company_id, date), INDEX idx_company_billable (company_id, billable, deleted_at), CONSTRAINT fk_te_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_te_job FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE SET NULL, CONSTRAINT fk_te_visit FOREIGN KEY (visit_id) REFERENCES visits(id) ON DELETE SET NULL, CONSTRAINT fk_te_user FOREIGN KEY (user_id) REFERENCES users(id), CONSTRAINT fk_te_checkin FOREIGN KEY (check_in_id) REFERENCES check_ins(id) ON DELETE SET NULL, CONSTRAINT fk_te_approver FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ================================================================ -- SECTION 13: SCHEDULING -- ================================================================ CREATE TABLE working_hours ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, company_id INT UNSIGNED NOT NULL, user_id INT UNSIGNED NOT NULL, day_of_week TINYINT UNSIGNED NOT NULL, -- 0=Sunday, 1=Monday … 6=Saturday start_time TIME NULL, -- NULL means day off end_time TIME NULL, break_mins SMALLINT UNSIGNED NOT NULL DEFAULT 0, -- future: break time PRIMARY KEY (id), UNIQUE KEY uq_user_day (user_id, day_of_week), INDEX idx_company_user (company_id, user_id), CONSTRAINT fk_wh_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_wh_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE leave_entries ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, company_id INT UNSIGNED NOT NULL, user_id INT UNSIGNED NOT NULL, type ENUM('holiday','sick','training','compassionate','parental','unpaid','other') NOT NULL, start_date DATE NOT NULL, end_date DATE NOT NULL, days_taken DECIMAL(4,1) NOT NULL DEFAULT 1.0, -- supports half-days notes VARCHAR(500) NULL, approved TINYINT(1) NOT NULL DEFAULT 0, approved_by INT UNSIGNED NULL, approved_at DATETIME NULL, rejected TINYINT(1) NOT NULL DEFAULT 0, rejection_reason VARCHAR(300) NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), INDEX idx_company_user (company_id, user_id), INDEX idx_company_dates (company_id, start_date, end_date), INDEX idx_company_type (company_id, type, approved), CONSTRAINT fk_le_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_le_user FOREIGN KEY (user_id) REFERENCES users(id), CONSTRAINT fk_le_approver FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ================================================================ -- SECTION 14: CONTENT — NOTES & FILES -- ================================================================ -- Polymorphic: entity_type = job | quote | invoice | customer | visit CREATE TABLE notes ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, company_id INT UNSIGNED NOT NULL, entity_type VARCHAR(20) NOT NULL, entity_id INT UNSIGNED NOT NULL, text TEXT NOT NULL, is_internal TINYINT(1) NOT NULL DEFAULT 0, -- staff-only note pinned TINYINT(1) NOT NULL DEFAULT 0, -- future: pin important notes created_by INT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at DATETIME NULL, PRIMARY KEY (id), INDEX idx_entity (entity_type, entity_id, deleted_at), INDEX idx_company_type (company_id, entity_type), INDEX idx_company_user (company_id, created_by), CONSTRAINT fk_note_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_note_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE files ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, company_id INT UNSIGNED NOT NULL, entity_type VARCHAR(20) NOT NULL, entity_id INT UNSIGNED NOT NULL, filename VARCHAR(200) NOT NULL, -- stored name on disk original_name VARCHAR(200) NOT NULL, -- original upload name mime_type VARCHAR(100) NOT NULL, file_size INT UNSIGNED NOT NULL DEFAULT 0, -- bytes disk VARCHAR(20) NOT NULL DEFAULT 'local', -- local | s3 | gcs disk_path VARCHAR(500) NOT NULL, thumbnail_path VARCHAR(500) NULL, -- future: image thumbnails is_public TINYINT(1) NOT NULL DEFAULT 0, -- future: customer-visible portal uploaded_by INT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, deleted_at DATETIME NULL, PRIMARY KEY (id), INDEX idx_entity (entity_type, entity_id, deleted_at), INDEX idx_company (company_id, entity_type), CONSTRAINT fk_file_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_file_uploaded_by FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ================================================================ -- SECTION 15: ACTIVITY LOG & NOTIFICATIONS -- Full audit trail — every meaningful action logged here -- ================================================================ CREATE TABLE activity_log ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, company_id INT UNSIGNED NOT NULL, type VARCHAR(60) NOT NULL, -- e.g. job.created, invoice.paid, customer.deleted entity_type VARCHAR(20) NOT NULL, entity_id INT UNSIGNED NULL, entity_ref VARCHAR(30) NULL, -- JOB-0156, INV-0089 for display without joins user_id INT UNSIGNED NULL, -- NO FK — audit must survive user deletion user_name VARCHAR(160) NULL, -- denormalised for same reason description VARCHAR(500) NOT NULL, meta JSON NULL, -- old status, new status, amount, diff etc ip_address VARCHAR(45) NULL, user_agent VARCHAR(300) NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), INDEX idx_company_entity (company_id, entity_type, entity_id), INDEX idx_company_type (company_id, type), INDEX idx_company_user (company_id, user_id), INDEX idx_company_created (company_id, created_at), CONSTRAINT fk_al_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE notifications ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, company_id INT UNSIGNED NOT NULL, user_id INT UNSIGNED NOT NULL, activity_log_id BIGINT UNSIGNED NOT NULL, type VARCHAR(60) NOT NULL, -- mirrors activity_log.type for filtering without join read_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), INDEX idx_user_read (user_id, read_at), INDEX idx_company_user (company_id, user_id, created_at), CONSTRAINT fk_notif_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_notif_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, CONSTRAINT fk_notif_activity FOREIGN KEY (activity_log_id) REFERENCES activity_log(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ================================================================ -- SECTION 16: RECYCLE BIN -- snapshot_json holds the full record + child rows at time of delete -- Supports restore and permanent delete -- ================================================================ CREATE TABLE deleted_items ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, company_id INT UNSIGNED NOT NULL, entity_type VARCHAR(30) NOT NULL, -- job | invoice | quote | customer | payment | time_entry entity_id BIGINT UNSIGNED NOT NULL, entity_ref VARCHAR(30) NULL, -- JOB-0156, INV-0089 entity_summary VARCHAR(300) NULL, -- "Nolan Construction – £570" for UI display snapshot_json LONGTEXT NOT NULL, -- full row data + child rows deleted_by INT UNSIGNED NULL, deleted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, restored_at DATETIME NULL, restored_by INT UNSIGNED NULL, purged_at DATETIME NULL, -- permanently deleted PRIMARY KEY (id), INDEX idx_company_type (company_id, entity_type, deleted_at), INDEX idx_company_entity (company_id, entity_id), CONSTRAINT fk_di_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_di_deleter FOREIGN KEY (deleted_by) REFERENCES users(id) ON DELETE SET NULL, CONSTRAINT fk_di_restorer FOREIGN KEY (restored_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ================================================================ -- SECTION 17: INTEGRATIONS & EXTERNAL SERVICES -- ================================================================ CREATE TABLE integrations ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, company_id INT UNSIGNED NOT NULL, provider ENUM( 'stripe','xero','quickbooks','sage','freeagent', 'gocardless','paypal', 'google_calendar','outlook_calendar', 'twilio','sendgrid','mailgun', 'zapier','webhooks', 'google_maps','what3words' ) NOT NULL, credentials_encrypted TEXT NULL, -- AES-256 encrypted; key stored in .env not DB settings_json JSON NULL, -- non-sensitive config (webhook URLs, preferences) active TINYINT(1) NOT NULL DEFAULT 0, connected_at DATETIME NULL, last_sync_at DATETIME NULL, last_error VARCHAR(500) NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_company_provider (company_id, provider), CONSTRAINT fk_int_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Webhook event log for integrations (Stripe, Zapier etc) CREATE TABLE webhook_events ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, company_id INT UNSIGNED NULL, -- NULL if company not yet resolved provider VARCHAR(30) NOT NULL, event_type VARCHAR(100) NOT NULL, external_id VARCHAR(100) NULL, -- provider's event ID (idempotency) payload LONGTEXT NULL, -- raw payload for debugging status ENUM('pending','processed','failed','ignored') NOT NULL DEFAULT 'pending', error VARCHAR(500) NULL, processed_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_provider_event (provider, external_id), INDEX idx_company_status (company_id, status), INDEX idx_provider_type (provider, event_type), CONSTRAINT fk_we_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ================================================================ -- SECTION 18: BILLING & SUBSCRIPTIONS -- ================================================================ CREATE TABLE subscriptions ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, company_id INT UNSIGNED NOT NULL, plan ENUM('starter','pro','enterprise') NOT NULL, status ENUM('active','past_due','cancelled','trialling','paused') NOT NULL, user_limit SMALLINT UNSIGNED NOT NULL DEFAULT 0, -- 0 = unlimited stripe_sub_id VARCHAR(100) NULL, stripe_cust_id VARCHAR(100) NULL, current_period_start DATETIME NULL, current_period_end DATETIME NULL, cancel_at_period_end TINYINT(1) NOT NULL DEFAULT 0, cancelled_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_company (company_id), INDEX idx_stripe_sub (stripe_sub_id), INDEX idx_stripe_cust (stripe_cust_id), INDEX idx_status (status), CONSTRAINT fk_sub_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Usage metrics for billing and plan enforcement CREATE TABLE usage_metrics ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, company_id INT UNSIGNED NOT NULL, metric VARCHAR(60) NOT NULL, -- active_users | jobs_this_month | storage_gb value DECIMAL(12,4) NOT NULL DEFAULT 0, period_start DATE NOT NULL, period_end DATE NOT NULL, recorded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_company_metric_period (company_id, metric, period_start), INDEX idx_company_metric (company_id, metric), CONSTRAINT fk_um_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ================================================================ -- SECTION 19: API & SESSIONS (CodeIgniter) -- ================================================================ CREATE TABLE api_tokens ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, company_id INT UNSIGNED NOT NULL, user_id INT UNSIGNED NULL, -- NULL for company-level tokens name VARCHAR(80) NOT NULL, -- "Mobile App", "Zapier" token_hash VARCHAR(64) NOT NULL, -- SHA-256 of token abilities JSON NULL, -- scoped permissions for this token last_used_at DATETIME NULL, expires_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, revoked_at DATETIME NULL, PRIMARY KEY (id), UNIQUE KEY uq_token_hash (token_hash), INDEX idx_company (company_id, revoked_at), CONSTRAINT fk_at_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_at_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- CI4 session table (standard format) CREATE TABLE ci_sessions ( id VARCHAR(128) NOT NULL, ip_address VARCHAR(45) NOT NULL, timestamp DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, data BLOB NOT NULL, PRIMARY KEY (id), KEY idx_timestamp (timestamp) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ================================================================ -- SECTION 20: FUTURE-PROOFING TABLES -- (schema defined now so migrations are minimal later) -- ================================================================ -- Customer portal access — customers log in to view jobs/invoices/pay CREATE TABLE customer_portal_users ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, company_id INT UNSIGNED NOT NULL, customer_id INT UNSIGNED NOT NULL, email VARCHAR(180) NOT NULL, password_hash VARCHAR(255) NOT NULL, status ENUM('active','invited','suspended') NOT NULL DEFAULT 'invited', invite_token VARCHAR(100) NULL, last_login_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_company_email (company_id, email), INDEX idx_company_customer (company_id, customer_id), CONSTRAINT fk_cpu_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_cpu_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Recurring job schedules CREATE TABLE recurring_schedules ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, company_id INT UNSIGNED NOT NULL, customer_id INT UNSIGNED NOT NULL, type_id INT UNSIGNED NULL, status_id INT UNSIGNED NULL, title VARCHAR(200) NOT NULL, description TEXT NULL, frequency ENUM('daily','weekly','fortnightly','monthly','quarterly','yearly') NOT NULL, interval_value SMALLINT UNSIGNED NOT NULL DEFAULT 1, day_of_week TINYINT UNSIGNED NULL, day_of_month TINYINT UNSIGNED NULL, start_date DATE NOT NULL, end_date DATE NULL, next_run_date DATE NULL, last_run_date DATE NULL, active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), INDEX idx_company_next (company_id, next_run_date, active), INDEX idx_company_cust (company_id, customer_id), CONSTRAINT fk_rs_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_rs_customer FOREIGN KEY (customer_id) REFERENCES customers(id), CONSTRAINT fk_rs_type FOREIGN KEY (type_id) REFERENCES job_types(id) ON DELETE SET NULL, CONSTRAINT fk_rs_status FOREIGN KEY (status_id) REFERENCES job_statuses(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Materials and parts catalogue CREATE TABLE catalogue_items ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, company_id INT UNSIGNED NOT NULL, sku VARCHAR(60) NULL, name VARCHAR(200) NOT NULL, description TEXT NULL, category VARCHAR(80) NULL, unit VARCHAR(20) NULL, -- "each", "m²", "litre" unit_cost DECIMAL(10,2) NOT NULL DEFAULT 0.00, unit_price DECIMAL(10,2) NOT NULL DEFAULT 0.00, tax_rate DECIMAL(5,2) NOT NULL DEFAULT 20.00, 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), INDEX idx_company_active (company_id, active), INDEX idx_company_category (company_id, category), FULLTEXT idx_ft_search (name, description, sku), CONSTRAINT fk_ci_company_1 FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Email template storage (invoice emails, quote emails etc) CREATE TABLE email_templates ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, company_id INT UNSIGNED NOT NULL, type ENUM('invoice','quote','job_complete','payment_receipt','reminder','welcome','custom') NOT NULL, name VARCHAR(100) NOT NULL, subject VARCHAR(200) NOT NULL, body_html TEXT NOT NULL, body_text TEXT NULL, is_default TINYINT(1) NOT NULL DEFAULT 0, 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), INDEX idx_company_type (company_id, type, active), CONSTRAINT fk_et_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Sent email log CREATE TABLE email_log ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, company_id INT UNSIGNED NOT NULL, entity_type VARCHAR(20) NULL, entity_id INT UNSIGNED NULL, to_email VARCHAR(180) NOT NULL, to_name VARCHAR(120) NULL, subject VARCHAR(200) NOT NULL, template_id INT UNSIGNED NULL, provider VARCHAR(30) NULL, -- sendgrid | mailgun | smtp provider_id VARCHAR(100) NULL, -- provider message ID status ENUM('queued','sent','delivered','opened','bounced','failed') NOT NULL DEFAULT 'queued', opened_at DATETIME NULL, sent_at DATETIME NULL, error VARCHAR(500) NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), INDEX idx_company_entity (company_id, entity_type, entity_id), INDEX idx_company_status (company_id, status), INDEX idx_provider (provider, provider_id), CONSTRAINT fk_el_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; SET foreign_key_checks = 1; -- ================================================================ -- SUMMARY -- 26 tables across 20 sections: -- 1. companies -- tenants -- 2. company_settings -- key/value config store -- 3. company_locations -- offices/depots/branches -- 4. roles -- access roles -- 5. role_permissions -- one row per permission per role -- 6. users -- staff accounts -- 7. customers -- customer records -- 8. customer_phones -- multiple phone numbers -- 9. customer_addresses -- billing + multiple site addresses -- 10. customer_contacts -- named contacts (business only) -- 11. customer_tags -- freeform tags -- 12. custom_field_defs -- field definitions (all 4 entity types) -- 13. custom_field_values -- field values per record -- 14. job_types -- customisable job type list -- 15. job_statuses -- customisable status list with completion/cancel flags -- 16. pricing_types -- pricing basis (hourly/fixed/estimated/quoted/free) -- 17. jobs -- job records -- 18. job_assignees -- many engineers per job -- 19. visits -- scheduled appointments within jobs -- 20. visit_assignees -- engineers per visit -- 21. check_ins -- real-time clock-in/out with GPS -- 22. checklist_templates -- reusable checklist templates -- 23. checklist_template_items -- 24. job_checklists -- actual checklist items on a job -- 25. quotes -- quote records -- 26. quote_items -- line items -- 27. invoices -- invoice records -- 28. invoice_items -- line items -- 29. tax_rates -- per-company tax rate library -- 30. payments -- payment records (separate from invoices) -- 31. time_entries -- billable/non-billable time (GENERATED cost column) -- 32. working_hours -- per-user schedule (per day, NULL = day off) -- 33. leave_entries -- holiday/sick/training leave -- 34. notes -- polymorphic notes (job/quote/invoice/customer/visit) -- 35. files -- polymorphic file attachments -- 36. activity_log -- full audit trail (NO FK on user_id intentionally) -- 37. notifications -- per-user notification inbox -- 38. deleted_items -- recycle bin with JSON snapshot -- 39. integrations -- Stripe, Xero, GoCardless, calendar, etc -- 40. webhook_events -- inbound webhook log for idempotency -- 41. subscriptions -- Stripe subscription per company -- 42. usage_metrics -- billing/plan usage tracking -- 43. api_tokens -- scoped API access tokens -- 44. ci_sessions -- CodeIgniter 4 session storage -- 45. customer_portal_users -- future: customer self-service portal -- 46. recurring_schedules -- future: repeat job automation -- 47. catalogue_items -- future: materials/parts library -- 48. email_templates -- future: branded email templates -- 49. email_log -- future: sent email audit trail -- -- BIGINT PKs: activity_log, notifications, payments, time_entries, -- check_ins, notes, files, deleted_items, webhook_events, email_log -- All others: INT UNSIGNED -- Every table: company_id composite index (tenant isolation) -- Soft delete: deleted_at on customers, users, jobs, quotes, invoices, payments, -- time_entries, notes, files -- Hard delete with snapshot: deleted_items table -- ================================================================ -- ================================================================ -- DEMO DATA — Bristol Heating & Plumbing Ltd -- ================================================================ -- ================================================================ -- FIELDLY — DEMO DATABASE -- Run AFTER fieldly_schema.sql -- Creates a realistic demo company with customers, jobs, quotes, -- invoices, payments, time entries, activity and more -- ================================================================ SET NAMES utf8mb4; SET foreign_key_checks = 0; -- ── Company ────────────────────────────────────────────────────── INSERT INTO companies (id, uuid, name, slug, plan, primary_colour, status, timezone, country_code, created_at, updated_at) VALUES (1, 'a1b2c3d4e5f6a1b2c3d4e5f6a1b2c3d4', 'Bristol Heating & Plumbing Ltd', 'bristol-hp', 'pro', '#4f46e5', 'active', 'Europe/London', 'GB', NOW(), NOW()); -- ── Settings ───────────────────────────────────────────────────── INSERT INTO company_settings (company_id, setting_group, setting_key, setting_value, updated_at) VALUES (1,'branding','company_name','Bristol Heating & Plumbing Ltd',NOW()), (1,'branding','theme_id','indigo',NOW()), (1,'branding','dark_mode','0',NOW()), (1,'regional','country','GB',NOW()), (1,'regional','currency','GBP',NOW()), (1,'regional','language','en-GB',NOW()), (1,'regional','timezone','Europe/London',NOW()), (1,'regional','date_format','DD/MM/YYYY',NOW()), (1,'numbering','cust_prefix','CUST-',NOW()), (1,'numbering','cust_next','7',NOW()), (1,'numbering','job_prefix','JOB-',NOW()), (1,'numbering','job_next','9',NOW()), (1,'numbering','quote_prefix','QUO-',NOW()), (1,'numbering','quote_next','5',NOW()), (1,'numbering','inv_prefix','INV-',NOW()), (1,'numbering','inv_next','7',NOW()), (1,'invoice_defaults','payment_terms','Payment due within 30 days of invoice date',NOW()), (1,'invoice_defaults','bank_details','Account: Bristol HP Ltd\nSort Code: 20-00-00\nAccount No: 12345678',NOW()), (1,'invoice_defaults','footer_text','Thank you for choosing Bristol Heating & Plumbing Ltd',NOW()), (1,'quote_defaults','valid_days','30',NOW()), (1,'stripe','enabled','0',NOW()), (1,'stripe','publishable_key','',NOW()); -- ── Tax Rates ───────────────────────────────────────────────────── INSERT INTO tax_rates (id, company_id, name, rate, is_default, active, created_at) VALUES (1, 1, 'Standard Rate (20%)', 20.00, 1, 1, NOW()), (2, 1, 'Reduced Rate (5%)', 5.00, 0, 1, NOW()), (3, 1, 'Zero Rated (0%)', 0.00, 0, 1, NOW()); -- ── Roles ───────────────────────────────────────────────────────── INSERT INTO roles (id, company_id, name, color, is_default, is_system, sort_order, created_at, updated_at) VALUES (1, 1, 'Admin', '#ef4444', 0, 1, 0, NOW(), NOW()), (2, 1, 'Office Manager', '#f97316', 0, 1, 1, NOW(), NOW()), (3, 1, 'Engineer', '#22c55e', 1, 1, 2, NOW(), NOW()), (4, 1, 'Accounts', '#eab308', 0, 1, 3, NOW(), NOW()), (5, 1, 'Supervisor', '#3b82f6', 0, 1, 4, NOW(), NOW()); -- Admin permissions (all) INSERT INTO role_permissions (role_id, permission) VALUES (1,'view_dashboard'),(1,'view_customers'),(1,'edit_customers'), (1,'view_jobs'),(1,'edit_jobs'),(1,'complete_jobs'),(1,'assign_jobs'), (1,'view_quotes'),(1,'edit_quotes'),(1,'view_invoices'),(1,'edit_invoices'), (1,'record_payments'),(1,'view_time'),(1,'edit_time'),(1,'log_time'), (1,'view_reports'),(1,'view_calendar'),(1,'edit_calendar'),(1,'view_settings'), (1,'manage_users'),(1,'manage_roles'),(1,'view_global_settings'),(1,'edit_global_settings'), (1,'view_activity'),(1,'manage_files'),(1,'manage_notes'); -- Engineer permissions INSERT INTO role_permissions (role_id, permission) VALUES (3,'view_dashboard'),(3,'view_jobs'),(3,'complete_jobs'),(3,'log_time'), (3,'view_time'),(3,'view_calendar'),(3,'manage_files'),(3,'manage_notes'),(3,'view_activity'); -- Accounts permissions INSERT INTO role_permissions (role_id, permission) VALUES (4,'view_dashboard'),(4,'view_invoices'),(4,'edit_invoices'),(4,'record_payments'), (4,'view_quotes'),(4,'view_reports'),(4,'view_customers'),(4,'view_activity'); -- Office Manager permissions INSERT INTO role_permissions (role_id, permission) VALUES (2,'view_dashboard'),(2,'view_customers'),(2,'edit_customers'), (2,'view_jobs'),(2,'edit_jobs'),(2,'assign_jobs'),(2,'view_quotes'),(2,'edit_quotes'), (2,'view_invoices'),(2,'edit_invoices'),(2,'record_payments'),(2,'view_time'), (2,'edit_time'),(2,'log_time'),(2,'view_reports'),(2,'view_calendar'),(2,'edit_calendar'), (2,'view_settings'),(2,'manage_users'),(2,'view_global_settings'), (2,'view_activity'),(2,'manage_files'),(2,'manage_notes'); -- ── Job Types ────────────────────────────────────────────────────── INSERT INTO job_types (id, company_id, name, color, active, sort_order, created_at) VALUES (1, 1, 'Boiler Service', '#4f46e5', 1, 0, NOW()), (2, 1, 'Boiler Repair', '#ef4444', 1, 1, NOW()), (3, 1, 'Boiler Installation', '#f97316', 1, 2, NOW()), (4, 1, 'Plumbing Repair', '#06b6d4', 1, 3, NOW()), (5, 1, 'Radiator Installation','#8b5cf6', 1, 4, NOW()), (6, 1, 'Emergency Call Out', '#dc2626', 1, 5, NOW()), (7, 1, 'Gas Safety Certificate','#22c55e',1, 6, NOW()), (8, 1, 'Survey / Quote Visit', '#64748b', 1, 7, NOW()); -- ── Job Statuses ─────────────────────────────────────────────────── INSERT INTO job_statuses (id, company_id, name, color, is_default, is_complete, is_cancelled, active, sort_order, created_at) VALUES (1, 1, 'Scheduled', '#3b82f6', 1, 0, 0, 1, 0, NOW()), (2, 1, 'In Progress', '#f97316', 0, 0, 0, 1, 1, NOW()), (3, 1, 'On Hold', '#eab308', 0, 0, 0, 1, 2, NOW()), (4, 1, 'Completed', '#22c55e', 0, 1, 0, 1, 3, NOW()), (5, 1, 'Cancelled', '#6b7280', 0, 0, 1, 1, 4, NOW()), (6, 1, 'Quoted', '#8b5cf6', 0, 0, 0, 1, 5, NOW()); -- ── Pricing Types ────────────────────────────────────────────────── INSERT INTO pricing_types (id, company_id, name, basis, chargeable, description, is_default, active, sort_order, created_at) VALUES (1, 1, 'Hourly Rate', 'hourly', 1, 'Charged by time worked', 1, 1, 0, NOW()), (2, 1, 'Fixed Price', 'fixed_amount', 1, 'Set price agreed upfront', 0, 1, 1, NOW()), (3, 1, 'Per Quote', 'quoted', 1, 'Price defined in linked quote', 0, 1, 2, NOW()), (4, 1, 'No Charge', 'no_charge', 0, 'Free of charge', 0, 1, 3, NOW()), (5, 1, 'Warranty', 'no_charge', 0, 'Covered under warranty', 0, 1, 4, NOW()), (6, 1, 'Estimated', 'estimated', 1, 'Estimate — may vary', 0, 1, 5, NOW()), (7, 1, 'Call Out Fee', 'fixed_amount', 1, 'Fixed call-out charge', 0, 1, 6, NOW()); -- ── Users ────────────────────────────────────────────────────────── -- password for all users: password INSERT INTO users (id, company_id, role_id, first_name, last_name, email, password_hash, phone, color, initials, hourly_rate, status, created_at, updated_at) VALUES (1, 1, 1, 'Sarah', 'Mitchell', 'sarah@bristolhp.co.uk', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '07700 900001', '#4f46e5', 'SM', 0.00, 'active', NOW(), NOW()), (2, 1, 2, 'Tom', 'Bradley', 'tom@bristolhp.co.uk', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '07700 900002', '#f97316', 'TB', 0.00, 'active', NOW(), NOW()), (3, 1, 3, 'James', 'Hartley', 'james@bristolhp.co.uk', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '07700 900003', '#22c55e', 'JH', 45.00, 'active', NOW(), NOW()), (4, 1, 3, 'Priya', 'Sharma', 'priya@bristolhp.co.uk', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '07700 900004', '#8b5cf6', 'PS', 42.00, 'active', NOW(), NOW()), (5, 1, 4, 'Rachel', 'Owen', 'rachel@bristolhp.co.uk', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '07700 900005', '#eab308', 'RO', 0.00, 'active', NOW(), NOW()); -- Working hours (Mon–Fri 08:00–17:00 for all) INSERT INTO working_hours (company_id, user_id, day_of_week, start_time, end_time) VALUES (1,1,1,'08:00:00','17:00:00'),(1,1,2,'08:00:00','17:00:00'),(1,1,3,'08:00:00','17:00:00'),(1,1,4,'08:00:00','17:00:00'),(1,1,5,'08:00:00','17:00:00'), (1,2,1,'08:00:00','17:00:00'),(1,2,2,'08:00:00','17:00:00'),(1,2,3,'08:00:00','17:00:00'),(1,2,4,'08:00:00','17:00:00'),(1,2,5,'08:00:00','17:00:00'), (1,3,1,'07:30:00','16:30:00'),(1,3,2,'07:30:00','16:30:00'),(1,3,3,'07:30:00','16:30:00'),(1,3,4,'07:30:00','16:30:00'),(1,3,5,'07:30:00','16:30:00'), (1,4,1,'08:00:00','17:00:00'),(1,4,2,'08:00:00','17:00:00'),(1,4,3,'08:00:00','17:00:00'),(1,4,4,'08:00:00','17:00:00'),(1,4,5,'08:00:00','17:00:00'), (1,5,1,'09:00:00','17:00:00'),(1,5,2,'09:00:00','17:00:00'),(1,5,3,'09:00:00','17:00:00'),(1,5,4,'09:00:00','17:00:00'),(1,5,5,'09:00:00','17:00:00'); -- ── Customers ────────────────────────────────────────────────────── INSERT INTO customers (id, company_id, ref, type, first_name, last_name, company_name, vat_number, email, status, notes, created_by, created_at, updated_at) VALUES (1, 1, 'CUST-0001', 'individual', 'David', 'Thornton', NULL, NULL, 'david.thornton@gmail.com', 'active', 'Long-term customer. Prefers morning appointments.', 1, DATE_SUB(NOW(),INTERVAL 18 MONTH), NOW()), (2, 1, 'CUST-0002', 'business', 'Nolan', 'Construction','Nolan Construction Ltd', 'GB123456789', 'accounts@nolanconstruction.co.uk','active', 'Large commercial account. Net 30 payment terms.', 1, DATE_SUB(NOW(),INTERVAL 14 MONTH), NOW()), (3, 1, 'CUST-0003', 'individual', 'Margaret', 'Davies', NULL, NULL, 'margaret.davies@hotmail.co.uk', 'active', NULL, 1, DATE_SUB(NOW(),INTERVAL 12 MONTH), NOW()), (4, 1, 'CUST-0004', 'business', 'Green', 'Lettings', 'Green Lettings Ltd', 'GB987654321', 'maintenance@greenlettings.co.uk','active','Manages 24 rental properties. Regular contract.', 1, DATE_SUB(NOW(),INTERVAL 10 MONTH), NOW()), (5, 1, 'CUST-0005', 'individual', 'Robert', 'Ashford', NULL, NULL, 'r.ashford@outlook.com', 'active', NULL, 2, DATE_SUB(NOW(),INTERVAL 6 MONTH), NOW()), (6, 1, 'CUST-0006', 'individual', 'Helen', 'Patel', NULL, NULL, 'helen.patel@gmail.com', 'active', 'New customer — referred by David Thornton.', 2, DATE_SUB(NOW(),INTERVAL 2 MONTH), NOW()); -- Phones INSERT INTO customer_phones (customer_id, type, number, is_primary, sort_order) VALUES (1, 'Mobile', '07711 100001', 1, 0), (1, 'Home', '0117 900 0001',0, 1), (2, 'Work', '0117 900 0002', 1, 0), (2, 'Mobile', '07711 100002', 0, 1), (3, 'Mobile', '07711 100003', 1, 0), (4, 'Work', '0117 900 0004', 1, 0), (5, 'Mobile', '07711 100005', 1, 0), (6, 'Mobile', '07711 100006', 1, 0); -- Addresses INSERT INTO customer_addresses (customer_id, type, label, line1, town, county, postcode, country, is_primary, is_default, created_at) VALUES (1, 'billing', 'Home', '14 Harbour View', 'Bristol', 'Bristol', 'BS1 4RB', 'United Kingdom', 1, 1, NOW()), (1, 'site', 'Home', '14 Harbour View', 'Bristol', 'Bristol', 'BS1 4RB', 'United Kingdom', 1, 1, NOW()), (2, 'billing', 'Head Office', '45 Temple Street', 'Bristol', 'Bristol', 'BS1 6AA', 'United Kingdom', 1, 0, NOW()), (2, 'site', 'Main Office', '45 Temple Street', 'Bristol', 'Bristol', 'BS1 6AA', 'United Kingdom', 1, 1, NOW()), (2, 'site', 'Site B', 'Industrial Park, Unit 4', 'Keynsham', 'Somerset', 'BS31 2AB', 'United Kingdom', 0, 0, NOW()), (3, 'billing', 'Home', '8 Maple Close', 'Clifton', 'Bristol', 'BS8 2TH', 'United Kingdom', 1, 1, NOW()), (3, 'site', 'Home', '8 Maple Close', 'Clifton', 'Bristol', 'BS8 2TH', 'United Kingdom', 1, 1, NOW()), (4, 'billing', 'Office', '22 Queens Road', 'Bristol', 'Bristol', 'BS8 1RE', 'United Kingdom', 1, 0, NOW()), (4, 'site', 'Property 1', '15 Park Street', 'Bristol', 'Bristol', 'BS1 5PE', 'United Kingdom', 0, 1, NOW()), (4, 'site', 'Property 2', '7 Victoria Road', 'Fishponds', 'Bristol', 'BS16 3LU','United Kingdom', 0, 0, NOW()), (5, 'billing', 'Home', '3 Redland Grove', 'Redland', 'Bristol', 'BS6 6PX', 'United Kingdom', 1, 1, NOW()), (5, 'site', 'Home', '3 Redland Grove', 'Redland', 'Bristol', 'BS6 6PX', 'United Kingdom', 1, 1, NOW()), (6, 'billing', 'Home', '19 Cotham Hill', 'Bristol', 'Bristol', 'BS6 6JY', 'United Kingdom', 1, 1, NOW()), (6, 'site', 'Home', '19 Cotham Hill', 'Bristol', 'Bristol', 'BS6 6JY', 'United Kingdom', 1, 1, NOW()); -- Contacts (business only) INSERT INTO customer_contacts (customer_id, name, role_title, email, phone, is_primary, is_billing, sort_order) VALUES (2, 'James Nolan', 'Managing Director', 'james@nolanconstruction.co.uk', '07711 200001', 1, 0, 0), (2, 'Sue Chambers', 'Accounts Manager', 'sue@nolanconstruction.co.uk', '07711 200002', 0, 1, 1), (4, 'Oliver Green', 'Director', 'oliver@greenlettings.co.uk', '07711 300001', 1, 1, 0), (4, 'Amy Prescott', 'Property Manager', 'amy@greenlettings.co.uk', '07711 300002', 0, 0, 1); -- Tags INSERT INTO customer_tags (company_id, customer_id, tag) VALUES (1, 1, 'Residential'), (1, 1, 'Annual Service'), (1, 2, 'Commercial'), (1, 2, 'Priority'), (1, 2, 'Net-30'), (1, 3, 'Residential'), (1, 3, 'OAP Discount'), (1, 4, 'Commercial'), (1, 4, 'Contract'), (1, 4, 'Landlord'), (1, 5, 'Residential'), (1, 6, 'Residential'), (1, 6, 'Referral'); -- ── Checklist Templates ──────────────────────────────────────────── INSERT INTO checklist_templates (id, company_id, name, job_type_id, created_at, updated_at) VALUES (1, 1, 'Boiler Service Checklist', 1, NOW(), NOW()), (2, 1, 'Gas Safety Certificate Checks', 7, NOW(), NOW()), (3, 1, 'New Boiler Installation', 3, NOW(), NOW()); INSERT INTO checklist_template_items (template_id, text, sort_order) VALUES (1, 'Check boiler pressure (should be 1–1.5 bar)', 0), (1, 'Inspect flue and ventilation', 1), (1, 'Clean burner and heat exchanger', 2), (1, 'Check all seals and gaskets', 3), (1, 'Test thermostat and controls', 4), (1, 'Check gas pressure and flow rate', 5), (1, 'Run boiler through full cycle', 6), (1, 'Check for CO leaks', 7), (1, 'Customer signature obtained', 8), (2, 'Check all gas appliances', 0), (2, 'Test flues and ventilation', 1), (2, 'Check for gas leaks', 2), (2, 'Verify safe isolation', 3), (2, 'Complete certificate documentation', 4), (3, 'Site survey and measurements', 0), (3, 'Old boiler removed and disposed', 1), (3, 'New boiler mounted and connected', 2), (3, 'System flushed and inhibitor added', 3), (3, 'Flue fitted and tested', 4), (3, 'Commission and set controls', 5), (3, 'Customer handed documentation', 6); -- ── Jobs ─────────────────────────────────────────────────────────── INSERT INTO jobs (id, company_id, customer_id, type_id, status_id, pricing_type_id, number, title, description, priority, estimated_mins, scheduled_date, scheduled_time, pricing_rate, source, completed_at, created_by, created_at, updated_at) VALUES -- Completed (1, 1, 1, 1, 4, 1, 'JOB-0001', 'Annual Boiler Service', 'Annual service on Vaillant combi boiler', 'normal', 90, DATE_SUB(CURDATE(),INTERVAL 45 DAY), '09:00:00', 45.00, 'manual', DATE_SUB(NOW(),INTERVAL 44 DAY), 1, DATE_SUB(NOW(),INTERVAL 50 DAY), NOW()), (2, 1, 2, 4, 4, 2, 'JOB-0002', 'Fix Burst Pipe - Site B', 'Burst pipe in utility area at industrial park site', 'high', 120, DATE_SUB(CURDATE(),INTERVAL 30 DAY), '08:00:00', 350.00, 'manual', DATE_SUB(NOW(),INTERVAL 29 DAY), 1, DATE_SUB(NOW(),INTERVAL 32 DAY), NOW()), (3, 1, 3, 7, 4, 2, 'JOB-0003', 'Gas Safety Certificate', 'Annual gas safety inspection and certificate', 'normal', 60, DATE_SUB(CURDATE(),INTERVAL 20 DAY), '14:00:00', 85.00, 'manual', DATE_SUB(NOW(),INTERVAL 19 DAY), 2, DATE_SUB(NOW(),INTERVAL 22 DAY), NOW()), (4, 1, 4, 3, 4, 3, 'JOB-0004', 'New Boiler - 15 Park Street', 'Supply and install new Worcester Bosch combi boiler', 'normal', 480, DATE_SUB(CURDATE(),INTERVAL 14 DAY), '08:00:00', 0.00, 'quote', DATE_SUB(NOW(),INTERVAL 12 DAY), 1, DATE_SUB(NOW(),INTERVAL 20 DAY), NOW()), -- Active / scheduled (5, 1, 5, 2, 1, 1, 'JOB-0005', 'Boiler Not Firing', 'Worcester boiler not lighting — error code E9', 'high', 90, DATE_ADD(CURDATE(),INTERVAL 1 DAY), '10:00:00', 45.00, 'manual', NULL, 2, DATE_SUB(NOW(),INTERVAL 3 DAY), NOW()), (6, 1, 4, 1, 1, 1, 'JOB-0006', 'Annual Service - Victoria Rd', 'Annual boiler service at rental property', 'normal', 90, DATE_ADD(CURDATE(),INTERVAL 3 DAY), '13:00:00', 45.00, 'manual', NULL, 2, DATE_SUB(NOW(),INTERVAL 1 DAY), NOW()), (7, 1, 6, 6, 2, 7, 'JOB-0007', 'Emergency - No Hot Water', 'Customer has no hot water — possible diverter valve fault', 'urgent', 60, CURDATE(), '11:30:00', 75.00, 'manual', NULL, 2, NOW(), NOW()), -- Quoted / pending (8, 1, 1, 3, 6, 3, 'JOB-0008', 'Boiler Replacement Quote', 'Survey visit for boiler replacement quotation', 'normal', 60, DATE_ADD(CURDATE(),INTERVAL 7 DAY), '09:00:00', 0.00, 'manual', NULL, 1, DATE_SUB(NOW(),INTERVAL 2 DAY), NOW()); -- Assignees INSERT INTO job_assignees (job_id, user_id, assigned_at, assigned_by) VALUES (1, 3, DATE_SUB(NOW(),INTERVAL 50 DAY), 1), (2, 3, DATE_SUB(NOW(),INTERVAL 32 DAY), 1), (2, 4, DATE_SUB(NOW(),INTERVAL 32 DAY), 1), (3, 4, DATE_SUB(NOW(),INTERVAL 22 DAY), 2), (4, 3, DATE_SUB(NOW(),INTERVAL 20 DAY), 1), (4, 4, DATE_SUB(NOW(),INTERVAL 20 DAY), 1), (5, 3, DATE_SUB(NOW(),INTERVAL 3 DAY), 2), (6, 4, DATE_SUB(NOW(),INTERVAL 1 DAY), 2), (7, 3, NOW(), 2), (8, 4, DATE_SUB(NOW(),INTERVAL 2 DAY), 1); -- Checklists for completed jobs INSERT INTO job_checklists (job_id, company_id, template_id, text, completed, completed_by, completed_at, sort_order) VALUES (1, 1, 1, 'Check boiler pressure (should be 1–1.5 bar)', 1, 3, DATE_SUB(NOW(),INTERVAL 44 DAY), 0), (1, 1, 1, 'Inspect flue and ventilation', 1, 3, DATE_SUB(NOW(),INTERVAL 44 DAY), 1), (1, 1, 1, 'Clean burner and heat exchanger', 1, 3, DATE_SUB(NOW(),INTERVAL 44 DAY), 2), (1, 1, 1, 'Check all seals and gaskets', 1, 3, DATE_SUB(NOW(),INTERVAL 44 DAY), 3), (1, 1, 1, 'Test thermostat and controls', 1, 3, DATE_SUB(NOW(),INTERVAL 44 DAY), 4), (1, 1, 1, 'Check gas pressure and flow rate', 1, 3, DATE_SUB(NOW(),INTERVAL 44 DAY), 5), (1, 1, 1, 'Run boiler through full cycle', 1, 3, DATE_SUB(NOW(),INTERVAL 44 DAY), 6), (1, 1, 1, 'Check for CO leaks', 1, 3, DATE_SUB(NOW(),INTERVAL 44 DAY), 7), (1, 1, 1, 'Customer signature obtained', 1, 3, DATE_SUB(NOW(),INTERVAL 44 DAY), 8), (3, 1, 2, 'Check all gas appliances', 1, 4, DATE_SUB(NOW(),INTERVAL 19 DAY), 0), (3, 1, 2, 'Test flues and ventilation', 1, 4, DATE_SUB(NOW(),INTERVAL 19 DAY), 1), (3, 1, 2, 'Check for gas leaks', 1, 4, DATE_SUB(NOW(),INTERVAL 19 DAY), 2), (3, 1, 2, 'Verify safe isolation', 1, 4, DATE_SUB(NOW(),INTERVAL 19 DAY), 3), (3, 1, 2, 'Complete certificate documentation', 1, 4, DATE_SUB(NOW(),INTERVAL 19 DAY), 4), -- Active job checklists (not completed) (7, 1, NULL, 'Diagnose fault — check diverter valve', 0, NULL, NULL, 0), (7, 1, NULL, 'Check pressure and expansion vessel', 0, NULL, NULL, 1), (7, 1, NULL, 'Test hot water output', 0, NULL, NULL, 2), (7, 1, NULL, 'Replace faulty parts if required', 0, NULL, NULL, 3); -- ── Time Entries ─────────────────────────────────────────────────── INSERT INTO time_entries (company_id, job_id, user_id, date, start_time, end_time, duration_mins, billable, rate, notes, created_at) VALUES (1, 1, 3, DATE_SUB(CURDATE(),INTERVAL 44 DAY), '09:00:00', '10:45:00', 105, 1, 45.00, 'Annual service completed', NOW()), (1, 2, 3, DATE_SUB(CURDATE(),INTERVAL 29 DAY), '08:00:00', '10:30:00', 150, 1, 45.00, 'Locate and repair burst pipe', NOW()), (1, 2, 4, DATE_SUB(CURDATE(),INTERVAL 29 DAY), '08:30:00', '10:00:00', 90, 1, 42.00, 'Assist with repair', NOW()), (1, 3, 4, DATE_SUB(CURDATE(),INTERVAL 19 DAY), '14:00:00', '15:15:00', 75, 1, 42.00, 'Gas safety inspection', NOW()), (1, 4, 3, DATE_SUB(CURDATE(),INTERVAL 12 DAY), '08:00:00', '13:00:00', 300, 1, 45.00, 'New boiler installation day 1', NOW()), (1, 4, 4, DATE_SUB(CURDATE(),INTERVAL 12 DAY), '08:00:00', '12:00:00', 240, 1, 42.00, 'Assist with installation', NOW()), (1, 4, 3, DATE_SUB(CURDATE(),INTERVAL 11 DAY), '08:00:00', '10:30:00', 150, 1, 45.00, 'Commission and handover', NOW()); -- ── Quotes ───────────────────────────────────────────────────────── INSERT INTO quotes (id, company_id, customer_id, job_id, number, title, status, valid_until, subtotal, tax_total, total, notes, sent_at, accepted_at, created_by, created_at, updated_at) VALUES (1, 1, 4, 4, 'QUO-0001', 'New Boiler Installation - 15 Park Street', 'accepted', DATE_ADD(CURDATE(),INTERVAL 16 DAY), 1958.33, 391.67, 2350.00, 'Worcester Bosch 30i combi boiler. 5 year parts & labour warranty included.', DATE_SUB(NOW(),INTERVAL 22 DAY), DATE_SUB(NOW(),INTERVAL 18 DAY), 1, DATE_SUB(NOW(),INTERVAL 25 DAY), NOW()), (2, 1, 1, 8, 'QUO-0002', 'Boiler Replacement - 14 Harbour View', 'sent', DATE_ADD(CURDATE(),INTERVAL 28 DAY), 1625.00, 325.00, 1950.00, 'Vaillant ecoTEC plus 30kW combi. Includes full system flush and new controls.', DATE_SUB(NOW(),INTERVAL 1 DAY), NULL, 1, DATE_SUB(NOW(),INTERVAL 2 DAY), NOW()), (3, 1, 2, NULL, 'QUO-0003', 'Annual Service Contract - All Sites', 'draft', DATE_ADD(CURDATE(),INTERVAL 30 DAY), 2083.33, 416.67, 2500.00, 'Annual service contract for 3 properties. Includes priority call-outs.', NULL, NULL, 2, DATE_SUB(NOW(),INTERVAL 4 DAY), NOW()), (4, 1, 3, NULL, 'QUO-0004', 'Radiator Upgrade - 8 Maple Close', 'declined', DATE_SUB(CURDATE(),INTERVAL 5 DAY), 541.67, 108.33, 650.00, '4x new radiators with TRVs. System powerflush included.', DATE_SUB(NOW(),INTERVAL 25 DAY), NULL, 2, DATE_SUB(NOW(),INTERVAL 30 DAY), NOW()); INSERT INTO quote_items (quote_id, description, qty, unit_price, discount, tax_rate, line_total, sort_order) VALUES (1, 'Worcester Bosch 30i Combi Boiler', 1.00, 950.00, 0, 20, 950.00, 0), (1, 'Labour — removal and installation', 8.00, 75.00, 0, 20, 600.00, 1), (1, 'Flue kit and fittings', 1.00, 125.00, 0, 20, 125.00, 2), (1, 'System flush and inhibitor', 1.00, 150.00, 0, 20, 150.00, 3), (1, 'Magnetic filter installation', 1.00, 133.33, 0, 20, 133.33, 4), (2, 'Vaillant ecoTEC plus 30kW Combi Boiler', 1.00, 875.00, 0, 20, 875.00, 0), (2, 'Labour — removal and installation', 8.00, 75.00, 0, 20, 600.00, 1), (2, 'Flue kit and fittings', 1.00, 100.00, 0, 20, 100.00, 2), (2, 'System flush and inhibitor', 1.00, 50.00, 0, 20, 50.00, 3), (3, 'Annual boiler service — 3 properties', 3.00, 350.00, 0, 20, 1050.00, 0), (3, 'Priority call-out (up to 3/year)', 1.00, 583.33, 0, 20, 583.33, 1), (3, 'Gas safety certificates — 3 properties', 3.00, 150.00, 0, 20, 450.00, 2), (4, 'Radiator 600x1200mm with TRV', 4.00, 95.00, 0, 20, 380.00, 0), (4, 'Labour — fit and connect 4 radiators', 2.00, 55.00, 0, 20, 110.00, 1), (4, 'Powerflush — full system', 1.00, 51.67, 0, 20, 51.67, 2); -- ── Invoices ─────────────────────────────────────────────────────── INSERT INTO invoices (id, company_id, customer_id, job_id, quote_id, number, title, status, due_date, subtotal, tax_total, total, amount_paid, balance_due, notes, payment_terms, bank_details, sent_at, paid_at, created_by, created_at, updated_at) VALUES -- Paid (1, 1, 1, 1, NULL, 'INV-0001', 'Annual Boiler Service - 14 Harbour View', 'paid', DATE_SUB(CURDATE(),INTERVAL 15 DAY), 78.75, 15.75, 94.50, 94.50, 0.00, NULL, 'Payment due within 30 days', 'Sort: 20-00-00 / Acc: 12345678', DATE_SUB(NOW(),INTERVAL 44 DAY), DATE_SUB(NOW(),INTERVAL 30 DAY), 1, DATE_SUB(NOW(),INTERVAL 44 DAY), NOW()), (2, 1, 2, 2, NULL, 'INV-0002', 'Emergency Pipe Repair - Site B', 'paid', DATE_SUB(CURDATE(),INTERVAL 1 DAY), 350.00, 70.00, 420.00, 420.00, 0.00, NULL, 'Payment due within 30 days', 'Sort: 20-00-00 / Acc: 12345678', DATE_SUB(NOW(),INTERVAL 29 DAY), DATE_SUB(NOW(),INTERVAL 10 DAY), 1, DATE_SUB(NOW(),INTERVAL 29 DAY), NOW()), (3, 1, 3, 3, NULL, 'INV-0003', 'Gas Safety Certificate - 8 Maple Close', 'paid', DATE_SUB(CURDATE(),INTERVAL 10 DAY), 70.83, 14.17, 85.00, 85.00, 0.00, NULL, 'Payment due within 30 days', 'Sort: 20-00-00 / Acc: 12345678', DATE_SUB(NOW(),INTERVAL 19 DAY), DATE_SUB(NOW(),INTERVAL 5 DAY), 5, DATE_SUB(NOW(),INTERVAL 19 DAY), NOW()), -- Invoiced from accepted quote (4, 1, 4, 4, 1, 'INV-0004', 'New Boiler Installation - 15 Park Street','part_paid',DATE_ADD(CURDATE(),INTERVAL 16 DAY), 1958.33,391.67,2350.00,1175.00,1175.00,'50% deposit paid on acceptance. Balance on completion.','Payment due within 30 days', 'Sort: 20-00-00 / Acc: 12345678', DATE_SUB(NOW(),INTERVAL 12 DAY), NULL, 1, DATE_SUB(NOW(),INTERVAL 12 DAY), NOW()), -- Overdue (5, 1, 2, NULL, 3, 'INV-0005', 'Annual Service Contract - Nolan Group', 'overdue', DATE_SUB(CURDATE(),INTERVAL 5 DAY), 2083.33,416.67,2500.00,0.00, 2500.00,NULL, 'Payment due within 30 days', 'Sort: 20-00-00 / Acc: 12345678', DATE_SUB(NOW(),INTERVAL 35 DAY), NULL, 5, DATE_SUB(NOW(),INTERVAL 35 DAY), NOW()), -- Draft (6, 1, 5, NULL, NULL,'INV-0006', 'Boiler Repair - 3 Redland Grove', 'draft', DATE_ADD(CURDATE(),INTERVAL 30 DAY), 0.00, 0.00, 0.00, 0.00, 0.00, NULL, 'Payment due within 30 days', 'Sort: 20-00-00 / Acc: 12345678', NULL, NULL, 2, DATE_SUB(NOW(),INTERVAL 1 DAY), NOW()); -- Invoice items INSERT INTO invoice_items (invoice_id, description, qty, unit_price, discount, tax_rate, line_total, sort_order) VALUES (1, 'Annual boiler service — Vaillant combi', 1.00, 78.75, 0, 20, 78.75, 0), (2, 'Emergency pipe repair — labour', 2.50, 45.00, 0, 20, 112.50, 0), (2, 'Materials — pipe, fittings, lagging', 1.00, 237.50, 0, 20, 237.50, 1), (3, 'Gas safety certificate and inspection', 1.00, 70.83, 0, 20, 70.83, 0), (4, 'Worcester Bosch 30i Combi Boiler', 1.00, 950.00, 0, 20, 950.00, 0), (4, 'Labour — removal and installation', 8.00, 75.00, 0, 20, 600.00, 1), (4, 'Flue kit and fittings', 1.00, 125.00, 0, 20, 125.00, 2), (4, 'System flush and inhibitor', 1.00, 150.00, 0, 20, 150.00, 3), (4, 'Magnetic filter installation', 1.00, 133.33, 0, 20, 133.33, 4), (5, 'Annual service contract — 3 properties', 3.00, 350.00, 0, 20, 1050.00, 0), (5, 'Priority call-out allowance', 1.00, 583.33, 0, 20, 583.33, 1), (5, 'Gas safety certificates', 3.00, 150.00, 0, 20, 450.00, 2); -- ── Payments ─────────────────────────────────────────────────────── INSERT INTO payments (company_id, invoice_id, amount, method, reference, paid_at, recorded_by, notes, created_at) VALUES (1, 1, 94.50, 'bank_transfer', 'BACS-001', DATE_SUB(NOW(),INTERVAL 30 DAY), 5, NULL, NOW()), (1, 2, 420.00, 'bank_transfer', 'BACS-002', DATE_SUB(NOW(),INTERVAL 10 DAY), 5, NULL, NOW()), (1, 3, 85.00, 'card', 'CARD-001', DATE_SUB(NOW(),INTERVAL 5 DAY), 4, 'Paid by card on site', NOW()), (1, 4, 1175.00, 'bank_transfer', 'BACS-DEPOSIT',DATE_SUB(NOW(),INTERVAL 10 DAY),5, '50% deposit on acceptance', NOW()); -- ── Notes ────────────────────────────────────────────────────────── INSERT INTO notes (company_id, entity_type, entity_id, text, is_internal, created_by, created_at) VALUES (1, 'job', 1, 'Service completed. Boiler pressure was slightly low at 0.8 bar — topped up to 1.2 bar. All checks passed. Certificate left with customer.', 0, 3, DATE_SUB(NOW(),INTERVAL 44 DAY)), (1, 'job', 1, 'Customer asked about boiler replacement — mentioned it is 12 years old. Sent quote for replacement job.', 1, 3, DATE_SUB(NOW(),INTERVAL 44 DAY)), (1, 'job', 2, 'Pipe had frozen and burst at the elbow joint in the utility room. Repaired and lagged. Advised customer to keep heating on during cold spells.', 0, 3, DATE_SUB(NOW(),INTERVAL 29 DAY)), (1, 'job', 4, 'Full installation complete. Customer very happy. Boiler commissioned and controls demonstrated. All paperwork left with tenant.', 0, 3, DATE_SUB(NOW(),INTERVAL 11 DAY)), (1, 'customer', 2, 'James called — chasing the annual service contract invoice. Stated they will pay end of next week. Follow up required.', 1, 2, DATE_SUB(NOW(),INTERVAL 3 DAY)), (1, 'invoice', 5, 'Second reminder sent. James Nolan confirmed payment by end of month.', 1, 5, DATE_SUB(NOW(),INTERVAL 2 DAY)), (1, 'job', 7, 'Customer called at 10:45 — complete loss of hot water since this morning. James Hartley dispatched immediately.', 1, 2, DATE_SUB(NOW(),INTERVAL 0 DAY)); -- ── Visits ───────────────────────────────────────────────────────── INSERT INTO visits (company_id, job_id, title, status, scheduled_start, scheduled_end, created_by, created_at, updated_at) VALUES (1, 5, 'Boiler repair - initial visit', 'scheduled', CONCAT(DATE_ADD(CURDATE(),INTERVAL 1 DAY),' 10:00:00'), CONCAT(DATE_ADD(CURDATE(),INTERVAL 1 DAY),' 11:30:00'), 2, NOW(), NOW()), (1, 6, 'Annual service', 'scheduled', CONCAT(DATE_ADD(CURDATE(),INTERVAL 3 DAY),' 13:00:00'), CONCAT(DATE_ADD(CURDATE(),INTERVAL 3 DAY),' 14:30:00'), 2, NOW(), NOW()), (1, 7, 'Emergency - no hot water', 'in_progress',CONCAT(CURDATE(),' 11:30:00'), CONCAT(CURDATE(),' 13:00:00'), 2, NOW(), NOW()), (1, 8, 'Survey visit for quote', 'scheduled', CONCAT(DATE_ADD(CURDATE(),INTERVAL 7 DAY),' 09:00:00'), CONCAT(DATE_ADD(CURDATE(),INTERVAL 7 DAY),' 10:00:00'), 1, NOW(), NOW()); INSERT INTO visit_assignees (visit_id, user_id) VALUES (1,3),(2,4),(3,3),(4,4); -- ── Activity Log ─────────────────────────────────────────────────── INSERT INTO activity_log (company_id, type, entity_type, entity_id, entity_ref, user_id, user_name, description, created_at) VALUES (1,'customer.created','customer',1,'CUST-0001',1,'Sarah Mitchell','Customer CUST-0001 created: David Thornton',DATE_SUB(NOW(),INTERVAL 18 MONTH)), (1,'customer.created','customer',2,'CUST-0002',1,'Sarah Mitchell','Customer CUST-0002 created: Nolan Construction Ltd',DATE_SUB(NOW(),INTERVAL 14 MONTH)), (1,'job.created', 'job', 1,'JOB-0001', 1,'Sarah Mitchell','Job JOB-0001 created: Annual Boiler Service',DATE_SUB(NOW(),INTERVAL 50 DAY)), (1,'job.created', 'job', 2,'JOB-0002', 1,'Sarah Mitchell','Job JOB-0002 created: Fix Burst Pipe - Site B',DATE_SUB(NOW(),INTERVAL 32 DAY)), (1,'job.completed', 'job', 1,'JOB-0001', 3,'James Hartley', 'Job JOB-0001 completed',DATE_SUB(NOW(),INTERVAL 44 DAY)), (1,'invoice.created','invoice', 1,'INV-0001', 1,'Sarah Mitchell','Invoice INV-0001 created',DATE_SUB(NOW(),INTERVAL 44 DAY)), (1,'invoice.sent', 'invoice', 1,'INV-0001', 1,'Sarah Mitchell','Invoice INV-0001 sent',DATE_SUB(NOW(),INTERVAL 44 DAY)), (1,'payment.recorded','invoice', 1,'INV-0001', 5,'Rachel Owen', 'Payment £94.50 recorded on INV-0001',DATE_SUB(NOW(),INTERVAL 30 DAY)), (1,'job.completed', 'job', 2,'JOB-0002', 3,'James Hartley', 'Job JOB-0002 completed',DATE_SUB(NOW(),INTERVAL 29 DAY)), (1,'invoice.created','invoice', 2,'INV-0002', 1,'Sarah Mitchell','Invoice INV-0002 created',DATE_SUB(NOW(),INTERVAL 29 DAY)), (1,'payment.recorded','invoice', 2,'INV-0002', 5,'Rachel Owen', 'Payment £420.00 recorded on INV-0002',DATE_SUB(NOW(),INTERVAL 10 DAY)), (1,'quote.created', 'quote', 1,'QUO-0001', 1,'Sarah Mitchell','Quote QUO-0001 created: New Boiler Installation',DATE_SUB(NOW(),INTERVAL 25 DAY)), (1,'quote.sent', 'quote', 1,'QUO-0001', 1,'Sarah Mitchell','Quote QUO-0001 sent to customer',DATE_SUB(NOW(),INTERVAL 22 DAY)), (1,'quote.converted_invoice','quote',1,'QUO-0001',1,'Sarah Mitchell','Quote QUO-0001 converted to invoice INV-0004',DATE_SUB(NOW(),INTERVAL 12 DAY)), (1,'payment.recorded','invoice', 4,'INV-0004', 5,'Rachel Owen', 'Payment £1,175.00 deposit on INV-0004',DATE_SUB(NOW(),INTERVAL 10 DAY)), (1,'job.created', 'job', 7,'JOB-0007', 2,'Tom Bradley', 'Job JOB-0007 created: Emergency - No Hot Water',NOW()), (1,'job.checked_in', 'job', 7,'JOB-0007', 3,'James Hartley', 'Checked in to job JOB-0007',NOW()); SET foreign_key_checks = 1; -- ================================================================ -- DEMO LOGIN CREDENTIALS -- URL: http://localhost:8080 -- Admin: sarah@bristolhp.co.uk / password -- Office: tom@bristolhp.co.uk / password -- Engineer: james@bristolhp.co.uk / password -- Engineer: priya@bristolhp.co.uk / password -- Accounts: rachel@bristolhp.co.uk / password -- ================================================================ -- ================================================================ -- JOB CREDITS TABLE (pre-payments recorded against jobs) -- ================================================================ CREATE TABLE IF NOT EXISTS job_credits ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, company_id INT UNSIGNED NOT NULL, job_id INT UNSIGNED NOT NULL, invoice_id INT UNSIGNED NULL, amount DECIMAL(10,2) NOT NULL, method VARCHAR(30) NOT NULL, reference VARCHAR(100) NULL, notes VARCHAR(500) NULL, applied TINYINT(1) NOT NULL DEFAULT 0, created_by INT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), INDEX idx_company_job (company_id, job_id), INDEX idx_applied (company_id, applied), CONSTRAINT fk_jcr_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_jcr_job FOREIGN KEY (job_id) REFERENCES jobs(id), CONSTRAINT fk_jcr_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE SET NULL, CONSTRAINT fk_jcr_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ================================================================ -- ALL DONE -- Login credentials: -- sarah@bristolhp.co.uk / password (Admin) -- tom@bristolhp.co.uk / password (Office Manager) -- james@bristolhp.co.uk / password (Engineer) -- priya@bristolhp.co.uk / password (Engineer) -- rachel@bristolhp.co.uk / password (Accounts) -- ================================================================