CBMS AI — New Database Setup & Bank-by-Bank Migration Strategy¶

Version: 2.0
Date: 2026-06-15
Audience: Database Team (DBA), AI Team Lead, Backend Engineers
Status: Active — For Review and Execution
Project: CBMS AI — Cooperative Bank Management System (TFSCB)

Table of Contents¶

Executive Summary
Current State — What Exists Today
New Database Architecture
Schema Design — Full Layout
Extension and Compatibility Layer
Package Schemas and Stored Procedures
Connection Architecture and Pooling
Roles and Permissions Model
Report Infrastructure
Open Gaps — Action Required
Bank-by-Bank Migration Strategy
Per-Bank Migration Runbook
Reconciliation and Validation
Rollback Procedure
Migration Wave Plan and Timeline
Roles and Responsibilities
Risk Register

1. Executive Summary¶

CBMS AI is migrating the TFSCB cooperative bank core banking system from a legacy Oracle-based monolith to a modern microservices architecture using Spring Boot 3, React.js, and PostgreSQL 13. The target architecture isolates each banking domain into its own PostgreSQL schema, with business logic retained in PL/pgSQL (not Java), and Oracle compatibility provided through the orafce extension and a compat wrapper schema.

The migration is not a single cutover event. It is a bank-by-bank rollout across all cooperative banks currently running on the legacy system. Each bank undergoes a structured parallel-run and cutover process independently, reducing risk and allowing issues discovered in early banks to be fixed before later banks migrate.

Three critical blockers must be resolved before any bank migration begins:

Blocker	Owner	Must be done by
GAP 1 — Package schemas not in Flyway	DBA	Before Wave 0
GAP 2 — orafce not verified on all environments	DBA	Before Wave 0
GAP 3 — 576 unclassified tables not assigned to schemas	DBA + Architect	Before Wave 0

2. Current State — What Exists Today¶

2.1 Legacy System¶

The legacy system runs on a single Oracle-to-PostgreSQL migrated schema:

Property	Value
Server	`neocoredb` (port 5432, remote 5443)
Legacy schema	`varappuzha`
Tables	~1,006 total (430 classified by module, 576 unclassified)
Functions	640
Procedures	69
Views	444
DB version	PostgreSQL 13

The varappuzha schema is the system of record during transition. All live bank data lives here until a bank's domain has been fully migrated and reconciled.

2.2 What Has Already Been Migrated¶

Item	Status
640 functions, 69 procedures, 444 views	Migrated to Flyway V7 in `common-service` (covers `alappaddemo` schema only)
Oracle function replacements	Partially done via `orafce` extension and `compat` schema wrappers
Service schemas created	Partially — some `[domain]_svc` schemas exist, DDL not finalized
Table-to-module classification	430 of 1,006 tables classified; 576 remain unclassified

2.3 What Does Not Exist Yet (Gaps)¶

Gap	Detail
GAP 1	Package schemas (`pkgdayend`, `pkgtrans`, `pkg_fin_rpt`, `pkgreports`, `pkggenreports`, `pkg_reports_general`, `pkgschedule`, `pkg_app_exec`, `pkg_app_valdate`, `pkg_gn_pbk`) have no Flyway migration scripts
GAP 2	`orafce` extension not verified on dev, staging, or production environments
GAP 3	576 tables unclassified — no schema assignment
GAP 4	Jasper Reports not integrated with microservices
GAP 5	Heavy report query strategy not defined (reports returning 1,000+ rows block connections)
GAP 6	`BackendBusinessProcess.java` does not wrap package schema functions
GAP 7	Multi-bank isolation model not finalized
GAP 8	No automated test coverage for the 640 financial functions

3. New Database Architecture¶

3.1 Architecture Principles¶

One schema per microservice — each Spring Boot service owns exactly one PostgreSQL schema. No service queries another service's schema via SQL. Cross-service data is accessed through REST APIs (OpenFeign).
Business logic stays in PostgreSQL — Java code is a thin JDBC wrapper. Financial calculations, interest computation, day-end routines, and report generation remain as PL/pgSQL functions. This preserves the Oracle migration investment and allows bank-specific customization without code deploys.
Oracle compatibility through compat schema — all Oracle-compatible function calls go through compat.* wrappers, not orafce.* or oracle.* directly. This decouples the codebase from the orafce extension and enables future native PG rewrites module by module.
Reports through rpt schema only — Jasper templates never query varappuzha.* or any [domain]_svc.* table directly. All report data is exposed through rpt.* views backed by PL/pgSQL functions.
Bank customization through config schema — per-bank parameter overrides, GL account mappings, and feature flags live in config. Same code, different config rows = different bank behavior. No schema forking per bank.

3.2 Overall Topology¶

PostgreSQL Instance — neocoredb
│
├── varappuzha                  Legacy schema — system of record during transition
│                               Decommissioned per-bank after successful cutover
│
├── Service Schemas (64 total)
│   ├── customer_svc            Customer master, KYC, address, guardian, joint
│   ├── deposit_svc             Fixed deposits, recurring deposits, MDS, thrift
│   ├── sbca_svc                Savings and current accounts (act_master family)
│   ├── termloan_svc            Loans and advances (agri, personal, group)
│   ├── share_svc               Share accounts, dividend, transfer, resolution
│   ├── gl_svc                  General ledger, account heads, GL abstract
│   ├── transaction_svc         Cash transactions, transfers, receipts
│   ├── product_svc             Product definitions for all account types
│   ├── periodic_svc            Day-end, day-begin, balance tables
│   ├── syssetup_svc            Branch master, parameters, system config
│   ├── security_svc            Users, roles, screen access, login history
│   ├── charges_svc             Charge definitions, categories, exemptions
│   ├── interest_svc            Interest rate tables, DRF interest, OD interest
│   ├── rtgs_svc                RTGS/NEFT, remittance, DD, stop payment
│   ├── bills_svc               Bills lodgement, collection, guarantor
│   ├── investment_svc          Investment accounts, purchase, sale, maturity
│   ├── borrowings_svc          Borrowing accounts, repayment, schedule
│   ├── locker_svc              Locker master, operations, rent, charges
│   ├── suspense_svc            Suspense accounts, products, repayment
│   ├── employee_svc            Employee master, payroll, salary, leave
│   ├── branch_svc              Branch management (separate from syssetup)
│   ├── trading_svc             Trading purchase, sales, stock
│   └── [remaining]_svc         All other domain schemas
│
├── Shared Infrastructure Schemas
│   ├── common_svc              Shared lookup tables, holiday master, shared sequences
│   ├── compat                  Oracle-compatible function wrappers (NVL, ADD_MONTHS etc.)
│   ├── rpt                     Report views and PL/pgSQL set-returning functions (Jasper layer)
│   ├── staging                 Materialized views for heavy report pre-computation
│   └── config                  Per-bank parameter overrides and feature flags
│
└── System Schemas
    ├── public                  Extensions only (orafce, uuid-ossp, pg_partman, pg_trgm)
    └── flyway_schema_history   Per-schema Flyway migration tracking

4. Schema Design — Full Layout¶

4.1 Service Schema → Module Mapping¶

Module (TFSCB)	Schema	Tables (Classified)	Service Port
General Ledger	`gl_svc`	18	8088
Product Management	`product_svc`	54	8083
Security	`security_svc`	14	—
Interest Rate Creation	`interest_svc`	6	—
Charges Configuration	`charges_svc`	11	—
System Setup	`syssetup_svc`	48	8089
Periodic Activity	`periodic_svc`	14	8087
Customer	`customer_svc`	37	8082
RTGS/NEFT	`rtgs_svc`	5	—
Share	`share_svc`	29	8086
SB/CA	`sbca_svc`	41	—
Time Deposits	`deposit_svc`	65	8081
Loans/Advances	`termloan_svc`	54	—
Transaction	`transaction_svc`	19	8085
Suspense	`suspense_svc`	21	—
Bills	`bills_svc`	28	—
Investment	`investment_svc`	10	—
Borrowings	`borrowings_svc`	7	—
Lockers	`locker_svc`	8	—
Supporting Modules	`common_svc`	16	—
Employee/Payroll	`employee_svc`	~30 (from unclassified)	—
Unclassified (576)	TBD — see Section 10	576	—

4.2 common_svc — Shared Tables¶

Tables that have no single owning domain and are referenced by multiple services belong in common_svc:

lookup_master, lookup_master_desc
holiday_master
ifsc_bank_branch
id_generation
token_issue, token_lost, token_config
denomination_trans, cash_denomination
sms_parameter, sms_parameter_bkp
module_master
standing_instruction, standing_instruction_batch
standing_instruction_credit, standing_instruction_debit
screen_master (duplicate of security_svc — resolve to one owner)
branch_daybegin, branch_status

All shared sequences (account number, deposit number, loan number generators) also live in common_svc.

4.3 application.yml — Service Connection Template¶

Every Spring Boot service uses this template. The currentSchema list gives the service access to its own schema, compat functions, and common_svc tables, in that search-path order:

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

4.4 Flyway Migration File Convention¶

src/main/resources/db/migration/
  V1__create_schema_and_extensions.sql
  V2__create_[entity]_table.sql
  V3__create_[entity]_related_tables.sql
  V4__add_index_[purpose].sql
  V5__create_function_[name].sql
  V6__create_view_[name].sql
  V7__seed_[reference_data].sql

Rules (enforced in CI): - Migration files are append-only — never edit a deployed migration - Each migration file touches only the owning service's schema - No migration file may reference varappuzha.* or another [domain]_svc.* - Functions moved from varappuzha or package schemas get their own V[n]__create_function_[name].sql

# CI check — fails the build if cross-schema references exist
grep -rE "varappuzha\.|_svc\." src/main/resources/db/migration/ && echo "CROSS-SCHEMA REFERENCE FOUND — FIX BEFORE MERGE" && exit 1

5. Extension and Compatibility Layer¶

5.1 Extension Installation (DBA — run once per database)¶

-- Must be run by a superuser BEFORE any Flyway migration runs
\c neocoredb

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;
CREATE EXTENSION IF NOT EXISTS pg_trgm      SCHEMA public;
CREATE EXTENSION IF NOT EXISTS pgcrypto     SCHEMA public;

-- Set database-level search path so oracle.* functions are always available
ALTER DATABASE neocoredb SET search_path = "$user", public, oracle;

-- Verify
SELECT extname, extversion FROM pg_extension ORDER BY extname;

Expected output after installation:

extname	extversion
orafce	3.x
pg_partman	4.x
pg_trgm	1.x
pgcrypto	1.x
uuid-ossp	1.x

5.2 compat Schema — Oracle Wrapper Functions¶

The compat schema sits between service code and orafce. Services call compat.*, never oracle.* directly. This makes orafce removable in the future without touching any service code.

CREATE SCHEMA IF NOT EXISTS compat;

-- NVL equivalent
CREATE OR REPLACE FUNCTION compat.nvl(anyelement, anyelement)
RETURNS anyelement LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
$$ SELECT COALESCE($1, $2); $$;

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

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

-- DECODE (Oracle-style multi-branch CASE)
CREATE OR REPLACE FUNCTION compat.decode(VARIADIC args ANYARRAY)
RETURNS anyelement LANGUAGE plpgsql IMMUTABLE AS $$
DECLARE
  i INTEGER := 2;
BEGIN
  WHILE i < array_length(args, 1) LOOP
    IF args[1] = args[i] THEN RETURN args[i + 1]; END IF;
    i := i + 2;
  END LOOP;
  IF array_length(args, 1) % 2 = 0 THEN RETURN args[array_length(args, 1)]; END IF;
  RETURN NULL;
END; $$;

-- MONTHS_BETWEEN
CREATE OR REPLACE FUNCTION compat.months_between(p_date1 DATE, p_date2 DATE)
RETURNS NUMERIC LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
$$ SELECT EXTRACT(YEAR FROM AGE(p_date1, p_date2)) * 12
        + EXTRACT(MONTH FROM AGE(p_date1, p_date2)); $$;

-- TRUNC (date)
CREATE OR REPLACE FUNCTION compat.trunc(p_date DATE)
RETURNS DATE LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
$$ SELECT DATE_TRUNC('day', p_date)::DATE; $$;

-- TO_NUMBER
CREATE OR REPLACE FUNCTION compat.to_number(p_text TEXT)
RETURNS NUMERIC LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
$$ SELECT p_text::NUMERIC; $$;

-- ROWNUM replacement — use ROW_NUMBER() OVER () in queries instead
-- CONNECT BY replacement — use recursive CTEs (WITH RECURSIVE) instead
-- These two cannot be wrapped — they require query rewrites (see GAP audit)

5.3 Orafce Gap Audit (DBA Action — Run Against varappuzha)¶

Before migrating any procedure, identify which Oracle constructs are not covered by orafce and require manual rewrite:

-- Run on neocoredb against varappuzha schema
-- Outputs: function name, which non-portable construct it uses
SELECT
  routine_name,
  CASE
    WHEN routine_definition ILIKE '%ROWNUM%'      THEN 'ROWNUM — rewrite to ROW_NUMBER() OVER ()'
    WHEN routine_definition ILIKE '%CONNECT BY%'  THEN 'CONNECT BY — rewrite to WITH RECURSIVE'
    WHEN routine_definition ILIKE '%LEVEL%'       THEN 'LEVEL pseudo-column — rewrite to recursive CTE depth'
    WHEN routine_definition ILIKE '%BULK COLLECT%' THEN 'BULK COLLECT — rewrite to ARRAY or cursor loop'
    WHEN routine_definition ILIKE '%FORALL%'      THEN 'FORALL — rewrite to FOREACH or unnest()'
    WHEN routine_definition ILIKE '%DBMS_%'       THEN 'DBMS package — rewrite to PG equivalent'
    WHEN routine_definition ILIKE '%UTL_%'        THEN 'UTL package — rewrite to PG equivalent'
    ELSE 'orafce compatible — no rewrite needed'
  END AS migration_status
FROM information_schema.routines
WHERE routine_schema = 'varappuzha'
ORDER BY migration_status, routine_name;

Save this output as docs/DB SIDE/function_audit_report.csv. Functions flagged as requiring rewrite must be completed before the service that calls them can go live.

6. Package Schemas and Stored Procedures¶

6.1 Package Schema Inventory¶

The following package schemas exist in the legacy system and are not yet in Flyway. This is GAP 1 — the highest priority DBA task:

Package Schema	Domain	Flyway Migration	Responsible Service
`pkgdayend`	Day-end orchestration	Not created	`periodic_svc`
`pkgtrans`	Transaction processing	Not created	`transaction_svc`
`pkg_fin_rpt`	Financial reports	Not created	`rpt` schema
`pkgreports`	General reports	Not created	`rpt` schema
`pkggenreports`	Generic report generation	Not created	`rpt` schema
`pkg_reports_general`	Report utility functions	Not created	`rpt` schema
`pkgschedule`	Scheduled tasks	Not created	`periodic_svc`
`pkg_app_exec`	Application execution helpers	Not created	`common_svc`
`pkg_app_valdate`	Validation functions	Not created	`common_svc`
`pkg_gn_pbk`	Passbook generation	Not created	`rpt` schema

6.2 DBA Steps to Create Flyway Scripts for Package Schemas¶

# Step 1: DBA dumps each package schema as SQL-only DDL
pg_dump \
  --host=neocoredb \
  --port=5432 \
  --dbname=neocoredb \
  --schema=pkgdayend \
  --schema-only \
  --no-owner \
  --no-acl \
  --file=pkgdayend_schema_only.sql

# Repeat for each package schema above
# Step 2: DBA delivers the .sql files to the Java developer
# Step 3: Java developer creates Flyway V8 through V17 scripts in common-service
# V8__create_pkg_pkgdayend.sql
# V9__create_pkg_pkgtrans.sql
# ... etc.

6.3 BackendBusinessProcess.java — Wrapper Pattern¶

Once package schemas are in Flyway (Gap 6 resolved), map functions to the correct service. Pattern for calling a PL/pgSQL function from Java:

// Example: calling pkgdayend.run_dayend() from periodic-service
@Repository
public class DayEndRepository {

    @PersistenceContext
    private EntityManager em;

    public DayEndResult runDayEnd(String branchCode, LocalDate businessDate) {
        StoredProcedureQuery query = em
            .createStoredProcedureQuery("periodic_svc.run_dayend")
            .registerStoredProcedureParameter("p_branch_code", String.class, ParameterMode.IN)
            .registerStoredProcedureParameter("p_business_date", LocalDate.class, ParameterMode.IN)
            .registerStoredProcedureParameter("p_result", String.class, ParameterMode.OUT);

        query.setParameter("p_branch_code", branchCode);
        query.setParameter("p_business_date", businessDate);
        query.execute();

        return DayEndResult.from((String) query.getOutputParameterValue("p_result"));
    }
}

7. Connection Architecture and Pooling¶

7.1 Why PgBouncer is Mandatory¶

64 Spring Boot services × 10 connections per HikariCP pool = 640 potential connections before Jasper, before admin tools, before batch processes. PostgreSQL 13 default max_connections is 100. Without a pooler, services will crash with FATAL: sorry, too many clients already.

7.2 Connection Flow¶

┌─────────────────────────────────────────────────────────────────────┐
│  Application Tier (Docker / Kubernetes)                              │
│                                                                      │
│  64 Spring Boot services   ──HikariCP (10 conn/svc)──▶             │
│  Jasper Report Server      ──HikariCP (5 report conns)──▶          │
│  Admin / DBA tools         ──direct (restricted IP)──▶             │
└────────────────────────────────┬────────────────────────────────────┘
                                 │
              ┌──────────────────▼──────────────────┐
              │              PgBouncer               │
              │                                      │
              │  OLTP Pool (transaction mode)        │
              │  ├─ max_client_conn: 500             │
              │  └─ max server conn: 80              │
              │                                      │
              │  Report Pool (session mode)          │
              │  ├─ max_client_conn: 50              │
              │  └─ max server conn: 15              │
              └──────────────┬───────────────────────┘
                             │
             ┌───────────────┴────────────────┐
             ▼                                ▼
    PostgreSQL PRIMARY                 PostgreSQL REPLICA
    Port 5432                          Port 5433
    ├─ All writes                      ├─ rpt.* queries (Jasper)
    ├─ OLTP reads                      ├─ staging.* reads
    └─ Flyway migrations               └─ Analytics queries

7.3 PgBouncer Configuration¶

; /etc/pgbouncer/pgbouncer.ini

[databases]
; OLTP — routes to primary
neocoredb_oltp = host=neocoredb port=5432 dbname=neocoredb pool_mode=transaction

; Reports — routes to replica, session mode for Jasper cursor support
neocoredb_reports = host=neocoredb-replica port=5432 dbname=neocoredb pool_mode=session

[pgbouncer]
listen_port = 6432
listen_addr = *
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
max_client_conn = 500
default_pool_size = 80
min_pool_size = 5
reserve_pool_size = 10
reserve_pool_timeout = 3
server_idle_timeout = 600
log_connections = 0
log_disconnections = 0

8. Roles and Permissions Model¶

8.1 Role Hierarchy¶

-- Superuser setup (done once by DBA)

-- One role per service (least-privilege)
CREATE ROLE customer_svc_user    LOGIN PASSWORD 'CHANGE_ME';
CREATE ROLE deposit_svc_user     LOGIN PASSWORD 'CHANGE_ME';
CREATE ROLE transaction_svc_user LOGIN PASSWORD 'CHANGE_ME';
CREATE ROLE periodic_svc_user    LOGIN PASSWORD 'CHANGE_ME';
-- ... repeat for all 64 services

-- Report role — read-only on rpt and staging only
CREATE ROLE report_user LOGIN PASSWORD 'CHANGE_ME';

-- Batch role — can write staging, execute rpt functions
CREATE ROLE batch_user  LOGIN PASSWORD 'CHANGE_ME';

-- Migration role — used by Flyway only (elevated)
CREATE ROLE flyway_user LOGIN PASSWORD 'CHANGE_ME';
GRANT ALL ON DATABASE neocoredb TO flyway_user;

8.2 Service Role Grants¶

-- Template — repeat for each service, substituting the schema name
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 ALL SEQUENCES IN SCHEMA customer_svc TO customer_svc_user;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA customer_svc TO customer_svc_user;

-- Every service needs read access to common_svc and compat
GRANT USAGE  ON SCHEMA common_svc                TO customer_svc_user;
GRANT SELECT ON ALL TABLES IN SCHEMA common_svc  TO customer_svc_user;
GRANT USAGE  ON SCHEMA compat                    TO customer_svc_user;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA compat  TO customer_svc_user;

-- Set default search_path per role
ALTER ROLE customer_svc_user SET search_path = customer_svc, common_svc, compat, public;

8.3 Report Role Grants¶

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;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA rpt     TO report_user;

-- Explicitly block access to all service schemas and varappuzha
REVOKE ALL ON SCHEMA varappuzha                  FROM report_user;
-- Repeat REVOKE for every [domain]_svc schema
-- report_user must NEVER see raw transactional tables

9. Report Infrastructure¶

9.1 Layer Architecture¶

Jasper Template (.jrxml)
        │  calls
        ▼
rpt.v_<report_name>              Stable view — the contract Jasper binds to
        │  wraps
        ▼
rpt.<report_name>_fn(params)     PL/pgSQL set-returning function (all logic here)
        │  reads from
        ▼
staging.<report_name>            Materialized view (pre-computed for heavy reports)
        │  aggregated from
        ▼
[service]_svc.* / varappuzha.*   Raw transactional tables (via rpt functions only)

9.2 Real-Time vs Materialized Reports¶

Report type	Row count	Strategy	Refresh
Account balance, ledger	< 100 rows	Real-time: `rpt.*` function queries live tables	On demand
Day-end summary, branch totals	100–1,000 rows	Materialized: `staging.*` view refreshed at day-end	Daily
Audit reports, historical	1,000+ rows	Materialized + async generation, result stored	Scheduled

9.3 Adding a New Report (No Code Deploy)¶

-- Step 1: Write the report function in rpt schema
CREATE OR REPLACE FUNCTION rpt.loan_overdue_fn(
    p_branch_code TEXT,
    p_as_of_date  DATE
)
RETURNS TABLE (
    borrow_no      TEXT,
    customer_name  TEXT,
    loan_amount    NUMERIC(15,2),
    overdue_amount NUMERIC(15,2),
    overdue_days   INTEGER,
    npa_category   TEXT
)
LANGUAGE plpgsql AS $$
BEGIN
    RETURN QUERY
    SELECT
        lb.borrow_no,
        c.cust_name,
        ld.sanction_amount,
        ld.overdue_amount,
        (p_as_of_date - ld.due_date)::INTEGER,
        lc.npa_category
    FROM termloan_svc.loans_borrower lb
    JOIN customer_svc.customer c      ON lb.cust_id = c.cust_id
    JOIN termloan_svc.loans_data ld   ON lb.borrow_no = ld.borrow_no
    JOIN termloan_svc.loans_classify_details lc ON lb.borrow_no = lc.borrow_no
    WHERE lb.branch_code = p_branch_code
      AND ld.due_date < p_as_of_date
      AND ld.overdue_amount > 0
    ORDER BY ld.overdue_amount DESC;
END;
$$;

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

-- Step 3: Register in the report registry
INSERT INTO rpt.report_registry
  (report_code, display_name, view_name, jasper_path, param_schema, refresh_type)
VALUES
  ('LOAN_OVERDUE', 'Loan Overdue Statement',
   'rpt.v_loan_overdue', '/reports/loans/overdue.jrxml',
   '{"branch_code": "text", "as_of_date": "date"}',
   'REALTIME');

Drop the .jrxml file into Jasper's template directory. No code changes, no rebuild, no deployment.

9.4 Bank-Specific Report Customization¶

-- Override a report parameter per bank without touching the function
INSERT INTO config.report_params (param_key, param_value, branch_code, description)
VALUES
  ('LOAN_OVERDUE_THRESHOLD_DAYS', '90', 'VARP001', 'Varappuzha overdue threshold'),
  ('LOAN_OVERDUE_THRESHOLD_DAYS', '60', 'TFSCB001', 'TFSCB overdue threshold');

-- The rpt function reads from config:
-- EXECUTE format('SELECT param_value FROM config.report_params WHERE param_key = $1 AND branch_code = $2')
--   INTO v_threshold USING 'LOAN_OVERDUE_THRESHOLD_DAYS', p_branch_code;

10. Open Gaps — Action Required¶

This table is the primary action list for the DB team and AI team lead before any bank migration begins.

#	Gap	Impact if unresolved	Owner	Priority	Status
GAP 1	Package schemas (`pkgdayend`, `pkgtrans`, etc.) not in Flyway	Fresh deployment fails at runtime — day-end, transactions, reports all broken	DBA	P0	Open
GAP 2	orafce not verified on dev/staging/prod	Any server restart or new deployment fails — `oracle.nvl()` and 40+ Oracle built-ins missing	DBA	P0	Open
GAP 3	576 tables unclassified	DDL generation scripts cannot be finalized — 57% of the DB has no schema home	DBA + Architect	P0	Open
GAP 4	Jasper Reports not integrated	Reports cannot be served through microservices	Team Lead	P1	Open
GAP 5	Heavy report query strategy undefined	Reports returning 1,000+ rows will exhaust JDBC connections and time out	Team Lead + Java Dev	P1	Open
GAP 6	`BackendBusinessProcess.java` missing package schema wrappers	Day-end, transactions, and financial reports not callable from Java	Java Dev	P1	Blocked on GAP 1
GAP 7	No automated test coverage for 640 financial functions	Any PL/pgSQL change carries undetected regression risk in interest calc, GL posting	Team Lead	P2	Open
GAP 8	ROWNUM / CONNECT BY functions not audited	Unknown number of functions require manual rewrite before they can be deployed	DBA	P1	Open

10.1 Classifying the 576 Unclassified Tables¶

Apply these rules in order to resolve GAP 3:

Rule 1 — Prefix pattern → target schema:

Prefix / Pattern	Target Schema
`payroll_`, `salary_`, `emp_`, `leave_`, `paycodes_`, `income_tax_`	`employee_svc`
`forex_`, `currency_`	`syssetup_svc`
`atm_`, `ace_upi_`, `upi_`	`transaction_svc`
`gahan_`, `gold_`	`termloan_svc`
`npa_`, `reconciliation_`	`gl_svc`
`mpr_`, `rpt_`, `query_report_`	`rpt` schema
`tds_`	`deposit_svc`
`lookup_master`, `holiday_master`, `ifsc_`, `id_generation`	`common_svc`
`pass_book`, `token_`, `denomination_`	`common_svc`
`sms_`	`common_svc`
`agent_`	`product_svc`
`trading_`	`investment_svc`
`drf_`	`deposit_svc`
`mds_`	`deposit_svc`
`arc_`	`termloan_svc`
`_bkp`, `_old`, `_legacy`, `_tmp`, `_bkup`, `_bkup_new` (suffix)	`archive` schema — do not migrate to any service

Rule 2 — Zero-table modules resolve here: Modules listed with 0 classified tables (Payroll, SMS, MMBS, Room Rent, CRM, Service Tax) will gain tables once Rule 1 is applied.

Rule 3 — Remaining tables after Rules 1–2: DBA queries actual usage in production to determine owning domain, then assigns manually.

11. Bank-by-Bank Migration Strategy¶

11.1 The Multi-Tenant Model¶

Each cooperative bank gets its own PostgreSQL database (not just schema) within the same PostgreSQL instance. This provides full data isolation while sharing the server infrastructure:

PostgreSQL Instance (neocoredb)
│
├── Database: cbms_varappuzha        ← Varappuzha Co-op Bank
│     ├── all 45 schemas (64 service + common + compat + rpt + staging + config)
│     └── all 64 microservice schemas populated with this bank's data
│
├── Database: cbms_tfscb             ← TFSCB Bank
│     └── same 45-schema layout, different data
│
├── Database: cbms_<bank3>           ← Next bank
│
└── Database: cbms_registry          ← Central control plane (shared)
      ├── bank_master                   bank_id, db_host, db_port, db_name, status
      ├── migration_log                 per-bank migration phase tracking
      └── schema_version               per-bank schema version history

Each microservice resolves its database connection string at startup from cbms_registry.bank_master using the bank_id from the incoming JWT or request context. One deployment of 64 services serves all banks.

11.2 Wave Plan¶

Wave	Banks	Parallel Run	Purpose
Wave 0 — Pilot	1 (smallest bank, internal)	4 weeks	Discover all gaps, validate the runbook
Wave 1 — Early Adopters	3–5 (volunteer, medium complexity)	2 weeks each	Prove the process, refine automation
Wave 2 — Bulk Migration	Remaining banks	1 week each	Execute using validated automated runbook

Critical rule: Never move a bank to the next wave if the previous wave left unresolved reconciliation discrepancies.

11.3 Bank Prioritization Matrix¶

Score each bank on these factors to determine wave assignment:

Factor	Low (1 pt)	Medium (2 pts)	High (3 pts)
Active accounts	< 5,000	5,000–20,000	> 20,000
Daily transactions	< 500	500–2,000	> 2,000
Modules in use	Only SB + Deposits + Loans	+ Shares + Bills	+ MDS + Suspense + Investment
Staff tech readiness	High	Medium	Low
Legacy data quality	Clean	Minor gaps	Many unclassified tables

Score 5–8 → Wave 0 or Wave 1
Score 9–11 → Wave 1 or Wave 2 start
Score 12–15 → Wave 2 end (last to migrate)

12. Per-Bank Migration Runbook¶

Stage 1 — Pre-Migration Assessment (2–3 days per bank)¶

1.1 Bank data profile

-- Run against varappuzha schema — replace 'VARP001' with actual branch_code
SELECT
  'customer'      AS module, COUNT(*) AS row_count FROM varappuzha.customer     WHERE branch_code = 'VARP001'
UNION ALL SELECT
  'act_master',              COUNT(*)               FROM varappuzha.act_master   WHERE branch_code = 'VARP001'
UNION ALL SELECT
  'deposit_acinfo',          COUNT(*)               FROM varappuzha.deposit_acinfo WHERE branch_code = 'VARP001'
UNION ALL SELECT
  'loans_borrower',          COUNT(*)               FROM varappuzha.loans_borrower WHERE branch_code = 'VARP001'
UNION ALL SELECT
  'share_acct',              COUNT(*)               FROM varappuzha.share_acct   WHERE branch_code = 'VARP001'
UNION ALL SELECT
  'cash_trans (90 days)',     COUNT(*)               FROM varappuzha.cash_trans   WHERE branch_code = 'VARP001' AND trans_date >= CURRENT_DATE - 90;

1.2 Open transaction check — migration must not start with open items

-- Any pending authorizations?
SELECT COUNT(*) AS pending_auth
FROM varappuzha.trans_auth
WHERE authorize_status = 'CREATED'
  AND branch_code = 'VARP001';
-- Must be 0 before migration starts

-- Day-end status — must show clean close
SELECT business_date, dayend_status
FROM varappuzha.daily_dayend_status
WHERE branch_code = 'VARP001'
ORDER BY business_date DESC
LIMIT 5;
-- Most recent row must be 'COMPLETED'

1.3 Register bank in control plane

INSERT INTO cbms_registry.bank_master
  (bank_id, bank_name, db_host, db_port, db_name, status, wave)
VALUES
  ('VARP001', 'Varappuzha Co-operative Bank', 'neocoredb', 5432, 'cbms_varappuzha', 'MIGRATION_PENDING', 0);

Stage 2 — Target Database Provisioning (1 day)¶

-- Run on neocoredb as superuser
CREATE DATABASE cbms_varappuzha
  ENCODING 'UTF8'
  LC_COLLATE 'en_US.UTF-8'
  LC_CTYPE   'en_US.UTF-8'
  TEMPLATE template0;

\c cbms_varappuzha

-- Extensions (must come before Flyway runs)
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;
CREATE EXTENSION IF NOT EXISTS pg_trgm      SCHEMA public;
CREATE EXTENSION IF NOT EXISTS pgcrypto     SCHEMA public;

ALTER DATABASE cbms_varappuzha SET search_path = "$user", public, oracle;

-- Create all schemas
CREATE SCHEMA compat;
CREATE SCHEMA common_svc;
CREATE SCHEMA rpt;
CREATE SCHEMA staging;
CREATE SCHEMA config;
CREATE SCHEMA archive;
CREATE SCHEMA customer_svc;
CREATE SCHEMA deposit_svc;
CREATE SCHEMA sbca_svc;
CREATE SCHEMA termloan_svc;
-- ... all 64 service schemas

-- Create all compat functions (from Section 5.2)
-- Run the compat function DDL script here

-- Create roles (from Section 8)
-- Run the roles DDL script here

Then trigger all 64 microservices to run Flyway migrations against the new database. Flyway creates every table, index, constraint, sequence, and function defined in migration scripts. This is why GAP 1 must be resolved first — if package schemas aren't in Flyway, the database will be incomplete.

Stage 3 — Data Extraction from Legacy¶

3.1 Export strategy — bank-filtered, not full dump

#!/bin/bash
# run_extract.sh — extracts one bank's data from varappuzha
BANK_CODE="VARP001"
LEGACY_DB="neocoredb"
LEGACY_SCHEMA="varappuzha"
EXPORT_DIR="/tmp/migration_${BANK_CODE}"
mkdir -p $EXPORT_DIR

# Tables that have branch_code — filter to this bank only
BANK_TABLES=(
  "customer" "cust_addr" "cust_phone" "cust_joint" "cust_guardian"
  "act_master" "act_charges" "act_interest" "act_closing"
  "deposit_acinfo" "deposit_sub_acinfo" "deposit_interest" "deposit_balance"
  "loans_borrower" "loans_facility_details" "loans_sanction" "loans_repayment"
  "share_acct" "share_dividend" "share_transfer"
  "cash_trans" "transfer_trans" "trans_auth"
  # ... all bank-specific tables
)

for table in "${BANK_TABLES[@]}"; do
  echo "Exporting ${LEGACY_SCHEMA}.${table} for bank ${BANK_CODE}..."
  psql -h neocoredb -d $LEGACY_DB -c \
    "\COPY (SELECT * FROM ${LEGACY_SCHEMA}.${table} WHERE branch_code = '${BANK_CODE}') TO '${EXPORT_DIR}/${table}.csv' CSV HEADER"
done

# Reference tables — no branch_code filter, full export (goes to common_svc)
COMMON_TABLES=(
  "lookup_master" "lookup_master_desc" "holiday_master"
  "ifsc_bank_branch" "id_generation" "sms_parameter"
  "denomination_config" "token_config"
)

for table in "${COMMON_TABLES[@]}"; do
  echo "Exporting common table ${table}..."
  psql -h neocoredb -d $LEGACY_DB -c \
    "\COPY (SELECT * FROM ${LEGACY_SCHEMA}.${table}) TO '${EXPORT_DIR}/common_${table}.csv' CSV HEADER"
done

echo "Export complete: $EXPORT_DIR"

3.2 Historical / backup table decision

Before extraction, categorize all _bkp, _old, _legacy, _tmp suffixed tables:

-- Find all backup/historical tables for this bank
SELECT table_name,
       pg_size_pretty(pg_total_relation_size('varappuzha.' || table_name)) AS size
FROM information_schema.tables
WHERE table_schema = 'varappuzha'
  AND (table_name LIKE '%_bkp%'
    OR table_name LIKE '%_old%'
    OR table_name LIKE '%_legacy%'
    OR table_name LIKE '%_bkup%'
    OR table_name LIKE '%_tmp%')
ORDER BY pg_total_relation_size('varappuzha.' || table_name) DESC;

Tables with data less than 1 year old → move to archive schema in target DB
Tables with data older than 1 year → export to flat file, keep out of active DB
Confirm with bank operations before discarding any table

Stage 4 — Data Load into New Database¶

4.1 Load order — respects foreign key dependency chain

Load sequence (strict order):
1.  common_svc tables        (no dependencies)
2.  config tables            (bank-specific parameters)
3.  security_svc             (users, roles — needed by other services for FK)
4.  syssetup_svc             (branch_master — referenced by almost everything)
5.  product_svc              (product definitions — referenced by accounts)
6.  interest_svc             (interest rates — referenced by products)
7.  charges_svc              (charge definitions)
8.  customer_svc             (customer master — referenced by all accounts)
9.  gl_svc                   (account heads — referenced by transactions)
10. share_svc                (share accounts)
11. deposit_svc              (deposit accounts)
12. sbca_svc                 (savings/current accounts)
13. termloan_svc             (loans — may reference deposits as security)
14. bills_svc
15. investment_svc
16. borrowings_svc
17. locker_svc
18. suspense_svc
19. transaction_svc          (references all account types)
20. periodic_svc             (balance tables — loaded last)
21. employee_svc
22. rpt / staging schemas    (views only — no data load needed, refreshed by day-end)

4.2 Bulk load script

#!/bin/bash
# run_load.sh
TARGET_DB="cbms_varappuzha"
EXPORT_DIR="/tmp/migration_VARP001"

TABLE_SCHEMA_MAP=(
  "customer:customer_svc"
  "cust_addr:customer_svc"
  "act_master:sbca_svc"
  "deposit_acinfo:deposit_svc"
  "loans_borrower:termloan_svc"
  "lookup_master:common_svc"
  "holiday_master:common_svc"
  # ... complete mapping from classification matrix
)

# Disable FK checks for bulk load speed
psql -d $TARGET_DB -c "SET session_replication_role = replica;"

for entry in "${TABLE_SCHEMA_MAP[@]}"; do
  table="${entry%%:*}"
  schema="${entry##*:}"
  echo "Loading ${table} → ${schema}.${table}..."
  psql -d $TARGET_DB -c \
    "\COPY ${schema}.${table} FROM '${EXPORT_DIR}/${table}.csv' CSV HEADER"
done

# Re-enable FK enforcement
psql -d $TARGET_DB -c "SET session_replication_role = DEFAULT;"

echo "Load complete."

4.3 Sequence resynchronization — critical step

After bulk load, all sequences must be reset to avoid primary key conflicts on new inserts:

-- Run this for every table with a serial/sequence PK
-- Replace table and column names as appropriate
DO $$
DECLARE
  seq_val BIGINT;
BEGIN
  -- customer
  SELECT MAX(cust_id) INTO seq_val FROM customer_svc.customer;
  PERFORM setval(pg_get_serial_sequence('customer_svc.customer', 'cust_id'), COALESCE(seq_val, 1));

  -- act_master
  SELECT MAX(act_id) INTO seq_val FROM sbca_svc.act_master;
  PERFORM setval(pg_get_serial_sequence('sbca_svc.act_master', 'act_id'), COALESCE(seq_val, 1));

  -- deposit_acinfo
  SELECT MAX(deposit_no) INTO seq_val FROM deposit_svc.deposit_acinfo;
  PERFORM setval(pg_get_serial_sequence('deposit_svc.deposit_acinfo', 'deposit_no'), COALESCE(seq_val, 1));

  -- ... repeat for every table with a serial PK
  RAISE NOTICE 'Sequences synchronized.';
END;
$$;

Stage 5 — Parallel Run¶

Duration: 5 business days minimum (Wave 0: 4 weeks)

During parallel run: - Legacy system is the system of record for all writes - New system shadows the writes (dual-write via application layer or DB trigger) - Daily reconciliation runs at day-end - All discrepancies are investigated and fixed in the new system

5.1 Dual-write approach

Option A (preferred) — Application-level: The API gateway routes each write to both the legacy DB and the new DB. The legacy response is returned to the client.

Option B — DB trigger-level: A trigger on varappuzha tables replicates INSERT/UPDATE/DELETE to the corresponding new schema table. Use only if Option A is not feasible.

5.2 End-of-day reconciliation query

-- Run this on BOTH databases at day-end and compare output
-- Legacy query (on neocoredb, varappuzha schema):
SELECT
  'ACCOUNTS'    AS category,
  COUNT(*)      AS count,
  SUM(current_balance) AS total_balance
FROM varappuzha.act_master WHERE branch_code = 'VARP001' AND status = 'ACTIVE'

UNION ALL SELECT
  'DEPOSITS', COUNT(*), SUM(balance_amount)
FROM varappuzha.deposit_acinfo WHERE branch_code = 'VARP001' AND status = 'ACTIVE'

UNION ALL SELECT
  'LOANS_OUTSTANDING', COUNT(*), SUM(outstanding_amount)
FROM varappuzha.loans_borrower WHERE branch_code = 'VARP001' AND status = 'ACTIVE';

-- New system query (on cbms_varappuzha, service schemas):
SELECT
  'ACCOUNTS', COUNT(*), SUM(current_balance)
FROM sbca_svc.act_master WHERE branch_code = 'VARP001' AND status = 'ACTIVE'

UNION ALL SELECT
  'DEPOSITS', COUNT(*), SUM(balance_amount)
FROM deposit_svc.deposit_acinfo WHERE branch_code = 'VARP001' AND status = 'ACTIVE'

UNION ALL SELECT
  'LOANS_OUTSTANDING', COUNT(*), SUM(outstanding_amount)
FROM termloan_svc.loans_borrower WHERE branch_code = 'VARP001' AND status = 'ACTIVE';

-- Both queries must return identical rows. Any difference is a defect.
-- Log result to cbms_registry.migration_log
INSERT INTO cbms_registry.migration_log
  (bank_id, phase, business_date, reconciliation_status, discrepancy_details)
VALUES
  ('VARP001', 'PARALLEL_RUN', CURRENT_DATE, 'CLEAN', NULL);

5.3 Go-live criteria checklist

All of these must be TRUE before cutover is approved:

[ ] 5 consecutive business days of CLEAN reconciliation (zero discrepancy)
[ ] Day-end procedure ran successfully on new system for 5 consecutive days
[ ] All user-facing screens tested and signed off by bank operations staff
[ ] Rollback drill completed (test rollback was successfully executed)
[ ] Backup of new database confirmed restorable (restore tested, not just backed up)
[ ] Jasper reports producing correct output from new system
[ ] PgBouncer pool sizes validated under peak transaction load
[ ] Bank manager sign-off obtained in writing
[ ] DBA sign-off on cbms_registry.migration_log showing all checks passed

Stage 6 — Cutover¶

Recommended window: Friday evening after business close (18:00)

T - 60 min  Notify all bank staff: system entering maintenance window
T - 30 min  Confirm legacy day-end is COMPLETED (query daily_dayend_status)
T - 30 min  Freeze legacy system: set to read-only (ALTER DATABASE ... SET default_transaction_read_only = on)
T - 15 min  Run delta sync — capture any rows written since bulk load (see below)
T - 5  min  Run final reconciliation — must be CLEAN
T - 0       Switch API Gateway connection strings to new database (cbms_varappuzha)
T + 5  min  Smoke test: login, view an account balance, post a test deposit (then reverse it)
T + 15 min  Declare cutover complete, open system to staff
T + 60 min  Monitor error logs, DB connection counts, slow query log

Day 1–7:    Legacy remains read-only (not decommissioned). DBA on-call.
Day 7:      If no issues — decommission legacy for this bank.
            If any issue — execute rollback procedure.

Delta sync — captures the last few transactions before freeze:

-- Insert any rows written to legacy AFTER the bulk load (by timestamp or max ID)
INSERT INTO sbca_svc.act_master
SELECT * FROM varappuzha.act_master
WHERE branch_code = 'VARP001'
  AND created_at > :bulk_load_completed_at
ON CONFLICT (act_id) DO UPDATE
  SET current_balance = EXCLUDED.current_balance,
      last_updated    = EXCLUDED.last_updated,
      authorize_status = EXCLUDED.authorize_status;

-- Repeat for all tables that had inserts during the parallel run period

13. Reconciliation and Validation¶

13.1 Financial Reconciliation Queries (Master Set)¶

Run these against both legacy and new system. All figures must match exactly:

-- 1. Total savings account balances by product
SELECT prod_code, COUNT(*) AS accounts, SUM(current_balance) AS total
FROM [schema].act_master
WHERE branch_code = :bank AND status = 'ACTIVE'
GROUP BY prod_code ORDER BY prod_code;

-- 2. Total deposit outstanding by type
SELECT prod_type, COUNT(*), SUM(principal_amount), SUM(interest_accrued)
FROM [schema].deposit_acinfo
WHERE branch_code = :bank AND status = 'ACTIVE'
GROUP BY prod_type;

-- 3. Loan portfolio summary
SELECT prod_code, COUNT(*), SUM(sanction_amount), SUM(outstanding_amount), SUM(overdue_amount)
FROM [schema].loans_borrower lb
JOIN [schema].loans_data ld ON lb.borrow_no = ld.borrow_no
WHERE lb.branch_code = :bank AND lb.status = 'ACTIVE'
GROUP BY prod_code;

-- 4. Share capital
SELECT COUNT(*) AS members, SUM(paid_up_shares), SUM(share_value)
FROM [schema].share_acct
WHERE branch_code = :bank AND status = 'ACTIVE';

-- 5. GL trial balance (key heads only)
SELECT ac_hd_id, ac_hd_name, SUM(debit_amount) - SUM(credit_amount) AS balance
FROM [schema].gl_abstract
WHERE branch_code = :bank AND gl_date = :business_date
GROUP BY ac_hd_id, ac_hd_name
ORDER BY ac_hd_id;

13.2 Tolerance Policy¶

Metric	Tolerance
Account count	Zero tolerance — counts must match exactly
Balance amounts	Zero tolerance — must match to the paise
Transaction count	Zero tolerance
Interest accrued	± 1 rupee (rounding differences) — investigate if larger

Any discrepancy outside tolerance stops the cutover. It is not a warning — it is a blocker.

14. Rollback Procedure¶

14.1 Rollback Triggers¶

Execute rollback immediately if any of the following occur post-cutover:

Reconciliation discrepancy > 0 on any financial figure
Day-end fails on new system
A core transaction (deposit, withdrawal, loan disbursement) produces an error
DBA cannot resolve an application error within 2 hours of escalation
A financial function produces a different result on new system vs legacy

14.2 Rollback Steps¶

# Step 1: Flip API Gateway back to legacy database
# Update the connection string environment variable and restart the gateway
export DB_NAME=neocoredb
export DB_SCHEMA=varappuzha
# Restart API gateway service

# Step 2: Remove read-only restriction from legacy
psql -h neocoredb -d neocoredb -c \
  "ALTER DATABASE neocoredb SET default_transaction_read_only = off;"

# Step 3: Log the rollback
psql -h neocoredb -d cbms_registry -c "
INSERT INTO cbms_registry.rollback_log
  (bank_id, phase, reason, rolled_back_at, resolved)
VALUES
  ('VARP001', 'POST_CUTOVER', 'Describe the issue here', NOW(), FALSE);"

# Step 4: Keep new database intact — do not drop or truncate
# The new database is preserved for investigation and re-migration

# Step 5: Investigate using DB logs and migration_log
# Step 6: Fix the issue, re-run 5 days of parallel run, attempt cutover again

15. Migration Wave Plan and Timeline¶

Month 1  (June–July 2026)
  Week 1–2: Resolve GAP 1 (package schema Flyway scripts — DBA + Java Dev)
  Week 1:   Resolve GAP 2 (orafce verified on all environments — DBA)
  Week 2–3: Resolve GAP 3 (classify 576 tables — DBA + Architect)
  Week 3–4: Finalize DDL generation scripts for all schemas
  Week 4:   Provision Wave 0 bank database, run Flyway migrations

Month 2  (July–August 2026)
  Week 1:   Wave 0 bulk data load
  Week 2–4: Wave 0 parallel run (4 weeks, daily reconciliation)
  Week 4:   Document all issues found, fix in microservices

Month 3  (August–September 2026)
  Week 1:   Wave 0 cutover (if 5 clean days achieved)
  Week 1:   Begin Wave 1 — Bank 2 provisioning and load
  Week 2:   Begin Wave 1 — Bank 3 provisioning and load
  Week 3:   Wave 1 Bank 2 parallel run (2 weeks)
  Week 4:   Wave 1 Bank 3 parallel run (2 weeks)

Month 4  (September–October 2026)
  Week 1–2: Wave 1 cutovers (Banks 2–3)
  Week 2–3: Wave 1 Banks 4–5 provisioning, load, parallel run
  Week 4:   Wave 1 Banks 4–5 cutover

Month 5+  (October 2026 onward)
  Wave 2: Bulk migration using automated runbook
  Target: 3–5 banks per month
  Complete by: Month 10 (March 2027 estimate, depending on bank count)

Month 11+ Decommission all legacy varappuzha schemas
          Archive historical data
          Final orafce deprecation plan begins (native PG function rewrites)

16. Roles and Responsibilities¶

Role	Name	Responsibilities
DBA	Prashanth / Gibi	GAP 1 (package schema dumps), GAP 2 (orafce verification), GAP 3 (table classification), sequence resync, reconciliation queries, rollback execution
AI Team Lead	—	GAP 4 (Jasper integration design), GAP 5 (heavy query strategy), architecture decisions, cross-service dependency documentation, wave approval sign-off
Java Developer	—	GAP 6 (BackendBusinessProcess.java wrappers), Flyway V8–V17 migration scripts (from DBA dumps), dual-write proxy implementation
Domain Experts	Per module	Screen-level business logic validation, UAT sign-off per bank, legacy SQL validation
Manager	—	Bank communication, wave scheduling, bank manager sign-off collection, escalation

Decision Authority¶

Decision	Who can approve
Bank classified as ready for Wave 0 / Wave 1	AI Team Lead + DBA
Cutover go / no-go	AI Team Lead + DBA + Bank Manager
Rollback execution	DBA (no approval needed — immediate on trigger)
Decommission legacy for a bank	AI Team Lead + Manager + Bank Manager

17. Risk Register¶

Risk	Likelihood	Impact	Mitigation
orafce missing on production — functions fail	High	Critical	DBA runs verification script Week 1, blocks all other work
576 unclassified tables assigned to wrong schema	Medium	High	Double-check against Rule 1 patterns, DBA reviews production usage before finalizing
Cross-schema procedure calls fail after migration	Medium	High	Artifact audit (Section 5.3) identifies all cross-schema calls before DDL scripts run
Reconciliation discrepancy discovered post-cutover	Low	Critical	5-day parallel run with zero-tolerance policy prevents this; rollback procedure executes immediately
Jasper reports not connecting to new schemas	High	High	GAP 4 must be resolved before Wave 0 cutover; test all reports in parallel run
HikariCP pool exhaustion under peak load	Medium	High	PgBouncer mandatory (Section 7), pool sizes validated during Wave 0 parallel run
Day-end procedure fails on new system	Medium	Critical	Run day-end on new system every day during parallel run — not just during cutover
Bank staff resistance to cutover	Medium	Medium	Involve bank staff in UAT during parallel run; get sign-off before cutover date is set
Data volume too large for maintenance window	Low	High	Delta sync approach (Section 12 Stage 6) minimizes cutover window to 15 min + delta only
Package schema functions break after Flyway migration	Medium	High	Flyway creates idempotent scripts; test on dev environment before applying to any bank

CBMS AI — New Database Setup & Migration Strategy · v2.0 · 2026-06-15
Prepared by: AI Team · For: Database Team (Prashanth & Gibi), AI Team Lead
Source documents: Database_Architecture_Design.md, Gaps_Quick_Reference.md, Microservices_Database_Migration_Strategy.pdf, TFSCB_Tables_By_Module.pdf, varappuzhadb_context_mini.md