Populate Engagement Events

The populateEngagementEvents function generates invoice events for work orders and opportunities based on their billing cadence, dates, and amount calculations. This document explains all calculation scenarios and how invoice events are created.

Overview

The function takes an engagement ID and generates invoice events (rows in the invoice_events table) that represent when invoices should be sent and how much they should be for. Each event includes:

• Invoice Date: When the invoice should be sent
• Due Date: When payment is due (invoice date + payableAfterDays)
• Amounts: Net amount, VAT amount, and gross amount
• Month Key: YYYYMM format for efficient querying

Function Flow

1. Fetch Engagement: Retrieves engagement details including dates, cadence, amount, and related entities
2. Determine Date Range: Calculates start and end dates, extending for recurring engagements
3. Calculate Invoice Dates: Generates invoice dates based on cadence type
4. Calculate Amounts: Determines net, VAT, and gross amounts for each invoice event
5. Delete Existing Events: Removes old events for this engagement
6. Create New Events: Inserts new invoice events into the database

Date Range Determination

Start Date

• Uses engagement.startMonth if available
• Falls back to current date if not set

End Date

• Uses engagement.endMonth if available
• For recurring engagements with lookAheadMonths parameter:
  • Extends to currentDate + lookAheadMonths if no end date or if end date is further out
• Defaults to startDate + 12 months if no end date is set

Invoice Cadence Types

The system supports six different invoice cadence types, each generating invoice events differently:

1. Monthly Cadence

Generates one invoice event per month from start date to end date.

Example:

• Start Date: 2024-01-01
• End Date: 2024-03-31
• Invoice Dates Generated:
  • 2024-01-01
  • 2024-02-01
  • 2024-03-01

Calculation:

current = startDate
while (current <= endDate) {
  invoiceDates.push(current)
  current = addMonthsSafe(current, 1)
}

2. Quarterly Cadence

Generates one invoice event every 3 months (quarterly).

Example:

• Start Date: 2024-01-01
• End Date: 2024-12-31
• Invoice Dates Generated:
  • 2024-01-01 (Q1)
  • 2024-04-01 (Q2)
  • 2024-07-01 (Q3)
  • 2024-10-01 (Q4)

Calculation:

current = startDate
while (current <= endDate) {
  invoiceDates.push(current)
  current = addMonthsSafe(current, 3)
}

3. Annual Cadence

Generates one invoice event every 12 months (annually).

Example:

• Start Date: 2024-01-01
• End Date: 2026-12-31
• Invoice Dates Generated:
  • 2024-01-01
  • 2025-01-01
  • 2026-01-01

Calculation:

current = startDate
while (current <= endDate) {
  invoiceDates.push(current)
  current = addMonthsSafe(current, 12)
}

4. Upfront Cadence

Generates a single invoice event on the start date.

Example:

• Start Date: 2024-01-01
• End Date: 2024-12-31
• Invoice Dates Generated:
  • 2024-01-01 (only)

Calculation:

invoiceDates.push(startDate)

5. On Completion Cadence

Generates a single invoice event on the end date.

Example:

• Start Date: 2024-01-01
• End Date: 2024-12-31
• Invoice Dates Generated:
  • 2024-12-31 (only)

Calculation:

invoiceDates.push(endDate)

6. Custom Cadence (with Milestones)

Generates invoice events based on engagement milestones. Each milestone defines:

• targetMonth: The month when the invoice should be sent
• amountPct: The percentage of the total engagement amount for this milestone

Example:

• Start Date: 2024-01-01
• End Date: 2024-06-30
• Milestones:
  • Milestone 1: targetMonth = 2024-02-01, amountPct = 30%
  • Milestone 2: targetMonth = 2024-04-01, amountPct = 40%
  • Milestone 3: targetMonth = 2024-06-01, amountPct = 30%
• Invoice Dates Generated:
  • 2024-02-01
  • 2024-04-01
  • 2024-06-01

Calculation:

milestones.forEach((milestone) => {
  if (milestone.targetMonth >= startDate && milestone.targetMonth <= endDate) {
    invoiceDates.push(milestone.targetMonth)
  }
})
invoiceDates.sort() // Sort chronologically

Amount Calculations

The system calculates three amounts for each invoice event:

• Net Amount: Base amount after partner fees
• VAT Amount: VAT calculated on net amount
• Gross Amount: Net amount + VAT amount

Base Amount Calculation

The base amount starts with the engagement amount and applies partner fees if a partner exists:

baseAmount = engagement.amount

// Apply collection fee (if partner exists)
if (partner.collectionFeePct) {
  baseAmount = baseAmount * (1 - partner.collectionFeePct / 100)
} else if (partner.collectionFee) {
  baseAmount = baseAmount - partner.collectionFee
}

// Apply service fee (if partner exists)
if (partner.serviceFeePct) {
  baseAmount = baseAmount * (1 - partner.serviceFeePct / 100)
} else if (partner.serviceFee) {
  baseAmount = baseAmount - partner.serviceFee
}

Example:

• Engagement Amount: $10,000
• Partner Collection Fee: 2% (percentage)
• Partner Service Fee: $500 (fixed)
• Calculation:
  1. After collection fee: $10,000 × (1 - 0.02) = $9,800
  2. After service fee: $9,800 - $500 = $9,300
  3. Final base amount: $9,300

VAT Rate Determination

The VAT rate is determined in the following priority order:

1. VAT Override: If engagement.vatOverride is true and engagement.vatRatePct is set
2. Engagement Tax Code: If engagement has a tax code with ratePct
3. Partner Tax Code: If engagement has a partner with a tax code
4. Client Tax Code: If engagement has a client with a tax code
5. Error: If none found, throws error

Standard Amount Calculation (Non-Milestone)

For engagements without milestones, the same amount is used for all invoice events:

netAmount = baseAmountAfterFees
vatAmount = netAmount × (vatRate / 100)
grossAmount = netAmount + vatAmount

Example:

• Base Amount After Fees: $10,000
• VAT Rate: 20%
• Calculation:
  • Net Amount: $10,000.00
  • VAT Amount: $10,000 × 0.20 = $2,000.00
  • Gross Amount: $10,000 + $2,000 = $12,000.00

Milestone Amount Calculation

For engagements with milestones, each invoice event uses the milestone's percentage:

milestoneNetAmount = (totalBaseAmountAfterFees × milestone.amountPct) / 100
milestoneVatAmount = milestoneNetAmount × (vatRate / 100)
milestoneGrossAmount = milestoneNetAmount + milestoneVatAmount

Example:

• Total Base Amount After Fees: $10,000
• Milestone 1: amountPct = 30%
• Milestone 2: amountPct = 40%
• Milestone 3: amountPct = 30%
• VAT Rate: 20%

Milestone 1 Calculation:

• Net Amount: ($10,000 × 30) / 100 = $3,000.00
• VAT Amount: $3,000 × 0.20 = $600.00
• Gross Amount: $3,000 + $600 = $3,600.00

Milestone 2 Calculation:

• Net Amount: ($10,000 × 40) / 100 = $4,000.00
• VAT Amount: $4,000 × 0.20 = $800.00
• Gross Amount: $4,000 + $800 = $4,800.00

Milestone 3 Calculation:

• Net Amount: ($10,000 × 30) / 100 = $3,000.00
• VAT Amount: $3,000 × 0.20 = $600.00
• Gross Amount: $3,000 + $600 = $3,600.00

Total: $10,000 net + $2,000 VAT = $12,000 gross (matches standard calculation)

Due Date Calculation

Each invoice event has a due date calculated as:

dueDate = invoiceDate + engagement.payableAfterDays

Example:

• Invoice Date: 2024-01-15
• Payable After Days: 30
• Due Date: 2024-02-14

Month Key Generation

Each invoice event gets a month key in YYYYMM format for efficient querying:

monthKey = year × 100 + month

Example:

• Date: 2024-03-15
• Month Key: 2024 × 100 + 3 = 202403

Likelihood Percentage

The likelihood percentage determines the probability of the invoice event occurring:

• Opportunities: Uses engagement.probabilityPct (0-100%)
• Work Orders: Always 100% (guaranteed)

Complete Example: Monthly Recurring Engagement

Engagement Details:

• Type: Work Order
• Amount: $12,000
• Start Date: 2024-01-01
• End Date: 2024-12-31
• Invoice Cadence: Monthly
• Payable After Days: 30
• VAT Rate: 20%
• No partner fees

Calculation Steps:

1. Date Range: 2024-01-01 to 2024-12-31
2. Invoice Dates: 12 dates (one per month)
3. Base Amount: $12,000 (no partner fees)
4. Amount Per Event: $12,000 / 12 = $1,000 net per event

Generated Events:

Complete Example: Custom Cadence with Milestones

Engagement Details:

• Type: Work Order
• Amount: $10,000
• Start Date: 2024-01-01
• End Date: 2024-06-30
• Invoice Cadence: Custom
• Payable After Days: 30
• VAT Rate: 20%
• Partner Collection Fee: 2%
• Milestones:
  • Milestone 1: 2024-02-01, 30%
  • Milestone 2: 2024-04-01, 40%
  • Milestone 3: 2024-06-01, 30%

Calculation Steps:

1. Date Range: 2024-01-01 to 2024-06-30
2. Base Amount After Fees:
   • $10,000 × (1 - 0.02) = $9,800
3. Invoice Dates: 3 dates (from milestones)
4. Amount Per Milestone:

Generated Events:

Verification:

• Total Net: $2,940 + $3,920 + $2,940 = $9,800 ✓
• Total VAT: $588 + $784 + $588 = $1,960 ✓
• Total Gross: $3,528 + $4,704 + $3,528 = $11,760 ✓

Recurring Engagements with Look-Ahead

For recurring engagements, the system extends the end date based on the lookAheadMonths parameter:

if (engagement.billing === "recurring" && lookAheadMonths) {
  lookAheadDate = currentDate + lookAheadMonths
  if (!endDate || endDate < lookAheadDate) {
    endDate = lookAheadDate
  }
}

Example:

• Current Date: 2024-06-01
• Start Date: 2024-01-01
• End Date: 2024-12-31
• Look-Ahead Months: 12
• New End Date: 2025-06-01 (extends to 12 months from current date)

This ensures recurring revenue forecasts always have enough future events.

Event Deletion and Recreation

Before creating new events, the function deletes all existing events for the engagement:

await db.delete(invoiceEvents).where(
  and(
    eq(invoiceEvents.sourceType, "work_order" | "opportunity"),
    eq(invoiceEvents.sourceId, engagementId)
  )
)

This ensures:

• No duplicate events
• Events reflect current engagement configuration
• Historical events are preserved (only events for this specific engagement are deleted)

Error Handling

The function throws errors in the following cases:

1. Engagement Not Found: If engagement ID doesn't exist
2. No Tax Code Found: If VAT rate cannot be determined and engagement requires VAT calculation

Related Functions

• calculateEngagementAmounts(): Calculates amounts for non-milestone engagements
• calculateMilestoneAmounts(): Calculates amounts for milestone-based engagements
• calculateBaseAmountAfterFees(): Applies partner fees to base amount
• getEngagementVatRate(): Determines VAT rate for engagement
• calculateInvoiceDates(): Generates invoice dates based on cadence