PayInsight — Backend API

A production-ready REST API for employee management and salary calculations. No UI; API only.

---

Project overview

• Employee CRUD — Create, read, update, delete employees (full name, job title, country, salary).
• Salary calculation — Compute deductions (TDS by country) and net salary from an employee’s gross salary.
• Salary metrics — Min/max/average salary by country; average salary by job title.

Stack: Python 3.11+, FastAPI, SQLAlchemy 2.0 (async + aiosqlite), SQLite3, pytest. All I/O is async; errors are handled with appropriate HTTP status codes and validation.

---

Quick start

Local (no Docker)

python -m venv .venv
source .venv/bin/activate   # Windows: .venv\Scripts\activate
pip install -r requirements.txt
# Set env if needed (default: SQLite file in project)
uvicorn app.main:app --reload

• API: http://localhost:8000
• Interactive API docs: http://localhost:8000/docs
• OpenAPI JSON: http://localhost:8000/openapi.json

Docker

docker-compose up --build

Same URLs as above (port 8000).

Tests

pytest
# Or in Docker:
docker-compose run app pytest

Verify setup (3 commands)

pip install -r requirements.txt && pytest -q && uvicorn app.main:app --host 127.0.0.1 --port 8000

Then open http://localhost:8000/docs or run: curl http://localhost:8000/health → {"status":"ok"}.

---

Environment variables

Variable	Description	Default
DATABASE_URL	SQLite URL: sqlite+aiosqlite:///./payinsight.db (or absolute path)	sqlite+aiosqlite:///./payinsight.db

---

API documentation

Base URL

• Local: http://localhost:8000
• All request/response bodies are JSON unless noted.

1. Health

• GET /health
  • Response: 200 — { "status": "ok" } (or similar)

2. Employee CRUD (base path: /api/employees)

• POST /api/employees

  • Body: { "full_name": string, "job_title": string, "country": string, "salary": number }
  • Response: 201 — created employee (with id, timestamps if applicable).

• GET /api/employees

  • Response: 200 — list of employees.

• GET /api/employees/{id}

  • Response: 200 — single employee; 404 if not found.

• PUT /api/employees/{id}

  • Body: same as create (all fields optional for partial update).
  • Response: 200 — updated employee; 404 if not found.

• DELETE /api/employees/{id}

  • Response: 204 no content; 404 if not found.

Validation errors: 422 with detail array.

3. Salary calculation

• GET /api/salary/calculation/{employee_id}
  • Uses the employee’s stored salary as gross.
  • Deduction rules:
    • India: TDS 10% of gross → net = gross − TDS
    • United States: TDS 12% of gross → net = gross − TDS
    • All other countries: No deductions → net = gross
  • Response: 200 — e.g. { "gross": number, "deduction": number, "net": number }
  • 404 if employee not found.

4. Salary metrics

• GET /api/salary/metrics/by-country?country=India

  • Response: 200 — { "min": number, "max": number, "average": number }
  • 404 if no employees in that country.

• GET /api/salary/metrics/by-job-title?job_title=Engineer

  • Response: 200 — { "average": number }
  • 404 if no employees with that job title.

---

Docker

• Dockerfile: Multi-stage (optional): build deps → runtime with uvicorn app.main:app.
• docker-compose.yml: Service app with port 8000, env for DATABASE_URL, volume for SQLite file if persistent DB desired.

Run:

docker-compose up --build

Tests:

docker-compose run app pytest

---

Folder structure

See STRUCTURE.md for the full tree. Summary:

app/
├── main.py         # FastAPI app, lifespan, routes
├── config.py       # Settings (DATABASE_URL)
├── database.py     # SQLAlchemy async engine, session, CRUD
├── exceptions.py   # Global 500 handler
├── models/         # SQLAlchemy ORM (Employee)
├── schemas/        # Pydantic (EmployeeCreate, EmployeeResponse, ...)
├── routes/         # employees, salary
└── services/       # salary (deduction rules)
tests/
├── conftest.py     # client, clean_db fixtures
├── test_health.py
├── test_db_employees.py
├── test_api_employees.py
├── test_salary_calculation.py
└── test_salary_metrics.py

---

Implementation details (AI usage)

Be intentional and transparent: note where and how you used AI (tools, prompts, rationale).

• Scaffolding: Project structure, AGENTS.md, TDD_TODO.md, STRUCTURE.md, and README skeleton were generated with Cursor from the Backend Problem Statement (TDD, production-ready, async, SQLite).
• Code generation: FastAPI routes, Pydantic schemas, SQLAlchemy async layer (models, database.py, CRUD), and salary deduction service were drafted with AI; refined for async session handling and test isolation.
• Tests: pytest test cases (DB layer, API CRUD, salary calculation, salary metrics) were generated from AGENTS.md; test DB isolation (file-based test DB, unique country/job_title in metrics tests) and init_db in get_session were added so all tests pass reliably.
• Documentation: README (quick start, env vars, API paths, folder structure) and this Implementation Details section were drafted with AI and updated to match the final API and SQLAlchemy stack.
• Trade-offs: Used SQLAlchemy 2.0 async + aiosqlite (as requested) for full async; file-based test DB and idempotent init_db() in get_session for test stability; global exception handler for 500 safety.

---

License

[Your choice — e.g. MIT, or as required by the hiring exercise.]