Version: MarketPulse

Export History Management

Author(s)

Ashik
Debaleena

Last Updated Date

2026-03-25

SRS References

SRS Document: MarketPulse-SRS-v1.0
Section: Data Export & History Tracking
Document Link: Export History SRS

Version History

Version	Date	Author	Changes
1.0	2026-03-18	Ashik	Initial export history feature document
1.1	2026-03-19	Ashik	Renamed exporttype to filetype; Added exporttrackingdetails table; Added refined listings API endpoint with advanced date filtering
1.2	2026-03-20	Ashik	Changed file download endpoint to use filename (guid.extension); Removed DateFilterType enum; Simplified RefinedVehicleListingFilter to use direct date range fields; Removed DateFilterHelper; Updated POST /export-history to use RefinedVehicleListingFilter as request

Feature Overview

The Export History Management feature provides comprehensive tracking and management of all data export operations in the MarketPulse platform. This feature records export requests, tracks their processing status, stores generated files, and provides download capabilities with role-based access control. Additionally, it includes advanced vehicle listing viewing capabilities with professional-grade filtering options.

Key Features

Export Tracking: Record all export operations with unique export codes and status tracking
Individual Record Tracking: Track each exported vehicle listing via exporttrackingdetails table
Multi-Type Exports: Support for Full exports (all records) and Incremental exports (delta only)
Status Monitoring: Track export lifecycle from Queued → Processing → Completed/Failed
File Management: Store generated export files with metadata (size, filename, path)
Download Capability: Secure file download with role-based access control
Filter & Pagination: Search and filter export history with server-side pagination
Retry Failed Exports: Ability to retry exports that failed during processing
Audit Trail: Complete history of who exported what data and when
Role-Based Access: Dealers see only their exports; Admins/Resellers see all exports
Export Code Generation: Human-readable sequential export codes (exp-001, exp-002, etc.)
Date Range Filtering: Flexible date range filters for posted date and created date
Refined Listings View: Paginated view of refined vehicle listings with advanced filters
Multi-Field Search: Search across make, model, listing title, and dealer name
Export to Excel: Direct export capability from refined listings view

Export Types

CSV: Export data in CSV (Comma-Separated Values) format
Excel: Export data in Excel (.xlsx) format with formatting and totals

Export Status Flow

Queued → InProgress → Completed
                   ↓
                  Failed (with retry capability)

Requirements

Functional Requirements

ID	Requirement	Priority
FR-1	Track all export operations with unique export codes	High
FR-2	Support Full and Incremental export types	High
FR-3	Monitor export status (Queued/InProgress/Completed/Failed)	High
FR-4	Store export files with metadata (size, filename, path)	High
FR-5	Provide secure file download with access control	High
FR-6	Paginated export history listing with filters	High
FR-7	Filter exports by dealer, status, date range	High
FR-8	Retry failed exports	Medium
FR-9	Search exports by export code or dealer name	Medium
FR-10	Role-based visibility (dealers see only their exports)	High
FR-11	Export file storage in dealer-specific directories	Medium
FR-12	Track who requested each export	Medium
FR-13	Store filter criteria used for each export (JSON)	Medium
FR-14	Track export completion time and record counts	Medium
FR-15	Track individual vehicle listings per export (exporttrackingdetails)	High
FR-16	View paginated refined vehicle listings	High
FR-17	Filter listings by posted date range	High
FR-18	Filter listings by created date range	High
FR-19	Search listings across make, model, title, and dealer name	High
FR-20	Filter listings by dealer and source	High
FR-21	Support direct Excel export from refined listings view	Medium

Non-Functional Requirements

ID	Requirement	Priority
NFR-1	Export history listing response time < 500ms	High
NFR-2	File download initiation time < 200ms	High
NFR-3	Support concurrent export downloads (100+ users)	High
NFR-4	Export file storage with 99.9% availability	High
NFR-5	Pagination support for 10,000+ export records	Medium
NFR-6	File download bandwidth optimization	Medium
NFR-7	Refined listings query response time < 800ms	High
NFR-8	Support pagination for 100,000+ refined listings	High
NFR-9	Date range query optimization	Medium
NFR-10	Keyword search across multiple fields in < 1s	High

Database Schema

ExportTracking Table

Current Structure:

CREATE TABLE IF NOT EXISTS public.exporttracking
(
    exportid uuid NOT NULL DEFAULT gen_random_uuid(),
    exportcode text,
    dealerid uuid NOT NULL,
    filetype character varying(20) NOT NULL DEFAULT 'CSV'::character varying,
    status character varying(20) NOT NULL DEFAULT 'Queued'::character varying,
    totalrecords integer NOT NULL DEFAULT 0,
    filtercriteria jsonb,
    filepath text,
    requestedat timestamp with time zone NOT NULL DEFAULT now(),
    completedat timestamp with time zone,
    requestedby uuid,
    errormessage text,
    createdat timestamp with time zone NOT NULL DEFAULT now(),
    
    CONSTRAINT exporttracking_pkey PRIMARY KEY (exportid),
    CONSTRAINT fkexportdealer FOREIGN KEY (dealerid)
        REFERENCES public.dealermaster (dealerid)
        ON UPDATE NO ACTION
        ON DELETE CASCADE,
    CONSTRAINT fkexportrequestedby FOREIGN KEY (requestedby)
        REFERENCES public.userdetails (userid)
        ON UPDATE NO ACTION
        ON DELETE SET NULL,
    CONSTRAINT chkexportstatus CHECK (status::text = ANY (ARRAY['Queued'::character varying::text, 'InProgress'::character varying::text, 'Completed'::character varying::text, 'Failed'::character varying::text])),
    CONSTRAINT chkfiletype CHECK (filetype::text = ANY (ARRAY['CSV'::character varying::text, 'Excel'::character varying::text]))
);

-- Indexes
CREATE INDEX IF NOT EXISTS idxexportdealer
    ON public.exporttracking USING btree (dealerid ASC NULLS LAST);

CREATE INDEX IF NOT EXISTS idxexportrequested
    ON public.exporttracking USING btree (dealerid ASC NULLS LAST, requestedat DESC NULLS FIRST);

CREATE INDEX IF NOT EXISTS idxexportstatus
    ON public.exporttracking USING btree (status ASC NULLS LAST);

ExportTrackingDetails Table

Structure:

CREATE TABLE IF NOT EXISTS public.exporttrackingdetails
(
    trackingdetailsid uuid NOT NULL DEFAULT gen_random_uuid(),
    exporttrackingid uuid NOT NULL,
    refinedlistingid uuid NOT NULL,
    exportedat timestamp with time zone NOT NULL DEFAULT now(),
    
    CONSTRAINT exporttrackingdetails_pkey PRIMARY KEY (trackingdetailsid),
    CONSTRAINT fkexporttrackingid FOREIGN KEY (exporttrackingid)
        REFERENCES public.exporttracking (exportid)
        ON UPDATE NO ACTION
        ON DELETE CASCADE,
    CONSTRAINT fkrefinedlistingid FOREIGN KEY (refinedlistingid)
        REFERENCES public.refinedvehiclelisting (refinedlistingid)
        ON UPDATE NO ACTION
        ON DELETE CASCADE
);

-- Indexes
CREATE INDEX IF NOT EXISTS idxexporttrackingid
    ON public.exporttrackingdetails USING btree (exporttrackingid ASC NULLS LAST);

CREATE INDEX IF NOT EXISTS idxrefinedlistingid
    ON public.exporttrackingdetails USING btree (refinedlistingid ASC NULLS LAST);

CREATE INDEX IF NOT EXISTS idxexportedat
    ON public.exporttrackingdetails USING btree (exportedat DESC NULLS FIRST);

Required Migration (Add Missing Columns):

-- Rename exporttype column to filetype
ALTER TABLE public.exporttracking 
    RENAME COLUMN exporttype TO filetype;

-- Rename constraint
ALTER TABLE public.exporttracking 
    RENAME CONSTRAINT chkexporttype TO chkfiletype;

-- Add filename and filesize columns
ALTER TABLE public.exporttracking 
    ADD COLUMN IF NOT EXISTS filename VARCHAR(255),
    ADD COLUMN IF NOT EXISTS filesize BIGINT;

-- Create index on exportcode for fast lookup
CREATE INDEX IF NOT EXISTS idxexportcode 
    ON public.exporttracking USING btree (exportcode ASC NULLS LAST);

Data Models

ExportTracking

Stores comprehensive information about all export operations.

Fields:

exportid: Unique identifier for export (auto-generated UUID, primary key)
exportcode: Human-readable export code (e.g., "exp-001", TEXT, indexed)
dealerid: Reference to dealer (UUID, foreign key to dealermaster, indexed, NOT NULL)
filetype: File format type - 'CSV' or 'Excel' (VARCHAR(20), default: 'CSV')
status: Current status - 'Queued', 'InProgress', 'Completed', 'Failed' (VARCHAR(20), indexed, default: 'Queued')
totalrecords: Number of records included in the export (INTEGER, default: 0)
filtercriteria: JSON object storing the filter parameters with actual date ranges (e.g., PostedDateFrom/To, not enum values like "Last7Days") (JSONB)
filepath: Full file path on server (e.g., "/app/exports/{exportcode}.xlsx") (TEXT)
filename: Original filename for download (e.g., "export-001.xlsx") (VARCHAR(255), to be added)
filesize: File size in bytes for displaying "245 KB" in UI (BIGINT, to be added)
requestedat: Timestamp when export was requested (TIMESTAMPTZ, NOT NULL, default: now(), indexed DESC)
completedat: Timestamp when export completed (TIMESTAMPTZ, NULL until finished)
requestedby: User ID who requested the export (UUID, foreign key to userdetails, nullable)
errormessage: Error details if export failed (TEXT, nullable)
createdat: Record creation timestamp (TIMESTAMPTZ, NOT NULL, default: now())

ExportTrackingDetails

Stores individual vehicle listing records included in each export operation.

Fields:

trackingdetailsid: Unique identifier for tracking detail record (auto-generated UUID, primary key)
exporttrackingid: Reference to parent export record (UUID, foreign key to exporttracking, indexed, NOT NULL)
refinedlistingid: Reference to vehicle listing that was exported (UUID, foreign key to refinedvehiclelisting, indexed, NOT NULL)
exportedat: Timestamp when this specific record was exported (TIMESTAMPTZ, NOT NULL, default: now(), indexed DESC)

Purpose & Use Cases:

The ExportTrackingDetails table provides granular tracking of individual vehicle listings within each export operation. This enables:

Audit Trail: Complete history of which specific vehicle listings were exported and when
Duplicate Detection: Identify if a listing has been previously exported by querying exporttrackingdetails
Incremental Exports: Support for delta exports by excluding listings already exported (query by refinedlistingid)
Export Analysis: Analytics on most frequently exported listings, export patterns by dealer/source
Data Integrity: Maintain foreign key relationships to ensure data consistency
Compliance: Meet regulatory requirements for tracking data exports and downloads

Relationship Example:

ExportTracking (1) ─────< (Many) ExportTrackingDetails (Many) >───── (1) RefinedVehicleListing
    exportid                      exporttrackingid   refinedlistingid          refinedlistingid
    exp-001                       links to export     links to vehicle          Toyota Camry 2023
    187 records                   batch details       listing details           [listing data]

Request and Response Models

ExportTrackingStatus Enum

public enum ExportTrackingStatus
{
    Queued = 1,
    InProgress = 2,
    Completed = 3,
    Failed = 4
}

ExportHistoryFilter

public record ExportHistoryFilter
{
    public Guid? DealerId { get; init; }
    public ExportTrackingStatus? Status { get; init; }
    public string? SearchKeyword { get; init; }  // Search by export code or dealer name
    public DateTime? CreatedAtFrom { get; init; }
    public DateTime? CreatedAtTo { get; init; }
    public int PageNumber { get; init; } = 1;
    public int RowsPerPage { get; init; } = 10;
}

ExportHistorySummary

public record ExportHistorySummary
{
    public Guid ExportId { get; init; }
    public string ExportCode { get; init; } = string.Empty;
    public string DealerName { get; init; } = string.Empty;
    public ExportTrackingStatus Status { get; init; }
    public int Records { get; init; }
    public string Size { get; init; } = string.Empty;  // Formatted like "245 KB"
    public DateTime CreatedAt { get; init; }
    public string? ExportedBy { get; init; }  // User ID who requested the export
    public string? FileName { get; init; }
    public string? FilterCriteria { get; init; }  // JSONB string with actual date ranges (e.g., PostedDateFrom/To, not enum values)
}

ServerPaginatedData<T>

public class ServerPaginatedData<T>
{
    public List<T> Data { get; set; } = [];
    public int TotalNumber { get; set; }
    public bool HasPreviousPage { get; set; }
    public bool HasNextPage { get; set; }
    public int TotalPages { get; set; }
    public int PageNumber { get; set; }
    public int RowsPerPage { get; set; }
}

CommonResponse

public record CommonResponse
{
    public int Status { get; init; }
    public string? Message { get; init; }
}

CreateExportHistoryResponse

public record CreateExportHistoryResponse : CommonResponse
{
    public string? ExportCode { get; init; }  // Generated export code (e.g., "exp-001")
    public Guid? ExportId { get; init; }  // Export tracking ID
}

CreateExportHistoryRequest

Note: This model is used internally by the backend for processing and updating export records. The public API endpoint uses RefinedVehicleListingFilter instead.

public record CreateExportHistoryRequest
{
    public string? ExportCode { get; init; }  // Human-readable export code (e.g., "exp-001")
    public Guid? DealerId { get; init; } // it can be for 1 dealer or for all dealer
    public string FileType { get; init; } = "CSV";  // "CSV" or "Excel"
    public string Status { get; init; } = "Queued";  // "Queued", "InProgress", "Completed", "Failed"
    public int TotalRecords { get; init; } = 0;  // Number of records in the export
    public string? FilterCriteria { get; init; }  // JSONB string with actual date ranges (PostedDateFrom/To, CreatedAtFrom/To, etc.)
    public string? FilePath { get; init; }  // Full file path on server (e.g., "/app/exports/\{exportcode\}.xlsx")
    public string? FileName { get; init; }  // Original filename for download (e.g., "export-001.xlsx")
    public long? FileSize { get; init; }  // File size in bytes
    public Guid? RequestedBy { get; init; }  // User ID who requested the export
    public DateTime? CompletedAt { get; init; }  // Timestamp when export completed (null until finished)
    public string? ErrorMessage { get; init; }  // Error details if export failed
}

RefinedVehicleListingFilter

public class RefinedVehicleListingFilter
{
    public Guid? DealerId { get; set; }
    public Guid? SourceId { get; set; }
    public string? SearchKeyword { get; set; }  // Search across make, model, listing title, dealer name
    
    // Pagination
    public int PageNumber { get; set; } = 1;
    public int RowsPerPage { get; set; } = 10;
    
    // Date Range Filters
    public DateTime? PostedDateFrom { get; set; }  // Filter by posted date range start
    public DateTime? PostedDateTo { get; set; }    // Filter by posted date range end
    public DateTime? CreatedAtFrom { get; set; }   // Filter by created date range start
    public DateTime? CreatedAtTo { get; set; }     // Filter by created date range end
}

RefinedVehicleListingDetails

public class RefinedVehicleListingDetails
{
    [XLColumn(Ignore = true)]
    public Guid? RefinedListingId { get; set; }
    
    [XLColumn(Header = "Make", Order = 1)]
    public string? Make { get; set; }
    
    [XLColumn(Header = "Model", Order = 2)]
    public string? Model { get; set; }
    
    [XLColumn(Header = "Listing Title", Order = 3)]
    public string? ListingTitle { get; set; }
    
    [XLColumn(Header = "Description", Order = 4)]
    public string? Description { get; set; }
    
    [XLColumn(Header = "Mileage", Order = 5)]
    public string? Mileage { get; set; }
    
    [XLColumn(Header = "Price", Order = 6)]
    public string? Price { get; set; }
    
    [XLColumn(Header = "Images", Order = 7)]
    public string[]? Images { get; set; }
    
    [XLColumn(Header = "Posted Date", Order = 8)]
    public string? PostedDate { get; set; }
    
    [XLColumn(Header = "Year", Order = 9)]
    public string? Year { get; set; }
    
    [XLColumn(Header = "Location", Order = 10)]
    public string? Location { get; set; }
    
    [XLColumn(Header = "Source Name", Order = 11)]
    public string? SourceName { get; set; }
    
    [XLColumn(Header = "Dealer Name", Order = 12)]
    public string? DealerName { get; set; }
    
    [XLColumn(Header = "Dealer Code", Order = 13)]
    public string? DealerCode { get; set; }
    
    [XLColumn(Ignore = true)]
    public Guid? RawListingId { get; set; }
    
    [XLColumn(Ignore = true)]
    public Guid? EnrichmentJobId { get; set; }
    
    [XLColumn(Ignore = true)]
    public string? SellerType { get; set; }
    
    [XLColumn(Ignore = true)]
    public double? ConfidenceScore { get; set; }
    
    [XLColumn(Ignore = true)]
    public string[]? ClassificationReasons { get; set; }
    
    [XLColumn(Ignore = true)]
    public string[]? DealerIndicators { get; set; }
    
    [XLColumn(Ignore = true)]
    public bool? IsQualified { get; set; }
    
    [XLColumn(Ignore = true)]
    public string? Attributes { get; set; }
    
    [XLColumn(Ignore = true)]
    public DateTime? CreatedAt { get; set; }
    
    [XLColumn(Ignore = true)]
    public DateTime? UpdatedAt { get; set; }
}

API Interfaces

Endpoint	Method	Purpose	Parameters	Request Model	Response Model	Status Codes
`/records/export`	POST	Create new export history record	JWT in header	RefinedVehicleListingFilter	CreateExportHistoryResponse	201, 400, 401, 403, 500
`/records/export-history`	GET	Get paginated list of export history records	JWT in header, Query params (filter/paginate)	ExportHistoryFilter	`ServerPaginatedData<ExportHistorySummary>`	200, 400, 401, 403, 500
`/records`	GET	Get paginated refined vehicle listings	JWT in header, Query params (filter/paginate)	RefinedVehicleListingFilter	`ServerPaginatedData<RefinedVehicleListingDetails>`	200, 400, 401, 403, 404, 500
`/records/export/{exportId:guid}/file`	GET	Download export file	JWT in header (path)	-	File Stream (Excel/CSV/Image)	200, 401, 403, 404, 500
`/records/export/{exportId:guid}/retry`	POST	Retry a failed export	JWT in header, exportId (path)	-	CommonResponse	200, 400, 401, 403, 404, 500

API Examples

Create Export History

Request:

POST /export
Host: https://marketpulse.inworkglobal.com/api
Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...
Content-Type: application/json

{
  "dealerId": "550e8400-e29b-41d4-a716-446655440001",
  "sourceId": null,
  "searchKeyword": null,
  "pageNumber": 1,
  "rowsPerPage": 10,
  "postedDateFrom": "2026-02-01T00:00:00Z",
  "postedDateTo": "2026-02-28T23:59:59Z",
  "createdAtFrom": null,
  "createdAtTo": null
}

Success Response (201 Created):

{
  "status": 0,
  "message": "Export has been queued successfully",
  "exportCode": "exp-012",
  "exportId": "ff1a2b3c-4d5e-6f7a-8b9c-0d1e2f3a4b5c"
}

Error Response (400 Bad Request - Invalid Date Range):

{
  "status": -20006,
  "message": "Invalid filter: PostedDateFrom cannot be greater than PostedDateTo"
}

Error Response (401 Unauthorized):

{
  "status": -20009,
  "message": "Unauthorized: Invalid or expired token"
}

Error Response (403 Forbidden):

{
  "status": -20007,
  "message": "Access denied: Insufficient permissions to create export"
}

Error Response (500 Internal Server Error):

{
  "status": -16000,
  "message": "An unexpected error occurred while creating export"
}

Get Paginated Export History

Request:

GET /export-history?PageNumber=1&RowsPerPage=10&Status=3
Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...

Success Response (200 OK):

{
  "data": [
    {
      "exportId": "550e8400-e29b-41d4-a716-446655440001",
      "exportCode": "exp-001",
      "dealerName": "Sunset Auto Group",
      "status": 3,
      "records": 187,
      "size": "245 KB",
      "createdAt": "2026-02-16T09:30:00Z",
      "exportedBy": "John Smith",
      "fileName": "export-001.xlsx",
      "filterCriteria": "{\"PostedDateFrom\":\"2026-02-09T00:00:00Z\",\"PostedDateTo\":\"2026-02-16T23:59:59Z\",\"Status\":\"Active\"}"
    },
    {
      "exportId": "660f9500-f3ac-52e5-b827-557766551111",
      "exportCode": "exp-002",
      "dealerName": "Metro Honda Direct",
      "status": 3,
      "records": 42,
      "size": "58 KB",
      "createdAt": "2026-02-16T08:30:00Z",
      "exportedBy": "Stocksource Admin",
      "fileName": "export-002.csv",
      "filterCriteria": "{\"PostedDateFrom\":\"2026-02-01T00:00:00Z\",\"PostedDateTo\":\"2026-02-28T23:59:59Z\"}"
    },
    {
      "exportId": "770g0600-g4bd-63f6-c938-668877662222",
      "exportCode": "exp-003",
      "dealerName": "Bayshore BMW Specialists",
      "status": 3,
      "records": 63,
      "size": "89 KB",
      "createdAt": "2026-02-16T04:30:00Z",
      "exportedBy": "Michael Williams",
      "fileName": "export-003.xlsx",
      "filterCriteria": "{\"PostedDateFrom\":\"2026-01-17T00:00:00Z\",\"PostedDateTo\":\"2026-02-16T23:59:59Z\",\"SourceId\":\"abc123\"}"
    },
    {
      "exportId": "990i2800-i6df-85h8-e150-880099884444",
      "exportCode": "exp-005",
      "dealerName": "Golden Gate Auto",
      "status": 3,
      "records": 234,
      "size": "312 KB",
      "createdAt": "2026-02-16T01:30:00Z",
      "exportedBy": "David Miller",
      "fileName": "export-005.xlsx",
      "filterCriteria": null
    },
    {
      "exportId": "aa0j3900-j7eg-96i9-f261-991100995555",
      "exportCode": "exp-006",
      "dealerName": "Capital City Cars",
      "status": 2,
      "records": 0,
      "size": "--",
      "createdAt": "2026-02-16T11:30:00Z",
      "exportedBy": "Sarah Davis",
      "fileName": null,
      "filterCriteria": "{\"PostedDateFrom\":\"2026-02-16T00:00:00Z\",\"PostedDateTo\":\"2026-02-16T23:59:59Z\"}"
    },
    {
      "exportId": "bb0k4000-k8fh-07j0-g372-002211006666",
      "exportCode": "exp-007",
      "dealerName": "Rocky Mountain Motors",
      "status": 3,
      "records": 156,
      "size": "208 KB",
      "createdAt": "2026-02-15T23:30:00Z",
      "exportedBy": "Stocksource Admin",
      "fileName": "export-007.xlsx",
      "filterCriteria": "{\"SearchKeyword\":\"Toyota\"}"
    },
    {
      "exportId": "cc0l5100-l9gi-18k1-h483-113322117777",
      "exportCode": "exp-008",
      "dealerName": "Garden State Motors",
      "status": 1,
      "records": 0,
      "size": "--",
      "createdAt": "2026-02-16T11:35:00Z",
      "exportedBy": "Amanda Moore",
      "fileName": null,
      "filterCriteria": "{\"CreatedAtFrom\":\"2026-02-09T00:00:00Z\",\"CreatedAtTo\":\"2026-02-15T23:59:59Z\"}"
    },
    {
      "exportId": "dd0m6200-m0hj-29l2-i594-224433228888",
      "exportCode": "exp-009",
      "dealerName": "Sunset Auto Group",
      "status": 3,
      "records": 38,
      "size": "52 KB",
      "createdAt": "2026-02-15T21:00:00Z",
      "exportedBy": "Matthew Taylor",
      "fileName": "export-009.csv",
      "filterCriteria": "{\"PostedDateFrom\":\"2026-02-14T00:00:00Z\",\"PostedDateTo\":\"2026-02-14T23:59:59Z\"}"
    },
    {
      "exportId": "ee0n7300-n1ik-30m3-j605-335544339999",
      "exportCode": "exp-010",
      "dealerName": "Bayshore BMW Specialists",
      "status": 3,
      "records": 11,
      "size": "18 KB",
      "createdAt": "2026-02-16T06:30:00Z",
      "exportedBy": "Stocksource Admin",
      "fileName": "export-010.csv",
      "filterCriteria": "{\"PostedDateFrom\":\"2025-11-18T00:00:00Z\",\"PostedDateTo\":\"2026-02-16T23:59:59Z\",\"SourceId\":\"xyz789\"}"
    }
  ],
  "totalNumber": 87,
  "hasPreviousPage": false,
  "hasNextPage": true,
  "totalPages": 9,
  "pageNumber": 1,
  "rowsPerPage": 10
}

Success Response - No Results (200 OK):

{
  "data": [],
  "totalNumber": 0,
  "hasPreviousPage": false,
  "hasNextPage": false,
  "totalPages": 0,
  "pageNumber": 1,
  "rowsPerPage": 10
}

Error Response (400 Bad Request):

{
  "status": -20006,
  "message": "Invalid filter parameters: PageNumber must be greater than 0"
}

Error Response (401 Unauthorized):

{
  "status": -20009,
  "message": "Unauthorized: Invalid or expired token"
}

Error Response (403 Forbidden):

{
  "status": -20007,
  "message": "Access denied: Insufficient permissions"
}

Error Response (500 Internal Server Error):

{
  "status": -16000,
  "message": "An unexpected error occurred while retrieving export history"
}

Download Export File

Request:

GET /export/ee0n7300-n1ik-30m3-j605-335544339999/file
Host: https://marketpulse.inworkglobal.com/api
Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...

Success Response (200 OK):

Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Content-Disposition: attachment; filename="export-001.xlsx"
Content-Length: 250880

[Binary file content]

Error Response (404 Not Found):

{
  "status": -20001,
  "message": "Export file not found or has been deleted"
}

Error Response (403 Forbidden):

{
  "status": -20007,
  "message": "Access denied: You do not have permission to download this export"
}

Error Response (400 Bad Request - Export Not Completed):

{
  "status": -20006,
  "message": "Export is not available for download. Current status: InProgress"
}

Retry Failed Export

Request:

POST /export/880h1700-h5ce-74g7-d049-779988773333/retry
Host: https://marketpulse.inworkglobal.com/api
Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...

Success Response (200 OK):

{
  "status": 0,
  "message": "Export retry initiated successfully. Export status changed to Queued."
}

Error Response (400 Bad Request - Wrong Status):

{
  "status": -20006,
  "message": "Only failed exports can be retried. Current status: Completed"
}

Error Response (404 Not Found):

{
  "status": -20001,
  "message": "Export record not found"
}

Error Response (403 Forbidden):

{
  "status": -20007,
  "message": "Access denied: You do not have permission to retry this export"
}

Get Paginated Refined Vehicle Listings

Request - Basic Pagination:

GET /records?PageNumber=1&RowsPerPage=10&DealerId=550e8400-e29b-41d4-a716-446655440001
Host: https://marketpulse.inworkglobal.com/api
Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...

Request - With Posted Date Range:

GET /records?PageNumber=1&RowsPerPage=10&DealerId=550e8400-e29b-41d4-a716-446655440001&PostedDateFrom=2026-02-17T00:00:00Z&PostedDateTo=2026-03-19T23:59:59Z
Host: https://marketpulse.inworkglobal.com/api
Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...

Request - With Created Date Range:

GET /records?PageNumber=1&RowsPerPage=10&DealerId=550e8400-e29b-41d4-a716-446655440001&CreatedAtFrom=2026-01-01T00:00:00Z&CreatedAtTo=2026-03-19T23:59:59Z
Host: https://marketpulse.inworkglobal.com/api
Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...

Request - Search by Keyword with Date Filter:

GET /records?PageNumber=1&RowsPerPage=10&SearchKeyword=honda%20civic&PostedDateFrom=2026-03-01T00:00:00Z&PostedDateTo=2026-03-31T23:59:59Z
Host: https://marketpulse.inworkglobal.com/api
Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...

Success Response (200 OK):

{
  "data": [
    {
      "refinedListingId": "aa1b2c3d-4e5f-6a7b-8c9d-0e1f2a3b4c5d",
      "make": "Toyota",
      "model": "Camry",
      "listingTitle": "2023 Toyota Camry SE - Low Mileage",
      "description": "Excellent condition, one owner, full service history",
      "mileage": "12,500",
      "price": "$24,999",
      "images": [
        "https://example.com/images/1.jpg",
        "https://example.com/images/2.jpg"
      ],
      "postedDate": "2026-03-15T10:30:00Z",
      "year": "2023",
      "location": "Los Angeles, CA",
      "sourceName": "Cars.com",
      "dealerName": "Sunset Auto Group",
      "dealerCode": "SA001",
      "rawListingId": "bb2c3d4e-5f6a-7b8c-9d0e-1f2a3b4c5d6e",
      "enrichmentJobId": "cc3d4e5f-6a7b-8c9d-0e1f-2a3b4c5d6e7f",
      "sellerType": "Dealer",
      "confidenceScore": 0.95,
      "classificationReasons": ["Dealer name in listing", "Multiple inventory"],
      "dealerIndicators": ["Business phone", "Dealer license"],
      "isQualified": true,
      "attributes": "{\"transmission\": \"Automatic\", \"fuelType\": \"Gasoline\"}",
      "createdAt": "2026-03-15T10:35:00Z",
      "updatedAt": "2026-03-15T10:35:00Z"
    },
    {
      "refinedListingId": "dd4e5f6a-7b8c-9d0e-1f2a-3b4c5d6e7f8a",
      "make": "Honda",
      "model": "Accord",
      "listingTitle": "2022 Honda Accord Sport - Certified Pre-Owned",
      "description": "Clean title, factory warranty remaining",
      "mileage": "18,200",
      "price": "$22,500",
      "images": [
        "https://example.com/images/3.jpg"
      ],
      "postedDate": "2026-03-14T14:20:00Z",
      "year": "2022",
      "location": "San Diego, CA",
      "sourceName": "Autotrader",
      "dealerName": "Metro Honda Direct",
      "dealerCode": "MH002",
      "rawListingId": "ee5f6a7b-8c9d-0e1f-2a3b-4c5d6e7f8a9b",
      "enrichmentJobId": "ff6a7b8c-9d0e-1f2a-3b4c-5d6e7f8a9b0c",
      "sellerType": "Dealer",
      "confidenceScore": 0.92,
      "classificationReasons": ["CPO program", "Dealer branding"],
      "dealerIndicators": ["Multiple locations", "Professional photos"],
      "isQualified": true,
      "attributes": "{\"transmission\": \"CVT\", \"fuelType\": \"Gasoline\"}",
      "createdAt": "2026-03-14T14:25:00Z",
      "updatedAt": "2026-03-14T14:25:00Z"
    }
  ],
  "totalNumber": 47,
  "hasPreviousPage": false,
  "hasNextPage": true,
  "totalPages": 5,
  "pageNumber": 1,
  "rowsPerPage": 10
}

Success Response - No Results (200 OK):

{
  "data": [],
  "totalNumber": 0,
  "hasPreviousPage": false,
  "hasNextPage": false,
  "totalPages": 0,
  "pageNumber": 1,
  "rowsPerPage": 10
}

Error Response (400 Bad Request - Invalid Date Range):

{
  "status": -20006,
  "message": "Invalid filter: PostedDateFrom cannot be greater than PostedDateTo"
}

Error Response (401 Unauthorized):

{
  "status": -20009,
  "message": "Unauthorized: Invalid or expired token"
}

Error Response (403 Forbidden):

{
  "status": -20007,
  "message": "Access denied: Insufficient permissions to view refined listings"
}

Error Response (404 Not Found):

{
  "status": -20001,
  "message": "No refined listings found for the specified dealer"
}

Error Response (500 Internal Server Error):

{
  "status": -16000,
  "message": "An unexpected error occurred while retrieving refined listings"
}

Development Tasks & Estimates

Task ID	Task Description	Estimated Hours	Priority
EH-001	Create database migration script to add filename and filesize	1	High
EH-002	Create Contract models (DTOs, enums, filters) in ContractLibrary	3	High
EH-003	Implement ExportHistoryDAL with Dapper queries	4	High
EH-004	Implement ExportHistoryService with business logic	3	High
EH-005	Create ExportHistoryController with all API endpoints	4	High
EH-006	Implement export code generation utility	2	High
EH-007	Integrate export tracking with ResellerController	3	High
EH-008	Implement file download with streaming and access control	3	High
EH-009	Add DI registration in Program.cs	1	High
EH-010	Write unit tests for DAL layer	3	Medium
EH-011	Write unit tests for Service layer	3	Medium
EH-012	Write integration tests for API endpoints	4	Medium
EH-013	Test role-based access control and authorization	2	High
EH-014	Test file download functionality and headers	2	High
EH-015	Code review and refactoring	2	Medium
EH-016	Documentation and API examples	2	Low
RL-001	Create RefinedVehicleListingFilter model with date range fields	2	High
RL-002	Create RefinedVehicleListingDetails DTO with XLColumn attributes	2	High
RL-003	Implement RefinedVehicleListingDAL with date filtering	4	High
RL-004	Implement RefinedVehicleListingService with business logic	4	High
RL-005	Create RefinedListingsController with GET endpoint	3	High
RL-006	Implement keyword search across multiple fields	2	Medium
RL-007	Add validation for date range parameters	2	Medium
RL-008	Write unit tests for RefinedVehicleListingService	3	Medium
RL-009	Write integration tests for refined listings endpoint	4	Medium
RL-010	Test date range filtering functionality	2	High
RL-011	Test role-based access for refined listings	2	High
	Total	72 hours

Review & Approval

Reviewer(s):
- Sanket Mal
- Ribhu Gautam
Approval Date: [To be completed after reviews]

Additional Notes

Security Considerations

Validate user access to export records based on JWT claims (entity_id, user_type)
Dealers can only access their own exports (filter by dealerId from JWT)
Resellers can access all exports for dealers under their reseller account
Admins have full access to all exports
Validate file paths to prevent directory traversal attacks
Use secure file streaming to prevent memory exhaustion
Add rate limiting to download endpoints (e.g., 10 downloads per minute per user)
Sanitize export codes and filenames before file system operations
Log all export download attempts for audit purposes
Refined Listings Security:
- Enforce role-based access for refined listings (dealers see only their data)
- Validate DealerId parameter matches JWT claims for dealer users
- Sanitize search keywords to prevent SQL injection
- Validate date range inputs (FromDate must be <= ToDate)
- Implement rate limiting for search queries (e.g., 100 requests per minute)

Performance Considerations

Index on exportcode for fast lookup by export code
Composite index on (dealerid, requestedat DESC) for optimized dealer-specific queries
Index on status for filtering by export status
Use file streaming for downloads to prevent loading entire file into memory
Consider CDN or cloud storage (S3, Azure Blob) for production file storage
Implement pagination with efficient COUNT query optimization
Cache frequently accessed export metadata in Redis (optional)
Use LIMIT and OFFSET efficiently in Dapper queries
Refined Listings Performance:
- Composite indexes on refinedvehiclelisting table:
  - (dealerid, posteddate DESC) for dealer-specific date-sorted queries
  - (dealerid, createdat DESC) for creation date filtering
  - (sourceid, posteddate DESC) for source-specific queries
- Use query optimization for keyword search (full-text search or ILIKE with indexes)
- Limit maximum RowsPerPage to 100 to prevent performance degradation
- Consider materialized views for frequently accessed dealer summaries
- Use EXPLAIN ANALYZE to optimize complex filtered queries

File Storage Strategy

Local Filesystem (Recommended for MVP):

Store files in /app/exports/{dealerId}/{guid}.{extension} (e.g., /app/exports/550e8400.../ee0n7300-n1ik-30m3-j605-335544339999.xlsx)
Filename format: {guid}.{extension} where guid is the exportId and extension is xlsx, csv, jpg, etc.
Dealer-specific subdirectories for organization
File path stored in database for retrieval
Advantages: Simple, no external dependencies
Disadvantages: Not horizontally scalable, backup complexity

Cloud Storage (Future Enhancement):

Store files in S3, Azure Blob Storage, or Google Cloud Storage
Generate pre-signed URLs for secure downloads
Set expiration on pre-signed URLs (e.g., 1 hour)
Advantages: Scalable, reliable, built-in backup
Disadvantages: Additional cost, external dependency

Future Enhancements

Scheduled Exports: Allow users to schedule recurring exports (daily, weekly, monthly)
Export Templates: Save filter configurations as reusable templates
Custom Column Selection: Allow users to choose which columns to include in export
CSV Format Support: Add CSV export option alongside Excel
Email Delivery: Email export files to specified recipients when complete
Export Comparison: Compare two exports to see what changed

Validation Rules:

Date Range Validation:
- If PostedDateFrom is provided, PostedDateTo should also be provided (and vice versa)
- PostedDateFrom must be less than or equal to PostedDateTo
- If CreatedAtFrom is provided, CreatedAtTo should also be provided (and vice versa)
- CreatedAtFrom must be less than or equal to CreatedAtTo
- Maximum date range: 1 year (optional constraint to prevent performance issues)
Pagination Validation:
- PageNumber must be >= 1
- RowsPerPage must be between 1 and 100
- Default RowsPerPage to 10 if not specified
Filter Combinations:
- Posted date filters and created date filters can be applied simultaneously
- Date filters can be combined with dealer, source, and keyword filters
Search Keyword:
- Minimum length: 2 characters (optional)
- Maximum length: 100 characters
- Sanitize special characters to prevent SQL injection

Integration with Export Tracking:

When a refined listing export is performed:

Create an ExportTracking record with status = 'Queued'
Store the applied filter criteria in filtercriteria JSONB column with actual date values
For each exported listing, create an ExportTrackingDetails record linking exporttrackingid and refinedlistingid
Update totalrecords count and status = 'Completed' when export finishes
Store generated file info (filepath, filename, filesize) for download capability

FilterCriteria JSON Format:

Store filter parameters as JSON string with actual date ranges for better readability:

✅ Correct: {"PostedDateFrom":"2026-02-09T00:00:00Z","PostedDateTo":"2026-02-16T23:59:59Z","SearchKeyword":"Toyota"}
✅ Correct: {"CreatedAtFrom":"2026-01-01T00:00:00Z","CreatedAtTo":"2026-01-31T23:59:59Z","DealerId":"550e8400-e29b-41d4-a716-446655440001"}
✅ Correct: {"PostedDateFrom":"2026-03-01T00:00:00Z","PostedDateTo":"2026-03-19T23:59:59Z","SourceId":"abc123"}

Export History Management

Author(s)​

Last Updated Date​

SRS References​

Version History​

Feature Overview​

Key Features​

Export Types​

Export Status Flow​

Requirements​

Functional Requirements​

Non-Functional Requirements​

Database Schema​

ExportTracking Table​

ExportTrackingDetails Table​

Data Models​

ExportTracking​

ExportTrackingDetails​

Request and Response Models​

ExportTrackingStatus Enum​

ExportHistoryFilter​

ExportHistorySummary​

ServerPaginatedData<T>​

CommonResponse​

CreateExportHistoryResponse​

CreateExportHistoryRequest​

RefinedVehicleListingFilter​

RefinedVehicleListingDetails​

API Interfaces​

API Examples​

Create Export History​

Get Paginated Export History​

Download Export File​

Retry Failed Export​

Get Paginated Refined Vehicle Listings​

Development Tasks & Estimates​

Review & Approval​

Additional Notes​

Security Considerations​

Performance Considerations​

File Storage Strategy​

Future Enhancements​

Author(s)

Last Updated Date

SRS References

Version History

Feature Overview

Key Features

Export Types

Export Status Flow

Requirements

Functional Requirements

Non-Functional Requirements

Database Schema

ExportTracking Table

ExportTrackingDetails Table

Data Models

ExportTracking

ExportTrackingDetails

Request and Response Models

ExportTrackingStatus Enum

ExportHistoryFilter

ExportHistorySummary

ServerPaginatedData<T>

CommonResponse

CreateExportHistoryResponse

CreateExportHistoryRequest

RefinedVehicleListingFilter

RefinedVehicleListingDetails

API Interfaces

API Examples

Create Export History

Get Paginated Export History

Download Export File

Retry Failed Export

Get Paginated Refined Vehicle Listings

Development Tasks & Estimates

Review & Approval

Additional Notes

Security Considerations

Performance Considerations

File Storage Strategy

Future Enhancements