4편: plays 테이블의 회수 — 주간/월간 랭킹과 유령 기록

1편에서 plays(판 단위 원천)와 player_game_stats(집계)를 둘 다 만들어 뒀다고 했습니다. 당시엔 집계만 있어도 서비스가 돌았어요. 레벨도, 전체 랭킹도 집계만 읽으면 되니까요. plays는 "언젠가 쓰겠지" 하고 넣어둔 테이블이었습니다.

그 언젠가가 왔습니다. 주간·월간 랭킹입니다.

회귀 위험 0으로 얹기

기존 랭킹 API는 잘 돌고 있었습니다. 여기에 기간 개념을 넣으면서 제일 피하고 싶었던 건, 기존 랭킹이 미묘하게 달라지는 것이었어요. 순위 하나가 바뀌어도 유저 입장에서는 버그로 보입니다.

그래서 규칙을 이렇게 잡았습니다.

 

GET /rankings?period=daily|weekly|monthly|all   (기본 all)

• period가 없거나 all이면 기존 함수를 그대로 호출합니다. 새 코드를 한 줄도 안 탑니다.
• 그 외 값일 때만 새 모듈(periodRankings.js)로 갑니다.

클라이언트도 같은 규칙을 거울처럼 지켰습니다. period === 'all'이면 아예 파라미터를 붙이지 않습니다.

 

js

const VISIBLE_PERIODS = ['weekly', 'monthly', 'all'];   // daily는 숨김(배열에 넣으면 노출)

그 결과 기존 사용 흐름에서 나가는 요청과 돌아오는 응답이 바이트 단위로 동일합니다. 회귀 가능성을 코드로 증명한 셈이에요. "테스트로 확인했습니다"보다 "다른 코드를 아예 안 탑니다"가 훨씬 마음이 편합니다.

daily는 서버에 구현해 두고 UI에서는 숨겼습니다. 아직 유저 수가 많지 않아 일간 랭킹은 표본이 너무 작거든요. 나중에 배열에 문자열 하나 추가하면 바로 켜집니다.

KST 경계를 서버 로컬 시간에 맡기지 않기

기간 랭킹에서 진짜 까다로운 건 SQL이 아니라 "이번 주가 언제부터인가" 입니다.

서버 로컬 타임존에 의존하면 안 됩니다. 컨테이너의 TZ 설정 하나로 경계가 9시간 밀리니까요. 그렇다고 SQLite의 날짜 함수에 맡기면 인덱스를 못 탑니다(WHERE date(played_at) >= ... 같은 식으로 컬럼을 감싸는 순간 인덱스가 죽어요).

그래서 경계 epoch를 JS에서 계산해서 바인드하는 방식으로 갔습니다.

 

js

const KST = 9 * 3600 * 1000;   // KST=UTC+9 고정(서버 로컬 TZ에 의존하지 않음)

export function periodSince(period, now = Date.now()) {
  if (period !== 'daily' && period !== 'weekly' && period !== 'monthly') return null;
  const k = new Date(now + KST);
  const y = k.getUTCFullYear(), m = k.getUTCMonth(), d = k.getUTCDate();
  if (period === 'monthly') return Date.UTC(y, m, 1) - KST;
  if (period === 'daily')   return Date.UTC(y, m, d) - KST;
  const back = (k.getUTCDay() + 6) % 7;   // 월요일 시작
  return Date.UTC(y, m, d - back) - KST;
}

트릭은 now + 9시간을 해두고 UTC 필드로 읽는 것입니다. 그러면 그 값이 곧 KST의 연/월/일/요일이 돼요. 거기서 경계의 UTC 자정을 만든 뒤 다시 9시간을 빼면 우리가 원하는 epoch가 나옵니다. getUTCDay() + 6) % 7은 일요일 시작인 JS 요일을 월요일 시작으로 돌리는 계산이고요.

이렇게 나온 숫자 하나를 그냥 바인드하니 쿼리는 인덱스를 그대로 탑니다.

 

sql

SELECT player_key AS key, MAX(score) AS score, played_at
FROM plays
WHERE game = ? AND mode = 'score' AND score IS NOT NULL AND played_at >= ?
GROUP BY player_key
ORDER BY score DESC, played_at ASC
LIMIT ?

여기 SQLite 특례가 하나 숨어 있습니다. MAX() 집계와 함께 있는 bare 컬럼(played_at)은 그 극값 행의 값을 가져옵니다. 그래서 "각 플레이어의 최고 점수와 그 점수를 낸 날짜"가 서브쿼리 없이 한 번에 나옵니다. 표준 SQL은 아니라 주석으로 명시해 뒀어요.

동점 처리도 정했습니다. 점수는 먼저 달성한 사람이 위(played_at ASC), 대전은 승수가 같으면 판수가 적은 쪽(승률이 높으니까)이 위로 갑니다.

사라진 게스트가 랭킹에 남아 있었다

기간 랭킹을 붙이면서 랭킹 화면을 자주 들여다봤는데, 이상한 걸 발견했습니다. 분명히 정리됐어야 할 게스트가 전체 랭킹에 계속 떠 있었어요.

원인은 스키마에 있었습니다.

 

sql

CREATE TABLE score_rankings (
  ...
  player_key TEXT REFERENCES players(key) ON DELETE SET NULL ON UPDATE CASCADE,
  nickname   TEXT,     -- 스냅샷
  score      INTEGER NOT NULL,
  ...
);

score_rankings만 ON DELETE SET NULL입니다. 다른 테이블은 전부 CASCADE인데 여기만 다르죠. 의도가 있었습니다. 점수 기록은 "그때 그 사람이 이 점수를 냈다"는 역사라서, 계정이 지워졌다고 최고 점수 자체를 없애는 게 맞나 싶었거든요. 그래서 닉네임을 스냅샷으로 같이 저장해 두고, 플레이어가 사라지면 참조만 끊게 했습니다.

이 설계가 게스트 TTL과 만나니 유령이 됐습니다. 14일 지난 게스트를 지우면 players 행은 사라지는데, score_rankings의 행은 player_key만 NULL이 되고 닉네임과 점수는 그대로 남아 영원히 랭킹에 박혀 있는 거예요. 심지어 이제는 주인이 없으니 지울 방법도 마땅치 않습니다(NULL은 다 똑같이 생겼으니까요).

순서와 트랜잭션

고치면서 두 가지를 신경 썼습니다.

 

js

export function pruneExpiredGuests() {
  const db = getDb();
  const cutoff = Date.now() - TTL_MS;
  const rows = db.prepare(
    "SELECT key FROM players WHERE kind = 'guest' AND last_seen IS NOT NULL AND last_seen < ?"
  ).all(cutoff);
  if (!rows.length) return [];
  const keys = rows.map(r => r.key);
  transaction(db, () => {
    // ① score_rankings 먼저: players 삭제 시 SET NULL로 player_key가 사라지기 전에 특정해야 한다.
    const placeholders = keys.map(() => '?').join(',');
    db.prepare(`DELETE FROM score_rankings WHERE player_key IN (${placeholders})`).run(...keys);
    // ② players: 동일한 keys로 특정(key=PK). SELECT~DELETE 사이 재접속(last_seen 갱신)에도
    //    ①②가 같은 대상만 지우도록 cutoff 재평가 대신 key IN을 쓴다. FK CASCADE가 나머지 정리.
    db.prepare(`DELETE FROM players WHERE key IN (${placeholders})`).run(...keys);
  });
  return keys.map(k => k.slice(2));
}

첫째, 삭제 순서. players를 먼저 지우면 SET NULL이 발동해서 어느 랭킹 행이 그 게스트 것이었는지 알 수 없게 됩니다. 반드시 score_rankings를 먼저 지워야 해요. 이런 건 한 번 순서가 뒤집히면 되돌릴 수도 없어서, 주석에 이유를 박아 뒀습니다.

둘째, 레이스 컨디션. 원래 players 삭제는 WHERE last_seen < cutoff 조건을 다시 평가했습니다. 그런데 SELECT와 DELETE 사이에 그 게스트가 재접속하면? last_seen이 갱신되면서 ②의 조건에서 빠집니다. 결과는 랭킹 기록만 지워지고 플레이어는 살아남는 어중간한 상태예요. 그래서 ①②가 처음에 뽑은 동일한 key 목록을 쓰도록 통일하고, 둘을 한 트랜잭션으로 묶었습니다.

그리고 안내 한 줄

기술적으로는 다 정리됐는데, 유저 입장에서는 여전히 이상할 수 있습니다. 어제까지 랭킹에 있던 이름이 오늘 없어지니까요.

그래서 랭킹 화면 상단에 조건 분기 없이 항상 안내를 한 줄 넣었습니다. "게스트 기록은 14일 후 정리된다"는 내용이에요. 게스트인지 회원인지 판단해서 보여줄 수도 있었지만, 그러면 회원은 이 정책을 영영 모릅니다. 게스트로 놀다가 가입할지 말지 고민하는 사람에게 가장 필요한 정보이기도 하고요. (덧붙여 이 안내는 처음에 두 문장이 한 줄로 이어져 읽기 나빠서, 며칠 뒤 <br />로 나누는 커밋이 따로 하나 있습니다. 이런 게 제일 자주 나오는 후속 작업인 것 같아요.)

4편을 마치며 — 옮기길 잘했나

전체를 돌아보면 이렇습니다.

• 1편: 파일 네 개에 흩어진 신원을 players.key 하나로 통합. 회원가입 병합과 게스트 정리가 SQL 한 줄로 축소.
• 2편: 배포 파이프라인에 스크립트를 끼울 자리가 없어서 마이그레이션을 서버 기동에 통합. 실패하면 일부러 안 띄우기.
• 3편: FK가 드러낸 크래시를 경로별 방어가 아니라 단일 보증 지점으로 해결.
• 4편: 미리 만들어 둔 plays 테이블로 기간 랭킹을 회귀 위험 0으로 얹고, SET NULL이 만든 유령 기록 정리.

가장 크게 남은 건 "저장소를 바꾸면 그동안 조용히 깨져 있던 게 드러난다" 는 겁니다. 게스트 닉네임이 저장 안 되던 것도, 랭킹의 유령 기록도, 파일 저장소였으면 앞으로도 모른 채 굴러갔을 거예요. FK 위반으로 서버가 몇 번 죽은 건 아팠지만, 그 대가로 데이터가 실제로 맞는지를 DB가 대신 검사해 주게 됐습니다.

의존성은 여전히 0개고요.

👉 PlayTalk 에서 직접 플레이해 보실 수 있습니다.