ADR 001: Item Table Design - Type-Specific Extension Tables

Accepted architecture decision for Homey's base item table and type-specific extension tables.

Status: Accepted

Date: 2026-01-11

Deciders: Architecture Team

Context

Homey manages diverse household items with varying attributes:

Groceries - require expiry dates, quantities, brands
Medications - need dosage, frequency, refill dates (future)
Clothing - track size, color, season (future)
Documents - store metadata, scan dates (future)
Generic items - minimal metadata

The decision: How to model items in the database to support current needs (groceries) and future expansion while maintaining type safety and data integrity.

Decision

Chosen: Type-Specific Extension Tables (Joined-Table Inheritance)

Schema

-- Base table (required for all items)
CREATE TABLE items (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    category TEXT NOT NULL,              -- 'GROCERY', 'MEDICATION', etc.
    location_id TEXT NOT NULL,
    created_at INTEGER NOT NULL,
    notes TEXT,                           -- Shared: all items can have notes
    tags TEXT,                            -- Shared: all items can be tagged
    photo_uri TEXT,                       -- Shared: all items can have photos
    FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE CASCADE
);

-- Type-specific extension (optional, 1:1 with items)
CREATE TABLE grocery_items (
    item_id TEXT PRIMARY KEY,
    expiry_date INTEGER NOT NULL,        -- Required for groceries
    brand TEXT,
    quantity REAL NOT NULL DEFAULT 1.0,
    unit TEXT NOT NULL DEFAULT 'unit',
    FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE
);

-- Future extensions follow same pattern
CREATE TABLE medication_items (
    item_id TEXT PRIMARY KEY,
    dosage TEXT NOT NULL,
    frequency TEXT NOT NULL,
    refill_date INTEGER,
    FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE
);

Room Implementation

// Base entity
@Entity(tableName = "items")
data class ItemEntity(
    @PrimaryKey val id: String,
    val name: String,
    val category: ItemCategory,
    val locationId: String,
    val createdAt: Instant,
    val notes: String? = null,
    val tags: String? = null,
    val photoUri: String? = null
)

// Type-specific extension
@Entity(
    tableName = "grocery_items",
    foreignKeys = [ForeignKey(
        entity = ItemEntity::class,
        parentColumns = ["id"],
        childColumns = ["itemId"],
        onDelete = CASCADE
    )]
)
data class GroceryItemEntity(
    @PrimaryKey val itemId: String,
    val expiryDate: Instant,              // Non-nullable = required
    val brand: String? = null,
    val quantity: Double = 1.0,
    val unit: String = "unit"
)

// Room relation for joined queries
data class GroceryItemWithDetails(
    @Embedded val item: ItemEntity,
    @Relation(
        parentColumn = "id",
        entityColumn = "itemId"
    )
    val groceryDetails: GroceryItemEntity
)

enum class ItemCategory {
    GROCERY,
    MEDICATION,  // Future
    CLOTHING,    // Future
    DOCUMENT,    // Future
    GENERIC
}

Rationale

Why This Design Wins

1. Type Safety at Compile-Time

Non-nullable fields enforce business rules:

// ✅ Compiler prevents invalid state
val grocery = GroceryItemEntity(
    itemId = "123",
    expiryDate = Instant.now()  // Must provide - won't compile without it
)

// ❌ Runtime validation fragile
data class Item(val expiryDate: Instant?)
require(expiryDate != null)  // Easy to forget

2. Database Integrity via Foreign Keys

Cascading deletes prevent orphaned records:

DELETE FROM items WHERE id = '123';
-- grocery_items row automatically deleted via FK constraint

No manual cleanup needed. Database enforces consistency.

3. Query Flexibility

Efficient queries for different use cases:

// List all items (lightweight)
@Query("SELECT * FROM items WHERE location_id = :locationId")
suspend fun getItemsByLocation(locationId: String): List<ItemEntity>

// Get grocery details (joined)
@Query("""
    SELECT * FROM items
    INNER JOIN grocery_items ON items.id = grocery_items.item_id
    WHERE category = 'GROCERY' AND expiry_date < :date
""")
suspend fun getExpiringGroceries(date: Instant): List<GroceryItemWithDetails>

No wasted columns, no null-heavy rows.

4. Shared Attributes Without Duplication

Common fields (notes, tags, photos) live in base table:

// ✅ Any item can have notes
val pasta = ItemEntity(..., notes = "Brand X tastes bad")
val medicine = ItemEntity(..., notes = "Take with food")

No redundancy across extension tables.

5. Extensibility Without Breaking Changes

Adding new item types = new extension table:

-- Add to schema, no migration of existing data
CREATE TABLE medication_items (...);

Existing grocery_items unaffected. Clean separation.

Alternatives Considered

Alternative 1: Single Table with Nullable Columns

data class Item(
    val id: String,
    val name: String,
    val category: ItemCategory,
    val locationId: String,

    // Type-specific (nullable)
    val expiryDate: Instant? = null,
    val brand: String? = null,
    val dosage: String? = null,
    val frequency: String? = null,
    val size: String? = null,
    val color: String? = null
    // ... grows with every item type
)

Rejected because:

❌ No compile-time safety - can create grocery without expiry
❌ Database can’t enforce - nullable columns = validation in application layer
❌ Schema bloat - 50 item types = 200+ nullable columns
❌ Storage waste - groceries waste space for medication columns
❌ Runtime errors - item.expiryDate!! crashes if missing

When it’s acceptable: Generic inventory apps (Sortly, Airtable) where flexibility > correctness.

Why not for Homey: We have opinionated workflows. Groceries MUST have expiry dates for the app to function. Type system should enforce this.

Alternative 2: Separate Tables per Type (No Base)

// No shared table
@Entity(tableName = "grocery_items")
data class GroceryItemEntity(
    @PrimaryKey val id: String,
    val name: String,
    val locationId: String,
    val expiryDate: Instant,
    val notes: String? = null  // Duplicate
)

@Entity(tableName = "medication_items")
data class MedicationItemEntity(
    @PrimaryKey val id: String,
    val name: String,
    val locationId: String,
    val dosage: String,
    val notes: String? = null  // Duplicate
)

Rejected because:

❌ Shared field duplication - notes/tags/photos repeated everywhere
❌ Polymorphic queries impossible - can’t "get all items in location"
❌ Navigation breaks - Item ID alone doesn’t tell you which table to query
❌ Migration nightmare - changing shared fields = update N tables

When it’s acceptable: Completely independent entities (users vs. orders).

Why not for Homey: Items are a single domain concept that varies by subtype. Natural hierarchy.

Alternative 3: JSON/EAV (Entity-Attribute-Value)

@Entity(tableName = "items")
data class ItemEntity(
    @PrimaryKey val id: String,
    val name: String,
    val category: ItemCategory,
    val attributes: String  // JSON blob: {"expiryDate": "...", "brand": "..."}
)

// Or separate EAV table
@Entity(tableName = "item_attributes")
data class ItemAttribute(
    val itemId: String,
    val key: String,    // "expiryDate"
    val value: String   // "2024-01-15"
)

Rejected because:

❌ Zero type safety - everything is String
❌ No database constraints - can’t enforce "expiryDate is required"
❌ Query performance - JSON parsing or 10-way joins for attributes
❌ Migration hell - schema changes = parse all JSON blobs
❌ IDE support lost - no autocomplete, no refactoring

When it’s acceptable: User-defined custom fields (CRM systems).

Why not for Homey: Attributes are known at compile-time. No user customization needed.

Alternative 4: Class Table Inheritance (Three Layers)

// Abstract superclass
@Entity(tableName = "entities")
data class Entity(
    @PrimaryKey val id: String,
    val entityType: String
)

// Item subclass
@Entity(tableName = "items")
data class ItemEntity(
    @PrimaryKey val id: String,
    val name: String,
    val locationId: String,
    val itemType: String,
    FOREIGN KEY (id) REFERENCES entities(id)
)

// Grocery subclass
@Entity(tableName = "grocery_items")
data class GroceryItemEntity(
    @PrimaryKey val id: String,
    val expiryDate: Instant,
    FOREIGN KEY (id) REFERENCES items(id)
)

Rejected because:

❌ Over-engineered - unnecessary abstraction (Entity)
❌ 3-table join for simple queries
❌ Complex for no gain - Item isn’t a subclass of anything

When it’s acceptable: Deep hierarchies (Animal → Mammal → Dog).

Why not for Homey: Items are top-level. Two-table join sufficient.

Consequences

Positive

✅ Compiler catches missing required fields
✅ Database enforces referential integrity
✅ Extension tables only loaded when needed (performance)
✅ Shared attributes centralized (single source of truth)
✅ Future item types = add table, no data migration

Negative

⚠️ More tables than single-table approach
⚠️ Requires foreign key support (SQLite has this)
⚠️ Slight complexity in queries (2-table join)

Migration Strategy

For MVP (Groceries only):

@Database(
    entities = [ItemEntity::class, GroceryItemEntity::class],
    version = 1
)

When adding Medications:

@Database(
    entities = [
        ItemEntity::class,
        GroceryItemEntity::class,
        MedicationItemEntity::class  // NEW
    ],
    version = 2
)

val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(db: SupportSQLiteDatabase) {
        db.execSQL("""
            CREATE TABLE medication_items (
                item_id TEXT PRIMARY KEY NOT NULL,
                dosage TEXT NOT NULL,
                frequency TEXT NOT NULL,
                refill_date INTEGER,
                FOREIGN KEY(item_id) REFERENCES items(id) ON DELETE CASCADE
            )
        """)
        // No existing data needs migration
    }
}

No destructive changes. Additive only.

Implementation Guidelines

1. Adding a New Item Type

Criteria: * Has ≥3 type-specific required attributes * Has domain logic distinct from grocery workflow * Is a planned MVP or post-MVP feature

Steps: 1. Create *ItemEntity with non-nullable required fields 2. Add to @Database entities list 3. Write migration (DDL only, no data changes) 4. Create DAO methods for type-specific queries 5. Add category to ItemCategory enum

2. Shared Attributes

If an attribute applies to all or most item types: * Add to ItemEntity base table as nullable * Examples: notes, tags, photoUri, purchaseDate

If an attribute applies to one type only: * Add to type-specific extension table * Examples: expiryDate (groceries), dosage (medications)

3. Avoiding Premature Optimization

Don’t create extension tables "just in case": * ❌ ClothingItemEntity before clothing feature exists * ❌ GenericItemEntity as catch-all (use ItemEntity + GENERIC category)

Create only when: * Feature is in active development * Type-specific attributes are defined * Business logic differs from existing types

References

Martin Fowler - Patterns of Enterprise Application Architecture (Class Table Inheritance)
Android Room Documentation - Relations
SQL Antipatterns (Bill Karwin) - EAV Pattern
Domain-Driven Design (Eric Evans) - Entities and Value Objects

Decision Log

2024-07-10: Initial schema created with grocery_items
2026-01-11: Documented as ADR after architecture review
2026-01-11: Removed ClothingItemEntity and GenericItemEntity (premature)