Skip to content
Datanotes

Tables

A table lives in datanotes/metastore.yaml, and each of its rows is a Markdown note whose front matter key table names the table. Each column is a key of that front matter. You create and change tables only through operations, which validate each change, journal it and can undo it.

The examples start from an empty vault and build a small diary of classic films in the public domain. Later examples organize the tables of a second diary, kept in the catalog diary.

Create a table with typed columns

Section titled “Create a table with typed columns”

create_table takes the table’s columns and their types. The table and each column need a description: what a row records, and what each column holds. An agent matches your words against the descriptions and the aliases to find the table. filename names each row’s note after one of its columns.

create_table

{
  "name": "film",
  "description": "Classic films I watched or want to watch",
  "columns": [
    {
      "name": "title",
      "type": "string",
      "required": true,
      "unique": 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,
      "description": "Running time in minutes"
    },
    {
      "name": "status",
      "type": "enum",
      "values": ["to watch", "watched"],
      "ordered": true,
      "default": "to watch",
      "description": "Where the film is on my list"
    },
    {
      "name": "rating",
      "type": "integer",
      "min": 1,
      "max": 5,
      "description": "My rating, from 1 (poor) to 5 (a masterpiece)"
    },
    { "name": "watched_on", "type": "date", "description": "The day I last watched it" }
  ],
  "filename": "{title}",
  "aliases": ["films", "movies", "watchlist"]
}

Answer:

{
  "ok": true,
  "migration_id": "20261012T165218-create_table-create-table-main-public-film",
  "summary": null
}

datanotes/metastore.yaml now reads:

# Metastore (Datanotes): catalogs, schemas, tables, columns, views.
id: 01a13107-8515-7229-b1f9-a1e1d9894805
config:
  history: true


catalogs:
  main:
    schemas:
      public:
        tables:
          film:
            description: Classic films I watched or want to watch
            aliases:
              - films
              - movies
              - watchlist
            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: status
                type: enum
                default: to watch
                values:
                  - to watch
                  - watched
                description: Where the film is on my list
                ordered: true
              - name: rating
                type: integer
                min: 1
                max: 5
                description: My rating, from 1 (poor) to 5 (a masterpiece)
              - name: watched_on
                type: date
                description: The day I last watched it

The engine writes the definition in YAML Ain’t Markup Language (YAML), and you can read the file like any other note. Change it only through the operations (add_column, alter_table, …): they validate the change against the rows, journal it and make it undoable. A hand edit does none of that.

The file uses the operations’ words: a column is required, a table or a column has a description, and fields keeps the fixed front matter keys. The table’s partition, filename, archive and exclude go under options.

The file also holds two things the engine adds on its own. One is the id column every table has, with its description. The other is archive, the folder for archived rows. archive is _archive unless configure_database sets another folder.

Describe every table and column

Section titled “Describe every table and column”

Agents and people understand the schema through its descriptions. The engine refuses a table or a column without one. The refusal lists every missing description at once, with the shape that fixes it. Write each one for a reader who never saw your notes.

create_table

{
  "name": "screening",
  "description": "Films shown in my town",
  "columns": [
    { "name": "film", "type": "string", "required": true },
    { "name": "venue", "type": "string" },
    {
      "name": "starts_at",
      "type": "datetime",
      "description": "When the screening begins"
    }
  ],
  "filename": "{starts_at:YYYY-MM-DD} {film}"
}

Refused:

descriptions are required: main.public.screening has none for columns film, venue
Hint: Say what a row records in "description" and what each column holds in its own "description": {"name": "screening", "description": "<what a row of screening records>", "columns": [{"name": "film", "type": "string", "description": "<what film holds>"}, …]}. Agents and people understand the schema through them.
Docs: https://datanotes.dev/guides/database-objects/tables.md#describe-every-table-and-column (offline: read_docs("guides/database-objects/tables#describe-every-table-and-column"))

A description can change, but it never goes. alter_column and alter_table refuse null or an empty text:

alter_column

{ "table": "film", "column": "year", "changes": { "description": null } }

Refused:

the description of column year of main.public.film cannot be removed (null or empty): every column needs one
Hint: Pass the new text, as in {"table": "main.public.film", "column": "year", "changes": {"description": "<what year holds>"}}, or leave "description" out to keep the one it has.
Docs: https://datanotes.dev/guides/database-objects/tables.md#describe-every-table-and-column (offline: read_docs("guides/database-objects/tables#describe-every-table-and-column"))

You don’t write every description yourself. The engine writes those of the columns it sets itself: id, and identity columns of kind uuid or created.

A table created before descriptions were required may lack some, and it stays valid. describe_table and describe_database name what it lacks in missing_descriptions, such as {"table": false, "columns": ["year"]}. Fill each one with alter_table or alter_column. An infer_table proposal leaves its descriptions null for you to write, as Infer a table from notes shows.

Find the table that matches a request

Section titled “Find the table that matches a request”

list_tables returns every table with its description and aliases. An agent matches your words against them, so “add this to my watchlist” leads to the film table.

list_tables (no arguments)

Answer:

{
  "tables": [
    {
      "name": "film",
      "catalog": "main",
      "schema": "public",
      "qualified": "main.public.film",
      "description": "Classic films I watched or want to watch",
      "aliases": ["films", "movies", "watchlist"]
    }
  ],
  "views": []
}

Describe a table before writing to it

Section titled “Describe a table before writing to it”

describe_table lists the columns with their types and constraints, and the table’s row count. Read it before you write: it tells you which values the table accepts.

describe_table

{ "table": "film" }
Answer (83 lines) 
{
  "qualified": "main.public.film",
  "name": "film",
  "catalog": "main",
  "schema": "public",
  "description": "Classic films I watched or want to watch",
  "aliases": ["films", "movies", "watchlist"],
  "layout": {
    "folder": "datanotes/main/public/film",
    "row_folder": "datanotes/main/public/film",
    "archive": "datanotes/main/public/film/_archive"
  },
  "options": { "filename": "{title}", "archive": "_archive" },
  "columns": [
    {
      "name": "id",
      "type": "string",
      "required": true,
      "aliases": [],
      "unique": true,
      "description": "Unique id of the row (UUIDv7): set when the row is created, never changes",
      "identity": "uuid",
      "dense": true
    },
    {
      "name": "title",
      "type": "string",
      "required": true,
      "aliases": [],
      "unique": true,
      "description": "The film's title, as on its poster",
      "dense": false
    },
    {
      "name": "year",
      "type": "integer",
      "required": false,
      "aliases": [],
      "description": "Year the film first came out",
      "dense": false
    },
    {
      "name": "minutes",
      "type": "integer",
      "required": false,
      "min": 1,
      "aliases": [],
      "description": "Running time in minutes",
      "dense": false
    },
    {
      "name": "status",
      "type": "enum",
      "required": false,
      "values": ["to watch", "watched"],
      "aliases": [],
      "ordered": true,
      "description": "Where the film is on my list",
      "default": "to watch",
      "dense": false
    },
    {
      "name": "rating",
      "type": "integer",
      "required": false,
      "min": 1,
      "max": 5,
      "aliases": [],
      "description": "My rating, from 1 (poor) to 5 (a masterpiece)",
      "dense": false
    },
    {
      "name": "watched_on",
      "type": "date",
      "required": false,
      "aliases": [],
      "description": "The day I last watched it",
      "dense": false
    }
  ],
  "untyped_fields": ["table"],
  "row_count": 0
}

Add fixed front matter keys, a note template and tags

Section titled “Add fixed front matter keys, a note template and tags”

create_table takes three more arguments:

  • fields: front matter keys every new row gets with a fixed value and no checks, such as cssclasses, which styles the notes in Obsidian
  • row_template: the text of every new row’s note, with {column} placeholders
  • tags: key/value labels for the table, which list_tables and describe_table return

create_table

{
  "name": "watch",
  "description": "Each time I watch a film",
  "columns": [
    {
      "name": "film",
      "type": "string",
      "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": "occurred_at",
      "type": "datetime",
      "default": "{now}",
      "description": "When the film started"
    },
    {
      "name": "price",
      "type": "number",
      "min": 0,
      "step": 0.01,
      "description": "What the ticket cost, if anything"
    }
  ],
  "filename": "{occurred_at:YYYY-MM-DD} {film}",
  "fields": { "cssclasses": ["watch"] },
  "row_template": "Watched {film} ({where}).\n\n## Thoughts\n",
  "tags": { "area": "films" }
}

insert_row

{
  "table": "watch",
  "values": {
    "film": "Nosferatu",
    "where": "outdoor screening",
    "place": "the park by the river"
  }
}

Answer:

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

datanotes/main/public/watch/2026-10-12 Nosferatu.md now reads:

---
table: main.public.watch
cssclasses:
  - watch
id: 01a13107-8604-75c6-9afd-059611c065b0
film: Nosferatu
where: outdoor screening
place: the park by the river
occurred_at: 2026-10-12T16:52:19Z
---
Watched Nosferatu (outdoor screening).


## Thoughts

Set a table’s options when you create it

Section titled “Set a table’s options when you create it”

create_table also takes the options that alter_table changes later. Two of them:

  • exclude: folders whose notes never become rows, such as an editor’s templates
  • history: true keeps every version of the table, whatever the database’s setting

create_table

{
  "name": "review",
  "description": "What I wrote about a film after watching it",
  "columns": [
    {
      "name": "film",
      "type": "string",
      "required": true,
      "unique": true,
      "description": "The film the review is about"
    },
    { "name": "verdict", "type": "string", "description": "The review in one sentence" }
  ],
  "filename": "{film}",
  "exclude": ["datanotes/main/public/review/Templates"],
  "history": true
}

Answer:

{ "ok": true, "summary": null }

Keep a folder’s notes out of the table and Table history and time travel show both at work.

Rename or move a table

Section titled “Rename or move a table”

alter_table renames a table with name, or moves it to another catalog or schema. Here actors becomes actor, singular like the other tables. The rows’ folder and their front matter key table follow, and the engine rewrites the links to them. dry_run lists the notes that would move.

alter_table

{ "table": "classics.actors", "name": "actor", "dry_run": true }

Answer:

{
  "ok": true,
  "op": "alter_table",
  "dry_run": true,
  "planned_edits": [
    {
      "path": "datanotes/diary/classics/actors/Max Schreck.md",
      "set": { "table": "diary.classics.actor" },
      "remove": []
    }
  ],
  "planned_moves": [
    {
      "from": "store:transaction_log/diary.classics.actors/00000000000000000000.json",
      "to": "store:transaction_log/diary.classics.actor/00000000000000000000.json"
    },
    {
      "from": "store:transaction_log/diary.classics.actors/00000000000000000001.json",
      "to": "store:transaction_log/diary.classics.actor/00000000000000000001.json"
    },
    {
      "from": "store:transaction_log/diary.classics.actors/00000000000000000002.json",
      "to": "store:transaction_log/diary.classics.actor/00000000000000000002.json"
    },
    {
      "from": "store:transaction_log/diary.classics.actors/00000000000000000003.json",
      "to": "store:transaction_log/diary.classics.actor/00000000000000000003.json"
    },
    {
      "from": "store:transaction_log/diary.classics.actors/00000000000000000004.json",
      "to": "store:transaction_log/diary.classics.actor/00000000000000000004.json"
    },
    {
      "from": "store:transaction_log/diary.classics.actors/00000000000000000005.json",
      "to": "store:transaction_log/diary.classics.actor/00000000000000000005.json"
    },
    {
      "from": "datanotes/diary/classics/actors/Max Schreck.md",
      "to": "datanotes/diary/classics/actor/Max Schreck.md"
    }
  ],
  "planned_texts": ["datanotes/metastore.yaml"]
}

alter_table

{ "table": "classics.actors", "name": "actor" }
Answer (48 lines) 
{
  "ok": true,
  "op": "alter_table",
  "notes_changed": 1,
  "files_moved": 7,
  "planned_edits": [
    {
      "path": "datanotes/diary/classics/actors/Max Schreck.md",
      "set": { "table": "diary.classics.actor" },
      "remove": []
    }
  ],
  "planned_moves": [
    {
      "from": "store:transaction_log/diary.classics.actors/00000000000000000000.json",
      "to": "store:transaction_log/diary.classics.actor/00000000000000000000.json"
    },
    {
      "from": "store:transaction_log/diary.classics.actors/00000000000000000001.json",
      "to": "store:transaction_log/diary.classics.actor/00000000000000000001.json"
    },
    {
      "from": "store:transaction_log/diary.classics.actors/00000000000000000002.json",
      "to": "store:transaction_log/diary.classics.actor/00000000000000000002.json"
    },
    {
      "from": "store:transaction_log/diary.classics.actors/00000000000000000003.json",
      "to": "store:transaction_log/diary.classics.actor/00000000000000000003.json"
    },
    {
      "from": "store:transaction_log/diary.classics.actors/00000000000000000004.json",
      "to": "store:transaction_log/diary.classics.actor/00000000000000000004.json"
    },
    {
      "from": "store:transaction_log/diary.classics.actors/00000000000000000005.json",
      "to": "store:transaction_log/diary.classics.actor/00000000000000000005.json"
    },
    {
      "from": "datanotes/diary/classics/actors/Max Schreck.md",
      "to": "datanotes/diary/classics/actor/Max Schreck.md"
    }
  ],
  "planned_texts": ["datanotes/metastore.yaml"],
  "commits": [
    { "table": "diary.classics.actor", "version": 6, "operation": "ALTER TABLE" }
  ],
  "migration_id": "20261012T165235-alter_table-move-table-diary-classics-acto"
}

Then move it to a schema of its own:

alter_table

{ "table": "classics.actor", "catalog": "diary", "schema": "credits" }
Answer (52 lines) 
{
  "ok": true,
  "op": "alter_table",
  "notes_changed": 1,
  "files_moved": 8,
  "planned_edits": [
    {
      "path": "datanotes/diary/classics/actor/Max Schreck.md",
      "set": { "table": "diary.credits.actor" },
      "remove": []
    }
  ],
  "planned_moves": [
    {
      "from": "store:transaction_log/diary.classics.actor/00000000000000000000.json",
      "to": "store:transaction_log/diary.credits.actor/00000000000000000000.json"
    },
    {
      "from": "store:transaction_log/diary.classics.actor/00000000000000000001.json",
      "to": "store:transaction_log/diary.credits.actor/00000000000000000001.json"
    },
    {
      "from": "store:transaction_log/diary.classics.actor/00000000000000000002.json",
      "to": "store:transaction_log/diary.credits.actor/00000000000000000002.json"
    },
    {
      "from": "store:transaction_log/diary.classics.actor/00000000000000000003.json",
      "to": "store:transaction_log/diary.credits.actor/00000000000000000003.json"
    },
    {
      "from": "store:transaction_log/diary.classics.actor/00000000000000000004.json",
      "to": "store:transaction_log/diary.credits.actor/00000000000000000004.json"
    },
    {
      "from": "store:transaction_log/diary.classics.actor/00000000000000000005.json",
      "to": "store:transaction_log/diary.credits.actor/00000000000000000005.json"
    },
    {
      "from": "store:transaction_log/diary.classics.actor/00000000000000000006.json",
      "to": "store:transaction_log/diary.credits.actor/00000000000000000006.json"
    },
    {
      "from": "datanotes/diary/classics/actor/Max Schreck.md",
      "to": "datanotes/diary/credits/actor/Max Schreck.md"
    }
  ],
  "planned_texts": ["datanotes/metastore.yaml"],
  "commits": [
    { "table": "diary.credits.actor", "version": 7, "operation": "ALTER TABLE" }
  ],
  "migration_id": "20261012T165235-alter_table-move-table-diary-classics-acto-2"
}

list_folder

{ "folder": "datanotes/diary/credits/actor" }

Answer:

{
  "folder": "datanotes/diary/credits/actor",
  "folders": [],
  "files": ["datanotes/diary/credits/actor/Max Schreck.md"]
}

Change sub-folders and file names

Section titled “Change sub-folders and file names”

alter_table also changes partition, the sub-folders, and filename, the name pattern. The engine renames every row to fit, and refuses the change when a row can’t fill the pattern.

insert_row

{
  "table": "film",
  "values": { "title": "Metropolis", "year": 1927, "minutes": 153, "status": "to watch" }
}

alter_table

{ "table": "film", "partition": "{year}" }

Answer:

{
  "ok": true,
  "op": "alter_table",
  "files_moved": 3,
  "planned_moves": [
    {
      "from": "datanotes/diary/classics/film/His Girl Friday.md",
      "to": "datanotes/diary/classics/film/1940/His Girl Friday.md"
    },
    {
      "from": "datanotes/diary/classics/film/Metropolis.md",
      "to": "datanotes/diary/classics/film/1927/Metropolis.md"
    },
    {
      "from": "datanotes/diary/classics/film/Nosferatu.md",
      "to": "datanotes/diary/classics/film/1922/Nosferatu.md"
    }
  ],
  "planned_texts": ["datanotes/metastore.yaml"],
  "commits": [
    { "table": "diary.classics.film", "version": 13, "operation": "ALTER TABLE" }
  ],
  "migration_id": "20261012T165235-alter_table-alter-table-diary-classics-fil-5"
}

Metropolis has no rating yet, so the engine refuses a name with {rating}:

alter_table

{ "table": "film", "filename": "{title} ({rating} stars)" }

Refused:

alter_table refused — nothing was written:
conflict datanotes/diary/classics/film/1927/Metropolis.md: cannot compute the new path: path placeholder {rating} has no value
Hint: Resolve what the message names, then call alter_table again; dry_run: true checks without writing.
Docs: https://datanotes.dev/guides/agents/reading-errors.md (offline: read_docs("guides/agents/reading-errors"))

alter_table

{ "table": "film", "filename": "{title} ({year})" }

Answer:

{
  "ok": true,
  "op": "alter_table",
  "files_moved": 3,
  "planned_moves": [
    {
      "from": "datanotes/diary/classics/film/1922/Nosferatu.md",
      "to": "datanotes/diary/classics/film/1922/Nosferatu (1922).md"
    },
    {
      "from": "datanotes/diary/classics/film/1927/Metropolis.md",
      "to": "datanotes/diary/classics/film/1927/Metropolis (1927).md"
    },
    {
      "from": "datanotes/diary/classics/film/1940/His Girl Friday.md",
      "to": "datanotes/diary/classics/film/1940/His Girl Friday (1940).md"
    }
  ],
  "planned_texts": ["datanotes/metastore.yaml"],
  "commits": [
    { "table": "diary.classics.film", "version": 14, "operation": "ALTER TABLE" }
  ],
  "migration_id": "20261012T165235-alter_table-alter-table-diary-classics-fil-6"
}

list_folder

{ "folder": "datanotes/diary/classics/film/1940" }

Answer:

{
  "folder": "datanotes/diary/classics/film/1940",
  "folders": [],
  "files": ["datanotes/diary/classics/film/1940/His Girl Friday (1940).md"]
}

Set the archive folder and untyped fields

Section titled “Set the archive folder and untyped fields”

archive names the folder, inside the table’s folder, that archived rows go to, and rows already archived move with it. archive: "" goes back to the database’s archive_folder. fields sets untyped fields that every new row gets, as names and values like in create_table, and a null value removes one.

A change that touches only metadata, meaning fields, tags, description, history or the template, rewrites the definition alone and moves no row.

alter_table

{ "table": "film", "archive": "_done" }

Answer:

{
  "ok": true,
  "op": "alter_table",
  "planned_texts": ["datanotes/metastore.yaml"],
  "commits": [
    { "table": "diary.classics.film", "version": 15, "operation": "ALTER TABLE" }
  ],
  "migration_id": "20261012T165235-alter_table-alter-table-diary-classics-fil-7"
}

alter_table

{ "table": "film", "fields": { "cut": "restored", "subtitles": "off" } }

Answer:

{
  "ok": true,
  "op": "alter_table",
  "planned_texts": ["datanotes/metastore.yaml"],
  "commits": [
    { "table": "diary.classics.film", "version": 16, "operation": "ALTER TABLE" }
  ],
  "migration_id": "20261012T165235-alter_table-alter-table-diary-classics-fil-8"
}

alter_table

{ "table": "film", "fields": { "subtitles": null } }

Answer:

{
  "ok": true,
  "op": "alter_table",
  "planned_texts": ["datanotes/metastore.yaml"],
  "commits": [
    { "table": "diary.classics.film", "version": 17, "operation": "ALTER TABLE" }
  ],
  "migration_id": "20261012T165235-alter_table-alter-table-diary-classics-fil-9"
}

insert_row

{
  "table": "film",
  "values": {
    "title": "Battleship Potemkin",
    "year": 1925,
    "minutes": 75,
    "status": "to watch"
  }
}

Answer:

{
  "ok": true,
  "op": "insert_row",
  "files_created": 1,
  "planned_creates": ["datanotes/diary/classics/film/1925/Battleship Potemkin (1925).md"],
  "commits": [{ "table": "diary.classics.film", "version": 18, "operation": "WRITE" }],
  "migration_id": "20261012T165235-insert_row-insert-into-film-4"
}

datanotes/diary/classics/film/1925/Battleship Potemkin (1925).md now reads:

---
table: diary.classics.film
cut: restored
id: 01a13107-c7bc-77d9-864a-69b0e86fe6dc
title: Battleship Potemkin
year: 1925
minutes: 75
status: to watch
---

Give new notes a template

Section titled “Give new notes a template”

row_template, in create_table and alter_table, is the text of every new row’s note, with {column} placeholders. Notes you create by hand in the table’s folder get it too.

alter_table

{
  "table": "film",
  "row_template": "# {title} ({year})\n\n## What I liked\n\n## Who I watched it with\n"
}

Answer:

{
  "ok": true,
  "op": "alter_table",
  "planned_texts": ["datanotes/metastore.yaml"],
  "commits": [
    { "table": "diary.classics.film", "version": 19, "operation": "ALTER TABLE" }
  ],
  "migration_id": "20261012T165235-alter_table-alter-table-diary-classics-fil-10"
}

insert_row

{
  "table": "film",
  "values": { "title": "Sherlock Jr.", "year": 1924, "minutes": 45, "status": "to watch" }
}

Answer:

{
  "ok": true,
  "op": "insert_row",
  "files_created": 1,
  "planned_creates": ["datanotes/diary/classics/film/1924/Sherlock Jr. (1924).md"],
  "commits": [{ "table": "diary.classics.film", "version": 20, "operation": "WRITE" }],
  "migration_id": "20261012T165235-insert_row-insert-into-film-5"
}

datanotes/diary/classics/film/1924/Sherlock Jr. (1924).md now reads:

---
table: diary.classics.film
cut: restored
id: 01a13107-c7d7-743d-88de-0ad585741f15
title: Sherlock Jr.
year: 1924
minutes: 45
status: to watch
---
# Sherlock Jr. (1924)


## What I liked


## Who I watched it with

row_template: "" removes the template, and notes already written keep their text:

alter_table

{ "table": "film", "row_template": "" }

Answer:

{
  "ok": true,
  "op": "alter_table",
  "planned_texts": ["datanotes/metastore.yaml"],
  "commits": [
    { "table": "diary.classics.film", "version": 21, "operation": "ALTER TABLE" }
  ],
  "migration_id": "20261012T165235-alter_table-alter-table-diary-classics-fil-11"
}

Set defaults for new tables

Section titled “Set defaults for new tables”

The other settings shape the tables you create next:

  • default_schema, with default_catalog: places a table given only a name
  • default_partition: the sub-folders of a table with a time_column, as a date format
  • default_filename: the file names of a table with a time_column, as a date format
  • archive_folder: the name of a new table’s archive folder

Existing tables keep their settings.

configure_database

{
  "default_schema": "log",
  "default_partition": "YYYY/MM",
  "default_filename": "YYYY-MM-DD HHmm",
  "archive_folder": "_old"
}

Answer:

{
  "default_catalog": "diary",
  "default_schema": "log",
  "default_partition": "YYYY/MM",
  "default_filename": "YYYY-MM-DD HHmm",
  "archive_folder": "_old",
  "timezone": "Europe/Rome",
  "history": true,
  "autolink": true
}

create_table

{
  "name": "watch",
  "description": "Each time I watch a film",
  "columns": [
    {
      "name": "film",
      "type": "link",
      "ref": "diary.classics.film",
      "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": "place",
      "type": "string",
      "description": "The cinema, park or room, in my words"
    }
  ],
  "time_column": "occurred_at"
}

Answer:

{
  "ok": true,
  "migration_id": "20261012T165235-create_table-create-table-diary-log-watch"
}

The new table is diary.log.watch, and its rows go in sub-folders by year and month. A date-time without an offset gets Rome’s offset:

insert_row

{
  "table": "watch",
  "values": {
    "film": "[[Nosferatu (1922)]]",
    "occurred_at": "2026-10-06T21:30",
    "where": "outdoor screening",
    "place": "the park by the river"
  }
}

Answer:

{
  "ok": true,
  "op": "insert_row",
  "files_created": 1,
  "planned_creates": ["datanotes/diary/log/watch/2026/10/2026-10-06 2130.md"],
  "commits": [{ "table": "diary.log.watch", "version": 1, "operation": "WRITE" }],
  "migration_id": "20261012T165236-insert_row-insert-into-watch"
}

datanotes/diary/log/watch/2026/10/2026-10-06 2130.md now reads:

---
table: diary.log.watch
id: 01a13107-c826-7dcb-a634-54ffb9fe1b55
film: "[[Nosferatu (1922)]]"
occurred_at: 2026-10-06T21:30:00+02:00
where: outdoor screening
place: the park by the river
---

Copy a table to try changes

Section titled “Copy a table to try changes”

clone_table copies a table’s definition and rows under a new name, as independent copies. It can also copy the table as it was at an earlier version, as Copy a table as it was shows. With copy_rows: false, it copies only the definition. Use a copy to try something without touching the original.

clone_table

{ "source": "diary.classics.film", "target": "sandbox.classics.film_copy" }

Answer:

{
  "ok": true,
  "op": "clone_table",
  "files_created": 5,
  "planned_creates": [
    "datanotes/sandbox/classics/film_copy/1922/Nosferatu (1922).md",
    "datanotes/sandbox/classics/film_copy/1924/Sherlock Jr. (1924).md",
    "datanotes/sandbox/classics/film_copy/1925/Battleship Potemkin (1925).md",
    "datanotes/sandbox/classics/film_copy/1927/Metropolis (1927).md",
    "datanotes/sandbox/classics/film_copy/1940/His Girl Friday (1940).md"
  ],
  "planned_texts": ["datanotes/metastore.yaml"],
  "commits": [
    { "table": "sandbox.classics.film_copy", "version": 0, "operation": "CLONE" }
  ],
  "migration_id": "20261012T165236-clone_table-clone-diary-classics-film-to-s"
}

select_rows

{ "table": "sandbox.classics.film_copy", "columns": ["title", "year"] }

Answer:

{
  "table": "sandbox.classics.film_copy",
  "total": 5,
  "rows": [
    {
      "path": "datanotes/sandbox/classics/film_copy/1922/Nosferatu (1922).md",
      "title": "Nosferatu",
      "year": 1922
    },
    {
      "path": "datanotes/sandbox/classics/film_copy/1924/Sherlock Jr. (1924).md",
      "title": "Sherlock Jr.",
      "year": 1924
    },
    {
      "path": "datanotes/sandbox/classics/film_copy/1925/Battleship Potemkin (1925).md",
      "title": "Battleship Potemkin",
      "year": 1925
    },
    {
      "path": "datanotes/sandbox/classics/film_copy/1927/Metropolis (1927).md",
      "title": "Metropolis",
      "year": 1927
    },
    {
      "path": "datanotes/sandbox/classics/film_copy/1940/His Girl Friday (1940).md",
      "title": "His Girl Friday",
      "year": 1940
    }
  ]
}

clone_table

{
  "source": "diary.classics.film",
  "target": "sandbox.classics.film_empty",
  "copy_rows": false
}

Answer:

{
  "ok": true,
  "op": "clone_table",
  "planned_texts": ["datanotes/metastore.yaml"],
  "commits": [
    { "table": "sandbox.classics.film_empty", "version": 0, "operation": "CLONE" }
  ],
  "migration_id": "20261012T165236-clone_table-clone-diary-classics-film-to-s-2"
}

describe_table

{ "table": "film_empty" }

Answer:

{
  "name": "film_empty",
  "catalog": "sandbox",
  "schema": "classics",
  "rows": 0,
  "columns": ["id", "added_on", "title", "year", "minutes", "status", "rating"]
}

Drop a table and archive its rows

Section titled “Drop a table and archive its rows”

drop_table refuses a table that still has rows. With mode: "archive", it removes the definition and moves the rows to the table’s archive folder. The engine deletes nothing, and you can undo the operation.

You choose that folder with archive when you create the table. It’s a vault path, as here, or a folder name inside the table’s folder (_archive by default).

create_table

{
  "name": "scratch",
  "description": "Throwaway notes for trying things out",
  "columns": [{ "name": "note", "type": "string", "description": "The note's text" }],
  "filename": "{note}",
  "archive": "Archive/scratch"
}

insert_row

{ "table": "scratch", "values": { "note": "temporary" } }

drop_table

{ "table": "scratch" }

Refused:

drop_table refused — nothing was written:
conflict datanotes/metastore.yaml: table has 1 rows; pass mode: "archive" to archive them with the table
Hint: Pass mode: "archive" to archive the rows with the table, or delete them first (delete_rows).
Docs: https://datanotes.dev/guides/database-objects/tables.md#drop-a-table-and-archive-its-rows (offline: read_docs("guides/database-objects/tables#drop-a-table-and-archive-its-rows"))

drop_table

{ "table": "scratch", "mode": "archive" }

Answer:

{
  "ok": true,
  "op": "drop_table",
  "files_moved": 1,
  "planned_moves": [
    {
      "from": "datanotes/main/public/scratch/temporary.md",
      "to": "Archive/scratch/temporary.md"
    }
  ],
  "planned_texts": ["datanotes/metastore.yaml"],
  "commits": [{ "table": "main.public.scratch", "version": 2, "operation": "DROP TABLE" }],
  "migration_id": "20261012T165219-drop_table-drop-table-scratch"
}

list_folder

{ "folder": "Archive/scratch" }

Answer:

{ "folder": "Archive/scratch", "folders": [], "files": ["Archive/scratch/temporary.md"] }

See the tables in metastore.yaml

Section titled “See the tables in metastore.yaml”

The engine wrote this file in its own words. It holds film, watch and review, and scratch is gone because you dropped it.

datanotes/metastore.yaml now reads:

# Metastore (Datanotes): catalogs, schemas, tables, columns, views.
id: 01a13107-8515-7229-b1f9-a1e1d9894805
config:
  history: true


catalogs:
  main:
    schemas:
      public:
        tables:
          film:
            description: Classic films I watched or want to watch
            aliases:
              - films
              - movies
              - watchlist
            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: status
                type: enum
                default: to watch
                values:
                  - to watch
                  - watched
                description: Where the film is on my list
                ordered: true
              - name: rating
                type: integer
                min: 1
                max: 5
                description: My rating, from 1 (poor) to 5 (a masterpiece)
              - name: watched_on
                type: date
                description: The day I last watched it
          watch:
            description: Each time I watch a film
            tags:
              area: films
            options:
              filename: "{occurred_at:YYYY-MM-DD} {film}"
              archive: _archive
            fields:
              cssclasses:
                - watch
            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: 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: occurred_at
                type: datetime
                default: "{now}"
                description: When the film started
              - name: price
                type: number
                min: 0
                step: 0.01
                description: What the ticket cost, if anything
            row_template: |
              Watched {film} ({where}).


              ## Thoughts
          review:
            description: What I wrote about a film after watching it
            options:
              filename: "{film}"
              archive: _archive
              exclude: datanotes/main/public/review/Templates
              history: true
            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 the review is about
                unique: true
              - name: verdict
                type: string
                description: The review in one sentence