Transaction Architecture in Aivinn: Normal, Recurring, and AI-Driven

Table of Contents

1. Part 1 — Normal Transactions: Schema to UI
2. Part 2 — How a Transaction Impacts the Rest of the App
3. Part 3 — Recurring Transactions: Schema to Cron
4. Part 4 — Designing the AI Tools

Part 1 — Normal Transactions: Schema to UI

Schema

A transaction is wallet-scoped, not user-scoped — Aivinn wallets can be shared by multiple people, so every calculation groups by wallet_id. user_id is stored purely as audit metadata (who logged it), never used to filter or sum anything:

Loading code snippet…

The two composite indexes at the bottom aren't incidental — they mirror the exact WHERE + GROUP BY shape used by the two heaviest queries (list-by-date-range, and the raw-SQL summary aggregation below), rather than indexing columns individually and hoping the query planner combines them well.

CRUD

Route → Controller → Service, the pattern used consistently across the app:

Loading code snippet…

Every controller action calls checkWalletAccess(userId, walletId) before touching data — access control lives at the controller boundary, not scattered through service logic.

Create applies the sign convention once, at the write boundary, so nothing downstream has to re-derive it:

Loading code snippet…

Read (list) joins against an in-memory category cache rather than a live SQL join per row — getCategoryMap() is fetched once, in parallel with the DB query, and mapped onto results afterward:

Loading code snippet…

Pagination is cursor-based (take: limit + 1, then popping the extra row to detect nextCursor) rather than offset-based, avoiding the classic "page 50 shifts because someone deleted a row on page 3" problem.

Read (summary) uses raw SQL rather than Prisma's query builder, since a single SUM(CASE WHEN ...) aggregation is both clearer and faster than pulling every row into Node and reducing client-side:

Loading code snippet…

Update and delete both invalidate the wallet's cached context (clearUserContext) in a finally block, so a stale cache never outlives a mutation regardless of whether the mutation itself succeeded or the side-effects afterward failed.

UI Display

The transaction modal is a single component serving both ADD and UPDATE modes, switched by a mode prop rather than two separate components — category picker, amount input, and date picker are shared, with mode-specific behavior (prefill, "hasChanged" gating on the submit button) branching inside:

Loading code snippet…

The list screen (transactions.tsx) renders a FlatList fed by an infinite-scroll hook (useFetchTransactionsApi, built on useInfiniteQuery), flattening paginated pages into one array:

Loading code snippet…

The calendar screen (react-native-calendars) renders a custom dayComponent, checking a Set of transaction dates (built once via useMemo from the fetched date list) against each rendered day — an O(1) lookup per day cell rather than filtering the full transaction array 30+ times per month render.

Part 2 — How a Transaction Impacts the Rest of the App

A createTransaction call doesn't just insert a row — it fires four downstream recalculations. Each is described only briefly here, since they're systems with their own separate design history:

• Wellness score — a composite 60–100 number built from five weighted components (Habit, Budget, Income Stability, Goal, Challenge). A TRANSACTION trigger recomputes four of the five.
• Challenges — behavioral challenge progress (e.g. "avoid dining out this week") evaluated against the specific category, amount, and type of the transaction just logged.
• Budget threshold checks — compares new cumulative spend against the active budget for the current payday-based cycle, can fire a warning notification on crossing a threshold.
• Goals — specifically BUDGET_LIMIT-type goals, re-evaluated against the category's spend for the current cycle.

The performance decision: Promise.all, not sequential awaits

All four fire concurrently, and — just as importantly — the transaction write itself doesn't wait for any of them:

Loading code snippet…

Two separate Promise.all calls doing two different jobs:

1. The first — creating the row, fetching the category map, and fetching wallet payday — genuinely need to happen before the function can return anything meaningful, so they're awaited together rather than three sequential round-trips.
2. The second — the four side effects — are deliberately not awaited by the caller at all. They're fired, wrapped in their own Promise.all().catch(), and left to resolve independently. The HTTP response returns to the user the moment the transaction itself is written, without waiting for wellness score math or challenge evaluation to finish.

This matters for perceived speed: a user tapping "Add Transaction" sees the modal close and the list update immediately, while four unrelated recalculations finish silently a few hundred milliseconds later in the background. Running them in Promise.all rather than sequential awaits also means the total side-effect time is bounded by the slowest of the four, not the sum of all four — meaningful when each involves its own DB round-trip.

The one deliberate constraint on this pattern: every side-effect promise is guarded independently, wrapped in a single outer .catch() that only logs — a failure in, say, challenge evaluation, never surfaces as an error to the user or rolls back the transaction that already succeeded. The transaction write is the source of truth; everything downstream is best-effort.

Part 3 — Recurring Transactions: Schema to Cron

Schema

Recurring and one-time future transactions share one table, distinguished by a boolean rather than living in separate schemas:

Loading code snippet…

Crucially, this table only ever stores one row per rule — the next occurrence — never one row per future month. scheduled_transaction_id on transactions is nullable with ON DELETE SET NULL: deleting a rule must never delete the real historical transactions it already generated, only sever the link.

Why one table, and why one row

The feature started as "recurring transactions only." It broadened after a design review surfaced a real gap: if only recurring future transactions were held back from appearing as real transactions, a user could fabricate a large one-time future expense, watch safe-to-spend drop, then delete it before it was ever due — manipulating the number with no trace. The fix was to unify the rule: any future-dated transaction is held, recurring or not. A one-time future transaction became, structurally, just a recurring rule that never reschedules after firing once.

Storing only the next occurrence (rather than pre-generating rows for every future month) mirrors how calendar apps handle recurring events generally: compute what's needed for whatever range is being viewed, on demand, rather than materializing a rule's entire future in advance.

CRUD

The scheduled_transactions table has no standalone HTTP routes. Every operation is invoked internally by transactionsService, which decides — based on a transaction's date — whether an action belongs to the transactions table or the scheduled_transactions table:

Loading code snippet…

• Create, future date → row goes into scheduled_transactions only.
• Create, today/past date → row goes into transactions; if recurring, a second scheduled_transactions row is created for the next occurrence, stamped last_materialized_at = today so it isn't double-reserved.
• Update, date moved into the future → the real transactions row is deleted, a scheduled_transactions row created in its place (logged → pending conversion).
• Update, date moved into today/past → the reverse: the scheduled_transactions row is deleted, createTransaction is called fresh (pending → logged conversion).
• Delete on a pending item → removes the scheduled_transactions row directly; per product decision, this always affects the whole series for a recurring rule (no per-occurrence exceptions), since every projected future date resolves back to the same single row.

The roll-forward while loop in resolveScheduledDates handles backdated recurring entries — logging June's rent while it's August correctly skips July's now-elapsed occurrence rather than treating it as instantly overdue, landing the rule's real next date on the first genuinely future month.

Reading: read-time projection

Because only one row exists per rule, "does this recurring rule have an occurrence in October" isn't a stored fact — it's computed by walking forward from next_due_date in monthly steps until the query's date range is covered:

Loading code snippet…

This single function powers three different consumers with the same underlying logic: calendar dots (range = the currently visible month), the merged transactions list (range = whatever day/period is being viewed), and safe-to-spend reservation (range = the current payday cycle).

Cron

A daily job is the only thing that converts a pending row into a real transaction without user interaction:

Loading code snippet…

Two things worth calling out: is_recurring: false is passed deliberately when materializing — createTransaction's own recurring branch would otherwise try to create its own next-occurrence row, duplicating the reschedule this loop already performs explicitly right after. And because materialization routes through the exact same createTransaction used by manual entry, the full Part 2 side-effect ripple (wellness score, challenges, thresholds, goals) fires identically whether a transaction was typed by a user or generated silently overnight.

For testability, the job's actual logic is exported separately from its cron.schedule() wrapper, so a due date can be backdated via a direct SQL update and the materialization function invoked on demand — no need to wait for or fake the real clock.

Part 4 — Designing the AI Tools

Three tools — create_transaction, update_transaction, delete_transaction — sit in front of the exact same transactionsService functions used by the manual UI. This was a deliberate constraint: the tools contain no scheduling logic of their own. They gather structured inputs from a conversation and hand off to the same code path, so a transaction created by chat behaves identically — future-holding, recurring rescheduling, side-effect ripple, everything — to one created by hand.

create_transaction — handling both normal and recurring in one tool

Rather than a separate tool for recurring creation, is_recurring is just one more optional field on the same schema used for a normal expense/income log:

Loading code snippet…

The recurring-specific conversational behavior lives entirely in the tool's description, since there's no separate state machine — the model reads plain-language rules and is expected to follow a multi-turn pattern:

If the transaction's category is one of: Salary, Rent, Utilities, Transportation, Insurance, or Subscriptions —
ask the user whether they want this to repeat monthly BEFORE calling this tool.
If yes, also ask what date it should start — never assume today. Only call the tool once a specific
date has been confirmed.

The function body itself branches on the resulting status the shared service returns, and — critically for the tools described next — flattens the created id to the top level of its response:

Loading code snippet…

This flattening was a direct fix to an observed failure: an earlier version buried the id under a key name that changed depending on status (data.transaction.id vs data.scheduled_transaction.id), and the model reliably failed to hold onto it across a conversation turn as a result. A single, consistently-named transactionId/status pair fixed what looked like a "memory" problem but was actually a data-shape problem.

update_transaction and delete_transaction — same scope constraint, applied to both transaction types

Both tools carry a status: 'LOGGED' | 'PENDING' field alongside transactionId, because a normal transaction and a scheduled one live in different tables and route to different underlying service calls:

Loading code snippet…

Both tools are constrained to only ever act on a transaction the agent itself created earlier in the same conversation, using the exact id/status pair its own prior create_transaction call returned — never a searched-for or guessed id. This was a scope decision, not a technical limitation: a natural-language lookup tool (resolving "delete the rent one from last week" into an id) was considered and explicitly deferred, in favor of a narrower, honestly-communicated capability — the agent corrects mistakes it just made, and says plainly that it can't touch anything older, rather than pretending to search for something it has no way to find.

delete_transaction carries one additional constraint the other two don't: a required confirmation protocol, enforced entirely at the prompt level, since the tool function itself has no way to verify a user actually said yes before executing:

1. Restate which transaction you're about to delete (amount, category, date).
2. Ask the user to explicitly confirm — e.g. "Should I delete this €50 Transportation entry from today? (yes/no)"
3. Only call this tool if the user's next message is an unambiguous "yes".
4. Never call this tool in the same turn you proposed the deletion.

For a recurring item specifically, that same confirmation message states plainly that deleting affects every future occurrence, not just the one being discussed in the conversation — mirroring the equivalent warning shown in the manual UI's delete confirmation modal, so the two entry points communicate the same consequence in the same terms.