Column types

A table declares columns. Each column has a type. The type decides three things at once: how the value is stored, which control a form renders for it, and which checks run before a write is accepted.

You declare a column with a name, a type, and a label:

table members "Members" {
  display: name

  name       text required        "Name"
  email      email                "Email address"
  joined_on  date required        "Joined on"
  fee_cents  money                "Annual fee"
}

The label is the last item on the line. Every declaration in the language follows this rule.

Text

text

A single line of text, up to 255 characters. The form renders a one-line input.

For longer text, write long text as two words. It stores the same way and renders a textarea. A long text column is truncated in a report, because a table cell is too small for a paragraph.

title  text required  "Title"
notes  long text      "Notes"

markdown

Formatted text that an author writes in Markdown. The engine parses it with raw HTML disabled and sanitizes the result a second time. It also keeps a plain-text copy for search.

A markdown column is not a long text column with formatting. It is a contract about what the engine accepts and what it renders.

Editing Markdown in a form

A form shows a markdown field as a visual editor. You do not declare anything to get it. The text looks as it will look on the page, and a toolbar above it applies formatting:

Button	Keyboard (Ctrl on Windows and Linux, Cmd on a Mac)	Markdown it writes
Heading, Subheading	Ctrl+Alt+1, Ctrl+Alt+2	# , ##
Bold, Italic, Code	Ctrl+B, Ctrl+I, Ctrl+E	**bold**, *italic*, `code`
Link	Ctrl+K	[text](address)
Bulleted list, Numbered list	Ctrl+Shift+8, Ctrl+Shift+7	- , 1.
Quote	Ctrl+Shift+9	>
Code block, Divider	—	a fenced block, ---
Undo, Redo	Ctrl+Z, Ctrl+Y (Cmd+Shift+Z on a Mac)	—

Markdown typed into the editor also turns into formatting: ## at the start of a line makes a subheading, - a list, and **word** a bold word. Plain text that is pasted is read as Markdown. Pasted formatted text keeps only the formatting in the table above.

Ctrl+] and Ctrl+[ indent and outdent a list item. The Tab key is not used by the editor, so it always moves to the next field.

The Markdown button on the right of the toolbar shows the text as Markdown source. Press it again to go back. The form saves the Markdown in both views.

Some things to know:

• The editor stores Markdown. Nothing else is saved, and the rules in this section apply to text from the editor as they do to typed Markdown.
• An untouched field is saved exactly as it was stored. The editor writes the field only after you change it. Saving a form in which you changed only the title does not rewrite the text.
• A table is edited as Markdown source. It shows under the label “Table – edited as Markdown” inside the visual editor.
• Some texts open in the Markdown view. A text with a table inside a list or a quote cannot be shown by the visual editor without changing it. It opens as Markdown, with a note that says why.
• Formatting next to punctuation can move. Markdown cannot mark some positions as bold or italic, for example italic “(note)” directly after a letter. The editor then moves the formatting inward, to “(” + note + “)”, as you apply it. What you see is what is saved.
• Without JavaScript the field is a text box that holds the Markdown source, and the form saves it in the same way.

Images in Markdown

A Markdown text can show a picture that is stored in an img column of the same application. Write a Markdown image whose address is the picture’s address:

![The site plan](/matterdata/blob/media/file/2/<digest>)

The four parts after blob/ are the table, the img column, the row id, and the picture’s digest. Add .thumb to the digest for the 320-pixel thumbnail. Copy the address from a page that shows the picture.

In an application under a mount, a page shows the address with the mount in front, for example /club/matterdata/blob/media/file/2/<digest>. You can write either form. The engine stores the text as you wrote it and shows the picture under the application’s mount. The visual editor in a form shows the picture from the same address. An address under the mount of a different application is refused.

When a form or a sheet saves the text, the engine checks each image that the text did not already contain. The person who saves must be able to open the picture: a page of the application must show them that row and that column. If the check fails, the save is refused and the markdown field shows this message, with the address that was written:

The text shows an image that is not available: /matterdata/blob/media/file/99/…. Use the address of a picture in this application, or remove the image.

The message is the same for a row that does not exist, a row that does not hold that picture, and a row the person may not see. The save does not tell the person which rows exist.

An address outside the application, such as https://…, is also refused. The engine never shows an image from another site.

The check applies to the person who saves, not to every reader. A reader who may not see the picture’s row sees the word “Image” and the alt text instead of the picture.

email

An email address. The form renders an email input and checks the address format. A report shows the value as a mailto: link.

Numbers

int

A whole number. Add min: and max: to bound it.

seats int required "Seats" {
  min: 1
  max: 500
}

money

An amount, stored as whole minor units. A price of 12.50 euros is stored as 1250.

There is no floating-point number anywhere in the data model. min: and max: on a money column are in minor units too, so min: 500 is five euros.

The form accepts the local spelling, such as 12,50. A report shows the formatted amount.

Dates and times

date

A calendar date, stored as YYYY-MM-DD. A date has no time and no time zone.

datetime

One instant, stored in UTC with a Z suffix. The engine normalizes the value on write.

Storage is always UTC. The time zone is a question about display and about input, and never about storage. Text order is therefore chronological order, so a report can sort on the column directly.

The form renders a datetime-local control, which carries no offset. The engine reads that value in the time zone of the page, and shows a stored instant in the same time zone. A submitted value that does carry an offset is converted from that offset, and consults no time zone. See Set the time zone.

Choices

bool

True or false, stored as 0 or 1. The form renders a checkbox.

enum

A closed set of values that you name in the declaration. Each member has a value and a label.

status enum(draft "Draft", active "Active", closed "Closed") required "Status"

The form renders a select. A report shows the label and never the stored value. The database rejects any value that is not a member.

The select also carries an empty option. To name it, see empty_label.

Relations

ref

A reference to one row of another table.

fee_group ref(fee_groups) required "Fee group"

The form renders a select over the target table’s display columns. A report shows the target’s display value.

The select also carries an empty option. To name it, see empty_label.

The database column is named <name>_id. In .mtd you always write the logical name.

multi

A reference to many rows of another table. The engine owns a junction table for it, and you never name that table.

tags multi(tags) "Tags"

The form renders a checkbox list. A multi is a set of rows and not a value, so it cannot be a column in a report and cannot be a cell in a sheet. To ask a question about it, use count(...) or exists(...).

list

The other side of a reference that already exists.

posts list(posts.author) "Posts"

A list is not stored and never appears in a form. Like a multi, it is a set of rows. A detail region can show one, because a record layout has room for a list of links.

Derived values

A derived column is computed per query. It is read-only, it never appears in a form, and it has no column in the database. Because the engine compiles it to SQL, a report can sort and filter on it like any stored column.

count

How many rows of another table point at this row.

signups count(event_signups.event) "Signups"

Add a where: to count a subset. “How many are unexcused” is then a column, and not a second region.

sum, min and max

An aggregate over a related table’s column.

total sum(order_lines.order.amount) "Total" {
  empty: 0
}

empty: is required on every aggregate. It states the value for a row with no related rows, which is the case that otherwise returns nothing and renders an empty cell.

Arithmetic is integer only. Division is not an operator: use ratio(...) inside exactly one round(...).

Values the engine owns

The engine writes these columns. A form never accepts one from a request, even if the request names it.

slug

A readable address derived from other columns.

slug slug(title) "Address"

The engine derives the value, normalizes it, and reserves it. A slug is unique against live values and against retired ones, so an address never points at a different row later.

A slug does not change when its source changes. A URL that changes by itself is a broken link that somebody else is holding.

unguessable

A random token for an address that is hard to guess, such as a private share link.

share_token unguessable "Share link"

The engine generates the value at insert. The alphabet has 32 symbols and drops the four letters that are misread on paper. A miss on a route bound by an unguessable is rate limited per tenant and per client.

img

A picture. The engine stores the file beside the tenant database and keeps the facts about it in seven columns.

photo img "Photo" {
  max_mb:  5
  max_px:  2048
  accept:  jpeg, png, webp
}

An upload is re-encoded from its decoded pixels. The engine applies the EXIF orientation and drops everything else. A file whose header declares a size that is too large is refused before it is decoded.

CAUTION: A backup of a tenant is two things. Copy the database file and the blob directory together. An organization that resets itself every night has a third: its stored template, which is a database file and a blob directory of its own.

Modifiers and properties

A modifier is an adjective and takes no value. A property takes a value and is written inside braces.

Modifier	Effect
required	The column must have a value.
unique	No two rows can hold the same value.
readonly	A form shows the value and renders no control.
sensitive	Only a role whose rule grants the column can read or write it.
optimized	An img is re-encoded to JPEG at quality 82.
derived	The table’s before insert or before update hook writes the column. See Mark a column that the hook writes.
versioned	The table keeps a version of the column at every save that changes it. See versioned.

Property	Effect
default:	The value for a new row. Whole numbers only.
min: / max:	Bounds for a number, or a length for text.
help:	One sentence shown with the control.
empty:	Required on an aggregate. The value when there are no related rows.
empty_label:	The text of the empty option in a select. enum and ref only.

empty_label

Every select carries an empty option. In an English app it reads Please choose. Use empty_label: to say what the empty value means.

alliance ref(alliances) "Alliance" { empty_label: "Whole kingdom" }

The property changes one string. It adds no enum member, and it stores no value. An empty value stays empty.

empty_label: applies to enum and ref. These are the two types that render a select. On any other type the compiler refuses it and names the type you wrote.

The column must be optional. On a required column the compiler refuses empty_label:, because the empty option can never be saved. If the empty value has a meaning, drop required from the column.

A form can name the empty option differently for one page:

form of events {
  fields: title, alliance

  field alliance { empty_label: "All alliances" }
}

The form’s text wins on that page. The column’s text holds on every other page.

The text renders in a form select only. A report cell, a detail cell and an export are unchanged. An export keeps the empty cell, because a spreadsheet reads an empty cell as no value.

History

versioned

A versioned column keeps a version at every save that changes it. A version holds the values of all of the table’s versioned columns after the save, the time of the save, and who saved it.

table posts "Posts" {
  title text required versioned "Title"
  body  markdown required versioned "Body" { max_kb: 256 }
}

The record itself is updated as before. Reports, filters, sorting, exports, rules, and computed columns read the record and do not change. The newest version always holds the same values as the record.

• A new record gets its first version.
• A save that changes a versioned column adds a version. A save that changes none adds nothing.
• Saves by one person within 10 minutes are one version. The later save replaces the newest version. A save by a visitor without a sign-in never does this, and neither does a save that finds the record in a different lifecycle state from the newest version.
• A record written outside the engine gets a version at its next save.
• Deleting a record deletes its history.

Show the history with a versions region.

The compiler refuses versioned on these columns, and the message names the repair:

Refused	Reason
an aggregate, list, multi, img, slug, or unguessable	a save does not write one value into the record
a column with value:, derived, or sensitive	a computed column has no saved value, a hook rewrites a derived one, and a sensitive one’s history would need its own grant
the lifecycle column, or the scope’s column	the engine writes them, and every version records the state already
a column named row_id, saved_at, saved_by, state, or present	the history table uses these names
a column that a retention … on rule clears	the history would keep what the retention erases

The versions statement

versions keep says how much history each record keeps. It is optional and appears once in a table block. Without it, a table with a versioned column keeps the 20 newest versions of each record.

table posts "Posts" {
  title text required versioned "Title"
  versions keep 20 within 1y
}

Statement	Keeps
versions keep 20	the 20 newest versions of a record, from 1 to 200
versions keep 90d	the versions of the last 90 days, and at most 200
versions keep 20 within 1y	at most 20, and none older than a year

A version is removed when it passes either limit. The newest version is never removed, because it is the current value. The count is applied when a record is saved. The age is applied by the nightly retention run and uses the windows of retention: d, m, and y.

The history is stored in a table the engine owns, <table>_versions, in the organization’s file. Its size shows on the usage page at /matterdata/usage as Version history, apart from the picture quota.

drop versions of

A change that removes history needs an acknowledgement at the deploy:

Change	Acknowledgement
stop versioning one column, and keep the column	drop versions of body !destructive "Reason" in the table
drop a versioned column	drop body !destructive "Reason" covers the column and its history
keep less: a smaller count, or a new or shorter age	retention posts_versions !destructive "Reason" in the app block

Versioning a column of a table that already has records writes one version per record at the deploy, with its current values. Versioning a second column adds it to later versions; older versions do not hold it, and restoring one of them keeps that column’s current value.