Database Schema

The Loan Pilot database is built on a relational PostgreSQL schema (via Supabase) designed for multi-tenant loan processing. It focuses on maintaining strict isolation between organizations while linking borrowers, loan applications, and deep financial analysis data.

Core Schema Entities

The database structure is centered around the Organization as the primary tenant. All data—from user profiles to loan documents—is scoped to an organization_id.

Organizations and Access

• organizations: Represents the lending institution (e.g., a bank or NBFC). Each organization is identified by a unique slug and ID.
• profiles: Extends the authentication system to link users (bank staff) to an organization.
  • id: UUID (matches Supabase Auth UID)
  • organization_id: Reference to the user's institution.
  • full_name: The display name of the staff member.

Loan Lifecycle

• borrowers: Contains the business or individual profiles applying for credit.
  • Includes KYC and verification status: udyam_verified, gstin_verified, cibil_score.
• loan_applications: The central entity for any loan request.
  • Tracks loan_amount, loan_type, status, and purpose.
  • Linked to a borrower_id and an organization_id.
• loan_documents: Metadata for files uploaded to storage.
  • file_path: The pointer to the file in storage (S3/Local).
  • document_type: Categorizes the file (e.g., bank_statement, gst_return, financial_statement).

Specialized Analysis Tables

The system uses specialized tables to store structured data extracted from documents by AI providers. These tables are designed for fast retrieval and automated credit decisioning.

Bank Statement Analysis

The bank_statement_analysis table flattens complex banking data into queryable metrics while retaining the original transaction history.

| Column | Type | Description | | :--- | :--- | :--- | | average_balance | Numeric | Calculated average over the period. | | net_income | Numeric | Total credits minus total debits. | | negative_balance_days | Integer | Count of days the account was overdrawn. | | transactions | JSONB | List of extracted line items. | | enhanced_analysis | JSONB | AI-generated fraud risk and health scores. |

Financial Statement Analysis

Used for P&L, Balance Sheets, and Income Tax Returns (ITR).

• statement_type: Enum (balance_sheet, profit_loss, itr).
• Financial Metrics: Stores standardized fields like revenue, total_assets, and net_income.
• Ratios: Pre-calculated AI-driven ratios such as debt_to_equity_ratio, current_ratio, and dscr.

GST and Reconciliation

• gst_returns_analysis: Stores extracted turnover, tax liabilities, and filing consistency from GST documents.
• reconciliation_analysis: A specialized table that compares data across sources (e.g., comparing turnover reported in GST vs. actual credits in Bank Statements) to identify discrepancies.

Activity Tracking

The application_activities table serves as the audit log for every application. It tracks:

• Document uploads and deletions.
• AI analysis triggers and completions.
• Status changes performed by staff.

Interfacing with the Database

When interacting with the database via the Supabase client, ensure that the organization_id is included in filters to maintain data integrity.

Example: Fetching Application with Analysis

To retrieve a comprehensive view for the Credit Memo:

const { data, error } = await supabase
  .from('loan_applications')
  .select(`
    *,
    borrowers (*),
    bank_statement_analysis (*),
    financial_statement_analysis (*)
  `)
  .eq('id', applicationId)
  .single();

Data Storage Policy

• Sensitive Data: Highly sensitive financial fields are stored in JSONB columns (raw_ai_response) to allow for evolving AI models without constant schema migrations.
• Multi-tenancy: All tables implement Row Level Security (RLS) policies ensuring users can only access data where profiles.organization_id == table.organization_id.