Tables & Enums

Core Tables

buyers

Buyer profiles and Lucia user store. All columns become Lucia user attributes.

Column	Type	Notes
id	uuid PK	Default gen_random_uuid()
title	text	Salutation (Dr., Prof.)
first_name	text
last_name	text
email	text UNIQUE	Login identifier
phone	text UNIQUE	Login identifier
interest_type	interest_type	NOT NULL
preferred_language	preferred_language	Default de
newsletter_opt_in	boolean	Default false
privacy_accepted_at	timestamptz	NOT NULL
biological_sex	biological_sex	Optional
project_id	uuid FK → projects	Added in migration 0009
organization_id	uuid FK → organizations	Denormalized in 0015
portal_id	uuid FK → portals	Latest portal in 0015
crm_contact_id	text	Optional CRM reference

units

Property units. id is a string (CMS identifier, e.g., top-1).

Column	Type	Notes
id	text PK	CMS/unit identifier
unit_code	text	Code unique within project
project_id	uuid FK → projects	Added in migration 0009
title	text	NOT NULL
status	unit_status	Default available
rooms	numeric(4,1)
area_sqm	numeric(8,2)
purchase_price_eur	integer	NOT NULL
agent_id	uuid FK → agents	Assigned agent
Document filenames	text	expose, floor_plan, energy_certificate

agents

Sales agent profiles, scoped to organizations.

Column	Type	Notes
id	uuid PK
name	text	NOT NULL
role	text
email	text
phone	text
whatsapp	text	WhatsApp number
avatar_url	text	Profile photo URL
organization_id	uuid FK → organizations	Added in migration 0009

sessions

Lucia session store for buyer sessions.

Column	Type	Notes
id	text PK	Opaque session ID (40+ chars)
user_id	uuid FK → buyers	NOT NULL, CASCADE delete
expires_at	timestamptz	NOT NULL
project_id	uuid FK → projects	Added in migration 0009

admin_sessions

Lucia session store for CRM (agent/developer) sessions.

Column	Type	Notes
id	text PK	Opaque session ID
user_id	uuid FK → users	NOT NULL, CASCADE delete
expires_at	timestamptz	NOT NULL
active_project_id	uuid FK → projects	Current active project

otp_codes

One-time password storage (hash, not plaintext).

Column	Type	Notes
id	uuid PK
identifier	text	Email or phone
code_hash	text	SHA-256 hash
channel	otp_channel	email or whatsapp
expires_at	timestamptz
attempts	integer	Default 0
used	boolean	Default false
project_id	uuid FK → projects	Added in migration 0009

Multi-Tenant Tables (spike/cf-runtime)

organizations, projects, portals, portal_domains

See Tenant Model for the complete hierarchy.

users

CRM identities (agents, developers, and buyers if they have CRM access).

Column	Type	Notes
id	uuid PK
email	text UNIQUE	Login identity
name	text
type	user_type	buyer, agent, or developer

audit_events

Immutable audit trail for all mutations.

Column	Type	Notes
id	uuid PK
organization_id	uuid FK → organizations	For filtering
project_id	uuid FK → projects	For filtering
action	text	e.g., unit.update, portal.publish
entity_type	text	Entity class
entity_id	text	Entity UUID
before	jsonb	Previous state (null for creates)
after	jsonb	New state (null for deletes)

jobs

Background job queue for the Worker Box.

Column	Type	Notes
id	uuid PK
type	job_type	openimmo.import, image.label, ecb.refresh
status	job_status	queued, running, done, failed
payload	jsonb	Job input
result	jsonb	Job output