PM design sheet — Swasti V3 form designs (authoritative)

Companion to 01-kickoff-brief.md, 03-mform-json-analysis.md, 04-mform-to-frappe-skill.md. The PM (Fahimuddin) has been maintaining this spreadsheet throughout the project lifecycle. Per the post-kickoff feedback (feedback_swasti_redesign_not_mirror.md), this sheet is the design authority over the V2 form JSONs. Where they conflict, sheet wins.

Source: ~/Dhwani/swasti-mform-migration/raw/pm-design/pm-form-design.xlsx (116 KB, 12 tabs). Extracted per-tab JSON + Markdown: ~/Dhwani/swasti-mform-migration/raw/pm-design/extracted/.

1. Headline findings

1. The new Livelihood program design is fully specified here. This is the only authoritative source for the 4th program — V2 has no analog. ~22 questions across Section 2 (SHG/FPCL eligibility) + Section 3 (Follow-up 1 - goat purchase + market channels) + later sections (loan repayment monthly follow-ups). Different domain entirely (SHG/FPCL/livestock/microfinance).
2. Health Screening is being redesigned, not just migrated. BMI/HB/BP/RBS thresholds rewritten in HS Logics tab; BP scoring switched to “higher of Sys/Dia → 6-status outcome matrix” (BP New Logic); 12+ DRIS comments flag specific V2 quirks for change (“not calculated in decimals”, “for normal added the range 18.5-22.9”, “corrected range by adding custom regex”, etc.).
3. (formId, order) → fieldname registry confirmed. HS Statuses and card details row 3 explicitly: Health Screening (1010) || Current Status (order 36). The PM has been thinking in this exact join key — A16.1 design is right.
4. Status workflow is consolidated and renumbered. Status of Apply For Scheme Docu lists 16 Scheme statuses + 11 Document statuses with Sl. no, Open/Closed flag, trigger logic — single source of truth for closure terminals. Replaces the V2’s scattered terminal-status array (closedItself in form 1011).
5. Custom-fields catalog in Apply for Scheme and Apply For Document enumerates every value for Occupation/Marital/Religion/Caste/Civic IDs. Partially resolves A14b — these masters are here, the 107 schemes themselves are not.
6. A14a (Kannada/Telugu translations) — NOT resolved here. Only English in the entire sheet.
7. Validation language is plain English, not regex. “0-160 KG”, “10 Digits”, “Cannot be a Future Date” — needs translation to Frappe mandatory_depends_on / regex / validate() checks. The conversion skill’s validation-layer-matrix.md is the reference.

2. Tab inventory

3. Common form-design column model

All form-design tabs (Health Screening, Livelihood) share this column shape:

Mapping into the conversion skill’s output:

4. Health Screening (form 1010 in V2 JSON) — design vs. V2 deltas

138 rows in the sheet vs. 90 questions in V2 JSON 1010.json. The design has strictly more structure than the V2 dump.

Section structure (per design)

1. 1.0 Member Details (12 fields, all Auto populate from Member Form) — Name, Father’s/Spouse, DOB, Age, Address, Phone, Gender, Religion, Occupation, Annual Income, Marital, Caste, Disabled, plus 1.12 Are you Pregnant? (visible if Gender=Female) and 1.13 Blood Group (+/-).
2. 2.0 BMI Calculation Section — Weight (0–160 kg, dec≤2), Height (0.1–2.5 m, dec≤2), BMI (auto), BMI Status Adults (auto, 5 statuses), BMI Status Child (auto, 4 statuses).
3. 3.0 Screening — HB Value (0–20, dec≤1) + HB Status (auto), BP Value 1 with SBP (0–250) + DBP (60–110) + BP Status (auto), 1st Diabetes Level / RBS (50–550, no decimals).
4. (implicit) 4.0 Initial determination — first closure rules (“Close the Screening Form, no need for Follow-ups” If HB status = No Anaemia AND…).
5. 5.0 Follow-up 1 (referral centre visit) — Doctor’s screening values, Status of Follow-up (auto from values), close conditions.
6. 6.0 Follow-up 2 (medication adherence) — Q on regular meds, side effects multi-select, refer to Doctor, diet plan, education, “Do you want to do 3rd Follow-up?”.
7. 7.0 Follow-up 3 — diet/exercise compliance, education provided.
8. 8.0 Re-screening — Auto-populate prior values, re-measure, re-status (matrix at R130-R138 — HB×BP×RBS → Status outcome).

Closure logic (extracted)

• “If HB status - No Anaemia OR BP status - Normal OR RBS status - Normal” → close, status = Member has normal health status (per HS Statuses row 3).
• 8 outcome rows (R131-R138) define the final 3-axis status matrix (HB × BP × RBS) → 5 final form statuses (“Member Under Medication and Improved”, “Managing”, “Needs Close Monitoring”, etc.).

DRIS comments visible (open questions to PM)

• “moved here” (1.13 Blood Group placement)
• “for normal added the range as 18.5–22.9” (BMI threshold)
• “corrected range by adding custom regex” (height)
• “not calculated in decimals” / “not recorded in decimal” (RBS, BP)
• “which BMI standard WFH or WFA, also on further agewise classification of bmi statuses”
• “are we not going to profiling child less than 5 yr olds”
• “why logic needed”, “why only Hb here”, “clarify statuses why need so many”
• “please add statuses” (5.1.3 Follow-up status)
• “on need to add here” (8.1 Status of Screening)

Deltas vs. V2 JSON 1010

• V2 has 90 questions, design has more sub-questions (bottom-line: design splits some computed/group-show rows; V2 inlines).
• V2 self-link via getDynamicOption on 1011. Design replaces with explicit “Follow-up 1 / 2 / 3 / Re-screening” sections, each with Auto populate from Follow-up 1 / Group show. In V3 these become a parent record + child audit-log entries (D3).
• V2 stores HB/BP/RBS status as user-selected enum. Design says Auto Calculate / Single select — server-side compute from numeric values, not user-pick. The lookup tables are in HS Logics.

5. Livelihood (NEW PROGRAM — only authoritative source)

120 rows in the sheet. No V2 JSON exists for this program — design is canonical.

Section structure

1. 1.0 Member Details (8 fields, all Auto populate from Member Form) — same pattern as HS.
2. 2.0 SHG eligibility & loan:
   • 2.0 Are you a Member of SHG? (Yes/No → if No, close form: “Member not in SHG - Closing the Form”)
   • 2.1 Name of SHG (Text, skip if 2.0 = No)
   • 2.2 Since when you are member of SHG (Number, 0-30 years)
   • 2.3 Are you a shareholder of Meshavaram FPCL?
   • 2.4–2.10 (omitted for brevity — likely additional eligibility/profile)
   • 2.11 Have you participated in a village-level awareness campaign on goat rearing?
   • 2.12 Have you received a revolving fund loan from the project?
   • 2.13 Date (DD/MM/YYYY) of receipt (Date, skip if 2.12 = No)
   • 2.14 Amount Received (Number, default 10000)
   • 2.15 When you want to do the Follow-up? (Single select: 7 / 10 / 15 days)
   • Closure: 2.12=Yes → “Member received Loan - Moving to Follow-up”; 2.12=No → close.
3. 3.0 Follow-up 1 - Livelihood (“should be completed only once”, monthly cadence based on date of receipt of loan):
   • 3.1 Count of male/female goats purchased using revolving fund (Multi-select Male/Female)
   • 3.2 Number of male goats (0-5, skip if 3.1=Female only)
   • 3.3 Number of female goats (0-5, skip if 3.1=Male only)
   • 3.4 Goat insurance done? (Yes/No)
   • 3.5–3.6 Which market channels do you use to sell goats? (Multi-select: Local Market, Broker, …)
4. (implicit later sections) monthly loan-repayment follow-ups.

Key rule

• Livelihood Follow-up 1 is single-instance (“should not allow multiple”). This is different from HS/Scheme self-link multi-instance follow-ups. V3 must enforce this constraint.
• Monthly cadence of loan-repayment follow-ups — derived from the receipt date (2.13).

Implications for the import pipeline (A16)

• A16.1 (registry): No V2 JSON for Livelihood, so no (formId, order) registry needed for it. Doctype is built directly from this sheet.
• A16.2 (skill run): Skip the skill for Livelihood — author Doctype directly from PM design.
• A16.4 (master data): No new masters introduced beyond what Member already provides + the SHG/FPCL/market dropdowns enumerated in this tab.

6. HS Logics — calculation rules (design authority for compute fields)

Tab structure: 4 lookup-table groups side-by-side: BMI / Hemoglobin / Blood Pressure / Blood Sugar.

BMI (Adults, ≥18)

Hemoglobin (4 cohorts × 5 statuses)

• Children (0–14 years), M/F: No Anemia ≥11.0, Mild 10.0–10.9, Moderate 7.0–9.9, Severe <7.0, High Hemoglobin >18.0
• Men (≥15), Male: ≥13.0 / 11.0–12.9 / 8.0–10.9 / <8.0 / >18.0
• Women (≥15), Female: ≥12.0 / 11.0–11.9 / 8.0–10.9 / <8.0 / >18.0
• Pregnant Female: ≥11.0 / 10.0–10.9 / 7.0–9.9 / <7.0 / >18.0

Blood Pressure (13+, M/F)

• Systolic ≤80 AND Diastolic ≤70 → Low
• Systolic >100 AND ≤120 AND Diastolic >60 AND ≤80 → Normal
• Systolic >120 AND ≤139 AND Diastolic … → High Normal
• Systolic ≥140 AND ≤159 (Pregnant: ≥140 + Diastolic constraints) → Slightly High
• (continued — 6 statuses total: Low / Normal / High Normal / Slightly High / Moderate High / Severe High)

Blood Sugar (all ages)

• Single set of thresholds — extract from columns 22+ when full-rendering the sheet.

A16 implication: these tables drive a server-side compute helper per metric. Encode as Python lookup in Frappe app (NOT relying on JSON-encoded thresholds in V2 — those are wrong per DRIS comments).

7. BP New Logic (redesign — supersedes V2 BP encoding)

Tab opens with: Result = Higher value between Sys & Dia.

6 lookup tables, one per Systolic level (1=Low, 2=Normal, 3=High Normal, 4=Slightly High, 5=Moderate High, 6=Severe High). Each has columns Sys, Dia, Result. The result is always max(Sys_level, Dia_level).

Concrete rule:

sys_level = bucket(systolic)   # 1..6
dia_level = bucket(diastolic)  # 1..6
bp_status_level = max(sys_level, dia_level)
bp_status = STATUS_NAMES[bp_status_level]

The 36-row matrix in the sheet is a regression fixture — every (sys_level, dia_level) → result is enumerated. Use it as-is for unit testing the Frappe BP compute.

8. Workflow status taxonomy (Status of Apply For Scheme Docu)

Single source of truth for closure terminals — supersedes V2 form 1003’s closedItself array.

Apply for Scheme — 16 statuses

Apply for Document — 11 statuses

Note the “3rd time” repeat-count rule — both workflows close after a 3rd consecutive delay/non-receipt. Encode as a delay-counter in the audit-log (A16.3).

9. Apply for Scheme + Apply for Document workflow design

89 rows. Tab opens with: Apply for SP Workflow - Documented as in version 4.11.4 of Diceflow — confirms the V2 platform name is Diceflow (mForm V2 = Diceflow v4.11.4).

Custom Fields catalog (resolves part of A14b — these masters are HERE)

Workflow shape

• User Role: Individual Facilitator executes the workflow.
• Step 1: Select Member.
• Step 2: Show Details collected from Add Member workflow + previously stored in Diceflow. Pull Occupation Status, Annual Income, … from Member configuration.
• Step 3+ (continued) — questionnaire steps Q.14, Q.15, Q.17, Q.20, Q.24 referenced in the status taxonomy above. These Q numbers should map to V2 form 1002 (Scheme Application) + 1003 (Scheme Followup) order values — verify on registry build.

Validations (notable)

• “If Date of Birth or Gender of the Member is not stored in the Member configuration, …” (validation rule — confirm completion behaviour)
• Migrant Status, Disability Type, Own Land status are deliberately NOT captured in the Apply for Scheme workflow (per row 2 — done in I4WE separately).

Document closure rule (R89)

• “If No, Close Workflow, Document Not received, Enter Reason” — final question on the document follow-up.

10. Hierarchy + role model

12 rows. Phase 1 + Phase 2 organisational tree:

Phase 1

Phase 2 — same shape, different role names per level

Implication for Frappe permissions / scoping:

• This is the role-based access model. Different from rel-mis’s User Program Assignment + UPA Geography which scopes by geography hierarchy.
• Per the feedback memory (feedback_swasti_rel_mis_reference.md), do NOT lift rel-mis’s UPA model for Swasti. This sheet’s role-by-level model is the design.
• A V3 design call: Frappe Role doctype mapping per level + standard Frappe permission rules (or a thin custom User Role Assignment doctype if multi-program is needed).

11. Add Member Workflow + Update Member

Add Member (28 rows) — member-create form

Update Member (5 rows)

• Step 1: Enter Name (while firing workflow)
• Step 2: Lookup for the Member with this Name for the Location of the User ← important: lookup is scoped to the user’s location
• Step 3: Display the values stored as defaults
• Step 4: All fields except Name can be updated and stored back
• Step 5: (continued)

Note “for the Location of the User” — this is the geography scoping model in plain English. It’s user-scoped (i.e., scoped to the user’s assigned location), not a UPA-style multi-geography expansion. Confirms our feedback memory: simpler than rel-mis.

12. Resolution of open questions from §10 of the brief

13. Mapping to A16 import-pipeline jobs

14. Action items emerging from the sheet

15. Open questions surfaced by the sheet

1. The DRIS-flagged BMI standard ambiguity — WFH (Weight-for-Height) vs. WFA (Weight-for-Age) for child BMI. Critical for the BMI Status (Child) compute helper. Open with PM.
2. Profiling members under 5 years — per row 25 DRIS comment. If yes, need a separate <5yr BMI cohort. If no, hard-fail on attempt.
3. Civic IDs list — sheet says “List of 9” but doesn’t enumerate. Likely matches kickoff screenshots 30-civic-id-multiselect-35min.jpg / 12-civic-id-doc-33min.jpg — confirm.
4. Phase 1 vs Phase 2 — sheet defines two organisational hierarchies. When does Swasti switch phases? Both must be supported, or one is forward-looking?
5. “For the Location of the User” in Update Member — single location or all assigned locations? If multiple, this is a P1/P2 nuance the design hasn’t fully spelled out.
6. Livelihood Follow-up 1 single-instance constraint — what error/UX when a user attempts a 2nd? Frappe doesn’t enforce single-instance child records natively; needs custom before_insert hook.
7. Rare input types 13/29/30 (from 03-mform-json-analysis.md) — not addressed in this sheet. Likely safe to defer until skill run encounters them.

16. References

• Source xlsx: ~/Dhwani/swasti-mform-migration/raw/pm-design/pm-form-design.xlsx
• Per-tab JSON+MD extracted: ~/Dhwani/swasti-mform-migration/raw/pm-design/extracted/
• Cross-references:
  • 01-kickoff-brief.md §10 — import pipeline plan, A14–A18 action items
  • 03-mform-json-analysis.md — V2 form JSON shape, 13 forms, self-link decoder
  • 04-mform-to-frappe-skill.md — conversion skill, validation _id table, four-files-in-parity rule
  • 04a-validation-source-resolution.md — A14c resolution
  • 02-reliance-frappe-context.md — rel-mis Frappe shape (REFERENCE; do not lift UPA/multi-geography model)
• feedback_swasti_redesign_not_mirror.md — V2 forms are content source, not design authority