Skip to main content

MyEMS Database Design: 13 Databases

Table of Contents​

Database architecture design​

MyEMS uses 13 MySQL databases. Install scripts live in database/install. Each script creates one database with utf8mb4 / utf8mb4_unicode_ci. Tables use BIGINT primary keys, UTC DATETIME columns, and DECIMAL(21, 6) for measured and aggregated values.

DatabaseTablesPurpose
myems_system_db166Master data: spaces, meters, points, equipment, plants, tariffs, and associations
myems_historical_db12Raw point values and uploaded cost, offline-meter, repair, and plan files
myems_energy_db39Hourly energy aggregation
myems_billing_db39Hourly cost aggregation, same table set as energy
myems_carbon_db39Hourly carbon aggregation, same table set as energy
myems_energy_baseline_db39Hourly baseline, same table set as energy
myems_energy_model_db398,760-hour typical year, same object set as energy
myems_energy_plan_db39Hourly plan, same table set as energy
myems_energy_prediction_db39Hourly prediction, same table set as energy
myems_fdd_db9Fault rules and email, SMS, web, and WeChat messages
myems_user_db10Users, privileges, sessions, API keys, and audit logs
myems_reporting_db4Report definitions, templates, generated files, and report emails
myems_production_db8Products, teams, shifts, and hourly production counts

Design concept​

DatabaseResponsibilityWriter
myems_system_dbConfiguration and topologyWeb UI through myems-api
myems_historical_dbPoint time series and uploaded workbooksData acquisition and file import
myems_energy_dbHourly energyNormalization and aggregation
myems_billing_dbHourly costBilling, from energy values and tariffs
myems_carbon_dbHourly emissionsCarbon calculation, from energy values and emission factors
myems_energy_baseline_dbHourly baselineBaseline service
myems_energy_model_dbOne value for each hour of a non-leap yearModeling service
myems_energy_plan_dbHourly planPlanning service
myems_energy_prediction_dbHourly forecastPrediction service
myems_fdd_dbRules and outbound notificationsFDD service
myems_user_dbIdentity and privilegesAPI
myems_reporting_dbScheduled reportsReporting service
myems_production_dbOutput counts used with energy intensityProduction import and aggregation

Configuration objects reference each other by id. Cross-database tables store that same id and do not declare foreign keys to another database.

Naming conventions​

ObjectRuleExample
Databasemyems_ prefix, _db suffixmyems_system_db
Tabletbl_ prefixtbl_meters
Association tabletbl_{owner}_{child}tbl_spaces_meters
Hourly fact table{object}_{measure}_hourlytbl_meter_hourly
Model fact tablesame object set, _8760 suffixtbl_meter_8760
Primary keyidid BIGINT NOT NULL AUTO_INCREMENT
Business keyuuid CHAR(36) on configuration objectsmeter, space, equipment
Time*_datetime_utc or utc_date_timestart_datetime_utc
Index{table}_index_{n}tbl_meters_index_1

Detailed database description​

1. System Database (myems_system_db)​

myems_system_db holds master data. It has 166 tables. Configuration objects usually carry id, name, uuid, and description. Association tables only store the two foreign keys (plus id). Component tables under a plant store one row per physical device and a companion *_points table.

Energy classification​

TableKey fieldsDescription
tbl_energy_categoriesname, uuid, unit_of_measure, kgce, kgco2eEnergy category and default conversion factors
tbl_energy_itemsname, uuid, energy_category_idItem under a category

kgce and kgco2e are default factors on the category. Time-bounded prices and emission factors live in the tariff and emission-factor tables.

Meters and points​

TableKey fieldsDescription
tbl_metersenergy_category_id, energy_item_id, cost_center_id, master_meter_id, is_counted, is_enabled, hourly_low_limit, hourly_high_limitOnline meter
tbl_offline_meterssame limits and category fields, no master_meter_idManually entered meter
tbl_virtual_metersequation (LONGTEXT), energy_category_id, energy_item_id, is_counted, is_enabledMeter calculated from an equation
tbl_variablesname, virtual_meter_id, meter_type, meter_idVariable binding inside a virtual-meter equation
tbl_pointsdata_source_id, object_type, units, high_limit, low_limit, higher_limit, lower_limit, ratio, offset_constant, is_trend, is_virtual, address, faults, definitionsData point. address, faults, and definitions are JSON text. Points have no uuid
tbl_points_set_valuespoint_id, utc_date_time, set_value, is_setCommanded set value for a point
tbl_meters_pointsmeter_id, point_idMeter-to-point binding
tbl_meters_commandsmeter_id, command_idMeter-to-command binding

higher_limit, lower_limit, and is_in_alarm on tbl_points are used by the FDD service. ratio and offset_constant scale the raw value.

Data acquisition​

TableKey fieldsDescription
tbl_gatewaysname, uuid, token, last_seen_datetime_utcAcquisition gateway
tbl_data_sourcesgateway_id, protocol, connection (LONGTEXT), process_id, last_seen_datetime_utcConnection under a gateway. connection is JSON
tbl_protocolsname, codeProtocol catalog
tbl_sensorsname, uuidSensor
tbl_sensors_pointssensor_id, point_idSensor-to-point binding
tbl_iot_sim_cardsiccid, imsi, operator, status, used_traffic, total_trafficSIM card inventory. This table has no uuid

Spaces and cost centers​

TableKey fieldsDescription
tbl_spacesparent_space_id, area, number_of_occupants, timezone_id, contact_id, cost_center_id, latitude, longitude, is_input_counted, is_output_countedSpace tree
tbl_cost_centersname, uuid, external_idCost center
tbl_contactsname, uuid, email, phoneContact
tbl_timezonesname, utc_offsetTime zone
tbl_working_calendarsnameWorking calendar
tbl_working_calendars_non_working_daysworking_calendar_id, date_local, descriptionNon-working day

A space links to child objects through association tables, not through a space_id column on those objects:

tbl_spaces_meters, tbl_spaces_offline_meters, tbl_spaces_virtual_meters, tbl_spaces_points, tbl_spaces_sensors, tbl_spaces_equipments, tbl_spaces_combined_equipments, tbl_spaces_commands, tbl_spaces_shopfloors, tbl_spaces_stores, tbl_spaces_tenants, tbl_spaces_charging_stations, tbl_spaces_microgrids, tbl_spaces_photovoltaic_power_stations, tbl_spaces_energy_storage_power_stations, tbl_spaces_wind_farms, tbl_spaces_distribution_systems, tbl_spaces_energy_flow_diagrams, tbl_spaces_working_calendars.

Equipment​

TableKey fieldsDescription
tbl_equipmentsis_input_counted, is_output_counted, is_enabled, cost_center_id, efficiency_indicator, svg_id, camera_urlEquipment
tbl_combined_equipmentssame fields as tbl_equipmentsCombined equipment
tbl_combined_equipments_equipmentscombined_equipment_id, equipment_idMembers of a combined equipment

Both equipment types bind meters, offline meters, virtual meters, parameters, data sources, and commands:

  • tbl_equipments_meters, tbl_equipments_offline_meters, tbl_equipments_virtual_meters, tbl_equipments_parameters, tbl_equipments_data_sources, tbl_equipments_commands
  • tbl_combined_equipments_meters, tbl_combined_equipments_offline_meters, tbl_combined_equipments_virtual_meters, tbl_combined_equipments_parameters, tbl_combined_equipments_data_sources, tbl_combined_equipments_commands

Shopfloors, stores, and tenants​

TableKey fieldsDescription
tbl_shopfloorsarea, is_input_counted, is_enabled, contact_id, cost_center_idShopfloor
tbl_storesaddress, latitude, longitude, area, store_type_id, is_input_counted, is_enabled, contact_id, cost_center_idStore
tbl_store_typesname, uuid, simplified_codeStore type
tbl_tenantsbuildings, floors, rooms, area, tenant_type_id, is_input_counted, is_enabled, is_key_tenant, lease_number, lease_start_datetime_utc, lease_end_datetime_utc, is_in_lease, contact_id, cost_center_idTenant
tbl_tenant_typesname, uuid, simplified_codeTenant type

Each of these three objects has association tables for meters, offline meters, virtual meters, points, sensors, commands, and working calendars. Shopfloors also have tbl_shopfloors_equipments.

Photovoltaic power stations​

tbl_photovoltaic_power_stations stores station_code, address, latitude, longitude, latitude_point_id, longitude_point_id, rated_capacity, rated_power, contact_id, cost_center_id, five SVG slots (svg_id through svg5_id), is_cost_data_displayed, phase_of_lifecycle, and commissioning_date.

Child tables:

TableRole
tbl_photovoltaic_power_stations_invertors / _invertors_pointsInverters and their points
tbl_photovoltaic_power_stations_grids / _grids_pointsGrid connections and their points
tbl_photovoltaic_power_stations_loads / _loads_pointsLoads and their points
tbl_photovoltaic_power_stations_data_sourcesData sources
tbl_photovoltaic_power_stations_usersBound users

Energy storage​

tbl_energy_storage_containers stores rated_capacity, rated_power, contact_id, and cost_center_id. Component tables, each with a companion *_points table where listed:

Component tablePoints table
tbl_energy_storage_containers_batteries (soc_point_id, power_point_id, charge_meter_id, discharge_meter_id)—
tbl_energy_storage_containers_power_conversion_systemstbl_energy_storage_containers_pcses_points
—tbl_energy_storage_containers_bmses_points (bms_id, point_id; no BMS master table)
tbl_energy_storage_containers_dcdcstbl_energy_storage_containers_dcdcs_points
tbl_energy_storage_containers_stsestbl_energy_storage_containers_stses_points
tbl_energy_storage_containers_gridstbl_energy_storage_containers_grids_points
tbl_energy_storage_containers_loadstbl_energy_storage_containers_loads_points
tbl_energy_storage_containers_hvacstbl_energy_storage_containers_hvacs_points
tbl_energy_storage_containers_firecontrolstbl_energy_storage_containers_firecontrols_points

Also: tbl_energy_storage_containers_commands, tbl_energy_storage_containers_data_sources, tbl_energy_storage_containers_schedules.

tbl_energy_storage_power_stations uses the same station fields as a photovoltaic station (address, coordinates, rated capacity and power, contact, cost center, five SVGs, lifecycle). It groups containers in tbl_energy_storage_power_stations_containers and users in tbl_energy_storage_power_stations_users.

Microgrids​

tbl_microgrids adds postal_code and serial_number to the station field set. Child tables:

ComponentPoints
tbl_microgrids_batteries—
—tbl_microgrids_bmses_points (bms_id, point_id; no BMS master table)
tbl_microgrids_power_conversion_systemstbl_microgrids_pcses_points
tbl_microgrids_photovoltaicstbl_microgrids_pvs_points
tbl_microgrids_evchargerstbl_microgrids_evchargers_points
tbl_microgrids_generatorstbl_microgrids_generators_points
tbl_microgrids_gridstbl_microgrids_grids_points
tbl_microgrids_heatpumpstbl_microgrids_heatpumps_points
tbl_microgrids_loadstbl_microgrids_loads_points

Also: tbl_microgrids_commands, tbl_microgrids_data_sources, tbl_microgrids_schedules, tbl_microgrids_sensors, tbl_microgrids_users.

Other plants and integrators​

TableKey fieldsDescription
tbl_charging_stationsaddress, coordinates, rated_capacity, rated_power, contact, cost center, five SVGs, lifecycleCharging station
tbl_wind_farmsaddress, coordinates, rated_power, contact, cost center, five SVGsWind farm
tbl_virtual_power_plantscost_center_id, balancing_price_point_id, svg_idVirtual power plant
tbl_virtual_power_plants_microgridsvirtual_power_plant_id, microgrid_idMicrogrids in a virtual power plant
tbl_power_integratorspower_point_id, result_point_id, is_enabledIntegrate power into energy
tbl_fuel_integratorsmodel, power_point_id, result_point_id, is_enabledFuel integrator
tbl_heat_integratorshigh_temperature_point_id, low_temperature_point_id, flow_point_id, heat_capacity, liquid_density, coefficient, result_point_id, is_enabledHeat integrator

Commands and control​

TableKey fieldsDescription
tbl_commandstopic, payload (LONGTEXT), set_valueCommand definition
tbl_control_modesname, uuid, is_activeControl mode
tbl_control_modes_timescontrol_mode_id, start_time_of_day, end_time_of_day, power_value, power_point_id, power_equationOne period inside a control mode

Tariffs and emission factors​

TableKey fieldsDescription
tbl_tariffsenergy_category_id, tariff_type, unit_of_price, valid_from_datetime_utc, valid_through_datetime_utcTariff. tariff_type is timeofuse
tbl_tariffs_timeofusestariff_id, start_time_of_day, end_time_of_day, peak_type, price DECIMAL(23, 8)Price of one daily period. peak_type is toppeak, onpeak, midpeak, offpeak, or deep
tbl_cost_centers_tariffscost_center_id, tariff_idTariff binding
tbl_emission_factorsenergy_category_id, unit_of_factor, valid_from_datetime_utc, valid_through_datetime_utcTime-bounded emission factor
tbl_emission_factors_timeofusesemission_factor_id, start_time_of_day, end_time_of_day, factorFactor of one daily period
tbl_cost_centers_emission_factorscost_center_id, emission_factor_idFactor binding

Diagrams, menus, files, and work orders​

TableKey fieldsDescription
tbl_distribution_systemsname, uuid, svg_idDistribution system
tbl_distribution_circuitsdistribution_system_id, distribution_room, switchgear, peak_load, peak_current, customers, metersCircuit
tbl_distribution_circuits_pointsdistribution_circuit_id, point_idCircuit points
tbl_energy_flow_diagramsname, uuidEnergy-flow diagram
tbl_energy_flow_diagrams_nodesenergy_flow_diagram_id, nameNode
tbl_energy_flow_diagrams_linksenergy_flow_diagram_id, source_node_id, target_node_id, meter_uuidLink
tbl_svgsname, uuid, source_code (LONGTEXT)SVG graphic
tbl_menusname, route, parent_menu_id, is_hiddenMenu tree. No uuid
tbl_knowledge_filesfile_name, uuid, upload_datetime_utc, upload_user_uuid, file_object (LONGBLOB)Knowledge-base file
tbl_work_ordersnumber, category, title, priority, status, belong_to_object_type, belong_to_object_id, source_type, source_id, creator_user_id, assignee_user_id, scheduled and actual timestampsWork order
tbl_work_order_logswork_order_id, user_id, action, from_status, to_status, comment, created_datetime_utcStatus log
tbl_work_order_fileswork_order_id, file_name, file_path, file_size, uploaded_by_user_idAttachment path. File bytes are stored on disk
tbl_versionsversion, release_dateSchema version

2. Historical Database (myems_historical_db)​

myems_historical_db stores raw point values and uploaded workbooks. It has 12 tables.

Each value family has a history table and a latest table. History rows include is_bad and is_published. Latest rows keep point_id, utc_date_time, and actual_value only. Both are indexed by (point_id, utc_date_time) and by utc_date_time.

Tableactual_value typeDescription
tbl_analog_value, tbl_analog_value_latestDECIMAL(21, 6)Analog
tbl_energy_value, tbl_energy_value_latestDECIMAL(21, 6)Cumulative energy
tbl_digital_value, tbl_digital_value_latestINTDigital
tbl_text_value, tbl_text_value_latestLONGTEXTText

Uploaded files share file_name, uuid, upload_datetime_utc, status (new, done, error), and file_object (LONGBLOB):

TablePurpose
tbl_cost_filesCost workbook
tbl_offline_meter_filesOffline-meter workbook
tbl_data_repair_filesData-repair workbook
tbl_energy_plan_filesEnergy-plan workbook

3. Energy Database (myems_energy_db)​

myems_energy_db stores hourly actual energy. It has 39 tables and no daily, monthly, or yearly tables. Reports roll hours up in queries.

Every table has id, a parent object id, start_datetime_utc, and actual_value DECIMAL(21, 6). Category tables also have energy_category_id. Item tables also have energy_item_id. The composite index is (object_id, [category or item id], start_datetime_utc).

GroupTables
Meterstbl_meter_hourly, tbl_offline_meter_hourly, tbl_virtual_meter_hourly
Equipmenttbl_equipment_input_category_hourly, tbl_equipment_input_item_hourly, tbl_equipment_output_category_hourly
Combined equipmenttbl_combined_equipment_input_category_hourly, tbl_combined_equipment_input_item_hourly, tbl_combined_equipment_output_category_hourly
Spacetbl_space_input_category_hourly, tbl_space_input_item_hourly, tbl_space_output_category_hourly
Shopfloortbl_shopfloor_input_category_hourly, tbl_shopfloor_input_item_hourly
Storetbl_store_input_category_hourly, tbl_store_input_item_hourly
Tenanttbl_tenant_input_category_hourly, tbl_tenant_input_item_hourly
Energy storage containertbl_energy_storage_container_{charge,discharge,grid_buy,grid_sell,load}_hourly
Energy storage power stationtbl_energy_storage_power_station_{charge,discharge,grid_buy,grid_sell,load}_hourly
Microgridtbl_microgrid_{charge,discharge,evcharger,grid_buy,grid_sell,load,photovoltaic}_hourly
Photovoltaic power stationtbl_photovoltaic_power_station_{generation,grid_buy,grid_sell,load}_hourly

Meter, offline-meter, virtual-meter, storage, microgrid, and photovoltaic tables have no energy-category column. The category is implied by the parent object.

4. Billing Database (myems_billing_db)​

myems_billing_db has the same 39 hourly tables and the same columns as myems_energy_db. actual_value is cost in the tariff currency. Billing reads hourly energy and the tariff bound to the cost center.

5. Carbon Database (myems_carbon_db)​

myems_carbon_db has the same 39 hourly tables. actual_value is emissions. Calculation uses hourly energy together with tbl_energy_categories.kgco2e or the time-of-use rows in tbl_emission_factors.

6. Energy Baseline Database (myems_energy_baseline_db)​

myems_energy_baseline_db has the same 39 hourly tables. actual_value is the baseline for that hour.

7. Energy Model Database (myems_energy_model_db)​

myems_energy_model_db has the same 39 object groups. Table names end in _8760 instead of _hourly, and the time column is hour_of_year (INT) instead of start_datetime_utc. Example: tbl_meter_8760(meter_id, hour_of_year, actual_value).

8. Energy Plan Database (myems_energy_plan_db)​

myems_energy_plan_db has the same 39 hourly tables as myems_energy_db. actual_value is the planned quantity. Plan workbooks land first in myems_historical_db.tbl_energy_plan_files.

9. Energy Prediction Database (myems_energy_prediction_db)​

myems_energy_prediction_db has the same 39 hourly tables. actual_value is the predicted quantity.

10. FDD Database (myems_fdd_db)​

myems_fdd_db has 9 tables.

TableKey fieldsDescription
tbl_rulesname, uuid, category, fdd_code, priority, channel, expression, message_template, is_enabled, last_run_datetime_utc, next_run_datetime_utc, is_run_immediatelyFault rule. expression is JSON. message_template is plain text with $ substitutions
tbl_email_servershost, port, requires_authentication, user_name, password, from_addrSMTP server
tbl_email_messagesrule_id, recipient_email, subject, message, attachment_file_object, scheduled_datetime_utc, statusOutbound email
tbl_text_messages_outboxrule_id, recipient_mobile, message, acknowledge_code, statusOutbound SMS
tbl_text_messages_inboxsender_mobile, message, received_datetime_utc, statusInbound SMS
tbl_web_messagesrule_id, user_id, subject, category, priority, message, status, reply, belong_to_object_type, belong_to_object_idWeb message
tbl_wechat_configsapi_server, app_id, app_secret, access_token, expires_datetime_utcWeChat app
tbl_wechat_messages_outboxrule_id, recipient_openid, message_template_id, message_data, statusOutbound WeChat
tbl_wechat_messages_inboxsender_openid, message, received_datetime_utc, statusInbound WeChat

tbl_rules.category is one of REALTIME, SYSTEM, SPACE, METER, TENANT, STORE, SHOPFLOOR, EQUIPMENT, COMBINEDEQUIPMENT. priority is CRITICAL, HIGH, MEDIUM, or LOW. channel is WEB, EMAIL, SMS, WECHAT, or CALL. fdd_code identifies the rule, for example REALTIME01 or SPACE02.

11. User Database (myems_user_db)​

myems_user_db has 10 tables.

TableKey fieldsDescription
tbl_usersname, uuid, display_name, email, phone, salt, password, is_admin, is_read_only, privilege_id, account_expiration_datetime_utc, password_expiration_datetime_utc, failed_login_countUser. password is the salted hash (VARCHAR(256))
tbl_privilegesname, data (LONGTEXT, JSON)Privilege set
tbl_sessionsuser_uuid, token, utc_expiresLogin session
tbl_logsuser_uuid, request_datetime_utc, request_method, resource_type, resource_id, request_bodyAPI audit log. request_body is JSON
tbl_api_keysname, token, created_datetime_utc, expires_datetime_utcAPI key. id is INT
tbl_new_usersname, uuid, display_name, email, salt, passwordPending registration
tbl_email_messagesrecipient_email, subject, message, scheduled_datetime_utc, statusUser mail such as password reset. status is new, sent, or timeout
tbl_email_message_sessionsrecipient_email, token, expires_datetime_utcToken for a mail action
tbl_verification_codesrecipient_email, verification_code, created_datetime_utc, expires_datetime_utcEmail verification code. id is INT
tbl_notificationsuser_id, created_datetime_utc, status, subject, message, urlIn-app notification. status is unread, read, or archived

12. Reporting Database (myems_reporting_db)​

myems_reporting_db has 4 tables.

TableKey fieldsDescription
tbl_reportsname, uuid, expression, is_enabled, last_run_datetime_utc, next_run_datetime_utc, is_run_immediatelyReport schedule. expression is JSON
tbl_template_filesuuid, report_id, file_name, file_type, file_objectTemplate bound to a report
tbl_reports_filesuuid, create_datetime_utc, file_name, file_type, file_objectGenerated file
tbl_email_messagesrecipient_email, subject, message, attachment_file_object, scheduled_datetime_utc, statusReport delivery mail. This table has no rule_id

13. Production Database (myems_production_db)​

myems_production_db has 8 tables. Hourly product counts share start_datetime_utc, product_id, and product_count.

TableKey fieldsDescription
tbl_productsname, uuid, unit_of_measure, tag, standard_product_coefficientProduct
tbl_teamsname, uuid, descriptionTeam
tbl_shopfloors_productsshopfloor_id, product_idProducts made on a shopfloor
tbl_shopfloors_teamsshopfloor_id, team_idTeams on a shopfloor
tbl_shiftsshopfloor_id, team_id, product_id, product_count, start_datetime_utc, end_datetime_utc, reference_timestampShift record
tbl_equipment_hourlyequipment_id, start_datetime_utc, product_id, product_countHourly output of one equipment
tbl_shopfloor_hourlyshopfloor_id, start_datetime_utc, product_id, product_countHourly output of a shopfloor
tbl_space_hourlyspace_id, start_datetime_utc, product_id, product_countHourly output rolled up to a space

Data flow relationship​

Processing order:

  1. Gateways write point values into myems_historical_db. Uploaded workbooks land in the file tables of the same database.
  2. Normalization turns point values into meter readings, using ratio and offset_constant from tbl_points.
  3. Aggregation writes hourly rows in myems_energy_db for meters and for each parent object (equipment, space, store, tenant, shopfloor, storage, microgrid, photovoltaic power station).
  4. Billing multiplies those hours by tbl_tariffs / tbl_tariffs_timeofuses and writes myems_billing_db.
  5. Carbon multiplies those hours by kgco2e or tbl_emission_factors and writes myems_carbon_db.
  6. Baseline, model, plan, and prediction services write their own databases. The model database stores hour_of_year instead of a timestamp.
  7. FDD evaluates tbl_rules.expression against point limits and aggregated values, then enqueues email, SMS, web, or WeChat messages.
  8. Reporting reads energy, billing, and carbon to fill tbl_template_files and stores the result in tbl_reports_files.
  9. Production hourly counts in myems_production_db are the denominator for energy intensity.

Table structure design specification​

Common fields​

Configuration tables in myems_system_db usually include:

FieldTypeDescription
idBIGINT NOT NULL AUTO_INCREMENTPrimary key
nameVARCHAR(128) or VARCHAR(255)Display name
uuidCHAR(36) NOT NULLStable business key
descriptionVARCHAR(255)Optional description

Time-series and association tables do not follow that set. Fact tables use id plus foreign keys and actual_value. Many association tables are only id and two foreign keys. tbl_points, tbl_menus, and tbl_iot_sim_cards have no uuid.

Data types​

UseType
Primary and foreign keysBIGINT (INT on tbl_api_keys and tbl_verification_codes)
Energy, cost, carbon, limits, areaDECIMAL(21, 6)
Tariff period priceDECIMAL(23, 8)
LatitudeDECIMAL(12, 10)
LongitudeDECIMAL(13, 10)
TimestampsDATETIME, stored in UTC
Calendar datesDATE
Time of dayTIME
FlagsBOOL
Short codes and statusVARCHAR
JSON documents (address, faults, definitions, connection, rule and report expression, privilege data, request_body)LONGTEXT
Equations (tbl_virtual_meters.equation, power_equation)LONGTEXT
Digital point valueINT
FilesLONGBLOB

Indexes​

Secondary indexes are named {table_name}_index_{n}.

PatternExample
Name lookuptbl_meters_index_1 (name)
Parent plus timetbl_meter_hourly_index_1 (meter_id, start_datetime_utc)
Category plus timetbl_space_input_category_hourly_index_1 (space_id, energy_category_id, start_datetime_utc)
Point historytbl_analog_value_index_1 (point_id, utc_date_time), tbl_analog_value_index_2 (utc_date_time)
Tariff validitytbl_tariffs_index_2 (energy_category_id, valid_from_datetime_utc, valid_through_datetime_utc)

The scripts do not declare foreign keys. Referential integrity is enforced by the API.