schema.md

MongoDB Schema Documentation

The JSON-SCADA MongoDB database is comprised of the following collections.

• realtimeData - This is the table that contains point information, tag names, point attributes and realtime values.
• protocolDriverInstances - Configuration of protocol drivers instances. See specific protocol for documentation.
• protocolConnections - Configuration of protocol connections. See specific protocol for documentation.
• commandsQueue - Queue for commands.
• soeData - Sequence of Events data. This is a Capped Collection, it has a limited size.
• processInstances - Configuration and information about JSON-SCADA instances of processes.

Please notice that all numeric fields from the schema is recorded as BSON Doubles (64 bit floating point). However, some numeric fields are expected to contain only integer values. When numbers are updated by the Mongo Shell manually, all numeric data is converted to BSON Doubles by default. Some languages like Node.js can cause values to be stored as integers or doubles depending on the current value. It is important that values are always stored as BSON Doubles as otherwise problems may be encountered by protocol drivers, specially those programmed in C#/DotNet Core.

All string parameters are UTF-8 encoded.

Dates are stored as MongoDB BSON Dates (UTC).

realtimeData collection

Example document.

{
    "_id": 3285.0,
    "alarmDisabled": false,
    "alarmRange": 0.0,
    "alarmState": -1.0,
    "alarmed": false,
    "alerted": false,
    "alertState": "ok",
    "annotation": "",
    "commandBlocked": false,
    "commandOfSupervised": 0.0,
    "commissioningRemarks": "",
    "description": "KAW2~FD21 13,8kV~Active power",
    "eventTextFalse": "undefined",
    "eventTextTrue": "undefined",
    "formula": 0.0,
    "frozen": false,
    "frozenDetectTimeout": 300.0,
    "group1": "KAW2",
    "group2": "FD21 13,8kV",
    "group3": "",
    "hiLimit": null,
    "hihiLimit": null,
    "hihihiLimit": null,
    "historianDeadBand": 0.0,
    "historianPeriod": 0.0,
    "historianLastValue": null,
    "hysteresis": 0.0,
    "invalid": false,
    "invalidDetectTimeout": 300.0,
    "isEvent": false,
    "kconv1": 1.0,
    "kconv2": 0.0,
    "loLimit": null,
    "location": null,
    "loloLimit": null,
    "lololoLimit": null,
    "notes": "",
    "origin": "supervised",
    "overflow": false,
    "parcels": null,
    "priority": 0.0,
    "protocolSourceASDU": 13.0,
    "protocolSourceCommandDuration": null,
    "protocolSourceCommandUseSBO": null,
    "protocolSourceCommonAddress": 1.0,
    "protocolSourceConnectionNumber": 61.0,
    "protocolSourceObjectAddress": 3285.0,
    "sourceDataUpdate": {
        "valueAtSource": 2.9959075450897217,
        "valueStringAtSource": "2.9959075450897217",
        "valueJsonAtSource": {},
        "valueBsonAtSource": null,
        "asduAtSource": "M_ME_NC_1",
        "causeOfTransmissionAtSource": "3",
        "timeTagAtSource": null,
        "timeTagAtSourceOk": false,
        "timeTag": { "$date": "2020-08-10T19:04:59.774Z" },
        "notTopicalAtSource": false,
        "invalidAtSource": false,
        "overflowAtSource": false,
        "blockedAtSource": false,
        "substitutedAtSource": false
    },
    "stateTextFalse": "undefined",
    "stateTextTrue": "undefined",
    "substituted": false,
    "supervisedOfCommand": 0.0,
    "tag": "KAW2AL-21MTWT",
    "timeTag": { "$date": "2020-08-10T19:04:59.785Z" },
    "timeTagAlarm": { "$date": "2020-07-13T20:24:54.126Z" },
    "timeTagAlertState": { "$date": "2020-08-10T19:04:60.000Z" },
    "timeTagAtSource": null,
    "timeTagAtSourceOk": null,
    "transient": false,
    "type": "analog",
    "ungroupedDescription": "Active power",
    "unit": "MW",
    "updatesCnt": 86580.0,
    "valueDefault": 3.1,
    "valueString": "2.9959 MW",
    "valueJson": {},
    "protocolDestinations": [
        {
        "protocolDestinationConnectionNumber": 1001.0,
        "protocolDestinationCommonAddress": 1.0,
        "protocolDestinationObjectAddress": 3285.0,
        "protocolDestinationASDU": 13.0,
        "protocolDestinationCommandDuration": 0.0,
        "protocolDestinationCommandUseSBO": false,
        "protocolDestinationKConv1": 1.0,
        "protocolDestinationKConv2": 0.0,
        "protocolDestinationGroup": 0.0,
        "protocolDestinationHoursShift": -2.0
        }
    ],
    "value": 2.9959075450897217,
    "zeroDeadband": 0.0
}

Configuration fields

• id [Double] - Numeric key for the point. This is stored as a BSON Double but should only contain integer values. Must be unique for the collection. Mandatory parameter.
• tag [String] - String key for the point. It must begin with a letter char (A-Z or a-z) or underscore. Allowed symbols are underscore, dash and dot. Do not use spaces or symbols like #/|*!^~%$. Tags beginning with "_System." are reserved for internal system data. There is no enforced limit for the size but we recommend to keep it below 30 characters to make displays more readable. Must be unique for the collection. Mandatory parameter.
• type [String] - Data type. Can be "digital", "analog", "string" or "json". Mandatory parameter.
• origin [String] - How the value is obtained. Can be "supervised", "calculated", "manual", or "command". Mandatory parameter.
• description [String] - Complete textual description of the tag information. Mandatory parameter.
• ungroupedDescription [String] - Textual description leave out grouping.
• group1 [String] - Main group (highest level). E.g. station or installation name. Mandatory parameter.
• group2 [String] - Secondary grouping. E.g. bay or area name. Mandatory parameter.
• group3 [String] - Lowest level grouping. E.g. device ir equipment name. Mandatory parameter.
• valueDefault [Double] - Numeric default value. Mandatory parameter.
• priority [String] - Alarm priority: 0=highest, 9=lowest. Mandatory parameter.
• frozenDetectTimeout [Double] - Time in seconds to detect frozen (not changing) analog value. Use zero to never detect. Mandatory parameter.
• invalidDetectTimeout [Double] - Time in seconds to detect invalid/old value when not updating. All supervised values expire, do not use zero for supervised values. This timeout should be at least more than the station integrity interrogation period. Mandatory parameter.
• historianDeadBand [Double] - Absolute dead band parameter for historian. Does not affect non analog tags. Mandatory parameter.
• historianPeriod [Double] - Period of integrity recording on historian. Currently only values 0 and -1 are supported. Value 0.0 will not record by integrity. Value -1 will remove tag from historian. Mandatory parameter.
• commandOfSupervised [Double] - Key (_id) pointing to the command point related to a supervised point. Only meaningful for origin=supervised points (put zero here for other origins). Put value zero for this parameter when the supervised point does not have a related command. Mandatory parameter.
• supervisedOfCommand [Double] - Key (_id) pointing to a supervised point related to a command point (tag where the command feedback manifests). Only meaningful for origin=command points (put zero here for other origins). Put value zero for this parameter when the command point does not have a related supervised (not recommended as this is a blind command with no feedback for the user). Mandatory parameter.
• location [GeoJSON] - Reserved for location coordinates. Currently not in use. Can be null. Mandatory parameter.
• isEvent [Boolean] - Flag meaning that only transitions OFF->ON for type=digital matters for alarms and SOE (commonly used for electrical protection events). For type=analog values it means that all valid changes of values should be recorded as SOE (future use). Mandatory parameter.
• unit [String] - Unit of measurement when type=analog. Mandatory parameter.
• alarmState [Double] - Considered state for alarm (0=off=false, 1=on=true, 2=both states, 3=state OFF->ON transition, -1=no state produces alarms but alarms can be signaled by other means) when type=digital. Mandatory parameter.
• stateTextTrue [String] - Text for state true (numeric value not zero) when type=digital. Normally expressed as present tense (e.g. "ON"). Mandatory parameter.
• stateTextFalse [String] - Text for state false (numeric value zero) when type=digital. Normally expressed as present tense (e.g. "OFF"). Mandatory parameter.
• eventTextTrue [String] - Text for state change false to true when type=digital. Normally expressed as past tense (e.g. "Switched ON"). Mandatory parameter.
• eventTextFalse [String] - Text for state change true to false when type=digital. Normally expressed as present tense (e.g. "Switched ON"). Mandatory parameter.
• formula [Double] - A formula code for calculation of value. See the Calculations section for documentation. Only meaningful when origin=calculated. Can be null for other origins. Mandatory parameter.
• parcels [Array of Double] - Numeric key references to parcel points for calculations. Only meaningful when origin=calculated. Can be null for other origins. Mandatory parameter.
• kconv1 [Double] - Conversion factor 1 (multiplier). Applied when origin=supervised, origin=command or origin=calculated. Use -1 to invert states of digital values and commands. Mandatory parameter.
• kconv2 [Double] - Conversion factor 2 (adder). Applied when origin=supervised or origin=calculated. Mandatory parameter.
• zeroDeadband [Double] - When acquired value is below this deadband it will be zeroed. Only meaningful for type=analog. Mandatory parameter.
• protocolSourceConnectionNumber [Double] - Indicates the protocol connection that can updated the point. Should contain only integer values. Only meaningful when origin=supervised or origin=command. Mandatory parameter.
• protocolSourceCommonAddress [Double or String] - Protocol common address (device address). Only meaningful when origin=supervised or origin=command. See protocol documentation. Mandatory parameter.
• protocolSourceObjectAddress [Double or String] - Protocol object address. Only meaningful when origin=supervised or origin=command. See protocol documentation. Mandatory parameter.
• protocolSourceASDU [Double or String] - Protocol information ASDU TI type. Only meaningful when origin=supervised or origin=command. See protocol documentation. Mandatory parameter.
• protocolSourceCommandDuration [Double] - Additional command specification. Only meaningful when origin=command. See protocol documentation. Mandatory parameter.
• protocolSourceCommandUseSBO [Boolean] - Use or not Select Before Operate for commands. Only meaningful when origin=command. See protocol documentation. Mandatory parameter.
• protocolDestinations [Array of Objects] - List of protocol destinations for server protocol connections. Can be null or empty array when not point is not to be distributed. See protocol documentation. Mandatory parameter.
• protocolDestinationConnectionNumber [Double] - Indicates the protocol connection that will monitor updates to the point. Should contain only integer values. Mandatory parameter.
• protocolDestinationCommonAddress [Double or String] - Protocol common address (device address). See protocol documentation. Mandatory parameter.
• protocolDestinationObjectAddress [Double or String] - Protocol object address. See protocol documentation. Mandatory parameter.
• protocolDestinationASDU [Double or String] - Protocol information ASDU TI type. See protocol documentation. Mandatory parameter.
• protocolDestinationCommandDuration [Double] - Additional command specification. See protocol documentation. Mandatory parameter.
• protocolDestinationCommandUseSBO [Boolean] - Use or not Select Before Operate for commands. See protocol documentation. Mandatory parameter.
• protocolDestinationKConv1 [Double] - Conversion factor 1 (multiplier). Mandatory parameter.
• protocolDestinationKConv2 [Double] - Conversion factor 2 (adder). Mandatory parameter.
• protocolDestinationGroup [Double or String] - Group number or dataset id of points. See protocol documentation. Mandatory parameter.
• protocolDestinationHoursShift [Double] - Number of hours to add to timestamps. Mandatory parameter.

Fields that can be configured and altered by the operators.

• hiLimit [Double] - High limit for out-of-range alarm. Use null, Infinity or a big value to avoid alarm. Only meaningful for type=analog. Mandatory parameter.
• hihiLimit [Double] - High-high limit for out-of-range alarm. Use null,Infinity or a big value to avoid alarm. Only meaningful for type=analog. Currently not used. Mandatory parameter.
• hihihiLimit [Double] - High-high-high limit for out-of-range alarm. Use null, Infinity or a big value to avoid alarm. Only meaningful for type=analog. Currently not used. Mandatory parameter.
• loLimit [Double] - Low limit for out-of-range alarm. Use null, -Infinity or a big negative value to avoid alarm. Only meaningful for type=analog. Mandatory parameter.
• loloLimit [Double] - Low-low limit for out-of-range alarm. Use null, -Infinity or a big negative value to avoid alarm. Only meaningful for type=analog. Currently not used. Mandatory parameter.
• lololoLimit [Double] - Low-low-low limit for out-of-range alarm. Use -Infinity or a big negative value to avoid alarm. Only meaningful for type=analog. Currently not used. Mandatory parameter.
• hysteresis [Double] - Hysteresis (maximum absolute value variation that will not produce out-of-range alarms) for limits verification. Only meaningful for type=analog. Mandatory parameter.
• substituted [Boolean] - When true, indicates that the value is substituted locally by the operator. Mandatory parameter.
• alarmDisabled [Boolean] - When true, indicates that alarms are disabled for the point. Mandatory parameter.
• annotation [String] - Blocking annotation text (reason command for blocking). Mandatory parameter.
• commandBlocked [Boolean] - When true, the command is disabled by the operator. Mandatory parameter.
• notes [String] - Documental notes text about the point. Mandatory parameter.
• commissioningRemarks [String] - Remarks about the point commissioning. Mandatory parameter.

Fields updated by the system.

• alarmed [Boolean] - When true means the point is alarmed. Mandatory parameter.
• alarmRange [Double] - Current alarm range for analog tags. 0=normal, 1=hiLimit violated, -1=loLimit violated. Mandatory parameter.
• alerted [Boolean] - When true means the point is alerted (Grafana alert). Optional parameter.
• alertState [Boolean] - Grafana alert state name. Optional parameter.
• historianLastValue [Double] - Last value sent to historian (for dead band processing). Only for analog tags. Mandatory parameter.
• invalid [Boolean] - When true value is considered old or not trusted. Mandatory parameter.
• overflow [Boolean] - Overflow detected for type=analog value. Mandatory parameter.
• transient [Boolean] - Flags a transient value. Mandatory parameter.
• frozen [Boolean] - When true, value is considered frozen (not changing). Mandatory parameter.
• value [Double] - Current value as a number. Mandatory parameter.
• valueJson [String] - Current value as JSON. Optional parameter.
• valueString [String] - Current value as a string. Mandatory parameter.
• timeTag [Date] - Last update time. Mandatory parameter.
• timeTagAlarm [Date] - Last alarm time (when alarmed). Mandatory parameter.
• timeTagAlertState [Date] - Time of last Grafana alert state update. Optional parameter.
• timeTagAtSource [Date] - Timestamp from the source. Mandatory parameter.
• timeTagAtSourceOk [Boolean] - When true, the source timestamp is * considered ok. Mandatory parameter.
• updatesCnt [Double] - Count of updates. Mandatory parameter.
• sourceDataUpdate [Object] - Information updated by protocol driver or calculation process.

The sourceDataUpdate object

• sourceDataUpdate.asduAtSource [String] - Protocol ASDU/TI type.
• sourceDataUpdate.causeOfTransmissionAtSource [String] - Cause of transmission. E.g. For IEC60870-5-104, "3"=Spontaneous, "20"=Station Interrogation.
• sourceDataUpdate.notTopicalAtSource [Boolean] - When true means old value at source.
• sourceDataUpdate.invalidAtSource [Boolean] - When true means invalid (not trusted) value at source.
• sourceDataUpdate.blockedAtSource [Boolean] - When true means value is blocked at source.
• sourceDataUpdate.substitutedAtSource [Boolean] - When true means the value is replaced at source.
• sourceDataUpdate.carryAtSource [Boolean] - Flags a counter carry at source.
• sourceDataUpdate.overflowAtSource [Boolean] - Flags a overflow of value at source.
• sourceDataUpdate.transientAtSource [Boolean] - Flags a transient value at source.
• sourceDataUpdate.valueAtSource [Double] - Current numeric value at source.
• sourceDataUpdate.valueStringAtSource [String] - Current string value at source.
• sourceDataUpdate.valueJsonAtSource [String] - Current JSON value at source.
• sourceDataUpdate.valueBsonAtSource [Object] - Current value at source as a Javascript object.
• sourceDataUpdate.timeTagAtSource [Date] - Source timestamp.
• sourceDataUpdate.timeTagAtSourceOk [Boolean] - Source timestamp ok.
• sourceDataUpdate.timeTag [Date] - Local update time.

Fields only existing for the special tag "_System.Status.AlarmBeep"

• beepType [Double] - Beep type. Mandatory parameter.
• beepGroup1List [Array of String] - List of group1 alarmed. Mandatory parameter.

commandsQueue collection

This collection has no configuration, it is written by the system when user issue commands in the UI. Commands are inserted here to be dispatched by protocols drivers. Protocol acknowledgement information is updated here also by protocol drivers.

Custom applications can also create commands to be dispatched by protocol drivers if desired.

Commands are processed only for new insertions. Old documents are preserved and can only be manually removed.

Example document

{
    "_id":{
        "$oid":"5f1098dfd0ea7b5d01d6045c"
    },
    "protocolSourceConnectionNumber":61.0,
    "protocolSourceCommonAddress":1.0,
    "protocolSourceObjectAddress":64139,
    "protocolSourceASDU":45.0,
    "protocolSourceCommandDuration":0.0,
    "protocolSourceCommandUseSBO":false,
    "pointKey":64139.0,
    "tag":"KAW2KPR21XCBR5217----K",
    "timeTag":{
        "$date":"2020-07-16T18:13:51.182Z"
    },
    "value":1.0,
    "valueString":"1",
    "originatorUserName":"Protocol connection: IEC104DIST",
    "originatorIpAddress":"127.0.0.1:58446 127.0.0.1:58446 ",
    "delivered":true,
    "ack":true,
    "ackTimeTag":{
        "$date":"2020-07-16T18:13:51.304Z"
    }
}

• _id [ObjectId] - MongoDB document id.
• protocolSourceConnectionNumber [Double] - Indicates the protocol connection that will dispatch the command. Should contain only integer values.
• protocolSourceCommonAddress [Double] - Protocol common address (device address). See specific protocol documentation.
• protocolSourceObjectAddress [Double or String] - Protocol object address. See specific protocol documentation.
• protocolSourceASDU [Double or String] - Protocol information ASDU type. See specific protocol documentation.
• protocolSourceCommandDuration [Double] - Additional command specification. See specific protocol documentation.
• protocolSourceCommandUseSBO [Boolean] - When true means it is desired to use Select-Before-Operate sequence.
• pointKey [Double] - Numeric key of the point (link to _id field of realtimeData collection).
• tag [String] - Point tag name of event.
• timeTag [Date] - Timestamp for the insertion of the command document.
• value [Double] - Numeric value for the command.
• valueString [String] - String text for the command.
• originatorUserName [String] - Name of command originator process and user name.
• originatorIpAddress [String] - IP address of originator.
• delivered [Boolean] - When true means the protocol driver consumed and dispatched the command.
• ack [Boolean] - The value means the protocol driver received true=positive or false=negative confirmation for the dispatched command. This property is to be inserted by the consuming protocol driver.
• ackTimeTag [Date] - Timestamp of the ack insertion.
• cancelReason [String] - Text description of cancel reason (when the command is cancelled).
• resultDescription [String] - Text description of the command result (when provided by the protocol).

soeData collection

Here are stored Sequence of Events (SOE) information for digital values with source timestamps.

This is a Capped Collection, it has a limited size. Old documents are overwritten when the maximum sized is reached. Data here is only written for digital states when the protocol provides a source timestamp (like for IEC 60870-5-104 type 30).

{
    "_id":{
        "$oid":"5f3427575afe8a451246eb4e"
    },
    "tag":"KAW2IB1-bRPRT----CmFl",
    "pointKey":2742,
    "group1":"KAW2",
    "description":"KAW2~IB1 13,8kV~Protection-Communic.Failure",
    "eventText":"COMM FAILURE",
    "invalid":false,
    "priority":3,
    "timeTag":{
        "$date":"2020-08-12T17:31:03.702Z"
    },
    "timeTagAtSource":{
        "$date":"2020-08-12T13:31:03.499Z"
    },
    "timeTagAtSourceOk":true,
    "ack":0
}

• _id [ObjectId] - MongoDB document id.
• tag [String] - Point tag name of event.
• pointKey [Int32] - Numeric key of the point (link to _id field of realtimeData collection).
• group1 [String] - Highest level grouping.
• description [String] - Full description of monitored information.
• eventText [String] - Text related to the event status change.
• invalid [Boolean] - When true means the status change is not trusted to be ok.
• priority [Int32] - Priority of the point, 0 (highest) - 9 (lowest)
• timeTag [Date] - Timestamp for the arrival of information.
• timeTagAtSource [Date] - Timestamp for the change stamped by the source device (RTU/IED).
• timeTagAtSourceOk [Boolean] - When true means the source timestamp is considered ok.
• ack [Int32] - Operator acknowledgement (0=not acknowledged, 1=acknowledged, 2=eliminated from lists).

processInstances collection

This collection must be configured when some process requires more than one instance. Also it can be used to restrict nodes that can connect to the database by filling the nodeNames array. For this collection, the pair processName/processInstanceNumber should not repeat (there is a unique index for those fields combined to prevent this kind of error).

processInstances entry for the CS_DATA_PROCESSOR module

Example document for the CS_DATA_PROCESSOR module. Currently, this process supports just one redundant instance. There is no need to configure this document for this module as it can create the entry automatically when one is not found.

{
    "_id":{
        "$oid":"1d3427575afe8a451246eb23"
    },
    processName: "CS_DATA_PROCESSOR",
    processInstanceNumber: 1.0,
    enabled: true,
    logLevel: 1.0,
    nodeNames: [], 
    activeNodeName: "mainNode",
    activeNodeKeepAliveTimeTag: { "$date": "2020-08-11T21:04:59.678Z" },
    softwareVersion: "0.1.1",
    latencyAvg: 123.2,
    latencyAvgMinute: 89.1,
    latencyPeak: 240.12
}

• _id [ObjectId] - MongoDB document id.
• processName [String] - Process name ("CS_DATA_PROCESSOR" or "CALCULATIONS")
• instanceNumber [Double] - Process instance number.
• enabled [Boolean] - When true, this instance is enabled.
• logLevel [Double] - Log level (0=min, 3=max).
• nodeNames [Array of String] - Names of allowed nodes. If null or empty any node is allowed.
• activeNodeName [String] - Name of the current active node for this process instance.
• activeNodeKeepAliveTimeTag [Date] - Keep-alive for the active node.
• softwareVersion [String] - Software version of the process.
• latencyAvg [Double] - Average latency in ms (only for CS_DATA_PROCESSOR).
• latencyAvgMinute [Double] - Average latency on a minute in ms (only for CS_DATA_PROCESSOR).
• latencyPeak [Double] - Peak latency (only for CS_DATA_PROCESSOR).

processInstances entry for the CALCULATIONS module

Example document for the CALCULATIONS module. Currently, this process supports just one redundant instance. There is no need to configure this document for this module as it can create the entry automatically when one is not found.

{
    "_id":{
        "$oid":"1d3427575afe8a451246eb24"
    },
    processName: "CALCULATIONS",
    processInstanceNumber: 1.0,
    enabled: true,
    logLevel: 1.0,
    nodeNames: [], 
    activeNodeName: "mainNode",
    activeNodeKeepAliveTimeTag: { "$date": "2020-08-11T21:04:59.678Z" },
    softwareVersion: "0.1.1",
    periodOfCalculation: 2.0
}

• _id [ObjectId] - MongoDB document id.
• processName [String] - Process name ("CS_DATA_PROCESSOR" or "CALCULATIONS")
• instanceNumber [Double] - Process instance number.
• enabled [Boolean] - When true, this instance is enabled.
• logLevel [Double] - Log level (0=min, 3=max).
• nodeNames [Array of String] - Names of allowed nodes. If null or empty any node is allowed.
• activeNodeName [String] - Name of the current active node for this process instance.
• activeNodeKeepAliveTimeTag [Date] - Keep-alive for the active node.
• softwareVersion [String] - Software version of the process.
• periodOfCalculation [Double] - Period in seconds to run the calculation cycle.

Extending the Database Schema

MongoDB schemas can be extended without affecting the JSON-SCADA standard processes. New properties and collections can be added to the standard schema. However, care should be taken to avoid naming collisions with future properties of the system.

All JSON-SCADA collections and fields are named with an initial lower case letter. So, extended collections and properties should avoid this reserved convention.

Is is recommended that custom collections and extended fields/properties be named with an initial upper case letter and also should be used a prefix that can identify the company and/or application.

PostgreSQL/TimescaleDB Schema

Historian table

In this table historical data is written. Local and source timestamps are recorded so that SOE events can be also extracted from this table. This table is converted to a TimescaleDB hypertable to be treated optimally as a time series table. The recommended partition is by day, but this can be changed if desired.

CREATE TABLE hist (
    tag text not null,
    time_tag TIMESTAMPTZ(3),
    value float not null,
    value_json jsonb,
    time_tag_at_source TIMESTAMPTZ(3),
    flags bit(8) not null,
    PRIMARY KEY ( tag, time_tag )
    );
CREATE INDEX ind_timeTag on hist ( time_tag );
CREATE INDEX ind_tagTimeTag on hist ( tag, time_tag_at_source );
comment on table hist is 'Historical data table';
comment on column hist.tag is 'String key for the point';
comment on column hist.value is 'Value as a double precision float';
comment on column hist.time_tag is 'GMT Timestamp for the time data was received by the server';
comment on column hist.time_tag_at_source is 'Field GMT timestamp for the event (null if not available)';
comment on column hist.value_json is 'Structured value as JSON, can be null when do not apply. For digital point it should be the status as in {s:"OFF"}';
comment on column hist.flags is 'Bit mask 0x80=value invalid, 0x40=Time tag at source invalid, 0x20=Analog, 0x10=value recorded by integrity (not by variation)';

-- timescaledb hypertable, partitioned by day
SELECT create_hypertable('hist', 'time_tag', chunk_time_interval=>86400000000);

Realtime Data Table

This table is updated with all fields and values changed from the realtimeData collection from the MongoDB database. It is a helper for apps that can use only the PostgreSQL historian. The internal structure of the realtimeData collection is reflected in the json_data field that has bson type, so when this internal schema change it will be automatically updated here without changing the PostgreSQL table schema. The application should select the the data fields it needs from this JSON object structure. This table only has the latest snapshots for tags, it does not grow with time, so it does not need to be converted to be managed by TimescaleDB.

CREATE TABLE realtime_data (
    tag text not null,
    time_tag TIMESTAMPTZ(3) not null,
    json_data jsonb,
    PRIMARY KEY ( tag )
    );

comment on table realtime_data is 'Realtime data and catalog data';
comment on column realtime_data.tag is 'String key for the point';
comment on column realtime_data.time_tag is 'GMT Timestamp for the data update';
comment on column realtime_data.json_data is 'Data image as JSON from Mongodb realtimeData collection';
CREATE INDEX ind_tag on realtime_data ( tag );

Helper Views for Grafana

These views are helpful to create queries in Grafana, the column names are adapted to what is best expected by the Grafana query editor.

CREATE VIEW grafana_hist AS
    SELECT
        time_tag AS "time",
        tag AS metric,
        value as value,
        time_tag_at_source,
        value_json,
        flags
    FROM hist;

CREATE VIEW grafana_realtime AS
    SELECT
        tag as metric, 
        time_tag as time, 
        cast(json_data->>'value' as float) as value, 
        cast(json_data->>'_id' as text) as point_key, 
        json_data->>'valueString' as value_string, 
        cast(json_data->>'invalid' as boolean) as invalid, 
        json_data->>'description' as description, 
        json_data->>'group1' as group1  
    FROM realtime_data;