DB Schema

A DB Schema is a declarative description of a database table structure.

Schemas are used for:

• automatic table creation
• database structure updates
• installation of initial data
• storage of mandatory system records

Database structure changes must be performed exclusively through schemas.

To synchronise schemas with the database, use the following command:

admin → update-db

The DB Schema is responsible for the physical storage of data.

The Object Schema uses the tables defined by the DB Schema and determines the business logic for working with that data.

td> td> td>

Property	Type	Description
title *	string	Table name. Used as documentation for developers.
properties *	array	Table properties.
rows_key	string	Field used to identify records when comparing the schema with existing database data. Typically used together with `required_rows` and `install_rows`.
required_rows	array	Required records. Records from this list are automatically added during database updates. Required records may be edited.
install_rows	array	Installation records. Records from this list are added during system installation.

properties

Property	Type	Description
title *	string	Property name. Used as documentation.
article *	string	Identifier. The system name of the property.
type *	string	Data type. Uses MySQL syntax: `varchar(75)`, `int`, `datetime`, etc.
is_required *	boolean	Whether the field is required.
default	mixed	Default value. If not specified, the value is `null`.

required_rows

Records whose existence is guaranteed by the system.

When `update-db` is executed, missing records are automatically created.

These records may be modified by users but cannot be completely removed from the system.

install_rows

Initial records that are added only during the first system installation.

After installation, these records are no longer synchronised.

Boolean Type

The framework uses the `char(1)` type to store boolean values.

Values:

• Y — true
• N — false

Conversion between boolean values and the `Y/N` format is performed automatically at the API level.

System Properties

These properties may be used in any table and receive special handling from the framework.

Property	Type	Description
is_system *	char(1)	System record. If the `is_system` field has the value `Y`, the record cannot be modified or deleted.
is_active	char(1)	Record status. Used as a soft-delete mechanism. This field is required if the Object Schema property `is_trash` is set to `true`. If `is_active` has the value `N`, the record is considered deleted.
created_at	datetime	Record creation date. Default value: `CURRENT_TIMESTAMP`.

Examples

{
    "title": "Call history",
    "properties": [
        {
            "title": "Employee ID",
            "article": "employee_id",
            "type": "int",
            "default": null,
            "is_required": "Y"
        },
        {
            "title": "Client ID",
            "article": "client_id",
            "type": "int",
            "default": null,
            "is_required": "N"
        },
        {
            "title": "Duration (sec)",
            "article": "duration",
            "type": "int",
            "default": null,
            "is_required": "N"
        },
        {
            "title": "Record",
            "article": "record",
            "type": "varchar(255)",
            "default": null,
            "is_required": "N"
        },
        {
            "title": "Created at",
            "article": "created_at",
            "type": "datetime",
            "default": "CURRENT_TIMESTAMP",
            "is_required": "Y"
        }
    ]
}