breadcrumb-the-shire/db/updates/2026-03-03-status-taxonomy-hard-cut.sql

-- Hard-cut: central status/outcome taxonomy with deterministic cleanup and CHECK constraints.

-- Preflight: count invalid values per in-scope column before cleanup.
SELECT 'system_audit_log.outcome' AS field_name, COUNT(*) AS invalid_count
FROM system_audit_log
WHERE outcome IS NULL OR outcome NOT IN ('success', 'failed', 'denied');

SELECT 'system_audit_log.channel' AS field_name, COUNT(*) AS invalid_count
FROM system_audit_log
WHERE channel IS NULL OR channel NOT IN ('web', 'api', 'scheduler', 'cli');

SELECT 'user_lifecycle_audit_log.action' AS field_name, COUNT(*) AS invalid_count
FROM user_lifecycle_audit_log
WHERE action IS NULL OR action NOT IN ('deactivate', 'delete', 'restore');

SELECT 'user_lifecycle_audit_log.trigger_type' AS field_name, COUNT(*) AS invalid_count
FROM user_lifecycle_audit_log
WHERE trigger_type IS NULL OR trigger_type NOT IN ('manual', 'cron', 'system');

SELECT 'user_lifecycle_audit_log.status' AS field_name, COUNT(*) AS invalid_count
FROM user_lifecycle_audit_log
WHERE status IS NULL OR status NOT IN ('success', 'skipped', 'failed');

SELECT 'import_audit_runs.status' AS field_name, COUNT(*) AS invalid_count
FROM import_audit_runs
WHERE status IS NULL OR status NOT IN ('running', 'success', 'partial', 'failed');

SELECT 'scheduled_jobs.last_run_status' AS field_name, COUNT(*) AS invalid_count
FROM scheduled_jobs
WHERE last_run_status IS NOT NULL AND last_run_status NOT IN ('running', 'success', 'failed', 'skipped');

SELECT 'scheduled_job_runs.status' AS field_name, COUNT(*) AS invalid_count
FROM scheduled_job_runs
WHERE status IS NULL OR status NOT IN ('success', 'failed', 'skipped');

SELECT 'scheduled_job_runs.trigger_type' AS field_name, COUNT(*) AS invalid_count
FROM scheduled_job_runs
WHERE trigger_type IS NULL OR trigger_type NOT IN ('scheduler', 'manual');

SELECT 'mail_log.status' AS field_name, COUNT(*) AS invalid_count
FROM mail_log
WHERE status IS NULL OR status NOT IN ('queued', 'sent', 'failed');

SELECT 'tenants.status' AS field_name, COUNT(*) AS invalid_count
FROM tenants
WHERE status IS NULL OR status NOT IN ('active', 'inactive');

SELECT 'scheduler_runtime_status.last_result' AS field_name, COUNT(*) AS invalid_count
FROM scheduler_runtime_status
WHERE last_result IS NOT NULL AND last_result NOT IN ('ok', 'lock_not_acquired', 'unexpected_error');

-- Deterministic normalization before constraints.
UPDATE system_audit_log
SET outcome = 'success'
WHERE outcome IS NULL OR outcome NOT IN ('success', 'failed', 'denied');

UPDATE system_audit_log
SET channel = 'web'
WHERE channel IS NULL OR channel NOT IN ('web', 'api', 'scheduler', 'cli');

UPDATE user_lifecycle_audit_log
SET action = 'deactivate'
WHERE action IS NULL OR action NOT IN ('deactivate', 'delete', 'restore');

UPDATE user_lifecycle_audit_log
SET trigger_type = 'system'
WHERE trigger_type IS NULL OR trigger_type NOT IN ('manual', 'cron', 'system');

UPDATE user_lifecycle_audit_log
SET status = 'failed'
WHERE status IS NULL OR status NOT IN ('success', 'skipped', 'failed');

UPDATE import_audit_runs
SET status = 'failed'
WHERE status IS NULL OR status NOT IN ('running', 'success', 'partial', 'failed');

UPDATE scheduled_jobs
SET last_run_status = NULL
WHERE last_run_status IS NOT NULL AND last_run_status NOT IN ('running', 'success', 'failed', 'skipped');

UPDATE scheduled_job_runs
SET status = 'failed'
WHERE status IS NULL OR status NOT IN ('success', 'failed', 'skipped');

UPDATE scheduled_job_runs
SET trigger_type = 'scheduler'
WHERE trigger_type IS NULL OR trigger_type NOT IN ('scheduler', 'manual');

UPDATE mail_log
SET status = 'queued'
WHERE status IS NULL OR status NOT IN ('queued', 'sent', 'failed');

UPDATE tenants
SET status = 'active'
WHERE status IS NULL OR status NOT IN ('active', 'inactive');

UPDATE scheduler_runtime_status
SET last_result = NULL
WHERE last_result IS NOT NULL AND last_result NOT IN ('ok', 'lock_not_acquired', 'unexpected_error');

-- Add CHECK constraints once (idempotent via information_schema guard).
SET @schema_name := DATABASE();

SET @sql := IF(
    (SELECT COUNT(*) FROM information_schema.table_constraints
     WHERE constraint_schema = @schema_name
       AND table_name = 'system_audit_log'
       AND constraint_name = 'chk_system_audit_log_outcome') = 0,
    'ALTER TABLE system_audit_log ADD CONSTRAINT chk_system_audit_log_outcome CHECK (outcome IN (''success'', ''failed'', ''denied''))',
    'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    (SELECT COUNT(*) FROM information_schema.table_constraints
     WHERE constraint_schema = @schema_name
       AND table_name = 'system_audit_log'
       AND constraint_name = 'chk_system_audit_log_channel') = 0,
    'ALTER TABLE system_audit_log ADD CONSTRAINT chk_system_audit_log_channel CHECK (channel IN (''web'', ''api'', ''scheduler'', ''cli''))',
    'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    (SELECT COUNT(*) FROM information_schema.table_constraints
     WHERE constraint_schema = @schema_name
       AND table_name = 'user_lifecycle_audit_log'
       AND constraint_name = 'chk_user_lifecycle_action') = 0,
    'ALTER TABLE user_lifecycle_audit_log ADD CONSTRAINT chk_user_lifecycle_action CHECK (action IN (''deactivate'', ''delete'', ''restore''))',
    'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    (SELECT COUNT(*) FROM information_schema.table_constraints
     WHERE constraint_schema = @schema_name
       AND table_name = 'user_lifecycle_audit_log'
       AND constraint_name = 'chk_user_lifecycle_trigger_type') = 0,
    'ALTER TABLE user_lifecycle_audit_log ADD CONSTRAINT chk_user_lifecycle_trigger_type CHECK (trigger_type IN (''manual'', ''cron'', ''system''))',
    'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    (SELECT COUNT(*) FROM information_schema.table_constraints
     WHERE constraint_schema = @schema_name
       AND table_name = 'user_lifecycle_audit_log'
       AND constraint_name = 'chk_user_lifecycle_status') = 0,
    'ALTER TABLE user_lifecycle_audit_log ADD CONSTRAINT chk_user_lifecycle_status CHECK (status IN (''success'', ''skipped'', ''failed''))',
    'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    (SELECT COUNT(*) FROM information_schema.table_constraints
     WHERE constraint_schema = @schema_name
       AND table_name = 'import_audit_runs'
       AND constraint_name = 'chk_import_audit_runs_status') = 0,
    'ALTER TABLE import_audit_runs ADD CONSTRAINT chk_import_audit_runs_status CHECK (status IN (''running'', ''success'', ''partial'', ''failed''))',
    'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    (SELECT COUNT(*) FROM information_schema.table_constraints
     WHERE constraint_schema = @schema_name
       AND table_name = 'scheduled_jobs'
       AND constraint_name = 'chk_scheduled_jobs_last_run_status') = 0,
    'ALTER TABLE scheduled_jobs ADD CONSTRAINT chk_scheduled_jobs_last_run_status CHECK (last_run_status IS NULL OR last_run_status IN (''running'', ''success'', ''failed'', ''skipped''))',
    'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    (SELECT COUNT(*) FROM information_schema.table_constraints
     WHERE constraint_schema = @schema_name
       AND table_name = 'scheduled_job_runs'
       AND constraint_name = 'chk_scheduled_job_runs_status') = 0,
    'ALTER TABLE scheduled_job_runs ADD CONSTRAINT chk_scheduled_job_runs_status CHECK (status IN (''success'', ''failed'', ''skipped''))',
    'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    (SELECT COUNT(*) FROM information_schema.table_constraints
     WHERE constraint_schema = @schema_name
       AND table_name = 'scheduled_job_runs'
       AND constraint_name = 'chk_scheduled_job_runs_trigger_type') = 0,
    'ALTER TABLE scheduled_job_runs ADD CONSTRAINT chk_scheduled_job_runs_trigger_type CHECK (trigger_type IN (''scheduler'', ''manual''))',
    'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    (SELECT COUNT(*) FROM information_schema.table_constraints
     WHERE constraint_schema = @schema_name
       AND table_name = 'mail_log'
       AND constraint_name = 'chk_mail_log_status') = 0,
    'ALTER TABLE mail_log ADD CONSTRAINT chk_mail_log_status CHECK (status IN (''queued'', ''sent'', ''failed''))',
    'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    (SELECT COUNT(*) FROM information_schema.table_constraints
     WHERE constraint_schema = @schema_name
       AND table_name = 'tenants'
       AND constraint_name = 'chk_tenants_status') = 0,
    'ALTER TABLE tenants ADD CONSTRAINT chk_tenants_status CHECK (status IN (''active'', ''inactive''))',
    'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(
    (SELECT COUNT(*) FROM information_schema.table_constraints
     WHERE constraint_schema = @schema_name
       AND table_name = 'scheduler_runtime_status'
       AND constraint_name = 'chk_scheduler_runtime_status_last_result') = 0,
    'ALTER TABLE scheduler_runtime_status ADD CONSTRAINT chk_scheduler_runtime_status_last_result CHECK (last_result IS NULL OR last_result IN (''ok'', ''lock_not_acquired'', ''unexpected_error''))',
    'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;