본 문서는 채팅 고도화(실시간 전송·읽음 커서·게스트 초대·커뮤니티 도메인·contextType 확장 메커니즘)를 지탱하는 스키마 설계를 확정합니다. DDL 전문 작성·실행은 private-mysql-implementer가 BE-01에서 수행하며, 본 문서가 그 입력(테이블 정의·인덱스 근거·마이그레이션 순서)입니다.
AS-IS (실제 마이그레이션 재구성)
채팅 관련 현재 스키마는 V8__create_messages_rooms.sql + V11__add_last_message_at_to_rooms.sql로 재구성됩니다. 최신 마이그레이션은 V37이므로 신규는 V38부터입니다.
메시지 본문도 기존 messages 테이블(TEXT)을 그대로 사용 — 관계형 커서 조회(id 기준)와 소프트 삭제 정책을 유지하므로 이관 불필요합니다.
실시간 팬아웃 상태(세션 레지스트리)는 in-memory(Simple Broker)로 DB 비영속 — 저장소 대상 아님.
Detail Design — 테이블 정의
1. rooms (기존 확장 — additive)
컬럼
타입
NULL
DEFAULT
COMMENT
근거
context_type
VARCHAR(30)
NULL
—
연결된 외부 도메인 유형 (COMMUNITY / GOODS_PRODUCT). DIRECT/GROUP 순수 방은 NULL
FR-16/17/18, ENUM 금지→VARCHAR. 기존 방 호환 위해 nullable
context_id
BIGINT
NULL
—
연결된 외부 엔티티 id (community_id 또는 product_id). context_type이 NULL이면 NULL
FR-16. FK 금지→일반 BIGINT
기존 DIRECT/GROUP 방은 두 컬럼 모두 NULL 유지 → additive, 기존 데이터 무충돌.
context_type/context_id는 항상 쌍으로 채워짐(둘 다 NULL 또는 둘 다 값). 정합은 애플리케이션 레벨(Room.createForContext)에서 보장.
2. room_participants (기존 확장 — additive)
컬럼
타입
NULL
DEFAULT
COMMENT
근거
participant_type
VARCHAR(20)
NOT NULL
’MEMBER’
참여 유형 (MEMBER / GUEST)
FR-11~14, ENUM 금지→VARCHAR. 기존 참여자는 정회원이므로 DEFAULT ‘MEMBER’로 백필
can_speak
TINYINT(1)
NOT NULL
1
발화 권한 (1=발화 가능, 0=읽기 전용 게스트)
FR-13, BOOLEAN 금지→TINYINT(1). 기존 참여자는 발화 가능이므로 DEFAULT 1
expires_at
DATETIME(6)
NULL
—
게스트 참여 만료 시각. MEMBER는 NULL(무기한)
FR-14, 날짜 DATETIME(6)
last_read_message_id
BIGINT
NULL
—
마지막으로 읽은 messages.id (읽음 커서). 미열람 시 NULL
FR-7/9, 안읽은 수 계산 기준. FK 금지→일반 BIGINT
participant_type/can_speak는 NOT NULL이지만 의미 있는 비즈니스 기본값(기존 참여자 = 정회원 = 발화 가능)이 존재하므로 DEFAULT를 영구 유지하는 단일 ALTER로 백필합니다. 컨벤션의 “DEFAULT 임시 부여 → 채우기 → DEFAULT 제거” 3단계는 유효 기본값이 없을 때의 절차이므로 여기선 불필요 — DEFAULT를 남겨 신규 MEMBER INSERT 경로도 안전하게 유지합니다.
last_read_message_id는 messages.id를 논리 참조하되 FK 금지 규칙에 따라 일반 BIGINT. 정합(방 내 메시지인지)은 애플리케이션에서 markReadUpTo forward-only로 보장.
3. room_invitations (신규)
컬럼
타입
NULL
DEFAULT
COMMENT
근거
id
BIGINT
NOT NULL
AUTO_INCREMENT
PK
PK=id 통일
room_id
BIGINT
NOT NULL
—
초대 대상 방 id
조회 키(멱등·목록). FK 금지
inviter_user_id
BIGINT
NOT NULL
—
초대한 사용자 id (방장)
FR-11
invitee_user_id
BIGINT
NOT NULL
—
초대받은 정회원 id
FR-11, 멱등 조회 키
status
VARCHAR(20)
NOT NULL
’PENDING’
초대 상태 (PENDING/ACCEPTED/REJECTED/REVOKED/EXPIRED)
상태 전이표(TDD). ENUM 금지→VARCHAR
can_speak
TINYINT(1)
NOT NULL
1
수락 시 부여할 발화 권한
FR-13, BOOLEAN→TINYINT(1)
expires_at
DATETIME(6)
NOT NULL
—
초대로 부여될 게스트 참여 만료 시각(초대 시 expiresInDays로 산정)
FR-14, DATETIME(6)
responded_at
DATETIME(6)
NULL
—
수락/거절/철회/만료 처리 시각. PENDING이면 NULL
상태 전이 감사
created_at/by, updated_at/by, deleted_at/by
(audit 6종)
—
—
JpaAuditingBase 표준
기존 관례
상태 값은 TDD 상태 전이표의 5종. status는 RoomInvitation.accept()/reject()/revoke()/expire()가 캡슐화 전이, terminal 재전이는 애플리케이션에서 거부.
4. communities (신규)
컬럼
타입
NULL
DEFAULT
COMMENT
근거
id
BIGINT
NOT NULL
AUTO_INCREMENT
PK
PK=id
name
VARCHAR(100)
NOT NULL
—
커뮤니티 이름
FR-1
description
VARCHAR(500)
NULL
—
커뮤니티 설명
FR-1. 선택 입력
visibility
VARCHAR(20)
NOT NULL
—
공개 여부 (PUBLIC/PRIVATE)
FR-1/2, ENUM 금지→VARCHAR
sport_category
VARCHAR(30)
NOT NULL
—
스포츠 종목 카테고리
FR-1, ENUM 금지→VARCHAR
host_user_id
BIGINT
NOT NULL
—
현재 방장 사용자 id (위임 시 갱신)
FR-3, FK 금지
created_at/by, updated_at/by, deleted_at/by
(audit 6종)
—
—
표준
기존 관례
host_user_id는 방장 위임(transferHostTo) 시 UPDATE. 별도 조회 패턴(방장별 커뮤니티 목록)이 인터페이스 시그니처에 없으므로 인덱스 미부여(쓰기 비용 회피).
5. community_members (신규)
컬럼
타입
NULL
DEFAULT
COMMENT
근거
id
BIGINT
NOT NULL
AUTO_INCREMENT
PK
PK=id
community_id
BIGINT
NOT NULL
—
소속 커뮤니티 id
조회 키. FK 금지
user_id
BIGINT
NOT NULL
—
멤버 사용자 id
조회 키
role
VARCHAR(20)
NOT NULL
’MEMBER’
역할 (HOST/MEMBER)
FR-3, ENUM 금지→VARCHAR
status
VARCHAR(20)
NOT NULL
—
멤버십 상태 (PENDING_APPROVAL/ACTIVE/LEFT/KICKED)
FR-2/3/5 상태 전이표. ENUM 금지→VARCHAR
joined_at
DATETIME(6)
NULL
—
ACTIVE 전이 시각(승인·즉시가입). PENDING이면 NULL
FR-2
created_at/by, updated_at/by, deleted_at/by
(audit 6종)
—
—
표준
기존 관례
쿼리 패턴 → 인덱스 매핑
TDD “인터페이스 시그니처”를 근거로, 각 인덱스가 어떤 쿼리를 위한 것인지 1:1 매핑합니다. 근거 없는 인덱스는 만들지 않습니다.
equality 2개(participant_type, deleted_at IS NULL)를 선두, range(expires_at)를 말미 — equality→range 원칙. TDD 제안 순서 (participant_type, expires_at, deleted_at)를 조정: range 컬럼(expires_at) 뒤의 deleted_at은 인덱스로 걸러지지 않으므로 deleted_at을 range 앞으로 이동해 두 equality를 앞에 배치(선두 저카디널리티지만 GUEST 행만 좁혀 배치 비용 회피 목적이 명확)
P2
findActiveByUserId(userId)
user_id=? AND deleted_at IS NULL (+ 앱단 만료·타입 필터)
기존 idx_room_participants_user_id_deleted_at (user_id, deleted_at) 재사용
user_id(고카디널리티) equality + deleted_at equality로 충분. 신규 인덱스 불필요(쓰기 비용 회피)
room_id=? AND deleted_at IS NULL AND id > afterMessageId ORDER BY id ASC LIMIT ?
동일 idx_messages_room_id_deleted_at 재사용
M1과 동일 구조(암묵 PK id로 range+정렬 커버). 신규 인덱스 불필요
messages는 신규 인덱스 0개 — 기존 (room_id, deleted_at) 인덱스의 암묵 PK 부가 특성으로 커서(id) 쿼리를 커버합니다. deleted_at을 항상 IS NULL로 고정 필터하는 두 쿼리 특성이 전제이므로, 만약 삭제 메시지 포함 조회가 생기면 그때 (room_id, id) 전용 인덱스를 재검토합니다.
room_invitations
#
쿼리 (근거 시그니처)
조건
인덱스
컬럼 순서 근거
I1
findPendingBy(roomId, inviteeUserId) (멱등 초대)
room_id=? AND invitee_user_id=? AND status=‘PENDING’ AND deleted_at IS NULL
visibility·deleted_at equality로 공개 커뮤니티 집합을 좁힘. name LIKE ‘%kw%‘는 선행 와일드카드라 인덱스 불가 → 좁혀진 집합에서 필터. visibility 저카디널리티지만 PUBLIC만 스캔 대상이 되어 PRIVATE 제외 효과가 명확
C2
findById(id)
id=?
PK
불필요
community_members
#
쿼리 (근거 시그니처)
조건
인덱스
컬럼 순서 근거
CM1
findActiveBy(communityId, userId)
community_id=? AND user_id=? AND deleted_at IS NULL (+ status=‘ACTIVE’)
community_id equality 선두 + status equality로 ACTIVE 멤버만. CM1 유니크는 (community_id,user_id) 순이라 status 필터 미커버 → 별도 필요
용량 추정 · 보존 정책
개인 프로젝트·로컬 docker 단일 인스턴스 규모(PRD 300 동시 세션 가정) 기준 초안입니다.
테이블
1행 크기(대략)
예상 행 수(1년)
증가율
1년 후 크기
보존 정책
rooms (+2컬럼)
~120B
수백~수천
커뮤니티·거래 방 생성 시
< 1MB
소프트 삭제 유지, 만료 삭제 없음
room_participants (+4컬럼)
~120B
수천~수만
방당 수명당 참여자
수 MB
소프트 삭제. 게스트 방출은 soft-delete(읽은 이력 유지, FR-14)
messages (기존)
본문 가변(TEXT)
무한 성장
실시간 활성화로 증가 가속
수십~수백 MB
PRD 결정: 소프트 삭제 유지, 무기한 보존(별도 만료 삭제 없음)
room_invitations
~120B
수백
초대 발송 시
< 1MB
소프트 삭제. 만료 초대는 status=EXPIRED 전이(행 유지)
communities
~200B
수십~수백
커뮤니티 개설 시
< 1MB
소프트 삭제
community_members
~120B
수천
가입 시
수 MB
소프트 삭제
무한 성장 테이블은 messages뿐입니다. PRD/NFR가 “메시지 무기한 보존(별도 만료 삭제 없음)“으로 확정했고, Open Questions에 스토리지 증가 대응을 미결로 남겼습니다. 본 설계는 파티셔닝/아카이빙을 선반영하지 않습니다(개인 프로젝트 규모에 과함 — 단순함 우선). 향후 스토리지 임계 도달 시 room_id·created_at 기반 월 단위 아카이빙 테이블 분리를 재검토합니다.
게스트 방출·초대 만료는 물리 삭제가 아닌 상태 전이/soft-delete로 이력을 보존합니다(FR-14 “읽은 이력 유지”).
로그성 대량 적재 데이터가 없어 TTL(MySQL 이벤트 스케줄러) 도입은 불필요합니다.
무중단 마이그레이션 (expand-contract)
배포 순서: 스키마 먼저 → 코드 (spring.jpa.hibernate.ddl-auto=validate이므로 컬럼이 코드보다 먼저 존재해야 함). 이번 변경은 전부 expand(additive) 단계이며 contract(제거) 단계가 없습니다.
단계
작업
락 영향
롤백 지점
S1
V38: rooms ADD context_type, context_id (둘 다 NULL) + idx_rooms_context
DEFAULT 있는 NOT NULL·nullable ADD COLUMN = MySQL 8.0.12+ INSTANT(기존 행 즉시 백필, 락 없음). 인덱스는 INPLACE/LOCK=NONE
역방향 DDL: DROP idx_rp_guest_expires, DROP 4개 컬럼
S3
V40: CREATE room_invitations (인덱스 포함)
신규 테이블 = 락 무관
DROP TABLE room_invitations
S4
V41: CREATE communities
신규 테이블 = 락 무관
DROP TABLE communities
S5
V42: CREATE community_members (UNIQUE·인덱스 포함)
신규 테이블 = 락 무관
DROP TABLE community_members
C1
코드 배포 (피처 플래그 OFF: chat.realtime.enabled=false, chat.community.enabled=false)
—
플래그 OFF 유지 → 기존 REST 채팅만 동작
C2
점진 활성화 (플래그 ON)
—
플래그 OFF로 즉시 비활성 (스키마 롤백 불필요)
파일 분리 판단: S1S5를 개별 마이그레이션(V38V42)으로 분리 권장 — 테이블별 롤백 지점을 독립시키고 실패 격리. 단일 파일로 묶어도 무방하나, 실패 시 부분 롤백이 어려움. (BE-01 티켓은 “마이그레이션 파일 산출 대표”이므로 파일 개수는 implementer 재량 — 분리 권장을 명시)
락 없음 근거: 모든 ALTER는 MySQL 8.0 INSTANT(컬럼 추가) 또는 INPLACE/LOCK=NONE(인덱스). 온라인 DDL로 서비스 중단·쓰기 블로킹 없음. NOT NULL 컬럼도 유효 DEFAULT가 있어 백필이 즉시 이뤄지므로 3단계 분리 불요.
contract 없음: expand-only라 기존 코드는 신규 컬럼/테이블을 참조하지 않아, 코드 롤백만으로 안전합니다. 스키마 롤백(역방향 DDL)은 불가피할 때만 위 롤백 지점을 역순 적용.
ERD (Mermaid erDiagram)
erDiagram
COMMUNITIES ||--o{ COMMUNITY_MEMBERS : has
ROOMS ||--o{ ROOM_PARTICIPANTS : has
ROOMS ||--o{ MESSAGES : contains
ROOMS ||--o{ ROOM_INVITATIONS : has
ROOMS {
bigint id PK
varchar type
varchar name
datetime last_message_at
varchar context_type "NULL COMMUNITY|GOODS_PRODUCT"
bigint context_id "NULL"
}
ROOM_PARTICIPANTS {
bigint id PK
bigint room_id
bigint user_id
datetime joined_at
varchar participant_type "MEMBER|GUEST DEFAULT MEMBER"
tinyint can_speak "DEFAULT 1"
datetime expires_at "NULL"
bigint last_read_message_id "NULL"
}
MESSAGES {
bigint id PK
bigint room_id
bigint user_id
text content
}
ROOM_INVITATIONS {
bigint id PK
bigint room_id
bigint inviter_user_id
bigint invitee_user_id
varchar status "PENDING|ACCEPTED|REJECTED|REVOKED|EXPIRED"
tinyint can_speak
datetime expires_at
datetime responded_at "NULL"
}
COMMUNITIES {
bigint id PK
varchar name
varchar description "NULL"
varchar visibility "PUBLIC|PRIVATE"
varchar sport_category
bigint host_user_id
}
COMMUNITY_MEMBERS {
bigint id PK
bigint community_id
bigint user_id
varchar role "HOST|MEMBER"
varchar status "PENDING_APPROVAL|ACTIVE|LEFT|KICKED"
datetime joined_at "NULL"
}
FK 컬럼 금지 규칙상 관계는 논리적(ID 참조)입니다. ERD의 관계선은 도메인 의미이며 실제 FK 제약을 생성하지 않습니다.
컨벤션 점검: FK 컬럼 0 · ENUM 0(전부 VARCHAR) · JSON 0 · BOOLEAN 0(can_speak=TINYINT(1)) · 날짜 전부 DATETIME(6) · PK=id · 참조 {entity}_id · 신규 컬럼/테이블 COMMENT 필수 명시 — 위반 없음.
Document History
날짜
변경 내용
2026-07-04
최초 작성 — BE TDD 인터페이스 시그니처 기반 테이블·인덱스 설계 확정. rooms/room_participants expand + room_invitations/communities/community_members 신규. idx_rp_guest_expires 컬럼 순서를 equality→range 원칙으로 조정, messages 신규 인덱스 0(암묵 PK 부가 활용). expand-only 무중단 순서·INSTANT 락 영향 명시.