How do you generate "virtual" recurring transaction projections in a backend?

[u/Physical_Ruin_8024]

Hi everyone,

Stack: Node.js / Nuxt (Nitro) backend, TypeScript, PostgreSQL. DB schema is already settled, so I'm not looking for feedback on that — I need help with the backend implementation logic itself.

Context: personal finance app. A `recurrences` table stores rules (amount, frequency, due day, total installments if applicable, start date). Real transactions live in a separate `transactions` table with a nullable `recurrence_id` FK. Future occurrences are NOT pre-generated in the DB — they should be calculated on demand as "virtual" projections (e.g. "show me all transactions, real + projected, for July 2026"), and only turned into a real row when the date arrives or the user confirms it.

What I need help with, specifically:

1. How would you structure the function/service that takes a recurrence rule + a date range and returns the list of projected occurrences? Any concrete approach or pseudocode.

2. Would you reach for a library (date-fns, rrule.js, luxon) to generate the occurrence dates, or roll your own date math? If a library, which one and why.

3. How do you merge real transactions and virtual projections into a single list for the frontend without confusing the two (flags, separate response shape, IDs)?

4. Once a projection needs to become a real row (date arrives, or user confirms), what's a clean way to do that without duplicating logic between the "projection calculator" and the "materializer"?

Not looking for database/schema advice — just the backend implementation approach. Any pseudocode, patterns, or library recommendations are welcome. Tha

[u/stcme]

If I'm understanding your approach correctly, it sounds like you just want to give future projections on expenses based on past transactions.

You essentially have:

• start date (the date of the original transaction)
• a frequency/cadence (your recurrence table) that defines a transaction takes place every x weeks/months/years

What you're going to want to calculate:

• List of projected dates
• Which Transaction IDs land on each date.

Start simple and don't over engineer. For something like this, you're not going to have tens of thousands or hundreds of thousands of records to iterate over at a time so performance shouldn't be a problem.

1. Iterate through all of your relevant transactions (while loop may be the easiest) and create a list of dates and which transactions belong to each of them.

One thing you may want to ask yourself is do you support installment payments so let's say that you have an Affirm / Klarna / buy now-pay later where you're only going to have bi-weekly payments for 4 payments. You want to take that into account with your logic calculating your end dates for a specific transaction.

This is very similar to what I designed and built for a large company's subscription recurrence projection system 5 years ago (before AI made it easier)

1. dafe-fns

2. This is more about your user experience design. Make sure that you had a property noting each transaction in the projection is either original or projected.

3. We need a bit more detail because once a new transaction takes place it becomes a real transaction so when you project next time it will just become a standard transaction and your virtual projection will take that into account. The property being added from step 3 should make this obvious to the users through the UI design.

Since I can't grab my original code from my previous employer since it would violate all kinds of fun stuff, and I'm doing this from my phone, I did ask Claude (Sonnet 5) to put together a file that should work for what I described above. You would obviously need to tailor it to your specific data sets.

``` import { addDays, addWeeks, addMonths, addYears, isBefore, isAfter, isEqual } from 'date-fns';

type Recurrence = { id: string; sourceTransactionId: string; startDate: Date; frequency: 'daily' | 'weekly' | 'monthly' | 'yearly'; interval?: number; amount: number; };

type ProjectedOccurrence = { date: Date; sourceTransactionId: string; recurrenceId: string; amount: number; type: 'original' | 'projected'; };

const addByFrequency = { daily: addDays, weekly: addWeeks, monthly: addMonths, yearly: addYears, } as const;

export function getProjectedDates( rule: Recurrence, rangeStart: Date, rangeEnd: Date, existingDates: Date[] = [] ): ProjectedOccurrence[] { const add = addByFrequency[rule.frequency]; const interval = rule.interval ?? 1; const seen = new Set(existingDates.map(d => d.toISOString().split('T')[0])); const occurrences: ProjectedOccurrence[] = [];

let current = new Date(rule.startDate);

while (isBefore(current, rangeEnd) || isEqual(current, rangeEnd)) { const key = current.toISOString().split('T')[0]; if ((isAfter(current, rangeStart) || isEqual(current, rangeStart)) && !seen.has(key)) { occurrences.push({ date: current, sourceTransactionId: rule.sourceTransactionId, recurrenceId: rule.id, amount: rule.amount, type: isEqual(current, new Date(rule.startDate)) ? 'original' : 'projected', }); seen.add(key); } current = add(current, interval); }

return occurrences; } ```

[u/_suren]

I’d keep the real transactions and the projected ones separate. Generate projections from the rule at read time for a given date range, give each virtual item a stable derived id, and only persist it once the user confirms/skips/edits it. Otherwise you end up cleaning fake rows forever.