πŸ“š Migration Documentation: 20231113151649_ReviewAfterColumnsForBigBuckets


πŸ“‘ Table of Contents

  1. Overview & Purpose
  2. Dependencies & Technologies
  3. Key Functional Areas
  4. Detailed Data Models
  5. Migration Workflow
  6. UML Class Diagram
  7. Sample Code Blocks
  8. Usage Examples
  9. Conclusion

1. 🧐 Overview & Purpose

This Entity Framework Core Migration (ReviewAfterColumnsForBigBuckets) performs the following tasks:

  • Extends the existing irby.Ticket table with three new columns to support a "Review After" workflow.
  • Creates three new tables β€” AuditReport, GeoLocations, and LicenseReport β€” to capture various reporting data extracted from imported documents or external systems.

Why?

  • ReviewAfter flags allow tickets to be automatically queued for reΒ­-inspection.
  • AuditReport, GeoLocations, LicenseReport tables provide structured stores for analytics, compliance auditing, and geospatial metadata.

2. βš™οΈ Dependencies & Technologies

Technology Purpose
.NET (.NET 6+/7+) Runtime & language features
Entity Framework Core ORM for database migrations & context
SQL Server (MSSQL) Target relational database
C# Migration code language
EF Core Tools (CLI) dotnet ef migrations & database update

3. πŸ› οΈ Key Functional Areas

3.1 Ticket Table Enhancements

Column Type Nullable Default Description
ReviewAfter bit No false Flag to queue ticket for future review
ReviewAfterDate datetime2 Yes ‑ Date after which a review is required
ReviewReason nvarchar(max) Yes ‑ Explanation for scheduling the review

3.2 New Reporting Tables

Table Name Purpose
AuditReport Stores user activity logs (controller actions, IP addresses, etc.)
GeoLocations Holds geospatial and property metadata extracted from documents
LicenseReport Tracks license information (state, expiration, renewals, etc.)

4. πŸ—„οΈ Detailed Data Models

4.1 Ticket Table Updates

migrationBuilder.AddColumn<bool>(
    name: "ReviewAfter",
    schema: "irby",
    table: "Ticket",
    type: "bit",
    nullable: false,
    defaultValue: false);

migrationBuilder.AddColumn<DateTime>(
    name: "ReviewAfterDate",
    schema: "irby",
    table: "Ticket",
    type: "datetime2",
    nullable: true);

migrationBuilder.AddColumn<string>(
    name: "ReviewReason",
    schema: "irby",
    table: "Ticket",
    type: "nvarchar(max)",
    nullable: true);

Business Impact

  • ReviewAfter: triggers downstream processes or alerts.
  • ReviewAfterDate & ReviewReason: provide context for audits and SLA compliance.

4.2 AuditReport Table

Column Type Nullable Description
JdeNumber nvarchar(max) Yes Unique identifier in JDE system
FirstName, LastName nvarchar(max) Yes User’s name
Role, Department nvarchar(max) Yes Organizational details
Controller, Action nvarchar(max) Yes MVC controller & action invoked
IpAddress nvarchar(max) Yes User’s IP address
ActivityDate nvarchar(max) Yes Timestamp of the activity (string)
LoginStatus, PageAccessed nvarchar(max) Yes Authentication & navigation info
DocumentImageId nvarchar(max) Yes Related document reference
LastNonInteractiveSignInDateTime nvarchar(max) Yes Metadata from Identity system
LastSignInDateTime nvarchar(max) Yes Metadata from Identity system
EmployeeEmail nvarchar(max) Yes User's corporate email

No primary key is specified; this table acts as a β€œflat” audit export.


4.3 GeoLocations Table

Column Type Nullable Description
DocumentImageId uniqueidentifier No PK, reference to document image
Latitude, Longitude decimal(18,2) No Geospatial coordinates
PropertyName, Address nvarchar(max) Yes Property/site metadata
OwnerType, PropertyType, RentableArea, … nvarchar(max) Yes Lease & property-specific metadata (see code)
BlobName nvarchar(max) Yes Blob storage reference
MonthlySalesTax, … nvarchar(max) Yes Financial metadata

Designed for location-based reporting and mapping visualizations.


4.4 LicenseReport Table

Column Type Nullable Description
State nvarchar(max) Yes License jurisdiction
LicenseNumber nvarchar(max) Yes Unique license identifier
LicenseClassifications nvarchar(max) Yes e.g., professional, contractor, etc.
ClassificationCodes nvarchar(max) Yes Regulatory codes
IssuingParty nvarchar(max) Yes Agency or authority issuing license
DateAcquired nvarchar(max) Yes When the license was obtained
Status, ExpirationDate, RenewalStatus, Qualifiers nvarchar(max) Yes Lifecycle and qualifiers

Used for compliance dashboards and expiration alerts.


5. πŸ”„ Migration Workflow

5.1 Applying the Migration

  1. Add Migration (already done):
    dotnet ef migrations add ReviewAfterColumnsForBigBuckets --project IrbyImaging.DbContext
    
  2. Update Database:
    dotnet ef database update --project IrbyImaging.DbContext
    

The above will apply Up(), creating new columns and tables.


5.2 Rolling Back the Migration

dotnet ef database update <PreviousMigrationName> --project IrbyImaging.DbContext

This will invoke the Down() method, executing:

migrationBuilder.DropTable("AuditReport");
migrationBuilder.DropTable("GeoLocations");
migrationBuilder.DropTable("LicenseReport");
migrationBuilder.DropColumn("ReviewAfter", schema: "irby", table: "Ticket");
migrationBuilder.DropColumn("ReviewAfterDate", schema: "irby", table: "Ticket");
migrationBuilder.DropColumn("ReviewReason", schema: "irby", table: "Ticket");

6. πŸ“Š UML Class Diagram

classDiagram
  class Ticket {
    +bool ReviewAfter
    +DateTime? ReviewAfterDate
    +string ReviewReason
  }
  class AuditReport {
    +string JdeNumber
    +string FirstName
    +string LastName
    +string Role
    +string Department
    +string Controller
    +string Action
    +string IpAddress
    +string ActivityDate
    +string LoginStatus
    +string PageAccessed
    +string DocumentImageId
    +string LastNonInteractiveSignInDateTime
    +string LastSignInDateTime
    +string EmployeeEmail
  }
  class GeoLocations {
    +Guid DocumentImageId
    +decimal Latitude
    +decimal Longitude
    +string PropertyName
    +string Address
    +string OwnerType
    +string PropertyType
    +string RentableArea
    +string MonthToMonth
    +string LeasePurpose
    +string MonthlyBaseRent
    +string ContractDate
    +string StreetAddress
    +string CommencementDate
    +string Landlord
    +string ExpiratinDate
    +string State
    +string RegionLead
    +string LeaseCode
    +string FullyExecuted
    +string SubjectToEscalation
    +string LeaseTerm
    +string QuantaReviewed
    +string City
    +string County
    +string TotalCost
    +string Zip
    +string BlobName
    +string MonthlySalesTax
    +string MonthlyAVTax
    +string MonthlyInsurancePayment
  }
  class LicenseReport {
    +string State
    +string LicenseNumber
    +string LicenseClassifications
    +string ClassificationCodes
    +string IssuingParty
    +string DateAcquired
    +string Status
    +string ExpirationDate
    +string RenewalStatus
    +string Qualifiers
  }
  Ticket -- AuditReport : uses >
  Ticket -- GeoLocations : uses >
  Ticket -- LicenseReport : uses >

7. πŸ”§ Sample Code Blocks

Full Migration Class

public partial class ReviewAfterColumnsForBigBuckets : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // 1. Add ReviewAfter columns
        migrationBuilder.AddColumn<bool>( /* ... */ );
        migrationBuilder.AddColumn<DateTime>( /* ... */ );
        migrationBuilder.AddColumn<string>( /* ... */ );

        // 2. Create AuditReport
        migrationBuilder.CreateTable("AuditReport", columns: table => new { /*...*/ });

        // 3. Create GeoLocations
        migrationBuilder.CreateTable("GeoLocations", columns: table => new { /*...*/ });

        // 4. Create LicenseReport
        migrationBuilder.CreateTable("LicenseReport", columns: table => new { /*...*/ });
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        // Drop in reverse order
        migrationBuilder.DropTable("AuditReport");
        migrationBuilder.DropTable("GeoLocations");
        migrationBuilder.DropTable("LicenseReport");
        migrationBuilder.DropColumn("ReviewAfter", schema: "irby", table: "Ticket");
        migrationBuilder.DropColumn("ReviewAfterDate", schema: "irby", table: "Ticket");
        migrationBuilder.DropColumn("ReviewReason", schema: "irby", table: "Ticket");
    }
}

8. πŸš€ Usage Examples

using (var context = new IrbyDbContext())
{
    // Create a Ticket with review-after logic
    var ticket = new Ticket {
        Title = "Lease Expiration Check",
        ReviewAfter = true,
        ReviewAfterDate = DateTime.UtcNow.AddDays(30),
        ReviewReason = "LeaseExpiresSoon"
    };
    context.Tickets.Add(ticket);
    context.SaveChanges();
}

// Query geo-locations for mapping dashboard
var locations = context.GeoLocations
    .Where(g => g.State == "TX")
    .ToList();

9. 🏁 Conclusion

This migration is a pivotal enhancement to the IrbyImaging database schema, adding robust auditing, geospatial, and licensing data stores while supporting a new ReviewAfter workflow on tickets. By following the provided migration commands and sample usage, developers can seamlessly integrate these new capabilities into their existing .NET applications.

Happy coding! πŸŽ‰