# 작업 보고: task-2991-sql-fixes
- 팀: dev3-team (다그다)
- 담당: 루 (Lugh, 백엔드)
- 작업 내용: task-2991 CustomerChat caller-binding SQL 에 대한 Codex 게이트(MEDIUM 1건)·로키 레드팀(Low 1건, Medium 1건) 지적 3건 보완
- 생성/수정 파일:
  - `/home/jay/projects/InsuRo/.worktrees/task-2991-dev3/supabase/migrations/20260820T000001_task2991_caller_binding.sql` (적용 SQL)
  - `/home/jay/projects/InsuRo/.worktrees/task-2991-dev3/supabase/migrations/20260820T000000_task2991_caller_binding_ROLLBACK.sql` (롤백 SQL)
- 테스트 결과: 통과 (로컬 Docker Postgres 15, 격리 컨테이너, 작업 종료 후 즉시 제거함)
- 비고: **프로덕션 DB 미적용**. ANU 승인 전까지 두 파일 모두 로컬/워크트리에만 존재. 프로덕션에는 DDL/DML 실행하지 않았음.

## dev3-team 추가 항목
- GLM 코드 품질: 해당 없음 (SQL 마이그레이션 직접 편집, GLM 미사용)
- 버그 발견/수정: 없음 (지적 3건 모두 지정된 방식대로 수정 완료, 신규 결함 미발견)

---

## 보완 3건 반영 내역

### 1. [Codex MEDIUM] 대화 선택 비결정성 제거
- **변경 위치**: 적용 SQL `chat_list_messages` 함수 (원본 170~177행 → 현재 186~194행), `chat_send_message` 함수 (원본 215~222행 → 현재 232~240행)
- **변경 전**: 두 함수 모두
  ```sql
  WHERE t.token = p_token
    AND t.is_active = true
  LIMIT 1;
  ```
  ORDER BY 없이 LIMIT 1 — 같은 (customer_id, agent_id) 쌍에 대화가 2개 이상이면 플래너/저장순서에 따라 `chat_verify_and_open` (`ORDER BY conv.created_at ASC LIMIT 1`)과 다른 대화를 고를 수 있었음.
- **변경 후**: 두 함수 모두
  ```sql
  WHERE t.token = p_token
    AND t.is_active = true
  ORDER BY conv.created_at ASC
  LIMIT 1;
  ```
  `chat_verify_and_open`(90~152행)과 완전히 동일한 선택 규칙(`created_at ASC` 최우선)으로 통일.
- **헤더 주석**: 적용 SQL 39~55행에 보완 3건 요약 섹션 추가.

### 2. [로키 Low] 트리거 함수 REVOKE 누락
- **변경 위치**: 적용 SQL `chat_update_conversation_on_message()` 정의 직후 (262~293행)
- **변경 전**: `CREATE OR REPLACE FUNCTION ... RETURNS trigger ...` 뒤 바로 `DROP TRIGGER IF EXISTS` — REVOKE 문 없음(나머지 4개 함수는 모두 `REVOKE ALL ... FROM PUBLIC` 보유).
- **변경 후**: 함수 정의 직후
  ```sql
  REVOKE ALL ON FUNCTION public.chat_update_conversation_on_message() FROM PUBLIC;
  ```
  추가 — 5개 SECURITY DEFINER 함수 전부 동일한 방어심층 패턴 확보.

### 3. [로키 Medium] 인덱스 3종 동봉
- **변경 위치**: 적용 SQL 새 섹션 "── 7) 성능 인덱스 3종" (295~326행), COMMIT 직전
- **추가 내용**:
  | 인덱스 | 대상 | 충돌 검토 |
  |---|---|---|
  | `idx_conversations_customer_agent` | `conversations(customer_id, agent_id)` | 기존 `idx_conversations_customer_id`(단일 컬럼, `20260309160000_db-safety-improvements.sql`)와 이름·정의 모두 다름 → 신규 이름으로 충돌 없음 |
  | `idx_conversation_messages_conv_created_asc` | `conversation_messages(conversation_id, created_at ASC)` | 기존 `idx_conversation_messages_conv_created`는 동일 컬럼이나 `created_at **DESC**` — 같은 이름 재사용 시 `IF NOT EXISTS`가 조용히 스킵해 ASC 인덱스가 안 생기는 사고 위험 → 신규 이름 사용 |
  | `idx_customer_chat_tokens_customer_active` | `customer_chat_tokens(customer_id, is_active)` | `db-safety-improvements.sql` PART4 가 **완전히 동일한 정의**로 이미 생성 → 의도적으로 같은 이름 재사용(정의 100% 동일하므로 안전), 프로덕션에선 `IF NOT EXISTS`로 no-op 됨을 로컬 검증으로 실증 |
- 전부 `CREATE INDEX IF NOT EXISTS` — 멱등.
- **롤백 대응**: 롤백 SQL에 `DROP INDEX IF EXISTS public.idx_conversations_customer_agent;` 및 `... idx_conversation_messages_conv_created_asc;` 추가. `idx_customer_chat_tokens_customer_active`는 **의도적으로 DROP 하지 않음** — 롤백 SQL 89~103행에 판단 근거(정의 동일 → "우리가 만든 것"과 "이미 있던 것" 구분 불가 → 남의 인덱스를 지우는 부작용 방지, 필요 시 ANU 승인 하 수동 DROP)를 주석으로 명시.

---

## 로컬 검증 결과

로컬 Docker Postgres 15 (`t2991-pg`, 격리 컨테이너, 검증 후 `docker rm -f`로 완전 제거)에 실제 컬럼 정의(`profiles`/`customers`/`customer_chat_tokens`/`conversations`/`conversation_messages`, 마이그레이션 원본에서 그대로 추출)를 재현하고, `_backup/task-2991_pg_policies_before.json` 원문대로 anon 정책 5건 + 기존 인덱스 3종(`idx_customer_chat_tokens_customer_active`, `idx_conversations_customer_id`, `idx_conversation_messages_conv_created`)을 사전 구성한 뒤 검증.

### 3회 재실행 멱등성
```
RUN 1: BEGIN → ... → COMMIT (에러 없음, idx_customer_chat_tokens_customer_active NOTICE: already exists, skipping)
RUN 2: BEGIN → ... → COMMIT (에러 없음, 정책 5건 모두 "does not exist, skipping" 후 재생성 없이 진행 — CREATE OR REPLACE FUNCTION 정상, 신규 인덱스 2종도 "already exists, skipping")
RUN 3: BEGIN → ... → COMMIT (동일)
```
3회 모두 `COMMIT`으로 종료, 에러 0건.

### ★ 세 RPC 가 동일한 대화를 선택하는지 (1번 수정의 핵심 증거)
동일 (customer_id, agent_id) 쌍에 대화 2건을 의도적으로 생성(하나는 2일 전 생성=`conv_old`, 하나는 방금 생성=`conv_new`)한 뒤 anon 롤로 3개 RPC 를 순서대로 호출:

```sql
-- 사전 조건: conv_old=87ae50e6-... (created_at ASC 기준 가장 오래됨), conv_new=54d73cbe-...(더 최근)

SET ROLE anon;
SELECT public.chat_verify_and_open('test-token-dup-conv', '홍길동', '010-1234-5678');
--  verify_and_open_conv
-- --------------------------------------
--  87ae50e6-daff-420d-a4bb-694979d989a1   ← conv_old (ASC 최상단)

SELECT public.chat_send_message('test-token-dup-conv', '안녕하세요, 상담 문의드립니다');
--            sent_message_id
-- --------------------------------------
--  db8e3972-84dd-4d8b-8515-6945b7011fa3

SELECT * FROM public.chat_list_messages('test-token-dup-conv', NULL);
--                  id                  | sender_type |            content            |          created_at
-- --------------------------------------+-------------+--------------------------------+-------------------------------
--  db8e3972-84dd-4d8b-8515-6945b7011fa3 | customer    | 안녕하세요, 상담 문의드립니다  | 2026-08-20 15:02:08.519769+00
```

메시지 실제 저장 위치와 "가장 오래된 대화" 를 교차 확인:
```sql
SELECT
  (SELECT conversation_id FROM conversation_messages WHERE content='안녕하세요, 상담 문의드립니다') AS message_landed_in_conv,
  (SELECT id FROM conversations WHERE customer_id=... ORDER BY created_at ASC LIMIT 1) AS expected_oldest_conv,
  (SELECT count(*) FROM conversations WHERE customer_id=...) AS total_conversations_for_pair;
--        message_landed_in_conv        |         expected_oldest_conv         | total_conversations_for_pair
-- --------------------------------------+--------------------------------------+------------------------------
--  87ae50e6-daff-420d-a4bb-694979d989a1 | 87ae50e6-daff-420d-a4bb-694979d989a1 |                            2
```
→ `chat_verify_and_open` 이 고른 대화(`87ae50e6...`), `chat_send_message` 가 실제로 메시지를 삽입한 대화(`87ae50e6...`), `chat_list_messages` 가 목록을 조회해 낸 대화가 **완전히 일치**. 대화가 2개 존재하는 상황에서도 세 RPC 가 항상 같은(가장 오래된) 스레드를 가리킴을 실증.

### 인덱스 3종 생성 확인
```
indexname                                   | tablename
---------------------------------------------+----------------------
idx_conversation_messages_conv_created       | conversation_messages  (기존, DESC — 유지)
idx_conversation_messages_conv_created_asc   | conversation_messages  (신규, ASC)
idx_conversations_customer_id                | conversations          (기존, 단일컬럼 — 유지)
idx_conversations_customer_agent             | conversations          (신규, 복합)
idx_customer_chat_tokens_customer_active     | customer_chat_tokens   (기존과 이름·정의 동일 — 충돌 없이 유지)
```
5개 인덱스 모두 정상 공존, 이름 충돌 없음.

### 트리거 함수 proacl — PUBLIC 실행권한 부재 확인
```
proname                              | proacl
--------------------------------------+---------------------------------------
chat_gate_info                       | {postgres=X/postgres,anon=X/postgres}
chat_list_messages                   | {postgres=X/postgres,anon=X/postgres}
chat_send_message                    | {postgres=X/postgres,anon=X/postgres}
chat_update_conversation_on_message  | {postgres=X/postgres}          ← anon 권한 없음(REVOKE 반영)
chat_verify_and_open                 | {postgres=X/postgres,anon=X/postgres}
```

### 롤백 실행 후 anon 정책 5건 원상복구 확인
롤백 실행 후 `pg_policies` 조회 결과, 5건 모두 `_backup/task-2991_pg_policies_before.json` 원문(qual/with_check/roles/cmd)과 **글자 단위 일치**:
```
tablename              | policyname                                 | cmd    | roles  | qual | with_check
------------------------+---------------------------------------------+--------+--------+------+-----------------------------------
conversation_messages  | Anon can insert customer messages           | INSERT | {anon} |      | (sender_type = 'customer'::text)
conversation_messages  | Anon can read messages by conversation      | SELECT | {anon} | true |
conversations          | Anon can read conversations                 | SELECT | {anon} | true |
conversations          | Anon can update conversation last message   | UPDATE | {anon} | true | true
customer_chat_tokens   | Anon can read chat tokens                   | SELECT | {anon} | true |
```
함수 5종(트리거 함수 포함) 전부 `DROP` 확인(`pg_proc` 조회 결과 0 rows).
롤백이 신규 인덱스 2종(`idx_conversations_customer_agent`, `idx_conversation_messages_conv_created_asc`)만 제거하고 `idx_customer_chat_tokens_customer_active`는 그대로 유지함을 `pg_indexes` 전/후 비교로 확인.

### 전체 사이클(적용→롤백→재적용) 재검증
롤백 직후 적용 SQL을 다시 실행 — 에러 없이 `COMMIT`, 인덱스 5종 전부 재생성 확인. 적용/롤백/재적용 순환이 어느 지점에서도 깨지지 않음.

---

## 불변식 유지 확인
- **트랜잭션**: 적용 SQL `BEGIN;`(57행) ~ `COMMIT;`(328행), 롤백 SQL `BEGIN;`(24행) ~ `COMMIT;`(끝) — 둘 다 단일 트랜잭션 유지. 신규 추가한 인덱스 구문도 동일 트랜잭션 안쪽에 위치(트랜잭션 밖 `CREATE INDEX CONCURRENTLY` 미사용 — 짧은 락 허용 전제, 기존 설계와 동일).
- **멱등**: 3회 연속 재실행 + 롤백 후 재적용까지 총 4회 적용 테스트 모두 에러 0건.
- **search_path**: `grep -c "SET search_path = public, pg_temp"` → 5건 (5개 SECURITY DEFINER 함수 전부, 트리거 함수 포함) — 누락 없음.
- **롤백 충실도**: 백업 JSON 원문 그대로 anon 정책 5건 복원(로컬 실측으로 글자 단위 일치 확인). agent(설계사) `auth.uid()` 기반 정책은 이번 수정에서도 전혀 건드리지 않음(적용/롤백 SQL 어디에도 해당 정책 DROP/CREATE 없음 — grep 재확인).
- **PII**: RPC 반환 타입 재확인 — `chat_gate_info` → `agent_display_name`만, `chat_verify_and_open` → `uuid`만, `chat_list_messages` → `id/sender_type/content/created_at`만, `chat_send_message` → `uuid`만. 고객 이름·전화번호를 반환하는 RPC 없음(본 보완 작업에서 반환 타입 변경 없음, 원본 설계 유지).

## 프로덕션 미적용 명시
이번 작업은 워크트리(`/home/jay/projects/InsuRo/.worktrees/task-2991-dev3`) 내 2개 마이그레이션 파일만 수정했으며, **프로덕션 Supabase DB 에는 어떠한 DDL/DML 도 실행하지 않았다.** 모든 검증은 로컬 Docker Postgres 15 컨테이너(`t2991-pg`)에서만 수행했고, 검증 완료 직후 `docker rm -f t2991-pg` 로 컨테이너를 완전히 제거했다(`docker ps -a` 로 제거 확인 완료). ANU 승인 전까지 적용 SQL/롤백 SQL 모두 미적용 상태를 유지한다.