adrs/036-database-project-separation.md

ADR 036: Database Project Separation from Abstractions

Status: Accepted
Date: 2025-10-28
Updated: 2026-01-08 (db-manager consolidation)
Deciders: Architecture Team
Related: ADR 034 (GUID Primary Keys), ADR 035 (Prism Journal Tables)

Note (2026-01-08): This ADR has been updated to reflect the evolution from per-component DbMigrator projects to a centralized db-manager component. The core decision about separating database code into .Database projects remains unchanged.

Context

Previously, Dynaplex components placed Entity Framework Core DbContext classes and entity models in the .Abstractions projects:

components/
  identity/
    src/
      Acsis.Dynaplex.Engines.Identity.Abstractions/
        Database/
          IdentityDb.cs          # DbContext
          User.cs                # Entity
          Group.cs               # Entity
        Interfaces/
        Models/

This structure created several issues:

Circular Dependencies: Abstractions projects needed EF Core packages for DbContext, but EF Core is an implementation detail
API Surface Pollution: Database entities exposed in abstractions forced unnecessary coupling
Migration Complexity: Running migrations required referencing Abstractions, mixing contracts with implementation
Passport Pattern Issues: External entity references from other components' DbContexts caused "entity requires primary key" errors during migrations
Conceptual Confusion: "Abstractions" should contain interfaces and DTOs, not concrete database implementations

Specific Pain Points

The Passport pattern migration (ADR 034) exposed critical issues:

// In BbuDb (BBU component's DbContext)
public virtual DbSet<Location> Locations { get; set; }  // From Spatial.Abstractions

// When running migrations:
// ERROR: The entity type 'Location' requires a primary key to be defined

The problem: EF Core couldn't determine the primary key for Location because it was configured in a different DbContext (SpatialDb) in a different assembly. Helper methods like ConfigureAllPassportEntities() failed because they tried to configure entities that didn't belong to the current schema.

Decision

We will separate database concerns into dedicated .Database projects for each component:

Project Structure

Current structure:

components/
  identity/
    src/
      Acsis.Dynaplex.Engines.Identity.Abstractions/     # Interfaces, DTOs only
        Interfaces/
          IIdentityService.cs
        Models/
          UserDto.cs
      Acsis.Dynaplex.Engines.Identity.Database/         # Database project
        IdentityDb.cs                             # DbContext
        User.cs                                   # Entity
        Group.cs                                  # Entity
        Migrations/                               # EF Core migrations
      Acsis.Dynaplex.Engines.Identity/                  # Service implementation

Note: Database migrations are run by the centralized db-manager component at engines/db-manager/, not per-component DbMigrator projects. This change (2026-01) reduced deployment complexity from 13+ containers to 1 for migrations.

Key Principles

Abstractions are Pure Contracts:
- Only interfaces, DTOs, and request/response models
- No EF Core dependencies
- No database entities
- No DbContext classes
Database Projects Own Entities:
- Contains DbContext
- Contains entity classes
- Contains EF Core configurations
- Contains migrations folder
- References Prism.Database for Passport support
Explicit External References:
- External entities referenced using ExcludeFromMigrations()
- FK relationships configured explicitly per entity
- No automatic entity discovery across schemas
Migration Commands Updated:
- dplx CLI now references .Database projects instead of .Abstractions
- Migration paths: components/{name}/src/Acsis.Dynaplex.Engines.{Name}.Database

Passport Pattern Integration

Database projects must explicitly configure Passport relationships:

// In IdentityDb.cs
protected override void OnModelCreating(ModelBuilder modelBuilder) {
    modelBuilder.HasDefaultSchema(SCHEMA);
    base.OnModelCreating(modelBuilder);

    // Configure Passport table as external reference
    modelBuilder.Entity<Passport>(b => {
        b.ToTable(Passport.TABLE_NAME, PrismDb.SCHEMA_NAME, t => t.ExcludeFromMigrations());
        b.HasKey(x => x.GlobalId);
        b.Property(x => x.GlobalId).HasColumnName("global_id");
    });

    // Configure FK for each entity with Passport
    modelBuilder.Entity<User>(b => {
        b.HasOne<Passport>()
            .WithOne()
            .HasForeignKey<User>(x => x.Id)
            .OnDelete(DeleteBehavior.Restrict)
            .HasConstraintName($"fk__{SCHEMA}__{User.TABLE_NAME}__{PrismDb.SCHEMA_NAME}__{Passport.TABLE_NAME}__id");
    });
}

Why explicit configuration?

Prevents EF Core from trying to create Passport tables in non-Prism schemas
Makes foreign key relationships clear and maintainable
Avoids "missing primary key" errors during migrations
Each component controls its own entity configuration

Project References

Database projects reference:

Acsis.Dynaplex.Engines.CoreData.Abstractions - for primitives like PTIDS
Acsis.Dynaplex.Engines.Prism.Database - for Passport and PrismDb
Microsoft.EntityFrameworkCore - for DbContext and configurations
Any other component's .Database projects if needed (e.g., Spatial for Location)

Abstractions projects reference:

No EF Core packages
No other Database projects
Only other Abstractions projects for shared contracts

Consequences

Positive

✅ Clean Separation: Abstractions truly abstract, database is implementation
✅ No Circular Dependencies: Clear dependency graph
✅ Proper Encapsulation: Database entities hidden from API contracts
✅ Migration Clarity: Migrations reference the correct project
✅ Passport Compatibility: External entity references work correctly
✅ Easier Testing: Can test abstractions without database
✅ Schema Isolation: Each component fully owns its database schema
✅ Type Safety: Compile-time errors prevent cross-schema issues

Negative

⚠️ More Projects: Each component now has multiple projects (Abstractions, Database, Service, ApiClient)
⚠️ Migration Effort: Existing components need refactoring
⚠️ Template Updates: Project templates need new Database template
⚠️ Learning Curve: Developers must understand new structure
⚠️ Cross-Component Queries: More complex when entities are in separate projects
⚠️ db-manager Registration: New components must be registered in db-manager

Migration Strategy

Components refactored in this order:

✅ Prism (owns Passport table, no changes needed)
✅ Catalog (pilot implementation)
✅ Identity (User, Group - 2 entities)
✅ Spatial (10 entities - largest component)
Pending: BBU, Workflow, Core-Data, etc.

For each component:

Create Acsis.Dynaplex.Engines.{Name}.Database project
Move entities and DbContext from Abstractions
Add Prism.Database reference
Configure Passport relationships explicitly
Update dplx CLI references (Config.fs, Commands.fs)
Update project templates
Test migration generation
Update documentation

Implementation Details

CLI Tool Updates

strata/core/src/Acsis.Dynaplex/:

Config.fs: getMigrationPaths now returns databasePath instead of abstractionsPath
Commands.fs: All migration commands use .Database project paths
Templates.fs: Added Database to TemplateType enum and createDatabase function

Template Files

New template: strata/project-templates/Database/

.template.config/template.json - template definition
DPLX_DBCONTEXT_NAME.cs - DbContext with Passport pattern
Entities/SampleEntity.cs - sample entity with PTID
DPLX_COMPONENT_NAMESPACE.Database.csproj - project file

dplx component create

When creating a new component with dplx new, the Database project is created automatically unless --no-db flag is used:

dplx new my-component

Creates:

Acsis.Dynaplex.Engines.MyComponent.Abstractions
Acsis.Dynaplex.Engines.MyComponent.Database
Acsis.Dynaplex.Engines.MyComponent (service)
Acsis.Dynaplex.Engines.MyComponent.ApiClient

After creation with database support:

Add a ProjectReference to the Database project in engines/db-manager/src/Acsis.Dynaplex.Engines.DbManager/
Register the DbContext in db-manager's Program.cs

Automatic Migration Generation (Optional)

To further reduce manual work, an automatic migration generation system is available but disabled by default:

How It Works

Build-Time Detection: After each successful build, checks for pending model changes
Auto-Creation: If changes detected, generates migration with format: Auto_yyyyMMddHHmmss
Developer Commits: Developer commits auto-generated migration with their code changes

Enabling Auto-Migration

Uncomment this line in Directory.Build.props:

<!-- Currently commented out - uncomment to enable -->
<Import Project="$(StrataDir)resources/build/AutoMigration.targets" Condition="'$(IsDatabase)' == 'True'" />

Configuration

Disable locally: Set environment variable DISABLE_AUTO_MIGRATION=true
CI behavior: Automatically disabled in CI (uses dplx migration check instead)

Project-specific: Add to .csproj file:

<PropertyGroup>
  <DISABLE_AUTO_MIGRATION>true</DISABLE_AUTO_MIGRATION>
</PropertyGroup>

CI/CD Validation

Use dplx migration check in CI pipelines to ensure migrations are up-to-date:

# GitHub Actions example
- name: Check migrations
  run: dplx migration check

This command:

✅ Exits 0 if all migrations are up-to-date
❌ Exits 1 if pending model changes detected
Outputs which components need migrations

Trade-offs

Pros:

✅ Zero manual migration steps
✅ Impossible to forget migrations
✅ Consistent workflow across team

Cons:

⚠️ Migration names lack semantic meaning (timestamp only)
⚠️ Requires discipline to commit migrations immediately
⚠️ May create extra migrations if devs build frequently

Recommendation: Start with manual migrations using dplx migration add. Enable auto-generation only if the team frequently forgets to create migrations.

References

Notes

This decision aligns with microservices principles: each service owns its data schema
Database projects can still be shared via project references when needed (e.g., Catalog referencing Spatial.Database)
The separation enables future extraction of components into separate repositories
Template updates ensure all new components follow this pattern automatically
This ADR supersedes any previous conventions about placing database code in Abstractions

Documentation