"""ORM rows owned by Helix data-access management.""" from __future__ import annotations import uuid from datetime import datetime from sqlalchemy import ( JSON, Boolean, Computed, DateTime, ForeignKey, Index, Integer, String, Text, UniqueConstraint, ) from sqlalchemy.orm import Mapped, mapped_column, relationship from sqlalchemy.sql import func from app.core.database import Base from app.core.sql_types import GUID as Uuid from app.domains.data_access import constants as c RESOURCE_STATUS_ACTIVE = "active" RESOURCE_STATUS_SUSPENDED = "suspended" RESOURCE_STATUS_DELETED = "deleted" RESOURCE_STATUSES = frozenset({ RESOURCE_STATUS_ACTIVE, RESOURCE_STATUS_SUSPENDED, RESOURCE_STATUS_DELETED, }) OWNER_RESOLUTION_RESOLVED = "resolved" OWNER_RESOLUTION_UNRESOLVED = "unresolved_owner" OWNER_RESOLUTION_FALLBACK = "fallback_owner" OWNER_RESOLUTION_STATUSES = frozenset({ OWNER_RESOLUTION_RESOLVED, OWNER_RESOLUTION_UNRESOLVED, OWNER_RESOLUTION_FALLBACK, }) OWNER_SOURCE_CATALOG = "catalog" OWNER_SOURCE_BOOTSTRAP = "bootstrap" OWNER_SOURCE_CATALOG_TRANSFERRED = "catalog_transferred" OWNER_SOURCE_BOOTSTRAP_TRANSFERRED = "bootstrap_transferred" OWNER_SOURCE_DATASET_INHERITED = "dataset_inherited" OWNER_SOURCE_MANUAL = "manual" OWNER_SOURCES = frozenset({ OWNER_SOURCE_CATALOG, OWNER_SOURCE_BOOTSTRAP, OWNER_SOURCE_CATALOG_TRANSFERRED, OWNER_SOURCE_BOOTSTRAP_TRANSFERRED, OWNER_SOURCE_DATASET_INHERITED, OWNER_SOURCE_MANUAL, }) PRINCIPAL_STATUS_ACTIVE = "active" PRINCIPAL_STATUS_SUSPENDED = "suspended" PRINCIPAL_STATUS_DELETED_CONFIRMED = "deleted_confirmed" PRINCIPAL_STATUSES = frozenset({ PRINCIPAL_STATUS_ACTIVE, PRINCIPAL_STATUS_SUSPENDED, PRINCIPAL_STATUS_DELETED_CONFIRMED, }) SERVICE_ACCOUNT_ENVIRONMENTS = frozenset({"dev", "staging", "prod"}) ENTITLEMENT_VERSION_V1 = "v1" ENTITLEMENT_STATUS_ACTIVE = "active" ENTITLEMENT_STATUSES = frozenset({ENTITLEMENT_STATUS_ACTIVE}) REQUEST_STATUS_PENDING_FEISHU = "pending_feishu" REQUEST_STATUS_APPROVED = "approved" REQUEST_STATUS_REJECTED = "rejected" REQUEST_STATUS_STALE_REQUIRES_RESUBMIT = "stale_requires_resubmit" REQUEST_STATUS_CANCELLED = "cancelled" REQUEST_STATUSES = frozenset({ REQUEST_STATUS_PENDING_FEISHU, REQUEST_STATUS_APPROVED, REQUEST_STATUS_REJECTED, REQUEST_STATUS_STALE_REQUIRES_RESUBMIT, REQUEST_STATUS_CANCELLED, }) REQUEST_ITEM_STATUS_PENDING = "pending" REQUEST_ITEM_STATUS_APPROVED = "approved" REQUEST_ITEM_STATUS_REJECTED = "rejected" REQUEST_ITEM_STATUS_STALE = "stale" REQUEST_ITEM_STATUSES = frozenset({ REQUEST_ITEM_STATUS_PENDING, REQUEST_ITEM_STATUS_APPROVED, REQUEST_ITEM_STATUS_REJECTED, REQUEST_ITEM_STATUS_STALE, }) BINDING_STATUS_ACTIVE = "active" BINDING_STATUS_REVOKED_PENDING = "revoked_pending" BINDING_STATUS_REVOKED = "revoked" BINDING_STATUSES = frozenset({ BINDING_STATUS_ACTIVE, BINDING_STATUS_REVOKED_PENDING, BINDING_STATUS_REVOKED, }) BINDING_CURRENT_STATUSES = frozenset({ BINDING_STATUS_ACTIVE, BINDING_STATUS_REVOKED_PENDING, }) RECONCILE_STATUS_NOT_STARTED = "not_started" RECONCILE_STATUS_PENDING = "pending_reconcile" RECONCILE_STATUS_OBSERVED = "observed" RECONCILE_STATUS_APPLIED = "applied" RECONCILE_STATUS_RETRYABLE = "retryable" RECONCILE_STATUS_FAILED = "failed" RECONCILE_STATUSES = frozenset({ RECONCILE_STATUS_NOT_STARTED, RECONCILE_STATUS_PENDING, RECONCILE_STATUS_OBSERVED, RECONCILE_STATUS_APPLIED, RECONCILE_STATUS_RETRYABLE, RECONCILE_STATUS_FAILED, }) EFFECT_DESIRED_PRESENT = "present" EFFECT_DESIRED_ABSENT = "absent" EFFECT_DESIRED_STATES = frozenset({ EFFECT_DESIRED_PRESENT, EFFECT_DESIRED_ABSENT, }) EFFECT_STATUS_PENDING_RECONCILE = "pending_reconcile" EFFECT_STATUS_OBSERVED = "observed" EFFECT_STATUS_APPLIED = "applied" EFFECT_STATUS_RETRYABLE = "retryable" EFFECT_STATUS_FAILED = "failed" EFFECT_STATUSES = frozenset({ EFFECT_STATUS_PENDING_RECONCILE, EFFECT_STATUS_OBSERVED, EFFECT_STATUS_APPLIED, EFFECT_STATUS_RETRYABLE, EFFECT_STATUS_FAILED, }) EFFECT_MANAGED_REASON_GRANT_REQUEST = "grant_request" EFFECT_MANAGED_REASON_REVOKE_REQUEST = "revoke_request" EFFECT_MANAGED_REASON_GROUP_RENAME_BRIDGE = "group_rename_bridge" EFFECT_MANAGED_REASONS = frozenset({ EFFECT_MANAGED_REASON_GRANT_REQUEST, EFFECT_MANAGED_REASON_REVOKE_REQUEST, EFFECT_MANAGED_REASON_GROUP_RENAME_BRIDGE, }) class DataAccessResource(Base): """One requestable BigQuery dataset projection. Runtime resource-owner authority is intentionally derived only from this row: ``owner_resolution_status='resolved'`` and a non-NULL ``owner_person_id``. Bootstrap/catalog raw input remains provenance, not a second authorization read path. """ __tablename__ = "data_access_resources" __mapper_args__ = {"eager_defaults": True} __table_args__ = ( UniqueConstraint( "active_locator_key", name="uq_data_access_resources_active_locator", ), Index("ix_data_access_resources_integration", "integration_id"), Index("ix_data_access_resources_owner", "owner_person_id"), Index("ix_data_access_resources_status", "status"), Index("ix_data_access_resources_requestable", "requestable"), Index("ix_data_access_resources_source", "owner_source", "source_version"), Index("ix_data_access_resources_catalog_id", "catalog_resource_id"), Index( "ix_data_access_resources_parent", "parent_resource_id", ), ) id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4) integration_id: Mapped[str] = mapped_column( String(36), ForeignKey("integrations.id", ondelete="RESTRICT"), nullable=False, ) provider: Mapped[str] = mapped_column( String(32), nullable=False, default=c.PROVIDER_BIGQUERY, server_default=c.PROVIDER_BIGQUERY, ) resource_type: Mapped[str] = mapped_column( String(32), nullable=False, default=c.RESOURCE_TYPE_DATASET, server_default=c.RESOURCE_TYPE_DATASET, ) project_id: Mapped[str] = mapped_column( String(64, collation="utf8mb4_bin"), nullable=False, ) dataset_id: Mapped[str] = mapped_column( String(1024, collation="utf8mb4_bin"), nullable=False, ) parent_resource_id: Mapped[uuid.UUID | None] = mapped_column( Uuid, ForeignKey("data_access_resources.id", ondelete="CASCADE"), ) table_id: Mapped[str | None] = mapped_column( String(1024, collation="utf8mb4_bin"), ) table_type: Mapped[str | None] = mapped_column(String(32)) display_name: Mapped[str | None] = mapped_column(String(255)) labels: Mapped[dict[str, str] | None] = mapped_column(JSON) catalog_resource_id: Mapped[str | None] = mapped_column( String(128, collation="utf8mb4_bin"), ) requestable: Mapped[bool] = mapped_column( Boolean, nullable=False, default=True, server_default="1", ) status: Mapped[str] = mapped_column( String(24), nullable=False, default=RESOURCE_STATUS_ACTIVE, server_default=RESOURCE_STATUS_ACTIVE, ) owner_person_id: Mapped[str | None] = mapped_column( String(36), ForeignKey("identity_persons.id", ondelete="SET NULL"), ) owner_resolution_status: Mapped[str] = mapped_column( String(32), nullable=False, default=OWNER_RESOLUTION_RESOLVED, server_default=OWNER_RESOLUTION_RESOLVED, ) owner_source: Mapped[str] = mapped_column(String(32), nullable=False) source_version: Mapped[str] = mapped_column(String(128), nullable=False) source_etag: Mapped[str] = mapped_column(String(256), nullable=False) source_payload_digest: Mapped[str | None] = mapped_column(String(128)) active_locator_key: Mapped[str | None] = mapped_column( String(64), Computed( "CASE WHEN status = 'active' THEN " "SHA2(CONCAT_WS('|', integration_id, provider, resource_type, " "project_id, dataset_id, COALESCE(table_id, '')), 256) ELSE NULL END", ), ) # Request-visible business description projected from catalog input. notes: Mapped[str | None] = mapped_column(Text) 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(), ) @property def resource_owner_authority_enabled(self) -> bool: return ( self.owner_resolution_status == OWNER_RESOLUTION_RESOLVED and self.owner_person_id is not None ) class DataAccessPrincipal(Base): """DAX principal identity used by request and reconciliation state. Google Groups are projected here while their directory facts remain owned by ``email_groups``. Typed GCP service-account requests use the normalised email as their immutable subject selector; a later observed catalog may enrich that record but cannot be required to author a request. """ __tablename__ = "data_access_principals" __mapper_args__ = {"eager_defaults": True} __table_args__ = ( UniqueConstraint( "principal_type", "external_subject_id", name="uq_data_access_principals_type_subject", ), UniqueConstraint("email_group_id", name="uq_data_access_principals_group"), Index("ix_data_access_principals_type_status", "principal_type", "status"), Index("ix_data_access_principals_email", "email_snapshot"), Index("ix_data_access_principals_catalog", "catalog_id"), ) id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4) principal_type: Mapped[str] = mapped_column(String(32), nullable=False) external_subject_id: Mapped[str] = mapped_column( String(128, collation="utf8mb4_bin"), nullable=False, ) email_snapshot: Mapped[str] = mapped_column( String(255, collation="utf8mb4_bin"), nullable=False, ) status: Mapped[str] = mapped_column( String(32), nullable=False, default=PRINCIPAL_STATUS_ACTIVE, server_default=PRINCIPAL_STATUS_ACTIVE, ) catalog_id: Mapped[str | None] = mapped_column( String(128, collation="utf8mb4_bin"), ) source_version: Mapped[str | None] = mapped_column(String(128)) source_etag: Mapped[str | None] = mapped_column(String(256)) source_payload_digest: Mapped[str | None] = mapped_column(String(128)) email_group_id: Mapped[uuid.UUID | None] = mapped_column( Uuid, ForeignKey("email_groups.id", ondelete="SET NULL"), ) 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(), ) class DataAccessServiceAccountProfile(Base): """Optional observed catalog metadata for one GCP service-account principal. It is not a selector or authorization requirement for self-service DAX authoring; dataset ownership remains the approval authority. """ __tablename__ = "data_access_service_account_profiles" __mapper_args__ = {"eager_defaults": True} __table_args__ = ( UniqueConstraint("catalog_service_account_id", name="uq_dax_sa_profile_catalog"), Index("ix_dax_sa_profile_owner", "technical_owner_person_id"), Index("ix_dax_sa_profile_project_env", "gcp_project_id", "environment"), ) principal_id: Mapped[uuid.UUID] = mapped_column( Uuid, ForeignKey("data_access_principals.id", ondelete="CASCADE"), primary_key=True, ) catalog_service_account_id: Mapped[str] = mapped_column( String(128, collation="utf8mb4_bin"), nullable=False, ) gcp_project_id: Mapped[str] = mapped_column( String(64, collation="utf8mb4_bin"), nullable=False, ) environment: Mapped[str] = mapped_column(String(16), nullable=False) workload_ref: Mapped[str] = mapped_column(String(255), nullable=False) technical_owner_person_id: Mapped[uuid.UUID | None] = mapped_column( Uuid, ForeignKey("identity_persons.id", ondelete="SET NULL"), ) lifecycle_provenance: Mapped[str | None] = mapped_column(String(128)) 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(), ) principal: Mapped[DataAccessPrincipal] = relationship("DataAccessPrincipal") class DataAccessEntitlement(Base): """Canonical semantic entitlement catalog. DAX V1 intentionally has one entitlement: ``read`` version ``v1``. Provider role strings are compiler output from this row, not request input and not per-resource configuration. """ __tablename__ = "data_access_entitlements" __mapper_args__ = {"eager_defaults": True} __table_args__ = ( UniqueConstraint( "entitlement", "version", name="uq_data_access_entitlements_entitlement_version", ), Index("ix_data_access_entitlements_status", "status"), Index("ix_data_access_entitlements_requestable", "requestable"), Index( "ix_data_access_entitlements_scope", "provider", "resource_type", "principal_type", ), ) id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4) entitlement: Mapped[str] = mapped_column(String(32), nullable=False) version: Mapped[str] = mapped_column( String(16), nullable=False, default=ENTITLEMENT_VERSION_V1, server_default=ENTITLEMENT_VERSION_V1, ) domain: Mapped[str] = mapped_column( String(32), nullable=False, default=c.DOMAIN_DATA_ACCESS, server_default=c.DOMAIN_DATA_ACCESS, ) provider: Mapped[str] = mapped_column( String(32), nullable=False, default=c.PROVIDER_BIGQUERY, server_default=c.PROVIDER_BIGQUERY, ) resource_type: Mapped[str] = mapped_column( String(32), nullable=False, default=c.RESOURCE_TYPE_DATASET, server_default=c.RESOURCE_TYPE_DATASET, ) principal_type: Mapped[str] = mapped_column( String(32), nullable=False, default=c.PRINCIPAL_TYPE_GOOGLE_GROUP, server_default=c.PRINCIPAL_TYPE_GOOGLE_GROUP, ) provider_member_prefix: Mapped[str] = mapped_column( String(32), nullable=False, default=c.BIGQUERY_GROUP_MEMBER_PREFIX, server_default=c.BIGQUERY_GROUP_MEMBER_PREFIX, ) provider_role: Mapped[str] = mapped_column( String(128), nullable=False, default=c.BIGQUERY_DATA_VIEWER_ROLE, server_default=c.BIGQUERY_DATA_VIEWER_ROLE, ) requestable: Mapped[bool] = mapped_column( Boolean, nullable=False, default=True, server_default="1", ) status: Mapped[str] = mapped_column( String(24), nullable=False, default=ENTITLEMENT_STATUS_ACTIVE, server_default=ENTITLEMENT_STATUS_ACTIVE, ) description: Mapped[str | None] = mapped_column(Text) 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(), ) @property def semantic_key(self) -> str: return f"{self.entitlement}:{self.version}" @property def provider_effect(self) -> dict[str, str]: return { "member_prefix": self.provider_member_prefix, "role": self.provider_role, } @property def is_canonical_read_v1(self) -> bool: return ( self.domain == c.DOMAIN_DATA_ACCESS and self.provider == c.PROVIDER_BIGQUERY and self.resource_type == c.RESOURCE_TYPE_DATASET and self.entitlement == c.ENTITLEMENT_READ and self.version == ENTITLEMENT_VERSION_V1 and self.principal_type == c.PRINCIPAL_TYPE_GOOGLE_GROUP and self.provider_member_prefix == c.BIGQUERY_GROUP_MEMBER_PREFIX and self.provider_role == c.BIGQUERY_DATA_VIEWER_ROLE ) class DataAccessRequest(Base): """Semantic data-access grant request. ``principal_id`` is the durable cross-type identity when projected. The legacy ``principal_email_group_id`` remains a read-compatible Google Group reference; provider-facing subject and email fields are snapshots used for callback staleness checks and later compilation. """ __tablename__ = "data_access_requests" __mapper_args__ = {"eager_defaults": True} __table_args__ = ( UniqueConstraint( "feishu_instance_code", name="uq_data_access_requests_feishu_instance_code", ), UniqueConstraint( "requester_person_id", "idempotency_key", name="uq_data_access_requests_requester_idempotency", ), Index("ix_data_access_requests_requester", "requester_person_id"), Index("ix_data_access_requests_principal", "principal_email_group_id"), Index("ix_data_access_requests_principal_v2", "principal_id"), Index("ix_data_access_requests_entitlement", "entitlement_id"), Index("ix_data_access_requests_status", "status"), Index("ix_data_access_requests_reconcile_status", "reconcile_status"), Index("ix_data_access_requests_approval_request", "approval_request_id"), ) id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4) requester_person_id: Mapped[uuid.UUID] = mapped_column( Uuid, ForeignKey("identity_persons.id"), nullable=False, ) principal_email_group_id: Mapped[uuid.UUID | None] = mapped_column( Uuid, ForeignKey("email_groups.id"), nullable=True, ) principal_id: Mapped[uuid.UUID | None] = mapped_column( Uuid, ForeignKey("data_access_principals.id"), nullable=True, ) principal_type_snapshot: Mapped[str] = mapped_column( String(32), nullable=False, default=c.PRINCIPAL_TYPE_GOOGLE_GROUP, server_default=c.PRINCIPAL_TYPE_GOOGLE_GROUP, ) principal_subject_snapshot: Mapped[str | None] = mapped_column(String(128)) principal_google_group_id_snapshot: Mapped[str | None] = mapped_column( String(128), ) principal_email_snapshot: Mapped[str] = mapped_column( String(255), nullable=False, ) entitlement_id: Mapped[uuid.UUID] = mapped_column( Uuid, ForeignKey("data_access_entitlements.id"), nullable=False, ) entitlement_snapshot: Mapped[str] = mapped_column( String(32), nullable=False, default=c.ENTITLEMENT_READ, server_default=c.ENTITLEMENT_READ, ) entitlement_version_snapshot: Mapped[str] = mapped_column( String(16), nullable=False, default=ENTITLEMENT_VERSION_V1, server_default=ENTITLEMENT_VERSION_V1, ) approval_flow_key: Mapped[str] = mapped_column( String(64), nullable=False, default=c.APPROVAL_FLOW_DATA_ACCESS_GROUP_GRANT, server_default=c.APPROVAL_FLOW_DATA_ACCESS_GROUP_GRANT, ) feishu_instance_code: Mapped[str | None] = mapped_column(String(128)) approval_request_id: Mapped[uuid.UUID | None] = mapped_column( Uuid, ForeignKey("approval_requests.id", ondelete="SET NULL"), ) idempotency_key: Mapped[str | None] = mapped_column(String(128)) business_reason: Mapped[str | None] = mapped_column(Text) status: Mapped[str] = mapped_column( String(32), nullable=False, default=REQUEST_STATUS_PENDING_FEISHU, server_default=REQUEST_STATUS_PENDING_FEISHU, ) reconcile_status: Mapped[str] = mapped_column( String(32), nullable=False, default=RECONCILE_STATUS_NOT_STARTED, server_default=RECONCILE_STATUS_NOT_STARTED, ) last_reconcile_run_id: Mapped[uuid.UUID | None] = mapped_column(Uuid) last_reconcile_at: Mapped[datetime | None] = mapped_column( DateTime(timezone=True), ) last_reconcile_error: Mapped[str | None] = mapped_column(Text) trace_id: Mapped[str | None] = mapped_column(String(128)) submitted_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), nullable=False, server_default=func.now(), ) decided_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True)) 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(), ) entitlement: Mapped[DataAccessEntitlement] = relationship( "DataAccessEntitlement", ) items: Mapped[list[DataAccessRequestItem]] = relationship( back_populates="request", cascade="all, delete-orphan", ) class DataAccessRequestItem(Base): """One requested dataset item under a data-access request.""" __tablename__ = "data_access_request_items" __mapper_args__ = {"eager_defaults": True} __table_args__ = ( UniqueConstraint( "request_id", "line_no", name="uq_data_access_request_items_request_line", ), UniqueConstraint( "request_id", "resource_id", name="uq_data_access_request_items_request_resource", ), Index("ix_data_access_request_items_resource", "resource_id"), Index("ix_data_access_request_items_status", "status"), ) id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4) request_id: Mapped[uuid.UUID] = mapped_column( Uuid, ForeignKey("data_access_requests.id", ondelete="CASCADE"), nullable=False, ) resource_id: Mapped[uuid.UUID] = mapped_column( Uuid, ForeignKey("data_access_resources.id"), nullable=False, ) line_no: Mapped[int] = mapped_column(Integer, nullable=False) resource_project_id_snapshot: Mapped[str] = mapped_column( String(64), nullable=False, ) resource_dataset_id_snapshot: Mapped[str] = mapped_column( String(1024), nullable=False, ) resource_source_version_snapshot: Mapped[str] = mapped_column( String(128), nullable=False, ) resource_source_etag_snapshot: Mapped[str] = mapped_column( String(256), nullable=False, ) resource_owner_person_id_snapshot: Mapped[uuid.UUID | None] = mapped_column( Uuid, ForeignKey("identity_persons.id", ondelete="SET NULL"), ) status: Mapped[str] = mapped_column( String(32), nullable=False, default=REQUEST_ITEM_STATUS_PENDING, server_default=REQUEST_ITEM_STATUS_PENDING, ) staleness_reason: Mapped[str | None] = mapped_column(String(64)) 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(), ) request: Mapped[DataAccessRequest] = relationship(back_populates="items") resource: Mapped[DataAccessResource] = relationship("DataAccessResource") class DataAccessBinding(Base): """Approved semantic dataset access binding. Uniqueness is based on resource id + ``email_groups.id`` principal + entitlement id. The primary-email snapshot is preserved for provider compile/revoke history and rename bridges, but it is not the semantic key. """ __tablename__ = "data_access_bindings" __mapper_args__ = {"eager_defaults": True} __table_args__ = ( UniqueConstraint( "active_semantic_key", name="uq_data_access_bindings_active_semantic", ), Index("ix_data_access_bindings_resource", "resource_id"), Index("ix_data_access_bindings_principal", "principal_email_group_id"), Index("ix_data_access_bindings_principal_v2", "principal_id"), Index("ix_data_access_bindings_entitlement", "entitlement_id"), Index("ix_data_access_bindings_status", "status"), Index("ix_data_access_bindings_reconcile_status", "reconcile_status"), Index("ix_data_access_bindings_request_item", "source_request_item_id"), ) id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4) resource_id: Mapped[uuid.UUID] = mapped_column( Uuid, ForeignKey("data_access_resources.id"), nullable=False, ) principal_email_group_id: Mapped[uuid.UUID | None] = mapped_column( Uuid, ForeignKey("email_groups.id"), nullable=True, ) principal_id: Mapped[uuid.UUID | None] = mapped_column( Uuid, ForeignKey("data_access_principals.id"), nullable=True, ) principal_type_snapshot: Mapped[str] = mapped_column( String(32), nullable=False, default=c.PRINCIPAL_TYPE_GOOGLE_GROUP, server_default=c.PRINCIPAL_TYPE_GOOGLE_GROUP, ) principal_subject_snapshot: Mapped[str | None] = mapped_column(String(128)) principal_google_group_id_snapshot: Mapped[str | None] = mapped_column( String(128), ) principal_email_snapshot: Mapped[str] = mapped_column( String(255), nullable=False, ) entitlement_id: Mapped[uuid.UUID] = mapped_column( Uuid, ForeignKey("data_access_entitlements.id"), nullable=False, ) entitlement_snapshot: Mapped[str] = mapped_column( String(32), nullable=False, default=c.ENTITLEMENT_READ, server_default=c.ENTITLEMENT_READ, ) entitlement_version_snapshot: Mapped[str] = mapped_column( String(16), nullable=False, default=ENTITLEMENT_VERSION_V1, server_default=ENTITLEMENT_VERSION_V1, ) source_request_id: Mapped[uuid.UUID | None] = mapped_column( Uuid, ForeignKey("data_access_requests.id", ondelete="SET NULL"), ) source_request_item_id: Mapped[uuid.UUID | None] = mapped_column( Uuid, ForeignKey("data_access_request_items.id", ondelete="SET NULL"), ) status: Mapped[str] = mapped_column( String(32), nullable=False, default=BINDING_STATUS_ACTIVE, server_default=BINDING_STATUS_ACTIVE, ) reconcile_status: Mapped[str] = mapped_column( String(32), nullable=False, default=RECONCILE_STATUS_PENDING, server_default=RECONCILE_STATUS_PENDING, ) last_reconcile_run_id: Mapped[uuid.UUID | None] = mapped_column(Uuid) last_reconcile_at: Mapped[datetime | None] = mapped_column( DateTime(timezone=True), ) last_reconcile_error: Mapped[str | None] = mapped_column(Text) active_semantic_key: Mapped[str | None] = mapped_column( String(64), Computed( "CASE WHEN status IN ('active', 'revoked_pending') THEN " "SHA2(CONCAT_WS('|', resource_id, COALESCE(principal_id, principal_email_group_id), " "entitlement_id), 256) ELSE NULL END", ), ) granted_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), nullable=False, server_default=func.now(), ) revoked_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True)) revoke_reason: Mapped[str | None] = mapped_column(String(64)) # ── Lifecycle (migration 606) ────────────────────────────────── # Personal (google_user) grants expire; the holder renews a bounded # number of times before a fresh Feishu request is required. Group / # service-account grants never carry ``expires_at``. expires_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True)) expiry_reminder_stage: Mapped[int] = mapped_column( Integer, nullable=False, default=0, server_default="0", ) expiry_last_reminded_at: Mapped[datetime | None] = mapped_column( DateTime(timezone=True), ) renewal_count: Mapped[int] = mapped_column( Integer, nullable=False, default=0, server_default="0", ) last_renewed_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True)) last_renewed_by_person_id: Mapped[str | None] = mapped_column(String(36)) # Service-account grants: the technical owner re-confirms twice a year # that the workload still needs the dataset. owner_confirmation_due_at: Mapped[datetime | None] = mapped_column( DateTime(timezone=True), ) owner_confirmed_at: Mapped[datetime | None] = mapped_column( DateTime(timezone=True), ) owner_confirmed_by_person_id: Mapped[str | None] = mapped_column(String(36)) owner_confirmation_reminder_count: Mapped[int] = mapped_column( Integer, nullable=False, default=0, server_default="0", ) owner_confirmation_last_reminded_at: Mapped[datetime | None] = mapped_column( DateTime(timezone=True), ) 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(), ) resource: Mapped[DataAccessResource] = relationship("DataAccessResource") entitlement: Mapped[DataAccessEntitlement] = relationship( "DataAccessEntitlement", ) # ── Lifecycle event kinds (migration 606) ─────────────────────────── LIFECYCLE_EVENT_EXPIRY_STAMPED = "expiry_stamped" LIFECYCLE_EVENT_EXPIRY_REMINDER = "expiry_reminder" LIFECYCLE_EVENT_RENEWED = "renewed" LIFECYCLE_EVENT_EXTENDED_BY_ADMIN = "extended_by_admin" LIFECYCLE_EVENT_EXPIRED_REVOKE = "expired_revoke" LIFECYCLE_EVENT_EXPIRED_OVERDUE = "expired_overdue" LIFECYCLE_EVENT_OWNER_CONFIRMATION_REQUESTED = "owner_confirmation_requested" LIFECYCLE_EVENT_OWNER_CONFIRMATION_REMINDER = "owner_confirmation_reminder" LIFECYCLE_EVENT_OWNER_CONFIRMED = "owner_confirmed" LIFECYCLE_EVENT_OWNER_DECLINED = "owner_declined" LIFECYCLE_EVENT_OWNER_CONFIRMATION_LAPSED_REVOKE = "owner_confirmation_lapsed_revoke" LIFECYCLE_EVENT_RELEASED_BY_HOLDER = "released_by_holder" LIFECYCLE_EVENT_REVOKED_BY_ADMIN = "revoked_by_admin" LIFECYCLE_EVENT_REVOKED_OFFBOARDING = "revoked_offboarding" LIFECYCLE_EVENT_REVOKED_SELF_ATTESTATION = "revoked_self_attestation" # ``revoke_reason`` vocabulary written by the lifecycle (the column is free # text elsewhere; these are the values the admin console filters on). REVOKE_REASON_EXPIRED = "expired" REVOKE_REASON_OWNER_CONFIRMATION_LAPSED = "owner_confirmation_lapsed" REVOKE_REASON_OWNER_DECLINED = "owner_declined" REVOKE_REASON_HOLDER_RELEASED = "holder_released" REVOKE_REASON_ADMIN = "admin_revoked" REVOKE_REASON_OFFBOARDING = "offboarding" REVOKE_REASON_SELF_ATTESTATION = "self_attestation" class DataAccessLifecycleEvent(Base): """Append-only timeline of what happened to one binding after grant. Read by the admin console's timeline drawer and by the audit surface; written only by :mod:`app.domains.data_access.lifecycle`. ``detail`` is resource-shaped context (old / new expiry, reminder stage, who confirmed) — never an identifier. """ __tablename__ = "data_access_lifecycle_events" __table_args__ = ( Index("ix_dax_lifecycle_events_binding_created", "binding_id", "created_at"), Index("ix_dax_lifecycle_events_kind_created", "event_kind", "created_at"), ) id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4) binding_id: Mapped[uuid.UUID] = mapped_column( Uuid, ForeignKey("data_access_bindings.id", ondelete="CASCADE"), nullable=False, ) event_kind: Mapped[str] = mapped_column(String(64), nullable=False) actor_type: Mapped[str] = mapped_column( String(16), nullable=False, default="system", server_default="system", ) actor_person_id: Mapped[str | None] = mapped_column(String(36)) detail: Mapped[dict | None] = mapped_column(JSON) created_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), nullable=False, server_default=func.now(), ) class DataAccessCompiledEffect(Base): """Provider-specific effect compiled from semantic binding state. ``principal_email_group_id`` remains the semantic principal. The provider member string is a compiled snapshot, e.g. ``group:analytics@example.com``, so rename bridges can compare and recompile without treating aliases as authority. """ __tablename__ = "data_access_compiled_effects" __mapper_args__ = {"eager_defaults": True} __table_args__ = ( Index("ix_data_access_compiled_effects_binding", "binding_id"), Index("ix_data_access_compiled_effects_resource", "resource_id"), Index("ix_data_access_compiled_effects_principal", "principal_email_group_id"), Index("ix_data_access_compiled_effects_principal_v2", "principal_id"), Index("ix_data_access_compiled_effects_status", "status"), Index( "ix_data_access_compiled_effects_target", "target_locator_key", ), ) id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4) binding_id: Mapped[uuid.UUID] = mapped_column( Uuid, ForeignKey("data_access_bindings.id"), nullable=False, ) resource_id: Mapped[uuid.UUID] = mapped_column( Uuid, ForeignKey("data_access_resources.id"), nullable=False, ) principal_email_group_id: Mapped[uuid.UUID | None] = mapped_column( Uuid, ForeignKey("email_groups.id"), nullable=True, ) principal_id: Mapped[uuid.UUID | None] = mapped_column( Uuid, ForeignKey("data_access_principals.id"), nullable=True, ) principal_type_snapshot: Mapped[str] = mapped_column( String(32), nullable=False, default=c.PRINCIPAL_TYPE_GOOGLE_GROUP, server_default=c.PRINCIPAL_TYPE_GOOGLE_GROUP, ) principal_subject_snapshot: Mapped[str | None] = mapped_column(String(128)) entitlement_id: Mapped[uuid.UUID] = mapped_column( Uuid, ForeignKey("data_access_entitlements.id"), nullable=False, ) provider: Mapped[str] = mapped_column( String(32), nullable=False, default=c.PROVIDER_BIGQUERY, server_default=c.PROVIDER_BIGQUERY, ) resource_type: Mapped[str] = mapped_column( String(32), nullable=False, default=c.RESOURCE_TYPE_DATASET, server_default=c.RESOURCE_TYPE_DATASET, ) target_project_id: Mapped[str] = mapped_column( String(64, collation="utf8mb4_bin"), nullable=False, ) target_dataset_id: Mapped[str] = mapped_column( String(1024, collation="utf8mb4_bin"), nullable=False, ) target_table_id: Mapped[str | None] = mapped_column( String(1024, collation="utf8mb4_bin"), ) provider_member: Mapped[str] = mapped_column(String(320), nullable=False) provider_access_kind: Mapped[str] = mapped_column( String(32), nullable=False, default="groupByEmail", server_default="groupByEmail", ) provider_role: Mapped[str] = mapped_column( String(128), nullable=False, default=c.BIGQUERY_DATA_VIEWER_ROLE, server_default=c.BIGQUERY_DATA_VIEWER_ROLE, ) target_locator_key: Mapped[str] = mapped_column( String(64), Computed( "SHA2(CONCAT_WS('|', provider, resource_type, " "target_project_id, target_dataset_id, " "COALESCE(target_table_id, '')), 256)", ), ) desired_state: Mapped[str] = mapped_column( String(16), nullable=False, default=EFFECT_DESIRED_PRESENT, server_default=EFFECT_DESIRED_PRESENT, ) entitlement_version_snapshot: Mapped[str] = mapped_column( String(16), nullable=False, ) resource_source_version_snapshot: Mapped[str] = mapped_column( String(128), nullable=False, ) resource_source_etag_snapshot: Mapped[str] = mapped_column( String(256), nullable=False, ) compiler_version: Mapped[str] = mapped_column(String(64), nullable=False) managed_reason: Mapped[str] = mapped_column(String(64), nullable=False) already_present_at_first_apply: Mapped[bool] = mapped_column( Boolean, nullable=False, default=False, server_default="0", ) status: Mapped[str] = mapped_column( String(32), nullable=False, default=EFFECT_STATUS_PENDING_RECONCILE, server_default=EFFECT_STATUS_PENDING_RECONCILE, ) last_provider_etag: Mapped[str | None] = mapped_column(String(256)) last_provider_outcome: Mapped[str | None] = mapped_column(String(64)) last_error: Mapped[str | None] = mapped_column(Text) 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(), ) binding: Mapped[DataAccessBinding] = relationship("DataAccessBinding") resource: Mapped[DataAccessResource] = relationship("DataAccessResource") entitlement: Mapped[DataAccessEntitlement] = relationship( "DataAccessEntitlement", ) runs: Mapped[list[DataAccessEffectRun]] = relationship( back_populates="effect", cascade="all, delete-orphan", ) class DataAccessEffectRun(Base): """Per-effect provider observation/apply history. DAX-026 starts with observe-mode entries only. Rows snapshot the compiled effect and provider outcome so admins can see each attempted resource/principal/effect even after the effect's latest status changes. """ __tablename__ = "data_access_effect_runs" __mapper_args__ = {"eager_defaults": True} __table_args__ = ( Index("ix_data_access_effect_runs_run", "run_id"), Index("ix_data_access_effect_runs_effect", "effect_id"), Index("ix_data_access_effect_runs_binding", "binding_id"), Index("ix_data_access_effect_runs_request", "request_id"), Index("ix_data_access_effect_runs_status", "effect_status"), Index("ix_data_access_effect_runs_trace", "trace_id"), ) id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4) run_id: Mapped[uuid.UUID] = mapped_column(Uuid, nullable=False) effect_id: Mapped[uuid.UUID] = mapped_column( Uuid, ForeignKey("data_access_compiled_effects.id", ondelete="CASCADE"), nullable=False, ) binding_id: Mapped[uuid.UUID] = mapped_column( Uuid, ForeignKey("data_access_bindings.id"), nullable=False, ) request_id: Mapped[uuid.UUID | None] = mapped_column( Uuid, ForeignKey("data_access_requests.id", ondelete="SET NULL"), ) resource_id: Mapped[uuid.UUID] = mapped_column( Uuid, ForeignKey("data_access_resources.id"), nullable=False, ) integration_id: Mapped[str] = mapped_column( String(36), ForeignKey("integrations.id", ondelete="CASCADE"), nullable=False, ) trace_id: Mapped[str | None] = mapped_column(String(128)) mode: Mapped[str] = mapped_column( String(16), nullable=False, default="observe", server_default="observe", ) provider: Mapped[str] = mapped_column(String(32), nullable=False) resource_type: Mapped[str] = mapped_column(String(32), nullable=False) target_project_id: Mapped[str] = mapped_column( String(64, collation="utf8mb4_bin"), nullable=False, ) target_dataset_id: Mapped[str] = mapped_column( String(1024, collation="utf8mb4_bin"), nullable=False, ) target_table_id: Mapped[str | None] = mapped_column( String(1024, collation="utf8mb4_bin"), ) provider_member: Mapped[str] = mapped_column(String(320), nullable=False) principal_id: Mapped[uuid.UUID | None] = mapped_column( Uuid, ForeignKey("data_access_principals.id"), nullable=True, ) principal_type_snapshot: Mapped[str] = mapped_column( String(32), nullable=False, default=c.PRINCIPAL_TYPE_GOOGLE_GROUP, server_default=c.PRINCIPAL_TYPE_GOOGLE_GROUP, ) principal_subject_snapshot: Mapped[str | None] = mapped_column(String(128)) provider_access_kind: Mapped[str] = mapped_column( String(32), nullable=False, default="groupByEmail", server_default="groupByEmail", ) provider_role: Mapped[str] = mapped_column(String(128), nullable=False) desired_state: Mapped[str] = mapped_column(String(16), nullable=False) effect_status: Mapped[str] = mapped_column(String(32), nullable=False) provider_outcome: Mapped[str] = mapped_column(String(64), nullable=False) provider_etag: Mapped[str | None] = mapped_column(String(256)) error_message: Mapped[str | None] = mapped_column(Text) started_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), nullable=False, ) completed_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), nullable=False, ) created_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), nullable=False, server_default=func.now(), ) effect: Mapped[DataAccessCompiledEffect] = relationship( back_populates="runs", ) binding: Mapped[DataAccessBinding] = relationship("DataAccessBinding") request: Mapped[DataAccessRequest | None] = relationship("DataAccessRequest") resource: Mapped[DataAccessResource] = relationship("DataAccessResource")