# Revit-MCP Database Schema: SQLite Storage for Projects and Rooms

> Discover the Revit-MCP database schema. Learn how it uses SQLite to efficiently store Revit project and room data with normalized tables and JSON metadata.

- Repository: [MCP servers for Revit/revit-mcp](https://github.com/mcp-servers-for-revit/revit-mcp)
- Tags: api-reference
- Published: 2026-02-16

---

**Revit-MCP uses a local SQLite database (`revit-data.db`) with two normalized tables—`projects` and `rooms`—to store Revit project metadata and spatial data, featuring foreign key relationships and JSON metadata columns for flexible extensibility.**

The revit-mcp server persists all building information in a structured SQLite database schema defined in [`src/database/db.ts`](https://github.com/mcp-servers-for-revit/revit-mcp/blob/main/src/database/db.ts). This lightweight, file-based approach eliminates external database dependencies while providing robust relational integrity for architectural project management.

## Database Architecture Overview

The database schema is initialized automatically when the server starts via the `initializeDatabase()` function in [`src/database/db.ts`](https://github.com/mcp-servers-for-revit/revit-mcp/blob/main/src/database/db.ts). The implementation enables strict foreign key enforcement to maintain referential integrity between parent projects and their child room records.

### SQLite Configuration

The database operates as a single file named `revit-data.db` in the application root. Foreign key support is explicitly enabled:

```sql
PRAGMA foreign_keys = ON;

```

This pragma ensures that deleting a project automatically cascades to remove associated rooms, preventing orphaned records.

## Projects Table Schema

The `projects` table stores metadata for each Revit file processed by the server.

| Column | Type | Description |
|--------|------|-------------|
| `id` | INTEGER | Primary key with AUTOINCREMENT |
| `project_name` | TEXT | Human-readable project identifier |
| `project_path` | TEXT | File system path to the .rvt file |
| `project_number` | TEXT | Client or internal project number |
| `project_address` | TEXT | Physical building location |
| `client_name` | TEXT | Project owner or client |
| `project_status` | TEXT | Current phase (e.g., "Active", "Completed") |
| `author` | TEXT | User who created the record |
| `timestamp` | TEXT | ISO 8601 creation time |
| `last_updated` | TEXT | ISO 8601 modification time |
| `metadata` | TEXT | JSON-encoded flexible attributes |

The `metadata` column accepts arbitrary JSON objects, allowing extensions to store custom project attributes without schema migrations.

## Rooms Table Schema

The `rooms` table contains spatial data for individual architectural spaces, linked to projects via foreign key.

| Column | Type | Description |
|--------|------|-------------|
| `id` | INTEGER | Primary key with AUTOINCREMENT |
| `project_id` | INTEGER | Foreign key referencing `projects.id` |
| `room_id` | TEXT | Revit's unique room identifier |
| `room_name` | TEXT | Descriptive room name |
| `room_number` | TEXT | Room number from Revit |
| `department` | TEXT | Functional department assignment |
| `level` | TEXT | Building level or story |
| `area` | REAL | Calculated area in square units |
| `perimeter` | REAL | Perimeter measurement |
| `occupancy` | TEXT | Occupancy count or classification |
| `comments` | TEXT | User annotations |
| `timestamp` | TEXT | ISO 8601 creation time |
| `metadata` | TEXT | JSON-encoded flexible attributes |

A unique constraint on the combination of `project_id` and `room_id` prevents duplicate room entries within the same project.

## Database Constraints and Indexes

The schema implements integrity constraints and performance optimizations defined in [`src/database/db.ts`](https://github.com/mcp-servers-for-revit/revit-mcp/blob/main/src/database/db.ts).

### Foreign Key Enforcement

Foreign key constraints ensure that every room references a valid project. When a project is deleted, SQLite automatically removes associated rooms via cascade deletion.

### Performance Indexes

The following indexes optimize common query patterns:

- `idx_projects_name` on `projects.project_name`
- `idx_projects_timestamp` on `projects.timestamp`
- `idx_rooms_project_id` on `rooms.project_id`
- `idx_rooms_room_number` on `rooms.room_number`

These indexes support fast lookups when filtering by project name, retrieving recent projects, or fetching rooms by project association.

## Service Layer CRUD Operations

The [`src/database/service.ts`](https://github.com/mcp-servers-for-revit/revit-mcp/blob/main/src/database/service.ts) file provides a TypeScript abstraction over the raw SQL, handling JSON serialization and timestamp management automatically.

### Storing Project Data

The `storeProject` function upserts project records and returns the SQLite row ID:

```typescript
import { storeProject } from "./src/database/service.js";

const projectId = storeProject({
  project_name: "Corporate Headquarters",
  project_path: "C:/Revit/Projects/HQ.rvt",
  project_number: "2024-001",
  project_address: "100 Business Plaza, New York, NY",
  client_name: "Acme Corporation",
  project_status: "Active",
  author: "Jane Smith",
  metadata: { phase: "Design Development", budget: 5000000 }
});

console.log(`Project stored with ID: ${projectId}`);

```

This function automatically sets the `timestamp` and `last_updated` fields. If a project with the same name exists, it updates the existing record rather than creating a duplicate.

### Storing Room Data

The `storeRoom` function links rooms to projects via foreign key:

```typescript
import { storeRoom } from "./src/database/service.js";

const roomId = storeRoom(projectId, {
  room_id: "R-101",
  room_name: "Executive Conference Room",
  room_number: "101",
  department: "Administration",
  level: "2",
  area: 45.5,
  perimeter: 28.0,
  occupancy: "12",
  comments: "Video conferencing equipped",
  metadata: { fire_rating: "2h", acoustic_rating: "STC 50" }
});

console.log(`Room stored with ID: ${roomId}`);

```

The function enforces the unique constraint on `(project_id, room_id)` and handles JSON encoding of the metadata object.

### Querying Data

Retrieve projects and their associated rooms using service layer functions:

```typescript
import { getAllProjects, getRoomsByProjectId } from "./src/database/service.js";

// Fetch all projects
const projects = getAllProjects();
console.log("Projects:", projects);

// Get rooms for a specific project
const rooms = getRoomsByProjectId(projectId);
console.log("Rooms:", rooms);

```

The service layer automatically parses JSON metadata columns and formats timestamps as ISO 8601 strings.

## MCP Tool Integration

The database schema powers the MCP tools defined in [`src/tools/store_project_data.ts`](https://github.com/mcp-servers-for-revit/revit-mcp/blob/main/src/tools/store_project_data.ts) and [`src/tools/store_room_data.ts`](https://github.com/mcp-servers-for-revit/revit-mcp/blob/main/src/tools/store_room_data.ts).

When the MCP server receives a `store_project_data` request, it invokes `storeProject` from the service layer. Similarly, `store_room_data` calls `storeRoom`, passing the project ID and room parameters. These tools return the new record IDs and full project or room records to confirm successful persistence.

This architecture ensures that all database interactions flow through the validated schema and service abstractions, maintaining data integrity across the MCP interface.

## Summary

- **revit-mcp** uses a local SQLite database (`revit-data.db`) with foreign key enforcement enabled via `PRAGMA foreign_keys = ON`.
- The database schema consists of two normalized tables: `projects` for Revit file metadata and `rooms` for spatial data.
- Both tables include JSON `metadata` columns for extensible attributes without requiring schema migrations.
- The `rooms` table references `projects` via a foreign key with cascade delete behavior to prevent orphaned records.
- Indexes on `project_name`, `timestamp`, `project_id`, and `room_number` optimize query performance.
- The service layer in [`src/database/service.ts`](https://github.com/mcp-servers-for-revit/revit-mcp/blob/main/src/database/service.ts) abstracts CRUD operations, handling JSON serialization and timestamp management automatically.

## Frequently Asked Questions

### What type of database does revit-mcp use?

revit-mcp uses **SQLite**, a serverless, file-based SQL database engine. All data is stored in a single file named `revit-data.db` in the application directory, making deployment simple and eliminating the need for separate database server configuration while providing full ACID compliance.

### Can I extend the database schema to store additional project attributes?

Yes. The schema includes a flexible `metadata` column (TEXT type) in both the `projects` and `rooms` tables that accepts JSON-encoded objects. You can store arbitrary key-value pairs in this field without modifying the database schema, and the service layer in [`src/database/service.ts`](https://github.com/mcp-servers-for-revit/revit-mcp/blob/main/src/database/service.ts) automatically handles JSON serialization and parsing.

### How does revit-mcp handle duplicate room entries?

The database enforces a unique constraint on the combination of `project_id` and `room_id` in the `rooms` table. When the `storeRoom` function encounters an existing room with the same `room_id` for a given project, it performs an upsert operation—updating the existing record rather than creating a duplicate—ensuring data consistency.

### What happens to room data when a project is deleted?

Due to SQLite foreign key constraints with `ON DELETE CASCADE` behavior enabled via `PRAGMA foreign_keys = ON`, deleting a project from the `projects` table automatically deletes all associated records in the `rooms` table. This cascade deletion ensures referential integrity and prevents orphaned room records from remaining in the database.