DEV Community

Cover image for Room Migration v19: Adding a Unified Persons Directory Without Breaking Existing Data
DEVANSHU PATIL
DEVANSHU PATIL

Posted on

Room Migration v19: Adding a Unified Persons Directory Without Breaking Existing Data

Room Migration v19: Adding a Unified Persons Directory Without Breaking Existing Data

When an application grows organically, early data models inevitably hit structural limits.

In FinLedger's earliest releases, the lending subsystem was completely standalone. The lending_records SQLite table simply stored a raw text column: person_name TEXT NOT NULL. It worked well enough for logging simple loans to a few friends.

However, several sprints later, we designed the Split & Group Expenses Engine to allow roommates and friends to split dinner bills and vacation costs. The split engine also required contacts: people who participated in group expenses and owed shares of restaurant checks.

Suddenly, we had a serious architectural dilemma:

  • A user had "Rahul Patil" in their lending ledger with a Rs. 1,000 loan.
  • The same user had "Rahul" in their split expense ledger owing Rs. 450 for a pizza order.

Because contacts were stored as disconnected string fields in two separate tables, the application had no way of knowing that these two records belonged to the same human being. You couldn't see an aggregate person balance ("Rahul owes me Rs. 1,450 in total"), and editing a friend's phone number had to be done in multiple places.

Here is how we solved this technical debt in Room Migration v19 (MIGRATION_18_19) by unifying contacts into a single, relational persons directory without losing a single byte of user data.

The Architectural Flaw: Disconnected String Silos

flowchart TD
    subgraph SiloedSchema["The Legacy Siloed Schema (Pre-Migration 19)"]
        LendingTable["lending_records Table:
        - id: loan-01
        - person_name: 'Rahul Patil' (RAW TEXT)
        - amount: 1000.00"]

        SplitTable["split_members Table:
        - id: member-01
        - member_name: 'Rahul' (RAW TEXT)
        - share_amount: 450.00"]

        LendingTable -.->|NO RELATIONSHIP| SplitTable

        LendingTable --> BrokenReport["Broken User Experience:
        - Cannot view total combined debt (Rs. 1,450)
        - Inconsistent contact names
        - Updating phone numbers required multiple edits"]
        SplitTable --> BrokenReport
    end

To create a clean relational architecture, we needed to:

  1. Create a centralized persons directory table.
  2. Scan existing lending_records and extract unique contact names.
  3. Generate stable UUIDs for each unique person.
  4. Alter lending_records to reference person_id as a foreign key.
  5. Guarantee that this migration executes seamlessly on users' devices without crashing or locking the database.

Designing SQLite Migration 18 to 19

In an offline-first app, database migrations run on users' physical hardware during application startup. If a migration script fails, the app crashes on launch, effectively bricking the user's data.

SQLite has strict limitations: historically, it did not support adding foreign key constraints to existing tables via ALTER TABLE.

The canonical, safe SQLite migration pattern requires a structured 5-step table recreation:

flowchart TD
    subgraph MigrationSteps["The 5-Step Room Migration v19"]
        Step1["1. Create New persons Table:
        id TEXT PRIMARY KEY,
        name TEXT NOT NULL,
        normalized_name TEXT NOT NULL,
        created_at INTEGER"] --> Step2["2. Extract & Deduplicate Legacy Contacts:
        INSERT INTO persons (id, name, normalized_name, ...)
        SELECT lower(hex(randomblob(16))), person_name, ...
        FROM lending_records GROUP BY trim(lower(person_name))"]

        Step2 --> Step3["3. Create Temporary lending_records_new Table:
        Include person_id TEXT NOT NULL REFERENCES persons(id)"]

        Step3 --> Step4["4. Backfill person_id from Legacy Strings:
        Copy rows matching legacy person_name to newly created persons.id"]

        Step4 --> Step5["5. Table Swap:
        DROP TABLE lending_records;
        ALTER TABLE lending_records_new RENAME TO lending_records;
        Rebuild indexes."]
    end

Let's break down the critical decisions in this migration:

1. Case-Insensitive String Deduplication

During step 2, if a user had previously entered "Rahul" for one loan and "rahul " for another, a naive GROUP BY person_name would have created two duplicate rows in the new persons table.

By utilizing GROUP BY trim(lower(person_name)), the SQL script deduplicated case variances, consolidating multiple historical loans under a single unified person entity.

2. Client-Side UUID Generation in SQLite

Because SQLite doesn't have a native UUID() function, the migration script generated pseudo-random 16-byte hex strings using:
lower(hex(randomblob(16)))
This ensured that every newly created person received a cryptographically unique primary key directly inside SQLite, without requiring complex Android Java/Kotlin runtime loops.

3. Atomic Execution

The entire migration is wrapped in a single database transaction. If any step fails—such as a disk I/O error or constraint mismatch—the database rolls back to schema version 18 untouched.

The Result: Unified Multi-Subsystem Intelligence

Once Migration 19 landed:

  • The persons table became the single source of truth for contact identity across the entire application.
  • When the Split & Group Expenses Engine launched in v2.0, it immediately bound group members to the existing persons directory.
  • Users can now open a friend's profile and see a unified ledger:
    • Direct Loans Lent: Rs. 1,000.00
    • Split Expenses Owed: Rs. 450.00
    • Net Total Receivable: Rs. 1,450.00

Key Takeaway

Schema migrations in offline-first software are not just database maintenance; they are the foundation of product evolution.

By safely migrating free-form string columns into a unified, relational Persons directory using defensive SQLite table-swap patterns, FinLedger unlocked multi-subsystem intelligence while guaranteeing 100% data preservation for existing users.


FinLedger is a privacy-first, offline-first personal finance application for Android that models bank accounts, credit cards, and cash without double-counting.

Explore FinLedger on Google Play: https://play.google.com/store/apps/details?id=com.devanshufinance.app

Top comments (0)