Revenue & Commission Processing Engine
Founder's prior work, generalized
A database-driven processing workflow implementing complex brokerage and advisor revenue rules across multiple transaction sources.
This case study has been intentionally generalized to protect confidential business information.
Overview
A database-driven workflow that applies brokerage and advisor revenue rules to transactions from several sources and produces revenue reporting from the results.
The problem
Revenue rules for brokerage and advisory activity are complex, transactions arrive from more than one source, and amounts in other currencies have to be converted at the right historical rate.
The solution
The rules are implemented as SQL Server stored procedures that classify, convert and calculate in a repeatable way.
- Stored procedures implementing the business rules.
- Transaction classification across multiple transaction sources.
- Historical FX handling, so each amount is converted using the rate that applied to it.
- Revenue reporting built on the processed results.
- Data reconciliation to confirm the output agrees with its sources.
Architecture
- Transaction sourcesSeveral internal and external feeds
- ClassificationTransactions sorted by type and rule
- Stored proceduresBusiness rules and historical FX
- Revenue reportingReconciled results
Key engineering decisions
Rules in the database, close to the data
Implementing the rules as stored procedures keeps the processing next to the transaction data it works on, in one place that can be reviewed and changed.
Historical rates, not current ones
Foreign-currency amounts are converted with the rate for the transaction’s date, so re-running a period reproduces the same result.
Challenges
Turning complex rules into maintainable logic
Business and financial rules have to be translated into database logic that stays readable and correct as the rules change.
Many sources, one result
Transactions from different sources have to be classified consistently before the rules can be applied to them.
Reliability & security
Reconciliation
Processed results are reconciled against their sources.
Repeatable processing
The same inputs and rates produce the same output, which makes results checkable.
Results & impact
- Complex revenue rules are applied consistently by a repeatable process.
- Revenue reporting is built on classified, reconciled data.