OCPO FastAPI Template

This is as a realtime quey API for any entity that want's to get CSD data. Built on FASTAPI, a lightweight Python API framework.

Technology Stack

• ⚡ FastAPI
  • 🧰 SQLAlchemy V2 for the Python SQL database interactions (ORM).
  • 🔍 Pydantic, used by FastAPI, for the data validation and settings management.
  • 💾 PostgreSQL as the SQL database.
  • 🔐 JWT Authentication - JSON Web Token based authentication
• 🐋 Docker Compose for optional containerized development and builds (the app can also be run directly on the host; see below)
• ✅ Tests with Pytest.
• 🏭 CI (continuous integration) and CD (continuous deployment) based on GitHub Actions.

✨ Features

• User authentication with JWT tokens
• Role-based access control (superuser and regular users)
• Full CRUD operations for users and items
• Secure password hashing
• Email uniqueness validation
• Self-service user management
• Administrative user management
• Item ownership and permissions
• Async database operations
• Input validation and error handling

Configure

You can then update configs in the .env files to customize your configurations.

Before deploying it, make sure you change at least the values for:

• POSTGRES_PASSWORD

You can (and should) pass these as environment variables from secrets

Running without Docker

You can run the API and Alembic on your machine with a PostgreSQL instance you provide (local install, managed service, or Postgres only in Docker).

Prerequisites

• Python 3.12
• PostgreSQL (16 or newer is a good match for the Docker image in compose.yaml)

Python environment

From the repository root:

python -m venv .venv

Activate the virtual environment:

• Linux or macOS: source .venv/bin/activate
• Windows (PowerShell): .venv\Scripts\Activate.ps1

Install dependencies:

python -m pip install --upgrade pip
python -m pip install -r requirements.txt
python -m pip install -r requirements-dev.txt

The second requirements file is optional for running the API alone; include it for tests, linting, and other dev tooling.

Database and .env

Create an empty database (and role, if needed) that matches the values you will put in .env. Copy .env.example to .env at the repository root and set at least POSTGRES_*, SECRET_KEY, FIRST_SUPERUSER, and FIRST_SUPERUSER_PASSWORD as described in Configure.

For PostgreSQL on the same machine using the default port, use POSTGRES_SERVER=localhost and POSTGRES_PORT=5432.

Settings load the root .env when commands are run with a working directory of src (see app/core/config.py).

Migrations and API server

Alembic and Uvicorn expect the working directory to be src so the app package and alembic.ini resolve correctly (this mirrors the layout under /usr/src in the container).

cd src
alembic upgrade head
uvicorn app.main:app --reload --host 0.0.0.0 --port 8000

Then open the interactive docs at http://localhost:8000/docs (ReDoc: http://localhost:8000/redoc).

To install and configure ocpo-api as a Windows service using NSSM, see docs/nssm-windows-service.md.

Hybrid: Postgres in Docker, API on the host

If you want Docker only for the database, start the dev database service and leave the API on the host:

docker compose --profile dev up db-dev -d

With the default port mapping in compose.yaml, Postgres is available on the host at localhost:8081. Point .env at it with POSTGRES_SERVER=localhost, POSTGRES_PORT=8081, and the same POSTGRES_USER, POSTGRES_PASSWORD, and POSTGRES_DB values your Compose stack uses. Then run the Python steps above from src.

Tests without Docker

With requirements-dev.txt installed and PostgreSQL reachable using the connection details in .env, from src run:

cd src
python -m pytest

The test suite configures the engine from the same settings as the app and, for the session-scoped engine fixture, drops and recreates tables in that database. Use a dedicated database (for example a separate POSTGRES_DB value in .env while testing) or keep using ./scripts/test-local.sh, which runs tests against an isolated Compose profile.

Windows service (NSSM)

To run the API as a Windows service with NSSM (including use of the repository run_api.bat wrapper for PYTHONPATH and the virtualenv), see docs/nssm-windows-service.md.

🐋 Running with Docker

If requirements change build a new base image

docker buildx build -f Dockerfile.base -t ocl_base_image:latest .

Start the stack with Docker Compose:

./scripts/dev-local.sh

To seed data into the db. (Make sure to change the SEED_FILE variable in your .env)

# Option 1 (CLI): copy the XLSX into the running container, then run the seeder
docker cp "Incident_DATA.xlsx" ocl-api-oag-api-dev-1:/usr/src/Incident_DATA.xlsx
docker exec -it ocl-api-oag-api-dev-1 python -m app.seed_incident_db --file "/usr/src/Incident_DATA.xlsx"

Alternatively (recommended for non-technical users), upload the monthly XLSX and seed via the API:

• Swagger: open /docs on the same base URL you use for the API (for example http://localhost:8000/docs without Docker, or the host port from compose.yaml with Docker) → POST /api/v1/incidents/seed
• curl (replace the base URL and port to match your setup):

curl -X POST "http://localhost:8000/api/v1/incidents/seed" \
  -H "Authorization: Bearer <access_token>" \
  -F "file=@Incident_DATA.xlsx"

This will:

• Build the API container
• Start PostgreSQL
• Set up the database
• Start the FastAPI server

🧪 Testing

Running Tests

Execute the test suite:

./scripts/test-local.sh

This will:

• Set up a test database
• Run the pytest suite

Test Coverage

The project includes:

• Unit tests for CRUD operations
• Integration tests for API endpoints
• Fixture-based test setup
• Transaction rollback for test isolation
• Async test support

📚 API Documentation

Once running, access the interactive API documentation at:

• Without Docker (Uvicorn on port 8000 as above): Swagger UI at http://localhost:8000/docs, ReDoc at http://localhost:8000/redoc
• With Docker Compose: use the host port mapped to the API container in compose.yaml (for example 8085 → http://localhost:8085/docs)

📁 Project Structure

.
├── Dockerfile           # Main Dockerfile for the application
├── Dockerfile.base      # Base Dockerfile for common dependencies
├── README.md           # Project documentation
├── compose.yaml        # Docker Compose configuration
├── development.md      # Development guidelines and setup
├── requirements-dev.txt # Development dependencies
├── requirements.txt    # Production dependencies
├── scripts/           # Utility scripts
│   ├── create_alembic_revision.sh  # Database migration script
│   ├── dev-local.sh               # Local development startup
│   └── test-local.sh             # Test execution script
└── src/               # Source code root
    ├── alembic.ini    # Alembic configuration
    └── app/           # Main application package
        ├── alembic/   # Database migrations
        │   ├── README
        │   ├── env.py
        │   ├── script.py.mako
        │   └── versions/
        ├── api/       # API layer
        │   ├── deps.py           # FastAPI dependencies
        │   ├── main.py          # API configuration
        │   └── routes/         # API endpoints
        │       ├── items.py    # Item endpoints
        │       ├── login.py    # Authentication endpoints
        │       ├── users.py    # User endpoints
        │       └── utils.py    # Route utilities
        ├── core/      # Core functionality
        │   ├── config.py      # Application configuration
        │   ├── db.py         # Database connection
        │   └── security.py   # Security utilities
        ├── crud/      # Database operations
        │   ├── base.py      # Base CRUD operations
        │   ├── items.py     # Item CRUD operations
        │   └── users.py     # User CRUD operations
        ├── main.py    # Application entry point
        ├── models/    # Data models
        │   ├── api/         # API models (Pydantic)
        │   │   ├── generic.py
        │   │   ├── items.py
        │   │   └── users.py
        │   └── db/          # Database models (SQLAlchemy)
        │       ├── base.py
        │       ├── items.py
        │       └── users.py
        ├── pytest.ini  # Pytest configuration
        ├── tests/      # Test suite
        │   ├── api/          # API tests
        │   │   └── routes/
        │   │       ├── test_items.py
        │   │       ├── test_login.py
        │   │       └── test_users.py
        │   ├── conftest.py   # Test fixtures
        │   ├── crud/         # CRUD tests
        │   │   ├── test_item.py
        │   │   └── test_user.py
        │   └── utils/        # Test utilities
        │       ├── item.py
        │       ├── user.py
        │       └── utils.py
        └── utils.py    # General utilities