# task-2991 — 고객 채팅 caller-binding SQL 쌍 작성 (루/백엔드)

- 작업 디렉토리: `/home/jay/projects/InsuRo/.worktrees/task-2991-dev3`
- 산출물:
  - `/home/jay/projects/InsuRo/.worktrees/task-2991-dev3/supabase/migrations/20260820T000000_task2991_caller_binding_ROLLBACK.sql`
  - `/home/jay/projects/InsuRo/.worktrees/task-2991-dev3/supabase/migrations/20260820T000001_task2991_caller_binding.sql`
- **프로덕션 미적용** (아래 "미적용 확인" 참조)

## 적용 SQL 요약

### 제거하는 anon 정책 5건 (전부 `DROP POLICY IF EXISTS`)
| # | 테이블 | 정책명 | cmd |
|---|---|---|---|
| 1 | customer_chat_tokens | Anon can read chat tokens | SELECT |
| 2 | conversations | Anon can read conversations | SELECT |
| 3 | conversations | Anon can update conversation last message | UPDATE |
| 4 | conversation_messages | Anon can read messages by conversation | SELECT |
| 5 | conversation_messages | Anon can insert customer messages | INSERT |

이후 anon 용 신규 테이블 정책은 만들지 않음 — anon 접근은 RPC 전용으로 전환.

### 추가하는 객체
- `public.chat_gate_info(p_token text) → TABLE(agent_display_name text)` — 토큰 유효 시 `profiles.display_name` 만 반환, 무효 시 0 rows(예외 없음, 존재 여부 미노출)
- `public.chat_verify_and_open(p_token text, p_name text, p_phone text) → uuid` — 토큰+이름+전화(숫자만 비교) 일치 시 `conversations` 조회/생성 후 id 반환, 불일치/무효 시 NULL
- `public.chat_list_messages(p_token text, p_after timestamptz DEFAULT NULL) → TABLE(id, sender_type, content, created_at)` — 토큰에 묶인 대화의 메시지만, `p_after` 있으면 증분, `created_at ASC`
- `public.chat_send_message(p_token text, p_content text) → uuid` — 토큰에 묶인 대화에 `sender_type='customer'` 로 INSERT, 무효 토큰/빈 내용이면 INSERT 없이 NULL
- `public.chat_update_conversation_on_message()` + 트리거 `trg_chat_update_conversation_on_message` (`conversation_messages` AFTER INSERT) — `conversations.last_message_at/last_message_preview(100자)/unread_count` 갱신, `sender_type='customer'` 일 때만 unread 증가

전부 `SECURITY DEFINER` + `SET search_path = public, pg_temp` + `REVOKE ALL ... FROM PUBLIC` 후 `GRANT EXECUTE ... TO anon`.

**agent(설계사) 경로 auth.uid() 정책(실측 145건)·`CrmMessenger.tsx` 의 authenticated UPDATE 정책은 무변경** — 파일 어디에도 이 정책들을 대상으로 한 DROP/ALTER 없음(grep 으로 확인, 아래 자체검증 참조).

## 롤백 SQL 대조표 — 백업 원문 vs 재생성문 5건

원본 출처: `supabase/migrations/_backup/task-2991_pg_policies_before.json` (2026-08-20 프로덕션 실측 pg_policies 덤프)에서 `customer_chat_tokens`/`conversations`/`conversation_messages` 중 `roles=["anon"]` 인 행만 추출.

| # | 테이블 | 정책명 | cmd | 백업 원문 qual/with_check | 롤백 SQL 재생성문 | 일치 |
|---|---|---|---|---|---|---|
| 1 | customer_chat_tokens | Anon can read chat tokens | SELECT | qual=`true` | `FOR SELECT TO anon USING (true)` | ✅ |
| 2 | conversations | Anon can read conversations | SELECT | qual=`true` | `FOR SELECT TO anon USING (true)` | ✅ |
| 3 | conversations | Anon can update conversation last message | UPDATE | qual=`true`, with_check=`true` | `FOR UPDATE TO anon USING (true) WITH CHECK (true)` | ✅ |
| 4 | conversation_messages | Anon can read messages by conversation | SELECT | qual=`true` | `FOR SELECT TO anon USING (true)` | ✅ |
| 5 | conversation_messages | Anon can insert customer messages | INSERT | qual=`null`, with_check=`(sender_type = 'customer'::text)` | `FOR INSERT TO anon WITH CHECK (sender_type = 'customer'::text)` | ✅ |

**실증 검증**: 로컬 Docker Postgres 15 컨테이너(프로덕션과 무관한 임시 인스턴스)에 실제 프로덕션 스키마(customers/profiles/customer_chat_tokens/conversations/conversation_messages, RLS ENABLE, 원본 anon 정책 5건)를 재현 →
1. 적용 SQL 실행 → 5개 정책 DROP + 함수 4종/트리거 CREATE 확인
2. 롤백 SQL 실행 → `pg_policies` 조회 결과가 백업 JSON 5건과 **정책명/cmd/roles/qual/with_check 전부 바이트 단위로 동일**함을 `SELECT * FROM pg_policies WHERE ...` 로 직접 대조 확인 (아래 자체 검증 결과의 원문 출력 포함)

```
       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 rows)
```
백업 JSON 원문과 1:1 일치.

## 자체 검증 결과

### 1. search_path (search_path 하이재킹 방지)
`grep`으로 5개 `SECURITY DEFINER` 함수(`chat_gate_info`, `chat_verify_and_open`, `chat_list_messages`, `chat_send_message`, `chat_update_conversation_on_message`) 전부 바로 아래 `SET search_path = public, pg_temp` 존재 확인. Docker 테스트 DB에서 `pg_proc.proconfig` 로도 5/5 확인:
```
chat_gate_info                      | t | {"search_path=public, pg_temp"}
chat_list_messages                  | t | {"search_path=public, pg_temp"}
chat_send_message                   | t | {"search_path=public, pg_temp"}
chat_update_conversation_on_message | t | {"search_path=public, pg_temp"}
chat_verify_and_open                | t | {"search_path=public, pg_temp"}
```

### 2. PII 미반환
- `chat_gate_info` 반환 타입은 `TABLE(agent_display_name text)` 뿐 — `customers.name`/`phone` 어디에도 SELECT 되지 않음.
- `chat_verify_and_open` 은 `customers.name`/`phone` 을 함수 내부 지역변수(`v_customer_name`/`v_customer_phone`)로만 읽어 **비교 후 폐기**, 반환값은 `uuid`(대화 id)뿐.
- `chat_list_messages`/`chat_send_message` 는 `customers`/`profiles` 를 아예 조회하지 않음.
- 함수 소스 전체를 grep(`RETURN`/`RETURNS` 라인에서 name/phone 키워드) → 매치 없음.

### 3. 트랜잭션
두 파일 모두 `BEGIN;` ~ `COMMIT;` 로 전체를 감쌈(라인 확인: 적용 SQL 41~272, 롤백 SQL 20~80). 부분 적용 방지.

### 4. 멱등성
- 정책: `DROP POLICY IF EXISTS` 선행 후 `CREATE POLICY`.
- 함수: `CREATE OR REPLACE FUNCTION`.
- 트리거: `DROP TRIGGER IF EXISTS` 선행 후 `CREATE TRIGGER`.
- 실증: Docker 테스트 DB에서 적용 SQL을 **연속 3회** 실행(최초 1회 + 재실행 2회) 전부 에러 없이 성공 확인.

### 5. 기능 실증 테스트 (Docker Postgres, 프로덕션과 무관)
같은 임시 DB에서 실제 데이터를 넣고 RPC를 호출해 아래를 확인:
- `chat_gate_info`: 유효/무효/비활성 토큰 각각 1행/0행/0행 — 예외 없이 결과 행 수로만 구분(존재 여부 비노출)
- `chat_verify_and_open`: 이름 불일치·전화 불일치·무효 토큰·비활성 토큰·빈 이름·빈 전화(전부 비숫자) → 전부 NULL. 정상 일치(하이픈 포함 전화) → uuid 반환하며 `conversations` 행 신규 생성(1건). 같은 토큰으로 재호출(하이픈 없는 전화) → **동일 conv id 반환**(중복 생성 없음, `conversations` count=1 유지)
- `chat_send_message`: 정상 메시지 → id 반환 + INSERT 확인. 공백 문자열 → NULL + INSERT 없음. 무효 토큰 → NULL + INSERT 없음(`conversation_messages` count=1 로 검증)
- 트리거: 고객 메시지 INSERT 후 `last_message_preview`(내용 반영)/`unread_count`(1) 갱신 확인. 이어서 `sender_type='agent'` 로 직접 INSERT 시 `unread_count` **불변**(1 유지) 확인 — 설계 요구사항대로 agent 메시지는 unread 증가 안 시킴
- `chat_list_messages`: 정상 호출 시 삽입한 메시지 1건 반환(오름차순). `p_after`=미래 시각 → 0행. 무효 토큰 → 0행
- 정책 제거 확인: `pg_policies` 에서 anon roles 을 가진 대상 3테이블 정책 수 = 0
- agent 정책 무변경 확인: `Agents can read own chat tokens` 정책이 롤백 이전/이후 모두 그대로 존재

### 6. 눈 정독 (문법/오타/괄호)
두 파일 전체를 라인 단위로 재검토. 괄호 짝, 세미콜론, `$$ LANGUAGE plpgsql` 종결, `REVOKE`/`GRANT` 함수 시그니처(파라미터 타입 포함, 오버로드 대비 정확히 일치) 확인 완료. 위 Docker 실행이 문법 오류 0건으로 이를 실증.

## 미적용 확인

- **프로덕션 DB에 어떤 DDL 도 적용하지 않았다.** `psycopg2` 로 쓰기 연결을 연 적 없음(설치는 되어 있으나 import/connect 호출 안 함).
- 위 모든 실증 검증은 **로컬 Docker 컨테이너(`task2991-pg-test`, postgres:15, 포트 15432)** 에서만 수행했으며, 검증 완료 후 `docker rm -f` 로 즉시 폐기함. 프로덕션 Supabase 인스턴스에는 어떤 네트워크 연결도 시도하지 않았음.
- 두 마이그레이션 파일은 `/home/jay/projects/InsuRo/.worktrees/task-2991-dev3/supabase/migrations/` 에 **파일로만** 존재하며, ANU 승인 전까지 적용 대기 상태.