Stage 2 · L15

Give each database session a clear owner and lifetime

Core · original Session 2; extended normalization/cardinality visuals

The engine owns connections and pooling. A Session is a unit of work: it tracks objects and coordinates one transaction's database interaction. It is not a global request cache and should not be shared between concurrent requests. configure_database creates one engine/session factory; session_dependency opens a session for one request and closes it on exit.

session.add marks an object pending. flush sends its INSERT within the current transaction and obtains generated identifiers; it does not commit. A second connection cannot see the uncommitted row. commit completes the transaction so fresh sessions can see it. rollback cancels pending database changes; closing an unfinished session rolls them back. The baseline proves this with an observer session before and after rollback.

refresh reloads a row from PostgreSQL. The transfer uses it after flush to read the server-generated created_at. Returning a freshly generated UUID is not the same as proving its row was committed. Keep the return inside session_factory.begin's context: successful exit commits before the function returns to its caller; an exception from the rental constraint rolls back both inserted rows.

FastAPI's yield dependency owns cleanup. Depends(..., scope='function') closes the session before the response is sent in this pinned runtime. Cleanup alone does not choose the successful business commit. Stage 03 explicitly commits the write before claiming 201; stage 04 moves that decision to the service layer. expire_on_commit=False prevents surprise reloads of simple fields after commit, but does not make lazy relationships safe.

After an IntegrityError, SQLAlchemy requires rollback before that same session can do more work. Prefer a clear transaction context that rolls back on exception. The stage-end capstone's invalid child quantity demonstrates atomic parent/child rollback through a fresh session. It does not yet enforce total stock, authorization or idempotency; later stages add those business rules.

with SessionLocal() as session:
    session.add(customer)
    session.flush()  # INSERT inside this transaction; obtains its generated key.
    session.refresh(customer)
    session.rollback()  # Nothing became durable.

A second connection cannot see the uncommitted row. Rollback leaves no inserted row. Successful session_factory.begin exit commits before the function returns, so a fresh observer can read both related rows. refresh reloads generated values; flush is not durable completion. Apply these taught operations in the separate stage capstone.

python run_checks.py --stage 02 --role starter --prepare
python run_checks.py --stage 02 --role starter
python run_checks.py --stage 02 --role starter --transfer

python run_checks.py --stage 02 --role solution --transfer

def record_customer_rental(session_factory, email, equipment_id, quantity):
    # Capacity/permissions are deliberately not promised here: stage 05/07 adds them.
    with session_factory.begin() as session:
        equipment = session.get(Equipment, equipment_id)
        if equipment is None:
            raise ValueError("Equipment not found")
        customer = User(email=email)
        session.add(customer)
        session.flush()  # Obtain its UUID before assigning the foreign key.
        rental = Rental(equipment_id=equipment.id, owner_id=customer.id, quantity=quantity)
        session.add(rental)
        session.flush()
        session.refresh(rental)  # Read the database-generated created_at.
        return customer.id, rental.id, rental.created_at