Smile CDR v2023.08.PRE
On this page:

37.2Setting Up PostgreSQL

 

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

37.2.1Supported Versions

 

See platform requirements for information on supported versions of PostgreSQL.

37.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;

37.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). The following example shows properties for the clustermgr module but the same properties also apply to FHIR Storage (RDBMS) modules. It is recommended but not required to use separate database schemas for the Cluster Manager and FHIR Storage modules however.

module.clustermgr.config.db.driver    =POSTGRES_9_4
module.clustermgr.config.db.url       =jdbc:postgresql://localhost:5432/cdr_clustermgr
module.clustermgr.config.db.username  =my-username
module.clustermgr.config.db.password  =my-password
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.

37.2.4Inline Resource Storage

 

The Compressed BLOB Storage mode for resource storage is particularly slow on PostgreSQL. In addition, due to the way that Large Objects work in PostgreSQL, there is a hard limit of roughly 4 billion large objects for a given database. As a result, it is particularly important to choose a setting for Inline Resource Storage Below Size that is above the size of most of your resources if you are designing a system that will achieve large scale.

37.2.5Setting 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_PERMISSION_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_ID_CODE_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),
        APP_DEVELOPER_NAME varchar(200),
        APP_HOMEPAGE varchar(200),
        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),
        COMPANY_NAME varchar(200),
        EMAIL varchar(200) not null,
        FIRST_NAME varchar(200) not null,
        INDUSTRY varchar(200),
        JOB_TITLE varchar(200),
        LAST_NAME varchar(200) not null,
        NOTE varchar(400),
        REQUEST_STATUS varchar(20) not null,
        SUBSCRIBE_ACCEPTED boolean,
        MODULE_PID int8 not null,
        primary key (PID)
    );

    create table AG_APPL (
       PID int8 not null,
        ATTESTATION_ACCEPTED boolean not null,
        BACKEND_SERVICE boolean,
        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,
        BACKEND_SERVICE boolean,
        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_PERMISSION (
       PID int8 not null,
        PERMISSION 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,
        SCOPE_TYPE varchar(20),
        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_IDENTIFICATION_CODES (
       PID int8 not null,
        CDR_CODE varchar(6) not null,
        CODE_STATUS varchar(20) not null,
        EXPIRES_ON timestamp not null,
        USER_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_MODULE_DIAGNOSTICS (
       DIAGNOSTICS_JSON oid,
        MODULE_PID int8 not null,
        NODE_PID int8 not null,
        primary key (MODULE_PID, NODE_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,
        ARCHIVED_AT timestamp,
        AUDIENCE varchar(500),
        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,
        ARCHIVED_AT timestamp,
        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_CDRAUDITEVT_EVTTIMESTAMP on CDR_AUDIT_EVT (EVT_TIMESTAMP);
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_V2 on CDR_METRIC_TIMER (START_TIME, MODPROC_PID, IS_COLLAPSED);

    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, ARCHIVED_AT);

    alter table if exists CDR_OA2_SERVER 
       add constraint IDX_OA2SERVER_MODULE_ISSURL unique (MODULE_PID, ISS, ARCHIVED_AT);
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, ARCHIVED_AT);
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_PERMISSION 
       add constraint FK_AG_PERMISSION_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_IDENTIFICATION_CODES 
       add constraint FK_IDCODE_USER 
       foreign key (USER_PID) 
       references CDR_USER_DETAILS;

    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_MODULE_DIAGNOSTICS 
       add constraint FK_MULTI_NODE_MODULE_PID 
       foreign key (MODULE_PID) 
       references CDR_MODULE;

    alter table if exists CDR_NODE_MODULE_DIAGNOSTICS 
       add constraint FK_MULTI_NODE_PID 
       foreign key (NODE_PID) 
       references CDR_NODE;

    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;

37.2.6Setting 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_HFJ_REVINFO 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,
        UPDATE_TIME timestamp,
        WARNING_MSG oid,
        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,
        UPDATE_TIME timestamp,
        WARNING_MSG oid,
        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 int8,
        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_SEARCH_URL (
       RES_SEARCH_URL varchar(768) not null,
        CREATED_TIME timestamp not null,
        RES_ID int8 not null,
        primary key (RES_SEARCH_URL)
    );

    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,
        REQUEST_ID varchar(16),
        RES_TEXT oid,
        RES_ID int8 not null,
        RES_TEXT_VC text,
        RES_TYPE varchar(40) not null,
        RES_VER int8 not null,
        SOURCE_URI varchar(100),
        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,
        FHIR_ID varchar(64),
        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,
        SEARCH_URL_PRESENT boolean,
        RES_VER int8,
        primary key (RES_ID)
    );

    create table HFJ_REVINFO (
       REV int8 not null,
        REVTSTMP timestamp,
        primary key (REV)
    );

    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(48) 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(500),
        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,
        TAG_USER_SELECTED boolean,
        TAG_VERSION varchar(30),
        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 MPI_LINK_AUD (
       PID int8 not null,
        REV int8 not null,
        REVTYPE int2,
        PARTITION_DATE date,
        PARTITION_ID int4,
        CREATED timestamp,
        EID_MATCH boolean,
        GOLDEN_RESOURCE_PID int8,
        NEW_PERSON boolean,
        LINK_SOURCE int4,
        MATCH_RESULT int4,
        TARGET_TYPE varchar(40),
        PERSON_PID int8,
        RULE_COUNT int8,
        SCORE float8,
        TARGET_PID int8,
        UPDATED timestamp,
        VECTOR int8,
        VERSION varchar(16),
        primary key (PID, REV)
    );

    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_SRC on HFJ_RES_LINK (SRC_RESOURCE_ID);
create index IDX_RL_TGT_v2 on HFJ_RES_LINK (TARGET_RESOURCE_ID, SRC_PATH, SRC_RESOURCE_ID, TARGET_RESOURCE_TYPE, PARTITION_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_RESSEARCHURL_RES on HFJ_RES_SEARCH_URL (RES_ID);
create index IDX_RESSEARCHURL_TIME on HFJ_RES_SEARCH_URL (CREATED_TIME);
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_RESVERPROV_RES_PID on HFJ_RES_VER_PROV (RES_PID);
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_RES_RESID_UPDATED on HFJ_RESOURCE (RES_ID, RES_UPDATED, PARTITION_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);
create index FK_SEARCHINC_SEARCH on HFJ_SEARCH_INCLUDE (SEARCH_PID);

    alter table if exists HFJ_SEARCH_RESULT 
       add constraint IDX_SEARCHRES_ORDER unique (SEARCH_PID, SEARCH_ORDER);
create index IDX_SP_COORDS_HASH_V2 on HFJ_SPIDX_COORDS (HASH_IDENTITY, SP_LATITUDE, SP_LONGITUDE, RES_ID, PARTITION_ID);
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_V2 on HFJ_SPIDX_STRING (HASH_IDENTITY, RES_ID, PARTITION_ID);
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_V2 on HFJ_SPIDX_STRING (RES_ID, HASH_NORM_PREFIX, PARTITION_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_COORDS on HFJ_SPIDX_URI (RES_ID);

    alter table if exists HFJ_SPIDX_URI 
       add constraint IDX_SP_URI_HASH_URI_V2 unique (HASH_URI, RES_ID, PARTITION_ID);

    alter table if exists HFJ_SPIDX_URI 
       add constraint IDX_SP_URI_HASH_IDENTITY_V2 unique (HASH_IDENTITY, SP_URI, RES_ID, PARTITION_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, TAG_VERSION, TAG_USER_SELECTED);
create index IDX_EMPI_MATCH_TGT_VER on MPI_LINK (MATCH_RESULT, TARGET_PID, VERSION);
create index IDX_EMPI_GR_TGT on MPI_LINK (GOLDEN_RESOURCE_PID, TARGET_PID);

    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 MPI_LINK_AUD 
       add constraint FKaow7nxncloec419ars0fpp58m 
       foreign key (REV) 
       references HFJ_REVINFO;

    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;

-- we can't use convering index until the autovacuum runs for those rows, which kills index performance
ALTER TABLE hfj_resource SET (autovacuum_vacuum_scale_factor = 0.01);
ALTER TABLE hfj_forced_id SET (autovacuum_vacuum_scale_factor = 0.01);
ALTER TABLE hfj_res_link SET (autovacuum_vacuum_scale_factor = 0.01);
ALTER TABLE hfj_spidx_coords SET (autovacuum_vacuum_scale_factor = 0.01);
ALTER TABLE hfj_spidx_date SET (autovacuum_vacuum_scale_factor = 0.01);
ALTER TABLE hfj_spidx_number SET (autovacuum_vacuum_scale_factor = 0.01);
ALTER TABLE hfj_spidx_quantity SET (autovacuum_vacuum_scale_factor = 0.01);
ALTER TABLE hfj_spidx_quantity_nrml SET (autovacuum_vacuum_scale_factor = 0.01);
ALTER TABLE hfj_spidx_string SET (autovacuum_vacuum_scale_factor = 0.01);
ALTER TABLE hfj_spidx_token SET (autovacuum_vacuum_scale_factor = 0.01);
ALTER TABLE hfj_spidx_uri SET (autovacuum_vacuum_scale_factor = 0.01);

-- PG by default tracks the most common 100 values.  But our hashes cover 100s of SPs and need greater depth.
-- Set stats depth to the max for hash_value columns, and 1000 for hash_identity (one per SP).
alter table hfj_res_link alter column src_path set statistics 10000;
alter table hfj_res_link alter column target_resource_id set statistics 10000;
alter table hfj_res_link alter column src_resource_id set statistics 10000;
alter table hfj_spidx_coords alter column hash_identity set statistics 1000;
alter table hfj_spidx_date alter column hash_identity set statistics 1000;
alter table hfj_spidx_number alter column hash_identity set statistics 1000;
alter table hfj_spidx_quantity alter column hash_identity set statistics 1000;
alter table hfj_spidx_quantity alter column hash_identity_and_units set statistics 10000;
alter table hfj_spidx_quantity alter column hash_identity_sys_units set statistics 10000;
alter table hfj_spidx_quantity_nrml alter column hash_identity set statistics 1000;
alter table hfj_spidx_quantity_nrml alter column hash_identity_and_units set statistics 10000;
alter table hfj_spidx_quantity_nrml alter column hash_identity_sys_units set statistics 10000;
alter table hfj_spidx_string alter column hash_identity set statistics 1000;
alter table hfj_spidx_string alter column hash_exact set statistics 10000;
alter table hfj_spidx_string alter column hash_norm_prefix set statistics 10000;
alter table hfj_spidx_token alter column hash_identity set statistics 1000;
alter table hfj_spidx_token alter column hash_sys set statistics 10000;
alter table hfj_spidx_token alter column hash_sys_and_value set statistics 10000;
alter table hfj_spidx_token alter column hash_value set statistics 10000;
alter table hfj_spidx_uri alter column hash_identity set statistics 1000;
alter table hfj_spidx_uri alter column hash_uri set statistics 10000;