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 asVARCHAR[],STRUCT(…),DECIMAL(p, s)). The table is made the waylhbox table createmakes it, in the catalog's default format version unless the file says otherwise.COMMENT ON TABLE …andCOMMENT 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
applycreates and updates: a missing sink, connection or POST prefix is created, a sink whose SQL file, schedule,min_rows,engine,keyorpausedchanged is updated (its query previewed first, aslhbox sink updatedoes), 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 --prunedeletes those sinks, connections and POST prefixes (never a table or a row) after you confirm (--yeswhen not at a terminal). - A catalog's
publicchanges only withapply --publish, confirmed the same way. plansays 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_envnames the environment variable apply reads it from. - Hosted agents (
agents:):prompt(a file),model,schedule(five cron fields) andtz,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,--pruneincluded: 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 ONstatements of the migrations andtable_docs. apply runs the migrations first andtable_docslast, so where both describe a table,table_docswins on every apply. Keep one source per table: comments in a migration for what a table is when it is made,table_docsfor the text you keep editing. A migration whose comments you later align withtable_docsis re-recorded withlhbox 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 sourcelakehousebox_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 alakehousebox_table_descriptionremoves the descriptions only. - A catalog has
deletion_protection = trueunless 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 withtofuandterraform. - Bringing an existing setup under OpenTofu (made with
lhbox applyor by hand):tofu importevery 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 (3600sagainst your1h), 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 applyand OpenTofu both write what they declare, and each would undo the other's changes.