Content Pipeline v2 — Final Implementation Document

CREATE TABLE content_briefs_v2 (
    id TEXT PRIMARY KEY,
    title TEXT NOT NULL,
    struggle_angle TEXT NOT NULL,
    origin_story TEXT NOT NULL,
    attempts JSON NOT NULL,  -- array of {attempt, why_failed}
    the_moment TEXT NOT NULL,
    the_fix TEXT NOT NULL,
    reflection TEXT NOT NULL,
    voice_checklist JSON NOT NULL,  -- boolean map
    status TEXT CHECK(status IN ('draft', 'pending', 'approved', 'rejected', 'generating', 'completed')),
    style_reference TEXT,
    target_length INTEGER,
    created_by TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    approved_at TIMESTAMP,
    approved_by TEXT,
    generation_job_id TEXT,
    struggle_score REAL,
    content_output TEXT
);

def calculate_struggle_score(content: str) -> float:
    checks = {
        "i_statement_ratio": len(re.findall(r"^I\s", paragraphs)) / len(paragraphs),
        "temporal_markers": len(re.findall(r"\d{1,2}:\d{2}|yesterday|last night", content)),
        "struggle_patterns": len(re.findall(r"I thought.*but|I tried.*failed|didn't work", content)),
        "cost_mentions": len(re.findall(r"hour|minute|sleep|wife|kid|Aundrea|frustrated", content)),
        "admission_count": len(re.findall(r"I was wrong|I didn't expect|should have", content))
    }

    weights = {
        "i_statement_ratio": 0.30,
        "temporal_markers": 0.15,
        "struggle_patterns": 0.25,
        "cost_mentions": 0.20,
        "admission_count": 0.10
    }

    score = sum(checks[k] * weights[k] for k in weights)
    return min(score * 100, 100)  # Cap at 100

{
  "slug": "dns-night",
  "title": "The Night I Broke DNS and My Wife Couldn't Reach Facebook",
  "excerpt": "2 AM. Aundrea's phone wasn't loading Instagram...",
  "structure": {
    "hook": { "type": "timestamp_location_crisis", "content": "..." },
    "struggle": { "attempts": 2, "escalation": "..." },
    "moment": { "realization": "..." },
    "fix": { "solution": "...", "caveats": "..." },
    "reflection": { "lesson": "..." }
  },
  "voice_markers": {
    "i_statements": 12,
    "quotes": ["Aundrea said...", "I thought..."],
    "cost_mentions": ["2 AM", "wife acceptance factor"]
  }
}

@router.post("/admin/content/generate")
async def generate_content(
    request: GenerateRequest,
    brief_id: Optional[str] = None,  # NEW: v2 uses brief_id
    version: str = Query("v1", description="API version"),
):
    if request.content_type == "struggle_first" or version == "v2":
        return await generate_v2(request, brief_id)
    return await generate_v1(request)

USER (Daedalus/Matt)                     BACKEND (Socrates)
│                                          │
├─ Opens Magic Wand ──────────────────────►│
│  (new "Struggle-First" tab)              │
│                                          │
├─ Fills StruggleBriefForm ───────────────►│
│  ├─ What broke?                          │
│  ├─ Who was affected?                    │
│  ├─ Attempt 1 + why failed               │
│  ├─ Attempt 2 + why failed               │
│  ├─ The moment (realization)             │
│  ├─ The fix + caveats                    │
│  ├─ What you'd do differently            │
│  └─ Style reference                      │
│                                          │
├─ Completes VoiceChecklist ──────────────►│
│  (all 6 items checked)                   │
│                                          │
├─ Clicks "Submit for Review" ────────────►├─ POST /api/v1/content/briefs
│                                          │  ├─ Validate fields
│                                          │  ├─ Save to SQLite
│                                          │  └─ status = "pending"
│                                          │
│◄─ Shows "Brief submitted" confirmation ──┤
│                                          │
│                                          ├─ Telegram DM to Matt
│                                          │  "New brief: [title]
│                                          │   Approve? [Yes] [No] [Edit]"
│                                          │
MATT (in Telegram DM)                      │
│                                          │
├─ Clicks [Yes] ─────────────────────────►├─ POST /api/v1/content/briefs/{id}/approve
│                                          │  ├─ status = "approved"
│                                          │  ├─ Trigger generation
│                                          │  └─ WebSocket/SSE: "generating"
│                                          │
│◄─ "Generation started" notification ─────┤
│                                          │
│                                          ├─ Load style example
│                                          ├─ Build v2 prompt
│                                          ├─ Call phi4:14b (local)
│                                          ├─ Calculate struggle_score
│                                          ├─ Save output
│                                          └─ status = "completed"
│                                          │
│◄─ Notification: "Content ready" ─────────┤
│                                          │
├─ Opens ContentPreview ──────────────────►├─ GET /api/v1/content/briefs/{id}/output
│  ├─ Left: Brief (highlighted sections)   │
│  ├─ Right: Generated content             │
│  ├─ Struggle score bar chart             │
│  └─ [Edit Brief] [Accept] [Regenerate]   │
│                                          │
├─ Clicks [Accept] ───────────────────────►├─ Create post from output
│                                          │  ├─ Save to database
│                                          │  └─ Trigger static build
│◄─ "Published to notes.hoffdesk.com" ───┤

{
  "colors": {
    "struggle": {
      "high": "#22c55e",
      "medium": "#eab308",
      "low": "#ef4444"
    },
    "validation": {
      "pass": "#22c55e",
      "fail": "#ef4444",
      "pending": "#6b7280",
      "complete": "#3b82f6"
    },
    "section": {
      "active": "#f8fafc",
      "inactive": "#ffffff",
      "border": "#e2e8f0"
    }
  },
  "spacing": {
    "brief-section-gap": "1.5rem",
    "progress-sticky-top": "1rem",
    "preview-pane-gap": "1rem"
  },
  "preview": {
    "pane-width": "50%",
    "min-width": "320px",
    "max-content-width": "720px"
  },
  "animation": {
    "check-pop": "200ms cubic-bezier(0.34, 1.56, 0.64, 1)",
    "section-collapse": "300ms ease-out",
    "button-pulse": "1.5s ease-in-out infinite"
  }
}