Menu
Use when adding or changing a SysNDD DB migration, editing a SQL view (especially ndd_entity_view or the core read views), touching the migration runner or manifest, restoring a database, or diagnosing a startup failure related to migrations
Install with Codex or Claude Copy this prompt, paste it into Codex, Claude, or another assistant, and let it review the skill page and install it for you.
Based on SOC occupation classification
Use when adding or changing an external HTTP provider call in SysNDD (gnomAD, Ensembl, UniProt, AlphaFold, MGI, RGD, PubMed, PubTator, MONDO, JAX/HPO) or touching external-call timeouts, retries, caching, or per-request time budgets
Use when wiring a SysNDD Vue 3 view or component to the backend API, adding a typed API client, handling API errors, or binding backend data into BootstrapVueNext tables and tooltips — especially around Plumber response shapes and BVN table/tooltip quirks
Use when reviewing or writing SysNDD code for security vulnerabilities or correctness bugs — authorization/role gates, SQL/expression injection, credential and secret handling, data exposure through public or MCP surfaces, resource-exhaustion/DoS from external calls, error/info leakage, and the repo's known R/Plumber footguns — especially before merging a diff or PR
Use when changing gene clustering (STRING/Leiden functional axis or phenotype MCA/HCPC), the analysis snapshot builder or validator, the memoised clustering cache, cache coherence, or LLM cluster summaries — or deploying a clustering change so public endpoints and summaries reflect it
Use when writing, running, or fixing SysNDD R/Plumber API tests (testthat) — including database-writing tests, running a single test inside the API container, mocking external providers, or diagnosing SKIP-vs-PASS, helper-loading, or path-resolution issues
Use when adding or changing durable async jobs, worker-executed code, job handlers, queue lanes or priorities in SysNDD — or diagnosing why a submitted job never runs, stays queued, or fails with "No durable async job handler registered"
| name | sysndd-migrations-db |
| description | Use when adding or changing a SysNDD DB migration, editing a SQL view (especially ndd_entity_view or the core read views), touching the migration runner or manifest, restoring a database, or diagnosing a startup failure related to migrations |
Use this skill before adding a migration, editing a view, or touching the migration runner. db/migrations/*.sql are applied at API startup by the runner using a MySQL advisory lock (GET_LOCK('sysndd_migration'), migration-runner.R). See db/migrations/README.md for specifics.
A failing migration is supposed to crash boot. Do not weaken startup checks to work around it — fix the migration or the mount. Missing, empty, or stale migration mounts are fatal by design and are a packaging/deployment problem, not something to patch in code.
NNN_snake_case.sql, contiguous with the current latest (currently 041_add_analysis_reproducibility.sql). make lint-api checks the prefix.api/functions/migration-manifest.R: EXPECTED_LATEST_MIGRATION must equal the new sorted-latest filename, and keep EXPECTED_MIGRATION_COUNT in sync. Startup validates the manifest before the fast path (validate_migration_manifest): the dir must exist, contain SQL files, have the expected latest, and meet the minimum count — all fatal if not.039 re-asserts columns/types after a drifted restore). Prefer CREATE TABLE IF NOT EXISTS / guarded ALTER for re-assertable DDL.The core read views (ndd_entity_view, users_view, search_non_alt_loci_view, search_disease_ontology_set) are codified in 025_create_core_views.sql with SQL SECURITY INVOKER so a pristine DB boots. ndd_entity_view is later rebuilt by 026_add_entity_last_update.sql (adds the derived last_update column). The latest CREATE OR REPLACE VIEW ndd_entity_view migration is the source of truth and must stay mirrored byte-for-byte (modulo the sysndd_db. schema prefix and the migration's ALGORITHM/SQL SECURITY INVOKER clause) in db/C_Rcommands_set-table-connections.R. Edit a view definition → update both.
TRUNCATE (DDL auto-commits, breaking the transaction). Use refresh_disease_ontology_set() / metadata_with_foreign_key_checks_disabled() from metadata-refresh.R; enforced by test-unit-metadata-refresh-patterns.R.dbWriteTable-style DB restore silently drifts schema (narrow auto-sized VARCHARs, comparison_id recreated as DOUBLE PK without AUTO_INCREMENT, dropped columns). Use make db-restore-latest then make db-views-rebuild to recreate the DEFINER views; the idempotent re-assert migrations (039) repair the drift.COPY config.yml to api/Dockerfile — config is a runtime read-only mount only.bootstrap/load_modules.R and need a worker restart — see sysndd-async-jobs.Confirm the API starts cleanly (migration runner logs each applied migration), run make lint-api (prefix check), and consult db/migrations/README.md.