feat(persistence): Menlo.Application — EF Core + PostgreSQL persistence layer

[DigiBanks99]

Problem Statement

The Menlo home management application has a complete domain model (User, IAuditable, IAggregateRoot, strongly-typed IDs, value objects) in Menlo.Lib, but has no persistence layer. There is no database context, no EF Core schema configuration, no migrations, and no connection between the domain model and PostgreSQL. Family data cannot be saved or retrieved.

The existing docs/requirements/persistence/implementation.md points to Menlo.Api/Persistence as the home for the DbContext and migrations — an architectural decision that has since been revised. This PRD supersedes that guidance.

Additionally, the domain model lacks an ISoftDeletable contract to support the soft-delete requirement specified in docs/requirements/persistence/specifications.md (FR-005).

Solution

Introduce a new shared library project, Menlo.Application, to src/lib/. This project becomes the permanent home for:

• The single MenloDbContext connecting the domain model to PostgreSQL
• Per-slice focused context interfaces that give each bounded context access only to its own data
• EF Core interceptors that transparently enforce auditing and soft-delete behaviour
• EF Core entity type configurations and migrations
• Future command and query handlers for vertical slices

Add the ISoftDeletable and ISoftDeleteStampFactory contracts to Menlo.Lib, consistent with the existing IAuditable / IAuditStampFactory pair.

Wire Menlo.Application into Menlo.Api and Menlo.AppHost, ensuring the database is provisioned via Aspire, migrations run automatically on API startup, and the User entity (in the shared PostgreSQL schema) serves as the first end-to-end validated example of the complete stack.

Update all affected documentation and architecture diagrams to reflect the new project.

User Stories

1. As a developer, I want a Menlo.Application project in src/lib/, so that all persistence infrastructure is co-located with future application-layer handlers and is not mixed into the API host.
2. As a developer, I want a single MenloDbContext that I only need to configure once, so that cross-cutting concerns like naming conventions, value converters, and global filters are applied consistently across all bounded contexts.
3. As a developer, I want PostgreSQL schema separation enforced via EF Core entity configurations, so that each bounded context's tables are isolated in their own PostgreSQL schema as required by FR-002.
4. As a developer, I want snake_case naming applied automatically to all table and column names, so that PostgreSQL naming conventions are met without manually specifying column names on every property.
5. As a developer, I want strongly typed IDs (e.g., UserId) automatically mapped to uuid PostgreSQL columns via centrally registered value converters, so that I never need to configure this mapping per entity.
6. As a developer, I want monetary values stored as numeric(18,4) in PostgreSQL, so that rounding errors are prevented and FR-010 is satisfied.
7. As a developer, I want all datetime values stored in UTC (timestamptz), so that FR-011 is satisfied and there are no timezone-related data bugs.
8. As a developer, I want per-slice focused context interfaces (e.g., IUserContext) that expose only the DbSet<T> properties relevant to that bounded context, so that a feature handler cannot accidentally access data from a different domain boundary.
9. As a developer, I want MenloDbContext to implement all slice interfaces, so that there is a single scoped EF Core instance per request and shared change tracking is preserved.
10. As a developer, I want an ISoftDeletable interface in Menlo.Lib with IsDeleted, DeletedAt, and DeletedBy properties, so that the soft-delete contract is part of the domain model and not an infrastructure detail.
11. As a developer, I want an ISoftDeleteStampFactory interface in Menlo.Lib, so that the soft-delete interceptor can resolve who is performing the deletion without depending on EF Core internals.
12. As a developer, I want a SoftDeleteInterceptor that intercepts EntityState.Deleted transitions for ISoftDeletable entities and converts them to EntityState.Modified, so that soft deletes happen transparently without callers needing to know.
13. As a developer, I want the SoftDeleteInterceptor to set IsDeleted = true, DeletedAt, and DeletedBy on soft-deleted entities, so that the full deletion audit trail is preserved as required by BR-004.
14. As a developer, I want an AuditingInterceptor that automatically calls .Audit() on all IAuditable entities during SaveChangesAsync, so that audit fields are populated without callers needing to do it manually.
15. As a developer, I want EF Core global query filters applied to all ISoftDeletable entities, so that deleted records are automatically excluded from all queries without any caller needing to add .Where(x => !x.IsDeleted).
16. As a developer, I want a way to bypass the global soft-delete filter via .IgnoreQueryFilters(), so that administrative or restore workflows can still access deleted records.
17. As a developer, I want MigrateAsync() called on API startup, so that pending migrations are applied automatically and the API fails to start (with a non-zero exit code) if migration fails — ensuring a bad schema change is never deployed silently.
18. As a developer, I want Aspire to provision the PostgreSQL container and inject the connection string automatically in development, so that there is no manual setup required to run the application locally.
19. As a developer, I want SSL enforced in the Npgsql connection string in production environments and disabled in local development, so that FR-008 (encryption in transit) is satisfied without breaking the Aspire local dev workflow.
20. As a developer, I want Npgsql's default connection pooling used without additional configuration, so that FR-009 is satisfied with zero operational overhead at this application's scale.
21. As a developer, I want an AddMenloApplication() DI extension method that registers the MenloDbContext, interceptors, and all slice interface bindings, so that Menlo.Api wires everything up with a single call.
22. As a developer, I want the initial EF Core migration to create the shared.users table with all required columns, so that the User entity can be persisted from day one.
23. As a developer, I want the User entity updated to implement ISoftDeletable, so that users can be soft-deleted consistently with all other entities in the system.
24. As a developer, I want an integration test suite in Menlo.Application.Tests that uses a real PostgreSQL container via TestContainers, so that the persistence layer is tested against the actual database engine and not an in-memory approximation.
25. As a developer, I want the integration tests to verify that saving a User through IUserContext persists it correctly to the database, so that the slice interface pattern is confirmed to work end-to-end.
26. As a developer, I want the integration tests to verify that soft-deleting a User sets the correct flags and excludes the record from standard queries, so that the global query filter and interceptor work as designed.
27. As a developer, I want the integration tests to verify that auditing fields (CreatedBy, CreatedAt, ModifiedBy, ModifiedAt) are automatically populated on create and update, so that the AuditingInterceptor is confirmed correct.
28. As a developer, I want the integration tests to verify that the EF Core migration runs successfully against a clean PostgreSQL container, so that the migration is confirmed valid before it reaches production.
29. As a developer, I want the repo structure specifications, implementation guide, and architecture document updated to reflect Menlo.Application, so that future developers follow the correct pattern from the start.
30. As a developer, I want the C4 component diagram updated to show Menlo.Application as the application and data layer, so that the architecture diagrams remain accurate.
31. As a family member using the Menlo application, I want my data to be stored reliably and consistently, so that I never lose information due to a schema bug or missing migration.
32. As a family member using the Menlo application, I want deleted records to be recoverable by an administrator, so that accidental deletions do not permanently destroy data.
33. As a family member using the Menlo application, I want every change to my data to be traceable to a specific person and time, so that I have a clear audit trail for financial and planning decisions.
34. As the Menlo application, I want all database connections to use SSL in production, so that data is encrypted in transit and cannot be intercepted.
35. As the Menlo application, I want migrations to run before the API begins serving traffic, so that the database schema is always consistent with the deployed code.

Implementation Decisions

Project Structure

• A new Menlo.Application project is added to src/lib/. It references Menlo.Lib and contains EF Core infrastructure code.
• Menlo.Api adds a project reference to Menlo.Application.
• Menlo.Api does not reference EF Core directly; all EF Core concerns are encapsulated in Menlo.Application.
• The project is organised into a Common/ folder (for the DbContext, interceptors, and DI setup) and a folder per bounded context (e.g., Auth/ for the shared schema).

Single DbContext

• One MenloDbContext serves the entire application. It is registered as scoped in DI.
• Multi-schema PostgreSQL organisation is achieved through EF Core entity type configurations, not multiple DbContexts.
• MenloDbContext implements all per-slice context interfaces. Registering a slice interface resolves to the same scoped MenloDbContext instance, preserving shared change tracking.

Slice Context Interfaces

• Each bounded context has a focused interface exposing only its own DbSet<T> properties and SaveChangesAsync.
• Feature handlers inject the slice interface, not MenloDbContext directly, enforcing domain boundary access control at the type system level.
• SaveChangesAsync on a slice interface commits all pending changes tracked by the underlying MenloDbContext — the interfaces are role interfaces, not isolated units of work.
• The first slice interface is IUserContext for the shared schema / Auth bounded context.

Interceptors

• Two ISaveChangesInterceptor implementations are registered: AuditingInterceptor and SoftDeleteInterceptor.
• AuditingInterceptor inspects all entities in EntityState.Added or EntityState.Modified that implement IAuditable and calls .Audit() with the appropriate AuditOperation.
• SoftDeleteInterceptor inspects all entities in EntityState.Deleted that implement ISoftDeletable, converts the state to EntityState.Modified, and sets IsDeleted, DeletedAt, and DeletedBy using ISoftDeleteStampFactory.
• Both interceptors require IAuditStampFactory / ISoftDeleteStampFactory to be resolvable from DI to obtain the current actor.

Domain Contracts (Menlo.Lib)

• ISoftDeletable is added to Menlo.Lib.Common.Abstractions with properties: IsDeleted (bool), DeletedAt (DateTimeOffset?), and DeletedBy (UserId?).
• ISoftDeleteStampFactory is added to Menlo.Lib.Common.Abstractions following the same pattern as IAuditStampFactory.
• The User entity is updated to implement ISoftDeletable.

EF Core Configuration

• EFCore.NamingConventions package is used to apply PostgreSQL snake_case naming conventions globally via UseSnakeCaseNamingConvention().
• All strongly typed IDs are mapped to uuid columns via centrally registered value converters in OnModelCreating, applied by convention to all properties of matching types.
• Monetary values (decimal) are mapped to numeric(18,4) via value type configuration in entity type configurations.
• All DateTimeOffset properties are stored as timestamptz (Npgsql default), ensuring UTC storage.
• Global query filters are applied to all ISoftDeletable entities to exclude soft-deleted records from standard queries.

PostgreSQL Schemas

• The shared schema hosts cross-cutting entities, starting with the User aggregate.
• Future bounded contexts will use: planning, budget, financial, events, household.

Migration Strategy

• EF Core migrations are managed in Menlo.Application.
• MigrateAsync() is called during Menlo.Api startup. A migration failure causes the API process to exit with a non-zero exit code, preventing the container from being marked healthy.
• The initial migration creates the shared.users table.

Aspire Integration

• Menlo.AppHost registers a PostgreSQL container resource via Aspire.
• Aspire injects the connection string into Menlo.Api automatically in development.
• In production, the connection string is provided via environment variable.
• SSL is required in production via the connection string (SSL Mode=Require); disabled locally.

Documentation Updates

• docs/requirements/repo-structure/specifications.md — add Menlo.Application to the lib specification.
• docs/requirements/repo-structure/implementation.md — add creation steps and project reference commands for Menlo.Application.
• docs/requirements/repo-structure/diagrams/repo-structure.md — update mermaid diagram to include Menlo.Application.
• docs/explanations/architecture-document.md — update code organisation structure to reflect Menlo.Application and the slice interface pattern.
• docs/requirements/persistence/implementation.md — replace outdated Menlo.Api/Persistence guidance with the Menlo.Application-based approach.
• docs/diagrams/c4-component-diagram.md — update to show Menlo.Application as the application and data layer, replacing per-feature repository references with slice context interfaces.

Testing Decisions

What Makes a Good Test

Tests must verify externally observable behaviour, not implementation details. For a persistence layer, this means:

• Verify that data saved through a slice context interface can be retrieved from a real PostgreSQL database.
• Verify that deleted entities are invisible to standard queries (the global filter's observable effect), not that the interceptor internally changed EntityState.
• Verify that audit fields contain correct values after a save, not that Audit() was called internally.
• Verify that migrations can be applied to a clean database successfully.
• Do not test that OnModelCreating registered a value converter, or that the interceptor's internal logic executed — these are implementation details.

Modules to Test

Menlo.Application.Tests — integration tests using Testcontainers.PostgreSql:

• Create a User, save via IUserContext, retrieve by ID — assert all fields match.
• Update a User, save — assert ModifiedBy and ModifiedAt changed; CreatedBy and CreatedAt did not.
• "Delete" a User — assert the record is not returned by standard queries; assert that querying with .IgnoreQueryFilters() returns it with IsDeleted = true, DeletedAt set, and DeletedBy set.
• Verify migration applies cleanly to a fresh PostgreSQL container and __EFMigrationsHistory contains the initial migration record.

Prior Art

• Menlo.Api.Tests/TestWebApplicationFactory.cs and Menlo.Api.Tests/ApiSmokeTests.cs — existing WebApplicationFactory + integration test pattern.
• Menlo.Lib.Tests/Common/Abstractions/AuditingContractsTests.cs — testing of auditing contract behaviour.
• Menlo.Api.Tests/Fixtures/TestFixture.cs — shared test fixture setup pattern.

Out of Scope

• NFR-004 — Automated Backups: Daily pg_dump to a separate local storage volume (RPO < 24 hours) is a deployment and infrastructure concern. It is deferred and must be tracked as a dedicated deployment task in a separate PRD. No application code is required for this.
• Encryption at Rest (FR-007): LUKS host-level encryption is a server configuration concern, not an application code concern. It is deferred.
• All bounded contexts beyond shared/Auth: The planning, budget, financial, events, and household schemas and their entities are out of scope. Only the infrastructure to support them (project structure, DbContext, interceptors, naming conventions) is delivered here.
• Application-layer handlers, commands, and queries: Menlo.Application will eventually host command/query handlers for vertical slices. That application logic is out of scope for this PRD.
• User management API endpoints: The User entity is used as a proof-of-concept for the persistence stack only. No CRUD API endpoints for users are delivered in this PRD.

Further Notes

• P0 Prerequisite — Agent Skill: Before any implementation begins, an agentskills.io skill must be created that documents Menlo's EF Core + PostgreSQL persistence conventions (slice interfaces, interceptors, snake_case naming, TestContainers, numeric(18,4), no in-memory testing). This skill enables AI-assisted implementation of all future persistence slices and must be completed first.
• NFR-004 Backup: Must be raised as a separate GitHub issue/PRD. RPO < 24 hours via automated pg_dump to a separate local storage location. Failure to track this leaves home server data unprotected against hardware failure.
• MenloDbContext is the implementation, slice interfaces are the API: No feature code should ever depend on MenloDbContext directly. All data access goes through the relevant slice context interface.
• TestContainers is the only approved testing database: In-memory EF Core providers are explicitly prohibited. All persistence tests must use Testcontainers.PostgreSql against a real PostgreSQL image to catch provider-specific behaviours.