Credora for Sapphire Bank

Diagrams

Four architecture diagrams, each one page with export to PNG or PDF (all diagrams):

1. Big picture: the bank's databases and the outside-world relays, Exasol's layers, the web app, the four roles and the CRM.
2. AWS deployment: region, VPC, subnets, security groups, both domains on Route 53, and the ports and protocols on every path.
3. Inside Exasol: runtimes and BucketFS, virtual schemas, API and ML UDFs, Lua, results, roles, the preprocessor and the semantic layer.
4. Analysis flow: the 11 steps of RUN_ANALYSIS with source, rows and timing, ending in the PREFERRING top 50 and the CRM export.

Architecture

Latest run: 545 approve, 168 refer, 287 decline. The backend writes each offer and every negotiation step to the bank's PostgreSQL crm database.

Mock data generation

Everything starts with data. The admin screen has a Generate new mock data button. It first asks for confirmation:

On confirm, the generator truncates every table, creates a new data set and writes each part to the place where it would really live. Each run is seeded, and the same seed gives the same data on the same day. A full run takes about 75 seconds on AWS.

Data explorer: see all the data, and where it came from

Every piece of data in the UI carries a source tag, and every company opens as a tree of links. Click a node to see its records.

Shree Ganesh Polymers LLP · C000042 · RM007             Sapphire Bank · MySQL
├─ Current account ••4213 · 18 months of transactions   Sapphire Bank · Oracle
├─ Loan application A0000042: ₹4.5 cr · TL · 60 months  Sapphire Bank · PostgreSQL
├─ GST: GSTIN 27AAxxxxxx1Z5 · 7 years · Mid-level       API · GSTN (mock)
│   └─ 2019 … 2026 · monthly GSTR-1 / GSTR-3B
├─ CIBIL commercial: CMR rank 3                         API · CIBIL (mock)
├─ Partner 1: R. Mehta · 40%                            Sapphire Bank · MySQL
│   ├─ CIBIL: 782 · 4 accounts · 0 overdue              API · CIBIL (mock)
│   ├─ Savings ••8890 at Sapphire Bank                  Sapphire Bank · Oracle
│   └─ Savings at HDFC Bank                             API · AA (mock)
├─ Partner 2: S. Iyer · 35%
│   ├─ CIBIL: 741                                       API · CIBIL (mock)
│   └─ Current at SBI · Savings at ICICI Bank           API · AA (mock)
├─ Partner 3: A. Khan · 25%  …
└─ Exasol report: Approve · rank 37 of 1,000 · why and how   Exasol result

The same tags appear in the source cards on the admin dashboard, which show row counts, the last generation and the health of each database and API.

Roles: who sees what

The web app logs in to Exasol as the database user that matches the signed-in person's role, so the database itself enforces what each screen can show. A bug in the app cannot widen access.

ML and AI inside Exasol

The risk model is trained and scored by Python UDFs in Exasol's PYTHON3 script language container, with scikit-learn. The training data is read live from PostgreSQL through VS_LOS, and the data never leaves the database to be scored.

• Ranking: Exasol's PREFERRING HIGH RAROC_OFFER PLUS LOW PD PLUS HIGH OFFER_AMOUNT returns the skyline of approved companies that no other company beats on return, risk and size at once. Peeling off skyline after skyline gives layers; the best 50 go to the RMs. No hand-tuned weights.
• Models are versioned. The data scientist can train a new version after every new data set, compare AUC and feature importance, and activate one. RUN_ANALYSIS trains v1 itself if no model exists.

How one company is decided

Policy values live in CREDORA.CONFIG, so the data scientist can change them in a what-if: EBLR 8.25%, operating spread 0.75%, cost of funds 6.50%, opex 1.00%, LGD 45%, capital 12%, hurdle RAROC 15%.

Shree Ganesh Polymers LLP · 3 partners · Mid-level (7 yrs GST)
Decision: APPROVE · Grade A2 · PD 1.8% · Rank 37 of 1,000

               OFFER        LIMIT IF NEGOTIATED
Amount         ₹4.00 cr     ₹4.50 cr  (max)
ROI            10.25%       10.15%    (floor)
Tenure         60 months    84 months (max)
RAROC          16.2%        15.3%     (hurdle 15%)

Why (feature attribution):
             + GST turnover matches bank credits (gap 6%)
             + no bounces in 12 months
             + all partners CIBIL 740+
             − partner 3 has 2 recent enquiries
Sources:     Oracle CBS · MySQL cif · PostgreSQL los · CIBIL · GSTN · AA

Deployment on AWS

AWS architecture-diagram style: AWS Cloud, then Region, then VPC, then subnets, then security groups, with service icons in AWS category colours. The icons are simplified redraws, not the official icon files. Numbered arrows are explained below the diagram.

1. Users → Route 53: the domain root and www resolve to the app host's Elastic IP.
2. Users → app host: HTTPS through the internet gateway to Caddy, which serves the React app at the domain root, sends /api to the backend, and redirects www and HTTP to https://<DOMAIN>.
3. Backend → Exasol: SQL on :8563 over the VPC, logged in as the Exasol user of the signed-in role. Ask Credora queries through the Exasol MCP server as CREDORA_MCP.
4. Exasol → mock APIs: Python UDFs call CIBIL, GSTN and AA on the app host's private IP, ports 8101–8103 (open to the VPC only).
5. Exasol → RDS: the virtual schemas read Oracle (:1521), MySQL (:3306) and PostgreSQL (:5432) in place.
6. Generator → RDS (dashed): writes the bank data into Oracle, MySQL and PostgreSQL, and the CIBIL, GST and AA data into mockdb.
7. Backend → PostgreSQL crm: offers, negotiation steps and sanction (small, frequent writes).
8. Generator → S3 (dashed): the hidden truth per company, for the admin scorecard.
9. Deployer → Exasol and app host (dashed): Exasol ports 22, 2581, 8443 and 8563, and SSH to the app host, only from the deployer's IP.

Not drawn as arrows: on start, the app host reads the runtime secret from Secrets Manager with its instance role and writes it to .env.runtime for the containers. RDS is not publicly accessible; its subnet group spans two zones.

Exasol objects

Steps: how it works

1. AdminClicks Generate new mock data and confirms "Generating new mock data will clear all the existing data".
2. GeneratorTruncates every table, then loads 1,000 companies, about 3,500 partners, about 850,000 Oracle transactions, CIBIL reports for every partner and company, 2–10 years of GST, partners' other-bank accounts and 5,000 past loans. About 75 seconds.
3. EveryoneCan browse the new data in the data explorer (as far as their role allows), with each item tagged by its source.
4. Data scientistOptionally trains a new model version (TRAIN_MODEL → TRAIN_PD), compares it and activates it.
5. Loan operationsClicks Run analysis. The backend runs EXECUTE SCRIPT CREDORA.RUN_ANALYSIS('<run>') as CREDORA_SVC; the 11 steps below take about 20 seconds.
6. Loan operationsOpens the Exasol report for any company: decision, why (per-feature attribution), how (each step with its SQL, Lua or Python and its timing) and which source each number came from.
7. Exasol · AIIf an API key is set, CREDIT_MEMO writes a short memo for each of the top 50.
8. Relationship managerQuotes and proposes terms with the customer; Exasol checks them against the limit and RAROC. Above the limit the offer is escalated.
9. Loan operationsVerifies and sanctions (maker-checker). PostgreSQL crm records each stage.
10. Exasol · purgePURGE_EXPIRED deletes raw API data past its purge time; the row count and SHA-256 fingerprint are kept in PURGE_LOG as proof.

Inside RUN_ANALYSIS, as measured on the live deployment

One repo, one command

The repo is the submission: web app, backend, mock APIs, generator, infrastructure, every Exasol script and UDF, and the ML code. Anyone with an AWS account fills in an env file and runs one command to recreate the whole system.

credora/
├─ README.md            what it is, logins, deploy
├─ .env.example         every setting, no secrets
├─ Makefile             setup · deploy · ship · exasol
│                       generate · status · destroy
├─ docker-compose.yml   app host services
├─ deploy/              credora.py, exasol_setup.py,
│                       Caddyfile, remote/start.sh
├─ infra/               OpenTofu: private subnets,
│                       RDS x3, app host, S3,
│                       Secrets Manager, IAM, Route 53
├─ exasol/
│  ├─ sql/              schemas, connections,
│  │                    virtual schemas, security
│  ├─ udf/              API, ML and AI UDFs
│  └─ lua/              RUN_ANALYSIS, TRAIN_MODEL,
│                       PURGE_EXPIRED
├─ db/                  Oracle, MySQL, PostgreSQL
│                       and mockdb schemas
├─ mocks/
│  ├─ cibil/  gstn/  aa/   FastAPI, own DB each
│  └─ generator/        mock data generator
├─ backend/             FastAPI
├─ frontend/            React + TypeScript (Vite)
├─ docs/                architecture, data model,
│                       APIs, why Exasol, porting
└─ tests/

AWS_PROFILE=           # the target account
AWS_REGION=ap-south-1
PROJECT=credora

DOMAIN=                # e.g. credora.loan
EXTERNAL_DOMAIN=       # e.g. credora.link (outside world)
ADMIN_CIDR=            # your IP/32; blank = detected

EXASOL_INSTANCE=r6i.xlarge
ORACLE_INSTANCE=       # optional, db.t3.small
APP_INSTANCE=          # optional, t3.large

APP_ADMIN_PASSWORD=    # optional: all four or none
APP_DATASCI_PASSWORD=
APP_LOANOPS_PASSWORD=
APP_RM_PASSWORD=

AI_PROVIDER=           # anthropic | bedrock | blank = off
ANTHROPIC_API_KEY=
AI_MODEL=claude-sonnet-5-5