VerifyVASP
Search
K

Database

System Requirement

  • A separate database system is required in the use of enclave management
  • MySQL, PostgreSQL, MSSQL, and Oracle DBMS are supported.
  • MySQL is recommended.

Table Description

  • Basically, You need to create 4 tables.
    • verifications : A table to store the requests/responses of user verification API.
    • counter_party_keys : A table to store the public key of the beneficiary VASP.
    • own_keys : A table to store the public key of the originating VASP.
    • commands : A table to store the intermediate requests of asynchronous APIs.
  • Additionally, You need to create more tables depending on the features you want to use.
    • chainalysis_sanction_results : A table to store the histories of risk assessment with Chainalysis Sanction API.
    • chainalysis_kyt_results : A table to store the histories of risk assessment with Chainalysis KYT API.
    • chainalysis_kyt_alerts : A table to store KYT alerts generated by risk assessment with Chainalysis KYT API.
    • refinitiv_wco_results : A table to store the histories of risk assessment with Refinitiv World Check One API.

Backup, Restore, and Recover Policies

  • verifications table is recommended to be backed up every day as it manages the verification history of Travel Rules in it.
  • own_keys is recommended to be backed up regularly.
  • chainalysis_sanction_results, chainalysis_kyt_results, chainalysis_kyt_alerts, refinitiv_wco_results are recommended to be backed up regularly.
  • counter_party_keys table is used for the caching purpose, thus it does not require to be restored.
  • commands table does not require to be backed up or restored as only intermediate processing data for temporary use are in it.

Storage Size Considerations

  • verifications table: approximately 4-5 KB per request
  • counter_party_keys table: approximately 1 KB per beneficiary address
  • own_keys: approximately 1 KB per request if PerVerification key type is used
  • commands: approximately 1-5 KB per asynchronous request
  • chainalysis_sanction_results : approximately 1-2 KB per request
  • chainalysis_kyt_results : approximately 2-3 KB per request
  • chainalysis_kyt_alerts : approximately 0-3 KB per request
  • refinitiv_wco_results : approximately 2-3 KB per request

Table Definition

verifications table (Required)

  • In withdrawal request processing, the pre-screening result from the Travel Rules is stored in this table.
  • When POST /verifications API is called, the request/response result will be recorded in the verifications table.
  • Following columns are encrypted.
    • ivms101_originator
    • ivms101_originating_vasp
    • ivms101_beneficiary
    • ivms101_beneficiary_vasp

Table Creation Query

MySQL
PostgreSQL
MSSQL
Oracle
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 DEFAULT NULL COMMENT 'Verified at.',
`ordered_at` datetime DEFAULT NULL COMMENT 'Ordered at.',
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP 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;
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);
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 DEFAULT NULL,
ordered_at datetime2 DEFAULT NULL,
created_at datetime2 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);
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" date DEFAULT NULL,
"ordered_at" date DEFAULT NULL,
"created_at" date 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;
/

counter_party_keys Table (Required)

  • The table stores the public key of the beneficiary VASP, which is used to encrypt personal information in the verification process.
  • According to the key type determined by the originating VASP, the public key of the beneficiary VASP is recorded.
  • When POST /verifications API is called, it is checked whether the public key of the beneficiary VASP is being cashed. If not, an automatic request to the beneficiary VASP is sent and saved in this table.

Table Creation Query

MySQL
PostgreSQL
MSSQL
Oracle
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;
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
);
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)
);
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;
/

own_keys Table (Required)

  • The table stores the public key and private key of the Own VASP - the enclave VASP itself - both of which are used in personal information encryption during the verification process.
  • When the other VASP requests a public key inquiry, the requested type of public key can be returned by being fetched from this table.
  • The private key is saved as encrypted.
Table Creation Query
MySQL
PostgreSQL
MSSQL
Oracle
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;
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);
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);
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;
/

commands Table (Required)

  • The intermediate requests in processing asynchronous API are stored in this table.
  • This table is only used inside the enclave, not by VASP.

Table Creation Query

MySQL
PostgreSQL
MSSQL
Oracle
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;
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);
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);
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");

chainalysis_sanction_results Table (Optional)

Table Creation Query

MySQL
PostgreSQL
MSSQL
Oracle
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 DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime DEFAULT CURRENT_TIMESTAMP,
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;
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);
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 DEFAULT CURRENT_TIMESTAMP,
updated_at datetime2 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);
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" date DEFAULT CURRENT_TIMESTAMP,
"updated_at" date 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;
/

chainalysis_kyt_results Table (Optional)

Table Creation Query

MySQL
PostgreSQL
MSSQL
Oracle
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 DEFAULT NULL,
`external_id` varchar(128) DEFAULT NULL,
`status` enum('ERROR', 'REGISTERED', 'CHECKING', 'PROCESSED') NOT NULL,
`worker_id` varchar(128) DEFAULT