Angelica Job Application Tracker

A teaching-focused Flask + MySQL web app for tracking job applications, follow-ups, resume versions, and interviews. It uses direct SQL (no ORM, no SQLAlchemy), environment-based configuration, session login, and Docker Compose for local deployment.

---

1. What this project is

You can register, log in, and manage your own job pipeline: companies, statuses, interviews, and notes. Every query is scoped to your user id so you only ever see your data.

---

2. What Flask is

Flask is a lightweight Python web framework. It maps URLs to Python functions (“views”), renders Jinja HTML templates, reads form data, sets flash messages, and signs session cookies so the server remembers who is logged in between requests.

---

3. What MySQL is

MySQL is a relational database server. Data lives in tables with rows and columns. This project uses foreign keys so that follow-ups, resumes, and interviews stay tied to one application (and applications to one user).

---

4. What “direct database connection” means

Instead of an ORM (Object-Relational Mapper) building SQL for you, this project:

1. Opens a connection with mysql-connector-python.
2. Creates a cursor to run SQL strings.
3. Uses %s placeholders and a tuple of values so user input is never concatenated into raw SQL.
4. Commits after writes (INSERT / UPDATE / DELETE).
5. Closes the cursor and connection to free server resources.

See db.py for reusable helpers (fetch_one, fetch_all, execute_write) and comments on why each step exists.

---

5. Why .env is used

Secrets and per-machine settings (database password, SECRET_KEY) should not be hardcoded in Python files:

• Source code often ends up in git; passwords in code are a common leak.
• .env holds local values; .gitignore excludes .env** so it is not committed.
• python-dotenv loads those variables into os.environ when the app starts.
• .env.example documents which variables are required without containing real secrets.

Docker Compose can inject the same variable names with different values (for example DB_HOST=mysql_db inside the network).

---

6. How authentication works

1. Register: email + password → password is hashed with werkzeug.security.generate_password_hash → only the hash is stored in MySQL.
2. Login: load user by email → check_password_hash compares your typed password to the stored hash → on success, session['user_id'] (and email) is set.
3. Protected routes: the @login_required decorator in auth.py checks for user_id in the session; if missing, you are redirected to login.
4. Logout: session.clear() removes the server-side session data associated with your cookie.

Flask signs the session cookie with SECRET_KEY. If someone changes the cookie bytes, the signature fails — that is why the secret must stay private and random in production.

---

7. Project layout

angelica_job_tracker/
├── app.py                 # Flask app factory, blueprint registration, entrypoint
├── db.py                  # MySQL connection + safe query helpers
├── auth.py                # Register, login, logout, login_required
├── dashboard.py           # Dashboard stats (SQL aggregates)
├── applications.py        # Applications CRUD + follow-ups, resumes, interviews
├── init_db.sql            # Schema (run once / used by Docker on first MySQL start)
├── requirements.txt
├── Dockerfile
├── docker-compose.yml
├── .env.example
├── .gitignore
├── README.md
├── templates/             # Jinja HTML (Bootstrap 5)
└── static/                # css/, js/

---

8. Install Python dependencies (local)

From the angelica_job_tracker folder:

python -m venv .venv

Windows (PowerShell):

.\.venv\Scripts\Activate.ps1
pip install -r requirements.txt

macOS / Linux:

source .venv/bin/activate
pip install -r requirements.txt

---

9. Create the database and import the schema

1. Start MySQL on your machine (localhost).
2. Create the database and tables using the provided script.

Option A — MySQL client from a terminal:

mysql -u root -p < init_db.sql

Option B — MySQL Workbench: open init_db.sql, run it against your server.

The script contains CREATE DATABASE IF NOT EXISTS angelica; and USE angelica;, then creates users, applications, follow_ups, resume_versions, and interviews.

---

10. Configure environment variables (local host)

copy .env.example .env

(On macOS/Linux: cp .env.example .env.)

Edit .env:

• DB_HOST=localhost when Flask runs on your computer and MySQL is on the same machine.
• DB_PORT, DB_USER, DB_PASSWORD, DB_NAME must match your MySQL login and database name.
• SECRET_KEY: set a long random string (used to sign sessions).
• FLASK_ENV=development: enables Flask debug features while learning; use production for real deployment.

Never commit .env.

---

11. Run locally

With .venv activated and MySQL running:

python app.py

Open http://127.0.0.1:5000 — you should be redirected to login, then the dashboard after signing in.

---

12. Run with Docker Compose

Important: localhost inside a container

• On your laptop, localhost means your laptop.
• Inside the Flask container, localhost means that container, not the MySQL container.

So in Docker Compose, DB_HOST must be the MySQL service name from docker-compose.yml — here it is mysql_db.

The Compose file sets that for the web service. You can still override values via a .env file next to docker-compose.yml (for example DB_PASSWORD, SECRET_KEY).

Start everything

From angelica_job_tracker:

docker compose up --build

• Flask: http://localhost:5000
• MySQL: port 3306 is published to the host (optional use for GUI tools).

Volumes and ports (short)

• mysql_data volume: persists database files so data survives container restarts.
• 5000:5000: maps host port 5000 to the app container’s port 5000.
• 3306:3306: exposes MySQL to the host (handy for debugging; tighten in production).

On first MySQL startup, the image runs SQL files in /docker-entrypoint-initdb.d. This project mounts init_db.sql there so tables are created automatically.

Docker-specific .env (optional)

You might keep two mental “profiles”:

Setting	Flask on host + MySQL on host	Docker Compose
DB_HOST	localhost	mysql_db (service name)
DB_PASSWORD	your local root password	must match MYSQL_ROOT_PASSWORD in Compose

Compose uses ${DB_PASSWORD:-rootroot} so, if you set DB_PASSWORD in .env, both MySQL and the web container share it.

---

13. Common errors and fixes

Symptom	Likely cause	What to do
RuntimeError: SECRET_KEY is missing	No .env or empty key	Copy .env.example to .env and set SECRET_KEY.
Can't connect to MySQL server	Wrong DB_HOST / port / firewall	Local: DB_HOST=localhost. Docker: DB_HOST=mysql_db.
Access denied for user	Wrong DB_USER / DB_PASSWORD	Match MySQL credentials; restart app after editing .env.
Unknown database 'angelica'	Schema not imported	Run init_db.sql (see section 9).
Tables missing inside Docker	Volume already initialized without script	Remove the named volume only in dev (docker compose down -v) and up again — deletes DB data.
Registration says email exists	Normal	Use another email or delete the row in users (dev only).

---

14. Future improvements

• CSRF tokens for all POST forms (e.g. Flask-WTF).
• Production WSGI server (Gunicorn/Waitress) behind Nginx or a cloud load balancer.
• Migrations for schema changes (still without ORM — e.g. raw SQL migration files).
• File uploads for real resume PDFs (stored outside the web root).
• Email reminders for follow-ups and interviews.
• Tests (pytest) hitting a disposable MySQL instance in CI.

---

Quick recap for interviews

• File structure: app.py wires the app; db.py is the only database layer; feature blueprints split auth, dashboard, and applications; templates live under templates/.
• Login: hashed passwords in MySQL; successful login sets session['user_id']; @login_required guards private routes; SECRET_KEY signs the session cookie.
• Queries: SQL with %s parameters → mitigates SQL injection; commit persists writes; closing connections avoids resource leaks.
• Run locally: venv → pip install -r requirements.txt → import init_db.sql → .env with DB_HOST=localhost → python app.py.
• Run with Docker: docker compose up --build; use service name mysql_db as DB_HOST for the web container (already set in docker-compose.yml).

Built with care for Angelica — read the comments in db.py, auth.py, and applications.py while you step through requests in the debugger.