> For the complete documentation index, see [llms.txt](https://docs.kula.digital/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.kula.digital/whats-in-your-data/deputy.md).

# Deputy — ontology map

What Deputy gives Kula Intelligence — rosters, timesheets, staff and locations — what it cannot give (pay, above all), and why a fixed timesheet can never show who was late.

**Source id:** `com.deputy` · **Role:** rostering and timesheets — the studio's **staff-hours system of record**, a **satellite** for everything else: it knows who was rostered, who clocked on and off, and which hours a manager approved. It knows nothing about members, classes, attendance or sales. · **Access:** Deputy's Resource API (read) plus its timesheet API (the one write), OAuth, one Deputy install per Kula org.

Deputy answers a different kind of question from every other source. The booking systems say what members did; Deputy says what the **team** did: the roster a manager published, the hours people actually clocked, and whether those hours were approved for payroll. Its data lives in its own canonical schema, **`workforce`**, plus the staff and location rows it adds to `people`.

Two things set it apart from every other map in this section:

* **Pay never enters Kula.** Rates, costs, pay rules and awards are dropped before anything is stored — by design, not by limitation. Hours are answerable; "what did we pay Sam" is not.
* **It is the one source Kula writes back to.** When a manager fixes a timesheet in Kula's Time entries app (revert to the roster, accept, edit), Kula makes that change in Deputy, with a comment on the timesheet saying who changed it (*Changed by Sam (Kula Ops)*). The original times, the new times and the reason are recorded in tables Deputy never sees. See [Kula's own tables](#kulas-own-tables--what-was-decided-here). How to set the app up and use it, for an operator who asks: [Time entries](https://docs.kula.digital/apps/time-entries.md) (`get_doc('help/time-entries')`).

## Coverage at a glance

| Deputy entity              | Canonical home                                                                                                    | Grain                                                                                                          | History                                                                                                   | Freshness                                |
| -------------------------- | ----------------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------- | --------------------------------------------------------------------------------------------------------- | ---------------------------------------- |
| Roster                     | `workforce.shift`                                                                                                 | **one rostered shift** — a person, a place, a start and an end; an **open shift** has no person (`open_shift`) | rolling window **14 days back to 28 days ahead**, re-read every sweep; plus 3 months back on first import | hourly sweep, and on a Deputy webhook    |
| Timesheet                  | `workforce.timesheet` (what Deputy holds **now**) and `workforce.timesheet_original` (what was **first clocked**) | **one clock-on to clock-off**; a person can have several in a day                                              | **3 months back** on first import, then live                                                              | hourly sweep, and on a Deputy webhook    |
| Employee                   | `people.staff`                                                                                                    | one per Deputy employee                                                                                        | current snapshot                                                                                          | refreshed daily, and on a Deputy webhook |
| Company (a workplace)      | `people.location`                                                                                                 | one per Deputy location                                                                                        | current snapshot                                                                                          | refreshed daily                          |
| Operational unit (an area) | raw only                                                                                                          | —                                                                                                              | current snapshot                                                                                          | refreshed daily                          |
| Leave                      | raw only — no canonical table yet                                                                                 | —                                                                                                              | 3 months back, then live                                                                                  | hourly sweep                             |
| Unavailability             | raw only — no canonical table yet                                                                                 | —                                                                                                              | 3 months back, then live                                                                                  | hourly sweep                             |

If a row isn't in this table, we don't have it. Two further facts about the roster:

* **Future shifts are here.** A published roster lands ahead of time, so `workforce.shift` holds next week's rostered hours as well as last week's. A count of shift rows is therefore **not** a count of shifts worked.
* **Every row carries `site_id` — the Deputy install, not a Kula site.** It is ingest provenance (it looks like `acme.au.deputy.com`). Where a shift or timesheet happened is `location_id` / `people.location.name`.

### Kula's own tables — what was decided here

These are **not from Deputy**. They record what Kula's users decided, and they are why a Deputy studio has a real audit trail:

| Table                                        | What it holds                                                                                                                                                                               |
| -------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `workforce.timesheet_fix`                    | one row per fix a manager (or the auto-clear rule) made, and one per change seen being made in Deputy itself (`external_change`) — before and after, who, when, why                         |
| `workforce.vendor_write`                     | one row per attempt at Deputy, with the step it reached (comment, update, approve, re-read) — internal, not queryable                                                                       |
| `workforce.change_log`                       | **every** change to anything in the schema, append-only, with before and after                                                                                                              |
| `workforce.employer`, `workforce.employment` | which company **pays** each person, and as what (full time, part time, casual, contractor) from which date — Deputy records only where someone works, so this is the operator's own mapping |
| `workforce.settings`, `workforce.access`     | the studio's clearing rules (tolerance, pay period) and who holds the payroll grant — internal, not queryable                                                                               |

`workforce.timesheet` also has three Kula-owned columns — `review_status` (`pending`, `auto_cleared`, `fixed`, `accepted`, `left_pending`), `reviewed_at` and `reviewed_by`. They are Kula's word on the timesheet, not Deputy's: `review_status = 'pending'` does **not** mean Deputy hasn't approved it. Read `time_approved` for that.

## What we cannot get

Each item names its gap kind.

**Pay — rates, costs, pay rules, awards, agreements.** Dropped at the boundary before anything lands: not in the raw copy, not in a canonical row, not in a log. Kula is an operations product and pay stays in Deputy. "What did we pay X", "labour cost as a percentage of revenue" and "who is on overtime" are not answerable here; **hours** are. *(not pulled, by design)*

**Who pays each person.** Deputy records the location someone works at, not the legal entity that employs them. A studio whose trainers are paid by one company and whose front-of-house by another has to say so: the operator maps each person to a company in the Time entries app (`workforce.employment`). Until they have, the company is simply unknown — the payroll export is split by it. *(product doesn't do it)*

**Leave and unavailability as answers.** Both land raw; neither has a canonical table yet, so "who is on leave next week" and "who can't work Fridays" are not answerable from the canonical model today. *(endpoint exists, not wired)*

**Areas on a shift.** The operational unit (area) a shift is for is kept as its Deputy id only; there is no area name or area-level roll-up. *(endpoint exists, not wired)*

**Anything before Kula connected, beyond the first import.** The first import reaches back **3 months** of timesheets and rosters. Older history needs a wider window requested. *(not pulled for this studio)*

**What a timesheet looked like before Kula first saw it.** If a manager already fixed a timesheet in Deputy before Kula's first capture, the fixed times are what Kula holds as the "original". The lateness record is reliable **from connection forward**; it understates anything fixed before. *(live-forward only)*

**Anything about members, classes, attendance or money.** Deputy is the team's system, not the floor's or the till's. `bookings.*`, `commerce.*` and `people.member` get nothing from this source. *(product doesn't do it)*

**A time zone for a location.** A Deputy workplace carries none. The dates (`work_date`, `shift_date`) are Deputy's own **site-local calendar day**; the instants (`starts_at`, `ends_at`) are UTC. Bucket in the studio's time zone, not in UTC. *(vendor has no API)*

**Missed webhook events.** Deputy does not replay events it failed to deliver. The hourly sweep is the reconciliation: a change made in Deputy appears in Kula within the hour at worst, usually within seconds. *(vendor has no API)*

## Identity and join keys

| Thing        | Deputy id                            | Canonical                                                                                                                                               |
| ------------ | ------------------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------- |
| The install  | host name, e.g. `acme.au.deputy.com` | `site_id` on every row — **provenance, not a Kula site code**                                                                                           |
| Employee     | `Id`                                 | `people.staff.source_external_id` (`source = 'com.deputy'`); on shifts and timesheets, `staff_external_id`, and `staff_id` once the employee has landed |
| Workplace    | `Id` (a Deputy "Company")            | `people.location.source_external_id`; on shifts and timesheets, `location_external_id` and `location_id`                                                |
| Roster entry | `Id`                                 | `workforce.shift.source_external_id`; a timesheet's `shift_external_id` / `shift_id` is the shift it was clocked against — **NULL means unrostered**    |
| Timesheet    | `Id`                                 | `workforce.timesheet.source_external_id`; `workforce.timesheet_original.timesheet_id` is the same row's first capture                                   |

**The same person is one row per source.** A Deputy employee who is also a GymMaster instructor is two `people.staff` rows. Kula links them by **email and mobile number** (`canonical_staff_id`) — which is why Deputy employees land with both. Always roll up with `COALESCE(canonical_staff_id, id)` before counting people or summing hours across sources. A person whose email or mobile doesn't match anything stays a separate row; that is the normal, unmatched remainder, not a fault.

**Kula's site is not Deputy's location.** The studio's sites in Kula Ops are mapped to Deputy locations by the owner (Time entries → Settings). That mapping is what limits a manager to their own site's timesheets; a Deputy location nobody has mapped is visible to the owner alone. In SQL, "where" is always `location_id` joined to `people.location`.

## Counting traps

**Filter `discarded = false`.** Deputy soft-deletes: a discarded timesheet stays, flagged. Counting without the filter counts deleted rows.

**`in_progress` timesheets have no end and no hours yet.** `ends_at` is NULL until the clock-off. A report of "hours so far this fortnight" must keep them out of an average and say so.

**Lateness is read from the ORIGINAL, never the current timesheet.** The operator's chore is fixing late and early timesheets by reverting them to the roster — after which `workforce.timesheet` shows the person on time **by definition**. "Who is regularly late" compares `workforce.timesheet_original` (what was clocked) with `workforce.shift` (what was rostered). Reading `workforce.timesheet` for lateness reports a studio of punctual staff.

**Two approvals, and they are different.** `time_approved` is a manager accepting the **hours**; `pay_rule_approved` is the payroll rule step after it. "Approved hours" means `time_approved`. A timesheet can be time-approved and not yet pay-approved.

**`total_hours` is Deputy's own figure.** It applies Deputy's rounding and break rules, so it is not always `ends_at − starts_at − break`. That gap is exactly why managers revert to the roster. For "hours payroll will see", sum `total_hours`; for "how long did they actually work", compute from `workforce.timesheet_original`.

**Leave is not worked time.** `is_leave` rows are leave. Keep them out of hours worked and out of "who was at work".

**One person can have several timesheets in a day.** Split shifts and a double clock-on are real. Sum per person per day; "one row per person-day" is wrong.

**`workforce.shift` includes the future and the unassigned.** Filter `shift_date <= current_date` (or join a timesheet) before calling a row "worked", and exclude `open_shift` rows from per-person rostered hours — nobody holds them. A shift that isn't `published` is not yet visible to the staff.

**`variance_start_min` / `variance_end_min` are a cache.** They are the clock-on and clock-off minus the roster, computed when the row was written; NULL when the roster hadn't landed yet. Recompute from the joined shift rather than trusting them for a recent timesheet. Positive is **late** (start) or **late finish** (end); negative is early.

**A night shift belongs to the day it started.** `work_date` is the site-local day of the clock-on; the clock-off may be the next calendar day.

**An unrostered timesheet isn't an error.** `shift_id IS NULL` means someone clocked on with no shift: a swap, a cover, an extra. It is an exception for a manager to look at, and there is nothing to revert it to.

**A pay period is the owner's setting.** `pay_period.anchor_date` and `pay_period.days` (default 14) are set in Time entries. With no anchor set, the workforce tools use a rolling window ending today and say so — don't present it as the studio's pay period.

**`external_change` rows are not Kula's doing.** In `timesheet_fix`, a row of that kind is somebody editing the timesheet **in Deputy** after Kula captured it. Kula's own fixes are `revert_to_roster`, `accept_actual`, `edit`, `auto_clear` and `leave_pending`.

**No guarded views, and today is in.** Unlike the event tables, the `workforce` tables have no midnight cut-off: today's timesheets are readable, and a person who clocked on this morning is already there.

## Questions this source can and can't answer

**Can answer well**

* Is this pay period ready for payroll — what is cleared, what is still an exception, by location
* Hours by person, by location, by week — rostered against worked
* Who is regularly late or leaves early, and on which weekday
* What next week's roster looks like, by location and day, and how many shifts are still open
* What a manager changed on a timesheet, who, when and why; what was changed in Deputy behind Kula's back
* Which company pays whom, once the operator has said

**Cannot answer from Deputy alone**

* What anyone was paid, or labour cost of any kind *(pay never lands)*
* Whether staffing matched demand *(needs a booking source for the demand side; Deputy has only the staff side)*
* Who is on leave or unavailable *(landed raw, not mapped yet)*
* Any member, class, attendance or revenue question *(not Deputy's data)*
* Who was late **before** Kula connected *(history starts at connection)*

**Phrase a gap as the vendor's or the product's limit, not missing data.** "Kula doesn't take pay from Deputy — only hours" is correct and useful; "your payroll data is incomplete" is not.

## Recipes — what works well

### Is the fortnight ready → `get_timesheet_summary`

Per location: timesheets, cleared, would-clear, exceptions by kind (`late`, `early_out`, `long`, `missed_clock`, `unrostered`), unapproved, approved hours and a `ready_to_export` flag. It reads the studio's own tolerance and pay period. Counts only — available at Analytics scope.

### Hours by person → `get_staff_hours`

Rostered against worked hours per person per pay period, rolled up to the human across sources. Names, so Operations scope. Use `periods` for a trend.

### Who is late → `get_lateness_patterns`, not `workforce.timesheet`

It reads the **first capture** against the roster and names a weekday pattern only where there are enough shifts ("late on Wednesdays (4 of 5)"). Hand-rolling this from `workforce.timesheet` reads the fixed times and finds nothing — the wrong tool gives a clean-looking wrong answer.

### Next week's roster → `workforce.shift`

There is no tool for the forward roster; the table has it:

```sql
SELECT s.shift_date,
       l.name                                              AS location,
       count(*)                                            AS shifts,
       count(*) FILTER (WHERE s.open_shift)                AS open_shifts,
       round((sum(extract(epoch FROM (s.ends_at - s.starts_at))
                  - s.mealbreak_seconds) FILTER (WHERE NOT s.open_shift) / 3600)::numeric, 1) AS rostered_hours
FROM workforce.shift s
LEFT JOIN people.location l ON l.id = s.location_id
WHERE s.shift_date BETWEEN current_date AND current_date + 6
  AND s.published
  AND s.ends_at IS NOT NULL
GROUP BY 1, 2 ORDER BY 1, 2;
```

Only **published** shifts, because the staff can't see the rest. The roster runs 28 days ahead; beyond that there is nothing.

### Approved hours by location and week → `workforce.timesheet`

```sql
SELECT date_trunc('week', t.work_date)::date AS week,
       l.name                                AS location,
       round(sum(t.total_hours), 1)          AS approved_hours
FROM workforce.timesheet t
LEFT JOIN people.location l ON l.id = t.location_id
WHERE NOT t.discarded
  AND NOT t.is_leave
  AND t.time_approved
  AND t.work_date >= current_date - 56
GROUP BY 1, 2 ORDER BY 1, 2;
```

`total_hours` is Deputy's figure after its own rules — say so when the number is going to payroll.

### Is the data current → `get_system_status`, then the capture window

`get_system_status` first: it reports Deputy as current / stale / failing. Then establish how far back the lateness record goes before charting a trend over it:

```sql
SELECT min(work_date)                          AS timesheets_from,
       max(work_date)                          AS timesheets_to,
       count(*)                                AS timesheets,
       count(*) FILTER (WHERE discarded)       AS discarded,
       count(*) FILTER (WHERE in_progress)     AS clocked_on_now
FROM workforce.timesheet
WHERE source = 'com.deputy';
```

A `timesheets_from` three months back is the first import, not the studio's start. Don't read earlier absence as "no staff".

### What doesn't work on Deputy alone

`list_at_risk_members`, `get_class_utilisation`, `get_teacher_performance`, `get_retention_curve`, `get_member_payments` — everything member-, class- or money-shaped needs sources Deputy isn't. On a Deputy-only org they return empty, and that is the correct result, not a fault.

### The interesting questions are in the join

With a booking source **and** Deputy connected, the valuable analyses cross the two: instructor hours rostered against classes taught (via the shared person, `COALESCE(canonical_staff_id, id)`); staffing against utilisation by weekday and hour; the front desk's rostered hours against when members actually arrive. Keep pay out of it — Deputy never had it to give.

## Where this lives in the code

| Concern                                              | Path                                                                      |
| ---------------------------------------------------- | ------------------------------------------------------------------------- |
| Ingestor (pull, webhooks, the timesheet fix)         | `services/ingestors/deputy/`                                              |
| Pay stripped at the boundary                         | `services/ingestors/deputy/internal/dpclient/redact.go`                   |
| Shift and timesheet transforms                       | `services/ingestors/deputy/internal/transform/workforce.go`               |
| 1:1 projections (employees, locations)               | `services/intelligence/internal/ingest/project/templates_deputy.go`       |
| Canonical writers                                    | `services/intelligence/internal/ingest/pg/workforce.go`                   |
| Canonical tables                                     | `services/intelligence/migrations/postgres/org/350_workforce/`            |
| The rule that clears a timesheet                     | `services/intelligence/internal/workforce/autoclear/`                     |
| The ledgered write at Deputy                         | `services/intelligence/internal/workforce/fixer/`                         |
| Time entries app (fix queue, export, access)         | `services/intelligence/internal/ops/workforce/`                           |
| Read tools for Claude                                | `services/intelligence/internal/tools/workforce_hours.go`                 |
| Operator-facing connect guide                        | [Deputy connector](https://docs.kula.digital/your-data-sources/deputy.md) |
| Operator-facing Time entries manual (set-up and use) | [Time entries](https://docs.kula.digital/apps/time-entries.md)            |
