# 05 — Database Entity Map

PostgreSQL via Prisma. All ids are CUID. All money is integer minor units (`fils` for AED). All timestamps are UTC. All translatable text lives in `ContentTranslation`.

## Identity & access

- **User** (id, email unique, phoneE164, fullName, status, locale, createdAt)
- **UserCredential** (userId, type [PASSWORD/OAUTH], provider, providerSubject, passwordHash, lastUsedAt)
- **Session** (id, userId, refreshTokenHash, userAgent, ip, expiresAt)
- **OtpCode** (id, userId?, channel, hash, purpose, expiresAt, consumedAt)
- **Role** (id, code unique, name, isSystem)
- **Permission** (id, code unique, description)
- **RolePermission** (roleId, permissionId) PK composite
- **UserRole** (userId, roleId, businessId nullable) — `businessId` set means role is scoped to that business
- **Business** (id, slug, type, legalName, countryId, status, contactEmail, contactPhone)
- **UserMembership** (userId, businessId, role[code], invitedBy, joinedAt)
- **Address** (id, ownerType, ownerId, line1, line2, cityId, postalCode, geoLat, geoLng)

## Geography

- **Country** (id, iso2 unique, name, currencyCode, defaultLocale, isActive)
- **Region** (id, countryId, code, name)
- **City** (id, regionId, slug, name, geoLat, geoLng, heroImageId)
- **Currency** (code, symbol, fractionDigits)
- **Locale** (code unique, name, isRtl)

## Catalog

- **Destination** (id, cityId, slug unique, type [ATTRACTION/AREA/COLLECTION], coverMediaId, isFeatured)
- **Category** (id, slug, name, parentId, kind [STAY/ACTIVITY/FOOD/PHARMACY/GROCERY/SIM])
- **Listing** (id, businessId, kind, slug, title, summary, addressId, status [DRAFT/PUBLISHED/SUSPENDED], priceFromMinor, currencyCode, ratingAverage, ratingCount, isFeatured)
- **ListingMedia** (listingId, mediaId, position)
- **ListingCategory** (listingId, categoryId)
- **Room** (id, listingId, code, name, capacity, sqm, bedConfig, priceMinor)
- **MenuItem** (id, listingId, code, name, priceMinor)  *(restaurants/grocers/pharmacies)*
- **SimPlan** (id, businessId, code, name, dataMb, voiceMins, validityDays, priceMinor, isEsim)
- **TaxiTier** (id, code, name, baseFareMinor, perKmMinor, capacity)
- **EmergencyNumber** (id, countryId, code [POLICE/AMBULANCE/FIRE/UTILITY/COASTGUARD], number, label)

## Commerce

- **Booking** (id, userId, businessId, listingId, kind, status, checkInAt, checkOutAt, guestCount, currencyCode, subtotalMinor, taxMinor, totalMinor, notes)
- **BookingItem** (id, bookingId, refType, refId, quantity, priceMinor)
- **Order** (id, userId, businessId, kind [FOOD/PHARMACY/GROCERY/SIM], status, deliveryAddressId, totalMinor)
- **OrderItem** (id, orderId, refType, refId, quantity, priceMinor)
- **PaymentIntent** (id, userId, providerKey, providerRef, amountMinor, currencyCode, status, idempotencyKey unique)
- **Payment** (id, paymentIntentId, status, capturedAt, providerData JSON)
- **PaymentMethod** (id, userId, type, last4, expMonth, expYear, providerToken)
- **Refund** (id, paymentId, amountMinor, status, requestedBy, approvedBy, reason)
- **Payout** (id, businessId, periodStart, periodEnd, amountMinor, status)
- **Promotion** (id, code, kind, valueMinor, percentBps, validFrom, validTo, maxUses, usedCount, scope JSON)

## Engagement

- **SavedItem** (userId, refType [LISTING/DESTINATION/TRIP], refId, savedAt) PK composite
- **Review** (id, userId, listingId, rating 1-5, body, status [PENDING/PUBLISHED/REJECTED])
- **Trip** (id, userId, name, status, citiesJSON, startDate, endDate, paceCode, budgetTier, partyType, interestsJSON, totalEstimateMinor, currencyCode)
- **TripStep** (id, tripId, dayIndex, position, kind [STAY/DINE/ACTIVITY/TRANSPORT/NOTE], refType, refId, title, startAt, endAt, priceMinor)
- **Notification** (id, userId, kind, payload JSON, readAt)
- **SupportTicket** (id, userId, subject, status, priority, lastMessageAt)
- **TicketMessage** (id, ticketId, authorId, body, attachments JSON)
- **AuditLog** (id, actorId, actorType, action, resourceType, resourceId, ip, userAgent, before JSON, after JSON, createdAt)
- **ContentTranslation** (id, entityType, entityId, locale, fieldName, fieldValue, status) — translation table for any translatable field

## Indexing strategy (key indexes)

- `User(email)`, `User(phoneE164)`
- `Listing(kind, status, ratingAverage)`, `Listing(businessId)`
- `Booking(userId, status)`, `Booking(businessId, status)`
- `AuditLog(actorId, createdAt)`, `AuditLog(resourceType, resourceId)`
- `ContentTranslation(entityType, entityId, locale)`
- `Trip(userId, status)`

## Multi-tenant safety

Prisma middleware reads `RequestContext` and:

1. Adds `userId` filter on `own` queries.
2. Adds `businessId IN memberships` filter on `assigned` queries.
3. Refuses to return rows where the actor lacks read permission.

## Soft delete

`status` columns are used in MVP; physical delete only via admin cleanup tools later. Audit logs are append-only.

## Documented assumptions

1. Only AED in MVP, but `currencyCode` is always present so other currencies require zero schema change.
2. Translatable fields keep a *base* row in the parent table (English by default) and additional rows in `ContentTranslation`. Reads merge the requested locale, falling back to base.