Smart Energy Data Model Architecture
Entities, Relationships, Storage Tiers, RLS, Example Queries
A production-grade multi-tenant data model for DER platforms: identity, telemetry, forecasting, grid constraints, tariffs/contracts, control policies, and dispatch execution. Includes storage-tier design and security primitives (RLS, RBAC, immutable audit logging).
Prototype environment. Illustrative data.
What it visualizes
- •entity graph (tenant/site/asset/telemetry/forecast/grid/tariff/contract/policy/dispatch)
- •N–N asset-to-contract allocation and market settlement concepts
- •storage tiers (Postgres + TSDB + event store)
- •isolation (row-level security) and auditability
- •example queries for operations and portfolio finance
Used for
- •designing multi-tenant data models for DER platforms
- •validating storage-tier design and security primitives
- •optimizing data model architecture for scalability
Related System Views
Prototype 04
Smart Energy Data Model Architecture
Entity Relationships & Multi-Tenant System Structure / DER Platform Data Model
Identity Domain
Tenant
• tenantId
• name
• plan
• complianceRegion
Site
• siteId
• tenantId (FK)
• timezone
• address / geo
• gridOperatorId
Asset
• assetId
• siteId (FK)
• assetType
(PV, Battery, EVSE, Meter, Inverter, Controller)
• manufacturerModel
• commissioningDate
• status
EVSE
• evseId
• siteId (FK)
• clusterId
• connectorTypes
• maxKw
• ocppId
Operations Domain
TelemetryPoint
• assetId (FK)
• ts
• metric
(power_kw, voltage_v, soc_pct, temp_c, freq_hz)
• value
• qualityFlag
• source
ForecastPoint
• siteId / assetId (FK)
• ts
• metric
• predictedValue
• modelVersion
• confidence
LoadProfile
• profileId
• siteId (FK)
• period (15m/1h/1d)
• seriesRef
GridConnection
• connectionId
• siteId (FK)
• operatorId (DSO/TSO)
• voltageLevel
• interconnectionCapacityImportKw
• interconnectionCapacityExportKw
• curtailmentPolicy
• protectionScheme
• marketRegion
GridConstraint
• constraintId
• connectionId (FK)
• type
(exportLimit, reactivePower, rampRate)
• value
• effectiveFrom / to
ChargingSession
• sessionId
• evseId (FK)
• startedAt / endedAt
• kwhDelivered
• tariffApplied
• vehicleId (optional)
Market Domain
TariffModel
• tariffId
• siteId (FK)
• marketRegion
• contractId (FK)
• rateStructure
TariffRate
• tariffId (FK)
• timeWindow
• buyRate
• sellRate
• currency
• unit (€/kWh)
• peakFlag
EnergyContract
• contractId
• counterparty
• contractType
(PPA, Retail, Feed-in, Flexibility)
• settlementModel
• start / end
• termsRef
AssetContractLink
• assetId (FK)
• contractId (FK)
• allocatedCapacity
• effectiveFrom / to
EmissionFactor
• factorId
• marketRegion
• ts (or date)
• co2PerKwh
• source (grid mix)
• methodVersion
Control Domain
ControlPolicy
• policyId
• scope (site/asset/portfolio)
• objective
(costMin, CO2Min, resilienceMax)
• constraintsRef
• version
• enabled
DispatchPlan
• planId
• scopeId
• horizon
• createdAt
• optimizerVersion
• status
DispatchCommand
• commandId
• assetId (FK)
• tsScheduled
• commandType
(charge, discharge, curtail, setpoint)
• targetValue
• rampRate
• executedAt
• executionResult
Entity Relationships
• Tenant 1—N Site
• Site 1—N Asset
• Site 1—1 GridConnection
• Asset 1—N TelemetryPoint
• Site 1—N TariffModel
• TariffModel 1—N TariffRate
• Site 1—N ControlPolicy
• ControlPolicy 1—N DispatchPlan
• DispatchPlan 1—N DispatchCommand
• GridConnection 1—N GridConstraint
• Asset N—N EnergyContract (via AssetContractLink)
• EVSE 1—N ChargingSession
Storage Architecture (3-Tier Model)
Relational DB (PostgreSQL)
• Tenant / Site / Asset
• Contracts & Tariffs
• Control Policies
• Permissions & RBAC
• Reference data
Time-Series DB (InfluxDB/TimescaleDB)
• Telemetry streams
• Forecast points
• KPI aggregations
• Load profiles
• Performance metrics
Event Store (Kafka/EventStore)
• Control events
• Audit trail
• Dispatch commands
• State transitions
• System alerts
Data Access Layer
RLS / Tenant Isolation
Row-level security enforced at DB layer with tenant_id scoping
Per-Site RBAC
Role-based access control with site-level permissions granularity
Audit Logging
All mutations logged with user context and timestamp for compliance
Example Queries (Production-Ready)
Query 1: Site Net Import/Export
Show site-level net import/export over last 24h with 15-minute granularity
SELECT ts, SUM(value) as net_kw
FROM TelemetryPoint
WHERE assetId IN (SELECT assetId FROM Asset WHERE siteId = ?)
AND metric = 'grid_power_kw'
AND ts > NOW() - INTERVAL '24 hours'
GROUP BY time_bucket('15 minutes', ts)
FROM TelemetryPoint
WHERE assetId IN (SELECT assetId FROM Asset WHERE siteId = ?)
AND metric = 'grid_power_kw'
AND ts > NOW() - INTERVAL '24 hours'
GROUP BY time_bucket('15 minutes', ts)
Query 2: Portfolio ROI by Segment
Compute ROI for segment=commercial, region=DE, with tariff constraints
SELECT s.siteId, s.name,
SUM(revenue) / SUM(capex) as roi
FROM Site s
JOIN TariffModel tm ON s.siteId = tm.siteId
WHERE s.segment = 'commercial'
AND tm.marketRegion = 'DE'
GROUP BY s.siteId
SUM(revenue) / SUM(capex) as roi
FROM Site s
JOIN TariffModel tm ON s.siteId = tm.siteId
WHERE s.segment = 'commercial'
AND tm.marketRegion = 'DE'
GROUP BY s.siteId