흩어진 문자와 통화를 한 고객 아래로 모으기 — Unified Event Table과 Idempotent Sync
문자 테이블과 통화 테이블을 따로 두면 페이지네이션부터 막힙니다. 시간축을 한 테이블로 만드는 설계와, 같은 통화가 두 번 올라오지 않게 하는 멱등 키를 코드와 함께 공개합니다.
링커 기능 하나를 골라 안이 어떻게 움직이는지 적는 시리즈입니다. 지난 글은 PC에서 문자를 주고받게 만들기입니다.
한 고객과 주고받는 건 보통 이렇게 흘러갑니다. 문자로 문의가 오고, 전화로 조건을 이야기하고, 다시 문자로 자료를 보냅니다.
폰에서는 이게 두 앱에 나뉘어 있습니다. 문자는 메시지 앱에, 통화는 통화기록에.
링커는 이걸 한 줄로 붙입니다. 그 「한 줄」이 데이터 모델에서 어떻게 생겼는지 적습니다.
두 테이블을 따로 두면 어디서 막히나
가장 먼저 떠오르는 건 이겁니다.
SELECT * FROM message
WHERE peer_phone = ?
ORDER BY sent_at DESC LIMIT 20;
SELECT * FROM call
WHERE peer_phone = ?
ORDER BY started_at DESC LIMIT 20;
그리고 애플리케이션에서 합쳐 정렬합니다. 스레드가 작을 때는 됩니다.
막히는 건 페이지네이션입니다.
각각 20건씩 가져와 합치면 40건인데, 진짜 최근 20건이 그 안에 있다는 보장이 없습니다. 한쪽에 몰려 있으면 다른 쪽 20건은 통째로 버려집니다.
「더 보기」를 누를 때도 커서를 두 개 들고 다녀야 하고 그 커서는 타입마다 의미가 다릅니다.
UNION ALL로 합쳐 정렬하면 페이지네이션은 됩니다. 대신 정렬을 인덱스로 못 탑니다. 두 테이블에서 각각 뽑아 합친 다음 정렬해야 해서 결국 전체를 훑습니다.
시간축을 테이블 하나로 만듭니다
그래서 이벤트가 일어난 시각 자체를 한 테이블에 뒀습니다. 문자냐 통화냐는 그 행의 속성이고, 타입별 상세는 1:1로 매답니다.
erDiagram
conversation ||--o{ conversation_event : "시간순 이벤트"
conversation_event ||--o| event_message : "event_type=MESSAGE"
conversation_event ||--o| event_call : "event_type=CALL"
event_message ||--o{ message_attachment : "MMS 사진"
event_call ||--o| call_analysis : "전사·요약·할 일"
conversation {
bigint id PK
bigint user_id
varchar remote_phone
timestamptz last_event_at
varchar last_event_preview
int unread_count
}
conversation_event {
bigint id PK
bigint conversation_id FK
varchar event_type
varchar direction
timestamptz action_at
varchar device_event_key
}CREATE TABLE conversation_event (
id BIGSERIAL PRIMARY KEY,
conversation_id BIGINT NOT NULL,
event_type VARCHAR(20) NOT NULL, -- CALL|MESSAGE
direction VARCHAR(20) NOT NULL, -- IN|OUT|MISS
action_at TIMESTAMPTZ NOT NULL, -- 정렬 키
device_event_key VARCHAR(255), -- 멱등 키
...
);
CREATE INDEX ix_conversation_event_conv_action_at
ON conversation_event (conversation_id, action_at DESC);
얻는 게 셋입니다.
정렬과 페이지네이션이 인덱스 하나로 끝납니다. (conversation_id, action_at DESC)를 그대로 탑니다.
타입을 추가해도 타임라인 코드가 안 바뀝니다. 실제로 나중에 「링커 카드」와 「PC에서 건 전화」가 들어왔는데 정렬 쪽은 손대지 않았습니다.
direction이 공통입니다. 수신·발신·부재중은 문자와 통화가 같은 축을 씁니다.
치르는 값도 있습니다. 상세를 붙이려면 조인이 한 번 더 필요하고, event_type과 실제 매달린 자식이 어긋날 수 있습니다. DB가 막아 주지 않아 애플리케이션이 지켜야 합니다.
정렬 키는 들어온 시각이 아닙니다
action_at을 따로 둔 이유가 있습니다.
폰은 과거 것을 나중에 올립니다. 처음 설치하면 최근 6개월을 한꺼번에 밀어 올리고, 오프라인이었던 동안 쌓인 것도 나중에 옵니다.
created_at으로 정렬하면 3개월 전 통화가 오늘 맨 위에 뜹니다.
정렬 키는 그 일이 실제로 일어난 시각이어야 언제 올려도 제자리에 꽂힙니다.
통화 종료 시각은 상세 테이블에 있습니다.
COMMENT ON COLUMN conversation_event_call.ended_at
IS '통화 종료 시각.
시작 시각은 conversation_event.action_at';
시작 시각을 공통 테이블에 둔 건 문자와 같은 축에 세우기 위해서입니다. 문자에는 종료 시각이 없으니까요.
시간 타입 이야기를 덧붙이면, 이 초기 테이블은
TIMESTAMPTZ인데 이후에 만든 테이블은TIMESTAMP를 씁니다. 나중에 「타임존 없는 타입으로 통일한다」는 규칙을 세웠기 때문입니다. 시간 타입은 첫 테이블에서 정하고 적어 두는 편이 낫습니다.
같은 통화가 두 번 올라오지 않게
동기화는 여러 번 실행됩니다. WorkManager는 재시도하고, 네트워크는 끊기고, 사용자는 「다시 불러오기」를 누릅니다.
INSERT 전에 SELECT로 확인하는 방식은 동시성에서 샙니다. DB에 제약으로 박았습니다.
CREATE UNIQUE INDEX uq_conversation_event_device_key
ON conversation_event (
conversation_id, event_type, device_event_key
);
키가 세 칸인 게 포인트입니다.
conversation_id — 스레드가 다르면 별개입니다. device_event_key — 단말이 발급한 영구 식별자입니다. event_type — 문자와 통화가 같은 규칙으로 키를 만들기 때문입니다. 상대에게 문자를 받은 그 밀리초에 전화가 걸려 오면 키가 같아집니다.
sequenceDiagram
autonumber
participant D as 폰 (Room)
participant S as 서버
participant DB as PostgreSQL
Note over D: 실시간 수신 · 6개월 불러오기 ·<br/>내가 보낸 것 — 경로 셋
D->>D: key = 정규화된번호_밀리초
D->>S: 배치 업로드 (최대 N건)
S->>DB: INSERT ... ON CONFLICT DO NOTHING
DB-->>S: imported / skipped
S-->>D: 결과
D->>D: 올린 것만 synced 표시키는 앱이 만듭니다.
// canonical 키: 불러오기/발신과 동일 규칙
// (정규화된 번호_ms). 경로 간 디둡되도록.
val externalKey =
"${PhoneUtil.deviceKeyPhone(sender)}_${timestamp}"
「경로 간 디둡되도록」 이 주석이 설계의 전부입니다.
같은 문자가 앱에 들어오는 길이 여러 개입니다. 실시간 수신(SMS_DELIVER), 초기 6개월 불러오기, 내가 보낸 것의 로컬 기록.
세 경로가 같은 규칙으로 키를 만들어야 하나로 합쳐집니다. 한 곳이라도 규칙이 다르면 같은 문자가 두 줄로 보입니다.
스레드를 정하는 것도 번호 정규화입니다
conversation 한 줄은 (user_id, remote_phone)으로 정해집니다. 그러면 이게 전부 같은 사람이어야 합니다.
010-1234-5678
01012345678
+82 10-1234-5678
fun normalize(raw: String?): String? {
if (raw.isNullOrBlank()) return null
val digits = raw.replace(Regex("[^0-9]"), "")
if (digits.isBlank()) return null
return if (digits.startsWith("82")
&& digits.length >= 11) {
"0" + digits.substring(2) // +82 10... → 010...
} else digits
}
length >= 11 조건은 82로 시작하는 국내 번호를 국가코드로 오인하지 않으려고 붙였습니다.
검색어에는 이 함수를 그대로 쓰면 안 됩니다.
/**
* 숫자·구분자로만 이뤄진 검색어에만 적용한다 —
* "센트럴2차" 처럼 글자가 섞이면
* 거기서 숫자만 뽑아 쓰는 순간
* 거의 모든 번호에 매칭되기 때문이다.
*/
fun normalizeIfPhoneLike(raw: String?): String? =
raw?.takeIf { it.matches(PHONE_LIKE) }
?.let { normalize(it) }
「센트럴2차」에서 숫자만 뽑으면 2입니다. 2가 들어간 모든 번호가 걸립니다. 정규화는 무엇을 정규화하지 않을지를 함께 정해야 합니다.
상세는 N+1 없이 붙입니다
이벤트 20건을 읽고 각각의 상세를 조회하면 쿼리가 21번 나갑니다. 통화 분석과 첨부까지 붙으면 더 늘어납니다.
id를 모아 한 번에 읽고 메모리에서 맵으로 합칩니다.
val events = eventRepository
.findAllByConversationIdOrderByActionAt(conversationId)
if (events.isEmpty()) return emptyList()
val eventIds = events.map { it.id!! }
val callByEventId = callRepository
.findAllByConversationEventIdIn(eventIds)
.associateBy { it.conversationEvent.id!! }
val messageByEventId = messageRepository
.findAllByConversationEventIdIn(eventIds)
.associateBy { it.conversationEvent.id!! }
val analysisByCallId = analysisRepository
.findAllByConversationEventCallIdIn(callIds)
.associateBy { it.conversationEventCall.id!! }
return events.map { /* 맵에서 꺼내 조립 */ }
깊이만큼 단계가 늘어나지만 쿼리 수는 깊이에 비례할 뿐 건수에 비례하지 않습니다. 이벤트가 20건이든 2,000건이든 다섯 번입니다.
JPA의 @ManyToOne 지연 로딩에 맡기면 이게 그대로 N+1이 됩니다. 그래서 배치로 읽고 명시적으로 조립합니다.
남의 대화를 열지 못하게
conversation_id는 연속된 정수입니다. 주소창에서 숫자 하나만 바꾸면 남의 대화를 열어 볼 수 있어야 정상이 아닙니다.
그래서 이벤트를 읽기 전에 소유권부터 봅니다.
fun getEvents(userId: Long, conversationId: Long)
: List<ConversationEventResult> {
val conversation = getById(conversationId)
if (conversation.userId != userId) {
throw InvalidDataException(
"conversation 소유권 불일치: id=$conversationId")
}
...
}
이 검사가 서비스 계층에 있는 게 중요합니다. 컨트롤러에 두면 서비스를 부르는 다른 경로가 생겼을 때 빠집니다.
user_id는 인덱스에도 들어가 있습니다.
CREATE INDEX ix_conversation_user_last_event_at
ON conversation (user_id, last_event_at DESC);
목록 쿼리가 user_id로 먼저 좁혀지므로 성능과 격리가 같은 인덱스를 씁니다.
목록을 위해 비정규화합니다
대화 목록에서는 스레드마다 마지막 이벤트를 보여줘야 합니다. 스레드가 2,000개면 마지막 이벤트를 찾는 쿼리가 2,000번 나갑니다.
윈도 함수로 한 번에 뽑을 수도 있지만 목록은 가장 자주 열리는 화면입니다. 그래서 conversation에 답을 미리 적어 둡니다.
last_event_at TIMESTAMPTZ, -- 목록 정렬용
last_event_preview VARCHAR(200),
last_event_type VARCHAR(20),
unread_count INT NOT NULL DEFAULT 0
비정규화의 값은 늘 같습니다. 갱신 지점이 늘어납니다.
새 이벤트가 들어오는 모든 경로에서 이 넷을 함께 고쳐야 하고, 한 곳을 빠뜨리면 목록 순서가 틀어집니다. 그래서 이벤트 생성은 한 군데를 지나가게 모아 뒀습니다.
아직 정본이 없는 것도 함께 내려줍니다
지난 글의 「PC에서 보낸 문자」가 여기서 만납니다. 폰이 아직 안 보냈으면 conversation_event에 행이 없습니다.
웹은 5초마다 폴링하므로 클라이언트가 그 자리에 만든 줄은 다음 폴링에 쓸려 나갑니다. 그래서 서버가 합쳐 내려 줍니다.
fun getTimeline(userId: Long, conversationId: Long)
: ConversationTimelineResponse {
val events = getEvents(userId, conversationId)
val linker = loadLinker(userId, peerPhone)
val pending = loadPendingOutbound(userId, peerPhone)
val calls = loadCallRequests(userId, peerPhone)
return ConversationTimelineResponse.of(...)
}
한 줄이 두 번 보이지 않을까요. 겹칠 수가 없습니다. pending 조회가 애초에 「아직 안 나간 것」만 가져오기 때문입니다.
fun getUnsentByPeerPhone(userId: Long, peerPhone: String) =
repository.findUnsentByPeerPhone(userId, peerPhone)
.map(::OutboundMessageResult)
발송에 성공하면 폰이 정본을 만들고 같은 순간 이 조회에서 빠집니다. 중복을 걷어내는 코드가 따로 없습니다.
「PC에서 건 전화」쪽 주석이 같은 규칙을 더 분명히 말합니다.
/**
* PC 에서 걸려다 발신까지 가지 못한 전화.
*
* 🔑 걸린 전화는 여기 없다.
* 통화가 끝나면 통화 기록이 timeline 에 올라오므로,
* 요청까지 내려주면 한 통화가 두 줄이 된다.
*/
두 출처의 조건을 상호배타로 만들면 합칠 때 중복 제거가 필요 없습니다. 나중에 dedup 하는 것보다 애초에 겹치지 않게 뽑는 편이 덜 깨집니다.
폰이 본문을 가져가 놓고 결과를 안 알리면 상태가 굳습니다. 그대로 내려주면 웹이 스피너를 계속 돌립니다.
status = if (isDispatchStuck)
OutboundMessageStatus.FAILED.name
else
status.name
저장된 상태와 보여줄 상태를 분리했습니다. 굳었는지는 시각으로 판정하므로 배치가 안 돌아도 화면은 정확합니다.
정리
- 서로 다른 종류를 시간순으로 섞어야 하면 시간축을 테이블 하나로 만듭니다. 정렬과 페이지네이션이 인덱스 하나로 끝납니다.
- 정렬 키는 「일어난 시각」이지 「들어온 시각」이 아닙니다. 과거 데이터를 나중에 올리는 순간 갈립니다.
- 멱등은 유니크 인덱스로 박습니다.
SELECT후INSERT는 동시성에서 샙니다. - 멱등 키에 타입을 넣습니다. 종류가 다른 것이 같은 규칙으로 키를 만들면 충돌합니다.
- 키를 만드는 규칙은 모든 유입 경로가 공유해야 합니다.
- 정규화는 「하지 않을 입력」을 함께 정합니다.
- 상세는 id를 모아 배치로 읽습니다. 쿼리 수를 건수가 아니라 깊이에 비례하게 만듭니다.
- 연속 정수 id를 노출하는 조회는 서비스 계층에서 소유권을 봅니다.
- 합칠 두 출처는 조건을 상호배타로 만듭니다.
- 저장된 상태와 보여줄 상태는 다를 수 있습니다.
다음 글은 통화 녹음을 화자별 말풍선으로 바꾸는 파이프라인입니다.