Build an Internship Tracker API

Build a tested Python API that stores internship applications in SQLite.

Introduction

30 Second Summary

Internship searches generate a trail of company names, roles, and changing outcomes. A scattered list makes it difficult to see where each application stands.

In this project, you will build a local internship application tracker as a REST API using Python, FastAPI, and SQLite. Passing automated API tests plus a public GitHub repository turn the working tracker into a portfolio project.

What You'll Build

Picture your browser returning a saved internship application as JSON with a generated ID under a 201 Created response.

By the end of this project, you'll have:

  • A working application tracker where you can submit a company, role, and status through interactive API documentation before retrieving every saved application.
  • Reliable local persistence that keeps records after a server restart while rejecting unsupported statuses with a 422 validation response.
  • A recruiter-ready public repository with passing tests, clear setup instructions, and a documented reason for choosing SQLite.
  • Secret Mission: Add a validated status filter that returns only applications at one selected stage.

Are there any prerequisites?

You need a Windows laptop with Python, Git, a GitHub account, and Visual Studio Code already available. Basic confidence creating files and pasting code is enough because no previous API or database experience is required.

Before We Start

Before any hands-on work begins, commit to building a small internship application tracker API that you can finish and share. It will demonstrate your Python, API, database, testing, documentation, and GitHub skills through one complete portfolio project.

Set Up Your Windows Python Project

Your tracker will soon become a FastAPI service. A reproducible project environment keeps its package versions consistent for every developer.

In this step, you'll prepare that environment in Visual Studio Code. A virtual environment will isolate the pinned packages from your other Python projects.

In this step, get ready to:
  • Prepare a Visual Studio Code workspace with supported Python and Git.
  • Add the pinned dependencies plus local-file exclusions.
  • Create an activated virtual environment with the dependencies installed.
Create the project workspace

A workspace gives the editor one named project folder. Its terminal starts from that same location.

  • Press the Windows key to open the search bar.
  • Type Visual Studio Code into the search bar.
  • Press Enter to open Visual Studio Code.

You'll see the Visual Studio Code welcome screen. This is where you'll keep the project files and terminal together.

  • Select File from the top menu.
  • Select Open Folder....
  • Select Desktop in the folder dialog.
  • Select New Folder.
  • Name the folder internship-application-tracker-api.
  • Select Select Folder.
  • Select Yes, I trust the authors if the Workspace Trust dialog appears.

Your project now has a clear home. You'll see internship-application-tracker-api at the top of the Explorer sidebar.

  • Select View from the top menu.
  • Select Terminal.
  • Select the Launch Profile dropdown in the terminal panel.
  • Select Command Prompt.

The Command Prompt terminal starts inside your internship-application-tracker-api folder. Every setup command now applies to the correct project.

The pinned packages require Python 3.10 or newer. Checking now prevents package installation failures later.

  • Check your installed Python version by running this command:
py --version

What does this check do?

The py command uses the Windows Python launcher. Its output identifies the Python version available to this project.

Match the result in your terminal to the scenario below.

✔️ I see the required Python version

Your Python version meets the project requirement. Continue to the Git check below.

ⓧ I see an older Python version

Your current Python cannot run every pinned package in this project. Install Python 3.10 or newer before continuing.

  • Open the official Python Releases for Windows page.
  • Download a stable Windows installer that provides Python 3.10 or newer.
  • Run the downloaded installer.
  • Complete the installer prompts.
  • Return to Visual Studio Code.
  • Select the Launch Profile dropdown in the terminal panel.
  • Select Command Prompt.
  • Check the updated Python version by running this command:
py --version

What confirms the upgrade?

The new Command Prompt reads the updated Python installation. You can continue once the output reports Python 3.10 or newer.

Still seeing the older version?

Close every open Command Prompt terminal in Visual Studio Code. Open a fresh Command Prompt so it can read the updated installation.

If the older version remains active, help me identify which Python installation the Windows launcher is using.

ⓧ Python is not found

The Windows Python launcher is unavailable. Install a supported Python release before continuing.

  • Open the official Python Releases for Windows page.
  • Download a stable Windows installer that provides Python 3.10 or newer.
  • Run the downloaded installer.
  • Complete the installer prompts.
  • Return to Visual Studio Code.
  • Select the Launch Profile dropdown in the terminal panel.
  • Select Command Prompt.
  • Check the installed Python version by running this command:
py --version

What confirms the installation?

A Python version in the output confirms that the Windows launcher is available. Continue when the version is 3.10 or newer.

Python still unavailable?

Close every open Command Prompt terminal after the installation. Open a fresh Command Prompt before trying the version check again.

If Python remains unavailable, help me troubleshoot the Windows Python launcher.

Git will track each project change before you publish the finished repository. This check confirms its command-line tool is ready.

  • Check your installed Git version by running this command:
git --version

What does this check do?

The command asks Git to report its installed version. A version number confirms that Command Prompt can access Git.

You'll see a Git version number in the terminal. Your two local tools are ready for the project.

Git version missing?

Close the current terminal after confirming Git is installed on Windows. Open a fresh Command Prompt from the Launch Profile dropdown before retrying the check.

If the command still fails, help me make Git available in my Visual Studio Code Command Prompt terminal.

Add the setup files

Two configuration files make your setup reproducible. One pins the project packages while the other keeps local machine files out of version control.

  • Select the new-file icon beside internship-application-tracker-api in the Explorer sidebar.
  • Name the file requirements.txt.
  • Paste this dependency list into requirements.txt:
fastapi[standard]==0.162.0
pydantic==2.14.2
pytest==9.2.0
httpx==0.28.1

What does this file control?

  • FastAPI supplies the API framework plus its standard development tools.
  • Pydantic validates the data sent to the API.
  • pytest runs the automated checks you'll add later.
  • HTTPX supports requests made through the API test client.
  • Save requirements.txt.
  • Confirm the file remains listed in the Explorer sidebar.

You'll see requirements.txt beside your project folder contents. Its four pinned package lines are now saved.

Dependency file looks wrong?

Check that the filename ends once with .txt. A file named requirements.txt.txt will not match the installation command.

If the package list differs from the reference, help me compare my requirements file with the expected four pinned packages.

  • Select the new-file icon beside internship-application-tracker-api in the Explorer sidebar.
  • Name the file .gitignore.
  • Paste these local-file exclusions into .gitignore:
.venv/
__pycache__/
.pytest_cache/
applications.db

What does this file exclude?

  • The .venv/ entry excludes the local virtual environment.
  • The __pycache__/ entry excludes files Python generates while running code.
  • The .pytest_cache/ entry excludes the local pytest cache.
  • The applications.db entry excludes the local SQLite database.
  • Save .gitignore.
  • Confirm the file remains listed in the Explorer sidebar.

You'll see .gitignore beside requirements.txt. Your dependency rules and local-file exclusions are both saved.

Ignore file missing?

Check that the filename is exactly .gitignore. Remove any extra .txt extension from the name.

If Visual Studio Code does not show the file, help me create a .gitignore file without an extra extension on Windows.

✔️ Awesome, I've got everything!

Both setup files are saved. Their contents now match the project configuration.

ⓧ I'd like to double check the full code

fastapi[standard]==0.162.0
pydantic==2.14.2
pytest==9.2.0
httpx==0.28.1

What are you checking?

This is the complete requirements.txt file for this step. Compare every package name plus its pinned version.

.venv/
__pycache__/
.pytest_cache/
applications.db

What are you checking here?

This is the complete .gitignore file for this step. Compare all four local-file exclusions.

Build and activate the virtual environment

Different Python projects can require different package versions. Your local environment keeps this tracker's dependencies separate from everything else on the laptop.

  • Create the local .venv environment by running this command:
py -m venv .venv

What does this command do?

Python creates an isolated environment inside the .venv folder. The folder contains its own Python executable plus a dedicated package location.

  • Confirm the environment folder exists in the Explorer sidebar.

You'll see a new .venv folder inside internship-application-tracker-api.

Environment folder missing?

Check that the terminal path points to internship-application-tracker-api. The command creates .venv inside the terminal's current folder.

If the command fails, help me troubleshoot creating a Windows Python virtual environment.

Activation changes which Python installation receives the next package command. This keeps every dependency inside the project environment.

  • Activate the virtual environment by running this command:
.venv\Scripts\activate

What does activation change?

Command Prompt now prioritizes the Python executable inside .venv. Package installations stay isolated within this project.

Your Command Prompt now displays the environment name before the project folder path. That prefix confirms the environment is active.

Environment name not showing?

Confirm that the terminal profile is Command Prompt. The activation command in this step is specific to that Windows shell.

If activation still fails, help me activate my .venv environment in Windows Command Prompt.

  • Install the pinned project dependencies by running this command:
py -m pip install -r requirements.txt

What does this command install?

The command reads every pinned package from requirements.txt. It installs those exact releases inside the active virtual environment.

You'll see package download plus installation output in the terminal. The prompt returns when the dependencies are ready.

Dependency installation failed?

Confirm that the terminal shows the active environment name. Check that requirements.txt is saved inside internship-application-tracker-api.

If installation still fails, help me diagnose my pinned Python package installation.

Before the final check, which Python path do you expect Command Prompt to list first?

  • Verify the active Python executable by running this command:
where python

What does this final check prove?

The command shows the Python executables available to Command Prompt. The first path identifies which one runs when this terminal uses Python.

The first path ends with .venv\Scripts\python. This confirms that your isolated project environment is active.

Project Python is not first?

Run the activation command again from the internship-application-tracker-api folder. Repeat the final check in that same Command Prompt terminal.

If another Python path remains first, help me make my .venv Python executable the active one.

That's the setup locked in. Your project now has pinned packages inside an active local environment.

Your Windows Python workspace is reproducible and ready. Next, you'll turn it into a working internship application tracker API.

Build an In-Memory Tracker

Your Windows project environment is ready for application code. The next milestone is a working REST API built with FastAPI.

This first version uses in-memory storage to keep feedback immediate. You will test the storage boundary by restarting the server after creating a record.

In this step, get ready to:
  • Build the in-memory API routes.
  • Create an application through interactive documentation.
  • Test the stored record across a server restart.
Start the FastAPI application

The app object is the entry point for your API. FastAPI uses its title when it generates the interactive documentation.

  • In the VS Code Explorer sidebar, click New File.
  • Name the file main.py.
  • Create the FastAPI application by adding this code:
from fastapi import FastAPI, status as http_status
from pydantic import BaseModel

app = FastAPI(title="Internship Application Tracker API")

What does this code do?

  • The FastAPI import provides the application class.
  • The http_status alias provides named HTTP status constants.
  • The BaseModel import provides the foundation for structured request data.
  • The app object gives your API its documentation title.
  • Save main.py.
  • Start the development server from the activated Command Prompt terminal by running this command:
fastapi dev

What does this command do?

The fastapi dev command detects the app object in main.py. It starts a development server that reloads after saved code changes.

The server keeps this Command Prompt terminal occupied while it runs. Keep that terminal running during the next substep.

  • Open a new browser tab.
  • Enter http://127.0.0.1:8000/docs in the address bar.

You should see interactive documentation titled Internship Application Tracker API.

You now have a live API serving its own interactive documentation.

Docs page not loading?

  • Check that the Command Prompt terminal still shows the running server.
  • Confirm that main.py is saved inside the internship-application-tracker-api folder.
  • Confirm that the browser address is http://127.0.0.1:8000/docs.

Help me diagnose why my FastAPI documentation page is not loading.

Add application models and routes

Pydantic request models describe the fields your API accepts. A response model adds the generated ID returned to the client.

  • In main.py, place the following code below app = FastAPI(title="Internship Application Tracker API"):
class ApplicationCreate(BaseModel):
    company: str
    role: str
    status: str = "Applied"


class Application(ApplicationCreate):
    id: int


applications: list[Application] = []


@app.get("/applications", response_model=list[Application])
def list_applications() -> list[Application]:
    return applications

How does listing work?

  • The ApplicationCreate model defines the submitted company.
  • The same model defines the submitted role.
  • The status field defaults to Applied.
  • The Application model adds a numeric id.
  • The applications list collects the records created by the API.
  • The list_applications() route returns every record in that list.
  • Save main.py.
  • Return to the interactive documentation from earlier.
  • Refresh the browser page.

You should see a new GET /applications operation.

GET operation missing?

  • Check the Command Prompt terminal for a Python syntax error.
  • Confirm that the route code appears below the applications list.
  • Refresh the documentation after the server reloads.

Help me find why my GET operation is missing from the FastAPI docs.

The GET /applications route reads the current records. The POST /applications route creates the records that it returns.

  • In main.py, place the following route below list_applications():
@app.post(
    "/applications",
    response_model=Application,
    status_code=http_status.HTTP_201_CREATED,
)
def create_application(application: ApplicationCreate) -> Application:
    created_application = Application(
        id=len(applications) + 1,
        **application.model_dump(),
    )
    applications.append(created_application)
    return created_application

How does creation work?

  • The route accepts request data shaped like ApplicationCreate.
  • The response_model setting documents the returned Application shape.
  • The named status constant returns HTTP 201 after creation.
  • The list length generates the next numeric ID.
  • The model_dump() method converts the submitted model into dictionary data.
  • The append() call stores the new record in the list.
  • Save main.py.
  • Refresh the interactive documentation.

You should now see both GET /applications and POST /applications.

POST operation missing?

  • Confirm that the @app.post decorator starts at the left edge of main.py.
  • Check that create_application() appears below the decorator.
  • Inspect the Command Prompt terminal for the line number of any syntax error.

Help me debug why POST /applications is missing from my FastAPI docs.

✔️ Awesome, I've got everything!

Your main.py file now matches the complete in-memory tracker.

ⓧ I'd like to double check the full code

from fastapi import FastAPI, status as http_status
from pydantic import BaseModel

app = FastAPI(title="Internship Application Tracker API")


class ApplicationCreate(BaseModel):
    company: str
    role: str
    status: str = "Applied"


class Application(ApplicationCreate):
    id: int


applications: list[Application] = []


@app.get("/applications", response_model=list[Application])
def list_applications() -> list[Application]:
    return applications


@app.post(
    "/applications",
    response_model=Application,
    status_code=http_status.HTTP_201_CREATED,
)
def create_application(application: ApplicationCreate) -> Application:
    created_application = Application(
        id=len(applications) + 1,
        **application.model_dump(),
    )
    applications.append(created_application)
    return created_application

The interactive documentation can now act as a browser-based client. You can use it to send JSON request data without building a separate interface.

  • Return to the interactive documentation from earlier.
  • Refresh the browser page.
  • Expand the POST /applications operation.
  • Click Try it out.
  • Replace the company value with Example Co.
  • Replace the role value with Software Engineer Intern.
  • Replace the status value with Applied.
  • Send the request using the button below the request body.

You should see a 201 response. The returned JSON should contain an id of 1.

  • Expand the GET /applications operation.
  • Click Try it out.
  • Send the request using the control for that operation.

You should see a JSON list containing the Example Co application with ID 1.

Your tracker now creates a structured application record. It can retrieve that record through a separate route.

Record missing from the list?

  • Confirm that the creation request returned status 201.
  • Check that applications.append(created_application) appears inside create_application().
  • Send the GET request from the same running server session.

Help me understand why my created application is missing from GET /applications.

Test a server restart

The API has proved that creation works during one server session. A restart now tests whether the record belongs to the running process or the wider project.

  • Switch back to the Command Prompt terminal from earlier.
  • Press Ctrl+C to stop the server.

The Command Prompt returns to an active command line.

  • Start the development server again by running this command:
fastapi dev

What does restarting change?

This command starts a new server process from the saved main.py file. The routes are available again through the same documentation URL.

Server not restarting?

  • Confirm that the virtual environment name still appears in the Command Prompt.
  • Check that the terminal is still inside the internship-application-tracker-api folder.
  • Inspect the terminal output for the file and line connected to the failure.

Help me troubleshoot why fastapi dev no longer starts after restarting the server.

  • Return to http://127.0.0.1:8000/docs.
  • Refresh the browser page.

Before you run GET /applications, do you expect the Example Co record to remain?

  • Expand the GET /applications operation.
  • Click Try it out.
  • Send the request using the control for that operation.

You will see an empty JSON list, []. The application record has disappeared after the restart.

Why did the record disappear?

  • Python creates applications as an empty list when main.py loads.
  • The POST request added the record only to that list.
  • Restarting creates a fresh Python process.
  • The fresh process creates a new empty list.

That experiment paid off. You now have direct evidence of the storage limit.

Next, you will move the records into SQLite so the same restart test preserves them.

Persist Applications with SQLite

Your first FastAPI tracker worked until the process restarted. The missing record proved that the applications list lived only in temporary memory.

This step moves each application into SQLite. A database file gives the tracker durable local storage without requiring a separate database server.

In this step, get ready to:
  • Create an SQLite database with an applications table.
  • Store records with parameterized SQL.
  • Restart the API to prove that saved records persist.
Connect the API to SQLite

The API needs a consistent path to its database file. It also needs a reusable connection that returns rows with their column names.

  • In main.py, replace the import section at the top with this database setup:
from contextlib import closing
from pathlib import Path
import sqlite3

from fastapi import FastAPI, status as http_status
from pydantic import BaseModel

DB_PATH = Path("applications.db")

What does this setup do?

  • The closing helper closes each database connection after the surrounding block finishes.
  • Path represents the location of applications.db.
  • sqlite3 gives Python access to SQLite.
  • DB_PATH keeps the database location in one reusable value.
  • Save main.py.
  • Return to the Command Prompt terminal where FastAPI is running.

You'll see FastAPI reload the app. The new database imports load without stopping the server.

Does the reload stop?

Check that all three new imports sit above the FastAPI import. Confirm that DB_PATH appears before the app definition.

Help me fix the database imports in main.py.

A connection is the API's active link to the database file. The row factory preserves each result's column names.

  • In main.py, add this function below the Application class:
def get_connection() -> sqlite3.Connection:
    connection = sqlite3.connect(DB_PATH)
    connection.row_factory = sqlite3.Row
    return connection

What does this function do?

  • sqlite3.connect(DB_PATH) opens applications.db. SQLite creates the file when it does not exist.
  • sqlite3.Row keeps each result connected to its column names.
  • get_connection() gives every database operation the same configuration.
  • Save main.py.
  • Return to the running terminal to confirm FastAPI reloads successfully.

The app reloads with the connection helper ready. Its routes still start successfully.

Does the connection helper fail?

Check that DB_PATH is defined above get_connection(). Confirm that sqlite3.Row uses a capital R.

Help me debug get_connection().

The database file also needs a table that defines each stored field. Running the table setup when the app starts makes the required structure available to every route.

  • In main.py, add this table setup directly below get_connection():
def create_table() -> None:
    with closing(get_connection()) as connection:
        connection.execute(
            """
            CREATE TABLE IF NOT EXISTS applications (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                company TEXT NOT NULL,
                role TEXT NOT NULL,
                status TEXT NOT NULL
            )
            """
        )
        connection.commit()


create_table()

What does this table setup do?

  • CREATE TABLE IF NOT EXISTS creates the table only when it is missing.
  • id uses AUTOINCREMENT to generate a new number for each application.
  • company, role, plus status must contain text.
  • commit() saves the table creation to the database file.
  • create_table() runs whenever the API process starts.
  • Save main.py.
  • Check the file list on the left side of Visual Studio Code.

You'll see applications.db beside main.py. That file is now the durable storage layer for your tracker.

Can't see applications.db?

Confirm that create_table() appears below the function definition. Check the running terminal for a Python error mentioning the table setup.

Help me create applications.db.

Save applications with SQL

A parameterized query sends application values separately from the SQL statement. The database assigns the new row's ID after the insert succeeds.

  • In main.py, replace the existing create_application() route with this database-backed version:
@app.post(
    "/applications",
    response_model=Application,
    status_code=http_status.HTTP_201_CREATED,
)
def create_application(application: ApplicationCreate) -> Application:
    with closing(get_connection()) as connection:
        cursor = connection.execute(
            "INSERT INTO applications (company, role, status) VALUES (?, ?, ?)",
            (application.company, application.role, application.status),
        )
        connection.commit()
        application_id = cursor.lastrowid

    if application_id is None:
        raise RuntimeError("Database did not return an application ID.")

    return Application(id=application_id, **application.model_dump())

How does the insert work?

  • The three ? placeholders keep submitted values separate from the SQL statement.
  • connection.commit() writes the new record to applications.db.
  • cursor.lastrowid retrieves the ID generated by SQLite.
  • Application returns the submitted fields with the generated ID.
  • Save main.py.
  • Refresh http://127.0.0.1:8000/docs in your browser.
  • Expand POST /applications.
  • Click Try it out.
  • Replace the request body with this application:
{
            "company": "Example Co",
            "role": "Software Engineer Intern",
            "status": "Applied"
        }

What does this request test?

This request sends all three fields expected by ApplicationCreate. The API should store them as one row in applications.db.

  • Submit the request using the control below the request body.

You'll see a 201 Created response. The returned JSON includes a generated numeric id.

Does the request fail?

Check that the insert contains three ? placeholders. Confirm that the values tuple follows the same company, role, status order.

Help me debug the application insert.

Read records after a restart

The list route must read from the same table used by the create route. Converting each row into an Application keeps the API response matched to the existing response model.

  • In main.py, replace the in-memory applications list plus the old list_applications() route with this database-backed route:
@app.get("/applications", response_model=list[Application])
def list_applications() -> list[Application]:
    with closing(get_connection()) as connection:
        rows = connection.execute(
            "SELECT id, company, role, status FROM applications ORDER BY id"
        ).fetchall()

    return [Application(**dict(row)) for row in rows]

How does the list query work?

  • SELECT retrieves every field required by Application.
  • ORDER BY id keeps records in creation order.
  • fetchall() collects all matching rows.
  • dict(row) converts each named SQLite row into values the response model can read.
  • Save main.py.
  • Refresh the interactive docs.
  • Expand GET /applications.
  • Click Try it out.
  • Submit the request using the request control.

You'll see the saved Example Co application with the same generated ID returned by the create request.

Is the list empty?

Confirm that the create request returned 201 Created before testing the list route. Check that both routes use get_connection().

Help me debug the application list.

Your updated file should now match this complete database-backed version.

✔️ Awesome, I've got everything!

Great. Double-check that you saved main.py before testing the restart.

ⓧ I'd like to double check the full code

from contextlib import closing
from pathlib import Path
import sqlite3

from fastapi import FastAPI, status as http_status
from pydantic import BaseModel

DB_PATH = Path("applications.db")

app = FastAPI(title="Internship Application Tracker API")


class ApplicationCreate(BaseModel):
    company: str
    role: str
    status: str = "Applied"


class Application(ApplicationCreate):
    id: int


def get_connection() -> sqlite3.Connection:
    connection = sqlite3.connect(DB_PATH)
    connection.row_factory = sqlite3.Row
    return connection


def create_table() -> None:
    with closing(get_connection()) as connection:
        connection.execute(
            """
            CREATE TABLE IF NOT EXISTS applications (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                company TEXT NOT NULL,
                role TEXT NOT NULL,
                status TEXT NOT NULL
            )
            """
        )
        connection.commit()


create_table()


@app.get("/applications", response_model=list[Application])
def list_applications() -> list[Application]:
    with closing(get_connection()) as connection:
        rows = connection.execute(
            "SELECT id, company, role, status FROM applications ORDER BY id"
        ).fetchall()

    return [Application(**dict(row)) for row in rows]


@app.post(
    "/applications",
    response_model=Application,
    status_code=http_status.HTTP_201_CREATED,
)
def create_application(application: ApplicationCreate) -> Application:
    with closing(get_connection()) as connection:
        cursor = connection.execute(
            "INSERT INTO applications (company, role, status) VALUES (?, ?, ?)",
            (application.company, application.role, application.status),
        )
        connection.commit()
        application_id = cursor.lastrowid

    if application_id is None:
        raise RuntimeError("Database did not return an application ID.")

    return Application(id=application_id, **application.model_dump())

Before you restart the API, do you think the saved application will keep the same ID?

  • Return to the running Command Prompt terminal.
  • Press Ctrl+C to stop the FastAPI process.
  • Start a fresh API process by running this command:
fastapi dev

What does this restart prove?

This command starts a new FastAPI process. The new process reads records from applications.db through DB_PATH.

The create_table() call safely keeps the existing table because its SQL includes IF NOT EXISTS.

  • Return to http://127.0.0.1:8000/docs after the server starts.
  • Refresh the browser page.
  • Expand GET /applications.
  • Click Try it out.
  • Submit the request using the request control.

You'll see the same saved application with the same ID. The record survived because SQLite stored it in applications.db.

Did the record disappear?

Confirm that DB_PATH still points to applications.db in the project folder. Check that the insert calls connection.commit() before closing its connection.

Help me restore SQLite persistence.

You have turned the temporary tracker into a persistent API. Next, you'll constrain application statuses and prove the behavior with automated tests.

Validate Data and Add Tests

Your SQLite-backed tracker now keeps applications through server restarts. The generated IDs survive too.

Persistence can also preserve unsupported values.

In this step, data validation will block those values. Automated API tests with pytest will prove that the behavior stays reliable.

In this step, get ready to:
  • Restrict application statuses to four supported values.
  • Test the successful create-to-list flow with an isolated database.
  • Confirm that an unsupported status receives an HTTP 422 response.
Constrain application statuses

FastAPI reads the type on each request field. A Literal type gives the status field a fixed set of accepted values.

  • In main.py, add the Literal import directly below import sqlite3 by using this line:
from typing import Literal

What does Literal provide?

Literal lets a type accept a specific set of values. Your request model will use it to define the supported application stages.

  • Save main.py.
  • Confirm that the editor shows no warning underline beneath the new import.
  • In main.py, replace the section from DB_PATH through the Application class with this updated section:
ApplicationStatus = Literal["Applied", "Interview", "Offer", "Rejected"]
DB_PATH = Path("applications.db")

app = FastAPI(title="Internship Application Tracker API")


class ApplicationCreate(BaseModel):
    company: str
    role: str
    status: ApplicationStatus = "Applied"


class Application(ApplicationCreate):
    id: int

How does the model validate status?

  • The ApplicationStatus alias accepts Applied, Interview, Offer, or Rejected.
  • The Applied default still handles requests that omit the status field.
  • The existing Application response model keeps the generated id in successful responses.
  • Save main.py.
  • Return to the interactive documentation from earlier.
  • Refresh the browser page.
  • Expand POST /applications.
  • Select Try it out.
  • Change the company value to Example Co.
  • Change the role value to Software Engineer Intern.
  • Change the status value to Maybe.

Before you submit the request, do you think Maybe will reach the database?

  • Submit the request from the interactive documentation.

You will see an HTTP 422 validation response. That guardrail now stops the unsupported status before SQLite can save it.

Still accepting Maybe?

  • Confirm that ApplicationCreate.status uses ApplicationStatus.
  • Check that Maybe is absent from the ApplicationStatus values.
  • Save main.py before refreshing the documentation.

Help me find why my FastAPI request model still accepts an unsupported status.

✔️ Awesome, I've got everything!

Your request model now blocks unsupported application statuses.

ⓧ I'd like to double check the full code

from contextlib import closing
from pathlib import Path
import sqlite3
from typing import Literal

from fastapi import FastAPI, status as http_status
from pydantic import BaseModel

ApplicationStatus = Literal["Applied", "Interview", "Offer", "Rejected"]
DB_PATH = Path("applications.db")

app = FastAPI(title="Internship Application Tracker API")


class ApplicationCreate(BaseModel):
    company: str
    role: str
    status: ApplicationStatus = "Applied"


class Application(ApplicationCreate):
    id: int


def get_connection() -> sqlite3.Connection:
    connection = sqlite3.connect(DB_PATH)
    connection.row_factory = sqlite3.Row
    return connection


def create_table() -> None:
    with closing(get_connection()) as connection:
        connection.execute(
            """
            CREATE TABLE IF NOT EXISTS applications (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                company TEXT NOT NULL,
                role TEXT NOT NULL,
                status TEXT NOT NULL
            )
            """
        )
        connection.commit()


create_table()


@app.get("/applications", response_model=list[Application])
def list_applications() -> list[Application]:
    with closing(get_connection()) as connection:
        rows = connection.execute(
            "SELECT id, company, role, status FROM applications ORDER BY id"
        ).fetchall()

    return [Application(**dict(row)) for row in rows]


@app.post(
    "/applications",
    response_model=Application,
    status_code=http_status.HTTP_201_CREATED,
)
def create_application(application: ApplicationCreate) -> Application:
    with closing(get_connection()) as connection:
        cursor = connection.execute(
            "INSERT INTO applications (company, role, status) VALUES (?, ?, ?)",
            (application.company, application.role, application.status),
        )
        connection.commit()
        application_id = cursor.lastrowid

    if application_id is None:
        raise RuntimeError("Database did not return an application ID.")

    return Application(id=application_id, **application.model_dump())

What should match?

This reference contains the completed validation model. The database operations remain unchanged from your persistent tracker.

Test creation and listing

A reliable test must avoid your personal applications.db file. The test helper redirects DB_PATH to a fresh temporary database.

  • In the Visual Studio Code Explorer sidebar, select New File inside internship-application-tracker-api.
  • Name the file test_main.py.
  • Add the test imports plus the temporary client helper by pasting this code:
from fastapi.testclient import TestClient

import main


def make_client(tmp_path, monkeypatch) -> TestClient:
    test_database = tmp_path / "applications.db"
    monkeypatch.setattr(main, "DB_PATH", test_database)
    main.create_table()
    return TestClient(main.app)

How does the test client stay isolated?

  • The tmp_path fixture supplies a unique temporary folder for the test.
  • The monkeypatch fixture temporarily points main.DB_PATH at that folder.
  • The create_table() call prepares the temporary database.
  • The TestClient sends requests directly to your app.
  • Save test_main.py.
  • Confirm that the editor shows no red warning underlines in the helper.

Seeing import warnings?

  • Confirm that the file is named test_main.py.
  • Check that main.py sits in the same folder.
  • Confirm that Visual Studio Code is using the interpreter from .venv.

Help me troubleshoot the imports and temporary database helper in test_main.py.

  • At the bottom of test_main.py, add the successful create-to-list test by pasting this function:
def test_create_and_list_application(tmp_path, monkeypatch):
    client = make_client(tmp_path, monkeypatch)

    create_response = client.post(
        "/applications",
        json={
            "company": "Example Co",
            "role": "Software Engineer Intern",
            "status": "Applied",
        },
    )

    assert create_response.status_code == 201
    created_application = create_response.json()
    assert created_application["company"] == "Example Co"
    assert created_application["id"] == 1

    list_response = client.get("/applications")

    assert list_response.status_code == 200
    assert list_response.json() == [created_application]

What does this test prove?

  • The first request creates an application in the temporary database.
  • The 201 assertion confirms that creation succeeds.
  • The ID assertion confirms that SQLite generated the first record ID.
  • The final assertion confirms that the list operation returns the created record.
  • Save test_main.py.
  • In the Command Prompt terminal running the development server, press CTRL+C.

Before you run the test suite, do you think the temporary database will behave like your local SQLite database?

  • Run the first automated API test by entering this command:
pytest

What should you see?

Pytest discovers one test. It reports that the create-to-list flow passed.

Your personal applications.db remains untouched because the test used its own temporary database.

First test not passing?

  • Check that the terminal prompt shows the activated .venv environment.
  • Confirm that test_main.py starts with the imports plus make_client().
  • Compare every request path with /applications.

Help me diagnose why my create-to-list pytest test is failing.

Test invalid status validation

The browser check showed that the request model rejects Maybe. A repeatable test protects that rule whenever the API changes.

  • At the bottom of test_main.py, add the invalid-status test by pasting this function:
def test_rejects_unknown_status(tmp_path, monkeypatch):
    client = make_client(tmp_path, monkeypatch)

    response = client.post(
        "/applications",
        json={
            "company": "Example Co",
            "role": "Software Engineer Intern",
            "status": "Maybe",
        },
    )

    assert response.status_code == 422

What does the validation test prove?

This test sends the same unsupported status every time. The 422 assertion proves that request validation blocks it.

The route function never receives the invalid request. The temporary database cannot store the rejected value.

  • Save test_main.py.

Before you run the full suite, do you expect both the successful request and the rejected request to pass their tests?

  • Run both automated API tests by entering this command:
pytest

What should you see?

You will see 2 passed in the test summary. Your valid request receives 201.

The request using Maybe receives 422. Your API now has an automated guardrail against unsupported statuses.

Not seeing two passing tests?

  • Confirm that both function names begin with test_.
  • Check that ApplicationCreate.status uses ApplicationStatus.
  • Compare the expected response codes with 201 plus 422.

Help me interpret my pytest output and fix the failing API test.

✔️ Awesome, I've got everything!

Your test suite now proves successful creation plus invalid-status rejection.

ⓧ I'd like to double check the full code

from fastapi.testclient import TestClient

import main


def make_client(tmp_path, monkeypatch) -> TestClient:
    test_database = tmp_path / "applications.db"
    monkeypatch.setattr(main, "DB_PATH", test_database)
    main.create_table()
    return TestClient(main.app)


def test_create_and_list_application(tmp_path, monkeypatch):
    client = make_client(tmp_path, monkeypatch)

    create_response = client.post(
        "/applications",
        json={
            "company": "Example Co",
            "role": "Software Engineer Intern",
            "status": "Applied",
        },
    )

    assert create_response.status_code == 201
    created_application = create_response.json()
    assert created_application["company"] == "Example Co"
    assert created_application["id"] == 1

    list_response = client.get("/applications")

    assert list_response.status_code == 200
    assert list_response.json() == [created_application]


def test_rejects_unknown_status(tmp_path, monkeypatch):
    client = make_client(tmp_path, monkeypatch)

    response = client.post(
        "/applications",
        json={
            "company": "Example Co",
            "role": "Software Engineer Intern",
            "status": "Maybe",
        },
    )

    assert response.status_code == 422

What should match?

This reference contains the temporary client helper plus both completed tests. Each test receives a separate SQLite database through tmp_path.

Your tracker now preserves valid applications while rejecting bad status values. Next, you will document the project and publish the tested code to GitHub.

Publish Your GitHub Repository

Your tracker now saves applications in SQLite. Its automated tests prove that valid requests work.

They also prove that unsupported statuses receive an HTTP 422 response. A reviewer still needs clear instructions for running the project.

You'll capture the tested project with Git. Then you'll publish it through GitHub so that reviewers can see your engineering decisions.

In this step, get ready to:
  • Create project documentation in README.md.
  • Capture the tested source code in a local Git commit.
  • Publish the commit to a public GitHub repository.
Create your project documentation

A README gives another developer one place to understand the tracker. It records the setup process plus the reason you chose SQLite.

  • In the Visual Studio Code window from earlier, use the file creation control in the Explorer sidebar to create README.md inside internship-application-tracker-api.
  • Paste the first part of the documentation into the blank README.md file using the content below:
# Internship Application Tracker API

A small REST API for recording internship applications while demonstrating Python, FastAPI, SQLite, validation, and automated testing.

## Features

- Create an application with a company, role, and status.
- List saved applications.
- Keep records after server restarts with SQLite.
- Reject unsupported statuses.
- Test API behavior with pytest.

## Requirements

- Windows
- Python 3.10 or newer
- Git

## Run locally

```text
py -m venv .venv
.venv\Scripts\activate
py -m pip install -r requirements.txt
fastapi dev
```

What does this documentation cover?

  • The opening explains the purpose of the tracker.
  • The feature list shows what a reviewer can demonstrate with the API.
  • The requirements make the project environment reproducible.
  • The run commands show how to create the environment before starting the API.
  • Save README.md.
  • Confirm the editor now shows the project title plus the Features section.

Documentation missing from the editor?

  • Check that the file is named README.md with the capital letters shown.
  • Confirm the file sits directly inside internship-application-tracker-api.

Ask for help if the Markdown headings or code fence look different. Help me check the first part of my README.md against the required project documentation.

The final sections show how to verify the project. They also explain the engineering value of durable storage plus isolated tests.

  • Place your cursor after the closing code fence in README.md.
  • Leave one blank line.
  • Append the remaining documentation by pasting the content below:
Open `http://127.0.0.1:8000/docs` to use the interactive API documentation.

## Run the tests

```text
pytest
```

## Engineering decision

The first version stored applications in memory, which made every record disappear after a server restart. SQLite adds durable storage without requiring a separate database server. Parameterized SQL queries keep user values separate from SQL statements, and temporary test databases keep tests isolated from personal application data.

Why include the engineering decision?

The final section explains the problem that SQLite solved. It shows that you can connect a technical choice to an observable limitation.

The testing note shows how temporary databases protect personal application data. That gives a reviewer evidence of deliberate test design.

  • Save README.md.
  • Confirm the file ends with the Engineering decision section.

Engineering section in the wrong place?

  • Check that the browser URL appears after the local run code fence.
  • Check that the testing section appears before the engineering decision.

Use this prompt if the two documentation chunks do not join cleanly. Help me compare my README.md structure with the required internship tracker documentation.

✔️ Awesome, I've got everything!

Great. Double-check that you saved README.md before continuing.

ⓧ I'd like to double check the full code

# Internship Application Tracker API

A small REST API for recording internship applications while demonstrating Python, FastAPI, SQLite, validation, and automated testing.

## Features

- Create an application with a company, role, and status.
- List saved applications.
- Keep records after server restarts with SQLite.
- Reject unsupported statuses.
- Test API behavior with pytest.

## Requirements

- Windows
- Python 3.10 or newer
- Git

## Run locally

```text
py -m venv .venv
.venv\Scripts\activate
py -m pip install -r requirements.txt
fastapi dev
```

Open `http://127.0.0.1:8000/docs` to use the interactive API documentation.

## Run the tests

```text
pytest
```

## Engineering decision

The first version stored applications in memory, which made every record disappear after a server restart. SQLite adds durable storage without requiring a separate database server. Parameterized SQL queries keep user values separate from SQL statements, and temporary test databases keep tests isolated from personal application data.

Before you run the documented test command, do you expect both API checks to remain green after adding only documentation?

  • Verify the documented project from the activated virtual environment by running this command:
pytest

What does this check prove?

The test run proves that adding documentation did not change the API behavior. It also confirms that the command shown in your README works.

You'll see 2 passed. Your documentation now matches a working test command.

Tests no longer passing?

  • Confirm the terminal is still inside internship-application-tracker-api.
  • Check that the activated environment indicator is still visible in the terminal prompt.
  • Confirm that main.py plus test_main.py remain unchanged.

Share the test summary without posting personal application data. Help me diagnose why pytest stopped passing after I added README.md.

Commit your project with Git

A Git commit captures a named snapshot of the project. This gives the public repository a clear starting point.

  • Initialize the local repository on the main branch by running this command:
git init -b main

What does this command do?

The command starts Git history inside internship-application-tracker-api. The branch begins with the name main.

You'll see Git confirm that it initialized a repository in your project folder.

Git initialization failed?

Confirm the terminal is inside internship-application-tracker-api. Check that the Git version command worked during setup.

Use this prompt for help with an environment-specific failure. Help me troubleshoot why git init did not initialize my Windows project repository.

Your existing .gitignore keeps the virtual environment plus local database outside version control. The repository receives only the files another developer needs.

  • Stage the publishable project files by running this command:
git add .

What gets staged?

Git stages the source code plus tests. It also stages the dependency list plus README.

The ignore rules leave .venv plus applications.db on your laptop.

The terminal prompt returns without reporting a staging failure. Your publishable files are ready for the first commit.

Local files included unexpectedly?

Confirm .gitignore still contains entries for .venv/ plus applications.db.

Use this prompt if the ignored files still appear in the staged project. Help me check why Git is including files listed in my .gitignore.

  • Create the first project snapshot by running this command:
git commit -m "Add existing file"

What does the commit capture?

The commit saves the staged versions of your documentation plus source files. Its message identifies this snapshot in the repository history.

That's a meaningful milestone. You'll see a commit summary for branch main with the message Add existing file.

Commit paused for author details?

Git may pause when author identity has not been configured on the laptop. Follow the identity guidance shown by Git before repeating the commit.

Use this prompt to interpret the message without changing project files. Help me resolve the Git author identity issue shown during my first commit.

Publish and verify your repository

A GitHub repository becomes the public home for your local commit. Keeping the new repository empty prevents generated files from conflicting with the files you already created.

  • Return to GitHub in your browser.
  • Select New repository.
  • Enter internship-application-tracker-api as the repository name.
  • Choose the public option under Visibility.
  • Keep the repository empty by leaving every initialization option unselected.
  • Select Create repository.
  • Copy the repository's remote URL here: REMOTE-URL.

You'll see setup commands for the new empty repository. This confirms that GitHub created the public destination without extra files.

  • Replace the placeholder in the command below with REMOTE-URL before running it:
git remote add origin REMOTE-URL

What does the remote connection do?

The command gives your GitHub repository the local name origin. Future push commands can use that short name.

  • Verify the saved remote by running this command:
git remote -v

What should the remote show?

Git lists the repository URL for sending commits. It also lists the same destination for retrieving commits.

You'll see the GitHub URL you copied beside origin.

Remote URL missing or incorrect?

Compare the displayed URL with the one on your GitHub repository page. Confirm that you replaced the full REMOTE-URL placeholder.

Use this prompt if origin does not point to the new repository. Help me troubleshoot the origin remote for my GitHub repository.

GitHub may ask you to authenticate during the first push. This authorizes Git to publish the commit without exposing your account password.

Before you push, which project files do you expect GitHub to display after the upload finishes?

  • Publish the local main branch by running this command:
git push -u origin main

What does this push do?

The command sends your local commit to origin. It also links the local main branch with the remote branch.

Complete the GitHub authentication flow if prompted. You'll then see Git confirm that the main branch was published.

Push blocked by authentication?

Complete the GitHub sign-in flow offered by your credential helper. GitHub account passwords are not accepted for Git operations.

Use this prompt to choose the authentication path shown on your laptop. Help me complete GitHub authentication for my first Git push on Windows.

  • Return to the newly created repository page in your browser.
  • Refresh the repository page.

You'll see README.md, main.py, test_main.py, plus requirements.txt. The README displays the passing-test command.

Your FastAPI project is now public. The local .venv folder plus applications.db remain absent from GitHub.

Secret mission

Filter Applications by Status

Your tracker returns every saved application in one list. Add a validated status filter so you can focus on one stage while preserving the original unfiltered view.

Clean Up Your Resources

Clean Up Your Resources

Choose whether to keep your resources available, pause your local API, or delete everything. This project runs locally with a GitHub Free repository, so keeping everything costs $0.

Resources you used:

  • A local internship-application-tracker-api project folder.
  • A local .venv virtual environment inside the project folder.
  • A local applications.db SQLite database inside the project folder.
  • A public internship-application-tracker-api GitHub repository.

Keep everything running

No action is required. Choose this option if you want your complete project ready for future demonstrations.

  • Keep the local internship-application-tracker-api folder on your laptop.
  • Leave the public GitHub repository available for reviewers.
  • Restart the local API from a Command Prompt terminal in internship-application-tracker-api by running these commands:
.venv\Scripts\activate
fastapi dev

How Do These Commands Restart the API?

The first command activates the project environment. The second command starts the FastAPI development server.

The server holds the terminal until Ctrl+C stops it. Your interactive documentation remains available whenever the server is running.

Pause - I'll come back to this later

Stop the running server to free local memory. Your code and saved application records remain available for your return.

  • Stop the API in the Command Prompt terminal by pressing Ctrl+C.
  • Leave the active virtual environment by running this command:
deactivate

What Happens When You Pause?

Pressing Ctrl+C ends the development server. deactivate returns Command Prompt to your system environment.

Your complete project remains on your laptop. The public repository also stays available on GitHub.

Delete - I don't want to use this again

This option removes your local project and its public repository. The confirmation screens give you a final chance to check each destructive action.

Remove the local copy:

  • Stop the API in the Command Prompt terminal by pressing Ctrl+C if it is running.
  • Close Visual Studio Code.
  • Press the Windows key to open the search bar.
  • Type File Explorer in the search bar.
  • Press Enter to open File Explorer.
  • Go to the location containing your internship-application-tracker-api folder.
  • Select the internship-application-tracker-api folder.
  • Press the Delete key.

You should no longer see the internship-application-tracker-api folder in its previous location.

What Does the Local Deletion Remove?

Deleting the project folder also removes its nested .venv environment. It removes the local applications.db database too.

Remove the public repository:

  • Return to the public internship-application-tracker-api repository in GitHub.
  • Click Settings.
  • Select General.
  • Scroll to Danger Zone.
  • Select Delete this repository.
  • Review GitHub's deletion warnings.
  • Enter internship-application-tracker-api when GitHub asks for the repository name.
  • Complete GitHub's final deletion confirmation.

You should no longer see the repository in your GitHub repository list.

Can a Deleted Repository Be Restored?

Repository deletion is destructive. Some deleted repositories can be restored within 90 days under GitHub's current policy.

Nice Work!

Nice Work!

You did it. Your internship application tracker is now a tested FastAPI REST API that stores application records between restarts.

You've learned how to:

  • Build REST endpoints for creating retrievable internship application records with generated IDs.
  • Persist application records in SQLite through parameterized SQL. Reject unsupported statuses through Pydantic validation before storage.
  • Prove API behavior with pytest tests that use isolated temporary databases. Publish the tested source with setup instructions in a public GitHub repository.
  • Extend GET /applications with a validated status query parameter in the optional Secret Mission. Confirm status filtering with a third automated test.

Ready to quiz yourself?