ℹThis page lives in the Developers section and is also referenced from Deployment. If you arrived from there, your sidebar has switched to Developers.

Data Layer

PostgreSQL database managed by Prisma ORM, object storage via MinIO, and real-time sync with Martin tile server.

Database Schema

All structured data lives in PostgreSQL. Prisma ORM provides type-safe migrations and query generation.

Core Models

The schema is built around an Organization as the top-level tenant. Every other record is scoped to one organization, and queries are filtered by the caller's organization on every request.

Model	Purpose
Organization	Tenant container. Owns users, sites, buildings, files, sensors, comments, and roles.
User	A member of one organization, assigned a single role.
Role	Named role (Admin / User / Viewer) with a list of (action, subject) permissions.
Site	A geographic grouping of buildings inside an organization.
Building	The primary asset entity. Belongs to a site and an organization.
Infrastructure	Non-building assets (utility lines, roads, etc.).
File	Metadata for an uploaded binary; the binary itself lives in MinIO.
Sensor / SensorType	An IoT sensor and its category/unit definition.
Comment	Threaded annotation attached to other entities.

Migrations

Prisma handles migrations declaratively. Workflow:

# 1. Update schema.prisma
# 2. Create migration (generates SQL)
npx prisma migrate dev --name <migration_name>

# 3. In CI/CD before deploy
npx prisma migrate deploy

Migrations stored in prisma/migrations/ as timestamped .sql files for version control and auditing.

Unstructured Files (MinIO)

Binary assets — IFC models, glTF, DXF, LAS/LAZ point cloud tiles, PDFs, images, video, CSV — are stored in MinIO (S3-compatible). Each File record in PostgreSQL holds the metadata (name, type, size, owner, parent building); the binary itself lives in MinIO and is referenced by an object key.

Spatial Database (PostGIS + Martin)

Geospatial data lives in PostGIS, the PostgreSQL spatial extension. Martin is a tile server that reads building geometries and attributes from PostGIS and serves them as Mapbox Vector Tiles directly to MapLibre — no GeoJSON round-trip through the API. Treat PostGIS + Martin as one spatial store with a tile-serving front door.

Setup

# martin.toml

[tiles.buildings]
query = """
SELECT 
  id,
  ST_AsGeom(geom) as geometry,
  address,
  energyUse
FROM buildings
WHERE buildingLatitude IS NOT NULL
"""

Client Usage

const buildingTiles = 'http://localhost:3001/tiles/buildings/{z}/{x}/{y}.pbf';

map.addSource('buildings', {
  type: 'vector',
  tiles: [buildingTiles],
  minzoom: 10,
});

map.addLayer({
  id: 'building-fill',
  type: 'fill',
  source: 'buildings',
  paint: { 'fill-color': '#fff', 'fill-opacity': 0.5 },
});

Data Consistency

PostGIS for Geospatial Queries

// Find all buildings within 500m of a point
const nearby = await prisma.$queryRaw`
  SELECT * FROM "Building"
  WHERE ST_DWithin(
    ST_SetSRID(ST_Point(buildingLongitude, buildingLatitude), 4326),
    ST_SetSRID(ST_Point($1, $2), 4326),
    500
  )
`;

Indexing Strategy

Index	Reason
buildingLatitude, buildingLongitude	Map viewport queries
organizationId	Multi-tenant filtering
createdAt	Timeline queries
s3Key	File lookups

Transaction Safety

Bulk operations wrap in transactions:

await prisma.$transaction(async (tx) => {
  // All-or-nothing
  for (const buildingData of csvRows) {
    await tx.building.create({ data: buildingData });
  }
});

Backups & Recovery

• Automated: Daily database backups via Fullhost
• Retention: Rolling window of the 5 most recent backups (oldest is up to 5 days old)

Performance Tuning

Query Monitoring

const result = await prisma.$queryRaw`
  SELECT ... FROM buildings
  -- Debug: inspect query plan
  EXPLAIN ANALYZE
`;

Connection Pooling

The application connects to PostgreSQL using a standard DATABASE_URL (host, port, database, schema):

DATABASE_URL="postgresql://<user>:<pass>@<host>:5432/cdt?schema=public"

Caching

Frequently accessed data (org config, sensor types) cached in Memcached with 1-hour TTL.