StreamVault - Web Series Streaming Platform

CS-GY 6083 - Principles of Database Systems
NYU Tandon School of Engineering - Fall 2025
Project Part II

Team Members

Gopala Krishna Abba (ga2664)
Riya Patil (rsp9219)
Neha Nainan (nan6504)

Project Overview

StreamVault is a web-based streaming platform management system built for the CS-GY 6083 Project Part II. It extends the database schema from Part I (GRN_* tables) with authentication capabilities and provides a full CRUD interface for managing web series, episodes, production houses, contracts, and user feedback.

Key Features

User Authentication: Registration, login, logout with bcrypt password hashing
Forgot Password Flow: Secure email-based password reset with expiring tokens
Role-Based Access Control: Customer and Employee roles with different permissions
Customer Features: Browse series, view details, submit/edit feedback, manage profile
Employee Features: Full CRUD on all entities (series, episodes, producers, contracts)
Analytics Dashboard: Chart.js visualizations with query caching
Deadlock Protection: Automatic retry mechanism for database transaction conflicts
Netflix-Style UI: Professional Bebas Neue + Montserrat typography with iconic logo
Dark Theme: Cinematic dark theme with red accent colors and optimized visibility

Screenshots

Homepage & Authentication

Homepage

Netflix-style animated splash screen with cinematic background

Login Page

Secure login interface with password visibility toggle

Sign Up Pages

User registration form with validation

Registration form with password strength requirements

Customer Experience

Browse Screen

Series browsing interface with card-based layout

Series Detail Pages

Series detail page with episodes, reviews, and trailer

Series information with production details and user feedback

Admin Dashboard

Admin Dashboard Views

Admin dashboard with series management interface

CRUD operations for managing web series and episodes

Analytics Dashboard

Analytics Visualizations

Top series by viewers and ratings with Chart.js

Series distribution by country and genre

Production house performance metrics

Tech Stack

Component	Technology
Backend	Python 3.13, Flask 3.0
Database	MySQL 9.5 (Homebrew)
DB Connector	mysql-connector-python 8.2.0
Template Engine	Jinja2
Password Hashing	bcrypt 4.1.2
Frontend	HTML5, CSS3, Bootstrap 5.3
Charts	Chart.js 4.x (via CDN)
Icons	Bootstrap Icons

Project Structure

project_part2/
├── app/
│   ├── __init__.py          # Flask application factory
│   ├── config.py            # Configuration settings
│   ├── db.py                # Database connection & helpers (SQL injection prevention)
│   ├── security.py          # Password hashing, input validation, XSS protection
│   ├── auth.py              # Authentication routes & decorators
│   ├── routes_customer.py   # Customer-facing routes
│   ├── routes_employee.py   # Admin/employee routes (CRUD)
│   ├── routes_analytics.py  # Analytics dashboard (extra credit)
│   ├── email_utils.py       # Email sending for password reset
│   ├── templates/           # Jinja2 templates
│   │   ├── base.html
│   │   ├── auth/
│   │   ├── customer/
│   │   ├── employee/
│   │   ├── analytics/
│   │   └── errors/
│   └── static/
│       └── css/style.css    # Dark theme with full text visibility
├── run.py                   # Application entry point (port 5001)
├── schema_mysql.sql         # MySQL DDL (all tables + indexes)
├── seed_data.sql            # Sample data for demo
├── business_queries.sql     # Q1-Q6 advanced SQL queries
├── requirements.txt         # Python dependencies
├── README.md                # This file
└── report_outline.md        # Report template

Setup Instructions

Prerequisites

Python 3.8+ (tested with Python 3.13)
MySQL 8.0+ or Homebrew MySQL on macOS
macOS, Linux, or Windows

Option A: Setup on macOS with Homebrew (Recommended)

1. Install MySQL via Homebrew

brew install mysql

2. Configure MySQL for Port 3307 (to avoid conflicts)

Create/edit /opt/homebrew/etc/my.cnf:

[mysqld]
port=3307
socket=/opt/homebrew/var/mysql/mysql.sock

[client]
port=3307
socket=/opt/homebrew/var/mysql/mysql.sock

3. Start MySQL Service

brew services start mysql

4. Create Database and User

# Connect to MySQL (no password for fresh install)
/opt/homebrew/bin/mysql -u root --socket=/opt/homebrew/var/mysql/mysql.sock

# In MySQL shell, run:
CREATE DATABASE IF NOT EXISTS streaming_platform;
CREATE USER IF NOT EXISTS 'streaming_user'@'localhost' IDENTIFIED BY 'streaming_password';
GRANT ALL PRIVILEGES ON streaming_platform.* TO 'streaming_user'@'localhost';
FLUSH PRIVILEGES;
EXIT;

5. Load Schema and Seed Data

cd ~/project_part2

# Load schema
/opt/homebrew/bin/mysql -u streaming_user -pstreaming_password \
  --socket=/opt/homebrew/var/mysql/mysql.sock \
  streaming_platform < schema_mysql.sql

# Load sample data
/opt/homebrew/bin/mysql -u streaming_user -pstreaming_password \
  --socket=/opt/homebrew/var/mysql/mysql.sock \
  streaming_platform < seed_data.sql

6. Install Python Dependencies

cd ~/project_part2
python3 -m venv venv
source venv/bin/activate
pip install -r requirements.txt

7. Run the Application

python run.py

Access the app at: http://localhost:5001

Option B: Setup on Standard MySQL (Port 3306)

1. Create MySQL Database and User

-- Log into MySQL as root
mysql -u root -p

-- Create database
CREATE DATABASE streaming_platform;

-- Create user
CREATE USER 'streaming_user'@'localhost' IDENTIFIED BY 'streaming_password';

-- Grant privileges
GRANT ALL PRIVILEGES ON streaming_platform.* TO 'streaming_user'@'localhost';
FLUSH PRIVILEGES;

2. Run Schema and Seed Data

mysql -u streaming_user -p streaming_platform < schema_mysql.sql
mysql -u streaming_user -p streaming_platform < seed_data.sql

3. Update Configuration

Edit app/config.py to use port 3306:

MYSQL_PORT = int(os.environ.get('MYSQL_PORT') or 3306)
MYSQL_SOCKET = os.environ.get('MYSQL_SOCKET') or None  # Remove socket

4. Install Dependencies and Run

cd project_part2
python -m venv venv
source venv/bin/activate  # On Windows: venv\Scripts\activate
pip install -r requirements.txt
python run.py

Environment Variables (Optional)

Create a .env file to override defaults:

SECRET_KEY=your-secret-key-here
MYSQL_HOST=localhost
MYSQL_PORT=3307
MYSQL_USER=streaming_user
MYSQL_PASSWORD=streaming_password
MYSQL_DATABASE=streaming_platform
MYSQL_SOCKET=/opt/homebrew/var/mysql/mysql.sock
CACHE_ENABLED=true
CACHE_TTL=300

Current Configuration

Setting	Value
Flask Port	5001 (changed from 5000 to avoid macOS AirPlay conflict)
MySQL Port	3307 (Homebrew default to avoid conflict with system MySQL)
MySQL Socket	`/opt/homebrew/var/mysql/mysql.sock`
Database	`streaming_platform`
DB User	`streaming_user`
DB Password	`streaming_password`

Features & Requirements Mapping

Authentication (Requirement 4.1)

Feature	Route	File
Registration	`/register`	`auth.py`
Login	`/login`	`auth.py`
Logout	`/logout`	`auth.py`
Create Employee	`/admin/create-employee`	`auth.py`

Customer Features (Requirement 4.2)

Feature	Route	File
Browse Series	`/`, `/series`	`routes_customer.py`
Series Detail	`/series/<ws_id>`	`routes_customer.py`
Submit Feedback	`/series/<ws_id>/feedback`	`routes_customer.py`
Delete Feedback	`/series/<ws_id>/feedback/delete`	`routes_customer.py`
My Account	`/my-account`	`routes_customer.py`

Employee Features (Requirement 4.3)

Feature	Route	File
Dashboard	`/admin/`	`routes_employee.py`
Manage Series	`/admin/series/*`	`routes_employee.py`
Manage Episodes	`/admin/series/<ws_id>/episodes/*`	`routes_employee.py`
Manage Schedules	`/admin/episodes/<ep_id>/schedules/*`	`routes_employee.py`
Manage Production Houses	`/admin/production-houses/*`	`routes_employee.py`
Manage Producers	`/admin/producers/*`	`routes_employee.py`
Manage Contracts	`/admin/contracts/*`	`routes_employee.py`
Manage Associations	`/admin/associations/*`	`routes_employee.py`

Security Implementation

SQL Injection Prevention (Requirement 5.1)

File: app/db.py

All database queries use parameterized prepared statements:

def execute_query(query, params=None, fetch_one=False, fetch_all=True):
    """
    SECURITY: This function uses parameterized queries to prevent SQL injection.
    The 'params' tuple is passed separately to cursor.execute(), ensuring
    user input is never concatenated into the SQL string.
    """
    cursor.execute(query, params or ())  # Safe parameterized execution

XSS Protection (Requirement 5.2)

Files: app/security.py, app/templates/base.html

Jinja2 auto-escaping enabled globally
Additional sanitize_input() function for defense in depth
User content rendered with {{ variable }} (auto-escaped)

Password Security (Requirement 5.3)

File: app/security.py

def hash_password(password):
    """bcrypt with cost factor 12"""
    salt = bcrypt.gensalt(rounds=12)
    return bcrypt.hashpw(password.encode('utf-8'), salt).decode('utf-8')

def check_password(password, hashed_password):
    """Timing-safe comparison"""
    return bcrypt.checkpw(password.encode('utf-8'), hashed_password.encode('utf-8'))

Role-Based Access Control (Requirement 5.4)

File: app/auth.py

@login_required
@role_required('EMPLOYEE')
def admin_only_route():
    pass

Transactions (Requirement 5.5)

File: app/db.py

@contextmanager
def transaction():
    """
    DEADLOCK PREVENTION STRATEGY:
    1. Keep transactions short
    2. Access tables in consistent order
    3. Use appropriate isolation level
    """
    db.start_transaction()
    yield cursor
    db.commit()  # or rollback on exception

Part II Additional Features

1. Forgot Password Flow

Files: app/auth.py, app/email_utils.py, schema_mysql.sql

Route	Description
`/forgot-password`	Enter email/username to receive reset link
`/reset-password/<token>`	Enter new password with valid token

Security Features:

Secure Token: Generated using secrets.token_urlsafe(32)
Single-Use: Token marked as USED after password reset
Expiration: Token expires after 60 minutes (configurable)
No Account Enumeration: Generic success message regardless of account existence

Database Table:

CREATE TABLE GRN_PASSWORD_RESET (
    RESET_ID INT AUTO_INCREMENT PRIMARY KEY,
    LOGIN_ID VARCHAR(12) NOT NULL,
    TOKEN VARCHAR(255) NOT NULL UNIQUE,
    EXPIRES_AT DATETIME NOT NULL,
    USED TINYINT(1) NOT NULL DEFAULT 0,
    CREATED_AT DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (LOGIN_ID) REFERENCES GRN_LOGIN(LOGIN_ID) ON DELETE CASCADE
);

Email Configuration (.env):

MAIL_SERVER=smtp.gmail.com
MAIL_PORT=587
MAIL_USE_TLS=true
MAIL_USE_SSL=false
MAIL_USERNAME=your-email@gmail.com
MAIL_PASSWORD=your-16-char-app-password
MAIL_DEFAULT_SENDER=your-email@gmail.com

⚠️ IMPORTANT: Gmail App Password Setup (Required for Gmail SMTP)

Regular Gmail passwords will NOT work. You must create an App Password:

Enable 2-Step Verification (if not already):
- Go to: https://myaccount.google.com/security
- Click "2-Step Verification" → Turn ON
Create App Password:
- Go to: https://myaccount.google.com/apppasswords
- Select app: "Mail"
- Select device: "Mac" (or "Other")
- Click "Generate"
- Copy the 16-character password (e.g., abcdefghijklmnop)
Update .env file (no spaces in password!):
```
MAIL_PASSWORD=abcdefghijklmnop
```

Restart Flask and test:

lsof -ti:5001 | xargs kill -9
cd ~/project_part2 && source venv/bin/activate && python run.py

Security Note: App Passwords are safe - they only allow email sending and can be revoked anytime at https://myaccount.google.com/apppasswords

2. Deadlock Protection

File: app/db.py

Application-level retry mechanism for MySQL deadlocks:

MYSQL_ERROR_DEADLOCK = 1213          # ER_LOCK_DEADLOCK
MYSQL_ERROR_LOCK_WAIT_TIMEOUT = 1205 # ER_LOCK_WAIT_TIMEOUT
DEADLOCK_MAX_RETRIES = 3

@contextmanager
def transaction():
    """
    Transaction with automatic retry on deadlock.
    - Detects MySQL error codes 1213 and 1205
    - Retries up to 3 times with exponential backoff
    - Raises DeadlockError if all retries fail
    """

Deadlock Prevention Strategies:

Keep transactions SHORT - minimal work inside transaction
Access tables in CONSISTENT ORDER across application
Use appropriate ISOLATION LEVEL (InnoDB default: REPEATABLE READ)
Use PRIMARY KEY for row-level locks only

Stored Procedure Example:

CREATE PROCEDURE sp_safe_update_subscription(
    IN p_account_id VARCHAR(12),
    IN p_new_subscription DECIMAL(10,2)
)
-- Uses primary key access for minimal locking
-- Immediate commit to release locks quickly

Extra Credit Features

1. Performance Indexes (schema_mysql.sql)

-- Index for viewer aggregation queries
CREATE INDEX idx_episode_ws_viewers ON GRN_EPISODE (WS_ID, TOTAL_VIEWERS);

-- Index for rating queries
CREATE INDEX idx_feedback_ws_rating ON GRN_FEEDBACK (WS_ID, RATING);

-- Index for series search
CREATE INDEX idx_ws_name ON GRN_WEB_SERIES (WS_NAME);

-- Additional indexes for common query patterns...

Performance Analysis: See EXPLAIN output in project report.

2. Analytics Dashboard (routes_analytics.py)

Route: /analytics/dashboard
Features:
- Top 10 series by viewers (bar chart)
- Top 10 series by rating (bar chart)
- Series distribution by country (pie chart)
- Series distribution by genre (doughnut chart)
- Rating distribution (bar chart)
- Production house performance (bar chart)

3. Query Caching (db.py)

def execute_cached_query(query, params=None, cache_key=None, ttl=300):
    """In-memory cache for read-heavy analytics queries"""
    # Check cache first
    if cache_key in _query_cache and not expired:
        return _query_cache[cache_key]
    # Execute and cache
    result = execute_query(query, params)
    _query_cache[cache_key] = result
    return result

4. Dark Theme UI (style.css)

Cinematic dark theme with proper contrast
All text colors optimized for visibility on dark backgrounds
Comprehensive Bootstrap class overrides
Custom CSS variables for consistent theming

5. Netflix/HBO Style Typography

Professional streaming platform fonts:

Element	Font	Style
Logo & Headings	Bebas Neue	Uppercase, letter-spacing, Netflix trailer style
Body Text	Montserrat	Clean, professional like HBO Max

StreamVault Logo Features:

Netflix-inspired design with emphasized "S" and "V" letters
Red accent color with glow effect
Larger, vertically stretched letters for cinematic impact
Hover animation with enhanced glow

6. Series Display Enhancements

Feature	Description
Series Posters	Full image display with `object-fit: contain`
Card Layout	4 cards per row on large screens
Rating Badge	Overlay badge with star rating on images
YouTube Trailers	Embedded video players on series detail pages
Red Accent Theme	All card headings use consistent red color

7. Customer Experience

Feature	Details
Episodes View	Sequential numbering (1, 2, 3...) instead of IDs
Tech Issues Column	Hidden for customers, visible only to employees
Episode Count	Shows actual/planned (e.g., "3/62")

Demo Accounts

Role	Username	Password
Employee (Admin)	`admin`	`Password123`
Customer	`johnsmith`	`Password123`
Customer	`emilyj`	`Password123`
Customer	`davidw`	`Password123`
Customer	`sarahb`	`Password123`

Note: All demo accounts use the same password: Password123

Quick Commands Reference

# Activate virtual environment
cd ~/project_part2 && source venv/bin/activate

# Start the Flask app
python run.py

# Check MySQL status (Homebrew)
brew services list | grep mysql

# Start MySQL (Homebrew)
brew services start mysql

# Stop MySQL (Homebrew)
brew services stop mysql

# Connect to database directly
/opt/homebrew/bin/mysql -u streaming_user -pstreaming_password \
  --socket=/opt/homebrew/var/mysql/mysql.sock streaming_platform

# Reload schema (warning: drops all data)
/opt/homebrew/bin/mysql -u streaming_user -pstreaming_password \
  --socket=/opt/homebrew/var/mysql/mysql.sock \
  streaming_platform < schema_mysql.sql

# Reload seed data
/opt/homebrew/bin/mysql -u streaming_user -pstreaming_password \
  --socket=/opt/homebrew/var/mysql/mysql.sock \
  streaming_platform < seed_data.sql

Business Queries

See business_queries.sql for full implementations:

Query	Type	Description
Q1	3+ Table Join	Series with production house, countries, and average rating
Q2	Multi-row Subquery	Series above average rating
Q3	Correlated Subquery	Top series per country by viewers
Q4	SET Operator (UNION)	Series availability comparison between regions
Q5	CTE/Inline View	Performance tier classification
Q6	TOP-N/BOTTOM-N	Top 5 by viewers, Bottom 5 by rating

Troubleshooting

Port 5000 in Use (macOS)

On macOS, AirPlay Receiver uses port 5000. The app is configured to use port 5001 instead.

MySQL Connection Failed

Check if MySQL is running: brew services list
Verify socket exists: ls -la /opt/homebrew/var/mysql/mysql.sock
Test connection: /opt/homebrew/bin/mysql -u root --socket=/opt/homebrew/var/mysql/mysql.sock

Login Not Working

Ensure the seed data was loaded correctly. The password hash in the database must match Password123.

Dark Text Not Visible

Hard refresh your browser (Cmd+Shift+R on Mac) to clear CSS cache.

License

This project is submitted as coursework for CS-GY 6083 at NYU Tandon.

Acknowledgments

Course: CS-GY 6083 - Principles of Database Systems
Instructor: NYU Tandon School of Engineering
Semester: Fall 2025

Name		Name	Last commit message	Last commit date
Latest commit History 33 Commits
app		app
demo_images		demo_images
.gitignore		.gitignore
README.md		README.md
business_queries.sql		business_queries.sql
report_outline.md		report_outline.md
requirements.txt		requirements.txt
run.py		run.py
schema_mysql.sql		schema_mysql.sql
seed_data.sql		seed_data.sql

igopalakrishna/StreamVault

Folders and files

Latest commit

History

Repository files navigation

StreamVault - Web Series Streaming Platform

Team Members

Table of Contents

Project Overview

Key Features

Screenshots

Homepage & Authentication

Homepage

Login Page

Sign Up Pages

Customer Experience

Browse Screen

Series Detail Pages

Admin Dashboard

Admin Dashboard Views

Analytics Dashboard

Analytics Visualizations

Tech Stack

Project Structure

Setup Instructions

Prerequisites

Option A: Setup on macOS with Homebrew (Recommended)

1. Install MySQL via Homebrew

2. Configure MySQL for Port 3307 (to avoid conflicts)

3. Start MySQL Service

4. Create Database and User

5. Load Schema and Seed Data

6. Install Python Dependencies

7. Run the Application

Option B: Setup on Standard MySQL (Port 3306)

1. Create MySQL Database and User

2. Run Schema and Seed Data

3. Update Configuration

4. Install Dependencies and Run

Environment Variables (Optional)

Current Configuration

Features & Requirements Mapping

Authentication (Requirement 4.1)

Customer Features (Requirement 4.2)

Employee Features (Requirement 4.3)

Security Implementation

SQL Injection Prevention (Requirement 5.1)

XSS Protection (Requirement 5.2)

Password Security (Requirement 5.3)

Role-Based Access Control (Requirement 5.4)

Transactions (Requirement 5.5)

Part II Additional Features

1. Forgot Password Flow

2. Deadlock Protection

Extra Credit Features

1. Performance Indexes (schema_mysql.sql)

2. Analytics Dashboard (routes_analytics.py)

3. Query Caching (db.py)

4. Dark Theme UI (style.css)

5. Netflix/HBO Style Typography

6. Series Display Enhancements

7. Customer Experience

Demo Accounts

Quick Commands Reference

Business Queries

Troubleshooting

Port 5000 in Use (macOS)

MySQL Connection Failed

Login Not Working

Dark Text Not Visible

License

Acknowledgments

About

Resources

Uh oh!

Stars

Watchers

Forks

Releases

Packages 0

Packages