🖥️ '데이터 기반의 커뮤니티 운영'을 위한 핵심 DB 설계 여정

나는 132명 육아 커뮤니티를 운영하는데, 카카오톡 대화 데이터를 
체계적으로 관리하고 싶어. 

현재 문제:
- 분기마다 3시간씩 수작업으로 참여도 분석
- 1.5년치 대화 내역이 txt 파일로만 존재
- 누가 언제 어떤 말을 했는지 찾기 어려움

향후 하고 싶은 것:
- 신규 멤버 온보딩 설문
- 3일차 피드백 자동 수집
- AI 대화 요약
- 추천 제품/레시피 검색
- 월간 활동 순위

이런 기능들을 모두 지원할 수 있는 데이터베이스 구조를 설계해줘.

-- 1️⃣ rooms: 카톡방 정보 (마치 아파트 동호수 관리)
CREATE TABLE rooms (
    room_id TEXT PRIMARY KEY,              -- 방 고유번호 (SHA-1 해시)
    room_name TEXT UNIQUE NOT NULL,        -- "19기 카톡봇", "유주맘"
    display_name TEXT,                     -- 화면에 보여줄 별명
    room_type TEXT DEFAULT 'chat',         -- 일반채팅/공지방 구분
    meta_data TEXT,                        -- JSON: 방 설명, 규칙 등
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    last_activity TEXT,
    message_count INTEGER DEFAULT 0,
    participant_count INTEGER DEFAULT 0
);

-- 2️⃣ participants: 참여자 정보 (각 방의 멤버 명단)
CREATE TABLE participants (
    participant_id TEXT PRIMARY KEY,        -- 참여자 고유번호
    room_id TEXT NOT NULL,                 -- 어느 방 소속인지
    user_name TEXT NOT NULL,               -- "지니K"
    user_identifier TEXT,                  -- 카카오톡 내부 ID
    kakao_id TEXT,
    role TEXT DEFAULT 'member',            -- 멤버/관리자 구분
    meta_data TEXT,                        -- JSON: 온보딩 설문 답변 등
    joined_at TEXT DEFAULT CURRENT_TIMESTAMP,
    last_seen_at TEXT,
    message_count INTEGER DEFAULT 0,
    FOREIGN KEY (room_id) REFERENCES rooms(room_id)
);

-- 3️⃣ messages: 실제 대화 내용
CREATE TABLE messages (
    message_id INTEGER PRIMARY KEY AUTOINCREMENT,
    room_id TEXT NOT NULL,
    participant_id TEXT NOT NULL,
    content TEXT NOT NULL,
    message_type TEXT DEFAULT 'text',      -- 텍스트/이미지/링크 구분
    meta_data TEXT,                        -- JSON: 답글, 반응 등
    sent_at TEXT DEFAULT CURRENT_TIMESTAMP,
    is_deleted INTEGER DEFAULT 0,
    FOREIGN KEY (room_id) REFERENCES rooms(room_id),
    FOREIGN KEY (participant_id) REFERENCES participants(participant_id)
);

-- 4️⃣ bot_actions: 봇이 한 행동 기록 (설문, 알림 등)
CREATE TABLE bot_actions (
    action_id INTEGER PRIMARY KEY AUTOINCREMENT,
    room_id TEXT NOT NULL,
    participant_id TEXT,                   -- 누구에게 보냈는지
    action_type TEXT NOT NULL,             -- survey/reminder/summary
    action_data TEXT,                      -- JSON: 설문 질문, 알림 내용
    status TEXT DEFAULT 'pending',         -- 대기중/완료/실패
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    completed_at TEXT,
    FOREIGN KEY (room_id) REFERENCES rooms(room_id),
    FOREIGN KEY (participant_id) REFERENCES participants(participant_id)
);

-- 5️⃣ statistics_cache: 통계 캐시 (빠른 조회용)
CREATE TABLE statistics_cache (
    cache_id INTEGER PRIMARY KEY AUTOINCREMENT,
    room_id TEXT,
    participant_id TEXT,
    metric_type TEXT NOT NULL,             -- daily/weekly/monthly
    metric_name TEXT NOT NULL,             -- message_count/active_days
    metric_value REAL NOT NULL,
    period_start TEXT NOT NULL,
    period_end TEXT NOT NULL,
    calculated_at TEXT DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(room_id, participant_id, metric_type, metric_name, period_start)
);

-- 6️⃣ keyword_trends: 키워드 트렌드 (화제 추적)
CREATE TABLE keyword_trends (
    trend_id INTEGER PRIMARY KEY AUTOINCREMENT,
    room_id TEXT NOT NULL,
    keyword TEXT NOT NULL,
    frequency INTEGER DEFAULT 1,
    first_seen TEXT DEFAULT CURRENT_TIMESTAMP,
    last_seen TEXT DEFAULT CURRENT_TIMESTAMP,
    period TEXT NOT NULL,                  -- daily/weekly/monthly
    UNIQUE(room_id, keyword, period, DATE(last_seen))
);

-- 자주 조회하는 패턴에 맞춰 인덱스 생성
CREATE INDEX idx_messages_room_sent ON messages(room_id, sent_at DESC);
CREATE INDEX idx_messages_participant_sent ON messages(participant_id, sent_at DESC);
CREATE INDEX idx_participants_room ON participants(room_id);
CREATE INDEX idx_bot_actions_status ON bot_actions(status, created_at);

데이터베이스 전문가 관점에서 이 스키마를 평가해줘:
- 정규화가 적절한가?
- 성능 문제는 없는가?
- 확장성은 충분한가?
- 개선할 점은?