# Tables

> Create a table, find and describe it, set its folders, file names, template and tags, rename, copy and drop it.

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

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

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

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

`datanotes/metastore.yaml` now reads:

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

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

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

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

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

Refused:

```text
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](https://datanotes.dev/guides/read-and-write/import-notes/#infer-a-table-from-notes) shows.

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

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

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

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

Answer (83 lines)

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

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

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

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

Answer:

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

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

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

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

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

[Keep a folder’s notes out of the table](https://datanotes.dev/guides/read-and-write/import-notes/#keep-a-folders-notes-out-of-the-table-with-exclude) and [Table history and time travel](https://datanotes.dev/guides/history/table-history/) show both at work.

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

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

Answer:

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

```json
{ "table": "classics.actors", "name": "actor" }
```

Answer (48 lines)

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

```json
{ "table": "classics.actor", "catalog": "diary", "schema": "credits" }
```

Answer (52 lines)

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

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

Answer:

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

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

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

**`alter_table`**

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

Answer:

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

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

Refused:

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

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

Answer:

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

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

Answer:

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

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

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

Answer:

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

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

Answer:

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

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

Answer:

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

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

Answer:

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

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

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

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

Answer:

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

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

Answer:

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

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

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

Answer:

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

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

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

Answer:

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

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

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

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

Answer:

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

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

`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](https://datanotes.dev/guides/history/table-history/#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`**

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

Answer:

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

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

Answer:

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

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

Answer:

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

```json
{ "table": "film_empty" }
```

Answer:

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

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

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

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

**`drop_table`**

```json
{ "table": "scratch" }
```

Refused:

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

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

Answer:

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

```json
{ "folder": "Archive/scratch" }
```

Answer:

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

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

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