Subject Area : 210 - Audit Trail
Subject Area Name Definition
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.
ER Diagram Name Physical Display Level
Logical Comment
Physical Column
Physical Name Entity/Table Name Logical Only Do Not Generate Definition
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.
Physical Name Attribute/Column Name Definition Physical Data Type
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
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
Physical Name Attribute/Column Name Definition Physical Data Type
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)
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.
Physical Name Attribute/Column Name Definition Physical Data Type
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
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.
Physical Name Attribute/Column Name Definition Physical Data Type
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
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.
Physical Name Attribute/Column Name Definition Physical Data Type
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)
audit_prop_chg Audit Property Change false A data change record indicating the Inserted or Updated value. Deleted Values are not recorded here.
Physical Name Attribute/Column Name Definition Physical Data Type
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)
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.
Physical Name Attribute/Column Name Definition Physical Data Type
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
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.
Physical Name Attribute/Column Name Definition Physical Data Type
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
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.
Physical Name Attribute/Column Name Definition Physical Data Type
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
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.
Physical Name Attribute/Column Name Definition Physical Data Type
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
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.

Physical Name Attribute/Column Name Definition Physical Data Type
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)
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
Physical Name Attribute/Column Name Definition Physical Data Type
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)