Database Schema

Xylem uses PostgreSQL as its primary database, accessed through Prisma ORM. The schema is defined in xylem-backend/prisma/schema.prisma.

Entity Relationship Overview

┌──────────────┐     ┌──────────────────┐     ┌─────────────────────────┐
│ Organization │────▶│      User        │────▶│     RefreshToken        │
│              │     │                  │     └─────────────────────────┘
│  - name      │     │  - firstName     │
│  - domain    │     │  - email         │     ┌─────────────────────────┐
│  - website   │     │  - password      │────▶│   EmailVerification     │
└──────┬───────┘     │  - roleId ───────┼──┐  └─────────────────────────┘
       │             └────────┬─────────┘  │
       │                      │            │  ┌─────────────────────────┐
       │              ┌───────▼────────┐   └─▶│        Role             │
       │              │  ProjectShare  │      │  - name                 │
       │              │  - accessLevel │      │  - permissions[]        │
       │              └───────┬────────┘      └─────────────────────────┘
       │                      │
       ▼                      ▼
┌──────────────┐     ┌────────────────┐      ┌─────────────────────────┐
│   Project    │────▶│ ProjectVariant │─────▶│ ProjectVariantSolution  │
│              │     │                │      │  - solutionEngineId     │
│  - name      │     │  - name        │      │  - solutionData (JSON)  │
│  - tags[]    │     │  - productMix  │      │  - overallScore         │
│  - location  │     │  - metaData    │      └─────────────────────────┘
│  - coverImage│     │  - currentStage│
│  - surveyMapId     └────────────────┘
└──────┬───────┘
       │
       ▼
┌──────────────┐
│  SurveyMap   │
│  - dwgId     │
│  - dxfId     │
│  - pdfId     │
│  - geojsonId │
│  - metaData  │
└──────────────┘

Core Models

Project

The central entity. A project represents a real estate development site.

Field	Type	Description
id	UUID	Primary key
name	String	Project name
tags	String[]	Searchable tags
location	String?	Address or location description
coverImage	String?	URL path to cover image
surveyMapId	UUID?	Link to the uploaded survey map
organizationId	UUID?	Owning organization

ProjectVariant

Each project can have multiple design variants with different configurations.

Field	Type	Description
name	String	Variant name
variantTags	String[]	Tags for filtering
currentStage	Enum	DRAFT → IN_PROGRESS → COMPLETED
productMix	JSON	Array of unit mix entries (BHK types, counts, areas)
metaData	JSON?	Additional configuration data

ProjectVariantSolution

Solutions generated by the simulation engine, stored as JSON blobs.

Field	Type	Description
solutionEngineId	String	Unique ID from simulation engine
solutionData	JSON	Complete solution object
overallScore	Float?	Primary metric for sorting

SurveyMap

Tracks the processing pipeline of uploaded DWG survey files.

Field	Type	Description
originalDwgId	UUID	Reference to uploaded DWG file
dxfId	UUID?	Converted DXF file reference
pdfId	UUID?	Converted PDF file reference
geojsonId	UUID?	Converted GeoJSON file reference
metaData	JSON?	AI-extracted metadata (Gemini)

Auth & User Models

User

Field	Type	Description
firstName, lastName	String	User name
email	String (unique)	Login email
password	String	Bcrypt-hashed password
isEmailVerified	Boolean	Email verification status
isActive	Boolean	Account active flag
roleId	UUID	Assigned role (FK to Role)
organizationId	UUID?	Organization membership

Role & Permission

Roles are linked to permissions in a many-to-many relationship.

model Role {
  id          String       @id @default(uuid())
  name        String       @unique
  description String?
  permissions Permission[]
  users       User[]
}

model Permission {
  id    String @id @default(uuid())
  name  String @unique  // e.g., "manage:users"
  roles Role[]
}

ProjectShare

Enables sharing projects with specific users at different access levels.

Field	Type	Description
projectId	UUID	Shared project
userId	UUID	User receiving access
accessLevel	Enum	VIEWER or EDITOR
sharedById	UUID	User who initiated the share

Organization Models

OrganizationInvitation

Tracks email invitations to join an organization with a specific role.

OrganizationJoinRequest

Handles user-initiated requests to join an organization. Status: PENDING → APPROVED / REJECTED.

DomainBlacklist

Maintains a list of email domains that are blocked from registration.

Domain-Specific Models

RegulatoryRule

Stores visual node-graph-based regulatory rules (created in the admin panel with ReactFlow).

Field	Type	Description
id	String	Rule ID (e.g., RUL-XXXXXXXX)
name	String	Human-readable rule name
authority	String	Governing authority (BDA, BBMP, etc.)
parameters	JSON	{ inputs: [], outputs: [] }
graph	JSON	ReactFlow node/edge graph definition

RegulatoryVariable

Global variable registry for the regulatory engine — defines the inputs and outputs available to rules.

Field	Type	Description
key	String (PK)	Dot-notation key (e.g., site.area)
label	String	Display label
category	String	INPUT or OUTPUT
dataType	String	number, boolean, string
unit	String?	Unit of measurement

CatalogUnit

Pre-defined apartment unit templates used in floorplate generation.

Field	Type	Description
unitId	String (PK)	e.g., 2bhk_corner_96
bhkType	String	1BHK, 2BHK, 2.5BHK, 3BHK, 4BHK
areaSqm	Float	Unit area in square meters
entranceDirection	String	NORTH, SOUTH, EAST, WEST
isCorner	Boolean	Corner unit flag
geometry	JSON	CAD geometry entities

Prisma Commands

# Generate Prisma client after schema changes
npx prisma generate

# Create and apply a migration
npx prisma migrate dev --name description_of_change

# Deploy migrations in production
npx prisma migrate deploy

# Open Prisma Studio (GUI)
npx prisma studio

# Seed the database
npx prisma db seed