-ǚjXKddlZddlmZmZddlmZmZddlmZeje Z GddeZ dZ e ZeeZd d Zd d Zy) N) create_enginetext) sessionmakerDeclarativeBase)settingsceZdZdZy)BasezSingle base for ALL models.N)__name__ __module__ __qualname____doc__G/Users/manta/Documents/Projects/TheRoad-I1/backend/app/core/database.pyr r s%rr c>ttjddddS)NT ) pool_pre_ping pool_size max_overflow pool_timeout)rr database_urlrrr get_enginers ..db_apr ssr)bindctddlm}m}m}m}ddlm}m}m}m }m }m } m } m } m} m} m}m}m}m}m}m}m}ddlm}y)u SQLAlchemy Base.metadata 에 model 등록만 — lock-free / idempotent. 호출 시점: - dispatch / 임의 script / runtime entry point 가 ORM 사용 전 호출 가능 - init_db() 내부에서도 호출 - DDL/DML 실행하지 않음 → ALTER TABLE / mass UPDATE lock chain 위험 0 FK relation 해소 (예: episode.project_id → project_registry.id) 필수. Codex iter (root cause): init_db() 가 runtime DDL/DML 함수라 dispatch script 가 호출 시 lock chain 발생 → register_models() 로 분리. r) UserAccountSessionProjectRegistry ProjectMember)Episode EntityCanon EntityAlias RelationFactRelationParticipant SceneStillEntityEpisodeLink ImageAssetProjectSettings WorldGuideWebbookPackageGenerationTrace OperationLogPipelineProgress LLMCallLogCharacterOutlook ScenePlan) ActivityLogN)app.models.catalogrrrr app.models.projectr!r"r#r$r%r&r'r(r)r*r+r,r-r.r/r0r1app.logging.modelsr2)rrrr r!r"r#r$r%r&r'r(r)r*r+r,r-r.r/r0r1r2s rregister_modelsr6s)XW /rc ttjjtdgd}tj 5}t |D]e\}} |jtd|jtd|jt||jg dddy#t$r%}|dk(rtjd|Yd}~_d}~wwxYw#t$r?}|jtjd |||td |d ||d}~wwxYw#1swYyxYw) uStartup-only: model 등록 + Base.metadata.create_all + raw SQL _migrations. runtime entry point (dispatch / script) 에서는 호출 금지 — _migrations 의 ALTER TABLE / mass UPDATE 가 ACCESS EXCLUSIVE lock 시도 → backend uvicorn 의 active transaction 과 lock chain. 운영에서는 alembic 으로 schema 이관 진행 중 (legacy _migrations block 은 점진 deprecate). 호출 진입점: backend FastAPI lifespan startup, test conftest, dev/init. runtime script 는 register_models() 만 사용. T) checkfirst)=aCREATE TABLE IF NOT EXISTS step_run ( id text PRIMARY KEY, project_id text NOT NULL, episode_id text NOT NULL, step_id text NOT NULL, status text NOT NULL DEFAULT 'pending', run_id text, resolved_model text, input_hash text, upstream_revision text, prompt_version text, applicable_count integer, completed_count integer DEFAULT 0, failed_count integer DEFAULT 0, error_message text, result_summary text, started_at text, completed_at text, created_at text NOT NULL, updated_at text NOT NULL, sync_status text, sync_error text, synced_at text, recovery_count integer NOT NULL DEFAULT 0, last_recovery_reason text, owner_host text, owner_pid integer, owner_boot_id text, heartbeat_at timestamptz, cancel_requested_at timestamptz, CONSTRAINT step_run_project_id_episode_id_step_id_key UNIQUE(project_id, episode_id, step_id) )z=ALTER TABLE step_run ADD COLUMN IF NOT EXISTS owner_host TEXTz?ALTER TABLE step_run ADD COLUMN IF NOT EXISTS owner_pid INTEGERz@ALTER TABLE step_run ADD COLUMN IF NOT EXISTS owner_boot_id TEXTzFALTER TABLE step_run ADD COLUMN IF NOT EXISTS heartbeat_at TIMESTAMPTZzMALTER TABLE step_run ADD COLUMN IF NOT EXISTS cancel_requested_at TIMESTAMPTZzZCREATE INDEX IF NOT EXISTS idx_step_run_status_owner_host ON step_run (status, owner_host)aCREATE TABLE IF NOT EXISTS run_cancel_request ( id text PRIMARY KEY, project_id text NOT NULL, episode_id text NOT NULL, scope text NOT NULL DEFAULT 'episode', requested_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP, requested_by text, reason text, cleared_at timestamptz, CONSTRAINT run_cancel_request_scope_key UNIQUE(project_id, episode_id, scope) )zsCREATE INDEX IF NOT EXISTS idx_run_cancel_request_active ON run_cancel_request (project_id, episode_id, cleared_at)zBALTER TABLE project_registry ADD COLUMN IF NOT EXISTS name_en TEXTzAALTER TABLE entity_canon ADD COLUMN IF NOT EXISTS t2i_prompt TEXTzKALTER TABLE project_settings ADD COLUMN IF NOT EXISTS style_rules_json TEXTzHALTER TABLE project_settings ADD COLUMN IF NOT EXISTS world_summary TEXTzHALTER TABLE scene_still ADD COLUMN IF NOT EXISTS dependent_scene_id TEXTzAALTER TABLE scene_still ADD COLUMN IF NOT EXISTS shot_type_1 TEXTzAALTER TABLE scene_still ADD COLUMN IF NOT EXISTS shot_type_2 TEXTaLCREATE TABLE IF NOT EXISTS shot_type ( id SERIAL PRIMARY KEY, name VARCHAR(50) NOT NULL UNIQUE, category VARCHAR(30) NOT NULL, intent VARCHAR(30) NOT NULL, description TEXT NOT NULL, llm_description TEXT NOT NULL, is_active BOOLEAN DEFAULT true, sort_order INTEGER DEFAULT 0 )zAALTER TABLE image_asset ADD COLUMN IF NOT EXISTS theme_label TEXTzbCREATE INDEX IF NOT EXISTS idx_scene_still_project_episode ON scene_still (project_id, episode_id)z]CREATE INDEX IF NOT EXISTS idx_image_asset_entity_type ON image_asset (entity_id, asset_type)zJCREATE INDEX IF NOT EXISTS idx_image_asset_still ON image_asset (still_id)znCREATE INDEX IF NOT EXISTS idx_image_asset_project_episode ON image_asset (project_id, episode_id, asset_type)zfCREATE INDEX IF NOT EXISTS idx_entity_episode_link_canon ON entity_episode_link (canon_id, episode_id)zqCREATE INDEX IF NOT EXISTS idx_pipeline_progress_project ON pipeline_progress (project_id, episode_id, operation)z\CREATE INDEX IF NOT EXISTS idx_llm_call_log_project ON llm_call_log (project_id, created_at)zDALTER TABLE llm_call_log ADD COLUMN IF NOT EXISTS metadata_json TEXTz\CREATE INDEX IF NOT EXISTS idx_activity_log_project ON activity_log (project_id, created_at)zeCREATE INDEX IF NOT EXISTS idx_character_outlook_pair ON character_outlook (character_id, outlook_id)zfCREATE INDEX IF NOT EXISTS idx_image_asset_variant ON image_asset (still_id, asset_type, variant_type)zQALTER TABLE scene_still ADD COLUMN IF NOT EXISTS scene_type TEXT DEFAULT 'normal'zCALTER TABLE scene_still ADD COLUMN IF NOT EXISTS scene_summary TEXTzSALTER TABLE scene_still ADD COLUMN IF NOT EXISTS audio_entity_ids TEXT DEFAULT '[]'z[ALTER TABLE scene_still ADD COLUMN IF NOT EXISTS hallucination_entity_ids TEXT DEFAULT '[]'zAALTER TABLE world_guide ADD COLUMN IF NOT EXISTS source_hash TEXTzCALTER TABLE scene_still ADD COLUMN IF NOT EXISTS shot_index INTEGERzFALTER TABLE scene_still ADD COLUMN IF NOT EXISTS shot_description TEXTzFALTER TABLE scene_still ADD COLUMN IF NOT EXISTS based_on_beat INTEGERzDALTER TABLE scene_still ADD COLUMN IF NOT EXISTS scene_index INTEGERzCALTER TABLE image_asset ADD COLUMN IF NOT EXISTS shot_index INTEGERzQALTER TABLE scene_still ADD COLUMN IF NOT EXISTS is_selected BOOLEAN DEFAULT TRUEzVALTER TABLE scene_still ADD COLUMN IF NOT EXISTS image_generated BOOLEAN DEFAULT FALSEzCUPDATE scene_still SET is_selected = TRUE WHERE is_selected IS NULLzUPDATE scene_still SET image_generated = EXISTS (SELECT 1 FROM image_asset ia WHERE ia.still_id = scene_still.id) WHERE image_generated IS NULL OR image_generated = FALSEz?ALTER TABLE entity_canon ADD COLUMN IF NOT EXISTS short_id TEXTz{CREATE UNIQUE INDEX IF NOT EXISTS uq_entity_canon_short_id ON entity_canon(project_id, short_id) WHERE short_id IS NOT NULLa WITH max_nums AS ( SELECT project_id, entity_type, COALESCE(MAX(CAST(SUBSTRING(short_id FROM '[0-9]+$') AS INTEGER)), 0) as max_num FROM entity_canon WHERE short_id IS NOT NULL GROUP BY project_id, entity_type ), ranked AS ( SELECT ec.id, ec.entity_type, ec.project_id, ROW_NUMBER() OVER (PARTITION BY ec.project_id, ec.entity_type ORDER BY ec.created_at) + COALESCE(mn.max_num, 0) as rn FROM entity_canon ec LEFT JOIN max_nums mn ON ec.project_id = mn.project_id AND ec.entity_type = mn.entity_type WHERE ec.short_id IS NULL ) UPDATE entity_canon SET short_id = CASE ranked.entity_type WHEN 'character' THEN 'C' || LPAD(ranked.rn::text, 2, '0') WHEN 'location' THEN 'L' || LPAD(ranked.rn::text, 2, '0') WHEN 'location_part' THEN 'LP' || LPAD(ranked.rn::text, 2, '0') WHEN 'prop' THEN 'P' || LPAD(ranked.rn::text, 2, '0') WHEN 'outlook' THEN 'O' || LPAD(ranked.rn::text, 2, '0') END FROM ranked WHERE entity_canon.id = ranked.id z_ALTER TABLE entity_episode_link ADD COLUMN IF NOT EXISTS t2i_appearance_count INTEGER DEFAULT 0zLALTER TABLE project_registry ADD COLUMN IF NOT EXISTS planning_doc_text TEXTz>ALTER TABLE step_run ADD COLUMN IF NOT EXISTS sync_status TEXTz=ALTER TABLE step_run ADD COLUMN IF NOT EXISTS sync_error TEXTzrTsS*8$ '  H % ? t ( /,Ur