Geography - Backend Documentation

1. Documentation Evidence

2. Backend Scope and Boundaries

Owns

• CRUD for the three-tier hierarchy: provinces, districts, municipalities.
• The referenced-row pre-check that refuses a rename or reparent on any row a child row, the school profile, or a person's address already points at.
• The referenced-row pre-check that refuses a hard delete under the same conditions.
• Listing: provinces and districts are unpaginated (7 and 77 rows respectively, nationally fixed); municipalities paginate, because the national count can reach 753.

Does Not Own

• Auth/identity — delegated to JwtAuthGuard and RoleGuard (the module imports RoleModule for RoleService).
• The address CHECK constraints that make a half-filled address representable — those live on users and school_profile, not on this module's own tables. This module only owns the rows an address points at.
• Resolving a place name during import — data-transfer's own resolver does that, reading these tables.

Source of Truth

3. Module Composition

GeographyDistrictsService depends on GeographyProvincesService (to validate provinceId on write), and GeographyMunicipalitiesService depends on GeographyDistrictsService the same way — each tier validates its immediate parent through the sibling service rather than a raw query, so the "does this parent exist" answer is asked once per tier.

4. File and Directory Map

apps/api/src/modules/lookups/
  lookups.module.ts
  geography/
    geography.controller.ts
    geography-provinces.service.ts
    geography-districts.service.ts
    geography-municipalities.service.ts
    geography.service.spec.ts
  dto/
    geography.dto.ts

The three services were split out of what the districts service's own docblock calls a single GeographyService at 650 source lines — one file per tier, sharing their rationale through cross-references in each other's docblocks rather than restating it three times.

5. Data Model

5.1 Schema Source

packages/db/src/schema/school/geography.ts

5.2 Tables

provinces

districts

municipalities

5.3 The Composite Foreign Key, and Its One Subtlety

A person's address (and the school's own) stores all three ids — province, district and municipality — rather than the municipality alone. Deriving the parents by join would be more normalised, but it cannot represent a partially known address, which is the normal case at the admissions desk: the office has the district and not yet the ward.

Storing all three makes an inconsistent triple representable (a district from the wrong province), so it is forbidden by composite foreign keys: (province_id, district_id) must exist in districts, and (district_id, municipality_id) in municipalities.

The subtlety: a composite foreign key defaults to MATCH SIMPLE, under which the constraint is not enforced at all if any column of the key is NULL. So the key alone does nothing for a half-filled address — precisely the case it exists for. The CHECK constraints on the address columns (owned by users and school_profile, not by this module) close that gap by making the fill order mandatory: a district implies a province, a municipality implies a district. With those in place, both columns of each key are non-NULL together, and the key is live. This was verified by probing all 16 presence-combinations against a real database: the 4 valid ones accepted, the 12 invalid ones rejected 23514, and a district from the wrong province rejected 23503.

Why the composite targets are unique(...) and not uniqueIndex(...): both satisfy a composite foreign key once they exist, but drizzle-kit emits every ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY before every CREATE INDEX, so an index-form target does not yet exist when the referencing key is added and the migration dies on a clean database with SQLSTATE 42830. A table constraint is emitted inline in CREATE TABLE and is therefore always in place first. This is recorded against designations_department_id_id_unique in lookups.ts, where the index form passed a hand-ordered probe and failed the real migration.

5.4 What Is and Is Not Seeded

All 7 provinces and all 77 districts are seeded in full — that set is small, fixed and nationally defined, so anything less would be an arbitrary cut.

Municipalities are NOT complete. Nepal has 753 local levels; the seed carries 36: the six metropolitan cities, the eleven sub-metropolitan cities, and every local level of the three Kathmandu Valley districts. A school outside those adds its own from this module's POST /api/lookups/municipalities, which is the supported path and the reason this module exists at all rather than a static list. Seeding all 753 by hand was judged not defensible in a file nobody would ever review.

No row is typed vdc. Village Development Committees were abolished in 2017, so none currently exists; the enum keeps the value because a school may still hold a historical address recorded that way, and refusing to represent it would force an operator to mis-file the record.

The migration seeds the same rows once; seed-geography.ts is idempotent catch-up for a database that has not been migrated. ON CONFLICT DO NOTHING against the case-insensitive indexes means a school that renamed or retired a row keeps its version rather than having the seed's copy pushed back over it — and the seed deliberately does not reactivate a row it finds inactive, because is_active = false is a decision the school made on its own screen.

5.5 No deleted_at, Deliberately

Same reasoning as departments and designations. A soft delete never fires ON DELETE RESTRICT, so a soft-deleted district would keep resolving for every address still pointing at it while vanishing from the admin's list — gone to the eye, present to the query. Retirement is is_active = false: the row still resolves for existing references and leaves every pick list. Hard deletion is permitted only when nothing references the row, and ON DELETE RESTRICT enforces that at the database level once the application's own pre-check has passed.

RESTRICT rather than CASCADE throughout, for the obvious reason: a cascade would silently erase a person's recorded address because an operator tidied up a district.

6. Services and Responsibilities

6.1 GeographyProvincesService

6.2 GeographyDistrictsService

Same shape as provinces, with two differences: create/update first resolve the parent province through provincesService.findRowOrThrow, and isReferenced checks a different column set (municipalities, school_profile, users, scoped by district rather than province). Errors: DISTRICT_NOT_FOUND, PROVINCE_NOT_FOUND (bad parent), GEOGRAPHY_NAME_TAKEN, GEOGRAPHY_RENAME_REFERENCED, GEOGRAPHY_IN_USE.

Lists are ORDER BY name ASC, id ASC with no caller-chosen sort — see §7.

6.3 GeographyMunicipalitiesService

Same shape again, with three differences: findAll is paginated (PaginationUtil.normalize/getDrizzleParams, falling back to PaginationUtil.UNPAGINATED_HARD_CAP when pagination is off); create/update resolve the parent district through districtsService.findRowOrThrow; and isReferenced checks only two tables (school_profile, users) — there is no fourth child table, because municipalities are the leaf of the hierarchy. Errors: MUNICIPALITY_NOT_FOUND, DISTRICT_NOT_FOUND (bad parent), GEOGRAPHY_NAME_TAKEN, GEOGRAPHY_RENAME_REFERENCED, GEOGRAPHY_IN_USE.

6.4 The Referenced-Row Check, in Detail

Each tier's isReferenced(id) runs a fixed set of Promise.all-parallel existence checks and returns true if any finds a row:

• Provinces: users.permanentProvinceId, users.currentProvinceId, schoolProfile.provinceId, districts.provinceId.
• Districts: users.permanentDistrictId, users.currentDistrictId, schoolProfile.districtId, municipalities.districtId.
• Municipalities: users.permanentMunicipalityId, users.currentMunicipalityId, schoolProfile.municipalityId.

The list is enumerated explicitly rather than derived — every service's own docblock says why: missing a referencing column here would return an unmapped 500 instead of a 409 or a rename refusal, silently, the next time a new address column is added elsewhere.

users.deleted_at is deliberately NOT filtered in any of the three checks: ON DELETE RESTRICT counts every row regardless of the person's own soft-delete state, so a province referenced only by a removed person is genuinely undeletable at the database level, and a liveness-filtered application check would promise a delete the database then refuses.

6.5 Rename and Reparent Refusal

update() on all three services computes isRenaming (name changed) and, for districts and municipalities, isReparenting (parent id changed). If either is true and the row is referenced, the write is refused with 409 GEOGRAPHY_RENAME_REFERENCED before any column is touched. Retirement (isActive alone) is never gated by this check — a school always stays able to remove a row from its pick lists.

The reason this is refused outright rather than merely discouraged: the export module writes place names, not surrogate ids (see the data-transfer docs), so a rename here is retroactive in a way a flat classification's rename is not — it silently rewrites the content of every export already downloaded and repoints every future name-resolved import.

6.6 Delete

delete() on all three services runs the same isReferenced check, then a hard DELETE.

Why this is a pre-check in the application, not a caught SQLSTATE: this repo's established pattern (lookups.service.ts's deleteDepartment), and foreign-key violation codes are deliberately not mapped to 409 (all-exceptions.filter.spec.ts). More specifically here: this session probed an explicit ON DELETE RESTRICT and it raises SQLSTATE 23001, not the 23503 a caught-code translator would have expected — a mapping written against the wrong code would never fire, and the delete would surface as an unmapped 500 instead of 409 GEOGRAPHY_IN_USE.

6.7 Name Uniqueness

Each tier's assertNameFree is a pre-read backed by the corresponding lower(btrim(name)) unique index (scoped to the parent for districts and municipalities). The index is the real guarantee; the pre-read exists only so the operator gets a mapped 409 GEOGRAPHY_NAME_TAKEN instead of an unmapped 23505.

7. Runtime Flows

7.1 Adding a municipality

7.2 Refused rename of a referenced district

7.3 Refused delete via ON DELETE RESTRICT's actual SQLSTATE

7.4 Ordering

Every list in this hierarchy is ORDER BY name ASC, id ASC, with no query parameter to change it — unlike academic_sessions, whose sort is caller-chosen. Without the id tiebreak, two rows sharing a name under offset pagination would silently drop and duplicate rows across pages, and there is no reason for an operator scanning a fixed national list to want it reversed.

8. Caching

No cache layer sits in front of any of the three tables — unlike academic_sessions's Redis-backed list reads. Provinces and districts are unpaginated, fixed-size lookups (7 and 77 rows); a plain indexed read is already cheap enough that a cache would add invalidation surface without a measurable win. Municipalities paginate but are read scoped to one districtId in the common case, keeping most queries well under a page.

9. Permissions

Geography holds its own permission module rather than folding into PersonClassifications, even though both are seeded reference data read by every people form. PersonClassifications_READ's broad grant to staff and teachers was a decision about two flat vocabularies (ethnicity, mother tongue); extending it here would have extended the write codes too, over data every address foreign-keys into and whose rename is retroactive through the export.