Corteza System

Corteza system database structure

Table 1. sys_application
Column	Type	Default	Description
id	bigint(20) unsigned NOT NULL
rel_owner	bigint(20) unsigned NOT NULL
name	text NOT NULL		something we can differentiate application by
enabled	tinyint(1) NOT NULL
unify	json	NULL	unify specific settings
created_at	datetime NOT NULL	CURRENT_TIMESTAMP
updated_at	datetime	NULL
deleted_at	datetime	NULL

Table 2. sys_automation_script
Column	Type	Default	Description
id	bigint(20) unsigned NOT NULL
rel_namespace	bigint(20) unsigned NOT NULL
name	varchar(64) NOT NULL	'unnamed'	The name of the script
source	text	NOT NULL	Source code for the script
source_ref	varchar(200) NOT NULL		Where is the script located (if remote)
async	tinyint(1) NOT NULL	'0'	Do we run this script asynchronously?
rel_runner	bigint(20) unsigned NOT NULL	'0'	Who is running the script? 0 for invoker
run_in_ua	tinyint(1) NOT NULL	'0'	Run this script inside user-agent environment
timeout	int(10) unsigned NOT NULL	'0'	Any explicit timeout set for this script (milliseconds)?
critical	tinyint(1) NOT NULL	'1'	Is it critical that this script is executed successfully
enabled	tinyint(1) NOT NULL	'1'	Is this script enabled?
created_by	bigint(20) unsigned NOT NULL	'0'
created_at	datetime NOT NULL	CURRENT_TIMESTAMP
updated_by	bigint(20) unsigned NOT NULL	'0'
updated_at	datetime	NULL
deleted_by	bigint(20) unsigned NOT NULL	'0'
deleted_at	datetime	NULL

Table 3. sys_automation_trigger
Column	Type	Default	Description
id	bigint(20) unsigned NOT NULL
rel_script	bigint(20) unsigned NOT NULL		Script that is triggered
resource	varchar(128) NOT NULL		Resource triggering the event
event	varchar(128) NOT NULL		Event triggered
event_condition	text NOT NULL		Trigger condition
enabled	tinyint(1) NOT NULL	'1'	Trigger enabled?
weight	int(11) NOT NULL	'0'
created_by	bigint(20) unsigned NOT NULL	'0'
created_at	datetime NOT NULL	CURRENT_TIMESTAMP
updated_by	bigint(20) unsigned NOT NULL	'0'
updated_at	datetime	NULL
deleted_by	bigint(20) unsigned NOT NULL	'0'
deleted_at	datetime	NULL

Table 4. sys_credentials
Column	Type	Default	Description
id	bigint(20) unsigned NOT NULL
rel_owner	bigint(20) unsigned NOT NULL
label	text NOT NULL		something we can differentiate credentials by
kind	varchar(128) NOT NULL		hash, facebook, gplus, github, linkedin …
credentials	text NOT NULL		crypted/hashed passwords, secrets, social profile ID
meta	json NOT NULL
expires_at	datetime	NULL
created_at	datetime NOT NULL	CURRENT_TIMESTAMP
updated_at	datetime	NULL
deleted_at	datetime	NULL
last_used_at	datetime	NULL

Table 5. sys_organisation
Column	Type	Default	Description
id	bigint(20) unsigned NOT NULL
fqn	text NOT NULL
name	text NOT NULL
created_at	datetime NOT NULL	CURRENT_TIMESTAMP
updated_at	datetime	NULL
archived_at	datetime	NULL
deleted_at	datetime	NULL

Table 6. sys_permission_rules
Column	Type	Default	Description
rel_role	bigint(20) unsigned NOT NULL
resource	varchar(128) NOT NULL
operation	varchar(128) NOT NULL
access	tinyint(1) NOT NULL

Table 7. sys_reminder
Column	Type	Default	Description
id	bigint(20) unsigned NOT NULL
resource	varchar(128) NOT NULL		Resource that this reminder is bound to
payload	json NOT NULL		Payload for this reminder
snooze_count	int(11) NOT NULL	'0'	Number of times this reminder was snoozed
assigned_to	bigint(20) unsigned NOT NULL	'0'	Assignee for this reminder
assigned_by	bigint(20) unsigned NOT NULL	'0'	User that assigned this reminder
assigned_at	datetime NOT NULL		When the reminder was assigned
dismissed_by	bigint(20) unsigned NOT NULL	'0'	User that dismissed this reminder
dismissed_at	datetime	NULL	Time the reminder was dismissed
remind_at	datetime	NULL	Time the user should be reminded
created_by	bigint(20) unsigned NOT NULL	'0'
created_at	datetime NOT NULL	CURRENT_TIMESTAMP
updated_by	bigint(20) unsigned NOT NULL	'0'
updated_at	datetime	NULL
deleted_by	bigint(20) unsigned NOT NULL	'0'
deleted_at	datetime	NULL

Table 8. sys_role
Column	Type	Default	Description
id	bigint(20) unsigned NOT NULL
name	text NOT NULL
handle	text NOT NULL
created_at	datetime NOT NULL	CURRENT_TIMESTAMP
updated_at	datetime	NULL
archived_at	datetime	NULL
deleted_at	datetime	NULL

Table 9. sys_role_member
Column	Type	Default	Description
rel_role	bigint(20) unsigned NOT NULL
rel_user	bigint(20) unsigned NOT NULL

Table 10. sys_settings
Column	Type	Default	Description
rel_owner	bigint(20) unsigned NOT NULL	'0'	Value owner
0 for global settings	name	varchar(200) NOT NULL	Unique set of setting keys
value	json	NULL	Setting value
updated_at	datetime NOT NULL	CURRENT_TIMESTAMP	When was the value updated
updated_by	bigint(20) unsigned NOT NULL	'0'	Who created/updated the value

Table 11. sys_user
Column	Type	Default	Description
id	bigint(20) unsigned NOT NULL
email	text NOT NULL
username	text NOT NULL
name	text NOT NULL
handle	text NOT NULL
kind	varchar(8) NOT NULL	''
meta	json NOT NULL
rel_organisation	bigint(20) unsigned NOT NULL
rel_user_id	bigint(20) unsigned NOT NULL
created_at	datetime NOT NULL	CURRENT_TIMESTAMP
updated_at	datetime	NULL
suspended_at	datetime	NULL
deleted_at	datetime	NULL
email_confirmed	tinyint(1) NOT NULL	'0'