Subject Area : 210 - Audit Trail |
# | Name | Definition | ER Diagram | Entity/Table | ||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Total | ||||||||||||||||||||||||||||||||||||||||||||||||||
. | 210 - Audit Trail | Tables to support an audit trail of changes to the database and also audit messages formed with a template message and substitution arguments. | Details.. | Details.. |
# | Name | Physical Display Level | ||||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Total | 2 | |||||||||||||||||||||||||||||||||||||||||||||||||
. | Logical | Comment | ||||||||||||||||||||||||||||||||||||||||||||||||
. | Physical | Column |
# | Physical Name | Name | Logical Only | Do Not Generate | Definition | Attribute/Column | ||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Total | 12 | |||||||||||||||||||||||||||||||||||||||||||||||||
. | app_user | Application User | false | A user authorized to use the FootPrints system. The user is either a Customer User or an Agent User. The application user holds the minimum information needed to identify the user and associate the user with a user profile, where user settings are stored. A contact is not necessarily an application user. An application user entry is required in order for the user to access any online functions. | Details.. | |||||||||||||||||||||||||||||||||||||||||||||
. | audit_event_type | Audit Event Type | false | A discriminator indicating the type of Audit Log Entry. Values include Audit Log Note, Audit Log Data Change, and Audit Log Data Version | Details.. | |||||||||||||||||||||||||||||||||||||||||||||
. | audit_field_cfg | Audit Field Configuration | false | false | Configuration values for the Audit that determine if a dynamic item should be audited. If only Dynamic Items are supported, then auditing may be something that cannot be turned off? Configuration Setting Values are used for the audit indicator. Within the Java code, automatic logging of classes can be turned on. | Details.. | ||||||||||||||||||||||||||||||||||||||||||||
. | audit_log | Audit Log | false | The main Audit Log table which uses a serial key to establish the order of Audit Trail entries as committed to the system. It has subordinate notes and data values which are considered unordered within the Audit Log. It is possible and desirable that multiple Audit Log entries would have the same transaction id. | Details.. | |||||||||||||||||||||||||||||||||||||||||||||
. | audit_msg_tmpl | Audit Message Template | false | A template message that is generated from the application with string substitution parameters. Only string variables are supported, because the eventual usage is just string presentation, and localization of the parameters is not supported. | Details.. | |||||||||||||||||||||||||||||||||||||||||||||
. | audit_prop_chg | Audit Property Change | false | A data change record indicating the Inserted or Updated value. Deleted Values are not recorded here. | Details.. | |||||||||||||||||||||||||||||||||||||||||||||
. | audit_prop_dim | Audit Property Dimension | false | A dimension table that holds every combination of Object Name, Property Name, Insert Update Delete Code, etc. that has been used in the Audit Trail. The purpose of this data design is to compress the repeated information in the Audit Trail, reducing data and speeding up indexed queries. | Details.. | |||||||||||||||||||||||||||||||||||||||||||||
. | audit_rule_name | Audit Rule Name | false | This is a derived table of current and historical Rule Names that is used to make a permanant record of the rule name for the Audit trail. The replication will be done on publish and will avoid locking the definition tables during Audit History storage. The name of the Rule is replicated from the Rule Definition to avoid problems with query complexity, deletion and modification of Rule names, and locking. If a rule is deleted on publish, the delete date will be filled in. This table is cached in memory so the Audit Function does not have to repetitively retrieve it. | Details.. | |||||||||||||||||||||||||||||||||||||||||||||
. | audit_txn | Audit Transaction | false | A record of the Principal and create time of the start of the commit time of a logical transaction. In many cases multiple Audit Log entries will use the same transaction. | Details.. | |||||||||||||||||||||||||||||||||||||||||||||
. | item_field_defn | Item Field Definition | true | true | Item Field Definition is an association between the Item Definition entity and the Field Definition entity. This association contains a distinct value for each of the intersection points between the two entities. | Details.. | ||||||||||||||||||||||||||||||||||||||||||||
. | sys_component | System Component | false |
A lookup table for System Component. System Component is used to identify an area of the system. The primary use is when a component is updating system information with a transaction that is not initiated from a customer or agent. |
Details.. | |||||||||||||||||||||||||||||||||||||||||||||
. | val_type_cd | Value Type Code | false |
A lookup table for the data value type discriminator. Many tables share this discriminator for name value pair datatypes. Values include: B Boolean D Timestamp - Date + Time I Integer L Long R Real Number S String T Text - long string |
Details.. |
# | Physical Name | Name | Definition | Physical Data Type | ||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Total | 8 | |||||||||||||||||||||||||||||||||||||||||||||||||
. | pwd | Password | This space intentionally left blank. | varchar(255) | ||||||||||||||||||||||||||||||||||||||||||||||
. | user_name | User Name | The name of the Application User. | nvarchar(4000) | ||||||||||||||||||||||||||||||||||||||||||||||
. | cust_login_type_id | Customer Login Type Id | A Foreign Key to the Customer Login Type table. | int | ||||||||||||||||||||||||||||||||||||||||||||||
. | app_user_id | Application User Id | The automatically generated primary key of the app_user table. | bigint | ||||||||||||||||||||||||||||||||||||||||||||||
. | lic_type_id | License Type Id | A Foreign Key to the License Type table. | bigint | ||||||||||||||||||||||||||||||||||||||||||||||
. | user_login | User Login | The login name used to access the Application. This may not be filled in if external application security is used. | nvarchar(4000) | ||||||||||||||||||||||||||||||||||||||||||||||
. | email_addr | Email Address | An email address for the Application User. | nvarchar(4000) | ||||||||||||||||||||||||||||||||||||||||||||||
. | delete_date | Delete Date | The value of a Application User, stored as a date. | datetime |
# | Physical Name | Name | Definition | Physical Data Type | ||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Total | 2 | |||||||||||||||||||||||||||||||||||||||||||||||||
. | audit_event_type_id | Audit Event Type Id | The unique identifier for an instance of Audit Event Type. This identifier must be kept in sync with programming code that uses an enumeration or fixed constant to identify particular values. | BIGINT | ||||||||||||||||||||||||||||||||||||||||||||||
. | audit_event_type_name | Audit Event Type Name | The name of the Audit Event Type. | varchar(255) |
# | Physical Name | Name | Definition | Physical Data Type | ||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Total | 3 | |||||||||||||||||||||||||||||||||||||||||||||||||
. | audit_field_cfg_id | Audit Field Configuration Id | The automatically generated primary key of the audit_field_cfg table. | bigint | ||||||||||||||||||||||||||||||||||||||||||||||
. | is_audited | Is Audited | Indicates if changes to the field should be recorded in the Audit Trail. | bit | ||||||||||||||||||||||||||||||||||||||||||||||
. | item_field_defn_id | Item Field Definition Id | A Foreign Key to the Definition table for an instance of type Definition. | bigint |
# | Physical Name | Name | Definition | Physical Data Type | ||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Total | 18 | |||||||||||||||||||||||||||||||||||||||||||||||||
. | audit_log_id | Audit Log Id | The automatically generated primary key of the audit_log table. | bigint | ||||||||||||||||||||||||||||||||||||||||||||||
. | audit_event_type_id | Audit Event Type Id | The unique identifier for an instance of Audit Event Type. This identifier must be kept in sync with programming code that uses an enumeration or fixed constant to identify particular values. | BIGINT | ||||||||||||||||||||||||||||||||||||||||||||||
. | audit_txn_id | Audit Transaction Id | The automatically generated primary key of the audit_txn table. | bigint | ||||||||||||||||||||||||||||||||||||||||||||||
. | insert_update_delete_cd | Insert Update Delete Code | Indicates I-Insert, U-Update, D-Delete database action. | varchar(255) | ||||||||||||||||||||||||||||||||||||||||||||||
. | string_parm_1 | String Parameter 1 | A substitution parameter for the referenced formatted string. | nvarchar(max) | ||||||||||||||||||||||||||||||||||||||||||||||
. | string_parm_2 | String Parameter 2 | A substitution parameter for the referenced formatted string. | nvarchar(4000) | ||||||||||||||||||||||||||||||||||||||||||||||
. | string_parm_3 | String Parameter 3 | A substitution parameter for the referenced formatted string. | nvarchar(4000) | ||||||||||||||||||||||||||||||||||||||||||||||
. | string_parm_4 | String Parameter 4 | A substitution parameter for the referenced formatted string. | nvarchar(4000) | ||||||||||||||||||||||||||||||||||||||||||||||
. | string_parm_5 | String Parameter 5 | A substitution parameter for the referenced formatted string. | nvarchar(4000) | ||||||||||||||||||||||||||||||||||||||||||||||
. | string_parm_6 | String Parameter 6 | A substitution parameter for the referenced formatted string. | nvarchar(4000) | ||||||||||||||||||||||||||||||||||||||||||||||
. | audit_prop_dim_id | Audit Property Dimension Id | A Foreign Key to the Audit Property Dimension table. | bigint | ||||||||||||||||||||||||||||||||||||||||||||||
. | domain_obj_id | Domain Object Id | The numeric Primary Key of the Object that is autologged. | bigint | ||||||||||||||||||||||||||||||||||||||||||||||
. | string_parm_7 | String Parameter 7 | A substitution parameter for the referenced formatted string. | nvarchar(4000) | ||||||||||||||||||||||||||||||||||||||||||||||
. | string_parm_8 | String Parameter 8 | A substitution parameter for the referenced formatted string. | nvarchar(4000) | ||||||||||||||||||||||||||||||||||||||||||||||
. | audit_msg_tmpl_id | Audit Message Template Id | A Foreign Key to the Audit Message Template table. | bigint | ||||||||||||||||||||||||||||||||||||||||||||||
. | create_date | Create Date | The transaction date of the Audit Log entry. This date may not be in strict order with the Audit Log id in the event that features are used to continue a previous Audit Log transaction. | datetime | ||||||||||||||||||||||||||||||||||||||||||||||
. | raw_msg | Raw Message | The value of a Audit Log, stored as a long text object. | nvarchar(max) | ||||||||||||||||||||||||||||||||||||||||||||||
. | string_parm_7_int | String Parameter 7 Int | This is a computed column. The integer version of parameter 7. It is only indexed when parameter 8 is '_USER_SESSION_'. It greatly increases the User browse screens ability to compute the last_login. | int |
# | Physical Name | Name | Definition | Physical Data Type | ||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Total | 3 | |||||||||||||||||||||||||||||||||||||||||||||||||
. | audit_msg_tmpl_id | Audit Message Template Id | The automatically generated primary key of the audit_msg_tmpl table. | bigint | ||||||||||||||||||||||||||||||||||||||||||||||
. | audit_msg_tmpl_key | Audit Message Template Key | A search key for the Audit Message Template, composed of letters and numbers. | varchar(255) | ||||||||||||||||||||||||||||||||||||||||||||||
. | audit_msg_tmpl | Audit Message Template | The template message consisting of text and substitution variables. | varchar(4000) |
# | Physical Name | Name | Definition | Physical Data Type | ||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Total | 14 | |||||||||||||||||||||||||||||||||||||||||||||||||
. | audit_prop_chg_id | Audit Property Change Id | The automatically generated primary key of the audit_prop_chg table. | bigint | ||||||||||||||||||||||||||||||||||||||||||||||
. | audit_prop_dim_id | Audit Property Dimension Id | A Foreign Key to the Audit Property Dimension table. | bigint | ||||||||||||||||||||||||||||||||||||||||||||||
. | new_string_val | New String Value | The new value of an inserted or updated property, for a property of type string. | nvarchar(4000) | ||||||||||||||||||||||||||||||||||||||||||||||
. | new_text_val | New Text Value | The new value of an inserted or updated property, for a property of type text. | nvarchar(max) | ||||||||||||||||||||||||||||||||||||||||||||||
. | new_date_val | New Date Value | The new value of an inserted or updated property, for a property of type date. | datetime | ||||||||||||||||||||||||||||||||||||||||||||||
. | new_real_val | New Real Value | The new value of an inserted or updated property, for a property of type real. | numeric(38,20) | ||||||||||||||||||||||||||||||||||||||||||||||
. | new_long_val | New Long Value | The new value of an inserted or updated property, for a property of type long integer. This may be a key, in which case the Convenience Value might also be stored. | bigint | ||||||||||||||||||||||||||||||||||||||||||||||
. | audit_log_id | Audit Log Id | A Foreign Key to the Audit Log table. | bigint | ||||||||||||||||||||||||||||||||||||||||||||||
. | old_string_val | Old String Value | The old value of an inserted or updated property, for a property of type string. | nvarchar(4000) | ||||||||||||||||||||||||||||||||||||||||||||||
. | old_text_val | Old Text Value | The old value of an inserted or updated property, for a property of type text. | nvarchar(max) | ||||||||||||||||||||||||||||||||||||||||||||||
. | old_date_val | Old Date Value | The old value of an inserted or updated property, for a property of type date. | datetime | ||||||||||||||||||||||||||||||||||||||||||||||
. | old_real_val | Old Real Value | The old value of an inserted or updated property, for a property of type real. | numeric(38,20) | ||||||||||||||||||||||||||||||||||||||||||||||
. | old_long_val | Old Long Value | The old value of an inserted or updated property, for a property of type long integer. This may be a key, in which case the Convenience Value might also be stored. | bigint | ||||||||||||||||||||||||||||||||||||||||||||||
. | val_type_cd | Value Type Code | The data type for a Value Type Code. The choices are 'S' string, 'D' date, 'T' text, 'I' integer. | char(1) |
# | Physical Name | Name | Definition | Physical Data Type | ||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Total | 9 | |||||||||||||||||||||||||||||||||||||||||||||||||
. | audit_prop_dim_id | Audit Property Dimension Id | The automatically generated primary key of the audit_prop_dim table. | bigint | ||||||||||||||||||||||||||||||||||||||||||||||
. | prop_mbr_name | Property Member Name | The name of the Object Property Name. | varchar(255) | ||||||||||||||||||||||||||||||||||||||||||||||
. | val_type_cd | Value Type Code | The data type for a Value Type Code. The choices are 'S' string, 'D' date, 'T' text, 'I' integer. | char(1) | ||||||||||||||||||||||||||||||||||||||||||||||
. | audit_class_name | Audit Class Name | The Java Class name of the object. | varchar(255) | ||||||||||||||||||||||||||||||||||||||||||||||
. | prop_class_name | Property Class Name | The Java Class name of the object. | varchar(255) | ||||||||||||||||||||||||||||||||||||||||||||||
. | coll_class_name | Collection Class Name | The Java Class name of the object. | varchar(255) | ||||||||||||||||||||||||||||||||||||||||||||||
. | is_audited | Is Audited | Indicates if Is Audited. | bit | ||||||||||||||||||||||||||||||||||||||||||||||
. | item_defn_id | Item Definition Id | A Foreign Key to the Definition table. | bigint | ||||||||||||||||||||||||||||||||||||||||||||||
. | item_field_defn_id | Item Field Definition Id | A Foreign Key to the Definition table for an instance of type Definition. | bigint |
# | Physical Name | Name | Definition | Physical Data Type | ||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Total | 3 | |||||||||||||||||||||||||||||||||||||||||||||||||
. | audit_rule_name_id | Audit Rule Name Id | The primary key is the original rule definition id. It will not change as the rule is renamed. If the rule is deleted, the deleted date will be filled in, but the rule name will still be known in this table. | bigint | ||||||||||||||||||||||||||||||||||||||||||||||
. | rule_name | Rule Name | The name of the Rule is replicated from the Rule Definition to avoid problems with query complexity, deletion and modification of Rule names, and locking. | varchar(255) | ||||||||||||||||||||||||||||||||||||||||||||||
. | deleted_date | Deleted Date | The date that the Audit Rule Name was published as deleted. | datetime |
# | Physical Name | Name | Definition | Physical Data Type | ||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Total | 5 | |||||||||||||||||||||||||||||||||||||||||||||||||
. | audit_txn_id | Audit Transaction Id | The automatically generated primary key of the audit_txn table. | bigint | ||||||||||||||||||||||||||||||||||||||||||||||
. | txn_start_date | Transaction Start Date | The transaction date of the Audit Log entry. This date may not be in strict order with the Audit Log id in the event that features are used to continue a previous Audit Log transaction. | datetime | ||||||||||||||||||||||||||||||||||||||||||||||
. | app_user_id | Application User Id | A Foreign Key to the Application User table. The audit trail transaction may or may not be associated with a customer or agent user. If this column is null, then a system component should be identified. | bigint | ||||||||||||||||||||||||||||||||||||||||||||||
. | sys_component_id | System Component Id |
This is a foreign key to the system component table. The audit trail transaction may or may not be associated with a customer or agent user. The system component will be set if there is no application user identified. It identifies the source of the transaction. Values include SYSTEM, WEB_SERVICES, ASSET_MANAGEMENT, CALENDAR, IMPORTING, etc. All identifiers are negative, so that they can be distinguished from userids in case they are both stored in the same dynamic table CreatedBy, and UpdatedBy fields. We are going to try first normal form normalization here and see if it works. |
int | ||||||||||||||||||||||||||||||||||||||||||||||
. | audit_rule_name_id | Audit Rule Name Id | The primary key is the original rule definition id. It will not change as the rule is renamed. If the rule is deleted, the deleted date will be filled in, but the rule name will still be known in this table. | bigint |
# | Physical Name | Name | Definition | Physical Data Type | ||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Total | 3 | |||||||||||||||||||||||||||||||||||||||||||||||||
. | item_field_defn_id | Item Field Definition Id | A Foreign Key to the Definition table for an instance of type Definition. | bigint | ||||||||||||||||||||||||||||||||||||||||||||||
. | item_defn_id | Item Definition Id | A Foreign Key to the Definition table for an instance of type Definition. | bigint | ||||||||||||||||||||||||||||||||||||||||||||||
. | field_defn_id | Field Definition Id | A Foreign Key to the Definition table. | bigint |
# | Physical Name | Name | Definition | Physical Data Type | ||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Total | 3 | |||||||||||||||||||||||||||||||||||||||||||||||||
. | sys_component_id | System Component Id | The unique identifier for an instance of System Component. This identifier must be kept in sync with programming code that uses an enumeration or fixed constant to identify particular values. | int | ||||||||||||||||||||||||||||||||||||||||||||||
. | sys_component_name | System Component Name | The unique string identifier for an instance of System Component. This identifier must be kept in sync with programming code that uses an enumeration constant to identify particular values. A scan program is generally used to populate this table. | varchar(255) | ||||||||||||||||||||||||||||||||||||||||||||||
. | sys_component_descr | System Component Description | A descriptive name of the System Component. This descriptive name may be localized. | varchar(512) |
# | Physical Name | Name | Definition | Physical Data Type | ||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Total | 2 | |||||||||||||||||||||||||||||||||||||||||||||||||
. | val_type_cd | Value Type Code | The data type for a Value Type Code. The choices are 'S' string, 'D' date, 'T' text, 'I' integer. | char(1) | ||||||||||||||||||||||||||||||||||||||||||||||
. | val_type_name | Value Type Name | The name of the Value Type Code. | varchar(255) |