design-db

B2B 파트너 연동 DB 설계 (design-db)

Background

• 근거 TDD: /Users/biuea/Desktop/dpdpdndn/프로젝트/스포츠앱/B2B 파트너 연동/TDD.md
• 대상 레포: /Users/biuea/sports-application (backend/src/main/resources/db/migration/)
• 이 문서는 설계 산출물이며 DDL 전문 작성·실행은 private-mysql-implementer가 담당한다.

실측한 기존 마이그레이션 컨벤션 (SSOT)

항목	실측 결과	근거
파일 명명	순차 정수 V{정수}__{snake_case}.sql — private-db-schema-convention의 타임스탬프 형식 아님	레포 db/migration/ 전수
최신 버전	V37 (V37__fix_cart_items_active_unique.sql)	실측
신규 시작 번호	V38~	—

번호 배정 조정 방침 (공통, 잠정): 현재 배정 ② partner=V38/V39/V40 · ③ limited_drops=V41 · ⑥ alerts=V42는 세 과제가 같은 db/migration/ 네임스페이스를 공유해 발생한 잠정 배정이다. 먼저 머지되는 쪽이 V38부터 순차 점유하고, 나중 쪽은 origin/dev 기준 최신 번호로 재배정한다. 구현 시 origin/dev 기준 워크트리에서 최신 버전을 실측 후 확정한다.

| COMMENT | 전 컬럼·테이블 COMMENT 필수 | V20__create_mcp_tokens.sql, V21__create_mcp_audit_logs.sql | | 인덱스 명명 | INDEX idx_{table}_{cols} / UNIQUE KEY uq_{table}_{cols} + 인덱스 COMMENT | V20, V21 | | 시간 컬럼 | DATETIME(6) | 전수 | | BOOLEAN | TINYINT(1) NOT NULL DEFAULT 0 | V20:13-14 | | status | VARCHAR(32) (ENUM 금지) | V20:12 | | FK | 물리 FK 없음, 일반 컬럼(user_id, partner_id) | 전수 | | 감사 로그 | append-only, created_*만 (updated/deleted 없음), 90일 보관 | V21:1-5 | | ip/UA 길이 | ip_addr VARCHAR(45), client_user_agent VARCHAR(500) | V21:15-16 | | 낙관락 | version BIGINT NOT NULL DEFAULT 0 | V20:17 |

신규 3개 테이블은 전부 가산(additive) — 기존 테이블·데이터 무영향. CREATE TABLE은 신규 객체 생성이므로 기존 테이블에 락을 유발하지 않는다.

저장소 선택

데이터 단위	저장소	채택 사유
partner (신원·상태)	MySQL	관계형·트랜잭션·낮은 볼륨. 기본 저장소
partner_api_key (자격증명 라이프사이클)	MySQL	상태 전이·유니크 제약(key_hash)·트랜잭션 필요
partner_audit_log (감사)	MySQL	append-only 정형 로그. 기존 mcp_audit_logs 선례와 동일 패턴. 조회는 partner_id+기간 범위

• MongoDB 미채택: 세 단위 모두 정형 스키마·유니크 제약·관계형 조회(파트너별 감사)로 private-mongodb-convention의 문서형/스키마리스 채택 근거(가변 스키마·중첩 집계·샤딩 규모)에 해당하지 않는다. 전부 MySQL.

테이블 정의

1. partner — 협력사 신원 (V38)

컬럼	타입	NULL	기본값	COMMENT / 근거
id	BIGINT AUTO_INCREMENT	NOT NULL	—	PK
name	VARCHAR(255)	NOT NULL	—	협력사 표시명 (운영자 입력)
status	VARCHAR(32)	NOT NULL	—	상태: ACTIVE | SUSPENDED (ENUM 금지 → VARCHAR)
linked_user_id	BIGINT	NOT NULL	—	연동 전용 User id (users.id, 물리 FK 없음). owner_id 네임스페이스로 해석
version	BIGINT	NOT NULL	0	낙관락(@Version) — 동시 상태 전이 lost-update 방지 (mcp_tokens 선례)
created_at	DATETIME(6)	NOT NULL	—	생성 시각 (UTC)
created_by	BIGINT	NULL	—	생성자 user_id (ADMIN)
updated_at	DATETIME(6)	NOT NULL	—	마지막 수정 시각
updated_by	BIGINT	NULL	—	마지막 수정자 user_id

• soft-delete 미도입: Partner 라이프사이클은 status(ACTIVE/SUSPENDED)로 완결. 삭제는 본 과제 범위 밖(TDD Non-Goals). deleted_at 없음 — 단순함 우선.

2. partner_api_key — 인증 키 (V39)

컬럼	타입	NULL	기본값	COMMENT / 근거
id	BIGINT AUTO_INCREMENT	NOT NULL	—	PK. partner_<id>_<random>의 <id>가 이 값 (필터가 parseKeyId로 추출)
partner_id	BIGINT	NOT NULL	—	소유 파트너 (partner.id, 물리 FK 없음)
key_hash	VARCHAR(255)	NOT NULL	—	BCrypt 해시 (평문은 발급 시 1회 노출). Stripe 패턴
status	VARCHAR(32)	NOT NULL	—	상태: ACTIVE | REVOKED (ENUM 금지 → VARCHAR)
revoked_at	DATETIME(6)	NULL	—	폐기·재발급 시각 (NULL=활성)
last_used_at	DATETIME(6)	NULL	—	마지막 인증 성공 시각 (필터가 갱신)
created_at	DATETIME(6)	NOT NULL	—	발급 시각 (UTC)
created_by	BIGINT	NULL	—	발급자 user_id (ADMIN)

• append-only에 가까움. 상태 변경은 status/revoked_at만 — updated_* 생략(폐기 시각이 유일한 변경 이벤트, revoked_at으로 기록).
• last_used_at 갱신은 쓰기 발생: 인증 성공마다 1회 UPDATE. 볼륨 ≈ 파트너 요청 수(수천/일)로 무시 가능.

3. partner_audit_log — 파트너 활동 감사 (V40, append-only)

컬럼	타입	NULL	COMMENT / 근거
id	BIGINT AUTO_INCREMENT NOT NULL	—	PK
partner_id	BIGINT NOT NULL	—	요청 파트너 (partner.id)
user_id	BIGINT NOT NULL	—	연동 User id (owner로 귀속된 계정)
http_method	VARCHAR(10) NOT NULL	—	GET/POST/PATCH/PUT/DELETE
request_path	VARCHAR(512) NOT NULL	—	요청 경로 (쿼리스트링 제외)
target_resource	VARCHAR(255) NULL	—	대상 리소스 식별자 (예: productId) — 파싱 실패 시 NULL
status_code	INT NOT NULL	—	응답 HTTP 상태 코드 (201/401/403/404 등)
latency_ms	INT NOT NULL	—	처리 소요 시간 (밀리초)
ip_addr	VARCHAR(45) NULL	—	클라이언트 IP (IPv4/IPv6). mcp 선례 길이
client_user_agent	VARCHAR(500) NULL	—	User-Agent 문자열. mcp 선례 길이
called_at	DATETIME(6) NOT NULL	—	요청 시각 (UTC, 조회·정렬 기준)
created_at	DATETIME(6) NOT NULL	—	레코드 적재 시각 (append-only)

• mcp_audit_logs와 테이블 미공유 (도메인 격리, ADR-001 / TDD FR-8·방안 F 미채택). 컬럼 구성은 mcp 선례를 준용.
• append-only: updated_*/deleted_* 없음.

쿼리 패턴 → 인덱스 매핑

partner

#	쿼리 패턴	WHERE / 조건	인덱스	근거
P1	findById(partnerId) (필터가 api_key → partner_id로 조회)	PK	(PK)	클러스터 PK
P2	findByLinkedUserId(linkedUserId) (운영자 역조회)	linked_user_id = ?	UNIQUE KEY uq_partner_linked_user_id (linked_user_id)	파트너 1개 = 연동 User 1개(1:1). UNIQUE가 무결성(중복 연동 방지) + 조회를 동시 충족

• linked_user_id를 UNIQUE로 설계한 근거: 도메인상 한 연동 User는 정확히 한 Partner에 귀속. 일반 인덱스보다 무결성 보장이 크고, 카디널리티는 사실상 유니크라 선두 인덱스로 적합.

partner_api_key

#	쿼리 패턴	WHERE / 조건	인덱스	근거
K1	findById(keyId) (필터 인증 진입점: partner_<keyId>_<random> 파싱 후 PK 조회)	PK	(PK)	인증은 hash 스캔이 아니라 PK로 1건 조회 후 BCrypt matches — 스캔 없음
K2	findActiveByPartnerId(partnerId) (재발급 시 구 ACTIVE 키 조회)	partner_id = ? AND status = 'ACTIVE'	INDEX idx_partner_api_key_partner_id_status (partner_id, status)	복합 컬럼 순서 근거: partner_id(고카디널리티, 등가 조건) 선두 → status(저카디널리티) 후행. partner_id 단독으로도 파트너당 키 수가 적어 선택적, status는 ACTIVE 1건 확정에 기여
K3	key_hash 유일성 보장	(제약)	UNIQUE KEY uq_partner_api_key_key_hash (key_hash)	해시 재사용·충돌 방지. 조회용 아님(인증은 K1 PK 경유) — 무결성 제약

partner_audit_log

#	쿼리 패턴	WHERE / 조건	인덱스	근거
A1	findBy(partnerId, from, to, pageable) (운영자 감사 조회)	partner_id = ? AND called_at BETWEEN ? AND ? ORDER BY called_at DESC	INDEX idx_partner_audit_log_partner_id_called_at (partner_id, called_at)	ESR 순서: Equality(partner_id) → Range/Sort(called_at). partner_id 등가로 좁힌 뒤 called_at 범위 스캔 + 정렬을 인덱스 순서로 커버
A2	보관 정책 만료 삭제	called_at < :threshold	INDEX idx_partner_audit_log_called_at (called_at)	90일 경과 row 범위 스캔·삭제 (A1 복합은 선두가 partner_id라 전역 called_at 스캔에 부적합). mcp_audit_logs 선례 동일

• A1·A2 외 인덱스는 만들지 않는다 (쓰기 비용). status_code/user_id별 집계는 현재 요구 쿼리 없음 → Observability는 애플리케이션 지표(태그 partner)로 수집, DB 인덱스 미생성.

용량 추정 · 보존 정책

테이블	예상 행 수	증가율	1년 후	보존 정책
partner	수십~수백	파트너 온보딩 시 +1	< 1,000 행	영구 (무한 성장 아님)
partner_api_key	파트너당 1~수 개	재발급 시 +1 (구 키 REVOKED로 잔존)	< 5,000 행	영구. REVOKED 키는 감사·추적용 잔존
partner_audit_log	모든 파트너 요청 적재	등록 1,000건/일 + 조회·수정 포함 ≈ 3,000~5,000행/일	≈ 1.1M~1.8M 행 / ~1.5GB (row ≈ 1KB, UA 500 + path 512)	90일 보관 — mcp_audit_logs와 동일. hard-delete 스케줄러는 별도 티켓(본 설계 범위 밖), A2 인덱스로 범위 삭제 지원

• 무한 성장 테이블은 partner_audit_log 하나 — 90일 보관으로 상한. 파티셔닝은 현재 규모(~1.5GB/년)에 과함 → 미채택(단순함 우선). 볼륨 증가 시 called_at 월 단위 RANGE 파티션 재검토.

무중단 마이그레이션 (expand-contract)

세 테이블 모두 신규 가산이므로 기존 데이터 백필이 없다. NOT NULL 컬럼도 신규 테이블이라 3단계 분리 불필요(백필 대상 데이터 없음). expand-contract의 contract 단계는 롤백 시 DROP뿐이다.

단계	작업	배포 순서	락 영향	롤백 지점
1. DB expand	V38 partner → V39 partner_api_key → V40 partner_audit_log 순차 생성	스키마 먼저	CREATE TABLE = 신규 객체, 기존 테이블 무락. 온라인 안전	데이터 없을 때 역방향 DROP TABLE partner_audit_log; DROP TABLE partner_api_key; DROP TABLE partner; (생성 역순)
2. 코드 배포 (플래그 OFF)	partner.auth.enabled=false. 필터 휴면, 관리 API만 배포	스키마 후 코드	없음	코드 롤백 — 테이블 잔존(무해)
3. 플래그 ON	partner.auth.enabled=true	코드 후 설정	없음	partner.auth.enabled=false로 즉시 비활성 (B2C/JWT/mcp 무영향)

• 세 테이블 간 순서: partner_api_key.partner_id·partner_audit_log.partner_id가 partner를 논리 참조 → partner를 먼저 생성(물리 FK는 없으나 롤백·의미 순서 정렬). 롤백은 생성 역순.
• 배포 순서 근거: 테이블이 없으면 코드가 부팅 시 스키마 검증에 실패할 수 있으므로 스키마 먼저. 이후 코드는 플래그 OFF로 배포해 인증 경로를 게이트.

자가 점검 (TDD 도메인 모델 ↔ 컬럼 대응)

TDD 도메인 필드	대응 컬럼	확인
Partner(id, name, status, linkedUserId)	partner.id/name/status/linked_user_id	OK
PartnerApiKey(id, partnerId, keyHash, status, revokedAt, lastUsedAt)	partner_api_key 전 컬럼	OK
PartnerAuditLog(partnerId, userId, method, path, targetResource, statusCode, latencyMs, ip, ua, calledAt)	partner_audit_log 전 컬럼	OK
authenticate(keyId, plainKey) — keyId=PK, plainKey→BCrypt matches(key_hash)	K1(PK) + key_hash	OK
findActiveByPartnerId	K2 복합 인덱스	OK

• 컨벤션 위반 점검: FK 없음 OK / ENUM 없음(status VARCHAR) OK / JSON 없음 OK / BOOLEAN 없음 OK / DATETIME(6) OK / 전 컬럼·테이블 COMMENT 대상(implementer 작성) OK / PK=id, 참조={entity}_id OK.

Document History

날짜	변경 내용
2026-07-03	최초 작성 — 기존 컨벤션 실측(순차 정수 최신 V37), partner/partner_api_key/partner_audit_log 3테이블 정의, 쿼리→인덱스 매핑(ESR), 용량·90일 보관, expand-contract 배포 순서