Export System

The export system is a config-driven pipeline that turns SQL queries against the Gold Warehouse into formatted, optionally encrypted Excel files — delivered via email (Microsoft Graph API) or downloaded through a web UI. Everything is defined in a single YAML file; adding a new export requires zero code changes.

Architecture

sequenceDiagram
  participant C as exports.yml
  participant H as Handler
  participant Q as Query Runner
  participant E as Excel Writer
  participant M as Email (Graph API)
  C->>H: Export definition
  H->>Q: Execute SQL vs Gold
  Q-->>H: DataFrame
  H->>E: Generate .xlsx
  E-->>H: File bytes
  H->>M: Send with attachment

Flow in Detail

1. Config loading (config.py): exports.yml is parsed and validated. Every export must define query_file, recipients, filename, and subject. The config loader validates that referenced SQL files exist and that at least one recipient is specified.

2. Query execution (query_runner.py): Connects to the Gold Warehouse via pyodbc + ODBC Driver 18 with AAD token auth. The token is acquired through DefaultAzureCredential (Managed Identity in Azure, az login locally) scoped to https://database.windows.net/.default. The SQL file is read from disk and executed, returning a pandas DataFrame.

3. Excel generation (excel_writer.py): The DataFrame is written to an in-memory .xlsx file using openpyxl. Column widths are auto-adjusted based on content length (capped at 50 characters). The \{date\} placeholder in the filename is resolved to the current UTC date.

4. Optional encryption: If the export defines a password field, the Excel file is encrypted using msoffcrypto-tool with Office standard encryption. The password is included in the email body so recipients can open the file.

5. Email delivery (shared/notifications.py): The encrypted (or plain) Excel file is sent as an email attachment via the Microsoft Graph API, using the sender address from exports.yml or the EXPORT_SENDER_EMAIL environment variable.

6. Tracking (tracker.py): Successful runs update a last-run timestamp, enabling the timer blueprint to skip exports that have already run within their schedule window.

Config-Driven Architecture

All export definitions live in functions/exports/exports.yml. The top-level sender field defines the default email sender. Each export under exports: is a self-contained definition:

sender: daan.aerts@geris.nl

exports:
  finance_report:
    query_file: queries/finance_report.sql    # SQL file relative to exports/
    schedule: "0 0 8 1 * *"                   # Cron: 8am on 1st of month
    recipients:
      - cfo@geris.nl
    filename: "Finance_Report_{date}"          # {date} -> YYYY-MM-DD
    subject: "Monthly Finance Report"
    body: "See attached monthly finance report."
    allowed_groups:                             # Per-export authorization
      - "15c18657-4701-4f29-9fe4-59a71424b71a"
    password: auto                             # auto-generate memorable password

Adding a New Export

1. Write a SQL query file in functions/exports/queries/
2. Add an entry to exports.yml with the required fields
3. Deploy the Function App — no code changes needed

Schedule Syntax

The schedule field uses 6-field cron syntax (second, minute, hour, day, month, day-of-week). Set schedule: null for manual-only exports that can only be triggered via the API or UI.

Trigger Modes

Scheduled (Timer)

The export_timer_bp blueprint fires on a 15-minute interval and checks each export's schedule field against the current time using croniter. Exports whose cron expression matches are executed automatically.

API (Programmatic)

The export_api_bp blueprint exposes an HTTP endpoint for triggering exports programmatically:

POST /api/exports/{export_name}/run

Supports an optional recipient query parameter to override the default recipients (must be a @geris.nl address for security).

Web UI (On-Demand)

The export_ui_bp blueprint serves a lightweight HTML interface where authorized users can:

• Browse available exports
• Trigger an export and download the resulting Excel file directly
• View export history and last-run timestamps

The UI is served from functions/exports/static/ and communicates with the API endpoints.

Password Encryption

Exports support three password modes:

When encryption is enabled:

• The Excel file is encrypted using msoffcrypto-tool (Office standard encryption)
• The password is appended to the email body so recipients can open the file
• A new random password is generated per export run (for auto mode)

Per-Export Group Authorization

Each export can restrict who is allowed to trigger it via the allowed_groups field. This is a list of Entra ID security group object IDs. When set, the handler checks the caller's group membership claims against this list before executing the export.

This is particularly important for exports containing sensitive financial or HR data — only members of the specified groups can trigger the export through the API or UI.

Environment Variables

GO-LIVE BLOCKERS

Two issues must be resolved before the export system goes to production. See the ADR Log for full details.

Export UI Auth Not Enabled

Auth checks on the Export Manager endpoints (ui.py, api.py) are commented out with TODO: Re-enable markers. The export UI is currently open to anyone with the Function App URL.

Fix: Create App Registration, enable Easy Auth on the Function App, uncomment auth checks, set ALLOWED_GROUP_IDS.

Email Sender Not Configured

The Function App's Managed Identity lacks Mail.Send permission in Microsoft Graph. Email sending does not work yet — the Download button on the UI works as a workaround for testing.

Fix: Create a dedicated shared mailbox (e.g., exports@geris.nl), grant scoped Mail.Send permission to the MI, update EXPORT_SENDER_EMAIL.

Related Pages

• Azure Functions Overview — module architecture and shared utilities
• Function Endpoints — auto-generated endpoint reference
• ADR Decision Log — go-live blocker decisions