Sangeetha-Grantha

Metadata Value
Status Archived
Version 1.0.0
Last Updated 2026-09-10
Author Sangeetha Grantha Team
Document Type Archive

Krithi Bulk Import from CSV - Comprehensive Strategy & Design

[!NOTE] Historical evidence: results, counts, commands, and observations below belong to the original work described here. The editorial update date is not a new test or corpus verification. For present behavior, use current ingestion guide.

1. Executive Summary

This document provides a comprehensive strategy and detailed design for bulk importing Krithis from CSV files located in /database/for_import/. The CSV files contain Krithi names, Ragas, and hyperlinks to source pages where full details can be scraped. This strategy builds upon existing research and leverages the current import infrastructure.

Key Findings

  1. CSV Data Structure: Three CSV files contain ~1,700+ Krithi entries with basic metadata (name, raga) and source URLs
  2. Existing Infrastructure: The application already has WebScrapingService and ImportService that can be extended
  3. Source Analysis: URLs point to blogspot.com pages (Thyagaraja Vaibhavam, Guru Guha, Syama Krishna Vaibhavam) with varying structure
  4. Recommended Approach: Phased implementation starting with CSV parsing, then batch scraping, followed by entity resolution and review workflow

Strategic Recommendations

2. CSV Data Analysis

2.1 File Inventory

File Composer Estimated Entries Source Domain
Thyagaraja-Krithi-For-Import.csv Thyagaraja ~690 thyagaraja-vaibhavam.blogspot.com
Dikshitar-Krithi-For-Import.csv Muthuswami Dikshitar ~480 guru-guha.blogspot.com
Syama-Sastri-Krithi-For-Import.csv Syama Sastri ~70 syamakrishnavaibhavam.blogspot.com

Total: ~1,240+ Krithi entries across the Trinity composers

2.2 CSV Structure

Krithi,Raga,Hyperlink
abhimAnamennaDu,kunjari,http://thyagaraja-vaibhavam.blogspot.com/2007/11/thyagaraja-kriti-abhimaanamennadu-raga.html

Fields:

2.3 Data Quality Observations

Strengths:

Challenges:

2.4 Source URL Patterns

Thyagaraja Vaibhavam:

Guru Guha (Dikshitar):

Syama Krishna Vaibhavam:

3. Architecture & Integration

3.1 Existing Infrastructure

The application already has:

  1. WebScrapingService:
    • Uses Gemini AI to extract structured metadata from URLs
    • Returns ScrapedKrithiMetadata with title, composer, raga, tala, deity, temple, lyrics, sections
    • Handles HTML cleaning and content extraction
  2. ImportService:
    • submitImports(): Creates ImportedKrithi records in staging table
    • reviewImport(): Approves imports and creates canonical Krithi entities
    • Handles entity creation (composer, raga, tala) if not found
  3. ImportRepository:
    • Database operations for imported_krithis and import_sources tables
    • Status tracking (PENDING, APPROVED, REJECTED)
  4. ImportRoutes:
    • /v1/admin/imports/scrape: Single URL scraping endpoint
    • /v1/admin/imports/krithis: Batch import submission
    • /v1/admin/imports/{id}/review: Review workflow

3.2 Proposed Architecture

┌─────────────────────────────────────────────────────────┐
│              CSV Bulk Import Service                     │
│  (New: CsvBulkImportService)                            │
└──────┬──────────┬──────────┬──────────┬─────────────────┘
       │          │          │          │
       ▼          ▼          ▼          ▼
┌──────────┐ ┌──────────┐ ┌──────────┐ ┌──────────┐
│ CSV      │ │ URL      │ │ Batch   │ │ Entity   │
│ Parser   │ │ Validator│ │ Scraper │ │ Resolver │
└────┬─────┘ └────┬─────┘ └────┬────┘ └────┬────┘
     │            │             │            │
     └────────────┴─────────────┴────────────┘
                    │
       ┌────────────▼────────────┐
       │  Existing Services      │
       │  - WebScrapingService   │
       │  - ImportService        │
       │  - ImportRepository     │
       └─────────────────────────┘

3.3 Service Layer Design

New Service: CsvBulkImportService

class CsvBulkImportService( private val webScrapingService: WebScrapingService, private val importService: ImportService, private val entityResolutionService: EntityResolutionService, // New private val deduplicationService: DeduplicationService, // New private val csvParser: CsvParser // New ) { suspend fun importFromCsv( csvFilePath: String, composerContext: String? = null, options: BulkImportOptions = BulkImportOptions.default() ): BulkImportResult

suspend fun validateCsvFile(csvFilePath: String): CsvValidationResult

```kotlin
suspend fun processBatch(
    entries: List<CsvKrithiEntry>,
    batchId: UUID
): Flow<ImportProgress> } ```

New Service: EntityResolutionService

class EntityResolutionService(
    private val composerRepo: ComposerRepository,
    private val ragaRepo: RagaRepository,
    private val deityRepo: DeityRepository,
    private val templeRepo: TempleRepository
) {
    suspend fun resolveComposer(name: String): EntityMatch<ComposerDto>
    suspend fun resolveRaga(name: String): EntityMatch<RagaDto>
    suspend fun resolveDeity(name: String): EntityMatch<DeityDto>
    suspend fun resolveTemple(name: String, deityContext: String?): EntityMatch<TempleDto>
}

New Service: DeduplicationService

class DeduplicationService( private val importRepo: ImportRepository, private val krithiRepo: KrithiRepository ) { suspend fun findDuplicates( imported: ImportedKrithiDto, batchContext: List = emptyList() ): List

```kotlin
suspend fun detectBatchDuplicates(batch: List<ImportedKrithiDto>): DeduplicationResult } ```

4. Implementation Plan

4.1 Phase 1: CSV Parsing & Validation (Week 1)

Objective: Parse CSV files and validate URLs

Deliverables:

  1. CSV Parser data class CsvKrithiEntry( val krithiName: String, val raga: String, val hyperlink: String, val composerContext: String? = null, // From filename val rowNumber: Int )
class CsvParser {
    suspend fun parseFile(filePath: String): List<CsvKrithiEntry>
    suspend fun validateEntry(entry: CsvKrithiEntry): ValidationResult
}

2. **URL Validator**
class UrlValidator {
    suspend fun validateUrl(url: String): UrlValidationResult
    suspend fun checkAccessibility(url: String): Boolean
    suspend fun detectSourceType(url: String): SourceType
}

3. **CSV Import API Endpoint**
POST /v1/admin/imports/csv/upload
POST /v1/admin/imports/csv/validate
POST /v1/admin/imports/csv/process

Implementation Steps:

  1. Create CsvParser using Kotlin CSV library or manual parsing
  2. Add URL validation (check if accessible, detect source type)
  3. Create API endpoint for CSV upload/validation
  4. Add CSV file reading capability (read from /database/for_import/)
  5. Generate validation report (valid URLs, broken links, duplicates)

Success Criteria:

4.2 Phase 2: Batch Scraping (Week 2)

Objective: Scrape all valid URLs with rate limiting and error handling

Deliverables:

  1. Batch Scraper class BatchScrapingService( private val webScrapingService: WebScrapingService, private val rateLimiter: RateLimiter ) { suspend fun scrapeBatch( urls: List, options: BatchScrapingOptions ): Flow

     suspend fun scrapeWithRetry(
     url: String,
     maxRetries: Int = 3
 ): ScrapedKrithiMetadata
}

2. **Rate Limiter**
class RateLimiter(
    private val requestsPerSecond: Int = 2, // Conservative for blogspot
    private val maxConcurrency: Int = 3
) {
    suspend fun <T> withRateLimit(block: suspend () -> T): T
}

3. **Progress Tracking**
data class BulkImportBatch(
    val id: UUID,
    val sourceFile: String,
    val totalEntries: Int,
    val processedEntries: Int,
    val successfulScrapes: Int,
    val failedScrapes: Int,
    val status: BatchStatus,
    val startedAt: Instant,
    val completedAt: Instant?
)

Implementation Steps:

  1. Extend WebScrapingService with retry logic
  2. Implement rate limiting (2 requests/second, 3 concurrent)
  3. Create batch scraping service with progress tracking
  4. Add database table for batch tracking
  5. Implement resume capability (if batch fails, resume from last successful)

Success Criteria:

4.3 Phase 3: Entity Resolution & De-duplication (Week 3)

Objective: Resolve entities and detect duplicates

Deliverables:

  1. Entity Resolution Service (as described in Section 3.3)
2. **Name Normalization**
class NameNormalizationService {
    fun normalizeComposerName(name: String): String
    fun normalizeRagaName(name: String): String
    fun normalizeDeityName(name: String): String
    fun normalizeTempleName(name: String): String
}

3. **Fuzzy Matching**
class FuzzyMatchingService {
    fun similarityScore(str1: String, str2: String): Double
    fun findBestMatch(
        query: String,
        candidates: List<String>,
        threshold: Double = 0.85
    ): MatchResult?
}
  1. De-duplication Service (as described in Section 3.3)

Implementation Steps:

  1. Implement name normalization (handle transliteration variations)
  2. Add PostgreSQL trigram indexes for fuzzy matching
  3. Create entity resolution service with confidence scoring
  4. Implement de-duplication (exact match, fuzzy match, semantic match)
  5. Add confidence thresholds for auto-mapping vs manual review

Success Criteria:

4.4 Phase 4: Review Workflow Integration (Week 4)

Objective: Integrate with existing review workflow and enhance UI

Deliverables:

  1. Enhanced Import Review UI
    • Batch import status dashboard
    • Filter by composer, raga, confidence score
    • Bulk approval for high-confidence imports
    • Side-by-side comparison with existing krithis
2. **Auto-approval Rules**
data class AutoApprovalRules(
    val minConfidenceScore: Double = 0.95,
    val requireComposerMatch: Boolean = true,
    val requireRagaMatch: Boolean = true,
    val allowAutoCreateEntities: Boolean = false
)

3. **Batch Operations**
POST /v1/admin/imports/batch/{id}/approve-all
POST /v1/admin/imports/batch/{id}/reject-all
POST /v1/admin/imports/batch/{id}/bulk-review

Implementation Steps:

  1. Enhance existing import review UI with batch context
  2. Add auto-approval logic for high-confidence imports
  3. Implement bulk review operations
  4. Add batch statistics dashboard
  5. Create import quality report

Success Criteria:

5. Data Flow

5.1 Complete Import Flow

1. CSV File Upload/Selection
   ↓
2. CSV Parsing & Validation
   ├─ Parse entries
   ├─ Validate URLs
   └─ Generate validation report
   ↓
3. Batch Creation
   ├─ Create import_batch record
   └─ Initialize progress tracking
   ↓
4. Batch Scraping (Parallel with rate limiting)
   ├─ For each URL:
   │  ├─ Scrape with WebScrapingService
   │  ├─ Extract metadata (Gemini AI)
   │  ├─ Handle errors/retries
   │  └─ Update progress
   └─ Collect all scraped metadata
   ↓
5. Entity Resolution
   ├─ For each scraped entry:
   │  ├─ Resolve composer (from CSV context + scraped)
   │  ├─ Resolve raga (from CSV + scraped)
   │  ├─ Resolve deity (from scraped)
   │  ├─ Resolve temple (from scraped)
   │  └─ Assign confidence scores
   └─ Flag ambiguous resolutions
   ↓
6. De-duplication
   ├─ Check against existing imported_krithis
   ├─ Check against existing krithis
   ├─ Check within batch
   └─ Generate duplicate matches
   ↓
7. Data Cleansing
   ├─ Normalize text
   ├─ Clean whitespace
   ├─ Fix encoding issues
   └─ Validate structure
   ↓
8. Staging
   ├─ Create imported_krithi records
   ├─ Store raw + resolved data
   ├─ Set status = PENDING
   └─ Link to batch
   ↓
9. Quality Scoring
   ├─ Calculate completeness score
   ├─ Calculate resolution confidence
   ├─ Calculate source quality
   └─ Assign quality tier
   ↓
10. Review Queue
    ├─ High confidence → Auto-approve (optional)
    ├─ Medium confidence → Review queue
    └─ Low confidence → Detailed review
    ↓
11. Canonicalization (via existing reviewImport)
    ├─ Create Krithi entity
    ├─ Create lyric variants
    ├─ Create sections
    └─ Link entities

5.2 Error Handling

Scraping Errors:

Entity Resolution Errors:

De-duplication Errors:

6. Database Schema Enhancements

6.1 Import Batch Tracking

CREATE TABLE import_batches ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), source_file TEXT NOT NULL, – CSV filename composer_context TEXT, – Implicit composer from filename total_entries INT NOT NULL, processed_entries INT NOT NULL DEFAULT 0, successful_scrapes INT NOT NULL DEFAULT 0, failed_scrapes INT NOT NULL DEFAULT 0, successful_imports INT NOT NULL DEFAULT 0, failed_imports INT NOT NULL DEFAULT 0, status VARCHAR(50) NOT NULL, – pending, processing, completed, failed, partial started_at TIMESTAMPTZ, completed_at TIMESTAMPTZ, error_summary JSONB, created_at TIMESTAMPTZ NOT NULL DEFAULT timezone(‘UTC’, now()) );

CREATE INDEX idx_import_batches_status ON import_batches(status);
CREATE INDEX idx_import_batches_source_file ON import_batches(source_file);

6.2 Enhanced Imported Krithis

– Add columns to existing imported_krithis table ALTER TABLE imported_krithis ADD COLUMN IF NOT EXISTS import_batch_id UUID REFERENCES import_batches(id), ADD COLUMN IF NOT EXISTS csv_row_number INT, ADD COLUMN IF NOT EXISTS csv_krithi_name TEXT, ADD COLUMN IF NOT EXISTS csv_raga TEXT, ADD COLUMN IF NOT EXISTS extraction_confidence DECIMAL(3,2), ADD COLUMN IF NOT EXISTS entity_mapping_confidence DECIMAL(3,2), ADD COLUMN IF NOT EXISTS duplicate_candidates JSONB, ADD COLUMN IF NOT EXISTS quality_score DECIMAL(3,2), ADD COLUMN IF NOT EXISTS quality_tier VARCHAR(20), – excellent, good, fair, poor ADD COLUMN IF NOT EXISTS processing_errors JSONB;

CREATE INDEX idx_imported_krithis_batch ON imported_krithis(import_batch_id);
CREATE INDEX idx_imported_krithis_quality ON imported_krithis(quality_tier, quality_score);

6.3 Entity Resolution Cache

CREATE TABLE entity_resolution_cache ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), entity_type VARCHAR(50) NOT NULL, – composer, raga, deity, temple raw_name TEXT NOT NULL, normalized_name TEXT NOT NULL, resolved_entity_id UUID NOT NULL, confidence DECIMAL(3,2) NOT NULL, resolution_method VARCHAR(50), – exact, fuzzy, ai_assisted created_at TIMESTAMPTZ NOT NULL DEFAULT timezone(‘UTC’, now()), UNIQUE(entity_type, normalized_name) );

CREATE INDEX idx_entity_cache_type_name ON entity_resolution_cache(entity_type, normalized_name);

7. API Design

7.1 CSV Import Endpoints

// Upload and validate CSV POST /v1/admin/imports/csv/validate Request: { “filePath”: “database/for_import/Thyagaraja-Krithi-For-Import.csv”, “composerContext”: “Thyagaraja” // Optional, inferred from filename } Response: { “totalEntries”: 690, “validUrls”: 685, “brokenUrls”: 5, “duplicates”: 2, “validationReport”: […] }

// Process CSV import POST /v1/admin/imports/csv/process Request: { “filePath”: “database/for_import/Thyagaraja-Krithi-For-Import.csv”, “composerContext”: “Thyagaraja”, “options”: { “rateLimitPerSecond”: 2, “maxConcurrency”: 3, “maxRetries”: 3, “autoApproveThreshold”: 0.95, “skipBrokenUrls”: true } } Response: { “batchId”: “uuid”, “status”: “processing”, “totalEntries”: 690 }

// Get batch status GET /v1/admin/imports/batches/{batchId} Response: { “id”: “uuid”, “sourceFile”: “Thyagaraja-Krithi-For-Import.csv”, “status”: “processing”, “totalEntries”: 690, “processedEntries”: 450, “successfulScrapes”: 445, “failedScrapes”: 5, “successfulImports”: 440, “failedImports”: 5, “startedAt”: “2026-01-20T10:00:00Z”, “progress”: 65.2 }

// List all batches GET /v1/admin/imports/batches Query params: status, sourceFile, composerContext Response: List

// Cancel batch
POST /v1/admin/imports/batches/{batchId}/cancel

7.2 Enhanced Import Review Endpoints

// List imports with batch context GET /v1/admin/imports Query params:

// Bulk review POST /v1/admin/imports/batch/{batchId}/bulk-review Request: { “action”: “approve” | “reject” | “flag”, “importIds”: [“uuid1”, “uuid2”, …], “reviewerNotes”: “Optional notes” }

// Auto-approve high confidence
POST /v1/admin/imports/batch/{batchId}/auto-approve
Request: {
  "minConfidence": 0.95,
  "requireComposerMatch": true,
  "requireRagaMatch": true
}

8. Quality Assurance

8.1 Validation Rules

CSV Validation:

Scraping Validation:

Entity Resolution Validation:

Data Quality Validation:

8.2 Quality Scoring

data class QualityScore( val overall: Double, // 0.0 - 1.0 val completeness: Double, // 40% weight val resolutionConfidence: Double, // 30% weight val sourceQuality: Double, // 20% weight val validationPass: Double, // 10% weight val tier: QualityTier )

enum class QualityTier {
    EXCELLENT, // >= 0.90, auto-approve candidate
    GOOD,      // >= 0.75, quick review
    FAIR,      // >= 0.60, standard review
    POOR       // < 0.60, detailed review
}

8.3 Testing Strategy

Unit Tests:

Integration Tests:

Manual Testing:

9. Performance Considerations

9.1 Rate Limiting

Blogspot.com Considerations:

Estimated Time:

9.2 Caching Strategy

Entity Resolution Cache:

Scraped Content Cache:

9.3 Batch Processing

Batch Size:

Database Optimization:

10. Risk Assessment & Mitigation

10.1 Technical Risks

Risk Probability Impact Mitigation
Broken URLs High Medium Validate URLs before scraping, skip broken ones, log for manual review
Rate Limiting/IP Blocking Medium High Conservative rate limiting, exponential backoff, user-agent rotation
HTML Structure Changes Low High Use AI extraction (Gemini) which is more resilient, version scrapers
Entity Resolution Accuracy Medium High Confidence thresholds, manual review for ambiguous cases, cache resolutions
Duplicate Detection False Positives Medium Medium Multi-level detection, manual review for uncertain matches
Performance at Scale Low Medium Batch processing, caching, database optimization

10.2 Data Quality Risks

Risk Probability Impact Mitigation
Incomplete Metadata High Medium Accept incompleteness, flag for manual completion
Incorrect Entity Mappings Medium High Confidence scoring, manual review, audit trail
Transliteration Variations High Medium Normalization algorithms, fuzzy matching
Missing Lyrics/Sections Medium Medium Flag for manual review, accept partial data

10.3 Operational Risks

Risk Probability Impact Mitigation
Long Import Times High Low Background processing, progress tracking, resume capability
Manual Review Bottleneck High Medium Auto-approval for high confidence, prioritize review queue
Storage Growth Low Low Archive old batches, cleanup rejected imports

11. Success Metrics

11.1 Import Volume

11.2 Data Quality

11.3 Operational Efficiency

12. Implementation Checklist

Phase 1: CSV Parsing & Validation

Phase 2: Batch Scraping

Phase 3: Entity Resolution & De-duplication

Phase 4: Review Workflow Integration

13. Recommendations

13.1 Immediate Actions (Week 1)

  1. Start with CSV Parsing: Build CSV parser and validator first
  2. Validate All URLs: Run validation on all 3 CSV files to identify broken links
  3. Test with Small Batch: Import 10-20 entries to validate end-to-end flow
  4. Set Up Monitoring: Add logging and progress tracking from day one

13.2 Medium-Term (Weeks 2-4)

  1. Implement Batch Scraping: Build robust batch scraping with rate limiting
  2. Entity Resolution: Focus on composer and raga resolution (most critical)
  3. De-duplication: Implement multi-level duplicate detection
  4. Review Workflow: Enhance UI for efficient batch review

13.3 Long-Term (Months 2-3)

  1. Optimize Performance: Cache, batch operations, database optimization
  2. Improve Accuracy: Refine entity resolution algorithms based on learnings
  3. Automation: Increase auto-approval threshold as confidence grows
  4. Documentation: Create runbooks and troubleshooting guides

14. Conclusion

This strategy provides a comprehensive plan for bulk importing Krithis from CSV files. The phased approach balances speed of implementation with quality assurance, leveraging existing infrastructure while adding necessary enhancements.

Key Success Factors:

  1. ✅ Leverage existing WebScrapingService and ImportService
  2. ✅ Implement robust entity resolution for composer and raga
  3. ✅ Multi-level de-duplication to avoid canonical duplicates
  4. ✅ Quality scoring to prioritize review workflow
  5. ✅ Batch processing with progress tracking and resume capability
  6. ✅ Conservative rate limiting to avoid IP blocking

Expected Outcomes:

The implementation can begin immediately with Phase 1 (CSV parsing), building incrementally toward a production-ready bulk import system.

15. References

Documentation home · Feature status