Implement Schema, Indexes, Security, Application Access, and Migration Automation

DROP DATABASE IF EXISTS servicehub22;CREATE DATABASE servicehub22  CHARACTER SET utf8mb4  COLLATE utf8mb4_unicode_ci;USE servicehub22;CREATE TABLE tenant (  tenant_id BIGINT PRIMARY KEY,  tenant_key VARCHAR(64) NOT NULL UNIQUE,  display_name VARCHAR(200) NOT NULL,  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)) ENGINE=InnoDB;CREATE TABLE customer (  tenant_id BIGINT NOT NULL,  customer_id BIGINT NOT NULL,  external_ref VARCHAR(128) NOT NULL,  email VARCHAR(320) NULL,  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),  PRIMARY KEY (tenant_id, customer_id),  UNIQUE KEY uq_customer_external (tenant_id, external_ref),  CONSTRAINT fk_customer_tenant FOREIGN KEY (tenant_id)    REFERENCES tenant(tenant_id)) ENGINE=InnoDB;CREATE TABLE ticket (  tenant_id BIGINT NOT NULL,  ticket_id BIGINT NOT NULL,  customer_id BIGINT NOT NULL,  status ENUM('OPEN','PENDING','RESOLVED','CLOSED') NOT NULL,  priority TINYINT NOT NULL,  subject VARCHAR(300) NOT NULL,  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),  updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)    ON UPDATE CURRENT_TIMESTAMP(6),  PRIMARY KEY (tenant_id, ticket_id),  CONSTRAINT chk_ticket_priority CHECK (priority BETWEEN 1 AND 5),  CONSTRAINT fk_ticket_customer FOREIGN KEY (tenant_id, customer_id)    REFERENCES customer(tenant_id, customer_id)) ENGINE=InnoDB;CREATE TABLE ticket_comment (  tenant_id BIGINT NOT NULL,  ticket_id BIGINT NOT NULL,  comment_id BIGINT NOT NULL,  author_ref VARCHAR(128) NOT NULL,  body TEXT NOT NULL,  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),  PRIMARY KEY (tenant_id, ticket_id, comment_id),  CONSTRAINT fk_comment_ticket FOREIGN KEY (tenant_id, ticket_id)    REFERENCES ticket(tenant_id, ticket_id) ON DELETE CASCADE) ENGINE=InnoDB;CREATE TABLE request_dedup (  tenant_id BIGINT NOT NULL,  idempotency_key VARCHAR(128) NOT NULL,  operation_name VARCHAR(80) NOT NULL,  result_ticket_id BIGINT NULL,  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),  PRIMARY KEY (tenant_id, idempotency_key)) ENGINE=InnoDB;CREATE TABLE outbox_event (  event_id BIGINT AUTO_INCREMENT PRIMARY KEY,  tenant_id BIGINT NOT NULL,  aggregate_type VARCHAR(40) NOT NULL,  aggregate_id BIGINT NOT NULL,  event_type VARCHAR(80) NOT NULL,  payload LONGTEXT NOT NULL,  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),  published_at DATETIME(6) NULL) ENGINE=InnoDB;CREATE TABLE schema_migration (  version VARCHAR(32) PRIMARY KEY,  description VARCHAR(255) NOT NULL,  checksum_sha256 CHAR(64) NOT NULL,  applied_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),  applied_by VARCHAR(128) NOT NULL) ENGINE=InnoDB;

USE servicehub22;ALTER TABLE ticket  ADD KEY ix_ticket_queue (tenant_id, status, updated_at, ticket_id),  ADD KEY ix_ticket_customer (tenant_id, customer_id, created_at, ticket_id);ALTER TABLE ticket_comment  ADD KEY ix_comment_recent (tenant_id, ticket_id, created_at, comment_id);ALTER TABLE outbox_event  ADD KEY ix_outbox_unpublished (published_at, event_id);SHOW INDEX FROM ticket;SHOW INDEX FROM ticket_comment;SHOW INDEX FROM outbox_event;

USE servicehub22;INSERT INTO tenant VALUES(1,'acme','Acme Support',NOW(6)),(2,'globex','Globex Support',NOW(6));INSERT INTO customer(tenant_id,customer_id,external_ref,email) VALUES(1,1001,'ACME-C-1','alice@example.invalid'),(1,1002,'ACME-C-2','bob@example.invalid'),(2,2001,'GLOBEX-C-1','carol@example.invalid');INSERT INTO ticket(tenant_id,ticket_id,customer_id,status,priority,subject) VALUES(1,5001,1001,'OPEN',2,'Login problem'),(1,5002,1002,'PENDING',3,'Invoice question'),(2,9001,2001,'OPEN',1,'API unavailable');INSERT INTO ticket_comment VALUES(1,5001,1,'agent-7','Initial investigation',NOW(6));-- Deliberately wrong: customer 2001 belongs to tenant 2.-- This must fail with a foreign-key error.INSERT INTO ticket(tenant_id,ticket_id,customer_id,status,priority,subject)VALUES (1,5999,2001,'OPEN',2,'cross-tenant bug');

CREATE ROLE IF NOT EXISTS cap_runtime;CREATE ROLE IF NOT EXISTS cap_migrator;CREATE ROLE IF NOT EXISTS cap_observer;GRANT SELECT, INSERT, UPDATE, DELETE ON servicehub22.* TO cap_runtime;GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX, DROP,      REFERENCES, TRIGGER ON servicehub22.* TO cap_migrator;GRANT SELECT ON servicehub22.* TO cap_observer;CREATE USER IF NOT EXISTS 'cap_app'@'localhost' IDENTIFIED BY 'DISPOSABLE-app-secret';CREATE USER IF NOT EXISTS 'cap_migrate'@'localhost' IDENTIFIED BY 'DISPOSABLE-migrate-secret';CREATE USER IF NOT EXISTS 'cap_observe'@'localhost' IDENTIFIED BY 'DISPOSABLE-observe-secret';GRANT cap_runtime TO 'cap_app'@'localhost';GRANT cap_migrator TO 'cap_migrate'@'localhost';GRANT cap_observer TO 'cap_observe'@'localhost';SET DEFAULT ROLE cap_runtime FOR 'cap_app'@'localhost';SET DEFAULT ROLE cap_migrator FOR 'cap_migrate'@'localhost';SET DEFAULT ROLE cap_observer FOR 'cap_observe'@'localhost';SHOW GRANTS FOR 'cap_app'@'localhost';SHOW GRANTS FOR cap_runtime;

SHOW VARIABLES WHERE Variable_name IN ('have_ssl','require_secure_transport','character_set_server','collation_server','sql_mode','time_zone');SHOW SESSION STATUS LIKE 'Ssl_version';SHOW SESSION STATUS LIKE 'Ssl_cipher';

import mariadb from 'mariadb';import fs from 'node:fs';const pool = mariadb.createPool({  host: process.env.DB_HOST,  port: Number(process.env.DB_PORT ?? 3306),  database: 'servicehub22',  user: process.env.DB_USER,  password: process.env.DB_PASSWORD,  connectionLimit: Number(process.env.DB_POOL_SIZE ?? 10),  connectTimeout: 5000,  acquireTimeout: 5000,  charset: 'utf8mb4',  // Production TCP example: provide a CA and verify the certificate.  ssl: process.env.DB_CA_FILE ? { ca: fs.readFileSync(process.env.DB_CA_FILE) } : undefined,  resetAfterUse: true});export async function withDb(fn) {  let conn;  try {    conn = await pool.getConnection();    await conn.query("SET SESSION time_zone = '+00:00'");    await conn.query("SET SESSION sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'");    return await fn(conn);  } finally {    if (conn) conn.release();  }}

db/  migrations/    V001__capstone_schema.sql    V002__workload_indexes.sql    V003__future_additive_change.sql  checks/    verify_contract.sql  seed/    seed_small.sql  reset/    reset_capstone.sqlRules:  migration files are immutable after deployment  calculate SHA-256 before execution  insert version/checksum/applied_by only after success  fail deployment if an already-applied version has a different checksum  destructive contract changes require a compatibility window and rollback boundary

-- Compute the file hash outside MariaDB, then let the migration runner pass that exact-- 64-hex value as a bound parameter when it records the successful migration.SELECT version,description,checksum_sha256,applied_at,applied_byFROM servicehub22.schema_migrationORDER BY applied_at;

-- Server/session contractSELECT @@version AS version,       @@sql_mode AS sql_mode,       @@character_set_server AS server_charset,       @@collation_server AS server_collation,       @@time_zone AS session_time_zone;-- Required tablesSELECT table_name, engine, table_collationFROM information_schema.TABLESWHERE table_schema='servicehub22'ORDER BY table_name;-- Critical indexesSELECT table_name,index_name,       GROUP_CONCAT(column_name ORDER BY seq_in_index) AS columns_in_orderFROM information_schema.STATISTICSWHERE table_schema='servicehub22'  AND index_name IN ('ix_ticket_queue','ix_ticket_customer','ix_comment_recent','ix_outbox_unpublished')GROUP BY table_name,index_nameORDER BY table_name,index_name;-- Runtime user should not own DDL privileges.SHOW GRANTS FOR 'cap_app'@'localhost';-- Foreign-key contractSELECT table_name,constraint_name,referenced_table_nameFROM information_schema.REFERENTIAL_CONSTRAINTSWHERE constraint_schema='servicehub22'ORDER BY table_name,constraint_name;