DuckLake

The storage layer of the famulus reference: catalog, placement, export, migration, maintenance

Published

October 14, 2026

All famulus reference collections are stored in one DuckLake. This page describes the catalog, the placement of the data on PDAI-1, the schemas, the export and migration procedures, the maintenance runbook and how to read the lake. Concept and API: Famulus reference. Overview: The Famulus universe.

Catalog	`/Volumes/PDAI-1/pdai-collections/_lake/famulus.ducklake` (data-mounts key `famulus_lake_catalog`)
Read replica	`/Volumes/PDAI-1/pdai-collections/_lake/famulus.read.ducklake`
Query trace	`/Volumes/PDAI-1/pdai-collections/_lake/famulus_reference_traces.duckdb` (key `famulus_lake_traces`; outside the lake)
DATA_PATH	`/Volumes/PDAI-1/pdai-collections/` (the collection root)
Writer	mac-studio only
Code	`pdai/famulus-reference/src/famulus_reference/` (`lake.py`, `build/export_lake.py`, `migrate/attach_legacy.py`, `maintain.py`)

Why DuckLake

Property	Use in the famulus reference
Catalog database + Parquet data files	the catalog holds tables, schemas, file lists and snapshots; the data is immutable Parquet (zstd) inside each collection folder
Snapshots	every committed write creates a snapshot id; one export = one snapshot
Time travel	a citation records its snapshot id; the cited state can be read again as long as the snapshot is retained
SQL access from DuckDB	existing DuckDB code (famulus-proof, ai-query, landings) reads the lake with `ATTACH 'ducklake:…'` and unchanged SQL
Schemas	one schema per collection; lake-wide union views in `main`

Catalog backend

Option	Decision
DuckDB file	chosen. No database server; the catalog is one file next to the data
PostgreSQL / SQLite catalog	not used; multiple writers are not needed
Writer	single writer, mac-studio (`config.WRITE_HOSTS`). On any other host a write to the production catalog raises `WriteRefused`. Clients queue lake jobs via `~/syncthink/queue/` (allow-list `platform/config/syncthink_jobs.yaml`: `famulus-lake-export`, `famulus-lake-migrate`, `famulus-lake-maintain-dry`)

Read replica

A DuckDB-file catalog is locked by the process that writes it; readers in other processes cannot attach it during an export, migration or maintenance run (verified). Therefore:

Every write session ends, after the writer has closed the catalog, by publishing famulus.read.ducklake (copy to a temporary file in _lake/, then atomic rename).
All readers (MCP server, famulus-proof lake mode, clients over SMB) attach the replica. They are never blocked and see the new state after the writer has closed.
This is safe because Parquet files are immutable and ducklake_cleanup_old_files removes old files only after 24 h, so a replica never refers to deleted files.
FAMULUS_LAKE_PRIMARY=1 makes readers attach the primary catalog instead.

Placement on PDAI-1

/Volumes/PDAI-1/pdai-collections/            ← DATA_PATH
├── _lake/
│   ├── famulus.ducklake                     catalog (DuckDB file, writer only)
│   ├── famulus.read.ducklake                read replica (all readers)
│   ├── famulus_reference_traces.duckdb      query trace of the MCP server
│   ├── core/                                schema main (fr_lake_collection, fr_release, …)
│   ├── landing/  platform_catalog/  assess/ lake-wide legacy DBs
├── _register/_lake/                         schema register
└── <coll>/
    ├── <coll>_famulus.duckdb                per-collection famulus DB (rebuildable)
    └── _lake/
        ├── *.parquet                        schema <coll>
        └── legacy/*.parquet                 schema legacy_<coll>

DuckLake 1.0 has no per-schema data_path option. lake.ensure_collection_schema therefore sets the path column of the catalog’s ducklake_schema row (DuckLake specification, relative to DATA_PATH) to <coll>/_lake/ directly after CREATE SCHEMA and before the first table is created. Once a schema contains tables, its path is not changed. Schema main is set to _lake/core/ at init-lake.

Schemas

Schema	Content	Parquet path
`<coll>` (collection schema)	the 14 core tables of `famulus-reference/1` (`fr_collection` … `fr_harvest`)	`<coll>/_lake/`
`legacy_<coll>` (legacy schema)	raw copies of the collection’s legacy DBs, tables named `<kind>__<table>` (e.g. `fql__fq_documents`, `stanza__stz_tokens`)	`<coll>/_lake/legacy/`
`main`	`fr_lake_collection`, `fr_release`, `fr_migration_log`, `fr_maintenance_log`; views `main.fr_*` = `UNION ALL BY NAME` over all collection schemas	`_lake/core/`
`register`	`_register` DB (persons, places)	`_register/_lake/`
`landing`	landing DBs (e.g. eulerOO-IVA `letters_reference`)	`_lake/landing/`
`platform_catalog`	`catalog.duckdb`	`_lake/platform_catalog/`
`assess`	`grounding_index.duckdb`	`_lake/assess/`

Table definitions: XML Schemas → famulus-reference/1.

Export

famulus-reference export --collection <coll> [--famulus-db PATH] [STUDIO] writes <coll>/<coll>_famulus.duckdb into the lake.

Step	Effect
attach	famulus DB attached `READ_ONLY`; collection schema created with its path if missing
one transaction	every core table of the collection schema is replaced by the content of the famulus DB; result: exactly one snapshot per export
deletions	addresses present before and absent now are inserted into `fr_harvest` with `deleted = true` (OAI-PMH incremental harvesting)
registration	row in `main.fr_lake_collection` (sha256 of the famulus DB, export time, host, row count); `main.fr_*` views refreshed
commit message	`export <coll> sha256=<12 hex>`
flush	`ducklake_flush_inlined_data` for the collection schema and `main`: small inserts are inlined into the catalog by default; an export is a batch, so its rows are written to Parquet at once
replica	read replica published after the writer has closed

Migration of existing DuckDBs

#	Step	Command / location
1	Inventory (read-only, any host): every `*.duckdb` under the collection root and the code trees (platform, pdai, apps, landings2026) with size, tables, row counts, schema hash, kind, collection, consumers	`famulus-reference migrate-inventory --out report.json`; report platform/reports/duckdb-inventory.md
2	Raw legacy copies: each legacy DB attached `READ_ONLY`, base tables copied into `legacy_<coll>`	`famulus-reference migrate --collection <coll>` [STUDIO]; lake-wide DBs: `migrate --lakewide`
3	Parity log: row count and order-independent row-hash sum of source and target per table	`main.fr_migration_log`
4	Adapter: consumers read the lake with `FAMULUS_LAKE=1` (famulus-proof `lake.connect_sidecar` exposes `legacy_<coll>` tables under their sidecar names); falls back to the sidecar file when the lake has no copy	`development/famulus-proof/src/famulus_proof/lake.py`
5	Switch-over: defaults flipped when parity is 100 %; snapshot tagged `v1-migration`	release in `fr_release`
6	Subsumed files: after a two-week shadow period the legacy files become candidates in the PDAI-1 space audit; nothing is deleted without the owner’s approval	`platform/scripts/pdai1_space_audit.py`

Exclusions and special cases:

File	Treatment
`search_chats.duckdb`	excluded: operational query log, not reference data
`*.work.duckdb`	excluded: build scratch files
per-document `index.duckdb` (2,474 files)	not copied raw; their `pages` tables are read by `famulus-reference build` into `fr_page` / `fr_text` (layer `ocr`) with file path and sha256 as provenance
full-text indexes (`fts_main_*`)	not in the lake; forms that call `match_bm25` use the sidecar file

Precondition

PDAI-1 had 24 GiB free on 2026-10-14. Steps 2–6 wait for the report-only space audit (target ≥ 80 GiB free; the lake needs about 30–40 GB of Parquet plus headroom).

Maintenance

famulus-reference maintain [--dry-run] [--orphans] [--backup-dir D] [STUDIO], launchd com.ai-platform.famulus-lake-maintain daily at 03:30. Each step is called individually (not via CHECKPOINT) and logged in main.fr_maintenance_log. Adapted from the runbook in DuckLake in production: maintenance.

#	Step	Setting	Reason
1	`ducklake_flush_inlined_data`	all schemas	inserts below 10 rows are stored in the catalog
2	`ducklake_merge_adjacent_files`	all schemas	few large Parquet files instead of many small ones
3	`ducklake_expire_snapshots`	older than 7 days, except snapshots in `fr_release` and snapshots handed out by `fr_cite` / `fr_bibtex`; the newest snapshot is always kept	citations stay reproducible
4	`ducklake_cleanup_old_files`	`older_than` 24 h (article: 1 h)	SMB readers and the read replica can refer to files for long scans
5	`ducklake_delete_orphaned_files`	weekly (`--orphans`), dry run first	result feeds the space audit
6	catalog backup	dated copy of `famulus.ducklake`, 14 kept	catalog loss = lake loss

Dry run from a client: queue job famulus-lake-maintain-dry.

Reader recipes

All readers attach READ_ONLY. Use the read replica; the primary catalog is locked while mac-studio writes.

duckdb CLI

LOAD ducklake;
ATTACH 'ducklake:/Volumes/PDAI-1/pdai-collections/_lake/famulus.read.ducklake' AS lk (READ_ONLY);
SELECT fr_id, image_path FROM lk.main.fr_page WHERE fr_id LIKE 'fr:hartmann/%' LIMIT 10;
SELECT * FROM lk.snapshots() ORDER BY snapshot_id DESC LIMIT 5;
SELECT count(*) FROM lk.hartmann.fr_segment AT (VERSION => 42);   -- time travel to snapshot 42

With famulus.ducklake instead of famulus.read.ducklake the same statements read the primary catalog (fails while a write session is open).

Python

from famulus_reference import lake, api

with lake.connect() as con:                     # READ_ONLY, attaches the replica as lk
    print(api.cite(con, "fr:hartmann/bsb00028171/0137/print1"))

from famulus_proof import lake as fl            # FAMULUS_LAKE=1
print(fl.resolve_fr("fr:hartmann/bsb00028171/0137"))

Via MCP (preferred from clients: no SMB scan, results traced)

call_tool("famulus-reference", "fr_sql",
          {"sql": "SELECT seg_type, count(*) FROM main.fr_segment GROUP BY 1"})

Agent access: SQL guard and limits

The read-only SQL tool fr_sql takes three patterns from duckdb-agent-loop-poc: schema-first prompting (fr_get_structure, fr_get_sidecars, lake schema description), an iteration cap, and a per-run trace (fr_run, fr_run_step). The iteration cap belongs to the calling agent loop (Partitur, orchestrator); the server does not count calls. The PoC has no read-only enforcement, no row cap and no timeout. All three gaps are closed by our SQL guard and limits:

Gap in the PoC	Closed by
no read-only enforcement	`sqlguard.validate`: exactly one SELECT/WITH/SHOW/DESCRIBE statement; no DDL/DML/ATTACH/COPY/PRAGMA/SET anywhere in the AST; DuckDB file readers (`read_csv`, `read_parquet`, `glob`, …) and `ducklake_*` maintenance functions refused; file paths as tables refused; lake attached `READ_ONLY`
no row cap	max. 500 rows, `truncated` flag
no timeout	statement interrupted after 20 s (`api.SQL_TIMEOUT_S`, `connection.interrupt()` from a timer — DuckDB has no statement-timeout setting); short-lived connection per request

Risks

Risk	Handling
DuckDB version alignment	every venv that reads the lake pins `duckdb >= 1.5.5` (ai-lemma-index upgraded from 1.5.3); extensions `ducklake`, `fts` installed on mac-studio
SMB latency on clients	reads through the MCP server; direct SMB reads only for small queries
single writer	all lake writes run on mac-studio, serialised through the syncthink queue; readers use the replica
PDAI-1 space	migration blocked until the space audit has freed ≥ 80 GiB
schema drift between legacy DBs	raw copy first (`legacy_<coll>`), mapping into `fr_*` afterwards