# Columns and types

> Choose column types and attributes, compute generated columns, name rows after a time, and change the columns of a table that already has rows.

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`**

```json
{
  "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:

```json
{
  "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`**

```json
{
  "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`**

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

Answer:

```json
{
  "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`**

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

Answer:

```json
{
  "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`**

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

Answer:

```json
{
  "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`**

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

Answer:

```json
{
  "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`**

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

**`update_row`**

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

Sorting follows the scale, furthest along first:

**`select_rows`**

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

Answer:

```json
{
  "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`**

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

Answer:

```json
{
  "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`**

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

Answer:

```json
{
  "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`**

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

Answer:

```json
{
  "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`**

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

Answer:

```json
{
  "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`**

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

Refused:

```text
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`**

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

Refused:

```text
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`**

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

Answer:

```json
{
  "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`**

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

Answer:

```json
{
  "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`**

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

Answer:

```json
{
  "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:

```markdown
---
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`**

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

Answer:

```json
{
  "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:

```markdown
---
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`**

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

Answer:

```json
{
  "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`**

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

Answer:

```json
{
  "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`**

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

Answer:

```json
{
  "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`**

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

Answer:

```json
{
  "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`**

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

Answer:

```json
{
  "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`**

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

Answer:

```json
{
  "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`**

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

**`insert_row`**

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

Answer:

```json
{
  "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:

```markdown
---
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`**

```json
{
  "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:

```json
{
  "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`**

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

Answer:

```json
{
  "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`**

```json
{
  "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:

```json
{
  "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:

```markdown
---
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`**

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

Refused:

```text
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`**

```json
{
  "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:

```json
{
  "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`**

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

Answer:

```json
{
  "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:

```markdown
---
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`**

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

Refused:

```text
"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`**

```json
{
  "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:

```json
{
  "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`**

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

Answer:

```json
{
  "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`**

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

Answer:

```json
{
  "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:

```markdown
---
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`**

```json
{
  "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:

```json
{
  "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`**

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

Answer:

```json
{
  "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`**

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

**`list_folder`**

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

Answer:

```json
{
  "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:

```yaml
# 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
```