Smile CDR v2022.11.PRE
On this page:

34.2Setting Up PostgreSQL

 

This page contains details on how to set up databases for use with PostgreSQL.

34.2.1Supported Versions

 

See platform requirements for information on supported versions of PostgreSQL.

34.2.2Creating a Database for Smile CDR

 

A script similar to the following can be used to create a new database named cdr with username cdr and password SmileCDR in PostgreSQL:

	CREATE DATABASE cdr;
	CREATE ROLE cdr LOGIN password 'SmileCDR';
	GRANT ALL PRIVILEGES ON DATABASE cdr TO cdr;

34.2.3Database Connection Properties

 

When configuring Smile CDR to connect to a PostgreSQL database, set the appropriate persistence and cluster manager properties to values similar to the following (making adjustments where necessary, e.g. for hostname, port number, username and password):

module.clustermgr.config.db.driver    =POSTGRES_9_4
module.clustermgr.config.db.url       =jdbc:postgresql://localhost:5432/cdr
module.clustermgr.config.db.username  =cdr
module.clustermgr.config.db.password  =SmileCDR
Note that while the driver type is POSTGRES_9_4, this driver supports Postgres 9.4 and above. This is the driver dialect, not the database version.

34.2.4Setting up a Cluster Manager Database

 

To initialize a new PostgreSQL database for the Cluster Manager, initialize a database with the following SQL:

create sequence SEQ_AG_ADDRESS_PID start 1 increment 50;
create sequence SEQ_AG_APP_REQUEST_PID start 1 increment 50;
create sequence SEQ_AG_APPL_PID start 1 increment 50;
create sequence SEQ_AG_ATTESTATION_PID start 1 increment 50;
create sequence SEQ_AG_CLASS_PID start 1 increment 50;
create sequence SEQ_AG_COUNTRY_PID start 1 increment 50;
create sequence SEQ_AG_DATA_PID start 1 increment 50;
create sequence SEQ_AG_DOCUMENT_PID start 1 increment 50;
create sequence SEQ_AG_DRAFT_PID start 1 increment 50;
create sequence SEQ_AG_MEMBER_PHONE_PID start 1 increment 50;
create sequence SEQ_AG_METADATA_PID start 1 increment 50;
create sequence SEQ_AG_NOTE_PID start 1 increment 50;
create sequence SEQ_AG_OAUTH_REDIRECT_PID start 1 increment 50;
create sequence SEQ_AG_PHONE_PID start 1 increment 50;
create sequence SEQ_AG_PREREQUISITE_PID start 1 increment 50;
create sequence SEQ_AG_PROFILE_PID start 1 increment 50;
create sequence SEQ_AG_SANDBOX_CLIENT_PID start 1 increment 50;
create sequence SEQ_AG_SCOPE_PID start 1 increment 50;
create sequence SEQ_AG_SCREENSHOT_PID start 1 increment 50;
create sequence SEQ_AUDITEVT_HEADER_PID start 1 increment 50;
create sequence SEQ_AUDITEVT_PID start 1 increment 50;
create sequence SEQ_AUDITEVT_TARGETMODULE_PID start 1 increment 50;
create sequence SEQ_AUDITEVT_TARGETRES_PID start 1 increment 50;
create sequence SEQ_AUDITEVT_TARGETUSER_PID start 1 increment 50;
create sequence SEQ_AUDITEVT_TYPE_PID start 1 increment 50;
create sequence SEQ_CACHE_SYNC_PID start 1 increment 50;
create sequence SEQ_CDATEMPLATE_PID start 1 increment 50;
create sequence SEQ_CDATEMPLATEPARAM_PID start 1 increment 50;
create sequence SEQ_FHIRSEARCHPARAM_PID start 1 increment 50;
create sequence SEQ_HTTPSESSION_PID start 1 increment 50;
create sequence SEQ_METRICHC_PID start 1 increment 50;
create sequence SEQ_METRICHEALTH_PID start 1 increment 50;
create sequence SEQ_MODPROC_PID start 1 increment 50;
create sequence SEQ_MODSTAT_PID start 1 increment 50;
create sequence SEQ_MODULE_CFGPROP_PID start 1 increment 50;
create sequence SEQ_MODULE_DEP_PID start 1 increment 50;
create sequence SEQ_MODULE_PID start 1 increment 50;
create sequence SEQ_MODULE_PROT_CFG_PID start 1 increment 50;
create sequence SEQ_MODULE_PROT_DEP_PID start 1 increment 50;
create sequence SEQ_MODULE_PROT_SUPPLIES_PID start 1 increment 50;
create sequence SEQ_MODULE_PROTOTYPE_PID start 1 increment 50;
create sequence SEQ_NODE_PID start 1 increment 50;
create sequence SEQ_NODEPROC_PID start 1 increment 50;
create sequence SEQ_OA2ATOKADNLINFO_PID start 1 increment 50;
create sequence SEQ_OA2ATOKEN_PID start 1 increment 50;
create sequence SEQ_OA2ATOKGRNTDA_PID start 1 increment 50;
create sequence SEQ_OA2ATOKSCP_PID start 1 increment 50;
create sequence SEQ_OA2AUTHCODE_PID start 1 increment 50;
create sequence SEQ_OA2CLIDETAASCOP_PID start 1 increment 50;
create sequence SEQ_OA2CLIDETAGSCOP_PID start 1 increment 50;
create sequence SEQ_OA2CLIDETAUTHGRNTTYP_PID start 1 increment 50;
create sequence SEQ_OA2CLIDETCLISEC_PID start 1 increment 50;
create sequence SEQ_OA2CLIDETREGREDIRURI_PID start 1 increment 50;
create sequence SEQ_OA2CLIENTDETAILS_PID start 1 increment 50;
create sequence SEQ_OA2CLIENTDETAILSSCOPE_PID start 1 increment 50;
create sequence SEQ_OA2CLIENTPERM_PID start 1 increment 50;
create sequence SEQ_OA2REFTOKEN_PID start 1 increment 50;
create sequence SEQ_OA2RTOKADNLINFO_PID start 1 increment 50;
create sequence SEQ_OA2RTOKGRNTDA_PID start 1 increment 50;
create sequence SEQ_OA2RTOKLNCHRESID_PID start 1 increment 50;
create sequence SEQ_OA2RTOKREQPRM_PID start 1 increment 50;
create sequence SEQ_OA2RTOKSCP_PID start 1 increment 50;
create sequence SEQ_OA2SERVER_PID start 1 increment 50;
create sequence SEQ_USER_OA2CLN_TOS_PID start 1 increment 50;
create sequence SEQ_USER_PERM_PID start 1 increment 50;
create sequence SEQ_USER_PID start 1 increment 50;
create sequence SEQ_USER_PSN_PID start 1 increment 50;
create sequence SEQ_USERDEFLNCHCTX_PID start 1 increment 50;
create sequence SEQ_USERTFAKEY_PID start 1 increment 50;
create sequence SEQ_USROA2CLNTAPRVDSCP_PID start 1 increment 50;
create sequence SEQ_XACT_LOG_PID start 1 increment 50;
create sequence SEQ_XACT_LOG_STEP_PID start 1 increment 50;

    create table AG_ADDRESS (
       PID int8 not null,
        ADDRESS_LINE1 varchar(200) not null,
        ADDRESS_LINE2 varchar(200),
        CITY varchar(200) not null,
        COUNTRY varchar(200) not null,
        POSTAL_CODE varchar(200) not null,
        REGION varchar(200) not null,
        AG_PROFILE_PID int8,
        primary key (PID)
    );

    create table AG_APP_REQUEST (
       PID int8 not null,
        APP_DESCRIPTION varchar(400) not null,
        APP_DEVELOPER_NAME varchar(200),
        APP_HOMEPAGE varchar(200) not null,
        APP_NAME varchar(200) not null,
        CREATED_BY varchar(200) not null,
        CREATED_DATE timestamp not null,
        LAST_MODIFIED_BY varchar(200) not null,
        LAST_MODIFIED_DATE timestamp not null,
        MEMBER_COMMENT varchar(200),
        EMAIL varchar(200) not null,
        FIRST_NAME varchar(200) not null,
        LAST_NAME varchar(200) not null,
        NOTE varchar(400),
        REQUEST_STATUS varchar(20) not null,
        MODULE_PID int8 not null,
        primary key (PID)
    );

    create table AG_APPL (
       PID int8 not null,
        ATTESTATION_ACCEPTED boolean not null,
        CONFIDENTIAL boolean,
        CONFIDENTIAL_TYPE varchar(20),
        JWK_SET_URL varchar(4000),
        HOME_URL varchar(200),
        LAUNCH_URL varchar(200),
        LONG_DESC varchar(4000) not null,
        APP_NAME varchar(200) not null,
        OAUTH_REDIRECT_URL varchar(200) not null,
        PRIVACY_POLICY_URL varchar(200) not null,
        SHORT_DESC varchar(200) not null,
        AG_STATUS varchar(20) not null,
        TERMS_OF_SERVICE_URL varchar(200) not null,
        VERSION_NUMBER int8,
        CREATED_BY varchar(200) not null,
        CREATED_DATE timestamp not null,
        LAST_MODIFIED_BY varchar(200) not null,
        LAST_MODIFIED_DATE timestamp not null,
        IDENTIFIER varchar(200),
        SEED varchar(256),
        VIDEO_URL varchar(200),
        VIDEO_URL_STATUS varchar(20),
        JWK_SET_PID int8,
        ATTESTATION_PID int8,
        ICON_PID int8,
        MODULE_PID int8 not null,
        OAUTH2_CLIENT_PID int8,
        PARENT_PID int8,
        PROFILE_PID int8,
        USER_PID int8,
        primary key (PID)
    );

    create table AG_ATTESTATION (
       PID int8 not null,
        ARCHIVE_DATE timestamp,
        CREATED_BY varchar(200) not null,
        CREATED_DATE timestamp not null,
        LAST_MODIFIED_BY varchar(200) not null,
        LAST_MODIFIED_DATE timestamp not null,
        TITLE varchar(200) not null,
        HTML_PID int8,
        MODULE_PID int8 not null,
        PLAIN_HTML_PID int8,
        primary key (PID)
    );

    create table AG_CLASS (
       PID int8 not null,
        CLASSIFICATION varchar(200) not null,
        AG_APPL_PID int8,
        primary key (PID)
    );

    create table AG_COUNTRY (
       PID int8 not null,
        ISO_CODE_ALPHA2 varchar(2) not null,
        AG_APPL_PID int8,
        primary key (PID)
    );

    create table AG_DATA (
       PID int8 not null,
        DATA_LOB oid not null,
        primary key (PID)
    );

    create table AG_DOCUMENT (
       PID int8 not null,
        FILEACTIVE boolean not null,
        CREATED_BY varchar(200) not null,
        CREATED_DATE timestamp not null,
        LAST_MODIFIED_BY varchar(200) not null,
        LAST_MODIFIED_DATE timestamp not null,
        FILEDESC varchar(200),
        FILENAME varchar(200) not null,
        FILETYPE varchar(200) not null,
        DOCUMENT_PID int8 not null,
        AG_APPL_PID int8,
        primary key (PID)
    );

    create table AG_DRAFT (
       PID int8 not null,
        CREATED_BY varchar(200) not null,
        CREATED_DATE timestamp not null,
        LAST_MODIFIED_BY varchar(200) not null,
        LAST_MODIFIED_DATE timestamp not null,
        DATA_HASH int4,
        APP_NAME varchar(200) not null,
        DRAFT_PID int8 not null,
        MODULE_PID int8 not null,
        SOURCE_APP_PID int8,
        USER_PID int8 not null,
        primary key (PID)
    );

    create table AG_MEMBER_PHONE (
       PID int8 not null,
        COUNTRY_CODE varchar(20) not null,
        PHONE_NUMBER varchar(20) not null,
        PHONE_TYPE varchar(20) not null,
        AG_APP_REQUEST_PID int8,
        primary key (PID)
    );

    create table AG_METADATA (
       PID int8 not null,
        METADATA_KEY varchar(100) not null,
        METADATA_SEARCHABLE boolean not null,
        METADATA_VALUE varchar(4000) not null,
        AG_APPL_PID int8,
        primary key (PID)
    );

    create table AG_NOTE (
       PID int8 not null,
        CREATED_BY varchar(200) not null,
        CREATED_DATE timestamp not null,
        LAST_MODIFIED_BY varchar(200) not null,
        LAST_MODIFIED_DATE timestamp not null,
        MESSAGE varchar(1500) not null,
        REASON varchar(1000) not null,
        NOTE_TYPE varchar(20),
        AG_APPL_PID int8,
        primary key (PID)
    );

    create table AG_OAUTH_REDIRECT (
       PID int8 not null,
        OAUTH_REDIRECT_URL varchar(200) not null,
        AG_APPL_PID int8,
        primary key (PID)
    );

    create table AG_PHONE (
       PID int8 not null,
        COUNTRY_CODE varchar(20) not null,
        PHONE_NUMBER varchar(20) not null,
        PHONE_TYPE varchar(20) not null,
        AG_PROFILE_PID int8,
        primary key (PID)
    );

    create table AG_PREREQUISITE (
       PID int8 not null,
        PREREQUISITE varchar(200) not null,
        AG_APPL_PID int8,
        primary key (PID)
    );

    create table AG_PROFILE (
       PID int8 not null,
        ABOUT_US varchar(250),
        ARCHIVE_DATE timestamp,
        CREATED_BY varchar(200) not null,
        CREATED_DATE timestamp not null,
        LAST_MODIFIED_BY varchar(200) not null,
        LAST_MODIFIED_DATE timestamp not null,
        BUSINESS_NAME varchar(200),
        DESIGNATION varchar(200),
        DUNS_NUMBER varchar(200),
        FULL_NAME varchar(200) not null,
        INCORPORATED boolean,
        INDIVIDUAL boolean not null,
        SALES_CONTACT varchar(200),
        SUPPORT_CONTACT varchar(200),
        SUPPORT_WEBPAGE varchar(200),
        PROFILE_TYPE varchar(20),
        WEBSITE_URL varchar(200),
        MODULE_PID int8 not null,
        USER_PID int8 not null,
        primary key (PID)
    );

    create table AG_SANDBOX_CLIENT (
       PID int8 not null,
        SEED varchar(256),
        MODULE_PID int8 not null,
        OAUTH2_CLIENT_PID int8 not null,
        USER_PID int8 not null,
        primary key (PID)
    );

    create table AG_SCOPE (
       PID int8 not null,
        SCOPE varchar(200) not null,
        AG_APPL_PID int8,
        primary key (PID)
    );

    create table AG_SCREENSHOT (
       PID int8 not null,
        FILENAME varchar(200) not null,
        POSITION int4,
        ASSET_STATUS varchar(20) not null,
        SCREENSHOT_PID int8 not null,
        AG_APPL_PID int8,
        primary key (PID)
    );

    create table CDR_AUDIT_EVT (
       PID int8 not null,
        EVT_ADDITIONAL_JSON oid,
        AUSER_TYPE int4,
        HAVE_HEADERS boolean,
        REMOTE_ADDRESS varchar(100),
        REQUEST_ID varchar(128),
        HAVE_TARGET_MODULES boolean not null,
        SOURCE_TRANSACTION_ID varchar(128),
        HAVE_TARGET_RESOURCES boolean not null,
        HAVE_TARGET_USERS boolean not null,
        EVT_TIMESTAMP timestamp not null,
        TRANSACTION_GUID varchar(36),
        TYPE_DISPLAY varchar(200) not null,
        USER_PID int8,
        CLIENT_PID int8,
        ENDPOINT_MODULE_PID int8 not null,
        TYPE_PID int8 not null,
        primary key (PID)
    );

    create table CDR_AUDIT_EVT_HEADER (
       PID int8 not null,
        EVT_HEADER_NAME varchar(100) not null,
        EVT_HEADER_VALUE varchar(250),
        EVENT_PID int8 not null,
        primary key (PID)
    );

    create table CDR_AUDIT_EVT_TARGET_MODULE (
       PID int8 not null,
        EVENT_PID int8 not null,
        MODULE_PID int8 not null,
        primary key (PID)
    );

    create table CDR_AUDIT_EVT_TARGET_RES (
       PID int8 not null,
        REQ_VAL_RESULT int4,
        RES_ID varchar(120) not null,
        RES_VERSION int8,
        RES_VERSION_STR varchar(128),
        EVENT_PID int8 not null,
        PERS_MODULE_PID int8 not null,
        primary key (PID)
    );

    create table CDR_AUDIT_EVT_TARGET_USER (
       PID int8 not null,
        USER_PID int8 not null,
        EVENT_PID int8 not null,
        primary key (PID)
    );

    create table CDR_AUDIT_EVT_TYPE (
       PID int8 not null,
        CODEVAL varchar(200),
        SYSTEM_URL varchar(200),
        primary key (PID)
    );

    create table CDR_CACHE_SYNC (
       PID int8 not null,
        CACHE_ID varchar(256) not null,
        MODULE_PID int8 not null,
        UPDATED_TIME timestamp not null,
        VERSION int8 not null,
        primary key (PID)
    );

    create table CDR_CDA_TEMPLATE (
       PID int8 not null,
        TEMPLATE_DESCRIPTION varchar(250),
        TEMPLATE_ID varchar(250) not null,
        TEMPLATE_CONTENT oid not null,
        MODULE_PID int8 not null,
        primary key (PID)
    );

    create table CDR_CDA_TEMPLATE_PARAM (
       PID int8 not null,
        TEMPLATE_PARAMETER_NAME varchar(250) not null,
        TEMPLATE_PID int8,
        primary key (PID)
    );

    create table CDR_DB_VERSION (
       DB_VERSION varchar(100) not null,
        DB_INIT_TIME timestamp not null,
        primary key (DB_VERSION)
    );

    create table CDR_FHIR_SEARCHPARAM (
       PID int8 not null,
        SP_BASE varchar(500) not null,
        SP_CODE varchar(500) not null,
        SP_DESC varchar(500),
        SP_FHIR_STATUS varchar(255),
        LAST_UPDATED timestamp not null,
        LAST_USED timestamp,
        STAT_OVERALL_COUNT int8,
        SP_PATH varchar(500),
        STAT_RESOURCE_SPREAD float8 not null,
        SP_RES_ID varchar(150),
        SP_SYNC_STATUS varchar(255) not null,
        SP_TITLE varchar(500),
        SP_TYPE varchar(20),
        BUF_UNIQUE_RESOURCES oid,
        BUF_UNIQUE_VALUES oid,
        STAT_VALUE_SPREAD float8 not null,
        MODULE_PID int8 not null,
        primary key (PID)
    );

    create table CDR_GLOBAL_GAUGE (
       ID varchar(200) not null,
        VAL_DOUBLE float8,
        VAL_LONG int8,
        primary key (ID)
    );

    create table CDR_HTTP_SESSION (
       PID int8 not null,
        SES_ATTRS oid not null,
        SES_EXPIRY timestamp not null,
        LAST_ACCESSED_TIME timestamp,
        MAX_INACTIVE int8 not null,
        PRINCIPAL_USERNAME varchar(200),
        SES_ID varchar(200) not null,
        MODULE_PID int8 not null,
        primary key (PID)
    );

    create table CDR_METRIC_GAUGE (
       GAUGE_TYPE int4 not null,
        INTERVL int4 not null,
        START_TIME timestamp not null,
        IS_COLLAPSED boolean not null,
        GAUGE_VAL int8 not null,
        MODPROC_PID int8 not null,
        primary key (GAUGE_TYPE, INTERVL, MODPROC_PID, START_TIME)
    );

    create table CDR_METRIC_HC (
       PID int8 not null,
        ERROR_STRING varchar(200),
        HEALTHCHECK_TYPE int4 not null,
        IS_HEALTHY boolean not null,
        HEALTHY_STRING varchar(200),
        MODPROC_PID int8 not null,
        primary key (PID)
    );

    create table CDR_METRIC_HEALTH (
       PID int8 not null,
        CUSTOM_HEALTHCHECK_NAME varchar(256),
        ERROR_STRING varchar(256),
        HEALTHCHECK_TYPE int4 not null,
        IS_HEALTHY boolean not null,
        HEALTHY_STRING varchar(256),
        MODPROC_PID int8 not null,
        primary key (PID)
    );

    create table CDR_METRIC_TIMER (
       INTERVL int4 not null,
        START_TIME timestamp not null,
        TIMER_TYPE int4 not null,
        IS_COLLAPSED boolean not null,
        COUNT_VAL int8,
        RATE_15_MIN float4 not null,
        FIRST_SINCE_RS boolean not null,
        RATE_5_MIN float4 not null,
        LATENCY_MAX int4 not null,
        LATENCY_MEAN int4 not null,
        LATENCY_MIN int4 not null,
        MODPROC_PID int8 not null,
        RATE_1_MIN float4 not null,
        primary key (INTERVL, MODPROC_PID, START_TIME, TIMER_TYPE)
    );

    create table CDR_MODULE (
       PID int8 not null,
        DELETED boolean not null,
        MODULE_DISABLED boolean not null,
        MATURITY int4,
        MODULE_ID varchar(50) not null,
        PORTVAL int4,
        NODE_PID int8 not null,
        PROTOTYPE_PID int8 not null,
        primary key (PID)
    );

    create table CDR_MODULE_CFG_PROP (
       PID int8 not null,
        VALUE_STRING varchar(200),
        VALUE_EXTENDED oid,
        MODULE_PID int8 not null,
        PROTOTYPE_PID int8 not null,
        primary key (PID)
    );

    create table CDR_MODULE_DEP (
       PID int8 not null,
        MODULE_PID int8 not null,
        PROTOTYPE_PID int8 not null,
        TARGET_PID int8,
        primary key (PID)
    );

    create table CDR_MODULE_PROCESS (
       PID int8 not null,
        FIRST_MP boolean,
        MODULE_PID int8 not null,
        NODEPROC_PID int8 not null,
        RESTART_REQUIRED boolean not null,
        MODULE_STATUS int4 not null,
        MODULE_STATUS_DESC varchar(200),
        STATUS_TIMESTAMP timestamp,
        primary key (PID)
    );

    create table CDR_MODULE_PROT_CFG (
       PID int8 not null,
        IS_ADVANCED boolean not null,
        CATEGORY_KEY varchar(100) not null,
        DEFAULT_VALUE varchar(200),
        DESC_KEY varchar(200),
        ENUM_TYPE varchar(200),
        CFG_INDEX int4 not null,
        CFG_KEY varchar(200) not null,
        CFG_MATURITY varchar(200),
        NAME_KEY varchar(200) not null,
        IS_OPTIONAL boolean not null,
        CFG_TYPE varchar(100) not null,
        PROTOTYPE_PID int8 not null,
        primary key (PID)
    );

    create table CDR_MODULE_PROT_DEP (
       PID int8 not null,
        DESCRIPTION_KEY varchar(200) not null,
        ENTRY_KEY varchar(200) not null,
        PROTOTYPE_PID int8,
        primary key (PID)
    );

    create table CDR_MODULE_PROT_SUPPLIES (
       PID int8 not null,
        ENTRY_KEY int4 not null,
        PROTOTYPE_PID int8,
        primary key (PID)
    );

    create table CDR_MODULE_PROTOTYPE (
       PID int8 not null,
        MODULE_TYPE varchar(200) not null,
        STOPPABLE boolean not null,
        primary key (PID)
    );

    create table CDR_MODULE_STATE (
       PID int8 not null,
        STATE_TYPE int4 not null,
        STATE_VAL varchar(200),
        MODULE_PID int8 not null,
        primary key (PID)
    );

    create table CDR_NODE (
       PID int8 not null,
        IS_CONFIG_LOCKED boolean,
        IS_DELETED boolean,
        NODE_ID varchar(30) not null,
        IS_SECURITY_STRICT boolean,
        primary key (PID)
    );

    create table CDR_NODE_PROCESS (
       PID int8 not null,
        CREATED_TIME timestamp not null,
        HEARTBEAT_TIME timestamp,
        KILLED_BY varchar(20),
        NODE_PID int8 not null,
        PROCESS_ID varchar(20) not null,
        PROCESS_NAME varchar(100),
        STARTED_TIME timestamp,
        NODE_STATUS int4 not null,
        STOPPED_TIME timestamp,
        OPT_LOCK int4 not null,
        primary key (PID)
    );

    create table CDR_OA2_CLI_DET_AUTH_GRNT_TYP (
       PID int8 not null,
        GRANT_TYPE varchar(100) not null,
        CLIENT_PID int8 not null,
        primary key (PID)
    );

    create table CDR_OA2_CLI_DET_AUTOAP_SCOPE (
       PID int8 not null,
        SCOPE varchar(764) not null,
        CLIENT_PID int8 not null,
        primary key (PID)
    );

    create table CDR_OA2_CLI_DET_AUTOGRNT_SCOPE (
       PID int8 not null,
        SCOPE varchar(764) not null,
        CLIENT_PID int8 not null,
        primary key (PID)
    );

    create table CDR_OA2_CLI_DET_CLISEC (
       PID int8 not null,
        SEC_ACTIVATION timestamp,
        SEC_CREATION timestamp,
        SEC_DESC varchar(250),
        SEC_EXPIRATION timestamp,
        CLI_SECRET varchar(250) not null,
        CLIENT_PID int8 not null,
        primary key (PID)
    );

    create table CDR_OA2_CLI_DET_REG_REDIR_URI (
       PID int8 not null,
        REDIR_URI varchar(200) not null,
        CLIENT_PID int8 not null,
        primary key (PID)
    );

    create table CDR_OA2_SERVER (
       PID int8 not null,
        AUTH_WELL_KNOWN_CONFIG_URL varchar(500),
        CUSTOM_TOKEN_PARAMS varchar(500),
        FEDERATION_AUTH_SCRIPT_TEXT oid,
        FEDERATION_AUTH_URL varchar(500),
        FEDERATION_JWKS_URL varchar(500),
        FEDERATION_REG_ID varchar(500) not null,
        FEDERATION_REQ_SCOPES varchar(500),
        FEDERATION_TOKEN_URL varchar(500),
        FEDERATION_USER_INFO_URL varchar(500),
        FEDERATION_USER_MAPPING_TEXT oid,
        FHIR_ENDPOINT_URL varchar(500),
        ISS varchar(200) not null,
        SERVER_NAME varchar(200),
        NOTES varchar(1000),
        ORGANIZATION_ID varchar(500),
        RESPONSE_TYPE varchar(500),
        INTRSPCT_CLIENT_ID varchar(200),
        INTRSPCT_CLIENT_SCRT varchar(200),
        JWKS_FILE varchar(500),
        JWKS_TEXT oid,
        MODULE_PID int8 not null,
        primary key (PID)
    );

    create table CDR_OAUTH2_ATOKEN (
       PID int8 not null,
        EXPIRATION timestamp not null,
        GRANT_TYPE varchar(20),
        ID_TOKEN_BYTES oid,
        ID_TOKEN_VAL varchar(1000),
        ISSUED timestamp not null,
        REDIRECT_URI varchar(200),
        TOKEN_HASH varchar(150) not null,
        TOKEN_BYTES oid,
        TOKEN_VAL varchar(1000),
        CLIENT_PID int8 not null,
        MODULE_PID int8 not null,
        REFRESH_TOKEN_PID int8,
        USER_PID int8,
        primary key (PID)
    );

    create table CDR_OAUTH2_ATOKEN_ADNLINFO (
       PID int8 not null,
        INFO_KEY varchar(200) not null,
        INFO_VALUE varchar(200) not null,
        TOKEN_PID int8 not null,
        primary key (PID)
    );

    create table CDR_OAUTH2_ATOKEN_GRNTDA (
       PID int8 not null,
        PERM_ARG varchar(764),
        PERM_NAME varchar(100) not null,
        TOKEN_PID int8 not null,
        primary key (PID)
    );

    create table CDR_OAUTH2_ATOKEN_SCOPE (
       PID int8 not null,
        SCOPE varchar(764) not null,
        TOKEN_PID int8 not null,
        primary key (PID)
    );

    create table CDR_OAUTH2_ATOKEN_UDATA (
       ATOKEN_PID int8 not null,
        TOKEN_KEY varchar(256) not null,
        VAL oid,
        primary key (ATOKEN_PID, TOKEN_KEY)
    );

    create table CDR_OAUTH2_AUTH_CODE (
       PID int8 not null,
        OAUTH2_AUTHENTICATION oid,
        CLIENT_ID varchar(200) not null,
        CODE_VAL varchar(100) not null,
        EXPIRATION timestamp not null,
        PKCE_CHALLENGE varchar(200),
        PKCE_CHALLENGE_TYPE varchar(10),
        STATE_VAL varchar(256),
        primary key (PID)
    );

    create table CDR_OAUTH2_CLIENT_DET_SCOPE (
       PID int8 not null,
        SCOPE varchar(764) not null,
        CLIENT_PID int8 not null,
        primary key (PID)
    );

    create table CDR_OAUTH2_CLIENT_DETAILS (
       PID int8 not null,
        ACCESS_TOKEN_VALIDITY_SECONDS int4,
        ALWAYS_REQUIRE_APPROVAL boolean,
        ATTESTATION_ACCEPTED boolean,
        CAN_INTROSPECT_ANY boolean,
        CAN_INTROSPECT_OWN boolean,
        CAN_REUSE_TOKENS boolean,
        CLIENT_ID varchar(200) not null,
        CLIENT_NAME varchar(200),
        CLIENT_SECRET varchar(200),
        ENABLED boolean,
        FIXED_SCOPE boolean not null,
        JWKS_URL varchar(4000),
        PUBLIC_JWKS oid,
        REFRESH_TOKEN_VALIDITY_SECONDS int4,
        REMEMBER_APPROVED_SCOPES boolean,
        SECRET_CLIENT_CAN_CHANGE boolean,
        SECRET_REQUIRED boolean not null,
        MODULE_PID int8,
        primary key (PID)
    );

    create table CDR_OAUTH2_CLIENT_PERM (
       PID int8 not null,
        PERMISSION_TYPE varchar(100) not null,
        PERMISSION_ARG varchar(200),
        CLIENT_PID int8 not null,
        primary key (PID)
    );

    create table CDR_OAUTH2_JWT_UNIQ_ID (
       JWT_ID varchar(500) not null,
        CONSUMED_TIMESTAMP timestamp not null,
        primary key (JWT_ID)
    );

    create table CDR_OAUTH2_KEYSTORE (
       KEYSTORE_ID varchar(50) not null,
        FILE_PATH varchar(4000),
        JSON_KEYS varchar(4000),
        primary key (KEYSTORE_ID)
    );

    create table CDR_OAUTH2_REFRESH_TOKEN (
       PID int8 not null,
        EXPIRATION timestamp,
        ISSUED timestamp not null,
        TOKEN_VAL varchar(150) not null,
        CLIENT_PID int8,
        USER_PID int8,
        primary key (PID)
    );

    create table CDR_OAUTH2_RTOKEN_ADNLINFO (
       PID int8 not null,
        INFO_KEY varchar(200) not null,
        INFO_VALUE varchar(200) not null,
        TOKEN_PID int8 not null,
        primary key (PID)
    );

    create table CDR_OAUTH2_RTOKEN_GRNTDA (
       PID int8 not null,
        PERM_ARG varchar(764),
        PERM_NAME varchar(100) not null,
        TOKEN_PID int8 not null,
        primary key (PID)
    );

    create table CDR_OAUTH2_RTOKEN_LNCHRESID (
       PID int8 not null,
        RESOURCE_ID varchar(64) not null,
        RESOURCE_TYPE varchar(50) not null,
        TOKEN_PID int8 not null,
        primary key (PID)
    );

    create table CDR_OAUTH2_RTOKEN_REQPRM (
       PID int8 not null,
        PARM_KEY varchar(200) not null,
        PARM_VALUE varchar(1600),
        TOKEN_PID int8 not null,
        primary key (PID)
    );

    create table CDR_OAUTH2_RTOKEN_SCOPE (
       PID int8 not null,
        SCOPE varchar(100) not null,
        TOKEN_PID int8 not null,
        primary key (PID)
    );

    create table CDR_OAUTH2_RTOKEN_UDATA (
       TOKEN_PID int8 not null,
        TOKEN_KEY varchar(256) not null,
        VAL oid,
        primary key (TOKEN_PID, TOKEN_KEY)
    );

    create table CDR_USER_DETAILS (
       PID int8 not null,
        IS_ANON_ACCT boolean,
        CREDS_EXPIRE_ON timestamp,
        USER_DISABLED boolean not null,
        EMAIL varchar(200),
        EXPIRES_ON timestamp,
        IS_EXTERNAL boolean,
        FAILED_LOGIN_ATTEMPTS int4,
        FAMILY_NAME varchar(200),
        GIVEN_NAME varchar(200),
        LAST_ACTIVE timestamp,
        LAST_CONNECTED timestamp,
        USER_LOCKED boolean not null,
        MODULE_PID int8 not null,
        NOTES oid,
        USER_PASSWORD varchar(200),
        IS_SERVICE_ACCT boolean,
        IS_SYSTEM_USER boolean not null,
        UPDATED_TS timestamp not null,
        USERNAME varchar(200) not null,
        USERNAME_HAS_NAMESPACE boolean,
        primary key (PID)
    );

    create table CDR_USER_DETAILS_DEF_LNCH_CTX (
       PID int8 not null,
        CONTEXT_TYPE varchar(50) not null,
        RESOURCE_ID varchar(64) not null,
        USER_PID int8 not null,
        primary key (PID)
    );

    create table CDR_USER_DETAILS_OA2CLNAPRVSCP (
       PID int8 not null,
        IS_APPROVED boolean not null,
        SCOPE varchar(100) not null,
        CLIENT_PID int8 not null,
        USER_PID int8 not null,
        primary key (PID)
    );

    create table CDR_USER_OA2CLN_TOS (
       PID int8 not null,
        CREATED_BY varchar(200) not null,
        CREATED_DATE timestamp not null,
        REVOKED_BY varchar(200),
        REVOKED_DATE timestamp,
        VERSION varchar(200) not null,
        CLIENT_PID int8 not null,
        USER_PID int8 not null,
        primary key (PID)
    );

    create table CDR_USER_PERM (
       PID int8 not null,
        PERMISSION_TYPE varchar(100) not null,
        PERMISSION_ARG varchar(200),
        USER_PID int8,
        primary key (PID)
    );

    create table CDR_USER_PSN (
       PID int8 not null,
        CREATED_BY varchar(200) not null,
        CREATED_DATE timestamp not null,
        REVOKED_BY varchar(200),
        REVOKED_DATE timestamp,
        USER_PID int8 not null,
        VERSION varchar(200) not null,
        primary key (PID)
    );

    create table CDR_USER_TFA_KEY (
       PID int8 not null,
        CONFIRMED_AT timestamp,
        KEY_EXPIRES timestamp,
        FAILED_VERIFS int4,
        A_KEY varchar(250) not null,
        A_STYLE varchar(20) not null,
        OPTLOCK int4 not null,
        USER_PID int8 not null,
        primary key (PID)
    );

    create table CDR_XACT_LOG (
       PID int8 not null,
        INITIAL_TIMESTAMP timestamp not null,
        EVT_OUTCOME int4 not null,
        SRC_XACT_ID varchar(128),
        EVT_SUBTYPE int4 not null,
        SRC_GUID varchar(36),
        EVT_TYPE int4 not null,
        USER_PID int8,
        CLIENT_PID int8,
        CDR_ENDPOINT_MODULE_PID int8,
        primary key (PID)
    );

    create table CDR_XACT_LOG_STEP (
       STEP_PID int8 not null,
        BODY_BYTES oid,
        BODY_TYPE int4,
        LOCAL_HOST varchar(100),
        LOCAL_PORT int4,
        EVT_OUTCOME int4,
        PROCESSING_TIME int8,
        REMOTE_HOST varchar(100),
        REMOTE_PORT int4,
        REQUEST_URL_STR varchar(250),
        REQ_VAL_RESULT int4,
        REQUEST_VERB varchar(7),
        RESPONSE_STATUS int2,
        SUBSCRIPTION_ID varchar(77),
        LOG_TIMESTAMP timestamp not null,
        STEP_TYPE int4 not null,
        LOG_PID int8 not null,
        primary key (STEP_PID)
    );
create index IDX_AG_APPL_IDENTIFIER on AG_APPL (IDENTIFIER);
create index IDX_AG_ATTEST_ARCHIVE_DATE on AG_ATTESTATION (ARCHIVE_DATE);

    alter table if exists AG_DRAFT 
       add constraint IDX_AG_DRAFT_APPNAME_USER unique (APP_NAME, USER_PID);

    alter table if exists AG_OAUTH_REDIRECT 
       add constraint IDX_AG_OAUTH_REDIRECT_PID unique (OAUTH_REDIRECT_URL, PID);
create index IDX_AE_TGUID on CDR_AUDIT_EVT (TRANSACTION_GUID);
create index IDX_AUDITEVT_HEADER_EVT on CDR_AUDIT_EVT_HEADER (EVENT_PID);
create index IDX_AUDITEVT_TARGMOD_EVT on CDR_AUDIT_EVT_TARGET_MODULE (EVENT_PID);
create index IDX_AUDITEVT_TARGRES_EVT on CDR_AUDIT_EVT_TARGET_RES (EVENT_PID);
create index IDX_AUDITEVT_TARGUSR_EVT on CDR_AUDIT_EVT_TARGET_USER (EVENT_PID);

    alter table if exists CDR_AUDIT_EVT_TYPE 
       add constraint IDX_AUDITEVTTYPE_SYSTEM_CODE unique (SYSTEM_URL, CODEVAL);

    alter table if exists CDR_CACHE_SYNC 
       add constraint IDX_CACHE_SYNC_ID unique (MODULE_PID, CACHE_ID);
create index IDX_CDR_TEMPLATE_ID on CDR_CDA_TEMPLATE (TEMPLATE_ID);

    alter table if exists CDR_CDA_TEMPLATE 
       add constraint IDX_CDATEMPL_ID unique (TEMPLATE_ID);
create index IDX_FHIRSP_SP_RES_ID on CDR_FHIR_SEARCHPARAM (MODULE_PID, SP_RES_ID);
create index IDX_FHIRSP_BASE on CDR_FHIR_SEARCHPARAM (MODULE_PID, SP_BASE);
create index IDX_FHIRSP_CODE on CDR_FHIR_SEARCHPARAM (MODULE_PID, SP_CODE);
create index IDX_FHIRSP_OVERALLCOUNT on CDR_FHIR_SEARCHPARAM (MODULE_PID, STAT_OVERALL_COUNT);
create index IDX_FHIRSP_RESSPREAD on CDR_FHIR_SEARCHPARAM (MODULE_PID, STAT_RESOURCE_SPREAD);
create index IDX_FHIRSP_TYPE on CDR_FHIR_SEARCHPARAM (MODULE_PID, SP_TYPE);
create index IDX_FHIRSP_VALSPREAD on CDR_FHIR_SEARCHPARAM (MODULE_PID, STAT_VALUE_SPREAD);
create index IDX_FHIRSP_LASTUPDATED on CDR_FHIR_SEARCHPARAM (MODULE_PID, LAST_UPDATED);
create index IDX_FHIRSP_LASTUSED on CDR_FHIR_SEARCHPARAM (MODULE_PID, LAST_USED);
create index IDX_HTTPSESSION_EXPIRY on CDR_HTTP_SESSION (SES_EXPIRY);
create index IDX_HTTPSESSION_MODULE on CDR_HTTP_SESSION (MODULE_PID);

    alter table if exists CDR_HTTP_SESSION 
       add constraint IDX_HTTPSESSION_ID unique (SES_ID);
create index IDX_GAUGE_MODCOLLINTSTART on CDR_METRIC_GAUGE (MODPROC_PID, GAUGE_TYPE, INTERVL, START_TIME);
create index IDX_GAUGE_MODCOLLSTART on CDR_METRIC_GAUGE (MODPROC_PID, IS_COLLAPSED, START_TIME);

    alter table if exists CDR_METRIC_HC 
       add constraint IDX_METRICHC_NODEP_HID unique (MODPROC_PID, HEALTHCHECK_TYPE);

    alter table if exists CDR_METRIC_HEALTH 
       add constraint IDX_METRICHC_MOD_TYPE_NAME unique (MODPROC_PID, HEALTHCHECK_TYPE, CUSTOM_HEALTHCHECK_NAME);
create index IDX_TIMER_MODCOLLINTSTART on CDR_METRIC_TIMER (MODPROC_PID, TIMER_TYPE, INTERVL, START_TIME);
create index IDX_TIMER_MODCOLLSTART on CDR_METRIC_TIMER (MODPROC_PID, IS_COLLAPSED, START_TIME);

    alter table if exists CDR_MODULE 
       add constraint IDX_MODULE_ID unique (NODE_PID, MODULE_ID);

    alter table if exists CDR_MODULE_CFG_PROP 
       add constraint IDX_CDR_MODULE_CDR_PROP_MODKEY unique (MODULE_PID, PROTOTYPE_PID);

    alter table if exists CDR_MODULE_PROCESS 
       add constraint IDX_MODPROC_NODEPROC_MOD unique (NODEPROC_PID, MODULE_PID);

    alter table if exists CDR_MODULE_PROT_CFG 
       add constraint IDX_CDR_MODULE_PROT_CDG_MODKEY unique (PROTOTYPE_PID, CFG_KEY);

    alter table if exists CDR_MODULE_PROT_DEP 
       add constraint IDX_MODULEPROTDEP_PROT_KEY unique (PROTOTYPE_PID, ENTRY_KEY);

    alter table if exists CDR_MODULE_PROTOTYPE 
       add constraint IDX_MODULEPROTOTYPE_TYPE unique (MODULE_TYPE);
create index IDX_MODSTAT_MOD on CDR_MODULE_STATE (MODULE_PID);

    alter table if exists CDR_MODULE_STATE 
       add constraint IDX_MODSTAT_MODTYPE unique (MODULE_PID, STATE_TYPE);

    alter table if exists CDR_NODE 
       add constraint IDX_NODE_ID unique (NODE_ID);

    alter table if exists CDR_NODE_PROCESS 
       add constraint IDX_NODEPROC_PROCID unique (PROCESS_ID);

    alter table if exists CDR_OA2_CLI_DET_AUTH_GRNT_TYP 
       add constraint IDX_OA2TOKGRTTYP_TOK_GRNT_TYP unique (CLIENT_PID, GRANT_TYPE);

    alter table if exists CDR_OA2_CLI_DET_AUTOAP_SCOPE 
       add constraint IDX_OA2CLIDETAASCOPE_TOK_SCOP unique (CLIENT_PID, SCOPE);

    alter table if exists CDR_OA2_CLI_DET_AUTOGRNT_SCOPE 
       add constraint IDX_OA2CLIDETAGSCOPE_TOK_SCOP unique (CLIENT_PID, SCOPE);

    alter table if exists CDR_OA2_CLI_DET_CLISEC 
       add constraint IDX_OA2CLIDETCLISEC_SECRET unique (CLIENT_PID, CLI_SECRET);

    alter table if exists CDR_OA2_CLI_DET_REG_REDIR_URI 
       add constraint IDX_OA2CLIDETRRURI_TOK_SCO unique (CLIENT_PID, REDIR_URI);

    alter table if exists CDR_OA2_SERVER 
       add constraint IDX_OA2SERVER_FED_REG_ID unique (MODULE_PID, FEDERATION_REG_ID);

    alter table if exists CDR_OA2_SERVER 
       add constraint IDX_OA2SERVER_MODULE_ISSURL unique (MODULE_PID, ISS);
create index IDX_OA2ATOKEN_TOKEN_HASH on CDR_OAUTH2_ATOKEN (TOKEN_HASH);
create index IDX_OA2ATOKEN_USER on CDR_OAUTH2_ATOKEN (USER_PID);
create index IDX_OA2ATOKEN_EXPIRATION on CDR_OAUTH2_ATOKEN (EXPIRATION);

    alter table if exists CDR_OAUTH2_ATOKEN_ADNLINFO 
       add constraint IDX_OA2ATOKADLNF_ATOKEN_ADNLNF unique (TOKEN_PID, INFO_KEY);
create index IDX_OA2ATOKGRNT_ATOKEN_PERM on CDR_OAUTH2_ATOKEN_GRNTDA (TOKEN_PID, PERM_NAME);

    alter table if exists CDR_OAUTH2_ATOKEN_SCOPE 
       add constraint IDX_OA2ATOKSCP_ATOKEN_SCOPE unique (TOKEN_PID, SCOPE);
create index IDX_OA2AUTHCODE_EXPIRATION on CDR_OAUTH2_AUTH_CODE (EXPIRATION);

    alter table if exists CDR_OAUTH2_AUTH_CODE 
       add constraint IDX_OA2AUTHCODE_CODE unique (CODE_VAL);

    alter table if exists CDR_OAUTH2_CLIENT_DET_SCOPE 
       add constraint IDX_OA2CLIDETSCOP_TOKEN_SCOPE unique (CLIENT_PID, SCOPE);

    alter table if exists CDR_OAUTH2_CLIENT_DETAILS 
       add constraint IDX_OA2CLIENTDETAILS_CLI_MOD unique (MODULE_PID, CLIENT_ID);
create index IDX_OA2JWTUNIQID_TIMESTAMP on CDR_OAUTH2_JWT_UNIQ_ID (CONSUMED_TIMESTAMP);
create index IDX_OA2REFTOKEN_USER on CDR_OAUTH2_REFRESH_TOKEN (USER_PID);
create index IDX_OA2REFTOKEN_EXPIRATION on CDR_OAUTH2_REFRESH_TOKEN (EXPIRATION);

    alter table if exists CDR_OAUTH2_REFRESH_TOKEN 
       add constraint IDX_OA2REFTOKEN_TOKEN unique (TOKEN_VAL);

    alter table if exists CDR_OAUTH2_RTOKEN_ADNLINFO 
       add constraint IDX_OA2RTOKADLNF_RTOKEN_ADNLNF unique (TOKEN_PID, INFO_KEY);

    alter table if exists CDR_OAUTH2_RTOKEN_REQPRM 
       add constraint IDX_OA2RTOKREQPRM_RTOKEN_PARM unique (TOKEN_PID, PARM_KEY);

    alter table if exists CDR_OAUTH2_RTOKEN_SCOPE 
       add constraint IDX_OA2RTOKSCP_RTOKEN_SCOPE unique (TOKEN_PID, SCOPE);
create index IDX_USER_FAMILYNAME on CDR_USER_DETAILS (MODULE_PID, FAMILY_NAME);
create index IDX_USER_GIVENNAME on CDR_USER_DETAILS (MODULE_PID, GIVEN_NAME);

    alter table if exists CDR_USER_DETAILS 
       add constraint IDX_USERDET_MODULE_USERNAME unique (MODULE_PID, USERNAME);
create index IDX_USERDEFLNCHCTX_USER on CDR_USER_DETAILS_DEF_LNCH_CTX (USER_PID);

    alter table if exists CDR_USER_DETAILS_DEF_LNCH_CTX 
       add constraint IDX_USERDEFLNCHCTX_USER_CTYP unique (USER_PID, CONTEXT_TYPE);
create index IDX_USROA2CLNTAPRVDSCP_CLNTUSR on CDR_USER_DETAILS_OA2CLNAPRVSCP (CLIENT_PID, USER_PID);
create index IDX_USER_OA2CLN_TOS_USRCLNT on CDR_USER_OA2CLN_TOS (USER_PID, CLIENT_PID);
create index IDX_USER_PERM_PERM_USER on CDR_USER_PERM (PERMISSION_TYPE, USER_PID);
create index IDX_USER_PSN_USR on CDR_USER_PSN (USER_PID, VERSION, CREATED_DATE, REVOKED_DATE);

    alter table if exists CDR_USER_TFA_KEY 
       add constraint IDX_USERTFAKEY_USER unique (USER_PID);
create index IDX_XACTLOG_INITTS on CDR_XACT_LOG (INITIAL_TIMESTAMP);
create index IDX_XACTID on CDR_XACT_LOG (SRC_XACT_ID);

    alter table if exists CDR_XACT_LOG 
       add constraint IDX_GUID unique (SRC_GUID);
create index IDX_XACTLOGSTEP_LOGID on CDR_XACT_LOG_STEP (LOG_PID);

    alter table if exists AG_ADDRESS 
       add constraint FK_AG_ADDRESS_AG_PROFILE 
       foreign key (AG_PROFILE_PID) 
       references AG_PROFILE;

    alter table if exists AG_APP_REQUEST 
       add constraint FK_AG_APP_REQUEST_MODULE 
       foreign key (MODULE_PID) 
       references CDR_MODULE;

    alter table if exists AG_APPL 
       add constraint FK_AG_APPL_JWK_SET 
       foreign key (JWK_SET_PID) 
       references AG_DATA;

    alter table if exists AG_APPL 
       add constraint FK_AG_APPL_AG_ATTESTATION 
       foreign key (ATTESTATION_PID) 
       references AG_ATTESTATION;

    alter table if exists AG_APPL 
       add constraint FK_AG_APPL_ICON 
       foreign key (ICON_PID) 
       references AG_DATA;

    alter table if exists AG_APPL 
       add constraint FK_AG_APPL_MODULE 
       foreign key (MODULE_PID) 
       references CDR_MODULE;

    alter table if exists AG_APPL 
       add constraint FK_AG_APPL_OAUTH2_CLIENT 
       foreign key (OAUTH2_CLIENT_PID) 
       references CDR_OAUTH2_CLIENT_DETAILS;

    alter table if exists AG_APPL 
       add constraint FK_AG_APPL_PARENT_PID 
       foreign key (PARENT_PID) 
       references AG_APPL;

    alter table if exists AG_APPL 
       add constraint FK_AG_APPL_AG_PROFILE 
       foreign key (PROFILE_PID) 
       references AG_PROFILE;

    alter table if exists AG_APPL 
       add constraint FK_AG_APPL_USER 
       foreign key (USER_PID) 
       references CDR_USER_DETAILS;

    alter table if exists AG_ATTESTATION 
       add constraint FK_AG_ATTESTATION_HTML 
       foreign key (HTML_PID) 
       references AG_DATA;

    alter table if exists AG_ATTESTATION 
       add constraint FK_AG_ATTESTATION_MODULE 
       foreign key (MODULE_PID) 
       references CDR_MODULE;

    alter table if exists AG_ATTESTATION 
       add constraint FK_AG_ATTESTATION_PLAIN_HTML 
       foreign key (PLAIN_HTML_PID) 
       references AG_DATA;

    alter table if exists AG_CLASS 
       add constraint FK_AG_CLASS_AG_APPL 
       foreign key (AG_APPL_PID) 
       references AG_APPL;

    alter table if exists AG_COUNTRY 
       add constraint FK_AG_COUNTRY_AG_APPL 
       foreign key (AG_APPL_PID) 
       references AG_APPL;

    alter table if exists AG_DOCUMENT 
       add constraint FK_AG_DOCUMENT_DATA_BLOB 
       foreign key (DOCUMENT_PID) 
       references AG_DATA;

    alter table if exists AG_DOCUMENT 
       add constraint FK_AG_DOCUMENT_AG_APPL 
       foreign key (AG_APPL_PID) 
       references AG_APPL;

    alter table if exists AG_DRAFT 
       add constraint FK_AG_DRAFT_DATA_BLOB 
       foreign key (DRAFT_PID) 
       references AG_DATA;

    alter table if exists AG_DRAFT 
       add constraint FK_AG_DRAFT_MODULE 
       foreign key (MODULE_PID) 
       references CDR_MODULE;

    alter table if exists AG_DRAFT 
       add constraint FK_AG_DRAFT_SOURCE_APP_PID 
       foreign key (SOURCE_APP_PID) 
       references AG_APPL;

    alter table if exists AG_DRAFT 
       add constraint FK_AG_DRAFT_USER 
       foreign key (USER_PID) 
       references CDR_USER_DETAILS;

    alter table if exists AG_MEMBER_PHONE 
       add constraint FK_AG_MEMBER_PHONE_APP_REQUEST 
       foreign key (AG_APP_REQUEST_PID) 
       references AG_APP_REQUEST;

    alter table if exists AG_METADATA 
       add constraint FK_AG_METADATA_AG_APPL 
       foreign key (AG_APPL_PID) 
       references AG_APPL;

    alter table if exists AG_NOTE 
       add constraint FK_AG_NOTE_AG_APPL 
       foreign key (AG_APPL_PID) 
       references AG_APPL;

    alter table if exists AG_OAUTH_REDIRECT 
       add constraint FK_AG_OAUTH_REDIRECT_AG_APPL 
       foreign key (AG_APPL_PID) 
       references AG_APPL;

    alter table if exists AG_PHONE 
       add constraint FK_AG_PHONE_AG_PROFILE 
       foreign key (AG_PROFILE_PID) 
       references AG_PROFILE;

    alter table if exists AG_PREREQUISITE 
       add constraint FK_AG_PREREQUISITE_AG_APPL 
       foreign key (AG_APPL_PID) 
       references AG_APPL;

    alter table if exists AG_PROFILE 
       add constraint FK_AG_PROFILE_MODULE 
       foreign key (MODULE_PID) 
       references CDR_MODULE;

    alter table if exists AG_PROFILE 
       add constraint FK_AG_PROFILE_USER 
       foreign key (USER_PID) 
       references CDR_USER_DETAILS;

    alter table if exists AG_SANDBOX_CLIENT 
       add constraint FK_AG_SANDBOX_CLIENT_MODULE 
       foreign key (MODULE_PID) 
       references CDR_MODULE;

    alter table if exists AG_SANDBOX_CLIENT 
       add constraint FK_AG_CLIENT_OAUTH2_CLIENT 
       foreign key (OAUTH2_CLIENT_PID) 
       references CDR_OAUTH2_CLIENT_DETAILS;

    alter table if exists AG_SANDBOX_CLIENT 
       add constraint FK_AG_SANDBOX_CLIENT_USER 
       foreign key (USER_PID) 
       references CDR_USER_DETAILS;

    alter table if exists AG_SCOPE 
       add constraint FK_AG_SCOPE_AG_APPL 
       foreign key (AG_APPL_PID) 
       references AG_APPL;

    alter table if exists AG_SCREENSHOT 
       add constraint FK_AG_SCREENSHOT_DATA_BLOB 
       foreign key (SCREENSHOT_PID) 
       references AG_DATA;

    alter table if exists AG_SCREENSHOT 
       add constraint FK_AG_SCREENSHOT_AG_APPL 
       foreign key (AG_APPL_PID) 
       references AG_APPL;

    alter table if exists CDR_AUDIT_EVT 
       add constraint FK_AUDITEVT_CLIENT 
       foreign key (CLIENT_PID) 
       references CDR_OAUTH2_CLIENT_DETAILS;

    alter table if exists CDR_AUDIT_EVT 
       add constraint FK_AUDITEVT_ENDPOINTMODULE 
       foreign key (ENDPOINT_MODULE_PID) 
       references CDR_MODULE;

    alter table if exists CDR_AUDIT_EVT 
       add constraint FK_AUDITEVT_TYPE 
       foreign key (TYPE_PID) 
       references CDR_AUDIT_EVT_TYPE;

    alter table if exists CDR_AUDIT_EVT 
       add constraint FK_AUDITEVT_USER 
       foreign key (USER_PID) 
       references CDR_USER_DETAILS;

    alter table if exists CDR_AUDIT_EVT_HEADER 
       add constraint FK_AUDITEVT_HEADER_EVT 
       foreign key (EVENT_PID) 
       references CDR_AUDIT_EVT;

    alter table if exists CDR_AUDIT_EVT_TARGET_MODULE 
       add constraint FK_AUDITEVT_TARGETMODULE_EVT 
       foreign key (EVENT_PID) 
       references CDR_AUDIT_EVT;

    alter table if exists CDR_AUDIT_EVT_TARGET_MODULE 
       add constraint FK_AUDITEVT_TARGETMOD_MODULE 
       foreign key (MODULE_PID) 
       references CDR_MODULE;

    alter table if exists CDR_AUDIT_EVT_TARGET_RES 
       add constraint FK_AUDITEVT_TARGETRES_EVT 
       foreign key (EVENT_PID) 
       references CDR_AUDIT_EVT;

    alter table if exists CDR_AUDIT_EVT_TARGET_RES 
       add constraint FK_AUDITEVT_TARGETRES_PERSMOD 
       foreign key (PERS_MODULE_PID) 
       references CDR_MODULE;

    alter table if exists CDR_AUDIT_EVT_TARGET_USER 
       add constraint FK_AUDITEVT_TARGETUSER_EVT 
       foreign key (EVENT_PID) 
       references CDR_AUDIT_EVT;

    alter table if exists CDR_AUDIT_EVT_TARGET_USER 
       add constraint FK_AUDITEVT_TARGETUSER_USER 
       foreign key (USER_PID) 
       references CDR_USER_DETAILS;

    alter table if exists CDR_CACHE_SYNC 
       add constraint FK_CACHE_MODULE 
       foreign key (MODULE_PID) 
       references CDR_MODULE;

    alter table if exists CDR_CDA_TEMPLATE 
       add constraint FK_CDA_MODULE 
       foreign key (MODULE_PID) 
       references CDR_MODULE;

    alter table if exists CDR_CDA_TEMPLATE_PARAM 
       add constraint FK_CDATEMPLATE_PID 
       foreign key (TEMPLATE_PID) 
       references CDR_CDA_TEMPLATE;

    alter table if exists CDR_FHIR_SEARCHPARAM 
       add constraint FK_FHIRSEARCHPARAM_MODULE 
       foreign key (MODULE_PID) 
       references CDR_MODULE;

    alter table if exists CDR_HTTP_SESSION 
       add constraint FK_HTTPSESSION_MODULE 
       foreign key (MODULE_PID) 
       references CDR_MODULE;

    alter table if exists CDR_METRIC_GAUGE 
       add constraint FK_METGAUGE_MODP 
       foreign key (MODPROC_PID) 
       references CDR_MODULE_PROCESS;

    alter table if exists CDR_METRIC_HC 
       add constraint FK_HC_MODP 
       foreign key (MODPROC_PID) 
       references CDR_MODULE_PROCESS;

    alter table if exists CDR_METRIC_HEALTH 
       add constraint FK_HEALTH_MODP 
       foreign key (MODPROC_PID) 
       references CDR_MODULE_PROCESS;

    alter table if exists CDR_METRIC_TIMER 
       add constraint FK_METTIMER_MODP 
       foreign key (MODPROC_PID) 
       references CDR_MODULE_PROCESS;

    alter table if exists CDR_MODULE 
       add constraint FK_MODULE_NODE 
       foreign key (NODE_PID) 
       references CDR_NODE;

    alter table if exists CDR_MODULE 
       add constraint FK_MODULE_PROTOTYPE 
       foreign key (PROTOTYPE_PID) 
       references CDR_MODULE_PROTOTYPE;

    alter table if exists CDR_MODULE_CFG_PROP 
       add constraint FK_MODULECFGPROP_MODULE 
       foreign key (MODULE_PID) 
       references CDR_MODULE;

    alter table if exists CDR_MODULE_CFG_PROP 
       add constraint FK_MODULECFGPROP_PROTOTYPE 
       foreign key (PROTOTYPE_PID) 
       references CDR_MODULE_PROT_CFG;

    alter table if exists CDR_MODULE_DEP 
       add constraint FK_MODULEDEP_MODULE 
       foreign key (MODULE_PID) 
       references CDR_MODULE;

    alter table if exists CDR_MODULE_DEP 
       add constraint FK_MODULEDEP_PROTOTYPE 
       foreign key (PROTOTYPE_PID) 
       references CDR_MODULE_PROT_DEP;

    alter table if exists CDR_MODULE_DEP 
       add constraint FK_MODULEDEP_TARGET 
       foreign key (TARGET_PID) 
       references CDR_MODULE;

    alter table if exists CDR_MODULE_PROCESS 
       add constraint FK_MODPROC_MODULE 
       foreign key (MODULE_PID) 
       references CDR_MODULE;

    alter table if exists CDR_MODULE_PROCESS 
       add constraint FK_MODPROC_NODEPROC 
       foreign key (NODEPROC_PID) 
       references CDR_NODE_PROCESS;

    alter table if exists CDR_MODULE_PROT_CFG 
       add constraint FK_CDR_MODULEPROTCFG_PROT 
       foreign key (PROTOTYPE_PID) 
       references CDR_MODULE_PROTOTYPE;

    alter table if exists CDR_MODULE_PROT_DEP 
       add constraint FK_MODULE_PROT_DEP_PROT 
       foreign key (PROTOTYPE_PID) 
       references CDR_MODULE_PROTOTYPE;

    alter table if exists CDR_MODULE_PROT_SUPPLIES 
       add constraint FK_MODULE_PROT_SUPPLIES_PROT 
       foreign key (PROTOTYPE_PID) 
       references CDR_MODULE_PROTOTYPE;

    alter table if exists CDR_MODULE_STATE 
       add constraint FK_MODSTAT_MODULE 
       foreign key (MODULE_PID) 
       references CDR_MODULE;

    alter table if exists CDR_NODE_PROCESS 
       add constraint FK_NODEPROCESS_NODE 
       foreign key (NODE_PID) 
       references CDR_NODE;

    alter table if exists CDR_OA2_CLI_DET_AUTH_GRNT_TYP 
       add constraint FK_OA2CLIDETAUTHGRNTTY_CLI 
       foreign key (CLIENT_PID) 
       references CDR_OAUTH2_CLIENT_DETAILS;

    alter table if exists CDR_OA2_CLI_DET_AUTOAP_SCOPE 
       add constraint FK_OA2CLIDETAUTOAPPSCOP_CLI 
       foreign key (CLIENT_PID) 
       references CDR_OAUTH2_CLIENT_DETAILS;

    alter table if exists CDR_OA2_CLI_DET_AUTOGRNT_SCOPE 
       add constraint FK_OA2CLIDETAUTOGRNTPSCOP_CLI 
       foreign key (CLIENT_PID) 
       references CDR_OAUTH2_CLIENT_DETAILS;

    alter table if exists CDR_OA2_CLI_DET_CLISEC 
       add constraint FK_OA2CLIDETCLISEC_CLI 
       foreign key (CLIENT_PID) 
       references CDR_OAUTH2_CLIENT_DETAILS;

    alter table if exists CDR_OA2_CLI_DET_REG_REDIR_URI 
       add constraint FK_OA2CLIDETREGREDURI_CLI 
       foreign key (CLIENT_PID) 
       references CDR_OAUTH2_CLIENT_DETAILS;

    alter table if exists CDR_OA2_SERVER 
       add constraint FK_OA2SERVER_MODULE 
       foreign key (MODULE_PID) 
       references CDR_MODULE;

    alter table if exists CDR_OAUTH2_ATOKEN 
       add constraint FK_OA2ATOKEN_CLIENT 
       foreign key (CLIENT_PID) 
       references CDR_OAUTH2_CLIENT_DETAILS;

    alter table if exists CDR_OAUTH2_ATOKEN 
       add constraint FK_OA2ATOKEN_MODULE 
       foreign key (MODULE_PID) 
       references CDR_MODULE;

    alter table if exists CDR_OAUTH2_ATOKEN 
       add constraint FK_OA2ATOKEN_REF_TOK 
       foreign key (REFRESH_TOKEN_PID) 
       references CDR_OAUTH2_REFRESH_TOKEN;

    alter table if exists CDR_OAUTH2_ATOKEN 
       add constraint FK_OA2ATOKEN_USER 
       foreign key (USER_PID) 
       references CDR_USER_DETAILS;

    alter table if exists CDR_OAUTH2_ATOKEN_ADNLINFO 
       add constraint FK_OA2ATOKADNLINFO_TOKEN 
       foreign key (TOKEN_PID) 
       references CDR_OAUTH2_ATOKEN;

    alter table if exists CDR_OAUTH2_ATOKEN_GRNTDA 
       add constraint FK_OA2ATOKGRNTDA_TOKEN 
       foreign key (TOKEN_PID) 
       references CDR_OAUTH2_ATOKEN;

    alter table if exists CDR_OAUTH2_ATOKEN_SCOPE 
       add constraint FK_OA2ATOKSCP_TOKEN 
       foreign key (TOKEN_PID) 
       references CDR_OAUTH2_ATOKEN;

    alter table if exists CDR_OAUTH2_ATOKEN_UDATA 
       add constraint FK_OA2ATOKUD_TOKEN 
       foreign key (ATOKEN_PID) 
       references CDR_OAUTH2_ATOKEN;

    alter table if exists CDR_OAUTH2_CLIENT_DET_SCOPE 
       add constraint FK_OA2CLIDETSCOP_CLIENT 
       foreign key (CLIENT_PID) 
       references CDR_OAUTH2_CLIENT_DETAILS;

    alter table if exists CDR_OAUTH2_CLIENT_DETAILS 
       add constraint FK_OA2CLIENTDETAILS_MODULE 
       foreign key (MODULE_PID) 
       references CDR_MODULE;

    alter table if exists CDR_OAUTH2_CLIENT_PERM 
       add constraint FK_OA2CLIENTPERM_CLIENT 
       foreign key (CLIENT_PID) 
       references CDR_OAUTH2_CLIENT_DETAILS;

    alter table if exists CDR_OAUTH2_REFRESH_TOKEN 
       add constraint FK_OA2REFTOKEN_CLIENT 
       foreign key (CLIENT_PID) 
       references CDR_OAUTH2_CLIENT_DETAILS;

    alter table if exists CDR_OAUTH2_REFRESH_TOKEN 
       add constraint FK_OA2REFTOKEN_USER 
       foreign key (USER_PID) 
       references CDR_USER_DETAILS;

    alter table if exists CDR_OAUTH2_RTOKEN_ADNLINFO 
       add constraint FK_OA2RTOKADNLINFO_TOKEN 
       foreign key (TOKEN_PID) 
       references CDR_OAUTH2_REFRESH_TOKEN;

    alter table if exists CDR_OAUTH2_RTOKEN_GRNTDA 
       add constraint FK_OA2RTOKGRNTDA_TOKEN 
       foreign key (TOKEN_PID) 
       references CDR_OAUTH2_REFRESH_TOKEN;

    alter table if exists CDR_OAUTH2_RTOKEN_LNCHRESID 
       add constraint FK_OA2RTOKLNCHRESID_TOKEN 
       foreign key (TOKEN_PID) 
       references CDR_OAUTH2_REFRESH_TOKEN;

    alter table if exists CDR_OAUTH2_RTOKEN_REQPRM 
       add constraint FK_OA2RTOKREQPRM_TOKEN 
       foreign key (TOKEN_PID) 
       references CDR_OAUTH2_REFRESH_TOKEN;

    alter table if exists CDR_OAUTH2_RTOKEN_SCOPE 
       add constraint FK_OA2RTOKSCP_TOKEN 
       foreign key (TOKEN_PID) 
       references CDR_OAUTH2_REFRESH_TOKEN;

    alter table if exists CDR_OAUTH2_RTOKEN_UDATA 
       add constraint FK_OA2RTOKUD_TOKEN 
       foreign key (TOKEN_PID) 
       references CDR_OAUTH2_REFRESH_TOKEN;

    alter table if exists CDR_USER_DETAILS 
       add constraint FK_USERDETAILS_MODULE 
       foreign key (MODULE_PID) 
       references CDR_MODULE;

    alter table if exists CDR_USER_DETAILS_DEF_LNCH_CTX 
       add constraint FK_USERDEFLNCHCTX_USER 
       foreign key (USER_PID) 
       references CDR_USER_DETAILS;

    alter table if exists CDR_USER_DETAILS_OA2CLNAPRVSCP 
       add constraint FK_USROA2CLNTAPRVDSCP_CLIENT 
       foreign key (CLIENT_PID) 
       references CDR_OAUTH2_CLIENT_DETAILS;

    alter table if exists CDR_USER_DETAILS_OA2CLNAPRVSCP 
       add constraint FK_USROA2CLNTAPRVDSCP_USER 
       foreign key (USER_PID) 
       references CDR_USER_DETAILS;

    alter table if exists CDR_USER_OA2CLN_TOS 
       add constraint FK_USER_OA2CLN_TOS_CLIENT 
       foreign key (CLIENT_PID) 
       references CDR_OAUTH2_CLIENT_DETAILS;

    alter table if exists CDR_USER_OA2CLN_TOS 
       add constraint FK_USER_OA2CLN_TOS_USER 
       foreign key (USER_PID) 
       references CDR_USER_DETAILS;

    alter table if exists CDR_USER_PERM 
       add constraint FK_USERPERM_USER 
       foreign key (USER_PID) 
       references CDR_USER_DETAILS;

    alter table if exists CDR_USER_PSN 
       add constraint FK_USER_PSN_USER 
       foreign key (USER_PID) 
       references CDR_USER_DETAILS;

    alter table if exists CDR_USER_TFA_KEY 
       add constraint FK_USERTFAKEY_USER 
       foreign key (USER_PID) 
       references CDR_USER_DETAILS;

    alter table if exists CDR_XACT_LOG 
       add constraint FK_TRANSIT_CLIENT 
       foreign key (CLIENT_PID) 
       references CDR_OAUTH2_CLIENT_DETAILS;

    alter table if exists CDR_XACT_LOG 
       add constraint FK_XACTLOGSTEP_MODULE 
       foreign key (CDR_ENDPOINT_MODULE_PID) 
       references CDR_MODULE;

    alter table if exists CDR_XACT_LOG 
       add constraint FK_TRANSIT_EVENT 
       foreign key (USER_PID) 
       references CDR_USER_DETAILS;

    alter table if exists CDR_XACT_LOG_STEP 
       add constraint FK_XACTLOGSTEP_XACTLOG 
       foreign key (LOG_PID) 
       references CDR_XACT_LOG;

34.2.5Setting up a FHIR Storage (Relational) Database

 

To initialize a new PostgreSQL database for FHIR Storage (Relational), initialize a database with the following SQL:

create sequence SEQ_BLKEXCOL_PID start 1 increment 50;
create sequence SEQ_BLKEXCOLFILE_PID start 1 increment 50;
create sequence SEQ_BLKEXJOB_PID start 1 increment 50;
create sequence SEQ_BLKIMJOB_PID start 1 increment 50;
create sequence SEQ_BLKIMJOBFILE_PID start 1 increment 50;
create sequence SEQ_CDH_LB_REF start 1 increment 50;
create sequence SEQ_CDH_LB_SUB_GROUP start 1 increment 50;
create sequence SEQ_CDH_LB_WL start 1 increment 50;
create sequence SEQ_CDH_LB_WL_SUBS start 1 increment 50;
create sequence SEQ_CNCPT_MAP_GRP_ELM_TGT_PID start 1 increment 50;
create sequence SEQ_CODESYSTEM_PID start 1 increment 50;
create sequence SEQ_CODESYSTEMVER_PID start 1 increment 50;
create sequence SEQ_CONCEPT_DESIG_PID start 1 increment 50;
create sequence SEQ_CONCEPT_MAP_GROUP_PID start 1 increment 50;
create sequence SEQ_CONCEPT_MAP_GRP_ELM_PID start 1 increment 50;
create sequence SEQ_CONCEPT_MAP_PID start 1 increment 50;
create sequence SEQ_CONCEPT_PC_PID start 1 increment 50;
create sequence SEQ_CONCEPT_PID start 1 increment 50;
create sequence SEQ_CONCEPT_PROP_PID start 1 increment 50;
create sequence SEQ_EMPI_LINK_ID start 1 increment 50;
create sequence SEQ_FORCEDID_ID start 1 increment 50;
create sequence SEQ_HISTORYTAG_ID start 1 increment 50;
create sequence SEQ_IDXCMBTOKNU_ID start 1 increment 50;
create sequence SEQ_IDXCMPSTRUNIQ_ID start 1 increment 50;
create sequence SEQ_NPM_PACK start 1 increment 50;
create sequence SEQ_NPM_PACKVER start 1 increment 50;
create sequence SEQ_NPM_PACKVERRES start 1 increment 50;
create sequence SEQ_RES_REINDEX_JOB start 1 increment 50;
create sequence SEQ_RESLINK_ID start 1 increment 50;
create sequence SEQ_RESOURCE_HISTORY_ID start 1 increment 50;
create sequence SEQ_RESOURCE_ID start 1 increment 50;
create sequence SEQ_RESPARMPRESENT_ID start 1 increment 50;
create sequence SEQ_RESTAG_ID start 1 increment 50;
create sequence SEQ_SEARCH start 1 increment 50;
create sequence SEQ_SEARCH_INC start 1 increment 50;
create sequence SEQ_SEARCH_RES start 1 increment 50;
create sequence SEQ_SPIDX_COORDS start 1 increment 50;
create sequence SEQ_SPIDX_DATE start 1 increment 50;
create sequence SEQ_SPIDX_NUMBER start 1 increment 50;
create sequence SEQ_SPIDX_QUANTITY start 1 increment 50;
create sequence SEQ_SPIDX_QUANTITY_NRML start 1 increment 50;
create sequence SEQ_SPIDX_STRING start 1 increment 50;
create sequence SEQ_SPIDX_TOKEN start 1 increment 50;
create sequence SEQ_SPIDX_URI start 1 increment 50;
create sequence SEQ_SUBSCRIPTION_ID start 1 increment 50;
create sequence SEQ_TAGDEF_ID start 1 increment 50;
create sequence SEQ_VALUESET_C_DSGNTN_PID start 1 increment 50;
create sequence SEQ_VALUESET_CONCEPT_PID start 1 increment 50;
create sequence SEQ_VALUESET_PID start 1 increment 50;

    create table BT2_JOB_INSTANCE (
       ID varchar(100) not null,
        JOB_CANCELLED boolean not null,
        CMB_RECS_PROCESSED int4,
        CMB_RECS_PER_SEC float8,
        CREATE_TIME timestamp not null,
        CUR_GATED_STEP_ID varchar(100),
        DEFINITION_ID varchar(100) not null,
        DEFINITION_VER int4 not null,
        END_TIME timestamp,
        ERROR_COUNT int4,
        ERROR_MSG varchar(500),
        EST_REMAINING varchar(100),
        FAST_TRACKING boolean,
        PARAMS_JSON varchar(2000),
        PARAMS_JSON_LOB oid,
        PROGRESS_PCT float8,
        REPORT oid,
        START_TIME timestamp,
        STAT varchar(20) not null,
        TOT_ELAPSED_MILLIS int4,
        WORK_CHUNKS_PURGED boolean not null,
        primary key (ID)
    );

    create table BT2_WORK_CHUNK (
       ID varchar(100) not null,
        CREATE_TIME timestamp not null,
        END_TIME timestamp,
        ERROR_COUNT int4 not null,
        ERROR_MSG varchar(500),
        INSTANCE_ID varchar(100) not null,
        DEFINITION_ID varchar(100) not null,
        DEFINITION_VER int4 not null,
        RECORDS_PROCESSED int4,
        SEQ int4 not null,
        CHUNK_DATA oid,
        START_TIME timestamp,
        STAT varchar(20) not null,
        TGT_STEP_ID varchar(100) not null,
        primary key (ID)
    );

    create table CDH_LB_REF (
       PID int8 not null,
        EXPIRES timestamp,
        LB_RES_ID int8 not null,
        ORDER_DATE timestamp,
        ROOT_RES_ID int8 not null,
        RULE_SYSTEM varchar(200) not null,
        RULE_VALUE varchar(200) not null,
        SUBS_RES_ID int8 not null,
        TRACK_PARAM varchar(200),
        TRACK_SUBPARAM varchar(200),
        primary key (PID)
    );

    create table CDH_LB_SUB_GROUP (
       PID int8 not null,
        SUBS_GROUP varchar(200) not null,
        SUBS_ID varchar(200) not null,
        SUBS_RES_ID int8 not null,
        primary key (PID)
    );

    create table CDH_LB_WL (
       PID int8 not null,
        SUBSCRIBER_TYPE varchar(200) not null,
        WATCHLIST_SYSTEM varchar(200) not null,
        WATCHLIST_VALUE varchar(200) not null,
        primary key (PID)
    );

    create table CDH_LB_WL_SUBS (
       PID int8 not null,
        SEED_STATUS int4 not null,
        SUBS_ID varchar(200) not null,
        SUBS_RES_ID int8 not null,
        WATCHLIST_ID int8 not null,
        primary key (PID)
    );

    create table HFJ_BINARY_STORAGE_BLOB (
       BLOB_ID varchar(200) not null,
        BLOB_DATA oid not null,
        CONTENT_TYPE varchar(100) not null,
        BLOB_HASH varchar(128),
        PUBLISHED_DATE timestamp not null,
        RESOURCE_ID varchar(100) not null,
        BLOB_SIZE int4,
        primary key (BLOB_ID)
    );

    create table HFJ_BLK_EXPORT_COLFILE (
       PID int8 not null,
        RES_ID varchar(100) not null,
        COLLECTION_PID int8 not null,
        primary key (PID)
    );

    create table HFJ_BLK_EXPORT_COLLECTION (
       PID int8 not null,
        TYPE_FILTER varchar(1000),
        RES_TYPE varchar(40) not null,
        OPTLOCK int4 not null,
        JOB_PID int8 not null,
        primary key (PID)
    );

    create table HFJ_BLK_EXPORT_JOB (
       PID int8 not null,
        CREATED_TIME timestamp not null,
        EXP_TIME timestamp,
        JOB_ID varchar(36) not null,
        REQUEST varchar(1024) not null,
        EXP_SINCE timestamp,
        JOB_STATUS varchar(10) not null,
        STATUS_MESSAGE varchar(500),
        STATUS_TIME timestamp not null,
        OPTLOCK int4 not null,
        primary key (PID)
    );

    create table HFJ_BLK_IMPORT_JOB (
       PID int8 not null,
        BATCH_SIZE int4 not null,
        FILE_COUNT int4 not null,
        JOB_DESC varchar(500),
        JOB_ID varchar(36) not null,
        ROW_PROCESSING_MODE varchar(20) not null,
        JOB_STATUS varchar(10) not null,
        STATUS_MESSAGE varchar(500),
        STATUS_TIME timestamp not null,
        OPTLOCK int4 not null,
        primary key (PID)
    );

    create table HFJ_BLK_IMPORT_JOBFILE (
       PID int8 not null,
        JOB_CONTENTS oid not null,
        FILE_DESCRIPTION varchar(500),
        FILE_SEQ int4 not null,
        TENANT_NAME varchar(200),
        JOB_PID int8 not null,
        primary key (PID)
    );

    create table HFJ_FORCED_ID (
       PID int8 not null,
        PARTITION_DATE date,
        PARTITION_ID int4,
        FORCED_ID varchar(100) not null,
        RESOURCE_PID int8 not null,
        RESOURCE_TYPE varchar(100) default '',
        primary key (PID)
    );

    create table HFJ_HISTORY_TAG (
       PID int8 not null,
        PARTITION_DATE date,
        PARTITION_ID int4,
        TAG_ID int8,
        RES_VER_PID int8 not null,
        RES_ID int8 not null,
        RES_TYPE varchar(40) not null,
        primary key (PID)
    );

    create table HFJ_IDX_CMB_TOK_NU (
       PID int8 not null,
        PARTITION_DATE date,
        PARTITION_ID int4,
        HASH_COMPLETE int8 not null,
        IDX_STRING varchar(500) not null,
        RES_ID int8,
        primary key (PID)
    );

    create table HFJ_IDX_CMP_STRING_UNIQ (
       PID int8 not null,
        PARTITION_DATE date,
        PARTITION_ID int4,
        IDX_STRING varchar(500) not null,
        RES_ID int8,
        primary key (PID)
    );

    create table HFJ_PARTITION (
       PART_ID int4 not null,
        PART_DESC varchar(200),
        PART_NAME varchar(200) not null,
        primary key (PART_ID)
    );

    create table HFJ_RES_LINK (
       PID int8 not null,
        PARTITION_DATE date,
        PARTITION_ID int4,
        SRC_PATH varchar(500) not null,
        SRC_RESOURCE_ID int8 not null,
        SOURCE_RESOURCE_TYPE varchar(40) not null,
        TARGET_RESOURCE_ID int8,
        TARGET_RESOURCE_TYPE varchar(40) not null,
        TARGET_RESOURCE_URL varchar(200),
        TARGET_RESOURCE_VERSION int8,
        SP_UPDATED timestamp,
        primary key (PID)
    );

    create table HFJ_RES_PARAM_PRESENT (
       PID int8 not null,
        PARTITION_DATE date,
        PARTITION_ID int4,
        HASH_PRESENCE int8,
        SP_PRESENT boolean not null,
        RES_ID int8 not null,
        primary key (PID)
    );

    create table HFJ_RES_REINDEX_JOB (
       PID int8 not null,
        JOB_DELETED boolean not null,
        REINDEX_COUNT int4,
        RES_TYPE varchar(100),
        SUSPENDED_UNTIL timestamp,
        UPDATE_THRESHOLD_HIGH timestamp not null,
        UPDATE_THRESHOLD_LOW timestamp,
        primary key (PID)
    );

    create table HFJ_RES_TAG (
       PID int8 not null,
        PARTITION_DATE date,
        PARTITION_ID int4,
        TAG_ID int8,
        RES_ID int8,
        RES_TYPE varchar(40) not null,
        primary key (PID)
    );

    create table HFJ_RES_VER (
       PID int8 not null,
        PARTITION_DATE date,
        PARTITION_ID int4,
        RES_DELETED_AT timestamp,
        RES_VERSION varchar(7),
        HAS_TAGS boolean not null,
        RES_PUBLISHED timestamp not null,
        RES_UPDATED timestamp not null,
        RES_ENCODING varchar(5) not null,
        RES_TEXT oid,
        RES_ID int8 not null,
        RES_TEXT_VC varchar(4000),
        RES_TYPE varchar(40) not null,
        RES_VER int8 not null,
        primary key (PID)
    );

    create table HFJ_RES_VER_PROV (
       RES_VER_PID int8 not null,
        PARTITION_DATE date,
        PARTITION_ID int4,
        REQUEST_ID varchar(16),
        SOURCE_URI varchar(100),
        RES_PID int8 not null,
        primary key (RES_VER_PID)
    );

    create table HFJ_RESOURCE (
       RES_ID int8 not null,
        PARTITION_DATE date,
        PARTITION_ID int4,
        RES_DELETED_AT timestamp,
        RES_VERSION varchar(7),
        HAS_TAGS boolean not null,
        RES_PUBLISHED timestamp not null,
        RES_UPDATED timestamp not null,
        SP_HAS_LINKS boolean,
        HASH_SHA256 varchar(64),
        SP_INDEX_STATUS int8,
        RES_LANGUAGE varchar(20),
        SP_CMPSTR_UNIQ_PRESENT boolean,
        SP_CMPTOKS_PRESENT boolean,
        SP_COORDS_PRESENT boolean,
        SP_DATE_PRESENT boolean,
        SP_NUMBER_PRESENT boolean,
        SP_QUANTITY_NRML_PRESENT boolean,
        SP_QUANTITY_PRESENT boolean,
        SP_STRING_PRESENT boolean,
        SP_TOKEN_PRESENT boolean,
        SP_URI_PRESENT boolean,
        RES_TYPE varchar(40) not null,
        RES_VER int8,
        primary key (RES_ID)
    );

    create table HFJ_SEARCH (
       PID int8 not null,
        CREATED timestamp not null,
        SEARCH_DELETED boolean,
        EXPIRY_OR_NULL timestamp,
        FAILURE_CODE int4,
        FAILURE_MESSAGE varchar(500),
        LAST_UPDATED_HIGH timestamp,
        LAST_UPDATED_LOW timestamp,
        NUM_BLOCKED int4,
        NUM_FOUND int4 not null,
        PREFERRED_PAGE_SIZE int4,
        RESOURCE_ID int8,
        RESOURCE_TYPE varchar(200),
        SEARCH_PARAM_MAP oid,
        SEARCH_QUERY_STRING oid,
        SEARCH_QUERY_STRING_HASH int4,
        SEARCH_TYPE int4 not null,
        SEARCH_STATUS varchar(10) not null,
        TOTAL_COUNT int4,
        SEARCH_UUID varchar(36) not null,
        OPTLOCK_VERSION int4,
        primary key (PID)
    );

    create table HFJ_SEARCH_INCLUDE (
       PID int8 not null,
        SEARCH_INCLUDE varchar(200) not null,
        INC_RECURSE boolean not null,
        REVINCLUDE boolean not null,
        SEARCH_PID int8 not null,
        primary key (PID)
    );

    create table HFJ_SEARCH_RESULT (
       PID int8 not null,
        SEARCH_ORDER int4 not null,
        RESOURCE_PID int8 not null,
        SEARCH_PID int8 not null,
        primary key (PID)
    );

    create table HFJ_SPIDX_COORDS (
       SP_ID int8 not null,
        PARTITION_DATE date,
        PARTITION_ID int4,
        SP_MISSING boolean not null,
        SP_NAME varchar(100) not null,
        RES_ID int8 not null,
        RES_TYPE varchar(100) not null,
        SP_UPDATED timestamp,
        HASH_IDENTITY int8,
        SP_LATITUDE float8,
        SP_LONGITUDE float8,
        primary key (SP_ID)
    );

    create table HFJ_SPIDX_DATE (
       SP_ID int8 not null,
        PARTITION_DATE date,
        PARTITION_ID int4,
        SP_MISSING boolean not null,
        SP_NAME varchar(100) not null,
        RES_ID int8 not null,
        RES_TYPE varchar(100) not null,
        SP_UPDATED timestamp,
        HASH_IDENTITY int8,
        SP_VALUE_HIGH timestamp,
        SP_VALUE_HIGH_DATE_ORDINAL int4,
        SP_VALUE_LOW timestamp,
        SP_VALUE_LOW_DATE_ORDINAL int4,
        primary key (SP_ID)
    );

    create table HFJ_SPIDX_NUMBER (
       SP_ID int8 not null,
        PARTITION_DATE date,
        PARTITION_ID int4,
        SP_MISSING boolean not null,
        SP_NAME varchar(100) not null,
        RES_ID int8 not null,
        RES_TYPE varchar(100) not null,
        SP_UPDATED timestamp,
        HASH_IDENTITY int8,
        SP_VALUE numeric(19, 2),
        primary key (SP_ID)
    );

    create table HFJ_SPIDX_QUANTITY (
       SP_ID int8 not null,
        PARTITION_DATE date,
        PARTITION_ID int4,
        SP_MISSING boolean not null,
        SP_NAME varchar(100) not null,
        RES_ID int8 not null,
        RES_TYPE varchar(100) not null,
        SP_UPDATED timestamp,
        HASH_IDENTITY int8,
        HASH_IDENTITY_AND_UNITS int8,
        HASH_IDENTITY_SYS_UNITS int8,
        SP_SYSTEM varchar(200),
        SP_UNITS varchar(200),
        SP_VALUE float8,
        primary key (SP_ID)
    );

    create table HFJ_SPIDX_QUANTITY_NRML (
       SP_ID int8 not null,
        PARTITION_DATE date,
        PARTITION_ID int4,
        SP_MISSING boolean not null,
        SP_NAME varchar(100) not null,
        RES_ID int8 not null,
        RES_TYPE varchar(100) not null,
        SP_UPDATED timestamp,
        HASH_IDENTITY int8,
        HASH_IDENTITY_AND_UNITS int8,
        HASH_IDENTITY_SYS_UNITS int8,
        SP_SYSTEM varchar(200),
        SP_UNITS varchar(200),
        SP_VALUE float8,
        primary key (SP_ID)
    );

    create table HFJ_SPIDX_STRING (
       SP_ID int8 not null,
        PARTITION_DATE date,
        PARTITION_ID int4,
        SP_MISSING boolean not null,
        SP_NAME varchar(100) not null,
        RES_ID int8 not null,
        RES_TYPE varchar(100) not null,
        SP_UPDATED timestamp,
        HASH_EXACT int8,
        HASH_IDENTITY int8,
        HASH_NORM_PREFIX int8,
        SP_VALUE_EXACT varchar(200),
        SP_VALUE_NORMALIZED varchar(200),
        primary key (SP_ID)
    );

    create table HFJ_SPIDX_TOKEN (
       SP_ID int8 not null,
        PARTITION_DATE date,
        PARTITION_ID int4,
        SP_MISSING boolean not null,
        SP_NAME varchar(100) not null,
        RES_ID int8 not null,
        RES_TYPE varchar(100) not null,
        SP_UPDATED timestamp,
        HASH_IDENTITY int8,
        HASH_SYS int8,
        HASH_SYS_AND_VALUE int8,
        HASH_VALUE int8,
        SP_SYSTEM varchar(200),
        SP_VALUE varchar(200),
        primary key (SP_ID)
    );

    create table HFJ_SPIDX_URI (
       SP_ID int8 not null,
        PARTITION_DATE date,
        PARTITION_ID int4,
        SP_MISSING boolean not null,
        SP_NAME varchar(100) not null,
        RES_ID int8 not null,
        RES_TYPE varchar(100) not null,
        SP_UPDATED timestamp,
        HASH_IDENTITY int8,
        HASH_URI int8,
        SP_URI varchar(254),
        primary key (SP_ID)
    );

    create table HFJ_SUBSCRIPTION_STATS (
       PID int8 not null,
        CREATED_TIME timestamp not null,
        RES_ID int8,
        primary key (PID)
    );

    create table HFJ_TAG_DEF (
       TAG_ID int8 not null,
        TAG_CODE varchar(200),
        TAG_DISPLAY varchar(200),
        TAG_SYSTEM varchar(200),
        TAG_TYPE int4 not null,
        primary key (TAG_ID)
    );

    create table MPI_LINK (
       PID int8 not null,
        PARTITION_DATE date,
        PARTITION_ID int4,
        CREATED timestamp not null,
        EID_MATCH boolean,
        GOLDEN_RESOURCE_PID int8 not null,
        NEW_PERSON boolean,
        LINK_SOURCE int4 not null,
        MATCH_RESULT int4 not null,
        TARGET_TYPE varchar(40),
        PERSON_PID int8 not null,
        RULE_COUNT int8,
        SCORE float8,
        TARGET_PID int8 not null,
        UPDATED timestamp not null,
        VECTOR int8,
        VERSION varchar(16) not null,
        primary key (PID)
    );

    create table NPM_PACKAGE (
       PID int8 not null,
        CUR_VERSION_ID varchar(200),
        PACKAGE_DESC varchar(200),
        PACKAGE_ID varchar(200) not null,
        UPDATED_TIME timestamp not null,
        primary key (PID)
    );

    create table NPM_PACKAGE_VER (
       PID int8 not null,
        CURRENT_VERSION boolean not null,
        PKG_DESC varchar(200),
        DESC_UPPER varchar(200),
        FHIR_VERSION varchar(10) not null,
        FHIR_VERSION_ID varchar(20) not null,
        PACKAGE_ID varchar(200) not null,
        PACKAGE_SIZE_BYTES int8 not null,
        SAVED_TIME timestamp not null,
        UPDATED_TIME timestamp not null,
        VERSION_ID varchar(200) not null,
        PACKAGE_PID int8 not null,
        BINARY_RES_ID int8 not null,
        primary key (PID)
    );

    create table NPM_PACKAGE_VER_RES (
       PID int8 not null,
        CANONICAL_URL varchar(200),
        CANONICAL_VERSION varchar(200),
        FILE_DIR varchar(200),
        FHIR_VERSION varchar(10) not null,
        FHIR_VERSION_ID varchar(20) not null,
        FILE_NAME varchar(200),
        RES_SIZE_BYTES int8 not null,
        RES_TYPE varchar(40) not null,
        UPDATED_TIME timestamp not null,
        PACKVER_PID int8 not null,
        BINARY_RES_ID int8 not null,
        primary key (PID)
    );

    create table TRM_CODESYSTEM (
       PID int8 not null,
        CODE_SYSTEM_URI varchar(200) not null,
        CURRENT_VERSION_PID int8,
        CS_NAME varchar(200),
        RES_ID int8,
        primary key (PID)
    );

    create table TRM_CODESYSTEM_VER (
       PID int8 not null,
        CS_DISPLAY varchar(200),
        CODESYSTEM_PID int8,
        CS_VERSION_ID varchar(200),
        RES_ID int8 not null,
        primary key (PID)
    );

    create table TRM_CONCEPT (
       PID int8 not null,
        CODEVAL varchar(500) not null,
        CODESYSTEM_PID int8,
        DISPLAY varchar(400),
        INDEX_STATUS int8,
        PARENT_PIDS oid,
        CODE_SEQUENCE int4,
        CONCEPT_UPDATED timestamp,
        primary key (PID)
    );

    create table TRM_CONCEPT_DESIG (
       PID int8 not null,
        LANG varchar(500),
        USE_CODE varchar(500),
        USE_DISPLAY varchar(500),
        USE_SYSTEM varchar(500),
        VAL varchar(2000) not null,
        CS_VER_PID int8,
        CONCEPT_PID int8,
        primary key (PID)
    );

    create table TRM_CONCEPT_MAP (
       PID int8 not null,
        RES_ID int8,
        SOURCE_URL varchar(200),
        TARGET_URL varchar(200),
        URL varchar(200) not null,
        VER varchar(200),
        primary key (PID)
    );

    create table TRM_CONCEPT_MAP_GROUP (
       PID int8 not null,
        CONCEPT_MAP_URL varchar(200),
        SOURCE_URL varchar(200) not null,
        SOURCE_VS varchar(200),
        SOURCE_VERSION varchar(200),
        TARGET_URL varchar(200) not null,
        TARGET_VS varchar(200),
        TARGET_VERSION varchar(200),
        CONCEPT_MAP_PID int8 not null,
        primary key (PID)
    );

    create table TRM_CONCEPT_MAP_GRP_ELEMENT (
       PID int8 not null,
        SOURCE_CODE varchar(500) not null,
        CONCEPT_MAP_URL varchar(200),
        SOURCE_DISPLAY varchar(500),
        SYSTEM_URL varchar(200),
        SYSTEM_VERSION varchar(200),
        VALUESET_URL varchar(200),
        CONCEPT_MAP_GROUP_PID int8 not null,
        primary key (PID)
    );

    create table TRM_CONCEPT_MAP_GRP_ELM_TGT (
       PID int8 not null,
        TARGET_CODE varchar(500) not null,
        CONCEPT_MAP_URL varchar(200),
        TARGET_DISPLAY varchar(500),
        TARGET_EQUIVALENCE varchar(50),
        SYSTEM_URL varchar(200),
        SYSTEM_VERSION varchar(200),
        VALUESET_URL varchar(200),
        CONCEPT_MAP_GRP_ELM_PID int8 not null,
        primary key (PID)
    );

    create table TRM_CONCEPT_PC_LINK (
       PID int8 not null,
        CHILD_PID int8,
        CODESYSTEM_PID int8 not null,
        PARENT_PID int8,
        REL_TYPE int4,
        primary key (PID)
    );

    create table TRM_CONCEPT_PROPERTY (
       PID int8 not null,
        PROP_CODESYSTEM varchar(500),
        PROP_DISPLAY varchar(500),
        PROP_KEY varchar(500) not null,
        PROP_TYPE int4 not null,
        PROP_VAL varchar(500),
        PROP_VAL_LOB oid,
        CS_VER_PID int8,
        CONCEPT_PID int8,
        primary key (PID)
    );

    create table TRM_VALUESET (
       PID int8 not null,
        EXPANSION_STATUS varchar(50) not null,
        EXPANDED_AT timestamp,
        VSNAME varchar(200),
        RES_ID int8,
        TOTAL_CONCEPT_DESIGNATIONS int8 default 0 not null,
        TOTAL_CONCEPTS int8 default 0 not null,
        URL varchar(200) not null,
        VER varchar(200),
        primary key (PID)
    );

    create table TRM_VALUESET_C_DESIGNATION (
       PID int8 not null,
        VALUESET_CONCEPT_PID int8 not null,
        LANG varchar(500),
        USE_CODE varchar(500),
        USE_DISPLAY varchar(500),
        USE_SYSTEM varchar(500),
        VAL varchar(2000) not null,
        VALUESET_PID int8 not null,
        primary key (PID)
    );

    create table TRM_VALUESET_CONCEPT (
       PID int8 not null,
        CODEVAL varchar(500) not null,
        DISPLAY varchar(400),
        INDEX_STATUS int8,
        VALUESET_ORDER int4 not null,
        SOURCE_DIRECT_PARENT_PIDS oid,
        SOURCE_PID int8,
        SYSTEM_URL varchar(200) not null,
        SYSTEM_VER varchar(200),
        VALUESET_PID int8 not null,
        primary key (PID)
    );
create index IDX_BT2JI_CT on BT2_JOB_INSTANCE (CREATE_TIME);
create index IDX_BT2WC_II_SEQ on BT2_WORK_CHUNK (INSTANCE_ID, SEQ);
create index IDX_CDH_LB_REF_RULE_SUBS on CDH_LB_REF (RULE_SYSTEM, RULE_VALUE, SUBS_RES_ID);
create index IDX_CDH_LB_REF_TRACK_PARAM on CDH_LB_REF (TRACK_PARAM);
create index IDX_CDH_LB_REF_TRACK_SUBPARAM on CDH_LB_REF (TRACK_SUBPARAM);
create index IDX_CDH_LB_REF_EXPIRES on CDH_LB_REF (EXPIRES);

    alter table if exists CDH_LB_REF 
       add constraint IDX_CDH_LB_REF_UNIQ unique (RULE_SYSTEM, RULE_VALUE, ROOT_RES_ID, SUBS_RES_ID, LB_RES_ID);
create index IDX_CDH_LB_SUB_GROUP_GROUP on CDH_LB_SUB_GROUP (SUBS_GROUP);
create index IDX_CDH_LB_SUB_GROUP_ID on CDH_LB_SUB_GROUP (SUBS_ID);

    alter table if exists CDH_LB_SUB_GROUP 
       add constraint IDX_CDH_LB_SUB_ID_GROUP unique (SUBS_RES_ID, SUBS_GROUP);

    alter table if exists CDH_LB_WL 
       add constraint IDX_CDH_LB_WL_WATCHLIST_TOKEN unique (WATCHLIST_SYSTEM, WATCHLIST_VALUE);

    alter table if exists CDH_LB_WL_SUBS 
       add constraint IDX_CDH_LB_WL_SUBS_WATCHLIST unique (WATCHLIST_ID, SUBS_RES_ID);
create index IDX_BLKEX_EXPTIME on HFJ_BLK_EXPORT_JOB (EXP_TIME);

    alter table if exists HFJ_BLK_EXPORT_JOB 
       add constraint IDX_BLKEX_JOB_ID unique (JOB_ID);

    alter table if exists HFJ_BLK_IMPORT_JOB 
       add constraint IDX_BLKIM_JOB_ID unique (JOB_ID);
create index IDX_BLKIM_JOBFILE_JOBID on HFJ_BLK_IMPORT_JOBFILE (JOB_PID);
create index IDX_FORCEID_FID on HFJ_FORCED_ID (FORCED_ID);

    alter table if exists HFJ_FORCED_ID 
       add constraint IDX_FORCEDID_RESID unique (RESOURCE_PID);

    alter table if exists HFJ_FORCED_ID 
       add constraint IDX_FORCEDID_TYPE_FID unique (RESOURCE_TYPE, FORCED_ID);
create index IDX_RESHISTTAG_RESID on HFJ_HISTORY_TAG (RES_ID);

    alter table if exists HFJ_HISTORY_TAG 
       add constraint IDX_RESHISTTAG_TAGID unique (RES_VER_PID, TAG_ID);
create index IDX_IDXCMBTOKNU_STR on HFJ_IDX_CMB_TOK_NU (IDX_STRING);
create index IDX_IDXCMBTOKNU_RES on HFJ_IDX_CMB_TOK_NU (RES_ID);
create index IDX_IDXCMPSTRUNIQ_RESOURCE on HFJ_IDX_CMP_STRING_UNIQ (RES_ID);

    alter table if exists HFJ_IDX_CMP_STRING_UNIQ 
       add constraint IDX_IDXCMPSTRUNIQ_STRING unique (IDX_STRING);

    alter table if exists HFJ_PARTITION 
       add constraint IDX_PART_NAME unique (PART_NAME);
create index IDX_RL_TPATHRES on HFJ_RES_LINK (SRC_PATH, TARGET_RESOURCE_ID);
create index IDX_RL_SRC on HFJ_RES_LINK (SRC_RESOURCE_ID);
create index IDX_RL_DEST on HFJ_RES_LINK (TARGET_RESOURCE_ID);
create index IDX_RESPARMPRESENT_RESID on HFJ_RES_PARAM_PRESENT (RES_ID);
create index IDX_RESPARMPRESENT_HASHPRES on HFJ_RES_PARAM_PRESENT (HASH_PRESENCE);
create index IDX_RES_TAG_RES_TAG on HFJ_RES_TAG (RES_ID, TAG_ID, PARTITION_ID);
create index IDX_RES_TAG_TAG_RES on HFJ_RES_TAG (TAG_ID, RES_ID, PARTITION_ID);

    alter table if exists HFJ_RES_TAG 
       add constraint IDX_RESTAG_TAGID unique (RES_ID, TAG_ID);
create index IDX_RESVER_TYPE_DATE on HFJ_RES_VER (RES_TYPE, RES_UPDATED);
create index IDX_RESVER_ID_DATE on HFJ_RES_VER (RES_ID, RES_UPDATED);
create index IDX_RESVER_DATE on HFJ_RES_VER (RES_UPDATED);

    alter table if exists HFJ_RES_VER 
       add constraint IDX_RESVER_ID_VER unique (RES_ID, RES_VER);
create index IDX_RESVERPROV_SOURCEURI on HFJ_RES_VER_PROV (SOURCE_URI);
create index IDX_RESVERPROV_REQUESTID on HFJ_RES_VER_PROV (REQUEST_ID);
create index IDX_RES_DATE on HFJ_RESOURCE (RES_UPDATED);
create index IDX_RES_TYPE_DEL_UPDATED on HFJ_RESOURCE (RES_TYPE, RES_DELETED_AT, RES_UPDATED, PARTITION_ID, RES_ID);
create index IDX_SEARCH_RESTYPE_HASHS on HFJ_SEARCH (RESOURCE_TYPE, SEARCH_QUERY_STRING_HASH, CREATED);
create index IDX_SEARCH_CREATED on HFJ_SEARCH (CREATED);

    alter table if exists HFJ_SEARCH 
       add constraint IDX_SEARCH_UUID unique (SEARCH_UUID);

    alter table if exists HFJ_SEARCH_RESULT 
       add constraint IDX_SEARCHRES_ORDER unique (SEARCH_PID, SEARCH_ORDER);
create index IDX_SP_COORDS_HASH on HFJ_SPIDX_COORDS (HASH_IDENTITY, SP_LATITUDE, SP_LONGITUDE);
create index IDX_SP_COORDS_UPDATED on HFJ_SPIDX_COORDS (SP_UPDATED);
create index IDX_SP_COORDS_RESID on HFJ_SPIDX_COORDS (RES_ID);
create index IDX_SP_DATE_HASH_V2 on HFJ_SPIDX_DATE (HASH_IDENTITY, SP_VALUE_LOW, SP_VALUE_HIGH, RES_ID, PARTITION_ID);
create index IDX_SP_DATE_HASH_HIGH_V2 on HFJ_SPIDX_DATE (HASH_IDENTITY, SP_VALUE_HIGH, RES_ID, PARTITION_ID);
create index IDX_SP_DATE_ORD_HASH_V2 on HFJ_SPIDX_DATE (HASH_IDENTITY, SP_VALUE_LOW_DATE_ORDINAL, SP_VALUE_HIGH_DATE_ORDINAL, RES_ID, PARTITION_ID);
create index IDX_SP_DATE_ORD_HASH_HIGH_V2 on HFJ_SPIDX_DATE (HASH_IDENTITY, SP_VALUE_HIGH_DATE_ORDINAL, RES_ID, PARTITION_ID);
create index IDX_SP_DATE_RESID_V2 on HFJ_SPIDX_DATE (RES_ID, HASH_IDENTITY, SP_VALUE_LOW, SP_VALUE_HIGH, SP_VALUE_LOW_DATE_ORDINAL, SP_VALUE_HIGH_DATE_ORDINAL, PARTITION_ID);
create index IDX_SP_NUMBER_HASH_VAL_V2 on HFJ_SPIDX_NUMBER (HASH_IDENTITY, SP_VALUE, RES_ID, PARTITION_ID);
create index IDX_SP_NUMBER_RESID_V2 on HFJ_SPIDX_NUMBER (RES_ID, HASH_IDENTITY, SP_VALUE, PARTITION_ID);
create index IDX_SP_QUANTITY_HASH_V2 on HFJ_SPIDX_QUANTITY (HASH_IDENTITY, SP_VALUE, RES_ID, PARTITION_ID);
create index IDX_SP_QUANTITY_HASH_UN_V2 on HFJ_SPIDX_QUANTITY (HASH_IDENTITY_AND_UNITS, SP_VALUE, RES_ID, PARTITION_ID);
create index IDX_SP_QUANTITY_HASH_SYSUN_V2 on HFJ_SPIDX_QUANTITY (HASH_IDENTITY_SYS_UNITS, SP_VALUE, RES_ID, PARTITION_ID);
create index IDX_SP_QUANTITY_RESID_V2 on HFJ_SPIDX_QUANTITY (RES_ID, HASH_IDENTITY, HASH_IDENTITY_SYS_UNITS, HASH_IDENTITY_AND_UNITS, SP_VALUE, PARTITION_ID);
create index IDX_SP_QNTY_NRML_HASH_V2 on HFJ_SPIDX_QUANTITY_NRML (HASH_IDENTITY, SP_VALUE, RES_ID, PARTITION_ID);
create index IDX_SP_QNTY_NRML_HASH_UN_V2 on HFJ_SPIDX_QUANTITY_NRML (HASH_IDENTITY_AND_UNITS, SP_VALUE, RES_ID, PARTITION_ID);
create index IDX_SP_QNTY_NRML_HASH_SYSUN_V2 on HFJ_SPIDX_QUANTITY_NRML (HASH_IDENTITY_SYS_UNITS, SP_VALUE, RES_ID, PARTITION_ID);
create index IDX_SP_QNTY_NRML_RESID_V2 on HFJ_SPIDX_QUANTITY_NRML (RES_ID, HASH_IDENTITY, HASH_IDENTITY_SYS_UNITS, HASH_IDENTITY_AND_UNITS, SP_VALUE, PARTITION_ID);
create index IDX_SP_STRING_HASH_IDENT on HFJ_SPIDX_STRING (HASH_IDENTITY);
create index IDX_SP_STRING_HASH_NRM_V2 on HFJ_SPIDX_STRING (HASH_NORM_PREFIX, SP_VALUE_NORMALIZED, RES_ID, PARTITION_ID);
create index IDX_SP_STRING_HASH_EXCT_V2 on HFJ_SPIDX_STRING (HASH_EXACT, RES_ID, PARTITION_ID);
create index IDX_SP_STRING_RESID on HFJ_SPIDX_STRING (RES_ID);
create index IDX_SP_TOKEN_HASH_V2 on HFJ_SPIDX_TOKEN (HASH_IDENTITY, SP_SYSTEM, SP_VALUE, RES_ID, PARTITION_ID);
create index IDX_SP_TOKEN_HASH_S_V2 on HFJ_SPIDX_TOKEN (HASH_SYS, RES_ID, PARTITION_ID);
create index IDX_SP_TOKEN_HASH_SV_V2 on HFJ_SPIDX_TOKEN (HASH_SYS_AND_VALUE, RES_ID, PARTITION_ID);
create index IDX_SP_TOKEN_HASH_V_V2 on HFJ_SPIDX_TOKEN (HASH_VALUE, RES_ID, PARTITION_ID);
create index IDX_SP_TOKEN_RESID_V2 on HFJ_SPIDX_TOKEN (RES_ID, HASH_SYS_AND_VALUE, HASH_VALUE, HASH_SYS, HASH_IDENTITY, PARTITION_ID);
create index IDX_SP_URI on HFJ_SPIDX_URI (RES_TYPE, SP_NAME, SP_URI);
create index IDX_SP_URI_HASH_IDENTITY on HFJ_SPIDX_URI (HASH_IDENTITY, SP_URI);
create index IDX_SP_URI_HASH_URI on HFJ_SPIDX_URI (HASH_URI);
create index IDX_SP_URI_RESTYPE_NAME on HFJ_SPIDX_URI (RES_TYPE, SP_NAME);
create index IDX_SP_URI_UPDATED on HFJ_SPIDX_URI (SP_UPDATED);
create index IDX_SP_URI_COORDS on HFJ_SPIDX_URI (RES_ID);

    alter table if exists HFJ_SUBSCRIPTION_STATS 
       add constraint IDX_SUBSC_RESID unique (RES_ID);
create index IDX_TAG_DEF_TP_CD_SYS on HFJ_TAG_DEF (TAG_TYPE, TAG_CODE, TAG_SYSTEM, TAG_ID);

    alter table if exists HFJ_TAG_DEF 
       add constraint IDX_TAGDEF_TYPESYSCODE unique (TAG_TYPE, TAG_SYSTEM, TAG_CODE);
create index IDX_EMPI_MATCH_TGT_VER on MPI_LINK (MATCH_RESULT, TARGET_PID, VERSION);

    alter table if exists MPI_LINK 
       add constraint IDX_EMPI_PERSON_TGT unique (PERSON_PID, TARGET_PID);

    alter table if exists NPM_PACKAGE 
       add constraint IDX_PACK_ID unique (PACKAGE_ID);

    alter table if exists NPM_PACKAGE_VER 
       add constraint IDX_PACKVER unique (PACKAGE_ID, VERSION_ID);
create index IDX_PACKVERRES_URL on NPM_PACKAGE_VER_RES (CANONICAL_URL);

    alter table if exists TRM_CODESYSTEM 
       add constraint IDX_CS_CODESYSTEM unique (CODE_SYSTEM_URI);

    alter table if exists TRM_CODESYSTEM_VER 
       add constraint IDX_CODESYSTEM_AND_VER unique (CODESYSTEM_PID, CS_VERSION_ID);
create index IDX_CONCEPT_INDEXSTATUS on TRM_CONCEPT (INDEX_STATUS);
create index IDX_CONCEPT_UPDATED on TRM_CONCEPT (CONCEPT_UPDATED);

    alter table if exists TRM_CONCEPT 
       add constraint IDX_CONCEPT_CS_CODE unique (CODESYSTEM_PID, CODEVAL);
create index FK_CONCEPTDESIG_CONCEPT on TRM_CONCEPT_DESIG (CONCEPT_PID);

    alter table if exists TRM_CONCEPT_MAP 
       add constraint IDX_CONCEPT_MAP_URL unique (URL, VER);
create index IDX_CNCPT_MAP_GRP_CD on TRM_CONCEPT_MAP_GRP_ELEMENT (SOURCE_CODE);
create index IDX_CNCPT_MP_GRP_ELM_TGT_CD on TRM_CONCEPT_MAP_GRP_ELM_TGT (TARGET_CODE);
create index FK_TERM_CONCEPTPC_CHILD on TRM_CONCEPT_PC_LINK (CHILD_PID);
create index FK_TERM_CONCEPTPC_PARENT on TRM_CONCEPT_PC_LINK (PARENT_PID);
create index FK_CONCEPTPROP_CONCEPT on TRM_CONCEPT_PROPERTY (CONCEPT_PID);

    alter table if exists TRM_VALUESET 
       add constraint IDX_VALUESET_URL unique (URL, VER);
create index FK_TRM_VALUESET_CONCEPT_PID on TRM_VALUESET_C_DESIGNATION (VALUESET_CONCEPT_PID);

    alter table if exists TRM_VALUESET_CONCEPT 
       add constraint IDX_VS_CONCEPT_CSCD unique (VALUESET_PID, SYSTEM_URL, CODEVAL);

    alter table if exists TRM_VALUESET_CONCEPT 
       add constraint IDX_VS_CONCEPT_ORDER unique (VALUESET_PID, VALUESET_ORDER);

    alter table if exists BT2_WORK_CHUNK 
       add constraint FK_BT2WC_INSTANCE 
       foreign key (INSTANCE_ID) 
       references BT2_JOB_INSTANCE;

    alter table if exists CDH_LB_REF 
       add constraint FK_LB_RES 
       foreign key (LB_RES_ID) 
       references HFJ_RESOURCE;

    alter table if exists CDH_LB_REF 
       add constraint FK_LB_ROOT 
       foreign key (ROOT_RES_ID) 
       references HFJ_RESOURCE;

    alter table if exists CDH_LB_REF 
       add constraint FK_LB_SUBS 
       foreign key (SUBS_RES_ID) 
       references HFJ_RESOURCE;

    alter table if exists CDH_LB_SUB_GROUP 
       add constraint FK_LB_GP_SUBS 
       foreign key (SUBS_RES_ID) 
       references HFJ_RESOURCE;

    alter table if exists CDH_LB_WL_SUBS 
       add constraint FK_LB_WL_SUBS 
       foreign key (SUBS_RES_ID) 
       references HFJ_RESOURCE;

    alter table if exists CDH_LB_WL_SUBS 
       add constraint FK_LB_WL_ID 
       foreign key (WATCHLIST_ID) 
       references CDH_LB_WL;

    alter table if exists HFJ_BLK_EXPORT_COLFILE 
       add constraint FK_BLKEXCOLFILE_COLLECT 
       foreign key (COLLECTION_PID) 
       references HFJ_BLK_EXPORT_COLLECTION;

    alter table if exists HFJ_BLK_EXPORT_COLLECTION 
       add constraint FK_BLKEXCOL_JOB 
       foreign key (JOB_PID) 
       references HFJ_BLK_EXPORT_JOB;

    alter table if exists HFJ_BLK_IMPORT_JOBFILE 
       add constraint FK_BLKIMJOBFILE_JOB 
       foreign key (JOB_PID) 
       references HFJ_BLK_IMPORT_JOB;

    alter table if exists HFJ_FORCED_ID 
       add constraint FK_FORCEDID_RESOURCE 
       foreign key (RESOURCE_PID) 
       references HFJ_RESOURCE;

    alter table if exists HFJ_HISTORY_TAG 
       add constraint FKtderym7awj6q8iq5c51xv4ndw 
       foreign key (TAG_ID) 
       references HFJ_TAG_DEF;

    alter table if exists HFJ_HISTORY_TAG 
       add constraint FK_HISTORYTAG_HISTORY 
       foreign key (RES_VER_PID) 
       references HFJ_RES_VER;

    alter table if exists HFJ_IDX_CMB_TOK_NU 
       add constraint FK_IDXCMBTOKNU_RES_ID 
       foreign key (RES_ID) 
       references HFJ_RESOURCE;

    alter table if exists HFJ_IDX_CMP_STRING_UNIQ 
       add constraint FK_IDXCMPSTRUNIQ_RES_ID 
       foreign key (RES_ID) 
       references HFJ_RESOURCE;

    alter table if exists HFJ_RES_LINK 
       add constraint FK_RESLINK_SOURCE 
       foreign key (SRC_RESOURCE_ID) 
       references HFJ_RESOURCE;

    alter table if exists HFJ_RES_LINK 
       add constraint FK_RESLINK_TARGET 
       foreign key (TARGET_RESOURCE_ID) 
       references HFJ_RESOURCE;

    alter table if exists HFJ_RES_PARAM_PRESENT 
       add constraint FK_RESPARMPRES_RESID 
       foreign key (RES_ID) 
       references HFJ_RESOURCE;

    alter table if exists HFJ_RES_TAG 
       add constraint FKbfcjbaftmiwr3rxkwsy23vneo 
       foreign key (TAG_ID) 
       references HFJ_TAG_DEF;

    alter table if exists HFJ_RES_TAG 
       add constraint FK_RESTAG_RESOURCE 
       foreign key (RES_ID) 
       references HFJ_RESOURCE;

    alter table if exists HFJ_RES_VER 
       add constraint FK_RESOURCE_HISTORY_RESOURCE 
       foreign key (RES_ID) 
       references HFJ_RESOURCE;

    alter table if exists HFJ_RES_VER_PROV 
       add constraint FK_RESVERPROV_RES_PID 
       foreign key (RES_PID) 
       references HFJ_RESOURCE;

    alter table if exists HFJ_RES_VER_PROV 
       add constraint FK_RESVERPROV_RESVER_PID 
       foreign key (RES_VER_PID) 
       references HFJ_RES_VER;

    alter table if exists HFJ_SEARCH_INCLUDE 
       add constraint FK_SEARCHINC_SEARCH 
       foreign key (SEARCH_PID) 
       references HFJ_SEARCH;

    alter table if exists HFJ_SPIDX_COORDS 
       add constraint FKC97MPK37OKWU8QVTCEG2NH9VN 
       foreign key (RES_ID) 
       references HFJ_RESOURCE;

    alter table if exists HFJ_SPIDX_DATE 
       add constraint FK_SP_DATE_RES 
       foreign key (RES_ID) 
       references HFJ_RESOURCE;

    alter table if exists HFJ_SPIDX_NUMBER 
       add constraint FK_SP_NUMBER_RES 
       foreign key (RES_ID) 
       references HFJ_RESOURCE;

    alter table if exists HFJ_SPIDX_QUANTITY 
       add constraint FK_SP_QUANTITY_RES 
       foreign key (RES_ID) 
       references HFJ_RESOURCE;

    alter table if exists HFJ_SPIDX_QUANTITY_NRML 
       add constraint FK_SP_QUANTITYNM_RES 
       foreign key (RES_ID) 
       references HFJ_RESOURCE;

    alter table if exists HFJ_SPIDX_STRING 
       add constraint FK_SPIDXSTR_RESOURCE 
       foreign key (RES_ID) 
       references HFJ_RESOURCE;

    alter table if exists HFJ_SPIDX_TOKEN 
       add constraint FK_SP_TOKEN_RES 
       foreign key (RES_ID) 
       references HFJ_RESOURCE;

    alter table if exists HFJ_SPIDX_URI 
       add constraint FKGXSREUTYMMFJUWDSWV3Y887DO 
       foreign key (RES_ID) 
       references HFJ_RESOURCE;

    alter table if exists HFJ_SUBSCRIPTION_STATS 
       add constraint FK_SUBSC_RESOURCE_ID 
       foreign key (RES_ID) 
       references HFJ_RESOURCE;

    alter table if exists MPI_LINK 
       add constraint FK_EMPI_LINK_GOLDEN_RESOURCE 
       foreign key (GOLDEN_RESOURCE_PID) 
       references HFJ_RESOURCE;

    alter table if exists MPI_LINK 
       add constraint FK_EMPI_LINK_PERSON 
       foreign key (PERSON_PID) 
       references HFJ_RESOURCE;

    alter table if exists MPI_LINK 
       add constraint FK_EMPI_LINK_TARGET 
       foreign key (TARGET_PID) 
       references HFJ_RESOURCE;

    alter table if exists NPM_PACKAGE_VER 
       add constraint FK_NPM_PKV_PKG 
       foreign key (PACKAGE_PID) 
       references NPM_PACKAGE;

    alter table if exists NPM_PACKAGE_VER 
       add constraint FK_NPM_PKV_RESID 
       foreign key (BINARY_RES_ID) 
       references HFJ_RESOURCE;

    alter table if exists NPM_PACKAGE_VER_RES 
       add constraint FK_NPM_PACKVERRES_PACKVER 
       foreign key (PACKVER_PID) 
       references NPM_PACKAGE_VER;

    alter table if exists NPM_PACKAGE_VER_RES 
       add constraint FK_NPM_PKVR_RESID 
       foreign key (BINARY_RES_ID) 
       references HFJ_RESOURCE;

    alter table if exists TRM_CODESYSTEM 
       add constraint FK_TRMCODESYSTEM_CURVER 
       foreign key (CURRENT_VERSION_PID) 
       references TRM_CODESYSTEM_VER;

    alter table if exists TRM_CODESYSTEM 
       add constraint FK_TRMCODESYSTEM_RES 
       foreign key (RES_ID) 
       references HFJ_RESOURCE;

    alter table if exists TRM_CODESYSTEM_VER 
       add constraint FK_CODESYSVER_CS_ID 
       foreign key (CODESYSTEM_PID) 
       references TRM_CODESYSTEM;

    alter table if exists TRM_CODESYSTEM_VER 
       add constraint FK_CODESYSVER_RES_ID 
       foreign key (RES_ID) 
       references HFJ_RESOURCE;

    alter table if exists TRM_CONCEPT 
       add constraint FK_CONCEPT_PID_CS_PID 
       foreign key (CODESYSTEM_PID) 
       references TRM_CODESYSTEM_VER;

    alter table if exists TRM_CONCEPT_DESIG 
       add constraint FK_CONCEPTDESIG_CSV 
       foreign key (CS_VER_PID) 
       references TRM_CODESYSTEM_VER;

    alter table if exists TRM_CONCEPT_DESIG 
       add constraint FK_CONCEPTDESIG_CONCEPT 
       foreign key (CONCEPT_PID) 
       references TRM_CONCEPT;

    alter table if exists TRM_CONCEPT_MAP 
       add constraint FK_TRMCONCEPTMAP_RES 
       foreign key (RES_ID) 
       references HFJ_RESOURCE;

    alter table if exists TRM_CONCEPT_MAP_GROUP 
       add constraint FK_TCMGROUP_CONCEPTMAP 
       foreign key (CONCEPT_MAP_PID) 
       references TRM_CONCEPT_MAP;

    alter table if exists TRM_CONCEPT_MAP_GRP_ELEMENT 
       add constraint FK_TCMGELEMENT_GROUP 
       foreign key (CONCEPT_MAP_GROUP_PID) 
       references TRM_CONCEPT_MAP_GROUP;

    alter table if exists TRM_CONCEPT_MAP_GRP_ELM_TGT 
       add constraint FK_TCMGETARGET_ELEMENT 
       foreign key (CONCEPT_MAP_GRP_ELM_PID) 
       references TRM_CONCEPT_MAP_GRP_ELEMENT;

    alter table if exists TRM_CONCEPT_PC_LINK 
       add constraint FK_TERM_CONCEPTPC_CHILD 
       foreign key (CHILD_PID) 
       references TRM_CONCEPT;

    alter table if exists TRM_CONCEPT_PC_LINK 
       add constraint FK_TERM_CONCEPTPC_CS 
       foreign key (CODESYSTEM_PID) 
       references TRM_CODESYSTEM_VER;

    alter table if exists TRM_CONCEPT_PC_LINK 
       add constraint FK_TERM_CONCEPTPC_PARENT 
       foreign key (PARENT_PID) 
       references TRM_CONCEPT;

    alter table if exists TRM_CONCEPT_PROPERTY 
       add constraint FK_CONCEPTPROP_CSV 
       foreign key (CS_VER_PID) 
       references TRM_CODESYSTEM_VER;

    alter table if exists TRM_CONCEPT_PROPERTY 
       add constraint FK_CONCEPTPROP_CONCEPT 
       foreign key (CONCEPT_PID) 
       references TRM_CONCEPT;

    alter table if exists TRM_VALUESET 
       add constraint FK_TRMVALUESET_RES 
       foreign key (RES_ID) 
       references HFJ_RESOURCE;

    alter table if exists TRM_VALUESET_C_DESIGNATION 
       add constraint FK_TRM_VALUESET_CONCEPT_PID 
       foreign key (VALUESET_CONCEPT_PID) 
       references TRM_VALUESET_CONCEPT;

    alter table if exists TRM_VALUESET_C_DESIGNATION 
       add constraint FK_TRM_VSCD_VS_PID 
       foreign key (VALUESET_PID) 
       references TRM_VALUESET;

    alter table if exists TRM_VALUESET_CONCEPT 
       add constraint FK_TRM_VALUESET_PID 
       foreign key (VALUESET_PID) 
       references TRM_VALUESET;