Skip to content

Rebuild datasets as a narrower table: 0071 cannot apply at the D1 100-column cap #1182

Closed

Description

@neuromechanist

Problem

D1 caps the datasets table at 100 columns. Migration 0071_signal_defaults.sql adds 5 columns and fails to apply with too many columns on sqlite_altertab_datasets: SQLITE_ERROR [code: 7500]. This blocks every deploy, so the dev -> main PR and the production backfill sweeps cannot proceed.

Environment Last applied migration Columns
prod (nemar-db) 0069 89
dev (nemar-db-dev) 0070 98
either, with 0071 — 103 (fails)

Derivable from the repo without credentials: 11 base columns in 0001_initial.sql, plus 90 applied ADD COLUMN statements, minus 3 historical DROP COLUMN, equals 98.

Neither 0070 nor 0071 has landed on production, and 0071 has landed nowhere. That is what makes the sequencing below safe.

Incremental DROP COLUMN is not possible

SQLite refuses DROP COLUMN on a column that is indexed or bound by a CHECK constraint. Four of the six attestation columns carry CHECK constraints (attestation_deposit_type, attestation_key_status, attestation_deidentified, attestation_no_duplicate), so the attestation consolidation cannot be done with ALTER TABLE at all. It forces a full table rebuild: create the narrow table, copy, drop the old one, rename.

Since a rebuild is forced regardless, the scope is now to design the narrow target schema properly rather than shave columns one at a time. A Fable planner is designing that transfer.

Two hazards the rebuild must handle. access_requests.dataset_id REFERENCES datasets(id) ON DELETE CASCADE, and with foreign keys enforced a DROP TABLE performs an implicit DELETE FROM that fires cascade actions. And datasets.id must be preserved exactly, because the four triggers on the table key the FTS5 index (datasets_fts.rowid) and the embedding dirty flag to it.

Approach

No new table. One table stays the model. We reclaim budget by dropping columns that are exactly derivable or redundant, and by collapsing one group that is written and read as a unit into a single JSON column.

The test applied to every group was mechanical: a group can be collapsed only if it is written as a unit, read as a unit, and never filtered on individually. Groups that fail that test are left alone.

Tier A: drop 5 exactly-derivable or redundant columns

All figures below are full-table counts over all 789 production rows, not a sample.

Column Evidence Frees
zenodo_latest_version_id 0 of 789 rows non-null. It is a cache of a value the Zenodo concept record already resolves to, so the doomsday path can recover it with one API call 1
ezid_identifier exactly equal to 'doi:' || UPPER(concept_doi) in 768 of 768 non-null rows, and 0 rows have a concept_doi without it 1
doi_provider 'ezid' in 789 of 789 rows; structurally constant under ADR 0007 1
num_citations exactly num_dataset_citations + num_datapaper_citations in 789 of 789 rows 1
file_size_formatted stale denormalization: in 100 of 275 sampled rows the stored string matches neither a decimal nor a binary rendering of file_size within 2 percent 1

Two indexes must be dropped first, since SQLite refuses to drop an indexed column: idx_datasets_ezid and idx_datasets_num_citations (idx_datasets_zenodo_concept stays, since its column stays).

Tier B: collapse the 6 attestation columns into 1

attestation_deposit_type, attestation_key_status, attestation_deidentified, attestation_no_duplicate, attestation_upstream_source, attestation_accepted_at become a single attestation JSON column. Net 5 columns freed.

This group passes the collapse test exactly. All six are written by one UPDATE in backend/src/routes/datasets/upload.ts and one INSERT in the same file, they are read together, and no query filters or orders on any of them individually. Only 2 rows are currently populated, so the backfill is trivial.

Considered and rejected

staleness_warn_stage and staleness_admin_notified_at collapse 2 into 1 and free only a single column, and unlike attestation they are filtered on directly by the stale-dataset cleanup cron. The ratio is bad: one column of headroom in exchange for rewriting a live cron predicate. Left as is.

zarr_* and archive_* are the largest groups on the table but fail the test outright. zarr_status is itself a search facet, and both sweeps select their candidates with WHERE ..._checked_at IS NULL. Collapsing them would push filtering into json_extract on the hot path of the sweeps.

Deferred to a follow-up

The 12 sweep bookkeeping stamps (enrichment_updated_at, metadata_updated_at, archive_checked_at, zarr_checked_at, records_checked_at, citations_updated_at, channel_montage_checked_at, hed_checked_at, data_checked_at, availability_report_at, recording_stats_at, signal_defaults_at) could collapse into one JSON column and free 11. That is the single largest remaining win, but it rewrites the candidate-selection SQL of every sweep, which is too large a review surface to attach to a release unblock. Filed separately.

Column arithmetic

Both environments converge on the same schema because migrations apply in order.

Starting from 98 (dev, post-0070): drop 5 in Tier A gives 93, add the attestation column gives 94, drop the 6 old attestation columns gives 88. Applying 0071 then gives 93, leaving 7 columns of headroom.

That headroom is thin, which is why #1183 matters: collapsing the 12 sweep stamps frees a further 11 and takes the table to 82.

The intermediate order matters. Tier A drops run first so the transient peak while attestation coexists with the six columns it replaces is 93, never approaching the cap.

Migration sequencing

0071_signal_defaults.sql is already committed to dev but is applied in no environment, so it can be renumbered safely. The reclamation must run before it, otherwise the runner hits the failing migration first and never reaches the fix.

  • rename 0071_signal_defaults.sql to 0072_signal_defaults.sql
  • add 0071_reclaim_dataset_columns.sql

Code surface

ezid_identifier and doi_provider each appear in 11 non-migration source files, file_size_formatted in 5, num_citations in 4, and zenodo_latest_version_id in 3. The changes are mechanical (derive at read time instead of selecting a stored column) but this is not a migration-only PR.

External and contractual obligations

Checked, because a dropped column must not remove a field a third party is served.

shared/contract/dataset.ts is the declared single source of truth for the shapes the CLI, website, data plane, and third parties (EEGDash) exchange.

DOI metadata registered at EZID and DataCite is the hardest external commitment, since DOIs are permanent. It is unaffected: services/datacite.ts reads none of the columns being dropped. The neuroschema v0.4.0 landing payload served from data.nemar.org is likewise unaffected; neither the contract's neuroschemaDatasetSchema nor the vendored dataset.bundle.json declares any of them.

Two dropped columns are in the public API contract, both .optional(): file_size_formatted and num_citations. Optional means the contract does not force them, but consumers may read them when present, so both fields must continue to be served, derived at read time instead of stored. num_citations derives exactly from the two component counts, and file_size_formatted derives from file_size.

doi_provider, ezid_identifier, and the Zenodo columns appear in no external contract; they are internal and admin only.

The governing rule: dropping a column is acceptable, dropping a served field is not.

Risks

zenodo_concept_id is deliberately KEPT. Zenodo is not dead: it is the hard backup for the doomsday scenario where GitHub and git-annex are both lost and the metadata has to be recovered from somewhere else, and the deposit workers are still running. What is dropped is only the second, empty column. One pointer per dataset is enough to find the Zenodo record, and the record itself resolves to every version.

Dropping file_size_formatted changes displayed sizes, because the stored strings use binary units while the repo's formatBytes() is decimal. Sizes will shift (for example 78.93 MB becomes 82.8 MB). Given 100 of 275 sampled rows are already wrong, this replaces stale strings with correct ones.

Definition of done

  • 0071_reclaim_dataset_columns.sql applies cleanly to dev and to prod
  • 0072_signal_defaults.sql applies after it, landing at 93 columns
  • All read paths for the 6 dropped columns derive their values instead
  • Attestation reads and writes go through the single JSON column
  • A test pins the column count with headroom, so the next migration fails loudly in CI rather than at deploy time
  • ADR 0033 records the column budget and the collapse test

Activity

  1. changed the title [-]Reclaim datasets column budget: 0071 cannot apply at the D1 100-column cap[/-] [+]Rebuild datasets as a narrower table: 0071 cannot apply at the D1 100-column cap[/+] on Sep 1, 2026
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugSomething isn't working

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions