T011: Create EF Core migration for Certificate tables

[Hemavathi15sg]

Phase 2: Foundational

Task: T011
Status: Not Started
Story: Foundational (blocking all user stories)
Depends On: T006-T010 (entities defined)

Description

Create EF Core database migration for all Certificate-related tables with indexes, constraints, and soft delete pattern.

Acceptance Criteria

• Run: dotnet ef migrations add AddCertificateEntities --context CourseRegistrationDbContext --project CourseRegistration.Infrastructure --output-dir Data/Migrations
• Migration file creates Certificate table with:
  • Primary key: CertificateId (GUID)
  • UNIQUE constraint on SerialNumber (filtered: WHERE IsDeleted = 0)
  • Foreign keys: StudentId, CourseId, RevokedByAdminId
  • All 17 columns with correct types and nullable flags
• Migration creates CertificateVerification table with:
  • Primary key: VerificationId
  • FK to Certificate.CertificateId
  • All 10 columns with correct types
• Migration creates CertificateAuditLog table with:
  • Primary key: AuditLogId
  • FK to Certificate.CertificateId
  • All 9 columns with correct types
• Migration creates 9 performance indexes (per data-model.md):
  • IDX: Certificate(StudentId, IsDeleted, IssueDateUtc DESC)
  • IDX: Certificate(Status)
  • IDX: Certificate(CourseId, IsDeleted)
  • IDX: CertificateVerification(CertificateId, VerificationTimestamp DESC)
  • IDX: CertificateVerification(VerifierIP, VerificationTimestamp DESC)
  • IDX: CertificateAuditLog(CertificateId, EventTimestamp DESC)
  • (additional as per data-model)
• Up() and Down() methods are complete and reversible
• Migration compiles without errors

Effort Estimate

3 hours

References

• data-model.md: Database Constraints and Indexes section
• plan.md: Technical Context (SQL Server)