ApiFeatures.Mqtt.Postgresqls 9.1.0

ApiFeatures.Mqtt.Postgresqls

PostgreSQL persistence layer for the ApiFeatures.Mqtt module, using Entity Framework Core 9 + Npgsql. Implements the four DB-tied Cores service interfaces (IMqttService, IMqttConnectionService, IBrokerService, IBrokerUserService) as an alternative to ApiFeatures.Mqtt.MongoDatabases. References ApiFeatures.Mqtt.Cores and ApiFeatures.Modules.Cores; never references ApiFeatures.Mqtt.Apis or ApiFeatures.Mqtt.MongoDatabases.

Schema

Greenfield, fixed snake_case names (no per-table overrides):

Enums (MqttConnectionStatuses, BrokerProtocols) persist as text (HasConversion<string>()). Timestamps are timestamptz (UTC). No relational FKs are declared — the Mongo layer has no referential link either, and the services manage cascade/orphan cleanup in code (DeleteByBrokerIdAsync); keeping parity avoids a behavioural change a FK would introduce.

Polymorphic MqOptions → jsonb

MqttEntity.Options (abstract MqOptions, discriminated by Type; concretes HiveMqOptions, SelfHostedBrokerOptions) maps to a single jsonb column via an EF ValueConverter<MqOptions, string> backed by an internal System.Text.Json Type-switch converter (Serializers/MqOptionsJsonConverter.cs). This mirrors the Apis-resident converter but is a deliberate local duplicate — not shared via Cores — to keep zero blast radius on the already-merged restructure (Tasks 1-6). The password stays embedded inside the jsonb (same trust boundary as Mongo); password-omission in API responses remains an Apis-layer mapper concern.

Usage

using ApiFeatures.Modules.Cores.Models.Options;
using ApiFeatures.Mqtt.Postgresqls.Extensions;

var postgresOptions = new ModulePostgresDatabaseOptions
{
    Host = "localhost",
    Port = 5432,          // optional, defaults to 5432
    DatabaseName = "mqtt",
    Username = "postgres",
    Password = "postgres"
};

builder.Services.AddMqttsModule(addMqttsFeatureOptions);
builder.Services.WithPostgresDatabase(postgresOptions);

WithPostgresDatabase registers the MqttPostgresDatabaseContext and the four Postgres service implementations, all keyed ServiceKeys.Default. It does not register IBrokerUserPasswordHasher — that implementation stays in ApiFeatures.Mqtt.Apis and is registered by AddMqttsModule; PostgresBrokerUserService resolves it lazily via keyed DI.

Duplicate-key → 409 translation

The Mongo layer relies on the Apis endpoint catching a Mongo-specific write exception. That catch does not fire for Postgres, so this library translates the Npgsql unique-violation itself, at the service layer:

• PostgresBrokerService.CreateAsync/UpdateAsync catch a DbUpdateException wrapping a Npgsql.PostgresException with SqlState == "23505" on ix_brokers_port → BrokerPortConflictException.
• PostgresBrokerUserService.CreateAsync/UpdateAsync do the same on ix_broker_users_broker_id_username → BrokerUserUsernameConflictException.

This asymmetry (Mongo translates at the endpoint, Postgres at the service) is intentional for this task; the pre-check validators (PortExistsAsync/UsernameExistsAsync) remain the friendly first line of defence in both providers, and the 23505 catch is the authoritative backstop for the concurrent-create race.

Migrations tooling

Migrations live inside this library (Migrations/); MigrationsAssembly is this assembly. A design-time factory (DesignTime/MqttPostgresDesignTimeDbContextFactory.cs) lets dotnet ef run without booting a host app — it reads the MQTT_POSTGRES_CONNECTION environment variable, falling back to Host=localhost;Port=5432;Database=mqtt;Username=postgres;Password=postgres.

Run from the repo root (requires dotnet-ef: dotnet tool install --global dotnet-ef):

# Create a new migration (authored inside this library)
dotnet ef migrations add <Name> \
  -p src/modules/mqtt/databases/ApiFeatures.Mqtt.Postgresqls \
  -c MqttPostgresDatabaseContext

# Apply the schema to a running Postgres (manual — operator/dev runs this; no auto-migrate on startup)
dotnet ef database update \
  -p src/modules/mqtt/databases/ApiFeatures.Mqtt.Postgresqls \
  -c MqttPostgresDatabaseContext

# (Re)generate the committed idempotent SQL script
dotnet ef migrations script --idempotent \
  -p src/modules/mqtt/databases/ApiFeatures.Mqtt.Postgresqls \
  -c MqttPostgresDatabaseContext \
  -o src/modules/mqtt/databases/ApiFeatures.Mqtt.Postgresqls/Migrations/Scripts/mqtt-postgres.sql

Alternatively, run the committed idempotent script directly (Migrations/Scripts/mqtt-postgres.sql) against your target database — it produces the same schema as dotnet ef database update.

Configuration

Bound to ModulePostgresDatabaseOptions (from ApiFeatures.Modules.Cores):

* required for a password-authenticated server; optional only for trust-auth local dev.

Dependencies

• ApiFeatures.Mqtt.Cores
• ApiFeatures.Modules.Cores
• BusinessFeatures.Cores
• Microsoft.EntityFrameworkCore (9.0.0)
• Npgsql.EntityFrameworkCore.PostgreSQL (9.0.2)