Documentation source

Automation Ratio

The scoring engine for the gamification thesis — persistent time-series of agent-vs-human work ownership per tenant, workstream, role, and user.

## Overview

The automation ratio is the platform's answer to "am I shifting my role from manual work to agent work?" It turns the command-center thesis into a single, persistent score that surfaces on Today, on insights, on entity detail, and in the weekly digest — all reading from one rolled-up table.

Before this layer existed, Today showed a static "agent share this week: X% (↑ Y pp)" strip computed on every page load with no history, no scope discrimination, and no leaderboard. The new layer is **read-cheap by construction**: one nightly cron writes pre-aggregated snapshot rows; every consumer surface hits a single indexed query.

The score serves the dopamine loop the gamification thesis depends on. PE analysts want visible proof their AI-leveraged hours grew this quarter. Consulting managers want to show clients "your team's % of automated work tripled." First-time admins need the loop visible week-one to know the platform is "doing something."

## Key Concepts

### Scope

Every snapshot is keyed by a `scope_kind` discriminator + an optional `scope_id`. The four supported scopes:

| Scope | `scope_id` | Use |
|---|---|---|
| `tenant` | null | Workspace-level KPI, transformation dashboard |
| `workstream` | workstream entity id | Per-workstream rollup, leaderboard |
| `role` | role entity id | Per-role rollup, role detail page |
| `user` | profile id | Per-user trend, Today card |

The discriminated union in TypeScript:

```ts
type AutomationScope =
  | { kind: "tenant" }
  | { kind: "workstream"; id: string }
  | { kind: "role"; id: string }
  | { kind: "user"; id: string };
```

### Effective owner

Per [ADR-0010](/docs/adr/0010-effective-owner-for-automation-ratio), the effective owner of a completed task entity is derived **only** from `entities[type=task].content.completed_by`:

- `"agent"` → counts toward `agent_owned_count`
- `"human"` or `"system"` → counts toward `human_owned_count`
- Anything else (null, missing) → excluded from the rollup

This single source of truth means the Today card, the leaderboard, and the trendline cannot disagree about who owned the work.

### Snapshot

A row in `automation_ratio_snapshots`:

| Column | Type | Notes |
|---|---|---|
| `tenant_id` | uuid | RLS scope |
| `scope_kind` | enum | `tenant` / `workstream` / `role` / `user` |
| `scope_id` | uuid \| null | Null only when `scope_kind = 'tenant'` |
| `period_start` / `period_end` | date | Inclusive window |
| `agent_owned_count` / `human_owned_count` | integer | Raw counts |
| `ratio` | numeric(5,4) | 0..1 — agent share |
| `delta_pp` | numeric(6,2) \| null | Percentage points vs prior period; null when no prior data |
| `computed_at` | timestamptz | When the cron wrote this row |

A single `UNIQUE NULLS NOT DISTINCT` constraint on `(tenant_id, scope_kind, scope_id, period_start)` enforces idempotent upserts — `NULL` `scope_id` values for the tenant scope collapse to one tuple, so PostgREST can resolve `onConflict` against one named constraint regardless of which scope is being written.

### Single calculation primitive

Per [ADR-0011](/docs/adr/0011-single-automation-ratio-aggregator), all automation-ratio counting flows through `aggregateBuckets()` in `features/automation-ratio/server/aggregate.ts`. Two callers consume the primitive:

1. The **nightly cron** (`computeAutomationRatioForTenant`) — fans out to all four scopes and persists snapshots.
2. The **live `/today` card** (`weeklyTaskEntityAutomationRatio` in `features/actions/server/queries.ts`) — fetches user-scoped task entities and runs the same aggregator twice (current window + prior window) for the week-over-week delta.

Goal-progress reads (`computeAutomationRatioProgress`) and leaderboard / trend reads consume snapshots written by the cron — they MUST NOT re-implement counting against the entities table. New surfaces follow the same rule.

## How It Works

```
Task entities (completed) ─┐
                           ├──► automation-ratio-snapshots-cron (Inngest, nightly 0 3 * * *)
parent_id → role           │              │
   role.parent → workstream│              ▼
                           │   automation_ratio_snapshots
                           │              │
Onassignment-changed hook  │      ┌───────┼─────────┐
(human → agent shift)      │      ▼       ▼         ▼
   ├─► activity row        │  /api/automation-ratio (trend + current)
   └─► task.delegated_to_  │  /api/automation-ratio/leaderboard
       agent analytics     │  POST /api/admin/automation-ratio/recompute
```

### Nightly compute

`computeAutomationRatioForTenant(tenantId, periodStart, periodEnd, options)`:

1. Loads completed `task` entities in the window via the admin client (no RLS — system path).
2. Resolves `task → role → workstream` lookups via `parent_id` joins, only when those scopes are requested.
3. `aggregateBuckets()` (imported from `./aggregate`) bucketizes each task into the requested scopes by effective owner. Same primitive the live `/today` card uses — see [ADR-0011](/docs/adr/0011-single-automation-ratio-aggregator).
4. Looks up the prior period's snapshots in one batch to compute `delta_pp`.
5. Upserts in a single batch on the `(tenant_id, scope_kind, scope_id, period_start)` UNIQUE NULLS NOT DISTINCT constraint — idempotent across reruns.

### Cron gate

`automation-ratio-snapshots-cron` checks `isCronAllowed()` before any work — the same gate that protects every other Sprinter cron from accidentally firing in preview deploys or local dev (without `ENABLE_LOCAL_CRON=1`).

### Shift detection

`onTaskAssignmentChanged(ctx, prev, next, taskEntityId)` is a server-side hook **designed to be wired into task-assignment write sites in a follow-up PR** (the producer is captured as a follow-up — see `documents/work/2026-04-25-automation-ratio-gamification/followups.md`). When wired, and only when the task transitions from human-owned (`assignee_user_id` set, `agent_slug` null) to agent-owned (`assignee_user_id` null, `agent_slug` set), it will:

- Inserts an activity row with `action: 'shift_to_agent'` and the prev-user / new-agent metadata.
- Fires the `task.delegated_to_agent` analytics event.
- (Future) Broadcasts on a tenant realtime channel so the celebration toast fires instantly.

The hook is fire-and-forget — failure of any side effect (analytics, activity, realtime) does not block the caller's transaction.

## API Reference

### Server reads (RLS-scoped)

```ts
// features/automation-ratio/server/snapshots.ts
getAutomationRatioTrend(scope: AutomationScope, opts?: { periods?: number }):
  Promise<AutomationRatioSnapshot[]>;

getCurrentAutomationRatio(scope: AutomationScope):
  Promise<AutomationRatioSnapshot | null>;
```

### Compute (admin client)

```ts
// features/automation-ratio/server/compute.ts
computeAutomationRatioForTenant(
  tenantId: string,
  periodStart: Date,
  periodEnd: Date,
  options?: { kinds?: AutomationScopeKind[] },
): Promise<{ written: number; skipped: number }>;

defaultWeeklyWindow(now?: Date): { periodStart: Date; periodEnd: Date };

aggregateBuckets(input: { ... }): Map<string, ScopeBucket>; // pure helper
```

### Leaderboard

```ts
// features/automation-ratio/server/leaderboard.ts
getAutomationLeaderboard(opts: {
  scopeKind: "workstream" | "role" | "user";
  limit?: number;       // 1..200, default 25
  periodStart?: string; // ISO date, defaults to most recent
}): Promise<AutomationLeaderboardRow[]>;
```

### Shift detection

```ts
// features/automation-ratio/server/on-assignment-changed.ts
onTaskAssignmentChanged(
  ctx: { tenantId: string; userId: string | null },
  prev: { assignee_user_id: string | null; agent_slug: string | null },
  next: { assignee_user_id: string | null; agent_slug: string | null },
  taskEntityId: string,
): Promise<{ shifted: boolean }>;

isHumanToAgentShift(prev, next): boolean; // pure predicate
```

### REST routes

| Route | Auth | Notes |
|---|---|---|
| `GET /api/automation-ratio?scope=&scopeId=&periods=` | `requireAuth` | Returns `{ current, trend }` for the scope; periods clamped 1..52 |
| `GET /api/automation-ratio/leaderboard?scopeKind=&limit=&periodStart=` | `requireAuth` | Returns `{ rows }` ranked by ratio desc |
| `POST /api/admin/automation-ratio/recompute` | `requireAdmin` | Admin-only ad-hoc trigger; default 7-day window |

## For Agents

Agents currently consume this layer through the REST routes above. Future tools may include:

- `getAutomationRatio({ scope, scopeId? })` — agent-callable wrapper around the trend route.
- `recomputeAutomationRatio({ tenantId, windowDays? })` — admin-only tool wrapping the recompute route.

These are intentionally not yet shipped — the read APIs are stable, but the semantics around agent-initiated recomputes still need an authorization model that doesn't let an agent spam the rollup.

## Design Decisions

- **One table, four scopes** — discriminator over four parallel tables. See `.claude/rules/no-parallel-systems.md`.
- **Pre-aggregate, don't compute on read** — Today loads this on every visit; reads must be a single indexed query.
- **Shift detection ≠ score** — The celebration UX (act of delegation) and the score (who eventually completed the work) are orthogonal. They can never drift because they answer different questions.
- **Goals as fields, not their own table** — `role.automationGoal` is a schema field; per-user goal lives in `profiles.metadata`. Adding a goals table would violate the no-parallel-systems rule.
- **Subtle celebration UX** — zero-chroma glyphs, one-time toast, no confetti. PE/finance audience prefers restraint.
- **No backfill on day one** — first cron run produces 1 snapshot; sparkline renders "building history…" until ≥ 2 weeks of data exist. Backfill is a deferred follow-up.

## Related Modules

- [Sessions](./sessions) — completed sessions are the input the cron reads.
- [Tasks](./tasks) — Task entities are what get scored.
- [Analytics](./analytics-cost) — the `task.delegated_to_agent` event lives here.
- [Inngest](../integrations/inngest) — `automation-ratio-snapshots-cron` registration.
- [ADR-0010](/docs/adr/0010-effective-owner-for-automation-ratio) — the effective-owner derivation rule.
- [ADR-0011](/docs/adr/0011-single-automation-ratio-aggregator) — single shared aggregator across the cron and the live `/today` path.

## Overview The automation ratio is the platform's answer to "am I shifting my role from manual work to agent work?" It turns the command-center thesis into a single, persistent score that surfaces on Today, on insights, on entity detail, and in the weekly digest — all reading from one rolled-up table. Before this layer existed, Today showed a static "agent share this week: X% (↑ Y pp)" strip computed on every page load with no history, no scope discrimination, and no leaderboard. The new layer is **read-cheap by construction**: one nightly cron writes pre-aggregated snapshot rows; every consumer surface hits a single indexed query. The score serves the dopamine loop the gamification thesis depends on. PE analysts want visible proof their AI-leveraged hours grew this quarter. Consulting managers want to show clients "your team's % of automated work tripled." First-time admins need the loop visible week-one to know the platform is "doing something." ## Key Concepts ### Scope Every snapshot is keyed by a `scope_kind` discriminator + an optional `scope_id`. The four supported scopes: | Scope | `scope_id` | Use | |---|---|---| | `tenant` | null | Workspace-level KPI, transformation dashboard | | `workstream` | workstream entity id | Per-workstream rollup, leaderboard | | `role` | role entity id | Per-role rollup, role detail page | | `user` | profile id | Per-user trend, Today card | The discriminated union in TypeScript: ```ts type AutomationScope = | { kind: "tenant" } | { kind: "workstream"; id: string } | { kind: "role"; id: string } | { kind: "user"; id: string }; ``` ### Effective owner Per [ADR-0010](/docs/adr/0010-effective-owner-for-automation-ratio), the effective owner of a completed task entity is derived **only** from `entities[type=task].content.completed_by`: - `"agent"` → counts toward `agent_owned_count` - `"human"` or `"system"` → counts toward `human_owned_count` - Anything else (null, missing) → excluded from the rollup This single source of truth means the Today card, the leaderboard, and the trendline cannot disagree about who owned the work. ### Snapshot A row in `automation_ratio_snapshots`: | Column | Type | Notes | |---|---|---| | `tenant_id` | uuid | RLS scope | | `scope_kind` | enum | `tenant` / `workstream` / `role` / `user` | | `scope_id` | uuid \| null | Null only when `scope_kind = 'tenant'` | | `period_start` / `period_end` | date | Inclusive window | | `agent_owned_count` / `human_owned_count` | integer | Raw counts | | `ratio` | numeric(5,4) | 0..1 — agent share | | `delta_pp` | numeric(6,2) \| null | Percentage points vs prior period; null when no prior data | | `computed_at` | timestamptz | When the cron wrote this row | A single `UNIQUE NULLS NOT DISTINCT` constraint on `(tenant_id, scope_kind, scope_id, period_start)` enforces idempotent upserts — `NULL` `scope_id` values for the tenant scope collapse to one tuple, so PostgREST can resolve `onConflict` against one named constraint regardless of which scope is being written. ### Single calculation primitive Per [ADR-0011](/docs/adr/0011-single-automation-ratio-aggregator), all automation-ratio counting flows through `aggregateBuckets()` in `features/automation-ratio/server/aggregate.ts`. Two callers consume the primitive: 1. The **nightly cron** (`computeAutomationRatioForTenant`) — fans out to all four scopes and persists snapshots. 2. The **live `/today` card** (`weeklyTaskEntityAutomationRatio` in `features/actions/server/queries.ts`) — fetches user-scoped task entities and runs the same aggregator twice (current window + prior window) for the week-over-week delta. Goal-progress reads (`computeAutomationRatioProgress`) and leaderboard / trend reads consume snapshots written by the cron — they MUST NOT re-implement counting against the entities table. New surfaces follow the same rule. ## How It Works ``` Task entities (completed) ─┐ ├──► automation-ratio-snapshots-cron (Inngest, nightly 0 3 * * *) parent_id → role │ │ role.parent → workstream│ ▼ │ automation_ratio_snapshots │ │ Onassignment-changed hook │ ┌───────┼─────────┐ (human → agent shift) │ ▼ ▼ ▼ ├─► activity row │ /api/automation-ratio (trend + current) └─► task.delegated_to_ │ /api/automation-ratio/leaderboard agent analytics │ POST /api/admin/automation-ratio/recompute ``` ### Nightly compute `computeAutomationRatioForTenant(tenantId, periodStart, periodEnd, options)`: 1. Loads completed `task` entities in the window via the admin client (no RLS — system path). 2. Resolves `task → role → workstream` lookups via `parent_id` joins, only when those scopes are requested. 3. `aggregateBuckets()` (imported from `./aggregate`) bucketizes each task into the requested scopes by effective owner. Same primitive the live `/today` card uses — see [ADR-0011](/docs/adr/0011-single-automation-ratio-aggregator). 4. Looks up the prior period's snapshots in one batch to compute `delta_pp`. 5. Upserts in a single batch on the `(tenant_id, scope_kind, scope_id, period_start)` UNIQUE NULLS NOT DISTINCT constraint — idempotent across reruns. ### Cron gate `automation-ratio-snapshots-cron` checks `isCronAllowed()` before any work — the same gate that protects every other Sprinter cron from accidentally firing in preview deploys or local dev (without `ENABLE_LOCAL_CRON=1`). ### Shift detection `onTaskAssignmentChanged(ctx, prev, next, taskEntityId)` is a server-side hook **designed to be wired into task-assignment write sites in a follow-up PR** (the producer is captured as a follow-up — see `documents/work/2026-04-25-automation-ratio-gamification/followups.md`). When wired, and only when the task transitions from human-owned (`assignee_user_id` set, `agent_slug` null) to agent-owned (`assignee_user_id` null, `agent_slug` set), it will: - Inserts an activity row with `action: 'shift_to_agent'` and the prev-user / new-agent metadata. - Fires the `task.delegated_to_agent` analytics event. - (Future) Broadcasts on a tenant realtime channel so the celebration toast fires instantly. The hook is fire-and-forget — failure of any side effect (analytics, activity, realtime) does not block the caller's transaction. ## API Reference ### Server reads (RLS-scoped) ```ts // features/automation-ratio/server/snapshots.ts getAutomationRatioTrend(scope: AutomationScope, opts?: { periods?: number }): Promise<AutomationRatioSnapshot[]>; getCurrentAutomationRatio(scope: AutomationScope): Promise<AutomationRatioSnapshot | null>; ``` ### Compute (admin client) ```ts // features/automation-ratio/server/compute.ts computeAutomationRatioForTenant( tenantId: string, periodStart: Date, periodEnd: Date, options?: { kinds?: AutomationScopeKind[] }, ): Promise<{ written: number; skipped: number }>; defaultWeeklyWindow(now?: Date): { periodStart: Date; periodEnd: Date }; aggregateBuckets(input: { ... }): Map<string, ScopeBucket>; // pure helper ``` ### Leaderboard ```ts // features/automation-ratio/server/leaderboard.ts getAutomationLeaderboard(opts: { scopeKind: "workstream" | "role" | "user"; limit?: number; // 1..200, default 25 periodStart?: string; // ISO date, defaults to most recent }): Promise<AutomationLeaderboardRow[]>; ``` ### Shift detection ```ts // features/automation-ratio/server/on-assignment-changed.ts onTaskAssignmentChanged( ctx: { tenantId: string; userId: string | null }, prev: { assignee_user_id: string | null; agent_slug: string | null }, next: { assignee_user_id: string | null; agent_slug: string | null }, taskEntityId: string, ): Promise<{ shifted: boolean }>; isHumanToAgentShift(prev, next): boolean; // pure predicate ``` ### REST routes | Route | Auth | Notes | |---|---|---| | `GET /api/automation-ratio?scope=&scopeId=&periods=` | `requireAuth` | Returns `{ current, trend }` for the scope; periods clamped 1..52 | | `GET /api/automation-ratio/leaderboard?scopeKind=&limit=&periodStart=` | `requireAuth` | Returns `{ rows }` ranked by ratio desc | | `POST /api/admin/automation-ratio/recompute` | `requireAdmin` | Admin-only ad-hoc trigger; default 7-day window | ## For Agents Agents currently consume this layer through the REST routes above. Future tools may include: - `getAutomationRatio({ scope, scopeId? })` — agent-callable wrapper around the trend route. - `recomputeAutomationRatio({ tenantId, windowDays? })` — admin-only tool wrapping the recompute route. These are intentionally not yet shipped — the read APIs are stable, but the semantics around agent-initiated recomputes still need an authorization model that doesn't let an agent spam the rollup. ## Design Decisions - **One table, four scopes** — discriminator over four parallel tables. See `.claude/rules/no-parallel-systems.md`. - **Pre-aggregate, don't compute on read** — Today loads this on every visit; reads must be a single indexed query. - **Shift detection ≠ score** — The celebration UX (act of delegation) and the score (who eventually completed the work) are orthogonal. They can never drift because they answer different questions. - **Goals as fields, not their own table** — `role.automationGoal` is a schema field; per-user goal lives in `profiles.metadata`. Adding a goals table would violate the no-parallel-systems rule. - **Subtle celebration UX** — zero-chroma glyphs, one-time toast, no confetti. PE/finance audience prefers restraint. - **No backfill on day one** — first cron run produces 1 snapshot; sparkline renders "building history…" until ≥ 2 weeks of data exist. Backfill is a deferred follow-up. ## Related Modules - [Sessions](./sessions) — completed sessions are the input the cron reads. - [Tasks](./tasks) — Task entities are what get scored. - [Analytics](./analytics-cost) — the `task.delegated_to_agent` event lives here. - [Inngest](../integrations/inngest) — `automation-ratio-snapshots-cron` registration. - [ADR-0010](/docs/adr/0010-effective-owner-for-automation-ratio) — the effective-owner derivation rule. - [ADR-0011](/docs/adr/0011-single-automation-ratio-aggregator) — single shared aggregator across the cron and the live `/today` path.