# 19.01 Update Lead Sales Ledger

## Key Facts

* **Job Name:** `update_lead_sales_ledger`
* **Cron Expression:** `5 0 * * *`
* **Frequency:** Daily at 12:05 AM UTC
* **Owner/Team:** Data Processing Team
* **Last Modified:** 2025-02-12
* **Dependencies:** PostgreSQL (billing\_db), Internal API (heartbeat)

***

## 1. Overview

### Purpose

This cron job processes mortgage lead sales data and logs them into the `lead_sales_ledger` table in the `billing_db`. It ensures that each lead is correctly priced based on historical product pricing and any applicable broker discounts.

### Business Impact

This job is critical for tracking lead sales and generating accurate billing records. It ensures that all mortgage brokers are charged based on the pricing plans they were subscribed to at the time the lead was delivered.

***

## 2. Detailed Description

### Workflow

1. **Fetch Leads**
   * Queries mortgage leads from various lead type tables.
   * Ensures that only leads from the past 370 days and not earlier than Jan 1, 2025, are processed.
   * Can be safely re-run without creating duplicate records or altering previously processed data.
2. **Calculate Pricing**
   * Determines the product MSRP (standard price) based on historical pricing at the time of lead delivery.
   * Applies any applicable discounts based on the mortgage broker's subscription plan.
   * Calculates the final billed amount.
   * Ensures that each lead’s price is determined based on its recorded delivery date rather than the current pricing.
3. **Insert into `lead_sales_ledger`**
   * Inserts processed leads into the ledger while maintaining **idempotency**.
   * Skips leads that are already recorded to prevent duplication.
   * Can rebuild the ledger from scratch without causing inconsistencies in historical data.
4. **Log a Heartbeat**
   * Sends a heartbeat request to the internal API (`/heartbeat`) upon successful execution.
   * Provides a monitoring mechanism to ensure the cron job runs as expected each day.

***

## 3. Execution Schedule & Environment

* **Cron Schedule:** `5 0 * * *` (Runs daily at 12:05 AM UTC)
* **Server:** Digital Ocean droplet running Ubuntu
* **Environment Variables:**
  * `BILLING_DB_NAME`
  * `BILLING_DB_USER`
  * `BILLING_DB_PASSWORD`
  * `BILLING_DB_HOST`
  * `BILLING_DB_PORT`
  * `INTERNAL_API_URL`
  * `INTERNAL_API_KEY`

***

## 4. Configuration & Parameters

### Configuration Files:

* Environment variables are stored globally on the server.
* The cron job runs inside a virtual environment managed by `run_cron.sh`.

### Parameters:

* **Lead Types:** The job processes leads for `professional`, `construction`, `hard_money`, and `land`.
* **Historical Pricing:** The script dynamically pulls product pricing at the time of lead delivery.

***

## 5. Dependencies & Integration Points

### Database Dependencies:

* **Reads from:**
  * `mortgage_broker_filter_to_mortgage_lead`
  * `construction_loan_lead_filter_to_construction_loan_lead`
  * `hard_money_lead_filter_to_hard_money_lead`
  * `land_lead_filter_to_land_lead`
  * `product_price`
  * `mortgage_broker_subscription_plan`
* **Writes to:**
  * `lead_sales_ledger`

### API Dependencies:

* **Internal API (`api.wealthington.co/heartbeat`)**
  * Receives a heartbeat notification upon successful execution.

***

## 6. Error Handling & Logging

### Error Handling:

* The script catches database errors and logs them before exiting.
* API failures (heartbeat logs) are logged but do not halt execution.

### Logging:

* Log file: `logs/update_lead_sales_ledger.log`
* Logs include:
  * Lead processing status (inserted/skipped)
  * Database errors
  * API response status for heartbeat requests

### Monitoring:

* The heartbeat is logged to the internal API and can be monitored in the `monitoring.heartbeat` table.

***

## 7. Troubleshooting & Maintenance

### Common Issues:

| Issue                         | Possible Cause                  | Resolution                                    |
| ----------------------------- | ------------------------------- | --------------------------------------------- |
| Job fails to execute          | Environment variables missing   | Ensure `.env` variables are correctly set     |
| Leads are missing from ledger | Query filter incorrect          | Validate lead query conditions                |
| Heartbeat logging failure     | API downtime or bad credentials | Check API logs and confirm `INTERNAL_API_KEY` |

### Diagnostic Steps:

1. Check the log file (`logs/update_lead_sales_ledger.log`).
2. Manually run the script:

   ```bash
   cd /home/josh/wealthington-services/processing-layer/jobs/01-update-lead-sales-ledger
   source venv/bin/activate
   python main.py
   ```

***

## 8. Updating or Modifying the Cron Job

### Versioning:

* All changes must be committed to Git (`wealthington-services` repo).

### Testing:

* Before deployment, test the script in a development environment.

### Deployment:

1. Make necessary changes in the script.
2. Commit and push the changes.
3. SSH into the server and pull the latest version.

***

## 9. Additional Notes

* **Historical Context:**
  * Originally, the script used only active subscription plans. Now, it references historical pricing for accuracy.
* **Future Enhancements:**
  * Implementing retry logic for heartbeat logging failures.

***

## 10. Contact Information

* **Primary Contact:** Josh Holt