Sangeetha-Grantha

Metadata	Value
Status	Active
Version	1.1.0
Last Updated	2026-09-10
Author	Sangeetha Grantha Team
Document Type	Design reference

Database Layer Optimization & Modernization

---

[!NOTE] Design/reference material: this page may include proposals or earlier implementation assumptions. Use current feature map for implemented behavior and current operating steps.

Implementation Date: 2025-01-27

Executive Summary

This feature optimizes the application’s Data Access Layer (DAL) by leveraging modern features of the Kotlin Exposed ORM framework. The primary goals are to reduce database round-trips, improve transaction efficiency, and eliminate anti-patterns like “Delete-Insert” for collection updates.

✅ All optimizations have been successfully implemented across all repositories.

Problem Statement

The previous implementation of the DAL suffered from several inefficiencies:

1. Delete-Insert Anti-Pattern: Updating child collections (e.g., KrithiSections) involved deleting all existing records and re-inserting the new set. This caused:
   • Loss of metadata (e.g., original created_at timestamps).
   • Unnecessary expansion of transaction logs.
   • Potential indexing churn.
2. Multiple Round-Trips: Operations like create and update required two separate database queries: one to perform the action and another to SELECT the resulting entity to return to the caller. This doubled the network latency for these critical operations.

Solution Implemented

1. Smart Collection Updates (Delta Updates) ✅ COMPLETED

Collection management has been refactored to implement a smart diffing algorithm:

• Fetch existing records.
• Compare with incoming data to identify:
  • Updates: Records that exist in both but have changed values.
  • Inserts: New records not present in the database.
  • Deletes: Records in the database not present in the new set.
• Execute targeted batchInsert, update, and deleteWhere operations.

Implemented in:

• KrithiRepository.saveSections() - Smart diffing for krithi sections
• KrithiRepository.saveLyricVariantSections() - Smart diffing for lyric variant sections
• KrithiRepository.update() - Smart diffing for raga associations
• KrithiRepository.updateTags() - Smart diffing for tag associations

Benefits:

• ✅ Preserves history and metadata of unchanged/updated records.
• ✅ Minimizes database writes.
• ✅ Reduces transaction log expansion.

2. Single Round-Trip Persistence (Exposed Returning) ✅ COMPLETED

All repositories now utilize RETURNING clause capabilities to perform writes and reads in a single query.

Implementation Pattern:

• Create Operations: Use insert { ... }.resultedValues to retrieve the generated ID and default values immediately.
• Update Operations: Use Table.updateReturning() to apply changes and retrieve the updated record in one atomic operation.

Repositories Optimized:

1. ✅ UserRepository - create(), update()
2. ✅ KrithiRepository - create(), update(), createLyricVariant(), updateLyricVariant()
3. ✅ ImportRepository - createImport(), reviewImport()
4. ✅ ComposerRepository - create(), update()
5. ✅ RagaRepository - create(), update()
6. ✅ TalaRepository - create(), update()
7. ✅ TempleRepository - create(), update()
8. ✅ TagRepository - create(), update()
9. ✅ KrithiNotationRepository - createVariant(), updateVariant(), createRow(), updateRow()

Code Pattern Example:

// Create with RETURNING
suspend fun create(...): EntityDto = DatabaseFactory.dbQuery {
    EntityTable.insert {
        // ... field assignments
    }
        .resultedValues
        ?.single()
        ?.toEntityDto()
        ?: error("Failed to insert entity")
}

// Update with RETURNING
suspend fun update(id: Uuid, ...): EntityDto? = DatabaseFactory.dbQuery {
    EntityTable
        .updateReturning(
            where = { EntityTable.id eq id.toJavaUuid() }
        ) {
            // ... field updates
        }
        .singleOrNull()
        ?.toEntityDto()
}

Technical Requirements

• Framework: JetBrains Exposed ORM 1.0.0-rc-4 (supports updateReturning and resultedValues)
• Database: PostgreSQL 18 (dev pinned via Docker Compose) / 15+ (prod) (fully supports RETURNING clause)

Impact Analysis

Performance Improvements ✅ ACHIEVED

• Query Reduction: ~40-50% reduction in queries per create/update operation
• Latency Reduction: Eliminated one round-trip per create/update operation
• Network Overhead: Reduced data transfer by eliminating redundant SELECT queries
• Transaction Efficiency: Atomic operations reduce lock contention

Data Integrity ✅ ACHIEVED

• Audit Fields: Better preservation of created_at timestamps on sub-resources
• Metadata Preservation: Unchanged records maintain their original metadata
• Atomic Operations: RETURNING clauses ensure consistency

Code Quality ✅ ACHIEVED

• Consistent Patterns: All repositories follow the same optimization pattern
• Idiomatic Exposed: Proper use of modern Exposed ORM features
• Maintainability: Clear, readable code with consistent error handling

Implementation Details

Before Optimization

// Two queries: INSERT + SELECT
KrithisTable.insert { ... }
KrithisTable.selectAll().where { ... }.single()

After Optimization

// Single query: INSERT with RETURNING
KrithisTable.insert { ... }
    .resultedValues
    ?.single()
    ?.toKrithiDto()

Before Optimization

// Two queries: UPDATE + SELECT
KrithisTable.update { ... }
KrithisTable.selectAll().where { ... }.singleOrNull()

After Optimization

// Single query: UPDATE with RETURNING
KrithisTable.updateReturning(where = { ... }) { ... }
    .singleOrNull()
    ?.toKrithiDto()

Testing

• ✅ All repository methods tested and verified
• ✅ No linting errors introduced
• ✅ Backward compatibility maintained
• ✅ Error handling preserved

Related Documentation

• Exposed RC-4 Features Testing
• Query Optimization Evaluation
• Backend System Design

---

Section index · Documentation home · Feature status