"""SQLAlchemy models for the unified integration framework. Three tables: * ``integrations`` — one row per external-system instance. Carries credentials (encrypted), per-instance config, and health state. * ``integration_approval_flows`` — sub-definitions for ``chat_approval`` integrations where one connector hosts many distinct workflows (e.g. Feishu: laptop_replacement / api_key_request / ...). * ``platform_settings`` — flat key-value store for platform-internal business parameters that are **not** external-system credentials (offboarding grace period, default currency, etc.). ``kind`` and ``provider`` are plain strings so drivers register new values at import time without a schema change. The registry module is the source of truth for what's a valid (kind, provider) pair at any given time; the DB stays permissive so drivers can be introduced and retired without data churn. """ from __future__ import annotations import uuid from datetime import datetime from typing import Any from sqlalchemy import ( JSON, BigInteger, Boolean, DateTime, ForeignKey, Index, Integer, String, Text, UniqueConstraint, func, ) from sqlalchemy.orm import Mapped, mapped_column, relationship from app.core.database import Base from app.core.sql_types import GUID as Uuid class Integration(Base): """One configured instance of one external-system driver. Single-instance ``kind``s (HRIS, chat+approval) resolve via ``is_primary=True``; multi-instance ``kind``s (cloud accounts, software provisioning) are looked up by ``slug`` or by an explicit ``integration_id`` FK carried on the domain row. """ __tablename__ = "integrations" __mapper_args__ = {"eager_defaults": True} __table_args__ = ( UniqueConstraint("slug", name="uq_integrations_slug"), Index("ix_integrations_kind_provider", "kind", "provider"), Index("ix_integrations_kind_primary", "kind", "is_primary"), Index("ix_integrations_enabled", "enabled"), ) id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4) # Taxonomy — free-form strings, validated at runtime against the # driver registry. Keeping them open-ended avoids a schema change # every time a new provider (Azure, Slack, ...) is introduced. kind: Mapped[str] = mapped_column(String(32), nullable=False) provider: Mapped[str] = mapped_column(String(64), nullable=False) # Admin-facing identity name: Mapped[str] = mapped_column(String(128), nullable=False) slug: Mapped[str] = mapped_column(String(64), nullable=False) description: Mapped[str | None] = mapped_column(Text) # Lifecycle enabled: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True) # Resolved when callers request a kind without a specific slug. # Kinds with at most one "primary" row; ensured in service layer. is_primary: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False) # Config split: public fields go in JSON, secrets are AES-GCM # encrypted. Exact schema per (kind, provider) is driven by the # registered driver class. config_public: Mapped[dict[str, Any] | None] = mapped_column(JSON) secrets_encrypted: Mapped[str | None] = mapped_column(Text) secrets_nonce: Mapped[str | None] = mapped_column(String(64)) # Optional per-instance capability subset (e.g. cloud providers # that support dns + object_storage but not load_balancers). capabilities: Mapped[list[str] | None] = mapped_column(JSON) # Health / last-test telemetry — written by /test-connection. last_tested_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True)) last_test_status: Mapped[str | None] = mapped_column(String(16)) last_test_message: Mapped[str | None] = mapped_column(Text) created_by: Mapped[uuid.UUID | None] = mapped_column( Uuid, ForeignKey("identity_persons.id"), ) updated_by: Mapped[uuid.UUID | None] = mapped_column( Uuid, ForeignKey("identity_persons.id"), ) created_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), nullable=False, server_default=func.now(), ) updated_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), nullable=False, server_default=func.now(), onupdate=func.now(), ) approval_flows: Mapped[list[IntegrationApprovalFlow]] = relationship( "IntegrationApprovalFlow", back_populates="integration", cascade="all, delete-orphan", lazy="selectin", ) class IntegrationApprovalFlow(Base): """Per-integration approval flow mapping. For chat_approval integrations (Feishu etc.) that host many distinct approval templates. ``flow_key`` is the platform-internal identifier domain code looks up by (e.g. ``api_key_request``); ``external_code`` is the external system's template ID. """ __tablename__ = "integration_approval_flows" __mapper_args__ = {"eager_defaults": True} __table_args__ = ( UniqueConstraint( "integration_id", "flow_key", name="uq_integration_approval_flows_integration_flow_key", ), Index("ix_integration_approval_flows_flow_key", "flow_key"), ) id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4) integration_id: Mapped[uuid.UUID] = mapped_column( Uuid, ForeignKey("integrations.id", ondelete="CASCADE"), nullable=False, ) flow_key: Mapped[str] = mapped_column(String(64), nullable=False) label: Mapped[str] = mapped_column(String(128), nullable=False) external_code: Mapped[str] = mapped_column(String(128), nullable=False) # Optional: widget-id → canonical-field-name map the domain uses # to parse payload without hardcoded Feishu widget IDs. payload_schema: Mapped[dict[str, Any] | None] = mapped_column(JSON) active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True) # When TRUE the approval webhook handler synthesizes an # ``approval_requests`` row on the fly for instances we've never # seen — i.e. submissions that originate directly in Feishu, not # via Helix's ``instance/create`` (see migration 149). Default # FALSE preserves the strict "unknown instance_code → silent drop" # behavior for Helix-initiated flows. accept_external_origin: Mapped[bool] = mapped_column( Boolean, nullable=False, default=False, ) created_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), nullable=False, server_default=func.now(), ) updated_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), nullable=False, server_default=func.now(), onupdate=func.now(), ) integration: Mapped[Integration] = relationship( "Integration", back_populates="approval_flows", ) class PlatformSetting(Base): """Flat key-value store for platform-internal (non-integration) config. Keys are namespaced dotted strings (``offboarding.grace_period_days``, ``audit.retention_days``, ...) — the admin UI groups by the first segment. Values are JSON so callers can store numbers, strings, or structured dicts as needed. """ __tablename__ = "platform_settings" __mapper_args__ = {"eager_defaults": True} key: Mapped[str] = mapped_column(String(128), primary_key=True) value: Mapped[Any] = mapped_column(JSON, nullable=False) description: Mapped[str | None] = mapped_column(Text) updated_by: Mapped[uuid.UUID | None] = mapped_column( Uuid, ForeignKey("identity_persons.id"), ) updated_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), nullable=False, server_default=func.now(), onupdate=func.now(), ) created_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), nullable=False, server_default=func.now(), ) class StorageUploadGrant(Base): """Durable ledger for every browser-direct upload capability issued.""" __tablename__ = "storage_upload_grants" __table_args__ = ( UniqueConstraint("object_key", name="uq_storage_upload_grants_object_key"), Index("ix_storage_upload_grants_person_created", "person_id", "created_at"), Index("ix_storage_upload_grants_status_created", "status", "created_at"), Index( "ix_storage_upload_grants_cleanup", "status", "cleanup_attempted_at", "created_at", ), ) id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4) person_id: Mapped[uuid.UUID] = mapped_column( Uuid, ForeignKey("identity_persons.id"), nullable=False, ) purpose: Mapped[str] = mapped_column(String(64), nullable=False) object_key: Mapped[str] = mapped_column(String(512), nullable=False) content_type: Mapped[str] = mapped_column(String(128), nullable=False) declared_size_bytes: Mapped[int] = mapped_column(BigInteger, nullable=False) checksum_sha256: Mapped[str] = mapped_column(String(44), nullable=False) status: Mapped[str] = mapped_column(String(24), nullable=False, default="issued") referenced_resource_type: Mapped[str | None] = mapped_column(String(64)) referenced_resource_id: Mapped[str | None] = mapped_column(String(128)) expires_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False) referenced_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True)) deleted_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True)) cleanup_attempted_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True)) cleanup_attempts: Mapped[int] = mapped_column(Integer, nullable=False, default=0) created_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), nullable=False, server_default=func.now(), )