Version: MyBestDealNow

Dealer Vehicle Inventory Management

(Manual Upload + VAuto Third-Party Integration)

Author(s)

Abhishak Kumar Roy
Faizal Khan
Sanket Mal

Last Updated Date

2026-02-03

Version History

Version	Date	Changes	Author
1.0	2025-07-14	Initial Draft (Manual Inventory)	Abhishak Kumar Roy
2.0	2026-01-21	Added VAuto third-party inventory integration, scheduler, and sync rules	Faizal Khan

Feature Overview

Objective

To manage and maintain the dealer's vehicle inventory using:

Manual entry & Excel-based bulk upload
Automated third-party inventory sync from VAuto

So that:

Dealers can bid using vehicles from their inventory
Buyers can browse accurate, up-to-date dealer inventory

Scope

Manual inventory management
Excel bulk upload
Third-party inventory ingestion (VAuto)
Scheduled daily inventory synchronization
Image and document attachment
Integration with auctions and buyer search
Inventory status & reservation handling
Full audit trail and change history

Dependencies

IAM (User / Dealer / Location)
File storage (S3)
Dealer Management Service
SFTP Service (for VAuto integration)

Inventory Sources

Inventory data can originate from:

Manual Entry (UI)
Excel Bulk Upload
VAuto Third-Party Inventory Feed (Automated)

All sources ultimately populate the same InventoryMaster model.

VAuto Third-Party Inventory Integration

Overview

VAuto provides dealer inventory as a daily CSV export placed on our SFTP server. The system ingests these files once per day and synchronizes inventory records accordingly.

Each dealer is mapped to a unique VAuto dealer ID and SFTP folder.

Important Notes:

Each dealer has a separate SFTP folder
Files are placed directly inside the dealer folder
One file per dealer per day

File Export Schedule

Frequency: Once per day
Export Time: 3:00 AM CST

File Naming Convention

inventory_<vAutoDealerId>_YYYYMMDD.csv

Example:

inventory_MP12614_20260116.csv

Scheduler & Sync Process

Execution

A scheduler runs every 24 hours
Scans all dealer SFTP folders
Picks files matching the current date
Each file is processed independently per dealer

VIN-Based Inventory Sync Rules

The CSV file represents the complete inventory snapshot for that day.

For each VIN in the file:

1. Existing VIN Found

Vehicle is identified using VIN
All mapped fields are fully replaced with CSV values
Inventory status remains Available

2. New VIN Found

A new inventory record is created
Default values:
- VehicleStatus = Available
- ReservationStatus = Open

3. VIN Missing From Today’s File

Vehicle is not deleted
VehicleStatus is set to Unavailable

This ensures historical and auction data is preserved.

Inventory Status & Reservation Handling

VehicleStatus (Enum -> Changed)

Available
Unavailable
Sold

Available → Actively listed and usable
Unavailable → Removed from VAuto feed
Sold → Final state (manual or auction-driven)

ReservationStatus (Enum)

Open
Reserved

Newly imported vehicles default to Open
Used for auction and buyer reservation workflows

VAuto CSV → Inventory Mapping Summary

Mappings from VAuto csv file to inventorymaster table:

VIN → Vin
Stock # → StockNumber
New/Used → VehicleType
Year / Make / Model → Direct mapping
Model Number → ModelNumber (new)
Body → BodyStyle + BodyStyleDetails (new)
Transmission / Drivetrain → Direct mapping
Series → trim
Body Door ct → Doors
Odometer → odometer (new)
engine Cylinder ct → engine cylinder ct (new)
engine Displacement → engine displacement (new)
Colour → colorexterior
Interior Color → colorinterior
City MPG → mileage
Highway MPG → highwaymileage (new)
MSRP → Msrp (new)
Price → ActualPrice & DiscountedPrice
Inventory Date → inventorydate (new)
Certified → IsCertifiedPreOwned (Used only)
Description → Description (new)
Features → KeyFeatures
Photo URLs → FileMetadata table (de-duplicated by URL)
Photos Last Modified date → PhotosLastModifiedDate (new)
Engine → Engine
Fuel → FuelType
Dealer Postal Code → dealerPostalCode (new)

Image Handling (VAuto & Manual)

Each image URL is stored once in FileMetadata
Same image URL can be linked to multiple inventories
Prevents duplicate image storage
Images are display-only (not searchable)

New Tables

InventoryChangeLog

Every change made by VAuto sync or manual update is recorded. We delete the records once they are 180 days old.

About This table is introduced to keep history of what's changed and changed to what along with change source for dispute resolution.

Fields:

id (PK)
inventoryid
filename
fieldname
oldvalue
newvalue
source (Manual / Vauto)
changedat

This provides:

Full traceability
Dispute resolution support
Regulatory & operational audit safety

DataSyncHistory

Tracking File processing status.

About This table keeps track of CSV/Excel processing related data for tracking high level actions with error details.

Fields:

id (PK)
filename
provider
category (Inventory, Dealer)
status (Success, Failed, Partial, InProgress)
recordsdeleted (No. of records Deleted)
recordsupdated (No. of records Updated)
recordsinserted (No. of records Inserted)
recordsfailed (No. of records failed processing)
recordsskipped (No. of records skipped due to any reason like incomplete row)
errordetails
processstartedat
processendedat
createdat

This provides:

File level traceability

FileMetadata

Storing image related data for inventories.

About

This table keeps all the data related to files. Files wont be uploaded more than once but reused from single source. The files will have unique ids and those ids will be associated to all the inventories using them. When a dealer removes a file, then, (i) if file has a filename, then that will be deleted from our server if no other dealer is using that file. (ii) if it has url then that url will be deleted from table. After removal of files, their fileid will be removed from the associated inventory.

Fields:

fileid (PK, GUID)
source (VAuto, UserUpload)
filename
fileurl (External URL)
uniqueurl (Stored File name)
createdat
updatedat

This provides:

Optimized File control
Avoiding Duplicate File Uploads from similar URL (Multiple Inventories can use same image without uploading twice)

IntegrationConfigurations

Syncing data based on Dealer's preference.

About This table is used for managing Dealer's preference for automating inventory sync with third party inventory providers.

Fields:

integrationid (PK)
dealerid (FK) (MBDN's dealerid)
provider (VAuto / AutoTrader)
integrationtype (Inventory)
externaldealerid
status (Active→Syncing from third party will happen, Disabled→Syncing from third party wont happen, Paused→Temporarily Skip syncing)
createdat
updatedat
additionalconfig (Extra Data in JSON Format)

This provides:

Granularized Control for sync with Each third party.

Manual Inventory & Bulk Upload

Manual inventory creation/editing
Excel-based bulk upload
Image linking via filenames
Dealer-level access control
Buyer inventory browsing

Refer to the sections below for existing models and APIs.

Data Models (C#)

public enum SortDirection
{
    ASC = 1,
    DESC
}

public record InventoryFilter
{
    // Dealer identification
    public Guid? DealerId { get; set; }
    
    public int? Year { get; set; }
    public int? MinYear { get; set; }
    public int? MaxYear { get; set; }
    
    public string? Make { get; set; }
    public string? Model { get; set; }
    public VehicleType? VehicleType { get; set; }
    public FuelType? FuelType { get; set; }
    public TransmissionType? Transmission { get; set; }
    public VehicleCondition? VehicleCondition { get; set; }
    public VehicleStatus? Status { get; set; }
    
    public decimal? MinPrice { get; set; }
    public decimal? MaxPrice { get; set; }
    
    public int? MinMileage { get; set; }
    public int? MaxMileage { get; set; }
    
    public string? ColorExterior { get; set; }
    public BodyStyle? BodyStyle { get; set; }
    
    public bool? IsCertifiedPreOwned { get; set; }
    
    // Search
    public string? SearchKeyword { get; set; }
    
    // Sorting
    public string? SortBy { get; set; } = "CreatedAt";
    public SortDirection SortDirection { get; set; } = SortDirection.DESC;
    
    // Pagination
    [Range(1, int.MaxValue, ErrorMessage = "Page number must be greater than 0")]
    public int PageNumber { get; set; } = 1;
    
    [Range(1, 100, ErrorMessage = "Page size must be between 1 and 100")]
    public int PageSize { get; set; } = 20;
}

public class Location
{
    public Guid LocationId { get; set; } = Guid.NewGuid();

    [Required]
    public Guid DealerId { get; set; }

    [Required]
    public string Name { get; set; }

    public string AddressLine1 { get; set; }
    public string AddressLine2 { get; set; }
    public string City { get; set; }
    public string State { get; set; }
    public string ZipCode { get; set; }

    public string Notes { get; set; }
}

public enum VehicleType
{
    New,
    PreOwned,
    Both
}

public enum BodyStyle
{
        SUV = 1,
        Sedan,
        Hatchback,
        Coupe,
        Convertible,
        Van,
        Wagon,
        Crossover,
        Pickup,
        Roadster,
        Other
}

public enum VehicleStatus   // Some enum elements removed
{
    Available,
    Unavailable,
    Sold
}

public enum ReservationStatus
{
    Reserved,
    Open
}

public enum Drivetrain
{
    FWD, // Front-Wheel Drive
    RWD, // Rear-Wheel Drive
    AWD, // All-Wheel Drive
    FourWD // 4-Wheel Drive
}

public enum VehicleCondition
{
    New,
    Excellent,
    Good,
    Fair,
    Poor,
    Salvage
}

public enum FuelType // "Other" added
{
    Gasoline,
    Diesel,
    Electric,
    Hybrid,
    PlugInHybrid,
    Ethanol,
    FlexFuel,
    CNG, // Compressed Natural Gas
    LPG,  // Liquefied Petroleum Gas
    Other
}

public enum TransmissionType // "Other" added
{
    Manual,
    Automatic,
    CVT, // Continuously Variable Transmission
    SemiAutomatic,
    Other
}

public record InventoryMaster
{
    public Guid InventoryId { get; set; }
    public Guid DealerId { get; set; }
    
    [Required]
    public string Vin { get; set; } = string.Empty;
    
    public int? StockNumber { get; set; }
    
    [Required]
    public int Year { get; set; }
    
    public VehicleType? VehicleType { get; set; }
    public DateTime? YearOfPurchase { get; set; }
    
    [Required]
    public Brand Make { get; set; }
    
    [Required]
    public string Model { get; set; } = string.Empty;

    
    public string? Trim { get; set; }
    public BodyStyle? BodyStyle { get; set; }
    public string? ColorExterior { get; set; }
    public string? ColorInterior { get; set; }
    public int? Mileage { get; set; }
    public string? MileageUnit { get; set; }
    public string? Engine { get; set; }
    public TransmissionType? Transmission { get; set; }
    public FuelType? FuelType { get; set; }
    public Drivetrain? Drivetrain { get; set; }
    public int? Doors { get; set; }

    public string? Odometer { get; set; } = string.Empty;
    public int? EngineCylinderCt { get; set; } = null;
    public float? EngineDisplacement { get; set; } = null;
    public string? FuelTypeDetails { get; set; } = string.Empty;
    public string? TransmissionDetails { get; set; } = string.Empty;
    public string? BodyStyleDetails { get; set; } = string.Empty;
    public string? ModelNumber { get; set; } = string.Empty;
    public float? MSRP { get; set; } = null;
    public DateTime? InventoryDate { get; set; };
    public string? Description { get; set; } = string.Empty;
    public int? HighwayMileage { get; set; } = null;
    public DateTime? PhotosLastModifiedDate { get; set; };
    public string? DealerPostalCode { get; set; } = string.Empty;

    
    
    public decimal? ActualPrice { get; set; }
    public decimal? DiscountedPrice { get; set; }
    public string Currency { get; set; } = Currency.USD;
    public bool TaxIncluded { get; set; } = false;
    
    public VehicleCondition? VehicleCondition { get; set; }
    public bool? IsCertifiedPreOwned { get; set; };
    public DateTime? RegistrationDate { get; set; }
    public DateTime? WarrantyExpiryDate { get; set; }
    
    public VehicleStatus Status { get; set; } = VehicleStatus.Available;
    public Guid LocationId { get; set; }
    
    public List<string>? Images { get; set; }
    public List<string>? KeyFeatures { get; set; }
    public string? AdditionalNote { get; set; }
    
    public Guid CreatedBy { get; set; }
    public DateTimeOffset CreatedAt { get; set; } = DateTimeOffset.UtcNow;
    public Guid? UpdatedBy { get; set; }
    public DateTimeOffset? UpdatedAt { get; set; }
    public bool IsDeleted { get; set; } = false;
}

public record UpdateInventoryRequest
{
    [StringLength(50)]
    public string? StockNumber { get; set; }
    
    [Range(1900, 2030, ErrorMessage = "Year must be between 1900 and 2030")]
    public int? Year { get; set; }
    
    public VehicleType? VehicleType { get; set; }
    
    [DataType(DataType.Date)]
    public DateTime? YearOfPurchase { get; set; }
    
    public Brand? Make { get; set; }
    
    [StringLength(100)]
    public string? Model { get; set; }
    
    [StringLength(100)]
    public string? Trim { get; set; }
    
    public BodyStyle? BodyStyle { get; set; }
    
    [StringLength(50)]
    public string? ColorExterior { get; set; }
    
    [StringLength(50)]
    public string? ColorInterior { get; set; }
    
    [Range(0, int.MaxValue, ErrorMessage = "Mileage must be a positive number")]
    public int? Mileage { get; set; }
    
    [StringLength(20)]
    public string? MileageUnit { get; set; }
    
    [StringLength(100)]
    public string? Engine { get; set; }
    
    public TransmissionType? Transmission { get; set; }
    
    public FuelType? FuelType { get; set; }
    
    public Drivetrain? Drivetrain { get; set; }
    
    [Range(1, 10, ErrorMessage = "Number of doors must be between 1 and 10")]
    public int? Doors { get; set; }
    
    [Range(0, double.MaxValue, ErrorMessage = "Actual price must be a positive number")]
    public decimal? ActualPrice { get; set; }
    
    [Range(0, double.MaxValue, ErrorMessage = "Discounted price must be a positive number")]
    public decimal? DiscountedPrice { get; set; }
    
    [StringLength(3)]
    public string? Currency { get; set; }
    
    public bool? TaxIncluded { get; set; }
    
    public VehicleCondition? VehicleCondition { get; set; }
    
    public bool? IsCertifiedPreOwned { get; set; }
    
    [DataType(DataType.Date)]
    public DateTime? RegistrationDate { get; set; }
    
    [DataType(DataType.Date)]
    public DateTime? WarrantyExpiryDate { get; set; }
    
    public VehicleStatus? Status { get; set; }
    
    public Guid? LocationId { get; set; }
    
    public List<string>? Images { get; set; }
    
    public List<string>? KeyFeatures { get; set; }
    
    [StringLength(1000)]
    public string? AdditionalNote { get; set; }

    public string? Odometer { get; set; } = string.Empty;
    public int? EngineCylinderCt { get; set; } = null;
    public float? EngineDisplacement { get; set; } = null;
    public string? FuelTypeDetails { get; set; } = string.Empty;
    public string? TransmissionDetails { get; set; } = string.Empty;
    public string? BodyStyleDetails { get; set; } = string.Empty;
    public string? ModelNumber { get; set; } = string.Empty;
    public float? MSRP { get; set; } = null;
    public DateTime? InventoryDate { get; set; };
    public string? Description { get; set; } = string.Empty;
    public int? HighwayMileage { get; set; } = null;
    public DateTime? PhotosLastModifiedDate { get; set; };
    public string? DealerPostalCode { get; set; } = string.Empty;
}

Bulk Upload Instructions

Excel Template Columns:

VIN	Make	Model	Year	Mileage	Price	Color	LocationCode	ImageFile1	ImageFile2	Status

Image Upload Instructions:

Upload all images in a zip folder or as a multi-select batch
Use the image file names in the Excel (e.g. IMG_1234.jpg)
System will link based on VIN and filename match

Scheduler Execution Flow

1. Scheduler Trigger

Scheduler runs once every 24 hours
Execution is aligned with VAuto export completion time
(3:00 AM CST)

2. Dealer Eligibility Check

For each dealer configured in IntegrationConfigurations:

Fetch records where:
- provider = VAuto
- integrationtype = Inventory
Evaluate integration status:

Status	Action
Active	Proceed with sync
Paused	Skip processing
Disabled	Skip processing

No SFTP calls are made for dealers whose status in IntegrationConfigurations is Paused or Disabled.

3. SFTP File Discovery

For eligible dealers (Active status):

Connect to the dealer-specific SFTP folder
Search for files matching the naming convention: inventory_<vAutoDealerId>_YYYYMMDD.csv
Validate:
- File exists for the current date
- File has not been processed earlier

Note : Even if the file processing was failed or skipped for any reason, it wont be retried.

4. File Registration

Once a valid file is detected:

Create a DataSyncHistory record with:
- status = InProgress
- provider = VAuto
- category = Inventory
- processstartedat = current timestamp

This ensures file-level traceability even if processing fails mid-way.

5. CSV Parsing & Validation

Parse CSV headers and rows
Validate mandatory fields:
- VIN
- DealerId (VAuto DealerId)
- Make
- Model
- Price
Rows with invalid or missing mandatory data are:
- Skipped
- Logged as failed records

6. VIN-Based Inventory Synchronization

The CSV file represents the complete daily inventory snapshot for the dealer.

6.1 Existing VIN Found

Identify inventory using (VIN + MBDN's DealerId) combination
Fully replace all mapped fields with CSV values
Set:
- VehicleStatus = Available
Log field-level changes in InventoryChangeLog
If there was a row which already had all the important fields (VIN, DealerId, Make, Model, Price) but in the latest csv, these fields are missing, then they will be marked InventoryStatus = Unavailable

6.2 New VIN Found

Create a new inventory record
Default values:
- VehicleStatus = Available
- ReservationStatus = Open
Insert or associate images via FileMetadata
Log creation in InventoryChangeLog

6.3 VIN Missing From Today’s File

After processing all rows:

Identify existing inventories for the dealer whose VINs are not present in the current file
Update:
- VehicleStatus = Unavailable
No hard delete is performed

7. Image Processing

Image URLs from the CSV are:
- De-duplicated using uniqueurl
- Stored once in FileMetadata
Inventory records store references to FileMetadata.fileid
Multiple inventories may reference the same image

8. Sync Completion & Metrics Update

After file processing completes:

Update DataSyncHistory with:
- status = InProgress / Success / Partial / Failed / FileEmptyOrNoValidRows
- recordsinserted
- recordsupdated
- recordsdeleted
- recordsfailed
- recordsskipped
- processendedat

9. Error Handling & Recovery

Row-level failures do not stop file processing
File-level failures mark the sync as Failed

10. Audit & Observability

The system guarantees:

Field-level change tracking (InventoryChangeLog)
File-level traceability (DataSyncHistory)
Dealer-controlled sync behavior

Key Guarantees

No duplicate inventory creation
No hard deletes from third-party feeds
Safe pause / resume behavior
Full audit trail
Dealer-specific data isolation

Notes

Manual edits may be overwritten on next sync
Deletions from VAuto feed result in Unavailable status, not hard delete
All timestamps are stored in UTC

Dealer Vehicle Inventory Management

Author(s)​

Last Updated Date​

Version History​

Feature Overview​

Objective​

Scope​

Dependencies​

Inventory Sources​

VAuto Third-Party Inventory Integration​

Overview​

Important Notes:​

File Export Schedule​

File Naming Convention​

Scheduler & Sync Process​

Execution​

VIN-Based Inventory Sync Rules​

1. Existing VIN Found​

2. New VIN Found​

3. VIN Missing From Today’s File​

Inventory Status & Reservation Handling​

VehicleStatus (Enum -> Changed)​

ReservationStatus (Enum)​

VAuto CSV → Inventory Mapping Summary​

Image Handling (VAuto & Manual)​

New Tables​

InventoryChangeLog​

DataSyncHistory​

FileMetadata​

IntegrationConfigurations​

Manual Inventory & Bulk Upload​

Data Models (C#)​

Bulk Upload Instructions​

Scheduler Execution Flow​

1. Scheduler Trigger​

2. Dealer Eligibility Check​

3. SFTP File Discovery​

4. File Registration​

5. CSV Parsing & Validation​

6. VIN-Based Inventory Synchronization​

6.1 Existing VIN Found​

6.2 New VIN Found​

6.3 VIN Missing From Today’s File​

7. Image Processing​

8. Sync Completion & Metrics Update​

9. Error Handling & Recovery​

10. Audit & Observability​

Key Guarantees​

Notes​

Author(s)

Last Updated Date

Version History

Feature Overview

Objective

Scope

Dependencies

Inventory Sources

VAuto Third-Party Inventory Integration

Overview

Important Notes:

File Export Schedule

File Naming Convention

Scheduler & Sync Process

Execution

VIN-Based Inventory Sync Rules

1. Existing VIN Found

2. New VIN Found

3. VIN Missing From Today’s File

Inventory Status & Reservation Handling

VehicleStatus (Enum -> Changed)

ReservationStatus (Enum)

VAuto CSV → Inventory Mapping Summary

Image Handling (VAuto & Manual)

New Tables

InventoryChangeLog

DataSyncHistory

FileMetadata

IntegrationConfigurations

Manual Inventory & Bulk Upload

Data Models (C#)

Bulk Upload Instructions

Scheduler Execution Flow

1. Scheduler Trigger

2. Dealer Eligibility Check

3. SFTP File Discovery

4. File Registration

5. CSV Parsing & Validation

6. VIN-Based Inventory Synchronization

6.1 Existing VIN Found

6.2 New VIN Found

6.3 VIN Missing From Today’s File

7. Image Processing

8. Sync Completion & Metrics Update

9. Error Handling & Recovery

10. Audit & Observability

Key Guarantees

Notes