Build the Production Schema, Index Portfolio, Security Model, and Migration Pipeline

USE servicehub_capstone;INSERT INTO sites(site_code,site_name) VALUES('NORTH','North Plant'),('HARBOR','Harbor Workshop'),('WEST','West Field');INSERT INTO assets(site_id,asset_tag,asset_type,metadata)SELECT s.site_id, CONCAT(s.site_code,'-',LPAD(n.n,4,'0')),       CASE n.n % 3 WHEN 0 THEN 'PUMP' WHEN 1 THEN 'ROBOT' ELSE 'COMPRESSOR' END,       JSON_OBJECT('criticality', CASE WHEN n.n % 10=0 THEN 'high' ELSE 'normal' END)FROM sites sJOIN (  WITH RECURSIVE seq(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM seq WHERE n<200)  SELECT n FROM seq) n;INSERT INTO work_orders(site_id,asset_id,status,priority,summary,scheduled_at,created_at)SELECT a.site_id,a.asset_id,       CASE x.n % 8 WHEN 0 THEN 'CLOSED' WHEN 1 THEN 'IN_PROGRESS' ELSE 'OPEN' END,       1 + (x.n % 5),       CONCAT('Inspection ',x.n,' for ',a.asset_tag),       TIMESTAMP('2026-08-01 08:00:00') + INTERVAL (x.n % 1440) MINUTE,       TIMESTAMP('2026-07-01 00:00:00') + INTERVAL x.n MINUTEFROM assets aJOIN (  WITH RECURSIVE seq(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM seq WHERE n<20)  SELECT n FROM seq) x;INSERT INTO work_order_events(work_order_id,event_type,actor,payload,occurred_at)SELECT work_order_id,'CREATED','seed',JSON_OBJECT('source','capstone'),created_atFROM work_orders;SELECT COUNT(*) AS assets FROM assets;SELECT COUNT(*) AS work_orders FROM work_orders;SELECT status,COUNT(*) FROM work_orders GROUP BY status ORDER BY status;

ALTER TABLE work_orders  ADD INDEX idx_wo_site_status_schedule    (site_id,status,scheduled_at,work_order_id),  ADD INDEX idx_wo_asset_created    (asset_id,created_at,work_order_id);ALTER TABLE work_order_events  ADD INDEX idx_event_work_order_time    (work_order_id,occurred_at,event_id);SHOW INDEX FROM work_orders;SHOW INDEX FROM work_order_events;EXPLAIN FORMAT=TREESELECT work_order_id,asset_id,priority,summary,scheduled_atFROM work_ordersWHERE site_id=1 AND status='OPEN'ORDER BY scheduled_at,work_order_idLIMIT 50;

CREATE INDEX idx_wo_status_only ON work_orders(status);EXPLAIN FORMAT=TREESELECT work_order_id,asset_id,priority,summary,scheduled_atFROM work_ordersWHERE site_id=1 AND status='OPEN'ORDER BY scheduled_at,work_order_idLIMIT 50;EXPLAIN ANALYZESELECT work_order_id,asset_id,priority,summary,scheduled_atFROM work_ordersWHERE site_id=1 AND status='OPEN'ORDER BY scheduled_at,work_order_idLIMIT 50;-- Remove the unjustified write/storage cost after evidence review.DROP INDEX idx_wo_status_only ON work_orders;

CREATE ROLE IF NOT EXISTS 'r_sh_app_rw', 'r_sh_app_ro', 'r_sh_operator';GRANT SELECT ON servicehub_capstone.sites TO 'r_sh_app_ro';GRANT SELECT ON servicehub_capstone.assets TO 'r_sh_app_ro';GRANT SELECT ON servicehub_capstone.work_orders TO 'r_sh_app_ro';GRANT SELECT ON servicehub_capstone.work_order_events TO 'r_sh_app_ro';GRANT SELECT,INSERT,UPDATE ON servicehub_capstone.work_orders TO 'r_sh_app_rw';GRANT SELECT,INSERT ON servicehub_capstone.work_order_events TO 'r_sh_app_rw';GRANT SELECT,INSERT,UPDATE ON servicehub_capstone.integration_requests TO 'r_sh_app_rw';GRANT PROCESS ON *.* TO 'r_sh_operator';DROP USER IF EXISTS 'sh_cap_app'@'127.0.0.1';CREATE USER 'sh_cap_app'@'127.0.0.1'  IDENTIFIED BY 'Disposable-Capstone-Only!'  REQUIRE SSL;GRANT 'r_sh_app_ro','r_sh_app_rw' TO 'sh_cap_app'@'127.0.0.1';SET DEFAULT ROLE 'r_sh_app_ro','r_sh_app_rw' TO 'sh_cap_app'@'127.0.0.1';SHOW GRANTS FOR 'sh_cap_app'@'127.0.0.1';SHOW CREATE USER 'sh_cap_app'@'127.0.0.1';SHOW VARIABLES LIKE 'have_ssl';SHOW VARIABLES LIKE 'require_secure_transport';

-- Connect as sh_cap_app over TLS, then:SELECT CURRENT_USER(),CURRENT_ROLE();SHOW SESSION STATUS LIKE 'Ssl_cipher';DROP TABLE servicehub_capstone.sites;CREATE USER 'should_fail'@'localhost' IDENTIFIED BY 'x';-- Both administrative actions should be denied.-- A permitted business read should still succeed:SELECT work_order_id,status,summaryFROM servicehub_capstone.work_ordersORDER BY work_order_id LIMIT 3;

SELECT @@session.autocommit,       @@session.transaction_isolation,       @@session.time_zone,       @@session.sql_mode,       @@character_set_connection,       @@collation_connection,       CURRENT_ROLE();SET SESSION time_zone = '+00:00';SET SESSION sql_mode = @@GLOBAL.sql_mode;ROLLBACK;SET autocommit = 1;

CREATE TABLE IF NOT EXISTS schema_migrations (  version VARCHAR(32) PRIMARY KEY,  checksum CHAR(64) NOT NULL,  owner VARCHAR(80) NOT NULL,  status ENUM('APPLIED','FAILED') NOT NULL,  applied_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)) ENGINE=InnoDB;ALTER TABLE work_orders  ADD COLUMN response_due_at DATETIME(6) NULL,  ALGORITHM=INSTANT;INSERT INTO schema_migrations(version,checksum,owner,status)VALUES ('022_002_expand_response_due',        SHA2('ALTER TABLE work_orders ADD COLUMN response_due_at DATETIME(6) NULL ALGORITHM=INSTANT',256),        'servicehub-db','APPLIED');-- Bounded backfill: run repeatedly and checkpoint progress.UPDATE work_ordersSET response_due_at = scheduled_at - INTERVAL 30 MINUTEWHERE response_due_at IS NULLORDER BY work_order_idLIMIT 500;SELECT version,status,applied_at FROM schema_migrations ORDER BY version;SELECT COUNT(*) AS remaining_backfillFROM work_orders WHERE response_due_at IS NULL;

SELECT COLUMN_NAME,IS_NULLABLE,COLUMN_TYPEFROM information_schema.COLUMNSWHERE TABLE_SCHEMA='servicehub_capstone'  AND TABLE_NAME='work_orders'  AND COLUMN_NAME IN ('work_order_id','status','scheduled_at','response_due_at')ORDER BY ORDINAL_POSITION;SELECT INDEX_NAME,GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS columns_in_orderFROM information_schema.STATISTICSWHERE TABLE_SCHEMA='servicehub_capstone'  AND TABLE_NAME='work_orders'GROUP BY INDEX_NAMEORDER BY INDEX_NAME;SHOW GRANTS FOR 'sh_cap_app'@'127.0.0.1';SHOW CREATE USER 'sh_cap_app'@'127.0.0.1';