π Migration Documentation: 20231113151649_ReviewAfterColumnsForBigBuckets
π Table of Contents
- Overview & Purpose
- Dependencies & Technologies
- Key Functional Areas
- Detailed Data Models
- 4.1 Ticket Table Updates
- 4.2 AuditReport Table
- 4.3 GeoLocations Table
- 4.4 LicenseReport Table
- Migration Workflow
- UML Class Diagram
- Sample Code Blocks
- Usage Examples
- Conclusion
1. π§ Overview & Purpose
This Entity Framework Core Migration (ReviewAfterColumnsForBigBuckets) performs the following tasks:
- Extends the existing
irby.Tickettable 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
- Add Migration (already done):
dotnet ef migrations add ReviewAfterColumnsForBigBuckets --project IrbyImaging.DbContext - 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! π