-- URUP Estimator schema. MySQL 8.0 / MariaDB 10.6+. -- Client identity is deliberately absent. Work is identified by job_no only. CREATE TABLE IF NOT EXISTS users ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(120) NOT NULL, email VARCHAR(190) NOT NULL, password_hash VARCHAR(255) NOT NULL, role ENUM('requester','developer','production_manager','admin') NOT NULL DEFAULT 'requester', can_estimate TINYINT(1) NOT NULL DEFAULT 0, active TINYINT(1) NOT NULL DEFAULT 1, -- 1 forces the account to set its own password before it can reach anything else. -- Seeded and administrator-created accounts start at 1 by design. must_change_password TINYINT(1) NOT NULL DEFAULT 1, password_set_at DATETIME(3) NULL, created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), PRIMARY KEY (id), UNIQUE KEY uq_users_email (email), KEY ix_users_estimator (can_estimate, active) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS skills ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, code VARCHAR(24) NOT NULL, name VARCHAR(80) NOT NULL, hourly_rate DECIMAL(10,2) NOT NULL DEFAULT 0, sort_order SMALLINT NOT NULL DEFAULT 0, active TINYINT(1) NOT NULL DEFAULT 1, PRIMARY KEY (id), UNIQUE KEY uq_skills_code (code) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS rfes ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, rfe_no VARCHAR(24) NOT NULL, -- RFE-2026-0001 job_no VARCHAR(48) NOT NULL, -- the only client reference permitted title VARCHAR(200) NOT NULL, description TEXT NULL, priority ENUM('low','normal','high','urgent') NOT NULL DEFAULT 'normal', status ENUM('draft','submitted','assigned','in_progress','awaiting_clarity', 'estimated','issued','accepted','declined','cancelled') NOT NULL DEFAULT 'draft', requester_name VARCHAR(120) NOT NULL, requester_email VARCHAR(190) NOT NULL, requester_phone VARCHAR(40) NULL, requester_dept VARCHAR(120) NULL, assignee_id INT UNSIGNED NULL, created_by INT UNSIGNED NULL, requested_at DATETIME(3) NOT NULL, -- clock starts target_at DATETIME(3) NULL, -- when the estimate is needed assigned_at DATETIME(3) NULL, started_at DATETIME(3) NULL, issued_at DATETIME(3) NULL, -- clock stops clarity_paused_ms BIGINT NOT NULL DEFAULT 0, -- time waiting on the originator contingency_pct DECIMAL(5,2) NOT NULL DEFAULT 15.00, notes TEXT NULL, created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3), PRIMARY KEY (id), UNIQUE KEY uq_rfes_no (rfe_no), KEY ix_rfes_job (job_no), KEY ix_rfes_status (status, requested_at), KEY ix_rfes_assignee (assignee_id, status), CONSTRAINT fk_rfes_assignee FOREIGN KEY (assignee_id) REFERENCES users(id) ON DELETE SET NULL, CONSTRAINT fk_rfes_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS rfe_documents ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, rfe_id INT UNSIGNED NOT NULL, filename VARCHAR(255) NOT NULL, mime VARCHAR(120) NOT NULL, kind ENUM('docx','pdf','xlsx','html','md','txt','other') NOT NULL DEFAULT 'other', size_bytes INT UNSIGNED NOT NULL, sha256 CHAR(64) NOT NULL, storage_path VARCHAR(400) NOT NULL, parse_status ENUM('pending','parsed','failed','skipped') NOT NULL DEFAULT 'pending', parse_error VARCHAR(500) NULL, parsed_at DATETIME(3) NULL, extracted_text MEDIUMTEXT NULL, uploaded_by INT UNSIGNED NULL, created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), PRIMARY KEY (id), KEY ix_docs_rfe (rfe_id), CONSTRAINT fk_docs_rfe FOREIGN KEY (rfe_id) REFERENCES rfes(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS estimate_lines ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, rfe_id INT UNSIGNED NOT NULL, seq SMALLINT NOT NULL DEFAULT 0, ref VARCHAR(40) NULL, -- source reference, e.g. 3.2.1 category VARCHAR(80) NOT NULL DEFAULT 'General', component VARCHAR(200) NOT NULL, description TEXT NULL, complexity ENUM('xs','s','m','l','xl') NOT NULL DEFAULT 'm', confidence ENUM('low','medium','high') NOT NULL DEFAULT 'medium', origin ENUM('manual','rules','ai') NOT NULL DEFAULT 'manual', source_doc_id INT UNSIGNED NULL, source_locator VARCHAR(200) NULL, -- page 4, sheet Scope!A12, heading path notes TEXT NULL, is_excluded TINYINT(1) NOT NULL DEFAULT 0, created_by INT UNSIGNED NULL, updated_by INT UNSIGNED NULL, created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3), deleted_at DATETIME(3) NULL, PRIMARY KEY (id), KEY ix_lines_rfe (rfe_id, deleted_at, seq), CONSTRAINT fk_lines_rfe FOREIGN KEY (rfe_id) REFERENCES rfes(id) ON DELETE CASCADE, CONSTRAINT fk_lines_doc FOREIGN KEY (source_doc_id) REFERENCES rfe_documents(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS estimate_line_hours ( line_id INT UNSIGNED NOT NULL, skill_id INT UNSIGNED NOT NULL, hours DECIMAL(8,2) NOT NULL DEFAULT 0, PRIMARY KEY (line_id, skill_id), CONSTRAINT fk_lh_line FOREIGN KEY (line_id) REFERENCES estimate_lines(id) ON DELETE CASCADE, CONSTRAINT fk_lh_skill FOREIGN KEY (skill_id) REFERENCES skills(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS rfe_clarifications ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, rfe_id INT UNSIGNED NOT NULL, question TEXT NOT NULL, answer TEXT NULL, asked_by INT UNSIGNED NULL, asked_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), answered_at DATETIME(3) NULL, answered_by_name VARCHAR(120) NULL, blocks_clock TINYINT(1) NOT NULL DEFAULT 1, PRIMARY KEY (id), KEY ix_clar_rfe (rfe_id, answered_at), CONSTRAINT fk_clar_rfe FOREIGN KEY (rfe_id) REFERENCES rfes(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS estimate_versions ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, rfe_id INT UNSIGNED NOT NULL, version_no SMALLINT NOT NULL, total_hours DECIMAL(10,2) NOT NULL DEFAULT 0, pert_hours DECIMAL(10,2) NOT NULL DEFAULT 0, total_cost DECIMAL(12,2) NOT NULL DEFAULT 0, snapshot JSON NOT NULL, issued_by INT UNSIGNED NULL, issued_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), PRIMARY KEY (id), UNIQUE KEY uq_ver (rfe_id, version_no), CONSTRAINT fk_ver_rfe FOREIGN KEY (rfe_id) REFERENCES rfes(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS rfe_events ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, rfe_id INT UNSIGNED NOT NULL, event VARCHAR(60) NOT NULL, actor_id INT UNSIGNED NULL, actor_name VARCHAR(120) NULL, detail JSON NULL, created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), PRIMARY KEY (id), KEY ix_events_rfe (rfe_id, created_at), CONSTRAINT fk_events_rfe FOREIGN KEY (rfe_id) REFERENCES rfes(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS counters ( name VARCHAR(40) NOT NULL, value INT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;