# 086 마이그레이션 — 대무자(代務者) 관리 테이블 + 감사 로그 처리자 추적

작성일: 2026-05-11
작성자: johngreen
관련: `.omc/specs/deep-dive-user-substitute-management.md`, `.omc/plans/autopilot-impl.md`

## 목적

- `USER_SUBSTITUTES` 테이블 신설 — 직원 부재 시 결재/일반 업무 대신 처리 권한 위임.
- `SYSTEM_AUDIT_LOG.PROCESSOR_ID/PROCESSOR_NAME` 컬럼 추가 — 대무 처리 시 "원본 사용자(A)" 와 "실제 처리자(B)" 분리 기록.
- `APPROVAL_PROXY_SETTINGS` 기존 운영 데이터를 `USER_SUBSTITUTES` 로 1회 복사 (어댑터 방향).

## Phase 1 범위 (Non-Goal 명시)

- **결재 대무 한정.** 메뉴/AUTHORITY_MASTER 기반 일반 권한 union 은 **Phase 2** 로 descope.
- `APPROVAL_PROXY_SETTINGS` 테이블 유지 (rollback safety). Phase 2 에서 폐기 검토.

## 전제 조건 (Hard Prerequisite)

PostgreSQL `btree_gist` 확장이 설치되어 있어야 합니다. EXCLUDE 제약이 이 확장에 의존합니다.

```sql
-- 사전 점검 (이 SELECT 가 1 row 를 반환하면 진행 가능)
SELECT 1 FROM pg_extension WHERE extname = 'btree_gist';

-- 미설치 시 superuser 권한으로 활성화
CREATE EXTENSION IF NOT EXISTS btree_gist;
```

`btree_gist` 미설치 + 활성화 권한 없음 → 운영팀에 확장 설치 요청 후 마이그레이션 재시도. **trigger 기반 fallback 은 race-unsafe 라 사용하지 않습니다.**

## 스키마

### USER_SUBSTITUTES 테이블 (신설)

| 컬럼 | 타입 | 제약 | 설명 |
|---|---|---|---|
| `SUBSTITUTE_ID` | BIGSERIAL | PK | 자동 증가 |
| `COMPANY_CODE` | VARCHAR(50) | NOT NULL | 회사 코드 (테넌트 식별) |
| `ORIGINAL_USER_ID` | VARCHAR(50) | NOT NULL | 위임자 (A, 원본 사용자) |
| `PROXY_USER_ID` | VARCHAR(50) | NOT NULL | 대무자 (B) |
| `START_DATE` | DATE | NULL 가능 | 시작일 (NULL = 즉시) |
| `END_DATE` | DATE | NOT NULL | 종료일 (그 날 23:59:59 까지 유효) |
| `REASON` | VARCHAR(500) | NULL | 사유 |
| `IS_ACTIVE` | BOOLEAN | DEFAULT TRUE | 활성 플래그 |
| `CREATED_BY` | VARCHAR(50) | | 생성자 |
| `CREATED_DATE` | TIMESTAMP | DEFAULT NOW() | 생성 시각 |
| `UPDATED_BY` | VARCHAR(50) | | 수정자 |
| `UPDATED_DATE` | TIMESTAMP | DEFAULT NOW() | 수정 시각 |

### 제약 / 인덱스

- `CHECK (ORIGINAL_USER_ID <> PROXY_USER_ID)` — self-위임 DB-level 차단
- `EXCLUDE USING gist (... WITH =, daterange WITH &&) WHERE (IS_ACTIVE)` — 같은 (COMPANY, ORIGINAL, PROXY) 쌍의 활성 기간 겹침 차단
- 인덱스 2개: `(COMPANY_CODE, ORIGINAL_USER_ID, IS_ACTIVE)`, `(COMPANY_CODE, PROXY_USER_ID, IS_ACTIVE)`

### SYSTEM_AUDIT_LOG ALTER

- `PROCESSOR_ID VARCHAR(50)` — 실제 처리자 (B). 평시 = USER_ID 와 동일.
- `PROCESSOR_NAME VARCHAR(100)` — 처리자 이름.

## SQL

```sql
-- =================================================================
-- 086: USER_SUBSTITUTES + SYSTEM_AUDIT_LOG 처리자 추적
-- =================================================================

-- 0. 전제 확장
CREATE EXTENSION IF NOT EXISTS btree_gist;

-- 1. USER_SUBSTITUTES 테이블
CREATE TABLE IF NOT EXISTS USER_SUBSTITUTES (
    SUBSTITUTE_ID     BIGSERIAL    PRIMARY KEY,
    COMPANY_CODE      VARCHAR(50)  NOT NULL,
    ORIGINAL_USER_ID  VARCHAR(50)  NOT NULL,
    PROXY_USER_ID     VARCHAR(50)  NOT NULL,
    START_DATE        DATE         NULL,
    END_DATE          DATE         NOT NULL,
    REASON            VARCHAR(500),
    IS_ACTIVE         BOOLEAN      NOT NULL DEFAULT TRUE,
    CREATED_BY        VARCHAR(50),
    CREATED_DATE      TIMESTAMP    NOT NULL DEFAULT NOW(),
    UPDATED_BY        VARCHAR(50),
    UPDATED_DATE      TIMESTAMP    NOT NULL DEFAULT NOW(),
    CONSTRAINT chk_user_substitutes_self
        CHECK (ORIGINAL_USER_ID <> PROXY_USER_ID),
    CONSTRAINT chk_user_substitutes_date
        CHECK (START_DATE IS NULL OR START_DATE <= END_DATE),
    CONSTRAINT excl_user_substitutes_overlap
        EXCLUDE USING gist (
            COMPANY_CODE WITH =,
            ORIGINAL_USER_ID WITH =,
            PROXY_USER_ID WITH =,
            daterange(START_DATE, END_DATE, '[]') WITH &&
        ) WHERE (IS_ACTIVE = TRUE)
    -- daterange 의 lower bound 가 NULL 이면 -infinity. EXCLUDE 인덱스는
    -- IMMUTABLE 함수만 허용하므로 COALESCE(.., CURRENT_DATE) 같은 STABLE 함수 사용 불가.
    -- START_DATE NULL 인 같은 쌍 활성 row 들은 어쨌든 겹침으로 자연 차단됨.
);

-- 2. 인덱스
CREATE INDEX IF NOT EXISTS idx_user_substitutes_original
    ON USER_SUBSTITUTES (COMPANY_CODE, ORIGINAL_USER_ID, IS_ACTIVE);

CREATE INDEX IF NOT EXISTS idx_user_substitutes_proxy
    ON USER_SUBSTITUTES (COMPANY_CODE, PROXY_USER_ID, IS_ACTIVE);

-- 3. SYSTEM_AUDIT_LOG 컬럼 추가
ALTER TABLE SYSTEM_AUDIT_LOG
    ADD COLUMN IF NOT EXISTS PROCESSOR_ID   VARCHAR(50),
    ADD COLUMN IF NOT EXISTS PROCESSOR_NAME VARCHAR(100);

-- 4. APPROVAL_PROXY_SETTINGS → USER_SUBSTITUTES 1회 복사 (idempotent)
--    기존 운영 데이터 보존 + 어댑터 방향 read 경로가 즉시 동작하도록
INSERT INTO USER_SUBSTITUTES (
    COMPANY_CODE, ORIGINAL_USER_ID, PROXY_USER_ID,
    START_DATE, END_DATE, REASON, IS_ACTIVE,
    CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE
)
SELECT
    p.COMPANY_CODE, p.ORIGINAL_USER_ID, p.PROXY_USER_ID,
    p.START_DATE, p.END_DATE, p.REASON,
    CASE WHEN p.IS_ACTIVE = 'Y' THEN TRUE ELSE FALSE END,
    'migration_086', NOW(),
    'migration_086', NOW()
FROM APPROVAL_PROXY_SETTINGS p
WHERE NOT EXISTS (
    SELECT 1 FROM USER_SUBSTITUTES s
    WHERE s.COMPANY_CODE = p.COMPANY_CODE
      AND s.ORIGINAL_USER_ID = p.ORIGINAL_USER_ID
      AND s.PROXY_USER_ID = p.PROXY_USER_ID
      AND s.START_DATE IS NOT DISTINCT FROM p.START_DATE
      AND s.END_DATE = p.END_DATE
);
```

## 사전 점검

```sql
-- A. btree_gist 확장 설치 여부
SELECT extname, extversion FROM pg_extension WHERE extname = 'btree_gist';
-- 기대: 1 row. 없으면 superuser 로 CREATE EXTENSION 후 진행.

-- B. APPROVAL_PROXY_SETTINGS 에 EXCLUDE 제약을 위반하는 데이터 사전 탐지
--    (같은 쌍 + 기간 겹침). 있으면 마이그레이션 4번 INSERT 가 일부 실패할 수 있음.
SELECT COMPANY_CODE, ORIGINAL_USER_ID, PROXY_USER_ID, COUNT(*)
FROM APPROVAL_PROXY_SETTINGS
WHERE IS_ACTIVE = 'Y'
GROUP BY COMPANY_CODE, ORIGINAL_USER_ID, PROXY_USER_ID
HAVING COUNT(*) > 1;
-- 결과 있으면 운영팀과 상의 후 비활성화 처리.

-- C. SYSTEM_AUDIT_LOG 컬럼 사전 상태
SELECT column_name FROM information_schema.columns
WHERE table_name = 'system_audit_log'
  AND column_name IN ('processor_id', 'processor_name');
-- 빈 결과여야 정상. 이미 있으면 ALTER 의 IF NOT EXISTS 가 안전.
```

## 사후 검증

```sql
-- D. 테이블 + 제약 + 인덱스 확인
\d USER_SUBSTITUTES
-- 확인 항목: PK, CHECK 2개, EXCLUDE, 인덱스 2개

-- E. 컬럼 추가 확인
SELECT column_name FROM information_schema.columns
WHERE table_name = 'system_audit_log'
  AND column_name IN ('processor_id', 'processor_name');
-- 기대: 2 rows

-- F. EXCLUDE 동작 확인 (테스트)
BEGIN;
INSERT INTO USER_SUBSTITUTES
  (COMPANY_CODE, ORIGINAL_USER_ID, PROXY_USER_ID, END_DATE)
  VALUES ('TEST_CO', 'A', 'B', CURRENT_DATE + 7);
INSERT INTO USER_SUBSTITUTES
  (COMPANY_CODE, ORIGINAL_USER_ID, PROXY_USER_ID, END_DATE)
  VALUES ('TEST_CO', 'A', 'B', CURRENT_DATE + 14);
-- 두 번째 INSERT 가 23P01 (exclusion_violation) 으로 거부되어야 정상
ROLLBACK;

-- G. self-위임 차단 확인
BEGIN;
INSERT INTO USER_SUBSTITUTES
  (COMPANY_CODE, ORIGINAL_USER_ID, PROXY_USER_ID, END_DATE)
  VALUES ('TEST_CO', 'A', 'A', CURRENT_DATE + 7);
-- 23514 (check_violation) 거부되어야 정상
ROLLBACK;

-- H. 데이터 복사 검증
SELECT
    (SELECT COUNT(*) FROM APPROVAL_PROXY_SETTINGS) AS legacy_count,
    (SELECT COUNT(*) FROM USER_SUBSTITUTES)        AS new_count;
```

## 실행

```bash
# 1) 메타 DB
psql -h <host> -U postgres -d invyone -f RUN_086.sql

# 2) 각 테넌트 DB
for db in $(psql -tA -d invyone -c "SELECT db_name FROM company_mng WHERE db_status='active'"); do
  echo "=== $db ==="
  psql -h <host> -U postgres -d "$db" -f RUN_086.sql
done
```

`RUN_086.sql` 은 위 "SQL" 섹션 0~4 블록을 그대로 담은 파일입니다.

## 롤백

```sql
-- 4번 단계 데이터 복사로 발생한 row 만 표시적으로 제거 (선택)
DELETE FROM USER_SUBSTITUTES WHERE CREATED_BY = 'migration_086';

-- 컬럼 제거
ALTER TABLE SYSTEM_AUDIT_LOG
    DROP COLUMN IF EXISTS PROCESSOR_ID,
    DROP COLUMN IF EXISTS PROCESSOR_NAME;

-- 테이블 제거
DROP TABLE IF EXISTS USER_SUBSTITUTES;

-- btree_gist 확장은 보존 (다른 테이블/인덱스가 사용 중일 수 있음)
```

## 적용 환경 체크리스트

- [ ] 로컬 docker `naengangi-pg` (`btree_gist` 1.7 available, 활성화 후 확인)
- [ ] wace 개발서버 PostgreSQL (운영팀 확인 필요)
- [ ] 운영 메타 DB (`invyone`)
- [ ] 운영 각 테넌트 DB (loop)
