# Relations and hierarchies

> Link rows to rows of other tables, keep two-way links in step, take allowed values from a table, build trees and limit link targets.

A link column holds a wikilink to a note, such as `[[Charade]]`, and `ref` names the table that note must be a row of. The link points to a row of the other table, and you reach that row’s columns through the link.

The examples on this page share one diary of classic films, with actors, films, viewings, tags and genres.

## Point a column to a row of another table

A viewing is of one film, so its `film` column is a `link` whose `ref` is the film table. The value must link to a note that is a row of `film`.

**`create_table`**

```json
{
  "name": "watch",
  "description": "Each time I watch a film",
  "columns": [
    {
      "name": "film",
      "type": "link",
      "ref": "main.public.film",
      "required": true,
      "description": "The film I watched"
    },
    {
      "name": "where",
      "type": "enum",
      "values": ["cinema", "home", "outdoor screening"],
      "required": true,
      "description": "The kind of place I watched it in"
    },
    {
      "name": "place",
      "type": "string",
      "description": "The cinema, park or room, in my words"
    },
    { "name": "with", "type": "string", "description": "Who watched it with me" },
    {
      "name": "occurred_at",
      "type": "datetime",
      "required": true,
      "description": "When the film started"
    }
  ],
  "time_column": "occurred_at"
}
```

Answer:

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

**`insert_row`**

```json
{
  "table": "watch",
  "values": {
    "film": "[[His Girl Friday]]",
    "where": "cinema",
    "place": "the film club downtown",
    "with": "my sister",
    "occurred_at": "2026-03-14T20:30"
  },
  "body": "A full hall, and everyone laughed at the same lines.\n"
}
```

Answer:

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

`datanotes/main/public/watch/2026-03-14T2030.md` now reads:

```markdown
---
table: main.public.watch
id: 01a13107-8eb8-7211-b7b8-e2dd6a7d89ac
film: "[[His Girl Friday]]"
where: cinema
place: the film club downtown
with: my sister
occurred_at: 2026-03-14T20:30
---
A full hall, and everyone laughed at the same lines.
```

**`insert_row`**

```json
{
  "table": "watch",
  "values": {
    "film": "[[Charade]]",
    "where": "home",
    "place": "my living room",
    "occurred_at": "2026-09-26T21:30"
  }
}
```

The engine refuses a link to a note that doesn’t exist (`broken_link`):

**`insert_row`**

```json
{
  "table": "watch",
  "values": { "film": "[[Charde]]", "where": "home", "occurred_at": "2026-09-27T21:00" }
}
```

Refused:

```text
insert_row refused — nothing was written:
invalid datanotes/main/public/watch/2026-09-27T2100.md: film link does not resolve: Charde
Hint: Link to a note that exists, as "[[Note name]]": list_link_targets(table, column) lists the rows the column may point to. Then call insert_row again (dry_run: true checks without writing).
Docs: https://datanotes.dev/guides/database-objects/relations.md#point-a-column-to-a-row-of-another-table (offline: read_docs("guides/database-objects/relations#point-a-column-to-a-row-of-another-table"))
```

It also refuses a link to a note of another table (`ref`), here an actor:

**`insert_row`**

```json
{
  "table": "watch",
  "values": {
    "film": "[[Cary Grant]]",
    "where": "home",
    "occurred_at": "2026-09-27T21:00"
  }
}
```

Refused:

```text
insert_row refused — nothing was written:
invalid datanotes/main/public/watch/2026-09-27T2100.md: film link must point to a row of table "main.public.film"
Hint: Link to a row of the table the column points to (its ref) that its ref_where / ref_by allow: list_link_targets(table, column) lists them. Then call insert_row again (dry_run: true checks without writing).
Docs: https://datanotes.dev/guides/database-objects/relations.md#limit-link-targets-with-ref_by-and-ref_where (offline: read_docs("guides/database-objects/relations#limit-link-targets-with-ref_by-and-ref_where"))
```

## Link to several rows with a list of links

A film has several actors, so `cast` is a `list` of `link` items, and the engine checks each one against `ref`. `inverse: films` names the actor column that links back, and from then on the engine keeps both sides in step. Both columns must exist, and you declare `inverse` on one side only.

**`add_column`**

```json
{
  "table": "actor",
  "column": {
    "name": "films",
    "type": "list",
    "items": "link",
    "ref": "main.public.film",
    "description": "The films the actor plays in"
  }
}
```

Answer:

```json
{
  "ok": true,
  "op": "add_column",
  "planned_texts": ["datanotes/metastore.yaml"],
  "commits": [{ "table": "main.public.actor", "version": 4, "operation": "ADD COLUMNS" }],
  "migration_id": "20261012T165221-add_column-add-column-films-to-actor"
}
```

**`add_column`**

```json
{
  "table": "film",
  "column": {
    "name": "cast",
    "type": "list",
    "items": "link",
    "ref": "main.public.actor",
    "inverse": "films",
    "description": "The actors in the film"
  }
}
```

Answer:

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

**`update_row`**

```json
{
  "table": "film",
  "path": "datanotes/main/public/film/Charade.md",
  "set": { "cast": ["[[Cary Grant]]", "[[Audrey Hepburn]]"] }
}
```

Answer:

```json
{
  "ok": true,
  "op": "update_row",
  "notes_changed": 3,
  "planned_edits": [
    {
      "path": "datanotes/main/public/film/Charade.md",
      "set": { "cast": ["[[Cary Grant]]", "[[Audrey Hepburn]]"] },
      "remove": []
    },
    {
      "path": "datanotes/main/public/actor/Cary Grant.md",
      "set": { "films": ["[[Charade]]"] },
      "remove": []
    },
    {
      "path": "datanotes/main/public/actor/Audrey Hepburn.md",
      "set": { "films": ["[[Charade]]"] },
      "remove": []
    }
  ],
  "commits": [
    { "table": "main.public.actor", "version": 5, "operation": "UPDATE" },
    { "table": "main.public.film", "version": 5, "operation": "UPDATE" }
  ],
  "migration_id": "20261012T165221-update_row-update-film"
}
```

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

```markdown
---
table: main.public.film
id: 01a13107-8e9d-794f-86d9-01c37a42eeef
title: Charade
year: 1963
minutes: 113
cast:
  - "[[Cary Grant]]"
  - "[[Audrey Hepburn]]"
---
```

The same write added the film to both actors, in one operation with one undo:

**`read_note`**

```json
{ "path": "datanotes/main/public/actor/Cary Grant.md" }
```

Answer:

```markdown
---
table: main.public.actor
id: 01a13107-8e7b-77ae-bda9-01cb6be24f6c
name: Cary Grant
born: 1904-01-18
films:
  - "[[Charade]]"
---
```

One wrong item makes the engine refuse the whole list:

**`update_row`**

```json
{
  "table": "film",
  "path": "datanotes/main/public/film/His Girl Friday.md",
  "set": { "cast": ["[[Cary Grant]]", "[[Charade]]"] }
}
```

Refused:

```text
update_row refused — nothing was written:
invalid datanotes/main/public/film/His Girl Friday.md: cast[1] link must point to a row of table "main.public.actor"
Hint: Link to a row of the table the column points to (its ref) that its ref_where / ref_by allow: list_link_targets(table, column) lists them. Then call update_row again (dry_run: true checks without writing).
Docs: https://datanotes.dev/guides/database-objects/relations.md#limit-link-targets-with-ref_by-and-ref_where (offline: read_docs("guides/database-objects/relations#limit-link-targets-with-ref_by-and-ref_where"))
```

**`update_row`**

```json
{
  "table": "film",
  "path": "datanotes/main/public/film/His Girl Friday.md",
  "set": { "cast": ["[[Cary Grant]]", "[[Rosalind Russell]]"] }
}
```

## Read linked rows in the same call

`expand` replaces each link with the linked row’s columns, so one call returns the films with their actors.

**`select_rows`**

```json
{
  "table": "film",
  "columns": ["title", "cast"],
  "expand": ["cast"],
  "where": { "title": "Charade" }
}
```

Answer:

```json
{
  "table": "main.public.film",
  "total": 1,
  "rows": [
    {
      "path": "datanotes/main/public/film/Charade.md",
      "title": "Charade",
      "cast": [
        {
          "path": "datanotes/main/public/actor/Cary Grant.md",
          "table": "main.public.actor",
          "id": "01a13107-8e7b-77ae-bda9-01cb6be24f6c",
          "name": "Cary Grant",
          "born": "1904-01-18",
          "films": ["[[Charade]]", "[[His Girl Friday]]"]
        },
        {
          "path": "datanotes/main/public/actor/Audrey Hepburn.md",
          "table": "main.public.actor",
          "id": "01a13107-8e88-76f0-8353-970d7c524c3b",
          "name": "Audrey Hepburn",
          "born": "1929-05-04",
          "films": ["[[Charade]]"]
        }
      ]
    }
  ]
}
```

## Repair two-way links with `align_relations`

Writes through the engine keep both sides of an `inverse` link in step, and the engine mirrors links you edit by hand about 2s later. When you declare `inverse` on data that already exists, `align_relations` adds the missing reciprocal links. `check_table` reports the missing ones first, with code `inverse`.

**`add_column`**

```json
{
  "table": "film",
  "column": {
    "name": "see_also",
    "type": "list",
    "items": "link",
    "ref": "self",
    "description": "Films to watch next if I liked this one"
  }
}
```

**`update_row`**

```json
{
  "table": "film",
  "path": "datanotes/main/public/film/Charade.md",
  "set": { "see_also": ["[[His Girl Friday]]"] }
}
```

Charade points to His Girl Friday, but His Girl Friday doesn’t point back. Declare `see_also` symmetric:

**`alter_column`**

```json
{ "table": "film", "column": "see_also", "changes": { "inverse": "see_also" } }
```

Answer:

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

**`check_table`**

```json
{ "table": "film" }
```

Answer:

```json
{
  "table": "film",
  "rows_checked": 3,
  "total": 1,
  "violations": [
    {
      "path": "datanotes/main/public/film/Charade.md",
      "violations": [
        {
          "column": "see_also",
          "code": "inverse",
          "message": "datanotes/main/public/film/His Girl Friday.md does not link back in \"see_also\" (align_relations adds it)",
          "value": "datanotes/main/public/film/His Girl Friday.md"
        }
      ]
    }
  ]
}
```

**`align_relations`**

```json
{ "table": "film" }
```

Answer:

```json
{
  "ok": true,
  "op": "align_relations",
  "notes_changed": 1,
  "planned_edits": [
    {
      "path": "datanotes/main/public/film/His Girl Friday.md",
      "set": { "see_also": ["[[Charade]]"] },
      "remove": []
    }
  ],
  "commits": [
    { "table": "main.public.film", "version": 10, "operation": "ALIGN RELATIONS" }
  ],
  "migration_id": "20261012T165221-align_relations-align-inverse-links-of-main-pu"
}
```

**`select_rows`**

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

Answer:

```json
{
  "table": "main.public.film",
  "total": 3,
  "rows": [
    {
      "path": "datanotes/main/public/film/Charade.md",
      "title": "Charade",
      "see_also": ["[[His Girl Friday]]"]
    },
    {
      "path": "datanotes/main/public/film/His Girl Friday.md",
      "title": "His Girl Friday",
      "see_also": ["[[Charade]]"]
    },
    {
      "path": "datanotes/main/public/film/Night of the Living Dead.md",
      "title": "Night of the Living Dead",
      "see_also": null
    }
  ]
}
```

## Take allowed values from another table

`vocabulary` limits a text column, or a list of text, to the names of another table’s rows, without links. Each value must be the `name` of a row of that table, so adding a row to the vocabulary table adds an allowed value.

**`create_table`**

```json
{
  "name": "tag",
  "description": "Words I tag films with",
  "columns": [
    {
      "name": "name",
      "type": "string",
      "required": true,
      "unique": true,
      "description": "The tag, as I write it"
    }
  ],
  "filename": "{name}"
}
```

**`insert_row`**

```json
{ "table": "tag", "values": { "name": "funny" } }
```

**`insert_row`**

```json
{ "table": "tag", "values": { "name": "rewatch" } }
```

**`add_column`**

```json
{
  "table": "film",
  "column": {
    "name": "tags",
    "type": "list",
    "items": "string",
    "vocabulary": "main.public.tag",
    "description": "My tags for the film, from the tag table"
  }
}
```

Answer:

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

**`update_row`**

```json
{
  "table": "film",
  "path": "datanotes/main/public/film/His Girl Friday.md",
  "set": { "tags": ["funny", "fast talk"] }
}
```

Refused:

```text
update_row refused — nothing was written:
invalid datanotes/main/public/film/His Girl Friday.md: tags[1] "fast talk" is not in main.public.tag (add it there, or use one of: funny, rewatch)
Hint: Use one of the values listed in the message, or first insert the new value as a row of the vocabulary table. Then call update_row again (dry_run: true checks without writing).
Docs: https://datanotes.dev/guides/database-objects/relations.md#take-allowed-values-from-another-table (offline: read_docs("guides/database-objects/relations#take-allowed-values-from-another-table"))
```

**`insert_row`**

```json
{ "table": "tag", "values": { "name": "fast talk" } }
```

Once you add `fast talk` to the tag table, the same write goes through:

**`update_row`**

```json
{
  "table": "film",
  "path": "datanotes/main/public/film/His Girl Friday.md",
  "set": { "tags": ["funny", "fast talk"] }
}
```

Answer:

```json
{
  "ok": true,
  "op": "update_row",
  "notes_changed": 1,
  "planned_edits": [
    {
      "path": "datanotes/main/public/film/His Girl Friday.md",
      "set": { "tags": ["funny", "fast talk"] },
      "remove": []
    }
  ],
  "commits": [{ "table": "main.public.film", "version": 12, "operation": "UPDATE" }],
  "migration_id": "20261012T165221-update_row-update-film-4"
}
```

## Build a tree of rows with a parent link

`hierarchy: true` makes a link column a parent link to the same table (`ref: self`). A row can never be its own ancestor. The `within` filter on the parent column returns every row below a given one, at any depth. Here the genres form a tree: Cinema holds the sound era, which holds screwball comedy.

**`create_table`**

```json
{
  "name": "genre",
  "description": "Eras and genres of cinema, as a tree",
  "columns": [
    {
      "name": "name",
      "type": "string",
      "required": true,
      "description": "The name of the era or genre"
    },
    {
      "name": "kind",
      "type": "enum",
      "values": ["cinema", "era", "genre"],
      "required": true,
      "description": "The level in the tree: cinema, an era or a genre"
    },
    {
      "name": "parent",
      "type": "link",
      "ref": "self",
      "hierarchy": true,
      "description": "The era or cinema one level up"
    }
  ],
  "filename": "{name}"
}
```

Answer:

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

**`insert_row`**

```json
{ "table": "genre", "values": { "name": "Cinema", "kind": "cinema" } }
```

**`insert_row`**

```json
{
  "table": "genre",
  "values": { "name": "Sound film", "kind": "era", "parent": "[[Cinema]]" }
}
```

**`insert_row`**

```json
{
  "table": "genre",
  "values": { "name": "Screwball comedy", "kind": "genre", "parent": "[[Sound film]]" }
}
```

**`select_rows`**

```json
{
  "table": "genre",
  "columns": ["name", "parent"],
  "where": { "parent": { "within": "[[Cinema]]" } }
}
```

Answer:

```json
{
  "table": "main.public.genre",
  "total": 2,
  "rows": [
    {
      "path": "datanotes/main/public/genre/Screwball comedy.md",
      "name": "Screwball comedy",
      "parent": "[[Sound film]]"
    },
    {
      "path": "datanotes/main/public/genre/Sound film.md",
      "name": "Sound film",
      "parent": "[[Cinema]]"
    }
  ]
}
```

The engine refuses a loop:

**`update_row`**

```json
{
  "table": "genre",
  "path": "datanotes/main/public/genre/Cinema.md",
  "set": { "parent": "[[Screwball comedy]]" }
}
```

Refused:

```text
update_row refused — nothing was written:
invalid datanotes/main/public/genre/Cinema.md: parent would make "Cinema" its own ancestor (Cinema → Screwball comedy → Sound film → Cinema)
Hint: A row cannot be its own ancestor: point the hierarchy column to a row that is not below this one. Then call update_row again (dry_run: true checks without writing).
Docs: https://datanotes.dev/guides/database-objects/relations.md#build-a-tree-of-rows-with-a-parent-link (offline: read_docs("guides/database-objects/relations#build-a-tree-of-rows-with-a-parent-link"))
```

## Limit link targets with `ref_by` and `ref_where`

A link column can limit which rows it points to, without a CHECK. `ref_by` reads a column of the row itself. With `levels: [cinema, era, genre]`, Cinema has no parent, an era’s parent must be Cinema, and a genre’s parent must be an era.

These are the levels of the CHECK above, which stays. Each refusal below names both rules, and the `ref_by` one also lists the valid parents.

`describe_table` shows the expanded `map`. You can also write `map` directly, with `null` for a top level.

`ref_where` is a `where` filter that every linked row must match. The engine checks writes as it does for CHECKs that follow links, including the rows that link to a changed row.

**`alter_column`**

```json
{
  "table": "genre",
  "column": "parent",
  "changes": { "ref_by": { "column": "kind", "levels": ["cinema", "era", "genre"] } }
}
```

Answer:

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

**`describe_table`**

```json
{ "table": "genre" }
```

Answer:

```json
{
  "name": "parent",
  "type": "link",
  "required": false,
  "ref": "main.public.genre",
  "aliases": [],
  "description": "The era or cinema one level up",
  "hierarchy": true,
  "ref_by": {
    "column": "kind",
    "levels": ["cinema", "era", "genre"],
    "map": { "cinema": null, "era": ["cinema"], "genre": ["era"] }
  },
  "dense": false
}
```

A genre needs a parent, and the refusal (`required`) says why:

**`update_row`**

```json
{
  "table": "genre",
  "path": "datanotes/main/public/genre/Slapstick comedy.md",
  "unset": ["parent"]
}
```

Refused:

```text
update_row refused — nothing was written:
invalid datanotes/main/public/genre/Slapstick comedy.md: parent is required for kind "genre": it needs a parent with kind era
invalid datanotes/main/public/genre/Slapstick comedy.md: parent_by_level violates CHECK parent_by_level: {"or":[{"kind":"cinema","parent":{"exists":false}},{"kind":"era","parent.kind":"cinema"},{"kind":"genre","parent.kind":"era"}]}
Hint: Give a value to every required column; describe_table marks them (required: true) and lists an enum's values. The row breaks a CHECK constraint of the table (named in the message, with its rule): change the values so the rule holds. describe_table lists the table's constraints. Then call update_row 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"))
```

The engine refuses a parent of the wrong level and lists the valid ones:

**`update_row`**

```json
{
  "table": "genre",
  "path": "datanotes/main/public/genre/Slapstick comedy.md",
  "set": { "parent": "[[Cinema]]" }
}
```

Refused:

```text
update_row refused — nothing was written:
invalid datanotes/main/public/genre/Slapstick comedy.md: parent [[Cinema]] is not a valid parent for kind "genre" (needs kind era); valid: Silent film, Sound film
invalid datanotes/main/public/genre/Slapstick comedy.md: parent_by_level violates CHECK parent_by_level: {"or":[{"kind":"cinema","parent":{"exists":false}},{"kind":"era","parent.kind":"cinema"},{"kind":"genre","parent.kind":"era"}]}
Hint: Link to a row of the table the column points to (its ref) that its ref_where / ref_by allow: list_link_targets(table, column) lists them. The row breaks a CHECK constraint of the table (named in the message, with its rule): change the values so the rule holds. describe_table lists the table's constraints. Then call update_row again (dry_run: true checks without writing).
Docs: https://datanotes.dev/guides/database-objects/relations.md#limit-link-targets-with-ref_by-and-ref_where (offline: read_docs("guides/database-objects/relations#limit-link-targets-with-ref_by-and-ref_where"))
```

`list_link_targets` returns the valid targets for a row before you write it, so an agent or a picker offers only those:

**`list_link_targets`**

```json
{ "table": "genre", "column": "parent", "values": { "kind": "genre" } }
```

Answer:

```json
{
  "table": "main.public.genre",
  "column": "parent",
  "ref": "main.public.genre",
  "required": true,
  "empty": false,
  "reason": "kind \"genre\" needs kind era",
  "rows": [
    {
      "name": "Silent film",
      "link": "[[datanotes/main/public/genre/Silent film|Silent film]]",
      "path": "datanotes/main/public/genre/Silent film.md",
      "kind": "era"
    },
    {
      "name": "Sound film",
      "link": "[[datanotes/main/public/genre/Sound film|Sound film]]",
      "path": "datanotes/main/public/genre/Sound film.md",
      "kind": "era"
    }
  ],
  "total": 2
}
```

Silent film can’t become a top level while a genre hangs from it:

**`update_row`**

```json
{
  "table": "genre",
  "path": "datanotes/main/public/genre/Silent film.md",
  "set": { "kind": "cinema" },
  "unset": ["parent"]
}
```

Refused:

```text
update_row refused — nothing was written:
invalid datanotes/main/public/genre/Slapstick comedy.md: parent [[Silent film]] is not a valid parent for kind "genre" (needs kind era); valid: Sound film (after this change to [[Silent film]], a row it links to)
invalid datanotes/main/public/genre/Slapstick comedy.md: parent_by_level violates CHECK parent_by_level: {"or":[{"kind":"cinema","parent":{"exists":false}},{"kind":"era","parent.kind":"cinema"},{"kind":"genre","parent.kind":"era"}]} (after this change to [[Silent film]], a row it links to)
Hint: Link to a row of the table the column points to (its ref) that its ref_where / ref_by allow: list_link_targets(table, column) lists them. The row breaks a CHECK constraint of the table (named in the message, with its rule): change the values so the rule holds. describe_table lists the table's constraints. Then call update_row again (dry_run: true checks without writing).
Docs: https://datanotes.dev/guides/database-objects/relations.md#limit-link-targets-with-ref_by-and-ref_where (offline: read_docs("guides/database-objects/relations#limit-link-targets-with-ref_by-and-ref_where"))
```

A new `genre` column on `film` uses `ref_where`, so a film links to a genre, never to an era or to Cinema:

**`add_column`**

```json
{
  "table": "film",
  "column": {
    "name": "genre",
    "type": "link",
    "ref": "main.public.genre",
    "ref_where": { "kind": "genre" },
    "description": "The film's genre"
  }
}
```

Answer:

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

**`list_link_targets`**

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

Answer:

```json
{
  "table": "main.public.film",
  "column": "genre",
  "ref": "main.public.genre",
  "required": false,
  "empty": false,
  "reason": "ref_where {\"kind\":\"genre\"}",
  "rows": [
    {
      "name": "Screwball comedy",
      "link": "[[datanotes/main/public/genre/Screwball comedy|Screwball comedy]]",
      "path": "datanotes/main/public/genre/Screwball comedy.md"
    },
    {
      "name": "Slapstick comedy",
      "link": "[[datanotes/main/public/genre/Slapstick comedy|Slapstick comedy]]",
      "path": "datanotes/main/public/genre/Slapstick comedy.md"
    }
  ],
  "total": 2
}
```

## Compute a link from other columns

A generated link column builds its link from a template. `link({occurred_at:YYYY-MM-DD})`, with `ref` set to a table of days, links each viewing to its day’s note. That note may not exist yet; the link resolves once it does.

**`create_table`**

```json
{
  "name": "day",
  "description": "One note per day of my diary",
  "columns": [
    {
      "name": "date",
      "type": "date",
      "required": true,
      "description": "The day the note is about"
    }
  ],
  "time_column": "date",
  "filename": "{date:YYYY-MM-DD}"
}
```

**`add_column`**

```json
{
  "table": "watch",
  "column": {
    "name": "on_day",
    "type": "link",
    "ref": "main.public.day",
    "generated": "link({occurred_at:YYYY-MM-DD})",
    "description": "The diary note of the day I watched the film"
  }
}
```

Answer:

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

**`insert_row`**

```json
{
  "table": "watch",
  "values": {
    "film": "[[Night of the Living Dead]]",
    "where": "cinema",
    "place": "the film club downtown",
    "with": "my sister",
    "occurred_at": "2026-10-31T23:00"
  },
  "body": "A late show. Half the hall jumped at the same moment.\n"
}
```

Answer:

```json
{
  "ok": true,
  "op": "insert_row",
  "files_created": 1,
  "planned_creates": ["datanotes/main/public/watch/2026-10-31T2300.md"],
  "commits": [{ "table": "main.public.watch", "version": 4, "operation": "WRITE" }],
  "migration_id": "20261012T165223-insert_row-insert-into-watch"
}
```

`datanotes/main/public/watch/2026-10-31T2300.md` now reads:

```markdown
---
table: main.public.watch
id: 01a13107-9616-7da5-909a-3168599a50d5
film: "[[Night of the Living Dead]]"
where: cinema
place: the film club downtown
with: my sister
occurred_at: 2026-10-31T23:00
on_day: "[[datanotes/main/public/day/2026-10-31|2026-10-31]]"
---
A late show. Half the hall jumped at the same moment.
```

## Delete a row that other notes link to

`on_referenced` tells `delete_rows` what to do with links to the rows it deletes:

* **`"restrict"`**: the default; refuses rows that other notes link to
* **`"set_null"`**: clears those links, but refuses a required one, like `ON DELETE SET NULL` on a `NOT NULL` column in Structured Query Language (SQL)
* **`"ignore"`**: leaves the links dangling

**`delete_rows`**

```json
{ "table": "film", "where": { "title": "Charade" } }
```

Refused:

```text
delete_rows refused — nothing was written:
conflict datanotes/main/public/film/Charade.md: referenced by datanotes/main/public/actor/Audrey Hepburn.md, datanotes/main/public/actor/Cary Grant.md, datanotes/main/public/film/His Girl Friday.md, datanotes/main/public/watch/2026-09-26T2130.md
Hint: Other rows link to it: pass on_referenced ("set_null" or "ignore"), or change those rows first.
Docs: https://datanotes.dev/guides/database-objects/relations.md#delete-a-row-that-other-notes-link-to (offline: read_docs("guides/database-objects/relations#delete-a-row-that-other-notes-link-to"))
```

Charade’s viewing needs its `film`, so the engine also refuses to clear the link:

**`delete_rows`**

```json
{ "table": "film", "where": { "title": "Charade" }, "on_referenced": "set_null" }
```

Refused:

```text
delete_rows refused — nothing was written:
conflict datanotes/main/public/watch/2026-09-26T2130.md: "film" is required and would be left empty: change or delete this row first, or use on_referenced "ignore"
Hint: Other rows link to it: pass on_referenced ("set_null" or "ignore"), or change those rows first.
Docs: https://datanotes.dev/guides/database-objects/relations.md#delete-a-row-that-other-notes-link-to (offline: read_docs("guides/database-objects/relations#delete-a-row-that-other-notes-link-to"))
```

Delete the viewing and the film together with `batch`. The viewing goes first, then `set_null` clears Charade from Cary Grant’s `films`. If the engine refused any step, it would apply none of them.

**`batch`**

```json
{
  "summary": "remove Charade and its viewing",
  "operations": [
    { "op": "delete_rows", "table": "watch", "where": { "film": "[[Charade]]" } },
    {
      "op": "delete_rows",
      "table": "film",
      "where": { "title": "Charade" },
      "on_referenced": "set_null"
    }
  ]
}
```

Answer (72 lines)

```json
{
  "ok": true,
  "op": "batch",
  "dry_run": false,
  "migration_id": "20261012T165223-batch-remove-charade-and-its-viewing",
  "steps": [
    {
      "index": 0,
      "op": "delete_rows",
      "ok": true,
      "migration_id": "20261012T165223-delete_rows-archive-1-rows-of-watch",
      "result": {
        "ok": true,
        "op": "delete_rows",
        "files_moved": 1,
        "planned_moves": [
          {
            "from": "datanotes/main/public/watch/2026-09-26T2130.md",
            "to": "datanotes/main/public/watch/_archive/2026-09-26T2130.md"
          }
        ],
        "commits": [{ "table": "main.public.watch", "version": 5, "operation": "DELETE" }],
        "migration_id": "20261012T165223-delete_rows-archive-1-rows-of-watch"
      }
    },
    {
      "index": 1,
      "op": "delete_rows",
      "ok": true,
      "migration_id": "20261012T165223-delete_rows-archive-1-rows-of-film",
      "result": {
        "ok": true,
        "op": "delete_rows",
        "notes_changed": 4,
        "files_moved": 1,
        "planned_edits": [
          {
            "path": "datanotes/main/public/actor/Audrey Hepburn.md",
            "set": { "films": [] },
            "remove": []
          },
          {
            "path": "datanotes/main/public/actor/Cary Grant.md",
            "set": { "films": ["[[His Girl Friday]]"] },
            "remove": []
          },
          {
            "path": "datanotes/main/public/film/His Girl Friday.md",
            "set": { "see_also": [] },
            "remove": []
          },
          {
            "path": "datanotes/main/public/watch/_archive/2026-09-26T2130.md",
            "set": { "film": null },
            "remove": []
          }
        ],
        "planned_moves": [
          {
            "from": "datanotes/main/public/film/Charade.md",
            "to": "datanotes/main/public/film/_archive/Charade.md"
          }
        ],
        "commits": [
          { "table": "main.public.actor", "version": 7, "operation": "DELETE" },
          { "table": "main.public.film", "version": 14, "operation": "DELETE" }
        ],
        "migration_id": "20261012T165223-delete_rows-archive-1-rows-of-film"
      }
    }
  ]
}
```

**`read_note`**

```json
{ "path": "datanotes/main/public/actor/Cary Grant.md" }
```

Answer:

```markdown
---
table: main.public.actor
id: 01a13107-8e7b-77ae-bda9-01cb6be24f6c
name: Cary Grant
born: 1904-01-18
films:
  - "[[His Girl Friday]]"
---
```

## See the relations in `metastore.yaml`

The engine wrote this file with the relations from this page:

* **Link columns**: each with its `ref`, and `self` kept as written
* **`inverse`**: on one side only
* **`vocabulary`, `hierarchy` and `ref_where`**: as declared
* **`ref_by`**: with the `levels` shortcut kept as written
* **Generated link**: the `on_day` column with its template

`datanotes/metastore.yaml` now reads:

```yaml
# Metastore (Datanotes): catalogs, schemas, tables, columns, views.
id: 01a13107-8e71-7263-9eba-ac6e61e42db5
config:
  history: true


catalogs:
  main:
    schemas:
      public:
        tables:
          actor:
            description: Actors of the films I watch
            options:
              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
                unique: true
              - name: born
                type: date
                description: The actor's date of birth
              - name: films
                type: list
                items: link
                ref: main.public.film
                description: The films the actor plays in
          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
                unique: true
              - name: year
                type: integer
                description: Year the film first came out
              - name: minutes
                type: integer
                min: 1
                description: Running time in minutes
              - name: cast
                type: list
                items: link
                ref: main.public.actor
                description: The actors in the film
                inverse: films
              - name: see_also
                type: list
                items: link
                ref: self
                description: Films to watch next if I liked this one
                inverse: see_also
              - name: tags
                type: list
                items: string
                description: My tags for the film, from the tag table
                vocabulary: main.public.tag
              - name: genre
                type: link
                ref: main.public.genre
                description: The film's genre
                ref_where:
                  kind: genre
          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: link
                required: true
                ref: main.public.film
                description: The film I watched
              - name: where
                type: enum
                required: true
                values:
                  - cinema
                  - home
                  - outdoor screening
                description: The kind of place I watched it in
              - name: place
                type: string
                description: The cinema, park or room, in my words
              - name: with
                type: string
                description: Who watched it with me
              - name: occurred_at
                type: datetime
                required: true
                description: When the film started
              - name: on_day
                type: link
                ref: main.public.day
                description: The diary note of the day I watched the film
                generated: link({occurred_at:YYYY-MM-DD})
          tag:
            description: Words I tag films with
            options:
              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 tag, as I write it
                unique: true
          genre:
            description: Eras and genres of cinema, as a tree
            constraints:
              parent_by_level:
                or:
                  - kind: cinema
                    parent:
                      exists: false
                  - kind: era
                    parent.kind: cinema
                  - kind: genre
                    parent.kind: era
            options:
              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 name of the era or genre
              - name: kind
                type: enum
                required: true
                values:
                  - cinema
                  - era
                  - genre
                description: "The level in the tree: cinema, an era or a genre"
              - name: parent
                type: link
                ref: self
                description: The era or cinema one level up
                hierarchy: true
                ref_by:
                  column: kind
                  levels:
                    - cinema
                    - era
                    - genre
          day:
            description: One note per day of my diary
            options:
              filename: "{date:YYYY-MM-DD}"
              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: date
                type: date
                required: true
                description: The day the note is about
```