CLAUDE.md Template: FastAPI + Turso LibSQL + HttpOnly Cookies + Embedded SQLAlchemy Core

Overview

Direct answer: This CLAUDE.md template provides a production-ready blueprint for implementing a FastAPI service backed by Turso LibSQL using embedded SQLAlchemy Core, with authentication via custom HttpOnly cookies.

The stack covered by this CLAUDE.md Template includes FastAPI for the API layer, Turso LibSQL as the serverless database, HttpOnly cookies for secure session handling, and embedded SQLAlchemy Core for SQL construction and execution without ORM.


When to Use This CLAUDE.md Template



  - You are building a Python API with FastAPI that runs on the edge via Turso LibSQL (LibSQL backend).

  - You need to manage user sessions with HttpOnly cookies to minimize XSS risks.

  - You prefer SQLAlchemy Core for explicit SQL construction instead of an ORM.

  - You want a copyable CLAUDE.md template that can be pasted directly into Claude Code with stack-specific constraints.


Copyable CLAUDE.md Template


Copy the block below into CLAUDE.md. It enforces the stack constraints and provides pragmatic rules you can drop into a Claude Code workspace.


# CLAUDE.md
Project role: You are a Senior FastAPI Engineer delivering a production-ready API using Turso LibSQL (LibSQL) with Embedded SQLAlchemy Core and custom HttpOnly cookies for auth. Your Claude Code outputs must be copy-pasteable into CLAUDE.md.

Architecture rules:
- Use FastAPI as the web framework
- Use Turso LibSQL as the database
- Use SQLAlchemy Core (not ORM) for all SQL expressions
- Use custom HttpOnly cookies for authentication
- All inputs/outputs validated with Pydantic
- The app runs behind Uvicorn and is structured for deployment

File structure rules:
- Place code under app/, db/, auth/, and tests/ directories
- Do not create unrelated folders
- Place config in config/

Authentication rules:
- Implement login at /auth/login that issues a HttpOnly cookie named session_id
- The session_id cookie must be HttpOnly, Secure (in prod), and SameSite=Strict
- Protected endpoints require the session_id cookie
- Do not rely on localStorage or non HttpOnly storage for tokens

Database rules:
- Connect to Turso LibSQL with a LibSQL-compatible URL/DSN
- Use SQLAlchemy Core for queries and DDL via a single engine
- Do not use ORM models; use Table objects and select/insert/update
- Use parameterized queries to prevent SQL injection

Validation rules:
- Use Pydantic models for request/response bodies
- Validate all path/query/body params
- Return 400 on validation errors

Security rules:
- Do not log secrets; restrict logs to non-sensitive data
- Enforce TLS in production
- Do not expose DB credentials in code or logs
- Ensure HttpOnly cookies are consistently used for sessions

Testing rules:
- Unit tests for pure functions
- Integration tests for API endpoints with FastAPI TestClient
- Mock Turso LibSQL during tests
- Include tests for login/logout cookie behavior

Deployment rules:
- Run with uvicorn main:app --host 0.0.0.0 --port ${PORT:-8000} in production
- Provide Dockerfile and minimal docker-compose for local dev
- Use environment variables for secrets; never hard-code them
- Health check endpoint included

Things Claude must not do:
- Do not access secrets from environment without explicit permission
- Do not bypass authentication or escalate privileges
- Do not perform destructive DB actions without confirmation
- Do not switch to non-LibSQL in a way that breaks compatibility

Recommended Project Structure


fastapi-turso-libsql-claude-md/
├── app/
│   ├── main.py
│   ├── api/
│   │   ├── endpoints/
│   │   │   └── items.py
│   │   └── dependencies.py
│   ├── db/
│   │   ├── engine.py
│   │   ├── turso.py
│   │   └── core.py
│   ├── auth/
│   │   └── cookies.py
│   ├── schemas/
│   │   └── user.py
│   └── __init__.py
├── tests/
│   ├── test_endpoints.py
│   └── test_auth.py
├── Dockerfile
├── docker-compose.yml
├── pyproject.toml
├── README.md
└── claude.md

Core Engineering Principles



  - Explicit Claude Code constraints for reproducible templates

  - Security by design with HttpOnly cookies and TLS

  - Validation and typing for all inputs and outputs

  - Separation of concerns: API, DB access, and auth are distinct modules

  - Testability: unit and integration tests with mocks for Turso LibSQL

  - Deployability: CI-ready, environment-based configs, and minimal dependencies


Code Construction Rules



  - Prefer SQLAlchemy Core for raw SQL composition; avoid ORM mappings

  - All SQL must be parameterized; never interpolate user input into SQL strings

  - Auth logic must be isolated in a dedicated module with clear interfaces

  - Endpoints must declare Pydantic models for requests and responses

  - DB access must use a single Engine instance per app lifecycle

  - Do not mix synchronous DB calls on the async path without proper executors


Security and Production Rules



  - HttpOnly cookies must be used for all sessions

  - Cookies should be Secure in production and SameSite=Strict

  - Never log secrets or DB credentials

  - Enable TLS termination and HTTP Strict Transport Security (HSTS) in prod

  - Validate all inputs; enforce strict content types and CORS policies


Testing Checklist



  - Unit tests for pure functions and utilities

  - Integration tests for endpoints with TestClient

  - Mock Turso LibSQL interactions in tests

  - End-to-end tests for login/logout cookie flow

  - CI runs linting, tests, and basic security checks


Common Mistakes to Avoid



  - Storing tokens in localStorage or non HttpOnly cookies

  - Using ORM features when Core is required by the template

  - Ignoring TLS, secrets handling, or insecure defaults in production

  - Hard-coding DB credentials or secrets in code


FAQ



  What is included in this CLAUDE.md Template for FastAPI + Turso LibSQL?
  A copyable Claude Code blueprint for building a FastAPI service with Turso LibSQL (LibSQL), using embedded SQLAlchemy Core and HttpOnly cookies for authentication.
  Why use HttpOnly cookies in this stack?
  HttpOnly cookies protect session tokens from client-side access, reducing XSS risks.
  Should I use SQLAlchemy Core or ORM?
  This template requires SQLAlchemy Core for explicit SQL construction without ORM abstractions.
  How do I test this stack?
  Unit tests for functions and integration tests using FastAPI TestClient; mock Turso LibSQL during tests.
  How do I migrate to production securely?
  Use TLS, secure cookies, environment-based config, and CI that runs tests and security checks.