Schema Overview

Ampra uses a dual-database architecture: PostgreSQL for relational data with referential integrity, and MongoDB for high-volume time-series telemetry and prediction data.

---

PostgreSQL — Relational Database

Database: ampradb · ORM: Entity Framework Core · Provider: Npgsql

Entity Relationship Diagram

Legend: PK = Primary Key · FK = Foreign Key · UK = Unique Key · IX = Index

---

Table Reference

Users (ApplicationUser)

Extends ASP.NET Identity's IdentityUser with custom fields for email verification and profile data.

Column	Type	Nullable	Default	Description
Id	text	No	GUID	Primary key (Identity-generated)
UserName	text	Yes	—	Display name
Email	text	Yes	—	Unique email address
NormalizedEmail	text	Yes	—	Upper-case email for lookups
PasswordHash	text	Yes	—	PBKDF2-hashed password
FirstName	text	Yes	—	User's first name
LastName	text	Yes	—	User's last name
CreatedAt	timestamp with time zone	No	UTC NOW	Registration timestamp
IsEmailVerified	boolean	No	false	Email verification status
EmailVerificationCode	text	Yes	—	6-digit verification code
EmailVerificationCodeExpiry	timestamp with time zone	Yes	—	Code expiration time
LastCodeSentAt	timestamp with time zone	Yes	—	Rate limiting for code resend
FailedVerificationAttempts	integer	No	0	Counter for lockout logic
VerificationLockoutUntil	timestamp with time zone	Yes	—	Lockout expiry after failed attempts

Indexes:

• EmailIndex on NormalizedEmail
• UserNameIndex (unique) on NormalizedUserName

---

PowerGroups

Logical grouping of sun sources for organizational hierarchy (e.g., by site, region, or customer).

Column	Type	Nullable	Constraint	Description
Id	uuid	No	PK	Auto-generated GUID
Name	varchar(100)	No	Required	Display name for the group
Description	varchar(500)	Yes	—	Optional description
LogoUrl	varchar(500)	Yes	—	URL to group logo in MinIO
UserId	text	No	FK → Users.Id	Owner of this power group
CreatedAt	timestamp with time zone	No	Default UTC NOW	Creation timestamp
UpdatedAt	timestamp with time zone	No	Default UTC NOW	Last modification timestamp

Relationships:

• One-to-Many with SunSources (ON DELETE SET NULL — sources become ungrouped)
• Many-to-One with Users (ON DELETE CASCADE — user deletion removes all groups)

Indexes: UserId

---

SunSources

The core entity representing a monitored solar energy installation.

Column	Type	Nullable	Constraint	Description
Id	uuid	No	PK	Auto-generated GUID
Name	varchar(100)	No	Required	Display name
Description	varchar(500)	Yes	—	Optional description
IconUrl	text	Yes	—	Custom icon URL
UserId	text	No	FK → Users.Id	Owner
PowerGroupId	uuid	Yes	FK → PowerGroups.Id	Optional group assignment
ConnectionType	integer	No	Enum	0 = MQTT, 1 = Webhook
Latitude	double precision	Yes	[-90, 90]	GPS latitude
Longitude	double precision	Yes	[-180, 180]	GPS longitude
MqttTopic	text	Yes	—	Assigned MQTT topic path
MqttApiKey	text	Yes	—	64-char hex API key for MQTT auth
WebhookUrl	text	Yes	—	Generated webhook URL
WebhookSecret	text	Yes	—	Base64-encoded HMAC-SHA256 secret
IsActive	boolean	No	Default true	Active/inactive toggle
InstalledCapacityWatts	double precision	Yes	[0, 1,000,000]	Installed panel capacity in watts
Currency	varchar(3)	No	Default EUR	ISO 4217 currency code
SystemCost	numeric	Yes	—	Total system installation cost
MaintenanceCostPerYear	numeric	Yes	—	Annual maintenance cost
InstallationDate	timestamp with time zone	Yes	—	System installation date
CreatedAt	timestamp with time zone	No	Default UTC NOW	Creation timestamp
UpdatedAt	timestamp with time zone	No	Default UTC NOW	Last modification timestamp

Relationships:

• Many-to-One with Users (ON DELETE CASCADE)
• Many-to-One with PowerGroups (ON DELETE SET NULL)
• One-to-Many with WeatherData, KwhPriceHistories, SunSourceSharings (all CASCADE)

Indexes: UserId, PowerGroupId

---

WeatherData

Weather observations and forecasts linked to specific sun sources via geolocation.

Column	Type	Nullable	Description
Id	uuid	No	Primary key
SunSourceId	uuid	No	FK → SunSources.Id
Date	timestamp with time zone	No	Forecast date
TemperatureMax	double precision	Yes	Maximum temperature (°C)
TemperatureMin	double precision	Yes	Minimum temperature (°C)
ShortwaveRadiationSum	double precision	Yes	Daily solar radiation (MJ/m²)
WeatherCode	integer	Yes	WMO weather code
UvIndexMax	double precision	Yes	Maximum UV index
PrecipitationSum	double precision	Yes	Total precipitation (mm)
Sunrise	timestamp with time zone	Yes	Sunrise time
Sunset	timestamp with time zone	Yes	Sunset time
CreatedAt	timestamp with time zone	No	Record creation timestamp

Indexes:

• SunSourceId
• (SunSourceId, Date) — unique (prevents duplicate forecasts)

---

KwhPriceHistories

Historical electricity pricing used for ROI calculations.

Column	Type	Nullable	Description
Id	uuid	No	Primary key
SunSourceId	uuid	No	FK → SunSources.Id
PricePerKwh	numeric	No	Price per kilowatt-hour
EffectiveFrom	timestamp with time zone	No	Date this price becomes active
CreatedAt	timestamp with time zone	No	Record creation timestamp
UpdatedAt	timestamp with time zone	No	Last modification timestamp

Indexes:

• SunSourceId
• (SunSourceId, EffectiveFrom) — composite for efficient price lookups by date

---

SunSourceSharings

Controls public visibility of sun source data for sharing features.

Column	Type	Nullable	Default	Description
Id	uuid	No	—	Primary key
SunSourceId	uuid	No	—	FK → SunSources.Id
IsEnabled	boolean	No	false	Master sharing toggle
ShareMonitor	boolean	No	false	Share real-time metrics
ShareWeather	boolean	No	false	Share weather data
ShareForecast	boolean	No	false	Share ML predictions
ShareReturns	boolean	No	false	Share ROI calculations
CreatedAt	timestamp with time zone	No	UTC NOW	Creation timestamp
UpdatedAt	timestamp with time zone	No	UTC NOW	Last modification timestamp

Indexes: SunSourceId (unique — one sharing config per source)

---

UserSettings

Key-value store for user preferences.

Column	Type	Nullable	Description
Id	uuid	No	Primary key
UserId	text	No	FK → Users.Id
SettingType	integer	No	Enum value (see below)
Value	text	No	Setting value as string
UpdatedAt	timestamp with time zone	No	Last modification timestamp

Setting Types:

Value	Name	Description	Valid Values
1	TemperatureUnit	Display temperature unit	0 (Metric/°C), 1 (Imperial/°F)
2	AutoUpdateWeatherData	Auto-refresh weather on schedule	0 (disabled), 1 (enabled)
3	AutoRunPredictions	Auto-run ML predictions on schedule	0 (disabled), 1 (enabled)

Indexes: (UserId, SettingType) — unique (one value per setting per user)

---

ASP.NET Identity Tables

These tables are auto-managed by ASP.NET Identity and should not be modified directly:

Table	Purpose
AspNetRoles	Role definitions (Overseer, Admin, User)
AspNetUserRoles	User-to-role assignments
AspNetUserClaims	User claims storage
AspNetRoleClaims	Role claims storage
AspNetUserLogins	External login providers
AspNetUserTokens	Authentication tokens

---

MongoDB — Time-Series Database

Database: ampradb · Driver: MongoDB.Driver (C#) / PyMongo (Python)

Collections

Collection	Purpose	Indexed Fields
normalized_sun_source_data	Real-time telemetry data points	sunSourceId, timestamp
predictions	ML model prediction outputs	sunSourceId, createdAt
model_metadata	Trained model information	sunSourceId

---

normalized_sun_source_data

Each document represents a single telemetry snapshot from a solar installation:

Field	BSON Type	Description
_id	ObjectId	Auto-generated document ID
sunSourceId	String (GUID)	Source identifier
timestamp	DateTime	Measurement time (UTC)
Battery Metrics
stateOfCharge	Double	Battery charge level (%)
stateOfHealth	Double	Battery health (%)
batteryVoltage	Double	Battery voltage (V)
batteryCurrent	Double	Battery current (A)
batteryTemperature	Double	Battery temperature (°C)
batteryCapacity	Double	Total battery capacity (Ah)
remainingCapacity	Double	Remaining battery capacity (Ah)
Solar Metrics
solarVoltage	Double	PV array voltage (V)
solarCurrent	Double	PV array current (A)
solarPower	Double	PV production power (W)
Load Metrics
outputVoltage	Double	Inverter output voltage (V)
outputCurrent	Double	Inverter output current (A)
outputFrequency	Double	Output frequency (Hz)
loadPower	Double	Current load consumption (W)
loadPercentage	Double	Load as % of rated capacity
apparentPower	Double	Apparent power (VA)
Grid Metrics
gridVoltage	Double	Grid voltage (V)
gridCurrent	Double	Grid current (A)
gridFrequency	Double	Grid frequency (Hz)
Energy Totals
totalEnergyProduced	Double	Lifetime energy produced (kWh)
totalEnergyConsumed	Double	Lifetime energy consumed (kWh)
dailyEnergyProduced	Double	Today's energy produced (kWh)
dailyEnergyConsumed	Double	Today's energy consumed (kWh)
Status
operatingMode	String	Current operating mode
deviceStatus	String	Device health status
faultCodes	Array<String>	Active fault codes
warningCodes	Array<String>	Active warning codes
Audit
rawData	Document	Original unmodified payload
metadata	Document	Key-value pairs for additional context

---

Migration History

#	Migration	Date	Changes
1	InitialIdentity	2026-02-07	ASP.NET Identity tables
2	AddEmailVerification	2026-02-07	Verification code fields on User
3	AddLastCodeSentAt	2026-02-07	Rate limiting for code resend
4	AddPowerGroupsAndSunSources	2026-02-07	Core entity tables
5	AddLocationToSunSource	2026-02-10	Latitude/Longitude fields
6	AddLogoUrlToPowerGroup	2026-02-11	Group branding
7	AddWeatherData	2026-02-14	Weather observations table
8	AddUserSettings	2026-02-14	User preferences table
9	AddIconUrlToSunSource	2026-02-14	Custom source icons
10	RemoveProtocolFromSunSource	2026-02-21	Schema cleanup
11	AddInstalledCapacityToSunSource	2026-02-23	Panel capacity tracking
12	AddKwhPriceHistoryAndCurrency	2026-02-24	ROI pricing model
13	AddSunSourceSharing	2026-03-01	Public sharing feature
14	SelfHostedMqtt	2026-03-02	MQTT credential fields
15	PendingModelSync	2026-03-06	Schema alignment
16	PendingChanges	2026-03-06	Schema alignment
17	BusinessLogicAuditOverhaul	2026-03-06	SystemCost, MaintenanceCost, InstallationDate, PricePerKwh precision