Gas Targets App (React/FastAPI)

Software Engineer/Developer: Manuel Solalinde (manolosolalinde@gmail.com)

Every month, reservoir and production engineers must set the monthly gas-production target for every well. Doing this well-by-well was a slow, manual grind; this full-stack web application lets an engineer process the entire well set in a single analysis, turning that process into about an hour of compute. It combines an interpolation/trend tool purpose-built for this task with guided manual review of the edge cases, then exports the finalized targets as the operator CSV consumed downstream. *(This page documents the v2.1.0 architecture — a React frontend with a FastAPI backend on AWS App Runner — since superseded by a unified Streamlit redesign on ECS Fargate.)* The heavy lifting — reading raw production history from the corporate data warehouse and fitting a trend per well — runs as a parallel, queue-driven Lambda fan-out, so the full well set is processed in one pass instead of one well at a time.

Overview

Each month an engineer creates an analysis (defined by its start/end date) and walks it through a four-stage workflow in the app. The application loads the analysis data from the corporate Amazon Redshift warehouse into a private working database, generates a production trend for every well in parallel, then surfaces two case-study reviews so the engineer can spot wells that need manual overrides before the final targets are exported.

Workflow

The user flow is organized as a set of sequential tabs:

• Database control — start/stop the RDS working-database instance on demand, so the database only runs (and only bills) while an analysis is in progress.
• Analysis selection — create a new analysis (the start/end dates form its ID), load an existing one, or delete one to reset its trends and overrides.
• Update DB & Generate Trends — two one-time-per-analysis operations: Update DB pulls header, raw production, and restim data from Redshift into the working DB; Generate Trends fans out one job per well to compute its trend. Both run asynchronously and report live status, well counts, and any wells that errored.
• Overrides & Well Review — two case studies flag wells that need attention: a Last-7-days production review (wells producing in the recent window) and a Trend review (wells whose computed trend disagrees with their producing status). The engineer reviews each flagged well's timeseries — production vs. forecast, target, and budget — and uploads an Excel overrides sheet (well ID, code, override volume, comments) that is immediately reflected back in the review tables.
• Download final output — export the finalized monthly gas-targets operator CSV, with all manual overrides applied.

Architecture

The system is a containerized, cloud-native stack provisioned entirely as code with Pulumi:

• Frontend — a React (Redux) single-page app served by an AWS App Runner service, authenticated through Azure AD, talking to the backend over the internet.
• Backend — a Python / FastAPI API on a second App Runner service. It reaches the working database through a VPC connector and pushes work onto SQS using IAM authentication.
• Worker — a Lambda function triggered by SQS messages. It reads production data from a cross-account corporate Amazon Redshift warehouse over scoped access, and reads/writes the working database.
• Data — a PostgreSQL RDS working database holds the per-analysis header, raw, restim, trends, and overrides tables.
• Queue — an SQS queue decouples the API from the workers and is the trigger that drives the parallel fan-out.

The asynchronous operations follow a consistent pattern: the frontend calls a backend endpoint to kick off the work, then polls a state endpoint until the work reports finished. The backend enqueues one SQS message per unit of work (per table for the DB update, per well for trend generation); each message triggers an independent Lambda invocation, and a per-well job-status table tracks each well so the state endpoint knows when the whole batch is complete.

Design decisions & trade-offs

• On-demand working database — rather than a shared, always-on warehouse, the RDS working DB is started and stopped per analysis, so it only runs (and only bills) while an analysis is in progress. This keeps cost bounded and leaves the engineer in control of when compute is live.
• Queue-driven fan-out over direct invocation — instead of the backend invoking workers directly, an SQS queue drives the Lambda fan-out. This decouples the API from worker scaling and isolates per-well failures, so one bad well can't take down the batch.
• Production-faithful local development — locally the environment can't reach the queue or the warehouse, so Redshift reads are mocked and the backend invokes the Lambda directly. This reproduces the production data flow without any cloud dependencies.

CI/CD & Local Development

• Deployment — automated with GitHub Actions, which runs the backend test suite, builds Docker images for the frontend, backend, and Lambda, pushes them to Amazon ECR, then updates the App Runner / Lambda services.
• Local environment — a docker compose stack runs the frontend, backend, database, and Lambda together on a developer machine. Because the local environment can't reach Redshift or SQS, the warehouse reads are mocked and the backend invokes the Lambda directly instead of going through the queue — reproducing the production data flow without the cloud dependencies.

Note: Screenshots and architecture diagrams are omitted, as they display proprietary client data and systems. Available on request, subject to client approval.