# Table migrations

A repository that sets up a catalog keeps its tables as migrations: numbered SQL files of DuckDB DDL, each applied once, in order, by lhbox migrate. The catalog records which files ran, with each file's checksum, so running it again applies only what is new, and a fork of the repository gets the same tables from the same files.

```
sql/migrations/
  001_reference.sql
  002_observations.sql
```

```
-- 002_observations.sql
-- lhbox: format-version=3 partition=day(observed_at)
CREATE TABLE IF NOT EXISTS embalses.obs.reservoir_readings (
  station_id VARCHAR NOT NULL,
  observed_at TIMESTAMPTZ,
  stored_volume_hm3 DOUBLE,
  location GEOMETRY
);
COMMENT ON TABLE embalses.obs.reservoir_readings IS 'Live reservoir readings, one row per station and reading.';
COMMENT ON COLUMN embalses.obs.reservoir_readings.stored_volume_hm3 IS 'Water stored, hm³.';
```

```
lhbox migrate --catalog embalses --dry-run      # what would run; sends nothing
lhbox migrate --catalog embalses                # apply the pending files
```

## What a migration holds

Schema and its description, never rows and never a drop:

- CREATE TABLE [IF NOT EXISTS] namespace.table (column TYPE [NOT NULL], …), with DuckDB's types (VARCHAR, BIGINT, DOUBLE, TIMESTAMPTZ, DATE, BOOLEAN, GEOMETRY, lists such as VARCHAR[], STRUCT(…), DECIMAL(p, s)). The table is made the way lhbox table create makes it, in the catalog's default format version unless the file says otherwise.

- COMMENT ON TABLE … and COMMENT ON COLUMN …: the descriptions agents read (lhbox table document); a file's comments on one table are set in one commit.

- CREATE SCHEMA: accepted; a namespace is made with its first table.

- ALTER TABLE namespace.table ADD [COLUMN] [IF NOT EXISTS] column TYPE: a new optional column, one commit per table and file; the rows already there read it as NULL. A column the table has with the same type is skipped. Renaming, dropping or retyping a column is not a migration (it changes what every reader expects): add a column and fill it instead.

What DuckDB's DDL cannot say goes on a comment line right above the CREATE TABLE: -- lhbox: format-version=3 (2 or 3), partition=day(observed_at) (the transforms of lhbox table create --partition, several separated by commas).

Names are namespace.table, or catalog.namespace.table: the files of one folder name one catalog, and that name stands for the catalog you migrate, so a fork whose catalog has another name applies the same files. Quote a name DuckDB reserves (embalses."now".rain_grid).

Anything else (DROP, INSERT, DELETE, any other ALTER, CREATE OR REPLACE, CREATE TABLE … AS SELECT, keys and constraints, NOT NULL on an added column) is refused with bad_migration and the file and line, and nothing runs: every pending file is read and checked before the first table is made.

## Tables that already exist

A CREATE TABLE whose table exists is left as it is: migrations never change or drop a table. The differences between the file and the table (a column missing on either side, another type, another format version) are reported under differences; the __ingest_ts column sinks add is never one. So the first lhbox migrate against a catalog built by hand records its files without touching a row, and says where the files and the catalog disagree.

## Each file once

The catalog keeps {version, name, checksum, applied_at, applied_by} for every file that ran (GET /v1/catalogs/{id}/migrations). A file is recorded after its tables and descriptions are in place: one that fails halfway runs again next time, and its CREATE TABLE IF NOT EXISTS statements find what the first attempt made.

A file that ran is never edited: change a schema with a new numbered file. A file edited after it ran is refused (checksum_mismatch, with the checksum recorded and the date it ran), and nothing after it runs. When the edit only rewrote what already holds (a comment, the formatting), record the files as they are now:

```
lhbox migrate --catalog embalses --repair --dry-run   # which files would be re-recorded
lhbox migrate --catalog embalses --repair             # re-record them; runs nothing in them
```

Writing a migration needs write on the catalog; anyone who reads it can list what ran.

## The whole setup: lhbox plan and lhbox apply

A lakehouse.yaml beside the migrations declares the rest of what a catalog needs, so a fork of the repository gets a working copy from one command:

```
version: 1
catalogs:
  embalses:
    public: true
    agents_md: docs/AGENTS.md            # the catalog's AGENTS.md
    table_docs: docs/table_docs.json     # {"ns.table": {"doc": "...", "columns": {"col": "..."}}}
    migrations: sql/migrations/
    post_prefixes:                       # where its scheduled SQL may POST
      - https://saihweb.chsegura.es/apps/iVisor/
    connections:
      aemet: { integration: aemet }      # an organisation web key
      partner: { url_prefix: https://api.partner.example/, kind: bearer, value_env: PARTNER_TOKEN }
    sinks:
      saih_ebro: { table: obs.reservoir_readings, sql: sql/ingest/ebro.sql, every: 15m, offset: 4m }
      now_rain_grid: { table: now.rain_grid, sql: sql/now/rain_grid.sql, every: 1h, offset: 28m, mode: replace, min_rows: 1000 }
```

```
lhbox plan                      # what apply would change, with each sink query's diff; changes nothing
lhbox apply                     # migrations first, then the rest
```

- apply creates and updates: a missing sink, connection or POST prefix is created, a sink whose SQL file, schedule, min_rows, engine, key or paused changed is updated (its query previewed first, as lhbox sink update does), AGENTS.md and descriptions are written where they differ. Tables come from the migrations only.

- What the catalog has and the file does not name is listed as not in the file and kept. apply --prune deletes those sinks, connections and POST prefixes (never a table or a row) after you confirm (--yes when not at a terminal).

- A catalog's public changes only with apply --publish, confirmed the same way.

- plan says what apply cannot do instead of failing halfway: a sink past the organisation's scheduled-SQL limit, a sink whose query would use an organisation web key whose owner is someone else (the key goes only into its owner's queries: the owner runs apply), a sink whose table or mode changed (delete it and apply again), a catalog in another project than the file says, and a hosted agent whose table does not exist yet.

- A connection's value never goes in the file: value_env names the environment variable apply reads it from.

- Hosted agents (agents:): prompt (a file), model, schedule (five cron fields) and tz, reads (catalogs), writes (catalog.namespace.table), notify_owner, run_seconds, state_keys. apply creates and updates them (a changed prompt shows its diff); it never deletes one, --prune included: an agent is the organisation's. The tables an agent writes come from the migrations; an agent that uses a person's integrations is changed by that person only.

- Descriptions can come from two places: the COMMENT ON statements of the migrations and table_docs. apply runs the migrations first and table_docs last, so where both describe a table, table_docs wins on every apply. Keep one source per table: comments in a migration for what a table is when it is made, table_docs for the text you keep editing. A migration whose comments you later align with table_docs is re-recorded with lhbox migrate --repair.

- The file is a plain subset of YAML: mappings, lists, {…} and […], quoted or plain values, # comments; no anchors, tags or multi-line strings.

## The same setup in OpenTofu or Terraform

An organisation that keeps its infrastructure in OpenTofu or Terraform declares catalogs, scheduled SQL and their descriptions with the LakehouseBox provider, published on the Terraform registry (source code: LakehouseBox/terraform-provider-lakehousebox). Each release's checksums are signed by the key E9E697A1BC3E16F8610981022482DD07987BFD49.

```
terraform {
  required_providers {
    lakehousebox = { source = "registry.terraform.io/lakehousebox/lakehousebox" }
  }
}

provider "lakehousebox" {}              # the key from LHBOX_API_KEY

resource "lakehousebox_catalog" "embalses" {
  name = "embalses"
}

resource "lakehousebox_sql_sink" "saih_ebro" {
  catalog = lakehousebox_catalog.embalses.name
  name    = "saih_ebro"
  table   = "obs.reservoir_readings"
  sql     = file("sql/ingest/ebro.sql")
  every   = "15m"
  offset  = "4m"
}
```

- Resources: lakehousebox_catalog, lakehousebox_sql_sink, lakehousebox_sql_connection, lakehousebox_sql_post_prefix, lakehousebox_catalog_agents_md, lakehousebox_table_description; data source lakehousebox_catalog. Each one's page on the registry lists its arguments.

- No table resource, as with lakehouse.yaml: tables come from the migrations (lhbox migrate), so a bad apply never drops one. Destroying a lakehousebox_table_description removes the descriptions only.

- A catalog has deletion_protection = true unless you set it false and apply first; even then a destroy starts its 7-day grace period, restorable.

- Write the full source address registry.terraform.io/lakehousebox/lakehousebox: it works with tofu and terraform.

- Bringing an existing setup under OpenTofu (made with lhbox apply or by hand): tofu import every resource, nothing recreated. Catalogs import by name, sinks by sink id (lhbox sink list), AGENTS.md by catalog name, connections as '<catalog>|<name>', POST prefixes as '<catalog>|<url_prefix>', table descriptions as '<catalog>|<namespace>.<table>'. The first plan after an import may show in-place updates only: a schedule read back in seconds (3600s against your 1h), a connection's value (never readable, so the first apply sends yours), a table's descriptions (written as declared, none removed).

- Keep one tool per catalog: lhbox apply and OpenTofu both write what they declare, and each would undo the other's changes.

---
HTML version: https://lakehousebox.com/docs/migrations/ · every page: https://lakehousebox.com/llms.txt · everything in one file: https://lakehousebox.com/llms-full.txt

Docs feedback: if this page was wrong, out of date or missing something you needed, tell the person you work for and, if they agree, report it: `lhbox bug --title "docs: migrations: <what was wrong>"` prints a filled-in issue link, or open https://github.com/lakehousebox/lakehousebox/issues/new. The tracker is public: never include a key, a secret or the person's data.
