"""identity_sync_department_state — per-(target, dept) sync projection. Revision ID: 214 Revises: 213 Create Date: 2026-05-28 Why this exists --------------- The existing ``identity_sync_state`` table is person-keyed (``(target_id, person_id)``). Department sync (Feishu Contact target — push HRIS org tree into Feishu ``/contact/v3/departments``) needs the same idempotency-by-hash + external-id tracking shape, but keyed on ``dept_code`` (= ``identity_org_nodes.org_node_source_id``) instead of ``person_id``. We do NOT bolt department state onto ``identity_sync_state`` because: * The FK semantics are different — dept rows would have a NULL ``person_id`` and a new dept_code column, which makes the (target_id, person_id) unique constraint and the per-person event hook query path more brittle (every query has to add ``WHERE person_id IS NOT NULL`` or risk picking up dept rows by accident). * The orchestrator path is different — departments need topological- order push, soft-delete-on-disappear semantics, and a separate mass-action brake threshold. A second table makes those code paths symmetric to the user path without coupling them. Schema notes ------------ * PK is ``id`` (UUID) to match the existing identity_sync tables' shape; the logical key is the unique constraint ``(target_id, dept_code)``. * ``external_id`` carries the Feishu-side identifier — for the ``feishu_department`` runner this is the auto-generated ``open_department_id`` (``od-XXX``) captured at adopt-by-name time (or POST-create time when HRIS adds a dept Feishu doesn't yet have). Helix does NOT pick the value; Feishu does. See ADR in ``docs/architecture/DECISIONS.md``. * No FK to ``identity_org_nodes`` because that table's PK is ``id`` (UUID), but the logical key consumers use is ``org_node_source_id`` (string). Storing the string here means a HRIS sync that hard-deletes-and-recreates an org node (same code, new UUID) does NOT orphan our state row. * ``last_payload_hash`` + ``last_status`` + ``retry_count`` mirror identity_sync_state exactly so the admin UI's "State" tab can render both tables with the same component. Idempotency ----------- Table creation guarded via ``information_schema.TABLES`` probe. Safe to re-run. """ from __future__ import annotations import sqlalchemy as sa from alembic import op revision = "214" down_revision = "213" branch_labels = None depends_on = None def _table_exists(bind, table: str) -> bool: return bind.execute( sa.text( "SELECT 1 FROM information_schema.TABLES " "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :t LIMIT 1" ), {"t": table}, ).scalar() is not None def _fk_source_col_type( bind, target_table: str, target_column: str, *, fallback_type: str = "char", fallback_collation: str | None = "utf8mb4_unicode_ci", length: int = 36, ): """Mirror the target FK column's data type + collation (TiDB strict-FK). See migration 192/210 docstrings for the full failure mode — TiDB rejects FK creation with (3780, "Referencing column ... incompatible.") when the source column's collation differs from the target's, even though MySQL accepts it. """ 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() if row: data_type = (row[0] or fallback_type).lower() collation = row[1] or fallback_collation else: data_type = fallback_type.lower() collation = fallback_collation type_cls = sa.CHAR if data_type == "char" else sa.String if collation: return type_cls(length, collation=collation) return type_cls(length) def upgrade() -> None: bind = op.get_bind() if _table_exists(bind, "identity_sync_department_state"): return target_col = _fk_source_col_type(bind, "identity_sync_targets", "id") op.create_table( "identity_sync_department_state", sa.Column("id", sa.CHAR(36), primary_key=True), sa.Column( "target_id", target_col, sa.ForeignKey("identity_sync_targets.id", ondelete="CASCADE"), nullable=False, ), sa.Column( "dept_code", sa.String(128), nullable=False, comment=( "Stable HRIS dept code — equals " "identity_org_nodes.org_node_source_id. The Helix " "side of the (dept_code → external_id) adoption map." ), ), sa.Column( "external_id", sa.String(255), nullable=True, comment=( "Identifier the downstream system assigned. For Feishu, " "this is the auto-generated od-XXX open_department_id — " "captured at adopt-by-name time (FeishuDepartmentRunner). " "See ADR in docs/architecture/DECISIONS.md." ), ), sa.Column( "last_payload_hash", sa.String(64), nullable=True, comment="sha256 of the last rendered, sorted-key JSON payload.", ), sa.Column( "last_status", sa.String(16), nullable=False, server_default="pending", comment=( "pending | success | skipped | failed | " "soft_deleted | excluded." ), ), sa.Column("last_synced_at", sa.DateTime(timezone=True), nullable=True), sa.Column("last_error", sa.Text, nullable=True), sa.Column( "retry_count", sa.Integer, nullable=False, server_default="0", ), sa.Column( "created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now(), ), sa.Column( "updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now(), ), sa.UniqueConstraint( "target_id", "dept_code", name="uq_identity_sync_department_state_target_dept", ), sa.Index( "ix_identity_sync_department_state_target_status", "target_id", "last_status", ), sa.Index( "ix_identity_sync_department_state_dept_code", "dept_code", ), ) def downgrade() -> None: bind = op.get_bind() if _table_exists(bind, "identity_sync_department_state"): op.drop_table("identity_sync_department_state")