Modelling bank account transfers with double entry accounting

From an accounting perspective, you would normally ignore any lags in money actually showing up and being spendable in an account just as you ignore the fact that that checks you write may not be cashed for some time. There may be a separate function if you want to reconcile what the accounting statements show as the balances in certain accounts against what the bank shows those balances to be (for example, you may want to proactively re-issue refund checks if a customer hasn't cashed it after a certain period of time).

So normally you'd just debit one bank's account and credit the other, ignore the time lag from an accounting perspective, have balanced transactions, and omit the transfer account. Just like you'd have a balanced transaction when you issue a check to a vendor to pay for the fish they delivered and ignore the fact that they may deposit the check today or 3 weeks from today.

There are subtleties if you are doing cash accounting (what individuals do) and accrual accounting (what virtually every company does) in how you actually account for certain transactions. But in either regime, your accounting balances aren't going to exactly match your bank balances at any point in time.

I'll answer from a "personal finance" standpoint since Accounting rules are different and off-topic.

Any personal finance software I've used to track bank accounts has some notion of "cleared" transactions. So while the money may not be in your second account yet, if you put the transfer there and flag it as "uncleared" you should still be able to reconcile your bank account and not have to deal with the "limbo" in separate accounts.

As a user of these programs, this make much more sense to me that using a "limbo" account that I then have to clear out once transactions clear.

Bank account reconciliation is typically separate from double-entry accounting since income and expense do mot match 1:1 with cash transactions, and may not impact the financials at all. A bank transfer of cash does not impact the financial statements, and there are many non-cash transactions that do not impact bank accounts.

I moved from Quicken (not double entry) to GnuCash (double entry), and the main reason I did so was the lack of the forced double entry in Quicken. Not having double entry came back to bite me so many times that I pulled out a lot of hair which never grew back. The way GnuCash handles it is to force an entry into the "Imbalance" account. It's simple, in that regardless of the type of transaction, there is one place it will always go. At any time you can bring up the "Imbalance" account and see everything in your books that is currently imbalanced. I greatly prefer this from the point of view of the user.

Disclaimer: the personal finance portion of this answer has now ended, and the software engineering portion has now begun.

As an aside, there are plenty of ways to skin this cat, and I'm not sure that forcing this at the database level is the best one. This is akin to the debate of whether or not to have foreign keys in a relational database. In the last 10-15 years more DBAs are starting to lean towards removing FKs, and letting the application/API layer control the constraints rather than the DB. In my opinion this is happening due to the natural progression of migrating big data sets from relational to NoSQL databases, since the shape of future data is oftentimes unknown, and performance is oftentimes hindered by FKs in large DBs. Perhaps the data shape is extremely well defined in an accounting database and that isn't relevant. That being said, your question is pretty much the same whether you put this constraint in the DB, or in the API layer. You can decide where to put it, and let's discuss "How should the constraint be handled?"

I like the flexibility of the Imbalance account if you don't yet know the target account. When you do know the target account (which you would typically know with a transfer), then some sort of pending flag may be useful at the transaction level. This may offer more flexibility than a "Transfer" account, since any kind of transaction can then be pending, for a variety of reasons. (The check is in the mail, for example.) So, perhaps any transaction, which has (at least!) two accounts linked to it, could be marked pending to indicate that you don't know where that money is. It could be in one account, or both (or more if a split), or neither until it clears. But the norm is that the "current state" will always show balances as if the transaction is already cleared, because you have to show something.

As an example, it isn't uncommon for a user to set up a transfer to a new account that doesn't exist yet, and then create the new account after the money clears. So in that scenario you'd have the target account use the Imbalance account, with the pending flag enabled. Pending can be removed first, or Imbalance can be replaced with the new account first; the order wouldn't matter.