"""Projects domain - SQLAlchemy models.""" from __future__ import annotations from datetime import datetime from typing import Optional from uuid import UUID as PyUUID, uuid4 from sqlalchemy import BigInteger, Boolean, ForeignKey, Integer, String, Text from sqlalchemy.dialects.postgresql import JSONB, UUID from sqlalchemy.orm import Mapped, mapped_column from sqlalchemy.sql import func from sqlalchemy.types import DateTime from src.core.database import Base import sqlalchemy as sa # PostgreSQL enums - created in migration, use create_type=False PROJECT_TYPE_ENUM = sa.Enum( "react_app", "nuxt_app", "html", "shopify_theme", "wordpress_theme", "shopify_app", "ai_assist_shopify", "ai_assist_wordpress", name="p2hs_project_type", create_type=False, ) PROJECT_STATUS_ENUM = sa.Enum( "creating", "ready", "error", "archived", name="p2hs_project_status", create_type=False, ) MVP_PROJECT_TYPES = ("react_app", "nuxt_app", "html") SHOPIFY_APP_PROJECT_TYPE = "shopify_app" AI_ASSIST_SHOPIFY_PROJECT_TYPE = "ai_assist_shopify" AI_ASSIST_WORDPRESS_PROJECT_TYPE = "ai_assist_wordpress" AI_ASSIST_PROJECT_TYPES = (AI_ASSIST_SHOPIFY_PROJECT_TYPE, AI_ASSIST_WORDPRESS_PROJECT_TYPE) ALLOWED_PROJECT_TYPES = (*MVP_PROJECT_TYPES, SHOPIFY_APP_PROJECT_TYPE, *AI_ASSIST_PROJECT_TYPES) PROJECT_SOURCE_KIND_GENERATED = "generated" PROJECT_SOURCE_KIND_GITHUB_IMPORT = "github_import" PROJECT_SOURCE_KIND_GITLAB_IMPORT = "gitlab_import" PROJECT_SOURCE_KIND_BITBUCKET_IMPORT = "bitbucket_import" class ProjectImportSourceCatalog(Base): """Core-owned catalog for existing-project import source presentation.""" __tablename__ = "project_import_source_catalog" provider: Mapped[str] = mapped_column(String(40), primary_key=True) label: Mapped[str] = mapped_column(String(120), nullable=False) icon: Mapped[Optional[str]] = mapped_column(String(120), nullable=True) sort_order: Mapped[int] = mapped_column(Integer, nullable=False, server_default="100") visible: Mapped[bool] = mapped_column(Boolean, nullable=False, server_default=sa.true()) metadata_json: Mapped[dict] = mapped_column(JSONB, nullable=False, server_default="{}") created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now()) updated_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), server_default=func.now(), onupdate=func.now() ) class Project(Base): """Project entity.""" __tablename__ = "projects" # repo: attached dynamically by repository when loading with repo id: Mapped[PyUUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid4) workspace_id: Mapped[PyUUID] = mapped_column(UUID(as_uuid=True), ForeignKey("workspaces.id", ondelete="CASCADE"), nullable=False) # Immutable public ref for hostnames (paired with workspace.tenant_ref). project_ref: Mapped[Optional[str]] = mapped_column(String(16), unique=True, nullable=True) primary_hosting_provider: Mapped[Optional[str]] = mapped_column(String(40), nullable=True) # Open migration. Not a second serving provider. Ordinary deploys keep using # the recorded source until cutover_verified is set by a successful activation. hosting_migration: Mapped[Optional[dict]] = mapped_column(JSONB, nullable=True) live_hostname: Mapped[Optional[str]] = mapped_column(Text, nullable=True) preview_hostname: Mapped[Optional[str]] = mapped_column(Text, nullable=True) slug: Mapped[str] = mapped_column(String(100), nullable=False) # Immutable GitHub repo name segment (org/{github_repo_name}); unique, not derived from title. github_repo_name: Mapped[Optional[str]] = mapped_column(String(100), nullable=True, unique=True) name: Mapped[str] = mapped_column(String(255), nullable=False) description: Mapped[Optional[str]] = mapped_column(Text, nullable=True) type: Mapped[str] = mapped_column(PROJECT_TYPE_ENUM, nullable=False) status: Mapped[str] = mapped_column(PROJECT_STATUS_ENUM, server_default="creating") created_by: Mapped[Optional[PyUUID]] = mapped_column(UUID(as_uuid=True), ForeignKey("users.id"), nullable=True) created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now()) updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now()) deleted_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True) active_revision_id: Mapped[Optional[PyUUID]] = mapped_column( UUID(as_uuid=True), ForeignKey("project_revisions.id", ondelete="SET NULL"), nullable=True ) active_preview_id: Mapped[Optional[PyUUID]] = mapped_column( UUID(as_uuid=True), ForeignKey("preview_environments.id", ondelete="SET NULL"), nullable=True ) active_session_id: Mapped[Optional[PyUUID]] = mapped_column( UUID(as_uuid=True), ForeignKey("prompt_sessions.id", ondelete="SET NULL"), nullable=True ) boilerplate_version: Mapped[Optional[str]] = mapped_column(String(60), nullable=True) discovery_json: Mapped[Optional[dict]] = mapped_column(JSONB, nullable=True) # Version of platform-managed files last pushed to this repo (p2hs.platform.json, # .cursor/instructions.md, etc.). NULL = pre-versioning / never synced. platform_files_version: Mapped[Optional[str]] = mapped_column(String(20), nullable=True) # Set when the project was created from a template (soft FK — no hard FK to avoid circular deps). template_id: Mapped[Optional[PyUUID]] = mapped_column(UUID(as_uuid=True), nullable=True) template_ref: Mapped[Optional[str]] = mapped_column(String(63), nullable=True) template_github_repo: Mapped[Optional[str]] = mapped_column(String(255), nullable=True) template_starter_branch: Mapped[Optional[str]] = mapped_column(String(100), nullable=True) template_starter_sha: Mapped[Optional[str]] = mapped_column(String(40), nullable=True) # Source metadata for imported customer repos. The P2HS managed repo remains # authoritative for codegen/preview; this records where the clean import came from. source_kind: Mapped[str] = mapped_column(String(40), nullable=False, server_default=PROJECT_SOURCE_KIND_GENERATED) source_workspace_user_github_installation_id: Mapped[Optional[PyUUID]] = mapped_column(UUID(as_uuid=True), nullable=True) source_workspace_user_gitlab_connection_id: Mapped[Optional[PyUUID]] = mapped_column(UUID(as_uuid=True), nullable=True) source_workspace_user_bitbucket_connection_id: Mapped[Optional[PyUUID]] = mapped_column(UUID(as_uuid=True), nullable=True) source_workspace_user_azure_connection_id: Mapped[Optional[PyUUID]] = mapped_column(UUID(as_uuid=True), nullable=True) source_repo_full_name: Mapped[Optional[str]] = mapped_column(String(255), nullable=True) source_branch: Mapped[Optional[str]] = mapped_column(String(100), nullable=True) source_root_dir: Mapped[Optional[str]] = mapped_column(String(255), nullable=True) source_import_env: Mapped[bool] = mapped_column(sa.Boolean, nullable=False, server_default=sa.true()) source_imported_commit_sha: Mapped[Optional[str]] = mapped_column(String(40), nullable=True) source_r2_key: Mapped[Optional[str]] = mapped_column(sa.Text, nullable=True) source_s3_object_url: Mapped[Optional[str]] = mapped_column(sa.Text, nullable=True) source_s3_access_key_enc: Mapped[Optional[str]] = mapped_column(sa.Text, nullable=True) source_s3_secret_key_enc: Mapped[Optional[str]] = mapped_column(sa.Text, nullable=True) source_s3_endpoint_url: Mapped[Optional[str]] = mapped_column(sa.Text, nullable=True) source_s3_region: Mapped[Optional[str]] = mapped_column(String(64), nullable=True) # Per-step setup observability (cf secrets, pages ensure, pushes, …); merged by worker. setup_steps: Mapped[dict] = mapped_column(JSONB, server_default="{}") # Durable facts extracted from user prompts/discovery that must survive regeneration # (admin path, persistence requirement, auth mode, ...) -- see # src/domain/projects/runtime_invariants.py for the shape and extraction rules. runtime_invariants: Mapped[dict] = mapped_column(JSONB, server_default="{}") class ProjectJobOutbox(Base): """Durable pending intent for ARQ jobs (committed with project creation).""" __tablename__ = "project_job_outbox" id: Mapped[PyUUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid4) project_id: Mapped[PyUUID] = mapped_column( UUID(as_uuid=True), ForeignKey("projects.id", ondelete="CASCADE"), nullable=False ) job_kind: Mapped[str] = mapped_column(String(40), nullable=False) payload_json: Mapped[dict] = mapped_column(JSONB, server_default="{}") status: Mapped[str] = mapped_column(String(20), nullable=False, server_default="pending") attempts: Mapped[int] = mapped_column(sa.Integer, nullable=False, server_default="0") last_error: Mapped[Optional[str]] = mapped_column(Text, nullable=True) publishing_started_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True) created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now()) published_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True) class ProjectImportSession(Base): """Reserved import handshake before a real project row is created.""" __tablename__ = "project_import_sessions" project_id: Mapped[PyUUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid4) workspace_id: Mapped[PyUUID] = mapped_column( UUID(as_uuid=True), ForeignKey("workspaces.id", ondelete="CASCADE"), nullable=False ) created_by: Mapped[PyUUID] = mapped_column(UUID(as_uuid=True), ForeignKey("users.id"), nullable=False) provider: Mapped[str] = mapped_column(String(40), nullable=False, server_default="github") status: Mapped[str] = mapped_column(String(30), nullable=False, server_default="started") github_installation_id: Mapped[Optional[int]] = mapped_column(BigInteger, nullable=True) workspace_user_github_installation_id: Mapped[Optional[PyUUID]] = mapped_column(UUID(as_uuid=True), nullable=True) workspace_user_gitlab_connection_id: Mapped[Optional[PyUUID]] = mapped_column(UUID(as_uuid=True), nullable=True) workspace_user_bitbucket_connection_id: Mapped[Optional[PyUUID]] = mapped_column(UUID(as_uuid=True), nullable=True) workspace_user_azure_connection_id: Mapped[Optional[PyUUID]] = mapped_column(UUID(as_uuid=True), nullable=True) source_repo_full_name: Mapped[Optional[str]] = mapped_column(String(255), nullable=True) source_branch: Mapped[Optional[str]] = mapped_column(String(100), nullable=True) source_root_dir: Mapped[Optional[str]] = mapped_column(String(255), nullable=True) created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now()) updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now()) class ProjectRepo(Base): """Project GitHub repo link.""" __tablename__ = "project_repos" id: Mapped[PyUUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid4) project_id: Mapped[PyUUID] = mapped_column(UUID(as_uuid=True), ForeignKey("projects.id", ondelete="CASCADE"), unique=True) github_repo_id: Mapped[Optional[int]] = mapped_column(BigInteger, nullable=True) repo_full_name: Mapped[str] = mapped_column(String(255), nullable=False) clone_url: Mapped[Optional[str]] = mapped_column(Text, nullable=True) html_url: Mapped[Optional[str]] = mapped_column(Text, nullable=True) default_branch: Mapped[str] = mapped_column(String(100), server_default="main") preview_branch: Mapped[str] = mapped_column(String(100), server_default="preview") # Durable setup progress for idempotent worker retries (see project_setup_pipeline). setup_stage: Mapped[Optional[str]] = mapped_column(String(50), nullable=True) initial_commit_sha: Mapped[Optional[str]] = mapped_column(String(40), nullable=True) setup_preview_id: Mapped[Optional[PyUUID]] = mapped_column(UUID(as_uuid=True), nullable=True) webhook_secret_hash: Mapped[Optional[str]] = mapped_column(String(255), nullable=True) created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now()) class ProjectSettings(Base): """Project settings.""" __tablename__ = "project_settings" project_id: Mapped[PyUUID] = mapped_column(UUID(as_uuid=True), ForeignKey("projects.id", ondelete="CASCADE"), primary_key=True) code_apply_policy: Mapped[Optional[str]] = mapped_column(String(20), nullable=True) build_command: Mapped[Optional[str]] = mapped_column(Text, nullable=True) output_directory: Mapped[Optional[str]] = mapped_column(String(255), nullable=True) settings: Mapped[dict] = mapped_column(JSONB, server_default="{}") updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now()) class ProjectEnvVar(Base): """Project environment variable (encrypted at rest). visibility: "secret" → CF Worker Secret binding, never in repo files. "public" → CF plain env var, also written to .env.example for GitHub repo. """ __tablename__ = "project_env_vars" id: Mapped[PyUUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid4) project_id: Mapped[PyUUID] = mapped_column(UUID(as_uuid=True), ForeignKey("projects.id", ondelete="CASCADE"), nullable=False) key: Mapped[str] = mapped_column(String(255), nullable=False) encrypted_value: Mapped[str] = mapped_column(Text, nullable=False) environment: Mapped[str] = mapped_column(String(50), server_default="all") visibility: Mapped[str] = mapped_column(String(20), server_default="secret", nullable=False) created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now()) updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now()) class ProjectCloudSchemaSnapshot(Base): """Last-known Cloud record-engine catalog for codegen. Cloud owns entities and rows. Core stores a projection so a failed or empty schema fetch cannot look like a new database. """ __tablename__ = "project_cloud_schema_snapshots" project_id: Mapped[PyUUID] = mapped_column( UUID(as_uuid=True), ForeignKey("projects.id", ondelete="CASCADE"), primary_key=True ) entities_json: Mapped[list] = mapped_column(JSONB, nullable=False, server_default="[]") updated_at: Mapped[datetime] = mapped_column( DateTime(timezone=True), server_default=func.now(), onupdate=func.now() )