# Backend Specification

## 1. System Summary

**System Name:** Simple Loan Calculator

**Description:** A single-page web application that calculates loan payments and generates detailed amortization schedules for anonymous users. The backend provides REST API endpoints for performing loan calculations, persisting results, and retrieving complete amortization schedules with payment breakdowns.

**Primary Users:** Anonymous users (no authentication required)

**Core Functionality:**
- Calculate monthly loan payments using standard amortization formula
- Generate complete amortization schedules showing principal/interest breakdown
- Persist calculation results with full payment schedules
- Retrieve detailed amortization data for completed calculations
- Support edge cases including zero-interest loans

## 2. Source Input Summary

This backend specification is generated from:

- **DDL Schema File:** `/home/ubuntu/dpg/pipeline/step-02-prd-generation/output/20260514_035937/schema.sql`
  - Defines `loan_calculations` and `amortization_schedule_entries` tables
  - Establishes foreign key relationships and constraints
  
- **PRD File:** `/home/ubuntu/dpg/pipeline/step-02-prd-generation/output/20260514_035937/full_prd.md`
  - Comprehensive product requirements including calculation formulas
  - Detailed workflows, business rules, and validation requirements
  - UI specifications and user interaction patterns

- **Source Prompt:** Provided
- **Images:** Not provided

## 3. Generation Mode

**Mode:** `strict`

The backend implementation strictly adheres to the provided schema, PRD requirements, and analysis specification. All entities, fields, constraints, validation rules, and business logic are implemented exactly as specified without deviation or interpretation.

## 4. Backend Scope

The backend system provides:

1. **RESTful API endpoints** for loan calculation operations (create, read, list, delete)
2. **Server-side calculation engine** implementing amortization formulas
3. **Data persistence layer** for storing calculation results and schedules
4. **Validation layer** enforcing all business rules and constraints
5. **Transactional batch operations** for atomic creation of calculations with schedules
6. **Eager loading capabilities** for efficient retrieval of calculations with full schedules

**Out of Scope:**
- User authentication and authorization
- Frontend application code
- Payment processing or actual loan disbursement
- Multi-currency support (USD assumed)
- Real-time calculation updates or WebSocket connections

## 5. Roles

### AnonymousUser

**Description:** Unauthenticated users who can perform loan calculations without creating accounts.

**Permissions:**
- Create loan calculations with amortization schedules
- Retrieve calculation results and detailed schedules
- List all calculations (pagination supported)
- Delete calculations

**Restrictions:**
- None (all operations available to anonymous users)

## 6. Entities and Fields

### 6.1 LoanCalculation

**Table:** `loan_calculations`

**Description:** Represents a single loan calculation instance with input parameters and computed results including monthly payment, total amount paid, and total interest paid.

| Field Name | Type | Required | Description | Constraints |
|------------|------|----------|-------------|-------------|
| id | str (UUID) | Yes | Unique identifier | Primary key |
| principal_amount | Decimal | Yes | Initial loan amount borrowed | Min: 1, Max: 100,000,000 |
| annual_interest_rate | Decimal | Yes | Yearly interest rate as percentage | Min: 0.01, Max: 99.99 |
| loan_term_months | int | Yes | Duration of loan in months | Min: 1, Max: 600 |
| monthly_payment | Decimal | Yes | Calculated fixed monthly payment amount | Computed, non-negative |
| total_amount_paid | Decimal | Yes | Calculated total amount paid over loan lifetime | Computed, non-negative |
| total_interest_paid | Decimal | Yes | Calculated total interest paid over loan lifetime | Computed, non-negative |
| created_at | datetime | Yes | Timestamp when calculation was performed | Auto-set on creation |
| updated_at | datetime | Yes | Timestamp of last update | Auto-updated |

**Status Field:** None

### 6.2 AmortizationScheduleEntry

**Table:** `amortization_schedule_entries`

**Description:** Represents a single payment period in the loan amortization schedule showing payment breakdown and remaining balance.

| Field Name | Type | Required | Description | Constraints |
|------------|------|----------|-------------|-------------|
| id | str (UUID) | Yes | Unique identifier | Primary key |
| calculation_id | str (UUID) | Yes | Foreign key reference to parent loan calculation | FK to loan_calculations.id |
| payment_number | int | Yes | Sequential payment number (1 to N) | Positive integer, min: 1 |
| payment_date | date | No | Scheduled payment date | Optional |
| payment_amount | Decimal | Yes | Total payment amount for this period | Non-negative |
| principal_portion | Decimal | Yes | Amount applied to principal reduction | Non-negative |
| interest_portion | Decimal | Yes | Amount applied to interest | Non-negative |
| remaining_balance | Decimal | Yes | Outstanding principal after this payment | Non-negative |
| cumulative_interest | Decimal | Yes | Total interest paid up to this point | Non-negative |
| cumulative_principal | Decimal | Yes | Total principal paid up to this point | Non-negative |
| created_at | datetime | Yes | Creation timestamp | Auto-set on creation |
| updated_at | datetime | Yes | Last update timestamp | Auto-updated |

**Status Field:** None

## 7. Enumerations

No enumerations are defined for this system.

## 8. Relationships

### 8.1 AmortizationScheduleEntry → LoanCalculation

**Type:** Many-to-One (required)

**Description:** Each amortization schedule entry belongs to exactly one loan calculation.

**Navigation:** Yes (required for retrieving complete schedules)

**Foreign Key:** `amortization_schedule_entries.calculation_id` → `loan_calculations.id`

**Cascade Behavior:** Deleting a LoanCalculation cascades delete to all associated AmortizationScheduleEntry records

**Cardinality:** One LoanCalculation has many (0 to N) AmortizationScheduleEntry records, where N equals loan_term_months

## 9. API Endpoints

### 9.1 Create Loan Calculation

**Endpoint:** `POST /loan-calculations`

**Description:** Creates a new loan calculation with complete amortization schedule in a single transaction.

**Request Body:**
```json
{
  "principal_amount": "Decimal (1 to 100,000,000)",
  "annual_interest_rate": "Decimal (0.01 to 99.99)",
  "loan_term_months": "int (1 to 600)"
}
```

**Response:** `201 Created`
```json
{
  "id": "uuid",
  "principal_amount": "Decimal",
  "annual_interest_rate": "Decimal",
  "loan_term_months": "int",
  "monthly_payment": "Decimal",
  "total_amount_paid": "Decimal",
  "total_interest_paid": "Decimal",
  "created_at": "datetime",
  "updated_at": "datetime"
}
```

**Errors:**
- `400 Bad Request` - Invalid input parameters or validation failures
- `500 Internal Server Error` - Calculation or persistence errors

### 9.2 Get Loan Calculation Summary

**Endpoint:** `GET /loan-calculations/{id}`

**Description:** Retrieves summary information for a specific loan calculation without schedule details.

**Path Parameters:**
- `id` (UUID) - Loan calculation identifier

**Response:** `200 OK`
```json
{
  "id": "uuid",
  "principal_amount": "Decimal",
  "annual_interest_rate": "Decimal",
  "loan_term_months": "int",
  "monthly_payment": "Decimal",
  "total_amount_paid": "Decimal",
  "total_interest_paid": "Decimal",
  "created_at": "datetime",
  "updated_at": "datetime"
}
```

**Errors:**
- `404 Not Found` - Calculation ID does not exist

### 9.3 Get Loan Calculation with Full Schedule

**Endpoint:** `GET /loan-calculations/{id}/details`

**Description:** Retrieves loan calculation with complete amortization schedule (eager-loaded).

**Path Parameters:**
- `id` (UUID) - Loan calculation identifier

**Response:** `200 OK`
```json
{
  "id": "uuid",
  "principal_amount": "Decimal",
  "annual_interest_rate": "Decimal",
  "loan_term_months": "int",
  "monthly_payment": "Decimal",
  "total_amount_paid": "Decimal",
  "total_interest_paid": "Decimal",
  "created_at": "datetime",
  "updated_at": "datetime",
  "amortization_schedule_entries": [
    {
      "id": "uuid",
      "payment_number": "int",
      "payment_date": "date | null",
      "payment_amount": "Decimal",
      "principal_portion": "Decimal",
      "interest_portion": "Decimal",
      "remaining_balance": "Decimal",
      "cumulative_interest": "Decimal",
      "cumulative_principal": "Decimal"
    }
  ]
}
```

**Errors:**
- `404 Not Found` - Calculation ID does not exist

### 9.4 List Loan Calculations

**Endpoint:** `GET /loan-calculations`

**Description:** Retrieves paginated list of all loan calculations (summary only, no schedules).

**Query Parameters:**
- `page` (int, default: 1) - Page number
- `page_size` (int, default: 20, max: 100) - Items per page
- `sort_by` (string, default: "created_at") - Sort field
- `sort_order` (string, default: "desc") - Sort direction (asc/desc)

**Response:** `200 OK`
```json
{
  "items": [
    {
      "id": "uuid",
      "principal_amount": "Decimal",
      "annual_interest_rate": "Decimal",
      "loan_term_months": "int",
      "monthly_payment": "Decimal",
      "total_amount_paid": "Decimal",
      "total_interest_paid": "Decimal",
      "created_at": "datetime",
      "updated_at": "datetime"
    }
  ],
  "total": "int",
  "page": "int",
  "page_size": "int",
  "total_pages": "int"
}
```

**Errors:**
- `400 Bad Request` - Invalid pagination parameters

### 9.5 Delete Loan Calculation

**Endpoint:** `DELETE /loan-calculations/{id}`

**Description:** Deletes a loan calculation and cascades delete to all associated amortization schedule entries.

**Path Parameters:**
- `id` (UUID) - Loan calculation identifier

**Response:** `204 No Content`

**Errors:**
- `404 Not Found` - Calculation ID does not exist
- `500 Internal Server Error` - Deletion failure

## 10. Workflow Logic

### 10.1 Perform Loan Calculation Workflow

**Trigger:** User submits POST request to `/loan-calculations` with principal amount, annual interest rate, and loan term months.

**Steps:**

1. **Input Validation**
   - Validate principal_amount is between 1 and 100,000,000
   - Validate annual_interest_rate is between 0.01 and 99.99
   - Validate loan_term_months is between 1 and 600
   - Return `400 Bad Request` if any validation fails

2. **Calculate Monthly Payment**
   - Convert annual rate to monthly rate: `r = annual_interest_rate / 12 / 100`
   - If interest rate is 0%: `monthly_payment = principal_amount / loan_term_months`
   - Else: Apply amortization formula `M = P[r(1+r)^n]/[(1+r)^n-1]`
     - P = principal_amount
     - r = monthly interest rate (decimal)
     - n = loan_term_months
   - Round to 2 decimal places for storage

3. **Calculate Total Amounts**
   - `total_amount_paid = monthly_payment × loan_term_months`
   - `total_interest_paid = total_amount_paid - principal_amount`

4. **Generate Amortization Schedule**
   - Initialize `remaining_balance = principal_amount`
   - Initialize `cumulative_interest = 0`
   - Initialize `cumulative_principal = 0`
   - For each payment period (1 to loan_term_months):
     - Calculate `interest_portion = remaining_balance × monthly_interest_rate`
     - Calculate `principal_portion = monthly_payment - interest_portion`
     - Update `remaining_balance = remaining_balance - principal_portion`
     - Update `cumulative_interest += interest_portion`
     - Update `cumulative_principal += principal_portion`
     - Handle final payment adjustment to ensure `remaining_balance = 0.00`
     - Create AmortizationScheduleEntry record

5. **Persist Data**
   - Begin database transaction
   - Insert LoanCalculation record with calculated values
   - Bulk insert all AmortizationScheduleEntry records
   - Commit transaction
   - Return `500 Internal Server Error` if transaction fails

6. **Return Response**
   - Return `201 Created` with LoanCalculation summary JSON

**Service:** `create_loan_calculation_with_schedule`

### 10.2 View Amortization Schedule Details Workflow

**Trigger:** User requests GET `/loan-calculations/{id}/details`

**Steps:**

1. **Retrieve Calculation**
   - Query LoanCalculation by ID with eager loading of amortization_schedule_entries
   - Return `404 Not Found` if calculation does not exist

2. **Order Schedule Entries**
   - Sort amortization_schedule_entries by payment_number ascending

3. **Return Response**
   - Return `200 OK` with complete calculation and schedule JSON
   - Include all schedule entry fields: payment_number, payment_date, payment_amount, principal_portion, interest_portion, remaining_balance, cumulative_interest, cumulative_principal

### 10.3 Reset Calculator Workflow

**Trigger:** User deletes calculation via DELETE `/loan-calculations/{id}`

**Steps:**

1. **Validate Existence**
   - Query LoanCalculation by ID
   - Return `404 Not Found` if calculation does not exist

2. **Delete Calculation**
   - Begin database transaction
   - Delete LoanCalculation record (cascade deletes all AmortizationScheduleEntry records)
   - Commit transaction
   - Return `500 Internal Server Error` if transaction fails

3. **Return Response**
   - Return `204 No Content` on successful deletion

## 11. Business Logic

### 11.1 Input Constraints

- **Principal Amount:** Must be between $1 and $100,000,000 inclusive
- **Annual Interest Rate:** Must be between 0.01% and 99.99% inclusive, supporting up to 4 decimal places
- **Loan Term:** Must be between 1 and 600 months (1 to 50 years)

### 11.2 Monthly Payment Calculation

**Standard Formula (interest rate > 0%):**

```
M = P[r(1+r)^n]/[(1+r)^n-1]

Where:
  M = Monthly payment
  P = Principal amount
  r = Monthly interest rate (annual_rate / 12 / 100)
  n = Number of payments (loan_term_months)
```

**Special Case (interest rate = 0%):**

```
M = P / n

Where:
  M = Monthly payment
  P = Principal amount
  n = Number of payments (loan_term_months)
```

### 11.3 Amortization Schedule Generation

**Requirements:**
- Generate exactly N entries where N equals loan_term_months
- Each entry must be calculated sequentially using previous remaining balance

**Per-Payment Calculations:**

```
interest_portion = remaining_balance × monthly_interest_rate
principal_portion = monthly_payment - interest_portion
new_remaining_balance = previous_remaining_balance - principal_portion
cumulative_interest += interest_portion
cumulative_principal += principal_portion
```

**Final Payment Adjustment:**
- The final payment may differ slightly to ensure remaining balance reaches exactly $0.00
- Tolerance: remaining balance must be within ±$0.01

### 11.4 Rounding and Precision

- **Display Values:** All currency values rounded to 2 decimal places
- **Internal Calculations:** Use higher precision (Decimal type) to minimize rounding errors
- **Final Balance:** Must be exactly $0.00 (within $0.01 tolerance)

### 11.5 Data Integrity Rules

- **Payment Number:** Must be positive integer starting from 1, sequential
- **Remaining Balance:** Must be non-negative (>= 0)
- **Cumulative Interest:** Must be non-negative (>= 0)
- **Cumulative Principal:** Must be non-negative (>= 0)
- **Cascade Delete:** Deleting a LoanCalculation removes all associated AmortizationScheduleEntry records

### 11.6 Performance Requirements

- All calculations must complete within 500ms for up to 600 payment periods
- Batch insert of schedule entries should use bulk operations

## 12. Validation Rules

### 12.1 Input Validation (Request Body)

| Field | Rule | Error Message |
|-------|------|---------------|
| principal_amount | Must be numeric | "Principal amount must be a valid number" |
| principal_amount | Must be positive | "Principal amount must be positive" |
| principal_amount | Must be >= 1 | "Principal amount must be at least $1" |
| principal_amount | Must be <= 100,000,000 | "Principal amount cannot exceed $100,000,000" |
| annual_interest_rate | Must be numeric | "Annual interest rate must be a valid number" |
| annual_interest_rate | Must be positive | "Annual interest rate must be positive" |
| annual_interest_rate | Must be >= 0.01 | "Annual interest rate must be at least 0.01%" |
| annual_interest_rate | Must be <= 99.99 | "Annual interest rate cannot exceed 99.99%" |
| loan_term_months | Must be integer | "Loan term must be a whole number" |
| loan_term_months | Must be positive | "Loan term must be positive" |
| loan_term_months | Must be >= 1 | "Loan term must be at least 1 month" |
| loan_term_months | Must be <= 600 | "Loan term cannot exceed 600 months (50 years)" |

### 12.2 Computed Field Validation

| Field | Rule | Error Condition |
|-------|------|-----------------|
| payment_number | Must be > 0 | Internal calculation error if <= 0 |
| remaining_balance | Must be >= 0 | Calculation error if negative |
| cumulative_interest | Must be >= 0 | Calculation error if negative |
| cumulative_principal | Must be >= 0 | Calculation error if negative |
| remaining_balance (final) | Must be ~= 0.00 | Final payment did not zero balance (within $0.01) |

### 12.3 Database Constraints

- **UUID Fields:** Must be valid UUID v4 format
- **Foreign Keys:** calculation_id must reference existing loan_calculations.id
- **Unique Constraints:** None explicitly required
- **NOT NULL:** All required fields must have non-null values

## 13. Implementation Notes

### 13.1 Technology Stack

- **Framework:** FastAPI (Python 3.10+)
- **ORM:** SQLAlchemy (with SQLite for development)
- **Validation:** Pydantic v2 for request/response models
- **Dependency Injection:** FastAPI `Depends(get_db)` for database session management
- **Package Management:** `uv` for fast dependency resolution
- **API Documentation:** Automatic OpenAPI (Swagger) documentation via FastAPI

### 13.2 Architecture Pattern

**Service Layer Pattern:**
- **API Layer:** FastAPI route handlers in `routers/` directory
- **Service Layer:** Business logic in `services/loan_calculator_service.py`
  - `create_loan_calculation_with_schedule()` - Batch write service
  - `calculate_monthly_payment()` - Pure calculation function
  - `generate_amortization_schedule()` - Schedule generation logic
- **Repository Layer:** SQLAlchemy ORM models in `models/` directory
- **Schema Layer:** Pydantic models in `schemas/` directory
  - Request schemas (e.g., `LoanCalculationCreate`)
  - Response schemas (e.g., `LoanCalculationResponse`, `LoanCalculationDetailResponse`)

### 13.3 Database Session Management

```python
# Dependency injection pattern
def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

# Usage in routes
@router.post("/loan-calculations")
def create_calculation(
    request: LoanCalculationCreate,
    db: Session = Depends(get_db)
):
    return loan_calculator_service.create_loan_calculation_with_schedule(db, request)
```

### 13.4 Eager Loading Implementation

For the `/loan-calculations/{id}/details` endpoint:

```python
# Use SQLAlchemy joinedload
from sqlalchemy.orm import joinedload

calculation = db.query(LoanCalculation)\
    .options(joinedload(LoanCalculation.amortization_schedule_entries))\
    .filter(LoanCalculation.id == calculation_id)\
    .first()
```

### 13.5 Transactional Batch Operations

The `create_loan_calculation_with_schedule` service must:
1. Begin a transaction
2. Insert LoanCalculation record
3. Generate all AmortizationScheduleEntry records
4. Bulk insert schedule entries using `db.bulk_insert_mappings()` or similar
5. Commit transaction
6. Rollback on any error and return appropriate HTTP error

### 13.6 Decimal Precision

Use Python's `decimal.Decimal` type for all monetary calculations:
```python
from decimal import Decimal, ROUND_HALF_UP

# Example rounding
monthly_payment = calculated_value.quantize(Decimal('0.01'), rounding=ROUND_HALF_UP)
```

### 13.7 API Documentation

FastAPI automatically generates:
- **OpenAPI specification:** Available at `/openapi.json`
- **Swagger UI:** Available at `/docs`
- **ReDoc:** Available at `/redoc`

All endpoints, request schemas, response schemas, and validation rules are documented automatically.

### 13.8 Error Handling

Implement consistent error responses:
```python
{
  "detail": "Error message",
  "error_code": "VALIDATION_ERROR | NOT_FOUND | INTERNAL_ERROR",
  "field_errors": {
    "field_name": ["Error message for field"]
  }
}
```

## 14. Assumptions

1. **Anonymous Access:** No user authentication or authorization is required; all calculations are anonymous
2. **Server-Side Calculations:** All loan calculations and amortization schedule generation occur server-side via API (not client-side JavaScript)
3. **Persistence Optional:** Backend provides persistence, but client may choose to use API for calculation only without storing results
4. **Cascade Delete Enabled:** Deleting a LoanCalculation automatically removes all associated AmortizationScheduleEntry records via database cascade
5. **Payment Dates Optional:** Payment dates are optional; if provided by client in future, calculated from start date
6. **Single Currency:** All calculations assume USD ($); no currency conversion or multi-currency support
7. **Synchronous Operations:** All API endpoints are synchronous (no async/background jobs required)
8. **Performance Acceptable:** 500ms response time for 600-month calculations is acceptable
9. **No Rate Limiting:** No rate limiting or abuse prevention required for anonymous access
10. **Standard Amortization:** Only fixed-rate, fully-amortizing loans with equal monthly payments are supported (no balloon payments, variable rates, or interest-only periods)
11. **Database SQLite:** Development and initial deployment use SQLite; production migration to PostgreSQL expected
12. **No Audit Trail:** No requirement to track who created/modified calculations or maintain change history

## 15. Future Improvements

### 15.1 Infrastructure and DevOps

- **Alembic Migrations:** Implement database migration management using Alembic for schema versioning and rollback capabilities
- **PostgreSQL Migration:** Migrate from SQLite to PostgreSQL for production deployment with improved concurrency and performance
- **Docker Containerization:** Create Dockerfile and docker-compose.yml for consistent deployment across environments
- **CI/CD Pipeline:** Set up automated testing, linting, and deployment pipelines

### 15.2 Security and Authentication

- **JWT Authentication:** Implement JWT-based authentication for user accounts (optional for anonymous users)
- **Role-Based Access Control (RBAC):** Add roles for different user types (e.g., authenticated users can save calculation history)
- **Rate Limiting:** Implement rate limiting to prevent API abuse (e.g., 100 calculations per IP per hour)
- **Input Sanitization:** Enhanced validation to prevent injection attacks

### 15.3 Testing

- **Unit Tests:** Comprehensive unit tests for calculation logic, validation, and services
- **Integration Tests:** API endpoint tests covering all success and error scenarios
- **Load Testing:** Performance testing for concurrent requests and large-scale calculations
- **Test Coverage:** Achieve >90% code coverage with pytest

### 15.4 Feature Enhancements

- **Payment Frequency Options:** Support bi-weekly, weekly, or custom payment frequencies
- **Extra Payments:** Model scenarios with extra principal payments showing accelerated payoff
- **Loan Comparison:** Compare multiple loan scenarios side-by-side
- **Export Capabilities:** Export amortization schedules to CSV, Excel, or PDF formats
- **Visualization Data:** Provide data endpoints for charting principal vs. interest over time
- **Multi-Currency Support:** Support multiple currencies with real-time conversion rates
- **Balloon Payments:** Support loans with balloon payment structures
- **User History:** Store calculation history with localStorage or optional user accounts
- **Loan Type Presets:** Predefined configurations for mortgages, auto loans, personal loans
- **Effective Annual Rate:** Calculate and display EAR vs. nominal APR
- **Refinancing Calculator:** Compare current loan against refinancing options

### 15.5 Performance Optimization

- **Caching:** Implement Redis caching for frequently accessed calculations
- **Async Operations:** Migrate to async endpoints for improved concurrency
- **Bulk Operations API:** Support batch creation of multiple calculations in single request
- **Database Indexing:** Add indexes on frequently queried fields (created_at, calculation_id)

### 15.6 Monitoring and Observability

- **Application Logging:** Structured logging with log levels and request tracing
- **Metrics Collection:** Prometheus metrics for API performance, error rates, and usage patterns
- **Health Checks:** Implement `/health` and `/readiness` endpoints for orchestration
- **Error Tracking:** Integrate Sentry or similar for error monitoring and alerting