Skip to content

Schema Overview

Database Engine

Current (main): PostgreSQL 16 running in Docker on the VPS
Target (spike/cf-runtime): Neon (serverless PostgreSQL)

ORM: Drizzle

The database schema is defined in src/db/schema.ts using Drizzle ORM’s schema definition DSL. Migrations are generated and managed with drizzle-kit.

Schema Map

The database contains these table groups:

Buyer Portal (MVP)

Table	Purpose
`buyers`	Buyer user profiles (Lucia user table)
`sessions`	Lucia session store (FK → buyers)
`otp_codes`	One-time password hashes
`favorites`	Saved units per buyer
`consents`	GDPR consent audit trail
`document_downloads`	Tracked document downloads

Admin / Content (Phase 4)

Table	Purpose
`units`	Property units
`unit_images`	Unit image gallery
`agents`	Sales agent profiles
`pages`	CMS content pages
`portal_settings`	Portal branding and config (legacy, being replaced by `portals`)

Multi-Tenant (Phase 4, `spike/cf-runtime`)

Table	Purpose
`organizations`	Real estate developer entities
`projects`	Real estate projects
`portals`	Portal branding/config (replaces `portal_settings`)
`portal_domains`	Custom domains per portal
`users`	Admin identities (agents, developers)
`memberships`	Organization membership and roles
`agent_project_assignments`	Agent-to-project assignments
`admin_sessions`	Admin Lucia session store
`admin_login_tokens`	Magic-link tokens
`audit_events`	Immutable audit log
`jobs`	Background job queue

Enum Types

Enum	Values
`interest_type`	`personal_use`, `investment`, `general_interest`, `other`
`preferred_language`	`de`, `en`
`consent_purpose`	`data_processing`, `newsletter`, `analytics`, `document_downloads`
`document_type`	`floor_plan`, `expose`, `energy_certificate`, `brochure`, `other`
`otp_channel`	`email`, `whatsapp`
`biological_sex`	`female`, `male`, `diverse`, `intersex`, `prefer_not_to_say`
`unit_status`	`available`, `reserved`, `sold`
`unit_image_label`	`floorplan`, `floorplan_3d`, `exterior`, `interior`, `site_plan`, `other`
`project_status`	`active`, `archived`
`user_type`	`buyer`, `agent`, `developer`
`membership_role`	`developer`, `agent`
`audit_actor_role`	`operator`, `developer`, `agent`, `buyer`
`job_type`	`openimmo.import`, `image.label`, `ecb.refresh`
`job_status`	`queued`, `running`, `done`, `failed`
`portal_domain_status`	`pending`, `active`, `failed`

Key Relationships

organizations 1──N projects
projects      1──N portals
portals       1──N portal_domains
projects      1──N units
projects      1──N buyers
projects      1──N pages
organizations 1──N agents
users         N──M organizations (via memberships)
users         N──M projects (via agent_project_assignments)
buyers        1──N favorites
buyers        1──N consents
buyers        1──N document_downloads