# FastAPI + Python + PostgreSQL (Async SQLAlchemy & Pydantic v2) — AI Agent Guidelines & Architecture Rules

> Production guidelines for FastAPI, Pydantic v2 validation, Async SQLAlchemy 2.0 (asyncpg), Alembic migrations, and PostgreSQL/Redis.
> Technologies: FastAPI, Python, PostgreSQL, SQLAlchemy, Alembic, Pydantic, Redis

---

## AGENTS.md
```markdown
# Project Architecture & Guidelines (FastAPI + Async SQLAlchemy + PostgreSQL)

## 1. System Architecture
- **Framework**: FastAPI (ASGI with Uvicorn / Gunicorn).
- **Data Validation**: Pydantic v2 (`BaseModel`, `Field`, `ConfigDict`).
- **Database & ORM**: PostgreSQL via Async SQLAlchemy 2.0 (`asyncpg` driver).
- **Schema Migrations**: Alembic (`alembic revision --autogenerate`, `alembic upgrade head`).

## 2. Async Connection Pool & Session Management (Critical)
- Configure `create_async_engine` with explicit pool settings:
  ```python
  from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker, AsyncSession

  engine = create_async_engine(
      settings.DATABASE_URL,
      pool_size=10,
      max_overflow=20,
      pool_timeout=30,
      pool_pre_ping=True, # Validates stale connections before use
  )

  async_session = async_sessionmaker(
      engine,
      class_=AsyncSession,
      expire_on_commit=False,
  )
  ```
- Dependency Injection: Always yield database sessions in FastAPI route handlers:
  ```python
  async def get_db() -> AsyncGenerator[AsyncSession, None]:
      async with async_session() as session:
          try:
              yield session
          except Exception:
              await session.rollback()
              raise
  ```

## 3. SQLAlchemy 2.0 Syntax & Relational Queries
- Use modern 2.0 `select()` syntax with `scalars().all()` or `scalar_one_or_none()`. Do NOT use legacy 1.4 `session.query()`.
- To prevent async lazy-loading errors (`MissingGreenlet`), always use `selectinload()` or `joinedload()` for relations.

## 4. Input Validation & Error Handling
- Define separate Pydantic schemas for `Create`, `Update`, and `Response` objects.
- Set `response_model` on all FastAPI router decorators for automatic response serialization.
- Use custom `HTTPException` with standardized JSON error bodies.

## 5. Common Pitfalls to Avoid
- ❌ Mixing sync database drivers with async event loops (always use `postgresql+asyncpg://`).
- ❌ Lazy loading outside greenlet: Always eager load relations in async SQLAlchemy.
- ❌ Blocking operations in `async def` endpoints: Use `run_in_threadpool` or background Celery tasks for heavy CPU workloads.

## 6. Testing Conventions
- Use `pytest` with `pytest-asyncio` (`asyncio_mode = "auto"`) and `httpx.AsyncClient` for testing FastAPI routes end-to-end.
- Override the `get_db` dependency with a transactional test session that rolls back after every test — never let tests commit against the real database.
- Test Pydantic schema validation boundaries explicitly (missing required fields, wrong types, out-of-range values) since these are the first line of defense against bad input.
- Run `mypy .` in CI alongside `pytest`; async SQLAlchemy's `MissingGreenlet` errors are far easier to catch statically than at runtime.

## 7. Git Workflow & PR Conventions
- Conventional Commits (`feat:`, `fix:`, `refactor:`) scoped to the router or domain module, e.g. `fix(auth): refresh expired JWT correctly`.
- Alembic migrations ship in the same PR as the SQLAlchemy model change that generated them.
- Require `pytest`, `ruff check .`, and `alembic upgrade head --sql` (dry-run) to pass before merge.
- Rebase feature branches onto `main`; never merge with unresolved Alembic branch-point conflicts.
```

---

## CLAUDE.md
```markdown
# CLAUDE.md — FastAPI + PostgreSQL (Async)

Refer to @AGENTS.md for async database session management and Pydantic v2 guidelines.

## Common Commands
- `uvicorn app.main:app --reload --port 8000` - Run development server
- `pytest -v` - Run test suite with pytest-asyncio
- `alembic revision --autogenerate -m "description"` - Generate DB migration
- `alembic upgrade head` - Apply database migrations
- `ruff check .` - Run fast Python linter
- `ruff format .` - Format code

## Claude Specific Directives
- When writing database queries, always use async SQLAlchemy 2.0 `select()` and `session.execute()`.
- Type hints are mandatory on all parameters and return values.
```

---

## .cursor/rules/stack.mdc
```markdown
---
description: FastAPI + Async SQLAlchemy + PostgreSQL architecture rules
globs: ["**/*.py"]
alwaysApply: true
---

# FastAPI + Async SQLAlchemy + PostgreSQL

- FastAPI on Uvicorn/Gunicorn, Pydantic v2 for validation, Async SQLAlchemy 2.0 with `asyncpg`.
- `create_async_engine` with explicit `pool_size`, `max_overflow`, `pool_pre_ping=True`. Always yield sessions via a `get_db` dependency that rolls back on exception.
- Modern SQLAlchemy 2.0 `select()` + `scalars()` syntax only — never legacy `session.query()`.
- Eager-load relations with `selectinload()`/`joinedload()` to avoid `MissingGreenlet` errors from lazy loading outside the async greenlet.
- Separate Pydantic schemas per operation: `Create`, `Update`, `Response`. Set `response_model` on every route.
- `postgresql+asyncpg://` only — never mix a sync driver into an async event loop.
- Offload CPU-heavy work with `run_in_threadpool` or a background task queue; never block an `async def` route handler.
- Type hints mandatory on every parameter and return value.
```

---

## Architecture Overview & Best Practices
## Architecture Overview

Production conventions for high-throughput, async-first Python microservices and AI agent backends using **FastAPI**, **SQLAlchemy 2.0**, and **PostgreSQL**.

### Verified Real-World Adoption

This architecture is deployed in production by AI and data platforms including **Mem0**, **OpenBB**, **Baserow**, and **Open WebUI**.

### Key Architectural Nuances

- **Async Connection Pooling**: Leverages `asyncpg` with `pool_pre_ping=True` for resilient connection lifecycle management.
- **Strict Separation of DTOs**: Pydantic v2 ensures fast serialization and automated OpenAPI documentation.