Skip to main content

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
  • avgConsumption6Months must 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
  • outsideTCRadius must match GPS — updated whenever GPS is modified
  • previousMonthLossPercentage and previousToPreviousMonthLossPercentage updated on adjacent month docs when loss% changes
LLDRelationship
LLD-ai-audit-run.mdAI audit eligibility, Set A/B/C classification, computation logic
LLD-ai-audit-freeze.mdFreeze cascade — what happens after user accepts AI suggestions
LLD-ai-audit-schemas.mdai-audit-result and ai-audit-installation schemas
LLD-ai-audit-fetch.mdTable 1 (Tagging) and Table 2 (Remarks) pagination
LLD-seed-ai-audit-data.mdSeed API pattern reference

2. Endpoints

RouteMethodPurpose
POST /seed/prepare-demo-dataPOSTModify section 187 data for demo
POST /seed/restore-demo-dataPOSTRestore section 187 data from _demoBackup

Both routes require SUPER_ADMIN authentication.


3. Section & Data Profile

PropertyValue
Section code187
Section nameNR- UNIT-2
Total TCs1,097
Total installations15,474
AI eligible TCs90
Available months5: 2025-09 through 2026-01
Primary demo month2025-12
GPS spread7.9 km × 7.4 km
Avg installations per TC14.1
outsideTCRadius=true3,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.installationCount is not populated (0/null) — must be recomputed
  • Remark distribution: 59.7% emin > 0 (abnormal), <1% MNR/unbilled/doorlock
  • avgConsumption6Months populated 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:

  1. Have valid GPS coordinates
  2. Exist across all 5 months (2025-09 through 2026-01)
  3. Have ≥10 tagged installations (for realistic AI audit)
  4. Have ≥2 nearby outsideTCRadius=true installations within 2km (for Set C candidates)

4.2 Categories & Distribution

CategoryCountBefore AI Audit Loss%After AI Audit Loss%demoWeight Range
High loss1050-70%10-15%Distributed across 30-1
Moderate loss1015-30%5-12%Distributed across 30-1
Normal loss100-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 <= 10 with "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:

PageHighModerateNormaldemoWeight Range
13-43-43-430-21
23-43-43-420-11
33-43-43-410-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:

  1. Set B (UNTAG): Some tagged installations are GPS-far (>2km) — AI suggests untagging. These have LOW consumption → removing them barely changes installationConsumption.
  2. Set C (TAG): Nearby installations from other TCs (within 2km, outsideTCRadius=true) — AI suggests tagging. These have HIGH consumption → adding them significantly increases installationConsumption.
  3. Remarks (avg consumption): Some tagged installations have remark flags (MNR, doorlock, etc.) with consumption: 0 but avgConsumption6Months > 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:

  1. 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
  2. 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 outsideTCRadius remains false since they're still tagged to this TC — outsideTCRadius is relative to their own TC)
    • Keep remaining installations as Set A (within 2km)
  3. Assign consumption values to tagged installations:

    • Set A installations: distribute most of IC_before across them
    • Set B installations: assign LOW consumption (≤50 units each) — so removing them barely affects loss%
    • Total across all tagged installations = IC_before
  4. 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)
  5. 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
    • Compute avgConsumption6Months from actual consumption in OTHER months for each
    • These installations contribute 0 to current installationConsumption but avgConsumption6Months to AI-computed consumption
  6. Update TC document fields:

    • installationConsumption: sum of all tagged installation consumption
    • installationCount: count of tagged installations
    • lossPercentage: computed from formula
    • billingCount: recount all remark flags across tagged installations
    • auditStatus: "AUDITED" (loss% is not null)
    • demoWeight: assigned value (30 to 1)
    • Recompute totalInstallationSanctionedLoadInKVA, revenueParams, overloaded
  7. Update adjacent month loss fields:

    • On M+1 doc: previousMonthLossPercentage = this month's lossPercentage
    • On M+2 doc: previousToPreviousMonthLossPercentage = this month's lossPercentage

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 demoWeight on TC docs
  • Ensure installationCount and billingCount are 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.coordinates on the installation doc
  • outsideTCRadius remains 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 RemarkFields to SetFields to Unset/Zero
MNRmnr: true, consumption: 0emin: 0
DoorlockdoorLock: true, consumption: 0emin: 0
Unbilledunbilled: true, consumption: 0emin: 0
Vacantvacant: true, consumption: 0emin: 0
Zero ConsumptionzeroConsumption: true, consumption: 0emin: 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:

  1. Must already have outsideTCRadius: true in the DB
  2. Must have GPS within 2km of the demo TC (verify with haversine)
  3. If their consumption is too low, scale up proportionally
  4. Do NOT change their tcId — they remain tagged to their current TC. The AI audit discovers them via the outsideTCRadius: true + proximity query.

7. Transformer Document Updates

For each of the 30 TCs, across all 5 months:

7.1 Fields Updated

FieldSource
installationConsumptionSum of tagged installations' consumption
installationCountCount of tagged installations
lossPercentageComputed from formula
auditStatus"AUDITED" if lossPercentage is not null
billingCount.*Recounted from tagged installations' remark flags
revenueParams.*Recomputed from tagged installations
overloadedtotalInstallationSanctionedLoadInKVA > tcCapacity
totalInstallationSanctionedLoadInKVASum of tagged installations' sanctionedLoad.kw
demoWeightAssigned value (1-30)
_demoBackupOriginal 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 MonthM+1M+2
2025-092025-102025-11
2025-102025-112025-12
2025-112025-122026-01
2025-122026-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

  • fetchTransformers for 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
  • meterConstant unchanged 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
  • outsideTCRadius consistent with actual GPS distance from installation's own TC

12.6 Remarks & Avg Consumption (Table 2)

  • Remarked installations have consumption: 0 for that month
  • avgConsumption6Months matches average of actual consumption from other months
  • At least 3-4 different remark types per high-loss TC
  • aiSuggestsConsumption will be true for remarked installations with avgConsumption6Months > 0

12.7 Adjacent Month Consistency

  • previousMonthLossPercentage on month M matches lossPercentage of same TC in month M-1
  • previousToPreviousMonthLossPercentage on month M matches lossPercentage of 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 _demoBackup or demoWeight fields 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.js
  • api/src/entities/audit/helpers/gpsConstants.js (GPS_RANGE_THRESHOLD_METERS, computeDistanceMeters)

14. Error Handling

ScenarioStatusResponse
Demo data already prepared (TCs have _demoBackup)400"Demo data already prepared. Restore first."
No eligible TCs found400"Could not find 30 eligible TCs in section 187"
No demo data to restore (_demoBackup not found)400"No demo data to restore"
Auth failure401/403Standard auth errors
DB error500DatabaseError

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 bulkWrite for 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-result docs exist for demo TCs, the AI audit will refuse to run ("already frozen" or stale data). Always clean up before demo.
  • installationCount on transformer is currently 0/null for section 187. The prepare endpoint MUST populate this correctly — the UI displays it.
  • installation.tcId in section 187 matches transformer.id directly (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.