"""Seed Slack integration + software catalog + license plan + connector binding + scheduled sync job + notification template default. Revision ID: 056 Revises: 055 Create Date: 2026-04-23 End-to-end seed so a fresh Helix instance ships with Slack ready the moment the admin supplies the bot/user tokens. What lands: * One ``integrations`` row (``slug='slack-primary'``, ``provider='slack'``, ``kind='software_provisioning'``, ``enabled=False``, secrets empty). Admin plugs in the tokens via ``scripts/seed_slack_integration.py`` (which reads ``HELIX_SLACK_BOT_TOKEN`` / ``HELIX_SLACK_USER_TOKEN`` from env) or the ``/admin/integrations`` UI. * One ``asset_software_catalog`` row ``name='Slack'`` with ``offboarding_policy='auto_revoke'`` (per user decision 2026-04-23 — leavers must be actively removed from Slack, unlike Zoom's lazy sso_cascade) and ``sso_provider='google'``. * One ``software_license_plans`` row ``name='Slack Full Member'`` with ``over_quota_policy='reject'`` (no LRU; per user decision no quota enforcement on new requests) and ``max_seats=NULL`` (no cap). * One ``software_connectors`` binding pointing the Slack catalog at the Slack integration with ``execution_mode='manual_notify'`` (the PingCAP workspace is on the Pro plan, so SCIM is not available — invites/deactivates go through IT admins by hand, with a Feishu DM alert). When the workspace upgrades, admin flips this to ``auto_api`` in ``/admin/integrations`` and no migration is needed. * One ``scheduled_jobs`` row ``slack.seat_sync`` on a daily 03:00 UTC cron (matches Zoom's off-peak window). Disabled on first boot so the admin can plug tokens in before the sync attempts to call Slack. * Platform-settings default for ``email_template.provisioning.manual_notify`` — the Feishu / email template IT admins see when a new manual task lands. Idempotent: safe to re-run. Every seed step checks for the slug / software name / job name / setting key first so existing rows (admin-created Slack 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 = "056" down_revision = "055" branch_labels = None depends_on = None _SOFTWARE_NAME = "Slack" _PLAN_NAME = "Slack Full Member" _INTEGRATION_SLUG = "slack-primary" _JOB_NAME = "slack.seat_sync" _MANUAL_NOTIFY_TEMPLATE = { "subject": "[Helix] 请手工{action} {software_name} 坐席: {actor}", "body_text": ( "Hi,\n\n" "员工 {actor} ({actor_email}) 的 {software_name} 坐席" "({plan_name}) 需要您手动{action}:\n\n" " · 操作: 在外部系统中{action}该用户的账号\n" " · 任务号: {task_id}\n" " · 完成后: 打开 {portal_url}/admin/provisioning 找到该任务," "点「完成」,坐席台账会随之关闭。\n\n" "如果该软件后续升级到可自动 provision 的档位(如 Slack 升级到 " "Business+),把绑定的 execution_mode 改为 auto_api,后续任务" "就不再需要您手动操作了。\n\n" "— Helix IT 平台" ), } 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', 'slack', :name, :slug, :desc, " " 0, 1, '{}', NULL, NULL)" ), { "id": integration_id, "name": "Slack", "slug": _INTEGRATION_SLUG, "desc": ( "Slack Workspace — Bot Token (xoxb-) + 可选 User Token " "(xoxp- admin scope)。Bot token 每个档位都能用,读成员" "列表和发 DM;User token 只在 Business+ / Enterprise Grid " "档位能用于 SCIM 自动 invite/deactivate。PingCAP 当前在 " "Pro 档,binding 必须保持 execution_mode=manual_notify。" ), }, ) # ─── Seed: software catalog row ───────────────────────── software_id = bind.execute( sa.text( "SELECT id FROM asset_software_catalog WHERE name = :n LIMIT 1" ), {"n": _SOFTWARE_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": _SOFTWARE_NAME, "remark": ( "Slack 工作区。员工申请 → lead 审批 → IT 管理员收到飞书 " "DM 手动在 pingcap.slack.com 邀请;离职时走 auto_revoke: " "系统创建 revoke 任务,Pro 档位下同样 manual_notify 给 IT 处理。" "slack.seat_sync 每天核对成员列表修正漂移。" ), }, ) # ─── Seed: license plan row ───────────────────────────── 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": ( "Slack 工作区付费成员 (full member)。max_seats 不做硬" "约束(用户决定:超额时仍然通过,通知 IT 手动处理计费)。" ), }, ) # ─── Seed: software connector binding ─────────────────── 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( { "execution_mode": "manual_notify", "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()) """ ), { # Generate UUID in Python rather than MySQL's UUID() — # TiDB doesn't implement UUID() reliably (per prior ops # experience), so passing a pre-minted string keeps this # migration portable. "id": str(uuid.uuid4()), "name": _JOB_NAME, "display_name": "Slack · 工作区成员同步", "description": ( "每天 03:00 UTC 拉 Slack 工作区的 full member 列表," "镜像到 software_seat_allocations,并把 Slack 上已不" "在的成员对应坐席标为 revoked。填好 bot_token 并启用" "集成后,把该任务 enabled 置 1 即可启用。" ), "handler_key": _JOB_NAME, }, ) # ─── Seed: manual_notify notification template default ── _seed_setting( bind, key="email_template.provisioning.manual_notify", value=_MANUAL_NOTIFY_TEMPLATE, description=( "一键通知 IT 管理员手工处理坐席任务的飞书/邮件模板。" "任何 binding 配了 execution_mode=manual_notify 的软件都会用它" "(Slack Pro 档位是首个用例)。可用变量: " "{actor} {actor_email} {action} {software_name} {plan_name} " "{task_id} {portal_url}。" ), ) def downgrade() -> None: """Conservative downgrade — drop only the scheduled job + template setting. The integration / software / plan / connector rows may have been customised by the admin, so we leave them in place. """ bind = op.get_bind() bind.execute( sa.text("DELETE FROM scheduled_jobs WHERE name = :n"), {"n": _JOB_NAME}, ) bind.execute( sa.text("DELETE FROM platform_settings WHERE `key` = :k"), {"k": "email_template.provisioning.manual_notify"}, ) def _seed_setting(bind, *, key: str, value, description: str) -> None: exists = bind.execute( sa.text("SELECT 1 FROM platform_settings WHERE `key` = :k"), {"k": key}, ).scalar() if exists: return bind.execute( sa.text( "INSERT INTO platform_settings (`key`, value, description) " "VALUES (:k, :v, :d)" ), { "k": key, "v": json.dumps(value, ensure_ascii=False), "d": description, }, )