트리거·프로시저 · routines.sql

-- =====================================================================
-- nestpay MariaDB 루틴 (routines.sql) - 전수 감사(2026-07-21) 반영판
-- 방침(유지보수 최우선): 비즈니스 로직은 전부 Java. DB 루틴은 두 종류만.
--   1) 불변식 방어 트리거 - 로직 없음, "금지"만.
--   2) 파티션 수명 관리(생성·보존 DROP) - DB가 스스로 해야 자연스러운 유일한 운영 루틴.
-- 1차 방어는 권한(GRANT), 트리거는 root/DBA 실수까지 막는 2차 방어선.
-- =====================================================================

DELIMITER $$

-- ---------------------------------------------------------------------
-- 1. append-only 방어 트리거 (#29) - 원장·감사·접근기록·증거 로그
-- ---------------------------------------------------------------------
CREATE TRIGGER trg_ledger_no_update BEFORE UPDATE ON ledger_entries FOR EACH ROW
BEGIN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'ledger_entries is append-only: UPDATE forbidden'; END$$
CREATE TRIGGER trg_ledger_no_delete BEFORE DELETE ON ledger_entries FOR EACH ROW
BEGIN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'ledger_entries is append-only: DELETE forbidden'; END$$

CREATE TRIGGER trg_txn_no_delete BEFORE DELETE ON transactions FOR EACH ROW
BEGIN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'transactions is append-only: DELETE forbidden'; END$$

-- transactions UPDATE 화이트리스트: status·confirmed_at·bank_tran_ref·fail_reason만 가변.
-- 금액·유형·참조·스냅샷·증빙 컬럼 전부 불변 (감사 보완: 스냅샷 변조 차단)
CREATE TRIGGER trg_txn_immutable_columns BEFORE UPDATE ON transactions FOR EACH ROW
BEGIN
  IF NEW.amount <> OLD.amount OR NEW.type <> OLD.type OR NEW.subtype <> OLD.subtype
  OR NEW.fee_amount <> OLD.fee_amount OR NEW.txn_uid <> OLD.txn_uid
  OR NEW.initiator_type <> OLD.initiator_type OR NEW.initiator_id <> OLD.initiator_id
  OR NOT (NEW.idempotency_key <=> OLD.idempotency_key)
  OR NOT (NEW.card_id <=> OLD.card_id)
  OR NOT (NEW.counterparty_card_id <=> OLD.counterparty_card_id)
  OR NOT (NEW.merchant_id <=> OLD.merchant_id)
  OR NOT (NEW.related_txn_id <=> OLD.related_txn_id)
  OR NOT (NEW.fee_rate_snap <=> OLD.fee_rate_snap)
  OR NOT (NEW.fee_fixed_snap <=> OLD.fee_fixed_snap)
  OR NOT (NEW.vat_amount <=> OLD.vat_amount)
  OR NOT (NEW.cancelable_until <=> OLD.cancelable_until)
  OR NOT (NEW.scheduled_at <=> OLD.scheduled_at)
  OR NOT (NEW.bank_account_id <=> OLD.bank_account_id)
  OR NOT (NEW.pg_order_id <=> OLD.pg_order_id)
  OR NOT (NEW.memo <=> OLD.memo)
  OR NEW.created_at <> OLD.created_at THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'transactions: only status/confirmed_at/bank_tran_ref/fail_reason may change';
  END IF;
END$$

CREATE TRIGGER trg_audit_no_update BEFORE UPDATE ON audit_logs FOR EACH ROW
BEGIN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'audit_logs is append-only'; END$$
CREATE TRIGGER trg_audit_no_delete BEFORE DELETE ON audit_logs FOR EACH ROW
BEGIN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'audit_logs is append-only'; END$$

-- 접근기록·증거 로그 방어 대칭화 (감사 보완: security-5/8)
CREATE TRIGGER trg_pii_no_update BEFORE UPDATE ON pii_access_logs FOR EACH ROW
BEGIN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'pii_access_logs is append-only'; END$$
CREATE TRIGGER trg_pii_no_delete BEFORE DELETE ON pii_access_logs FOR EACH ROW
BEGIN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'pii_access_logs is append-only (5yr partition drop only)'; END$$
CREATE TRIGGER trg_login_no_update BEFORE UPDATE ON login_histories FOR EACH ROW
BEGIN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'login_histories is append-only'; END$$
CREATE TRIGGER trg_reject_no_update BEFORE UPDATE ON reject_logs FOR EACH ROW
BEGIN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'reject_logs is append-only'; END$$
CREATE TRIGGER trg_extapi_no_update BEFORE UPDATE ON external_api_logs FOR EACH ROW
BEGIN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'external_api_logs is append-only'; END$$

-- 입금 통지: 상태 전이 컬럼만 가변, 증거 원문 불변 (감사 보완: security-9)
CREATE TRIGGER trg_notice_immutable BEFORE UPDATE ON deposit_notices FOR EACH ROW
BEGIN
  IF NOT (NEW.raw_text <=> OLD.raw_text) OR NEW.dedup_key <> OLD.dedup_key
  OR NOT (NEW.bank_tran_ref <=> OLD.bank_tran_ref)
  OR NOT (NEW.parsed_amount <=> OLD.parsed_amount)
  OR NOT (NEW.parsed_name <=> OLD.parsed_name)
  OR NOT (NEW.identifier_value <=> OLD.identifier_value)
  OR NEW.received_at <> OLD.received_at THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'deposit_notices: only status/matched_txn_id may change';
  END IF;
END$$
CREATE TRIGGER trg_notice_no_delete BEFORE DELETE ON deposit_notices FOR EACH ROW
BEGIN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'deposit_notices is append-only (archive runbook only)'; END$$

-- ---------------------------------------------------------------------
-- 2. 로트 방어: 감소만 허용, 속성 불변
--    복원(취소·출금실패·선물반환)은 UPDATE 증가가 아니라 "신규 로트 생성
--    (origin_lot_id 참조·lot_type·expires_at 승계)" 방식 - queries.sql 0장 규약
-- ---------------------------------------------------------------------
CREATE TRIGGER trg_lot_decrease_only BEFORE UPDATE ON point_lots FOR EACH ROW
BEGIN
  IF NEW.amount_remaining > OLD.amount_remaining OR NEW.amount_remaining < 0
  OR NEW.amount_init <> OLD.amount_init OR NEW.expires_at <> OLD.expires_at
  OR NEW.lot_type <> OLD.lot_type OR NEW.wallet_id <> OLD.wallet_id THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'point_lots: remaining may only decrease; attributes immutable';
  END IF;
END$$

DELIMITER ;

-- ---------------------------------------------------------------------
-- 3. 파티션 자동 확장 (감사 교정: 대상 = V2 실제 파티션 7종과 일치,
--    테이블별 실패 격리 + 신규 파티션 ANALYZE + p_max 적체 감시는 11장 대사)
-- ---------------------------------------------------------------------
DELIMITER $$
CREATE PROCEDURE sp_extend_month_partitions()
BEGIN
  DECLARE next_edge DATE DEFAULT DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 2 MONTH), '%Y-%m-01');
  DECLARE pname VARCHAR(10) DEFAULT DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 MONTH), 'p%Y%m');
  DECLARE tbl VARCHAR(64);
  DECLARE done INT DEFAULT 0;
  -- 하드코딩 대신 p_max 실존 파티션 테이블 자동 선별 (감사 교정: 목록 불일치 재발 방지)
  DECLARE cur CURSOR FOR
    SELECT DISTINCT p.table_name FROM information_schema.partitions p
    WHERE p.table_schema = DATABASE() AND p.partition_name = 'p_max';
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO tbl;
    IF done = 1 THEN LEAVE read_loop; END IF;
    BEGIN
      -- 테이블 단위 실패 격리: 오류는 app_error_logs에 남기고 다음 테이블 진행
      DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
        INSERT INTO app_error_logs (severity, source, error_code, message, created_at)
        VALUES ('CRITICAL', 'BATCH', 'PARTITION_EXTEND_FAIL', CONCAT('table=', tbl, ' pname=', pname), NOW());
      IF NOT EXISTS (
        SELECT 1 FROM information_schema.partitions p2
        WHERE p2.table_schema = DATABASE() AND p2.table_name = tbl AND p2.partition_name = pname
      ) THEN
        SET @ddl = CONCAT('ALTER TABLE ', tbl, ' REORGANIZE PARTITION p_max INTO (',
          ' PARTITION ', pname, ' VALUES LESS THAN (TO_DAYS(''', next_edge, ''')),',
          ' PARTITION p_max VALUES LESS THAN MAXVALUE)');
        PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;
        SET @an = CONCAT('ANALYZE TABLE ', tbl);        -- 새 파티션 통계 공백 방지(감사 보완)
        PREPARE s FROM @an; EXECUTE s; DEALLOCATE PREPARE s;
      END IF;
    END;
  END LOOP;
  CLOSE cur;
END$$

-- ---------------------------------------------------------------------
-- 4. 보존 파티션 DROP (감사 신설: scale-7) - 보존 매트릭스(schema.dbml 서문) 집행
--    DELETE 대신 O(1) DROP. 대상·개월수는 호출부(이벤트)에서 명시.
-- ---------------------------------------------------------------------
CREATE PROCEDURE sp_drop_expired_partitions(IN in_table VARCHAR(64), IN keep_months INT)
BEGIN
  DECLARE pname VARCHAR(64);
  DECLARE done INT DEFAULT 0;
  DECLARE edge VARCHAR(10) DEFAULT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL keep_months MONTH), 'p%Y%m');
  DECLARE cur CURSOR FOR
    SELECT p.partition_name FROM information_schema.partitions p
    WHERE p.table_schema = DATABASE() AND p.table_name = in_table
      AND p.partition_name LIKE 'p2%' AND p.partition_name < edge;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  OPEN cur;
  drop_loop: LOOP
    FETCH cur INTO pname;
    IF done = 1 THEN LEAVE drop_loop; END IF;
    SET @ddl = CONCAT('ALTER TABLE ', in_table, ' DROP PARTITION ', pname);
    PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;
    INSERT INTO audit_logs (actor_type, action, target_type, detail, created_at)
    VALUES ('SYSTEM', 'PARTITION_DROP', in_table, pname, NOW());
  END LOOP;
  CLOSE cur;
END$$
DELIMITER ;

-- 매월 25일 03:00 KST: 파티션 확장 / 04:00: 보존 집행 (outbox 워커가 이중 감시)
CREATE EVENT IF NOT EXISTS ev_extend_partitions
  ON SCHEDULE EVERY 1 MONTH STARTS (TIMESTAMP(DATE_FORMAT(CURDATE(), '%Y-%m-25')) + INTERVAL 3 HOUR)
  DO CALL sp_extend_month_partitions();

DELIMITER $$
CREATE EVENT IF NOT EXISTS ev_retention_drop
  ON SCHEDULE EVERY 1 MONTH STARTS (TIMESTAMP(DATE_FORMAT(CURDATE(), '%Y-%m-25')) + INTERVAL 4 HOUR)
  DO BEGIN
    CALL sp_drop_expired_partitions('notification_inbox', 2);   -- 30일 보존(2개월 여유)
    CALL sp_drop_expired_partitions('external_api_logs', 60);   -- 5년
    CALL sp_drop_expired_partitions('reject_logs', 60);
    CALL sp_drop_expired_partitions('app_error_logs', 60);
    CALL sp_drop_expired_partitions('login_histories', 60);
    CALL sp_drop_expired_partitions('pii_access_logs', 60);
  END$$
DELIMITER ;
-- ledger_entries는 보존 DROP 대상 아님(영구 - 보존 매트릭스)

-- ---------------------------------------------------------------------
-- 5. 권한 설계 (1차 방어 - 배포 스크립트에서 실계정·서버 2대 IP로 치환.
--    'app'@'%' 금지: 'app'@'<API서버1 IP>', 'app'@'<API서버2 IP>'로 한정 - 감사 보완)
-- ---------------------------------------------------------------------
--   INSERT/SELECT 전용(증거·기록): ledger_entries, audit_logs, pii_access_logs,
--     login_histories, reject_logs, external_api_logs, app_error_logs, lot_allocations,
--     policy_agreements, integrity_findings(상태 컬럼만 UPDATE 허용 시 별도)
--   INSERT/SELECT/UPDATE(상태 전이형): transactions(트리거로 컬럼 제한), deposit_notices(동일),
--     point_lots(트리거로 감소만), 나머지 업무 테이블
--   DELETE 허용(보존 매트릭스 명시분만): outbox_jobs(DONE 90일), webhook_deliveries(180일),
--     hourly_summaries(90일)
--   DDL·TRIGGER·EVENT: 마이그레이션 전용 계정만. 파티션 DROP은 이벤트(definer) 경유.

-- ---------------------------------------------------------------------
-- 6. transactions·deposit_notices 연 단위 아카이브 런북 (감사 신설: scale-2)
--    * 비파티션 대형 2종의 5년+ 성장 대응. DBA 절차 - 자동화하지 않는다(위험 작업).
--    1) 대상 연도 확정(예: 만 5년 경과분). 사전 조건: 해당 기간 UNKNOWN/HOLD 0건 확인.
--    2) 마이그레이션 계정으로: CREATE TABLE transactions_arch_YYYY LIKE transactions;
--       (트리거 없음 상태로 생성됨을 확인)
--    3) INSERT INTO transactions_arch_YYYY SELECT ... WHERE created_at < :edge (연 단위 청크)
--    4) 행수·SUM(amount)·MIN/MAX(id) 대조 검증 기록(audit_logs)
--    5) 원본 삭제: 방어 트리거 trg_txn_no_delete를 마이그레이션 계정이 일시 DROP →
--       DELETE (id 범위 청크) → 트리거 재생성 → 검증 재실행. 전 과정 audit_logs 기록.
--    6) 아카이브 테이블은 읽기전용 계정만 SELECT 부여. related_txn_id 교차 참조는
--       추적 화면에서 아카이브 테이블 UNION 조회로 유지.
-- ---------------------------------------------------------------------