| Metadata | Value |
|---|---|
| Status | Active |
| Version | 1.1.0 |
| Last Updated | 2026-09-10 |
| Author | Sangeetha Grantha Team |
| Document Type | Decision record |
[!NOTE] Decision record: preserve the original rationale and check its decision/supersession status. Current runtime guidance is in system architecture and Flyway migrations.
The KrithiRepository.saveSections() method was using an inefficient DELETE+INSERT pattern that:
created_at timestampskrithi_lyric_sectionsBefore (DELETE+INSERT):
-- Deletes ALL sections
DELETE FROM krithi_sections WHERE krithi_id = '50756508-a56c-4053-b654-f44cc372cb74'
-- Re-inserts ALL sections (even unchanged ones)
INSERT INTO krithi_sections (...) VALUES (...)
INSERT INTO krithi_sections (...) VALUES (...)
INSERT INTO krithi_sections (...) VALUES (...)
After (UPDATE/INSERT/DELETE):
-- Only updates changed section
UPDATE krithi_sections
SET section_type = 'CHARANAM', updated_at = NOW()
WHERE id = '1aab9810-fea6-4ef8-920d-a54e4219f6bf'
-- Only inserts new section (if any)
INSERT INTO krithi_sections (...) VALUES (...)
-- Only deletes removed section (if any)
DELETE FROM krithi_sections WHERE id = '...'
List<Pair<String, Int>> to List<Triple<String, Int, String?>>(sectionType, orderIndex, label) to preserve label informationorder_indexorder_index, different sectionType or labelorder_index not in existing sectionsorder_index not in new sectionscreated_at timestamps are preserved for existing sectionsnotes field is preserved (not in request, so not overwritten)updated_at is modified for changed sectionsKrithiRepository.kt)suspend fun saveSections(krithiId: Uuid, sections: List<Triple<String, Int, String?>>) = DatabaseFactory.dbQuery {
val now = OffsetDateTime.now(ZoneOffset.UTC)
val javaKrithiId = krithiId.toJavaUuid()
// Get existing sections indexed by order_index
val existingSections = KrithiSectionsTable
.selectAll()
.where { KrithiSectionsTable.krithiId eq javaKrithiId }
.associateBy { it[KrithiSectionsTable.orderIndex] }
// Build map of new sections by order_index
val newSectionsMap = sections.associateBy { it.second }
// Determine what to update, insert, and delete
val toUpdate = mutableListOf<Triple<UUID, String, String?>>()
val toInsert = mutableListOf<Triple<String, Int, String?>>()
val toDelete = mutableListOf<UUID>()
// Process existing sections
existingSections.forEach { (orderIndex, row) ->
val existingId = row[KrithiSectionsTable.id].value
val existingType = row[KrithiSectionsTable.sectionType]
val existingLabel = row[KrithiSectionsTable.label]
val newSection = newSectionsMap[orderIndex]
if (newSection != null) {
val (newType, _, newLabel) = newSection
if (newType != existingType || newLabel != existingLabel) {
toUpdate.add(Triple(existingId, newType, newLabel))
}
} else {
toDelete.add(existingId)
}
}
// Find sections to insert
newSectionsMap.forEach { (orderIndex, section) ->
if (!existingSections.containsKey(orderIndex)) {
toInsert.add(section)
}
}
// Execute updates
toUpdate.forEach { (id, sectionType, label) ->
KrithiSectionsTable.update({ KrithiSectionsTable.id eq id }) {
it[KrithiSectionsTable.sectionType] = sectionType
it[KrithiSectionsTable.label] = label
it[KrithiSectionsTable.updatedAt] = now
}
}
// Execute inserts
if (toInsert.isNotEmpty()) {
KrithiSectionsTable.batchInsert(toInsert) { section ->
val (sectionType, orderIndex, label) = section
this[KrithiSectionsTable.id] = UUID.randomUUID()
this[KrithiSectionsTable.krithiId] = javaKrithiId
this[KrithiSectionsTable.sectionType] = sectionType
this[KrithiSectionsTable.orderIndex] = orderIndex
this[KrithiSectionsTable.label] = label
this[KrithiSectionsTable.notes] = null
this[KrithiSectionsTable.createdAt] = now
this[KrithiSectionsTable.updatedAt] = now
}
}
// Execute deletes
if (toDelete.isNotEmpty()) {
KrithiSectionsTable.deleteWhere {
KrithiSectionsTable.id inList toDelete
}
}
}
KrithiService.kt)suspend fun saveKrithiSections(id: Uuid, sections: List<KrithiSectionRequest>) {
// Pass full section data including label for efficient updates
val sectionsData = sections.map {
Triple(it.sectionType, it.orderIndex, it.label)
}
dal.krithis.saveSections(id, sectionsData)
dal.auditLogs.append(
action = "UPDATE_KRITHI_SECTIONS",
entityTable = "krithi_sections",
entityId = id
)
}
created_at timestamps maintainednotes field preservedkrithi_lyric_sections references remain valid✅ More efficient SQL generation ✅ Preserves metadata (created_at, notes) ✅ Maintains foreign key relationships ✅ Better performance for partial updates
⚠️ More complex code (diff logic) ⚠️ Requires reading existing data first (one extra query) ⚠️ Slightly more memory usage (loading existing sections)
The DELETE+INSERT pattern is still acceptable for:
krithi_tags)created_at is preservednotes field is preservedsaveLyricVariantSections() ✅ OPTIMIZED
(lyric_variant_id, section_id) unique constraintcreated_at, normalized_text metadataupdateTags() ✅ OPTIMIZED
tag_id (composite primary key with krithi_id)sourceInfo, confidence for tags that remainupdate() method for ragas ✅ OPTIMIZED
(ragaId, orderIndex) composite keybatchUpdate() if Exposed supports it| Method | Table | Key Strategy | Metadata Preserved | Status |
|---|---|---|---|---|
saveSections() |
krithi_sections |
Match by order_index |
created_at, notes |
✅ Optimized |
saveLyricVariantSections() |
krithi_lyric_sections |
Match by section_id |
created_at, normalized_text |
✅ Optimized |
updateTags() |
krithi_tags |
Match by tag_id |
sourceInfo, confidence |
✅ Optimized |
update() ragas |
krithi_ragas |
Match by (ragaId, orderIndex) |
N/A (composite key) | ✅ Optimized |
Before Optimization:
After Optimization:
All identified DELETE+INSERT patterns have been successfully optimized. The implementations:
The codebase now uses efficient UPDATE/INSERT/DELETE operations that only process what actually changed, resulting in significant performance improvements and better data integrity.