rad-cohesion
geometric packing game (tetris-ish) on an icosahedron
Star
Fork
Watch
rad-cohesion appview migrations README.md
2.4 kB · 42 lines
Markdown
at appview

SQL migrations #

Add files named 0001_description.sql, 0002_description.sql, and so on. Umzug records their filenames in the strict schema_migrations table. 0001_ingestion_index.sql defines the account, run, scenario, and Jetstream cursor tables. Later migrations add the independent discovery cursor and bootstrap phases (0003_collection_bootstrap.sql), then author status-check timestamps (0004_author_status_refresh.sql), and anonymous leaderboard identities and submissions (0005_leaderboards.sql). Add future changes as new numbered files.

0007_scenario_discovery_order.sql adds an index for public scenario discovery, ordered by publication time and URI. Publication times are compared as instants so timestamps with different UTC offsets sort chronologically.

0008_player_listing_order.sql adds author-specific chronological indexes for player scenario and run pages. It replaces the older scenario author/date index with one that compares timestamps as instants.

0009_guest_run_subdivision.sql sets subdivision to 5 on older Guest run snapshots that lack the field. It checks their historical board fingerprint before updating, preserves explicit subdivisions and stored challenge identities, and keeps snapshot JSON in the canonical order used by current writers. An unexpected fingerprint aborts the migration transaction without changing records.

The full Node application's startup applies pending files before opening the HTTP listener. All pending SQL and migration bookkeeping share one BEGIN IMMEDIATE transaction. A failed migration prevents startup and rolls back the batch; migrations do not run during build or prerender.

  • Append migrations; never rename, remove, or edit an applied file.
  • Use STRICT tables, explicit NOT NULL, and domain CHECK constraints.
  • Use bound parameters in application queries. Migration SQL is trusted code.
  • Do not put transaction statements or connection pragmas in migration files.
  • Migrations run forwards. Back up before upgrading; rollback means restoring both the previous image and its database backup.
  • Stop the running service before deploying schema changes. The migration lock serializes concurrent migration commands, but does not make old application code compatible with a new schema.

The runner rejects missing or out-of-order migration history, including starting an older image against a database with newer migrations.