Persistence

Persistence

EF Core 10 with a dual-provider setup — SQLite by default, Postgres opt-in. See ADR-0018.

Why dual-provider

• Local-first — fresh clone runs against SQLite (plans.db) with zero setup.
• Hosted-capable — a hosted instance can swap in Postgres via one config change.
• OSS-friendly — no commercial DB licence.

How to switch

// appsettings.json
{
  "Persistence": {
    "Provider": "postgres"            // or "sqlite" (default)
  },
  "ConnectionStrings": {
    "Plans": "Host=localhost;Database=plans;Username=postgres;Password=postgres"
  }
}

PersistenceServiceCollectionExtensions.AddErpPersistence(IConfiguration) reads Persistence:Provider and ConnectionStrings:Plans and registers the matching DbContext subclass.

DbContext layout

PlanDbContext              ← base, holds the model
├─ SqlitePlanDbContext     ← thin subclass, owns Migrations/Sqlite/
└─ PostgresPlanDbContext   ← thin subclass, owns Migrations/Postgres/

Two subclasses exist because EF Core rejects two ModelSnapshot classes keyed to the same context type. At runtime the active subclass is also registered under the base PlanDbContext service type so IPlanRepository stays provider-agnostic.

Adding a migration

When you change the model you must migrations add for both providers, or CI will fail the drift guard.

# SQLite
dotnet ef migrations add MyChange \
  --project src/ERP/Infrastructure/Persistence \
  --startup-project src/ApiService \
  --context SqlitePlanDbContext

# Postgres
export ERP_PERSISTENCE_CONNECTION='Host=localhost;Database=plans;Username=postgres;Password=placeholder'
dotnet ef migrations add MyChange \
  --project src/ERP/Infrastructure/Persistence \
  --startup-project src/ApiService \
  --context PostgresPlanDbContext

(The Postgres factory requires the env var even though has-pending-model-changes never opens the connection — a placeholder is fine.)

dotnet-ef is pinned in .config/dotnet-tools.json — use the tool restore to get a reproducible version: dotnet tool restore.

CI guards (issue #81)

Two jobs in .github/workflows/ci.yml protect the setup:

Job	What it does
Migration drift	dotnet ef migrations has-pending-model-changes for both contexts. Fails if you changed the model without migrations add.
Postgres migration smoke	Spins up postgres:16 and runs dotnet ef database update against it. Proves the Postgres migration actually applies.

Run locally:

./build.sh CheckMigrations           # drift, both providers
./build.sh MigrationsPostgresSmoke   # needs a live Postgres

TickerQ tables

TickerQ persists its operational store inside the same PlanDbContext under the ticker schema (Constants.DefaultSchema). SQLite ignores schemas, Postgres uses them. Migration: AddTickerQOperationalStore.

What lives where

Aggregate	Storage
SavedPlan (+ child collections)	Plans + targets + availability rows
ShareLinkToken	Per-plan tokens for the share-link feature (#80)
TickerQ TimeTickers / CronTickers / CronTickerOccurrences	Operational store under ticker schema

The repository contract (IPlanRepository in ERP.Application) stays provider-agnostic. Implementation is in ERP.Infrastructure.Persistence.PlanRepository.