Database Schema & Drizzle Setup
Relevant source files
The following files were used as context for generating this wiki page:
The database layer of LV.AI uses Neon Postgres and the Drizzle Object-Relational Mapper (ORM) to manage construction project data. This system structures information for Leistungsverzeichnisse (LVs), bidding processes, and project management within the D/A/CH region (Germany, Austria, Switzerland). The schema enforces data integrity for GAEB-compliant documents and construction-specific workflows.
The implementation relies on Drizzle ORM to define table structures in TypeScript, ensuring type safety across the application. The system supports multi-tenant access where users own projects, and each project maintains its own set of chat histories, documents, and offers.
Sources: lib/db/schema.ts:1-10, README.md:28-36, CLAUDE.md:15-20
Core Domain Models
The schema defines a hierarchy where the User owns Project entities. Each project serves as a container for construction-specific data and chat sessions. The Chat table maintains a 1:1 relationship with a project, allowing the AI to maintain context for specific construction tasks.
Entity Relationships
The following diagram illustrates the core relationships between users, projects, and communication logs.
The diagram shows the cascading relationship from high-level user ownership down to specific line items in a bill of quantities.
Sources: lib/db/schema.ts:50-100, lib/db/migrations/0001_noisy_lockheed.sql:63-80
Key Data Fields
| Table | Field | Type | Description |
|---|---|---|---|
| Project | land | landEnum | Specifies D/A/CH jurisdiction (DE, AT, CH). |
| Project | status | projektStatusEnum | Tracks phase (e.g., ausschreibung, vergabe). |
| Chat | visibility | varchar | Controls access (public vs. private). |
| Message_v2 | parts | json | Stores AI and user message segments. |
Sources: lib/db/schema.ts:50-95, lib/db/migrations/meta/0002_snapshot.json:448-470
AVA and GAEB Specification Tables
LV.AI handles complex construction data through specialized tables for Bills of Quantities (LVs) and bidding. These tables utilize specific decimal precisions to meet financial auditing requirements.
Bill of Quantities (LV) Structure
The LvDocument table stores the header information for GAEB files. Individual line items reside in the LvPosition table, which uses precise decimal scales for quantities (scale 3) and prices (scale 2).
This flow represents the hierarchical structure of a Leistungsverzeichnis.
Sources: lib/db/schema.ts:168-205, lib/db/migrations/0001_noisy_lockheed.sql:128-155
Bidding and Offers
Bidders submit offers through the Offer table. Each offer contains multiple OfferPosition records, which represent the bidder's specific pricing for each OZ (Ordnungszahl) in the LV.
| Field | Precision | Description |
|---|---|---|
menge | 12, 3 | Support for three decimal places in quantities. |
einheitspreis | 12, 2 | Standard currency precision for unit prices. |
gesamtpreis | 14, 2 | Higher precision for total sums of items. |
Sources: lib/db/schema.ts:207-250, lib/db/migrations/meta/0002_snapshot.json:570-620
Enums and Legal Frameworks
The database utilizes PostgreSQL enums to enforce D/A/CH compliance. These enums ensure that project phases and document formats adhere to industry standards like VOB, ÖNORM, and SIA.
- landEnum:
DE,AT,CH. - projektStatusEnum:
entwurf,ausschreibung,vergabe,ausfuehrung,abrechnung,abgeschlossen. - gaebFormatEnum: Supports X81 through X89.
- bieterStatusEnum:
offen,eingereicht,gueltig,unvollstaendig,ausgeschlossen,vergeben,abgelehnt.
Sources: lib/db/schema.ts:32-48, lib/db/schema.test.ts:40-80
Management and Automation
Additional tables support project administrative tasks, including invoicing, reminders, and document storage.
- Invoices: Stores
eRechnungformats (XRechnung, ZUGFeRD) and payment status. - Reminders: Tracks deadlines (
frist), payments (zahlung), and warranty periods (gewaehrleistung). - Files: Differentiates between
FileGlobal(user-wide) andFileProject(specific to a construction site). - Drawings: Stores links to SVG and PDF architectural plans.
Sources: lib/db/schema.ts:252-320, lib/db/migrations/0001_noisy_lockheed.sql:85-125
Drizzle Workflow and Migrations
The project manages database changes through drizzle-kit. The package.json file defines the command suite for maintaining the schema.
The diagram illustrates the synchronization between TypeScript definitions and the physical database.
Sources: package.json:7-15
Migration Scripts
pnpm db:generate: Creates new migration files from schema changes.pnpm db:migrate: Executes pending SQL migrations vialib/db/migrate.ts.pnpm db:push: Pushes schema changes directly to the database without generating migration files (used in development).pnpm db:studio: Launches a local web interface to browse and edit database records.
Sources: package.json:7-15, README.md:65-75
Summary
The LV.AI database architecture provides a robust foundation for construction project management. It strictly enforces GAEB data structures and D/A/CH legal requirements through Postgres enums and highly precise decimal fields. The use of Drizzle ORM facilitates a type-safe workflow, ensuring that the AI agent and the user interface interact with a consistent and validated data model throughout the construction lifecycle.