πŸ“š Documentation: 20230822130112_AddAuditTable.cs


πŸ—‚ Table of Contents

  1. Overview & Purpose
  2. Dependencies & Technologies
  3. Migration Class Breakdown
  4. Schema Changes
  5. Using the Migration
  6. UML Diagram
  7. Sample Code Snippets
  8. Conclusion

1. Overview & Purpose

πŸ” What is this file?

πŸš€ Why do we need it?

  • Audit logging: Track user activitiesβ€”login status, page accessed, IP, etc.
  • Accountability: Store historical data for compliance, debugging, and analytics.

2. Dependencies & Technologies

Technology / Package Version Purpose
Microsoft.EntityFrameworkCore x.y.z ORM for migrations & data access
Microsoft.EntityFrameworkCore.Tools x.y.z CLI tools for Add-Migration & Update-Database
.NET 6 / .NET 7 >=6.0 Runtime for the EF Core migration project

3. Migration Class Breakdown

public partial class AddAuditTable : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder) { /* … */ }
    protected override void Down(MigrationBuilder migrationBuilder) { /* … */ }
}
  • Up: Defines operations to apply this migration (i.e., create the table).
  • Down: Reverts those operations (i.e., drops the table) if you roll back.

4. Schema Changes

4.1 AuditDetail Table

  • Schema: irby
  • Table: AuditDetail

4.2 Columns & Data Types

Column Name CLR Type SQL Type Nullable Description
AuditDetailId Guid uniqueidentifier No PK, unique ID for each audit record
Area string nvarchar(max) Yes MVC Area name
ControllerName string nvarchar(max) Yes Name of the controller handling the request
ActionName string nvarchar(max) Yes Action method invoked
Namespace string nvarchar(max) Yes Fully qualified namespace
UserId Guid uniqueidentifier No ID of the authenticated user
RoleId Guid? uniqueidentifier Yes FK to Role
EmployeeId Guid? uniqueidentifier Yes FK to Employee
LangId string nvarchar(max) Yes Culture / Language ID
IpAddress string nvarchar(max) Yes Client IP address
IsFirstLogin string nvarchar(max) Yes Flag for first-time login
ActivityDate DateTime datetime2 No Timestamp of the activity
LoginStatus string nvarchar(max) Yes e.g., Success / Failure
PageAccessed string nvarchar(max) Yes URI or page name accessed
SessionId string nvarchar(max) Yes Session identifier
UrlReferrer string nvarchar(max) Yes Referring URL
DocumentImageId Guid? uniqueidentifier Yes FK or link to a document image

4.3 Constraints & Indexes

  • Primary Key
    CONSTRAINT PK_AuditDetail PRIMARY KEY (AuditDetailId)
    
  • Foreign Keys
    • EmployeeId β†’ irby.Employee(EmployeeId)
    • RoleId β†’ irby.Role(RoleId)
  • Indexes
    • IX_AuditDetail_EmployeeId
    • IX_AuditDetail_RoleId

5. Using the Migration

5.1 Generating & Applying

  1. Create migration (if you were writing it manually):
    dotnet ef migrations add AddAuditTable --schema irby
    
  2. Apply to database:
    dotnet ef database update
    

5.2 Querying Audit Data

using var context = new IrbyDbContext();
var recentLogins = context.AuditDetails
    .Where(a => a.LoginStatus == "Success" && a.ActivityDate >= DateTime.UtcNow.AddDays(-7))
    .OrderByDescending(a => a.ActivityDate)
    .ToList();

6. UML Diagram

+---------------------+
|     AuditDetail     |
+---------------------+
| - AuditDetailId PK  |
| - Area              |
| - ControllerName    |
| - ActionName        |
| - Namespace         |
| - UserId            |
| - RoleId   FK β€”β€”> Role.RoleId
| - EmployeeId FK β€”β€”> Employee.EmployeeId
| - LangId            |
| - IpAddress         |
| - IsFirstLogin      |
| - ActivityDate      |
| - LoginStatus       |
| - PageAccessed      |
| - SessionId         |
| - UrlReferrer       |
| - DocumentImageId   |
+---------------------+

7. Sample Code Snippets

Adding an Audit Entry

var audit = new AuditDetail {
    AuditDetailId = Guid.NewGuid(),
    UserId        = currentUserId,
    RoleId        = currentRoleId,
    EmployeeId    = employeeId,
    ActionName    = "Login",
    ControllerName= "Account",
    ActivityDate  = DateTime.UtcNow,
    IpAddress     = HttpContext.Connection.RemoteIpAddress.ToString(),
    LoginStatus   = loginSuccess ? "Success" : "Failure"
};

context.AuditDetails.Add(audit);
context.SaveChanges();

Rolling Back the Migration

# Reverts one migration (i.e., drops AuditDetail)
dotnet ef database update LastGoodMigrationName

8. Conclusion

πŸ’‘ What did we achieve?

  • Introduced a comprehensive AuditDetail table to track user activity.
  • Provided schema definitions, constraints, and indexes.
  • Ensured seamless integration with EF Core’s migration pipeline.

πŸ”œ Next Steps

  • Seed initial data if needed.
  • Extend queries and reporting layers to consume audit logs.
  • Secure sensitive data (e.g., IP addresses) per compliance policies.

Generated by the IrbyImaging.DbContext Team 🏭