reference/patterns/database-architecture.md

Database Architecture and Schema Isolation

Overview

The Dynaplex database architecture implements a sophisticated schema isolation system that prevents cross-schema pollution while enabling cross-component entity references. This document explains the complete architecture, from the foundational patterns to the automatic isolation mechanisms.

Architecture Principles

Single Database, Multiple Schemas

The Dynaplex architecture uses a single shared database instance with isolated schemas per component:

acsis (database)
├── prism (schema)       ← Foundation: passports, platform_types, journals
├── identity (schema)    ← Users, tenants, authentication
├── catalog (schema)     ← Items, item types, categories
├── spatial (schema)     ← Locations, buildings, regions
├── workflow (schema)    ← Process definitions, workflow runs
└── [other components]

Key Benefits:

Operational simplicity (single database to manage)
Cost efficiency (shared infrastructure)
Performance (connection pooling)
Clear service boundaries (schema isolation)

Reference: ADR-009: Database Schema per Service

Database Project Separation

Each component has a dedicated .Database project separate from .Abstractions:

engines/catalog/src/
├── Acsis.Dynaplex.Engines.Catalog.Abstractions/    # Interfaces, DTOs (no EF Core)
├── Acsis.Dynaplex.Engines.Catalog.Database/        # DbContext, entities, migrations
├── Acsis.Dynaplex.Engines.Catalog/                 # Service implementation
└── Acsis.Dynaplex.Engines.Catalog.DbMigrator/      # Migration runner

Why This Matters:

✅ Abstractions remain pure contracts (no database dependencies)
✅ Database entities hidden from API surface
✅ Clear separation of concerns
✅ Prevents circular dependencies

Reference: ADR-036: Database Project Separation

Component Database Structure

Standard Database Project Layout

Acsis.Dynaplex.Engines.Catalog.Database/
├── CatalogDb.cs                    # DbContext with schema configuration
├── CatalogDbContextFactory.cs      # Design-time factory for migrations
├── Item.cs                         # Entity classes
├── ItemType.cs
├── ItemCategory.cs
├── Configurations/                 # Optional: IEntityTypeConfiguration classes
│   ├── ItemConfiguration.cs
│   └── ItemTypeConfiguration.cs
└── Migrations/                     # EF Core migrations
    ├── 20251031120000_Initial.cs
    └── CatalogDbModelSnapshot.cs

DbContext Pattern

Every component's DbContext follows this pattern:

using Acsis.Dynaplex.Engines.Prism.Database;
using Acsis.Dynaplex;
using Microsoft.EntityFrameworkCore;

namespace Acsis.Dynaplex.Engines.Catalog.Database;

public partial class CatalogDb : DynaplexDbContext
{
    // 1. Define the schema constant (CRITICAL!)
    public const string SCHEMA = "catalog";

    // 2. Define DbSets for your entities
    public virtual DbSet<Item> Items { get; set; }
    public virtual DbSet<ItemType> ItemTypes { get; set; }

    // 3. Reference external entities you need
    public virtual DbSet<Passport> Passports { get; set; }
    public virtual DbSet<Tenant> Tenants { get; set; }

    // 4. Constructor
    public CatalogDb(DbContextOptions<CatalogDb> options) : base(options) { }

    // 5. OnModelCreating with standard pattern
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Apply configurations from this assembly
        modelBuilder.ApplyConfigurationsFromAssembly(Assembly.GetExecutingAssembly());

        // Set default schema
        modelBuilder.HasDefaultSchema(SCHEMA);

        // Call base (applies tenant filtering)
        base.OnModelCreating(modelBuilder);

        // Configure external references
        modelBuilder.AddExternalReference<Passport>(Passport.TABLE_NAME, PrismDb.SCHEMA, p => p.GlobalId);
        modelBuilder.AddExternalReference<Tenant>(Tenant.TABLE_NAME, IdentityDb.SCHEMA, t => t.Id);

        // Auto-configure Passport relationships
        modelBuilder.ConfigurePassportRelationships(SCHEMA);

        // Add indexes for tenant-scoped entities
        modelBuilder.Entity<Item>().HasIndex(i => i.TenantId);

        // CRITICAL: Schema isolation (must be last!)
        modelBuilder.ConfigureSchemaIsolation(SCHEMA);

        OnModelCreatingPartial(modelBuilder);
    }

    partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
}

Key Components Explained

1. DynaplexDbContext Base Class

Located in: strata/core/src/Acsis.Dynaplex/DynaplexDbContext.cs

Provides:

Multi-tenancy support via _tenantId field
Automatic tenant filtering on all entities with TenantId property
DbContext pooling compatibility
Standard base configuration

public abstract class DynaplexDbContext : DbContext
{
    protected Guid? _tenantId;

    public void SetTenantId(Guid? tenantId) => _tenantId = tenantId;

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Applies tenant filters automatically
        modelBuilder.ConfigureTenantFilters(this);
    }
}

2. SCHEMA Constant

CRITICAL: Every DbContext must define a public const string SCHEMA field:

public const string SCHEMA = "catalog";

This constant:

Defines the schema name for all tables in this component
Is used by ComponentSchemaRegistry for assembly-to-schema mapping
Enables automatic schema isolation
Must match the schema naming convention (lowercase, singular)

Schema Naming Convention:

Use lowercase
Use singular form (e.g., "catalog" not "catalogs")
Match component name (e.g., "spatial", "identity", "workflow")

Schema Isolation Mechanism

The Problem

When a DbContext references entities from another component, EF Core attempts to create those tables in the current schema during migrations:

// In CatalogDb (schema = "catalog")
public DbSet<Passport> Passports { get; set; }  // From Prism (schema = "prism")

// Without schema isolation:
// Migration tries to: CREATE TABLE catalog.passports (...)
// Result: Duplicate table, FK constraint failures, schema pollution

The Solution: Automatic Schema Isolation

The Dynaplex architecture uses a three-part solution:

1. ComponentSchemaRegistry

Located in: strata/core/src/Acsis.Dynaplex/ComponentSchemaRegistry.cs

Purpose: Automatically discovers which schema each assembly "owns"

How it works:

Scans all types in an assembly
Finds any DbContext class
Reads the public static SCHEMA field from that DbContext
Caches the result: Assembly → Schema

internal static class ComponentSchemaRegistry
{
    private static readonly ConcurrentDictionary<Assembly, string?> _schemaCache = new();

    internal static string? GetSchema(Assembly assembly)
    {
        return _schemaCache.GetOrAdd(assembly, DiscoverSchema);
    }

    private static string? DiscoverSchema(Assembly assembly)
    {
        foreach (var type in assembly.GetTypes())
        {
            if (!typeof(DbContext).IsAssignableFrom(type))
                continue;

            var schemaField = type.GetField("SCHEMA", BindingFlags.Public | BindingFlags.Static);
            var schema = schemaField?.GetValue(null) as string;

            if (!string.IsNullOrWhiteSpace(schema))
                return schema;
        }
        return null;
    }
}

Example:

var passportAssembly = typeof(Passport).Assembly;  // Acsis.Dynaplex.Engines.Prism.Database
var schema = ComponentSchemaRegistry.GetSchema(passportAssembly);
// Returns: "prism" (discovered from PrismDb.SCHEMA constant)

2. ConfigureSchemaIsolation Extension

Located in: strata/core/src/Acsis.Dynaplex/DatabaseExtensions.cs

Purpose: Automatically exclude cross-schema entities from migrations

How it works:

Iterates through all entities in the model
For each entity, determines its "owning" schema via ComponentSchemaRegistry
If entity belongs to different schema than current DbContext, marks it as excluded from migrations
Optionally moves the entity to its correct schema if not already set

public static void ConfigureSchemaIsolation(this ModelBuilder modelBuilder, string contextSchema)
{
    foreach (var entityType in modelBuilder.Model.GetEntityTypes())
    {
        var clrType = entityType.ClrType;
        string? entitySchema = entityType.GetSchema();

        if (clrType != null)
        {
            // Discover the entity's owning schema from its assembly
            var owningSchema = ComponentSchemaRegistry.GetSchema(clrType.Assembly);

            // If entity belongs to different component, move it to that schema
            if (!string.IsNullOrEmpty(owningSchema) && owningSchema != contextSchema)
            {
                entitySchema = owningSchema;
                entityType.SetSchema(owningSchema);
            }
        }

        // If entity belongs to different schema, exclude from migrations
        if (!string.IsNullOrEmpty(entitySchema) && entitySchema != contextSchema)
        {
            entityType.SetIsTableExcludedFromMigrations(true);
        }
    }
}

Key Points:

Call this LAST in OnModelCreating (after all entity configurations)
Entities are still in the model (for relationships and FK constraints)
EF Core generates correct FK constraints pointing to the proper schema.table
Migrations just won't attempt to create the excluded tables

3. Usage Pattern

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.HasDefaultSchema(SCHEMA);
    base.OnModelCreating(modelBuilder);

    // ... your entity configurations ...

    // MUST be last!
    modelBuilder.ConfigureSchemaIsolation(SCHEMA);
}

How Schema Isolation Prevents Pollution

Before ConfigureSchemaIsolation:

-- CatalogDb migration tries to create:
CREATE TABLE catalog.passports (...);  -- ❌ WRONG SCHEMA!
CREATE TABLE catalog.items (...);      -- ✅ Correct

After ConfigureSchemaIsolation:

-- CatalogDb migration creates:
CREATE TABLE catalog.items (...);  -- ✅ Correct

-- FK constraint references existing table:
ALTER TABLE catalog.items
    ADD CONSTRAINT fk__catalog__items__prism__passports__id
    FOREIGN KEY (id) REFERENCES prism.passports(global_id);  -- ✅ Correct schema!

-- No attempt to create catalog.passports ✅

Prism Foundation

Role of Prism

Prism is the foundational infrastructure component providing:

Passports - Global unique ID registry for all entities
Platform Types - Type registry mapping PTIDs to entity types
Identifiers - Alternative identifier system (barcodes, serial numbers, etc.)
Attribute System - Dynamic attributes for entities
Journal Tables - Centralized audit log for all components

All journal tables live in the Prism schema regardless of which component's data they track.

Reference: ADR-035: Prism Journal Tables

Passport Pattern

The Passport pattern provides global unique identifiers across all components:

// Prism.Database/Passport.cs
public class Passport
{
    public const string TABLE_NAME = "passports";

    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid GlobalId { get; set; }  // UUIDv7

    public short PlatformTypeId { get; set; }  // Links to platform_types
}

Every entity that needs a global ID:

Has Id property of type Guid
Has PlatformTypeId property of type short
Has a public const short PTID constant (see PTIDS in CoreData)
Configures FK relationship to prism.passports

Automatic Passport Configuration

Use ConfigurePassportRelationships to auto-configure all entities:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.HasDefaultSchema(SCHEMA);
    base.OnModelCreating(modelBuilder);

    // Configure external Passport reference
    modelBuilder.AddExternalReference<Passport>(Passport.TABLE_NAME, PrismDb.SCHEMA, p => p.GlobalId);

    // Automatically configure FK relationships for all entities with PlatformTypeId
    modelBuilder.ConfigurePassportRelationships(SCHEMA);

    // Schema isolation (must be last)
    modelBuilder.ConfigureSchemaIsolation(SCHEMA);
}

How ConfigurePassportRelationships works:

Finds all entities in the current schema with PlatformTypeId property
Automatically creates FK relationship: entity.Id → prism.passports.GlobalId
Generates constraint name following convention: fk__{schema}__{table}__prism__passports__id
Only configures entities in the current schema (skips external references)

Manual Alternative (if needed):

modelBuilder.Entity<Item>(b => {
    b.HasOne<Passport>()
        .WithOne()
        .HasForeignKey<Item>(x => x.Id)
        .OnDelete(DeleteBehavior.Restrict)
        .HasConstraintName($"fk__{SCHEMA}__{Item.TABLE_NAME}__{PrismDb.SCHEMA}__{Passport.TABLE_NAME}__id");
});

Cross-Component References

Referencing External Entities

When a component needs to reference entities from another component:

Pattern 1: AddExternalReference (Recommended)

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

    // Reference Passport from Prism
    modelBuilder.AddExternalReference<Passport>(
        Passport.TABLE_NAME,    // "passports"
        PrismDb.SCHEMA,         // "prism"
        p => p.GlobalId         // Primary key
    );

    // Reference Tenant from Identity
    modelBuilder.AddExternalReference<Tenant>(
        Tenant.TABLE_NAME,      // "tenants"
        IdentityDb.SCHEMA,      // "identity"
        t => t.Id               // Primary key
    );

    // ... rest of configuration ...
    modelBuilder.ConfigureSchemaIsolation(SCHEMA);
}

What AddExternalReference does:

Configures table name and schema
Configures primary key
Auto-maps key property to snake_case column name
Returns EntityTypeBuilder for additional configuration if needed

Pattern 2: Manual Configuration (Legacy)

modelBuilder.Entity<Passport>(b => {
    b.ToTable(Passport.TABLE_NAME, PrismDb.SCHEMA, t => t.ExcludeFromMigrations());
    b.HasKey(x => x.GlobalId);
    b.Property(x => x.GlobalId).HasColumnName("global_id");
});

Note: Manual configuration requires explicit ExcludeFromMigrations() call. With AddExternalReference, the subsequent ConfigureSchemaIsolation call handles exclusion automatically.

Project References

Database projects can reference other Database projects:

<!-- Acsis.Dynaplex.Engines.Catalog.Database.csproj -->
<ItemGroup>
  <!-- Required: Prism for Passport pattern -->
  <ProjectReference Include="$(PrismDatabase)" />

  <!-- Required: CoreData for primitives (PTIDS, etc.) -->
  <ProjectReference Include="$(CoreDataAbstractions)" />

  <!-- Optional: Other components if needed -->
  <ProjectReference Include="$(IdentityDatabase)" />
  <ProjectReference Include="$(SpatialDatabase)" />
</ItemGroup>

Guidelines:

Always reference Prism.Database (for Passport)
Always reference CoreData.Abstractions (for PTIDS and primitives)
Only reference other .Database projects if you need to query/relate to their entities
Avoid circular dependencies (use abstractions if needed for DTOs)

Migration Patterns

Creating Migrations

ALWAYS use the dplx CLI or database-migration-expert agent:

# Recommended: Use dplx CLI
dplx db migrations add -c catalog -n AddItemCategories

# Or use Claude slash command
/generate-migration

NEVER run dotnet ef migrations directly - the CLI ensures:

Correct DbContext is used
Proper project references
Migration naming conventions
Schema validation

Migration Ordering

Aspire AppHost ensures migrations run in dependency order:

// Prism MUST run first (owns passports table)
var prismMigrator = builder.AddProject<Prism_DbMigrator>("prism-migrator")
    .WaitFor(database);

// Other components depend on Prism
var catalogMigrator = builder.AddProject<Catalog_DbMigrator>("catalog-migrator")
    .WaitFor(prismMigrator);  // Blocks until Prism ready

var identityMigrator = builder.AddProject<Identity_DbMigrator>("identity-migrator")
    .WaitFor(prismMigrator);

Critical: Prism must migrate before any component that uses Passport pattern.

Migration Structure

Each migration should:

Only create/modify tables in its own schema
Reference external tables with proper schema prefix
Use lowercase_underscore naming for tables and columns
Follow FK constraint naming convention

FK Constraint Naming:

fk__{source_schema}__{source_table}__{target_schema}__{target_table}__{column}

Example:

ALTER TABLE catalog.items
    ADD CONSTRAINT fk__catalog__items__prism__passports__id
    FOREIGN KEY (id) REFERENCES prism.passports(global_id);

Best Practices

1. Always Call ConfigureSchemaIsolation

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // ... configurations ...

    // MUST be last line before OnModelCreatingPartial
    modelBuilder.ConfigureSchemaIsolation(SCHEMA);

    OnModelCreatingPartial(modelBuilder);
}

2. Use AddExternalReference for Cross-Component Entities

// ✅ Good: Clean, automatic exclusion
modelBuilder.AddExternalReference<Passport>(Passport.TABLE_NAME, PrismDb.SCHEMA, p => p.GlobalId);

// ❌ Avoid: Manual configuration (verbose, error-prone)
modelBuilder.Entity<Passport>(b => {
    b.ToTable(Passport.TABLE_NAME, PrismDb.SCHEMA, t => t.ExcludeFromMigrations());
    b.HasKey(x => x.GlobalId);
    // ...
});

3. Use ConfigurePassportRelationships for Auto-Configuration

// ✅ Good: Automatic FK configuration for all Passport entities
modelBuilder.ConfigurePassportRelationships(SCHEMA);

// ❌ Avoid: Manual configuration for each entity (unless you need custom behavior)
modelBuilder.Entity<Item>(b => {
    b.HasOne<Passport>()...
});

4. Define SCHEMA Constant in Every DbContext

// ✅ REQUIRED
public const string SCHEMA = "catalog";

// ❌ WRONG: Using string literal
modelBuilder.HasDefaultSchema("catalog");  // No registry entry!

5. Reference Database Projects, Not Abstractions

<!-- ✅ Correct: Reference Database project for entities -->
<ProjectReference Include="$(PrismDatabase)" />

<!-- ❌ Wrong: Abstractions don't have entities anymore -->
<ProjectReference Include="$(PrismAbstractions)" />

6. Add TenantId Indexes

// For multi-tenant entities, always add index on TenantId
modelBuilder.Entity<Item>().HasIndex(i => i.TenantId);
modelBuilder.Entity<ItemType>().HasIndex(it => it.TenantId);

7. Use Standard OnModelCreating Order

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // 1. Apply configurations from assembly (optional)
    modelBuilder.ApplyConfigurationsFromAssembly(Assembly.GetExecutingAssembly());

    // 2. Set default schema
    modelBuilder.HasDefaultSchema(SCHEMA);

    // 3. Call base (applies tenant filtering)
    base.OnModelCreating(modelBuilder);

    // 4. Configure external references
    modelBuilder.AddExternalReference<Passport>(...);
    modelBuilder.AddExternalReference<Tenant>(...);

    // 5. Manual entity configurations (if needed)
    modelBuilder.Entity<Item>()...

    // 6. Auto-configure Passport relationships
    modelBuilder.ConfigurePassportRelationships(SCHEMA);

    // 7. Add indexes
    modelBuilder.Entity<Item>().HasIndex(i => i.TenantId);

    // 8. Schema isolation (MUST be last!)
    modelBuilder.ConfigureSchemaIsolation(SCHEMA);

    // 9. Partial method hook
    OnModelCreatingPartial(modelBuilder);
}

Common Pitfalls

1. Forgetting ConfigureSchemaIsolation

Symptom: Migrations try to create tables from other schemas

Solution:

// Add this as last line in OnModelCreating
modelBuilder.ConfigureSchemaIsolation(SCHEMA);

2. No SCHEMA Constant in DbContext

Symptom: ComponentSchemaRegistry can't discover schema, isolation doesn't work

Solution:

public const string SCHEMA = "catalog";  // REQUIRED!

3. Calling ConfigureSchemaIsolation Too Early

Symptom: External entities not properly configured yet

Solution: Always call ConfigureSchemaIsolation last in OnModelCreating

4. Referencing Abstractions Instead of Database Projects

Symptom: Build errors, missing entity types

Solution:

<!-- ✅ Correct -->
<ProjectReference Include="$(PrismDatabase)" />

<!-- ❌ Wrong -->
<ProjectReference Include="$(PrismAbstractions)" />

5. Manual Passport Configuration Without ExcludeFromMigrations

Symptom: Migrations try to create catalog.passports table

Solution: Use AddExternalReference instead of manual configuration, or add explicit exclusion:

modelBuilder.Entity<Passport>(b => {
    b.ToTable(Passport.TABLE_NAME, PrismDb.SCHEMA, t => t.ExcludeFromMigrations());
    // ...
});

6. Circular Database Project References

Symptom: Build fails with circular dependency error

Solution:

Review project references - Database projects should form a DAG (Directed Acyclic Graph)
Prism is at the root (no dependencies)
Other components depend on Prism, not each other (unless truly needed)
Use Abstractions for DTOs if you need shared types without database coupling

Troubleshooting

Problem: Migration tries to create tables from other schemas

Check:

Is ConfigureSchemaIsolation(SCHEMA) called in OnModelCreating?
Is it called last (before OnModelCreatingPartial)?
Does your DbContext have public const string SCHEMA field?

Fix:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.HasDefaultSchema(SCHEMA);
    base.OnModelCreating(modelBuilder);
    // ... configurations ...
    modelBuilder.ConfigureSchemaIsolation(SCHEMA);  // ← Add this!
}

Problem: FK constraint fails during migration

Check:

Are you trying to reference an entity that doesn't exist yet?
Is Prism migrator running before your component's migrator?
Is the external entity properly configured with correct schema?

Fix:

// Ensure external references are configured
modelBuilder.AddExternalReference<Passport>(Passport.TABLE_NAME, PrismDb.SCHEMA, p => p.GlobalId);

// Check Aspire AppHost migration ordering
var catalogMigrator = builder.AddProject<Catalog_DbMigrator>("catalog-migrator")
    .WaitFor(prismMigrator);  // ← Ensure dependency

Problem: Entity not found in model

Check:

Is the entity's assembly referenced in your Database project?
Is the entity class public?
Is there a DbSet<T> for the entity in the DbContext?

Fix:

// Add DbSet
public virtual DbSet<Passport> Passports { get; set; }

// Or configure without DbSet
modelBuilder.AddExternalReference<Passport>(Passport.TABLE_NAME, PrismDb.SCHEMA, p => p.GlobalId);

Problem: Passport FK constraint "missing primary key" error

Check:

Is Passport entity configured with primary key?
Is Passport excluded from migrations?
Is AddExternalReference called before ConfigurePassportRelationships?

Fix:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.HasDefaultSchema(SCHEMA);
    base.OnModelCreating(modelBuilder);

    // 1. Configure Passport FIRST
    modelBuilder.AddExternalReference<Passport>(Passport.TABLE_NAME, PrismDb.SCHEMA, p => p.GlobalId);

    // 2. Then configure relationships
    modelBuilder.ConfigurePassportRelationships(SCHEMA);

    // 3. Then schema isolation
    modelBuilder.ConfigureSchemaIsolation(SCHEMA);
}

Summary

The Dynaplex database architecture provides:

Schema Isolation: Automatic prevention of cross-schema table creation via ConfigureSchemaIsolation
Assembly-Based Discovery: ComponentSchemaRegistry maps assemblies to schemas using DbContext.SCHEMA constants
Clean Separation: .Database projects separate from .Abstractions
Prism Foundation: Centralized Passport, Platform Types, and Journal infrastructure
Automatic Configuration: Helper methods reduce boilerplate and prevent errors
Type Safety: Compile-time errors prevent schema pollution
Multi-Tenancy: Automatic tenant filtering via DynaplexDbContext

The key insight: By combining assembly-based schema discovery with automatic migration exclusion, the system prevents schema pollution while maintaining clean FK relationships across component boundaries.

Code References

Key Classes

DynaplexDbContext: strata/core/src/Acsis.Dynaplex/DynaplexDbContext.cs
DatabaseExtensions: strata/core/src/Acsis.Dynaplex/DatabaseExtensions.cs
ComponentSchemaRegistry: strata/core/src/Acsis.Dynaplex/ComponentSchemaRegistry.cs
PrismDb: engines/prism/src/Acsis.Dynaplex.Engines.Prism.Database/PrismDb.cs
Passport: engines/prism/src/Acsis.Dynaplex.Engines.Prism.Database/Passport.cs

Example DbContexts

Prism (foundation): engines/prism/src/Acsis.Dynaplex.Engines.Prism.Database/PrismDb.cs
Identity (manual config): engines/identity/src/Acsis.Dynaplex.Engines.Identity.Database/IdentityDb.cs
Catalog (clean pattern): engines/catalog/src/Acsis.Dynaplex.Engines.Catalog.Database/CatalogDb.cs
Spatial (complex): engines/spatial/src/Acsis.Dynaplex.Engines.Spatial.Database/SpatialDb.cs

Documentation

Database Architecture and Schema Isolation

Overview

Table of Contents

Architecture Principles

Single Database, Multiple Schemas

Database Project Separation

Component Database Structure

Standard Database Project Layout

DbContext Pattern

Key Components Explained

1. DynaplexDbContext Base Class

2. SCHEMA Constant

Schema Isolation Mechanism

The Problem

The Solution: Automatic Schema Isolation

1. ComponentSchemaRegistry

2. ConfigureSchemaIsolation Extension

3. Usage Pattern

How Schema Isolation Prevents Pollution

Prism Foundation

Role of Prism

Passport Pattern

Automatic Passport Configuration

Cross-Component References

Referencing External Entities

Pattern 1: AddExternalReference (Recommended)

Pattern 2: Manual Configuration (Legacy)

Project References

Migration Patterns

Creating Migrations

Migration Ordering

Migration Structure

Best Practices

1. Always Call ConfigureSchemaIsolation

2. Use AddExternalReference for Cross-Component Entities

3. Use ConfigurePassportRelationships for Auto-Configuration

4. Define SCHEMA Constant in Every DbContext

5. Reference Database Projects, Not Abstractions

6. Add TenantId Indexes

7. Use Standard OnModelCreating Order

Common Pitfalls

1. Forgetting ConfigureSchemaIsolation

2. No SCHEMA Constant in DbContext

3. Calling ConfigureSchemaIsolation Too Early

4. Referencing Abstractions Instead of Database Projects

5. Manual Passport Configuration Without ExcludeFromMigrations

6. Circular Database Project References

Troubleshooting

Problem: Migration tries to create tables from other schemas

Problem: FK constraint fails during migration

Problem: Entity not found in model

Problem: Passport FK constraint "missing primary key" error

Summary

Related Documentation

Code References

Key Classes

Example DbContexts