# Entity Relationship Diagram (ERD)

The BlackSoil Co-Lending ecosystem relies on a set of normalized, relational tables. This schema ensures that Repayment Schedules (RPS), payment allocations, and historical metrics remain tightly coupled and queryable at scale.

## Table Architecture

### Core Tables

#### 1. `co_lending_loans`
The master record for a co-lending loan.
* **`loan_id`**: (Primary Key) Unique identifier matching the core system.
* **`disbursement_date`**: The date the loan was disbursed.
* **`loan_amount`**: The total 100% principal amount.
* **`tenure`**: Length of the loan in months.
* **`product_id`**: Used to fetch live configurations (like Partner Splits and Hurdle Rates).

#### 2. `co_lending_emi_payments`
The massive schedule table holding the Repayment Schedules (RPS). Note that one physical loan has **multiple rows per month** (for each `rps_type`).
* **`id`**: (Primary Key)
* **`loan_id`**: (Foreign Key)
* **`rps_type`**: Enum (`orig`, `partner_a`, `partner_b`, `hurdle`) denoting which schedule this row belongs to.
* **`installment_number`**: The sequence number of the EMI.
* **`emi_date` / `due_date`**: The scheduled due date.
* **`principal_due`**, **`interest_due`**: Core mathematical components.
* **`bounce_charges`**, **`delay_penalty`**: Penalties that dynamically inflate over time as the loan defaults.
* **`charges_waived`**: Manual waivers for penalties.

#### 3. `co_lending_payment_logs`
An audit trail of every monetary injection into the system.
* **`id`**: (Primary Key)
* **`loan_id`**: (Foreign Key)
* **`payment_date`**: The date the cash arrived (can be backdated).
* **`amount`**: The total cash received.
* **`source`**: Enum (`bank`, `cash`, `parked_cron`, etc.)

#### 4. `co_lending_payment_transactions`
The highly granular allocation table. Every entry in `co_lending_payment_logs` is split into multiple rows here, detailing exactly which schedule and which component the cash cleared.
* **`id`**: (Primary Key)
* **`payment_log_id`**: (Foreign Key)
* **`co_lending_emi_payments_id`**: (Foreign Key -> points to the exact RPS row).
* **`rps_type`**: Inherited from the RPS row.
* **`payment_for`**: Enum (`principal`, `interest`, `bpi`, `bounce`, `penal`, `service_fee`).
* **`amount`**: The exact micro-fraction of cash allocated to this bucket.

#### 5. `co_lending_parked_transactions`
The holding area for overpayments. If cash remains after all due EMIs are cleared, it lands here.
* **`id`**: (Primary Key)
* **`loan_id`**: (Foreign Key)
* **`payment_log_id`**: (Foreign Key -> points to the original overpayment log).
* **`total_amount`**: The original parked cash.
* **`available_amount`**: The current unconsumed balance (reduces as future EMIs consume it).

#### 6. `co_lending_daily_metrics`
The time-machine snapshot table used for business reporting.
* **`id`**: (Primary Key)
* **`loan_id`**: (Foreign Key)
* **`rps_type`**: Enum (tracks metrics for all 4 distinct schedules).
* **`as_on_date`**: The snapshot date.
* **`dpd`**: Days Past Due as of this date.
* **`principal_outstanding`**, **`interest_outstanding`**, **`total_bounce_due`**, **`total_penal_due`**: Current snapshot balances.

---

## Mermaid ERD Visualization

```mermaid
erDiagram
    co_lending_loans ||--o{ co_lending_emi_payments : "1 to Many (RPS)"
    co_lending_loans ||--o{ co_lending_payment_logs : "1 to Many (Payments)"
    co_lending_loans ||--o{ co_lending_parked_transactions : "1 to Many (Overpayments)"
    co_lending_loans ||--o{ co_lending_daily_metrics : "1 to Many (Snapshots)"
    
    co_lending_payment_logs ||--o{ co_lending_payment_transactions : "1 to Many (Allocations)"
    co_lending_payment_logs ||--o{ co_lending_parked_transactions : "1 to 1 (If Overpaid)"
    
    co_lending_emi_payments ||--o{ co_lending_payment_transactions : "1 to Many (Cleared By)"

    co_lending_loans {
        string loan_id PK
        date disbursement_date
        decimal loan_amount
        int tenure
    }

    co_lending_emi_payments {
        int id PK
        string loan_id FK
        string rps_type
        date due_date
        decimal principal_due
        decimal interest_due
        decimal bounce_charges
        decimal delay_penalty
    }

    co_lending_payment_logs {
        int id PK
        string loan_id FK
        date payment_date
        decimal amount
    }

    co_lending_payment_transactions {
        int id PK
        int payment_log_id FK
        int co_lending_emi_payments_id FK
        string rps_type
        string payment_for
        decimal amount
    }

    co_lending_parked_transactions {
        int id PK
        string loan_id FK
        int payment_log_id FK
        decimal total_amount
        decimal available_amount
    }

    co_lending_daily_metrics {
        int id PK
        string loan_id FK
        string rps_type
        date as_on_date
        int dpd
        decimal principal_outstanding
    }
```