"""Seed Jira integration + two software catalogs (Jira Software + Jira Service Management) + their license plans + connector bindings + scheduled sync job. Revision ID: 077 Revises: 076 Create Date: 2026-04-28 (Renumbered from 076 → 077 in a follow-up commit because PR #86's ``076_cloud_scope_environment.py`` and PR #88's ``076_seed_jira_integration.py`` were both authored against ``075`` in parallel and both merged into main, leaving Alembic with two heads. Renumbering this seed to chain after the schema change is the safer ordering — the cloud-scope column doesn't touch any of the integrations / software_catalog rows this seed inserts.) End-to-end seed so a fresh Helix instance ships with Jira ready the moment the admin supplies the API token. What lands: * One ``integrations`` row (``slug='jira-primary'``, ``provider='jira'``, ``kind='software_provisioning'``, ``enabled=False``, secrets empty). Admin plugs in site_url + admin_email + api_token (+ optional org_id) via ``scripts/seed_jira_integration.py`` (which reads ``HELIX_JIRA_*`` env vars) or the ``/admin/integrations`` UI. * Two ``asset_software_catalog`` rows — ``Jira Software`` and ``Jira Service Management`` — each with ``offboarding_policy='auto_revoke'`` (per user decision 2026-04-28 — leavers must be removed from product groups; account survives so issue history stays attributed) and ``sso_provider='google'`` (the Atlassian site federates against the company Google Workspace). * Two ``software_license_plans`` rows — one per product — with ``over_quota_policy='reject'`` (no LRU eviction; admin manually manages the Atlassian seat purchase) and ``max_seats=NULL``. * Two ``software_connectors`` bindings pointing each catalog at the one Jira integration. Bindings differ only by their ``binding_config.group_name`` — ``jira-software-users`` vs ``jira-servicedesk-users``. Same integration, multiple seats. * One ``scheduled_jobs`` row ``jira.seat_sync`` on a daily 03:00 UTC cron (matches Zoom + Slack's off-peak window). Disabled on first boot so the admin can plug credentials in before the first sync. Idempotent: safe to re-run. Every seed step checks for the slug / software name / job name first so existing rows (admin-created Jira entries from before this migration ran) are left untouched. """ from __future__ import annotations import json import uuid import sqlalchemy as sa from alembic import op revision = "077" down_revision = "076" branch_labels = None depends_on = None _INTEGRATION_SLUG = "jira-primary" _JOB_NAME = "jira.seat_sync" # (catalog_name, plan_name, group_name, remark) _PRODUCTS: list[tuple[str, str, str, str]] = [ ( "Jira Software", "Jira Software (full member)", "jira-software-users", "Atlassian Jira Software 工作区。员工申请 → 审批 → 后端调用 " "/rest/api/3/group/user 把账号加入 jira-software-users 组,占用一个 " "Software seat;离职走 auto_revoke,把人从该组里移出(账号保留,issue " "历史归属不变)。jira.seat_sync 每天核对组成员修正漂移。", ), ( "Jira Service Management", "Jira Service Management (full member)", "jira-servicedesk-users", "Atlassian Jira Service Management(IT 工单 / 服务台)。同 Jira " "Software 共用一个 Atlassian 站点,但 license 通过 group 区分。" "Helix 把 jira-servicedesk-users 组成员当作一个独立的坐席台账。", ), ] def upgrade() -> None: bind = op.get_bind() # ─── Seed: integration row ─────────────────────────────── integration_id = bind.execute( sa.text("SELECT id FROM integrations WHERE slug = :s"), {"s": _INTEGRATION_SLUG}, ).scalar() if integration_id is None: integration_id = str(uuid.uuid4()) bind.execute( sa.text( "INSERT INTO integrations " "(id, kind, provider, name, slug, description, " " enabled, is_primary, " " config_public, secrets_encrypted, secrets_nonce) " "VALUES " "(:id, 'software_provisioning', 'jira', :name, :slug, :desc, " " 0, 1, '{}', NULL, NULL)" ), { "id": integration_id, "name": "Jira (Atlassian)", "slug": _INTEGRATION_SLUG, "desc": ( "Atlassian Cloud Jira / Confluence。需要 site_url " "(.atlassian.net) + admin_email + api_token " "(ATCTT 或 ATATT 都可)。可选 org_id 仅在离职策略选 " "delete + 想走 Atlassian Admin API 彻底停用 Atlassian " "账号时需要。一个 integration 行可挂多个 software_" "connectors 行,每个 product (Jira Software / Jira " "Service Management / Confluence) 一个 binding。" ), }, ) # ─── Seed: each product's catalog + plan + connector ───── for catalog_name, plan_name, group_name, remark in _PRODUCTS: software_id = bind.execute( sa.text( "SELECT id FROM asset_software_catalog WHERE name = :n LIMIT 1" ), {"n": catalog_name}, ).scalar() if software_id is None: software_id = str(uuid.uuid4()) bind.execute( sa.text( "INSERT INTO asset_software_catalog " "(id, name, asset_type, status, remark, sso_provider, " " offboarding_policy, offboarding_grace_days) " "VALUES " "(:id, :name, 'SaaS', 'active', :remark, 'google', " " 'auto_revoke', 0)" ), { "id": software_id, "name": catalog_name, "remark": remark, }, ) plan_id = bind.execute( sa.text( "SELECT id FROM software_license_plans " "WHERE software_id = :sid AND name = :n LIMIT 1" ), {"sid": software_id, "n": plan_name}, ).scalar() if plan_id is None: plan_id = str(uuid.uuid4()) bind.execute( sa.text( "INSERT INTO software_license_plans " "(id, software_id, name, price, billing_cycle, " " description, status, max_seats, over_quota_policy) " "VALUES " "(:id, :sid, :name, 0, 'monthly', :desc, 'active', " " NULL, 'reject')" ), { "id": plan_id, "sid": software_id, "name": plan_name, "desc": ( f"占座方式: 加入 Atlassian group {group_name}。" "max_seats 留空(由 Atlassian 侧实际购买决定)," "Helix 不做硬约束,超额时仍允许通过。" ), }, ) connector_exists = bind.execute( sa.text( "SELECT 1 FROM software_connectors WHERE software_id = :sid LIMIT 1" ), {"sid": software_id}, ).scalar() if not connector_exists: bind.execute( sa.text( "INSERT INTO software_connectors " "(id, software_id, integration_id, binding_config, is_active) " "VALUES (:id, :sid, :iid, :cfg, 1)" ), { "id": str(uuid.uuid4()), "sid": software_id, "iid": integration_id, "cfg": json.dumps( { "group_name": group_name, "action_on_offboard": "deactivate", }, ensure_ascii=False, ), }, ) # ─── Seed: scheduled job ──────────────────────────────── job_exists = bind.execute( sa.text("SELECT 1 FROM scheduled_jobs WHERE name = :n LIMIT 1"), {"n": _JOB_NAME}, ).scalar() if not job_exists: bind.execute( sa.text( """ INSERT INTO scheduled_jobs (id, name, display_name, description, handler_key, trigger_type, interval_seconds, cron_expr, timezone, enabled, config, run_count, failure_count, created_at, updated_at) VALUES (:id, :name, :display_name, :description, :handler_key, 'cron', NULL, '0 3 * * *', 'UTC', 0, '{}', 0, 0, NOW(), NOW()) """ ), { # Pre-mint UUID — TiDB's UUID() is unreliable per prior # ops experience, so the migration controls the id. "id": str(uuid.uuid4()), "name": _JOB_NAME, "display_name": "Jira · 工作区成员同步", "description": ( "每天 03:00 UTC 拉每个 Jira product group 的成员,镜像到 " "software_seat_allocations 并把已不在组里的成员对应坐席" "标为 revoked。配好 api_token 并启用集成后,把该任务 " "enabled 置 1 即可生效。" ), "handler_key": _JOB_NAME, }, ) def downgrade() -> None: """Conservative downgrade — drop only the scheduled job. The integration / software / plan / connector rows may have been customised by the admin (e.g. group_name changed, more bindings added for Confluence), so we leave them in place to avoid losing operator state on a roll-back drill. """ bind = op.get_bind() bind.execute( sa.text("DELETE FROM scheduled_jobs WHERE name = :n"), {"n": _JOB_NAME}, )