Backend and database

By the end of this phase you have a running FastAPI service with a validated config module, and every table the product needs migrated into Supabase Postgres. That means users, documents, chunks with vector embeddings, chat threads, messages, and citations. This is Phase 1 in docs/todos.md, and it comes first for a reason. Auth, chat, retrieval, and grounding all depend on this data model.

It is plumbing, not the fun part. As Dave puts it, "it's not as sexy as some of the tutorials where we go straight into 'here's how you build stuff', but this is how you set up a project."

Install dependencies with uv

Work from the backend/ folder. uv sync creates the virtual environment, then you add the runtime dependencies from docs/guides/backend-setup.md.

cd backend
uv sync
uv add fastapi uvicorn pydantic pydantic-settings httpx structlog openai supabase pydantic-ai sqlalchemy alembic "psycopg[binary]" pgvector
uv add --dev pytest ruff

Two details in backend/pyproject.toml keep this reproducible. add-bounds = "exact" pins every dependency to an exact version, and exclude-newer = "7 days" ignores packages published in the last week so a freshly yanked release cannot break your install.

[tool.uv]
add-bounds = "exact"
exclude-newer = "7 days"

[dependency-groups]
dev = [
    "ipykernel==7.2.0",
    "pytest==9.0.3",
    "ruff==0.15.15",
]

Anything added with --dev lands in the dev dependency group, installed locally and excluded from deployment. After changing groups, run uv sync --dev to pull them in.

The config module

backend/app/config.py is the single source of truth for configuration. Nothing else in the codebase calls os.getenv; everything imports settings from here. The class is built on pydantic-settings, which loads backend/.env and validates it at import time.

from pydantic_settings import BaseSettings, SettingsConfigDict

class Settings(BaseSettings):
    model_config = SettingsConfigDict(
        env_file=_BACKEND_DIR / ".env",
        env_file_encoding="utf-8",
        extra="ignore",
    )

    supabase_url: str
    supabase_anon_key: str
    supabase_service_role_key: str
    database_url: str

    openai_api_key: str
    openai_embedding_model: str = "text-embedding-3-small"
    openai_embedding_dimensions: int = 1536

settings = Settings()

The point is fail-fast validation. Fields without defaults are required, so if database_url is missing from .env, the app refuses to start with a clear validation error naming the missing field. Dave demonstrates this live by deleting a variable and notes, "that is just very helpful, especially if we move into deployment where it can be a lot trickier to manage all of your environment variables."

FastAPI entry point

backend/app/main.py creates the app, wires CORS from settings, and exposes a health check. At this stage it is little more than an entry point, and that is the goal. Prove the app starts and can import its own config.

from fastapi import FastAPI
from fastapi.middleware.cors import CORSMiddleware

from app.config import settings

app = FastAPI(title="Document Copilot")

app.add_middleware(
    CORSMiddleware,
    allow_origins=settings.cors_origins,
    allow_credentials=True,
    allow_methods=["*"],
    allow_headers=["*"],
)

@app.get("/health")
async def health() -> dict[str, str]:
    return {"status": "ok"}

Run it from backend/:

uv run uvicorn app.main:app --reload
# INFO:     Uvicorn running on http://127.0.0.1:8000

Open http://127.0.0.1:8000/health and you should see {"status": "ok"}.

Make the app package importable

Those from app.config import settings imports only work because of a small section in pyproject.toml that most tutorials skip:

[build-system]
requires = ["hatchling"]
build-backend = "hatchling.build"

[tool.hatch.build.targets.wheel]
packages = ["app"]

This tells uv to install the local app/ folder as an editable package, so from app... resolves from uvicorn, tests, notebooks, and direct script execution alike, regardless of working directory. Dave proves it matters by deleting the section, running uv sync, and watching the import fail on the spot. Without it, imports depend on where you happen to run Python from, which is exactly the kind of path fragility he warns about, "when you are following tutorials everything is very cookie cutter, but this is where you spend time in the real world."

The data model

The models live one file per table in backend/app/database/models/, which keeps navigation simple. Click a model, see its table. Seven tables carry the whole product:

The retrieval-critical columns sit on document_chunks in backend/app/database/models/document_chunk.py:

class DocumentChunk(Base):
    __tablename__ = "document_chunks"

    text: Mapped[str] = mapped_column(Text, nullable=False)
    embedding: Mapped[list[float] | None] = mapped_column(
        Vector(EMBEDDING_DIMENSIONS),  # vector(1536), pgvector
        nullable=True,
    )
    chunk_metadata: Mapped[dict[str, Any]] = mapped_column(
        JSONB,
        nullable=False,
        server_default=sql_text("'{}'::jsonb"),
    )

The Vector(1536) column comes from the pgvector extension and matches the OpenAI embedding dimensions in config. A generated search_vector tsvector column is added in the migration itself (more on that below), giving you semantic and keyword search on the same table.

The one table worth pausing on is message_citations. In the video, Dave does not accept it blindly. The AI proposed it, he did not understand why, and he pushed back with "what's the idea behind the message citation table, why do we need that?" The answer convinced him to keep it. It records exactly which chunk each assistant message relied on, with the excerpt and filing metadata denormalized for the UI. A separate table with foreign keys to both chat_messages and document_chunks enforces the grounding contract at the schema level, rather than trusting the model to embed citations in free text. That interrogate-then-decide loop is the process he wants you to copy, "not just blindly accepting everything, but pushing back."

Migrate with Alembic

Alembic tracks schema changes over time. You change the SQLAlchemy models, autogenerate a migration, review it, and apply it. backend/alembic/env.py imports the app metadata and reads the connection string from settings, so there is only one place the database URL lives.

from app.config import settings
from app.database.models import Base  # register models on metadata

target_metadata = Base.metadata

def get_database_url() -> str:
    return settings.sqlalchemy_database_url

Autogenerate handles plain tables, but it cannot infer Postgres-specific features. The first migration (backend/alembic/versions/3c81ca3fef26_initial_schema.py) adds them as explicit op.execute statements. Those cover the vector extension, the generated tsvector column, the HNSW and GIN indexes, and row level security policies so users can only read their own chats.

op.execute("CREATE EXTENSION IF NOT EXISTS vector")

op.execute(
    """
    ALTER TABLE document_chunks
    ADD COLUMN search_vector tsvector
    GENERATED ALWAYS AS (to_tsvector('english', text)) STORED
    """
)
op.execute(
    """
    CREATE INDEX ix_document_chunks_embedding_hnsw
    ON document_chunks
    USING hnsw (embedding vector_cosine_ops)
    """
)
op.execute(
    """
    CREATE INDEX ix_document_chunks_search_vector_gin
    ON document_chunks
    USING gin (search_vector)
    """
)

Always review what autogenerate produced before applying it. Then run the migration:

uv run alembic upgrade head

Dave's first attempt fails with an import error around psycopg. The catch is that psycopg2 and psycopg (version 3) are different drivers, and SQLAlchemy defaults to psycopg2 for plain postgresql:// URLs. The fix is a small helper on the settings class that rewrites the Supabase URL for the psycopg v3 driver, which keeps the raw DATABASE_URL in .env untouched:

@computed_field
@property
def sqlalchemy_database_url(self) -> str:
    """Normalize Supabase-style URLs for SQLAlchemy + psycopg v3."""
    url = self.database_url
    if url.startswith("postgresql://"):
        return url.replace("postgresql://", "postgresql+psycopg://", 1)
    if url.startswith("postgres://"):
        return url.replace("postgres://", "postgresql+psycopg://", 1)
    return url

This is also the moment Dave starts committing, "now we have an actual project, we want to keep track of the changes, so we can clearly see our diffs when AI starts to mess with some things."

Checkpoint

Before moving on, confirm:

• uv run uvicorn app.main:app --reload starts, and GET /health returns {"status": "ok"}
• from app.config import settings works, and removing a required env var makes startup fail with a validation error
• uv run alembic upgrade head completes without errors
• The Supabase Table Editor shows users, source_documents, document_chunks, chat_threads, chat_messages, message_citations, and alembic_version
• The embedding column on document_chunks shows type vector in Supabase