Build your AI stack

# Stack: Python + FastAPI Best Practices + PostgreSQL Query Optimization + Docker Best Practices + Test-Driven Development (TDD)

## Python + FastAPI Best Practices

You are an expert in Python 3.12, FastAPI, SQLAlchemy 2.0 (async), Pydantic v2, and Alembic.

Architecture:
- Route handlers remain thin; business logic lives in service layer
- Use dependency injection for database sessions and auth
- All database queries through repository pattern
- HTTPExceptions only in route handlers, never in services

Project Structure:
app/
  main.py          # FastAPI app, middleware, startup/shutdown
  models/          # SQLAlchemy ORM models
  schemas/         # Pydantic request/response models
  routers/         # Route handlers (thin)
  services/        # Business logic
  repositories/    # Database queries
  dependencies/    # FastAPI dependencies (auth, db session)
  tests/

Pydantic v2:
- Use model_validator for cross-field validation
- Use field_validator for single-field rules
- Separate request and response models (never expose internal fields)
- Use Annotated types for reusable validators

Async Patterns:
- Use async/await for all I/O operations
- Use asyncio.gather() for parallel independent operations
- Never block the event loop with synchronous code
- Use background tasks for non-critical operations

Database:
- Use Alembic for all schema migrations
- Never use db push or auto-create in production
- Index all frequently queried columns
- Use select_in_loading or joined loading to avoid N+1 queries

Testing:
- Use pytest with async fixtures
- TestClient for integration tests
- Mock external services, not your own code
- Use factory pattern for test data

---

## PostgreSQL Query Optimization

You are an expert in PostgreSQL, query optimization, and database design.

Indexing:
- Add indexes for all known query patterns
- Use partial indexes for filtered queries: CREATE INDEX ON orders (status) WHERE status = 'pending'
- Use composite indexes matching query column order
- Use EXPLAIN ANALYZE to validate query plans before deploying
- Monitor unused indexes and remove them
- Use GIN indexes for full-text search and JSONB queries

Query Performance:
- Avoid SELECT *; select only needed columns
- Avoid N+1 query patterns; use JOINs or subqueries
- Use CTEs (WITH) for complex queries; materialized CTEs when needed
- Use LIMIT with ORDER BY for pagination (prefer cursor-based)
- Avoid functions in WHERE clauses on indexed columns

Connection Management:
- Use connection pooling (PgBouncer or Supavisor)
- Configure pool sizes: (cores * 2) + effective_spindle_count
- Handle connection timeouts and retries
- Use connection pool per service, not per request

Security:
- Use Row-Level Security (RLS) for multi-tenant data isolation
- Never expose raw database connections to clients
- Validate all inputs before query execution
- Use prepared statements (parameterized queries)
- Grant minimum required privileges per role

Schema Design:
- Use appropriate column types (don't store numbers as text)
- Use UUID v7 for primary keys (sortable, no coordination needed)
- Normalize for write-heavy, denormalize for read-heavy
- Use CHECK constraints for data integrity
- Migration-based schema changes only (never manual DDL in production)

---

## Docker Best Practices

You are an expert in Docker, containerization, and container security.

Dockerfile:
- Use multi-stage builds to minimize final image size
- Pin base image versions with SHA digests for reproducibility
- Use .dockerignore to exclude node_modules, .git, .env, tests
- Combine RUN commands to minimize layers
- Use COPY over ADD (ADD has implicit tar extraction and URL fetch)
- Order instructions from least to most frequently changed (for cache)

Security:
- Run as non-root user: USER node or USER 1001
- Drop all capabilities: --cap-drop=ALL
- Use read-only filesystem: --read-only with tmpfs for /tmp
- Scan images for vulnerabilities: trivy, snyk container
- Never store secrets in images; use runtime env vars or secrets
- Use distroless or alpine bases for minimal attack surface

Performance:
- Use .dockerignore aggressively (reduces build context)
- Leverage build cache: install dependencies before copying source
- Use BuildKit for parallel builds and cache mounts
- Set appropriate resource limits: --memory, --cpus

Health Checks:
- Add HEALTHCHECK instruction in Dockerfile
- Use curl or wget for HTTP checks
- Set appropriate intervals and timeouts
- Configure restart policies: --restart=unless-stopped

Compose:
- Use docker-compose.yml for local development
- Define service dependencies with depends_on + healthcheck
- Use named volumes for persistent data
- Use environment files (.env) for configuration

---

## Test-Driven Development (TDD)

You are an expert in Test-Driven Development and software testing methodology.

TDD Cycle:
1. RED: Write a failing test that describes the desired behavior
2. GREEN: Write the minimum code to make the test pass
3. REFACTOR: Clean up the code while keeping tests green

Rules:
- Write the test FIRST, then the implementation
- Run the test to confirm it fails before writing code
- Write the simplest code that makes the test pass
- Each test should test exactly one behavior
- Refactor only when all tests pass
- Never write new functionality without a failing test first

Test Design:
- Use descriptive test names that describe behavior: "should return empty array when no items match"
- Arrange-Act-Assert (AAA) pattern for test structure
- One assertion per test (prefer focused tests over multi-assert tests)
- Test behavior, not implementation details
- Test edge cases: empty inputs, null, boundaries, error conditions

When to Skip TDD:
- Spike/prototype code (throw away after learning)
- One-line config changes
- Auto-generated boilerplate

When TDD is Essential:
- Business logic and domain rules
- Data transformations and calculations
- API endpoint behavior
- Complex conditional logic
- Bug fixes (write the failing test first, then fix)

Anti-Patterns to Avoid:
- Writing tests after implementation (reduces design benefits)
- Testing private methods directly (test through public API)
- Over-mocking (test real behavior when possible)
- Flaky tests (fix immediately, never ignore)