Credora

Pre-approved corporate loan offers for Sapphire Bank, decided inside Exasol.

Built for ExaThon India 2026 · Powered by Exasol Personal.

Sign in with one click on the login page, or use these demo logins. All data is synthetic, so the passwords are public on purpose.

The hand-off: the data scientist runs the analysis and publishes it, loan operations assigns its companies to relationship managers, each manager works her own companies and logs their loan status, and admin, data scientist and loan operations track every assigned company.

Every role also has Ask Credora: plain-English questions answered by Claude through the official Exasol MCP server, logged in to Exasol as that role's own database user.

Run your own copy: install Exasol Personal, clone this repo, fill in .env.<name> with your AWS profile and Route 53 domain, then run python deploy/credora.py deploy --env <name>. Step by step: Deploy it yourself.

Built on Exasol Personal#

Credora started on the Exasol Personal Local Starter Kit (exakit 0.2.3): its personal launcher 2.3.0, the Exasol MCP server 2.3.0, exapump 0.13.1 and pyexasol. Check yours with exakit version. We then deployed Exasol Personal to AWS with the official launcher (exasol 2.3.0, Exasol 2026.1.0 on an r6i.xlarge), because we wanted a working, always-on demo that exercises as much of Exasol as one real use case allows, end to end:

Details and code for each: 4. Exasol at the centre and docs/WHY_EXASOL.md.

Verify it yourself#

Each claim can be checked on the live site and in one or two files.

Contents#

1. The problem
2. How Credora solves it
3. Architecture
4. Exasol at the centre
5. Role-based access, enforced by Exasol
6. Ask Credora: try these
7. Scale and measured performance
8. Key decisions and trade-offs
9. Security and data governance
10. Failure modes
11. Deploy it yourself
12. Repository layout
13. Documentation
14. Built with Exasol open source: thank you
15. About this project, contributing and contact

1. The problem#

Sapphire Bank has 1,000 corporate current-account customers, each run by 2 to 5 partners. Instead of waiting for companies to come to it, the bank wants to approach its best customers with a pre-approved loan offer: an amount, a rate (ROI) and a tenure, plus a negotiation limit the relationship manager can move within without going back to credit.

To decide that, a credit officer needs data that lives in six places, half inside the bank and half outside it:

Requirements

• Functional: combine all six sources per company; score credit risk with a model trained on the bank's own loan history; price an offer and a negotiation limit that still meet the bank's return hurdle; rank the portfolio; show every number with its source; let four roles work the result, from data generation to sanction.
• Non-functional: no copy of core banking leaves the bank's databases; consent-based outside data is deleted on time, with proof; each role sees only what it may see, enforced by the database; the full analysis of 1,000 companies runs in under a minute; the whole system rebuilds in any AWS account from this repo with one command.
• Out of scope: real CIBIL, GSTN or Account Aggregator connections (Credora ships faithful mocks); loan disbursement and servicing; real customer data of any kind.

2. How Credora solves it#

One call, EXECUTE SCRIPT CREDORA.RUN_ANALYSIS('<run>'), does the whole job inside Exasol in about 18 seconds:

1. Read the bank's data in place. Virtual schemas expose Oracle, MySQL and PostgreSQL as Exasol schemas. Queries are pushed down to the source; nothing is copied first.
2. Fetch the outside world. Python UDFs call the CIBIL, GSTN and Account Aggregator relays over the public internet, 16 in parallel, and stage the replies with a purge time.
3. Build features. Thirteen credit features per company: GST turnover and filing discipline, the gap between GST turnover and bank credits, balances, inflow volatility, bounces, CIBIL ranks and partner scores, other-bank balances, debt service cover.
4. Score risk. A gradient-boosted model (the champion), trained inside Exasol on the bank's past loans, gives each company a probability of default and the effect of each feature on it.
5. Flag the unusual. An Isolation Forest UDF looks at all companies of the run at once and flags the most unusual 3%: GST turnover far from bank credits or balances, volatile inflows, large partner money at other banks (possible circular trading or inflated turnover).
6. Price and decide. Amount, ROI and tenure as the offer; floor ROI, maximum amount and maximum tenure as the negotiation limit, where the bank's risk-adjusted return (RAROC) still meets its hurdle; Approve, Refer or Decline with reasons. A flagged company that would be approved is referred with "Anomaly: ..." as the reason, never declined on it.
7. Rank. Exasol's PREFERRING skyline peels off the companies that are best on return, risk and size at the same time. The top 50 go to the relationship managers.
8. Shadow-score the challenger. A logistic regression scorecard, trained alongside the champion, scores the same companies and runs the same decision SQL, so the Models screen shows which grades and decisions it would change. It decides nothing until the data scientist requests its activation and the admin approves it.
9. Hand over. The data scientist reviews the draft run and publishes it to loan operations; the web app exports its offers to the CRM. Loan operations assigns the companies to relationship managers, each manager sees only her assigned companies (Exasol filters the rows), negotiates inside the limit and logs the loan status, and loan operations verifies and sanctions what goes above it. Admin, data scientist and loan operations follow every company on the Loan status tracker.

Every step is logged with its row count and duration, and the web app shows each one with the SQL, Lua or Python that ran.

3. Architecture#

Diagrams: all diagrams · big picture · AWS deployment · inside Exasol · analysis flow · roles and AI access · full design document

   Sapphire Bank (private RDS)                    Outside world (https://credora.link)
   Oracle CBS   MySQL CIF   PostgreSQL LOS        cibil.   gst.   aa.   (HTTPS relays)
      |             |             |                  |       |      |
  Oracle VS    adbc-vs-rs    adbc-vs-rs          Python SET UDFs, 16 in parallel
      \_____________|_____________/__________________|_______|______|
                                  |
               Exasol Personal on EC2 (r6i.xlarge, via the official launcher)
   virtual schemas · API UDFs · ML training and scoring · Lua orchestration · PREFERRING
   per-role users · masking · purge with proof · preprocessor framework · semantic layer
                                  |
          Web app at https://credora.loan (React + FastAPI behind Caddy, HTTPS)
     Admin · Data scientist · Loan operations · Relationship manager · Ask Credora (MCP)
       each role reads Exasol as its own user: CREDORA_ADMIN / _DATASCI / _LOANOPS / _RM

4. Exasol at the centre#

Credora is assembled from Exasol's open-source building blocks, from the exasol and exasol-labs GitHub organisations, plus the bank-specific logic in this repo. docs/WHY_EXASOL.md covers each feature in depth.

5. Role-based access, enforced by Exasol#

Access is decided by Exasol, not by the web app. Each of the four roles has its own Exasol user and database role (exasol/sql/07_security.sql). The backend runs every read for a signed-in person as that person's role user (backend/app/exa.py ROLE_USER, backend/app/routes/core.py db()); a query Exasol refuses comes back as 403 Exasol denied access for your role. Ask Credora starts the Exasol MCP server logged in as the same user (backend/app/assistant.py exasol_login()), so the AI can read exactly what the person can read, no more. Analysis, training, the CRM export and the purge run as the service user CREDORA_SVC.

Diagram: roles and AI access shows the four roles as rings of data, one Ask Credora question travelling from the login token through the topic gate and exasol_login() into Exasol as the role's own user, and what each role and its AI can and cannot do.

How the layers fit:

• Objects and columns are decided by Exasol grants. The latest-run views behind the semantic layer run with their owner's rights and are granted per role (exasol/semantic/11_latest_run_views.sql).
• Rows are decided by Exasol too. Loan operations' assignments live in CREDORA.RM_ASSIGNMENT (CIF_ID, RM_ID, EXA_USER, ASSIGNED_BY, ASSIGNED_AT), written by the backend as CREDORA_SVC. The RM's views (exasol/sql/13_rm_row_security.sql) join it on EXA_USER = CURRENT_USER; views run with their owner's rights, so R_RM is granted only these views and has no grant on DECISIONS, SCORES, FEATURES, APPLICATIONS or V_COMPANY_MASKED. The V_LATEST_* views (and so CREDORA_SEM.PORTFOLIO) apply the same filter to any user holding R_RM. The demo maps CREDORA_RM to RM001; in production each relationship manager gets an own Exasol user with R_RM and the same views work unchanged. The backend's own RM checks stay as defence in depth.
• Only published runs are official. A finished run is a draft until the data scientist publishes it (CREDORA.RUN_PUBLISH, written as CREDORA_SVC). V_LATEST_RUN, the RM views and the backend's current run all use the latest published run, so Exasol decides what loan operations and the RM see; the data scientist and the admin can still open drafts. An approved policy proposal's run is published by the system on the admin's behalf.
• Semantic SQL is routed per role by the preprocessor framework: one rule per role in exasol/extensions/10_semantic_views_module.sql. CREDORA_SVC has no rule and pays nothing.
• Demo book: python deploy/credora.py rm-book --env <name> runs exasol/sql/14_seed_rm_book.sql: 25 of the best pre-approved Top 50 companies for RM001, idempotent (nothing happens once RM001 has an assignment). A new deployment runs it after the first published analysis.
• The AI cannot write. The MCP server runs with enable_write_query, enable_write_bucketfs and the preprocessor tools switched off (MCP_SETTINGS in backend/app/assistant.py).

Verified on the live site: an admin question joined VS_CIF."partner", VS_LOS."application" and VS_CBS.TXN / ACCOUNT across three databases in one statement. Logged in to Exasol as CREDORA_RM: V_RM_DECISIONS returns 25 rows (her assigned companies, one published run), V_RM_COMPANY shows full PAN and GSTIN for them, CREDORA_SEM.PORTFOLIO counts 25 companies analysed where loan operations counts 1,000, and SELECT COUNT(*) FROM CREDORA.DECISIONS fails with insufficient privileges: SELECT on table DECISIONS.

See it in the app: every role has the Exasol control room, a map of the database read from Exasol's system tables with the role's own Exasol user. The admin's map shows every role's grants (EXA_DBA_*, through SELECT ANY DICTIONARY) and the live sessions; the RM's shows only her own grants, the virtual schemas with no objects visible, and Exasol's refusal to read CREDORA.MODEL_REGISTRY. The Exasol console (top bar, Explain mode) lists each statement your screens ran with the Exasol user it ran as.

6. Ask Credora: try these#

Ask Credora answers from the semantic layer CREDORA_SEM.PORTFOLIO first (16 certified metrics, exasol/semantic/12_credora_model.sql), then with plain SQL where the role allows it. Each answer lists every MCP tool call with the exact SQL it ran. A one-word topic check runs before any tool use: unrelated requests (poems, recipes, "ignore your rules") are refused and never reach Exasol.

The page suggests four questions per role, each exercising a different Exasol feature:

Ask a question outside a role's grants (for example the admin's cross-database question as rm) to see Exasol, not the prompt, set the limit.

7. Scale and measured performance#

The demo data set is generated, but its volumes are realistic for a mid-size corporate book.

RUN_ANALYSIS on the live deployment (one r6i.xlarge node):

Two risk models are trained together by TRAIN_MODEL on the same 5,000 past loans and measured on the same 25% hold-out:

A full data regeneration takes about 75 seconds.

8. Key decisions and trade-offs#

9. Security and data governance#

• Network: only Caddy (80, 443) is public. Exasol's ports (8563, 8443, 2581, 22) open only to admin IPs; the RDS databases sit in private subnets reachable from the VPC only.
• Least privilege in the database: each role reads as its own Exasol user (section 5). Loan operations cannot read raw outside data; relationship managers read only row-filtered views that return the companies assigned to them, so Exasol, not the app, keeps them to their own companies.
• AI inside the same boundary: Ask Credora's MCP server logs in as the signed-in role's Exasol user with writes switched off; a topic check refuses unrelated questions before any query; every SQL statement it ran is shown with the answer. Credit memos are generated inside Exasol by the CREDIT_MEMO UDF, with the API key held in CONNECTION AI_LLM.
• Secrets: generated per deployment by OpenTofu into AWS Secrets Manager; Exasol keeps API tokens in CONNECTION objects; nothing secret is in the repo.
• TLS to Exasol, pinned: the backend and the Exasol MCP server connect with host/<certificate fingerprint>:8563, so pyexasol refuses any certificate but the deployment's own (Exasol's is self-signed). The fingerprint comes from the launcher's connection-instructions.txt at ship time (EXASOL_FINGERPRINT), never from code. The Exasol Scheduler can only validate against CAs, so its connection, inside the VPC, does not check the certificate.
• Exasol passwords: PASSWORD_SECURITY_POLICY requires at least 12 characters with upper- and lower-case letters for every password set from now on; the generated passwords pass it, and no lock-out rule can take a role user's screens down.
• Audit by the database: Exasol's audit log records every statement; each session and statement names the app user and screen (credora:<user>:<feature>), and the admin reads it under Exasol audit.
• View owner: the owner's-rights views (V_COMPANY_MASKED, V_LATEST_*, V_RM_*, V_SESSION_SCOPE) are created by the deploy user SYS, so they read with DBA rights; each is granted only to the roles that may use it, and R_RM holds grants on its views only. A dedicated, minimally granted view owner is the next step, left out for now so the row-level-security views stay exactly as verified.
• Workload isolation: consumer groups keep the AI (CREDORA_AI, and 60 s, NICE and 2 GiB per Ask Credora session) behind the screens (CREDORA_APP) and the analysis (CREDORA_BATCH).
• Consent and retention: raw CIBIL, GST and Account Aggregator replies carry a purge time. The Exasol Scheduler runs PURGE_EXPIRED every 15 minutes, inside Exasol; it deletes what is due and keeps the row count and a SHA-256 fingerprint as proof (CREDORA.PURGE_LOG), and each run is a row in SCHED.SCHED_HISTORY. The scheduled task table is writable only by the deploy user.
• Time zone: the database keeps the launcher's EUROPE/BERLIN. PURGE_AT, RUN_LOG and the other timestamps are plain TIMESTAMP values written in that zone and compared with SYSTIMESTAMP; switching the database to ASIA/KOLKATA would shift every stored value by 4.5 hours (purges early, runs mis-dated) unless all of them were migrated with it. The backend converts every timestamp to UTC with DBTIMEZONE, the UI shows IST, and the scheduler's cron expressions name Asia/Kolkata themselves.
• Synthetic data only: every company, person and account is generated.

10. Failure modes#

11. Deploy it yourself#

Everything below runs from your own machine (Windows, macOS or Linux) against your own AWS account. One command builds the whole system; nothing in the code is tied to a particular account or domain.

Step 1. Install Exasol Personal (the exasol launcher). Follow the install steps in the Exasol Personal repository, then check it works:

exasol version

Step 2. Install the other tools.

Step 3. Prepare the AWS account.

1. Log the AWS CLI in to the account you want to use, under a profile name of your choice: bash aws configure --profile mysite # or: aws sso login --profile mysite / aws login --profile mysite aws sts get-caller-identity --profile mysite
2. Have a domain as a Route 53 hosted zone in that account (for example mybank.example). The app is served at its root, with docs., video. and ppt. beside it. A second hosted zone is optional: it becomes the "outside world" with public cibil., gst. and aa. relays.
3. The default sizes need 6 vCPUs of on-demand EC2 in the region (Exasol r6i.xlarge + app host t3.large). New accounts usually allow 8.

Step 4. Clone the repo and install the deploy tool.

git clone https://github.com/anamika-iit-kgp/credora.git
cd credora
make setup            # without make: uv venv deploy/.venv && uv pip install -p deploy/.venv -r deploy/requirements.txt

Step 5. Fill in the environment file for your deployment. The name after .env. is the deployment name (here mysite):

cp .env.example .env.mysite

Database, API and Exasol passwords are generated per deployment and stored in AWS Secrets Manager; you never type them. .env.* files are gitignored.

Step 6. Deploy.

python deploy/credora.py deploy --env mysite      # same as: make deploy ENV=mysite

On Windows use deploy\.venv\Scripts\python (macOS/Linux: deploy/.venv/bin/python) if python is not the deploy tool's virtual environment. The first deploy takes about an hour (the Oracle RDS instance is the slowest part) and prints the site address at the end. Then:

python deploy/credora.py access --env mysite      # writes deployments/mysite/ACCESS.md: every URL, login and password

Open https://<DOMAIN> and sign in with a demo user (admin, datasci, loanops, rm; password Demo@123 unless you set one).

deploy is idempotent (safe to re-run after a failure) and builds everything in order:

1. Exasol Personal on EC2 through the official launcher, its ports limited to your IP.
2. RDS for Oracle, MySQL and PostgreSQL, the app host, S3, Secrets Manager and DNS for both domains, with OpenTofu.
3. The web app, API, mock APIs, generator, docs, video and presentation sites, with Docker Compose.
4. 1,000 companies of synthetic data.
5. In Exasol: the Rust container, both virtual schema adapters, the Oracle JDBC driver, connections, UDFs, Lua scripts, virtual schemas, roles, users, consumer groups and the password policy, the scheduler's tables and jobs, the preprocessor framework, the semantic model, the preprocessor-library modules and the comments on every object.
6. The first model training and analysis.

Everything local about a deployment (launcher directory, OpenTofu state, SSH keys, logs, ACCESS.md) lives in deployments/<name>/, which is gitignored. Copy it with .env.<name> to operate the same deployment from another machine. See docs/PORTING.md.

12. Repository layout#

credora/
├─ deploy/        one-command deploy tool, remote start script
├─ infra/         OpenTofu: RDS x3, app host, S3, secrets, Route 53 for both domains
├─ exasol/
│  ├─ sql/        schemas, connections, virtual schemas, security, row security, comments,
│  │              consumer groups, the scheduler's tables and jobs
│  ├─ udf/        Python UDFs: outside APIs, ML training and scoring, AI memo
│  ├─ lua/        RUN_ANALYSIS, TRAIN_MODEL, PURGE_EXPIRED
│  ├─ extensions/ the semantic layer as a preprocessor-framework module
│  └─ semantic/   latest-run views and the Credora semantic model
├─ db/            Sapphire Bank schemas (Oracle, MySQL, PostgreSQL) and the mock API schemas
├─ mocks/         mock CIBIL, GSTN and Account Aggregator APIs; synthetic data generator
├─ backend/       FastAPI: roles, jobs, reports, CRM export, Ask Credora, Exasol audit
├─ scheduler/     the Exasol Scheduler container (pinned release binary)
├─ tests/udf/     UDF unit tests with exasol/udf-mock-python
├─ .github/       CI: backend tests, frontend build, UDF and generator tests, deploy tools
├─ frontend/      React + TypeScript
├─ sites/         docs, video, presentation and outside-world sites
├─ media/         demo video and presentation files
└─ docs/          architecture, diagrams, data model, APIs, why Exasol, porting

13. Documentation#

14. Built with Exasol open source: thank you#

Credora is assembled from Exasol's own products and open-source projects, from both the exasol and the exasol-labs GitHub organisations. Every one below runs in the live deployment or in CI; the versions are pinned in deploy/exasol_setup.py and the requirements files. Thank you to the Exasol team and the contributors of these projects.

15. About this project, contributing and contact#

Credora is an ExaThon India 2026 project: a hands-on try-out of the Exasol Personal Local Starter Kit and the Exasol Personal product, deployed on AWS to show how far one analytics database can carry a real banking workflow: federation, in-database ML, AI, governance and security in one place.

• Contributions are welcome. Fork the repo and open a pull request; for a larger change, open an issue first to discuss it.
• Want to build on Credora, adapt it for your bank, or talk about buying or licensing it? Raise an issue in this repository and we will get in touch.
• Found a bug or have a question about how something works in Exasol? An issue is the right place too.

Project by Anamika, Product Manager @ Equitas Small Finance Bank. The live deployment runs in my own AWS account, on the domains credora.loan and credora.link, which I registered for this project. Sapphire Bank is fictional and all data is synthetic.

License#

MIT. See LICENSE.