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

kotlin

/**
 * ⚠️ 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)

kotlin

@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:

kotlin

@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)

kotlin

@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)

kotlin

@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:

kotlin

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):

kotlin

@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):

kotlin

// ❌ 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

kotlin

// ❌ 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

kotlin

// ❌ 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

kotlin

// ❌ 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

kotlin

// ❌ 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

kotlin

/**
 * 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

kotlin

// 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:

kotlin

@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

kotlin

@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

kotlin

/**
 * 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

kotlin

@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

kotlin

@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:

kotlin

// 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:

kotlin

// 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:

kotlin

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:

Schema mapping doc is authoritative source
Django field names in @ColumnInfo
Exact type mappings per documentation
No invented/assumed fields
Typed JSON for known structures
Server-authoritative timestamp handling
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.

Search AI Tools

kotlin-schema-first-entities

Install this agent skill to your Project

SKILL.md

Kotlin Schema-First Room Entity Implementation

When to Use This Skill

Core Principle

MANDATORY VERIFICATION PROTOCOL

Before Creating ANY Room Entity

CRITICAL PATTERNS

Pattern 1: Hybrid Naming Convention (REQUIRED)

Pattern 2: Indexes MUST Match PostgreSQL (REQUIRED)

Pattern 3: Foreign Keys with Cascade Behavior (REQUIRED)

Pattern 4: TypeConverters for Complex Types (REQUIRED)

FORBIDDEN ANTI-PATTERNS

❌ Anti-Pattern 1: Inventing Fields

❌ Anti-Pattern 2: Wrong Type Mappings

❌ Anti-Pattern 3: Client-Side Timestamps

❌ Anti-Pattern 4: Untyped JSON

REQUIRED VALIDATION STEPS

Step 1: Field Count Verification

Step 2: Enum Value Verification

Step 3: Type Conversion Round-Trip Test

ENFORCEMENT CHECKLIST

SUCCESS CRITERIA

REFERENCE DOCUMENTS

INTEGRATION WITH OTHER SKILLS

EXAMPLE: Correct Implementation

VERIFICATION TESTS (MANDATORY)

Test 1: Schema Alignment

Test 2: Capabilities Match Django

COMMON ERRORS & FIXES

Error 1: "Field not in schema mapping doc"

Error 2: "Capabilities structure doesn't match"

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

SUCCESS INDICATORS

SUMMARY