Schema operations (DDL)

Operations that define the database: tables, columns, constraints, bases and views. The overview lists every operation by kind. The MCP server, datanotes mcp, offers each operation as an MCP tool of the same name, and engineTools() of datanotes/mcp gives the same definitions to your own MCP server. DatanotesClient.call(name, args) runs one from code. Over HTTP each one is POST /datanotes/operations/<name>/, with its arguments as the JSON body: the HTTP API reference lists them.

Every write is validated. Writes to rows and to the schema are journaled and undone with undo_migration; configure_database, the webhook operations, vacuum_table and sync_semantic_index are not journaled.

create_table

Create Table · write

Create a table: name, in catalog and schema (default: the database’s defaults); the definition goes in metastore.yaml, the rows in <root>/<catalog>/<schema>/<name>/. Names: lowercase letters, digits, _ (no dots: pass the catalog and the schema as their own arguments).

• description (required): what a row of the table records. Every column needs its own description too, what it holds: the table is refused otherwise, with every missing one listed. Descriptions are how agents and people understand the schema, so write them for a reader who never saw the notes. The engine writes those of id and of identity “uuid” / “created” columns.
• columns: specs like {"name": "kind", "type": "enum", "values": ["a","b"], "required": true, "description": "What kind of entry the row is"}. Types: string, integer, number, boolean, date, datetime, enum, link, list (+ items), json. Attributes: required, default ("{now}"/"{today}"), min, max, step, values, ordered (enum in order: filters/avg use the position), ref (link target, full catalog.schema.table or “self”), unique, label, description, aliases, generated (e.g. "date(occurred_at)", "{name} {surname}"), identity (“filename” | “created”), vocabulary (allowed values = names of another table’s rows), hierarchy: true (one parent link to its own table), dense (written on every row), inverse on a link column: the column of the ref table that links back (friends ↔ friends, children ↔ parents, partner ↔ partner); writes keep both sides in step.
• time_column: rows named by that date/datetime; otherwise filename (e.g. "{name}").
• The table’s options, as alter_table names them: aliases (how agents find it, with the description), partition, exclude, archive, history (keep its versions; default: the database’s setting), autolink_target (true / false: may automatic links link its rows? default: yes, unless its rows are named by a date or a time, as with time_column).
• row_template: markdown body of new rows. Shared columns come from bases: set_table_bases.
• tags: key/value labels of the table, strings ({"domain": "health"}); list_tables and describe_table return them, alter_table changes them. Enum columns: values and, for each, its meaning in value_descriptions ({"stated": "the user said it", …}): describe_table shows them and a refused value lists them. Generated link columns: generated: "link({occurred_at:YYYY-MM-DD})" with ref = the days table links each row to the row of ref named by the template (its folder read from the schema; the target may not exist yet). Filtered link targets (link columns): ref_where (a where every target must match) and ref_by (a column of the row picks the target level: {"column": "kind", "levels": ["area", "project", "workstream"]} or {"column": "kind", "map": {"area": null, "project": ["area"]}}; null = top level, no link; target_column when the target names it differently: an enum target column must have every value the map allows, a string one takes any value). A link to no row of the table, or to the row itself, is refused too. Existing rows that break them refuse the change; list_link_targets lists the valid targets of a row.

The schema changes only through operations like this one: never edit metastore.yaml by hand (no validation, no undo).

Examples: Catalogs, schemas and the database, Tables, Columns and types, Bases, Relations and hierarchies, Write and move rows, Automatic links, Import existing notes, Table history and time travel.

Parameter	Type	Required	Default	Description
name	string	yes
description	string \	null		null
columns	object[]	yes
catalog	string \	null		null
schema	string \	null		null
time_column	string \	null		null
filename	string \	null		null
partition	string \	null		null
exclude	string[] \	null		null
archive	string \	null		null
aliases	string[] \	null		null
history	boolean \	null		null
autolink_target	boolean \	null		null
row_template	string \	null		null
fields	object \	null		null
tags	object \	null		null
dry_run	boolean		false

drop_table

Drop Table · destructive write

Drop a table. Refused while it has rows unless mode: "archive", which removes the definition from metastore.yaml and moves all rows to the table’s archive folder (nothing is hard-deleted; undo restores). The transaction log is kept: restore_table can bring it back. Refused while a CHECK that follows links, or a ref_where / ref_by, of another table reads this table through a link (link targets are not rewritten): the conflicts name them; drop or change them first.

The schema changes only through operations like this one: never edit metastore.yaml by hand (no validation, no undo).

Examples: Tables.

Parameter	Type	Required	Default	Description
table	string	yes
mode	"archive" \	null		null
dry_run	boolean		false

alter_table

Alter Table · destructive write

Alter a table: move it (catalog, schema), rename it (name), change partition / filename patterns, description (changes, never goes: “” is refused), aliases, exclude folders, row_template (“” removes it), history (true | false | “default”), autolink_target (true | false | “default”: may automatic links link its rows? default: yes, unless its rows are named by a date or a time) or tags (key/value labels merged into the table’s, e.g. {"domain": "health"}; a null value removes that tag; clear_tags: true removes every tag first, so with tags the table keeps exactly those), archive (folder name inside the table folder) or fields (untyped fields, name → default; a null value removes one). Rows, archive and transaction log follow; links are updated; renamed rows keep their old name in aliases. A change of metadata only (tags, description, aliases, history, autolink_target, row_template, fields) rewrites the table entry alone: no row is moved or rewritten. Refused when a row cannot fill a pattern. Renaming or moving the table is refused while a CHECK that follows links, or a ref_where / ref_by, of another table reads it through a link (link targets are not rewritten): the conflicts name them. Use dry_run to see planned_moves; undo with undo_migration.

The schema changes only through operations like this one: never edit metastore.yaml by hand (no validation, no undo).

Examples: Catalogs, schemas and the database, Tables, Automatic links, Import existing notes, Table history and time travel.

Parameter	Type	Required	Default	Description
table	string	yes
catalog	string \	null		null
schema	string \	null		null
name	string \	null		null
partition	string \	null		null
filename	string \	null		null
description	string \	null		null
aliases	string[] \	null		null
history	boolean \	"default" \	null
autolink_target	boolean \	"default" \	null
exclude	string[] \	null		null
row_template	string \	null		null
tags	object \	null		null
clear_tags	boolean		false	Remove every tag first (with tags: the table keeps exactly those).
archive	string \	null		null
fields	object \	null		null
dry_run	boolean		false

clone_table

Clone Table · write

CREATE TABLE target CLONE source: same definition plus independent copies of its rows (deep clone), optionally as of a version / timestamp. copy_rows: false copies the definition only (CREATE TABLE LIKE). target: catalog.schema.table, schema.table (in the source’s catalog) or a name (source’s catalog and schema). Useful for experiments, e.g. a sandbox copy in the agent catalog. The clone is a new table, so it needs the descriptions create_table does: a source without them is refused, naming what to describe first (alter_table, alter_column). A past version lacking one gets the one the source has now.

The schema changes only through operations like this one: never edit metastore.yaml by hand (no validation, no undo).

Examples: Tables, Bases, Table history and time travel.

Parameter	Type	Required	Default	Description
source	string	yes
target	string	yes
version	integer \	null		null
timestamp	string \	null		null
copy_rows	boolean		true	Copy the rows (default); false copies the definition only (CREATE TABLE LIKE).
dry_run	boolean		false

add_column

Add Column · write

Add a typed column. Sparse by default (written only on rows that have a value — use for metrics, e.g. {"name": "metric_mood", "type": "integer", "min": 1, "max": 5, "label": "Mood", "aliases": ["mood"], "description": "Mood of the day, 1 = bad, 5 = great"}). description is required: what the column holds, for the agents and people who read the schema (the engine writes it for identity “uuid” / “created” columns). dense: true in the spec adds it to every row with its default. Call describe_table first: never add a column whose meaning an existing column (name/label/aliases/description) already covers. Column names are permanent identifiers; only rename_column changes them. Enum columns: values and, for each, its meaning in value_descriptions ({"stated": "the user said it", …}): describe_table shows them and a refused value lists them. Generated link columns: generated: "link({occurred_at:YYYY-MM-DD})" with ref = the days table links each row to the row of ref named by the template (its folder read from the schema; the target may not exist yet). Filtered link targets (link columns): ref_where (a where every target must match) and ref_by (a column of the row picks the target level: {"column": "kind", "levels": ["area", "project", "workstream"]} or {"column": "kind", "map": {"area": null, "project": ["area"]}}; null = top level, no link; target_column when the target names it differently: an enum target column must have every value the map allows, a string one takes any value). A link to no row of the table, or to the row itself, is refused too. Existing rows that break them refuse the change; list_link_targets lists the valid targets of a row.

The schema changes only through operations like this one: never edit metastore.yaml by hand (no validation, no undo).

Examples: Columns and types, Relations and hierarchies, Import existing notes.

Parameter	Type	Required	Default	Description
table	string	yes
column	object	yes		The column’s spec: name, type, description and its other attributes; dense: true writes it on every row.
dry_run	boolean		false

rename_column

Rename Column · destructive write

Rename a column in metastore.yaml and on every row. Refused if to_column is already a column of the table; a row that has a stray to_column key with a different value is a conflict, and nothing is written. CHECKs, ref_where and ref_by that read the column (in this table, or through a link from another table: parent.kind) follow the new name. Views and note text that mention the old name are not rewritten.

The schema changes only through operations like this one: never edit metastore.yaml by hand (no validation, no undo).

Examples: Columns and types.

Parameter	Type	Required	Default	Description
table	string	yes
from_column	string	yes
to_column	string	yes
dry_run	boolean		false

drop_column

Drop Column · destructive write

Remove a column from the schema and every row. Previous values are journaled; revert with undo_migration. Refused while a CHECK, ref_where or ref_by of any table reads the column (directly or through a link): the conflicts name them; drop or change them first.

The schema changes only through operations like this one: never edit metastore.yaml by hand (no validation, no undo).

Examples: Columns and types.

Parameter	Type	Required	Default	Description
table	string	yes
column	string	yes		The column’s name.
dry_run	boolean		false

alter_column

Alter Column · destructive write

Change a column’s attributes (type, min/max/step, values, required, default, label, aliases, description, note, ref_where, ref_by (see list_link_targets); null removes an attribute, except description, which changes but never goes (null or “” is refused); dense true/false). Refused with the list of violating rows when existing values do not satisfy the new definition. A different scale means a different column: add a new one instead of rescaling. To rename one enum value use rename_value. Dropping an enum value that a ref_by (of any table) allows as a target level is refused, naming it.

The schema changes only through operations like this one: never edit metastore.yaml by hand (no validation, no undo).

Examples: Tables, Columns and types, Relations and hierarchies.

Parameter	Type	Required	Default	Description
table	string	yes
column	string	yes		The column’s name.
changes	object	yes
dry_run	boolean		false

rename_value

Rename Value · destructive write

Rename one value of an enum column (e.g. high → very high) in metastore.yaml and every row, keeping its position, so an ordered scale is unchanged. CHECKs and ref_where conditions on the column (also through a link from another table) and ref_by levels / maps that name the value use the new one. Note text (queries, prose) is not rewritten. Refused if to_value already exists. Undo with undo_migration. Adding or removing classes changes the scale: create a new table instead.

The schema changes only through operations like this one: never edit metastore.yaml by hand (no validation, no undo).

Examples: Columns and types.

Parameter	Type	Required	Default	Description
table	string	yes
column	string	yes
from_value	string	yes
to_value	string	yes
dry_run	boolean		false

comment_on

Comment On · destructive write

COMMENT ON CATALOG / SCHEMA: set the description of a catalog (catalog alone) or of one of its schemas (catalog and schema) in metastore.yaml: what it holds, so agents pick the right place. description “” removes it. An unknown catalog or schema is refused with the existing ones. describe_database and system.information_schema.catalogs / schemata show it. Undo with undo_migration. A table’s description: alter_table.

The schema changes only through operations like this one: never edit metastore.yaml by hand (no validation, no undo).

Examples: Catalogs, schemas and the database.

Parameter	Type	Required	Default	Description
catalog	string	yes
schema	string \	null		null
description	string	yes		The new description; “” removes it.
dry_run	boolean		false

define_base

Define Base · destructive write

Create or replace a base: a named set of columns (same spec as create_table) that tables inherit, like a parent class (bases in metastore.yaml). Tables using it get the columns with the same name and meaning; ref: "self" means the inheriting table (e.g. a parent column with hierarchy: true). bases builds on other bases. Descriptions are required, as in create_table: description says what the tables of the base share, and each column has its description (a column given without one keeps the one the base had for it). Replacing a base changes every inheriting table: rename_columns ({"old": "new"}) renames the column on their rows, drop_columns removes it from their rows; dropping a column without listing it is refused. A renamed column is followed into the CHECKs and ref_where / ref_by that read it (the base’s own included); dropping one they read is refused, naming them. All affected rows are validated before any write, inherited ref_where / ref_by and CHECKs that follow links included. Enum columns: values and, for each, its meaning in value_descriptions ({"stated": "the user said it", …}): describe_table shows them and a refused value lists them.

The schema changes only through operations like this one: never edit metastore.yaml by hand (no validation, no undo).

Examples: Bases.

Parameter	Type	Required	Default	Description
name	string	yes
columns	object[]	yes
bases	string[] \	null		null
description	string \	null		null
rename_columns	object \	null		null
drop_columns	string[] \	null		null
dry_run	boolean		false

set_table_bases

Set Table Bases · destructive write

Set the bases a table inherits from (bases, in order; [] removes them). Inherited columns come after id and before the table’s own columns. An own column with the name of an inherited one is replaced by the inherited definition (rows must fit it, its ref_where / ref_by included); a table may only restyle an inherited column (label, description, aliases, note, dense) with alter_column. Dense and generated/identity columns are filled on existing rows.

The schema changes only through operations like this one: never edit metastore.yaml by hand (no validation, no undo).

Examples: Bases.

Parameter	Type	Required	Default	Description
table	string	yes
bases	string[]	yes
dry_run	boolean		false

add_constraint

Add Constraint · write

ALTER TABLE ADD CONSTRAINT name CHECK: check is a where filter every row must match, e.g. {"value": {"gte": 1, "lte": 5}}. A condition on a column the row leaves empty passes, like SQL; inside or / and / not an empty value is tested as it is ({"exists": true} there fails). A key may follow a link column to the row it names, as in queries: {"parent.kind": "project"} (further dots follow further links). An empty link passes at the top level and fails inside or / and / not; only the first link of a longer path is skipped when empty (parent.parent.kind fails for a row whose parent has no parent). A link to a row that does not exist fails; on a list of links every linked row must match. not turns that into a pass: {"not": {"parent.kind": "area"}} passes a link to a missing row, so add "parent.kind": {"exists": true} to require a real one. Links resolve as in the vault (by path, from the row’s note: same-named rows in two folders stay apart); renaming or moving a linked row keeps them (they follow it). Refused while existing rows violate it (they are listed); then enforced on every write, by the same rule. A write that changes a linked row re-checks the rows that link to it and is refused, listing them, when it breaks them, naming the changed row (in a batch, re-link them first: each step is checked on the vault the earlier ones left); the inverse links a write adds, undo_migration and restore_table are checked too. rename_column / rename_value follow into CHECKs; drop_column of a column a CHECK reads is refused.

The schema changes only through operations like this one: never edit metastore.yaml by hand (no validation, no undo).

Examples: CHECK constraints.

Parameter	Type	Required	Default	Description
table	string	yes
name	string	yes
check	object	yes
dry_run	boolean		false

drop_constraint

Drop Constraint · destructive write

Remove the CHECK constraint name from table.

The schema changes only through operations like this one: never edit metastore.yaml by hand (no validation, no undo).

Examples: CHECK constraints.

Parameter	Type	Required	Default	Description
table	string	yes
name	string	yes
dry_run	boolean		false

create_view

Create View · destructive write

Create (or replace) a view: a named select stored in metastore.yaml. Name it with the prefix vw_ (e.g. vw_open_tasks): views share names and folders with tables. query takes the select_rows parameters (table, columns, where, order_by, limit, join, group_by, aggregates, expand; relative dates keep it current) and must run now. Or query: {union: [{table, columns, where}, …], columns, where, order_by, limit, expand} stacks the rows of several tables (UNION ALL, each row tagged _source). Its filters may read the note the view is shown for: {this.<property>} ({this.date+1d}, {this.date-1w} shift a date), {this.link}, {this.name}, {this.path}, filled in from select_rows this_note (one view serves every daily note, every project note). description and aliases let agents find it; labels are column headings. Views are selected with select_rows(table=<view>), which may narrow them further (where, columns, order_by, limit; a where operator the view already has on a column replaces it). A view writes no note: it lives only in metastore.yaml.

The schema changes only through operations like this one: never edit metastore.yaml by hand (no validation, no undo).

Examples: Views.

Parameter	Type	Required	Default	Description
name	string	yes
query	object	yes
catalog	string \	null		null
schema	string \	null		null
description	string \	null		null
aliases	string[] \	null		null
labels	object \	null		null
replace	boolean		false
dry_run	boolean		false

drop_view

Drop View · destructive write

Remove a view from metastore.yaml (undo with undo_migration).

The schema changes only through operations like this one: never edit metastore.yaml by hand (no validation, no undo).

Examples: Views.

Parameter	Type	Required	Default	Description
view	string	yes
dry_run	boolean		false