# FastAPI + SQLite + Basic Auth Backend Pattern

Template for single-file FastAPI apps that need persistent storage with seed data and HTTP Basic Auth. Used in Vermögen, adaptable to any personal data tracker.

## Architecture

```python
"""
AppName — Description
FastAPI + SQLite + Basic Auth + Static Frontend
"""
import os, sqlite3, datetime
from pathlib import Path
from contextlib import contextmanager
from fastapi import FastAPI, Request, HTTPException, Depends
from fastapi.responses import FileResponse
from fastapi.security import HTTPBasic, HTTPBasicCredentials
from fastapi.middleware.cors import CORSMiddleware

FRONTEND_DIR = Path(__file__).parent / "frontend"
DATA_DIR = Path(os.environ.get("DATA_DIR", str(Path(__file__).parent / "data")))
DB_PATH = DATA_DIR / "app.db"
AUTH_USER = "user"
AUTH_PASS = "password"

DATA_DIR.mkdir(parents=True, exist_ok=True)

app = FastAPI(title="AppName")
security = HTTPBasic()
```

## Database (with context manager + WAL)

```python
@contextmanager
def get_db():
    conn = sqlite3.connect(str(DB_PATH))
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA journal_mode=WAL")
    conn.execute("PRAGMA foreign_keys=ON")
    try:
        yield conn
        conn.commit()
    finally:
        conn.close()

def init_db():
    with get_db() as db:
        db.execute("""
            CREATE TABLE IF NOT EXISTS items (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                category TEXT NOT NULL,
                value REAL DEFAULT 0,
                note TEXT,
                sort_order INTEGER DEFAULT 0,
                updated_at TEXT DEFAULT (datetime('now','localtime'))
            )
        """)
        # Seed only if table is empty
        count = db.execute("SELECT COUNT(*) FROM items").fetchone()[0]
        if count == 0:
            seed_items = [
                ("Item 1", "Category A", 100.0, "Note 1", 1),
                ("Item 2", "Category B", 200.0, None, 2),
            ]
            for item in seed_items:
                db.execute("""
                    INSERT INTO items (name, category, value, note, sort_order)
                    VALUES (?, ?, ?, ?, ?)
                """, item)

init_db()  # Called at module load
```

## Key patterns

- Use `@contextmanager` for clean connection lifecycle with auto-commit
- `row_factory = sqlite3.Row` allows dict-like column access: `row["name"]`
- `PRAGMA journal_mode=WAL` for concurrent reads
- Seed data runs only when table is empty (`COUNT(*) == 0`)
- `datetime('now','localtime')` for local timezone timestamps
- `DATA_DIR` from env var with sensible default — enables Docker volume mounts

## Typical API endpoints

```python
@app.get("/api/items")
def list_items(auth=Depends(check_auth)):
    with get_db() as db:
        rows = db.execute("SELECT * FROM items ORDER BY sort_order").fetchall()
    return [dict(r) for r in rows]

@app.put("/api/items/{item_id}")
async def update_item(item_id: int, request: Request, auth=Depends(...)):
    data = await request.json()
    allowed = ["name", "category", "value", "note"]
    updates = {k: v for k, v in data.items() if k in allowed}
    if not updates:
        raise HTTPException(400, "No valid fields")
    set_clause = ", ".join(f"{k}=?" for k in updates)
    values = list(updates.values())
    with get_db() as db:
        db.execute(
            f"UPDATE items SET {set_clause}, updated_at=datetime('now','localtime') WHERE id=?",
            values + [item_id]
        )
    return {"status": "ok", "id": item_id}

@app.post("/api/items")
async def create_item(request: Request, auth=Depends(...)):
    data = await request.json()
    name = data.get("name", "").strip()
    if not name:
        raise HTTPException(400, "Name required")
    with get_db() as db:
        max_sort = db.execute("SELECT COALESCE(MAX(sort_order), 0) FROM items").fetchone()[0]
        cursor = db.execute("""
            INSERT INTO items (name, category, value, note, sort_order)
            VALUES (?, ?, ?, ?, ?)
        """, (name, data.get("category", ""), data.get("value", 0), data.get("note"), max_sort + 1))
        return {"status": "ok", "id": cursor.lastrowid}

@app.delete("/api/items/{item_id}")
def delete_item(item_id: int, auth=Depends(...)):
    with get_db() as db:
        db.execute("DELETE FROM items WHERE id=?", (item_id,))
    return {"status": "ok"}
```

## Dockerfile (minimal)

```dockerfile
FROM python:3.11-slim
RUN pip install --no-cache-dir fastapi uvicorn
WORKDIR /app
COPY main.py .
COPY frontend/ frontend/
ENV DATA_DIR=/data
EXPOSE 3003
CMD ["uvicorn", "main:app", "--host", "0.0.0.0", "--port", "3003"]
```

No dependencies beyond stdlib + FastAPI + uvicorn. SQLite is built-in.