This feature is still under active development. While it is available for use, it may not be fully functional or stable.

Analytics

How _analytics data is stored and can be queried
erDiagram asset { int id PK "SERIAL PRIMARY KEY" text enterprise "NOT NULL" text site "DEFAULT '' NOT NULL" text area "DEFAULT '' NOT NULL" text line "DEFAULT '' NOT NULL" text workcell "DEFAULT '' NOT NULL" text origin_id "DEFAULT '' NOT NULL" } product_type { product_type_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY external_product_type_id TEXT NOT NULL cycle_time_ms INTEGER NOT NULL asset_id INTEGER REFERENCES asset(id) _ CONSTRAINT "external_product_asset_uniq UNIQUE (external_product_type_id, asset_id)" _ CHECK "(cycle_time_ms > 0)" } work_order { work_order_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY external_work_order_id TEXT NOT NULL asset_id INTEGER NOT NULL REFERENCES asset(id) product_type_id INTEGER NOT NULL REFERENCES product_type(product_type_id) quantity INTEGER NOT NULL status INTEGER "NOT NULL DEFAULT 0, -- 0: planned, 1: in progress, 2: completed" start_time TIMESTAMPTZ end_time TIMESTAMPTZ _ CONSTRAINT "asset_workorder_uniq UNIQUE (asset_id, external_work_order_id)" _ CHECK "(quantity > 0)" _ CHECK "(status BETWEEN 0 AND 2)" _ UNIQUE "(asset_id, start_time)" _ EXCLUDE "USING gist (asset_id WITH =, tstzrange(start_time, end_time) WITH &&) WHERE (start_time IS NOT NULL AND end_time IS NOT NULL)" } product { product_type_id INTEGER REFERENCES product_type(product_type_id) product_batch_id TEXT asset_id INTEGER REFERENCES asset(id) start_time TIMESTAMPTZ end_time TIMESTAMPTZ NOT NULL quantity INTEGER NOT NULL bad_quantity INTEGER "DEFAULT 0" _ CHECK "(quantity > 0)" _ CHECK "(bad_quantity >= 0)" _ CHECK "(bad_quantity <= quantity)" _ CHECK "(start_time <= end_time)" _ UNIQUE "(asset_id, end_time, product_batch_id)" _ HYPERTABLE "create_hypertable('product', 'end_time', if_not_exists => TRUE)" _ INDEX "INDEX idx_products_asset_end_time ON product(asset_id, end_time DESC)" } shift { shift_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY asset_id INTEGER REFERENCES asset(id) start_time TIMESTAMPTZ NOT NULL end_time TIMESTAMPTZ NOT NULL _ CONSTRAINT "shift_start_asset_uniq UNIQUE (start_time, asset_id)" _ CHECK "(start_time < end_time)" _ EXCLUDE "USING gist (asset_id WITH =, tstzrange(start_time, end_time) WITH &&)" } state { asset_id INTEGER REFERENCES asset(id) start_time TIMESTAMPTZ NOT NULL state INT NOT NULL _ CHECK "(state >= 0)" _ UNIQUE "(start_time, asset_id)" _ HYPERTABLE "create_hypertable('states', 'start_time', if_not_exists => TRUE)" _ INDEX "INDEX idx_states_asset_start_time ON states(asset_id, start_time DESC)" } asset ||--o{ work_order : "id" asset ||--o{ product_type : "id" asset ||--o{ product : "id" asset ||--o{ shift : "id" asset ||--o{ state : "id" work_order ||--o{ product_type : "product_type_id" product ||--o{ product_type : "product_type_id"
erDiagram asset { int id PK "SERIAL PRIMARY KEY" text enterprise "NOT NULL" text site "DEFAULT '' NOT NULL" text area "DEFAULT '' NOT NULL" text line "DEFAULT '' NOT NULL" text workcell "DEFAULT '' NOT NULL" text origin_id "DEFAULT '' NOT NULL" } product_type { product_type_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY external_product_type_id TEXT NOT NULL cycle_time_ms INTEGER NOT NULL asset_id INTEGER REFERENCES asset(id) _ CONSTRAINT "external_product_asset_uniq UNIQUE (external_product_type_id, asset_id)" _ CHECK "(cycle_time_ms > 0)" } work_order { work_order_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY external_work_order_id TEXT NOT NULL asset_id INTEGER NOT NULL REFERENCES asset(id) product_type_id INTEGER NOT NULL REFERENCES product_type(product_type_id) quantity INTEGER NOT NULL status INTEGER "NOT NULL DEFAULT 0, -- 0: planned, 1: in progress, 2: completed" start_time TIMESTAMPTZ end_time TIMESTAMPTZ _ CONSTRAINT "asset_workorder_uniq UNIQUE (asset_id, external_work_order_id)" _ CHECK "(quantity > 0)" _ CHECK "(status BETWEEN 0 AND 2)" _ UNIQUE "(asset_id, start_time)" _ EXCLUDE "USING gist (asset_id WITH =, tstzrange(start_time, end_time) WITH &&) WHERE (start_time IS NOT NULL AND end_time IS NOT NULL)" } product { product_type_id INTEGER REFERENCES product_type(product_type_id) product_batch_id TEXT asset_id INTEGER REFERENCES asset(id) start_time TIMESTAMPTZ end_time TIMESTAMPTZ NOT NULL quantity INTEGER NOT NULL bad_quantity INTEGER "DEFAULT 0" _ CHECK "(quantity > 0)" _ CHECK "(bad_quantity >= 0)" _ CHECK "(bad_quantity <= quantity)" _ CHECK "(start_time <= end_time)" _ UNIQUE "(asset_id, end_time, product_batch_id)" _ HYPERTABLE "create_hypertable('product', 'end_time', if_not_exists => TRUE)" _ INDEX "INDEX idx_products_asset_end_time ON product(asset_id, end_time DESC)" } shift { shift_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY asset_id INTEGER REFERENCES asset(id) start_time TIMESTAMPTZ NOT NULL end_time TIMESTAMPTZ NOT NULL _ CONSTRAINT "shift_start_asset_uniq UNIQUE (start_time, asset_id)" _ CHECK "(start_time < end_time)" _ EXCLUDE "USING gist (asset_id WITH =, tstzrange(start_time, end_time) WITH &&)" } state { asset_id INTEGER REFERENCES asset(id) start_time TIMESTAMPTZ NOT NULL state INT NOT NULL _ CHECK "(state >= 0)" _ UNIQUE "(start_time, asset_id)" _ HYPERTABLE "create_hypertable('states', 'start_time', if_not_exists => TRUE)" _ INDEX "INDEX idx_states_asset_start_time ON states(asset_id, start_time DESC)" } asset ||--o{ work_order : "id" asset ||--o{ product_type : "id" asset ||--o{ product : "id" asset ||--o{ shift : "id" asset ||--o{ state : "id" work_order ||--o{ product_type : "product_type_id" product ||--o{ product_type : "product_type_id"

asset

This table holds all assets. An asset for us is the unique combination of enterprise, site, area, line, workcell & origin_id.

All keys except for id and enterprise are optional. In our example we have just started our CNC cutter, so it’s unique asset will get inserted into the database. It already contains some data we inserted before so the new asset will be inserted at id: 8

identerprisesitearealineworkcellorigin_id
1acme-corporation
2acme-corporationnew-york
3acme-corporationlondonnorthassembly
4stark-industriesberlinsouthfabricationcell-a13002
5stark-industriestokyoeasttestingcell-b33005
6stark-industriespariswestpackagingcell-c23009
7umhcologneofficedevserver1sensor0
8cuttingincoperatedcolognecnc-cutter

work_order

This table holds all work orders. A work order is a unique combination of external_work_order_id and asset_id.

work_order_idexternal_work_order_idasset_idproduct_type_idquantitystatusstart_timeend_time
1#24758110002022-01-01T08:00:00Z2022-01-01T18:00:00Z

product_type

This table holds all product types. A product type is a unique combination of external_product_type_id and asset_id.

product_type_idexternal_product_type_idcycleTimeasset_id
1desk-leg-011210.08

product

This table holds all products.

product_type_idproduct_batch_idasset_idstart_timeend_timequantitybad_quantity
1batch-n11382022-01-01T08:00:00Z2022-01-01T08:10:00Z1007

shift

This table holds all shifts. A shift is a unique combination of asset_id and start_time.

shiftIdasset_idstart_timeend_time
182022-01-01T08:00:00Z2022-01-01T19:00:00Z

state

This table holds all states. A state is a unique combination of asset_id and start_time.

stateIdasset_idstart_timestate
182022-01-01T08:00:00Z20000
282022-01-01T08:10:00Z10000
Last modified April 16, 2024: Update favicons (#285) (acbfbbf)