HERON (GEK TERNA Group)

Marketing Consent & Unsubscribe Platform

Summary

The public REST API, database schema, and ingestion pipeline behind a national energy retailer’s marketing preference pages. Recipients act on personalized campaign links; decisions sync to the corporate CRM in real time.

Architecture

A queued campaign workbook is read from blob storage, validated and normalized, written to a shared PostgreSQL schema, and returned to SharePoint enriched with per recipient links; when a recipient acts on a link the preference API resolves it against the same schema, sends the decision to the corporate CRM, and only then writes the audit record.
Three services joined by one database, with the CRM confirming before anything is recorded locally.
  • Campaign identity deliberately excludes the activity dates. The unique index, the conflict target, and the grouping tuple all agree on name, channel, and landing page only, because the start timestamp defaults to import time and including it would create a duplicate campaign on every re-upload of the same file.
  • The upsert distinguishes an absent spreadsheet column from a present but blank one, using explicit presence flags in a CASE expression, so a blank cell clears a date to null while a missing column preserves the stored value. A plain COALESCE upsert cannot express that difference.
  • The rate limit is checked twice around the outbound call, once before and once atomically with the insert, because the external round trip is the slow part and a concurrent request can consume the last slot during it. The window is counted in SQL rather than in process memory, so it holds across every serverless instance.
  • Bulk enrollment inserts are sized against the PostgreSQL bind parameter ceiling, batching at 60000 parameters, and conflicts are resolved server side with ON CONFLICT DO NOTHING RETURNING followed by a single sweep to recover identifiers for skipped rows. The pipeline is therefore fully re-runnable on the same file and still returns a complete mapping.

Highlights

  • Two-stage auth: caller credentials validated against Microsoft Entra ID, then a short-lived RS256 JWT bound to the link identifier, published via a self-hosted JWKS endpoint.
  • Distributed per-link rate limiting on PostgreSQL advisory locks and rolling-window counts, correct across horizontally scaled serverless instances.
  • Idempotent spreadsheet ingestion with conflict-tolerant bulk upserts and resumable chunked SharePoint uploads via Microsoft Graph.
  • 100% line coverage across the API and ETL (239 tests), separate UAT/production pipelines, architecture decision records.

Technologies

  • Python
  • FastAPI
  • Azure Functions
  • PostgreSQL
  • Alembic
  • Microsoft Graph
  • Microsoft Entra ID
All projectsView ResumeGet in touch