Persistence and auth

Budget: 1-2 weeks.

Why this matters

SessionStore in models.py is an in-memory Python dict. Restart the server and everything a user built is gone. There are no user accounts, so every visitor shares the same nginx basic-auth credential. This is the single largest gap between the current POC and anything you can charge for.

This is also the chapter where your backend instincts carry you the furthest. You already know SQL, you already know how to model domains. What is new is the Python async plus SQLAlchemy 2.0 patterns, and wiring authentication into FastAPI.

What to learn

Topic	Time
Postgres refresher: transactions, indexes, JSONB for VRP payloads	1 day
SQLAlchemy 2.0 async sessions, or SQLModel if you prefer Pydantic	2-3 days
Alembic migrations: autogenerate, edit, apply, roll back	1 day
Pick and set up a managed auth provider (Clerk, Supabase Auth, WorkOS)	1-2 days
JWT verification as a FastAPI dependency	0.5 day
Repository layer that scopes every query by `user_id`	1-2 days
Design the schema (users, sessions, scenarios, solutions)	0.5 day
Replace `SessionStore` and write the isolation integration test	2-3 days

Resources

SQLAlchemy 2.0 docs: start with the "Unified Tutorial."
SQLAlchemy async ORM: the async session patterns you will use with FastAPI.
SQLModel: Pydantic-flavored alternative if you prefer it.
Alembic tutorial: migrations in half a day.
FastAPI dependencies: where your JWT check will live.
FastAPI SQL databases: official async SQL walkthrough.
Managed auth providers: Clerk, Supabase Auth, WorkOS.

Exercise

Design and implement the schema for users, sessions, scenarios, and solutions. Keep the VRP request and solution payloads as JSONB. Replace SessionStore with a repository that reads and writes Postgres. Add a FastAPI dependency that resolves a JWT to a user_id and injects it into every request. Write one integration test that proves a user cannot read another user's scenario even if they guess the ID.

Schema sketch

create table users (
  id uuid primary key,
  email text unique not null,
  created_at timestamptz not null default now()
);

create table sessions (
  id uuid primary key,
  user_id uuid not null references users(id),
  name text not null,
  created_at timestamptz not null default now()
);

create table scenarios (
  id uuid primary key,
  session_id uuid not null references sessions(id),
  name text not null,
  request jsonb not null,
  original_request jsonb not null,
  created_at timestamptz not null default now()
);

create table solutions (
  id uuid primary key,
  scenario_id uuid not null references scenarios(id),
  status text not null,
  payload jsonb,
  created_at timestamptz not null default now()
);

create index on sessions (user_id);
create index on scenarios (session_id);
create index on solutions (scenario_id);