Database Structure

You can download DDL scripts for supported databases:

ShedLock

Onboarding Server uses ShedLock to synchronize scheduled operations. You need to create appropriate DB table, i.e.:

CREATE TABLE shedlock (
    name VARCHAR(64) NOT NULL PRIMARY KEY,
    lock_until TIMESTAMP NOT NULL,
    locked_at TIMESTAMP NOT NULL,
    locked_by VARCHAR(255) NOT NULL
);

See the SchedLock documentation for the details.

Only one ShedLock table is required per PowerAuth stack in case the same schema is used for all deployed applications.

Auditing

The DDL files contain an audit_log table definition. The table differs slightly per database.

Only one audit_log table is required per PowerAuth stack in case the same schema is used for all deployed applications.

For more information about auditing library, see the Wultra auditing library documentation.

Table Documentation

This chapter explains individual tables and their columns. The column types are used from PostgreSQL dialect, other databases use types that are equivalent (mapping is usually straight-forward).

es_onboarding_process Onboarding Process Table

Stores onboarding processes created for tracking status of user onboarding.

Schema

Name	Type	Info	Note
`id`	`VARCHAR(36)`	`NOT NULL PRIMARY KEY`	Autogenerated record identifier (UUID).
`identification_data`	`VARCHAR(1024)`	`NOT NULL`	Data sent by the customer as JSON.
`custom_data`	`VARCHAR(1024)`	`NOT NULL`	Custom data as JSON.
`user_id`	`VARCHAR(256)`		Resolved user identifier.
`activation_id`	`VARCHAR(36)`		Identifier of created activation.
`status`	`VARCHAR(32)`	`NOT NULL`	Status of onboarding process (`ACTIVATION_IN_PROGRESS`, `VERIFICATION_IN_PROGRESS`, `FINISHED`, `FAILED`).
`activation_removed`	`BOOLEAN`	`DEFAULT FALSE`	Whether activation was removed in PowerAuth server for a failed process.
`error_detail`	`VARCHAR(256)`		Detail of error (e.g. information about timeout or non-existent user).
`error_origin`	`VARCHAR(256)`		Origin of the error (`DOCUMENT_VERIFICATION`, `PRESENCE_CHECK`, `CLIENT_EVALUATION`, `OTP_VERIFICATION`, `PROCESS_LIMIT_CHECK`, `USER_REQUEST`).
`error_score`	`INTEGER`	`NOT NULL DEFAULT 0`	Error score for failures which occurred during onboarding process.
`timestamp_created`	`TIMESTAMP`	`NOT NULL DEFAULT CURRENT_TIMESTAMP`	Timestamp when process was started.
`timestamp_last_updated`	`TIMESTAMP`		Timestamp when record was last updated.
`timestamp_finished`	`TIMESTAMP`		Timestamp when process was finished.
`timestamp_failed`	`TIMESTAMP`		Timestamp when process failed.

es_onboarding_otp Onboarding OTP Table

Stores onboarding OTP codes used during activation and user verification.

Schema

Name	Type	Info	Note
`id`	`VARCHAR(36)`	`NOT NULL PRIMARY KEY`	Autogenerated record identifier (UUID).
`process_id`	`VARCHAR(36)`	`NOT NULL`	Process identifier (UUID).
`otp_code`	`VARCHAR(32)`	`NOT NULL`	Generated OTP code.
`status`	`VARCHAR(36)`		Generated OTP code.
`type`	`VARCHAR(32)`	`NOT NULL`	OTP code type (`ACTIVATION`, `USER_VERIFICATION`).
`error_detail`	`VARCHAR(256)`		Detail of error (e.g. information about timeout or exceeded number of failed attempts).
`error_origin`	`VARCHAR(256)`		Origin of the error (`DOCUMENT_VERIFICATION`, `PRESENCE_CHECK`, `CLIENT_EVALUATION`, `OTP_VERIFICATION`, `PROCESS_LIMIT_CHECK`, `USER_REQUEST`).
`failed_attempts`	`INTEGER`		Number of failed attempts for verification.
`total_attempts`	`INTEGER`		Number of total attempts for verification.
`timestamp_created`	`TIMESTAMP`	`NOT NULL DEFAULT CURRENT_TIMESTAMP`	Timestamp when process was started.
`timestamp_expiration`	`TIMESTAMP`	`NOT NULL`	Timestamp when the OTP expires.
`timestamp_last_updated`	`TIMESTAMP`		Timestamp when record was last updated.
`timestamp_verified`	`TIMESTAMP`		Timestamp when OTP was verified.
`timestamp_failed`	`TIMESTAMP`		Timestamp when OTP failed.

es_identity_verification Identity Verification Table

Stores information related to identity verification within an onboarding process.

Schema

Name	Type	Info	Note
`id`	`VARCHAR(36)`	`NOT NULL PRIMARY KEY`	Autogenerated record identifier (UUID).
`activation_id`	`VARCHAR(36)`	`NOT NULL`	Activation identifier.
`user_id`	`VARCHAR(256)`	`NOT NULL`	User identifier.
`process_id`	`VARCHAR(36)`	`NOT NULL`	Onboarding process identifier.
`status`	`VARCHAR(32)`	`NOT NULL`	Status of customer verification (`NOT_INITIALIZED`, `IN_PROGRESS`, `VERIFICATION_PENDING`, `ACCEPTED`, `REJECTED`, `FAILED`).
`phase`	`VARCHAR(32)`	`NOT NULL`	Current phase (`DOCUMENT_UPLOAD`, `DOCUMENT_VERIFICATION`, `PRESENCE_CHECK`, `CLIENT_EVALUATION`, `OTP_VERIFICATION`, `COMPLETED`).
`reject_reason`	`TEXT`		Reason for the identity verification rejection.
`reject_origin`	`VARCHAR(256)`		Origin of the rejection (`DOCUMENT_VERIFICATION`, `PRESENCE_CHECK`).
`error_detail`	`VARCHAR(256)`		Detail of error (e.g. information about timeout or exceeded number of failed attempts).
`error_origin`	`VARCHAR(256)`		Origin of the error (`DOCUMENT_VERIFICATION`, `PRESENCE_CHECK`, `CLIENT_EVALUATION`, `OTP_VERIFICATION`, `PROCESS_LIMIT_CHECK`, `USER_REQUEST`).
`session_info`	`INTEGER`		Attributes related to presence check session in JSON format.
`timestamp_created`	`TIMESTAMP`	`NOT NULL DEFAULT CURRENT_TIMESTAMP`	Timestamp when record was created.
`timestamp_last_updated`	`TIMESTAMP`		Timestamp when record was last updated.
`timestamp_finished`	`TIMESTAMP`		Timestamp when identity verification was finished.
`timestamp_failed`	`TIMESTAMP`		Timestamp when identity verification failed.

es_document_verification Document Verification Table

Stores information related to document verification within an identity verification.

Schema

Name	Type	Info	Note
`id`	`VARCHAR(36)`	`NOT NULL PRIMARY KEY`	Autogenerated record identifier (UUID).
`activation_id`	`VARCHAR(36)`	`NOT NULL`	Activation identifier.
`identity_verification_id`	`VARCHAR(36)`	`NOT NULL`	Identity verification identifier.
`type`	`VARCHAR(32)`	`NOT NULL`	Type of document (`ID_CARD`, `PASSPORT`, `DRIVING_LICENSE`, `SELFIE_PHOTO`, `SELFIE_VIDEO`, etc.).
`side`	`VARCHAR(5)`		Document side: `FRONT`, `BACK` or `null`.
`other_side_id`	`VARCHAR(36)`		Identifier of document with opposite side.
`provider_name`	`VARCHAR(64)`		Name of provider which performed the verification.
`status`	`VARCHAR(32)`	`NOT NULL`	Status of document (`IN_PROGRESS`, `ACCEPTED`, `REJECTED`, `FAILED`).
`filename`	`VARCHAR(256)`		Filename specified during upload from mobile client.
`upload_id`	`VARCHAR(36)`		Upload identifier in remote document verification system.
`verification_id`	`VARCHAR(36)`		Verification identifier in remote document verification system.
`photo_id`	`VARCHAR(256)`		Identifier of extracted customer photograph from ID card.
`verification_score`	`INTEGER`		Overall score achieved during document verification and fraud detection (0 - 100).
`reject_reason`	`TEXT`		Overall reason for the document rejection.
`reject_origin`	`VARCHAR(256)`		Origin of the rejection (`DOCUMENT_VERIFICATION`, `PRESENCE_CHECK`).
`error_detail`	`VARCHAR(256)`		Overall error detail in case a generic error occurred.
`error_origin`	`VARCHAR(256)`		Origin of the error (`DOCUMENT_VERIFICATION`, `PRESENCE_CHECK`, `CLIENT_EVALUATION`, `OTP_VERIFICATION`, `PROCESS_LIMIT_CHECK`, `USER_REQUEST`).
`original_document_id`	`VARCHAR(36)`		Identifier of record which was replaced by this record.
`used_for_verification`	`BOOLEAN`	`DEFAULT FALSE`	Whether document is being used for customer verification or it has been replaced by another record.
`timestamp_created`	`TIMESTAMP`	`NOT NULL DEFAULT CURRENT_TIMESTAMP`	Timestamp when record was created.
`timestamp_uploaded`	`TIMESTAMP`		Timestamp when document was uploaded to document verification system.
`timestamp_verified`	`TIMESTAMP`		Timestamp when document was verified in document verification system.
`timestamp_disposed`	`TIMESTAMP`		Timestamp when document was disposed in document verification system.
`timestamp_last_updated`	`TIMESTAMP`		Timestamp when record was last updated.

es_document_data Document Data Table

Stores data for large documents (temporarily) during the identity verification.

Schema

Name	Type	Info	Note
`id`	`VARCHAR(36)`	`NOT NULL PRIMARY KEY`	Autogenerated record identifier (UUID).
`activation_id`	`VARCHAR(36)`	`NOT NULL`	Activation identifier.
`identity_verification_id`	`VARCHAR(36)`	`NOT NULL`	Identity verification identifier.
`filename`	`VARCHAR(256)`	`NOT NULL`	Filename.
`data`	`BYTEA`	`NOT NULL`	Raw file data as BLOB.
`timestamp_created`	`TIMESTAMP`	`NOT NULL DEFAULT CURRENT_TIMESTAMP`	Timestamp when record was created.

es_document_result Document Result Table

Stores result of document verification.

Schema

Name	Type	Info	Note
`id`	`BIGINT`	`NOT NULL PRIMARY KEY`	Autogenerated record identifier (Long).
`document_verification_id`	`VARCHAR(36)`	`NOT NULL`	Document verification identifier.
`phase`	`VARCHAR(32)`	`NOT NULL`	Phase of processing (`UPLOAD`, `VERIFICATION`).
`reject_reason`	`TEXT`		Reason why document was rejected.
`reject_origin`	`VARCHAR(256)`		Origin of the rejection (`DOCUMENT_VERIFICATION`, `PRESENCE_CHECK`).
`verification_result`	`TEXT`		JSON serialized document validation result.
`error_detail`	`VARCHAR(256)`		JSON serialized errors which occurred during document processing.
`error_origin`	`VARCHAR(256)`		Origin of the error (`DOCUMENT_VERIFICATION`, `PRESENCE_CHECK`, `CLIENT_EVALUATION`, `OTP_VERIFICATION`, `PROCESS_LIMIT_CHECK`, `USER_REQUEST`).
`extracted_data`	`TEXT`		JSON serialized data extracted from the uploaded document.
`timestamp_created`	`TEXT`		Timestamp when record was created.

es_sca_result SCA Result Table

Stores result of SCA (Strong Customer Authentication) steps (presence check and OTP verification).

Schema

Name	Type	Info	Note
`id`	`BIGINT`	`NOT NULL PRIMARY KEY`	Autogenerated record identifier (Long).
`identity_verification_id`	`VARCHAR(36)`	`NOT NULL`	Identity verification identifier.
`process_id`	`VARCHAR(36)`	`NOT NULL`	Process identifier (UUID).
`presence_check_result`	`VARCHAR(32)`		Result of presence check (`SUCCESS`, `FAILED`).
`otp_verification_result`	`VARCHAR(32)`		Result of the last OTP verification (`SUCCESS`, `FAILED`).
`sca_result`	`VARCHAR(32)`		Aggregated result of `presence_check_result` and `otp_verification_result` (`SUCCESS`, `FAILED`).
`timestamp_created`	`TIMESTAMP`	`NOT NULL DEFAULT CURRENT_TIMESTAMP`	Timestamp when the SCA was started.
`timestamp_last_updated`	`TIMESTAMP`		Timestamp when record was last updated.

Last updated on Sep 26, 2023 (11:58) Edit on Github Send Feedback