Database Pattern & Entity Framework
Relevant source files
The following files were used as context for generating this wiki page:
The database architecture in this project is built upon Entity Framework Core (EF Core) using the Code-First approach. It centralizes data access through a single AppDbContext while attempting to maintain modularity by applying configurations for various domain entities such as Customers, Users, Contracts, and Leave Types.
The system utilizes SQLite as the primary database engine, with connection strings managed via standard .NET configuration providers. The architecture supports automated migrations and data seeding during application initialization to ensure environment consistency across development and testing.
Sources: packages/api/Common/Database/AppDbContext.cs:11-34, packages/api/Common/Database/DatabaseModule.cs:13-30, REVIEW.md:4-20
Core Architecture
The project employs a modular service-based architecture where individual modules (e.g., Customers, Users) interact with the central database context. While the AppDbContext serves as the primary gateway to the data store, current architectural reviews suggest moving towards more isolated module configurations to reduce coupling.
Data Flow and Initialization
The following diagram illustrates the application startup and database initialization flow:
The initialization process ensures that the schema is up-to-date through MigrateAsync() and populates initial data via module-specific seeders.
Sources: packages/api/Common/Database/DatabaseModule.cs:22-30, packages/api/Program.cs:15-25
Entity Configuration & Context
The AppDbContext manages several DbSet properties representing the system's core entities. Configuration is decoupled from the context class using specialized configuration classes for each entity.
Database Sets (DbSets)
| Entity | DbSet Name | Description |
|---|---|---|
Customer | Customers | Management of client organizations and contact details. |
User | Users | System users, roles, and authentication identities. |
Contract | Contracts | Project or service agreements between customers and consultants. |
ContractTask | ContractTasks | Specific line items or tasks associated with a contract. |
LeaveType | LeaveTypes | Catalog of available leave categories. |
UserLeaveAllowance | UserLeaveAllowances | Tracking of leave quotas per user and year. |
Counter | Counters | Shared sequential number generation for entities. |
Sources: packages/api/Common/Database/AppDbContext.cs:16-22, packages/api/Migrations/AppDb/AppDbContextModelSnapshot.cs:22-255
Relationship Mapping
The project defines complex relationships, particularly between contracts, customers, and consultants.
Entity relationships are configured with restricted delete behaviors (e.g., DeleteBehavior.Restrict) to prevent accidental cascading deletes of critical data.
Sources: packages/api/Migrations/AppDb/20260515084245_AddContractsAndCounters.Designer.cs:158-185, packages/api/Migrations/AppDb/AppDbContextModelSnapshot.cs:222-253
Configuration and Best Practices
Database configuration is primarily handled in DatabaseModule.cs, which retrieves the AppDb connection string from IConfiguration.
public static IServiceCollection AddAppDatabase(this IServiceCollection services, IConfiguration configuration)
{
var connectionString = configuration.GetConnectionString(ConnectionStringKey)
?? throw new InvalidOperationException($"Connection string '{ConnectionStringKey}' is not configured.");
services.AddDbContext<AppDbContext>(options => options.UseSqlite(connectionString));
return services;
}
Sources: packages/api/Common/Database/DatabaseModule.cs:13-19
Architectural Concerns
Recent code reviews have identified areas for improvement regarding the database pattern:
- Decoupling: Moving from a single large
AppDbContextto module-specific contexts to prevent tight coupling between unrelated features. - Abstraction: Encouraging the use of DTOs (Data Transfer Objects) instead of exposing full database entities through the API.
- Connection Strings: Ensuring connection strings are not hardcoded but managed via
appsettings.json.
Sources: REVIEW.md:4-44
Summary
The Database Pattern in this project leverages Entity Framework Core to provide a structured, schema-bound data access layer for a SQLite backend. While it provides robust features like automated migrations, sequential number counters, and comprehensive entity relationships, the project is currently evolving to further modularize these database concerns to improve scalability and maintainability.