ga-commission-system/docs/01_DB_SCHEMA.md

01_DB_SCHEMA — DB 스키마 + 프로젝트 뼈대

1. 기술 스택

영역	기술
Backend	Java 17, Spring Boot 3.2.5, Spring Security, Spring Batch
ORM	MyBatis 3 + mybatis-spring-boot-starter:3.0.3 (JPA 아님)
페이징	pagehelper-spring-boot-starter:2.1.0
DB	PostgreSQL 16
Cache	Redis 7 (선택)
Frontend	React 18 + Vite + TS + Ant Design 5 + AG Grid Community + React Query + Zustand
Build	Gradle 멀티모듈 (Java 17 toolchain)
기타	Lombok, MapStruct 1.5.5, Flyway, Springdoc OpenAPI 2.3

2. 프로젝트 구조 (멀티모듈)

ga-commission-system/
├── settings.gradle              # 모듈 7개 (ga-common/core/api/batch/admin/frontend)
├── build.gradle                 # subprojects { Lombok, MapStruct, JUnit5 }
├── gradle.properties
├── ga-common/                   # 공통 프레임워크 (라이브러리, bootJar disabled)
├── ga-core/                     # 도메인 코어 (VO/Mapper/XML, ga-common 의존)
├── ga-api/                      # REST API (executable jar, 8080)
├── ga-batch/                    # Spring Batch (executable jar, 8081)
├── ga-admin/                    # (현재 빈 모듈, 향후 관리자 전용)
├── ga-frontend/                 # Vite 빌드, dist/ → nginx
├── docker-compose.yml
├── Dockerfile
└── docs/                        # 본 폴더

모듈 의존성

• ga-core → ga-common
• ga-api → ga-core (transitively ga-common)
• ga-batch→ ga-core
• ga-admin→ ga-core (현재 미사용)

핵심 build.gradle 설정

// 루트 build.gradle subprojects
plugins {
    id 'java'
    id 'org.springframework.boot' version '3.2.5' apply false
    id 'io.spring.dependency-management' version '1.1.4' apply false
}
java { toolchain { languageVersion = JavaLanguageVersion.of(17) } }
compileJava { options.compilerArgs << '-parameters' }   // MyBatis 파라미터 이름

// ga-common: 라이브러리 (bootJar disabled, jar enabled)
// ga-api / ga-batch: bootJar enabled, jar disabled

MyBatis 설정 (ga-common/src/main/resources/mybatis/mybatis-config.xml)

<configuration>
    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
        <setting name="cacheEnabled" value="false"/>
        <setting name="callSettersOnNulls" value="true"/>
        <setting name="defaultStatementTimeout" value="60"/>
        <setting name="jdbcTypeForNull" value="NULL"/>
    </settings>
    <typeHandlers>
        <!-- JsonTypeHandler 만 글로벌. EncryptTypeHandler 는 컬럼별 명시 -->
        <typeHandler handler="com.ga.common.mybatis.JsonTypeHandler"/>
    </typeHandlers>
</configuration>

application.yml (ga-api)

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/ga
    username: ga
    password: ga
    hikari: { maximum-pool-size: 20 }
  flyway:
    locations: classpath:db/migration   # ga-common 의 V1~V17
    baseline-on-migrate: true
mybatis:
  config-location: classpath:mybatis/mybatis-config.xml
  mapper-locations: classpath*:mapper/**/*.xml
ga:
  jwt:
    secret: ${JWT_SECRET:replace-me-256-bit-or-longer-secret-key}
    access-ttl-min: 120
    refresh-ttl-day: 7
  encrypt:
    key: ${ENCRYPT_KEY:32byte-key-for-aes-256-cbc-aaaa}   # 32 byte

---

3. Flyway 마이그레이션 V1~V17

위치: ga-common/src/main/resources/db/migration/

모든 금액 DECIMAL(15,2), 모든 수수료율 DECIMAL(7,4). 모든 테이블에 인덱스 부여.

V1__조직_인사.sql (4 테이블)

grade            (grade_id PK, grade_name, grade_level, grade_group, description, is_active, sort_order)
organization     (org_id PK, parent_org_id FK self, org_name, org_type[HQ/BR/TM], org_level, region,
                  effective_from, effective_to, is_active, created_at, updated_at, created_by)
agent            (agent_id PK, org_id FK, grade_id FK, agent_name, resident_no(AES), license_no,
                  phone, email, bank_code, account_no(AES), status[ACTIVE/LEAVE/SUSPEND],
                  join_date, leave_date, created_at, updated_at)
agent_org_history(history_id PK, agent_id FK, from_org_id, to_org_id, from_grade_id, to_grade_id,
                  change_type[TRANSFER/PROMOTE/DEMOTE/JOIN/LEAVE], change_date, change_reason)

인덱스: idx_org_parent, idx_org_type(org_type,is_active), idx_agent_org(org_id,status), idx_agent_lic(license_no), idx_agent_status.

V2__상품_계약.sql (3 테이블)

insurance_company(company_id PK, company_code UQ, company_name, company_type, biz_no,
                  contact_name, contact_phone, contact_email, is_active)
product          (product_id PK, company_id FK, product_code, product_name, insurance_type,
                  product_group, pay_period_type, is_active, launch_date, end_date)
contract         (contract_id PK, agent_id FK, product_id FK, policy_no UQ, contractor_name,
                  insured_name, premium DECIMAL(15,2), pay_cycle, pay_period, coverage_period,
                  status, contract_date, effective_date, lapse_date, revival_date, created_at, updated_at)

V3__수수료규정.sql (5 테이블)

commission_rate (rate_id PK, product_id FK, commission_year, rate_pct DECIMAL(7,4),
                 pay_method_cond, min_premium, effective_from, effective_to, version)
payout_rule     (rule_id PK, grade_id FK, insurance_type, commission_year, payout_pct DECIMAL(7,4),
                 pay_method_cond, performance_grade, effective_from, effective_to, version)
override_rule   (override_id PK, from_grade, to_grade, override_pct DECIMAL(7,4),
                 calc_type, insurance_type_cond, cap_amount, effective_from, effective_to)
chargeback_rule (cb_rule_id PK, company_code, insurance_type, lapse_month_from, lapse_month_to,
                 cb_rate DECIMAL(7,4), include_override, installment_allowed, max_installments,
                 effective_from, effective_to)
exception_type_code (exception_code PK, exception_name, category, direction[PLUS/MINUS],
                     auto_calc_yn, approval_required, tax_included, description, is_active)

V4__데이터수신.sql (5 테이블)

company_profile         (company_code PK FK, data_format, receive_method, file_encoding, delimiter,
                         header_row, data_start_row, sheet_name, date_format, amount_format,
                         api_url, ftp_host, ftp_path, receive_schedule, is_active)
company_field_mapping   (mapping_id PK, company_code FK, source_field, source_col_index,
                         target_table, target_field, data_type, transform_rule, default_value,
                         is_required, sort_order, version, effective_from)
code_mapping            (code_id PK, company_code, code_type, source_code, source_name,
                         target_code, target_name, is_active)
raw_commission_data     (raw_id PK, company_code, batch_id, settle_month, data_type,
                         raw_content TEXT, parse_status[PENDING/OK/ERROR], mapped_ledger_id,
                         mapped_table, file_name, row_number, received_at, parsed_at)
parse_error_log         (error_id PK, raw_id FK, company_code, error_type, error_field,
                         error_value, error_message, resolve_status[OPEN/RESOLVED/IGNORED],
                         resolved_by, resolve_note, resolved_at, created_at)

V5__원장관리.sql (3 테이블, 동일 컬럼)

recruit_ledger / maintain_ledger / exception_ledger
공통 컬럼:
  ledger_id PK, contract_id FK, agent_id FK, policy_no, company_code, product_code,
  insurance_type, commission_year, premium DECIMAL(15,2), pay_method,
  company_rate DECIMAL(7,4), company_amount DECIMAL(15,2),
  payout_rate  DECIMAL(7,4), agent_amount   DECIMAL(15,2),
  tax_amount   DECIMAL(15,2),
  reconcile_status[PENDING/MATCHED/DIFF], reconcile_diff,
  advance_yn, advance_amount, chargeback_yn, settle_month, status[CALCULATED/CONFIRMED/HOLD],
  version, created_at, updated_at

exception_ledger 추가:
  exception_code, approve_status, approver_id, approve_date,
  recurring_yn, recurring_until, recurring_parent_id

V6__정산_지급.sql (4 테이블)

settle_master  (settle_id PK, agent_id FK, settle_month,
                recruit_total, maintain_total, exception_plus, exception_minus,
                override_total, chargeback_total, gross_amount, tax_amount, net_amount,
                status[DRAFT/CONFIRMED/HOLD/PAID], calc_date, confirmed_by, confirmed_at,
                hold_reason, UNIQUE(agent_id, settle_month))
override_settle(os_id PK, settle_id FK, from_agent_id, to_agent_id, base_amount,
                override_pct, override_amount, calc_basis)
chargeback     (cb_id PK, contract_id FK, agent_id FK, lapse_month_diff, base_amount,
                cb_rate, cb_amount, installment_seq, installment_total, status, created_at)
payment        (payment_id PK, settle_id FK, agent_id FK, bank_code, account_no(AES),
                amount, transfer_date, status[PENDING/SENT/SUCCESS/FAIL], result_code, result_msg)

V7__배치_감사.sql (4 테이블)

batch_job_log  (job_log_id PK, job_name, job_params, settle_month, status[REQUESTED/RUNNING/
                COMPLETED/FAILED], step_name, total_count, success_count, error_count,
                error_message, started_at, ended_at, duration_ms)
reconciliation (recon_id PK, company_code, settle_month, expected_amount, actual_amount,
                diff_amount, recon_status, resolved_by, resolved_at)
audit_log      (audit_id PK, user_id, action_type, target_table, target_id,
                before_data JSONB, after_data JSONB, ip_address, user_agent, created_at)
users          (user_id PK, login_id UQ, password(BCrypt), user_name, agent_id FK nullable,
                email, phone, status[ACTIVE/LOCKED/EXPIRED], last_login_at,
                fail_count, password_changed_at, created_at, updated_at)

V8__초기데이터.sql

• grade 6 건 (FC/SFC/SMGR/DM/GM/SVP)
• exception_type_code 10 건 (ADJUST_PLUS / ADJUST_MINUS / BONUS / PENALTY / ...)

V9__공통코드.sql (2 테이블)

common_code_group (group_code VARCHAR(20) PK, group_name, description, is_system CHAR(1),
                   is_active, sort_order, created_at, created_by, updated_at, updated_by)
common_code       (code_id PK, group_code FK, code VARCHAR(30), code_name, code_name_en,
                   code_desc, attr1, attr2, attr3, ref_code, sort_order, is_active,
                   created_at, created_by, updated_at, updated_by,
                   UNIQUE(group_code, code))

V10__메뉴관리.sql (5 테이블)

menu                  (menu_id PK, parent_menu_id FK self, menu_code UQ, menu_name,
                       menu_type[DIRECTORY/PAGE/LINK], menu_path, menu_icon, component_path,
                       menu_level, sort_order, is_visible, is_active, description)
menu_permission       (perm_id PK, menu_id FK, perm_code[READ/CREATE/UPDATE/DELETE/APPROVE/
                       EXPORT/PRINT], perm_name, sort_order, UNIQUE(menu_id, perm_code))
role                  (role_id PK, role_code UQ, role_name, role_desc, role_level,
                       is_system, is_active)
role_menu_permission  (rmp_id PK, role_id FK, menu_id FK, perm_code, is_granted,
                       UNIQUE(role_id, menu_id, perm_code))
user_role             (ur_id PK, user_id FK, role_id FK, UNIQUE(user_id, role_id))

V11__시스템관리.sql (6 테이블)

login_log        (log_id PK, user_id, login_id, login_type[PWD/SSO], result[SUCCESS/FAIL],
                  fail_reason, ip_address, user_agent, login_at)
api_access_log   (access_id PK, user_id, request_uri, http_method, params, response_code,
                  response_time_ms, ip_address, created_at)        -- 비동기 저장
data_change_log  (change_id PK, user_id, menu_code, table_name, record_id,
                  before_data JSONB, after_data JSONB, action[CREATE/UPDATE/DELETE], created_at)
file_storage     (file_id PK, group_id, original_name, stored_name, file_path, file_size,
                  content_type, download_count, is_deleted, created_by, created_at)
notification     (noti_id PK, user_id, noti_type, title, content, link_url, is_read, read_at, created_at)
system_config    (config_key VARCHAR(50) PK, config_value, config_group, description,
                  is_encrypted, updated_by, updated_at)

V12__공통_초기데이터.sql

• 공통코드 18 그룹 + 상세 42 건 (AGENT_STATUS / CONTRACT_STATUS / SETTLE_STATUS / PAY_CYCLE / INSURANCE_TYPE / ORG_TYPE / GRADE_GROUP / RECONCILE_STATUS / PARSE_STATUS / EXCEPTION_DIRECTION / APPROVE_STATUS / PAYMENT_STATUS / NOTI_TYPE / FILE_TYPE / DATA_FORMAT / RECEIVE_METHOD / TRANSFORM_RULE / CHANGE_TYPE)
• 역할 4 건 (SUPER_ADMIN / ADMIN / MANAGER / AGENT)
• 대메뉴 10 건 + 시스템설정 13 건

V13__인덱스최적화.sql

• recruit_ledger(settle_month, agent_id), recruit_ledger(policy_no)
• maintain_ledger(settle_month, agent_id), maintain_ledger(policy_no)
• exception_ledger(settle_month, approve_status)
• settle_master(settle_month, status)
• contract(agent_id, status), contract(policy_no)
• raw_commission_data(company_code, settle_month, parse_status)
• api_access_log(created_at), data_change_log(created_at, table_name)

V14__파티셔닝.sql

• 헬퍼 함수 create_monthly_partition(table_name, year_month) 정의
• recruit_ledger / maintain_ledger / api_access_log / data_change_log 를 settle_month 또는 created_at 기준 RANGE 파티션으로 변환 (PG 13+)

V15__뷰.sql

v_agent_monthly_summary  (agent_id, agent_name, org_name, settle_month,
                          recruit_amt, maintain_amt, exception_plus, exception_minus,
                          override_amt, chargeback_amt, gross, tax, net, status)
v_org_settle_summary     (org_id, org_name, settle_month, agent_count, total_gross, total_net)
v_chargeback_risk        (contract_id, policy_no, agent_name, lapse_month_diff,
                          predicted_cb_amount, risk_grade)

V16__관리자_초기데이터.sql

• users 에 admin / admin1234! (BCrypt) + SUPER_ADMIN 역할 매핑
• 메뉴 33건 + 메뉴별 권한 150건 + 역할-메뉴-권한 매트릭스 323건

V17__테스트데이터.sql

• 보험사 5 (SAMSUNG / HANWHA / KYOBO / DB / KB)
• 상품 20 (보험사당 4)
• 조직 7 (본부 1 / 지점 3 / 팀 3) + 설계사 50
• 계약 200 (ACTIVE 190 / LAPSE 10)
• 수수료 규정: commission_rate 100 / payout_rule 120 / override_rule 10 / chargeback_rule 4

---

4. 환경변수 / 시크릿

키	기본값	설명
JWT_SECRET	replace-me-...	HS256 256bit+
ENCRYPT_KEY	32byte-key-...	AES-256 키 (32 byte)
DB_HOST / DB_PORT / DB_NAME / DB_USERNAME / DB_PASSWORD	localhost/5432/ga/ga/ga	DataSource
REDIS_HOST / REDIS_PORT	localhost / 6379	선택 (캐시)

운영 DB 프로파일 (application-trading_ai.yml):

spring.datasource:
  url: jdbc:postgresql://192.168.0.60:55432/trading_ai?currentSchema=ga
  username: kyu
  password: ${DB_PASSWORD}
spring.flyway.schemas: ga