OPI Project Management

A platform instead — from the database up, on one schema.

Projects and tasks. Budgets and budget-versus-actual. Expenses, invoices and invoice OCR, payments and outstanding balances. Subcontractor commitments and pay applications. Materials, equipment, field reporting, health and safety.

It serves three audiences that share almost nothing, through three front doors onto one database. The field files a report from a tablet on site. The office reconciles it against a budget line on a full screen. Clients and subcontractors each get their own portal, and each sees only its own slice.

The site's own surface, cropped to the cards: construction log, HSE events, equipment on site, personnel, materials. An office tool would carry none of these.

The same project at a desk — the Commercial Hub through its first lens. Two named lenses rather than one number, each stating what it answers and what it does not.

Deciding which of those a given screen is for is most of the design work. Every one of them gets a guided tour, built per context — including the subcontractor, who is furthest from the office and least likely to have chosen this software. There is a demo project the platform generates for itself, so nobody learns on live data.

A later layer is the business console, behind its own login inside the same application: CRM and leads, marketing, a content pipeline with its own monthly spend cap, a support desk, per-tenant observability. It is there because running the platform and running the business around it turned out to be one job.

The requirements never stopped moving, and neither did the person building them.

This was a first real project, started straight out of a degree, inside an industry that had to be learned at the same time as the software. What made that survivable was putting usage first and asking the same question of every screen: how does this work on a site, and how does this work at a desk. The two answers are rarely the same, and getting them wrong does not produce a bug. It produces a feature nobody opens.

Some answers took whole detours. The client portal came in three passes across a year, each partly redoing the last. The commitments and subcontractor layer arrived in one commit that touched six hundred files.

The record of that is not how many commits there were but what they went back to. Things were built, used, and rebuilt when use showed why — the client portal three times, the invitation flow three times in eight weeks, a PDF export three ways in two days. And from the end of 2025 the reason started being written down before the fix: a review of how three screens computed money, an audit of every place a price was stated, a diagnosis of why an account could not be deleted. By the time the commercial layer landed it arrived as a written specification, then a plan, then sixty-eight migrations in one commit — thirty of them rollbacks.

What it looks like in the finished thing: the project's financial view does not show one number. It shows two named lenses — what has actually been paid, and what unpaid invoices still claim — each stating what question it answers, what it does not, and how moving money changes the other. Cash against accrual is the commonest way a construction budget gets misread. The screen argues with the misreading before anyone can make it.

The sharpest instance was the day the founding assumption expired.

The system was built for one company, in-house. There was nothing to isolate, so there was no reason yet to know much about row-level security. When it went multi-tenant that stopped being true, and the boundary had to go in underneath a system that was already running — across the whole schema, in seven numbered migrations, in a single day.

It broke immediately, in a way that is only obvious afterwards. The policies read a user's company through a helper function. The helper reads the profile table. The profile table has a policy. The policy calls the helper. A query for one row recursed until Postgres gave up.

The rule the schema still runs on came out of that afternoon: the four tables the helpers themselves read check the signed-in user directly, and may never call a helper. Everything else may. The dependency runs one way, so it cannot come back around.

-- Problem: Helper functions query base tables (profile, user_roles) which
-- triggers RLS evaluation, which calls helper functions again = INFINITE RECURSION
--
-- Solution: Base tables (profile, user_roles, client_contacts, business_users)
-- CANNOT use helper functions in their RLS policies. Must use direct checks.

CREATE POLICY "…" ON profile
  FOR INSERT
  WITH CHECK (
    -- check the role directly, without a helper function
    EXISTS (
      SELECT 1 FROM user_roles ur
      WHERE ur.user_id = auth.uid()
        AND ur.role_id IN ( /* the privileged roles */ )
    )
  );

That is the lesson the whole project taught, and it was not get it right the first time. It was: build so that the thing you have not learned yet can be put in underneath, without taking the system down while you do it.

In production use since the start of 2024, and not finished — on purpose.

What began as one firm's internal tool became a product with a business attached to it, and the business console is where that ended up as software: the sales pipeline, the marketing, the support desk and the books all became things the platform had to do, so they became things the platform does.

Iteration has not stopped and is not supposed to. The small things nobody notices when they are handled are the same ones everybody notices when they are not.