Catalogue schema

The catalogue is one SQLite database at .catalogue/index.db, rebuilt by dscat ingest. dataset and version are columns on every content table, so a query can scope to a single release. The tables are declared in dscat.index (see dscat.index.Catalogue) as SQLAlchemy Core Table objects; this page describes the shape they produce.

For anything the CLI does not cover, query the database directly:

sqlite3 .catalogue/index.db "SELECT ... ;"

Tables

dataset(name, display_name)

version(dataset, version, ship_folder, dictionary_path, ingested_at)

tbl(                          -- one row per physical CSV
  id, dataset, version, table_name, display_title,
  role,                       -- '' for SPARK; proband/mother/father/sibling/mz_twin for SSC
  file_path,                  -- POSIX, relative to the repository root
  n_rows, n_cols, file_bytes, notes)

feature(                      -- one row per dictionary variable, defined once per table
  feature_uid, dataset, version, table_name, name,
  qualified_id, definition, field_type, measurement_scale,
  value_coding, notes, display_title, display_hint,
  roles_applicable,           -- comma-separated family roles (SSC); '' for SPARK
  source_sheet)

document(id, dataset, version, path, kind, title, md_path)

synonym(term, expansion)      -- directed; every member of a group maps to every other

feature_fts is a SQLite FTS5 virtual table over name, display_title, definition, value_coding, and notes. It uses external content (the feature table) and porter stemming, and is rebuilt from feature on each ingest. Rank matches with bm25(feature_fts), where a lower score is a better match.

Indexes

• feature(dataset, version, table_name, name)

• feature(name)

• tbl(dataset, version, table_name, role)

Example queries

-- Features whose value coding mentions a sentinel, in the latest SPARK version
SELECT table_name, name, value_coding FROM feature
WHERE dataset = 'spark' AND version = '2026-03-23' AND value_coding LIKE '%-999%';

-- Which SSC measures exist for fathers
SELECT DISTINCT table_name FROM tbl WHERE dataset = 'ssc' AND role = 'father';

-- The ranked search that `dscat search` runs, by hand
SELECT f.table_name, f.name, bm25(feature_fts) AS rank
FROM feature_fts JOIN feature f ON f.feature_uid = feature_fts.rowid
WHERE feature_fts MATCH 'anxiety OR worry' AND f.dataset = 'ssc'
ORDER BY rank LIMIT 10;

The dataclasses written into these tables are dscat.model.FeatureRow and dscat.model.TableRow; the read and write helpers are on dscat.index.Catalogue.