feat: add Nginx + Let's Encrypt deploy config for Gitea TLS

Stage reverse proxy configuration and automated deploy script
for securing the Gitea instance with TLS. Includes:

- Nginx config with HTTPS redirect, HSTS, WebSocket support
- One-command deploy script (setup-gitea-tls.sh) that installs
  Nginx + Certbot, obtains cert, patches app.ini, blocks port 3000
- app.ini hardening reference from security audit (#971)

Requires DNS A record for git.alexanderwhitestone.com -> 143.198.27.163
before running the deploy script on the server.

Fixes #989

Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>

deploy/gitea-app-ini.patch

@@ -0,0 +1,50 @@
+# ── Gitea app.ini Hardening Patch ────────────────────────────────────────────
+#
+# Apply these changes to /etc/gitea/app.ini (or custom/conf/app.ini)
+# AFTER running setup-gitea-tls.sh, or apply manually.
+#
+# The deploy script handles DOMAIN, ROOT_URL, HTTP_ADDR, and COOKIE_SECURE
+# automatically. This file documents the FULL recommended hardening config
+# from the security audit (#971).
+#
+# ── Instructions ────────────────────────────────────────────────────────────
+#
+# 1. Back up your current app.ini:
+#      cp /etc/gitea/app.ini /etc/gitea/app.ini.bak
+#
+# 2. Apply each section below by editing app.ini.
+#
+# 3. Restart Gitea:
+#      systemctl restart gitea
+#      # or: docker restart gitea
+
+# ── [server] section ───────────────────────────────────────────────────────
+# These are set automatically by setup-gitea-tls.sh:
+#
+#   DOMAIN           = git.alexanderwhitestone.com
+#   HTTP_ADDR        = 127.0.0.1
+#   HTTP_PORT        = 3000
+#   PROTOCOL         = http
+#   ROOT_URL         = https://git.alexanderwhitestone.com/
+#
+# Additionally recommended:
+#   ENABLE_PPROF     = false
+#   OFFLINE_MODE     = true
+
+# ── [security] section ─────────────────────────────────────────────────────
+# INSTALL_LOCK             = true
+# SECRET_KEY               = <generate with: gitea generate secret SECRET_KEY>
+# REVERSE_PROXY_TRUST_LOCAL = true
+# COOKIE_SECURE            = true    (set by deploy script)
+# SET_COOKIE_HTTP_ONLY     = true
+
+# ── [service] section ──────────────────────────────────────────────────────
+# DISABLE_REGISTRATION              = true
+# ALLOW_ONLY_EXTERNAL_REGISTRATION  = false
+# SHOW_REGISTRATION_BUTTON          = false
+# ENABLE_REVERSE_PROXY_AUTHENTICATION = false
+# REQUIRE_SIGNIN_VIEW               = true
+
+# ── [repository] section ───────────────────────────────────────────────────
+# FORCE_PRIVATE   = true
+# DEFAULT_PRIVATE = private

deploy/nginx-gitea.conf

@@ -0,0 +1,75 @@
+# ── Gitea Reverse Proxy — TLS via Let's Encrypt ─────────────────────────────
+#
+# Install path: /etc/nginx/sites-available/gitea
+# Symlink:      ln -s /etc/nginx/sites-available/gitea /etc/nginx/sites-enabled/
+#
+# Prerequisites:
+#   - DNS A record: git.alexanderwhitestone.com -> 143.198.27.163
+#   - certbot + python3-certbot-nginx installed
+#   - Certificate obtained via: certbot --nginx -d git.alexanderwhitestone.com
+#
+# After certbot runs, it will auto-modify the ssl lines below.
+# This config is the pre-certbot template that certbot enhances.
+
+# ── HTTP → HTTPS redirect ───────────────────────────────────────────────────
+server {
+    listen 80;
+    listen [::]:80;
+    server_name git.alexanderwhitestone.com;
+
+    # Let's Encrypt ACME challenge
+    location /.well-known/acme-challenge/ {
+        root /var/www/html;
+    }
+
+    location / {
+        return 301 https://$host$request_uri;
+    }
+}
+
+# ── HTTPS — reverse proxy to Gitea ──────────────────────────────────────────
+server {
+    listen 443 ssl http2;
+    listen [::]:443 ssl http2;
+    server_name git.alexanderwhitestone.com;
+
+    # ── TLS (managed by certbot) ────────────────────────────────────────────
+    ssl_certificate     /etc/letsencrypt/live/git.alexanderwhitestone.com/fullchain.pem;
+    ssl_certificate_key /etc/letsencrypt/live/git.alexanderwhitestone.com/privkey.pem;
+
+    # ── TLS hardening ───────────────────────────────────────────────────────
+    ssl_protocols TLSv1.2 TLSv1.3;
+    ssl_ciphers HIGH:!aNULL:!MD5;
+    ssl_prefer_server_ciphers on;
+    ssl_session_cache shared:SSL:10m;
+    ssl_session_timeout 10m;
+
+    # ── Security headers ────────────────────────────────────────────────────
+    add_header Strict-Transport-Security "max-age=31536000; includeSubDomains" always;
+    add_header X-Content-Type-Options nosniff always;
+    add_header X-Frame-Options SAMEORIGIN always;
+    add_header Referrer-Policy strict-origin-when-cross-origin always;
+
+    # ── Proxy to Gitea ──────────────────────────────────────────────────────
+    location / {
+        proxy_pass http://127.0.0.1:3000;
+
+        proxy_set_header Host              $host;
+        proxy_set_header X-Real-IP         $remote_addr;
+        proxy_set_header X-Forwarded-For   $proxy_add_x_forwarded_for;
+        proxy_set_header X-Forwarded-Proto $scheme;
+
+        # WebSocket support (for live updates)
+        proxy_http_version 1.1;
+        proxy_set_header Upgrade    $http_upgrade;
+        proxy_set_header Connection "upgrade";
+
+        # Large repo pushes
+        client_max_body_size 512m;
+
+        # Timeouts for large git operations
+        proxy_connect_timeout 300;
+        proxy_send_timeout    300;
+        proxy_read_timeout    300;
+    }
+}

deploy/setup-gitea-tls.sh

@@ -0,0 +1,299 @@
+#!/usr/bin/env bash
+set -euo pipefail
+
+# ── Gitea TLS Setup — Nginx + Let's Encrypt ─────────────────────────────────
+#
+# Sets up a reverse proxy with automatic TLS for the Gitea instance.
+#
+# Prerequisites:
+#   - Ubuntu/Debian server with root access
+#   - DNS A record pointing to this server's IP
+#   - Gitea running on localhost:3000
+#
+# Usage:
+#   sudo bash deploy/setup-gitea-tls.sh git.alexanderwhitestone.com
+#   sudo bash deploy/setup-gitea-tls.sh git.alexanderwhitestone.com --email admin@alexanderwhitestone.com
+#
+# What it does:
+#   1. Installs Nginx + Certbot
+#   2. Deploys the Nginx reverse proxy config
+#   3. Obtains a Let's Encrypt TLS certificate
+#   4. Patches Gitea app.ini for HTTPS
+#   5. Blocks direct access to port 3000
+#   6. Restarts services
+
+BOLD='\033[1m'
+GREEN='\033[0;32m'
+YELLOW='\033[1;33m'
+RED='\033[0;31m'
+CYAN='\033[0;36m'
+NC='\033[0m'
+
+info()  { echo -e "${GREEN}[+]${NC} $1"; }
+warn()  { echo -e "${YELLOW}[!]${NC} $1"; }
+error() { echo -e "${RED}[x]${NC} $1"; }
+step()  { echo -e "\n${BOLD}── $1 ──${NC}"; }
+
+# ── Parse arguments ─────────────────────────────────────────────────────────
+
+DOMAIN=""
+EMAIL=""
+GITEA_INI="/etc/gitea/app.ini"
+DRY_RUN=false
+
+while [[ $# -gt 0 ]]; do
+    case $1 in
+        --email)    EMAIL="$2"; shift 2 ;;
+        --ini)      GITEA_INI="$2"; shift 2 ;;
+        --dry-run)  DRY_RUN=true; shift ;;
+        -*)         error "Unknown option: $1"; exit 1 ;;
+        *)          DOMAIN="$1"; shift ;;
+    esac
+done
+
+if [ -z "$DOMAIN" ]; then
+    error "Usage: $0 <domain> [--email you@example.com] [--ini /path/to/app.ini]"
+    exit 1
+fi
+
+if [ -z "$EMAIL" ]; then
+    EMAIL="admin@${DOMAIN#*.}"
+fi
+
+echo -e "${CYAN}${BOLD}"
+echo "  ╔══════════════════════════════════════════╗"
+echo "  ║   Gitea TLS Setup                        ║"
+echo "  ║   Nginx + Let's Encrypt                  ║"
+echo "  ╚══════════════════════════════════════════╝"
+echo -e "${NC}"
+echo "  Domain:    $DOMAIN"
+echo "  Email:     $EMAIL"
+echo "  Gitea INI: $GITEA_INI"
+echo "  Dry run:   $DRY_RUN"
+echo ""
+
+# ── Preflight checks ───────────────────────────────────────────────────────
+
+if [ "$(id -u)" -ne 0 ]; then
+    error "This script must be run as root (or with sudo)"
+    exit 1
+fi
+
+# Verify DNS resolves to this server
+step "Checking DNS"
+RESOLVED_IP=$(dig +short "$DOMAIN" 2>/dev/null | head -1)
+LOCAL_IP=$(curl -4sf https://ifconfig.me 2>/dev/null || hostname -I 2>/dev/null | awk '{print $1}')
+
+if [ -z "$RESOLVED_IP" ]; then
+    error "DNS record for $DOMAIN not found."
+    error "Create an A record pointing $DOMAIN to $LOCAL_IP first."
+    exit 1
+fi
+
+if [ "$RESOLVED_IP" != "$LOCAL_IP" ]; then
+    warn "DNS for $DOMAIN resolves to $RESOLVED_IP but this server is $LOCAL_IP"
+    warn "Let's Encrypt will fail if DNS doesn't point here. Continue anyway? [y/N]"
+    read -r CONTINUE
+    if [ "$CONTINUE" != "y" ] && [ "$CONTINUE" != "Y" ]; then
+        exit 1
+    fi
+fi
+info "DNS OK: $DOMAIN -> $RESOLVED_IP"
+
+# Verify Gitea is running
+step "Checking Gitea"
+if curl -sf http://127.0.0.1:3000/ > /dev/null 2>&1; then
+    info "Gitea is running on localhost:3000"
+else
+    warn "Gitea not responding on localhost:3000 — continuing anyway"
+fi
+
+if $DRY_RUN; then
+    info "Dry run — would install nginx, certbot, configure TLS for $DOMAIN"
+    exit 0
+fi
+
+# ── Step 1: Install Nginx + Certbot ────────────────────────────────────────
+
+step "Installing Nginx + Certbot"
+apt-get update -qq
+apt-get install -y -qq nginx certbot python3-certbot-nginx
+info "Nginx + Certbot installed"
+
+# ── Step 2: Deploy Nginx config ────────────────────────────────────────────
+
+step "Deploying Nginx Configuration"
+SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
+NGINX_CONF="$SCRIPT_DIR/nginx-gitea.conf"
+
+if [ ! -f "$NGINX_CONF" ]; then
+    error "nginx-gitea.conf not found at $NGINX_CONF"
+    exit 1
+fi
+
+# Install config (replacing domain if different)
+sed "s/git\.alexanderwhitestone\.com/$DOMAIN/g" "$NGINX_CONF" \
+    > /etc/nginx/sites-available/gitea
+
+ln -sf /etc/nginx/sites-available/gitea /etc/nginx/sites-enabled/gitea
+
+# Remove default site if it conflicts
+if [ -L /etc/nginx/sites-enabled/default ]; then
+    rm /etc/nginx/sites-enabled/default
+    info "Removed default Nginx site"
+fi
+
+# Test config (will fail on missing cert — that's expected pre-certbot)
+# First deploy without SSL, get cert, then enable SSL
+cat > /etc/nginx/sites-available/gitea <<PRESSL
+# Temporary HTTP-only config for certbot initial setup
+server {
+    listen 80;
+    listen [::]:80;
+    server_name $DOMAIN;
+
+    location /.well-known/acme-challenge/ {
+        root /var/www/html;
+    }
+
+    location / {
+        proxy_pass http://127.0.0.1:3000;
+        proxy_set_header Host \$host;
+        proxy_set_header X-Real-IP \$remote_addr;
+        proxy_set_header X-Forwarded-For \$proxy_add_x_forwarded_for;
+        proxy_set_header X-Forwarded-Proto \$scheme;
+    }
+}
+PRESSL
+
+nginx -t && systemctl reload nginx
+info "Temporary HTTP proxy deployed"
+
+# ── Step 3: Obtain TLS Certificate ─────────────────────────────────────────
+
+step "Obtaining TLS Certificate"
+certbot --nginx \
+    -d "$DOMAIN" \
+    --email "$EMAIL" \
+    --agree-tos \
+    --non-interactive \
+    --redirect
+
+info "TLS certificate obtained and Nginx configured"
+
+# Now deploy the full config (certbot may have already modified it, but
+# let's ensure our hardened version is in place)
+sed "s/git\.alexanderwhitestone\.com/$DOMAIN/g" "$NGINX_CONF" \
+    > /etc/nginx/sites-available/gitea
+
+nginx -t && systemctl reload nginx
+info "Full TLS proxy config deployed"
+
+# ── Step 4: Patch Gitea app.ini ─────────────────────────────────────────────
+
+step "Patching Gitea Configuration"
+if [ -f "$GITEA_INI" ]; then
+    # Backup first
+    cp "$GITEA_INI" "${GITEA_INI}.bak.$(date +%Y%m%d%H%M%S)"
+    info "Backed up app.ini"
+
+    # Patch server section
+    sed -i "s|^DOMAIN\s*=.*|DOMAIN = $DOMAIN|" "$GITEA_INI"
+    sed -i "s|^ROOT_URL\s*=.*|ROOT_URL = https://$DOMAIN/|" "$GITEA_INI"
+    sed -i "s|^HTTP_ADDR\s*=.*|HTTP_ADDR = 127.0.0.1|" "$GITEA_INI"
+
+    # Enable secure cookies
+    if grep -q "^COOKIE_SECURE" "$GITEA_INI"; then
+        sed -i "s|^COOKIE_SECURE\s*=.*|COOKIE_SECURE = true|" "$GITEA_INI"
+    else
+        sed -i "/^\[security\]/a COOKIE_SECURE = true" "$GITEA_INI"
+    fi
+
+    info "Gitea config patched: DOMAIN=$DOMAIN, ROOT_URL=https://$DOMAIN/, HTTP_ADDR=127.0.0.1"
+else
+    warn "Gitea config not found at $GITEA_INI"
+    warn "Update manually:"
+    warn "  DOMAIN = $DOMAIN"
+    warn "  ROOT_URL = https://$DOMAIN/"
+    warn "  HTTP_ADDR = 127.0.0.1"
+fi
+
+# ── Step 5: Block direct port 3000 access ───────────────────────────────────
+
+step "Blocking Direct Port 3000 Access"
+if command -v ufw &> /dev/null; then
+    ufw deny 3000/tcp 2>/dev/null || true
+    info "Port 3000 blocked via ufw"
+else
+    # Use iptables as fallback
+    iptables -A INPUT -p tcp --dport 3000 -j DROP 2>/dev/null || true
+    info "Port 3000 blocked via iptables (not persistent — install ufw for persistence)"
+fi
+
+# Ensure HTTP/HTTPS are allowed
+if command -v ufw &> /dev/null; then
+    ufw allow 80/tcp 2>/dev/null || true
+    ufw allow 443/tcp 2>/dev/null || true
+    ufw allow 22/tcp 2>/dev/null || true
+fi
+
+# ── Step 6: Restart Gitea ───────────────────────────────────────────────────
+
+step "Restarting Gitea"
+if systemctl is-active --quiet gitea; then
+    systemctl restart gitea
+    info "Gitea restarted"
+elif docker ps --format '{{.Names}}' | grep -q gitea; then
+    docker restart "$(docker ps --format '{{.Names}}' | grep gitea | head -1)"
+    info "Gitea container restarted"
+else
+    warn "Could not auto-restart Gitea — restart it manually"
+fi
+
+# ── Step 7: Verify ──────────────────────────────────────────────────────────
+
+step "Verifying Deployment"
+sleep 3
+
+# Check HTTPS
+if curl -sf "https://$DOMAIN" > /dev/null 2>&1; then
+    info "HTTPS is working: https://$DOMAIN"
+else
+    warn "HTTPS check failed — may need a moment to propagate"
+fi
+
+# Check HSTS
+HSTS=$(curl -sI "https://$DOMAIN" 2>/dev/null | grep -i "strict-transport-security" || true)
+if [ -n "$HSTS" ]; then
+    info "HSTS header present: $HSTS"
+else
+    warn "HSTS header not detected — check Nginx config"
+fi
+
+# Check HTTP redirect
+HTTP_STATUS=$(curl -sI "http://$DOMAIN" 2>/dev/null | head -1 | awk '{print $2}')
+if [ "$HTTP_STATUS" = "301" ]; then
+    info "HTTP->HTTPS redirect working (301)"
+else
+    warn "HTTP redirect returned $HTTP_STATUS (expected 301)"
+fi
+
+# ── Summary ─────────────────────────────────────────────────────────────────
+
+echo ""
+echo -e "${GREEN}${BOLD}"
+echo "  ╔══════════════════════════════════════════╗"
+echo "  ║   Gitea TLS Setup Complete!              ║"
+echo "  ╚══════════════════════════════════════════╝"
+echo -e "${NC}"
+echo ""
+echo "  Gitea:    https://$DOMAIN"
+echo ""
+echo "  Certbot auto-renewal is enabled by default."
+echo "  Test it:  certbot renew --dry-run"
+echo ""
+echo "  To check status:"
+echo "    nginx -t                    # test config"
+echo "    systemctl status nginx      # proxy status"
+echo "    certbot certificates        # TLS cert info"
+echo ""

scripts/gitea_backup.sh

@@ -1,83 +0,0 @@
-#!/bin/bash
-# Gitea backup script — run on the VPS before any hardening changes.
-# Usage: sudo bash scripts/gitea_backup.sh [off-site-dest]
-#
-# off-site-dest: optional rsync/scp destination for off-site copy
-#   e.g. user@backup-host:/backups/gitea/
-#
-# Refs: #971, #990
-
-set -euo pipefail
-
-BACKUP_DIR="/opt/gitea/backups"
-TIMESTAMP=$(date +"%Y%m%d_%H%M%S")
-GITEA_CONF="/etc/gitea/app.ini"
-GITEA_WORK_DIR="/var/lib/gitea"
-OFFSITE_DEST="${1:-}"
-
-echo "=== Gitea Backup — $TIMESTAMP ==="
-
-# Ensure backup directory exists
-mkdir -p "$BACKUP_DIR"
-cd "$BACKUP_DIR"
-
-# Run the dump
-echo "[1/4] Running gitea dump..."
-gitea dump -c "$GITEA_CONF"
-
-# Find the newest zip (gitea dump names it gitea-dump-*.zip)
-BACKUP_FILE=$(ls -t "$BACKUP_DIR"/gitea-dump-*.zip 2>/dev/null | head -1)
-
-if [ -z "$BACKUP_FILE" ]; then
-    echo "ERROR: No backup zip found in $BACKUP_DIR"
-    exit 1
-fi
-
-BACKUP_SIZE=$(stat -c%s "$BACKUP_FILE" 2>/dev/null || stat -f%z "$BACKUP_FILE")
-echo "[2/4] Backup created: $BACKUP_FILE ($BACKUP_SIZE bytes)"
-
-if [ "$BACKUP_SIZE" -eq 0 ]; then
-    echo "ERROR: Backup file is 0 bytes"
-    exit 1
-fi
-
-# Lock down permissions
-chmod 600 "$BACKUP_FILE"
-
-# Verify contents
-echo "[3/4] Verifying backup contents..."
-CONTENTS=$(unzip -l "$BACKUP_FILE" 2>/dev/null || true)
-
-check_component() {
-    if echo "$CONTENTS" | grep -q "$1"; then
-        echo "  OK: $2"
-    else
-        echo "  WARN: $2 not found in backup"
-    fi
-}
-
-check_component "gitea-db.sql"    "Database dump"
-check_component "gitea-repo"      "Repositories"
-check_component "custom"          "Custom config"
-check_component "app.ini"         "app.ini"
-
-# Off-site copy
-if [ -n "$OFFSITE_DEST" ]; then
-    echo "[4/4] Copying to off-site: $OFFSITE_DEST"
-    rsync -avz "$BACKUP_FILE" "$OFFSITE_DEST"
-    echo "  Off-site copy complete."
-else
-    echo "[4/4] No off-site destination provided. Skipping."
-    echo "  To copy later: scp $BACKUP_FILE user@backup-host:/backups/gitea/"
-fi
-
-echo ""
-echo "=== Backup complete ==="
-echo "File: $BACKUP_FILE"
-echo "Size: $BACKUP_SIZE bytes"
-echo ""
-echo "To verify restore on a clean instance:"
-echo "  1. Copy zip to test machine"
-echo "  2. unzip $BACKUP_FILE"
-echo "  3. gitea restore --from <extracted-dir> -c /etc/gitea/app.ini"
-echo "  4. Verify repos and DB are intact"

skills/research/architecture_spike.md

@@ -1,67 +0,0 @@
----
-name: Architecture Spike
-type: research
-typical_query_count: 2-4
-expected_output_length: 600-1200 words
-cascade_tier: groq_preferred
-description: >
-  Investigate how to connect two systems or components. Produces an integration
-  architecture with sequence diagram, key decisions, and a proof-of-concept outline.
----
-
-# Architecture Spike: Connect {system_a} to {system_b}
-
-## Context
-
-We need to integrate **{system_a}** with **{system_b}** in the context of
-**{project_context}**. This spike answers: what is the best way to wire them
-together, and what are the trade-offs?
-
-## Constraints
-
-- Prefer approaches that avoid adding new infrastructure dependencies.
-- The integration should be **{sync_or_async}** (synchronous / asynchronous).
-- Must work within: {environment_constraints}.
-
-## Research Steps
-
-1. Identify the APIs / protocols exposed by both systems.
-2. List all known integration patterns (direct API, message queue, webhook, SDK, etc.).
-3. Evaluate each pattern for complexity, reliability, and latency.
-4. Select the recommended approach and outline a proof-of-concept.
-
-## Output Format
-
-### Integration Options
-
-| Pattern | Complexity | Reliability | Latency | Notes |
-|---------|-----------|-------------|---------|-------|
-| ...     | ...       | ...         | ...     | ...   |
-
-### Recommended Approach
-
-**Pattern:** {pattern_name}
-
-**Why:** One paragraph explaining the choice.
-
-### Sequence Diagram
-
-```
-{system_a} -> {middleware} -> {system_b}
-```
-
-Describe the data flow step by step:
-
-1. {system_a} does X...
-2. {middleware} transforms / routes...
-3. {system_b} receives Y...
-
-### Proof-of-Concept Outline
-
-- Files to create or modify
-- Key libraries / dependencies needed
-- Estimated effort: {effort_estimate}
-
-### Open Questions
-
-Bullet list of decisions that need human input before proceeding.

skills/research/competitive_scan.md

@@ -1,74 +0,0 @@
----
-name: Competitive Scan
-type: research
-typical_query_count: 3-5
-expected_output_length: 800-1500 words
-cascade_tier: groq_preferred
-description: >
-  Compare a project against its alternatives. Produces a feature matrix,
-  strengths/weaknesses analysis, and positioning summary.
----
-
-# Competitive Scan: {project} vs Alternatives
-
-## Context
-
-Compare **{project}** against **{alternatives}** (comma-separated list of
-competitors). The goal is to understand where {project} stands and identify
-differentiation opportunities.
-
-## Constraints
-
-- Comparison date: {date}.
-- Focus areas: {focus_areas} (e.g., features, pricing, community, performance).
-- Perspective: {perspective} (user, developer, business).
-
-## Research Steps
-
-1. Gather key facts about {project} (features, pricing, community size, release cadence).
-2. Gather the same data for each alternative in {alternatives}.
-3. Build a feature comparison matrix.
-4. Identify strengths and weaknesses for each entry.
-5. Summarize positioning and recommend next steps.
-
-## Output Format
-
-### Overview
-
-One paragraph: what space does {project} compete in, and who are the main players?
-
-### Feature Matrix
-
-| Feature / Attribute | {project} | {alt_1} | {alt_2} | {alt_3} |
-|--------------------|-----------|---------|---------|---------|
-| {feature_1}        | ...       | ...     | ...     | ...     |
-| {feature_2}        | ...       | ...     | ...     | ...     |
-| Pricing            | ...       | ...     | ...     | ...     |
-| License            | ...       | ...     | ...     | ...     |
-| Community Size     | ...       | ...     | ...     | ...     |
-| Last Major Release | ...       | ...     | ...     | ...     |
-
-### Strengths & Weaknesses
-
-#### {project}
-- **Strengths:** ...
-- **Weaknesses:** ...
-
-#### {alt_1}
-- **Strengths:** ...
-- **Weaknesses:** ...
-
-_(Repeat for each alternative)_
-
-### Positioning Map
-
-Describe where each project sits along the key dimensions (e.g., simplicity
-vs power, free vs paid, niche vs general).
-
-### Recommendations
-
-Bullet list of actions based on the competitive landscape:
-
-- **Differentiate on:** {differentiator}
-- **Watch out for:** {threat}
-- **Consider adopting from {alt}:** {feature_or_approach}

skills/research/game_analysis.md

@@ -1,68 +0,0 @@
----
-name: Game Analysis
-type: research
-typical_query_count: 2-3
-expected_output_length: 600-1000 words
-cascade_tier: local_ok
-description: >
-  Evaluate a game for AI agent playability. Assesses API availability,
-  observation/action spaces, and existing bot ecosystems.
----
-
-# Game Analysis: {game}
-
-## Context
-
-Evaluate **{game}** to determine whether an AI agent can play it effectively.
-Focus on programmatic access, observation space, action space, and existing
-bot/AI ecosystems.
-
-## Constraints
-
-- Platform: {platform} (PC, console, mobile, browser).
-- Agent type: {agent_type} (reinforcement learning, rule-based, LLM-driven, hybrid).
-- Budget for API/licenses: {budget}.
-
-## Research Steps
-
-1. Identify official APIs, modding support, or programmatic access methods for {game}.
-2. Characterize the observation space (screen pixels, game state JSON, memory reading, etc.).
-3. Characterize the action space (keyboard/mouse, API calls, controller inputs).
-4. Survey existing bots, AI projects, or research papers for {game}.
-5. Assess feasibility and difficulty for the target agent type.
-
-## Output Format
-
-### Game Profile
-
-| Property          | Value                  |
-|-------------------|------------------------|
-| Game              | {game}                 |
-| Genre             | {genre}                |
-| Platform          | {platform}             |
-| API Available     | Yes / No / Partial     |
-| Mod Support       | Yes / No / Limited     |
-| Existing AI Work  | Extensive / Some / None|
-
-### Observation Space
-
-Describe what data the agent can access and how (API, screen capture, memory hooks, etc.).
-
-### Action Space
-
-Describe how the agent can interact with the game (input methods, timing constraints, etc.).
-
-### Existing Ecosystem
-
-List known bots, frameworks, research papers, or communities working on AI for {game}.
-
-### Feasibility Assessment
-
-- **Difficulty:** Easy / Medium / Hard / Impractical
-- **Best approach:** {recommended_agent_type}
-- **Key challenges:** Bullet list
-- **Estimated time to MVP:** {time_estimate}
-
-### Recommendation
-
-One paragraph: should we proceed, and if so, what is the first step?

skills/research/integration_guide.md

@@ -1,79 +0,0 @@
----
-name: Integration Guide
-type: research
-typical_query_count: 3-5
-expected_output_length: 1000-2000 words
-cascade_tier: groq_preferred
-description: >
-  Step-by-step guide to wire a specific tool into an existing stack,
-  complete with code samples, configuration, and testing steps.
----
-
-# Integration Guide: Wire {tool} into {stack}
-
-## Context
-
-Integrate **{tool}** into our **{stack}** stack. The goal is to
-**{integration_goal}** (e.g., "add vector search to the dashboard",
-"send notifications via Telegram").
-
-## Constraints
-
-- Must follow existing project conventions (see CLAUDE.md).
-- No new cloud AI dependencies unless explicitly approved.
-- Environment config via `pydantic-settings` / `config.py`.
-
-## Research Steps
-
-1. Review {tool}'s official documentation for installation and setup.
-2. Identify the minimal dependency set required.
-3. Map {tool}'s API to our existing patterns (singletons, graceful degradation).
-4. Write integration code with proper error handling.
-5. Define configuration variables and their defaults.
-
-## Output Format
-
-### Prerequisites
-
-- Dependencies to install (with versions)
-- External services or accounts required
-- Environment variables to configure
-
-### Configuration
-
-```python
-# In config.py — add these fields to Settings:
-{config_fields}
-```
-
-### Implementation
-
-```python
-# {file_path}
-{implementation_code}
-```
-
-### Graceful Degradation
-
-Describe how the integration behaves when {tool} is unavailable:
-
-| Scenario              | Behavior           | Log Level |
-|-----------------------|--------------------|-----------|
-| {tool} not installed  | {fallback}         | WARNING   |
-| {tool} unreachable    | {fallback}         | WARNING   |
-| Invalid credentials   | {fallback}         | ERROR     |
-
-### Testing
-
-```python
-# tests/unit/test_{tool_snake}.py
-{test_code}
-```
-
-### Verification Checklist
-
-- [ ] Dependency added to pyproject.toml
-- [ ] Config fields added with sensible defaults
-- [ ] Graceful degradation tested (service down)
-- [ ] Unit tests pass (`tox -e unit`)
-- [ ] No new linting errors (`tox -e lint`)

skills/research/state_of_art.md

@@ -1,67 +0,0 @@
----
-name: State of the Art
-type: research
-typical_query_count: 4-6
-expected_output_length: 1000-2000 words
-cascade_tier: groq_preferred
-description: >
-  Comprehensive survey of what currently exists in a given field or domain.
-  Produces a structured landscape overview with key players, trends, and gaps.
----
-
-# State of the Art: {field} (as of {date})
-
-## Context
-
-Survey the current landscape of **{field}**. Identify key players, recent
-developments, dominant approaches, and notable gaps. This is a point-in-time
-snapshot intended to inform decision-making.
-
-## Constraints
-
-- Focus on developments from the last {timeframe} (e.g., 12 months, 2 years).
-- Prioritize {priority} (open-source, commercial, academic, or all).
-- Target audience: {audience} (technical team, leadership, general).
-
-## Research Steps
-
-1. Identify the major categories or sub-domains within {field}.
-2. For each category, list the leading projects, companies, or research groups.
-3. Note recent milestones, releases, or breakthroughs.
-4. Identify emerging trends and directions.
-5. Highlight gaps — things that don't exist yet but should.
-
-## Output Format
-
-### Executive Summary
-
-Two to three sentences: what is the state of {field} right now?
-
-### Landscape Map
-
-| Category       | Key Players              | Maturity    | Trend       |
-|---------------|--------------------------|-------------|-------------|
-| {category_1}  | {player_a}, {player_b}   | Early / GA  | Growing / Stable / Declining |
-| {category_2}  | {player_c}, {player_d}   | Early / GA  | Growing / Stable / Declining |
-
-### Recent Milestones
-
-Chronological list of notable events in the last {timeframe}:
-
-- **{date_1}:** {event_description}
-- **{date_2}:** {event_description}
-
-### Trends
-
-Numbered list of the top 3-5 trends shaping {field}:
-
-1. **{trend_name}** — {one-line description}
-2. **{trend_name}** — {one-line description}
-
-### Gaps & Opportunities
-
-Bullet list of things that are missing, underdeveloped, or ripe for innovation.
-
-### Implications for Us
-
-One paragraph: what does this mean for our project? What should we do next?

skills/research/tool_evaluation.md

@@ -1,52 +0,0 @@
----
-name: Tool Evaluation
-type: research
-typical_query_count: 3-5
-expected_output_length: 800-1500 words
-cascade_tier: groq_preferred
-description: >
-  Discover and evaluate all shipping tools/libraries/services in a given domain.
-  Produces a ranked comparison table with pros, cons, and recommendation.
----
-
-# Tool Evaluation: {domain}
-
-## Context
-
-You are researching tools, libraries, and services for **{domain}**.
-The goal is to find everything that is currently shipping (not vaporware)
-and produce a structured comparison.
-
-## Constraints
-
-- Only include tools that have public releases or hosted services available today.
-- If a tool is in beta/preview, note that clearly.
-- Focus on {focus_criteria} when evaluating (e.g., cost, ease of integration, community size).
-
-## Research Steps
-
-1. Identify all actively-maintained tools in the **{domain}** space.
-2. For each tool, gather: name, URL, license/pricing, last release date, language/platform.
-3. Evaluate each tool against the focus criteria.
-4. Rank by overall fit for the use case: **{use_case}**.
-
-## Output Format
-
-### Summary
-
-One paragraph: what the landscape looks like and the top recommendation.
-
-### Comparison Table
-
-| Tool | License / Price | Last Release | Language | {focus_criteria} Score | Notes |
-|------|----------------|--------------|----------|----------------------|-------|
-| ...  | ...            | ...          | ...      | ...                  | ...   |
-
-### Top Pick
-
-- **Recommended:** {tool_name} — {one-line reason}
-- **Runner-up:** {tool_name} — {one-line reason}
-
-### Risks & Gaps
-
-Bullet list of things to watch out for (missing features, vendor lock-in, etc.).

src/dashboard/routes/tasks.py

@@ -104,29 +104,25 @@ class _TaskView:
 @router.get("/tasks", response_class=HTMLResponse)
 async def tasks_page(request: Request):
     """Render the main task queue page with 3-column layout."""
-    try:
+    with _get_db() as db:
-        with _get_db() as db:
+        pending = [
-            pending = [
+            _TaskView(_row_to_dict(r))
-                _TaskView(_row_to_dict(r))
+            for r in db.execute(
-                for r in db.execute(
+                "SELECT * FROM tasks WHERE status IN ('pending_approval') ORDER BY created_at DESC"
-                    "SELECT * FROM tasks WHERE status IN ('pending_approval') ORDER BY created_at DESC"
+            ).fetchall()
-                ).fetchall()
+        ]
-            ]
+        active = [
-            active = [
+            _TaskView(_row_to_dict(r))
-                _TaskView(_row_to_dict(r))
+            for r in db.execute(
-                for r in db.execute(
+                "SELECT * FROM tasks WHERE status IN ('approved','running','paused') ORDER BY created_at DESC"
-                    "SELECT * FROM tasks WHERE status IN ('approved','running','paused') ORDER BY created_at DESC"
+            ).fetchall()
-                ).fetchall()
+        ]
-            ]
+        completed = [
-            completed = [
+            _TaskView(_row_to_dict(r))
-                _TaskView(_row_to_dict(r))
+            for r in db.execute(
-                for r in db.execute(
+                "SELECT * FROM tasks WHERE status IN ('completed','vetoed','failed') ORDER BY completed_at DESC LIMIT 50"
-                    "SELECT * FROM tasks WHERE status IN ('completed','vetoed','failed') ORDER BY completed_at DESC LIMIT 50"
+            ).fetchall()
-                ).fetchall()
+        ]
-            ]
-    except sqlite3.OperationalError as exc:
-        logger.warning("Task DB unavailable: %s", exc)
-        pending, active, completed = [], [], []
 
     return templates.TemplateResponse(
         request,
@@ -150,14 +146,10 @@ async def tasks_page(request: Request):
 @router.get("/tasks/pending", response_class=HTMLResponse)
 async def tasks_pending(request: Request):
     """Return HTMX partial for pending approval tasks."""
-    try:
+    with _get_db() as db:
-        with _get_db() as db:
+        rows = db.execute(
-            rows = db.execute(
+            "SELECT * FROM tasks WHERE status='pending_approval' ORDER BY created_at DESC"
-                "SELECT * FROM tasks WHERE status='pending_approval' ORDER BY created_at DESC"
+        ).fetchall()
-            ).fetchall()
-    except sqlite3.OperationalError as exc:
-        logger.warning("Task DB unavailable: %s", exc)
-        return HTMLResponse('<div class="empty-column">Database unavailable</div>')
     tasks = [_TaskView(_row_to_dict(r)) for r in rows]
     parts = []
     for task in tasks:
@@ -174,14 +166,10 @@ async def tasks_pending(request: Request):
 @router.get("/tasks/active", response_class=HTMLResponse)
 async def tasks_active(request: Request):
     """Return HTMX partial for active (approved/running/paused) tasks."""
-    try:
+    with _get_db() as db:
-        with _get_db() as db:
+        rows = db.execute(
-            rows = db.execute(
+            "SELECT * FROM tasks WHERE status IN ('approved','running','paused') ORDER BY created_at DESC"
-                "SELECT * FROM tasks WHERE status IN ('approved','running','paused') ORDER BY created_at DESC"
+        ).fetchall()
-            ).fetchall()
-    except sqlite3.OperationalError as exc:
-        logger.warning("Task DB unavailable: %s", exc)
-        return HTMLResponse('<div class="empty-column">Database unavailable</div>')
     tasks = [_TaskView(_row_to_dict(r)) for r in rows]
     parts = []
     for task in tasks:
@@ -198,14 +186,10 @@ async def tasks_active(request: Request):
 @router.get("/tasks/completed", response_class=HTMLResponse)
 async def tasks_completed(request: Request):
     """Return HTMX partial for completed/vetoed/failed tasks (last 50)."""
-    try:
+    with _get_db() as db:
-        with _get_db() as db:
+        rows = db.execute(
-            rows = db.execute(
+            "SELECT * FROM tasks WHERE status IN ('completed','vetoed','failed') ORDER BY completed_at DESC LIMIT 50"
-                "SELECT * FROM tasks WHERE status IN ('completed','vetoed','failed') ORDER BY completed_at DESC LIMIT 50"
+        ).fetchall()
-            ).fetchall()
-    except sqlite3.OperationalError as exc:
-        logger.warning("Task DB unavailable: %s", exc)
-        return HTMLResponse('<div class="empty-column">Database unavailable</div>')
     tasks = [_TaskView(_row_to_dict(r)) for r in rows]
     parts = []
     for task in tasks:
@@ -241,17 +225,13 @@ async def create_task_form(
     now = datetime.now(UTC).isoformat()
     priority = priority if priority in VALID_PRIORITIES else "normal"
 
-    try:
+    with _get_db() as db:
-        with _get_db() as db:
+        db.execute(
-            db.execute(
+            "INSERT INTO tasks (id, title, description, priority, assigned_to, created_at) VALUES (?, ?, ?, ?, ?, ?)",
-                "INSERT INTO tasks (id, title, description, priority, assigned_to, created_at) VALUES (?, ?, ?, ?, ?, ?)",
+            (task_id, title, description, priority, assigned_to, now),
-                (task_id, title, description, priority, assigned_to, now),
+        )
-            )
+        db.commit()
-            db.commit()
+        row = db.execute("SELECT * FROM tasks WHERE id=?", (task_id,)).fetchone()
-            row = db.execute("SELECT * FROM tasks WHERE id=?", (task_id,)).fetchone()
-    except sqlite3.OperationalError as exc:
-        logger.warning("Task DB unavailable: %s", exc)
-        raise HTTPException(status_code=503, detail="Task database unavailable") from exc
 
     task = _TaskView(_row_to_dict(row))
     return templates.TemplateResponse(request, "partials/task_card.html", {"task": task})
@@ -300,17 +280,13 @@ async def modify_task(
     description: str = Form(""),
 ):
     """Update task title and description."""
-    try:
+    with _get_db() as db:
-        with _get_db() as db:
+        db.execute(
-            db.execute(
+            "UPDATE tasks SET title=?, description=? WHERE id=?",
-                "UPDATE tasks SET title=?, description=? WHERE id=?",
+            (title, description, task_id),
-                (title, description, task_id),
+        )
-            )
+        db.commit()
-            db.commit()
+        row = db.execute("SELECT * FROM tasks WHERE id=?", (task_id,)).fetchone()
-            row = db.execute("SELECT * FROM tasks WHERE id=?", (task_id,)).fetchone()
-    except sqlite3.OperationalError as exc:
-        logger.warning("Task DB unavailable: %s", exc)
-        raise HTTPException(status_code=503, detail="Task database unavailable") from exc
     if not row:
         raise HTTPException(404, "Task not found")
     task = _TaskView(_row_to_dict(row))
@@ -322,17 +298,13 @@ async def _set_status(request: Request, task_id: str, new_status: str):
     completed_at = (
         datetime.now(UTC).isoformat() if new_status in ("completed", "vetoed", "failed") else None
     )
-    try:
+    with _get_db() as db:
-        with _get_db() as db:
+        db.execute(
-            db.execute(
+            "UPDATE tasks SET status=?, completed_at=COALESCE(?, completed_at) WHERE id=?",
-                "UPDATE tasks SET status=?, completed_at=COALESCE(?, completed_at) WHERE id=?",
+            (new_status, completed_at, task_id),
-                (new_status, completed_at, task_id),
+        )
-            )
+        db.commit()
-            db.commit()
+        row = db.execute("SELECT * FROM tasks WHERE id=?", (task_id,)).fetchone()
-            row = db.execute("SELECT * FROM tasks WHERE id=?", (task_id,)).fetchone()
-    except sqlite3.OperationalError as exc:
-        logger.warning("Task DB unavailable: %s", exc)
-        raise HTTPException(status_code=503, detail="Task database unavailable") from exc
     if not row:
         raise HTTPException(404, "Task not found")
     task = _TaskView(_row_to_dict(row))
@@ -358,26 +330,22 @@ async def api_create_task(request: Request):
     if priority not in VALID_PRIORITIES:
         priority = "normal"
 
-    try:
+    with _get_db() as db:
-        with _get_db() as db:
+        db.execute(
-            db.execute(
+            "INSERT INTO tasks (id, title, description, priority, assigned_to, created_by, created_at) "
-                "INSERT INTO tasks (id, title, description, priority, assigned_to, created_by, created_at) "
+            "VALUES (?, ?, ?, ?, ?, ?, ?)",
-                "VALUES (?, ?, ?, ?, ?, ?, ?)",
+            (
-                (
+                task_id,
-                    task_id,
+                title,
-                    title,
+                body.get("description", ""),
-                    body.get("description", ""),
+                priority,
-                    priority,
+                body.get("assigned_to", ""),
-                    body.get("assigned_to", ""),
+                body.get("created_by", "operator"),
-                    body.get("created_by", "operator"),
+                now,
-                    now,
+            ),
-                ),
+        )
-            )
+        db.commit()
-            db.commit()
+        row = db.execute("SELECT * FROM tasks WHERE id=?", (task_id,)).fetchone()
-            row = db.execute("SELECT * FROM tasks WHERE id=?", (task_id,)).fetchone()
-    except sqlite3.OperationalError as exc:
-        logger.warning("Task DB unavailable: %s", exc)
-        raise HTTPException(status_code=503, detail="Task database unavailable") from exc
 
     return JSONResponse(_row_to_dict(row), status_code=201)
 
@@ -385,12 +353,8 @@ async def api_create_task(request: Request):
 @router.get("/api/tasks", response_class=JSONResponse)
 async def api_list_tasks():
     """List all tasks as JSON."""
-    try:
+    with _get_db() as db:
-        with _get_db() as db:
+        rows = db.execute("SELECT * FROM tasks ORDER BY created_at DESC").fetchall()
-            rows = db.execute("SELECT * FROM tasks ORDER BY created_at DESC").fetchall()
-    except sqlite3.OperationalError as exc:
-        logger.warning("Task DB unavailable: %s", exc)
-        return JSONResponse([], status_code=200)
     return JSONResponse([_row_to_dict(r) for r in rows])
 
 
@@ -405,17 +369,13 @@ async def api_update_status(task_id: str, request: Request):
     completed_at = (
         datetime.now(UTC).isoformat() if new_status in ("completed", "vetoed", "failed") else None
     )
-    try:
+    with _get_db() as db:
-        with _get_db() as db:
+        db.execute(
-            db.execute(
+            "UPDATE tasks SET status=?, completed_at=COALESCE(?, completed_at) WHERE id=?",
-                "UPDATE tasks SET status=?, completed_at=COALESCE(?, completed_at) WHERE id=?",
+            (new_status, completed_at, task_id),
-                (new_status, completed_at, task_id),
+        )
-            )
+        db.commit()
-            db.commit()
+        row = db.execute("SELECT * FROM tasks WHERE id=?", (task_id,)).fetchone()
-            row = db.execute("SELECT * FROM tasks WHERE id=?", (task_id,)).fetchone()
-    except sqlite3.OperationalError as exc:
-        logger.warning("Task DB unavailable: %s", exc)
-        raise HTTPException(status_code=503, detail="Task database unavailable") from exc
     if not row:
         raise HTTPException(404, "Task not found")
     return JSONResponse(_row_to_dict(row))
@@ -424,13 +384,9 @@ async def api_update_status(task_id: str, request: Request):
 @router.delete("/api/tasks/{task_id}", response_class=JSONResponse)
 async def api_delete_task(task_id: str):
     """Delete a task."""
-    try:
+    with _get_db() as db:
-        with _get_db() as db:
+        cursor = db.execute("DELETE FROM tasks WHERE id=?", (task_id,))
-            cursor = db.execute("DELETE FROM tasks WHERE id=?", (task_id,))
+        db.commit()
-            db.commit()
-    except sqlite3.OperationalError as exc:
-        logger.warning("Task DB unavailable: %s", exc)
-        raise HTTPException(status_code=503, detail="Task database unavailable") from exc
     if cursor.rowcount == 0:
         raise HTTPException(404, "Task not found")
     return JSONResponse({"success": True, "id": task_id})
@@ -444,19 +400,15 @@ async def api_delete_task(task_id: str):
 @router.get("/api/queue/status", response_class=JSONResponse)
 async def queue_status(assigned_to: str = "default"):
     """Return queue status for the chat panel's agent status indicator."""
-    try:
+    with _get_db() as db:
-        with _get_db() as db:
+        running = db.execute(
-            running = db.execute(
+            "SELECT * FROM tasks WHERE status='running' AND assigned_to=? LIMIT 1",
-                "SELECT * FROM tasks WHERE status='running' AND assigned_to=? LIMIT 1",
+            (assigned_to,),
-                (assigned_to,),
+        ).fetchone()
-            ).fetchone()
+        ahead = db.execute(
-            ahead = db.execute(
+            "SELECT COUNT(*) as cnt FROM tasks WHERE status IN ('pending_approval','approved') AND assigned_to=?",
-                "SELECT COUNT(*) as cnt FROM tasks WHERE status IN ('pending_approval','approved') AND assigned_to=?",
+            (assigned_to,),
-                (assigned_to,),
+        ).fetchone()
-            ).fetchone()
-    except sqlite3.OperationalError as exc:
-        logger.warning("Task DB unavailable: %s", exc)
-        return JSONResponse({"is_working": False, "current_task": None, "tasks_ahead": 0})
 
     if running:
         return JSONResponse(

tests/dashboard/test_tasks_api.py

@@ -3,99 +3,6 @@
 Verifies task CRUD operations and the dashboard page rendering.
 """
 
-import sqlite3
-from unittest.mock import patch
-
-
-# ---------------------------------------------------------------------------
-# DB error handling tests
-# ---------------------------------------------------------------------------
-
-_DB_ERROR = sqlite3.OperationalError("database is locked")
-
-
-def test_tasks_page_degrades_on_db_error(client):
-    """GET /tasks renders empty columns when DB is unavailable."""
-    with patch(
-        "dashboard.routes.tasks._get_db",
-        side_effect=_DB_ERROR,
-    ):
-        response = client.get("/tasks")
-    assert response.status_code == 200
-    assert "TASK QUEUE" in response.text
-
-
-def test_pending_partial_degrades_on_db_error(client):
-    """GET /tasks/pending returns fallback HTML when DB is unavailable."""
-    with patch(
-        "dashboard.routes.tasks._get_db",
-        side_effect=_DB_ERROR,
-    ):
-        response = client.get("/tasks/pending")
-    assert response.status_code == 200
-    assert "Database unavailable" in response.text
-
-
-def test_active_partial_degrades_on_db_error(client):
-    """GET /tasks/active returns fallback HTML when DB is unavailable."""
-    with patch(
-        "dashboard.routes.tasks._get_db",
-        side_effect=_DB_ERROR,
-    ):
-        response = client.get("/tasks/active")
-    assert response.status_code == 200
-    assert "Database unavailable" in response.text
-
-
-def test_completed_partial_degrades_on_db_error(client):
-    """GET /tasks/completed returns fallback HTML when DB is unavailable."""
-    with patch(
-        "dashboard.routes.tasks._get_db",
-        side_effect=_DB_ERROR,
-    ):
-        response = client.get("/tasks/completed")
-    assert response.status_code == 200
-    assert "Database unavailable" in response.text
-
-
-def test_api_create_task_503_on_db_error(client):
-    """POST /api/tasks returns 503 when DB is unavailable."""
-    with patch(
-        "dashboard.routes.tasks._get_db",
-        side_effect=_DB_ERROR,
-    ):
-        response = client.post("/api/tasks", json={"title": "Test"})
-    assert response.status_code == 503
-
-
-def test_api_list_tasks_empty_on_db_error(client):
-    """GET /api/tasks returns empty list when DB is unavailable."""
-    with patch(
-        "dashboard.routes.tasks._get_db",
-        side_effect=_DB_ERROR,
-    ):
-        response = client.get("/api/tasks")
-    assert response.status_code == 200
-    assert response.json() == []
-
-
-def test_queue_status_degrades_on_db_error(client):
-    """GET /api/queue/status returns idle status when DB is unavailable."""
-    with patch(
-        "dashboard.routes.tasks._get_db",
-        side_effect=_DB_ERROR,
-    ):
-        response = client.get("/api/queue/status")
-    assert response.status_code == 200
-    data = response.json()
-    assert data["is_working"] is False
-    assert data["current_task"] is None
-
-
-# ---------------------------------------------------------------------------
-# Existing tests
-# ---------------------------------------------------------------------------
-
 
 def test_tasks_page_returns_200(client):
     response = client.get("/tasks")

tests/infrastructure/test_db_pool.py

@@ -242,145 +242,6 @@ class TestCloseAll:
             conn.execute("SELECT 1")
 
 
-class TestConnectionLeaks:
-    """Test that connections do not leak."""
-
-    def test_get_connection_after_close_returns_fresh_connection(self, tmp_path):
-        """After close, get_connection() returns a new working connection."""
-        pool = ConnectionPool(tmp_path / "test.db")
-        conn1 = pool.get_connection()
-        pool.close_connection()
-
-        conn2 = pool.get_connection()
-        assert conn2 is not conn1
-        # New connection must be usable
-        cursor = conn2.execute("SELECT 1")
-        assert cursor.fetchone()[0] == 1
-        pool.close_connection()
-
-    def test_context_manager_does_not_leak_connection(self, tmp_path):
-        """After context manager exit, thread-local conn is cleared."""
-        pool = ConnectionPool(tmp_path / "test.db")
-        with pool.connection():
-            pass
-        # Thread-local should be cleaned up
-        assert pool._local.conn is None
-
-    def test_context_manager_exception_does_not_leak_connection(self, tmp_path):
-        """Connection is cleaned up even when an exception occurs."""
-        pool = ConnectionPool(tmp_path / "test.db")
-        try:
-            with pool.connection():
-                raise RuntimeError("boom")
-        except RuntimeError:
-            pass
-        assert pool._local.conn is None
-
-    def test_threads_do_not_leak_into_each_other(self, tmp_path):
-        """A connection opened in one thread is invisible to another."""
-        pool = ConnectionPool(tmp_path / "test.db")
-        # Open a connection on main thread
-        pool.get_connection()
-
-        visible_from_other_thread = []
-
-        def check():
-            has_conn = hasattr(pool._local, "conn") and pool._local.conn is not None
-            visible_from_other_thread.append(has_conn)
-
-        t = threading.Thread(target=check)
-        t.start()
-        t.join()
-
-        assert visible_from_other_thread == [False]
-        pool.close_connection()
-
-    def test_repeated_open_close_cycles(self, tmp_path):
-        """Repeated open/close cycles do not accumulate leaked connections."""
-        pool = ConnectionPool(tmp_path / "test.db")
-        for _ in range(50):
-            with pool.connection() as conn:
-                conn.execute("SELECT 1")
-            # After each cycle, connection should be cleaned up
-            assert pool._local.conn is None
-
-
-class TestPragmaApplication:
-    """Test that SQLite pragmas can be applied and persist on pooled connections.
-
-    The codebase uses WAL journal mode and busy_timeout pragmas on connections
-    obtained from the pool. These tests verify that pattern works correctly.
-    """
-
-    def test_wal_journal_mode_persists(self, tmp_path):
-        """WAL journal mode set on a pooled connection persists for its lifetime."""
-        pool = ConnectionPool(tmp_path / "test.db")
-        conn = pool.get_connection()
-        conn.execute("PRAGMA journal_mode=WAL")
-        mode = conn.execute("PRAGMA journal_mode").fetchone()[0]
-        assert mode == "wal"
-
-        # Same connection should retain the pragma
-        same_conn = pool.get_connection()
-        mode2 = same_conn.execute("PRAGMA journal_mode").fetchone()[0]
-        assert mode2 == "wal"
-        pool.close_connection()
-
-    def test_busy_timeout_persists(self, tmp_path):
-        """busy_timeout pragma set on a pooled connection persists."""
-        pool = ConnectionPool(tmp_path / "test.db")
-        conn = pool.get_connection()
-        conn.execute("PRAGMA busy_timeout=5000")
-        timeout = conn.execute("PRAGMA busy_timeout").fetchone()[0]
-        assert timeout == 5000
-        pool.close_connection()
-
-    def test_pragmas_apply_per_connection(self, tmp_path):
-        """Pragmas set on one thread's connection are independent of another's."""
-        pool = ConnectionPool(tmp_path / "test.db")
-        conn_main = pool.get_connection()
-        conn_main.execute("PRAGMA cache_size=9999")
-
-        other_cache = []
-
-        def check_pragma():
-            conn = pool.get_connection()
-            # Don't set cache_size — should get the default, not 9999
-            val = conn.execute("PRAGMA cache_size").fetchone()[0]
-            other_cache.append(val)
-            pool.close_connection()
-
-        t = threading.Thread(target=check_pragma)
-        t.start()
-        t.join()
-
-        # Other thread's connection should NOT have our custom cache_size
-        assert other_cache[0] != 9999
-        pool.close_connection()
-
-    def test_session_pragma_resets_on_new_connection(self, tmp_path):
-        """Session-level pragmas (cache_size) reset on a new connection."""
-        pool = ConnectionPool(tmp_path / "test.db")
-        conn1 = pool.get_connection()
-        conn1.execute("PRAGMA cache_size=9999")
-        assert conn1.execute("PRAGMA cache_size").fetchone()[0] == 9999
-        pool.close_connection()
-
-        conn2 = pool.get_connection()
-        cache = conn2.execute("PRAGMA cache_size").fetchone()[0]
-        # New connection gets default cache_size, not the previous value
-        assert cache != 9999
-        pool.close_connection()
-
-    def test_wal_mode_via_context_manager(self, tmp_path):
-        """WAL mode can be set within a context manager block."""
-        pool = ConnectionPool(tmp_path / "test.db")
-        with pool.connection() as conn:
-            conn.execute("PRAGMA journal_mode=WAL")
-            mode = conn.execute("PRAGMA journal_mode").fetchone()[0]
-            assert mode == "wal"
-
-
 class TestIntegration:
     """Integration tests for real-world usage patterns."""