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

  • You need to create 5 tables in total.
    • verifications: A table to store the requests/responses of user verification API.
    • pre_screenings: A table to store the requests/responses of pre-screening API of the risk of the user account number.
    • 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.

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.
  • pre_screenings, own_keys 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

  • pre-screenings table: approximately 1 KB per request
  • 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

Table Definition

verifications table

  • In withdrawal request processing, the pre-screening result from the Travle 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 abount the reason',
`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',
`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 DEFAULT 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,
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,
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 check (verification_id > 0) 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,
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 datetime DEFAULT NULL,
is_exceeding_threshold BOOLEAN DEFAULT '1' NOT NULL,
tx_hash 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,
to_account_number nvarchar(256) DEFAULT NULL,
ivms101_beneficiary nvarchar(MAX) DEFAULT NULL,
ivms101_beneficiary_vasp nvarchar(MAX) DEFAULT NULL,
verified_at datetime DEFAULT NULL,
ordered_at datetime DEFAULT NULL,
created_at datetime 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,
"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,
"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;
/

pre_screenings table

  • When withdrawal is requested, the result of the risk level estimated with virtual asset account numbers of both beneficiary and originator is recorded.
  • When POST /pre-screening API is called, the request/response will be recorded in the pre_screenings table.

Table Creation Query

MySQL
PostgreSQL
MSSQL
Oracle
CREATE TABLE `pre_screenings` (
`pre_screening_id` bigint(20) unsigned NOT NULL COMMENT 'Pre-Screening request ID',
`pre_screening_asset` varchar(256) DEFAULT NULL COMMENT 'The asset associated with the address',
`pre_screening_address` varchar(256) DEFAULT NULL COMMENT 'Address',
`cluster_name` varchar(256) DEFAULT NULL COMMENT 'The name of the named cluster',
`cluster_category` varchar(256) DEFAULT NULL COMMENT 'The category of the named cluster',
`rating` enum('UNKNOWN', 'HIGH', 'MEDIUM', 'LOW') DEFAULT NULL COMMENT 'The risk rating of the known recipient address',
`created_at` datetime DEFAULT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Created at.',
PRIMARY KEY (`pre_screening_id`),
INDEX `idx_pre_screenings_address` (`pre_screening_address`),
INDEX `idx_pre_screenings_asset` (`pre_screening_asset`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TYPE enum_rating AS ENUM ('UNKNOWN', 'HIGH', 'MEDIUM', 'LOW');
​
CREATE TABLE pre_screenings (
pre_screening_id numeric(20) NOT NULL PRIMARY KEY,
pre_screening_asset varchar(256) DEFAULT NULL,
pre_screening_address varchar(256) DEFAULT NULL,
cluster_name varchar(256) DEFAULT NULL,
cluster_category varchar(256) DEFAULT NULL,
rating enum_rating DEFAULT NULL,
created_at timestamp DEFAULT CURRENT_TIMESTAMP
);
​
CREATE INDEX idx_pre_screenings_address ON pre_screenings(pre_screening_address);
CREATE INDEX idx_pre_screenings_asset ON pre_screenings(pre_screening_asset);
CREATE TYPE enum_rating AS ENUM ('UNKNOWN', 'HIGH', 'MEDIUM', 'LOW');
​
CREATE TABLE pre_screenings (
pre_screening_id numeric(20) NOT NULL PRIMARY KEY,
pre_screening_asset varchar(256) DEFAULT NULL,
pre_screening_address varchar(256) DEFAULT NULL,
cluster_name varchar(256) DEFAULT NULL,
cluster_category varchar(256) DEFAULT NULL,
rating enum_rating DEFAULT NULL,
created_at timestamp DEFAULT CURRENT_TIMESTAMP
);
​
CREATE INDEX idx_pre_screenings_address ON pre_screenings(pre_screening_address);
CREATE INDEX idx_pre_screenings_asset ON pre_screenings(pre_screening_asset);
CREATE TABLE "pre_screenings" (
"pre_screening_id" number(20) check ("pre_screening_id" > 0) NOT NULL,
"pre_screening_asset" varchar2(256) DEFAULT NULL,
"pre_screening_address" varchar2(256) DEFAULT NULL,
"cluster_name" varchar2(256) DEFAULT NULL,
"cluster_category" varchar2(256) DEFAULT NULL,
"rating" varchar2(20) DEFAULT NULL check ("rating" in ('UNKNOWN', 'HIGH', 'MEDIUM', 'LOW')),
"created_at" date DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT "pre_screening_id" PRIMARY KEY ("pre_screening_id")
);
​
CREATE INDEX "idx_pre_screenings_address" ON "pre_screenings"("pre_screening_address");
CREATE INDEX "idx_pre_screenings_asset" ON "pre_screenings"("pre_screening_asset");

counter_party_keys Table

  • 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 check (id > 0) NOT NULL PRIMARY KEY,
type nvarchar(10) 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 datetime DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT key_uniqueness 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 "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

  • 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 publickey)',
`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 check (id > 0) NOT NULL PRIMARY KEY,
type nvarchar(10) 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 datetime DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT key_uniqueness 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 "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

  • 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");

​

Getting Started - Previous
VASP API Functionality
Next - Getting Started
Enclave Installation & Execution
Last modified 1yr ago