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:

   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