library-management-system-dbms

Flask + MySQL (TiDB Cloud) library system with LangGraph 5-agent AI concierge, SSE chat, FKs, transactions, auth + CSRF on Render.

Comment

Inspiration

This started as my college DBMS course project. My v1 — library-management-system — worked, but it cheated: all data lived in Google Sheets via gspread on Vercel. No foreign keys, no transactions, no JOINs. My professor called it out: "that's not a DBMS."

So for v2 I did a proper rewrite: library-management-system-dbms — same Flask app and same Lumina AI concierge I already built, but all CRUD moved to a normalized MySQL database on TiDB Cloud with real integrity, auth, and audit logging. Deployed on Render.

Live Demo: library-management-system-dbms-rryf.onrender.com

V1 (Sheets): github.com/kairav7220/library-management-system

V2 (DBMS, current): github.com/kairav7220/library-management-system-dbms

What it does

Full library workflow for a college library — or just talk to it:

  • Database-backed CRUD — 12 normalized MySQL tables: users, books, categories, genres, members, employees, subscriptions, payments, sales, issues, logs, customers
  • Login + role gate — session auth with scrypt-hashed passwords, admin-only /logs audit page, every login/logout written to DB
  • CSRF protection — Flask-WTF per-session tokens, tokenless POSTs rejected with 400
  • Lumina Concierge (reused from v1) — 5 LangGraph agents: Catalog, Circulation, Membership, Reference, Director, routed by intent classifier. Example: "issue book 5 to member 2", "who has overdue books?"
  • SSE streaming chat — token-level streaming with typing indicator, session history + resume
  • DB integrity — FKs like book_sell.mem_id -> members(mem_id), book_issues.book_id -> books(book_id), sales + payments as atomic transactions
  • Optional RAG — Mistral embeddings + pgvector semantic book search

How we built it

I reused the v1 frontend + agents wholesale and swapped the data layer:

V1: Flask -> gsheets_client.py -> Google Sheets + Vercel serverless
V2: Flask -> tools/db.py (PyMySQL) -> MySQL / TiDB Cloud + Render (gunicorn flask_app:app)
Chat: Widget (Material 3) -> POST /chat/stream (SSE) -> Intent Classifier (LCEL)
-> 5 ReAct agents -> per-entity tools -> MySQL

Stack: Python 3.11, Flask + Jinja2, LangGraph + LangChain, Mistral open-mistral-7b, MySQL 8 (TiDB Cloud serverless), PyMySQL with auto-TLS for tidbcloud.com, Render Web Service, optional Postgres DATABASE_URL for RAG + chat sessions (SQLite fallback).

Schema + seed in schema.sql, sample JOINs/aggregates/transactions in queries.sql, reset_db.py drops + rebuilds everything.

Challenges we ran into

  • Sheets -> MySQL migration: old orphan rows violated FKs on import. Had to clean data and reseed from scratch.
  • Vercel -> Render: Vercel serverless can't hold MySQL pools + SSE streams. Moved to Render persistent service with gunicorn.
  • TiDB TLS: TiDB requires --ssl-mode=REQUIRED, local MySQL doesn't. Fixed with auto-TLS when host contains tidbcloud.com.
  • CSRF broke chat: enabling Flask-WTF blocked SSE POSTs. Kept CRUD protected, handled chat routes separately.
  • Agent reuse bugs: v1 tools expected row-numbers (Sheets), v2 uses primary keys. Rewrote all tools/*.py to SQL while keeping agent prompts intact.

Accomplishments that we're proud of

  • Turned a "Sheets hack" into a real DBMS that passes college viva: ER design, FKs, transactions, queries.sql demos
  • Reused 90% of v1 AI code (agents, graph, widget) without rewriting — proper v1 -> v2 evolution
  • Shipped auth + audit + CSRF most student projects skip, live on Render with streaming chat

What we learned

  • Sheets is not a database — FKs and transactions catch bugs UI never will
  • How to design normalized schema (12 tables) and debug with JOINs/aggregates
  • Serverless vs persistent hosting tradeoffs for DB + SSE apps
  • Reusing agents across backends: keep prompts stable, swap only tools

What's next for Library Management System DBMS

  • Overdue fine automation + email reminders
  • Barcode scan for issue/return
  • Member vs employee dashboards
  • Merge RAG into MySQL (or single Postgres) so no second DB needed
  • Page citations for RAG answers + eval dashboard showing routing + latency

Built with: Python, Flask, MySQL, TiDB Cloud, LangGraph, Mistral AI, Jinja2, SSE, Render, scrypt, Flask-WTF

Share this project:

Updates