PMFlow/backend/app/models/ai_project_control.py

"""AI 应用项目控制面核心模型。"""

from __future__ import annotations

from datetime import datetime
from typing import Optional

from sqlalchemy import BigInteger, Boolean, DECIMAL, ForeignKey, Integer, Text, TIMESTAMP, UniqueConstraint, VARCHAR
from sqlalchemy.orm import Mapped, mapped_column

from .base import Base
from .types import JSONB


class ModelCatalogItem(Base):
    """项目级模型目录条目。"""

    __tablename__ = "model_catalog_items"
    __table_args__ = (
        UniqueConstraint(
            "tenant_id",
            "provider",
            "model_key",
            "version",
            name="uq_model_catalog_items_tenant_provider_key_version",
        ),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    provider: Mapped[str] = mapped_column(VARCHAR(120), nullable=False, index=True)
    model_key: Mapped[str] = mapped_column(VARCHAR(120), nullable=False, index=True)
    display_name: Mapped[str] = mapped_column(VARCHAR(255), nullable=False)
    version: Mapped[str] = mapped_column(VARCHAR(120), nullable=False)
    modalities: Mapped[list[str]] = mapped_column(JSONB, nullable=False, default=list)
    capability_tags: Mapped[list[str]] = mapped_column(JSONB, nullable=False, default=list)
    strengths: Mapped[list[str]] = mapped_column(JSONB, nullable=False, default=list)
    limitations: Mapped[list[str]] = mapped_column(JSONB, nullable=False, default=list)
    compliance_status: Mapped[str] = mapped_column(VARCHAR(50), nullable=False, default="review_required")
    filing_status: Mapped[str] = mapped_column(VARCHAR(50), nullable=False, default="unknown")
    input_cost_per_mtok: Mapped[Optional[float]] = mapped_column(DECIMAL(12, 4), nullable=True)
    output_cost_per_mtok: Mapped[Optional[float]] = mapped_column(DECIMAL(12, 4), nullable=True)
    official_docs_url: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True, index=True)


class ProjectModelBinding(Base):
    """项目与模型绑定。"""

    __tablename__ = "project_model_bindings"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    project_id: Mapped[int] = mapped_column(BigInteger, ForeignKey("projects.id"), nullable=False, index=True)
    charter_id: Mapped[Optional[int]] = mapped_column(BigInteger, ForeignKey("truth_nodes.id"), nullable=True, index=True)
    model_catalog_item_id: Mapped[int] = mapped_column(
        BigInteger, ForeignKey("model_catalog_items.id"), nullable=False, index=True
    )
    selected_by_user_id: Mapped[Optional[int]] = mapped_column(BigInteger, ForeignKey("users.id"), nullable=True)
    status: Mapped[str] = mapped_column(VARCHAR(32), nullable=False, default="active", index=True)
    selection_reason: Mapped[dict] = mapped_column(JSONB, nullable=False, default=dict)
    estimated_daily_tokens: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    estimated_monthly_cost_usd: Mapped[Optional[float]] = mapped_column(DECIMAL(12, 2), nullable=True)
    active_from: Mapped[Optional[datetime]] = mapped_column(TIMESTAMP, nullable=True)
    archived_at: Mapped[Optional[datetime]] = mapped_column(TIMESTAMP, nullable=True)


class ModelRecommendationSnapshot(Base):
    """推荐结果快照。"""

    __tablename__ = "model_recommendation_snapshots"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    project_id: Mapped[int] = mapped_column(BigInteger, ForeignKey("projects.id"), nullable=False, index=True)
    charter_id: Mapped[Optional[int]] = mapped_column(BigInteger, ForeignKey("truth_nodes.id"), nullable=True, index=True)
    recommendation_scope: Mapped[str] = mapped_column(VARCHAR(32), nullable=False, default="charter")
    snapshot_input: Mapped[dict] = mapped_column(JSONB, nullable=False, default=dict)
    recommendations: Mapped[list[dict]] = mapped_column(JSONB, nullable=False, default=list)
    top_model_catalog_item_id: Mapped[Optional[int]] = mapped_column(
        BigInteger, ForeignKey("model_catalog_items.id"), nullable=True
    )
    generated_by: Mapped[str] = mapped_column(VARCHAR(32), nullable=False, default="rule_engine")


class ModelUsageEvent(Base):
    """模型用量事件。"""

    __tablename__ = "model_usage_events"
    __table_args__ = (
        UniqueConstraint("tenant_id", "event_key", name="uq_model_usage_events_tenant_event_key"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    project_id: Mapped[int] = mapped_column(BigInteger, ForeignKey("projects.id"), nullable=False, index=True)
    project_model_binding_id: Mapped[Optional[int]] = mapped_column(
        BigInteger, ForeignKey("project_model_bindings.id"), nullable=True, index=True
    )
    source_type: Mapped[str] = mapped_column(VARCHAR(32), nullable=False, default="manual", index=True)
    event_key: Mapped[Optional[str]] = mapped_column(VARCHAR(255), nullable=True)
    input_tokens: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    output_tokens: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    cost_usd: Mapped[float] = mapped_column(DECIMAL(12, 4), nullable=False, default=0.0)
    event_payload: Mapped[dict] = mapped_column(JSONB, nullable=False, default=dict)
    occurred_at: Mapped[Optional[datetime]] = mapped_column(TIMESTAMP, nullable=True, index=True)


class ModelEvalRun(Base):
    """模型效果验证结果。"""

    __tablename__ = "model_eval_runs"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    project_id: Mapped[int] = mapped_column(BigInteger, ForeignKey("projects.id"), nullable=False, index=True)
    charter_id: Mapped[Optional[int]] = mapped_column(BigInteger, ForeignKey("truth_nodes.id"), nullable=True, index=True)
    project_model_binding_id: Mapped[Optional[int]] = mapped_column(
        BigInteger, ForeignKey("project_model_bindings.id"), nullable=True, index=True
    )
    status: Mapped[str] = mapped_column(VARCHAR(32), nullable=False, default="pending", index=True)
    success_criteria: Mapped[dict] = mapped_column(JSONB, nullable=False, default=dict)
    test_cases: Mapped[list[dict]] = mapped_column(JSONB, nullable=False, default=list)
    summary: Mapped[dict] = mapped_column(JSONB, nullable=False, default=dict)
    score: Mapped[Optional[float]] = mapped_column(DECIMAL(5, 2), nullable=True)


class PromptPackage(Base):
    """开发 Prompt 包。"""

    __tablename__ = "prompt_packages"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    project_id: Mapped[int] = mapped_column(BigInteger, ForeignKey("projects.id"), nullable=False, index=True)
    charter_id: Mapped[int] = mapped_column(BigInteger, ForeignKey("truth_nodes.id"), nullable=False, index=True)
    task_id: Mapped[Optional[int]] = mapped_column(BigInteger, ForeignKey("tasks.id"), nullable=True, index=True)
    project_model_binding_id: Mapped[Optional[int]] = mapped_column(
        BigInteger, ForeignKey("project_model_bindings.id"), nullable=True, index=True
    )
    package_kind: Mapped[str] = mapped_column(VARCHAR(32), nullable=False, default="development")
    status: Mapped[str] = mapped_column(VARCHAR(32), nullable=False, default="active", index=True)
    package_text: Mapped[str] = mapped_column(Text, nullable=False)
    package_payload: Mapped[dict] = mapped_column(JSONB, nullable=False, default=dict)
    generated_from: Mapped[dict] = mapped_column(JSONB, nullable=False, default=dict)


class DevWorkspaceBinding(Base):
    """IDE 工作区绑定。"""

    __tablename__ = "dev_workspace_bindings"
    __table_args__ = (
        UniqueConstraint("tenant_id", "workspace_key", name="uq_dev_workspace_bindings_tenant_workspace_key"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    project_id: Mapped[int] = mapped_column(BigInteger, ForeignKey("projects.id"), nullable=False, index=True)
    user_id: Mapped[int] = mapped_column(BigInteger, ForeignKey("users.id"), nullable=False, index=True)
    workspace_key: Mapped[str] = mapped_column(VARCHAR(255), nullable=False)
    repo_root: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    editor_type: Mapped[str] = mapped_column(VARCHAR(32), nullable=False, default="vscode")
    status: Mapped[str] = mapped_column(VARCHAR(32), nullable=False, default="active", index=True)
    last_seen_at: Mapped[Optional[datetime]] = mapped_column(TIMESTAMP, nullable=True)


class RepoBinding(Base):
    """项目仓库绑定。"""

    __tablename__ = "repo_bindings"
    __table_args__ = (
        UniqueConstraint("tenant_id", "provider", "owner", "repo_name", name="uq_repo_bindings_tenant_provider_repo"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    project_id: Mapped[int] = mapped_column(BigInteger, ForeignKey("projects.id"), nullable=False, index=True)
    provider: Mapped[str] = mapped_column(VARCHAR(32), nullable=False, default="github", index=True)
    external_repo_id: Mapped[Optional[str]] = mapped_column(VARCHAR(255), nullable=True)
    owner: Mapped[str] = mapped_column(VARCHAR(255), nullable=False)
    repo_name: Mapped[str] = mapped_column(VARCHAR(255), nullable=False)
    default_branch: Mapped[str] = mapped_column(VARCHAR(255), nullable=False, default="main")
    installation_id: Mapped[Optional[str]] = mapped_column(VARCHAR(255), nullable=True)
    installation_account_login: Mapped[Optional[str]] = mapped_column(VARCHAR(255), nullable=True)
    status: Mapped[str] = mapped_column(VARCHAR(32), nullable=False, default="active", index=True)
    webhook_secret_hint: Mapped[Optional[str]] = mapped_column(VARCHAR(255), nullable=True)


class GitHubAppInstallation(Base):
    """GitHub App 安装记录。"""

    __tablename__ = "github_app_installations"
    __table_args__ = (
        UniqueConstraint("tenant_id", "installation_id", name="uq_github_app_installations_tenant_installation"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    installation_id: Mapped[str] = mapped_column(VARCHAR(255), nullable=False, index=True)
    account_login: Mapped[str] = mapped_column(VARCHAR(255), nullable=False, index=True)
    account_type: Mapped[Optional[str]] = mapped_column(VARCHAR(64), nullable=True)
    app_slug: Mapped[Optional[str]] = mapped_column(VARCHAR(255), nullable=True)
    target_type: Mapped[Optional[str]] = mapped_column(VARCHAR(64), nullable=True)
    status: Mapped[str] = mapped_column(VARCHAR(32), nullable=False, default="active", index=True)
    permissions: Mapped[dict] = mapped_column(JSONB, nullable=False, default=dict)
    events: Mapped[list[str]] = mapped_column(JSONB, nullable=False, default=list)
    app_metadata: Mapped[dict] = mapped_column("metadata", JSONB, nullable=False, default=dict)
    last_synced_at: Mapped[Optional[datetime]] = mapped_column(TIMESTAMP, nullable=True)


class GitHubAppRepository(Base):
    """GitHub App 安装下的仓库记录。"""

    __tablename__ = "github_app_repositories"
    __table_args__ = (
        UniqueConstraint("tenant_id", "installation_id", "external_repo_id", name="uq_github_app_repositories_repo"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    installation_id: Mapped[str] = mapped_column(VARCHAR(255), nullable=False, index=True)
    external_repo_id: Mapped[str] = mapped_column(VARCHAR(255), nullable=False, index=True)
    owner: Mapped[str] = mapped_column(VARCHAR(255), nullable=False, index=True)
    repo_name: Mapped[str] = mapped_column(VARCHAR(255), nullable=False, index=True)
    full_name: Mapped[str] = mapped_column(VARCHAR(255), nullable=False, index=True)
    default_branch: Mapped[Optional[str]] = mapped_column(VARCHAR(255), nullable=True)
    is_private: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    permissions: Mapped[dict] = mapped_column(JSONB, nullable=False, default=dict)
    status: Mapped[str] = mapped_column(VARCHAR(32), nullable=False, default="active", index=True)
    last_synced_at: Mapped[Optional[datetime]] = mapped_column(TIMESTAMP, nullable=True)


class GitHubWebhookDelivery(Base):
    """GitHub webhook 投递审计。"""

    __tablename__ = "github_webhook_deliveries"
    __table_args__ = (
        UniqueConstraint("tenant_id", "delivery_id", name="uq_github_webhook_deliveries_tenant_delivery"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    delivery_id: Mapped[str] = mapped_column(VARCHAR(255), nullable=False, index=True)
    event_type: Mapped[str] = mapped_column(VARCHAR(64), nullable=False, index=True)
    repo_binding_id: Mapped[Optional[int]] = mapped_column(BigInteger, ForeignKey("repo_bindings.id"), nullable=True, index=True)
    project_id: Mapped[Optional[int]] = mapped_column(BigInteger, ForeignKey("projects.id"), nullable=True, index=True)
    installation_id: Mapped[Optional[str]] = mapped_column(VARCHAR(255), nullable=True, index=True)
    status: Mapped[str] = mapped_column(VARCHAR(32), nullable=False, default="received", index=True)
    failure_reason: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    payload: Mapped[dict] = mapped_column(JSONB, nullable=False, default=dict)
    received_at: Mapped[Optional[datetime]] = mapped_column(TIMESTAMP, nullable=True, index=True)


class TaskCodeLink(Base):
    """任务与代码交付对象映射。"""

    __tablename__ = "task_code_links"
    __table_args__ = (
        UniqueConstraint(
            "tenant_id",
            "task_id",
            "repo_binding_id",
            "branch_name",
            name="uq_task_code_links_tenant_task_repo_branch",
        ),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    task_id: Mapped[int] = mapped_column(BigInteger, ForeignKey("tasks.id"), nullable=False, index=True)
    repo_binding_id: Mapped[int] = mapped_column(BigInteger, ForeignKey("repo_bindings.id"), nullable=False, index=True)
    branch_name: Mapped[Optional[str]] = mapped_column(VARCHAR(255), nullable=True)
    pr_number: Mapped[Optional[int]] = mapped_column(Integer, nullable=True, index=True)
    last_commit_sha: Mapped[Optional[str]] = mapped_column(VARCHAR(255), nullable=True)
    status: Mapped[str] = mapped_column(VARCHAR(32), nullable=False, default="linked", index=True)


class ProgressEvent(Base):
    """开发协同进度事件。"""

    __tablename__ = "progress_events"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    task_id: Mapped[int] = mapped_column(BigInteger, ForeignKey("tasks.id"), nullable=False, index=True)
    project_id: Mapped[int] = mapped_column(BigInteger, ForeignKey("projects.id"), nullable=False, index=True)
    user_id: Mapped[Optional[int]] = mapped_column(BigInteger, ForeignKey("users.id"), nullable=True, index=True)
    repo_binding_id: Mapped[Optional[int]] = mapped_column(BigInteger, ForeignKey("repo_bindings.id"), nullable=True, index=True)
    workspace_binding_id: Mapped[Optional[int]] = mapped_column(
        BigInteger, ForeignKey("dev_workspace_bindings.id"), nullable=True, index=True
    )
    event_type: Mapped[str] = mapped_column(VARCHAR(32), nullable=False, index=True)
    source: Mapped[str] = mapped_column(VARCHAR(32), nullable=False, default="manual", index=True)
    derived_task_status: Mapped[Optional[str]] = mapped_column(VARCHAR(32), nullable=True)
    delivery_id: Mapped[Optional[str]] = mapped_column(VARCHAR(255), nullable=True, index=True)
    event_source_id: Mapped[Optional[str]] = mapped_column(VARCHAR(255), nullable=True, index=True)
    summary: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    payload: Mapped[dict] = mapped_column(JSONB, nullable=False, default=dict)
    occurred_at: Mapped[Optional[datetime]] = mapped_column(TIMESTAMP, nullable=True, index=True)


class PMFlowExecutionRuntimeCredential(Base):
    """Execution Bridge 外部运行端回写凭据。"""

    __tablename__ = "pmflow_execution_runtime_credentials"
    __table_args__ = (
        UniqueConstraint("tenant_id", "key_id", name="uq_pmflow_runtime_credentials_tenant_key"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    key_id: Mapped[str] = mapped_column(VARCHAR(80), nullable=False, index=True)
    secret_hash: Mapped[str] = mapped_column(VARCHAR(128), nullable=False)
    project_id: Mapped[int] = mapped_column(BigInteger, ForeignKey("projects.id"), nullable=False, index=True)
    task_id: Mapped[int] = mapped_column(BigInteger, ForeignKey("tasks.id"), nullable=False, index=True)
    prompt_package_id: Mapped[int] = mapped_column(
        BigInteger, ForeignKey("prompt_packages.id"), nullable=False, index=True
    )
    repo_binding_id: Mapped[Optional[int]] = mapped_column(
        BigInteger, ForeignKey("repo_bindings.id"), nullable=True, index=True
    )
    runtime_target: Mapped[str] = mapped_column(VARCHAR(32), nullable=False, index=True)
    allowed_sources: Mapped[list[str]] = mapped_column(JSONB, nullable=False, default=list)
    status: Mapped[str] = mapped_column(VARCHAR(32), nullable=False, default="active", index=True)
    expires_at: Mapped[datetime] = mapped_column(TIMESTAMP, nullable=False, index=True)
    created_by: Mapped[Optional[int]] = mapped_column(BigInteger, ForeignKey("users.id"), nullable=True, index=True)
    last_used_at: Mapped[Optional[datetime]] = mapped_column(TIMESTAMP, nullable=True)
    use_count: Mapped[int] = mapped_column(Integer, nullable=False, default=0)


class DevSession(Base):
    """开发活跃会话。"""

    __tablename__ = "dev_sessions"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    project_id: Mapped[int] = mapped_column(BigInteger, ForeignKey("projects.id"), nullable=False, index=True)
    user_id: Mapped[int] = mapped_column(BigInteger, ForeignKey("users.id"), nullable=False, index=True)
    workspace_binding_id: Mapped[Optional[int]] = mapped_column(
        BigInteger, ForeignKey("dev_workspace_bindings.id"), nullable=True, index=True
    )
    status: Mapped[str] = mapped_column(VARCHAR(32), nullable=False, default="active", index=True)
    started_at: Mapped[Optional[datetime]] = mapped_column(TIMESTAMP, nullable=True)
    heartbeat_at: Mapped[Optional[datetime]] = mapped_column(TIMESTAMP, nullable=True)
    ended_at: Mapped[Optional[datetime]] = mapped_column(TIMESTAMP, nullable=True)
    session_payload: Mapped[dict] = mapped_column(JSONB, nullable=False, default=dict)


class ReleaseValidationRun(Base):
    """发布验证运行记录。"""

    __tablename__ = "release_validation_runs"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    project_id: Mapped[int] = mapped_column(BigInteger, ForeignKey("projects.id"), nullable=False, index=True)
    charter_id: Mapped[Optional[int]] = mapped_column(BigInteger, ForeignKey("truth_nodes.id"), nullable=True, index=True)
    release_node_id: Mapped[Optional[int]] = mapped_column(BigInteger, ForeignKey("truth_nodes.id"), nullable=True, index=True)
    project_model_binding_id: Mapped[Optional[int]] = mapped_column(
        BigInteger, ForeignKey("project_model_bindings.id"), nullable=True, index=True
    )
    status: Mapped[str] = mapped_column(VARCHAR(32), nullable=False, default="pending", index=True)
    score: Mapped[Optional[float]] = mapped_column(DECIMAL(5, 2), nullable=True)
    key_metrics: Mapped[dict] = mapped_column(JSONB, nullable=False, default=dict)
    risk_items: Mapped[list[dict]] = mapped_column(JSONB, nullable=False, default=list)
    rollback_plan: Mapped[dict] = mapped_column(JSONB, nullable=False, default=dict)
    validation_summary: Mapped[dict] = mapped_column(JSONB, nullable=False, default=dict)


class FeedbackIngest(Base):
    """项目反馈摄取。"""

    __tablename__ = "feedback_ingests"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    project_id: Mapped[int] = mapped_column(BigInteger, ForeignKey("projects.id"), nullable=False, index=True)
    charter_id: Mapped[Optional[int]] = mapped_column(BigInteger, ForeignKey("truth_nodes.id"), nullable=True, index=True)
    ingested_by_user_id: Mapped[Optional[int]] = mapped_column(BigInteger, ForeignKey("users.id"), nullable=True)
    source_type: Mapped[str] = mapped_column(VARCHAR(32), nullable=False, default="manual", index=True)
    title: Mapped[Optional[str]] = mapped_column(VARCHAR(255), nullable=True)
    content: Mapped[str] = mapped_column(Text, nullable=False)
    metrics: Mapped[dict] = mapped_column(JSONB, nullable=False, default=dict)
    samples: Mapped[list[dict]] = mapped_column(JSONB, nullable=False, default=list)
    status: Mapped[str] = mapped_column(VARCHAR(32), nullable=False, default="active", index=True)


class IterationRecommendation(Base):
    """基于反馈生成的下一轮建议。"""

    __tablename__ = "iteration_recommendations"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    project_id: Mapped[int] = mapped_column(BigInteger, ForeignKey("projects.id"), nullable=False, index=True)
    charter_id: Mapped[Optional[int]] = mapped_column(BigInteger, ForeignKey("truth_nodes.id"), nullable=True, index=True)
    feedback_ingest_id: Mapped[Optional[int]] = mapped_column(
        BigInteger, ForeignKey("feedback_ingests.id"), nullable=True, index=True
    )
    status: Mapped[str] = mapped_column(VARCHAR(32), nullable=False, default="draft", index=True)
    priority: Mapped[str] = mapped_column(VARCHAR(32), nullable=False, default="medium", index=True)
    recommendation_summary: Mapped[dict] = mapped_column(JSONB, nullable=False, default=dict)
    proposed_charter_patch: Mapped[dict] = mapped_column(JSONB, nullable=False, default=dict)
    proposed_tasks: Mapped[list[dict]] = mapped_column(JSONB, nullable=False, default=list)


class RevenueEvent(Base):
    """项目经营收入事件。"""

    __tablename__ = "revenue_events"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    project_id: Mapped[int] = mapped_column(BigInteger, ForeignKey("projects.id"), nullable=False, index=True)
    source_type: Mapped[str] = mapped_column(VARCHAR(32), nullable=False, default="manual", index=True)
    category: Mapped[str] = mapped_column(VARCHAR(32), nullable=False, default="other", index=True)
    currency: Mapped[str] = mapped_column(VARCHAR(16), nullable=False, default="USD")
    amount_usd: Mapped[float] = mapped_column(DECIMAL(12, 2), nullable=False, default=0.0)
    external_ref: Mapped[Optional[str]] = mapped_column(VARCHAR(255), nullable=True, index=True)
    event_payload: Mapped[dict] = mapped_column(JSONB, nullable=False, default=dict)
    occurred_at: Mapped[Optional[datetime]] = mapped_column(TIMESTAMP, nullable=True, index=True)


class ROISnapshot(Base):
    """ROI 观察快照。"""

    __tablename__ = "roi_snapshots"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    project_id: Mapped[int] = mapped_column(BigInteger, ForeignKey("projects.id"), nullable=False, index=True)
    range_key: Mapped[str] = mapped_column(VARCHAR(32), nullable=False, default="monthly", index=True)
    window_start: Mapped[Optional[datetime]] = mapped_column(TIMESTAMP, nullable=True)
    window_end: Mapped[Optional[datetime]] = mapped_column(TIMESTAMP, nullable=True)
    total_revenue_usd: Mapped[float] = mapped_column(DECIMAL(12, 2), nullable=False, default=0.0)
    total_cost_usd: Mapped[float] = mapped_column(DECIMAL(12, 2), nullable=False, default=0.0)
    roi_ratio: Mapped[Optional[float]] = mapped_column(DECIMAL(12, 4), nullable=True)
    snapshot_payload: Mapped[dict] = mapped_column(JSONB, nullable=False, default=dict)


class ComplianceCheckRun(Base):
    """项目合规检查运行记录。"""

    __tablename__ = "compliance_check_runs"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    project_id: Mapped[int] = mapped_column(BigInteger, ForeignKey("projects.id"), nullable=False, index=True)
    charter_id: Mapped[Optional[int]] = mapped_column(BigInteger, ForeignKey("truth_nodes.id"), nullable=True, index=True)
    release_node_id: Mapped[Optional[int]] = mapped_column(BigInteger, ForeignKey("truth_nodes.id"), nullable=True, index=True)
    project_model_binding_id: Mapped[Optional[int]] = mapped_column(
        BigInteger, ForeignKey("project_model_bindings.id"), nullable=True, index=True
    )
    status: Mapped[str] = mapped_column(VARCHAR(32), nullable=False, default="pending", index=True)
    score: Mapped[Optional[float]] = mapped_column(DECIMAL(5, 2), nullable=True)
    checklist: Mapped[list[dict]] = mapped_column(JSONB, nullable=False, default=list)
    issues: Mapped[list[dict]] = mapped_column(JSONB, nullable=False, default=list)
    summary: Mapped[dict] = mapped_column(JSONB, nullable=False, default=dict)


class EvidencePack(Base):
    """发布与合规证据包。"""

    __tablename__ = "evidence_packs"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    project_id: Mapped[int] = mapped_column(BigInteger, ForeignKey("projects.id"), nullable=False, index=True)
    compliance_check_run_id: Mapped[Optional[int]] = mapped_column(
        BigInteger, ForeignKey("compliance_check_runs.id"), nullable=True, index=True
    )
    package_kind: Mapped[str] = mapped_column(VARCHAR(32), nullable=False, default="compliance_release")
    status: Mapped[str] = mapped_column(VARCHAR(32), nullable=False, default="draft", index=True)
    content: Mapped[dict] = mapped_column(JSONB, nullable=False, default=dict)
    export_payload: Mapped[dict] = mapped_column(JSONB, nullable=False, default=dict)