ReviewOS

stacks/wildloop

Use REAL column affinity for lat/lng/area/perimeter numeric columns

#974
Closed glennmichael123 opened this 9 days ago ·
9 days ago

Problem

Many numeric columns are declared INTEGER (trails.latitude/longitude/rating, territories.center_lat/lng/area_size/perimeter, activities.elevation, etc.) though they hold floats. SQLite's type affinity preserves the float values today, but this is incorrect schema that will truncate under strict mode / Postgres / MySQL, and FK-ish ids/counts are stored as REAL in places too.

Acceptance

  • Regenerate migrations so numeric columns use REAL and id/count columns use INTEGER.
  • Verify values survive a fresh migrate.
9 days ago

Done in c651b43 (+ review fixes in ee82b1c).

Float-holding numeric columns were declared INTEGER. Now they're REAL; ids/counts stay INTEGER.

  • Framework (vendored helpers.ts): the SQLite branch mapped every number to 'integer'. schema.float() columns now emit REAL on SQLite (other drivers unchanged).
  • Models: switched 17 float-holding attributes from schema.number() to schema.float() across Trail, Territory, Activity, TerritoryStats, Challenge, TerritoryHistory, and UserStats (total_distance/total_elevation — caught by the adversarial review, since total_distance is the fractional sum of activities.distance which this change also made REAL).
  • Migrations: brought the create-table SQL in line (INTEGER→REAL) since SQLite can't ALTER COLUMN type; a fresh migrate rebuilds the tables as REAL.

Verified by scripts/verify-column-affinity.ts: all 17 float columns are REAL, ids/counts INTEGER, fractional values round-trip exactly (REAL storage class). A full scan confirms no float-factory column remains on schema.number(). Territory-engine regression (heavy float math) green.

Note: committed on branch chore/migrate-text-to-mustache (the branch checked out at the time), not main yet.

Sign in to comment on this issue.