Airtable for Billable Work: Time Tracking, Invoicing, and a Clean Handoff to QuickBooks or Xero

Plenty of agencies, studios, and professional-services teams run delivery in Airtable and still bill out of a spreadsheet. The projects, tasks, and people are already in the base; the hours live in a timer app; someone copies totals into an invoice at month end and quietly writes off whatever does not reconcile. That gap is where margin goes.

This tutorial builds the missing layer: a time-entry ledger, rate resolution you can defend to a client, retainer and budget burn-down, a repeatable invoice run that produces draft invoices, and a one-way handoff into QuickBooks or Xero. It assumes you already have Clients, Projects, and People tables roughly along the lines of our data modelling guide.

The rule that makes the rest work

Never store "hours this month" as an editable number. Store individual time entries and roll them up. It is the same discipline as the stock ledger in our inventory tutorial: an append-only record of events, with every total derived from it. An editable Hours field on a project has no history, no attribution, and no way to answer "which hours were on invoice 1043?" six months later when the client asks.

Step 1: the tables

Time Entries — one record per person per task per day. Not per week.

Field	Type	Notes
Entry ID	Autonumber / formula	Human reference
Person	Link to People	Who did the work
Project	Link to Projects	Required
Task	Link to Tasks	Optional but useful
Date	Date	Date of work, not date entered
Hours	Number (precision 2)	Decimal hours, 0.25 increments
Notes	Long text	Goes on the invoice — see below
Billable	Checkbox	Default on, unticked for internal and rework
Rate applied	Currency	Written once, at approval
Amount	Formula	{Hours} * {Rate applied}
Status	Single select	Draft, Submitted, Approved, Invoiced, Written off
Invoice	Link to Invoices	Empty until the invoice run

Rate Cards — one record per Client × Role (× optional Project override), with Effective from and Effective to dates and a Rate currency field.

Invoices — one record per invoice: Invoice number, Client, Period start, Period end, Status (Draft, Approved, Sent, Paid, Void), Subtotal (rollup), Tax, Total, External ID, Sync status, PDF.

Invoice Lines — optional but recommended: one record per billable grouping (project, role, or task), linked to both the invoice and the entries that make it up. Clients rarely want 340 individual lines; they want seven, with the detail available on request.

Step 2: rate resolution, done once

The mistake is a lookup from the client to the entry. Lookups are live: raise your rates in January and every historical entry silently reprices, including the ones you already invoiced. Your base and your accounting system then disagree and you will never work out why.

Instead, resolve the rate once, when the entry is approved, and write it into the static Rate applied currency field. An automation does this:

• Trigger: when Status becomes Approved.
• Find records in Rate Cards where client matches, role matches, Effective from ≤ entry Date, and Effective to is empty or ≥ entry Date.
• Script or conditional step: prefer a project-level override if one exists, else the client × role rate, else the role's default. If nothing matches, set a Rate issue flag and stop — do not fall back to zero silently.
• Update record: write Rate applied.

A short Run script step is cleanest here because the precedence logic is a list, not a branch tree. Keep it under the 50-record write batch rule from our scripting guide.

To check your precedence logic before you trust it, build a view of approved entries where Rate applied is empty or zero. It should always be empty. If it is not, you are about to under-bill.

Step 3: capture that people will actually use

A timesheet nobody fills in is worse than no timesheet, because the gaps look like idle capacity. Three patterns that work:

1. A mobile-friendly interface form. One page: project picker filtered to the person's active projects, date defaulted to today, hours, notes. Three taps. See the mobile field choices in our field teams tutorial.
2. A start/stop button. A button field running an automation that creates an entry with the start time, and a second that stamps the end and computes hours. Convenient, but it produces junk entries when people forget to stop; add a nightly automation that flags any entry running longer than ten hours.
3. Weekly grid entry. A grouped grid view filtered to Person is current user and Date is within the last seven days. Power users prefer this to any form.

Whatever you pick, add a Reminder automation on Friday afternoon that messages anyone whose approved hours for the week are below a threshold. Chasing timesheets is the actual job; the base should do it.

Write notes for the client, not for yourself. "Fixed the thing" becomes a line item the client reads two weeks later. Make Notes required on the form and give people three good examples in the field description. This single habit removes most invoice disputes.

Step 4: approval and the write-off decision

Approval is where margin is defended, so make it a real step rather than a checkbox someone ticks in bulk. A simple chain — Submitted → project lead reviews → Approved — follows the pattern in our approval workflows post.

The reviewer has three options per entry, and the base should make all three equally easy: approve as billable, approve as non-billable (training, internal, goodwill), or write off with a reason. Add a Write-off reason single select — Rework, Scope goodwill, Estimate error, Admin — and report on it quarterly. The reasons tell you whether you have a delivery problem or an estimating problem. A write-off field with no reason tells you nothing.

Once an entry is Invoiced, lock it. Airtable has no field-level lock, so enforce it with an automation that reverts edits, or more practically with an interface that hides the fields and a view permission setup that keeps delivery staff out of the raw table. Record the attempt either way.

Step 5: budgets, retainers, and burn

Two different models, two different maths.

Fixed budget / time-and-materials cap. On Projects: Budget amount (currency), Billed to date (rollup of Amount where entries are Invoiced), Approved unbilled (rollup where Approved), and Burn % as a formula over budget. Add a Burn status formula returning OK, Watch (over 75%), or Over so you can colour a view and trigger an alert. The alert matters more than the dashboard: an automation that posts to the delivery channel the first time a project crosses 80% gives a project manager time to raise a change order instead of a discount.

Monthly retainer. Create a Retainer Periods table: one record per client per month with Included hours, Rollover hours in, Used hours (rollup of approved entries in the period), Overage hours (formula), and Rollover out subject to your cap. Link entries to the period with an automation on approval that matches client and month. Retainers quietly break when rollover rules live in someone's head; put them in a formula and in the client's contract, and make sure both say the same thing.

Step 6: the invoice run

Make this a repeatable procedure, not a monthly act of heroism.

1. Freeze. A view Billing – ready filtered to Status is Approved, Billable is ticked, Invoice is empty, and Date ≤ period end. Anything still Submitted on billing day either gets approved or waits for next month — do not hold the whole run for one person.
2. Generate. A script, triggered from a button on the client record, groups the ready entries for that client, creates one Invoice record, creates Invoice Lines per project and role, links the entries, sets their Status to Invoiced, and stamps the invoice number from a counter table. Idempotency is the thing to get right: the script must select entries by the filter and link them in the same pass, so a second click finds nothing left to bill rather than duplicating. The rerun-safety patterns in our automation monitoring post apply directly.
3. Number properly. Sequential, gapless, from a single counter record you increment in the script. Do not use Airtable's autonumber for a legal invoice number — duplicating a base or deleting a draft record will put holes in the sequence.
4. Review. An interface page per draft invoice showing the lines, total, burn against budget, and an expandable list of underlying entries. One human reads it before anything leaves the building.
5. Produce the document. Generate the PDF with the approach in our document generation tutorial, or let the accounting system render it — see below.

Step 7: handing off to QuickBooks or Xero

Decide the system of record first, and write it down: Airtable owns hours and draft invoices; the accounting system owns issued invoices, tax, payments, and ledgers. Every integration problem in this area comes from blurring that line.

That gives you a deliberately boring integration:

• One way out. On invoice Status → Approved, push a draft invoice into QuickBooks or Xero via their API (through a Run script step, Make, or n8n — the trade-offs are in our automation tooling comparison). Send customer reference, line items, period, and your invoice number as the external reference.
• Store the returned ID. Write the accounting system's invoice ID into External ID immediately. That field is your idempotency key: if it is non-empty, never create again. This is the same discipline as the shared-ID matching in our CRM integration guide.
• Narrow way back. A scheduled job, hourly or nightly, pulls payment status for invoices that are Sent and not Paid, and updates Status, Paid date, and Amount paid. Nothing else comes back. Pulling the whole ledger into Airtable creates a second, worse accounting system.
• Never compute tax in Airtable. VAT, sales tax, reverse charge, and multi-jurisdiction rules change under you. Send net lines and let the accounting system apply tax codes.
• Match customers explicitly. Keep the accounting customer ID on the Clients record. Name matching will eventually invoice "Acme Ltd" and "Acme Limited" as two customers.
• Log every call. A Sync Log table with timestamp, invoice, direction, status code, and response snippet. When finance asks why invoice 1043 is not in Xero, you want an answer in thirty seconds.

Add a reconciliation view that lists approved invoices with an empty External ID older than an hour. That one view catches almost every silent failure this integration can have.

What to watch once it is live

• Unbilled approved hours by age. Anything over sixty days is usually never getting billed. Report it weekly.
• Realisation rate. Billed amount divided by (approved hours × standard rate), per project. It is the number that tells you which clients are actually profitable.
• Entries with no notes, and entries logged more than fourteen days late — both are leading indicators of a disputed invoice.
• Rate issues. The view from step 2, checked before every run.

When not to build this in Airtable

If you need payroll, multi-currency revenue recognition, statutory reporting, or an auditor's trail across general-ledger accounts, use a proper system and sync the hours into it. Airtable is excellent at the messy, firm-specific half — capture, approval, rates, retainer rules, and client-visible context — and a poor substitute for a ledger. The honest signals that you have gone too far are the same ones in Outgrowing Airtable.

---

BaseBrainers builds billing and operations bases like this one, including the accounting integrations, as part of our Airtable for business operations work. If your hours and your invoices currently disagree, tell us about it.