[Bug] DELETE /jobs/{job_id} removes the workspace but orphans the database rows"

[arpit2006]

What happened

DELETE /jobs/{job_id} deletes the on-disk working directory for a scan job, but it does not remove the corresponding rows from the SQLite database. After deletion the job still appears in dashboard charts (/trends, /cwe-distribution, /dependency-diff) and the findings are still returned by /jobs/{job_id}/findings, even though the workspace is gone.

Steps to reproduce

1. Run a scan (upload a ZIP or paste a GitHub URL). Note the returned job_id.
2. Call DELETE /jobs/{job_id}.
3. Call GET /trends — the deleted job's scan is still counted.
4. Call GET /jobs/{job_id}/findings — it still returns the old findings.
5. Check the working directory $PATCHPILOT_WORKDIR/<job_id> — it has been deleted.

Expected behaviour

Deleting a job removes:

• The on-disk workspace (already happens ✅)
• All rows in findings where job_id = ?
• All rows in verify_outcomes where job_id = ?
• The row in jobs where job_id = ?

These three deletions should be wrapped in a single database transaction so they succeed or fail together.

Actual behaviour

Only safe_rmtree(job_dir) is called. No SQL DELETE statements are executed.

Relevant code — backend/app/main.py, lines 1022–1027:

@app.delete("/jobs/{job_id}")
def delete_job(job_id: str):
    job_dir = WORK_ROOT / job_id
    if job_dir.exists():
        safe_rmtree(job_dir)
    return {"deleted": True}

Environment

Field	Value
OS	Any
Python version	3.10+
PatchPilot version / commit	main
Scan method	Any

Logs

No error is raised — the endpoint returns {"deleted": true} normally. The issue is silent data leakage, not a crash.

Additional context

The fix is a few lines of SQL added to delete_job. The function should also be made async so it can await the DB calls. A test should be added to tests/test_job_endpoints.py that:

1. Creates a scan job (mock or fixture).
2. Calls DELETE /jobs/{job_id}.
3. Verifies the DB rows are gone.

Acceptance criteria:

• DELETE /jobs/{job_id} deletes rows from findings, verify_outcomes, and jobs in a transaction.
• Returns {"deleted": true} only if all DB deletions succeed.
• A test in tests/test_job_endpoints.py covers the DB cleanup.

[github-actions]

👋 Welcome @arpit2006!

Thank you for opening an issue and contributing to Patchpilot. 🚀

Next Steps

• Please ensure you've provided all the required details.
• A maintainer will review your issue shortly.
• Feel free to ask questions if anything is unclear.

⚡ Want faster assignment and quicker responses?

Join our Discord community:

🔗 https://discord.gg/FcXuyw2Rs

We actively discuss issues, features, roadmap updates, and contributor support there.

Happy Contributing! 🎉