Architecture Decision: Database Functions vs Java Business Logic¶
CBMS — Cooperative Bank Management System Internal Document — Team Discussion Date: 2026-06-10
1. System Background¶
CBMS was originally built on Oracle Database using PL/SQL packages for all business logic. The system has been migrated to PostgreSQL with the following characteristics:
- All Oracle PL/SQL functions and packages were manually converted to PostgreSQL PL/pgSQL
- Oracle built-in compatibility is handled by the
orafcePostgreSQL extension - The original Oracle package structure is preserved as PostgreSQL schemas (one schema per Oracle package)
- Business logic, calculations, and report data fetching all reside in the database
- Reports are built using Jasper Reports, fed directly by PostgreSQL functions
This history is the foundation of every architecture decision in this document.
2. Current Database Schema Landscape¶
The production PostgreSQL database (neocoredb) contains multiple schemas serving distinct purposes.
2.1 Main Business Schema¶
| Schema | Contents |
|---|---|
alappaddemo |
All core banking functions — 640 functions, 69 procedures, 444 views, 93 composite types, 1 trigger |
2.2 Oracle Package Schemas (Business Logic)¶
Each of these was an Oracle package. During migration, each package became a PostgreSQL schema.
| Schema | Original Oracle Package | Contents |
|---|---|---|
pkgdayend |
PKG_DAYEND |
Day-end batch processing for all branches |
pkgtrans |
PKG_TRANS |
Transaction processing engine |
pkg_fin_rpt |
PKG_FIN_RPT |
Financial report data functions |
pkg_reports_general |
PKG_REPORTS_GENERAL |
General report data functions |
pkgreports |
PKG_REPORTS |
Report execution and dispatch |
pkggenreports |
PKG_GEN_REPORTS |
Generic report generation |
pkgschedule |
PKG_SCHEDULE |
Scheduled job processing |
pkg_app_exec |
PKG_APP_EXEC |
Application-level execution logic |
pkg_app_valdate |
PKG_APP_VALDATE |
Application validation rules |
pkg_gn_pbk |
PKG_GN_PBK |
Passbook generation |
Important: Functions in
alappaddemointernally call functions from these schemas. If any of these schemas are missing on a deployment target, functions inalappaddemowill fail at runtime even if they were created successfully.
2.3 Oracle Compatibility Layer — Handled by orafce¶
These schemas are provided automatically by the orafce PostgreSQL extension and do not require separate migration scripts.
| Schema | Provides |
|---|---|
oracle |
nvl(), decode(), to_char(), months_between(), add_months() and other Oracle built-ins |
dbms_output |
Oracle DBMS_OUTPUT compatibility |
dbms_random |
Oracle DBMS_RANDOM compatibility |
dbms_utility |
Oracle DBMS_UTILITY compatibility |
dbms_alert |
Oracle DBMS_ALERT compatibility |
dbms_assert |
Oracle DBMS_ASSERT compatibility |
dbms_pipe |
Oracle DBMS_PIPE compatibility |
utl_file |
Oracle UTL_FILE file I/O compatibility |
plvchr |
PL/Vision character utilities |
plvdate |
PL/Vision date utilities |
plvstr |
PL/Vision string utilities |
plvsubst |
PL/Vision substitution utilities |
plvlex |
PL/Vision lexical utilities |
plunit |
PL/Vision unit testing |
Action Required: Verify
If not installed:orafceis installed on every target server before running any Flyway migrations:CREATE EXTENSION orafce;
2.4 Standard Schema¶
| Schema | Purpose |
|---|---|
public |
PostgreSQL default schema — utility functions |
3. The Architecture Decision¶
Option A — Keep Business Logic in PostgreSQL (Current Architecture)¶
React UI
↓
Spring Boot Microservice
↓
BackendBusinessProcess.java (thin JDBC wrapper)
↓
PostgreSQL function / procedure
↓
Executes across schemas (alappaddemo → pkgdayend, pkgtrans, pkg_fin_rpt...)
Option B — Rewrite Business Logic in Java¶
React UI
↓
Spring Boot Microservice
↓
Java business logic (replaces all PL/pgSQL)
↓
PostgreSQL (storage only — plain CRUD)
4. Why Option A Is the Only Viable Choice¶
The following six factors make Option B technically and operationally infeasible for CBMS. These are not preferences — they are hard constraints.
4.1 Oracle-to-PostgreSQL Migration Is Already Complete¶
The PL/SQL to PL/pgSQL migration has already been done — manually, function by function, with Oracle compatibility handled through orafce. This represents years of work and a tested, running system.
Rewriting to Java means doing this migration a second time, in a different language, losing all the work already invested.
Impact of Option B: Discard the completed Oracle-to-PostgreSQL migration and start over in Java.
4.2 Cross-Schema Dependencies Are Deeply Embedded¶
The alappaddemo functions do not work in isolation. They call functions across pkgdayend, pkgtrans, pkg_fin_rpt, and other package schemas. These inter-schema calls are woven through the entire codebase.
Example dependency chain:
alappaddemo.rpt_pkg_dep_fd_list()
→ pkg_fin_rpt.get_fd_balance()
→ pkgtrans.get_last_trans_dt()
→ alappaddemo.get_account_behaves()
Rewriting any one function in Java requires rewriting its entire dependency chain.
Impact of Option B: No function can be migrated to Java independently — the entire chain must move together.
4.3 Report Customization Is Bank-Specific and Happens Without Code Changes¶
This is the most critical constraint.
CBMS serves multiple cooperative banks. Each bank requires customized report formats, layouts, and calculations. Reports are modified and new reports are added using:
- PL/pgSQL scripts — written directly, deployed to the database
- Jasper Report templates — designed using Jasper tooling, deployed without application changes
This workflow means: - A bank can get a new or modified report without any microservice deployment - The DBA or implementation team writes the PL/pgSQL function and the Jasper template - No Java developer involvement required for report changes
New Report Request
↓
DBA writes PL/pgSQL function in pkg_fin_rpt or pkgreports
↓
Implementation team designs Jasper template
↓
Report is live — zero application code changes
If report logic moves to Java, every report change requires a Java code change, a build, and a redeployment. This eliminates the bank's ability to customize reports independently and makes every report change a software release.
Impact of Option B: Report customization becomes a full development cycle. Multi-bank customization becomes impossible without branching the codebase per bank.
4.4 Report Data Volume — Thousands of Records¶
Banking reports routinely process thousands of records: - Day book: all transactions for the day across all accounts - Balance sheet: cumulative balances across all GL heads - DCB (Demand, Collection, Balance): all loan accounts - Trial balance: all accounts aggregated
PostgreSQL processes these entirely within the database engine. The results are already aggregated, filtered, and formatted when they reach the application.
If this logic moves to Java: - All raw records must be fetched across the network to the Java service - Aggregation, filtering, and calculation happen in Java heap memory - Risk of out-of-memory errors on large branches - Network latency multiplied by record count - JVM garbage collection pressure during heavy report runs
Impact of Option B: Significant performance degradation for all reports. Additional infrastructure required (heap tuning, streaming frameworks, pagination logic) to handle what the database currently does natively.
4.5 Transactional Integrity of Day-End Procedures¶
Day-end batch procedures (pkgdayend) perform coordinated updates across multiple tables in a single database transaction:
- Interest posting to all deposit accounts
- Inoperative account charge deduction
- GL balance updates
- Cash balance reconciliation
In PostgreSQL, if any step fails, the entire batch rolls back automatically. No partial day-end state is possible.
In Java, achieving equivalent transactional guarantees across multiple service calls requires distributed transaction coordination (Saga pattern or 2PC), adding significant complexity and new failure modes.
Impact of Option B: Day-end processing reliability depends on distributed transaction infrastructure that does not currently exist in the system.
4.6 Rewrite Scale Is Not Feasible¶
| Object Type | Count | Estimated PL/pgSQL Lines | Java Rewrite Estimate |
|---|---|---|---|
Functions (alappaddemo) |
640 | ~85,000 | 8–12 months |
Procedures (alappaddemo) |
69 | ~12,000 | 1–2 months |
| Package schema functions | ~400 (estimated) | ~60,000 | 6–9 months |
| Views | 444 | ~6,000 | 2–3 months |
| Total | ~1,550+ | ~163,000+ | ~18–26 months |
This estimate does not include the time required to build and run a financial regression test suite to validate that the rewritten logic produces identical results to the original.
Impact of Option B: Minimum 18–26 months of rewrite work before the first function moves to Java in production.
5. Decision¶
Option A — Keep all business logic in PostgreSQL.
BackendBusinessProcess.java in each microservice acts as a typed JDBC wrapper. All calculations, validations, batch processing, and report data fetching remain in PostgreSQL functions. This is not a temporary measure — it is the correct long-term architecture for a database-centric banking system of this nature.
6. Identified Gaps — Action Required¶
The following gaps exist between the current state and a complete, deployable system. Each is a concrete task.
Gap 1 — Package Schemas Not Covered by Flyway Migrations¶
Severity: High
Flyway V7 in common-service covers all objects in alappaddemo. The pkg_* package schemas have no Flyway migrations. A fresh deployment will have alappaddemo functions but will be missing the schemas they call internally.
Action: DBA to provide pg_dump --schema-only for each package schema. Dev team to create Flyway migration files from each dump.
| Schema | Migration File | Owner | Status |
|---|---|---|---|
pkgdayend |
V8__create_pkgdayend_schema.sql |
Java Dev | Pending — dump required |
pkgtrans |
V9__create_pkgtrans_schema.sql |
Java Dev | Pending — dump required |
pkg_fin_rpt |
V10__create_pkg_fin_rpt_schema.sql |
Java Dev | Pending — dump required |
pkgreports |
V11__create_pkgreports_schema.sql |
Java Dev | Pending — dump required |
pkggenreports |
V12__create_pkggenreports_schema.sql |
Java Dev | Pending — dump required |
pkg_reports_general |
V13__create_pkg_reports_general_schema.sql |
Java Dev | Pending — dump required |
pkgschedule |
V14__create_pkgschedule_schema.sql |
Java Dev | Pending — dump required |
pkg_app_exec |
V15__create_pkg_app_exec_schema.sql |
Java Dev | Pending — dump required |
pkg_app_valdate |
V16__create_pkg_app_valdate_schema.sql |
Java Dev | Pending — dump required |
pkg_gn_pbk |
V17__create_pkg_gn_pbk_schema.sql |
Java Dev | Pending — dump required |
Gap 2 — orafce Extension Not Verified on Target Servers¶
Severity: High
oracle.nvl() and other Oracle compatibility functions are already called from BackendBusinessProcess.java and from within PL/pgSQL function bodies. If orafce is not installed on a target server, these calls fail immediately.
Action:
-- Run on every target server
SELECT * FROM pg_extension WHERE extname = 'orafce';
-- If not installed
CREATE EXTENSION orafce;
This must be done before any Flyway migrations run on a new server.
| Server | orafce Verified |
Status |
|---|---|---|
| Development | — | Pending |
| Staging | — | Pending |
| Production | — | Pending |
Gap 3 — No Jasper Report Integration in Microservices¶
Severity: High — affects report workflow
Reports are generated via Jasper templates fed by PostgreSQL functions in pkg_fin_rpt, pkgreports, and related schemas. The current microservice architecture has no integration point for Jasper Reports.
Two approaches to resolve:
| Approach | Description | Pros | Cons |
|---|---|---|---|
| A — Jasper Report Server | Deploy JasperReports Server; microservices call its REST API to trigger reports | Clean separation; no code change for new reports | Requires Jasper Server infrastructure |
| B — Direct JDBC in Java | BackendBusinessProcess.java calls report functions; Java streams result to Jasper engine embedded in service | Simpler infrastructure | Report functions return large datasets — memory pressure |
Recommendation: Approach A (Jasper Report Server) preserves the existing workflow — DBA writes PL/pgSQL, designer creates
.jrxmltemplate, deploys to Jasper Server. No microservice change required for any new or modified report. This directly supports the multi-bank customization requirement.
Action: Team Lead to evaluate Jasper Report Server deployment as part of the overall infrastructure plan.
Gap 4 — Heavy Report Queries Have No Performance Strategy¶
Severity: Medium
Report functions returning thousands of records (day book, DCB, balance sheet, trial balance) will stress the JDBC connection if called synchronously from a REST endpoint.
Actions required: - Identify all report functions that return more than 500 rows - Implement asynchronous report generation — request triggers a background job, result is fetched when ready - Consider database-side pagination for interactive reports (limit/offset passed as function parameters) - Connection pool sizing must account for long-running report queries blocking connections
Gap 5 — BackendBusinessProcess.java Does Not Cover Package Schema Functions¶
Severity: Medium
Current BackendBusinessProcess.java files wrap functions from alappaddemo only. Functions in pkgdayend, pkgtrans, pkg_fin_rpt etc. are not yet wrapped.
Action: Once package schema dumps are received (Gap 1), map functions from each schema to the appropriate microservice BackendBusinessProcess.java. Day-end functions → periodicactivity-service; report functions → respective domain services or a dedicated report-service.
Gap 6 — No Multi-Bank Configuration Strategy¶
Severity: Medium — affects future deployments
Report customization is bank-specific. Currently this is handled at the database level (PL/pgSQL per bank). As the number of banks grows, a strategy is needed to manage per-bank customizations without forking the codebase.
Action: Team Lead to define the multi-tenant or multi-instance strategy:
| Strategy | Description |
|---|---|
| Separate schema per bank | Each bank has its own schema — functions customized per bank |
| Separate database per bank | Full isolation — each bank is a separate PostgreSQL database |
| Configuration-driven | Single schema, bank-specific behavior controlled by configuration tables |
Gap 7 — No Automated Test Coverage for Financial Functions¶
Severity: Medium — risk management
640+ financial functions have no automated test suite. Any future modification to a function (interest calculation, day-end logic, GL posting) has no safety net to catch regressions.
Action: Define a minimum test coverage strategy for critical financial functions before any production modifications are made to the PL/pgSQL codebase.
7. Summary of Actions¶
| # | Action | Owner | Priority |
|---|---|---|---|
| 1 | Verify orafce installed on all target servers |
DBA / Team Lead | Immediate |
| 2 | Provide pg_dump --schema-only for all package schemas |
DBA | High |
| 3 | Create Flyway migration files from package schema dumps | Java Dev | High |
| 4 | Evaluate Jasper Report Server deployment approach | Team Lead | High |
| 5 | Map package schema functions to BackendBusinessProcess.java | Java Dev + Domain Experts | Medium |
| 6 | Identify heavy report functions — design async strategy | Team Lead + Java Dev | Medium |
| 7 | Define multi-bank configuration strategy | Team Lead + Manager | Medium |
| 8 | Define minimum financial function test coverage plan | Team Lead | Medium |
8. What Is Already Done¶
| Deliverable | Detail | Status |
|---|---|---|
| BackendBusinessProcess.java | All 15 microservices — wrappers for alappaddemo functions |
Done |
| Flyway V7 | common-service — all alappaddemo objects (640 functions, 69 procedures, 444 views, 93 types, 1 trigger) |
Done |
| Oracle compatibility strategy | Confirmed — orafce extension handles all Oracle schema compatibility |
Confirmed |
| Architecture decision | Option A confirmed — logic stays in PostgreSQL | Confirmed |
Internal Document — Team Discussion CBMS Project · 2026-06-10