In production2023-11 – nowFull-stack developer, owns the integration layer

Metas

Financial dashboards for many companies, each with a different access to give: an API, a database, or nothing at all.

AnonymizedA consulting platform that tracks financial and operational indicators for multiple client companies, from mid-size firms to large multinationals, across different industries. The name is withheld because authorization was never requested. Every constraint, decision and number in this case reflects what was actually built.

  • rails
  • react
  • postgresql
  • oracle
  • devise

The problem and constraints

Metas exists to turn a client company’s own financial and operational numbers into a single, comparable score, month over month, across every area of the business and every company in a group. The client sets a target and a weight for each indicator; a person or a system reports what actually happened; the platform turns that into a score that management can compare across dozens of areas and, for a multi-company client, across every subsidiary at once.

The first constraint was that “get the number” means something different for every client, because no two clients run the same systems or grant the same access. Some clients have a modern API and are happy to hand out a key. Some run older, on-premises software with no API at all, but will allow a read-only database account behind a VPN. Some, for regulatory or infrastructure reasons, will not open any door at all beyond what a person can already see on their own screen. A platform that only knew how to call an API would simply be unable to serve a meaningful share of the clients this product exists for.

The second constraint was that a client’s organization rarely maps onto a flat list of “companies.” A holding company owns subsidiaries, a subsidiary has its own business units, and each level needs its own indicators, its own targets, and its own roll-up into the level above it, while a single person managing the whole group needs to see all of it at once without switching between separate logins for each entity.

The third constraint was that a score feeds real decisions the client’s own management makes about a team’s performance and, at some clients, that team’s compensation, far beyond a cosmetic dashboard number. Getting a formula subtly wrong, or letting a stale or manually-fudged number slip in unnoticed, doesn’t just look bad on a chart, it produces a wrong real-world decision downstream.

The last constraint was access itself, in the most literal sense. Reaching a client’s real numbers sometimes means normal software work (calling an endpoint), sometimes means acting as a network engineer (standing up and maintaining a VPN tunnel into infrastructure nobody on the client’s side can fully explain), and sometimes means acting as a data archaeologist (reverse-engineering what a legacy system’s own screens are actually showing, when the system itself won’t tell you).

Architecture

A client’s organization lives as a self-referential tree of Company rows: a holding company, its subsidiaries, and their business units are all the same kind of record, distinguished only by a category field and a parent pointer, so the same schema serves a client with one legal entity and a client with a four-level group without any special-casing. Users are attached to one or more companies through a join table, and an ordinary user’s access is scoped strictly to the companies they’re actually attached to. One role sits outside that scoping entirely: a cross-company administrator who can see every client’s structure at once, the role a consultant managing several client relationships actually uses day to day.

Underneath every company sits a set of Areas, and underneath every area a set of Indicators, each with a weight and one of five scoring formulas (a plain ascending ratio, a descending one, a cost-savings variant, and two deviation formulas for over- and under-shooting a target). An indicator’s monthly value can arrive in one of two ways, tracked by a single is_manual flag: someone types it into the dashboard by hand, or it’s written by whichever integration mechanism that specific client uses. Both paths feed the exact same downstream formula engine, so the platform never has to know or care, at scoring time, where a number originally came from.

That second path, the integration itself, is where most of the real engineering cost lives, and it’s three separate mechanisms, chosen per client based on what that client’s own systems will actually allow a third party to do. Where a client exposes an API, the integration is a straightforward scheduled call. Where a client permits database access but has no API, the integration is a read-only query running over a VPN tunnel built specifically for that client. Where neither exists, and a client’s own staff can only see numbers through their own software’s screens, the integration is RPA: driving that software the way a person would, to extract the same numbers a human operator would have typed in by hand anyway.

Once a value lands, an area’s own score follows a deliberately blunt rule: the weighted result only counts at all if the area’s aggregate outcome clears 80% of its target; short of that, the area’s score is exactly zero rather than a fraction of one. That zero, or that score, rolls up through the company tree the same way the company structure itself does, so a holding company’s dashboard reflects real performance at every subsidiary underneath it, beyond whatever happened to be typed in most recently.

A missed target isn’t left as a bare number, either. Whoever owns an indicator that came up short attaches a written justification for that specific month, and can back it with supporting evidence, a document, a screenshot, a spreadsheet, uploaded and tied to that same indicator and month. Neither the justification nor the evidence changes the score itself; the formula and the 80% floor run exactly the same either way. What they change is what a reviewer sees sitting next to a red number: not just that a target was missed, but the specific, dated explanation the area’s own owner gave for why, on the record, the same month it happened rather than reconstructed from memory later.

Architecture

Metas: how an indicator's value reaches a company's score An indicator's monthly value comes from one of two sources: someone types it in by hand, or it arrives through an integration, an API call, a read-only database query over a VPN, or RPA driving the client's own software, chosen per client depending on what that client's systems allow. Either way, the value is scored against its weight and one of five formulas. That score rolls up into its area, which is zeroed out entirely if it falls short of 80% of target, and areas roll up into a self-referential company tree (holding, subsidiary, business unit). One role sees every company at once; every other role stays scoped to its own. Manual entry typed in by hand Integration API, DB query + VPN, or RPA picked per client's access Indicator weight × one of 5 formulas monthly value, is_manual flag Area zeroes below 80% of target Company self-referential tree holding → subsidiary → unit one role sees every company at once; every other role stays scoped to its own

Decisions

DECISION 01/04 · One of three ways in, chosen per client

ChosenReach a client's real numbers through whichever of three mechanisms that client's own IT actually allows: a plain API call, a read-only database query over an IPsec VPN tunnel, or RPA driving the client's own reporting software when neither an API nor direct database access exists

Discarded insteadRequire every client to expose the same kind of access (an API) before onboarding, and turn away the ones that can't

Client IT maturity and policy varies enormously in practice: some expose a clean API, some only permit VPN plus a read-only database account, and some (a hospital, in the hardest case so far) can barely support that much. Refusing a client for not having the right kind of access would mean losing exactly the business this product exists to serve

Cost acceptedThree separate integration codepaths to build, test, and keep working, each with its own failure mode: rate limits and schema drift on the API path, tunnel and credential maintenance on the VPN path, brittleness to any UI change on the RPA path

DECISION 02/04 · Company hierarchy as a self-referential tree

ChosenModel a client's organization as a single Company table with a parent_id pointing at another row of the same table, plus a category (holding, subsidiary, business unit) rather than one purpose-built

Discarded insteadSeparate tables for holding, subsidiary, and business unit, each with its own schema and its own queries

Real client org charts don't share one shape: some clients are a single legal entity, others are multi-level holding structures three or four layers deep. A fixed number of hardcoded levels breaks the first time a client's structure doesn't fit it

Cost acceptedAny query that needs 'every company under this one' has to walk the tree recursively instead of running a plain join, and nothing at the database level stops a company from accidentally being set as its own ancestor

DECISION 03/04 · A score below the target floor is zero

ChosenAn area's total score is exactly 0 whenever its aggregate result falls short of 80% of its target, even when several individual indicators inside that area performed well

Discarded insteadAward a score proportional to how close the area got to its target, so a near-miss still earns most of the points

This score feeds evaluation and compensation decisions the client's own management makes about that area's team. A graduated scale blurs exactly the line that decision needs; the client chose an explicit floor instead

Cost acceptedAn area at 79% of target scores identically to one at 10%, which can look unforgiving to a team that missed by very little. That's the client's own deliberate trade-off

DECISION 04/04 · Manual and integrated values share one pipeline

ChosenEvery indicator carries a single is_manual flag rather than living in a separate table depending on where its value comes from: a manual indicator is typed in by a person through the dashboard, a non-manual one is written by whichever integration mechanism that client uses, and both flow through the exact same weight and formula logic afterward

Discarded insteadKeep manually entered and integration-sourced indicators in separate tables, each with its own scoring code

From the scoring formula's point of view, an indicator's value is an indicator's value regardless of where it came from. One table means one formula engine, one weight validation, and one monthly rollup, instead of maintaining the same logic twice and keeping both copies in sync forever

Cost acceptedA bad integration write looks identical to a bad manual entry at the database level. Catching that one client's automated feed has gone stale depends entirely on someone actually reviewing the numbers; the schema has no way to enforce that on its own

Invariants

  • A group of sibling indicators whose weights don't sum to exactly 100 is detected and surfaced, never accepted silently

    Guaranteed byUser#have_goals_incompleteds? groups an indicator set by level, sums the weight in each group, and flags any group that doesn't land on exactly 100

  • A role's admin or manager standing is computed from its place in the hierarchy, never stored as an independent flag that could drift out of sync

    Guaranteed byRole#admin? and Role#manager? read parent_id and label at call time (a root role that isn't 'analista' is a manager; a role whose parent is 'admin' is itself admin) instead of caching the answer anywhere

  • An ordinary company-scoped user never sees a company it doesn't have an active company_users row for

    Guaranteed byUser#company and User#companies resolve strictly through the company_users join, filtered to active: true; the one deliberate exception is the separate saga_admin scope, built specifically to cross that boundary

  • An area's score is either the full weighted result or exactly zero, never a value in between when the area is short of its target

    Guaranteed byArea#calcular_indicators only computes total_nota when total_real clears the 0.8 premise threshold; otherwise it returns 0 for both total_nota and total_geral

  • An indicator that is a child of another indicator is never required to declare its own weight

    Guaranteed byvalidates_presence_of :weight, unless: :child? on the Indicator model, app/models/indicator.rb

What broke

Symptom
The hospital integration, the hardest one built so far, needed a plain database connection over an IPsec VPN tunnel instead of the usual API call: the hospital's own network team could never open a port to expose an API, so a direct read-only path into their Oracle database was the only option left
Root cause
The hospital had no DBA and no network engineer of its own, so nobody there could actually document how their network or database were laid out; both had to be mapped from the outside, from zero, with no help from whoever originally built their systems. Worse, once the connection was up the numbers didn't match: the hospital's own software computed several values on the fly, inside database views, and never persisted the computed result as a plain column. Querying the underlying tables directly, the only path an external read-only connection has, returned the raw inputs to those calculations instead of the values the hospital's own screens actually displayed
Fix
Every affected view's calculation was reverse-engineered by hand and rebuilt as an equivalent query, checked field by field against what the hospital's own system showed for the same period, one query rewritten and re-verified per day for about a month until every output matched exactly
Prevention
The network topology and the database structure, both fully undocumented on the hospital's side going in, are now completely mapped and written down on this side, so the same integration doesn't have to be reverse-engineered from zero again if it ever needs to change

Results

3integration mechanisms, chosen per client's own accessauthor's direct account: API, a database query over VPN, or RPA, none of the three implementations live in this repository
5scoring formulas supportedIndicator.calculation_type enum, app/models/indicator.rb: asc, desc, cost_savings, positive_deviation, negative_deviation
80%of target an area must clear before its score counts at allArea#calcular_indicators, app/models/area.rb: atende_premissa = total_real >= 0.8
35database migrationscount of files in db/migrate/, 2025-09-01
10domain modelscount of files in app/models/, excluding the base ApplicationRecord class, 2025-09-01
45React componentscount of files under app/javascript/components/, 2025-09-01

Full stack

Backend

  • Ruby on Rails
  • Devise (authentication)
  • Pundit (authorization policies)

Frontend

  • React
  • Chakra UI
  • Redux Toolkit
  • ApexCharts / Highcharts

Data

  • PostgreSQL (application database)
  • Oracle (read-only, over client integrations)

Infra

  • IPsec VPN tunnels (per-client, on demand)
  • RPA against client-side software

Interested in a project like this? Get in touch.

Get in touch