
Ask HN: Finance industry devs, where do you put the business logic, DB or Code - ruslan_talpa
If your answer is &quot;majority in db&quot; then how do you combat arguments like:<p>* You can&#x27;t test&#x2F;debug BL in DB (or it&#x27;s very hard)<p>* You can&#x27;t do continuous integration<p>* The code a mess and hard to maintain&#x2F;make sense of<p>If your answer is &quot;majority in app&quot;:<p>* Do you have an API on top of DB that enforces the rules?<p>* Is the security&#x2F;data validation also done in the API&#x2F;app layer?
======
antrix
I've worked in finance technology for eight years now, in various domains.

As a broad generalization, if you are latency bound (milliseconds or less)
then everything is done in code and the DB is never in the main loop.

At the other end, if you are throughput bound - think end of day/month
activities - then stored procedures all the way.

For everything in between, prefer to have the logic in code due to the
maintenance benefits that come with it.

> Do you have an API on top of DB that enforces the rules?

In general, a database is owned by the app it is associated with and the app
mediates all access to that data. The usual enterprise integration patterns
(esb, messaging, web services, file dumps) are used to share data with other
systems.

> Is the security/data validation also done in the API/app layer?

Largely yes. There's some level of security (like app access control and
encryption at rest) that's handled in the DB tier but authorization is largely
an app level concern.

Similarly, some constraints are imposed in the DB schema but those are quite
basic. Most validation is done in the app tier.

------
niftich
> * You can't test/debug BL in DB (or it's very hard)

You test/debug in a separate, isolated full-stack environment that has a copy
of the data.

> * You can't do continuous integration

Correct.

> * The code a mess and hard to maintain/make sense of

This is conjecture, and it's entirely dependent on the quality of the code.

~~~
ruslan_talpa
About the code maintainability, can you elaborate a bit and share your
experience? How many functions in the db? How do you namespace/separate them
in modules? For the sake of the argument, if those functions would be moved to
the app, would there be more or less of them, will they be more complex or
less complex.

I am particularly interested in this part since it seems to be a common
theme/argument against BL in DB

~~~
niftich
Not fintech, but I dev and maintain some systems with business-logic-in-DB. We
use Oracle, the language is PL/SQL.

You get one level of namespacing, and function names have to be less than 32
characters, so abbreviated names are common. This can be unwieldy, but
alleviated by good comments and docs.

You want to code each function to do one thing: have a well-defined input, and
a well-defined output. Living inside the database gives us transaction support
for free -- atomic operations that either succeed or rollback. The application
running inside the DB defines the external interface with its functions (the
same way a web service or a library would), and keeps the raw tables as an
implementation detail.

If we moved more functions out of the app into a higher layer (ie.
Java/Python/etc), we'd actually have more functions overall, as you'd either
have to have a bridge method that calls the equivalent function inside the DB,
or rely on an ORM and invent some instrumentation to deal with concurrency
requirements [1]

At the end of the day, a modern RDBMS allows you to implement applications
that present a well-defined interface to the outside world, with the internal
implementation remaining a private 'black box'.

[1] [https://docs.jboss.org/hibernate/orm/3.3/reference/en-
US/htm...](https://docs.jboss.org/hibernate/orm/3.3/reference/en-
US/html/transactions.html#transactions-optimistic)

------
bbcbasic
I've worked at finance places where a lot of BL is in the DB.

Testing is a trade-off. You can test the end points of stored procedures that
the app has access to for example. Although where I worked admittedly there
was no automated testing.

You can do CI if you have a way to deploy changes to scripts automatically.

The code doesn't need to be a mess but admittedly PLSQL doesn't have the same
nice abstraction patterns as OO so it does get a bit W.E.T. sometimes.

------
CyberFonic
My experience is with insurance, funds management and telco billing systems.

Although every project is different, as a general rule I prefer to use stored
procedures to make changes to the data. The SPs are written to ensure data
integrity. Of course, there are some very basic business rules evident at this
level but the primary concern is the correctness of data as updated/inserted.
I would characterise these as the "data integrity rules".

Each SP encompasses a document, a test suite and the implementation. It goes
through the DEV-TEST-UAT-PROD QA - promotion stepwise deployment. I see that
as a reasonable substitute for continuous integration.

The OO layer implements business rules. (The SPs are
transactional/procedural). I consider the BRs as a lower level business logic.
Then the application provides the high level business logic, e.g. validation,
workflow, etc.

In reference to your question, I generally end up with three layers, DB, BR
(API), BL (app). Technically the DB also has an API, but that is only visible
to the BR layer.

------
nostrademons
Most hardcore finance shops (quants, hedge funds, algo traders) I know don't
use a DB in the traditional SQL RDBMS sense. Proprietary time-series databases
rule here - because microseconds count, it's very important to think about the
access patterns of your data and optimize for the algorithms you're actually
running.

~~~
bbcbasic
Finance is broad. Outside of hft, RDBMS is a workhorse for back office data,
metadata, powering websites etc.

------
schappim
When doing consulting for an agency, I was managing the integration of a new
iPhone app with a legacy government database. We were given access to their
code and their database.

One of my wise old developers frowned and when I asked why he told me there
were literally hundreds of stored procedures in this database and no
documentation.

It was at this time he told me to when possible keep business logic in code so
it can be managed by a version control, and easily replicated for CI.

He advised the exception to this rule was when the stored procedure gave a
large performance boost.

~~~
ruslan_talpa
Maybe the number of functions would not increase much (if you ignore the
ORM/framework) but their size i think would suffer because of all the
additional code for turning rows to objects.

------
cdnsteve
All in app for this particular API, using CI, good test coverage.

DB handles data integrity. App/API handles data validation.

~~~
ruslan_talpa
Can you give a general description of that system, what's it's core
functionality? Can you give an example of a "business rule" that you
implemented in the app and a "data integrity rule" that you implemented in the
db? Thank you

------
usernamebias
I am not in "Fintech", but I may be able to help with some background in
solution architecture.

A pattern that applies to most industries is Service-oriented Architecture
(SOA) -- Where each layer (presentation, logic, data) is independent of each
other.

This allow for fantastic scalability and granular debugging.

~~~
ruslan_talpa
Thank you but this is more of a "theoretical" answer and you don't really
define what you mean by "logic" layer and how you implement it. Can you maybe
give an example of a business rule (in finance) and where exactly will you
implement it?

~~~
usernamebias
Sure thing.

First, weirdly enough I prefer to call the Business logic layer just the Logic
layer. This is just personal, for all intensive purposes its the Business
logic.

Goes without saying this example is basic and open for improvements. Let's
suppose an application exists where a user can donate $10 a month and the app
would randomly distribute it to charities of the users choice.

\-- Presentation layer, usually a public facing web application or mobile app
will handle user interaction with said app. Here the pretty UI will make sure
the user provides proper card information, charities etc.

\-- Logic layer, will be some API that will process the users information and
distribute the money programmatically.

\-- Data layer, is some kind of relational database where data will can be
persist if necessary. e.g User credentials.

Standard security protocols such as form validation and sanitization should
always be performed both at Presentation level and at Logic level. For
compliance, Things like encryption at data rest and while its in transit are
important. Method of communication between each layer would be critical as
well. There is a whole LOT more things to consider so when thinking security.
These are just an example.

~~~
ruslan_talpa
From high level i agree with your statement (about separating stuff) but when
you say "logic layer", what prevents me from looking at it as a specific
schema (in the sens of PostgreSQL) in the db (call it api) that has only
stored procedures and views and the presentation layer has access only to that
schema and not the underlying data? I don't have to move data across between
layers (data<->logic) over the network.

------
flukus
I don't like a lot of logic in the the database, but you can do continuous
integration with tools like flyway:

[https://flywaydb.org/](https://flywaydb.org/)

In fact, it's pretty hard to do CI without tools like this, even with zero
stored procs.

