"""An MDM enrolment belongs to the device, not to the person carrying it. Jamf came into Helix through the seat-reconciliation door, because that was the surface that already existed and "seat == device" balanced the arithmetic. The arithmetic was right and the model was wrong. A software seat is held by a person and dies with their employment; a managed Mac is company hardware that gets returned, re-imaged and handed to the next hire while never once leaving the tenant. Modelling one as the other made every departure emit a "please revoke the Jamf seat" todo whose correct resolution was always to do nothing — and the Jamf administrator said so plainly: the only devices he removes are the ones the company no longer owns. So this migration moves the relationship onto the device. ``asset_mdm_devices`` is the ledger: one row per enrolled device, keyed by the tenant's own identifiers, with the holder as a derived attribute that the nightly sync recomputes. Every resolution column is a plain UUID with no foreign key — TiDB locks the parent row on each child INSERT, and a nightly batch that rewrites the whole fleet would queue ~50 inserts behind a lock on one integration row, which is the 03:00 cron-collision shape this platform has already paid for once. ``jamf.seat_sync`` is retired rather than renamed. The scheduled-job model says a handler key is the link to a job's run history, so the new device sync is minted as its own row and inherits the old one's schedule and enabled state; the old row is disabled and relabelled so the history stays readable and the worker's boot guard (which only inspects ENABLED rows) stays satisfied. Migrations run before containers restart, so the handler can disappear from the code in the same deploy. The data cleanup is part of the schema change, not a follow-up. Leaving the imported Jamf seat allocations behind would leave the old behaviour running off stale rows: the offboarding scanner reads allocations, not sync jobs, so a departure tomorrow would still raise the seat blocker this change exists to remove. The three statements below close the allocations, release the blockers they already produced, and cancel the manual-notify tasks those blockers created — each scoped to software that is actually bound to an MDM integration, so nothing else moves. Revision ID: 558 Revises: 557 """ from __future__ import annotations import uuid import sqlalchemy as sa from alembic import op from app.core.migration_helpers import id_type_for, table_kw_for revision = "558" down_revision = "557" branch_labels = None depends_on = None DEVICES = "asset_mdm_devices" OLD_JOB = "jamf.seat_sync" NEW_JOB = "jamf.device_sync" NOTIFY_JOB = "mdm.cleanup_notify" _NEW_JOB_DESC = ( "每晚拉取 Jamf 已纳管的 Apple 设备,写入设备台账:按序列号关联资产、" "解析当前使用人(Jamf 的 User-and-Location 为空时回落到资产台账)、" "刷新纳管状态。本次没再出现的设备判定为已从 Jamf 移除 —— 这是" "「管理员确实清理了」的唯一硬证据,待清理待办据此自动结案。" "同时把设备数按在职/离职残留/外部/无归属四桶喂给统一对账" "(席位=设备,用于合同用量),但不再建立人↔席位分配 —— 设备的纳管" "关系属于设备,不随离职回收。" "本任务也会补开清理待办:资产已处置、设备却仍在纳管中而没有待办的," "在这里被发现并开单 —— 覆盖上线前的历史处置,以及处置钩子没能写成功" "的那些。发卡与催办由「MDM · 设备清理待办触达」负责,本任务不发通知。" ) _OLD_JOB_RETIRED_LABEL = "(已停用)Jamf · 设备席位同步 → 见 Jamf · 设备同步" def upgrade() -> None: conn = op.get_bind() # ── 1. the device ledger ────────────────────────────────────── if not sa.inspect(conn).has_table(DEVICES): op.create_table( DEVICES, sa.Column("id", id_type_for("identity_persons"), primary_key=True), # Which MDM tenant. No FK — see the module docstring. sa.Column( "integration_id", id_type_for("identity_persons"), nullable=False, ), sa.Column("provider", sa.String(32), nullable=False), # computer | mobile — Jamf numbers the two inventories # separately, so the type is part of the natural key. sa.Column("device_type", sa.String(16), nullable=False), sa.Column("external_id", sa.String(64), nullable=False), # Jamf ``managementId``; MDM commands key off this, not the # inventory id. sa.Column("management_id", sa.String(64), nullable=True), sa.Column("serial_number", sa.String(128), nullable=True), # Lowercased serial, stored rather than computed so the # disposal lookup is a plain indexed equality. sa.Column("serial_key", sa.String(128), nullable=True), sa.Column("device_name", sa.String(255), nullable=True), sa.Column("model", sa.String(255), nullable=True), sa.Column("os_version", sa.String(64), nullable=True), sa.Column( "managed", sa.Boolean(), nullable=False, server_default="1", ), sa.Column("mdm_assigned_email", sa.String(255), nullable=True), sa.Column("mdm_assigned_name", sa.String(255), nullable=True), sa.Column( "asset_id", id_type_for("identity_persons"), nullable=True, ), sa.Column( "assigned_person_id", id_type_for("identity_persons"), nullable=True, ), # mdm | asset — whether Jamf could attribute the device # itself or Helix had to work it out from the asset ledger. sa.Column("owner_source", sa.String(16), nullable=True), # matched | leaver_residual | external | unowned sa.Column( "match_category", sa.String(24), nullable=False, server_default="unowned", ), # enrolled | pending_cleanup | gone sa.Column( "enrollment_state", sa.String(24), nullable=False, server_default="enrolled", ), sa.Column( "cleanup_task_id", id_type_for("identity_persons"), nullable=True, ), sa.Column( "cleanup_requested_at", sa.DateTime(timezone=True), nullable=True, ), sa.Column("cleanup_reason", sa.String(64), nullable=True), sa.Column( "cleanup_confirmed_at", sa.DateTime(timezone=True), nullable=True, ), # sync | admin — the vendor confirming the removal and a # human claiming it are different facts. sa.Column("cleanup_confirmed_via", sa.String(16), nullable=True), # When the roster last overturned a human's "I removed it". # Recorded rather than inferred: the contradiction clears # ``cleanup_confirmed_at``, so anything derived from that # field is true exactly when this is not. sa.Column( "cleanup_contradicted_at", sa.DateTime(timezone=True), nullable=True, ), sa.Column( "cleanup_last_reminded_at", sa.DateTime(timezone=True), nullable=True, ), sa.Column( "first_seen_at", sa.DateTime(timezone=True), nullable=True, ), sa.Column( "last_seen_at", sa.DateTime(timezone=True), nullable=True, ), sa.Column( "last_contact_at", sa.DateTime(timezone=True), nullable=True, ), sa.Column("gone_at", sa.DateTime(timezone=True), nullable=True), sa.Column( "created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("CURRENT_TIMESTAMP"), ), sa.Column( "updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("CURRENT_TIMESTAMP"), onupdate=sa.text("CURRENT_TIMESTAMP"), ), **table_kw_for("identity_persons"), ) op.create_index( "uq_asset_mdm_devices_external", DEVICES, ["integration_id", "device_type", "external_id"], unique=True, ) # Deliberately non-unique: a tenant can carry two records for one # serial after a re-enrolment, and the ledger must show both # rather than silently pick one. op.create_index( "ix_asset_mdm_devices_serial", DEVICES, ["serial_key"], ) op.create_index("ix_asset_mdm_devices_asset", DEVICES, ["asset_id"]) op.create_index( "ix_asset_mdm_devices_person", DEVICES, ["assigned_person_id"], ) op.create_index( "ix_asset_mdm_devices_state", DEVICES, ["enrollment_state", "match_category"], ) # ── 2. retire jamf.seat_sync, mint jamf.device_sync ─────────── # Inherit the retired job's schedule and enabled flag: prod enabled # this sync on 2026-06-26 and the device ledger needs it running from # the first night, but a tenant that never turned it on must not have # it switched on by a deploy. old = conn.execute( sa.text( "SELECT enabled, cron_expr, timezone FROM scheduled_jobs " "WHERE handler_key = :k", ), {"k": OLD_JOB}, ).first() enabled = int(old.enabled) if old is not None else 0 cron = (old.cron_expr if old is not None else None) or "0 3 * * *" tz = (old.timezone if old is not None else None) or "UTC" conn.execute( sa.text( "INSERT INTO scheduled_jobs " "(id, name, display_name, description, handler_key, " " trigger_type, cron_expr, timezone, enabled, config, " " run_count, failure_count, created_at, updated_at) " "SELECT :id, :name, :display, :desc, :handler, 'cron', " ":cron, :tz, :enabled, '{}', 0, 0, NOW(), NOW() " "FROM DUAL WHERE NOT EXISTS " "(SELECT 1 FROM scheduled_jobs WHERE handler_key = :handler)", ), { "id": str(uuid.uuid4()), "name": NEW_JOB, "display": "Jamf · 设备同步", "desc": _NEW_JOB_DESC, "handler": NEW_JOB, "cron": cron, "tz": tz, "enabled": enabled, }, ) # Disabled, not deleted: ``scheduled_job_runs`` hangs off this row and # the run history is the record of what the old model actually did. conn.execute( sa.text( "UPDATE scheduled_jobs SET enabled = 0, display_name = :label, " "description = :desc, updated_at = NOW() " "WHERE handler_key = :k", ), { "k": OLD_JOB, "label": _OLD_JOB_RETIRED_LABEL, "desc": ( "已停用。Jamf 的纳管关系改为挂在设备上(asset_mdm_devices)," "由「Jamf · 设备同步」接管;本行仅保留历史运行记录。" ), }, ) # ── 2b. the cleanup-todo chaser ─────────────────────────────── # Enabled unconditionally, unlike the sync: it costs nothing when no # cleanup is open, and a tenant that later enables the sync must not # discover that its disposal cards were never being sent. # # Working hours in Asia/Shanghai, on the half hour. A cleanup card is # a two-minute console task with no deadline; waking an administrator # at 03:00 to tell him a sold laptop is still enrolled is how a # useful notification becomes one people filter. conn.execute( sa.text( "INSERT INTO scheduled_jobs " "(id, name, display_name, description, handler_key, " " trigger_type, cron_expr, timezone, enabled, config, " " run_count, failure_count, created_at, updated_at) " "SELECT :id, :name, :display, :desc, :handler, 'cron', " ":cron, 'Asia/Shanghai', 1, '{}', 0, 0, NOW(), NOW() " "FROM DUAL WHERE NOT EXISTS " "(SELECT 1 FROM scheduled_jobs WHERE handler_key = :handler)", ), { "id": str(uuid.uuid4()), "name": NOTIFY_JOB, "display": "MDM · 设备清理待办触达", "desc": ( "把「资产已处置但设备仍在 MDM 中」的清理待办发给 MDM 软件目录" "负责人(缺失或已离职则扇出给管理员):新待办首次发卡,超过 7 天" "未处理每周催办,超过 21 天同时抄送全部管理员。设备从 MDM 消失" "即自动结案。每次最多发 5 张、最久未处理的优先,压下的条数写进" "运行摘要 —— 一次办公室撤点处置三十台不该变成一分钟三十张卡;" "上限由任务配置 notify_burst_cap 调整。" ), "handler": NOTIFY_JOB, "cron": "30 9-18 * * 1-5", }, ) # ── 3. unwind the person-keyed Jamf seats ───────────────────── # Scope: software bound by an ACTIVE connector to an MDM-kind # integration. Provider-agnostic on purpose — the defect is "seat == # device was modelled as seat == person", which is true of any MDM, # not of Jamf specifically. mdm_software = sa.text( "SELECT DISTINCT sc.software_id FROM software_connectors sc " "JOIN integrations i ON i.id = sc.integration_id " "WHERE sc.is_active = 1 AND i.kind = 'mdm'" ) software_ids = [r[0] for r in conn.execute(mdm_software).all()] if software_ids: params: dict[str, str] = { f"s{i}": sid for i, sid in enumerate(software_ids) } placeholders = ", ".join(f":{k}" for k in params) # Every explanatory string is bound, never inlined. ``sa.text`` # reads ``:name`` as a bind parameter wherever it appears — # including inside a quoted literal — so a sentence containing a # colon ("改挂设备:离职不再…") makes the statement demand a # parameter named after the following characters and the whole # migration dies on execute. Binding sidesteps the parser # entirely and is what a literal should have been anyway. params["alloc_reason"] = ( "MDM 纳管关系改挂设备(迁移 558):席位=设备,不再按人分配" ) params["blocker_note"] = ( "[迁移 558] MDM 设备不随离职回收,纳管关系已改挂设备" ) params["task_note"] = ( "[迁移 558] MDM 纳管关系改挂设备:离职不再要求清理 MDM," "仅设备处置时提醒" ) # 3a. Close the imported allocations. ``released`` rather than # ``revoked``: nothing was taken away from anyone — the row was a # modelling error and the device stays exactly where it was. conn.execute( sa.text( "UPDATE software_seat_allocations sa " "JOIN software_license_plans p ON p.id = sa.plan_id " "SET sa.status = 'revoked', sa.revoked_at = NOW(), " " sa.drift_observed_at = NULL, " " sa.reason = :alloc_reason, " " sa.updated_at = NOW() " f"WHERE p.software_id IN ({placeholders}) " " AND sa.status IN ('active', 'pending')", ), params, ) # 3b. Release the offboarding blockers those allocations raised. # ``skipped`` is the existing "this resource needs no action" # resolution; the note says why so the process history does not # read as an unexplained waive. Processes left holding zero # pending blockers are flipped BLOCKED → READY by the existing # ``offboarding.reconcile_processes`` cron on its next 5-minute # tick — the state machine stays in the service layer. conn.execute( sa.text( "UPDATE offboarding_blockers " "SET resolution = 'skipped', " " resolution_ref = 'migration-558-mdm-device-model', " " notes = CONCAT(COALESCE(notes, ''), :blocker_note), " " updated_at = NOW() " f"WHERE software_id IN ({placeholders}) " " AND resource_type = 'software_seat' " " AND resolution = 'pending'", ), params, ) # 3c. Cancel the manual-notify todos already sitting in the # admin's queue. ``notified`` is included deliberately: those are # precisely the cards the Jamf administrator was told to action # and correctly ignored. conn.execute( sa.text( "UPDATE provisioning_tasks " "SET status = 'cancelled', completed_at = NOW(), " " error_message = :task_note, " " updated_at = NOW() " f"WHERE software_id IN ({placeholders}) " " AND target_type = 'seat' " " AND status IN ('pending', 'in_progress', 'notified')", ), params, ) def downgrade() -> None: conn = op.get_bind() for key in (NEW_JOB, NOTIFY_JOB): conn.execute( sa.text("DELETE FROM scheduled_jobs WHERE handler_key = :k"), {"k": key}, ) conn.execute( sa.text( "UPDATE scheduled_jobs SET display_name = :label, enabled = :en, " "updated_at = NOW() WHERE handler_key = :k", ), { "k": OLD_JOB, "label": "Jamf · 设备席位同步", # Left disabled on the way down: the seat rows this migration # closed are not restored (they were wrong), so re-arming the # job would re-import them on its next tick. "en": 0, }, ) if sa.inspect(conn).has_table(DEVICES): op.drop_table(DEVICES)