StreamVault
A Flask and MySQL streaming-platform backend with bcrypt authentication, role-based access control, and deadlock-safe transactions
Implemented the authentication, security, and transaction layer for a multi-role streaming-platform backend, then proved it with six advanced SQL queries and a live analytics dashboard.
I built the backend for a streaming-platform management system: Flask authentication with bcrypt hashing, role-based access control for customer and employee workflows, deadlock-safe transactions, and a Chart.js analytics dashboard backed by six advanced SQL queries.
Built as a 3-person NYU coursework team. Based on the commit history, I implemented the backend end-to-end: authentication and RBAC, the parameterized data-access layer, deadlock-retry transaction handling, the customer and employee workflows, the forgot-password flow, and the six-query analytics dashboard.
Overview
StreamVault is a Flask and MySQL backend for managing a streaming platform's catalog, productions, and viewer feedback. It authenticates customers and employees with bcrypt-hashed passwords and role-based access control, gives employees full CRUD over series, episodes, production houses, and contracts, and gives customers a feedback and profile flow. A self-service password-reset path issues expiring single-use tokens, and an analytics dashboard surfaces viewer and ratings trends through six advanced SQL queries with query-level caching.
Problem
A streaming-platform backend needs to authenticate two different user roles safely, stay correct under concurrent writes, close off SQL injection everywhere, not just in the obvious form fields, and turn raw catalog data into queryable business insight rather than plain CRUD.
Intended User
Built for a streaming platform's two user roles: viewers browsing and reviewing series, and staff managing the catalog, productions, and contracts.
Architecture
Flask routes for customer and employee workflows sit behind a shared authentication layer that hashes passwords with bcrypt and enforces access through a role_required decorator. Every query, including pagination's LIMIT/OFFSET, runs through a parameterized data-access layer in db.py, so user input is never concatenated into SQL. A transaction() context manager wraps writes with deadlock detection (MySQL error codes 1213/1205) and automatic retry, keeping each transaction short and touching tables in a consistent order to limit contention. The analytics dashboard runs six SQL queries, multi-table joins, a correlated subquery, a CTE-based tier classification, a UNION-based regional comparison, and top-N/bottom-N rankings, with results cached in memory and rendered through Chart.js.
My Contribution
Built as a 3-person NYU coursework team, I implemented the backend end-to-end: the Flask application factory and configuration, the authentication and RBAC system, the parameterized data-access layer, the deadlock-retry transaction wrapper, the customer and employee route handlers, the forgot-password flow, and the analytics dashboard's six SQL queries.
Implementation
- Implemented bcrypt password hashing (cost factor 12) with a timing-safe comparison for login.
- Built role-based access control with a role_required decorator gating employee-only routes.
- Closed the one SQL-injection gap in the data-access layer by replacing string-interpolated LIMIT/OFFSET pagination with parameterized queries.
- Wrote a deadlock-retry transaction wrapper that detects MySQL error codes 1213 and 1205 and retries up to 3 times.
- Designed the forgot-password flow: a secrets.token_urlsafe(32) reset token, single-use enforcement, a 60-minute expiry, and an enumeration-resistant generic response.
- Built the analytics dashboard's six SQL business queries: joins, a correlated subquery, a CTE, a UNION set comparison, and top-N/bottom-N rankings, with in-memory query caching.
Key Decisions
Parameterized queries everywhere, including pagination
Why — Passing every user input, even integer pagination parameters, through %s placeholders rather than string interpolation made the SQL-injection-prevention claim hold across the entire data-access layer, not just the obvious form fields.
Deadlock detection with bounded retry
Why — Concurrent writes from customer and employee workflows could collide on the same rows. Catching MySQL error codes 1213 and 1205 and retrying kept those collisions from surfacing as user-facing failures.
Trade-off — Retries add latency to a contended write, and the retry ceiling means a write can still fail under sustained contention.
Role-based access control via a route decorator
Why — A single role_required decorator enforced employee-only routes (CRUD on series, episodes, contracts) consistently rather than checking role inline in every handler.
Single-use, time-boxed password-reset tokens
Why — A secrets.token_urlsafe(32) token that expires in 60 minutes and is marked used after redemption closes the replay window on the password-reset flow.
Testing & Validation
Validated through the documented demo-account flows for both roles (an employee admin account and four customer accounts) and the README's recorded UI walkthrough, with the parameterized-query and bcrypt-hashing approach verified directly in the data-access and security modules.
Results
Implemented full authentication and role-based access control for customer and employee workflows, a parameterized data-access layer with no string-interpolated SQL, a deadlock-retry transaction wrapper, and an analytics dashboard backed by six advanced SQL queries spanning joins, a correlated subquery, a CTE, a UNION set operator, and top-N/bottom-N rankings.
Reliability & Failure Handling
The deadlock-retry transaction wrapper keeps writes short, accesses tables in a consistent order, and automatically retries on MySQL deadlock or lock-wait-timeout errors before surfacing a failure.
Deployment & Runtime
Validated through local MySQL execution, demo-account workflows, and the documented project walkthrough.