🗂️ Migration Documentation: AddSSNColumn (20240124141838)

File: IrbyImaging.DbContext/Migrations/20240124141838_AddSSNColumn.cs
Migration Class: AddSSNColumn
Namespace: IrbyImaging.DbContext.Migrations


📑 Table of Contents

  1. Overview & Purpose
  2. Dependencies & Technologies
  3. Migration Structure
  4. Database Tables Created
  5. Detailed Table Schemas
  6. Usage Examples
  7. UML Diagram
  8. Conclusion

1. Overview & Purpose

This Entity Framework Core migration performs two primary tasks:

  • 🔒 Add SSN Support: Introduces a nullable LastFourSSN column (type nvarchar(max)) to the irby.Employee table to store employees’ last four Social Security digits.
  • 🗃️ Create Reporting Tables: Establishes 13 new reporting tables (e.g., AuditReport, LeaseDetails, LicenseReport, etc.) used for analytics and export scenarios.

Why?

  • Security & Compliance: Only storing the last four digits of SSN reduces sensitive data exposure.
  • Reporting: Pre-defined “view” tables simplify data access for business intelligence tools.

2. Dependencies & Technologies

Technology Purpose
.NET (C#) Language & runtime
Entity Framework Core ORM for migrations & database access
Microsoft.EntityFrameworkCore.Migrations Migration API (MigrationBuilder)

3. Migration Structure

3.1 Up Method ⚙️

The Up method applies schema changes:

protected override void Up(MigrationBuilder migrationBuilder)
{
    // 1️⃣ Add LastFourSSN column to Employee
    migrationBuilder.AddColumn<string>(
        name: "LastFourSSN",
        schema: "irby",
        table: "Employee",
        type: "nvarchar(max)",
        nullable: true);

    // 2️⃣ Create 13 new reporting tables
    migrationBuilder.CreateTable(
        name: "AuditReport",
        columns: table => new { /* ... columns ... */ },
        constraints: table => { });
    // ... other CreateTable calls for ContractsByLead, ContractTypeDetails, etc. ...
}
  • AddColumn<T>: Adds a new column of type <T>
  • CreateTable: Defines each new table’s schema

3.2 Down Method ↩️

The Down method reverts those changes in reverse order:

protected override void Down(MigrationBuilder migrationBuilder)
{
    // 1️⃣ Drop all 13 reporting tables
    migrationBuilder.DropTable(name: "AuditReport");
    // ... other DropTable calls ...

    // 2️⃣ Remove LastFourSSN from Employee
    migrationBuilder.DropColumn(
        name: "LastFourSSN",
        schema: "irby",
        table: "Employee");
}

4. Database Tables Created 🗃️

Table Name Description
AuditReport Tracks user/controller actions & login status
ContractsByLead Counts contracts grouped by director
ContractTypeDetails Contracts count by state & work type
ContractTypeNumber Document type counts
DirectorLeaseInfo Lease-cost summary per director
DirectorProperties # of properties per director by state
GeoLocations Geospatial & lease metadata per document image
JobAndFieldOfficeMapping Job ↔ office geolocation & manager info
LeaseDetails Full lease metadata
LeasePropertyDetails Lease cost summary by purpose
LicenseReport License details & statuses
StatePropertyCosts Total cost summary per state

5. Detailed Table Schemas

Below are summarized schemas for each reporting table. All columns default to nullable: true unless specified.

5.1 AuditReport

Column Type Nullable
JdeNumber nvarchar Yes
FirstName, LastName, Role… nvarchar Yes
IpAddress, PageAccessed… nvarchar Yes
DocumentImageId nvarchar Yes
LastSignInDateTime nvarchar Yes
EmployeeEmail nvarchar Yes

5.2 ContractsByLead

Column Type Nullable
Director nvarchar Yes
NumberOfContracts int No

5.3 ContractTypeDetails

Column Type Nullable
State nvarchar Yes
TypeOfWork nvarchar Yes
NumberOfContracts int No

5.4 ContractTypeNumber

Column Type Nullable
DocumentType nvarchar Yes
TypeCount int No

5.5 DirectorLeaseInfo

Column Type Nullable
Director nvarchar Yes
NumberOfProperties int Yes
MonthlyAVTax, …, TotalCost decimal(18,2) Yes

5.6 DirectorProperties

Column Type Nullable
State nvarchar Yes
Director nvarchar Yes
NumberOfProperties int No

5.7 GeoLocations

Column Type Nullable
DocumentImageId uniqueidentifier No
Latitude, Longitude decimal(18,2) No
PropertyName, Address… nvarchar Yes
… (20+ fields) nvarchar Yes

5.8 JobAndFieldOfficeMapping

Column Type Nullable
Latitude, Longitude decimal(18,2) No
FieldOffice nvarchar Yes
JobNumber, ProjectManager decimal(18,2) No
Manager, Superintendent nvarchar Yes

5.9 LeaseDetails

Column Type Nullable
DocumentImageId uniqueidentifier No
City, County, DocumentName… nvarchar Yes
MonthlyAVTax, …, TotalCost decimal(18,2) Yes

5.10 LeasePropertyDetails

Column Type Nullable
LeasePurpose nvarchar Yes
NumberOfProperties int Yes
MonthlyAVTax, … decimal(18,2) Yes

5.11 LicenseReport

Column Type Nullable
State, LicenseNumber nvarchar Yes
LicenseClassifications… nvarchar Yes
Status, ExpirationDate… nvarchar Yes

5.12 StatePropertyCosts

Column Type Nullable
State nvarchar Yes
MonthlyTotal decimal(18,2) No

6. Usage Examples

  1. Add & Apply Migration

    # 1. Create migration
    dotnet ef migrations add AddSSNColumn
    
    # 2. Update database
    dotnet ef database update
    
  2. Rollback

    # Roll back to previous migration
    dotnet ef database update LastGoodMigrationName
    
  3. Querying New Tables

    using var context = new IrbyDbContext();
    var auditEntries = context.Set<AuditReport>().ToList();
    

7. UML Diagram

+---------------------+
|     Employee        |         ← existing table
|---------------------|
| + LastFourSSN : str |
+---------------------+

           ▲
           | extends

+-------------------------+
|   AddSSNColumn (EF M)   |
|-------------------------|
| + Up()                  |
| + Down()                |
+-------------------------+

Creates: AuditReport, ContractsByLead, …, StatePropertyCosts
(each with flat schemas – no PKs defined)

8. Conclusion

This migration enhances data compliance by safely storing SSN fragments and streamlines reporting through a suite of ready‐to‐use summary tables. After applying it, your database will be immediately prepared for both application features that require employee SSNs (last four digits) and various reporting dashboards or BI tools consuming the new tables. 🎉


End of 20240124141838_AddSSNColumn Migration Documentation