Catalogs, schemas and the database

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

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

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

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

Answer:

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

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

alter_table

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

Answer:

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

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

Answer:

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

comment_on

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

Answer:

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

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

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

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

Answer:

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

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

Answer:

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

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

Answer:

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

{ "table": "film" }

Answer:

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

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

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

Answer (48 lines)

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

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

Answer:

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

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

configure_database

{ "root": "Areas/Diary" }

Answer:

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

list_folder

{ "folder": "Areas" }

Answer:

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

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

describe_table

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

Answer:

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

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

Refused:

"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

{ "root": "datanotes" }

Answer:

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

# 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