In relational databases, deletion is notoriously fraught.
In financial software, deletion is outright dangerous.
When a user taps "Delete" on a personal loan record, what should happen behind the scenes?
- Should the database delete the loan agreement?
- What happens to the three partial repayments logged over the past six months?
- What happens to the companion bank statement transactions where those repayments deposited money into the user's checking account?
- If the user deletes a bank transaction directly from their transaction feed, does the debt tracker know that a repayment was reversed?
If you rely solely on SQLite's built-in ON DELETE CASCADE, you will inevitably cause orphaned financial records and silent balance drift.
Here is how FinLedger architected a coordinated, multi-entity cascading deletion engine (LendingRepository.kt, commit 220a5fa) that maintains absolute mathematical integrity across interconnected tables.
The Danger of Naive Cascades
Consider what happens when database tables are coupled through superficial foreign keys:
flowchart TD subgraph CascadeFailure["The Flaw of Naive ON DELETE CASCADE"] User["User Deletes Loan Record in Lending Screen"] --> DB["SQLite Executes: DELETE FROM lending_records WHERE id = :loanId"] DB -->|ON DELETE CASCADE| Repayments["SQLite Deletes 3 Repayment Rows"] Repayments -.->|UNLINKED!| CompanionTx["Companion Bank Transactions in 'transactions' table: REMAIN BEHIND!"] CompanionTx --> Drift["Catastrophic Accounting Drift: 1. Checking account balance permanently overstated by Rs. 3,000! 2. Transaction statement shows money arriving with zero source. 3. If user recalculates ledger, math breaks!"] end Because companion bank entries live in the master transactions table—completely separate from the lending table—a simple foreign key cascade cannot reach them.
The repayments vanish from the debt tracker, but the money remains deposited in the bank account! The user's ledger now contains phantom money with no historical origin.
The Coordinated Deletion Pipeline (LendingRepository.kt)
To guarantee that deletion never leaves orphaned records or drifted balances, FinLedger executes deletions through a coordinated 6-step pipeline wrapped inside Room's withTransaction:
flowchart TD subgraph CoordinatedPipeline["FinLedger Coordinated Deletion Pipeline"] Trigger["User Confirms Loan Deletion"] --> Boundary["Open Atomic SQLite Transaction (withTransaction)"] Boundary --> Step1["Step 1: Fetch Repayment Graph: Query all repayments associated with loanId; collect companionTransactionIds and accountIds."] Boundary --> Step2["Step 2: Reverse Balance Adjustments: For each repayment, atomically subtract the deposited amount from the corresponding liquid bank account."] Boundary --> Step3["Step 3: Delete Companion Bank Entries: Delete matching rows from master 'transactions' table."] Boundary --> Step4["Step 4: Delete Repayment Entities: Delete all rows from 'lending_repayments' table."] Boundary --> Step5["Step 5: Delete Primary Loan Agreement: Delete row from 'lending_records' table."] Boundary --> Step6["Step 6: Update Person Lifecycle State: Recalculate LendingPersonSummary. If zero records remain, update person archive status."] end Let's examine why each step must occur in this exact deterministic order:
1. Pre-Deletion Graph Querying
Before executing any DELETE statement, the repository fetches the full relational graph. It extracts:
- Every repayment ID bound to this loan.
- The amount and target account ID for each repayment.
- The companion ledger transaction ID linking to the bank statement.
2. Balance Reversal Math
If Rahul repaid Rs. 1,000 into your HDFC Bank account, deleting that loan agreement means that repayment never legally occurred.
The engine adjusts the HDFC account balance downward by Rs. 1,000. This ensures that bank balances and ledger statement rows stay in 100% parity.
3. Deleting Companion Transactions
The companion transaction in the transactions table is deleted next. Because the balance adjustment and transaction deletion occur within the same SQLite transaction, an unexpected power failure or app crash cannot leave the database in an inconsistent state.
4. Recalculating Person Summary
Finally, the primary loan entity is removed.
LendingPersonSummary re-evaluates the person's remaining active loans. If this was the contact's only debt, their active balance drops cleanly to zero, and the UI dynamically updates the person's status.
Reverse Cascades: Deleting from the Ledger Feed
What if the user never visits the Lending screen, but instead deletes a repayment entry directly from their main bank statement feed?
FinLedger's TransactionRepository intercepts all transaction deletions:
- It inspects whether the transaction has a non-null
companionRepaymentId. - If found, it routes through
syncLendingStateOnTransactionDelete. - The repayment entity is deleted, and the person's outstanding loan balance is automatically restored to its pre-repayment state.
Whether deletion originates in the Lending Subsystem or the Banking Subsystem, the bi-directional sync engine ensures that both sides reconcile perfectly.
Key Takeaway
Data deletion in financial software is never an isolated table operation.
By implementing coordinated, atomic multi-entity deletion pipelines that reverse balance adjustments, clean up companion ledger entries, and update person lifecycles simultaneously, FinLedger guarantees that deleting a record leaves zero accounting scars.
FinLedger is a privacy-first, offline-first personal finance application for Android that models bank accounts, credit cards, and cash without double-counting.
Explore FinLedger on Google Play: https://play.google.com/store/apps/details?id=com.devanshufinance.app

