Clio Stores Billable Time in Seconds. The Column Named Hours Is Optional.

Ask a Clio data warehouse for billable hours by timekeeper and the number comes back in the tens of thousands. Nobody billed that. The activities table holds time entries, expenses and costs together, and quantity means something different on each kind of row. Inside Clio Manage every screen converts and filters before anyone sees a figure. The conversion lives in the application, and the extract copied only the rows.

A firm's operations lead wants billable hours by timekeeper for last month.

Point an AI agent at the extracted schema and it finds the table immediately. activities has a user_id, a date, a non_billable flag and a column called quantity. Sum the quantity where the entry is billable, group by user.

The query runs. Every timekeeper gets a number, and every number is four figures for a month nobody could have worked.

This one's loud, and that's the lucky version. The quiet version arrives a step later, when somebody divides by 3600 and the total starts looking reasonable.

Before you start

• Clio in a database. No general-availability connector exists yet. Fivetran lists a Clio connector carrying the Lite and In Dev badges, with no schema section and no feature marked supported. Most firms get the data out through the API on a schedule, or through Clio's own CSV exports.
• The permission that decides what lands. Clio redacts money and hours on other people's entries, based on the settings of whoever authorised the extract. The changelog is explicit: version 4.0.5 redacts "price and total fields of Activity records based on a user's Billing Rate Visibility setting", and 4.0.8 does the same for hours. Check who authorised yours before anyone reads a null as a zero.
• Which fields you asked for. Clio's Fields page says that "for most endpoints, the id and etag fields are the only default fields returned." Your warehouse holds what somebody typed into fields=, and nothing else.
• Somewhere to practise. Clio's handbook says "Clio does not have a separate sandbox environment. Instead, the 7-day free trial account (Clio Manage) or a developer team account (Clio Platform) serves as your test environment." After the trial expires you apply for a developer account to keep access. Accounts are per region, so a US account won't reach EU data.
• Casing varies by extract tool. Clio's API is lower-case snake_case. Snowflake destinations usually upper-case it, and a nested matter object can land as matter_id, matter.id or a JSON column. The SQL below uses the API's names.

The question

"How many billable hours did each timekeeper record last month?"

Every firm asks it. Utilisation, realisation and revenue per lawyer all divide by that number, so an error in it propagates into three more figures before anyone notices the first one.

It comes out of the warehouse rather than out of Clio because those ratios need the firm's other systems beside it: the general ledger, the compensation model, the headcount.

What breaks

Here's the query almost anyone writes first:

SELECT user_id,
       SUM(quantity) AS billable_hours
FROM   activities
WHERE  non_billable = FALSE
  AND  date >= DATE '2026-08-01'
  AND  date <  DATE '2026-09-01'
GROUP  BY user_id;

Nothing about it is careless. The table is called activities, the column is called quantity, and non_billable is a documented boolean that means exactly what it says: "Whether the Activity is non-billable."

The result is nonsense at a glance, which is the best thing about it. A timekeeper who logged 140 hours comes back with 504,000 of something.

The dangerous fix is the obvious one. Divide by 3600 and the totals land near where a partner expects them to be, and the number ships. It's still wrong, and now nothing about it looks wrong.

Why it breaks

Two facts are true of activities at the same time, and the first query reads neither.

One table, four kinds of row

activities isn't a timesheet. Clio's OpenAPI file gives Activity_base.type four values: TimeEntry, ExpenseEntry, HardCostEntry and SoftCostEntry. An hour of a partner's time and a courier receipt are rows in the same table, told apart by one string column.

The API has a type parameter on the list endpoint for exactly this reason. Its description reads "Filter Activity records to those of a specific type", with the same four values. A caller who doesn't name it gets all four.

There's a second filter, status, and it carries a trap of its own. Its description names four states, "draft, billed, unbilled or non-billable", while its enum carries six. billable and written_off are the extra two. Read the enum, not the sentence.

One column, three units

This is the part that produces the wrong number. Clio documents quantity like this, verbatim:

The field is applicable to TimeEntry, ExpenseEntry, and SoftCostEntry.

Version <= 4.0.3: The number of hours the TimeEntry took.

Latest version: The number of seconds the TimeEntry took.

Three things follow, and each one bites differently.

On a time entry, it's seconds. The changelog dates the switch precisely. Version 4.0.4, "Release Date: 2017-08-04", "Promotion to default version: 2017-08-04", summary of changes: "Update quantity field on Activity records to return values in seconds rather than hours."

On an expense or a soft cost, it's a count of units. Not seconds, not hours. price alongside it is the amount per unit rather than an hourly rate. So a naive SUM(quantity) adds a few dozen photocopies to half a million seconds and calls the result hours.

On a hard cost, the description doesn't apply at all. Clio's own sentence lists three of the four types, and HardCostEntry isn't one of them.

The column that's actually hours is called quantity_in_hours, and Clio's description of it is one line with no version clause: "The number of hours the TimeEntry took." It exists because quantity isn't hours. You'll have it in the warehouse only if the extract asked for it.

The same word, a third unit, one table over

line_items holds the lines on a bill, and it has a quantity too. Clio documents that one as "The amount of hours for the LineItem."

Bill lines are in hours. Activities are in seconds. Both columns are called quantity, and a query that joins the two tables and sums across them has combined units with no warning of any kind.

Two faithful extracts can disagree by a factor of 3600

The unit depends on which API version the extract requested, and nothing in the warehouse records that.

Clio's versioning policy says "an unversioned API request will return a response from the default version of the API." The default today is well past 4.0.4, so an extract that never pinned X-API-VERSION holds seconds. An extract pinned to 4.0.3 or earlier, or an archive built before August 2017, holds hours in the same column with the same name and the same type.

Both are correct about their own firm. Neither says so anywhere a query can read.

The same entry, the same column name, two units. Illustrative rows; the field description and the version numbers are Clio's. The ratio in the discovery query is what separates the two cases.

Redaction makes a permission look like a zero

One more column belongs in this section, because it turns a missing number into a plausible one.

Since version 4.0.5, Clio redacts price and total on another user's activity based on that user's Billing Rate Visibility setting. Since 4.0.8, it does the same to the hours fields, and the changelog notes that because the change "involved firm security settings, it was backfilled to previous API versions on August 12, 2022." A quantity_redacted boolean marks the affected rows, and Clio lists the six fields it covers: quantity, rounded_quantity, rounded_quantity_in_hours, quantity_in_hours, total and non_billable_total.

So whoever clicked Authorize decides which rows arrive complete. A firm-wide hours report built on an extract authorised by one associate is missing other people's time, and a SUM reads those nulls as nothing at all.

What the application did for you

Inside Clio Manage, nobody meets any of this.

The Activities page carries a Type column and a filter that separates time from expenses. The reports show hours. The import templates take time in decimal hours, and the exports let a user take time and expenses together or separately. Every one of those surfaces converts, filters or labels before a person sees a number.

The API sits underneath them and hands back the stored value: seconds on time, unit counts on expenses, one column. It also hands you the fix in the same response, since quantity_in_hours and the type filter are both right there. Both are optional, so the convenience the application applied silently is the first thing an extract leaves behind.

That shape is familiar by now. Maximo folds tasks and history into one table, so its trap is a filter nobody declared. Workday's PERSON_NAME holds several populations in one table. JD Edwards is the closest sibling of all: it puts dollars and hours in the same columns of F0902 and tells them apart by a ledger type. Clio's version adds a twist neither of those has. Its unit also changed with a version, so the same column can mean different things at two firms running the same software.

The fix

Patching one query fixes one afternoon. The next question, realisation or utilisation or revenue per lawyer, starts from the same table and makes the same reasonable mistake.

The definition belongs in the semantic model, written once with Clio's documentation as its citation, so every hours question inherits it.

Start with the joins. None of them is a foreign key anywhere a reader can see, because the data arrived from an API and each extract tool flattens nested objects its own way. The list endpoint's filter parameters are the evidence for cardinality: each one takes a single id and offers the keyword null for rows that have none, which is a many-to-one that can't fan out.

# subject_areas/clio_billing/relationships.yaml
relationships:
  - from_table: activities
    from_column: user_id
    to_table: users
    to_column: id
    relationship: many_to_one
    confidence: proposed
    review_state: unreviewed
    description: >
      The timekeeper who recorded the entry. The list endpoint filters on a
      single user_id, and the resource nests a single user object. Your
      extract tool may have landed that object under a different name.
    source: https://docs.developers.clio.com/openapi.json

  - from_table: activities
    from_column: matter_id
    to_table: matters
    to_column: id
    relationship: many_to_one
    confidence: proposed
    review_state: unreviewed
    description: >
      An activity belongs to at most one matter. The filter parameter takes
      the keyword null to match activities without one, so the column is
      nullable and the join can never inflate an hours total.
    source: https://docs.developers.clio.com/openapi.json

Then the part that carries the weight. The column types can't say which rows are time and which unit they're in, so the semantic model has to:

entities:
  - name: clio_time_entry
    description: >
      A time entry, as distinct from the other rows in the same table.
      activities also holds expense entries, hard costs and soft costs.
      On a time entry, quantity is seconds on any extract that took the
      default API version, and hours on one pinned to 4.0.3 or earlier.
      quantity_in_hours is hours on every version.
    resolves_to:
      table: activities
      key: [id]
      selector: "activities.type = 'TimeEntry'"
      forbidden_selectors:
        - >
          Reading quantity without this selector. Adds expense unit counts
          to a total of seconds and labels the result hours.
    caveats:
      - >
        quantity_in_hours is present only if the extract named it in
        fields=. If it is absent, quantity / 3600.0 is right for a
        default-version extract and wrong for a 4.0.3 one. Run the
        discovery query before choosing; do not guess.
      - >
        Hours on another user's entry may be null with quantity_redacted
        set. That is a permission, not a zero.
    source: https://docs.developers.clio.com/openapi.json

And the metric binds to the entity rather than to the table:

metrics:
  - name: clio_billable_hours
    calculation: >
      Hours recorded on billable time entries, by timekeeper or by matter.
      Applies the time entry selector so expense rows never enter the sum,
      and reads quantity_in_hours so the unit does not depend on which API
      version the extract requested.
    requires_entity: clio_time_entry
    source_tables: [activities]
    primary_table: activities
    other_names: [billable hours, hours billed, recorded hours, timekeeper hours]
    binding: >
      SELECT a.user_id, SUM(a.quantity_in_hours) AS billable_hours
      FROM   activities a
      WHERE  a.type         = 'TimeEntry'
        AND  a.non_billable = FALSE
      GROUP  BY a.user_id
    citation: >
      Clio Manage API v4 OpenAPI file: Activity_base.quantity,
      quantity_in_hours and the type enum; API Changelog 4.0.4.

requires_entity: clio_time_entry is the load-bearing line. A billable-hours figure reached by summing quantity across every row is a path the semantic model has already declared invalid, so utilisation and realisation agree on what an hour was.

Four decisions in here can't be read from the schema, and a person makes each of them once.

Which version your extract took. The column won't say. The discovery query below finds out, and someone writes the answer into the entity's description so nobody re-derives it.

Rounded hours or raw. rounded_quantity_in_hours is what the bill was computed from, and Clio says "the rounded value is used to calculate the total". quantity_in_hours is what the timekeeper entered. A realisation report wants both. A headline wants one.

Whether flat-fee work counts. Matter.billing_method marks matters that bill flat or on contingency. Hours on those are real effort and don't drive revenue, so whether they belong in utilisation is the firm's call.

Where non-billable ends. non_billable and no_charge are separate flags. Clio describes no_charge as "whether the non-billable Activity is shown on the bill", so two booleans describe three states.

Reproduce it yourself

Two routes work. If you already have Clio in a warehouse, start at step 2 against your own extract. If you don't, Clio's 7-day trial is the sandbox, and the handbook ships sample CSVs for Contacts, Matters, Time Entries and Expenses that load on your account's Imports page.

The trial route reproduces the whole trap in an afternoon. You enter time in hours through the import page, then read it back through the API at the default version and it comes out in seconds.

1. Run the discovery query. It splits your activities rows by type and tells you which unit quantity holds.
2. Run the gap query. It shows what the first query added to a real hours total.
3. Declare the entity and the metric, then ask "how many billable hours did each timekeeper record last month" in plain English, in the assistant your team already opens, and check the answer against the corrected query.

The discovery query. This is your own version of the number this page can't give you.

SELECT type,
       COUNT(*)                                     AS rows_in_activities,
       SUM(quantity)                                AS sum_quantity,
       SUM(quantity_in_hours)                       AS sum_quantity_in_hours,
       MAX(quantity / NULLIF(quantity_in_hours, 0)) AS quantity_per_hour
FROM   activities
GROUP  BY type
ORDER  BY rows_in_activities DESC;

Read it like this. quantity_per_hour on the TimeEntry row is 3600 if your extract holds seconds and 1 if it holds hours. On every other row it's null, because quantity_in_hours is documented for time entries and nothing else. The rows that aren't TimeEntry are the ones the first query folded into its total.

If quantity_in_hours isn't a column at all, your extract never asked for it. Fix that in the extract before you touch the SQL.

The gap query. Same window, same billable filter, both answers side by side:

WITH per_user AS (
  SELECT user_id,
         SUM(quantity)                                                AS first_query,
         SUM(CASE WHEN type = 'TimeEntry' THEN quantity_in_hours END) AS real_hours,
         SUM(CASE WHEN type <> 'TimeEntry' THEN quantity ELSE 0 END)  AS expense_units
  FROM   activities
  WHERE  non_billable = FALSE
    AND  date >= DATE '2026-08-01'
    AND  date <  DATE '2026-09-01'
  GROUP  BY user_id
)
SELECT user_id,
       first_query,
       first_query / 3600.0 AS naive_hours,
       real_hours,
       expense_units,
       first_query / 3600.0 - real_hours AS drift_from_dividing
FROM   per_user
ORDER  BY drift_from_dividing DESC;

naive_hours is what dividing by 3600 gives you. drift_from_dividing is what that shortcut costs, and it's every expense unit count turned into a fraction of an hour and left in the total. A timekeeper who never books expenses shows a drift of zero, which is why the shortcut survives review.

Corrected. One population, and the column documented as hours:

SELECT user_id,
       SUM(quantity_in_hours) AS billable_hours
FROM   activities
WHERE  type         = 'TimeEntry'
  AND  non_billable = FALSE
  AND  date >= DATE '2026-08-01'
  AND  date <  DATE '2026-09-01'
GROUP  BY user_id;

The queries use no dialect-specific functions, so they run as written on Postgres, Snowflake, BigQuery and SQL Server. Only the casing changes by route.

If a timekeeper still disagrees with Clio's own report, check three things before the SQL. Whether the report you're comparing against uses rounded hours. Whether any row came back with quantity_redacted set. And whether the report's date field is the one you filtered on, since an activity carries both a date it was performed and an updated_at.

What this looks like in Agami

Everything above holds whoever builds the semantic model. Here's what it is in our product, in the terms this post has used.

The unit lives in the model, next to the column. Clio is the first app in this series whose schema of record is a single machine-readable file the vendor maintains, so the description on quantity, version clauses and all, can seed the model verbatim. That doesn't fix the trap by itself. It does mean the caveat sits one line from the column instead of in a help article a fetcher can't read.

clio_time_entry is drafted, and a person approves it. Entities, descriptions and metric definitions are drafted from the schema, then your team approves them, and the approval is reversible. Approval is where an operations lead signs off that a billable hour means a TimeEntry read from quantity_in_hours.

A metric that needs the entity can't be reached without it. requires_entity on clio_billable_hours means a sum over raw quantity isn't a path the semantic model offers. Utilisation and realisation divide by the same hours.

Hours ship once they match the firm's own report. Reconciliation takes a screenshot of the report your partners trust, a CSV export, or numbers pasted into chat, and compares them at a one percent tolerance by default. A mismatch opens the SQL so you can see why. On this table it points at a type, a unit or a redacted row, and finding which one is the useful output.

Every answer returns with its SQL beside it. Whether a total came from quantity or quantity_in_hours is visible on the answer. Someone who knows the firm spots a four-figure month before it reaches a compensation review.

And the number we don't have. We haven't run this against a real Clio warehouse, so this page carries no figure for how much of a firm's activities table is expenses, or how far a first-draft hours number drifts. The discovery query returns yours. The semantic model also can't decide whether flat-fee hours belong in utilisation. It records the answer once a person gives it.

Frequently asked questions

Is Clio's activities.quantity in hours or seconds?

Seconds, on any extract that took the default API version. Clio's field description says "Version <= 4.0.3: The number of hours the TimeEntry took. Latest version: The number of seconds the TimeEntry took." Version 4.0.4, released 2017-08-04, made the change. The column documented as hours on every version is quantity_in_hours.

Why are my Clio billable hours in the tens of thousands?

The query is summing seconds and calling them hours, and it's probably adding expense rows too. activities holds TimeEntry, ExpenseEntry, HardCostEntry and SoftCostEntry rows in one table. On an expense, quantity is a count of units rather than any measure of time.

Can I just divide Clio's quantity by 3600?

Only after filtering to type = 'TimeEntry', and only if your extract took the default API version. Dividing across every row converts expense unit counts into fractions of an hour and leaves them in the total. An extract pinned to 4.0.3 or earlier holds hours already, so dividing it would be wrong by the same factor in the other direction.

Why is quantity_in_hours missing from my Clio warehouse?

Because the extract didn't ask for it. Clio's Fields page says "for most endpoints, the id and etag fields are the only default fields returned", so every other column exists only if it was named in fields=. Fix it in the extract rather than working around it in SQL.

Doesn't Clio Duo already answer this?

Inside Clio Manage, it may. Clio Duo works against live Clio data, where every screen already shows hours and already separates time from expenses. A firm that only ever asks Duo never meets this trap. A firm that moved the data out to report on it beside the ledger has moved past what Duo can see.

References

1. Clio Manage API v4, OpenAPI file. The schema of record: 419 component schemas, and every field description quoted in this post. Activity_base.quantity, quantity_in_hours, rounded_quantity_in_hours, quantity_redacted, non_billable, no_charge, the type enum, and LineItem_base.quantity.
2. Clio Manage API reference. The same file, rendered for a person.
3. Clio API Changelog. Version 4.0.4 and the switch to seconds; 4.0.5 and 4.0.8 on redaction, with the August 2022 backfill.
4. Clio API Versioning Policy. Unversioned requests take the default version, and deprecated versions return 410.
5. Clio, Fields. id and etag are the only defaults for most endpoints.
6. Get a Developer Account, Clio Developers Handbook. No separate sandbox, the 7-day trial, and per-region accounts.
7. Populating your Clio Manage account with sample data. Sample CSVs for Contacts, Matters, Time Entries and Expenses.
8. Clio connector, Fivetran and Lite connectors. The Lite and In Dev badges, and what a Lite connector's schema is built from.
9. Time Entries, Filter and Export Activities and Export Data From Clio Manage, Clio Help Center. The application surfaces that convert and filter before you see a number.
10. Clio Duo press release. The vendor's own natural-language answer, inside the application.
11. agami-core on GitHub

Make your Clio data answerable

Agami is the trust layer between your AI assistant and your warehouse. It declares which activities rows are time and which column holds hours, so an agent returns a billable-hours figure that matches your firm's report or says why it cannot.

Start a free trial or talk to us →