Databases

Purpose

Guide database technology selection, schema design, query optimization, and migration patterns -- with EasyPlatform-specific context for service-to-database mapping.

When to Use

• •Choosing which database technology for a new feature or service
• •Designing a schema or data model for a new entity
• •Optimizing slow queries or adding indexes
• •Writing or reviewing database migrations
• •Troubleshooting connection or performance issues
• •Understanding EasyPlatform's database topology

When NOT to Use

• •Writing C# repository code -- use easyplatform-backend skill (repositories follow platform patterns)
• •Cross-service data access design -- use arch-cross-service-integration skill (must use message bus, never direct DB access)
• •General backend CQRS implementation -- use easyplatform-backend skill
• •Frontend data fetching -- use frontend-angular-api-service skill

Prerequisites

• •Understand which EasyPlatform service you are working in
• •Read docs/claude/architecture.md for service boundaries

EasyPlatform Database Topology

CRITICAL: Each service owns its database. Never access another service's database directly -- use the message bus.

Workflow

Step 1: Identify Service and Database

IF using MongoDB persistence module THEN MongoDB patterns apply. IF using EF Core with SQL Server THEN SQL Server / EF Core patterns apply. IF using EF Core with PostgreSQL THEN PostgreSQL / EF Core patterns apply. IF caching or session data THEN Redis patterns apply.

Step 2: Select Task

Step 3A: Schema Design

1. •Define entity class following platform patterns (see CLAUDE.md Entity section)
2. •For MongoDB: design document structure, decide embed vs. reference
3. •For SQL Server/PostgreSQL: design tables with proper normalization, foreign keys
4. •For Redis: design key naming convention ({service}:{entity}:{id})
5. •Add navigation properties using [PlatformNavigationProperty] where needed

Step 3B: Query Optimization

1. •Identify the slow query (check logs or EXPLAIN ANALYZE / MongoDB .explain())
2. •Check if proper indexes exist for the query's filter and sort columns
3. •For MongoDB: check if aggregation pipeline can replace multiple queries
4. •For SQL Server/PostgreSQL: check if CTEs or window functions simplify logic
5. •Verify N+1 queries are prevented -- use loadRelatedEntities parameter in repository calls

Step 3C: Migration

1. •For EF Core (SQL Server/PostgreSQL):
   bash

   dotnet ef migrations add MigrationName --project [Service].Persistence
   dotnet ef database update
   

2. •For MongoDB (platform migration):
   • •Create PlatformMongoMigrationExecutor<ServiceDbContext> class
   • •Name format: YYYYMMDD_Description
   • •Use paged processing for large datasets
3. •For data seeding: use PlatformDataMigrationExecutor<DbContext>

Step 3D: Index Design

1. •MongoDB: Use compound indexes matching query patterns
   javascript

   db.collection.createIndex({ companyId: 1, status: 1, createdDate: -1 })
   

2. •SQL Server/PostgreSQL: Index foreign keys and frequently filtered columns
   sql

   CREATE INDEX IX_Employee_CompanyId_Status ON Employees(CompanyId, Status) INCLUDE (Name);
   

3. •Redis: No traditional indexes -- design keys for direct lookup patterns

Step 4: Verification

• •Run the query/migration in a test environment
• •Verify performance improvement with explain plans
• •Ensure migration is idempotent and backward-compatible
• •Check that repository extensions use static expressions (see CLAUDE.md patterns)

Output Format

## Database: [Task Summary]

### Context
- Service: [Service name]
- Database: [MongoDB | SQL Server | PostgreSQL | Redis]
- Task: [schema | query | migration | index]

### Recommendation
[Specific technical recommendation]

### Implementation
[Code or SQL/MongoDB commands]

### Verification
[How to confirm correctness]

Detailed References

Load for database-specific deep dives:

Related Skills

• •easyplatform-backend -- for C# repository, entity, and migration execution patterns
• •database-optimization -- for advanced performance tuning and N+1 prevention
• •arch-cross-service-integration -- for cross-service data access via message bus

IMPORTANT Task Planning Notes (MUST FOLLOW)

• •Always plan and break work into many small todo tasks
• •Always add a final review todo task to verify work quality and identify fixes/enhancements