Tables and indexes

An app doesn’t own tables. It uses the tables of its space, which people see and edit in Matter too, and which agents work on with Matter’s own tools. Moving an app to another space leaves its tables behind.

Declare them in schema.json

{
  "tables": [
    {
      "name": "Deals",
      "fields": [
        { "name": "Name", "type": "title" },
        { "name": "Stage", "type": "select", "options": ["Lead", "Won", "Lost"] },
        { "name": "Amount", "type": "currency", "currency": "EUR" },
        { "name": "Email", "type": "email" },
        { "name": "Owner", "type": "person" },
        { "name": "Company", "type": "relation", "table": "Companies" }
      ],
      "indexes": [
        { "name": "by_stage", "match": ["Stage"], "order": [{ "field": "Amount", "direction": "desc" }] },
        { "name": "by_owner_stage", "match": ["Owner", "Stage"] },
        { "name": "by_email", "match": ["Email"], "unique": true }
      ]
    }
  ]
}

Apply it with matter table apply schema.json. It creates whatever is missing (tables first, then fields and options, then indexes) and changes nothing else. Run it again whenever the schema grows. It never removes fields or rows. Add --prune to delete the indexes of these tables that the schema no longer names.

Field types

title, text, number, currency, checkbox, date, select, multi_select, url, email, phone, person, relation, attachment and json.

title renames the table’s name field.
select and multi_select take options. Applying adds any that are missing.
relation takes table, the name of a table in the same space.
currency takes currency, a currency code. The default is USD.
person and relation fields hold one value unless you add "allow_multiple": true.

How reads use indexes

Each read goes through one index:

match lists the fields a read gives exact values for.
order sets the order the rows come back in. Its first field is the one a range bounds.

A read gives values for all of an index’s match fields, and a list means “any of these”. The results of a list merge in the index’s order. So one index, match: [Status], order: [Score desc], serves all of these:

“new prospects, best first”: eq: { Status: 'new' };
“all prospects, best first”: every status, plus null for empty;
counts per status, with groupCount.

Prefer a few broad indexes like that over many narrow ones.

The rules

An index covers one to three fields, match and order together.
An index can match at most one field that holds several values (a multi-select, or a person or relation field that allows several).
A unique index has match fields only, none holding several values. It refuses a second row with the same values. Empty values don’t count. Text matches ignoring case, emails always ignore case, and links match in a canonical form.
Attachment and JSON fields can’t be indexed.
A table can have at most 100 indexes. Indexes that people and agents made count too.

People can make indexes in Matter as well, and a field’s Unique switch makes a unique index. table apply reuses an index that already has the name you give.

Small and large tables

Small tables, under 10,000 rows, are on every member’s computer, and reads of them are local.

Tables over 10,000 rows are read through the server, using the same indexes, so the same code works for both. Their reads are live too: the server says when a write touches what a read read. On a large table:

reads go through an index: eq, range and the order come from the index;
paging is by cursor only, with after, before and until;
filter checks the rows the index finds; a page whose filter passed over many entries comes back short, with a nextCursor;
without an index, a read pages through the table in its own order, and takes no sort or filter;
a sort, or a filter without an index, is refused: either would read every row.

A table that might grow past 10,000 rows should have an index for every read your app makes. Reads of a small table may scan it in a sort order without an index, but they’re refused once it grows.

See Reading for the read options.