System and Audit Tables

[ 1 Access Control Rule Table ] [ 2 Access Control Rule System Mapping Table ] [ 3 Audit Table ] [ 4 External System Table ] [ 5 External System Authentication Table ] [ 6 External System Role Table ] [ 7 External System Role Mapping Table ] [ 8 Security Table ]

Access Control Rule Table

This table contains all the rules of ACLs (access control rules) either from security or project team within CAM.

Query the table by: select * from access_control_rule limit 100

^Use the other Query type

Column	Description	Keys (Primary/ Foreign)

Column	Description	Keys (Primary/ Foreign)
id	The internal CAM id for the access control rule system mapping table. Is a mandatory field.	PK
object_entity_id	Id of the object entity (doc, folder etc)	FK on the object’s table eg document
group_id	Id of the group	FK on groups
grant_access	Allows to grant access to the user or group
role_id	Id of the role	FK on role
is_implicit	Is an implicit security role rule?
project_team_id	Id of the project team	FK on project_team
is_deleted	Is the record deleted?
extSystemInstanceId	The external system id (iManage, ND etc)	FK on externalsystem
createdDate	Displays the audit creation date. Is mandatory.
createdByUserId	The user id of the person who created the record.	FK on user
camCreatedDate	The date the record is created in CAM
camCreatedBy	The user who created in CAM	FK on user

Access Control Rule System Mapping Table

This table contains all the mappings of ACLs (access control rules) either from security or project team within CAM.

Query the table by: select * from access_control_rule_system_mapping limit 100

^Use the other Query type

Column	Description	Keys (Primary/ Foreign)

Column	Description	Keys (Primary/ Foreign)
id	The internal CAM id for the access control rule system mapping table. Is a mandatory field.	PK
access_control_rule_id	The id of the access control rule.	FK on access control rule
is_deleted	Is the record deleted?
extSystemInstanceId	The external system id (iManage, ND etc)	FK on externalsystem
createdDate	Displays the audit creation date. Is mandatory.
createdByUserId	The user id of the person who created the record.	FK on user
camCreatedDate	The date the record is created in CAM
camCreatedBy	The user who created in CAM	FK on user

Audit Table

This table contains all the audit information within CAM.

Query the table by: select * from audit limit 100

^Use the audit Query type

Column	Description	Keys (Primary/ Foreign)

Column	Description	Keys (Primary/ Foreign)
id	The internal CAM id for the audit table. Is a mandatory field.	PK
docId	It is a mandatory field if a document is set.	FK on document table
folderId	It is a mandatory field if a folder is set.	FK on folder table
userId	Displays the user Id associated to the user. Displays the user Id associated to the user.	FK on user table
extDocId	The external document id (filled if a document was added/changed/updated/deleted)
extFolder	The external folder id (filled if a folder/workspace was added/changed/updated/deleted)
extUserId	The external userid (filled if a user or security was added/changed/updated/deleted)
objectType	Displays the object type.
actionCodeId	The id of the particular action done on the object. Is a mandatory field.
actionCodeName	The name of the particular action done on the object. Is a mandatory field.
logType	The type of the log.
applicationName	Name of the CAM application module making the modification/addition/deletion.
ipAddress	If the ip address is filled from an external system or user.
clientCode	If a client is changed, this is the client code.
MatterCode	If a matter is changed, this is the matter code.
actionUserName	If a action has a specific user name passed, this will be filled.
ticketId
comments	Any comments left on the audit.
docNumber	If a document was added/changed/updated/deleted, the docnumber is filled.
hasJournal	Set to 1 if a journal is with the object.
version	Version of the object.
metadata	Any metadata added/changed/updated/deleted
createdDate	Displays the audit creation date. Is mandatory.
createdByUserId	The user id of the person who created the record.
camCreatedDate	The date the record is created in CAM
camCreatedBy	The user who created in CAM
extSystemId	The external system id (iManage, ND etc)	FK on externalsystem
subscriberId	Displays the tenant subscriber Id.
runId	The ID of the job run
actionDate	Date the action on the record was taken
extContainerId	If the configured External System is iManage this column displays the database name, followed by the folder number (e.g. demo!1234), in case of NetDocuments, this displays the 12 digit folder number (e.g. 4816-5122-8805). It is a mandatory field.	FK on externalsystem
extContainerName	If the configured External System is iManage, this column displays the database name, followed by the document number and version (e.g. demo!463.1), in case of NetDocuments, this displays the document name.	FK on externalsystem

External System Table

This table contains all the external system information within CAM.

Query the table by: select * from external_system_nt limit 100

^Use the other Query type

Column	Description	Keys (Primary/ Foreign)

Column	Description	Keys (Primary/ Foreign)
id	The internal CAM id of each external system.	PK
name	Name of the external system. E.g. iManage, NetDocuments, O365 etc
nickname	A nickname. Typically most people will set this the same as the name.
description	Description of the external system.
type	The system type. Can be 0,1,2
base_url	The url of the cloud based system.
icon_path	Path of where the icons are stored for the system. Is blank if not set (uses CAM default icons).
libraries	If a particular library within the API is used. Most commonly is blank.
active	Is actively used or not. 0 is active, 1 is not.
is_deleted	Marks if the system is deleted from CAM. 0 is not deleted. 1 is deleted.
created_user_id	The user id who created the external system connection.
last_modified_user_id	The user id who last modified the external system connection.
created_date	The date created
last_modified_date	The date last modified.

External System Authentication Table

This table contains all the external system authentication method information within CAM.

Query the table by: select * from external_system_auth limit 100

^Use the other Query type

Column	Description	Keys (Primary/ Foreign)

Column	Description	Keys (Primary/ Foreign)
id	The internal CAM id of each external system authentication row.	PK
systemUniqueId	The external system unique id. Each tenant or instance will have a separate id. They look like: 91b692a0-c09d-00e8-9e62-0dfac7e82c00:~imanage:~Worksite2022	FK on externalsystem
system	Type of the external system role. E.g. iManage, Office365 etc
camrepoName	Name of the CAM instance that connects to this external system. E.g. Worksite2022
healthy	Marks if the system has errored recently. If it has, it is unhealthy (0). Healthy is =1
errorMessage	If an error occurred with the system, the last error displays here. For example, Unable to authenticate with IManage server. Attempts(10) Consumed. Please contact system administrator.(Root Cause : 403 Forbidden)