Python + Postgres Reliability Engineering — Production Data Pipeline Hardening

We run a multi-stage data pipeline that ingests business data from external sources, enriches it, verifies it against third-party providers, and publishes finished lists to clients through a web portal. It's been in production for a while, it makes money, and it works — the problem is that it doesn't work predictably. We're looking for someone to make it reliable. Not to rebuild it. The actual problem About one run in five fails. Some runs stall and never reach a terminal state — we currently have 51 sitting in a non-terminal status, the oldest three months old, and nothing detects or clears them. Recovery is a person noticing throughput dropped. The dominant defect class is a single shape repeated at every stage: something didn't happen, and nothing downstream can tell the difference between that and success. A validation step returns no result and the empty result overwrites a good one. A quality gate writes its "stop" marker to local disk, so on a two-host fleet a stop on one host is invisible on the other — and a missing marker means proceed. A stage commits its rows before its validation gate runs, so failed runs leave data behind that later runs treat as authoritative. None of this is exotic. It's a system that grew fast and never had its failure states modelled. What's already done You won't be doing archaeology. We've completed a full data-flow review across every stage — field-level inventory, schemas, edge cases, and a failure taxonomy with file-level references and measured baselines from production. You'll get that document on day one. That means we can talk about scope in specifics rather than generalities, and it means we'll expect estimates grounded in it. Scope Roughly six workstreams, commissioned one at a time so either side can stop: Instrumentation and baselining — unify run tracking, real terminal states, classified errors, one view that answers "what's running and is it healthy". This delivers numbers, not fixes, and it's first. Model failure explicitly — distinguish "not attempted" from "attempted and empty" from "attempted and failed" at every stage boundary. Make gate results durable and shared instead of host-local, and fail closed. Worker liveness and recovery — heartbeats currently reflect self-report rather than progress, so a worker hung on a network call reports healthy indefinitely. Detection and recovery need to be automatic. Database constraints — uniqueness is enforced on raw string columns that the writers never normalise, so duplicates accumulate. Each constraint needs a data-cleanup migration on live production data before it can be applied. Collapse duplicated logic — several core concepts are implemented more than once and the copies disagree. We'll decide which is correct; you implement it. CI and a test gate — a substantial test suite exists and nothing runs it before code reaches main. Not in scope: rewrites, re-platforming, new features, or changes to our matching, scoring and enrichment logic. Those are tuned and we're not touching them. Stack Python workers, containerised, on a managed instance group on GCP PostgreSQL (Supabase) — significant logic in SQL functions behind row-level security Next.js portal on Vercel Work claimed from a queue in Postgres, lane-based concurrency Several metered external data providers behind fallback chains Volume is around 100 pipeline runs a day, peaking at ~170 in season, processing a few hundred to a few thousand records per run. Skills we actually need Essential PostgreSQL, deeply. Not just writing queries — constraints and their failure modes, triggers, writing safe migrations against live production data with real rows in it, understanding what a NOT VALID constraint does and doesn't guarantee. A large share of this work is schema-level. Python in a large codebase you didn't write. Job queue and worker semantics. Leases, at-least-once delivery, idempotency, reclaiming stale work without double-executing or losing it. This is the part where being subtly wrong causes rare, hard-to-trace corruption. Observability. Structured logging, error classification, defining SLOs that mean something and instrumenting to measure them. Live data migration. Several constraints can't be applied until existing violations are resolved. That's careful, reversible, verifiable work. Valuable GCP — managed instance groups, deployment, and diagnosing host-level issues CI/CD from close to zero Supabase specifics: RLS, SECURITY DEFINER functions, connection pooling behaviour under long transactions Enough TypeScript/Next.js to follow read paths into the portal What matters more than the tech list You instrument before you change things. If your first move is a fix rather than a measurement, we're not a fit. You're comfortable saying an estimate is uncertain and explaining why. You leave runbooks, tests and documentation as part of the work, not as a separate line item we have to ask for. You push back when we're wrong. One thing to know upfront A significant share of this codebase was written, and a lot of the day-to-day operation is driven, by AI coding agents — with repository-level instructions and task-specific skills committed alongside the code. This is deliberate and it works, but it changes things: the codebase is large relative to the team maintaining it, and handover includes the agent-facing instruction layer, not just the code. We're flagging it early because it affects estimates. If that's an awkward fit, better to know now. How this runs Paid discovery first — short, fixed-fee, deliverable is a document: failure-mode analysis, instrumentation plan, prioritised backlog with effort estimates, and a proposed scope with acceptance criteria. We own the output whether or not we proceed. We may run discovery with more than one firm. Then a small paid pilot on one bounded workstream. Then phase by phase, fixed price per phase with written acceptance criteria. All work lands in our repo via pull request, reviewed by us. Payment on acceptance — criteria demonstrably met on our infrastructure. To apply Skip the generic proposal. Answer these four: Tell me about a production system you inherited and made more reliable. What were the SLOs before and after, and how did you measure them? This is the question we care most about. How would you establish a baseline for a pipeline whose run-tracking table has never recorded a failed status? You need to add a uniqueness constraint to a column with ~1,500 existing duplicate rows in production. Walk me through your approach. Who specifically would do the work, and are any subcontractors involved? Tell us your rough band for the discovery phase. If you'd rather ask questions before quoting, that's a good sign — do that instead.