Skip to main content
Version: MuddVision

Dealer Onboard

Author(s)​

  • Yeasa Mondal

Last Updated Date​

2025-10-27

SRS References​

Version History​

VersionDateChangesAuthor
1.02025-10-27Initial draftYeasa Mondal

Feature Overview​

Objective:
Implement an automated dealer onboarding system for the MuddVision platform that runs as a daily background job. The system queries Salesforce for all dealers with muddVisionPlatform = true, validates their external service integrations (Workfront, Authenticom, Facebook Ads, etc.), and imports dealer records into the local database. This ensures the MuddVision platform maintains an up-to-date dealer registry synchronized with Salesforce without manual intervention.

Scope:

  • Daily scheduled background job using .NET IHostedService (BackgroundService)
  • MassTransit-based communication with Salesforce to fetch dealer records
  • Automated data synchronization (Upsert) using clientId as the unique key. New dealers are inserted, and existing dealers are updated.
  • Asynchronous verification of external service IDs (Workfront, Authenticom, Facebook Ads)
  • Transactional dealer and address record creation with primary address support
  • Per-service verification flags and timestamps for partial import support
  • Comprehensive logging, metrics, and alerting for job execution

Dependencies:

  • Salesforce: Source of dealer data; dealers with muddVisionPlatform = true
  • MassTransit: Message-based communication with Salesforce consumer
  • External Verification Services: Workfront, Authenticom, Facebook Ads (APIs pending)
  • Dealer Management Service: Hosts the background job
  • Database: DealerMaster table with external ID fields, Address table with primary address FK

Implementation Details​

Background Job Architecture​

The Dealer Onboard feature is implemented as a scheduled background job DailySalesforceOnboardJob within the Dealer Management Service using .NET IHostedService (BackgroundService pattern).

Job Configuration:

  • Schedule: Once daily (configurable via cron expression) ⏳ Pending: exact time and timezone
  • Batch Processing: Configurable batch size for processing dealers ⏳ Pending: batch size and concurrency limits
  • Retry Policy: Exponential backoff for transient failures
  • Execution Mode: Dry-run and production modes supported

Job Execution Flow​

Step 1: Fetch Dealers from Salesforce​

The job uses MassTransit to request all dealers from Salesforce where muddVisionPlatform = true.

MassTransit Request Contract:

public class GetSalesforceDealersRequest
{
    public string Filter { get; set; }  // SOQL filter: "muddVisionPlatform = true"
    public int? BatchSize { get; set; } // Optional batch size
}

public class GetSalesforceDealersResponse
{
    public MuddStatusCode StatusCode { get; set; }  // MuddStatusCode
    public List<AccountDto> Dealers { get; set; }
    public string Message { get; set; }
}

Step 2: Data Synchronization (Upsert Strategy)​

The job performs an "Upsert" operation (Update or Insert) to synchronize dealer data from Salesforce with the local database. This ensures that new dealers are added and existing dealers are updated with the latest information.

Synchronization Strategy:

  1. Query for Existing Dealer: For each dealer from Salesforce, the system first queries the DealerMaster table by clientId.
  2. Update or Insert:
    • If a dealer with the matching clientId is found, the existing record is updated with the new data from Salesforce.
    • If no matching dealer is found, a new record is inserted into the DealerMaster table.

Benefits of This Approach:

  • Data Consistency: Keeps the local database synchronized with Salesforce.
  • No Duplicates: Prevents the creation of duplicate records by using clientId as a unique identifier.
  • Efficiency: A single, clear workflow handles both new and existing dealers.

Synchronization Rule: Use clientId (muddId) as the unique key for all Upsert operations.

Step 3: Transactional Import​

Create dealer and address records atomically in a database transaction:

  1. Insert dealer record into DealerMaster table
  2. Insert address records into Address table
  3. Set PrimaryAddressId FK in DealerMaster pointing to primary address
  4. Commit transaction

Transaction Rules:

  • All inserts must succeed or rollback entirely
  • Primary address must be identified and linked
  • Set initial verification flags to false (unverified)

Step 4: Asynchronous Verification (Fire-and-forget)​

After successful import, trigger asynchronous verification of external service IDs:

Verification Process:

  • Call verification endpoints for each external service (Workfront, Authenticom, Facebook Ads, etc.)
  • Update verification flags and timestamps in DealerMaster table
  • Log verification results (success/failure)
  • Continue processing even if verification fails (partial import allowed)

Verification Result Model:


:::warning[Verification APIs Pending]
External verification endpoints are not yet implemented. The job will record unverified status and retry verification when APIs become available.
:::

---

**Upsert Handling Flow:**

For each dealer from Salesforce: │ ├─► Call: UpsertDealerAsync(dealerDto) │ │ │ ├─► Query database for existing dealer by clientId │ │ │ │ │ ├─► Found? │ │ │ ├─► Yes → Update existing record │ │ │ │ Log: "Successfully updated ClientId" │ │ │ │ Count as "updated" │ │ │ │ │ │ │ └─► No → Insert new record │ │ │ │ │ │ │ ├─► Success? │ │ │ │ ├─► Yes → Log: "Successfully imported ClientId" │ │ │ │ │ Count as "imported" │ │ │ │ │ │ │ │ │ └─► No → Log: "Failed to import ClientId: Error" │ │ │ │ Count as "failed" │ │ │ │ │ │ │ └─► Continue to next dealer │ │ │ │ │ └─► Continue to next dealer


### **MassTransit Communication Pattern**

┌──────────────────────────┐ │ DailySalesforceOnboardJob│ │ (IHostedService) │ └────────┬─────────────────┘ │ │ 1. Publish GetSalesforceDealersRequest │ (Filter: muddVisionPlatform = true) ▼ ┌──────────────────────────┐ │ MassTransit Bus │ └────────┬─────────────────┘ │ │ 2. Route to Salesforce consumer ▼ ┌──────────────────────────┐ │ Salesforce Consumer │ │ - Query SF via SOQL │ │ - Map to DTOs │ │ - Return dealer list │ └────────┬─────────────────┘ │ │ 3. Response: List<AccountDto> ▼ ┌──────────────────────────┐ │ DailySalesforceOnboardJob│ │ - Query for existing dealer│ │ - Update or Insert (Upsert)│ │ - Count & log results │ │ - Async verification │ └──────────────────────────┘


---

## **Database Schema**

### **DealerMaster Table**

Primary table for storing dealer information with external service IDs and verification flags.

| Column                  | Type         | Constraints                  | Description                                |
|-------------------------|--------------|------------------------------|--------------------------------------------|
| id                      | VARCHAR(50)  | PRIMARY KEY, AUTO-GENERATED  | Unique dealer identifier                   |
| name                    | VARCHAR(255) | NOT NULL                     | Dealer name                                |
| email                   | VARCHAR(255) | NOT NULL, UNIQUE             | Dealer email address                       |
| phone                   | VARCHAR(50)  | NOT NULL                     | Contact phone number                       |
| address                 | VARCHAR(50)  | NOT NULL                     | address                                    |
| billingAddress          | jsonB        |                              | address in json with city/state/etc        |
| clientid                | VARCHAR(100) | NOT NULL, UNIQUE             | Salesforce client ID (muddId)              |
| dvdId                   | VARCHAR(100) |                              | Authenticom dealer ID                      |
| accountOwnerId          | VARCHAR(100) |                              | Salesforce account owner ID                |
| workfrontId             | VARCHAR(100) |                              | Workfront integration ID                   |
| facebookAdId            | VARCHAR(100) |                              | Facebook Ads account ID                    |
| make                    | VARCHAR(100) |                              | Vehicle make                               |
| status                  | VARCHAR(50)  | NOT NULL                     | Dealer account status (Active/Inactive)    |
| source                  | VARCHAR(50)  | NOT NULL, DEFAULT 'Salesforce' | Source of dealer record                  |
| lastVerifiedAt          | DATETIME     | NULL                         | Last verification attempt timestamp        |
| importedAt              | DATETIME     | NOT NULL, DEFAULT NOW()      | Import timestamp from Salesforce           |
| createdAt               | DATETIME     | NOT NULL, DEFAULT NOW()      | Record creation timestamp                  |
| updatedAt               | DATETIME     | NOT NULL, DEFAULT NOW()      | Last update timestamp                      |

**Indexes:**
- PRIMARY KEY on `Id`
- UNIQUE INDEX on `clientId`
- UNIQUE INDEX on `email`
- INDEX on `status` (for filtering active dealers)
- INDEX on `importedAt` (for monitoring recent imports)
- INDEX on `isWorkfrontVerified, isAuthenticomVerified, isFacebookVerified` (for reporting unverified dealers)

---

## **Workflow**

The end-to-end workflow describes how different components interact throughout the dealer onboarding process:

### **5. Data Flow Summary**

Salesforce (Source) ↓ SOQL Query (muddVisionPlatform = true) MassTransit Bus ↓ GetSalesforceDealersResponse DailySalesforceOnboardJob ↓ Query for existing dealer by clientId Database (DealerMaster + Address tables) ↓ Found? Yes -> Update Record ↓ Found? No -> Insert Record ↓ Success: Fire-and-Forget External Verification Services (Workfront, Authenticom, Facebook) ↓ Verification Results Database (Update verification flags)


---

## **Development Tasks & Estimates**

| **No** | **Task Name**                                                | **Estimate (Hours)** | **Dependencies**       | **Notes**                                                   |
|--------|--------------------------------------------------------------|----------------------|------------------------|-------------------------------------------------------------|
| 1      | Build Salesforce consumer for account retrieval (MassTransit)| 4 hours             | None                   | API endpoint with filter `muddVisionPlatform = true`, SOQL query implementation |
| 2      | Database configuration - Create `DealerMaster`  tables       | 6 hours              | None                   | DDL scripts with UNIQUE constraints, indexes, FK relationships |
| 3      | Implement `DailySalesforceOnboardJob` (IHostedService)       | 6 hours              | Task 1, Task 2         | Background job with timer, MassTransit client, exception handling |
| 4      | Build dealer import logic with Upsert functionality          | 4 hours              | Task 2, Task 3         | `UpsertDealerAsync()`, transaction management, update/insert logic |
| 5      | Implement external service verification (Workfront)          |  -                   | Task 4                 | API client, `VerifyWorkfrontAsync()`, flag update logic     |
| 6      | Implement external service verification (Authenticom)        |   -                  | Task 4                 | API client, `VerifyAuthenticomAsync()`, flag update logic   |
| 7      | Implement external service verification (Facebook Ads)       |   -                  | Task 4                 | API client, `VerifyFacebookAsync()`, flag update logic      |
| 8      | Add comprehensive logging, metrics, and alerting             |   -                  | Task 3                 | Structured logging, Prometheus metrics, alert thresholds    |
| 9      | Unit testing (all components)                                |  8 hours             | Tasks 1-7              | Mock external services, test duplicate handling, transaction rollback |
| 10     | Integration testing (end-to-end job execution)               | 8 hours              | Tasks 1-7              | Test with live Salesforce sandbox, verify data integrity    |
| 12     | **Total**                                                    | **36 hours**         | -                      | Approximately 2 weeks for 1 developer                       |

---






## **Process Flow Diagram**

### **Daily Background Job Flow**

┌────────────────────────────────┐ │ Scheduler (IHostedService) │ │ DailySalesforceOnboardJob │ │ Runs once daily at 2 AM UTC │ └────────────┬───────────────────┘ │ │ 1. Timer triggers job execution ▼ ┌────────────────────────────────┐ │ Publish MassTransit Request │ │ GetSalesforceDealersRequest │ │ Filter: muddVisionPlatform │ │ = true │ └────────────┬───────────────────┘ │ │ 2. Route via MassTransit Bus ▼ ┌────────────────────────────────┐ │ Salesforce Consumer Service │ │ - Execute SOQL query │ │ - Fetch dealer records │ │ - Map to DTOs │ └────────────┬───────────────────┘ │ │ 3. Response: List<AccountDto> ▼ ┌────────────────────────────────┐ │ DailySalesforceOnboardJob │ │ Process each dealer │ └────────────┬───────────────────┘ │ │ For each dealer: ▼ ┌────────────────────────────────┐ │ Step 1: Upsert Dealer │ │ Call UpsertDealerAsync() │ └────────────┬───────────────────┘ │ │ Query by clientId ▼ ┌────────────────────────────────┐ │ Dealer Found in DB? │ └────────────┬───────────────────┘ │ ├─► Yes ──┐ │ ▼ │ ┌────────────────────────────┐ │ │ Update Existing Record │ │ │ Count as "updated" │ │ │ Log: "Updated ClientId" │ │ └────────────┬───────────────┘ │ │ └─► No ──────────┤ ▼ ┌────────────────────────────────┐ │ Insert New Record │ └────────┬───────────────────────┘ │ ├─► Success ──┐ │ ▼ │ ┌────────────────────────────┐ │ │ Count as "imported" │ │ │ Log: "Imported ClientId" │ │ └────────────┬───────────────┘ │ │ └─► Failure ──────────┐ ▼ ┌────────────────────────────┐ │ Count as "failed" │ │ Log: "Import failed" │ └────────────┬───────────────┘ │ │ All paths continue to next dealer ▼ ┌────────────────────────────────┐ │ Step 2: Async Verification │ │ (Only if imported) │ │ - Workfront.VerifyById() │ │ - Authenticom.VerifyById() │ │ - Facebook.VerifyById() │ └────────────┬───────────────────┘ │ │ Success ▼ ┌────────────────────────────────┐ │ Step 3: Fire-and-Forget │ │ Async Verification │ │ (Do not block import) │ └────────────┬───────────────────┘ │ │ Parallel calls ▼ ┌────────────────────────────────┐ │ External Service Verification│ │ - Workfront.VerifyById() │ │ - Authenticom.VerifyById() │ │ - Facebook.VerifyById() │ └────────────┬───────────────────┘ │ │ Collect results ▼ ┌────────────────────────────────┐ │ Update Verification Flags │ │ - isWorkfrontVerified │ │ - isAuthenticomVerified │ │ - isFacebookVerified │ │ - lastVerifiedAt │ └────────────┬───────────────────┘ │ ▼ ┌────────────────────────────────┐ │ Log verification results │ │ Continue to next dealer │ └────────────────────────────────┘

After all dealers processed: │ ▼ ┌────────────────────────────────┐ │ Job Completion Summary │ │ - Log metrics: │ │ • Imported count │ │ • Updated count │ │ • Failed count │ │ • Duration │ │ - Update health metrics │ │ - Send alerts if failures │ └────────────────────────────────┘


### **Error Handling Flow**

┌────────────────────────┐ │ Process Dealer │ └───────┬────────────────┘ │ ├─ Salesforce Error ──→ Log error, retry after backoff │ ├─ Deduplication Check Error ──→ Log warning, skip dealer │ ├─ Transaction Failure ──→ Rollback, log error, increment failed counter │ └─ Verification Failure ──→ Log warning, set flags to false, continue (partial import allowed)