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 ``

1. For MongoDB (platform migration):

- Create PlatformMongoMigrationExecutor<ServiceDbContext> class - Name format: YYYYMMDD_Description - Use paged processing for large datasets

1. 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 }) ``

1. SQL Server/PostgreSQL: Index foreign keys and frequently filtered columns

``sql CREATE INDEX IXEmployeeCompanyId_Status ON Employees(CompanyId, Status) INCLUDE (Name); ``

1. 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