# 데이터베이스

## 시스템 요구 사항

* 시스템/인프라 구성도

![](/files/dna0nnO882LH2caPJr3P)

* VerifyVASP에서 제공하는 Enclave 서버에서 사용하는 데이터베이스가 필요합니다.
* 지원 가능한 DB : MySQL, PostgreSQL, MSSQL, Oracle
* MySQL 권장합니다.

## 테이블 설명

* 기본적으로 우선 4 개의 테이블이 필요합니다.
  * `verifications` : 사용자 검증 API에 대한 요청/응답 저장합니다.
  * `counter_party_keys` : 수신 VASP의 Public Key 저장합니다.
  * `own_keys` : 송신 VASP의 Public Key 저장합니다.
  * `commands` : 비동기 방식으로 호출되는 API 들의 중간 요청 저장합니다.
* 추가적으로, 사용하려는 기능에 따라 다음의 테이블들이 필요합니다.
  * `chainalysis_sanction_results` : Chainalysis Sanction API 호출 이력 정보를 저장합니다.
  * `chainalysis_kyt_results` : Chainalysis KYT API 호출 이력 정보를 저장합니다.
  * `chainalysis_kyt_alerts` : Chainalysis KYT API 호출에 의해 생성된 alert 정보들을 저장합니다.
  * `refinitiv_wco_results` : Refinitiv World Check One API 호출 이력 정보를 저장합니다.

## 테이블 백업, 복원, 복구 정책

* `verifications` 테이블은 Travel Rule 검증 히스토리가 저장되므로 매일 백업할 것을 권고합니다.
* `own_keys` 테이블은 주기적으로 백업 바랍니다.
* `chainalysis_sanction_results`, `chainalysis_kyt_results`, `chainalysis_kyt_alerts`, `refinitiv_wco_results` 또한 주기적으로 백업 바랍니다.
* `counter_party_keys` 테이블은 Caching 용도로 사용되는 테이블로 복구할 필요가 없습니다.
* `commands` 테이블은 비동기 방식으로 호출되는 API들의 일시적인 요청이 저장되는 테이블이므로 백업 및 복구가 필요 없습니다.

## 테이블 크기 (스토리지 사용량)

* verifications 테이블 : 요청 당 약 4 \~ 5 KB
* counter\_party\_keys 테이블 : beneficiary 주소 당 약 1 KB
* own\_keys : PerVerification 키 타입을 사용한 경우 요청 당 약 1 KB
* commands : 비동기 API 요청 당 약 1 \~ 5 KB
* chainalysis\_sanction\_results : 요청 당 약 1 \~ 2 KB
* chainalysis\_kyt\_results : 요청 당 약 2 \~ 3 KB
* chainalysis\_kyt\_alerts : 요청 당 약 0 \~ 3 KB
* refinitiv\_wco\_results : 요청 당 약 2 \~ 3 KB

## 테이블 정의

### verifications 테이블 (필수) <a href="#verifications" id="verifications"></a>

* 출금 요청 시, Travel Rule을 통해 사전 검증에 대한 내용을 기록합니다.
* **POST /verifications API**를 호출했을 때의, 요청/응답에 대한 내용을 verifications 테이블에 기록합니다.
* 암호화되어 저장되는 컬럼은 아래와 같습니다.
  * ivms101\_originator
  * ivms101\_originating\_vasp
  * ivms101\_beneficiary
  * ivms101\_beneficiary\_vasp

#### 테이블 생성

{% tabs %}
{% tab title="MySQL" %}

```sql
CREATE TABLE `verifications` (
  `verification_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Verification ID',
  `verification_uuid` varchar(40) NOT NULL COMMENT 'Verification UUID',
  `result` enum('WAIT', 'VERIFIED', 'UNKNOWN', 'DENIED', 'ERROR', 'PENDING', 'TRANSFER_ERROR') NOT NULL DEFAULT 'WAIT' COMMENT 'Verification Result',
  `reason` varchar(256) DEFAULT NULL COMMENT 'Reason',
  `message` varchar(1024) DEFAULT NULL COMMENT 'Additional information about the reason',
  `network` varchar(128) DEFAULT NULL COMMENT 'Network for token transfer',
  `symbol` varchar(16) DEFAULT NULL COMMENT 'Symbol',
  `amount` varchar(128) DEFAULT NULL COMMENT 'Amount',
  `trade_price` varchar(128) DEFAULT NULL COMMENT 'Trading Price',
  `trade_currency` varchar(128) DEFAULT NULL COMMENT 'Trading Currency',
  `trade_iso_datetime` datetime DEFAULT NULL COMMENT 'Trading time',
  `is_exceeding_threshold` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Whether the threshold has been exceeded',
  `tx_hash` varchar(128) DEFAULT NULL COMMENT 'Tx hash',
  `vout` varchar(128) DEFAULT NULL COMMENT 'Vout',
  `originating_vasp_id` bigint(20) unsigned DEFAULT NULL COMMENT 'Originating VASP ID',
  `originator_account_number` varchar(256) DEFAULT NULL COMMENT 'Originator Account Address or ID',
  `ivms101_originator` text(65535) DEFAULT NULL COMMENT 'Originator Information',
  `ivms101_originating_vasp` text(65535) DEFAULT NULL COMMENT 'Originating VASP Information',
  `beneficiary_vasp_id` bigint(20) unsigned DEFAULT NULL COMMENT 'Beneficiary VASP ID',
  `beneficiary_account_number` varchar(256) DEFAULT NULL COMMENT 'Beneficiary Account Address or ID',
  `ivms101_beneficiary` text(65535) DEFAULT NULL COMMENT 'Beneficiary Information',
  `ivms101_beneficiary_vasp` text(65535) DEFAULT NULL COMMENT 'Beneficiary VASP Information',
  `verified_at` datetime(3) DEFAULT NULL COMMENT 'Verified at.',
  `ordered_at` datetime(3) DEFAULT NULL COMMENT 'Ordered at.',
  `created_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT 'Created at.',
  PRIMARY KEY (`verification_id`),
  UNIQUE KEY `uk_verifications_verification_uuid` (`verification_uuid`),
  INDEX `idx_verifications_originator_account` (`originator_account_number`),
  INDEX `idx_verifications_beneficiary_account` (`beneficiary_account_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
```

{% endtab %}

{% tab title="PostgreSQL" %}

```sql
CREATE TYPE enum_result AS ENUM ('WAIT', 'VERIFIED', 'UNKNOWN', 'DENIED', 'ERROR', 'PENDING', 'TRANSFER_ERROR');

CREATE TABLE verifications (
  verification_id SERIAL NOT NULL PRIMARY KEY,
  verification_uuid varchar(40) NOT NULL,
  UNIQUE(verification_uuid),
  result enum_result DEFAULT 'WAIT',
  reason varchar(256) DEFAULT NULL,
  message varchar(1024) DEFAULT NULL,
  network varchar(256) DEFAULT NULL,
  symbol varchar(16) DEFAULT NULL,
  amount varchar(128) DEFAULT NULL,
  trade_price varchar(128) DEFAULT NULL,
  trade_currency varchar(128) DEFAULT NULL,
  trade_iso_datetime timestamp DEFAULT NULL,
  is_exceeding_threshold boolean DEFAULT true NOT NULL,
  tx_hash varchar(128) DEFAULT NULL,
  vout varchar(128) DEFAULT NULL,
  originating_vasp_id numeric(20) DEFAULT NULL,
  originator_account_number varchar(256) DEFAULT NULL,
  ivms101_originator varchar(65535) DEFAULT NULL,
  ivms101_originating_vasp varchar(65535) DEFAULT NULL,
  beneficiary_vasp_id numeric(20) DEFAULT NULL,
  beneficiary_account_number varchar(256) DEFAULT NULL,
  ivms101_beneficiary varchar(65535) DEFAULT NULL,
  ivms101_beneficiary_vasp varchar(65535) DEFAULT NULL,
  verified_at timestamp DEFAULT NULL,
  ordered_at timestamp DEFAULT NULL,
  created_at timestamp DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_verifications_originator_account ON verifications(originator_account_number);
CREATE INDEX idx_verifications_beneficiary_account ON verifications(beneficiary_account_number);
```

{% endtab %}

{% tab title="MSSQL" %}

```sql
CREATE TABLE verifications (
  verification_id BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  verification_uuid nvarchar(40) NOT NULL UNIQUE,
  result nvarchar(20) DEFAULT 'WAIT' NOT NULL check (result in ('WAIT', 'VERIFIED', 'UNKNOWN', 'DENIED', 'ERROR', 'PENDING', 'TRANSFER_ERROR')),
  reason nvarchar(256) DEFAULT NULL,
  message nvarchar(1024) DEFAULT NULL,
  network nvarchar(128) DEFAULT NULL,
  symbol nvarchar(16) DEFAULT NULL,
  amount nvarchar(128) DEFAULT NULL,
  trade_price nvarchar(128) DEFAULT NULL,
  trade_currency nvarchar(128) DEFAULT NULL,
  trade_iso_datetime datetime2 DEFAULT NULL,
  is_exceeding_threshold tinyint DEFAULT 1 NOT NULL,
  tx_hash nvarchar(128) DEFAULT NULL,
  vout nvarchar(128) DEFAULT NULL,
  originating_vasp_id BIGINT check (originating_vasp_id > 0) DEFAULT NULL,
  originator_account_number nvarchar(256) DEFAULT NULL,
  ivms101_originator nvarchar(MAX) DEFAULT NULL,
  ivms101_originating_vasp nvarchar(MAX) DEFAULT NULL,
  beneficiary_vasp_id BIGINT check (beneficiary_vasp_id > 0) DEFAULT NULL,
  beneficiary_account_number nvarchar(256) DEFAULT NULL,
  ivms101_beneficiary nvarchar(MAX) DEFAULT NULL,
  ivms101_beneficiary_vasp nvarchar(MAX) DEFAULT NULL,
  verified_at datetime2(3) DEFAULT NULL,
  ordered_at datetime2(3) DEFAULT NULL,
  created_at datetime2(3) DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_verifications_originator_account ON verifications(originator_account_number);
CREATE INDEX idx_verifications_beneficiary_account ON verifications(beneficiary_account_number);
```

{% endtab %}

{% tab title="Oracle" %}

```sql
CREATE TABLE "verifications" (
  "verification_id" number(20) NOT NULL,
  "verification_uuid" varchar2(40) NOT NULL,
  "result" varchar2(20) DEFAULT 'WAIT' NOT NULL check ("result" in ('WAIT', 'VERIFIED', 'UNKNOWN', 'DENIED', 'ERROR', 'PENDING', 'TRANSFER_ERROR')),
  "reason" varchar2(256) DEFAULT NULL,
  "message" varchar2(1024) DEFAULT NULL,
  "network" varchar2(128) DEFAULT NULL,
  "symbol" varchar2(16) DEFAULT NULL,
  "amount" varchar2(128) DEFAULT NULL,
  "trade_price" varchar2(128) DEFAULT NULL,
  "trade_currency" varchar2(128) DEFAULT NULL,
  "trade_iso_datetime" date DEFAULT NULL,
  "is_exceeding_threshold" number(1) DEFAULT 1,
  "tx_hash" varchar2(128) DEFAULT NULL,
  "vout" varchar2(128) DEFAULT NULL,
  "originating_vasp_id" varchar2(20) DEFAULT NULL,
  "originator_account_number" varchar2(256) DEFAULT NULL,
  "ivms101_originator" clob DEFAULT NULL,
  "ivms101_originating_vasp" clob DEFAULT NULL,
  "beneficiary_vasp_id" varchar2(20) DEFAULT NULL,
  "beneficiary_account_number" varchar2(256) DEFAULT NULL,
  "ivms101_beneficiary" clob DEFAULT NULL,
  "ivms101_beneficiary_vasp" clob DEFAULT NULL,
  "verified_at" timestamp(3) DEFAULT NULL,
  "ordered_at" timestamp(3) DEFAULT NULL,
  "created_at" timestamp(3) DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT "pk_verification_id" PRIMARY KEY ("verification_id"),
  CONSTRAINT "uk_verification_uuid" UNIQUE("verification_uuid")
);

CREATE INDEX "idx_originator_account" ON "verifications"("originator_account_number");
CREATE INDEX "idx_beneficiary_account" ON "verifications"("beneficiary_account_number");

CREATE SEQUENCE "verifications_id_seq";

CREATE OR REPLACE TRIGGER verifications_trigger
BEFORE INSERT ON "verifications"
FOR EACH ROW
BEGIN
  SELECT "verifications_id_seq".nextval
  INTO :new."verification_id"
  FROM dual;
END;
/
```

{% endtab %}
{% endtabs %}

### counter\_party\_keys 테이블 (필수) <a href="#counter-party-keys" id="counter-party-keys"></a>

* 검증 과정에서 개인정보 암호화에 사용되는 beneficiary VASP의 Public Key를 저장합니다.
* Originating VASP가 정한 Key Type에 따라, beneficiary VASP의 Public Key를 기록합니다.
* **POST /verifications API**를 호출했을 때, beneficiary VASP에 대한 public key를 캐싱하고 있는지를 검사한 뒤, 없으면 자동으로 beneficiary VASP에 요청하여 이 테이블에 저장합니다.

#### 테이블 생성

{% tabs %}
{% tab title="MySQL" %}

```sql
CREATE TABLE `counter_party_keys` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Key ID',
  `type` enum('PerVasp', 'PerAddress', 'PerVerification') NOT NULL COMMENT 'Key types',
  `vasp_id` bigint(20) unsigned NOT NULL COMMENT 'Counter party VASP ID',
  `key_identifier` varchar(256) NOT NULL COMMENT 'Identifier of key (address or vaspId)',
  `public_key` varchar(256) NOT NULL COMMENT 'Public Key of counter party',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Created at.',
  PRIMARY KEY (`id`),
  UNIQUE KEY `key_uniqueness` (`vasp_id`, `key_identifier`, `type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
```

{% endtab %}

{% tab title="PostgreSQL" %}

```sql
CREATE TYPE enum_key_types AS ENUM ('PerVasp', 'PerAddress', 'PerVerification');

CREATE TABLE counter_party_keys (
  id SERIAL NOT NULL PRIMARY KEY,
  type enum_key_types NOT NULL,
  vasp_id numeric(20) NOT NULL,
  key_identifier varchar(256) NOT NULL,
  UNIQUE(vasp_id, key_identifier, type),
  public_key varchar(256) NOT NULL,
  created_at timestamp DEFAULT CURRENT_TIMESTAMP
);
```

{% endtab %}

{% tab title="MSSQL" %}

```sql
CREATE TABLE counter_party_keys (
  id BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  type nvarchar(20) NOT NULL check (type in ('PerVasp', 'PerAddress', 'PerVerification')),
  vasp_id BIGINT check (vasp_id > 0) NOT NULL,
  key_identifier nvarchar(256) NOT NULL,
  public_key nvarchar(256) NOT NULL,
  created_at datetime2 DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT key_uniq_counter_party_keys UNIQUE(vasp_id, key_identifier, type)
);
```

{% endtab %}

{% tab title="Oracle" %}

```sql
CREATE TABLE "counter_party_keys" (
  "id" number(20) NOT NULL,
  "type" varchar2(20) NOT NULL check ("type" in ('PerVasp', 'PerAddress', 'PerVerification')),
  "vasp_id" varchar2(20) NOT NULL,
  "key_identifier" varchar2(256) NOT NULL,
  "public_key" varchar2(256) NOT NULL,
  "created_at" date DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT "pk_counter_party_keys_id" PRIMARY KEY ("id"),
  CONSTRAINT "key_uniq_counter_party_keys" UNIQUE ("vasp_id", "key_identifier", "type")
);

CREATE SEQUENCE "counter_party_keys_id_seq";

CREATE OR REPLACE TRIGGER counter_party_keys_trigger
BEFORE INSERT ON "counter_party_keys"
FOR EACH ROW
BEGIN
  SELECT "counter_party_keys_id_seq".nextval
  INTO :new."id"
  FROM dual;
END;
/
```

{% endtab %}
{% endtabs %}

### own\_keys 테이블 (필수) <a href="#own-keys" id="own-keys"></a>

* 검증 과정에서 개인정보 암호화에 사용되는 Own VASP(Enclave 자신 VASP)의 public key 와 private key를 저장합니다.
* 상대 VASP에서 public key를 요청했을 때 Key Type에 따라 해당 테이블에 저장하고 있던 public key를 반환합니다.
* private key는 암호화되어 저장합니다.

#### 테이블 생성

{% tabs %}
{% tab title="MySQL" %}

```sql
CREATE TABLE `own_keys` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Key ID',
  `type` enum('PerVasp', 'PerAddress', 'PerVerification') NOT NULL COMMENT 'Key types',
  `key_identifier` varchar(256) NOT NULL COMMENT 'Identifier of key (address or type or public key)',
  `public_key` varchar(256) NOT NULL COMMENT 'Public Key',
  `private_key` varchar(256) NOT NULL COMMENT 'Private Key',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Created at.',
  PRIMARY KEY (`id`),
  UNIQUE KEY `key_uniqueness` (`key_identifier`, `type`),
  INDEX `public_key` (`public_key`, `private_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
```

{% endtab %}

{% tab title="PostgreSQL" %}

```sql
CREATE TABLE own_keys (
  id SERIAL NOT NULL PRIMARY KEY,
  type enum_key_types NOT NULL,
  key_identifier varchar(256) NOT NULL,
  UNIQUE(key_identifier, type),
  public_key varchar(256) NOT NULL,
  private_key varchar(256) NOT NULL,
  created_at timestamp DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX public_key ON own_keys(public_key, private_key);
```

{% endtab %}

{% tab title="MSSQL" %}

```sql
CREATE TABLE own_keys (
  id BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  type nvarchar(20) NOT NULL check (type in ('PerVasp', 'PerAddress', 'PerVerification')),
  key_identifier nvarchar(256) NOT NULL,
  public_key nvarchar(256) NOT NULL,
  private_key nvarchar(256) NOT NULL,
  created_at datetime2 DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT key_uniq_own_keys UNIQUE(key_identifier, type)
);

CREATE INDEX public_key ON own_keys(public_key, private_key);
```

{% endtab %}

{% tab title="Oracle" %}

```sql
CREATE TABLE "own_keys" (
  "id" number(20) NOT NULL,
  "type" varchar2(20) NOT NULL check ("type" in ('PerVasp', 'PerAddress', 'PerVerification')),
  "key_identifier" varchar2(256) NOT NULL,
  "public_key" varchar2(256) NOT NULL,
  "private_key" varchar2(256) NOT NULL,
  "created_at" date DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT "pk_own_keys_id" PRIMARY KEY ("id"),
  CONSTRAINT "key_uniq_own_keys" UNIQUE ("key_identifier", "type")
);

CREATE INDEX "idx_public_key" ON "own_keys"("public_key", "private_key");
CREATE SEQUENCE "own_keys_id_seq";

CREATE OR REPLACE TRIGGER own_keys_trigger
BEFORE INSERT ON "own_keys"
FOR EACH ROW
BEGIN
  SELECT "own_keys_id_seq".nextval
  INTO :new."id"
  FROM dual;
END;
/
```

{% endtab %}
{% endtabs %}

### commands 테이블 (필수) <a href="#commands" id="commands"></a>

* 비동기 API 호출 시 임시적인 요청이 저장되는 테이블입니다.
* enclave 내부 동작을 위해 사용되며 VASP에서는 신경 쓰지 않아도 되는 테이블입니다.

#### 테이블 생성

{% tabs %}
{% tab title="MySQL" %}

```sql
CREATE TABLE `commands` (
  `command_id` bigint(20) unsigned NOT NULL COMMENT 'Command ID',
  `command_type` varchar(32) NOT NULL COMMENT 'Command type',
  `command_body` text(65535) NOT NULL COMMENT 'Command body',
  `status` enum('CREATED', 'PROCESSING', 'DONE', 'ERROR') NOT NULL DEFAULT 'CREATED' COMMENT 'Command status',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Created at.',
  `fetched_at` datetime DEFAULT NULL COMMENT 'Fetched at.',
  `finished_at` datetime DEFAULT NULL COMMENT 'Finished at.',
  PRIMARY KEY (`command_id`),
  INDEX `status_id` (`status`, `command_id`),
  INDEX `status_finished_at` (`status`, `finished_at`),
  INDEX `status_fetched_at_created_at` (`status`, `fetched_at`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
```

{% endtab %}

{% tab title="PostgreSQL" %}

```sql
CREATE type enum_status as ENUM('CREATED', 'PROCESSING', 'DONE', 'ERROR');

CREATE TABLE commands(
  command_id numeric(20) NOT NULL PRIMARY KEY,
  command_type varchar(32) NOT NULL,
  command_body text NOT NULL,
  status enum_status NOT NULL DEFAULT 'CREATED',
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  fetched_at timestamp DEFAULT NULL,
  finished_at timestamp DEFAULT NULL
);

CREATE INDEX status_id ON commands(status, command_id);
CREATE INDEX status_finished_at ON  commands(status, finished_at);
CREATE INDEX status_fetched_at_created_at ON commands(status, fetched_at, created_at);
```

{% endtab %}

{% tab title="MSSQL" %}

```sql
CREATE TABLE commands (
  command_id BIGINT NOT NULL PRIMARY KEY,
  command_type nvarchar(32) NOT NULL,
  command_body nvarchar(MAX) NOT NULL,
  status nvarchar(20) DEFAULT 'CREATED' NOT NULL check (status in ('CREATED', 'PROCESSING', 'DONE', 'ERROR')),
  created_at datetime2 DEFAULT CURRENT_TIMESTAMP,
  fetched_at datetime2 DEFAULT NULL,
  finished_at datetime2 DEFAULT NULL
);

CREATE INDEX idx_status_id ON commands(status, command_id);
CREATE INDEX idx_status_finished_at ON commands(status, finished_at);
CREATE INDEX idx_status_fetched_at_created_at ON commands(status, fetched_at, created_at);
```

{% endtab %}

{% tab title="Oracle" %}

```sql
CREATE TABLE "commands" (
  "command_id" number(20) NOT NULL,
  "command_type" varchar2(32) NOT NULL,
  "command_body" clob NOT NULL,
  "status" varchar2(20) DEFAULT 'CREATED' NOT NULL check ("status" in ('CREATED', 'PROCESSING', 'DONE', 'ERROR')),
  "created_at" date DEFAULT CURRENT_TIMESTAMP,
  "fetched_at" date DEFAULT NULL,
  "finished_at" date DEFAULT NULL,
  CONSTRAINT "command_id" PRIMARY KEY ("command_id")
);

CREATE INDEX "idx_status_id" ON "commands"("status", "command_id");
CREATE INDEX "idx_status_finished_at" ON "commands"("status", "finished_at");
CREATE INDEX "idx_status_fetched_at_created_at" ON "commands"("status", "fetched_at", "created_at");
```

{% endtab %}
{% endtabs %}

### chainalysis\_sanction\_results 테이블 (선택) <a href="#chainalysis-sanction-results" id="chainalysis-sanction-results"></a>

* [Chainalysis Sanction 기능을 이용한 위험 평가 API](/reference/enclave-api-reference/v1/screening-api/risk-assessment-sanction-api.md)를 사용할 때 필요합니다.
* Chainalysis Sanction API를 통해 송수신인의 지갑 주소에 대한 위험도 레벨을 평가한 이력을 저장합니다.

#### 테이블 생성

{% tabs %}
{% tab title="MySQL" %}

```sql
CREATE TABLE `chainalysis_sanction_results` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `request_id` varchar(40) NOT NULL,
  `verification_uuid` varchar(40) NOT NULL,
  `counterparty_vasp_id` bigint(20) unsigned NOT NULL,
  `direction` enum('OUTGOING', 'INCOMING') NOT NULL,
  `address` varchar(512) NOT NULL,
  `status` enum('NOHIT', 'SANCTION', 'CLOSED', 'ERROR') NOT NULL,
  `ofac_name` varchar(1024) DEFAULT NULL,
  `ofac_description` varchar(4096) DEFAULT NULL,
  `ofac_url` varchar(1024) DEFAULT NULL,
  `created_at` datetime(3) DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` datetime(3) DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_request_id` (`request_id`),
  INDEX `idx_counterparty_vasp_id` (`counterparty_vasp_id`, `created_at`),
  INDEX `idx_address` (`address`, `created_at`),
  INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
```

{% endtab %}

{% tab title="PostgreSQL" %}

```sql
CREATE TYPE enum_direction AS ENUM ('OUTGOING', 'INCOMING');
CREATE TYPE enum_chainalysis_sanction_status AS ENUM ('NOHIT', 'SANCTION', 'CLOSED', 'ERROR');

CREATE TABLE chainalysis_sanction_results (
  id SERIAL NOT NULL PRIMARY KEY,
  request_id varchar(40) NOT NULL,
  verification_uuid varchar(40) NOT NULL,
  counterparty_vasp_id numeric(20) NOT NULL,
  direction enum_direction NOT NULL,
  address varchar(512) NOT NULL,
  status enum_chainalysis_sanction_status NOT NULL,
  ofac_name varchar(1024) DEFAULT NULL,
  ofac_description varchar(4096) DEFAULT NULL,
  ofac_url varchar(1024) DEFAULT NULL,
  created_at timestamp DEFAULT CURRENT_TIMESTAMP,
  updated_at timestamp DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT uniq_sanction_request_id UNIQUE (request_id)
);

CREATE INDEX idx_chainalysis_sanction_results_counterparty_vasp_id ON chainalysis_sanction_results(counterparty_vasp_id, created_at);
CREATE INDEX idx_chainalysis_sanction_results_address ON chainalysis_sanction_results(address, created_at);
CREATE INDEX idx_chainalysis_sanction_results_created_at ON chainalysis_sanction_results(created_at);
```

{% endtab %}

{% tab title="MSSQL" %}

```sql
CREATE TABLE chainalysis_sanction_results (
  id BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  request_id nvarchar(40) NOT NULL,
  verification_uuid nvarchar(40) NOT NULL,
  counterparty_vasp_id BIGINT NOT NULL,
  direction nvarchar(20) NOT NULL check (direction in ('OUTGOING', 'INCOMING')),
  address nvarchar(512) NOT NULL,
  status nvarchar(20) NOT NULL check (status in ('NOHIT', 'SANCTION', 'CLOSED', 'ERROR')),
  ofac_name nvarchar(1024) DEFAULT NULL,
  ofac_description nvarchar(4000) DEFAULT NULL,
  ofac_url nvarchar(1024) DEFAULT NULL,
  created_at datetime2(3) DEFAULT CURRENT_TIMESTAMP,
  updated_at datetime2(3) DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT key_uniq_sanction_request_id UNIQUE (request_id)
);

CREATE INDEX idx_chainalysis_sanction_results_counterparty_vasp_id ON chainalysis_sanction_results(counterparty_vasp_id, created_at);
CREATE INDEX idx_chainalysis_sanction_results_address ON chainalysis_sanction_results(address, created_at);
CREATE INDEX idx_chainalysis_sanction_results_created_at ON chainalysis_sanction_results(created_at);
```

{% endtab %}

{% tab title="Oracle" %}

```sql
CREATE TABLE "chainalysis_sanction_results" (
  "id" number(20) NOT NULL,
  "request_id" varchar2(40) NOT NULL,
  "verification_uuid" varchar2(40) NOT NULL,
  "counterparty_vasp_id" varchar2(20) NOT NULL,
  "direction" varchar2(20) NOT NULL CHECK ("direction" IN ('OUTGOING', 'INCOMING')),
  "address" varchar2(512) NOT NULL,
  "status" varchar2(20) NOT NULL CHECK ("status" IN ('NOHIT', 'SANCTION', 'CLOSED', 'ERROR')),
  "ofac_name" varchar2(1024) DEFAULT NULL,
  "ofac_description" varchar2(2048) DEFAULT NULL,
  "ofac_url" varchar2(1024) DEFAULT NULL,
  "created_at" timestamp(3) DEFAULT CURRENT_TIMESTAMP,
  "updated_at" timestamp(3) DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT "chainalysis_sanction_pk" PRIMARY KEY ("id"),
  CONSTRAINT "uniq_sanction_request_id" UNIQUE ("request_id")
);

CREATE INDEX "idx_counterparty_vasp_id" ON "chainalysis_sanction_results" ("counterparty_vasp_id", "created_at");
CREATE INDEX "idx_address" ON "chainalysis_sanction_results" ("address", "created_at");
CREATE INDEX "idx_created_at" ON "chainalysis_sanction_results" ("created_at");

CREATE SEQUENCE "chainalysis_sanction_seq";

-- Create a trigger
CREATE OR REPLACE TRIGGER chainalysis_sanction_trg
BEFORE INSERT ON "chainalysis_sanction_results"
FOR EACH ROW
BEGIN
  SELECT "chainalysis_sanction_seq".NEXTVAL
  INTO :new."id"
  FROM dual;
END;
/
```

{% endtab %}
{% endtabs %}

### chainalysis\_kyt\_results 테이블 (선택) <a href="#chainalysis-kyt-results" id="chainalysis-kyt-results"></a>

* [Chainalysis KYT 기능을 이용한 위험 평가 API](/reference/enclave-api-reference/v1/screening-api/risk-assessment-kyt-api.md)를 사용할 때 필요합니다.
* 트랜잭션 리포트 하기 전이라면, Chainalysis KYT API를 통해 수신인의 지갑 주소에 대한 위험 평가 결과를 저장합니다.
* 트랜잭션 리포트를 한 후라면, Chainalysis KYT API를 통해 가상 자산 전송 트랜잭션에 대한 위험 평가 결과를 저장합니다.

#### 테이블 생성

{% tabs %}
{% tab title="MySQL" %}

```sql
CREATE TABLE `chainalysis_kyt_results` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `request_id` varchar(40) NOT NULL,
  `verification_uuid` varchar(40) NOT NULL,
  `counterparty_vasp_id` bigint(20) unsigned NOT NULL,
  `api_type` enum('TRANSFER', 'ATTEMPT') NOT NULL,
  `user_id` varchar(128) NOT NULL,
  `direction` enum('OUTGOING', 'INCOMING') NOT NULL,
  `network` varchar(128) DEFAULT NULL,
  `asset` varchar(16) NOT NULL,
  `amount` varchar(128) NOT NULL,
  `usd_price` varchar(128) DEFAULT NULL,
  `transfer_ref` varchar(1024) DEFAULT NULL,
  `output_address` varchar(512) DEFAULT NULL,
  `timestamp` datetime(3) DEFAULT NULL,
  `external_id` varchar(128) DEFAULT NULL,
  `status` enum('ERROR', 'REGISTERED', 'CHECKING', 'PROCESSED') NOT NULL,
  `worker_id` varchar(128) DEFAULT NULL,
  `last_checked_at` datetime(3) DEFAULT NULL,
  `alert_count` int(11) DEFAULT NULL,
  `created_at` datetime(3) DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` datetime(3) DEFAULT CURRENT_TIMESTAMP(3),
  `assessed_at` datetime(3) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_request_id` (`request_id`),
  INDEX `idx_external_id` (`external_id`),
  INDEX `idx_counterparty_vasp_id` (`counterparty_vasp_id`, `created_at`),
  INDEX `idx_direction` (`direction`, `created_at`),
  INDEX `idx_status_last_checked_at` (`status`, `last_checked_at`),
  INDEX `idx_status_worker_id` (`status`, `worker_id`, `id`),
  INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
```

{% endtab %}

{% tab title="PostgreSQL" %}

```sql
CREATE TYPE enum_api_type AS ENUM('TRANSFER', 'ATTEMPT');
CREATE TYPE enum_chainalysis_kyt_status AS ENUM('ERROR', 'REGISTERED', 'CHECKING', 'PROCESSED');

CREATE TABLE chainalysis_kyt_results (
  id SERIAL NOT NULL PRIMARY KEY,
  request_id varchar(40) NOT NULL,
  verification_uuid varchar(40) NOT NULL,
  counterparty_vasp_id numeric(20) NOT NULL,
  api_type enum_api_type NOT NULL,
  user_id varchar(128) NOT NULL,
  direction enum_direction NOT NULL,
  network varchar(128) DEFAULT NULL,
  asset varchar(16) NOT NULL,
  amount varchar(128) NOT NULL,
  usd_price varchar(128) DEFAULT NULL,
  transfer_ref varchar(1024) DEFAULT NULL,
  output_address varchar(512) DEFAULT NULL,
  timestamp timestamp DEFAULT NULL,
  external_id varchar(128) DEFAULT NULL,
  status enum_chainalysis_kyt_status NOT NULL,
  worker_id varchar(128) DEFAULT NULL,
  last_checked_at timestamp DEFAULT NULL,
  alert_count numeric(11) DEFAULT NULL,
  created_at timestamp DEFAULT CURRENT_TIMESTAMP,
  updated_at timestamp DEFAULT CURRENT_TIMESTAMP,
  assessed_at timestamp DEFAULT NULL,
  CONSTRAINT uniq_kyt_request_id UNIQUE (request_id)
);

CREATE INDEX idx_chainalysis_kyt_results_external_id ON chainalysis_kyt_results(external_id);
CREATE INDEX idx_chainalysis_kyt_results_counterparty_vasp_id ON chainalysis_kyt_results(counterparty_vasp_id, created_at);
CREATE INDEX idx_chainalysis_kyt_results_direction ON chainalysis_kyt_results(direction, created_at);
CREATE INDEX idx_chainalysis_kyt_results_status_last_checked_at ON chainalysis_kyt_results(status, last_checked_at);
CREATE INDEX idx_chainalysis_kyt_results_status_worker_id ON chainalysis_kyt_results(status, worker_id, id);
CREATE INDEX idx_chainalysis_kyt_results_created_at ON chainalysis_kyt_results(created_at);
```

{% endtab %}

{% tab title="MSSQL" %}

```sql
CREATE TABLE chainalysis_kyt_results (
  id BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  request_id nvarchar(40) NOT NULL,
  verification_uuid nvarchar(40) NOT NULL,
  counterparty_vasp_id BIGINT NOT NULL,
  api_type nvarchar(10) NOT NULL check (api_type in ('TRANSFER', 'ATTEMPT')),
  user_id nvarchar(128) NOT NULL,
  direction nvarchar(10) NOT NULL check (direction in ('OUTGOING', 'INCOMING')),
  network nvarchar(128) DEFAULT NULL,
  asset nvarchar(16) NOT NULL,
  amount nvarchar(128) NOT NULL,
  usd_price nvarchar(128) DEFAULT NULL,
  transfer_ref nvarchar(1024) DEFAULT NULL,
  output_address nvarchar(512) DEFAULT NULL,
  timestamp datetime2(3) DEFAULT NULL,
  external_id nvarchar(128) DEFAULT NULL,
  status nvarchar(20) NOT NULL check (status in ('ERROR', 'REGISTERED', 'CHECKING', 'PROCESSED')),
  worker_id nvarchar(128) DEFAULT NULL,
  last_checked_at datetime2(3) DEFAULT NULL,
  alert_count int DEFAULT NULL,
  created_at datetime2(3) DEFAULT CURRENT_TIMESTAMP,
  updated_at datetime2(3) DEFAULT CURRENT_TIMESTAMP,
  assessed_at datetime2(3) DEFAULT NULL,
  CONSTRAINT key_uniq_kyt_request_id UNIQUE (request_id)
);

CREATE INDEX idx_chainalysis_kyt_results_external_id ON chainalysis_kyt_results(external_id);
CREATE INDEX idx_chainalysis_kyt_results_counterparty_vasp_id ON chainalysis_kyt_results(counterparty_vasp_id, created_at);
CREATE INDEX idx_chainalysis_kyt_results_direction ON chainalysis_kyt_results(direction, created_at);
CREATE INDEX idx_chainalysis_kyt_results_status_last_checked_at ON chainalysis_kyt_results(status, last_checked_at);
CREATE INDEX idx_chainalysis_kyt_results_status_worker_id ON chainalysis_kyt_results(status, worker_id, id);
CREATE INDEX idx_chainalysis_kyt_results_created_at ON chainalysis_kyt_results(created_at);
```

{% endtab %}

{% tab title="Oracle" %}

```sql
CREATE TABLE "chainalysis_kyt_results" (
  "id" number(20) NOT NULL,
  "request_id" varchar2(40) NOT NULL,
  "verification_uuid" varchar2(40) NOT NULL,
  "counterparty_vasp_id" varchar2(20) NOT NULL,
  "api_type" varchar2(20) NOT NULL CHECK ("api_type" IN ('TRANSFER', 'ATTEMPT')),
  "user_id" varchar2(128) NOT NULL,
  "direction" varchar2(20) NOT NULL CHECK ("direction" IN ('OUTGOING', 'INCOMING')),
  "network" varchar2(128) DEFAULT NULL,
  "asset" varchar2(16) NOT NULL,
  "amount" varchar2(128) NOT NULL,
  "usd_price" varchar2(128) DEFAULT NULL,
  "transfer_ref" varchar2(1024) DEFAULT NULL,
  "output_address" varchar2(512) DEFAULT NULL,
  "timestamp" timestamp(3) DEFAULT NULL,
  "external_id" varchar2(128) DEFAULT NULL,
  "status" varchar2(20) NOT NULL CHECK ("status" IN ('ERROR', 'REGISTERED', 'CHECKING', 'PROCESSED')),
  "worker_id" varchar2(128) DEFAULT NULL,
  "last_checked_at" timestamp(3) DEFAULT NULL,
  "alert_count" number(11) DEFAULT NULL,
  "created_at" timestamp(3) DEFAULT CURRENT_TIMESTAMP,
  "updated_at" timestamp(3) DEFAULT CURRENT_TIMESTAMP,
  "assessed_at" timestamp(3) DEFAULT NULL,
  CONSTRAINT "chainalysis_kyt_results_pk" PRIMARY KEY ("id"),
  CONSTRAINT "uniq_kyt_request_id" UNIQUE ("request_id")
);

CREATE INDEX "idx_kyt_external_id" ON "chainalysis_kyt_results" ("external_id");
CREATE INDEX "idx_kyt_counterparty_vasp_id" ON "chainalysis_kyt_results" ("counterparty_vasp_id", "created_at");
CREATE INDEX "idx_kyt_direction" ON "chainalysis_kyt_results" ("direction", "created_at");
CREATE INDEX "idx_kyt_status_last_checked_at" ON "chainalysis_kyt_results" ("status", "last_checked_at");
CREATE INDEX "idx_kyt_status_worker_id" ON "chainalysis_kyt_results" ("status", "worker_id", "id");
CREATE INDEX "idx_kyt_created_at" ON "chainalysis_kyt_results" ("created_at");

-- Create a sequence
CREATE SEQUENCE "chainalysis_kyt_results_seq";

-- Create a trigger
CREATE OR REPLACE TRIGGER chainalysis_kyt_results_trg
BEFORE INSERT ON "chainalysis_kyt_results"
FOR EACH ROW
BEGIN
  SELECT "chainalysis_kyt_results_seq".NEXTVAL
  INTO :new."id"
  FROM dual;
END;
/
```

{% endtab %}
{% endtabs %}

### chainalysis\_kyt\_alerts 테이블 (선택) <a href="#chainalysis-kyt-alerts" id="chainalysis-kyt-alerts"></a>

* [Chainalysis KYT 기능을 이용한 위험 평가 API](/reference/enclave-api-reference/v1/screening-api/risk-assessment-kyt-api.md)를 사용할 때 필요합니다.
* Chainalysis KYT API를 통한 위험 평가로 발생한 alert 들을 저장합니다.
* 위 `chainalysis_kyt_results` 테이블의 평가 이력과 1:n 대응됩니다.

#### 테이블 생성

{% tabs %}
{% tab title="MySQL" %}

```sql
CREATE TABLE `chainalysis_kyt_alerts` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `external_id` varchar(128) NOT NULL,
  `alert_id` varchar(128) NOT NULL,
  `alert_level` enum('LOW', 'MEDIUM', 'HIGH', 'SEVERE') NOT NULL,
  `entity_category` varchar(256) DEFAULT NULL,
  `service_name` varchar(256) DEFAULT NULL,
  `exposure_type` enum('DIRECT', 'INDIRECT') NOT NULL,
  `alert_amount` varchar(128) NOT NULL,
  `created_at` datetime(3) DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` datetime(3) DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_alert_id` (`alert_id`),
  INDEX `idx_external_id` (`external_id`),
  INDEX `idx_alert_level` (`alert_level`),
  INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
```

{% endtab %}

{% tab title="PostgreSQL" %}

```sql
CREATE TYPE enum_alert_level AS ENUM ('LOW', 'MEDIUM', 'HIGH', 'SEVERE');
CREATE TYPE enum_exposure_type AS ENUM('DIRECT', 'INDIRECT');

CREATE TABLE chainalysis_kyt_alerts (
  id SERIAL NOT NULL PRIMARY KEY,
  external_id varchar(128) NOT NULL,
  alert_id varchar(128) NOT NULL UNIQUE,
  alert_level enum_alert_level NOT NULL,
  entity_category varchar(256) DEFAULT NULL,
  service_name varchar(256) DEFAULT NULL,
  exposure_type enum_exposure_type NOT NULL,
  alert_amount varchar(128) NOT NULL,
  created_at timestamp DEFAULT CURRENT_TIMESTAMP,
  updated_at timestamp DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_chainalysis_kyt_alerts_external_id ON chainalysis_kyt_alerts(external_id);
CREATE INDEX idx_chainalysis_kyt_alerts_alert_level ON chainalysis_kyt_alerts(alert_level);
CREATE INDEX idx_chainalysis_kyt_alerts_created_at ON chainalysis_kyt_alerts(created_at);
```

{% endtab %}

{% tab title="MSSQL" %}

```sql
CREATE TABLE chainalysis_kyt_alerts (
  id BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  external_id nvarchar(128) NOT NULL,
  alert_id nvarchar(128) NOT NULL UNIQUE,
  alert_level nvarchar(20) NOT NULL check (alert_level in ('LOW', 'MEDIUM', 'HIGH', 'SEVERE')),
  entity_category nvarchar(256) DEFAULT NULL,
  service_name nvarchar(256) DEFAULT NULL,
  exposure_type nvarchar(20) NOT NULL check (exposure_type in ('DIRECT', 'INDIRECT')),
  alert_amount nvarchar(128) NOT NULL,
  created_at datetime2(3) DEFAULT CURRENT_TIMESTAMP,
  updated_at datetime2(3) DEFAULT CURRENT_TIMESTAMP,
);

CREATE INDEX idx_chainalysis_kyt_alerts_external_id ON chainalysis_kyt_alerts(external_id);
CREATE INDEX idx_chainalysis_kyt_alerts_alert_level ON chainalysis_kyt_alerts(alert_level);
CREATE INDEX idx_chainalysis_kyt_alerts_created_at ON chainalysis_kyt_alerts(created_at);
```

{% endtab %}

{% tab title="Oracle" %}

```sql
CREATE TABLE "chainalysis_kyt_alerts" (
  "id" number(20) NOT NULL,
  "external_id" varchar2(128) NOT NULL,
  "alert_id" varchar2(128) NOT NULL,
  "alert_level" varchar2(20) NOT NULL CHECK ("alert_level" IN ('LOW', 'MEDIUM', 'HIGH', 'SEVERE')),
  "entity_category" varchar2(256) DEFAULT NULL,
  "service_name" varchar2(256) DEFAULT NULL,
  "exposure_type" varchar2(20) NOT NULL CHECK ("exposure_type" IN ('DIRECT', 'INDIRECT')),
  "alert_amount" varchar2(128) NOT NULL,
  "created_at" timestamp(3) DEFAULT CURRENT_TIMESTAMP,
  "updated_at" timestamp(3) DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT "chainalysis_kyt_alerts_pk" PRIMARY KEY ("id"),
  CONSTRAINT "uniq_alert_id" UNIQUE ("alert_id")
);

CREATE INDEX "idx_kyta_external_id" ON "chainalysis_kyt_alerts" ("external_id");
CREATE INDEX "idx_kyta_alert_level" ON "chainalysis_kyt_alerts" ("alert_level");
CREATE INDEX "idx_kyta_created_at" ON "chainalysis_kyt_alerts" ("created_at");

-- Create a sequence
CREATE SEQUENCE "chainalysis_kyt_alerts_seq";

-- Create a trigger
CREATE OR REPLACE TRIGGER chainalysis_kyt_alerts_trg
BEFORE INSERT ON "chainalysis_kyt_alerts"
FOR EACH ROW
BEGIN
  SELECT "chainalysis_kyt_alerts_seq".NEXTVAL
  INTO :new."id"
  FROM dual;
END;
/
```

{% endtab %}
{% endtabs %}

### refinitiv\_wco\_results 테이블 (선택) <a href="#refinitiv-wco-results" id="refinitiv-wco-results"></a>

* [Refinitiv WCO 기능을 이용한 위험 평가 API](/reference/enclave-api-reference/v1/screening-api/risk-assessment-wco-api.md)를 사용할 때 필요합니다.
* Refinitiv World Check One API를 통해 송수신인의 실명과 기타 개인 정보를 이용하여 위험 평가를 수행한 이력을 저장합니다.

#### 테이블 생성

{% tabs %}
{% tab title="MySQL" %}

```sql
CREATE TABLE `refinitiv_wco_results` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `request_id` varchar(40) NOT NULL,
  `verification_uuid` varchar(40) NOT NULL,
  `counterparty_vasp_id` bigint(20) unsigned NOT NULL,
  `direction` enum('OUTGOING', 'INCOMING') NOT NULL,
  `case_system_id` varchar(128) NOT NULL,
  `aggregated_result_summaries` varchar(4096) DEFAULT NULL,
  `worker_id` varchar(128) DEFAULT NULL,
  `status` enum('ERROR', 'REGISTERED', 'CHECKING', 'PROCESSED') NOT NULL,
  `last_checked_at` datetime(3) DEFAULT NULL,
  `created_at` datetime(3) DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` datetime(3) DEFAULT CURRENT_TIMESTAMP(3),
  `assessed_at` datetime(3) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_request_id` (`request_id`),
  INDEX `idx_counterparty_vasp_id` (`counterparty_vasp_id`, `created_at`),
  INDEX `idx_direction` (`direction`, `created_at`),
  INDEX `idx_status_last_checked_at` (`status`, `last_checked_at`),
  INDEX `idx_status_worker_id` (`status`, `worker_id`, `id`),
  INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
```

{% endtab %}

{% tab title="PostgreSQL" %}

```sql
CREATE TYPE enum_refinitiv_wco_status AS ENUM('ERROR', 'REGISTERED', 'CHECKING', 'PROCESSED');

CREATE TABLE refinitiv_wco_results (
  id SERIAL NOT NULL PRIMARY KEY,
  request_id varchar(40) NOT NULL,
  verification_uuid varchar(40) NOT NULL,
  counterparty_vasp_id numeric(20) NOT NULL,
  direction enum_direction NOT NULL,
  case_system_id varchar(128) NOT NULL,
  aggregated_result_summaries varchar(4096) DEFAULT NULL,
  worker_id varchar(128) DEFAULT NULL,
  status enum_refinitiv_wco_status NOT NULL,
  last_checked_at timestamp DEFAULT NULL,
  created_at timestamp DEFAULT CURRENT_TIMESTAMP,
  updated_at timestamp DEFAULT CURRENT_TIMESTAMP,
  assessed_at timestamp DEFAULT NULL,
  CONSTRAINT key_uniq_wco_request_id UNIQUE (request_id)
);

CREATE INDEX idx_refinitiv_wco_results_counterparty_vasp_id ON refinitiv_wco_results(counterparty_vasp_id, created_at);
CREATE INDEX idx_refinitiv_wco_results_direction ON refinitiv_wco_results(direction, created_at);
CREATE INDEX idx_refinitiv_wco_results_status_last_checked_at ON refinitiv_wco_results(status, last_checked_at);
CREATE INDEX idx_refinitiv_wco_results_status_worker_id ON refinitiv_wco_results(status, worker_id, id);
CREATE INDEX idx_refinitiv_wco_results_created_at ON refinitiv_wco_results(created_at);
```

{% endtab %}

{% tab title="MSSQL" %}

```sql
CREATE TABLE refinitiv_wco_results (
  id BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  request_id nvarchar(40) NOT NULL,
  verification_uuid nvarchar(40) NOT NULL,
  counterparty_vasp_id BIGINT NOT NULL,
  direction nvarchar(20) NOT NULL check (direction in ('OUTGOING', 'INCOMING')),
  case_system_id nvarchar(128) NOT NULL,
  aggregated_result_summaries nvarchar(4000) DEFAULT NULL,
  worker_id nvarchar(128) DEFAULT NULL,
  status nvarchar(20) NOT NULL check (status in ('ERROR', 'REGISTERED', 'CHECKING', 'PROCESSED')),
  last_checked_at datetime2(3) DEFAULT NULL,
  created_at datetime2(3) DEFAULT CURRENT_TIMESTAMP,
  updated_at datetime2(3) DEFAULT CURRENT_TIMESTAMP,
  assessed_at datetime2(3) DEFAULT NULL,
  CONSTRAINT key_uniq_wco_request_id UNIQUE (request_id)
);

CREATE INDEX idx_refinitiv_wco_results_counterparty_vasp_id ON refinitiv_wco_results(counterparty_vasp_id, created_at);
CREATE INDEX idx_refinitiv_wco_results_direction ON refinitiv_wco_results(direction, created_at);
CREATE INDEX idx_refinitiv_wco_results_status_last_checked_at ON refinitiv_wco_results(status, last_checked_at);
CREATE INDEX idx_refinitiv_wco_results_status_worker_id ON refinitiv_wco_results(status, worker_id, id);
CREATE INDEX idx_refinitiv_wco_results_created_at ON refinitiv_wco_results(created_at);
```

{% endtab %}

{% tab title="Oracle" %}

```sql
CREATE TABLE "refinitiv_wco_results" (
  "id" number(20) NOT NULL,
  "request_id" varchar2(40) NOT NULL,
  "verification_uuid" varchar2(40) NOT NULL,
  "counterparty_vasp_id" varchar2(20) NOT NULL,
  "direction" varchar2(20) NOT NULL CHECK ("direction" IN ('OUTGOING', 'INCOMING')),
  "case_system_id" varchar2(128) NOT NULL,
  "aggregated_result_summaries" varchar2(2048) DEFAULT NULL,
  "worker_id" varchar2(128) DEFAULT NULL,
  "status" varchar2(20) NOT NULL CHECK ("status" IN ('ERROR', 'REGISTERED', 'CHECKING', 'PROCESSED')),
  "last_checked_at" timestamp(3) DEFAULT NULL,
  "created_at" timestamp(3) DEFAULT CURRENT_TIMESTAMP,
  "updated_at" timestamp(3) DEFAULT CURRENT_TIMESTAMP,
  "assessed_at" timestamp(3) DEFAULT NULL,
  CONSTRAINT "refinitiv_wco_results_pk" PRIMARY KEY ("id"),
  CONSTRAINT "uniq_wco_request_id" UNIQUE ("request_id")
);

CREATE INDEX "idx_wco_counterparty_vasp_id" ON "refinitiv_wco_results" ("counterparty_vasp_id", "created_at");
CREATE INDEX "idx_wco_direction" ON "refinitiv_wco_results" ("direction", "created_at");
CREATE INDEX "idx_wco_status_last_checked_at" ON "refinitiv_wco_results" ("status", "last_checked_at");
CREATE INDEX "idx_wco_status_worker_id" ON "refinitiv_wco_results" ("status", "worker_id", "id");
CREATE INDEX "idx_wco_created_at" ON "refinitiv_wco_results" ("created_at");

-- Create a sequence
CREATE SEQUENCE "refinitiv_wco_results_seq";

-- Create a trigger
CREATE OR REPLACE TRIGGER refinitiv_wco_results_trg
BEFORE INSERT ON "refinitiv_wco_results"
FOR EACH ROW
BEGIN
  SELECT "refinitiv_wco_results_seq".NEXTVAL
  INTO :new."id"
  FROM dual;
END;
/
```

{% endtab %}
{% endtabs %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs-kr.verifyvasp.com/getting-started/database.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.