17주차 코드 뒤풀이: 분석 SQL을 근거까지 읽기

services:
  db:
    image: postgres:17.10-alpine
    ports:
      - "${FCL_DB_PORT:-5432}:5432"
    environment:
      POSTGRES_DB: financial_core
      POSTGRES_USER: app
      POSTGRES_PASSWORD: ${FCL_DB_PASSWORD:?set FCL_DB_PASSWORD in this terminal}
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U app -d financial_core"]
      interval: 2s
      timeout: 2s
      retries: 30
    volumes:
      - financial-core-db:/var/lib/postgresql/data

volumes:
  financial-core-db:

CREATE TABLE account (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    owner_id VARCHAR(64) NOT NULL,
    account_no VARCHAR(32) NOT NULL UNIQUE,
    status VARCHAR(16) NOT NULL CHECK (status IN ('ACTIVE', 'CLOSED')),
    currency VARCHAR(3) NOT NULL CHECK (currency = 'KRW'),
    balance BIGINT NOT NULL CHECK (balance >= 0),
    version BIGINT NOT NULL DEFAULT 0
);

CREATE INDEX idx_account_owner_id ON account(owner_id, id);

CREATE TABLE business_tx (
    id UUID PRIMARY KEY,
    tx_type VARCHAR(32) NOT NULL,
    status VARCHAR(16) NOT NULL,
    correlation_id VARCHAR(64) NOT NULL UNIQUE,
    requested_at TIMESTAMPTZ NOT NULL,
    completed_at TIMESTAMPTZ
);

CREATE TABLE ledger_entry (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    business_tx_id UUID NOT NULL REFERENCES business_tx(id),
    account_id BIGINT NOT NULL REFERENCES account(id),
    entry_type VARCHAR(32) NOT NULL,
    amount BIGINT NOT NULL CHECK (amount > 0),
    signed_amount BIGINT NOT NULL,
    balance_after BIGINT NOT NULL CHECK (balance_after >= 0),
    reversal_of BIGINT REFERENCES ledger_entry(id),
    created_at TIMESTAMPTZ NOT NULL,
    UNIQUE (business_tx_id, account_id, entry_type)
);

CREATE INDEX idx_ledger_account_created_id
    ON ledger_entry(account_id, created_at DESC, id DESC);

CREATE TABLE idempotency_request (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    scope VARCHAR(64) NOT NULL,
    actor_id VARCHAR(64) NOT NULL,
    idempotency_key VARCHAR(128) NOT NULL,
    request_hash CHAR(64) NOT NULL,
    status VARCHAR(16) NOT NULL,
    response_status INTEGER,
    response_body TEXT,
    created_at TIMESTAMPTZ NOT NULL,
    completed_at TIMESTAMPTZ,
    UNIQUE (scope, actor_id, idempotency_key)
);

\set ON_ERROR_STOP on
INSERT INTO account(owner_id,account_no,status,currency,balance) VALUES ('w17-owner','W17-001','ACTIVE','KRW',100);
INSERT INTO business_tx(id,tx_type,status,correlation_id,requested_at,completed_at) VALUES ('00000000-0000-0000-0000-000000000017','OPENING','COMPLETED','w17-open',now(),now());
INSERT INTO ledger_entry(business_tx_id,account_id,entry_type,amount,signed_amount,balance_after,created_at) SELECT '00000000-0000-0000-0000-000000000017',id,'OPENING',100,100,100,now() FROM account WHERE account_no='W17-001';
BEGIN;
DO $g$ BEGIN IF (SELECT count(*) FROM account a LEFT JOIN(SELECT account_id,sum(signed_amount)b FROM ledger_entry GROUP BY account_id)l ON l.account_id=a.id WHERE a.balance<>coalesce(l.b,0))<>0 THEN RAISE EXCEPTION 'baseline mismatch';END IF;END $g$;
SELECT 'W17_BASELINE=0';
SAVEPOINT injected;
UPDATE account SET balance=balance+7 WHERE id=(SELECT min(id) FROM account);
DO $g$ BEGIN IF (SELECT count(*) FROM account a LEFT JOIN(SELECT account_id,sum(signed_amount)b FROM ledger_entry GROUP BY account_id)l ON l.account_id=a.id WHERE a.balance<>coalesce(l.b,0))<>1 THEN RAISE EXCEPTION 'injected mismatch not exact one';END IF;END $g$;
SELECT 'W17_INJECTED=1';
ROLLBACK TO SAVEPOINT injected;
DO $g$ BEGIN IF (SELECT count(*) FROM account a LEFT JOIN(SELECT account_id,sum(signed_amount)b FROM ledger_entry GROUP BY account_id)l ON l.account_id=a.id WHERE a.balance<>coalesce(l.b,0))<>0 THEN RAISE EXCEPTION 'rollback did not restore';END IF;END $g$;
SELECT 'W17_RESTORED=0';
ROLLBACK;

param(
 [ValidateSet('Compose','Container')][string]$Mode='Compose',
 [ValidateSet('Reconcile','Ranking','Savepoint')][string]$Drill='Reconcile',
 [string]$Container='',
 [string]$ComposeProject='w17-reconcile-lab',
 [Parameter(Mandatory=$true)][string]$ProjectRoot,
 [Parameter(Mandatory=$true)][string]$EvidenceDir,
 [string]$SqlPath=''
)
$ErrorActionPreference='Stop'
$root=Split-Path -Parent $PSScriptRoot
if(-not [IO.Path]::IsPathRooted($ProjectRoot)){throw 'ProjectRoot must be an absolute learner project path'}
if(-not [IO.Path]::IsPathRooted($EvidenceDir)){throw 'EvidenceDir must be an absolute learner-owned path'}
$project=(Resolve-Path -LiteralPath $ProjectRoot).Path
$e=[IO.Path]::GetFullPath($EvidenceDir)
New-Item -ItemType Directory -Force $e|Out-Null

if($Drill-eq'Ranking'){
 if([string]::IsNullOrWhiteSpace($SqlPath)-or-not[IO.Path]::IsPathRooted($SqlPath)){throw 'Ranking requires an absolute learner-owned -SqlPath'}
 $ranking=(Resolve-Path -LiteralPath $SqlPath).Path
 $learnerSql=Get-Content -Raw -LiteralPath $ranking
 foreach($pattern in @('\bROW_NUMBER\s*\(','(?<!DENSE_)\bRANK\s*\(','\bDENSE_RANK\s*\(','ORDER\s+BY\s+amount\s+DESC','account_id')){
  if($learnerSql-cnotmatch $pattern){throw "ranking.sql missing required construct: $pattern"}
 }
 if($learnerSql-cmatch'(?i)\b(TODO|TBD|FILL_ME|PLACEHOLDER)\b'){throw 'ranking.sql contains an unresolved placeholder'}
 $sql=$learnerSql
}else{
 $v001=Get-Content -Raw (Join-Path $project 'src/main/resources/db/migration/V001__common.sql')
 $drillSql=Get-Content -Raw (Join-Path $root 'sql/w17/reconcile-drill.sql')
 $sql="DO `$ddl`$ BEGIN IF EXISTS(SELECT 1 FROM pg_namespace WHERE nspname='w17_drill') THEN EXECUTE 'DROP SCHEMA w17_drill CASCADE'; END IF; END `$ddl`$; CREATE SCHEMA w17_drill; SET search_path TO w17_drill;`n$v001`n$drillSql"
}

$compose=Join-Path $root 'compose.yaml'
$owned=$false
try{
 if($Mode-eq'Compose'){
  if($Container){throw 'W17 Compose mode rejects -Container'}
  if([string]::IsNullOrWhiteSpace($env:FCL_DB_PASSWORD)){$env:FCL_DB_PASSWORD='w17-disposable-password'}
  & docker compose -f $compose -p $ComposeProject up -d --wait db
  if($LASTEXITCODE-ne 0){throw "W17 compose up exit=$LASTEXITCODE"}
  $owned=$true
  $Container=(& docker compose -f $compose -p $ComposeProject ps -q db).Trim()
 }elseif([string]::IsNullOrWhiteSpace($Container)){throw 'W17 Container mode requires -Container'}
 if([string]::IsNullOrWhiteSpace($Container)){throw 'W17 PostgreSQL container resolution failed'}

 if($Drill-eq'Ranking'){
  $out=@($sql|docker exec -i $Container psql -X -q -A -t -F '|' -v ON_ERROR_STOP=1 -U app -d financial_core 2>&1)
  $exit=$LASTEXITCODE
  if($exit-ne0){throw "W17 ranking psql exit=$exit`n$($out-join"`n")"}
  $actual=@($out|ForEach-Object{([string]$_)-replace'\s',''}|Where-Object{$_-match'^\d+\|\d+\|\d+\|\d+\|\d+$'})
  $expected=@('1|100|1|1|1','2|100|2|1|1','3|80|3|3|2','4|70|4|4|3')
  if(Compare-Object $expected $actual){throw "W17 ranking exact rows mismatch expected=$expected actual=$actual"}
  $sourceHash=(Get-FileHash -LiteralPath $ranking -Algorithm SHA256).Hash.ToLowerInvariant()
  $target=Join-Path $e 'ranking-result.txt'
  $lines=@('W17_RANKING_RESULT','rows=4','ties=rank-1-1-dense-1-1',"source_sha256=$sourceHash",'native_exit=0')
  $lines+=@($actual|ForEach-Object{"row=$_"})
  $lines|Set-Content -Encoding utf8 $target
  "W17_RANKING_GREEN rows=4 ties=ROW_NUMBER:1,2 RANK:1,1,3 DENSE_RANK:1,1,2 source_sha256=$sourceHash native_exit=0 cleanup=1"
 }else{
  $out=@($sql|docker exec -i $Container psql -v ON_ERROR_STOP=1 -U app -d financial_core 2>&1)
  $exit=$LASTEXITCODE
  $name=if($Drill-eq'Savepoint'){'savepoint-transcript.txt'}else{'reconcile-transcript.txt'}
  $target=Join-Path $e $name
  $out|Set-Content -Encoding utf8 $target
  if($exit-ne 0){throw "W17 psql exit=$exit`n$($out-join"`n")"}
  $text=$out-join"`n"
  foreach($marker in @('W17_BASELINE=0','W17_INJECTED=1','W17_RESTORED=0')){
   if($text-cnotmatch [regex]::Escape($marker)){throw "missing $marker"}
  }
  $hash=(Get-FileHash -LiteralPath $target -Algorithm SHA256).Hash.ToLowerInvariant()
  if($Drill-eq'Savepoint'){
   "W17_SAVEPOINT_GREEN injected=1 restored=0 transcript_sha256=$hash native_exit=0 cleanup=1"
  }else{
   "W17_RECONCILE_GREEN baseline=0 injected=1 restored=0 transcript_sha256=$hash native_exit=0 cleanup=1"
  }
 }
}finally{
 if($owned){
  & docker compose -f $compose -p $ComposeProject down -v|Out-Null
  if($LASTEXITCODE-ne 0){throw 'W17 compose cleanup failed'}
 }
}