Project & Task Costing with Timesheet Billing

A design reference for turning tracked project time into internal cost and customer billing

1. Requirements

1.1 Functional requirements

• Represent a hierarchical work breakdown: a project contains tasks; a task can nest under a group task and can declare predecessor tasks that must reach a closed state before it may be marked complete.
• Support reusable project templates: a template holds tasks with relative start offsets/durations and pre-wired predecessor/parent relationships, so instantiating one produces an already-linked task set, not a flat list.
• Let any unit of work an employee performs be captured as a dated time log, optionally tied to a task and project, and always tied to an activity type describing the kind of work performed.
• Compute two independent monetary figures per time log — an internal cost (from an activity- and employee-based rate) and a billable amount (from a rate that can differ) — and keep the two from being conflated downstream.
• Resolve a working rate for both figures without manual pricing: default from the activity type, sharpened by an employee-specific override where one exists, while still allowing a hand-typed override.
• Let a biller convert unbilled, billable time log lines into a customer invoice, either by converting one timesheet directly or by letting a project-scoped invoice absorb every outstanding billable line for that project automatically.
• Once a line is invoiced, record the linkage so the same hours cannot be billed twice, and let it be cleanly reversed on cancellation.
• Roll up project- and task-level costing/billing totals automatically from the underlying time log rows.
• Make circular predecessor chains structurally impossible, and block a task from closing while a predecessor remains open.

1.2 Non-functional requirements

• Idempotent invoice linkage: converting, cancelling, and reconverting a time log must not double-count billed hours or corrupt the source’s billed percentage.
• Currency awareness: an employee’s cost and a customer’s bill may sit in different currencies than the company’s books; both native- and base-currency figures are retained per line.
• Low ceremony for the common case: logging time against a known activity type should yield a usable rate with zero pricing steps, while remaining overridable.
• Auditable rollups: every aggregate on a project or task must trace to submitted time log rows, not a separately maintained running total.

1.3 Constraints

• Rates are stamped onto the individual time log line at save time, not resolved centrally at read time — a later change to an activity type’s default never retroactively touches a rate already recorded on a submitted line.
• Task dependency is a single per-task predecessor list, not a scheduling engine — no resource leveling, critical path, or multi-project scheduler exists here.
• Turning tracked time into a bill is a mapping into an ordinary customer invoice; once a billable line lands on it, the invoice’s own ledger posting follows the same mechanics as any other invoice (see the accounts module’s ledger-posting design for how that posting balances).

2. High-Level Design

2.1 Component diagram

2.2 Data flow — instantiating a project from a template

1. A project is created with a template selected, before it has any tasks. Instantiation fires once, right after creation.
2. Each template task is copied into a real task, carrying subject, description, weighting, group/type flags and a back-reference to the template task.
3. Dates are derived from the project’s expected start date plus each template task’s relative offset and duration, skipping past any holiday on the applicable calendar.
4. Predecessor links are replayed: for a new task whose template counterpart declared a predecessor, the matching real task (found via the template back-reference) is appended to the new task’s own predecessor list — the graph is reproduced, not copied as text.
5. Parent/child links are replayed the same way, matching each new task’s template parent against the other newly created tasks.

2.3 Data flow — two paths into billing, one convergence point

3. Deep Dive

3.1 Data model

Project The top-level work container: an estimated cost, a default cost center, a percent-complete figure driven by one of four selectable methods (manual, task-completion ratio, task-progress average, task-weight-adjusted average), and read-only rollups covering actual dates/hours, costing amount, purchase cost, sales amount, billable amount, billed amount, consumed material cost, gross margin and gross-margin percent. Rollups recompute from scratch on each save by re-querying the timesheet, sales order, sales invoice and purchase invoice lines tagged to it — none is an incremental counter.

Task Sits in a parent/child hierarchy, with its own status, priority, weight and percent-progress. It carries two rollups — costing amount and billing amount — but, unlike the project, does not separately track billed (invoiced) amount; it distinguishes cost from bill, not bill from billed. A task can be flagged as a template task, in which case its predecessor and parent links must themselves point only at other template tasks.

Predecessor list (a per-task table) Each task carries a table of predecessor references — tasks that must close before this one may complete. Each row mirrors the referenced task’s subject and project as display text, but graph identity comes from the link column alone. This one table is the whole dependency mechanism: read to gate completion, walked both directions to catch cycles, and appended-to automatically when a task is filed under a parent. A second, differently-named table with the same single-column shape (a bare link to a task) exists in the schema, but nothing found in this module wires it up as the table behind any field on Project, Task, or Project Template — unused schema from an earlier design.

Project Template / Project Template Task A disable-able list of template tasks with relative start offsets and durations rather than absolute dates, able to pre-declare the same predecessor/parent relationships a real task would have; instantiation (§2.2) replays them. Authoring is guarded: a template task depending on another task requires that other task to be in the same template’s list, or the template is rejected.

Activity Type / Activity Cost Activity Type is a named kind of work carrying exactly two numbers — a default costing rate and a default billing rate, both per hour — plus a disable flag; no employee, customer or project dimension attached. Activity Cost is an optional override of the same two numbers, keyed on an employee + activity type pair (or, with employee blank, a second default scoped to the activity type alone — functionally redundant with the Activity Type default). A uniqueness rule blocks two overrides for one employee/activity pair, and a separate rule blocks two blank-employee defaults for one activity type.

Timesheet The header for a batch of time logs against one employee, optionally tied to a project, a customer, and a currency that defaults from the customer’s own currency. One exchange rate applies uniformly to every line’s cost and bill figures — no separate cost-side and bill-side rate, even though cost conceptually belongs to the employee’s currency and billing to the customer’s. Status derives from three layered signals: submission state, then percent-billed (promoting to Partially Billed or Billed), then, if a header-level invoice-reference field happens to be set, a final override to Completed — no code path found in this module ever writes that field, so reaching Completed here requires something outside this module to set it by hand.

Timesheet line (the costing/billing unit) Each row carries activity type, from/to time, hours, a billable flag, billing hours (defaults to full hours when billable), a costing rate and amount, a billing rate and amount, base-currency mirrors of all four, and — once invoiced — a reference to the covering invoice. Costing runs against raw hours; billing runs against billing hours, which can be set lower than actual hours to write off unbillable time without touching cost.

Sales Invoice / invoice-side linking rows The customer-facing document carrying the billable amount into the ledger. It holds a child table of linking rows, one per source line pulled in, each snapshotting billing hours, billing amount, activity type and a pointer back to the source line, plus a header total that is a plain sum over those rows — the same invoice used for any other sale, not a separate document type.

3.2 Algorithm — rate resolution (a lookup, not a pricing engine)

Resolving a line’s rate is a two-tier lookup, invoked when an activity type is chosen or the timesheet’s employee changes:

resolve_rate(employee, activity_type, currency=None):
    override = find Activity Cost row for (employee, activity_type)
    if override exists:
        return (override.costing_rate, override.billing_rate)   # no currency adjustment

    default = find Activity Type row for activity_type
    if default exists and currency given and currency != company_base_currency:
        rate = exchange_rate(company_base_currency -> currency)
        return (default.costing_rate * rate, default.billing_rate * rate)
    return (default.costing_rate, default.billing_rate) if default else nothing

Two asymmetries matter here:

• The employee override is never currency-adjusted, even when a currency is requested — only the generic default is; an override is trusted to already be denominated correctly.
• The currency-aware call only happens client-side, when an activity type or employee is picked in the editing form, passing the timesheet’s own currency. The server-side recalculation that runs on every save calls the same lookup without a currency argument, so it never converts; it only fills costing_rate/billing_rate when the line’s value is exactly zero, leaving an already-populated rate untouched. A line created purely programmatically against a foreign-currency timesheet therefore defaults to the unconverted activity-type rate.

Once settled: costing_amount = costing_rate × hours, billing_amount = billing_rate × billing_hours, both mirrored to base currency via the timesheet’s single shared exchange rate.

3.3 Algorithm — the actual dependency model

The predecessor list drives four behaviors, all against that one table, none of it a general scheduler:

• Completion gating. A task cannot close while any task in its own predecessor list is still open; checked at the moment of the transition, not continuously.
• Cycle prevention. Before a save takes effect, a bounded walk follows the predecessor list in both directions (as this task’s predecessors, and as another task’s predecessor) up to fifteen hops; if the task reappears in either walk, the save is rejected. Fifteen is a hard, uninspectable ceiling — a genuine cycle deeper than that would go uncaught.
• Downstream rescheduling. When a task’s end date moves, every task in the same project that lists it as a predecessor, and whose start now falls before that new end date, has its start pushed to the day after and its end shifted by the same span it already had — a fixed-duration slide, not a resource-aware re-plan.
• Self-registration under a parent. Filing a task under a parent auto-appends it into the parent’s own predecessor list if absent — “is a subtask of” and “blocks completion of” become the same row.

No forward-scheduling pass, resource leveling, or critical-path computation exists here; “dependency” means exactly this one predecessor list, read and written from both ends.

3.4 Timesheet-to-invoice conversion (a mapping utility, not a re-pricing step)

Both paths in §2.3 copy already-computed figures onto the invoice rather than recomputing them.

• Direct conversion starts from one timesheet’s unbilled, billable lines. If the caller supplies an item to bill against, one summary line is added, priced at (remaining billable amount) / (remaining billable hours) — a blended average across every unbilled line, not a per-activity rate. Without an item, no invoice line is added at all, only linking rows. Either way, every unbilled billable line becomes a linking row carrying its own already-computed hours and amount.
• Auto-fetch triggers when an invoice has a project set, no linking rows yet, and a standing setting is enabled; it pulls in every outstanding billable line across the whole project, not just one timesheet’s.
• Both converge on one header total: a plain sum of the linking rows. Nothing re-derives a rate at this stage.
• The write-back that marks a line billed happens on invoice submission: each linking row is matched to its source line, which is stamped with the invoice reference; the owning timesheet is reloaded, its totals and percent-billed recomputed, its status reset — bypassing the timesheet’s own save-time validation for that recomputation. Cancelling runs the same match-and-stamp logic with the reference cleared, which is what makes the cycle idempotent.

3.5 Error handling

• Double-invoicing guard: before submission, each linking row is checked against its source line’s current invoice reference; already set to a different invoice rejects submission.
• Stale-state guard: a timesheet must be submitted-and-not-fully-settled to be invoiced; draft, fully billed, or cancelled timesheets are rejected by name.
• Time overlap: overlapping from/to windows for the same user or employee are rejected at save time, each independently relaxable via settings (both default to enforced).
• Mandatory fields before submission: from-time and to-time, an activity type whenever an employee is set, and hours greater than zero.
• Zero/complete guards: converting a timesheet with no outstanding billable hours, or one already fully billed, is rejected before an invoice is constructed.
• Rate-override uniqueness: an override cannot duplicate an employee/activity pair, nor can a second blank-employee default exist for one activity type.

4. Scale and Reliability

• Load pattern: bursty around timesheet-submission cycles and period-end billing runs — many small writes, not high per-transaction volume.
• Rollups are full re-aggregations, not incremental counters. Project and task totals recompute by re-querying every relevant line each time a related timesheet is saved, submitted, or cancelled — consistent, but growing in cost with historical lines per project.
• The invoice-side write-back reloads the whole owning timesheet per linked line rather than updating the matched row directly, stepping around that timesheet’s own validation — costly once many invoices reference one long-lived timesheet.
• The cycle check has a hard, fixed hop limit rather than a traversal bounded by actual node count — adequate for realistic task lists, not a general guarantee at unusual depth.
• No queueing: rollups, rescheduling, and write-backs run synchronously inside the triggering request; nothing smooths a large batch invoicing run.

5. Trade-off Analysis

6. What to Revisit as the System Grows

• Add a customer- or project-scoped billing rate tier. Today’s only inputs are activity type and, optionally, employee; pricing the same work differently per client requires hand-editing every line.
• Split the timesheet’s single exchange rate into a cost-side and bill-side rate, or make the shared-rate assumption explicit, once cross-currency staffing becomes more than an edge case.
• Replace the fixed-depth cycle check with a real graph traversal bounded by the actual task count, rather than a hand-picked hop limit.
• Resolve or remove the second, unwired predecessor-shaped table identified in §3.1 — dead schema confuses future extension.
• Move the invoice-side write-back to a targeted update of the matched line instead of reloading the whole owning timesheet, once invoices-per-timesheet grows large.
• Consider incremental rollup maintenance if project lifetimes and time-log volume grow to where full recompute becomes visibly slow.