Skip to main content

Data model

The plain-language version is the data model primer. This page is the technical one: what the tables are, and why the awkward decisions were made.

Source of truth: apps/api/src/db/schema.ts (Drizzle) and packages/shared/src/domain.ts (Zod). Migrations are generated from the Drizzle schema, never hand-written:

pnpm --filter @crag-topo/api db:generate

Entity relationships​

Conventions every syncable table follows​

These are not stylistic. The sync protocol depends on all four.

1. Client-generatable ULID primary keys​

crg_01J4X8..., rte_01J4X9.... Prefixed so an ID is self-describing in a log, ULID-bodied so they sort roughly by creation time.

Why not autoincrement: a phone with no signal has to be able to create a tick now. Waiting for the server to assign an ID means the write cannot happen offline, which is the entire product. A client-minted ID also makes push idempotent — retrying a mutation cannot create a duplicate.

Minting lives in packages/shared/src/ids.ts and works in all three runtimes (workerd, Hermes, Node).

2. created_at / updated_at as epoch milliseconds​

Integers, not ISO strings, not SQLite datetime. Comparison is the hot path — every sync query is where updated_at > ? — and integers compare correctly without parsing or timezone reasoning.

3. Soft delete, always​

deleted_at is set; the row stays. A hard delete is invisible to a device that was offline when it happened, so that device keeps a route forever.

Consequence: every read query in the API filters deleted_at is null, and every sync query deliberately does not — the tombstone is the payload.

4. An index on (updated_at, id)​

Every syncable table has one. Sync pages with a keyset cursor on that exact pair, and the index is what makes it O(log n) instead of a scan.

The awkward decisions​

Why routes.crag_id is denormalised​

A route belongs to a sector, which belongs to a crag. Storing crag_id on the route as well is redundant.

It is there because the two hottest queries — "all routes at this crag" and "routes at this crag in this grade band" — would otherwise join through sectors on every call, and because scoping a sync pull to a set of crags needs a direct predicate. The cost is that moving a sector between crags must update its routes too; the API does this in one transaction, and it happens approximately never.

Why grades are stored twice​

grade is the published string: "E2 5c", "7a+", "V4". grade_band is a 0–100 integer.

The band exists only so that "show me everything between f6a and f7a at this crag" can span a sector graded in UK trad. It is lossy by construction and never displayed. See grading systems for why converting between systems for display would be wrong.

normaliseGrade() returns null rather than a default when it cannot parse a grade — a misparsed grade defaulting to 0 would sort as "easiest", which is exactly the failure mode you do not want in a safety-adjacent filter.

Why route lines are their own table​

Not a column on routes, not a column on photos.

A long route photographed from two angles has two lines. A single topo photo carries lines for every route on it. It is a genuine many-to-many with its own attributes (the points, the anchors, the label position), so it is its own record.

It also leaves room for the 3D renders on the wishlist: a future (route_id, model_id) line is an additive change rather than a rewrite.

Why points are normalised 0–1 rather than pixels​

[
  { "x": 0.62, "y": 0.95 },
  { "x": 0.63, "y": 0.72 },
  { "x": 0.61, "y": 0.55 }
]

The same line renders correctly at thumbnail size, full-screen, and on an iPad, and survives the photo being re-encoded at a different resolution — which it will be, because the image pipeline will serve several sizes. Storing pixels would tie the data to one particular encode of one particular file.

Why climbed_on is a YYYY-MM-DD string​

An ascent belongs to a day out, not to an instant. Storing a timestamp forces a timezone decision that is meaningless — climbing "on the 11th" is true regardless of where the phone thinks it is — and makes "how many days did I climb this year" a harder query than it should be.

Why JSON columns​

route_types, points, anchors, partner_user_ids, partner_names, preferred_grade_systems. SQLite has no array type, and D1 has no jsonb.

The rule: JSON is acceptable for data that is only ever read as a whole with its parent row. The moment something needs to be queried across rows — "which routes did I climb with Sam" — it becomes a real join table. partner_user_ids is the one most likely to graduate; the mutual-tick feature on the roadmap will probably force it, and that is fine.

Why user_crags exists​

Which crags a user has downloaded. It scopes the sync pull so a device never downloads the whole world, and it drives the "downloaded" state in the UI. Composite primary key, no surrogate id — it is a pure join with one attribute.

Local schema on the device​

apps/mobile/src/lib/db.ts mirrors this schema with two differences:

  • Columns the phone has no use for are omitted (photo credit, camera bearing, and so on until they are needed)
  • Two extra tables: outbox (mutations waiting for signal) and sync_state (per-kind cursors)

The two schemas are maintained by hand and must be changed together. That is a known sharp edge; generating the local schema from the shared Zod definitions is the obvious fix and is not done yet.

Changing the schema​

  1. Edit apps/api/src/db/schema.ts.
  2. pnpm --filter @crag-topo/api db:generate, then rename the generated migration to something readable.
  3. Update packages/shared/src/domain.ts so the wire contract matches.
  4. Update the local mirror in apps/mobile/src/lib/db.ts and the mappers in apps/mobile/src/lib/sync.ts.
  5. Update this page.
  6. If the change is structural rather than additive it needs an ADR — confirm it with @mattmoran56 before writing the record.

The migrations job in CI applies every migration to a throwaway local D1 on each PR, so a migration that no longer parses fails before it reaches a real database.