🗂️ Migration Documentation: AddSSNColumn (20240124141838)
File: IrbyImaging.DbContext/Migrations/20240124141838_AddSSNColumn.cs
Migration Class: AddSSNColumn
Namespace: IrbyImaging.DbContext.Migrations
📑 Table of Contents
- Overview & Purpose
- Dependencies & Technologies
- Migration Structure
- Database Tables Created
- Detailed Table Schemas
- Usage Examples
- UML Diagram
- 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
-
Add & Apply Migration
# 1. Create migration
dotnet ef migrations add AddSSNColumn
# 2. Update database
dotnet ef database update
-
Rollback
# Roll back to previous migration
dotnet ef database update LastGoodMigrationName
-
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