Job Application Tracker API

A self-hosted REST API for tracking job applications, companies, interview events, status history, and follow-up reminders. Built as a portfolio project — small enough to finish, large enough to demonstrate real backend skills.

This is the Core Portfolio Version: local-only, single user, no deployment, no auth. The full design rationale lives in docs/spec.md.

Stack

• FastAPI + Pydantic v2 for the HTTP layer
• SQLAlchemy 2.0 (typed ORM) + Alembic migrations
• PostgreSQL 16 via Docker Compose
• pytest + httpx for tests, with per-test transaction rollback
• Uvicorn as the ASGI server

Architecture

Three thin layers, kept thin:

HTTP route  →  service  →  repository  →  Postgres
              (business    (SQLAlchemy
               logic)       queries)

• Routes parse and validate input via Pydantic, call services, serialize responses. No business logic, no SQL.
• Services own business rules (status-history side effects, reminder completion, delete behavior).
• Repositories are thin SQLAlchemy query modules.

Quick start

Requires Python 3.11+ and Docker. From a fresh clone:

python -m venv .venv && source .venv/bin/activate
pip install -e ".[dev]"
cp .env.example .env

docker compose up -d          # starts Postgres on localhost:5433
alembic upgrade head          # runs migrations on the main database
pytest                        # runs the test suite (creates schema in the test database)
uvicorn app.main:app --reload # starts the API on http://localhost:8000

OpenAPI docs are auto-generated at http://localhost:8000/docs.

Environment variables

Variable	Purpose	Example
DATABASE_URL	Main Postgres URL used by the API and by Alembic	postgresql+psycopg://job_tracker:job_tracker@localhost:5433/job_tracker
TEST_DATABASE_URL	Database used by the test suite (defaults to <main>_test if unset)	postgresql+psycopg://job_tracker:job_tracker@localhost:5433/job_tracker_test

.env.example ships with safe local-development values. Never commit a real .env — it's already in .gitignore.

The Docker Compose file maps Postgres to port 5433 to avoid conflicts with a host-installed Postgres on port 5432. Update DATABASE_URL if you change this.

Project layout

job-tracker-api/
├── app/
│   ├── main.py                # FastAPI app factory + router registration
│   ├── config.py              # Pydantic Settings
│   ├── db.py                  # SQLAlchemy engine, session, declarative Base
│   ├── enums.py               # ApplicationStatus, ApplicationSource, EventKind
│   ├── errors.py              # Error envelope helpers + exception handlers
│   ├── models/                # SQLAlchemy 2.0 typed ORM
│   ├── schemas/               # Pydantic request/response schemas (extra=forbid)
│   ├── repositories/          # SQLAlchemy queries
│   ├── services/              # Business logic
│   └── routes/                # HTTP endpoints under /v1
├── alembic/
│   └── versions/              # 0001..0004 — companies, applications/status_history, events, reminders
├── tests/
│   ├── conftest.py            # session-scoped engine + per-test savepoint rollback
│   ├── test_companies.py
│   ├── test_applications.py   # also covers status-history transitions
│   ├── test_events.py
│   ├── test_reminders.py
│   └── test_health.py
├── scripts/init-test-db.sh    # ensures the test database exists on first compose up
├── docker-compose.yml
├── pyproject.toml
├── .env.example
├── docs/spec.md               # full design document
└── README.md

API overview

All endpoints are versioned under /v1. Creates return 201 Created with a Location header. Updates use PATCH, never PUT. List endpoints use limit/offset (default limit=50, max 100).

Companies — /v1/companies

GET, POST, GET /{id}, PATCH /{id}, DELETE /{id}.

Deleting a company that still has applications returns 409 company_has_applications.

Applications — /v1/applications

GET, POST, GET /{id}, PATCH /{id}, DELETE /{id}, plus GET /{id}/status-history.

• POST rejects a status field — new applications always start as applied.
• applied_at must not be in the future.
• salary_range is { min?, max?, currency } with min ≤ max and a 3-letter uppercase currency.
• PATCH writes a status_history row only when the status actually changes. Non-status updates and no-op status patches do not write history and do not bump updated_at.
• Terminal statuses (accepted, rejected, withdrawn, ghosted) cannot be changed — 409 invalid_status_transition.
• DELETE cascades to events, reminders, and status history.

Events — /v1/applications/{aid}/events and /v1/events/{id}

POST/GET under the application; GET/PATCH/DELETE flat.

completed_at cannot be earlier than scheduled_at and cannot be in the future (400 invalid_event_completion). Setting completed_at to null clears it.

Reminders — /v1/reminders and /v1/applications/{aid}/reminders

POST under the application (the body must not include application_id or completed_at). GET /v1/reminders?due=actionable returns incomplete reminders with due_at <= now(). PATCH /v1/reminders/{id} accepts:

• message, due_at — partial edits
• { "completed": true } — server-stamps completed_at; idempotent
• { "completed": false } — clears completed_at; idempotent

Creating an application does not create a reminder automatically.

Errors

All error responses use the same envelope:

{ "error": "company_not_found", "message": "...", "status": 404 }

Validation errors (422) include a errors: [{ field, message }] array.

Tests

pytest                       # full suite
pytest tests/test_applications.py -v

The suite uses per-test transaction rollback with SQLAlchemy's join_transaction_mode="create_savepoint". Each test runs against a real Postgres session bound to a connection whose outer transaction is rolled back after the test, so commits inside services never persist between tests.

The schema is created via Base.metadata.create_all once per session against the test database. Alembic migrations are exercised manually with alembic upgrade head against the main database — both must pass for a release.

Auth

This is a local-only single-user project; there is no authentication. If you deploy it, add X-API-Key middleware that protects everything except /health, and either protect /docs behind the same check or disable it.

Migrations

alembic upgrade head                          # apply pending migrations
alembic downgrade -1                          # roll back one
alembic revision --autogenerate -m "message"  # draft a new migration (review before committing)

Existing migrations: 0001 companies → 0002 applications & status_history → 0003 events → 0004 reminders.

Always review autogenerated migrations before committing — Alembic doesn't always produce what you mean (e.g. for renames or for column-type changes).

Design choices worth talking about

• Separate status_history table instead of just a status column — preserves the funnel and time-in-stage data that would be lost otherwise. Initial row is inserted in the same transaction as application creation.
• Restrictive delete on companies with applications — surfaces the cleanup decision instead of silently cascading.
• Cascade delete on applications to their events, reminders, and history — those rows have no useful meaning without the parent application.
• PATCH not PUT because every update is partial. No-op patches return 200 with the unchanged resource.
• Offset pagination is fine for the single-user portfolio scope. Cursor pagination is in docs/spec.md as a v1 follow-up.
• In-process tests against a real Postgres with savepoint rollback — faster than recreating the schema per test, and closer to production behavior than ORM mocks.

Deployment

This project is intentionally local-only. To deploy it you would:

1. Add X-API-Key middleware (or single-user JWT).
2. Disable or protect /docs.
3. Move DATABASE_URL and the API key to platform secrets.
4. Pick one of Fly.io / Railway / Render and ship a single API container; the Postgres can be a managed instance from the same provider.

None of that is in scope here — see docs/spec.md for the v1 enhancements list.

License

MIT