LLD: Prepare Demo Data API
1. Overview
Two admin-only endpoints that prepare and restore section 187 (NR- UNIT-2) for the Mumbai demo. The prepare endpoint modifies existing real data (no inserts or deletes) to create a compelling AI Audit demo flow. The restore endpoint reverts all changes from _demoBackup.
Demo Narrative
A user opens the AI Audit page → sees TCs sorted by demoWeight → page 1 shows a mix of high-loss, moderate-loss, and normal TCs → clicks a high-loss TC (50-70%) → runs AI Audit → AI suggests GPS-based retagging (Table 1) and avg consumption substitution for remarked installations (Table 2) → loss drops to 10-15% → user freezes → data committed.
Key Constraints
- Update-only — no inserts or deletes to any collection
- Never modify
meterConstant— treat as immutable avgConsumption6Monthsmust be real — computed from actual consumption across months- All numbers must be mathematically consistent — loss formula, billing counts, installation sums
- GPS distances must be real — haversine-computed, not faked
outsideTCRadiusmust match GPS — updated whenever GPS is modifiedpreviousMonthLossPercentageandpreviousToPreviousMonthLossPercentageupdated on adjacent month docs when loss% changes
Related LLDs
| LLD | Relationship |
|---|---|
LLD-ai-audit-run.md | AI audit eligibility, Set A/B/C classification, computation logic |
LLD-ai-audit-freeze.md | Freeze cascade — what happens after user accepts AI suggestions |
LLD-ai-audit-schemas.md | ai-audit-result and ai-audit-installation schemas |
LLD-ai-audit-fetch.md | Table 1 (Tagging) and Table 2 (Remarks) pagination |
LLD-seed-ai-audit-data.md | Seed API pattern reference |
2. Endpoints
| Route | Method | Purpose |
|---|---|---|
POST /seed/prepare-demo-data | POST | Modify section 187 data for demo |
POST /seed/restore-demo-data | POST | Restore section 187 data from _demoBackup |
Both routes require SUPER_ADMIN authentication.
3. Section & Data Profile
| Property | Value |
|---|---|
| Section code | 187 |
| Section name | NR- UNIT-2 |
| Total TCs | 1,097 |
| Total installations | 15,474 |
| AI eligible TCs | 90 |
| Available months | 5: 2025-09 through 2026-01 |
| Primary demo month | 2025-12 |
| GPS spread | 7.9 km × 7.4 km |
| Avg installations per TC | 14.1 |
| outsideTCRadius=true | 3,021 (19.5%) |
Current Data Characteristics
- All eligible TCs have
meterConstant: 1 - Current loss% range: 90-98% (agricultural/IP TCs with high TC consumption, low tagged installation consumption)
transformer.installationCountis not populated (0/null) — must be recomputed- Remark distribution: 59.7%
emin > 0(abnormal), <1% MNR/unbilled/doorlock avgConsumption6Monthspopulated for ~60% of installations- Dense GPS clustering: 143-406 nearby TCs within 2km per TC
4. TC Selection & Categories
4.1 Selection Criteria
From the 90 AI-eligible TCs in section 187, select 30 TCs that:
- Have valid GPS coordinates
- Exist across all 5 months (2025-09 through 2026-01)
- Have ≥10 tagged installations (for realistic AI audit)
- Have ≥2 nearby outsideTCRadius=true installations within 2km (for Set C candidates)
4.2 Categories & Distribution
| Category | Count | Before AI Audit Loss% | After AI Audit Loss% | demoWeight Range |
|---|---|---|---|---|
| High loss | 10 | 50-70% | 10-15% | Distributed across 30-1 |
| Moderate loss | 10 | 15-30% | 5-12% | Distributed across 30-1 |
| Normal loss | 10 | 0-10% | N/A — cannot run AI audit (Precondition 5) | Distributed across 30-1 |
Precondition 5 constraint: The AI audit API rejects TCs with
lossPercentage >= -5 && lossPercentage <= 10with "AI audit is not necessary" (aiAuditTC.js:103). Normal-loss TCs exist for page variety only — the demo user should not attempt to run AI audit on them. Only high-loss and moderate-loss TCs are AI-auditable.
4.3 Page Distribution
Each page of 10 TCs (sorted by demoWeight desc) contains a mix:
| Page | High | Moderate | Normal | demoWeight Range |
|---|---|---|---|---|
| 1 | 3-4 | 3-4 | 3-4 | 30-21 |
| 2 | 3-4 | 3-4 | 3-4 | 20-11 |
| 3 | 3-4 | 3-4 | 3-4 | 10-1 |
5. Data Modification Strategy
5.1 Core Formula Reference
lossPercentage = ((tcConsumption × meterConstant) - installationConsumption) / (tcConsumption × meterConstant) × 100
Since meterConstant is never modified (always 1 for this section):
lossPercentage = (tcConsumption - installationConsumption) / tcConsumption × 100
installationConsumption = tcConsumption × (1 - lossPercentage / 100)
5.2 High-Loss TCs (50-70% → 10-15%)
Goal: Before AI audit, TC shows 50-70% loss. After AI audit + freeze, loss drops to 10-15%.
How the loss drops via AI audit:
- Set B (UNTAG): Some tagged installations are GPS-far (>2km) — AI suggests untagging. These have LOW consumption → removing them barely changes installationConsumption.
- Set C (TAG): Nearby installations from other TCs (within 2km, outsideTCRadius=true) — AI suggests tagging. These have HIGH consumption → adding them significantly increases installationConsumption.
- Remarks (avg consumption): Some tagged installations have remark flags (MNR, doorlock, etc.) with
consumption: 0butavgConsumption6Months > 0— AI substitutes avg consumption → installationConsumption increases.
Combined effect: installationConsumption increases → loss% drops from 50-70% to 10-15%.
Step-by-step for each high-loss TC:
Given a TC with tcConsumption = T:
-
Calculate target installationConsumption values:
- Before:
IC_before = T × (1 - targetLoss_before / 100)(e.g., T × 0.4 for 60% loss) - After:
IC_after = T × (1 - targetLoss_after / 100)(e.g., T × 0.88 for 12% loss) - Deficit to be gained via AI audit:
IC_after - IC_before
- Before:
-
Classify current tagged installations:
- Compute haversine distance from TC GPS to each installation GPS
- Mark ~2-3 installations as Set B candidates: move their GPS >2km from TC (update
outsideTCRadiusremains false since they're still tagged to this TC —outsideTCRadiusis relative to their own TC) - Keep remaining installations as Set A (within 2km)
-
Assign consumption values to tagged installations:
- Set A installations: distribute most of
IC_beforeacross them - Set B installations: assign LOW consumption (≤50 units each) — so removing them barely affects loss%
- Total across all tagged installations =
IC_before
- Set A installations: distribute most of
-
Prepare Set C candidates (from other TCs):
- Find installations within 2km of this TC that have
outsideTCRadius: true - Ensure their consumption is HIGH enough that adding them bridges the gap:
IC_after - IC_before + SetB_consumption - Update their consumption if needed (proportionally scale up existing consumption)
- Find installations within 2km of this TC that have
-
Create remark variety on tagged installations:
- Convert 2-4 tagged installations' remark flags:
- 1 MNR:
mnr: true, consumption: 0 - 1 doorlock:
doorLock: true, consumption: 0 - 1 unbilled:
unbilled: true, consumption: 0 - 1 vacant:
vacant: true, consumption: 0
- 1 MNR:
- Compute
avgConsumption6Monthsfrom actual consumption in OTHER months for each - These installations contribute 0 to current installationConsumption but
avgConsumption6Monthsto AI-computed consumption
- Convert 2-4 tagged installations' remark flags:
-
Update TC document fields:
installationConsumption: sum of all tagged installation consumptioninstallationCount: count of tagged installationslossPercentage: computed from formulabillingCount: recount all remark flags across tagged installationsauditStatus:"AUDITED"(loss% is not null)demoWeight: assigned value (30 to 1)- Recompute
totalInstallationSanctionedLoadInKVA,revenueParams,overloaded
-
Update adjacent month loss fields:
- On M+1 doc:
previousMonthLossPercentage = this month's lossPercentage - On M+2 doc:
previousToPreviousMonthLossPercentage = this month's lossPercentage
- On M+1 doc:
5.3 Moderate-Loss TCs (15-30% → 5-12%)
Same approach as high-loss, but with smaller deltas:
- Fewer Set B installations (1-2)
- Smaller Set C additions
- Fewer remark conversions (1-2 installations)
- Net AI audit improvement: 10-18 percentage points
5.4 Normal-Loss TCs (0-10% → ~same)
Minimal modifications:
- Set
demoWeighton TC docs - Ensure
installationCountandbillingCountare accurate - May convert 1-2 installations to have remark flags for variety
- AI audit should show mostly Set A (NO_CHANGE) with minor adjustments
- Loss% change after AI audit: <3 percentage points
5.5 Month-to-Month Variation
For each TC, apply a variation factor per month to avoid identical data:
const VARIATION_FACTORS = {
"2025-09": 1.00, // baseline
"2025-10": 1.05, // +5%
"2025-11": 0.95, // -5%
"2025-12": 1.02, // +2% (primary demo month — closest to target)
"2026-01": 0.98, // -2%
};
Apply to installationConsumption and individual installation consumption values. Recompute lossPercentage accordingly. Some months may naturally have lower loss (e.g., 5%) — this is expected and adds realism.
Important: The variation applies to the "before" loss%. The "after" improvement ratio remains consistent (AI audit always improves high-loss TCs by a similar magnitude).
6. Installation Modifications Detail
6.1 GPS Modifications (Set B Creation)
For installations designated as Set B on high-loss TCs:
// Move GPS >2km from TC in a realistic direction (along existing road/cluster axis)
// Use TC GPS as reference, offset by ~2.5-3km
const offsetLat = 0.025; // ~2.8km
const offsetLng = 0.030; // ~2.5km at lat 13.5°
newCoordinates = [
originalCoordinates[0] + offsetLng, // or - for opposite direction
originalCoordinates[1] + offsetLat,
];
- Update
gpsCoordinates.coordinateson the installation doc outsideTCRadiusremains as-is (this flag is relative to the installation's own tagged TC, not the demo TC)- Store original GPS in
_demoBackup
6.2 Remark Flag Conversions
Convert some emin > 0 (abnormal) installations to other remark types:
| Target Remark | Fields to Set | Fields to Unset/Zero |
|---|---|---|
| MNR | mnr: true, consumption: 0 | emin: 0 |
| Doorlock | doorLock: true, consumption: 0 | emin: 0 |
| Unbilled | unbilled: true, consumption: 0 | emin: 0 |
| Vacant | vacant: true, consumption: 0 | emin: 0 |
| Zero Consumption | zeroConsumption: true, consumption: 0 | emin: 0 |
For each converted installation:
// Compute avgConsumption6Months from actual consumption in other months
const otherMonthDocs = await installationModel.find({
accountId: inst.accountId,
month: { $ne: currentMonth },
consumption: { $gt: 0 },
});
const avg = otherMonthDocs.reduce((sum, d) => sum + d.consumption, 0) / otherMonthDocs.length;
// Update avgConsumption6Months on the current month doc
This ensures avgConsumption6Months is computed from real data.
6.3 Consumption Redistribution
When modifying installation consumption values to hit a target installationConsumption:
// Scale proportionally from existing values
const currentSum = taggedInstallations.reduce((s, i) => s + (i.consumption || 0), 0);
const scaleFactor = targetInstallationConsumption / currentSum;
for (const inst of taggedInstallations) {
if (inst.hasRemark) continue; // remarked installations stay at 0
inst.newConsumption = Math.round(inst.consumption * scaleFactor);
}
// Adjust rounding difference on the largest installation
const actualSum = taggedInstallations.reduce((s, i) => s + (i.newConsumption || 0), 0);
const diff = targetInstallationConsumption - actualSum;
largestInstallation.newConsumption += diff;
6.4 Set C Candidate Preparation
For installations from other TCs that will become Set C:
- Must already have
outsideTCRadius: truein the DB - Must have GPS within 2km of the demo TC (verify with haversine)
- If their consumption is too low, scale up proportionally
- Do NOT change their
tcId— they remain tagged to their current TC. The AI audit discovers them via theoutsideTCRadius: true+ proximity query.
7. Transformer Document Updates
For each of the 30 TCs, across all 5 months:
7.1 Fields Updated
| Field | Source |
|---|---|
installationConsumption | Sum of tagged installations' consumption |
installationCount | Count of tagged installations |
lossPercentage | Computed from formula |
auditStatus | "AUDITED" if lossPercentage is not null |
billingCount.* | Recounted from tagged installations' remark flags |
revenueParams.* | Recomputed from tagged installations |
overloaded | totalInstallationSanctionedLoadInKVA > tcCapacity |
totalInstallationSanctionedLoadInKVA | Sum of tagged installations' sanctionedLoad.kw |
demoWeight | Assigned value (1-30) |
_demoBackup | Original values of all modified fields |
7.2 Adjacent Month Loss% Updates
After modifying a TC's lossPercentage for month M:
// Update M+1 doc
await transformerModel.updateOne(
{ id: tcId, month: nextMonth },
{ $set: { previousMonthLossPercentage: newLossPercentage } }
);
// Update M+2 doc
await transformerModel.updateOne(
{ id: tcId, month: nextNextMonth },
{ $set: { previousToPreviousMonthLossPercentage: newLossPercentage } }
);
Month adjacency for 5 months:
| Modified Month | M+1 | M+2 |
|---|---|---|
| 2025-09 | 2025-10 | 2025-11 |
| 2025-10 | 2025-11 | 2025-12 |
| 2025-11 | 2025-12 | 2026-01 |
| 2025-12 | 2026-01 | (none) |
| 2026-01 | (none) | (none) |
8. _demoBackup Structure
Stored on every modified document (transformer and installation):
{
_demoBackup: {
// Only fields that were actually changed
consumption: 450, // original consumption (installation)
lossPercentage: 95.2, // original loss% (transformer)
installationConsumption: 731, // original (transformer)
installationCount: 0, // original (transformer)
billingCount: { ... }, // original (transformer)
gpsCoordinates: { ... }, // original GPS (installation, if moved)
mnr: false, // original remark flag (installation, if converted)
emin: 150, // original emin (installation, if converted)
avgConsumption6Months: null, // original avg (installation)
// ... etc
}
}
9. fetchTransformers Sort Modification
Add demoWeight descending sort to the existing query in fetchTransformers.js:
// Current (no sort)
transformerModel.find(filter, projection).skip(skip).limit(limit).lean()
// Modified
transformerModel.find(filter, projection)
.sort({ demoWeight: -1 }) // demo TCs first, null/0 fall to end
.skip(skip)
.limit(limit)
.lean()
Note: MongoDB sorts null values last in descending order, so non-demo TCs (without demoWeight) naturally appear after all demo TCs.
10. Audit Summary Recomputation
After all modifications are complete, recompute audit summaries for section 187 across all 5 months:
const location = { sectionCode: "187", /* ...full location from any TC */ };
const months = ["2025-09", "2025-10", "2025-11", "2025-12", "2026-01"];
await Promise.all(months.map(month => recomputeAuditSummaries(location, month)));
This updates all 6 hierarchy levels (SECTION → MESCOM) for each month.
11. API Design
11.1 Prepare Demo Data
POST /seed/prepare-demo-data
Authorization: Bearer <SUPER_ADMIN_SESSION>
Body: {} (no parameters needed — all config is hardcoded)
Execution Order
Phase 1: Select 30 TCs from section 187
└── Query eligible TCs, verify GPS, month coverage, installation count
└── Categorize into high/moderate/normal
└── Assign demoWeight values
Phase 2: Backup original data
└── For each of 30 TCs × 5 months: read current fields, store in _demoBackup
└── For each affected installation × 5 months: read current fields, store in _demoBackup
Phase 3: Modify installations (per TC, per month)
└── Redistribute consumption across tagged installations
└── Move Set B candidate GPS coordinates >2km
└── Convert remark flags (emin → MNR/doorlock/unbilled/vacant)
└── Compute real avgConsumption6Months for remarked installations
└── Scale Set C candidate consumption if needed
Phase 4: Recompute transformer fields (per TC, per month)
└── installationConsumption = sum of tagged installations
└── installationCount = count of tagged installations
└── lossPercentage from formula
└── billingCount from remark flag counts
└── revenueParams, overloaded, totalInstallationSanctionedLoadInKVA
└── Set demoWeight
Phase 5: Update adjacent month loss% fields
└── previousMonthLossPercentage on M+1 docs
└── previousToPreviousMonthLossPercentage on M+2 docs
Phase 6: Recompute audit summaries
└── recomputeAuditSummaries(location, month) × 5 months
Phase 7: Clean up any existing ai-audit-result / ai-audit-installation for these TCs
└── Delete unfrozen AI audit results (so demo starts fresh)
Response
{
"success": true,
"message": "Demo data prepared successfully",
"data": {
"section": "187",
"sectionName": "NR- UNIT-2",
"months": ["2025-09", "2025-10", "2025-11", "2025-12", "2026-01"],
"tcsPrepared": 30,
"highLoss": 10,
"moderateLoss": 10,
"normalLoss": 10,
"installationsModified": 450,
"auditSummariesRecomputed": 5
}
}
11.2 Restore Demo Data
POST /seed/restore-demo-data
Authorization: Bearer <SUPER_ADMIN_SESSION>
Body: {} (no parameters needed)
Execution Order
Phase 1: Restore transformer documents
└── Find all transformers with _demoBackup in section 187
└── For each: $set original values from _demoBackup, $unset _demoBackup and demoWeight
Phase 2: Restore installation documents
└── Find all installations with _demoBackup in section 187
└── For each: $set original values from _demoBackup, $unset _demoBackup
Phase 3: Recompute audit summaries
└── recomputeAuditSummaries(location, month) × 5 months
Phase 4: Clean up AI audit staging data
└── Delete any ai-audit-result and ai-audit-installation for demo TCs
Response
{
"success": true,
"message": "Demo data restored successfully",
"data": {
"transformersRestored": 150,
"installationsRestored": 450,
"auditSummariesRecomputed": 5,
"aiAuditResultsCleaned": 2
}
}
12. Validation Checklist (Post-Prepare)
After running prepare, these must all pass:
12.1 Page Ordering
-
fetchTransformersfor section 187, any month → page 1 shows 10 TCs with demoWeight 30-21 - Mix of high/moderate/normal loss on every page
12.2 Loss% Accuracy
- For each demo TC:
lossPercentage == round(((tcConsumption × meterConstant) - installationConsumption) / (tcConsumption × meterConstant) × 100, 2) -
installationConsumption == sum of tagged installation consumption values -
meterConstantunchanged from original
12.3 Billing Counts
-
billingCount.mnr == count of tagged installations with mnr=true - Same for unbilled, vacant, zeroConsumption, doorlock, abnormal, subnormal
- Filter counts in UI match table data
12.4 AI Audit Eligibility
- All 10 high-loss TCs pass eligibility: no remark, meterConstant > 0, tcConsumption not null, lossPercentage not null, within [-100, 100], lossPercentage outside [-5, 10] (Precondition 5), auditedByAI not true
- Same for moderate-loss TCs (loss 15-30% passes Precondition 5)
- Normal-loss TCs (0-10%) are expected to fail AI audit eligibility — they are for page variety only
12.5 GPS & Tagging (Table 1)
- Set B installations: haversine distance from TC > 2000m
- Set A installations: haversine distance from TC ≤ 2000m
- Set C candidates:
outsideTCRadius: true, within 2000m of TC, tagged to different TC -
outsideTCRadiusconsistent with actual GPS distance from installation's own TC
12.6 Remarks & Avg Consumption (Table 2)
- Remarked installations have
consumption: 0for that month -
avgConsumption6Monthsmatches average of actual consumption from other months - At least 3-4 different remark types per high-loss TC
-
aiSuggestsConsumptionwill betruefor remarked installations withavgConsumption6Months > 0
12.7 Adjacent Month Consistency
-
previousMonthLossPercentageon month M matcheslossPercentageof same TC in month M-1 -
previousToPreviousMonthLossPercentageon month M matcheslossPercentageof same TC in month M-2
12.8 Audit Summaries
- Section-level summary counts match actual TC counts for each month
- Loss buckets match actual audited TC distribution
- Failed reasons match actual failed TC characteristics
12.9 Restore Integrity
- After restore: all modified fields match original values
- No
_demoBackupordemoWeightfields remain - Audit summaries recomputed to original state
13. File Structure
api/src/entities/seed/
seed.route.v1.js — Add two new route entries
controller/
seedAiAuditTestData.js — Existing
prepareDemoData.js — New: prepare handler + logic
restoreDemoData.js — New: restore handler + logic
helpers/
demoTCSelection.js — TC selection + categorization logic
demoInstallationModifier.js — Installation consumption/GPS/remark modifications
demoTransformerRecompute.js — Transformer field recomputation
Reuses existing helpers:
api/src/entities/audit/helpers/recomputeAuditSummaries.jsapi/src/entities/audit/helpers/gpsConstants.js(GPS_RANGE_THRESHOLD_METERS, computeDistanceMeters)
14. Error Handling
| Scenario | Status | Response |
|---|---|---|
Demo data already prepared (TCs have _demoBackup) | 400 | "Demo data already prepared. Restore first." |
| No eligible TCs found | 400 | "Could not find 30 eligible TCs in section 187" |
No demo data to restore (_demoBackup not found) | 400 | "No demo data to restore" |
| Auth failure | 401/403 | Standard auth errors |
| DB error | 500 | DatabaseError |
15. Performance Notes
Prepare Endpoint
- Reads: ~30 TCs × 5 months + ~400 installations × 5 months = ~2,150 reads
- Writes: ~150 TC updates + ~2,000 installation updates + 30 audit summary upserts = ~2,180 writes
- Use
bulkWritefor batch updates where possible - Estimated time: 15-30 seconds
Restore Endpoint
- Reads: Find docs with
_demoBackup(~2,150 docs) - Writes: ~2,150 updates + 30 audit summary upserts
- Estimated time: 10-20 seconds
16. Important Notes
- Phase 7 (clean up AI audit staging) is critical — if old
ai-audit-resultdocs exist for demo TCs, the AI audit will refuse to run ("already frozen" or stale data). Always clean up before demo. installationCounton transformer is currently 0/null for section 187. The prepare endpoint MUST populate this correctly — the UI displays it.installation.tcIdin section 187 matchestransformer.iddirectly (no "TC-" prefix stripping needed, unlike seed data).- The prepare endpoint is idempotent with restore — running prepare, then restore, then prepare again returns to the same demo state.
- Only 5 months exist (2025-09 through 2026-01). Do NOT reference months 2026-02 or 2026-03.