From Spreadsheet Chaos to a Live Operations Dashboard: How We Built Solerro's Cloud Data Platform

01 / FRAGMENTATIONThe Data Landscape That Was Holding Operations Back

Solar is operationally intensive. Every installation moves through a defined pipeline — site survey, design and engineering, permitting, installation, commissioning — with different teams, different timelines, and different financial calculations at each stage. When that pipeline is invisible, leadership flies blind: they can’t see where projects are stuck, which teams are outperforming, or whether margins are holding.

Solerro’s data lived in four places that didn’t talk to each other cleanly: Scoop CRM (project pipeline tracking with sparse and partially inaccurate API docs), Aurora Solar (designs and proposals), Google Cloud Platform (existing Cloud SQL infrastructure for operational data), and a layer of Zapier workflows that connected systems with automation flows nobody could fully audit. When one system changed, downstream flows could silently break — and often did. There was no API layer, no analytics infrastructure, no AWS foundation, no source of truth.

The COO didn’t bring us in because Solerro lacked developers. They have an internal tech team. The challenge wasn’t writing code — it was architecting a data platform that brings multiple systems into harmony, surfaces the right metrics, and scales with the business. That’s a different skill set, and it’s the same one we ran on the multi-account AWS modernization for a global healthcare research enterprise.

02 / UNIFICATIONWatch Four Fragmented Sources Become One Source of Truth

The first deliverable was making the data queryable. Four sources, one normalized schema in Postgres on RDS, served by a serverless Lambda API. Hit Run Sync to watch the unification happen — particles travel from each source through the Lambda hub into the RDS destination, with the consolidated record count climbing live.

That’s the basic unification. Once it’s running, every project record in any of the four upstream systems flows into RDS as a normalized, queryable row spanning 100+ fields per project — customer info, system specs (modules, inverters, batteries, optimizers), financing terms, dealer fees, profitability calculations, site survey data, and project phase tracking. Add Aurora Solar’s design data on top, and you have a query layer that the analytics can finally bite into.

The Scoop integration was the gnarly part

Scoop’s API documentation was sparse and in places inaccurate. Pagination params were documented one way and behaved another way; nested JSON returned shapes that didn’t match the docs. We reverse-engineered the actual contract through trial and error, built a TypeScript utility that paginates correctly, normalizes the nested structure into a relational schema, and bulk-inserts into RDS with retry logic and structured error handling. That utility now serves as the replicable framework we extend to every new CRM Solerro onboards.

// Scoop's docs claimed page-based pagination. The API actually uses cursors.
// Reverse-engineered through trial and error.
async function syncScoopProjects(): Promise<SyncResult> {
  let cursor: string | null = null;
  let total = 0;

  do {
    const page = await withRetry(() =>
      scoop.get('/projects', { cursor, limit: 50 })
    );

    // Flatten nested JSON. Scoop returns deep structures the schema can't accept directly.
    const normalized = page.records.map(flattenScoopProject);

    await rds.bulkUpsert('projects', normalized, {
      conflictKey: 'scoop_id',
      auditUser:   'sync.lambda',
    });

    total += normalized.length;
    cursor = page.next_cursor;  // docs said `page_token` — it’s actually `next_cursor`
  } while (cursor);

  return { recordsSynced: total, source: 'scoop' };
}

03 / EVENT-DRIVENPhase Changes as Events, Not Polling

Solar projects move through phases — Site Survey Pending → Site Survey Verification → Design & Engineering → Permitting → Installation → Commissioning. Knowing when a project changes phase, and how long it spent in the previous one, is the operational signal leadership cares about most. We built that as an event-driven pipeline: phase changes in Scoop fire a webhook → land in SQS → Lambda consumer updates RDS, computes phase duration, fires a Slack notification to the right channel. No polling, no missed events, no spreadsheet tracking.

export const handler: SQSHandler = async (event) => {
  for (const record of event.Records) {
    const phase: PhaseChange = JSON.parse(record.body);

    // 1. Update RDS — record the new phase + transition timestamp
    const previous = await rds.getCurrentPhase(phase.projectId);
    const durationMs = Date.now() - new Date(previous.enteredAt).getTime();

    await rds.recordPhaseTransition({
      projectId:    phase.projectId,
      fromPhase:    previous.phase,
      toPhase:      phase.newPhase,
      durationMs,
      transitionAt: new Date().toISOString(),
    });

    // 2. Notify the right Slack channel based on the phase
    await slack.notify(slackChannelFor(phase.newPhase), {
      project:    phase.projectId,
      moved:      `${previous.phase} → ${phase.newPhase}`,
      durationHr: Math.round(durationMs / 36e5),
    });
  }
};

04 / DASHBOARDThe Live Operations Dashboard Leadership Was Missing

Once the data is unified and the events are flowing, the analytics layer is the deliverable leadership actually feels. Below is a working mock of the executive dashboard pattern we shipped for Solerro — KPIs counting up live, the pipeline funnel rebalancing on a 6-second loop with bottleneck flags surfacing automatically, and a phase-change feed updating from the SQS pipeline.

The dashboard runs on QuickSight, connected directly to the RDS database, with role-based sharing for the executive team. Because it’s pointed at the same source of truth that the Lambda API and Scoop sync write to, the visualization is always live — no manual refresh, no spreadsheet exports, no “what’s the latest as of yesterday” meeting.

05 / OUTCOMESWhat Shipped

Stack

06 / TAKEAWAYThe Pattern Behind Every “Unified Data Platform” Engagement

Every operationally-mature business hits the same wall: data lives in five systems, none of which were designed to talk to each other, and leadership is making decisions on stale spreadsheets pulled together by hand. The fix is rarely “buy a better CRM” or “hire one more analyst.” The fix is a data infrastructure layer that unifies the sources, exposes them through a real API, and surfaces the metrics leadership needs to actually see what the business is doing.

Solerro’s COO put it directly: “It’s really hard to architect a solution that brings everything together. And that’s what Brian and his team do.” Same instinct shows up in every clean energy engagement, every healthcare data platform, every fintech consolidation we ship.