Smart Energy Data Model Architecture
Entities, Relationships, Storage Tiers, RLS, Example Queries
Ein Production-Grade-Multi-Tenant-Datenmodell für DER-Platforms: Identity, Telemetry, Forecasting, Grid Constraints, Tariffs/Contracts, Control Policies und Dispatch Execution. Enthält Storage-Tier-Design und Security-Primitives (RLS, RBAC, Immutable Audit Logging).
Prototyp-Umgebung. Illustrative Daten.
Visualisiert
- •Entity Graph (Tenant/Site/Asset/Telemetry/Forecast/Grid/Tariff/Contract/Policy/Dispatch)
- •N–N Asset-to-Contract Allocation und Market Settlement Concepts
- •Storage Tiers (Postgres + TSDB + Event Store)
- •Isolation (Row-Level Security) und Auditability
- •Example Queries für Operations und Portfolio Finance
Verwendet für
- •Design von Multi-Tenant-Datenmodellen für DER-Platforms
- •Validierung von Storage-Tier-Design und Security-Primitives
- •Optimierung von Data-Model-Architecture für Skalierbarkeit
Verwandte Systemansichten
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