Database Model

This document describes where and in what format the Platform stores data. Refer to this when determining backup scopes, performing direct queries, or adjusting retention periods.

Multiple Storage Types

The Platform uses polyglot persistence, dividing storage based on the nature of the data. If you do not know where specific data is located, your backup will be incomplete.

Storage	Contents	Scale
PostgreSQL	Master data · Configuration · Transactions	pp schema 169 tables + field app pf schema
Aster TSDB (pp)	Time series · Events · Aggregations · KPI	125 tables
Iceberg (spark.plantpulse)	Long-term retention · Analytics	1 table

---

PostgreSQL — Master and Configuration

Relational data is stored here. Schema changes are managed by Flyway, and history is recorded in flyway_schema_history_v2.

Table names mostly consist of 159 entries with mm_ prefixes (master/meta). The full list by group is available below.

Group	Representative Tables
Organization·Asset	mm_asset_tree · mm_enterprise · mm_site · mm_area · mm_line · mm_equipment · mm_asset_class
Collection Definition	mm_opc · mm_tag · mm_point · mm_edge
Alarm	mm_alarm · mm_alarm_config · mm_alarm_delivery · mm_alarm_suppressed · mm_alarm_shelve · mm_alarm_out_of_service
Production	mm_order · mm_order_oee · mm_product · mm_shift · mm_calendar
Maintenance·Failure	mm_maintenance_order · mm_maintenance_plan · mm_failure_record
People·Customer	mm_employee · mm_customer
Security	mm_security · mm_token · user_login · user_login_session

---

Relationship — Asset and Collection

mm_asset_tree A single table contains all five layers: Enterprise(E) → Site(S) → Area(A) → Line(L) → Equipment(M). Columns shared across all layers, such as name, description, equipment classification, operation status, and external number, exist only in the tree — there is no site_name in mm_site, and no enterprise_name in mm_enterprise. Layer-specific unique columns are stored in 1:1 extension tables (mm_enterprise · mm_site · mm_area · mm_line · mm_equipment) and are deleted together when the corresponding tree row is removed.

Rule	Enforcement
Parent must be the immediate upper layer; Area and below must belong to the same site	Foreign keys fk_mm_asset_tree_parent · fk_mm_asset_tree_parent_site
Only Enterprise can be a root; Enterprise has no site_id; Site's site_id is itself	CHECK constraint
Layer-specific ID format — official prefix or legacy ASSET_	CHECK chk_asset_id
Columns accepting only equipment (work orders, maintenance, calendars, etc.)	Foreign keys referencing mm_equipment

Location paths are provided by view mv_path — for each node: asset_type · depth(Enterprise 0 ~ Equipment 4) · enterprise_id · site_id · area_id · line_id · equipment_id · id_path · name_path.

Relationship — Alarm

Relationship — Production and User

Foreign Keys Ensure Integrity

The above relationships are mostly declared as foreign keys. Therefore, attempting to DELETE a referenced row directly will either be rejected or deleted together depending on the table (ON DELETE CASCADE). Use the UI or API for deletions.

Full Tables (169)

Group	Tables
Organization·Asset (29)	mm_area · mm_area_function · mm_asset_class · mm_asset_class_iso_class · mm_asset_connection_status · mm_asset_document · mm_asset_health_status · mm_asset_layout · mm_asset_plan_generation · mm_asset_plan_segment · mm_asset_qr_access_audit · mm_asset_qr_token · mm_asset_relation · mm_asset_standard_profile · mm_asset_status · mm_asset_tree · mm_asset_type · mm_canvas · mm_enterprise · mm_equipment · mm_equipment_criticality · mm_image · mm_line · mm_line_kind · mm_operational_status · mm_site · mm_stats_site · mm_stats_system · mm_status
Collection Definition (11)	mm_edge · mm_edge_status_snapshot · mm_opc · mm_opc_connection_status · mm_opc_latency_status · mm_opc_protocol · mm_opc_source_type · mm_point · mm_tag · mm_tag_java_type · mm_tag_type
Alarm (16)	mm_alarm · mm_alarm_cause · mm_alarm_config · mm_alarm_config_recipient · mm_alarm_config_revision · mm_alarm_delay · mm_alarm_delivery · mm_alarm_label_revision · mm_alarm_notification · mm_alarm_out_of_service · mm_alarm_resolution_guide · mm_alarm_resolution_guide_cause · mm_alarm_shelve · mm_alarm_signal_gap · mm_alarm_suppressed · mm_tag_alarm_recipient
Equipment Command·Statement (13)	mm_asset_command · mm_asset_command_revision · mm_asset_statement · mm_asset_statement_deployment · mm_asset_statement_plugin · mm_asset_statement_revision · mm_command_area · mm_command_execution · mm_command_execution_event · mm_command_switch · mm_command_switch_audit · mm_event · mm_event_attributes
Production (35)	mm_calendar · mm_calendar_revision · mm_calendar_type · mm_defect_cause · mm_defect_record · mm_defect_record_revision · mm_holiday · mm_order · mm_order_ems · mm_order_flow · mm_order_note · mm_order_oee · mm_order_pause · mm_order_performance · mm_order_progress · mm_order_quantity_correction · mm_order_quantity_correction_revision · mm_order_ram · mm_order_revision · mm_process_segment · mm_process_segment_asset · mm_product · mm_product_route · mm_product_standard_profile · mm_production_target · mm_shift · mm_shift_override · mm_shift_pattern · mm_shift_pattern_asset · mm_shift_pattern_slot · mm_shift_schedule_revision · mm_shift_slot_break · mm_stop_cause · mm_stop_reason · mm_stop_reason_revision
Maintenance·Failure (25)	mm_failure_record · mm_failure_record_change · mm_failure_record_label · mm_failure_record_link · mm_fault_cause · mm_fault_cause_alarm_cause · mm_fault_code_map · mm_fault_code_map_history · mm_iso_detection_method · mm_iso_equipment_category · mm_iso_equipment_class · mm_iso_failure_cause · mm_iso_failure_mechanism · mm_iso_failure_mode · mm_iso_failure_mode_class · mm_iso_maintainable_item · mm_iso_maintainable_item_class · mm_iso_maintenance_activity · mm_maintenance_attachment · mm_maintenance_code_map · mm_maintenance_code_map_history · mm_maintenance_order · mm_maintenance_order_revision · mm_maintenance_plan · mm_maintenance_plan_revision
People·Customer (10)	mm_app_account · mm_app_account_audit · mm_app_account_revision · mm_app_account_scope · mm_customer · mm_employee · mm_employee_audit · mm_employee_emergency_contact · mm_employee_photo · mm_employee_qualification
Flow (5)	mm_flow · mm_flow_health_status · mm_flow_link · mm_flow_node · mm_flow_snapshot
Security (3)	mm_security · mm_security_asset_scope · mm_token
Others (22)	mm_blob · mm_config · mm_diagnostic_code · mm_diagnostic_state · mm_metadata · mm_query_history · mm_reference_external_key · mm_reference_revision · mm_reference_text · mm_report · mm_schedule_fire_log · mm_schedule_watermark · dual · edge_active_flows · flow_snapshots · flyway_schema_history_v2 · metadata · node_component · user_login · user_login_session · version · version_history

Aster TSDB — Time Series and Events

The keyspace is pp. The 125 tables are divided into two categories.

Prefix	Count	Meaning
tm_	117	Time series measurement, aggregation, statistics, KPI
ts_	8	Internal structure of the general-purpose time series engine

tm_ is further divided by target — Tags 32, KPI 26, Assets 17, System 6, Monitor 6, Options 5, OPC 5, Site 3, blob 3, etc.

Core Table — tm_tag_point

This is where raw collection values are stored. Other tag tables are generally derived from this.

PRIMARY KEY (tag_id, timestamp)
WITH CLUSTERING ORDER BY (timestamp DESC)

Property	Value
Partition Key	tag_id — One tag per partition
Clustering	timestamp Descending — Default is reading from newest first
Default TTL	315360000 seconds = 3650 days (10 years) — Official value is in storage.tag.point.ttl (Retention Period Settings)
Compaction	UnifiedCompactionStrategy (Cassandra 5.0+ feature)
Index	SAI(Storage Attached Index) on asset_id · opc_id

Why use static columns?

site_id · asset_id · line_id · area_id · opc_id · tag_name · type are static. Since the value is the same within a single tag (= one partition), it is stored only once per partition. This significantly reduces capacity as it avoids repeated storage for every data point.

Derived Tables — Pre-computed

Since aggregation at query time is expensive in Aster TSDB, values are pre-calculated during writes. This results in a high number of tables.

Category	Examples
Aggregation	tm_tag_point_aggregation_{1,5,10,30}_minutes · _1_hours
Sampling	tm_tag_point_sampling_{10,30}_seconds · _{1,5,10,30}_minutes · _1_hours
Count	tm_tag_point_count · _by_date · _by_opc · _by_site
Quality·Validation	tm_tag_point_validation · _by_timestamp · _validation_count
AI Analysis	tm_tag_point_anomalies · _forecasts
Snapshot·Retention	tm_tag_point_snapshot · tm_tag_point_archive

Alarms follow the same method — tm_tag_alarm and _count · _count_by_date · _count_by_opc · _count_by_site · _duration · _on.

Do not delete aggregation tables directly

They are not reconstructed from raw data. If deleted, screens and reports for that period will be blank, and recovery is only possible via backup.

KPI Tables — OEE · RAM · EMS

Equipment KPIs (OEE · RAM · EMS) are stored in 26 tables starting with tm_kpi_ — categorized by domain as tm_kpi_oee_* · tm_kpi_ram_* · tm_kpi_ems_*. A calculation job writes values for each piece of equipment approximately every 10 seconds.

Legacy tm_asset_oee* · tm_asset_ram* · tm_asset_ems* tables no longer exist

KPIs are now written only to new tables. Queries and reports using old names must be updated to match the new tables and partition keys. Since keys have changed, using old conditions will likely result in empty results rather than errors, and these empty results are indistinguishable from 「No Data」.

Tables are divided into four categories based on their purpose.

Category	Name	Primary Key	Meaning of one row
Current Value	tm_kpi_*_current	(asset_id)	Current value of equipment — one row per equipment, overwritten upon each calculation
Per Order	tm_kpi_oee_order · tm_kpi_ems_order	(order_id)	Value for a single work order — one row per order, remains after order completion
History	tm_kpi_*_history etc.	((asset_id, day), timestamp)	Value of one calculation (tick) — partition by UTC date, newest first
Rollup	tm_kpi_*_rollup	((scope, scope_id, day), timestamp)	Values aggregated by line, area, site, or enterprise

• Buckets are UTC dates. day is integer yyyymmdd, and month is integer yyyymm, representing the date interpreted as UTC for timestamp of the row — not the local midnight of the site. Therefore, one local day spans two UTC buckets (rows from 00:00 to 08:59 KST are in the previous day's UTC bucket).
• Infrequent data uses monthly buckets — equipment status (tm_kpi_oee_status), failure (tm_kpi_ram_failure · _failure_rollup), and fuel (tm_kpi_ems_fuel) are partitioned by month.
• Rollup scope is one of LINE · AREA · SITE · ENTERPRISE, and scope_id is the ID of that line, area, or site. Enterprise level is scope_id = 'ALL'.
• Cause counters tm_kpi_oee_defect_cause · _stop_cause · _fault_cause are daily accumulations by defect, stop, or failure cause. The primary key is ((asset_id, month), date, cause_code).
• History rows are order accumulations. One row of *_history is the total value for the order up to that tick (uptime, production count, energy, etc.). Do not sum the rows — for period values, read the last row per order or the *_order row.
• Duration of equipment status is not stored. tm_kpi_oee_status only stores the time the status changed, and a status continues until the time in the next row. Do not cut at month boundaries; read buckets consecutively.

Querying — Provide all partition keys

Include both asset_id and day (or month) in the condition. Do not use ALLOW FILTERING. When querying a period, expand the period into a list of UTC dates and read once per bucket.

SELECT timestamp, oee, availability, performance, quality
  FROM pp.tm_kpi_oee_history
 WHERE asset_id = 'EQUIPMENT_000001' AND day = 20260925
   AND timestamp >= '2026-09-25 00:00:00+0000';

If values per equipment belonging to a site are needed, obtain the equipment list from PostgreSQL and read tm_kpi_*_current for each equipment. Site totals are in scope = 'SITE' of the rollup table.

The retention period is 5 years. The default TTL for tables with buckets (history, event, status, rollup, etc.) is 157680000 seconds (1825 days), and the official value is in storage.asset.kpi.ttl (or storage.asset.standard.ttl if unavailable) (Retention Period Settings). Current value (*_current), per order (*_order), and cause counters have no TTL. Since 14 history tables are copied daily to tw_kpi_* in Iceberg, they can be queried in Iceberg even after being deleted from Aster TSDB after 5 years.

---

Data Separation — Partition Key as the Query Axis

Aster TSDB does not support joins. Separate tables are created based on the query method — which is why there are 125 tables. Storing the same data in multiple tables with different partition keys is a design choice, not duplication.

Partition Key Format	Purpose	Example
(tag_id) + timestamp	Time-axis query for a single tag	tm_tag_point
(asset_id, date, hour) + timestamp	Daily/hourly interval for one equipment	tm_asset_data
(asset_id, day) + timestamp	UTC daily KPI for one equipment	tm_kpi_oee_history
(scope, scope_id, day) + timestamp	UTC daily KPI for line, area, site, or enterprise	tm_kpi_oee_rollup
(site_id, year, month, day)	Site-unit aggregation	tm_site_summary
(id) only	Current value — no time axis, no expiration	*_count, *_last_update, tm_kpi_*_current

Full Tables (125)

Tag · Point (32)

Table	Primary Key (Partition · Clustering)
tm_tag_alarm	tag_id, alarm_seq
tm_tag_alarm_count	``
tm_tag_alarm_count_by_date	tag_id, year, month, day, hour, minute
tm_tag_alarm_count_by_opc	(opc_id, tag_id), year, month, day, hour, minute
tm_tag_alarm_count_by_site	(site_id, tag_id), year, month, day, hour, minute
tm_tag_alarm_duration	alarm_config_id, alarm_seq
tm_tag_alarm_on	``
tm_tag_point	tag_id, timestamp
tm_tag_point_aggregation_10_minutes	tag_id, timestamp
tm_tag_point_aggregation_1_hours	tag_id, timestamp
tm_tag_point_aggregation_1_minutes	tag_id, timestamp
tm_tag_point_aggregation_30_minutes	tag_id, timestamp
tm_tag_point_aggregation_5_minutes	tag_id, timestamp
tm_tag_point_anomalies	job_id, timestamp, tag_id
tm_tag_point_archive	(site_id, year, month, day, hour), minute, second, tuuid
tm_tag_point_count	``
tm_tag_point_count_by_date	tag_id, year, month, day, hour, minute
tm_tag_point_count_by_opc	``
tm_tag_point_count_by_site	``
tm_tag_point_forecasts	job_id, timestamp, tag_id
tm_tag_point_map	(year, month, day, hour, minute), timestamp
tm_tag_point_sampling_10_minutes	tag_id, timestamp
tm_tag_point_sampling_10_seconds	tag_id, timestamp
tm_tag_point_sampling_1_hours	tag_id, timestamp
tm_tag_point_sampling_1_minutes	tag_id, timestamp
tm_tag_point_sampling_30_minutes	tag_id, timestamp
tm_tag_point_sampling_30_seconds	tag_id, timestamp
tm_tag_point_sampling_5_minutes	tag_id, timestamp
tm_tag_point_snapshot	(site_id, snapshot_id, date), timestamp
tm_tag_point_validation	tag_id, timestamp
tm_tag_point_validation_by_timestamp	yyyymmdd, timestamp, tag_id
tm_tag_point_validation_count	tag_id, year, month, day, hour, minute

Asset (17)

Table	Primary Key (Partition · Clustering)
tm_asset_aggregation	asset_id, timestamp
tm_asset_alarm	asset_id, alarm_seq
tm_asset_command	asset_id, timestamp
tm_asset_connection_status	asset_id, timestamp
tm_asset_context	asset_id, timestamp
tm_asset_data	(asset_id, date, hour), timestamp
tm_asset_data_anomalies	job_id, timestamp, asset_id, tag_id
tm_asset_data_based_day	asset_id, timestamp
tm_asset_data_based_hour	asset_id, timestamp
tm_asset_data_based_minute	(asset_id, date), timestamp
tm_asset_data_based_second	(asset_id, date, hour), timestamp
tm_asset_data_based_timestamp	(asset_id, date, hour), timestamp
tm_asset_data_forecasts	job_id, timestamp, asset_id, tag_id
tm_asset_event	asset_id, timestamp
tm_asset_health_status	asset_id, timestamp
tm_asset_status	asset_id, timestamp
tm_asset_timeline	asset_id, timestamp, timeline_group, timeline_type

KPI — OEE · RAM · EMS (26)

The «Day» in the bucket column is day (UTC yyyymmdd), and «Month» is month (UTC yyyymm). The month of the cause counter is obtained from date. See the KPI Tables section for reading instructions.

Table	Primary Key (Partition · Clustering)	Bucket	Contents
tm_kpi_oee_current	asset_id	—	Current OEE of equipment
tm_kpi_oee_order	order_id	—	OEE of a single work order
tm_kpi_oee_history	(asset_id, day), timestamp	Day	OEE per calculation tick (order accumulation)
tm_kpi_oee_event	(asset_id, day), timestamp	Day	OEE input events
tm_kpi_oee_count	(asset_id, day), timestamp	Day	Good/Defect production count (lot, serial)
tm_kpi_oee_rolling	(asset_id, day), timestamp	Day	OEE for the last N minutes
tm_kpi_oee_rollup	(scope, scope_id, day), timestamp	Day	Line, Area, Site, Enterprise OEE
tm_kpi_oee_status	(asset_id, month), timestamp	Month	Equipment status change
tm_kpi_oee_status_current	asset_id	—	Previous status of equipment
tm_kpi_oee_defect_cause	(asset_id, month), date, cause_code	Month	Daily accumulation of defect causes (counter)
tm_kpi_oee_stop_cause	(asset_id, month), date, cause_code	Month	Daily accumulation of stop causes (counter)
tm_kpi_oee_fault_cause	(asset_id, month), date, cause_code	Month	Daily accumulation of failure causes (counter)
tm_kpi_ram_current	asset_id	—	Current RAM of equipment
tm_kpi_ram_history	(asset_id, day), timestamp	Day	RAM per calculation tick (order accumulation)
tm_kpi_ram_failure	(asset_id, month), timestamp	Month	Failure start/end events
tm_kpi_ram_failure_rollup	(scope, scope_id, month), timestamp, asset_id	Month	Line, Area, Site failure events
tm_kpi_ram_rolling	(asset_id, day), timestamp	Day	RAM for the last N minutes
tm_kpi_ram_rollup	(scope, scope_id, day), timestamp	Day	Line, Area, Site, Enterprise RAM
tm_kpi_ems_current	asset_id	—	Current EMS of equipment
tm_kpi_ems_order	order_id	—	EMS of a single work order
tm_kpi_ems_history	(asset_id, day), timestamp	Day	EMS per calculation tick (order accumulation)
tm_kpi_ems_power	(asset_id, day), timestamp	Day	Power measurement events (kW, kWh)
tm_kpi_ems_power_rollup	(scope, scope_id, day), timestamp, asset_id	Day	Line, Area, Site power events
tm_kpi_ems_fuel	(asset_id, month), timestamp	Month	Fuel measurement events (Scope 1)
tm_kpi_ems_rolling	(asset_id, day), timestamp	Day	EMS for the last N minutes
tm_kpi_ems_rollup	(scope, scope_id, day), timestamp	Day	Line, Area, Site, Enterprise EMS

Collection · Site (8)

Table	Primary Key (Partition · Clustering)
tm_opc_connection_latency_history	opc_id, timestamp
tm_opc_connection_status_history	opc_id, timestamp
tm_opc_point_count	``
tm_opc_point_count_by_date	opc_id, year, month, day, hour, minute
tm_opc_point_last_update	``
tm_site_status	site_id, timestamp
tm_site_summary	site_id, year, month, day, hour, minute
tm_site_summary_total	``

Monitoring (13)

Table	Primary Key (Partition · Clustering)
tm_monitor_cep_metrics	type, timestamp
tm_monitor_cluster_metrics	type, ip, timestamp
tm_monitor_cluster_status	type, ip, timestamp
tm_monitor_nodes	type, ip
tm_monitor_platform_health	type, ip, timestamp
tm_monitor_platform_status	type, ip, timestamp
tm_system_health_status	app_name, timestamp
tm_system_log	app_name, timestamp, seq
tm_system_log_count	(app_name, level), year, month, day, hour, minute
tm_system_log_hourly	app_name, bucket_start, instance_id, level
tm_system_properties	property, timestamp
tm_system_start_date	``
tm_system_trend	year, month, day, hour

Batch · Audit (4)

Table	Primary Key (Partition · Clustering)
tm_batch_audit	yyyymmdd, timestamp, id
tm_batch_job_run	yyyymmdd, timestamp, id
tm_warehouse_audit	yyyymmdd, timestamp, id
tm_warehouse_storage_usage_daily	year_bucket, day, table_name

Time Series Engine (TSE Only) (8)

Table	Primary Key (Partition · Clustering)
ts_data_points	key, column1
ts_row_key_index	key, column1
ts_row_key_time_index	metric, table_name, row_time
ts_row_keys	(metric, table_name, row_time), data_type, tags
ts_service_index	(service, service_key), key
ts_spec	spec_type, name
ts_string_index	key, column1
ts_tag_indexed_row_keys	(metric, table_name, row_time, single_tag_pair), data_type, tag_collection_hash, tags

Others (17)

Table	Primary Key (Partition · Clustering)
tm_blob	blob_id, timestamp
tm_blob_count_and_size	``
tm_blob_object	``
tm_data_gateway_count	``
tm_data_gateway_log	lang, timestamp
tm_database_size	keyspace_name, date
tm_diagnostic	year, timestamp
tm_diagnostic_count	year, level
tm_domain_change_history	(domain_type, object_id), timestamp
tm_edge_status	``
tm_option_eql_listener_result	``
tm_option_eql_statement	``
tm_option_eql_statement_history	stmt_name, timestamp
tm_option_job_trigger_history	job_name, context_fire_time
tm_option_listener_push_cache	url, timestamp
tm_pipeline_exception_history	bucket, timestamp, type, object_id
tm_trigger_result	trigger_id, timestamp

Iceberg — Long-term Retention

Data from archive tables (tm_tag_point_archive, default 365 days) is accumulated in object storage for analysis. Data does not move directly from raw tables but goes through an archive process.

Item	Value
Catalog	spark.plantpulse
Table	tw_tag_point — Tag time series. There are 9 types starting with tw_asset_* · tw_blob etc. tw_. KPIs have 14 separate types in tw_kpi_*
Partition	site_id → year → month → day
Format	Iceberg v2 (supports row-level delete)
Compression	Zstandard
Execution	Spark SQL

KPI Archive — tw_kpi_*

KPIs are moved directly from tm_kpi_* without passing through archive tables. Since the previous day's data is copied every dawn, recent data exists in both Aster TSDB and Iceberg, while data older than 5 years (TTL) remains only in Iceberg. Data in Iceberg is not deleted. Partitions are year → month → day (for monthly bucket tables: year → month).

Aster TSDB	Iceberg
tm_kpi_oee_history · _event · _count · _status · _rollup	tw_kpi_oee_history · _event · _count · _status · _rollup
tm_kpi_ram_history · _failure · _rollup · _failure_rollup	tw_kpi_ram_history · _failure · _rollup · _failure_rollup
tm_kpi_ems_history · _power · _fuel · _rollup · _power_rollup	tw_kpi_ems_history · _power · _fuel · _rollup · _power_rollup

Current value (*_current · oee_status_current), per order (*_order), last N minutes (*_rolling), and cause counters (*_cause) are not moved — they are either not history or can be recalculated from history.

Searching for data beyond the retention period

It is no longer in Aster TSDB. Query the Iceberg side → Analytics Layer. Retention periods per table are in Data Retention Period (TTL) Settings.

---

Common Backup Omissions

The three storage types must each be backed up. A common mistake is to only back up PostgreSQL, which would result in the total loss of time series data.

Storage	Risk of Omission
PostgreSQL	Loss of equipment/tag definitions, users, and dashboards
Aster TSDB	Loss of time series data within the retention period
Iceberg / Object Storage	Loss of long-term history

For procedures, refer to Backup and Recovery.

---

Related Documents

• Backup and Recovery
• Storage Layer · Analytics Layer
• Domain ID Rules — SITE_ · EQUIPMENT_ · TAG_ prefixes
• Database Management — Installation and operation