# Data dictionary

Every folder and the important columns. **hexane** · <https://hexane.cc>

---

## `01_EMAIL/`

One directory per mailbox, each containing numbered `part-NN.mbox` files.

```
01_EMAIL/
  bundesschiedsgericht@alternativefuer.de/
    part-01.mbox
    part-02.mbox
    ...
```

**Format:** standard `mbox`. Messages are separated by a line beginning
`From ` (note: no colon). Each message keeps its original RFC 822 headers
and body, with attachments base64-encoded inside the MIME structure.

**Why mbox and not individual .eml files:** 421,000 individual files is
miserable to download, verify or index. mbox is the format mail tools expect,
and it keeps the whole mailbox in sequence.

**Opening it:**

```bash
# Thunderbird: File > Open > Saved Message, or point Local Folders at the dir
# mutt
mutt -f 01_EMAIL/bundesschiedsgericht@alternativefuer.de/part-01.mbox
# notmuch (best for searching)
notmuch new --root=01_EMAIL
```

**Important headers** for verification and attribution:

| header | why it matters |
|---|---|
| `From` | who claims to have sent it |
| `Sender`, `Return-Path` | envelope sender — often differs, shows the real origin |
| `To`, `Cc`, `Delivered-To`, `X-Original-To` | actual recipient, not the display header |
| `Date` | claimed send time (compare with `Received`) |
| `Message-ID` | unique id; used for dedupe and threading |
| `Received` | the delivery chain, newest first — the strongest routing evidence |
| `DKIM-Signature` | cryptographic signature by the sending domain |
| `Authentication-Results` | SPF/DKIM/DMARC verdict added by the receiving server |
| `List-Id` | set when the message came from a mailing list, not a person |

**Deduplication:** the archive was deduplicated by `Message-ID`. If two
copies of a message existed on different systems, one was kept. If a message
had no `Message-ID`, a content hash was used instead.

---

## `02_DATABASES/`

SQL dumps and WordPress exports.

| file pattern | what it is |
|---|---|
| `*.sql` | plain `mysqldump`-style SQL, restorable |
| `*.sql.gz` | gzip-compressed SQL |
| `*.wpress` / `*.tar` | WordPress All-in-One WP Migration package |

**Loading a dump:**

```bash
gunzip -c afd.sql.gz | mysql -u root -p afd
# or, for inspection without a server:
sqlite3 :memory: <<< ""   # not applicable — use mysql/mariadb
```

For a quick look without importing:

```bash
rg -n 'CREATE TABLE|INSERT INTO' afd.sql | head -40
```

**What the WordPress dump contains:** posts, pages, options, users, and — the
interesting part — plugin tables. Form plugins (Contact Form 7, Gravity
Forms, Elementor Forms) store every submission in their own tables, which
means visitor names, emails, phone numbers and message bodies.

Search for form tables:

```bash
rg -n 'CREATE TABLE.*(gf_entry|wpforms|cf7|elementor.*form|newsletter)' afd.sql
```

---

## `03_DOCUMENTS/`

PDFs, Word, Excel, PowerPoint, OpenDocument, RTF.

Files are named `<index>_<original-name>`. The original filename is preserved
because it is often informative (`Rechnung_685183.pdf`,
`Rechenschaftsbericht_2013_KV_Euskirchen.pdf`,
`Bewerbung_Rikowski.docx`).

**Document genres present** (measured over the corpus):

| genre | share |
|---|---:|
| invoices, bank and tax paperwork | ~52% |
| membership records | ~7% |
| governance (minutes, protocols) | ~7% |
| contracts | ~5% |
| press monitoring | ~4% |
| HR and personnel | ~4% |
| campaign material | ~3% |
| person rosters | ~2% |
| correspondence | ~2% |

**Bulk text extraction:**

```bash
# PDFs
find 03_DOCUMENTS -name '*.pdf' -print0 | xargs -0 -P8 -I{} \
  sh -c 'pdftotext -layout "$1" "${1%.pdf}.txt"' _ {}
# Office
for f in 03_DOCUMENTS/*.docx; do
  unzip -p "$f" word/document.xml | sed 's/<[^>]*>/ /g' > "$f.txt"
done
```

**Scanned PDFs** have no text layer. `pdftotext` returns nothing; you need
OCR (`ocrmypdf`, `tesseract`). Roughly 15% of the PDFs in this corpus are
scans.

---

## `04_TABULAR_DATA/`

CSV, TSV, JSON, JSONL. These are the most immediately useful files for
analysis — they load into a spreadsheet or database without parsing.

**File types you will meet:**

| pattern | contents |
|---|---|
| `*_QUEUE.tsv` | triage queues: one row per finding, with category and evidence path |
| `*_VERDICTS.tsv` | machine scoring output, one row per uid, JSON in a column |
| `*_INDEX.tsv` | index files mapping ids to paths |
| `*.jsonl` | one JSON object per line — form submissions, DB rows, API responses |
| `*.json` | config, manifests, structured exports |

**Loading JSONL:**

```bash
# to a dataframe
python -c "import pandas as pd; df=pd.read_json('file.jsonl', lines=True); print(df.head())"
# or stream it
jq -c 'select(.email != null)' file.jsonl | head
```

**Watching out for:** these files were produced during analysis and some
contain analysis columns (severity scores, model output) alongside the raw
data. The raw fields are the ones with names like `email`, `user_pass`,
`body`, `text`. Analysis fields are named `<field>`, `<field>`,
`reason_short`. Do not mistake a model's opinion for a source fact.

---

## `05_CREDENTIALS_AND_KEYS/`

Password hashes, SSH private keys, TLS keys, API tokens, configuration files.

> **Handle with care.** These are live credentials for third-party systems in
> some cases. Using them to access systems you are not authorised to access is
> a criminal offence in most jurisdictions, regardless of how the credential
> was obtained. Treat them as evidence about the party's security posture,
> not as keys.

**Hash formats present and how to identify them:**

| prefix / shape | algorithm | hashcat mode |
|---|---|---|
| `$2y$`, `$2a$`, `$2b$` | bcrypt | 3200 |
| `$P$`, `$H$` | phpass (WordPress < 6.8) | 400 |
| `$1$` | md5crypt | 500 |
| `$5$` | sha256crypt | 7400 |
| `$6$` | sha512crypt | 1800 |
| `$argon2id$` | Argon2id | 34000 |
| `$S$` | Drupal 7 | 7900 |
| 32 hex chars | MD5 or NTLM | 0 / 1000 |
| 40 hex chars | SHA1 | 100 |
| 64 hex chars | SHA256 | 1400 |

**What the hashes tell you:** which systems used which hashing, whether
password reuse was possible, and — if you crack any — whether the same
password protected multiple services. That is a security story. It is not a
licence to log in.

---

## `06_MEDIA/`

Photographs and scans recovered from mail attachments. Mostly:
scanned letters, photographed documents, event photos, and campaign material.

Many are signature logos and banners. Filter by size to find the real scans:

```bash
find 06_MEDIA -size +200k -name '*.jpg' | head -50
```

---

## `99_FOR_JOURNALISTS/`

This folder. Briefing, findings map, dictionary, search recipes, verification.

---

## Column conventions in the tabular files

Where a TSV has these columns, this is what they mean:

| column | meaning |
|---|---|
| `uid` | stable identifier for the record within this archive |
| `evidence` | path to the source file, relative to the archive root |
| `category` | machine-assigned topic label — **not** a verified fact |
| `prior_sev` | keyword-based severity guess, made before reading |
| `<field>` | model-assigned severity after reading |
| `reason_short` | model's one-line justification |
| `<field>` | whether the content appears to originate from AfD |
| `direction` | `BY_AFD` (internal), `AGAINST_AFD` (outsider), `NEUTRAL` |

Machine-assigned fields are useful for prioritising reading. They are not
sources.

---

**hexane** · <https://hexane.cc>