UniLib — University Library System
A full-stack institutional library management platform covering book cataloging, member workflows, ACID transaction safety, audit logging, and real-time circulation analytics.
FTS5 indexes cut catalog queries from 340ms to <18ms on 10K+ books.
All borrow/return flows use SQLite IMMEDIATE transactions. Zero race conditions.
Every circulation event is written as an immutable ledger row.
Title metadata separate from physical copies — one author update, not thousands of rows.
The Problem & Context
Many university departments manage physical library assets through fragmented spreadsheets or manual paper ledgers. This results in lost inventory, untracked book renewals, and manual calculation of late fines.
UniLib was built to provide an institutional-grade platform: centralizing thousands of titles, automating member check-out/return cycles, and giving department heads live metrics on book circulation through a clean admin dashboard.
System Architecture
Clean separation between the React frontend and Flask API with a single SQLite database file:
Key Capabilities
Book Catalog & Two-Tier Model
Title metadata is decoupled from physical copy barcodes. A single book title record points to multiple copy records with distinct locations, conditions, and availability states.
Member Identity & Loan Quotas
Role-based quotas: faculty can borrow up to 10 books for 60 days; students are capped at 3 books for 14 days. Enforced at the database transaction layer.
ACID Borrow/Return Engine
All checkout and checkin operations execute inside isolated transactions. Copy status, member active count, and audit log write succeed or roll back together atomically.
Automated Overdue & Fine Engine
A daily cron task evaluates all open loan records against current timestamps, calculates late fee penalties according to institutional rules, and sends email reminders.
Circulation Analytics Dashboard
Visual breakdown of popular genres, highest-demand titles, member borrowing velocity, and historical loss rates powered by Recharts on the frontend.
Batch CSV Import & Export
Department staff can bulk-upload existing catalog spreadsheets. The import engine validates ISBN checksums, flags duplicates, and imports records in bulk transactions.
Technical Implementation: Atomic Borrow Transaction
To prevent race conditions when two students attempt to borrow the last available copy of a popular textbook at the same instant, checkout operations use an immediate transaction with strict row-locking:
def borrow_book(copy_id: str, member_id: str) -> dict:
conn = get_db()
# Acquire write lock immediately — prevents read-modify-write race
conn.execute("BEGIN IMMEDIATE")
try:
copy = conn.execute(
"SELECT status, book_id FROM book_copies WHERE id = ?", (copy_id,)
).fetchone()
if not copy or copy['status'] != 'available':
raise ValueError("Copy is no longer available")
# Update copy status and insert borrow record atomically
conn.execute("UPDATE book_copies SET status = 'borrowed' WHERE id = ?", (copy_id,))
conn.execute(
"INSERT INTO loans (copy_id, member_id, borrowed_at, due_at) VALUES (?, ?, ?, ?)",
(copy_id, member_id, now(), calculate_due_date(member_id))
)
conn.commit()
return {"success": True}
except Exception:
conn.rollback()
raiseEngineering Challenges & Solutions
Challenge 1: Race Conditions in Last-Copy Reservation
When multiple students simultaneously reserve the final copy of a popular textbook, race conditions caused negative stock counts. Solved with SQLite BEGIN IMMEDIATE — acquiring a write lock before reads, making check-then-decrement truly atomic.
Challenge 2: Slow Multi-Field Catalog Search
Searching across tens of thousands of books by title, author, ISBN, and tags caused slow table scans (340ms+). Created SQLite FTS5 + B-tree indexes with weighted bm25 scoring, reducing query response times to under 18ms.
Design Decisions & Trade-offs
SQLite over PostgreSQL
Why: Zero external infrastructure — runs on a single binary with no server setup. Trade-off: Single-writer limits concurrent throughput, acceptable for library-scale circulation volumes.
Two-Tier Book Model
Why: Separating title metadata from physical copies means updating an author name affects one row, not hundreds of copy records. Trade-off: More joins in queries, mitigated by indexing.
Client-Side React, Server-Side Flask
Why: Keeps frontend lightweight and backend simple — no SSR complexity. Trade-off: Heavier initial JS bundle, acceptable for an internal institutional admin tool.
What I'd Do Differently
I'd move catalog search to a dedicated engine like Meilisearch — the weighted FTS5 scoring I built manually works well, but a purpose-built engine gives typo tolerance and ranking out of the box. I'd also add Alembic migrations from day one — starting with raw SQL DDL made schema evolution painful as requirements shifted mid-build.
Lessons Learned & Outcomes
Building UniLib reinforced how critical atomic transactions are in inventory systems. Even a brief race condition can cascade into lost or duplicated book records. The indexing work also showed that query optimization is an ongoing process — as the catalog grows, new access patterns demand fresh indexes.
The system successfully automated the full circulation workflow for academic libraries, eliminating manual ledger errors and giving administrators real-time visibility into book demand and member activity.