Loan Operations Database

Introduction

30 Second Summary

A loan payment can look complete in one record while the customer's balance tells a different story. Once those records disagree, every repayment report becomes harder to trust.

In this project, you will build a local loan-operations database with PostgreSQL that treats each EMI payment as one protected financial operation. You will also turn its connected records into live delinquency reports.

What You'll Build

You'll demo a repayment moving safely through the account ledger before the updated delinquency position appears in your reports.

By the end of this project, you'll have:

• A connected loan history that traces each customer from account activity to scheduled installments and payments.
• An atomic payment workflow where a rollback removes every related change.
• Live delinquency reports that surface overdue borrowers while supporting risk ranking and execution-plan comparison.
• Secret Mission: Detect long gaps between successive repayments with a window function.

Are there any prerequisites?

You'll need a Windows computer plus intermediate SQL familiarity.

Step 1 guides you through installing PostgreSQL 18 and pgAdmin 4 as free local software.

Before We Start

Before the hands-on work begins, lock in what this database must protect. You will also define the delinquency question it must answer.

An EMI payment is a multi-record financial operation. The database must keep its payment record consistent with the account balance.

The reporting target is equally specific. The database must identify borrowers with overdue unpaid installments.

Install and Connect PostgreSQL

Your payment workflow needs a database engine that can coordinate several related records as one operation. PostgreSQL gives the project that transaction boundary.

pgAdmin gives you a visual workspace for running SQL. This step installs the local tools before confirming the exact server version and database connection.

In this step, get ready to:

• Install the bundled local database toolset.
• Connect pgAdmin to the local PostgreSQL server.
• Verify the server version and active database.

Install the local database tools

The interactive Windows installer bundles the database server with pgAdmin and the command-line tools. This gives you every local tool required by the project without adding optional modules.

Why PostgreSQL for this project?

PostgreSQL supports stored procedures. It also supports row-level triggers.

Its transaction controls keep related writes together. Its execution plans support the reporting work later.

• Choose the tab that matches the database tools currently available on your computer.

✔️ The required version is installed

PostgreSQL 18 is already installed with pgAdmin 4 and the command-line tools.

• Continue to the pgAdmin connection substep below.

ⓧ I have an older version

This project uses PostgreSQL 18. Back up any databases you need from the older installation before changing your local database setup.

• Visit the official PostgreSQL Windows page.
• Download the interactive Windows installer for PostgreSQL 18.

You should see the downloaded installer file in your Windows Downloads folder.

• Run the downloaded installer.
• Approve the Windows permission request if it appears.
• Keep the default toolset selected on the Select Components screen.

The selected toolset should show PostgreSQL Server, pgAdmin 4, and Command Line Tools.

The local postgres password is a credential for this computer. A password manager keeps it private and available for the connection step.

• Enter a password for the local postgres user.
• Store the password in your password manager.

• Keep the listener port set to 5432.
• Allow the installer to install the selected components.
• Leave Stack Builder unselected when the installer reaches completion.

You should reach the installation completion screen with PostgreSQL 18 installed.

ⓧ PostgreSQL is not installed

The official Windows installer provides the server and its local management tools in one setup flow.

• Visit the official PostgreSQL Windows page.
• Download the interactive Windows installer for PostgreSQL 18.

You should see the downloaded installer file in your Windows Downloads folder.

• Run the downloaded installer.
• Approve the Windows permission request if it appears.
• Keep the default toolset selected on the Select Components screen.

The selected toolset should show PostgreSQL Server, pgAdmin 4, and Command Line Tools.

The local postgres password is a credential for this computer. A password manager keeps it private and available for the connection step.

• Enter a password for the local postgres user.
• Store the password in your password manager.

• Keep the listener port set to 5432.
• Allow the installer to install the selected components.
• Leave Stack Builder unselected when the installer reaches completion.

You should reach the installation completion screen with PostgreSQL 18 installed.

That is the longest setup task complete. Your Windows computer now has the local database server and its management tools.

Did the installation stop early?

• Run the downloaded installer again with a Windows account that can install software.
• Confirm that the default toolset remains selected on the Select Components screen.
• Keep port 5432 available for the local PostgreSQL server.

Help me diagnose a PostgreSQL 18 Windows installation problem.

Connect with pgAdmin

A pgAdmin server registration stores the address and database identity needed for a connection. Your registration points to the PostgreSQL server running on this computer.

• Press the Windows key to open the Windows search bar.
• Type pgAdmin 4 and press Enter to open it.

You should see pgAdmin with the Servers group in the browser panel.

• Choose the tab that matches what appears under Servers.

✔️ The local server is listed

• Expand Servers in the browser panel.
• Select the bundled local PostgreSQL server.
• Enter the retained postgres password if pgAdmin requests it.
• Expand Databases under the connected server.
• Select the postgres database.
• Open Query Tool for the selected database.

You should see an empty Query Tool editor connected to the postgres database.

ⓧ The local server is missing

• Right-click Servers in the browser panel.
• Select Register.
• Select Server.

The registration tells pgAdmin how to reach the local server. The values below point to the installation on this computer.

• Give the registration a local name that you will recognize.
• Enter localhost in Host name/address.
• Enter 5432 in Port.
• Enter postgres in Maintenance database.
• Enter postgres in Username.
• Enter the retained local password in Password.
• Use the dialog confirmation control to save the registration.

The registered local server should now appear under Servers.

• Expand Databases under the registered server.
• Select the postgres database.
• Open Query Tool for the selected database.

You should see an empty Query Tool editor connected to the postgres database.

Cannot connect to the local server?

• Confirm that Host name/address contains localhost.
• Confirm that Port contains 5432.
• Re-enter the local postgres password stored during installation.

Help me troubleshoot my local pgAdmin connection.

Confirm the environment

A successful connection proves that pgAdmin can reach the server. The final query confirms that the connection also targets the required PostgreSQL version and maintenance database.

• Predict the server version and database name that the result will show.

• Run the environment check in Query Tool by using this query:

SELECT version(), current_database();

What does this query confirm?

• version() returns text describing the connected PostgreSQL server version.
• current_database() returns the name of the database used by this Query Tool connection.

The result should identify PostgreSQL 18 in the version column. The database column should show postgres.

You have a working local database connection. The engine is ready for the normalized loan portfolio.

• Keep Query Tool open and connected to the postgres database.

No result from the version check?

• Confirm that the Query Tool tab is connected to the postgres database.
• Select the complete query before running it again.
• Reconnect the registered local server if pgAdmin shows a disconnected state.

Help me diagnose my Query Tool version check.

Your local PostgreSQL environment is ready. Next up, you will build the normalized schema and load its starting loan portfolio.

Build the Normalized Loan Portfolio

Your local PostgreSQL 18 workspace is connected to the postgres database. You can now turn that empty workspace into a working loan portfolio.

Reliable reporting needs each fact to have one clear home. The relational schema must also reject invalid links between customers, accounts, loans, or installments.

The Query Tool in pgAdmin 4 gives you one place to create those protections. You will then load a small portfolio whose dates stay meaningful whenever you run the scripts.

In this step, get ready to:

• Create seven normalized tables with practical constraints.
• Load customers, accounts, transactions, loans, installments, or payments.
• Query the finished portfolio to confirm its row counts.

Create the relational schema

Normalization separates each kind of fact into a focused table. Constraints protect the links between those tables before unreliable data reaches a report.

• Recreate the isolated loan_ops schema by running this SQL in the Query Tool from earlier:

DROP SCHEMA IF EXISTS loan_ops CASCADE;
CREATE SCHEMA loan_ops;
SET search_path TO loan_ops, public;

What does this SQL do?

• The first statement removes an earlier loan_ops schema so the project starts from a repeatable state.
• The second statement creates the clean project schema.
• The third statement makes loan_ops the first location PostgreSQL checks for unqualified object names.

• Save the current query as 01_schema.sql.
• Confirm the Query Tool reports that the schema statements completed successfully.

Your project now has an isolated namespace. Every table in this script lands inside loan_ops.

Schema reset failed?

Confirm the Query Tool is still connected to the local postgres database. Run the three statements together so the search path changes in the same session.

Help me troubleshoot the loan_ops schema creation in PostgreSQL.

• Append the customer table definition to 01_schema.sql by copying this SQL below the existing statements:

CREATE TABLE customers (
    customer_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    full_name varchar(100) NOT NULL,
    email varchar(255) NOT NULL UNIQUE,
    phone varchar(25) NOT NULL,
    created_at date NOT NULL DEFAULT CURRENT_DATE
);

How are customers protected?

• The identity primary key gives each customer an automatically generated identifier.
• The unique email constraint prevents two customer rows from sharing one business identifier.
• The required fields prevent incomplete customer records.

• Save 01_schema.sql.
• Select the new CREATE TABLE customers statement.
• Run the selected statement.

You now have the customers table ready to anchor account ownership.

Customer table not created?

Check that the opening parenthesis has a matching closing parenthesis. Confirm the statement ends after the closing parenthesis.

Help me fix the customers table definition.

• Append the account table definition to 01_schema.sql by copying this SQL below the customer table:

CREATE TABLE accounts (
    account_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint NOT NULL,
    account_number varchar(20) NOT NULL UNIQUE,
    account_type varchar(10) NOT NULL CHECK (account_type IN ('SAVINGS', 'CURRENT')),
    balance numeric(14,2) NOT NULL CHECK (balance >= 0),
    status varchar(10) NOT NULL DEFAULT 'ACTIVE' CHECK (status IN ('ACTIVE', 'FROZEN', 'CLOSED')),
    CONSTRAINT fk_accounts_customer
        FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE RESTRICT,
    CONSTRAINT uq_account_owner UNIQUE (account_id, customer_id)
);

How are accounts protected?

• The checks limit account types to the supported values.
• The balance check rejects negative cached balances.
• The customer foreign key requires a valid owner.
• The composite unique constraint prepares an ownership key that loans can reference.

How Ownership Stays Intact

A loan stores both its customer and disbursement account. The composite account ownership key later lets PostgreSQL verify that both identifiers describe the same relationship.

This database-level rule protects every insert path. A future application cannot bypass it with an incorrect account choice.

• Save 01_schema.sql.
• Select the new CREATE TABLE accounts statement.
• Run the selected statement.

The accounts table now accepts valid account states while preserving customer ownership.

Account table rejected?

Confirm the customers table was created first. The account foreign key depends on it.

Help me resolve the accounts table dependency or constraint error.

• Append the ledger table definition to 01_schema.sql by copying this SQL below the account table:

CREATE TABLE account_transactions (
    transaction_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    account_id bigint NOT NULL REFERENCES accounts(account_id) ON DELETE RESTRICT,
    transaction_type varchar(6) NOT NULL CHECK (transaction_type IN ('CREDIT', 'DEBIT')),
    amount numeric(14,2) NOT NULL CHECK (amount > 0),
    transaction_time timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    reference_code varchar(40) NOT NULL UNIQUE,
    description varchar(200) NOT NULL
);

What does the ledger retain?

• Each row records one credit or debit against a valid account.
• Positive amounts keep transaction direction inside transaction_type.
• Unique reference codes prevent duplicate business events.

• Save 01_schema.sql.
• Select the new CREATE TABLE account_transactions statement.
• Run the selected statement.

You now have an auditable account ledger in account_transactions.

Ledger table failed?

Confirm the referenced accounts table exists. Check that every constraint remains inside the table parentheses.

Help me diagnose the account_transactions table statement.

• Append the loan table definition to 01_schema.sql by copying this SQL below the ledger table:

CREATE TABLE loans (
    loan_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint NOT NULL,
    disbursement_account_id bigint NOT NULL,
    principal_amount numeric(14,2) NOT NULL CHECK (principal_amount > 0),
    annual_interest_rate numeric(5,2) NOT NULL CHECK (annual_interest_rate BETWEEN 0 AND 100),
    term_months integer NOT NULL CHECK (term_months > 0),
    start_date date NOT NULL,
    loan_status varchar(10) NOT NULL DEFAULT 'ACTIVE' CHECK (loan_status IN ('ACTIVE', 'CLOSED', 'DEFAULTED')),
    CONSTRAINT fk_loans_customer
        FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE RESTRICT,
    CONSTRAINT fk_loan_account_owner
        FOREIGN KEY (disbursement_account_id, customer_id)
        REFERENCES accounts(account_id, customer_id) ON DELETE RESTRICT
);

How are loans validated?

• Positive principal values prevent empty loan contracts.
• The rate check keeps annual interest between the defined bounds.
• The customer foreign key requires an existing borrower.
• The composite foreign key requires the disbursement account to belong to that borrower.

• Save 01_schema.sql.
• Select the new CREATE TABLE loans statement.
• Run the selected statement.

The loans table now rejects a disbursement account owned by a different customer.

Loan ownership constraint failed?

Confirm the accounts table includes the composite unique constraint on account_id plus customer_id.

Help me fix the composite loan ownership foreign key.

• Append the installment table definition to 01_schema.sql by copying this SQL below the loan table:

CREATE TABLE emi_schedules (
    emi_schedule_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    loan_id bigint NOT NULL REFERENCES loans(loan_id) ON DELETE RESTRICT,
    installment_number integer NOT NULL CHECK (installment_number > 0),
    due_date date NOT NULL,
    principal_due numeric(12,2) NOT NULL CHECK (principal_due >= 0),
    interest_due numeric(12,2) NOT NULL CHECK (interest_due >= 0),
    amount_due numeric(12,2) NOT NULL CHECK (amount_due > 0),
    CONSTRAINT ck_emi_amount_parts CHECK (principal_due + interest_due = amount_due),
    CONSTRAINT uq_loan_installment UNIQUE (loan_id, installment_number)
);

How are installments protected?

• Each schedule row belongs to an existing loan.
• The amount-parts check requires principal plus interest to equal the installment total.
• The composite unique constraint prevents duplicate installment numbers within one loan.

• Save 01_schema.sql.
• Select the new CREATE TABLE emi_schedules statement.
• Run the selected statement.

Your loan model can now store a dated repayment schedule with internally consistent amounts.

Installment table failed?

Confirm the loans table exists. Check the amount-parts constraint for matching column names.

Help me troubleshoot the emi_schedules table definition.

• Append the payment table definition to 01_schema.sql by copying this SQL below the installment table:

CREATE TABLE emi_payments (
    payment_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    emi_schedule_id bigint NOT NULL REFERENCES emi_schedules(emi_schedule_id) ON DELETE RESTRICT,
    account_id bigint NOT NULL REFERENCES accounts(account_id) ON DELETE RESTRICT,
    amount_paid numeric(12,2) NOT NULL CHECK (amount_paid > 0),
    payment_date date NOT NULL DEFAULT CURRENT_DATE,
    reference_code varchar(40) NOT NULL UNIQUE
);

What does a payment record?

• Each payment points to one scheduled installment.
• Each payment also records the funding account.
• The positive-amount check rejects empty or reversed payment values.
• The unique reference code gives the payment a traceable business identifier.

• Save 01_schema.sql.
• Select the new CREATE TABLE emi_payments statement.
• Run the selected statement.

The emi_payments table can now connect received money to an installment plus its funding account.

Payment table failed?

Confirm both emi_schedules and accounts already exist. The payment table depends on both.

Help me resolve the emi_payments table dependency error.

• Append the audit table definition to 01_schema.sql by copying this SQL below the payment table:

CREATE TABLE payment_audit (
    audit_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    payment_id bigint,
    operation varchar(6) NOT NULL CHECK (operation IN ('INSERT', 'UPDATE', 'DELETE')),
    old_amount numeric(12,2),
    new_amount numeric(12,2),
    changed_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

What will the audit table capture?

• The operation column distinguishes inserted, updated, or deleted payments.
• The old and new amount columns preserve the value transition.
• The timestamp records when PostgreSQL observed the change.

• Save 01_schema.sql.
• Select the new CREATE TABLE payment_audit statement.
• Run the selected statement.

You now have all seven tables. The audit table is ready for the trigger added later in the project.

Audit table failed?

Check that each operation value remains inside the allowed operation list. Confirm the final table parenthesis is present.

Help me fix the payment_audit table statement.

✔️ Awesome, I've got everything!

Great. Save 01_schema.sql before rebuilding the schema from the complete script.

ⓧ I'd like to double check the full code

DROP SCHEMA IF EXISTS loan_ops CASCADE;
CREATE SCHEMA loan_ops;
SET search_path TO loan_ops, public;

CREATE TABLE customers (
    customer_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    full_name varchar(100) NOT NULL,
    email varchar(255) NOT NULL UNIQUE,
    phone varchar(25) NOT NULL,
    created_at date NOT NULL DEFAULT CURRENT_DATE
);

CREATE TABLE accounts (
    account_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint NOT NULL,
    account_number varchar(20) NOT NULL UNIQUE,
    account_type varchar(10) NOT NULL CHECK (account_type IN ('SAVINGS', 'CURRENT')),
    balance numeric(14,2) NOT NULL CHECK (balance >= 0),
    status varchar(10) NOT NULL DEFAULT 'ACTIVE' CHECK (status IN ('ACTIVE', 'FROZEN', 'CLOSED')),
    CONSTRAINT fk_accounts_customer
        FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE RESTRICT,
    CONSTRAINT uq_account_owner UNIQUE (account_id, customer_id)
);

CREATE TABLE account_transactions (
    transaction_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    account_id bigint NOT NULL REFERENCES accounts(account_id) ON DELETE RESTRICT,
    transaction_type varchar(6) NOT NULL CHECK (transaction_type IN ('CREDIT', 'DEBIT')),
    amount numeric(14,2) NOT NULL CHECK (amount > 0),
    transaction_time timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    reference_code varchar(40) NOT NULL UNIQUE,
    description varchar(200) NOT NULL
);

CREATE TABLE loans (
    loan_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint NOT NULL,
    disbursement_account_id bigint NOT NULL,
    principal_amount numeric(14,2) NOT NULL CHECK (principal_amount > 0),
    annual_interest_rate numeric(5,2) NOT NULL CHECK (annual_interest_rate BETWEEN 0 AND 100),
    term_months integer NOT NULL CHECK (term_months > 0),
    start_date date NOT NULL,
    loan_status varchar(10) NOT NULL DEFAULT 'ACTIVE' CHECK (loan_status IN ('ACTIVE', 'CLOSED', 'DEFAULTED')),
    CONSTRAINT fk_loans_customer
        FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE RESTRICT,
    CONSTRAINT fk_loan_account_owner
        FOREIGN KEY (disbursement_account_id, customer_id)
        REFERENCES accounts(account_id, customer_id) ON DELETE RESTRICT
);

CREATE TABLE emi_schedules (
    emi_schedule_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    loan_id bigint NOT NULL REFERENCES loans(loan_id) ON DELETE RESTRICT,
    installment_number integer NOT NULL CHECK (installment_number > 0),
    due_date date NOT NULL,
    principal_due numeric(12,2) NOT NULL CHECK (principal_due >= 0),
    interest_due numeric(12,2) NOT NULL CHECK (interest_due >= 0),
    amount_due numeric(12,2) NOT NULL CHECK (amount_due > 0),
    CONSTRAINT ck_emi_amount_parts CHECK (principal_due + interest_due = amount_due),
    CONSTRAINT uq_loan_installment UNIQUE (loan_id, installment_number)
);

CREATE TABLE emi_payments (
    payment_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    emi_schedule_id bigint NOT NULL REFERENCES emi_schedules(emi_schedule_id) ON DELETE RESTRICT,
    account_id bigint NOT NULL REFERENCES accounts(account_id) ON DELETE RESTRICT,
    amount_paid numeric(12,2) NOT NULL CHECK (amount_paid > 0),
    payment_date date NOT NULL DEFAULT CURRENT_DATE,
    reference_code varchar(40) NOT NULL UNIQUE
);

CREATE TABLE payment_audit (
    audit_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    payment_id bigint,
    operation varchar(6) NOT NULL CHECK (operation IN ('INSERT', 'UPDATE', 'DELETE')),
    old_amount numeric(12,2),
    new_amount numeric(12,2),
    changed_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

The complete schema script is designed to rebuild the project safely before data is loaded. Running it now proves the table order satisfies every dependency.

• Select the complete contents of 01_schema.sql.
• Run the selected script.

You should see every statement complete successfully. The rebuilt loan_ops schema now contains all seven empty tables.

Full schema script stopped?

Use the first reported statement location to find the failed table. Compare that table with the full-file reference before running the entire script again.

Help me identify why the complete schema script stops.

Load a consistent starting portfolio

The schema now protects structure. The seed data adds a connected portfolio whose account balances, ledger references, schedules, or payments agree from the start.

• Start a new query tab in the Query Tool.
• Target loan_ops while inserting four customers by running this SQL:

SET search_path TO loan_ops, public;

INSERT INTO customers (full_name, email, phone) VALUES
('Alice Morgan', 'alice.morgan@example.test', '+1-555-0101'),
('Brian Okafor', 'brian.okafor@example.test', '+1-555-0102'),
('Chitra Rao', 'chitra.rao@example.test', '+1-555-0103'),
('Diego Santos', 'diego.santos@example.test', '+1-555-0104');

What does this seed establish?

• The search path directs the remaining seed statements to loan_ops.
• The insert creates four borrowers with unique email addresses.
• Their insertion order gives them customer identifiers from 1 through 4.

• Save the current query as 02_seed.sql.
• Confirm the Query Tool reports that four customer rows were inserted.

Your portfolio now has Alice Morgan, Brian Okafor, Chitra Rao, or Diego Santos as its four borrowers.

Customer seed failed?

Confirm the schema script completed after its final rebuild. A duplicate email means the seed statement has already run against the current schema.

Help me diagnose the customer seed insert.

• Append the funded account data to 02_seed.sql by copying this SQL below the customer insert:

INSERT INTO accounts (customer_id, account_number, account_type, balance) VALUES
(1, 'ACCT-1001', 'SAVINGS', 6000.00),
(2, 'ACCT-1002', 'SAVINGS', 2500.00),
(3, 'ACCT-1003', 'CURRENT', 12000.00),
(4, 'ACCT-1004', 'SAVINGS', 1500.00);

How do the accounts connect?

• Each account references the customer created in the matching insertion position.
• Account 1 belongs to Alice Morgan.
• Alice's cached balance starts at 6000.00.

• Save 02_seed.sql.
• Select the new account insert.
• Run the selected statement.

The portfolio now has four funded accounts with one account assigned to each customer.

Account seed failed?

Confirm the four customer rows were inserted first. A foreign key failure means at least one referenced customer identifier is unavailable.

Help me fix the account seed relationships.

• Append the opening ledger history to 02_seed.sql by copying this SQL below the account insert:

INSERT INTO account_transactions
    (account_id, transaction_type, amount, reference_code, description)
VALUES
(1, 'CREDIT', 12000.00, 'OPEN-A-1001', 'Opening and salary credits'),
(1, 'DEBIT', 2500.00, 'EMI-A-001', 'Loan 1 installment 1'),
(1, 'DEBIT', 1000.00, 'EMI-A-002A', 'Partial loan 1 installment 2'),
(1, 'DEBIT', 2500.00, 'SPEND-A-001', 'Card and transfer activity'),
(2, 'CREDIT', 3000.00, 'OPEN-B-1002', 'Opening credit'),
(2, 'DEBIT', 500.00, 'SPEND-B-001', 'Card activity'),
(3, 'CREDIT', 18200.00, 'OPEN-C-1003', 'Opening and business credits'),
(3, 'DEBIT', 3200.00, 'EMI-C-001', 'Loan 3 installment 1'),
(3, 'DEBIT', 3000.00, 'SPEND-C-001', 'Business payment'),
(4, 'CREDIT', 1500.00, 'OPEN-D-1004', 'Opening credit');

What does the ledger show?

• Opening credits establish the source of each account's funds.
• Debit rows record loan repayments or other account activity.
• The references EMI-A-001 plus EMI-A-002A identify Alice's existing EMI debits.
• The reference EMI-C-001 identifies Chitra's existing EMI debit.

• Save 02_seed.sql.
• Select the new ledger insert.
• Run the selected statement.

Your ledger now contains ten traceable transactions across the four accounts.

Ledger seed failed?

Confirm all four account rows exist. A duplicate reference means part of this ledger data is already present.

Help me diagnose the account transaction seed insert.

• Append the loan data to 02_seed.sql by copying this SQL below the ledger insert:

INSERT INTO loans
    (customer_id, disbursement_account_id, principal_amount, annual_interest_rate, term_months, start_date)
VALUES
(1, 1, 9000.00, 8.00, 4, CURRENT_DATE - 90),
(2, 2, 6400.00, 10.00, 4, CURRENT_DATE - 75),
(3, 3, 12000.00, 7.50, 4, CURRENT_DATE - 60);

How do the loans stay current?

• The three loans connect Alice, Brian, or Chitra to their matching accounts.
• Dates derived from CURRENT_DATE keep the portfolio aligned with the day you run the script.
• The ownership foreign key verifies each customer-account pair during insertion.

• Save 02_seed.sql.
• Select the new loan insert.
• Run the selected statement.

The portfolio now contains three loans with valid customer ownership.

Loan seed failed?

Confirm each loan uses a customer identifier that matches the owner of its disbursement account. The composite foreign key rejects mismatched pairs.

Help me resolve the loan seed ownership failure.

• Append the installment schedule data to 02_seed.sql by copying this SQL below the loan insert:

INSERT INTO emi_schedules
    (loan_id, installment_number, due_date, principal_due, interest_due, amount_due)
VALUES
(1, 1, CURRENT_DATE - 60, 2250.00, 250.00, 2500.00),
(1, 2, CURRENT_DATE - 30, 2250.00, 250.00, 2500.00),
(1, 3, CURRENT_DATE + 5, 2250.00, 250.00, 2500.00),
(1, 4, CURRENT_DATE + 35, 2250.00, 250.00, 2500.00),
(2, 1, CURRENT_DATE - 45, 1600.00, 200.00, 1800.00),
(2, 2, CURRENT_DATE - 15, 1600.00, 200.00, 1800.00),
(2, 3, CURRENT_DATE + 15, 1600.00, 200.00, 1800.00),
(2, 4, CURRENT_DATE + 45, 1600.00, 200.00, 1800.00),
(3, 1, CURRENT_DATE - 30, 3000.00, 200.00, 3200.00),
(3, 2, CURRENT_DATE - 1, 3000.00, 200.00, 3200.00),
(3, 3, CURRENT_DATE + 29, 3000.00, 200.00, 3200.00),
(3, 4, CURRENT_DATE + 59, 3000.00, 200.00, 3200.00);

How does the schedule stay meaningful?

• Each loan receives four numbered installments.
• Relative dates create overdue installments plus upcoming installments whenever the script runs.
• Schedule 2 belongs to loan 1.
• That schedule has an amount due of 2500.00.

• Save 02_seed.sql.
• Select the new installment insert.
• Run the selected statement.

You now have twelve EMI schedules spread across the three loans.

Schedule seed failed?

Confirm the three loan rows exist. Check that every principal amount plus interest amount equals its matching total.

Help me find the invalid installment seed row.

• Append the existing payment history to 02_seed.sql by copying this SQL below the schedule insert:

INSERT INTO emi_payments
    (emi_schedule_id, account_id, amount_paid, payment_date, reference_code)
VALUES
(1, 1, 2500.00, CURRENT_DATE - 58, 'EMI-A-001'),
(2, 1, 1000.00, CURRENT_DATE - 28, 'EMI-A-002A'),
(9, 3, 3200.00, CURRENT_DATE - 29, 'EMI-C-001');

How do payments align with the ledger?

• The three payment references match the related debit references already stored in account_transactions.
• Schedule 2 receives a partial payment of 1000.00.
• Its remaining unpaid amount is therefore 1500.00.

• Save 02_seed.sql.
• Select the new payment insert.
• Run the selected statement.

The seed portfolio now has three existing EMI payments with matching ledger references.

Payment seed failed?

Confirm all twelve schedule rows exist. A duplicate reference means one of the matching payments is already present.

Help me troubleshoot the EMI payment seed insert.

Query the visible baby version

A common table expression can calculate payment totals before the main report uses them. Joins then reconnect the normalized customer, loan, schedule, or payment facts.

• Start a temporary query tab in the Query Tool.
• Display one summary row per loan by running this SQL:

WITH payment_totals AS (
    SELECT emi_schedule_id, SUM(amount_paid) AS total_paid
    FROM emi_payments
    GROUP BY emi_schedule_id
)
SELECT
    c.full_name,
    l.loan_id,
    SUM(es.amount_due) AS scheduled_total,
    SUM(COALESCE(pt.total_paid, 0)) AS paid_total,
    SUM(es.amount_due - COALESCE(pt.total_paid, 0)) AS remaining_total
FROM customers c
JOIN loans l ON l.customer_id = c.customer_id
JOIN emi_schedules es ON es.loan_id = l.loan_id
LEFT JOIN payment_totals pt ON pt.emi_schedule_id = es.emi_schedule_id
GROUP BY c.full_name, l.loan_id
ORDER BY l.loan_id;

What does the portfolio query calculate?

• The CTE adds all payments for each schedule.
• The main query groups schedules under their customer plus loan.
• The left join keeps unpaid schedules in the report.
• The final expressions compare scheduled totals with paid totals to calculate what remains.

You should see three loan rows. Alice Morgan has 10000.00 scheduled with 3500.00 paid.

Brian Okafor has 7200.00 remaining. Chitra Rao has 9600.00 remaining.

Portfolio rows missing?

Confirm the temporary tab uses the same Query Tool connection. Run the search-path statement from 02_seed.sql in that tab if PostgreSQL cannot find the project tables.

Help me troubleshoot the normalized portfolio summary query.

The report computes paid totals from payment rows. It computes remaining totals from schedules plus those payments.

Overdue status stays derived from due dates plus payment totals. This keeps a stored status from drifting away from the source records.

• Switch back to 02_seed.sql.
• Append the final row-count check by copying this SQL below the payment insert:

SELECT
    (SELECT COUNT(*) FROM customers) AS customers,
    (SELECT COUNT(*) FROM accounts) AS accounts,
    (SELECT COUNT(*) FROM loans) AS loans,
    (SELECT COUNT(*) FROM emi_schedules) AS emi_schedules,
    (SELECT COUNT(*) FROM emi_payments) AS emi_payments;

What does the final check measure?

Each scalar count measures one required part of the starting portfolio. One result row makes the complete seed state easy to compare.

• Save 02_seed.sql.

Before you run the final check, do you expect the portfolio summary to have changed any stored row counts?

• Select the final count query in 02_seed.sql.
• Run the selected query.

You should see 4 customers, 4 accounts, 3 loans, 12 EMI schedules, or 3 EMI payments.

That is the full starting portfolio loaded. Your normalized facts now agree across customers, accounts, loans, schedules, payments, or ledger references.

Counts do not match?

A smaller count usually means an earlier insert did not complete. A larger count means part of the seed script ran more than once after the schema rebuild.

Rebuild with 01_schema.sql before rerunning 02_seed.sql from the beginning.

Help me reconcile the final seed counts.

✔️ Awesome, I've got everything!

Great. Keep 02_seed.sql saved with the final count query at the bottom.

ⓧ I'd like to double check the full code

SET search_path TO loan_ops, public;

INSERT INTO customers (full_name, email, phone) VALUES
('Alice Morgan', 'alice.morgan@example.test', '+1-555-0101'),
('Brian Okafor', 'brian.okafor@example.test', '+1-555-0102'),
('Chitra Rao', 'chitra.rao@example.test', '+1-555-0103'),
('Diego Santos', 'diego.santos@example.test', '+1-555-0104');

INSERT INTO accounts (customer_id, account_number, account_type, balance) VALUES
(1, 'ACCT-1001', 'SAVINGS', 6000.00),
(2, 'ACCT-1002', 'SAVINGS', 2500.00),
(3, 'ACCT-1003', 'CURRENT', 12000.00),
(4, 'ACCT-1004', 'SAVINGS', 1500.00);

INSERT INTO account_transactions
    (account_id, transaction_type, amount, reference_code, description)
VALUES
(1, 'CREDIT', 12000.00, 'OPEN-A-1001', 'Opening and salary credits'),
(1, 'DEBIT', 2500.00, 'EMI-A-001', 'Loan 1 installment 1'),
(1, 'DEBIT', 1000.00, 'EMI-A-002A', 'Partial loan 1 installment 2'),
(1, 'DEBIT', 2500.00, 'SPEND-A-001', 'Card and transfer activity'),
(2, 'CREDIT', 3000.00, 'OPEN-B-1002', 'Opening credit'),
(2, 'DEBIT', 500.00, 'SPEND-B-001', 'Card activity'),
(3, 'CREDIT', 18200.00, 'OPEN-C-1003', 'Opening and business credits'),
(3, 'DEBIT', 3200.00, 'EMI-C-001', 'Loan 3 installment 1'),
(3, 'DEBIT', 3000.00, 'SPEND-C-001', 'Business payment'),
(4, 'CREDIT', 1500.00, 'OPEN-D-1004', 'Opening credit');

INSERT INTO loans
    (customer_id, disbursement_account_id, principal_amount, annual_interest_rate, term_months, start_date)
VALUES
(1, 1, 9000.00, 8.00, 4, CURRENT_DATE - 90),
(2, 2, 6400.00, 10.00, 4, CURRENT_DATE - 75),
(3, 3, 12000.00, 7.50, 4, CURRENT_DATE - 60);

INSERT INTO emi_schedules
    (loan_id, installment_number, due_date, principal_due, interest_due, amount_due)
VALUES
(1, 1, CURRENT_DATE - 60, 2250.00, 250.00, 2500.00),
(1, 2, CURRENT_DATE - 30, 2250.00, 250.00, 2500.00),
(1, 3, CURRENT_DATE + 5, 2250.00, 250.00, 2500.00),
(1, 4, CURRENT_DATE + 35, 2250.00, 250.00, 2500.00),
(2, 1, CURRENT_DATE - 45, 1600.00, 200.00, 1800.00),
(2, 2, CURRENT_DATE - 15, 1600.00, 200.00, 1800.00),
(2, 3, CURRENT_DATE + 15, 1600.00, 200.00, 1800.00),
(2, 4, CURRENT_DATE + 45, 1600.00, 200.00, 1800.00),
(3, 1, CURRENT_DATE - 30, 3000.00, 200.00, 3200.00),
(3, 2, CURRENT_DATE - 1, 3000.00, 200.00, 3200.00),
(3, 3, CURRENT_DATE + 29, 3000.00, 200.00, 3200.00),
(3, 4, CURRENT_DATE + 59, 3000.00, 200.00, 3200.00);

INSERT INTO emi_payments
    (emi_schedule_id, account_id, amount_paid, payment_date, reference_code)
VALUES
(1, 1, 2500.00, CURRENT_DATE - 58, 'EMI-A-001'),
(2, 1, 1000.00, CURRENT_DATE - 28, 'EMI-A-002A'),
(9, 3, 3200.00, CURRENT_DATE - 29, 'EMI-C-001');

SELECT
    (SELECT COUNT(*) FROM customers) AS customers,
    (SELECT COUNT(*) FROM accounts) AS accounts,
    (SELECT COUNT(*) FROM loans) AS loans,
    (SELECT COUNT(*) FROM emi_schedules) AS emi_schedules,
    (SELECT COUNT(*) FROM emi_payments) AS emi_payments;

Your normalized loan portfolio is ready. Next, you will make one isolated payment insert and see the inconsistency that a safe banking workflow must prevent.

Reproduce an Unsafe Payment

Your normalized PostgreSQL portfolio now gives every customer, account, loan, and installment fact a clear home. The next question is whether one valid row can represent a complete financial operation.

A direct EMI payment can increase the loan's paid total while leaving the account balance and ledger untouched. You will reproduce that contradiction inside a transaction that protects the seeded portfolio.

In this step, get ready to:

• Capture the consistent payment and account state.
• Reproduce an isolated EMI payment.
• Roll back the unsafe payment.

Capture the consistent starting state

The baseline ties three facts together: the scheduled amount, the recorded payments, and the cached account balance. These values give you a reliable comparison point for the experiment.

• In the pgAdmin Query Tool from earlier, select the SQL currently in the editor.
• Replace the selected SQL with the baseline query by pasting this code:

SET search_path TO loan_ops, public;

SELECT
    a.balance,
    es.amount_due,
    COALESCE(SUM(p.amount_paid), 0) AS total_paid
FROM accounts a
CROSS JOIN emi_schedules es
LEFT JOIN emi_payments p ON p.emi_schedule_id = es.emi_schedule_id
WHERE a.account_id = 1
  AND es.emi_schedule_id = 2
GROUP BY a.balance, es.amount_due;

What does this query prove?

• The search_path points unqualified table names at the loan_ops schema.
• The query pairs account 1 with EMI schedule 2.
• The payment join collects every payment recorded against that schedule.
• The aggregate produces one row containing the account balance, scheduled amount, and paid total.

• Save the editor as 03_unsafe_payment_demo.sql.
• Execute the baseline query in the Query Tool.

You should see a balance of 6000.00, an amount due of 2500.00, and a paid total of 1000.00.

Baseline values look different?

Confirm that the Query Tool is connected to the postgres database. Check that the first line sets the search path to loan_ops.

If the query returns different values, rerun the established 01_schema.sql script before rerunning 02_seed.sql.

Help me diagnose unexpected baseline values in my loan operations database.

Insert an isolated payment

A direct insert satisfies the payment table's constraints. It leaves the related account balance and ledger outside the operation.

Before you run the next block, predict which of the three values can change from one valid insert.

• Append the transaction experiment below the baseline query in 03_unsafe_payment_demo.sql by pasting this code:
• Execute only the pasted transaction block in the Query Tool.

BEGIN;

INSERT INTO emi_payments
    (emi_schedule_id, account_id, amount_paid, payment_date, reference_code)
VALUES
    (2, 1, 500.00, CURRENT_DATE, 'UNSAFE-DEMO');

SELECT
    a.balance,
    es.amount_due,
    (SELECT SUM(p.amount_paid)
     FROM emi_payments p
     WHERE p.emi_schedule_id = es.emi_schedule_id) AS total_paid,
    (SELECT COUNT(*)
     FROM account_transactions t
     WHERE t.reference_code = 'UNSAFE-DEMO') AS matching_ledger_rows
FROM accounts a
CROSS JOIN emi_schedules es
WHERE a.account_id = 1
  AND es.emi_schedule_id = 2;

What does the contradiction show?

• The BEGIN command keeps the experiment inside an open transaction.
• The payment total becomes 1500.00 because the new 500.00 payment joins the existing 1000.00.
• The matching_ledger_rows value stays at 0 because the insert does not create an account transaction.
• The account balance stays at 6000.00 because the insert does not debit the account.

• Save 03_unsafe_payment_demo.sql.
• Inspect the result grid for the comparison query.

You should see 1500.00 paid alongside 0 matching ledger rows and an unchanged 6000.00 balance. This visible contradiction is the intended result.

Payment experiment did not run?

Confirm that you executed the block only once. A previous attempt with the same UNSAFE-DEMO reference can conflict with the unique reference constraint.

Use the Query Tool's rollback control if an earlier attempt left a transaction open. Execute the transaction block once after the rollback completes.

Help me troubleshoot the unsafe EMI payment experiment.

Trace the missing accounting event

The loan schedule now says that the borrower paid another 500.00. The account still says that no money left.

A report reading emi_payments would disagree with a statement built from account_transactions. That disagreement makes the isolated insert unsafe for banking operations.

Roll back the unsafe state

The ROLLBACK command cancels every update made since BEGIN. A final count provides direct proof that the temporary payment has disappeared.

Before you finish the transaction, predict what a count of UNSAFE-DEMO rows will show after the rollback.

• Append the rollback check to the bottom of 03_unsafe_payment_demo.sql by pasting this code:
• Execute only the pasted rollback block in the Query Tool.

ROLLBACK;

SELECT COUNT(*) AS unsafe_payment_rows
FROM emi_payments
WHERE reference_code = 'UNSAFE-DEMO';

What does this check prove?

• The ROLLBACK command removes the temporary payment from the transaction.
• The count searches for the unique UNSAFE-DEMO reference after the rollback.
• A result of 0 proves that the payment did not persist.
• The ledger, cached balance, and audit storage remain unchanged because the isolated insert never updated them.

• Save the completed 03_unsafe_payment_demo.sql file.
• Inspect the final result grid in the Query Tool.

You should see unsafe_payment_rows equal to 0. Your rollback safely removed the unsafe payment while preserving the seed portfolio.

Unsafe payment still appears?

Confirm that the rollback block ran in the same Query Tool session as the open transaction. A rollback in a different session cannot close the original transaction.

Check that the final query filters for the exact UNSAFE-DEMO reference.

Help me remove an unsafe payment that remains after my rollback check.

✔️ Awesome, I've got everything!

Your saved script now captures the baseline, reproduces the inconsistency, and restores the original state.

ⓧ I'd like to double check the full code

The reference below is the exact completed 03_unsafe_payment_demo.sql script.

SET search_path TO loan_ops, public;

SELECT
    a.balance,
    es.amount_due,
    COALESCE(SUM(p.amount_paid), 0) AS total_paid
FROM accounts a
CROSS JOIN emi_schedules es
LEFT JOIN emi_payments p ON p.emi_schedule_id = es.emi_schedule_id
WHERE a.account_id = 1
  AND es.emi_schedule_id = 2
GROUP BY a.balance, es.amount_due;

BEGIN;

INSERT INTO emi_payments
    (emi_schedule_id, account_id, amount_paid, payment_date, reference_code)
VALUES
    (2, 1, 500.00, CURRENT_DATE, 'UNSAFE-DEMO');

SELECT
    a.balance,
    es.amount_due,
    (SELECT SUM(p.amount_paid)
     FROM emi_payments p
     WHERE p.emi_schedule_id = es.emi_schedule_id) AS total_paid,
    (SELECT COUNT(*)
     FROM account_transactions t
     WHERE t.reference_code = 'UNSAFE-DEMO') AS matching_ledger_rows
FROM accounts a
CROSS JOIN emi_schedules es
WHERE a.account_id = 1
  AND es.emi_schedule_id = 2;

ROLLBACK;

SELECT COUNT(*) AS unsafe_payment_rows
FROM emi_payments
WHERE reference_code = 'UNSAFE-DEMO';

You have exposed the consistency gap without contaminating the portfolio. Next, you will replace the isolated insert with one controlled payment path that keeps every related record synchronized.

Post EMI Payments Atomically

Your unsafe insert changed the repayment record while leaving the account balance untouched. The missing ledger entry exposed a financial state that could not be trusted.

PostgreSQL can place every related write inside one controlled transaction path. A stored procedure coordinates that payment path.

An audit trigger records the resulting payment change. You will prove the workflow with a rollback before committing a real payment.

In this step, get ready to:

• Create the automated EMI payment workflow.
• Prove its rollback behavior.
• Commit a payment through the workflow.

Create the payment workflow

The workflow needs one guarded entry point for every EMI payment. It also needs an automatic audit record for each payment change.

• In the pgAdmin Query Tool from earlier, start a fresh query tab with the toolbar's new-query control.
• Copy the complete automation script from the second tab below into the editor.

✔️ I have the script ready

Your editor should contain the complete trigger function, trigger declaration, and payment procedure.

ⓧ I'd like to double check the full code

SET search_path TO loan_ops, public;

CREATE OR REPLACE FUNCTION audit_emi_payment_changes()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO payment_audit
            (payment_id, operation, old_amount, new_amount)
        VALUES
            (NEW.payment_id, 'INSERT', NULL, NEW.amount_paid);
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO payment_audit
            (payment_id, operation, old_amount, new_amount)
        VALUES
            (NEW.payment_id, 'UPDATE', OLD.amount_paid, NEW.amount_paid);
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO payment_audit
            (payment_id, operation, old_amount, new_amount)
        VALUES
            (OLD.payment_id, 'DELETE', OLD.amount_paid, NULL);
    END IF;

    RETURN NULL;
END;
$$;

CREATE OR REPLACE TRIGGER trg_audit_emi_payments
AFTER INSERT OR UPDATE OR DELETE ON emi_payments
FOR EACH ROW
EXECUTE FUNCTION audit_emi_payment_changes();

CREATE OR REPLACE PROCEDURE record_emi_payment(
    p_emi_schedule_id bigint,
    p_account_id bigint,
    p_amount numeric,
    p_reference_code text
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_amount_due numeric(12,2);
    v_paid_amount numeric(12,2);
    v_schedule_loan_id bigint;
    v_loan_customer_id bigint;
    v_account_customer_id bigint;
    v_account_balance numeric(14,2);
BEGIN
    IF p_amount IS NULL OR p_amount <= 0 THEN
        RAISE EXCEPTION 'Payment amount must be positive';
    END IF;

    SELECT amount_due, loan_id
    INTO v_amount_due, v_schedule_loan_id
    FROM emi_schedules
    WHERE emi_schedule_id = p_emi_schedule_id
    FOR UPDATE;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'EMI schedule % does not exist', p_emi_schedule_id;
    END IF;

    SELECT customer_id
    INTO v_loan_customer_id
    FROM loans
    WHERE loan_id = v_schedule_loan_id;

    SELECT customer_id, balance
    INTO v_account_customer_id, v_account_balance
    FROM accounts
    WHERE account_id = p_account_id
      AND status = 'ACTIVE'
    FOR UPDATE;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Active account % does not exist', p_account_id;
    END IF;

    IF v_account_customer_id <> v_loan_customer_id THEN
        RAISE EXCEPTION 'Account % does not belong to the loan customer', p_account_id;
    END IF;

    SELECT COALESCE(SUM(amount_paid), 0)
    INTO v_paid_amount
    FROM emi_payments
    WHERE emi_schedule_id = p_emi_schedule_id;

    IF v_paid_amount + p_amount > v_amount_due THEN
        RAISE EXCEPTION 'Payment exceeds the remaining EMI amount';
    END IF;

    IF v_account_balance < p_amount THEN
        RAISE EXCEPTION 'Account % has insufficient funds', p_account_id;
    END IF;

    INSERT INTO account_transactions
        (account_id, transaction_type, amount, reference_code, description)
    VALUES
        (p_account_id, 'DEBIT', p_amount, p_reference_code,
         'EMI payment for schedule ' || p_emi_schedule_id);

    UPDATE accounts
    SET balance = balance - p_amount
    WHERE account_id = p_account_id;

    INSERT INTO emi_payments
        (emi_schedule_id, account_id, amount_paid, payment_date, reference_code)
    VALUES
        (p_emi_schedule_id, p_account_id, p_amount, CURRENT_DATE, p_reference_code);
END;
$$;

What does this script do?

• The PL/pgSQL function checks TG_OP. Each payment change creates a corresponding row in payment_audit.
• The trg_audit_emi_payments trigger invokes the audit function after a payment row changes.
• The record_emi_payment procedure validates the payment before any financial records change.
• A successful procedure call creates a ledger debit. It also updates the cached balance before inserting the payment.

Why lock the schedule first?

Row locking with FOR UPDATE prevents competing calls from changing the same schedule or account at the same time. Each call finishes its checks against a stable financial state.

• Execute the complete automation script with the Query Tool's run control.

The script completes without changing the seeded balances or payments. The database now has one controlled path for future EMI payments.

The core safeguard is now in place. Payment validation, ledger updates, balance changes, and auditing can move together.

• Save the script as 04_automation.sql.

Did the automation script stop?

• Confirm the Query Tool is still connected to the local postgres database.
• Confirm the loan_ops schema from the earlier step still exists.
• Run the complete file from its first SET search_path statement.

Help me diagnose why the EMI automation script did not create its function, trigger, or procedure.

Prove rollback behavior

A safe workflow must preserve atomicity when a transaction is cancelled. This test makes every related change visible before rolling the whole set back.

• Start a fresh query tab with the Query Tool's new-query control.
• Build the rollback test by copying the following code into the editor:

SET search_path TO loan_ops, public;

BEGIN;
CALL record_emi_payment(2, 1, 500.00, 'EMI-DEMO-ROLLBACK');

SELECT
    a.balance,
    (SELECT SUM(p.amount_paid)
     FROM emi_payments p
     WHERE p.emi_schedule_id = 2) AS schedule_paid,
    (SELECT COUNT(*)
     FROM account_transactions t
     WHERE t.reference_code = 'EMI-DEMO-ROLLBACK') AS ledger_rows,
    (SELECT COUNT(*)
     FROM payment_audit pa
     JOIN emi_payments p ON p.payment_id = pa.payment_id
     WHERE p.reference_code = 'EMI-DEMO-ROLLBACK') AS audit_rows
FROM accounts a
WHERE a.account_id = 1;

ROLLBACK;

What does this test prove?

• The first BEGIN opens a temporary transaction.
• The procedure call posts a test payment through the guarded workflow.
• The query inspects the temporary balance, payment total, ledger row, and audit row.
• The final ROLLBACK cancels every write made in that transaction.

Before you run it, which values do you expect to change inside the transaction? Hold that prediction for the result.

• Execute the rollback test with the Query Tool's run control.

The temporary balance reads 5500.00. The temporary scheduled payment total reads 1500.00.

The ledger count reads 1. The audit count also reads 1.

• Save the rollback test as 04_payment_workflow_demo.sql.

Before you check, which values should return to their starting state? Keep your prediction in mind.

• Confirm the rollback result by running this query in a temporary Query Tool tab:

SELECT
    a.balance,
    (SELECT SUM(p.amount_paid)
     FROM emi_payments p
     WHERE p.emi_schedule_id = 2) AS schedule_paid,
    (SELECT COUNT(*)
     FROM emi_payments p
     WHERE p.reference_code = 'EMI-DEMO-ROLLBACK') AS rolled_back_payment_rows
FROM accounts a
WHERE a.account_id = 1;

What does this check inspect?

The query reads the persistent state after the rollback. It checks the original balance, the original payment total, and the test reference count.

The balance is back to 6000.00. The scheduled payment total is back to 1000.00.

The rolled-back payment count is 0. You have proved that the workflow removes every related change together.

Did the rollback leave a test payment?

• Confirm the test script ends with ROLLBACK.
• Confirm the verification query uses the reference EMI-DEMO-ROLLBACK.
• Rerun the rollback script before checking the persistent state again.

Help me find why the rollback test left persistent payment data.

Commit the payment

A committed transaction makes the coordinated writes durable. Schedule 2 already has 1000.00 paid, so the final payment uses its remaining 1500.00.

• Append the following transaction block to the end of 04_payment_workflow_demo.sql:

BEGIN;
CALL record_emi_payment(2, 1, 1500.00, 'EMI-A-002B');
COMMIT;

What does this transaction do?

The procedure posts the remaining installment through the guarded workflow. The final COMMIT preserves the payment and every related write.

• Save 04_payment_workflow_demo.sql.

Before you run the complete file, which transaction should leave durable changes? Hold that prediction for the final check.

• Execute the complete 04_payment_workflow_demo.sql file with the Query Tool's run control.

The rollback section still exposes its temporary result before cancelling it. The second transaction commits the real payment.

Before you run the final check, which values should now agree? Keep your prediction in mind.

• Verify the committed payment by running this query in a temporary Query Tool tab:

SELECT
    a.balance,
    es.amount_due,
    (SELECT SUM(p.amount_paid)
     FROM emi_payments p
     WHERE p.emi_schedule_id = es.emi_schedule_id) AS schedule_paid,
    (SELECT COUNT(*)
     FROM account_transactions t
     WHERE t.reference_code = 'EMI-A-002B') AS ledger_rows,
    (SELECT COUNT(*)
     FROM payment_audit pa
     JOIN emi_payments p ON p.payment_id = pa.payment_id
     WHERE p.reference_code = 'EMI-A-002B'
       AND pa.operation = 'INSERT') AS audit_rows
FROM accounts a
CROSS JOIN emi_schedules es
WHERE a.account_id = 1
  AND es.emi_schedule_id = 2;

What does the final check compare?

• The balance shows the cached account value after the debit.
• The schedule values compare the required amount with total payments.
• The ledger count proves that the account debit exists.
• The audit count proves that the payment insert was recorded.

The account balance is 4500.00. Both amount_due and schedule_paid read 2500.00.

The ledger count is 1. The audit count is also 1.

That is the consistency guarantee working. The payment, ledger debit, account balance, and audit history now describe the same financial event.

Do the committed values disagree?

• Confirm the committed reference is EMI-A-002B.
• Confirm the committed amount is 1500.00.
• Confirm you executed 04_automation.sql before running the workflow demo.

Help me reconcile the committed EMI payment with its balance, ledger, and audit results.

✔️ Awesome, I've got everything!

Your saved demo file contains the rollback inspection followed by the committed payment transaction.

ⓧ I'd like to double check the full code

SET search_path TO loan_ops, public;

BEGIN;
CALL record_emi_payment(2, 1, 500.00, 'EMI-DEMO-ROLLBACK');

SELECT
    a.balance,
    (SELECT SUM(p.amount_paid)
     FROM emi_payments p
     WHERE p.emi_schedule_id = 2) AS schedule_paid,
    (SELECT COUNT(*)
     FROM account_transactions t
     WHERE t.reference_code = 'EMI-DEMO-ROLLBACK') AS ledger_rows,
    (SELECT COUNT(*)
     FROM payment_audit pa
     JOIN emi_payments p ON p.payment_id = pa.payment_id
     WHERE p.reference_code = 'EMI-DEMO-ROLLBACK') AS audit_rows
FROM accounts a
WHERE a.account_id = 1;

ROLLBACK;

BEGIN;
CALL record_emi_payment(2, 1, 1500.00, 'EMI-A-002B');
COMMIT;

How should this file end?

The saved file ends with the committed EMI-A-002B transaction. The verification queries remain temporary checks in separate Query Tool tabs.

Your database now posts EMI payments through one atomic path. Next, you will turn the synchronized records into live delinquency reports and inspect how PostgreSQL executes the reporting queries.

Report and Tune Delinquency

Your atomic payment workflow now keeps each EMI payment aligned with its ledger entry. The cached account balance stays synchronized.

The audit trail captures each payment change. However, loan teams still need live answers about overdue exposure.

In this step, PostgreSQL transforms normalized operational facts into delinquency reports. Those reports update as schedules or payments change.

Targeted indexes support the report paths that matter. You will inspect execution plans to see how PostgreSQL chooses a scan.

In this step, get ready to:

• Create live overdue reporting from schedules and payments.
• Run portfolio analytics that expose repayment risk.
• Compare report behavior before and after tuning.

Create live overdue reporting

A view runs its defining query whenever you select from it. This keeps delinquency status tied to the current date and the latest payment totals.

Why derive overdue status?

Overdue status changes as time passes. It also changes when a borrower makes another payment.

Deriving the status from schedules and payments keeps each fact in one place. The report always reflects the latest operational data.

• Switch back to the pgAdmin Query Tool from earlier.
• Start a new query tab for 05_reports.sql.
• Add the overdue view definition by pasting this SQL:

SET search_path TO loan_ops, public;

CREATE OR REPLACE VIEW overdue_emi_report AS
WITH payment_totals AS (
    SELECT emi_schedule_id, SUM(amount_paid) AS total_paid, MAX(payment_date) AS last_payment_date
    FROM emi_payments
    GROUP BY emi_schedule_id
)
SELECT c.customer_id, c.full_name, l.loan_id, es.emi_schedule_id, es.installment_number,
       es.due_date, es.amount_due, COALESCE(pt.total_paid, 0)::numeric(12,2) AS total_paid,
       (es.amount_due - COALESCE(pt.total_paid, 0))::numeric(12,2) AS overdue_amount,
       CURRENT_DATE - es.due_date AS days_past_due, pt.last_payment_date,
       CASE WHEN COALESCE(pt.total_paid, 0) = 0 THEN 'UNPAID' ELSE 'PARTIAL' END AS repayment_status
FROM emi_schedules es
JOIN loans l ON l.loan_id = es.loan_id
JOIN customers c ON c.customer_id = l.customer_id
LEFT JOIN payment_totals pt ON pt.emi_schedule_id = es.emi_schedule_id
WHERE l.loan_status = 'ACTIVE'
  AND es.due_date < CURRENT_DATE
  AND COALESCE(pt.total_paid, 0) < es.amount_due;

What does this view do?

• The payment_totals CTE groups payment history by installment.
• The joins connect each schedule to its loan and customer.
• The calculations derive the unpaid amount and days past due.
• The filters retain active loans with overdue unpaid installments.

• Execute the selected SQL in Query Tool.
• Save the query tab beside 04_automation.sql as 05_reports.sql.

The statement completes successfully. Your database now has a live overdue_emi_report view.

View creation did not complete?

Confirm that Query Tool is connected to the local postgres database. Check that the first line sets the search path to loan_ops.

Ask for help with the failed view statement.

• Append the overdue lookup to 05_reports.sql by pasting this SQL:

SELECT * FROM overdue_emi_report ORDER BY days_past_due DESC, loan_id, installment_number;

What does this lookup show?

The query returns one row for each overdue installment. The oldest delinquency appears first.

Each row includes the borrower and unpaid amount. It also includes the calculated days past due.

• Save 05_reports.sql.

Before you execute the lookup, which borrowers do you expect to appear after Alice Morgan completed schedule 2?

• Execute only the overdue lookup in Query Tool.

You will see overdue rows for Brian Okafor and Chitra Rao. Alice Morgan is absent because her overdue installments are fully paid.

That is the reporting loop working. A committed payment now changes the live delinquency result without another status table.

Missing an overdue borrower?

Confirm that 02_seed.sql was run before the committed payment workflow. Check that the view filters dates earlier than CURRENT_DATE.

Help me compare my overdue rows with the expected seeded state.

✔️ Awesome, I've got everything!

Your live overdue view is saved. The lookup identifies the borrowers who still have unpaid overdue installments.

ⓧ I'd like to double check the full code

SET search_path TO loan_ops, public;

CREATE OR REPLACE VIEW overdue_emi_report AS
WITH payment_totals AS (
    SELECT emi_schedule_id, SUM(amount_paid) AS total_paid, MAX(payment_date) AS last_payment_date
    FROM emi_payments
    GROUP BY emi_schedule_id
)
SELECT c.customer_id, c.full_name, l.loan_id, es.emi_schedule_id, es.installment_number,
       es.due_date, es.amount_due, COALESCE(pt.total_paid, 0)::numeric(12,2) AS total_paid,
       (es.amount_due - COALESCE(pt.total_paid, 0))::numeric(12,2) AS overdue_amount,
       CURRENT_DATE - es.due_date AS days_past_due, pt.last_payment_date,
       CASE WHEN COALESCE(pt.total_paid, 0) = 0 THEN 'UNPAID' ELSE 'PARTIAL' END AS repayment_status
FROM emi_schedules es
JOIN loans l ON l.loan_id = es.loan_id
JOIN customers c ON c.customer_id = l.customer_id
LEFT JOIN payment_totals pt ON pt.emi_schedule_id = es.emi_schedule_id
WHERE l.loan_status = 'ACTIVE'
  AND es.due_date < CURRENT_DATE
  AND COALESCE(pt.total_paid, 0) < es.amount_due;

SELECT * FROM overdue_emi_report ORDER BY days_past_due DESC, loan_id, installment_number;

Run analytical reports

A CTE gives one query a named intermediate result. Window functions calculate rankings or running totals while preserving individual rows.

• Start another Query Tool tab for the analytical reports.
• Build the loan-level portfolio summary by running this query:

WITH payment_totals AS (
    SELECT emi_schedule_id, SUM(amount_paid) AS total_paid
    FROM emi_payments
    GROUP BY emi_schedule_id
),
loan_portfolio AS (
    SELECT
        c.full_name,
        l.loan_id,
        l.principal_amount,
        SUM(es.amount_due) AS scheduled_emi_total,
        SUM(COALESCE(pt.total_paid, 0)) AS paid_total,
        SUM(es.amount_due - COALESCE(pt.total_paid, 0)) AS remaining_emi_total
    FROM customers c
    JOIN loans l ON l.customer_id = c.customer_id
    JOIN emi_schedules es ON es.loan_id = l.loan_id
    LEFT JOIN payment_totals pt ON pt.emi_schedule_id = es.emi_schedule_id
    GROUP BY c.full_name, l.loan_id, l.principal_amount
)
SELECT *
FROM loan_portfolio
ORDER BY remaining_emi_total DESC;

How does the portfolio summary work?

• The first CTE totals every payment against its schedule.
• The second CTE rolls schedules up to one row per loan.
• The final sort places the largest remaining EMI exposure first.

You will see one row for each loan. Each row compares its scheduled total with its paid and remaining totals.

Portfolio totals look incomplete?

Confirm that the joins use loan_id for schedules. Confirm that payment totals join through emi_schedule_id.

Help me trace a missing portfolio total.

• Rank customer risk by running this query:

WITH customer_risk AS (
    SELECT
        c.customer_id,
        c.full_name,
        COUNT(o.emi_schedule_id) AS overdue_installments,
        COALESCE(SUM(o.overdue_amount), 0) AS overdue_amount,
        COALESCE(MAX(o.days_past_due), 0) AS max_days_past_due
    FROM customers c
    LEFT JOIN overdue_emi_report o ON o.customer_id = c.customer_id
    GROUP BY c.customer_id, c.full_name
)
SELECT
    dense_rank() OVER (
        ORDER BY overdue_amount DESC, overdue_installments DESC
    ) AS risk_rank,
    full_name,
    overdue_installments,
    overdue_amount,
    max_days_past_due
FROM customer_risk
ORDER BY risk_rank, full_name;

How is customer risk ranked?

• The CTE keeps customers with no overdue rows by using a left join.
• The dense_rank() window assigns the same rank to equal risk groups.
• The ordering prioritizes overdue exposure before installment count.

Brian Okafor ranks ahead of Chitra Rao because his overdue exposure is larger. Customers without delinquency remain visible with zero exposure.

Risk ranks look duplicated?

Equal ordering values intentionally receive the same dense rank. Check the overdue amount before comparing the installment count.

Help me interpret my customer risk ranking.

• Calculate cumulative payments by running this query:

SELECT
    c.full_name,
    l.loan_id,
    p.payment_date,
    p.reference_code,
    p.amount_paid,
    SUM(p.amount_paid) OVER (
        PARTITION BY l.loan_id
        ORDER BY p.payment_date, p.payment_id
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_paid
FROM emi_payments p
JOIN emi_schedules es ON es.emi_schedule_id = p.emi_schedule_id
JOIN loans l ON l.loan_id = es.loan_id
JOIN customers c ON c.customer_id = l.customer_id
ORDER BY l.loan_id, p.payment_date, p.payment_id;

How does the running total work?

• The partition restarts the calculation for each loan.
• The ordering gives every payment a stable place in its loan history.
• The explicit frame adds all payments from the first row through the current row.

Alice Morgan's cumulative payment total reaches 5000.00 after the committed payment. Each preceding row shows how that total grew.

Running total resets unexpectedly?

Confirm that the window partitions by l.loan_id. Check that the order includes both payment date and payment ID.

Help me debug the cumulative payment window.

• Reconcile cached balances against the ledger by running this query:

WITH ledger_balances AS (
    SELECT
        account_id,
        SUM(
            CASE transaction_type
                WHEN 'CREDIT' THEN amount
                WHEN 'DEBIT' THEN -amount
            END
        ) AS calculated_balance
    FROM account_transactions
    GROUP BY account_id
)
SELECT
    a.account_number,
    a.balance AS stored_balance,
    lb.calculated_balance,
    a.balance - lb.calculated_balance AS difference
FROM accounts a
JOIN ledger_balances lb ON lb.account_id = a.account_id
ORDER BY a.account_number;

What does reconciliation prove?

• Credits add to the calculated ledger balance.
• Debits subtract from the calculated ledger balance.
• The final difference compares the cached balance with the auditable ledger.

Every account shows a 0.00 difference. The stored balances agree with the transaction ledger.

Seeing a nonzero difference?

Identify the affected account number first. Compare its stored balance with the signed sum of its credits and debits.

Help me trace a ledger reconciliation difference.

Inspect and support the report path

An execution plan shows how PostgreSQL intends to access the rows required by a query. Comparing plans helps you connect an index design to the path it supports.

This dataset is deliberately small. PostgreSQL may still choose a sequential scan when reading the table is cheaper than using an index.

• Start a new query tab for 06_indexing.sql.
• Add the baseline plan by pasting this SQL:

SET search_path TO loan_ops, public;

EXPLAIN (ANALYZE, BUFFERS)
SELECT loan_id, installment_number, due_date, amount_due
FROM emi_schedules
WHERE due_date < CURRENT_DATE
ORDER BY due_date, loan_id;

What does this plan measure?

The query retrieves overdue schedule rows in report order. EXPLAIN (ANALYZE, BUFFERS) executes that query and reports the chosen plan.

The baseline gives you a comparison point before the reporting indexes exist.

Before you run the baseline, which scan type do you think PostgreSQL will choose for twelve schedule rows?

• Execute the baseline plan in Query Tool.
• Save the query tab beside 05_reports.sql as 06_indexing.sql.

You will see an executed plan with actual timing and buffer information. The plan may use a sequential scan because the table is small.

Plan output did not appear?

Select the entire statement from EXPLAIN through its final full stop. Execute that selection in Query Tool.

Help me run the baseline execution plan.

• Append the reporting indexes to 06_indexing.sql by pasting this SQL:

CREATE INDEX IF NOT EXISTS idx_emi_schedules_due_lookup
ON emi_schedules (due_date, loan_id)
INCLUDE (amount_due, installment_number);

CREATE INDEX IF NOT EXISTS idx_emi_payments_schedule_lookup
ON emi_payments (emi_schedule_id, payment_date)
INCLUDE (amount_paid);

What do these indexes support?

• The schedule index starts with the due date used by overdue lookups.
• The loan ID supports report ordering after the date filter.
• The payment index groups payment history by schedule.
• The included columns make report values available from the index structure.

• Execute both index statements in Query Tool.
• Save 06_indexing.sql.

Both index statements complete without an error. The database now has dedicated structures for overdue lookup and payment aggregation.

Index creation did not complete?

Confirm that the current search path includes loan_ops. Check each index name against the script before running the statements again.

Help me debug the reporting index statements.

• Append the second plan to 06_indexing.sql by pasting this SQL:

EXPLAIN (ANALYZE, BUFFERS)
SELECT loan_id, installment_number, due_date, amount_due
FROM emi_schedules
WHERE due_date < CURRENT_DATE
ORDER BY due_date, loan_id;

Why run the same plan again?

The query stays identical so the indexes are the only deliberate database change. This gives you a clean before-and-after comparison.

• Save 06_indexing.sql.

Before you run the second plan, do you expect the new schedule index to replace the original scan?

• Execute only the second plan in Query Tool.

You may still see a sequential scan. PostgreSQL can correctly decide that reading a twelve-row table is cheaper than using the new index.

Still seeing a sequential scan?

Treat the chosen scan as a cost decision for this small dataset. Verify the index definitions directly before diagnosing an indexing problem.

Help me compare the two execution plans.

• Append the index catalog check to 06_indexing.sql by pasting this SQL:

SELECT indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'loan_ops'
  AND indexname IN ('idx_emi_schedules_due_lookup', 'idx_emi_payments_schedule_lookup')
ORDER BY indexname;

What does the catalog check prove?

The pg_indexes catalog view lists index names and definitions. This check proves that both reporting indexes exist even if the planner chooses another scan.

• Save 06_indexing.sql.

Before you run the final check, which two index names should the catalog return?

• Execute only the catalog query in Query Tool.

You will see idx_emi_payments_schedule_lookup and idx_emi_schedules_due_lookup with their definitions.

You have closed the loop from transaction-safe payments to live delinquency reporting. Your database now exposes risk while preserving a trustworthy operational record.

Catalog query returns no rows?

Confirm that both index statements completed in the loan_ops schema. Check the two names in the catalog filter for spelling differences.

Help me locate the missing reporting indexes.

✔️ Awesome, I've got everything!

Both reporting indexes exist. You have also compared the overdue lookup plan before and after creating them.

ⓧ I'd like to double check the full code

SET search_path TO loan_ops, public;

EXPLAIN (ANALYZE, BUFFERS)
SELECT loan_id, installment_number, due_date, amount_due
FROM emi_schedules
WHERE due_date < CURRENT_DATE
ORDER BY due_date, loan_id;

CREATE INDEX IF NOT EXISTS idx_emi_schedules_due_lookup
ON emi_schedules (due_date, loan_id)
INCLUDE (amount_due, installment_number);

CREATE INDEX IF NOT EXISTS idx_emi_payments_schedule_lookup
ON emi_payments (emi_schedule_id, payment_date)
INCLUDE (amount_paid);

EXPLAIN (ANALYZE, BUFFERS)
SELECT loan_id, installment_number, due_date, amount_due
FROM emi_schedules
WHERE due_date < CURRENT_DATE
ORDER BY due_date, loan_id;

SELECT indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'loan_ops'
  AND indexname IN ('idx_emi_schedules_due_lookup', 'idx_emi_payments_schedule_lookup')
ORDER BY indexname;

Secret mission

Detect Repayment Gaps

A healthy repayment total can hide long pauses between payments. Build a repayment-cadence report that compares successive payment dates and flags gaps over 30 days for review.

Clean Up Your Resources

Clean Up Your Resources

Your database runs locally through PostgreSQL and pgAdmin at no ongoing cost. Choose whether to keep it available, pause its service, or remove the project schema.

Resources you used:

• The local loan_ops schema containing the project database objects and stored data.

Keep everything running

No action is needed. Choose this option while you still want the database ready for demonstrations.

• Leave loan_ops in the local postgres database.
• Keep the PostgreSQL service available for future demonstrations.
• Retain your SQL scripts so you can inspect or rebuild the project later.

Pause - I'll come back to this later

Shut down the local database service to free up memory. The loan_ops schema stays intact for your return.

• Close pgAdmin 4.
• Press the Windows key to open search.
• Type Services into the search field.
• Select the Windows Services app.
• Find the installed PostgreSQL service with the service name shown by your installer.
• Stop the installed PostgreSQL service.
• Confirm Windows Services no longer shows the service as running.
• Start the same PostgreSQL service from Windows Services when you return.

Delete - I don't want to use this again

This option removes the working loan_ops schema. Your local database software remains installed for future projects.

Before You Delete

Deleting the schema looks drastic because every database object inside it disappears. Your SQL script files remain available if you want to rebuild it.

• Delete the project database objects by running this command in the pgAdmin Query Tool from earlier:

DROP SCHEMA loan_ops CASCADE;

What Does This Command Remove?

• The seven normalized tables are deleted.
• The seed data is deleted.
• The overdue_emi_report view is deleted.
• The record_emi_payment procedure is deleted.
• The audit trigger function is deleted.
• The audit trigger is deleted.
• The two reporting indexes are deleted.

• Reload the schema list for the local postgres database.

You should no longer see loan_ops in the schema list. The project database has now been removed.

Still Seeing the Schema?

The schema list can show an older state until pgAdmin reloads it.

• Reload the schema list once more.
• Confirm the Query Tool remains connected to the local postgres database.
• Run the schema deletion command again.

Help me diagnose why loan_ops still appears after I ran the deletion command.

Nice Work!

Nice Work!

You made it! Your local PostgreSQL loan-operations database now protects each EMI payment as a complete financial operation.

You've learned how to:

• Apply normalization to build a loan-servicing schema where every financial fact has one clear home.
• Post EMI payments through record_emi_payment as one atomic transaction. Capture each payment change automatically in an audit trail.
• Build live delinquency reports from normalized facts. Inspect execution plans before supporting report paths with targeted indexes.
• Secret Mission: Use lag() to detect repayment gaps. Classify each payment cadence as REVIEW or ON TRACK.

Ready to quiz yourself?