# External Credit Data Import Roadmap

Date: 2026-06-20

## Important: See CSV Import Guide

**For step-by-step instructions and best practices, refer to the comprehensive guide:**

📖 **[CSV Legacy Import Guide](./CSV_LEGACY_IMPORT_GUIDE.md)**

This document covers:
- CSV file format and supported headers
- Admin UI upload process
- Row-level import feedback
- Data reconciliation and totals validation
- Troubleshooting and FAQ

---

## Purpose

This roadmap describes how to import historical loan and payment data from an existing credit lending system into LendCore without breaking current loan, repayment, and reporting logic.

The sample source format is the `paul test.csv` file with columns:

- `Day`
- `Member`
- `ID Number`
- `Address`
- `Reference`
- `Phone`
- `Guarantor`
- `Disbursed`
- `Cycle`
- `Principal`
- `Payable`
- `Paid`
- `Outstanding`
- `Due`
- `Arrears`

## High-level strategy

1. Treat this as onboarding existing active customers, not new applicants.
2. Do not re-create registration or LAF payments; set all imported loans to have `registration_fee = 0` and `laf_fee = 0`.
3. Mark imported users as `approved` if they already existed in the prior system.
4. Create loans as already active (`status = active`) and disbursed.
5. Preserve meaningful totals and allow LendCore reports to show correct `Total Loans Disbursed`, `Total Repaid`, `Outstanding Balance`, and `Current Fines`.
6. Avoid modifying existing LendCore business logic; instead import data into the existing model structure and let the existing admin summary calculations work.

## Business assumptions

- The imported users were already active in the previous system.
- Registration and LAF have already been paid externally.
- We do not need to re-run tier assignment decisions for their historical state, except to satisfy minimum LendCore model requirements.
- Imported loans should be visible as real loans in user and loan reports.

## Recommended import approach

### 1. Validate and normalize CSV columns

- Confirm the expected columns exist.
- Normalize header names to a consistent internal schema:
  - `Member` -> `member_name`
  - `ID Number` -> `national_id`
  - `Address` -> `address`
  - `Reference` -> `reference`
  - `Phone` -> `phone`
  - `Guarantor` -> `guarantor`
  - `Disbursed` -> `disbursed_date`
  - `Cycle` -> `cycle`
  - `Principal` -> `principal`
  - `Payable` -> `payable`
  - `Paid` -> `paid`
  - `Outstanding` -> `outstanding`
  - `Due` -> `due`
  - `Arrears` -> `arrears`

### 2. User handling

- Prefer matching existing users by `national_id`.
- If the user already exists, update their contact info and ensure `status = approved`.
- Do not create a new user if the import is supposed to only onboard already-existing customers; instead log unmatched rows for later reconciliation.
- If a placeholder user must be created, do so with minimal data and mark `status = approved`.
- Use a safe unique email generation strategy if email is missing.

### 3. Loan creation policy

- Create one loan record per imported CSV row using:
  - `amount` = `principal`
  - `disbursement_date` = parsed `disbursed_date`
  - `reference` = `reference`
  - `guarantor` = `guarantor`
  - `cycle` = `cycle`
  - `status` = `active`
  - `laf_fee` = `0`
  - `registration_fee` = `0`
  - `laf_fee_paid` = `0`
  - `registration_fee_paid` = `0`
  - `fee_payment_completed_at` = now()

- Derive `interest` from source fields, if possible:
  - `interest` = `payable` - `principal` when `payable` represents total payable on the loan.

- Set `total_payable` = `principal + interest`.

- If loan duration or repayment schedule cannot be inferred, keep schedule fields conservative and rely on actual payments and outstanding balances for reporting.

### 4. Loan status and reporting consistency

To keep admin summary views consistent (for example `admin/users/{id}`):

- Ensure the imported loan is visible under `user->loans` with status `active` or `completed`.
- Ensure the user has at least one disbursement transaction, so `totalLoansDisbursed` is not zero.
- If `Outstanding` > 0, preserve that as either loan `outstanding_balance` or as a set of active repayments with correct balance.
- If `Paid` > 0, insert historical payment transactions and/or repayment records so `Total Repaid` and `Outstanding Balance` match the source data.
- If `Arrears` is present, map it to either `loan.arrears` or a repayment schedule with overdue installments.

### 5. Fees and LAF/Reg handling

- Because the previous system already paid registration and LAF, imported records should not require these fees.
- Set:
  - `registration_fee = 0`
  - `registration_fee_paid = 0`
  - `laf_fee = 0`
  - `laf_fee_paid = 0`
- This preserves the imported loan as a post-fee active loan and avoids re-triggering loan approval/disbursement fee logic.

### 6. Payment history reconstruction

- Use `Paid` as the amount already repaid on the loan.
- If possible, create a historical payment transaction using `recordLoanPayment()` or `transactionService` so the payment is reflected in loan and user summaries.
- Do not re-run regular repayments unless they represent actual past installments. Instead, use a one-off imported payment to reduce the loan balance.

### 7. Outstanding/Current due representation

- `Outstanding` should be the remaining balance on the loan after imported payments.
- `Due` may represent current installment balance; if you cannot map it precisely to schedule installments, preserve it in `loan.amount_due`.
- `Arrears` should be preserved in `loan.arrears` and optionally create overdue repayment records.

### 8. Preserve existing LendCore structure

- Do not change the core schema or model relationships just to match external data.
- Import into existing `users`, `loans`, `transactions`, and `repayments` structures.
- Avoid introducing a parallel data model for imported loans.
- Use existing services where possible: `LoanService`, `RepaymentService`, `TransactionService`.

## Recommended import sequence

1. Run a validation pass against the CSV and confirm the 7 already-uploaded users.
2. Identify any rows whose `national_id` does not match existing LendCore users.
3. For matched users, create loans and transactions with active/disbursed status.
4. After import, inspect these values in admin user detail views:
   - `Total Loans Disbursed`
   - `Total Repaid`
   - `Current Fines`
   - `Outstanding Balance`
5. Compare results against the original CSV totals.
6. Adjust mapping logic for `Payable`, `Due`, and `Arrears` if summary totals diverge.

## Special note on `Total Loans Disbursed`

The user summary screen computes `totalLoansDisbursed` from the user's loans and from transaction summaries. If imported loans are not marked as `active`/`completed` or if no disbursement transaction exists, that value may stay at `KSh 0.00`.

Therefore, the import must ensure:

- each imported loan is in a status counted by the admin summary,
- at least one disbursement transaction exists for the loan,
- the loan amount field reflects the actual principal disbursed.

## Practical mapping suggestions

- `Principal` -> `loan.amount`
- `Payable` -> `loan.total_payable` (or `loan.amount + loan.interest`)
- `Paid` -> imported payment transaction(s)
- `Outstanding` -> `loan.outstanding_balance` and/or repayment `balance`
- `Due` -> `loan.amount_due` or current due repayment balance
- `Arrears` -> `loan.arrears` and/or overdue repayment/fine state

## Safeguards

- Do not update the current `status` of active users except to ensure `approved`.
- Do not create registration or LAF fee postings for imported loans.
- Do not infer a loan as new application; treat it as already onboarded.
- Keep the import script reversible by logging imported rows and using a source tag like `['import_source' => 'legacy_system']` on transactions.

## Testing and validation

- Start with the 7 users already imported as the baseline.
- Validate one user end-to-end in the admin UI (`admin/users/{id}`) and verify loan totals.
- Confirm user summary values match the imported totals from the CSV.
- Confirm report pages and repayment listings still work after the import.

## Recommended file and command

- Create a dedicated import command or seeder, e.g. `php artisan import:legacy-loans path/to/paul	est.csv`
- Keep source-specific logic isolated in one import class.
- Tag imported records clearly with metadata or transaction notes.

## Outcome

The goal is to bring external continuing loan data into LendCore while maintaining:

- accurate loan disbursed totals,
- correct outstanding and repayment balances,
- `approved` status for imported users,
- zero registration and LAF requirements,
- intact existing LendCore logic for reports and dashboards.
