CREATE TABLE IF NOT EXISTS admin_bootstrap_challenges (
 id TEXT PRIMARY KEY, tenant_key TEXT NOT NULL, code_hash TEXT NOT NULL, expires_at TIMESTAMPTZ NOT NULL, attempts INTEGER NOT NULL DEFAULT 0 CHECK(attempts>=0), max_attempts INTEGER NOT NULL DEFAULT 5 CHECK(max_attempts BETWEEN 1 AND 10),
 candidate_staff_id TEXT NOT NULL DEFAULT '', candidate_union_id TEXT NOT NULL DEFAULT '', candidate_display_name TEXT NOT NULL DEFAULT '', candidate_department TEXT NOT NULL DEFAULT '',
 status TEXT NOT NULL CHECK(status IN ('pending','candidate_detected','confirmed','expired','locked')), created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW());
CREATE UNIQUE INDEX IF NOT EXISTS uq_admin_bootstrap_pending ON admin_bootstrap_challenges(tenant_key) WHERE status IN ('pending','candidate_detected');