Add design spec: automated Langflow project-skill matching with CRM entry

Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
2026-07-09 07:49:32 +02:00
parent da35fc0e12
commit 1981f5c90b
1 changed files with 298 additions and 0 deletions
--- a/docs/superpowers/specs/2026-07-09-langflow-projekt-matching-design.md
+++ b/docs/superpowers/specs/2026-07-09-langflow-projekt-matching-design.md
@@ -0,0 +1,298 @@
+# Design: Automated project–skill matching in Langflow with CRM entry
+
+Date: 2026-07-09
+Status: approved section by section in brainstorming; pending final spec review
+
+## 1. Goal
+
+Convert the interactive Claude skill `projekt-anlegen`
+(`/home/tlg/mkt/bewerb/.claude/skills/projekt-anlegen/SKILL.md`) into a fully
+automated Langflow workflow:
+
+- Triggered by new e-mails with freelance project descriptions arriving at
+  `chancen@destengs.com`.
+- Uses ONLY the local AI at the vLLM OpenAI-compatible endpoint
+  `http://127.0.0.1:8081/v1` (model `AxionML/Qwen3.5-9B-NVFP4`).
+- No human review pause (the skill's Schritt 6 is removed; ambiguities are
+  resolved by defaults, see §5.2).
+- Projects with `Must-have-Match` > 85 % are written to EspoCRM (full
+  replication: Account, Contact, Opportunity) and announced by notification
+  e-mail; all others are rejected silently (trace only).
+- Every run is traced in Langfuse for manual quality annotation.
+
+### Infrastructure (existing)
+
+| Service | Address | Notes |
+|---|---|---|
+| Langflow | `http://127.0.0.1:8090` | rootless podman pod `langflow_pod` |
+| Langfuse | `http://127.0.0.1:8091` | pod-internal: `http://langflow_pod:3000` |
+| vLLM | `http://127.0.0.1:8081/v1` | from pod: `http://host.containers.internal:8081/v1`; reasoning model, 65k context |
+| EspoCRM | `https://crm.creature-go.com` | API creds in `/home/tlg/mkt/bewerb/.secrets/espocrm-api.md` |
+| Mail | `mail.destengs.com` | IMAP 993/SSL, SMTP 587/STARTTLS, account `chancen@destengs.com` |
+
+Reference inputs (read-only): `vorgaben/Lebenslauf_Dr-Ing_Thomas_Langer.md`,
+`vorgaben/rahmenbedingungen.md` under `/home/tlg/mkt/bewerb/`.
+
+## 2. Decisions made during brainstorming
+
+| Topic | Decision |
+|---|---|
+| CRM scope | Full replication of the skill: Account (dedup/create, Reseller/Customer) + Contact (dedup/create) + Opportunity (team, agency/direct linking) |
+| Duplicate key | Canonical project URL (redirect followed, query stripped), stored in and matched against `cProjektlink` |
+| Langfuse evaluation | Tracing + manual annotation (score configs + annotation queue); no LLM-as-judge |
+| Mailbox | Only ONE address exists: `chancen@destengs.com` ("chances@" in the original brief was a typo). Design-phase notifications go there; production notifications to `Thomas.Langer@destengs.com` |
+| Trigger cadence | Poll every 5 minutes |
+| Non-project mail | Left untouched in the Inbox, marked with an IMAP keyword so it is inspected only once |
+| Retry policy | No cross-cycle retries for processing errors: every project reaches a terminal state on first attempt; trigger mail is then ALWAYS moved to Trash; failures produce an aggregated alert mail. Only Flow-1-level failures (IMAP down, lock held) wait for the next cycle |
+| Architecture | A: two Langflow flows (Ingest + Per-Project) + systemd timer webhook trigger |
+| Fetch failure | No snippet fallback: alert mail + terminal state `failed`, nothing written to CRM |
+| max_tokens | Effectively unbounded (no ~4k cap); reasoning model may need long chains |
+| Notification subject | `[Projekt-Match] Freelancermap — <Must> % — <Name>` |
+| Alert subject | `[Projekt-Match-Fehler] Freelancermap — <n> Projekt(e)` |
+
+## 3. Architecture overview
+
+```
+systemd user timer (5 min)
+      │ curl POST /api/v1/webhook/<flow1_id>   (returns immediately)
+      ▼
+┌─ Flow 1: INGEST ───────────────────────────────┐
+│ lockfile guard (skip run if another is active) │
+│ IMAP poll: INBOX without keyword $ProjektChecked│
+│ → project-mail detection (deterministic)       │
+│ → split mail into project items                │
+│ → canonical URL per item (follow redirects)    │
+│ → CRM dedup: Opportunity.cProjektlink equals   │
+│ → for each NEW project: run Flow 2 via API ────┼──┐
+│ → aggregate results                            │  │
+│ → failures? ONE alert mail per trigger mail    │  │
+│ → all terminal? move trigger mail → Trash      │  │
+│ → non-project mail: flag $ProjektChecked, keep │  │
+└────────────────────────────────────────────────┘  │
+┌─ Flow 2: PER-PROJECT ──────────────────────────┐ ◀┘
+│ fetch page text → LLM extract → LLM CV-match   │
+│ → deterministic Misc rules + match calculation │
+│ → gate Must-have-Match > 85 %                  │
+│ → yes: CRM writes + notification mail          │
+│ → no: end (trace only)                         │
+│ returns {status, error?, opportunityId?, …}    │
+└────────────────────────────────────────────────┘
+```
+
+Control flow (loops, conditionals) lives in custom Python components inside
+the flows; the systemd timer carries no logic. One Flow 2 run per project ⇒
+one Langfuse trace per project.
+
+## 4. Flow 1: Ingest
+
+1. **Lock.** Acquire a lockfile in the Langflow data dir. If held: exit
+   immediately (a previous run is still working; state lives in the inbox, so
+   nothing is lost — worst case a mail starts ~10 min after arrival instead
+   of 5).
+2. **IMAP poll.** `mail.destengs.com:993` SSL, login `chancen@destengs.com`.
+   Select INBOX; fetch messages NOT carrying custom IMAP keyword
+   `$ProjektChecked`.
+3. **Project-mail detection** — deterministic, no LLM:
+   - Own workflow mails (subject starts `[Projekt-Match]` or
+     `[Projekt-Match-Fehler]`) → skip, flag `$ProjektChecked`.
+   - Mail contains freelancermap project URLs (`/nproj/…` or `/projekt/…`)
+     → project mail.
+   - Anything else → flag `$ProjektChecked`, leave untouched in Inbox.
+4. **Split.** Parse body (HTML preferred, else plain text); extract each
+   project item: title + tracking URL. (Example 1 yields 5 items.)
+5. **Canonical URL.** GET each tracking URL following redirects; strip query
+   string → e.g. `https://www.freelancermap.de/projekt/python-entwickler-fuer-eine-ki-anwendung-m-w-d`.
+   Resolution failure → item is terminal `failed` with reason.
+6. **CRM dedup.** `GET /Opportunity where cProjektlink equals <canonical>`;
+   hit → terminal `duplicate`.
+7. **Dispatch.** For each remaining project:
+   `POST /api/v1/run/<flow2_id>` (header `x-api-key`) with canonical URL and
+   title. Await result: `created` / `rejected` / `failed` (+ error reason,
+   opportunity ID, match values).
+8. **Alerting.** If ≥ 1 project ended `failed`: send ONE alert mail per
+   trigger mail — subject `[Projekt-Match-Fehler] Freelancermap — <n>
+   Projekt(e)`, body listing each failed project (title, URL, error reason).
+   All alerting lives here; Flow 2 only returns status.
+9. **Finalize.** All items terminal (`created`/`rejected`/`duplicate`/
+   `failed`) — which is always true at this point — → move trigger mail to
+   Trash (IMAP MOVE, fallback COPY+\Deleted+EXPUNGE). Release lock.
+
+## 5. Flow 2: Per-Project
+
+Input: canonical URL, title. Output: `{status, error?, opportunityId?,
+mustMatch?, niceMatch?, projectName?}`.
+
+### 5.1 Fetch
+
+GET canonical URL, extract readable text. 2–3 in-run retries with short
+backoff for transient HTTP errors. Still failing (or login wall / empty
+text) → return `failed` with reason; NO fallback to e-mail snippet text (a
+snippet cannot support a reasonable requirements matching).
+
+### 5.2 LLM call 1 — Extraction
+
+vLLM chat completion, `guided_json` schema enforcement, no restrictive
+`max_tokens` (leave unset / remaining-context; reasoning chains may be
+long). Output JSON:
+
+- `projectName` (without portal suffix),
+- `requirements[]`: verbatim text (careful shortening allowed, meaning never
+  changed) + category `Must`/`Nice`/`Misc` — explicit section headings
+  outrank signal words; Rahmenbedingungen (start, location/remote, workload,
+  duration) are Misc,
+- `offerType`: `Projekt` / `Arbeitnehmer-Angebot` / `ANÜ` (→ team
+  `DesTEngS` / `Arbeitnehmer` / `ANÜ`),
+- `buyerType`: agency / direct,
+- `companyName`, `contactPerson` (nullable).
+
+Prompt includes few-shot examples. Defaults replace the skill's "ask in
+review": ambiguous `offerType` → `Projekt`; ambiguous `buyerType` → agency.
+Schema-invalid output after retry → `failed`.
+
+### 5.3 LLM call 2 — CV matching
+
+One call: full CV markdown + all Must/Nice requirements → per requirement
+`✅`/`❌`/`❔` + one-line justification. Prompt carries the skill's strict
+calibration (✅ only on clear CV evidence; PoC ≠ production operation;
+"mehrjährig" taken literally; product name ≠ platform experience; "wie
+z. B."-lists: equivalent alternatives count; ❔ only for genuine partial
+evidence). Misc items are NOT sent to the LLM.
+
+### 5.4 Deterministic rules (pure Python — numbers never come from the LLM)
+
+- **Misc evaluation** (from `rahmenbedingungen.md`, same rules as skill
+  Schritt 3b): Start ≤ today + 56 days → ✅ else ❌, missing → ❔; workload
+  75–100 % → ✅, < 75 % or missing → ❔; duration always ✅; 100 % remote →
+  ✅, hybrid/onsite → Nominatim geocode + Haversine distance to Sauerlach
+  (47.9721 N, 11.6528 E): ≤ 50 km ✅, 50–60 km ❔, > 60 km ❌, unclear → ❌,
+  geocode failure → ❔ (NOT a run failure); Sicherheitsüberprüfung demanded →
+  always ❌.
+- **Match computation**: mean over Must rows with ✅=100, ❔=25, ❌=0; same
+  for Nice; Misc never enters a match; round to whole percent; empty
+  category → `–`.
+- **Description markdown** in the skill's exact Schritt-5 format: match line
+  (`Must-have-Match: X % · Nice-to-have-Match: Y %`), table
+  `| Nr. | Kat. | ❔ | Anforderung |`, order Must → Nice → Misc, numbering
+  from 1.
+
+### 5.5 Gate
+
+`Must-have-Match > 85` → CRM branch. Otherwise: terminal `rejected` — no CRM
+write, no mail; matches/table/decision recorded in the Langfuse trace.
+
+### 5.6 CRM writes (EspoCRM API, skill Schritt 7 replicated)
+
+1. Team-ID by name lookup (from offerType); never guessed; lookup empty →
+   `failed`.
+2. Account dedup (`contains` on a core name token) else create
+   (`type`: `Reseller` for agency, `Customer` for direct).
+3. Contact dedup (last name) else create with `accountId` (last token =
+   lastName, rest = firstName). No contact person named → Opportunity
+   created WITHOUT contact (automation simplification).
+4. Opportunity name-duplicate check (`startsWith`), suffix ` (2)`, ` (3)`…;
+   create with `description` markdown, `cProjektlink` = canonical URL,
+   `teamsIds`, and agency → `cAccount1Id` (no `accountId`) / direct →
+   `accountId` (no `cAccount1Id`).
+5. Verify read-back: name, link, description intact (match line + all rows),
+   linking correct, team correct. Mismatch → `failed`.
+
+CRM errors get 2–3 in-run retries (transient); persistent error → `failed`.
+Partial writes heal on a later attempt because Account/Contact/Opportunity
+creation are each dedup-guarded.
+
+### 5.7 Notification mail
+
+SMTP `mail.destengs.com:587` STARTTLS. Subject:
+`[Projekt-Match] Freelancermap — <Must> % — <Name>`. Body: short note, both
+match values, direct link
+`https://crm.creature-go.com/#Opportunity/view/<id>`. Recipient from config
+variable: design phase `chancen@destengs.com`, after gates 1+2 pass
+`Thomas.Langer@destengs.com`.
+
+## 6. Langfuse monitoring
+
+- **Integration**: Langflow-native via env vars on the Langflow container:
+  `LANGFUSE_PUBLIC_KEY`, `LANGFUSE_SECRET_KEY`,
+  `LANGFUSE_HOST=http://langflow_pod:3000` (pod-internal address —
+  langfuse-web binds the pod IP, not localhost). Dedicated Langfuse project
+  `projekt-matching`.
+- **Trace content**: automatic spans for all components incl. both LLM calls
+  (prompts + outputs); Flow 2 additionally attaches metadata via the Langfuse
+  SDK: canonical URL, requirement table, both match values, decision, CRM
+  opportunity ID. Traces are filterable by decision.
+- **Manual annotation** (chosen eval mode): two score configs —
+  `extraction-correct` (Must/Nice/Misc correctly derived?) and
+  `matching-correct` (✅/❌/❔ against CV correct?) — plus one annotation
+  queue fed by Flow 2 traces. Thomas scores in the Langfuse UI (:8091);
+  scores are a quality record only, no automation acts on them.
+
+## 7. Configuration and secrets
+
+- All runtime settings in Langflow global variables (encrypted in Langflow's
+  Postgres): IMAP/SMTP credentials, EspoCRM base URL + API key, notification
+  recipient, alert recipient, match threshold (85), vLLM endpoint + model
+  name. The EspoCRM key is read from
+  `/home/tlg/mkt/bewerb/.secrets/espocrm-api.md` once during setup; it never
+  appears in flow JSON, git, or chat.
+- CV and `rahmenbedingungen.md` are copied into the bind-mounted Langflow
+  data dir; components read them at runtime. CV update = file replacement,
+  no flow edit.
+- Both flows are exported as JSON into a git-tracked project directory
+  (restorable); custom component code lives there too.
+
+## 8. Error handling
+
+Principle: terminal state on first attempt, alert instead of retry loops.
+In-run robustness (2–3 short HTTP retries against freelancermap, vLLM, CRM,
+Nominatim) absorbs transient hiccups; nothing is retried across cycles.
+
+| Failure | Behavior |
+|---|---|
+| Langflow down at timer tick | curl fails silently; next tick retries; inbox holds state |
+| Flow 1: IMAP unreachable / lock held | Run aborts/skips; mails untouched; next cycle retries; no alert |
+| URL resolution / page fetch fails | Project terminal `failed`; listed in aggregated alert |
+| vLLM error / schema-invalid JSON | Project terminal `failed`; alert |
+| CRM error (persistent) | Project terminal `failed`; alert |
+| Nominatim failure | That Misc row → ❔; run continues (skill rule) |
+| Opportunity created, notification send fails | Accepted edge case: retryless design means no resend; trace records it |
+| Match-value drift over time | Start-date rule is date-relative; gates test thresholds, not exact values |
+
+Trigger mail ALWAYS reaches Trash once every contained project is terminal
+(`created`/`rejected`/`duplicate`/`failed`) — errors surface via the alert
+mail and failed Langfuse traces, never via inbox accumulation.
+
+## 9. Test plan
+
+Known references from the skill: example 1 project 4
+(`…/projekt/python-entwickler-fuer-eine-ki-anwendung-m-w-d`) scored 86 %
+Must; example 2 (`…/projekt/test-engineer-m-w-d-3020995`) scored 25 % Must.
+Before every CRM-writing test, opportunities whose `cProjektlink` matches
+the canonical test URLs are deleted — including those the original Claude
+skill created — else dedup blocks the test.
+
+1. **Component tests**: invoke Flow 2 directly via the run API for both
+   example projects. Verify exact description format, threshold behavior
+   (> 85 % / < 50 %), CRM entities and linking, verify read-back.
+2. **Gate 1 (consider)**: self-built projektagent-style mail with example
+   1's five projects sent `chancen@` → `chancen@`. Expect: five Flow 2 runs,
+   project 4 in CRM (Must > 85 %, the skill scored it 86 %), notification
+   `[Projekt-Match] Freelancermap — <Must> % — …` at `chancen@`, trigger
+   mail in Trash, five Langfuse traces.
+3. **Gate 2 (reject)**: example-2 mail. Expect: trace with table and
+   matches, Must < 50 %, no CRM entry, no notification, mail in Trash.
+4. **Dedup test**: resend gate-1 mail. Expect: no second opportunity, no
+   second notification, mail in Trash.
+5. **Recipient flip**: notification AND alert recipient variables →
+   `Thomas.Langer@destengs.com`. Design phase ends.
+6. **Gate 3**: delete test opportunity, resend example-1 mail. Expect: CRM
+   entry re-created, notification to `Thomas.Langer@destengs.com`; receipt
+   confirmed by Thomas (that inbox is not accessible to the automation
+   builder).
+
+## 10. Out of scope
+
+- Portals other than freelancermap (detection is URL-based; other portals
+  are an extension point in Flow 1 step 3/4).
+- Login-protected ausschreibungen (fetch fails → alert; no snippet fallback).
+- LLM-as-judge evaluation, automated actions on Langfuse scores.
+- Resending notifications lost to the accepted SMTP edge case (§8).