Schema design

CryoHealth runs on a single PostgreSQL 16 database with the PostGIS extension. Every service reads and writes the same 17 tables, and every change to them is a TypeORM migration in CryoHealth-api. Nothing else alters the schema.

Current as of migration 1790097238238-BackfillProtocolSteps (11 migrations). Browse the migrations on GitHub ↗

Conventions

Every table has a uuid primary key named id, generated by uuid_generate_v4(). Timestamps are timestamptz.
Locations are PostGIS geometries in WGS 84 (SRID 4326), except glaciers and district centroids, which store latitude and longitude as plain numbers.
Column names follow two styles. Tables from the initial API schema use camelCase (lakeId, createdAt). Tables and columns added for the web dashboard use snake_case (district_id, created_at).
Pipeline outputs keep their inputs: runId on observations and scores, and the full score breakdown in hazard_scores.components.

Tables by domain

Hazard monitoring

Glacial lakes, glaciers, and the satellite observations and scores computed for them.

lakes observations hazard_scores lake_risk_scores glaciers glacier_observations

Alerts

Human-issued GLOF alerts, their acknowledgements, and the audit trail behind them.

alerts alert_acknowledgements audit

Community health

CHW case records, IMCI protocols, and the offline sync ledger.

chw_cases cases protocols sync_log

Identity & facilities

Users, roles, CHW profiles, and the health facilities they belong to.

users chw_profiles facilities

Reference geography

Administrative districts that lakes, glaciers, alerts, and cases are grouped by.

districts

Relationships

All 20 foreign keys. lakes, users, and districts are the hubs most other tables point to.

From	References	On delete
lakes.district_id	districts.id	set null
observations.lakeId	lakes.id	cascade
hazard_scores.lakeId	lakes.id	cascade
lake_risk_scores.lake_id	lakes.id	cascade
glaciers.district_id	districts.id	set null
glacier_observations.glacier_id	glaciers.id	cascade
alerts.lakeId	lakes.id	set null
alerts.district_id	districts.id	set null
alerts.issuedById	users.id	no action
alert_acknowledgements.alert_id	alerts.id	cascade
alert_acknowledgements.chw_id	users.id	cascade
audit.actorId	users.id	no action
chw_cases.chwId	users.id	no action
cases.chw_id	users.id	restrict
cases.district_id	districts.id	set null
sync_log.userId	users.id	no action
users.facilityId	facilities.id	no action
chw_profiles.user_id	users.id	cascade
chw_profiles.district_id	districts.id	set null
facilities.lakeId	lakes.id	no action

Enums

tier

normalwatchhighcritical

Used by lakes.currentTier, hazard_scores.tier, alerts.tier

dam_type

morainebedrockiceunknown

Used by lakes.damType

role

cryohealth_adminfacility_adminchwviewer

Used by users.role

alert_status

activecleared

Used by alerts.status

sync_state

queuedsynced

Used by chw_cases.syncState

Tables

PK primary key · FK foreign key · UQ unique · null column is nullable

Hazard monitoring

lakes

Monitored glacial lakes and their current hazard tier.

Column	Type	Details
`id`PK	uuid	default `uuid_generate_v4()`
`name`	varchar
`nameUr`	varchar · null	Urdu name
`slug`UQ	varchar
`valley`	varchar
`district`	varchar	Free-text district name
`district_id`FK	uuid · null	→ districts on delete set null
`damType`	dam_type	default `'unknown'`
`glacierContact`	boolean	default `false`
`icimodId`UQ	varchar · null	ICIMOD inventory ID
`geom`	geometry(Point, 4326)
`boundary`	geometry(Polygon, 4326) · null
`elevationM`	integer · null
`area_km2`	numeric · null
`historicalGlof`	boolean	default `false`
`currentTier`	tier	default `'normal'`
`current_risk_score`	numeric	default `0`
`downstream_population`	integer	default `0`
`stale`	boolean	default `false` No recent usable observation
`source`	text	Provenance of the lake record
`sourceUrl`	varchar · null
`createdAt`	timestamptz	default `now()`
`updatedAt`	timestamptz	default `now()`

district (text) and district_id (FK) coexist: the text column predates the districts table.

observations

Lake water-extent measurements from Sentinel-2 scenes, written by the EO pipeline.

Column	Type	Details
`id`PK	uuid	default `uuid_generate_v4()`
`lakeId`FK	uuid	→ lakes on delete cascade
`capturedAt`	timestamptz
`source`	varchar	default `'sentinel2'`
`areaKm2`	numeric(12,6)
`cloudFraction`	numeric(5,4) · null
`sceneId`	varchar · null
`runId`	varchar	Pipeline run that produced it
`createdAt`	timestamptz	default `now()`

Indexes: (lakeId, capturedAt) · UNIQUE (lakeId, capturedAt, source) — dedupe re-runs

hazard_scores

Per-run hazard score and tier for a lake, with the inputs that produced it.

Column	Type	Details
`id`PK	uuid	default `uuid_generate_v4()`
`lakeId`FK	uuid	→ lakes on delete cascade
`runId`	varchar
`score`	numeric(8,4)
`tier`	tier
`components`	jsonb	Score inputs, kept so any tier can be recomputed
`computedAt`	timestamptz
`createdAt`	timestamptz	default `now()`

Indexes: (lakeId, computedAt)

lake_risk_scores

Time series of lake risk scores with a confidence value and data source.

Column	Type	Details
`id`PK	uuid	default `uuid_generate_v4()`
`lake_id`FK	uuid	→ lakes on delete cascade
`score`	numeric
`tier`	text	Plain text, not the tier enum
`confidence`	numeric	default `0.8`
`source`	text	default `'sentinel-1'`
`observed_at`	timestamptz	default `now()`

Indexes: (lake_id, observed_at DESC)

Parallel to hazard_scores; both tables exist in the current schema.

glaciers

Glacier inventory (RGI / GLIMS) for the monitored region.

Column	Type	Details
`id`PK	uuid	default `uuid_generate_v4()`
`name`	text
`rgi_id`	text · null	Randolph Glacier Inventory ID
`glims_id`	text · null
`district_id`FK	uuid · null	→ districts on delete set null
`lat`	double precision
`lng`	double precision
`area_km2`	numeric · null
`length_km`	numeric · null
`elevation_min_m`	integer · null
`elevation_max_m`	integer · null
`status`	text	default `'unknown'`
`terminus_type`	text · null
`source`	text · null
`last_observed`	timestamptz · null
`notes`	text · null
`created_at`	timestamptz	default `now()`

Indexes: (district_id)

glacier_observations

Dated glacier measurements: area, length, and terminus change.

Column	Type	Details
`id`PK	uuid	default `uuid_generate_v4()`
`glacier_id`FK	uuid	→ glaciers on delete cascade
`observed_at`	timestamptz
`area_km2`	numeric · null
`length_km`	numeric · null
`terminus_change_m`	numeric · null
`status`	text · null
`source`	text · null
`notes`	text · null
`created_at`	timestamptz	default `now()`

Indexes: (glacier_id, observed_at DESC)

Alerts

alerts

GLOF alerts issued by a person, with bilingual body text and action items.

Column	Type	Details
`id`PK	uuid	default `uuid_generate_v4()`
`lakeId`FK	uuid · null	→ lakes on delete set null
`district_id`FK	uuid · null	→ districts on delete set null
`tier`	tier
`title`	varchar
`body`	text
`body_en`	text · null	Backfilled from `body`
`body_ur`	text · null
`chips`	jsonb · null	Short action tags
`checklist`	jsonb · null	Numbered action items
`windowStart`	timestamptz · null
`windowEnd`	timestamptz · null
`estimated_window`	text · null
`downstreamSummary`	text · null
`affected_population`	integer	default `0`
`status`	alert_status	default `'active'`
`issuedById`FK	uuid · null	→ users on delete no action
`createdAt`	timestamptz	default `now()`
`clearedAt`	timestamptz · null

Indexes: UNIQUE (lakeId, tier) WHERE status = 'active' — one active alert per lake and tier

chips and checklist are nullable and never backfilled: older alerts carry none rather than invented ones.

alert_acknowledgements

Records that a CHW has seen and acknowledged an alert.

Column	Type	Details
`id`PK	uuid	default `uuid_generate_v4()`
`alert_id`FK	uuid	→ alerts on delete cascade
`chw_id`FK	uuid	→ users on delete cascade
`acknowledged_at`	timestamptz	default `now()`

Indexes: UNIQUE (alert_id, chw_id)

audit

Append-only audit trail. Every alert creation or tier override records a human-readable reason.

Column	Type	Details
`id`PK	uuid	default `uuid_generate_v4()`
`actorId`FK	uuid · null	→ users on delete no action
`action`	varchar
`entityType`	varchar
`entityId`	varchar · null
`reason`	text · null
`meta`	jsonb · null
`createdAt`	timestamptz	default `now()`

Community health

chw_cases

Triage cases captured offline on the field app and synced to the server.

Column	Type	Details
`id`PK	uuid	default `uuid_generate_v4()`
`chwId`FK	uuid	→ users on delete no action
`capturedAt`	timestamptz
`payload`	jsonb
`outcome`	varchar · null
`syncState`	sync_state	default `'synced'`
`deviceId`	varchar
`clientCaseId`UQ	varchar	Idempotency key for offline upsert
`createdAt`	timestamptz	default `now()`

Indexes: (chwId, capturedAt)

cases

Structured case records used by the web dashboard's CHW and admin views.

Column	Type	Details
`id`PK	uuid	default `uuid_generate_v4()`
`chw_id`FK	uuid	→ users on delete restrict
`district_id`FK	uuid · null	→ districts on delete set null
`patient_age`	integer · null
`patient_sex`	text · null
`symptoms`	text
`diagnosis`	text · null
`treatment`	text · null
`outcome`	text · null
`is_disaster_related`	boolean	default `false`
`created_at`	timestamptz	default `now()`
`deleted_at`	timestamptz · null	Soft delete

Indexes: (chw_id, created_at DESC) · (district_id, created_at DESC)

Parallel to chw_cases: cases holds structured columns, chw_cases holds the device payload as jsonb.

protocols

IMCI and disaster-response protocols. All dosing and diagnosis text shown to CHWs comes from here.

Column	Type	Details
`id`PK	uuid	default `uuid_generate_v4()`
`slug`UQ	text
`title`	text
`category`	text
`body`	text
`steps`	jsonb · null	Ordered protocol steps
`source`	text	default `'WHO IMNCI'`
`is_disaster`	boolean	default `false`
`created_at`	timestamptz	default `now()`
`updated_at`	timestamptz	default `now()`

sync_log

One row per device sync, for tracing offline data back to its upload.

Column	Type	Details
`id`PK	uuid	default `uuid_generate_v4()`
`userId`FK	uuid	→ users on delete no action
`deviceId`	varchar
`startedAt`	timestamptz
`finishedAt`	timestamptz · null
`itemCount`	integer	default `0`
`status`	varchar	default `'ok'`
`detail`	jsonb · null
`createdAt`	timestamptz	default `now()`

Identity & facilities

users

Everyone who can sign in: admins, facility staff, CHWs, and viewers.

Column	Type	Details
`id`PK	uuid	default `uuid_generate_v4()`
`role`	role
`name`	varchar
`phone`UQ	varchar · null
`lhwId`UQ	varchar · null	Lady Health Worker ID
`passwordHash`	varchar
`facilityId`FK	uuid · null	→ facilities on delete no action
`active`	boolean	default `true`
`createdAt`	timestamptz	default `now()`

chw_profiles

Extra profile data for community health workers.

Column	Type	Details
`id`PK	uuid	default `uuid_generate_v4()`
`user_id`FK	uuid · null	→ users on delete cascade
`full_name`	text	default `'Community Health Worker'`
`district_id`FK	uuid · null	→ districts on delete set null
`phone`	text · null
`language`	text	default `'ur'`
`created_at`	timestamptz	default `now()`

facilities

Health facilities (BHUs and others) and the lake that threatens them.

Column	Type	Details
`id`PK	uuid	default `uuid_generate_v4()`
`name`	varchar
`type`	varchar	default `'bhu'`
`district`	varchar
`geom`	geometry(Point, 4326) · null
`contact`	varchar · null
`lakeId`FK	uuid · null	→ lakes on delete no action
`vulnerability`	text	default `'low'`
`createdAt`	timestamptz	default `now()`

Reference geography

districts

Administrative districts of Gilgit Baltistan.

Column	Type	Details
`id`PK	uuid	default `uuid_generate_v4()`
`name`UQ	text
`province`	text	default `'Gilgit Baltistan'`
`population`	integer · null
`centroid_lat`	double precision · null
`centroid_lng`	double precision · null
`created_at`	timestamptz	default `now()`