Skip to content
Codex
Documentation GitHub
Database Issues

SQL Migration Column Evolution — Silent Data Loss on INSERT

SQL Migration Column Evolution — Silent Data Loss on INSERT

Problem

V010 migration added an area column to the blocks table to support layout grid cell assignments (INK-235). However, the insert_blocks() function in the SqlitePageRepository was not updated to include area in its INSERT statement. Because SQLite silently defaults missing columns to NULL, every block insertion quietly discarded the area value — a data loss bug with no error signal.

Symptoms:

  • Blocks are inserted and read back successfully, but the area field is always NULL
  • Layout assignments appear to work in the UI (set in memory) but are lost after page reload
  • No runtime errors, no warnings, no failed queries — completely silent

Investigation

Steps Tried

  1. Layout area assignments worked in memory and passed through the Tauri command layer correctly
  2. After save and reload, all area assignments reverted to NULL
  3. Inspected the SQLite database directly — confirmed area column existed (migration ran) but all values were NULL
  4. Traced the write path from assign_block_area use case through update_page to insert_blocks()
  5. Found the INSERT statement was missing the area column entirely

Root Cause

Schema migrations and repository SQL statements evolve independently. When a migration adds a column to a table, there is no compile-time enforcement that all repository write paths (INSERT, UPDATE, UPSERT) are updated to include the new column. SQLite’s behavior compounds this:

  • SQLite silently defaults any missing column to its DEFAULT value (typically NULL)
  • The INSERT succeeds with no error
  • The application reads back the row and gets NULL for the missing field
  • If the domain struct defaults to None or an empty value, the bug is invisible at runtime

This creates a class of bug that is:

  1. Silent — no errors at any layer
  2. Intermittent in appearance — data seems to persist until the process restarts
  3. Hard to detect — requires a roundtrip test that writes and re-reads the specific field

The Gap

Migration V010       Repository Code           Domain Entity
─────────────       ───────────────           ─────────────
ALTER TABLE blocks   INSERT INTO blocks        struct Block {
ADD COLUMN area      (id, page_id, ...         area: Option<String>
TEXT;                 content_type_metadata)    }
                     VALUES (...)
                     // area NOT listed ──────── area set correctly
                                                 but never written

The Rust compiler ensures the domain entity has the field, and the migration ensures the column exists, but nothing ensures the SQL string in the repository matches both.

Solution

Update the INSERT statement to include the new column:

// Before (data loss — area silently dropped)
"INSERT INTO blocks (id, page_id, slot_id, content, content_loro, content_loro_version, content_type, content_type_metadata)
 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8)"


// After (correct — area preserved)
"INSERT INTO blocks (id, page_id, slot_id, content, content_loro, content_loro_version, content_type, content_type_metadata, area)
 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9)"
// with params![..., block.area]

Verification

Add a roundtrip test that exercises the new column through the full write/read path:

#[test]
fn test_insert_block_with_area_roundtrips() {
    // Create a block with area = Some("main")
    // Insert via repository
    // Read back via repository
    // Assert area == Some("main")
}

Prevention

Mandatory Audit After Column Addition

After adding any column via migration, perform these five checks before considering the work complete:

  1. Search all INSERT statements for the affected table

    Terminal window
    grep -rn "INSERT INTO blocks" crates/infrastructure/

  2. Search all UPDATE statements for the affected table

    Terminal window
    grep -rn "UPDATE blocks" crates/infrastructure/

  3. Search the domain entity for the corresponding field

    Terminal window
    grep -n "area" crates/domain/src/block.rs

  4. Verify the field appears in all write paths — every INSERT, UPDATE, and UPSERT that touches the table must include the new column

  5. Add a roundtrip test that writes the new field and reads it back, asserting the value survives the trip

Migration Annotation Convention

When writing a migration that adds a column, add a comment to the migration file noting which repository methods need updating:

-- Migration V010: Add layout support to pages and blocks
--
-- REPOSITORY AUDIT REQUIRED:
--   - pages: insert_page(), update_page() in page_repository.rs
--   - blocks: insert_blocks(), update_block() in page_repository.rs
--
ALTER TABLE pages ADD COLUMN layout TEXT;
ALTER TABLE blocks ADD COLUMN area TEXT;

Warning Signs

  • Any migration that adds a column to a table with existing repository write paths
  • INSERT statements that use positional parameters (?1, ?2, ...) — easy to miss a new column
  • Domain structs with Option<T> fields that default to None — NULL reads back as a valid value, hiding the bug
  • No roundtrip test for a newly added field

Why the Compiler Cannot Help

Rust’s type system enforces that block.area exists on the struct, but the SQL statement is a raw string. The compiler has no way to verify that the string mentions all fields of the struct. This is a fundamental limitation of raw SQL in any language. Solutions like compile-time SQL checking (e.g., sqlx::query!) can catch some of these issues, but they require a different infrastructure setup and are not used in this codebase.

References

  • Discovery: INK-235 deep review (layout definition and rendering)
  • File: crates/infrastructure/sqlite/src/workspace/page/write.rs (insert_blocks function)
  • Migration: crates/infrastructure/sqlite/migrations/workspace/V010__add_layout_to_pages.sql

Was this page helpful?