How I Built It

I solved the circular dependency by making the authoring a loop. Set the rules for the nearest period. Run the model. Read the projected available cash it surfaces for the months ahead. Use that to author the next period's rules. Run again. Repeat, period by period, until the rules covered the full horizon out to the early 2030s. It was deliberately manual and iterative, and that was the point. Each pass fed the next with information that did not exist until the previous pass had run. Because every period got its own rules, a single allocation's evolution over time lives in the model as a series of time-bounded rules rather than one static figure.

Underneath the authoring loop is a three-stage executor, run in strict order.

Stage one builds the grid. A single script generates one row per modeled account per qualifying month, the skeleton the rest of the pipeline fills in. I designed it to be idempotent. It reads the rows that already exist, builds a set of composite keys, and only creates the pairs that are missing. A partial failure, or adding an account later, never corrupts the grid. Re-running only ever adds what is absent.

// build a set of composite keys for rows that already exist
const existingKeys = new Set(
  existingRows.map(
    (row) => `${row.monthId}|${row.accountType}|${row.accountId}`
  )
)

// queue only the month-account pairs that are not already present
const toCreate = []
for (const month of qualifyingMonths) {
  for (const account of modeledAccounts) {
    const key = `${month.id}|${account.type}|${account.id}`
    if (existingKeys.has(key)) continue // skip pairs that already exist
    toCreate.push(buildRow(month, account))
  }
}

// write in batches of 50 to respect the airtable api limit
for (let i = 0; i < toCreate.length; i += 50) {
  await table.createRecordsAsync(toCreate.slice(i, i + 50))
}

// build a set of composite keys for rows that already exist
const existingKeys = new Set(
  existingRows.map(
    (row) => `${row.monthId}|${row.accountType}|${row.accountId}`
  )
)

// queue only the month-account pairs that are not already present
const toCreate = []
for (const month of qualifyingMonths) {
  for (const account of modeledAccounts) {
    const key = `${month.id}|${account.type}|${account.id}`
    if (existingKeys.has(key)) continue // skip pairs that already exist
    toCreate.push(buildRow(month, account))
  }
}

// write in batches of 50 to respect the airtable api limit
for (let i = 0; i < toCreate.length; i += 50) {
  await table.createRecordsAsync(toCreate.slice(i, i + 50))
}

// build a set of composite keys for rows that already exist
const existingKeys = new Set(
  existingRows.map(
    (row) => `${row.monthId}|${row.accountType}|${row.accountId}`
  )
)

// queue only the month-account pairs that are not already present
const toCreate = []
for (const month of qualifyingMonths) {
  for (const account of modeledAccounts) {
    const key = `${month.id}|${account.type}|${account.id}`
    if (existingKeys.has(key)) continue // skip pairs that already exist
    toCreate.push(buildRow(month, account))
  }
}

// write in batches of 50 to respect the airtable api limit
for (let i = 0; i < toCreate.length; i += 50) {
  await table.createRecordsAsync(toCreate.slice(i, i + 50))
}

Stage two prices each row. The second script reads every active rule, indexes the rules by the account they target, and for each grid row sums the contributions from the rules whose effective window includes that month. The subtlety is frequency. A rule can fire one-time, bi-weekly, semi-monthly, monthly, quarterly, or annually, and all of them have to resolve to a single monthly-equivalent figure. Bi-weekly is the one that catches people. Twenty-six paychecks a year is not two a month. I annualize the cadence and re-spread it, so a bi-weekly rule contributes about 2.167 times its amount per month rather than two. Any single calendar month is an average, not a literal count.

// convert each cadence to a monthly-equivalent multiplier
const MONTHLY_FACTOR = {
  "One-Time": 1,        // applied only in the exact effective-from month
  "Bi-Weekly": 26 / 12, // 26 occurrences annualized, then spread monthly
  "Semi-Monthly": 2,
  "Monthly": 1,
  "Quarterly": 1 / 3,
  "Annually": 1 / 12,
}

// convert each cadence to a monthly-equivalent multiplier
const MONTHLY_FACTOR = {
  "One-Time": 1,        // applied only in the exact effective-from month
  "Bi-Weekly": 26 / 12, // 26 occurrences annualized, then spread monthly
  "Semi-Monthly": 2,
  "Monthly": 1,
  "Quarterly": 1 / 3,
  "Annually": 1 / 12,
}

// convert each cadence to a monthly-equivalent multiplier
const MONTHLY_FACTOR = {
  "One-Time": 1,        // applied only in the exact effective-from month
  "Bi-Weekly": 26 / 12, // 26 occurrences annualized, then spread monthly
  "Semi-Monthly": 2,
  "Monthly": 1,
  "Quarterly": 1 / 3,
  "Annually": 1 / 12,
}

Stage three chains the balances. The third script walks each account forward in time. It seeds a running balance from the account's synced starting value, then for every month sets the starting balance, applies growth or interest, and carries the ending balance into the next month. The math is per type. Cash and investment accounts treat a contribution as additive. Credit and debt accounts treat the same positive number as a payment that reduces the balance, so one rule means opposite things depending on what it targets. Growth rates needed careful parsing too, because the source stored them in two different formats, a decimal fraction in one table and a percentage string in another. The pipeline has to read each correctly or the projection silently drifts.

A few decisions held the whole thing together. The one-way sync kept the engine disposable and the source data safe. Gating generation to current and future months meant the model never wasted rows recomputing a settled past. A single flag on each account decided what entered the projection at all, which let closed accounts stay out of the forecast without erasing their history. And making every stage either idempotent or fully overwriting meant re-running the pipeline after any change was always safe, as long as the three stages ran in order.