Appearance

Database structure ​

How to document engine's database ​

The database structure of Lesli and all the engines must be documented and maintained within the respective engine in the following path: engine_root/docs/database.md. If you make any changes that add, remove or renumber any table, you must document it within this file, or your pull request will be rejected.

This documentation file must include a ER diagram with a detailed description of the engine database tables and columns, we recommend to use (mermaid)[https://mermaid.js.org/] to work with this kind of diagrams.

Example:

Namespace and Table Number Standard and Required Tables ​

To calculate engines and tables numerations, you should meet the following requirements:

1. Each engine has an account table, that isolates the resources to each account, this table is always the code CC.EE.00.01

2. After the account, the catalogs namespace is used with the numeration CC.EE.01. A catalog table is a table that has only data, with no foreign key other than the account, and is used to provide data to other more complex tables. Your first catalog table must have the code CC.EE.01.01

3. The workflows namespace is next and it contains the codes CC.EE.02.TT starting with the table CC.EE.02.01: cloud_ENGINE_workflows

4. The custom_fields namespace is next and it contains the codes CC.EE.03.TT starting with the table CC.EE.03.01: cloud_ENGINE_custom_fields

5. The custom_validations namespace is next and it contains the codes CC.EE.04.TT starting with the table CC.EE.04.01: cloud_ENGINE_custom_validations

6. The dashboards namespace is next and it contains the codes CC.EE.05.TT starting with the table CC.EE.05.01: cloud_ENGINE_dashboards

The codes CC.EE.06.TT to CC.EE.09.TT are reserved for future usage.

After this you can create any number of tables depending on the amount of resources you will have in your engine. Starting with the namespace CC.EE.10, these tables have the following format:

  • CC.EE.10.00 Main table
  • CC.EE.10.01 Main table actions
  • CC.EE.10.02 Main table activities
  • CC.EE.10.03 Main table discussions
  • CC.EE.10.04 Main table subscribers
  • CC.EE.10.05 Main table files
  • CC.EE.10.06 Main table custom_field_values
  • CC.EE.10.07 to 10.09 are reserved
  • CC.EE.10.10 and higher are for your custom tables

Example of database.md file ​

Here is an example of a database.md file for the CloudHelp engine (code 07.02) (this is just an example, for the real database documentation you should review the DATABASE STRUCTURE section):

    00.01. help/account                     # Required table

    01.01. help/catalogs                    # Required table
    01.02. help/catalog/ticket_categories 
    01.03. help/catalog/ticket_priorities

    01.01. help/catalog_workspaces                    
    01.02. help/catalog_categories 
    01.03. help/catalog_priorities


    02.01. help/workflows                   # Required table
    02.02. help/workflow/statuses           # Required table
    02.03. help/workflow/associations       # Required table
    02.04. help/workflow/actions            # Required table

    03.01. help/custom_fields               # Required table

    04.01. help/custom_validations          # Required table
    04.02. help/custom_validation/rules     # Required table
    04.03. help/custom_validation/fields    # Required table

    05.01. help/dashboards                  # Required table
    05.02. help/dasboard/components         # Required table

    # Note that here 06.XX to 09.XX are unused and reserved

    10.00. help/tickets
    10.01. help/ticket/actions              # Required child table for tickets
    10.02. help/ticket/activities           # Required child table for tickets
    10.03. help/ticket/discussions          # Required child table for tickets
    10.04. help/ticket/subscribers          # Required child table for tickets
    10.05. help/ticket/files                # Required child table for tickets
    10.06. help/ticket/custom_field_values  # Required child table for tickets
    10.10. help/ticket/timelines
    10.11. help/ticket/assignments

    11.00. help/slas
    11.01. help/sla/actions                 # Required child table for tickets
    11.02. help/sla/activities              # Required child table for tickets
    11.03. help/sla/discussions             # Required child table for tickets
    11.04. help/sla/subscribers             # Required child table for tickets
    11.05. help/sla/files                   # Required child table for tickets
    11.06. help/sla/custom_field_values     # Required child table for tickets
    11.10. help/sla/associations