How revit-mcp Uses SQLite Transactions for Data Integrity in Batch Room Imports

The revit-mcp database service guarantees ACID compliance during bulk operations by wrapping batch inserts in SQLite transactions via the better-sqlite3 db.transaction() API, ensuring that all room records are committed atomically or fully rolled back on error.

The revit-mcp project provides a Model Context Protocol (MCP) server for Autodesk Revit, managing project and room data in a local SQLite database. To prevent data corruption during high-volume imports, the service implements SQLite transactions for data integrity, leveraging the better-sqlite3 driver's transaction API to enforce atomic commits and automatic rollbacks.

Atomic Batch Inserts with better-sqlite3 Transactions

When importing room data from Revit models, the system must handle hundreds of records without creating orphaned entries or partial datasets. The solution relies on better-sqlite3's transactional capabilities to treat the entire batch as a single atomic unit.

Implementing storeRoomsBatch with db.transaction()

In src/database/service.ts, the storeRoomsBatch function defines a transaction wrapper that executes multiple INSERT or UPDATE statements as one logical operation:

// src/database/service.ts – bulk-store rooms inside a transaction
export function storeRoomsBatch(projectId: number, rooms: RoomData[]): number {
  // `db.transaction` creates a function that runs all its statements
  // inside a single SQLite transaction.
  const insertMany = db.transaction((roomsData: RoomData[]) => {
    let count = 0;
    for (const room of roomsData) {
      // Each call uses the normal `storeRoom` logic (INSERT or UPDATE)
      storeRoom(projectId, room);
      count++;
    }
    return count; // returned after the transaction commits
  });

  // Execute the transaction; if any `storeRoom` fails, everything rolls back.
  return insertMany(rooms);
}

The db.transaction() method returns a new function (insertMany) that, when called, wraps all internal database operations in BEGIN and COMMIT statements. If any statement fails, better-sqlite3 automatically issues a ROLLBACK, leaving the database in its pre-transaction state.

Automatic Rollback on Constraint Violations

The transaction mechanism provides automatic rollback on failure without requiring explicit error handling in the application code. If a storeRoom call encounters a constraint violation—such as a duplicate unique key or a foreign key mismatch—the better-sqlite3 driver catches the error, rolls back the entire transaction, and re-throws the exception. This prevents the "half-written" state where some rooms from a batch appear in the database while others are missing.

Enforcing Referential Integrity via Foreign Keys

Beyond atomic transactions, the database layer maintains referential integrity between projects and rooms using SQLite's foreign key constraints.

Enabling PRAGMA foreign_keys in db.ts

In src/database/db.ts, the connection initialization explicitly enables foreign key enforcement, which is disabled by default in SQLite for backward compatibility:

// src/database/db.ts – enable foreign-key checks (required for cascade deletes)
import Database from 'better-sqlite3';
...
export const db = new Database(DB_PATH);

// Ensure foreign-key constraints are enforced for the whole connection.
db.pragma('foreign_keys = ON');

Without this pragma, foreign key constraints would be parsed but not enforced, allowing orphaned room records to exist without associated projects.

Cascade Deletes for Project-Room Relationships

The schema defines the rooms table with a foreign key reference to projects(id) that includes ON DELETE CASCADE. When a project is deleted within a transaction, SQLite automatically removes all associated room records as part of the same atomic operation. This cascade behavior works in conjunction with the explicit transactions described earlier, ensuring that deleting a project and its rooms succeeds entirely or fails entirely without manual cleanup code.

Summary

• Atomic batch operations: The storeRoomsBatch function in src/database/service.ts uses db.transaction() to wrap multiple room inserts into a single atomic unit.
• Automatic rollback: better-sqlite3 automatically rolls back transactions when constraint violations or errors occur, preventing partial data writes.
• Foreign key enforcement: src/database/db.ts enables PRAGMA foreign_keys = ON to ensure referential integrity between projects and rooms.
• Cascade deletes: The schema uses ON DELETE CASCADE to automatically clean up related room records when projects are deleted, all within the same transactional context.

Frequently Asked Questions

What happens if a single room fails during a batch import in revit-mcp?

If any individual room insert fails inside storeRoomsBatch—for example, due to a unique constraint violation or invalid foreign key—the better-sqlite3 driver automatically rolls back the entire transaction. This means none of the rooms from that batch are persisted to the database, maintaining consistency and preventing partial imports.

Does revit-mcp support WAL mode for SQLite concurrency?

While the provided source analysis focuses on transaction integrity rather than concurrency modes, the better-sqlite3 driver supports Write-Ahead Logging (WAL) mode. To enable WAL mode for improved read concurrency, you would call db.pragma('journal_mode = WAL') in src/database/db.ts alongside the existing foreign_keys = ON pragma.

How does revit-mcp handle foreign key violations during transactions?

When PRAGMA foreign_keys = ON is enabled in src/database/db.ts, SQLite enforces referential integrity at the database level. If a transaction attempts to insert a room with a non-existent projectId, SQLite throws a foreign key constraint error. better-sqlite3 catches this error and rolls back the entire transaction, ensuring that referential integrity violations never result in orphaned records.

Can I adjust the transaction batch size in revit-mcp's storeRoomsBatch?

The storeRoomsBatch function accepts an array of RoomData objects and processes the entire array within a single transaction. While the current implementation does not internally chunk large arrays into smaller sub-transactions, you can control the batch size by passing smaller slices of your data array to storeRoomsBatch from your application code. Each call creates a separate transaction boundary, allowing you to balance between transaction atomicity and memory usage.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}