Capstone — Full-Stack Data Web App

Build a small but real full-stack web app that uses every major Level-4 skill:

• Data ingest: read a CSV, API or scrape on startup (or refresh) — into SQLite.
• Storage: at least two related tables.
• Auth: sign-up / login / logout with bcrypt hashing.
• UI: ≥ 3 pages in Jinja templates + 1 chart (PNG or embedded).
• Deploy: live URL accessible from a phone.

Topics that work well at this scale:

• Habit tracker — user logs daily habits; charts show streaks and rates.
• Reading list — books pulled from OpenLibrary; per-user reading progress.
• Spending log — user adds transactions; categorised bar chart.
• Class scoreboard — teacher imports a CSV; students view their progress.
• Weather dashboard — pulls live weather; users save favourite cities.

File tree

my_app/
├─ app.py              # Flask routes
├─ db.py               # SQLite CRUD
├─ auth.py             # signup/login/logout helpers
├─ data_load.py        # one-off CSV/API/scrape → DB
├─ requirements.txt
├─ templates/
│   ├─ base.html
│   ├─ index.html
│   ├─ login.html / signup.html
│   ├─ dashboard.html
│   └─ chart.html      (or embed in dashboard)
├─ static/
│   ├─ style.css
│   └─ chart.png       (generated by data_load.py)
└─ README.md

Routes (minimum)

GET  /                          home + chart preview
GET  /signup,    POST /signup
GET  /login,     POST /login
POST /logout
GET  /dashboard  (login_required)   user's data
POST /add        (login_required)   add a record
GET  /chart.png                    serve the chart
GET  /api/items  (JSON, public or auth'd)

Schema (minimum)

users(id, email, password, created_at)
items(id, user_id, ... topic-specific fields ..., created_at)
                  └── foreign key → users(id)

Build order — 60 minutes

0–5      sketch routes + schema
5–15     db.py — init + CRUD
15–25    auth — signup/login/logout
25–40    app.py — routes + templates
40–50    chart — matplotlib → static/chart.png
50–55    deploy — push, deploy, smoke test
55–60    README — how to run, screenshots

# db.py
import sqlite3
from pathlib import Path

DB = Path("habits.db")

def get_conn():
    con = sqlite3.connect(DB); con.row_factory = sqlite3.Row
    con.execute("PRAGMA foreign_keys = ON")
    return con

def init():
    with get_conn() as con:
        con.executescript("""
            CREATE TABLE IF NOT EXISTS users (
              id INTEGER PRIMARY KEY AUTOINCREMENT,
              email TEXT UNIQUE NOT NULL,
              password TEXT NOT NULL,
              created_at TEXT DEFAULT CURRENT_TIMESTAMP
            );
            CREATE TABLE IF NOT EXISTS ticks (
              id INTEGER PRIMARY KEY AUTOINCREMENT,
              user_id INTEGER NOT NULL REFERENCES users(id),
              habit TEXT NOT NULL,
              date TEXT NOT NULL
            );
        """)

def add_tick(uid, habit, date):
    with get_conn() as con:
        con.execute(
            "INSERT INTO ticks (user_id, habit, date) VALUES (?,?,?)",
            (uid, habit, date),
        )

def list_ticks(uid):
    with get_conn() as con:
        return [dict(r) for r in con.execute(
            "SELECT * FROM ticks WHERE user_id=? ORDER BY date DESC",
            (uid,),
        )]

# chart.py — generate static/chart.png for the dashboard
import matplotlib.pyplot as plt
import pandas as pd
import db

def render(uid):
    rows = db.list_ticks(uid)
    if not rows:
        return
    df = pd.DataFrame(rows)
    by_day = df.groupby(["date", "habit"]).size().unstack(fill_value=0)
    fig, ax = plt.subplots(figsize=(8, 4))
    by_day.plot(ax=ax)
    ax.set_title("Habit ticks per day")
    fig.tight_layout()
    fig.savefig("static/chart.png", dpi=150)

# app.py — wired together (excerpt)
from flask import Flask, render_template, request, redirect, url_for, flash, session
import db, auth, chart

app = Flask(__name__)
app.secret_key = os.environ["FLASK_SECRET"]
db.init()

@app.route("/dashboard")
@auth.login_required
def dashboard():
    rows = db.list_ticks(session["user_id"])
    chart.render(session["user_id"])
    return render_template("dashboard.html", rows=rows)

@app.route("/add", methods=["POST"])
@auth.login_required
def add():
    db.add_tick(session["user_id"],
                request.form["habit"].strip(),
                request.form["date"])
    return redirect(url_for("dashboard"))

Now you build. The 60-minute schedule above is intentionally tight — that's how you ship small things. Don't add features beyond the brief. Polish what you have.

• Use the Lesson 41/42/43 code as a starting template.
• Pre-write the SQL in the data module.
• Build one route at a time; smoke test it; move on.
• Deploy as soon as auth + one route work — incremental, not big-bang.

You shipped a complete data web app. Auth, schema, routes, templates, a chart, a public URL. From here you can extend with everything Levels 5+ teach. This project goes on your portfolio.