Corteza Core Developer’s Manual ⛏

getting-started ⛏

versions ⛏

Git ⛏

repositories ⛏

flow ⛏

branches-tags ⛏

Corteza Database

Technologies

Database support

At the moment, Corteza only supports the MySQL database. This will change in the near future with the introduction of a ORM and a better test coverage of existing feature.

Corteza database structure

This section provides an overview of the entire database structure. You can use the following links to refer to:

Corteza system database structure,
Corteza Low Code database structure,
Corteza Messaging database structure.

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
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
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
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'

Corteza Low Code database structure

Table 12. compose_attachment
Column	Type	Default
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 13. compose_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 14. compose_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 15. 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 16. 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 17. 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 18. 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 19. 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 20. 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 21. compose_record
Column	Type	Default
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 22. 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 23. 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

Corteza Messaging database structure

Table 24. messaging_attachment
Column	Type	Default
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 25. messaging_channel
Column	Type	Default
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 26. messaging_channel_member
Column	Type	Default
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 27. messaging_mention
Column	Type	Default
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 28. messaging_message
Column	Type	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 29. messaging_message_attachment
Column	Type	Default	Description
rel_message	bigint(20) unsigned NOT NULL
rel_attachment	bigint(20) unsigned NOT NULL

Table 30. messaging_message_flag
Column	Type	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 31. 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 32. 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 33. messaging_unread
Column	Type	Default
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'

Database Setup ⛏

Database Style Guide ⛏

Database Style Migrations ⛏

Database Style Quality ⛏

Database Style Tools ⛏

Parts ⛏

Corteza System

Corteza system database structure

Table 34. 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 35. 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 36. 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 37. 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 38. sys_organisation
Column	Type	Default
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 39. 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 40. 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 41. sys_role
Column	Type	Default
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 42. sys_role_member
Column	Type	Default	Description
rel_role	bigint(20) unsigned NOT NULL
rel_user	bigint(20) unsigned NOT NULL

Table 43. 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 44. sys_user
Column	Type	Default
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'

Corteza Low Code

Corteza Low Code database structure

Table 45. compose_attachment
Column	Type	Default
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 46. compose_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 47. compose_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 48. 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 49. 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 50. 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 51. 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 52. 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 53. 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 54. compose_record
Column	Type	Default
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 55. 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 56. 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

Corteza Low Code Record Validation

Corteza Low Code provides a powerful, flexible system for record validation that can be used for simple operations such as checking if the field is empty or complex operations that require API access.

All validator errors are instances of validator.ValidatorError class, with the interface of:

interface ValidatorError {
  kind: string;(1)
  message: string;(2)
  meta: { [key: string]: unknown };(3)
}

1	The kind of the error; for now, this can be an arbitrary string that describes the error.
2	The optional message with a more verbose error description. Defaults to `err.kind`.
3	A loosely defined object that stores any additional metadata, such as field name, recordID, …

The validation flow

Dispatch beforeFormSubmit event,
- if any automation script returns a validator.ValidatorError, stop the execution and show the errors,
run the validator.RecordValidator system,
1. on validator.ValidatorError, dispatch the onFormSubmitError event that can be used to fix the validation errors,
2. run the validator.RecordValidator system,
  - if errors persist, stop the execution and show the errors,
request record create/update on the API,
- if the API returns a validator.ValidatorError, stop the execution and show the errors.

Corteza Messaging

Corteza Messaging database structure

Table 57. messaging_attachment
Column	Type	Default
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 58. messaging_channel
Column	Type	Default
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 59. messaging_channel_member
Column	Type	Default
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 60. messaging_mention
Column	Type	Default
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 61. messaging_message
Column	Type	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 62. messaging_message_attachment
Column	Type	Default	Description
rel_message	bigint(20) unsigned NOT NULL
rel_attachment	bigint(20) unsigned NOT NULL

Table 63. messaging_message_flag
Column	Type	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 64. 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 65. 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 66. messaging_unread
Column	Type	Default
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'

api-server ⛏

api-server-setup ⛏

api-server-structure ⛏

api-server-style-guide ⛏

api-server-style-tools ⛏

api-server-style-quality ⛏

api-server-style-build ⛏

corredor-server ⛏

corredor-server-setup ⛏

corredor-server-structure ⛏

corredor-server-style-guide ⛏

corredor-server-style-tools ⛏

corredor-server-style-quality ⛏

corredor-server-style-build ⛏

Web Applications ⛏

Intro

web-apps-setup ⛏

web-apps-structure ⛏

web-apps-style-guide ⛏

web-apps-style-tools ⛏

web-apps-style-quality ⛏

web-apps-style-build ⛏

Build Pipelines

Testing and releasing

Corteza are tested and built with Drone CI (look for .drone.yml files on root of each repository for details). Drone builds one or more binary or bundle (JavaScript, CSS, HTML files) artifacts and places them on https://release.cortezaproject.org.

Files can be then downloaded manually or automatically.

Packaging

Corteza officially supports packaging into Docker Image files.

Unofficially, released packages can be used with Vagrant box https://github.com/cortezaproject/corteza-vagrant or installed manually on the (virtual) machine.