"""GitHub 组织成员资格:从一个飞书表单里的单选控件,变成一张台账。 「申请加入 GitHub 组织」这条飞书审批流(``AAF99056-F989-4D8C-93E4- 321CB602CBA6``,近半年 16 单)走完之后,会发生两件 Helix 完全看不见的事: 有人手工去 GitHub 点邀请,然后申请人自己去点接受。生产取证:随手取的 一张已通过的单子,从提交到终态 8 天,其中一半时间花在这两个人工办理 节点上。 看不见的代价不在入职这一侧,在离职那一侧 —— 平台今天完全不知道谁在 哪个 GitHub 组织里,所以离职流程从来没有回收过任何一个组织成员资格。 一个离开公司的人,对 5 个组织(pingcap / tikv / pingcap-inc / PingCAP-QE / tidbcloud)里私有仓库的访问权,靠的是有没有人记得手工去踢。 这次建的五张表,分别回答一个问题: * ``github_orgs`` —— 我们管着哪些组织。以前这份清单只存在于飞书表单的 选项里,加一个组织要改飞书 + 改代码 + 改前端。 * ``github_org_memberships`` —— 谁在里面。自然键是 ``(org, handle)`` 而 不是 ``(org, person)``:GitHub 侧的事实是按 handle 说的,而 handle 未必 对得上人。对账时「GitHub 上有、Helix 认不出是谁」必须能如实落一行 —— 那正是需要人看的东西。 * ``github_org_membership_events`` —— 这条成员资格经历过什么。只追加。 * ``github_org_join_requests`` —— 申请单,形状对齐 ``vpn_applications``。 **单向**指向:台账 FK 到申请单,申请单不回指 —— 互指会在 ORM 元数据 里形成不可解的环,而反向边是冗余的(台账的自然键 ``(org, handle)`` 申请单两者都有)。 * ``github_org_auto_join_rules`` —— 哪些部门的人天然就该在里面。 两个刻意的设计,写在这里以免以后被"顺手统一": 1. **状态机把「已邀请」和「已加入」分开**。GitHub 的组织成员资格是邀请 制的:调完 API 对方并没有进组,要本人点接受。合并成一个 active,等于 对外宣称一批还没进组的人已经有访问权;而离职回收又会去撤一个不存在 的成员资格。7 天过期也因此需要一个自己的状态(可重发),它与"被拒绝" (不可重发)不是一回事。 2. **``feishu_option_value`` 存在登记表里**。飞书单选控件的选项值形如 ``l9i7g355-les25siqrij-0``,既不可推导也不稳定,而 Helix 发起审批时 必须原样回填。存进数据行,是为了让"飞书那边加了个新组织"变成改一行 数据,而不是改代码再发一次版。 组织登记数据在本迁移里播种:5 个组织的 slug / 显示名 / 飞书选项值,全部 取自 2026-08-29 对生产飞书审批定义的只读读取。社区组织 (pingcap / tikv)的 ``offboarding_policy`` 同样播种为 ``remove``,与公司 自有组织一致 —— 豁免是一个需要 org owner 明确做出的决定,不是一个默认值。 Revision ID: 566 Revises: 565 """ from __future__ import annotations import sqlalchemy as sa from alembic import op from app.core.migration_helpers import id_type_for, table_kw_for revision = "566" down_revision = "565" branch_labels = None depends_on = None ORGS = "github_orgs" MEMBERSHIPS = "github_org_memberships" EVENTS = "github_org_membership_events" REQUESTS = "github_org_join_requests" RULES = "github_org_auto_join_rules" #: 5 个组织的播种数据。``feishu_option_value`` 来自生产飞书审批定义 #: ``AAF99056-...`` 的 ``widget16663393860820001`` 控件选项(2026-08-29 读取)。 #: 顺序按飞书表单里的排列,让 Helix 的下拉与员工在飞书上看惯的顺序一致。 _SEED_ORGS = ( # (slug, display_name, is_community, feishu_option_value, sort_order) ("pingcap", "PingCAP", True, "l9i7g355-les25siqrij-0", 10), ("tikv", "TiKV", True, "m2d9cr1w-ao3qpg2wcn-1", 20), ("pingcap-inc", "PingCAP Inc", False, "l9i7zgha-v6cojchbxqe-1", 30), ("PingCAP-QE", "PingCAP QE", False, "lecjg78b-opkqbn35dtm-1", 40), ("tidbcloud", "TiDB Cloud", False, "legl5z3l-yp7yya2lv4-1", 50), ) def upgrade() -> None: conn = op.get_bind() if sa.inspect(conn).has_table(ORGS): return person_id = id_type_for("identity_persons") # ── 组织登记表 ──────────────────────────────────────────────── op.create_table( ORGS, sa.Column("id", person_id, primary_key=True), # GitHub 上的组织 login。 sa.Column("slug", sa.String(39), nullable=False), # slug 的小写副本,唯一约束挂在它上面 —— 让"不区分大小写地唯一" # 这条正确性约束与建库时的 collation 解耦。 sa.Column("slug_lower", sa.String(39), nullable=False), sa.Column("display_name", sa.String(128), nullable=False), sa.Column("description", sa.Text(), nullable=True), # 是不是社区组织。展示与默认值推断用;离职行为由下一列决定 —— # "是社区组织"是事实,"离职后可以留下"是政策,两个判断。 sa.Column( "is_community", sa.Boolean(), nullable=False, server_default="0", ), # remove | exempt sa.Column( "offboarding_policy", sa.String(16), nullable=False, server_default="remove", ), sa.Column( "request_enabled", sa.Boolean(), nullable=False, server_default="1", ), sa.Column( "auto_join_enabled", sa.Boolean(), nullable=False, server_default="0", ), # member | admin。默认 member —— owner 永远不该由自动化发出。 sa.Column( "default_role", sa.String(16), nullable=False, server_default="member", ), # 飞书单选控件的选项值,见模块文档第 2 条。 sa.Column("feishu_option_value", sa.String(128), nullable=True), sa.Column( "integration_id", id_type_for("integrations"), nullable=True, ), sa.Column("sort_order", sa.Integer(), nullable=False, server_default="0"), sa.Column( "is_active", sa.Boolean(), nullable=False, server_default="1", ), sa.Column("notes", sa.Text(), nullable=True), sa.Column( "last_reconciled_at", sa.DateTime(timezone=True), nullable=True, ), sa.Column("last_reconcile_error", sa.String(512), nullable=True), sa.Column("created_by", person_id, 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"), ), sa.Column("deleted_at", sa.DateTime(timezone=True), nullable=True), sa.ForeignKeyConstraint( ["integration_id"], ["integrations.id"], ondelete="SET NULL", ), sa.ForeignKeyConstraint( ["created_by"], ["identity_persons.id"], ondelete="SET NULL", ), sa.UniqueConstraint("slug_lower", name="uq_github_orgs_slug_lower"), **table_kw_for("identity_persons"), ) op.create_index( "ix_github_orgs_active", ORGS, ["is_active", "deleted_at"], ) # ── 自动加入规则 ────────────────────────────────────────────── # # 先于 memberships 建表:后者有一个指向它的 FK。 op.create_table( RULES, sa.Column("id", person_id, primary_key=True), sa.Column("org_id", person_id, nullable=False), # 剥掉租户根后的部门路径,例如 "Engineering Group / TiDB"。 # NULL = 不限部门。子树语义按路径段比较(见 core/github_org_match)。 sa.Column("match_department_path", sa.String(512), nullable=True), sa.Column("match_job_title", sa.String(128), nullable=True), sa.Column("match_location", sa.String(64), nullable=True), sa.Column( "is_active", sa.Boolean(), nullable=False, server_default="1", ), sa.Column("notes", sa.Text(), nullable=True), sa.Column("created_by", person_id, 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"), ), sa.Column("deleted_at", sa.DateTime(timezone=True), nullable=True), sa.ForeignKeyConstraint( ["org_id"], [f"{ORGS}.id"], ondelete="CASCADE", ), sa.ForeignKeyConstraint( ["created_by"], ["identity_persons.id"], ondelete="SET NULL", ), **table_kw_for("identity_persons"), ) op.create_index( "ix_github_org_auto_join_rules_org", RULES, ["org_id", "is_active"], ) # ── 申请单 ──────────────────────────────────────────────────── # # 先于 memberships 建表 —— 后者 FK 到它,而它不回指(见模块文档)。 op.create_table( REQUESTS, sa.Column("id", person_id, primary_key=True), sa.Column("person_id", person_id, nullable=False), sa.Column("org_id", person_id, nullable=False), sa.Column("github_login", sa.String(39), nullable=False), sa.Column( "is_community_member", sa.Boolean(), nullable=False, server_default="0", ), sa.Column("community_team", sa.String(128), nullable=True), sa.Column("reason", sa.Text(), nullable=True), # pending | approved | rejected | cancelled | failed sa.Column( "status", sa.String(16), nullable=False, server_default="pending", ), sa.Column("feishu_instance_code", sa.String(64), nullable=True), sa.Column( "approval_request_id", id_type_for("approval_requests"), nullable=True, ), sa.Column("decided_at", sa.DateTime(timezone=True), nullable=True), sa.Column("executed_at", sa.DateTime(timezone=True), nullable=True), sa.Column("last_error", sa.String(512), 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"), ), sa.ForeignKeyConstraint(["person_id"], ["identity_persons.id"]), sa.ForeignKeyConstraint( ["org_id"], [f"{ORGS}.id"], ondelete="RESTRICT", ), sa.ForeignKeyConstraint( ["approval_request_id"], ["approval_requests.id"], ondelete="SET NULL", ), **table_kw_for("identity_persons"), ) op.create_index( "ix_github_org_join_requests_person", REQUESTS, ["person_id", "status"], ) op.create_index( "ix_github_org_join_requests_status", REQUESTS, ["status", "created_at"], ) op.create_index( "ix_github_org_join_requests_instance", REQUESTS, ["feishu_instance_code"], ) # ── 成员台账 ────────────────────────────────────────────────── op.create_table( MEMBERSHIPS, sa.Column("id", person_id, primary_key=True), sa.Column("org_id", person_id, nullable=False), # 可空:GitHub 上有、但认不出是谁,是待办状态而不是错误状态。 sa.Column("person_id", person_id, nullable=True), sa.Column("github_login", sa.String(39), nullable=False), sa.Column("github_login_lower", sa.String(39), nullable=False), # pending_invite | active | removed | declined | expired sa.Column( "state", sa.String(24), nullable=False, server_default="pending_invite", ), sa.Column( "role", sa.String(16), nullable=False, server_default="member", ), # approval | auto_join | import | manual | reconcile sa.Column( "source", sa.String(24), nullable=False, server_default="manual", ), sa.Column("join_request_id", person_id, nullable=True), sa.Column("auto_join_rule_id", person_id, nullable=True), sa.Column("external_invitation_id", sa.String(64), nullable=True), sa.Column("invited_at", sa.DateTime(timezone=True), nullable=True), # invited_at + 7 天。存下来让"快过期了"能进 SQL。 sa.Column( "invitation_expires_at", sa.DateTime(timezone=True), nullable=True, ), sa.Column("joined_at", sa.DateTime(timezone=True), nullable=True), sa.Column("removed_at", sa.DateTime(timezone=True), nullable=True), sa.Column("removed_reason", sa.String(255), nullable=True), sa.Column("removal_source", sa.String(24), nullable=True), sa.Column("removed_by", person_id, nullable=True), sa.Column("last_verified_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"), ), sa.ForeignKeyConstraint( ["org_id"], [f"{ORGS}.id"], ondelete="CASCADE", ), sa.ForeignKeyConstraint( ["person_id"], ["identity_persons.id"], ondelete="SET NULL", ), sa.ForeignKeyConstraint( ["join_request_id"], [f"{REQUESTS}.id"], ondelete="SET NULL", ), sa.ForeignKeyConstraint( ["auto_join_rule_id"], [f"{RULES}.id"], ondelete="SET NULL", ), sa.ForeignKeyConstraint( ["removed_by"], ["identity_persons.id"], ondelete="SET NULL", ), sa.UniqueConstraint( "org_id", "github_login_lower", name="uq_github_org_memberships_org_login", ), **table_kw_for("identity_persons"), ) op.create_index( "ix_github_org_memberships_person", MEMBERSHIPS, ["person_id", "state"], ) op.create_index( "ix_github_org_memberships_state", MEMBERSHIPS, ["state", "org_id"], ) op.create_index( "ix_github_org_memberships_invite_expiry", MEMBERSHIPS, ["state", "invitation_expires_at"], ) # ── 成员资格事件流 ──────────────────────────────────────────── op.create_table( EVENTS, sa.Column("id", person_id, primary_key=True), sa.Column("membership_id", person_id, nullable=False), sa.Column("event_type", sa.String(32), nullable=False), sa.Column("from_state", sa.String(24), nullable=True), sa.Column("to_state", sa.String(24), nullable=True), sa.Column("actor_person_id", person_id, nullable=True), # person | system | integration sa.Column( "actor_kind", sa.String(16), nullable=False, server_default="system", ), sa.Column("detail", sa.JSON(), nullable=True), sa.Column("trace_id", sa.String(64), nullable=True), sa.Column( "created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("CURRENT_TIMESTAMP"), ), sa.ForeignKeyConstraint( ["membership_id"], [f"{MEMBERSHIPS}.id"], ondelete="CASCADE", ), sa.ForeignKeyConstraint( ["actor_person_id"], ["identity_persons.id"], ondelete="SET NULL", ), **table_kw_for("identity_persons"), ) op.create_index( "ix_github_org_membership_events_membership", EVENTS, ["membership_id", "created_at"], ) # ── 播种 5 个组织 ───────────────────────────────────────────── # # 用 uuid_generate 式的字面量:迁移里不 import 域模型(模型可能在未来 # 被改名/移动,迁移必须永远可重放)。 import uuid as _uuid orgs_table = sa.table( ORGS, sa.column("id", sa.String), sa.column("slug", sa.String), sa.column("slug_lower", sa.String), sa.column("display_name", sa.String), sa.column("is_community", sa.Boolean), sa.column("offboarding_policy", sa.String), sa.column("request_enabled", sa.Boolean), sa.column("auto_join_enabled", sa.Boolean), sa.column("default_role", sa.String), sa.column("feishu_option_value", sa.String), sa.column("sort_order", sa.Integer), sa.column("is_active", sa.Boolean), ) op.bulk_insert( orgs_table, [ { "id": str(_uuid.uuid4()), "slug": slug, "slug_lower": slug.lower(), "display_name": display, "is_community": community, # 社区组织也播种成 remove:豁免必须是 org owner 明确做出的 # 决定,不是一个安静的默认值。 "offboarding_policy": "remove", "request_enabled": True, # 自动加入默认关闭。规则表是空的,但把开关也关上,意味着 # 「上线后误配一条规则」不会立刻开始给人发邀请。 "auto_join_enabled": False, "default_role": "member", "feishu_option_value": option_value, "sort_order": order, "is_active": True, } for slug, display, community, option_value, order in _SEED_ORGS ], ) def downgrade() -> None: conn = op.get_bind() insp = sa.inspect(conn) # memberships 先于 requests 落地:前者 FK 指向后者,反过来没有边 # (申请单不存 membership_id —— 见模型里的说明)。 for table in (EVENTS, MEMBERSHIPS, REQUESTS, RULES, ORGS): if insp.has_table(table): op.drop_table(table)