# Phase 1: DDL↔코드 정합성 수정 지침서

**작성일**: 2026-03-16
**기준**: `CRYPTOMENTS_V2_DDL.sql` v1.4 (registration_status COMMENT 반영 완료)
**대상**: `node-service/packages/` (VS Code에서 실행)
**예상 소요**: 1.5시간
**선행 작업**: DDL `registration_status` COMMENT 변경 → 로컬 DB 반영

```sql
-- 로컬 DB 적용 (migration)
ALTER TABLE relayer_wallets
  MODIFY COLUMN registration_status VARCHAR(20) NOT NULL DEFAULT 'PENDING'
    COMMENT 'PENDING / REGISTERING / REGISTERED / DEREGISTERING / REVOKED';
```

---

## 작업 순서 (의존성 기준)

```
A-1 → A-3 → A-4 → A-5 → A-6 → A-2 → 빌드 검증
```

A-1이 가장 영향 범위 넓고, A-2는 단순 삭제라 마지막.

---

## A-1. RelayerStatus 분리 (HIGH)

### 현재 코드

```typescript
// types/wallet.ts:112
export type RelayerStatus = 'REGISTERING' | 'ACTIVE' | 'INACTIVE' | 'DEREGISTERING';
```

하나의 `status` 필드로 운영 상태와 등록 상태를 혼용 중.

### DDL 정의 (2개 별도 컬럼)

```sql
status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE'
    COMMENT 'ACTIVE / PAUSED',

registration_status VARCHAR(20) NOT NULL DEFAULT 'PENDING'
    COMMENT 'PENDING / REGISTERING / REGISTERED / DEREGISTERING / REVOKED',
```

### 수정 대상 4개 파일

#### 1) `packages/common/src/types/wallet.ts`

```typescript
// ── Before ──
export type RelayerStatus = 'REGISTERING' | 'ACTIVE' | 'INACTIVE' | 'DEREGISTERING';

export interface RelayerWallet {
  id: number;
  network_id: number;
  wallet_address_id: number;
  address: string;
  relayer_role: RelayerRole;
  status: RelayerStatus;
  priority: number;
  current_pending_tx_count: number;
  max_pending_tx_count: number;
}

// ── After ──
export type RelayerOperationStatus = 'ACTIVE' | 'PAUSED';
export type RegistrationStatus = 'PENDING' | 'REGISTERING' | 'REGISTERED' | 'DEREGISTERING' | 'REVOKED';

export interface RelayerWallet {
  id: number;
  network_id: number;
  wallet_address_id: number;
  address: string;                        // JOIN으로 추가
  relayer_role: RelayerRole;
  contract_address: string | null;        // 등록 대상 컨트랙트
  registration_tx_hash: string | null;    // 등록 TX 해시
  registration_status: RegistrationStatus; // 온체인 등록 상태
  status: RelayerOperationStatus;          // 운영 상태
  priority: number;
  current_pending_tx_count: number;
  max_pending_tx_count: number;
}
```

> **주의**: 기존 `RelayerStatus` → `RelayerOperationStatus`로 이름 변경. import 하는 모든 파일에서 수정 필요.

#### 2) `packages/common/src/db/repositories/RelayerWalletRepo.ts`

**변경 A**: import 수정

```typescript
// Before
import type { RelayerWallet, RelayerRole, RelayerStatus } from '../../types/wallet.js';

// After
import type {
  RelayerWallet, RelayerRole,
  RelayerOperationStatus, RegistrationStatus,
} from '../../types/wallet.js';
```

**변경 B**: `insert()` — `status` 컬럼은 DB DEFAULT('ACTIVE') 사용, `registration_status` 추가

```typescript
// Before
async insert(data: {
  network_id: number;
  wallet_address_id: number;
  relayer_role: RelayerRole;
  status: RelayerStatus;
}): Promise<number> {
  const result = await execute(
    `INSERT INTO relayer_wallets (network_id, wallet_address_id, relayer_role, status)
     VALUES (?, ?, ?, ?)`,
    [data.network_id, data.wallet_address_id, data.relayer_role, data.status],
  );
  return result.insertId;
}

// After
async insert(data: {
  network_id: number;
  wallet_address_id: number;
  relayer_role: RelayerRole;
  registration_status?: RegistrationStatus;   // 기본 'PENDING'
}): Promise<number> {
  const regStatus = data.registration_status ?? 'PENDING';
  const result = await execute(
    `INSERT INTO relayer_wallets (network_id, wallet_address_id, relayer_role, registration_status)
     VALUES (?, ?, ?, ?)`,
    [data.network_id, data.wallet_address_id, data.relayer_role, regStatus],
  );
  return result.insertId;
}
```

**변경 C**: `updateStatus()` → `updateOperationStatus()` + `updateRegistrationStatus()` 분리

```typescript
// Before
async updateStatus(id: number, status: RelayerStatus): Promise<void> {
  await execute(
    'UPDATE relayer_wallets SET status = ? WHERE id = ?',
    [status, id],
  );
}

// After
async updateOperationStatus(id: number, status: RelayerOperationStatus): Promise<void> {
  await execute(
    'UPDATE relayer_wallets SET status = ? WHERE id = ?',
    [status, id],
  );
},

async updateRegistrationStatus(id: number, status: RegistrationStatus): Promise<void> {
  await execute(
    'UPDATE relayer_wallets SET registration_status = ? WHERE id = ?',
    [status, id],
  );
},
```

**변경 D**: `updateRegistration()` — 타입에 `REGISTERING`/`DEREGISTERING` 추가

```typescript
// Before
async updateRegistration(
  id: number,
  contractAddress: string,
  registrationTxHash: string,
  registrationStatus: 'PENDING' | 'REGISTERED' | 'REVOKED',
): Promise<void> { ... }

// After
async updateRegistration(
  id: number,
  contractAddress: string,
  registrationTxHash: string,
  registrationStatus: RegistrationStatus,
): Promise<void> { ... }
```

**변경 E**: `findActiveRelayer()` — `registration_status = 'REGISTERED'` 조건 추가

```sql
-- Before
WHERE rw.network_id = ?
  AND rw.relayer_role = ?
  AND rw.status = 'ACTIVE'
  AND rw.current_pending_tx_count < rw.max_pending_tx_count

-- After
WHERE rw.network_id = ?
  AND rw.relayer_role = ?
  AND rw.status = 'ACTIVE'
  AND rw.registration_status = 'REGISTERED'
  AND rw.current_pending_tx_count < rw.max_pending_tx_count
```

#### 3) `packages/relayer-api/src/routes/relayer.ts`

현재 `updateStatus(id, 'REGISTERING')`, `updateStatus(id, 'DEREGISTERING')` 등으로 혼용 중.

**매핑 규칙**:

| 기존 호출 | 신규 호출 |
|-----------|----------|
| `updateStatus(id, 'REGISTERING')` | `updateRegistrationStatus(id, 'REGISTERING')` |
| `updateStatus(id, 'ACTIVE')` | `updateOperationStatus(id, 'ACTIVE')` + `updateRegistrationStatus(id, 'REGISTERED')` |
| `updateStatus(id, 'INACTIVE')` | `updateOperationStatus(id, 'PAUSED')` |
| `updateStatus(id, 'DEREGISTERING')` | `updateRegistrationStatus(id, 'DEREGISTERING')` |

**register 라우트** (현재 line ~57-109):

```typescript
// Before
const relayerId = await relayerWalletRepo.insert({
  network_id: networkId,
  wallet_address_id: walletAddressId,
  relayer_role: role,
  status: 'REGISTERING',
});

// After
const relayerId = await relayerWalletRepo.insert({
  network_id: networkId,
  wallet_address_id: walletAddressId,
  relayer_role: role,
  registration_status: 'REGISTERING',
});
```

```typescript
// register 성공 시 (현재 line ~109)
// Before
await relayerWalletRepo.updateStatus(relayerId, 'ACTIVE');

// After — 두 상태 모두 업데이트
await relayerWalletRepo.updateRegistrationStatus(relayerId, 'REGISTERED');
// status는 이미 DEFAULT 'ACTIVE'이므로 별도 업데이트 불필요
```

```typescript
// register 실패 시 (현재 line ~75, 81)
// Before
await relayerWalletRepo.updateStatus(relayerId, 'INACTIVE');

// After
await relayerWalletRepo.updateRegistrationStatus(relayerId, 'REVOKED');
await relayerWalletRepo.updateOperationStatus(relayerId, 'PAUSED');
```

**unregister 라우트** (현재 line ~147-198):

```typescript
// Before
await relayerWalletRepo.updateStatus(relayerId, 'DEREGISTERING');

// After
await relayerWalletRepo.updateRegistrationStatus(relayerId, 'DEREGISTERING');
```

```typescript
// unregister 성공 후 INACTIVE 처리 (현재 line ~164, 171, 198)
// Before
await relayerWalletRepo.updateStatus(relayerId, 'INACTIVE');

// After
await relayerWalletRepo.updateRegistrationStatus(relayerId, 'REVOKED');
await relayerWalletRepo.updateOperationStatus(relayerId, 'PAUSED');
```

#### 4) `packages/blockchain-api/src/services/ContractDeployService.ts`

`addRelayer` 성공 시 (현재 line ~199):

```typescript
// Before
await relayerWalletRepo.updateRegistration(
  relayerWalletId, contractAddress, tx.hash, 'REGISTERED',
);

// After — 동일 (updateRegistration의 타입만 RegistrationStatus로 확장됨)
await relayerWalletRepo.updateRegistration(
  relayerWalletId, contractAddress, tx.hash, 'REGISTERED',
);
```

`addRelayer` TX 실패 롤백 (현재 line ~194):

```typescript
// Before
await relayerWalletRepo.updateStatus(relayerWalletId, 'INACTIVE');

// After
await relayerWalletRepo.updateRegistrationStatus(relayerWalletId, 'REVOKED');
await relayerWalletRepo.updateOperationStatus(relayerWalletId, 'PAUSED');
```

`removeRelayer` 성공 시 (현재 line ~251):

```typescript
// Before
await relayerWalletRepo.updateRegistration(
  relayerWalletId, contractAddress, tx.hash, 'REVOKED',
);

// After — 동일
await relayerWalletRepo.updateRegistration(
  relayerWalletId, contractAddress, tx.hash, 'REVOKED',
);
// + 운영 상태도 PAUSED로
await relayerWalletRepo.updateOperationStatus(relayerWalletId, 'PAUSED');
```

---

## A-3. CollectionStatus: `COMPLETED` → `CONFIRMED` (MEDIUM)

### 수정 대상 3개 파일

#### 1) `packages/common/src/types/tx.ts`

```typescript
// Before
export type CollectionStatus =
  | 'QUEUED'
  | 'DEFERRED'
  | 'COLLECTING'
  | 'BROADCASTING'
  | 'COMPLETED'
  | 'FAILED';

// After
export type CollectionStatus =
  | 'QUEUED'
  | 'DEFERRED'
  | 'COLLECTING'
  | 'BROADCASTING'
  | 'CONFIRMED'
  | 'FAILED';
```

#### 2) `packages/common/src/db/repositories/CollectionQueueRepo.ts`

현재 코드에서 `'COMPLETED'` 리터럴을 직접 사용하는 곳은 없음 (상태 업데이트는 파라미터 전달).
하지만 `updateStatus()` 호출부(CollectionPoller)에서 `'COMPLETED'` → `'CONFIRMED'` 변경 필요.

#### 3) `packages/relayer-api/src/services/CollectionPoller.ts`

Poller에서 TX 확인 성공 시 상태 업데이트 부분 확인 필요:

```typescript
// 있다면: 'COMPLETED' → 'CONFIRMED' 변경
await collectionQueueRepo.updateStatus(entry.id, 'CONFIRMED');
```

> **참고**: 현재 grep에서 CollectionPoller에 `COMPLETED` 리터럴이 없음. `BROADCASTING` 이후 상태 전이가 아직 미구현일 수 있으므로, Poller 코드를 열어 TX confirm 후 최종 상태가 뭔지 확인할 것.

---

## A-4. WithdrawalType 값 변경 (MEDIUM)

### 수정 대상 3개 파일

#### 1) `packages/common/src/types/tx.ts`

```typescript
// Before
export type WithdrawalType = 'USER' | 'REFUND' | 'PARTNER';

// After
export type WithdrawalType = 'USER_PAYOUT' | 'REFUND' | 'PARTNER_WITHDRAW';
```

#### 2) `packages/common/src/db/repositories/WithdrawalRepo.ts`

`withdrawal_type`을 직접 비교/삽입하는 쿼리가 있는지 확인. 현재 Repo에는 status 기반 조회만 있으므로 타입 변경만으로 충분할 수 있음.

#### 3) `packages/relayer-api/src/services/WithdrawalPoller.ts`

Poller에서 `withdrawal_type`으로 분기하는 로직이 있다면 값 변경:

```typescript
// Before
if (withdrawal.withdrawal_type === 'USER') { ... }

// After
if (withdrawal.withdrawal_type === 'USER_PAYOUT') { ... }
```

> **전체 검색**: `grep -r "'USER'" --include="*.ts"`, `grep -r "'PARTNER'" --include="*.ts"` 로 다른 곳에서 WithdrawalType 리터럴 사용 확인할 것. (현재 grep 결과: types/tx.ts 외에 사용처 없음)

---

## A-5. WalletKey: `key_version` → `encryption_key_version` (LOW)

### 수정 대상 2개 파일

#### 1) `packages/common/src/types/wallet.ts`

```typescript
// Before (line 61)
key_version: string;

// After
encryption_key_version: string;
```

#### 2) `packages/common/src/db/repositories/WalletKeyRepo.ts`

**변경 A**: `findByAddressId()` — ORDER BY 컬럼명

```typescript
// Before
'SELECT encrypted_private_key FROM wallet_keys WHERE wallet_address_id = ? ORDER BY key_version DESC LIMIT 1',

// After
'SELECT encrypted_private_key FROM wallet_keys WHERE wallet_address_id = ? ORDER BY encryption_key_version DESC LIMIT 1',
```

**변경 B**: `insert()` — INSERT 컬럼명

```typescript
// Before
async insert(data: {
  wallet_address_id: number;
  encrypted_private_key: string;
  key_version?: string;
}): Promise<number> {
  const result = await execute(
    `INSERT INTO wallet_keys (wallet_address_id, encrypted_private_key, key_version)
     VALUES (?, ?, ?)`,
    [data.wallet_address_id, data.encrypted_private_key, data.key_version ?? 'v1'],
  );

// After
async insert(data: {
  wallet_address_id: number;
  encrypted_private_key: string;
  encryption_key_version?: string;
}): Promise<number> {
  const result = await execute(
    `INSERT INTO wallet_keys (wallet_address_id, encrypted_private_key, encryption_key_version)
     VALUES (?, ?, ?)`,
    [data.wallet_address_id, data.encrypted_private_key, data.encryption_key_version ?? 'v1'],
  );
```

---

## A-6. WalletIndexManager: `last_index` → `current_index` (LOW)

### 수정 대상 2개 파일

#### 1) `packages/common/src/types/wallet.ts`

```typescript
// Before (line 76)
last_index: number;

// After
current_index: number;
```

#### 2) `packages/common/src/db/repositories/WalletIndexManagerRepo.ts`

3곳 변경:

```typescript
// 변경 1: INSERT (line 23)
// Before
`INSERT INTO wallet_index_manager (hd_wallet_id, network_id, wallet_type, last_index)
 VALUES (?, ?, ?, 0)`,

// After
`INSERT INTO wallet_index_manager (hd_wallet_id, network_id, wallet_type, current_index)
 VALUES (?, ?, ?, 0)`,


// 변경 2: nextIndex 계산 (line 30)
// Before
const nextIndex = Number(rows[0].last_index) + 1;

// After
const nextIndex = Number(rows[0].current_index) + 1;


// 변경 3: UPDATE (line 31-33)
// Before
`UPDATE wallet_index_manager SET last_index = ?
 WHERE hd_wallet_id = ? AND network_id = ? AND wallet_type = ?`,

// After
`UPDATE wallet_index_manager SET current_index = ?
 WHERE hd_wallet_id = ? AND network_id = ? AND wallet_type = ?`,
```

---

## A-2. WalletAddressStatus: `SUSPENDED` 제거 (LOW)

### 수정 대상 1개 파일

#### `packages/common/src/types/wallet.ts`

```typescript
// Before (line 26)
export type WalletAddressStatus = 'ACTIVE' | 'INACTIVE' | 'SUSPENDED';

// After
export type WalletAddressStatus = 'ACTIVE' | 'INACTIVE';
```

> DDL에 `SUSPENDED`가 없으므로 단순 제거. 코드에서 `'SUSPENDED'` 리터럴을 사용하는 곳이 있는지 확인할 것.

---

## 빌드 검증

모든 수정 완료 후:

```bash
cd node-service
pnpm run build
```

5개 패키지 모두 SUCCESS 확인. TypeScript 컴파일 에러가 발생하면:

1. `RelayerStatus` → `RelayerOperationStatus` 이름 변경으로 인한 import 누락이 가장 가능성 높음
2. `updateStatus()` → `updateOperationStatus()` / `updateRegistrationStatus()` 호출부 미수정
3. `'COMPLETED'` / `'USER'` / `'PARTNER'` 리터럴 잔존

---

## 체크리스트

- [ ] DDL migration SQL 실행 (`registration_status` COMMENT)
- [ ] A-1: `types/wallet.ts` — RelayerOperationStatus + RegistrationStatus 분리, RelayerWallet 인터페이스 확장
- [ ] A-1: `RelayerWalletRepo.ts` — import, insert, updateStatus 분리, updateRegistration 타입, findActiveRelayer 쿼리
- [ ] A-1: `relayer.ts` — register/unregister 라우트의 상태 업데이트 호출 변경
- [ ] A-1: `ContractDeployService.ts` — addRelayer/removeRelayer 상태 업데이트 호출 변경
- [ ] A-3: `types/tx.ts` — COMPLETED → CONFIRMED
- [ ] A-3: `CollectionPoller.ts` — COMPLETED 리터럴 있으면 변경
- [ ] A-4: `types/tx.ts` — USER → USER_PAYOUT, PARTNER → PARTNER_WITHDRAW
- [ ] A-4: `WithdrawalPoller.ts` — 리터럴 있으면 변경
- [ ] A-5: `types/wallet.ts` — key_version → encryption_key_version
- [ ] A-5: `WalletKeyRepo.ts` — ORDER BY, INSERT 컬럼명 변경
- [ ] A-6: `types/wallet.ts` — last_index → current_index
- [ ] A-6: `WalletIndexManagerRepo.ts` — INSERT, SELECT, UPDATE 컬럼명 변경 (3곳)
- [ ] A-2: `types/wallet.ts` — SUSPENDED 제거
- [ ] `pnpm run build` — 5개 패키지 SUCCESS