Dealer Offboard

Author(s)

• Yeasa Mondal

Last Updated Date

2026-04-28

---

SRS References

---

Version History

Version	Date	Changes	Author
1.0	2026-04-28	Initial draft	Yeasa Mondal

---

Feature Overview

Objective:
Implement an automated dealer offboarding system for the MuddVision platform that works in conjunction with the dealer onboarding process. When dealers are disabled in Salesforce (where muddVisionPlatform = false or dealer is no longer present in sync), the system automatically marks them as disabled in the local database using a soft-delete mechanism. This ensures the MuddVision platform maintains data integrity while preventing access for offboarded dealers without losing historical data.

Scope:

• Soft offboarding mechanism using mudd_status column (active, disabled)
• Sync-based tracking using unique sync_id for each synchronization run
• Automated marking of dealers not present in latest sync as disabled
• Login blocking for disabled dealers through dealer_status check joining tblmudd_users and tbldealermaster on clientid
• Role-based access control (Administrators bypass offboarding checks)
• Preserves historical data (no hard deletes) and further daily sync for connected integration of disabled dealer
• Comprehensive logging, metrics, and alerting for offboarding operations

Dependencies:

• Salesforce: Source of truth for dealer status
• Dealer Onboard Job: Works in tandem with onboarding process
• Dealer Management Service: Hosts the offboarding logic
• Database: tbldealermaster table with mudd_status and sync_id columns
• Authentication Service: Cognito for authentication, DB for authorization
• User Management: tblmudd_users table for user-dealer mapping

---

Implementation Details

Soft Offboarding Architecture

The Dealer Offboard feature is implemented as part of the dealer synchronization process within the Dealer Management Service, extending the DailySalesforceOnboardJob with offboarding logic.

Key Concepts:

• Soft Delete: Instead of deleting dealer records, we mark them as disabled
• Sync Tracking: Each synchronization run is tagged with a unique sync_id
• Automatic Detection: Dealers missing from the latest sync are automatically offboarded
• Authorization Layer: Login blocking is enforced at the application level, not authentication

---

Database Schema Updates

tbldealermaster Table - New Columns

Column	Type	Constraints	Description
mudd_status	enum	NOT NULL, DEFAULT 'active'	mudd_dealer_status: active disabled
sync_id	uuid	NULL	Unique identifier for each sync run

Indexes:

• INDEX on mudd_status (for filtering active/disabled dealers)
• INDEX on sync_id (for identifying latest sync)

---

Offboarding Workflow

Step 1: Sync Initialization

At the start of each synchronization job:

1. Generate a unique sync_id (e.g., UUID or timestamp-based identifier)
2. This sync_id will be assigned to all dealers processed in the current sync run

public record DealerDataPageRequest
{
    public List<AccountDto> Dealers { get; init; } = [];
    public int PageNumber { get; init; }
    public bool IsLastPage { get; init; }
    public Guid SyncId { get; init; }
}

---

Step 2: Upsert with Active Status

During the dealer synchronization process (paginated):

For each dealer received from Salesforce:

1. Perform upsert operation (insert or update)
2. Set mudd_status = 'active' for all incoming dealers
3. Set sync_id = current_sync_id

Batchupsert

Key Points:

• All dealers present in Salesforce sync are marked active
• Each dealer gets the current sync_id stamped
• This happens for every page of the paginated response

---

Step 3: Offboard Missing Dealers (Critical Step)

After processing the final page (isLastPage == true) of dealers from Salesforce:

1. Identify all dealers in the database whose sync_id does NOT match the current sync_id
2. These are dealers that were not present in the latest Salesforce sync
3. Mark them as disabled

public async Task<int> DisableMissingDealersAsync(Guid syncId)
        {
            try
            {
                if (syncId == Guid.Empty)
                {
                    logger.LogError("DisableMissingDealersAsync called with EMPTY syncId. Aborting to prevent mass disable.");
                    return -1; 
                }

                string disableSql = @"
                    UPDATE public.tbldealermaster
                    SET mudd_status = 'disabled'
                    WHERE sync_id IS DISTINCT FROM @SyncId
                    AND mudd_status != 'disabled';";

                int disabledCount = await dapperExecutor.ExecuteAsync(disableSql, new { SyncId = syncId });

                logger.LogInformation(
                    "Disabled {DisabledCount} dealers that were not in sync_id {SyncId}",
                    disabledCount,
                    syncId);

                return disabledCount;
            }
            catch (Exception ex)
            {
                logger.LogError(ex, "Error disabling missing dealers for sync_id {SyncId}: {Message}", syncId, ex.Message);
                throw;
            }
        }
  

This ensures:

• Only currently valid dealers remain active
• Missing dealers are automatically offboarded
• No manual intervention required
• Historical data is preserved

---

Login Blocking for Disabled Dealers

Authorization Architecture

Login Flow with Offboarding Check

┌─────────────────────────┐
│ User Login Request      │
└──────────┬──────────────┘
           │
           │ 1. Authenticate via Cognito
           ▼
┌─────────────────────────┐
│ Cognito Authentication  │
│ - Verify credentials    │
│ - Return JWT token      │
└──────────┬──────────────┘
           │
           │ 2. Authentication successful
           ▼
┌─────────────────────────┐
│ Check Dealer Status     │
│ JOIN tbldealermaster    │
│ ON client_id            │
└──────────┬──────────────┘
           │
           │ 3. Authorization check
           ▼
┌─────────────────────────┐
│ Is user Administrator?  │
└──────────┬──────────────┘
           │
    ┌──────┴──────┐
    │             │
   Yes           No
    │             │
    ▼             ▼
  ✅ Allow   ┌────────────────┐
             │ Check mudd_status│
             └────────┬────────┘
                      │
               ┌──────┴──────┐
               │             │
            active       disabled
               │             │
               ▼             ▼
            ✅ Allow      ❌ Block (403)

---

Key Benefits

Benefit	Description
✅ No Hard Deletes	Historical data is preserved for auditing and compliance
✅ Fully Automated	No manual intervention required; offboarding happens during sync
✅ Safe & Auditable	All status changes are logged with timestamps
✅ No Data Loss	Disabled dealers can be re-enabled if they return to Salesforce
✅ Clean Separation of Concerns	Cognito handles authentication, database handles authorization
✅ Real-time Enforcement	Login blocking is immediate after offboarding
✅ Sync Tracking	sync_id provides complete audit trail of synchronization runs

---

Development Tasks & Estimates

No	Task Name	Estimate (Hours)	Dependencies	Notes
1	Add mudd_status, sync_id columns to tbldealermaster	2 hours	None	Migration script with indexes
2	Implement sync_id generation and tracking in onboard job	3 hours	Task 1	Update DailySalesforceOnboardJob with SyncContext
3	Update upsert logic to set mudd_status = 'active' and sync_id	2 hours	Task 2	Modify UpsertDealerAsync() method
4	Implement OffboardMissingDealersAsync() method	4 hours	Task 2, Task 3	Query and update dealers not in current sync
5	Integrate offboarding into job completion workflow	2 hours	Task 4	Call offboard method after final page
6	Implement authorization layer for login blocking	5 hours	Task 1	Create authorization service with dealer status check
7	Update login endpoint with authorization checks	3 hours	Task 6	Integrate authorization service into login flow
8	Add comprehensive logging for offboarding operations	2 hours	Task 4, Task 5	Log sync_id, offboarded count, dealer IDs
9	Create monitoring dashboard for offboarding metrics	4 hours	Task 8	Track active/disabled counts, offboarding trends
10	Unit testing (offboarding logic and authorization)	6 hours	Tasks 3-7	Mock sync scenarios, test status transitions
11	Integration testing (end-to-end sync with offboarding)	6 hours	Tasks 1-9	Test with Salesforce sandbox, verify login blocking
12	Documentation and runbook for offboarding operations	2 hours	All tasks	Document edge cases, troubleshooting steps
13	Total	41 hours	-	Approximately 2 weeks for 1 developer

---

Edge Cases & Considerations

1. Dealer Re-activation

Scenario: A dealer is offboarded but later reappears in Salesforce sync.

Behavior:

• During upsert, mudd_status is set back to active
• sync_id is updated to current sync
• User access is automatically restored

Code Behavior: Existing upsert logic handles this automatically.

---

2. Partial Sync Failure

Scenario: Job fails mid-sync (e.g., after processing 3 of 5 pages).

Behavior:

• Do NOT run offboarding logic if sync is incomplete
• Only offboard after successful processing of all pages
• Use transaction or completion flag to ensure atomicity

Protection:

if (request.IsLastPage && request.PageNumber > 0)
{
    disabledCount = await dealerDAL.DisableMissingDealersAsync(request.SyncId);
}

---

3. User with No Dealer Mapping and is not an admin

Scenario: User exists in tblmudd_users but has no client_id (orphaned user), also not an admin.

Behavior:

• Block login - considering disabled user

---

One-Line Summary

🔥 Dealers not present in the latest Salesforce sync are automatically soft-disabled via mudd_status, and any user mapped to a disabled dealer is blocked from logging in through an authorization layer check.

---