VSAT – Vulnerability & Security Assessment Tracker

A lightweight academic vulnerability management and tracking system built with Flask and MySQL. It centralizes assessed assets and security findings, and supports a controlled vulnerability lifecycle, Analyst assignment, activity tracking, a dashboard, reports, and CSV export.

VSAT is a management tool. It does not scan systems, perform penetration testing, or exploit anything. It tracks findings produced by authorized assessments, or synthetic demo findings.

Built as a 5th semester B.E. Computer Science & Engineering mini project using Python and database concepts.

Live demo: https://vsat-qptp.onrender.com (Hosted on Render Free, so the first load after a period of inactivity can be slow.)

---

Table of Contents

1. ✨ Features
2. 🛠️ Tech Stack
3. 🗄️ Database Design
4. 🔒 Security Design
5. 🚀 Getting Started
6. 📖 Usage
7. ☁️ Deployment
8. 📁 Project Structure
9. 🎯 Scope and Limitations
10. 🗺️ Roadmap
11. ⚠️ Disclaimer
12. 👥 Authors

---

✨ Features

• Authentication and authorization
  • Email and password login; passwords stored as hashes (never plain text)
  • Two system roles: Admin and Analyst, enforced on the server
  • Inactive accounts cannot log in; users are deactivated, not deleted
  • Logout is a CSRF-protected POST request
  • Generic "invalid login" message and safe redirect handling after login
  • No public registration: the Admin creates Analyst accounts
• Asset management
  • Create, view, edit, search, and filter assets by criticality
  • Asset types: Web Application, API, Server, Database, Network Device, Workstation, Cloud Service, Other
  • Unique asset names; optional fields (identifier, owner, description) saved as NULL when empty
• Vulnerability management
  • Create, view, edit, search, and filter (severity, status) vulnerabilities linked to an asset
  • Severity: Low, Medium, High, Critical
  • Categories: Injection, Cross-Site Scripting (XSS), Broken Access Control, Authentication and Session, Cryptographic Failure, Security Misconfiguration, Vulnerable or Outdated Component, Information Disclosure, Other
  • Optional CVE ID (validated against the CVE format), due date, discoverer, and assignee
  • Admin-only soft delete: the record is hidden, not erased
• Status lifecycle
  • Open → In Progress → Resolved → Verified → Closed
  • Allowed moves are defined in one place (can_transition()) and checked on the server, so invalid jumps are rejected even if the browser is tampered with
  • The status dropdown shows the full workflow and disables invalid moves
  • Reopening a Closed vulnerability (Closed → Open) is Admin-only
• Assignment and comments
  • Assign a vulnerability to an active Analyst, or leave it unassigned, with an optional note
  • Add comments to a vulnerability
• Activity history
  • Key events are recorded and kept in order: created, status changed, assigned, commented, deleted
  • Each entry stores the user, the old and new status or assignee, any comment, and a timestamp
  • Ordinary field edits (for example changing a title) are not logged individually
• Dashboard
  • Total assets, active findings, open, in-progress, resolved/verified/closed, and critical counts
  • Severity and status breakdown charts (Chart.js), plus recent vulnerabilities and recent activity
• Reports
  • Filter by asset, severity, status, and category
  • Summary metrics, findings table, and a printable report view
  • CSV export that keeps the selected filters
  • Soft-deleted vulnerabilities are excluded from lists, the dashboard, reports, and CSV
• User management (Admin only)
  • View users, create Analyst accounts, activate or deactivate accounts
  • Shows active Admin and Analyst counts and the number of findings assigned to each user
  • Each Analyst can have a security specialty (for example Application Security, Web Security, Vulnerability Management). A specialty is a label only; it does not change permissions.
• Role-based Settings
  • All signed-in users can view their own account details and change their password after confirming the current password
  • Admins see a shortcut to existing user management, account totals, active finding count, and a non-sensitive database connectivity check
  • Analyst users do not see Admin-only controls
  • Notification preferences, theme switching, global audit logs, and configurable security policies are not included
• Custom error pages for 400, 403, 404, and 500 errors (the 500 page shows no internal details)
• Clean UI with Bootstrap 5, shared CSS/JS, and reusable Jinja2 macros

---

🛠️ Tech Stack

Layer	Technology
Language	Python 3.x (developed on 3.14)
Web framework	Flask
Database	MySQL 8.0+ (Aiven MySQL in production)
ORM	Flask-SQLAlchemy / SQLAlchemy with the PyMySQL driver
Authentication	Flask-Login, Werkzeug password hashing
Forms and CSRF	Flask-WTF / WTForms
Config / secrets	python-dotenv (.env file)
Frontend	Jinja2 templates, Bootstrap 5, vanilla JavaScript, Chart.js
Production server	Gunicorn on Render
Version control	Git and GitHub

---

🗄️ Database Design

Four main tables:

Table	Purpose
users	Accounts with a role (Admin / Analyst), an optional specialty, and an active flag. Users are deactivated, not deleted.
assets	Systems or applications being tracked.
vulnerabilities	Security issues linked to assets (soft delete using is_deleted).
vuln_activity	Activity history of key actions on a vulnerability.

Key design decisions:

• CHECK constraints limit severity, status, category, and action to allowed values.
• Foreign keys use ON DELETE RESTRICT, so MySQL refuses to delete a row that history depends on.
• Soft delete for vulnerabilities, so activity history always stays valid.
• All timestamps are stored in UTC.

The SQL script for adding the users.specialty column to an existing database is in migrations/001_add_user_specialty.sql. A new database created with init-db does not need it. For an existing database, apply migrations/002_add_remediation_details.sql before deploying the remediation feature, or run flask --app run.py migrate-remediation-details after the updated code is available. This adds nullable remediation and verification fields plus the resolver reference without deleting existing records.

---

🔒 Security Design

This project is about security, so the app itself follows good practices:

• CSRF protection on all forms (including logout)
• Password hashing (Werkzeug); passwords are never stored in source code
• Role-based access control, checked on the server for every protected action
• Protected routes: login is required
• Server-side validation of status transitions
• A limited MySQL user (vsat_user) with privileges only on vsat_db
• Secrets kept in .env, which is ignored by Git (only .env.example is committed)
• SQL queries go through the ORM (protects against SQL injection)
• Custom error pages that do not expose internal details

---

🚀 Getting Started

Prerequisites

• Python 3.x (developed on 3.14)
• MySQL Server 8.0 or newer
• Git

1. Clone the repository

git clone https://github.com/SamRepository25/Vulnerability-Security-Assessment-Tracker.git
cd Vulnerability-Security-Assessment-Tracker

2. Create and activate a virtual environment

Windows (PowerShell):

python -m venv .venv
.venv\Scripts\Activate.ps1

Linux / macOS:

python3 -m venv .venv
source .venv/bin/activate

3. Install dependencies

pip install -r requirements.txt

4. Create the MySQL database and user

Log in to MySQL as root and run:

CREATE DATABASE vsat_db CHARACTER SET utf8mb4;
CREATE USER 'vsat_user'@'localhost' IDENTIFIED BY 'choose_a_strong_password';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX, REFERENCES ON vsat_db.* TO 'vsat_user'@'localhost';
FLUSH PRIVILEGES;

The user only has rights on vsat_db. The CREATE, ALTER, INDEX, and REFERENCES privileges are needed to create the tables.

5. Configure environment variables

Copy .env.example to .env and fill in your values:

SECRET_KEY=put_a_long_random_string_here
DB_HOST=localhost
DB_PORT=3306
DB_NAME=vsat_db
DB_USER=vsat_user
DB_PASSWORD=choose_a_strong_password

To generate a good secret key:

python -c "import secrets; print(secrets.token_hex(32))"

Never commit your .env file or share it. It contains secrets.

6. Check the database connection

flask --app run check-db

Expected: a message saying the database connection was successful.

7. Create the tables

flask --app run init-db

8. Create the first Admin account

flask --app run create-admin

Follow the prompts to enter the admin details.

9. Run the app

flask --app run run --debug

Open http://127.0.0.1:5000 in your browser and log in.

--debug is for development only. Do not use it in production.

---

📖 Usage

1. Log in as Admin.
2. Open Users and create one or more Analyst accounts (name, email, password, and a security specialty).
3. Add one or more assets.
4. Add vulnerabilities against those assets (use synthetic or authorized data only).
5. Assign a vulnerability to an Analyst.
6. Move it through the status lifecycle as it is investigated and fixed.
7. Add comments and review the activity history on the vulnerability page.
8. Check the Dashboard, then open Reports to filter, print, or export findings as CSV.

Action	Analyst	Admin
Log in, view dashboard and reports	Yes	Yes
Create / edit assets	Yes	Yes
Create / edit vulnerabilities	Yes	Yes
Assign and comment	Yes	Yes
Reopen a Closed vulnerability	No	Yes
Delete (soft-delete) a vulnerability	No	Yes
Manage users (create Analysts, activate / deactivate)	No	Yes
Change status on assigned vulnerabilities	Yes	Yes

---

☁️ Deployment

The live demo runs on this architecture:

Render  →  Gunicorn  →  Flask application  →  Aiven MySQL

• Hosting: Render (Free plan)
• Database: Aiven MySQL
• Secrets (SECRET_KEY, database settings) are set as environment variables on Render, not stored in the code.
• The Render Free plan has no shell access, so database changes for the live site are applied through Aiven or an external MySQL client, using scripts such as the ones in migrations/.

---

📁 Project Structure

Vulnerability-Security-Assessment-Tracker/
├── app/
│   ├── __init__.py          # App factory
│   ├── extensions.py        # Flask extensions (database, login, CSRF)
│   ├── models.py            # User, Asset, Vulnerability, VulnActivity
│   ├── constants.py         # Allowed values, workflow transitions, actions
│   ├── forms.py             # Flask-WTF forms
│   ├── auth.py              # Login / logout
│   ├── assets.py            # Asset routes
│   ├── vulnerabilities.py   # Vulnerability routes
│   ├── reports.py           # Reports, printable view, CSV export
│   ├── users.py             # Admin user management
│   ├── routes.py            # Dashboard and general routes
│   ├── cli.py               # check-db, init-db, create-admin
│   ├── templates/           # Jinja2 templates
│   └── static/              # CSS and JavaScript
├── migrations/              # SQL scripts for database changes
├── config.py                # Reads settings from .env
├── run.py                   # Entry point
├── requirements.txt
├── .env.example
└── README.md

---

🎯 Scope and Limitations

VSAT is intentionally small and designed to demonstrate core vulnerability management concepts. It is not an enterprise platform and is not a replacement for mature tools such as DefectDojo.

Not implemented (possible future work):

• Automated vulnerability scanning or penetration testing
• CVSS scoring and automated CVE enrichment
• Scanner result import
• Email notifications
• REST API
• Ticketing integrations
• Advanced trend analytics
• PDF report generation (reports can be printed or exported as CSV)
• A separate assessment entity or a dedicated remediation record
• Full field-level audit logging of every edit

---

🗺️ Roadmap

• Flask foundation
• MySQL connection
• Database models
• Authentication, roles, and CSRF protection
• Asset management
• Vulnerability management with status lifecycle
• Assignment, comments, and activity history
• Admin-only vulnerability soft delete
• Dashboard with Chart.js
• Reports, printable view, and CSV export
• User management and Analyst specialties
• Custom error pages
• Deployment on Render with Aiven MySQL

---

⚠️ Disclaimer

This project is for educational purposes. Use only synthetic or authorized data. VSAT does not scan, attack, or exploit any system.

---

👥 Author

• B SIMAK AHMED

Course: B.E. Computer Science and Engineering 5th Semester Mini Project

---

Testing

The automated tests use a throw-away in-memory SQLite database. They never connect to MySQL, Aiven or Render.

pip install pytest pytest-cov
pytest                                   # all tests
pytest tests/test_comprehensive.py -v    # the main integration/regression suite
pytest --cov=app --cov-report=term-missing

Limits: SQLite is not MySQL, so the raw migrations/*.sql scripts and upgrading a legacy MySQL table are not run by the tests; verify them on a disposable MySQL schema. The Flask test client does not execute JavaScript, so charts and the confirm/collapse widgets need a manual browser check.

Session cookies

Session cookies are HttpOnly, SameSite=Lax and Secure by default (the live site is HTTPS). For local development over plain http://, set SESSION_COOKIE_SECURE=0 in .env, otherwise the browser will not send the cookie and login will appear to do nothing.