Blame for database/tests/ledger_integrity_test.sql

/**
 * DB-INT-01: 비가역 원장 트리거 및 멱등성 제약 검증 스크립트
 *
 * 이 스크립트는 PostgreSQL 환경에서 실행되어 settlement_ledger 테이블의
 * 비가역성(Immutability)과 멱등성(Idempotency)이 물리적으로 보장되는지 확인합니다.
 *
 * 안전장치:
 * - 기본적으로 실행을 거부합니다. 같은 psql 세션에서
 *   SET app.carboany_test_mode = 'on';
 *   을 먼저 지정한 로컬/테스트 DB에서만 실행하십시오.
 * - 모든 변경은 마지막에 ROLLBACK 됩니다.
 */

BEGIN;

DO $$
BEGIN
    IF current_setting('app.carboany_test_mode', true) IS DISTINCT FROM 'on' THEN
        RAISE EXCEPTION
            'Refusing to run ledger integrity test without SET app.carboany_test_mode = on';
    END IF;
END $$;

-- 1. 테스트용 데이터 삽입
INSERT INTO public.settlement_ledger (
    transaction_id, campaign_id, sender_id, sender_name, receiver_id, receiver_name,
    category, asset, amount, idempotency_key, previous_hash, entry_hash, description
) VALUES (
    'test_txn_001', NULL, 'sponsor-A', 'Sponsor A', 'citizen-001', 'John Doe',
    'POINT_REWARD', 'POINT', 1000.0, 'test_idemp_key_001', NULL, 'test_hash_001', 'Test Entry'
);

-- 2. UPDATE 시도 (에러 발생 기대)
DO $$
BEGIN
    BEGIN
        UPDATE public.settlement_ledger SET amount = 2000.0 WHERE idempotency_key = 'test_idemp_key_001';
        RAISE EXCEPTION 'Update should have been blocked by trigger';
    EXCEPTION WHEN raise_exception THEN
        IF SQLERRM NOT LIKE 'settlement_ledger is append-only:%' THEN
            RAISE;
        END IF;
        RAISE NOTICE 'SUCCESS: Update blocked as expected: %', SQLERRM;
    END;
END $$;

-- 3. DELETE 시도 (에러 발생 기대)
DO $$
BEGIN
    BEGIN
        DELETE FROM public.settlement_ledger WHERE idempotency_key = 'test_idemp_key_001';
        RAISE EXCEPTION 'Delete should have been blocked by trigger';
    EXCEPTION WHEN raise_exception THEN
        IF SQLERRM NOT LIKE 'settlement_ledger is append-only:%' THEN
            RAISE;
        END IF;
        RAISE NOTICE 'SUCCESS: Delete blocked as expected: %', SQLERRM;
    END;
END $$;

-- 4. 동일 Idempotency Key 재삽입 시도 (Unique Violation 기대)
DO $$
BEGIN
    BEGIN
        INSERT INTO public.settlement_ledger (
            transaction_id, campaign_id, sender_id, sender_name, receiver_id, receiver_name,
            category, asset, amount, idempotency_key, previous_hash, entry_hash, description
        ) VALUES (
            'test_txn_002', NULL, 'sponsor-A', 'Sponsor A', 'citizen-001', 'John Doe',
            'POINT_REWARD', 'POINT', 1000.0, 'test_idemp_key_001', 'test_hash_001', 'test_hash_002', 'Duplicate Entry'
        );
        RAISE EXCEPTION 'Duplicate insert should have been blocked by unique constraint';
    EXCEPTION WHEN unique_violation THEN
        RAISE NOTICE 'SUCCESS: Duplicate insert blocked by unique constraint';
    END;
END $$;

ROLLBACK;