Columns and types

You declare columns once, in metastore.yaml, and the engine checks every write against them. Changing a column changes every row that has it, in one undoable operation.

The examples on this page share one fresh vault, which starts with a film table holding Nosferatu and Metropolis.

Add a sparse column

A new column is sparse by default: the table knows it, but only the rows that get a value store it. Use sparse columns for things you record sometimes. Every column needs a description, and label and aliases add other words for it. They tell an agent what the column means, so it reuses the column instead of adding a second one. note links to a note that documents the column.

add_column

{
  "table": "film",
  "column": {
    "name": "minutes",
    "type": "integer",
    "min": 1,
    "label": "Runtime",
    "aliases": ["runtime", "length"],
    "description": "Running time in minutes",
    "note": "[[How I count minutes]]"
  }
}

Answer:

{
  "ok": true,
  "op": "add_column",
  "planned_texts": ["datanotes/metastore.yaml"],
  "commits": [{ "table": "main.public.film", "version": 3, "operation": "ADD COLUMNS" }],
  "migration_id": "20261012T165219-add_column-add-column-minutes-to-film"
}

update_row

{
  "table": "film",
  "path": "datanotes/main/public/film/Metropolis.md",
  "set": { "minutes": 153 }
}

Only Metropolis has a value. The Nosferatu note has no minutes key, so it reads as null:

select_rows

{ "table": "film", "columns": ["title", "minutes"] }

Answer:

{
  "table": "main.public.film",
  "total": 2,
  "rows": [
    {
      "path": "datanotes/main/public/film/Metropolis.md",
      "title": "Metropolis",
      "minutes": 153
    },
    {
      "path": "datanotes/main/public/film/Nosferatu.md",
      "title": "Nosferatu",
      "minutes": null
    }
  ]
}

Add a dense column to every row

dense: true writes the column on every row, existing rows included, with its default.

add_column

{
  "table": "film",
  "column": {
    "name": "favourite",
    "type": "boolean",
    "default": false,
    "description": "True for the films I would watch again and again",
    "dense": true
  }
}

Answer:

{
  "ok": true,
  "op": "add_column",
  "notes_changed": 2,
  "planned_edits": [
    {
      "path": "datanotes/main/public/film/Metropolis.md",
      "set": { "favourite": false },
      "remove": []
    },
    {
      "path": "datanotes/main/public/film/Nosferatu.md",
      "set": { "favourite": false },
      "remove": []
    }
  ],
  "planned_texts": ["datanotes/metastore.yaml"],
  "commits": [{ "table": "main.public.film", "version": 5, "operation": "ADD COLUMNS" }],
  "migration_id": "20261012T165219-add_column-add-column-favourite-to-film"
}

select_rows

{ "table": "film", "columns": ["title", "favourite"] }

Answer:

{
  "table": "main.public.film",
  "total": 2,
  "rows": [
    {
      "path": "datanotes/main/public/film/Metropolis.md",
      "title": "Metropolis",
      "favourite": false
    },
    {
      "path": "datanotes/main/public/film/Nosferatu.md",
      "title": "Nosferatu",
      "favourite": false
    }
  ]
}

Turn an enum into an ordered scale

An enum accepts only its values. With ordered: true, the values form a scale: filters, sorting and averages use each value’s position (1, 2…), while the notes keep the words. Here the last value is seen; the next example renames it.

add_column

{
  "table": "film",
  "column": {
    "name": "status",
    "type": "enum",
    "values": ["to watch", "seen"],
    "ordered": true,
    "description": "Where the film is on my list"
  }
}

Answer:

{
  "ok": true,
  "op": "add_column",
  "planned_texts": ["datanotes/metastore.yaml"],
  "commits": [{ "table": "main.public.film", "version": 6, "operation": "ADD COLUMNS" }],
  "migration_id": "20261012T165219-add_column-add-column-status-to-film"
}

update_row

{
  "table": "film",
  "path": "datanotes/main/public/film/Nosferatu.md",
  "set": { "status": "seen" }
}

update_row

{
  "table": "film",
  "path": "datanotes/main/public/film/Metropolis.md",
  "set": { "status": "to watch" }
}

Sorting follows the scale, furthest along first:

select_rows

{ "table": "film", "columns": ["title", "status"], "order_by": "-status" }

Answer:

{
  "table": "main.public.film",
  "total": 2,
  "rows": [
    {
      "path": "datanotes/main/public/film/Nosferatu.md",
      "title": "Nosferatu",
      "status": "seen"
    },
    {
      "path": "datanotes/main/public/film/Metropolis.md",
      "title": "Metropolis",
      "status": "to watch"
    }
  ]
}

So does a filter. gt: "to watch" means further along than to watch:

select_rows

{
  "table": "film",
  "columns": ["title", "status"],
  "where": { "status": { "gt": "to watch" } }
}

Answer:

{
  "table": "main.public.film",
  "total": 1,
  "rows": [
    {
      "path": "datanotes/main/public/film/Nosferatu.md",
      "title": "Nosferatu",
      "status": "seen"
    }
  ]
}

Rename an enum value

rename_value renames one value in the definition and on every row. The value keeps its position on the scale. Here seen becomes watched.

rename_value

{ "table": "film", "column": "status", "from_value": "seen", "to_value": "watched" }

Answer:

{
  "ok": true,
  "op": "rename_value",
  "notes_changed": 1,
  "planned_edits": [
    {
      "path": "datanotes/main/public/film/Nosferatu.md",
      "set": { "status": "watched" },
      "remove": []
    }
  ],
  "planned_texts": ["datanotes/metastore.yaml"],
  "commits": [{ "table": "main.public.film", "version": 9, "operation": "UPDATE" }],
  "migration_id": "20261012T165219-rename_value-rename-film-status-value-seen-"
}

select_rows

{ "table": "film", "columns": ["title", "status"], "where": { "title": "Nosferatu" } }

Answer:

{
  "table": "main.public.film",
  "total": 1,
  "rows": [
    {
      "path": "datanotes/main/public/film/Nosferatu.md",
      "title": "Nosferatu",
      "status": "watched"
    }
  ]
}

Describe what each enum value means

value_descriptions says when to use each value of an enum. describe_table shows the descriptions, and so does the refusal of a wrong value, so an agent picks the right one.

alter_column

{
  "table": "film",
  "column": "status",
  "changes": {
    "value_descriptions": {
      "to watch": "on my list, not seen yet",
      "watched": "seen to the end"
    }
  }
}

Answer:

{
  "ok": true,
  "op": "alter_column",
  "planned_texts": ["datanotes/metastore.yaml"],
  "commits": [
    { "table": "main.public.film", "version": 10, "operation": "CHANGE COLUMN" }
  ],
  "migration_id": "20261012T165219-alter_column-alter-column-status-in-film"
}

update_row

{
  "table": "film",
  "path": "datanotes/main/public/film/Nosferatu.md",
  "set": { "status": "finished" }
}

Refused:

update_row refused — nothing was written:
invalid datanotes/main/public/film/Nosferatu.md: status must be one of: to watch (on my list, not seen yet); watched (seen to the end)
Hint: Use one of the allowed values listed in the message, written exactly; describe_table shows what each one means. To add a value, alter_column the enum. Then call update_row again (dry_run: true checks without writing).
Docs: https://datanotes.dev/guides/database-objects/columns.md#describe-what-each-enum-value-means (offline: read_docs("guides/database-objects/columns#describe-what-each-enum-value-means"))

Change a column’s attributes

alter_column changes a column’s attributes. If rows would break the new definition, the engine refuses the change, lists those rows and changes nothing.

alter_column

{ "table": "film", "column": "minutes", "changes": { "max": 120 } }

Refused:

alter_column refused — nothing was written:
invalid datanotes/main/public/film/Metropolis.md: minutes must be <= 120
Hint: Give a value inside the column's range (the message names the bound); describe_table shows its min and max. Then call alter_column again (dry_run: true checks without writing).
Docs: https://datanotes.dev/guides/database-objects/tables.md#describe-a-table-before-writing-to-it (offline: read_docs("guides/database-objects/tables#describe-a-table-before-writing-to-it"))

A change that every row satisfies goes through:

alter_column

{
  "table": "film",
  "column": "minutes",
  "changes": { "max": 300, "description": "Running time of the commonly cited version" }
}

Answer:

{
  "ok": true,
  "op": "alter_column",
  "planned_texts": ["datanotes/metastore.yaml"],
  "commits": [
    { "table": "main.public.film", "version": 11, "operation": "CHANGE COLUMN" }
  ],
  "migration_id": "20261012T165219-alter_column-alter-column-minutes-in-film"
}

null removes an attribute. Without max, any runtime is valid again.

alter_column

{ "table": "film", "column": "minutes", "changes": { "max": null } }

Answer:

{
  "ok": true,
  "op": "alter_column",
  "planned_texts": ["datanotes/metastore.yaml"],
  "commits": [
    { "table": "main.public.film", "version": 12, "operation": "CHANGE COLUMN" }
  ],
  "migration_id": "20261012T165219-alter_column-alter-column-minutes-in-film-2"
}

dense: true makes the column dense, so the engine writes it on every row, including Nosferatu, which has no value.

alter_column

{ "table": "film", "column": "minutes", "changes": { "dense": true } }

Answer:

{
  "ok": true,
  "op": "alter_column",
  "notes_changed": 1,
  "planned_edits": [
    {
      "path": "datanotes/main/public/film/Nosferatu.md",
      "set": { "minutes": null },
      "remove": []
    }
  ],
  "planned_texts": ["datanotes/metastore.yaml"],
  "commits": [
    { "table": "main.public.film", "version": 13, "operation": "CHANGE COLUMN" }
  ],
  "migration_id": "20261012T165219-alter_column-alter-column-minutes-in-film-3"
}

datanotes/main/public/film/Nosferatu.md now reads:

---
table: main.public.film
id: 01a13107-86c8-78ea-9f5d-46e8a916d253
title: Nosferatu
year: 1922
favourite: false
status: watched
minutes: null
---

dense: false makes the column sparse again. Rows written from now on store it only when it has a value, and the empty minutes already in Nosferatu stays:

alter_column

{ "table": "film", "column": "minutes", "changes": { "dense": false } }

Answer:

{
  "ok": true,
  "op": "alter_column",
  "planned_texts": ["datanotes/metastore.yaml"],
  "commits": [
    { "table": "main.public.film", "version": 14, "operation": "CHANGE COLUMN" }
  ],
  "migration_id": "20261012T165219-alter_column-alter-column-minutes-in-film-4"
}

datanotes/main/public/film/Nosferatu.md now reads:

---
table: main.public.film
id: 01a13107-86c8-78ea-9f5d-46e8a916d253
title: Nosferatu
year: 1922
favourite: false
status: watched
minutes: null
---

Rename a column

rename_column renames the key in metastore.yaml and on every row.

rename_column

{ "table": "film", "from_column": "favourite", "to_column": "loved_it" }

Answer:

{
  "ok": true,
  "op": "rename_column",
  "notes_changed": 2,
  "planned_edits": [
    {
      "path": "datanotes/main/public/film/Metropolis.md",
      "set": { "loved_it": false },
      "remove": ["favourite"]
    },
    {
      "path": "datanotes/main/public/film/Nosferatu.md",
      "set": { "loved_it": false },
      "remove": ["favourite"]
    }
  ],
  "planned_texts": ["datanotes/metastore.yaml"],
  "commits": [
    { "table": "main.public.film", "version": 15, "operation": "RENAME COLUMN" }
  ],
  "migration_id": "20261012T165219-rename_column-rename-column-favourite-to-lov"
}

select_rows

{ "table": "film", "columns": ["title", "loved_it"] }

Answer:

{
  "table": "main.public.film",
  "total": 2,
  "rows": [
    {
      "path": "datanotes/main/public/film/Metropolis.md",
      "title": "Metropolis",
      "loved_it": false
    },
    {
      "path": "datanotes/main/public/film/Nosferatu.md",
      "title": "Nosferatu",
      "loved_it": false
    }
  ]
}

Drop a column and undo the drop

drop_column removes the column and its values from every row. The journal keeps the values, so undo_migration brings them back.

drop_column

{ "table": "film", "column": "loved_it" }

Answer:

{
  "ok": true,
  "op": "drop_column",
  "notes_changed": 2,
  "planned_edits": [
    {
      "path": "datanotes/main/public/film/Metropolis.md",
      "set": {},
      "remove": ["loved_it"]
    },
    {
      "path": "datanotes/main/public/film/Nosferatu.md",
      "set": {},
      "remove": ["loved_it"]
    }
  ],
  "planned_texts": ["datanotes/metastore.yaml"],
  "commits": [{ "table": "main.public.film", "version": 16, "operation": "DROP COLUMNS" }],
  "migration_id": "20261012T165219-drop_column-drop-column-loved_it-from-film"
}

undo_migration

{ "migration_id": "20261012T165219-drop_column-drop-column-loved_it-from-film" }

Answer:

{
  "ok": true,
  "op": "undo_migration",
  "undoes": "20261012T165219-drop_column-drop-column-loved_it-from-film",
  "migration_id": "20261012T165219-undo_migration-undo-20261012t165219-drop_colu",
  "conflicts": [],
  "failed": []
}

The values are back:

select_rows

{ "table": "film", "columns": ["title", "loved_it"] }

Answer:

{
  "table": "main.public.film",
  "total": 2,
  "rows": [
    {
      "path": "datanotes/main/public/film/Metropolis.md",
      "title": "Metropolis",
      "loved_it": false
    },
    {
      "path": "datanotes/main/public/film/Nosferatu.md",
      "title": "Nosferatu",
      "loved_it": false
    }
  ]
}

Default to today’s date or the current time

As a default, "{today}" gives a date and "{now}" gives a date-time. The engine evaluates them when it writes the row.

add_column

{
  "table": "film",
  "column": {
    "name": "added",
    "type": "date",
    "default": "{today}",
    "description": "The day I put the film on my list"
  }
}

Answer:

{
  "ok": true,
  "op": "add_column",
  "planned_texts": ["datanotes/metastore.yaml"],
  "commits": [{ "table": "main.public.film", "version": 18, "operation": "ADD COLUMNS" }],
  "migration_id": "20261012T165219-add_column-add-column-added-to-film"
}

add_column

{
  "table": "film",
  "column": {
    "name": "added_at",
    "type": "datetime",
    "default": "{now}",
    "description": "When I put the film on my list"
  }
}

insert_row

{ "table": "film", "values": { "title": "Sherlock Jr.", "year": 1924 } }

Answer:

{
  "ok": true,
  "op": "insert_row",
  "files_created": 1,
  "planned_creates": ["datanotes/main/public/film/Sherlock Jr..md"],
  "commits": [{ "table": "main.public.film", "version": 20, "operation": "WRITE" }],
  "migration_id": "20261012T165219-insert_row-insert-into-film-3"
}

datanotes/main/public/film/Sherlock Jr..md now reads:

---
table: main.public.film
id: 01a13107-87e6-7ae8-9e54-569fd9404f1a
loved_it: false
title: Sherlock Jr.
year: 1924
added: 2026-10-12
added_at: 2026-10-12T16:52:19Z
---

Store lists and structured values

A list column holds several values of its items type, and the engine checks each one. A json column stores any YAML Ain’t Markup Language (YAML) value as is, without checks, such as objects or lists of objects. Its name comes from JavaScript Object Notation (JSON).

add_column

{
  "table": "film",
  "column": {
    "name": "seen_at",
    "type": "list",
    "items": "enum",
    "values": ["cinema", "home", "outdoor screening"],
    "description": "Every kind of place I saw the film in"
  }
}

Answer:

{
  "ok": true,
  "op": "add_column",
  "planned_texts": ["datanotes/metastore.yaml"],
  "commits": [{ "table": "main.public.film", "version": 21, "operation": "ADD COLUMNS" }],
  "migration_id": "20261012T165219-add_column-add-column-seen_at-to-film"
}

add_column

{
  "table": "film",
  "column": {
    "name": "viewings",
    "type": "json",
    "description": "Each viewing: when, where, and a note on it"
  }
}

Answer:

{
  "ok": true,
  "op": "add_column",
  "planned_texts": ["datanotes/metastore.yaml"],
  "commits": [{ "table": "main.public.film", "version": 22, "operation": "ADD COLUMNS" }],
  "migration_id": "20261012T165219-add_column-add-column-viewings-to-film"
}

update_row

{
  "table": "film",
  "path": "datanotes/main/public/film/Nosferatu.md",
  "set": {
    "seen_at": ["outdoor screening", "home"],
    "viewings": [
      { "on": "2026-07-18", "where": "outdoor screening", "note": "live piano" },
      {
        "on": "2026-10-02",
        "where": "home",
        "note": "the shadow on the stairs still works"
      }
    ]
  }
}

Answer:

{
  "ok": true,
  "op": "update_row",
  "notes_changed": 1,
  "planned_edits": [
    {
      "path": "datanotes/main/public/film/Nosferatu.md",
      "set": {
        "seen_at": ["outdoor screening", "home"],
        "viewings": [
          { "on": "2026-07-18", "where": "outdoor screening", "note": "live piano" },
          {
            "on": "2026-10-02",
            "where": "home",
            "note": "the shadow on the stairs still works"
          }
        ]
      },
      "remove": []
    }
  ],
  "commits": [{ "table": "main.public.film", "version": 23, "operation": "UPDATE" }],
  "migration_id": "20261012T165219-update_row-update-film-4"
}

datanotes/main/public/film/Nosferatu.md now reads:

---
table: main.public.film
id: 01a13107-86c8-78ea-9f5d-46e8a916d253
title: Nosferatu
year: 1922
loved_it: false
status: watched
minutes: null
seen_at:
  - outdoor screening
  - home
viewings:
  - on: 2026-07-18
    where: outdoor screening
    note: live piano
  - on: 2026-10-02
    where: home
    note: the shadow on the stairs still works
---

One wrong item makes the engine refuse the whole write:

update_row

{
  "table": "film",
  "path": "datanotes/main/public/film/Metropolis.md",
  "set": { "seen_at": ["home", "festival"] }
}

Refused:

update_row refused — nothing was written:
invalid datanotes/main/public/film/Metropolis.md: seen_at[1] must be one of: cinema; home; outdoor screening
Hint: Use one of the allowed values listed in the message, written exactly; describe_table shows what each one means. To add a value, alter_column the enum. Then call update_row again (dry_run: true checks without writing).
Docs: https://datanotes.dev/guides/database-objects/columns.md#describe-what-each-enum-value-means (offline: read_docs("guides/database-objects/columns#describe-what-each-enum-value-means"))

Compute a column from other columns

The engine computes a generated column from other columns on every write, and you can’t set it yourself. The expression takes one of three forms:

A column name: the value of that column
date(col): the date of col, with an optional day shift
A template: text with {col} and {col:FORMAT} placeholders

min(…), max(…) and coalesce(…) pick among several columns.

create_table

{
  "name": "watch",
  "columns": [
    {
      "name": "film",
      "type": "string",
      "required": true,
      "description": "The film I watched"
    },
    {
      "name": "occurred_at",
      "type": "datetime",
      "required": true,
      "description": "When the film started"
    },
    {
      "name": "where",
      "type": "enum",
      "values": ["cinema", "home", "outdoor screening"],
      "description": "The kind of place I watched it in"
    },
    {
      "name": "day",
      "type": "date",
      "generated": "date(occurred_at)",
      "description": "The day of the viewing, for daily notes"
    },
    {
      "name": "label",
      "type": "string",
      "generated": "{film} ({occurred_at:DD/MM HH:mm}, {where})",
      "description": "A one-line summary of the viewing, for lists"
    }
  ],
  "time_column": "occurred_at",
  "description": "Each time I watch a film"
}

Answer:

{
  "ok": true,
  "op": "create_table",
  "planned_texts": ["datanotes/metastore.yaml"],
  "commits": [{ "table": "main.public.watch", "version": 0, "operation": "CREATE TABLE" }],
  "migration_id": "20261012T165219-create_table-create-table-main-public-watch"
}

insert_row

{
  "table": "watch",
  "values": {
    "film": "His Girl Friday",
    "occurred_at": "2026-10-03T21:30",
    "where": "home"
  }
}

Answer:

{
  "ok": true,
  "op": "insert_row",
  "files_created": 1,
  "planned_creates": ["datanotes/main/public/watch/2026-10-03T2130.md"],
  "commits": [{ "table": "main.public.watch", "version": 1, "operation": "WRITE" }],
  "migration_id": "20261012T165219-insert_row-insert-into-watch"
}

datanotes/main/public/watch/2026-10-03T2130.md now reads:

---
table: main.public.watch
id: 01a13107-882d-7a93-a182-8b82b267bc02
film: His Girl Friday
occurred_at: 2026-10-03T21:30
where: home
day: 2026-10-03
label: His Girl Friday (03/10 21:30, home)
---

The engine refuses a value for a generated column:

insert_row

{
  "table": "watch",
  "values": {
    "film": "His Girl Friday",
    "occurred_at": "2026-10-04T21:30",
    "day": "2026-10-01"
  }
}

Refused:

"day" is a generated column: its value is computed
Hint: Leave generated and identity columns out of the values: the engine computes them (recompute_columns computes them again).
Docs: https://datanotes.dev/guides/database-objects/columns.md#compute-a-column-from-other-columns (offline: read_docs("guides/database-objects/columns#compute-a-column-from-other-columns"))

time_column names each row’s file after that date-time: here, the time the viewing started. The name follows the database’s filename format, YYYY-MM-DD[T]HHmm by default.

Let the engine fill identity columns

The engine fills an identity column on its own. filename keeps the note’s file name, and created records when the engine created the row and never changes. Every table also has id, the identity column that holds the row’s universally unique identifier (UUID). The meaning of id and created never changes, so the engine writes their descriptions too.

add_column

{
  "table": "film",
  "column": {
    "name": "note_name",
    "type": "string",
    "identity": "filename",
    "description": "The name of the film's note, kept as the file is renamed"
  }
}

Answer:

{
  "ok": true,
  "op": "add_column",
  "planned_texts": ["datanotes/metastore.yaml"],
  "commits": [{ "table": "main.public.film", "version": 24, "operation": "ADD COLUMNS" }],
  "migration_id": "20261012T165219-add_column-add-column-note_name-to-film"
}

add_column

{
  "table": "film",
  "column": { "name": "created", "type": "datetime", "identity": "created" }
}

Answer:

{
  "ok": true,
  "op": "add_column",
  "planned_texts": ["datanotes/metastore.yaml"],
  "commits": [{ "table": "main.public.film", "version": 25, "operation": "ADD COLUMNS" }],
  "migration_id": "20261012T165219-add_column-add-column-created-to-film"
}

insert_row

{ "table": "film", "values": { "title": "Charade", "year": 1963 } }

Answer:

{
  "ok": true,
  "op": "insert_row",
  "files_created": 1,
  "planned_creates": ["datanotes/main/public/film/Charade.md"],
  "commits": [{ "table": "main.public.film", "version": 26, "operation": "WRITE" }],
  "migration_id": "20261012T165219-insert_row-insert-into-film-4"
}

datanotes/main/public/film/Charade.md now reads:

---
table: main.public.film
id: 01a13107-8858-72d3-9e0f-fa82efb6aac7
loved_it: false
title: Charade
year: 1963
added: 2026-10-12
added_at: 2026-10-12T16:52:19Z
note_name: Charade
created: 2026-10-12T16:52:19Z
---

Partition rows into folders

options.partition puts rows in subfolders named from their values. A {column:FORMAT} placeholder formats a date, so here each actor goes in the folder of their birth year. A table of events could use one folder per year and month, with {occurred_at:YYYY/MM}.

create_table

{
  "name": "actor",
  "description": "Actors of the films I watch",
  "columns": [
    {
      "name": "name",
      "type": "string",
      "required": true,
      "description": "The actor's name, as credited"
    },
    {
      "name": "born",
      "type": "date",
      "required": true,
      "description": "The actor's date of birth"
    }
  ],
  "filename": "{name}",
  "partition": "{born:YYYY}"
}

Answer:

{
  "ok": true,
  "op": "create_table",
  "planned_texts": ["datanotes/metastore.yaml"],
  "commits": [{ "table": "main.public.actor", "version": 0, "operation": "CREATE TABLE" }],
  "migration_id": "20261012T165221-create_table-create-table-main-public-actor"
}

insert_row

{ "table": "actor", "values": { "name": "Cary Grant", "born": "1904-01-18" } }

Answer:

{
  "ok": true,
  "op": "insert_row",
  "files_created": 1,
  "planned_creates": ["datanotes/main/public/actor/1904/Cary Grant.md"],
  "commits": [{ "table": "main.public.actor", "version": 1, "operation": "WRITE" }],
  "migration_id": "20261012T165221-insert_row-insert-into-actor"
}

insert_row

{ "table": "actor", "values": { "name": "Audrey Hepburn", "born": "1929-05-04" } }

list_folder

{ "folder": "datanotes/main/public/actor" }

Answer:

{
  "folder": "datanotes/main/public/actor",
  "folders": ["datanotes/main/public/actor/1904", "datanotes/main/public/actor/1929"],
  "files": []
}

See the column definitions in `metastore.yaml`

The engine wrote this file with the columns from this page, in the operations’ words. Two things have a place of their own:

time_column: the table’s filename pattern under options
partition: under options, next to filename

datanotes/metastore.yaml now reads:

# Metastore (Datanotes): catalogs, schemas, tables, columns, views.
id: 01a13107-86b6-7f78-987f-14efb8d3e8d0
config:
  history: true

catalogs:
  main:
    schemas:
      public:
        tables:
          film:
            description: Classic films I watched or want to watch
            options:
              filename: "{title}"
              archive: _archive
            columns:
              - name: id
                type: string
                required: true
                description: "Unique id of the row (UUIDv7): set when the row is created, never changes"
                unique: true
                identity: uuid
                dense: true
              - name: title
                type: string
                required: true
                description: The film's title, as on its poster
              - name: year
                type: integer
                description: Year the film first came out
              - name: minutes
                type: integer
                min: 1
                label: Runtime
                description: Running time of the commonly cited version
                aliases:
                  - runtime
                  - length
                note: "[[How I count minutes]]"
              - name: loved_it
                type: boolean
                default: false
                description: True for the films I would watch again and again
                dense: true
              - name: status
                type: enum
                values:
                  - to watch
                  - watched
                value_descriptions:
                  to watch: on my list, not seen yet
                  watched: seen to the end
                description: Where the film is on my list
                ordered: true
              - name: added
                type: date
                default: "{today}"
                description: The day I put the film on my list
              - name: added_at
                type: datetime
                default: "{now}"
                description: When I put the film on my list
              - name: seen_at
                type: list
                items: enum
                values:
                  - cinema
                  - home
                  - outdoor screening
                description: Every kind of place I saw the film in
              - name: viewings
                type: json
                description: "Each viewing: when, where, and a note on it"
              - name: note_name
                type: string
                description: The name of the film's note, kept as the file is renamed
                identity: filename
              - name: created
                type: datetime
                description: "When the row was created: set by the engine, never changes"
                identity: created
          watch:
            description: Each time I watch a film
            options:
              filename: "{occurred_at:YYYY-MM-DD[T]HHmm}"
              archive: _archive
            columns:
              - name: id
                type: string
                required: true
                description: "Unique id of the row (UUIDv7): set when the row is created, never changes"
                unique: true
                identity: uuid
                dense: true
              - name: film
                type: string
                required: true
                description: The film I watched
              - name: occurred_at
                type: datetime
                required: true
                description: When the film started
              - name: where
                type: enum
                values:
                  - cinema
                  - home
                  - outdoor screening
                description: The kind of place I watched it in
              - name: day
                type: date
                description: The day of the viewing, for daily notes
                generated: date(occurred_at)
              - name: label
                type: string
                description: A one-line summary of the viewing, for lists
                generated: "{film} ({occurred_at:DD/MM HH:mm}, {where})"
          actor:
            description: Actors of the films I watch
            options:
              partition: "{born:YYYY}"
              filename: "{name}"
              archive: _archive
            columns:
              - name: id
                type: string
                required: true
                description: "Unique id of the row (UUIDv7): set when the row is created, never changes"
                unique: true
                identity: uuid
                dense: true
              - name: name
                type: string
                required: true
                description: The actor's name, as credited
              - name: born
                type: date
                required: true
                description: The actor's date of birth

Columns and types

Add a sparse column

Add a dense column to every row

Turn an enum into an ordered scale

Rename an enum value

Describe what each enum value means

Change a column’s attributes

Rename a column

Drop a column and undo the drop

Default to today’s date or the current time

Store lists and structured values

Compute a column from other columns

Let the engine fill identity columns

Partition rows into folders

See the column definitions in metastore.yaml

See the column definitions in `metastore.yaml`