Note (2026-04-24): After this document was written, legal_entity was renamed to tenant and the old tenant was renamed to organization. Read references to these terms with the pre-rename meaning.

SCC Test Ingestion Design

Context

SCC Group is a potential client that sent 12 test invoices (as .msg email files) and a mapping spreadsheet. They want to see Orcha extract account assignment information (Kontierungsinformationen) from their invoices.

Their current process: a single inbox (eingangsrechnung@scc.at) receives invoices for 3 group companies. Invoices are forwarded to SAP, triaged, sent for approval, then booked with full account assignment (Buchungskreis, creditor, GL account, cost center or order number, VAT, etc.).

Decisions

• Single legal entity representing the whole SCC group (not 3 separate LEs per Buchungskreis)
• Full pipeline exercise: MSG→EML conversion, then SES acquisition→triage→ingestion
• Freelancer Excel handling: pre-extract CATS timesheet data into EML body during conversion (option 1 — no pipeline changes)
• Script-heavy approach: Babashka scripts for setup, conversion, triggering, and report generation. No app code changes beyond custom prompts in the DB.
• Empty business partners: no creditor master data; AI extracts vendor info from invoices directly

Data Inventory

12 MSG Files (invoices)

Mapping Spreadsheet (Mappinginformationen.xlsx)

• Kontenplan: 211 GL accounts (number + name) → gl_accounts_dataset
• KFZ-Mitarbeiter Zuordnung: 2 entries (plate → employee) → merged into cost center dataset
• Mitarbeiter-Kostenstellenzuord.: 5 entries (employee/location → cost center) → cost_center_dataset
• Liste der Buchungskreise: 3 companies (SCC=1000, HR Force=2000, XIT=3000) → custom extraction prompt

Architecture

flowchart LR
    MSG[".msg files"] -->|Python extract-msg| EML[".eml files"]
    XLSX["Mappinginformationen.xlsx"] -->|BB script| DB[(PostgreSQL)]
    EML -->|BB upload| S3["LocalStack S3"]
    S3 -->|SQS trigger| ACQ["SES Acquisition"]
    ACQ --> TRIAGE["Triage"]
    TRIAGE --> INGEST["Ingestion Pipeline"]
    INGEST --> DOC["Documents + structured_data"]
    DOC -->|BB report script| HTML["HTML Report"]

Component Design

1. Setup Script (Babashka)

Inserts into PostgreSQL:

• 1 tenant
• 1 legal entity
• 1 SES doc source (with plus-address token)
• GL accounts dataset (211 rows from Kontenplan)
• Cost center dataset (5 rows, headers: ["code", "employee", "vehicle_plate", "location"])
• Custom prompt additions (extraction, cost-center-match, accounts-match)

2. MSG→EML Conversion (Python, called from BB)

For each .msg file:

1. Parse with extract-msg: sender, subject, date, HTML body, attachments
2. Construct MIME/EML with Python email stdlib
3. Rewrite TO header to documents+{TOKEN}@mail.getorcha.com
4. For freelancer emails (Novak, Krammel): parse CATS Excel, append structured timesheet table to HTML body
5. Save .eml to staging directory

3. Pipeline Trigger (Babashka)

1. Upload each .eml to S3: incoming/{uuid}.eml
2. Send SQS message per EML to acquisition queue (S3 event notification format)
3. Pipeline takes over: SES acquisition → triage → ingestion → extraction → post-processing

4. Custom Prompts

:extraction additions:

• Buchungskreis identification: SCC EDV-Beratung AG=1000, HR Force EDV-Beratung GmbH=2000, XIT-CROSS IT GmbH=3000. Determine from invoice recipient.
• Extract all required fields: Rechnungsnummer, Bruttobetrag, Währung, Zahlungsziel, Bankverbindung
• For freelancer invoices: if timesheet data is present in the email body, extract order numbers and positions for account assignment

:cost-center-match additions:

• vehicle_plate column: if invoice references a license plate (e.g., in subject or body), find the matching row to get the employee and cost center
• location column: for office-related invoices (rent, utilities), match by delivery/service address
• Freelancer invoices use order/position numbers from the email body, not cost centers — return null cost center with a note

:accounts-match additions:

• Fixed asset invoices (vehicles, equipment purchases) → account 9900 (Anlagenverrechnung)
• All other invoices → appropriate expense account from Kontenplan

5. HTML Report (Babashka)

Standalone script that queries all SCC documents from the DB after ingestion completes.

Generates a self-contained HTML file with per-invoice sections:

Header info:

• Buchungskreis (company code)
• Typ (Rechnung/Gutschrift)
• Buchungsdatum
• Kreditor (vendor name, extracted)
• Rechnungsnummer
• Bruttobetrag + Währung
• Zahlungsziel
• Bankverbindung (IBAN/BIC)

Line items:

• Bezeichnung/Positionstext
• Konto (GL account number + name)
• Kostenstelle OR Auftragsnummer + Auftragsposition
• Vorsteuerkennzeichen
• Nettobetrag
• Buchungsschlüssel (Soll/Haben)

Per invoice: link to the document in the app

6. Cost Center Dataset Structure

Flattened from 3 spreadsheet sheets:

code    | employee           | vehicle_plate | location
--------|--------------------|---------------|---------------------------
10154   | Christoph Paumann  | W-78547A      |
100920  |                    |               | Hofgasse 3, 8010 Graz
10141   | Franz Dorfer       |               |
20001   | Michael Homole     | W-12068J      |
100910  |                    |               | Mantlergasse 30-32, 1130 Wien