Home / General reference / Data / Data queries / Core tables

Core Tables

Article • 23.04.2021 • 17 minute(s) to read

The Database of the Novunex Platform offers several Core Tables for each subscription. These tables can be accessed via SQL queries. While you can access the data in the Core Tables via SQL SELECET queries, it is not possible to add or change data by UPDATE, INSERT or DELETE queries.

While reading from the Core tables can in some cases significantly reduce complexity in your Processes and Data Queries, it is not necessary for all Processes and Data Queries. In other words, you can access data from the Core Tables also via other means.

The remainder of this page lists the Core Tables that can be accessed and describes their columns.

erDiagram EntityType ||--o{ Entity : "" EntityType ||--o{ Attribute : "" Entity |o--o{ File : "" Entity |o--o{ Signature : "" Entity |o--o{ History : "" Process |o--o{ ProcessInstance : "" ProcessInstance |o--o{ ProcessInstanceVariable : "" UserTransaction |o--o{ User : "" UserUserGroup |o--o{ User : "" UserUserGroup |o--o{ UserGroup : ""

Attribute

Table containing the Attribute definition of the Entity Types in the current Subscription. The values stored in the Attributes are found in the history table.

Column SQL data type Description
Id bigint ID of the Attribute (unique for the Account)
Name nvarchar of length 255 The name of the Attribute
Description nvarchar of max. supported length Description of the Attribute
AssignedDataType int Assigned data type of attribute:
Details nvarchar of max. supported length Configuration of the Attribute serialized as JSON string
IsList bit Flag that indicates whether the Attribute is an array
IsMandatory bit Not null flag - indicating that a value must be set for the entity
IsTableColumn bit Deprecated - do not use
IsUnique bit Index flag - forcing unique values for the Attribute
IsUniqueInList bit Index flag within an array - forcing unique values for the Attribute within its array
DefaultValue nvarchar of max. supported length The default value of the Attribute
MaxValue nvarchar of max. supported length The maximum value / uppper bound of the Attribute
MinValue nvarchar of max. supported length The minimum value / lower bound of the Attribute
DisplayInDataExplorer bit Flag to enable / disable access via the Data Explorer
DisplayOrder int The display order of the Attributes within the Entity Type
EntityTypeId bigint The ID of the Entity Type of the Attribute
Guid nvarchar of max. supported length Globally unique ID of the Attribute
IsDateOnly bit Flag specifying to use only the date part of a Date Time Attribute. Unused for other data types.
LookupType int Configuration if and what Lookup or Predefined Values this Attribute uses:
LookupItems nvarchar of max. supported length If this Attribute uses Predefined Values, the values are stored as a JSON string here.
LookupQuery nvarchar of max. supported length If this Attribute uses a Lookup, the query for the Lookup is stored here.
SerialNumberDefinitionId bigint The Serial Number definition of this Attribute, only used in the Attribute is a Serial Number)
TableName nvarchar of length 255 Deprecated - do not use

ActivityPermission

Table containing the Users currently assigned to interact with Activities in an Instance.

Column SQL data type Description
Id bigint Unique ID of the table row
ActivityId nvarchar A unique identifier of the Activity
ActivityName nvarchar The name of the Activities
Priority int The priority of the Activities
DateTimeUtc datetime2 with a fraction of second precision of 7 The timestamp of the Activity permission (UTC timezone)
DueDate datetime2 with a fraction of second precision of 7 The due date of the Activity (UTC timezone)
HideInTaskList bit Flag indicating if the Activity is shown in the task list
IsActive bit Flag indicating if the Activity is active, not disabled
ParentActivityId bigint Unique ID of the parent Activity
ParentActivityName nvarchar The name of the parent Activity
ProcessInstanceId bigint The unique ID of the Instance
ProcessName nvarchar The name of the Process
ParentProcessName nvarchar The name of the parent Process
TaskDisplayInformation nvarchar Description of this Activity shown to the Users in their task list.
ProcessGuid nvarchar A unique identifier of the Process
ParentProcessGuid nvarchar A unique identifier of the parent Process
ParentProcessInstanceId bigint The ID of the parent Instance
UserId int The ID of the assigned User
UserGroupId int The ID if the assigned User Group
Status int Status of Activity user execution, i.e, if the Activity has been executed or not encoded as 0 / 1 or True / False
SubscriptionId int The corresponding Subscription ID

Dashboard

Table containing then configuration of the Dashboards of the current Subscription. Each modification of a Dashboard produces a new row in this table. Previous configuration, i.e., rows, are also kept in the table. Each Dashboard is uniquely identified by its Guid and the current configuration is identified by the Revision

Column SQL data type Description
Id bigint Unique ID of the table row
Guid nvarchar of length 36 A unique identifier for the Dashboard
Name nvarchar of length 255 The name of the Dashboard
Revision int The version of the Dashboard
CreationDateUtc datetime2 with a fraction of second precision of 7 The creation timestamp of the Dashboard (UTC timezone)
CreationUserId bigint The user ID of the Dashboard creator
ModificationDateUtc datetime2 with a fraction of second precision of 7 The modification timestamp of the Dashboard (UTC timezone)
LastUpdateUserId bigint The ID of the user who last modified the Dashboard
Layout nvarchar of length 255 The configuration parameter of the Dashboard layout
ParentDashboardId bigint null for active Dashboards and for inactive Dashboards this column holds the Id of the currently active Dashboard, i.e., to last revision
IsHistory bit False / 0 for active Dashboard and True / 1 for archived Dashboards
AppId bigint The corresponding ID of the Novunex App this Dashboard belongs to
AppName nvarchar of length 255 The name of the Novunex App this Dashboard belongs to
SubscriptionId bigint The corresponding subscription ID this Dashboard belongs to
IsPersonal bit Deprecated - do not use

Entity

Table containing meta information of Entities.

Column SQL data type Description
Id bigint The ID of the Entity that is unique for the Account)
EntityTypeId bigint The corresponding Entity Types ID
CreationDateUtc datetime2 with a fraction of second precision of 7 The creation timestamp of the Entity (UTC timezone)
CreationUserId bigint The user ID of the Entity creator
ModificationDateUtc datetime2 with a fraction of second precision of 7 The modification timestamp of the Entity (UTC timezone)
LastUpdateUserId bigint The ID of the user who last modified the Entity
Revision int The version of the Entity
SubscriptionId bigint The ID of the subscription this Entity belongs to
UserGroupPermissionsId bigint Deprecated - do not use

History of Entities

The history of the Entities describes the changes of the Attributes of a given Entity Type. The history is only available, when the Track Changes setting of an Entity Type is set to Yes. The history is accessible by the name of the Entity Type and the suffix _History. For example, the history of the Entity Type [MyEntityType] can be accessed with [MyEntityType_History].

Each Attribute of the Entity Type becomes a column in the history table. Since it is possible the change the Attributes of an Entity Type, the history always represents the last version. Therefore, deleted Attributes are also deleted from the history. Newly added Attributes are set to null for existing Attributes.

Arrays are represented as multiple rows in the history table. I.e., each entry in an array corresponds to a separate row in the history table. The array index is given by the column ValueIndex and counts up until all elements of all arrays are iterated. Non-array values are only filled in the first row, i.e., when the array index is 0.

Column SQL data type Description
Id bigint ID of the history entry (unique for the account)
EntityId bigint ID of the Entity this history entry describes
CreationDateUtc datetime2 with a fraction of second precision of 7 The creation timestamp of the Entity (UTC timezone)
ModificationDateUtc datetime2 with a fraction of second precision of 7 The modification timestamp of the Entity (UTC timezone)
CreationUserId bigint The ID of the User that created the Entity
LastUpdateUserId bigint The ID of the User that last modified the Entity
Revision int The version number of the Entity
ValueIndex int Index for arrays. For non-array values this column is always 0.
Attribute Columns Data Type of the corresponding Attribute Each Attribute of the Entity Type becomes a column

EntityType

Table containing meta information of Entity Types.

Column SQL data type Description
Id bigint The ID of the Entity Types which is unique for the Account
Guid nvarchar of length 36 A globally unique identifier for the Entity Types
Name nvarchar of length 255 The name of the Entity Types
Description nvarchar of max. supported length The description of the Entity Types
Icon nvarchar of length 255 An icon representing the Entity Types
Details nvarchar of max. supported length Configuration parameters of the Entity Types as JSON string
CanEditManually bit Flag to enable / disable access via the Data Editor
CreationDateUtc datetime2 with a fraction of second precision of 7 The creation timestamp of the Entity Type (UTC timezone)
CreationUserId bigint The user ID of the Entity Types creator
ModificationDateUtc datetime2 with a fraction of second precision of 7 The modification timestamp of the Entity Types (UTC timezone)
LastUpdateUserId bigint The ID of the user who last modified the Entity Types
KeepHistory bit Flag to enable the audit trail of Entity modifications. If this is True / 1, then the history table exists for this Entity Types
Revision int The version of the Entity Types
AppId bigint The ID of corresponding Novunex App this Entity Types belongs to
AppName nvarchar of length 255 The name of the Novunex App this Entity Types belongs to
SubscriptionId bigint The ID of the Subscription this Entity Types belongs to
IncudedInOverallSearch bit Deprecated - do not use
IsVisibleInList bit Deprecated - do not use
UserGroupPermissionsId bigint Deprecated - do not use

File

Table containing file related information and meta data.

Column SQL data type Description
Id bigint The ID of the file which is unique for the Account
Name nvarchar of length 255 The name of the file
Category nvarchar of length 255 The file category
Description nvarchar of max. supported length Deprecated - do not use
Guid nvarchar of length 36 A globally unique identifier for the file
Size decimal with a total length of 18 digits and 2 from those are decimal places. The file size in bytes
Url nvarchar of length 500 The absolute URL of the file
Revision int The version of the file
CreationDateUtc datetime2 with a fraction of second precision of 7 The creation timestamp of the file (UTC timezone)
CreationUserId bigint The user ID of the file creator
ModificationDateUtc datetime2 with a fraction of second precision of 7 The modification timestamp of the file (UTC timezone)
LastUpdateUserId bigint The ID of the User who last modified the file
SubscriptionId bigint The ID of the Subscription this Entity Types belongs to
LocalizedDescriptions nvarchar of max. supported length Deprecated - do not use
LocalizedNames nvarchar of max. supported length Deprecated - do not use
SerialNumberDefinitionId bigint Deprecated - do not use

Process

Table containing Process definitions and meta data.

Column SQL data type Description
Id bigint The unique ID of the Process which is unique for the Account
Name nvarchar of length 255 The name of the Process
Description nvarchar of max. supported length The description of the Process
Category nvarchar of length 255 The category name of the Process
Icon nvarchar of max. supported length The icon of the Process
Guid nvarchar of length 36 A globally unique identifier for the Process
Version int The version of the Process
Details nvarchar of max. supported length Configuration parameters of the Process as JSON string
ProcessHelpId bigint Instructions given for the Process
InputSettings nvarchar of max. supported length Process Start Parameters stored as JSON string
CanStartManually bit Flag indicating whether the Process can be started by a User or only by the Scheduler or other Process.
RunAsync bit Flag indicating whether the Process starts in an asynchronous, non-blocking way.
IsDeleted bit Flag indicating whether the Process has been deleted
IsDeployed bit Flag indicating whether the Process has been deployed
IsSingleton bit Flag indicating whether only a single Instance can have the status running for this Process at any given time
DisplayStartParameter bit Flag to enable or disable the Start Parameters form upon starting a process manually
UserGroupPermissionsId bigint Defines which User Groups are execute the Process. While this table has only one entry but it is possible that multiple User Groups are allowed to execute the Process, there is another table detailing which Users Groups are linked to this entry here
UserPermissionsId bigint Defines which Users are allowed to execute the Process. While this table has only one entry but it is possible that multiple Users are allowed to execute the Process, there is another table detailing which Users Groups are linked to this entry here
CreationDateUtc datetime2 with a fraction of second precision of 7 The creation timestamp of the Process (UTC timezone)
CreationUserId bigint The user ID of the Process creator
ModificationDateUtc datetime2 with a fraction of second precision of 7 The modification timestamp of the Process (UTC timezone)
LastUpdateUserId bigint The ID of the User who last modified the Process
AppId bigint The ID of the corresponding Novunex App this Process belongs to
AppName nvarchar of length 255 The name of the Novunex App this Process belongs to
SubscriptionId bigint The ID of the Subscription this Process belongs to
Color nvarchar of max. supported length Deprecated - do not use
IsAtomic bit Deprecated - do not use
IsProcessStep bit Deprecated - do not use
IsTemplate bit Deprecated - do not use
LocalizedNames nvarchar of max. supported length Deprecated - do not use
LocalizedDescriptions nvarchar of max. supported length Deprecated - do not use

ProcessInstance

Table containing information of Instances.

Column SQL data type Description
Id bigint The unique ID of the Instance
ProcessId bigint The ID of the Process
Name nvarchar of length 255 Name of the Process
Version nvarchar of length 20 Version of the process
IsDebugMode nvarchar of length 20 Boolean Flag encoded as a string indicating whether this Instances was created in debug mode or not
Context nvarchar of max. supported length The context of the Instance, containing all context variables encoded as a JSON string
Status int The current status of the Instance:
  • 1 - Started
  • 2 - In Progress
  • 3 - Closed
  • 4 - Canceled
  • 5 - Faulted
LastOutcome nvarchar of length 255 The last Outcome in the Instance passed through
FromParentActivityId nvarchar of length 36 ID of the parent Activity that created the process Instance. This column is null if the Instance was not started by an Activity)
ParentProcessInstanceId bigint The ID of the Instance that created this Instance, or null if this Instance was not started by another Process
CreationDateUtc datetime2 with a fraction of second precision of 7 The creation timestamp of the Instance (UTC timezone)
CreationUserId bigint The ID of the User that created the Instance
ModificationDateUtc datetime2 with a fraction of second precision of 7 The modification timestamp of the Instance (UTC timezone)
LastUpdateUserId bigint The ID of the User who last modified the Instance
SubscriptionId bigint The ID of the Subscription this Process belongs to

ProcessInstanceVariable

Table containing a list of all Context Variables of all Instances.

It is recommended to querying the ProcessInstanceVariable table from Activities, i.e., inside the Process context. If you query the ProcessInstanceVariable table from outside the Process context, the entries can be out of sync.

Column SQL data type Description
Id bigint The unique ID of the table row
ProcessInstanceId bigint The ID of the Instance
Name nvarchar of length 100 Name of the variable
Value nvarchar of max. supported length The value of the variable
ParentProcessInstanceId bigint The ID of the Instance that created this Instance, or null if this Instance was not started by another Process

Signature

Table containing information of signatures.

Column SQL data type Description
Id bigint The unique ID of the Signature which is unique for the Account
Reason nvarchar of length 500 The description of the Signature
Comment nvarchar of max. supported length A comment stored along with the Signature
TimestampUtc datetime2 with a fraction of second precision of 7 The time when the the Signature was issued (UTC timezone)
Host nvarchar of length 255 The host name of the computer used to issue the Signature
IpAddress nvarchar of length 36 The IP address of the computer used to issue the Signature
CreationDateUtc datetime2 with a fraction of second precision of 7 The creation timestamp of the Signature (UTC timezone)
CreationUserId bigint The user ID of the Signature creator
ModificationDateUtc datetime2 with a fraction of second precision of 7 The modification timestamp of the Signature (UTC timezone)
LastUpdateUserId bigint The ID of the User who last modified the Signature
SubscriptionId bigint The ID of the Subscription this Signature belongs to

User

Table containing all user-related meta data and information.

Column SQL data type Description
Id bigint The unique ID of the User that is unique for the Account
UserName nvarchar of length 500 The email address used as username of the User
FirstName nvarchar of length 100 The first name of the User
LastName nvarchar of length 100 The last name of the User
ProfileImage nvarchar of length 500 The URL of the profile image
JobTitle nvarchar of length 100 The job title of the User
Department nvarchar of length 100 The name of the user’s department
Location nvarchar of length 100 The user’s location or location name, respectively
ManagerId bigint The User ID of the assigned manager
IsoLanguageCode nvarchar of length 10 The localized ISO language code of the user
TimeZone nvarchar of length 100 The timezone of the User
IsDeActivated bit Flag indicating whether the User is deactivated or not
IsDeleted bit Flag indicating whether the user is deleted or not
CreationDateUtc datetime2 with a fraction of second precision of 7 The creation timestamp of the User (UTC timezone)
CreationUserId bigint The ID of the User that created this User
ModificationDateUtc datetime2 with a fraction of second precision of 7 Timestamp of the last modification of the User (UTC timezone)
LastUpdateUserId bigint The ID of the User who last modified the User
AccountId bigint Deprecated - do not use
Division nvarchar of length 100 Deprecated - do not use
ExternalAccount nvarchar of length 36 Deprecated - do not use
ExternalUserName nvarchar of length 500 Deprecated - do not use
IsOutOfOffice bit Deprecated - do not use
ResetPasswordRequestId bigint Deprecated - do not use
SecurityStamp nvarchar of length 1000 Deprecated - do not use
SignatureImage nvarchar of length 500 Deprecated - do not use

UserGroup

Table containing all User Group-related meta data and information.

Column SQL data type Description
Id bigint The ID of the User Group which unique for the Account
Name nvarchar of length 255 The name of the User Group
IsSysAdminGroup bit Flag indicating whether the User Group is a system admin group or not
CreationDateUtc datetime2 with a fraction of second precision of 7 The creation timestamp of the User Group (UTC timezone)
CreationUserId bigint The ID of the User that created the User Group
ModificationDateUtc datetime2 with a fraction of second precision of 7 The timestamp of the last modification of the User Group (UTC timezone)
LastUpdateUserId bigint The ID of the User who last modified the User Group
SubscriptionId bigint The ID of the corresponding Subscription this User Group belongs to
LocalizedNames nvarchar of max. supported length Deprecated - do not use

UserTransaction

Table containing audit logs of actions to User and User Groups.

Column SQL data type Description
Id bigint The ID of the User actions which is unique for the Account
Transaction nvarchar of length 2000 The name / description of the User action
TransactionType int The action that was done:
  • 1 - Modified
  • 2 - Created
  • 3 - Deleted
  • 4 - Started
  • 5 - Logged In
  • 6 - Stopped
  • 7 - Activated
  • 8 - Deactivated
  • 9 - Deployed
  • 10 - Canceled
  • 11 - Failed
TransactionTo nvarchar of length 255 Target name or description of the User action
TransactionToId nvarchar of length 255 Target ID of the User action
CreationDateUtc datetime2 with a fraction of second precision of 7 The creation timestamp of the User action (UTC timezone)
CreationUserId bigint The ID of the doing the action
ModificationDateUtc datetime2 with a fraction of second precision of 7 Timestamp of the last modification of the action (UTC timezone)
LastUpdateUserId bigint The ID of the User who last modified the action
SubscriptionId bigint The ID of the corresponding Subscription this action belongs to

UserUserGroup

Table containing a n:m mapping between User and User Group memberships. I.e., this table links the Users and UserGroup table entries.

Column SQL data type Description
Id bigint The unique ID of the User to User Group mapping. The mapping is unique for the Account
UserGroupId bigint The User Group ID
UserId bigint The Id of the User assigned the User Group
In this article