# 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 real-time loan calculations using standard amortization formulas, generates complete payment schedules, and tracks anonymous user sessions for analytics purposes.

**Domain:** freemeal.dev.dalfin.ai

**Key Features:**
- Loan payment calculation using standard amortization formula
- Complete amortization schedule generation
- Anonymous session tracking for analytics
- Real-time input validation
- Export and print capabilities for results
- No authentication required

## 2. Source Input Summary

This backend specification is generated from the following source materials:

- **DDL Schema File:** `/home/ubuntu/dpg/pipeline/step-02-prd-generation/output/20260514_032743/schema.sql`
  - Defines three tables: `calculation_sessions`, `calculations`, and `amortization_entries`
  - Includes foreign key relationships and indexes
  
- **PRD File:** `/home/ubuntu/dpg/pipeline/step-02-prd-generation/output/20260514_032743/full_prd.md`
  - Describes loan calculator workflows and user interactions
  - Specifies calculation formulas and business rules
  - Details display requirements and validation rules

- **Domain URL:** freemeal.dev.dalfin.ai

## 3. Generation Mode

**Mode:** strict

In strict mode, the backend implementation adheres precisely to the entities, fields, relationships, and business rules defined in the source materials. No additional features or modifications beyond the specified requirements are included.

## 4. Backend Scope

The backend provides a RESTful API for:

1. **Loan Calculations:** Processing loan parameters and computing monthly payments, total interest, and total amounts
2. **Amortization Schedule Generation:** Creating detailed payment schedules for the entire loan term
3. **Session Management:** Tracking anonymous user sessions for analytics without requiring authentication
4. **Data Retrieval:** Fetching calculation details with associated amortization entries
5. **Analytics Support:** Recording session activity, calculation counts, and usage patterns

**Module Structure:**

| Module | Entities | Description |
|--------|----------|-------------|
| calculation | Calculationsession, Calculation, Amortizationentry | Handles loan calculation sessions, calculation results, and amortization schedule generation |

## 5. Roles

| Role | Description | Authentication Required |
|------|-------------|------------------------|
| Anonymous User | Any visitor to the application who can perform loan calculations and view results | No |

**Note:** The system is designed for anonymous access only. No user registration, login, or authentication mechanisms are implemented.

## 6. Entities and Fields

### 6.1 Calculationsession

**Table Name:** `calculation_sessions`

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

| Field Name | Type | Required | Description | Constraints |
|------------|------|----------|-------------|-------------|
| id | str | Yes | Unique identifier | Primary key |
| session_id | str | Yes | Unique session identifier for tracking user activity | Unique, indexed |
| 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 | Default: 0 |
| 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 | Auto-generated |
| updated_at | datetime | Yes | Last update timestamp | Auto-updated |

**Status Field:** None

### 6.2 Calculation

**Table Name:** `calculations`

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

| Field Name | Type | Required | Description | Constraints |
|------------|------|----------|-------------|-------------|
| id | str | Yes | Unique identifier | Primary key |
| session_id | str | Yes | Reference to calculation session | Foreign key to calculation_sessions.session_id |
| principal | float | Yes | Loan amount borrowed (must be > 0) | > 0, <= 100,000,000 |
| annual_interest_rate | float | Yes | Annual interest rate as decimal (must be >= 0) | >= 0, < 100 |
| loan_term_months | int | Yes | Duration of loan in months (must be > 0) | > 0, <= 600 |
| monthly_payment | float | Yes | Calculated monthly payment amount | Computed value |
| total_interest | float | Yes | Total interest paid over loan term | Computed value |
| total_amount | float | Yes | Total amount paid (principal + interest) | Computed value |
| created_at | datetime | Yes | Creation timestamp | Auto-generated |
| updated_at | datetime | Yes | Last update timestamp | Auto-updated |

**Status Field:** None

### 6.3 Amortizationentry

**Table Name:** `amortization_entries`

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

| Field Name | Type | Required | Description | Constraints |
|------------|------|----------|-------------|-------------|
| id | str | Yes | Unique identifier | Primary key |
| calculation_id | str | Yes | Reference to parent calculation | Foreign key to calculations.id |
| payment_number | int | Yes | Sequential payment number (1 to N, must be > 0) | > 0 |
| payment_date | date | Yes | Scheduled payment date | - |
| beginning_balance | float | Yes | Loan balance at start of period (must be >= 0) | >= 0 |
| 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 (must be >= 0) | >= 0 |
| cumulative_interest | float | Yes | Total interest paid to date | - |
| cumulative_principal | float | Yes | Total principal paid to date | - |
| created_at | datetime | Yes | Creation timestamp | Auto-generated |
| updated_at | datetime | Yes | Last update timestamp | Auto-updated |

**Status Field:** None

## 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 calculation session |
| AmortizationEntry → Calculation | Many-to-One | Yes | Each amortization entry belongs to one calculation; navigation required for retrieving complete schedules |

**Relationship Details:**

- **Calculation.session_id** references **CalculationSession.session_id**
- **AmortizationEntry.calculation_id** references **Calculation.id**
- Amortization entries must be eagerly loaded when retrieving calculation details to provide complete payment schedules

## 9. API Endpoints

### 9.1 CalculationSession Endpoints

| Method | Route | Description | Request Body | Response |
|--------|-------|-------------|--------------|----------|
| POST | `/calculation-sessions` | Create a new calculation session | CalculationSessionCreate | CalculationSession |
| GET | `/calculation-sessions/{id}` | Get calculation session by ID | - | CalculationSession |
| GET | `/calculation-sessions` | List all calculation sessions | Query params: skip, limit | List[CalculationSession] |
| PUT | `/calculation-sessions/{id}` | Update calculation session | CalculationSessionUpdate | CalculationSession |
| DELETE | `/calculation-sessions/{id}` | Delete calculation session | - | 204 No Content |
| POST | `/sessions/track` | Create or update calculation session | SessionTrackRequest | CalculationSession |
| GET | `/sessions/{session_id}/calculations` | Get all calculations for a session | - | List[Calculation] |

### 9.2 Calculation Endpoints

| Method | Route | Description | Request Body | Response |
|--------|-------|-------------|--------------|----------|
| POST | `/calculations` | Create a new calculation | CalculationCreate | Calculation |
| GET | `/calculations/{id}` | Get calculation by ID | - | Calculation |
| GET | `/calculations` | List all calculations | Query params: skip, limit | List[Calculation] |
| PUT | `/calculations/{id}` | Update calculation | CalculationUpdate | Calculation |
| DELETE | `/calculations/{id}` | Delete calculation | - | 204 No Content |
| POST | `/calculations/calculate` | Perform loan calculation and generate amortization schedule | LoanCalculationRequest | CalculationWithAmortization |
| GET | `/calculations/{id}/details` | Get calculation with full amortization schedule | - | CalculationDetails |
| GET | `/calculations/{id}/amortization` | Get amortization schedule for a calculation | - | List[AmortizationEntry] |

### 9.3 AmortizationEntry Endpoints

| Method | Route | Description | Request Body | Response |
|--------|-------|-------------|--------------|----------|
| POST | `/amortization-entries` | Create a new amortization entry | AmortizationEntryCreate | AmortizationEntry |
| GET | `/amortization-entries/{id}` | Get amortization entry by ID | - | AmortizationEntry |
| GET | `/amortization-entries` | List all amortization entries | Query params: skip, limit | List[AmortizationEntry] |
| PUT | `/amortization-entries/{id}` | Update amortization entry | AmortizationEntryUpdate | AmortizationEntry |
| DELETE | `/amortization-entries/{id}` | Delete amortization entry | - | 204 No Content |

### 9.4 Special Endpoint Details

**POST /calculations/calculate**

This endpoint performs the complete loan calculation workflow:
- Validates input parameters (principal, annual_interest_rate, loan_term_months)
- Calculates monthly payment using amortization formula
- Generates complete amortization schedule with N entries (where N = loan_term_months)
- Stores calculation and all amortization entries in a single transaction
- Returns calculation with embedded amortization schedule

**Request Schema:**
```
{
  "session_id": "string",
  "principal": float (> 0, <= 100,000,000),
  "annual_interest_rate": float (>= 0, < 100),
  "loan_term_months": int (> 0, <= 600)
}
```

**Response Schema:**
```
{
  "calculation": Calculation object,
  "amortization_entries": List[AmortizationEntry]
}
```

**GET /calculations/{id}/details**

This endpoint retrieves a calculation with its complete amortization schedule using eager loading for optimal performance.

**Response includes:**
- All calculation fields
- Complete array of amortization entries with all fields

## 10. Workflow Logic

### 10.1 Perform Loan Calculation

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

**Steps:**
1. Validate input parameters against validation rules
2. Calculate monthly interest rate: `monthly_rate = annual_interest_rate / 12 / 100`
3. If interest rate is 0%:
   - Calculate monthly payment: `monthly_payment = principal / loan_term_months`
4. If interest rate > 0%:
   - Apply amortization formula: `M = P × [i(1 + i)^n] / [(1 + i)^n - 1]`
   - Where: M = monthly payment, P = principal, i = monthly_rate, n = loan_term_months
5. Calculate total amount: `total_amount = monthly_payment × loan_term_months`
6. Calculate total interest: `total_interest = total_amount - principal`
7. Generate amortization schedule (see workflow 10.2)
8. Store calculation record with computed values
9. Store all amortization entries in batch
10. Update session calculation_count and last_access_at
11. Return calculation with complete amortization schedule

**Service:** `create_calculation_with_amortization`

### 10.2 Generate Amortization Schedule

**Trigger:** Part of loan calculation workflow

**Steps:**
1. Initialize variables:
   - `remaining_balance = principal`
   - `cumulative_interest = 0`
   - `cumulative_principal = 0`
   - `payment_date = calculation_date + 30 days`
2. For each payment period (1 to loan_term_months):
   - Set `beginning_balance = remaining_balance`
   - Calculate `interest_portion = beginning_balance × monthly_rate` (rounded to 2 decimals)
   - Calculate `principal_portion = monthly_payment - interest_portion`
   - Calculate `ending_balance = beginning_balance - principal_portion`
   - Update `cumulative_interest += interest_portion`
   - Update `cumulative_principal += principal_portion`
   - If final payment and ending_balance is not exactly 0:
     - Adjust payment_amount within $1.00 to zero out balance
   - Create AmortizationEntry with all calculated values
   - Increment `payment_date` by 30 days
   - Set `remaining_balance = ending_balance`
3. Return list of amortization entries

### 10.3 Track Session

**Trigger:** User visits application or performs calculation

**Steps:**
1. Check if session_id exists in request (cookie or header)
2. If session_id exists:
   - Retrieve existing CalculationSession by session_id
   - Update last_access_at to current timestamp
   - If calculation performed, increment calculation_count
3. If session_id does not exist:
   - Generate new UUID for session_id
   - Create new CalculationSession record
   - Set first_access_at and last_access_at to current timestamp
   - Set calculation_count to 0
   - Store user_agent from request headers
   - Store anonymized IP address
4. Return session_id to client for storage

### 10.4 View Amortization Schedule

**Trigger:** User requests calculation details

**Steps:**
1. Retrieve Calculation by ID
2. Eagerly load associated AmortizationEntry records
3. Order amortization entries by payment_number ascending
4. Return calculation with embedded amortization schedule
5. Frontend displays summary and expandable schedule table

### 10.5 Reset Calculator

**Trigger:** User clicks reset button

**Steps:**
1. Clear all input fields on frontend
2. Clear all displayed results
3. No backend interaction required
4. Session remains active

### 10.6 Export/Print Results

**Trigger:** User requests export or print

**Steps:**
1. Retrieve calculation details with amortization schedule
2. Format data according to export type (PDF, CSV, or print)
3. For CSV: Generate comma-separated values with headers
4. For PDF: Generate formatted document with summary and table
5. For Print: Return print-friendly HTML
6. Return formatted data to client

## 11. Business Logic

### 11.1 Loan Calculation Rules

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

2. **Interest Rate Validation:**
   - Must be between 0.00% and 99.99%
   - Must be non-negative (>= 0)
   - Supports up to 4 decimal places

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 = monthly interest rate, n = loan term in months
   - Special case: If interest rate is 0%, then `M = P / n`

5. **Amortization Schedule Rules:**
   - Generate exactly N entries where N = loan_term_months
   - First payment date estimated as 30 days from calculation date
   - Subsequent payments 30 days apart
   - Interest for each period = Beginning balance × Monthly interest rate (rounded to 2 decimals)
   - Principal portion = Monthly payment - Interest portion
   - Ending balance = Beginning balance - Principal portion
   - Final payment may be adjusted within $1.00 to ensure balance reaches exactly $0.00

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

### 11.2 Session Management Rules

1. **Session Creation:**
   - Create session on first visit
   - Generate UUID for session_id
   - Store in browser cookie or local storage

2. **Session Tracking:**
   - Update last_access_at on every request
   - Increment calculation_count when calculation performed
   - Store user_agent from request headers
   - Anonymize IP address before storage for GDPR compliance

3. **Session Expiration:**
   - Sessions expire after 24 hours of inactivity
   - Expired sessions may be purged from database

4. **Data Retention:**
   - Calculations stored for 90 days maximum for analytics purposes
   - After 90 days, records may be archived or deleted

### 11.3 Rate Limiting

1. **Request Limits:**
   - 100 requests per minute per IP address
   - Return 429 Too Many Requests if limit exceeded
   - Include Retry-After header in response

### 11.4 Privacy and Compliance

1. **Anonymous Usage:**
   - No personally identifiable information collected
   - No authentication required
   - Session tracking for analytics only

2. **IP Address Handling:**
   - IP addresses anonymized before storage
   - Last octet removed or hashed for IPv4
   - Last 80 bits removed for IPv6

3. **Disclaimer:**
   - Application provides estimates only
   - Does not constitute financial advice
   - Users should consult financial professionals for actual loan decisions

## 12. Validation Rules

### 12.1 Input Validation

| Field | Validation Rules | Error Message |
|-------|------------------|---------------|
| principal | Must be numeric, positive, > 0, <= 100,000,000 | "Principal must be between $1.00 and $100,000,000.00" |
| annual_interest_rate | Must be numeric, non-negative, >= 0, < 100 | "Interest rate must be between 0.00% and 99.99%" |
| loan_term_months | Must be integer, positive, > 0, <= 600 | "Loan term must be between 1 and 600 months" |
| payment_number | Must be positive integer, > 0 | "Payment number must be greater than 0" |
| beginning_balance | Must be non-negative, >= 0 | "Beginning balance must be non-negative" |
| ending_balance | Must be non-negative, >= 0 | "Ending balance must be non-negative" |

### 12.2 Format Validation

1. **Currency Format:**
   - Maximum 2 decimal places
   - Proper decimal format (e.g., 1234.56)
   - No currency symbols in input

2. **Interest Rate Format:**
   - Maximum 4 decimal places
   - Entered as percentage (e.g., 5.25 for 5.25%)
   - Converted to decimal for calculations (5.25% → 0.0525)

3. **Date Format:**
   - ISO 8601 format for API (YYYY-MM-DD)
   - Localized format for display

### 12.3 Real-time Validation

1. **Frontend Validation:**
   - Inline error messages for invalid inputs
   - Calculate button disabled when any input is invalid
   - Visual indicators for validation state (red border, error icon)

2. **Backend Validation:**
   - All inputs validated on server side
   - Return 400 Bad Request with detailed error messages
   - Validation errors include field name and specific issue

### 12.4 Business Rule Validation

1. **Calculation Integrity:**
   - Verify total_amount = principal + total_interest
   - Verify final amortization entry ending_balance = 0 (within $0.01)
   - Verify sum of all principal_portions = principal (within $0.01)
   - Verify sum of all interest_portions = total_interest (within $0.01)

2. **Relationship Validation:**
   - Verify session_id exists before creating calculation
   - Verify calculation_id exists before creating amortization entry
   - Prevent orphaned records

## 13. Implementation Notes

### 13.1 Technology Stack

- **Framework:** FastAPI (Python web framework)
- **ORM:** SQLAlchemy ORM with SQLite database
- **Validation:** Pydantic v2 for request/response schemas
- **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 (FastAPI route handlers) handle HTTP requests/responses
- Service layer contains business logic and orchestrates operations
- Repository layer (SQLAlchemy models) handles data persistence
- Clear separation of concerns for maintainability

### 13.3 Database Session Management

- Use FastAPI dependency injection with `Depends(get_db)`
- Database sessions created per request
- Automatic session cleanup after request completion
- Transaction management for batch operations

### 13.4 Batch Operations

**create_calculation_with_amortization Service:**
- Creates calculation and all amortization entries in single transaction
- Uses SQLAlchemy bulk operations for performance
- Rolls back entire transaction if any operation fails
- Ensures data consistency

### 13.5 Eager Loading

**Detail Endpoints:**
- Use SQLAlchemy `joinedload` or `selectinload` for eager loading
- Prevents N+1 query problems
- Optimizes performance for calculation details with amortization schedule

### 13.6 API Documentation

- Automatic OpenAPI documentation available at `/docs` (Swagger UI)
- ReDoc documentation available at `/redoc`
- Pydantic schemas automatically generate request/response examples
- Include descriptions and examples in schema definitions

### 13.7 Error Handling

- Use FastAPI HTTPException for standard HTTP errors
- Return consistent error response format:
  ```json
  {
    "detail": "Error message",
    "field": "field_name",
    "code": "ERROR_CODE"
  }
  ```
- Log errors for debugging and monitoring

### 13.8 CORS Configuration

- Configure CORS middleware for frontend domain
- Allow credentials for session cookie handling
- Restrict allowed origins in production

### 13.9 Calculation Precision

- Use Python Decimal type for financial calculations
- Convert to float only for database storage and API responses
- Implement banker's rounding (round half to even)

### 13.10 Session Management

- Generate UUID v4 for session identifiers
- Store session_id in HTTP-only cookie for security
- Include session_id in response headers for client-side storage option

## 14. Assumptions

1. **User Authentication:**
   - Users are anonymous and do not require authentication
   - No user registration or login functionality needed
   - Session tracking sufficient for analytics

2. **Session Tracking:**
   - Session tracking uses browser-generated UUID stored in cookie or local storage
   - Cookies are preferred but local storage is fallback
   - Session ID passed in request header or cookie

3. **Payment Schedule:**
   - First payment date is estimated as 30 days from calculation date (not user-specified)
   - All subsequent payments are 30 days apart
   - Payment dates are estimates only, not actual due dates

4. **Payment Amounts:**
   - All payments are equal except potentially the final payment
   - Final payment adjusted for rounding to ensure zero balance
   - Adjustment limited to $1.00 maximum

5. **Calculation Processing:**
   - Calculations are performed server-side for consistency and accuracy
   - Client-side validation for user experience only
   - Server-side validation is authoritative

6. **Privacy Compliance:**
   - IP addresses are anonymized before storage for GDPR compliance
   - No personally identifiable information is collected or stored
   - Session data used for analytics only, not user tracking

7. **Legal Disclaimer:**
   - Application provides estimates only
   - Does not constitute financial advice
   - Users responsible for verifying calculations with financial institutions

8. **Data Accuracy:**
   - Calculations use standard amortization formulas
   - Results may differ slightly from actual loan terms due to lender-specific rules
   - Rounding differences may occur

9. **Browser Support:**
   - Modern browsers with JavaScript enabled
   - Cookie or local storage support required for session tracking
   - Responsive design for mobile and desktop

10. **Performance:**
    - Expected load: moderate (< 1000 concurrent users)
    - SQLite sufficient for initial deployment
    - Calculations complete within 1 second for typical loan terms

## 15. Future Improvements

### 15.1 Database and Infrastructure

1. **Database Migration:**
   - Implement Alembic for database migrations
   - Support version control for schema changes
   - Enable rollback capabilities

2. **PostgreSQL Migration:**
   - Migrate from SQLite to PostgreSQL for production
   - Improve concurrent access and performance
   - Enable advanced indexing and query optimization

3. **Containerization:**
   - Create Docker containers for application
   - Docker Compose for local development
   - Kubernetes deployment for production scaling

### 15.2 Authentication and Authorization

1. **JWT Authentication:**
   - Implement JWT-based authentication for optional user accounts
   - Allow users to save calculation history
   - Maintain anonymous access as default

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

### 15.3 Testing

1. **Unit Tests:**
   - Test calculation logic and formulas
   - Test validation rules
   - Test service layer methods

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

3. **End-to-End Tests:**
   - Test complete workflows
   - Test error scenarios
   - Test performance under load

### 15.4 Feature Enhancements

1. **Payment Frequency Options:**
   - Support for bi-weekly payments
   - Support for weekly payments
   - Adjust amortization schedule accordingly

2. **Extra Payment Scenarios:**
   - Calculate impact of extra payments
   - Early payoff calculations
   - Payment schedule adjustments

3. **Comparison Tool:**
   - Compare multiple loan scenarios side-by-side
   - Visualize differences in total interest and payoff time
   - Save comparison results

4. **Multi-Currency Support:**
   - Support for multiple currencies
   - Currency conversion
   - Locale-specific formatting

5. **Internationalization:**
   - Multi-language interface support
   - Localized date and number formats
   - Translation management

6. **Sharing Capabilities:**
   - Generate unique URLs for calculations
   - Share results via link
   - Embed calculator in other websites

7. **Mobile Applications:**
   - Native iOS app
   - Native Android app
   - Offline calculation support

8. **Financial Tool Integration:**
   - Export to financial planning software
   - API for third-party integrations
   - Webhook notifications

9. **Advanced Visualizations:**
   - Payment breakdown over time graphs
   - Principal vs. interest charts
   - Interactive amortization schedule visualization

10. **Schedule Enhancements:**
    - Yearly subtotals in amortization schedule
    - Jump to specific year functionality
    - Highlight milestones (halfway point, principal exceeds interest)
    - Collapsible year groups

11. **User Accounts (Optional):**
    - Save calculation history
    - Named calculations
    - Calculation templates
    - Export history to CSV/PDF

### 15.5 Performance and Monitoring

1. **Caching:**
   - Cache frequently accessed calculations
   - Redis for session storage
   - CDN for static assets

2. **Monitoring:**
   - Application performance monitoring (APM)
   - Error tracking and alerting
   - Usage analytics and reporting

3. **Optimization:**
   - Database query optimization
   - API response compression
   - Lazy loading for large datasets