Schema

Unlock the Power of Organized Energy Data: A Clear, Relational Blueprint for Properties, Meters, Accounts, and Billing Insights.

Property Schema Documentation

This document outlines the MySQL table schema for a “Property” and its related entities, serving as a metadata wrapper for utility meters. Accounts exist independently and can be associated with multiple meters across multiple properties. The schema includes tables for Property, UtilityAccount, UtilityMeter, UtilityBill, UtilityCharge, and UtilityUsage, with fields, data types, and relationships clearly defined for client understanding and integration.

SQL migrations to create empty tables for this schema can be downloaded here.

Table: Property

The Property table stores metadata about a specific property, acting as a wrapper for associated utility meters.

Field Data Type Description
id INT Unique identifier for the property (Primary Key).
ep_object_type VARCHAR(50) Type of object, defaults to “Property”.
name VARCHAR(255) Name of the property (e.g., “Bus Garage”).
sq_ft INT Square footage of the property (e.g., 5160).
status VARCHAR(50) Current status (e.g., “Active”).
company_name VARCHAR(255) Company that owns/manages the property (e.g., “Horseheads Central School District”).
weather_station_alpha_id VARCHAR(50) Weather station identifier (e.g., “KELM”).
street1 VARCHAR(255) Street address line 1 (e.g., “601 Sayre St”).
street2 VARCHAR(255) Street address line 2 (may be empty).
city VARCHAR(100) City (e.g., “Horseheads”).
state VARCHAR(50) State or province (e.g., “NY”).
country VARCHAR(50) Country (e.g., “US”).
zip_code VARCHAR(20) ZIP or postal code (e.g., “14845-2372”).
longitude DOUBLE Geographic longitude (e.g., -76.83255762797054).
latitude DOUBLE Geographic latitude (e.g., 42.16156295772226).
year_built INT Year the property was built (e.g., 2021).
construction_type VARCHAR(50) Construction type (e.g., “BRICK_MORTAR”).
primary_use VARCHAR(50) Primary use of the property (e.g., “PARKING”).
story_type VARCHAR(50) Story type (e.g., “SINGLE”).
cooling_energy_type VARCHAR(50) Energy type for cooling (e.g., “ELECTRIC”).
heating_energy_type VARCHAR(50) Energy type for heating (e.g., “GAS”).
energystar_certified BOOLEAN Energy Star certification status (e.g., false).
data_current_through TIMESTAMP Date until data is current (e.g., “2025-02-01T00:00:00.000Z”).
metadata JSON Additional metadata (e.g., {}).
created_at TIMESTAMP Creation timestamp, defaults to current time.
updated_at TIMESTAMP Last update timestamp, updates on change.
  • Constraints: id is the primary key.
  • Engine: InnoDB for foreign key and transaction support.

Table: UtilityAccount

The UtilityAccount table stores utility account details, independent of properties, and can link to multiple meters.

Field Data Type Description
id INT Unique identifier for the account (Primary Key).
ep_object_type VARCHAR(50) Type of object, defaults to “UtilityAccount”.
utility_company_name VARCHAR(255) Utility company name (e.g., “New York State Electric & Gas”).
status VARCHAR(50) Status of the account (e.g., “Active”).
acct_num VARCHAR(100) Account number (e.g., “14845-2372”).
latest_invoice_date TIMESTAMP Date of latest invoice (e.g., “2025-03-23T00:00:00.000Z”).
delivery_method VARCHAR(50) Bill delivery method (e.g., “Self Entered”).
delivery_name VARCHAR(255) Name for delivery (e.g., null).
delivery_email VARCHAR(255) Email for delivery (e.g., null).
master_acct_num VARCHAR(100) Master account number (e.g., null).
online_access_url TEXT URL for online access (e.g., null).
metadata JSON Additional metadata (e.g., {}).
created_at TIMESTAMP Creation timestamp (e.g., “2023-04-18T22:35:06.000Z”).
updated_at TIMESTAMP Last update timestamp (e.g., “2025-04-03T16:19:36.000Z”).
  • Constraints: id is the primary key.
  • Engine: InnoDB.

Table: UtilityMeter

The UtilityMeter table stores meter details, linked to properties and utility accounts.

Field Data Type Description
id INT Unique identifier for the meter (Primary Key).
ep_object_type VARCHAR(50) Type of object, defaults to “UtilityMeter”.
meter_wrapper_id INT Internal meter identifier (e.g., 32349).
property_id INT ID of the associated property (e.g., 6980).
property_name VARCHAR(255) Name of the associated property (e.g., “Bus Garage”).
status VARCHAR(50) Status of the meter (e.g., “Active”).
meter_num VARCHAR(50) Meter number (e.g., “Main”).
service_address VARCHAR(255) Service address (e.g., null).
service_name VARCHAR(50) Service name (e.g., null).
premise_num VARCHAR(50) Premise number (e.g., null).
association_type VARCHAR(50) Type of association (e.g., “STANDARD”).
latest_usage_date TIMESTAMP Latest usage date (e.g., “2025-03-20T00:00:00.000Z”).
deactivation_date TIMESTAMP Deactivation date (e.g., null).
energy_type VARCHAR(50) Energy type (e.g., “ELECTRIC”, “GAS”).
measurement_unit VARCHAR(50) Unit of measurement (e.g., “kWh”, “Therms”).
demand_measurement_unit VARCHAR(50) Unit for demand (e.g., “kW”, null).
billing_frequency VARCHAR(50) Billing frequency (e.g., “MONTHLY”).
eligibility_start_month TIMESTAMP Start of eligibility (e.g., “1996-01-01T00:00:00.000Z”).
eligibility_end_month TIMESTAMP End of eligibility (e.g., “2025-02-01T00:00:00.000Z”).
supply_only BOOLEAN Indicates supply-only meter (e.g., false).
metadata JSON Additional metadata (e.g., {}).
utility_account_id INT ID of the associated utility account (e.g., 25830).
created_at TIMESTAMP Creation timestamp (e.g., “2023-04-18T22:35:06.000Z”).
updated_at TIMESTAMP Last update timestamp (e.g., “2025-04-03T16:19:36.000Z”).
  • Constraints:
    • id is the primary key.
    • property_id is a foreign key to Property(id).
    • utility_account_id is a foreign key to UtilityAccount(id).
  • Engine: InnoDB.

Table: UtilityBill

The UtilityBill table stores bill details, tied to utility accounts and indirectly to meters.

Field Data Type Description
id INT Unique identifier for the bill (Primary Key).
ep_object_type VARCHAR(50) Type of object, defaults to “UtilityBill”.
utility_account_id INT ID of the associated utility account (e.g., 25830).
invoice_date TIMESTAMP Invoice date (e.g., “2023-01-24T00:00:00.000Z”).
estimated BOOLEAN Indicates if bill is estimated (e.g., false).
account_holder_name VARCHAR(255) Account holder name (e.g., null).
amount_due DECIMAL(10, 2) Amount due (e.g., null).
previous_balance DECIMAL(10, 2) Previous balance (e.g., null).
amount_paid DECIMAL(10, 2) Amount paid (e.g., null).
archived BOOLEAN Indicates if bill is archived (e.g., false).
archive_reason VARCHAR(255) Reason for archiving (e.g., null).
metadata JSON Additional metadata (e.g., {}).
created_at TIMESTAMP Creation timestamp (e.g., “2023-04-18T22:36:11.000Z”).
start_date TIMESTAMP Billing period start (e.g., “2022-12-22T00:00:00.000Z”).
end_date TIMESTAMP Billing period end (e.g., “2023-01-24T00:00:00.000Z”).
  • Constraints:
    • id is the primary key.
    • utility_account_id is a foreign key to UtilityAccount(id).
  • Engine: InnoDB.

Table: UtilityCharge

The UtilityCharge table stores charge details for each bill.

Field Data Type Description
id INT Unique identifier for the charge (Primary Key).
ep_object_type VARCHAR(50) Type of object, defaults to “UtilityCharge”.
utility_bill_id INT ID of the associated bill (e.g., 1493645).
utility_account_id INT ID of the associated account (e.g., 25830).
utility_meter_id INT ID of the associated meter (e.g., 35751).
amount DECIMAL(10, 2) Charge amount (e.g., 1513.7).
description TEXT Charge description (e.g., null).
utility_charge_type VARCHAR(50) Type of charge (e.g., “usage_cost”).
start_date TIMESTAMP Charge period start (e.g., “2022-12-22T00:00:00.000Z”).
end_date TIMESTAMP Charge period end (e.g., “2023-01-24T00:00:00.000Z”).
line_item_group_id VARCHAR(36) Group identifier (e.g., “37f9f04d-5f16-455b-8eef-9070db0fc64d”).
  • Constraints:
    • id is the primary key.
    • utility_bill_id is a foreign key to UtilityBill(id).
    • utility_account_id is a foreign key to UtilityAccount(id).
    • utility_meter_id is a foreign key to UtilityMeter(id).
  • Engine: InnoDB.

Table: UtilityUsage

The UtilityUsage table stores usage details for each bill.

Field Data Type Description
id INT Unique identifier for the usage (Primary Key).
ep_object_type VARCHAR(50) Type of object, defaults to “UtilityUsage”.
utility_bill_id INT ID of the associated bill (e.g., 1493645).
utility_account_id INT ID of the associated account (e.g., 25830).
utility_meter_id INT ID of the associated meter (e.g., 35751).
amount DECIMAL(10, 2) Usage amount (e.g., 17920 for usage, 50.4 for demand).
estimated_meter_read BOOLEAN Indicates if meter read is estimated (e.g., false).
description TEXT Usage description (e.g., null).
utility_usage_type VARCHAR(50) Type of usage (e.g., “usage_amount”, “demand_consumption”).
measurement_unit VARCHAR(50) Unit of measurement (e.g., “kWh”, “kW”).
start_date TIMESTAMP Usage period start (e.g., “2022-12-22T00:00:00.000Z”).
end_date TIMESTAMP Usage period end (e.g., “2023-01-24T00:00:00.000Z”).
kbtus DECIMAL(10, 2) Energy usage in kBTUs (e.g., 61143.04).
lbs_co2 DECIMAL(10, 2) CO2 emissions in pounds (e.g., 15411.2).
gallons DECIMAL(10, 2) Water usage in gallons (e.g., 0).
supply BOOLEAN Indicates if usage is for supply (e.g., false).
line_item_group_id VARCHAR(36) Group identifier (e.g., “37f9f04d-5f16-455b-8eef-9070db0fc64d”).
  • Constraints:
    • id is the primary key.
    • utility_bill_id is a foreign key to UtilityBill(id).
    • utility_account_id is a foreign key to UtilityAccount(id).
    • utility_meter_id is a foreign key to UtilityMeter(id).
  • Engine: InnoDB.

Relationships

  • Property to UtilityMeter: UtilityMeter links to Property via property_id, reflecting the property as a wrapper for meters.
  • UtilityMeter to UtilityAccount: UtilityMeter links to UtilityAccount via utility_account_id. Accounts are independent and can span multiple properties.
  • UtilityBill to UtilityAccount: UtilityBill links to UtilityAccount via utility_account_id, tying bills to accounts.
  • UtilityBill to UtilityCharge and UtilityUsage: UtilityCharge and UtilityUsage link to UtilityBill, UtilityAccount, and UtilityMeter via their respective IDs, detailing costs and consumption.

Notes

  • Engine: All tables use InnoDB for foreign key and transaction support.
  • Data Types:
    • INT for IDs, VARCHAR for strings (lengths estimated, adjustable).
    • DECIMAL(10, 2) for monetary and measurement values (e.g., amount, kbtus).
    • JSON for flexible metadata storage.
    • TIMESTAMP for date/time fields.
  • Nullability: Fields like service_address, amount_due, etc., allow NULL where specified in the original schema.
  • Usage: These tables are created in order (Property, UtilityAccount, UtilityMeter, UtilityBill, UtilityCharge, UtilityUsage) to satisfy foreign key dependencies.

This schema provides a structured, relational view of properties, meters, accounts, bills, charges, and usage, ready for MySQL implementation.