Version: MyBestDealNow

Live Auction Bid Feed System

Authors

Rishika Kumari

Last Updated Date

2025-12-13

SRS References

Version History

Version	Date	Changes	Author
1.2	2025-12-13	Added validation rules, error examples, edge cases, security, live polling example & enhanced testing	Rishika Kumari
1.1	2025-12-13	Updated InventoryId nullability & Polling SQL logic	Rishika Kumari
1.0	2025-12-13	Initial draft - Cursor-based feed with Live Logic	Rishika Kumari

Feature Overview

Objective:
Implement a real-time, paginated feed of bid activities (Place, Update, Withdraw) for a specific auction. The system enables dealers and admins to monitor bid actions as they happen. It utilizes cursor-based pagination to support both infinite scrolling (fetching historical data) and live polling (fetching real-time updates) without missing or duplicating records.

Scope:

Phase 1 (Current): Polling-based feed using cursor pagination.
Real-time Diff Calculation: Backend calculates LastBidAmount so the frontend can display increments/decrements.
Dynamic Context: Supports bids placed on specific Inventory items OR generic Vehicle Specifications.
Auction-level Filtering: Provides a global activity feed for a specific auction context.

Dependencies:

AuctionEngineService - For core bid logic and persistence.
InventoryService - For vehicle specification and inventory details.
PostgreSQL Database - For data persistence with UUID support.

Requirements

Functional Requirements

Feed Display Requirements
- Users must see a paginated list of all bid activities for a specific auction.
- Feed items must display:
  - Dealer Name (Company Name).
  - Action (Place/Update/Withdraw).
  - Bid Amount (Current).
  - Car Details (Make/Model from Inventory OR Vehicle Spec).
- Feed must include LastBidAmount (the amount before the current action) for diff calculations.
- Newest activities must appear at the top.
Pagination & Real-Time Requirements
- Scrolling Down (NextCursor): Users must be able to load older feed seamlessly.
- Live Updates (PreviousCursor): Frontend must be able to poll for only new records that occurred after the latest displayed item.
- Tie-Breaking: Pagination must be stable using (UpdatedTime, BidHistoryId) to handle identical timestamps.
Data Integrity
- If a bid is Placed: LastBidAmount must be 0.
- If a bid is Updated: LastBidAmount must reflect the previous value.
- If a bid is Withdrawn: The feed must capture the final state.
- If InventoryId is missing (Slot Bid), Make/Model must fallback to VehicleSpecification.

Non-Functional Requirements

Performance
- API response time must be under 300ms.
- Efficient Subquery logic for calculating LastBidAmount.
- Database indexing on (auctionid, updatedat DESC) and (bidid, updatedat DESC).
Usability
- Polling Interval: 3-5 seconds for live updates.
- Limit Parameter: Defaults to 20, adjustable for Mobile vs Desktop views.

Design Specifications

UI/UX Design

Feed Interface Logic:

Placement: "Best Motors placed a bid of $12,000 on Honda Civic"
Update: "Best Motors updated bid on Honda Civic (Increased by $500)"
- Logic: BidAmount - LastBidAmount
Withdraw: "Best Motors withdrew bid on Honda Civic"

Data Models

Database Schema & Query Logic

SQL Strategy: The query handles the "Either/Or" scenario for Vehicle details (Inventory vs Spec) using LEFT JOIN and COALESCE. It supports dynamic direction switching: Strictly Less Than (<) for scrolling down (Older) and Strictly Greater Than (>) for polling (Newer).

SELECT
    bh.historyid AS BidHistoryId,
    bh.action AS Action,
    bh.bidamount AS BidAmount,
    bh.updatedat AS UpdatedTime,

    -- 1. SUBQUERY FOR LAST BID AMOUNT
    -- Fetches the single record immediately preceding the current row
    COALESCE(
        (SELECT sub.bidamount
         FROM public.bidhistory sub
         WHERE sub.bidid = bh.bidid
           AND (sub.updatedat < bh.updatedat OR (sub.updatedat = bh.updatedat AND sub.historyid < bh.historyid))
         ORDER BY sub.updatedat DESC, sub.historyid DESC
         LIMIT 1),
    0) AS LastBidAmount,

    -- 2. DEALER DETAILS
    dm.dealerid AS DealerId,
    dm.dealercode AS DealerCode,
    dm.companyname AS DealerName,

    -- 3. SMART CAR DETAILS (Inventory Fallback Logic)
    b.inventoryid AS InventoryId,
    COALESCE(inv.make, vs.make) AS Make,
    COALESCE(inv.model, vs.model) AS Model

FROM
    public.bidhistory bh
JOIN
    public.bid b ON bh.bidid = b.bidid
JOIN
    public.dealermaster dm ON b.dealerid = dm.dealerid

-- 4. LEFT JOINS (To handle missing Inventory links)
LEFT JOIN
    public.inventorymaster inv ON b.inventoryid = inv.inventoryid
LEFT JOIN
    public.vehiclespecification vs ON b.vehiclespecificationid = vs.vehiclespecificationid

WHERE
    b.auctionid = @AuctionId

    -- 5. CURSOR PAGINATION LOGIC (Bi-Directional)
    AND (
        -- CASE A: Scrolling Down (Fetching Older History) - Default
        (@IsPolling = false AND (
            (bh.updatedat < @CursorTime) OR
            (bh.updatedat = @CursorTime AND bh.historyid < @CursorId)
        ))
        OR
        -- CASE B: Live Polling (Fetching Newer History)
        (@IsPolling = true AND (
            (bh.updatedat > @CursorTime) OR
            (bh.updatedat = @CursorTime AND bh.historyid > @CursorId)
        ))
        OR
        -- CASE C: Initial Load
        (@CursorTime IS NULL)
    )

ORDER BY
    bh.updatedat DESC, bh.historyid DESC
LIMIT @Limit

  // Request DTO
  public class BidFeedFilter
  {
      /// <summary>
      /// Mandatory. The unique identifier of the Auction context.
      /// </summary>
      public Guid AuctionId { get; set; }

      /// <summary>
      /// Optional. Default 20. Max records to return per request.
      /// </summary>
      public int Limit { get; set; } = 20;

      /// <summary>
      /// Optional. Used for Scrolling Down (Fetching OLDER feed).
      /// Value is obtained from the 'nextCursor' of the previous response.
      /// </summary>
      public string? NextCursor { get; set; }

      /// <summary>
      /// Optional. Used for Polling/Live Updates (Fetching NEWER feed).
      /// Value is obtained from the 'previousCursor' of the first response.
      /// </summary>
      public string? PreviousCursor { get; set; }
  }

  // Response DTO (Item)
  public record BidFeedItem
  {
      public Guid BidHistoryId { get; init; } // Tie-breaker ID
      public Guid BidId { get; init; }
      public string Action { get; init; } // "PLACE", "UPDATE", "WITHDRAW"

      // Amount Details
      public decimal BidAmount { get; init; }
      public decimal LastBidAmount { get; init; } // Derived via Subquery

      // Timing
      public DateTime UpdatedTime { get; init; }

      // Dealer Info
      public Guid DealerId { get; init; }
      public string DealerName { get; init; } // Mapped from CompanyName
      public string DealerCode { get; init; }

      // Vehicle Info
      public Guid? InventoryId { get; init; }
      public string Make { get; init; }       // Mapped via COALESCE
      public string Model { get; init; }      // Mapped via COALESCE
  }

  ## **C# Contract Models**

  // Response Wrapper
  public class CursorPaginatedData<T>
  {
      public List<T> Data { get; set; } = [];
      public string? NextCursor { get; set; }
      public string? PreviousCursor { get; set; }
      public bool HasNextPage { get; set; }
      public bool HasPreviousPage { get; set; }
      public int? TotalCount { get; set; }
      public int Limit { get; set; }
  }

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

API Interfaces

Endpoint	Method	Parameters	Response	Response Status Codes
/api/auction/bid-feed	GET	BidFeedFilter	`CursorPaginatedData<BidFeedItem>`	200, 400, 401, 500

Request Validation Rules

AuctionId: Must be a valid GUID and reference an existing auction
Limit: Must be between 1 and 100 (default: 20)
NextCursor & PreviousCursor: Mutually exclusive - only one can be provided
Cursor Format: Must be valid Base64-encoded string in format {timestamp}|{guid}

Authorization

Dealers can view bid feed for auctions they are participating in
Admins can view bid feed for any auction
Unauthorized access returns 401
Access to non-participating auction returns 403

API Request & Response Examples

1. Get Live Auction Feed (Initial Load)

Endpoint: GET /api/auction/bid-feed

Request Headers:

Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...

Query Parameters (BidFeedFilter): ?auctionId=8b2e9c7d-4f5a-4e3b-9c8d-7e6f5a4b3c2d&limit=20

Success Response (200 OK - CursorPaginatedData<BidFeedItem>):

{
  "data": [
    {
      "bidHistoryId": "3fa85f64-5717-4562-b3fc-2c963f66afa6",
      "bidId": "1a2b3c4d-5e6f-7g8h-9i0j-k1l2m3n4o5p6",
      "action": "UPDATE",
      "bidAmount": 15500.0,
      "lastBidAmount": 15000.0,
      "updatedTime": "2025-12-12T10:30:05.123Z",
      "dealerId": "9f8c65-...",
      "dealerName": "Auto World Ltd",
      "dealerCode": "D-105",
      "inventoryId": "77a85f...",
      "make": "Toyota",
      "model": "Camry"
    },
    {
      "bidHistoryId": "5af8f61b-7391-4984-bd21-b354efa78...",
      "bidId": "b15c1993-90fe-48f9-a313-0c10ab652...",
      "action": "PLACE",
      "bidAmount": 12000.0,
      "lastBidAmount": 0.0,
      "updatedTime": "2025-12-12T10:29:55.000Z",
      "dealerId": "2e4f6a...",
      "dealerName": "Best Motors",
      "dealerCode": "D-106",
      "inventoryId": "88b96c...",
      "make": "Honda",
      "model": "Civic"
    }
  ],
  "nextCursor": "MjAyNS0xMi0xMlQxMDoyOTo1NS4wMDBafDVhZjhmNjFiLTczOTEtNDk4NC1iZDIxLWIzNTRlZmE3OA==",
  "previousCursor": "MjAyNS0xMi0xMlQxMDozMDowNS4xMjNafDNmYTg1ZjY0LTU3MTctNDU2Mi1iM2ZjLTJjOTYzZjY2YWZhNg==",
  "hasNextPage": true,
  "hasPreviousPage": false,
  "totalCount": 450,
  "limit": 20
}

2. Get Older History (Scrolling Down)

Endpoint: GET /api/auction/bid-feed

Query Parameters: Uses nextCursor from the previous response.

?auctionId=8b2e9c7d-4f5a-4e3b-9c8d-7e6f5a4b3c2d&limit=20&nextCursor=MjAyNS0xMi0xMlQxMDoyOTo1NS4wMDBafDVhZjhmNjFiLTczOTEtNDk4NC1iZDIxLWIzNTRlZmE3OA==

Success Response (200 OK): Returns items OLDER than the cursor time.

{
  "data": [
    {
      "bidHistoryId": "11223344-5566-7788-99aa-bbccddeeff00",
      "bidId": "aabbccdd-1122-3344-5566-778899aabbcc",
      "action": "UPDATE",
      "bidAmount": 11500.0,
      "lastBidAmount": 11000.0,
      "updatedTime": "2025-12-12T10:28:00.000Z",
      "dealerId": "2e4f6a8c-...",
      "dealerName": "Best Motors",
      "dealerCode": "D-106",
      "inventoryId": null,
      "make": "Honda",
      "model": "Civic"
    }
  ],
  "nextCursor": "MjAyNS0xMi0xMlQxMDoyNzowMC4wMDBafDk5ODg3Nzc2LTY2NTUtNDQzMy0yMjExLTAwMDAwMDAwMDAwMA==",
  "previousCursor": "MjAyNS0xMi0xMlQxMDoyODowMC4wMDBafDExMjIzMzQ0LTU1NjYtNzc4OC05OWFhLWJiY2NkZGVlZmYwMA==",
  "hasNextPage": true,
  "hasPreviousPage": true,
  "totalCount": 450,
  "limit": 20
}

3. Live Polling (Fetching New Updates)

Endpoint: GET /api/auction/bid-feed

Query Parameters: Uses previousCursor from the initial/latest response.

?auctionId=8b2e9c7d-4f5a-4e3b-9c8d-7e6f5a4b3c2d&limit=20&previousCursor=MjAyNS0xMi0xMlQxMDozMDowNS4xMjNafDNmYTg1ZjY0LTU3MTctNDU2Mi1iM2ZjLTJjOTYzZjY2YWZhNg==

Success Response (200 OK): Returns NEW items that occurred AFTER the cursor time.

{
  "data": [
    {
      "bidHistoryId": "7f8e9d0c-1a2b-3c4d-5e6f-7a8b9c0d1e2f",
      "bidId": "6a5b4c3d-2e1f-0a9b-8c7d-6e5f4a3b2c1d",
      "action": "PLACE",
      "bidAmount": 16000.0,
      "lastBidAmount": 0.0,
      "updatedTime": "2025-12-12T10:30:45.678Z",
      "dealerId": "3d2e1f0a-...",
      "dealerName": "Premium Auto",
      "dealerCode": "D-107",
      "inventoryId": "99a88b77-...",
      "make": "BMW",
      "model": "X5"
    },
    {
      "bidHistoryId": "8a9b0c1d-2e3f-4a5b-6c7d-8e9f0a1b2c3d",
      "bidId": "1a2b3c4d-5e6f-7g8h-9i0j-k1l2m3n4o5p6",
      "action": "UPDATE",
      "bidAmount": 16000.0,
      "lastBidAmount": 15500.0,
      "updatedTime": "2025-12-12T10:30:30.456Z",
      "dealerId": "9f8c65-...",
      "dealerName": "Auto World Ltd",
      "dealerCode": "D-105",
      "inventoryId": "77a85f...",
      "make": "Toyota",
      "model": "Camry"
    }
  ],
  "nextCursor": null,
  "previousCursor": "MjAyNS0xMi0xMlQxMDozMDo0NS42NzhabDdmOGU5ZDBjLTFhMmItM2M0ZC01ZTZmLTdhOGI5YzBkMWUyZg==",
  "hasNextPage": false,
  "hasPreviousPage": true,
  "totalCount": 452,
  "limit": 20
}

Empty Polling Response (No New Data):

{
  "data": [],
  "nextCursor": null,
  "previousCursor": "MjAyNS0xMi0xMlQxMDozMDowNS4xMjNafDNmYTg1ZjY0LTU3MTctNDU2Mi1iM2ZjLTJjOTYzZjY2YWZhNg==",
  "hasNextPage": false,
  "hasPreviousPage": false,
  "totalCount": 450,
  "limit": 20
}

4. Error Response Examples

400 Bad Request - Invalid Parameters:

{
  "status": 400,
  "message": "Both NextCursor and PreviousCursor cannot be provided simultaneously."
}

400 Bad Request - Invalid Cursor:

{
  "status": 400,
  "message": "Invalid cursor format. Cursor may be expired or malformed."
}

400 Bad Request - Invalid Limit:

{
  "status": 400,
  "message": "Limit must be between 1 and 100."
}

401 Unauthorized:

{
  "status": 401,
  "message": "Authentication required. Please provide a valid bearer token."
}

403 Forbidden:

{
  "status": 403,
  "message": "Access denied. You do not have permission to view this auction."
}

404 Not Found:

{
  "status": 404,
  "message": "Auction not found with ID: 8b2e9c7d-4f5a-4e3b-9c8d-7e6f5a4b3c2d"
}

500 Internal Server Error:

{
  "status": 500,
  "message": "An unexpected error occurred while processing your request."
}

Edge Cases & Error Handling

1. Empty Auction (No Bids)

Scenario: User requests feed for an auction with no bid feed.

Response:

{
  "data": [],
  "nextCursor": null,
  "previousCursor": null,
  "hasNextPage": false,
  "hasPreviousPage": false,
  "totalCount": 0,
  "limit": 20
}

2. Invalid/Expired Cursor

Scenario: Cursor becomes invalid due to data purge or format change.

Handling:

Return 400 Bad Request with clear error message
Client should restart pagination from the beginning (without cursor)
Consider cursor versioning for format changes

3. Non-Existent Auction ID

Scenario: Request made with GUID that doesn't exist in database.

Handling:

Return 404 Not Found
Distinguish between "never existed" and "deleted" auctions

4. Concurrent Updates During Pagination

Scenario: New bids arrive while user is scrolling through feed.

Handling:

Cursor-based pagination ensures consistency
Historical pages remain stable
New items only appear when polling with previousCursor

5. Both Cursors Provided

Scenario: Client mistakenly sends both nextCursor and previousCursor.

Handling:

Return 400 Bad Request
Message: "Both NextCursor and PreviousCursor cannot be provided simultaneously."

6. Vehicle Data Missing (Deleted Inventory)

Scenario: Inventory or Vehicle Specification record deleted after bid placement.

Handling:

Make and Model return as empty strings or "Unknown"
Consider soft-delete strategy for reference data
Log warning for data integrity investigation

7. Dealer Deleted/Deactivated

Scenario: Dealer account removed but bid feed retained.

Handling:

Display generic name like "Deleted Dealer" or last known company name
Include dealerCode for traceability

Workflow & Technical Implementation Details

1. Cursor Generation Logic (Backend)

The nextCursor and previousCursor fields are opaque Base64 strings to the frontend. Internally, they store the Time and ID of the item to ensure precision.

Format:

Raw String: "{UpdatedTime}|{BidHistoryId}"

Example: 2025-12-13T14:30:00.1234567Z|a1b2c3d4-e5f6-7g8h-9i0j-k1l2m3n4o5p6

Encoding: Base64(UTF8_Bytes(RawString))

Usage:

NextCursor: Points to the Last Item (Oldest) in the current list. Used to fetch items older than this.

PreviousCursor: Points to the First Item (Newest) in the current list. Used to poll for items newer than this.

Polling Strategy (Frontend)
Initial Load: Call API with auctionId only.
- Store the previousCursor from the response (Points to Top Item).
- Render the initial list.
Live Polling (Every 3-5 seconds):
- Call API with auctionId AND previousCursor.
- Backend returns NEW items (if any).
- If data is not empty:
  - Prepend new items to the top of the UI list.
  - Update previousCursor to the new top item.
- If data is empty:
  - Do nothing (keep using the old previousCursor).

Development Tasks & Estimates

No.	Task Name	Estimate (Hours)	Dependencies
1	Database Indexing: Create indices on BidHistory and Bid tables	2 hours	None
2	DAL Implementation: Write SQL query with COALESCE & Subquery logic	6 hours	Task 1
3	Service Layer: Implement Cursor Encoding/Decoding logic	4 hours	Task 2
4	Controller: Create API endpoint and integrate filters	3 hours	Task 3
5	Unit Testing: Test Pagination, Tie-breaking, and Polling scenarios	5 hours	Task 4
6	Integration Testing: Verify Live Feed behavior with real data	4 hours	Task 5
Total	Grand Total	24 hours

Testing & Quality Assurance

Unit Test Scenarios:

Cursor Encoding/Decoding: Verify Base64 encoding and parsing logic
Validation Logic: Test limit ranges, cursor exclusivity, GUID validation
Authorization Rules: Verify dealer and admin access permissions

Integration Test Scenarios:

Standard Pagination: Verify Page 2 loads items older than Page 1 without duplicates.
Tie-Breaking: Create 2 bids at the exact same millisecond. Verify that scrolling does not skip the second bid.
Live Polling: Verify that passing PreviousCursor returns only newly inserted bids.
Data Integrity: Verify LastBidAmount is correct for the first bid (0) vs updated bids (Previous Amount).
Vehicle Context: Verify a bid placed with only VehicleSpecificationId correctly displays Make/Model (using the COALESCE fallback).
Empty Auction: Request feed for auction with no bids, verify graceful empty response.
Invalid Cursor: Pass malformed cursor, verify 400 error with appropriate message.
Non-Existent Auction: Request feed for invalid AuctionId, verify 404 response.
Mutual Exclusivity: Pass both NextCursor and PreviousCursor, verify 400 error.
Limit Boundaries: Test with limit=0, limit=1, limit=100, limit=101.
HasNextPage Logic: Fetch with exact page boundary, verify correct hasNextPage value.
Deleted Vehicle Data: Delete Inventory record, verify Make/Model fallback or graceful handling.
Authorization: Test dealer accessing non-participating auction, verify 403.
Concurrent Updates: Insert new bids during pagination, verify historical consistency.

Performance Test Scenarios:

Response Time: Verify <300ms response with 10,000+ bid feed records.
Index Effectiveness: Run EXPLAIN ANALYZE on query, verify index usage.
Polling Load: Simulate 100 concurrent clients polling every 3 seconds.
Cursor Overhead: Measure Base64 encoding/decoding performance impact.

Deployment Considerations

Configuration Changes

Database Migration:

Execute the following Index Creation scripts to ensure O(log N) performance for the feed:

CREATE INDEX idx_bid_auctionid ON public.bid(auctionid); CREATE INDEX idx_bidhistory_pagination ON public.bidhistory(updatedat DESC, historyid DESC); CREATE INDEX idx_bidhistory_lookup ON public.bidhistory(bidid, updatedat DESC, historyid DESC);

Live Auction Bid Feed System

Authors​

Last Updated Date​

SRS References​

Version History​

Feature Overview​

Requirements​

Functional Requirements​

Non-Functional Requirements​

Design Specifications​

UI/UX Design​

Data Models​

Database Schema & Query Logic​

API Interfaces​

Request Validation Rules​

Authorization​

API Request & Response Examples​

1. Get Live Auction Feed (Initial Load)​

2. Get Older History (Scrolling Down)​

3. Live Polling (Fetching New Updates)​

4. Error Response Examples​

Edge Cases & Error Handling​

1. Empty Auction (No Bids)​

2. Invalid/Expired Cursor​

3. Non-Existent Auction ID​

4. Concurrent Updates During Pagination​

5. Both Cursors Provided​

6. Vehicle Data Missing (Deleted Inventory)​

7. Dealer Deleted/Deactivated​

Workflow & Technical Implementation Details​

1. Cursor Generation Logic (Backend)​

Development Tasks & Estimates​

Testing & Quality Assurance​

Unit Test Scenarios:​

Integration Test Scenarios:​

Performance Test Scenarios:​

Deployment Considerations​

Configuration Changes​

Database Migration:​

Authors

Last Updated Date

SRS References

Version History

Feature Overview

Requirements

Functional Requirements

Non-Functional Requirements

Design Specifications

UI/UX Design

Data Models

Database Schema & Query Logic

API Interfaces

Request Validation Rules

Authorization

API Request & Response Examples

1. Get Live Auction Feed (Initial Load)

2. Get Older History (Scrolling Down)

3. Live Polling (Fetching New Updates)

4. Error Response Examples

Edge Cases & Error Handling

1. Empty Auction (No Bids)

2. Invalid/Expired Cursor

3. Non-Existent Auction ID

4. Concurrent Updates During Pagination

5. Both Cursors Provided

6. Vehicle Data Missing (Deleted Inventory)

7. Dealer Deleted/Deactivated

Workflow & Technical Implementation Details

1. Cursor Generation Logic (Backend)

Development Tasks & Estimates

Testing & Quality Assurance

Unit Test Scenarios:

Integration Test Scenarios:

Performance Test Scenarios:

Deployment Considerations

Configuration Changes

Database Migration: