스키마 버전 이력 (Flyway 75단계)
-- ═══════════════════════════════════════════════════════════
-- V1__baseline.sql
-- ═══════════════════════════════════════════════════════════
CREATE TABLE `users` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`login_id` varchar(20) UNIQUE NOT NULL COMMENT '영숫자 4~20, 소문자 정규화 저장(#45)',
`password_hash` varchar(255) NOT NULL COMMENT 'Argon2id',
`name` varchar(100) NOT NULL COMMENT '본인인증 실명',
`name_norm` varchar(100) NOT NULL COMMENT '이름 비교용 정규화(공백·특수문자 제거, 로마자화)',
`ci_hash` char(64) NOT NULL COMMENT '본인인증 CI HMAC',
`active_ci` char(64) COMMENT 'status=ACTIVE일 때만 ci_hash 복사, 그 외 NULL - UNIQUE로 1인 1활성계정 강제(#7)',
`di_hash` char(64),
`phone_enc` varbinary(64) NOT NULL,
`phone_hash` char(64) NOT NULL COMMENT '선물 대상 검색용(#5·#6)',
`birth_date` date COMMENT '본인인증 결과. 성인 전용 검증',
`marketing_agree` boolean NOT NULL DEFAULT false COMMENT '광고성 푸시 수신동의(#20)',
`status` varchar(20) NOT NULL DEFAULT 'ACTIVE' COMMENT 'ACTIVE|SUSPENDED|WITHDRAWN',
`withdrawn_at` datetime COMMENT '탈퇴 시각 - 재가입 대기(admin 설정) 판정 기준',
`destroy_due_at` date COMMENT '개인정보 파기 예정일 = 탈퇴+법정보존. 파기 배치 대상 선별',
`anonymized_at` datetime COMMENT '파기(익명화) 완료 시각 - phone_enc·name·birth_date 무효화',
`created_at` datetime NOT NULL
);
CREATE TABLE `merchants` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`biz_no` varchar(10) UNIQUE NOT NULL COMMENT '사업자등록번호(국세청 진위확인 #28)',
`biz_name` varchar(200) NOT NULL,
`ceo_name` varchar(100) NOT NULL,
`ceo_name_norm` varchar(100) NOT NULL,
`ceo_ci_hash` char(64) NOT NULL COMMENT '대표자 본인인증',
`biz_type` varchar(20) NOT NULL COMMENT 'CORP(법인)|SOLE(개인사업자)',
`category` varchar(50),
`address` varchar(300),
`biz_cert_path` varchar(300) COMMENT '사업자등록증 이미지(비공개 저장)',
`vat_mode` varchar(10) NOT NULL DEFAULT 'TAXED' COMMENT 'TAXED|EXEMPT - 매장이 설정(#24)',
`vat_rate` decimal(5,2) NOT NULL DEFAULT 10 COMMENT '결제 시점 스냅샷의 원천',
`status` varchar(20) NOT NULL DEFAULT 'PENDING' COMMENT 'PENDING|ACTIVE|REJECTED|SUSPENDED|CLOSED',
`reject_reason` varchar(500),
`approved_by` bigint COMMENT '승인 관리자(#28)',
`approved_at` datetime,
`created_at` datetime NOT NULL
);
CREATE TABLE `merchant_accounts` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`merchant_id` bigint UNIQUE NOT NULL COMMENT '매장 1계정(하위계정 미도입)',
`login_id` varchar(20) UNIQUE NOT NULL,
`password_hash` varchar(255) NOT NULL,
`status` varchar(20) NOT NULL DEFAULT 'ACTIVE',
`created_at` datetime NOT NULL
);
CREATE TABLE `admin_accounts` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`login_id` varchar(20) UNIQUE NOT NULL,
`password_hash` varchar(255) NOT NULL,
`name` varchar(100) NOT NULL,
`is_root` boolean NOT NULL DEFAULT false COMMENT '계정 생성·삭제·OTP 초기화는 root만(#9 정책)',
`otp_secret_enc` varbinary(128) COMMENT 'TOTP 시크릿. 전 계정 의무화 - 미설정 시 기능 제한',
`otp_enabled` boolean NOT NULL DEFAULT false,
`otp_fail_count` int NOT NULL DEFAULT 0,
`status` varchar(20) NOT NULL DEFAULT 'ACTIVE' COMMENT 'ACTIVE|LOCKED|DISABLED',
`created_at` datetime NOT NULL
);
CREATE TABLE `admin_allowed_ips` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`cidr` varchar(50) NOT NULL COMMENT '단건 IP 또는 CIDR(#33)',
`memo` varchar(200),
`created_by` bigint NOT NULL,
`created_at` datetime NOT NULL
);
CREATE TABLE `auth_pins` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`principal_type` varchar(10) NOT NULL,
`principal_id` bigint NOT NULL,
`pin_hash` varchar(255) NOT NULL COMMENT 'Argon2id. 간편로그인+거래인증 겸용(#16)',
`fail_count` int NOT NULL DEFAULT 0 COMMENT '5회 초과 시 전체 로그인 강등',
`locked_until` datetime,
`updated_at` datetime NOT NULL
);
CREATE TABLE `auth_passkeys` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`principal_type` varchar(10) NOT NULL,
`principal_id` bigint NOT NULL,
`credential_id` varchar(255) UNIQUE NOT NULL COMMENT 'FIDO2/WebAuthn',
`public_key` varbinary(512) NOT NULL,
`device_label` varchar(100),
`created_at` datetime NOT NULL
);
CREATE TABLE `auth_devices` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`principal_type` varchar(10) NOT NULL,
`principal_id` bigint NOT NULL,
`device_uid` varchar(128) NOT NULL COMMENT '앱 설치 식별자 - PIN 기기 바인딩',
`platform` varchar(10) NOT NULL COMMENT 'IOS|ANDROID',
`model` varchar(100),
`status` varchar(20) NOT NULL DEFAULT 'ACTIVE' COMMENT 'ACTIVE|REVOKED - 동시 1기기: 새 기기 등록 시 기존 REVOKED(2차 항목 4)',
`last_login_at` datetime,
`created_at` datetime NOT NULL
);
CREATE TABLE `login_histories` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`principal_type` varchar(10) NOT NULL,
`principal_id` bigint NOT NULL,
`method` varchar(20) NOT NULL COMMENT 'PASSWORD|PIN|PASSKEY|OTP',
`ip` varchar(45),
`device_uid` varchar(128),
`result` varchar(20) NOT NULL COMMENT 'SUCCESS|FAIL_PW|FAIL_OTP|BLOCKED_IP 등',
`created_at` datetime NOT NULL
);
CREATE TABLE `bank_accounts` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`owner_type` varchar(10) NOT NULL COMMENT 'USER|MERCHANT',
`owner_id` bigint NOT NULL,
`bank_code` varchar(10) NOT NULL,
`account_enc` varbinary(128) NOT NULL,
`account_hash` char(64) NOT NULL COMMENT '중복 등록 검사용',
`holder_name` varchar(100) NOT NULL COMMENT '실명인증 예금주',
`holder_name_norm` varchar(100) NOT NULL COMMENT '정규화 prefix 비교(#15 확장 규칙)',
`verified_at` datetime COMMENT '실명+1원인증 완료 시각. 순서: 실명일치→1원인증(#15)',
`status` varchar(20) NOT NULL DEFAULT 'ACTIVE' COMMENT 'ACTIVE|REMOVED - 변경 시 REMOVED 처리, 이력 보존(#10 정책)',
`cooldown_until` datetime COMMENT '계좌 변경 후 출금 냉각 24h',
`created_at` datetime NOT NULL
);
CREATE TABLE `cards` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`user_id` bigint NOT NULL,
`number_hash` char(64) UNIQUE NOT NULL COMMENT '재사용 영구 차단의 단일 진실(#2). 삭제 금지',
`number_enc` varbinary(64) NOT NULL COMMENT 'BIN 972963 + 랜덤9 + Luhn, 16자리',
`cvc_enc` varbinary(32) NOT NULL COMMENT '고정 CVC 확정. 표시용, 서버 암호화 보관',
`issued_at` datetime NOT NULL,
`expires_on` date NOT NULL COMMENT '발급+5년. 만료 시 재발행 절차 재사용',
`design_code` varchar(20) NOT NULL COMMENT 'OCEAN|MIDNIGHT|SUNSET|PEARL(#10)',
`color_code` varchar(20) COMMENT '사용자 선택 색상(#7)',
`is_primary` boolean NOT NULL DEFAULT false COMMENT '대표 카드 = 기본 수취(#5 정책)',
`status` varchar(20) NOT NULL DEFAULT 'ACTIVE' COMMENT 'ACTIVE|SUSPENDED|REISSUED|EXPIRED',
`reissued_to` bigint COMMENT '재발행 체인 참조(#2)',
`created_at` datetime NOT NULL
);
CREATE TABLE `wallets` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`owner_type` varchar(12) NOT NULL COMMENT 'USER_CARD|MERCHANT|SYSTEM',
`card_id` bigint UNIQUE COMMENT 'USER_CARD일 때만, 카드 1:1',
`merchant_id` bigint UNIQUE COMMENT 'MERCHANT일 때만. 정산 출금·취소 반환 전용(#8 정책)',
`system_code` varchar(30) UNIQUE COMMENT 'SYSTEM일 때만: FEE_REVENUE(수수료수익)|EXPIRED(낙전)|GIFT_ESCROW(선물보류)|UNMATCHED(미매칭입금)|SETTLEMENT_CLEARING(대외청산)|FORFEITED(탈퇴 소액포기)|EXCHANGE_{제휴사}(포인트전환 청산, #49 구현보류)',
`balance` decimal(15,0) NOT NULL DEFAULT 0 COMMENT 'CHECK: SYSTEM 제외 balance>=0. 사용자·매장=FOR UPDATE 잠금 지점, SYSTEM=일 배치 파생(핫로우 방지 규약)',
`created_at` datetime NOT NULL
);
CREATE TABLE `point_lots` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`wallet_id` bigint NOT NULL,
`lot_type` varchar(10) NOT NULL COMMENT 'DEPOSIT(유상 충전·포인트전환 유입: 결제·선물·출금·환급 가능)|REWARD(무상 적립: 결제만, 환급·출금 불가) - 유상/무상 2구분. 환급은 소스 무관 전체 DEPOSIT 잔액 기준',
`source` varchar(30) NOT NULL COMMENT 'DEPOSIT(충전)|GIFT(선물수취)|REISSUE(재발행 이관)|EVENT(이벤트 적립)|EXCHANGE:{code}(포인트전환 유입, 구현보류) - 이력 메타(환급 판정에 미사용)',
`amount_init` decimal(15,0) NOT NULL,
`amount_remaining` decimal(15,0) NOT NULL COMMENT 'CHECK(0 <= remaining <= init). 감소만 허용(방어 트리거)',
`expires_at` datetime NOT NULL COMMENT '모든 포인트 유효기간 필수(#42). 재발행 이관은 승계, 선물 수취는 리셋(승인 정책)',
`origin_lot_id` bigint COMMENT '재발행 승계 시 원 로트 참조',
`created_at` datetime NOT NULL
);
CREATE TABLE `transactions` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`txn_uid` char(26) UNIQUE NOT NULL COMMENT '대외 노출용 ULID',
`type` varchar(12) NOT NULL COMMENT 'DEPOSIT(충전)|WITHDRAW(출금·환급)|GIFT(선물)|PAYMENT(결제)|CANCEL(취소)|ADJUST(관리자 조정)|EXPIRE(소멸)',
`subtype` varchar(20) NOT NULL COMMENT 'VACCT(가상계좌 입금)|FIRMBANK(펌뱅킹 이체)|REISSUE|GIFT_LINK|GIFT_PHONE|GIFT_QR|QR_STORE|QR_ORDER|CPM|PG_ONLINE|SETTLEMENT|FORFEIT(소액포기)|UNMATCHED_RETURN|EXCHANGE_IN|EXCHANGE_OUT(포인트전환, 구현보류) 등',
`status` varchar(12) NOT NULL COMMENT 'PENDING|HOLD|UNKNOWN|CONFIRMED|FAILED|REVERSED|CANCELED - 선차감·리컨실러 상태머신(#3)',
`initiator_type` varchar(10) NOT NULL COMMENT 'USER|MERCHANT|ADMIN|SYSTEM',
`initiator_id` bigint NOT NULL DEFAULT 0,
`idempotency_key` varchar(64) COMMENT 'UNIQUE(initiator_type, initiator_id, idempotency_key) - 사용자 스코프 멱등(#3)',
`card_id` bigint,
`counterparty_card_id` bigint COMMENT '선물 수신 카드 - 받은선물 내역·gift_recv 집계·FDS 선물집중의 키(감사 보완)',
`merchant_id` bigint,
`amount` decimal(15,0) NOT NULL,
`fee_amount` decimal(15,0) NOT NULL DEFAULT 0,
`fee_rate_snap` decimal(7,4) COMMENT '적용 정률 스냅샷(#25)',
`fee_fixed_snap` decimal(15,0) COMMENT '적용 정액 스냅샷',
`vat_amount` decimal(15,0) COMMENT '결제 시점 부가세 스냅샷(#24)',
`cancelable_until` datetime COMMENT '결제 시점 취소기한 스냅샷',
`scheduled_at` datetime COMMENT '출금·정산 실행 예정 시각 - 신청 시점 payout_policies(+N일 HH시) 스냅샷. 정책 변경 소급 방지',
`related_txn_id` bigint COMMENT '역분개·이관·취소의 원거래 참조 - 추적 플로우차트(#32)의 간선',
`bank_tran_ref` varchar(64) COMMENT '펌뱅킹 거래 식별자 - 리컨실러 조회 키',
`bank_account_id` bigint COMMENT '출금·정산 실행 대상 계좌 스냅샷 - 이후 계좌 변경해도 "어디로 나갔나" 불변(전수 대조에서 발견된 누락 보완)',
`detail_id` bigint COMMENT '거래상세 PK 참조 (발주 답변 2026-07-22): 원장=단일 거래 테이블(type 분기), 업무 상세만 거래타입별 테이블로 분리 — "거래(거래타입,상세PK) ← 거래상세". 상세 테이블 = pg_orders(PG결제)/gift_links(선물링크)/deposit_notices(입금)/merchant_qrs(QR결제) 등, (type,subtype)이 어느 상세 테이블인지 결정. 상세 설계는 재논의 예정',
`pg_order_id` bigint COMMENT 'PG 온라인 결제 주문 참조 (detail_id의 PG 사례 - 재편 시 detail_id로 통합)',
`fail_reason` varchar(30) COMMENT 'MERCHANT_INSUFFICIENT_BALANCE|CANCEL_WINDOW_EXPIRED 등 코드',
`memo` varchar(200) COMMENT '선물 메시지(50자·금칙어 필터) 등',
`created_at` datetime NOT NULL,
`confirmed_at` datetime
);
CREATE TABLE `ledger_entries` (
`id` bigint PRIMARY KEY AUTO_INCREMENT COMMENT '실PK는 (id, created_at) - 월 파티셔닝',
`txn_id` bigint NOT NULL COMMENT 'FK(transactions) - 파티션 간 FK는 논리 참조(앱 강제)',
`wallet_id` bigint NOT NULL,
`direction` char(2) NOT NULL COMMENT 'DR|CR. 거래별 ΣDR=ΣCR (복식·Java 검증+일 대사)',
`amount` decimal(15,0) NOT NULL COMMENT 'CHECK(amount > 0)',
`balance_after` decimal(15,0) COMMENT '사용자·매장 지갑=NOT NULL 체인(#29). SYSTEM 지갑 leg=NULL(핫로우 규약, 일 배치 SUM 검증) - NULL 규칙은 CHECK+대사로 강제',
`created_at` datetime NOT NULL
);
CREATE TABLE `lot_allocations` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`ledger_entry_id` bigint NOT NULL COMMENT '차감(DR) 엔트리',
`lot_id` bigint NOT NULL,
`amount` decimal(15,0) NOT NULL
);
CREATE TABLE `deposit_identifiers` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`user_id` bigint NOT NULL,
`kind` varchar(20) NOT NULL COMMENT 'VIRTUAL_ACCOUNT|DEPOSITOR_CODE - 펌뱅킹 계약 확정 후 단일화',
`value` varchar(64) UNIQUE NOT NULL COMMENT '가상계좌 식별자 평문 여부는 법무 확인 대기 - 의무 적용 시 value_hash 전환(질의서 반영)',
`status` varchar(20) NOT NULL DEFAULT 'ACTIVE',
`created_at` datetime NOT NULL
);
CREATE TABLE `deposit_notices` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`source` varchar(10) NOT NULL COMMENT 'PGVACCT(발주사 PG 가상계좌 입금통지 웹훅)|FIRMBANK(펌뱅킹 조회 확정)|SMS(보조 감지)',
`raw_text` text COMMENT 'SMS 원문 보존(#29 증거 보존) - 절단 방지 TEXT',
`dedup_key` char(64) NOT NULL COMMENT 'SMS=HMAC(수신번호+원문+수신분), 펌뱅킹=bank_tran_ref 사본 - 소스별 결정적 중복키(SMS는 ref 부재)',
`bank_tran_ref` varchar(64) COMMENT '펌뱅킹 거래 식별자',
`parsed_amount` decimal(15,0),
`parsed_name` varchar(100),
`parsed_name_norm` varchar(100) COMMENT '정규화 prefix 매칭용',
`identifier_value` varchar(64) COMMENT '가상계좌/입금자코드 파싱값',
`status` varchar(20) NOT NULL DEFAULT 'NEW' COMMENT 'NEW|MATCHED|UNMATCHED|IGNORED',
`matched_txn_id` bigint COMMENT '매칭 성사 시 DEPOSIT(충전) 거래 참조',
`received_at` datetime NOT NULL
);
CREATE TABLE `unmatched_deposits` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`notice_id` bigint NOT NULL,
`amount` decimal(15,0) NOT NULL,
`status` varchar(20) NOT NULL DEFAULT 'PENDING' COMMENT 'PENDING|MATCHED|RETURNED|HOLD - 관리자 재량 처리(#48)',
`resolved_by` bigint,
`resolved_at` datetime,
`resolution` varchar(20) COMMENT 'MANUAL_MATCH|RETURN|HOLD',
`resolution_note` varchar(500) COMMENT '사유 필수 - 원장 역분개와 이중 기록',
`return_method` varchar(30) COMMENT '반환 방법(펌뱅킹 이체 등)',
`return_bank_code` varchar(10),
`return_account_enc` varbinary(128) COMMENT '반환 계좌 AES-256 - 계좌번호 암호화 의무(감사 보완)',
`created_at` datetime NOT NULL
);
CREATE TABLE `gift_links` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`sender_card_id` bigint NOT NULL,
`hold_txn_id` bigint NOT NULL COMMENT '선차감(GIFT_ESCROW 계상) 거래',
`token_hash` char(64) UNIQUE NOT NULL COMMENT '클레임 토큰(1회용) - 원문 미저장',
`amount` decimal(15,0) NOT NULL,
`message` varchar(50) COMMENT '금칙어 필터(2차 항목 8)',
`expires_at` datetime NOT NULL COMMENT 'TTL 24시간 확정 - 만료 시 자동 반환',
`status` varchar(20) NOT NULL DEFAULT 'CREATED' COMMENT 'CREATED|CLAIMED|EXPIRED|CANCELED(송금인 회수)',
`claimed_card_id` bigint COMMENT '수령 카드(수령자 대표 카드)',
`claimed_at` datetime
);
CREATE TABLE `merchant_qrs` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`merchant_id` bigint NOT NULL,
`qr_type` varchar(15) NOT NULL COMMENT 'STORE_STATIC|STORE_DYNAMIC|ORDER - USER_PAY(CPM)·USER_RECEIVE는 Redis 단명 토큰(DB 미저장)',
`label` varchar(50) COMMENT '카운터1 등 - 매장당 복수 QR(#24)',
`amount` decimal(15,0) COMMENT 'ORDER형만',
`key_version` int NOT NULL DEFAULT 1 COMMENT 'AES-GCM 키 버전',
`status` varchar(20) NOT NULL DEFAULT 'ACTIVE' COMMENT 'ACTIVE|REVOKED|USED(ORDER 1회용)',
`expires_at` datetime COMMENT 'DYNAMIC/ORDER TTL',
`created_at` datetime NOT NULL
);
CREATE TABLE `merchant_api_credentials` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`merchant_id` bigint UNIQUE NOT NULL,
`client_id` varchar(32) UNIQUE NOT NULL,
`api_key_hash` varchar(255) NOT NULL COMMENT '시크릿 해시만 보관(발급 시 1회 표시)',
`prev_key_hash` varchar(255) COMMENT '키 롤 유예(24h 신구 동시 유효)',
`prev_key_expires_at` datetime,
`webhook_url` varchar(500) COMMENT '매장앱에서 입력(#26)',
`webhook_secret_enc` varbinary(512) COMMENT 'AES-256-GCM 암호화 - 발신 웹훅 HMAC 서명 생성에 원문 필요(해시 불가, 감사 보완)',
`status` varchar(15) NOT NULL DEFAULT 'REQUESTED' COMMENT 'REQUESTED(신청)|APPROVED|REJECTED|REVOKED - admin 승인 큐(#28)',
`requested_at` datetime NOT NULL,
`approved_by` bigint,
`mode` varchar(10) NOT NULL DEFAULT 'SANDBOX' COMMENT 'SANDBOX|LIVE - 테스트 통과 후 매장이 전환(#26·#28)',
`test_create_ok_at` datetime COMMENT '체크리스트: 결제 생성',
`test_webhook_ok_at` datetime COMMENT '체크리스트: 웹훅 2xx',
`test_query_ok_at` datetime COMMENT '체크리스트: 상태조회',
`created_at` datetime NOT NULL
);
CREATE TABLE `pg_orders` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`merchant_id` bigint NOT NULL,
`order_no` varchar(64) NOT NULL COMMENT '쇼핑몰 주문번호 - UNIQUE(merchant_id, order_no) 멱등',
`amount` decimal(15,0) NOT NULL,
`supply_amount` decimal(15,0) COMMENT '공급가액',
`vat_amount` decimal(15,0) NOT NULL COMMENT 'PG API로 수신·보관(#24)',
`item_name` varchar(200),
`qr_id` bigint,
`txn_id` bigint COMMENT '결제 성사 시 거래 참조',
`status` varchar(20) NOT NULL DEFAULT 'CREATED' COMMENT 'CREATED|PAID|CANCELED|EXPIRED',
`is_sandbox` boolean NOT NULL DEFAULT false COMMENT '테스트 주문 실원장 완전 분리',
`created_at` datetime NOT NULL
);
CREATE TABLE `webhook_deliveries` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`merchant_id` bigint NOT NULL,
`event_type` varchar(30) NOT NULL COMMENT 'PAYMENT_COMPLETED|PAYMENT_CANCELED|TEST 등',
`pg_order_id` bigint,
`payload` text NOT NULL,
`target_url` varchar(500) NOT NULL,
`attempts` int NOT NULL DEFAULT 0 COMMENT '지수 백오프 최대 10회/24h',
`next_retry_at` datetime,
`last_http_code` int,
`claimed_at` datetime COMMENT '발송 클레임 시각 - SENDING 고아 복구 스위퍼 기준',
`status` varchar(20) NOT NULL DEFAULT 'PENDING' COMMENT 'PENDING|SENDING(클레임-커밋 후 발송)|DELIVERED|EXHAUSTED(#30 감시)',
`created_at` datetime NOT NULL
);
CREATE TABLE `fee_policies` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`fee_type` varchar(20) NOT NULL COMMENT 'MERCHANT_PAYMENT|MERCHANT_PAYOUT|USER_DEPOSIT|USER_WITHDRAW|USER_GIFT|USER_EXCHANGE(포인트전환, 구현보류)',
`scope` varchar(15) NOT NULL COMMENT 'GLOBAL|USER|MERCHANT',
`target_id` bigint,
`rate` decimal(7,4) NOT NULL DEFAULT 0 COMMENT '정률% - 정액과 합산 조합(#25)',
`fixed_amount` decimal(15,0) NOT NULL DEFAULT 0
);
CREATE TABLE `limit_policies` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`limit_type` varchar(15) NOT NULL COMMENT 'DEPOSIT|BALANCE|WITHDRAW|GIFT|PAYMENT(#37)',
`window` varchar(10) NOT NULL COMMENT 'PER_TXN|DAILY|MONTHLY|CAP(보유)',
`scope` varchar(15) NOT NULL,
`target_id` bigint,
`amount` decimal(15,0) NOT NULL COMMENT 'BALANCE는 법정 상한 초과 설정 차단(앱 검증)'
);
CREATE TABLE `payout_policies` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`scope` varchar(20) NOT NULL COMMENT 'GLOBAL_USER|GLOBAL_MERCHANT|USER|MERCHANT',
`target_id` bigint,
`delay_days` int NOT NULL COMMENT '신청 +N일. **매장(GLOBAL_MERCHANT) 기본 0 = 즉시 정산**(발주 확정 2026-07-22, 가맹점 출금요청 시 즉각). 사용자 출금만 대기 가능',
`execute_time` time NOT NULL COMMENT '실행 시각 HH:mm (KST). delay_days=0이면 무시(즉시)'
);
CREATE TABLE `cancel_policies` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`scope` varchar(15) NOT NULL COMMENT 'GLOBAL|MERCHANT',
`target_id` bigint,
`days` int NOT NULL COMMENT '결제 후 N일 - 거래에 cancelable_until 스냅샷'
);
CREATE TABLE `card_quota_policies` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`scope` varchar(15) NOT NULL COMMENT 'GLOBAL|USER(#43)',
`target_id` bigint,
`max_cards` int NOT NULL COMMENT 'ACTIVE 카드 기준'
);
CREATE TABLE `point_expiry_policies` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`lot_type` varchar(10) UNIQUE NOT NULL COMMENT 'DEPOSIT|REWARD',
`months` int NOT NULL COMMENT '충전성 값은 법무 확인 후 설정(#42)'
);
CREATE TABLE `global_settings` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`skey` varchar(50) UNIQUE NOT NULL COMMENT 'REJOIN_WAIT_DAYS|CPM_CONFIRM_THRESHOLD|MAINTENANCE_ON|MAINTENANCE_MSG|MAINTENANCE_UNTIL|MIN_APP_VERSION_IOS|MIN_APP_VERSION_ANDROID|BANK_COOLDOWN_HOURS|FORFEIT_MAX_AMOUNT(탈퇴 소액포기 상한) 등 단일값 설정',
`svalue` varchar(500) NOT NULL,
`updated_by` bigint,
`updated_at` datetime NOT NULL
);
CREATE TABLE `fds_rules` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`code` varchar(30) UNIQUE NOT NULL COMMENT 'PASSTHROUGH|GIFT_CONCENTRATION|SELF_PAYMENT|CANCEL_ABUSE|POST_CHANGE_WITHDRAW|ENUMERATION|MULTI_ACCOUNT_DEVICE|NIGHT_LARGE(#38 8종)',
`name` varchar(100) NOT NULL,
`params` text NOT NULL COMMENT 'JSON: 임계값·시간창 - admin에서 조정(배포 불필요)',
`action` varchar(10) NOT NULL COMMENT 'BLOCK|HOLD|ALERT|MONITOR',
`enabled` boolean NOT NULL DEFAULT true,
`updated_at` datetime NOT NULL
);
CREATE TABLE `fds_alerts` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`rule_id` bigint NOT NULL,
`principal_type` varchar(10) NOT NULL,
`principal_id` bigint NOT NULL,
`txn_id` bigint COMMENT '관련 거래',
`detail` text COMMENT '탐지 근거 JSON(증거 보존)',
`status` varchar(20) NOT NULL DEFAULT 'OPEN' COMMENT 'OPEN|REVIEWED|ACTIONED|DISMISSED',
`reviewed_by` bigint,
`reviewed_at` datetime,
`review_note` varchar(500),
`created_at` datetime NOT NULL
);
CREATE TABLE `notices` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`kind` varchar(10) NOT NULL COMMENT 'NOTICE|EVENT(#18)',
`title` varchar(200) NOT NULL,
`body` text NOT NULL,
`starts_at` datetime,
`ends_at` datetime,
`status` varchar(10) NOT NULL DEFAULT 'DRAFT' COMMENT 'DRAFT|PUBLISHED|HIDDEN',
`created_by` bigint,
`created_at` datetime NOT NULL
);
CREATE TABLE `banners` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`image_path` varchar(300) NOT NULL,
`link_type` varchar(10) NOT NULL COMMENT 'NOTICE|EVENT|URL|NONE - 외부 URL 허용(승인 정책)',
`link_target` varchar(500),
`sort_order` int NOT NULL DEFAULT 0,
`starts_at` datetime,
`ends_at` datetime,
`status` varchar(10) NOT NULL DEFAULT 'DRAFT'
);
CREATE TABLE `faqs` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`category` varchar(30) NOT NULL,
`question` varchar(300) NOT NULL,
`answer` text NOT NULL,
`sort_order` int NOT NULL DEFAULT 0,
`status` varchar(10) NOT NULL DEFAULT 'PUBLISHED'
);
CREATE TABLE `inquiries` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`principal_type` varchar(10) NOT NULL COMMENT 'USER|MERCHANT',
`principal_id` bigint NOT NULL,
`title` varchar(200) NOT NULL,
`body` text NOT NULL,
`status` varchar(10) NOT NULL DEFAULT 'OPEN' COMMENT 'OPEN|ANSWERED|CLOSED',
`answer` text,
`answered_by` bigint,
`answered_at` datetime COMMENT '답변 시 푸시 통지',
`created_at` datetime NOT NULL
);
CREATE TABLE `inquiry_attachments` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`inquiry_id` bigint NOT NULL,
`file_path` varchar(300) NOT NULL COMMENT '웹루트 밖 비공개 저장. 서버 재인코딩·EXIF 제거 후',
`created_at` datetime NOT NULL
);
CREATE TABLE `policy_documents` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`kind` varchar(10) NOT NULL COMMENT 'TERMS|PRIVACY(#21)',
`version` varchar(20) NOT NULL,
`body` text NOT NULL,
`effective_at` datetime NOT NULL COMMENT '시행일 - 사전 고지·예약 게시',
`requires_reconsent` boolean NOT NULL DEFAULT false COMMENT '개정 건별 재동의 여부 admin 선택(승인 정책)',
`status` varchar(10) NOT NULL DEFAULT 'DRAFT' COMMENT 'DRAFT|ACTIVE|ARCHIVED - 과거 버전 영구 보존'
);
CREATE TABLE `policy_agreements` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`principal_type` varchar(10) NOT NULL,
`principal_id` bigint NOT NULL,
`policy_id` bigint NOT NULL,
`agreed_at` datetime NOT NULL COMMENT '분쟁 대비 증빙'
);
CREATE TABLE `push_tokens` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`principal_type` varchar(10) NOT NULL,
`principal_id` bigint NOT NULL,
`platform` varchar(10) NOT NULL,
`token` varchar(255) UNIQUE NOT NULL COMMENT 'FCM(#20)',
`updated_at` datetime NOT NULL
);
CREATE TABLE `push_campaigns` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`title` varchar(100) NOT NULL,
`body` varchar(500) NOT NULL,
`target` varchar(20) NOT NULL COMMENT 'ALL|IOS|ANDROID|SELECTED - SELECTED 시 대상은 push_campaign_targets(JSON 금지 원칙)',
`is_ad` boolean NOT NULL COMMENT '광고성=수신동의자만+야간(21~08) 차단 자동',
`scheduled_at` datetime,
`sent_count` int DEFAULT 0,
`fail_count` int DEFAULT 0,
`status` varchar(10) NOT NULL DEFAULT 'DRAFT',
`created_by` bigint,
`created_at` datetime NOT NULL
);
CREATE TABLE `push_campaign_targets` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`campaign_id` bigint NOT NULL,
`principal_type` varchar(10) NOT NULL,
`principal_id` bigint NOT NULL,
`sent_at` datetime,
`result` varchar(20) COMMENT 'SENT|FAILED|NO_TOKEN|OPTED_OUT'
);
CREATE TABLE `notification_inbox` (
`id` bigint PRIMARY KEY AUTO_INCREMENT COMMENT '실PK (id, created_at) 월 파티션 - 30일 보존 배치 삭제(#46 확정)',
`principal_type` varchar(10) NOT NULL,
`principal_id` bigint NOT NULL,
`ntype` varchar(20) NOT NULL COMMENT 'TXN|GIFT|SETTLEMENT|INQUIRY|CAMPAIGN 등',
`title` varchar(100) NOT NULL,
`body` varchar(500),
`deeplink` varchar(200),
`read_at` datetime,
`created_at` datetime NOT NULL
);
CREATE TABLE `monthly_statements` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`card_id` bigint NOT NULL,
`yyyymm` char(6) NOT NULL,
`deposit_sum` decimal(15,0) NOT NULL DEFAULT 0,
`withdraw_sum` decimal(15,0) NOT NULL DEFAULT 0,
`payment_sum` decimal(15,0) NOT NULL DEFAULT 0,
`gift_sent_sum` decimal(15,0) NOT NULL DEFAULT 0,
`gift_recv_sum` decimal(15,0) NOT NULL DEFAULT 0,
`expire_sum` decimal(15,0) NOT NULL DEFAULT 0,
`fee_sum` decimal(15,0) NOT NULL DEFAULT 0,
`closing_balance` decimal(15,0) NOT NULL COMMENT '월말 잔액 - 월 단위 대사 겸용(#23)',
`created_at` datetime NOT NULL
);
CREATE TABLE `merchant_monthly_statements` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`merchant_id` bigint NOT NULL,
`yyyymm` char(6) NOT NULL,
`sales_count` int NOT NULL DEFAULT 0,
`sales_sum` decimal(15,0) NOT NULL DEFAULT 0,
`cancel_count` int NOT NULL DEFAULT 0,
`cancel_sum` decimal(15,0) NOT NULL DEFAULT 0,
`supply_sum` decimal(15,0) NOT NULL DEFAULT 0 COMMENT '공급가액(#24 부가세 분리)',
`vat_sum` decimal(15,0) NOT NULL DEFAULT 0,
`fee_sum` decimal(15,0) NOT NULL DEFAULT 0 COMMENT '결제+정산 수수료',
`payout_sum` decimal(15,0) NOT NULL DEFAULT 0,
`closing_balance` decimal(15,0) NOT NULL,
`created_at` datetime NOT NULL
);
CREATE TABLE `outbox_jobs` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`job_type` varchar(30) NOT NULL COMMENT 'PUSH|WEBHOOK|RECONCILE|GIFT_EXPIRE|LOT_EXPIRE|SETTLEMENT|STATEMENT|PARTITION 등 - 큐 미사용(#22) 단일 패턴',
`payload` text NOT NULL,
`run_after` datetime NOT NULL,
`attempts` int NOT NULL DEFAULT 0,
`max_attempts` int NOT NULL DEFAULT 10,
`status` varchar(10) NOT NULL DEFAULT 'PENDING' COMMENT 'PENDING|RUNNING|DONE|EXHAUSTED',
`last_error` varchar(500),
`created_at` datetime NOT NULL
);
CREATE TABLE `audit_logs` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`actor_type` varchar(10) NOT NULL COMMENT 'ADMIN|SYSTEM',
`actor_id` bigint,
`action` varchar(50) NOT NULL COMMENT 'MERCHANT_APPROVE|FORCE_SUSPEND|POLICY_CHANGE|MANUAL_MATCH|OTP_RESET|IP_ADD 등',
`target_type` varchar(30),
`target_id` bigint,
`detail` text COMMENT '변경 전후 값 JSON',
`reason` varchar(500) COMMENT '강제 조치·조정은 사유 필수(#40·#48)',
`ip` varchar(45),
`created_at` datetime NOT NULL
);
CREATE TABLE `pii_access_logs` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`admin_id` bigint NOT NULL,
`target_type` varchar(10) NOT NULL,
`target_id` bigint NOT NULL,
`fields` varchar(200) NOT NULL COMMENT '열람 항목(전화번호·계좌 등)',
`purpose` varchar(200),
`created_at` datetime NOT NULL
);
CREATE TABLE `external_api_logs` (
`id` bigint PRIMARY KEY AUTO_INCREMENT COMMENT '실PK (id, created_at) 월 파티션',
`provider` varchar(30) NOT NULL COMMENT 'FIRMBANK|NICE|NTS(국세청)|FCM|WEBHOOK_OUT 등',
`operation` varchar(50) NOT NULL COMMENT 'TRANSFER|BALANCE_QUERY|VERIFY_NAME|SEND_PUSH 등',
`txn_id` bigint COMMENT '관련 거래 - 리컨실러·분쟁 추적의 핵심 연결고리',
`request_body` text COMMENT '민감정보 마스킹 후 저장',
`response_body` text COMMENT '계좌번호·CI류만 선별 마스킹 후 보존(결과코드·금액·거래참조는 원문 - 증거 가치 유지, 감사 확정)',
`http_status` int,
`result_code` varchar(30) COMMENT '기관 응답 코드',
`latency_ms` int,
`created_at` datetime NOT NULL
);
CREATE TABLE `reject_logs` (
`id` bigint PRIMARY KEY AUTO_INCREMENT COMMENT '실PK (id, created_at) 월 파티션',
`principal_type` varchar(10) NOT NULL COMMENT 'USER|MERCHANT|ANON(비인증 시도)',
`principal_id` bigint COMMENT '비인증 시도는 NULL(감사 보완)',
`ip` varchar(45) COMMENT 'FDS 열거(ENUMERATION) 룰 - IP 기준 탐지',
`action` varchar(30) NOT NULL COMMENT 'PAYMENT|WITHDRAW|GIFT|DEPOSIT_MATCH|QR_VERIFY|LOGIN|CARD_ISSUE 등',
`reject_code` varchar(40) NOT NULL COMMENT 'LIMIT_EXCEEDED|INSUFFICIENT_BALANCE|FDS_BLOCK|QR_EXPIRED|CANCEL_WINDOW_EXPIRED|PIN_LOCKED 등',
`context` text COMMENT '판정 근거 JSON(한도값·잔액·룰ID 등 당시 상태)',
`created_at` datetime NOT NULL
);
CREATE TABLE `integrity_check_runs` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`check_type` varchar(30) NOT NULL COMMENT 'DOUBLE_ENTRY|BALANCE_CHAIN|WALLET_CACHE|SEGREGATION|STALE_TXN|OUTBOX_HEALTH|ORPHAN_LEDGER(고아 원장)|SUMMARY_RECON(집계-원장 대사)|SYSTEM_WALLET(시스템 지갑 SUM 검증)',
`scope` varchar(100) COMMENT '검사 범위(일자·파티션)',
`status` varchar(10) NOT NULL COMMENT 'PASS|FAIL|RUNNING',
`checked_count` bigint COMMENT '검사 행 수',
`anomaly_count` int NOT NULL DEFAULT 0,
`started_at` datetime NOT NULL,
`finished_at` datetime
);
CREATE TABLE `integrity_findings` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`run_id` bigint NOT NULL,
`target_type` varchar(30) NOT NULL COMMENT 'WALLET|TXN|LOT 등',
`target_id` bigint NOT NULL,
`detail` text NOT NULL COMMENT '기대값 vs 실제값',
`status` varchar(20) NOT NULL DEFAULT 'OPEN' COMMENT 'OPEN|INVESTIGATING|RESOLVED(조정 역분개 참조)|FALSE_POSITIVE',
`resolved_by` bigint,
`resolved_at` datetime,
`resolution_note` varchar(500)
);
CREATE TABLE `daily_wallet_snapshots` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`snap_date` date NOT NULL,
`wallet_id` bigint NOT NULL,
`balance` decimal(15,0) NOT NULL,
`last_ledger_id` bigint NOT NULL COMMENT '스냅샷 시점의 마지막 원장 행 - 체인 검증 재개점'
);
CREATE TABLE `daily_summaries` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`summary_date` date NOT NULL,
`metric` varchar(40) NOT NULL COMMENT 'DEPOSIT_SUM|WITHDRAW_SUM|PAYMENT_SUM|GIFT_SUM|CANCEL_SUM|FEE_SUM|EXPIRE_SUM|NEW_USERS|NEW_MERCHANTS|ACTIVE_USERS|FDS_ALERTS|REJECTS 등',
`value` decimal(18,0) NOT NULL
);
CREATE TABLE `app_error_logs` (
`id` bigint PRIMARY KEY AUTO_INCREMENT COMMENT '실PK (id, created_at) 월 파티션',
`severity` varchar(10) NOT NULL COMMENT 'ERROR|CRITICAL',
`source` varchar(30) NOT NULL COMMENT 'API|WORKER|BATCH',
`error_code` varchar(50),
`message` varchar(1000) NOT NULL,
`trace_id` varchar(64) COMMENT '파일 로그(전체 스택)와의 연결 키',
`txn_id` bigint,
`created_at` datetime NOT NULL
);
CREATE TABLE `merchant_daily_summaries` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`merchant_id` bigint NOT NULL,
`summary_date` date NOT NULL,
`sales_count` int NOT NULL DEFAULT 0,
`sales_sum` decimal(15,0) NOT NULL DEFAULT 0,
`cancel_count` int NOT NULL DEFAULT 0,
`cancel_sum` decimal(15,0) NOT NULL DEFAULT 0,
`supply_sum` decimal(15,0) NOT NULL DEFAULT 0,
`vat_sum` decimal(15,0) NOT NULL DEFAULT 0,
`fee_sum` decimal(15,0) NOT NULL DEFAULT 0
);
CREATE TABLE `hourly_summaries` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`stat_hour` datetime NOT NULL COMMENT '정시 절단(KST)',
`metric` varchar(40) NOT NULL,
`value` decimal(18,0) NOT NULL
);
CREATE TABLE `exchange_providers` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`code` varchar(20) UNIQUE NOT NULL COMMENT '제휴 포인트 공급자 코드',
`name` varchar(100) NOT NULL,
`rate_in` decimal(10,4) COMMENT '외부 포인트 → nestpay 전환율',
`rate_out` decimal(10,4) COMMENT 'nestpay → 외부 포인트 역전환율(허용 시)',
`status` varchar(10) NOT NULL DEFAULT 'INACTIVE' COMMENT 'INACTIVE|ACTIVE - 구현 보류, 연동 요청 시 활성',
`created_at` datetime NOT NULL
);
CREATE UNIQUE INDEX `ux_users_active_ci` ON `users` (`active_ci`);
CREATE INDEX `ix_users_ci` ON `users` (`ci_hash`);
CREATE INDEX `ix_users_phone_hash` ON `users` (`phone_hash`);
CREATE INDEX `ix_users_status` ON `users` (`status`, `created_at`);
CREATE INDEX `ix_merchants_status` ON `merchants` (`status`);
CREATE UNIQUE INDEX `ux_pin_principal` ON `auth_pins` (`principal_type`, `principal_id`);
CREATE INDEX `ix_passkey_principal` ON `auth_passkeys` (`principal_type`, `principal_id`);
CREATE INDEX `ix_device_principal` ON `auth_devices` (`principal_type`, `principal_id`, `status`);
CREATE UNIQUE INDEX `ux_device_uid` ON `auth_devices` (`principal_type`, `principal_id`, `device_uid`);
CREATE INDEX `ix_device_uid` ON `auth_devices` (`device_uid`);
CREATE INDEX `ix_login_hist` ON `login_histories` (`principal_type`, `principal_id`, `created_at`);
CREATE INDEX `ix_bank_owner` ON `bank_accounts` (`owner_type`, `owner_id`, `status`);
CREATE INDEX `ix_cards_user` ON `cards` (`user_id`, `status`);
CREATE INDEX `ix_wallets_owner_type` ON `wallets` (`owner_type`);
CREATE INDEX `ix_lots_fifo` ON `point_lots` (`wallet_id`, `expires_at`);
CREATE INDEX `ix_lots_expiry` ON `point_lots` (`expires_at`, `amount_remaining`);
CREATE INDEX `ix_lots_type_remaining` ON `point_lots` (`lot_type`, `amount_remaining`);
CREATE INDEX `ix_txn_card` ON `transactions` (`card_id`, `created_at`);
CREATE INDEX `ix_txn_merchant` ON `transactions` (`merchant_id`, `created_at`);
CREATE INDEX `ix_txn_status` ON `transactions` (`status`, `created_at`);
CREATE UNIQUE INDEX `ux_txn_idem` ON `transactions` (`initiator_type`, `initiator_id`, `idempotency_key`);
CREATE INDEX `ix_txn_initiator_date` ON `transactions` (`initiator_type`, `initiator_id`, `type`, `created_at`);
CREATE INDEX `ix_txn_bank_ref` ON `transactions` (`bank_tran_ref`);
CREATE INDEX `ix_txn_created` ON `transactions` (`created_at`);
CREATE INDEX `ix_txn_related` ON `transactions` (`related_txn_id`);
CREATE INDEX `ix_txn_withdraw_sched` ON `transactions` (`type`, `status`, `scheduled_at`);
CREATE INDEX `ix_txn_initiator_created` ON `transactions` (`initiator_type`, `initiator_id`, `created_at`);
CREATE INDEX `ix_txn_counterparty` ON `transactions` (`counterparty_card_id`, `created_at`);
CREATE INDEX `ix_ledger_wallet_chain` ON `ledger_entries` (`wallet_id`, `id`);
CREATE INDEX `ix_ledger_txn` ON `ledger_entries` (`txn_id`);
CREATE INDEX `ix_alloc_lot` ON `lot_allocations` (`lot_id`);
CREATE INDEX `ix_alloc_entry` ON `lot_allocations` (`ledger_entry_id`);
CREATE UNIQUE INDEX `ux_notice_ref` ON `deposit_notices` (`source`, `dedup_key`);
CREATE INDEX `ix_notice_status` ON `deposit_notices` (`status`, `received_at`);
CREATE INDEX `ix_gift_expiry` ON `gift_links` (`status`, `expires_at`);
CREATE INDEX `ix_gift_sender` ON `gift_links` (`sender_card_id`, `status`);
CREATE INDEX `ix_qr_merchant` ON `merchant_qrs` (`merchant_id`, `status`);
CREATE INDEX `ix_apicred_queue` ON `merchant_api_credentials` (`status`, `requested_at`);
CREATE UNIQUE INDEX `ux_pg_order` ON `pg_orders` (`merchant_id`, `order_no`);
CREATE INDEX `ix_pg_status` ON `pg_orders` (`status`, `created_at`);
CREATE INDEX `ix_webhook_retry` ON `webhook_deliveries` (`status`, `next_retry_at`);
CREATE UNIQUE INDEX `ux_fee_policy` ON `fee_policies` (`fee_type`, `scope`, `target_id`);
CREATE UNIQUE INDEX `ux_limit_policy` ON `limit_policies` (`limit_type`, `window`, `scope`, `target_id`);
CREATE UNIQUE INDEX `ux_payout_policy` ON `payout_policies` (`scope`, `target_id`);
CREATE UNIQUE INDEX `ux_cancel_policy` ON `cancel_policies` (`scope`, `target_id`);
CREATE UNIQUE INDEX `ux_quota_policy` ON `card_quota_policies` (`scope`, `target_id`);
CREATE INDEX `ix_fds_queue` ON `fds_alerts` (`status`, `created_at`);
CREATE INDEX `ix_notice_pub` ON `notices` (`kind`, `status`, `starts_at`);
CREATE INDEX `ix_inquiry_owner` ON `inquiries` (`principal_type`, `principal_id`, `created_at`);
CREATE INDEX `ix_inquiry_queue` ON `inquiries` (`status`, `created_at`);
CREATE UNIQUE INDEX `ux_policy_ver` ON `policy_documents` (`kind`, `version`);
CREATE UNIQUE INDEX `ux_agreement` ON `policy_agreements` (`principal_type`, `principal_id`, `policy_id`);
CREATE INDEX `ix_push_principal` ON `push_tokens` (`principal_type`, `principal_id`);
CREATE UNIQUE INDEX `ux_campaign_target` ON `push_campaign_targets` (`campaign_id`, `principal_type`, `principal_id`);
CREATE INDEX `ix_inbox_owner` ON `notification_inbox` (`principal_type`, `principal_id`, `created_at`);
CREATE UNIQUE INDEX `ux_stmt_card_month` ON `monthly_statements` (`card_id`, `yyyymm`);
CREATE UNIQUE INDEX `ux_mstmt_month` ON `merchant_monthly_statements` (`merchant_id`, `yyyymm`);
CREATE INDEX `ix_outbox_poll` ON `outbox_jobs` (`status`, `run_after`);
CREATE INDEX `ix_audit_actor` ON `audit_logs` (`actor_type`, `actor_id`, `created_at`);
CREATE INDEX `ix_audit_target` ON `audit_logs` (`target_type`, `target_id`);
CREATE INDEX `ix_pii_admin` ON `pii_access_logs` (`admin_id`, `created_at`);
CREATE INDEX `ix_pii_target` ON `pii_access_logs` (`target_type`, `target_id`, `created_at`);
CREATE INDEX `ix_extapi_provider` ON `external_api_logs` (`provider`, `created_at`);
CREATE INDEX `ix_extapi_txn` ON `external_api_logs` (`txn_id`);
CREATE INDEX `ix_extapi_result` ON `external_api_logs` (`result_code`, `created_at`);
CREATE INDEX `ix_reject_principal` ON `reject_logs` (`principal_type`, `principal_id`, `created_at`);
CREATE INDEX `ix_reject_code` ON `reject_logs` (`reject_code`, `created_at`);
CREATE INDEX `ix_reject_ip` ON `reject_logs` (`ip`, `created_at`);
CREATE INDEX `ix_check_history` ON `integrity_check_runs` (`check_type`, `started_at`);
CREATE INDEX `ix_finding_open` ON `integrity_findings` (`status`);
CREATE UNIQUE INDEX `ux_snap_day_wallet` ON `daily_wallet_snapshots` (`snap_date`, `wallet_id`);
CREATE INDEX `ix_snap_wallet_latest` ON `daily_wallet_snapshots` (`wallet_id`, `snap_date`);
CREATE UNIQUE INDEX `ux_summary_day_metric` ON `daily_summaries` (`summary_date`, `metric`);
CREATE INDEX `ix_error_severity` ON `app_error_logs` (`severity`, `created_at`);
CREATE UNIQUE INDEX `ux_mds_merchant_day` ON `merchant_daily_summaries` (`merchant_id`, `summary_date`);
CREATE UNIQUE INDEX `ux_hourly` ON `hourly_summaries` (`stat_hour`, `metric`);
ALTER TABLE `users` COMMENT = 'SYSTEM VERSIONING. 탈퇴해도 행 유지(#39, 법정 보존)';
ALTER TABLE `merchants` COMMENT = 'SYSTEM VERSIONING. 대표자 변경=재심사(2차 항목 3)';
ALTER TABLE `admin_allowed_ips` COMMENT = '마지막 1건 삭제 불가·본인 IP 삭제 경고는 앱 로직';
ALTER TABLE `login_histories` COMMENT = '사용자 로그인 이력 화면(2차 항목 9) + 보안 감사. 월 파티션(실PK id,created_at)·5년 DROP·방어 트리거';
ALTER TABLE `bank_accounts` COMMENT = '출금은 ACTIVE 계좌 1건으로만(#36). ACTIVE 1건 보장은 앱 트랜잭션';
ALTER TABLE `cards` COMMENT = 'SYSTEM VERSIONING. 발급 수량 카운트 = ACTIVE만(#43). 카드번호 = 지갑 식별자(#41)';
ALTER TABLE `wallets` COMMENT = 'CHECK: owner_type별 해당 참조 컬럼만 NOT NULL(마이그레이션 정의)';
ALTER TABLE `transactions` COMMENT = 'append-only(취소=CANCEL 신규 행). 비파티션(UNIQUE 멱등 보장 우선) - 장기 증가 대응은 연 단위 아카이브 절차로';
ALTER TABLE `ledger_entries` COMMENT = 'append-only 절대(#29): UPDATE/DELETE 방어 트리거 + 권한 미부여. 월 파티션';
ALTER TABLE `lot_allocations` COMMENT = '한 차감이 복수 로트에 걸친 배분 기록 - 유효기간 FIFO 소진·취소 복원 근거(환급은 소스 무관 전체 DEPOSIT 잔액 기준이라 유형판정 미사용)';
ALTER TABLE `unmatched_deposits` COMMENT = '입금 즉시 UNMATCHED 시스템 지갑에 원장 계상 - 장부 밖 돈 없음';
ALTER TABLE `merchant_api_credentials` COMMENT = 'LIVE 전환 조건 = status=APPROVED + 3개 test_*_ok_at 모두 존재(앱 강제). SYSTEM VERSIONING(웹훅 URL·모드 변경 이력)';
ALTER TABLE `limit_policies` COMMENT = '집행은 사용자 전 카드 합산(#37)';
ALTER TABLE `payout_policies` COMMENT = '매장 정산 대기(+N일) 구조·로직 완전 구현 유지(제거 금지 - 향후 추가 비용 방지, 발주 2026-07-22). 매장 기본 delay=0(즉시 정산). 값만 바꾸면 +1일/+2일 대기 즉시 적용. scheduled_at = 신청 + delay일 HH시(delay 0이면 NOW → 배치가 바로 집행)';
ALTER TABLE `global_settings` COMMENT = 'SYSTEM VERSIONING - 설정 변경 이력 자동 보존';
ALTER TABLE `fds_rules` COMMENT = 'SYSTEM VERSIONING';
ALTER TABLE `inquiry_attachments` COMMENT = '이미지만, 최대 5장/장당 10MB(#19 확정)';
ALTER TABLE `push_campaign_targets` COMMENT = '지정 발송 대상 + 개별 발송 결과 - 도달 분석';
ALTER TABLE `notification_inbox` COMMENT = '푸시 발송과 동일 outbox 작업에서 원자적 기록';
ALTER TABLE `merchant_monthly_statements` COMMENT = '월 정산서(#24-5) - 카드사 동일 수준, 부가세 포함';
ALTER TABLE `outbox_jobs` COMMENT = '원장 트랜잭션과 같은 커밋에 INSERT - 후속작업 유실 불가(#29)';
ALTER TABLE `audit_logs` COMMENT = 'append-only. admin 전 조회·변경 행위 기록';
ALTER TABLE `pii_access_logs` COMMENT = '개인신용정보 접근기록(감독규정). 월 파티션(실PK id,created_at)·5년 DROP·방어 트리거. 기록 지점=*_enc 복호화 표시 API 전부(카탈로그 17.9b 규칙)';
ALTER TABLE `external_api_logs` COMMENT = '모든 대외 호출의 요청·응답 전건 기록 - "은행은 됐다는데 우리는 왜 실패?"의 판정 근거';
ALTER TABLE `reject_logs` COMMENT = '거래가 생성되기 전 거절된 시도의 기록 - transactions에 없는 "안 된 일"의 분석용. CS 문의("왜 안 돼요") 즉답 근거';
ALTER TABLE `integrity_check_runs` COMMENT = '#30 모니터링 대시보드의 데이터 원천 - 검증 실행 자체의 이력(언제 무엇을 검사했고 결과가 무엇이었나)';
ALTER TABLE `integrity_findings` COMMENT = '이상 건 개별 추적 - 발견부터 해소(역분개 조정)까지의 생애주기 기록';
ALTER TABLE `daily_wallet_snapshots` COMMENT = '희소 스냅샷(감사 확정): 당일 원장 변동 지갑만 기록 + 월 1회 전량 베이스라인. 과거일 잔액 = 해당일 이전 최신 스냅샷. ODKU 멱등 적재';
ALTER TABLE `daily_summaries` COMMENT = 'admin 운영 대시보드·추이 그래프의 원천 - 원장 재집계 없이 즉시 조회';
ALTER TABLE `app_error_logs` COMMENT = '서버 예외 요약(운영자 화면·알림용). 전체 스택트레이스는 파일 로그 - trace_id로 연결';
ALTER TABLE `merchant_daily_summaries` COMMENT = '차트 집계 계층(감사 신설): 일 마감 배치가 최근 W일(=취소기한+출금대기 상한+1) 멱등 재계산(ODKU). 과거=본 테이블, 당일=ix_txn_merchant 실시간, 접합은 UNION(18장). 결측일 0채움은 Java';
ALTER TABLE `hourly_summaries` COMMENT = 'admin 전역 당일 시간대 추이(감사 신설) - 시간 배치 적재, 90일 보존 DELETE';
ALTER TABLE `exchange_providers` COMMENT = '#49 포인트 전환 - 설계 유지·구현 보류. 전환 유입분은 DEPOSIT 로트 계상, 별도관리 대상 여부 법무 확인';
ALTER TABLE `merchants` ADD FOREIGN KEY (`approved_by`) REFERENCES `admin_accounts` (`id`);
ALTER TABLE `merchant_accounts` ADD FOREIGN KEY (`merchant_id`) REFERENCES `merchants` (`id`);
ALTER TABLE `admin_allowed_ips` ADD FOREIGN KEY (`created_by`) REFERENCES `admin_accounts` (`id`);
ALTER TABLE `cards` ADD FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);
ALTER TABLE `cards` ADD FOREIGN KEY (`reissued_to`) REFERENCES `cards` (`id`);
ALTER TABLE `wallets` ADD FOREIGN KEY (`card_id`) REFERENCES `cards` (`id`);
ALTER TABLE `wallets` ADD FOREIGN KEY (`merchant_id`) REFERENCES `merchants` (`id`);
ALTER TABLE `point_lots` ADD FOREIGN KEY (`wallet_id`) REFERENCES `wallets` (`id`);
ALTER TABLE `point_lots` ADD FOREIGN KEY (`origin_lot_id`) REFERENCES `point_lots` (`id`);
ALTER TABLE `transactions` ADD FOREIGN KEY (`card_id`) REFERENCES `cards` (`id`);
ALTER TABLE `transactions` ADD FOREIGN KEY (`counterparty_card_id`) REFERENCES `cards` (`id`);
ALTER TABLE `transactions` ADD FOREIGN KEY (`merchant_id`) REFERENCES `merchants` (`id`);
ALTER TABLE `transactions` ADD FOREIGN KEY (`bank_account_id`) REFERENCES `bank_accounts` (`id`);
ALTER TABLE `ledger_entries` ADD FOREIGN KEY (`wallet_id`) REFERENCES `wallets` (`id`);
ALTER TABLE `lot_allocations` ADD FOREIGN KEY (`lot_id`) REFERENCES `point_lots` (`id`);
ALTER TABLE `deposit_identifiers` ADD FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);
ALTER TABLE `unmatched_deposits` ADD FOREIGN KEY (`notice_id`) REFERENCES `deposit_notices` (`id`);
ALTER TABLE `unmatched_deposits` ADD FOREIGN KEY (`resolved_by`) REFERENCES `admin_accounts` (`id`);
ALTER TABLE `gift_links` ADD FOREIGN KEY (`sender_card_id`) REFERENCES `cards` (`id`);
ALTER TABLE `gift_links` ADD FOREIGN KEY (`claimed_card_id`) REFERENCES `cards` (`id`);
ALTER TABLE `merchant_qrs` ADD FOREIGN KEY (`merchant_id`) REFERENCES `merchants` (`id`);
ALTER TABLE `merchant_api_credentials` ADD FOREIGN KEY (`merchant_id`) REFERENCES `merchants` (`id`);
ALTER TABLE `merchant_api_credentials` ADD FOREIGN KEY (`approved_by`) REFERENCES `admin_accounts` (`id`);
ALTER TABLE `pg_orders` ADD FOREIGN KEY (`merchant_id`) REFERENCES `merchants` (`id`);
ALTER TABLE `pg_orders` ADD FOREIGN KEY (`qr_id`) REFERENCES `merchant_qrs` (`id`);
ALTER TABLE `webhook_deliveries` ADD FOREIGN KEY (`merchant_id`) REFERENCES `merchants` (`id`);
ALTER TABLE `webhook_deliveries` ADD FOREIGN KEY (`pg_order_id`) REFERENCES `pg_orders` (`id`);
ALTER TABLE `global_settings` ADD FOREIGN KEY (`updated_by`) REFERENCES `admin_accounts` (`id`);
ALTER TABLE `fds_alerts` ADD FOREIGN KEY (`rule_id`) REFERENCES `fds_rules` (`id`);
ALTER TABLE `fds_alerts` ADD FOREIGN KEY (`reviewed_by`) REFERENCES `admin_accounts` (`id`);
ALTER TABLE `notices` ADD FOREIGN KEY (`created_by`) REFERENCES `admin_accounts` (`id`);
ALTER TABLE `inquiries` ADD FOREIGN KEY (`answered_by`) REFERENCES `admin_accounts` (`id`);
ALTER TABLE `inquiry_attachments` ADD FOREIGN KEY (`inquiry_id`) REFERENCES `inquiries` (`id`);
ALTER TABLE `policy_agreements` ADD FOREIGN KEY (`policy_id`) REFERENCES `policy_documents` (`id`);
ALTER TABLE `push_campaigns` ADD FOREIGN KEY (`created_by`) REFERENCES `admin_accounts` (`id`);
ALTER TABLE `push_campaign_targets` ADD FOREIGN KEY (`campaign_id`) REFERENCES `push_campaigns` (`id`);
ALTER TABLE `monthly_statements` ADD FOREIGN KEY (`card_id`) REFERENCES `cards` (`id`);
ALTER TABLE `merchant_monthly_statements` ADD FOREIGN KEY (`merchant_id`) REFERENCES `merchants` (`id`);
ALTER TABLE `pii_access_logs` ADD FOREIGN KEY (`admin_id`) REFERENCES `admin_accounts` (`id`);
ALTER TABLE `integrity_findings` ADD FOREIGN KEY (`run_id`) REFERENCES `integrity_check_runs` (`id`);
ALTER TABLE `integrity_findings` ADD FOREIGN KEY (`resolved_by`) REFERENCES `admin_accounts` (`id`);
ALTER TABLE `daily_wallet_snapshots` ADD FOREIGN KEY (`wallet_id`) REFERENCES `wallets` (`id`);
ALTER TABLE `merchant_daily_summaries` ADD FOREIGN KEY (`merchant_id`) REFERENCES `merchants` (`id`);
-- ═══════════════════════════════════════════════════════════
-- V2__mariadb_features.sql
-- ═══════════════════════════════════════════════════════════
-- =====================================================================
-- V2: MariaDB 고유 기능 적용 (V1 = schema.dbml 생성 DDL)
-- 전수 감사(2026-07-21) 반영판 - MariaDB 11.4 실DB 재검증 대상
-- =====================================================================
-- ---------------------------------------------------------------------
-- 1. CHECK 제약 (돈 관련 불변식)
-- 시스템 지갑은 음수 허용(SETTLEMENT_CLEARING=은행 대사 계정, 감사 확정 규약)
-- ---------------------------------------------------------------------
ALTER TABLE wallets
ADD CONSTRAINT chk_wallet_balance CHECK (owner_type = 'SYSTEM' OR balance >= 0),
ADD CONSTRAINT chk_wallet_owner CHECK (
(owner_type = 'USER_CARD' AND card_id IS NOT NULL AND merchant_id IS NULL AND system_code IS NULL) OR
(owner_type = 'MERCHANT' AND merchant_id IS NOT NULL AND card_id IS NULL AND system_code IS NULL) OR
(owner_type = 'SYSTEM' AND system_code IS NOT NULL AND card_id IS NULL AND merchant_id IS NULL)
);
ALTER TABLE point_lots
ADD CONSTRAINT chk_lot_amounts CHECK (amount_remaining >= 0 AND amount_remaining <= amount_init AND amount_init > 0);
ALTER TABLE ledger_entries
ADD CONSTRAINT chk_ledger_amount CHECK (amount > 0),
ADD CONSTRAINT chk_ledger_direction CHECK (direction IN ('DR','CR'));
-- balance_after NULL 허용은 SYSTEM 지갑 leg 전용(교차 테이블 CHECK 불가 → SYSTEM_WALLET 대사가 검증)
ALTER TABLE transactions
ADD CONSTRAINT chk_txn_amount CHECK (amount > 0),
ADD CONSTRAINT chk_txn_fee CHECK (fee_amount >= 0);
ALTER TABLE lot_allocations
ADD CONSTRAINT chk_alloc_amount CHECK (amount > 0);
-- ---------------------------------------------------------------------
-- 2. 월별 RANGE 파티셔닝 (7종 - 감사 반영: login_histories·pii_access_logs 편입)
-- * 파티션 테이블 FK 불가 → 해당 FK 제거(논리 참조 + 고아 검사 대사)
-- * transactions·deposit_notices는 UNIQUE 멱등 우선으로 비파티션(아카이브 런북)
-- ---------------------------------------------------------------------
ALTER TABLE ledger_entries DROP FOREIGN KEY ledger_entries_ibfk_1;
ALTER TABLE ledger_entries DROP PRIMARY KEY, ADD PRIMARY KEY (id, created_at);
ALTER TABLE ledger_entries PARTITION BY RANGE (TO_DAYS(created_at)) (
PARTITION p202607 VALUES LESS THAN (TO_DAYS('2026-08-01')),
PARTITION p202608 VALUES LESS THAN (TO_DAYS('2026-09-01')),
PARTITION p_max VALUES LESS THAN MAXVALUE
);
ALTER TABLE notification_inbox DROP PRIMARY KEY, ADD PRIMARY KEY (id, created_at);
ALTER TABLE notification_inbox PARTITION BY RANGE (TO_DAYS(created_at)) (
PARTITION p202607 VALUES LESS THAN (TO_DAYS('2026-08-01')),
PARTITION p202608 VALUES LESS THAN (TO_DAYS('2026-09-01')),
PARTITION p_max VALUES LESS THAN MAXVALUE
);
ALTER TABLE external_api_logs DROP PRIMARY KEY, ADD PRIMARY KEY (id, created_at);
ALTER TABLE external_api_logs PARTITION BY RANGE (TO_DAYS(created_at)) (
PARTITION p202607 VALUES LESS THAN (TO_DAYS('2026-08-01')),
PARTITION p202608 VALUES LESS THAN (TO_DAYS('2026-09-01')),
PARTITION p_max VALUES LESS THAN MAXVALUE
);
ALTER TABLE reject_logs DROP PRIMARY KEY, ADD PRIMARY KEY (id, created_at);
ALTER TABLE reject_logs PARTITION BY RANGE (TO_DAYS(created_at)) (
PARTITION p202607 VALUES LESS THAN (TO_DAYS('2026-08-01')),
PARTITION p202608 VALUES LESS THAN (TO_DAYS('2026-09-01')),
PARTITION p_max VALUES LESS THAN MAXVALUE
);
ALTER TABLE app_error_logs DROP PRIMARY KEY, ADD PRIMARY KEY (id, created_at);
ALTER TABLE app_error_logs PARTITION BY RANGE (TO_DAYS(created_at)) (
PARTITION p202607 VALUES LESS THAN (TO_DAYS('2026-08-01')),
PARTITION p202608 VALUES LESS THAN (TO_DAYS('2026-09-01')),
PARTITION p_max VALUES LESS THAN MAXVALUE
);
ALTER TABLE login_histories DROP PRIMARY KEY, ADD PRIMARY KEY (id, created_at);
ALTER TABLE login_histories PARTITION BY RANGE (TO_DAYS(created_at)) (
PARTITION p202607 VALUES LESS THAN (TO_DAYS('2026-08-01')),
PARTITION p202608 VALUES LESS THAN (TO_DAYS('2026-09-01')),
PARTITION p_max VALUES LESS THAN MAXVALUE
);
ALTER TABLE pii_access_logs DROP FOREIGN KEY pii_access_logs_ibfk_1;
ALTER TABLE pii_access_logs DROP PRIMARY KEY, ADD PRIMARY KEY (id, created_at);
ALTER TABLE pii_access_logs PARTITION BY RANGE (TO_DAYS(created_at)) (
PARTITION p202607 VALUES LESS THAN (TO_DAYS('2026-08-01')),
PARTITION p202608 VALUES LESS THAN (TO_DAYS('2026-09-01')),
PARTITION p_max VALUES LESS THAN MAXVALUE
);
-- ---------------------------------------------------------------------
-- 3. SYSTEM VERSIONING (17종 - 감사 반영: 보안 가변 마스터 4종 추가)
-- ---------------------------------------------------------------------
ALTER TABLE users ADD SYSTEM VERSIONING;
ALTER TABLE merchants ADD SYSTEM VERSIONING;
ALTER TABLE cards ADD SYSTEM VERSIONING;
ALTER TABLE admin_accounts ADD SYSTEM VERSIONING;
ALTER TABLE bank_accounts ADD SYSTEM VERSIONING;
ALTER TABLE fee_policies ADD SYSTEM VERSIONING;
ALTER TABLE limit_policies ADD SYSTEM VERSIONING;
ALTER TABLE payout_policies ADD SYSTEM VERSIONING;
ALTER TABLE cancel_policies ADD SYSTEM VERSIONING;
ALTER TABLE card_quota_policies ADD SYSTEM VERSIONING;
ALTER TABLE point_expiry_policies ADD SYSTEM VERSIONING;
ALTER TABLE global_settings ADD SYSTEM VERSIONING;
ALTER TABLE fds_rules ADD SYSTEM VERSIONING;
ALTER TABLE merchant_api_credentials ADD SYSTEM VERSIONING; -- 웹훅 URL·모드·키 롤 이력(감사 보완)
ALTER TABLE merchant_accounts ADD SYSTEM VERSIONING;
ALTER TABLE admin_allowed_ips ADD SYSTEM VERSIONING;
ALTER TABLE deposit_identifiers ADD SYSTEM VERSIONING;
-- ═══════════════════════════════════════════════════════════
-- V3__admin_login_and_per_admin_ip.sql
-- ═══════════════════════════════════════════════════════════
-- =====================================================================
-- V3: 관리자 로그인 도입에 필요한 구조 변경 + 최초 관리자 계정 생성
--
-- 1) admin_allowed_ips 에 "이 규칙이 어느 관리자에게 적용되는지" 칸을 추가합니다.
-- · 지금까지는 "모든 관리자 공통" 규칙만 있었는데(전역 화이트리스트),
-- 관리자별로도 접근 IP 를 제한할 수 있어야 한다는 요구가 확정되어 칸을 늘립니다.
-- · admin_account_id 가 비어 있으면(NULL) = 모든 관리자에게 적용되는 공통 규칙
-- · admin_account_id 에 값이 있으면 = 그 관리자에게만 적용되는 개인 규칙
-- (개인 규칙이 하나라도 있는 관리자는, 그 규칙에 맞는 곳에서만 로그인·사용 가능)
--
-- 2) 최초 관리자 계정 2개(root, admin)를 만듭니다.
-- · 초기 비밀번호는 둘 다 123456 입니다. (아래 값은 그 비밀번호를
-- PBKDF2-SHA256(반복 21만 회)로 뭉갠 해시 — 원문은 저장하지 않습니다)
-- · OTP(일회용 비밀번호)는 아직 미등록 상태(otp_enabled=false)라서,
-- 최초 로그인 때 등록 절차를 반드시 거치게 됩니다(서버 로직).
-- · root 만 관리자 계정 생성·삭제·OTP 초기화를 할 수 있습니다(설계 #9).
-- =====================================================================
-- 준비: admin_allowed_ips 는 "시스템 버저닝(변경 이력 자동 보존)" 테이블이라
-- 구조 변경(ALTER)이 기본적으로 막혀 있습니다(MariaDB 오류 4119).
-- KEEP = "기존 이력을 그대로 보존한 채" 구조 변경을 허용하는 안전한 선택입니다.
SET @@system_versioning_alter_history = KEEP;
-- 1) 관리자별 적용 대상 칸 + 연결(외래키) + 조회 인덱스 추가
ALTER TABLE admin_allowed_ips
ADD COLUMN admin_account_id BIGINT NULL COMMENT '적용 대상 관리자(NULL=모든 관리자 공통 규칙)' AFTER id,
ADD CONSTRAINT fk_allowed_ips_admin FOREIGN KEY (admin_account_id) REFERENCES admin_accounts (id),
ADD INDEX ix_allowed_ips_admin (admin_account_id);
-- 2) 최초 관리자 계정 2개 생성 (초기 비밀번호: 123456)
INSERT INTO admin_accounts (login_id, password_hash, name, is_root, otp_enabled, otp_fail_count, status, created_at)
VALUES
('root', 'pbkdf2_sha256$210000$qKmu9J+iJsq6rCxclg+xmQ==$xC6BWCUGobEa3ab3PpZOK2QnxLyZsfSVRCN+ElMtkXY=', '최고 관리자', TRUE, FALSE, 0, 'ACTIVE', NOW()),
('admin', 'pbkdf2_sha256$210000$Z3VxcMVb/i/4Jkks0EAcvg==$T/x/rdLPTSmBXBEf5HbCsFyZW4AcTP4Nju8FcTWTHrY=', '일반 관리자', FALSE, FALSE, 0, 'ACTIVE', NOW());
-- ═══════════════════════════════════════════════════════════
-- V4__files_and_merchant_documents.sql
-- ═══════════════════════════════════════════════════════════
-- =====================================================================
-- V4: 중앙 파일 대장(files) + 매장 필수서류(merchant_documents)
--
-- 1) files — 업로드되는 모든 파일을 "한 테이블"에서 관리합니다(확정 원칙).
-- 다른 테이블은 파일을 직접 갖지 않고, 이 대장의 번호(file_id)만 참조합니다.
-- 실제 파일 내용물은 서버 디스크(업로드 폴더)에 저장하고, 여기엔 위치·정보만 둡니다.
-- (보관처(클라우드 등)가 확정되면 stored_name 이 가리키는 곳만 바뀌면 됩니다)
--
-- 2) merchant_documents — 매장별 "필요 서류" 목록입니다.
-- · 관리자가 서류명을 추가하면 = "이 서류를 내세요" 라는 요구(file_id 는 비어 있음)
-- · 파일이 올라오면 file_id 가 채워지고 제출 시각이 기록됩니다
-- · 요구된 서류가 전부 제출되어야만 매장 승인이 가능합니다(서버 로직으로 강제)
-- =====================================================================
-- 1) 중앙 파일 대장
CREATE TABLE files (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
original_name VARCHAR(300) NOT NULL COMMENT '사용자가 올린 원래 파일 이름',
stored_name VARCHAR(100) NOT NULL COMMENT '서버 디스크에 저장된 파일 이름(무작위 - 겹침·경로조작 방지)',
content_type VARCHAR(100) COMMENT '파일 형식(예: application/pdf, image/png)',
size_bytes BIGINT NOT NULL COMMENT '파일 크기(바이트)',
uploaded_by BIGINT NOT NULL COMMENT '올린 관리자 번호(추후 앱 제출 도입 시 주체 구분 확장)',
created_at DATETIME NOT NULL,
UNIQUE KEY ux_files_stored (stored_name)
);
-- 2) 매장 필수서류 (파일은 대장 번호로만 참조)
CREATE TABLE merchant_documents (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
merchant_id BIGINT NOT NULL COMMENT '어느 매장의 서류인지',
doc_name VARCHAR(100) NOT NULL COMMENT '서류 이름(예: 사업자등록증, 통장 사본)',
file_id BIGINT NULL COMMENT '제출된 파일(중앙 대장 참조). 비어 있으면 아직 미제출',
requested_by BIGINT NOT NULL COMMENT '서류를 요구한 관리자 번호',
submitted_at DATETIME NULL COMMENT '파일이 제출(업로드)된 시각',
created_at DATETIME NOT NULL,
CONSTRAINT fk_mdoc_merchant FOREIGN KEY (merchant_id) REFERENCES merchants (id),
CONSTRAINT fk_mdoc_file FOREIGN KEY (file_id) REFERENCES files (id),
CONSTRAINT fk_mdoc_admin FOREIGN KEY (requested_by) REFERENCES admin_accounts (id),
-- 같은 매장에 같은 이름의 서류를 두 번 요구하지 못하게 막습니다.
UNIQUE KEY ux_mdoc_merchant_doc (merchant_id, doc_name),
KEY ix_mdoc_merchant (merchant_id)
);
-- ═══════════════════════════════════════════════════════════
-- V5__system_wallets.sql
-- ═══════════════════════════════════════════════════════════
-- =====================================================================
-- V5: 시스템 지갑 6종 기초 데이터 (queries.sql 공통 규약 0.2)
--
-- 시스템 지갑 = 회사 장부상의 "계정 서랍"들입니다. 복식 분개의 반대쪽 다리(leg)로 쓰입니다.
-- · SETTLEMENT_CLEARING : 은행 대사 계정(들어오고 나가는 돈의 대외 청산)
-- · UNMATCHED : 주인을 못 찾은 입금 보관(장부 밖 돈 없음 원칙)
-- · FEE_REVENUE : 수수료 수익
-- · GIFT_ESCROW : 선물 링크 보류금
-- · EXPIRED : 유효기간 만료 소멸분(낙전)
-- · FORFEITED : 탈퇴 시 소액 포기분
--
-- 규약(0.2): 시스템 지갑은 잠금 금지·balance 동기 갱신 금지·원장 balance_after=NULL.
-- balance 는 일 마감 배치가 원장 SUM 으로 파생 갱신합니다.
-- (EXCHANGE_{제휴사} 지갑은 포인트 전환 기능이 구현 보류라 만들지 않습니다 - #49)
-- =====================================================================
INSERT INTO wallets (owner_type, system_code, balance, created_at) VALUES
('SYSTEM', 'SETTLEMENT_CLEARING', 0, NOW()),
('SYSTEM', 'UNMATCHED', 0, NOW()),
('SYSTEM', 'FEE_REVENUE', 0, NOW()),
('SYSTEM', 'GIFT_ESCROW', 0, NOW()),
('SYSTEM', 'EXPIRED', 0, NOW()),
('SYSTEM', 'FORFEITED', 0, NOW());
-- ═══════════════════════════════════════════════════════════
-- V6__widen_idempotency_key.sql
-- ═══════════════════════════════════════════════════════════
-- =====================================================================
-- V6: transactions.idempotency_key 길이 확장 (64 → 100)
--
-- 발견된 설계 모순(실측 검증 중 확인):
-- · 스키마: idempotency_key varchar(64)
-- · 정본 SQL(queries.sql 4.4): 키 = 'DEP:' + 소스 + ':' + dedup_key(64자)
-- → 'DEP:PGVACCT:' + 64자 = 최대 76자가 되어 64자를 넘습니다(잘림/오류).
-- 잘리면 원거래 역추적(수동매칭의 related_txn_id 연결)이 실패하므로 넉넉히 100자로 늘립니다.
-- (여유분은 향후 다른 멱등키 규칙('PAY:'... 등)의 접두사도 감안한 값)
-- =====================================================================
ALTER TABLE transactions MODIFY idempotency_key VARCHAR(100)
COMMENT 'UNIQUE(initiator_type, initiator_id, idempotency_key) - 사용자 스코프 멱등(#3). V6: 4.4 키(DEP:소스:해시64=최대76자) 수용 위해 64→100 확장';
-- ═══════════════════════════════════════════════════════════
-- V7__merchant_onboarding.sql
-- ═══════════════════════════════════════════════════════════
-- =====================================================================
-- V7: 매장 온보딩(신청·보충요청) + 매장 API 화이트IP (확정 요구 2026-07-25)
--
-- 1) merchants.supplement_note — 관리자의 "자료 보충 요청" 사유를 담는 칸.
-- 상태 흐름에 SUPPLEMENT(보완 대기)가 추가됩니다:
-- PENDING(심사중) ↔ SUPPLEMENT(보완 대기) → ACTIVE/REJECTED ...
-- (매장이 자료를 보완해 다시 제출하면 PENDING 으로 돌아옵니다 — 왕복 가능)
--
-- 2) files.uploader_type — 파일을 올린 주체 구분(ADMIN=관리자, MERCHANT=매장).
-- 매장이 직접 서류를 제출하는 흐름이 생겨 주체 구분이 필요해졌습니다.
--
-- 3) merchant_api_allowed_ips — 매장 open API(/pg/**) 전용 화이트IP.
-- 매장이 자기 서버 IP 를 "신청"하고 관리자가 "승인"해야 효력이 생깁니다.
-- (승인된 IP 가 하나도 없는 매장은 open API 호출 자체가 거부됩니다 — 보안 우선)
-- =====================================================================
-- 1) 보충 요청 사유 칸 (merchants 는 이력 자동보존 테이블 → KEEP 선언 필요)
SET @@system_versioning_alter_history = KEEP;
ALTER TABLE merchants
ADD COLUMN supplement_note VARCHAR(500) NULL COMMENT '자료 보충 요청 사유(상태 SUPPLEMENT 일 때 매장에게 보여 줌)' AFTER reject_reason;
-- 2) 파일 올린 주체 구분
ALTER TABLE files
ADD COLUMN uploader_type VARCHAR(10) NOT NULL DEFAULT 'ADMIN' COMMENT '올린 주체: ADMIN(관리자)|MERCHANT(매장)' AFTER uploaded_by;
-- 3) 매장 open API 화이트IP (신청 → 관리자 승인)
CREATE TABLE merchant_api_allowed_ips (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
merchant_id BIGINT NOT NULL COMMENT '어느 매장의 IP 인지',
cidr VARCHAR(50) NOT NULL COMMENT '허용할 서버 IP(단건 또는 CIDR)',
memo VARCHAR(200) COMMENT '설명(예: 운영 서버)',
status VARCHAR(15) NOT NULL DEFAULT 'REQUESTED' COMMENT 'REQUESTED(신청)|APPROVED(승인)|REJECTED(반려)',
approved_by BIGINT NULL COMMENT '승인/반려한 관리자',
approved_at DATETIME NULL,
created_at DATETIME NOT NULL,
CONSTRAINT fk_mip_merchant FOREIGN KEY (merchant_id) REFERENCES merchants (id),
CONSTRAINT fk_mip_admin FOREIGN KEY (approved_by) REFERENCES admin_accounts (id),
KEY ix_mip_merchant (merchant_id, status)
);
-- ═══════════════════════════════════════════════════════════
-- V8__content_file_refs.sql
-- ═══════════════════════════════════════════════════════════
-- =====================================================================
-- V8: 콘텐츠 파일 참조를 중앙 대장(files)으로 교정 (확정 원칙 준수)
--
-- 발견된 원칙 위반(설계 검토): 배너(banners.image_path)와 문의 첨부
-- (inquiry_attachments.file_path)가 파일 "경로"를 직접 갖고 있었습니다.
-- 확정 원칙(V4): 모든 파일은 중앙 대장(files) 한 곳에서만 관리하고,
-- 다른 테이블은 번호(file_id)로만 참조한다. → 두 테이블을 원칙대로 바꿉니다.
-- (신규 시스템이라 기존 데이터가 없어 안전하게 교체 가능합니다)
-- =====================================================================
-- 1) 배너: 경로 컬럼 제거 → 중앙 대장 참조로
ALTER TABLE banners
DROP COLUMN image_path,
ADD COLUMN image_file_id BIGINT NOT NULL COMMENT '배너 이미지(중앙 파일 대장 참조)' AFTER id,
ADD CONSTRAINT fk_banner_file FOREIGN KEY (image_file_id) REFERENCES files (id);
-- 2) 문의 첨부: 경로 컬럼 제거 → 중앙 대장 참조로
ALTER TABLE inquiry_attachments
DROP COLUMN file_path,
ADD COLUMN file_id BIGINT NOT NULL COMMENT '첨부 이미지(중앙 파일 대장 참조)' AFTER inquiry_id,
ADD CONSTRAINT fk_inqatt_file FOREIGN KEY (file_id) REFERENCES files (id);
-- ═══════════════════════════════════════════════════════════
-- V9__fds_rules.sql
-- ═══════════════════════════════════════════════════════════
-- =====================================================================
-- V9: FDS(이상거래 탐지) 기본 룰 8종 등록 (설계 #38)
--
-- · 룰의 수치(params JSON)는 "관리자 화면에서 조정"하는 설계입니다(배포 불필요).
-- 여기 값은 초기 제안값이며, 운영 개시 전 발주사와 협의해 조정해야 합니다.
-- · action: BLOCK(차단)|HOLD(보류)|ALERT(경보)|MONITOR(관찰만)
-- · 탐지 배치가 아직 준비되지 않은 4종은 enabled=0(끔)으로 정직하게 등록합니다.
-- (켜도 오류는 없지만 탐지가 돌지 않아 "켜져 있는데 조용한" 오해를 막기 위함)
-- · INSERT IGNORE + code UNIQUE 라 여러 번 실행돼도 안전합니다.
-- =====================================================================
INSERT IGNORE INTO fds_rules (code, name, params, action, enabled, updated_at) VALUES
-- 탐지 배치 지원 4종 (설계 14.1 ~ 14.4)
('PASSTHROUGH', '패스스루(입금 즉시 이체)', '{"scan_hours":24,"window_minutes":30,"threshold":1000000}', 'ALERT', 1, NOW()),
('GIFT_CONCENTRATION', '선물 집중 수신', '{"scan_hours":24,"senders":5,"threshold":1000000}', 'ALERT', 1, NOW()),
('MULTI_ACCOUNT_DEVICE','다계정 기기', '{"scan_hours":24,"principals":3}', 'ALERT', 1, NOW()),
('ENUMERATION', '무차별 시도(IP 열거)', '{"window_minutes":10,"attempts":20}', 'ALERT', 1, NOW()),
-- 탐지 배치 준비 중 4종 (룰 정의만 먼저 — 화면에서 수치·동작을 미리 조정 가능)
('SELF_PAYMENT', '본인 매장 결제', '{"scan_hours":24}', 'MONITOR', 0, NOW()),
('CANCEL_ABUSE', '취소 남용', '{"scan_hours":168,"cancels":5}', 'MONITOR', 0, NOW()),
('POST_CHANGE_WITHDRAW','정보 변경 직후 출금', '{"window_minutes":60,"threshold":500000}', 'MONITOR', 0, NOW()),
('NIGHT_LARGE', '심야 고액 거래', '{"from_hour":0,"to_hour":6,"threshold":1000000}', 'MONITOR', 0, NOW());
-- ═══════════════════════════════════════════════════════════
-- V10__outbox_claimed_at.sql
-- ═══════════════════════════════════════════════════════════
-- =====================================================================
-- V10: outbox_jobs 에 claimed_at(집어간 시각) 추가 — 정본 모순 교정
--
-- · 설계 쿼리 정본(queries.sql 13장)의 outbox 워커는 claimed_at 을 사용합니다.
-- (RUNNING 으로 집어간 뒤 10분 넘게 소식 없는 "고아" 작업을 되살리는 기준)
-- · 그런데 스키마 정본(dbml)과 실제 DB 에는 이 컬럼이 빠져 있었습니다.
-- 쿼리 정본에 맞춰 컬럼을 추가하고 dbml 도 함께 맞췄습니다(불일치 제거).
-- =====================================================================
ALTER TABLE outbox_jobs
ADD COLUMN claimed_at datetime NULL COMMENT '워커가 RUNNING 으로 집어간 시각 - 고아 스위퍼 기준(13장)' AFTER status;
-- ═══════════════════════════════════════════════════════════
-- V11__outbox_claim_token.sql
-- ═══════════════════════════════════════════════════════════
-- =====================================================================
-- V11: outbox_jobs 에 claim_token(집기 표) 추가 — MariaDB 10.3 호환 교정
--
-- · 쿼리 정본(13장)의 원래 집기 방식은 FOR UPDATE SKIP LOCKED 인데,
-- 이 문법은 MariaDB 10.6부터 지원됩니다. 발주사 서버와 같은 10.3에서는
-- 문법 오류가 나서(실측) 워커가 아예 돌지 못했습니다.
-- · 대신 "집기 표(claim_token)" 방식을 씁니다:
-- 1) UPDATE 한 방으로 PENDING → RUNNING + 내 표 붙이기 (원자적 — 겹칠 수 없음)
-- 2) 내 표가 붙은 작업만 SELECT 해서 처리
-- 워커가 여러 대여도 같은 작업을 두 번 집는 일이 없습니다.
-- · queries.sql 13장도 이 방식으로 함께 고쳤습니다(정본 동기화).
-- =====================================================================
ALTER TABLE outbox_jobs
ADD COLUMN claim_token char(36) NULL COMMENT '집기 표 - 워커가 UPDATE로 붙이고 자기 것만 SELECT (10.3 호환 클레임)' AFTER claimed_at;
-- 내 표가 붙은 작업 찾기용 (워커 주기마다 조회)
CREATE INDEX ix_outbox_claim ON outbox_jobs (claim_token);
-- ═══════════════════════════════════════════════════════════
-- V12__user_gender.sql
-- ═══════════════════════════════════════════════════════════
-- V12: 회원 성별 컬럼 추가
-- 왜: 본인인증(실서비스)은 이름·생년월일과 함께 "성별"도 결과로 돌려줍니다.
-- 관리자 회원 조회에서 성별을 보여 주려면 가입 시 이 값을 저장해 두어야 합니다.
-- 값: 'M'(남) | 'F'(여). 이 컬럼이 생기기 전에 가입한 옛 회원은 값이 없어(NULL) '미상'으로 표시됩니다.
-- users 는 시스템 버저닝(변경 이력 자동 보존) 테이블이라, 컬럼을 더하려면
-- 아래 설정으로 "이력까지 함께 바꾼다"고 알려 주어야 합니다(V3·V7 과 동일한 규약).
SET @@system_versioning_alter_history = KEEP;
ALTER TABLE users
ADD COLUMN gender varchar(1) NULL COMMENT '성별 M|F (본인인증 결과)' AFTER birth_date;
-- ═══════════════════════════════════════════════════════════
-- V13__user_avatar.sql
-- ═══════════════════════════════════════════════════════════
-- V13: 회원 프로필 사진(아바타) 컬럼 추가
-- 왜: 마이페이지에서 본인 프로필 사진을 올리고 볼 수 있어야 합니다.
-- 실물 파일은 중앙 파일 대장(files)에 저장하고, 회원 행에는 그 번호만 참조합니다(확정 원칙).
-- 값: files.id (사진을 지우면 NULL). 사진이 없으면 앱은 기본 사람 아이콘을 보여 줍니다.
-- users 는 시스템 버저닝(변경 이력 자동 보존) 테이블이라, 컬럼 추가 시 이력까지 함께
-- 바꾼다고 알려 주어야 합니다(V3·V7·V12 와 동일한 규약).
SET @@system_versioning_alter_history = KEEP;
ALTER TABLE users
ADD COLUMN avatar_file_id bigint NULL COMMENT '프로필 사진(중앙 파일 대장 files.id 참조, V13)' AFTER gender;
-- ═══════════════════════════════════════════════════════════
-- V14__rate_limits.sql
-- ═══════════════════════════════════════════════════════════
-- V14: 이용제한(Rate limit) — 동작별 호출 빈도 제한으로 무차별 시도(로그인·가입·출금 등)를 차단합니다.
-- 규칙(rules)은 관리자가 조정하고, 카운터(counters)는 "고정 창(fixed window)" 방식으로 셉니다.
-- · window_sec 초 단위 창마다 subject(IP 또는 회원)별 호출 수를 세고,
-- · max_count 를 넘으면 그 창이 끝날 때까지 429(요청 과다)로 막습니다.
CREATE TABLE rate_limit_rules (
action_key varchar(50) NOT NULL COMMENT '동작 키(login, signup.submit 등)',
description varchar(200) NOT NULL COMMENT '사람이 읽는 설명',
window_sec int NOT NULL COMMENT '집계 창(초)',
max_count int NOT NULL COMMENT '창 안에서 허용하는 최대 횟수',
basis varchar(10) NOT NULL COMMENT 'IP(주소 기준) | USER(회원 기준)',
enabled boolean NOT NULL DEFAULT TRUE COMMENT '켬/끔(끄면 제한 안 함)',
updated_at datetime NOT NULL,
PRIMARY KEY (action_key)
) COMMENT = '이용제한 규칙(관리자 조정)';
CREATE TABLE rate_limit_counters (
action_key varchar(50) NOT NULL,
subject varchar(100) NOT NULL COMMENT 'IP 주소 또는 회원 식별자(아이디/번호)',
window_start bigint NOT NULL COMMENT '창 시작 시각(epoch 초)',
cnt int NOT NULL DEFAULT 0 COMMENT '이 창에서의 호출 수',
PRIMARY KEY (action_key, subject, window_start)
) COMMENT = '이용제한 고정창 카운터(오래된 창은 배치로 정리)';
CREATE INDEX ix_ratelimit_window ON rate_limit_counters (window_start);
-- NestPay 에 실제 존재하는 동작만 등록합니다(예제의 상품권 전환·텔레그램 연동은 이 서비스에 없어 제외).
INSERT INTO rate_limit_rules (action_key, description, window_sec, max_count, basis, enabled, updated_at) VALUES
('login', '로그인 시도 (회원/5분)', 300, 10, 'USER', TRUE, NOW()),
('auth.totp.verify', '관리자 TOTP 검증 (IP/5분)', 300, 10, 'IP', TRUE, NOW()),
('signup.submit', '회원가입 시도 (IP/시간)', 3600, 5, 'IP', TRUE, NOW()),
('password.change', '비밀번호 변경 (회원/시간)', 3600, 5, 'USER', TRUE, NOW()),
('withdraw.create', '포인트 출금 신청 (회원/5분)', 300, 10, 'USER', TRUE, NOW()),
('inquiry.create', '1:1 문의 등록 (회원/시간)', 3600, 20, 'USER', TRUE, NOW()),
('onboarding.bank.code', '1원 인증코드 확인 (회원/10분)', 600, 10, 'USER', TRUE, NOW()),
('payment.create', '결제 생성 (회원/5분)', 300, 10, 'USER', TRUE, NOW());
-- ═══════════════════════════════════════════════════════════
-- V15__notice_event_image_link.sql
-- ═══════════════════════════════════════════════════════════
-- V15: 이벤트(EVENT) 공지에 "이미지 + 외부 링크" 지원
-- 왜: 공지(NOTICE)는 글(본문) 위주이지만, 이벤트(EVENT)는 이미지 배너를 목록에 보여 주고
-- 누르면 외부 이벤트 페이지(예: event.nestpay.co.kr)로 연결하는 형태로 운영합니다.
-- 사용: EVENT 는 image_file_id(이미지)+link_url(외부 주소)을 쓰고 body 는 비워 둘 수 있습니다.
-- NOTICE 는 지금처럼 title+body 만 쓰고 두 컬럼은 NULL.
ALTER TABLE notices
ADD COLUMN image_file_id bigint NULL COMMENT '이벤트 이미지(중앙 파일 대장 files.id 참조, V15)' AFTER body,
ADD COLUMN link_url varchar(500) NULL COMMENT '이벤트 외부 링크(클릭 시 이동, V15)' AFTER image_file_id;
-- ═══════════════════════════════════════════════════════════
-- V16__notification_settings.sql
-- ═══════════════════════════════════════════════════════════
-- V16: 알림 설정 — 이벤트별 발송 여부·채널·심각도를 관리자가 조정합니다.
-- 왜: "어떤 알림을 보낼지/어디로(앱 알림함·폰 푸시)/얼마나 중요하게" 를 코드 수정 없이 켜고 끕니다.
-- 채널: INBOX(앱 알림함만) | PUSH(폰 푸시만) | BOTH(둘 다). ※ FCM 실발송은 푸시 키 발급 후 연동.
-- 심각도: INFO(정보) | WARN(경고) | CRITICAL(심각) — 표시용.
-- ★ NestPay 가 실제로 보내는 알림 종류만 등록합니다(예제의 텔레그램·상품권 전환·추천인은 이 서비스에 없음).
CREATE TABLE notification_settings (
event_key varchar(40) NOT NULL COMMENT '알림 이벤트 키(enqueue payload 의 kind 와 일치)',
description varchar(200) NOT NULL,
enabled boolean NOT NULL DEFAULT TRUE COMMENT '발송 여부(끄면 이 이벤트 알림 안 감)',
channel varchar(10) NOT NULL DEFAULT 'BOTH' COMMENT 'INBOX | PUSH | BOTH',
severity varchar(10) NOT NULL DEFAULT 'INFO' COMMENT 'INFO | WARN | CRITICAL(표시용)',
updated_at datetime NOT NULL,
PRIMARY KEY (event_key)
) COMMENT = '알림 이벤트 설정(관리자 조정)';
INSERT INTO notification_settings (event_key, description, enabled, channel, severity, updated_at) VALUES
('DEPOSIT_CONFIRMED', '충전(입금) 완료', TRUE, 'BOTH', 'INFO', NOW()),
('DEPOSITOR_CODE', '입금자 코드 안내', TRUE, 'INBOX', 'INFO', NOW()),
('PAYMENT', '결제 완료', TRUE, 'BOTH', 'INFO', NOW()),
('CANCEL', '결제 취소', TRUE, 'BOTH', 'WARN', NOW()),
('GIFT', '선물 보내기/받기', TRUE, 'BOTH', 'INFO', NOW()),
('WITHDRAW', '출금 신청/완료/반환', TRUE, 'BOTH', 'INFO', NOW()),
('SETTLEMENT', '매장 정산 지급', TRUE, 'BOTH', 'INFO', NOW()),
('INQUIRY_ANSWERED', '1:1 문의 답변 등록', TRUE, 'BOTH', 'INFO', NOW());
-- ═══════════════════════════════════════════════════════════
-- V17__seed_policies.sql
-- ═══════════════════════════════════════════════════════════
-- V17: 이용약관·개인정보처리방침·탈퇴안내 실제 전문 시드 (발주사 제공 원문, 시행일 2026-09-01)
-- 'WITHDRAW_GUIDE'(탈퇴 안내) 같은 긴 종류 코드도 담을 수 있도록 kind 칸을 넉넉히(20자) 넓힙니다.
ALTER TABLE policy_documents MODIFY kind VARCHAR(20) NOT NULL;
-- 종류별 1개만 시행(ACTIVE)이므로, 기존 시행분(개발용 테스트 포함)은 먼저 보관(ARCHIVED) 처리합니다.
UPDATE policy_documents SET status = 'ARCHIVED'
WHERE kind IN ('TERMS', 'PRIVACY', 'WITHDRAW_GUIDE') AND status = 'ACTIVE';
INSERT INTO policy_documents (kind, version, body, effective_at, requires_reconsent, status) VALUES
('TERMS', '2023.07.21',
'네스트페이 전자금융거래 이용약관
제정 2022.07.20 / 개정 2023.05.12 / 개정 2023.07.21
제1장 총칙
제1조 (목적)
본 약관은 주식회사 페이네스트(이하 “회사”라 합니다)가 제공하는 전자지급 결제 대행 서비스, 선불전자지급수단의 발행 및 관리서비스(이하 통칭하여 “전자금융거래서비스”라 합니다)를 회원이 이용함에 있어, 회사와 회원 간 권리, 의무 및 회원의 이용절차 등에 관한 사항을 규정하는 것을 그 목적으로 합니다.
제2조 (정의)
1. 본 약관에서 정한 용어의 정의는 아래와 같습니다.
1) “전자금융거래”라 함은 회사가 전자적 장치를 통하여 전자금융업무를 제공하고, 회원이 회사의 종사자와 직접 대면하거나 의사소통을 하지 아니하고 자동화된 방식으로 이를 이용하는 거래를 말합니다.
2) “전자지급수단”이라 함은 선불전자지급수단, 신용카드 등 전자금융거래법 제2조 제11호에서 정하는 전자적 방법에 따른 지급수단을 말합니다.
3) “전자지급거래”라 함은 자금을 주는 자(지급인)가 회사로 하여금 전자지급수단을 이용하여 자금을 받는 자(수취인)에게 자금을 이동하게 하는 전자금융거래를 말합니다.
4) “전자적 장치”라 함은 전자금융거래정보를 전자적 방법으로 전송하거나 처리하는데 이용되는 장치를 말합니다.
5) “접근매체”라 함은 전자금융거래에 있어서 거래지시를 하거나 거래내용의 진실성과 정확성을 확보하기 위하여 사용되는 수단 또는 정보를 말합니다.
6) “전자금융거래서비스”라 함은 회사가 회원에게 제공하는 제4조 기재의 서비스를 말합니다.
7) “회원”이라 함은 본 약관에 동의하고 회사가 제공하는 전자금융거래서비스를 이용하는 이용자를 말합니다.
8) “거래지시”라 함은 회원이 회사에게 전자금융거래의 처리를 지시하는 것을 말합니다.
9) “오류”라 함은 회원의 고의 또는 과실 없이 전자금융거래가 계약 또는 거래지시에 따라 이행되지 아니한 경우를 말합니다.
2. 본 약관에서 정의하지 않은 용어는 전자금융거래법 등 관련 법령에 따릅니다.
제3조 (약관의 명시, 설명 및 변경)
회사는 회원이 전자금융거래를 하기 전에 본 약관을 게시하고 중요 내용을 확인할 수 있도록 하며, 약관 변경 시 시행일 1월 전에 게시·통지합니다. 법령 개정으로 긴급히 변경한 때에는 최소 1개월 이상 게시·통지합니다. 회원은 변경 시행일 전 영업일까지 계약을 해지할 수 있고, 이의를 제기하지 않으면 변경을 승인한 것으로 봅니다.
제4조 (전자금융거래서비스의 종류)
1) 전자지급 결제 대행 서비스 2) 선불전자지급수단 발행 및 관리서비스
제5조 (서비스 이용 시간)
연중무휴 1일 24시간 제공을 원칙으로 하되, 금융회사 등의 사정·설비 점검 시 중단될 수 있으며 사전(부득이한 경우 사후) 게시합니다.
제6조 (거래내용의 확인)
회원은 서비스 내 조회 화면으로 거래내용을 확인할 수 있고, 서면교부 요청 시 2주 이내 교부합니다. 거래금액 1만원 초과 기록 등은 5년, 1만원 이하 기록 등은 1년 보존합니다.
- 주소: 서울시 송파구 송파대로 201 테라타워2 A동 905호 / http://www.paynest.co.kr / 02-431-8333
제7조 (거래계약의 효력 및 거래지시의 철회)
회원은 지급의 효력이 발생하기 전까지 거래지시를 철회할 수 있으며, 효력 발생 후에는 관련 법령상 청약철회 방법에 따라 결제대금을 반환받을 수 있습니다.
제8조 (오류의 정정)
회원은 오류를 안 때 정정을 요구할 수 있고, 회사는 정정요구/인지일부터 2주 이내에 결과를 알립니다.
제9조 (전자금융거래 기록의 생성 및 보존)
회사는 추적·검색·정정이 가능한 기록을 제6조 기준에 따라 생성·보존합니다.
제10조 (개인정보 보호)
회사는 취득한 회원 정보를 법령·동의 없이 제3자에게 제공·누설하거나 목적 외 사용하지 않으며, 개인정보처리방침을 운용합니다.
제11조 (회사의 책임)
접근매체의 위·변조, 계약체결/거래지시 전송·처리 과정, 정보통신망 침입으로 획득한 접근매체 이용 사고로 손해가 발생하면 회사가 배상책임을 부담합니다. 다만 회원의 접근매체 대여·누설·방치 등 법정 사유가 있으면 책임의 전부 또는 일부를 회원이 부담할 수 있습니다.
제12조 (분쟁처리 절차)
고객센터/이메일로 분쟁처리를 요구할 수 있으며(전화 02-431-8333, info@paynest.co.kr), 회사는 15일 이내(착오송금중개·해외가맹점 분쟁은 31일 이내) 결과를 안내합니다. 금융감독원 금융분쟁조정위원회 등에 조정을 신청할 수 있습니다.
제13조 (회사의 안전성 확보 의무)
회사는 금융위원회가 정하는 정보기술부문·전자금융업무 기준을 준수합니다.
제14조 (약관 외 준칙) / 제15조 (관할)
개별 합의사항이 우선하며, 정하지 않은 사항은 전자금융거래법 등 관계 법령에 따릅니다. 분쟁 관할은 민사소송법에 따릅니다.
제2장 전자지급결제대행 서비스
제16조 (정의) “전자지급결제대행 서비스”는 재화 등의 구매에서 지급결제 정보를 송·수신하거나 그 대가의 정산을 대행·매개하는 서비스입니다.
제17조 (거래지시의 철회) 회원은 수취인 계좌 입금기록/전자적 장치 입력이 끝나기 전까지 철회할 수 있으며, 철회로 지급이 이루어지지 않은 자금은 반환합니다.
제18조 (이용금액의 한도) 회사 정책 및 결제업체 기준에 따라 결제한도가 제한될 수 있습니다.
제19조 (접근매체의 관리) 접근매체 양도·대여·담보·알선은 금지되며, 회원은 누설·노출·방치하지 않도록 주의해야 합니다. 분실·도난 통지 후 제3자 사용 손해는 회사가 배상합니다.
제3장 선불전자지급수단의 발행 및 관리 서비스
제20조 (정의) “선불전자지급수단”은 네스트페이머니 등 회사가 발행한 전자금융거래법상 선불전자지급수단을, “충전”은 지정 지불수단 구매 또는 활동 적립을 말합니다.
제21조 (접근매체의 관리) 분실·도난 통지 전 저장금액 손해는 책임지지 않으며, 제19조를 준용합니다.
제22조 (거래의 정지) 허위정보·명의도용·부정거래·접근매체 매매/양도 등의 경우 자격 박탈 또는 사용 제한할 수 있고, 사유 해소 후 승낙에 따라 재사용할 수 있습니다.
제23조 (충전) 계좌출금 등 지정 지불수단으로 구매하거나 적립받아 충전하며, 수단별 제한금액이 있을 수 있습니다.
제24조 (이용) 재화 등 구매 시 지불수단으로 사용하며 구매완료 시 즉시 차감됩니다. 무상 적립선불 → 구매선불 순으로 차감하고, 구매 취소 시 사용한 선불을 재충전합니다.
제25조 (유효기간) 마지막 이용일부터 10년 미사용 시 구매선불은 자동 소멸됩니다(도래 30일 전 포함 3회 이상 통지). 동의 철회 시 적립선불은 소멸됩니다. 무상 선불의 유효기간은 회차별로 앱에 공지합니다.
제26조 (환급) 구매선불은 전액 환급(구매일 7일 이내 구매취소 가능), 적립선불은 환급 제외. 기프트카드 충전분은 60% 이상(1만원 이하 80% 이상) 사용 시 잔액 환급. 현금 환급은 신청·입금확인 후 7영업일 이내 지정계좌로 지급합니다.
제27조 (선불충전금의 관리 및 공시) 회사는 선불충전금을 고유재산과 구분해 신탁 또는 지급보증보험에 가입하고, 매 영업일 점검·분기말 공시(http://www.paynest.co.kr)합니다. 등록취소·해산·파산·업무정지 등의 경우 신탁·보험을 통해 회원에게 우선 지급합니다.
제28조 (신탁 또는 지급보증보험) 선불충전금 전부를 신탁하며(지급준비금은 수시입출 가능 안전자산 예치), 직접 운용분은 전액 지급보증보험에 가입합니다.
제29조 (거래지시의 철회) 지급 정보가 수취인 지정 전자적 장치에 도달하기 전까지 철회할 수 있습니다.
제30조 (금지사항) 선불전자지급수단의 양도·판매·담보제공 등 처분행위는 금지됩니다.
제31조 (이용금액의 한도) 전자금융거래법상 한도를 보유 한도로 준용하며 정책에 따라 감액될 수 있고, 제18조를 준용합니다.
제32조 (착오송금) 착오송금 시 회사에 통지해 반환을 요청할 수 있고, 회사는 15일 이내 처리결과를 안내합니다. 미반환 시 예금자보호법 제5장에 따라 예금보험공사에 반환지원을 신청할 수 있습니다(2021.7.6 이후 착오송금 대상, 실지명의 확인 불가 거래는 제한).
부칙 제1조 (시행일) 이 약관은 2026년 09월 01일부터 시행합니다.',
'2026-09-01 00:00:00', FALSE, 'ACTIVE'),
('PRIVACY', '2022.12.19',
'네스트페이 개인정보 처리방침
주식회사 페이네스트(이하 “페이네스트”)는 네스트페이 선불결제 서비스 및 전자 결제 서비스를 이용하는 사용자의 개인정보를 중요시하며, 개인정보보호법 등 관계 법령을 준수합니다. 개인정보처리방침을 어플리케이션 화면 등에 상시 고지하며, 개정 시 변경 사유·내용을 공지합니다.
제1조 (수집하는 개인정보의 항목 및 수집 방법)
1. 수집항목
① 회원가입(필수): 성명, 생년월일, 성별, 내국인 여부, 휴대전화번호, 인증 비밀번호, 단말기 정보(모델·이동통신사), 본인확인값(CI, DI)
② 서비스 이용·처리(필수): 서비스 이용기록, 접속 로그, 쿠키, 접속 IP, MAC 주소, 운영체제·기기 정보, 회원 계좌정보(은행명·계좌번호·예금주성명), 본인확인값(CI, DI), 휴대전화번호
③ 충전·결제·환불(필수): 가맹점명, 단말기 정보, 카드정보(모바일 카드번호·CVC·유효기간·최근거래내역·보유잔액), 계좌정보(은행명·계좌번호·예금주성명)
④ 실명인증(선택): 성명, 휴대전화번호, 주민등록번호(및 발급일자), 운전면허번호(및 암호일련번호), 계좌정보, 본인확인값(CI, DI)
⑤ 소득공제(선택): 성명, 휴대전화번호, 주민등록번호
2. 수집방법: 어플리케이션·홈페이지·전자우편·전화 등 사용자 제시, 기기 자동수집 장치. 추가 수집 시 별도 동의를 받습니다.
제2조 (수집 및 이용목적)
1. 사용자 관리 및 서비스 제공(식별, 발급·충전·결제·환불, 본인확인, 상담·민원·분쟁 기록 보존, 고지사항 전달, 부정이용 방지, 소득공제·오픈뱅킹 처리)
2. 신규 서비스 개발 및 마케팅·광고(통계 기반 개발·맞춤 서비스, 이벤트·광고성 정보 전달)
제3조 (제3자 제공)
처리목적 범위 내에서만 처리하며 사전 동의 없이 제3자에게 제공하지 않습니다. 다만 사전 동의, 법령·수사기관의 적법한 요구, 급박한 생명·신체·재산 이익, 권리·의무 이전(합병·양수 등) 시 제공할 수 있습니다.
- 코리아크레딧뷰로(주): 이름·생년월일·연락처·성별·CI값 / 가입 시 회원 정보 확인 / 서비스 종료 시까지
- 금융결제원: 이름·생년월일·계좌번호·이메일·CI값 / 오픈뱅킹 서비스 운영
제4조 (처리 위탁)
- 수탁업체: 코리아크레딧뷰로(주) / 위탁업무: 사용자 본인 인증 / 보유·이용기간: 수탁자 기보유 정보로 별도 저장하지 않음
제5조 (보유 및 이용기간)
목적 달성 후 지체 없이 파기하되, 다음은 일정기간 보관합니다.
- 부정이용 기록 1년 / 이메일·아이디·전화번호 1년
- 계약·청약철회 기록 5년, 대금결제·재화공급 기록 5년, 소비자 불만·분쟁 3년(전자상거래법)
- 1만원 초과 전자금융거래 5년 / 1만원 이하 1년(전자금융거래법 시행령)
- 홈페이지 방문기록 3개월(통신비밀보호법) / 본인확인 기록 6개월(정보통신망법) / 개인위치정보 6개월(위치정보법)
제6조 (미사용 고객의 휴면계정)
1년간 접속 내역이 없으면 휴면계정으로 분리보관하며, 이 기간 개인정보를 활용·제공하지 않습니다. 카드 사용 또는 본인인증 로그인 시 사용계정으로 전환됩니다.
제7조 (파기)
목적 달성 후 지체 없이 파기합니다. 종이 출력물은 분쇄·소각, 전자파일은 재생 불가능한 기술적 방법으로 삭제합니다.
제8조 (사용자의 권리와 행사방법)
언제든지 개인정보 조회·수정, 회원탈퇴(동의 철회)가 가능합니다(어플리케이션 “회원 탈퇴” 메뉴 또는 1:1문의·개인정보보호책임자). 정정 요청 시 완료 전까지 해당 정보를 이용·제공하지 않습니다.
제9조 (기술적·관리적 보호대책)
개인정보 암호화 보관, 침입차단시스템·보안프로그램 운영, 취급 직원 최소화 및 정기 교육을 시행합니다.
제10조 (개인정보보호책임자)
- 책임자: 백운선(CISO) / 이메일: cs@paynest.co.kr / 연락처: 02-431-8333
- 침해 신고·상담: 개인정보침해신고센터(118), 개인정보분쟁조정위원회(1833-6972), 대검찰청 사이버수사과(1301), 경찰청 사이버수사국(182)
제11조 (적용 범위)
페이네스트 브랜드 전체에 적용되며, 외부 링크로 이동한 타사 서비스에는 본 방침이 적용되지 않습니다.
제12조 (고지의무)
변경 시 최소 7일 전(사용자에게 불리한 변경은 최소 30일 전) 어플리케이션 화면 등으로 고지합니다.
부칙 제1조 (시행일) 이 방침은 2026년 09월 01일부터 시행합니다.',
'2026-09-01 00:00:00', FALSE, 'ACTIVE'),
('WITHDRAW_GUIDE', '1.0',
'회원 탈퇴 안내
회원 탈퇴를 신청하기 전에 아래 내용을 반드시 확인해 주세요. 탈퇴는 관리자 승인 후 최종 처리되며, 처리 이후에는 되돌릴 수 없습니다.
1. 보유 포인트 소멸
- 탈퇴가 완료되면 보유하신 모든 포인트(충전·적립 잔액)는 즉시 소멸되며 환급되지 않습니다.
- 포인트를 환급받으시려면 탈퇴 신청 전에 먼저 출금(환급) 절차를 진행해 주세요.
2. 카드 이용 중지
- 탈퇴 시 발급받은 모든 카드가 정지되어 충전·결제·선물·출금을 이용하실 수 없습니다.
3. 진행 중인 거래
- 처리 중인 충전·결제·출금·선물이 있는 경우, 해당 거래가 마무리된 뒤 탈퇴를 신청해 주세요.
4. 개인정보 처리
- 회원정보는 개인정보 처리방침 및 관련 법령이 정한 보존기간에 따라 처리·파기됩니다.
- 전자금융거래 기록 등 법령상 보존 의무가 있는 정보는 해당 기간 동안 분리 보관됩니다.
5. 재가입 제한
- 부정 이용 방지를 위해 탈퇴 후 일정 기간 동안 동일 명의로 재가입이 제한될 수 있습니다.
6. 처리 절차
- 탈퇴는 신청 즉시 완료되지 않고, 관리자 승인 절차를 거쳐 최종 처리됩니다.
위 내용을 모두 확인하였으며, 이에 동의할 경우 탈퇴를 신청할 수 있습니다.',
'2026-09-01 00:00:00', FALSE, 'ACTIVE');
-- ═══════════════════════════════════════════════════════════
-- V18__account_closure_requests.sql
-- ═══════════════════════════════════════════════════════════
-- V18: 회원 탈퇴 신청 대장
-- 사용자가 앱에서 "탈퇴 신청"을 하면 한 줄이 쌓이고(PENDING), 관리자가 승인하면(APPROVED)
-- 그 순간 회원이 실제로 탈퇴(users.status='WITHDRAWN') 처리됩니다.
-- 출금(withdraw=돈 인출)과 헷갈리지 않도록 이름을 'account_closure(계정 해지=탈퇴)'로 둡니다.
CREATE TABLE account_closure_requests (
id BIGINT NOT NULL AUTO_INCREMENT COMMENT '탈퇴 신청 고유번호',
user_id BIGINT NOT NULL COMMENT '신청한 회원(users.id)',
status VARCHAR(20) NOT NULL DEFAULT 'PENDING' COMMENT '진행 상태: PENDING(대기)·APPROVED(승인=탈퇴완료)·REJECTED(반려)',
reason VARCHAR(200) NULL COMMENT '회원이 남긴 탈퇴 사유(선택 입력)',
points_at_request DECIMAL(15,0) NOT NULL DEFAULT 0 COMMENT '신청 시점 보유 포인트(소멸 예정 금액 안내·기록용)',
points_forfeited DECIMAL(15,0) NULL COMMENT '승인(탈퇴 확정) 시점의 실제 소멸 포인트',
requested_at DATETIME NOT NULL COMMENT '신청 일시',
processed_by BIGINT NULL COMMENT '승인·반려한 관리자(admin_accounts.id)',
processed_at DATETIME NULL COMMENT '승인·반려 일시',
PRIMARY KEY (id),
KEY ix_closure_user (user_id),
KEY ix_closure_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='회원 탈퇴 신청·승인 대장';
-- ═══════════════════════════════════════════════════════════
-- V19__card_quota_default.sql
-- ═══════════════════════════════════════════════════════════
-- V19: 회원당 발행 카드 최대 수량 기본값 = 1
-- 발주사 요구: "현재는 1개만 제공". 관리자 운영설정(정책 관리 > 카드 발급 수량)에서 언제든 변경 가능합니다.
-- 전역(GLOBAL) 정책이 없으면 카드 발급 자체가 막히므로, 여기서 기본 1장을 확실히 심어 둡니다.
-- (card_quota_policies 는 SYSTEM VERSIONED — DELETE 는 옛 값을 이력으로 보존하므로 감사 추적이 유지됩니다.)
DELETE FROM card_quota_policies WHERE scope = 'GLOBAL';
INSERT INTO card_quota_policies (scope, target_id, max_cards) VALUES ('GLOBAL', NULL, 1);
-- ═══════════════════════════════════════════════════════════
-- V20__app_gate.sql
-- ═══════════════════════════════════════════════════════════
-- V20: 앱 부팅 게이트(점검 모드 · 강제 업데이트 · 필수 공지 팝업)
-- 회원앱·매장앱이 켜질 때 서버에 물어보고, 상황에 맞게 사용을 막거나 안내를 띄웁니다.
-- 공지에 "대상 구분(회원/매장)"과 "무조건 표시(강제 팝업)"를 추가합니다.
-- · audience : ALL(전체) | USER(회원앱만) | STORE(매장앱만)
-- · force_show: 1이면 해당 앱 메인에 레이어(팝업)로 띄우고, 확인해야 사용할 수 있게 합니다.
-- (notices 는 SYSTEM VERSIONED 가 아니라 별도 플래그 없이 그대로 ALTER 합니다.)
ALTER TABLE notices
ADD COLUMN audience VARCHAR(10) NOT NULL DEFAULT 'ALL' AFTER kind,
ADD COLUMN force_show TINYINT(1) NOT NULL DEFAULT 0 AFTER audience;
-- 부팅 게이트 전역 설정(관리자 '전역 설정'에서 값 변경). 이미 있으면 값을 건드리지 않습니다(멱등).
-- · 점검 모드: 켜면 두 앱 모두 사용 중단 + 안내문/재개 예정 시각 표시
-- · 강제 업데이트: 앱 종류·플랫폼별 최소 지원 버전 + 스토어 이동 주소
-- (스토어 자체에는 강제 기능이 없어, 서버가 최소 버전을 내려주고 앱이 자기 버전과 비교해 막습니다.)
INSERT INTO global_settings (skey, svalue, updated_at) VALUES
('MAINTENANCE_ON', '0', NOW()),
('MAINTENANCE_MSG', '더 나은 서비스를 위해 시스템 점검 중입니다. 잠시 후 다시 이용해 주세요.', NOW()),
('MAINTENANCE_UNTIL','', NOW()),
('MIN_APP_VERSION_USER_IOS', '1.0.0', NOW()),
('MIN_APP_VERSION_USER_ANDROID', '1.0.0', NOW()),
('MIN_APP_VERSION_STORE_IOS', '1.0.0', NOW()),
('MIN_APP_VERSION_STORE_ANDROID','1.0.0', NOW()),
('APP_STORE_URL_USER_IOS', '', NOW()),
('APP_STORE_URL_USER_ANDROID', '', NOW()),
('APP_STORE_URL_STORE_IOS', '', NOW()),
('APP_STORE_URL_STORE_ANDROID','', NOW())
ON DUPLICATE KEY UPDATE svalue = svalue;
-- ═══════════════════════════════════════════════════════════
-- V21__banner_position.sql
-- ═══════════════════════════════════════════════════════════
-- V21: 배너에 "위치(position)" 추가 — 앱의 어느 화면 어느 자리에 보일지 지정
-- USER_HOME(회원 홈) · GUEST(비로그인 화면) · USER_CHARGE(충전 화면) · STORE_HOME(매장 홈)
-- 한 위치에 여러 개면 앱에서 롤링(자동 넘김), 하나면 단일로 보여 줍니다.
-- (banners 는 SYSTEM VERSIONED 가 아니라 그대로 ALTER 합니다.)
ALTER TABLE banners ADD COLUMN position VARCHAR(20) NOT NULL DEFAULT 'USER_HOME' AFTER id;
-- 위치별 예제 배너를 미리 등록해 둡니다(발주사 요구). 관리자가 이미지를 교체해 쓰면 됩니다.
-- · 실제 이미지 파일이 하나라도 있을 때만 시드합니다(없으면 깨진 배너를 만들지 않도록 건너뜀).
-- · 기존 배너 1건은 기본값 USER_HOME 이 되어, USER_HOME 은 2개(롤링) 시연이 됩니다.
INSERT INTO banners (position, image_file_id, link_type, link_target, sort_order, status)
SELECT p.position, f.id, p.link_type, p.link_target, p.sort_order, 'PUBLISHED'
FROM (
SELECT 'USER_HOME' AS position, 'URL' AS link_type, 'https://event.nestpay.co.kr' AS link_target, 2 AS sort_order
UNION ALL SELECT 'GUEST', 'NONE', NULL, 1
UNION ALL SELECT 'USER_CHARGE', 'NONE', NULL, 1
UNION ALL SELECT 'STORE_HOME', 'NONE', NULL, 1
) p
JOIN (SELECT id FROM files WHERE content_type LIKE 'image/%' ORDER BY id LIMIT 1) f;
-- ═══════════════════════════════════════════════════════════
-- V22__seed_examples.sql
-- ═══════════════════════════════════════════════════════════
-- V22: 예제 콘텐츠 시드(발주사 요구 "미리 예제로 등록") — FAQ · 이벤트 · 공지
-- 관리자가 실제 내용으로 교체해 쓰면 됩니다. (약관/개인정보/탈퇴안내는 V17, 배너는 V21에서 시드)
-- 자주 묻는 질문(FAQ)
INSERT INTO faqs (category, question, answer, sort_order, status) VALUES
('계정', '회원가입은 어떻게 하나요?', '본인인증 후 아이디·비밀번호를 설정하면 가입됩니다. 이용을 위해 본인 명의 계좌 등록(1원 인증)이 필요합니다.', 1, 'PUBLISHED'),
('충전', '포인트는 어떻게 충전하나요?', '홈 화면의 [충전]에서 계좌 입금 방식으로 충전할 수 있습니다. 입금자명에 표시된 코드로 자동 인식됩니다.', 2, 'PUBLISHED'),
('결제', '매장에서 어떻게 결제하나요?', '[결제]에서 매장 QR을 스캔하면 보유 포인트로 결제됩니다.', 3, 'PUBLISHED'),
('출금', '출금(환급)은 얼마나 걸리나요?', '출금 신청 후 정책에 따라 지정된 시각에 등록 계좌로 지급됩니다. 유상(충전) 포인트만 출금할 수 있습니다.', 4, 'PUBLISHED'),
('카드', '카드번호·CVC는 어디서 보나요?', '내 카드에서 자물쇠를 누르고 PIN(또는 패스키) 인증을 하면 전체 번호·CVC를 볼 수 있습니다.', 5, 'PUBLISHED'),
('선물', '포인트를 선물할 수 있나요?', '[선물]에서 상대방 휴대폰번호·링크·QR·카드번호로 포인트를 보낼 수 있습니다.', 6, 'PUBLISHED');
-- 이벤트(외부 이벤트 페이지 링크 — 앱 이벤트 목록에서 눌러 이동)
INSERT INTO notices (kind, audience, force_show, title, body, link_url, status, created_at) VALUES
('EVENT', 'ALL', 0, '첫 충전 이벤트', '신규 가입 후 첫 충전 시 5,000원을 드립니다.', 'https://event.nestpay.co.kr/first-charge', 'PUBLISHED', NOW()),
('EVENT', 'ALL', 0, '친구 초대 이벤트', '친구를 초대하고 함께 포인트를 받아 가세요.', 'https://event.nestpay.co.kr/invite', 'PUBLISHED', NOW());
-- 일반 공지
INSERT INTO notices (kind, audience, force_show, title, body, status, created_at) VALUES
('NOTICE', 'ALL', 0, '서비스 이용 안내', '네스트페이를 이용해 주셔서 감사합니다. 안전한 거래를 위해 앱을 최신 버전으로 유지해 주세요.', 'PUBLISHED', NOW());
-- ═══════════════════════════════════════════════════════════
-- V23__passkey_challenge_nonce.sql
-- ═══════════════════════════════════════════════════════════
-- V23: 패스키 챌린지 1회용 소진(replay 방지)
-- 챌린지는 서버 도장이 찍힌 무상태 토큰에 담겨 있어 유효시간 안에는 서명을 재사용할 수 있었습니다.
-- 인증(등록·로그인·거래)에 쓴 챌린지를 이 표에 남겨 두고, 같은 챌린지가 또 오면 거부합니다.
CREATE TABLE used_challenges (
challenge VARCHAR(64) NOT NULL COMMENT '이미 사용한 패스키 챌린지(1회용)',
expires_at DATETIME NOT NULL COMMENT '이 기록을 지워도 되는 시각(토큰 만료 이후)',
created_at DATETIME NOT NULL,
PRIMARY KEY (challenge),
-- 만료분 정리(DELETE ... WHERE expires_at < NOW())가 인덱스를 타도록 합니다.
KEY ix_used_challenges_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='패스키 챌린지 재사용(replay) 방지';
-- ═══════════════════════════════════════════════════════════
-- V24__admin_login_ratelimit.sql
-- ═══════════════════════════════════════════════════════════
-- V24: 관리자 로그인(1단계 비밀번호) 이용제한 규칙
-- 기존엔 OTP(2단계)에만 제한이 있어 1단계 비밀번호 무차별 대입을 막지 못했습니다.
-- 아이디 기준 5분 10회로 제한합니다(회원 로그인과 동일 강도). 이미 있으면 그대로.
INSERT INTO rate_limit_rules (action_key, description, window_sec, max_count, basis, enabled, updated_at)
VALUES ('admin.login', '관리자 로그인 시도 (아이디/5분)', 300, 10, 'USER', TRUE, NOW())
ON DUPLICATE KEY UPDATE action_key = action_key;
-- ═══════════════════════════════════════════════════════════
-- V25__deposit_accounts.sql
-- ═══════════════════════════════════════════════════════════
-- V25: 사용자 충전용 입금통장(회사 수납 계좌) 관리
-- 지금은 앱에 계좌가 하드코딩돼 있어, 관리자가 바꾸려면 앱을 다시 배포해야 합니다.
-- 이 표로 관리자에서 입금통장을 등록·활성화하고, 앱은 활성 계좌를 받아 보여 줍니다.
-- (제공 방식(고정계좌/가상계좌)이 확정되기 전까지는 '고정 계좌' 안내 용도로 씁니다.)
-- ※ 이 계좌는 모든 사용자에게 "입금하세요"라고 공개되는 회사 수납 계좌라 암호화하지 않습니다(개인정보 아님).
CREATE TABLE deposit_accounts (
id BIGINT NOT NULL AUTO_INCREMENT COMMENT '입금통장 고유번호',
bank_name VARCHAR(40) NOT NULL COMMENT '은행 이름(예: 하나은행)',
account_no VARCHAR(40) NOT NULL COMMENT '계좌번호',
holder VARCHAR(60) NOT NULL COMMENT '예금주',
label VARCHAR(60) NULL COMMENT '표시용 이름표(예: 충전 계좌)',
sort_order INT NOT NULL DEFAULT 0 COMMENT '노출 순서(작을수록 앞)',
status VARCHAR(10) NOT NULL DEFAULT 'ACTIVE' COMMENT 'ACTIVE(노출)|HIDDEN(숨김)',
created_at DATETIME NOT NULL,
PRIMARY KEY (id),
KEY ix_deposit_account_status (status, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='사용자 충전 입금통장(회사 수납 계좌)';
-- 현재 앱에 하드코딩돼 있던 계좌를 그대로 시드(앱이 서버 값으로 전환돼도 표시가 끊기지 않게).
INSERT INTO deposit_accounts (bank_name, account_no, holder, label, sort_order, status, created_at)
VALUES ('하나은행', '183-910047-00915', '(주)페이네스트', '충전 계좌', 1, 'ACTIVE', NOW());
-- ═══════════════════════════════════════════════════════════
-- V26__remove_demo_banners.sql
-- ═══════════════════════════════════════════════════════════
-- V26: 데모(자리표시) 배너 제거
--
-- 왜 지우나요?
-- V21 에서 "이미지 파일 1개"를 모든 위치(USER_HOME/GUEST/USER_CHARGE/STORE_HOME)에
-- 데모로 넣어 두었습니다. 이 이미지는 내용이 없는 단색 그림이라, 실제 앱 화면에서는
-- "배경색만 있고 내용이 없는 색 블록"처럼 보였습니다(특히 매장 홈).
--
-- 초기 배포본은 배너 없이 시작하는 것이 맞습니다. 실제 배너는 운영자가 관리자 화면에서
-- 직접 이미지를 올려 등록합니다. 그래서 V21 이 넣은 데모 배너를 여기서 정리합니다.
--
-- (신규 배포: V21 이 넣고 → V26 이 지우므로 결과적으로 배너 0개로 깨끗하게 시작합니다.)
-- (배너 기능 자체는 그대로 살아 있습니다 — 표(banners)와 position 컬럼은 유지됩니다.)
--
-- 안전장치: V21 데모가 사용한 "가장 오래된 이미지 파일" 을 참조하는 배너만 지웁니다.
-- (운영자가 이미 등록한 실제 배너는 다른 파일을 쓰므로 지워지지 않습니다.)
DELETE FROM banners
WHERE image_file_id = (
SELECT id FROM (
SELECT id FROM files WHERE content_type LIKE 'image/%' ORDER BY id LIMIT 1
) AS demo_file
);
-- ═══════════════════════════════════════════════════════════
-- V27__pg_order_lifecycle.sql
-- ═══════════════════════════════════════════════════════════
-- V27: PG 결제 주문(pg_orders) 수명주기 컬럼 + 만료시간 설정값
--
-- PG 오픈 API(WeChat식 결제 게이트웨이)를 위해 주문의 "언제 만료/결제/취소됐는지"를 기록합니다.
-- · expires_at : 이 시각이 지나면 결제 실패(EXPIRED) 처리 + 실패 웹훅 (스위퍼가 사용)
-- · paid_at : 사용자가 앱으로 결제를 완료한 시각
-- · canceled_at : 전체취소된 시각
-- (pg_orders 는 일반 테이블이라 그대로 ALTER 합니다. 상태 status: PENDING|PAID|EXPIRED|CANCELED)
ALTER TABLE pg_orders
ADD COLUMN expires_at datetime NULL COMMENT '주문 만료 시각(지나면 결제 실패) - 만료 스위퍼 기준' AFTER status,
ADD COLUMN paid_at datetime NULL COMMENT '결제 완료 시각' AFTER expires_at,
ADD COLUMN canceled_at datetime NULL COMMENT '전체취소 시각' AFTER paid_at;
-- 만료 스위퍼가 "PENDING 이면서 expires_at 지난" 주문을 빨리 찾도록 인덱스 추가.
CREATE INDEX ix_pg_expire ON pg_orders (status, expires_at);
-- 결제 주문 유효시간(분) — 관리자가 운영설정에서 바꿀 수 있고, 초기값은 5분입니다.
-- 이 값이 주문의 QR 유효시간과 만료 시각을 함께 정합니다.
INSERT INTO global_settings (skey, svalue, updated_by, updated_at)
VALUES ('PG_ORDER_TTL_MINUTES', '5', NULL, NOW())
ON DUPLICATE KEY UPDATE svalue = svalue;
-- ═══════════════════════════════════════════════════════════
-- V28__seed_default_policies.sql
-- ═══════════════════════════════════════════════════════════
-- V28: 운영 필수 "기본 정책" 시드
--
-- 왜 필요한가:
-- 수수료·정산지급·취소기간·한도·포인트소멸·FDS 룰은 시스템이 동작하려면 반드시 있어야 하는
-- 기본 설정인데, 그동안 로컬 DB 에만 수동으로 들어 있었고 마이그레이션에는 없었습니다.
-- → 신규 서버에 배포하면 비어 있어 결제·정산·한도 로직이 기준값 없이 동작하는 문제가 있었습니다.
-- 이 마이그레이션으로 "모든 환경 공통 기본값"을 심어, 배포하면 바로 정상 동작하게 합니다.
--
-- 멱등: GLOBAL 정책은 target_id 가 NULL 이라 UNIQUE 인덱스로도 중복이 안 걸립니다(NULL != NULL).
-- 그래서 NOT EXISTS 가드로 "이미 있으면 건너뜀"을 보장합니다(로컬처럼 이미 있는 곳서도 안전).
-- 수수료: 매장 결제 3% (정액 0)
INSERT INTO fee_policies (fee_type, scope, target_id, rate, fixed_amount)
SELECT 'MERCHANT_PAYMENT', 'GLOBAL', NULL, 3.0000, 0
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM fee_policies WHERE fee_type='MERCHANT_PAYMENT' AND scope='GLOBAL' AND target_id IS NULL);
-- 정산 지급: 매장 당일 10시 / 회원 익일 14시
INSERT INTO payout_policies (scope, target_id, delay_days, execute_time)
SELECT 'GLOBAL_MERCHANT', NULL, 0, '10:00:00'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM payout_policies WHERE scope='GLOBAL_MERCHANT' AND target_id IS NULL);
INSERT INTO payout_policies (scope, target_id, delay_days, execute_time)
SELECT 'GLOBAL_USER', NULL, 1, '14:00:00'
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM payout_policies WHERE scope='GLOBAL_USER' AND target_id IS NULL);
-- 취소 가능 기간: 7일
INSERT INTO cancel_policies (scope, target_id, days)
SELECT 'GLOBAL', NULL, 7
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM cancel_policies WHERE scope='GLOBAL' AND target_id IS NULL);
-- 한도: 일일 충전 200만원
INSERT INTO limit_policies (limit_type, `window`, scope, target_id, amount)
SELECT 'DEPOSIT', 'DAILY', 'GLOBAL', NULL, 2000000
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM limit_policies WHERE limit_type='DEPOSIT' AND `window`='DAILY' AND scope='GLOBAL' AND target_id IS NULL);
-- 포인트 소멸: 충전 60개월 / 적립 12개월 (lot_type UNIQUE)
INSERT IGNORE INTO point_expiry_policies (lot_type, months) VALUES ('DEPOSIT', 60);
INSERT IGNORE INTO point_expiry_policies (lot_type, months) VALUES ('REWARD', 12);
-- FDS 룰 8종 (code UNIQUE — 이미 있으면 건너뜀). 위험 항목 탐지 기본 정책.
INSERT IGNORE INTO fds_rules (code, name, params, action, enabled, updated_at) VALUES
('PASSTHROUGH', '패스스루(입금 즉시 이체)', '{"scan_hours":24,"window_minutes":30,"threshold":1000000}', 'ALERT', 1, NOW()),
('GIFT_CONCENTRATION', '선물 집중 수신', '{"scan_hours":24,"senders":5,"threshold":1000000}', 'ALERT', 1, NOW()),
('MULTI_ACCOUNT_DEVICE', '다계정 기기', '{"scan_hours":24,"principals":3}', 'ALERT', 1, NOW()),
('ENUMERATION', '무차별 시도(IP 열거)', '{"window_minutes":10,"attempts":20}', 'ALERT', 1, NOW()),
('SELF_PAYMENT', '본인 매장 결제', '{"scan_hours":24}', 'MONITOR', 0, NOW()),
('CANCEL_ABUSE', '취소 남용', '{"scan_hours":168,"cancels":5}', 'MONITOR', 0, NOW()),
('POST_CHANGE_WITHDRAW', '정보 변경 직후 출금', '{"window_minutes":60,"threshold":500000}', 'MONITOR', 0, NOW()),
('NIGHT_LARGE', '심야 고액 거래', '{"from_hour":0,"to_hour":6,"threshold":1000000}', 'MONITOR', 0, NOW());
-- ═══════════════════════════════════════════════════════════
-- V29__file_ref_fks.sql
-- ═══════════════════════════════════════════════════════════
-- V29: 파일 참조 무결성 완성 — 논리참조였던 2곳에 외래키(FK) 추가
--
-- 왜 필요한가:
-- 파일·이미지는 중앙 대장(files) 한 곳에만 실물 정보를 두고, 다른 테이블은 번호(file_id)로만
-- 참조하는 것이 원칙입니다. 대부분(merchant_documents·inquiry_attachments·banners)은 FK 로
-- 강제돼 있는데, 아래 두 곳은 "files.id 참조"라고 주석만 있고 FK 제약이 없어 무결성이
-- 코드에만 의존했습니다(잘못된 번호가 들어가도 DB 가 못 막음).
-- · users.avatar_file_id (V13, 프로필 사진)
-- · notices.image_file_id (V15, 이벤트 이미지)
-- → 이 마이그레이션으로 두 곳에도 FK 를 걸어, 없는 파일 번호를 넣지 못하게 하고
-- "참조 중인 파일은 대장에서 함부로 삭제되지 못하게(RESTRICT)" 만듭니다.
-- (파일 관리 메뉴의 삭제는 이 제약 덕분에 고아 파일만 안전하게 지울 수 있습니다.)
--
-- 안전성: 두 컬럼 모두 NULL 허용이고, 배포 전 점검에서 고아값(대장에 없는 번호) 0건 확인.
-- 프로필 사진: users.avatar_file_id → files.id
ALTER TABLE users
ADD CONSTRAINT fk_user_avatar_file FOREIGN KEY (avatar_file_id) REFERENCES files (id);
-- 이벤트 이미지: notices.image_file_id → files.id
ALTER TABLE notices
ADD CONSTRAINT fk_notice_image_file FOREIGN KEY (image_file_id) REFERENCES files (id);
-- ═══════════════════════════════════════════════════════════
-- V30__user_kyc_status.sql
-- ═══════════════════════════════════════════════════════════
-- V30: 회원 본인인증 상태(kyc_status) — 관리자 사전생성 회원의 "앱서 본인인증" 게이트용
--
-- 왜 필요한가:
-- 관리자가 회원을 직접 만들 수 있게 되면서(결정: 사전생성 후 앱서 본인인증),
-- "아직 본인인증(CI)을 안 한 회원"과 "정상 회원"을 구분할 표식이 필요합니다.
-- · VERIFIED : 앱에서 본인인증을 마친 정상 회원(기존·자체가입은 전부 이 값)
-- · PENDING : 관리자가 사전생성한 회원 — 최초 로그인 후 앱에서 본인인증을 마쳐야 서비스 이용 가능
--
-- 기본값을 VERIFIED 로 두어 기존 회원·자체가입 흐름은 전혀 영향받지 않습니다.
-- (자체가입은 가입 시점에 이미 본인인증을 하므로 항상 VERIFIED)
--
-- users 는 이력 자동보존(SYSTEM VERSIONED) 테이블이라 KEEP 선언이 필요합니다.
SET @@system_versioning_alter_history = KEEP;
ALTER TABLE users
ADD COLUMN kyc_status VARCHAR(20) NOT NULL DEFAULT 'VERIFIED'
COMMENT 'VERIFIED(본인인증 완료)|PENDING(관리자 사전생성 - 앱서 본인인증 대기)' AFTER status;
-- ═══════════════════════════════════════════════════════════
-- V31__flatten_admin_roles.sql
-- ═══════════════════════════════════════════════════════════
-- V31: 관리자 권한 평탄화 — root/일반 구분 제거(모든 관리자 동등)
--
-- 결정(발주사): "관리자는 전부 동등한 구조. root/admin 권한을 별도 설정하지 않는다."
-- · 그동안 is_root(최고 관리자) 여부로 일부 기능(계정관리·정책저장·감사열람 등)을 제한했으나,
-- 이제 모든 관리자가 동일한 관리 권한을 갖도록 바꿉니다.
-- · 코드에서는 로그인 시 모든 관리자에게 동일 권한을 부여하고(권한 게이트가 항상 통과),
-- 화면에서도 root/일반 구분 표시를 없앱니다.
-- · is_root 컬럼 자체는 남겨 두되(이력·호환) 값을 전부 TRUE 로 맞춰 "전원 동등"을 데이터로도 반영합니다.
-- (이후 생성되는 관리자도 동등하게 만들어집니다 — 애플리케이션에서 처리)
--
-- 안전장치("마지막 관리자 삭제/비활성 금지")는 is_root 가 전부 TRUE 가 되므로
-- "마지막 관리자 보호"와 동일하게 동작합니다.
UPDATE admin_accounts SET is_root = TRUE WHERE is_root = FALSE;
-- ═══════════════════════════════════════════════════════════
-- V32__neutralize_admin_names.sql
-- ═══════════════════════════════════════════════════════════
-- V32: 관리자 등급 표현 제거 — 시드 계정 이름의 "최고/일반 관리자" 라벨을 중립적으로 변경
--
-- 결정(발주사): 관리자 간 등급이 없다. 그런데 초기 시드 계정 이름이 "최고 관리자"·"일반 관리자"라
-- 화면에 등급이 있는 것처럼 보였습니다. 등급을 연상시키는 이름을 중립적인 이름으로 바꿉니다.
-- · 로그인 아이디(root/admin)는 계정 식별·로그인 자격이라 바꾸지 않습니다(운영자 로그인 유지).
-- · 이름은 관리자 화면에서 각자 자유롭게 바꿀 수 있습니다(여기서는 초기 라벨만 정리).
UPDATE admin_accounts SET name = '관리자(root)' WHERE login_id = 'root' AND name = '최고 관리자';
UPDATE admin_accounts SET name = '관리자(admin)' WHERE login_id = 'admin' AND name = '일반 관리자';
-- ═══════════════════════════════════════════════════════════
-- V33__drop_merchant_biz_cert_path.sql
-- ═══════════════════════════════════════════════════════════
-- V33: merchants.biz_cert_path(사업자등록증 경로) 유휴·패턴위반 컬럼 제거
--
-- 왜 제거하나:
-- · 모든 파일·이미지는 중앙 대장(files)에 저장하고 다른 테이블은 file_id 로만 참조하는 것이 확정 원칙입니다.
-- · biz_cert_path 는 파일 "경로"를 varchar 로 직접 들고 있어 이 원칙을 위반합니다.
-- · 실제 사업자등록증은 V4 이후 merchant_documents(file_id → files) 로 관리되며,
-- biz_cert_path 는 코드 어디에서도 읽거나 쓰지 않는 죽은 컬럼입니다(전수 확인).
-- → 안전하게 제거해 "파일은 files 대장 참조" 원칙을 완전히 지킵니다.
--
-- merchants 는 이력 자동보존(SYSTEM VERSIONED) 테이블이라 KEEP 선언이 필요합니다.
SET @@system_versioning_alter_history = KEEP;
ALTER TABLE merchants DROP COLUMN biz_cert_path;
-- ═══════════════════════════════════════════════════════════
-- V34__webhook_claim_token.sql
-- ═══════════════════════════════════════════════════════════
-- V34: 웹훅 발송 워커에 "워커별 집기 표식(claim_token)" 추가 — 2대 서버 중복 발송 방지
--
-- 문제(감사 발견, CRITICAL):
-- 기존 claimDue 는 PENDING→SENDING 으로만 바꾸고 "누가 집었는지" 표식이 없었고,
-- selectSending 은 SENDING 전체를 소유자 구분 없이 다시 읽었습니다.
-- → L4 뒤 2대 서버가 같은 20초 틱에 각자 claimDue 하면, 둘 다 낮은 id 의 SENDING 을 함께 읽어
-- 같은 웹훅(PAYMENT_COMPLETED/EXPIRED 등)을 매장에 2번 보냄(HMAC 서명까지 동일 → 매장이 구분 불가).
--
-- 해결: outbox_jobs 와 동일한 검증된 패턴 — 워커가 UUID 토큰으로 자기 몫만 집고, 자기 토큰 것만 발송.
-- (claimDue 가 claim_token 을 찍고, selectSending 은 claim_token 일치분만 조회)
ALTER TABLE webhook_deliveries
ADD COLUMN claim_token VARCHAR(64) NULL COMMENT '발송 워커의 집기 표식(UUID) - 2대 서버 중복 발송 방지' AFTER claimed_at;
-- ═══════════════════════════════════════════════════════════
-- V35__apply_integrity_routines.sql
-- ═══════════════════════════════════════════════════════════
-- V35: 원장 무결성 방어 루틴 실제 적용 (설계 db/routines.sql 을 Flyway 로 적용)
-- · append-only 트리거 14종(원장·감사·PII·증거로그·입금통지·로트) + 파티션 관리 프로시저 2·이벤트 2
-- · 코드 UPDATE 화이트리스트 정적감사 통과(트리거가 정상 흐름을 막지 않음), 대상 테이블 전부 BASE.
-- · flyway-mysql 이 DELIMITER 를 해석합니다. 이후 루틴 변경은 새 V번호로 추가(Flyway 불변성).
-- =====================================================================
-- nestpay MariaDB 루틴 (routines.sql) - 전수 감사(2026-07-21) 반영판
-- 방침(유지보수 최우선): 비즈니스 로직은 전부 Java. DB 루틴은 두 종류만.
-- 1) 불변식 방어 트리거 - 로직 없음, "금지"만.
-- 2) 파티션 수명 관리(생성·보존 DROP) - DB가 스스로 해야 자연스러운 유일한 운영 루틴.
-- 1차 방어는 권한(GRANT), 트리거는 root/DBA 실수까지 막는 2차 방어선.
-- =====================================================================
DELIMITER $$
-- ---------------------------------------------------------------------
-- 1. append-only 방어 트리거 (#29) - 원장·감사·접근기록·증거 로그
-- ---------------------------------------------------------------------
CREATE TRIGGER trg_ledger_no_update BEFORE UPDATE ON ledger_entries FOR EACH ROW
BEGIN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'ledger_entries is append-only: UPDATE forbidden'; END$$
CREATE TRIGGER trg_ledger_no_delete BEFORE DELETE ON ledger_entries FOR EACH ROW
BEGIN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'ledger_entries is append-only: DELETE forbidden'; END$$
CREATE TRIGGER trg_txn_no_delete BEFORE DELETE ON transactions FOR EACH ROW
BEGIN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'transactions is append-only: DELETE forbidden'; END$$
-- transactions UPDATE 화이트리스트: status·confirmed_at·bank_tran_ref·fail_reason만 가변.
-- 금액·유형·참조·스냅샷·증빙 컬럼 전부 불변 (감사 보완: 스냅샷 변조 차단)
CREATE TRIGGER trg_txn_immutable_columns BEFORE UPDATE ON transactions FOR EACH ROW
BEGIN
IF NEW.amount <> OLD.amount OR NEW.type <> OLD.type OR NEW.subtype <> OLD.subtype
OR NEW.fee_amount <> OLD.fee_amount OR NEW.txn_uid <> OLD.txn_uid
OR NEW.initiator_type <> OLD.initiator_type OR NEW.initiator_id <> OLD.initiator_id
OR NOT (NEW.idempotency_key <=> OLD.idempotency_key)
OR NOT (NEW.card_id <=> OLD.card_id)
OR NOT (NEW.counterparty_card_id <=> OLD.counterparty_card_id)
OR NOT (NEW.merchant_id <=> OLD.merchant_id)
OR NOT (NEW.related_txn_id <=> OLD.related_txn_id)
OR NOT (NEW.fee_rate_snap <=> OLD.fee_rate_snap)
OR NOT (NEW.fee_fixed_snap <=> OLD.fee_fixed_snap)
OR NOT (NEW.vat_amount <=> OLD.vat_amount)
OR NOT (NEW.cancelable_until <=> OLD.cancelable_until)
OR NOT (NEW.scheduled_at <=> OLD.scheduled_at)
OR NOT (NEW.bank_account_id <=> OLD.bank_account_id)
OR NOT (NEW.pg_order_id <=> OLD.pg_order_id)
OR NOT (NEW.memo <=> OLD.memo)
OR NEW.created_at <> OLD.created_at THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'transactions: only status/confirmed_at/bank_tran_ref/fail_reason may change';
END IF;
END$$
CREATE TRIGGER trg_audit_no_update BEFORE UPDATE ON audit_logs FOR EACH ROW
BEGIN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'audit_logs is append-only'; END$$
CREATE TRIGGER trg_audit_no_delete BEFORE DELETE ON audit_logs FOR EACH ROW
BEGIN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'audit_logs is append-only'; END$$
-- 접근기록·증거 로그 방어 대칭화 (감사 보완: security-5/8)
CREATE TRIGGER trg_pii_no_update BEFORE UPDATE ON pii_access_logs FOR EACH ROW
BEGIN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'pii_access_logs is append-only'; END$$
CREATE TRIGGER trg_pii_no_delete BEFORE DELETE ON pii_access_logs FOR EACH ROW
BEGIN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'pii_access_logs is append-only (5yr partition drop only)'; END$$
CREATE TRIGGER trg_login_no_update BEFORE UPDATE ON login_histories FOR EACH ROW
BEGIN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'login_histories is append-only'; END$$
CREATE TRIGGER trg_reject_no_update BEFORE UPDATE ON reject_logs FOR EACH ROW
BEGIN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'reject_logs is append-only'; END$$
CREATE TRIGGER trg_extapi_no_update BEFORE UPDATE ON external_api_logs FOR EACH ROW
BEGIN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'external_api_logs is append-only'; END$$
-- 입금 통지: 상태 전이 컬럼만 가변, 증거 원문 불변 (감사 보완: security-9)
CREATE TRIGGER trg_notice_immutable BEFORE UPDATE ON deposit_notices FOR EACH ROW
BEGIN
IF NOT (NEW.raw_text <=> OLD.raw_text) OR NEW.dedup_key <> OLD.dedup_key
OR NOT (NEW.bank_tran_ref <=> OLD.bank_tran_ref)
OR NOT (NEW.parsed_amount <=> OLD.parsed_amount)
OR NOT (NEW.parsed_name <=> OLD.parsed_name)
OR NOT (NEW.identifier_value <=> OLD.identifier_value)
OR NEW.received_at <> OLD.received_at THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'deposit_notices: only status/matched_txn_id may change';
END IF;
END$$
CREATE TRIGGER trg_notice_no_delete BEFORE DELETE ON deposit_notices FOR EACH ROW
BEGIN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'deposit_notices is append-only (archive runbook only)'; END$$
-- ---------------------------------------------------------------------
-- 2. 로트 방어: 감소만 허용, 속성 불변
-- 복원(취소·출금실패·선물반환)은 UPDATE 증가가 아니라 "신규 로트 생성
-- (origin_lot_id 참조·lot_type·expires_at 승계)" 방식 - queries.sql 0장 규약
-- ---------------------------------------------------------------------
CREATE TRIGGER trg_lot_decrease_only BEFORE UPDATE ON point_lots FOR EACH ROW
BEGIN
IF NEW.amount_remaining > OLD.amount_remaining OR NEW.amount_remaining < 0
OR NEW.amount_init <> OLD.amount_init OR NEW.expires_at <> OLD.expires_at
OR NEW.lot_type <> OLD.lot_type OR NEW.wallet_id <> OLD.wallet_id THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'point_lots: remaining may only decrease; attributes immutable';
END IF;
END$$
DELIMITER ;
-- ---------------------------------------------------------------------
-- 3. 파티션 자동 확장 (감사 교정: 대상 = V2 실제 파티션 7종과 일치,
-- 테이블별 실패 격리 + 신규 파티션 ANALYZE + p_max 적체 감시는 11장 대사)
-- ---------------------------------------------------------------------
DELIMITER $$
CREATE PROCEDURE sp_extend_month_partitions()
BEGIN
DECLARE next_edge DATE DEFAULT DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 2 MONTH), '%Y-%m-01');
DECLARE pname VARCHAR(10) DEFAULT DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), 'p%Y%m');
DECLARE tbl VARCHAR(64);
DECLARE done INT DEFAULT 0;
-- 하드코딩 대신 p_max 실존 파티션 테이블 자동 선별 (감사 교정: 목록 불일치 재발 방지)
DECLARE cur CURSOR FOR
SELECT DISTINCT p.table_name FROM information_schema.partitions p
WHERE p.table_schema = DATABASE() AND p.partition_name = 'p_max';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur;
read_loop: LOOP
FETCH cur INTO tbl;
IF done = 1 THEN LEAVE read_loop; END IF;
BEGIN
-- 테이블 단위 실패 격리: 오류는 app_error_logs에 남기고 다음 테이블 진행
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
INSERT INTO app_error_logs (severity, source, error_code, message, created_at)
VALUES ('CRITICAL', 'BATCH', 'PARTITION_EXTEND_FAIL', CONCAT('table=', tbl, ' pname=', pname), NOW());
IF NOT EXISTS (
SELECT 1 FROM information_schema.partitions p2
WHERE p2.table_schema = DATABASE() AND p2.table_name = tbl AND p2.partition_name = pname
) THEN
SET @ddl = CONCAT('ALTER TABLE ', tbl, ' REORGANIZE PARTITION p_max INTO (',
' PARTITION ', pname, ' VALUES LESS THAN (TO_DAYS(''', next_edge, ''')),',
' PARTITION p_max VALUES LESS THAN MAXVALUE)');
PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;
SET @an = CONCAT('ANALYZE TABLE ', tbl); -- 새 파티션 통계 공백 방지(감사 보완)
PREPARE s FROM @an; EXECUTE s; DEALLOCATE PREPARE s;
END IF;
END;
END LOOP;
CLOSE cur;
END$$
-- ---------------------------------------------------------------------
-- 4. 보존 파티션 DROP (감사 신설: scale-7) - 보존 매트릭스(schema.dbml 서문) 집행
-- DELETE 대신 O(1) DROP. 대상·개월수는 호출부(이벤트)에서 명시.
-- ---------------------------------------------------------------------
CREATE PROCEDURE sp_drop_expired_partitions(IN in_table VARCHAR(64), IN keep_months INT)
BEGIN
DECLARE pname VARCHAR(64);
DECLARE done INT DEFAULT 0;
DECLARE edge VARCHAR(10) DEFAULT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL keep_months MONTH), 'p%Y%m');
DECLARE cur CURSOR FOR
SELECT p.partition_name FROM information_schema.partitions p
WHERE p.table_schema = DATABASE() AND p.table_name = in_table
AND p.partition_name LIKE 'p2%' AND p.partition_name < edge;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur;
drop_loop: LOOP
FETCH cur INTO pname;
IF done = 1 THEN LEAVE drop_loop; END IF;
SET @ddl = CONCAT('ALTER TABLE ', in_table, ' DROP PARTITION ', pname);
PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;
INSERT INTO audit_logs (actor_type, action, target_type, detail, created_at)
VALUES ('SYSTEM', 'PARTITION_DROP', in_table, pname, NOW());
END LOOP;
CLOSE cur;
END$$
DELIMITER ;
-- 매월 25일 03:00 KST: 파티션 확장 / 04:00: 보존 집행 (outbox 워커가 이중 감시)
CREATE EVENT IF NOT EXISTS ev_extend_partitions
ON SCHEDULE EVERY 1 MONTH STARTS (TIMESTAMP(DATE_FORMAT(CURDATE(), '%Y-%m-25')) + INTERVAL 3 HOUR)
DO CALL sp_extend_month_partitions();
DELIMITER $$
CREATE EVENT IF NOT EXISTS ev_retention_drop
ON SCHEDULE EVERY 1 MONTH STARTS (TIMESTAMP(DATE_FORMAT(CURDATE(), '%Y-%m-25')) + INTERVAL 4 HOUR)
DO BEGIN
CALL sp_drop_expired_partitions('notification_inbox', 2); -- 30일 보존(2개월 여유)
CALL sp_drop_expired_partitions('external_api_logs', 60); -- 5년
CALL sp_drop_expired_partitions('reject_logs', 60);
CALL sp_drop_expired_partitions('app_error_logs', 60);
CALL sp_drop_expired_partitions('login_histories', 60);
CALL sp_drop_expired_partitions('pii_access_logs', 60);
END$$
DELIMITER ;
-- ledger_entries는 보존 DROP 대상 아님(영구 - 보존 매트릭스)
-- ---------------------------------------------------------------------
-- 5. 권한 설계 (1차 방어 - 배포 스크립트에서 실계정·서버 2대 IP로 치환.
-- 'app'@'%' 금지: 'app'@'<API서버1 IP>', 'app'@'<API서버2 IP>'로 한정 - 감사 보완)
-- ---------------------------------------------------------------------
-- INSERT/SELECT 전용(증거·기록): ledger_entries, audit_logs, pii_access_logs,
-- login_histories, reject_logs, external_api_logs, app_error_logs, lot_allocations,
-- policy_agreements, integrity_findings(상태 컬럼만 UPDATE 허용 시 별도)
-- INSERT/SELECT/UPDATE(상태 전이형): transactions(트리거로 컬럼 제한), deposit_notices(동일),
-- point_lots(트리거로 감소만), 나머지 업무 테이블
-- DELETE 허용(보존 매트릭스 명시분만): outbox_jobs(DONE 90일), webhook_deliveries(180일),
-- hourly_summaries(90일)
-- DDL·TRIGGER·EVENT: 마이그레이션 전용 계정만. 파티션 DROP은 이벤트(definer) 경유.
-- ---------------------------------------------------------------------
-- 6. transactions·deposit_notices 연 단위 아카이브 런북 (감사 신설: scale-2)
-- * 비파티션 대형 2종의 5년+ 성장 대응. DBA 절차 - 자동화하지 않는다(위험 작업).
-- 1) 대상 연도 확정(예: 만 5년 경과분). 사전 조건: 해당 기간 UNKNOWN/HOLD 0건 확인.
-- 2) 마이그레이션 계정으로: CREATE TABLE transactions_arch_YYYY LIKE transactions;
-- (트리거 없음 상태로 생성됨을 확인)
-- 3) INSERT INTO transactions_arch_YYYY SELECT ... WHERE created_at < :edge (연 단위 청크)
-- 4) 행수·SUM(amount)·MIN/MAX(id) 대조 검증 기록(audit_logs)
-- 5) 원본 삭제: 방어 트리거 trg_txn_no_delete를 마이그레이션 계정이 일시 DROP →
-- DELETE (id 범위 청크) → 트리거 재생성 → 검증 재실행. 전 과정 audit_logs 기록.
-- 6) 아카이브 테이블은 읽기전용 계정만 SELECT 부여. related_txn_id 교차 참조는
-- 추적 화면에서 아카이브 테이블 UNION 조회로 유지.
-- ---------------------------------------------------------------------
-- ═══════════════════════════════════════════════════════════
-- V36__ratelimit_hardening.sql
-- ═══════════════════════════════════════════════════════════
-- V36: 이용제한(rate limit) 규칙 보강 — 보안 감사에서 누락으로 확인된 민감동작에 제한을 추가합니다.
-- · 매장 로그인: 회원/관리자 로그인과 달리 아무 제한이 없어 무차별 대입이 가능했음 → 아이디·IP 이중 제한.
-- · 회원 로그인 IP 제한: 기존 규칙은 아이디 기준뿐이라 한 IP 에서 여러 아이디로 스프레이가 가능했음 → IP 제한 추가.
-- · 1원 보내기(실은행 송금): 제한이 없어 임의 계좌로 반복 송금(건당 비용·계좌열거) 가능했음 → 회원 기준 제한.
-- · 정산 신청: 멱등키로 중복은 이미 막히나, 남용 억제를 위해 분당 호출을 추가로 제한(방어 심화).
INSERT INTO rate_limit_rules (action_key, description, window_sec, max_count, basis, enabled, updated_at) VALUES
('store.login', '매장 로그인 시도 (아이디/5분)', 300, 10, 'USER', TRUE, NOW()),
('store.login.ip', '매장 로그인 시도 (IP/5분)', 300, 30, 'IP', TRUE, NOW()),
('login.ip', '회원 로그인 시도 (IP/5분·스프레이 차단)', 300, 30, 'IP', TRUE, NOW()),
('onboarding.bank.send', '1원 보내기 (회원/10분)', 600, 5, 'USER', TRUE, NOW()),
('settle.create', '정산 신청 (매장/분)', 60, 5, 'USER', TRUE, NOW());
-- ═══════════════════════════════════════════════════════════
-- V37__pg_orders_txn_index.sql
-- ═══════════════════════════════════════════════════════════
-- ─────────────────────────────────────────────────────────────
-- V37: pg_orders.txn_id 인덱스 추가
--
-- 왜 필요한가:
-- 결제 취소 권한 정책(2026-07-27: 회원 금지, 매장·관리자만)에 따라
-- 매장앱·관리자 취소 때마다 "이 결제가 PG 주문에 붙은 결제인지"를
-- pg_orders 를 txn_id 로 찾아 확인합니다(붙었으면 내부 취소 금지 — PG 환불 API 로만).
-- txn_id 에 인덱스가 없으면 주문이 쌓일수록 전체 훑기(풀스캔)가 되므로 미리 인덱스를 만듭니다.
-- ─────────────────────────────────────────────────────────────
CREATE INDEX ix_pg_txn ON pg_orders (txn_id);
-- ═══════════════════════════════════════════════════════════
-- V38__reconciliation.sql
-- ═══════════════════════════════════════════════════════════
-- ─────────────────────────────────────────────────────────────
-- V38: 금액 무결성 상시 검증(대사 · reconciliation) 인프라
--
-- 배경: 거래 시점의 복식분개(DR=CR) 코드 검증 + 원장 append-only 트리거는
-- "쓰기 시점"만 지킵니다. 이 마이그레이션은 "사후 상시 검증"을 위한 것으로,
-- 주기 워커(ReconcileWorker)가 아래를 대조해 어긋난 값을 integrity_findings 에 남깁니다:
-- ① 지갑 잔액 == 그 지갑 원장 순액(ΣCR-ΣDR) (비시스템 지갑)
-- ② 전역 ΣDR == ΣCR
-- ③ 거래별 ΣDR == ΣCR
-- ④ 수수료 거래별 fee_amount == FEE_REVENUE 기입액
-- 그리고 daily_summaries(일별 충전/출금/결제/수수료 합계)·daily_wallet_snapshots(잔액 스냅샷)를 채웁니다.
--
-- ★ 실행 대장은 V1 에 이미 있는 integrity_check_runs 를 그대로 씁니다
-- (integrity_findings.run_id 가 이 테이블을 FK 로 참조). 여기서는 "2대 동시 실행 방지"를 위해
-- (check_type, scope) 유니크만 추가합니다 — INSERT IGNORE 로 한 실행은 한 서버만 소유.
-- · check_type = 실행 종류(RECON=무결성 검사 / SUMMARY=일별 집계 / MANUAL=관리자 수동)
-- · scope = 중복 방지 키(RECON: 시간버킷 '2026-07-27T14' / SUMMARY: 일자 / MANUAL: uuid)
-- (MariaDB 10.3 엔 SKIP LOCKED 없음 — 다른 워커들과 같은 원자 클레임 방식)
-- ─────────────────────────────────────────────────────────────
-- 같은 (종류, 범위)로는 한 실행만 — INSERT IGNORE 선점의 근거.
CREATE UNIQUE INDEX `uq_check_scope` ON `integrity_check_runs` (`check_type`, `scope`);
-- 일별 집계·스냅샷을 "하루 한 벌"로 멱등하게 다시 채울 수 있도록 유니크 키 부여.
-- (워커가 같은 날 여러 번 돌아도 값이 통째로 갱신될 뿐 중복 행이 쌓이지 않게)
CREATE UNIQUE INDEX `uq_daily_summary` ON `daily_summaries` (`summary_date`, `metric`);
CREATE UNIQUE INDEX `uq_daily_wallet_snap` ON `daily_wallet_snapshots` (`snap_date`, `wallet_id`);
-- ═══════════════════════════════════════════════════════════
-- V39__deposit_requests.sql
-- ═══════════════════════════════════════════════════════════
-- V39: 충전신청(deposit_requests) — "무신청 충전 불가" 강제 테이블
--
-- 왜 필요한가:
-- 지금까지는 회원이 "회사 계좌로 아무 금액이나 보내면" 입금자 코드만 맞으면 그 금액이
-- 그대로 충전됐습니다(신청 개념 없음). 발주사 요구로 이를 바꿉니다.
-- · 회원은 충전 전에 "얼마를 충전할지" 먼저 신청(선언)해야 합니다.
-- · 신청할 때마다 회사 입금계좌를 "배정"하고(계좌는 매번 달라질 수 있다고 가정 — 스냅샷 보존),
-- 신청마다 "고유 입금코드"를 새로 발급합니다.
-- · 입금 통지가 오면 이 신청과 (고유코드 + 금액 정확일치)로 대조해,
-- "신청한 금액 그대로" 들어왔을 때만 충전을 확정합니다.
-- · 신청이 없거나(무신청)·금액이 다른 입금은 자동충전하지 않고 미매칭(unmatched_deposits)으로
-- 보내 관리자가 처리합니다.
-- · 단, 만료·취소된 신청이라도 "코드+금액이 일치하는 실제 입금"이 늦게 도착하면 여전히 충전합니다
-- (이미 들어온 돈은 환불이 어려우므로 — status<>MATCHED 이면 적립). 적립 시점에 보유·충전 한도를
-- 다시 검사해 한도를 넘으면 그때는 미매칭으로 보냅니다(법정 보유상한 초과 방지).
--
-- 스냅샷 원칙(거래 관련은 전부 스냅샷 — 정보 유실 방지):
-- 배정 계좌(은행·번호·예금주)는 신청 시점 값을 그대로 복사해 둡니다.
-- 나중에 관리자가 그 회사 계좌를 바꾸거나 삭제해도, 회원이 무엇을 보고 입금했는지가 남습니다.
CREATE TABLE deposit_requests (
id BIGINT NOT NULL AUTO_INCREMENT COMMENT '충전신청 고유번호',
user_id BIGINT NOT NULL COMMENT '신청 회원(users.id)',
card_id BIGINT NULL COMMENT '충전이 들어갈 대상 카드(선택 — 비어 있으면 확정 시 대표카드로)',
amount DECIMAL(15,0) NOT NULL COMMENT '신청(선언) 금액 — 이 금액 그대로 입금돼야만 충전 확정',
code VARCHAR(64) NOT NULL COMMENT '신청별 고유 입금코드(입금자명 칸에 적는 값 — 신청마다 새로 발급)',
deposit_account_id BIGINT NULL COMMENT '배정된 회사 입금계좌(deposit_accounts.id — 참조용. 실제 안내값은 아래 스냅샷 3열)',
account_bank VARCHAR(40) NOT NULL COMMENT '배정 계좌 은행 스냅샷(신청 시점 보존)',
account_no VARCHAR(40) NOT NULL COMMENT '배정 계좌번호 스냅샷',
account_holder VARCHAR(60) NOT NULL COMMENT '배정 계좌 예금주 스냅샷',
status VARCHAR(16) NOT NULL DEFAULT 'PENDING' COMMENT 'PENDING(입금대기)|MATCHED(충전완료)|EXPIRED(만료)|CANCELED(회원취소)',
expires_at DATETIME NOT NULL COMMENT '신청 만료 시각(만료되면 EXPIRED — 단 코드+금액 일치 늦은 입금은 여전히 충전, 환불 어려움)',
matched_notice_id BIGINT NULL COMMENT '이 신청과 매칭된 입금통지(deposit_notices.id)',
matched_txn_id BIGINT NULL COMMENT '충전 확정 거래(transactions.id)',
matched_at DATETIME NULL COMMENT '충전 확정 시각',
created_at DATETIME NOT NULL COMMENT '신청 시각',
PRIMARY KEY (id),
-- 고유 입금코드는 시스템 전체에서 유일 — 입금 통지가 이 코드로 신청을 정확히 1건 찾도록.
UNIQUE KEY ux_depreq_code (code),
-- 내 신청 목록(대기 우선) 조회용.
KEY ix_depreq_user (user_id, status, created_at),
-- 만료 스캔·상태별 관리자 조회용.
KEY ix_depreq_status (status, expires_at),
-- 신청 회원은 실제 회원이어야 함(회원 하드삭제는 없음 — 탈퇴는 상태 전이).
CONSTRAINT fk_depreq_user FOREIGN KEY (user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='충전신청 — 무신청 충전 불가 강제. 신청마다 계좌 배정(스냅샷)+고유코드, 금액 정확일치 시에만 확정';
-- ═══════════════════════════════════════════════════════════
-- V40__merchant_onboarding_extend.sql
-- ═══════════════════════════════════════════════════════════
-- V40: 매장 온보딩 보강 — 대표자 본인인증 / 가입채널 / 표준 필수서류(doc_type) / PG 신청 입력 / 정산계좌 등록주체
--
-- 왜 필요한가(발주 확정):
-- 1) 매장 가입 시 "대표자 본인인증"이 필요 → 회원가입처럼 이름·생년월일·성별·휴대폰·CI·DI 를 저장.
-- (기존엔 ceo_ci_hash 를 placeholder 로만 채웠음 — 실제 본인인증 결과로 대체하고 DI·생년·성별·휴대폰 보강)
-- 2) 가입 신청은 www·매장앱 둘 다 → 어디서 신청했는지(source) 기록.
-- 3) 기본 필수서류 6종(개인/법인 분기)을 "표준 분류(doc_type)"로 관리 → 신청 시 자동 생성, 법인 조건부 서류 강제.
-- (기존 merchant_documents.doc_name 자유입력만으로는 필수 세트를 강제할 수 없었음)
-- 4) PG 온라인 신청 입력값: 쇼핑몰 도메인(필수)·에스크로 확인증(옵션)·이행보증보험증권(옵션)을 저장.
-- 5) 정산계좌 등록 주체 구분: 매장 자가등록(실명+1원) vs 관리자 직접등록 — registrant_type 로 남김.
--
-- ※ merchants·bank_accounts·merchant_api_credentials 는 SYSTEM VERSIONED(이력 보존) 테이블이라
-- 컬럼 추가 시 아래 세션 설정이 없으면 MariaDB 가 ALTER 를 거부합니다(이력 컬럼 정합 유지).
SET @@system_versioning_alter_history = KEEP;
-- ── 1) merchants: 대표자 본인인증 6종 + 가입채널 ───────────────────────────────
ALTER TABLE `merchants`
ADD COLUMN `ceo_di_hash` char(64) NULL COMMENT '대표자 본인인증 DI(중복 대표자 판정용)' AFTER `ceo_ci_hash`,
ADD COLUMN `ceo_birth_date` char(10) NULL COMMENT '대표자 생년월일(yyyy-MM-dd) — 본인인증 결과' AFTER `ceo_di_hash`,
ADD COLUMN `ceo_gender` varchar(1) NULL COMMENT '대표자 성별 M|F — 본인인증 결과' AFTER `ceo_birth_date`,
ADD COLUMN `ceo_phone_enc` varbinary(256) NULL COMMENT '대표자 휴대폰 AES-256 암호문 — 본인인증 결과' AFTER `ceo_gender`,
ADD COLUMN `ceo_phone_hash` char(64) NULL COMMENT '대표자 휴대폰 SHA-256(검색용)' AFTER `ceo_phone_enc`,
ADD COLUMN `source` varchar(10) NULL COMMENT '가입 신청 채널 WWW|APP' AFTER `ceo_phone_hash`;
-- ── 2) merchant_documents: 표준 분류(doc_type) + 기본서류는 관리자 요구 없이 시스템 생성 가능 ──
ALTER TABLE `merchant_documents`
ADD COLUMN `doc_type` varchar(30) NULL COMMENT '표준 서류 분류 코드 — BIZ_CERT(사업자등록증)|CEO_ID(대표자 신분증)|SETTLE_BANKBOOK(정산통장 사본)|CEO_SEAL(대표자 인감증명서)|CORP_SEAL(법인인감증명서)|CORP_REGISTRY(법인 등기부등본)|SHAREHOLDERS(주주명부)|PG_ESCROW(에스크로 확인증)|PG_INSURANCE(이행보증보험증권)|ETC(관리자 추가). NULL=관리자 자유 추가(레거시)' AFTER `doc_name`;
-- 기본 필수서류는 "가입 시 시스템"이 만드므로 요구자(관리자)가 없을 수 있음 → NULL 허용으로 완화.
ALTER TABLE `merchant_documents` MODIFY COLUMN `requested_by` BIGINT NULL COMMENT '서류를 요구한 관리자 번호(NULL=가입 시 시스템 생성 기본 필수서류)';
-- ── 3) merchant_api_credentials: PG 온라인 신청 입력값 ──────────────────────────
ALTER TABLE `merchant_api_credentials`
ADD COLUMN `mall_domain` varchar(255) NULL COMMENT 'PG 신청: 쇼핑몰(웹사이트) 주소(도메인) — 필수' AFTER `webhook_url`,
ADD COLUMN `escrow_file_id` bigint NULL COMMENT 'PG 신청: 에스크로(구매안전서비스) 확인증 파일(옵션)' AFTER `mall_domain`,
ADD COLUMN `insurance_file_id` bigint NULL COMMENT 'PG 신청: 이행보증보험증권 파일(옵션)' AFTER `escrow_file_id`,
ADD CONSTRAINT `fk_pgcred_escrow` FOREIGN KEY (`escrow_file_id`) REFERENCES `files` (`id`),
ADD CONSTRAINT `fk_pgcred_insurance` FOREIGN KEY (`insurance_file_id`) REFERENCES `files` (`id`);
-- ── 4) bank_accounts: 정산계좌 등록 주체(경로 구분) ─────────────────────────────
ALTER TABLE `bank_accounts`
ADD COLUMN `registrant_type` varchar(10) NULL COMMENT '등록 주체 MERCHANT(매장 자가등록·실명+1원)|ADMIN(관리자 직접등록). NULL=레거시/회원계좌' AFTER `status`;
-- ═══════════════════════════════════════════════════════════
-- V41__pg_order_items.sql
-- ═══════════════════════════════════════════════════════════
-- V41: PG 온라인 결제 주문정보 보강 — 다품목(수량별) + 쇼핑몰 리턴정보
--
-- 왜 필요한가(발주 확정):
-- 1) 쇼핑몰 결제는 상품이 여러 개이고 상품별 수량이 달라 "묶음 결제"가 일반적인데,
-- 기존에는 대표 상품명(item_name) 한 줄만 받아 주문 내용을 온전히 남길 수 없었음.
-- → 품목 목록(이름·수량·단가)을 "주문 시점 스냅샷(JSON)"으로 통째로 보관.
-- (거래 관련 정보는 전부 스냅샷 원칙 — 쇼핑몰이 나중에 상품정보를 바꿔도 주문 기록은 불변)
-- 2) 쇼핑몰이 자기 주문코드 등 임의 정보를 실어 보내면, 결제 조회·웹훅 통지에 "그대로 되돌려줘야"
-- 쇼핑몰이 자기 주문과 손쉽게 대사(매칭)할 수 있음 → return_info(그대로 echo, 서버는 해석 안 함).
--
-- ※ pg_orders 는 BASE TABLE(이력 테이블 아님)이라 별도 세션 설정 없이 ALTER 가능합니다.
ALTER TABLE `pg_orders`
ADD COLUMN `items_json` TEXT NULL COMMENT '품목 스냅샷 JSON 배열 [{name,qty,unitPrice,amount}] — 다품목·수량 주문의 원본 보존(NULL=단일 상품명만 받은 주문)' AFTER `item_name`,
ADD COLUMN `return_info` varchar(500) NULL COMMENT '쇼핑몰이 보낸 임의 리턴정보(주문코드 등) — 조회·웹훅에 그대로 되돌려줌(서버는 내용 해석 안 함)' AFTER `items_json`;
-- ═══════════════════════════════════════════════════════════
-- V42__seed_rejoin_wait_days.sql
-- ═══════════════════════════════════════════════════════════
-- V42: 재가입 대기일(REJOIN_WAIT_DAYS) 설정 시드 — 누락으로 인한 재가입 500 오류 수정 (감사 CRITICAL #1)
--
-- [문제] global_settings 에 REJOIN_WAIT_DAYS 행이 없어서, 회원 조회 쿼리(selectByCi)의
-- 서브쿼리 (SELECT ... WHERE skey='REJOIN_WAIT_DAYS') 가 NULL 을 돌려주고,
-- 그 결과 in_rejoin_wait 판정이 NULL → 서비스에서 .intValue() 호출 시 NullPointerException(500).
-- → 탈퇴 회원이 재가입을 시도하면 자격확인 단계에서 서버 오류가 났습니다.
--
-- [값] "재가입 대기일"은 요구사항·문서 어디에도 구체 일수가 정해져 있지 않고, 관리자 정책 화면의
-- "기타 운영 값"으로 조정하는 항목입니다. 그래서 여기서는 임의 정책값을 만들지 않고, 크래시가 나지
-- 않는 안전 기본값 0(대기 없음)으로 행만 만들어 둡니다. 실제 대기일은 발주사/관리자가 정해
-- 관리자 화면에서 이 값을 바꾸면 즉시 반영됩니다.
--
-- ON DUPLICATE KEY UPDATE svalue=svalue : 이미 값이 있으면 건드리지 않습니다(안전 재실행).
INSERT INTO global_settings (skey, svalue, updated_at) VALUES
('REJOIN_WAIT_DAYS', '0', NOW())
ON DUPLICATE KEY UPDATE svalue = svalue;
-- ═══════════════════════════════════════════════════════════
-- V43__pg_order_status_default.sql
-- ═══════════════════════════════════════════════════════════
-- V43: pg_orders.status 기본값·주석을 실제 코드 체계(PENDING 시작)로 정정 (감사 #14)
--
-- [문제] 스키마 정본(dbml)·DB 컬럼은 status 기본값을 'CREATED', 상태집합을
-- 'CREATED|PAID|CANCELED|EXPIRED' 로 적어 두었지만, 실제 코드는 'CREATED' 를 전혀 쓰지 않고
-- 주문 생성 시 'PENDING' 으로 넣으며, 전 생명주기가 PENDING → PAID/EXPIRED/CANCELED 로 동작합니다.
-- (insertOrder 가 항상 'PENDING' 을 명시 삽입하므로 기본값은 실제로 쓰이진 않지만, 문서·DB·코드
-- 정합을 위해 기본값과 주석을 실제 체계로 맞춥니다.)
--
-- ※ pg_orders 는 BASE TABLE(이력 테이블 아님)이라 세션 설정 없이 ALTER 가능합니다.
-- 기존 행 값은 바꾸지 않습니다(이미 PENDING/PAID/EXPIRED/CANCELED 로만 존재).
ALTER TABLE `pg_orders`
MODIFY COLUMN `status` varchar(20) NOT NULL DEFAULT 'PENDING'
COMMENT 'PENDING|PAID|CANCELED|EXPIRED (주문 생성 시 PENDING)';
-- ═══════════════════════════════════════════════════════════
-- V44__deposit_notice_attempts.sql
-- ═══════════════════════════════════════════════════════════
-- V44: 입금통지 처리 시도 횟수(attempts) 추가 — 독성 통지 무한 재시도 격리용 (감사 #7)
--
-- [문제] 입금통지 워커가 통지 처리 중 예외가 나면 catch 가 비어 있어(조용히 삼킴) 그 통지는
-- NEW 로 남아 10초마다 영원히 재시도됐고, 운영자가 알 방법도 없었습니다.
-- [해결] 실패할 때마다 attempts 를 올리고, 한계(코드 MAX_ATTEMPTS)에 이르면 status 를 'FAILED' 로
-- 바꿔 재시도를 멈춥니다(격리). 동시에 오류 장부(app_error_logs)에 남겨 운영자가 확인합니다.
--
-- ※ deposit_notices 는 BASE TABLE(이력 테이블 아님)이라 세션 설정 없이 ALTER 가능합니다.
ALTER TABLE `deposit_notices`
ADD COLUMN `attempts` int NOT NULL DEFAULT 0
COMMENT '처리 시도 횟수 - 한계 도달 시 status=FAILED 로 격리(무한 재시도 방지, V44)' AFTER `status`;
-- ═══════════════════════════════════════════════════════════
-- V45__index_cleanup.sql
-- ═══════════════════════════════════════════════════════════
-- V45: 중복 인덱스 제거 + 대시보드 24시간 오류집계 인덱스 추가 (감사 MEDIUM)
--
-- [문제 1] V38 이 V1 baseline 에 이미 있던 UNIQUE 인덱스와 "완전히 동일한" UNIQUE 인덱스를 다시 만들어,
-- 같은 컬럼 조합의 중복 UNIQUE 인덱스가 실 DB 에 2쌍 존재합니다(쓰기마다 두 번 갱신 = 낭비).
-- · daily_summaries : uq_daily_summary ≡ ux_summary_day_metric (summary_date, metric)
-- · daily_wallet_snapshots : uq_daily_wallet_snap ≡ ux_snap_day_wallet (snap_date, wallet_id)
-- → V38 이 추가한 uq_* 쪽을 제거합니다. 남는 ux_* 도 같은 UNIQUE 라 ON DUPLICATE KEY 갱신은 그대로 동작.
--
-- [문제 2] 관리자 대시보드의 "최근 24시간 외부 API 오류 수"(http_status>=400 AND created_at>=NOW()-1일)가
-- 쓸 인덱스가 없어 파티션 스캔이 됩니다. (http_status, created_at) 인덱스를 추가해 범위 검색이 되게 합니다.
--
-- ※ 인덱스 추가/삭제는 시스템 버저닝 세션 플래그가 필요 없습니다(컬럼 변경이 아니라 인덱스 연산).
DROP INDEX `uq_daily_summary` ON `daily_summaries`;
DROP INDEX `uq_daily_wallet_snap` ON `daily_wallet_snapshots`;
CREATE INDEX `ix_extapi_status_time` ON `external_api_logs` (`http_status`, `created_at`);
-- ═══════════════════════════════════════════════════════════
-- V46__drop_dead_summary_tables.sql
-- ═══════════════════════════════════════════════════════════
-- V46: 사용되지 않는(죽은) 집계 테이블 3종 제거 (감사 MEDIUM, 사용자 승인)
--
-- [근거] 아래 3개는 적재/조회 코드가 전무하고(정확 참조 0), 실데이터도 0행이며,
-- 실제로 쓰이는 활성 테이블로 대체돼 있습니다:
-- · monthly_statements → merchant_monthly_statements (정산 마감본, AdminSettlement 사용) 로 대체
-- · merchant_daily_summaries → daily_summaries (일 집계, Reconcile 사용) 로 대체
-- · hourly_summaries → 사용처 없음(시간 집계 미구현)
-- 문서에는 이름만 남아 오해를 주므로 정리합니다. (되돌리려면 새 마이그레이션으로 재생성)
--
-- ※ 모두 BASE TABLE(이력 테이블 아님)이라 세션 설정 없이 DROP 가능. IF EXISTS 로 안전 재실행.
DROP TABLE IF EXISTS `monthly_statements`;
DROP TABLE IF EXISTS `merchant_daily_summaries`;
DROP TABLE IF EXISTS `hourly_summaries`;
-- ═══════════════════════════════════════════════════════════
-- V47__deposit_foreign_keys.sql
-- ═══════════════════════════════════════════════════════════
-- V47: 충전 매칭 경로 참조 컬럼에 외래키(FK) 추가 — 앱 검증에만 의존하던 정합성을 DB 로 보강 (감사 MEDIUM)
--
-- [근거] deposit_requests.card_id/matched_notice_id/matched_txn_id 와 deposit_notices.matched_txn_id 는
-- 각각 cards·deposit_notices·transactions 의 행을 가리키는데 FK 가 없어, 앱 코드 검증에만 의존했습니다.
-- 고아행 검사 결과 0건이라 FK 를 안전하게 걸 수 있습니다(모두 NULL 허용 — 매칭 전에는 NULL).
-- ※ card_id→cards(SYSTEM VERSIONED) 는 기존 user_id→users(versioned) FK 와 같은 방식이라 문제 없습니다.
-- append-only 테이블(참조 대상)은 삭제되지 않으므로 기본 RESTRICT 동작으로 충분합니다.
ALTER TABLE `deposit_requests`
ADD CONSTRAINT `fk_depreq_card` FOREIGN KEY (`card_id`) REFERENCES `cards`(`id`),
ADD CONSTRAINT `fk_depreq_notice` FOREIGN KEY (`matched_notice_id`) REFERENCES `deposit_notices`(`id`),
ADD CONSTRAINT `fk_depreq_txn` FOREIGN KEY (`matched_txn_id`) REFERENCES `transactions`(`id`);
ALTER TABLE `deposit_notices`
ADD CONSTRAINT `fk_depnotice_txn` FOREIGN KEY (`matched_txn_id`) REFERENCES `transactions`(`id`);
-- ═══════════════════════════════════════════════════════════
-- V48__pii_access_member_actor.sql
-- ═══════════════════════════════════════════════════════════
-- V48: 개인정보 열람기록(pii_access_logs)에 "회원 본인 열람"도 남길 수 있게 확장 (감사 MEDIUM)
--
-- [문제] 회원이 자기 카드의 전체번호·CVC 를 열람(reveal)할 때 접근기록이 전혀 남지 않았습니다.
-- 테이블이 admin_id NOT NULL(관리자 전용)이라 회원 행위자를 기록할 수 없었기 때문입니다.
-- → admin_id 를 NULL 허용으로 바꾸고(=행위자 id, 관리자 또는 회원), actor_type 으로 누구인지 구분합니다.
-- 기존 관리자 기록은 actor_type 기본값 'ADMIN' 이 붙어 그대로 유지됩니다.
ALTER TABLE `pii_access_logs`
MODIFY COLUMN `admin_id` bigint(20) NULL COMMENT '행위자 id (관리자 또는 회원 - actor_type 로 구분)',
ADD COLUMN `actor_type` varchar(10) NOT NULL DEFAULT 'ADMIN'
COMMENT 'ADMIN|MEMBER - 개인정보를 누가 열람했는지' AFTER `admin_id`;
-- ═══════════════════════════════════════════════════════════
-- V49__restore_planned_summary_tables.sql
-- ═══════════════════════════════════════════════════════════
-- V49: V46 에서 과도하게 드롭한 "설계됨·구현 대기" 집계 테이블 2종 복구 (감사 정정)
--
-- [배경] V46 은 사용되지 않는 집계 테이블 3종을 지웠는데, 그중 2개는 사실 이전 감사에서 "의도적으로
-- 설계한 차트 집계 계층"이었습니다(미구현일 뿐 죽은 게 아님). 성급한 제거였으므로 되돌립니다.
-- · merchant_daily_summaries : 매장앱 일/주/월 매출 차트의 집계 원천(감사 신설, 구현 대기)
-- · hourly_summaries : admin 전역 당일 시간대 추이(감사 신설, 구현 대기)
-- 반면 monthly_statements 는 회원 월명세를 "실시간 계산"(AppStatementService)으로 대체해 진짜로
-- 쓰이지 않으므로 그대로 제거 상태를 유지합니다. (V1 원본 DDL·인덱스·FK 그대로 복구)
CREATE TABLE `merchant_daily_summaries` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`merchant_id` bigint NOT NULL,
`summary_date` date NOT NULL,
`sales_count` int NOT NULL DEFAULT 0,
`sales_sum` decimal(15,0) NOT NULL DEFAULT 0,
`cancel_count` int NOT NULL DEFAULT 0,
`cancel_sum` decimal(15,0) NOT NULL DEFAULT 0,
`supply_sum` decimal(15,0) NOT NULL DEFAULT 0,
`vat_sum` decimal(15,0) NOT NULL DEFAULT 0,
`fee_sum` decimal(15,0) NOT NULL DEFAULT 0
);
CREATE UNIQUE INDEX `ux_mds_merchant_day` ON `merchant_daily_summaries` (`merchant_id`, `summary_date`);
ALTER TABLE `merchant_daily_summaries` ADD FOREIGN KEY (`merchant_id`) REFERENCES `merchants` (`id`);
CREATE TABLE `hourly_summaries` (
`id` bigint PRIMARY KEY AUTO_INCREMENT,
`stat_hour` datetime NOT NULL COMMENT '정시 절단(KST)',
`metric` varchar(40) NOT NULL,
`value` decimal(18,0) NOT NULL
);
CREATE UNIQUE INDEX `ux_hourly` ON `hourly_summaries` (`stat_hour`, `metric`);
-- ═══════════════════════════════════════════════════════════
-- V50__vendor_requests.sql
-- ═══════════════════════════════════════════════════════════
-- ─────────────────────────────────────────────────────────────
-- V50 : 발주사(네스트페이) 외부 API 요청 대장(vendor_requests)
--
-- 왜 필요한가요?
-- 발주사는 "거래고유번호(trackId)"를 한 번 쓰면 다시 못 쓰게 막습니다(재사용 시 PVAC.3500).
-- 그래서 우리가 보낸 번호를 반드시 우리 쪽에도 남겨 두어야 합니다. 남겨 두면:
-- · 같은 번호를 실수로 두 번 보내는 일을 막을 수 있고(UNIQUE),
-- · 답을 못 받았을 때(시간초과) "이 요청이 실제로 나갔었는지" 확인할 수 있으며,
-- · 1원인증처럼 2단계로 나뉜 절차에서 1단계 결과(검증 거래일자·거래번호)를
-- 2단계까지 안전하게 이어 줄 수 있습니다.
--
-- 지금까지는 이런 자리가 아예 없어서, 1원인증을 우리 서버가 스스로 판정하는
-- 임시 방식(무상태 토큰)으로 만들어 두었습니다. 실제 연동에서는 발주사가 판정하므로
-- 이 대장이 있어야 합니다.
--
-- 보안: 계좌번호·전화번호 같은 개인정보 원문은 여기 넣지 않습니다(지문/가린 값만).
-- ─────────────────────────────────────────────────────────────
CREATE TABLE vendor_requests (
id BIGINT NOT NULL AUTO_INCREMENT COMMENT '내부 일련번호',
track_id VARCHAR(50) NOT NULL COMMENT '우리가 발주사에 보낸 거래고유번호(재사용 금지)',
operation VARCHAR(50) NOT NULL COMMENT '요청 종류 (예: phone/send, account/one-req)',
subject_type VARCHAR(20) NOT NULL COMMENT '누구를 위한 요청인지 (USER=회원 · MERCHANT=매장 · GUEST=가입전)',
subject_id BIGINT NULL COMMENT '회원/매장 번호 (가입 전에는 아직 없어 NULL)',
-- 발주사가 돌려준 값들 (2단계 절차에서 다음 단계에 그대로 필요합니다)
vendor_trx_id VARCHAR(50) NULL COMMENT '발주사 거래고유번호(trxId)',
vendor_seq_no VARCHAR(50) NULL COMMENT '인증 거래번호(TX_SEQ_NO) — 재전송·확인 단계에서 사용',
verify_tr_dt VARCHAR(8) NULL COMMENT '1원인증 검증 거래일자(8자리) — 확인 단계 필수 보관',
verify_tr_no VARCHAR(20) NULL COMMENT '1원인증 검증 거래번호 — 확인 단계 필수 보관',
verify_txt VARCHAR(20) NULL COMMENT '통장 적요에 찍히는 한글 단어(사용자 안내용)',
-- 진행 상태
status VARCHAR(20) NOT NULL DEFAULT 'REQUESTED'
COMMENT 'REQUESTED=요청함 | SUCCEEDED=성공 | FAILED=실패 | UNKNOWN=결과확인불가(사람 확인 필요)',
result_code VARCHAR(30) NULL COMMENT '발주사 결과코드 (예: A000, DV50, PVAC.3500)',
result_message VARCHAR(200) NULL COMMENT '발주사 결과 메시지(개인정보 제외)',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '요청 시각',
updated_at DATETIME NULL COMMENT '결과 반영 시각',
PRIMARY KEY (id),
-- 같은 거래고유번호를 두 번 만들지 못하게 DB 가 직접 막습니다(발주사 PVAC.3500 예방).
UNIQUE KEY ux_vendor_track (track_id),
-- "이 회원의 이 종류 요청"을 최근 순으로 찾을 때 씁니다.
KEY ix_vendor_subject (subject_type, subject_id, operation, created_at),
-- 사람 확인이 필요한 미결 건을 관리자가 모아 볼 때 씁니다.
KEY ix_vendor_status (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='발주사 외부 API 요청 대장 — 거래고유번호 재사용 방지 + 2단계 절차 이어주기';
-- ═══════════════════════════════════════════════════════════
-- V51__vendor_identity_result.sql
-- ═══════════════════════════════════════════════════════════
-- ─────────────────────────────────────────────────────────────
-- V51 : 본인인증 결과를 잠시 보관할 자리(vendor_requests.result_enc)
--
-- 왜 필요한가요?
-- 발주사 휴대폰 본인인증은 "인증코드 확인"에 성공해도, 정작 사람을 식별하는 값
-- (이름·생년월일·통신사·성별·내외국인·CI)은 그 응답에 들어 있지 않습니다.
-- 발주사가 우리 서버의 "알림 주소(returnUrl)"로 따로 보내 줍니다.
--
-- 즉 아래처럼 두 갈래로 나뉘어 도착합니다.
-- (앱) 확인 요청 → "확인됐다"는 응답만 받음
-- (발주사) 알림 주소로 → 진짜 인증 결과(CI 포함) 전송
--
-- 서버가 2대라 메모리에 들고 있을 수 없으므로, 알림으로 받은 결과를 잠깐 DB 에 둡니다.
-- 그래야 앱이 다시 물어볼 때 어느 서버가 받든 같은 결과를 돌려줄 수 있습니다.
--
-- 보안: 개인정보이므로 원문 그대로 넣지 않고, 우리 서버 열쇠로 잠근 값(AES-GCM)만 넣습니다.
-- 그리고 회원가입/매장신청에 쓰이고 나면 지워, 필요한 동안만 남게 합니다.
-- ─────────────────────────────────────────────────────────────
ALTER TABLE vendor_requests
ADD COLUMN result_enc VARBINARY(1024) NULL
COMMENT '본인인증 결과를 우리 열쇠로 잠근 값(이름·생년월일·성별·통신사·내외국인·CI). 사용 후 삭제'
AFTER verify_txt,
ADD COLUMN result_at DATETIME NULL
COMMENT '알림으로 결과가 도착한 시각'
AFTER result_enc;
-- ═══════════════════════════════════════════════════════════
-- V52__identity_rate_limits.sql
-- ═══════════════════════════════════════════════════════════
-- ─────────────────────────────────────────────────────────────
-- V52 : 본인인증 호출 횟수 제한 규칙 추가
--
-- 왜 필요한가요?
-- 본인인증 시작 주소는 로그인 없이 누구나 부를 수 있는 공개 주소입니다.
-- 그런데 지금까지 횟수 제한이 하나도 걸려 있지 않았습니다. 그대로 두면
-- · 인증문자가 무한정 나가 비용이 발생하고,
-- · 인증사의 일일 한도(테스트 기준 10회)를 금방 소진해 정상 사용자가 가입을 못 하며,
-- · 남의 번호로 문자를 계속 보내는 괴롭힘에 쓰일 수 있습니다.
--
-- 또 인증번호 확인도 제한이 없으면 6자리 숫자를 계속 찍어 맞힐 수 있습니다.
--
-- 기준(basis)=IP : 로그인 전 단계라 회원 번호가 없으므로 접속 IP 로 셉니다.
-- ─────────────────────────────────────────────────────────────
INSERT INTO rate_limit_rules (action_key, description, window_sec, max_count, basis, enabled, updated_at)
VALUES
('identity.start', '본인인증 문자 발송 (IP/시간)', 3600, 10, 'IP', 1, NOW()),
('identity.confirm', '본인인증 번호 확인 (IP/10분)', 600, 10, 'IP', 1, NOW())
ON DUPLICATE KEY UPDATE
description = VALUES(description),
window_sec = VALUES(window_sec),
max_count = VALUES(max_count),
basis = VALUES(basis),
updated_at = NOW();
-- ═══════════════════════════════════════════════════════════
-- V53__nestpay_openapi_settings.sql
-- ═══════════════════════════════════════════════════════════
-- ─────────────────────────────────────────────────────────────
-- V53 : 네스트페이 오픈API(발주사 제공) 연동 설정을 관리자에서 바꿀 수 있게 자리 만들기
--
-- 왜 필요한가요?
-- 지금까지 이 값들은 서버 환경변수로만 넣을 수 있었습니다. 그러면 값을 바꿀 때마다
-- 서버에 접속해 환경변수를 고치고 다시 띄워야 해서, 운영자가 스스로 바꿀 수 없습니다.
-- 그래서 운영설정표(global_settings)에 자리를 만들어 관리자 화면에서 바꾸도록 합니다.
--
-- 비밀값(열쇠 2개)은 어떻게 지키나요?
-- · svalue 에 "ENC1." 표식 + 우리 열쇠로 잠근 값을 넣습니다(평문으로 두지 않습니다).
-- · 관리자 화면에서도 다시 보여 주지 않습니다(입력만 가능, 조회 불가).
-- ※ 우리 마스터 열쇠(APP_CRYPTO_KEY) 자체는 여기 넣지 않습니다.
-- DB 를 지키는 열쇠를 그 DB 안에 두면, DB 하나만 새어도 열쇠와 데이터가 함께 새기 때문입니다.
--
-- 값이 비어 있으면?
-- 서버는 환경변수(NESTPAY_OPENAPI_*) 값을 대신 씁니다. 둘 다 없으면 연동이 꺼진 상태로
-- 개발용 대역(스텁)이 동작합니다.
-- ─────────────────────────────────────────────────────────────
INSERT INTO global_settings (skey, svalue, updated_by, updated_at) VALUES
('NPOPEN_BASE_URL', '', NULL, NOW()),
('NPOPEN_PAY_KEY', '', NULL, NOW()),
('NPOPEN_SECRET_KEY', '', NULL, NOW()),
('NPOPEN_IDENTITY_RETURN_URL', '', NULL, NOW()),
('NPOPEN_DEPOSIT_BANK', '', NULL, NOW()),
('NPOPEN_DEPOSIT_ACCT', '', NULL, NOW()),
-- 예치금 잔액이 이 금액 아래로 내려가면 관리자에게 알립니다(0 이면 알리지 않음).
-- 예치금이 바닥나면 회원 출금·매장 정산이 전부 실패하므로 미리 채워 넣어야 합니다.
('NPOPEN_DEPOSIT_ALERT_MIN', '0', NULL, NOW())
ON DUPLICATE KEY UPDATE updated_at = updated_at;
-- ═══════════════════════════════════════════════════════════
-- V54__cash_receipts.sql
-- ═══════════════════════════════════════════════════════════
-- ─────────────────────────────────────────────────────────────
-- V54 : 현금영수증 발행 대장
--
-- 무엇을 담나요?
-- 매장이 "이 결제 건으로 현금영수증을 발행했다"는 사실과, 발주사가 돌려준
-- 승인번호·거래번호를 담습니다. 국세청에 올라가는 세금 자료라 함부로 지울 수 없고,
-- 취소도 "취소했다"는 기록으로 남겨야 합니다.
--
-- 왜 결제 표(transactions)에 칸을 더하지 않았나요?
-- · 현금영수증은 발행 → 취소 → 재발행처럼 결제와 다른 생애주기를 가집니다.
-- · 발주사 거래번호·승인번호처럼 결제와 무관한 값이 많습니다.
-- · 결제 표는 이미 넓어서, 세금 자료까지 섞으면 나중에 손대기 어려워집니다.
-- ─────────────────────────────────────────────────────────────
CREATE TABLE cash_receipts (
id BIGINT NOT NULL AUTO_INCREMENT,
txn_id BIGINT NOT NULL COMMENT '대상 결제 거래 번호(transactions.id)',
merchant_id BIGINT NOT NULL COMMENT '발행한 매장 번호 - 남의 매장 건을 발행하지 못하게 대조용',
-- ── 발주사와 주고받는 번호들 ────────────────────────────────
track_id VARCHAR(50) NOT NULL COMMENT '발행 요청 시 우리가 만든 거래고유번호(재사용 금지)',
cancel_track_id VARCHAR(50) NULL COMMENT '취소 요청 시 우리가 만든 거래고유번호(발행과 달라야 함)',
vendor_trx_id VARCHAR(50) NULL COMMENT '발주사 거래고유번호(trxId) - 취소할 때 rootTrxId 로 넣습니다',
auth_code VARCHAR(50) NULL COMMENT '발급기관 승인번호(authCd) - 고객에게 보여 주는 값',
-- ── 발행 내용(발주사에 보낸 값 그대로 스냅샷) ────────────────
-- 나중에 매장 정보나 정책이 바뀌어도 "그때 이렇게 발행했다"가 남아야 합니다.
auth_type VARCHAR(2) NOT NULL COMMENT '인증구분 03=개인(휴대폰) · 04=법인(사업자번호)',
identity_enc VARBINARY(255) NOT NULL COMMENT '인증값(휴대폰번호/사업자번호) - 개인정보라 암호화 보관',
identity_hash CHAR(64) NULL COMMENT '인증값 SHA-256 - 원문 없이 같은 값을 찾기 위한 검색용',
usage_type VARCHAR(20) NOT NULL COMMENT '발행용도 소득공제용 | 지출증빙용 | 자진발급',
cust_name VARCHAR(100) NOT NULL COMMENT '고객명(발주사 필수 항목)',
product_name VARCHAR(200) NOT NULL COMMENT '상품명(pdtName)',
amount DECIMAL(15,0) NOT NULL COMMENT '총 결제금액 - 취소할 때도 이 금액을 그대로 보냅니다',
supply_amount DECIMAL(15,0) NULL COMMENT '공급가액(발주사 회신값)',
vat_amount DECIMAL(15,0) NULL COMMENT '부가세(발주사 회신값). 면세 매장이면 0 이거나 비어 있습니다',
tax_type VARCHAR(20) NULL COMMENT '과세구분(발주사 회신값) 예: 과세 · 면세',
-- ── 진행 상태 ───────────────────────────────────────────────
status VARCHAR(20) NOT NULL DEFAULT 'UNKNOWN'
COMMENT 'UNKNOWN=결과 모름 · ISSUED=발행완료 · FAILED=발행실패 · CANCELED=취소완료 · CANCEL_FAILED=취소실패',
result_code VARCHAR(30) NULL COMMENT '발주사 결과코드(예: 0000)',
result_message VARCHAR(200) NULL COMMENT '발주사 결과 메시지',
issued_by BIGINT NULL COMMENT '발행을 누른 매장 계정 번호(현재는 매장 번호와 같음). 자동 취소 건은 비어 있습니다',
issued_at DATETIME NULL COMMENT '발행 완료 시각',
canceled_at DATETIME NULL COMMENT '취소 완료 시각',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-- ★ 이중 발행을 DB 가 직접 막기 위한 계산 칸입니다.
-- "지금 살아 있는(=국세청에 올라가 있을 수 있는) 건"만 txn_id 값을 갖고, 나머지는 비어(NULL) 있습니다.
-- MariaDB 는 유니크 인덱스에서 NULL 을 서로 다른 값으로 보므로,
-- "죽은 건은 얼마든지 쌓이되, 살아 있는 건은 결제당 하나"가 자동으로 지켜집니다.
-- (코드에서만 검사하면 서버 2대가 동시에 누를 때 뚫립니다)
--
-- 어떤 상태를 "살아 있다"고 보나요? — 판단 기준은 "다시 발행해도 안전한가"입니다.
-- · UNKNOWN : 결과를 모름 → 막습니다. 이미 발행됐을 수 있어 재발행하면 이중 발행입니다.
-- (보내는 도중 서버가 멈췄거나, 보냈는데 답을 못 받은 경우 모두 여기입니다)
-- · ISSUED : 발행됨 → 막습니다.
-- · CANCEL_FAILED : 취소 실패 → 막습니다. 아직 발행된 상태로 남아 있습니다.
-- · FAILED : 발행 실패 → 허용합니다. 발행되지 않았음이 확실하므로 다시 시도할 수 있어야 합니다.
-- · CANCELED : 취소 완료 → 허용합니다. 다시 발행할 수 있습니다.
alive_key BIGINT AS (CASE WHEN status IN ('UNKNOWN','ISSUED','CANCEL_FAILED')
THEN txn_id ELSE NULL END) VIRTUAL
COMMENT '재발행하면 안 되는 건만 txn_id 를 갖는 계산 칸 - 이중 발행 차단용',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='현금영수증 발행 대장 - 국세청 신고 자료의 근거';
-- 발주사에 보낸 거래고유번호는 겹치면 안 됩니다(겹치면 발주사가 거부합니다).
CREATE UNIQUE INDEX ux_cashrcpt_track ON cash_receipts (track_id);
-- 한 결제당 살아 있는 현금영수증은 하나만.
CREATE UNIQUE INDEX ux_cashrcpt_alive ON cash_receipts (alive_key);
-- 매장앱 목록(매장별), 회원앱 표시(결제건별), 관리자 실패건 조회(상태별)에 씁니다.
CREATE INDEX ix_cashrcpt_merchant ON cash_receipts (merchant_id, created_at);
CREATE INDEX ix_cashrcpt_txn ON cash_receipts (txn_id);
CREATE INDEX ix_cashrcpt_status ON cash_receipts (status, created_at);
-- ═══════════════════════════════════════════════════════════
-- V55__cash_receipt_rate_limits.sql
-- ═══════════════════════════════════════════════════════════
-- ─────────────────────────────────────────────────────────────
-- V55 : 현금영수증 발행·취소 호출 횟수 제한 규칙 추가
--
-- 왜 필요한가요?
-- 현금영수증 발행은 발주사(외부기관)를 부르는 요청입니다. 같은 결제로 두 번 발행되는 것은
-- DB 유니크 인덱스가 막아 주지만, <b>발행에 실패한 건은 다시 시도할 수 있게</b> 해 두었습니다
-- (발주사 사정으로 실패했을 때 매장이 다시 발행할 수 있어야 하기 때문입니다).
--
-- 그래서 실패가 반복되는 상황에서는 매장이 발주사를 <b>무제한으로 부를 수 있습니다.</b>
-- 버튼을 계속 누르거나 잘못 만든 프로그램이 반복 호출하면
-- · 발주사 쪽 한도를 소진해 다른 매장까지 발행이 막히고,
-- · 우리 서버는 매번 응답을 30초까지 기다려 느려집니다.
--
-- 취소도 같은 이유로 제한합니다.
--
-- 기준(basis)=USER : 매장 로그인 뒤에만 부를 수 있으므로 매장 번호로 셉니다.
-- (다른 매장 기준 규칙 settle.create 와 같은 방식입니다)
--
-- 한도를 정한 근거
-- 현금영수증은 손님 한 명당 한 번, 결제당 한 장입니다. 아주 바쁜 매장이라도
-- 1분에 10건을 넘기기 어렵습니다. 정상 사용은 막지 않으면서 폭주만 끊는 값입니다.
-- ─────────────────────────────────────────────────────────────
INSERT INTO rate_limit_rules (action_key, description, window_sec, max_count, basis, enabled, updated_at)
VALUES
('cash.receipt.issue', '현금영수증 발행 (매장/분)', 60, 10, 'USER', 1, NOW()),
('cash.receipt.cancel', '현금영수증 취소 (매장/분)', 60, 10, 'USER', 1, NOW())
ON DUPLICATE KEY UPDATE
description = VALUES(description),
window_sec = VALUES(window_sec),
max_count = VALUES(max_count),
basis = VALUES(basis),
updated_at = NOW();
-- ═══════════════════════════════════════════════════════════
-- V56__drop_unused_tables.sql
-- ═══════════════════════════════════════════════════════════
-- ─────────────────────────────────────────────────────────────
-- V56 : 쓰지 않는 표 3개 정리
--
-- 왜 지우나요?
-- 설계만 해 두고 <b>아무 코드도 쓰지 않는</b> 표입니다. 그대로 두면
-- · 나중에 보는 사람이 "이건 뭐지? 채워야 하나?" 하고 헤매고,
-- · 스키마 문서·백업·마이그레이션에 계속 따라다니며,
-- · 실제로는 비어 있어 통계나 조회에 아무 도움이 되지 않습니다.
--
-- 지우기 전에 전부 확인했습니다 — 매퍼 SQL·자바 코드·프로시저·이벤트에서 참조 0건.
--
-- ① deposit_identifiers : 옛 방식의 "고정 입금자코드" 표입니다.
-- 지금은 충전 신청마다 계좌를 새로 배정하는 방식으로 바뀌었고
-- (deposit_requests.deposit_account_id · account_no), 이 표는 쓰이지 않습니다.
-- 남아 있던 2행은 방식 전환 전의 잔재입니다.
--
-- ② hourly_summaries : 시간별 통계 — 만들어만 두고 채우는 코드가 없습니다.
-- ③ merchant_daily_summaries: 매장 일별 통계 — 위와 같습니다.
-- 실제로 도는 통계는 daily_summaries 하나이며, 필요해지면 그때 다시 만듭니다.
-- (빈 표를 미리 두는 것보다, 필요할 때 요구사항에 맞게 만드는 편이 낫습니다)
--
-- ★ 되돌리려면: 이 표들의 정의는 V1__baseline.sql 에 그대로 남아 있습니다.
-- ─────────────────────────────────────────────────────────────
DROP TABLE IF EXISTS deposit_identifiers;
DROP TABLE IF EXISTS hourly_summaries;
DROP TABLE IF EXISTS merchant_daily_summaries;
-- ═══════════════════════════════════════════════════════════
-- V57__close_orphan_gift_hold.sql
-- ═══════════════════════════════════════════════════════════
-- ─────────────────────────────────────────────────────────────
-- V57 : 끝났는데 "대기중"으로 남아 있는 선물 거래 정리
--
-- 무슨 문제였나요?
-- 링크 선물은 보낼 때 돈을 보류 지갑(GIFT_ESCROW)에 맡겨 두고
-- 거래를 HOLD(대기중) 상태로 남깁니다.
-- 그런데 수령·회수·만료로 상황이 끝나도 이 HOLD 를 바꿔 주는 코드가
-- 없었습니다(AppGiftMapper 에 transactions 를 고치는 문장이 0개였습니다).
--
-- 그 결과 돈은 이미 정리됐는데 회원 이용내역에는
-- "1,000원 선물 대기중" (끝난 거래)
-- "1,000원 반환됨" (되돌아온 거래)
-- 이 둘 다 보이는 상태가 됐습니다.
--
-- ★ 돈 계산은 틀리지 않았습니다.
-- 한도·월명세 합계는 모두 status='CONFIRMED' 만 세기 때문에
-- 이 HOLD 행은 어떤 금액 집계에도 들어가지 않았습니다.
-- 화면 표시만의 문제였습니다.
--
-- 어떻게 고치나요?
-- ① 코드 : 수령하면 CONFIRMED, 회수·만료면 CANCELED 로 끝맺도록 고쳤습니다
-- (AppGiftService.claim / returnToSender → giftMapper.closeHoldTxn)
-- ② 이 파일 : 고치기 전에 이미 쌓여 있던 행들을 같은 규칙으로 맞춰 줍니다.
--
-- 왜 CANCELED 인가요?
-- 결제를 취소했을 때도 CANCELED 를 씁니다. 되돌아왔다는 뜻이 같으므로
-- 운영자가 새로 배울 상태값을 늘리지 않았습니다.
--
-- ★ 안전장치
-- · 링크가 실제로 끝난 것(CLAIMED·CANCELED·EXPIRED)만 손댑니다.
-- 아직 받아갈 수 있는 링크(CREATED)의 HOLD 는 그대로 둡니다 — 진짜 대기중이니까요.
-- · 거래가 HOLD 인 것만 손댑니다. 이미 끝난 거래는 건드리지 않습니다.
-- · transactions 는 status 만 바꿀 수 있습니다(불변성 트리거). 이 문장은 그 규칙을 지킵니다.
-- ─────────────────────────────────────────────────────────────
-- ① 수령까지 끝난 선물 → 전달 완료
UPDATE transactions t
JOIN gift_links g ON g.hold_txn_id = t.id
SET t.status = 'CONFIRMED'
WHERE t.status = 'HOLD'
AND g.status = 'CLAIMED';
-- ② 회수·만료된 선물 → 되돌아옴
UPDATE transactions t
JOIN gift_links g ON g.hold_txn_id = t.id
SET t.status = 'CANCELED'
WHERE t.status = 'HOLD'
AND g.status IN ('CANCELED', 'EXPIRED');
-- ═══════════════════════════════════════════════════════════
-- V58__drop_unused_txn_columns.sql
-- ═══════════════════════════════════════════════════════════
-- ─────────────────────────────────────────────────────────────
-- V58 : 거래 표에서 아무도 쓰지 않는 칸 2개 제거
--
-- 지우는 칸
-- ① transactions.detail_id — "거래상세 PK 참조"로 설계만 해 둔 칸
-- ② transactions.pg_order_id — "PG 주문 참조"로 설계만 해 둔 칸
--
-- 정말 안 쓰는 게 맞나요? — 지우기 전에 전부 뒤졌습니다(참조 0건).
-- · API 자바 코드 0건
-- · 매퍼 SQL(XML) 0건
-- · 관리자 화면(JS) 0건
-- · 회원앱·매장앱(Dart) 0건
-- · 인덱스 0건
-- · 외래키(FK) 0건
-- · 뷰 없음
-- · 실제 데이터 52행 전부 비어 있음(NULL)
-- 유일한 흔적이 아래 ③번 트리거 안의 pg_order_id 한 줄이었습니다.
--
-- ★ 헷갈리기 쉬운 점 — 이름이 같은 다른 칸은 그대로 둡니다.
-- webhook_deliveries.pg_order_id 는 <b>실제로 쓰는 칸</b>입니다
-- (WebhookMapper 가 넣고 꺼내며, pg_orders 로 외래키도 걸려 있습니다).
-- 이 파일은 transactions 의 칸만 건드립니다.
--
-- 왜 지금 지우나요?
-- 빈 칸을 남겨 두면 다음에 보는 사람이 "채워야 하나?" 하고 헤매고,
-- 나중에 진짜 필요해서 다시 만들 때 옛 칸과 새 칸이 겹쳐
-- 어느 쪽이 진짜인지 알 수 없게 됩니다. 필요해지면 그때 요구사항에 맞게 만듭니다.
--
-- ★ 되돌리려면: 두 칸의 정의는 V1__baseline.sql 189~190 줄에 그대로 남아 있습니다.
-- ─────────────────────────────────────────────────────────────
-- ③ 먼저 트리거에서 pg_order_id 를 빼야 합니다.
-- (없어진 칸을 트리거가 계속 쳐다보면 거래 수정 때마다 오류가 납니다)
-- 지우는 김에 detail_id 도 감시 목록에 넣지 않습니다 — 칸 자체가 사라지니까요.
-- 나머지 감시 대상과 동작은 V35 와 완전히 같습니다.
DROP TRIGGER IF EXISTS trg_txn_immutable_columns;
DELIMITER $$
-- 거래는 한 번 쓰면 고칠 수 없습니다.
-- 딱 4개(status·confirmed_at·bank_tran_ref·fail_reason)만 바꿀 수 있고,
-- 금액·종류·상대방·스냅샷 같은 증거 값은 관리자라도 못 고칩니다.
CREATE TRIGGER trg_txn_immutable_columns BEFORE UPDATE ON transactions FOR EACH ROW
BEGIN
IF NEW.amount <> OLD.amount OR NEW.type <> OLD.type OR NEW.subtype <> OLD.subtype
OR NEW.fee_amount <> OLD.fee_amount OR NEW.txn_uid <> OLD.txn_uid
OR NEW.initiator_type <> OLD.initiator_type OR NEW.initiator_id <> OLD.initiator_id
OR NOT (NEW.idempotency_key <=> OLD.idempotency_key)
OR NOT (NEW.card_id <=> OLD.card_id)
OR NOT (NEW.counterparty_card_id <=> OLD.counterparty_card_id)
OR NOT (NEW.merchant_id <=> OLD.merchant_id)
OR NOT (NEW.related_txn_id <=> OLD.related_txn_id)
OR NOT (NEW.fee_rate_snap <=> OLD.fee_rate_snap)
OR NOT (NEW.fee_fixed_snap <=> OLD.fee_fixed_snap)
OR NOT (NEW.vat_amount <=> OLD.vat_amount)
OR NOT (NEW.cancelable_until <=> OLD.cancelable_until)
OR NOT (NEW.scheduled_at <=> OLD.scheduled_at)
OR NOT (NEW.bank_account_id <=> OLD.bank_account_id)
OR NOT (NEW.memo <=> OLD.memo)
OR NEW.created_at <> OLD.created_at THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'transactions: only status/confirmed_at/bank_tran_ref/fail_reason may change';
END IF;
END$$
DELIMITER ;
-- ①② 이제 빈 칸 두 개를 없앱니다.
ALTER TABLE transactions
DROP COLUMN detail_id,
DROP COLUMN pg_order_id;
-- ④ 상태 설명을 실제와 맞춥니다.
--
-- 옛 설명에는 UNKNOWN 과 REVERSED 가 적혀 있었는데,
-- 이 두 값을 거래에 넣는 코드가 <b>한 줄도 없습니다</b>(전수 확인).
-- 쓰지 않는 값이 설명에 남아 있으면
-- · 화면 만드는 사람이 있지도 않은 상태의 이름표를 만들고,
-- · 조회 조건에 넣어도 아무것도 안 걸리는 죽은 조건이 생깁니다.
-- 실제로 그런 죽은 조건이 3곳 있었고 이번에 함께 지웠습니다.
--
-- 지금 거래에 들어가는 값은 아래 5개가 전부입니다.
ALTER TABLE transactions
MODIFY COLUMN `status` varchar(12) NOT NULL
COMMENT 'PENDING(이체중·출금)|HOLD(선차감 후 대기·출금/선물)|CONFIRMED(완료)|FAILED(실패·출금)|CANCELED(취소·회수·만료) - 이 5개가 전부. 새 값을 늘리기 전에 정말 필요한지 먼저 검토할 것';
-- ═══════════════════════════════════════════════════════════
-- V59__txn_combination_checks.sql
-- ═══════════════════════════════════════════════════════════
-- ─────────────────────────────────────────────────────────────
-- V59 : 거래에 "있을 수 없는 조합"이 들어가지 못하게 막기
-- + 쓰지 않는 표 1개 정리
--
-- 무엇이 문제였나요?
-- 거래 표에는 종류(type)·방식(subtype)·상태(status) 세 칸이 있는데,
-- 지금까지 DB 는 이 셋의 조합을 <b>전혀 검사하지 않았습니다.</b>
-- 실제로 시험해 보니 있을 수 없는 조합이 그대로 들어갔습니다:
--
-- INSERT ... ('GIFT', 'FIRMBANK', 'PENDING', 부가세=50, 지급예정=지금)
-- → 성공 (선물인데 은행이체 방식이고, 선물에 없는 '이체중' 상태이며,
-- 결제에만 있는 부가세와 출금에만 있는 지급예정시각까지 들어감)
--
-- 지금까지는 자바 코드가 문자열을 올바르게 넣어 준 덕분에 사고가 없었을 뿐입니다.
-- 코드 한 줄만 잘못 고쳐도 이상한 거래가 원장에 남고, 원장은 고칠 수 없습니다.
--
-- 어떻게 막나요?
-- 아래 조합표를 DB 규칙(CHECK)으로 박아 둡니다.
-- 조합표는 <b>추측이 아니라 코드에서 전수로 확인한 값</b>입니다.
-- (매퍼 8개의 INSERT 문과 상태를 바꾸는 UPDATE 문을 모두 읽어 정리했습니다)
--
-- ┌──────────┬────────────────────────────────────────┬──────────────────────────────────┐
-- │ 종류 │ 방식 │ 상태 │
-- ├──────────┼────────────────────────────────────────┼──────────────────────────────────┤
-- │ DEPOSIT │ VACCT(가상계좌 입금) │ CONFIRMED │
-- │ WITHDRAW │ FIRMBANK(회원 출금) │ HOLD→PENDING→CONFIRMED|FAILED │
-- │ │ SETTLEMENT(매장 정산) │ 〃 │
-- │ │ UNMATCHED_RETURN(주인 못 찾은 돈 반환) │ 〃 │
-- │ │ WITHDRAW_RESTORE(출금 실패 되돌림) │ CONFIRMED │
-- │ GIFT │ GIFT_DIRECT(바로 선물) │ CONFIRMED │
-- │ │ GIFT_LINK(링크 선물) │ HOLD→CONFIRMED|CANCELED │
-- │ │ GIFT_RETURN(회수 반환) │ CONFIRMED │
-- │ │ GIFT_EXPIRE_RETURN(만료 반환) │ CONFIRMED │
-- │ PAYMENT │ QR_ORDER(주문형) · QR_STORE(매장형) │ CONFIRMED→CANCELED │
-- │ CANCEL │ QR_CANCEL(결제취소) · PG_REFUND(환불) │ CONFIRMED │
-- │ ADJUST │ FORFEIT(해지 시 소액 포기) │ CONFIRMED │
-- └──────────┴────────────────────────────────────────┴──────────────────────────────────┘
--
-- ★ EXPIRE(소멸)는 뺐습니다 — 이 종류를 넣는 코드가 한 줄도 없습니다(전수 확인).
-- 쓰지 않는 값을 허용 목록에 두면 "언젠가 쓰겠지" 하고 계속 남습니다.
-- 실제로 필요해지면 그때 이 규칙에 한 줄 추가하면 됩니다.
--
-- ★ 새 거래 방식을 추가할 때
-- 이 규칙에 먼저 추가하지 않으면 INSERT 가 거부됩니다. 불편해 보이지만
-- <b>일부러 그렇게 만든 것입니다</b> — 원장에 이상한 값이 들어가는 것보다
-- 개발 중에 막히는 편이 훨씬 안전합니다.
-- ─────────────────────────────────────────────────────────────
-- ① 종류 × 방식
ALTER TABLE transactions ADD CONSTRAINT chk_txn_type_subtype CHECK (
(type = 'DEPOSIT' AND subtype IN ('VACCT'))
OR (type = 'WITHDRAW' AND subtype IN ('FIRMBANK', 'SETTLEMENT', 'UNMATCHED_RETURN', 'WITHDRAW_RESTORE'))
OR (type = 'GIFT' AND subtype IN ('GIFT_DIRECT', 'GIFT_LINK', 'GIFT_RETURN', 'GIFT_EXPIRE_RETURN'))
OR (type = 'PAYMENT' AND subtype IN ('QR_ORDER', 'QR_STORE'))
OR (type = 'CANCEL' AND subtype IN ('QR_CANCEL', 'PG_REFUND'))
OR (type = 'ADJUST' AND subtype IN ('FORFEIT'))
);
-- ② 종류 × 상태
-- 흐름마다 밟는 단계가 다릅니다. 결제는 '이체중'이 될 수 없고,
-- 선물은 '실패'로 남지 않으며(잘못되면 거래 자체가 안 만들어집니다),
-- 돈이 밖으로 나가는 출금·정산만 4단계를 전부 밟습니다.
ALTER TABLE transactions ADD CONSTRAINT chk_txn_type_status CHECK (
(type = 'DEPOSIT' AND status = 'CONFIRMED')
OR (type = 'WITHDRAW' AND status IN ('HOLD', 'PENDING', 'CONFIRMED', 'FAILED'))
OR (type = 'GIFT' AND status IN ('HOLD', 'CONFIRMED', 'CANCELED'))
OR (type = 'PAYMENT' AND status IN ('CONFIRMED', 'CANCELED'))
OR (type = 'CANCEL' AND status = 'CONFIRMED')
OR (type = 'ADJUST' AND status = 'CONFIRMED')
);
-- ③ 결제에는 반드시 매장이 있어야 합니다.
-- 매장 없는 결제는 "누구에게 낸 돈인지 모르는 돈"이라 정산도 소명도 불가능합니다.
ALTER TABLE transactions ADD CONSTRAINT chk_txn_payment_merchant CHECK (
type <> 'PAYMENT' OR merchant_id IS NOT NULL
);
-- ─────────────────────────────────────────────────────────────
-- ④ 쓰지 않는 표 정리 : exchange_providers
--
-- "포인트 전환(제휴사)" 기능을 위해 설계만 해 둔 표입니다.
-- 지우기 전에 전부 확인했습니다 — 참조 0건:
-- · 매퍼 SQL 0건 · 자바 0건 · 관리자 화면 0건 · 앱 0건 · 데이터 0행
-- · 요구사항 문서에도 이 기능 언급이 없습니다
-- V1 주석에도 "설계 유지·구현 보류"라고 적혀 있습니다.
--
-- 앞서 V56 에서 같은 이유로 쓰지 않는 표 3개를 정리했고, 그 연장입니다.
-- ★ 되돌리려면: 표 정의는 V1__baseline.sql 678 줄에 그대로 남아 있습니다.
-- ─────────────────────────────────────────────────────────────
DROP TABLE IF EXISTS exchange_providers;
-- ═══════════════════════════════════════════════════════════
-- V60__remove_gift_link.sql
-- ═══════════════════════════════════════════════════════════
-- ─────────────────────────────────────────────────────────────
-- V60 : "링크 선물" 기능을 통째로 없앱니다
--
-- 왜 없애나요?
-- 선물은 두 가지가 있었습니다.
-- ① 바로 선물 — 받는 사람을 휴대폰 번호나 카드번호로 지정해 즉시 보냄
-- ② 링크 선물 — 금액을 정해 링크를 만들고, 상대가 24시간 안에 받아 감
--
-- ②가 ①보다 더 해 주는 일은 사실상 "회수" 하나뿐인데,
-- 그 하나를 위해 아래를 전부 떠안고 있었습니다.
-- · 주인 없는 돈이 머무는 보류 지갑(GIFT_ESCROW)
-- · 보류(HOLD) 상태와, 그것을 끝맺는 세 갈래(수령·회수·만료)
-- · 5분마다 도는 만료 워커
-- · 링크 토큰을 보관하는 표(gift_links)
--
-- ★ 결정적으로, 링크를 받으려면 <b>로그인이 필요합니다</b>(/app/me 구역).
-- 즉 "회원이 아닌 사람에게도 보낼 수 있다"는 링크의 유일한 명분이 없었습니다.
-- 받는 사람이 어차피 회원이어야 한다면, 번호로 바로 보내는 편이
-- 단계도 적고 중간에 잘못될 구간도 없습니다.
--
-- 실제로 사고도 있었습니다 — 세 갈래 중 어느 것도 보류 거래를 끝맺지 않아
-- 돈은 이미 정리됐는데 이용내역에는 "선물 대기중"이 영원히 남았습니다(V57 에서 정리).
-- 중간 상태가 없으면 애초에 생길 수 없는 문제입니다.
--
-- 앞으로 선물은 한 가지입니다
-- 회원앱에서 휴대폰 번호 또는 카드번호로 상대를 골라 바로 보냅니다.
-- 보내는 즉시 상대 지갑에 들어가므로 상태는 '완료' 하나뿐이고,
-- 보내기 전에 상대 이름을 가려서(김*이) 확인시켜 오송금을 막습니다.
--
-- ★ 옛 데이터가 있는 곳에서는?
-- 이 파일은 원장을 건드리지 않습니다(원장은 고치지도 지우지도 않는 것이 원칙).
-- 아래 ② 규칙이 좁아지므로, 이미 링크 선물 거래가 쌓인 데이터베이스에서는
-- 먼저 그 데이터를 어떻게 할지 정한 뒤 적용해야 합니다.
-- 개발 중 로컬은 데이터베이스를 처음부터 다시 만들어 적용했고,
-- 운영은 서비스를 열기 전이라 해당 거래가 없습니다.
--
-- ★ 되돌리려면
-- 표 정의는 V1__baseline.sql 에, 보류 지갑은 V5__system_wallets.sql 에 남아 있습니다.
-- ─────────────────────────────────────────────────────────────
-- ① 링크 표 제거
-- (sender_card_id · claimed_card_id 가 cards 를 참조하지만,
-- 이 표를 가리키는 쪽은 없으므로 그냥 지우면 됩니다)
DROP TABLE IF EXISTS gift_links;
-- ② 조합 규칙을 좁힙니다 (V59 에서 만든 규칙을 대체)
--
-- 선물에 남는 방식은 GIFT_DIRECT 하나, 상태는 CONFIRMED 하나입니다.
-- HOLD 도 CANCELED 도 더는 생기지 않습니다 — 중간 상태가 사라졌기 때문입니다.
-- 이제 "선물이 대기 중"이라는 상태 자체가 존재할 수 없습니다.
ALTER TABLE transactions DROP CONSTRAINT chk_txn_type_subtype;
ALTER TABLE transactions DROP CONSTRAINT chk_txn_type_status;
ALTER TABLE transactions ADD CONSTRAINT chk_txn_type_subtype CHECK (
(type = 'DEPOSIT' AND subtype IN ('VACCT'))
OR (type = 'WITHDRAW' AND subtype IN ('FIRMBANK', 'SETTLEMENT', 'UNMATCHED_RETURN', 'WITHDRAW_RESTORE'))
OR (type = 'GIFT' AND subtype IN ('GIFT_DIRECT'))
OR (type = 'PAYMENT' AND subtype IN ('QR_ORDER', 'QR_STORE'))
OR (type = 'CANCEL' AND subtype IN ('QR_CANCEL', 'PG_REFUND'))
OR (type = 'ADJUST' AND subtype IN ('FORFEIT'))
);
ALTER TABLE transactions ADD CONSTRAINT chk_txn_type_status CHECK (
(type = 'DEPOSIT' AND status = 'CONFIRMED')
OR (type = 'WITHDRAW' AND status IN ('HOLD', 'PENDING', 'CONFIRMED', 'FAILED'))
OR (type = 'GIFT' AND status = 'CONFIRMED')
OR (type = 'PAYMENT' AND status IN ('CONFIRMED', 'CANCELED'))
OR (type = 'CANCEL' AND status = 'CONFIRMED')
OR (type = 'ADJUST' AND status = 'CONFIRMED')
);
-- ③ 보류 지갑 제거
-- 선물 보류금이 머물 곳이 더는 필요 없습니다.
--
-- ★ 지우는 순서가 중요합니다.
-- 이 지갑을 가리키는 곳이 두 군데 있습니다(외래키).
-- · daily_wallet_snapshots — 일 마감이 남기는 잔액 사진(파생 데이터)
-- · point_lots — 포인트 로트 (시스템 지갑에는 원래 없음)
-- 가리키는 쪽을 먼저 치워야 지갑을 지울 수 있습니다.
--
-- ★ 안전장치
-- 분개(ledger_entries)가 한 줄이라도 남아 있으면 지우지 않습니다.
-- 남아 있다면 그건 <b>아직 정리되지 않은 돈</b>이라는 뜻이므로,
-- 지갑을 없애면 그 돈의 행방을 추적할 수 없게 됩니다. 사람이 먼저 확인해야 합니다.
-- ③-1 잔액 사진 먼저 정리 (원장이 아니라 파생 데이터라 지워도 됩니다)
DELETE FROM daily_wallet_snapshots
WHERE wallet_id IN (
SELECT id FROM (
SELECT w.id FROM wallets w
WHERE w.owner_type = 'SYSTEM' AND w.system_code = 'GIFT_ESCROW'
AND NOT EXISTS (SELECT 1 FROM ledger_entries l WHERE l.wallet_id = w.id)
) AS target
);
-- ③-2 지갑 제거
DELETE FROM wallets
WHERE owner_type = 'SYSTEM'
AND system_code = 'GIFT_ESCROW'
AND NOT EXISTS (SELECT 1 FROM ledger_entries l WHERE l.wallet_id = wallets.id)
AND NOT EXISTS (SELECT 1 FROM point_lots p WHERE p.wallet_id = wallets.id);
-- ④ 설명(COMMENT)을 실제와 맞춥니다
--
-- V1 에 적어 둔 설명에는 이제 존재하지 않는 값들이 남아 있습니다.
-- 설명이 실제와 다르면 다음 사람이 "이 값도 쓰나?" 하고 헤매거나,
-- 있지도 않은 값을 화면 이름표에 넣습니다(실제로 그런 일이 있었습니다).
ALTER TABLE transactions
MODIFY COLUMN `subtype` varchar(20) NOT NULL
COMMENT 'DEPOSIT:VACCT | WITHDRAW:FIRMBANK·SETTLEMENT·UNMATCHED_RETURN·WITHDRAW_RESTORE | GIFT:GIFT_DIRECT | PAYMENT:QR_ORDER·QR_STORE | CANCEL:QR_CANCEL·PG_REFUND | ADJUST:FORFEIT - 종류와의 조합을 chk_txn_type_subtype 이 강제';
ALTER TABLE wallets
MODIFY COLUMN `system_code` varchar(30)
COMMENT 'SYSTEM 일 때만: FEE_REVENUE(수수료수익)|EXPIRED(낙전)|UNMATCHED(미매칭입금)|SETTLEMENT_CLEARING(대외청산)|FORFEITED(탈퇴 소액포기) - 5개. GIFT_ESCROW 는 링크 선물 폐지로 제거';
-- ═══════════════════════════════════════════════════════════
-- V61__point_lots_txn_id.sql
-- ═══════════════════════════════════════════════════════════
-- ─────────────────────────────────────────────────────────────
-- V61 : 포인트 덩어리에 "어느 거래에서 생겼는지"를 적습니다
--
-- 무엇이 빠져 있었나요?
-- 포인트는 충전할 때마다 <b>덩어리(로트)</b>가 따로 생깁니다.
-- 유효기간이 각각 다르기 때문에 한 덩어리로 합칠 수 없습니다.
--
-- 그런데 그 덩어리에 "어느 거래에서 생겼는지"를 적는 칸이 없었습니다.
-- · 포인트를 <b>쓴 기록</b>은 남습니다 (lot_allocations → 어느 결제에 썼는지)
-- · 포인트가 <b>들어온 기록</b>은 없었습니다
-- 나간 근거는 있는데 들어온 근거가 없는 셈입니다.
--
-- 시각으로 찾아보려 해도 안 됩니다. 거래·분개·로트가 한 트랜잭션에서
-- <b>같은 초에</b> 기록되므로, 실제로 확인해 보니 충전 거래와 선물 거래가
-- 함께 걸려 어느 쪽인지 갈라낼 수 없었습니다.
--
-- 무엇이 곤란해지나요?
-- · 회원이 "내 포인트 5만원이 어느 충전에서 온 건가요?" 물으면 답할 수 없습니다.
-- · 선불충전금 잔액의 출처를 거래 단위로 제시해야 할 때 추정만 가능합니다.
-- · 만료로 소멸된 포인트가 어느 충전분이었는지 근거가 생성시각뿐입니다.
--
-- ★ 왜 "반드시 있어야 함(NOT NULL)" 인가요?
-- 거래 없이 생긴 포인트는 출처를 설명할 수 없는 돈입니다. 아예 못 만들게 막습니다.
-- 관리자가 수동으로 포인트를 주더라도 ADJUST 거래를 남기면 되고,
-- 실제로 탈퇴 시 소액 포기가 이미 그렇게(ADJUST/FORFEIT) 처리되고 있습니다.
--
-- 지금 넣는 이유도 이것입니다 — 서비스를 열기 전이라 기존 데이터가 없어
-- 빈칸 없이 NOT NULL 을 걸 수 있습니다. 나중에 데이터가 쌓이면
-- 과거 포인트의 출처는 영영 채울 수 없습니다.
--
-- 어디서 값을 넣나요? (로트가 생기는 곳 전부 — 5군데)
-- ① 충전 완료 AppDepositService → 충전 거래
-- ② 미매칭 입금 수동매칭 AdminUnmatchedService → 관리자가 만든 충전 거래
-- ③ 선물 수취 AppGiftService → 선물 거래
-- ④ 결제 취소 복원 AppCancelService → 취소 거래
-- ⑤ 출금 실패 복원 AppWithdrawService → 복원 거래
-- 다섯 곳 모두 같은 트랜잭션 안에서 이미 거래번호를 알고 있어, 넘기기만 하면 됩니다.
-- ─────────────────────────────────────────────────────────────
-- ① 칸 추가
-- 우선 빈칸을 허용해 만든 뒤(기존 행이 있어도 실패하지 않도록),
-- 아래 ②에서 채우고 ③에서 "반드시 있어야 함"으로 조입니다.
ALTER TABLE point_lots
ADD COLUMN `txn_id` bigint NULL
COMMENT '이 포인트 덩어리를 만든 거래(transactions.id) - 출처 추적·소명 근거'
AFTER id;
-- ② 이미 있는 행 채우기
-- 서비스 개시 전이라 보통은 0행입니다. 혹시 있다면 같은 지갑에 같은 시각으로
-- 기록된 거래를 찾아 채웁니다(한 트랜잭션에서 함께 쓰이므로 시각이 같습니다).
-- 여러 건이 걸리면 가장 이른 거래를 택합니다 — 정확히 가를 수 없는 옛 데이터라
-- 근사치이며, 이 한계 때문에 이 칸을 만드는 것입니다.
UPDATE point_lots p
SET p.txn_id = (
SELECT MIN(l.txn_id) FROM ledger_entries l
WHERE l.wallet_id = p.wallet_id
AND ABS(TIMESTAMPDIFF(SECOND, l.created_at, p.created_at)) <= 1
)
WHERE p.txn_id IS NULL;
-- 그래도 못 찾은 행이 있으면(참조할 거래가 없는 고아 로트) 여기서 멈춰야 합니다.
-- 아래 ③의 NOT NULL 이 실패하면서 마이그레이션이 중단되고, 사람이 원인을 봐야 합니다.
-- (조용히 넘어가면 출처 없는 포인트가 그대로 남습니다)
-- ③ 반드시 있어야 함 + 찾기 빠르게
ALTER TABLE point_lots
MODIFY COLUMN `txn_id` bigint NOT NULL
COMMENT '이 포인트 덩어리를 만든 거래(transactions.id) - 출처 추적·소명 근거';
CREATE INDEX ix_lots_txn ON point_lots (txn_id);
-- ④ 출처(source) 설명을 실제와 맞춥니다
-- REISSUE·EVENT·EXCHANGE 는 넣는 코드가 한 줄도 없습니다(전수 확인).
-- 쓰지 않는 값이 설명에 남아 있으면 다음 사람이 "이 값도 쓰나?" 하고 헤맵니다.
ALTER TABLE point_lots
MODIFY COLUMN `source` varchar(30) NOT NULL
COMMENT 'DEPOSIT(충전)|GIFT(선물수취)|CANCEL_RESTORE(결제취소 복원)|WITHDRAW_RESTORE(출금실패 복원) - 이 4개가 전부. 이력 메타이며 환급 판정에는 쓰지 않습니다(환급은 lot_type=DEPOSIT 기준)';
-- ═══════════════════════════════════════════════════════════
-- V62__banks.sql
-- ═══════════════════════════════════════════════════════════
-- ─────────────────────────────────────────────────────────────
-- V62 : 은행 목록을 데이터베이스 한 곳에서 관리합니다
--
-- 무엇이 문제였나요?
-- 은행 표가 두 곳에 <b>따로</b> 적혀 있었습니다.
-- · 앱 packages/nestpay_shared/lib/data/banks.dart (kBankNames)
-- · 관리자 apps/admin/js/ui.js (BANK_NAMES)
--
-- 따로 두니 실제로 어긋났습니다.
-- · 앱에만 없던 은행 4개(KDB산업·새마을금고·신협·우체국)
-- → 그 은행을 쓰는 회원·매장은 <b>계좌 등록 자체가 불가능</b>했습니다.
-- · 같은 코드인데 이름이 달랐습니다(003 = 'IBK기업' / '기업').
--
-- 두 파일을 맞춰 놓아도, 은행이 하나 늘 때마다 두 곳을 고치고
-- <b>앱을 새로 배포</b>해야 합니다. 사용자가 앱을 갱신하지 않으면 계속 옛 목록입니다.
--
-- 그래서 어떻게 하나요?
-- 은행 목록을 이 표 하나에 두고, 앱·매장앱·관리자가 모두 서버에서 받아 씁니다.
-- 은행이 늘거나 이름이 바뀌어도 <b>이 표만 고치면</b> 모든 화면에 즉시 반영됩니다.
--
-- ★ 코드는 화면에 보여 주지 않습니다.
-- 004·088 같은 값은 시스템 내부용입니다. 사람에게는 이름만 보여 주고,
-- 고른 항목의 코드를 서버로 보냅니다.
--
-- ★ 순서(sort_order)
-- 화면 선택 목록에 나오는 차례입니다. 작을수록 위에 옵니다.
-- 자주 쓰는 은행을 앞에 두어 스크롤을 줄입니다.
--
-- ★ 상태(status)
-- HIDDEN 으로 두면 목록에서 사라집니다. 이미 그 은행으로 등록된 계좌는
-- 그대로 남으므로(코드는 유지) 과거 기록이 깨지지 않습니다.
-- 합병·폐업 은행을 지우지 않고 감추는 용도입니다.
-- ─────────────────────────────────────────────────────────────
CREATE TABLE banks (
`code` varchar(3) NOT NULL COMMENT '금융결제원 표준 은행 코드 3자리 - 시스템 내부 값(화면에 보여 주지 않음)',
`name` varchar(40) NOT NULL COMMENT '사람에게 보여 줄 은행 이름',
`sort_order` int NOT NULL DEFAULT 0 COMMENT '선택 목록에 나오는 차례(작을수록 위)',
`status` varchar(10) NOT NULL DEFAULT 'ACTIVE' COMMENT 'ACTIVE=목록에 보임 | HIDDEN=목록에서 감춤(기존 계좌는 유지)',
`created_at` datetime NOT NULL,
PRIMARY KEY (`code`),
KEY `ix_banks_list` (`status`, `sort_order`)
) COMMENT = '은행 목록 - 앱·매장앱·관리자가 모두 이 표를 받아 씁니다(단일 출처)';
-- 처음 채우는 값 — 지금까지 앱·관리자에 나뉘어 있던 것을 합친 22개입니다.
INSERT INTO banks (`code`, `name`, `sort_order`, `status`, `created_at`) VALUES
-- 자주 쓰는 은행
('004', 'KB국민', 10, 'ACTIVE', NOW()),
('088', '신한', 20, 'ACTIVE', NOW()),
('020', '우리', 30, 'ACTIVE', NOW()),
('081', '하나', 40, 'ACTIVE', NOW()),
('011', 'NH농협', 50, 'ACTIVE', NOW()),
('003', 'IBK기업', 60, 'ACTIVE', NOW()),
-- 인터넷 전문은행
('090', '카카오뱅크', 110, 'ACTIVE', NOW()),
('089', '케이뱅크', 120, 'ACTIVE', NOW()),
('092', '토스뱅크', 130, 'ACTIVE', NOW()),
-- 그 밖의 전국 단위
('002', 'KDB산업', 210, 'ACTIVE', NOW()),
('007', '수협', 220, 'ACTIVE', NOW()),
('023', 'SC제일', 230, 'ACTIVE', NOW()),
('027', '한국씨티', 240, 'ACTIVE', NOW()),
('071', '우체국', 250, 'ACTIVE', NOW()),
('045', '새마을금고', 260, 'ACTIVE', NOW()),
('048', '신협', 270, 'ACTIVE', NOW()),
-- 지방은행
('031', '대구', 310, 'ACTIVE', NOW()),
('032', '부산', 320, 'ACTIVE', NOW()),
('034', '광주', 330, 'ACTIVE', NOW()),
('039', '경남', 340, 'ACTIVE', NOW()),
('037', '전북', 350, 'ACTIVE', NOW()),
('035', '제주', 360, 'ACTIVE', NOW());
-- ═══════════════════════════════════════════════════════════
-- V63__merchant_status_comment.sql
-- ═══════════════════════════════════════════════════════════
-- ─────────────────────────────────────────────────────────────
-- V63 : 매장 상태 설명을 실제와 맞춥니다
--
-- 무엇이 어긋나 있었나요?
-- 설명에는 SUPPLEMENT(보완 대기)가 빠져 있었습니다.
-- 그런데 이 값은 실제로 쓰입니다 —
-- 관리자가 [보충요청] 하면 PENDING → SUPPLEMENT 로 바뀌고,
-- 매장이 서류를 다시 내면(/store/resubmit) SUPPLEMENT → PENDING 으로 돌아옵니다.
-- 설명에 없으니 다음 사람이 "이 값은 뭐지?" 하거나, 화면 이름표에서 빠뜨리게 됩니다.
--
-- 상태별로 할 수 있는 일도 함께 적어 둡니다.
-- 문지기(StoreAuthFilter)가 이 규칙대로 막습니다 — 설명과 코드가 어긋나지 않도록.
-- ─────────────────────────────────────────────────────────────
-- ★ merchants 는 변경 이력을 자동 보존하는 표(SYSTEM VERSIONED)라 기본적으로 구조 변경이 막힙니다.
-- KEEP = "지금까지 쌓인 이력을 그대로 둔 채" 구조만 바꾸겠다는 뜻입니다(V3 와 같은 방식).
SET @@system_versioning_alter_history = KEEP;
ALTER TABLE merchants
MODIFY COLUMN `status` varchar(20) NOT NULL DEFAULT 'PENDING'
COMMENT 'PENDING(심사중)|SUPPLEMENT(보완대기)|ACTIVE(정상)|REJECTED(반려)|SUSPENDED(정지)|CLOSED(해지) - 할 수 있는 일: ACTIVE=전부 · PENDING/SUPPLEMENT=상태확인+서류/PG준비 · 그 밖=상태확인만(StoreAuthFilter 가 강제)';
-- ═══════════════════════════════════════════════════════════
-- V64__account_restrictions.sql
-- ═══════════════════════════════════════════════════════════
-- ─────────────────────────────────────────────────────────────
-- V64 : 기능별 이용제한 (회원·매장)
--
-- 무엇이 없었나요?
-- 지금까지는 "전부 열림(ACTIVE)" 아니면 "전부 닫힘(SUSPENDED)" 둘뿐이었습니다.
-- 그런데 실제 운영에서는 이런 경우가 생깁니다.
-- · 출금만 막고 결제는 계속 쓰게 하고 싶다(계좌 도용 의심)
-- · 선물만 막고 싶다(선물 되팔이 의심)
-- · 매장 정산만 잠깐 멈추고 결제는 받게 하고 싶다(정산 계좌 확인 중)
-- 상태 한 칸으로는 표현할 수 없어, 제한을 따로 적어 두는 표를 만듭니다.
--
-- ★ "전체 정지"는 여기에 넣지 않습니다.
-- 그건 이미 users.status / merchants.status 가 하고 있습니다.
-- 같은 일을 두 곳에 두면 어느 쪽이 진짜인지 알 수 없게 됩니다.
-- 이 표는 <b>개별 기능만</b> 다룹니다.
--
-- ★ 해제해도 행을 지우지 않습니다.
-- released_at 에 푼 시각을 적습니다. 언제 왜 막았고 언제 풀었는지가 남아야
-- 나중에 다툼이 생겼을 때 설명할 수 있습니다(PG 사의 소명 자료).
--
-- ★ 같은 대상·같은 기능에 살아 있는 제한은 하나뿐입니다.
-- alive_key(생성 칸) + UNIQUE 로 데이터베이스가 강제합니다.
-- 두 번 막아 두면 한 번 풀어도 여전히 막혀 있어 운영자가 혼란스럽습니다.
-- (현금영수증에서 쓴 것과 같은 방식입니다)
-- ─────────────────────────────────────────────────────────────
CREATE TABLE account_restrictions (
`id` bigint NOT NULL AUTO_INCREMENT,
`principal_type` varchar(10) NOT NULL COMMENT 'USER(회원) | MERCHANT(매장)',
`principal_id` bigint NOT NULL COMMENT '회원 번호 또는 매장 번호',
`feature` varchar(20) NOT NULL
COMMENT '막을 기능 — 회원: DEPOSIT(충전)·WITHDRAW(출금)·GIFT(선물)·PAYMENT(결제) / 매장: PAYMENT(결제받기)·SETTLEMENT(정산)',
`reason` varchar(200) NOT NULL COMMENT '막은 이유 - 필수. 나중에 왜 막았는지 설명할 수 있어야 합니다',
`created_by` bigint NOT NULL COMMENT '막은 관리자',
`created_at` datetime NOT NULL,
`released_by` bigint COMMENT '푼 관리자(아직 막혀 있으면 비어 있음)',
`released_at` datetime COMMENT '푼 시각. 비어 있으면 지금도 막힌 상태입니다',
`release_reason` varchar(200) COMMENT '푼 이유',
-- 살아 있는 제한만 값을 갖는 칸 — 같은 대상·기능에 두 번 걸리지 않게 합니다.
`alive_key` varchar(40) AS (CASE WHEN released_at IS NULL
THEN CONCAT(principal_type, ':', principal_id, ':', feature)
ELSE NULL END) VIRTUAL,
PRIMARY KEY (`id`),
UNIQUE KEY `ux_restriction_alive` (`alive_key`),
-- 문지기가 매 요청 "이 사람에게 살아 있는 제한이 있나"를 묻습니다. 그 조회를 그대로 태웁니다.
KEY `ix_restriction_lookup` (`principal_type`, `principal_id`, `released_at`),
-- 있을 수 없는 조합을 데이터베이스가 막습니다(회원에게 SETTLEMENT 를 걸 수는 없습니다).
CONSTRAINT `chk_restriction_feature` CHECK (
(`principal_type` = 'USER' AND `feature` IN ('DEPOSIT', 'WITHDRAW', 'GIFT', 'PAYMENT'))
OR (`principal_type` = 'MERCHANT' AND `feature` IN ('PAYMENT', 'SETTLEMENT'))
)
) COMMENT = '기능별 이용제한 - 전체 정지는 users.status/merchants.status 가 담당하고, 이 표는 개별 기능만 막습니다';
-- ═══════════════════════════════════════════════════════════
-- V65__seizures.sql
-- ═══════════════════════════════════════════════════════════
-- ─────────────────────────────────────────────────────────────
-- V65 : 포인트 압류 (사고가 났을 때 돈을 묶어 두는 기능)
--
-- 무엇에 쓰나요?
-- 사고(도용·분쟁·수사 협조 등)가 생기면 그 돈이 빠져나가지 못하게 묶어 둬야 합니다.
-- 조사 결과에 따라 <b>돌려주거나(반환)</b> <b>없애야(소각)</b> 합니다.
--
-- 어떻게 묶나요? — 돈을 실제로 옮깁니다
-- 회원 지갑 → 압류 보관 지갑(SEIZED) 으로 옮깁니다.
-- 그래서 잔액이 그만큼 줄고, 결제·출금·선물에 쓸 수 없게 됩니다.
--
-- ★ 왜 "표시만" 하지 않나요?
-- 잔액은 그대로 두고 "쓸 수 있는 금액"을 따로 계산하는 방법도 있지만,
-- 돈을 쓰는 곳(결제·출금·선물·정산)마다 그 계산을 넣어야 하고
-- <b>한 곳만 빠뜨려도 묶어 둔 돈이 새어 나갑니다.</b>
-- 지갑을 옮기면 잔액 자체가 줄어 어디서도 쓸 수 없습니다.
-- 이미 수수료·정산청산 같은 시스템 지갑이 같은 방식으로 돌고 있습니다.
--
-- 조사가 끝나면 둘 중 하나입니다
-- · 반환 — 압류 지갑 → 회원 지갑. 포인트 덩어리도 원래 성질(유효기간)대로 되살립니다.
-- · 소각 — 압류 지갑 → 소각 지갑(SEIZE_BURNED). 회원에게 돌아가지 않습니다.
--
-- ★ 전부 아니면 전무입니다(부분 반환·부분 소각 없음).
-- "3만원 중 1만원만 반환"을 허용하면 압류 건마다 남은 금액을 따로 추적해야 하고,
-- 돈 계산에 갈래가 늘수록 어긋날 자리가 늘어납니다.
-- 나눠 처리해야 하면 애초에 압류를 나눠서 걸면 됩니다.
--
-- ★ 압류 이력은 지우지 않습니다.
-- 언제 왜 묶었고 어떻게 끝냈는지가 남아야 나중에 설명할 수 있습니다(PG 사 소명 자료).
-- ─────────────────────────────────────────────────────────────
-- ① 돈이 머무는 자리 두 곳
-- · SEIZED : 조사 중인 돈이 머무는 곳. 아직 주인이 정해지지 않았습니다.
-- · SEIZE_BURNED : 소각으로 확정된 돈. 회원에게 돌아가지 않습니다.
-- (탈퇴 소액포기 FORFEITED · 유효기간 만료 EXPIRED 와 성격이 달라 따로 둡니다 —
-- 회계 처리와 소명 근거가 다르기 때문입니다)
INSERT INTO wallets (owner_type, system_code, balance, created_at) VALUES
('SYSTEM', 'SEIZED', 0, NOW()),
('SYSTEM', 'SEIZE_BURNED', 0, NOW());
-- ② 압류 대장
CREATE TABLE seizures (
`id` bigint NOT NULL AUTO_INCREMENT,
`principal_type` varchar(10) NOT NULL COMMENT 'USER(회원) | MERCHANT(매장)',
`principal_id` bigint NOT NULL COMMENT '회원 번호 또는 매장 번호',
`wallet_id` bigint NOT NULL COMMENT '묶은 지갑(회원은 카드 지갑, 매장은 매장 지갑)',
`amount` decimal(15,0) NOT NULL COMMENT '묶은 금액',
`reason` varchar(200) NOT NULL COMMENT '묶은 이유 - 필수. 관리자 내부용이며 앱에는 보여 주지 않습니다',
`status` varchar(10) NOT NULL DEFAULT 'HELD'
COMMENT 'HELD(보관중) | RETURNED(돌려줌) | BURNED(소각함)',
`seize_txn_id` bigint NOT NULL COMMENT '묶을 때 만든 거래',
`resolve_txn_id` bigint COMMENT '반환·소각할 때 만든 거래',
`created_by` bigint NOT NULL COMMENT '묶은 관리자',
`created_at` datetime NOT NULL,
`resolved_by` bigint COMMENT '끝낸 관리자',
`resolved_at` datetime COMMENT '끝낸 시각. 비어 있으면 아직 조사 중입니다',
`resolve_reason` varchar(200) COMMENT '반환·소각한 이유',
PRIMARY KEY (`id`),
-- 앱·문지기가 "이 사람에게 지금 묶여 있는 돈이 있나"를 묻습니다. 그 조회를 그대로 태웁니다.
KEY `ix_seizure_holder` (`principal_type`, `principal_id`, `status`),
CONSTRAINT `chk_seizure_amount` CHECK (`amount` > 0),
CONSTRAINT `chk_seizure_status` CHECK (`status` IN ('HELD', 'RETURNED', 'BURNED')),
CONSTRAINT `chk_seizure_type` CHECK (`principal_type` IN ('USER', 'MERCHANT'))
) COMMENT = '포인트 압류 대장 - 사고 시 돈을 묶어 두고, 조사 후 반환하거나 소각합니다';
-- ③ 거래 종류에 압류 3가지를 추가합니다
-- 관리자 조정(ADJUST) 계열입니다 — 회원·매장이 스스로 하는 일이 아니기 때문입니다.
-- SEIZE 묶기 : 회원/매장 지갑 → SEIZED
-- SEIZE_RETURN 돌려줌 : SEIZED → 회원/매장 지갑
-- SEIZE_BURN 소각 : SEIZED → SEIZE_BURNED
ALTER TABLE transactions DROP CONSTRAINT chk_txn_type_subtype;
ALTER TABLE transactions ADD CONSTRAINT chk_txn_type_subtype CHECK (
(type = 'DEPOSIT' AND subtype IN ('VACCT'))
OR (type = 'WITHDRAW' AND subtype IN ('FIRMBANK', 'SETTLEMENT', 'UNMATCHED_RETURN', 'WITHDRAW_RESTORE'))
OR (type = 'GIFT' AND subtype IN ('GIFT_DIRECT'))
OR (type = 'PAYMENT' AND subtype IN ('QR_ORDER', 'QR_STORE'))
OR (type = 'CANCEL' AND subtype IN ('QR_CANCEL', 'PG_REFUND'))
OR (type = 'ADJUST' AND subtype IN ('FORFEIT', 'SEIZE', 'SEIZE_RETURN', 'SEIZE_BURN'))
);
-- ④ 포인트 덩어리 출처에 압류 반환을 추가합니다
-- 반환할 때는 원래 덩어리의 성질(유효기간)을 이어받은 새 덩어리를 만듭니다
-- — 결제 취소 복원과 같은 방식입니다.
ALTER TABLE point_lots
MODIFY COLUMN `source` varchar(30) NOT NULL
COMMENT 'DEPOSIT(충전)|GIFT(선물수취)|CANCEL_RESTORE(결제취소 복원)|WITHDRAW_RESTORE(출금실패 복원)|SEIZE_RESTORE(압류 반환) - 이 5개가 전부. 이력 메타이며 환급 판정에는 lot_type=DEPOSIT 을 씁니다';
-- ⑤ 시스템 지갑 설명도 실제와 맞춥니다
ALTER TABLE wallets
MODIFY COLUMN `system_code` varchar(30)
COMMENT 'SYSTEM 일 때만: FEE_REVENUE(수수료수익)|EXPIRED(낙전)|UNMATCHED(미매칭입금)|SETTLEMENT_CLEARING(대외청산)|FORFEITED(탈퇴 소액포기)|SEIZED(압류 보관)|SEIZE_BURNED(압류 소각) - 7개';
-- ═══════════════════════════════════════════════════════════
-- V66__merchant_biz_type_check.sql
-- ═══════════════════════════════════════════════════════════
-- ─────────────────────────────────────────────────────────────
-- V66 : 매장 구분(개인/법인)에 잘못된 값이 들어가지 못하게 막습니다
--
-- 무슨 일이 있었나요?
-- 관리자 화면에서 매장 구분이 <b>INDIVIDUAL</b> 이라는 영어 그대로 보였습니다.
-- 화면은 CORP(법인)·SOLE(개인사업자) 두 가지만 우리말로 바꿔 주는데,
-- 데이터베이스에는 그 둘이 아닌 값이 들어가 있어서 바꿀 말을 찾지 못한 것입니다.
--
-- 찾아보니 코드(서버·관리자·홈페이지) 어디에도 INDIVIDUAL 을 넣는 곳은 없었습니다.
-- 즉 <b>규격에 없는 값이 어쩌다 들어갔는데 아무도 막지 않았다</b>는 뜻입니다.
--
-- ★ 화면만 고치면 안 됩니다.
-- "INDIVIDUAL 도 개인으로 보여 주자"고 하면 당장은 보기 좋아지지만,
-- 같은 뜻의 값이 두 개가 되어 통계·정산·서류 요구 규칙이 갈라집니다.
-- 값은 하나로 모으고, 앞으로 다른 값이 못 들어오게 데이터베이스가 막는 것이 맞습니다.
--
-- 하는 일
-- ① 이미 들어간 INDIVIDUAL 을 SOLE(개인사업자)로 맞춥니다 — 뜻이 같습니다.
-- ② CORP·SOLE 이외의 값은 아예 저장되지 않도록 검사 규칙을 겁니다.
--
-- ※ merchants 는 SYSTEM VERSIONED(변경 이력 자동 보존) 표라,
-- 구조를 바꾸기 전에 이력을 그대로 두라고 알려 주어야 합니다(그러지 않으면 오류 4119).
-- ─────────────────────────────────────────────────────────────
SET @@system_versioning_alter_history = KEEP;
-- ① 뜻이 같은 값을 하나로 모읍니다
UPDATE merchants SET biz_type = 'SOLE' WHERE biz_type = 'INDIVIDUAL';
-- ② 앞으로는 두 값만 허용합니다
ALTER TABLE merchants
ADD CONSTRAINT `chk_merchant_biz_type` CHECK (`biz_type` IN ('CORP', 'SOLE'));
-- ③ 설명도 실제와 맞춥니다
ALTER TABLE merchants
MODIFY COLUMN `biz_type` varchar(10) NOT NULL
COMMENT 'CORP(법인)|SOLE(개인사업자) - 이 둘만. chk_merchant_biz_type 이 강제합니다';
-- ═══════════════════════════════════════════════════════════
-- V67__deposit_by_holder_name.sql
-- ═══════════════════════════════════════════════════════════
-- ─────────────────────────────────────────────────────────────
-- V67 : 충전 입금을 "입금코드"가 아니라 <b>본인 명의 예금주명</b>으로 확인합니다
--
-- 무엇이 문제였나요?
-- 지금까지는 입금자명 칸에 우리가 준 코드(NC12345678)를 적게 하고, 그 코드로 신청을 찾았습니다.
-- 그런데 코드만 맞으면 <b>누구 계좌에서 보냈든</b> 충전됐습니다.
-- 남의 계좌에서 보낸 돈이 내 지갑에 들어올 수 있다는 뜻이라, 자금 출처를 증명할 수 없습니다.
--
-- 어떻게 바꾸나요?
-- 회원은 이미 [본인인증 → 계좌 실명조회 → 1원 인증]을 거쳐 <b>본인 명의 계좌</b>를 등록해 둡니다.
-- 그 계좌의 예금주명으로 들어온 입금만 인정합니다. 코드는 이제 필요 없습니다.
--
-- 확인하는 것 네 가지가 모두 맞아야 자동 충전됩니다.
-- ① 예금주명 : 등록된 인증계좌의 예금주와 같아야 합니다(공백·기호 무시하고 비교).
-- ② 금액 : 신청 금액과 <b>정확히</b> 같아야 합니다.
-- ③ 입금 계좌 : 신청할 때 배정해 준 우리 입금계좌로 들어와야 합니다.
-- ④ 시각 : 신청 시각 <b>1분 전 ~ 10분 뒤</b> 사이에 들어온 것만 인정합니다.
--
-- ★ 같은 이름·같은 금액이 동시에 신청되면?
-- 구분할 방법이 없으므로 <b>서로 다른 입금계좌</b>를 배정합니다(③으로 구분).
-- 배정할 계좌가 남아 있지 않으면 신청을 거절하고 잠시 후 다시 시도하도록 안내합니다.
-- 아래 UNIQUE 가 "같은 이름·같은 금액·같은 계좌"가 동시에 대기하는 것을 데이터베이스에서 막습니다.
--
-- ★ 기다리는 시간은 5분입니다.
-- 5분이 지나면 신청은 자동 취소되고, 배정했던 계좌는 다음 사람에게 다시 쓸 수 있습니다.
-- (취소 뒤 10분까지 들어온 입금은 ④ 덕분에 여전히 충전됩니다 — 늦게 도착한 돈을 버리지 않기 위함)
-- ─────────────────────────────────────────────────────────────
-- ① 입금자(=예금주) 이름을 신청 시점에 못 박아 둡니다.
-- 나중에 회원이 계좌를 바꿔도, 이 신청은 "그때 그 이름"으로 판단해야 하기 때문입니다.
ALTER TABLE deposit_requests
ADD COLUMN `payer_name` varchar(60) NULL
COMMENT '신청 시점 인증계좌의 예금주명(스냅샷) - 이 이름으로 들어온 입금만 인정' AFTER `card_id`,
ADD COLUMN `payer_name_norm` varchar(60) NULL
COMMENT '예금주명 비교용(공백·기호 제거+소문자) - 은행마다 표기가 조금씩 달라 정규화해 맞춥니다' AFTER `payer_name`;
-- ② 지금 대기 중인 신청만 값을 갖는 칸 — 같은 이름·금액·계좌가 겹치지 못하게 합니다.
-- (현금영수증·이용제한에서 쓴 것과 같은 방식입니다)
ALTER TABLE deposit_requests
ADD COLUMN `alive_key` varchar(160) AS (
CASE WHEN status = 'PENDING'
THEN CONCAT(payer_name_norm, ':', amount, ':', deposit_account_id)
ELSE NULL END) VIRTUAL,
ADD UNIQUE KEY `ux_depreq_alive` (`alive_key`);
-- ③ 입금 통지를 찾을 때 쓰는 조회를 인덱스로 받칩니다.
-- "이 이름 + 이 금액 + 이 계좌로 대기 중인 신청이 있나?" 를 매 통지마다 묻습니다.
CREATE INDEX `ix_depreq_match` ON deposit_requests (`payer_name_norm`, `amount`, `deposit_account_id`, `status`);
-- ④ 입금코드는 더 쓰지 않습니다.
-- 남겨 두면 "코드로도 되나?" 하는 혼란과, 코드로 매칭하던 옛 경로가 되살아날 위험이 있습니다.
ALTER TABLE deposit_requests DROP INDEX `ux_depreq_code`;
ALTER TABLE deposit_requests DROP COLUMN `code`;
-- ⑤ 회사 출금계좌 — 회원에게 돈을 보낼 때 <b>돈이 빠져나가는 우리 계좌</b>입니다.
-- 지금까지 관리자에서 설정할 곳이 없어, 펌뱅킹 연동이 붙는 순간 어느 계좌에서 나갈지
-- 정할 방법이 없었습니다(입금받는 계좌만 있었습니다).
CREATE TABLE payout_accounts (
`id` bigint NOT NULL AUTO_INCREMENT,
`bank_code` varchar(3) NOT NULL COMMENT '은행 코드(banks.code) - 화면에는 이름으로 보여 줍니다',
`account_no` varchar(40) NOT NULL COMMENT '계좌번호',
`holder` varchar(60) NOT NULL COMMENT '예금주(우리 회사 이름)',
`label` varchar(40) NOT NULL COMMENT '구분용 이름표 - 예) 출금 전용, 정산 전용',
`sort_order` int NOT NULL DEFAULT 1 COMMENT '여러 개일 때 쓰는 차례(작을수록 먼저)',
`status` varchar(10) NOT NULL DEFAULT 'ACTIVE'
COMMENT 'ACTIVE=사용함 | HIDDEN=쓰지 않음(기록은 남김)',
`created_at` datetime NOT NULL,
-- 지금 쓰는 계좌일 때만 값을 갖는 칸 — 아래 UNIQUE 와 함께 <b>활성 계좌는 딱 하나</b>임을 보장합니다.
`alive_key` varchar(10) AS (CASE WHEN status = 'ACTIVE' THEN 'ONLY' ELSE NULL END) VIRTUAL,
PRIMARY KEY (`id`),
UNIQUE KEY `ux_payout_acct` (`bank_code`, `account_no`),
-- ★ 출금계좌는 여러 개 등록해 두되 실제로 쓰는 것은 하나입니다.
-- 입금계좌는 "누가 보낸 돈인지" 구분하려고 여러 개를 돌려 쓰지만,
-- 출금은 우리가 보내는 것이라 구분할 일이 없습니다.
-- 오히려 활성이 여러 개면 "어느 계좌에서 나갔지?" 하고 헷갈립니다.
UNIQUE KEY `ux_payout_active_only_one` (`alive_key`),
KEY `ix_payout_list` (`status`, `sort_order`),
CONSTRAINT `chk_payout_status` CHECK (`status` IN ('ACTIVE', 'HIDDEN'))
) COMMENT = '회사 출금계좌 - 회원에게 송금(출금·정산)할 때 돈이 나가는 우리 계좌';
-- ⑥ 입금계좌 설명도 실제와 맞춥니다(여러 개 두고 돌려 쓰는 용도가 되었습니다).
ALTER TABLE deposit_accounts
MODIFY COLUMN `label` varchar(40) NOT NULL
COMMENT '구분용 이름표 - 같은 이름·금액이 겹칠 때 서로 다른 계좌를 배정하므로 여러 개 등록해 두는 것이 좋습니다';
-- ═══════════════════════════════════════════════════════════
-- V68__deposit_wait_10min.sql
-- ═══════════════════════════════════════════════════════════
-- ─────────────────────────────────────────────────────────────
-- V68 : 충전 입금을 기다리는 시간을 5분 → 10분으로 바꿉니다
--
-- 무엇이 문제였나요?
-- V67 에서 두 시간을 다르게 잡았습니다.
-- · 자동 취소 : 신청 후 5분
-- · 입금 인정 : 신청 1분 전 ~ 10분 뒤
-- 그러면 화면에는 "취소되었습니다" 라고 떴는데, 그 뒤에 도착한 입금으로
-- 조용히 충전되는 구간(5~10분)이 생깁니다.
-- 사용자는 취소된 줄 알고 다시 신청해 <b>돈을 두 번 보내는</b> 사고로 이어집니다.
--
-- 어떻게 바꾸나요?
-- 기다리는 시간을 인정 시간과 <b>똑같이 10분</b>으로 맞춥니다.
-- "취소되면 정말로 끝" 이 되어 헷갈릴 일이 없습니다.
--
-- ★ 그래도 취소된 신청을 찾는 것은 그대로 둡니다.
-- 청소 배치가 30초마다 돌기 때문에 "9분 50초에 취소 → 10분에 입금 도착" 같은
-- 경계가 생깁니다. 이때 돈은 실제로 들어왔으므로 반드시 충전해야 합니다.
-- (들어온 돈을 버리면 안 됩니다 — 매칭 SQL 의 status <> 'MATCHED' 조건이 이 역할을 합니다)
--
-- ※ 실제 대기 시간은 서버 코드(DepositRequestService.EXPIRE_MINUTES)가 정합니다.
-- 이 마이그레이션은 <b>표의 설명을 실제와 맞추는</b> 것입니다
-- (설명이 5분인 채로 두면 나중에 보는 사람이 잘못 이해합니다).
-- ─────────────────────────────────────────────────────────────
ALTER TABLE deposit_requests
MODIFY COLUMN `expires_at` datetime NOT NULL
COMMENT '신청 만료 시각(신청 +10분) - 지나면 자동취소. 인정 시간(신청 -1분~+10분)과 같게 맞춤(V68)';
-- ═══════════════════════════════════════════════════════════
-- V69__fds_enable_all_rules.sql
-- ═══════════════════════════════════════════════════════════
-- ─────────────────────────────────────────────────────────────
-- V69 : FDS 이상거래 탐지 8종을 <b>모두</b> 켭니다
--
-- 무엇이 문제였나요?
-- 요구사항은 "FDS 룰 8종 자동 탐지"인데, 실제로 도는 것은 4종뿐이었습니다.
-- · SELF_PAYMENT / CANCEL_ABUSE / POST_CHANGE_WITHDRAW / NIGHT_LARGE
-- → 표에 줄만 있고 <b>찾아내는 코드가 아예 없었습니다</b>.
-- · ENUMERATION
-- → 코드는 있었지만 재료가 되는 거절 기록(reject_logs)을 <b>아무도 적지 않아</b>
-- 영원히 0건이었습니다.
-- 그런데 관리자 화면에서는 8종 모두 켜고 끌 수 있어서,
-- 운영자는 "켰으니 감시되고 있다" 고 믿게 됩니다. 실제로는 아무 일도 일어나지 않았습니다.
-- 돈을 다루는 서비스에서 <b>감시하는 줄 알았는데 안 하고 있는 상태</b>가 가장 위험합니다.
--
-- 어떻게 바꾸나요?
-- ① 빠졌던 4종의 탐지 코드를 만들었습니다(AdminFdsMapper.xml 14.5~14.8).
-- ② 거절 기록을 실제로 남기게 했습니다(RejectLogService) — ENUMERATION 이 살아납니다.
-- ③ 이제 8종이 전부 동작하므로, 기본값을 <b>켬(ALERT)</b> 으로 맞춥니다.
--
-- ※ ALERT 는 "경보만 쌓기"입니다. 거래를 막지(BLOCK) 않으므로 정상 손님이 피해를 보지 않습니다.
-- 운영하며 오탐이 많으면 관리자 화면에서 수치(params)를 올리거나 끄면 됩니다(배포 불필요).
-- ─────────────────────────────────────────────────────────────
UPDATE fds_rules
SET action = 'ALERT', enabled = 1, updated_at = NOW()
WHERE code IN ('SELF_PAYMENT', 'CANCEL_ABUSE', 'POST_CHANGE_WITHDRAW', 'NIGHT_LARGE');
-- ═══════════════════════════════════════════════════════════
-- V70__unify_collation.sql
-- ═══════════════════════════════════════════════════════════
-- ─────────────────────────────────────────────────────────────
-- V70 : 표 4개의 "글자 비교 방식(콜레이션)"을 나머지와 같게 맞춥니다
--
-- 무엇이 문제였나요?
-- 우리 표는 대부분 utf8mb4_unicode_ci 인데, 아래 4개만 utf8mb4_general_ci 였습니다.
-- deposit_accounts · used_challenges · account_closure_requests · deposit_requests
-- 비교 방식이 다른 두 표의 <b>글자 칸끼리 맞대면</b> MariaDB 가 아예 거부합니다.
--
-- 실제로 이런 오류가 납니다(로컬에서 재현했습니다):
-- SELECT ... FROM deposit_requests d JOIN deposit_notices n
-- ON d.payer_name_norm = n.parsed_name
-- → ERROR 1267 Illegal mix of collations
-- (utf8mb4_general_ci) 와 (utf8mb4_unicode_ci) 를 '=' 로 비교할 수 없음
--
-- 지금 쓰는 SQL 은 값을 물음표(#{})로 넣어 비교해서 터지지 않지만,
-- "입금통지와 충전신청을 이름으로 맞대는" 조인을 <b>한 줄만 추가해도 그 순간 깨집니다</b>.
-- 입금 자동 매칭을 다루는 표들이라 실제로 그런 조인을 쓰게 될 자리입니다.
--
-- 왜 지금 고치나요?
-- 새 서버를 만들 때 마이그레이션을 처음부터 돌리면 <b>이 어긋남까지 그대로 물려받습니다</b>.
-- 운영에 올리기 전에 한 번 맞춰 두면, 이후로는 어느 서버든 같은 상태가 됩니다.
--
-- 왜 utf8mb4_unicode_ci 로 맞추나요?
-- 나머지 66개 표가 이미 그것을 쓰고 있어, 소수(4개)를 다수에 맞추는 쪽이 안전합니다.
-- (반대로 하면 66개 표를 바꿔야 하고, 그중에는 파티션·이력관리 표가 섞여 있습니다)
--
-- ※ 이 4개 표는 파티션도, 이력관리(SYSTEM VERSIONED)도 아닙니다 — 실측 확인했습니다.
-- 그래서 CONVERT TO 한 문장으로 안전하게 바꿀 수 있습니다.
-- ※ 표에 든 글자는 바뀌지 않습니다. "비교하는 규칙"만 바뀝니다.
-- ─────────────────────────────────────────────────────────────
ALTER TABLE `deposit_accounts` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `used_challenges` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `account_closure_requests` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `deposit_requests` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- ═══════════════════════════════════════════════════════════
-- V71__pg_api_logs.sql
-- ═══════════════════════════════════════════════════════════
-- ─────────────────────────────────────────────────────────────
-- V71 : PG 연동 호출 기록표(pg_api_logs) 만들기
--
-- 무엇을 위한 표인가요?
-- 쇼핑몰(매장 서버)이 우리 PG API 를 부를 때마다, 무엇을 보냈고 우리가 무엇을 돌려줬는지
-- 원문 그대로 남깁니다.
--
-- 왜 필요한가요?
-- 지금은 연동 테스트가 "통과 시각" 세 개(test_create_ok_at 등)로만 남습니다.
-- 그래서 이런 것을 전혀 알 수 없습니다.
-- · 매장이 몇 번 시도했는지
-- · 무엇을 잘못 보내서 막혔는지 (서명 불일치·IP 미허용·시각 오차)
-- · 우리가 어떤 오류 문구를 돌려줬는지
-- 매장이 "연동이 안 된다"고 문의해도 관리자가 볼 자료가 없어, 매번 개발자가 서버 로그를 뒤져야 했습니다.
--
-- 무엇을 남기나요?
-- · 인증을 통과한 호출 : 요청·응답 원문 전부
-- · 인증에서 막힌 호출 : 막힌 이유와 함께 남깁니다
-- (이때는 매장이 누구인지 확신할 수 없어, 보내온 매장코드(client_id)와 접속 주소(ip)로 남깁니다)
--
-- 왜 external_api_logs 에 합치지 않았나요?
-- 그 표는 "우리가 바깥으로 건 전화"(펌뱅킹·인증사 등) 기록입니다.
-- 이 표는 반대로 "바깥에서 우리에게 걸려 온 전화"라 성격이 반대이고,
-- 매장별로 찾는 일이 잦아 따로 두는 편이 조회도 관리도 단순합니다.
--
-- 보관 방식
-- 달마다 칸을 나눠(파티션) 담고, 5년이 지난 칸은 통째로 버립니다 — 다른 기록표와 같은 방식입니다.
-- 한 번 남긴 기록은 고칠 수 없습니다(아래 트리거).
-- ─────────────────────────────────────────────────────────────
CREATE TABLE `pg_api_logs` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '실PK (id, created_at) 월 파티션',
-- 누가 불렀나
`merchant_id` bigint(20) DEFAULT NULL COMMENT '매장 번호 — 인증을 통과했을 때만 채워집니다',
`client_id` varchar(64) DEFAULT NULL COMMENT '요청이 들고 온 매장코드 — 인증에 실패해도 남습니다',
`mode` varchar(10) DEFAULT NULL COMMENT 'SANDBOX(테스트) | LIVE(실거래) — 인증 통과 시',
`ip` varchar(45) NOT NULL COMMENT '요청이 들어온 주소',
-- 무엇을 불렀나
`method` varchar(10) NOT NULL COMMENT 'GET|POST 등',
`path` varchar(200) NOT NULL COMMENT '주소 (예: /pg/payments)',
`query` varchar(500) DEFAULT NULL COMMENT '주소 뒤 물음표 뒤에 붙은 값',
-- 주고받은 원문
`request_body` text DEFAULT NULL COMMENT '요청 원문(JSON). 너무 길면 뒤를 자르고 표시를 남깁니다',
`response_body` text DEFAULT NULL COMMENT '응답 원문(JSON). 파일 같은 내용은 담지 않습니다',
-- 결과
`http_status` int(11) NOT NULL COMMENT '응답 코드 (200·400·401 등)',
`outcome` varchar(20) NOT NULL COMMENT 'OK(정상) | AUTH_FAIL(인증에서 막힘) | ERROR(처리 중 실패)',
`reject_reason` varchar(200) DEFAULT NULL COMMENT '막힌 이유 한 줄 (예: 서명이 일치하지 않습니다)',
`latency_ms` int(11) DEFAULT NULL COMMENT '처리에 걸린 시간(밀리초)',
`created_at` datetime NOT NULL COMMENT '요청 시각(KST)',
PRIMARY KEY (`id`, `created_at`),
KEY `ix_pgapi_merchant` (`merchant_id`, `created_at`),
KEY `ix_pgapi_outcome` (`outcome`, `created_at`),
KEY `ix_pgapi_status` (`http_status`, `created_at`),
KEY `ix_pgapi_client` (`client_id`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='쇼핑몰(매장 서버)이 부른 PG API 전건 기록 - 연동 테스트 추적·장애 판정 근거';
-- 달마다 칸 나누기 — 오래된 기록을 통째로 버릴 수 있게 합니다.
-- (다음 달 칸은 매월 25일 자동 확장 작업이 스스로 만들어 줍니다 — p_max 가 있으면 대상에 자동 포함)
ALTER TABLE `pg_api_logs` PARTITION BY RANGE (TO_DAYS(`created_at`)) (
PARTITION p202608 VALUES LESS THAN (TO_DAYS('2026-09-01')),
PARTITION p202609 VALUES LESS THAN (TO_DAYS('2026-10-01')),
PARTITION p_max VALUES LESS THAN MAXVALUE
);
-- 한 번 남긴 기록은 고칠 수 없습니다 — 고칠 수 있으면 증거가 아닙니다.
DELIMITER $$
CREATE TRIGGER trg_pgapi_no_update BEFORE UPDATE ON `pg_api_logs` FOR EACH ROW
BEGIN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'pg_api_logs is append-only'; END$$
DELIMITER ;
-- 보존 집행 작업에 이 표를 더합니다(5년).
-- V35 에서 만든 작업을 지우고 같은 내용 + 새 표 한 줄을 넣어 다시 만듭니다.
-- (이미 적용된 마이그레이션 파일은 고칠 수 없으므로 이렇게 이어 붙입니다)
DROP EVENT IF EXISTS ev_retention_drop;
DELIMITER $$
CREATE EVENT ev_retention_drop
ON SCHEDULE EVERY 1 MONTH STARTS (TIMESTAMP(DATE_FORMAT(CURDATE(), '%Y-%m-25')) + INTERVAL 4 HOUR)
DO BEGIN
CALL sp_drop_expired_partitions('notification_inbox', 2); -- 30일 보존(2개월 여유)
CALL sp_drop_expired_partitions('external_api_logs', 60); -- 5년
CALL sp_drop_expired_partitions('reject_logs', 60);
CALL sp_drop_expired_partitions('app_error_logs', 60);
CALL sp_drop_expired_partitions('login_histories', 60);
CALL sp_drop_expired_partitions('pii_access_logs', 60);
CALL sp_drop_expired_partitions('pg_api_logs', 60); -- V71 신설: 5년
END$$
DELIMITER ;
-- ═══════════════════════════════════════════════════════════
-- V72__pg_signature_test.sql
-- ═══════════════════════════════════════════════════════════
-- ─────────────────────────────────────────────────────────────
-- V72 : PG 연동 테스트에 "서명 검증" 항목 추가
--
-- 무엇이 문제였나요?
-- 지금까지 웹훅 테스트는 "매장이 200 으로 답하면 통과"였습니다.
-- 그런데 우리는 웹훅을 보낼 때 위조를 막으려고 서명(X-Nestpay-Signature)을 붙입니다.
-- 매장이 그 서명을 확인하지 않고 무조건 200 을 돌려줘도 통과했습니다.
--
-- 즉, 남이 매장 주소로 "결제 완료" 가짜 알림을 보내면 그대로 믿는 쇼핑몰이
-- 실거래로 넘어갈 수 있었습니다. 물건이 공짜로 나갑니다.
--
-- 어떻게 확인하나요?
-- 일부러 틀린 서명을 붙인 시험 알림을 한 번 보냅니다.
-- · 매장이 거절(4xx)하면 → 서명을 제대로 확인하고 있는 것 → 통과
-- · 매장이 200 을 주면 → 아무나 보낸 알림도 믿는 것 → 불통과
--
-- 이 칸에는 그 시험을 통과한 시각이 들어갑니다.
-- ─────────────────────────────────────────────────────────────
-- merchant_api_credentials 는 시스템 버저닝(변경 이력 자동 보존) 표라,
-- 컬럼을 더하려면 아래 설정으로 "이력까지 함께 바꾼다"고 알려 주어야 합니다(V12·V13·V30 과 같은 규약).
SET @@system_versioning_alter_history = KEEP;
ALTER TABLE `merchant_api_credentials`
ADD COLUMN `test_signature_ok_at` datetime DEFAULT NULL
COMMENT '체크리스트: 웹훅 서명 검증(틀린 서명을 4xx로 거절)'
AFTER `test_query_ok_at`;
-- ═══════════════════════════════════════════════════════════
-- V73__account_no_digits_only.sql
-- ═══════════════════════════════════════════════════════════
-- ─────────────────────────────────────────────────────────────
-- V73 — 회사 계좌번호를 "숫자만" 으로 정리합니다.
--
-- 왜 필요한가요?
-- 관리자가 계좌를 등록할 때 사람이 보기 좋으라고 하이픈(-)이나 빈칸을 넣곤 합니다.
-- 그런데 그대로 저장되면 세 가지 문제가 생깁니다.
-- ① 앱 충전 화면에 "183-910047-00915" 처럼 하이픈이 섞여 안내됩니다.
-- ② 같은 계좌인데 글자가 달라(하이픈 유무) 비교가 어긋납니다.
-- ③ 은행·발주사에 보낼 때는 숫자만 보내야 해서 보낼 때마다 다시 지워야 합니다.
--
-- 회원 계좌·매장 정산통장은 이미 숫자만 저장하고 있었고(BankVerify.digits),
-- 관리자 화면으로 넣는 이 두 표만 규칙에서 빠져 있었습니다. 규칙을 하나로 맞춥니다.
-- (앞으로 들어오는 값은 서버가 저장 전에 숫자만 남깁니다 —
-- AdminDepositAccountService · AdminPayoutAccountService)
--
-- 안전한가요?
-- 계좌번호에서 숫자가 아닌 글자를 지우기만 합니다. 숫자 자체는 하나도 바뀌지 않습니다.
-- 이미 숫자만 들어 있는 줄은 바뀌지 않습니다(WHERE 조건으로 걸러냄).
-- ─────────────────────────────────────────────────────────────
-- 입금통장(회사 수납 계좌) — 사용자에게 안내되는 계좌입니다.
UPDATE deposit_accounts
SET account_no = REGEXP_REPLACE(account_no, '[^0-9]', '')
WHERE account_no REGEXP '[^0-9]';
-- 출금계좌(예치금 계좌) — 회원 출금·매장 정산을 내보내는 계좌입니다.
UPDATE payout_accounts
SET account_no = REGEXP_REPLACE(account_no, '[^0-9]', '')
WHERE account_no REGEXP '[^0-9]';
-- ═══════════════════════════════════════════════════════════
-- V74__drop_deposit_account_settings.sql
-- ═══════════════════════════════════════════════════════════
-- ─────────────────────────────────────────────────────────────
-- V74 — "돈이 나가는 회사 계좌"를 한 곳에서만 관리하도록 정리합니다.
--
-- 무엇이 문제였나요?
-- 같은 계좌가 두 곳에 있었습니다.
-- ① 관리자 화면 [운영 → 출금통장] (payout_accounts 표)
-- ② 전역 설정 NPOPEN_DEPOSIT_BANK · NPOPEN_DEPOSIT_ACCT
--
-- 그런데 실제 송금은 ②만 보고 있었습니다. 운영자가 ① 화면에서 계좌를 바꿔도
-- 돈은 ②에 적힌 옛 계좌로 나갔습니다. 돈이 나가는 계좌라 가장 위험한 엇갈림입니다.
--
-- 어떻게 고쳤나요?
-- 송금 코드(NestpayPayoutClient)가 ① 화면의 '사용(ACTIVE)' 계좌 중
-- 순서가 가장 앞선 것을 읽도록 바꿨습니다. 그래서 ② 두 줄은 이제 아무도 읽지 않습니다.
-- 읽지 않는 값이 화면에 남아 있으면 "이걸 고치면 되겠지" 하고 잘못 만지게 되므로 지웁니다.
--
-- 주의
-- 지우기 전에 ① 에 계좌가 등록되어 있어야 합니다. 비어 있으면 출금·정산 제출이
-- "돈을 보낼 회사 계좌가 없습니다" 로 실패합니다(돈이 잘못 나가는 것보다 안전한 실패).
-- ─────────────────────────────────────────────────────────────
DELETE FROM global_settings WHERE skey IN ('NPOPEN_DEPOSIT_BANK', 'NPOPEN_DEPOSIT_ACCT');
-- ═══════════════════════════════════════════════════════════
-- V75__deposit_request_account_digits.sql
-- ═══════════════════════════════════════════════════════════
-- ─────────────────────────────────────────────────────────────
-- V75 — 충전신청에 적어 둔 "그때 안내한 계좌번호"(스냅샷)도 숫자만으로 맞춥니다.
--
-- V73 에서 회사 계좌표(deposit_accounts·payout_accounts)를 정리했는데,
-- 충전신청(deposit_requests)에는 신청 당시 안내한 계좌번호를 따로 복사해 두고 있었습니다.
-- 이 값이 회원앱 충전 화면에 그대로 보이므로, 옛 신청을 열면 하이픈이 남아 보입니다.
--
-- 숫자는 하나도 바뀌지 않고 표기만 통일하는 것이라, 어느 계좌였는지는 그대로 남습니다.
-- (앞으로 만들어지는 신청은 회사 계좌표에서 숫자만 복사해 오므로 이미 깨끗합니다)
-- ─────────────────────────────────────────────────────────────
UPDATE deposit_requests
SET account_no = REGEXP_REPLACE(account_no, '[^0-9]', '')
WHERE account_no REGEXP '[^0-9]';