Data Infrastructure Engineer: Consolidate Our Agency's Data Into One SQL Source of Truth

DATA INFRASTRUCTURE ENGINEER: PROJECT APPLICATION CASE STUDY Contract project | Remote, global hours OK with EST overlap | Milestone-based pay (you propose the structure) THE TASK Read this whole post, then record a 3-8 minute Loom explaining how you would solve our problem: the architecture you'd build, what you'd keep vs replace, your migration plan, and your price, broken into milestones. Your Loom is the application. IMPORTANT NOTE We will predominantly use your Loom and case-study answer to grade your application. We expect a lot of applicants and need to separate engineers who have actually done this from people who talk about tools (or let AI talk for them). We also know some companies use "sample tasks" to farm free consulting. Stay at whiteboard level: no schemas, no code, no documents. If we want to use your specific ideas beyond hiring you, we will ask permission and pay you for your time! OUR BUSINESS RoofIgnite is a performance marketing agency for US roofing, HVAC, and home-services companies. We generate appointments for our clients through Meta ads and automated follow-up, and we spend about $100,000/month on our own client acquisition ads on top of that. We scaled from $100k/month to $300k/month in revenue in the last 12 months, and we're building toward $1M/month. Every system described below was built in-house while scaling that fast, and each one works. The problem is what happens when you put them all together. HOW OUR DATA WORKS TODAY Read this like a map. You'll get a live walkthrough of everything, with our founder Oscar, before starting anything. 1. THE CRM (GOHIGHLEVEL) Lead and client contact records live in GoHighLevel. Webhooks and automations fire at each pipeline stage and feed almost everything downstream. 2. THE SALES TRACKER (GOOGLE SHEET) Every lead we've ever generated, from first touch through dials, qualification, and close. GoHighLevel webhooks hit a Google Apps Script that writes each event to the lead's row, matched by contact ID. Ad attribution data comes in from two attribution platforms via API (we're offboarding one of them). There's a log tab recording every webhook received, for error tracing. Errors are rare these days. Separate tabs exist per niche; we'd rather have one table with a niche tag. A few sheet-native dashboards exist that nobody monitors anymore. 3. CREATIVE INTELLIGENCE (PYTHON + CRON ON A VPS) A nightly pipeline that connects the Meta Ads API, attribution, and the sales tracker: per-ad lead counts and qualified status, winner/loser creative rankings (with manual exclusions, because raw numbers sometimes lie), a Drive archive of every ad creative with visual descriptions and audio transcripts, and a library of sales-call transcripts. All of it feeds an AI copywriting workflow: pick winning ads and strong calls as training context, generate new briefs and scripts. This is the engine behind that $100k/month in ad spend. It works; its data could be cleaner. 4. THE CLOSE-TO-ONBOARDING HANDOFF (CUSTOM AUTOMATIONS) When a closer closes a deal, an internal tool generates a Stripe payment link tagged with the CRM contact ID. On payment, our internal dashboard receives webhooks and automations create the client's row in our client tracking sheet, a row in our account operations sheet, and a ClickUp onboarding task. Custom-built, not Zapier. Mostly smooth, occasional edge cases. 5. THE CLIENT TRACKING SHEET (GOOGLE SHEET, ~85 COLUMNS) Every client record after the close: statuses, team assignments, cycle and billing-relevant fields. Created automatically during onboarding, updated by the team, and read by several dashboards. Some of its columns are not native: they sync IN from Airtable (below). 6. THE ACCOUNT OPERATIONS SHEET (GOOGLE SHEET, MULTIPLE POD TABS) Per-account campaign and billing-cycle data (cycle dates, budgets, results), maintained by our account pods, one tab per pod. 7. AIRTABLE BASE #1 (OPERATIONS) One table mirrors the client tracking sheet through a TWO-WAY sync (sheet wins conflicts; rows matched by a permanent numeric Client ID; if you delete a sheet row it respawns, real deletes go through an admin tool). A second table mirrors the account operations pod tabs. Airtable exists for exactly one reason: linked records and rollups across tables (cycles billed, fees collected), which Google Sheets can't do. Those computed columns then sync BACK to the sheet. Yes, you read that right. 8. AIRTABLE BASE #2 (FINANCE) Expense projections plus employee records synced from the BambooHR API (hire/termination dates, division, role, pay group, pay amounts, active status). Payroll runs through QuickBooks for Canadian employees and Wise for US and Philippines contractors. The person who runs payroll currently visits about five different places to do it. 9. FOUR DASHBOARDS ON TOP A leadership dashboard for client status review and admin operations (reads from Airtable, writes to both Airtable and the sheets). A finance dashboard: real-time revenue vs projections, client books and team loading, churn and LTV analysis. Built because QuickBooks alone gave no clarity. A team command center: client success and media buyers see their accounts, per-client detail (ad account data, contract, running copy), and a billing tracker that pings our billing VA on Slack and bills through an internal Stripe tool. A marketing dashboard: month-over-month ad and sales performance (our own attribution, replacing a paid platform), ad editing pushed through the Meta API, the AI copywriting workflow, and commission tracking. Each dashboard is hand-wired to specific sheets, tabs, and column positions. Every new feature means another wire. THE ACTUAL PROBLEM Our record-keeping layer is Google Sheets and Airtable playing database, stitched together by two-way syncs with conflict rules. Exactly one person (the founder) can explain how all of it connects. He recorded a video walkthrough for a team member because nobody else could hold the map in their head. Answering "how many active clients do we have and what state is each one in?" means opening several tools and trusting none of them completely. Data hygiene fights us: duplicate records, IDs pasted into wrong columns, statuses that disagree between systems. Everything publishes straight to production. No staging, no branches. One bad change can touch everything. We want to launch a client-facing portal, and we won't do it on top of this. THE GOAL One central, queryable database holding our record-keeping data: leads, clients, cycles, billing, finance/employees. The sheets either retire or become disposable views of it. The operational tools keep working through the migration. They run the business; downtime is not an option. Real engineering hygiene around it: staging vs production, version control, backups, access control. Documented handoff. Our internal team uses AI coding tools daily and will maintain and extend what you build, and build clean dashboards on top. You do it right ONCE and hand it off. This is not a permanent staff-augmentation seat, and we both benefit from that. On the stack: we've been advised toward SQL (Postgres), and a peer agency at $1M/month runs their version of this on Supabase. But we are not engineers and we are not prescribing the solution. Telling us what you'd build and WHY, in terms we can follow, is most of the interview. YOUR LOOM (3-8 MINUTES) The architecture you'd build, named concretely (database, sync approach, hosting), and why it fits a business like ours. What you'd keep from the current setup and what you'd kill. Not everything here is broken; knowing the difference is the skill. Your migration order, and how you keep a 40-person team operating mid-migration. Your phase plan: milestones, what ships at each one, and the price per milestone (or your hourly rate and estimated hours per phase). We pay as milestones ship; we will not fund one large lump sum up front. One real project where you consolidated multi-system data into a single source of truth: what the mess looked like, what you built, and whether it's still running today. HOW TO SUBMIT (IN YOUR UPWORK PROPOSAL) Inform on 1-2 other businesses you've helped with similar cases. Your Loom link. Your proposed milestone and payment structure, and when you can start. We review on a rolling basis and we intend to move in days. If your plan is right and your price is fair, the first milestone (a paid architecture/discovery phase) starts almost immediately. PAYMENT Milestone-based, pay-as-we-go through Upwork. The budget listed on this post is a placeholder; you propose the numbers, and we fund milestone by milestone as work ships. We're looking for an expert who does this right one time and hands it off clean, and we'll pay properly for exactly that.