Main Data Structure

Entity Relationship Diagram (ERD)

The diagram below uses Crow's Foot notation and shows:

Main entities with their attributes
Relationships between entities
Cardinalities (one-to-one, one-to-many, many-to-many)

The live diagram is available here:

erDiagram
    Provider {
        int id PK
        string name
        boolean active
        string company_name
        string tax_identification
        string network
        int provider_sc_id UK
        string ramp_address
        datetime date_created
    }

    Facility {
        int id PK
        int provider_id FK
        int investor_group_id FK
        string name
        boolean active
        string collateral_type
        decimal interest_rate
        int loan_adapter_id FK
        int repayment_adapter_id FK
        boolean validate_eligibility_criteria
        boolean require_kyc
        datetime created_at
    }

    Loan {
        int id PK
        uuid uuid UK
        int facility_id FK
        int borrower_id FK
        int beneficiary_id FK
        string status
        string investment_status
        decimal investment_amount
        decimal gross_amount
        decimal net_amount
        decimal interest_rate
        string collateral_type
        string repayment_method
        date expected_disbursement_date
        date first_installment_date
        datetime date_created
    }

    Stakeholder {
        int id PK
        string name
        string tax_id UK
        string email
        string phone
        int created_by_id FK
        string occupation
        string nationality
        date birth_date
        date foundation_date
        string preferred_contact_method
        datetime created_at
    }

    InvestorGroup {
        int id PK
        string name
        string tax_id UK
        string emails
        int safe_id FK
        string pool_smart_contract
        string investment_method
        decimal daily_limit
        decimal title_total_capital
        int stark_bank_account_id FK
        datetime created_at
    }

    RepaymentSchedule {
        int id PK
        int loan_id FK
        date creation_date
        boolean is_active
        int version
        datetime created_at
    }

    RepaymentInstallment {
        int id PK
        int repayment_schedule_id FK
        int collateral_id FK
        decimal fixed_amount
        decimal fixed_principal
        decimal interest_at_due_date
        date due_date
        int installment_number
        datetime created_at
    }

    Collateral {
        int id PK
        uuid uuid UK
        string type
        int loan_id FK
        int stakeholder_id FK
        date maturity
        decimal value
        file file
        int content_type_id FK
        int object_id
        datetime date_created
    }

    LoanDisbursement {
        int id PK
        int loan_id FK
        uuid quote_identifier
        uuid payment_identifier
        string payment_status
        datetime payment_date
        file receipt
        string email_id
        text error_message
        datetime date_created
    }

    Boleto {
        int id PK
        int repayment_installment_id FK
        string stark_id UK
        int amount
        string bar_code
        file file
        string city
        datetime created
        text descriptions
        datetime due
        float fee
        float fine
        float interest
        string line
        string name
        int our_number UK
        string status
        datetime paid_at
    }

    BankAccount {
        int id PK
        string tax_id
        string ispb
        string bank_name
        string bank_code
        string branch
        string account
        string account_digit
        string account_type
        string pix_key
        string pix_value
        string validation_status
        string validation_transfer_id
        text error_text
        datetime created_at
    }

    PaymentQrCode {
        int id PK
        int created_by_id FK
        float amount
        string client_external_id
        text qr_code_string
        text qr_code_base64
        string qr_code_reference_id
        string qr_code_external_id
        datetime expiration_date
        boolean paid
        int facility_repay_created_id FK
        datetime date_created
    }

    RepaymentInstallmentToPaymentQRCode {
        int id PK
        int repayment_installment_id FK
        int payment_qr_code_id FK
        float amount
    }

    %% Core Business Relationships
    Provider ||--o{ Facility : "has"
    Provider ||--o{ Stakeholder : "creates"
    
    Facility ||--o{ Loan : "contains"
    Facility }o--|| InvestorGroup : "funded_by"
    
    Loan }o--|| Stakeholder : "borrower"
    Loan }o--o| Stakeholder : "beneficiary"
    Loan ||--o{ Collateral : "secured_by"
    Loan ||--o{ RepaymentSchedule : "has"
    Loan ||--o| LoanDisbursement : "disbursed_via"
    
    RepaymentSchedule ||--o{ RepaymentInstallment : "contains"
    
    RepaymentInstallment }o--o| Collateral : "related_to"
    RepaymentInstallment ||--o| Boleto : "can_be_paid_via"
    RepaymentInstallment ||--o{ RepaymentInstallmentToPaymentQRCode : "has_qr_codes"
    
    PaymentQrCode ||--o{ RepaymentInstallmentToPaymentQRCode : "linked_to_installments"
    
    Collateral }o--|| Stakeholder : "owned_by"
    
    %% Banking Relationships
    Stakeholder ||--o| BankAccount : "has_account"

🏗️ Main Entities

Core Business

Provider: Loan providers in the system
Facility: Specific credit lines for each provider
Loan: Individual loans within a facility
Stakeholder: Borrowers, beneficiaries and other involved parties

Financial Flow

RepaymentSchedule: Payment schedule for a loan
RepaymentInstallment: Individual installments of the schedule
Boleto: Bank slips for payment
PaymentQrCode: QR codes for payment processing
RepaymentInstallmentToPaymentQRCode: Many-to-many relationship between installments and QR codes

Collateral & Security

Collateral: Loan guarantees
CCB: Bank Credit Notes (Cédulas de Crédito Bancário)

Banking & Infrastructure

BankAccount: Bank account information for stakeholders
InvestorGroup: Investor groups

🔗 Key Relationships

Provider → Facility → Loan: Main business hierarchy
Loan → RepaymentSchedule → RepaymentInstallment: Payment structure
Loan ↔ Stakeholder: Relationship between loans and involved parties
Stakeholder ↔ BankAccount: Banking information for payments
InvestorGroup → Facility: Funding of credit lines
Collateral ↔ Loan: Loan guarantees
RepaymentInstallment ↔ PaymentQrCode: QR codes for installment payments (many-to-many)
PaymentQrCode → FacilityRepay: QR code payments create facility repayments

📋 Conventions

PK: Primary Key

FK: Foreign Key
UK: Unique Key
||--o{: One-to-Many relationship
}o--||: Many-to-One relationship
||--o|: One-to-One relationship
}o--o|: Zero-or-One relationship