Skip to content

SQLITE_INTEGRATION.md

Latest commit

 

History

History
147 lines (104 loc) · 3.76 KB

SQLITE_INTEGRATION.md

File metadata and controls

147 lines (104 loc) · 3.76 KB

SQLITE_INTEGRATION.md

Overview

The SQLite integration extends the existing mailfilter logging system by adding a structured database backend for header analysis and rule generation.

Existing Logging Mechanism

Mailfilter already supports optional logging of email headers into a plain text file:

SHOW_HEADERS = "/var/spool/filter/mailheader.log"

This mechanism writes raw headers sequentially for debugging and inspection.

SQLite Logging

The SQLite backend follows the same concept but stores data in structured form:

LOG_HEADERS_SQLITE3 = "/var/spool/filter/mailheader.log.sqlite3"

Both logging mechanisms can be enabled independently.

Design Approach

  • The SQLite logging is attached to the same processing path where headers are parsed and evaluated.

  • No separate parsing logic is introduced.

  • The original filtering behavior is not modified semantically.

  • Additional logging hooks were introduced at relevant points in the code.

Implementation Highlights

  • Initialization is performed during program startup if enabled
  • Logging occurs during header parsing and rule evaluation
  • Database connection is properly closed on shutdown

The implementation is encapsulated in a dedicated module (dblog.cc).

Stored Data

The database contains structured information including:

messages

  • message ID
  • decision (pass / deny / score-deny)
  • final score

header_entries

  • individual header fields (name/value)
  • linked to message ID

rule_hits

  • evaluation phase
  • expression
  • match result
  • score contribution
erDiagram
    MESSAGES {
        string msg_log_id PK "Primärschlüssel"
        string message_id "Original Message-ID"
        string from_addr "Absender"
        string to_addr "Empfänger"
        string subject "Original-Betreff"
        string normal_subject "Normalisierter Betreff (für Clustering)"
        string date_hdr "Date-Header (für Temporal Relevance)"
        integer msg_size "Nachrichtengröße"
        string decision "PASS / DENY / SCORE"
        integer final_score "Gesamt-Score"
        datetime created_at
    }

    HEADER_ENTRIES {
        int id PK
        string msg_log_id FK "Verweis auf messages"
        int ordinal "Reihenfolge (z.B. Received)"
        string tag "Header-Name (Received, From, Subject...)"
        string body "Header-Wert"
        datetime created_at
    }

    RULE_HITS {
        int id PK
        string msg_log_id FK "Verweis auf messages"
        string phase "Verarbeitungsphase"
        string expression "Regel-Ausdruck"
        boolean is_negative "Negativ-Regel?"
        boolean matched "Getroffen?"
        string header_tag
        string header_body
        boolean normalized_subject
        integer score_delta "Score-Änderung"
        datetime created_at
    }

    MESSAGES ||--o{ HEADER_ENTRIES : "hat viele Header"
    MESSAGES ||--o{ RULE_HITS : "hat viele Regel-Treffer"

    classDef messagesClass fill:#e3f2fd,stroke:#1976d2,stroke-width:3px,color:#000;
    classDef headersClass fill:#f1f8e9,stroke:#388e3c,stroke-width:3px,color:#000;
    classDef rulehitsClass fill:#fff3e0,stroke:#f57c00,stroke-width:3px,color:#000;

    class MESSAGES messagesClass
    class HEADER_ENTRIES headersClass
    class RULE_HITS rulehitsClass
Loading

Data Scope

  • Only email headers are stored
  • No message body is written to the database
  • Data reflects the internal processing state of mailfilter

Advantages

  • Structured and queryable data
  • Direct input for rule generation
  • No impact on existing mailfilter behavior
  • Fully deterministic and reproducible

Summary

The SQLite integration is a non-intrusive extension of the existing logging system. It enhances visibility into mailfilter's decision process without altering its core behavior.