# DatabaseS

## Kernel

### address_area

Address areas

Column | Type | Description
--- | --- | ---
id | INT | ID
city_id | INT | city ID, link to address_city:id
title | VARCHAR | title
last_update | TIMESTAMP | last modification time

### address_city

Address cities

Column | Type | Description
--- | --- | ---
id | INT | ID
country_id | INT | country ID, link to address_country:id
title | VARCHAR | title
last_update | TIMESTAMP | last modification time

### address_country

Address countries

Column | Type | Description
--- | --- | ---
id | INT | ID
title | VARCHAR | title
last_update | TIMESTAMP | last modification time

### address_house

Address houses

Column | Type | Description
--- | --- | ---
id | INT | ID
area_id | INT | area ID, link to address_area:id
quarter_id | INT | quarter ID, link to address_quarter:id
street_id | INT | street ID, link to address_street:id
house | INT | number
frac | VARCHAR | number suffix with letter or /
post_index | VARCHAR | post index
comment | VARCHAR | comment
last_update | TIMESTAMP | last modification time

### address_quarter

Address quarters

Column | Type | Description
--- | --- | ---
id | INT | ID
city_id | INT | city ID, link to address_city:id
title | VARCHAR | title
last_update | TIMESTAMP | last modification time

### address_street

Address streets

Column | Type | Description
--- | --- | ---
id | INT | ID
city_id | INT | city ID, link to address_city:id
title | VARCHAR | title
last_update | TIMESTAMP | last modification time

### config_global

Конфигурации.

### counter

Счётчики для интерфейса.

### customer

Customers

Column | Type | Description
--- | --- | ---
id | INT | ID
title | VARCHAR | title
title_pattern_id | INT |
title_pattern | VARCHAR |
param_group_id | INT | ID группы параметров
create_dt | DATETIME | время создания контрагента
create_user_id | INT | создавший пользователь
pswd | VARCHAR |

### customer_log

Лог изменений контрагентов.

### db_update_log

DB updates log. The table is created on a first use.

Column | Type | Description
--- | --- | ---
dt | DATETIME | Execution time
query_hash | CHAR | Query hash

### file_data

File data.

Column | Type | Description
--- | --- | ---
id | INT | ID
title | VARCHAR | file name
dt | DATETIME | upload time
secret | CHAR | secret for requesting

### iface_state

Кэш состояний для интерфейса, например подписи на вкладке привязанных процессов об их количестве. Данные файлов.

Column | Type | Description
--- | --- | ---
object_type | VARCHAR | тип объекта
object_id | INT | ID объекта
iface_id | VARCHAR | идентификатор элемента интерфейса
state | VARCHAR | закэшированная строка

### message

Message, entities of org.bgerp.model.msg.Message.

Column | Type | Description
--- | --- | ---
id | INT | ID
system_id | VARCHAR | external unique ID, depending on message type
process_id | INT | process ID, link to process:id
type_id | INT | message type ID
direction | TINYINT | direction, 1 - incoming, 2 - outgoing
user_id | INT | created or read user ID, link to user:id
from | VARCHAR | sender, can contain user:id
to | VARCHAR | recipient
from_dt | DATETIME | creation date and time
to_dt | DATETIME | sending or reading date and time
subject | VARCHAR | subject
text | VARCHAR | message content
attach_data | TEXT | attachments, comma-separated links to file_data::id with titles

### message_tag

Message tags.

Column | Type | Description
--- | --- | ---
message_id | INT | message ID, link to message:id
tag_id | INT | message tag ID

### news

News

Column | Type | Description
--- | --- | ---
id | INT | news ID
user_id | INT | user ID, link to user:id
create_dt | DATETIME | news create datetime
update_dt | TIMESTAMP | news update time
title | VARCHAR | news title
text | TEXT | news content
is_popup | BIT | 1 - for popup news
life_time | INT | days before deletion
read_time | INT | hours before marking as read
groups | VARCHAR | comma-separated target user groups IDs

### news_user

News recipients.

Column | Type | Description
--- | --- | ---
id | INT | news id
user_id | INT | user ID, link to user:id
is_read | BIT | attribute read news

### param_address

Parameter type address values.

Column | Type | Description
--- | --- | ---
id | INT | ID
param_id | INT | param ID, link to param_pref:id
n | INT | sequential number for multiple param values, starting from 1
house_id | INT | house ID, linke to address_house:id
flat | CHAR | apartment
room | VARCHAR | flat
pod | TINYINT | entrance
floor | TINYINT | floor
value | VARCHAR | text representation of the adddress
comment | VARCHAR | comment

### param_blob

Параметры типа "blob".

Column | Type | Description
--- | --- | ---
id | INT | ID
param_id | INT | param ID, link to param_pref:id
value | TEXT | value

### param_date

Parameter type date values.

Column | Type | Description
--- | --- | ---
id | INT | object (process, customer, user) ID
param_id | INT | param ID, link to param_pref:id
value | DATE | value

### param_datetime

Параметры типа "datetime".

Column | Type | Description
--- | --- | ---
id | INT | ID
param_id | INT | param ID, link to param_pref:id
value | DATETIME | value

### param_email

Параметры типа "email".

Column | Type | Description
--- | --- | ---
id | INT | ID
param_id | INT | param ID, link to param_pref:id
n | INT | sequential 1-based number for multiple values
value | VARCHAR | value
comment | VARCHAR | comment

### param_file

Parameter type file values.

Column | Type | Description
--- | --- | ---
id | INT | ID
param_id | INT | param ID, link to param_pref:id
n | INT | sequential 1-based number for multiple values
value | INT | file ID, link to file_data:id

### param_group_title

Parameter groups.

Column | Type | Description
--- | --- | ---
id | INT | ID
object | VARCHAR | тип объекта, к которому относится процесс
title | CHAR | title

### param_group

Parameter groups parameters.

Column | Type | Description
--- | --- | ---
group_id | INT | group ID, link to param_group:id
param_id | INT | param ID, link to param_pref:id

### param_list

Parameter type list values.

Column | Type | Description
--- | --- | ---
id | INT | object (process, customer, user) ID
param_id | INT | param ID, link to param_pref:id
value | INT | value code, link to param_list_value:id or another table
comment | VARCHAR | value comment

### param_list_value

Possible values for parameters with type list.

Column | Type | Description
--- | --- | ---
id | INT | value ID
param_id | INT | param ID, link to param_pref:id
title | VARCHAR | value title

### param_listcount

Parameter type listcount values.

Column | Type | Description
--- | --- | ---
id | INT | object (process, customer, user) ID
param_id | INT | param ID, link to param_pref:id
value | INT | value
count | DECIMAL | amount

### param_listcount_value

Справочник значений параметров типа "listcount".

Column | Type | Description
--- | --- | ---
id | INT | ID
param_id | INT | param ID, link to param_pref:id
title | VARCHAR | value

### param_money

Parameter type money values.

Column | Type | Description
--- | --- | ---
id | INT | object (process, customer, user) ID
param_id | INT | param ID, link to param_pref:id
value | DECIMAL | value

### param_log

Лог изменения параметров со старым и новым значениями для выборки изменений сразу всех параметров объектов.

Column | Type | Description
--- | --- | ---
dt | DATETIME | дата изменения
object_id | INT | object (process, customer, user) ID
user_id | INT | user ID, link to user:id
param_id | INT | param ID, link to param_pref:id
text | VARCHAR | change description

### param_phone

Параметры типа "телефон"

Column | Type | Description
--- | --- | ---
id | INT | ID
param_id | INT | param ID, link to param_pref:id
value | TEXT | value

### param_phone_item

Телефоны

Column | Type | Description
--- | --- | ---
id | INT | ID
param_id | INT | param ID, link to param_pref:id
n | TINYINT | порядковый номер телефона в списке
phone | VARCHAR | телефонный номер, только цифры без форматирования
format | VARCHAR | формат номера
comment | VARCHAR | comment

### param_pref

Перечень параметров

Column | Type | Description
--- | --- | ---
id | INT | ID
object | VARCHAR | Тип объекта, к которому относится параметр
type | VARCHAR | тип параметра
title | VARCHAR | title
order | INT | порядковый номер(в интерфейсе crm)
script | TEXT |
config | TEXT |
comment | VARCHAR | comment

### param_text

Cтроковые параметры

Column | Type | Description
--- | --- | ---
id | INT | ID
param_id | INT | param ID, link to param_pref:id
value | VARCHAR | value

### param_tree

Параметры типа "дерево"

Column | Type | Description
--- | --- | ---
id | INT | ID
param_id | INT | param ID, link to param_pref:id
value | VARCHAR | value

### param_tree_value

Значения параметров типа "дерево"

Column | Type | Description
--- | --- | ---
id | INT | ID
param_id | INT | param ID, link to param_pref:id
parent_id | INT | ID родителя
title | VARCHAR | value

### process

Процессы

Column | Type | Description
--- | --- | ---
id | INT | уникальный process ID
type_id | INT | ID типа процесса, link to process_type:id
priority | TINYINT | приоритет процесса
status_id | INT | ID статуса процесса, link to process_status_title:id
status_dt | DATETIME | время последнего изменения статуса
status_user_id | INT | ID пользователя, сделавшего последнее изменение статуса, link to user:id
description | TEXT | описание процесса
create_dt | DATETIME | время создания процесса
create_user_id | INT | ID пользователя, создавшего процесс, link to user:id
close_dt | DATETIME | время закрытия процесса
close_user_id | INT | ID пользователя, закрывшего процесс, link to user:id
groups | CHAR(100) | IDы групп решения с ролями, избыточное поле для ускорения выборки
executors | CHAR(100) | IDы исполнителей, избыточное поле для ускорения выборки
title | CHAR(200) | не используется

### process_executor

Исполнители процессов.

Column | Type | Description
--- | --- | ---
process_id | INT | process ID, link to process:id
group_id | INT | ID группы
role_id | INT | execution role ID from configuration
user_id | INT | user ID, link to user:id

### process_group

Группы решения процессов.

Column | Type | Description
--- | --- | ---
process_id | INT | process ID, link to process:id
group_id | INT | group ID, link to user_group_title:id
role_id | INT | execution role ID from configuration

### process_link

Привязка процессов к объектам, включая и другие процессы.

Column | Type | Description
--- | --- | ---
process_id | INT | process ID, link to process:id
object_type | VARCHAR | тип привязанного объекта, привязанные процессы: processLink, processDepend, processMade, контрагент: customer
object_id | INT | ID привязанного объекта
object_title | VARCHAR | title привязанного объекта (избыточное поле)
config | VARCHAR | configuration in key-value format

### process_log

Лог изменений процессов.

Column | Type | Description
--- | --- | ---
id | INT | process ID, link to process:id
dt | DATETIME | время
user_id | INT | ID пользователя сделавшего изменение
data | TEXT | текст с описанием изменения

### process_message_state

Process messages state.

Column | Type | Description
--- | --- | ---
process_id | INT | process ID, link to process:id
in_last_dt | DATETIME | last incoming message time from message:from_dt
out_last_dt | DATETIME | last outgoing message time from message:from_dt
in_count | INT | count of incoming messages
in_unread_count | INT | count of unread incoming messages
in_last_id | INT | last incoming message ID, link to message:id
out_last_id | INT | last outgoing message ID, link to message:id

### process_open_secret

Generated secrets for accessing processes via open interface.

Column | Type | Description
--- | --- | ---
id | INT | process ID, link to process:id
secret | CHAR(32) | generated secret

### process_status

Лог изменений статуса процессов.

Column | Type | Description
--- | --- | ---
process_id | INT | process ID, link to process:id
dt | DATETIME | change time
status_id | INT | status ID, link to process_status_title:id
user_id | INT | user ID, link to user:id
last | TINYINT | 1 - if the change was the latest for the status in the process
comment | VARCHAR | change comment

### process_status_title

Статусы процессов

Column | Type | Description
--- | --- | ---
id | INT | ID
pos | INT | порядковый номер(в интерфейсе crm)
title | VARCHAR | title

### process_type

типы процессов

Column | Type | Description
--- | --- | ---
id | INT | ID
title | VARCHAR | title
archive | TINYINT |
parent_id | INT | ID типа-родителя
use_parent_props | TINYINT | 1-дочерний тип наследует все св-ва родителя, 0-не неследует
child_count | INT | кол-во дочерних типов
data | TEXT |
config | TEXT |
last_modify_user_id | INT | ID пользователя сделавшего последнее изменение
last_modify_dt | DATETIME | время последнего изменения

### process_type_param

Конфигурация параметров типа процесса

Column | Type | Description
--- | --- | ---
type_id | INT | ID типа
param_id | INT | param ID, link to param_pref:id

### process_type_status

Конфигурация статусов типа процесса

Column | Type | Description
--- | --- | ---
type_id | INT | ID типа
status_id | INT | ID статуса
pos | INT | порядковый номер(в интерфейсе crm)

### user

Users

Column | Type | Description
--- | --- | ---
id | INT | ID
title | VARCHAR | name
login | VARCHAR | login
pswd | VARCHAR | password
comment | VARCHAR | comment
create_dt | DATETIME | creation time
status | INT | status (0-active, 1-disabled, 2-external)
config | TEXT | configuration
personalization | TEXT | personal iface settings

### user_group

Группы пользователей.

Column | Type | Description
--- | --- | ---
user_id | INT | ID пользователя, link to user:id
group_id | INT | ID группы, link to user_group_title:id
date_from | DATE | в группе с
date_to | DATE | в группе до

### user_group_title

User groups

Column | Type | Description
--- | --- | ---
id | INT | ID
title | VARCHAR | title
comment | VARCHAR | comment
config | TEXT | configuration
parent_id | INT | parent group ID, link to user_group_title:id
child_count | DATE | quantity of child groups

### user_group_permset

Relation user groups to permission sets.

Column | Type | Description
--- | --- | ---
group_id | INT | group ID, link to user_group_title:id
permset_id | INT | permission set ID, link to user_permset_title:id
pos | INT | order position

### user_permission

Personal user permissions.

Column | Type | Description
--- | --- | ---
user_id | INT | user ID, link to user:id
action | VARCHAR | Action class and method names.
config | VARCHAR | Action’s configuration options.

### user_permset

Relation user to permission sets.

Column | Type | Description
--- | --- | ---
user_id | INT | user ID, link to user:id
permset_id | INT | permission set ID, link to user_permset_title:id
pos | INT | order position

### user_permset_permission

Permission set' permissions.

Column | Type | Description
--- | --- | ---
permset_id | INT | permission set ID, link to user_permset_title:id
action | VARCHAR | Action class and method names.
config | VARCHAR | Action’s configuration options.

### user_permset_title

Permission sets.

Column | Type | Description
--- | --- | ---
id | INT | ID
title | VARCHAR | Title.
comment | VARCHAR | Comment.
config | TEXT | Key-value configuration.

## Plugin Invoice

### invoice

Invoices.

Column | Type | Description
--- | --- | ---
id | INT | ID
type_id | INT | invoice type ID, defined in configuration
date_from | DATE | first day of payed period
date_to | DATE | last day of payed period, NULL for month invoices
process_id | INT | process ID, link to process:id
number_cnt | INT | counter for number generation
number | CHAR | generated unique number
amount | DECIMAL | invoice amount
create_dt | DATETIME | creation time
create_user_id | INT | creation user ID, link to user:id
sent_dt | DATETIME | invoice sending time
sent_user_id | INT | sending user ID, link to user:id
payment_date | DATE | payment date
payment_user_id | INT | payment user ID, link to user:id
positions | TEXT | JSON serialized positions

### invoice_position_yyyyMM

Invoice positions.

Column | Type | Description
--- | --- | ---
invoice_id | INT | invoice ID, link to invoice:id
id | CHAR | string position ID
amount | DECIMAL | amount
title | CHAR | human readable title

## Плагин FullText

### fulltext_data

Полнотекстовый индекс.

Column | Type | Description
--- | --- | ---
object_type | VARCHAR | тип индексированного объекта
object_id | INT | идентификатор индексированного объекта
scheduled_dt | DATETIME | запланированное время индексирования, NULL - индексирование выполненно
data | MEDIUMTEXT | текст

## Group Plan

## grpl_board_group

Execution group shifts

Column | Type | Description
--- | --- | ---
board_id | INT | board ID from configuration
column_id | INT | column ID from configuration
date | DATE | day
group_id | INT | group ID, link to user_group_title:id

## grpl_board_process

Process board assignments

Column | Type | Description
--- | --- | ---
board_id | INT | board ID from configuration
process_id | INT | process ID, link to process:id
duration | INT | process execution duration in minutes
column_id | INT | column ID from configuration
date | DATE | day
time | TIME | time of the day

## Плагин Task

### task

Задачи процессов.

Column | Type | Description
--- | --- | ---
id | INT | уникальный идентификатор задачи
type_id | VARCHAR | строковый идентификатор типа задачи из конфигурации
process_id | INT | процесс, к которому относится задача, ссылка на process:id.
scheduled_dt | DATETIME | запланированное время выполнения
executed_dt | DATETIME | фактическое время выполнения
config | TEXT | конфигурация задачи в формате ключ-значение
log | TEXT | поле не используется

## Plugin Team

### team_party

Parties.

Column | Type | Description
--- | --- | ---
id | INT | ID
title | VARCHAR | Title.
secret | VARCHAR | Generated secret string.
create_dt | DATETIME | Creation time.

### team_party_member

Party members.

Column | Type | Description
--- | --- | ---
id | INT | ID
party_id | INT | party ID, link to team_party:id
title | VARCHAR | Member title.

### team_party_payment

Party payment.

Column
Type
Description
id
INT
ID
party_id
INT
party ID, link to team_party:id
member_id
INT
member ID, link to team_party_member:id
amount
DECIMAL
Payment amount.
description
VARCHAR
Payment description.

## Plugin Secret

### secret_open

Secrets for open interface.

Column | Type | Description
--- | --- | ---
id | CHAR(20) | unique secret ID, e.g. 'process-13432'
secret | CHAR(32) | secret value
dt | DATETIME | last update time

## Plugin DBA

### dba_query_history

History of entered queries.

Column | Type | Description
--- | --- | ---
id | INT | ID
last_dt | DATETIME | Timestamp of last such query
user_id | INT | user ID, link to user:id
data | TEXT | The text of entered query.

## Plugin Log

### log_action_yyyyMM

Action Logs.

Column | Type | Description
--- | --- | ---
id | INT | ID
user_id | INT | user ID, link to user:id
ip_address | VARCHAR | IP address of requesting user
action | VARCHAR | Semicolon separated action class and method.
parameters | TEXT | HTTP request parameters.
time | DATETIME | Request time.
duration | BIGING | Execution duration in milliseconds.
error | TEXT | Exception message for incorrect execution.