"""HR lifecycle automation foundation. Revision ID: 032 Revises: 031 Create Date: 2026-04-20 Five schema changes that together enable rule-driven onboarding and policy-driven offboarding across all resource types: 1. ``identity_persons.job_title`` (String 128, nullable) — sourced from HRIS; part of the 3-dim onboarding rule key (department + job_title + location). 2. ``software_connectors`` gains ``offboarding_policy`` + ``offboarding_config``. Each software declares how its seats are handled at offboarding: ``auto_revoke`` (driver API call) or ``sso_cascade`` (do nothing — dies with Google SSO). Default is ``sso_cascade`` (safest). 3. ``provision_policies`` gains ``match_job_title`` + ``match_location``. The unique key is now (department_name, match_job_title, match_location, target_type, target_id). NULL on any match key means "any value" — so the legacy department-only rule is still valid (job_title=NULL, location=NULL). 4. ``cmdb_cloud_accounts`` NEW. Per-user accounts within a cloud provider (e.g. "bob has an IAM user inside the AWS Singapore account"). Pre-existed as a dead reference in ``offboarding/service.py`` (wrapped in try/except pass). Now real. Each row declares its own ``offboarding_policy`` — ``auto_revoke`` for real IAM users that need deletion, ``sso_cascade`` for Google-federated login that dies with SSO, or ``must_transfer`` for shared accounts that change hands. 5. ``offboarding_reminder_log`` NEW. Idempotency marker for the 7/3/1-day-before-leaver-date reminders and the day-0 auto-escalation. One row per (process_id, reminder_kind) so the scheduler can skip what's already been sent. """ 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 = "032" down_revision = "031" branch_labels = None depends_on = None def upgrade() -> None: ID = id_type_for("identity_persons") KW = table_kw_for("identity_persons") # ── (1) identity_persons.job_title ────────────────────────────── op.add_column( "identity_persons", sa.Column("job_title", sa.String(128), nullable=True), ) op.create_index( "ix_identity_persons_job_title", "identity_persons", ["job_title"], ) # ── (2) software_connectors.offboarding_policy + _config ──────── op.add_column( "software_connectors", sa.Column( "offboarding_policy", sa.String(32), nullable=False, server_default="sso_cascade", ), ) op.add_column( "software_connectors", sa.Column("offboarding_config", sa.JSON, nullable=True), ) # ── (3) provision_policies 3-dim matching ─────────────────────── op.add_column( "provision_policies", sa.Column("match_job_title", sa.String(128), nullable=True), ) op.add_column( "provision_policies", sa.Column("match_location", sa.String(64), nullable=True), ) op.drop_constraint( "uq_provision_policy_dep_target", "provision_policies", type_="unique", ) op.create_unique_constraint( "uq_provision_policy_match_target", "provision_policies", [ "department_name", "match_job_title", "match_location", "target_type", "target_id", ], ) op.drop_index( "ix_provision_policies_department_active", table_name="provision_policies", ) op.create_index( "ix_provision_policies_match", "provision_policies", ["department_name", "match_job_title", "match_location", "is_active"], ) # ── (4) cmdb_cloud_accounts ───────────────────────────────────── op.create_table( "cmdb_cloud_accounts", sa.Column("id", ID, primary_key=True), sa.Column("provider", sa.String(32), nullable=False), sa.Column("account_id", sa.String(255), nullable=False), sa.Column("display_name", sa.String(255), nullable=True), sa.Column( "owner_person_id", ID, sa.ForeignKey("identity_persons.id", ondelete="SET NULL"), nullable=True, ), sa.Column( "integration_id", ID, sa.ForeignKey("integrations.id", ondelete="SET NULL"), nullable=True, ), sa.Column( "offboarding_policy", sa.String(32), nullable=False, server_default="sso_cascade", ), sa.Column("offboarding_config", sa.JSON, nullable=True), sa.Column("status", sa.String(32), nullable=False, server_default="active"), sa.Column("notes", sa.Text, nullable=True), 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.Column("deleted_at", sa.DateTime(timezone=True), nullable=True), sa.UniqueConstraint( "provider", "account_id", name="uq_cmdb_cloud_accounts_provider_account", ), **KW, ) op.create_index( "ix_cmdb_cloud_accounts_owner", "cmdb_cloud_accounts", ["owner_person_id"], ) op.create_index( "ix_cmdb_cloud_accounts_policy", "cmdb_cloud_accounts", ["offboarding_policy"], ) # ── (5) offboarding_reminder_log ──────────────────────────────── op.create_table( "offboarding_reminder_log", sa.Column("id", ID, primary_key=True), sa.Column( "process_id", ID, sa.ForeignKey("offboarding_processes.id", ondelete="CASCADE"), nullable=False, ), sa.Column("reminder_kind", sa.String(32), nullable=False), sa.Column("sent_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()), sa.Column("channels", sa.JSON, nullable=True), sa.Column("detail", sa.JSON, nullable=True), sa.UniqueConstraint( "process_id", "reminder_kind", name="uq_offboarding_reminder_log_process_kind", ), **KW, ) op.create_index( "ix_offboarding_reminder_log_process", "offboarding_reminder_log", ["process_id"], ) def downgrade() -> None: # Reverse in opposite order. op.drop_index( "ix_offboarding_reminder_log_process", table_name="offboarding_reminder_log", ) op.drop_table("offboarding_reminder_log") op.drop_index( "ix_cmdb_cloud_accounts_policy", table_name="cmdb_cloud_accounts", ) op.drop_index( "ix_cmdb_cloud_accounts_owner", table_name="cmdb_cloud_accounts", ) op.drop_table("cmdb_cloud_accounts") op.drop_index( "ix_provision_policies_match", table_name="provision_policies", ) op.create_index( "ix_provision_policies_department_active", "provision_policies", ["department_name", "is_active"], ) op.drop_constraint( "uq_provision_policy_match_target", "provision_policies", type_="unique", ) op.create_unique_constraint( "uq_provision_policy_dep_target", "provision_policies", ["department_name", "target_type", "target_id"], ) op.drop_column("provision_policies", "match_location") op.drop_column("provision_policies", "match_job_title") op.drop_column("software_connectors", "offboarding_config") op.drop_column("software_connectors", "offboarding_policy") op.drop_index( "ix_identity_persons_job_title", table_name="identity_persons", ) op.drop_column("identity_persons", "job_title")