qbit-edi/docs/ddl/edi-postgres.sql

CREATE TABLE edi_agreement (
   apply_test_documents BOOLEAN,
   component_separator VARCHAR(255),
   control_number_scope VARCHAR(255),
   create_date TIMESTAMP,
   decimal_mark VARCHAR(255),
   duplicate_window_days INTEGER,
   element_separator VARCHAR(255),
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   inbound_transport_id INTEGER,
   interchange_version VARCHAR(255),
   is_active BOOLEAN,
   line_ending VARCHAR(255),
   local_identity_id INTEGER,
   max_interchange_bytes BIGINT,
   modified_by VARCHAR(255),
   modify_date TIMESTAMP,
   notes VARCHAR(10000),
   on_duplicate VARCHAR(255),
   outbound_transport_id INTEGER,
   partner_id INTEGER,
   partner_identity_id INTEGER,
   release_character VARCHAR(255),
   repetition_separator VARCHAR(255),
   request_ta1 BOOLEAN,
   segment_terminator VARCHAR(255),
   send_ta1 VARCHAR(255),
   standard VARCHAR(255),
   syntax_identifier VARCHAR(255),
   usage_indicator VARCHAR(255),
   UNIQUE (local_identity_id, partner_identity_id, standard, usage_indicator)
);

CREATE TABLE edi_agreement_document (
   ack_due_minutes INTEGER,
   ack_expectation VARCHAR(255),
   ack_level VARCHAR(255),
   agreement_id INTEGER,
   batch_mode VARCHAR(255),
   batch_schedule VARCHAR(255),
   contains_phi BOOLEAN,
   create_date TIMESTAMP,
   direction VARCHAR(255),
   functional_id_code VARCHAR(255),
   guide_id INTEGER,
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   is_active BOOLEAN,
   map_id INTEGER,
   modified_by VARCHAR(255),
   modify_date TIMESTAMP,
   outbound_trigger VARCHAR(255),
   snip_level INTEGER,
   source_table_name VARCHAR(255),
   target_table_name VARCHAR(255),
   transaction_set VARCHAR(255),
   trigger_filter VARCHAR(10000),
   version VARCHAR(255),
   UNIQUE (agreement_id, direction, transaction_set, version)
);

CREATE TABLE edi_as2_mdn (
   create_date TIMESTAMP,
   delivered_at TIMESTAMP,
   delivery_attempts INTEGER,
   delivery_status VARCHAR(20),
   delivery_url VARCHAR(1000),
   direction VARCHAR(20),
   disposition VARCHAR(255),
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   inbox_id INTEGER,
   message_id VARCHAR(255),
   mic_algorithm VARCHAR(50),
   mic_value VARCHAR(255),
   modify_date TIMESTAMP,
   next_attempt_at TIMESTAMP,
   original_message_id VARCHAR(255),
   outbound_message_id INTEGER,
   partner_id INTEGER,
   payload_ref VARCHAR(255),
   received_at TIMESTAMP,
   signature_valid BOOLEAN,
   signed BOOLEAN,
   transport_id INTEGER
);

CREATE TABLE edi_certificate (
   alias VARCHAR(255),
   certificate_pem VARCHAR(10000),
   create_date TIMESTAMP,
   fingerprint_sha256 VARCHAR(255),
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   issuer VARCHAR(255),
   modified_by VARCHAR(255),
   modify_date TIMESTAMP,
   not_after TIMESTAMP,
   not_before TIMESTAMP,
   partner_id INTEGER,
   private_key_env_var VARCHAR(255),
   private_key_password_env_var VARCHAR(255),
   serial_number VARCHAR(255),
   status VARCHAR(20),
   subject VARCHAR(255),
   usage VARCHAR(255)
);

CREATE TABLE edi_code_list (
   create_date TIMESTAMP,
   element_id VARCHAR(255),
   external_code_set VARCHAR(255),
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   modified_by VARCHAR(255),
   modify_date TIMESTAMP,
   name VARCHAR(255),
   source VARCHAR(255)
);

CREATE TABLE edi_code_list_value (
   code VARCHAR(255),
   code_list_id INTEGER,
   create_date TIMESTAMP,
   description VARCHAR(10000),
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   is_active BOOLEAN,
   modified_by VARCHAR(255),
   modify_date TIMESTAMP,
   UNIQUE (code_list_id, code)
);

CREATE TABLE edi_control_number (
   agreement_id INTEGER,
   create_date TIMESTAMP,
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   last_value BIGINT,
   level VARCHAR(255),
   local_identity_id INTEGER,
   max_value BIGINT,
   min_value BIGINT,
   modified_by VARCHAR(255),
   modify_date TIMESTAMP,
   scope_key VARCHAR(255),
   wrap_allowed BOOLEAN,
   UNIQUE (scope_key, level)
);

CREATE TABLE edi_event (
   at TIMESTAMP,
   create_date TIMESTAMP,
   detail VARCHAR(10000),
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   inbox_id INTEGER,
   interchange_id INTEGER,
   modify_date TIMESTAMP,
   transaction_id INTEGER,
   type VARCHAR(255),
   user_name VARCHAR(255)
);

CREATE TABLE edi_exception (
   assigned_to_user_id VARCHAR(255),
   create_date TIMESTAMP,
   detail VARCHAR(10000),
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   inbox_id INTEGER,
   interchange_id INTEGER,
   modified_by VARCHAR(255),
   modify_date TIMESTAMP,
   occurrences INTEGER,
   outbound_message_id INTEGER,
   partner_id INTEGER,
   resolution VARCHAR(10000),
   resolved_at TIMESTAMP,
   resolved_by VARCHAR(255),
   status VARCHAR(255),
   summary VARCHAR(255),
   transaction_id INTEGER,
   type VARCHAR(255)
);

CREATE TABLE edi_functional_group (
   ack_due_at TIMESTAMP,
   ack_received_at TIMESTAMP,
   ack_status VARCHAR(255),
   ack_transaction_id INTEGER,
   control_number VARCHAR(255),
   create_date TIMESTAMP,
   functional_id_code VARCHAR(255),
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   inbound_position INTEGER,
   interchange_id INTEGER,
   modify_date TIMESTAMP,
   sender_id VARCHAR(255),
   transaction_count INTEGER,
   version VARCHAR(255)
);

CREATE TABLE edi_guide (
   compiled_checksum VARCHAR(255),
   create_date TIMESTAMP,
   description VARCHAR(10000),
   functional_id_code VARCHAR(255),
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   modified_by VARCHAR(255),
   modify_date TIMESTAMP,
   name VARCHAR(255),
   parent_guide_id INTEGER,
   partner_id INTEGER,
   revision INTEGER,
   source VARCHAR(255),
   standard VARCHAR(255),
   status VARCHAR(255),
   transaction_set VARCHAR(255),
   version VARCHAR(255),
   UNIQUE (name, revision)
);

CREATE TABLE edi_guide_element (
   code_list_id INTEGER,
   component_position INTEGER,
   create_date TIMESTAMP,
   data_type VARCHAR(255),
   element_id VARCHAR(255),
   fixed_value VARCHAR(255),
   guide_segment_id INTEGER,
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   max_length INTEGER,
   min_length INTEGER,
   modified_by VARCHAR(255),
   modify_date TIMESTAMP,
   name VARCHAR(255),
   position INTEGER,
   repeat_max INTEGER,
   situational_rule_id INTEGER,
   usage VARCHAR(255)
);

CREATE TABLE edi_guide_rule (
   ak3_code VARCHAR(255),
   ak4_code VARCHAR(255),
   create_date TIMESTAMP,
   expression VARCHAR(10000),
   guide_id INTEGER,
   guide_segment_id INTEGER,
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   ik3_code VARCHAR(255),
   ik4_code VARCHAR(255),
   message VARCHAR(10000),
   modified_by VARCHAR(255),
   modify_date TIMESTAMP,
   positions VARCHAR(255),
   severity VARCHAR(255),
   snip_level INTEGER,
   type VARCHAR(255)
);

CREATE TABLE edi_guide_segment (
   create_date TIMESTAMP,
   guide_id INTEGER,
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   loop_id VARCHAR(255),
   loop_max_repeat INTEGER,
   max_use INTEGER,
   min_use INTEGER,
   modified_by VARCHAR(255),
   modify_date TIMESTAMP,
   name VARCHAR(255),
   notes VARCHAR(10000),
   parent_loop_id VARCHAR(255),
   position VARCHAR(255),
   segment_id VARCHAR(255),
   sequence INTEGER,
   situational_rule_id INTEGER,
   usage VARCHAR(255)
);

CREATE TABLE edi_identity (
   application_code VARCHAR(255),
   create_date TIMESTAMP,
   description VARCHAR(10000),
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   interchange_id VARCHAR(255),
   interchange_qualifier VARCHAR(255),
   is_active BOOLEAN,
   modified_by VARCHAR(255),
   modify_date TIMESTAMP,
   partner_id INTEGER,
   standard VARCHAR(255),
   UNIQUE (standard, interchange_qualifier, interchange_id, application_code)
);

CREATE TABLE edi_inbox (
   attempts INTEGER,
   claimed_at TIMESTAMP,
   create_date TIMESTAMP,
   external_id VARCHAR(255),
   file_name VARCHAR(255),
   gateway_user_id VARCHAR(255),
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   last_error VARCHAR(255),
   modify_date TIMESTAMP,
   next_attempt_at TIMESTAMP,
   partner_id INTEGER,
   payload_ref VARCHAR(255),
   payload_sha256 VARCHAR(255),
   processed_at TIMESTAMP,
   received_at TIMESTAMP,
   remote_address VARCHAR(255),
   reprocess BOOLEAN,
   size_bytes BIGINT,
   status VARCHAR(255),
   transport_id INTEGER,
   transport_type VARCHAR(255),
   use_current_revisions BOOLEAN,
   username VARCHAR(255),
   UNIQUE (transport_type, external_id)
);

CREATE TABLE edi_interchange (
   agreement_id INTEGER,
   contains_phi BOOLEAN,
   control_number VARCHAR(255),
   create_date TIMESTAMP,
   direction VARCHAR(255),
   group_count INTEGER,
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   inbound_position INTEGER,
   inbox_id INTEGER,
   interchange_date TIMESTAMP,
   interchange_version VARCHAR(255),
   modify_date TIMESTAMP,
   original_interchange_id INTEGER,
   outbound_message_id INTEGER,
   partner_id INTEGER,
   payload_ref VARCHAR(255),
   payload_sha256 VARCHAR(255),
   receiver_id VARCHAR(255),
   receiver_qualifier VARCHAR(255),
   sender_id VARCHAR(255),
   sender_qualifier VARCHAR(255),
   size_bytes BIGINT,
   standard VARCHAR(255),
   status VARCHAR(255),
   ta1_status VARCHAR(255),
   transport_id INTEGER,
   usage_indicator VARCHAR(255)
);

CREATE TABLE edi_map (
   business_key_path VARCHAR(255),
   business_key_type VARCHAR(255),
   create_date TIMESTAMP,
   description VARCHAR(10000),
   direction VARCHAR(255),
   guide_id INTEGER,
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   match_key_fields VARCHAR(255),
   modify_date TIMESTAMP,
   name VARCHAR(255),
   on_match VARCHAR(255),
   parent_map_id INTEGER,
   partner_id INTEGER,
   revision INTEGER,
   secondary_key_path VARCHAR(255),
   standard VARCHAR(255),
   status VARCHAR(255),
   table_name VARCHAR(255),
   transaction_set VARCHAR(255),
   version VARCHAR(255)
);

CREATE TABLE edi_map_rule (
   condition VARCHAR(10000),
   create_date TIMESTAMP,
   default_value VARCHAR(255),
   edi_path VARCHAR(255),
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   map_id INTEGER,
   modify_date TIMESTAMP,
   notes VARCHAR(10000),
   qqq_path VARCHAR(255),
   required BOOLEAN,
   sequence INTEGER,
   transform VARCHAR(255),
   transform_args VARCHAR(10000)
);

CREATE TABLE edi_map_sample (
   create_date TIMESTAMP,
   expected_result_json VARCHAR(10000),
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   last_preview_at TIMESTAMP,
   last_preview_status VARCHAR(255),
   map_id INTEGER,
   modify_date TIMESTAMP,
   name VARCHAR(255),
   payload_ref VARCHAR(255)
);

CREATE TABLE edi_outbound_message (
   ack_for_group_id INTEGER,
   ack_for_interchange_id INTEGER,
   agreement_id INTEGER,
   attempts INTEGER,
   claimed_at TIMESTAMP,
   create_date TIMESTAMP,
   delivered_at TIMESTAMP,
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   interchange_id INTEGER,
   last_error VARCHAR(255),
   modify_date TIMESTAMP,
   next_attempt_at TIMESTAMP,
   outbound_request_id INTEGER,
   partner_id INTEGER,
   payload_ref VARCHAR(255),
   payload_sha256 VARCHAR(255),
   sent_at TIMESTAMP,
   size_bytes BIGINT,
   status VARCHAR(255),
   transport_id INTEGER,
   transport_message_id VARCHAR(255),
   transport_mic VARCHAR(255)
);

CREATE TABLE edi_outbound_request (
   agreement_document_id INTEGER,
   attempts INTEGER,
   claimed_at TIMESTAMP,
   create_date TIMESTAMP,
   event_id VARCHAR(255),
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   last_error VARCHAR(255),
   modify_date TIMESTAMP,
   next_attempt_at TIMESTAMP,
   source_record_id VARCHAR(255),
   source_table_name VARCHAR(255),
   status VARCHAR(255),
   UNIQUE (agreement_document_id, event_id)
);

CREATE TABLE edi_partner (
   code VARCHAR(255),
   contact_email VARCHAR(255),
   contact_name VARCHAR(255),
   contact_phone VARCHAR(255),
   contains_phi BOOLEAN,
   create_date TIMESTAMP,
   default_timezone VARCHAR(255),
   gateway_username VARCHAR(255),
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   modified_by VARCHAR(255),
   modify_date TIMESTAMP,
   name VARCHAR(255),
   notes VARCHAR(10000),
   status VARCHAR(255),
   UNIQUE (name),
   UNIQUE (code)
);

CREATE TABLE edi_payload (
   ciphertext BYTEA,
   create_date TIMESTAMP,
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   key_id VARCHAR(255),
   kind VARCHAR(255),
   modify_date TIMESTAMP,
   sha256 VARCHAR(255),
   size_bytes BIGINT
);

CREATE TABLE edi_transaction (
   ack_status VARCHAR(255),
   agreement_document_id INTEGER,
   business_key VARCHAR(255),
   business_key_type VARCHAR(255),
   contains_phi BOOLEAN,
   control_number VARCHAR(255),
   create_date TIMESTAMP,
   direction VARCHAR(255),
   error_count INTEGER,
   functional_group_id INTEGER,
   guide_id INTEGER,
   guide_revision INTEGER,
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   inbound_position INTEGER,
   interchange_id INTEGER,
   map_id INTEGER,
   map_revision INTEGER,
   modify_date TIMESTAMP,
   partner_id INTEGER,
   phi_business_key VARCHAR(255),
   secondary_key VARCHAR(255),
   segment_count INTEGER,
   status VARCHAR(255),
   target_record_id VARCHAR(255),
   target_table_name VARCHAR(255),
   transaction_set VARCHAR(255),
   version VARCHAR(255),
   warning_count INTEGER
);

CREATE TABLE edi_transaction_error (
   ack_code VARCHAR(255),
   component_position INTEGER,
   create_date TIMESTAMP,
   element_position INTEGER,
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   loop_id VARCHAR(255),
   message VARCHAR(10000),
   modify_date TIMESTAMP,
   segment_id VARCHAR(255),
   segment_position INTEGER,
   severity VARCHAR(255),
   snip_level INTEGER,
   transaction_id INTEGER,
   value_excerpt VARCHAR(255)
);

CREATE TABLE edi_transport (
   as2_local_id VARCHAR(255),
   as2_partner_id VARCHAR(255),
   as2_subject VARCHAR(255),
   as2_url VARCHAR(255),
   compress BOOLEAN,
   create_date TIMESTAMP,
   direction VARCHAR(255),
   email_attachment_name VARCHAR(255),
   email_from_alias VARCHAR(255),
   email_provider_name VARCHAR(255),
   email_to VARCHAR(255),
   encrypt_algorithm VARCHAR(255),
   gateway_endpoint_name VARCHAR(255),
   http_auth_password_env_var VARCHAR(255),
   http_auth_user_env_var VARCHAR(255),
   https_auth_type VARCHAR(255),
   https_headers_json VARCHAR(10000),
   https_inbound_auth VARCHAR(255),
   https_secret_env_var VARCHAR(255),
   https_url VARCHAR(255),
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   is_active BOOLEAN,
   is_van BOOLEAN,
   local_certificate_id INTEGER,
   max_attempts INTEGER,
   mdn_mode VARCHAR(255),
   mdn_signed BOOLEAN,
   modified_by VARCHAR(255),
   modify_date TIMESTAMP,
   name VARCHAR(255),
   notes VARCHAR(10000),
   partner_certificate_id INTEGER,
   partner_id INTEGER,
   partner_tls_certificate_id INTEGER,
   sftp_after_pickup VARCHAR(255),
   sftp_archive_path VARCHAR(255),
   sftp_file_pattern VARCHAR(255),
   sftp_host VARCHAR(255),
   sftp_host_key_fingerprint VARCHAR(255),
   sftp_inbound_path VARCHAR(255),
   sftp_outbound_path VARCHAR(255),
   sftp_password_env_var VARCHAR(255),
   sftp_poll_cron VARCHAR(255),
   sftp_port INTEGER,
   sftp_private_key_env_var VARCHAR(255),
   sftp_temp_suffix VARCHAR(255),
   sftp_username VARCHAR(255),
   sign_algorithm VARCHAR(255),
   type VARCHAR(255),
   van_name VARCHAR(255),
   weak_crypto BOOLEAN,
   UNIQUE (name)
);