Skip to content

QuickBooks integration plan

Forward-looking design doc covering the three QuickBooks surfaces that touch our operations: time tracking, time off, and invoicing. Written as a reference for both the user (Jeremy) and any worker that picks up implementation tickets — every section includes the open decisions that must be answered before code is written.

This is a planning document, not a build spec. Tickets get filed once the decisions in this doc are locked in.

TL;DR

  • Time tracking → partially shipped (#475-#483). Daily field log hours sync to QB Time after PM approval. Lab hours not yet captured per test.
  • Time off → table exists but no QB sync. We have time_off_days for nag suppression (#485). QB Time has its own time-off request workflow that our scheduler doesn't consult.
  • Invoicing → nothing. No QB Invoices API integration. No per-test pricing in the system today. No QB Customer / Item mapping. No invoice-generation cron.

Four integration points to add, prioritized by ROI/risk:

  1. Pull time off FROM QB Time → into our time_off_days (small, scheduling value)
  2. Quote generation from our price book (medium, sales-cycle speed)
  3. Push lab-test invoice lines TO QB Invoices (medium, real revenue value)
  4. Push field-hours invoice lines TO QB Invoices (medium, depends on the drive-time decision)

Quotes (#2) became a natural addition once D2 landed on "our own price book" — if we own the pricing, we can quote a prospect the moment they ask, without waiting for QB items to be set up. Same tables, two outputs: send a PDF/email to a prospect, or generate an invoice for completed work.

Bigger update (post-D7): QB invoice sync is deferred entirely for v1. Invoice records live in be-platform only. Trigger pattern also flipped: instead of "PM generates invoice when ready" (D4), the workflow is now per-test "Add to invoice" action by a PE or Admin, with one "open" invoice per client auto-aggregating lines until manually closed. QB push lands in a future phase once we've lived with the in-house version and understand the actual shape.

Decisions to lock in before any tickets

These shape the schema more than the code. Each has my recommendation in bold, with a one-line reason. Reply inline before we file tickets.

D1. Time-off direction

QB Time is the employer-of-record workflow. Do we treat it as the source of truth and pull approved time off into our system, or do we keep our self-attest time_off_days and push to QB Time?

  • (A) Pull from QB Time → time_off_days. ← RECOMMENDED. QB Time already has the approval workflow + the employees know how to use it; we just consume. The self-attest path from #485 stays but becomes the fallback for engineers who haven't filed in QB Time.
  • (B) Push our time_off_days → QB Time. Doubles up the workflows.
  • (C) Both directions. Overengineered for v1.

We will do (A).

D2. Pricing model

Where do test/service rates live, and how do they vary per client?

  • (A) Flat rate per test_type. Cheapest. Doesn't match reality if any clients have negotiated contract pricing.
  • (B) QuickBooks Service Items as source of truth, with optional per-customer overrides. ← RECOMMENDED. Pull from QB, cache locally, let admin override per (test_type, customer) pair. Matches how QuickBooks is already used.
  • (C) Maintain our own price book + push to QB. Doubles up.

(C)

D3. Drive-time billing

Field engineers drive to and from sites, and separately drive to pick up cylinders. How do these show up on the invoice?

  • (A) Rolled into one "On-site hours" line per day. Simplest.
  • (B) Separate "Travel" line item at a separate rate. ← RECOMMENDED for client transparency. Many clients audit invoices for itemization; rolling everything in invites disputes.
  • (C) Three line items: drive there, on-site, drive back. Granular to the point of being noisy.

If (B): we need a drive_minutes column on daily field logs separate from time_billed_hours (or rename to on_site_hours). Cylinder-pickup driving becomes its own daily-log subtype or a new table entirely — see open question Q5.

We will do (B).

D4. Invoicing cadence

When do invoices generate?

  • (A) Monthly batch on the 1st of each month. ← RECOMMENDED. Predictable cadence for clients + AR. Engineers + PMs know the cutoff. Matches how most testing labs bill.
  • (B) Per-project at completion. Variable cadence, AR forecasting becomes harder.
  • (C) Real-time on test completion. Invoice noise; clients hate it.
  • (D) Manual ("PM hits Generate Invoice when ready"). Highest flexibility, highest forgetting-to-bill risk.

Hybrid options exist (auto-monthly with manual generate for exceptions) — likely the final answer but adds UI scope.

We will do (D).

D5. Invoice approval gate

By analogy to the daily-log approval gate (#478) — does a PM/admin review pending invoices before they push to QB?

  • (A) Yes — PM reviews, marks Approved, then push. ← RECOMMENDED. Invoice mistakes are public to clients; an approval step catches them. Mirrors how daily-log → QB Time hours works today.
  • (B) No — auto-push on monthly cron. Trusts the data fully. Faster but unforgiving.

We will do (A).

D6. Pour billing vs. cylinder testing

A concrete pour includes cylinder casts that flow into the lab as CONC-COMP material_tests. Both are billable events. How to avoid double-counting?

  • (A) Bill the field event (pour) for on-site hours; bill the lab tests (CONC-COMP, etc.) per test. ← RECOMMENDED. They cover different work (sampling on-site vs. compression in the lab).
  • (B) Bill only the pour event (lump-sum). Hides lab work from the client.
  • (C) Bill only the individual lab tests. Hides field work.

We will do (A).

D7. Voided / re-tested material tests

What about tests that were performed, billed, then re-run (e.g. failed gradation triggers a re-test on the same sample)?

  • (A) Bill the re-test as a new line. Catches actual work performed.
  • (B) Don't bill the re-test (treat as warranty work on the first test).
  • (C) Project-by-project decision — depends on the client contract.

Likely (C) but file as a follow-up. For v1, default (A) — engineering work was performed, bill for it; PM can credit-memo on the QB side if needed.

We will do (C).

D8. Invoice trigger model (supersedes D4)

D4 picked "manual generation". The actual workflow we want is even more granular:

Per-test "Add to invoice" action, surfaced to PE / Admin roles only, available once a test reaches a terminal status (complete / reviewed / delivered). The action does:

  1. Look up the client's current open invoice (status='open').
  2. If none exists, create a new open invoice for that client.
  3. Append the test as a line item (price snapshotted from service_items + per-customer override).
  4. Mark the test as invoiced.

There's at most one open invoice per client at a time. PE/Admin can mark it closed when ready to send to the client, at which point a new "Add to invoice" action would open another.

This pattern means engineers never face billing UI — adding to invoices is gated to senior roles only.

D9. Defer QB invoice sync

For v1, invoices live in be-platform only. No QB Invoices API push, no qb_customer_map, no outbox/drift detection on the invoice side. We need time to live with the in-house workflow before deciding what to automate.

QB Time sync (existing #475-#483) is unaffected — that keeps running. Time-off pull (Phase 1) also unaffected. The deferral only touches what was Phase 4-5's invoice push.

When QB invoice sync eventually ships, it picks up from the closed in-house invoice records — pushes a copy to QB, marks the local record as pushed.

Current state — what's already shipped

Time tracking (partial)

Shipped via #475-#483, PRs #497-#508:

  • OAuth 2.0 connection to QB Time, refresh-token store
  • One-way sync: approved daily_field_logs → QB Time timesheet entries
  • Per-engineer toggle: admin can flip "sync to QB Time" on/off per engineer (#483)
  • Drift detection: if a QB Time entry was hand-edited after our push, we refuse to overwrite — log to qb_time_drift_log and surface in admin
  • User + jobcode mapping admin UI (#477)
  • Sync outbox (qb_time_sync_outbox) with status + retries
  • Email digest on persistent failures (#481)
  • Admin dashboard: sync queue, drift log, OAuth health (#482)

Not yet shipped:

  • Lab test time tracking — material_tests have no worked_minutes field. See D2 in the open questions — likely a "skip unless we need it for cost accounting" decision.
  • Pull-direction sync of approved time off from QB Time (this doc's Phase 1).

Time off (local only)

Shipped via #485:

  • time_off_days table: user_id, off_date, reason, recorded_by_id, recorded_at
  • Self-attest UI on /lab/ banner: engineer clicks "I was off yesterday" → row inserted with reason='self_attested'
  • Admin UI on /admin/users/<id> to record PTO/sick on someone else's behalf
  • Daily-log nag detection skips engineers with a time_off_days row for the date

Not yet shipped:

  • QB Time pull → time_off_days (Phase 1 of this doc).
  • Scheduler awareness of time_off_days — currently the schedule UI doesn't check time-off when suggesting engineer assignments.

Invoicing

Nothing. No tables, no API client, no UI. Everything below is new.

Phase 1 — Time-off pull (QB Time → time_off_days)

Scope: smallest, highest scheduling value. Build first.

Tables

No new tables. Re-use time_off_days (#485). Two adjustments:

  • Add a source column to disambiguate origins: 
    source TEXT NOT NULL DEFAULT 'manual'  -- one of:
                                        -- 'manual'        (admin entry)
                                        -- 'self_attested' (engineer banner)
                                        -- 'qbtime'        (pulled from QB Time)
  • Add a qbtime_request_id nullable column so we can update if QB Time edits the request later (e.g. extends a vacation by a day).

Both additions are schema-additive, no data migration risk.

Sync direction

Pull only. QB Time is the source. Our time_off_days is the cache.

Cron

New bin/qbtime_time_off_sync.rs mirroring the structure of bin/qbtime_sync.rs. Fires once per hour (or daily — see open question Q1). Queries QB Time's time_off_requests endpoint with status=approved, upserts rows by (user_id, off_date).

Failure mode

Failed pull → log + retry on next cron tick. No user-visible surface needed beyond the existing admin dashboard's "last QB Time sync time" indicator (could be split into in/out indicators).

Acceptance

  • Approved QB Time off-day appears in time_off_days within 1 hour
  • Re-pulled day with edited reason updates the existing row
  • Cancelled QB Time off-day clears the local row (or marks revoked_at — TBD)

Phase 2 — Foundation: our price book

Updated to reflect D2 = C and D9 (defer QB sync). We own the price book. The QB push + qb_customer_map were moved to a future phase since D9 defers QB invoice sync.

Build before Phase 3 (quotes) or Phase 4 (invoices) lights up.

New tables

-- Our service items. Source of truth for billable line items.
-- Categories: 'test' (one-off per material_test), 'hours'
-- (time-based — on-site, travel, cylinder pickup), 'flat'
-- (lump-sum like mobilization, project minimum).
CREATE TABLE service_items (
    id                  INTEGER PRIMARY KEY AUTOINCREMENT,
    code                TEXT NOT NULL UNIQUE,            -- e.g. 'CONC-SLUMP', 'TRAVEL', 'MOBILIZATION'
    name                TEXT NOT NULL,
    description         TEXT,
    category            TEXT NOT NULL,                   -- 'test' | 'hours' | 'flat'
    unit                TEXT NOT NULL,                   -- 'each' | 'hour' | 'mile' | 'day'
    default_price_cents INTEGER NOT NULL,
    -- When this service maps to a specific test type, link it so
    -- billable_lab_tests can auto-resolve the service for a completed
    -- material_test.
    test_type_id        INTEGER REFERENCES test_types(id),
    is_active           INTEGER NOT NULL DEFAULT 1,
    -- qb_item_id + qb_synced_at deferred per D9. Future phase adds
    -- the push column when QB invoice sync lands.
    created_at          TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at          TEXT NOT NULL DEFAULT (datetime('now'))
);

-- Per-customer price overrides. Optional — most clients pay the
-- default rate. Negotiated contract pricing lives here.
CREATE TABLE customer_service_prices (
    id                  INTEGER PRIMARY KEY AUTOINCREMENT,
    client_id           INTEGER NOT NULL REFERENCES clients(id),
    service_item_id     INTEGER NOT NULL REFERENCES service_items(id),
    unit_price_cents    INTEGER NOT NULL,
    effective_from      TEXT NOT NULL,
    effective_until     TEXT,                            -- null = open-ended
    UNIQUE(client_id, service_item_id, effective_from)
);

-- qb_customer_map deferred per D9. Future phase adds it when QB
-- invoice sync lands.

Admin UI

Two pages under /admin/billing/:

  • /services — manage the service_items catalog (CRUD, set defaults, mark inactive)
  • /services/<id>/customers — manage per-customer overrides for a given service

(qb-sync admin page deferred per D9.)

Sync direction

None for v1 — D9 defers QB push entirely. Price book lives only in be-platform. When QB invoice sync lands in a future phase, the push direction (us → QB) gets bolted on per D2 = C.

Phase 3 — Quote generation

New phase. Quotes are downstream of Phase 2's price book but upstream of any invoicing — generate a quote the moment a prospect calls, without QB involvement.

Why this matters

  • Faster quote turnaround = win more business. Today this is a manual spreadsheet exercise, and the lab loses deals when a competitor responds first.
  • Same price-book inputs feed both quotes and invoices. The marginal cost of adding quote generation on top of Phase 2 is mostly UI + PDF.
  • Accepted quotes can drop directly into a new project with the agreed-upon line items frozen — locks in pricing for the work that follows.

Pieces we already have to lean on

  • LibreOffice in the runtime image — already used for material test report generation. Same template + xlsx → PDF pipeline works for quotes (a quote is just a single-page xlsx-derived PDF).
  • Mailer (Resend integration) — already sends test-complete emails, daily-log nags, QB Time digests. Adding send_quote is a copy of existing helpers.
  • service_items table from Phase 2 — drop-down source for adding line items to a quote.
  • clients + contacts — quote recipient lookup.

New tables

-- A quote prepared for a (potentially new) customer. Customer may
-- be an existing client or a prospect not yet in the system.
CREATE TABLE quotes (
    id                  INTEGER PRIMARY KEY AUTOINCREMENT,
    quote_number        TEXT NOT NULL UNIQUE,            -- 'Q-2026-0001' etc.
    -- Either an existing client OR a prospect_name/email for unknowns.
    -- Once accepted, the prospect can be promoted to a clients row.
    client_id           INTEGER REFERENCES clients(id),
    prospect_name       TEXT,
    prospect_email      TEXT,
    prospect_phone      TEXT,
    project_description TEXT,
    -- Lifecycle:
    --   'draft'    — being edited, not sent
    --   'sent'     — emailed/printed to recipient
    --   'accepted' — customer confirmed
    --   'rejected' — customer declined
    --   'expired'  — past valid_until
    --   'voided'   — superseded or cancelled
    status              TEXT NOT NULL DEFAULT 'draft',
    valid_until         TEXT,                            -- ISO date; null = no expiry
    subtotal_cents      INTEGER NOT NULL DEFAULT 0,      -- denormalized, recomputed on save
    notes               TEXT,                            -- free-form for the quote body
    -- Once accepted + work begins, the quote ties to a real project.
    converted_to_project_id INTEGER REFERENCES projects(id),
    -- Audit fields
    created_by_id       INTEGER NOT NULL REFERENCES users(id),
    sent_at             TEXT,
    accepted_at         TEXT,
    expired_at          TEXT,
    created_at          TEXT NOT NULL DEFAULT (datetime('now'))
);

-- Each line on a quote. Snapshot of the price at quote-send time so
-- a later service_items price change doesn't retroactively alter
-- what the customer agreed to.
CREATE TABLE quote_line_items (
    id                  INTEGER PRIMARY KEY AUTOINCREMENT,
    quote_id            INTEGER NOT NULL REFERENCES quotes(id) ON DELETE CASCADE,
    service_item_id     INTEGER NOT NULL REFERENCES service_items(id),
    description         TEXT NOT NULL,                   -- copied from service_item, editable
    qty                 REAL NOT NULL,
    unit_price_cents    INTEGER NOT NULL,                -- frozen at send-time
    line_total_cents    INTEGER NOT NULL,                -- qty * unit_price, computed on save
    sort_order          INTEGER NOT NULL DEFAULT 0
);

-- Status change history for the audit trail.
CREATE TABLE quote_status_log (
    id                  INTEGER PRIMARY KEY AUTOINCREMENT,
    quote_id            INTEGER NOT NULL REFERENCES quotes(id),
    from_status         TEXT,
    to_status           TEXT NOT NULL,
    changed_by_id       INTEGER REFERENCES users(id),     -- null when system-driven (e.g. expiry)
    changed_at          TEXT NOT NULL DEFAULT (datetime('now')),
    note                TEXT
);

Workflow

[draft] --send--> [sent] --client accepts--> [accepted] --create project--> done
   |                |                              |
   |                |                              +--> attach to existing project
   |                +--client rejects------------> [rejected]
   |                +--valid_until passes---------> [expired]
   +--admin voids---------------------------------> [voided]

Admin UI

New pages under /admin/quotes/:

  • / — list view: status filters, search by prospect, "+ New quote"
  • /new — create form with line-item builder. Pick existing client or enter prospect details. Add lines from service_items dropdown (with per-customer pricing applied when client_id known). Live subtotal.
  • /<id> — view rendered quote, status, history, action buttons (Send, Mark accepted, Mark rejected, Void, Convert to project)
  • /<id>/edit — only available while status='draft'
  • /<id>/pdf — download the rendered quote PDF
  • /<id>/send — opens email composer with the quote PDF attached

PDF generation

Reuse the existing libreoffice path from material test reports:

  1. Open report-templates/quote.xlsx (new template) with the quote's data injected via umya-spreadsheet
  2. Headless libreoffice converts to PDF
  3. Cache the PDF or generate on demand (probably on demand for draft, cache after first send so the customer always sees the same thing they were sent)

Email delivery

Extend email::mailer::Mailer with send_quote(...):

  • Subject: "Quote {quote_number} for {project_description}"
  • Body: short intro from the PM, link to view in portal (or just the PDF attached)
  • Attaches the PDF, BCC's the PM, logs the send to quote_status_log

Quote → Project conversion

When status transitions to accepted, the PM can click "Create project from quote". This:

  1. Creates a new projects row populated from prospect details
  2. If the prospect wasn't already a client, creates a clients row
  3. Sets quote.converted_to_project_id so the link is traceable
  4. Copies the agreed line items into a new project_billing_overrides table (TBD — might just snapshot service prices into per-project rows so the invoice math later uses what was quoted, not what's current)

Numbering

Quote numbers like Q-2026-0001, mirroring T-2026-0001 test numbers. Year + sequential within year. New table or just a generation helper — the test number generator pattern works.

Out of scope for v1

  • Multi-version quotes (re-quote uses a new quote_number; old becomes voided with a superseded_by_id link if needed later)
  • Attachments (client scope docs, drawings) — file table later
  • Customer portal where they accept/reject online — for v1, they reply by email and PM updates status
  • Sales tax on quotes — quote shows pre-tax; "tax additional" note in the template
  • Multi-currency — USD only

Phase 4 — Lab test invoicing (in-house, no QB push)

Per D8 + D9. Per-test "Add to invoice" action by PE/Admin builds up one open invoice per client at a time. No QB sync.

New tables

-- An in-house invoice. One row per (client, billing cycle). At most
-- ONE row per client may have status='open' at a time — enforced
-- by a partial unique index below.
CREATE TABLE invoices (
    id                  INTEGER PRIMARY KEY AUTOINCREMENT,
    invoice_number      TEXT NOT NULL UNIQUE,            -- 'INV-2026-0001'
    client_id           INTEGER NOT NULL REFERENCES clients(id),
    -- Lifecycle:
    --   'open'    — being added to. New lines can land here.
    --   'closed'  — sealed. PE/Admin marked it closed; no more lines.
    --                When QB sync ships later, push happens at this
    --                transition.
    --   'sent'    — PDF emailed/delivered to client.
    --   'paid'    — payment recorded (manual flag for v1; QB webhook
    --                in the future phase).
    --   'voided'  — superseded or cancelled; lines released back to
    --                "uninvoiced" status.
    status              TEXT NOT NULL DEFAULT 'open',
    -- Denormalized for fast list views; recomputed when lines change.
    subtotal_cents      INTEGER NOT NULL DEFAULT 0,
    -- Audit
    created_by_id       INTEGER NOT NULL REFERENCES users(id),
    closed_at           TEXT,
    closed_by_id        INTEGER REFERENCES users(id),
    sent_at             TEXT,
    paid_at             TEXT,
    notes               TEXT,
    created_at          TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at          TEXT NOT NULL DEFAULT (datetime('now'))
);

-- "At most one open invoice per client" constraint.
CREATE UNIQUE INDEX uq_open_invoice_per_client
    ON invoices(client_id)
    WHERE status = 'open';

-- A line on an invoice. Polymorphic source: either a material_test
-- (lab work), a daily_field_log (field hours), or a manual line
-- (mobilization fee, mileage, etc.).
CREATE TABLE invoice_line_items (
    id                  INTEGER PRIMARY KEY AUTOINCREMENT,
    invoice_id          INTEGER NOT NULL REFERENCES invoices(id) ON DELETE CASCADE,
    -- Source pointer (one of these is set):
    material_test_id    INTEGER REFERENCES material_tests(id) UNIQUE,
    daily_field_log_id  INTEGER REFERENCES daily_field_logs(id),
    -- service_item that defines the description + default price.
    service_item_id     INTEGER NOT NULL REFERENCES service_items(id),
    description         TEXT NOT NULL,                   -- editable
    qty                 REAL NOT NULL,
    -- Price frozen at add-time so later price changes don't move
    -- the line. Snapshot considers customer_service_prices first,
    -- then service_items.default_price_cents.
    unit_price_cents    INTEGER NOT NULL,
    line_total_cents    INTEGER NOT NULL,                -- qty * unit_price
    sort_order          INTEGER NOT NULL DEFAULT 0,
    added_by_id         INTEGER NOT NULL REFERENCES users(id),
    added_at            TEXT NOT NULL DEFAULT (datetime('now'))
);

-- Status change history.
CREATE TABLE invoice_status_log (
    id                  INTEGER PRIMARY KEY AUTOINCREMENT,
    invoice_id          INTEGER NOT NULL REFERENCES invoices(id),
    from_status         TEXT,
    to_status           TEXT NOT NULL,
    changed_by_id       INTEGER REFERENCES users(id),
    changed_at          TEXT NOT NULL DEFAULT (datetime('now')),
    note                TEXT
);

The UNIQUE(material_test_id) constraint on invoice_line_items prevents double-invoicing a single test. If a test gets voided from one invoice and re-added to another, the void path nulls out material_test_id on the old line first.

The "Add to invoice" action

Surfaced on the material_test detail page when: - Test is in a terminal status (complete, reviewed, delivered) - Current user has admin or principal_engineer role - Test is not already on an invoice_line_items row

Button label: "Add to invoice".

On click:

  1. Find the client's open invoice. If none, create one (invoices.status='open', next sequential invoice_number).
  2. Resolve the line price: check customer_service_prices first, then service_items.default_price_cents for the service mapped to this test's test_type_id.
  3. Insert an invoice_line_items row pointing at this material_test.
  4. Recompute and update invoices.subtotal_cents.
  5. Toast: "Added to INV-2026-0001 ($X total now)."
  6. Redirect back to the test view (or to the invoice view — probably configurable per-user, but defaults to staying on the test page so the PE can move through their queue).

PE/Admin views (/admin/billing/...)

Whole admin area, role-gated to PE + Admin:

  • /admin/billing/ — landing: counts of open invoices, closed-but-unsent, sent-but-unpaid, plus a count of uninvoiced terminal tests across all clients.
  • /admin/billing/uninvoiced — list of all terminal tests not yet on an invoice, grouped by client, filterable by project / test type / date. Bulk action: "Add selected to invoice" (per client at a time).
  • /admin/billing/clients — list of clients with billing activity (any open invoice, or any uninvoiced tests). Shows AR state.
  • /admin/billing/clients/<id> — per-client summary: list of all invoices for that client (open + closed + sent + paid), current open-invoice quick-add UI, uninvoiced tests count, link to add line items.
  • /admin/billing/invoices/ — invoice list, filter by client / status / date range.
  • /admin/billing/invoices/<id> — invoice detail with lines, totals, status. Actions:
  • "Close invoice" — transitions to closed. Validates that there's ≥1 line and a non-zero total.
  • "Send to client" — closed → sent. Triggers PDF gen + email via the existing libreoffice/Resend pipeline reused from Phase 3 (quotes).
  • "Mark paid" — manual flag for v1 (D9 — no QB webhook yet).
  • "Void" — admin-only; nulls source-pointer columns on the lines so the underlying tests / daily logs return to uninvoiced state.
  • /admin/billing/invoices/<id>/edit — only valid in open status. Add lines (manual or by picking uninvoiced tests for this client), edit descriptions, reorder, remove lines.

Role gating

PrincipalEngineerOrAdmin request guard — new variant analogous to the existing ManagerOrAdmin guard. Lives in src/auth/guards.rs. All /admin/billing/* routes use it. PM role does NOT have access in v1 — the workflow intentionally lives at PE/Admin tier only.

Sync direction

None. Local-only per D9. When QB push ships in a future phase, the trigger is invoices.status transitioning to closed (or sent — TBD) and the push is a one-shot translate-and-POST per invoice.

Phase 5 — Field-hours invoicing

Same pattern as Phase 4 — "Add to invoice" per daily field log, auto-routes to the client's open invoice, no QB push. Different source data (daily_field_logs instead of material_tests) and the quantity is hours, not a unit count.

Schema additions

Three new columns on daily_field_logs (per D3 = B):

  • on_site_minutes — computed at submit from site_arrival_time → site_departure_time. Backfilled for existing logs from time_billed_hours (treat as on-site only historically).
  • drive_to_minutes — engineer-entered.
  • drive_from_minutes — engineer-entered.

invoice_line_items (from Phase 4) already supports field-log sources via the daily_field_log_id FK. No new table here.

"Add to invoice" — field log variant

On the daily-log view (/lab/projects/<id>/daily-logs/<id>), visible to PE/Admin when:

  • Log is reviewed (the existing approval gate from #478)
  • Lines aren't already on an invoice for this log

Button opens a small dialog that confirms the three categories that will be added as separate lines:

  • On-site work — hours from on_site_minutes
  • Travel — hours from drive_to_minutes + drive_from_minutes (single combined "Travel" line; the three-line variant from D3 = C was explicitly rejected)
  • Cylinder pickup — only if Q5 lands on the "separate event" answer; cylinder-pickup events get their own "Add to invoice" trigger from a separate UI surface

Each category resolves to a service_item (mapping table TBD — probably just a column on service_items flagging which category it represents). Line prices resolve from per-customer overrides then defaults, same as Phase 4.

Service items needed in price book

Three new rows in service_items (Phase 2):

  • "Field engineering — on-site hours" (category='hours', unit='hour', default hourly rate)
  • "Travel time" (category='hours', unit='hour', separate travel rate)
  • "Cylinder pickup" (category='hours', unit='hour'; probably same rate as Travel — kept separate so the invoice line is informative)

Sync direction

None per D9. Same as Phase 4.

Phase 6 — Polish (in-house surfaces)

  • Per-client stale-uninvoiced nag: weekly digest to PEs listing clients with >30 days of uninvoiced terminal tests
  • "Open invoice past N days" warning — open invoices that haven't been closed in a long time risk getting forgotten
  • PDF template polish for sent invoices (mirrors the quote PDF template from Phase 3 — reuse layout)
  • Invoice search / filter / export to CSV from the admin views

Future — QB invoice sync (deferred per D9)

Out of scope for v1. Re-evaluate after we've operated the in-house billing flow for a few months. When it ships, scope likely includes:

  • qb_customer_map table — link clients → QB customers
  • QB Item push for service_items (per D2 = C) — items get pushed to QB so invoice posts have valid Item refs
  • New qb_invoice_outbox (mirroring qb_time_sync_outbox) — push closed-or-sent invoices to QB Invoices API
  • Drift detection — same pattern as #480 / qb_time_drift_log
  • Failure email digest (extends #481)
  • Admin dashboard surface (extends #482)
  • Webhook listener for QB invoice paid → mark our invoice paid, notify PE

The invoice schema in Phase 4 was designed with this in mind — the invoices.status enum already includes paid and the transition to closed is the natural push trigger.

Open questions (Q1-Q7)

Things I'd want answered but aren't blocking the start of Phase 1.

Q1. Time-off pull frequency

Hourly or daily? Hourly is more responsive when an engineer requests time off this morning for tomorrow; daily is gentler on QB Time rate limits. My read: daily at 4 AM, plus a manual "refresh now" button on the admin user view for ad-hoc cases.

Daily at 4 AM MT (or as configured by the admin)

Q2. Cancelled time off

QB Time supports cancelling an approved request. Do we delete our time_off_days row or mark it revoked_at? My read: revoke + keep, so the audit trail survives.

Let's do revoke and keep.

Q3. Time-off range vs. per-day

QB Time stores time-off as a request with start + end. Our time_off_days is one row per calendar day. Expand requests into per-day rows at pull time. Trivial — note it here so we don't forget.

Q4. Half-days

QB Time supports partial-day time off (e.g. 4 hours PTO). Our table is binary (off or on). For scheduling: a half-day engineer is "available but reduced." Three options: (a) store the hours, (b) ignore partial days, (c) treat any partial day as full. My read: (a) — add hours_off column, default 8.0 for full days. Scheduler can decide what to do with partial values later.

we need to support hours_off.

Q5. Cylinder pickup workflow

Today there's no UI for "I drove to pick up cylinders." Options: (a) a new daily-log subtype, (b) a column on existing daily logs, (c) a separate cylinder_pickup_events table. My read: (c). Picking up cylinders is a discrete event; doesn't need a full daily log; gets billed differently.

Let's do (c).

Q6. Past-month re-billing

What if a test gets reviewed in March that was completed in February? Goes on March invoice (most recent) or backdated to February? My read: bill on March invoice with a note line "Includes Feb work reviewed late." Avoids re-opening old invoices.

Any test can go on any invoice.

Q7. Drift detection on invoices

QB Invoice can be edited after push (PM bumps a line, customer disputes, etc.). Same pattern as QB Time drift (#480) — detect on re-poll, log, don't overwrite. My read: yes, mirror the pattern exactly. The outbox row gains a drift_detected_at field.

Let's do drift detection.

What's NOT in scope (and won't be unless asked)

  • Pulling invoice payment status back from QB to drive PWA "is this client current?" indicators — possible future, but the lab portal doesn't surface AR today
  • Sales tax / nexus — assume QB handles tax computation on push; we send pre-tax amounts
  • Discounts / credit memos — handled QB-side
  • Recurring invoices — none of our work is subscription-shaped
  • Multi-entity QB (multiple companies under one QB account) — single company assumed

Suggested phasing + ticket structure

  1. Phase 1 — time-off pull — 1 ticket
  2. Phase 2 — service_items + customer_service_prices + admin UI — 3 tickets (QB push deferred per D9)
  3. Phase 3 — quotes + quote_line_items + builder UI + PDF template + email send + quote→project conversion — 5-6 tickets
  4. Phase 4 — invoices + invoice_line_items + open-invoice per-client logic + PE/Admin views + "Add to invoice" action on tests + PDF send pipeline — 5-6 tickets
  5. Phase 5 — daily-log time-field schema additions + field-log "Add to invoice" + cylinder pickup events (Q5) — 3-4 tickets
  6. Phase 6 — in-house polish (nags, PDF template iteration, search/filter/export) — 3 tickets
  7. Future — QB invoice sync (deferred per D9; revisit after operating Phase 4 + 5 for a few months)

Total: roughly 20-23 tickets through Phase 6. QB sync is indefinite, evaluated against real operating experience.

Realistic sequencing: ship Phase 1 + 2 first (unblocks both quoting and billing); then Phase 3 (quotes) gives sales-cycle value while Phase 4 (in-house lab invoicing) lands AR visibility. Phase 5 + 6 follow.

Decisions log

D1-D9 answered. Quote generation added as Phase 3 (post-D2). Phase 4-5 invoicing redesigned per D8 (per-test trigger, open invoice per client) and D9 (defer QB push entirely).

Choice Implication
D1 A Pull QB Time time off → time_off_days. Self-attest remains as fallback.
D2 C Our price book (service_items) is source of truth. QB push deferred to D9.
D3 B Separate "Travel" line; needs split on daily-log time fields.
D4 D → superseded Replaced by D8.
D5 A → reframed No QB outbox to gate. "Close invoice" + "Send to client" become the equivalent gates.
D6 A Bill pour on-site hours + each lab test individually.
D7 C Project-level re-test billing policy. Add column on projects (retest_billing_policy).
D8 per-test PE/Admin "Add to invoice" action on terminal tests. One open invoice per client; auto-creates if none.
D9 defer No QB invoice push in v1. Live with in-house workflow first.

Still open

  1. Open questions Q1-Q7 above — non-blocking but should answer before the dependent phase ships:
  2. Q1 (time-off pull frequency) — needs answer before Phase 1
  3. Q2-Q4 — Phase 1
  4. Q5 (cylinder pickup workflow) — Phase 5
  5. Q6-Q7 — Phase 4
  6. Hidden 4th use case? — per-mile reimbursement, equipment rental pass-through, subcontractor billing? None of these are covered. Flag if relevant.
  7. projects.retest_billing_policy column from D7 — values? Probably 'bill_all' | 'bill_first_only' | 'pm_decides'. Confirm when filing Phase 4.

Next action

Once you've signed off on this version (and answered Q1 + any "hidden 4th case"), I'll file the Phase 1 + Phase 2 tickets so the overnight worker can start. Phase 3 (quotes) waits for Phase 2 infrastructure to land.