Computed fields and rollups

What you will achieve

Invoice lines that compute their own total, and invoices that sum their lines, count them and add VAT, with no caller able to write any of those numbers. About ten minutes, starting from the stack in Run your own descriptor.

Before you start

• The stack from Run your own descriptor, run from its alvo-help-desk directory with COMPOSE_FILE and that page’s secrets exported in this shell. This page uses the admin key (roles admin, authenticated).
• Entities and fields, for fields, facets and references.

Choose the lowest rung that expresses the value: a computed field for an expression over the same row, a rollup for an aggregate over related rows, and a before-hook for a value decided when the row is written.

1. Compute a value from the row

A computed field is a CEL expression over the other fields of its own row. The database stores it as a generated column and keeps it current, and nobody writes it.

1. Start the stack over this page’s first descriptor. The first command deletes the stack’s database:

   Terminal

   docker compose down --volumes
   curl -fsSL -o help-desk.alvo.json https://raw.githubusercontent.com/Burgyn/MMLib.Alvo/main/website/src/snippets/computed-and-rollups/01-computed.alvo.json
   docker compose up --wait --wait-timeout 90

2. line_total multiplies two fields of the same line:

   help-desk.alvo.json

   {
     "$schema": "https://alvo.dev/schema/v1/project.json",
     "apiVersion": "alvo.dev/v1",
     "name": "help-desk",
     "auth": {
       "providers": ["local"],
       "roles": ["admin", "agent"]
     },
     "entities": {
       "invoices": {
         "fields": {
           "number": {
             "type": "string", "required": true, "unique": true, "maxLength": 20
           },
           "vat_total": {
             "type": "decimal", "precision": 12, "scale": 2, "required": true,
             "default": 0
           }
         },
         "rules": {
           "list": "'authenticated' in @user.roles",
           "get": "'authenticated' in @user.roles",
           "create": "'admin' in @user.roles",
           "update": "'admin' in @user.roles"
         }
       },
       "invoice_lines": {
         "fields": {
           "invoice_id": {
             "type": "ref", "entity": "invoices", "required": true,
             "onDelete": "cascade"
           },
           "description": {
             "type": "string", "required": true, "maxLength": 200
           },
           "unit_price": {
             "type": "decimal", "precision": 10, "scale": 2, "required": true
           },
           "quantity": {
             "type": "decimal", "precision": 8, "scale": 2, "required": true
           },
           "line_total": {
             "type": "decimal", "precision": 18, "scale": 2,
             "computed": "unit_price * quantity"
           }
         },
         "rules": {
           "list": "'authenticated' in @user.roles",
           "get": "'authenticated' in @user.roles",
           "create": "'admin' in @user.roles",
           "update": "'admin' in @user.roles",
           "delete": "'admin' in @user.roles"
         }
       }
     }
   }

   A computed field is declared as the type it produces, here a decimal with room for the product.

3. Create an invoice under an id you choose, then a line on it. The response carries line_total:

   Request

   curl -sS -X PUT http://localhost:8080/api/invoices/4c1d2b3a-6e5f-4a7b-8c9d-0e1f2a3b4c5d \
     -H "X-Alvo-Api-Key: admin.$ALVO_ADMIN_KEY_SECRET" \
     -H "Content-Type: application/json" \
     -d '{"number":"2026-0042","vat_total":11.5}'

   201 Created

   Response

   HTTP/1.1 201 Created
   Content-Type: application/json; charset=utf-8
   Location: /api/invoices/4c1d2b3a-6e5f-4a7b-8c9d-0e1f2a3b4c5d
   
   {
     "id": "4c1d2b3a-6e5f-4a7b-8c9d-0e1f2a3b4c5d",
     "number": "2026-0042"
   }

   Request

   curl -sS -X POST http://localhost:8080/api/invoice_lines \
     -H "X-Alvo-Api-Key: admin.$ALVO_ADMIN_KEY_SECRET" \
     -H "Content-Type: application/json" \
     -d '{"invoice_id":"4c1d2b3a-6e5f-4a7b-8c9d-0e1f2a3b4c5d","description":"On-site support, hours","unit_price":12.5,"quantity":4}'

   201 Created

   Response

   HTTP/1.1 201 Created
   Content-Type: application/json; charset=utf-8
   Location: /api/invoice_lines/1a8c100e-cef7-4ae4-ba72-b76498c382a2
   
   {
     "id": "1a8c100e-cef7-4ae4-ba72-b76498c382a2",
     "description": "On-site support, hours",
     "invoice_id": "4c1d2b3a-6e5f-4a7b-8c9d-0e1f2a3b4c5d",
     "line_total": 50.0,
     "quantity": 4.0,
     "unit_price": 12.5
   }

4. A body that names line_total is refused rather than quietly dropped, so a caller never gets a 201 that reports a different number from the one it sent:

   Request

   curl -sS -X POST http://localhost:8080/api/invoice_lines \
     -H "X-Alvo-Api-Key: admin.$ALVO_ADMIN_KEY_SECRET" \
     -H "Content-Type: application/json" \
     -d '{"invoice_id":"4c1d2b3a-6e5f-4a7b-8c9d-0e1f2a3b4c5d","description":"Travel","unit_price":20,"quantity":1,"line_total":15}'

   403 Forbidden

   Response

   HTTP/1.1 403 Forbidden
   Content-Type: application/problem+json
   
   {
     "type": "https://alvo.dev/errors/forbidden",
     "title": "Forbidden",
     "status": 403,
     "detail": "Field 'line_total' is computed by the database and cannot be written: it is a stored generated column, so the engine itself refuses every write to it. Remove it from the payload — its value follows from the fields the expression reads."
   }

   The refusal is a 403 forbidden, not a 422 validation: the detail names the field to remove.

2. Roll up related rows

A rollup aggregates the rows of another entity that point at this one. from names that entity, op the aggregate and field the child’s field. Alvo recomputes it inside the same transaction as every write to a child row, and only Alvo writes it: a body that names a rollup field is refused with the same 403 forbidden as one naming a computed field, on every write route and in every batch row.

1. Give each invoice the sum of its line totals, the number of its lines, and a gross total computed over the rollup:

   help-desk.alvo.json

   {
     "$schema": "https://alvo.dev/schema/v1/project.json",
     "apiVersion": "alvo.dev/v1",
     "name": "help-desk",
     "auth": {
       "providers": ["local"],
       "roles": ["admin", "agent"]
     },
     "entities": {
       "invoices": {
         "fields": {
           "number": {
             "type": "string", "required": true, "unique": true, "maxLength": 20
           },
           "vat_total": {
             "type": "decimal", "precision": 12, "scale": 2, "required": true,
             "default": 0
           },
           "net_total": {
             "type": "decimal",
             "precision": 18,
             "scale": 2,
             "rollup": {
               "from": "invoice_lines", "op": "sum", "field": "line_total"
             }
           },
           "line_count": {
             "type": "integer",
             "rollup": { "from": "invoice_lines", "op": "count" }
           },
           "gross_total": {
             "type": "decimal", "precision": 18, "scale": 2,
             "computed": "net_total + vat_total"
           }
         },
         "rules": {
           "list": "'authenticated' in @user.roles",
           "get": "'authenticated' in @user.roles",
           "create": "'admin' in @user.roles",
           "update": "'admin' in @user.roles"
         }
       },
       "invoice_lines": {
         "fields": {
           "invoice_id": {
             "type": "ref", "entity": "invoices", "required": true,
             "onDelete": "cascade"
           },
           "description": {
             "type": "string", "required": true, "maxLength": 200
           },
           "unit_price": {
             "type": "decimal", "precision": 10, "scale": 2, "required": true
           },
           "quantity": {
             "type": "decimal", "precision": 8, "scale": 2, "required": true
           },
           "line_total": {
             "type": "decimal", "precision": 18, "scale": 2,
             "computed": "unit_price * quantity"
           }
         },
         "rules": {
           "list": "'authenticated' in @user.roles",
           "get": "'authenticated' in @user.roles",
           "create": "'admin' in @user.roles",
           "update": "'admin' in @user.roles",
           "delete": "'admin' in @user.roles"
         }
       }
     }
   }

   net_total sums line_total, itself a computed field. gross_total is computed again, over net_total: a computed field may read a rollup of its own row.

2. Apply it by recreating the alvo container. Adding fields discards nothing, so the restart applies it (Apply and evolve your descriptor covers the other ways):

   Terminal

   curl -fsSL -o help-desk.alvo.json https://raw.githubusercontent.com/Burgyn/MMLib.Alvo/main/website/src/snippets/computed-and-rollups/02-rollup.alvo.json
   docker compose up -d --wait --force-recreate alvo

3. Create an invoice. Until a line is written for it, every rollup reads null, and so does a computed field over one:

   Request

   curl -sS -X PUT http://localhost:8080/api/invoices/4c1d2b3a-6e5f-4a7b-8c9d-0e1f2a3b4c5d \
     -H "X-Alvo-Api-Key: admin.$ALVO_ADMIN_KEY_SECRET" \
     -H "Content-Type: application/json" \
     -d '{"number":"2026-0042","vat_total":11.5}'

   201 Created

   Response

   HTTP/1.1 201 Created
   Content-Type: application/json; charset=utf-8
   Location: /api/invoices/4c1d2b3a-6e5f-4a7b-8c9d-0e1f2a3b4c5d
   
   {
     "id": "4c1d2b3a-6e5f-4a7b-8c9d-0e1f2a3b4c5d",
     "gross_total": null,
     "line_count": null,
     "net_total": null,
     "number": "2026-0042",
     "vat_total": 11.5
   }

4. Add two lines:

   Request

   curl -sS -X POST http://localhost:8080/api/invoice_lines \
     -H "X-Alvo-Api-Key: admin.$ALVO_ADMIN_KEY_SECRET" \
     -H "Content-Type: application/json" \
     -d '{"invoice_id":"4c1d2b3a-6e5f-4a7b-8c9d-0e1f2a3b4c5d","description":"On-site support, hours","unit_price":12.5,"quantity":4}'

   201 Created

   Response

   HTTP/1.1 201 Created
   Content-Type: application/json; charset=utf-8
   Location: /api/invoice_lines/4daaddc7-9332-4ba6-bd89-db54aa0da26e
   
   {
     "id": "4daaddc7-9332-4ba6-bd89-db54aa0da26e",
     "unit_price": 12.5,
     "quantity": 4,
     "line_total": 50
   }

   Request

   curl -sS -X POST http://localhost:8080/api/invoice_lines \
     -H "X-Alvo-Api-Key: admin.$ALVO_ADMIN_KEY_SECRET" \
     -H "Content-Type: application/json" \
     -d '{"invoice_id":"4c1d2b3a-6e5f-4a7b-8c9d-0e1f2a3b4c5d","description":"Replacement keyboard","unit_price":7.5,"quantity":1}'

   201 Created

   Response

   HTTP/1.1 201 Created
   Content-Type: application/json; charset=utf-8
   Location: /api/invoice_lines/e9b3ecb1-76e6-4586-a261-b1a97e596d56
   
   {
     "id": "e9b3ecb1-76e6-4586-a261-b1a97e596d56",
     "unit_price": 7.5,
     "quantity": 1,
     "line_total": 7.5
   }

5. Read the invoice. Each line’s write recomputed its totals:

   Request

   curl -sS -X GET http://localhost:8080/api/invoices/4c1d2b3a-6e5f-4a7b-8c9d-0e1f2a3b4c5d \
     -H "X-Alvo-Api-Key: admin.$ALVO_ADMIN_KEY_SECRET"

   200 OK

   Response

   HTTP/1.1 200 OK
   Content-Type: application/json; charset=utf-8
   
   {
     "id": "4c1d2b3a-6e5f-4a7b-8c9d-0e1f2a3b4c5d",
     "gross_total": 69.0,
     "line_count": 2,
     "net_total": 57.5,
     "number": "2026-0042",
     "vat_total": 11.5
   }

An update that moves a line to another invoice recomputes both invoices, and deleting a line recomputes its invoice. With no lines left, count answers 0 and the other four aggregates answer null.

Known issue in this build

The recompute does not advance the parent’s ETag, so after a write to a line the invoice’s totals are new but its tag is not: an If-Match taken before still succeeds, and If-None-Match still answers 304. Read the parent again after writing its children before you condition a write on its tag (#351).

How it works

The two look alike in the descriptor and work differently underneath. A computed field is part of the table’s definition, so the database itself refuses every write to it, from any source. A rollup is maintained by Alvo: it locks the parent row, then recomputes the aggregate from scratch, so concurrent writers cannot lose an update. A write that bypasses Alvo, such as a raw INSERT into the child table, leaves a rollup stale. docs/architecture/data-path.md has the details.

Options and variations

Rollup aggregates (op):

op	Answers	Needs field
sum	the sum of the child field	yes
count	the number of child rows	no
avg	the average of the child field	yes
min, max	the smallest or largest value of the child field	yes

When the child entity has more than one ref to this one, name the one to aggregate over with via.

What a computed expression may contain. It is checked when the descriptor is applied, and every refusal says why. Computed fields admit no function call (see the profiles in the CEL function catalog and CEL in Alvo).

Allowed	Refused
Arithmetic + - * / and unary - over numeric fields: unit_price * quantity, net_total + vat_total	A numeric constant: unit_price * 1.2. Keep the rate in a field of its own.
+ joining two strings: first_name + ' ' + last_name, where a text constant is in single quotes	Joining text and a number, first_name + bikes_count: there is no conversion.
An optional field joined only inside the branch its own has() guards: (has(middle_name) ? middle_name : '') + last_name	An optional field joined directly, or a combined guard such as has(a) && has(b) ? … : …
A ternary whose condition compares two fields of the row, or is has(f) or !has(f)	A boolean result: a comparison or has() may only be a ternary’s condition.
A rollup field of the same row	Another computed field, @user, @tenant, now() or any other function, old. or new.

A computed field that joins text must be a string or text field. A maxLength on it must hold the longest possible result; omitting maxLength is always accepted.

Not in this build

A rollup’s where, which would aggregate only some child rows, is refused at apply, because the aggregate would silently include every row (Capabilities in this build). Aggregate every child row, or move the distinction into the model, for example a separate child entity.

What can go wrong

Status	Problem type	When	Fix	Returned by
403	forbidden	The body names a computed or rollup field.	Remove the field from the body; its value follows from the fields its expression reads, or from the child rows.	every host
422	validation	The body breaks a facet of a field the expression reads, such as a missing required unit_price.	Follow the violation’s pointer and fixSuggestion.	every host

An expression Alvo cannot store is refused when the descriptor is applied, and the container does not come back. The end of docker compose logs alvo names the field and the reason, for example a numeric constant; see Run your own descriptor. In this build that refusal ends the process with exit code 139 instead of 78 (#340).

On SQLite, a computed decimal is evaluated as a floating-point number, so 0.1 * 3 reads as 0.30000000000000004 where PostgreSQL answers 0.30 (#162).

Reference

• Descriptor keys: computed, rollup and its from, op, field and via.
• Problem types: forbidden, validation.

Next

Indexes and uniqueness: speed up the queries you run most, and make a combination of fields unique.