CBMS AI — Jasper Reports Architecture & Management¶
Version: 1.0
Date: 2026-06-11
Audience: Backend Engineers, DBA, DevOps, Report Developers
Status: Draft for Review
1. Current State (Legacy)¶
What exists in ALAPAD-POSTGRES.rar¶
The current PostgreSQL-adapted Jasper report library contains:
| Asset | Count |
|---|---|
.jrxml source templates |
613 |
.jasper compiled binaries |
524 |
| Supporting assets (images, XLS) | ~100 |
| Total files | ~1,237 |
Report domains covered¶
| Domain | Representative Templates |
|---|---|
| Advances / OD | ADLedger, AdvanceOutstanding, ADV_OverdueList, ADV_LoanNoticeReprint |
| Loans / NPA | ARC_FiledList, ARCRegister, BorrowingLedger, GoldStockRegister |
| Deposits (FD/RD) | DepositLedger, Autorenewal_List, RD consolidated |
| GL / Accounts | AcHd_Ledger, AcHdSubDay, BALSHEET_RPT, ProfitLoss |
| Transactions | CashBook, CashBookShift, CashBookUserwise, CashPayment, Transfer |
| Payroll / HR | SalProcessedList, IncomeStatement |
| Shares | Dividend reports |
| MDS / Chit | MDS-specific reports |
| Locker | Locker operation reports |
| TDS / Tax | TDS deduction and certificate reports |
How legacy generates reports¶
Legacy Client (Swing UI)
│ report_code + params
▼
GenerateReportDAO.java (EJB)
│ 1. Reads .jrxml from filesystem path (TT.properties → REPORT_TEMPLATE)
│ 2. Compiles via JasperCompileManager
│ 3. Opens direct JDBC connection to PostgreSQL
│ 4. Fills via JasperFillManager (calls PL/pgSQL function inline in jrxml)
│ 5. Exports PDF / XLSX
▼
CJRPrinterAWT.java → print / preview
Key insight: The .jrxml itself contains the SQL query (usually a SELECT * FROM pkg_reports.function_name($P{param}) call). The report is self-describing — the template carries both layout and data query.
2. Why Direct JDBC Must Stay (Not REST API)¶
Reports must connect directly to PostgreSQL via JDBC, not through microservice REST APIs.
| Approach | Problem |
|---|---|
| Report → REST API → DB | N+1 latency for 1000s of rows; API timeout for large reports; streaming not supported through REST in Jasper |
| Report → Direct JDBC | Jasper's native model; cursor-based streaming; no timeout issues; works with rpt.* views |
The report-service gets its own JDBC pool pointed at the PostgreSQL replica (read-only). All other microservices never know reports exist.
3. Target Architecture¶
3.1 Dedicated report-service microservice¶
A single report-service handles all 613+ reports. No report logic lives in any other service.
Frontend / API Consumer
│ POST /api/reports/{report_code}
│ body: { params: {...}, format: "PDF" | "XLSX" | "HTML" }
▼
API Gateway (Spring Cloud Gateway)
│ routes /api/reports/** → report-service
▼
report-service (Spring Boot)
│
├── ReportController REST endpoint
├── ReportRegistryService Looks up template + function from rpt.report_registry
├── TemplateResolverService Loads .jrxml from filesystem (bank-aware)
├── JasperFillService Fills report via direct JDBC connection
└── ExportService Produces PDF / XLSX / HTML bytes
│
├── JDBC pool ──▶ PostgreSQL REPLICA (rpt.*, staging.*, varappuzha.*)
└── Template store: /opt/cbms/reports/{bank_code}/
3.2 Component diagram¶
┌──────────────────────────────────┐
│ report-service │
│ │
Frontend │ ┌─────────────────────────────┐ │
POST /reports/PDF ──▶│ │ ReportController │ │
│ └──────────┬──────────────────┘ │
│ │ │
│ ┌──────────▼──────────────────┐ │
│ │ ReportRegistryService │ │◀──── rpt.report_registry (DB)
│ │ (lookup: code → template) │ │
│ └──────────┬──────────────────┘ │
│ │ │
│ ┌──────────▼──────────────────┐ │
│ │ TemplateResolverService │ │◀──── /opt/cbms/reports/
│ │ (load .jrxml by bank_code) │ │ {bank_code}/{name}.jrxml
│ └──────────┬──────────────────┘ │
│ │ │
│ ┌──────────▼──────────────────┐ │
│ │ JasperFillService │ │
│ │ JasperFillManager.fill() │ │──▶ PostgreSQL REPLICA
│ └──────────┬──────────────────┘ │ (rpt.* views / staging.*)
│ │ │
│ ┌──────────▼──────────────────┐ │
│ │ ExportService │ │
│ │ PDF / XLSX / HTML │ │
│ └──────────┬──────────────────┘ │
└────────────┼─────────────────────┘
│
▼
Response: bytes
Content-Type: application/pdf
4. Template Storage & Management¶
4.1 Filesystem structure¶
Templates are stored on the server filesystem, not in the database and not compiled into the JAR. This is what enables zero-code-change report addition and bank-specific customization.
/opt/cbms/reports/
├── default/ ← Baseline templates (from ALAPAD-POSTGRES.rar)
│ ├── AccountHeadList.jrxml
│ ├── ADLedger.jrxml
│ ├── BALSHEET_RPT.jrxml
│ ├── CashBook.jrxml
│ └── ... (613 templates)
│
├── ALAPAD/ ← Alappad bank overrides (only files that differ)
│ ├── BALSHEET_RPT.jrxml (custom header/logo)
│ └── ARCRegister.jrxml
│
├── VARAPPUZHA/ ← Varappuzha bank overrides
│ └── CashBook.jrxml
│
└── assets/
├── logos/
│ ├── ALAPAD_logo.jpg
│ └── VARAPPUZHA_logo.jpg
└── fonts/
Template resolution order:
1. /opt/cbms/reports/{bank_code}/{template_name}.jrxml — bank-specific override
2. /opt/cbms/reports/default/{template_name}.jrxml — fallback baseline
This means a bank gets a customized report if their override file exists, otherwise the default runs. No code change required for either case.
4.2 Template versioning¶
Templates are plain XML files — version them in Git:
cbms-ai-reports/ ← Separate Git repository for report templates
├── default/
│ └── ... .jrxml files
├── ALAPAD/
│ └── ... .jrxml overrides
└── deploy.sh ← Copies templates to /opt/cbms/reports/ on target server
Deploying a new or modified report = git push + deploy.sh. No service restart needed (templates are read from disk at runtime).
4.3 Compiled .jasper files¶
Do not store .jasper files in Git. The report-service compiles .jrxml → .jasper at first use and caches in memory:
// Compiled reports cached in-process; invalidated on template file change
private final Map<String, JasperReport> compiledCache = new ConcurrentHashMap<>();
public JasperReport getCompiled(String templatePath) {
return compiledCache.computeIfAbsent(templatePath, path ->
JasperCompileManager.compileReport(path)
);
}
On production, pre-compile all templates at service startup to avoid first-request latency.
5. Report Registry (Database)¶
The registry table in rpt schema is the single source of truth for what reports exist and how to invoke them.
CREATE TABLE rpt.report_registry (
report_code TEXT PRIMARY KEY, -- e.g. 'CASH_BOOK'
display_name TEXT NOT NULL, -- 'Cash Book Report'
module TEXT NOT NULL, -- 'TRANSACTIONS' | 'LOANS' | 'DEPOSITS' ...
template_name TEXT NOT NULL, -- 'CashBook' (resolves to CashBook.jrxml)
data_source_type TEXT DEFAULT 'DB_FUNCTION', -- 'DB_FUNCTION' | 'DB_VIEW' | 'NATIVE_SQL'
data_source_ref TEXT, -- function/view name: rpt.cash_book_fn
param_schema JSONB, -- expected params + types
default_format TEXT DEFAULT 'PDF', -- 'PDF' | 'XLSX' | 'HTML'
allowed_formats TEXT[] DEFAULT '{PDF,XLSX}',
refresh_type TEXT DEFAULT 'REALTIME', -- 'REALTIME' | 'MATERIALIZED'
branch_codes TEXT[], -- NULL = all banks
authorized_roles TEXT[], -- roles that can run this report
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMP DEFAULT NOW(),
updated_at TIMESTAMP DEFAULT NOW()
);
Sample registry entries¶
INSERT INTO rpt.report_registry VALUES
('CASH_BOOK', 'Cash Book', 'TRANSACTIONS', 'CashBook',
'DB_FUNCTION', 'rpt.cash_book_fn',
'{"branch_code":"text","from_date":"date","to_date":"date"}',
'PDF', '{PDF,XLSX}', 'REALTIME', NULL, '{TELLER,MANAGER}', TRUE, NOW(), NOW()),
('BALANCE_SHEET', 'Balance Sheet', 'GL', 'BALSHEET_RPT',
'DB_FUNCTION', 'rpt.balance_sheet_fn',
'{"branch_code":"text","as_of_date":"date"}',
'PDF', '{PDF}', 'MATERIALIZED', NULL, '{MANAGER,AUDITOR}', TRUE, NOW(), NOW()),
('LOAN_OVERDUE', 'Loan Overdue List', 'LOANS', 'ARC_FiledList_new',
'DB_FUNCTION', 'rpt.loan_overdue_fn',
'{"branch_code":"text","category":"text","as_of_date":"date"}',
'PDF', '{PDF,XLSX}', 'REALTIME', NULL, '{LOAN_OFFICER,MANAGER}', TRUE, NOW(), NOW()),
('ADV_LEDGER', 'Advance Ledger', 'ADVANCES', 'ADLedger',
'DB_FUNCTION', 'rpt.adv_ledger_fn',
'{"acct_num":"text","from_date":"date","to_date":"date"}',
'PDF', '{PDF}', 'REALTIME', NULL, '{LOAN_OFFICER}', TRUE, NOW(), NOW());
6. How Reports Connect to Data¶
6.1 The .jrxml SQL query (existing pattern — keep it)¶
In the legacy .jrxml, the query looks like:
<queryString>
<![CDATA[
SELECT * FROM pkgreports.cash_book_report($P{branch_code}, $P{from_date}, $P{to_date})
]]>
</queryString>
In the new system, the function moves to the rpt schema and is named consistently:
<queryString>
<![CDATA[
SELECT * FROM rpt.cash_book_fn($P{branch_code}, $P{from_date}, $P{to_date})
]]>
</queryString>
This is a one-time find-and-replace migration across all 613 .jrxml files when migrating from pkgreports.* / pkg_reports.* to rpt.*.
6.2 PL/pgSQL function pattern (rpt schema)¶
CREATE OR REPLACE FUNCTION rpt.cash_book_fn(
p_branch_code TEXT,
p_from_date DATE,
p_to_date DATE
)
RETURNS TABLE (
trans_date DATE,
particulars TEXT,
voucher_no TEXT,
receipts NUMERIC,
payments NUMERIC,
balance NUMERIC
)
LANGUAGE plpgsql AS $$
BEGIN
RETURN QUERY
SELECT
ct.trans_date,
ct.particulars,
ct.voucher_no,
CASE WHEN ct.dr_cr = 'CR' THEN ct.amount ELSE 0 END,
CASE WHEN ct.dr_cr = 'DR' THEN ct.amount ELSE 0 END,
SUM(ct.amount) OVER (ORDER BY ct.trans_date, ct.trans_id)
FROM transaction_svc.cash_trans ct
WHERE ct.branch_code = p_branch_code
AND ct.trans_date BETWEEN p_from_date AND p_to_date
ORDER BY ct.trans_date, ct.trans_id;
END;
$$;
6.3 report-service JDBC configuration¶
# report-service application.yml
spring:
datasource:
report:
url: jdbc:postgresql://${REPORT_DB_HOST}:5432/neocoredb?currentSchema=rpt,staging,compat,public
username: report_user
password: ${REPORT_DB_PASSWORD}
hikari:
maximum-pool-size: 20
minimum-idle: 5
connection-timeout: 60000
# Session mode required for Jasper (temp tables, SET LOCAL)
# Route through PgBouncer session pool
7. Request Flow: Frontend → PDF¶
1. User clicks "Generate Cash Book" in frontend
│
2. Frontend: POST /api/reports/generate
{
"report_code": "CASH_BOOK",
"format": "PDF",
"params": {
"branch_code": "001",
"from_date": "2026-06-01",
"to_date": "2026-06-11"
}
}
│
3. API Gateway routes to report-service:8095
│
4. ReportController.generate()
│ a. Validates JWT — checks authorized_roles in report_registry
│
5. ReportRegistryService.lookup("CASH_BOOK")
│ → returns: template_name="CashBook", data_source_ref="rpt.cash_book_fn"
│
6. TemplateResolverService.resolve("CASH_BOOK", branch_code="001")
│ → Checks /opt/cbms/reports/ALAPAD/CashBook.jrxml (bank override)
│ → Falls back to /opt/cbms/reports/default/CashBook.jrxml
│
7. JasperFillService.fill(jasperReport, params, jdbcConnection)
│ → JasperFillManager.fillReport(report, params, connection)
│ → Jasper executes: SELECT * FROM rpt.cash_book_fn('001', '2026-06-01', '2026-06-11')
│ → Streams results, applies .jrxml layout
│
8. ExportService.export(jasperPrint, "PDF")
│ → JRPdfExporter
│
9. Response:
HTTP 200
Content-Type: application/pdf
Content-Disposition: attachment; filename="CashBook_001_20260611.pdf"
[PDF bytes]
8. Bank-to-Bank Customization¶
How it works¶
Each bank gets a folder under /opt/cbms/reports/{bank_code}/. Only override files go there — if a file is not overridden, the default is used automatically.
| Customization Need | Mechanism |
|---|---|
| Bank logo / header | Override .jrxml with bank-specific logo path |
| Different column layout | Override .jrxml with redesigned layout |
| Additional columns | Override .jrxml + extend rpt.*_fn() |
| Completely different report | New .jrxml + new entry in rpt.report_registry with branch_codes = '{BANK_X}' |
| Hide a report from a bank | Set branch_codes to exclude that bank in registry |
| Report available only to one bank | Set branch_codes = '{BANK_X}' |
No code change required in any case.¶
9. Adding a New Report (Zero Code Deploy)¶
This is the full workflow for adding a new report to any bank:
Step 1 — Write the data function in PostgreSQL:
CREATE OR REPLACE FUNCTION rpt.new_report_fn(p_branch_code TEXT, p_date DATE)
RETURNS TABLE (...) LANGUAGE plpgsql AS $$ ... $$;
Step 2 — Design the .jrxml template in Jasper Studio:
- Query: SELECT * FROM rpt.new_report_fn($P{branch_code}, $P{report_date})
- Add layout, formatting, bank logo parameter
Step 3 — Deploy the template:
cp NewReport.jrxml /opt/cbms/reports/default/
# OR for bank-specific:
cp NewReport.jrxml /opt/cbms/reports/ALAPAD/
Step 4 — Register the report:
INSERT INTO rpt.report_registry (report_code, display_name, module, template_name, ...)
VALUES ('NEW_REPORT', 'New Report Name', 'MODULE', 'NewReport', ...);
Step 5 — Frontend picks it up automatically (reads report list from registry via GET /api/reports/list).
Done. No Spring Boot restart. No code commit. No build pipeline.
10. Migrating 613 Legacy Templates¶
Migration steps¶
| Step | Task | Effort |
|---|---|---|
| 1 | Extract ALAPAD-POSTGRES.rar → /opt/cbms/reports/default/ |
1 hour |
| 2 | Bulk rename legacy function schema in .jrxml files: pkgreports. → rpt. |
2 hours (sed/script) |
| 3 | Identify which .jrxml have no matching PL/pgSQL function in new DB — these need migration | 1 week |
| 4 | Migrate or rewrite PL/pgSQL functions from varappuzha to rpt schema |
2–4 weeks |
| 5 | Register all reports in rpt.report_registry |
1 week |
| 6 | Move templates to Git repository (cbms-ai-reports) |
1 day |
| 7 | Test each report against new DB (compare output with legacy) | 3–4 weeks |
Bulk schema rename script¶
# Run from /opt/cbms/reports/default/
# Replace legacy Oracle-era schema references with new rpt schema
find . -name "*.jrxml" -exec sed -i \
-e 's/pkgreports\./rpt./g' \
-e 's/pkg_reports\./rpt./g' \
-e 's/pkg_fin_rpt\./rpt./g' \
-e 's/pkggenreports\./rpt./g' {} \;
11. report-service — Spring Boot Setup¶
Maven dependency (pom.xml)¶
<dependency>
<groupId>net.sf.jasperreports</groupId>
<artifactId>jasperreports</artifactId>
<version>6.21.3</version>
</dependency>
<dependency>
<groupId>net.sf.jasperreports</groupId>
<artifactId>jasperreports-pdf</artifactId>
<version>6.21.3</version>
</dependency>
<!-- Excel export -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.5</version>
</dependency>
Core service structure¶
report-service/
├── src/main/java/com/arcat/report/
│ ├── api/
│ │ └── ReportController.java ← POST /generate, GET /list, GET /preview
│ ├── service/
│ │ ├── ReportRegistryService.java ← reads rpt.report_registry
│ │ ├── TemplateResolverService.java ← filesystem lookup with bank fallback
│ │ ├── JasperFillService.java ← JasperFillManager wrapper
│ │ └── ExportService.java ← PDF / XLSX / HTML exporter
│ ├── config/
│ │ ├── ReportDataSourceConfig.java ← separate JDBC pool for reports
│ │ └── JasperCacheConfig.java ← in-memory compiled template cache
│ └── dto/
│ ├── ReportRequest.java
│ └── ReportMetadata.java
└── src/main/resources/
└── application.yml
REST API contract¶
GET /api/reports/list?module=LOANS&bank_code=ALAPAD
→ Returns list of available reports for this bank
POST /api/reports/generate
Body: { report_code, format, params }
→ Returns: PDF/XLSX bytes (streaming response)
GET /api/reports/preview/{report_code}
→ Returns HTML preview (useful for frontend preview pane)
12. Performance Considerations¶
| Concern | Solution |
|---|---|
| 1000s of rows per report | Jasper uses JDBC cursor-based streaming — data never fully loaded into heap |
| Concurrent report requests | HikariCP pool (20 connections) for report JDBC; Jasper thread-safe |
| Balance Sheet / heavy aggregate reports | Pre-computed in staging.* materialized views; report reads staging not live tables |
| Template compilation overhead | Compile-once + in-memory cache per template path |
| Report JDBC competing with OLTP writes | Report JDBC connects to read replica only — zero impact on primary |
| Long-running report blocking HTTP thread | Use StreamingResponseBody in Spring MVC for async streaming |
13. Open Action Items¶
| # | Action | Owner | Priority |
|---|---|---|---|
| 1 | Create report-service microservice with Jasper dependency |
Backend Lead | High |
| 2 | Extract ALAPAD-POSTGRES.rar to /opt/cbms/reports/default/ |
DevOps | High |
| 3 | Create cbms-ai-reports Git repo for template versioning |
DevOps | High |
| 4 | Run bulk schema rename script (pkgreports → rpt) on all 613 jrxml files | DBA | High |
| 5 | Create rpt.report_registry table and seed with all report entries |
DBA | High |
| 6 | Set up read replica for PostgreSQL; configure report_user role |
DevOps | High |
| 7 | Configure PgBouncer session-mode pool for report connections | DevOps | High |
| 8 | Audit 613 templates — identify which PL/pgSQL functions are missing in new DB | DBA + Backend | Medium |
| 9 | Migrate missing PL/pgSQL functions from legacy schema to rpt.* |
DBA | Medium |
| 10 | Add /api/reports/list endpoint to API Gateway routing |
Backend Lead | Medium |
| 11 | Test all 613 reports against new DB — compare output with legacy | QA + DBA | Medium |
| 12 | Bank-specific template folders for each bank (ALAPAD, VARAPPUZHA, etc.) | DBA | Low |
14. Summary¶
| Aspect | Legacy | New (CBMS AI) |
|---|---|---|
| Report engine | JasperReports (EJB) | JasperReports (Spring Boot microservice) |
| Template location | Filesystem (TT.properties path) | /opt/cbms/reports/{bank_code}/ |
| Template versioning | Unversioned filesystem | Git (cbms-ai-reports repo) |
| Data connection | Direct JDBC to Oracle/PostgreSQL | Direct JDBC to PostgreSQL REPLICA |
| Data functions | pkgreports.* / pkg_reports.* |
rpt.* (PostgreSQL schema) |
| Report registry | Implicit (file existence) | Explicit (rpt.report_registry table) |
| New report addition | Drop .jrxml + restart server | Drop .jrxml + INSERT into registry (no restart) |
| Bank customization | Separate installation per bank | Bank-override folder, shared codebase |
| Output formats | PDF, print | PDF, XLSX, HTML |
| Template count | 613 .jrxml | 613 migrated + new additions |