9.4 KiB

Raw Permalink Blame History

📋 설계 문서: get_videos 엔드포인트 업데이트

1. 요구사항 요약

기능적 요구사항

#	요구사항	현재 상태	변경
1	current_user 소유 프로젝트의 영상만 반환	구현됨	유지
2	status='completed', is_deleted=False 필터	구현됨	유지
3	동일 task_id 중 created_at 최신 영상 1개만 반환	미구현 (전체 반환)	신규
4	created_at DESC 정렬	구현됨	유지
5	DEBUG 쿼리 제거	6개 DEBUG 쿼리 존재	삭제

비기능적 요구사항

기존 페이지네이션 인터페이스(PaginatedResponse, PaginationParams) 유지
기존 응답 스키마(VideoListItem) 유지
SQLAlchemy 비동기 + PostgreSQL 호환

2. 설계 개요

현재 문제점

DEBUG 쿼리 6개 (lines 80~142): 전체 Video 수, completed 수, is_deleted 수, 전체 Project 수, 사용자 Project 수, 사용자 completed Video 수를 매 요청마다 조회 → 불필요한 DB 부하
task_id 중복 반환: 동일 task_id에 재생성된 영상이 여러 개 존재할 때 모두 반환

설계 방향

DEBUG 쿼리 6개 전면 삭제
서브쿼리 방식으로 task_id별 최신 영상 필터링
쿼리를 count 쿼리 + 데이터 쿼리 2개로 정리 (기존 구조 유지)

3. API 설계

엔드포인트

변경 없음 — 기존 인터페이스 유지

GET /archive/videos/?page=1&page_size=10

Method: GET
Auth: Bearer Token (get_current_user)
Query Params: page (int, default=1), page_size (int, default=10, max=100)
Response: PaginatedResponse[VideoListItem]

description 업데이트 내용

- 본인이 소유한 프로젝트의 영상만 반환됩니다.
- status가 'completed'인 영상만 반환됩니다.
- 동일 task_id의 영상이 여러 개인 경우, 가장 최근에 생성된 영상만 반환됩니다.
- created_at 기준 내림차순 정렬됩니다.

4. 데이터 모델

기존 모델 (변경 없음)

Video (app/video/models.py)

컬럼	타입	용도
id	Integer (PK, autoincrement)	고유 식별자
project_id	Integer (FK → project.id)	프로젝트 연결
task_id	String(36)	작업 식별자 (중복 가능)
status	String(50)	처리 상태
result_movie_url	String(2048)	영상 URL
is_deleted	Boolean	소프트 삭제
created_at	DateTime	생성 일시

Project (app/home/models.py)

컬럼	타입	용도
id	Integer (PK)	고유 식별자
user_uuid	String(36, FK → user.user_uuid)	소유자
store_name	String	업체명
region	String	지역명
is_deleted	Boolean	소프트 삭제

인덱스 활용

idx_video_task_id: task_id GROUP BY에 활용
idx_video_project_id: JOIN 조건에 활용
idx_video_is_deleted: WHERE 필터에 활용
idx_project_user_uuid: 사용자 소유 필터에 활용

5. 서비스 레이어

쿼리 설계 (핵심)

현재 아키텍처에서 get_videos는 라우터에서 직접 쿼리를 실행하고 있음 (별도 서비스 레이어 없음). 이 패턴을 유지하되, 쿼리 로직만 수정한다.

5.1 서브쿼리: task_id별 최신 Video ID 추출

from sqlalchemy import func, select

# 서브쿼리: 조건을 만족하는 영상 중, task_id별 MAX(id)를 추출
# (id는 autoincrement이므로 created_at 최신과 동일)
latest_video_ids = (
    select(func.max(Video.id).label("latest_id"))
    .join(Project, Video.project_id == Project.id)
    .where(
        Project.user_uuid == current_user.user_uuid,
        Video.status == "completed",
        Video.is_deleted == False,
        Project.is_deleted == False,
    )
    .group_by(Video.task_id)
    .subquery()
)

설계 근거: Video.id는 autoincrement이므로 나중에 생성된 레코드가 항상 더 큰 id를 가진다. 따라서 MAX(id)는 created_at이 가장 최신인 레코드와 일치한다. Window Function(ROW_NUMBER) 대비 쿼리가 단순하고 성능이 우수하다.

5.2 COUNT 쿼리 (페이지네이션용)

count_query = (
    select(func.count(Video.id))
    .where(Video.id.in_(select(latest_video_ids.c.latest_id)))
)

5.3 데이터 쿼리

data_query = (
    select(Video, Project)
    .join(Project, Video.project_id == Project.id)
    .where(Video.id.in_(select(latest_video_ids.c.latest_id)))
    .order_by(Video.created_at.desc())
    .offset(offset)
    .limit(pagination.page_size)
)

5.4 전체 쿼리 흐름

1. latest_video_ids (서브쿼리)
   → Video JOIN Project
   → WHERE: user_uuid, status, is_deleted 필터
   → GROUP BY task_id → MAX(id)

2. count_query
   → WHERE Video.id IN (latest_video_ids)
   → scalar count

3. data_query
   → Video JOIN Project
   → WHERE Video.id IN (latest_video_ids)
   → ORDER BY created_at DESC
   → OFFSET/LIMIT

생성되는 SQL (참고)

-- 서브쿼리
SELECT MAX(v.id) AS latest_id
FROM video v
JOIN project p ON v.project_id = p.id
WHERE p.user_uuid = :user_uuid
  AND v.status = 'completed'
  AND v.is_deleted = FALSE
  AND p.is_deleted = FALSE
GROUP BY v.task_id;

-- 데이터 쿼리
SELECT v.*, p.*
FROM video v
JOIN project p ON v.project_id = p.id
WHERE v.id IN (위 서브쿼리)
ORDER BY v.created_at DESC
OFFSET :offset LIMIT :limit;

6. 스키마

변경 없음 — 기존 스키마 유지

VideoListItem (app/video/schemas/video_schema.py)

class VideoListItem(BaseModel):
    video_id: int
    store_name: Optional[str]
    region: Optional[str]
    task_id: str
    result_movie_url: Optional[str]
    created_at: Optional[datetime]

PaginatedResponse[VideoListItem] (app/utils/pagination.py)

{
    "items": [VideoListItem, ...],
    "total": int,
    "page": int,
    "page_size": int,
    "total_pages": int,
    "has_next": bool,
    "has_prev": bool
}

7. 파일 구조

파일	작업	설명
app/archive/api/routers/v1/archive.py	수정	get_videos 함수 리팩토링

수정하지 않는 파일:

app/video/models.py (변경 없음)
app/video/schemas/video_schema.py (변경 없음)
app/utils/pagination.py (변경 없음)
app/dependencies/pagination.py (변경 없음)
app/home/models.py (변경 없음)

8. 구현 순서

개발 에이전트(/develop)가 따라야 할 순서:

Step 1: get_videos 함수 수정

DEBUG 쿼리 삭제 (lines 80~142)
- 전체 Video 수 조회 삭제
- completed 상태 Video 수 조회 삭제
- is_deleted=False Video 수 조회 삭제
- 전체 Project 수/상세 조회 삭제
- 현재 사용자 소유 Project 수 조회 삭제
- 현재 사용자 completed Video 수 조회 삭제
서브쿼리 추가: task_id별 MAX(id) 추출
- base_conditions를 서브쿼리의 WHERE절에 적용
COUNT 쿼리 수정: Video.id IN (서브쿼리) 조건 적용
데이터 쿼리 수정: Video.id IN (서브쿼리) + ORDER BY + OFFSET/LIMIT
엔드포인트 description 업데이트: "동일 task_id의 가장 최근 영상만 반환" 문구 추가, 기존 "재생성된 영상 포함 모든 영상이 반환됩니다" 문구 삭제

Step 2: 로깅 정리

기존 DEBUG 로그 삭제
핵심 로그만 유지: START, SUCCESS, EXCEPTION

9. 설계 검수 결과

검수 체크리스트

기존 프로젝트 패턴과 일관성: 기존 라우터 직접 쿼리 패턴 유지, PaginatedResponse.create() 활용
비동기 처리 설계: async/await + AsyncSession 유지
N+1 쿼리 문제: JOIN으로 한 번에 조회, 서브쿼리는 IN절로 단일 쿼리 실행
트랜잭션 경계: 읽기 전용 쿼리이므로 트랜잭션 불필요 (기존과 동일)
예외 처리 전략: 기존 try/except + HTTPException 500 패턴 유지
확장성: 서브쿼리 방식은 추가 필터 조건 확장 용이
직관적 구조: 서브쿼리(최신 ID 추출) → COUNT → DATA 3단계로 명확
SOLID 준수: 단일 책임(영상 목록 조회), 기존 인터페이스 유지(OCP)

성능 고려사항

서브쿼리 GROUP BY task_id는 idx_video_task_id 인덱스 활용
Video.id IN (서브쿼리)는 PK 인덱스로 빠른 조회
기존 대비 DEBUG 쿼리 6개 삭제로 DB 요청 횟수: 8회 → 2회

대안 검토

방식	장점	단점	채택
MAX(id) 서브쿼리	단순, 빠름, DB 무관	id 순서 = 시간 순서 전제	채택
ROW_NUMBER() 윈도우 함수	created_at 직접 기준	쿼리 복잡, 서브쿼리 래핑 필요	미채택
DISTINCT ON (PostgreSQL)	PostgreSQL 최적화	DB 종속, 정렬 제약	미채택

MAX(id) 서브쿼리 채택 근거: Video.id는 autoincrement이므로 MAX(id)가 created_at 최신 레코드와 일치. 쿼리가 가장 단순하고 모든 RDBMS에서 동작.

다음 단계

설계 검토 완료 후 /develop 명령으로 구현을 진행합니다.

9.4 KiB Raw Permalink Blame History