Corteza Database

Overview

Current Corteza version only supports MySQL databases. This will change in the 2020.12.0 with the rework of the storage layer.

Corteza uses the Percona Docker container for the database.

Database structure

System

Table 1. sys_actionlog
Column	Type	Default	Description
ts	DATETIME NOT NULL	NOW()
actor_ip_addr	VARCHAR(15) NOT NULL
actor_id	BIGINT UNSIGNED
request_origin	VARCHAR(32) NOT NULL
request_id	VARCHAR(64) NOT NULL
resource	VARCHAR(128) NOT NULL
action	VARCHAR(64) NOT NULL
error	VARCHAR(64) NOT NULL
severity	SMALLINT NOT NULL
description	TEXT
meta	JSON		NULL

Table 2. 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 3. 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 4. 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 5. 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 6. 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 7. 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 8. sys_role_member
Column	Type	Default	Description
rel_role	bigint(20) unsigned NOT NULL
rel_user	bigint(20) unsigned NOT NULL

Table 9. 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 10. 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'

Low Code

Table 11. compose_attachment
Column	Type	Default	Description
id	bigint(20) unsigned NOT NULL
rel_namespace	bigint(20) unsigned NOT NULL
rel_owner	bigint(20) unsigned NOT NULL
kind	varchar(32) NOT NULL
url	varchar(512)	NULL
preview_url	varchar(512)	NULL
size	int(10) unsigned	NULL
mimetype	varchar(255)	NULL
name	text
meta	json	NULL
created_at	datetime NOT NULL	CURRENT_TIMESTAMP
updated_at	datetime	NULL
deleted_at	datetime	NULL

Table 12. compose_chart
Column	Type	Default	Description
id	bigint(20) unsigned NOT NULL
handle	varchar(200) NOT NULL
rel_namespace	bigint(20) unsigned NOT NULL
name	varchar(64) NOT NULL	The name of the chart	config
json NOT NULL	Chart & reporting configuration	created_at	datetime NOT NULL
CURRENT_TIMESTAMP		updated_at	datetime
NULL		deleted_at	datetime

Table 13. compose_module
Column	Type	Default	Description
id	bigint(20) unsigned NOT NULL
handle	varchar(200) NOT NULL
rel_namespace	bigint(20) unsigned NOT NULL
name	varchar(64) NOT NULL	The name of the module	json
json NOT NULL	List of field definitions for the module	created_at	datetime NOT NULL
CURRENT_TIMESTAMP		updated_at	datetime
NULL		deleted_at	datetime

Table 14. compose_module_field
Column	Type	Default	Description
id	bigint(20) unsigned NOT NULL
rel_module	bigint(20) unsigned NOT NULL
place	tinyint(3) unsigned NOT NULL
kind	varchar(64) NOT NULL		The type of the form input field
options	json NOT NULL		Options in JSON format.
default_value	json	NULL	Default value as a record value set.
name	varchar(64) NOT NULL		The name of the field in the form
label	varchar(255) NOT NULL		The label of the form input
is_private	tinyint(1) NOT NULL		Contains personal/sensitive data?
is_required	tinyint(1) NOT NULL
is_visible	tinyint(1) NOT NULL
is_multi	tinyint(1) NOT NULL
created_at	datetime NOT NULL	CURRENT_TIMESTAMP
updated_at	datetime	NULL
deleted_at	datetime	NULL

Table 15. compose_namespace
Column	Type	Default	Description
id	bigint(20) unsigned NOT NULL
name	varchar(64) NOT NULL	Name	slug
varchar(64) NOT NULL	URL slug	enabled	tinyint(1) NOT NULL
Is namespace enabled?	meta	json NOT NULL	Meta data
created_at	datetime NOT NULL	CURRENT_TIMESTAMP
updated_at	datetime	NULL
deleted_at	datetime	NULL

Table 16. compose_page
Column	Type	Default	Description
id	bigint(20) unsigned NOT NULL		Page ID
handle	varchar(200) NOT NULL
rel_namespace	bigint(20) unsigned NOT NULL
self_id	bigint(20) unsigned NOT NULL		Parent Page ID
rel_module	bigint(20) unsigned NOT NULL	'0'
title	varchar(255) NOT NULL		Title (required)
description	text NOT NULL		Description
blocks	json NOT NULL		array of blocks for the page
visible	tinyint(4) NOT NULL		Is page visible in navigation?
weight	int(11) NOT NULL		Order for navigation
created_at	datetime NOT NULL	CURRENT_TIMESTAMP
updated_at	datetime	NULL
deleted_at	datetime	NULL

Table 17. compose_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 18. compose_record
Column	Type	Default	Description
id	bigint(20) unsigned NOT NULL
rel_namespace	bigint(20) unsigned NOT NULL
module_id	bigint(20) unsigned NOT NULL
owned_by	bigint(20) unsigned NOT NULL	'0'
created_at	datetime NOT NULL	CURRENT_TIMESTAMP
updated_at	datetime	NULL
deleted_at	datetime	NULL
created_by	bigint(20) unsigned NOT NULL	'0'
updated_by	bigint(20) unsigned NOT NULL	'0'
deleted_by	bigint(20) unsigned NOT NULL	'0'

Table 19. compose_record_value
Column	Type	Default	Description
record_id	bigint(20) NOT NULL
name	varchar(64) NOT NULL
value	longtext
ref	bigint(20) unsigned NOT NULL	'0'	Field is used for quicker lookups when it comes to values that represent a reference, such as recordID, userID and attachmentID.
deleted_at	datetime	NULL
place	int(10) unsigned NOT NULL	'0'

Table 20. compose_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

Messaging

Table 21. messaging_attachment
Column	Type	Default	Description
id	bigint(20) unsigned NOT NULL
rel_user	bigint(20) unsigned NOT NULL
url	varchar(512)	NULL
preview_url	varchar(512)	NULL
size	int(10) unsigned	NULL
mimetype	varchar(255)	NULL
name	text
meta	json	NULL
created_at	datetime NOT NULL	CURRENT_TIMESTAMP
updated_at	datetime	NULL
deleted_at	datetime	NULL

Table 22. messaging_channel
Column	Type	Default	Description
id	bigint(20) unsigned NOT NULL
name	text NOT NULL
topic	text NOT NULL
meta	json NOT NULL
type	enum('private', 'public', 'group')	NULL
membership_policy	enum('featured','forced','') NOT NULL	''
rel_organisation	bigint(20) unsigned NOT NULL
rel_creator	bigint(20) unsigned NOT NULL
created_at	datetime NOT NULL	CURRENT_TIMESTAMP
updated_at	datetime	NULL
archived_at	datetime	NULL
deleted_at	datetime	NULL
rel_last_message	bigint(20) unsigned NOT NULL	'0'

Table 23. messaging_channel_member
Column	Type	Default	Description
rel_channel	bigint(20) unsigned NOT NULL
rel_user	bigint(20) unsigned NOT NULL
type	enum('owner','member','invitee')	NULL
flag	enum('pinned','hidden','ignored','') NOT NULL	''
created_at	datetime NOT NULL	CURRENT_TIMESTAMP
updated_at	datetime	NULL

Table 24. messaging_mention
Column	Type	Default	Description
id	bigint(20) unsigned NOT NULL
rel_channel	bigint(20) unsigned NOT NULL
rel_message	bigint(20) unsigned NOT NULL
rel_user	bigint(20) unsigned NOT NULL
rel_mentioned_by	bigint(20) unsigned NOT NULL
created_at	datetime NOT NULL	CURRENT_TIMESTAMP

Table 25. messaging_message
Column	Type	Default	Description
id	bigint(20) unsigned NOT NULL
type	mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci		message
mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL			meta
json	NULL		rel_user
bigint(20) unsigned NOT NULL			rel_channel
bigint(20) unsigned NOT NULL			reply_to
bigint(20) unsigned NOT NULL	'0'		created_at
datetime NOT NULL	CURRENT_TIMESTAMP		updated_at
datetime	NULL		deleted_at
datetime	NULL		replies

Table 26. messaging_message_attachment
Column	Type	Default	Description
rel_message	bigint(20) unsigned NOT NULL
rel_attachment	bigint(20) unsigned NOT NULL

Table 27. messaging_message_flag
Column	Type	Default	Description
id	bigint(20) unsigned NOT NULL
rel_channel	bigint(20) unsigned NOT NULL
rel_message	bigint(20) unsigned NOT NULL
rel_user	bigint(20) unsigned NOT NULL
flag	text		created_at

Table 28. messaging_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 29. messaging_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 30. messaging_unread
Column	Type	Default	Description
rel_channel	bigint(20) unsigned NOT NULL	'0'
rel_reply_to	bigint(20) unsigned NOT NULL
rel_user	bigint(20) unsigned NOT NULL	'0'
count	int(10) unsigned NOT NULL	'0'
rel_last_message	bigint(20) unsigned NOT NULL	'0'