πŸ›‘οΈ

DITS Schema Studio PROD_01

MySQL 8.0
Filter Subsystems:
πŸ’‘ Interactive Tips DITS Core

• Click any table to illuminate connected foreign key web.

• Drag to Pan • Mousewheel to Zoom

• Click AI Chatbot Scope to highlight NL2SQL-ready entities.

Relational Schema Entity Cards

Complete inspection of all 39 database entities with column definitions, data types, and primary/foreign keys.

DITS AI CHATBOT ENGINE NL2SQL • Guardrails • Scope Injection

Conversational Intelligence & Text-to-SQL Simulator

See how the AI Chatbot translates Hindi/English officer queries into schema-aware SQL, automatically injecting territorial scope filters (assigned_district • assigned_section) to guarantee confidential intelligence isolation.

Qwen 27B via local vLLM API

πŸ’¬ Select Officer Query

INTENT SCOPE INJECTED

Query Prompt

Generated AST SQL Query (Schema-Aware) 

          


          
          

            
Accessed Database Tables

            

          

        

      

    

  


  
  

    

      

        
DITS Enterprise System Architecture

        
How the MySQL database, Spring Boot backend, vLLM local AI, OpenSearch, and DITS_AI_CHATBOT interconnect.

      


      

        
        

          

            πŸ“± 1. Ingestion & Client Layer
          

          

            Web Portal (Thymeleaf, Quill rich text, Leaflet GIS heatmaps) • Mobile field app • Firebase Cloud Messaging (FCM) • Audio voice notes.
          

        


        
        

          

            πŸ›‘οΈ 2. Security & Scope Interceptor
          

          

            Spring Security • JWT Auth • UserScopeResolver • Geo-fenced query injection (Global vs Region vs District vs Section).
          

        


        
        

          

            βš™οΈ 3. Core Business Services
          

          

            FormService (57-col incident lifecycle) • NetworkAnalysisService (co-involvement graph) • MediaRetentionService (30-day auto-purge).
          

        


        
        

          

            🧠 4. Intelligence & AI Acceleration
          

          

            vLLM running Qwen/Qwen3.8-27B • 5W extractor • Threat scoring (0-100) • OpenSearch bilingual Hindi index • Indian DoubleMetaphone phonetic engine.
          

        

      


      
      

        

          The 13 Specialized Intelligence Desks (ΰ€…ΰ€¨ΰ₯ΰ€­ΰ€Ύΰ€—)
          Mandatory classification for all DITS incidents
        

        

      

    

  


  
  

    

      

        
Database Metrics & Operational Scale

        
Live stats and record distribution parsed from ditsv2 (1).sql production snapshot.

      


      
      

        

          Police Stations (Thanas)
          1,726
          With GPS coordinates
        

        

          Districts (UP)
          75
          Bilingual En • Hi
        

        

          Incident Forms
          528
          57 attributes each
        

        

          Audit Records
          715
          Non-repudiation log
        

        

          Registered Personnel
          93
          Field to DevAdmin
        

      


      
      

        

          
Quantitative Threat Scoring Standard

          

            

              CRITICAL (80 - 100): Communal riots, arms firing, mob arson, VIP threat
              CRITICAL
            

            

              HIGH (60 - 79): Dacoity, highway blockade, stone pelting, viral rumors
              HIGH
            

            

              MEDIUM (40 - 59): Sensitive local disputes, land enmity, brawls
              MEDIUM
            

            

              LOW (0 - 39): Routine procedural reports, minor thefts, non-sensitive
              LOW
            

          

        


        

          
Media Lifecycle & Storage Governance

          

            Surveillance videos, high-resolution evidence photos, and witness audio recordings follow strict 30-day lifecycle retention policy.
          

          

            

              Videos
              30 Days
            

            

              Photos
              30 Days
            

            

              Documents
              30 Days
            

          

          

            πŸ”’ Any item marked is_important by an officer is preserved permanently and exempt from the nightly 2:00 AM purge job.