Indexes and uniqueness

Declare composite indexes for the queries you run most, make a combination of fields unique, and know which indexes Alvo already creates.

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 agent key (roles agent, authenticated).
Entities and fields, for fields and unique.

Indexes Alvo creates for you

Declare an index only beyond these, which every entity already has:

the primary key id;
every field with "unique": true;
every ref to a declared entity.

A ref to the built-in users entity is not indexed by itself.

1. Declare the indexes

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

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

The descriptor declares three indexes:

{
  "$schema": "https://alvo.dev/schema/v1/project.json",
  "apiVersion": "alvo.dev/v1",
  "name": "help-desk",
  "auth": {
    "providers": ["local"],
    "roles": ["admin", "agent"]
  },
  "entities": {
    "tickets": {
      "fields": {
        "queue": {
          "type": "enum", "values": ["billing", "hardware", "software"],
          "required": true
        },
        "number": { "type": "integer", "required": true },
        "title": { "type": "string", "required": true, "maxLength": 120 },
        "status": {
          "type": "enum", "values": ["open", "closed"], "default": "open"
        },
        "assignee_id": { "type": "ref", "entity": "users", "index": true }
      },
      "indexes": [
        { "fields": ["queue", "status"] },
        { "fields": ["queue", "number"], "unique": true }
      ],
      "rules": {
        "list": "'authenticated' in @user.roles",
        "get": "'authenticated' in @user.roles",
        "create": "'agent' in @user.roles || 'admin' in @user.roles",
        "update": "'agent' in @user.roles || 'admin' in @user.roles"
      }
    }
  }
}

"index": true on assignee_id is the one-field form. Use it for a single field, here a ref to users, which gets no index otherwise.
{"fields": ["queue", "status"]} is a composite index. List the fields in query order: callers filter on queue first, then on status.
{"fields": ["queue", "number"], "unique": true} makes the combination unique: one ticket per number within each queue.

A plain index never refuses a write; it only makes reads faster. When the schema assistant (or any JSON Patch) adds the first index, it must add the whole indexes list: an add at /indexes/- on a list that does not exist yet is refused.

2. Number tickets within each queue

Ticket 1 in billing, then ticket 1 in hardware. The same number in two queues is allowed:

curl -sS -X POST http://localhost:8080/api/tickets \
  -H "X-Alvo-Api-Key: agent.$ALVO_AGENT_KEY_SECRET" \
  -H "Content-Type: application/json" \
  -d '{"queue":"billing","number":1,"title":"Invoice sent twice"}'

201 Created

HTTP/1.1 201 Created
Content-Type: application/json; charset=utf-8
Location: /api/tickets/66f74c91-8e33-4460-b971-5d6142011be6

{
  "id": "66f74c91-8e33-4460-b971-5d6142011be6",
  "queue": "billing",
  "number": 1,
  "title": "Invoice sent twice"
}

curl -sS -X POST http://localhost:8080/api/tickets \
  -H "X-Alvo-Api-Key: agent.$ALVO_AGENT_KEY_SECRET" \
  -H "Content-Type: application/json" \
  -d '{"queue":"hardware","number":1,"title":"Laptop will not charge"}'

201 Created

HTTP/1.1 201 Created
Content-Type: application/json; charset=utf-8
Location: /api/tickets/fb953b71-a719-4363-8596-5ac4cfb7eab0

{
  "id": "fb953b71-a719-4363-8596-5ac4cfb7eab0",
  "queue": "hardware",
  "number": 1,
  "title": "Laptop will not charge"
}

A second ticket 1 in billing collides with the first:

curl -sS -X POST http://localhost:8080/api/tickets \
  -H "X-Alvo-Api-Key: agent.$ALVO_AGENT_KEY_SECRET" \
  -H "Content-Type: application/json" \
  -d '{"queue":"billing","number":1,"title":"Refund for March"}'

409 Conflict

HTTP/1.1 409 Conflict
Content-Type: application/problem+json

{
  "type": "https://alvo.dev/errors/conflict",
  "title": "Conflict",
  "status": 409,
  "detail": "This field is declared unique and another record already holds the value sent for it.",
  "violations": [
    {
      "pointer": "/queue",
      "code": "unique",
      "message": "This field is declared unique and another record already holds the value sent for it.",
      "fixSuggestion": "Send a value no other record holds, or change the record that holds it."
    },
    {
      "pointer": "/number",
      "code": "unique",
      "message": "This field is declared unique and another record already holds the value sent for it.",
      "fixSuggestion": "Send a value no other record holds, or change the record that holds it."
    }
  ]
}

Each field of the combination gets a violation with the code unique. The message is the one a single unique field gets; the pointers name the combination.

How it works

A declared index becomes a database index when the descriptor is applied, and a unique one makes the database refuse a duplicate, which Alvo answers as 409 conflict. On a tenant-scoped entity, Alvo puts tenant_id first in every unique index, both a field’s unique and a unique combination, so two tenants may hold the same value and neither learns that the other does. Multi-tenancy covers tenant-scoped entities.

Options and variations

You want	Declare
One field unique across the entity	the field’s `unique`
A combination unique, such as one number per queue	an entry in `indexes` with `unique`
A faster filter on one field	the field’s `index`
A faster filter on several fields together	an entry in `indexes` with its `fields` in query order

A field’s unique and a unique combination are different rules: unique on both queue and number would allow one ticket per queue in total.

What can go wrong

Status	Problem type	When	Fix	Returned by
409	`conflict`	A write would give two rows the same value of a unique field, or the same combination of a unique index (code `unique`).	Send a value, or a combination, that no other row holds.	every host

A new unique index cannot be created while duplicate rows exist. Before you add one to an entity that already holds data, remove or change the rows that share a value.

Reference

Descriptor keys: indexes, index, unique.
Problem types: conflict.

Apply and evolve your descriptor: change a running backend safely, with dry runs, revisions and rollback.