Multicatalog: derive cat_<id> scope at resolution time instead of persisting it in schema.path

[shefeek-jinnah]

Summary

Multicatalog currently encodes the catalog scope (cat_<id>) into ducklake_schema.path (e.g. schema.path = "cat_7/main"). This overloads a DuckLake spec field with a catalog-partitioning concern, and it creates a fragile invariant — "every multicatalog schema.path must carry cat_<id>" — that not every schema-creation path upholds. When the invariant is violated, reads fail with NotFound.

This issue proposes deriving the cat_<id> scope at resolution time from catalog_id (which the reader already knows) and keeping schema.path canonical (just the schema name), so the invariant can't drift.

Background: how scoping works today

• The writer physically scopes every multicatalog data/delete file to data_path/cat_<id>/<schema>/<table>/… — unconditionally (table_writer.rs, the scoped_base = join(base, cat_<id>) blocks in begin_write and write_delete_file).
• The scope is also persisted into schema.path as cat_<id>/<schema> (metadata_writer_postgres.rs, the schema_was_created insert), so that the standard resolution chain data_path + schema.path + table.path + file.path lands on the physical location.

The bug class

The reader resolves a relative file as data_path + schema.path + table.path + file.path. That only works if schema.path carries the cat_<id> scope. It does for freshly-created schemas, but not for:

• the default schema, and
• schemas created via get_or_create_schema(name, None) (stores path = name, unscoped),
• (and any schema created by tooling that doesn't route through the scoping insert).

For those, schema.path = "<schema>", so the reader looks under data_path/<schema>/<table>/… while the file physically sits at data_path/cat_<id>/<schema>/<table>/… → NotFound.

Root-cause analysis

The physical layout is uniform: because the writer always scopes to cat_<id>, every multicatalog file already lives under data_path/cat_<id>/…. So the failure is a metadata-only mismatch — the files are in the right place; only schema.path is sometimes wrong.

The deeper problem is that cat_<id> (a multicatalog infrastructure concern — not part of DuckLake's data model, which is one catalog per metadata store) is stored in schema.path (a DuckLake schema-level property). This couples two orthogonal things and makes correctness depend on an invariant that must be re-established by every schema-creation path. The bug is a symptom of that coupling.

What the DuckLake spec says

Research into the DuckLake spec, the DuckDB ducklake reference implementation, and the 0.2 release supports keeping schema.path canonical and paths relative:

• Relative by default. "By default, all paths written by DuckLake are relative paths." — paths docs. Absolute (path_is_relative = false) is reserved for files outside data_path.
• schema.path is the schema's own directory, relative to the catalog data_path — ducklake_schema spec. It is a schema-level property, not a place to encode catalog partitioning.
• Nested resolution chain introduced in DuckLake 0.2: a data file is relative to its table, the table relative to its schema, the schema relative to data_path — duckdb/ducklake#126, 0.2 announcement, ducklake_data_file spec.
• The reference impl decides relative-vs-absolute per file via GetRelativePath (ducklake_metadata_manager.cpp): it stores a file relative to its resolved table path when the physical path is under it, and absolute only otherwise (e.g. external files). It does not bake infrastructure prefixes into schema.path.

Takeaways: DuckLake favours relative paths for portability/relocatability, treats schema.path as a canonical schema directory, and uses per-file absolute purely as an escape hatch for files outside the resolvable tree.

Proposal (derive scope at resolution time)

• Reader: compute the resolution base as data_path/cat_<id> for multicatalog (a dedicated resolution_base() accessor; do not overload get_data_path(), which is also used for reporting/round-trip). Everything downstream (table, file, delete, encryption, vacuum) already resolves relative to that base, so no per-resolution-site changes are needed.
• Writer: base already carries cat_<id> via the same accessor → drop the explicit cat_<id> join; store schema.path as the plain schema name; keep files relative (bare filenames).
• Result: schema.path becomes canonical and spec-aligned; the cat_<id> scope can never drift because it's computed from catalog_id, not stored-and-hoped; the whole bug class disappears by construction.

Migration (metadata-only)

Because the physical layout is already uniform under cat_<id>, the migration is metadata-only — no bytes move: strip the leading cat_<id>/ from each affected ducklake_schema.path (idempotent, fits the existing migrate_*-on-open pattern). Data/delete file rows already store relative bare filenames, so they need no migration.

Escape hatch preserved

If external/attached files (outside data_path) are supported later, DuckLake's own mechanism — per-file path_is_relative = false — remains available for those. This proposal keeps that escape hatch; it just stops using it for normal in-base data.

Why this over per-file absolute paths (see #158)

#158 fixes the symptom by registering multicatalog files with absolute paths. That works, but:

• it flips the multicatalog default to absolute, embedding the data_path root in every row and losing relocatability (moving the data root breaks every absolute row);
• it diverges from DuckLake's relative-by-default behavior; and
• it treats a metadata mismatch as if it were a physical-layout problem.

Deriving the scope at resolution time addresses the root cause instead, keeps everything relative/relocatable, and reduces code.

Open question to confirm before implementing

Has any multicatalog file ever been written without the cat_<id> prefix (an older unscoped writer, an import, or single-catalog data sharing the same data_path)? The writer has always scoped physically, so this should be "no" — in which case the migration is purely metadata. If genuinely out-of-cat_<id> files exist, those specific files would need the per-file absolute escape hatch (which stays available).