# FastAPI, Jinja2, PostgreSQL Webapp Template

![Screenshot of homepage](docs/static/screenshot.jpg)

## Quickstart

This quickstart guide provides a high-level overview. See the full documentation for comprehensive information on [features](https://promptlytechnologies.com/fastapi-jinja2-postgres-webapp/index.html), [installation](https://promptlytechnologies.com/fastapi-jinja2-postgres-webapp/docs/installation.html), [architecture](https://promptlytechnologies.com/fastapi-jinja2-postgres-webapp/docs/architecture.html), [conventions, code style, and customization](https://promptlytechnologies.com/fastapi-jinja2-postgres-webapp/docs/customization.html), [deployment to cloud platforms](https://promptlytechnologies.com/fastapi-jinja2-postgres-webapp/docs/deployment.html), and [contributing](https://promptlytechnologies.com/fastapi-jinja2-postgres-webapp/docs/contributing.html).

## Features

This template combines three of the most lightweight and performant open-source web development frameworks into a customizable webapp template with:

- Pure Python backend
- Minimal-Javascript frontend
- Powerful, easy-to-manage database

The template also includes full-featured secure auth with:

- Token-based authentication
- Password recovery flow
- Role-based access control system

## Design Philosophy

The design philosophy of the template is to prefer low-level, best-in-class open-source frameworks that offer flexibility, scalability, and performance without vendor-lock-in. You'll find the template amazingly easy not only to understand and customize, but also to deploy to any major cloud hosting platform.

## Tech Stack

**Core frameworks:**

- [FastAPI](https://fastapi.tiangolo.com/): scalable, high-performance, type-annotated Python web backend framework
- [PostgreSQL](https://www.postgresql.org/): the world's most advanced open-source database engine
- [Jinja2](https://jinja.palletsprojects.com/en/3.1.x/): frontend HTML templating engine
- [SQLModel](https://sqlmodel.tiangolo.com/): easy-to-use Python ORM

**Additional technologies:**

- [uv](https://docs.astral.sh/uv/): Python dependency manager
- [Pytest](https://docs.pytest.org/en/7.4.x/): testing framework
- [Docker](https://www.docker.com/): development containerization
- [Github Actions](https://docs.github.com/en/actions): CI/CD pipeline
- [Quarto](https://quarto.org/docs/): simple documentation website renderer
- [MyPy](https://mypy.readthedocs.io/en/stable/): static type checker for Python
- [Bootstrap](https://getbootstrap.com/): HTML/CSS styler
- [Resend](https://resend.com/): zero- or low-cost email service used for password recovery

## Installation

For comprehensive installation instructions, see the [installation page](https://promptlytechnologies.com/fastapi-jinja2-postgres-webapp/docs/installation.html).

### uv

MacOS and Linux:

``` bash
wget -qO- https://astral.sh/uv/install.sh | sh
```

Windows:

``` bash
powershell -ExecutionPolicy ByPass -c "irm https://astral.sh/uv/install.ps1 | iex"
```

See the [uv installation docs](https://docs.astral.sh/uv/getting-started/installation/) for more information.

### Python

Install Python 3.12 or higher from either the official [downloads page](https://www.python.org/downloads/) or using uv:

``` bash
# Installs the latest version
uv python install
```

### Docker and Docker Compose

Install Docker Desktop and Coker Compose for your operating system by following the [instructions in the documentation](https://docs.docker.com/compose/install/).

### PostgreSQL headers

For Ubuntu/Debian:

``` bash
sudo apt update && sudo apt install -y python3-dev libpq-dev libwebp-dev
```

For macOS:

``` bash
brew install postgresql
```

For Windows:

- No installation required

### Python dependencies

From the root directory, run:

``` bash
uv venv
uv sync
```

This will create an in-project virtual environment and install all dependencies.

### Set environment variables

Copy `.env.example` to `.env` with `cp .env.example .env`.

Generate a 256 bit secret key with `openssl rand -base64 32` and paste it into the .env file.

Set your desired database name, username, and password in the .env file.

To use password recovery and other email features, register a [Resend](https://resend.com/) account, verify a domain, get an API key, and paste the API key and the email address you want to send emails from into the .env file. Note that you will need to [verify a domain through the Resend dashboard](https://resend.com/docs/dashboard/domains/introduction) to send emails from that domain.

### Start development database

To start the development database, run the following command in your terminal from the root directory:

``` bash
docker compose up -d
```

### Run the development server

Make sure the development database is running and tables and default permissions/roles are created first.

``` bash
uv run python -m uvicorn main:app --host 0.0.0.0 --port 8000 --reload
```

Navigate to http://localhost:8000/

### Lint types with mypy

``` bash
uv run mypy .
```

## Developing with LLMs

``` {python}
#| echo: false
#| include: false
import re
from pathlib import Path


def extract_file_paths(quarto_yml_path):
    """
    Extract href paths from _quarto.yml file.
    Returns a list of .qmd file paths.
    """
    with open(quarto_yml_path, 'r', encoding='utf-8') as f:
        content = f.read()

    # Find all href entries that point to .qmd files
    pattern = r'^\s*-\s*href:\s*(.*?\.qmd)\s*$'
    matches = re.findall(pattern, content, re.MULTILINE)
    return matches


def process_qmd_content(file_path):
    """
    Process a .qmd file by converting YAML frontmatter to markdown heading.
    Returns the processed content as a string.
    """
    with open(file_path, 'r', encoding='utf-8') as f:
        content = f.read()

    # Replace YAML frontmatter with markdown heading
    pattern = r'^---\s*\ntitle:\s*"([^"]+)"\s*\n---'
    processed_content = re.sub(pattern, r'# \1', content)
    return processed_content


# Get the current working directory
base_dir = Path.cwd()
quarto_yml_path = base_dir / '_quarto.yml'

# Extract file paths from _quarto.yml
qmd_files = extract_file_paths(quarto_yml_path)

# Process each .qmd file and collect contents
processed_contents = []
for qmd_file in qmd_files:
    file_path = base_dir / qmd_file
    if file_path.exists():
        processed_content = process_qmd_content(file_path)
        processed_contents.append(processed_content)

# Concatenate all contents with double newline separator
final_content = '\n\n'.join(processed_contents)

# Ensure the output directory exists
output_dir = base_dir / 'docs' / 'static'
output_dir.mkdir(parents=True, exist_ok=True)

# Write the concatenated content to the output file
output_path = output_dir / 'documentation.txt'
with open(output_path, 'w', encoding='utf-8') as f:
    f.write(final_content)
```

The `.cursor/rules` folder contains a set of AI rules for working on this codebase in the Cursor IDE. We have also provided an [llms.txt](static/llms.txt) system prompt file for use with other agentic LLM workflows and exposed the full Markdown-formatted project documentation as a [single text file](docs/static/documentation.txt) for easy downloading and embedding for RAG.

## Contributing

Your contributions are welcome! See the [issues page](https://github.com/promptly-technologies-llc/fastapi-jinja2-postgres-webapp/issues) for ideas. Fork the repository, create a new branch, make your changes, and submit a pull request.

## License

This project is created and maintained by [Promptly Technologies, LLC](https://promptlytechnologies.com/) and licensed under the MIT License. See the LICENSE file for more details.


# Architecture

## Data flow

This application uses a Post-Redirect-Get (PRG) pattern. The user submits a form, which sends a POST request to a FastAPI endpoint on the server. The database is updated, and the user is redirected to a GET endpoint, which fetches the updated data and re-renders the Jinja2 page template with the new data.

``` {python}
#| echo: false
#| include: false
from graphviz import Digraph

dot = Digraph()
dot.attr(rankdir='TB')
dot.attr('node', shape='box', style='rounded')

# Create client subgraph at top
with dot.subgraph(name='cluster_client') as client:
    client.attr(label='Client')
    client.attr(rank='topmost')
    client.node('A', 'User submits form', fillcolor='lightblue', style='rounded,filled')
    client.node('B', 'HTML/JS form validation', fillcolor='lightblue', style='rounded,filled')

# Create server subgraph below
with dot.subgraph(name='cluster_server') as server:
    server.attr(label='Server')
    server.node('C', 'FastAPI request validation in route signature', fillcolor='lightgreen', style='rounded,filled')
    server.node('D', 'Business logic validation in route function body', fillcolor='lightgreen', style='rounded,filled')
    server.node('E', 'Update database', fillcolor='lightgreen', style='rounded,filled')
    server.node('F', 'Middleware error handler', fillcolor='lightgreen', style='rounded,filled')
    server.node('G', 'Render error template', fillcolor='lightgreen', style='rounded,filled')
    server.node('H', 'Redirect to GET endpoint', fillcolor='lightgreen', style='rounded,filled')
    server.node('I', 'Fetch updated data', fillcolor='lightgreen', style='rounded,filled')
    server.node('K', 'Re-render Jinja2 page template', fillcolor='lightgreen', style='rounded,filled')

with dot.subgraph(name='cluster_client_post') as client_post:
    client_post.attr(label='Client')
    client_post.attr(rank='bottommost')
    client_post.node('J', 'Display rendered page', fillcolor='lightblue', style='rounded,filled')

# Add visible edges
dot.edge('A', 'B')
dot.edge('B', 'A')
dot.edge('B', 'C', label='POST Request to FastAPI endpoint')
dot.edge('C', 'D')
dot.edge('C', 'F', label='RequestValidationError')
dot.edge('D', 'E', label='Valid data')
dot.edge('D', 'F', label='Custom Validation Error')
dot.edge('E', 'H', label='Data updated')
dot.edge('H', 'I')
dot.edge('I', 'K')
dot.edge('K', 'J', label='Return HTML')
dot.edge('F', 'G')
dot.edge('G', 'J', label='Return HTML')

dot.render('static/data_flow', format='png', cleanup=True)
```

![Data flow diagram](static/data_flow.png)

The advantage of the PRG pattern is that it is very straightforward to implement and keeps most of the rendering logic on the server side. One disadvantage is that it requires an extra round trip to the database to fetch the updated data, and re-rendering the entire page template may be less efficient than a partial page update on the client side. Another disadvantage is that it if the user makes an invalid form submission, they will see an error page and will have to click the browser's "back" button to get back to the form with their original form inputs.

A future iteration of this application will use HTMX to update the page in place, so that on an invalid submission an error toast is displayed without a page reload (thus preserving the user's scroll position and form inputs).

# Authentication

## Security features

This template implements a comprehensive authentication system with security best practices:

1. **Token Security**:
   - JWT-based with separate access/refresh tokens
   - Strict expiry times (30 min access, 30 day refresh)
   - Token type validation
   - HTTP-only cookies
   - Secure flag enabled
   - SameSite=strict restriction

2. **Password Security**:
   - Strong password requirements enforced
   - Bcrypt hashing with random salt
   - Password reset tokens are single-use
   - Reset tokens have expiration

3. **Cookie Security**:
   - HTTP-only prevents JavaScript access
   - Secure flag ensures HTTPS only
   - Strict SameSite prevents CSRF

4. **Error Handling**:
   - Validation errors properly handled
   - Security-related errors don't leak information
   - Comprehensive error logging
   
The diagrams below show the main authentication flows.

## Registration and login flow

``` {python}
#| echo: false
#| include: false
from graphviz import Digraph

# Create graph for registration/login
auth = Digraph(name='auth_flow')
auth.attr(rankdir='TB')
auth.attr('node', shape='box', style='rounded')

# Client-side nodes
with auth.subgraph(name='cluster_client') as client:
    client.attr(label='Client')
    client.node('register_form', 'Submit registration', fillcolor='lightblue', style='rounded,filled')
    client.node('login_form', 'Submit login', fillcolor='lightblue', style='rounded,filled')
    client.node('store_cookies', 'Store secure cookies', fillcolor='lightblue', style='rounded,filled')

# Server-side nodes
with auth.subgraph(name='cluster_server') as server:
    server.attr(label='Server')
    # Registration path
    server.node('validate_register', 'Validate registration data', fillcolor='lightgreen', style='rounded,filled')
    server.node('hash_new', 'Hash new password', fillcolor='lightgreen', style='rounded,filled')
    server.node('store_user', 'Store user in database', fillcolor='lightgreen', style='rounded,filled')
    
    # Login path
    server.node('validate_login', 'Validate login data', fillcolor='lightgreen', style='rounded,filled')
    server.node('verify_password', 'Verify password hash', fillcolor='lightgreen', style='rounded,filled')
    server.node('fetch_user', 'Fetch user from database', fillcolor='lightgreen', style='rounded,filled')
    
    # Common path
    server.node('generate_tokens', 'Generate JWT tokens', fillcolor='lightgreen', style='rounded,filled')

# Registration path
auth.edge('register_form', 'validate_register', 'POST /register')
auth.edge('validate_register', 'hash_new')
auth.edge('hash_new', 'store_user')
auth.edge('store_user', 'generate_tokens', 'Success')

# Login path
auth.edge('login_form', 'validate_login', 'POST /login')
auth.edge('validate_login', 'fetch_user')
auth.edge('fetch_user', 'verify_password')
auth.edge('verify_password', 'generate_tokens', 'Success')

# Common path
auth.edge('generate_tokens', 'store_cookies', 'Set-Cookie')

auth.render('static/auth_flow', format='png', cleanup=True)
```

![Registration and login flow](static/auth_flow.png)

## Password reset flow

``` {python}
#| echo: false
#| include: false
from graphviz import Digraph

# Create graph for password reset
reset = Digraph(name='reset_flow')
reset.attr(rankdir='TB')
reset.attr('node', shape='box', style='rounded')

# Client-side nodes - using light blue fill
reset.node('forgot', 'User submits forgot password form', fillcolor='lightblue', style='rounded,filled')
reset.node('reset', 'User submits reset password form', fillcolor='lightblue', style='rounded,filled')
reset.node('email_client', 'User clicks reset link', fillcolor='lightblue', style='rounded,filled')

# Server-side nodes - using light green fill
reset.node('validate', 'Validation', fillcolor='lightgreen', style='rounded,filled')
reset.node('token_gen', 'Generate reset token', fillcolor='lightgreen', style='rounded,filled')
reset.node('hash', 'Hash password', fillcolor='lightgreen', style='rounded,filled')
reset.node('email_server', 'Send email with Resend', fillcolor='lightgreen', style='rounded,filled')
reset.node('db', 'Database', shape='cylinder', fillcolor='lightgreen', style='filled')

# Add edges with labels
reset.edge('forgot', 'token_gen', 'POST')
reset.edge('token_gen', 'db', 'Store')
reset.edge('token_gen', 'email_server', 'Add email/token as URL parameter')
reset.edge('email_server', 'email_client')
reset.edge('email_client', 'reset', 'Set email/token as form input')
reset.edge('reset', 'validate', 'POST')
reset.edge('validate', 'hash')
reset.edge('hash', 'db', 'Update')

reset.render('static/reset_flow', format='png', cleanup=True)
```

![Password reset flow](static/reset_flow.png)


# Installation

## Install all development dependencies in a VSCode Dev Container

If you use VSCode with Docker to develop in a container, the following VSCode Dev Container configuration will install all development dependencies:

``` json
{
	"name": "Python 3",
	"image": "mcr.microsoft.com/devcontainers/python:1-3.12-bookworm",
	"postCreateCommand": "sudo apt update && sudo apt install -y python3-dev libpq-dev graphviz libwebp-dev && npm install bootstrap@5.3.3 && npm install -g sass && npm install -g gulp && uv venv && uv sync",
	"features": {
		"ghcr.io/va-h/devcontainers-features/uv:1": {
			"version": "latest"
		},
		"ghcr.io/devcontainers/features/docker-outside-of-docker:1": {},
		"ghcr.io/rocker-org/devcontainer-features/quarto-cli:1": {}
	}
}
```

Simply create a `.devcontainer` folder in the root of the project and add a `devcontainer.json` file in the folder with the above content. VSCode may prompt you to install the Dev Container extension if you haven't already, and/or to open the project in a container. If not, you can manually select "Dev Containers: Reopen in Container" from `View > Command Palette`.

*IMPORTANT: If using this dev container configuration, you will need to set the `DB_HOST` environment variable to "host.docker.internal" in the `.env` file.*

## Install development dependencies manually

### uv

MacOS and Linux:

``` bash
wget -qO- https://astral.sh/uv/install.sh | sh
```

Windows:

``` bash
powershell -ExecutionPolicy ByPass -c "irm https://astral.sh/uv/install.ps1 | iex"
```

See the [uv installation docs](https://docs.astral.sh/uv/getting-started/installation/) for more information.

### Python

Install Python 3.12 or higher from either the official [downloads page](https://www.python.org/downloads/) or using uv:

``` bash
# Installs the latest version
uv python install
```

### Docker and Docker Compose

Install Docker Desktop and Docker Compose for your operating system by following the [instructions in the documentation](https://docs.docker.com/compose/install/).

### PostgreSQL headers

For Ubuntu/Debian:

``` bash
sudo apt update && sudo apt install -y python3-dev libpq-dev libwebp-dev
```

For macOS:

``` bash
brew install postgresql
```

For Windows:

- No installation required

### Python dependencies

From the root directory, run:

``` bash
uv venv
```

This will create an in-project virtual environment. Then run:

``` bash
uv sync
```

This will install all dependencies.

(Note: if `psycopg2` installation fails, you probably just need to install the PostgreSQL headers first and then try again.)

### Configure IDE

If you are using VSCode or Cursor as your IDE, you will need to select the `uv`-managed Python version as your interpreter for the project. Go to `View > Command Palette`, search for `Python: Select Interpreter`, and select the Python version labeled `('.venv':venv)`.

It is also recommended to install the [Python](https://marketplace.visualstudio.com/items?itemName=ms-python.python) and [Quarto](https://marketplace.visualstudio.com/items?itemName=quarto.quarto) IDE extensions.

## Install documentation dependencies manually

### Quarto CLI

To render the project documentation, you will need to download and install the [Quarto CLI](https://quarto.org/docs/get-started/) for your operating system.

### Graphviz

Architecture diagrams in the documentation are rendered with [Graphviz](https://graphviz.org/).

For macOS:

``` bash
brew install graphviz
```

For Ubuntu/Debian:

``` bash
sudo apt update && sudo apt install -y graphviz
```

For Windows:

- Download and install from [Graphviz.org](https://graphviz.org/download/#windows)

## Set environment variables

Copy .env.example to .env with `cp .env.example .env`.

Generate a 256 bit secret key with `openssl rand -base64 32` and paste it into the .env file.

Set your desired database name, username, and password in the .env file.

To use password recovery, register a [Resend](https://resend.com/) account, verify a domain, get an API key, and paste the API key and sender email address into the .env file.

If using the dev container configuration, you will need to set the `DB_HOST` environment variable to "host.docker.internal" in the .env file. Otherwise, set `DB_HOST` to "localhost" for local development. (In production, `DB_HOST` will be set to the hostname of the database server.)

## Start development database

To start the development database, run the following command in your terminal from the root directory:

``` bash
docker compose up -d
```

If at any point you change the environment variables in the .env file, you will need to stop the database service *and tear down the volume*:

``` bash
# Don't forget the -v flag to tear down the volume!
docker compose down -v
```

You may also need to restart the terminal session to pick up the new environment variables. You can also add the `--force-recreate` and `--build` flags to the startup command to ensure the container is rebuilt:

``` bash
docker compose up -d --force-recreate --build
```

## Run the development server

Before running the development server, make sure the development database is running and tables and default permissions/roles are created first. Then run the following command in your terminal from the root directory:

``` bash
uvicorn main:app --host 0.0.0.0 --port 8000 --reload
```

Navigate to http://localhost:8000/.

(Note: If startup fails with a sqlalchemy/psycopg2 connection error, make sure that Docker Desktop and the database service are running and that the environment variables in the `.env` file are correctly populated, and then try again.)

## Lint types with mypy

``` bash
mypy .
```


# Customization

## Development workflow

### Dependency management with `uv`

The project uses `uv` to manage dependencies:

- Add new dependency: `uv add <dependency>`
- Add development dependency: `uv add --dev <dependency>`
- Remove dependency: `uv remove <dependency>`
- Update lock file: `uv lock`
- Install all dependencies: `uv sync`
- Install only production dependencies: `uv sync --no-dev`
- Upgrade dependencies: `uv lock --upgrade`

### IDE configuration

If you are using VSCode or Cursor as your IDE, you will need to select the `uv`-managed Python version as your interpreter for the project. Go to `View > Command Palette`, search for `Python: Select Interpreter`, and select the Python version labeled `('.venv':venv)`.

If your IDE does not automatically detect and display this option, you can manually select the interpreter by selecting "Enter interpreter path" and then navigating to the `.venv/bin/python` subfolder in your project directory.

### Extending the template

The `routers/core/` and `utils/core/` directories contain the core backend logic for the template. 

Your custom Python backend code should go primarily in the `routers/app/` and `utils/app/` directories.

For the frontend, you will also need to develop custom Jinja2 templates in the `templates/` folder and add custom static assets in `static/`.

### Testing

The project uses Pytest for unit testing. It's highly recommended to write and run tests before committing code to ensure nothing is broken!

The following fixtures, defined in `tests/conftest.py`, are available in the test suite:

- `engine`: Creates a new SQLModel engine for the test database.
- `set_up_database`: Sets up the test database before running the test suite by dropping all tables and recreating them to ensure a clean state.
- `session`: Provides a session for database operations in tests.
- `clean_db`: Cleans up the database tables before each test by deleting all entries in the `PasswordResetToken`, `EmailUpdateToken`, `User`, `Role`, `Organization`, and `Account` tables.
- `test_account`: Creates a test account with a predefined email and hashed password.
- `test_user`: Creates a test user in the database linked to the test account.
- `auth_client`: Provides a `TestClient` instance with access and refresh token cookies set, overriding the `get_session` dependency to use the `session` fixture.
- `unauth_client`: Provides a `TestClient` instance without authentication cookies set, overriding the `get_session` dependency to use the `session` fixture.
- `test_organization`: Creates a test organization for use in tests.

To run the tests, use these commands:

- Run all tests: `uv run pytest`
- Run tests in debug mode (includes logs and print statements in console output): `uv run pytest -s`
- Run particular test files by name: `uv run pytest <test_file_name>`
- Run particular tests by name: `uv run pytest -k <test_name>`

### Type checking with mypy

The project uses type annotations and mypy for static type checking. To run mypy, use this command from the root directory:

```bash
mypy .
```

We find that mypy is an enormous time-saver, catching many errors early and greatly reducing time spent debugging unit tests. However, note that mypy requires you type annotate every variable, function, and method in your code base, so taking advantage of it requires a lifestyle change!

### Developing with LLMs

The `.cursor/rules` folder contains a set of AI rules for working on this codebase in the Cursor IDE. We have also provided an [llms.txt](static/llms.txt) system prompt file for use with other agentic LLM workflows and exposed the full Markdown-formatted project documentation as a [single text file](docs/static/documentation.txt) for easy downloading and embedding for RAG.

## Application architecture

### Post-Redirect-Get pattern

In this template, we use FastAPI to define the "API endpoints" of our application. An API endpoint is simply a URL that accepts user requests and returns responses. When a user visits a page, their browser sends what's called a "GET" request to an endpoint, and the server processes it (often querying a database), and returns a response (typically HTML). The browser renders the HTML, displaying the page.

We also create POST endpoints, which accept form submissions so the user can create, update, and delete data in the database. This template follows the Post-Redirect-Get (PRG) pattern to handle POST requests. When a form is submitted, the server processes the data and then returns a "redirect" response, which sends the user to a GET endpoint to re-render the page with the updated data. (See [Architecture](https://promptlytechnologies.com/fastapi-jinja2-postgres-webapp/docs/architecture.html) for more details.)

#### File structure

- FastAPI application entry point and homepage GET route: `main.py`
- Template FastAPI routes: `routers/core/`
    - Account and authentication endpoints: `account.py`
    - User profile management endpoints: `user.py`
    - Organization management endpoints: `organization.py`
    - Role management endpoints: `role.py`
    - Dashboard page: `dashboard.py`
    - Static pages (e.g., about, privacy policy, terms of service): `static_pages.py`
- Custom FastAPI routes for your app: `routers/app/`
- Jinja2 templates: `templates/`
- Static assets: `static/`
- Unit tests: `tests/`
- Test database configuration: `docker-compose.yml`
- Template helper functions: `utils/core/`
    - Auth helpers: `auth.py`
    - Database helpers: `db.py`
    - FastAPI dependencies: `dependencies.py`
    - Enums: `enums.py`
    - Image helpers: `images.py`
    - Database models: `models.py`
- Custom template helper functions for your app: `utils/app/`
- Exceptions: `exceptions/`
    - HTTP exceptions: `http_exceptions.py`
    - Other custom exceptions: `exceptions.py`
- Environment variables: `.env.example`, `.env`
- CI/CD configuration: `.github/`
- Project configuration: `pyproject.toml`
- Quarto documentation: 
    - README source: `index.qmd`
    - Website source: `index.qmd` + `docs/`
    - Configuration: `_quarto.yml` + `_environment`
- Rules for developing with LLMs in Cursor IDE: `.cursor/rules/`

Most everything else is auto-generated and should not be manually modified.

## Backend

### Code conventions

The GET route for the homepage is defined in the main entry point for the application, `main.py`. The entrypoint imports router modules from the `routers/core/` directory (for core/template logic) and `routers/app/` directory (for app-specific logic). In CRUD style, the core router modules are named after the resource they manage, e.g., `account.py` for account management. You should place your own endpoints in `routers/app/`.

We name our GET routes using the convention `read_<name>`, where `<name>` is the name of the resource, to indicate that they are read-only endpoints that do not modify the database. In POST routes that modify the database, you can use the `get_session` dependency as an argument to get a database session.

Routes that require authentication generally take the `get_authenticated_account` dependency as an argument. Unauthenticated GET routes generally take the `get_optional_user` dependency as an argument. If a route should *only* be seen by authenticated users (i.e., a login page), you can redirect to the dashboard if `get_optional_user` returns a `User` object.

### Context variables

Context refers to Python variables passed to a template to populate the HTML. In a FastAPI GET route, we can pass context to a template using the `templates.TemplateResponse` method, which takes the request and any context data as arguments. For example:

```python
@app.get("/welcome")
async def welcome(request: Request):
    return templates.TemplateResponse(
        request,
        "welcome.html",
        {"username": "Alice"}
    )
```

In this example, the `welcome.html` template will receive two pieces of context: the user's `request`, which is always passed automatically by FastAPI, and a `username` variable, which we specify as "Alice". We can then use the `{{{ username }}}` syntax in the `welcome.html` template (or any of its parent or child templates) to insert the value into the HTML.

### Email templating

Password reset and other transactional emails are also handled through Jinja2 templates, located in the `templates/emails` directory. The email templates follow the same inheritance pattern as web templates, with `base_email.html` providing the common layout and styling.

Here's how the default password reset email template looks:

![Default Password Reset Email Template](static/reset_email.png)

The email templates use inline CSS styles to ensure consistent rendering across email clients. Like web templates, they can receive context variables from the Python code (such as `reset_url` in the password reset template).

### Server-side form validation

Pydantic is used for data validation and serialization. It ensures that the data received in requests meets the expected format and constraints. Pydantic models are used to define the structure of request and response data, making it easy to validate and parse JSON payloads.

If a user-submitted form contains data that has the wrong number, names, or types of fields, Pydantic will raise a `RequestValidationError`, which is caught by middleware and converted into an HTTP 422 error response.

### Middleware exception handling

Middlewares—which process requests before they reach the route handlers and responses before they are sent back to the client—are defined in `main.py`. They are commonly used in web development for tasks such as error handling, authentication token validation, logging, and modifying request/response objects.

This template uses middlewares exclusively for global exception handling; they only affect requests that raise an exception. This allows for consistent error responses and centralized error logging. Middleware can catch exceptions raised during request processing and return appropriate HTTP responses. 

Middleware functions are decorated with `@app.exception_handler(ExceptionType)` and are executed in the order they are defined in `main.py`, from most to least specific.

Here's a middleware for handling the `PasswordMismatchError` exception from the previous example, which renders the `errors/validation_error.html` template with the error details:

```python
@app.exception_handler(PasswordValidationError)
async def password_validation_exception_handler(request: Request, exc: PasswordValidationError):
    return templates.TemplateResponse(
        request,
        "errors/validation_error.html",
        {
            "status_code": 422,
            "errors": {"error": exc.detail}
        },
        status_code=422,
    )
```

## Database configuration and access with SQLModel

SQLModel is an Object-Relational Mapping (ORM) library that allows us to interact with our PostgreSQL database using Python classes instead of writing raw SQL. It combines the features of SQLAlchemy (a powerful database toolkit) with Pydantic's data validation.

### Models and relationships

Core database models are defined in `utils/core/models.py`. Each model is a Python class that inherits from `SQLModel` and represents a database table. The key core models are:

- `Account`: Represents a user account with email and password hash
- `User`: Represents a user profile with details like name and avatar; the email and password hash are stored in the related `Account` model
- `Organization`: Represents a company or team
- `Role`: Represents a set of permissions within an organization
- `Permission`: Represents specific actions a user can perform (defined by ValidPermissions enum)
- `PasswordResetToken`: Manages password reset functionality with expiration
- `EmailUpdateToken`: Manages email update confirmation functionality with expiration

Two additional models are used by SQLModel to manage many-to-many relationships; you generally will not need to interact with them directly:

- `UserRoleLink`: Maps users to their roles (many-to-many relationship)
- `RolePermissionLink`: Maps roles to their permissions (many-to-many relationship)

Here's an entity-relationship diagram (ERD) of the current core database schema, automatically generated from our SQLModel definitions:

```{python}
#| echo: false
#| warning: false
import sys
sys.path.append("..")
from utils.core.models import *
from utils.core.db import engine
from sqlalchemy import MetaData
from sqlalchemy_schemadisplay import create_schema_graph

# Create the directed graph
graph = create_schema_graph(
    engine=engine,
    metadata=SQLModel.metadata,
    show_datatypes=True,
    show_indexes=True,
    rankdir='TB',
    concentrate=False
)

# Save the graph
graph.write_png('static/schema.png')
```

![Database Schema](static/schema.png)

To extend the database schema, define your own models in `utils/app/models.py` and import them in `utils/core/db.py` to make sure they are included in the `metadata` object in the `create_all` function.

### Database helpers

Database operations are facilitated by helper functions in `utils/core/db.py` (for core logic) and `utils/app/` (for app-specific helpers). Key functions in the core utils include:

- `set_up_db()`: Initializes the database schema and default data (which we do on every application start in `main.py`)
- `get_connection_url()`: Creates a database connection URL from environment variables in `.env`
- `get_session()`: Provides a database session for performing operations

To perform database operations in route handlers, inject the database session as a dependency (from `utils/core/db.py`):

```python
@app.get("/users")
async def get_users(session: Session = Depends(get_session)):
    users = session.exec(select(User)).all()
    return users
```

The session automatically handles transaction management, ensuring that database operations are atomic and consistent.

There is also a helper method on the `User` model that checks if a user has a specific permission for a given organization. Its first argument must be a `ValidPermissions` enum value (from `utils/core/models.py`), and its second argument must be an `Organization` object or an `int` representing an organization ID:

```python
permission = ValidPermissions.CREATE_ROLE
organization = session.exec(select(Organization).where(Organization.name == "Acme Inc.")).first()

user.has_permission(permission, organization)
```

You should create custom `AppPermissions` enum values for your application in `utils/app/` (if needed) and validate that users have the necessary permissions before allowing them to modify organization data resources.

### Cascade deletes

Cascade deletes (in which deleting a record from one table deletes related records from another table) can be handled at either the ORM level or the database level. This template handles cascade deletes at the ORM level, via SQLModel relationships. Inside a SQLModel `Relationship`, we set:

```python
sa_relationship_kwargs={
    "cascade": "all, delete-orphan"
}
```

This tells SQLAlchemy to cascade all operations (e.g., `SELECT`, `INSERT`, `UPDATE`, `DELETE`) to the related table. Since this happens through the ORM, we need to be careful to do all our database operations through the ORM using supported syntax. That generally means loading database records into Python objects and then deleting those objects rather than deleting records in the database directly. 

For example,

```python
session.exec(delete(Role))
```

will not trigger the cascade delete. Instead, we need to select the role objects and then delete them:

```python
for role in session.exec(select(Role)).all():
    session.delete(role)
```

This is slower than deleting the records directly, but it makes [many-to-many relationships](https://sqlmodel.tiangolo.com/tutorial/many-to-many/create-models-with-link/#create-the-tables) much easier to manage.

## Frontend

### HTML templating with Jinja2

To generate the HTML pages to be returned from our GET routes, we use Jinja2 templates. Jinja2's hierarchical templates allow creating a base template (`templates/base.html`) that defines the overall layout of our web pages (e.g., where the header, body, and footer should go). Individual pages can then extend this base template. We can also template reusable components that can be injected into our layout or page templates.

With Jinja2, we can use the `{% block %}` tag to define content blocks, and the `{% extends %}` tag to extend a base template. We can also use the `{% include %}` tag to include a component in a parent template. See the [Jinja2 documentation on template inheritance](https://jinja.palletsprojects.com/en/stable/templates/#template-inheritance) for more details.

### Custom theming with Bootstrap

[Install Node.js](https://nodejs.org/en/download/) on your local machine if it is not there already.

Install `bootstrap`, `sass`, `gulp`, and `gulp-sass` in your project:

```bash
npm install --save-dev bootstrap sass gulp gulp-cli gulp-sass
```

This will create a `node_modules` folder, a `package-lock.json` file, and a `package.json` file in the root directory of the project.

Create an `scss` folder and a basic `scss/styles.scss` file:

```bash
mkdir scss
touch scss/styles.scss
```

Your custom styles will go in `scss/styles.scss`, along with `@import` statements to include the Bootstrap components you want.

#### Customizing the Bootstrap SCSS

The default CSS for the template was compiled from the following `scss/styles.scss` configuration, which imports all of Bootstrap and overrides the `$theme-colors` and `$font-family-base` variables:

```scss
// styles.scss

// Include any default variable overrides here (functions won't be available)

// State colors
$primary: #7464a1;
$secondary: #64a19d;
$success: #67c29c;
$info: #1cabc4;
$warning: #e4c662;
$danger: #a16468;
$light: #f8f9fa;
$dark: #343a40;

// Bootstrap color map
$theme-colors: (
    "primary": $primary,
    "secondary": $secondary,
    "success": $success,
    "info": $info,
    "warning": $warning,
    "danger": $danger,
    "light": $light,
    "dark": $dark
);

$font-family-base: (
    "Nunito",
    -apple-system,
    BlinkMacSystemFont,
    "Segoe UI",
    Roboto,
    "Helvetica Neue",
    Arial,
    sans-serif,
    "Apple Color Emoji",
    "Segoe UI Emoji",
    "Segoe UI Symbol",
    "Noto Color Emoji"
);

// Include all of Bootstrap

@import "../node_modules/bootstrap/scss/bootstrap";
```

The most common use case for `styles.scss` is to define a custom color scheme and fonts, but it's also possible to customize some other visual details such as border radius and box shadow depth. See the [Bootstrap Sass customization documentation](https://getbootstrap.com/docs/5.3/customize/sass/) and the many free templates available at [Start Bootstrap](https://startbootstrap.com) for examples.

#### Compiling the SCSS to CSS

To compile the SCSS files to CSS, we use `gulp`. In the project root directory, create a `gulpfile.js` file with the following content:

```javascript
   const gulp = require('gulp');
   const sass = require('gulp-sass')(require('sass'));

   // Define a task to compile Sass
   gulp.task('sass', function() {
       return gulp.src('scss/**/*.scss') // Source folder containing Sass files
           .pipe(sass().on('error', sass.logError))
           .pipe(gulp.dest('static/css')); // Destination folder for compiled CSS
   });

   // Define a default task
   gulp.task('default', gulp.series('sass'));
```

To compile the SCSS file to `static/css`, run this command:

```bash
npx gulp
```

Note that this will overwrite the existing `static/css/styles.css` file, so if you want to define any custom CSS styles, you should do so in either the `scss/styles.scss` file or in `static/css/extras.css`.

### Client-side form validation

While this template includes comprehensive server-side validation through Pydantic models and custom validators, it's important to note that server-side validation should be treated as a fallback security measure. If users ever see the `validation_error.html` template, it indicates that our client-side validation has failed to catch invalid input before it reaches the server.

Best practices dictate implementing thorough client-side validation via JavaScript and/or HTML `input` element `pattern` attributes to:

- Provide immediate feedback to users
- Reduce server load
- Improve user experience by avoiding round-trips to the server
- Prevent malformed data from ever reaching the backend

Server-side validation remains essential as a security measure against malicious requests that bypass client-side validation, but it should rarely be encountered during normal user interaction. See `templates/authentication/register.html` for a client-side form validation example involving both JavaScript and HTML regex `pattern` matching.

# Deployment

This application requires two services to be deployed and connected to each other:

1. A PostgreSQL database (the storage layer)
2. A FastAPI app (the application layer)

There are *many* hosting options available for each of these services; this guide will cover only a few of them.

## Deploying and Configuring the PostgreSQL Database

### On Digital Ocean

#### Getting Started

- Create a [DigitalOcean](mdc:https:/www.digitalocean.com) account
- Install the [`doctl` CLI tool](mdc:https:/docs.digitalocean.com/reference/doctl) and authenticate with `doctl auth init`
- Install the [`psql` client](mdc:https:/www.postgresql.org/download)

#### Create a Project

Create a new project to organize your resources:

```bash
# List existing projects
doctl projects list

# Create a new project
doctl projects create --name "YOUR-PROJECT-NAME" --purpose "YOUR-PROJECT-PURPOSE" --environment "Production"
```

#### Set Up a Managed PostgreSQL Database

Create a managed, serverless PostgreSQL database instance:

```bash
doctl databases create your-db-name --engine pg --version 17 --size db-s-1vcpu-1gb --num-nodes 1 --wait
```

Get the database ID from the output of the create command and use it to retrieve the database connection details:

```bash
# Get the database connection details
doctl databases connection "your-database-id" --format Host,Port,User,Password,Database
```

Store these details securely in a `.env.production` file (you will need to set them later in application deployment as production secrets):

```bash
# Database connection parameters
DB_HOST=your-host
DB_PORT=your-port
DB_USER=your-user
DB_PASS=your-password
DB_NAME=your-database
```

You may also want to save your database id, although you can always find it again later by listing your databases with `doctl databases list`.

#### Setting Up a Firewall Rule (after Deploying Your Application Layer)

Note that by default your database is publicly accessible from the Internet, so you should create a firewall rule to restrict access to only your application's IP address once you have deployed the application. The command to do this is:

```bash
doctl databases firewalls append <database-cluster-id> --rule <type>:<value>
```

where `<type>` is `ip_addr` and `<value>` is the IP address of the application server. See the [DigitalOcean documentation](https://docs.digitalocean.com/reference/doctl/reference/databases/firewalls/append/) for more details.

**Note:** You can only complete this step after you have deployed your application layer and obtained a static IP address for the application server.

## Deploying and Configuring the FastAPI App

### On Modal.com

The big advantages of deploying on Modal.com are:
1. that they offer $30/month of free credits for each user, plus generous additional free credit allotments for startups and researchers, and
2. that it's a very user-friendly platform.

The disadvantages are:
1. that Modal is a Python-only platform and cannot run the database layer, so you'll have to deploy that somewhere else,
2. that you'll need to make some modest changes to the codebase to get it to work on Modal, and
3. that Modal offers a [static IP address for the application server](https://modal.com/docs/guide/proxy-ips) only if you pay for a higher-tier plan starting at $250/year, which makes securing the database layer with a firewall rule cost prohibitive.

#### Getting Started

- [Sign up for a Modal.com account](https://modal.com/signup)
- Install modal in the project directory with `uv add modal`
- Run `uv run modal setup` to authenticate with Modal

#### Defining the Modal Image and App

Create a new Python file in the root of your project, for example, `deploy.py`. This file will define the Modal Image and the ASGI app deployment.

1.  **Define the Modal Image in `deploy.py`:**
    - Use `modal.Image` to define the container environment. Chain methods to install dependencies and add code/files.
    - Start with a Debian base image matching your Python version (e.g., 3.13).
    - Install necessary system packages (`libpq-dev` for `psycopg2`, `libwebp-dev` for Pillow WebP support).
    - Install Python dependencies using `run_commands` with `uv`.
    - Add your local Python modules (`routers`, `utils`, `exceptions`) using `add_local_python_source`.
    - Add the `static` and `templates` directories using `add_local_dir`. The default behaviour (copying on container startup) is usually fine for development, but consider `copy=True` for production stability if these files are large or rarely change.

    ```python
    # deploy.py
    import modal
    import os

    # Define the base image
    image = (
        modal.Image.debian_slim(python_version="3.13")
        .apt_install("libpq-dev", "libwebp-dev")
        .pip_install_from_pyproject("pyproject.toml")
        .add_local_python_source("main")
        .add_local_python_source("routers")
        .add_local_python_source("utils")
        .add_local_python_source("exceptions")
        .add_local_dir("static", remote_path="/root/static")
        .add_local_dir("templates", remote_path="/root/templates")
    )

    # Define the Modal App
    app = modal.App(
        name="your-app-name",
        image=image,
        secrets=[modal.Secret.from_name("your-app-name-secret")]
    )
    ```

2.  **Define the ASGI App Function in `deploy.py`:**
    - Create a function decorated with `@app.function()` and `@modal.asgi_app()`.
    - Inside this function, import your FastAPI application instance from `main.py`.
    - Return the FastAPI app instance.
    - Use `@modal.concurrent()` to allow the container to handle multiple requests concurrently.

    ```python
    # deploy.py (continued)

    # Define the ASGI app function
    @app.function(
        allow_concurrent_inputs=100 # Adjust concurrency as needed
    )
    @modal.asgi_app()
    def fastapi_app():
        # Important: Import the app *inside* the function
        # This ensures it runs within the Modal container environment
        # and has access to the installed packages and secrets.
        # It also ensures the lifespan function (db setup) runs correctly
        # with the environment variables provided by the Modal Secret.
        from main import app as web_app

        return web_app
    ```

For more information on Modal FastAPI images and applications, see [this guide](https://modal.com/docs/guide/webhooks#how-do-web-endpoints-run-in-the-cloud).

#### Deploying the App

From your terminal, in the root directory of your project, run:

```bash
modal deploy deploy.py
```

Modal will build the image (if it hasn't been built before or if dependencies changed) and deploy the ASGI app. It will output a public URL (e.g., `https://your-username--your-app-name.modal.run`).

#### Setting Up Modal Secrets

The application relies on environment variables stored in `.env` (like `SECRET_KEY`, `DB_USER`, `DB_PASSWORD`, `DB_HOST`, `DB_PORT`, `DB_NAME`, `RESEND_API_KEY`, `BASE_URL`). These sensitive values should be stored securely using Modal Secrets.

Create a Modal Secret either through the Modal UI or CLI. Note that the name of the secret has to match the secret name you used in the `deploy.py` file, above (e.g., `your-app-name-secret`).

```bash
# Example using CLI
modal secret create your-app-name-secret \
    SECRET_KEY='your_actual_secret_key' \
    DB_USER='your_db_user' \
    DB_PASSWORD='your_db_password' \
    DB_HOST='your_external_db_host' \
    DB_PORT='your_db_port' \
    DB_NAME='your_db_name' \
    RESEND_API_KEY='your_resend_api_key' \
    BASE_URL='https://your-username--your-app-name-serve.modal.run'
```

**Important:** Ensure `DB_HOST` points to your *cloud* database host address, not `localhost` or `host.docker.internal`.

#### Testing the Deployment

Access the provided Modal URL in your browser. Browse the site and test the registration and password reset features to ensure database and Resend connections work.

# Contributing

## Contributors

### Opening issues and bug reports

When opening a new issue or submitting a bug report, please include:

1. A clear, descriptive title
2. For bug reports:
   - Description of the expected behavior
   - Description of the actual behavior
   - Steps to reproduce the issue
   - Version information (OS, Python version, package version)
   - Any relevant error messages or screenshots
3. For feature requests:
   - Description of the proposed feature
   - Use case or motivation for the feature
   - Any implementation suggestions (optional)

Labels help categorize issues:
- Use `bug` for reporting problems
- Use `enhancement` for feature requests
- Use `documentation` for documentation improvements
- Use `question` for general queries

### Contributing code

To contribute code to the project:

1. Fork the repository and clone your fork locally
2. Create a new branch from `main` with a descriptive name
3. Review the [customization](https://promptlytechnologies.com/fastapi-jinja2-postgres-webapp/customization.html), [architecture](https://promptlytechnologies.com/fastapi-jinja2-postgres-webapp/architecture.html), and [authentication](https://promptlytechnologies.com/fastapi-jinja2-postgres-webapp/authentication.html) pages for guidance on design patterns and code structure and style
4. Ensure all tests pass, including `mypy` type checking
5. Stage, commit, and push your changes to the branch:
   - Use clear, descriptive commit messages
   - Keep commits focused and atomic
6. Submit your pull request:
   - Provide a clear description of the changes
   - Link to any related issues

### Rendering the documentation

The README and documentation website are rendered with [Quarto](https://quarto.org/docs/). If you make changes to the `.qmd` files in the root folder and the `docs` folder, you will need to re-render the docs with Quarto.

Quarto expects environment variables to be set in a file called `_environment`, so before running Quarto render commands, you should copy your `.env` file to `_environment`.

``` bash
# To copy the .env file to _environment
cp .env _environment
# To render the documentation website
uv run quarto render
# To render the README
uv run quarto render index.qmd --output-dir . --output README.md --to gfm
```

Due to a quirk of Quarto, an unnecessary `index.html` file is created in the root folder when the README is rendered. This file can be safely deleted.

Note that even if your pull request is merged, your changes will not be reflected on the live website until a maintainer republishes the docs.

## Maintainers

### Git flow

When creating new features,

1. Open a Github issue with the label `feature` and assign it to yourself.
2. Create a new branch from the issue sidebar.
3. Follow the instructions in the popup to check out the branch locally and make your changes on the branch.
4. Commit your changes and push to the branch.
5. When you are ready to merge, open a pull request from the branch to main.
6. Assign someone else for code review.

### Publishing the documentation

To publish the documentation to GitHub Pages, run the following command:

``` bash
uv run quarto publish gh-pages
```