Skip to main content

LLD: AI Audit Schemas (Data Model)

1. Overview​

Defines the data model changes required for the AI Audit feature — GPS-based audit of Transformer Centres (TCs). The AI Audit flow:

  1. Classifies installations by distance from the TC (within / outside 5km radius)
  2. Suggests tagging/untagging installations based on GPS proximity
  3. Identifies installations with remarks (MNR, doorlock, etc.) and suggests using historical average consumption
  4. Stores results in intermediate/staging collections before committing ("freezing") to real data

This LLD covers only schema and data model changes — not API logic (covered in separate LLDs).

Collections Affected​

CollectionChange
ai-audit-resultNew — one doc per TC per month, stores AI audit result before freeze
ai-audit-installationNew — one doc per installation involved in an AI audit
transformerModified — 10 new fields (TC detail + meter detail properties)
installationModified — 1 new field (avgConsumption6Months) + new 2dsphere index

2. New Collection: ai-audit-result​

One document per TC per month. Stores the complete AI audit result as an intermediate/staging record before the user freezes (commits) the changes to the real transformer and installation documents.

2.1 Mongoose Schema​

import mongoose from "mongoose";

/**
* AI Audit Result — One document per TC per month (staging pattern)
*
* Unique identity: { tcId, month }
* Stores original audit values (before AI), computed AI audit values (after GPS + remarks analysis),
* and TC properties snapshot. Does not affect real data until frozen.
*
* Collection: ai-audit-result
*/

const billingCountShape = {
unbilled: { type: Number, default: 0 },
mnr: { type: Number, default: 0 },
vacant: { type: Number, default: 0 },
zeroConsumption: { type: Number, default: 0 },
doorlock: { type: Number, default: 0 },
abnormal: { type: Number, default: 0 },
subnormal: { type: Number, default: 0 },
billCancellation: { type: Number, default: 0 },
};

const revenueParamsShape = {
arr: { type: Number },
atc: { type: Number },
demand: { type: Number },
collection: { type: Number },
billingEfficiency: { type: Number },
collectionEfficiency: { type: Number },
};

const aiAuditResultSchema = new mongoose.Schema(
{
// ──── Identity ────
tcId: {
type: String,
required: true, // references transformer.id
},
tcNumber: {
type: String,
required: true,
},
tcName: {
type: String,
required: true,
},
month: {
type: String,
required: true,
validate: {
validator: (v) => /^\d{4}-(0[1-9]|1[0-2])$/.test(v),
message: "month must be in YYYY-MM format",
},
},

// ──── Location (flat, same as transformer) ────
location: {
sectionCode: { type: String, required: true },
sectionName: { type: String },
subDivisionCode: { type: String, required: true },
subDivisionName: { type: String },
divisionCode: { type: String, required: true },
divisionName: { type: String },
circleCode: { type: String, required: true },
circleName: { type: String },
zoneCode: { type: String, required: true },
zoneName: { type: String },
},

// ──── TC Properties (copied from transformer at audit time) ────
tcCapacity: { type: Number },
meterConstant: { type: Number },
meterConstantChanged: { type: Boolean, default: false },
readingDay: { type: Number, min: 1, max: 31 },
readingMR: { type: String },
gpsCoordinates: {
type: { type: String, enum: ["Point"], default: "Point" },
coordinates: { type: [Number] }, // [longitude, latitude]
},
reading: {
initialValue: { type: Number },
finalValue: { type: Number },
},

// ──── TC Detail Properties (copied from transformer) ────
tcMake: { type: String },
serialNumber: { type: String },
timsCode: { type: String },
dtlms: { type: String },
dtr: { type: String },
feeder: {
name: { type: String },
mdmCode: { type: String },
number: { type: String },
},
executionType: { type: String },

// ──── Meter Detail Properties (copied from transformer) ────
meterMake: { type: String },
meterSerial: { type: String },
ctRatio: { type: String },

// ──── Original Audit Values (before AI audit) ────
original: {
installationConsumption: { type: Number },
installationCount: { type: Number },
lossPercentage: { type: Number },
auditStatus: { type: String, enum: ["AUDITED", "AUDIT_FAILED"] },
billingCount: billingCountShape,
revenueParams: revenueParamsShape,
overloaded: { type: Boolean },
totalInstallationSanctionedLoadInKVA: { type: Number },
},

// ──── Computed AI Audit Values (after GPS + remarks analysis) ────
computed: {
installationConsumption: { type: Number },
installationCount: { type: Number },
lossPercentage: { type: Number },
auditStatus: { type: String, enum: ["AUDITED", "AUDIT_FAILED"] },
billingCount: billingCountShape,
revenueParams: revenueParamsShape,
overloaded: { type: Boolean },
totalInstallationSanctionedLoadInKVA: { type: Number },
},

// ──── TC Consumption (read-only, from transformer) ────
tcConsumption: { type: Number },
previousMonthLossPercentage: { type: Number },
previousToPreviousMonthLossPercentage: { type: Number },

// ──── Freeze Meta ────
frozen: { type: Boolean, default: false },
frozenOn: { type: Date, default: null },
},
{
timestamps: true,
collection: "ai-audit-result",
},
);

// ──── Indexes ────

// One AI audit result per TC per month
aiAuditResultSchema.index({ tcId: 1, month: 1 }, { unique: true });

export const aiAuditResultModel = mongoose.model(
"AiAuditResult",
aiAuditResultSchema,
"ai-audit-result",
);

2.2 Field Reference​

FieldTypeRequiredDefaultDescription
tcIdStringYes—References transformer.id
tcNumberStringYes—TC number (denormalized)
tcNameStringYes—TC name (denormalized)
monthStringYes—YYYY-MM format
locationObjectYes—Same flat structure as transformer.location
tcCapacityNumberNo—kVA rating
meterConstantNumberNo—Meter constant
meterConstantChangedBooleanNofalseWhether meter constant changed this month
readingDayNumberNo—Day of month for meter reading (1-31)
readingMRStringNo—MR code
gpsCoordinatesGeoJSON PointNo—TC GPS location [longitude, latitude]
readingObjectNo—{ initialValue, finalValue } meter readings
tcMakeStringNo—TC manufacturer
serialNumberStringNo—TC serial number
timsCodeStringNo—TIMS identifier
dtlmsStringNo—DTLMS code
dtrStringNo—DTR code
feederObjectNo—Feeder details { name, mdmCode, number }
executionTypeStringNo—e.g., "Open Billing"
meterMakeStringNo—Meter manufacturer
meterSerialStringNo—Meter serial / LD-SN
ctRatioStringNo—CT ratio
originalObjectNo—Snapshot of audit values before AI audit
computedObjectNo—Recomputed audit values after AI analysis
tcConsumptionNumberNo—Read-only, from transformer doc
previousMonthLossPercentageNumberNo—Read-only, from transformer doc
previousToPreviousMonthLossPercentageNumberNo—Read-only, from transformer doc
frozenBooleanNofalseSet to true when user freezes the AI audit
frozenOnDateNonullTimestamp of freeze

2.3 original and computed Sub-Document Shape​

Both original and computed share the same shape:

Sub-FieldTypeDefaultDescription
installationConsumptionNumber—Sum of tagged installations' consumption
installationCountNumber—Count of tagged installations
lossPercentageNumber—Computed loss %
auditStatusString (enum)—AUDITED or AUDIT_FAILED
billingCount.unbilledNumber0Count of unbilled installations
billingCount.mnrNumber0Count of MNR installations
billingCount.vacantNumber0Count of vacant installations
billingCount.zeroConsumptionNumber0Count of zero-consumption installations
billingCount.doorlockNumber0Count of door-locked installations
billingCount.abnormalNumber0Count of abnormal installations
billingCount.subnormalNumber0Count of subnormal installations
billingCount.billCancellationNumber0Count of bill-cancelled installations
revenueParams.arrNumber—ARR value
revenueParams.atcNumber—AT&C value
revenueParams.demandNumber—Total demand
revenueParams.collectionNumber—Total collection
revenueParams.billingEfficiencyNumber—Billing efficiency %
revenueParams.collectionEfficiencyNumber—Collection efficiency %
overloadedBoolean—Whether total sanctioned load exceeds TC capacity
totalInstallationSanctionedLoadInKVANumber—Sum of installations' sanctioned load

2.4 Indexes​

IndexFieldsTypePurpose
Primary{ tcId: 1, month: 1 }UniqueOne AI audit result per TC per month

3. New Collection: ai-audit-installation​

One document per installation involved in an AI audit. Links back to ai-audit-result. Contains both the tagging analysis (Table 1 — GPS distance, tag/untag suggestion) and the remarks analysis (Table 2 — remark type, average consumption suggestion).

3.1 Mongoose Schema​

import mongoose from "mongoose";

/**
* AI Audit Installation — One document per installation per AI audit
*
* Unique identity: { aiAuditResultId, accountId }
* Stores GPS distance analysis (tagging table) and remarks analysis (remarks table)
* for each installation involved in an AI audit of a TC.
*
* Collection: ai-audit-installation
*/

const aiAuditInstallationSchema = new mongoose.Schema(
{
// ──── Link to AI Audit Result ────
aiAuditResultId: {
type: String,
required: true, // references ai-audit-result document's _id or generated ID
},
tcId: {
type: String,
required: true, // the target TC being audited
},
month: {
type: String,
required: true,
validate: {
validator: (v) => /^\d{4}-(0[1-9]|1[0-2])$/.test(v),
message: "month must be in YYYY-MM format",
},
},

// ──── Installation Identity (copied from installation doc) ────
accountId: {
type: String,
required: true,
},
installationId: {
type: String, // installation.id field
},
rrNumber: { type: String },
consumerName: { type: String },
mrCode: { type: String },
readingDay: { type: Number, min: 1, max: 31 },
tariff: {
short: { type: String },
long: { type: String },
},
sanctionedLoad: {
kw: { type: Number },
hp: { type: Number },
},
gpsCoordinates: {
type: { type: String, enum: ["Point"], default: "Point" },
coordinates: { type: [Number] }, // [longitude, latitude]
},

// ──── Installation Data for this Month ────
consumption: { type: Number }, // actual consumption from installation doc
demand: { type: Number },
collection: { type: Number },

// ──── Table 1: Tagging Fields (GPS distance analysis) ────
distanceFromTC: { type: Number }, // distance in meters from TC GPS
withinRange: { type: Boolean }, // true if distance <= 5000m
currentTaggingStatus: {
type: String,
enum: ["TAGGED", "UNTAGGED"],
// TAGGED = currently tagged to this TC
// UNTAGGED = untagged or tagged to a different TC
},
aiSuggestion: {
type: String,
enum: ["TAG", "UNTAG", "NO_CHANGE"],
// TAG = AI recommends tagging to this TC
// UNTAG = AI recommends untagging from this TC
// NO_CHANGE = no action needed
},
sourceTcId: {
type: String,
default: null,
// If currently tagged to a DIFFERENT TC, stores that TC's id
// Needed for reauditing source TCs during freeze
},
sourceTcNumber: {
type: String,
default: null, // the source TC's number
},

// ──── Table 2: Remarks Fields (historical consumption analysis) ────
hasRemark: {
type: Boolean,
default: false, // true if installation has any remark/billing flag
},
remarkType: {
type: String,
enum: [
"UNBILLED", "MNR", "VACANT", "ZERO_CONSUMPTION",
"DOORLOCK", "ABNORMAL", "SUBNORMAL", null,
],
default: null,
},
avgConsumption6Months: {
type: Number,
default: null, // pre-computed from installation doc
},
aiSuggestsConsumption: {
type: Boolean,
default: false,
// true if AI suggests using avg consumption
// (true for all remarked installations with avgConsumption6Months > 0)
},

// ──── Billing Flags (copied from installation) ────
unbilled: { type: Boolean },
mnr: { type: Boolean },
vacant: { type: Boolean },
doorLock: { type: Boolean },
zeroConsumption: { type: Boolean },
emin: { type: Number }, // if has value -> abnormal
emax: { type: Number }, // if has value -> subnormal
},
{
timestamps: true,
collection: "ai-audit-installation",
},
);

// ──── Indexes ────

// One doc per installation per AI audit
aiAuditInstallationSchema.index(
{ aiAuditResultId: 1, accountId: 1 },
{ unique: true },
);

// Remarks table pagination
aiAuditInstallationSchema.index({ aiAuditResultId: 1, hasRemark: 1 });

// Tagging table filter tabs
aiAuditInstallationSchema.index({ aiAuditResultId: 1, aiSuggestion: 1 });

export const aiAuditInstallationModel = mongoose.model(
"AiAuditInstallation",
aiAuditInstallationSchema,
"ai-audit-installation",
);

3.2 Field Reference​

Installation Identity​

FieldTypeRequiredDefaultDescription
aiAuditResultIdStringYes—References ai-audit-result document
tcIdStringYes—The target TC being audited
monthStringYes—YYYY-MM format
accountIdStringYes—Installation account ID
installationIdStringNo—The installation.id field
rrNumberStringNo—RR number
consumerNameStringNo—Consumer name
mrCodeStringNo—MR code
readingDayNumberNo—Day of month for meter reading (1-31)
tariffObjectNo—{ short, long } tariff info
sanctionedLoadObjectNo—{ kw, hp } sanctioned load
gpsCoordinatesGeoJSON PointNo—Installation GPS [longitude, latitude]

Installation Data​

FieldTypeRequiredDefaultDescription
consumptionNumberNo—Actual consumption for this month
demandNumberNo—Demand amount
collectionNumberNo—Collection amount

Table 1 — Tagging (GPS Distance Analysis)​

FieldTypeRequiredDefaultDescription
distanceFromTCNumberNo—Distance in meters from TC GPS coordinates
withinRangeBooleanNo—true if distanceFromTC <= 5000
currentTaggingStatusString (enum)No—TAGGED or UNTAGGED relative to this TC
aiSuggestionString (enum)No—TAG, UNTAG, or NO_CHANGE
sourceTcIdStringNonullIf tagged to a different TC, that TC's id
sourceTcNumberStringNonullIf tagged to a different TC, that TC's number

Table 2 — Remarks (Historical Consumption Analysis)​

FieldTypeRequiredDefaultDescription
hasRemarkBooleanNofalsetrue if installation has any remark/billing flag
remarkTypeString (enum)NonullDerived remark type (see derivation rules below)
avgConsumption6MonthsNumberNonullPre-computed 6-month average consumption
aiSuggestsConsumptionBooleanNofalsetrue if AI suggests using avg consumption

Billing Flags (copied from installation)​

FieldTypeRequiredDefaultDescription
unbilledBooleanNo—Unbilled flag
mnrBooleanNo—Meter Not Read flag
vacantBooleanNo—Vacant premises flag
doorLockBooleanNo—Door locked flag
zeroConsumptionBooleanNo—Zero consumption flag
eminNumberNo—If has value, installation is abnormal
emaxNumberNo—If has value, installation is subnormal

3.3 Indexes​

IndexFieldsTypePurpose
Primary{ aiAuditResultId: 1, accountId: 1 }UniqueOne doc per installation per AI audit
Remarks query{ aiAuditResultId: 1, hasRemark: 1 }Non-uniqueRemarks table pagination
Tagging filter{ aiAuditResultId: 1, aiSuggestion: 1 }Non-uniqueTagging table filter tabs (TAG / UNTAG / NO_CHANGE)

4. Changes to Existing: transformer Schema​

File: common/src/schemas/transformer.model.js

Add 10 new fields to the transformer schema. These fields are populated by the data migration script from the production system and are used by the AI Audit feature for display in the TC detail view.

4.1 New Fields​

TC Detail Properties​

Add after the existing reading block, under a new section comment // ──── TC Detail Properties ────:

FieldTypeRequiredDefaultDescription
tcMakeStringNo—TC manufacturer name
serialNumberStringNo—TC serial number
timsCodeStringNo—TIMS identifier
dtlmsStringNo—DTLMS code
dtrStringNo—DTR code
feederObjectNo—Feeder details { name, mdmCode, number }
executionTypeStringNo—e.g., "Open Billing"

Meter Detail Properties​

Add after TC Detail Properties, under a new section comment // ──── Meter Detail Properties ────:

FieldTypeRequiredDefaultDescription
meterMakeStringNo—Meter manufacturer name
meterSerialStringNo—Meter serial number / LD-SN
ctRatioStringNo—CT ratio

4.2 Schema Additions (pseudocode)​

// ──── TC Detail Properties ────
tcMake: { type: String },
serialNumber: { type: String },
timsCode: { type: String },
dtlms: { type: String },
dtr: { type: String },
feeder: {
name: { type: String },
mdmCode: { type: String },
number: { type: String },
},
executionType: { type: String },

// ──── Meter Detail Properties ────
meterMake: { type: String },
meterSerial: { type: String },
ctRatio: { type: String },

4.3 New Index​

Add a 2dsphere index on the gpsCoordinates field (required for $geoNear aggregation in AI audit):

transformerSchema.index({ gpsCoordinates: "2dsphere" });

5. Changes to Existing: installation Schema​

File: common/src/schemas/installation.model.js

5.1 New Field​

FieldTypeRequiredDefaultDescription
avgConsumption6MonthsNumberNonullPre-computed by a batch script. Average of the last 6 months' consumption for this accountId. Computed only for installations with remark flags.
avgConsumption6Months: { type: Number, default: null },

5.2 New Index​

Add a 2dsphere index on the gpsCoordinates field (required for $geoNear aggregation in AI audit):

installationSchema.index({ gpsCoordinates: "2dsphere" });

6. Index Summary (All Collections)​

6.1 New Indexes​

CollectionIndexTypePurpose
ai-audit-result{ tcId: 1, month: 1 }UniqueOne AI audit result per TC per month
ai-audit-installation{ aiAuditResultId: 1, accountId: 1 }UniqueOne doc per installation per AI audit
ai-audit-installation{ aiAuditResultId: 1, hasRemark: 1 }Non-uniqueRemarks table pagination
ai-audit-installation{ aiAuditResultId: 1, aiSuggestion: 1 }Non-uniqueTagging table filter tabs
transformer{ gpsCoordinates: "2dsphere" }2dsphere$geoNear queries for GPS distance calculation
installation{ gpsCoordinates: "2dsphere" }2dsphere$geoNear queries for GPS distance calculation

6.2 Why 2dsphere?​

The AI Audit computes the distance between a TC and its nearby installations using MongoDB's $geoNear aggregation stage. This requires a 2dsphere index on the gpsCoordinates field of both the transformer and installation collections. Without this index, $geoNear will throw an error.

Both schemas already store GPS as GeoJSON Point format ({ type: "Point", coordinates: [longitude, latitude] }), which is compatible with 2dsphere indexing.


7. Derived Field Rules​

7.1 remarkType Derivation​

The remarkType field on ai-audit-installation is derived from the boolean billing flags copied from the installation document. If multiple flags are true, use the first match in priority order:

1. unbilled === true → "UNBILLED"
2. mnr === true → "MNR"
3. vacant === true → "VACANT"
4. zeroConsumption === true → "ZERO_CONSUMPTION"
5. doorLock === true → "DOORLOCK"
6. emin has value (> 0) → "ABNORMAL"
7. emax has value (> 0) → "SUBNORMAL"
8. none of the above → null

7.2 hasRemark Derivation​

hasRemark = remarkType !== null

7.3 aiSuggestsConsumption Derivation​

aiSuggestsConsumption = hasRemark && avgConsumption6Months != null && avgConsumption6Months > 0

7.4 withinRange Derivation​

withinRange = distanceFromTC <= 5000 // 5km = 5000 meters

The 5km threshold is stored as a named constant in code (not in the schema).

7.5 GPS Distance Computation​

Distance is computed via MongoDB $geoNear aggregation using the TC's gpsCoordinates as the reference point:

// Pseudocode — actual implementation in the API LLD
db.installation.aggregate([
{
$geoNear: {
near: tcGpsCoordinates, // { type: "Point", coordinates: [lng, lat] }
distanceField: "distanceFromTC", // output field name
spherical: true,
// No maxDistance — we want ALL installations, just compute their distance
}
},
// ... further pipeline stages
]);

8. Behavioural Notes​

8.1 Staging Pattern​

The ai-audit-result and ai-audit-installation collections are intermediate/staging collections. They do not affect the real transformer or installation data until the user explicitly freezes the AI audit result.

8.2 Re-trigger Behaviour​

When an AI audit is triggered for a TC + month combination that already has an unfrozen result (frozen: false), the existing ai-audit-result and all its linked ai-audit-installation documents are deleted and recreated fresh. This ensures the AI audit always reflects the latest state of the transformer and installation data.

If a frozen result exists (frozen: true), the AI audit cannot be re-triggered for that TC + month (the API should return an error).

8.3 Freeze Behaviour​

When the user freezes an AI audit result:

  1. frozen is set to true and frozenOn to the current timestamp on the ai-audit-result doc
  2. auditedByAI: true is set on the real transformer document (this field already exists in the transformer schema)
  3. The computed values from ai-audit-result are applied to the real transformer document
  4. Installation tagging changes (TAG/UNTAG) are applied to the real installation documents
  5. Affected TCs are reaudited and audit summaries are recomputed (cascade as described in LLD-audit-tc.md)

8.4 Data Flow Diagram​

┌──────────────────────────────────────────────────────────────────┐
│ AI Audit Trigger │
└───────────────────────────┬──────────────────────────────────────┘
│
▼
┌──────────────────────────┐
│ Delete existing unfrozen │
│ ai-audit-result + │
│ ai-audit-installation │
│ (if any) │
└────────────┬─────────────┘
│
┌────────────┼─────────────────────┐
▼ ▼ ▼
┌──────────────┐ ┌─────────────────┐ ┌─────────────────────┐
│ Copy TC props │ │ $geoNear query │ │ Compute original │
│ to ai-audit- │ │ installations │ │ audit values from │
│ result doc │ │ near TC GPS │ │ current transformer │
└──────────────┘ └────────┬────────┘ └─────────────────────┘
│
▼
┌────────────────────────────────┐
│ For each installation: │
│ - Compute distanceFromTC │
│ - Determine withinRange │
│ - Set currentTaggingStatus │
│ - Set aiSuggestion │
│ - Derive remarkType │
│ - Set aiSuggestsConsumption │
│ - Create ai-audit-installation │
└────────────────┬───────────────┘
│
▼
┌────────────────────────────────┐
│ Compute new audit values: │
│ - installationConsumption │
│ - installationCount │
│ - lossPercentage │
│ - billingCount │
│ - revenueParams │
│ - auditStatus │
│ - overloaded │
│ Store in computed block │
└────────────────┬───────────────┘
│
▼
┌────────────────────────────────┐
│ Save ai-audit-result doc │
│ (frozen: false) │
└────────────────────────────────┘
│
┌───────────────┴───────────────┐
▼ ▼
┌─────────────────┐ ┌─────────────────────┐
│ User reviews │ │ User reviews │
│ Table 1: Tagging │ │ Table 2: Remarks │
│ (GPS distance) │ │ (avg consumption) │
└────────┬────────┘ └──────────┬──────────┘
│ │
└───────────────┬───────────────┘
▼
┌────────────────────────────────┐
│ User freezes → apply to real │
│ transformer + installation docs │
│ → cascade reaudit + summaries │
└────────────────────────────────┘

9. File Structure​

New model files to create in common/src/schemas/:

common/src/schemas/
transformer.model.js — Modified (10 new fields + 2dsphere index)
installation.model.js — Modified (1 new field + 2dsphere index)
aiAuditResult.model.js — New
aiAuditInstallation.model.js — New