# Database Migrations

> **실제 SQL 파일 위치**: [`backend/src/main/resources/db/migration/`](../../backend/src/main/resources/db/migration/)
>
> Spring Boot Flyway 표준 위치를 따르므로 SQL 파일은 백엔드 모듈 안에 있습니다.
> Spring Boot 기동 시 Flyway가 자동으로 적용합니다.

## DB 정보
- **DB Name**: `kcgaidb`
- **User**: `kcg-app`
- **Schema**: `kcg`
- **Host**: `211.208.115.83:5432`
- **현재 버전**: v022 (2026-04-09)

---

## 마이그레이션 히스토리 (V001~V022)

Flyway 마이그레이션은 **증분 방식** — 각 파일은 이전 버전에 대한 변경(ALTER/INSERT/CREATE)만 포함합니다.
V001이 처음 테이블을 만들고, 이후 파일들이 컬럼 추가·시드 INSERT·신규 테이블 생성 등을 수행합니다.

### 인증/권한/감사 (V001~V007)

| 파일 | 내용 |
|---|---|
| `V001__auth_init.sql` | auth_user, auth_org, auth_role, auth_user_role, auth_login_hist, auth_setting |
| `V002__perm_tree.sql` | auth_perm_tree (권한 트리) + auth_perm (권한 매트릭스) |
| `V003__perm_seed.sql` | 역할 5종 시드 + 트리 노드 47개 + 역할별 권한 매트릭스 |
| `V004__access_logs.sql` | auth_audit_log + auth_access_log |
| `V005__parent_workflow.sql` | gear_group_parent_resolution, review_log, exclusions, label_sessions |
| `V006__demo_accounts.sql` | 데모 계정 5종 (admin/operator/analyst/field/viewer) |
| `V007__perm_tree_label_align.sql` | 트리 노드 명칭 일치 조정 |

### 마스터 데이터 (V008~V011)

| 파일 | 내용 |
|---|---|
| `V008__code_master.sql` | code_master (계층형 72코드: 위반유형/이벤트/단속 등) |
| `V009__gear_type_master.sql` | gear_type_master 6종 (어구 분류 룰 + 합법성 기준) |
| `V010__zone_polygon_master.sql` | zone_polygon_master (PostGIS, 8개 해역 시드) |
| `V011__vessel_permit_patrol.sql` | vessel_permit_master(9척) + patrol_ship_master(6척) + fleet_companies(2개) |

### Prediction 분석 (V012~V015)

| 파일 | 내용 |
|---|---|
| `V012__prediction_events_stats.sql` | vessel_analysis_results(파티션) + prediction_events + alerts + stats(시/일/월) + KPI + risk_grid + label_input |
| `V013__enforcement_operations.sql` | enforcement_records + plans + patrol_assignments + ai_model_versions + metrics |
| `V014__fleet_prediction_tables.sql` | fleet_vessels/tracking_snapshot + gear_identity_log + correlation_scores/raw_metrics + correlation_param_models + group_polygon_snapshots + gear_group_episodes/episode_snapshots + parent_candidate_snapshots + label_tracking_cycles + system_config |
| `V015__fix_numeric_precision.sql` | NUMERIC 정밀도 확대 (점수/비율 컬럼) |

### 모선 워크플로우 확장 + 기능 추가 (V016~V019)

| 파일 | 내용 |
|---|---|
| `V016__parent_workflow_columns.sql` | gear_group_parent_resolution 확장 (confidence, decision_source, episode_id 등) |
| `V017__role_color_hex.sql` | auth_role.color_hex 컬럼 추가 |
| `V018__prediction_event_features.sql` | prediction_events.features JSONB 컬럼 추가 |
| `V019__llm_ops_perm.sql` | ai-operations:llm-ops 권한 트리 노드 + ADMIN 권한 |

### 메뉴 DB SSOT (V020~V022)

| 파일 | 내용 |
|---|---|
| `V020__menu_config.sql` | menu_config 테이블 생성 + 시드 (V021에서 통합 후 폐기) |
| `V021__menu_into_perm_tree.sql` | auth_perm_tree에 메뉴 컬럼 추가 (url_path, label_key, component_key, nav_group, nav_sub_group, nav_sort) + 공유 리소스 분리 (statistics:reports, admin:data-hub, admin:notices) + menu_config DROP |
| `V022__perm_tree_i18n_labels.sql` | auth_perm_tree.labels JSONB 추가 — DB가 i18n SSOT (`{"ko":"...", "en":"..."}`) |

---

## 테이블 목록 (49개, flyway_schema_history 포함)

### 인증/권한 (8 테이블)

| 테이블 | PK | 설명 | 주요 컬럼 |
|---|---|---|---|
| `auth_user` | user_id (UUID) | 사용자 | user_acnt(UQ), pswd_hash, user_nm, rnkp_nm, email, org_sn(FK→auth_org), user_stts_cd, fail_cnt, auth_provider |
| `auth_org` | org_sn (BIGSERIAL) | 조직 | org_nm, org_abbr_nm, org_tp_cd, upper_org_sn(FK 자기참조) |
| `auth_role` | role_sn (BIGSERIAL) | 역할 | role_cd(UQ), role_nm, role_dc, dflt_yn, builtin_yn, color_hex |
| `auth_user_role` | (user_id, role_sn) | 사용자-역할 매핑 | granted_at, granted_by |
| `auth_perm_tree` | rsrc_cd (VARCHAR 100) | 권한 트리 + **메뉴 SSOT** | parent_cd(FK 자기참조), rsrc_nm, icon, rsrc_level, sort_ord, **url_path, label_key, component_key, nav_group, nav_sub_group, nav_sort, labels(JSONB)** |
| `auth_perm` | perm_sn (BIGSERIAL) | 권한 매트릭스 | role_sn(FK→auth_role), rsrc_cd(FK→auth_perm_tree), oper_cd, grant_yn, UQ(role_sn,rsrc_cd,oper_cd) |
| `auth_setting` | setting_key (VARCHAR 50) | 시스템 설정 | setting_val(JSONB) |
| `auth_login_hist` | hist_sn (BIGSERIAL) | 로그인 이력 | user_id, user_acnt, login_dtm, login_ip, result, fail_reason, auth_provider |

### 감사 (2 테이블)

| 테이블 | PK | 설명 | 주요 컬럼 |
|---|---|---|---|
| `auth_audit_log` | audit_sn (BIGSERIAL) | 감사 로그 | user_id, action_cd, resource_type, resource_id, detail(JSONB), ip_address, result |
| `auth_access_log` | access_sn (BIGSERIAL) | API 접근 이력 | user_id, http_method, request_path, status_code, duration_ms, ip_address |

### 모선 워크플로우 (7 테이블)

| 테이블 | PK | 설명 |
|---|---|---|
| `gear_group_parent_resolution` | id (BIGSERIAL), UQ(group_key, sub_cluster_id) | 모선 확정/거부 결과 (status, selected_parent_mmsi, confidence, decision_source, scores, episode_id) |
| `gear_group_parent_review_log` | id (BIGSERIAL) | 운영자 리뷰 이력 (action, actor, comment) |
| `gear_parent_candidate_exclusions` | id (BIGSERIAL) | 후보 제외 관리 (scope_type, excluded_mmsi, reason, active_from/until) |
| `gear_parent_label_sessions` | id (BIGSERIAL) | 학습 세션 (label_parent_mmsi, status, duration_days, anchor_snapshot) |
| `gear_parent_label_tracking_cycles` | (label_session_id, observed_at) | 학습 추적 사이클 (top_candidate, labeled_candidate 비교) |
| `gear_group_episodes` | episode_id (VARCHAR 50) | 어구 그룹 에피소드 (lineage_key, status, member_mmsis, center_point) |
| `gear_group_episode_snapshots` | (episode_id, observed_at) | 에피소드 스냅샷 |

### 마스터 데이터 (5 테이블)

| 테이블 | PK | 설명 | 시드 |
|---|---|---|---|
| `code_master` | code_id (VARCHAR 100) | 계층형 코드 | 12그룹, 72코드 |
| `gear_type_master` | gear_code (VARCHAR 20) | 어구 유형 | 6종 |
| `zone_polygon_master` | zone_code (VARCHAR 30) | 해역 폴리곤 (PostGIS GEOMETRY 4326) | 8해역 |
| `vessel_permit_master` | mmsi (VARCHAR 20) | 어선 허가 | 9척 |
| `patrol_ship_master` | ship_id (BIGSERIAL), UQ(ship_code) | 함정 | 6척 |

### Prediction 이벤트/통계 (8 테이블)

| 테이블 | PK | 설명 |
|---|---|---|
| `vessel_analysis_results` | (id, analyzed_at) 파티션 | 선박 분석 결과 (35컬럼: mmsi, risk_score, is_dark, transship_suspect, features JSONB 등) |
| `vessel_analysis_results_default` | — | 기본 파티션 |
| `prediction_events` | id (BIGSERIAL), UQ(event_uid) | 탐지 이벤트 (level, category, vessel_mmsi, status, features JSONB) |
| `prediction_alerts` | id (BIGSERIAL) | 경보 발송 (event_id FK, channel, delivery_status) |
| `event_workflow` | id (BIGSERIAL) | 이벤트 상태 변경 이력 (prev/new_status, actor) |
| `prediction_stats_hourly` | stat_hour (TIMESTAMPTZ) | 시간별 통계 (by_category/by_zone JSONB) |
| `prediction_stats_daily` | stat_date (DATE) | 일별 통계 |
| `prediction_stats_monthly` | stat_month (DATE) | 월별 통계 |

### Prediction 보조 (7 테이블)

| 테이블 | PK | 설명 |
|---|---|---|
| `prediction_kpi_realtime` | kpi_key (VARCHAR 50) | 실시간 KPI (value, trend, delta_pct) |
| `prediction_risk_grid` | (cell_id, stat_hour) | 위험도 격자 |
| `prediction_label_input` | id (BIGSERIAL) | 학습 피드백 입력 |
| `gear_correlation_scores` | (model_id, group_key, sub_cluster_id, target_mmsi) | 어구-선박 상관 점수 |
| `gear_correlation_raw_metrics` | id (BIGSERIAL) | 상관 원시 지표 |
| `correlation_param_models` | id (BIGSERIAL) | 상관 모델 파라미터 |
| `group_polygon_snapshots` | id (BIGSERIAL) | 그룹 폴리곤 스냅샷 (PostGIS) |

### Prediction 후보 (1 테이블)

| 테이블 | PK | 설명 |
|---|---|---|
| `gear_group_parent_candidate_snapshots` | id (BIGSERIAL) | 모선 후보 스냅샷 (25컬럼: 점수 분해, evidence JSONB) |

### 단속/작전 (3 테이블)

| 테이블 | PK | 설명 |
|---|---|---|
| `enforcement_records` | id (BIGSERIAL), UQ(enf_uid) | 단속 이력 (event_id FK, vessel_mmsi, action, result) |
| `enforcement_plans` | id (BIGSERIAL), UQ(plan_uid) | 단속 계획 (planned_date, risk_level, status) |
| `patrol_assignments` | id (BIGSERIAL) | 함정 배치 (ship_id FK, plan_id FK, waypoints JSONB) |

### AI 모델 (2 테이블)

| 테이블 | PK | 설명 |
|---|---|---|
| `ai_model_versions` | id (BIGSERIAL) | AI 모델 버전 (accuracy, status, train_config JSONB) |
| `ai_model_metrics` | id (BIGSERIAL) | 모델 메트릭 (model_id FK, metric_name, metric_value) |

### Fleet (3 테이블)

| 테이블 | PK | 설명 |
|---|---|---|
| `fleet_companies` | id (BIGSERIAL) | 선단 업체 (name_cn/en/ko, country) |
| `fleet_vessels` | id (BIGSERIAL) | 선단 선박 (company_id FK, mmsi, gear_code, fleet_role) |
| `fleet_tracking_snapshot` | id (BIGSERIAL) | 선단 추적 스냅샷 (company_id FK) |

### 기타 (2 테이블)

| 테이블 | PK | 설명 |
|---|---|---|
| `gear_identity_log` | id (BIGSERIAL) | 어구 식별 로그 (mmsi, name, parent_mmsi, match_method) |
| `system_config` | key (VARCHAR 100) | 시스템 설정 (value JSONB) |

---

## 인덱스 현황 (149개)

주요 패턴:
- **시계열 DESC**: `(occurred_at DESC)`, `(created_at DESC)`, `(analyzed_at DESC)` — 최신 데이터 우선 조회
- **복합 키**: `(group_key, sub_cluster_id, observed_at DESC)` — 어구 그룹 시계열
- **GiST 공간**: `polygon`, `polygon_geom` — PostGIS 공간 검색
- **GIN 배열**: `violation_categories` — 위반 카테고리 배열 검색
- **부분 인덱스**: `(released_at) WHERE released_at IS NULL` — 활성 제외만, `(is_dark) WHERE is_dark = true` — dark vessel만

## FK 관계 (21개)

```
auth_user ─→ auth_org (org_sn)
auth_user_role ─→ auth_user (user_id), auth_role (role_sn)
auth_perm ─→ auth_role (role_sn), auth_perm_tree (rsrc_cd)
auth_perm_tree ─→ auth_perm_tree (parent_cd, 자기참조)
code_master ─→ code_master (parent_id, 자기참조)
zone_polygon_master ─→ zone_polygon_master (parent_zone_code, 자기참조)
auth_org ─→ auth_org (upper_org_sn, 자기참조)
enforcement_records ─→ prediction_events (event_id), patrol_ship_master (patrol_ship_id)
event_workflow ─→ prediction_events (event_id)
prediction_alerts ─→ prediction_events (event_id)
patrol_assignments ─→ patrol_ship_master (ship_id), enforcement_plans (plan_id)
ai_model_metrics ─→ ai_model_versions (model_id)
gear_correlation_scores ─→ correlation_param_models (model_id)
gear_parent_label_tracking_cycles ─→ gear_parent_label_sessions (label_session_id)
fleet_tracking_snapshot ─→ fleet_companies (company_id)
fleet_vessels ─→ fleet_companies (company_id)
vessel_permit_master ─→ fleet_companies (company_id)
```

---

## 실행 방법

### 최초 1회 - DB/사용자 생성 (관리자 권한 필요)
```sql
psql -h 211.208.115.83 -U snp -d postgres

CREATE DATABASE kcgaidb;
CREATE USER "kcg-app" WITH PASSWORD 'Kcg2026ai';
GRANT ALL PRIVILEGES ON DATABASE kcgaidb TO "kcg-app";

\c kcgaidb
CREATE SCHEMA IF NOT EXISTS kcg AUTHORIZATION "kcg-app";
GRANT ALL ON SCHEMA kcg TO "kcg-app";
ALTER DATABASE kcgaidb OWNER TO "kcg-app";
```

### 마이그레이션 실행 (자동)
백엔드 기동 시 Flyway가 자동 적용:
```bash
cd backend && ./mvnw spring-boot:run
```

### 수동 적용
```bash
cd backend && ./mvnw flyway:migrate \
  -Dflyway.url=jdbc:postgresql://211.208.115.83:5432/kcgaidb \
  -Dflyway.user=kcg-app \
  -Dflyway.password=Kcg2026ai \
  -Dflyway.schemas=kcg
```

### Checksum 불일치 시 (마이그레이션 파일 수정 후)
```bash
cd backend && ./mvnw flyway:repair -Dflyway.url=... (위와 동일)
```

## 신규 마이그레이션 추가
[`backend/src/main/resources/db/migration/`](../../backend/src/main/resources/db/migration/)에 `V0NN__설명.sql` 형식으로 추가하면 다음 기동 시 자동 적용됩니다.

### 메뉴 추가 시 필수 포함 사항
auth_perm_tree에 INSERT 시 메뉴 SSOT 컬럼도 함께 지정:
```sql
INSERT INTO kcg.auth_perm_tree(
    rsrc_cd, parent_cd, rsrc_nm, rsrc_level, sort_ord, icon,
    url_path, label_key, component_key, nav_group, nav_sort,
    labels
) VALUES (
    'new-feature:sub', 'new-feature', '새 기능', 1, 10, 'Sparkles',
    '/new-feature/sub', 'nav.newFeatureSub', 'features/new-feature/SubPage', NULL, 1400,
    '{"ko":"새 기능 서브","en":"New Feature Sub"}'
);
```