One base where a bid, its overages and its invoices finally know about each other

Stop 1 · Scatter

The numbers were all there, just never in the same place

Every part of a job existed in a spreadsheet. None of the spreadsheets knew about each other.

Bids in one file, the project list in another, overages in a third, invoices in a fourth, and a rate card pasted in wherever it was needed. Each was maintained by hand and each was correct on its own. The cost was in the joins: to answer how a live job was doing you opened four files, matched them on a job number typed four times, and recalculated the margin yourself. That answer went stale the moment you closed the tabs.

Spreadsheets were not the wrong tool because they are spreadsheets. They were the wrong tool because this operation is relational, and a grid cannot hold a relationship — only a copy of one.

Stop 2 · Map

We drew their real process before designing a single table

First session was not about Airtable at all. It was about how a job actually moves through the studio.

They walked us through it end to end: how a lead becomes a bid, how a bid becomes several bid options with one marked as the frontrunner, what happens the moment something is awarded, how hours get booked against roles week by week, when an overage is raised and who signs it off, and how invoicing splits into deposit, milestone and final. We wrote that down as their process, not as a schema. Almost every design decision later came straight off that map.

Stop 3 · Schema

Tables shaped like the work, not like the old tabs

Sixteen tables, each one a noun from their process: clients, roles, rates, bids, overages, hours, invoices.

The temptation with a migration is to make one table per spreadsheet and call it done. We went back to the map instead. Bid options became their own table so a project can carry several and still have a single approved one. Weekly hours became rows rather than columns, so a job can run eleven weeks without anybody widening a sheet. Rates split into a default rate card and a per-client override table, because that is genuinely how they price.

Weekly hours as rows instead of columns is the single decision that has aged best. Every alternative we sketched needed schema changes to add a week; this one needs a record.

Stop 4 · Link

Linked records are the whole reason for the move

Once the tables point at each other, a job number is written once and every table that needs it reads it.

A project links to its client, its bid options, its overages, its timeline assignments, its weekly hours, its cost line items and its invoices. From those links, rollups do the arithmetic that used to be manual: approved bid total, producer budget, actual and forecast cost, hours projected against hours booked, percent of budget spent, bid margin, EAC margin. None of it is typed. Change an hour on a timeline assignment and the margin on the project moves with it.

Stop 5 · Bid

The Bid Builder prices a job role by role

Pick roles, assign vendors, enter hours. The rate card does the pricing and the margin appears as you type.

A bid opens against a project and a rate type. Each labour row is a role and a vendor, and the internal and external rates fill themselves in from that client's rate sheet rather than being pasted from a document. Hours are the only number a producer types. Underneath, the bid keeps a live subtotal, rep fee percent and amount, markup or discount, gross profit, blended margin and a final bid amount — so a producer can see what a rate change does to the margin before they send it, not after.

Rates resolve through the client rate table, so re-carding a client updates every future bid and leaves already-approved ones untouched. That was a hard requirement — historic bids have to stay what was actually agreed.

Stop 6 · Award

Awarded, then locked

A project can carry several bid options. Exactly one becomes the number the job is measured against.

Options sit side by side with a frontrunner flag while the pitch is live. When one is awarded it is marked and locked: its total becomes the project's approved bid and its labour rows become the producer budget that everything later is compared to. Locking matters more than it sounds — the baseline is what makes budget-spent and margin mean anything, and a baseline that can still be edited quietly is not a baseline.

Stop 7 · Run

The Project Tracker is where the job actually lives

One screen: top sheet, financials, team, labour by week, expenses, bid options, overages and invoices.

Labour rows seed themselves from the awarded bid, then producers adjust hours per week as the job moves — adding a week when it runs long, deleting one when it does not. Beside projected hours sit actual hours, so budget variance and EAC cost update as the work happens rather than at the post-mortem. Overages are raised against the same project, carry their own status and awarded cost, and roll into the approved total once signed off, which is precisely what used to get lost in email.

Add Week and Delete Week exist because the first build made weeks a fixed span set at award. Real jobs slip. The interface had to let a producer change that without anyone touching the schema.

Stop 8 · Books

Invoicing and the accounting view fall out of the same data

Milestones are generated when a job is awarded, and accounting reads the same records rather than a copy.

Awarding a job creates its invoice milestones on the agreed terms — a deposit and a final, or thirds, plus a line for every approved overage — each starting as pending with the amount already filled in. From there the invoice rows carry invoice number, PO, date sent, payment terms, amount received and balance due, which gives total invoiced, invoiced percent and pending payment for free. The accounting interface is a different view of those same rows, so the books and the producer's screen cannot disagree.