phase: 04-channel-crud-api
plan: 01
type: execute
wave: 1
depends_on: []
files_modified:
- schema.sql
- migrate_category.sql
- api.py
- smoke_test.sh
autonomous: true
requirements: [CH-01, CH-02, CH-03, CH-04]
must_haves:
truths:
- "POST /channels with name+category returns 201 with server-generated slug id"
- "GET /channels returns all channels sorted by name with id, name, category fields"
- "POST /channels with duplicate name returns 409 (not 500)"
- "POST /admin/reset?channel_id=X deletes only that channel's chunks"
- "POST /admin/reset without channel_id performs global reset (backward-compatible)"
- "POST /ingest with non-existent channel_id returns 404"
artifacts:
- path: "schema.sql"
provides: "channels table with category column"
contains: "category TEXT"
- path: "migrate_category.sql"
provides: "Idempotent migration for existing DBs"
contains: "ALTER TABLE channels ADD COLUMN IF NOT EXISTS category TEXT"
- path: "api.py"
provides: "Channel CRUD routes, scoped reset, ingest guard"
exports: ["POST /channels", "GET /channels", "POST /admin/reset", "POST /ingest"]
- path: "smoke_test.sh"
provides: "Integration tests for CH-01 through CH-04"
contains: "CH-01"
key_links:
- from: "api.py POST /channels"
to: "channels table"
via: "INSERT INTO channels (id, name, category)"
pattern: "INSERT INTO channels"
- from: "api.py GET /channels"
to: "channels table"
via: "SELECT id, name, category FROM channels ORDER BY name ASC"
pattern: "ORDER BY name ASC"
- from: "api.py POST /admin/reset"
to: "chunks table"
via: "DELETE FROM chunks WHERE channel_id = %s (scoped) or DELETE FROM chunks (global)"
pattern: "channel_id.*Query"
- from: "api.py POST /ingest"
to: "channels table"
via: "SELECT id FROM channels WHERE id = %s (existence check)"
pattern: "SELECT id FROM channels WHERE id"
Add Channel CRUD API endpoints and extend existing routes with channel-scoping.
Purpose: Enable channel creation, listing, scoped reset, and channel-existence validation -- the API layer Phase 5 UI will consume.
Output: Updated api.py with 2 new routes (POST /channels, GET /channels), modified POST /admin/reset and POST /ingest, updated schema.sql with category column, migration file, and smoke tests.
@$HOME/.claude/get-shit-done/workflows/execute-plan.md
@$HOME/.claude/get-shit-done/templates/summary.md
@.planning/PROJECT.md
@.planning/ROADMAP.md
@.planning/STATE.md
@.planning/phases/04-channel-crud-api/04-CONTEXT.md
@.planning/phases/04-channel-crud-api/04-RESEARCH.md
From api.py (current Pydantic models):
class IngestRequest(BaseModel):
filename: str
channel_id: str = "general"
class IngestResponse(BaseModel):
inserted: int
source: str
class QueryRequest(BaseModel):
question: str
channel_id: str = "general"
class QueryResponse(BaseModel):
answer: str
class ResetResponse(BaseModel):
deleted: int
message: str
From api.py (current imports):
import os
from pathlib import Path
import anthropic
import httpx
import psycopg2
from dotenv import load_dotenv
from fastapi import FastAPI, HTTPException
from fastapi.responses import FileResponse
from fastapi.staticfiles import StaticFiles
from pydantic import BaseModel
from ingest import ingest_file
from query import ask
From api.py (current admin_reset, lines 93-106):
@app.post("/admin/reset", response_model=ResetResponse)
def admin_reset():
try:
conn = psycopg2.connect(DB_URL)
cur = conn.cursor()
cur.execute("SELECT COUNT(*) FROM chunks")
count = cur.fetchone()[0]
cur.execute("DELETE FROM chunks")
conn.commit()
cur.close()
conn.close()
return ResetResponse(deleted=count, message=f"{count} Chunks geloescht.")
except Exception as e:
raise HTTPException(status_code=500, detail=f"Reset fehlgeschlagen: {e}")
From api.py (current ingest handler, lines 70-79):
@app.post("/ingest", response_model=IngestResponse)
def ingest(req: IngestRequest):
path = Path(req.filename)
if not path.exists():
raise HTTPException(status_code=404, detail=f"Datei nicht gefunden: {req.filename}")
try:
inserted = ingest_file(str(path), channel_id=req.channel_id)
except Exception as e:
raise HTTPException(status_code=500, detail=str(e))
return IngestResponse(inserted=inserted, source=path.name)
From schema.sql (current channels table):
CREATE TABLE IF NOT EXISTS channels (
id TEXT PRIMARY KEY,
name TEXT NOT NULL UNIQUE
);
INSERT INTO channels (id, name) VALUES ('general', 'General')
ON CONFLICT (id) DO NOTHING;
- schema.sql (current channels table definition -- must see current state before modifying)
- api.py (current imports and Pydantic models -- must see existing patterns)
- .planning/phases/04-channel-crud-api/04-CONTEXT.md (locked decisions D-07, D-12)
schema.sql -- Add category TEXT column to the channels CREATE TABLE statement (per D-07, D-12):
Change the channels table definition from:
```sql
CREATE TABLE IF NOT EXISTS channels (
id TEXT PRIMARY KEY,
name TEXT NOT NULL UNIQUE
);
```
To:
```sql
CREATE TABLE IF NOT EXISTS channels (
id TEXT PRIMARY KEY,
name TEXT NOT NULL UNIQUE,
category TEXT
);
```
No NOT NULL constraint, no DEFAULT. Leave the INSERT seed and all other tables unchanged.
**migrate_category.sql** -- Create new file in project root with idempotent migration:
```sql
-- Phase 4: Add category column to channels table
ALTER TABLE channels ADD COLUMN IF NOT EXISTS category TEXT;
```
One line, idempotent via IF NOT EXISTS (same pattern as Phase 3 migrate_channels.sql).
**api.py** -- Add two new imports at the top of the file:
```python
import re
import psycopg2.errors
```
Add `re` after `import os` (stdlib block). Add `import psycopg2.errors` directly after `import psycopg2`.
Also add `Query` to the fastapi import line:
```python
from fastapi import FastAPI, HTTPException, Query
```
Add `typing.List` import:
```python
from typing import List
```
Add two new Pydantic models after the existing `ResetResponse` class (before the Routes section):
```python
class ChannelCreate(BaseModel):
name: str
category: str | None = None
class ChannelResponse(BaseModel):
id: str
name: str
category: str | None = None
```
- schema.sql contains category TEXT inside the channels CREATE TABLE block
- migrate_category.sql exists and contains ALTER TABLE channels ADD COLUMN IF NOT EXISTS category TEXT
- api.py contains import re (stdlib)
- api.py contains import psycopg2.errors
- api.py fastapi import line contains Query (i.e. from fastapi import FastAPI, HTTPException, Query)
- api.py contains class ChannelCreate(BaseModel): with fields name: str and category: str | None = None
- api.py contains class ChannelResponse(BaseModel): with fields id: str, name: str, category: str | None = None
- api.py passes python3 -c "import ast; ast.parse(open('api.py').read())"
- api.py (must read current state after Task 1 modifications)
- .planning/phases/04-channel-crud-api/04-CONTEXT.md (D-01 through D-06, D-10, D-11)
- .planning/phases/04-channel-crud-api/04-RESEARCH.md (Pattern 1: slug derivation, Pattern 2: UniqueViolation ordering)
Add a helper function and two new route handlers to api.py. Place the helper function after the Pydantic models section and before the Routes section. Place the routes after the existing root() route and before the ingest() route.
**Helper function** (per D-01, D-02):
```python
def _derive_slug(name: str) -> str:
"""Lowercase name, replace spaces with hyphens."""
return name.lower().replace(" ", "-")
```
**POST /channels** (per D-01, D-02, D-03, D-04, D-05, D-10, D-11):
```python
@app.post("/channels", response_model=ChannelResponse, status_code=201)
def create_channel(req: ChannelCreate):
slug = _derive_slug(req.name)
if not re.fullmatch(r'^[a-z0-9_-]+$', slug):
raise HTTPException(
status_code=422,
detail="Channel name produces invalid slug. Use only letters, numbers, spaces, hyphens, underscores."
)
try:
conn = psycopg2.connect(DB_URL)
cur = conn.cursor()
cur.execute(
"INSERT INTO channels (id, name, category) VALUES (%s, %s, %s)",
(slug, req.name, req.category),
)
conn.commit()
cur.close()
conn.close()
return ChannelResponse(id=slug, name=req.name, category=req.category)
except psycopg2.errors.UniqueViolation:
raise HTTPException(status_code=409, detail="Channel name already exists.")
except Exception as e:
raise HTTPException(status_code=500, detail=str(e))
```
CRITICAL: `except psycopg2.errors.UniqueViolation` MUST appear BEFORE `except Exception`. This is non-negotiable (D-10, CH-03).
CRITICAL: Use `re.fullmatch` (NOT `re.match`) for slug validation. `re.match` only anchors at start and would accept trailing invalid characters.
**GET /channels** (per D-06):
```python
@app.get("/channels", response_model=List[ChannelResponse])
def list_channels():
try:
conn = psycopg2.connect(DB_URL)
cur = conn.cursor()
cur.execute("SELECT id, name, category FROM channels ORDER BY name ASC")
rows = cur.fetchall()
cur.close()
conn.close()
return [ChannelResponse(id=r[0], name=r[1], category=r[2]) for r in rows]
except Exception as e:
raise HTTPException(status_code=500, detail=str(e))
```
Sort is `ORDER BY name ASC` -- no pagination needed (small dataset, per D-06).
- api.py contains def _derive_slug(name: str) -> str: with body return name.lower().replace(" ", "-")
- api.py contains @app.post("/channels", response_model=ChannelResponse, status_code=201)
- api.py contains re.fullmatch(r'^[a-z0-9_-]+$', slug) (NOT re.match)
- api.py contains except psycopg2.errors.UniqueViolation: appearing BEFORE except Exception in the create_channel function
- api.py contains raise HTTPException(status_code=409, detail="Channel name already exists.")
- api.py contains raise HTTPException(status_code=422, detail="Channel name produces invalid slug...
- api.py contains @app.get("/channels", response_model=List[ChannelResponse])
- api.py contains SELECT id, name, category FROM channels ORDER BY name ASC
- api.py passes syntax check
- api.py (must read current state after Task 2 modifications -- especially the admin_reset and ingest functions)
- smoke_test.sh (current test structure and check() function pattern)
- .planning/phases/04-channel-crud-api/04-CONTEXT.md (D-08, D-09)
- .planning/phases/04-channel-crud-api/04-RESEARCH.md (Pattern 3: Query parameter, Pattern 4: channel-existence check)
api.py -- Replace the existing admin_reset function (per D-08):
Replace the entire function (currently lines ~93-106) with:
```python
@app.post("/admin/reset", response_model=ResetResponse)
def admin_reset(channel_id: str | None = Query(default=None)):
try:
conn = psycopg2.connect(DB_URL)
cur = conn.cursor()
if channel_id:
cur.execute("SELECT COUNT(*) FROM chunks WHERE channel_id = %s", (channel_id,))
count = cur.fetchone()[0]
cur.execute("DELETE FROM chunks WHERE channel_id = %s", (channel_id,))
message = f"{count} Chunks fuer Channel '{channel_id}' geloescht."
else:
cur.execute("SELECT COUNT(*) FROM chunks")
count = cur.fetchone()[0]
cur.execute("DELETE FROM chunks")
cur.execute("DELETE FROM channels WHERE id != 'general'")
message = f"{count} Chunks geloescht (globaler Reset)."
conn.commit()
cur.close()
conn.close()
return ResetResponse(deleted=count, message=message)
except Exception as e:
raise HTTPException(status_code=500, detail=f"Reset fehlgeschlagen: {e}")
```
Key changes from original:
- Signature adds `channel_id: str | None = Query(default=None)`
- With channel_id: DELETE WHERE channel_id = %s (scoped)
- Without channel_id: DELETE all chunks + DELETE channels WHERE id != 'general' (preserves general seed)
**api.py -- Replace the existing `ingest` function** (per D-09):
Replace the entire function with:
```python
@app.post("/ingest", response_model=IngestResponse)
def ingest(req: IngestRequest):
path = Path(req.filename)
if not path.exists():
raise HTTPException(status_code=404, detail=f"Datei nicht gefunden: {req.filename}")
# Channel-existence check (D-09)
try:
conn = psycopg2.connect(DB_URL)
cur = conn.cursor()
cur.execute("SELECT id FROM channels WHERE id = %s", (req.channel_id,))
if cur.fetchone() is None:
cur.close()
conn.close()
raise HTTPException(status_code=404, detail=f"Channel nicht gefunden: {req.channel_id}")
cur.close()
conn.close()
except HTTPException:
raise
except Exception as e:
raise HTTPException(status_code=500, detail=str(e))
# Existing ingest call
try:
inserted = ingest_file(str(path), channel_id=req.channel_id)
except Exception as e:
raise HTTPException(status_code=500, detail=str(e))
return IngestResponse(inserted=inserted, source=path.name)
```
CRITICAL: `except HTTPException: raise` MUST appear before `except Exception` in the channel-existence try block. Otherwise the 404 HTTPException gets caught by `except Exception` and becomes a 500.
**smoke_test.sh -- Add Phase 4 tests** after the existing QRY check (before the Results summary):
Add these checks using the existing `check()` function pattern:
```bash
# ── Phase 4: Channel CRUD ──────────────────────────────────────────────────
# CH-01: Create channel returns 201
check "CH-01: Create channel" bash -c \
"curl -sf -o /dev/null -w '%{http_code}' -X POST $BASE/channels -H 'Content-Type: application/json' -d '{\"name\":\"Smoke Test Channel\",\"category\":\"test\"}' | grep -q 201"
# CH-01: Create channel returns id field
check "CH-01: Channel has id" bash -c \
"curl -sf $BASE/channels | grep -q smoke-test-channel"
# CH-02: List channels returns array with id, name, category
check "CH-02: List channels" bash -c \
"curl -sf $BASE/channels | grep -q general"
# CH-03: Duplicate channel returns 409
check "CH-03: Duplicate returns 409" bash -c \
"curl -s -o /dev/null -w '%{http_code}' -X POST $BASE/channels -H 'Content-Type: application/json' -d '{\"name\":\"Smoke Test Channel\"}' | grep -q 409"
# CH-04: Scoped reset deletes only target channel chunks
check "CH-04: Scoped reset" bash -c \
"curl -sf -X POST '$BASE/admin/reset?channel_id=smoke-test-channel' | grep -q geloescht"
# CH-04: Global reset backward-compat (no channel_id param)
check "CH-04: Global reset" bash -c \
"curl -sf -X POST $BASE/admin/reset | grep -q geloescht"
```
Place these BEFORE the final `echo ""` / `echo "=== Results..."` lines.
- api.py admin_reset signature contains channel_id: str | None = Query(default=None)
- api.py contains DELETE FROM chunks WHERE channel_id = %s (scoped delete)
- api.py contains DELETE FROM channels WHERE id != 'general' (global reset preserves general)
- api.py ingest function contains SELECT id FROM channels WHERE id = %s (existence check)
- api.py ingest function contains except HTTPException: followed by raise BEFORE except Exception
- api.py ingest function contains Channel nicht gefunden in a 404 HTTPException detail
- smoke_test.sh contains checks labeled CH-01, CH-02, CH-03, CH-04
- smoke_test.sh CH-03 check verifies 409 status code
- api.py passes syntax check
1. python3 -c "import ast; ast.parse(open('api.py').read()); print('syntax ok')" -- api.py is valid Python
2. grep -c "category TEXT" schema.sql -- returns 1 (category column present)
3. cat migrate_category.sql -- contains ALTER TABLE with IF NOT EXISTS
4. grep "UniqueViolation" api.py -- exception handler present
5. grep "re.fullmatch" api.py -- correct slug validation function used
6. grep "CH-0" smoke_test.sh -- all four requirement checks present
7. Against running stack: bash smoke_test.sh -- all checks pass
- POST /channels with {"name": "Nginx Errors", "category": "web"} returns 201 with {"id": "nginx-errors", "name": "Nginx Errors", "category": "web"}
- POST /channels with same name returns 409
- POST /channels with {"name": "Ueberwachung!"} returns 422
- GET /channels returns list sorted by name with id, name, category fields
- POST /admin/reset?channel_id=X deletes only chunks for channel X
- POST /admin/reset (no param) deletes all chunks and all channels except general
- POST /ingest with non-existent channel_id returns 404
- All smoke_test.sh checks pass