# Database Schema Guide

이 문서는 현재 제공된 PostgreSQL DDL을 기준으로 애플리케이션 개발자가 참고할 수 있도록 정리한 문서입니다.

## 접속 및 스키마 전제

- 데이터베이스: `sbscax`
- 스키마: `core`
- 애플리케이션 접속 사용자: `db_appuser`
- 시간 기준: KST (`Asia/Seoul`, UTC+09:00)
- DB 위치: Google Cloud SQL (private IP)
- 개발/운영 접근 경로: 해당 Cloud SQL 인스턴스에 접근 가능한 bastion VM을 통해 연결

로컬 개발자의 상세 접속 절차는 [Bastion 경유 Cloud SQL 접속 가이드](cloud-sql-bastion-connection.md)를 참고합니다.

애플리케이션 SQL은 `core` 스키마를 명시적으로 사용해야 합니다. 연결 시 다음과 같이 `search_path`를 설정하거나 모든 객체명을 `core.<object>` 형식으로 작성합니다.

```sql
SET search_path TO core;
```

애플리케이션에는 `db_appuser`의 자격 증명을 코드나 저장소에 하드코딩하지 않습니다. Cloud SQL의 private 네트워크 경로와 비밀값 주입 방식은 인프라 설정을 따릅니다.

## 도메인 개요

처리 흐름은 다음과 같습니다.

```text
asset
  └─ request ──┬─ job ──┬─ task
               │        ├─ slice ── result_analysis
               │        │        ├─ result_clip
               │        │        └─ result_highlight
               │        └─ job status history
               └─ request status history
```

- `tb_asset`: 입력 영상과 자막 등 원본 자산
- `tb_request`: 자산에 대해 사용자가 요청한 분석 작업
- `tb_job`: 요청을 수행하기 위한 순차/계층형 작업
- `tb_task`: job 내부에서 worker가 처리하는 단위 작업
- `tb_slice`: 영상 구간 단위 결과
- `tb_result_*`: 분석/clip/highlight 결과 payload
- `tb_*_status_history`: request, job, task 상태 변경 감사 이력
- `tb_asset_log`: asset 변경 전후 데이터 감사 로그

## Enum

PostgreSQL enum 타입은 `core` 스키마에 생성하는 것을 전제로 합니다.

| 타입 | 허용 값 | 용도 |
| --- | --- | --- |
| `request_status` | `QUEUED`, `RUNNING`, `SUCCEEDED`, `FAILED`, `CANCELED` | request 상태 |
| `job_type` | `SLICE`, `ANALYZE`, `CLIP`, `HIGHLIGHT` | job 종류 |
| `job_status` | `WAITING`, `QUEUED`, `RUNNING`, `SUCCEEDED`, `FAILED`, `CANCELED` | job 상태 |
| `task_status` | `QUEUED`, `RUNNING`, `SUCCEEDED`, `FAILED`, `CANCELED` | task 상태 |
| `analysis_type` | `CLIP`, `HIGHLIGHT`, `PREP` | 요청 분석 종류 |
| `content_type` | `DRAMA`, `ENTERTAINMENT`, `SPORTS_GOLF` | 콘텐츠 종류 |

## 테이블 사전

모든 테이블의 ID는 `BIGINT GENERATED BY DEFAULT AS IDENTITY` 기본 키입니다. 시간 컬럼은 `TIMESTAMPTZ`이며, 애플리케이션에서 시간을 남기거나 표시할 때는 KST (`Asia/Seoul`, UTC+09:00)를 기준으로 합니다.

시간값을 저장할 때는 timezone 정보가 포함된 값을 사용하고, `TIMESTAMPTZ` 컬럼에 애플리케이션 로컬 시간을 timezone 없이 저장하지 않습니다. API 응답, 로그, 상태 이력의 시간 표현도 기본적으로 KST 기준으로 통일합니다.

### Asset

#### `core.tb_asset`

| 컬럼 | 타입 | Null | 설명 |
| --- | --- | --- | --- |
| `asset_id` | `BIGINT` | N | 자산 ID |
| `video_url` | `TEXT` | N | 영상 위치 |
| `subtitle_url` | `TEXT` | Y | 자막 위치 |
| `video_duration_sec` | `INTEGER` | Y | 영상 길이(초) |
| `metadata` | `JSONB` | Y | 선택 입력값 |
| `created_at`, `updated_at` | `TIMESTAMPTZ` | N | 생성/수정 시각 |
| `deleted_at` | `TIMESTAMPTZ` | Y | soft delete 시각 |

`deleted_at IS NULL`인 asset을 기본 조회 대상으로 사용합니다. DDL에는 URL 형식, duration 범위, JSON 구조 제약이 없으므로 애플리케이션 validation이 필요합니다.

#### `core.tb_asset_log`

| 컬럼 | 타입 | Null | 설명 |
| --- | --- | --- | --- |
| `log_id` | `BIGINT` | N | 로그 ID |
| `asset_id` | `BIGINT` | N | `tb_asset.asset_id` FK |
| `request_method` | `TEXT` | N | 변경 요청 method |
| `before_data`, `after_data` | `JSONB` | N | 변경 전/후 데이터 |
| `requested_at` | `TIMESTAMPTZ` | N | 요청 시각 |
| `request_by` | `TEXT` | Y | 요청자 |

### Request / Job / Task

#### `core.tb_request`

`tb_asset`에 대한 분석 요청입니다.

| 컬럼 | 타입 | Null | 설명 |
| --- | --- | --- | --- |
| `request_id` | `BIGINT` | N | 요청 ID |
| `asset_id` | `BIGINT` | N | `tb_asset.asset_id` FK |
| `content_type` | `content_type` | N | 콘텐츠 종류 |
| `analysis_type` | `analysis_type` | N | 분석 종류 |
| `request_status` | `request_status` | N | 요청 상태 |
| `request_params` | `JSONB` | N | 선택 입력값을 담는 요청 파라미터 |
| `requested_by` | `TEXT` | Y | 요청자 |
| `requested_at` | `TIMESTAMPTZ` | N | 요청 시각 |
| `started_at`, `completed_at` | `TIMESTAMPTZ` | Y | 처리 시작/완료 시각 |
| `message` | `TEXT` | Y | 상태/오류 메시지 |

#### `core.tb_job`

요청을 처리하는 작업입니다. `request_id`로 request에 속하고, `parent_job_id`로 job 간 계층을 표현할 수 있습니다. `retry_of_job_id`는 컬럼은 있으나 DDL상 FK가 없습니다.

| 컬럼 | 타입 | Null | 설명 |
| --- | --- | --- | --- |
| `job_id` | `BIGINT` | N | job ID |
| `request_id` | `BIGINT` | N | request FK |
| `parent_job_id` | `BIGINT` | Y | 부모 job FK(Self-reference) |
| `attempt_no` | `SMALLINT` | Y | 시도 번호, 기본값 `1` |
| `retry_of_job_id` | `BIGINT` | Y | 재시도 대상 job ID |
| `job_type` | `job_type` | N | job 종류 |
| `job_order` | `SMALLINT` | N | 처리 순서(DDL 주석: 1 slice, 2 analyze, 3 extract) |
| `job_status` | `job_status` | N | job 상태 |
| `output_data` | `JSONB` | Y | job 출력 |
| `created_at`, `started_at`, `completed_at` | `TIMESTAMPTZ` | 각 정의 따름 | 생명주기 시각 |
| `message` | `TEXT` | Y | 상태/오류 메시지 |

주의: `job_type` enum에는 `CLIP`, `HIGHLIGHT`가 있지만 컬럼 주석의 `EXTRACT`와 job order 주석은 서로 일치하지 않습니다. 구현 시 enum 정의를 기준으로 하고, 이 정책은 별도 합의 후 DDL/문서를 함께 변경해야 합니다.

#### `core.tb_task`

job 내부 worker 처리 단위입니다. `task_no`는 job 내부 순번이며 `worker_id`는 필수입니다. 입력은 `input_data`, 처리는 `task_status`, 결과는 `output_data`에 저장합니다.

`retry_count` 기본값은 `0`이며, 상태 재처리 정책과 worker lease/중복 실행 방지는 애플리케이션에서 명시적으로 구현해야 합니다.

### Slice / Result

#### `core.tb_slice`

job이 생성한 영상 구간입니다. `slice_index`로 job 내부 순서를 표현하고 `start_time`, `end_time`은 현재 `TEXT` 타입이므로 저장 형식을 애플리케이션 전체에서 일관되게 유지해야 합니다.

#### `core.tb_result_analysis`

`job_id`와 `slice_id`를 기준으로 분석 `payload`를 저장합니다. 두 FK 모두 정의되어 있습니다.

#### `core.tb_result_clip`

`job_id`와 선택적 `slice_id`에 대한 clip 결과 `payload`를 저장합니다.

#### `core.tb_result_highlight`

`job_id`와 선택적 `slice_id`에 대한 highlight 결과 `payload`를 저장합니다.

결과 테이블의 JSONB schema는 DDL에 고정되어 있지 않으므로 API 계약 또는 별도 payload 문서에서 버전을 관리하는 것을 권장합니다.

## 상태 이력

### `core.tb_request_status_history`

| 컬럼 | 타입 | Null | 설명 |
| --- | --- | --- | --- |
| `history_id` | `BIGINT` | N | 이력 ID |
| `request_id` | `BIGINT` | N | request FK |
| `from_status`, `to_status` | `request_status` | N | 변경 전/후 상태 |
| `change_source` | `TEXT` | N | DDL 주석상 `WORKER` |
| `changed_by` | `TEXT` | Y | worker ID |
| `message` | `TEXT` | Y | 변경 메시지 |
| `metadata` | `JSONB` | Y | 부가 정보 |
| `changed_at` | `TIMESTAMPTZ` | N | 변경 시각 |

### `core.tb_job_status_history`

`job_id`, `from_status`, `to_status`, `output_data`, `change_source`, `changed_by`, `message`, `metadata`, `changed_at`를 저장합니다. `job_id`, 상태 전후 값, `change_source`, `changed_at`은 필수이고 `output_data`는 선택입니다.

### `core.tb_task_status_history`

`task_id`, `from_status`, `to_status`, `change_source`, `changed_by`, `message`, `metadata`, `changed_at`를 저장합니다. `task_id`, 상태 전후 값, `change_source`, `changed_by`, `changed_at`은 필수입니다.

상태 컬럼을 변경할 때 해당 history row를 같은 트랜잭션에서 기록하는 것을 기본 원칙으로 합니다. 이력 테이블에는 상태 전이 유효성이나 시간 순서 제약이 없으므로, 허용 전이와 동시성 제어는 서비스 계층에서 보장해야 합니다.

## FK 및 삭제 정책

주요 FK는 `asset → request → job → task`와 `job → slice → result` 구조입니다. 모든 FK의 `ON DELETE`는 `NO ACTION`이므로 부모 row를 삭제해도 자식 row가 자동 삭제되지 않습니다. 운영 데이터는 물리 삭제보다 `tb_asset.deleted_at` 기반 soft delete를 우선 검토합니다.

## 개발 규칙

1. DB 객체는 `core` 스키마를 사용하고, migration/SQL에서는 스키마를 생략하지 않습니다.
2. 애플리케이션 DB 연결 사용자는 `db_appuser`로 통일합니다.
3. 상태 변경은 현재 상태 검증, 대상 row 갱신, status history 기록을 하나의 트랜잭션으로 처리합니다.
4. `JSONB` 필드는 임의의 키를 추가하기 전에 API 계약과 호환성 영향을 확인합니다.
5. ID는 애플리케이션에서 생성하지 않고 identity 기본값을 사용합니다.
6. FK가 있는 데이터를 삭제하거나 재처리할 때 `NO ACTION` 정책과 감사 이력 보존을 고려합니다.
7. private Cloud SQL에 직접 접근하지 말고 승인된 네트워크 경로(bastion VM 등)를 사용합니다.

## DDL 검토 메모

원본 DDL을 실제 `core` 스키마에 적용할 때는 enum/table/FK 객체에 `core.`를 붙이거나 실행 세션의 `search_path`를 `core`로 설정해야 합니다. 또한 PostgreSQL은 따옴표가 없는 타입명을 소문자로 해석하므로, `CONTENT_TYPE`, `REQUEST_STATUS` 등은 실제 생성된 소문자 enum 타입과 일치하는지 migration 환경에서 확인합니다.

현재 DDL에는 인덱스, 상태 전이 제약, 시간/JSONB validation, `retry_of_job_id` FK가 정의되어 있지 않습니다. 조회 패턴과 운영 요구사항이 확정되면 별도 migration으로 추가하며, 기존 애플리케이션 계약을 깨지 않도록 검토 후 적용합니다.