Site management, from the inside

The problem

The client rings to ask how the job is going, and whoever answers cannot say without ringing the foreman. Then someone sends a photo on WhatsApp and no record of anything survives. What is missing is not a pretty app: it is a place where the state of the job exists, and a way to show it to the client without showing them the margins.

Phases come out of the measurement

When a quote is won, the job is born from it: one phase per trade the quote contains, and each phase's weight is the slice of direct cost that trade is worth. A job where painting weighs 14% weighs that because the measurement says so. Percentage complete is the sum of the weights times what is done in each phase, which is why closing four painting tasks moves the needle less than closing one structural task.

sql

-- O peso de cada fase e a fatia do custo directo que a
-- especialidade vale na medicao. Ninguem o escreve a mao.
for v_cat in
  select l.category,
         sum(round(l.qty * l.material_cents)
           + round(l.qty * l.labour_hours * l.hourly_cents)
           + round(l.qty * l.equipment_cents)) as cents
    from obra_quote_lines l
   where l.quote_id = p_quote
   group by l.category
  having sum(...) > 0
   order by min(l.position)
loop
  insert into obra_phases (project_id, name, ord, weight_pct, budget_cents)
  values (v_p.id, v_cat.category, v_ord,
          round(100.0 * v_cat.cents / v_total, 2), v_cat.cents);
end loop;

The portal does not hide: it projects

obra_tasks and obra_updates have RLS on and no read policy at all. No select returns them, wherever it comes from, including the public API. The only door is a security definer function returning JSON with phases, percentage and the notes marked visible. Open the browser tools on the portal and you do not find hidden columns: you find what the function returned, and nothing else exists on that side.

sql

-- Sem politica de leitura, estas tabelas nao respondem a ninguem.
alter table obra_tasks   enable row level security;
alter table obra_updates enable row level security;

-- A unica porta do cliente: uma projeccao, nao um select filtrado.
create function obra_project_public(p_room text, p_code text)
returns jsonb security definer stable as $$
  select jsonb_build_object(
    'code', v_p.code, 'site', v_p.site,
    'progress_pct', (select round(sum(weighted), 1) from obra_progress(p_room)
                      where project_id = v_p.id),
    'phases',  (select jsonb_agg(...) from obra_phases where project_id = v_p.id),
    'updates', (select jsonb_agg(...) from obra_updates
                 where project_id = v_p.id and client_visible));
$$;

The rules live in the database

A phase does not close with open tasks, and the check is inside the function, not in the button. A blocked task is not a done task: marking it done clears the blocking reason, because holding both at once is a contradiction nobody can read later.

sql

-- Uma fase nao fecha com tarefas por fechar.
if p_status = 'concluida' and exists (
     select 1 from obra_tasks where phase_id = p_phase and done_at is null) then
  raise exception 'ha tarefas por fechar';
end if;

-- Feita e bloqueada ao mesmo tempo e uma contradicao: marcar feita
-- apaga o motivo do bloqueio.
update obra_tasks
   set done_at = case when p_done then coalesce(done_at, now()) else null end,
       blocked_reason = case when p_done then null else nullif(btrim(p_blocked), '') end
 where id = p_task and room_code = p_room;

The tables

Four tables. Two answer any read; the other two answer nobody from outside, and that is the difference between a portal and a screen with columns hidden.

Table	What it holds
obra_projects	The job, with the budget snapshotted at the moment it was won.
obra_phases	One per trade in the quote, each with its own weight and budget.
obra_tasks	RLS on, no read policy: only the functions answer.
obra_updates	The diary. Each entry carries client_visible, and that column is what the portal filters on.

What I would do differently for a real client

Real photographs, with storage and thumbnails, instead of an entry type called photo. A notification to the client when there is news, by email or SMS, instead of them having to go and look. Progress certificates for phased invoicing, which is how a construction job actually gets paid. Planned dates per phase with a simple Gantt. And authentication, with the client entering through a link of their own instead of a code that can be guessed.