Loan File Data Extraction Feature

Overview

This feature enhances the AI document analysis system to extract comprehensive loan file information from uploaded documents. The extracted data is automatically populated into the loan file (project) record, providing a complete view of loan details, lender information, property characteristics, and construction specifications.

NEW: Document Upload Loan File Creation - Users can now create new loan files by simply uploading a document. The AI will extract all necessary information and create a complete loan file automatically.

Features

1. Document Upload Loan File Creation (NEW)

  • Upload Document: Users can upload a loan document (PDF, image, etc.) from the loan files page
  • AI Extraction: The system automatically extracts all loan file information from the document
  • Automatic Creation: Creates a complete loan file with all extracted data
  • Initial Draw: Automatically creates an initial draw and links the uploaded document
  • Smart Validation: Prevents duplicate loan numbers and validates extracted data

2. Existing Draw Document Enhancement

  • Enhanced Extraction: When documents are uploaded to draws, the system now extracts 24 additional data points
  • Smart Updates: Only updates empty/placeholder fields, preserving existing data
  • Comprehensive Analysis: Extracts lender, loan, inspection, physical, and unit details

New Data Points Extracted

Lender Details

  • Company: Lender company name
  • Originator: Loan originator name
  • Phone: Lender contact phone number

Loan Details

  • Loan Number: String/Numeric ID (e.g., 3001250818)
  • Loan Type: Text/Category (e.g., "Choice Reno - Freddie Mac")
  • Origination: Text/Optional field
  • FHA Case Number: String/Optional field
  • HUD Date: Date/Optional field
  • Contingency Reserve %: Percentage/Numeric (e.g., 10)
  • Number of Draws: Integer (e.g., 5)

Inspection Details

  • Inspection Date: Date (e.g., 2/21/2025)
  • Inspection Occupancy: Text/Category (e.g., "Occupied")
  • Completion Occupancy: Text/Category (e.g., "Owner Occupied")
  • Months to Completion: Integer (e.g., 6)
  • Investor/Builder: Boolean/Checkbox (true/false)
  • Months Uninhabitable During Construction: Integer (e.g., 0)

Physical Details

  • Lot Size: Decimal with Unit (e.g., "0.42 acres")
  • Estimated Age: Integer with Unit (e.g., "63 Years")
  • Building Size: Integer with Unit (e.g., "1853 sq/ft")
  • Number of Stories: Integer (e.g., 2)
  • Construction Type: Text/Category (e.g., "Wood Frame")

Unit/Dwelling Counts

  • Number of Units at Start: Integer (e.g., 1)
  • Number of Units at Completion: Integer (e.g., 1)
  • Number of Dwellings at Start: Integer (e.g., 1)
  • Number of Dwellings at Completion: Integer (e.g., 1)

Database Schema Changes

New Fields Added to projects Table

-- Lender Details
ALTER TABLE "projects" ADD COLUMN "lender_company" text;
ALTER TABLE "projects" ADD COLUMN "lender_originator" text;
ALTER TABLE "projects" ADD COLUMN "lender_phone" text;

-- Loan Details
ALTER TABLE "projects" ADD COLUMN "loan_type" text;
ALTER TABLE "projects" ADD COLUMN "origination" text;
ALTER TABLE "projects" ADD COLUMN "fha_case_number" text;
ALTER TABLE "projects" ADD COLUMN "hud_date" timestamp;
ALTER TABLE "projects" ADD COLUMN "contingency_reserve_percent" numeric(5, 2);
ALTER TABLE "projects" ADD COLUMN "number_of_draws" integer;

-- Inspection Details
ALTER TABLE "projects" ADD COLUMN "inspection_date" timestamp;
ALTER TABLE "projects" ADD COLUMN "inspection_occupancy" text;
ALTER TABLE "projects" ADD COLUMN "completion_occupancy" text;
ALTER TABLE "projects" ADD COLUMN "months_to_completion" integer;
ALTER TABLE "projects" ADD COLUMN "is_investor_builder" boolean DEFAULT false;
ALTER TABLE "projects" ADD COLUMN "months_uninhabitable" integer;

-- Physical Details
ALTER TABLE "projects" ADD COLUMN "lot_size" text;
ALTER TABLE "projects" ADD COLUMN "estimated_age" integer;
ALTER TABLE "projects" ADD COLUMN "building_size" integer;
ALTER TABLE "projects" ADD COLUMN "number_of_stories" integer;
ALTER TABLE "projects" ADD COLUMN "construction_type" text;

-- Unit/Dwelling Counts
ALTER TABLE "projects" ADD COLUMN "units_at_start" integer;
ALTER TABLE "projects" ADD COLUMN "units_at_completion" integer;
ALTER TABLE "projects" ADD COLUMN "dwellings_at_start" integer;
ALTER TABLE "projects" ADD COLUMN "dwellings_at_completion" integer;

AI Analysis Updates

Enhanced DocumentAnalysisResult Type

The AI analysis now returns a comprehensive structure including:

type DocumentAnalysisResult = {
  property_details: {
    address: string;
    buildingSize: string;
    lotSize: string;
    estimatedAge: string;
    monthsToCompletion: string;
    numberOfStories: string;
    constructionType: string;
  };
  borrower_details: {
    name: string;
    loanNumber: string;
    consultant: string;
    contractor: string;
  };
  lender_details: {
    company: string;
    originator: string;
    phone: string;
  };
  loan_details: {
    loanNumber: string;
    loanType: string;
    origination: string;
    fhaCaseNumber: string;
    hudDate: string;
    contingencyReservePercent: string;
    numberOfDraws: string;
  };
  inspection_details: {
    inspectionDate: string;
    inspectionOccupancy: string;
    completionOccupancy: string;
    monthsToCompletion: string;
    isInvestorBuilder: string;
    monthsUninhabitable: string;
  };
  physical_details: {
    lotSize: string;
    estimatedAge: string;
    buildingSize: string;
    numberOfStories: string;
    constructionType: string;
  };
  unit_details: {
    unitsAtStart: string;
    unitsAtCompletion: string;
    dwellingsAtStart: string;
    dwellingsAtCompletion: string;
  };
  renovation_costs: {
    [category: string]: string;
  };
  additional_fees: {
    workWriteUp: string;
    drawInspections: string;
    permits: string;
    architecturalDrawing: string;
    allowableFeesTotal: string;
    contingencyReserve: string;
  };
  totals: {
    constructionCostsSubtotal: string;
    grandTotal: string;
  };
};

Automatic Data Population

When documents are uploaded and analyzed:

  1. AI Extraction: The AI models (Claude and Gemini) extract all relevant data points from the document
  2. Smart Updates: The system intelligently updates loan file fields only when:
    • Current field is empty, "TBD", "N/A", or starts with "TEMP-"
    • Extracted value is valid and not "Unknown" or "N/A"
  3. Data Validation: Values are parsed and validated before storage:
    • Integers are extracted from text (e.g., "63 Years" → 63)
    • Decimals are parsed for percentages
    • Dates are converted to proper timestamp format
    • Booleans are converted from "true"/"false" strings

UI Enhancements

Loan File Creation Options

The loan files page now offers two creation methods:

  • Upload Document (NEW): Purple button that launches the document upload flow
  • Create Manually: Traditional manual entry form

Document Upload Page (NEW)

  • Smart Upload Interface: Drag-and-drop or click to upload loan documents
  • Real-time Progress: Shows upload and AI processing progress
  • Comprehensive Information Display: Shows what data will be extracted
  • Automatic Redirection: Takes users to the newly created loan file

Loan File Details Page

The loan file details page now displays additional information in organized sections:

  • Lender & Loan Information: Company, originator, phone, loan type, FHA case number, HUD date, contingency reserve, planned draws
  • Property & Construction Details: Inspection details, occupancy information, physical characteristics, unit counts

Document Analysis Results

The document analysis results component now shows all extracted data points in organized sections:

  • Lender Details
  • Loan Details
  • Inspection Details
  • Physical Details
  • Unit/Dwelling Counts

Benefits

  1. Streamlined Loan File Creation: Users can create complete loan files by simply uploading a document
  2. Reduced Manual Entry: Eliminates the need to manually enter loan file information
  3. Automated Data Entry: Reduces manual data entry by automatically extracting loan file information from documents
  4. Comprehensive Records: Maintains complete loan file records with all relevant details
  5. Improved Accuracy: AI extraction reduces human error in data entry
  6. Better Organization: Structured data enables better reporting and analysis
  7. Enhanced User Experience: Users can see complete loan file information at a glance
  8. Time Savings: Creates loan files in seconds instead of minutes

Usage

Creating Loan Files from Documents (NEW)

  1. Navigate to Loan Files: Go to the loan files page
  2. Upload Document: Click the "Upload Document" button (purple)
  3. Select File: Choose a loan document (PDF, image, etc.)
  4. AI Processing: Wait for AI to extract information (usually 30-60 seconds)
  5. Review Results: System automatically creates loan file and redirects to it
  6. Edit if Needed: Manual edits can be made to any extracted information

Traditional Manual Creation

  1. Navigate to Loan Files: Go to the loan files page
  2. Create Manually: Click the "Create Manually" button
  3. Enter Information: Fill out the form with loan file details
  4. Submit: Create the loan file and add draws/documents later

Existing Draw Document Enhancement

  1. Upload Documents: When uploading documents to any draw, the AI will analyze them for loan file information
  2. Automatic Population: Extracted data automatically populates the loan file record
  3. View Information: Access the loan file details page to see all extracted information
  4. Manual Override: Users can still manually edit any fields if needed

Technical Implementation

Key Files Modified/Added

NEW FILES:

  • app/dashboard/loan-files/upload/page.tsx - Document upload page
  • components/document-upload-form-create.tsx - Upload form component
  • app/api/loan-files/create-from-document/route.ts - API endpoint for document processing

ENHANCED FILES:

  • app/dashboard/loan-files/page.tsx - Added upload button
  • drizzle/schema.ts - Added new database fields
  • utils/db/schema.ts - Updated schema with new fields
  • utils/ai/documentAnalysis.ts - Enhanced AI extraction logic
  • utils/ai/documentProcessor.ts - Updated project population logic
  • components/document-analysis-results.tsx - Enhanced UI display
  • app/dashboard/loan-files/[id]/page.tsx - Added loan file details display

API Endpoints

NEW:

  • POST /api/loan-files/create-from-document - Creates loan file from uploaded document

Migration

The database migration has been applied using:

yarn db:push

Future Enhancements

  • Bulk Upload: Support uploading multiple documents to create multiple loan files
  • Document Templates: Pre-configured extraction templates for different lender formats
  • OCR Improvements: Enhanced text extraction for scanned documents
  • Confidence Scoring: Show confidence levels for extracted data
  • Manual Review Queue: Allow manual review of extracted data before creation
  • Bulk Data Import: Import loan file data from spreadsheets or other systems
  • Data Validation Rules: Add business rules for data validation
  • Audit Trail: Track changes to loan file data
  • Reporting: Generate reports based on loan file characteristics
  • Integration: Connect with external loan management systems