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

Collection	Change
ai-audit-result	New — one doc per TC per month, stores AI audit result before freeze
ai-audit-installation	New — one doc per installation involved in an AI audit
transformer	Modified — 10 new fields (TC detail + meter detail properties)
installation	Modified — 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

Field	Type	Required	Default	Description
tcId	String	Yes	—	References transformer.id
tcNumber	String	Yes	—	TC number (denormalized)
tcName	String	Yes	—	TC name (denormalized)
month	String	Yes	—	YYYY-MM format
location	Object	Yes	—	Same flat structure as transformer.location
tcCapacity	Number	No	—	kVA rating
meterConstant	Number	No	—	Meter constant
meterConstantChanged	Boolean	No	false	Whether meter constant changed this month
readingDay	Number	No	—	Day of month for meter reading (1-31)
readingMR	String	No	—	MR code
gpsCoordinates	GeoJSON Point	No	—	TC GPS location [longitude, latitude]
reading	Object	No	—	{ initialValue, finalValue } meter readings
tcMake	String	No	—	TC manufacturer
serialNumber	String	No	—	TC serial number
timsCode	String	No	—	TIMS identifier
dtlms	String	No	—	DTLMS code
dtr	String	No	—	DTR code
feeder	Object	No	—	Feeder details { name, mdmCode, number }
executionType	String	No	—	e.g., "Open Billing"
meterMake	String	No	—	Meter manufacturer
meterSerial	String	No	—	Meter serial / LD-SN
ctRatio	String	No	—	CT ratio
original	Object	No	—	Snapshot of audit values before AI audit
computed	Object	No	—	Recomputed audit values after AI analysis
tcConsumption	Number	No	—	Read-only, from transformer doc
previousMonthLossPercentage	Number	No	—	Read-only, from transformer doc
previousToPreviousMonthLossPercentage	Number	No	—	Read-only, from transformer doc
frozen	Boolean	No	false	Set to true when user freezes the AI audit
frozenOn	Date	No	null	Timestamp of freeze

2.3 original and computed Sub-Document Shape

Both original and computed share the same shape:

Sub-Field	Type	Default	Description
installationConsumption	Number	—	Sum of tagged installations' consumption
installationCount	Number	—	Count of tagged installations
lossPercentage	Number	—	Computed loss %
auditStatus	String (enum)	—	AUDITED or AUDIT_FAILED
billingCount.unbilled	Number	0	Count of unbilled installations
billingCount.mnr	Number	0	Count of MNR installations
billingCount.vacant	Number	0	Count of vacant installations
billingCount.zeroConsumption	Number	0	Count of zero-consumption installations
billingCount.doorlock	Number	0	Count of door-locked installations
billingCount.abnormal	Number	0	Count of abnormal installations
billingCount.subnormal	Number	0	Count of subnormal installations
billingCount.billCancellation	Number	0	Count of bill-cancelled installations
revenueParams.arr	Number	—	ARR value
revenueParams.atc	Number	—	AT&C value
revenueParams.demand	Number	—	Total demand
revenueParams.collection	Number	—	Total collection
revenueParams.billingEfficiency	Number	—	Billing efficiency %
revenueParams.collectionEfficiency	Number	—	Collection efficiency %
overloaded	Boolean	—	Whether total sanctioned load exceeds TC capacity
totalInstallationSanctionedLoadInKVA	Number	—	Sum of installations' sanctioned load

2.4 Indexes

Index	Fields	Type	Purpose
Primary	{ tcId: 1, month: 1 }	Unique	One 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

Field	Type	Required	Default	Description
aiAuditResultId	String	Yes	—	References ai-audit-result document
tcId	String	Yes	—	The target TC being audited
month	String	Yes	—	YYYY-MM format
accountId	String	Yes	—	Installation account ID
installationId	String	No	—	The installation.id field
rrNumber	String	No	—	RR number
consumerName	String	No	—	Consumer name
mrCode	String	No	—	MR code
readingDay	Number	No	—	Day of month for meter reading (1-31)
tariff	Object	No	—	{ short, long } tariff info
sanctionedLoad	Object	No	—	{ kw, hp } sanctioned load
gpsCoordinates	GeoJSON Point	No	—	Installation GPS [longitude, latitude]

Installation Data

Field	Type	Required	Default	Description
consumption	Number	No	—	Actual consumption for this month
demand	Number	No	—	Demand amount
collection	Number	No	—	Collection amount

Table 1 — Tagging (GPS Distance Analysis)

Field	Type	Required	Default	Description
distanceFromTC	Number	No	—	Distance in meters from TC GPS coordinates
withinRange	Boolean	No	—	true if distanceFromTC <= 5000
currentTaggingStatus	String (enum)	No	—	TAGGED or UNTAGGED relative to this TC
aiSuggestion	String (enum)	No	—	TAG, UNTAG, or NO_CHANGE
sourceTcId	String	No	null	If tagged to a different TC, that TC's id
sourceTcNumber	String	No	null	If tagged to a different TC, that TC's number

Table 2 — Remarks (Historical Consumption Analysis)

Field	Type	Required	Default	Description
hasRemark	Boolean	No	false	true if installation has any remark/billing flag
remarkType	String (enum)	No	null	Derived remark type (see derivation rules below)
avgConsumption6Months	Number	No	null	Pre-computed 6-month average consumption
aiSuggestsConsumption	Boolean	No	false	true if AI suggests using avg consumption

Billing Flags (copied from installation)

Field	Type	Required	Default	Description
unbilled	Boolean	No	—	Unbilled flag
mnr	Boolean	No	—	Meter Not Read flag
vacant	Boolean	No	—	Vacant premises flag
doorLock	Boolean	No	—	Door locked flag
zeroConsumption	Boolean	No	—	Zero consumption flag
emin	Number	No	—	If has value, installation is abnormal
emax	Number	No	—	If has value, installation is subnormal

3.3 Indexes

Index	Fields	Type	Purpose
Primary	{ aiAuditResultId: 1, accountId: 1 }	Unique	One doc per installation per AI audit
Remarks query	{ aiAuditResultId: 1, hasRemark: 1 }	Non-unique	Remarks table pagination
Tagging filter	{ aiAuditResultId: 1, aiSuggestion: 1 }	Non-unique	Tagging 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 ────:

Field	Type	Required	Default	Description
tcMake	String	No	—	TC manufacturer name
serialNumber	String	No	—	TC serial number
timsCode	String	No	—	TIMS identifier
dtlms	String	No	—	DTLMS code
dtr	String	No	—	DTR code
feeder	Object	No	—	Feeder details { name, mdmCode, number }
executionType	String	No	—	e.g., "Open Billing"

Meter Detail Properties

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

Field	Type	Required	Default	Description
meterMake	String	No	—	Meter manufacturer name
meterSerial	String	No	—	Meter serial number / LD-SN
ctRatio	String	No	—	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

Field	Type	Required	Default	Description
avgConsumption6Months	Number	No	null	Pre-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

Collection	Index	Type	Purpose
ai-audit-result	{ tcId: 1, month: 1 }	Unique	One AI audit result per TC per month
ai-audit-installation	{ aiAuditResultId: 1, accountId: 1 }	Unique	One doc per installation per AI audit
ai-audit-installation	{ aiAuditResultId: 1, hasRemark: 1 }	Non-unique	Remarks table pagination
ai-audit-installation	{ aiAuditResultId: 1, aiSuggestion: 1 }	Non-unique	Tagging 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