"""google_orphan_accounts + Google Workspace catalog seed + google.seat_sync scheduled job + platform settings. Revision ID: 103 Revises: 102 Create Date: 2026-05-02 This migration lands the schema + seed for the auto-Google-account lifecycle feature (PR 2026-05-02). What goes in: * ``google_orphan_accounts`` — every Google Workspace user not matched to an :class:`IdentityPerson` lands here (shared mailboxes, service accounts, project-team aliases, etc.). ``owner_user_id`` points at the human responsible. When that human is offboarded, the RES_GOOGLE_ORPHAN_OWNER blocker forces a new owner before the leaver date can pass. * ``asset_software_catalog`` row "Google Workspace" + plan + ``software_connectors`` row with ``operation=user_suspend`` so the existing offboarding state machine naturally invokes ``GoogleWorkspaceDriver.revoke_seat`` (suspend + Drive/Calendar transfer to manager fallback) on termination day. ``offboarding_policy=auto_revoke`` is what makes the existing ``_apply_software_policy`` build a revoke task at scan time. We need a per-person ``software_seat_allocations`` row (created by ``google_account_provisioner.create_account_for``) for the scan to pick the seat up — no allocation, no blocker, no revoke. * ``scheduled_jobs`` row ``google.seat_sync`` — daily 03:30 UTC. Mirrors Google users into ``software_seat_allocations`` for the base "Google Workspace" plan AND for any other Google-bound plan whose connector binding selects ``operation=group_member``. Also refreshes ``google_orphan_accounts``. * ``platform_settings`` rows seeding: - ``google.account_provisioning`` — domains, OUs, default groups - ``google.contractor_company_codes`` — empty list (admin maintains) Both the catalog seed and the scheduled job seed are idempotent and disabled by default; the admin enables them after confirming the Google integration credentials. Doc-update rule (CLAUDE.md): PLATFORM.md + ROADMAP.md updated in the same PR. cloud-lifecycle-ops.md is NOT touched — Google Workspace is SaaS, not cloud infrastructure. """ from __future__ import annotations import json import uuid import sqlalchemy as sa from alembic import op revision = "103" down_revision = "102" branch_labels = None depends_on = None _CATALOG_NAME = "Google Workspace" _PLAN_NAME = "Google Workspace (基础账号)" _JOB_NAME = "google.seat_sync" _SETTING_PROVISIONING = "google.account_provisioning" _SETTING_CONTRACTOR_CODES = "google.contractor_company_codes" def _fk_source_col_type(bind, target_table: str, target_column: str, *, length: int = 36): """Mirror the FK target's DATA_TYPE + COLLATION so TiDB / MySQL 8 don't reject the constraint with error 3780 (see migrations 098 / 100 / 101 / 102 for the same helper).""" row = bind.execute( sa.text( "SELECT DATA_TYPE, COLLATION_NAME FROM " "information_schema.COLUMNS " "WHERE TABLE_SCHEMA = DATABASE() " " AND TABLE_NAME = :t AND COLUMN_NAME = :c" ), {"t": target_table, "c": target_column}, ).fetchone() data_type = (row[0] or "char").lower() if row else "char" collation = row[1] if row else None type_cls = sa.CHAR if data_type == "char" else sa.String return type_cls(length, collation=collation) if collation else type_cls(length) def upgrade() -> None: bind = op.get_bind() # ─── google_orphan_accounts ───────────────────────────────── persons_id = _fk_source_col_type(bind, "identity_persons", "id") op.create_table( "google_orphan_accounts", sa.Column("id", sa.String(36), primary_key=True), # Google primary email — also serves as the natural unique key # since one Google tenant only allows one user per email. sa.Column("primary_email", sa.String(255), nullable=False), # Google's stable numeric user id. Stored so a future "rename" # of the email doesn't lose the orphan record. sa.Column("google_user_id", sa.String(64), nullable=True), sa.Column("display_name", sa.String(255), nullable=True), sa.Column("org_unit_path", sa.String(255), nullable=True), sa.Column("suspended", sa.Boolean, nullable=False, server_default=sa.text("0")), sa.Column("is_admin", sa.Boolean, nullable=False, server_default=sa.text("0")), sa.Column("creation_time", sa.DateTime(timezone=True), nullable=True), sa.Column("last_login_time", sa.DateTime(timezone=True), nullable=True), # The human responsible for this shared/service account. NULL # until an admin assigns one. When the owner is offboarded, # OffboardingService._scan adds RES_GOOGLE_ORPHAN_OWNER blockers # that demand a re-assignment before the leaver flow can complete. sa.Column("owner_user_id", persons_id, nullable=True), # Free-text explanation of what this account is for ("CI runner # mailbox", "shared finance@", "old contractor handover"). # Surfaced in admin UI; nothing in code branches on this. sa.Column("purpose", sa.Text, nullable=True), sa.Column( "first_seen_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False, ), sa.Column( "last_seen_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False, ), sa.Column( "created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False, ), sa.Column( "updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False, ), # ondelete=SET NULL so an owner being hard-deleted from # identity_persons (rare — usually we soft-delete) doesn't # cascade-kill the orphan row; the row stays so the next # google.seat_sync flags it as un-owned. sa.ForeignKeyConstraint( ["owner_user_id"], ["identity_persons.id"], name="fk_google_orphan_accounts_owner", ondelete="SET NULL", ), sa.UniqueConstraint("primary_email", name="uq_google_orphan_accounts_email"), sa.Index("ix_google_orphan_accounts_owner", "owner_user_id"), ) # ─── Seed: Google Workspace catalog + plan + connector ────── # The catalog row is what the offboarding scan walks; the plan is # what holds seat allocations; the connector tells the driver how # to grant/revoke (operation=user_suspend → driver suspends the # account + transfers Drive/Calendar to successor on revoke). 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, category) " "VALUES " "(:id, :name, 'SaaS', 'active', :remark, 'google', " " 'auto_revoke', 0, 'mail')" ), { "id": software_id, "name": _CATALOG_NAME, "remark": ( "Google Workspace 基础账号(邮箱+SSO)。每个在职员工占一" "个 seat。入职流程通过 google_account_provisioner 自动" "创建账号 + 写 software_seat_allocations,离职走 " "auto_revoke + user_suspend(driver 会先把 Drive / " "Calendar 转交给 manager 兜底 successor 再 suspend)。" ), }, ) 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": ( "每个 active 员工 1 seat。max_seats 留空(由 Google " "Workspace 实际订阅决定),Helix 不做硬约束。" "google.seat_sync 每天 03:30 UTC 校准漂移。" ), }, ) # Connector seeded only if a Google integration row exists. If it # doesn't yet, admin runs migration first → adds integration in UI # → re-runs google.seat_sync (which back-fills the connector via # the seed script, OR admin creates the binding manually). Easier # than failing the migration. google_integration_id = bind.execute( sa.text( "SELECT id FROM integrations " "WHERE kind = 'software_provisioning' " " AND provider = 'google_workspace' " " AND enabled = 1 " "ORDER BY is_primary DESC, created_at ASC " "LIMIT 1" ) ).scalar() if google_integration_id is not None: 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": google_integration_id, "cfg": json.dumps( {"operation": "user_suspend"}, ensure_ascii=False, ), }, ) # ─── Seed: scheduled job (disabled by default) ────────────── 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, '30 3 * * *', 'UTC', 0, '{}', 0, 0, NOW(), NOW()) """ ), { "id": str(uuid.uuid4()), "name": _JOB_NAME, "display_name": "Google Workspace · 坐席同步 + 孤儿账号扫描", "description": ( "每天 03:30 UTC 拉 Google Workspace 全量用户:(a) 每个 " "active 用户 = 一个 Google Workspace 基础坐席,以 " "Google 为准镜像到 software_seat_allocations;(b) 任何 " "Google-rooted SoftwareLicensePlan 的 group_member " "binding,按 group 成员同步;(c) 不在 identity_persons " "里的 Google 账号入 google_orphan_accounts(共用账号 / " "服务账号 / 历史遗留),管理员需指派负责人,负责人离职" "时强阻断到换人为止。" ), "handler_key": _JOB_NAME, }, ) # ─── Seed: platform settings ───────────────────────────────── # provisioning shape — admin can edit via /admin/platform-settings # to change domains / OUs / default groups without code change. provisioning_default = { "fte": { "domain": "pingcap.com", "org_unit_path": "/Employees", }, "contractor": { "domain": "pingcap-c.com", "org_unit_path": "/Contractors", }, # Gating switch — admin must flip this to True after the # google_workspace integration is configured to actually start # creating accounts. Default False keeps freshly-deployed # instances from racing the integration setup. "auto_create_on_hire": False, # Local-part length cap (joined first.last). Beyond this we # truncate the family-name part — Google's hard cap is 64 # chars on the local part. "local_part_max_length": 30, # Ops contact CC'd on every "your account is ready" email so # IT has a paper trail without monitoring the inbox. "notify_cc_emails": [], } setting_exists = bind.execute( sa.text("SELECT 1 FROM platform_settings WHERE `key` = :k LIMIT 1"), {"k": _SETTING_PROVISIONING}, ).scalar() if not setting_exists: bind.execute( sa.text( "INSERT INTO platform_settings " "(`key`, value, description, created_at, updated_at) " "VALUES (:k, :v, :d, NOW(), NOW())" ), { "k": _SETTING_PROVISIONING, "v": json.dumps(provisioning_default, ensure_ascii=False), "d": ( "Google Workspace 自动开账号配置。fte / contractor 各自" "的邮箱后缀 + Google OU。auto_create_on_hire 为 False 时" "整条流水线静默(适合 integration 未配齐时)。" ), }, ) contractor_exists = bind.execute( sa.text("SELECT 1 FROM platform_settings WHERE `key` = :k LIMIT 1"), {"k": _SETTING_CONTRACTOR_CODES}, ).scalar() if not contractor_exists: bind.execute( sa.text( "INSERT INTO platform_settings " "(`key`, value, description, created_at, updated_at) " "VALUES (:k, :v, :d, NOW(), NOW())" ), { "k": _SETTING_CONTRACTOR_CODES, "v": json.dumps([], ensure_ascii=False), "d": ( "外包识别:命中此列表里的 SF EmpJob.company " "(identity_persons.company_code)的员工,自动开账号时" "走 contractor 域(默认 pingcap-c.com)+ " "Contractors OU。空列表 = 全部按 FTE 处理。" ), }, ) def downgrade() -> None: bind = op.get_bind() bind.execute( sa.text("DELETE FROM scheduled_jobs WHERE name = :n"), {"n": _JOB_NAME}, ) # Conservative: leave platform_settings + catalog/plan/connector # rows alone (admin may have customised them). Only drop the schema # we added in this migration. op.drop_table("google_orphan_accounts")