# 03. Database 및 Persistence 감사

> 원본 코드/프롬프트/기존 문서를 수정하지 않고 생성한 read-only 감사 문서입니다.

## 현재 DB 요약

현재 활성 DB는 PostgreSQL이다. SQLite 관련 파일은 legacy/historical 영역으로 판단된다.

확인된 실제 DB 정보는 다음과 같다.

| 항목 | 값 |
|---|---|
| DB 종류 | PostgreSQL |
| public tables | 26 |
| `image_asset` rows | 851 |
| `step_run` rows | 3655 |
| `prompt_template` rows | 114 |
| `scene_still` rows | 497 |
| `entity_canon` rows | 197 |
| `episode` rows | 2 |
| `project_registry` rows | 2 |

## 실제 public tables

| table | row count |
|---|---:|
| `activity_log` | 70 |
| `alembic_version` | 1 |
| `character_outlook` | 26 |
| `entity_alias` | 0 |
| `entity_canon` | 197 |
| `entity_episode_link` | 117 |
| `episode` | 2 |
| `generation_trace` | 0 |
| `image_asset` | 851 |
| `llm_call_log` | 810 |
| `operation_log` | 4 |
| `pipeline_progress` | 25 |
| `project_member` | 2 |
| `project_registry` | 2 |
| `project_settings` | 0 |
| `prompt_template` | 114 |
| `relation_fact` | 9 |
| `relation_participant` | 18 |
| `scene_plan` | 0 |
| `scene_still` | 497 |
| `session` | 45 |
| `shot_type` | 30 |
| `step_run` | 3655 |
| `user_account` | 2 |
| `webbook_package` | 0 |
| `world_guide` | 23 |

## Schema source가 여러 개임

```mermaid
flowchart TB
    Models[SQLAlchemy models] --> CreateAll[Base.metadata.create_all]
    StartupSQL[startup raw DDL in database.py] --> DB[(PostgreSQL)]
    CreateAll --> DB
    Alembic[Alembic versions 001-004] --> DB
    RawSQL[backend/migrations/*.sql] --> DB
    Manual[manual/imported prompt_template?] --> DB
```

현재 schema source는 하나가 아니다.

| Source | 위치 | 문제 |
|---|---|---|
| SQLAlchemy models | `backend/app/models/*` | `create_all()`은 기존 table alter를 하지 않음 |
| startup raw SQL | `backend/app/core/database.py` | 실패를 삼키고 rollback만 수행 |
| Alembic | `backend/alembic/versions/*.py` | baseline schema가 아니고 incremental 일부만 존재 |
| standalone SQL | `backend/migrations/*.sql` | Alembic과 별도 실행 체계 |
| actual DB | PostgreSQL | 코드 source와 drift 가능 |

가장 위험한 부분은 startup migration loop가 예외를 raise/log 하지 않고 넘어갈 수 있다는 점이다. 이 경우 앱은 뜨지만 컬럼/인덱스/타입 변경이 빠진 상태로 운영된다.

## ORM 모델 map

| 모델/테이블 | 위치 | 설명 |
|---|---|---|
| `UserAccount` | `models/catalog.py` | 사용자 |
| `Session` | `models/catalog.py` | cookie session |
| `ProjectRegistry` | `models/catalog.py` | 프로젝트 registry |
| `ProjectMember` | `models/catalog.py` | project ACL |
| `Episode` | `models/project.py` | episode metadata/source path/status |
| `EntityCanon` | `models/project.py` | canonical entity |
| `CharacterOutlook` | `models/project.py` | character outlook |
| `EntityAlias` | `models/project.py` | alias |
| `RelationFact`, `RelationParticipant` | `models/project.py` | relation facts |
| `SceneStill` | `models/project.py` | shot/still records |
| `EntityEpisodeLink` | `models/project.py` | entity-episode link |
| `ImageAsset` | `models/project.py` | reference/scene/floor/background assets |
| `ProjectSettings` | `models/project.py` | project settings |
| `WorldGuide` | `models/project.py` | world guide |
| `WebbookPackage` | `models/project.py` | export package |
| `OperationLog` | `models/project.py` | operation log |
| `PipelineProgress` | `models/project.py` | progress |
| `LLMCallLog` | `logging/models.py` | LLM call log |
| `GenerationTrace` | `models/project.py` | image/LLM trace |
| `ScenePlan` | `models/project.py` | scene plan |

주의: `step_run`, `shot_type`, `prompt_template`는 현재 DB에 존재하지만, 주요 ORM model source로 일관되게 관리되지 않는다.

## `image_asset` path invariant

`ImageAsset.file_path`는 `ImagePathType`을 사용한다.

```mermaid
flowchart LR
    Producer[service/step writes path] --> Type[ImagePathType bind]
    Type --> Relative[DB stores relative path if under project root]
    Relative --> DB[(image_asset.file_path)]
    DB --> Read[ORM read]
    Read --> Absolute[resolved absolute path]
    Absolute --> Consumer[API/services]
```

장점은 DB에 absolute local path가 고착되는 문제를 줄인다는 점이다.

하지만 invariant가 적용되는 범위가 좁다.

| 저장 위치 | invariant |
|---|---|
| `image_asset.file_path` | `ImagePathType` 적용 |
| checkpoint `manifest.json` | 별도 JSON, 경로 형식 강제 없음 |
| background/floor render manifest path | step별 custom |
| `Episode.source_path` | plain text, absolute 저장 가능 |
| planning doc path | service/router별 처리 |
| export/import path | 별도 serializer |

따라서 "DB row는 정상인데 checkpoint path가 cwd 의존" 또는 반대 상황이 가능하다.

## Checkpoint primary 구조

Pipeline output의 primary store는 DB가 아니라 filesystem checkpoint다.

```mermaid
flowchart TB
    Step[Step execution] --> Manifest[projects/{pid}/checkpoints/episodes/{eid}/{step}/manifest.json]
    Manifest --> Sync[checkpoint_sync/orchestrator.py]
    Sync --> EntityDB[(entity tables)]
    Sync --> SceneDB[(scene_still)]
    Sync --> OutlookDB[(character_outlook)]
    Sync --> EpisodeDB[(episode projection)]
    Step --> StepRun[(step_run)]
```

이 구조에서 DB는 projection이며, checkpoint가 실제 step output이다.

장점:

| 장점 | 설명 |
|---|---|
| LLM 결과 보존 | raw JSON/metadata를 파일로 보관 가능 |
| 재개 가능 | resume 시 manifest를 직접 읽을 수 있음 |
| DB schema 압박 감소 | step별 output shape가 달라도 저장 가능 |

리스크:

| 리스크 | 설명 |
|---|---|
| projection drift | checkpoint와 DB가 다를 수 있음 |
| path drift | JSON path는 DB type invariant를 타지 않음 |
| partial output | partial checkpoint가 downstream에 소비될 수 있음 |
| corruption | JSON 파일 손상 시 sync/read가 실패 |
| cleanup 어려움 | episode/project delete 시 checkpoint/assets orphan 가능 |

## Migration 현황

Alembic version files:

| file | 내용 |
|---|---|
| `001_add_indexes.py` | 일부 index 추가 |
| `002_phase5_image_asset_variants.py` | `image_asset` variant fields, initial short variant label |
| `003_resume_integrity.py` | resume/sync 관련 fields, `variant_label` 32 |
| `004_variant_label_extend.py` | `variant_label` 255 확장 |

Standalone raw SQL:

| file | 내용 |
|---|---|
| `backend/migrations/004_t2i_composer.sql` | t2i composer 계열 |
| `backend/migrations/005_variation_pipeline.sql` | variation pipeline 계열 |

Startup raw SQL:

| 위치 | 내용 |
|---|---|
| `core/database.py` | `step_run`, `shot_type`, index, column/type 추가 등 |

## `variant_label` 사고의 일반화

이전에 확인된 `variant_label` 길이 문제는 단일 컬럼 사이즈 문제가 아니라 schema governance 문제의 증상이다.

```mermaid
flowchart LR
    Code[code writes longer semantic labels] --> Insert[DB insert]
    Insert --> Limit[String length]
    Limit --> Error[DataError]
    Error --> Swallow[silent except or migration swallow]
    Swallow --> FakeComplete[step completed but DB row missing]
```

해당 사고에서 드러난 일반 문제는 다음과 같다.

| 일반 문제 | 설명 |
|---|---|
| schema source 분산 | 어떤 migration이 최종 truth인지 애매 |
| writer/DB contract 없음 | variant label 길이/형식이 model/prompt/DB에서 함께 정의되지 않음 |
| error swallowing | insert 실패가 step failure로 올라가지 않으면 completed 오판 |
| exit verify 부족 | expected asset count와 DB/file count mismatch를 강제하지 못함 |

현재 실제 DB의 `image_asset.variant_label`은 `varchar(255)`로 확인되지만, 같은 패턴은 다른 컬럼/JSON path에도 반복될 수 있다.

## Deletion/cleanup risk

| 동작 | 현재 경향 | 리스크 |
|---|---|---|
| project create | directory 먼저 생성 후 DB commit | commit 실패 시 orphan directory |
| project delete | soft delete 중심 | files/child rows 장기 잔존 |
| episode create | PDF 저장 후 extraction/DB commit | validation/commit 실패 시 orphan file |
| episode delete | source file과 일부 DB rows 삭제 | checkpoint/assets/step_run/log/trace orphan 가능 |
| image thumbnail serving | GET에서 thumbnail 생성 가능 | read endpoint가 storage mutation |

## 권장 개선 방향

| 우선순위 | 개선 |
|---|---|
| P0/P1 | Alembic baseline을 만들고 startup DDL을 제거하거나 fail-fast로 전환 |
| P1 | startup migration exception을 logging + raise 또는 explicit degraded status로 처리 |
| P1 | `step_run`, `prompt_template`, `shot_type` 등 raw table도 schema source를 명확히 지정 |
| P1 | checkpoint JSON path schema를 정의하고 path normalization helper를 강제 |
| P1 | asset step exit verify를 DB/files/checkpoint count로 강제 |
| P2 | project/episode create/delete에 filesystem transaction/cleanup policy 추가 |
| P2 | export/import schema를 최신 ORM fields와 동기화 |
| P2 | read endpoint side effect를 background/explicit thumbnail generation으로 분리 |