Installation Module · AI Architecture
TG 2kW · Stages 2.1–2.7 · MVP v1.0 · Tech Review
Every stage, gate, and database write in a single snake-flow view. Three rows wrap to fit the screen — flow goes left→right, turns down, then right→left, turns down, then left→right to exit. Click any node or gate for full details. Switch tabs above to explore each role's complete screen journey from login to end.
usc_number — the single thread connecting all modules.All tables, columns, types, and FK relationships spanning the full installation pipeline. Every record is keyed on lead_id / usc_number. The USC_STAGE_TRACKER powers dashboards without multi-table joins. Scroll horizontally to explore.
erDiagram
PARTNERS {
uuid partner_id PK
string partner_name
enum partner_type "INSTALLATION_PARTNER | B2B_PARTNER"
json gst_numbers
boolean is_active
timestamp created_at
timestamp updated_at
}
USERS {
uuid user_id PK
string full_name
string email UK
string phone_number
enum role "ADMIN | COL | SURVEYOR | WAREHOUSE | BACK_OFFICE | DQCI | IP_FIELD"
uuid partner_id FK "null = internal Claro staff"
string password_hash
boolean is_active
timestamp joined_at
timestamp last_login_at
}
LEAD_DATA {
uuid lead_id PK
bigint usc_number UK
string consumer_name
string consumer_mobile
uuid client_partner_id FK "B2B_PARTNER"
string district
string mandal
string village
decimal sanctioned_load_kw
decimal existing_cmd_kw
string consumer_category
uuid assigned_to FK "surveyor"
uuid uploaded_by FK
timestamp uploaded_at
timestamp created_at
timestamp updated_at
}
DIGITAL_SURVEY {
uuid survey_id PK
uuid lead_id FK
uuid surveyed_by FK
enum consumer_interest "INTERESTED | NOT_INTERESTED | CALLBACK_REQUESTED"
enum building_type "RCC | TIN_SHED | OTHER"
text consumer_response
text internal_remark
boolean survey_completed
timestamp survey_completed_at
timestamp created_at
timestamp updated_at
}
PHYSICAL_SURVEY {
uuid physical_survey_id PK
uuid lead_id FK
uuid digital_survey_id FK "gate: only INTERESTED leads"
uuid surveyed_by FK
timestamp survey_timestamp
decimal rooftop_area_sqft
enum rooftop_type "RCC | TIN_SHED"
decimal proposed_capacity_kw
decimal existing_cmd_kw
decimal dc_cable_length
decimal ac_cable_length
boolean mcb_available
string power_bill_url
string aadhaar_url
string consumer_consent_url "mandatory"
string ula_registration_number "mandatory"
string gps_photo_url
decimal latitude
decimal longitude
text remarks
boolean feasible_for_installation
timestamp created_at
timestamp updated_at
}
INSTALLATION_BATCH {
uuid batch_id PK
string assignment_batch_code UK
uuid installation_partner_id FK "INSTALLATION_PARTNER"
uuid assigned_by FK "COL user"
date installation_start_date
date installation_end_date
string village
timestamp created_at
}
INSTALLATION_BATCH_LEADS {
uuid id PK
uuid batch_id FK
uuid lead_id FK
uuid physical_survey_id FK "feasible survey"
}
DISPATCH_CHALLAN {
uuid challan_id PK
uuid batch_id FK
string mandal
string panel_model
string inverter_model
integer panel_quantity
integer inverter_quantity
date dispatch_date
string unsigned_dc_url "system-generated PDF"
string signed_dc_url "uploaded after IP signs"
enum stage_status "DC_PENDING | DC_DISPATCHED_AWAITING_SIGN | SIGNED_DC_UPLOADED"
uuid dispatched_by FK "warehouse user"
timestamp created_at
timestamp updated_at
}
INSTALLATION_RECORD {
uuid installation_id PK
uuid lead_id FK
uuid installation_partner_id FK
uuid submitted_by FK "IP field staff"
string inverter_sn_manual
string inverter_sn_ai_ocr "system OCR"
string inverter_sn_photo_url
string inverter_make
enum mounting_type
string photo_panel_view_url
string photo_inverter_view_url
string photo_earthing_url
string photo_la_url
decimal latitude
decimal longitude
decimal gps_distance_from_survey_m
enum gps_match_status "MATCHED | MISMATCH_FLAGGED | PENDING"
boolean human_detected "system AI check"
boolean all_sn_verified "system"
text ai_remarks "system"
enum stage_status "PENDING | SUBMITTED_AI_PENDING | AI_VERIFIED | SN_MISMATCH_REJECTED"
timestamp created_at
timestamp updated_at
}
MODULE_SERIAL_NUMBERS {
uuid module_sn_id PK
uuid installation_id FK
integer module_number "1 to 4"
string sn_manual
string sn_ai_ocr "system OCR"
string photo_url
boolean sn_match "manual matches OCR"
boolean db_verified "found in MANUFACTURER_INVENTORY"
boolean duplicate_found "already registered at another USC"
timestamp created_at
}
CIVIL_WORK_RECORD {
uuid civil_work_id PK
uuid lead_id FK
uuid installation_partner_id FK
uuid submitted_by FK "IP field staff"
string photo_civil_1_url
string photo_civil_2_url
string photo_inverter_url
decimal latitude "canonical install GPS"
decimal longitude
boolean civil_work_done
string folder_link "system Drive folder"
text ai_remarks "system"
enum stage_status "PENDING | SUBMITTED_AI_PENDING | CIVIL_VERIFIED | REWORK_REQUIRED"
timestamp created_at
timestamp updated_at
}
DCR_RECORD {
uuid dcr_id PK
uuid lead_id FK
uuid submitted_by FK "back office"
date dcr_date
string dcr_pdf_url
string dcr_cert_number "AI-derived from PDF"
string module_1_sn "AI-derived"
string module_2_sn "AI-derived"
string module_3_sn "AI-derived"
string module_4_sn "AI-derived"
string invoice_sr_no "AI-derived"
date invoice_date "AI-derived"
enum stage_status "DCR_PENDING | DCR_UPLOADED | DCR_VERIFIED"
timestamp created_at
timestamp updated_at
}
NET_METER_SYNC {
uuid sync_id PK
uuid lead_id FK
uuid submitted_by FK "back office"
string sync_report_url
string net_meter_registration_number "N8N-derived"
decimal latitude "N8N-derived"
decimal longitude "N8N-derived"
date synchronisation_date "N8N-derived"
boolean file_moved "system"
text remarks
enum stage_status "SYNC_PENDING | SYNC_UPLOADED | QC_READY"
timestamp created_at
timestamp updated_at
}
QC_ASSIGNMENT {
uuid qc_assignment_id PK
uuid lead_id FK
uuid assigned_dqci_id FK "DQCI user"
uuid assigned_by_id FK "COL user"
timestamp created_at
}
QC_SUBMISSION {
uuid qc_submission_id PK
uuid qc_assignment_id FK
uuid lead_id FK
uuid qc_engineer_id FK "DQCI"
uuid installation_partner_id FK
date qc_date
integer attempt_number "1 = first, 2+ = Re-QC"
boolean structure_alignment_ok
boolean structure_fasteners_ok
boolean panels_no_shadow_ok
boolean la_installed_ok
boolean module_earthing_ok
boolean inverter_installed_ok
boolean acdb_installed_ok
boolean dcdb_installed_ok
boolean cable_dressing_ok
boolean conduit_trunking_ok
boolean no_loose_wiring
boolean earthing_conduits_separate
boolean earthing_pits_completed
boolean la_dedicated_earthing
string photo_panels_la_url
string photo_inverter_acdb_dcdb_url
string photo_earthing_pit_url
string photo_net_meter_url
boolean issue_observed
enum final_qc_status "APPROVED_OK | APPROVED_WITH_OBSERVATION | REWORK_REQUIRED"
text additional_remarks
enum stage_status "QC_PENDING | QC_ASSIGNED | IN_PROGRESS | APPROVED_OK | REWORK_REQUIRED"
timestamp created_at
timestamp updated_at
}
TGREDCO_INSPECTION {
uuid inspection_id PK
uuid lead_id FK
uuid submitted_by FK
string pcr_url
string inspection_report_url
string inspection_rejection_reason
text remarks
enum stage_status "INSPECTION_PENDING | INSPECTION_PASSED | INSPECTION_REJECTED_REWORK"
timestamp created_at
timestamp updated_at
}
PAYMENT_COMPLIANCE_UPLOAD {
uuid compliance_id PK
uuid lead_id FK
uuid submitted_by FK
uuid sync_ref_id FK "NET_METER_SYNC"
string invoice_bill_url
string ca_certificate_url
string electricity_bill_url
date installation_date
date synchronisation_date
decimal latitude
decimal longitude
string beneficiary_name
string sim_number
enum stage_status "UPLOAD_PENDING | UPLOADED_TO_PORTAL | VERIFICATION_IN_PROGRESS"
timestamp created_at
timestamp updated_at
}
GOVERNMENT_APPROVAL_TRACK {
uuid approval_track_id PK
uuid lead_id FK
enum dm_review_status "PENDING_DM | DM_VERIFIED"
timestamp dm_reviewed_at
enum do_pd_approval_status "PENDING_DO_PD | TECH_ADMIN_APPROVED"
timestamp do_pd_approved_at
enum finance_verification_status "PENDING_FINANCE | FINANCE_VERIFIED"
timestamp finance_verified_at
enum final_approval_status "PENDING_FINAL | FINAL_APPROVED"
timestamp final_approved_at
timestamp updated_at
}
SUBSIDY_DISBURSEMENT {
uuid disbursement_id PK
uuid lead_id FK
decimal payment_percentage "75"
decimal amount_inr
date payment_date
enum stage_status "PAYMENT_PENDING | PAYMENT_75_RELEASED"
text remarks
timestamp created_at
timestamp updated_at
}
MANUFACTURER_INVENTORY {
uuid inventory_id PK
string serial_number UK
enum product_type "MODULE | INVERTER"
string manufacturer
string model
string batch_reference
boolean is_allocated
uuid allocated_to_lead_id FK "null until allocated"
timestamp allocated_at
timestamp created_at
}
AI_VERIFICATION_LOG {
uuid log_id PK
enum entity_type "INSTALLATION_RECORD | CIVIL_WORK_RECORD | QC_SUBMISSION"
uuid entity_id "polymorphic ref"
enum check_type "HUMAN_DETECTION | SN_OCR_MATCH | GPS_CHECK | SN_DB_VALIDATION"
enum result "PASS | FAIL | FLAGGED | PENDING"
decimal confidence_score
text ai_remarks
timestamp created_at
}
USC_STAGE_TRACKER {
uuid tracker_id PK
uuid lead_id FK "one row per USC"
enum current_phase "SURVEY | INSTALLATION | POST_INSTALLATION"
string current_stage "e.g. STAGE_5_INSTALLATION"
string current_status
timestamp last_updated_at
uuid last_updated_by FK
}
PARTNERS ||--o{ USERS : has
PARTNERS ||--o{ LEAD_DATA : owns_leads
PARTNERS ||--o{ INSTALLATION_BATCH : executes
PARTNERS ||--o{ INSTALLATION_RECORD : executes
PARTNERS ||--o{ CIVIL_WORK_RECORD : executes
PARTNERS ||--o{ QC_SUBMISSION : audited_for
USERS ||--o{ LEAD_DATA : assigned_to
USERS ||--o{ LEAD_DATA : uploaded_by
USERS ||--o{ DIGITAL_SURVEY : surveyed_by
USERS ||--o{ PHYSICAL_SURVEY : surveyed_by
USERS ||--o{ INSTALLATION_BATCH : assigned_by
USERS ||--o{ DISPATCH_CHALLAN : dispatched_by
USERS ||--o{ INSTALLATION_RECORD : submitted_by
USERS ||--o{ CIVIL_WORK_RECORD : submitted_by
USERS ||--o{ DCR_RECORD : submitted_by
USERS ||--o{ NET_METER_SYNC : submitted_by
USERS ||--o{ QC_ASSIGNMENT : assigned_dqci
USERS ||--o{ QC_ASSIGNMENT : assigned_by
USERS ||--o{ QC_SUBMISSION : conducted_by
USERS ||--o{ TGREDCO_INSPECTION : submitted_by
USERS ||--o{ PAYMENT_COMPLIANCE_UPLOAD : submitted_by
USERS ||--o{ USC_STAGE_TRACKER : last_updated_by
LEAD_DATA ||--o| DIGITAL_SURVEY : surveyed_in
LEAD_DATA ||--o| PHYSICAL_SURVEY : surveyed_in
LEAD_DATA ||--o| INSTALLATION_BATCH_LEADS : batched_in
LEAD_DATA ||--o| INSTALLATION_RECORD : installed_in
LEAD_DATA ||--o| CIVIL_WORK_RECORD : civil_done_in
LEAD_DATA ||--o| DCR_RECORD : dcr_in
LEAD_DATA ||--o| NET_METER_SYNC : synced_in
LEAD_DATA ||--o| QC_ASSIGNMENT : qc_assigned_in
LEAD_DATA ||--o{ QC_SUBMISSION : qc_submitted_in
LEAD_DATA ||--o| TGREDCO_INSPECTION : inspected_in
LEAD_DATA ||--o| PAYMENT_COMPLIANCE_UPLOAD : compliance_in
LEAD_DATA ||--o| GOVERNMENT_APPROVAL_TRACK : approved_in
LEAD_DATA ||--o| SUBSIDY_DISBURSEMENT : disbursed_in
LEAD_DATA ||--o{ MANUFACTURER_INVENTORY : sn_allocated_to
LEAD_DATA ||--|| USC_STAGE_TRACKER : tracked_by
DIGITAL_SURVEY ||--o| PHYSICAL_SURVEY : gates
INSTALLATION_BATCH ||--o{ INSTALLATION_BATCH_LEADS : contains
INSTALLATION_BATCH ||--o| DISPATCH_CHALLAN : dispatched_as
PHYSICAL_SURVEY ||--o{ INSTALLATION_BATCH_LEADS : triggers
INSTALLATION_RECORD ||--o{ MODULE_SERIAL_NUMBERS : has
QC_ASSIGNMENT ||--o{ QC_SUBMISSION : has
NET_METER_SYNC ||--o| PAYMENT_COMPLIANCE_UPLOAD : referenced_in