Database Layer — database.py#

One engine, one session factory, one dependency, one migration function — the entire persistence plumbing in 48 lines.

Purpose#

backend/app/database.py owns everything about how the app talks to SQLite: the engine, the session factory, the FastAPI dependency that scopes a session to a request, and the ad-hoc migration runner.

The file, annotated#

from sqlalchemy.orm import sessionmaker, declarative_base
from sqlalchemy import create_engine, text
 
DATABASE_URL = "sqlite:///./agentops.db"        # (1)!
 
engine = create_engine(
    DATABASE_URL,
    connect_args={"check_same_thread": False}   # (2)!
)
 
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
 
Base = declarative_base()                        # (3)!
 
 
def run_startup_migrations():
    """Apply small SQLite schema updates that create_all will not handle."""
    if not DATABASE_URL.startswith("sqlite"):
        return
 
    tool_call_columns = {
        "model_name": "VARCHAR(100)",
        "estimated_tokens": "INTEGER",
        "estimated_cost": "VARCHAR(30)",
    }
 
    with engine.begin() as connection:           # (4)!
        existing_columns = {
            row[1]
            for row in connection.execute(text("PRAGMA table_info(tool_calls)"))
        }
        for column_name, column_type in tool_call_columns.items():
            if column_name not in existing_columns:
                connection.execute(
                    text(f"ALTER TABLE tool_calls ADD COLUMN {column_name} {column_type}")
                )
 
def get_db():
    db = SessionLocal()
    try:
        yield db                                  # (5)!
    finally:
        db.close()

1. Relative path — resolves against the working directory, so the DB file lands in backend/app/agentops.db when the server is started correctly.
2. SQLite refuses cross-thread connection use by default; FastAPI's threadpool for sync endpoints requires disabling that check.
3. The declarative base every model in models.py inherits from.
4. engine.begin() wraps the column additions in a transaction; PRAGMA table_info makes the migration idempotent.
5. Generator dependency: FastAPI injects the session, and finally: db.close() guarantees cleanup even when the handler raises.

Responsibilities#

• Construct the singleton engine and SessionLocal factory.
• Provide Base for model registration.
• Provide get_db() — the only sanctioned way handlers obtain a session.
• Evolve existing databases via run_startup_migrations() (called once at boot from main.py).

Inputs / outputs#

Usage example#

Every endpoint follows this pattern:

from database import get_db
 
@router.get("/{agent_id}")
def get_agent(agent_id: int, db: Session = Depends(get_db)):
    agent = db.query(models.Agent).filter(models.Agent.id == agent_id).first()
    ...

Sessions are request-scoped: opened at injection, closed when the response is done. Handlers control transactions explicitly with db.commit() — there is no autocommit.

Edge cases#

• Working directory matters. Started from the wrong directory, the app creates a fresh empty database wherever it runs — the classic "where did my data go?" issue (Troubleshooting).
• Migrations only add. Removed or retyped columns are not handled; the strategy only ever appends nullable columns, which is why it is safe to run on every boot.
• Non-SQLite URLs short-circuit the migration function — a guard for a future Postgres move.

Performance notes#

SQLite is effectively single-writer; concurrent write-heavy load will serialize. Reads are fast at this scale. autoflush=False means queries inside a handler do not see un-flushed pending rows unless flushed/committed — the routers commit before reading back (e.g., db.refresh(task) after db.commit()).

Security notes#

The migration SQL uses f-strings, but inputs come from a hardcoded dict — no user data reaches it. All application queries go through the ORM (parameterized). See Security.

Future improvements#

• Make DATABASE_URL an environment variable.
• Adopt Alembic once the schema churns regularly (Roadmap).
• Consider sqlite WAL mode for better read/write concurrency.

Related files#

• ORM Models — what the engine persists
• Migrations — the strategy in depth
• Add a Migration — the how-to