"""Kanban: Projekte (je ein Board) → Aufgaben mit Status, Bearbeiter und Beschreibung. Projekte sind privat: Sehen und bearbeiten dürfen sie der Ersteller und die Mitglieder (Freunde, mit denen er das Projekt umsetzt); Administratoren sehen alles. Mitglieder verwalten und das Projekt löschen dürfen der Ersteller und Administratoren, Aufgaben löschen deren Ersteller und Administratoren. Projekte lassen sich als abgeschlossen markieren und mit Artikeln aus "P42 Projekte" verknüpfen. `position` (Kommazahl) sortiert Projekte und Aufgaben innerhalb einer Spalte. Erledigte Aufgaben wandern nach 2 Tagen ins Archiv und werden nach 14 Tagen gelöscht (gezählt ab "erledigt"). """ import asyncio import logging import math from datetime import timedelta from fastapi import APIRouter, Body, Depends, HTTPException from .auth import current_user from .common import ValidationError, clean_text, iso_after, iso_ago, now_iso, user_list from .db import transaction from .freunde import check_shareable, shareable router = APIRouter() STATUSES = ("offen", "arbeit", "wartet", "erledigt") MAX_NAME = 80 MAX_TITLE = 120 MAX_DESCRIPTION = 5000 MAX_TASKS = 1000 # pro Projekt MAX_PROJECTS = 300 MAX_LINKS = 20 MAX_CHECKLIST = 100 MAX_CHECK_TEXT = 200 MAX_REQUIRES = 30 # Eine Voraussetzung gibt ihre abhängigen Aufgaben frei, sobald sie so weit ist UNLOCKING = ("wartet", "erledigt") ARCHIVE_AFTER = timedelta(days=2) DELETE_AFTER = timedelta(days=14) CLEANUP_SECONDS = 3600 log = logging.getLogger(__name__) def cleanup(conn) -> int: """Seit 14 Tagen erledigte Aufgaben löschen.""" return conn.execute( "DELETE FROM kanban_tasks WHERE status = 'erledigt' AND done_at <= ?", (iso_ago(DELETE_AFTER),) ).rowcount async def cleanup_loop() -> None: """Stündlich aufräumen, auch wenn niemand das Kanban öffnet.""" while True: try: with transaction() as conn: cleanup(conn) except Exception: # nie die Schleife beenden log.exception("Kanban-Aufräumen fehlgeschlagen") await asyncio.sleep(CLEANUP_SECONDS) def _person(user_id, name) -> dict | None: return {"id": user_id, "name": name} if user_id is not None and name else None # Sichtbar: Ersteller, Mitglieder, Administratoren _VISIBLE = """(:admin OR k.created_by = :uid OR EXISTS (SELECT 1 FROM kanban_project_members m WHERE m.project_id = k.id AND m.user_id = :uid))""" def _who(user: dict) -> dict: return {"uid": user["id"], "admin": int(user["isAdmin"])} def _get_project(conn, project_id: int, user: dict): row = conn.execute(f"SELECT k.* FROM kanban_projects k WHERE k.id = :id AND {_VISIBLE}", {"id": project_id, **_who(user)}).fetchone() if not row: raise HTTPException(404, "Kanban-Projekt nicht gefunden – vielleicht wurde es gelöscht oder nicht mit dir geteilt.") return row def _member_ids(conn, project_id: int) -> list: return [r["user_id"] for r in conn.execute("SELECT user_id FROM kanban_project_members WHERE project_id = ?", (project_id,))] def _people(conn, row) -> list: """Wer am Projekt arbeitet (Ersteller + Mitglieder) – wählbar als Bearbeiter.""" ids = {row["created_by"], *_member_ids(conn, row["id"])} - {None} return [u for u in user_list(conn) if u["id"] in ids] def _members(conn, value, user: dict, owner_id, current=()) -> list: if not isinstance(value, list) or any(isinstance(m, bool) or not isinstance(m, int) for m in value): raise ValidationError("Ungültige Mitglieder.") ids = sorted(set(value) - {owner_id}) check_shareable(conn, user, ids, keep=current) return ids def _save_members(conn, project_id: int, ids: list) -> None: conn.execute("DELETE FROM kanban_project_members WHERE project_id = ?", (project_id,)) conn.executemany("INSERT INTO kanban_project_members (project_id, user_id) VALUES (?, ?)", [(project_id, i) for i in ids]) # Wer nicht mehr dabei ist, kann auch nicht mehr Bearbeiter sein people = {r["created_by"] for r in conn.execute("SELECT created_by FROM kanban_projects WHERE id = ?", (project_id,))} | set(ids) conn.execute( f"UPDATE kanban_tasks SET assignee = NULL WHERE project_id = ? AND assignee NOT IN ({','.join('?' * len(people))})", (project_id, *people), ) def can_edit_article(conn, user: dict, article_id: str) -> bool: """Artikel bearbeiten: Autor, wer an einem verknüpften Kanban-Projekt arbeitet, Administratoren.""" if user["isAdmin"]: return True return conn.execute( """SELECT 1 FROM projects p WHERE p.id = :a AND (p.created_by = :uid OR EXISTS ( SELECT 1 FROM kanban_project_links l JOIN kanban_projects k ON k.id = l.project_id WHERE l.article_id = p.id AND (k.created_by = :uid OR EXISTS (SELECT 1 FROM kanban_project_members m WHERE m.project_id = k.id AND m.user_id = :uid))))""", {"a": article_id, "uid": user["id"]}, ).fetchone() is not None def _may_delete(user: dict, created_by) -> bool: return user["isAdmin"] or created_by == user["id"] # ---------- Eingaben prüfen ---------- def _name(value, field="Name", max_len=MAX_NAME) -> str: return clean_text(value, field, max_len) def _description(value) -> str: if value is None: return "" if not isinstance(value, str): raise ValidationError("Ungültige Beschreibung.") text = value.replace("\r\n", "\n").strip() if len(text) > MAX_DESCRIPTION: raise ValidationError(f"Beschreibung ist zu lang (max. {MAX_DESCRIPTION} Zeichen).") return text PRIORITIES = {"hoch": 1, "mittel": 2, "niedrig": 3} PRIORITY_NAMES = {v: k for k, v in PRIORITIES.items()} def _priority(value) -> int: if value not in PRIORITIES: raise ValidationError("Unbekannte Priorität.") return PRIORITIES[value] def _status(value) -> str: if value not in STATUSES: raise ValidationError("Unbekannter Status.") return value def _assignee(conn, value, project): """Bearbeiter muss am Projekt arbeiten (Ersteller oder Mitglied).""" if value is None: return None if isinstance(value, bool) or not isinstance(value, int) or value not in {u["id"] for u in _people(conn, project)}: raise ValidationError("Bearbeiter muss an diesem Projekt mitarbeiten.") return value def _position(value) -> float: if isinstance(value, bool) or not isinstance(value, (int, float)) or not math.isfinite(value): raise ValidationError("Ungültige Position.") return float(value) def _links(conn, value, user: dict, current=()) -> list: """Verknüpfte Artikel: nur solche, die man selbst bearbeiten darf (sonst verschaffte die Verknüpfung Bearbeitungsrechte); schon verknüpfte dürfen bleiben.""" if value is None: return [] if not isinstance(value, list) or len(value) > MAX_LINKS or not all(isinstance(v, str) for v in value): raise ValidationError("Ungültige Artikel-Verknüpfung.") ids = list(dict.fromkeys(value)) found = {r["id"] for r in conn.execute( f"SELECT id FROM projects WHERE id IN ({','.join('?' * len(ids))})", ids )} if ids else set() if len(found) != len(ids): raise ValidationError("Ein verknüpfter Artikel existiert nicht mehr.") if any(a not in current and not can_edit_article(conn, user, a) for a in ids): raise ValidationError("Verknüpfen lassen sich nur Artikel, die du bearbeiten darfst.") return ids def _next(conn, table: str, where: str = "", args: tuple = ()) -> float: # table/where kommen nur aus diesem Modul, nie aus Anfragen row = conn.execute(f"SELECT MAX(position) FROM {table} {where}", args).fetchone() return (row[0] or 0) + 1 # ---------- Ausgabe ---------- def _project_links(conn) -> dict: links = {} for r in conn.execute( """SELECT l.project_id, p.id, p.title FROM kanban_project_links l JOIN projects p ON p.id = l.article_id ORDER BY p.title COLLATE NOCASE""" ): links.setdefault(r["project_id"], []).append({"id": r["id"], "title": r["title"]}) return links def _projects(conn, user: dict, project_id=None) -> list: rows = conn.execute( f"""SELECT k.*, cu.display_name AS created_by_name, uu.display_name AS updated_by_name FROM kanban_projects k LEFT JOIN users cu ON cu.id = k.created_by LEFT JOIN users uu ON uu.id = k.updated_by WHERE {_VISIBLE}""" + (" AND k.id = :id" if project_id is not None else "") + " ORDER BY k.position", {"id": project_id, **_who(user)}, ).fetchall() counts = {} for r in conn.execute("SELECT project_id, status, COUNT(*) AS n FROM kanban_tasks GROUP BY project_id, status"): counts.setdefault(r["project_id"], dict.fromkeys(STATUSES, 0))[r["status"]] = r["n"] links = _project_links(conn) return [ { "id": r["id"], "name": r["name"], "description": r["description"], "position": r["position"], "completedAt": r["completed_at"], "links": links.get(r["id"], []), "counts": counts.get(r["id"], dict.fromkeys(STATUSES, 0)), "createdAt": r["created_at"], "createdBy": _person(r["created_by"], r["created_by_name"]), "updatedAt": r["updated_at"], "updatedBy": _person(r["updated_by"], r["updated_by_name"]), "members": _member_ids(conn, r["id"]), "canManage": _may_delete(user, r["created_by"]), } for r in rows ] def _articles(conn, user: dict) -> list: """Artikel zum Verknüpfen; editable = darf man selbst verknüpfen.""" return [ {"id": a["id"], "title": a["title"], "editable": can_edit_article(conn, user, a["id"])} for a in conn.execute("SELECT id, title FROM projects ORDER BY title COLLATE NOCASE") ] def _overview(conn, user: dict) -> dict: cleanup(conn) return { "projects": _projects(conn, user), "articles": _articles(conn, user), "users": user_list(conn), "shareable": shareable(conn, user), } def _task(row) -> dict: return { "id": row["id"], "projectId": row["project_id"], "title": row["title"], "description": row["description"], "status": row["status"], "assignee": row["assignee"], "priority": PRIORITY_NAMES[row["priority"]], "position": row["position"], "doneAt": row["done_at"], "checklist": [], "requires": [], "blocked": False, "createdAt": row["created_at"], "createdBy": _person(row["created_by"], row["created_by_name"]), "updatedAt": row["updated_at"], "updatedBy": _person(row["updated_by"], row["updated_by_name"]), } def _task_extras(conn, project_id: int) -> dict: """Checklisten, Voraussetzungen und "gesperrt" für alle Aufgaben eines Projekts.""" extras = {} for r in conn.execute( """SELECT c.* FROM kanban_checklist c JOIN kanban_tasks t ON t.id = c.task_id WHERE t.project_id = ? ORDER BY c.task_id, c.position""", (project_id,), ): extras.setdefault(r["task_id"], {"checklist": [], "requires": [], "blocked": False})["checklist"].append( {"id": r["id"], "text": r["text"], "done": bool(r["done"])} ) for r in conn.execute( """SELECT d.task_id, d.requires_id, rt.status FROM kanban_dependencies d JOIN kanban_tasks t ON t.id = d.task_id JOIN kanban_tasks rt ON rt.id = d.requires_id WHERE t.project_id = ?""", (project_id,), ): e = extras.setdefault(r["task_id"], {"checklist": [], "requires": [], "blocked": False}) e["requires"].append(r["requires_id"]) e["blocked"] = e["blocked"] or r["status"] not in UNLOCKING return extras def _open_requirements(conn, task_id: int) -> list: """Titel der Voraussetzungen, die noch nicht in "Wartet" oder "Erledigt" stehen.""" return [r["title"] for r in conn.execute( f"""SELECT t.title FROM kanban_dependencies d JOIN kanban_tasks t ON t.id = d.requires_id WHERE d.task_id = ? AND t.status NOT IN ({','.join('?' * len(UNLOCKING))}) ORDER BY t.title""", (task_id, *UNLOCKING), )] def _checklist(value) -> list: if not isinstance(value, list) or len(value) > MAX_CHECKLIST: raise ValidationError(f"Ungültige Checkliste (max. {MAX_CHECKLIST} Punkte).") items = [] for item in value: if not isinstance(item, dict) or not isinstance(item.get("done", False), bool): raise ValidationError("Ungültiger Checklisten-Punkt.") items.append((_name(item.get("text"), "Checklisten-Punkt", MAX_CHECK_TEXT), item.get("done", False))) return items def _save_checklist(conn, task_id: int, items: list) -> None: conn.execute("DELETE FROM kanban_checklist WHERE task_id = ?", (task_id,)) conn.executemany( "INSERT INTO kanban_checklist (task_id, text, done, position) VALUES (?, ?, ?, ?)", [(task_id, text, int(done), i) for i, (text, done) in enumerate(items)], ) def _requires(conn, value, project_id: int, task_id=None) -> list: """Voraussetzungen prüfen: Aufgaben desselben Projekts, nicht sie selbst, keine Kreise.""" if not isinstance(value, list) or len(value) > MAX_REQUIRES or any(isinstance(v, bool) or not isinstance(v, int) for v in value): raise ValidationError("Ungültige Voraussetzungen.") ids = list(dict.fromkeys(value)) if task_id in ids: raise ValidationError("Eine Aufgabe kann nicht sich selbst voraussetzen.") if ids: found = {r[0] for r in conn.execute( f"SELECT id FROM kanban_tasks WHERE project_id = ? AND id IN ({','.join('?' * len(ids))})", (project_id, *ids) )} if found != set(ids): raise ValidationError("Voraussetzungen müssen Aufgaben aus diesem Projekt sein.") if task_id is not None: # Kreis: hängt eine der Voraussetzungen (über Umwege) von dieser Aufgabe ab? seen, todo = set(), list(ids) while todo: current = todo.pop() if current == task_id: raise ValidationError("Das ergäbe einen Kreis: diese Aufgabe ist selbst Voraussetzung einer der gewählten.") if current in seen: continue seen.add(current) todo += [r[0] for r in conn.execute("SELECT requires_id FROM kanban_dependencies WHERE task_id = ?", (current,))] return ids def _save_requires(conn, task_id: int, ids: list) -> None: conn.execute("DELETE FROM kanban_dependencies WHERE task_id = ?", (task_id,)) conn.executemany("INSERT INTO kanban_dependencies (task_id, requires_id) VALUES (?, ?)", [(task_id, r) for r in ids]) def _ensure_unlocked(conn, task_id: int, status: str) -> None: """Gesperrte Aufgaben bleiben in "Offen", bis alle Voraussetzungen in "Wartet" oder "Erledigt" stehen.""" if status != "offen" and (missing := _open_requirements(conn, task_id)): raise ValidationError("Gesperrt – erst auf „Wartet“ oder „Erledigt“ setzen: " + ", ".join(f"„{t}“" for t in missing)) def _board(conn, project_id: int, user: dict) -> dict: projects = _projects(conn, user, project_id) if not projects: raise HTTPException(404, "Kanban-Projekt nicht gefunden – vielleicht wurde es gerade gelöscht.") project = projects[0] cleanup(conn) tasks = conn.execute( """SELECT t.*, cu.display_name AS created_by_name, uu.display_name AS updated_by_name FROM kanban_tasks t LEFT JOIN users cu ON cu.id = t.created_by LEFT JOIN users uu ON uu.id = t.updated_by WHERE t.project_id = ? ORDER BY t.status, t.priority, t.position""", (project_id,), ).fetchall() archive_before = iso_ago(ARCHIVE_AFTER) archived = sorted( (r for r in tasks if r["status"] == "erledigt" and r["done_at"] and r["done_at"] <= archive_before), key=lambda r: r["done_at"], reverse=True, ) archived_ids = {r["id"] for r in archived} extras = _task_extras(conn, project_id) return { "project": project, "tasks": [{**_task(r), **extras.get(r["id"], {})} for r in tasks if r["id"] not in archived_ids], # Archiv: nur mit eigenem Button sichtbar; deleteAt = automatisches Löschen "archived": [ {**_task(r), **extras.get(r["id"], {}), "deleteAt": iso_after(r["done_at"], DELETE_AFTER)} for r in archived ], "users": user_list(conn), # Namen (auch ehemaliger Mitglieder) für die Anzeige "people": _people(conn, conn.execute("SELECT * FROM kanban_projects WHERE id = ?", (project_id,)).fetchone()), "shareable": shareable(conn, user), "articles": _articles(conn, user), } def _save_links(conn, project_id: int, links: list) -> None: conn.execute("DELETE FROM kanban_project_links WHERE project_id = ?", (project_id,)) conn.executemany( "INSERT INTO kanban_project_links (project_id, article_id) VALUES (?, ?)", [(project_id, a) for a in links] ) # ---------- Übersicht ---------- @router.get("/api/kanban") def get_overview(user: dict = Depends(current_user)): with transaction() as conn: return _overview(conn, user) # ---------- Projekte ---------- @router.post("/api/kanban/projekte", status_code=201) def create_project(data: dict = Body(...), user: dict = Depends(current_user)): name = _name(data.get("name")) description = _description(data.get("description")) with transaction() as conn: links = _links(conn, data.get("links"), user) members = _members(conn, data.get("members", []), user, user["id"]) if conn.execute("SELECT COUNT(*) FROM kanban_projects").fetchone()[0] >= MAX_PROJECTS: raise ValidationError(f"Maximal {MAX_PROJECTS} Kanban-Projekte.") now = now_iso() project_id = conn.execute( """INSERT INTO kanban_projects (name, description, position, created_at, created_by, updated_at, updated_by) VALUES (?, ?, ?, ?, ?, ?, ?)""", (name, description, _next(conn, "kanban_projects"), now, user["id"], now, user["id"]), ).lastrowid _save_links(conn, project_id, links) _save_members(conn, project_id, members) return {"id": project_id, **_overview(conn, user)} @router.get("/api/kanban/projekte/{project_id}") def get_board(project_id: int, user: dict = Depends(current_user)): with transaction() as conn: return _board(conn, project_id, user) @router.patch("/api/kanban/projekte/{project_id}") def update_project(project_id: int, data: dict = Body(...), user: dict = Depends(current_user)): with transaction() as conn: project = _get_project(conn, project_id, user) values = {} if "name" in data: values["name"] = _name(data["name"]) if "description" in data: values["description"] = _description(data["description"]) if "completed" in data: if not isinstance(data["completed"], bool): raise ValidationError("Ungültiger Wert für abgeschlossen.") values["completed_at"] = now_iso() if data["completed"] else None if "links" in data: current = [r["article_id"] for r in conn.execute("SELECT article_id FROM kanban_project_links WHERE project_id = ?", (project_id,))] _save_links(conn, project_id, _links(conn, data["links"], user, current)) if "members" in data: if not _may_delete(user, project["created_by"]): raise HTTPException(403, "Mitglieder verwalten dürfen nur wer das Projekt angelegt hat und Administratoren.") _save_members(conn, project_id, _members(conn, data["members"], user, project["created_by"], _member_ids(conn, project_id))) values.update(updated_at=now_iso(), updated_by=user["id"]) columns = ", ".join(f"{k} = ?" for k in values) # Schlüssel stammen nur aus der Liste oben conn.execute(f"UPDATE kanban_projects SET {columns} WHERE id = ?", (*values.values(), project_id)) return _board(conn, project_id, user) @router.delete("/api/kanban/projekte/{project_id}") def delete_project(project_id: int, user: dict = Depends(current_user)): with transaction() as conn: row = _get_project(conn, project_id, user) if not _may_delete(user, row["created_by"]): raise HTTPException(403, "Nur wer das Projekt angelegt hat oder ein Administrator darf es löschen.") conn.execute("DELETE FROM kanban_projects WHERE id = ?", (project_id,)) # Aufgaben & Links per CASCADE return _overview(conn, user) # ---------- Aufgaben ---------- def _get_task(conn, task_id: int, user: dict): row = conn.execute("SELECT * FROM kanban_tasks WHERE id = ?", (task_id,)).fetchone() if not row: raise HTTPException(404, "Aufgabe nicht gefunden – vielleicht wurde sie gerade gelöscht.") _get_project(conn, row["project_id"], user) return row def _end_of(conn, project_id: int, status: str) -> float: return _next(conn, "kanban_tasks", "WHERE project_id = ? AND status = ?", (project_id, status)) @router.post("/api/kanban/projekte/{project_id}/aufgaben", status_code=201) def create_task(project_id: int, data: dict = Body(...), user: dict = Depends(current_user)): title = _name(data.get("title"), "Titel", MAX_TITLE) description = _description(data.get("description")) status = _status(data.get("status", "offen")) priority = _priority(data.get("priority", "mittel")) checklist = _checklist(data.get("checklist", [])) with transaction() as conn: project = _get_project(conn, project_id, user) requires = _requires(conn, data.get("requires", []), project_id) if conn.execute("SELECT COUNT(*) FROM kanban_tasks WHERE project_id = ?", (project_id,)).fetchone()[0] >= MAX_TASKS: raise ValidationError(f"Maximal {MAX_TASKS} Aufgaben pro Projekt. Bitte erledigte Aufgaben löschen.") now = now_iso() conn.execute( """INSERT INTO kanban_tasks (project_id, title, description, status, assignee, priority, position, done_at, created_at, created_by, updated_at, updated_by) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)""", (project_id, title, description, status, _assignee(conn, data.get("assignee"), project), priority, _end_of(conn, project_id, status), now if status == "erledigt" else None, now, user["id"], now, user["id"]), ) task_id = conn.execute("SELECT last_insert_rowid()").fetchone()[0] _save_checklist(conn, task_id, checklist) _save_requires(conn, task_id, requires) _ensure_unlocked(conn, task_id, status) return _board(conn, project_id, user) @router.patch("/api/kanban/aufgaben/{task_id}") def update_task(task_id: int, data: dict = Body(...), user: dict = Depends(current_user)): """Felder einzeln ändern. Neuer Status oder neue Priorität ohne Position: ans Ende der Stufe.""" with transaction() as conn: row = _get_task(conn, task_id, user) values = {} if "title" in data: values["title"] = _name(data["title"], "Titel", MAX_TITLE) if "description" in data: values["description"] = _description(data["description"]) if "assignee" in data: values["assignee"] = _assignee(conn, data["assignee"], _get_project(conn, row["project_id"], user)) if "status" in data: values["status"] = _status(data["status"]) if values["status"] != row["status"]: # Erledigt-Uhr starten bzw. beim Zurückholen aus "erledigt" anhalten values["done_at"] = now_iso() if values["status"] == "erledigt" else None if "priority" in data: values["priority"] = _priority(data["priority"]) if "position" in data: values["position"] = _position(data["position"]) elif values.get("status", row["status"]) != row["status"] or values.get("priority", row["priority"]) != row["priority"]: # Neue Spalte oder neue Priorität: ans Ende (innerhalb der Stufe, da nach Priorität sortiert wird) values["position"] = _end_of(conn, row["project_id"], values.get("status", row["status"])) changed = bool(values) if "checklist" in data: _save_checklist(conn, task_id, _checklist(data["checklist"])) changed = True if "requires" in data: _save_requires(conn, task_id, _requires(conn, data["requires"], row["project_id"], task_id)) changed = True # Gesperrt: darf nicht aus "Offen" heraus. War sie schon weiter, bleibt sie, wo sie ist. if values.get("status", row["status"]) != row["status"]: _ensure_unlocked(conn, task_id, values["status"]) if changed: values.update(updated_at=now_iso(), updated_by=user["id"]) columns = ", ".join(f"{k} = ?" for k in values) # Schlüssel stammen nur aus der Liste oben conn.execute(f"UPDATE kanban_tasks SET {columns} WHERE id = ?", (*values.values(), task_id)) return _board(conn, row["project_id"], user) @router.post("/api/kanban/aufgaben/{task_id}/zurueckholen") def restore_task(task_id: int, user: dict = Depends(current_user)): """Archivierte Aufgabe zurück in die Spalte "Erledigt"; Archiv- und Lösch-Frist beginnen neu.""" with transaction() as conn: row = _get_task(conn, task_id, user) now = now_iso() conn.execute( "UPDATE kanban_tasks SET status = 'erledigt', done_at = ?, updated_at = ?, updated_by = ? WHERE id = ?", (now, now, user["id"], task_id), ) return _board(conn, row["project_id"], user) @router.delete("/api/kanban/aufgaben/{task_id}") def delete_task(task_id: int, user: dict = Depends(current_user)): with transaction() as conn: row = _get_task(conn, task_id, user) if not _may_delete(user, row["created_by"]): raise HTTPException(403, "Nur wer die Aufgabe angelegt hat oder ein Administrator darf sie löschen.") conn.execute("DELETE FROM kanban_tasks WHERE id = ?", (task_id,)) return _board(conn, row["project_id"], user) # ---------- Für die Artikelseite ---------- def boards_for_article(conn, article_id: str, user: dict) -> list: """Verknüpfte Boards, die das Konto sehen darf.""" rows = conn.execute( f"""SELECT k.id, k.name FROM kanban_project_links l JOIN kanban_projects k ON k.id = l.project_id WHERE l.article_id = :a AND {_VISIBLE} ORDER BY k.name COLLATE NOCASE""", {"a": article_id, **_who(user)}, ).fetchall() return [{"id": r["id"], "name": r["name"]} for r in rows]