DataPath SQL

DataPath is a Flask and SQLite app for tracking data-related job postings, application progress, and the technical skills required for target roles.

This repository is an extension of my final project for Harvard's CS50 SQL course on edX. The course project focused on designing a relational database for job and skill tracking. This version adds a small Flask interface, more sample data, setup instructions, integrity checks, and documentation.

The original CS50 course archive and exercise solutions are not included here. This repository is focused only on this project.

App Preview

Dashboard	Jobs

Role Fit

Purpose

When applying for data roles, I wanted one place to keep job postings, application status, and skill gaps. DataPath brings those pieces together.

The app is built around questions such as:

• Which data roles best match my current skill set?
• Which high-priority skills am I still missing for a target role?
• Which saved applications need attention before their deadlines?
• How many jobs are currently saved, applied to, in interview, rejected, or archived?
• How does my skill progress change when I mark a skill as acquired?

Technical Scope

• Relational schema design with primary keys, foreign keys, constraints, and many-to-many relationships
• SQLite views for application pipeline summaries, role-skill progress, and weighted role matching
• Seed data that can rebuild the same demo state from scratch
• Query examples for job-search decisions and skill gap analysis
• A Flask interface for adding jobs, editing application status, and updating skills
• Integrity checks for foreign keys, duplicate role-skill pairs, invalid statuses, and invalid skill flags

Features

• Dashboard with application pipeline metrics and upcoming saved postings
• Job posting CRUD workflow
• Status tracking for saved, applied, interview, offer, rejected, and archived
• Skill inventory with acquired/learning toggles
• Role-fit page with weighted match scores and missing high-priority skills
• Reproducible SQLite database generated from SQL files

Data Model

Core entities:

• companies: employers and industries
• roles: target data role families such as Data Analyst, Data Engineer, or Value Engineer
• skills: technical skills and whether they have been acquired
• required_skills: many-to-many mapping between roles and skills, with priority
• job_postings: individual job opportunities and their application status

The original conceptual ER diagram is available at docs/ER_final_project.png. A schema-focused overview is available at docs/schema.md.

Reproducibility

The generated SQLite database is intentionally not committed. It lives in instance/datapath.db, which is ignored by Git. The same demo database can be rebuilt from the tracked SQL files.

Tracked source of truth:

• schema.sql: tables, constraints, indexes, triggers, and views
• seed.sql: demo companies, roles, skills, required skills, and job postings
• queries.sql: example analysis and update queries
• tests/integrity_checks.sql: database validation checks

Expected seeded row counts:

Table	Rows
companies	9
roles	5
skills	17
job_postings	7
required_skills	40

Quick Start

Install dependencies with uv:

uv sync

Initialize the SQLite database with demo data:

uv run python -m flask --app app init-db

Run the web app:

uv run python -m flask --app app run

Open the local URL printed by Flask, usually http://127.0.0.1:5000.

Run SQL Directly

You can also create and inspect the database without Flask:

sqlite3 datapath.db < schema.sql
sqlite3 datapath.db < seed.sql
sqlite3 datapath.db < tests/integrity_checks.sql

Run the example project queries:

sqlite3 datapath.db < queries.sql

Resetting the App Database

The Flask app can reset the local database at any time:

uv run python -m flask --app app init-db

To create an empty database with the schema only:

uv run python -m flask --app app init-db --empty

Project Structure

datapath-sql/
├── app.py
├── schema.sql
├── seed.sql
├── queries.sql
├── pyproject.toml
├── uv.lock
├── docs/
│   ├── ER_final_project.png
│   ├── sample-output.md
│   ├── schema.md
│   └── screenshots/
├── static/
│   └── styles.css
├── templates/
└── tests/
    └── integrity_checks.sql

Notes

The database design remains the core of the project. Flask is included so the data can also be viewed and edited through a simple browser interface.