CBMS AI — Database Architecture Design

Version: 1.0
Date: 2026-06-11
Audience: Backend Engineers, DBA, DevOps
Status: Draft for Review

---

1. Overview

The CBMS AI platform is built on PostgreSQL 13 with a schema-per-microservice isolation strategy across 64 Spring Boot microservices. The database hosts both the legacy cooperative bank core (migrated from Oracle) and the new AI-era microservice schemas, running on a single PostgreSQL instance (neocoredb).

This document covers schema topology, migration strategy, Oracle compatibility, report infrastructure, connection architecture, and the gaps that need to be closed before production.

---

2. Schema Topology

PostgreSQL Instance — neocoredb (port 5432 / 5443 remote)
│
├── varappuzha              Legacy Oracle-migrated schema (source of truth during transition)
│
├── Per-Service Schemas     One schema per microservice (64 total)
│   ├── customer_svc
│   ├── deposit_svc
│   ├── termloan_svc
│   ├── share_svc
│   ├── gl_svc
│   ├── transaction_svc
│   ├── periodic_svc
│   ├── employee_svc
│   ├── branch_svc
│   ├── product_svc
│   └── [domain]_svc  ...
│
├── Shared Infrastructure Schemas
│   ├── compat              Orafce wrappers and ported Oracle functions
│   ├── rpt                 Report views and set-returning functions (Jasper layer)
│   ├── staging             Materialized views and pre-aggregated report data
│   └── config              Bank-specific parameter overrides
│
└── System Schemas
    ├── public              Extensions only (uuid-ossp, orafce, pg_partman)
    └── flyway_schema_history  Per-schema migration tracking (managed by Flyway)

---

3. Per-Service Schema Naming Convention

Each microservice owns exactly one schema. The schema name must match the service domain — no environment suffixes.

Microservice	Schema	Default Port
customer-service	customer_svc	8082
deposit-service	deposit_svc	8081
product-service	product_svc	8083
transaction-service	transaction_svc	8085
share-service	share_svc	8086
generalledger-service	gl_svc	8088
periodicactivity-service	periodic_svc	8087
system-setup-service	syssetup_svc	8089
bankandbranch-service	branch_svc	8090
termloan-service	termloan_svc	—
employee-service	employee_svc	—
bills-service	bills_svc	—
charges-service	charges_svc	—
trading-service	trading_svc	—
investments-service	investments_svc	—
borrowings-service	borrowings_svc	—
All others	[domain]_svc	—

application.yml template (all services)

spring:
  datasource:
    url: jdbc:postgresql://${DB_HOST}:${DB_PORT}/${DB_NAME}?currentSchema=${SERVICE_SCHEMA},compat,public
    username: ${DB_USER}
    password: ${DB_PASSWORD}
    hikari:
      maximum-pool-size: 10
      minimum-idle: 2
      connection-timeout: 30000
  jpa:
    properties:
      hibernate.default_schema: ${SERVICE_SCHEMA}
      hibernate.dialect: org.hibernate.dialect.PostgreSQLDialect
  flyway:
    schemas: ${SERVICE_SCHEMA}
    default-schema: ${SERVICE_SCHEMA}
    baseline-on-migrate: true

Rule: No service ever references another service's schema in its Hibernate configuration or SQL. Cross-service data access goes through the REST API layer (OpenFeign client), not cross-schema SQL joins.

---

4. Migration Strategy (Flyway)

Each microservice manages its own schema version history independently via Flyway.

File naming convention

src/main/resources/db/migration/
  V1__create_[entity]_table.sql
  V2__create_[entity]_related_tables.sql
  V3__add_[column]_to_[entity].sql
  V4__create_index_[purpose].sql

Rules

• Migration files are append-only — never edit a deployed migration
• Each migration file touches only the owning service's schema
• No migration file references varappuzha.* or another service's schema
• Migrations run automatically on service startup via Spring Boot Flyway auto-configuration

CI enforcement

# In CI pipeline, verify no cross-schema references in migration files
grep -r "varappuzha\." src/main/resources/db/migration/ && exit 1
grep -r "_svc\." src/main/resources/db/migration/ && exit 1

---

5. Oracle Compatibility Layer (compat schema)

The codebase migrated from Oracle. Some PL/pgSQL functions and stored procedures still use Oracle-style constructs. The compat schema isolates all compatibility shims.

Extension setup (public schema only)

CREATE EXTENSION IF NOT EXISTS orafce        SCHEMA public;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp"   SCHEMA public;
CREATE EXTENSION IF NOT EXISTS pg_partman    SCHEMA public;

compat schema wrappers

-- Wrap Orafce or reimplementations — services call compat.*, never orafce.* directly
CREATE OR REPLACE FUNCTION compat.nvl(anyelement, anyelement)
RETURNS anyelement LANGUAGE sql IMMUTABLE AS
$$ SELECT COALESCE($1, $2); $$;

CREATE OR REPLACE FUNCTION compat.add_months(p_date DATE, p_months INTEGER)
RETURNS DATE LANGUAGE sql IMMUTABLE AS
$$ SELECT (p_date + (p_months || ' month')::INTERVAL)::DATE; $$;

CREATE OR REPLACE FUNCTION compat.last_day(p_date DATE)
RETURNS DATE LANGUAGE sql IMMUTABLE AS
$$ SELECT (DATE_TRUNC('month', p_date) + INTERVAL '1 month - 1 day')::DATE; $$;

Orafce deprecation plan

Phase	Action	Timeline
Now	Audit all functions — tag which call orafce.* vs native PG	Sprint 1
Short-term	All Orafce calls go through compat.* wrappers	Sprint 2–3
Mid-term	Rewrite compat.* to native PG, module by module	Quarter 2
Long-term	Drop Orafce extension, delete compat schema	Quarter 3

Orafce is a C extension locked to PostgreSQL 13. Removing it is a prerequisite for any future PG version upgrade.

---

6. varappuzha Integration

The varappuzha schema is the migrated Oracle core banking schema. It is the source of truth during the transition period. New microservices must not depend on it permanently.

Transition strategy: Bridge Service (recommended)

A dedicated legacy-bridge-service exposes varappuzha data as REST APIs. No other service queries varappuzha directly.

customer-service  ──Feign──▶  legacy-bridge-service  ──JDBC──▶  varappuzha.customer
deposit-service   ──Feign──▶  legacy-bridge-service  ──JDBC──▶  varappuzha.deposit_acinfo

This allows old and new systems to run in parallel during migration without coupling service schemas to Oracle-era table names.

End state: ETL into service schemas

Once a service is feature-complete, its data is migrated from varappuzha into the service schema, and varappuzha becomes archive-only for that domain.

varappuzha.customer        ──ETL──▶  customer_svc.customer
varappuzha.deposit_acinfo  ──ETL──▶  deposit_svc.deposit_account_info
varappuzha.loans_borrower  ──ETL──▶  termloan_svc.loan_borrower

---

7. Report Infrastructure (rpt + staging schemas)

Reports are added without changing application code. Jasper templates bind exclusively to rpt.* views — never to service schemas or varappuzha.* directly.

Layer architecture

Jasper Template (.jrxml)
         │  binds to
         ▼
rpt.v_<report_name>              View — the stable contract Jasper calls
         │  wraps
         ▼
rpt.<report_name>_fn(params)     Set-returning PL/pgSQL function (all logic here)
         │  reads from
         ▼
staging.<report_name>            Materialized view (for heavy 1000+ record reports)
         │  aggregated from
         ▼
[service]_svc.*  /  varappuzha.* Raw transactional tables

Report registry table

Jasper discovers available reports dynamically from this table:

CREATE TABLE rpt.report_registry (
    report_code     TEXT PRIMARY KEY,
    display_name    TEXT NOT NULL,
    view_name       TEXT NOT NULL,       -- e.g. rpt.v_loan_overdue
    jasper_path     TEXT NOT NULL,       -- e.g. /reports/loans/overdue.jrxml
    param_schema    JSONB,               -- expected params and their types
    refresh_type    TEXT DEFAULT 'REALTIME',  -- REALTIME | MATERIALIZED | SCHEDULED
    branch_codes    TEXT[]               -- NULL = available to all banks
);

Adding a new report (no code deploy required)

-- Step 1: Write the fetch function
CREATE OR REPLACE FUNCTION rpt.loan_overdue_report(
    p_branch_code TEXT,
    p_as_of_date  DATE
)
RETURNS TABLE (
    borrow_no       TEXT,
    customer_name   TEXT,
    overdue_amount  NUMERIC,
    overdue_days    INTEGER
)
LANGUAGE plpgsql AS $$
BEGIN
    RETURN QUERY
    SELECT lb.borrow_no, c.cust_name, ...
    FROM termloan_svc.loan_borrower lb
    JOIN customer_svc.customer c ON lb.cust_id = c.cust_id
    WHERE lb.branch_code = p_branch_code
    AND ...;
END;
$$;

-- Step 2: Expose as a view Jasper calls
CREATE OR REPLACE VIEW rpt.v_loan_overdue AS
SELECT * FROM rpt.loan_overdue_report(
    current_setting('app.branch_code'),
    CURRENT_DATE
);

-- Step 3: Register the report
INSERT INTO rpt.report_registry VALUES (
    'LOAN_OVERDUE', 'Loan Overdue Report',
    'rpt.v_loan_overdue', '/reports/loans/overdue.jrxml',
    '{"branch_code": "text", "as_of_date": "date"}',
    'REALTIME', NULL
);

Drop the .jrxml into Jasper's template folder — done.

Staging materialized views (for heavy reports)

Reports aggregating thousands of records use pre-computed staging tables, refreshed by the day-end batch.

CREATE MATERIALIZED VIEW staging.mv_deposit_daily_balance AS
SELECT
    d.deposit_no,
    d.branch_code,
    b.balance_date,
    SUM(b.balance_amount) AS total_balance
FROM deposit_svc.deposit_account_info d
JOIN deposit_svc.deposit_dayend_balance b USING (deposit_no)
GROUP BY d.deposit_no, d.branch_code, b.balance_date
WITH NO DATA;

CREATE UNIQUE INDEX ON staging.mv_deposit_daily_balance (deposit_no, balance_date);

-- Called by periodicactivity-service during day-end:
REFRESH MATERIALIZED VIEW CONCURRENTLY staging.mv_deposit_daily_balance;

CONCURRENTLY avoids table locks — the view remains readable during refresh.

---

8. Bank-to-Bank Customization (config schema)

Each bank deployment overrides parameters in the config schema without forking the core schema.

CREATE TABLE config.report_params (
    param_key    TEXT PRIMARY KEY,
    param_value  TEXT,
    branch_code  TEXT,     -- NULL = global default
    description  TEXT
);

CREATE TABLE config.gl_mapping (
    logical_head  TEXT,    -- e.g. 'INTEREST_INCOME'
    ac_hd_id      INTEGER,
    branch_code   TEXT
);

CREATE TABLE config.feature_flags (
    flag_key     TEXT PRIMARY KEY,
    enabled      BOOLEAN DEFAULT FALSE,
    branch_code  TEXT
);

Functions in rpt and proc read from config — same code, different config rows = different bank behavior.

---

9. Connection Architecture

64 microservices each holding a HikariCP pool will exceed PostgreSQL's max_connections limit without a pooler. PgBouncer is mandatory.

┌─────────────────────────────────────────────────────┐
│  Application Tier                                    │
│                                                      │
│  64 Spring Boot services  ──HikariCP (10/svc)──▶    │
│  Jasper Report Server     ──HikariCP────────────▶    │
└──────────────────────────────┬──────────────────────┘
                               │
              ┌────────────────▼──────────────────┐
              │           PgBouncer               │
              │  OLTP pool: transaction mode      │
              │  Report pool: session mode        │
              └────────────┬──────────────────────┘
                           │
          ┌────────────────┴────────────────┐
          ▼                                 ▼
   PostgreSQL PRIMARY              PostgreSQL REPLICA
   (OLTP writes + reads)           (rpt.*, staging.* — read only)

Pool sizing

Pool	Mode	Max Connections	Users
OLTP pool	transaction	100	All service DB users
Report pool	session	20	report_user (Jasper)

Why session mode for reports

Jasper uses SET LOCAL, temporary tables, and cursor-based streaming — these require a persistent session. Transaction-mode pooling resets session state between queries, which breaks Jasper data sources.

---

10. Database Roles and Permissions

-- Per-service role (one per service)
CREATE ROLE customer_svc_user LOGIN PASSWORD '...';
GRANT USAGE ON SCHEMA customer_svc TO customer_svc_user;
GRANT SELECT, INSERT, UPDATE, DELETE
    ON ALL TABLES IN SCHEMA customer_svc TO customer_svc_user;
GRANT USAGE ON SCHEMA compat TO customer_svc_user;

-- Report role (Jasper only — read-only on rpt and staging)
CREATE ROLE report_user LOGIN PASSWORD '...';
GRANT USAGE ON SCHEMA rpt, staging TO report_user;
GRANT SELECT ON ALL TABLES IN SCHEMA rpt TO report_user;
GRANT SELECT ON ALL TABLES IN SCHEMA staging TO report_user;
REVOKE ALL ON SCHEMA varappuzha FROM report_user;
REVOKE ALL ON SCHEMA customer_svc FROM report_user;
-- (repeat REVOKE for all service schemas)

-- Batch/day-end role (periodicactivity-service)
CREATE ROLE batch_user LOGIN PASSWORD '...';
GRANT USAGE ON SCHEMA staging, rpt TO batch_user;
GRANT ALL ON ALL TABLES IN SCHEMA staging TO batch_user;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA rpt TO batch_user;

The report_user role must have no access to any *_svc or varappuzha schema. All report data flows through rpt.* views only.

---

11. Partitioning for High-Volume Tables

Transaction and balance tables grow without bound. Partition them by month using pg_partman.

-- transaction_svc
CREATE TABLE transaction_svc.cash_trans (
    trans_id    UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    trans_date  DATE NOT NULL,
    act_num     TEXT,
    amount      NUMERIC(15,2),
    branch_code TEXT,
    trans_type  TEXT
) PARTITION BY RANGE (trans_date);

SELECT partman.create_parent(
    p_parent_table => 'transaction_svc.cash_trans',
    p_control      => 'trans_date',
    p_type         => 'range',
    p_interval     => 'monthly',
    p_premake      => 3    -- pre-create 3 future months
);

Apply the same pattern to:

Table	Partition Key
transaction_svc.cash_trans	trans_date
transaction_svc.transfer_trans	trans_date
deposit_svc.deposit_dayend_balance	balance_date
termloan_svc.loans_dayend_balance	balance_date
gl_svc.gl	trans_date

---

12. Key Indexes for Report Performance

-- Branch + date is the most common report filter
CREATE INDEX CONCURRENTLY idx_cash_trans_branch_date
    ON transaction_svc.cash_trans (branch_code, trans_date);

-- Loan overdue queries
CREATE INDEX CONCURRENTLY idx_loan_installment_due
    ON termloan_svc.loans_installment (due_date, status)
    WHERE status <> 'PAID';

-- Deposit maturity queries
CREATE INDEX CONCURRENTLY idx_deposit_maturity
    ON deposit_svc.deposit_account_info (maturity_date, authorize_status)
    WHERE authorize_status = 'AUTHORIZED';

-- NPA classification sweep
CREATE INDEX CONCURRENTLY idx_loan_npa_classify
    ON termloan_svc.loans_classify_details (classification_date, npa_category);

---

13. Open Gaps — Action Items

#	Gap	Owner	Priority
1	Schema names have environment suffixes (_db-linux) in application.yml — standardize to [domain]_svc	Backend leads	High
2	No PgBouncer configured — 64 × 10 = 640 connections will breach max_connections	DevOps	High
3	compat schema not created — Orafce functions mixed into public	DBA	High
4	rpt and staging schemas not created	DBA	Medium
5	varappuzha access pattern undefined — decide bridge service vs ETL	Architect	High
6	config schema not created — bank-specific params currently hardcoded	Backend leads	Medium
7	No read replica configured — Jasper will contend with OLTP on single node	DevOps	Medium
8	Transaction tables not partitioned — will degrade as data grows	DBA	Medium
9	No CI check preventing cross-schema references in Flyway migrations	DevOps	Low
10	Orafce deprecation not started — blocks PG version upgrade path	DBA	Low

---

14. Summary

Layer	Technology	Purpose
Database	PostgreSQL 13	Single instance, neocoredb
Schema isolation	Schema-per-service	64 schemas, one per microservice
Migrations	Flyway	Per-service, SQL-based versioning
ORM	Hibernate JPA + Spring Data	Entity mapping and repositories
Oracle compatibility	Orafce + compat schema	Shims for migrated Oracle functions
Report layer	rpt schema + Jasper	Views as stable contracts for templates
Heavy reports	staging schema + MVs	Pre-aggregated, refreshed at day-end
Bank customization	config schema	Parameter overrides per bank, no code fork
Connection pooling	PgBouncer + HikariCP	Required for 64-service scale
Partitioning	pg_partman (monthly)	High-volume transaction and balance tables
Read scaling	PostgreSQL streaming replica	Report queries routed away from OLTP primary