Merge pull request 'fix: [HOTFIX] 确保生产CORS始终包含saas.xiaoxiajianji.com' (#93) from fix/cors-production-domains into main

之前的修复只在CORS_ORIGINS为空时才添加默认域名，
但生产环境通过环境变量设置了CORS_ORIGINS_RAW，
导致saas子域仍然不在允许列表中。

现在无论CORS_ORIGINS_RAW如何配置，都会确保包含
xiaoxiajianji.com和saas.xiaoxiajianji.com。

alembic/versions/010_add_foreign_key_constraints.py

@@ -0,0 +1,190 @@
+"""add foreign key constraints and missing indexes
+
+Revision ID: 010
+Revises: 009
+Create Date: 2026-06-28
+
+Summary:
+- Add ForeignKey constraints to all 35 logical FK columns across 16 tables
+- ON DELETE CASCADE for parent-child relationships (project → children)
+- ON DELETE SET NULL for optional user references (created_by, assignee, etc.)
+- Add missing indexes on 10 FK columns
+- Alter columns from NOT NULL to nullable where SET NULL is needed
+"""
+
+from alembic import op
+import sqlalchemy as sa
+
+revision = "010"
+down_revision = "009"
+branch_labels = None
+depends_on = None
+
+
+def upgrade() -> None:
+    # ── projects ──────────────────────────────────────────────
+    op.create_foreign_key("fk_projects_owner_user_id", "projects", "users", ["owner_user_id"], ["id"], ondelete="CASCADE")
+
+    # ── asset_libraries ───────────────────────────────────────
+    op.create_foreign_key("fk_asset_libraries_project_id", "asset_libraries", "projects", ["project_id"], ["id"], ondelete="CASCADE")
+
+    # ── assets ────────────────────────────────────────────────
+    # uploaded_by_user_id: nullable=False → nullable=True for SET NULL
+    op.alter_column("assets", "uploaded_by_user_id", existing_type=sa.String(36), nullable=True)
+    op.create_foreign_key("fk_assets_project_id", "assets", "projects", ["project_id"], ["id"], ondelete="CASCADE")
+    op.create_foreign_key("fk_assets_asset_library_id", "assets", "asset_libraries", ["asset_library_id"], ["id"], ondelete="CASCADE")
+    op.create_foreign_key("fk_assets_uploaded_by_user_id", "assets", "users", ["uploaded_by_user_id"], ["id"], ondelete="SET NULL")
+    # Add missing index
+    op.create_index("ix_assets_uploaded_by_user_id", "assets", ["uploaded_by_user_id"])
+
+    # ── project_titles ────────────────────────────────────────
+    # created_by_user_id: nullable=False → nullable=True for SET NULL
+    op.alter_column("project_titles", "created_by_user_id", existing_type=sa.String(36), nullable=True)
+    op.create_foreign_key("fk_project_titles_project_id", "project_titles", "projects", ["project_id"], ["id"], ondelete="CASCADE")
+    op.create_foreign_key("fk_project_titles_created_by_user_id", "project_titles", "users", ["created_by_user_id"], ["id"], ondelete="SET NULL")
+    # Add missing index
+    op.create_index("ix_project_titles_created_by_user_id", "project_titles", ["created_by_user_id"])
+
+    # ── edit_templates ────────────────────────────────────────
+    # created_by_user_id: nullable=False → nullable=True for SET NULL
+    op.alter_column("edit_templates", "created_by_user_id", existing_type=sa.String(32), nullable=True)
+    op.create_foreign_key("fk_edit_templates_project_id", "edit_templates", "projects", ["project_id"], ["id"], ondelete="CASCADE")
+    op.create_foreign_key("fk_edit_templates_created_by_user_id", "edit_templates", "users", ["created_by_user_id"], ["id"], ondelete="SET NULL")
+
+    # ── edit_plans ────────────────────────────────────────────
+    # title_id: nullable=False → nullable=True for SET NULL
+    # created_by_user_id: nullable=False → nullable=True for SET NULL
+    op.alter_column("edit_plans", "title_id", existing_type=sa.String(32), nullable=True)
+    op.alter_column("edit_plans", "created_by_user_id", existing_type=sa.String(32), nullable=True)
+    op.create_foreign_key("fk_edit_plans_project_id", "edit_plans", "projects", ["project_id"], ["id"], ondelete="CASCADE")
+    op.create_foreign_key("fk_edit_plans_template_id", "edit_plans", "edit_templates", ["template_id"], ["id"], ondelete="CASCADE")
+    op.create_foreign_key("fk_edit_plans_asset_library_id", "edit_plans", "asset_libraries", ["asset_library_id"], ["id"], ondelete="CASCADE")
+    op.create_foreign_key("fk_edit_plans_title_id", "edit_plans", "project_titles", ["title_id"], ["id"], ondelete="SET NULL")
+    op.create_foreign_key("fk_edit_plans_created_by_user_id", "edit_plans", "users", ["created_by_user_id"], ["id"], ondelete="SET NULL")
+
+    # ── edit_plan_clips ───────────────────────────────────────
+    op.create_foreign_key("fk_edit_plan_clips_edit_plan_id", "edit_plan_clips", "edit_plans", ["edit_plan_id"], ["id"], ondelete="CASCADE")
+    op.create_foreign_key("fk_edit_plan_clips_asset_id", "edit_plan_clips", "assets", ["asset_id"], ["id"], ondelete="CASCADE")
+
+    # ── ingest_jobs ───────────────────────────────────────────
+    # result_asset_id: nullable=False → nullable=True for SET NULL
+    op.alter_column("ingest_jobs", "result_asset_id", existing_type=sa.String(32), nullable=True)
+    op.create_foreign_key("fk_ingest_jobs_project_id", "ingest_jobs", "projects", ["project_id"], ["id"], ondelete="CASCADE")
+    op.create_foreign_key("fk_ingest_jobs_library_id", "ingest_jobs", "asset_libraries", ["library_id"], ["id"], ondelete="CASCADE")
+    op.create_foreign_key("fk_ingest_jobs_result_asset_id", "ingest_jobs", "assets", ["result_asset_id"], ["id"], ondelete="SET NULL")
+
+    # ── classification_jobs ───────────────────────────────────
+    op.create_foreign_key("fk_classification_jobs_project_id", "classification_jobs", "projects", ["project_id"], ["id"], ondelete="CASCADE")
+    op.create_foreign_key("fk_classification_jobs_asset_id", "classification_jobs", "assets", ["asset_id"], ["id"], ondelete="CASCADE")
+
+    # ── generation_tasks ──────────────────────────────────────
+    # edit_plan_id: nullable=False → nullable=True for SET NULL
+    # created_by_user_id: nullable=False → nullable=True for SET NULL
+    op.alter_column("generation_tasks", "edit_plan_id", existing_type=sa.String(32), nullable=True)
+    op.alter_column("generation_tasks", "created_by_user_id", existing_type=sa.String(32), nullable=True)
+    op.create_foreign_key("fk_generation_tasks_project_id", "generation_tasks", "projects", ["project_id"], ["id"], ondelete="CASCADE")
+    op.create_foreign_key("fk_generation_tasks_asset_library_id", "generation_tasks", "asset_libraries", ["asset_library_id"], ["id"], ondelete="CASCADE")
+    op.create_foreign_key("fk_generation_tasks_edit_plan_id", "generation_tasks", "edit_plans", ["edit_plan_id"], ["id"], ondelete="SET NULL")
+    op.create_foreign_key("fk_generation_tasks_created_by_user_id", "generation_tasks", "users", ["created_by_user_id"], ["id"], ondelete="SET NULL")
+
+    # ── generated_videos ──────────────────────────────────────
+    op.create_foreign_key("fk_generated_videos_project_id", "generated_videos", "projects", ["project_id"], ["id"], ondelete="CASCADE")
+    op.create_foreign_key("fk_generated_videos_generation_task_id", "generated_videos", "generation_tasks", ["generation_task_id"], ["id"], ondelete="CASCADE")
+    op.create_foreign_key("fk_generated_videos_duplicate_of", "generated_videos", "generated_videos", ["duplicate_of"], ["id"], ondelete="SET NULL")
+
+    # ── tasks ─────────────────────────────────────────────────
+    # parent_task_id: nullable=False → nullable=True for SET NULL
+    # assignee_user_id: nullable=False → nullable=True for SET NULL
+    op.alter_column("tasks", "parent_task_id", existing_type=sa.String(32), nullable=True)
+    op.alter_column("tasks", "assignee_user_id", existing_type=sa.String(32), nullable=True)
+    op.create_foreign_key("fk_tasks_project_id", "tasks", "projects", ["project_id"], ["id"], ondelete="CASCADE")
+    op.create_foreign_key("fk_tasks_parent_task_id", "tasks", "tasks", ["parent_task_id"], ["id"], ondelete="SET NULL")
+    op.create_foreign_key("fk_tasks_assignee_user_id", "tasks", "users", ["assignee_user_id"], ["id"], ondelete="SET NULL")
+
+    # ── milestones ────────────────────────────────────────────
+    op.create_foreign_key("fk_milestones_project_id", "milestones", "projects", ["project_id"], ["id"], ondelete="CASCADE")
+
+    # ── task_issues ───────────────────────────────────────────
+    # created_by_user_id: nullable=False → nullable=True for SET NULL
+    op.alter_column("task_issues", "created_by_user_id", existing_type=sa.String(32), nullable=True)
+    op.create_foreign_key("fk_task_issues_task_id", "task_issues", "tasks", ["task_id"], ["id"], ondelete="CASCADE")
+    op.create_foreign_key("fk_task_issues_project_id", "task_issues", "projects", ["project_id"], ["id"], ondelete="CASCADE")
+    op.create_foreign_key("fk_task_issues_created_by_user_id", "task_issues", "users", ["created_by_user_id"], ["id"], ondelete="SET NULL")
+
+
+def downgrade() -> None:
+    # ── task_issues ───────────────────────────────────────────
+    op.drop_constraint("fk_task_issues_created_by_user_id", "task_issues", type_="foreignkey")
+    op.drop_constraint("fk_task_issues_project_id", "task_issues", type_="foreignkey")
+    op.drop_constraint("fk_task_issues_task_id", "task_issues", type_="foreignkey")
+    op.alter_column("task_issues", "created_by_user_id", existing_type=sa.String(32), nullable=False)
+
+    # ── milestones ────────────────────────────────────────────
+    op.drop_constraint("fk_milestones_project_id", "milestones", type_="foreignkey")
+
+    # ── tasks ─────────────────────────────────────────────────
+    op.drop_constraint("fk_tasks_assignee_user_id", "tasks", type_="foreignkey")
+    op.drop_constraint("fk_tasks_parent_task_id", "tasks", type_="foreignkey")
+    op.drop_constraint("fk_tasks_project_id", "tasks", type_="foreignkey")
+    op.alter_column("tasks", "assignee_user_id", existing_type=sa.String(32), nullable=False)
+    op.alter_column("tasks", "parent_task_id", existing_type=sa.String(32), nullable=False)
+
+    # ── generated_videos ──────────────────────────────────────
+    op.drop_constraint("fk_generated_videos_duplicate_of", "generated_videos", type_="foreignkey")
+    op.drop_constraint("fk_generated_videos_generation_task_id", "generated_videos", type_="foreignkey")
+    op.drop_constraint("fk_generated_videos_project_id", "generated_videos", type_="foreignkey")
+
+    # ── generation_tasks ──────────────────────────────────────
+    op.drop_constraint("fk_generation_tasks_created_by_user_id", "generation_tasks", type_="foreignkey")
+    op.drop_constraint("fk_generation_tasks_edit_plan_id", "generation_tasks", type_="foreignkey")
+    op.drop_constraint("fk_generation_tasks_asset_library_id", "generation_tasks", type_="foreignkey")
+    op.drop_constraint("fk_generation_tasks_project_id", "generation_tasks", type_="foreignkey")
+    op.alter_column("generation_tasks", "created_by_user_id", existing_type=sa.String(32), nullable=False)
+    op.alter_column("generation_tasks", "edit_plan_id", existing_type=sa.String(32), nullable=False)
+
+    # ── classification_jobs ───────────────────────────────────
+    op.drop_constraint("fk_classification_jobs_asset_id", "classification_jobs", type_="foreignkey")
+    op.drop_constraint("fk_classification_jobs_project_id", "classification_jobs", type_="foreignkey")
+
+    # ── ingest_jobs ───────────────────────────────────────────
+    op.drop_constraint("fk_ingest_jobs_result_asset_id", "ingest_jobs", type_="foreignkey")
+    op.drop_constraint("fk_ingest_jobs_library_id", "ingest_jobs", type_="foreignkey")
+    op.drop_constraint("fk_ingest_jobs_project_id", "ingest_jobs", type_="foreignkey")
+    op.alter_column("ingest_jobs", "result_asset_id", existing_type=sa.String(32), nullable=False)
+
+    # ── edit_plan_clips ───────────────────────────────────────
+    op.drop_constraint("fk_edit_plan_clips_asset_id", "edit_plan_clips", type_="foreignkey")
+    op.drop_constraint("fk_edit_plan_clips_edit_plan_id", "edit_plan_clips", type_="foreignkey")
+
+    # ── edit_plans ────────────────────────────────────────────
+    op.drop_constraint("fk_edit_plans_created_by_user_id", "edit_plans", type_="foreignkey")
+    op.drop_constraint("fk_edit_plans_title_id", "edit_plans", type_="foreignkey")
+    op.drop_constraint("fk_edit_plans_asset_library_id", "edit_plans", type_="foreignkey")
+    op.drop_constraint("fk_edit_plans_template_id", "edit_plans", type_="foreignkey")
+    op.drop_constraint("fk_edit_plans_project_id", "edit_plans", type_="foreignkey")
+    op.alter_column("edit_plans", "created_by_user_id", existing_type=sa.String(32), nullable=False)
+    op.alter_column("edit_plans", "title_id", existing_type=sa.String(32), nullable=False)
+
+    # ── edit_templates ────────────────────────────────────────
+    op.drop_constraint("fk_edit_templates_created_by_user_id", "edit_templates", type_="foreignkey")
+    op.drop_constraint("fk_edit_templates_project_id", "edit_templates", type_="foreignkey")
+    op.alter_column("edit_templates", "created_by_user_id", existing_type=sa.String(32), nullable=False)
+
+    # ── project_titles ────────────────────────────────────────
+    op.drop_index("ix_project_titles_created_by_user_id", table_name="project_titles")
+    op.drop_constraint("fk_project_titles_created_by_user_id", "project_titles", type_="foreignkey")
+    op.drop_constraint("fk_project_titles_project_id", "project_titles", type_="foreignkey")
+    op.alter_column("project_titles", "created_by_user_id", existing_type=sa.String(36), nullable=False)
+
+    # ── assets ────────────────────────────────────────────────
+    op.drop_index("ix_assets_uploaded_by_user_id", table_name="assets")
+    op.drop_constraint("fk_assets_uploaded_by_user_id", "assets", type_="foreignkey")
+    op.drop_constraint("fk_assets_asset_library_id", "assets", type_="foreignkey")
+    op.drop_constraint("fk_assets_project_id", "assets", type_="foreignkey")
+    op.alter_column("assets", "uploaded_by_user_id", existing_type=sa.String(36), nullable=False)
+
+    # ── asset_libraries ───────────────────────────────────────
+    op.drop_constraint("fk_asset_libraries_project_id", "asset_libraries", type_="foreignkey")
+
+    # ── projects ──────────────────────────────────────────────
+    op.drop_constraint("fk_projects_owner_user_id", "projects", type_="foreignkey")

apps/api/app/api/routes/titles.py

@@ -51,7 +51,7 @@ def _to_response(item) -> TitleLibraryItemResponse:
 
 
 def _get_user_plan(user_id: str, user_repository: UserRepository) -> str:
-    user = user_repository.get_by_id(user_id)
+    user = user_repository.find_by_id(user_id)
     if user is None:
         return "free"
     return getattr(user, "subscription_plan", "free") or "free"

apps/api/app/api/routes/voices.py

@@ -55,7 +55,7 @@ def _to_response(item) -> VoiceLibraryItemResponse:
 
 
 def _get_user_plan(user_id: str, user_repository: UserRepository) -> str:
-    user = user_repository.get_by_id(user_id)
+    user = user_repository.find_by_id(user_id)
     if user is None:
         return "free"
     return getattr(user, "subscription_plan", "free") or "free"

apps/api/app/schemas/ingest_job.py

@@ -14,4 +14,4 @@ class IngestJobResponse(BaseModel):
     storage_key: str
     status: str
     error_message: str
-    result_asset_id: str
+    result_asset_id: str | None

apps/api/main.py

@@ -38,10 +38,11 @@ if settings.DEBUG:
     allow_origins = settings.CORS_ORIGINS  # Allow localhost in debug mode
 else:
     # In production, filter out any wildcard "*" origins
-    allow_origins = [origin for origin in settings.CORS_ORIGINS if origin != "*"]
-    if not allow_origins:
-        # Default to production domain if no valid origins configured
-        allow_origins = ["https://xiaoxiajianji.com"]
+    allow_origins = list({origin for origin in settings.CORS_ORIGINS if origin != "*"})
+    # Always ensure production domains are included
+    for domain in ("https://xiaoxiajianji.com", "https://saas.xiaoxiajianji.com"):
+        if domain not in allow_origins:
+            allow_origins.append(domain)
 
 app.add_middleware(
     CORSMiddleware,

infra/docker/compose.yml

@@ -72,11 +72,11 @@ services:
     # =========================================
     # 资源限制建议（生产环境建议启用）
     # =========================================
+    mem_limit: 2g
     deploy:
       resources:
         limits:
           cpus: '2.0'
-          memory: 2G
         reservations:
           cpus: '0.5'
           memory: 512M
@@ -112,11 +112,11 @@ services:
     # 资源限制建议（生产环境建议启用）
     # =========================================
     # 注意: Worker 需要处理视频，建议分配更多资源
+    mem_limit: 2g
     deploy:
       resources:
         limits:
           cpus: '2.0'
-          memory: 2G
         reservations:
           cpus: '0.5'
           memory: 1G

packages/adapters/sqlalchemy_impl/project_repository.py

@@ -54,12 +54,13 @@ class SQLAlchemyProjectRepository:
 
     def find_accessible_projects(self, user_id: str) -> list[Project]:
         """查找用户可访问的所有项目（自己拥有的 + 被共享的）"""
-        from sqlalchemy import or_
-        
+        from sqlalchemy import or_, cast
+        from sqlalchemy.dialects.postgresql import JSONB
+
         models = self.session.query(ProjectModel).filter(
             or_(
                 ProjectModel.owner_user_id == user_id,
-                ProjectModel.shared_users.contains([user_id])
+                cast(ProjectModel.shared_users, JSONB).contains([user_id])
             )
         ).all()
         return [self._to_entity(model) for model in models]