Relations and hierarchies

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

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

{
  "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

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

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

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

{
  "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

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

Refused:

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

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

Refused:

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

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

Answer:

{
  "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

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

Answer:

{
  "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

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

Answer:

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

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

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

Answer:

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

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

Refused:

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

{
  "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

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

Answer:

{
  "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

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

update_row

{
  "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

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

Answer:

{
  "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

{ "table": "film" }

Answer:

{
  "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

{ "table": "film" }

Answer:

{
  "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

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

Answer:

{
  "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

{
  "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

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

insert_row

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

add_column

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

Answer:

{
  "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

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

Refused:

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

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

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

update_row

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

Answer:

{
  "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

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

{
  "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

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

insert_row

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

insert_row

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

select_rows

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

Answer:

{
  "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

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

Refused:

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

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

Answer:

{
  "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

{ "table": "genre" }

Answer:

{
  "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

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

Refused:

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

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

Refused:

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

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

Answer:

{
  "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

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

Refused:

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

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

Answer:

{
  "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

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

Answer:

{
  "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

{
  "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

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

{
  "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

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

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

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

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

Refused:

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

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

Refused:

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

{
  "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)

{
  "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

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

Answer:

---
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:

# 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

Relations and hierarchies

Point a column to a row of another table

Link to several rows with a list of links

Read linked rows in the same call

Repair two-way links with align_relations

Take allowed values from another table

Build a tree of rows with a parent link

Limit link targets with ref_by and ref_where

Compute a link from other columns

Delete a row that other notes link to

See the relations in metastore.yaml

Repair two-way links with `align_relations`

Limit link targets with `ref_by` and `ref_where`

See the relations in `metastore.yaml`