Load Database Tables
The load database stores smart meter data, power quality readings, and feeder-level measurements used by the Hosting Capacity Module (HCM) and related services. It runs on PostgreSQL with the TimescaleDB extension.
Network Mapping Tables
These tables are populated by the network ingestor during network model ingestion and are used to support load aggregation. They are recomputed each time a new network model revision is ingested.
cpi_usage_point
Maps each CPI (Connection Point Identifier - NMI in Australia, ICP in New Zealand, other names by jurisdiction) to its corresponding usage point MRID in the network model. This decoupling exists because in some network models the usage point MRID is a system-generated identifier that differs from the external CPI. Where possible, the two values should be identical.
| Column | Type | Description |
|---|---|---|
| cpi | text (PK) | The external connection point identifier (e.g. NMI) |
| usage_point_mrid | text | The corresponding usage point MRID in the network model |
usage_point_aggregation_mapping
Defines how usage points aggregate up through the network hierarchy. The ingestor traces the network from each CPI up to the feeder head, identifying aggregation points (currently distribution transformers and the feeder head) and recording the relationships in this table. Each aggregation point is assigned a virtual usage point MRID (typically <transformer_mrid>-AGG).
A new set of mappings is written each time the network model is ingested, with from_ts and to_ts bounding the period of validity. This preserves a history of how the network was structured over time.
| Column | Type | Description |
|---|---|---|
| usage_point_mrid | text | The source usage point being mapped upward |
| aggregate_usage_point_mrid | text | The parent virtual usage point this maps to |
| proportion | double precision | Multiplier applied to this usage point's values when aggregating. For a CPI whose energy consumers are split across multiple transformers, this reflects the fractional share belonging to each transformer. Typically 1.0 for simple cases. |
| sequence | int | Determines aggregation order. Sequence 1 runs first (e.g. transformer level); sequence 2 depends on sequence 1 results (e.g. feeder head). |
| from_ts | timestamptz | Start of validity for this mapping, in UTC datetime format. |
| to_ts | timestamptz | End of validity. NULL indicates the current mapping, in UTC datetime format. |
| agg_source | CPI or USAGE_POINT | Whether the source is a CPI-level aggregation (cpi_aggregations) or an already-aggregated usage point (usage_point_aggregations). This supports multi-level aggregation where higher levels consume the outputs of lower levels. |
Note: Hosting capacity always uses the current network model. Running HCM with historical load data will reflect current feeder membership, not historical membership. This can cause unexpected totals at the feeder head if the network topology has changed significantly.
Smart Meter Energy Data Tables
These tables store interval energy data from smart meters. The load ingestor populates them from customer-supplied files (typically CSVs). Only 30-minute interval data is supported; data supplied at other intervals (e.g. 15-minute) is pre-aggregated to 30 minutes before ingestion.
meter_readings
Populated by the load ingestor from customer-supplied files. The supported file formats, channel codes, and interval handling vary by customer -- see Timeseries Data Ingestion for full details.
Data is always stored at 30-minute resolution regardless of the source interval.
| Column | Type | Description |
|---|---|---|
| reading_timestamp | timestamptz | Interval timestamp |
| meter_id | text | Meter identifier |
| cpi | text | Connection point identifier the meter belongs to |
| reading_type | enum | One of REAL_ENERGY_IMPORT, REAL_ENERGY_EXPORT, REACTIVE_ENERGY_IMPORT, REACTIVE_ENERGY_EXPORT |
| channel | text | Raw channel identifier from the source file |
| reading_value | int | Value in watt-hours |
cpi_aggregations
Holds interval energy readings aggregated to the usage point level (i.e. per CPI). Built from meter_readings by summing all meter channels for each CPI at each timestamp. This is the primary load data table consumed by the Hosting Capacity Module.
The schema is column-oriented (one column per reading type) rather than row-oriented, which improves query performance for the aggregation workloads that operate on this table.
| Column | Type | Description |
|---|---|---|
| usage_point_mrid | text | Usage point MRID (resolved via cpi_usage_point) |
| reading_timestamp | timestamptz | Interval timestamp |
| real_energy_import | bigint | Total real energy imported in watt-hours |
| real_energy_export | bigint | Total real energy exported in watt-hours |
| reactive_energy_import | bigint | Total reactive energy imported in watt-hours |
| reactive_energy_export | bigint | Total reactive energy exported in watt-hours |
usage_point_aggregations
Holds interval energy readings aggregated to virtual usage points -- currently distribution transformers and feeder heads. Built from cpi_aggregations using the mappings in usage_point_aggregation_mapping.
The schema is identical to cpi_aggregations. Transformer-level aggregations power the Network Explorer UI trends and displays. Feeder head aggregations are used for calibration and results validation.
| Column | Type | Description |
|---|---|---|
| usage_point_mrid | text | Virtual usage point MRID (e.g. a transformer or feeder head) |
| reading_timestamp | timestamptz | Interval timestamp |
| real_energy_import | bigint | Aggregated real energy imported in watt-hours |
| real_energy_export | bigint | Aggregated real energy exported in watt-hours |
| reactive_energy_import | bigint | Aggregated reactive energy imported in watt-hours |
| reactive_energy_export | bigint | Aggregated reactive energy exported in watt-hours |
Other Tables
pqv
This table is used for calibration and results validation workflows. It contains power quality and voltage (PQV) readings for each meter and time interval.
| Column Name | Data Type | Constraints | Description |
|---|---|---|---|
| cpi | TEXT | PRIMARY KEY, NOT NULL | Customer Point Identifier (NMI) |
| meter_id | TEXT | PRIMARY KEY, NOT NULL | Meter ID. Always agg for NMI-level aggregated readings, which is the standard form stored in this table. |
| reading_type | pqv_reading_type | PRIMARY KEY, NOT NULL | Type of reading. One of: MAX_VOLTAGE, MIN_VOLTAGE, INSTANT_VOLTAGE, AVERAGE_VOLTAGE, INSTANT_NET_ACTIVE_POWER, INSTANT_NET_REACTIVE_POWER |
| reading_phase | phase_reading | PRIMARY KEY, NOT NULL | Phase of reading. One of: A (Phase A), B (Phase B), C (Phase C), AVERAGE_OF_PHASES (Average of Phases), SUM_OF_PHASES (Sum of Phases) |
| timestamp | TIMESTAMPTZ | PRIMARY KEY, NOT NULL | Timestamp of the reading in UTC |
| value | REAL | Value of the reading. Units depend on reading type: volts for voltage types, watts for active power, VAR for reactive power. |
feeder_head_metrics
Stores electrical measurements at the head of each feeder. Populated by the load ingestor from customer-supplied SCADA or PQM data files. All power and voltage fields are sourced directly from the customer files. Unit conversion may be applied during ingestion depending on the source data format.
| Column | Type | Description |
|---|---|---|
| feeder_id | text | Feeder identifier |
| timestamp | timestamptz | Measurement timestamp |
| apparent_power | real | Apparent power at feeder head (MVA) |
| reactive_power | real | Reactive power at feeder head (MVAr) |
| real_power | real | Real power at feeder head (MW) |
| voltage_a_b | real | Line-to-line voltage A-B (V) |
| voltage_b_c | real | Line-to-line voltage B-C (V) |
| voltage_c_a | real | Line-to-line voltage C-A (V) |
| voltage_l_l | real | Average line-to-line voltage (V) - (computed as the average of the three line-to-line voltages) |
| type | MAX or MIN | Whether this row represents the maximum or minimum reading for the interval. All values are MAX by default, talk to Zepben for changes to this. |
Unused / Reserved Tables
The following tables exist in the schema but are not currently populated and are not used by any product features. They may be removed in a future release.
network_hierarchy
nmi_to_cluster
synthetic_load_mappings
Internal Tables
app_config - Stores internal configuration parameters used by the database's stored procedures. This table is not customer-facing and should not be modified manually.
databasechangelog and databasechangeloglock - Internal use only and should not be modified or queried directly.