---
title: 'feat: user-defined calculated/formula fields for data views'
type: task
status: untriaged
description: Add user-defined calculated/formula fields to the data view system.
base_uri: user:task/github/mistakia/league/359-feat-user-defined-calculatedformula-fields-for-data-views.md
created_at: '2026-05-23T20:39:44Z'
entity_id: 963cc218-912d-42fa-a41f-fdfaf3c40fe7
external_id: github:mistakia/league:359
external_url: https://github.com/mistakia/league/issues/359
github_api_id: 4509539364
github_graphql_id: I_kwDOD4Pg488AAAABDMocJA
github_number: 359
github_project_item_id: PVTI_lAHOABvSe84AnvdNzgtn7MI
github_project_number: 3
github_repository_name: league
github_repository_owner: mistakia
github_url: https://github.com/mistakia/league/issues/359
import_cid: bagaaieraaiqx3jgjrzi7la2cyvlgvw4gcoju7msryux7ptfbb7ymlipnqjjq
observations:
  - ████████████████████████████████████████████████
owner_identity_uri: █████████████████████████
priority: Medium
public_read: true
tags:
  - user:tag/league-xo-football.md
updated_at: '2026-11-02T15:47:40.029Z'
visibility_analyzed_at: '2026-11-05T23:48:06.059Z'
---

## Overview

Add user-defined calculated/formula fields to the data view system. A logged-in user can create a saved column that is a closed-grammar arithmetic expression (+, −, ×, ÷, unary minus, parens, numeric literals) over existing data view fields. The calculated field becomes a first-class column in the picker: selectable, sortable, filterable. Rate-type wrapping of calculated fields and cross-grain composition are explicitly deferred.

Expected outcomes:

- Persisted user-owned formula definitions referencing existing field IDs (with their column_params snapshotted at definition time)
- Backend translates a stored AST into a safe SQL expression spliced into the existing column pipeline, respecting NULL handling, division-by-zero, year pushdown, materialized CTE, and grain invariants
- UI for composing and managing formulas, surfaced inside the column picker
- Private to the owning user (visibility model can generalize later if #247 lands)

## Background

**Field registry** (`libs-server/data-views-column-definitions/`, aggregated by `index.mjs`): each column is an object with `column_name`, `source` (grain/splits/attach), `with()` / `join()` / `with_select()` / `main_select()` builders, `supported_rate_types`, `supports_output`. Orchestration in `libs-server/get-data-view-results.mjs`; SQL composed via Knex plus template fragments in `libs-server/data-views/select-string.mjs`. No general expression evaluator exists today — arithmetic is hand-rolled per column. Rate-type division shape is `CAST(num AS DECIMAL) / NULLIF(CAST(den AS DECIMAL), 0)` (`select-string.mjs:60-65`).

**User-saved data views** live in `user_data_views` (Postgres) keyed by `user_id`, with the full view config in a `table_state` jsonb column. Each saved column entry is shaped `{ column_id, params }` — params are pinned per-column at save time (`libs-server/validators.mjs:113-136`). No existing pattern for user-owned columns or sharing/visibility model.

**Rate types** (`libs-server/data-views/rate-type/`) are a closed set of CTE plugins registered via `output-aggregator-registry.mjs`. Columns declare `supported_rate_types`. Issue #210 (parameter-preset reusable columns) is not implemented; orthogonal to this issue (#210 composes parameters, this composes arithmetic across fields).

**Grain bridging** (`libs-server/data-views/identity-bridge-registry.mjs`) has 5 bridges. Restricted to same-grain leaves in v1 keeps complexity bounded.

**#247 status**: zero code. The paper dependency in #359's issue body is dropped — this implementation is standalone and may be generalized later if #247 lands.

**Schema workflow**: the league project does not use incremental migrations. Schema changes are authored in `db/adhoc/YYYY-MM-DD-<slug>.sql`, applied via `yarn db:exec`, and the canonical `db/schema.postgres.sql` is regenerated via `yarn export:schema`.

**No expression library** in `package.json`. The UI emits AST directly as jsonb — no parser needed.

## Design

### Approach

A calculated field is stored as an AST (jsonb) keyed to a `user_id`. On each data-view query the resolver loads the requesting user's saved calculated fields, synthesizes a request-scoped `column_definition` object for each, and merges them into the column lookup map. From that point on the engine treats `calc::<uuid>` identically to a built-in column. The synthesized column's `with()` aggregates each leaf field's `with()` output (deduped by table alias) so CTE materialization, year pushdown, and identity bridging continue to work unchanged. The synthesized `main_select()` returns the AST lowered to a SQL fragment (each numeric subexpression `CAST(... AS DECIMAL)`, division wrapped with `NULLIF(..., 0)`, unary minus emitted as `-(...)`).

The UI builds AST client-side; AST → SQL never happens client-side. Identifiers in saved AST leaves are existing `column_id` values plus pinned `params`.

### File organization

Backend, new:

- `libs-shared/calculated-fields/ast.mjs` — AST node shapes and operator constants (`OP_ADD`, `OP_SUB`, `OP_MUL`, `OP_DIV`, `OP_NEG`), kinds (`binary`, `unary`, `field`, `literal`). Shared so the UI can construct nodes with the same shape the backend validates.
- `libs-server/calculated-fields/evaluate-ast.mjs` — Pure function: lowers `(ast, leaf_sql_fragments)` to a SQL fragment string.
- `libs-server/calculated-fields/validate-ast.mjs` — Save-time validation: every field reference resolves; all leaves share grain; no nested calculated-field references in v1; operator/arity legal; depth ≤ 8.
- `libs-server/calculated-fields/synthesize-column-definition.mjs` — Given one calculated-field row, returns a `column_definition` conforming to the existing registry contract.
- `api/routes/data-view-calculated-fields.mjs` — REST: `GET /api/data-view-calculated-fields`, `POST`, `PUT /:calculated_field_id`, `DELETE /:calculated_field_id`. JWT-gated; ownership enforced on PUT/DELETE.

Backend, modified:

- `libs-server/get-data-view-results.mjs` — Inject the per-user calculated-fields map into the column-id lookup. Resolution of the user's calculated fields is inlined here (no separate cache module — query lifetime is request-scoped already).
- `libs-server/validators.mjs` — Extend column-id validator to accept `calc::<uuid>` shape.
- `api/routes/index.mjs` — Register the new route.

Database, new:

- `db/adhoc/<YYYY-MM-DD>-add-user-data-view-calculated-fields.sql`:
  ```sql
  CREATE TABLE user_data_view_calculated_fields (
    calculated_field_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name varchar(255) NOT NULL,
    ast jsonb NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
  );
  CREATE INDEX user_data_view_calculated_fields_user_id_idx
    ON user_data_view_calculated_fields(user_id);
  ```
- Regenerated `db/schema.postgres.sql`.

Display formatting (decimal precision, data type) and description are deferred — v1 returns numeric only, formatting inferred from leaves at synthesis time. Description can be added when a user needs it.

Frontend, new:

- `app/core/data-view-calculated-fields/` — Redux module (`actions.js`, `reducer.js`, `sagas.js`, `index.js`, `selectors.js`). Holds the user's calculated-field defs keyed by id; sagas fetch on login and on data-views page mount.
- `app/views/components/calculated-field-builder/` — One component handles both create and manage flows. List, rename, delete, and AST composition (field picker + operator buttons + literal entry + nested groups).

Frontend, modified:

- `app/core/data-views-fields/index.js` — Merge user calculated fields into `get_data_views_fields` selector under a new top-level group `"My Calculated Fields"`.
- `app/views/components/players-view-manager/players-view-manager.js` — Add "Create calculated field" entry inside the column picker.

### Integration points

- **Column registry merge**: request-scoped overlay only; no global mutation, no test isolation risk.
- **CTE pipeline** (`with()` aggregation): synthesized column's `with()` calls each leaf field's `with()` once and dedupes by table alias.
- **Splits**: synthesized column's `source.supports_splits` is the intersection of leaves'.
- **Caching**: `get_cache_info()` returns the min TTL across leaves.
- **Auth**: same JWT gate as data-view CRUD; backend rejects requests where `user_id` doesn't match the JWT.
- **Deleted references**: saved views containing references to deleted calculated fields render the column as `null` with an inline "deleted" annotation (no view-load failure).

### Knowledge Base Impact

- Append a note to `libs-server/data-views/ABOUT.md` describing the calculated-fields module and the same-grain v1 restriction.
- On completion add observations to the issue's task entity: `[architecture] AST-only storage (no parser) keeps validation server-side and serialization lossless.`; `[constraint] v1: calculated fields cannot reference other calculated fields and require same-grain leaves.`; `[performance] Synthesized columns reuse leaf CTEs to preserve year-pushdown and materialization invariants.`
- No new domain entry, no new tags, no new guidelines.

### Scratch-tier assessment

No scratch slug. All artifacts are durable (code, SQL adhoc file, schema regen). Explicit per workflow.

### Orchestrator-shape assessment

Single task. Bounded scope, single domain, ~7 backend files + 1 Redux module + 1 React component + 1 SQL adhoc. No multi-week phase boundaries. Explicit per workflow.

## Tasks

Ordered for logical implementation. Each task is bounded to a single concern.

1. **Schema**: author `db/adhoc/<date>-add-user-data-view-calculated-fields.sql` with the table + index. Apply with `yarn db:exec`. Run `yarn export:schema`. Commit adhoc file + schema diff.

2. **AST shape (shared)**: add `libs-shared/calculated-fields/ast.mjs` exporting operator constants and a `validate_ast_shape({ ast })` pure check (structural only — no DB lookups). Used by both backend and frontend.

3. **AST evaluator**: add `libs-server/calculated-fields/evaluate-ast.mjs`. Pure function `evaluate_ast({ ast, leaf_sql_fragments })` → string SQL fragment. Cast each binary subexpression to `DECIMAL`; wrap divide with `NULLIF`; emit unary minus as `-(...)`.

4. **AST validator**: add `libs-server/calculated-fields/validate-ast.mjs`. `validate_ast({ ast, column_registry, user_calculated_field_ids })` rejects: unresolved field refs, mixed grains across leaves, nested calc references, illegal operator/arity, depth > 8.

5. **Column synthesizer**: add `libs-server/calculated-fields/synthesize-column-definition.mjs`. `synthesize_column_definition({ calculated_field, column_registry })` returns a `column_definition` object whose `with()` aggregates leaf CTEs (deduped by table alias), `source.grain` = leaves' shared grain, `source.supports_splits` = leaves' intersection, `supported_rate_types: []`, `get_cache_info()` = min TTL across leaves, `main_select()` calls `evaluate_ast` to emit the final fragment with leaves resolved to their per-row column references.

6. **Validator extension**: modify `libs-server/validators.mjs` to accept `calc::<uuid>` column ids alongside built-in column ids.

7. **API route**: add `api/routes/data-view-calculated-fields.mjs` (`GET` list, `POST` create, `PUT /:calculated_field_id`, `DELETE /:calculated_field_id`). All endpoints JWT-gated. `POST` and `PUT` invoke `validate_ast` before persisting. Register in `api/routes/index.mjs`.

8. **Resolver integration**: modify `libs-server/get-data-view-results.mjs` to load the user's calculated fields (one Knex query keyed on `req.auth.userId`), synthesize each, and merge into the column-definition lookup before column resolution. Anonymous requests get an empty map.

9. **Redux module**: add `app/core/data-view-calculated-fields/` (actions, reducer, sagas, selectors, index). Fetch on login and on data-views page mount; cache in store.

10. **Builder component**: add `app/views/components/calculated-field-builder/`. Single component handles list/create/edit/delete. Composes AST nodes via field picker (reusing the existing column picker for leaf selection), operator buttons, and numeric input. Posts to the new API; updates Redux store on success.

11. **Field-selector integration**: modify `app/core/data-views-fields/index.js` to merge user calculated fields into the selector under a `"My Calculated Fields"` group. Logged-out users see no group.

12. **Picker entry point**: modify `app/views/components/players-view-manager/players-view-manager.js` to add a "Create calculated field" entry that opens the builder.

13. **Tests**:
    - `test/calculated-fields-evaluate-ast.spec.mjs` — unit: simple AST → expected SQL fragment for each operator and combination.
    - `test/calculated-fields-validate-ast.spec.mjs` — unit: rejection of mixed grain, nested calc refs, unresolved field ids, depth overflow, illegal arity.
    - `test/calculated-fields-api.spec.mjs` — integration: CRUD against the API with auth + ownership checks.
    - `test/data-views-calculated-fields-integration.spec.mjs` — integration: end-to-end data view query returning a column composed of two existing fields, verifying SQL emission and row results.

14. **KB update**: append a paragraph to `libs-server/data-views/ABOUT.md` describing the `calculated-fields/` module, the AST-only contract, and the same-grain v1 restriction.

15. **Task wrap-up**: on completion, add the three observations from the Knowledge Base Impact section to the task entity and transition status per `[[user:guideline/wrap-task-hygiene.md]]`.

## Notes

- v1 restricts to same-grain leaves; cross-grain composition is a separate follow-up that opts into `identity-bridge-registry`.
- v1 produces numeric-only columns; display precision inferred from leaves.
- v1 does not support rate-typing a calculated field or using one as a rate-type denominator; both are deferred.
- v1 does not support calculated fields referencing other calculated fields; deferred (would require cycle detection).
- Sharing/visibility model is private-only in v1. If #247 ever lands, calculated fields can adopt the same ownership/sharing primitives.
- Frontend deploy reminder: changes under `app/` and `libs-shared/` require `yarn build && yarn deploy:dist` separately from `yarn deploy`.