ADR 0006: EF Core + SQLite as Production Persistence

Status

Superseded (Milestone 3) — all re-evaluation triggers fired in M3-02 (Docker Compose + PostgreSQL). SQLite has been replaced by PostgreSQL / Npgsql. This ADR is retained for historical context.

Context and Problem Statement

The application needs a durable persistence layer for the production configuration. The choice involves two orthogonal questions: which ORM or data access library, and which database engine.

For a showcase project the database engine must not require a running server or Docker infrastructure to get started — the repository should clone and run with a single dotnet run.

Decision Drivers

  • Zero external infrastructure dependency for local development and CI in Milestone 1 and 2.
  • Schema changes must be version-controlled and reproducible (migration-based, not schema-diff).
  • Domain IDs are always generated by the domain layer, never by the database.
  • The ORM must not require a parameterless constructor or public setters on the aggregate — the domain model must remain the authority on its own invariants.
  • The persistence implementation is behind the ITicketRepository / IUnitOfWork port (ADR 0008), so the choice is isolated to the infrastructure layer.

Considered Options

Option A — EF Core + SQLite (Selected)

EF Core with the SQLite provider. Schema is defined via fluent IEntityTypeConfiguration<T>. Migrations are code-generated and version-controlled. Applied automatically at startup when Persistence:Provider = Sqlite.

Option B — Dapper + SQLite

Raw SQL queries with Dapper for lightweight mapping. More explicit control over SQL, but requires hand-authoring every query and managing schema scripts outside the codebase tooling. Adds friction for a showcase project where demonstrating ORM integration is part of the scope.

Option C — EF Core + PostgreSQL from Milestone 1

Use the intended production database engine from the start. Eliminates the SQLite-specific workarounds (see below), but requires Docker or a running Postgres server for every developer and every CI run from day one. Deferred to Milestone 3 when Docker Compose infrastructure is introduced.

Decision Outcome

Chosen option: Option A — EF Core + SQLite.

Why this trade-off makes sense for this project

  • SQLite is sufficient for a showcase application. A single file-based database removes the infrastructure barrier entirely. The architectural choices (ports, migrations, fluent config) are demonstrable without a server dependency.
  • EF Core migrations give schema changes a traceable history. Each schema change is an explicit migration file committed to the repository, reviewable in PRs, and replayable on any environment.
  • ValueGeneratedNever() enforces domain ID ownership. The database never generates an ID. Every TicketId is created by TicketId.New() in the domain layer before the entity reaches the repository. This is enforced in the EF Core configuration — not just by convention.
  • Auto-apply migrations at startup is acceptable for this scope. For a single-file SQLite database with a small schema, running MigrateAsync() on startup is safe and keeps the operational surface minimal. This would not be acceptable for a shared production database.

SQLite DateTimeOffset limitation

SQLite stores all values as TEXT, INTEGER, or REAL. It has no native DateTimeOffset column type. EF Core's default mapping stores DateTimeOffset as TEXT, which SQLite cannot use in ORDER BY expressions that require numeric comparison.

To preserve correct sort order for CreatedAt and DueAt, both are stored as INTEGER (UTC ticks as long) via explicit value converters:

Property C# Type Column type Converter
CreatedAt DateTimeOffset INTEGER DateTimeOffsetToBinaryConverter
DueAt DateTimeOffset? INTEGER Custom nullable ticks converter
Id TicketId TEXT TicketIdConverter (Guid)

This is a SQLite-specific workaround. When the provider is replaced with PostgreSQL (which has native timestamptz), these converters can be removed.

Consequences

Positive Consequences

  • The repository runs out of the box with no external dependencies.
  • Schema history is version-controlled alongside application code.
  • EF Core's change tracking integrates cleanly with the EfUnitOfWork implementation (_dbContext.SaveChangesAsync).
  • Indexes on CreatedAt and Status support the search and paging queries without additional configuration.

Negative Consequences

  • The DateTimeOffset → long workaround is SQLite-specific and must be removed when switching providers. It adds a small cognitive overhead when reading the migration (CreatedAt INTEGER is not self-explanatory).
  • Auto-applying migrations at startup is not safe for a shared multi-instance production deployment. This must be changed before Milestone 3.
  • SQLite does not enforce foreign keys by default, does not support concurrent writes well, and has limited ALTER TABLE support. None of these are blocking for Milestone 1–2 scope.

Re-evaluation Triggers

Milestone 3 (M3-02: Docker Compose — API + DB) is the planned trigger for replacing SQLite with PostgreSQL. At that point:

  1. Replace Microsoft.EntityFrameworkCore.Sqlite with Npgsql.EntityFrameworkCore.PostgreSQL.
  2. Remove the DateTimeOffset → long value converters — PostgreSQL natively handles timestamptz.
  3. Replace startup MigrateAsync() with an explicit migration step in the deployment pipeline.
  4. Update the InfrastructureComposition switch to add "Postgres" as a valid provider value.
  • ADR 0007 – Swappable Persistence via Runtime Provider Switch
  • src/ServiceDeskLite.Infrastructure/Persistence/Configurations/TicketConfiguration.cs
  • src/ServiceDeskLite.Infrastructure/Persistence/Configurations/TicketIdConverter.cs
  • src/ServiceDeskLite.Infrastructure/Persistence/Migrations/20260219091433_InitialCreate.cs
  • src/ServiceDeskLite.Infrastructure/Persistence/DependencyInjection/InfrastructureServiceCollectionExtensions.cs
  • src/ServiceDeskLite.Api/Program.cs — startup migration call