State Budget (IFMIS Portal)
    ↓
Departmental Allocations (17 volumes)
    ↓
Scheme Funding (Pragathi Paddu volume)
    ↓
Government Orders (GOIR portal) [⚠️ LINKAGE GAP]
    ↓
Tenders (eProcurement portal, 1,895 active)
    ↓
Contractor Awards (MIS Reports, authentication required) [⚠️ ACCESS GAP]
    ↓
Project Implementation (District portals, partial coverage)
    ↓
CAG Audits (Post-facto verification, 1-2 year lag)

✅ Tender listings: 1,895 live tenders accessible without authentication

✅ Government Orders: 57 procurement G.O.s with PDF downloads

✅ Tender volumes: ₹63,922 crore in FY 2023-24, ~2.5 lakh tenders over 3 years

✅ Agency coverage: 183 government agencies/PSUs using portal

🔐 MIS Reports: Contractor award history, completion tracking

🔐 Awarded tender details: Contractor names, award amounts

🔐 Performance data: Completion rates, delays, performance ratings

# Scrape MIS Reports for:
- Tender ID
- Tender title
- Tender amount
- Awarded contractor name
- Awarded amount
- Award date
- Estimated completion date
- Actual completion date (if available)
- Department/agency

Challenge: Same contractor may appear as:
- "ABC Construction Pvt Ltd"
- "ABC Construction Private Limited"  
- "ABC Constructions"
- "A.B.C. Construction"

Solution: Use LLM-based entity resolution + manual verification for top contractors

-- Largest contractors (by total award value)
SELECT contractor_name, SUM(award_amount) as total_awards, COUNT(*) as tender_count
GROUP BY contractor_name
ORDER BY total_awards DESC;

-- Fastest growing contractors (YoY growth rate)
-- Most awarded contractors (by tender count)
-- Contractors with highest completion rates (if completion data available)
-- Repeat winners (same department, same tender type)

Budget Entry Example:
- Scheme Name: "Mission Bhagiratha - Rural Water Supply"
- Head of Account: 2215-01-789-0-01
- Amount: ₹500 crore

Linkage Strategy:
1. Extract scheme name keywords: "Mission Bhagiratha", "Rural Water Supply"
2. Search G.O. archive for matching administrative sanctions
3. Use LLM to verify semantic match between budget line and G.O. text
4. Match amounts (allowing ±10% variance for sub-allocations)

Government Order Example:
- GO No: GO.MS.No.45, Dt.15.03.2024
- Subject: "Administrative sanction for rural water supply works in Karimnagar district"
- Sanctioned Amount: ₹45 crore

Linkage Strategy:
1. Extract location: "Karimnagar district"
2. Extract work type: "rural water supply"
3. Search eProcurement for tenders with:
   - Department: Panchayat Raj / Rural Water Supply
   - Location: Karimnagar
   - Tender type: Civil Works / Water Supply
   - Amount range: ₹40-50 crore
   - Tender date: After GO date
4. Use LLM to verify tender description matches GO sanctioned work

Tender Award Example:
- Tender ID: TS-RWS-2024-00456
- Work: "Laying of water supply pipelines in 25 villages, Karimnagar"
- Awarded Contractor: XYZ Infrastructure Pvt Ltd
- Award Amount: ₹43.5 crore
- Estimated Completion: 18 months from award

Linkage Strategy:
1. Extract village list from tender documents
2. Monitor CAG audit reports for mentions of:
   - Same contractor name
   - Same district/villages
   - Same project description
3. Check district portal project dashboards (if available)
4. File RTI for project completion status with Tender ID

# Pseudo-code for LLM-assisted entity resolution

def match_budget_to_tender(budget_entry, tender_candidates):
    """
    Use LLM to match budget allocation to likely tender
    """
    prompt = f"""
    Budget Entry:
    - Scheme: {budget_entry['scheme_name']}
    - Department: {budget_entry['department']}
    - Amount: {budget_entry['amount']} crore
    - Head of Account: {budget_entry['head']}

    Tender Candidates:
    {json.dumps(tender_candidates, indent=2)}

    For each tender, output:
    1. Match probability (0-1)
    2. Reasoning (key phrases that match/mismatch)
    3. Confidence level (low/medium/high)

    Return JSON array sorted by match probability descending.
    """

    matches = llm.generate(prompt, response_format="json")
    return matches

┌─────────────────────────────────────────────────────────────┐
│                     INGESTION LAYER                         │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  PDF Scraper (IFMIS, CAG) ──► OCR (Tesseract) ──► Text    │
│  Portal Scraper (eProcurement) ──► HTML Parser ──► JSON    │
│  CSV Importer (Open Budgets India) ──► Direct Load         │
│  RTI Document Processor ──► Manual Entry + OCR             │
│                                                             │
└──────────────────┬──────────────────────────────────────────┘
                   │
                   ▼
┌─────────────────────────────────────────────────────────────┐
│                   PROCESSING LAYER                          │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  PDF Table Extraction (Camelot/Tabula) ──► Pandas          │
│  Entity Resolution (LLM: Claude/GPT-4) ──► Match DB        │
│  Amount Normalization (₹ Lakh → Crore) ──► Standardization │
│  Date Parsing (Multiple Indian formats) ──► ISO 8601       │
│  Address Geocoding (District/Mandal) ──► Lat/Lon           │
│                                                             │
└──────────────────┬──────────────────────────────────────────┘
                   │
                   ▼
┌─────────────────────────────────────────────────────────────┐
│                     STORAGE LAYER                           │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  PostgreSQL (Structured Data)                               │
│    ├── budgets (scheme, dept, FY, amount, head_of_account) │
│    ├── government_orders (go_number, date, subject, pdf)   │
│    ├── tenders (tender_id, dept, amount, status, date)     │
│    ├── contractors (name, variants, total_awards)          │
│    ├── awards (tender_id, contractor_id, amount, date)     │
│    ├── projects (name, location, budget, contractor, status)│
│    └── linkages (budget_id, go_id, tender_id, confidence)  │
│                                                             │
│  S3/Minio (Document Storage)                                │
│    ├── /pdfs/budget/2025-26/*.pdf                          │
│    ├── /pdfs/cag/2024/*.pdf                                │
│    └── /pdfs/govt_orders/*.pdf                             │
│                                                             │
│  ElasticSearch (Full-Text Search)                           │
│    └── All text content indexed for keyword search          │
│                                                             │
└──────────────────┬──────────────────────────────────────────┘
                   │
                   ▼
┌─────────────────────────────────────────────────────────────┐
│                    ANALYTICS LAYER                          │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  District Spending Dashboard (Aggregations by location)     │
│  Contractor Intelligence (Award history, win rates)         │
│  Scheme Analytics (Budgeted vs. tendered vs. awarded)      │
│  Anomaly Detection (Budget overruns, sole-source awards)    │
│  Budget Execution Rate (Allocated → Tendered → Awarded %)   │
│                                                             │
└──────────────────┬──────────────────────────────────────────┘
                   │
                   ▼
┌─────────────────────────────────────────────────────────────┐
│                       AI/LLM LAYER                          │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  Entity Resolution (Match budget→GO→tender across systems)  │
│  Summarization (Generate scheme spending summaries)         │
│  Q&A Chatbot ("Which contractor won most roads tenders?")  │
│  Anomaly Detection ("Flag tenders with single bidder")     │
│  Investigative Lead Generation ("Similar tender amounts")   │
│  Translation (Telugu PDFs → English)                        │
│                                                             │
└─────────────────────────────────────────────────────────────┘

-- Core Tables

CREATE TABLE budgets (
    id SERIAL PRIMARY KEY,
    fiscal_year VARCHAR(10) NOT NULL, -- '2025-26'
    department VARCHAR(100) NOT NULL,
    scheme_name TEXT NOT NULL,
    head_of_account VARCHAR(50),
    budget_estimate DECIMAL(12,2), -- in crore
    revised_estimate DECIMAL(12,2),
    actual_expenditure DECIMAL(12,2),
    source_document TEXT, -- PDF URL
    page_number INT,
    extracted_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE government_orders (
    id SERIAL PRIMARY KEY,
    go_number VARCHAR(50) UNIQUE NOT NULL,
    go_date DATE NOT NULL,
    department VARCHAR(100),
    subject TEXT NOT NULL,
    sanctioned_amount DECIMAL(12,2),
    pdf_url TEXT,
    full_text TEXT, -- OCR extracted
    keywords TEXT[], -- For matching
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE tenders (
    id SERIAL PRIMARY KEY,
    tender_id VARCHAR(100) UNIQUE NOT NULL, -- From eProcurement
    title TEXT NOT NULL,
    department VARCHAR(100),
    tender_amount DECIMAL(12,2),
    bid_submission_date DATE,
    tender_opening_date DATE,
    work_location TEXT, -- District, Mandal
    tender_type VARCHAR(50), -- Civil Works, Goods, Services
    status VARCHAR(20), -- Live, Awarded, Cancelled
    portal_url TEXT,
    scraped_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE contractors (
    id SERIAL PRIMARY KEY,
    canonical_name VARCHAR(200) UNIQUE NOT NULL,
    name_variants TEXT[], -- For fuzzy matching
    total_awards_count INT DEFAULT 0,
    total_awards_value DECIMAL(15,2) DEFAULT 0,
    first_award_date DATE,
    last_award_date DATE,
    blacklisted BOOLEAN DEFAULT FALSE
);

CREATE TABLE awards (
    id SERIAL PRIMARY KEY,
    tender_id VARCHAR(100) REFERENCES tenders(tender_id),
    contractor_id INT REFERENCES contractors(id),
    award_amount DECIMAL(12,2),
    award_date DATE,
    estimated_completion_date DATE,
    actual_completion_date DATE,
    status VARCHAR(20) -- Ongoing, Completed, Delayed, Terminated
);

CREATE TABLE projects (
    id SERIAL PRIMARY KEY,
    project_name TEXT NOT NULL,
    district VARCHAR(50),
    mandal VARCHAR(50),
    villages TEXT[], -- Array of village names
    project_type VARCHAR(50), -- Roads, Water Supply, Irrigation, etc.
    physical_progress_percent INT,
    financial_progress_percent INT,
    budget_id INT REFERENCES budgets(id),
    contractor_id INT REFERENCES contractors(id)
);

-- Linkage table (for entity resolution confidence tracking)
CREATE TABLE linkages (
    id SERIAL PRIMARY KEY,
    budget_id INT REFERENCES budgets(id),
    go_id INT REFERENCES government_orders(id),
    tender_id VARCHAR(100) REFERENCES tenders(tender_id),
    project_id INT REFERENCES projects(id),
    match_confidence DECIMAL(3,2), -- 0.00 to 1.00
    match_method VARCHAR(50), -- 'llm', 'keyword', 'amount', 'manual'
    verified_by_human BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Indexes for performance
CREATE INDEX idx_budgets_fy_dept ON budgets(fiscal_year, department);
CREATE INDEX idx_tenders_status_date ON tenders(status, tender_opening_date);
CREATE INDEX idx_awards_contractor ON awards(contractor_id, award_date);
CREATE INDEX idx_go_keywords ON government_orders USING GIN(keywords);

def resolve_budget_to_tender(budget_entry):
    """Match budget line items to tenders using LLM"""

    # Get candidate tenders (filter by dept, date range, amount ±50%)
    candidates = db.get_tender_candidates(
        department=budget_entry.department,
        amount_min=budget_entry.amount * 0.5,
        amount_max=budget_entry.amount * 1.5,
        date_after=budget_entry.fiscal_year_start
    )

    # Use LLM to score matches
    prompt = f"""
    Budget Entry: {budget_entry.scheme_name} - {budget_entry.amount} crore
    Candidates: {json.dumps([{
        'tender_id': c.tender_id,
        'title': c.title,
        'amount': c.tender_amount,
        'location': c.work_location
    } for c in candidates])}

    Score each candidate 0-100 based on:
    - Semantic similarity of budget scheme name to tender title
    - Amount proximity
    - Location match (if budget specifies location)

    Return JSON: {{"matches": [{{"tender_id": "...", "score": 85, "reasoning": "..."}}]}}
    """

    result = claude.messages.create(
        model="claude-sonnet-4-5",
        messages=[{"role": "user", "content": prompt}],
        temperature=0
    )

    # Store high-confidence matches (score > 70)
    for match in result.matches:
        if match.score > 70:
            db.linkages.insert(
                budget_id=budget_entry.id,
                tender_id=match.tender_id,
                match_confidence=match.score / 100,
                match_method='llm'
            )

def detect_procurement_anomalies():
    """Flag suspicious patterns in tender data"""

    anomalies = []

    # Single bidder tenders
    single_bidder_tenders = db.query("""
        SELECT tender_id, title, tender_amount 
        FROM tenders 
        WHERE bidder_count = 1 AND status = 'Awarded'
    """)

    # LLM analyzes if single-bidder is justified
    for tender in single_bidder_tenders:
        prompt = f"""
        Tender: {tender.title}
        Amount: {tender.tender_amount} crore
        Only 1 bidder participated.

        Is this concerning? Consider:
        - Tender amount (higher amounts with single bidder more suspicious)
        - Work specialization (specialized work may have fewer bidders)
        - Location remoteness (remote areas may have fewer bidders)

        Return: {{"is_anomalous": true/false, "severity": "low/medium/high", "reasoning": "..."}}
        """

        result = llm.analyze(prompt)
        if result.is_anomalous:
            anomalies.append({
                'type': 'single_bidder',
                'tender': tender,
                'severity': result.severity,
                'reasoning': result.reasoning
            })

    return anomalies

def generate_investigation_leads():
    """Use LLM to identify interesting spending patterns"""

    # Get summary statistics
    stats = {
        'top_contractors': db.get_top_contractors(limit=20),
        'largest_tenders': db.get_largest_tenders(limit=50),
        'delayed_projects': db.get_delayed_projects(),
        'budget_vs_actual': db.get_budget_execution_rates()
    }

    prompt = f"""
    You are an investigative data journalist analyzing Telangana government spending.

    Data Summary:
    {json.dumps(stats, indent=2)}

    Generate 5 investigation leads that would be newsworthy. For each lead:
    1. Headline (catchy, specific)
    2. Data anomaly (what pattern is unusual)
    3. Questions to investigate (what additional data/RTI needed)
    4. Public interest angle (why citizens should care)

    Focus on: repeat winners, geographic concentration, budget overruns, delays, single-bidder awards
    """

    leads = llm.generate(prompt)
    return leads

def answer_spending_question(question):
    """Convert natural language to SQL, execute, return answer"""

    # Text-to-SQL generation
    schema_context = db.get_schema_description()

    prompt = f"""
    Schema:
    {schema_context}

    Question: "{question}"

    Generate PostgreSQL query to answer this question. Return only SQL, no explanation.
    """

    sql = llm.generate(prompt, temperature=0)
    result = db.execute(sql)

    # Natural language answer generation
    answer_prompt = f"""
    Question: {question}
    SQL Query: {sql}
    Result: {result}

    Generate a 2-3 sentence natural language answer with key numbers.
    """

    answer = llm.generate(answer_prompt)
    return answer, sql, result

Bootstrapped SaaS Opportunity?

✅ Yes - ₹5-7 crore/year from Telangana alone can support 10-15 person team.

Media Business Opportunity?

✅ Yes - Position as investigative journalism platform with revenue from custom reports + grants.

Data Licensing Opportunity?

✅ Yes - Academic institutions + international researchers would pay for clean spending datasets.

┌─────────────────────────────────────────────────────┐
│              FREE PUBLIC TIER                       │
├─────────────────────────────────────────────────────┤
│ - Basic search (by scheme, contractor, district)    │
│ - Limited to 100 searches/month                     │
│ - View spending summaries                           │
│ - No bulk downloads                                 │
│ - Supported by: Grants + Cross-subsidy from B2B     │
└─────────────────────────────────────────────────────┘

┌─────────────────────────────────────────────────────┐
│          JOURNALIST / NGO TIER (₹999/month)         │
├─────────────────────────────────────────────────────┤
│ - Unlimited searches                                │
│ - CSV exports                                       │
│ - Email alerts for new tenders/awards               │
│ - Access to CAG audit summaries                     │
└─────────────────────────────────────────────────────┘

┌─────────────────────────────────────────────────────┐
│      BUSINESS INTELLIGENCE TIER (₹50K/month)        │
├─────────────────────────────────────────────────────┤
│ - API access (10K calls/month)                      │
│ - Bulk data downloads                               │
│ - Contractor intelligence dashboards                │
│ - Competitive bidding analysis                      │
│ - Target: Construction companies, compliance firms  │
└─────────────────────────────────────────────────────┘

┌─────────────────────────────────────────────────────┐
│        CUSTOM RESEARCH (₹5-20L per project)         │
├─────────────────────────────────────────────────────┤
│ - Bespoke investigative reports                     │
│ - Scheme impact analysis                            │
│ - Contractor deep-dives                             │
│ - Target: Media orgs, political consultants, NGOs   │
└─────────────────────────────────────────────────────┘

❗ Contractor award data behind authentication: MIS Reports may require manual portal access, blocking automated scraping.

Mitigation: File RTI for bulk tender award data, or recruit insiders with portal access.

❗ District spending data doesn't exist publicly: District-level budget allocations and actual expenditures may only exist in treasury records.

Mitigation: Scale of ambition - build what IS possible (tender intelligence) before what requires data that doesn't exist.

❗ Government Orders not in searchable repository: GOIR portal wasn't verified by research.

Mitigation: Budget→Tender linkage may need to skip G.O. layer, going directly from budget scheme names to tender keywords.

❗ PDF extraction quality: Telangana budget PDFs may be scanned images, not selectable text.

Mitigation: Budget for professional OCR service (Google Document AI, AWS Textract) at ~$1.50 per 1,000 pages.

❗ Entity resolution accuracy: LLM may incorrectly match different entities with similar names.

Mitigation: Human-in-the-loop verification for high-value matches (>₹10 crore). Accept 70-80% accuracy for long tail.

❗ Portal structure changes: eProcurement site redesign could break scrapers.

Mitigation: Modular scraper architecture, monitor daily for structure changes, 24-hour SLA to fix.

❗ Low willingness-to-pay from journalists/NGOs: Free data culture in Indian journalism.

Mitigation: Focus B2B revenue on construction companies + compliance firms who see direct ROI.

❗ Government pushback: Telangana govt may pressure to take down "negative" stories.

Mitigation: Host outside India (AWS Singapore), clearly cite public data sources, engage pro-transparency lawyers preemptively.

❗ Competitor with more capital: Well-funded startup or govt launches similar platform.

Mitigation: Speed to market - launch MVP in 30 days, build brand with journalist community, become default source.

❗ Web scraping legal challenges: India doesn't have clear scraping case law.

Mitigation: Only scrape public data, respect robots.txt, cite data sources, consult with SFLC.in or Internet Freedom Foundation.

❗ Defamation risk from contractor intelligence: Contractor flags as "repeat single-bidder winner" may lead to defamation claims.

Mitigation: Only state factual data (X won Y tenders totaling ₹Z), avoid subjective labels like "suspicious". Clearly mark "Potential Anomalies" as requiring human verification.

Gray Area: If eProcurement portal later adds authentication, does that change "publicly available" status?

Mitigation: Scrape now while definitely public, archive data defensibly.

GO if:
✅ You have 12-18 months runway (personal savings or grant funding)

✅ You have technical skills (Python, web scraping, databases) or co-founder who does

✅ You have media connections (know 10+ journalists who'd use this)

✅ You're OK with ₹50 lakh-2 crore annual business, not ₹100 crore unicorn

✅ You're passionate about RTI, transparency, accountability (mission-driven)

NO-GO if:
❌ You need VC-scale returns (10X in 5 years)

❌ You don't have tech skills and can't find technical co-founder

❌ You expect govt to cooperate / provide data easily (they won't)

❌ You're risk-averse about legal challenges (govt may pressure you)

❌ You're doing this only for money (grant funding + subscriptions = hard revenue model)