Data model

This page is for developers who need to read or write SQL against the SBDMS database. The schema lives in one file, backend/Sbpdms.Backend/Services/SchemaManager.cs. Read that file when this page and the code disagree; the code wins.

How the schema is managed

There are no migration files. On every start the backend runs EnsureSchemaCreatedAsync (a CREATE TABLE IF NOT EXISTS per table) and then RunMigrationsAsync, which reads PRAGMA table_info and adds missing columns with ALTER TABLE ... ADD COLUMN. Both are safe to run repeatedly.

Rules that follow:

• Add, never drop or rename. SQLite cannot cheaply rebuild tables, so old columns stay.
• Dates are TEXT in ISO form. Booleans are INTEGER 0 or 1. Measurements are REAL.
• Soft delete. Children, guardians, staff, camp days and org sources have IsDeleted; every user-facing query filters IsDeleted = 0. Nothing is hard-deleted because the audit log and dose history point at those rows.
• Status columns are free text. The vocabularies below are enforced in the API, not the database.

Children and guardians

Table	Purpose	Key columns
Children	One row per child.	Name, Gender, Dob, Upc (unique, ABCD-1234), Status, MedicalHistory (checkbox tags), PriorDoseCount + PriorDoseSource (paper doses before SBDMS), PreferredCampSiteId, VerifiedAtCampSiteId, VerifiedBy, VerifiedAt, RegisteredBy (username or Self), Source + OrgSourceId + Class (roster provenance), IsDeleted.
Guardians	One row per adult.	Name, Relation, Phone1, Phone2, current address (RegionId, DistrictId, LocalLevelId, WardNo, Tole, Address, IsCurrentAddress), optional Permanent* address columns, Email, Upc (unique), IsDeleted.
ChildGuardians	Many-to-many link.	ChildId, GuardianId, Relation, IsPrimary. A child can have several guardians; one is primary.
ChildMonthRemarks	Follow-up notes per month.	ChildId, MonthDate (first of month), Remark, CreatedBy.
ChildMedicalHistory	Append-only medical notes.	ChildId, EntryText, CreatedBy, CreatedAt.

Children.Status vocabulary: Self Registered (family registered, not yet checked by staff), Verified (staff confirmed the record at a camp), Org Verified (pulled from an organisation roster). Reports never mix the last two.

A UPC (Unique Participant Code) is unique across both Children and Guardians. It is generated by the client and printed as the QR code.

Reference data and camp sites

Table	Purpose
Regions, Districts, LocalLevels	Nepal’s administrative hierarchy. Each has Name, NepName, Code. LocalLevels.Type is Municipality or Gaupalika.
CampSites	A place where camps run. DistrictId, LocalLevelId, Name, NepName, Code.
HPV	Every user: health workers and admins. Username (unique), Password (BCrypt), Role, Code (unique, used by the QR flow), IsInternal, LoginEnabled, IsDeleted, plus name and contact fields.
CampSiteHPVs	Which staff are assigned to which site. This table is the source of truth for scoping; the JWT is never trusted for it.
Units, Ingredients, ExclusionCriteria, DoseRules	Lookups used by dose preparation and screening. DoseRules has MinAge, MaxAge (whole months) and NoOfDrops.

HPV.Role values: SuperAdmin, GeneralAdmin, LocationAdmin, GeneralUser.

Dose preparations and camp days

Table	Purpose	Key columns
DosePreparations	One prepared batch of drops.	CampSiteId, Dbc (batch code, unique), DateTimeOfPrep, TotalQuantity, UnitId, TargetCampDayDate, LockedCampDayDate, Status.
DosePrepIngredients	What went into a batch.	DosePrepId, IngredientId, BatchNo, Quantity, UnitId.
DosePrepPersonnel	Who prepared it.	DosePrepId, HpvId, Role.
CampDays	One scheduled day at one site.	CampSiteId, StartDate, EndDate, Status, CreatedBy, IsDeleted.
CampDayDoses	Batches assigned to a day.	CampDayId, DosePrepId.
CampDayHPVs	Staff rostered on a day.	CampDayId, HpvId.
CampDayAudit	Human-readable history of a camp day.	CampDayId, Action, ChangedBy, Details.

DosePreparations.Status starts as Prepared; locking a batch to a camp day fills LockedCampDayDate. CampDays.Status vocabulary: Scheduled, Ongoing, Completed, Cancelled.

Dose sessions and dose records

The heart of the system is the sessions table, written by the dose-session controller. Its name is a legacy identifier kept for compatibility and is not printed here. One row is one visit of one child to one camp day with one staff member.

Key columns:

• Who and where: HpvId, CampDayId, ChildId, AccompanyingGuardianId (or AccompanyingOtherName + AccompanyingOtherRelation when the adult is not a registered guardian; the id is then 0 and treated as null).
• Screening: Temperature, Weight, Height, BMI, MUAC, HeadCircumference, Vitals, MedicalHistoryTags, MedicalHistoryNotes, ExclusionCriteria, ExclusionOther, ExclusionAdversityLevel (Mild, Moderate, Severe), AdverseEffect, Remarks.
• Timing: ScreeningStartedAt, ScreeningCompletedAt, PrashanStartedAt, PrashanCompletedAt.
• Outcome: Status, Batch, IsPostPrashanEntry (1 when a paper record was typed in later).
• Bookkeeping: IsLocked, IsLatest, CreatedAt.

Status vocabulary:

Status	Meaning
Started	Screening has begun. The row is open (IsLocked = 0).
Completed	The child received the drops. Locked.
FilteredOut	The child was screened and excluded. Locked.

The two-row pattern. When a health worker starts screening, the API inserts a Started row. If the wizard later submits with that session id, the same row is updated to Completed or FilteredOut. But if the wizard submits without a session id (for example after a page reload), the API first sets IsLatest = 0 on every existing row for that child and camp day, then inserts a fresh Completed row with IsLatest = 1. The old Started row is not deleted. So a child can legitimately have two rows for one camp day: an old Started with IsLatest = 0 and the real Completed with IsLatest = 1. Always filter on IsLatest = 1 when you want “the row that counts”.

Screened but not dosed. A child was screened and then nobody pressed the final button. In SQL this is a row where:

vs.Status = 'Started'
AND vs.IsLatest = 1
AND NOT EXISTS (
  SELECT 1 FROM <sessions table> v2
  WHERE v2.ChildId = vs.ChildId AND v2.CampDayId = vs.CampDayId
    AND v2.Status IN ('Completed', 'FilteredOut'))

The NOT EXISTS guards against rows created before IsLatest existed. The camp-day analytics tile, the Anusuchi-3 count and the incomplete endpoint all use this exact definition.

A second, older dose-record table (also a legacy name) holds one row per dose given: ChildId, Batch, DoseSequence (1, 2, 3…), DateAdministered, Location, Status (Presented or Missed). The API inserts into it whenever a session becomes Completed; DoseSequence is PriorDoseCount plus completed sessions plus one. For paper entries DateAdministered is the date the user typed, not today.

CampSiteChangeRequest records a request to move a session between sites (SessionId, FromCampSiteId, ToCampSiteId).

QR

ScannedQR is the handshake table for the two-step scan flow: HpvId, ScannedEntityId, ScannedType (Child or Guardian), IsConsumed, ExpiresAt (five minutes after creation).

Audit

AuditLog is append-only: TableName, Pk, Action, Actor, ActorRole, BeforeJson, AfterJson, Endpoint, Ip, At.

VerificationSiteMismatches records each time a staff member was warned that a child’s preferred site differs from the site they are at: ChildId, ChildUpc, PreferredCampSiteId, VerifyingCampSiteId, FinalCampSiteId, Decision (proceeded, changed, dosed-here, declined), Actor.

Organisation rosters

Table	Purpose
OrgSources	One school, orphanage or other organisation that brings a roster. OrgName, CampSiteId, address fields, CreatedBy, IsDeleted.
OrgChildren	Append-only staging of every imported roster row. OrgSourceId, ImportSessionId, ImportedAt, roster fields (Name, GuardianName, Dob, Age, Gender, Class, PrevDoseCount…), ChildUpc, GuardianUpc, MoreData (extra columns as JSON).

Each import inserts a fresh batch; older batches are kept but never shown. Pick the latest batch by ORDER BY Id DESC, not by ImportedAt (same-second imports collide). Each roster row is also pulled into Children and Guardians with Source = 'org_roster'.

The admin CSV import (participants and staff) has no table of its own: it writes straight into Children, Guardians, ChildGuardians and HPV, and keeps job progress in memory.

Indexes

Hot-path indexes exist on CampSiteHPVs(HpvId), CampDayHPVs(HpvId), ChildGuardians(GuardianId), the sessions table on ChildId, CampDayId and (CampDayId, Status), CampDays(CampSiteId), Children(Status) and Children(VerifiedAtCampSiteId). If a new query filters a large table on a new column, add an index in RunMigrationsAsync after the column exists.