05 — Modelo de datos · COMAL

Diagrama ER simplificado

User 1──* Membership
User 1──* Booking
User *──* Kitchen (via KitchenStaff for operators)
Kitchen 1──* Station
Kitchen 1──* KitchenEquipment
Kitchen 1──* KitchenCertification
Station 1──* SlotLock / AvailabilityRule
Booking *──1 Station
Booking 1──1 Payment
Booking 1──0..1 CheckIn
Booking 1──0..1 Review
Booking 1──0..* BookingEvent (audit)

Entidades principales

User

Campo	Tipo	Notas
id	uuid PK
email	citext unique
password_hash	text null	null si solo OAuth
name	text
phone	text null	E.164
role_default	enum	cook, operator, admin
locale	text	es-ES
created_at / updated_at	timestamptz
deleted_at	timestamptz null	soft delete

Kitchen

Campo	Tipo	Notas
id	uuid PK
slug	text unique
name	text
description	text
address_line / city / postal / country	text
lat / lng	numeric	índice geo
timezone	text	Europe/Madrid
base_price_cents	int	por hora
min_hours	int	default 2
cancellation_policy	jsonb
status	enum	draft, published, suspended
owner_user_id	uuid FK

Station

Campo	Tipo
id	uuid
kitchen_id	uuid FK
name	text
capacity_notes	text
is_active	bool

Equipment (catálogo) + KitchenEquipment (M2M)

code, label_es, icon_key

Certification

type, issuer, valid_until, document_url, verification_status

Booking

Campo	Tipo	Notas
id	uuid
public_code	text	COM-XXXX human
cook_user_id	uuid
station_id	uuid
starts_at / ends_at	timestamptz	exclusion constraint
status	enum	pending_payment, confirmed, checked_in, completed, cancelled, no_show
price_breakdown	jsonb
total_cents	int
currency	char(3)	EUR
hold_expires_at	timestamptz null
cancelled_at	timestamptz null

Constraint: EXCLUDE USING gist (station_id WITH =, tstzrange(starts_at, ends_at) WITH &&) WHERE (status IN ('confirmed','checked_in','completed','pending_payment'))

Payment

provider, provider_ref, amount_cents, status, raw_events jsonb

CheckIn

booking_id, method (qr|pin|manual), pin_hash, checked_in_at, checked_in_by

Membership

user_id, plan (base|pro), stripe_subscription_id, status, current_period_end

Review

booking_id unique, rating 1–5, body, published

Audit / BookingEvent

booking_id, actor_id, type, payload, created_at

Índices recomendados

bookings (cook_user_id, starts_at desc)
bookings gist exclusion (arriba)
kitchens (city), kitchens gist geo
kitchens (status) where published

Privacidad y retención

Dato	Clasificación	Retención
PII usuario	Personal	Hasta borrado + 30 días backup
Pagos	Sensible financiero	Según obligación fiscal (ES ~5–6 años refs)
Logs acceso	Técnico	12 meses
Certificados docs	Partner	Mientras kitchen activa + 1 año

Cifrado: TLS en tránsito; campo phone opcional encrypted at rest; documentos en bucket privado firmado.

Multitenancy

Lógico por kitchen_id para operadores
Admin COMAL cross-tenant
No shared DB por tenant en v1 (single DB, RLS por rol)

Eventos de dominio

UserRegistered KitchenPublished BookingHoldCreated BookingConfirmed PaymentCaptured BookingCancelled CheckInCompleted ReviewSubmitted MembershipStarted