# Backend Specification

## 1. System Summary

**System Name:** Simple Loan Calculator

**Description:** A single-page web application that calculates loan payments, total interest, and amortization schedules for anonymous users without authentication. The system provides instant loan calculations using standard amortization formulas, generates complete payment schedules, and tracks usage analytics through anonymous session management.

**Key Features:**
- Loan payment calculation using standard amortization formula
- Complete amortization schedule generation for entire loan term
- Anonymous session tracking for analytics
- Support for loan amounts up to $100M with terms up to 50 years
- Real-time validation and calculation results
- Export capabilities for results and schedules

## 2. Source Input Summary

This specification is derived from:
- **DDL Schema:** `/home/ubuntu/dpg/pipeline/step-02-prd-generation/output/20260514_022907/schema.sql` containing `calculation_sessions`, `calculations`, and `amortization_entries` tables
- **PRD Document:** `/home/ubuntu/dpg/pipeline/step-02-prd-generation/output/20260514_022907/full_prd.md` describing loan calculator workflows, calculation engine, amortization schedule generation, and analytics tracking
- **Mode:** Strict (exact adherence to source specifications)

## 3. Generation Mode

**Mode:** `strict`

The backend implementation strictly follows the provided DDL schema and PRD requirements without additions or modifications beyond what is explicitly specified.

## 4. Backend Scope

The backend encompasses a single module:

| Module | Description | Entities |
|--------|-------------|----------|
| **calculation** | Handles loan calculations, amortization schedules, and session tracking for anonymous users | CalculationSession, Calculation, AmortizationEntry |

**Core Responsibilities:**
- Perform loan payment calculations using amortization formulas
- Generate complete amortization schedules with payment breakdowns
- Track anonymous user sessions for analytics
- Validate input parameters against business rules
- Store calculation history for analytics purposes
- Provide detailed calculation results with full payment schedules

## 5. Roles

| Role | Description | Permissions |
|------|-------------|-------------|
| **Anonymous User** | Unauthenticated user accessing the loan calculator | Can create sessions, perform calculations, view results, and access amortization schedules |

**Note:** The system does not implement authentication or authorization. All users are treated as anonymous with full access to calculation features.

## 6. Entities and Fields

### 6.1 CalculationSession

**Table:** `calculation_sessions`

**Description:** Tracks anonymous user sessions for analytics and usage patterns.

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique identifier |
| session_id | str | Yes | Unique session identifier for tracking user activity |
| first_access_at | datetime | Yes | When session started |
| last_access_at | datetime | Yes | Most recent activity timestamp |
| calculation_count | int | Yes | Number of calculations performed in this session |
| user_agent | Optional[str] | No | Browser/device information |
| ip_address | Optional[str] | No | User IP address (anonymized for privacy) |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Status Field:** None

**Indexes:**
- Primary key on `id`
- Unique index on `session_id`

### 6.2 Calculation

**Table:** `calculations`

**Description:** Represents a single loan calculation instance with all input parameters and computed results.

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique identifier |
| session_id | str | Yes | Reference to calculation session |
| principal | float | Yes | Loan amount borrowed |
| annual_interest_rate | float | Yes | Annual interest rate as decimal (e.g., 4.5 for 4.5%) |
| loan_term_months | int | Yes | Duration of loan in months |
| monthly_payment | float | Yes | Calculated monthly payment amount |
| total_interest | float | Yes | Total interest paid over loan term |
| total_amount | float | Yes | Total amount paid (principal + interest) |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Status Field:** None

**Indexes:**
- Primary key on `id`
- Foreign key on `session_id` referencing `calculation_sessions(id)`
- Index on `session_id` for efficient session-based queries

### 6.3 AmortizationEntry

**Table:** `amortization_entries`

**Description:** Represents a single payment period in the loan amortization schedule.

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| id | str | Yes | Unique identifier |
| calculation_id | str | Yes | Reference to parent calculation |
| payment_number | int | Yes | Sequential payment number (1 to N) |
| payment_date | date | Yes | Scheduled payment date |
| beginning_balance | float | Yes | Loan balance at start of period |
| payment_amount | float | Yes | Total payment for the period |
| principal_portion | float | Yes | Amount applied to principal |
| interest_portion | float | Yes | Amount applied to interest |
| ending_balance | float | Yes | Loan balance at end of period |
| cumulative_interest | float | Yes | Total interest paid to date |
| cumulative_principal | float | Yes | Total principal paid to date |
| created_at | datetime | Yes | Creation timestamp |
| updated_at | datetime | Yes | Last update timestamp |

**Status Field:** None

**Indexes:**
- Primary key on `id`
- Foreign key on `calculation_id` referencing `calculations(id)`
- Composite index on `(calculation_id, payment_number)` for efficient schedule retrieval

## 7. Enumerations

No enumerations are defined for this system.

## 8. Relationships

| Relationship | Type | Navigation Required | Description |
|--------------|------|---------------------|-------------|
| Calculation → CalculationSession | Many-to-One | No | Each calculation belongs to one session; navigation not required for basic operations |
| AmortizationEntry → Calculation | Many-to-One | Yes | Each amortization entry belongs to one calculation; navigation required for retrieving complete schedules |

**Relationship Details:**

- **Calculation.session_id → CalculationSession.id**: A calculation is associated with a session for analytics tracking. The relationship is not eagerly loaded by default.

- **AmortizationEntry.calculation_id → Calculation.id**: Amortization entries form the payment schedule for a calculation. This relationship is eagerly loaded when retrieving calculation details to provide the complete amortization schedule.

## 9. API Endpoints

### 9.1 CalculationSession Endpoints

| Method | Route | Description | Request Body | Response |
|--------|-------|-------------|--------------|----------|
| POST | `/sessions` | Create or retrieve anonymous session | `{ "session_id": "string" }` (optional) | CalculationSession object |
| GET | `/sessions/{session_id}/calculations` | List calculations for a session | N/A | List of Calculation objects |
| GET | `/calculation-sessions/{id}` | Get session by ID | N/A | CalculationSession object |
| PUT | `/calculation-sessions/{id}` | Update session | CalculationSession fields | Updated CalculationSession |
| DELETE | `/calculation-sessions/{id}` | Delete session | N/A | Success confirmation |

### 9.2 Calculation Endpoints

| Method | Route | Description | Request Body | Response |
|--------|-------|-------------|--------------|----------|
| POST | `/calculations/calculate` | Perform loan calculation and generate amortization schedule | `{ "session_id": "string", "principal": float, "annual_interest_rate": float, "loan_term_months": int }` | Calculation object with summary |
| GET | `/calculations/{id}` | Get calculation by ID | N/A | Calculation object |
| GET | `/calculations/{id}/details` | Get calculation with full amortization schedule | N/A | Calculation with nested amortization_entries array |
| GET | `/calculations/{id}/amortization` | Get amortization schedule for a calculation | N/A | List of AmortizationEntry objects |
| PUT | `/calculations/{id}` | Update calculation | Calculation fields | Updated Calculation |
| DELETE | `/calculations/{id}` | Delete calculation | N/A | Success confirmation |

### 9.3 AmortizationEntry Endpoints

| Method | Route | Description | Request Body | Response |
|--------|-------|-------------|--------------|----------|
| GET | `/amortization-entries/{id}` | Get amortization entry by ID | N/A | AmortizationEntry object |
| POST | `/amortization-entries` | Create amortization entry | AmortizationEntry fields | Created AmortizationEntry |
| PUT | `/amortization-entries/{id}` | Update amortization entry | AmortizationEntry fields | Updated AmortizationEntry |
| DELETE | `/amortization-entries/{id}` | Delete amortization entry | N/A | Success confirmation |

### 9.4 Endpoint Details

#### POST /calculations/calculate

**Purpose:** Primary endpoint for performing loan calculations and generating complete amortization schedules.

**Request Body:**
```json
{
  "session_id": "uuid-string",
  "principal": 250000.00,
  "annual_interest_rate": 4.5,
  "loan_term_months": 360
}
```

**Response:**
```json
{
  "id": "calc-uuid",
  "session_id": "session-uuid",
  "principal": 250000.00,
  "annual_interest_rate": 4.5,
  "loan_term_months": 360,
  "monthly_payment": 1266.71,
  "total_interest": 206015.60,
  "total_amount": 456015.60,
  "created_at": "2024-01-15T10:30:00Z",
  "updated_at": "2024-01-15T10:30:00Z"
}
```

**Processing:**
1. Validate input parameters against business rules
2. Calculate monthly payment using amortization formula
3. Generate complete amortization schedule (N entries)
4. Store calculation and all amortization entries in transaction
5. Update session calculation count and last access time
6. Return calculation summary

#### GET /calculations/{id}/details

**Purpose:** Retrieve calculation with complete amortization schedule in a single response.

**Response:**
```json
{
  "id": "calc-uuid",
  "session_id": "session-uuid",
  "principal": 250000.00,
  "annual_interest_rate": 4.5,
  "loan_term_months": 360,
  "monthly_payment": 1266.71,
  "total_interest": 206015.60,
  "total_amount": 456015.60,
  "amortization_entries": [
    {
      "payment_number": 1,
      "payment_date": "2024-02-15",
      "beginning_balance": 250000.00,
      "payment_amount": 1266.71,
      "principal_portion": 329.21,
      "interest_portion": 937.50,
      "ending_balance": 249670.79,
      "cumulative_interest": 937.50,
      "cumulative_principal": 329.21
    },
    // ... remaining entries
  ],
  "created_at": "2024-01-15T10:30:00Z",
  "updated_at": "2024-01-15T10:30:00Z"
}
```

**Eager Loading:** This endpoint uses eager loading to fetch all amortization entries in a single query to avoid N+1 query problems.

#### POST /sessions

**Purpose:** Create a new anonymous session or retrieve an existing one.

**Request Body (Optional):**
```json
{
  "session_id": "client-generated-uuid"
}
```

**Response:**
```json
{
  "id": "db-uuid",
  "session_id": "session-uuid",
  "first_access_at": "2024-01-15T10:00:00Z",
  "last_access_at": "2024-01-15T10:00:00Z",
  "calculation_count": 0,
  "user_agent": "Mozilla/5.0...",
  "ip_address": "192.168.xxx.xxx",
  "created_at": "2024-01-15T10:00:00Z",
  "updated_at": "2024-01-15T10:00:00Z"
}
```

## 10. Workflow Logic

### 10.1 Perform Loan Calculation

**Trigger:** User submits loan parameters (principal, interest rate, term)

**Steps:**
1. **Input Validation**
   - Validate principal is between $1.00 and $100,000,000.00
   - Validate annual interest rate is between 0.00% and 99.99%
   - Validate loan term is between 1 and 600 months
   - Return validation errors if any parameter is invalid

2. **Calculate Monthly Payment**
   - If interest rate > 0: Use formula M = P × [i(1 + i)^n] / [(1 + i)^n - 1]
     - Where: P = principal, i = annual_rate / 12 / 100, n = loan_term_months
   - If interest rate = 0: M = principal / loan_term_months
   - Round result to 2 decimal places using banker's rounding

3. **Calculate Totals**
   - total_amount = monthly_payment × loan_term_months
   - total_interest = total_amount - principal

4. **Generate Amortization Schedule**
   - Create N entries where N = loan_term_months
   - For each payment period (1 to N):
     - payment_date = calculation_date + (payment_number × 30 days)
     - beginning_balance = previous ending_balance (or principal for first payment)
     - interest_portion = beginning_balance × monthly_interest_rate
     - principal_portion = monthly_payment - interest_portion
     - ending_balance = beginning_balance - principal_portion
     - cumulative_interest += interest_portion
     - cumulative_principal += principal_portion
   - Adjust final payment if needed to ensure ending_balance = $0.00

5. **Store Results**
   - Begin database transaction
   - Create Calculation record
   - Create all AmortizationEntry records
   - Update CalculationSession (increment calculation_count, update last_access_at)
   - Commit transaction

6. **Return Response**
   - Return calculation summary with computed values
   - Client can subsequently fetch full amortization schedule if needed

**Error Handling:**
- Validation errors return 400 Bad Request with detailed error messages
- Database errors rollback transaction and return 500 Internal Server Error
- Calculation timeout (>500ms) returns 408 Request Timeout

### 10.2 View Amortization Schedule

**Trigger:** User requests detailed payment schedule for a calculation

**Steps:**
1. Validate calculation ID exists
2. Fetch calculation record
3. Fetch all amortization entries ordered by payment_number
4. Return calculation with nested amortization entries array
5. Client displays schedule in tabular format

### 10.3 Reset Calculator

**Trigger:** User clicks reset/clear button

**Steps:**
1. Client clears all input fields
2. Client clears displayed results
3. No backend interaction required
4. Session remains active for subsequent calculations

### 10.4 Export/Print Results

**Trigger:** User requests export or print of calculation results

**Steps:**
1. Client fetches calculation details with full amortization schedule
2. Client formats data for export (PDF, CSV) or print
3. For CSV: Generate rows with all amortization entry fields
4. For PDF: Format calculation summary and schedule table
5. For Print: Apply print-friendly CSS styling
6. No backend processing required (client-side export)

**Future Enhancement:** Backend could provide export endpoints for server-side generation.

## 11. Business Logic

### 11.1 Loan Calculation Rules

1. **Principal Validation**
   - Must be between $1.00 and $100,000,000.00
   - Must be positive
   - Rounded to 2 decimal places

2. **Interest Rate Validation**
   - Must be between 0.00% and 99.99%
   - Must be non-negative
   - Supports up to 4 decimal places (e.g., 4.5625%)

3. **Loan Term Validation**
   - Must be between 1 and 600 months (50 years)
   - Must be positive integer only

4. **Monthly Payment Calculation**
   - Standard formula: M = P × [i(1 + i)^n] / [(1 + i)^n - 1]
   - Where: i = annual_rate / 12 / 100
   - Special case: If interest rate is 0%, monthly payment = principal / number of months

5. **Amortization Schedule Generation**
   - Generates exactly N entries where N = loan term in months
   - First payment date estimated as 30 days from calculation date
   - Subsequent payments 30 days apart (simplified monthly calculation)

6. **Interest Calculation per Period**
   - Interest for each period = Beginning balance × Monthly interest rate
   - Rounded to 2 decimal places

7. **Principal Calculation per Period**
   - Principal portion = Monthly payment - Interest portion

8. **Final Payment Adjustment**
   - Final payment may be adjusted within $1.00 to ensure balance reaches exactly $0.00
   - Prevents rounding errors from accumulating

9. **Rounding Rules**
   - All currency values use banker's rounding (round half to even)
   - All amounts rounded to 2 decimal places

### 11.2 Session Management Rules

1. **Session Creation**
   - Sessions created on first access or when client provides session_id
   - Session ID must be valid UUID format
   - IP addresses anonymized for GDPR/CCPA compliance

2. **Session Tracking**
   - calculation_count incremented with each calculation
   - last_access_at updated on every session activity
   - User agent and IP address captured for analytics

3. **Session Expiration**
   - Sessions expire after 24 hours of inactivity
   - Expired sessions eligible for cleanup

4. **Data Retention**
   - Calculations stored for 90 days maximum for analytics purposes
   - Automatic cleanup of old data

### 11.3 Rate Limiting

1. **Request Limits**
   - 100 requests per minute per IP address
   - Prevents abuse and ensures fair usage
   - Returns 429 Too Many Requests when exceeded

### 11.4 Performance Requirements

1. **Calculation Performance**
   - Calculation must complete within 500ms
   - Amortization schedule generation within 1 second
   - Timeout returns 408 Request Timeout

## 12. Validation Rules

### 12.1 Input Validation

| Field | Validation Rules |
|-------|------------------|
| **principal** | • Must be numeric<br>• Must be positive<br>• Must be >= $1.00<br>• Must be <= $100,000,000.00<br>• Rounded to 2 decimal places |
| **annual_interest_rate** | • Must be numeric<br>• Must be non-negative<br>• Must be >= 0.00%<br>• Must be <= 99.99%<br>• Supports up to 4 decimal places |
| **loan_term_months** | • Must be integer<br>• Must be positive<br>• Must be >= 1 month<br>• Must be <= 600 months |
| **session_id** | • Must be valid UUID format<br>• Must exist in database for calculation operations |

### 12.2 Calculation Validation

1. **Required Fields**
   - All three input fields (principal, annual_interest_rate, loan_term_months) must be provided
   - Missing fields return 400 Bad Request with field-specific error messages

2. **Amortization Entry Validation**
   - payment_number must be positive integer and sequential (1, 2, 3, ...)
   - payment_number must not exceed loan_term_months
   - ending_balance in final entry must equal $0.00 (within $1.00 tolerance)

3. **Calculation Integrity**
   - Sum of all principal_portions must equal original principal (within rounding tolerance)
   - Sum of all interest_portions must equal total_interest (within rounding tolerance)
   - Each ending_balance must equal beginning_balance - principal_portion

### 12.3 Performance Validation

1. **Timeout Limits**
   - Calculation operations must complete within 500ms
   - Amortization schedule generation must complete within 1 second
   - Operations exceeding limits return 408 Request Timeout

### 12.4 Error Response Format

All validation errors return structured error responses:

```json
{
  "error": "Validation Error",
  "details": [
    {
      "field": "principal",
      "message": "Principal must be between $1.00 and $100,000,000.00"
    }
  ]
}
```

## 13. Implementation Notes

### 13.1 Technology Stack

- **Framework:** FastAPI (Python web framework)
- **ORM:** SQLAlchemy ORM with SQLite database
- **Validation:** Pydantic v2 for request/response models and validation
- **Dependency Injection:** FastAPI `Depends(get_db)` for database session management
- **Package Management:** uv for fast Python package management
- **API Documentation:** Automatic OpenAPI (Swagger) documentation via FastAPI

### 13.2 Architecture Pattern

**Service Layer Pattern:**
- **Controllers (Routers):** Handle HTTP requests/responses, input validation
- **Service Layer:** Contains business logic, calculation algorithms, workflow orchestration
- **Repository Layer:** Data access through SQLAlchemy ORM
- **Models:** SQLAlchemy models for database entities
- **Schemas:** Pydantic models for API request/response validation

### 13.3 Database Session Management

- Use FastAPI dependency injection with `Depends(get_db)` for database sessions
- Automatic session lifecycle management (creation, commit, rollback, close)
- Transaction management for batch operations (calculation + amortization entries)

### 13.4 Calculation Engine

**Implementation Details:**
- Separate calculation service module for loan formulas
- Pure functions for mathematical calculations (testable, reusable)
- Amortization schedule generator as batch operation
- Rounding utilities using Python's `decimal` module for precision

### 13.5 Batch Write Service

**create_calculation_with_amortization:**
- Single transaction for creating calculation and all amortization entries
- Atomic operation ensures data consistency
- Rollback on any failure prevents partial data
- Optimized bulk insert for amortization entries

### 13.6 Eager Loading

**GET /calculations/{id}/details:**
- Uses SQLAlchemy `joinedload` or `selectinload` for amortization_entries
- Single query retrieves calculation with all related entries
- Prevents N+1 query problem
- Improves response time for large schedules

### 13.7 API Documentation

- Automatic OpenAPI schema generation at `/docs` (Swagger UI)
- ReDoc documentation at `/redoc`
- Schema includes all endpoints, request/response models, validation rules
- Interactive API testing through Swagger UI

### 13.8 Error Handling

- Global exception handlers for common errors
- Validation errors return 400 with detailed field-level messages
- Database errors return 500 with generic message (log details server-side)
- Not found errors return 404
- Rate limit errors return 429

### 13.9 Logging

- Structured logging for all calculation operations
- Log input parameters, calculation results, execution time
- Error logging with stack traces
- Analytics logging for session tracking

### 13.10 CORS Configuration

- Configure CORS for single-page application frontend
- Allow credentials for session cookie management
- Restrict origins in production environment

## 14. Assumptions

1. **User Authentication**
   - Users are anonymous and do not require authentication
   - No user accounts, login, or registration

2. **Session Management**
   - Session tracking uses browser-generated UUID stored in cookie or local storage
   - Client is responsible for generating and maintaining session ID
   - Sessions are for analytics only, not security

3. **Payment Schedule**
   - First payment date is estimated as 30 days from calculation date (not user-specified)
   - All payments are equal except potentially the final payment for rounding adjustment
   - Payment frequency is monthly only (no bi-weekly, weekly, etc.)

4. **Currency and Localization**
   - Currency is USD ($ symbol) with future support for other currencies
   - Date format follows MM/DD/YYYY or locale-based formatting
   - Number formatting uses US conventions (comma thousands separator, period decimal)

5. **Legal and Compliance**
   - Calculations are estimates and application provides no financial advice
   - Disclaimer required on frontend
   - IP addresses are anonymized for GDPR/CCPA compliance
   - No personally identifiable information is collected or stored

6. **Data Retention**
   - Calculations stored for 90 days for analytics
   - Automatic cleanup of expired data
   - No long-term storage or archival

7. **Performance**
   - Expected load: moderate (hundreds of calculations per hour)
   - SQLite sufficient for initial deployment
   - Single-server deployment initially

8. **Export Functionality**
   - Export/print handled client-side initially
   - No server-side PDF or CSV generation in initial version

9. **Calculation Accuracy**
   - Standard amortization formula provides estimates
   - Actual loan terms may vary based on lender policies
   - Application not responsible for financial decisions

## 15. Future Improvements

### 15.1 Database and Infrastructure

1. **Alembic Migrations**
   - Implement Alembic for database schema migrations
   - Version control for database changes
   - Support for rollback and forward migrations

2. **PostgreSQL Migration**
   - Migrate from SQLite to PostgreSQL for production
   - Improved concurrency and performance
   - Better support for analytics queries

3. **Docker Containerization**
   - Dockerize application for consistent deployment
   - Docker Compose for local development
   - Container orchestration for scaling

### 15.2 Authentication and Authorization

1. **JWT Authentication**
   - Implement JWT-based authentication for registered users
   - Allow users to save and retrieve calculation history
   - Secure API endpoints

2. **Role-Based Access Control (RBAC)**
   - Admin role for system management
   - User role for authenticated features
   - Analytics role for reporting access

### 15.3 Testing

1. **Unit Tests**
   - Test calculation algorithms and business logic
   - Test validation rules
   - Test service layer functions

2. **Integration Tests**
   - Test API endpoints end-to-end
   - Test database operations
   - Test transaction handling

3. **Performance Tests**
   - Load testing for concurrent calculations
   - Stress testing for large amortization schedules
   - Response time benchmarking

### 15.4 Feature Enhancements

1. **Payment Frequency Options**
   - Support for bi-weekly or weekly payment frequencies
   - Adjust amortization calculations accordingly

2. **Extra Payment Scenarios**
   - Calculate impact of extra payments
   - Payoff date calculation with additional payments
   - Interest savings visualization

3. **Loan Comparison**
   - Compare multiple loan scenarios side-by-side
   - Visual comparison of total costs
   - Recommendation engine

4. **Advanced Loan Types**
   - Support for adjustable-rate mortgages (ARM)
   - Balloon payment calculations
   - Interest-only periods

5. **Refinancing Calculator**
   - Compare current loan vs refinancing options
   - Break-even analysis
   - Closing cost considerations

6. **Multi-Currency Support**
   - Support for additional currencies (EUR, GBP, etc.)
   - Currency conversion
   - Locale-specific formatting

7. **Multi-Language Support**
   - Internationalization (i18n) for interface
   - Translated content and labels
   - Locale-based date and number formatting

8. **Advanced Visualizations**
   - Payment breakdown over time (charts)
   - Principal vs interest graphs
   - Equity buildup visualization
   - Interactive amortization charts

9. **Share and Save**
   - Save calculation results via unique URL
   - Share calculations with others
   - Bookmark favorite scenarios

10. **Mobile Applications**
    - Native iOS app
    - Native Android app
    - Responsive web design optimization

11. **Financial Tool Integration**
    - Integration with financial planning tools
    - Export to personal finance software
    - API for third-party integrations

12. **Server-Side Export**
    - Backend PDF generation
    - Backend CSV export
    - Email results functionality