"""Chain the per-account cloud IAM triad, and the leaver write→verify pair. Revision ID: 617 Revises: 616 Create Date: 2026-09-06 **The triad is not an optimisation — it removes a documented silent outage.** ``cmdb.scopes_sync``, ``cmdb.roles_sync`` and ``cmdb.grants_sync`` all take the SAME ``cloud_sync:{integration_id}`` advisory lock, while running as three independent crons scheduled minutes apart. When an earlier member overruns the gap the later one waits on ``acquire_timeout`` and, if that expires, does not run. ``cmdb.scopes.aws-primary`` silently did not run for **29 of 31 consecutive days** (2026-07-07 .. 2026-08-05) exactly this way: roles starts at 04:09 and takes ~15 min, scopes fires at 04:18 into a held lock. New AWS sub-accounts stayed invisible to Helix for over a month. See ``docs/ops/cloud-lifecycle-ops.md`` §5b. Chaining them makes the collision **structurally impossible**: the successor starts because the predecessor finished, so they can never contend for the lock they share. The ordering is also the real data dependency — roles are per scope, grants are per role. **What this migration deliberately does NOT chain.** * Anything on the billing path. ``cost_facts_sync`` / ``billing_daily_sync`` pull *yesterday's* vendor line items, and every chain edge here would make its successor start **earlier** than it does today (today's gaps are generous). Pulling a daily bill earlier risks reading a batch the vendor has not finished writing, which is a data-correctness change wearing the costume of a scheduling change. * ``cmdb.efficiency_compute``. Its own description says it must follow utilisation collection **and** cost facts **and** the daily bill — three predecessors. ``chain_after_job_id`` is single-parent, so the only faithful encoding is a linear chain through all three, which would drag the billing steps earlier. Left on its clock. **Three named pairs** ride along, each stated in the successor's own description and each carrying no billing: * ``leaver_revocation_reconcile`` (write, 03:25 UTC) → ``leaver_assurance`` (read-only verifier, 03:47 UTC). Its own successor ``offboarding.account_assurance_rosters`` was chained in migration 616. * ``cmdb.leaver_grant_sweep`` (01:30) → ``cmdb.leaver_iam_residue_sweep`` (02:10). * 🔴 ``software_seat.usage_signal_refresh`` → ``software_seat.idle_reclaim``, which **fixes an inverted ordering rather than merely recording one**. The collector, seeded by migration 612 on 2026-09-05, says in its own description «每日 02:40 UTC(排在 software_seat.idle_reclaim 之前)» — but 02:40 is forty minutes *after* idle_reclaim's 02:00, and idle_reclaim genuinely reads ``asset_seat_usage_signals``. The consumer has been running on day-old signals since the collector shipped, and on its first day it read an empty table. Chaining moves idle_reclaim to *after* the collector — later, never earlier. **Guarding.** The triad is 13 accounts × 2 edges, so exact per-row cron values are impractical; instead each edge applies only when the successor still carries a cron of the *shape* the seeder produced (``M 4 * * *`` for roles, ``M 5 * * *`` for grants). An operator who moved one of these to a different hour made a decision this migration knows nothing about, and the shape guard leaves it alone. Downgrade restores each converted row to a cron of the same **shape** (same hour, a stable name-derived minute). The original minute is not recoverable — the upgrade nulls it — and it does not need to be: what matters is that the fleet lands spread across the hour rather than all firing on :00, which is the thundering herd migration 418 removed. """ from __future__ import annotations import sqlalchemy as sa from alembic import op revision = "617" down_revision = "616" branch_labels = None depends_on = None #: ``(successor_prefix, predecessor_prefix, expected_cron_regexp, #: restore_hour)`` — the two edges of the per-account triad. Names are #: ``cmdb..``; the slug is matched by suffix so a #: new cloud account picks the chain up the moment its rows are seeded. TRIAD_EDGES: tuple[tuple[str, str, str, str], ...] = ( ("cmdb.roles.", "cmdb.scopes.", r"^[0-9]+ 4 \* \* \*$", "4"), ("cmdb.grants.", "cmdb.roles.", r"^[0-9]+ 5 \* \* \*$", "5"), ) #: ``(successor, predecessor, expected_cron)`` — exact, named pairs whose #: dependency is stated in the successor's own description and whose #: schedules already sit in the same window on the same day. NAMED_EDGES: tuple[tuple[str, str, str], ...] = ( # 03:25 → 03:47 UTC. The write side revokes; the read-only verifier # confirms it cleared what it claimed. Running the verifier *after* # rather than 22 minutes later is the entire point (DECISIONS: # 「告警必被回收清零」). ( "identity_sync.leaver_assurance", "identity_sync.leaver_revocation_reconcile", "47 3 * * *", ), # 01:30 → 02:10 Asia/Shanghai. The successor's own description: # 「跑在 cmdb.leaver_grant_sweep (01:30) 之后,这样常规回收链路已经有过 # 机会」. Running it before the revoke path has finished manufactures # governance todos for residue that is about to be cleaned up. ( "cmdb.leaver_iam_residue_sweep", "cmdb.leaver_grant_sweep", "10 2 * * *", ), # 🔴 This one fixes an inverted ordering, not just an implicit one. # ``software_seat.usage_signal_refresh`` (migration 612, seeded # 2026-09-05) says in its own description «每日 02:40 UTC(排在 # software_seat.idle_reclaim 之前)» — but 02:40 is 40 minutes # *after* idle_reclaim's 02:00, and idle_reclaim genuinely reads # ``asset_seat_usage_signals`` (``_evaluate_signal_vendors``). So the # consumer has been running on signals a full day stale since the # collector shipped, and on day one it read an empty table. Chaining # moves idle_reclaim to *after* the collector — later, never earlier # — which is both the fix and the encoding of the intent. ( "software_seat.idle_reclaim", "software_seat.usage_signal_refresh", "0 2 * * *", ), ) def _table_exists(bind, table: str) -> bool: return bool( bind.execute( sa.text( "SELECT 1 FROM information_schema.TABLES " "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :t LIMIT 1", ), {"t": table}, ).scalar(), ) def _column_exists(bind, table: str, column: str) -> bool: return bool( bind.execute( sa.text( "SELECT 1 FROM information_schema.COLUMNS " "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :t " "AND COLUMN_NAME = :c LIMIT 1", ), {"t": table, "c": column}, ).scalar(), ) def upgrade() -> None: bind = op.get_bind() if not _table_exists(bind, "scheduled_jobs"): return if not _column_exists(bind, "scheduled_jobs", "chain_after_job_id"): return for successor_prefix, predecessor_prefix, cron_re, _hour in TRIAD_EDGES: rows = bind.execute( sa.text( "SELECT id, name, cron_expr FROM scheduled_jobs " "WHERE name LIKE :p AND trigger_type = 'cron' " " AND chain_after_job_id IS NULL", ), {"p": f"{successor_prefix}%"}, ).fetchall() for row in rows: slug = row.name[len(successor_prefix):] if not slug: continue # Shape guard: only rows still on the seeded hour. REGEXP is # supported by MySQL 8 and TiDB alike. matches = bind.execute( sa.text("SELECT :c REGEXP :re"), {"c": row.cron_expr or "", "re": cron_re}, ).scalar() if not matches: continue parent_id = bind.execute( sa.text( "SELECT id FROM scheduled_jobs WHERE name = :n LIMIT 1", ), {"n": f"{predecessor_prefix}{slug}"}, ).scalar() if parent_id is None: # No predecessor for this account in this deployment, so # the ordering it was meant to guarantee does not exist # here either. Leaving the row on its cron is correct. continue bind.execute( sa.text( "UPDATE scheduled_jobs " "SET chain_after_job_id = :parent, " " trigger_type = 'chained', cron_expr = NULL, " " interval_seconds = NULL, updated_at = NOW() " "WHERE id = :id", ), {"parent": parent_id, "id": row.id}, ) for successor, predecessor, expected in NAMED_EDGES: parent_id = bind.execute( sa.text("SELECT id FROM scheduled_jobs WHERE name = :n LIMIT 1"), {"n": predecessor}, ).scalar() if parent_id is None: continue bind.execute( sa.text( "UPDATE scheduled_jobs " "SET chain_after_job_id = :parent, trigger_type = 'chained', " " cron_expr = NULL, interval_seconds = NULL, " " updated_at = NOW() " "WHERE name = :n AND cron_expr = :expected " " AND chain_after_job_id IS NULL", ), {"parent": parent_id, "n": successor, "expected": expected}, ) def downgrade() -> None: bind = op.get_bind() if not _table_exists(bind, "scheduled_jobs"): return if not _column_exists(bind, "scheduled_jobs", "chain_after_job_id"): return # Restore a cron of the same shape. The original minute is NOT # recoverable — the upgrade nulls it — so derive a stable one from # the row name. It will not generally equal the minute the row had # before the upgrade, and it deliberately is not the seeder's own # hash either; what matters is that the fleet lands spread across # the hour rather than all firing on :00 and re-creating the # thundering herd migration 418 removed. for successor_prefix, predecessor_prefix, _re, hour in TRIAD_EDGES: bind.execute( sa.text( "UPDATE scheduled_jobs s " "JOIN scheduled_jobs p ON p.id = s.chain_after_job_id " "SET s.chain_after_job_id = NULL, s.trigger_type = 'cron', " " s.cron_expr = CONCAT(" " CAST(CONV(SUBSTRING(MD5(s.name), 1, 8), 16, 10) % 60 " " AS CHAR), ' ', :hour, ' * * *'), " " s.updated_at = NOW() " "WHERE s.name LIKE :sp AND p.name LIKE :pp " " AND s.trigger_type = 'chained'", ), { "hour": hour, "sp": f"{successor_prefix}%", "pp": f"{predecessor_prefix}%", }, ) for successor, predecessor, expected in NAMED_EDGES: bind.execute( sa.text( "UPDATE scheduled_jobs s " "JOIN scheduled_jobs p ON p.id = s.chain_after_job_id " "SET s.chain_after_job_id = NULL, s.trigger_type = 'cron', " " s.cron_expr = :expected, s.updated_at = NOW() " "WHERE s.name = :n AND p.name = :pn " " AND s.trigger_type = 'chained'", ), {"n": successor, "pn": predecessor, "expected": expected}, )