## RAG: Citations Source: https://llmbestpractices.com/ai-agents/rag-citations Last updated: 2026-10-01 ## Overview Citations turn a RAG answer from a claim into a checkable claim. With Claude, use the API's native citations so the pointers are parsed and validated by the API; with other models, tag chunks with IDs and validate the markers yourself. Either way, cite every fact and render the sources as first-class UI. For the chunks that feed this step see [[ai-agents/rag-retrieval]] and [[ai-agents/rag-chunking]]. ## Use native citations on Claude Citations are generally available on the Claude API (no beta header) and supported by all active models; search results support all active models except Claude Haiku 3. Set `citations: {"enabled": true}` on each `search_result` or `document` block. Claude then returns text blocks with a `citations` list, and `cited_text` does not count toward output tokens. Return retrieved chunks as `search_result` blocks inside a tool result, or place them as top-level user content: ```python {"type": "tool_result", "tool_use_id": tool_use.id, "content": [ {"type": "search_result", "source": c.url, "title": c.title, "content": [{"type": "text", "text": c.text}], "citations": {"enabled": True}} for c in chunks ]} ``` Each citation is a `search_result_location` with `search_result_index`, `start_block_index`, `end_block_index`, and `cited_text`. The text block is the smallest citable unit, so split a chunk into several `content` blocks for finer citations. For whole documents, use `document` blocks with `citations` enabled: | Source type | Chunking | Citation points to | |---|---|---| | Plain text | sentences | `char_location`, 0-indexed character range | | PDF | sentences | `page_location`, 1-indexed page range | | Custom content | none (your blocks) | `content_block_location`, 0-indexed block range | To let Claude cite single sentences of a retrieved chunk, pass the chunk as a plain-text document. To control granularity yourself, pass custom content blocks. ## Respect the native-citation constraints - Citations are all or nothing within a request: every document, or every search result, must have them enabled or all must have them disabled. - Citations are incompatible with structured outputs. Enabling both (`output_config.format`, or the deprecated `output_format`) returns a 400 error. Pick one: native citations, or a JSON schema. - Within one `tool_result`, if any block is a `search_result`, every block must be. - Citations work with prompt caching; put `cache_control` on the top-level document blocks. The citation blocks in responses cannot be cached directly. See [[prompt-engineering/prompt-caching-strategies]]. - Enabling citations adds a small number of input tokens for system prompt additions and document chunking. ## Fall back to chunk IDs when native citations do not fit Use this path for models without native citations, or when you need a custom JSON schema from Claude. Give every retrieved chunk a stable ID and require the model to cite it. ```text Context: [1] (source: postgres-17-release-notes.md#jit) ... [2] (source: backend/postgres.md#tuning) ... Answer with inline citations like [1] or [2] for each factual claim. If the context does not answer the question, say so. Do not guess. ``` Map each ID back to the chunk's metadata (`source_url`, `heading_path`, `chunk_index`). For a JSON schema, return `{"answer": "...", "citations": [{"chunk_id": "abc123", "supports": "..."}]}` through structured outputs (see [[ai-agents/structured-output]]). ## Cite every fact If a claim cannot be cited, it should not be made. Every factual sentence ends with at least one citation; opinions and synthesis cite the chunks that ground them. Only meta-text ("Here is what I found") is exempt. In a domain agent, "common knowledge" is still a retrieval target. ## Validate prompt-based citations after generation A model can invent a `[4]` that does not exist, or cite `[1]` for a claim `[1]` does not support. Strip any marker whose ID is not in the retrieved set, score faithfulness (did the cited chunk contain the claim?) with a judge or an overlap check, and retry or return a fallback when it fails. Native citations guarantee valid pointers to your documents, but whether the cited text supports the claim still belongs in the eval. See [[ai-agents/rag-eval]]. ## Build verifiable links Give each chunk a deep link in its metadata: `source_url#heading-anchor`, a line range for code, a page number for PDFs. A bare `[1]` forces the user to hunt for the passage. ## Surface sources as first-class UI Render each citation as a clickable chip (`title - heading - last_updated`). Show the cited text on hover so users see what the model saw, and open the deep link on click. When retrieval finds nothing, instruct the model to say it has no source, render that as an empty state with a "show what was retrieved" link, and log every refusal; a spike means retrieval changed. ## Score citation behavior Track three numbers per release: coverage (share of factual sentences with a citation), validity (share of markers or pointers that resolve to retrieved content), and faithfulness (share of citations whose source supports the claim). A change that lifts answer relevance while faithfulness drops is a regression. ## Related - [[ai-agents/rag]] - [[ai-agents/rag-retrieval]] - [[ai-agents/rag-chunking]] - [[ai-agents/rag-eval]] - [[ai-agents/structured-output]] - [[prompt-engineering/prompt-caching-strategies]]
## RAG: Evaluation Source: https://llmbestpractices.com/ai-agents/rag-eval Last updated: 2026-10-01 ## Overview A single end-to-end accuracy number hides which half of a RAG system is broken. Score retrieval (did the right chunks come back?) and generation (did the answer use them correctly?) separately, on a fixed [[glossary/golden-set|golden set]], and rerun the suite on every change. For the general agent eval pattern, see [[ai-agents/evaluation]]. ## Build the golden set from real failures - Each row: `question`, `gold_chunk_ids[]`, `gold_answer`, `slice_tag`. - Start at 50 rows; grow toward 200 to 500 when you need to detect small differences between models. A 1-point recall gap on 200 queries is probably noise, so read the disagreements instead of trusting the aggregate. - Sample queries from real logs. Include short queries, multi-hop questions, exact identifiers, rare terms, and time-sensitive questions. - Version the set like code. Never edit a row to make a failing test pass. ## Score retrieval with recall, MRR, and nDCG - `recall@k`: the fraction of gold chunks that appear in the top k. Track k = 5, 10, and 50. Use `hit@k` (at least one gold chunk in the top k) when one chunk suffices. - `MRR@k`: the mean of 1 / rank of the first gold chunk. Use `MRR@5` when the prompt only sees five chunks. - `nDCG@k`: use when chunks have graded relevance instead of binary. ```python def recall_at_k(retrieved, gold, k): return len(set(retrieved[:k]) & gold) / len(gold) def mrr_at_k(retrieved, gold, k): return next((1 / r for r, c in enumerate(retrieved[:k], 1) if c in gold), 0.0) ``` Read the gap between depths. High `recall@50` with low `recall@5` means the reranker is the lever (see [[ai-agents/rag-reranking]]). Low `recall@50` means the candidate pool is missing the gold chunks, so fix chunking, the embedding model, or hybrid search first; a reranker cannot recover chunks that were never retrieved. ## Score generation with faithfulness and answer relevance - Faithfulness: is every claim in the answer supported by the retrieved context? Score per claim with a judge model or a RAGAS-style rubric. - Answer relevance: does the answer respond to the question? An on-topic but evasive answer fails. Faithfulness without relevance ships safe, useless answers; relevance without faithfulness ships confident hallucinations. Score citation validity and coverage too (see [[ai-agents/rag-citations]]). ## Audit the judge Judge models drift. Use a rubric with yes/no questions, not a vibe score, and prefer a stronger judge than the model under test. Each release, have humans rate 30 to 50 sampled rows and compare; when agreement is low, rewrite the rubric or retire the judge. See [[ai-agents/evaluation]] for the harness. ## A/B embedding models and index settings on one golden set When comparing models, dimensions, or index parameters, hold everything else fixed: same golden set, same chunks, same k, same query preprocessing. Change one variable at a time. 1. Embed the corpus and queries with each candidate (same dimension unless dimension is the variable). 2. Build identical indexes. 3. Compute `recall@10`, `MRR@10`, and latency per candidate, plus per-slice deltas. 4. Inspect the queries where the candidates disagree. See [[ai-agents/embeddings]] for the models and [[ai-agents/embeddings-dimensionality]] for the dimension procedure. ## Track slices, not just the aggregate An average hides regressions on small slices. A change that is 2 points better overall but 10 points worse on `adversarial` is a regression. Tag rows (`easy`, `edge`, `adversarial`, `time-sensitive`, `multi-hop`, `keyword-heavy`) and report `recall@5`, faithfulness, answer relevance, p95 latency, and cost per query per slice. ## Gate CI on the suite ```text prompt, model, chunker, or index change -> run suite -> diff vs baseline -> block merge when any slice regresses past your tolerance ``` - Run a sample on every PR and the full set nightly. - Cache embeddings and reranker scores where deterministic so the suite stays cheap. - Rerun after every embedding-model change, chunker change, or re-index. ## Triage failures by stage Read ten failed queries each release. - Gold chunk never retrieved: retrieval failure ([[ai-agents/rag-chunking]], [[ai-agents/rag-retrieval]]). - Retrieved but outside the top k: reranker failure. - In the prompt but the answer is wrong: generation or citation failure. ## Related - [[ai-agents/rag]] - [[ai-agents/evaluation]] - [[ai-agents/rag-retrieval]] - [[ai-agents/rag-reranking]] - [[ai-agents/rag-citations]] - [[ai-agents/rag-chunking]] - [[ai-agents/embeddings]] - [[ai-agents/embeddings-dimensionality]]
## RAG: Reranking Source: https://llmbestpractices.com/ai-agents/rag-reranking Last updated: 2026-10-01 ## Overview Embedding retrieval scores query and chunk independently. A cross-encoder reranker reads query and chunk together and scores the pair, which is more accurate and slower, so it runs only on the candidates retrieval returns. The reranker cannot recover a chunk that retrieval missed; see [[ai-agents/rag-retrieval]] for the stage before it. ## Retrieve broad, rerank narrow Return 30 to 100 candidates from retrieval, rerank, and pass the top few chunks to the prompt. ```text query -> dense top 30 + BM25 top 30 -> RRF merge to 50 -> rerank -> top 5 to 20 -> prompt ``` Merge multiple retrievers with RRF before reranking so you do not score the same chunk twice. Skipping the rerank stage is the most common reason a tuned system answers wrong on borderline queries. ## Choose a hosted reranker for the easy path | Reranker | Context | Notes | |---|---|---| | Cohere `rerank-v4.0-pro` / `rerank-v4.0-fast` | 32K | Multilingual; accepts semi-structured (JSON) documents; `-fast` for low latency | | Voyage `rerank-3` / `rerank-3-lite` | 32K, up to 1,000 documents | Pairs with Voyage embeddings; `rerank-2.5` is legacy but still served | | Jina `jina-reranker-v3` | listwise | 0.6B model that scores a batch of documents in one pass; v3.5 is faster | - Cohere `rerank-v3.5` has a 4K context and is superseded by v4.0. Pinecone's hosted inference lists `cohere-rerank-4-fast` and `bge-reranker-v2-m3` and marks `cohere-rerank-3.5` deprecated. - Cohere, Voyage, and Pinecone bill rerankers per request or per token; check each price sheet against your candidate count. - Run a bake-off on your golden set; the right reranker wins there, not on a leaderboard. See [[ai-agents/rag-eval]]. ## Self-host when latency or data residency demands it `BAAI/bge-reranker-v2-m3` (568M parameters, 8K context, Apache 2.0, multilingual) is the open-weight default. Qwen3-Reranker (0.6B, 4B, 8B) is the larger alternative. ```python from FlagEmbedding import FlagReranker reranker = FlagReranker("BAAI/bge-reranker-v2-m3", use_fp16=True) scores = reranker.compute_score([[query, chunk] for chunk in candidates]) ranked = sorted(zip(candidates, scores), key=lambda x: -x[1]) ``` - Pin the model revision in your image. - Batch pairs; unbatched GPU calls waste most of the hardware. - CPU reranking is for prototypes. Self-host when the corpus is sensitive, the latency budget is tight, or volume makes a hosted API uneconomic. ## Budget the reranker on latency Reranking is usually the slowest stage you can still afford. Measure p95 latency at your real candidate count, set a budget, and cut candidates (for example 50 to 25) or move to a smaller reranker before you remove the stage. ## Choose by lift, not by vendor claims - Score `recall@5` and `nDCG@5` on the golden set for each candidate reranker. - Score the same chunks with no reranker as the baseline. - Pick the reranker with the largest lift at acceptable latency and cost. One point of accuracy at four times the cost usually loses. ## Pass the right amount of context Tune the number of reranked chunks on your eval set, not by habit. Anthropic's contextual-retrieval experiments found that passing 20 chunks beat 10 and 5; your corpus may differ. Order chunks by reranker score, trim long chunks to the relevant section, and format them for citation as described in [[ai-agents/rag-citations]]. ## Related - [[ai-agents/rag]] - [[ai-agents/rag-retrieval]] - [[ai-agents/rag-chunking]] - [[ai-agents/rag-eval]] - [[ai-agents/rag-citations]] - [[ai-agents/embeddings]] - [[glossary/reranker]]
## RAG: Retrieval Source: https://llmbestpractices.com/ai-agents/rag-retrieval Last updated: 2026-10-01 ## Overview The generator writes confidently from whatever the retriever hands it, so retrieval sets the ceiling for a RAG system. These rules cover which retrievers to run, how to merge them, how to filter, and how to size the candidate pool. For chunk preparation see [[ai-agents/rag-chunking]]; for the step after retrieval see [[ai-agents/rag-reranking]]. ## Run dense and sparse retrieval in parallel, then fuse Dense embeddings handle paraphrase and meaning. Sparse retrieval (BM25, Postgres full-text, SPLADE) handles exact identifiers, rare names, version numbers, and one-word queries, which dense models fail on predictably (`EADDRINUSE`, `pg_dump --jobs=4`, SKUs). Query both with the same filters and merge the two top-k lists. Do not run them in sequence; a keyword filter after the dense step throws away candidates before the merge. Reciprocal Rank Fusion needs no score calibration. Each document scores `1 / (k + rank)` per list, summed across lists, with rank starting at 1. ```python def rrf(rankings, k=60): scores = {} for ranking in rankings: for rank, doc_id in enumerate(ranking, start=1): scores[doc_id] = scores.get(doc_id, 0) + 1 / (k + rank) return sorted(scores, key=scores.get, reverse=True) ``` - `k = 60` is the common default; lower values weight top ranks more. Qdrant's server-side RRF documents a default of 2, so pass `k` explicitly when you move between systems. - Weaviate hybrid queries default to relative score fusion (v1.24+) and use `alpha` from 0 (keyword only) to 1 (vector only); `rankedFusion` is the RRF-style alternative. - Pinecone supports one index holding dense and sparse vectors with a client-side `alpha`, or separate indexes fused client-side. - Confirm on your golden set that hybrid beats dense alone; the gain concentrates on identifier-heavy queries. See [[ai-agents/rag-eval]]. For the keyword arm inside Postgres, see [[backend/postgres-full-text-search]]. ## Filter on metadata before the vector step Filter by `tenant_id`, `lang`, `doc_type`, and date range inside the query, not on the merged list. Post-filtering a fixed top-k can return nothing when the filter is selective. ```python results = collection.query( query_embeddings=[query_vec], where={"$and": [{"tenant_id": "t_123"}, {"updated_ts": {"$gte": 1767225600}}]}, # 2026-01-01 UTC n_results=50, ) ``` - ChromaDB range operators (`$gt`, `$gte`, `$lt`, `$lte`) take numbers only, so store dates as epoch integers, and combine several conditions with `$and`. - pgvector applies a `WHERE` clause after an approximate index scan. Since 0.8.0, set `hnsw.iterative_scan` to `strict_order` or `relaxed_order` so the scan continues until enough rows pass the filter. - Check how your store filters before you trust a selective filter; see [[ai-agents/rag-vector-databases]]. ## Retrieve broad, rerank narrow Take 20 to 50 candidates from each retriever, merge to about 50, and let a reranker choose the 5 to 20 chunks that enter the prompt. Skipping the reranker stage costs borderline queries; see [[ai-agents/rag-reranking]]. Sweep k against `recall@k` on the golden set and pick the point where recall plateaus; setting k by feel under-retrieves hard queries and overpays on easy ones. ## Route queries that do not need hybrid If logs show many exact-identifier lookups, send those to BM25 alone and skip the embedding call. Send conceptual queries to dense, ambiguous ones to hybrid. Add a router only when the saved embedding calls outweigh its maintenance; for low-volume systems, always run hybrid. ## Expand the query when its wording differs from the corpus Multi-query expansion asks a small fast model (for example `claude-haiku-4-5` or a local model) for 3 to 5 paraphrases, retrieves on each, and merges with RRF. HyDE has the model draft a plausible answer and embeds that instead of the question. - HyDE helps short, vague queries ("what is X"). It hurts long, precise ones ("Postgres 17 JIT compile flags"); skip it there. - Both add a model call to every query. Keep them only where `recall@k` improves on the golden set. ## Diagnose recall before precision When answers are wrong, check `recall@k` first. Low recall points at chunking or the embedding model ([[ai-agents/rag-chunking]], [[ai-agents/embeddings]]). High recall with wrong chunks in the prompt points at the reranker. High recall with the right chunks and a wrong answer points at generation or citations ([[ai-agents/rag-citations]]). Most "the model is hallucinating" reports are retrieval misses. ## Related - [[ai-agents/rag]] - [[ai-agents/rag-chunking]] - [[ai-agents/rag-reranking]] - [[ai-agents/rag-eval]] - [[ai-agents/embeddings]] - [[ai-agents/rag-vector-databases]] - [[backend/postgres-full-text-search]] - [[backend/chromadb]]
## RAG: Vector Databases Source: https://llmbestpractices.com/ai-agents/rag-vector-databases Last updated: 2026-10-01 ## Overview The vector store is a deployment decision more than a quality decision: recall and latency depend more on chunking, the embedding model, and reranking than on the database. Pick the store that fits how the rest of the stack runs, and test its filtering behavior, which is where stores differ most. If you already run a search engine or document store with native vector search (Elasticsearch, OpenSearch, MongoDB Atlas), evaluate it before adding a system. For the Pinecone versus pgvector head-to-head, see [[comparisons/pinecone-vs-pgvector]]. ## Pick Pinecone for managed simplicity Pinecone is the no-ops choice: serverless, no nodes to size. - New indexes default to document indexes, whose schema can combine dense vectors, sparse vectors, and full-text fields, so hybrid search lives in one index. Classic vector indexes (dimension, metric) remain. - Use one namespace per tenant for isolation. Namespace limits depend on the plan (100 on Starter up to 1,000,000 on Enterprise). - Metadata is limited to 40 KB per record and is filterable by default. - Weak fit: steady high QPS where the bill dominates, or strict residency and on-prem requirements. ## Pick Qdrant for filtered search and self-hosting Qdrant is written in Rust and is available self-hosted or as Qdrant Cloud. - Create payload indexes before ingesting data. A payload index adds extra HNSW edges so filters apply during graph traversal; indexes added later require an HNSW rebuild to take effect. - For multi-tenant collections, mark the tenant field `is_tenant` (keyword or uuid type) to co-locate tenant data on disk. - The Query API supports hybrid queries with `prefetch` and RRF or distribution-based score fusion, plus sparse vectors. Qdrant wins when filtered recall under load is the requirement. ## Pick Weaviate for hybrid search as the default query Weaviate ships BM25 plus vector hybrid as a first-class query with `alpha` weighting and relative score or ranked fusion. HNSW is the default index; the flat index suits many small tenants. Modules add embedding providers, rerankers, and generation inside the database, which is convenient for prototypes. See [[ai-agents/rag-retrieval]] for fusion rules. ## Pick pgvector when Postgres is already in the stack pgvector (0.8.6 as of 2026-07) turns the database you already run into a vector store: one backup story, one connection pool, one access-control model. See [[backend/postgres]]. ```sql CREATE INDEX ON docs USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64); SET LOCAL hnsw.ef_search = 100; -- per transaction; default 40 SET hnsw.iterative_scan = relaxed_order; -- 0.8.0+; keeps scanning when a WHERE filter drops rows ``` - `vector` indexes cap at 2,000 dimensions and `halfvec` at 4,000. Shorten the dimension or index a `halfvec` cast for 3072-dimension models. - Without iterative scans, a selective filter applied after the index scan can return fewer than `LIMIT` rows. - Pair with full-text search for the BM25 arm; see [[backend/postgres-full-text-search]]. pgvector is the lowest-friction option when consolidation matters more than peak QPS. Benchmark at your vector count and QPS before committing. ## Pick ChromaDB for local-first dev and small services ChromaDB suits prototypes, notebooks, internal tools, and single-process apps; see [[backend/chromadb]]. Its default distance is `l2`, so set `cosine` or `ip` explicitly (see [[ai-agents/embeddings-dimensionality]]). Move to Qdrant, Weaviate, or pgvector when scale or multi-process access demands it. ## Tune HNSW with three knobs | Knob | Typical range | Effect | |---|---|---| | `m` (graph degree) | 16 default; 32 for higher recall | Bigger graph, more memory | | `ef_construction` | 64 to 200 | Better graph, slower builds | | `ef_search` | 40 to 200 | Higher recall, slower queries | Build once with a high `ef_construction`, sweep `ef_search` per workload, and pick the smallest value that clears the recall bar on your golden set (see [[ai-agents/rag-eval]]). ## Plan the migration before you need it Switching stores costs re-indexing, and switching embedding models costs a re-embed. - Export vectors, IDs, and metadata on a schedule; the export is the migration artifact. - Shadow the new store: write to both, query the old, and diff `recall@k` daily. - Flip traffic when the new store matches the old on the eval suite. ## Related - [[ai-agents/rag]] - [[ai-agents/rag-retrieval]] - [[ai-agents/rag-eval]] - [[ai-agents/embeddings]] - [[ai-agents/embeddings-dimensionality]] - [[backend/chromadb]] - [[backend/postgres]] - [[backend/postgres-full-text-search]] - [[comparisons/pinecone-vs-pgvector]]
## How to build reliable AI agents in production Source: https://llmbestpractices.com/ai-agents/reliable-agents-in-production Last updated: 2026-10-01 ## Overview Reliable agents come from narrowing scope, constraining actions, and instrumenting every step, not from a smarter model: a demo that works once on a happy path fails on the long tail of real traffic. This page is the production checklist behind [[ai-agents/agent-architecture-patterns]]; operations live in [[ops/llmops-best-practices]] and [[ops/llm-observability]]. ## Scope the task as narrowly as it goes Reliability falls as autonomy rises. Give the agent the smallest job with clear success criteria, and use a fixed workflow when you can name the steps. ## Constrain tools and treat their output as untrusted Expose the minimum tool set, validate every argument against a schema and then business rules, require confirmation for destructive actions, and sandbox filesystem and network access. Tool results and retrieved text can carry instructions; see [[ai-agents/prompt-injection-defense]] and [[ai-agents/tool-use-and-function-calling]]. ## Bound every loop Set a hard ceiling on steps, tool calls, wall-clock time, and tokens. A stuck agent should halt and escalate. Detect repeats by comparing the last few tool calls and arguments, and force state through a schema so the controller can see where the agent is. ## Make retries and resumption safe - Retry transient failures (rate limits, overload, timeouts) with exponential backoff and jitter; do not retry invalid requests. - Give every side-effecting tool an idempotency key so a retry cannot create a second ticket or payment. - Checkpoint state after each step so a crash resumes from the last good point instead of restarting. Anthropic's research system resumes from where an error occurred for this reason. - For long-running agents, shift traffic to a new version gradually while the old one keeps running (rainbow deployments), so a deploy does not kill in-flight runs. ## Evaluate before every release Build a golden set of representative tasks, run it on every prompt or model change, and gate the release on per-slice thresholds. Judge open-ended outputs with an LLM grader calibrated against human labels. See [[ai-agents/evaluation]]. ## Observe every step Log each step with a trace ID: prompt, tool calls and results, token counts, latency, and stop reason. Alert on loop-cap hits, tool error rates, and cost spikes. Without per-step traces a production failure cannot be reproduced. ## Degrade gracefully and cap cost Plan for the model being slow, wrong, or down: timeouts, retries, and a fallback ladder from a strong model to a cheaper one to a deterministic default (see [[ai-agents/model-routing]]). Cap spend per request and per user; see [[ai-agents/cost-control]]. ## Verify before launch - The golden set clears the release threshold. - Adversarial and malformed inputs are refused or escalated. - A forced tool failure recovers or halts cleanly. - Traces, cost caps, and alerts fire in a staging run. ## Related - [[ai-agents/agent-architecture-patterns]] - [[ai-agents/tool-use-and-function-calling]] - [[ai-agents/evaluation]] - [[ai-agents/cost-control]] - [[ai-agents/model-routing]] - [[ops/llm-observability]] - [[ops/llmops-best-practices]]
## Role Framing Source: https://llmbestpractices.com/ai-agents/role-framing Last updated: 2026-10-01 ## Overview Role framing tells the model what it is, who it writes for, and how it sounds. Anthropic's guidance is that a role in the system prompt focuses behavior and tone, and that even a single sentence helps. It sits in the Identity block of the system prompt; see [[ai-agents/system-prompts]] and [[glossary/system-prompt]]. ## Name the role, the domain, and the audience Two or three concrete sentences are enough. Skip any of the three and the model guesses. ```text You are a Postgres migration reviewer for a Python service team. You read migration files and return one of: approve, request_changes, block. You write for senior engineers who know SQL. ``` Naming the reader sets depth: "for a backend engineer new to Postgres" and "for a product manager who needs the decision, not the algorithm" produce different answers. An unnamed reader gets an average, generic answer. ## Use roles for voice and scope, not for accuracy A role reliably shifts tone, depth, and what the model treats as in scope. It does not add knowledge: Zheng et al. (Findings of EMNLP 2024) found that personas in system prompts did not improve accuracy on factual questions across four model families. Skip inflated credentials ("15 years of experience"); put the effort into constraints, examples, and evals. Use a strong persona only where voice is the product (brand copy), and re-test factual slices when you add one. ## Keep role, capabilities, and tone in separate lines - Role: "You are a code reviewer." - Capabilities: "You can call `search`, `read`, and `comment`. You cannot write files." - Tone: "Direct. No filler. No emoji." Folding capability into the persona ("a powerful AI that can do anything") invites overreach, and separate lines can be reviewed and changed independently. ## Add explicit calibration and anti-sycophancy rules Assistant models tend to agree with the user and to sound certain. Counter both with rules, not vibes: ```text If you do not know, say so and name what would resolve it. Do not invent function names, file paths, or version numbers. If the user is wrong, say so plainly and explain why. Do not open with praise. ``` Pair the calibration rule with a `confidence` field in the schema for high-stakes calls; see [[ai-agents/structured-output]]. ## Refresh the role in long sessions In long agent loops the role can fade as tool output fills the context. Re-state it where it keeps authority: a mid-conversation system message on models that support it, or a short reminder in the next user turn. Assistant prefill is no longer available on current Claude models, so it cannot be used for this. For coding agents, keep the role in the project's `CLAUDE.md` or brief; see [[ai-agents/claude-code]]. ## Related - [[ai-agents/system-prompts]] - [[prompt-engineering/prompt-design]] - [[ai-agents/few-shot]] - [[ai-agents/evaluation]] - [[glossary/system-prompt]]
## Structured Output: Schemas, Strict Tools, and Validation Source: https://llmbestpractices.com/ai-agents/structured-output Last updated: 2026-10-02 ## Overview Anything a program consumes must be JSON validated against a schema. Anthropic, OpenAI, and Google all offer schema-constrained decoding, which replaces "return JSON in this shape" in a prompt (a hope) with an enforced contract. For prose, length, and refusal rules see [[prompt-engineering/output-constraints]]. ## Define the schema before the prompt The schema is the contract; the prompt is the implementation. Write it first so ambiguity surfaces early. ```json { "type": "object", "required": ["verdict", "reasons"], "additionalProperties": false, "properties": { "verdict": {"enum": ["approve", "request_changes", "block"]}, "reasons": {"type": "array", "items": {"type": "string"}, "description": "1 to 5 reasons, each under 200 characters"} } } ``` Use enums for closed sets and avoid open `{}` objects, which become junk drawers. Bounds the provider cannot enforce go in `description` and in your validator (next sections). ## Use structured outputs or strict tool use - **Anthropic:** `output_config: {format: {type: "json_schema", schema: ...}}` (the SDKs wrap it as `messages.parse()` with Pydantic or Zod), or [[glossary/tool-use|tool use]] with `strict: true`, which guarantees schema-valid tool input; without `strict`, `input_schema` only guides the model. The top-level `output_format` parameter is deprecated. Forced `tool_choice` (`any` or `tool`) returns 400 on Fable 5.1, Opus 5.5, and Sonnet 5.5, and assistant prefill returns 400 on 4.6 and later; use `output_config.format` or `auto` with a strict tool instead of either. - **OpenAI:** `text: {format: {type: "json_schema", name: ..., strict: true, schema: ...}}` on the Responses API (`response_format` on Chat Completions), or function calling with `strict: true`. Strict mode requires `additionalProperties: false` and every field listed in `required`. - **Gemini:** a JSON schema in the generation config; field names differ by SDK, so check the current structured-output docs. ## Stay inside the supported schema subset Each provider enforces a subset of JSON Schema. For Anthropic, as of October 2026: - Not supported: recursive schemas, numeric constraints (`minimum`, `maximum`), string constraints (`minLength`, `maxLength`), `minItems` above 1, external `$ref`, and `additionalProperties` other than `false`. - Limits per request: 20 strict tools, 24 optional parameters, 16 parameters using `anyOf` or type arrays. Exceeding the compiler's complexity budget returns a 400 "Schema is too complex for compilation"; simplify the schema. - The first request with a new schema pays a compile delay; compiled grammars are cached for 24 hours. - Most SDK helpers strip unsupported constraints, add them to field descriptions, and validate the response against your original schema. ## Validate at the boundary Treat the model as an untrusted service and validate every response before downstream code touches it. ```python class Review(BaseModel): verdict: Literal["approve", "request_changes", "block"] reasons: list[str] = Field(min_length=1, max_length=5) # The Python parse() helper takes output_format; on the wire it is output_config.format. response = client.messages.parse( model="claude-sonnet-5-5", max_tokens=2048, messages=[{"role": "user", "content": "Review PR #401"}], output_format=Review, ) review = response.parsed_output ``` Python uses Pydantic; TypeScript uses Zod or Ajv (see [[coding/typescript-runtime-validation]]). Schema compliance can still fail in three ways. A `stop_reason` of `refusal` or `max_tokens` can return output that does not match the schema; check it before parsing. Enum values may differ from the schema only in capitalization, so compare case-insensitively. On validation failure, retry the original call or fall back to a different model; never write unvalidated output to a database. ## Never parse JSON from prose with regex A regex over `{...}` breaks on nested braces, code fences, escaped quotes, and two objects in one reply. If you cannot use a constrained mode, parse with a balanced-brace scanner and keep the last complete value. Prefer switching to a schema-enforced mode. ## Put reasoning in its own field, before the answer When a task needs working-out, use thinking, or a `reasoning` string field that precedes `answer`. Anthropic emits required properties first, in schema order, so mark both required. On Sonnet 5.5, a JSON-only answer to a multi-step task can skip thinking at low or medium effort; use adaptive thinking and see [[prompt-engineering/chain-of-thought]] and [[prompt-engineering/reasoning-model-prompting]]. ## Version the schema like an API Add fields with safe defaults, never repurpose one, rename through a deprecation window (new field, parallel run, retire old), and log the schema version so downstream parsers pick the right decoder. ## Related - [[prompt-engineering/prompt-design]] - [[ai-agents/system-prompts]] - [[prompt-engineering/chain-of-thought]] - [[ai-agents/evaluation]] - [[prompt-engineering/output-constraints]] - [[ai-agents/tool-use-and-function-calling]] - [[prompt-engineering/reasoning-model-prompting]] - [[glossary/guardrails]]
## System Prompts Source: https://llmbestpractices.com/ai-agents/system-prompts Last updated: 2026-10-01 ## Overview The system prompt is the instruction block that persists across every turn; the user message is the per-turn ask. Identity, scope, constraints, and the output contract go in system, which is what keeps an agent consistent across a session. This page covers structure and maintenance; the term is defined in [[glossary/system-prompt]]. ## Put durable rules in the system prompt Test each rule: would you have to repeat it every turn? If yes, it belongs in system. - System: identity, capabilities, constraints, output schema, tool list, voice rules, refusal behavior. - User: the specific task, the input, the per-turn variables. A system prompt that restates the current task wastes budget. A user message that restates the role each turn breaks the day a caller forgets. ## Use four labeled blocks ```text 1. Identity You are
- . Tools:
- .
3. Constraints Do not
## Tool use and function calling best practices Source: https://llmbestpractices.com/ai-agents/tool-use-and-function-calling Last updated: 2026-10-01 ## Overview Tool use lets a model emit a structured call that your code executes and answers with a result. Reliable tool use is a schema-and-description problem: the model picks the right tool with valid arguments when the tool is named, typed, and described well. This page covers the rules that hold across providers, with Claude API specifics marked; for MCP servers see [[ai-agents/mcp-tool-design]], and for the loop around tools see [[ai-agents/agent-architecture-patterns]]. ## Write long, specific descriptions Anthropic calls the description the most important factor in tool performance. Say what the tool does, when to use it and when not to, what each parameter means, what it returns, and what it does not return. Aim for at least three or four sentences; name units and formats. Weak: "Gets the stock price for a ticker." Strong: it adds the exchanges supported, that it returns the latest trade price in USD, and that it provides nothing else about the company. On the Claude API, add `input_examples` (schema-validated example inputs) for tools with nested or format-sensitive arguments. They cost roughly 20 to 50 tokens each for simple inputs and 100 to 200 for nested ones, and they are not supported on server tools. ## Shape tools around workflows Fewer, more capable tools reduce selection ambiguity: a `schedule_event` tool that checks availability and books beats three endpoint wrappers. Prefix names with the service (`github_list_prs`) when a library spans several. Keep destructive operations separate from reads so confirmation and permission rules can target them. Overlapping tools make the model guess; keep each tool's job distinct. ## Type every argument and use strict mode Declare JSON Schema types, mark only the inputs the tool cannot default as required, use enums for closed sets, and add format hints. Claude tool names must match `^[a-zA-Z0-9_-]{1,128}$`. Set `strict: true` on a tool definition to guarantee schema-valid inputs; it removes missing parameters and type mismatches. The discipline is the same as [[ai-agents/structured-output]]. ## Validate inputs before executing Strict mode checks shape, not truth. Validate against business rules before the call touches a system: the model can supply a plausible ID that does not exist or a value in range but wrong. Validation is also the first defense when arguments derive from untrusted text; see [[ai-agents/prompt-injection-defense]]. ## Return errors the model can act on Return a failed call as the tool result with `is_error: true` and a message that says what went wrong and what to try next: "order_id not found; call search_orders first" or "Rate limit exceeded. Retry after 60 seconds." A bare "Error 500" makes the model retry blindly. Claude retries an invalid call two or three times before giving up, so cheap, clear errors pay for themselves. ## Follow the Claude API rules for tool_choice and results - `tool_choice` is `auto` (default), `any`, `tool`, or `none`. On Claude Opus 5.5, Sonnet 5.5, and Fable 5.1, `any` and `tool` return a 400 error; manual extended thinking also rejects them. Use `auto` with `strict: true`, or structured outputs when you need a fixed JSON shape. Changing `tool_choice` invalidates cached message blocks. - A `tool_result` message must immediately follow its `tool_use` message, and `tool_result` blocks must come first in the content array, before any text. Otherwise the API returns 400. - Treat tool results as untrusted: they can carry instructions from web pages, email, or third-party APIs. ## Keep the tool surface minimal and measured Expose the fewest tools that solve the task, gate destructive actions behind confirmation, sandbox side effects, and rate-limit per session. Tool definitions are context, so prune tools the logs show are unused, and evaluate with realistic tasks that need several calls. See [[ai-agents/reliable-agents-in-production]] and [[glossary/tool-use]]. ## Related - [[ai-agents/agent-architecture-patterns]] - [[ai-agents/reliable-agents-in-production]] - [[ai-agents/structured-output]] - [[ai-agents/mcp-tool-design]] - [[ai-agents/prompt-injection-defense]] - [[glossary/tool-use]]
## Backend, Databases & APIs Source: https://llmbestpractices.com/backend Last updated: 2026-10-01 > Databases and API servers. Default to Postgres for app data and SQLite for local or single-writer cases. Reach for ChromaDB when the workload is small-scale vector retrieval. ## Postgres - [[postgres]]: Postgres 18 defaults, key choices, pooling, and where each topic lives. - [[postgres-indexes]]: B-tree, GIN, GiST, BRIN, partial, expression, and covering indexes; CONCURRENTLY. - [[postgres-jsonb]]: JSONB operators, GIN classes, SQL/JSON, and promoting keys to columns. - [[backend/postgres-explain]]: Reading EXPLAIN ANALYZE plans and the pg_stat_statements loop. - [[postgres-vacuum]]: Autovacuum tuning, bloat, pg_repack, and transaction ID wraparound. - [[postgres-replication]]: Streaming and logical replication, slots, lag, and sync modes. - [[postgres-partitioning]]: Range, list, and hash partitioning with pg_partman. - [[postgres-full-text-search]]: tsvector, tsquery, GIN, pg_trgm, and when to switch engines. - [[migrations]]: Forward-only migrations, lock_timeout, and zero-downtime patterns. ## Prisma - [[prisma]]: Prisma 7 status, minimal setup, and which page covers each task. - [[prisma-schema]]: schema.prisma modeling with the prisma-client generator. - [[prisma-migrations]]: migrate dev and deploy, baseline, drift, and squashing. - [[prisma-client]]: Driver adapters, the client singleton, logging, and $extends. - [[prisma-transactions]]: Array and interactive transactions, isolation, and P2034 retries. - [[prisma-pooling]]: Driver pool settings, PgBouncer, serverless, and Accelerate. - [[prisma-raw-queries]]: $queryRaw and $executeRaw with Prisma.sql. - [[prisma-v6-to-v7-upgrade]]: Checklist for upgrading Prisma 5 or 6 to 7. ## FastAPI - [[fastapi]]: FastAPI 0.142 baseline, install, and route layout. - [[fastapi-pydantic]]: Pydantic v2 input and output models, constraints, and aliases. - [[fastapi-dependencies]]: Depends, yield scope, and the lifespan context manager. - [[fastapi-async-io]]: async def versus def, the threadpool, and worker sizing. - [[fastapi-background-tasks]]: BackgroundTasks limits and when to use a real queue. - [[fastapi-openapi]]: OpenAPI 3.1 accuracy, docs URLs, and client generation. ## ChromaDB - [[chromadb]]: Chroma 1.x clients, retrieval defaults, and when to leave. - [[chromadb-collections]]: Collection design, distance settings, metadata, and embedding ownership. - [[chromadb-filters]]: where, where_document, and application-side hybrid retrieval. - [[chromadb-persistence]]: PersistentClient, server mode, Docker, auth, CVE-2026-45829, and backups. - [[chromadb-scale-limits]]: Single-node sizing and migration to pgvector, Qdrant, or Cloud. ## Other databases and services - [[sqlite]]: When SQLite fits, WAL pragmas, the WAL-reset bug, and backups. - [[supabase]]: Publishable and secret keys, explicit Data API grants, CLI, and pooling modes. - [[supabase-rls]]: Grants plus RLS policies, USING and WITH CHECK, and wrapped auth.uid(). ## Auth, payments, and operations - [[auth-sessions]]: Session cookies, rotation, refresh tokens, CSRF, and revocation. - [[payments-stripe]]: Stripe webhook verification, API versions, idempotency, and fulfillment. - [[webhooks]]: Inbound webhook HMAC, dedupe, fast 2xx, and replay protection. - [[email-deliverability]]: SPF, DKIM, DMARC, and Gmail and Yahoo bulk-sender rules. - [[observability]]: OpenTelemetry, structured logs, metrics, and stable semantic conventions. ## Related MOCs - [[coding/index|Coding]] - [[ai-agents/index|AI Agents]] - [[ops/index|Ops]]
## Auth Sessions: Best Practices Source: https://llmbestpractices.com/backend/auth-sessions Last updated: 2026-10-01 ## Overview Authenticate users with an opaque session token in an `HttpOnly`, `Secure`, `SameSite` cookie, and keep the source of truth on the server so a session can be revoked at once. This page covers cookie flags, rotation, and CSRF defenses; opaque sessions versus JWTs are in [[comparisons/oauth-vs-jwt]]. ## Set every session cookie with __Host-, HttpOnly, Secure, and SameSite ``` Set-Cookie: __Host-session=
## ChromaDB Best Practices Source: https://llmbestpractices.com/backend/chromadb Last updated: 2026-10-01 ## Overview Use ChromaDB for prototypes, internal tools, and small production [[ai-agents/rag]] workloads where one node holds the vectors and one application queries them. These notes target the Chroma 1.x line (Python `chromadb` 1.5.9, JavaScript `chromadb` 3.5.0 as of 2026-10-01). Each topic has its own page; this one holds the decisions. ## Pick the client by deployment, not preference ```python import chromadb client = chromadb.EphemeralClient() # in memory; tests and notebooks client = chromadb.PersistentClient(path="./.chroma") # embedded; one process owns the directory client = chromadb.HttpClient(host="localhost", port=8000) # server started with `chroma run --path ./.chroma` client = chromadb.CloudClient(tenant="...", database="...", api_key="...") # Chroma Cloud ``` `AsyncHttpClient` is the async variant of `HttpClient`. Use `PersistentClient` for scripts, single-worker services, and notebooks. Use the server once several processes or services need the data. Deployment, auth, and backup are in [[backend/chromadb-persistence]]. ## Model collections around retrieval tasks One collection holds one embedding space and one distance metric. Split collections by retrieval task or embedding model (`docs_support`, `code_snippets`), and separate tenants with a `tenant_id` metadata filter unless tenants need hard isolation or different embedding configurations. Design rules, naming, and embedding ownership are in [[backend/chromadb-collections]]. ## Query with a filter and a bounded n_results ```python results = collection.query( query_embeddings=[query_embedding], n_results=10, where={"$and": [{"tenant_id": "t_123"}, {"lang": "en"}]}, where_document={"$contains": "refund"}, ) ``` - `n_results` defaults to 10. A selective filter returns fewer rows than `n_results`, never padding. - Multiple conditions need an explicit `$and`. A `where` dict with two keys raises an error. - Chroma has no local BM25 index; the `search` hybrid API works on Chroma Cloud only. Merge keyword and vector results in your application; see [[backend/chromadb-filters]] and [[ai-agents/rag-retrieval]]. ## Outgrow ChromaDB on measured signals Chroma's single-node guidance sizes RAM at about 0.245 million 1024-dimension vectors per GB, and its tests held up to roughly 7 million embeddings. Move to pgvector when you already run [[backend/postgres]] and want SQL joins and one backup story, to Qdrant when filtered search at scale is the bottleneck, or to Chroma Cloud when you want to stay on the same API. Signals, sizing, and the migration path are in [[backend/chromadb-scale-limits]]; for the managed comparison see [[comparisons/pinecone-vs-pgvector]]. ## Related - [[ai-agents/embeddings]] - [[ai-agents/rag]] - [[backend/postgres]] - [[backend/chromadb-collections]] - [[backend/chromadb-filters]] - [[backend/chromadb-persistence]] - [[backend/chromadb-scale-limits]]
## ChromaDB: Collections and Embeddings Source: https://llmbestpractices.com/backend/chromadb-collections Last updated: 2026-10-01 ## Overview A ChromaDB collection owns one embedding space, one distance metric, an HNSW index configuration, and one metadata schema. Most retrieval bugs trace back to a collection that mixes embedding models or has a metadata schema that drifted. These rules cover when to split collections, what to fix at creation, and who computes the embeddings. ## Create one collection per retrieval task Split collections by workload and embedding model: `docs_support`, `code_snippets`, `product_catalog`. For many small tenants, keep one collection and filter on a `tenant_id` metadata key; use separate collections only when tenants need hard isolation or different embedding models. Always pass the tenant filter on every query, because a missing filter leaks rows across tenants. Names are 3 to 512 characters from `[a-zA-Z0-9._-]`, starting and ending with an alphanumeric. Prefix by service or environment (`prod_support_docs`) when instances are shared. Use `get_or_create_collection` for idempotent startup; `create_collection` raises when the collection exists. ## Set the distance metric at creation The default distance is `l2`. Choose `cosine` for text embeddings, or `ip` for inner product, through `configuration`. The space cannot be changed later, so a wrong choice means a new collection. `ef_search` is among the settings you can change afterwards with `collection.modify`. ```python collection = client.get_or_create_collection( name="docs_support", configuration={"hnsw": {"space": "cosine"}}, metadata={"embedding_model": "voyage-3", "dimension": 1024}, embedding_function=None, ) ``` The older `metadata={"hnsw:space": "cosine"}` form still works in 1.5.9, but `configuration` is the documented path. Custom `metadata` keys, such as the model name above, are yours to read back with `collection.metadata`. ## Keep metadata flat and typed before ingest - Decide the keys first: `source`, `tenant_id`, `created_at`, `lang`, `doc_id`, `chunk_index`. - Values are `str`, `int`, `float`, `bool`, or lists of those. A nested dict raises `ValueError`; store complex payloads as a JSON string and parse them client-side. - Use one type per key forever. A range filter such as `{"created_at": {"$gte": 5}}` silently skips rows whose value has a different type, so never write an ISO string to an integer timestamp key. - Metadata-only changes do not need re-embedding: call `collection.update(ids=..., metadatas=...)`. ```python collection.add( ids=["chunk-001"], documents=["Refunds are processed within 5 business days."], embeddings=[embedding], metadatas=[{"tenant_id": "acme", "source": "zendesk", "created_at": 1736294400, "lang": "en"}], ) ``` ## Give embeddings exactly one owner Chroma enforces the dimension, not the model. A collection created with 1024-dimension vectors rejects a 384-dimension query with an error, but two models that share a dimension mix silently and give meaningless scores. Choose one owner: - Collection-owned: pass an `embedding_function` and call `add(documents=...)` and `query(query_texts=...)`. Chroma persists the function and its parameters in the collection configuration and rebuilds it on `get_collection`. The default is `all-MiniLM-L6-v2` (384 dimensions), which runs locally; hosted providers such as OpenAI call their API on every add and query. - Pipeline-owned: create the collection with `embedding_function=None`, compute vectors yourself, and always pass `embeddings=` and `query_embeddings=`. A `query_texts` call on such a collection raises an error. This fits batching, caching, and rate limiting outside Chroma, and keeps tests free of network calls. Record `embedding_model` and `dimension` in collection metadata and assert them in every ingest worker before writing. ## Change the model by creating a new collection Provider models can change behind a name, and switching models invalidates every stored vector. Pin the exact model identifier. When the provider offers no version pinning, embed a fixed reference sentence at ingest and store a hash of its vector in collection metadata; a changed hash signals a silent model update. To upgrade, create a new collection, re-embed the corpus, compare recall on a golden set, move query traffic, and keep the old collection for rollback. See [[ai-agents/embeddings]] for the upgrade protocol and [[ai-agents/embeddings-dimensionality]] for Matryoshka truncation, the usual source of dimension mismatches. ## Reset only in development Gate `client.delete_collection(name)` followed by `create_collection` behind an explicit environment variable (`RESET_CHROMA=true`), never in a production path. In production, upsert by id and store a `version` metadata key so partial re-ingests converge. See [[backend/chromadb-persistence]] for backups. ## Related - [[backend/chromadb]] - [[backend/chromadb-filters]] - [[backend/chromadb-persistence]] - [[ai-agents/embeddings]] - [[ai-agents/embeddings-dimensionality]] - [[ai-agents/rag]] - [[ai-agents/ollama]]
## ChromaDB: Metadata Filters and Hybrid Retrieval Source: https://llmbestpractices.com/backend/chromadb-filters Last updated: 2026-10-01 ## Overview Chroma filters on two axes at once: `where` on metadata and `where_document` on stored document text, both combined with vector similarity in `query` and without it in `get`. Loose filters fill the context window with off-topic chunks, and a filter that matches nothing returns an empty result that downstream code mistakes for "no answer". These rules keep filters correct. ## Always pass a where filter when a scope applies An unfiltered query searches the whole collection. For multi-tenant collections that leaks rows across tenants, so filter by `tenant_id` on every query, ideally in one wrapper function that cannot be bypassed. ```python results = collection.query( query_embeddings=[query_vec], n_results=10, where={"$and": [{"tenant_id": "acme"}, {"lang": "en"}]}, ) ``` `n_results` counts matches after filtering. A selective filter returns fewer than `n_results` rows rather than padding the list with non-matching ones. ## Combine conditions with an explicit $and A `where` dict must have exactly one top-level operator. `{"tenant_id": "acme", "lang": "en"}` raises `ValueError`; wrap the conditions in `$and`, or use `$or` for alternatives. ```python where={"created_at": {"$gte": 1736294400}} # range where={"source": {"$in": ["zendesk", "confluence"]}} # set inclusion where={"lang": {"$ne": "de"}} # exclusion where={"$or": [{"source": "zendesk"}, {"source": "confluence"}]} # alternatives where={"tags": {"$contains": "billing"}} # array metadata ``` Metadata operators are `$eq`, `$ne`, `$gt`, `$gte`, `$lt`, `$lte`, `$in`, `$nin`, `$and`, `$or`, plus `$contains` and `$not_contains` for array values. Arrays hold strings, integers, floats, or booleans of one type; empty and nested arrays are not allowed. Use the operators instead of filtering in Python, which defeats the filter. Keep metadata types consistent so range filters do not skip rows; see [[backend/chromadb-collections]]. ## Use where_document for rare keywords, not as the retriever `where_document` supports `$contains`, `$not_contains`, `$regex`, `$not_regex`, `$and`, and `$or`. Matching is case-sensitive and returns no ranking. ```python results = collection.query( query_embeddings=[query_vec], n_results=20, where={"tenant_id": "acme"}, where_document={"$contains": "ERR_4012"}, ) ``` Use it to require an exact token the embedding may blur: error codes, SKUs, identifiers. Pair it with a `where` filter. Do not treat it as the primary retrieval path; it returns unranked matches. ## Over-fetch and rerank in the application When the filter is tight, or the question needs precise ordering, fetch more than you need and rerank before building the prompt. Fifty candidates reranked by a cross-encoder cost milliseconds; fifty chunks in the prompt cost tokens. See [[ai-agents/rag-reranking]] for the pattern and [[ai-agents/rag-retrieval]] for the retrieval loop. ## Merge BM25 and vector results yourself The local Chroma server has no BM25 index. Chroma's `search` API with hybrid ranking is marked experimental and works only on Chroma Cloud. For a local deployment, run a keyword search alongside the vector query and merge the ranked id lists with reciprocal rank fusion. ```python def rrf(*ranked_id_lists, k=60): scores = {} for ids in ranked_id_lists: for rank, doc_id in enumerate(ids): scores[doc_id] = scores.get(doc_id, 0) + 1 / (k + rank + 1) return sorted(scores, key=scores.get, reverse=True) ``` Use the keyword path when queries contain rare tokens: product codes, error messages, proper nouns. See [[ai-agents/rag-retrieval]] for the full pattern. ## Fail loudly on filters that match nothing A filter that silently returns zero rows is worse than an error. In development and tests, assert that a filter matches at least one record. ```python def assert_filter_matches(collection, where): if not collection.get(where=where, limit=1)["ids"]: raise ValueError(f"filter {where} matches nothing in {collection.name}") ``` Zero-match filters are the usual cause of "the model said it found nothing" bugs. ## Related - [[backend/chromadb]] - [[backend/chromadb-collections]] - [[ai-agents/rag]] - [[ai-agents/rag-retrieval]] - [[ai-agents/rag-reranking]]
## ChromaDB: Persistence, Server Mode, and Backup Source: https://llmbestpractices.com/backend/chromadb-persistence Last updated: 2026-10-01 ## Overview ChromaDB runs in-process (ephemeral or persistent), as a server reached over HTTP, or as Chroma Cloud. Pick by how many processes touch the data. A persistent client stores everything under one directory: `chroma.sqlite3` plus segment files. This page covers the persistent and server modes, Docker, security, and backups. ## Use PersistentClient when one process owns the directory ```python import chromadb client = chromadb.PersistentClient(path="/data/chroma") # default path is .chroma ``` The path must be writable and, in containers, on a volume. Chroma's documentation does not describe sharing one directory between processes, so give each directory a single owning process. For multi-worker web servers (Gunicorn, uvicorn workers) or several services, run a server and connect with `HttpClient`; each worker then holds a connection, not the files. ## Run server mode for shared access ```bash chroma run --path /db_path ``` ```bash docker run -d --name chroma -p 8000:8000 -v ./chroma-data:/data chromadb/chroma:
## ChromaDB: Scale Limits and Migration Source: https://llmbestpractices.com/backend/chromadb-scale-limits Last updated: 2026-10-01 ## Overview Single-node ChromaDB is built for ease of use at moderate scale, and it degrades gradually rather than failing loudly. Plan the exit from measured numbers, not a vector-count folklore threshold. This page gives the sizing formula, the signals to watch, and the migration path. ## Size the node from Chroma's own formula Chroma's single-node performance guide gives `N = R * 0.245`, where `R` is RAM in GB and `N` is the maximum collection size in millions of 1024-dimension vectors (with three small metadata fields). Reserve at least 1 GB for the system on top of that, and run with at least 2 GB of RAM. A 16 GB host therefore holds roughly 3.9 million such vectors. Disk should be at least the size of RAM plus overhead. Other documented limits: queries parallelize up to the number of vCPUs and then queue, so latency rises linearly under load; insert in batches of 50 to 250, with throughput plateauing near 150; Chroma's tests stayed stable up to about 7 million embeddings. ## Watch four signals, and plan the move before the first breach - Memory: resident memory approaching the formula's limit for your dimension. - Latency: p99 on filtered queries climbing as concurrency passes your vCPU count. - Recall: recall@10 on a golden query set falling as the collection grows. HNSW approximate search degrades without errors. - Restart time: a long reload after a crash means you cannot recover quickly. Record a baseline for all four now, so a regression is visible as a change, not an opinion. ## Move to pgvector when you already run Postgres pgvector lets you join vectors to relational data and reuse your backups and monitoring. Index limits matter: an HNSW index supports `vector` up to 2,000 dimensions and `halfvec` up to 4,000, so a 3,072-dimension embedding needs `halfvec`. ```sql CREATE EXTENSION IF NOT EXISTS vector; CREATE TABLE docs_support ( id text PRIMARY KEY, document text, tenant_id text, created_at bigint, embedding vector(1024) ); CREATE INDEX ON docs_support USING hnsw (embedding vector_cosine_ops); -- m=16, ef_construction=64 by default SET hnsw.iterative_scan = relaxed_order; -- keep scanning when a filter removes rows SELECT id, document, 1 - (embedding <=> $1::vector) AS score FROM docs_support WHERE tenant_id = 'acme' ORDER BY embedding <=> $1::vector LIMIT 10; ``` Approximate indexes filter after the index scan, so a selective `WHERE` can return fewer than `LIMIT` rows; iterative scans fix that. See [[backend/postgres-indexes]] and [[comparisons/pinecone-vs-pgvector]]. ## Choose Qdrant or Chroma Cloud for the other cases Move to Qdrant when filtered nearest-neighbor search at scale, horizontal scaling, or quantization is the bottleneck; see [[ai-agents/rag-vector-databases]]. Stay on the Chroma API with Chroma Cloud when you only need more capacity: `chroma copy --from-local --to-cloud` moves collections. Check its quotas first; the documented limits include 5,000,000 records per collection and 300 records per write. ## Migrate with a parallel write period 1. Export the collection with paged `get` calls (below). 2. Bulk-load into the target and rebuild its index. 3. Dual-write new documents to both stores. 4. Run the golden query set against both and compare recall@10. 5. Move reads when the target matches or beats Chroma, then stop writing to Chroma and archive the directory. ```python rows, offset = [], 0 while True: page = collection.get(limit=1000, offset=offset, include=["documents", "metadatas", "embeddings"]) if not page["ids"]: break rows += zip(page["ids"], page["documents"], page["metadatas"], page["embeddings"]) offset += 1000 ``` Migrate vectors as stored; re-embedding in the same step mixes two failures into one. Stage the migration on a snapshot first ([[backend/chromadb-persistence]]), and budget testing time because filter syntax and tuning knobs differ between stores. ## Related - [[backend/chromadb]] - [[backend/chromadb-persistence]] - [[backend/postgres]] - [[ai-agents/rag-vector-databases]] - [[ai-agents/rag]] - [[ai-agents/embeddings]] - [[comparisons/pinecone-vs-pgvector]]
## Email Deliverability: SPF, DKIM, DMARC Source: https://llmbestpractices.com/backend/email-deliverability Last updated: 2026-10-01 ## Overview Publish SPF, DKIM, and DMARC records that align with your From domain before the first transactional email; mailbox providers send unauthenticated mail to spam whatever its content. This standard applies to any provider (Resend, Postmark, AWS SES), whose dashboard shows the exact record values. Add the records at your DNS host: [[ops/cloudflare-dns]] or [[ops/namecheap-dns]]. ## Send from a dedicated subdomain Use `mail.example.com` or `send.example.com` as the sending identity so complaints against one stream do not damage the root domain that serves your site and corporate mail. Verify the subdomain in your provider and send from `noreply@mail.example.com`. ## Publish exactly one SPF record SPF is one TXT record that lists the servers allowed to send for the domain. Two `v=spf1` records are a permanent error and SPF fails ([dmarc.org](https://dmarc.org/wiki/FAQ)). ```dns mail.example.com. TXT "v=spf1 include:amazonses.com ~all" ``` Use the provider's `include` value. `~all` is softfail and `-all` is hardfail. SPF evaluation allows at most 10 DNS-querying terms (`include`, `a`, `mx`, `redirect`); more is a permanent error, so flatten or drop unused includes. Providers usually also ask for an MX record on the sending subdomain to process bounces ([Resend](https://resend.com/docs/dashboard/domains/introduction)). ## Publish the provider's DKIM keys and confirm they verify DKIM signs each message with a key the provider holds; receivers fetch the public key from DNS. Add the provider's records exactly as shown (AWS SES Easy DKIM issues three CNAMEs, per its [docs](https://docs.aws.amazon.com/ses/latest/dg/send-email-authentication-dkim-easy.html); Postmark issues a [TXT record](https://postmarkapp.com/support/article/1091-how-do-i-set-up-dkim-for-postmark)). Do not send production mail until the provider reports DKIM verified, or messages go out unsigned. ```dns xxxx._domainkey.mail.example.com. CNAME xxxx.dkim.amazonses.com. ``` ## Publish DMARC at p=none, then raise the policy DMARC tells receivers what to do when SPF and DKIM fail to align, and where to send reports. ```dns _dmarc.mail.example.com. TXT "v=DMARC1; p=none; rua=mailto:dmarc@example.com" ``` Start at `p=none` to collect aggregate reports without affecting delivery. Read them, confirm every real sender passes, then move to `p=quarantine` and finally `p=reject` ([dmarc.org](https://dmarc.org/overview/)). `p=none` only reports; spoofed mail is blocked only once the policy enforces. RFC 9989 (May 2026) replaced RFC 7489: it removes the `pct` tag, adds `np`, `psd`, and `t`, and finds policy by walking up the DNS tree instead of using the Public Suffix List. Stage a rollout by sending domain and policy level rather than by `pct`. ## Make DKIM and SPF align with the From domain DMARC passes only when SPF or DKIM passes and its domain aligns with the From header domain. Relaxed alignment (the default) accepts a subdomain match, so `mail.example.com` aligns with `example.com`; strict requires identical domains. DKIM aligns when the signing `d=` domain matches the From domain. SPF aligns when the Return-Path (MAIL FROM) domain does, which is why providers offer a [custom return-path domain](https://postmarkapp.com/support/article/910-how-do-i-add-a-custom-return-path). Bounces usually route through a subdomain such as `pm-bounces.example.com`, so strict SPF alignment (`aspf=s`) fails; leave `adkim` and `aspf` unset. The classic failure is SPF passing on the provider's bounce domain while DMARC fails because nothing aligns. ## Meet the Gmail and Yahoo bulk-sender rules Since 2024-02-01, senders of more than 5,000 messages per day to Gmail must have SPF and DKIM, a DMARC policy (at least `p=none`) with the From domain aligned to SPF or DKIM, and one-click unsubscribe (RFC 8058) on marketing and subscribed mail. Yahoo applies the same rules to bulk senders without a published volume threshold, and requires honoring unsubscribes within 2 days. All senders need valid forward and reverse DNS, TLS, and an RFC 5322 compliant message, and should keep the spam rate in Google Postmaster Tools below 0.10% and never reach 0.30%. Since 2025-05-05 Microsoft requires SPF, DKIM, and DMARC from high-volume senders (over 5,000 messages per day) to Outlook.com, Hotmail.com, and Live.com, and rejects non-compliant mail with `550 5.7.515`. Transactional mail such as password resets does not need an unsubscribe link, but it still needs full authentication. ## Warm up a new domain gradually A new domain has no reputation. Ramp volume over days to weeks, start with engaged recipients, and watch bounce and complaint rates. ## Related - [[ops/cloudflare-dns]] and [[ops/namecheap-dns]]: where to add the TXT, CNAME, and MX records. - [[ops/secrets-and-env]]: store provider API keys outside source control. - [[backend/webhooks]]: handle provider bounce and complaint webhooks. - [[backend/auth-sessions]]: transactional email backs password reset and magic-link flows. - [[howto/launch-a-new-site]]: deliverability setup as part of launch.
## FastAPI Best Practices Source: https://llmbestpractices.com/backend/fastapi Last updated: 2026-10-01 ## Overview Use FastAPI for Python HTTP services that need OpenAPI, async I/O, and typed request and response models. The current release is 0.142.2 (2026-09-30). It requires Python 3.10 or later and Pydantic 2.9 or later, generates OpenAPI 3.1, and pairs with [[backend/postgres]] through SQLAlchemy, asyncpg, or psycopg. This page is the baseline; each topic has its own page. ## Install fastapi[standard] and use the CLI `pip install "fastapi[standard]"` adds uvicorn, the FastAPI CLI, `httpx`, `email-validator` (needed for Pydantic's `EmailStr`), and the OpenTelemetry SDK. Run `fastapi dev` for development with reload and `fastapi run` for production. Since 0.142.0 FastAPI emits OpenTelemetry traces and metrics natively; see [[backend/observability]]. ## Keep the rules on the page that owns them - Separate input and output models, validate once at the edge: [[backend/fastapi-pydantic]]. - `Depends`, `yield` cleanup, and the lifespan context manager: [[backend/fastapi-dependencies]]. - Blocking calls, threadpool offload, and worker counts: [[backend/fastapi-async-io]]. - Work after the response and when to use a real queue: [[backend/fastapi-background-tasks]]. - Response models, status codes, docs URLs, and client generation: [[backend/fastapi-openapi]]. ## Lay routes out under routers One router per resource, mounted in `main.py`. Keep `main.py` to imports, app construction, and `include_router` calls. ``` app/ main.py # FastAPI(lifespan=...) + include_router calls deps.py # Depends() factories routers/ # users.py, orders.py; each: APIRouter(prefix="/users", tags=["users"]) schemas/ # Pydantic models db/ # SQLAlchemy or asyncpg layer ``` See [[file-organization/project-structure]] for broader layout rules, and [[backend/webhooks]] for the raw-body route pattern. ## Related - [[coding/python]] - [[backend/postgres]] - [[backend/prisma]] - [[backend/fastapi-pydantic]] - [[backend/fastapi-dependencies]] - [[backend/fastapi-async-io]] - [[backend/fastapi-background-tasks]] - [[backend/fastapi-openapi]]
## FastAPI: Async I/O Source: https://llmbestpractices.com/backend/fastapi-async-io Last updated: 2026-10-01 ## Overview An `async def` route runs on the event loop, so one blocking call stalls every in-flight request on that worker until it returns. A plain `def` route runs in a threadpool instead. Choosing between them correctly, and sizing workers and pools, is the performance contract of FastAPI. The general asyncio rules are in [[coding/python-async]]. ## Match the route type to what the route calls - `async def`: every I/O call inside is awaited (`asyncpg`, `httpx.AsyncClient`, async SQLAlchemy). - `def`: the route calls a blocking library (a sync SDK, `requests`, sync SQLAlchemy). FastAPI runs it in a threadpool, so the event loop stays free. This is the correct choice for blocking code, not a workaround. - Never call `time.sleep`, `requests.get`, or sync file and database I/O inside `async def`. Use `await asyncio.sleep()` and `httpx.AsyncClient`. ```python @app.get("/users/{user_id}") async def get_user(user_id: str, db: DB) -> UserRead: return await user_repo.get(db, user_id) ``` ## Know the threadpool has 40 threads `def` routes and dependencies run through AnyIO's default limiter, which allows 40 concurrent threads. Past that, requests queue. Offload blocking work from `async def` code with `starlette.concurrency.run_in_threadpool` or `anyio.to_thread.run_sync`; both share that same 40-thread limiter and its accounting. `asyncio.to_thread` uses a separate default executor, so mixing it in splits your thread budget. ```python from starlette.concurrency import run_in_threadpool async def resize(data: bytes) -> bytes: return await run_in_threadpool(_resize_sync, data) ``` Threads do not parallelize CPU-bound Python because of the GIL. Send CPU-heavy work to a process pool or a worker queue ([[backend/fastapi-background-tasks]]). ## Use async database drivers exclusively in async routes For [[backend/postgres]], use `asyncpg`, `psycopg` in async mode, or async SQLAlchemy on top of either. Do not use `psycopg2` or sync SQLAlchemy in `async def`. ```python engine = create_async_engine("postgresql+asyncpg://user:pass@localhost/db", pool_size=10, max_overflow=20) SessionLocal = async_sessionmaker(engine, expire_on_commit=False) ``` Set `expire_on_commit=False`: after a commit, attribute access on an expired object triggers a lazy load, which fails in async code. Create the engine and one shared `httpx.AsyncClient` in lifespan; see [[backend/fastapi-dependencies]]. ## Size workers and pools together One uvicorn process uses one CPU core. Each worker has its own event loop and its own pool, so the database sees `workers x (pool_size + max_overflow)` connections. With `pool_size=10`, `max_overflow=20`, and four workers, that is up to 120. Put PgBouncer in front when the product exceeds `max_connections`. ```bash fastapi run --workers 4 app/main.py # or: uvicorn app.main:app --workers 4 gunicorn app.main:app -k uvicorn_worker.UvicornWorker -w 4 # needs the uvicorn-worker package ``` On Kubernetes or another orchestrator, FastAPI's docs recommend one process per container and scaling by replicas rather than workers. The built-in `uvicorn.workers` module is deprecated in favor of the separate `uvicorn-worker` package. ## Cap concurrency to downstream services Async code can open far more simultaneous requests to a dependency than threads ever did. Bound them with a semaphore, one per downstream service so each limit tunes independently. ```python _sem = asyncio.Semaphore(50) async def call_downstream(client: httpx.AsyncClient, url: str) -> dict: async with _sem: r = await client.get(url, timeout=10.0) r.raise_for_status() return r.json() ``` Create the semaphore at module scope or in lifespan. Profile async bottlenecks with [[coding/python-performance]]. ## Related - [[backend/fastapi]] - [[coding/python-async]] - [[coding/python]] - [[backend/fastapi-dependencies]] - [[backend/postgres]] - [[coding/python-performance]]
## FastAPI: Background Tasks Source: https://llmbestpractices.com/backend/fastapi-background-tasks Last updated: 2026-10-01 ## Overview `BackgroundTasks` runs a function after the response is sent, in the same process and worker. It suits small work the caller need not wait for: a confirmation email, an audit log write, a cache invalidation. It is not a job queue: it does not persist tasks, retry, or survive a restart. FastAPI's own docs point to Celery or similar for heavy work that does not need the app's memory. ## Add tasks from routes and dependencies Declare `BackgroundTasks` as a parameter and call `add_task`. A dependency can declare it too, and FastAPI merges all tasks and runs them after the response. ```python @router.post("/signups", status_code=201) async def signup(payload: UserCreate, tasks: BackgroundTasks, db: DB) -> UserRead: user = await user_service.create(db, payload) tasks.add_task(send_welcome_email, user.id) return UserRead.model_validate(user) ``` The `201` returns immediately; `send_welcome_email` runs afterwards. `add_task` accepts both `async def` and plain `def` functions. An `async def` task is awaited on the event loop, so it must not block ([[backend/fastapi-async-io]]); a plain `def` task runs in the threadpool. ## Treat tasks as best-effort A task dies with its worker. A crash, a deploy, or an uncaught exception in the function discards it with no record. - Acceptable: welcome emails (the user can request a resend), cache invalidation (the next read rebuilds it), analytics pings. - Not acceptable: payment state changes, webhook delivery with guarantees, anything that must retry, anything another service depends on. ## Move durable work to a real queue | Need | Tool | | --- | --- | | Small, in-process, best-effort | `BackgroundTasks` | | Durable tasks, retries, schedules, many workers | Celery with Redis or RabbitMQ | | Low volume, no extra infrastructure | A Postgres `jobs` table (`status`, `attempts`, `run_at`) polled with `FOR UPDATE SKIP LOCKED` | Enqueue inside the request, acknowledge fast, and process elsewhere. This is also the pattern for inbound webhooks: verify the signature, enqueue, return 2xx ([[backend/webhooks]]). Schema ideas for the job table are in [[backend/postgres]]. ## Write tasks to be idempotent and self-contained A task may run twice once you add a queue or retries. Pass stable identifiers, not request-scoped objects, and open a fresh session inside the task; a request-scoped session may already be closed ([[backend/fastapi-dependencies]]). ```python async def send_welcome_email(user_id: str) -> None: async with SessionLocal() as db: user = await db.get(User, user_id) if user.welcome_sent: return # idempotency guard await mailer.send_welcome(user.email) user.welcome_sent = True await db.commit() ``` ## Related - [[backend/fastapi]] - [[coding/python]] - [[backend/fastapi-dependencies]] - [[backend/fastapi-async-io]] - [[backend/postgres]] - [[backend/webhooks]]
## FastAPI: Dependencies and Lifespan Source: https://llmbestpractices.com/backend/fastapi-dependencies Last updated: 2026-10-01 ## Overview `Depends` supplies per-request resources: a database session, the current user, settings. The `lifespan` context manager owns resources that live as long as the worker process: the connection pool, a shared HTTP client, a loaded model. Keep the two scopes separate and let dependencies read what lifespan created. The framework baseline is in [[backend/fastapi]]. ## Keep routes thin and chain dependencies A route declares what it needs and delegates. Fetching, access control, and resource setup live in dependencies that can be tested alone. FastAPI resolves the dependency graph per request and calls each dependency once, even when several others declare it. ```python async def get_current_user( token: Annotated[str, Depends(oauth2_scheme)], db: Annotated[AsyncSession, Depends(get_db)], ) -> User: return await auth_service.resolve(db, token) async def get_admin(user: Annotated[User, Depends(get_current_user)]) -> User: if not user.is_admin: raise HTTPException(status_code=403) return user ``` Name type-plus-dependency pairs once with `Annotated` and reuse them in signatures: ```python CurrentUser = Annotated[User, Depends(get_current_user)] DB = Annotated[AsyncSession, Depends(get_db)] @router.get("/profile") async def profile(user: CurrentUser, db: DB) -> UserRead: ... ``` ## Mind when yield cleanup runs Code after `yield` runs after the response is sent by default (`scope="request"`). A `commit()` there cannot change the response, so the client sees success even if the commit fails. Either commit in the route or service and keep only the close in the dependency, or end the dependency before the response is sent with `scope="function"`. ```python async def get_db(factory=Depends(get_session_factory)) -> AsyncGenerator[AsyncSession, None]: async with factory() as session: try: yield session await session.commit() except Exception: await session.rollback() raise DB = Annotated[AsyncSession, Depends(get_db, scope="function")] # commit runs before the response ``` If you catch an exception in a `yield` dependency, re-raise it unless you raise an `HTTPException`. Do not hand a request-scoped session to a background task; open a new session inside the task ([[backend/fastapi-background-tasks]]). ## Use a class for configurable dependencies A class with `__call__` is clearer than a closure factory. The instance is created once and shared across requests, so keep per-request state out of it. ```python class RateLimiter: def __init__(self, max_calls: int, window_seconds: int) -> None: self.max_calls, self.window_seconds = max_calls, window_seconds async def __call__(self, request: Request) -> None: if await exceeds_limit(f"rl:{request.client.host}", self.max_calls, self.window_seconds): raise HTTPException(status_code=429) @router.post("/payments", dependencies=[Depends(RateLimiter(10, 60))]) async def create_payment(...) -> PaymentRead: ... ``` ## Override dependencies in tests ```python app.dependency_overrides[get_db] = lambda: test_session # teardown app.dependency_overrides.clear() ``` Clear the overrides after each test so state does not leak. See [[coding/python-testing]]. ## Create app-wide resources in lifespan `@app.on_event("startup")` and `shutdown` are deprecated. If you pass `lifespan`, the event handlers are no longer called. Lifespan runs once per worker process, and only for the main app, not mounted sub-applications. Code before `yield` is startup; code after is shutdown. ```python @asynccontextmanager async def lifespan(app: FastAPI): engine = create_async_engine(settings.database_url, pool_size=10, pool_pre_ping=True) async with engine.connect() as conn: await conn.execute(text("SELECT 1")) # fail fast if the database is unreachable async with httpx.AsyncClient(timeout=10.0) as http: yield {"session_factory": async_sessionmaker(engine, expire_on_commit=False), "http": http} await engine.dispose() app = FastAPI(lifespan=lifespan) def get_session_factory(request: Request): return request.state.session_factory ``` - Yield a dict from lifespan to expose state as `request.state`; wrap access in a dependency so routes do not touch the app object and tests can override it. - `pool_pre_ping=True` discards stale connections after a database restart. Pool sizes multiply per worker; see [[backend/fastapi-async-io]] and [[backend/postgres]]. - Load heavy resources (ML models, indexes) before `yield` so failures surface at startup, not on the first request. A worker that cannot load its model should crash and let the process manager restart it. - An exception raised before `yield` stops startup. Wrap partially created resources in `try/finally` so they are released. ## Related - [[backend/fastapi]] - [[coding/python]] - [[backend/fastapi-async-io]] - [[backend/fastapi-pydantic]] - [[backend/fastapi-background-tasks]] - [[backend/postgres]] - [[coding/python-testing]]
## FastAPI: OpenAPI and Docs Source: https://llmbestpractices.com/backend/fastapi-openapi Last updated: 2026-10-01 ## Overview FastAPI generates an OpenAPI 3.1 schema from route declarations and Pydantic models. The schema feeds Swagger UI (`/docs`), ReDoc (`/redoc`), and client generators. It is only as accurate as your annotations: a route without a response type or status code produces a schema that misleads consumers and generates untyped clients. The model side is covered in [[backend/fastapi-pydantic]]. ## Declare the response type, status code, tags, and summary on every route ```python router = APIRouter(prefix="/orders", tags=["orders"]) @router.post( "/", response_model=OrderRead, status_code=201, summary="Place a new order", responses={404: {"model": ErrorDetail, "description": "Product not found"}}, ) async def create_order(payload: OrderCreate, db: DB) -> OrderRead: """Place an order for the authenticated customer. Validates inventory first.""" ``` - A return annotation is enough as the response type. Add `response_model` when the function returns an ORM object or dict that differs from the annotation. Either way the output is filtered through the model, so extra internal fields do not leak. - The default `status_code` is 200. Use 201 for creation and 204 for an empty delete; see [[cheatsheets/http-status-codes]]. - `summary` is the collapsed label; the docstring becomes the long description. - `responses` adds error shapes. FastAPI documents only the success response by default. ## Set shared metadata on the router `prefix`, `tags`, and `dependencies` on `APIRouter` apply to every route in it. Router-level dependencies run for every route, which is the right place for authentication; see [[backend/fastapi-dependencies]]. ```python router = APIRouter(prefix="/orders", tags=["orders"], dependencies=[Depends(verify_api_key)]) app.include_router(router) ``` Hide routes that should not appear in the public schema with `include_in_schema=False` (health checks, admin). For separate public and internal schemas, run two `FastAPI` apps with different `openapi_url` values. ## Disable all three docs URLs in production when the API is private `/docs` and `/redoc` both depend on `/openapi.json`. Setting `docs_url=None` and `redoc_url=None` leaves the raw schema public. Use `openapi_url=None` to remove the schema and, with it, the docs UIs. ```python app = FastAPI(title="Orders API", version="1.0.0", openapi_url=None) ``` If tooling needs the schema, serve it from an authenticated route or export it at build time instead. ## Generate clients from a stable schema ```bash npx @hey-api/openapi-ts -i http://localhost:8000/openapi.json -o src/client ``` FastAPI's docs recommend Hey API for TypeScript clients. Generators must support OpenAPI 3.1. Default operation IDs look like `create_order_orders__post`, so generated method names are ugly; set a clean ID function once. ```python from fastapi.routing import APIRoute def operation_id(route: APIRoute) -> str: return f"{route.tags[0]}-{route.name}" app = FastAPI(generate_unique_id_function=operation_id) ``` The function above assumes every route has a tag. A route that returns `Any` or omits a response type yields an untyped client method. Export the schema in CI and diff it so API changes are reviewed. ## Related - [[backend/fastapi]] - [[coding/python]] - [[backend/fastapi-pydantic]] - [[backend/fastapi-dependencies]] - [[cheatsheets/http-status-codes]] - [[backend/fastapi-background-tasks]]
## FastAPI: Pydantic v2 Models Source: https://llmbestpractices.com/backend/fastapi-pydantic Last updated: 2026-10-01 ## Overview Every FastAPI request body, response body, and parameter set passes through Pydantic. Model design decides whether internal state leaks to clients, whether clients can set server-owned fields, and whether validation runs redundantly. FastAPI 0.142 requires Pydantic 2.9 or later, and this page assumes the v2 API. For the framework baseline see [[backend/fastapi]]; for typing conventions see [[coding/python-typing]]. ## Use separate models for input and output The model that accepts input is not the model that returns output. ```python from datetime import datetime from pydantic import BaseModel, ConfigDict, EmailStr class UserCreate(BaseModel): email: EmailStr password: str class UserRead(BaseModel): model_config = ConfigDict(from_attributes=True) id: str email: EmailStr created_at: datetime ``` Input models accept exactly what the caller may set; `id`, `created_at`, and role flags belong on neither input. Output models expose exactly what the caller may see; `password_hash` and internal status appear on no model. `from_attributes=True` lets an output model be built from an ORM instance. `EmailStr` needs the `email-validator` package, which `fastapi[standard]` installs. ## Validate once, at the HTTP boundary Pydantic runs when FastAPI parses the body. Do not revalidate the same data in the service, repository, and database layers; pass typed model instances or dataclasses between layers. ```python @router.post("/users", status_code=201) async def create_user( payload: UserCreate, db: Annotated[AsyncSession, Depends(get_db)] ) -> UserRead: user = await user_service.create(db, payload) # payload is already validated return UserRead.model_validate(user) ``` The return annotation doubles as the response model. Set `response_model=UserRead` explicitly when the function returns an ORM object or dict and you annotate something else; see [[backend/fastapi-openapi]]. ## Prefer Field constraints; use validators for rules constraints cannot express Declarative constraints appear in the OpenAPI schema and need no code. Validators do not. ```python from pydantic import BaseModel, Field, field_validator, model_validator class OrderCreate(BaseModel): quantity: int = Field(gt=0) unit_price: float = Field(gt=0) coupon: str | None = None @field_validator("coupon") @classmethod def coupon_is_uppercase(cls, v: str | None) -> str | None: if v is not None and v != v.upper(): raise ValueError("coupon must be uppercase") return v @model_validator(mode="after") def total_under_limit(self): if self.quantity * self.unit_price > 10_000: raise ValueError("order total exceeds limit") return self ``` `@field_validator` runs after coercion to the annotated type, so never check `isinstance` on an annotated field. Use `@model_validator(mode="after")` for rules that span fields. ## Name aliases for the wire format, attributes for Python Use an alias generator for `camelCase` clients and keep snake_case attributes. ```python from pydantic import BaseModel, ConfigDict from pydantic.alias_generators import to_camel class InvoiceRead(BaseModel): model_config = ConfigDict( alias_generator=to_camel, validate_by_name=True, validate_by_alias=True, ) invoice_id: str line_item_count: int ``` From Pydantic 2.11, set `validate_by_name=True` with `validate_by_alias=True` instead of `populate_by_name`, which will be deprecated in Pydantic 3. FastAPI serializes responses by alias by default. Pass `by_alias=True` when you call `model_dump()` yourself. ## Compose nested models Model nested structures as classes, not `dict` or `Any`, so the OpenAPI schema renders them and clients are typed. Split a model that grows past about ten fields into sub-models that each capture one concept. ```python class Address(BaseModel): street: str city: str postal_code: str class CustomerCreate(BaseModel): name: str email: EmailStr billing_address: Address shipping_address: Address | None = None ``` ## Parse raw JSON with model_validate_json For queue messages, files, and webhook bodies that FastAPI does not parse, call `Model.model_validate_json(raw_bytes)`. It parses and validates in one pass, which is faster than `model_validate(json.loads(raw))`. `Model.model_json_schema()` exports the schema for contract tests without starting the app. For webhook handlers, verify the signature on the raw bytes first; see [[backend/webhooks]]. ## Related - [[backend/fastapi]] - [[coding/python]] - [[coding/python-typing]] - [[backend/fastapi-dependencies]] - [[backend/fastapi-openapi]] - [[backend/postgres]] - [[backend/webhooks]]
## Database Migrations Source: https://llmbestpractices.com/backend/migrations Last updated: 2026-10-01 ## Overview Treat migrations as code: forward-only, idempotent where practical, versioned with the app, applied by CI before the new binary takes traffic. This page covers the contract, the zero-downtime patterns for [[backend/postgres]], and the commands per framework. The Prisma workflow is in [[backend/prisma-migrations]]. ## Write forward-only migrations and never edit a shipped one - Once a migration lands in `main`, it is immutable; fixes go in a new migration. Edit a shipped file only to fix a syntax error nobody has run. - Use `IF NOT EXISTS` and `IF EXISTS` (`CREATE TABLE`, `ADD COLUMN`, `DROP INDEX`) so a re-run on a half-applied database is a no-op. - Skip `down` scripts in production. A rollback is a new forward migration that restores the old shape, written calmly, not invoked under stress. - Name files with a sortable prefix so order is deterministic everywhere: `20260514_120000_add_orders_idempotency_key.sql`. ## Ship migrations with the app Keep the migration in the same repo and PR as the code that needs it. Run `prisma migrate deploy`, `alembic upgrade head`, or `sqlx migrate run` as a pre-deploy CI step, and block deploys when the database is on a newer schema version than the artifact. ## Use the four-step rename for zero-downtime changes A rename or in-place type change locks the table and breaks old app instances mid-deploy. Split it across deploys. 1. Add the new column, nullable, with no default that touches every row. 2. Dual-write: the application writes both columns. Deploy. 3. Backfill old rows in a separate batched job, then verify with a row count. 4. Move readers to the new column, then drop the old column in a later deploy. The same steps cover type changes, column splits, and replacing a foreign-key target. ## Protect live tables from lock queues Many `ALTER TABLE` forms take an `ACCESS EXCLUSIVE` lock and hold it until the transaction commits; rewrites such as a type change hold it for the whole rewrite. Even a quick `ALTER` can queue behind a long query, and every later query then queues behind it. - Start migrations with `SET lock_timeout = '5s';` so a blocked migration fails and retries instead of freezing traffic. - Build indexes with `CREATE INDEX CONCURRENTLY` ([[backend/postgres-indexes]]). It cannot run in a transaction block, so give it its own migration. In Alembic use `op.get_context().autocommit_block()`. Prisma has no documented way to disable the migration transaction; see [[backend/prisma-migrations]]. - Add `NOT NULL` safely: add a `CHECK (col IS NOT NULL) NOT VALID` constraint, run `VALIDATE CONSTRAINT` (which does not block writes), then `SET NOT NULL`, which skips the table scan once a valid check exists. Postgres 18 can also add `NOT NULL` constraints as `NOT VALID`. - [[backend/postgres-partitioning|Partition very large tables]] so DDL touches one slice at a time. ## Run backfills as separate jobs A migration adds the column and index and finishes in seconds. A worker backfills in batches of 1,000 to 10,000 rows, sleeps between batches, and is resumable, with progress in a `backfill_state` table. Verify with `SELECT count(*) WHERE new_column IS NULL` before the cleanup migration. ## Use the framework's commands - Prisma: `prisma migrate dev` to author, `prisma migrate deploy` in CI. - Python: Alembic. `alembic revision --autogenerate -m "..."`, then edit by hand; autogenerate misses `CHECK` constraints and sees a rename as a drop plus an add. - Rust: `sqlx migrate add` and `sqlx migrate run`, with SQL files as the source of truth. - SQLite: the same contract, but column type changes need a table rebuild (create new table, copy, drop, rename); see [[backend/sqlite]]. See [[coding/general-principles]] for the broader rule on small, reversible deploys. ## Related - [[backend/postgres]] - [[backend/prisma-migrations]] - [[backend/sqlite]] - [[coding/general-principles]] - [[ops/hostinger-vps]]
## Observability Source: https://llmbestpractices.com/backend/observability Last updated: 2026-10-01 ## Overview Observability means answering questions about a running system without shipping a new build. Emit logs, metrics, and traces over OpenTelemetry (OTel), write structured logs from day one, and carry a trace ID through every request. Pick the vendor later; the instrumentation does not change. ## Use all three signals for different questions Logs say what happened (high-cardinality events for forensics). Metrics say how often and how slow (low-cardinality, cheap to dashboard). Traces say where the time went (one request across services). Wire all three: logs cannot show a latency trend, and metrics cannot explain one request. ## Instrument with OpenTelemetry and OTLP - Traces, metrics, and logs are all stable in the OTel specification for the API and protocol. The metrics SDK is still marked mixed, and profiles are in development. - The OTel SDKs (Python, Node, Go, Rust, Java) send OTLP to an OTel Collector, which forwards to Datadog, Honeycomb, Grafana Cloud, Sentry, or a self-hosted stack. Switching backends is a Collector config change. - Use auto-instrumentation for HTTP servers, [[backend/postgres]] clients, gRPC, and [[backend/fastapi]]; add manual spans for business operations. - FastAPI 0.142 and later emits traces, metrics, and logs natively when the OpenTelemetry packages from `fastapi[standard]` are installed. Set `OTEL_SERVICE_NAME` and `OTEL_EXPORTER_OTLP_ENDPOINT`; turn parts off with the `telemetry` argument to `FastAPI(...)`. - Prefer OTel over a vendor SDK as the primary integration, which would tie your wire format to one backend. ## Know which semantic conventions are stable Attribute names come from the semantic conventions (version 1.44.0 at the time of writing). HTTP and database conventions are stable; use `http.request.method`, not the old `http.method`. Instrumentations that predate the stable names emit both during migration when you set `OTEL_SEMCONV_STABILITY_OPT_IN` (for example `http/dup` or `database/dup`); drop the duplicates once dashboards use the new names. GenAI conventions have moved to their own repository; check their status before depending on those attribute names. ## Ship structured logs from day one Emit one JSON object per line with a stable schema, and serialize stack traces into a string field. ```json {"ts":"2026-10-01T12:00:00Z","level":"info","msg":"order.created","order_id":"ord_123","user_id":"usr_42","trace_id":"a1b2c3","span_id":"d4e5","duration_ms":42} ``` - Top-level fields: `ts`, `level`, `msg`, `service`, `env`, `trace_id`, `span_id`. Add domain fields per event. - Use a logger that writes JSON natively: `pino` (Node), `structlog` (Python), `zerolog` (Go). ## Propagate the trace ID on every hop A trace is only useful if every log line and outbound call carries its ID. - Accept the W3C `traceparent` header on inbound HTTP; start a trace if it is missing. - Put the trace ID into the logger context (`structlog.contextvars`, `pino` child loggers). - Forward `traceparent` on outbound HTTP, gRPC, and queue messages. A trace that ends at a service boundary is half a trace. ## Choose the right metric type Use a counter for totals that only increase (`http_requests_total`), a gauge for values that move both ways (`queue_depth`), and a histogram for distributions (`http_request_duration_seconds`). Histograms give percentiles that aggregate across instances; client-side summaries do not. Keep label cardinality low: `user_id` and `request_id` as labels explode the metric store, so put them in logs and traces. ## Build RED for services and USE for resources - RED for each service or route: Rate, Errors, Duration (p50, p95, p99). See [[backend/fastapi]] for route-level shape. - USE for each host or [[ops/hostinger-vps]] instance: Utilization, Saturation, Errors. If a dashboard is neither RED nor USE, state which question it answers. ## Send errors to a dedicated tracker and sample deliberately Sentry, Rollbar, or GlitchTip group exceptions by fingerprint, deduplicate across replicas, and alert on the first occurrence in a release. Initialize the SDK once at process start and tag every error with `release`, `env`, and `trace_id`. Drop `DEBUG` logs in production; use `INFO` for business events, not request chatter, `WARN` for recoverable problems, and `ERROR` for failures that need action. For traces, sample at the head to control volume and add tail-based sampling in the Collector so errored and slow requests are always kept. See [[coding/general-principles]] for cost-aware telemetry. ## Related - [[backend/postgres]] - [[backend/fastapi]] - [[coding/general-principles]] - [[ops/hostinger-vps]] - [[ops/cloudflare]]
## Stripe Payments: Best Practices Source: https://llmbestpractices.com/backend/payments-stripe Last updated: 2026-10-01 ## Overview A secure Stripe integration rests on server-side invariants: webhook signatures verified on the raw body, prices computed on the server, fulfillment driven by events, a pinned API version, and explicit tax ownership. Treat the client as untrusted. The generic webhook pattern is in [[backend/webhooks]]. ## Verify webhooks on the raw request body Verify every event with `stripe.webhooks.constructEvent(rawBody, signature, endpointSecret)` on the exact bytes Stripe sent; parsing or re-serializing the body first breaks the signature. Libraries reject events older than 5 minutes by default; a tolerance of 0 disables that check. Each endpoint has its own `whsec_` secret, and test and live differ. ```ts // Raw body ONLY on this route; mount express.json() after it. app.post("/webhook", express.raw({ type: "application/json" }), (req, res) => { let event: Stripe.Event try { event = stripe.webhooks.constructEvent(req.body, req.headers["stripe-signature"] as string, endpointSecret) } catch (err) { return res.status(400).send(`Signature verification failed: ${(err as Error).message}`) } enqueue(event) // slow work happens asynchronously res.json({ received: true }) }) ``` In a Next.js route handler, pass `await req.text()`, never `req.json()`. Exempt the route from CSRF middleware in Rails, Django, and similar. ## Pin the API version and expect messy delivery - The current API version is `2026-09-30.endive`. Since `2024-09-30.acacia`, Stripe ships monthly versions without breaking changes and a major release twice a year. - A webhook endpoint's payloads use the API version set when it was created (else the account default), and stored events never change. Match it to the version your SDK pins; `stripe-node` 12 and later pin the version current at release. - Delivery is at least once and unordered. Dedupe on event ID (plus `data.object.id` and `type` for the rare double event), never order by `created`, and fetch the object when you need current state. - Return `2xx` before slow work; redirects count as failures. Live mode retries for up to three days; sandbox retries three times over a few hours. - A rolled secret stays valid for up to 24 hours; accept either. Thin events (API v2) need a separate endpoint. ## Set the price on the server Create the PaymentIntent or Checkout Session on the server and compute the amount from trusted data. `amount` is a positive integer in the smallest currency unit (1099 is $10.99) and `currency` a lowercase ISO code. Look prices up by price ID and reject any amount the client sends. ## Make create calls idempotent Send an `Idempotency-Key` on every `POST`. Stripe stores the first request's status and body for the key, including `500` errors, and replays them. Keys are up to 255 characters, pruned after at least 24 hours, and error if reused with different parameters. Use a V4 UUID or a per-operation value such as an order ID, never sensitive data. `GET` and `DELETE` need none. ```ts await stripe.paymentIntents.create({ amount: priceFromDb, currency: "usd" }, { idempotencyKey: orderId }) ``` ## Use restricted keys Give each service a restricted key with only the permissions it needs, not an unrestricted `sk_` key. Limit keys to known IP addresses where egress is stable, rotate on a schedule, and block `sk_live_` and `rk_live_` strings with a pre-commit hook. Keep keys in a vault ([[ops/secrets-and-env]]). ## Fulfill from events, idempotently A customer can pay and lose connectivity before your landing page loads, so webhooks are required. For Checkout, handle `checkout.session.completed` and, for delayed methods such as bank debits, `checkout.session.async_payment_succeeded` (and `async_payment_failed`). The fulfillment function retrieves the Session with `line_items` expanded, acts only if `payment_status` is not `unpaid`, and records that it fulfilled. Also call it from the success page so a present customer gets access at once. It will run more than once, possibly concurrently, so make it safe to repeat. With a `success_url` set, Checkout waits up to 10 seconds for your webhook to answer before redirecting. Tie fulfillment to your own user records ([[backend/auth-sessions]]). ## Own the tax liability unless you use Managed Payments By default you register for, collect, file, and remit sales tax, VAT, and GST. Stripe Tax calculates and helps file but does not take liability. Stripe Managed Payments is the merchant-of-record product: Stripe is the seller of record and handles indirect tax on covered sales. Confirm the setup on the [[ops/pre-launch-checklist]]. ## Related - [[backend/webhooks]]: the general raw-body and replay-safety pattern. - [[ops/secrets-and-env]]: storing live and test keys per environment. - [[backend/auth-sessions]]: tying payments to authenticated users. - [[backend/fastapi]]: the Python raw-body route. - [[coding/python-security]]: input-trust rules for amounts. - [[ops/pre-launch-checklist]]: go-live tax and key-rotation gates.
## Postgres Best Practices Source: https://llmbestpractices.com/backend/postgres Last updated: 2026-10-01 ## Overview Use PostgreSQL 18 as the default relational store for app data. 18 shipped on 2025-09-25 and 18.6 is the current minor release; 19 is in beta with general availability planned for October 2026; 14 reaches end of life on 2026-11-12. This page holds the cross-cutting rules, and each topic below has its own page. For the head-to-head with MySQL, see [[comparisons/postgres-vs-mysql]]. ## Use the Postgres 18 features that change decisions Check `SHOW server_version` before relying on any of these. | Change in 18 | Rule | | --- | --- | | `uuidv7()` | Native time-ordered UUID. Use it for UUID primary keys. | | B-tree skip scan | A multicolumn index can serve a query with no equality on its leading column. See [[backend/postgres-indexes]]. | | Async I/O | `io_method` defaults to `worker`; `io_uring` needs a build with liburing. It speeds sequential scans, bitmap heap scans, and vacuum. | | Virtual generated columns | `GENERATED ALWAYS AS (expr)` is virtual by default. A virtual column cannot be indexed or logically replicated; write `STORED` when you need either. | | `EXPLAIN ANALYZE` | Reports `BUFFERS` without being asked. See [[backend/postgres-explain]]. | | `pg_upgrade` | Keeps planner statistics, but not extended statistics. Afterwards run `vacuumdb --all --analyze-in-stages --missing-stats-only`, then `vacuumdb --all --analyze-only`. | ## Choose primary keys that insert in order Use `bigint GENERATED ALWAYS AS IDENTITY` when one database assigns IDs. Use `uuidv7()` when clients or services generate IDs, or when IDs leave the system. Avoid random UUIDv4 (`gen_random_uuid()`) as a primary key: inserts land across the whole B-tree, causing page splits and poor cache locality. Before 18, generate UUIDv7 in the application. ## Keep one source of truth per fact `users.email` lives on `users`, not copied onto `orders`. - Start with 3NF. Use [[glossary/foreign-key|foreign keys]] with explicit `ON DELETE`, not application-layer cascades. - Denormalize only for a measured read pattern, and document the job that keeps the copy in sync. - Use `CHECK`, `NOT NULL`, and domain types. Do not push all validation into [[backend/fastapi]] or [[backend/prisma]]. - Take locks in a consistent order across transactions; see [[glossary/deadlock]]. ## Pool connections in transaction mode Postgres runs one process per connection, so hundreds of application connections starve the server. Put PgBouncer in front of any service with more than a handful of workers. - Use transaction pooling for web workloads. Use session pooling only when clients need session state: `LISTEN/NOTIFY`, session-level advisory locks, or `SET` without `LOCAL`. - PgBouncer 1.21 and later supports protocol-level prepared statements in transaction mode when `max_prepared_statements` is above zero. - See [[backend/prisma-pooling]] for the Prisma settings and [[backend/supabase]] for Supavisor. ## Send each topic to its page - Indexes: [[backend/postgres-indexes]]. JSONB: [[backend/postgres-jsonb]]. Full-text search: [[backend/postgres-full-text-search]]. - Slow queries: [[backend/postgres-explain]], [[cheatsheets/postgres-explain]], [[howto/debug-postgres-slow-query]], [[cheatsheets/postgres-window-functions]]. - Maintenance and scale: [[backend/postgres-vacuum]], [[backend/postgres-partitioning]], [[backend/postgres-replication]]. - Schema changes: [[backend/migrations]]. Row-level security on Supabase: [[backend/supabase-rls]]. ## Related - [[backend/prisma]] - [[backend/sqlite]] - [[backend/fastapi]] - [[backend/postgres-indexes]] - [[backend/postgres-explain]] - [[backend/postgres-vacuum]] - [[backend/postgres-jsonb]] - [[comparisons/postgres-vs-mysql]]
## Postgres: EXPLAIN and Query Plans Source: https://llmbestpractices.com/backend/postgres-explain Last updated: 2026-10-01 ## Overview Read the plan before you tune. The planner picks scans and join orders from statistics, and `EXPLAIN` shows what it chose, so the plan answers "why is this slow?". This page covers running `EXPLAIN`, reading nodes, and the `pg_stat_statements` loop. The umbrella rules live in [[backend/postgres]]; a node-by-node reference is in [[cheatsheets/postgres-explain]]. ## Run EXPLAIN ANALYZE and read BUFFERS `EXPLAIN` shows the estimate without running the query. `EXPLAIN ANALYZE` runs it and adds actual rows, time, and loops. Since Postgres 18, `ANALYZE` reports `BUFFERS` (cache hits and reads) automatically; name it anyway so the command behaves the same on older servers. ```sql EXPLAIN (ANALYZE, BUFFERS) SELECT id, total_cents FROM orders WHERE user_id = $1 AND created_at > now() - interval '30 days' ORDER BY created_at DESC LIMIT 50; ``` - `ANALYZE` executes the statement. For `INSERT`, `UPDATE`, or `DELETE`, wrap it in `BEGIN; ... ROLLBACK;`. - Add `WAL` for WAL records per node, and `SERIALIZE` to include the cost of converting output rows (including TOAST fetches). `SERIALIZE` explains a query whose plan is fast but whose client is slow on wide rows. - For a parameterized query (`$1`) with no values at hand, `EXPLAIN (GENERIC_PLAN)` shows the plan a prepared statement would reuse. It cannot combine with `ANALYZE`. ## Read the plan top-down and compute totals The root is what the client receives; the leaves are scans. Rows flow upward. - `actual time` and `actual rows` are per-loop averages. Multiply by `loops` for totals. - `Buffers: shared hit=N read=M` separates cache hits from reads. A `read` can still be served by the OS page cache. - Start at the node with the largest total actual time, fix it, and re-run. - `cost` is in planner units, not seconds. Compare costs as ratios between nodes. Low cost with high actual time usually means a bad row estimate. ## Compare estimated rows to actual rows A gap of roughly an order of magnitude between `rows=` (estimate) and `actual rows=` is the usual sign of a wrong plan. ```text Index Scan using orders_user_id_idx on orders (cost=0.43..2.50 rows=1 width=20) (actual time=0.04..18.23 rows=4200 loops=1) ``` This node estimated one row and returned 4,200, so a parent nested loop chosen on that estimate is probably the wrong join. Run `ANALYZE orders`, or raise `ALTER TABLE ... ALTER COLUMN ... SET STATISTICS` on skewed columns; see [[backend/postgres-vacuum]] for the analyze schedule. ## Match each scan type to its use - Sequential scan: right for small tables or queries returning most rows; wrong for selective predicates on large tables. - Index scan: right for selective predicates; wrong when the index returns most rows, because random heap reads cost more than a sequential scan. - Index-only scan: skips the heap for pages the visibility map marks all-visible, so watch `Heap Fetches` and keep the table vacuumed. See [[backend/postgres-indexes]] for `INCLUDE`. - Bitmap heap scan: builds a page bitmap from one or more indexes, then visits each page once. It wins for moderately selective predicates on scattered rows. ## Separate cold-cache problems from plan problems A query that is fast warm and slow cold has an I/O problem. Run it twice. If `read` drops to near zero the second time, the first run was a cold start. If `read` stays high, the working set exceeds `shared_buffers` and the OS cache: add a covering index that shrinks the working set, or partition cold history ([[backend/postgres-partitioning]]). ## Feed pg_stat_statements into the loop `pg_stat_statements` records normalized text, calls, and timing per query. Add it to `shared_preload_libraries`, restart, then `CREATE EXTENSION pg_stat_statements;`. ```sql SELECT calls, mean_exec_time, total_exec_time, query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20; ``` Sort by `total_exec_time`; mean time hides queries that are cheap but run constantly. Attribute the pain back to routes with [[backend/observability]] and the slow-query log. ## Related - [[backend/postgres]] - [[backend/postgres-indexes]] - [[backend/postgres-jsonb]] - [[backend/postgres-vacuum]] - [[backend/postgres-partitioning]] - [[backend/observability]] - [[cheatsheets/postgres-explain]]
## Postgres: Full-Text Search Source: https://llmbestpractices.com/backend/postgres-full-text-search Last updated: 2026-10-01 ## Overview Postgres full-text search is enough when search is a feature inside a transactional system. `tsvector` holds tokenized, stemmed terms, `tsquery` expresses the search, and a GIN index makes it fast; `pg_trgm` adds fuzzy and substring matching. This page covers the pipeline, weighting, ranking, and the point where a dedicated engine wins. The umbrella rules live in [[backend/postgres]]. ## Store a weighted tsvector in a STORED generated column A generated column keeps the `tsvector` in sync with its source. Write `STORED` explicitly: since Postgres 18, generated columns are virtual by default, and a virtual column cannot be indexed. ```sql ALTER TABLE articles ADD COLUMN search tsvector GENERATED ALWAYS AS ( setweight(to_tsvector('english', coalesce(title, '')), 'A') || setweight(to_tsvector('english', coalesce(body, '')), 'B') ) STORED; CREATE INDEX articles_search_gin ON articles USING GIN (search); ``` `to_tsvector('english', ...)` lowercases, drops stop words, and stems (`running` to `run`). For multilingual content, store the language in a `regconfig` column and pass it to `to_tsvector`. Use the same configuration at query time; mismatched configurations silently miss results. See [[backend/postgres-indexes]] for the index decision tree. ## Query with websearch_to_tsquery Pick the parser by input source: `websearch_to_tsquery` for end-user text (quotes, `-` exclusion, `or`), `plainto_tsquery` to require all words, `phraseto_tsquery` for ordered phrases. ```sql SELECT id, title FROM articles WHERE search @@ websearch_to_tsquery('english', 'postgres -mysql "full text"') LIMIT 50; ``` ## Weight fields and rank results `setweight` tags tokens A to D. The default `ts_rank` weights are `{0.1, 0.2, 0.4, 1.0}` for `{D, C, B, A}`; pass a custom array as the first argument to change them. Use A for titles, B for body text, and C or D for metadata and comments. `ts_rank_cd` also accounts for term proximity. The normalization bitmask controls document length effects: `0` ignores length (the default), `2` divides by document length, `16` divides by `1 + log(unique words)`, and `32` divides the rank by itself plus 1 to map it into 0 to 1. ```sql SELECT id, title, ts_rank_cd(search, query, 32) AS rank FROM articles, websearch_to_tsquery('english', $1) AS query WHERE search @@ query ORDER BY rank DESC LIMIT 50; ``` Ranking is the part teams outgrow first. Postgres ranks by term frequency and proximity and has no BM25. ## Use pg_trgm for fuzzy and substring search `pg_trgm` indexes three-character chunks. It supports similarity (`%`), fast `ILIKE '%foo%'`, and typo-tolerant matching. ```sql CREATE EXTENSION pg_trgm; CREATE INDEX users_name_trgm ON users USING GIN (name gin_trgm_ops); SELECT id, name, similarity(name, $1) AS score FROM users WHERE name % $1 ORDER BY score DESC LIMIT 10; ``` Use it for autocomplete, name search, and SKU lookup. It does not stem, so combine it with `tsvector` when both fuzzy and stemmed matching matter. ## Debug zero-result searches with ts_debug A bad text search configuration shows up as no results. Run `SELECT * FROM ts_debug('english', 'running quickly');` on a known document to see how each token is classified, and `\dF` in `psql` to list available configurations. ## Switch engines when search is the product Move to Elasticsearch, OpenSearch, Meilisearch, or Typesense when you need synonyms, per-field analyzers, BM25 relevance, rich faceting, geo-relevance, or very large corpora at low latency. Move to a vector store when relevance is semantic rather than lexical; see [[ai-agents/rag]]. A hybrid works: keep Postgres as the source of truth, stream changes with logical replication or CDC into the search engine, and hydrate results from the database. Before blaming Postgres, check the plan with [[backend/postgres-explain]]; most slow-search tickets are a missing index. ## Related - [[backend/postgres]] - [[backend/postgres-indexes]] - [[backend/postgres-jsonb]] - [[backend/postgres-explain]] - [[backend/postgres-partitioning]] - [[ai-agents/rag]]
## Postgres: Indexes Source: https://llmbestpractices.com/backend/postgres-indexes Last updated: 2026-10-01 ## Overview Choose the index type from the query shape: B-tree for equality, range, and order; GIN for JSONB, arrays, and full-text; GiST for ranges and nearest-neighbor; BRIN for huge append-only tables. Postgres 18 also ships hash and SP-GiST. The umbrella rules live in [[backend/postgres]]. ## Use B-tree by default for equality, range, and order B-tree handles `=`, `<`, `>`, `BETWEEN`, `IN`, `IS NULL`, and `ORDER BY`. It is what `CREATE INDEX` builds without a `USING` clause. ```sql CREATE INDEX orders_user_id_created_at_idx ON orders (user_id, created_at DESC); ``` Put equality columns first, then the range or sort column. Before Postgres 18, an index on `(a, b)` could not serve a query on `b` alone. Postgres 18 skip scan can, but it pays off only when `a` has few distinct values; still build the prefix your queries actually filter on, and read the index-lookup count that `EXPLAIN ANALYZE` now reports per index scan node. ## Use GIN for JSONB, full-text, and arrays GIN is an inverted index for columns holding many searchable values per row. - JSONB: the default `jsonb_ops` class supports `?`, `?|`, `?&`, `@>`, `@?`, and `@@`. `jsonb_path_ops` drops the key-existence operators and in return is smaller and faster. See [[backend/postgres-jsonb]]. - Full-text: index a stored `tsvector` column; see [[backend/postgres-full-text-search]]. - Arrays: `CREATE INDEX posts_tags_gin ON posts USING GIN (tags);` supports `&&` and `@>`. GIN is slow to build and update. Postgres 18 can build GIN indexes in parallel; bulk loads are still faster with the index dropped and recreated afterwards. ## Use GiST for ranges, spatial data, and exclusion constraints GiST supports overlap and nearest-neighbor queries on ranges and geometries. ```sql CREATE EXTENSION IF NOT EXISTS btree_gist; -- needed for "room_id WITH =" in GiST ALTER TABLE bookings ADD CONSTRAINT no_overlap EXCLUDE USING GiST (room_id WITH =, during WITH &&); ``` GiST also backs PostGIS and `pg_trgm` similarity search; see [[backend/postgres-full-text-search]]. ## Use BRIN only when row order matches the column BRIN stores one summary per block range, so a terabyte table needs kilobytes of index. It works only when physical row order correlates with the indexed column, such as append-only timestamps. On randomly ordered data it degrades to a sequential scan. ```sql CREATE INDEX events_created_at_brin ON events USING BRIN (created_at) WITH (pages_per_range = 32); ``` The default is 128 pages per range; smaller ranges give finer pruning and a larger index. Pair BRIN with [[backend/postgres-partitioning]] so inserts stay in order. ## Add partial, expression, and covering indexes for specific queries - Partial: exclude the rows you never query. `CREATE INDEX users_active_email_idx ON users (email) WHERE deleted_at IS NULL;` The planner uses it only when the query predicate implies the index predicate. - Expression: index what the query filters on. `CREATE INDEX users_lower_email_idx ON users (lower(email));` matches `WHERE lower(email) = lower($1)` and nothing that writes the expression differently. - Covering: `INCLUDE` adds non-key columns so index-only scans skip the heap. `INCLUDE` columns do not affect ordering or uniqueness. Use it on hot read paths, not on every index. ## Build and drop indexes CONCURRENTLY on live tables A plain `CREATE INDEX` blocks writes to the table (reads continue) until the build finishes. `CONCURRENTLY` builds without blocking inserts, updates, or deletes. ```sql CREATE INDEX CONCURRENTLY orders_user_id_idx ON orders (user_id); DROP INDEX CONCURRENTLY orders_old_idx; ``` `CREATE INDEX CONCURRENTLY` cannot run inside a transaction block, so give it its own migration; see [[backend/migrations]] and [[backend/prisma-migrations]]. A failed build leaves an `INVALID` index that still costs write overhead. Drop it and retry, or run `REINDEX INDEX CONCURRENTLY`. ## Drop indexes you do not use Every index costs write amplification, vacuum work, and storage. Prefer one composite index that serves several queries over many single-column indexes. Drop indexes with zero scans in `pg_stat_user_indexes` after a full business cycle, and check every replica first because the counters are per server. Validate each new index with [[backend/postgres-explain]]; the end-to-end loop is in [[howto/debug-postgres-slow-query]]. ## Related - [[backend/postgres]] - [[backend/postgres-explain]] - [[backend/postgres-jsonb]] - [[backend/postgres-full-text-search]] - [[backend/postgres-partitioning]] - [[backend/migrations]]
## Postgres: JSONB Source: https://llmbestpractices.com/backend/postgres-jsonb Last updated: 2026-10-01 ## Overview Use JSONB for rows whose shape varies, and promote any key you filter or sort on to a column. `jsonb` stores a decomposed binary form that supports containment queries, GIN indexes, and subscripting; `json` keeps the raw text and reparses it on every access. The umbrella rules live in [[backend/postgres]]. ## Prefer jsonb over json `json` preserves whitespace, key order, and duplicate keys. `jsonb` drops whitespace, does not preserve key order, and keeps only the last value of a duplicate key. Use `json` only to round-trip the exact text a third party sent. ```sql ALTER TABLE webhook_events ADD COLUMN payload JSONB NOT NULL; ``` ## Use JSONB for variable-shape data, not a column-per-key bag JSONB fits payloads whose schema is set by someone else: webhook bodies, audit events, third-party API responses, model output, dynamic form answers. It does not fit a fixed set of attributes such as `{ theme, locale, timezone }` hidden in one column. For ancestry data, the [[glossary/materialized-path]] pattern pairs well with a JSONB metadata column. ## Know the operators - `->` returns JSONB; `->>` returns text. `payload -> 'user' ->> 'id'`. - `#>` and `#>>` walk a path: `payload #>> '{user,address,country}'`. - `@>` tests containment: `payload @> '{"status": "paid"}'`. GIN-indexable. - `?` tests top-level key existence. GIN-indexable with the default class only. - `@?` and `@@` evaluate `jsonpath`: `payload @? '$.line_items[*] ? (@.amount_cents > 10000)'`. - Subscripting reads and writes: `payload['status']`, `UPDATE t SET payload['status'] = '"paid"'`. Cast `->>` results for typed predicates: `(payload ->> 'amount')::int > 1000`. In Postgres 17 and later, `JSON_TABLE()`, `JSON_QUERY()`, `JSON_VALUE()`, and `JSON_EXISTS()` follow the SQL/JSON standard; use `JSON_TABLE` to turn an array of objects into rows instead of `jsonb_array_elements` plus a lateral join. ## Index with GIN or a B-tree expression - Containment and `jsonpath` on many keys: `CREATE INDEX ON webhook_events USING GIN (payload jsonb_path_ops);`. This class is smaller and faster than the default and supports `@>`, `@?`, and `@@`. - Key-existence operators (`?`, `?|`, `?&`): use the default `jsonb_ops` class. - Equality or range on one key: `CREATE INDEX ON orders ((payload ->> 'status'));`. The query must use the same expression. See [[backend/postgres-indexes]] for the full decision tree and confirm the planner uses the index with [[backend/postgres-explain]]. ## Promote hot keys to real columns When a key appears in `WHERE`, `ORDER BY`, or `GROUP BY` on a hot path, promote it. Columns carry statistics, `NOT NULL`, `CHECK`, and foreign keys; JSONB paths carry none of them, and the planner estimates them poorly. ```sql ALTER TABLE orders ADD COLUMN status TEXT; -- backfill in batches outside the migration: UPDATE orders SET status = payload ->> 'status' WHERE status IS NULL AND id BETWEEN $1 AND $2; ALTER TABLE orders ALTER COLUMN status SET NOT NULL; ``` Run the backfill as a batched job; see [[backend/migrations]]. ## Watch the size of large documents Values over about 2 KB are TOASTed out of line, so `SELECT *` on a JSONB-heavy table pulls TOAST data per row. Select only the keys you need. `default_toast_compression` is `pglz`; `lz4` is available per column when Postgres was built with LZ4: `ALTER TABLE events ALTER COLUMN payload SET COMPRESSION lz4;`. Measure with `pg_column_size(payload)` on representative rows, and partition cold history if documents are large; see [[backend/postgres-partitioning]]. ## Related - [[backend/postgres]] - [[backend/postgres-indexes]] - [[backend/postgres-explain]] - [[backend/postgres-full-text-search]] - [[backend/postgres-partitioning]] - [[backend/migrations]]
## Postgres: Partitioning Source: https://llmbestpractices.com/backend/postgres-partitioning Last updated: 2026-10-01 ## Overview Partition tables that are very large or that need retention by dropping old data; do not partition medium tables. A partitioned table has no rows of its own; child partitions hold the data and the planner prunes the ones a query cannot touch. This page covers when to partition, the three strategies, the `pg_partman` workflow, and the constraints that surprise people. The umbrella rules live in [[backend/postgres]]. ## Partition when the table is large or retention needs it The Postgres docs give a rule of thumb: partitioning pays off when the table would otherwise exceed the physical memory of the server. Partition earlier when retention must be a `DROP`, or when vacuum on one huge table cannot keep up ([[backend/postgres-vacuum]]). Do not partition lookup, configuration, or low-churn tables; each partition adds planning cost, and the planner handles up to a few thousand partitions fairly well. Merging partitions back into one table later requires a rewrite. ## Pick range partitioning for time-series data Range partitioning splits on a continuous column, usually a timestamp. Use it when queries filter on that column and rows arrive in order. Monthly partitions are a sensible default for events, logs, and audits. ```sql CREATE TABLE events ( id bigint GENERATED ALWAYS AS IDENTITY, created_at timestamptz NOT NULL, user_id bigint NOT NULL, payload jsonb NOT NULL, PRIMARY KEY (id, created_at) ) PARTITION BY RANGE (created_at); CREATE TABLE events_2026_10 PARTITION OF events FOR VALUES FROM ('2026-10-01') TO ('2026-11-01'); ``` Every primary key or unique constraint on a partitioned table must include all partition key columns, and the key cannot use expressions. Uniqueness is enforced per partition, so "globally unique id" needs the partition column in the key. An insert with no matching partition fails unless a `DEFAULT` partition exists. ## Pick list or hash partitioning for non-time splits - List: map discrete values (region, a small set of tenants) to partitions. Add a `DEFAULT` partition for unknown values. Avoid it when the value set is large or unbounded. - Hash: `PARTITION BY HASH (key)` spreads writes evenly across N partitions. Pruning works only for equality on the key, so use it for point lookups, not range scans. A `DEFAULT` partition has a cost: attaching a new partition scans the default under an `ACCESS EXCLUSIVE` lock unless a `CHECK` constraint proves it holds no matching rows. Add that constraint before attaching. ## Automate the lifecycle with pg_partman `pg_partman` 5.x creates future partitions and removes old ones. It requires Postgres 14 or later and native partitioning only. ```sql CREATE EXTENSION pg_partman; SELECT partman.create_parent( p_parent_table => 'public.events', p_control => 'created_at', p_interval => '1 month', p_premake => 4 ); UPDATE partman.part_config SET retention = '12 months', retention_keep_table = false WHERE parent_table = 'public.events'; ``` Call `partman.run_maintenance_proc()` from `pg_cron`, an external scheduler, or the background worker; nothing creates partitions until maintenance runs. `retention_keep_table` defaults to `true`, which only detaches expired partitions and keeps the tables. Set it to `false` to drop them. ## Rely on partition pruning, and verify it Pruning is automatic when the query filters on the partition key. Constants and stable expressions such as `now() - interval '7 days'` are pruned at executor startup; volatile functions are not. ```sql EXPLAIN SELECT * FROM events WHERE created_at >= '2026-10-01' AND created_at < '2026-10-08'; -- Append node scans only events_2026_10 ``` Check with [[backend/postgres-explain]]: an `Append` over every partition means the predicate did not allow pruning, and `Subplans Removed` shows what startup pruning dropped. ## Know the row and key behavior - Updating the partition key moves the row to the matching partition automatically. - Foreign keys can reference partitioned tables and originate from them; the referenced unique key must include the partition key. - Exclusion constraints must include the partition key columns and compare them for equality. - Detach old data without blocking: `ALTER TABLE events DETACH PARTITION events_2025_09 CONCURRENTLY;`, then drop or archive it. See [[backend/postgres-indexes]] for BRIN on partitioned time-series and [[backend/migrations]] for rollout discipline. ## Related - [[backend/postgres]] - [[backend/postgres-indexes]] - [[backend/postgres-vacuum]] - [[backend/postgres-explain]] - [[backend/postgres-replication]] - [[backend/migrations]]
## Postgres: Replication Source: https://llmbestpractices.com/backend/postgres-replication Last updated: 2026-10-01 ## Overview Postgres has two replication models. Streaming (physical) replication ships WAL to a byte-identical replica. Logical replication decodes WAL into row changes for selected tables. They solve different problems: pick streaming for read scale and failover, logical for partial copies and low-downtime upgrades. The umbrella rules live in [[backend/postgres]] and the production playbook in [[ops/postgres-prod]]. ## Use streaming replication for read replicas and standbys The primary ships WAL to replicas, which apply it in order. It carries every change, including DDL. The defaults (`wal_level = replica`, `max_wal_senders = 10`) already allow it. ```bash pg_basebackup -h primary -D /var/lib/postgresql/data -R -C -S replica1 -X stream ``` `-R` writes the recovery configuration, and `-C -S` creates the replication slot `replica1`. The slot makes the primary keep the WAL the replica still needs, so a disconnected replica does not fall off the retention window. Use it for a [[glossary/read-replica|read replica]], a failover candidate, or a base for backups. ## Bound the WAL a slot can retain A slot whose consumer is gone keeps WAL forever and can fill the primary's disk. Set `max_slot_wal_keep_size` to cap retention (the default is unlimited). Postgres 18 adds `idle_replication_slot_timeout` to invalidate slots that stay inactive. Alert on inactive slots in `pg_replication_slots`, and drop slots for decommissioned replicas. ## Use logical replication for selective copies and upgrades Logical replication uses publications and subscriptions. It crosses major versions, copies only the tables you publish, and allows writable subscribers. ```sql -- primary CREATE PUBLICATION analytics FOR TABLE orders, order_items; -- subscriber CREATE SUBSCRIPTION analytics_sub CONNECTION 'host=primary dbname=app user=replica password=...' PUBLICATION analytics; ``` Use it for low-downtime major-version upgrades, shipping tables to a reporting store, and sharding migrations. Know the limits in Postgres 18: - DDL is not replicated; apply schema changes to both sides yourself. - Sequence data is not replicated. Identity and serial columns copy as table data, but the subscriber's sequence still shows the start value. - Large objects are not replicated, and only tables (including partitioned tables) can be published. - Stored generated columns replicate when you set `publish_generated_columns` or list them in a column list; virtual generated columns do not. - The subscription `streaming` option (14 and later) sends long in-progress transactions to the subscriber instead of spilling them to disk on the publisher until commit. Its default is `off` before Postgres 18 and `parallel` in 18, which applies them with parallel apply workers. Since Postgres 17, logical slots can fail over to a standby, and `pg_upgrade` carries valid logical slots and subscriptions forward from a 17 or later cluster. ## Treat replicas as read scale, not backups A `DROP TABLE` on the primary replays on the replica within seconds, and a corrupted page replicates as corruption. Recover from operator error with base backups plus archived WAL (point-in-time recovery); see [[ops/postgres-prod]]. Postgres 17 added incremental base backups (`pg_basebackup --incremental`, `pg_combinebackup`). Use [[glossary/read-replica|replicas]] for reporting, search indexing, and failover. ## Monitor lag in bytes and seconds ```sql -- on the primary SELECT application_name, pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes, write_lag, flush_lag, replay_lag FROM pg_stat_replication; ``` Alert when `replay_lag` exceeds the staleness your reads can tolerate, and alert on `lag_bytes` growth for failover candidates. Wire both into [[backend/observability]] before the first stale-read incident. ## Use async by default and sync per transaction Async commits do not wait for replicas, so a primary failure can lose up to the lag. With `synchronous_standby_names` set, a synchronous commit waits for the named standbys and adds a round trip. Request it per transaction for the few writes that need it, because a global setting stalls every commit when the sync standby disconnects. ```sql SET LOCAL synchronous_commit = remote_apply; -- visible to queries on the standby ``` `remote_write` is the cheapest level, `on` waits for the standby's flush, and `remote_apply` is the strongest. ## Trade hot_standby_feedback against bloat A long replica query can conflict with vacuum on the primary. `hot_standby_feedback = on` makes the primary keep the rows the replica still needs: no cancellations, but bloat on the primary ([[backend/postgres-vacuum]]). Keep it off on OLTP read replicas; turn it on for a dedicated analytics replica. ## Drill the failover Postgres has no built-in automatic failover; use Patroni or your provider's. The manual path: promote with `pg_ctl promote` or `pg_promote()`, repoint the pool, re-clone the old primary as a replica. Rehearse it quarterly. A topology that has never failed over is theoretical. ## Related - [[backend/postgres]] - [[ops/postgres-prod]] - [[backend/postgres-vacuum]] - [[backend/postgres-partitioning]] - [[backend/observability]] - [[backend/migrations]]
## Postgres: Vacuum and Bloat Source: https://llmbestpractices.com/backend/postgres-vacuum Last updated: 2026-10-01 ## Overview Vacuum reclaims dead tuples and refreshes planner statistics. Every `UPDATE` and `DELETE` leaves a dead tuple behind under multi-version concurrency control, and autovacuum clears them on a schedule. The defaults suit small tables and fall behind on hot ones. The umbrella rules live in [[backend/postgres]] and the production playbook in [[ops/postgres-prod]]. ## Tune autovacuum per table on hot writes Autovacuum starts when dead tuples exceed `autovacuum_vacuum_threshold` (50) plus `autovacuum_vacuum_scale_factor` (0.2) times the row count. Postgres 18 caps that trigger at `autovacuum_vacuum_max_threshold` (100 million dead tuples), so a billion-row table no longer waits for 200 million. Lower the scale factor on high-churn tables. ```sql ALTER TABLE events SET ( autovacuum_vacuum_scale_factor = 0.02, autovacuum_analyze_scale_factor = 0.01 ); ``` Confirm with `pg_stat_user_tables.last_autovacuum` that the table is vacuumed often enough. Run `VACUUM (ANALYZE)` by hand after a bulk load or large delete instead of waiting for the thresholds. ## Raise the cost budget before adding workers Autovacuum throttles itself with a cost budget: `autovacuum_vacuum_cost_limit` defaults to -1, which uses `vacuum_cost_limit` (200), with `autovacuum_vacuum_cost_delay` at 2 ms. The budget is shared across running workers, so adding workers alone does not speed anything up. ```sql ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 2000; ALTER SYSTEM SET autovacuum_max_workers = 6; SELECT pg_reload_conf(); ``` On Postgres 18, `autovacuum_max_workers` changes with a reload, up to `autovacuum_worker_slots` (default 16, restart to change). Before 18, changing it needs a restart. Watch `pg_stat_progress_vacuum`; a vacuum that takes longer than the table's churn interval falls behind permanently. Postgres 18 also lets normal vacuums freeze some all-visible pages early (`vacuum_max_eager_freeze_failure_rate`, default 0.03), which cuts later anti-wraparound work. ## Detect bloat from dead tuples ```sql SELECT relname, n_live_tup, n_dead_tup, round(n_dead_tup::numeric / nullif(n_live_tup, 0), 3) AS dead_ratio, last_autovacuum FROM pg_stat_user_tables WHERE n_dead_tup > 10000 ORDER BY dead_ratio DESC NULLS LAST; ``` A dead ratio near or above the 0.2 default scale factor means autovacuum is not keeping up. Use the `pgstattuple` extension for accurate table and index bloat, and export the metric to [[backend/observability]] so on-call sees a dashboard instead of running ad-hoc queries. ## Avoid VACUUM FULL in production `VACUUM FULL` rewrites the table and holds an `ACCESS EXCLUSIVE` lock, blocking reads and writes for the whole rewrite. On a 200 GB table that is an outage. Routine bloat needs tuned autovacuum, not a rewrite. Use `TRUNCATE` when the rows are not needed. ## Use pg_repack for online cleanup `pg_repack` rewrites a bloated table or its indexes online: it copies into a shadow table, captures concurrent changes with a trigger, and swaps under a brief lock. The table needs a primary key or a unique index on `NOT NULL` columns, and the rewrite needs free disk roughly equal to the table size. ```bash pg_repack -h db.internal -U postgres -d app_prod -t events pg_repack -h db.internal -U postgres -d app_prod --only-indexes -t events ``` Run it at low traffic and watch replication lag; see [[backend/postgres-replication]]. ## Prevent transaction ID wraparound Transaction IDs are 32-bit. Tables must be vacuumed within about 2 billion transactions, and the server stops accepting commands when fewer than 3 million remain. Autovacuum forces an anti-wraparound vacuum once a table's unfrozen age passes `autovacuum_freeze_max_age` (default 200 million). ```sql SELECT datname, age(datfrozenxid) AS xid_age FROM pg_database ORDER BY age(datfrozenxid) DESC; ``` Alert while the age is still far from the limit; an age that keeps climbing past `autovacuum_freeze_max_age` means vacuum is blocked or failing. The usual blockers are long-running transactions, abandoned replication slots, and orphaned prepared transactions. Monitor multixact age the same way. ## Partition cold history Vacuum cost grows with table size, not churn. A terabyte append-only table vacuums slowly even when only the last day changes. Range-partition by time so old partitions stay static, and drop the oldest with `DROP TABLE` instead of deleting rows; see [[backend/postgres-partitioning]]. ## Related - [[backend/postgres]] - [[backend/postgres-explain]] - [[backend/postgres-partitioning]] - [[backend/postgres-replication]] - [[ops/postgres-prod]] - [[backend/observability]]
## Prisma Best Practices Source: https://llmbestpractices.com/backend/prisma Last updated: 2026-10-01 ## Overview Use Prisma ORM 7 for typed access to Postgres and SQLite from TypeScript. Prisma 7 is the stable line (7.10.0). As of 2026-10-01 Prisma 8 is a release candidate (8.0.0-rc.19) with a different API (a `contract.prisma` file and `@prisma/orm-postgres`), GA is expected in October 2026, and Prisma 7 keeps bug and security fixes for 18 months after Prisma 8 reaches GA. These pages describe Prisma 7. ## Pin both packages to the 7 line On the npm registry, `prisma@latest` is the prerelease 8.0.0-rc.19 (the `prev` tag is 7.10.0), while `@prisma/client@latest` is 7.10.0 and `@prisma/adapter-pg@latest` is 7.10.0, so a bare `npm install prisma` pairs a Prisma 8 CLI with a Prisma 7 client. Pin both to 7 and re-check with `npm view prisma dist-tags` before relaxing the pin. ```bash npm install prisma@7 @prisma/client@7 ``` Prisma 7 needs Node 20.19+, 22.12+, or 24+, TypeScript 5.4+, and an ESM project (`"type": "module"`). ## Wire Prisma 7 in three files The connection URL lives in `prisma.config.ts`, not in `schema.prisma`; the generator must set `output`; the client needs a driver adapter. ```prisma // prisma/schema.prisma generator client { provider = "prisma-client" output = "../src/generated/prisma" } datasource db { provider = "postgresql" } ``` ```ts // prisma.config.ts import "dotenv/config" import { defineConfig, env } from "prisma/config" export default defineConfig({ schema: "prisma/schema.prisma", migrations: { path: "prisma/migrations" }, datasource: { url: env("DATABASE_URL") }, }) ``` ```ts // src/db.ts import { PrismaPg } from "@prisma/adapter-pg" import { PrismaClient } from "./generated/prisma/client" export const prisma = new PrismaClient({ adapter: new PrismaPg({ connectionString: process.env.DATABASE_URL }), }) ``` Prisma 7 does not load `.env` files itself, and `migrate dev` and `db push` no longer run `prisma generate` or seed scripts. Run `npx prisma generate` explicitly. ## Send each task to its page - Models, relations, native types: [[backend/prisma-schema]]. - Adapters, the client singleton, logging, extensions: [[backend/prisma-client]]. - Migration workflow, baseline, drift: [[backend/prisma-migrations]]. - Atomic writes, isolation, retries: [[backend/prisma-transactions]]. - Pool sizing, PgBouncer, serverless, Accelerate: [[backend/prisma-pooling]]. - SQL the query builder cannot express: [[backend/prisma-raw-queries]]. - Moving from Prisma 5 or 6: [[backend/prisma-v6-to-v7-upgrade]]. - Choosing against Drizzle: [[comparisons/prisma-vs-drizzle]]. ## Related - [[backend/postgres]] - [[backend/sqlite]] - [[backend/fastapi]] - [[backend/prisma-client]] - [[backend/prisma-v6-to-v7-upgrade]] - [[coding/typescript]]
## Prisma Client: Setup, Adapters, and Extensions Source: https://llmbestpractices.com/backend/prisma-client Last updated: 2026-10-01 ## Overview The generated Prisma client is the typed query builder emitted from `schema.prisma`: one property per model, with return types inferred from the query shape. In Prisma 7 the client has no built-in engine, so you construct it with a driver adapter and import it from the generator's `output` directory. This page covers the adapter, the singleton, logging, and `$extends`. ## Run prisma generate explicitly after schema changes The client is a build artifact. Prisma 7 no longer runs `generate` after `migrate dev` or `db push`. ```json { "scripts": { "postinstall": "prisma generate" } } ``` The `prisma-client` generator writes to the `output` directory you set, not `node_modules`. Add that directory to `.gitignore` and import from it, for example `./generated/prisma/client`. See [[backend/prisma-schema]] for the generator block. ## Pass a driver adapter that matches the database `new PrismaClient()` without an adapter fails in Prisma 7. The adapter wraps the Node driver, and the driver owns the connection pool. The only exception is Prisma Accelerate; see [[backend/prisma-pooling]]. | Database | Package | Class | | --- | --- | --- | | Postgres, Supabase, direct Neon | `@prisma/adapter-pg` | `PrismaPg` | | Neon over WebSocket or HTTP | `@prisma/adapter-neon` | `PrismaNeon`, `PrismaNeonHttp` | | SQLite file | `@prisma/adapter-better-sqlite3` | `PrismaBetterSqlite3` | | libSQL, Turso | `@prisma/adapter-libsql` | `PrismaLibSql` | | Cloudflare D1 | `@prisma/adapter-d1` | `PrismaD1`, `PrismaD1Http` | | MariaDB, MySQL | `@prisma/adapter-mariadb` | `PrismaMariaDb` | | PlanetScale | `@prisma/adapter-planetscale` | `PrismaPlanetScale` | | SQL Server | `@prisma/adapter-mssql` | `PrismaMssql` | ```ts import { PrismaPg } from "@prisma/adapter-pg" import { PrismaClient } from "../generated/prisma/client" const adapter = new PrismaPg({ connectionString: process.env.DATABASE_URL }) export const prisma = new PrismaClient({ adapter }) ``` SQLite adapters take `{ url }`: `new PrismaLibSql({ url })` or `new PrismaBetterSqlite3({ url })`. The underlying driver (`pg`, `@libsql/client`, `better-sqlite3`) installs as a dependency of the adapter. Keep the adapter package on the same major version as `prisma`. Build the adapter and client once, never per request. ## Use a singleton, and survive hot reload in development Multiple `PrismaClient` instances mean multiple pools. Export one instance from one module and import it everywhere. Dev servers (Next.js, Vite) reload modules and would create a client per reload, so cache it on `globalThis` outside production. ```ts const globalForPrisma = globalThis as unknown as { prisma?: PrismaClient } export const prisma = globalForPrisma.prisma ?? new PrismaClient({ adapter: new PrismaPg({ connectionString: process.env.DATABASE_URL }) }) if (process.env.NODE_ENV !== "production") globalForPrisma.prisma = prisma ``` In serverless handlers, create the client at module scope so warm invocations reuse it, and do not call `$disconnect` per invocation. Pool limits and poolers are in [[backend/prisma-pooling]]. ## Log queries through events, not stdout ```ts const prisma = new PrismaClient({ adapter, log: [ { emit: "event", level: "query" }, { emit: "stdout", level: "warn" }, { emit: "stdout", level: "error" }, ], }) prisma.$on("query", (e) => console.log(`${e.query} (${e.duration}ms)`)) ``` Route query events to your telemetry pipeline instead of stdout, and turn `query` logging off in production unless you are debugging: the emitted parameters can contain personal data. Pair slow queries with [[backend/postgres-explain]]. ## Use $extends for cross-cutting behavior `$extends` returns a new client with extra behavior; the base client is unchanged. Export and use the extended client everywhere. The `$use` middleware API was deprecated in 4.16.0 and is removed in Prisma 7. Chain `$extends` calls to compose. Four components exist: `query`, `result`, `model`, and `client`. ```ts const prisma = base.$extends({ query: { post: { findMany({ args, query }) { args.where = { ...args.where, deletedAt: null } return query(args) }, }, }, result: { user: { fullName: { needs: { firstName: true, lastName: true }, compute: (u) => `${u.firstName} ${u.lastName}`, }, }, }, model: { user: { signUp: (email: string) => base.user.create({ data: { email } }) }, }, client: { $health: () => base.$queryRaw`SELECT 1` }, }) ``` - `query` rewrites or wraps operations; call `query(args)` exactly once. A soft-delete filter on `findMany` does not cover `findFirst`, `count`, or `aggregate`; hook each operation you rely on, or use `$allOperations`. - Do not cache inside a `query` hook; it fires on every matching call, so invalidation becomes unpredictable. - `result` fields are virtual. `needs` lists the columns Prisma selects for you, and you cannot filter on computed fields in `where`. Promote the value to a column if you need to query it. - `model` and `client` add methods for domain operations and helpers. See [[backend/prisma-raw-queries]] for raw maintenance queries. - The `tx` client inside an interactive `$transaction` carries the same extensions; see [[backend/prisma-transactions]]. ## Derive types from the generated client Use the generated payload types instead of hand-written interfaces. ```ts import { Prisma } from "./generated/prisma/client" type UserWithOrders = Prisma.UserGetPayload<{ include: { orders: true } }> ``` Avoid `any` casts on query results; see [[coding/typescript]]. ## Related - [[backend/prisma]] - [[backend/prisma-schema]] - [[backend/prisma-migrations]] - [[backend/prisma-pooling]] - [[backend/prisma-transactions]] - [[backend/prisma-v6-to-v7-upgrade]] - [[comparisons/prisma-vs-drizzle]] - [[coding/typescript]]
## Prisma Migrations Source: https://llmbestpractices.com/backend/prisma-migrations Last updated: 2026-10-01 ## Overview Prisma Migrate turns schema changes into SQL files under `prisma/migrations/` and records what has run in the `_prisma_migrations` table. `migrate dev` authors migrations locally; `migrate deploy` applies them in CI and production. Treat a migration as immutable once any environment has applied it. The general migration contract is in [[backend/migrations]]. ## Author with migrate dev and ship with migrate deploy ```bash # Local: diff schema against migrations, write SQL, apply it npx prisma migrate dev --name add_product_sku # CI and production: apply pending migrations, no prompts npx prisma migrate deploy ``` - `migrate dev` uses a shadow database to compute the diff and may offer to reset your local database when history is inconsistent. Never run it against production. - `migrate deploy` applies migrations missing from `_prisma_migrations`, never creates new ones, and does not detect drift. Run it before the new application version takes traffic. `migrate status` reports whether history and database agree. - In Prisma 7, `migrate dev` and `db push` no longer run `prisma generate` or seed scripts. Add `npx prisma generate` to your workflow. - Commit the SQL with the schema change; reviewers read the SQL. ## Configure the shadow and direct connections in prisma.config.ts `prisma.config.ts` holds `datasource.url` and `datasource.shadowDatabaseUrl`; `directUrl` was removed in Prisma 7. The schema engine needs a direct, unpooled connection, so point `datasource.url` at the direct database URL when your application uses a pooler; see [[backend/prisma-pooling]]. The shadow database needs permission to create and drop databases, or an explicit `shadowDatabaseUrl`. ## Baseline an existing database A database that already has a schema needs a baseline, or `migrate deploy` fails on tables that exist. ```bash mkdir -p prisma/migrations/0_init npx prisma migrate diff --from-empty --to-schema prisma/schema.prisma \ --script > prisma/migrations/0_init/migration.sql npx prisma migrate resolve --applied 0_init ``` Run the `resolve` step against every environment that already has the schema. A fresh environment runs `migrate deploy` and executes the baseline as normal SQL. ## Detect drift with migrate diff Drift means the database no longer matches what the migration history produces: a hand-run `ALTER TABLE`, a half-applied migration, or an edited shipped file. ```bash npx prisma migrate diff \ --from-migrations prisma/migrations \ --to-config-datasource \ --exit-code ``` Exit code 0 means no differences, 2 means differences, 1 means an error. In development, fix drift with `prisma migrate reset`. In production, write a corrective migration; do not edit shipped ones. ## Edit generated SQL for what Prisma cannot express Use `prisma migrate dev --create-only` to write the migration without applying it, then edit the SQL for custom indexes, triggers, backfills, or `CREATE INDEX CONCURRENTLY` on a hot table ([[backend/postgres-indexes]]). Prisma has no documented flag to disable the migration transaction. Reports from Prisma's issue tracker indicate that on Postgres a file with several statements runs as one implicit transaction, so keep `CREATE INDEX CONCURRENTLY` as the only statement in its own migration file and test it before relying on it. ## Squash long histories with the documented steps Long histories slow shadow-database replay and `migrate reset`. To squash: 1. Delete the contents of `prisma/migrations/`. 2. Create `prisma/migrations/000000000000_squashed_migrations/migration.sql` from `migrate diff --from-empty --to-schema prisma/schema.prisma --script`. 3. Run `npx prisma migrate resolve --applied 000000000000_squashed_migrations` on every environment that already ran the old history. Squashing drops custom SQL you added by hand to old migrations; re-add views, triggers, and similar objects afterwards. Squash only when every environment has applied everything. ## Related - [[backend/prisma]] - [[backend/postgres]] - [[backend/migrations]] - [[backend/prisma-schema]] - [[backend/prisma-client]] - [[comparisons/prisma-vs-drizzle]] - [[backend/prisma-pooling]]
## Prisma Connection Pooling Source: https://llmbestpractices.com/backend/prisma-pooling Last updated: 2026-10-01 ## Overview Every `PrismaClient` owns a connection pool, and in Prisma 7 the underlying Node driver sets that pool's behavior. Long-lived servers need a sized pool; serverless and edge runtimes multiply pools across instances and need an external pooler. Decide the pool size and the pooler before you scale, not after the first `too many connections` error. ## Configure the pool on the adapter, not the URL The Prisma 6 URL parameters `connection_limit`, `pool_timeout`, `connect_timeout`, and `max_idle_connection_lifetime` no longer work. Pass driver options to the adapter. ```ts const adapter = new PrismaPg({ connectionString: process.env.DATABASE_URL, max: 10, // pool size connectionTimeoutMillis: 5_000, idleTimeoutMillis: 300_000, }) ``` The `pg` defaults differ from Prisma 6: `max` is 10, `connectionTimeoutMillis` is 0 (wait forever), and `idleTimeoutMillis` is 10 seconds. Set a connection timeout explicitly so an exhausted pool fails fast instead of hanging. Size the pool so instances times `max` stays under the database's `max_connections` or the pooler's client limit; see [[backend/postgres]]. ## Put PgBouncer in transaction mode in front of serverless and large fleets PgBouncer multiplexes many client connections onto few database connections. Transaction mode is the one Prisma Client works with. - Set `max_prepared_statements` above zero in PgBouncer 1.21 and later. Use `?pgbouncer=true` on the URL only with PgBouncer older than 1.21. - Use two URLs. The application connects through the pooler. The Prisma CLI needs a direct connection because the schema engine does not support pooling, so set `datasource.url` in `prisma.config.ts` to the direct URL; see [[backend/prisma-migrations]]. - Transaction mode resets server state between transactions. Session-level `SET`, session advisory locks, and `LISTEN/NOTIFY` do not work through it; give those code paths a separate direct connection. On Supabase, use the transaction pooler for serverless and a direct or session connection for persistent servers; see [[backend/supabase]]. ## Keep one client per process in serverless runtimes A cold container creates its own client and pool. A traffic spike of 100 containers opens up to 100 times `max` connections. Create the client at module scope so warm invocations reuse it, and do not call `$disconnect` per invocation. Keep `max` low per instance and let the pooler absorb the burst. See [[backend/prisma-client]]. ## Use Accelerate through accelerateUrl, without an adapter Prisma Accelerate is a managed pooler and cache. It is the one setup that takes no driver adapter. Install `@prisma/extension-accelerate`, use a `prisma://` or `prisma+postgres://` URL, and pass it as `accelerateUrl`. ```ts import { PrismaClient } from "./generated/prisma/client" import { withAccelerate } from "@prisma/extension-accelerate" const prisma = new PrismaClient({ accelerateUrl: process.env.DATABASE_URL, }).$extends(withAccelerate()) await prisma.user.findMany({ cacheStrategy: { ttl: 60, swr: 60 } }) ``` - Never pass an Accelerate URL to a driver adapter; `PrismaPg` expects a direct connection string and fails. - For serverless and edge bundles, generate with `npx prisma generate --no-engine`. - Caching is opt-in per query through `cacheStrategy`. ## Related - [[backend/prisma]] - [[backend/postgres]] - [[backend/prisma-client]] - [[backend/prisma-transactions]] - [[backend/prisma-migrations]] - [[backend/supabase]] - [[comparisons/prisma-vs-drizzle]]
## Prisma Raw Queries Source: https://llmbestpractices.com/backend/prisma-raw-queries Last updated: 2026-10-01 ## Overview Use raw SQL when the query builder cannot express the query, and keep the tagged-template form so values stay parameterized. `$queryRaw` returns rows and `$executeRaw` returns the affected row count. Reach for them deliberately: for the same table, a missing index or a materialized view often removes the need. ## Use $queryRaw for reads and $executeRaw for writes ```ts const rows = await prisma.$queryRaw
## Prisma Schema Source: https://llmbestpractices.com/backend/prisma-schema Last updated: 2026-10-01 ## Overview `schema.prisma` defines the models, relations, and generator that Prisma turns into migrations and the typed client. In Prisma 7 it no longer holds the connection URL. Treat the schema as the contract and everything else as derived from it. ## Declare the provider only; put the URL in prisma.config.ts Prisma 7 moved `url`, `directUrl`, and `shadowDatabaseUrl` out of the schema and into `prisma.config.ts`. The datasource block names the provider and, optionally, `relationMode`. ```prisma datasource db { provider = "postgresql" } ``` Do not switch providers without regenerating migrations; the provider selects the SQL dialect and the available native types. Keep the URL in an environment variable loaded by the config file; see [[backend/prisma]]. ## Use the prisma-client generator and set output ```prisma generator client { provider = "prisma-client" output = "../src/generated/prisma" } ``` - `prisma-client` replaces the deprecated `prisma-client-js`. `output` is required and must sit in your source tree, not `node_modules`. - Add the output directory to `.gitignore` and regenerate in `postinstall`; see [[backend/prisma-client]]. - Do not list `driverAdapters` in `previewFeatures`; adapters are standard in Prisma 7. Add `previewFeatures` only for a capability that is still flagged. - A schema can be split across files. Point `schema` in `prisma.config.ts` at a folder. ## Give every model an id and choose its generator deliberately Prisma requires one `@id` or `@@id` per model. ```prisma model Product { id String @id @default(uuid(7)) sku String @unique price Int createdAt DateTime @default(now()) updatedAt DateTime @updatedAt } ``` - `cuid()`, `uuid()`, and `uuid(7)` are generated by Prisma Client, not by the database. Prefer `uuid(7)` (time-ordered) over `uuid()` for index locality. To let Postgres 18 generate it, use `@default(dbgenerated("uuidv7()")) @db.Uuid`; see [[backend/postgres]]. - Use `autoincrement()` when one database assigns ids. - `@updatedAt` is maintained by Prisma on each `update`. `@default(now())` sets `createdAt`; do not compute it in application code. ## Put uniqueness and indexes in the schema - `@unique` for single-field natural keys (email, slug, external id); `@@unique([a, b])` for composite keys. - `@@index([col])` for columns you filter or sort on. Match each index to a real query; see [[backend/postgres-indexes]]. - Indexes and constraints Prisma cannot express belong in a hand-edited migration; see [[backend/prisma-migrations]]. ## Declare relations on both sides with explicit onDelete ```prisma model Order { id String @id @default(uuid(7)) userId String user User @relation(fields: [userId], references: [id], onDelete: Cascade) items OrderItem[] } model User { id String @id @default(uuid(7)) orders Order[] } ``` Specify `fields`, `references`, and `onDelete`. Defaults differ between required and optional relations, so choose `Cascade`, `Restrict`, or `SetNull` per relation instead of inheriting them. ## Set relationMode only when the database does not enforce foreign keys The default, `foreignKeys`, relies on the database. Use `relationMode = "prisma"` only for a database without foreign key support; Prisma then emulates the checks in its query layer and does not create indexes on relation columns, so add `@@index` on every foreign key column yourself. Do not combine it with Postgres, which already enforces foreign keys. ## Map to native types when defaults are too loose - `String` maps to `text` on Postgres. Use `@db.VarChar(n)` when length matters. - `Int` maps to `integer`. Use `BigInt` for values that may pass 2 billion. - `Json` maps to `jsonb`. Prisma filters Json fields with `path`, `equals`, `string_contains`, and `array_contains`; Postgres paths are arrays such as `["petName"]`. Use [[backend/prisma-raw-queries]] for `jsonb_path_query` and operators Prisma lacks, and see [[backend/postgres-jsonb]]. - Run `prisma migrate dev` after changing native types so the `ALTER TABLE` is generated. ## Related - [[backend/prisma]] - [[backend/postgres]] - [[backend/migrations]] - [[backend/prisma-migrations]] - [[backend/prisma-client]] - [[backend/prisma-transactions]] - [[comparisons/prisma-vs-drizzle]]
## Prisma Transactions Source: https://llmbestpractices.com/backend/prisma-transactions Last updated: 2026-10-01 ## Overview Prisma auto-commits each operation unless you group them with `$transaction`. The array form runs independent writes atomically; the interactive callback form is for logic that reads before it writes. The wrong form or isolation level produces lost updates, write skew, and deadlocks. ## Use the array form for independent writes ```ts await prisma.$transaction([ prisma.order.create({ data: { userId, total } }), prisma.inventory.update({ where: { sku }, data: { stock: { decrement: 1 } } }), prisma.auditLog.create({ data: { event: "order_placed", userId } }), ]) ``` The queries run sequentially in one transaction and commit or roll back together. They cannot pass results to each other; use nested writes or the callback form when one write needs another's generated id. ## Use the interactive callback for read-modify-write ```ts await prisma.$transaction(async (tx) => { const item = await tx.inventory.findUnique({ where: { sku } }) if (!item || item.stock < 1) throw new Error("out of stock") await tx.inventory.update({ where: { sku }, data: { stock: { decrement: 1 } } }) await tx.order.create({ data: { userId, total, sku } }) }) ``` - Use `tx`, not the global `prisma`, inside the callback. Calls on the global client run outside the transaction. - Throwing inside the callback rolls back. - The callback holds a pooled connection for its whole duration. Keep it short and do no network calls inside it; see [[backend/prisma-pooling]]. ## Set timeouts deliberately Interactive transactions default to `maxWait: 2000` ms (waiting for a connection) and `timeout: 5000` ms (running time). ```ts await prisma.$transaction(fn, { maxWait: 5_000, timeout: 10_000 }) ``` Set `timeout` below your HTTP request timeout so the database cleans up before the client gives up. ## Raise isolation when concurrent writers race Postgres defaults to `ReadCommitted`, which allows the read-modify-write race above when two transactions run together. `RepeatableRead` in Postgres is snapshot isolation: no phantoms, but write skew is still possible. `Serializable` prevents it. ```ts await prisma.$transaction(fn, { isolationLevel: Prisma.TransactionIsolationLevel.Serializable }) ``` Prisma returns error `P2034` for a write conflict or deadlock. Higher isolation raises abort rates, so retry: ```ts async function withRetry
## Prisma 6 to 7 Upgrade Source: https://llmbestpractices.com/backend/prisma-v6-to-v7-upgrade Last updated: 2026-10-01 ## Overview Prisma 7 (released 2025-11-19) removes the Rust query engine, so the client needs a driver adapter, the generator and import paths change, and the connection URL moves into `prisma.config.ts`. Work through the steps in order and run the type checker and tests after each. If you are starting fresh, skip this page and read [[backend/prisma]]. Prisma 8 is a release candidate with a different API and is a separate migration, covered by Prisma's own upgrade guide. ## Check the toolchain first Prisma 7 requires Node 20.19+, 22.12+, or 24+, TypeScript 5.4+, and an ESM project: `"type": "module"` in `package.json`, and `"module": "ESNext"` with `"moduleResolution": "bundler"` in `tsconfig.json`. Pin both packages: `npm install prisma@7 @prisma/client@7`. The `prisma` `latest` tag now resolves to the Prisma 8 release candidate. ## Swap the generator and fix imports ```prisma generator client { provider = "prisma-client" output = "../src/generated/prisma" } ``` - `prisma-client` replaces the deprecated `prisma-client-js`. `output` is mandatory and must be outside `node_modules`. - Remove `driverAdapters` from `previewFeatures` and drop `engineType`. - Add the output directory to `.gitignore` and keep `prisma generate` in `postinstall`. - Change every import of `PrismaClient` and `Prisma` from `@prisma/client` to the output path, for example `./generated/prisma/client`. This includes `Prisma.sql`, `Prisma.join`, and `Prisma.raw` ([[backend/prisma-raw-queries]]). ## Move connection settings into prisma.config.ts ```ts import "dotenv/config" import { defineConfig, env } from "prisma/config" export default defineConfig({ schema: "prisma/schema.prisma", migrations: { path: "prisma/migrations", seed: "tsx prisma/seed.ts" }, datasource: { url: env("DATABASE_URL") }, }) ``` - Delete `url`, `directUrl`, and `shadowDatabaseUrl` from the schema datasource. `directUrl` is gone; use the direct URL as `datasource.url`. - Prisma 7 does not load `.env` files. Import `dotenv/config` or run Node with `--env-file`. ## Install an adapter and re-check pool settings ```ts import { PrismaPg } from "@prisma/adapter-pg" import { PrismaClient } from "./generated/prisma/client" export const prisma = new PrismaClient({ adapter: new PrismaPg({ connectionString: process.env.DATABASE_URL }), }) ``` See [[backend/prisma-client]] for the adapter for each database. Three behavior changes follow from the driver taking over: - Pool defaults come from `pg`, not Prisma. There is no connection timeout by default, and `connection_limit` and `pool_timeout` URL parameters are ignored; see [[backend/prisma-pooling]]. - TLS certificates are now validated. A self-signed certificate that worked before needs a CA, or `ssl: { rejectUnauthorized: false }` in development only. - Accelerate users pass `accelerateUrl` and use no adapter; never give an Accelerate URL to an adapter. ## Update scripts and commands - `migrate dev` and `db push` no longer run `prisma generate` or seed scripts. Run `prisma generate` and `prisma db seed` yourself, and drop `--skip-generate` and `--skip-seed`. - `migrate diff` replaced `--from-url` and `--to-url` with `--from-config-datasource` and `--to-config-datasource`. The shadow database URL now comes from the config; see [[backend/prisma-migrations]]. ## Replace removed features - `$use` middleware is removed. Port each handler to a `query` extension scoped to the model and operation; see [[backend/prisma-client]]. - The Metrics API is removed. - MongoDB is not supported in Prisma 7. Stay on Prisma 6 for MongoDB projects. ## Related - [[backend/prisma]] - [[backend/prisma-client]] - [[backend/prisma-schema]] - [[backend/prisma-pooling]] - [[backend/prisma-migrations]] - [[backend/prisma-raw-queries]]
## SQLite Best Practices Source: https://llmbestpractices.com/backend/sqlite Last updated: 2026-10-01 ## Overview Use SQLite for single-writer workloads whose data fits on one local disk: local-first and edge apps, mobile, CI fixtures, embedded read-only data, and CLI tools. The current release is 3.53.4 (2026-07-24). Choose [[backend/postgres]] when more than one process writes concurrently or when you need managed replication and failover. ## Run a patched SQLite, especially in WAL mode The WAL-reset bug can corrupt a database through a rare race between concurrent writes or checkpoints on different connections. It affects SQLite 3.7.0 through 3.51.2 and is fixed in 3.51.3 (2026-03-13) and later, with backports in 3.50.7 and 3.44.6. Check the version your process actually loads: `SELECT sqlite_version();`. Language drivers bundle or link their own copy, so upgrade the driver or system library, not just the CLI. ## Choose SQLite for single-writer workloads SQLite allows one writer at a time across the whole database, with any number of concurrent readers. - Local-first and edge apps ([[ops/cloudflare-durable-objects|Cloudflare Durable Objects]], Turso) and mobile ([[ios/core-data]] sits on SQLite). - CI fixtures: a fresh `:memory:` database per test beats starting Postgres. - Embedded read-only data: ship a prebuilt file and read it without a server. ## Set the WAL pragmas on every connection ```sql PRAGMA journal_mode = WAL; -- persistent: stored in the file PRAGMA synchronous = NORMAL; -- per connection PRAGMA foreign_keys = ON; -- per connection, off by default PRAGMA busy_timeout = 5000; -- per connection PRAGMA cache_size = -64000; -- 64 MB (negative = KiB) PRAGMA temp_store = MEMORY; ``` - WAL lets readers run alongside the writer. The journal mode persists in the file; the other pragmas must be set on each new connection. - `synchronous = NORMAL` in WAL mode only syncs at checkpoints. It cannot corrupt the database, but the most recent commits can be lost after a power failure. Use `FULL` when that is unacceptable. - `busy_timeout` makes a blocked writer wait instead of failing at once with `SQLITE_BUSY`. - Declare tables `STRICT` (3.37 and later) so columns enforce their declared types instead of SQLite's loose affinity. ## Treat the writer as a serial queue - Funnel multiple writer threads through one connection or queue. - Open write transactions with `BEGIN IMMEDIATE` to take the write lock up front instead of failing on upgrade from a read lock. - For batched inserts, prepare the statement once and run it in one transaction; per-row auto-commit pays a sync for every row. - Avoid always having a reader open. Checkpoints cannot finish while overlapping readers never leave a gap, and the WAL file then grows without bound. ## Back up with .backup or VACUUM INTO Copying the file with `cp` while it is open can produce a corrupt backup. Use an online method. ```sh sqlite3 app.db ".backup '/backups/app-$(date +%F).db'" sqlite3 app.db "VACUUM INTO '/backups/app-$(date +%F).db';" ``` Both produce a consistent snapshot. `VACUUM INTO` also rebuilds the file and drops free pages. For continuous backup, use Litestream to stream WAL changes to object storage. ## Keep the file on a local disk WAL needs shared memory between all processes using the database, so every process must run on the same host. NFS, SMB, and FUSE-mounted object stores break SQLite's locking and can corrupt the file. If machines need shared access, use [[backend/postgres]] or a hosted SQLite service such as Turso or Cloudflare D1. For read-only fan-out, copy the file to each reader's local disk. ## Pick the driver for the runtime - Node: `better-sqlite3` (synchronous; long queries block the event loop). Bun: built-in `bun:sqlite`. - Edge and replication: libSQL (`@libsql/client`), the Turso fork. - Python: stdlib `sqlite3`, or `aiosqlite` in async code. Swift: GRDB.swift or Core Data. - [[backend/prisma]] reaches SQLite through `@prisma/adapter-better-sqlite3` or `@prisma/adapter-libsql`. For schema changes, SQLite 3.53 can add and remove `NOT NULL` and `CHECK` constraints with `ALTER TABLE`. Changing a column type still means rebuilding the table; see [[backend/migrations]]. ## Related - [[backend/postgres]] - [[backend/prisma]] - [[ios/core-data]] - [[backend/migrations]] - [[ops/cloudflare-durable-objects]]
## Supabase: Best Practices Source: https://llmbestpractices.com/backend/supabase Last updated: 2026-10-01 ## Overview Supabase bundles managed Postgres, Auth, Storage, Realtime, and an auto-generated REST and GraphQL Data API. Use it when you want a Postgres-backed backend with built-in auth and row-level security and no database cluster to run. Because the Data API exposes tables directly, the key model and [[backend/supabase-rls]] are the security boundary. ## Use publishable and secret keys, not anon and service_role New projects use `sb_publishable_...` (low privilege, safe in browser and mobile code) and `sb_secret_...` (elevated, server only). The legacy JWT-based `anon` and `service_role` keys are being deprecated by the end of 2026, and both systems coexist until then. A secret key maps to the `service_role` Postgres role, which has `BYPASSRLS`, so it skips every policy. - Keep secret keys in server environments only: API handlers, Edge Functions, CI. Supabase returns 401 when a secret key is used from a browser, but never rely on that. See [[ops/secrets-and-env]]. - Send the new keys in the `apikey` header. They are not JWTs, so do not verify them as JWTs or put them in `Authorization: Bearer`. - To rotate a leaked key: create a replacement, deploy it, then delete the old key. ```ts // Browser-safe const supabase = createClient(url, process.env.NEXT_PUBLIC_SUPABASE_PUBLISHABLE_KEY!) // Never: createClient(url, process.env.NEXT_PUBLIC_SUPABASE_SECRET_KEY!) ``` ## Grant Data API access explicitly New tables in the `public` schema are no longer exposed to the Data API automatically. The change is the default for new projects from 2026-05-30 and is enforced on all existing projects on 2026-10-30; tables that already exist keep their grants. Grant each role what it needs, then enable RLS: ```sql grant select on public.posts to anon; grant select, insert, update, delete on public.posts to authenticated; alter table public.posts enable row level security; ``` Without a `GRANT`, a role cannot reach the table at all, and a missing grant fails before any policy runs. Add the grants to your migrations so they are reviewed and reproducible. Policy details are in [[backend/supabase-rls]]. ## Initialize and link with the CLI ```bash npm install -D supabase # global npm install is unsupported npx supabase init npx supabase link # link to a hosted project supabase start # full local stack in Docker ``` `supabase init` creates `supabase/` with `config.toml`, a seed file, and the migrations folder. `supabase link` is required before pushing migrations. The local stack is ephemeral and reseeds from `supabase/seed.sql`. ## Change the schema through migrations only ```bash supabase migration new add_posts_table # writes supabase/migrations/
## Supabase Row Level Security: Best Practices Source: https://llmbestpractices.com/backend/supabase-rls Last updated: 2026-10-01 ## Overview Row Level Security (RLS) makes Postgres filter every query by policies, whichever client sent it. Supabase exposes tables over PostgREST, so a client-side filter is a convenience: a caller can edit the request and reach any row that no grant or policy blocks. RLS and grants are the security boundary. Key handling is in [[backend/supabase]]. ## Pair grants with policies Postgres runs two checks before a client touches a table. Grants decide whether a role may run an operation at all; policies decide which rows the operation affects. A table in an exposed schema without RLS is readable and writable by any role holding a grant. Existing projects typically gave `anon` and `authenticated` broad default grants, and adding policies does not take those back. ```sql revoke all on table public.reports from anon, authenticated; grant select, insert, update, delete on table public.reports to authenticated; ``` New tables are no longer exposed to the Data API automatically (the default for new projects since 2026-05-30, enforced on all existing projects on 2026-10-30), so grants must be explicit; see [[backend/supabase]]. Revoke `anon` access unless the data is meant to be public. ## Enable RLS on every exposed table, explicitly Tables created with raw SQL ship with RLS off, so PostgREST exposes every row to anyone with a grant. Enable RLS yourself and then add policies; with RLS on and no policies, nothing is accessible, which is the safe default. ```sql alter table public.profiles enable row level security; ``` ## Write USING for visibility and WITH CHECK for new values `USING` filters existing rows for `SELECT`, `UPDATE`, and `DELETE`. `WITH CHECK` validates the row an `INSERT` or `UPDATE` writes. `INSERT` needs `WITH CHECK`; `UPDATE` needs both, and an `UPDATE` also needs a matching `SELECT` policy. Name the role with `to`. ```sql create policy "owners read" on public.profiles for select to authenticated using ( (select auth.uid()) = user_id ); create policy "owners insert" on public.profiles for insert to authenticated with check ( (select auth.uid()) = user_id ); create policy "owners update" on public.profiles for update to authenticated using ( (select auth.uid()) = user_id ) with check ( (select auth.uid()) = user_id ); ``` An `UPDATE` policy with `USING` but no `WITH CHECK` lets a user reassign `user_id` and write rows they should not own. Always pair them. ## Wrap auth functions and index policy columns `auth.uid()` returns `null` for unauthenticated requests, so an equality policy matches nothing. Write `(select auth.uid())`, not `auth.uid()`, so Postgres evaluates it once per statement through an `initPlan` instead of per row. Index every column a policy filters on. ```sql create index profiles_user_id_idx on public.profiles (user_id); ``` Supabase's performance guide reports large improvements on big tables from indexing policy columns. See [[backend/postgres]] for indexing strategy. ## Do not trust user_metadata, and make views obey RLS - `auth.jwt()` claims in `user_metadata` are editable by the user. Keep authorization data (roles, tenant ids) in `app_metadata`. - A view runs with its owner's rights and bypasses RLS by default. On Postgres 15 and later create it with `with (security_invoker = true)` so it obeys the caller's policies. - `SECURITY DEFINER` functions run as their owner, so one owned by `postgres` bypasses the caller's RLS. Prefer `SECURITY INVOKER` and keep definer functions narrow. ## Never ship the secret key `sb_secret_...` keys and the legacy `service_role` key map to the `service_role` role, which has `BYPASSRLS`. The table owner and the `postgres` role also bypass RLS. Keep secret keys on the backend only, store them per [[ops/secrets-and-env]], and review exposure when an agent or [[ai-agents/mcp-security]] surface can reach them. See [[coding/python-security]] for backend-secret posture, [[backend/auth-sessions]] for how tokens reach the database, and [[comparisons/oauth-vs-jwt]] for the claim model. ## Test policies as the client would ```sql begin; set local role authenticated; set local request.jwt.claims = '{"sub":"00000000-0000-0000-0000-000000000000","role":"authenticated"}'; select * from public.profiles; rollback; ``` Run automated pgTAP tests for each policy where possible. Before shipping, list tables without RLS and review policies: ```sql select tablename, rowsecurity from pg_tables where schemaname = 'public'; select schemaname, tablename, policyname, cmd from pg_policies where schemaname = 'public'; ``` Any `public` table with `rowsecurity = false` is a hole. ## Related - [[backend/auth-sessions]] - [[backend/postgres]] - [[ops/secrets-and-env]] - [[coding/python-security]] - [[ai-agents/mcp-security]] - [[comparisons/oauth-vs-jwt]] - [[backend/supabase]]
## Inbound Webhooks: Security Best Practices Source: https://llmbestpractices.com/backend/webhooks Last updated: 2026-10-01 ## Overview Treat every inbound webhook as untrusted until its HMAC signature verifies; a public endpoint that acts on unsigned payloads is a remote command interface for anyone who finds the URL. This page is the house standard for receiving webhooks from any provider. Stripe's header, SDK helpers, and delivery quirks are in [[backend/payments-stripe]]. ## Verify the HMAC over the raw request body Compute the HMAC over the exact bytes received, before any parsing. Providers sign the raw payload, so re-serializing JSON changes whitespace, key order, or Unicode and breaks verification. Capture the raw body before middleware deserializes it, compare with a constant-time function, and reject on mismatch. A normal string comparison leaks the correct signature byte by byte. Providers differ in what they sign: Stripe signs `timestamp.body` and sends a hex digest in `Stripe-Signature`; Standard Webhooks signs `id.timestamp.body` and sends base64 with a `v1,` prefix, space-separated for multiple secrets; GitHub sends `X-Hub-Signature-256` over the body alone. Follow the provider's scheme; this example implements Standard Webhooks. ```python import base64, hashlib, hmac, time def verify(raw_body: bytes, msg_id: str, timestamp: str, signature_header: str, secret: str, tolerance: int = 300) -> bool: if abs(time.time() - int(timestamp)) > tolerance: # replay window return False key = base64.b64decode(secret.removeprefix("whsec_")) signed = f"{msg_id}.{timestamp}.".encode() + raw_body expected = base64.b64encode(hmac.new(key, signed, hashlib.sha256).digest()).decode() return any( hmac.compare_digest(expected, sig.split(",", 1)[1]) # constant time; never == for sig in signature_header.split() if sig.startswith("v1,") ) ``` In Node, use `crypto.timingSafeEqual` over equal-length buffers. See [[coding/python-security]] for the broader rules. ## Enforce a timestamp tolerance window Reject deliveries whose signed timestamp is outside the tolerance. Stripe's libraries default to 5 minutes, and Standard Webhooks requires a check of `webhook-timestamp` against an acceptable window. The timestamp is inside the signed payload, so an attacker cannot change it and a captured request expires. A tolerance of 0 disables the check in Stripe's libraries. Keep server clocks on NTP. ## Dedupe by event id and make handlers safe to re-run Providers deliver at least once, so the same event arrives again after retries or manual redelivery. Store each processed ID (`webhook-id`, `X-GitHub-Delivery`, or the Stripe event `id`) under a unique constraint or in Redis and ignore repeats; a GitHub redelivery keeps the original delivery ID, which makes this work. Record the ID in the same transaction as the side effect so a crash between the two cannot double-charge or double-send. Prefer upserts and conditional writes to blind inserts, and do not depend on delivery order; fetch current state from the provider's API when order matters. ## Return 2xx fast and process asynchronously Verify the signature synchronously, enqueue the validated payload, and return a 2xx. Providers time out quickly (GitHub expects a 2xx within 10 seconds), and a slow handler causes retries, duplicates, and eventually a disabled endpoint. Return 5xx only for transient failures you want retried, and 2xx for events you accepted but cannot act on. GitHub does not redeliver failed deliveries automatically, so redeliver missed ones after an outage. Queue patterns are in [[backend/fastapi-background-tasks]]. ## Support two signing secrets during rotation Keep two active secrets during a rollover and accept a payload that verifies against either; Standard Webhooks puts one signature per secret in the header. Verify against the new secret first, then the old, and retire the old one once deliveries stop matching it. Store secrets in a secret manager ([[ops/secrets-and-env]]) and log verification failures to [[ops/error-tracking]] to catch a botched rotation. Where a provider publishes source IP ranges (GitHub's `GET /meta`), allowlist them in addition to verifying signatures, not instead. ## Related - [[backend/payments-stripe]]: Stripe-specific signature header and SDK verification. - [[ops/secrets-and-env]]: storing and rotating signing secrets. - [[backend/auth-sessions]]: authenticating the rest of your API surface. - [[backend/fastapi]]: framework baseline for the receiving route. - [[ops/error-tracking]]: alerting on verification and processing failures. - [[coding/python-security]]: constant-time compares and input handling.
## Cheatsheets Source: https://llmbestpractices.com/cheatsheets Last updated: 2026-10-01 > Tables and code first, minimal prose. Each card answers a lookup in one screen and links to the deeper page on the topic. ## Git - [[cheatsheets/git-commands|Git commands]]: inspect, undo, branch, sync, stash, recover, and aliases. - [[cheatsheets/git-rebase|Git rebase]]: interactive todo commands, `--onto` recipes, conflicts, autosquash. - [[cheatsheets/git-merge-strategies|Git merge strategies]]: fast-forward, no-ff, squash, ours and theirs, `-s` and `-X`. ## Shell and terminal - [[cheatsheets/bash-one-liners|Bash one-liners]]: parameter expansion, traps, arithmetic, tests. - [[cheatsheets/find-grep-awk-sed|find, grep, awk, sed]]: file and text processing with GNU and BSD notes. - [[cheatsheets/jq-syntax|jq]]: selectors, filters, formats, flags, and recipes. - [[cheatsheets/curl-flags|curl]]: request, auth, output, TLS flags, and API-testing recipes. - [[cheatsheets/regex-patterns|Regex]]: syntax, flags per engine, and validated patterns. - [[cheatsheets/vim-commands|Vim]]: modes, motions, operators, search, registers, macros. - [[cheatsheets/tmux-commands|tmux]]: sessions, windows, panes, copy mode, config. - [[cheatsheets/ssh-config|SSH config]]: Host blocks, ProxyJump, multiplexing, safe defaults. - [[cheatsheets/openssl-commands|OpenSSL]]: keys, certificates, live TLS tests, conversions. - [[cheatsheets/cron-syntax|Cron]]: fields, schedules, per-platform timezones, traps. - [[cheatsheets/gh-cli|GitHub CLI]]: pr, issue, run, release, and `gh api --jq`. ## Containers, cloud, and servers - [[cheatsheets/docker-commands|Docker commands]]: build, run, Compose, cleanup. - [[cheatsheets/dockerfile-syntax|Dockerfile syntax]]: instructions, multi-stage example, ARG and COPY gotchas. - [[cheatsheets/kubernetes-commands|kubectl]]: context, inspect, apply, rollback, logs, debug. - [[cheatsheets/aws-cli-commands|AWS CLI]]: s3, ec2, lambda, sts, iam, region precedence. - [[cheatsheets/nginx-config|Nginx config]]: location matching, proxy, TLS, gzip. ## Postgres and SQL - [[cheatsheets/postgres-explain|Postgres EXPLAIN]]: plan nodes, cost fields, symptoms. - [[cheatsheets/postgres-functions|Postgres functions]]: aggregates, date and time, JSONB. - [[cheatsheets/postgres-window-functions|Postgres window functions]]: ranking, lag, frames. - [[cheatsheets/postgres-types|Postgres types]]: text, numbers, time, JSON, arrays, UUIDs. - [[cheatsheets/sql-joins|SQL joins]]: join types, row-set recipes, NULL bugs. ## Languages and frameworks - [[cheatsheets/javascript-array-methods|JavaScript arrays]]: copy vs mutate, ES2023 methods, gotchas. - [[cheatsheets/typescript-utility-types|TypeScript utility types]]: Pick, Omit, Record, Awaited, NoInfer. - [[cheatsheets/typescript-narrowing-patterns|TypeScript narrowing]]: guards, predicates, unions, assertions. - [[cheatsheets/react-hooks|React hooks]]: core hooks, effects, React 19 hooks. - [[cheatsheets/tsx-jsx-syntax|TSX and JSX]]: expressions, conditionals, keys, typed props. - [[cheatsheets/python-collections|Python collections]]: Counter, defaultdict, deque, ChainMap. - [[cheatsheets/python-itertools|Python itertools]]: chain, groupby, combinatorics, batched. - [[cheatsheets/python-regex|Python re]]: functions, flags, Python-only syntax. - [[cheatsheets/python-string-formatting|Python string formatting]]: f-strings, format specs, t-strings. - [[cheatsheets/swift-collection-methods|Swift collections]]: map, reduce, sorted, Dictionary helpers, lazy. ## CSS and accessibility - [[cheatsheets/css-selectors|CSS selectors]]: specificity, combinators, `:is`, `:where`, `:has`. - [[cheatsheets/css-pseudo-classes|CSS pseudo-classes]]: state, structural, and pseudo-elements. - [[cheatsheets/css-units|CSS units]]: rem, ch, dvh, container and grid units. - [[cheatsheets/css-grid-areas|CSS grid areas]]: `grid-template-areas` syntax and responsive layouts. - [[cheatsheets/aria-attributes|ARIA attributes]]: names, states, live regions, gotchas. ## Web, SEO, and AI - [[cheatsheets/http-status-codes|HTTP status codes]]: codes by class, action-to-status table, problem details. - [[cheatsheets/schema-org-types|schema.org types]]: rich-result types, required fields, `@id` pattern. - [[cheatsheets/ai-crawlers|AI crawler tokens]]: vendor-documented robots.txt tokens and their jobs. - [[cheatsheets/markdown-syntax|Markdown dialects]]: CommonMark, GitHub, Obsidian, MDX support. - [[cheatsheets/llm-prompt-patterns|LLM prompt patterns]]: XML delimiters, few-shot, roles, structured output. ## Editors and OS - [[cheatsheets/keyboard-shortcuts-vscode|VS Code shortcuts]]: macOS and Windows defaults, Linux differences. - [[cheatsheets/macos-keyboard|macOS shortcuts]]: Finder, screenshots, windows, tiling, text. ## Related MOCs - [[coding/index|Coding]] - [[backend/index|Backend]] - [[frontend/index|Frontend]] - [[seo/index|SEO]] - [[tooling/index|Tooling]]
## AI Crawler User-Agents Source: https://llmbestpractices.com/cheatsheets/ai-crawlers Last updated: 2026-10-01 ## Overview Match the token to its job before you allow or block it: training, search indexing for an answer engine, or a fetch triggered by one user's request. Blocking one token does not block the others. Every row below comes from the vendor's own crawler documentation, checked 2026-10-01. Where the tokens sit next to `llms.txt` and `ai.txt` is in [[seo/discoverability-files]]. ## Tokens Choose the token by job, not by vendor; the Job column says which control applies. | Token | Operator | Job | robots.txt | | --- | --- | --- | --- | | `GPTBot` | OpenAI | Training | Honored. | | `OAI-SearchBot` | OpenAI | Search index for ChatGPT | Honored. | | `ChatGPT-User` | OpenAI | User-initiated fetch | OpenAI: rules "may not apply". | | `OAI-AdsBot` | OpenAI | Safety check of ad landing pages | Not stated. | | `ClaudeBot` | Anthropic | Training | Honored. | | `Claude-SearchBot` | Anthropic | Search quality | Honored. | | `Claude-User` | Anthropic | User-initiated fetch | Honored. | | `Googlebot` | Google | Search, including AI Overviews and AI Mode | Honored; see below. | | `Google-Extended` | Google | Control token only: use of crawled content for Gemini training and for grounding in Gemini Apps and Vertex AI | Token, not a crawler. | | `Google-CloudVertexBot` | Google | Crawls a site owner requests for Vertex AI Agents | Honored. | | `PerplexityBot` | Perplexity | Search index | Honored. | | `Perplexity-User` | Perplexity | User-initiated fetch | "Generally ignores" robots.txt. | | `Applebot` | Apple | Search for Siri, Spotlight, Safari | Honored. | | `Applebot-Extended` | Apple | Control token only: Apple foundation model training | Token, not a crawler. | | `Meta-ExternalAgent` | Meta | Training and indexing | Honored. | | `Meta-WebIndexer` | Meta | Meta AI search | Honored. | | `Meta-ExternalFetcher` | Meta | User-requested fetch | "May bypass" robots.txt. | | `Amazonbot` | Amazon | Product improvement; may train Amazon AI models | Honored. | | `Amzn-SearchBot` | Amazon | Amazon search; no training | Honored. | | `Amzn-User` | Amazon | User-initiated fetch | May not follow all directives. | | `CCBot` | Common Crawl | Public corpus used by many model trainers | Honored. | | `MistralAI-Training` | Mistral | Training | Controllable. | | `MistralAI-Index` | Mistral | Search index; not training | Controllable. | | `MistralAI-User` | Mistral | User-initiated fetch; not training | Controllable. | | `Diffbot` | Diffbot | General crawling for its knowledge graph and search; not AI training | Honored by default. | No English-language vendor crawler documentation exists for these, so treat any rule as best effort: `Bytespider` (ByteDance; its webmaster page is outside the English web and third parties report it ignores robots.txt), DeepSeek (no published crawler token), and `cohere-ai` (Cohere states it does not crawl to train models and lists no crawlers). ## Allow or block Pair every `User-agent` line with an explicit directive. ``` # Keep out of training, stay visible in ChatGPT search User-agent: GPTBot Disallow: / User-agent: OAI-SearchBot Allow: / ``` Blocking `GPTBot` leaves `OAI-SearchBot` and `ChatGPT-User` free to fetch and cite you. To stay out of an answer engine entirely, block its search and user-fetch tokens too. ## Google specifics `Google-Extended` has no user-agent string of its own: Google crawls with its normal agents and applies the token afterwards. It limits training and grounding in Gemini Apps and Vertex AI. It does not affect Search inclusion or ranking, and it does not remove you from AI Overviews or AI Mode. Those follow `Googlebot`, so limit them with `nosnippet`, `data-nosnippet`, `max-snippet`, or `noindex`. ## Gotchas Avoid relying on robots.txt alone for fetchers that user actions trigger. | Gotcha | Fix | | --- | --- | | User-initiated fetchers (`ChatGPT-User`, `Perplexity-User`, `Meta-ExternalFetcher`, `Amzn-User`) may ignore robots.txt. | Enforce at the WAF or CDN if you must block them. | | User-agent strings are easy to spoof. | Verify against the IP lists that OpenAI, Anthropic, Perplexity, and Common Crawl publish. | | Vendors rename and split agents, so an old allowlist leaks. | Re-check the operator docs each quarter. | | A wildcard `User-agent: *` rule applies only to agents with no group of their own. | Name each agent you care about. | | Applebot follows your `Googlebot` rules when robots.txt does not name Applebot. | Add an explicit `Applebot` group. | ## Related - [[seo/discoverability-files]] - [[seo/generative-engine-optimization]] - [[seo/llms-txt]] - [[glossary/crawl-budget]] - [[start-here]]
## ARIA Attributes Cheatsheet Source: https://llmbestpractices.com/cheatsheets/aria-attributes Last updated: 2026-10-01 ## Overview Use a native HTML element first and add ARIA only where HTML cannot express the semantics. Full component patterns are in [[frontend/html-aria-patterns]] and [[frontend/accessibility]]. ## Names and descriptions An element's accessible name comes from `aria-labelledby`, then `aria-label`, then native sources (`