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.

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.

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.

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.

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.

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.

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).

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
Search

1.5.x

Enrollment Server