Prototype pipeline on bms-4 n8n that captures logistics-order emails from contractors, extracts the
order data from PDF/Excel attachments with OpenAI (gpt-4o-mini, switched from the original
Claude-based design 2026-09-15 — see Stage 2 below), and stores it in the p24_l_dispo_* Supabase
tables.
Issue: #7009. This doc is the source of truth for the pipeline design — per CLAUDE.md, docs
lead, workflow JSON follows. The n8n workflow branches are built + imported against the live
instance in a separate sys-admin/infra-task step (see Deployment below), NOT against the possibly
stale repo snapshot.
See also:docs/playbooks/n8n-workflow-debugging-lessons.md
— five general n8n debugging lessons (activation-blocking credentials, Wasabi SigV4 signing,
exists-vs-null, docker-compose env-var gaps, jsCode escaping) extracted from getting this
pipeline to actually run after activation.
Own workflow since 2026-09-16 (issue #7009 follow-up — workflow split). The Dispo Stage-1/Stage-2
branches were built additively inside the IMAP_Inbox_Sync__AI_Classifier workflow
(82xi1IcZPf0Efj5z) so the extraction pipeline could be prototyped and validated without a separate
import step. Activating that combined 57-node workflow then failed: n8n validates every node’s
credentials before allowing activation, not just the trigger node’s, and the classifier’s original
Fetch IMAP Emails node uses a dead imap credential (sn3que6TvNPKAXbr — plain Gmail account
password, which Gmail’s IMAP no longer accepts; fixed separately, not part of this doc). That one
dead, never-actually-exercised credential blocked activation of the entire workflow, Dispo branch
included. The Dispo branch has always had its own independently-working credentials
(gmailOAuth2tD8Cz6GiF3zmrtki, openAiApi7igBKdHLGh0wlBeW, awsDC7VuNYMdVTDjjQF,
postgreszNryy3fxESPwDwCu, httpHeaderAuthiUAjhYMLPftf7ffl) and never called into the
classifier’s nodes or vice versa (confirmed zero cross-branch connections in the live export), so it
was split into its own workflow: Dispo — Logistics Order Extraction (ecotrans.automation),
id 1PWIIWmodYYSYJNr. The classifier workflow (82xi1IcZPf0Efj5z) was restored to its original 29
nodes. Both workflows ship active: false — activation of each is an independent decision once its
own validation gate is met (classifier: IMAP credential fixed; Dispo: Krok-5 sample-email validation
below).
Architecture — TWO independent stages (revised #7009, post-PR-#7010)
The repo owner revised the original single-pass design (trigger → match → extract → upsert) into two
decoupled stages so that every mail and every attachment is captured cheaply and exactly
once, and the expensive LLM extraction runs separately in its own batch — never re-downloading a
mail and never processing the same mail twice across two batch runs:
Stage 1 — INGEST (continuous, cheap, no Claude)
gmailTrigger (ecotrans.automation@gmail.com)
→ capture EVERY mail → upsert p24_l_dispo_emails_raw (idempotent on gmail_message_id)
→ upload EVERY attachment → Wasabi (bucket p24-infra, prefix dispo-ecotrans/<gmail_message_id>/)
→ attachments jsonb = [{filename, mimetype, wasabi_url}, ...]
Stage 2 — BATCH (separate trigger/cron, reads the queue)
claim UPDATE p24_l_dispo_emails_raw SET processed_at=now()
WHERE id IN (SELECT id … WHERE processed_at IS NULL ORDER BY created_at
FOR UPDATE SKIP LOCKED LIMIT :batch) RETURNING *
→ match sender (domain OR exact address)
→ fetch attachment from Wasabi → PDF/Excel→text → OpenAI extract → upsert p24_l_dispo_zlecenia
→ unknown sender / low-confidence → p24_l_dispo_szablony
(processed_at stamped regardless of outcome — a row never returns to the queue)
Both branches now live in their own Dispo — Logistics Order Extraction (ecotrans.automation)
workflow (id 1PWIIWmodYYSYJNr, split out 2026-09-16 — see the note above), self-contained with its
own two entry points (Dispo Gmail Trigger for Stage 1, Dispo Batch Trigger for Stage 2 — no
cross-workflow calls). Both stay inactive until a human validates them on 2–3 real sample
emails (activation gate, design point 2/h).
LIVE (#7582, 2026-10-08) — To/Cc backfill done (#7679); gmail_message_id field propagation confirmed correct
3. n8n batch branch (Stage 2)
cron claim → OpenAI extract → zlecenia/szablony
own workflow 1PWIIWmodYYSYJNr (bms-4 n8n)
LIVE — gmail_message_id field-propagation bug fixed (#7430, 2026-10-05): Dispo Match Sender now reads claimed rows via $('Dispo Claim Batch').all() (not $input, which was overwritten by the Fetch Kontrahenci HTTP node) and spreads ...row so gmail_message_id flows through to Dispo Upsert Zlecenie. Image attachment → OpenAI vision path live (#7450, superseded #7446). Classification sub-flow LIVE (#7711, 2026-10-13)
shipped (#7571, CI-applied). The Stage-1 nodes that write To/Cc and the Gmail backfill are LIVE (#7582, 2026-10-08); the one-off classification backfill (#7679) ran, and the live Stage-2 classification sub-flow is LIVE (#7711, 2026-10-13) — an additive 20-node Dispo Classify sub-flow in 1PWIIWmodYYSYJNr classifies every new non-footer document (gpt-4o, see Stage 2 → Live Stage-2 classification; #7821 added the transient-reject report gate). UI consumer: radieu/et-operational-platform#2340
Data model (p24_l_dispo_* — “logistyka / dyspozytorka”)
Deliberately a separate namespace from the generic p24_l_emails_inbox inbox space so the order
domain can evolve independently. All tables: RLS on, service_role FOR ALL, grafana_readonly
FOR SELECT, anon nothing (#4645 convention).
p24_l_dispo_emails_raw (Stage-1 capture / Stage-2 queue). One row per Gmail message.
gmail_message_id UNIQUE NOT NULL — the idempotency key (ingest upserts with
?on_conflict=gmail_message_id, so re-polling never re-captures or re-uploads). attachments jsonb
= [{filename, mimetype, wasabi_url}, …] uploaded to Wasabi in Stage 1. processed_at is the
Stage-2 idempotency gate: NULL = not yet batch-processed; Stage 2 claims work with a guarded
UPDATE … WHERE processed_at IS NULL … FOR UPDATE SKIP LOCKED … RETURNING * so two concurrent
batches can never take the same row twice, and stamps processed_at regardless of outcome (success
/ no-match / error) so a row never re-enters the queue. Partial index
ix_dispo_emails_raw_unprocessed backs the claim scan. Subject/sender search — the et-op
“Zarząd Dispo” e-mail list (et-operational-platform#2346) runs a leading-wildcard
ilike '%term%' over subject, from_name, from_address; a leading wildcard can’t use a btree,
so #7838 added one GIN trigram index per column
(ix_dispo_emails_raw_{subject,from_name,from_address}_trgm, extensions.gin_trgm_ops) to keep
that search off a full seq-scan as the ingest table grows. pg_trgm already lives in the
extensions schema (#4664); at the current ~600 rows the planner may still prefer a seq-scan
(force it with SET enable_seqscan = off when verifying via EXPLAIN).
p24_l_dispo_kontrahenci — known contractors. Matched (Stage 2) by sender_domains text[]
(domain-level) OR sender_emails text[] (exact address) — both GIN-indexed; aktywny gates
matching. Krok-0 sample data showed real contractors (dhl.com, deutschebahn.com,
nagel-group.com, winzler.de, fhd-ls.de) send from many per-person addresses within one
domain, so domain match is the primary key and exact address the fallback. Ships EMPTY — seeded
manually by a human. Note @eco-trans.eu senders are the firm’s internal dispatch, not external
contractors, and must NOT be added to the allowlist.
p24_l_dispo_zlecenia — one extracted order per source Gmail message.
gmail_message_id UNIQUE NOT NULL — idempotency key. Stage 2 upserts with
?on_conflict=gmail_message_id (Prefer: resolution=merge-duplicates). status is derived from
the core extracted fields, not the model’s self-reported confidence (#7490 — gpt-4o-mini
reports confidence:low even on complete orders): new when numer_zlecenia + both routes are
present, bez_zlecenia when numer_zlecenia/both routes/cena/ladunek are all empty (ordinary
correspondence from a matched sender, kept for audit), else wymaga_przegladu (a parse error is
always wymaga_przegladu, never bez_zlecenia). The model’s confidence/missing_fields are
still kept in raw_extracted jsonb (full model output verbatim) for audit.
p24_l_dispo_szablony — review queue for candidates: unknown sender with attachment
(powod='nieznany_nadawca', kontrahent_id NULL), known contractor but low-confidence extraction
(niekompletna_ekstrakcja), or unrecognised attachment format (nowy_format_zalacznika).
Registration of a new contractor / prompt fix is manual (out of prototype scope). Review status
do_przegladu | zatwierdzony | odrzucony; reviewed_at/reviewed_by written by the review RPCs (§Review-action contract).
p24_l_dispo_review_actions (#7571) — uniform audit trail of every review decision. One row
per review RPC call: action (assign_sender|review_zlecenie|reject_szablon|requeue_email),
target_table, target_id (PK as text), decision, patch jsonb (params / applied corrections),
reviewed_by, created_at.
New columns (#7571)
p24_l_dispo_emails_raw.to_addresses / .cc_addresses (jsonb, default []): recipients as
[{name, address}, …]. jsonb, not text[] — the single From is already split into
from_address+from_name; multi-value recipients need name+address paired per entry, and jsonb
matches the existing attachments jsonb idiom on this table. Written by the Stage-1 Upsert Raw Email node + a one-off Gmail backfill — both LIVE as of 2026-10-08 (#7582); see Deployment 4d.
p24_l_dispo_dokumenty.kategoria — a hard CHECK dictionary of 30 business-purpose values
(NULL still allowed = “not yet classified”). The original 6-token set from #7571
(zlecenie | faktura | cmr | potwierdzenie | korespondencja | inne) was replaced by #7635
(migration …_dispo_dokumenty_kategoria_slownik_30_7635.sql) so kategoria can carry the real
business sub-type (faktura VAT ≠ proforma ≠ korygująca, CMR ≠ list przewozowy ≠ POD, dokument
paletowy, …). kategoria is the document’s business purpose; typ_dokumentu stays the file
format (pdf|obraz|excel|inny) and is never set by the classifier. The backfill (#7582) and the
Stage-2 classification node (deferred infra-task) must map to exactly these 30 slugs; a new
category means a new migration extending the CHECK (same convention as dokumenty.status). The
single source of truth for the slug list is
infra-src/n8n-workflows/prompts/dispo-document-categories.json
— the SQL CHECK, the classification prompt
(dispo-document-classification.md)
and the table below all derive from it, and scripts/tests/test_dispo_document_categories.py
asserts they agree. UI labels/groups land in radieu/et-operational-platform#2346.
p24_l_dispo_dokumenty.classification_reason / .classification_confidence (0..1 CHECK) /
.classified_at / .classification_model — fill the UI “Klasyfikacja” tab + audit. kontrahent_id
(wystawca) and szablon_id (wg szablony.reguly_rozpoznawania) already exist and are filled by the
same classification node.
p24_l_dispo_zlecenia.status — CHECK-constrained to
new | wymaga_przegladu | zatwierdzony | odrzucony | bez_zlecenia; the 5th token bez_zlecenia
was added by #7490 (migration …_dispo_zlecenia_status_bez_zlecenia_7490.sql) after #7571 first
introduced the CHECK — the #7490 issue body predated #7571 and wrongly assumed no CHECK existed.
reviewed_at/reviewed_by written by the review RPC.
p24_l_dispo_dokumenty.rozmiar_bajty / .content_md5 (#7490) — the real byte length + md5 of
the attachment, computed from the actual buffer in Dispo Fan Out Attachments via
getBinaryDataBuffer (on N8N_DEFAULT_BINARY_DATA_MODE=filesystem, bin.data is only a storage
reference, so the old approxBytes() stored ~9 bytes for every document — breaking the size column
and the Stage-2/#7476 <5 KB image skip). content_md5 feeds dispo_flag_repeated_image_stopki()
(#7489): after each per-document upsert, Dispo Signature Collapse → Dispo Flag Repeated Image Stopki calls rpc/dispo_flag_repeated_image_stopki (min_emails:5), flagging jest_stopka=true
on images whose identical content recurs across ≥5 distinct emails (reply-repeated PDFs/Excel are
never flagged — images only).
Route-code, tour-number, issuer and search RPC (#7875)
Normalization helpers
dispo_route_code(subject text) SQL function — normalises an ECO route code from any text:
lowercase, spaces stripped, leading zeros removed, single-letter suffix preserved.
ECO SA 01 / Isselburg → ecosa1; eco6P → eco6p; ECO WAB 1 → ecowab1.
Returns NULL when no ECO code is found. Used by the dictionary lookup and GENERATED columns.
dispo_tour_number(txt text) SQL function — extracts tour numbers from filenames
(Tour_10653768_… → "10653768") or subject ordinals (2. Tour → "2").
GENERATED columns on existing tables
p24_l_dispo_emails_raw.kod_trasy (GENERATED STORED) — normalised route code from
dispo_route_code(subject). Serves as the normalization helper for dictionary lookup.
Distribution on the 763-row live sample (2026-10-13): 46 % of emails have a route code;
prefixes found: eco+N (42), eco+sa+N (34), eco+wab+N (9), eco+wb+N (6), eco+sattel+N (1).
Partial b-tree index (ix_dispo_emails_raw_kod_trasy WHERE kod_trasy IS NOT NULL).
p24_l_dispo_emails_raw.numer_tury (GENERATED STORED) — tour ordinal from subject
(2. Tour → "2"). Same-day run ordinals, not fleet tour IDs. Partial b-tree index.
p24_l_dispo_dokumenty.numer_tury (GENERATED STORED) — fleet tour ID from filename
(Tour_10653768_… → "10653768"). High-cardinality; 57/1000 sampled documents have it.
p24_l_dispo_dokumenty.wystawca_nazwa (text, nullable) — issuing entity name from the
classification prompt (already returned by the model). Distinct from kontrahent_id.
Filled by pipeline / backfill infra-task (deferred). GIN trigram index.
The company operates a fixed set of ~50 named routes (e.g. ecosa1, eco2, ecowab1).
Rather than storing only a free-form regex match, the system maintains a canonical dictionary table:
p24_l_dispo_trasy — id, kod (normalized, unique), nazwa (display name), aliasy text[]
(raw alternate spellings from emails), aktywna (false = proposal; true = owner-confirmed).
Ships with 19 proposal rows derived from the 763-email sample (aktywna=false).
Owner imports the final ~50-route list via INSERT … ON CONFLICT (kod) DO UPDATE (upsert).
p24_l_dispo_trasy_wzmianki — link table recording every route mention per email/document
(email_id | dokument_id, trasa_id FK, trasa_zrodlo: subject|body|document, snippet,
pewnosc). An email may mention multiple routes; trasa_id on the parent row is the primary.
FK columnstrasa_id, trasa_zrodlo, trasa_pewnosc on p24_l_dispo_emails_raw,
p24_l_dispo_dokumenty, p24_l_dispo_zlecenia. NOT GENERATED — set by the pipeline.
dispo_match_trasa_kod(code text) — looks up a normalized code in the active dictionary.
Returns trasa_id or NULL. Fast path via the unique index on kod.
dispo_backfill_trasa_emails() — idempotent backfill: matches kod_trasy (GENERATED) against
the dictionary for existing emails, sets trasa_id/trasa_zrodlo='subject'/trasa_pewnosc=1.0.
Run after owner confirms routes with aktywna=true:
UPDATE p24_l_dispo_trasy SET aktywna = true WHERE kod IN ('ecosa1', 'eco1', ...);SELECT dispo_backfill_trasa_emails(); -- returns updated row count
Import path for owner’s ~50-route list:
INSERT INTO p24_l_dispo_trasy (kod, nazwa, aliasy, aktywna) VALUES (...)ON CONFLICT (kod) DO UPDATE SET nazwa = EXCLUDED.nazwa, aliasy = EXCLUDED.aliasy, aktywna = true;
Or from CSV: \copy p24_l_dispo_trasy (kod, nazwa, aliasy) FROM 'routes.csv' CSV HEADER;
then UPDATE p24_l_dispo_trasy SET aktywna = true WHERE aktywna = false;.
Search RPC
dispo_search(q, scope, lim, offs) RPC — SECURITY DEFINER, EXECUTE only service_role.
Unified search across emails, documents, and orders.
Route search priority: trasa_id FK (dictionary match) → kod_trasy GENERATED column →
trigram on subject/filename.
scope: 'all' | 'email' | 'document' | 'order'. Returns SETOF dispo_search_result
with matched_on field (trasa_id, kod_trasy, numer_tury, filename, from, numer_zlecenia…)
and score (1.0 exact, trigram similarity for ILIKE). Input is LIKE-sanitised; min 2 chars.
Replaces client-side PostgREST or() chaining (et-op#2412).
Owner guarantees — data integrity (#7875 scope-change 2026-10-13)
G1. Additive only — no cargo columns overwritten.dispo_backfill_trasa_emails() writes only
trasa_id, trasa_zrodlo, trasa_pewnosc. It never touches trasa_zaladunek,
trasa_rozladunek, ladunek, ilosc, waga, raw_extracted, __orig.
Verified by test_backfill_only_updates_trasa_columns in scripts/tests/test_dispo_route_code.py.
G2. NULL when no match.trasa_id stays NULL when the dictionary has no active row for a
route code. Shown in UI as “brak trasy”. A dispatcher manually assigns it; the system never guesses.
G3. Second-chance inference path (after dictionary confirmed):
p24_l_dispo_trasy.typowy_zaladunek / typowy_rozladunek — owner-supplied typical endpoints.
When subject-based extraction yields NULL, the pipeline may compare a zlecenia row’s
loading/unloading fields against these and write a low-confidence suggestion
(trasa_zrodlo='inference', trasa_pewnosc < 1.0). A dispatcher confirms or rejects.
'inference' is an allowed value in the trasa_zrodlo CHECK constraint on all four tables.
G4. Backfill diagnostic report.dispo_backfill_report() — SECURITY DEFINER, read-only.
Returns JSONB with:
- route_matches_by_email — per-route count of emails matched via trasa_id
- unmatched_emails_with_code — emails whose kod_trasy is non-NULL but trasa_id is NULL
- zlecenia_empty_loading_unloading — non-bez_zlecenia zlecenia with NULL trasa_zaladunek
or trasa_rozladunek (24 observed on 2026-10-13; listed for human cross-check, not fixed here).
Słownik kategoria — 30 wartości (#7635)
Pełny słownik procesowy. Źródło prawdy: infra-src/n8n-workflows/prompts/dispo-document-categories.json — SQL CHECK, prompt klasyfikacji i ta tabela muszą być spójne (pilnuje scripts/tests/test_dispo_document_categories.py). NULL = niesklasyfikowano. kategoria to sens biznesowy dokumentu; typ_dokumentu (bez zmian) to format pliku (pdf|obraz|excel|inny).
#
slug
Etykieta PL
Grupa
Wskazowki rozpoznania
1
zlecenie_transportowe
Zlecenie transportowe
Zlecenia
Transportauftrag, Frachtauftrag, Ladeauftrag, “Zlecenie transportowe”, Transport order, Spot-/Booking-Auftrag; adresy zaladunku i rozladunku, terminy, ladunek, stawka, numer zlecenia.
2
zmiana_zlecenia
Zmiana / anulowanie zlecenia
Zlecenia
”Anderung”, “Storno”, “Stornierung”, “zmiana zlecenia”, “anulowanie”, aktualizacja terminu lub adresu istniejacego zlecenia, bez nowego pelnego opisu ladunku.
3
potwierdzenie_zlecenia
Potwierdzenie zlecenia
Zlecenia
Auftragsbestatigung, order confirmation, potwierdzenie przyjecia zlecenia przez przewoznika.
4
awizo
Awizo
Zlecenia
avis, awizacja dostawy/zaladunku, slot/okno czasowe, ETA, numer rampy/bramy.
5
cmr
List przewozowy CMR
Dokumenty przewozowe
formularz CMR z ponumerowanymi polami (1 Nadawca, 2 Odbiorca, 3 Miejsce rozladowania…), “CMR”, konwencja o umowie miedzynarodowego przewozu drogowego, pola podpisow/pieczeci.
6
list_przewozowy
List przewozowy (inny niz CMR)
Dokumenty przewozowe
krajowy list przewozowy, Frachtbrief, waybill, consignment note, e-CMR/lista zaladunkowa uzywana jako list przewozowy.
7
dokument_wz
WZ / dowod dostawy towaru
Dokumenty przewozowe
Lieferschein, delivery note, WZ (wydanie zewnetrzne), packing list, specyfikacja towaru bez pol CMR.
8
potwierdzenie_zaladunku
Potwierdzenie zaladunku
Dokumenty przewozowe
Ladebestatigung, loading confirmation/receipt, potwierdzenie wjazdu/wyjazdu z zaladunku.
dokument biznesowy, ktory nie pasuje do zadnej kategorii powyzej, albo nieczytelny (z niska pewnoscia).
Reguły rozstrzygania (pełne w prompcie): wybierz kategorię wg głównego celu dokumentu; rodzina faktur rozróżniana ściśle (proforma / korygująca / nota obciążeniowa / VAT); cmr wymaga układu formularza CMR (inaczej list_przewozowy / dokument_wz); dokumenty paletowe → dokument_paletowy nawet jeśli podpisane; cykl życia zlecenia (zlecenie_transportowe / zmiana_zlecenia / potwierdzenie_zlecenia / awizo); loga, banery, podpisy → grafika z jest_stopka_podejrzenie=true; inne tylko dla realnych dokumentów biznesowych, które nie pasują nigdzie, lub treści nieczytelnej.
Backfill klasyfikacji (#7582 / #7679) — one-off gpt-4o
scripts/dispo_classify_backfill.py — jednorazowy, wznawialny i idempotentny skrypt backfillu, który
klasyfikuje każdy dokument z classified_at IS NULL AND jest_stopka = false (~476 wierszy; re-query, nie
hardcode) do słownika kategoria (30 wartości powyżej) oraz — jeśli wystawca pasuje do znanego
kontrahenta — ustawia kontrahent_id. Nie dotyczy live’owego workflow n8n (Stage 2 / vision branch
pozostają w 7446). Zarejestrowany w dev_r_ai_systems jako dispo-document-classification
(minimal-risk, advisory — patrz docs/eu-ai-act-compliance.md §4).
Model/API (1:1 z OCR dowodów rejestracyjnych w et-op src/lib/ocrBatchProcessor.ts): OpenAI Chat
Completions, gpt-4o, temperature=0, response_format={"type":"json_object"}, max_tokens=700, obrazy
jako image_url data-URL z detail:"high" ("low" dla obrazów < 8 KB lub z duplikatem content_md5),
retry z backoffem (baza 1–1.5 s, 3 próby). Prompt i słownik czytane w runtime z
infra-src/n8n-workflows/prompts/dispo-document-classification.md
dispo-document-categories.json
(nigdy hardcode). {{SZABLONY}} jest zawsze puste → szablon_id zawsze NULL (live’owe
p24_l_dispo_szablony to wiersze kolejki przeglądu, bez reguly_rozpoznawania).
Wejście wg typu pliku: obraz → image_url; pdf → tekst (pypdf/PyMuPDF), a gdy < 200 znaków
(skan) → render do 4 pierwszych stron na obrazy; excel → CSV pierwszych arkuszy, ucięty do 20 000
znaków; inny/nieczytelny → tylko nazwa pliku + kontekst (oczekiwana niska pewność). Klucz S3 wyliczany
z wasabi_url dokładnie jak et-op src/pages/api/dispo/preview-url.ts (ucięcie s3://p24-infra/,
odrzucenie ..).
Walidacja i zapis (po id): kategoria musi być jednym z 30 slugów (parytet z SQL CHECK — pilnuje
scripts/tests/test_dispo_classify_backfill.py); kontrahent_id musi istnieć na liście, inaczej NULL
(nigdy wymyślony id); confidence 0–100 → classification_confidence = confidence/100; zapisywane są
kategoria, kontrahent_id, szablon_id=NULL, classification_reason, classification_confidence, classified_at=now(), classification_model='gpt-4o'. typ_dokumentu i jest_stopka nie są ruszane.jest_stopka_podejrzenie (confidence ≥ 90) trafia tylko do raportu. Wiersze odrzucone po 3 próbach
zostają classified_at IS NULL.
Guardy: concurrency 5, batche po 25, kumulatywne liczenie tokenów/kosztu, twardy abort przy 25 USD
(oczekiwany łączny koszt ~5–12 USD), flagi --dry-run / --limit N / --pilot-size / --pilot-only /
--no-pilot / --cost-cap / --concurrency / --batch-size. Log do /var/log/p24-infra-workers/bez treści dokumentów i bez wartości sekretów. Błędy → Discord
(P24_DISCORD_INFRA_SCRIPTS_ERRORS_WEBHOOK_URL) + GH issue bug (Error Notification Standard).
Pilot-first (domyślnie): stratyfikowana próbka ~30 wierszy (≈10 pdf / 10 obraz / 10 excel+inny), po
której skrypt wypisuje raport (rozkład kategorii, mediana pewności, odrzucenia walidatora, 5 przykładów
najniższej pewności — tylko nazwy plików, zmierzony koszt) i automatycznie kontynuuje pełny run,
chyba że: > 10 % próbki odrzucone przez walidator, > 30 % to inne z pewnością < 0.6, albo prognozowany
koszt przekracza cap — wtedy exit 2 (orkiestrator dodaje human-action).
Uruchomienie (na bms-4, secret-manager/infra-task ma dostęp do kluczy z env hosta — bez SOPS):
# env wymagane na hoście (bez SOPS, bez druku wartości):# SUPABASE_URL (lub SUPA_HB_URL), SUPABASE_SERVICE_ROLE_KEY (lub SUPA_HB_KEY),# OPENAI_API_KEY, WASABI_ACCESS_KEY/WASABI_SECRET_KEY/WASABI_ENDPOINT/WASABI_REGION/WASABI_BUCKETpython3 scripts/dispo_classify_backfill.py --dry-run # plan (liczba wierszy, stratyfikacja)python3 scripts/dispo_classify_backfill.py --pilot-only # pilot + bramka, bez pełnego runupython3 scripts/dispo_classify_backfill.py # pilot -> bramka -> pełny run
Weryfikacja słownika na żywo przed runem: pg_get_constraintdef dla
p24_l_dispo_dokumenty_kategoria_check musi zawierać te same 30 slugów co
dispo-document-categories.json.
Poprawka reguły kierunku zlecenia + re-klasyfikacja (#7710)
Backfill z #7679 zaklasyfikował zlecenia transportowe DHL Freight jako potwierdzenie_zlecenia (181
wierszy): szablon PDF DHL ma tytuł “Auftragsbestätigung”, ale kierunek jest odwrotny — DHL
przydziela zlecenie Ecotransowi (Ecotrans jest przewoźnikiem, który otrzymuje zlecenia). Model
poszedł za tytułem dokumentu zamiast za kierunkiem.
Prompt (dispo-document-classification.md,
sekcja Decision rules → ORDER DIRECTION): rozstrzyganie zlecenie_transportowe vs
potwierdzenie_zleceniawg kierunku (kto komu zleca), nie wg tytułu — czytane z tematu maila,
nadawcy i obowiązków w treści. Dokument od spedytora/nadawcy przydzielający transport Ecotransowi →
zlecenie_transportowenawet jeśli nosi tytuł “Auftragsbestätigung”; potwierdzenie_zlecenia
tylko gdy wystawca potwierdza zlecenie, które Ecotrans u niego złożył (np.
“Transportauftragsbestätigung” od podwykonawcy TRANSA Spedition GmbH). Słownik (…categories.json,
30 slugów) bez zmian — parytet pilnują testy. Ta sama poprawiona wersja promptu obowiązuje
live’owy Stage 2 (#7582; integracja live — #7711, patrz niżej; #7446 zamknięty jako zastąpiony
przez #7450).
Skrypt (scripts/dispo_classify_backfill.py): flaga --reclassify-kategoria <slug> re-procesuje
wyłącznie wiersze, których bieżące kategoria = <slug> (niezależnie od classified_at),
nadpisując te same kolumny klasyfikacyjne przez ten sam walidator i ścieżkę zapisu
(classified_at = now()). Domyślne zachowanie (classified_at IS NULL) bez zmian. Każda tranzycja
old_kategoria -> new_kategoria (id + nazwa pliku, bez treści dokumentu) jest logowana do
/var/log/p24-infra-workers/dispo_reclassify.log, a macierz tranzycji + liczba zmienionych wierszy
trafiają do finalnego raportu i do dispo_reclassify_report.json (poprzedni run stracił stdout).
Uruchomienie re-klasyfikacji (na bms-4, te same guardy/koszt/pilot co backfill; cap np. 10 USD):
Oczekiwane po runie: wszystkie Tour_% → zlecenie_transportowe; fax% “Transportauftragsbestätigung”
od TRANSA zostają potwierdzenie_zlecenia; żaden wiersz spoza kategoria='potwierdzenie_zlecenia' nie
ruszony; typ_dokumentu/jest_stopka nietknięte.
Live Stage-2 classification of NEW documents (#7711) — design + current gap
The #7679 backfill only classified the documents that existed when it ran. New documents
arriving through the live 1PWIIWmodYYSYJNr workflow are not classified — Dispo Upsert Dokumenty writes only typ_dokumentu + jest_stopka, so every new row shows “nie sklasyfikowano”
in the Zarząd Dispo UI (et-operational-platform#2346). As of 2026-10-13 there were 76 such
classified_at IS NULL AND jest_stopka = false rows and the count grows per ingested mail.
Reuse of the existing Dispo Extract Order (PDF) call (the issue’s preferred option) is not
feasible: that node runs gpt-4o-mini with the order-extraction schema (parity would require
gpt-4o + the 30-slug classification schema), and it only sees the match + supported-attachment
subset — it would miss register_candidate, match_body and excel documents. The classifier must
cover all non-footer documents.
Agreed design (#7711) — an additive, independent classification sub-flow in the same workflow,
with zero edits to the existing money-path nodes/connections (so the extraction path is
untouched and rollback is trivial):
Own scheduleTrigger → fetch prompt (ai_prompts.dispo_document_classification, kept in lock-step
with dispo-document-classification.md
by scripts/sync_dispo_classification_prompt.py --apply; --check fails CI/cron on drift) +
fetch active kontrahenci.
Claim p24_l_dispo_dokumenty?classified_at=is.null&jest_stopka=eq.false with the e-mail context
embedded (emails:p24_l_dispo_emails_raw(subject,from_name,from_address,email_date,body_text)) —
the ORDER DIRECTION rule needs the subject/sender/body. Idempotent:classified_at IS NULL
never re-touches a backfilled row.
Sign Wasabi → fetch attachment → base64 (copied verbatim from the live Dispo Sign Wasabi Download / Fetch Attachment / PDF To Base64 nodes). Footer images never arrive
(jest_stopka=false filter).
gpt-4o, temperature:0, response_format json — system prompt with {{KONTRAHENCI}}
substituted and {{SZABLONY}}=(brak); validate (kategoria ∈ 30 slugs else reject → stays
NULL; kontrahent_id ∈ fetched ids else NULL; confidence/100; szablon_id=NULL) exactly like
the backfill’s validate_result; PATCH ?id=eq.<id> writing only the classification columns —
typ_dokumentu/jest_stopka never touched. Validation failure → no write + Discord + GH
bug issue.
Status (2026-10-13, #7711 infra-task) — LIVE. The owner approved the additive-sub-flow design
(issue #7711, 2026-10-13 05:56) and it was imported into the live 1PWIIWmodYYSYJNr workflow the
same day, keeping the workflow active, with the 47 money-path nodes left byte-for-byte unchanged
(verified before/after the import). The sub-flow is 20 nodes (#7711 shipped 19; #7821 added the
Dispo Classify Should Report? report gate), all prefixed Dispo Classify:
Dispo Classify Trigger (scheduleTrigger, 15 min)
→ Fetch Prompt (ai_prompts.dispo_document_classification)
→ Fetch Kontrahenci (active contractors)
→ Day Count (today's gpt-4o classifications, count=exact) → Day Cap Gate (IF < 500/day guard)
├─ over cap → Cap Reached (noOp) ← per-day cost guard (owner request)
└─ under cap → Claim (classified_at IS NULL AND jest_stopka=false, email context embedded, limit 10)
→ Sign Wasabi → Fetch Attachment → To Base64 (copied verbatim from the money path)
→ Build Request (gpt-4o, temperature 0, STRICT json_schema structured output w/ kategoria enum — #7817; {{KONTRAHENCI}} filled, {{SZABLONY}}=(brak))
→ Classify Document (OpenAI /v1/responses, onError:continueRegularOutput) → Parse+Validate
(mirrors backfill validate_result; emits valid/transient/report — #7821; permanent-vs-transient split — #7834)
→ Route (IF valid)
├─ valid → Upsert (PATCH ?id=eq.<id>&classified_at=is.null — idempotent, never overwrites)
│ (also the terminal-stamp path for a PERMANENT error: kategoria='inne', confidence 0 — #7834)
└─ invalid → Should Report? (IF report — #7821)
├─ report (genuine validation fail) → Error Build Report → Discord → Get GitHub Token → GH `bug` issue; row stays NULL
└─ transient (429/5xx/timeout/refusal) → dropped (no issue/Discord); row stays NULL, retried next tick
Safety properties:
Additive / reversible — zero edits to any money-path node or connection; rollback = delete the
20 nodes. The pre-change workflow JSON is backed up in the infra-task transcript.
Idempotent (scope 6) — both the claim (classified_at IS NULL) and the PATCH
(…&classified_at=is.null) guarantee a backfilled/already-classified row is never overwritten with
NULL, even under a race with the one-off backfill script.
Per-doc isolation — Sign Wasabi / Fetch Attachment / To Base64 / Build Request / Document /
Upsert run with onError: continueRegularOutput, so one bad document degrades to a validation
reject (left NULL + notified) instead of killing the 10-doc batch.
Per-day cost guard (owner request) — Day Count + Day Cap Gate stop the batch once
≥ 500 documents have been classified by gpt-4o on the current UTC day (worst case ≈ $5/day;
steady-state volume is a handful of new docs/day).
Footer images never reach the model — the claim filters jest_stopka=eq.false.
Prompt parity (scope 1) — the node fetches ai_prompts.dispo_document_classification, synced
from the repo .md by scripts/sync_dispo_classification_prompt.py --apply (run before import);
--check fails CI/cron on drift. The embedded 30-slug validation allow-list and the Build
Request json_schemakategoria enum are both guarded against drift from
dispo-document-categories.json by scripts/tests/test_dispo_classify_live_workflow.py.
Strict structured output (#7817) — Build Request sets
text.format = {type:"json_schema", strict:true, schema:{… kategoria: enum of the 30 slugs …}}.
Earlier json_object mode did not constrain the schema, so gpt-4o occasionally returned an
object with no kategoria → Parse+Validate rejected it (kategoria not in dictionary: undefined) and opened a GH bug issue for a document that then classified fine on the next tick
(observed: doc 11d8180f-…, issue #7817 — classified_at was set ~2 s after the issue was
filed). Strict json_schema makes an out-of-dictionary / missing kategoria impossible, so this
transient-reject noise class is eliminated at the source. Deploy: apply to the live workflow
with PUT /api/v1/workflows/1PWIIWmodYYSYJNr (keep active: true; only the Dispo Classify Build Request node changes), same mechanism as the #7450 live-apply.
Transient-vs-validation report gate (#7821) — strict json_schema (#7817) constrains only a
successful model reply; it cannot constrain an OpenAI error / timeout / rate-limit / refusal /
empty response. On any of those, Classify Document (which runs onError:continueRegularOutput)
passes an error/empty item downstream, Parse+Validate found no output_text, left text at its
'{}' default → obj = {} → kategoria undefined → the exact kategoria not in dictionary: undefined reject — filing a permanent GH bug issue + RED Discord for a transient blip that
classifies fine on the next 15-min tick (observed: doc dc43c4f9-…, issue #7821, at 09:00Z — 3 min
after #7817 went live at 08:57Z, proving #7817 did not close this path). The fix: Parse+Validate
now emits transient/report booleans — it parses JSON only when a non-empty model reply was
actually returned, and sets report=trueonly when a parseable JSON object failed the
dictionary/confidence checks (a genuine validation failure). A new Dispo Classify Should Report?
IF node on the Route-invalid branch is the only edge into Error - Build Report, so transient
items (report=false) are dropped: the row stays classified_at IS NULL and is retried next tick
with no GH issue / Discord. Real validation regressions still raise one issue as before.
Parity guarded by scripts/tests/test_dispo_classify_live_workflow.py
(test_parse_validate_splits_transient_from_validation_failure, test_error_chain_is_gated_on_report_flag).
Deploy:PUT /api/v1/workflows/1PWIIWmodYYSYJNr (keep active: true), same mechanism as #7450.
(The one-off scripts/dispo_classify_backfill.py needs no mirror: it never files a per-document GH
issue, and already treats an empty/unparseable/errored OpenAI reply as status="error" — not a
validator rejected — via the exception path in classify_document.)
Permanent-error terminal-stamp (#7834) — the #7821 gate correctly stopped filing GH issues for
transient blips, but it classified every no-usable-reply outcome as transient → classified_at
stayed NULL → the doc was re-claimed, re-downloaded from Wasabi and re-POSTed to OpenAI for the
same error on every 15-min tick, forever. For a permanent document error this is pure
waste and the doc never classifies. Parse+Validate now splits the error branch: a permanent
error (OpenAI description/message matches a context-window / too-large / corrupt-unreadable-file
signature) is stamped terminally via the existing Route(valid)→Upsert path —
kategoria='inne', classification_confidence=0, classification_reason prefixed
[#7834 permanent-skip] (carrying OpenAI’s own message), classified_at=now(),
classification_model='gpt-4o' — so the next Claim (same classified_at IS NULL filter) skips it.
No GH issue (report stays false). A transient error (429 / 5xx / timeout / network /
refusal) is unchanged: left NULL, retried, no issue. A config/auth fault (401) does not match the
permanent signature, so an OpenAI outage never mass-stamps docs as inne. Root case: the 444 KB scan
2026-10-13_09-05_export.pdf (dc43c4f9-…) that triggered this issue.
Why NOT a pre-flight byte-size guard (the issue’s option 1). Live data refutes byte-size as a
predictor: PDFs up to 4.4 MB classify fine (0.85–0.9 confidence) while the 444 KB scan
fails — the context-window blow-up is driven by rendered page/token count, not file bytes. Any
threshold low enough to catch 444 KB would wrongly downgrade the many large PDFs that classify
correctly to metadata-only guesses. Detecting the actual 400 is precise and makes the waste
one-time (one fetch + one 400 per doc, then terminal) instead of guessing a threshold.
Known limitation: excel/inny documents (no PDF/image bytes OpenAI vision can read) are classified
from filename + e-mail context only (metadata), the same fallback the backfill uses for
unsupported types — expect lower confidence for those. A transiently unclassifiable document
(OpenAI error/timeout/rate-limit/refusal/empty) stays classified_at IS NULL and is retried every tick
(bounded by the per-day guard), with no issue filed (#7821); it raises a GH bug issue only when
the model returned a parseable reply that failed the dictionary/confidence validation (a genuine
regression). A permanently unclassifiable document (oversized / corrupt — context-window 400) is
stamped terminally as inne and never retried (#7834).
Review-action contract (#7571) — used by the et-op UI server routes ONLY
The UI (radieu/et-operational-platform#2340) calls these exclusively via its own server routes
/api/dispo/* using the Supabase service role (route verifies role zarzad / platform_admin).
All are SECURITY DEFINER, search_path=public,pg_temp, EXECUTE revoked from PUBLIC and granted
to service_roleonly (anon/authenticated cannot call them). Every call writes one
p24_l_dispo_review_actions audit row.
Approve an unknown-sender szablony row: assign to existing kontrahent or create new (p_new_nazwa); append domain/address to sender_domains/sender_emails (normalised lower(trim), no dup); set szablon zatwierdzony + reviewed_*; re-queue the source mail (processed_at=NULL). Returns jsonb {szablon_id, kontrahent_id, status, requeued_gmail_message_id}.
szablon/kontrahent not found → no_data_found; neither p_kontrahent_id nor p_new_nazwa → invalid_parameter_value
dispo_review_zlecenie
p_zlecenie_id bigint, p_decision text (zatwierdzony|odrzucony), p_patch jsonb?, p_reviewed_by text?
Apply whitelisted field corrections from p_patch (numer_zlecenia, data_zlecenia, trasa_zaladunek, trasa_rozladunek, ladunek, ilosc, waga, cena, waluta, kontrahent_id — COALESCE: missing/null key = no change; iter.1 does not null-out fields), set status, reviewed_*, updated_at. Returns the updated zlecenia row.
bad decision → invalid_parameter_value; zlecenie not found → no_data_found
dispo_reject_szablon
p_szablon_id bigint, p_reviewed_by text?
Set szablon odrzucony + reviewed_*. Returns the szablon row.
Set processed_at=NULL for the mail. Returns the emails_raw row.
both/neither id → invalid_parameter_value; not found → no_data_found
Re-queue idempotency: re-processing a re-queued mail re-upserts zlecenia on
on_conflict=gmail_message_id and dokumenty on (gmail_message_id, filename) → no duplicate orders
or documents (the 7416 idempotency guarantees still hold).
Stage 1 — ingest branch design (to build on the LIVE instance)
Additive only, no Claude call — pure capture, cheap and deterministic. Runs in parallel to the
existing IMAP/OpenAI classifier; the emailReadImap node is deliberately not reused (the
n8n-nodes-imap community package is not installed on our instance — .claude/commands/n8n-nodes.md).
Node graph (all node patterns per .claude/commands/n8n-nodes.md):
Dispo Gmail Trigger — n8n-nodes-base.gmailTrigger (tv1), credential gmailOAuth2 id
tD8Cz6GiF3zmrtki (“Gmail ecotrans.automation@gmail.com” — the SAME mailbox the classifier and
Tronik_GPS_-_Kierowcy already use). options.downloadAttachments: true,
options.attachmentsPrefix: "attachment_". Poll-based (answers the “monitoring nowych maili”
question — poll, not webhook). Verify at activation: connects to ecotrans.automation@gmail.com,
NOT radieu@gmail.com. (Krok-0 note: this OAuth credential’s refresh token had expired and was
re-connected in the n8n UI by the user 2026-09-14 — confirm it is live before activation.)
Upload Attachments → Wasabi — the Dispo Upload To Wasabi node (bucket p24-infra,
endpoint s3.eu-central-2.wasabisys.com). Key:
dispo-ecotrans/<gmail_message_id>/<safeName>, where safeName is the attachment filename with
its S3-unsafe characters sanitised (see the key-sanitisation fix below). Collect one
{filename, mimetype, wasabi_url} object per attachment.
S3-key sanitisation fix (#7553, 2026-10-08). The objectKey in Dispo Fan Out Attachments
was built from the raw filename: dispo-ecotrans/${gmail_message_id}/${filename}. A filename
containing a space or + (e.g. the real DHL attachment 20221116 Haulier-Instructions DE+EN.pdf)
produced an object key whose SigV4 canonicalisation the native S3 node (n8n-nodes-base.s3,
introduced by the #7350 0-byte fix) and Wasabi recompute differently →
SignatureDoesNotMatch → HTTP 403, surfaced by n8n as the generic string
"Forbidden - perhaps check your credentials?". This is not a credential problem — the
credential is valid and normal filenames ingest fine; only keys with spaces/special characters
failed, and the bare-string error (not an error object) means Dispo Stage1 Error - Build Report
emitted no **failed node:** line. Fix: sanitise only the key segment in
Dispo Fan Out Attachments (keep the native S3 node — do NOT revert to a manual-SigV4
httpRequest PUT, that reintroduces the #7350 0-byte regression):
The human-readable original filename is still preserved in attachments[].filename,
p24_l_dispo_dokumenty, mimetype/typ_dokumentu — only the S3 key is sanitised.
wasabi_url derives from objectKey (Dispo Set Wasabi URL) and Stage 2 derives its download
oKey from the stored wasabi_url, so upload↔download stay consistent automatically — no other
node changed. Applied live via PUT /api/v1/workflows/1PWIIWmodYYSYJNr (kept active: true,
43 nodes). Objects that previously 403’d were never stored (no raw row was ever upserted for the
failing message), so no historical-key backfill is needed.
0-byte-upload fix (#7350, Stage-1 of #7009, 2026-10-03). This node was a manual-SigV4
httpRequest PUT (specifyBody: binaryData, inputDataFieldName: data). On n8n 2.26.3 that
node transmits Content-Length: 0 for a filesystem/stream binary body — every object it ever
uploaded landed as 0 bytes (empty-string ETag d41d8cd9…), silently breaking all
downstream attachment extraction even though Wasabi returned HTTP 200. Replaced (owner decision
Option B, over the alternative of widening NODE_FUNCTION_ALLOW_BUILTIN for a require('https')
Code node) with n8n’s native generic S3 node (n8n-nodes-base.s3, operation: upload,
binaryPropertyName: data) using a new n8n s3-type credential Wasabi p24-infra S3 (#7350)
(id 0bS1Y3dt7lQCmEeA, forcePathStyle: true, endpoint s3.eu-central-2.wasabisys.com,
region eu-central-2). The credential wraps the existingWASABI_ACCESS_KEY/WASABI_SECRET_KEY
(no new secret material). The generic S3 node — unlike httpRequest + an aws credential —
correctly signs SigV4 for non-AWS S3 hosts (see playbooks/n8n-workflow-debugging-lessons.md §2)
and streams the binary body. Validated end-to-end on 2026-10-03: a 4096-byte upload through
this exact node/credential, then an independent host-side SigV4 GET, roundtripped byte-for-byte
(SHA256 match, non-empty ETag). The upstream Dispo Sign Wasabi Upload Code node is unchanged —
it still carries objectKey + the binary data through (its now-unused SigV4 output is harmless),
so Dispo Set Wasabi URL’s fallback to $('Dispo Sign Wasabi Upload').item.json still resolves.
Inline signature/logo prune (#7356, Stage-1 of #7009).Dispo Fan Out Attachments drops
inline HTML-signature images (e.g. Outlook/Nagel imageNNN.png, repeated eco-trans.eube07e925.jpeg) before fan-out, so they never reach Wasabi, Stage-2, or
p24_l_dispo_szablony. An attachment is pruned only when it is an image/*and at least
one independent inline signal holds: (a) its filename is cid:-referenced in body_text
(or the binary carries a contentId/Content-Disposition: inline marker), or (b) it is under
~5 KB. Non-image attachments (PDF/Excel) and genuine larger image scans are always kept; when
every attachment on a mail is pruned the email still survives to aggregation as a marker item
with has_attachments=false.
Prune tightening (#7446). The #7356 signals missed the 389×86 PNG banner of execution
561445: it was >5 KB (so signal (b) didn’t fire) and its cid: reference lived in the
HTML part, not the captured text/plain body_text (so signal (a) didn’t fire — gmailTrigger
does not reliably surface contentId/inline markers on the binary either). Two additions,
both still gated on image/* so PDF/Excel are never touched:
(b′) raise the size cutoff for a non-cid image from ~5 KB to ~40 KB. Signature,
logo and banner images are almost always well under this; a genuine scanned A4 order page
is typically ≥ ~100 KB, so real document scans are still kept.
(c) dimension heuristic — cheaply parse the pixel dimensions from the decoded buffer
when available (PNG: width/height big-endian uint32 at IHDR offsets 16–19 / 20–23;
JPEG: scan the SOF0/SOF2 marker) and prune when the larger dimension ≤ ~700 px
(logo / banner / signature territory — a full-page document scan is ≥ ~1000 px on its long
edge). This catches the 389×86 banner directly. If dimensions can’t be parsed cheaply, fall
back to (a)/(b′) only — never guess.
Stage-1 prune is best-effort noise reduction; the Stage-2 Dispo Sniff Format magic-byte
route (design point 4) is the authoritative guarantee. Anything that still slips past this
prune is caught at Stage 2 and routed to p24_l_dispo_szablony
(powod='nowy_format_zalacznika') instead of crashing the OpenAI extraction call — no data
loss, no error-notification noise.
Upsert Raw Email — httpRequest POST
/rest/v1/p24_l_dispo_emails_raw?on_conflict=gmail_message_id, Prefer: resolution=merge-duplicates,
auth kapibara (id iUAjhYMLPftf7ffl). Writes gmail_message_id, from_address, from_name, subject, email_date, body_text, has_attachments, attachments — leaves processed_at NULL.
Stage 2 — batch branch design (LIVE on 1PWIIWmodYYSYJNr)
⚠️ Partially stale vs LIVE (flagged #7571; corrected re images 7711; gmail_message_id fix #7430). This section still
describes the original require('pdf-parse') text-extraction design (point 6 below) and the old
“images → unknown → szablony, never reach the LLM” routing (point 4 below). Neither matches
LIVE. The LIVE 1PWIIWmodYYSYJNr workflow (fresh export committed at
infra-src/n8n-workflows/Dispo_Logistics_Order_Extraction_1PWIIWmodYYSYJNr.json,
67 nodes) extracts via a Dispo PDF To Base64 node that sniffs magic bytes and sends the binary
straight to OpenAI — PDFs as input_file, images (image/*) as input_image (vision) —
gated by Dispo Is Supported Attachment? (supported = pdf || image/*). There is noDispo Sniff Format / Dispo Format Route / Dispo PDF To Text and no image→szablony prune on
the live Stage-2 path; #7446 (the separate-vision-branch design that point 4’s table encodes) was
**closed as superseded by 7450. The gmail_message_id field-propagation bug (#7430) was fixed
by having Dispo Match Sender read the original claimed rows via $('Dispo Claim Batch').all()
rather than $input (the Fetch Kontrahenci HTTP node in the chain replaced $input with the
kontrahenci response, silently discarding gmail_message_id from the emails_raw row). The fix
spreads ...row so gmail_message_id is available at every downstream node including
Dispo Upsert Zlecenie. Treat the committed fresh export as the source of truth for the
Stage-2 extraction nodes, not this prose.
Separate trigger (cron/scheduleTrigger). Reads and drains the emails_raw queue.
Claim Batch — httpRequest/postgres guarded claim on processed_at IS NULL with
FOR UPDATE SKIP LOCKED (SQL in the migration header). This is the “unikać dwukrotnego
przetworzenia w dwóch batchach” guarantee — two parallel batch runs never take the same row.
Dispo Fetch Kontrahenci — httpRequest GET
/rest/v1/p24_l_dispo_kontrahenci?aktywny=eq.true&select=id,nazwa,sender_emails,sender_domains,
auth kapibara.
Match Sender — code. Match the row’s from_address first by domain (sender_domains)
then by exact address (sender_emails), case-insensitive. Emit a route:
match + real (post-filter) attachment → match (attachment-based extraction path)
match + no real attachment + non-trivial body_text → match_body (body-text extraction
path, #7357 — some kontrahenci, e.g. Nagel-Group, put the whole order in the email body and
attach only inline signature/logo images; without this they fell to skip and were silently
dropped). “Non-trivial” = body_text.trim().length >= MIN_BODY_LEN (10 chars). This is
not kontrahent-specific config — it keys purely off “matched + no real attachment +
meaningful body”, so any future body-only kontrahent is handled automatically.
match + neither real attachment nor meaningful body_text → skip (nothing to extract)
no match + has attachment → register_candidate (→ p24_l_dispo_szablony,
powod='nieznany_nadawca')
no match + no attachment → skip
Follow with a switch (Dispo Route) on route — four outputs: Match, RegisterCandidate,
Skip, MatchBody (#7357). The MatchBody output → Dispo Body As Text (code, #7357),
which maps the raw body_text onto attachment_text and feeds the sameDispo Extract Order
node the attachment branches use — identical OpenAI JSON schema + system prompt + Dispo Parse Extraction → Dispo Upsert Zlecenie write path; only the input source differs.
Format detection — ⚠️ this point describes the superseded #7446 design, NOT live. On the
LIVE workflow (#7450): Dispo PDF To Base64 sniffs magic bytes and emits attachment_mime +
attachment_supported (supported = application/pdf || image/*); Dispo Is Excel? still splits
the Excel→CSV branch, but PDFs and images both flow to Dispo Extract Order (PDF) where
images are sent to OpenAI as input_image (vision) and PDFs as input_file. Images are no
longer routed to unknown/szablony and are no longer excluded from the LLM — the table below
(PNG/JPEG/GIF → unknown) is the pre-#7450 plan and is retained only as history. The historical
plan below read: “a recognised attachment type becomes plain text before reaching the LLM; an
unrecognised one never reaches the LLM at all. Routing is by sniffed binary magic bytes… Dispo Sniff Format (code) reads the leading bytes… and emits format ∈ {excel, pdf, unknown}:“
Format
Leading magic bytes (hex)
ASCII
format
PDF
25 50 44 46
%PDF
pdf
XLSX / OOXML (ZIP container)
50 4B 03 04 (also 50 4B 05 06 / 50 4B 07 08)
PK..
excel
XLS legacy (OLE2 / CFBF)
D0 CF 11 E0 A1 B1 1A E1
—
excel
PNG
89 50 4E 47 0D 0A 1A 0A
.PNG
unknown
JPEG
FF D8 FF
—
unknown
GIF
47 49 46 38
GIF8
unknown
anything else
—
—
unknown
The sniff is authoritative; the old .xlsx/.xls/.pdf filename check survives only as a
tiebreak when the bytes are ambiguous (e.g. a text/csv / plain-text attachment with no magic
number): ambiguous + filename .xlsx?$ → excel, filename .pdf$ → pdf, else unknown. A
PK.. buffer whose filename carries an explicit non-Excel extension (.docx/.zip) is also
routed unknown rather than fed to SheetJS. Dispo Format Route (switch on format) then has
three outputs:
excel → Dispo Excel To CSV — code using require('xlsx')/SheetJS.
pdf → Dispo Pass Through (PDF/other) (set, unchanged) → Dispo PDF To Text
(code) using require('pdf-parse') on the binary attachment fetched from Wasabi.
unknown → Dispo New Format Candidate (set/code) → the existingDispo Register Candidate HTTP node → p24_l_dispo_szablony with powod='nowy_format_zalacznika'
(the reason value already modelled on p24_l_dispo_szablony, no DB change). It carries the
matchedkontrahent_id from Match Sender (this path is only reached on the match
route — a known contractor who sent an unrecognised attachment format), unlike the
nieznany_nadawca path whose kontrahent_id is NULL. This terminal path still flows through
Dispo Stamp Processed (design point 8) so the row never re-enters the queue, and it does
not fire the error notification (design point 9) — an unrecognised format is an expected,
human-review outcome, not a batch error.
#7446 root cause + fix (design note).Dispo Is Excel? routed every non-.xlsx/.xls
attachment to the PDF branch with no guard on the actual file type. A 389×86 PNG logo/banner
(magic 89 50 4E 47, extensionless UUID filename af94bd0d-…) was therefore base64-encoded and
POSTed to OpenAI as a PDF; OpenAI returned 400 Bad request — "The file you uploaded is badly formatted or corrupted." (confirmed live, execution 561445), which — after #7437’s
error-reporting fix — files a bug issue on every hourly batch that claims such a mail and
extracts nothing for it (recurring noise + data loss). Magic-byte sniffing is the authoritative
fix: an image can no longer masquerade as a PDF because its filename lacks an extension, and the
unrecognised-format case is captured in the review queue for a human instead of crashing the
OpenAI call. Dispo Sniff Format reads the buffer via
await this.helpers.getBinaryDataBuffer(itemIndex, 'data') (or base64-decodes
item.binary.data.data) and compares buffer.subarray(0, 8) against the table above — no new
external module, so no NODE_FUNCTION_ALLOW_EXTERNAL change for this node.
Both external modules (xlsx, pdf-parse) already ship inside the official n8n image’s own
node_modules (verified live on bms-4-n8n-1, 2026-09-15 — no image rebuild needed) and just need
allowlisting: NODE_FUNCTION_ALLOW_EXTERNAL=xlsx,pdf-parse in bms-4/docker-compose.yml (n8n +
all 3 n8n-worker-* blocks) — required, sys-admin, server op. The xlsx half of this was
already applied live before this change (NODE_FUNCTION_ALLOW_EXTERNAL=xlsx confirmed live via
docker exec bms-4-n8n-1 printenv); the pdf-parse addition needs its own deploy+restart (see
Deployment). Considered and rejected: routing PDFs through the repo’s existing
infra-src/gotenberg/pdf-service (POST /v1/pdf-to-text, public endpoint
pdf.vps-i1.infra.zintegrowana.online) instead — it’s real and network-reachable, but wiring it in
would mean creating a brand-new n8n credential carrying PDF_SERVICE_API_KEY’s value, which is a
credential-distribution action outside sys-admin role scope (delegates to secret-manager) and
adds a cross-host (vps-i1 ↔ bms-4) dependency for no benefit over the zero-new-secret Code-node
path. Both attachment branches — plus the match_body branch via Dispo Body As Text (#7357) —
converge into Dispo Extract Order with $json.attachment_text set (attachment_is_excel flags
which path was taken, unused downstream; the body branch also sets extraction_source='body_text').
Extract Order — httpRequest POST https://api.openai.com/v1/chat/completions,
authentication: predefinedCredentialType / nodeCredentialType: openAiApi, credential
7igBKdHLGh0wlBeW (“OpenAi account”) — the same credential the workflow’s pre-existing “AI
Classification” node already uses; zero new secrets. Model gpt-4o-mini (parity with that
node’s cost/quality bar for this prototype — a one-line model-string change to gpt-4o if
extraction quality needs it, no config toggle added). response_format: {"type":"json_object"}
plus an explicit “return ONLY valid JSON matching this schema” system-prompt instruction (same
forced-JSON pattern as infra-src/n8n-workflows/inspection-sticker-processor.json’s Claude Vision
node) — full json_schema structured outputs deliberately not used, to keep the prototype simple.
Switched from the original Claude-based design (api.anthropic.com/v1/messages,
ANTHROPIC_API_KEY) 2026-09-15 — ANTHROPIC_API_KEY is no longer needed anywhere in this
pipeline (it was genuinely missing from bms-4; this change removes that blocker entirely, see
Deployment item 2 below). The attachment is fetched from its Wasabi URL, not re-downloaded from
Gmail. Output schema mirrors p24_l_dispo_zlecenia columns (numer_zlecenia, data_zlecenia, trasa_zaladunek, trasa_rozladunek, ladunek, ilosc, waga, cena, waluta) plus confidence
("high"|"low") + missing_fields[] to drive the review-queue routing. Dispo Parse Extraction
(downstream code node) reads OpenAI’s response shape — choices[0].message.content (a JSON
string to JSON.parse()) — not Claude’s content[] array of blocks. The exact prompt wording is
tuned from the Krok-0 sample emails.
Upsert Zlecenie — httpRequest POST
/rest/v1/p24_l_dispo_zlecenia?on_conflict=gmail_message_id, Prefer: resolution=merge-duplicates,
auth kapibara. status is set by Dispo Parse Extraction / Dispo Parse Extraction (PDF) from
the core extracted fields (#7490), NOT from the model’s confidence: new (number + both
routes present) / bez_zlecenia (number, both routes, cena, ladunek all empty — non-order
correspondence) / wymaga_przegladu (everything else, and always on a parse error). Persist
raw_extracted = the full model output (incl. confidence/missing_fields for audit).
Register Candidate — httpRequest POST /rest/v1/p24_l_dispo_szablony for the
unknown-sender / low-confidence paths.
Stamp Processed — httpRequest PATCH
/rest/v1/p24_l_dispo_emails_raw?id=eq.<id> set processed_at=now(), on every terminal path
(success, no-match, error) so the row never re-enters the queue.
Error handling (built from scratch — design point 2/f). On any failure in the batch branch:
Discord embed via P24_DISCORD_INFRA_SCRIPTS_ERRORS_WEBHOOK_URLand a bug-labelled GH issue
in radieu/p24-infra (CLAUDE.md Error Notification Standard). The candidate-registration path fans
out to the same notification node with a different message type. Extraction errors still stamp
processed_at (they do not block the queue) — they land in p24_l_dispo_szablony + the
notification.
The #7009 PRs ship only the DB migrations + this doc + the dev_r_services compliance row. The
workflow branches are not built into the repo JSON snapshot, because (design point 2/g) they must
be diffed against a fresh export of the LIVE workflow from bms-4 — editing the possibly-stale repo
snapshot and importing it risks clobbering live changes. Ordered checklist:
Krok 0 — DONE. The user provided the real-contractor domain set from a 2-month sample
(dhl.com, deutschebahn.com, nagel-group.com, winzler.de, fhd-ls.de; @eco-trans.eu is
internal, excluded). These seed the first p24_l_dispo_kontrahenci rows and tune the Extract
Order prompt.
ANTHROPIC_API_KEY → bms-4 — secret-manager.OBSOLETE (2026-09-15). Stage 2 was switched
from Claude to OpenAI, reusing the openAiApi credential (7igBKdHLGh0wlBeW) already live and
already used by this same workflow’s “AI Classification” node. ANTHROPIC_API_KEY was genuinely
missing from bms-4 (confirmed by a separate secret-manager check) — this pipeline no longer needs
it anywhere, and that gap is fully closed by the provider switch, not by a pending secret-manager
task.
xlsx,pdf-parse capability — sys-admin. Confirm/add
NODE_FUNCTION_ALLOW_EXTERNAL=xlsx,pdf-parse to bms-4/docker-compose.yml (n8n + all 3
n8n-worker-* blocks) and restart via start.sh (or docker compose up -d recreate). Both
modules already ship in the n8n image’s bundled node_modules — no image rebuild, no new
dependency install, just the allowlist. xlsx alone was already live as of 2026-09-15; the
pdf-parse addition (this change) still needs a deploy — blocked in this session by the Auto
Mode safety classifier on the scp+restart live-write step, needs a session with Auto Mode off to
complete (see Stage 2 note above for detail).
Build + import — sys-admin/infra-task. Export the live 82xi1IcZPf0Efj5z JSON, add the Stage 1
Stage 2 branches above, diff to confirm only new nodes/connections changed, import via the n8n
REST API. Keep the workflow inactive. (Stage 1 + Stage 2 + the OpenAI Stage-2 rebuild are all
imported live as of 2026-09-15, 57 nodes total, active: false.)
4b. Split into its own workflow — sys-admin/infra-task, DONE 2026-09-16. Activating the combined
57-node workflow failed on the classifier’s dead imap credential (sn3que6TvNPKAXbr —
see architecture note above), which blocks activation of the whole workflow including the
unrelated, independently-working Dispo branch. Re-exported the live 57-node workflow, confirmed
zero cross-branch connections between the 28 Dispo * nodes and the 29 classifier nodes, and:
POST /api/v1/workflows created Dispo — Logistics Order Extraction (ecotrans.automation),
id 1PWIIWmodYYSYJNr, 28 nodes, active: false — confirmed via GET, no duplicate created.
PUT /api/v1/workflows/82xi1IcZPf0Efj5z to strip the 28 Dispo nodes back out and restore the
classifier workflow to its original 29 nodes/connections (verified byte-identical in node names,
types, parameters and connections to the pre-#7009 backup, modulo pre-existing unrelated header
drift already live before this change) was blocked by the Auto Mode safety classifier
(“Modify Shared Resources”) in the session that did the split — the prepared 29-node payload is
ready, this PUT is the one remaining manual/human-supervised step to actually shrink the live
classifier workflow back down. Until it runs, the live classifier workflow still has 57 nodes
(28 of them now orphaned duplicates of the new standalone workflow) even though the repo JSON
already reflects the post-split 29-node state.
4c. Image-attachment routing — sys-admin/infra-task (#7446), PENDING. Apply the Stage-2
magic-byte route (replace Dispo Is Excel? with Dispo Sniff Format + Dispo Format Route,
add the unknown → Dispo New Format Candidate → Dispo Register Candidate path) and the Stage-1
prune tightening (design point 4 + Stage-1 point 2 above) against a fresh export of the live
1PWIIWmodYYSYJNr workflow (NOT the repo snapshot — this pipeline is live/active and not in the
repo JSON). Keep the workflow active. No DB change (nowy_format_zalacznika already modelled),
no new secret, no new external module. Validate on a real image-attachment email before and
after: before → OpenAI 400 + bug issue; after → a p24_l_dispo_szablony row with
powod='nowy_format_zalacznika', processed_at stamped, no OpenAI call, no bug issue. This
design (plan issue #7446) leads; the workflow JSON edit follows as the infra-task.
4d. To/Cc write + Gmail backfill — DONE 2026-10-08 (#7582). Classification — still PENDING. The DB
contract (To/Cc columns, kategoria CHECK dictionary, classification_* columns, zlecenia
status CHECK + reviewed_*, p24_l_dispo_review_actions, 4 review RPCs) shipped in-repo (#7571,
migrations 20261008120000/…120100/…120200). The LIVE workflow changes and the backfill are
delegated here and were done against a fresh export of the live 1PWIIWmodYYSYJNr (not the
repo snapshot):
Upsert Raw Email (Stage 1) — DONE (#7582). The Stage-1 path (Dispo Parse Email →
Dispo Aggregate By Email → Dispo Upsert Raw Email) now parses the gmailTrigger to/cc
({value:[{name,address}]}) into to_addresses/cc_addresses ([{name,address}], [] when
absent) and writes them in the upsert. Upsert stays on_conflict=gmail_message_id,
resolution=merge-duplicates; processed_at untouched (idempotent). Validated live on new mail.
Gmail backfill — DONE (#7582). One-off idempotent n8n workflow (webhook-triggered, reusing
the gmailOAuth2 + supabase-service-role-key creds): for each existing row with empty
to_addresses, fetched To/Cc from Gmail by gmail_message_id (simple:false → header parse) and
PATCHed onlyto_addresses/cc_addresses. Resumable (filter to_addresses=eq.[]),
rate-limit-aware (batch 15 to stay under Gmail per-user quota — larger bursts hit transient
Gmail 403/429s that a smaller batch fully recovered). Result: all 530 rows now carry
to_addresses (257 also cc_addresses), 0 remaining empty, 0 permanent failures. The one-off
workflow was deleted after draining. Classification/szablon backfill was NOT run (see below —
coupled to the deferred image-vision branch #7446 and gated on an LLM-cost approval).
Classification node (Stage 2) — PENDING (#7582 deferred, coupled to #7446). fill
typ_dokumentu (keep the mimetype split), kategoria
(map to the exact dictionary tokens — see New columns), kontrahent_id (wystawca),
szablon_id (per szablony.reguly_rozpoznawania), and classification_reason /
classification_confidence / classified_at / classification_model. **Coordinate with 7446
(do NOT double-edit Dispo Extract Order); §3 of #7571 (JPEG/PNG vision extraction, with a
cost/size guard, footer-skipping) extends #7446 rather than forking it. Related: #7490, #7476, #7421.
EU AI Act — the classification/vision node introduces an LLM decision. Per this pipeline’s
established deferral (the dev_r_ai_systems entry was deferred to activation at #7009), register
it in dev_r_ai_systems + add the row to docs/eu-ai-act-compliance.mdat the activation step
of this infra-task, before the node goes active.
The p24_l_dispo_review_actions log + review RPCs need no workflow change — the UI calls them
directly via its server routes.
4e. Document sizes + md5 + repeated-image stopki + status rule — sys-admin/infra-task (#7490), DONE
2026-10-08. Four owner-approved fixes, applied against a fresh live export of
1PWIIWmodYYSYJNr (kept active, 43 → 45 nodes). Blocked until #7476 + #7489 closed.
Real sizes + content_md5 — Dispo Fan Out Attachments now reads the real buffer with
getBinaryDataBuffer and sets rozmiar_bajty = buffer.length + content_md5 = md5(buffer)
(builtin crypto, already in NODE_FUNCTION_ALLOW_BUILTIN); carried through Dispo Aggregate By Email → Dispo Fan Out Documents → the Dispo Upsert Dokumenty body. Fixes the rozmiar_bajty = 9 trap (filesystem binary mode) that also made every image invisible to the 7477<5 KB
skip.
Repeated-image signature flag — two new nodes after the upsert: Dispo Signature Collapse
(collapse the per-document stream to one item so the RPC fires once) → Dispo Flag Repeated Image Stopki (rpc/dispo_flag_repeated_image_stopki, min_emails:5, idempotent).
Status from core fields, not confidence — both parse nodes (see Stage-2 point 6).
CHECK migration — bez_zlecenia added to p24_l_dispo_zlecenia_status_check (#7571 had
introduced the CHECK after the #7490 issue was written).
One-time backfills (live, reversible): (a) size+md5 on all 1016 pre-existing dokumenty rows
via Wasabi SigV4 HEAD (single-part ETag = md5; no object exceeded single-part); then
dispo_flag_repeated_image_stopki(5) flagged 426 more images (145 → 571 of 733). (b) status
recomputed on the 295 machine-status (new/wymaga_przegladu) zlecenia rows by the new rule —
148 changed (98 → new, 50 → bez_zlecenia); the prior status is stored per row in
raw_extracted.__status_before so it is fully reversible. Human-reviewed rows
(zatwierdzony/odrzucony) were deliberately out of scope.
Validated on live traffic (no synthetic injection — gmail-tools is windows-only): post-PUT
Stage-1 executions succeeded and the 4 documents ingested minutes later already carried correct
rozmiar_bajty+content_md5; a DHL Tour_*.pdf (≈400 KB), a Deutsche Bahn fax*.pdf (≈690 KB)
and image-only *.jpg (multi-MB, not flagged) all show real sizes/hashes; a new live row landed
bez_zlecenia through the extended CHECK.
Validate (activation gate). Run the 2–3 sample emails through manually: assert the trigger is
on the correct mailbox, a raw row lands in p24_l_dispo_emails_raw with its attachment on Wasabi,
re-polling the same email is idempotent (no duplicate raw row, no re-upload), the batch claim
marks processed_at, an order row lands in p24_l_dispo_zlecenia, re-running the batch is
idempotent, and the unknown-sender / low-confidence paths write to p24_l_dispo_szablony. Only
then set the workflow active.
Compliance at activation — dev_r_ai_systems + EU AI Act. Stage 2 uses an LLM for extraction;
the dev_r_ai_systems row + docs/eu-ai-act-compliance.md entry are deferred to the activation
step (design decision), not the prototype PRs.
Verification (migration halves, this + the #7010 PR)
Migration dry-run (BEGIN; … ROLLBACK;) via the bms-4 socat :15432 proxy: all tables +
indexes + RLS DO-block execute cleanly. After merge, confirm live:
SELECT tablename FROM pg_tables WHERE tablename LIKE 'p24_l_dispo_%'; (expect 4),
pg_policies shows service_role_all + grafana_readonly_select on each,
p24_l_dispo_zleceniaandp24_l_dispo_emails_raw each have the UNIQUE (gmail_message_id)
constraint, and p24_l_dispo_kontrahenci has the new sender_domains column + GIN index.