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'