Kotlin Schema-First Room Entity Implementation

Kotlin Schema-First Room Entity Implementation

When to Use This Skill

MANDATORY when:

• Creating ANY @Entity class for Room database
• Android app mirrors a Django/​PostgreSQL backend
• Backend has Pydantic type-safe contracts
• Documentation exists for PostgreSQL → SQLite schema mapping

Triggers:

• About to write @Entity annotation
• Creating data models for offline-first mobile app
• Implementing sync between backend database and SQLite

Core Principle

Android SQLite is a REPLICA of PostgreSQL, not an independent schema.

Schema Authority Hierarchy:

1. PostgreSQL (Django Models) ← SOURCE OF TRUTH
2. Pydantic Validation ← Contract enforcement
3. OpenAPI Schema ← API contract
4. Kotlin DTOs ← Generated from OpenAPI
5. Room Entities ← Hand-coded to match DTOs ± denormalization

---

MANDATORY VERIFICATION PROTOCOL

Before Creating ANY Room Entity

STOP and complete this checklist:

• Step 1: Locate schema mapping documentation

  • Find: docs/​api-contracts/​POSTGRESQL_TO_SQLITE_SCHEMA_MAPPING.md (or equivalent)
  • If missing: FAIL - Cannot proceed without schema reference

• Step 2: Find the corresponding section

  • Example: Section 1.2 for People domain, Section 1.1 for Attendance, etc.
  • Read the COMPLETE field list for that Django model

• Step 3: Copy EXACT field list

  • Do NOT add fields not in Django model
  • Do NOT remove fields from Django model
  • Do NOT assume field types
  • Use the field list EXACTLY as documented

• Step 4: Verify type mappings

  • Check Section 1 of schema mapping doc for type conversion rules
  • DateTimeField → Long (epoch ms), NOT String
  • DecimalField → String (for precision), NOT Double/​Float
  • BooleanField → Boolean (stored as INTEGER 0/​1)
  • JSONField with known structure → Typed data class, NOT Map<String, Any>
  • ForeignKey → Int/​Long (store ID only)
  • PointField (PostGIS) → Separate lat/​lng Double columns OR WKT String

• Step 5: Document denormalization

  • If flattening multiple Django tables → Document in KDoc
  • Justify with performance rationale
  • List which Django tables are merged

• Step 6: Add KDoc with schema authority

  /​**
   * ⚠️ SCHEMA AUTHORITY: Django model apps/​domain/​models/​model_name.py
   *
   * FIELD MAPPING: [list Django fields → Kotlin properties]
   *
   * DENORMALIZATION: [if applicable, explain what's merged and why]
   *
   * Last Verified Against Django: YYYY-MM-DD
   */
  

---

CRITICAL PATTERNS

Pattern 1: Hybrid Naming Convention (REQUIRED)

@Entity(tableName = "table_name")  // ← PostgreSQL table name
data class EntityName(
    @ColumnInfo(name = "django_field")  // ← MUST be PostgreSQL column name
    val kotlinProperty: Type             // ← CAN be idiomatic Kotlin name
)

Example:

@Entity(tableName = "peopleeventlog")
data class AttendanceEntity(
    @ColumnInfo(name = "punchintime")  // ← Django field (PostgreSQL column)
    val checkInTime: Long,              // ← Kotlin property (camelCase, descriptive)

    @ColumnInfo(name = "people")       // ← Django FK field
    val personId: Int,                  // ← Kotlin property (explicit it's ID)

    @ColumnInfo(name = "peoplename")   // ← Django field
    val fullName: String                // ← Kotlin property (more descriptive)
)

Rule:

• @ColumnInfo(name) = Django field name (becomes SQLite column)
• Kotlin property = Idiomatic camelCase name

---

Pattern 2: Indexes MUST Match PostgreSQL (REQUIRED)

@Entity(
    tableName = "peopleeventlog",
    indices = [
        Index(value = ["tenant", "cdtz"]),           // ← From Django Meta.indexes
        Index(value = ["tenant", "people"]),         // ← Column names are Django fields
        Index(value = ["tenant", "datefor"]),
        Index(value = ["tenant", "people", "shift", "datefor"],
              name = "pel_validation_lookup_idx")    // ← Composite index
    ]
)

Rule: Copy PostgreSQL indexes exactly (use Django field names in value arrays).

---

Pattern 3: Foreign Keys with Cascade Behavior (REQUIRED)

@Entity(
    foreignKeys = [
        ForeignKey(
            entity = PersonEntity::class,
            parentColumns = ["id"],
            childColumns = ["people"],          // ← Django field name
            onDelete = ForeignKey.RESTRICT      // ← Matches Django on_delete
        )
    ]
)
data class AttendanceEntity(
    @ColumnInfo(name = "people") val personId: Int  // ← Store FK ID
)

Django on_delete mapping:

• models.CASCADE → ForeignKey.CASCADE
• models.RESTRICT → ForeignKey.RESTRICT
• models.SET_NULL → ForeignKey.SET_NULL

CRITICAL: Enable FK constraints in database:

override fun init(configuration: DatabaseConfiguration) {
    super.init(configuration)
    openHelper.writableDatabase.execSQL("PRAGMA foreign_keys=ON;")
}

---

Pattern 4: TypeConverters for Complex Types (REQUIRED)

For DateTime (MANDATORY):

@TypeConverter
fun fromTimestamp(value: Long?): Instant? =
    value?.let { Instant.fromEpochMilliseconds(it) }

@TypeConverter
fun toTimestamp(instant: Instant?): Long? =
    instant?.toEpochMilliseconds()

For Typed JSON (REQUIRED for known structures):

// ❌ WRONG: Untyped Map
@ColumnInfo(name = "capabilities") val capabilities: Map<String, Any>

// ✅ CORRECT: Typed data class
@Serializable
data class UserCapabilities(
    @SerialName("can_use_ai_query") val canUseAiQuery: Boolean = false,
    // ... match Django JSONField structure EXACTLY
)

@TypeConverter
fun fromCapabilities(value: UserCapabilities): String =
    Json.encodeToString(value)

@TypeConverter
fun toCapabilities(value: String): UserCapabilities =
    Json.decodeFromString(value)

---

FORBIDDEN ANTI-PATTERNS

❌ Anti-Pattern 1: Inventing Fields

// ❌ WRONG: Field not in Django model
@Entity
data class UserEntity(
    val firstName: String,   // ← Django doesn't have this!
    val lastName: String,    // ← Django has "peoplename" only!
    val isFavorite: Boolean  // ← Not in Django model!
)

Consequence: Sync failures, data loss, schema drift.

How to detect: Missing field in schema mapping doc = FORBIDDEN

---

❌ Anti-Pattern 2: Wrong Type Mappings

// ❌ WRONG: Type mismatch
@ColumnInfo(name = "department") val department: String  // ← Django has FK (Int)
@ColumnInfo(name = "punchintime") val checkInTime: String  // ← Django DateTimeField → Long
@ColumnInfo(name = "hours_worked") val hoursWorked: Double  // ← Django DecimalField → String

Consequence: Type errors, precision loss, sync failures.

How to fix: Check Section 1 type mapping table.

---

❌ Anti-Pattern 3: Client-Side Timestamps

// ❌ WRONG: Generating server-authoritative timestamps
@Entity
data class AttendanceEntity(
    val createdAt: Long = Instant.now().toEpochMilliseconds()  // ← Django auto_now_add
)

Consequence: Timestamp mismatch between client and server, audit trail corruption.

How to fix: Server-authoritative timestamps come from API response.

---

❌ Anti-Pattern 4: Untyped JSON

// ❌ WRONG: Known structure as untyped map
@ColumnInfo(name = "capabilities") val capabilities: Map<String, Boolean>

Consequence: Runtime errors, no compile-time safety, typos in keys.

How to fix: Create typed data class matching Django JSONField default.

---

REQUIRED VALIDATION STEPS

Step 1: Field Count Verification

/​**
 * Django People model has 20+ fields.
 * This entity must include ALL exposed via API.
 *
 * Verified field count: 30 (People: 15, Profile: 8, Organizational: 7)
 */
@Entity
data class UserEntity(
    // List all 30 fields here...
)

Test: Count fields in Django model = Count fields in Room entity (± documented exclusions).

---

Step 2: Enum Value Verification

// Django TextChoices
class AssignmentStatus(models.TextChoices):
    SCHEDULED = 'SCHEDULED', 'Scheduled'
    CONFIRMED = 'CONFIRMED', 'Confirmed'
    IN_PROGRESS = 'IN_PROGRESS', 'In Progress'

// Kotlin enum MUST match EXACTLY (case-sensitive)
enum class AssignmentStatus {
    @SerialName("SCHEDULED") SCHEDULED,
    @SerialName("CONFIRMED") CONFIRMED,
    @SerialName("IN_PROGRESS") IN_PROGRESS
}

// ❌ WRONG: Different values
enum class AssignmentStatus {
    SCHEDULED,  // Missing @SerialName - won't deserialize correctly
    Confirmed,  // Wrong case - won't match Django
    InProgress  // Wrong format - won't match Django
}

Write test:

@Test
fun `enum values match Django choices`() {
    val expected = setOf("SCHEDULED", "CONFIRMED", "IN_PROGRESS")
    val actual = AssignmentStatus.values().map { it.name }.toSet()
    assertEquals(expected, actual)
}

---

Step 3: Type Conversion Round-Trip Test

@Test
fun `DateTime conversion is lossless`() {
    val original = Instant.parse("2025-11-08T14:30:00Z")
    val epoch = original.toEpochMilliseconds()
    val restored = Instant.fromEpochMilliseconds(epoch)
    assertEquals(original, restored)
}

@Test
fun `Decimal conversion preserves precision`() {
    val original = BigDecimal("8.123456")
    val text = original.toPlainString()
    val restored = text.toBigDecimal()
    assertEquals(original, restored)
}

---

ENFORCEMENT CHECKLIST

Before marking Room entity complete:

• Schema mapping doc read for this domain
• ALL Django fields accounted for (or exclusion documented)
• @ColumnInfo names match Django field names exactly
• Type mappings verified against mapping table
• Indexes copied from Django Meta.indexes
• Foreign keys have correct cascade behavior
• TypeConverters defined for complex types
• KDoc includes Django model file path
• Denormalization documented (if applicable)
• Verification date added to KDoc

If ANY checkbox is unchecked → Entity is NOT complete.

---

SUCCESS CRITERIA

✅ Room entity compiles without errors ✅ KSP generates DAO implementation successfully ✅ Type conversion tests pass (round-trip lossless) ✅ Enum validation tests pass (match Django choices) ✅ SQLite column names match PostgreSQL exactly ✅ No fields invented (all from Django model) ✅ No type mismatches (all per mapping table) ✅ All complex types have TypeConverters

---

REFERENCE DOCUMENTS

Primary: docs/​api-contracts/​POSTGRESQL_TO_SQLITE_SCHEMA_MAPPING.md

• Section 1: Type mapping table (18 Django types)
• Section 1.1: Attendance domain models
• Section 1.2: People domain models
• Section 1.3: Operations domain models
• Section 1.4: Journal domain models
• Section 1.5: Helpdesk domain models

Secondary: docs/​api-contracts/​SCHEMA_FIRST_DATA_CONTRACT_VALIDATION.md

• Forbidden patterns
• Required patterns
• 8-point validation pipeline

Skill Guides: docs/​api-contracts/​skills/​ROOM_IMPLEMENTATION_GUIDE.md

• Schema modification workflow
• TypeConverter patterns
• Migration strategies

---

INTEGRATION WITH OTHER SKILLS

Use with:

• room-database-implementation - For Room-specific errors (missing TypeConverters, FK issues)
• kotlin-coroutines-safety - For DAO suspend functions and Flow
• offline-first-architecture - For sync status fields and pending operations

This skill adds: Schema alignment verification on TOP of Room implementation best practices.

---

EXAMPLE: Correct Implementation

/​**
 * Room Entity for Attendance records (Check-in/​Check-out).
 *
 * ⚠️ SCHEMA AUTHORITY: Django model
 *   - File: apps/​attendance/​models/​people_eventlog.py
 *   - Model: PeopleEventlog
 *
 * DENORMALIZATION: None (exact replica of Django model)
 *
 * FIELD MAPPING (per POSTGRESQL_TO_SQLITE_SCHEMA_MAPPING.md Section 1.1):
 *   - Django punchintime (DateTimeField) → Kotlin Long (epoch ms)
 *   - Django people (ForeignKey) → Kotlin Int (person ID)
 *   - Django startlocation (PointField) → Kotlin Double lat/​lng (denormalized)
 *   - Django peventlogextras (EncryptedJSONField) → Kotlin String (encrypted JSON)
 *
 * Last Verified: 2025-11-08
 */
@Entity(
    tableName = "peopleeventlog",  // ← PostgreSQL table name
    foreignKeys = [
        ForeignKey(
            entity = PersonEntity::class,
            parentColumns = ["id"],
            childColumns = ["people"],
            onDelete = ForeignKey.RESTRICT  // ← Matches Django on_delete
        )
    ],
    indices = [
        Index(value = ["tenant", "cdtz"]),  // ← From Django Meta.indexes
        Index(value = ["tenant", "people"]),
        Index(value = ["tenant", "datefor"])
    ]
)
data class AttendanceEntity(
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "id") val id: Int? = null,

    @ColumnInfo(name = "uuid")
    val uuid: String = UUID.randomUUID().toString(),

    @ColumnInfo(name = "people")  // ← Django FK field name
    val personId: Int?,            // ← Kotlin property (explicit)

    @ColumnInfo(name = "client")
    val clientId: Int?,

    @ColumnInfo(name = "bu")
    val buId: Int?,

    @ColumnInfo(name = "punchintime")  // ← Django DateTimeField
    val checkInTime: Long?,             // ← Long epoch (per mapping table)

    @ColumnInfo(name = "punchouttime")
    val checkOutTime: Long?,

    @ColumnInfo(name = "datefor")      // ← Django DateField
    val attendanceDate: String?,        // ← ISO8601 date string

    @ColumnInfo(name = "start_latitude")  // ← Denormalized from PointField
    val startLatitude: Double?,

    @ColumnInfo(name = "start_longitude")
    val startLongitude: Double?,

    @ColumnInfo(name = "peventlogextras")  // ← EncryptedJSONField
    val eventExtras: String = "{}",         // ← Store encrypted JSON

    @ColumnInfo(name = "version")      // ← VersionField
    val version: Int = 1,               // ← Optimistic locking

    @ColumnInfo(name = "tenant")       // ← Multi-tenant FK
    val tenantId: Int?,

    @ColumnInfo(name = "cdtz")         // ← auto_now_add
    val createdAt: Long,                // ← Server-authoritative

    @ColumnInfo(name = "mdtz")         // ← auto_now
    val updatedAt: Long,                // ← Server-authoritative

    // Sync metadata (client-managed)
    @ColumnInfo(name = "mobile_id")
    val mobileId: String = UUID.randomUUID().toString(),

    @ColumnInfo(name = "sync_status")
    val syncStatus: String = "pending"
)

---

VERIFICATION TESTS (MANDATORY)

Test 1: Schema Alignment

@Test
fun `UserEntity has all fields from schema mapping doc`() {
    // Reference field list from schema doc
    val expectedFields = setOf(
        "id", "peoplecode", "peoplename", "loginid", "email",
        "isadmin", "is_staff", "enable", "gender", "dateofbirth",
        "dateofjoin", "department", "designation", "client", "bu",
        "capabilities", "tenant", "cdtz", "mdtz"
        // ... all 30 fields
    )

    val actualFields = UserEntity::class.memberProperties
        .mapNotNull { it.findAnnotation<ColumnInfo>()?.name }
        .toSet()

    val missing = expectedFields - actualFields
    val extra = actualFields - expectedFields

    assertTrue(missing.isEmpty(), "Missing Django fields: $missing")
    assertTrue(extra.isEmpty(), "Extra fields not in Django: $extra")
}

Test 2: Capabilities Match Django

@Test
fun `UserCapabilities has exact fields from Django default_capabilities`() {
    val expectedKeys = setOf(
        "can_use_ai_query",
        "can_use_nlp_search",
        "can_use_voice_commands",
        "can_use_advanced_analytics",
        "can_use_predictive_insights",
        "can_use_automated_scheduling",
        "can_use_smart_recommendations"
    )

    val json = Json.encodeToString(UserCapabilities())
    val jsonObject = Json.parseToJsonElement(json).jsonObject

    assertEquals(expectedKeys, jsonObject.keys)
}

---

COMMON ERRORS & FIXES

Error 1: "Field not in schema mapping doc"

Symptom: You want to add field notes: String to entity.

Check: Is notes in the Django model section of schema mapping doc?

• ✅ Yes → Safe to add
• ❌ No → FORBIDDEN - Do not add

Fix: If field is needed, add to Django model FIRST, then update mobile.

---

Error 2: "Capabilities structure doesn't match"

Symptom: Capabilities test fails - JSON keys don't match.

Check: Schema mapping doc has exact capabilities structure.

Fix:

// Read schema doc Section 1.2
// Copy EXACT field list for capabilities
// Do NOT invent permission fields

---

Error 3: "Type mismatch in DTO → Entity mapping"

Symptom:

// DTO has: departmentName: String (from API)
// Entity has: @ColumnInfo(name = "department") val departmentId: Int

This is CORRECT if:

• API enriches response with department name
• PostgreSQL stores department ID (FK)
• Entity stores ID (matches PostgreSQL)

Mapper handles enrichment:

fun UserDto.toEntity() = UserEntity(
    departmentId = this.departmentId  // Use ID from DTO, ignore name
)

---

SUCCESS INDICATORS

Green flags (entity is correct):

• ✅ KDoc references Django model file path
• ✅ All @ColumnInfo names are Django field names
• ✅ Field count matches schema doc (± documented denormalization)
• ✅ Type mappings match Section 1 table
• ✅ Indexes copied from Django Meta
• ✅ TypeConverters for all complex types
• ✅ Tests validate schema alignment

Red flags (entity is WRONG):

• 🚩 No KDoc referencing Django model
• 🚩 @ColumnInfo names are camelCase or different from Django
• 🚩 Field count doesn't match schema doc
• 🚩 Type mappings don't match (String instead of Int, Double instead of String)
• 🚩 No indexes defined (Django has indexes)
• 🚩 Map<String, Any> for known JSON structure
• 🚩 Invented fields (firstName, isFavorite, etc.)

---

SUMMARY

This skill enforces:

1. Schema mapping doc is authoritative source
2. Django field names in @ColumnInfo
3. Exact type mappings per documentation
4. No invented/​assumed fields
5. Typed JSON for known structures
6. Server-authoritative timestamp handling
7. FK IDs (not resolved objects)

Use this skill BEFORE room-database-implementation skill - it ensures schema alignment, then room-database-implementation ensures Room-specific correctness.

Prevents: Schema drift, sync failures, type mismatches, data loss.

Ensures: Perfect PostgreSQL → SQLite replica with zero impedance mismatch.