---
title: "Backend, Databases & APIs"
slug: "backend"
category: "backend"
tags: ["moc", "backend", "postgres", "sqlite"]
status: "stable"
last_updated: 2026-05-13
summary: "Best practices for Postgres, Prisma, FastAPI, SQLite, and ChromaDB."
---

> 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 vector retrieval.

## Pages

- [[postgres]]: Schema design, indexes, migrations, JSONB.
- [[postgres-indexes]]: B-tree, GIN, GiST, BRIN, partial, expression, and covering indexes.
- [[postgres-jsonb]]: JSONB operators, GIN indexes, jsonpath, and the size cost.
- [[postgres-explain]]: Reading EXPLAIN ANALYZE plans and feeding pg_stat_statements back into tuning.
- [[postgres-vacuum]]: Autovacuum tuning, bloat detection, pg_repack, and transaction ID wraparound.
- [[postgres-replication]]: Streaming and logical replication, replica lag, and sync modes.
- [[postgres-partitioning]]: Range, list, and hash partitioning with pg_partman.
- [[postgres-full-text-search]]: tsvector, tsquery, GIN indexes, pg_trgm, and when to switch engines.
- [[supabase-rls]]: Supabase Row Level Security house standard: enable RLS per table, pair USING with WITH CHECK, wrap auth.uid(), and never ship service_role.
- [[prisma]]: Schema modeling, migrations, transactions, pooling.
- [[fastapi]]: Pydantic models, dependency injection, async I/O.
- [[sqlite]]: When SQLite is the right database.
- [[chromadb]]: Embeddings storage and retrieval.
- [[chromadb-collections]]: How to design ChromaDB collections: namespacing strategy, metadata schema, and when to choose persistent vs in-memory clients.
- [[chromadb-embeddings]]: When to use ChromaDB's built-in embedding functions vs precomputed vectors, how to pin models, and why dimension consistency is non-negotiable.
- [[chromadb-filters]]: How to write effective where clauses, combine metadata filters with vector similarity, and structure hybrid filter-plus-keyword retrieval in ChromaDB.
- [[chromadb-persistence]]: How to configure PersistentClient and server mode, back up the data directory, and migrate to a new instance without downtime.
- [[chromadb-scale-limits]]: Where ChromaDB stops working well at scale, the specific signals to watch for, and how to migrate to Qdrant or pgvector.
- [[fastapi-pydantic]]: Pydantic v2 model design for FastAPI: separate read/write models, field validators, aliases, and nested composition.
- [[fastapi-dependencies]]: Depends() patterns for database sessions, auth, and shared state; nested deps, class-based deps, yield cleanup, and request scope.
- [[fastapi-async-io]]: Keeping FastAPI endpoints fully async: async database drivers, threadpool offload for sync code, and uvicorn worker configuration.
- [[fastapi-lifespan]]: Replacing startup/shutdown events with the lifespan context manager: DB pool init, shared client setup, and graceful shutdown.
- [[fastapi-background-tasks]]: BackgroundTasks for lightweight fire-and-forget work, its limits as a queue, and when to reach for Celery or Arq instead.
- [[fastapi-openapi]]: Keeping FastAPI's auto-generated OpenAPI schema accurate: response_model, tags, summaries, ReDoc vs Swagger UI, and client codegen.
- [[prisma-schema]]: Model datasources, generators, and models in schema.prisma; use field attributes, relation modes, and type mappings correctly.
- [[prisma-migrations]]: Use migrate dev locally and migrate deploy in CI; baseline existing databases, resolve drift, and squash safely.
- [[prisma-client]]: Instantiate PrismaClient as a singleton, use generated types for safety, configure logging and middleware, and handle cold starts in Lambda.
- [[prisma-transactions]]: Choose between $transaction array and interactive callback; set isolation levels; avoid deadlocks; guard read-modify-write patterns.
- [[prisma-pooling]]: Choose between Prisma Accelerate, PgBouncer, and direct connections; configure pgbouncer=true; avoid transaction-mode pitfalls in serverless.
- [[prisma-raw-queries]]: Use $queryRaw and $executeRaw with Prisma.sql tagged templates for safe raw SQL; know when the ORM is insufficient and when to drop down.
- [[prisma-driver-adapters]]: Prisma 7 requires a driver adapter to connect; pick @prisma/adapter-pg for Postgres or @prisma/adapter-libsql for SQLite, install it, and pass it to PrismaClient.
- [[prisma-client-extensions]]: Use $extends with the query, result, model, and client components for soft-delete, computed fields, and custom methods; it replaced the removed $use middleware.
- [[prisma-v6-to-v7-upgrade]]: Upgrade to Prisma 7: swap to the prisma-client generator, set an output path, install a driver adapter, fix imports, and replace removed $use middleware.
- [[migrations]]: Idempotent up-only migrations and zero-downtime patterns.
- [[observability]]: Logs, metrics, traces, and OpenTelemetry wiring.
- [[supabase]]: Supabase project setup, CLI migrations, Supavisor connection pooling, and the service_role key security rule.
- [[auth-sessions]]: Session management: HttpOnly/Secure/SameSite cookie flags, session vs JWT, refresh-token rotation, CSRF, and revocation.
- [[payments-stripe]]: Stripe security standard: raw-body webhook signature verification, idempotency keys, server-side amounts, and the MoR/tax exposure.
- [[webhooks]]: Inbound webhook security: HMAC verify on the raw body, dedup by event id, fast 2xx then async, and replay protection.
- [[email-deliverability]]: SPF, DKIM, and DMARC for transactional email, with the none to quarantine to reject policy progression.

## Related MOCs

- [[coding/index|Coding]]
- [[ai-agents/index|AI Agents]]
- [[ops/index|Ops]]