# Catalogs, schemas and the database

> Group tables in catalogs and schemas, describe the database, query its own schema, set its settings and move its folder.

Tables live in catalogs and schemas, and you name a table `catalog.schema.table`. Each catalog and schema is a folder under `datanotes/`, and `datanotes/metastore.yaml` defines all of them. A short name such as `film` resolves through the database’s default catalog and schema.

The examples on this page keep a diary of classic films in its own catalog, `diary`. You group the tables, describe them, read the database’s own schema and change the settings of the whole database.

## Place tables in catalogs and schemas

`catalog` and `schema` place a table, and default to `main` and `public`. The full name, like `diary.classics.film`, works wherever an operation takes a table. A shorter name works too when only one table matches it.

**`create_table`**

```json
{
  "catalog": "diary",
  "schema": "classics",
  "name": "film",
  "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,
      "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)"
    }
  ],
  "filename": "{title}",
  "description": "One note per film",
  "aliases": ["films I watched"]
}
```

Answer:

```json
{
  "ok": true,
  "op": "create_table",
  "planned_texts": ["datanotes/metastore.yaml"],
  "commits": [
    { "table": "diary.classics.film", "version": 0, "operation": "CREATE TABLE" }
  ],
  "migration_id": "20261012T165235-create_table-create-table-diary-classics-fi"
}
```

**`insert_row`**

```json
{
  "table": "classics.film",
  "values": {
    "title": "Nosferatu",
    "year": 1922,
    "minutes": 94,
    "status": "watched",
    "rating": 5
  }
}
```

Answer:

```json
{
  "ok": true,
  "op": "insert_row",
  "files_created": 1,
  "planned_creates": ["datanotes/diary/classics/film/Nosferatu.md"],
  "commits": [{ "table": "diary.classics.film", "version": 1, "operation": "WRITE" }],
  "migration_id": "20261012T165235-insert_row-insert-into-film"
}
```

## Describe tables, catalogs and schemas

`alter_table` changes how agents find a table, through its `description` and `aliases`. Tags are key/value labels on a table: you set them with `tags` in `create_table` or `alter_table`, and they merge into the tags the table has. `comment_on` says what a catalog, or one of its schemas, holds.

`metastore.yaml` keeps all of it, and you change it only through operations like these, never by hand.

**`alter_table`**

```json
{
  "table": "film",
  "description": "One note per classic film: what I watched and what I thought",
  "aliases": ["films I watched", "film diary"]
}
```

**`alter_table`**

```json
{ "table": "film", "tags": { "domain": "films", "review": "weekly" } }
```

Answer:

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

**`comment_on`**

```json
{ "catalog": "diary", "description": "My diaries: films, for now" }
```

Answer:

```json
{
  "ok": true,
  "op": "comment_on",
  "planned_texts": ["datanotes/metastore.yaml"],
  "migration_id": "20261012T165235-comment_on-comment-on-catalog-diary"
}
```

**`comment_on`**

```json
{
  "catalog": "diary",
  "schema": "classics",
  "description": "Public-domain classic films and their actors"
}
```

Answer:

```json
{
  "ok": true,
  "op": "comment_on",
  "planned_texts": ["datanotes/metastore.yaml"],
  "migration_id": "20261012T165235-comment_on-comment-on-schema-diary-classi"
}
```

`datanotes/metastore.yaml` now reads:

```yaml
# Metastore (Datanotes): catalogs, schemas, tables, columns, views.
id: 01a13107-c58d-771c-bba7-ac5fcb9ba9e6
config:
  history: true


catalogs:
  diary:
    description: "My diaries: films, for now"
    schemas:
      classics:
        description: Public-domain classic films and their actors
        tables:
          film:
            description: "One note per classic film: what I watched and what I thought"
            aliases:
              - films I watched
              - film diary
            tags:
              domain: films
              review: weekly
            options:
              filename: "{title}"
              archive: _archive
            columns:
              - name: id
                type: string
                unique: true
                identity: uuid
                required: true
                dense: true
                description: "Unique id of the row (UUIDv7): set when the row is created, never changes"
              - 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
                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)
```

`describe_database` shows the descriptions and the tags. `list_tables` and `describe_table` show the tags too. `system.information_schema` has the descriptions in the `comment` of `catalogs` and `schemata`, as databases name it, and the tags in `tables`:

**`describe_database`** (no arguments)

Answer:

```json
[
  {
    "catalog": "diary",
    "description": "My diaries: films, for now",
    "schemas": [
      {
        "schema": "classics",
        "description": "Public-domain classic films and their actors",
        "tables": [{ "name": "film", "tags": { "domain": "films", "review": "weekly" } }]
      }
    ]
  }
]
```

A `null` value removes a tag, and `comment_on` with `description: ""` removes a description:

**`alter_table`**

```json
{ "table": "film", "tags": { "review": null } }
```

Answer:

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

**`comment_on`**

```json
{ "catalog": "diary", "schema": "classics", "description": "" }
```

Answer:

```json
{
  "ok": true,
  "op": "comment_on",
  "planned_texts": ["datanotes/metastore.yaml"],
  "migration_id": "20261012T165235-comment_on-remove-the-comment-on-schema-d"
}
```

`clear_tags: true` removes every tag first, so with `tags` the table keeps exactly the tags you pass:

**`alter_table`**

```json
{ "table": "film", "clear_tags": true, "tags": { "area": "films" } }
```

Answer:

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

**`describe_table`**

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

Answer:

```json
{
  "qualified": "diary.classics.film",
  "description": "One note per classic film: what I watched and what I thought",
  "aliases": ["films I watched", "film diary"],
  "tags": { "area": "films" }
}
```

## Describe the whole database

`describe_database` shows the configuration and every catalog, schema and table, with row counts. A table you create as plain `film` goes to the default catalog `main` and schema `public`.

**`describe_database`** (no arguments)

Answer (49 lines)

```json
{
  "root": "datanotes",
  "metastore": "datanotes/metastore.yaml",
  "journal": "store:journal",
  "config": {
    "default_catalog": "main",
    "default_schema": "public",
    "default_partition": "",
    "default_filename": "YYYY-MM-DD[T]HHmm",
    "archive_folder": "_archive",
    "timezone": "",
    "history": true,
    "autolink": true
  },
  "catalogs": [
    {
      "catalog": "main",
      "default": true,
      "schemas": [
        {
          "schema": "public",
          "tables": [
            {
              "name": "film",
              "qualified": "main.public.film",
              "row_count": 1,
              "description": "Classic films I watched or want to watch"
            },
            {
              "name": "watch",
              "qualified": "main.public.watch",
              "row_count": 1,
              "description": "Each time I watch a film",
              "tags": { "area": "films" }
            },
            {
              "name": "review",
              "qualified": "main.public.review",
              "row_count": 0,
              "description": "What I wrote about a film after watching it"
            }
          ],
          "views": []
        }
      ]
    }
  ],
  "bases": []
}
```

## Query the database’s own schema

`system.information_schema` describes the database as tables that you query with `select_rows`. It has these tables:

* **`catalogs`** and **`schemata`**: the catalogs and their schemas
* **`tables`** and **`views`**: the tables and the stored views
* **`columns`**: the columns of every table
* **`check_constraints`**: the CHECK constraints, the rules that every row of a table must satisfy

This query lists the columns of `watch`:

**`select_rows`**

```json
{
  "table": "system.information_schema.columns",
  "columns": ["table_name", "column_name", "data_type", "is_nullable"],
  "where": { "table_name": "watch" }
}
```

Answer (48 lines)

```json
{
  "table": "system.information_schema.columns",
  "total": 6,
  "rows": [
    {
      "path": "system/information_schema/columns/00013",
      "table_name": "watch",
      "column_name": "id",
      "data_type": "string",
      "is_nullable": "NO"
    },
    {
      "path": "system/information_schema/columns/00014",
      "table_name": "watch",
      "column_name": "film",
      "data_type": "link",
      "is_nullable": "NO"
    },
    {
      "path": "system/information_schema/columns/00015",
      "table_name": "watch",
      "column_name": "occurred_at",
      "data_type": "datetime",
      "is_nullable": "NO"
    },
    {
      "path": "system/information_schema/columns/00016",
      "table_name": "watch",
      "column_name": "where",
      "data_type": "enum(cinema,home,outdoor screening)",
      "is_nullable": "YES"
    },
    {
      "path": "system/information_schema/columns/00017",
      "table_name": "watch",
      "column_name": "place",
      "data_type": "string",
      "is_nullable": "YES"
    },
    {
      "path": "system/information_schema/columns/00018",
      "table_name": "watch",
      "column_name": "with",
      "data_type": "string",
      "is_nullable": "YES"
    }
  ]
}
```

## Configure the database settings

`configure_database` sets defaults stored in `metastore.yaml`:

* **`timezone`**: the offset for date-times written without one
* **`default_catalog`** and **`default_schema`**: where new tables go
* **`default_filename`**: the file-name format of new time-named tables
* **`archive_folder`**: the archive folder

**`configure_database`**

```json
{ "timezone": "Europe/Rome", "default_catalog": "diary" }
```

Answer:

```json
{
  "default_catalog": "diary",
  "default_schema": "public",
  "default_partition": "",
  "default_filename": "YYYY-MM-DD[T]HHmm",
  "archive_folder": "_archive",
  "timezone": "Europe/Rome",
  "history": true,
  "autolink": true
}
```

## Move the database folder

A vault holds one database, in one folder: `datanotes/` for a new database. `root` renames or moves that folder, with `metastore.yaml` and every row in it. The folder can be anywhere in the vault, also in a subfolder.

The move is one operation. In it, the engine:

* rewrites every link into the folder, across the vault
* recomputes the generated columns
* moves a table’s `archive` or `exclude` folder inside the database folder

The journal lists the move as `move_database`, which undo refuses. To move the folder back, call `configure_database` again.

**`alter_table`**

```json
{ "table": "diary.classics.film", "exclude": ["datanotes/diary/classics/film/Templates"] }
```

**`configure_database`**

```json
{ "root": "Areas/Diary" }
```

Answer:

```json
{ "root": "Areas/Diary", "metastore": "Areas/Diary/metastore.yaml" }
```

**`list_folder`**

```json
{ "folder": "Areas" }
```

Answer:

```json
{ "folder": "Areas", "folders": ["Areas/Diary"], "files": [] }
```

The `exclude` folder of `film`, set right before the move, moved with the database:

**`describe_table`**

```json
{ "table": "diary.classics.film" }
```

Answer:

```json
{ "exclude": ["Areas/Diary/diary/classics/film/Templates"] }
```

Editors hide folders whose name starts with a dot, so the engine refuses one. It also refuses the vault root, a folder that exists, and a folder inside the database:

**`configure_database`**

```json
{ "root": "Areas/.diary" }
```

Refused:

```text
"root" cannot have a dot segment: "Areas/.diary"
Hint: Editors hide folders whose name starts with a dot, so the rows would vanish from view: name folders without a leading dot, e.g. root: "datanotes".
Docs: https://datanotes.dev/start/how-it-works.md#move-the-vault-or-the-database-folder (offline: read_docs("start/how-it-works#move-the-vault-or-the-database-folder"))
```

This call moves the folder back:

**`configure_database`**

```json
{ "root": "datanotes" }
```

Answer:

```json
{ "root": "datanotes", "metastore": "datanotes/metastore.yaml" }
```

## See the database’s definition in `metastore.yaml`

This is the database of this page as the engine wrote it. `metastore.yaml` holds:

* the catalogs with their descriptions
* the tables with their tags, `bases`, `constraints` and `options`
* the bases, with `ref: self` kept as written
* the settings under `config`

`datanotes/metastore.yaml` now reads:

```yaml
# Metastore (Datanotes): catalogs, schemas, tables, columns, views.
id: 01a13107-c58d-771c-bba7-ac5fcb9ba9e6
config:
  history: true
  default_catalog: diary
  timezone: Europe/Rome
  default_schema: log
  default_partition: YYYY/MM
  default_filename: YYYY-MM-DD HHmm
  archive_folder: _old


catalogs:
  diary:
    description: "My diaries: films, for now"
    schemas:
      classics:
        tables:
          film:
            description: "One note per classic film: what I watched and what I thought"
            aliases:
              - films I watched
              - film diary
            tags:
              area: films
            bases:
              - entry
            constraints:
              rated_when_watched:
                or:
                  - status:
                      ne: watched
                  - rating:
                      exists: true
            options:
              partition: "{year}"
              filename: "{title} ({year})"
              archive: _done
              exclude: datanotes/diary/classics/film/Templates
            fields:
              cut: restored
            columns:
              - name: id
                type: string
                unique: true
                identity: uuid
                required: true
                dense: true
                description: "Unique id of the row (UUIDv7): set when the row is created, never changes"
              - 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
                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)
          genre:
            description: Eras and genres of cinema, as a tree
            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
            bases:
              - tree
          genre_draft:
            description: Eras and genres of cinema, as a tree
            options:
              filename: "{name}"
              archive: _archive
            columns:
              - name: id
                type: string
                unique: true
                identity: uuid
                required: true
                dense: true
                description: "Unique id of the row (UUIDv7): set when the row is created, never changes"
              - name: name
                type: string
                required: true
                description: The name of the era or genre
      credits:
        tables:
          actor:
            description: Actors of the films in the diary
            bases:
              - credited
            options:
              filename: "{name}"
              archive: _archive
            columns:
              - name: id
                type: string
                unique: true
                identity: uuid
                required: true
                dense: true
                description: "Unique id of the row (UUIDv7): set when the row is created, never changes"
              - name: name
                type: string
                required: true
                description: The actor's name, as credited
      log:
        tables:
          watch:
            description: Each time I watch a film
            options:
              partition: "{occurred_at:YYYY/MM}"
              filename: "{occurred_at:YYYY-MM-DD HHmm}"
              archive: _old
            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: diary.classics.film
                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
  sandbox:
    schemas:
      classics:
        tables:
          film_copy:
            description: "One note per classic film: what I watched and what I thought"
            aliases:
              - films I watched
              - film diary
            tags:
              area: films
            bases:
              - entry
            constraints:
              rated_when_watched:
                or:
                  - status:
                      ne: watched
                  - rating:
                      exists: true
            options:
              partition: "{year}"
              filename: "{title} ({year})"
              archive: _done
            fields:
              cut: restored
            columns:
              - name: id
                type: string
                unique: true
                identity: uuid
                required: true
                dense: true
                description: "Unique id of the row (UUIDv7): set when the row is created, never changes"
              - 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
                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)
          film_empty:
            description: "One note per classic film: what I watched and what I thought"
            aliases:
              - films I watched
              - film diary
            tags:
              area: films
            bases:
              - entry
            constraints:
              rated_when_watched:
                or:
                  - status:
                      ne: watched
                  - rating:
                      exists: true
            options:
              partition: "{year}"
              filename: "{title} ({year})"
              archive: _done
            fields:
              cut: restored
            columns:
              - name: id
                type: string
                unique: true
                identity: uuid
                required: true
                dense: true
                description: "Unique id of the row (UUIDv7): set when the row is created, never changes"
              - 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
                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)
bases:
  entry:
    description: A note of the film diary
    columns:
      - name: added_on
        type: date
        description: When I added it to the diary
  credited:
    description: Someone in the credits of a film
    bases:
      - entry
    columns:
      - name: films
        type: list
        items: link
        ref: diary.classics.film
        description: The films they’re credited in
  tree:
    description: Rows arranged in a tree
    columns:
      - name: parent
        type: link
        ref: self
        hierarchy: true
        description: The row above this one
```