Database Model
This document describes where and how the Platform stores data. Refer to it when defining backup scope, querying data directly, or adjusting retention periods.
The Platform divides storage by data type (polyglot persistence). If you don't know where each piece of data lives, your backup will be incomplete.
| Storage | Contains | Scale |
|---|---|---|
| PostgreSQL | Master data · configuration · transactions | 43 tables |
Cassandra (pp) | Time series · events · aggregates | 99 tables |
Iceberg (spark.plantpulse) | Long-term retention · analytics | 1 table |
PostgreSQL — Master and Configuration
Relational data lives here. Schema changes are managed by Flyway, and history is
kept in flyway_schema_history.
Table names mostly carry mm_ prefixes (master/meta) — 36 of them.
| Group | Tables |
|---|---|
| Organization · Assets | mm_company · mm_site · mm_asset_tree · mm_asset_type · mm_asset_status · mm_asset_statement · mm_asset_statement_plugin |
| Collection definition | mm_opc · mm_tag · mm_point · mm_scada |
| Alarms | mm_alarm · mm_alarm_config · mm_alarm_recieve_users |
| Events · Triggers | mm_event · mm_event_attributes · mm_trigger · mm_trigger_attributes |
| Production | mm_order · mm_order_flow · mm_order_oee · mm_oee · mm_product · mm_shift · mm_calendar · mm_calendar_type |
| People · Vendors | mm_employee · mm_customer |
| Screens · Queries | mm_dashboard · mm_graph · mm_statement · mm_query_history |
| Security | mm_security · mm_token · user_login · user_login_session |
| Other | mm_blob · mm_metadata · metadata · version · version_history · dual |
mm_alarm_recieve_usersIt is recieve, not receive. The typo is baked into the schema, so you must write it
exactly that way in queries.
Cassandra — Time Series and Events
The keyspace is pp. Its 99 tables split into two families.
| Prefix | Count | Meaning |
|---|---|---|
tm_ | 91 | Time series · aggregates · statistics |
ts_ | 8 | Generic time series engine internals |
tm_ subdivides by target — 32 tags · 22 assets · 6 system · 6 monitor · 5 optional ·
5 OPC · 3 site · 3 blob, and more.
Core Table — tm_tag_point
This is where raw collected values go. Most other tag tables derive from it.
PRIMARY KEY (tag_id, timestamp)
WITH CLUSTERING ORDER BY (timestamp DESC)
| Property | Value |
|---|---|
| Partition key | tag_id — one tag = one partition |
| Clustering | timestamp descending — reads newest first by default |
| Default TTL | 5356800 seconds = 62 days |
| Compaction | UnifiedCompactionStrategy (Cassandra 5.0+) |
| Indexes | asset_id · opc_id with SAI (Storage Attached Index) |
site_id · asset_id · line_id · area_id · opc_id · tag_name · type are
static. Within one tag (one partition), their value is the same, so they are stored
once per partition instead of repeating with every data point. This cuts storage
significantly.
Derived Tables — Pre-computed
Cassandra makes aggregation at query time expensive, so the Platform pre-builds them at write time. That's why there are so many tables.
| Family | Examples |
|---|---|
| Aggregates | 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 |
| Counts | 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 |
| Snapshots · Retention | tm_tag_point_snapshot · tm_tag_point_archive |
Alarms follow the same pattern — tm_tag_alarm and _count · _count_by_date · _count_by_opc ·
_count_by_site · _duration · _on.
They are not regenerated from raw data. Dropping them leaves that time window blank in dashboards and reports; recovery requires a backup restore.
Iceberg — Long-term Retention
Data older than Cassandra's TTL (62 days) is archived to object storage for analytics.
| Item | Value |
|---|---|
| Catalog | spark.plantpulse |
| Table | tm_tag_point_warehouse |
| Partitioning | site_id → year → month → day |
| Format | Iceberg v2 (row-level delete support) |
| Compression | Zstandard |
| Execution | Spark SQL |
It is not in Cassandra. Query Iceberg instead → Analytics Layer.
Easy-to-miss items in backups
All three storage systems are separate backup targets. Some teams back up only PostgreSQL and assume they're safe — but then time series is completely missing.
| Storage | If missed |
|---|---|
| PostgreSQL | Equipment · tag definitions, users, dashboards disappear |
| Cassandra | Latest 62 days of data disappear |
| Iceberg / Object storage | Long-term history disappears |
See Backup and Recovery for procedures.
Related documents
- Backup and Recovery
- Storage Layer · Analytics Layer
- Domain ID Rules —
SITE_·ASSET_·TAG_prefixes - Database Administration — setup and operations