> ## Content Index
> Fetch the complete content index at: https://blog.agami.ai/llms.txt
> Use this file to discover other available public pages before exploring further.

# Clio Stores Billable Time in Seconds. The Column Named Hours Is Optional.
- URL: https://blog.agami.ai/clio-stores-billable-time-in-seconds-the-column-named-hours-is-optional/
- Published: 2026-09-18T16:30:00.000Z
- Updated: 2026-09-18T16:29:59.000Z
- Description: In a replicated Clio activities table, quantity is seconds on a time entry and a unit count on an expense. The semantic model holds the unit and the row filter that every Clio screen applies for you.
- Author: Agami Founders
- Tags: Data Agent, Clio, Semantic Model, NL to SQL, #content

*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](https://fivetran.com/docs/connectors/applications/clio?ref=blog.agami.ai) 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](https://docs.developers.clio.com/api-docs/clio-manage/fields/?ref=blog.agami.ai) 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](https://docs.developers.clio.com/api-docs/clio-manage/fields/?ref=blog.agami.ai) 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](https://docs.developers.clio.com/handbook/getting-started/get-a-developer-account/?ref=blog.agami.ai) 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:

```sql
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](https://docs.developers.clio.com/api-docs/clio-manage/api-versioning-policy/?ref=blog.agami.ai) 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.

![Two Clio extracts of the same 2.5-hour time entry: the firm pinned to API version 4.0.3 holds quantity 2.5 and a quantity-to-hours ratio of 1, the firm on the default version holds quantity 9000 and a ratio of 3600, and both hold quantity_in_hours 2.5](https://storage.ghost.io/c/24/a5/24a5250b-374e-491f-8cb6-1ec3386f4de8/content/images/2026/09/2026-09-18-clio-activity-quantity-in-your-warehouse-fig-1-1.png)

*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](https://blog.agami.ai/one-maximo-work-order-five-tasks-six-rows/?utm%5Fsource=blog&utm%5Fmedium=organic&utm%5Fcampaign=CLIO1) folds tasks and history into one table, so its trap is a filter nobody declared. [Workday's PERSON\_NAME](https://blog.agami.ai/in-your-workday-hcm-warehouse-person%5Fname-holds-people-who-dont-work-for-you/?utm%5Fsource=blog&utm%5Fmedium=organic&utm%5Fcampaign=CLIO1) holds several populations in one table. [JD Edwards](https://blog.agami.ai/how-to-sum-jd-edwards-actuals-without-adding-the-labor-hours-to-the-dollars/?utm%5Fsource=blog&utm%5Fmedium=organic&utm%5Fcampaign=CLIO1) 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.

```yaml
# 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:

```yaml
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:

```yaml
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](https://docs.developers.clio.com/handbook/getting-started/get-a-developer-account/?ref=blog.agami.ai) is the sandbox, and the handbook ships [sample CSVs](https://docs.developers.clio.com/handbook/build-your-app/populating-your-manage-account-with-sample-data/?ref=blog.agami.ai) 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.

```sql
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:

```sql
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:

```sql
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](https://www.clio.com/about/press/clio-duo-unlocks-a-ew-era-of-legal-platform-intelligence/?ref=blog.agami.ai) 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](https://docs.developers.clio.com/openapi.json?ref=blog.agami.ai). 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](https://docs.developers.clio.com/clio-manage/api-reference/?ref=blog.agami.ai). The same file, rendered for a person.
3. [Clio API Changelog](https://docs.developers.clio.com/api-docs/clio-manage/api-changelog/?ref=blog.agami.ai). 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](https://docs.developers.clio.com/api-docs/clio-manage/api-versioning-policy/?ref=blog.agami.ai). Unversioned requests take the default version, and deprecated versions return 410.
5. [Clio, Fields](https://docs.developers.clio.com/api-docs/clio-manage/fields/?ref=blog.agami.ai). `id` and `etag` are the only defaults for most endpoints.
6. [Get a Developer Account, Clio Developers Handbook](https://docs.developers.clio.com/handbook/getting-started/get-a-developer-account/?ref=blog.agami.ai). No separate sandbox, the 7-day trial, and per-region accounts.
7. [Populating your Clio Manage account with sample data](https://docs.developers.clio.com/handbook/build-your-app/populating-your-manage-account-with-sample-data/?ref=blog.agami.ai). Sample CSVs for Contacts, Matters, Time Entries and Expenses.
8. [Clio connector, Fivetran](https://fivetran.com/docs/connectors/applications/clio?ref=blog.agami.ai) and [Lite connectors](https://fivetran.com/docs/connectors/applications/lite-connectors?ref=blog.agami.ai). The `Lite` and `In Dev` badges, and what a Lite connector's schema is built from.
9. [Time Entries](https://help.clio.com/hc/en-us/articles/9289741706779-Time-Entries?ref=blog.agami.ai), [Filter and Export Activities](https://help.clio.com/hc/en-us/articles/9289743207835-Filter-and-Export-Activities?ref=blog.agami.ai) and [Export Data From Clio Manage](https://help.clio.com/hc/en-us/articles/50633368921243-Export-Data-From-Clio-Manage?ref=blog.agami.ai), Clio Help Center. The application surfaces that convert and filter before you see a number.
10. [Clio Duo press release](https://www.clio.com/about/press/clio-duo-unlocks-a-ew-era-of-legal-platform-intelligence/?ref=blog.agami.ai). The vendor's own natural-language answer, inside the application.
11. [agami-core on GitHub](https://github.com/AgamiAI/agami-core?utm%5Fsource=blog&utm%5Fmedium=organic&utm%5Fcampaign=CLIO1)

### 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](https://www.agami.ai/free-trial?ref=blog.agami.ai) [or talk to us →](https://www.agami.ai/talk-to-us?utm%5Fsource=blog&utm%5Fmedium=organic&utm%5Fcampaign=CLIO1)