In production250K-member electric cooperative Confidential system, demo on request

Partner File Automation Platform

A daily download-parse-import chore became scheduled jobs with an audit trail.

A web-based ETL platform that ingests partner files from FTP/SFTP and email attachments into MS SQL Server on a schedule.

Partner File Automation Platform screenshot

Team of one· Solo architect & engineer

No team, no agency, no hand-offs. I owned this project end to end:

  • Requirements
  • Architecture
  • Backend & APIs
  • Database
  • Infra & deployment
Stack
FastAPIETLSQLAlchemyDockerMS SQL Server

Where AI does the work: Scheduled jobs do the daily file ingestion that staff used to do by hand.

What was broken, and what fixed it

  1. Problem: Staff downloaded, parsed, and imported partner files by hand every day.

    Fix: Scheduled jobs collect files from FTP/SFTP servers and email attachments, parse them, and load them into MS SQL Server, with a dashboard showing each job and its result.

  2. Problem: Manual imports left no audit trail of what came in and when.

    Fix: Every action is audit-logged, and role-based access limits who can change jobs and mappings.

  3. Problem: A new file layout would otherwise need a code change.

    Fix: A field mapping editor lets staff map a new layout through configuration, with no code changes.

Approach

  • Scheduled jobs replace the manual download-parse-import loop.
  • A field mapping editor lets staff handle a new file layout without code changes.
  • Audit logging and role-based access on every action.

What I built

  • Scheduled ingestion from FTP/SFTP and email attachments
  • Dashboard of jobs and results
  • Field mapping editor
  • Audit logging and RBAC
  • FastAPI, SQLAlchemy, and Docker

Architecture

Interface

  • Charts

Logic & APIs

  • FastAPI
  • ETL
  • SQLAlchemy

Data

  • MS SQL Server

Infrastructure & delivery

  • Docker
  • Email
  • Server
  1. 1
    EmailServer

    Scheduled jobs collect partner files from FTP/SFTP servers and email attachments.

  2. 2
    FastAPISQLAlchemySQL Server

    A FastAPI service parses each file and maps its fields into MS SQL Server through SQLAlchemy.

  3. 3
    Charts

    A web dashboard shows each job and its result, with a field mapping editor for new file layouts.

  4. 4
    Docker

    The platform runs in Docker containers.

Engineering

  • Every action is audit-logged, so there is a record of what came in and when.
  • Role-based access control limits who can change mappings and jobs.
  • New file layouts are handled through mapping configuration, not code changes.
  • Containerized with Docker, so the same build runs the same way in every environment.

Outcome

  • Replaces a manual daily download-parse-import workflow with scheduled jobs.