Outlook Automation: Sync & Archiving

SOP Alignment: SOP-006 (Shipping & Logistics) Constitution Compliance: Articles I (Backbone as source of truth), II (Stateless execution), III (Auditability), IV (SOP alignment) Last Updated: 2026-03-01 Status: Active

This document describes all automated processes for managing Outlook emails, attachments, calendar events, and project archiving within the MDL Ops Engine.

1. Overview

The Outlook automation suite consists of Python scripts and n8n workflows that reduce manual overhead in project organization, document management, and financial ingestion. All scripts are idempotent (safe to re-run) and use state files to prevent duplicate processing.

2. Core Utility Scripts

2.1 Email Organization (organize_outlook_emails.py)

• Purpose: Automatically moves emails into project-specific folders under 01_Sales_Lifecycle.
• Logic: Matches Quote IDs (e.g., ML2026xxxx) in the subject or body and moves the message to the corresponding folder. Creates new folders if they don't exist.
• Constitution: Logs all moves to the Backbone Audit Log (Article III).

2.2 Attachment Sync (sync_outlook_attachments.py)

• Purpose: Downloads all relevant attachments from the MDL Communications Outlook account.
• Features:
• Date Filtering: Processes emails received after January 19, 2026.
• Duplicate Prevention: Uses data/processed_ids.json to track already downloaded files.
• Intelligent Filtering: Skips small images (logos, signatures) under 50KB.
• Target: Saves files to the GDrive Document Repository.

2.3 Project Archiving (archive_old_projects.py)

• Purpose: Moves completed or stale project folders to the 02_Archive directory.
• Criteria:
• Folders older than 2025.
• Folders marked with "- ARCHIVE".
• Stale folders (30 days of inactivity) containing an MDL invoice.

2.4 Outlook Tagger (outlook_tagger.py)

• Purpose: Shared utility library used by other scripts.
• Functions:
• tag_message_with_category() — applies Outlook category colours to emails (e.g. 🔴 "QBO Bill Created").
• create_bill_calendar_event() — creates a 📦 Bill Logged calendar event on the invoice date.
• create_bill_due_reminder() — creates a ⚠️ Bill Due calendar reminder on the calculated payment due date.
• _graph_headers() — returns valid Microsoft Graph API auth headers.

3. Running Utility Scripts

These scripts can be run manually from the terminal or scheduled via Task Scheduler:

# Sync attachments
python scripts/sync_outlook_attachments.py

# Organize emails by project
python scripts/organize_outlook_emails.py

# Archive old projects
python scripts/archive_old_projects.py

4. SOP-006 n8n Automation Workflows

All n8n workflows follow the naming convention: [Human Description] (SOP-006-[SUBTAG])

See the full lifecycle map: SOP006_n8n_Automation_Map.md

4.1 Package Tracking Ingest (SOP-006-TRACKING) ✅ LIVE

4.2 Shipping Invoice Ingest (SOP-006-INVOICING) ✅ LIVE

How the Invoice Pipeline Works

1. n8n trigger polls Inbox every 30 minutes for carrier invoice emails.
2. Attachment fetch — retrieves PDF invoice via Microsoft Graph API.
3. Intelligent Processor (/webhook/ingest-invoice) runs the background pipeline:
4. Gemini AI extracts: vendor name, invoice #, total, date, PO reference, currency.
5. Vendor matched in QBO, QBO Bill created (Net 30 default; Net 21 for UPS/FedEx).
6. Invoice logged to shipping_invoices Supabase table.
7. 📦 Bill Logged calendar event created on invoice date.
8. ⚠️ Bill Due calendar reminder created on due date.
9. Email notification sent to MDL_NOTIFY_EMAIL.
10. Source email tagged "QBO Bill Created" (🔴 Red category).
11. Hourly folder sweep (/webhook/scan-invoice-folders) catches any invoices that moved before the real-time trigger.

Manual Run Commands

# Dry run — scan inbox but make NO writes (safe to test anytime)
docker exec intelligent-processor python /app/scripts/ingest_shipping_invoice.py --dry-run

# Live run — Inbox + Logistics folders (last 30 days)
docker exec intelligent-processor python /app/scripts/ingest_shipping_invoice.py

# Custom folder + lookback window
docker exec intelligent-processor python /app/scripts/ingest_shipping_invoice.py \
  --folders Inbox "03_Operations/Logistics/TotalShip" --days 7

Supabase: shipping_invoices Table

✅ Migration already applied as of 2026-03-01.

CREATE TABLE IF NOT EXISTS public.shipping_invoices (
    id                BIGSERIAL PRIMARY KEY,
    invoice_number    TEXT UNIQUE,
    vendor_name       TEXT,
    invoice_date      DATE,
    po_reference      TEXT,
    currency          TEXT DEFAULT 'CAD',
    subtotal          NUMERIC(12, 2),
    tax               NUMERIC(12, 2),
    shipping_charge   NUMERIC(12, 2),
    total             NUMERIC(12, 2),
    qbo_bill_id       TEXT,
    source            TEXT DEFAULT 'outlook_auto',
    processed_at      TIMESTAMPTZ DEFAULT NOW()
);

Required Environment Variables

5. Best Practices & Constitution Compliance

5.1 Idempotency & Deduplication (Article II — Stateless Execution)

• Every script uses a state file in data/ to track processed message IDs.
• Scripts can be restarted mid-run without duplicating writes to QBO or Supabase.
• State files:
• data/tracking_ingest_state.json — tracking workflow
• data/invoice_ingest_state.json — invoicing workflow

5.2 Auditability (Article III — Universal Auditability)

• All successful invoice ingestions are logged to shipping_invoices in Supabase with processed_at timestamp and source field.
• The source Outlook email is tagged with QBO Bill Created category for visual audit trail.
• Calendar events serve as human-visible audit trail for finance.

5.3 AI Confidence & Human Oversight (Article V — AI & Intelligence)

• Gemini AI is used to extract data only — it does not approve or submit bills.
• If AI fails to extract an invoice number, the bill is NOT created and the email is not tagged.
• Regex fallback ensures partial data is still captured to Supabase even when AI confidence is low.
• Recommended enhancement: Log confidence_score to Supabase if AI extraction returns it.

5.4 SOP Alignment (Article IV)

• All n8n workflows reference their SOP ID in their name and documentation.
• The SOP-006 automation map (SOP006_n8n_Automation_Map.md) is the authoritative reference for all sub-step statuses.

5.5 Token & Auth Management

• OAuth tokens stored in secrets/o365_token.txt via FileSystemTokenBackend.
• If token expires: re-run the OAuth consent flow from the TokenManager or trigger re-auth via the BSC admin panel.
• Tokens should be refreshed automatically on each script run.

6. Troubleshooting