Python webapp to track to-do items with FastHTML and SQLite

app.py

#!/usr/bin/env python3

# ----------------------------------------------------------------------
# SPDX-License-Identifier: CC0-1.0
# Authored by Fereydoun Memarzanjany
#
# To the maximum extent possible under law, the Author waives all
# copyright and related or neighboring rights to this code.
#
# You should have received a copy of the CC0 legalcode along with this
# work; if not, see <http://creativecommons.org/publicdomain/zero/1.0/>
# ----------------------------------------------------------------------


###############################################
# main.py -- To-Do Tracker with FastHTML + sqlite3
###############################################
from fasthtml.common import *
import sqlite3, datetime

DB_NAME = "database.db"

###############################################
# 1) Initialize our local SQLite DB
###############################################
def init_db():
    conn = sqlite3.connect(DB_NAME, check_same_thread=False)
    cur = conn.cursor()
    cur.execute("""CREATE TABLE IF NOT EXISTS tasks (
           id INTEGER PRIMARY KEY AUTOINCREMENT,
           title TEXT NOT NULL,
           body TEXT,
           created_at TEXT NOT NULL,
           due_date TEXT,
           is_completed INTEGER NOT NULL DEFAULT 0
    )""")
    cur.execute("""CREATE TABLE IF NOT EXISTS tags (
           id INTEGER PRIMARY KEY AUTOINCREMENT,
           name TEXT UNIQUE NOT NULL
    )""")
    cur.execute("""CREATE TABLE IF NOT EXISTS task_tags (
           task_id INTEGER NOT NULL,
           tag_id INTEGER NOT NULL,
           PRIMARY KEY (task_id, tag_id)
    )""")
    conn.commit()
    return conn

conn = init_db()

###############################################
# 2) DB Helper functions
###############################################
def add_task(title, body, due_date, tags):
    cur = conn.cursor()
    created_at = datetime.datetime.now().isoformat(timespec='seconds')
    cur.execute(
        "INSERT INTO tasks(title, body, created_at, due_date, is_completed) VALUES (?,?,?,?,0)",
        (title, body, created_at, due_date or None)
    )
    task_id = cur.lastrowid
    # Insert tag rows & linking
    if tags.strip():
        for t in [x.strip() for x in tags.split(",") if x.strip()]:
            cur.execute("INSERT OR IGNORE INTO tags(name) VALUES (?)", (t,))
            cur.execute("SELECT id FROM tags WHERE name=?", (t,))
            tag_id = cur.fetchone()[0]
            cur.execute("INSERT OR IGNORE INTO task_tags(task_id, tag_id) VALUES (?,?)", (task_id, tag_id))
    conn.commit()

def toggle_completed(task_id):
    cur = conn.cursor()
    cur.execute("SELECT is_completed FROM tasks WHERE id=?", (task_id,))
    row = cur.fetchone()
    if not row: return
    now = 1 if row[0] == 0 else 0
    cur.execute("UPDATE tasks SET is_completed=? WHERE id=?", (now, task_id))
    conn.commit()

def list_tasks(completed_only, sort_by, filter_tag):
    """
    completed_only: '0' or '1'
    sort_by: 'created' or 'due'
    filter_tag: might be '' or a tag name
    """
    conly = (completed_only == "1")
    cur = conn.cursor()
    base_cols = """tasks.id, tasks.title, tasks.body,
                   tasks.created_at, tasks.due_date, tasks.is_completed,
                   GROUP_CONCAT(tags.name, ',') AS tags"""
    if filter_tag:
        sql = f"""SELECT {base_cols}
                    FROM tasks
               JOIN task_tags ON tasks.id = task_tags.task_id
               JOIN tags      ON tags.id = task_tags.tag_id
                   WHERE tags.name=?
                """
        params = [filter_tag]
        if conly:
            sql += " AND tasks.is_completed=1"
    else:
        sql = f"""SELECT {base_cols}
                    FROM tasks
               LEFT JOIN task_tags ON tasks.id = task_tags.task_id
               LEFT JOIN tags      ON tags.id = task_tags.tag_id
                   WHERE 1=1
                """
        params = []
        if conly:
            sql += " AND tasks.is_completed=1"

    sql += " GROUP BY tasks.id "

    if sort_by == "due":
        # earliest due first, tasks w/o due_date at bottom, then newest created
        sql += " ORDER BY (due_date IS NULL), due_date ASC, created_at DESC"
    else:
        # default = 'created' => newest first
        sql += " ORDER BY created_at DESC"

    cur.execute(sql, params)
    rows = cur.fetchall()
    out = []
    for r in rows:
        out.append(dict(
            id=r[0],
            title=r[1],
            body=r[2] or "",
            created_at=r[3],
            due_date=r[4] or "",
            is_completed=bool(r[5]),
            tags=(r[6] or "")
        ))
    return out

def list_all_tags():
    cur = conn.cursor()
    cur.execute("SELECT name FROM tags ORDER BY name ASC")
    return [r[0] for r in cur.fetchall()]

###############################################
# 3) Rendering partials
###############################################
def render_tasks_partial(completed_only, sort, tag):
    tasks = list_tasks(completed_only, sort, tag)
    lis = []
    for t in tasks:
        done_btn = Button(
            "Mark undone" if t["is_completed"] else "Mark done",
            hx_post=f"/toggle/{t['id']}",
            hx_target="#tasks-list",
            hx_swap="innerHTML"
        )
        # Tag links
        tlinks = []
        if t["tags"].strip():
            for tg in t["tags"].split(","):
                link = A(tg,
                         hx_get=f"/tasks?completed_only={completed_only}&sort={sort}&tag={tg}",
                         hx_target="#tasks-list",
                         hx_swap="innerHTML",
                         style="margin-right:0.5rem;")
                tlinks.append(link)

        dd_info = f"(Due: {t['due_date']})" if t["due_date"] else ""
        lis.append(Li(
            H3(t["title"]),
            P(t["body"]),
            P(f"Created: {t['created_at']} {dd_info}"),
            Div(*tlinks),
            done_btn,
            style="border:1px solid #ccc; margin:0.5rem; padding:0.5rem;"
        ))
    return Ul(*lis, id="tasks-list")


def render_tags_top(completed_only, sort, current_tag):
    all_tags = list_all_tags()
    out = []
    # "All" link => no tag
    out.append(
        A("All",
          hx_get=f"/tasks?completed_only={completed_only}&sort={sort}&tag=",
          hx_target="#tasks-list",
          hx_swap="innerHTML",
          style="margin-right:1rem; font-weight:bold;")
    )
    for tg in all_tags:
        style_ = "font-weight:bold;" if tg == current_tag else ""
        out.append(
            A(tg,
              hx_get=f"/tasks?completed_only={completed_only}&sort={sort}&tag={tg}",
              hx_target="#tasks-list",
              hx_swap="innerHTML",
              style="margin-right:1rem;" + style_)
        )
    return Div(*out, id="tags-top")


def render_top_controls(completed_only, sort, tag):
    """A wrapper that returns the entire top bar (add form, toggle form, sort form)."""
    toggle_val = "0" if completed_only == "1" else "1"
    toggle_label = "Show ALL tasks" if completed_only == "1" else "Show ONLY Completed"
    # The 'Add Task' form
    add_form = Form(
        Fieldset(
            Label("Title:", Input(name="title", required=True)),
            Label("Body:", Textarea(name="body", rows=2)),
            Label("Due date:", Input(name="due_date", type="date")),
            Label("Tags (comma-separated):", Input(name="tags")),
        ),
        Hidden(name="completed_only", value=completed_only),
        Hidden(name="sort", value=sort),
        Hidden(name="tag", value=tag),
        Button("Add Task", type="submit"),
        hx_post="/add",
        hx_target="#tasks-list",
        hx_swap="innerHTML",
        style="margin-bottom:1rem;"
    )

    # The toggle form
    toggle_form = Form(
        Hidden(name="completed_only", value=toggle_val),
        Hidden(name="sort", value=sort),
        Hidden(name="tag", value=tag),
        Button(toggle_label, type="submit", cls="secondary"),
        hx_post="/tasks",
        hx_target="#tasks-list",
        hx_swap="innerHTML",
        style="display:inline-block; margin-right:1rem;"
    )

    # The sort form
    sort_form = Form(
        Hidden(name="completed_only", value=completed_only),
        Hidden(name="tag", value=tag),
        Label("Sort by: "),
        Select(
            Option("Created (newest first)", value="created", selected=(sort=="created")),
            Option("Due date (earliest first)", value="due", selected=(sort=="due")),
            name="sort"
        ),
        Button("Apply", type="submit"),
        hx_post="/tasks",
        hx_target="#tasks-list",
        hx_swap="innerHTML",
        style="display:inline-block;"
    )

    return Div(
        add_form,
        toggle_form,
        sort_form,
        id="top-controls"
    )

###############################################
# 4) Build the FastHTML application & routes
###############################################
app, rt = fast_app()

@rt("/")
def get_home(req):
    qp = req.query_params
    completed = qp.get("completed_only","0")
    sort = qp.get("sort","created")
    tag = qp.get("tag","")

    # We'll fetch tasks/tags on load
    tasks_div = Div(
        hx_get=f"/tasks?completed_only={completed}&sort={sort}&tag={tag}",
        hx_trigger="load",
        hx_target="#tasks-list",
        hx_swap="innerHTML"
    )
    tags_div = Div(
        hx_get=f"/taglist?completed_only={completed}&sort={sort}&tag={tag}",
        hx_trigger="load",
        id="tags-top"
    )
    # The top bar controls
    top_controls = render_top_controls(completed, sort, tag)
    return Titled(
        "To-Do Tracker",
        top_controls,
        tags_div,
        tasks_div,
        Div(id="tasks-list")
    )

@rt("/tasks")
def get_tasks(completed_only:str="0", sort:str="created", tag:str="", req=None):
    tasks_part = render_tasks_partial(completed_only, sort, tag)
    tags_oob   = Div(render_tags_top(completed_only, sort, tag),
                     id="tags-top", hx_swap_oob="true")
    # Also re-render the top controls out of band, so the toggle button label updates
    top_controls_oob = Div(render_top_controls(completed_only, sort, tag),
                           id="top-controls", hx_swap_oob="true")
    return Div(tasks_part, tags_oob, top_controls_oob)

@rt("/tasks")
def post_tasks(completed_only:str="0", sort:str="created", tag:str="", req=None):
    tasks_part = render_tasks_partial(completed_only, sort, tag)
    tags_oob   = Div(render_tags_top(completed_only, sort, tag),
                     id="tags-top", hx_swap_oob="true")
    top_controls_oob = Div(render_top_controls(completed_only, sort, tag),
                           id="top-controls", hx_swap_oob="true")
    return Div(tasks_part, tags_oob, top_controls_oob)

@rt("/taglist")
def get_taglist(completed_only:str="0", sort:str="created", tag:str="", req=None):
    return render_tags_top(completed_only, sort, tag)

@rt("/add")
def post_add(title:str, body:str="", due_date:str=None, tags:str="",
             completed_only:str="0", sort:str="created", tag:str="", req=None):
    add_task(title, body, due_date, tags)
    tasks_part = render_tasks_partial(completed_only, sort, tag)
    tags_oob   = Div(render_tags_top(completed_only, sort, tag),
                     id="tags-top", hx_swap_oob="true")
    top_controls_oob = Div(render_top_controls(completed_only, sort, tag),
                           id="top-controls", hx_swap_oob="true")
    return Div(tasks_part, tags_oob, top_controls_oob)

@rt("/toggle/{task_id}")
def post_toggle(task_id:int, completed_only:str="0", sort:str="created", tag:str="", req=None):
    toggle_completed(task_id)
    tasks_part = render_tasks_partial(completed_only, sort, tag)
    tags_oob   = Div(render_tags_top(completed_only, sort, tag),
                     id="tags-top", hx_swap_oob="true")
    top_controls_oob = Div(render_top_controls(completed_only, sort, tag),
                           id="top-controls", hx_swap_oob="true")
    return Div(tasks_part, tags_oob, top_controls_oob)

serve()

# Makefile

.PHONY: setup run

setup:
	python3 -m venv venv
	venv/bin/pip install --upgrade pip
	venv/bin/pip install python-fasthtml

run:
	venv/bin/python app.py