Schema de base de donnees

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS vector;

packages/db/src/schema/
├── enums.ts                  pgEnums centralisés
├── pgvector.ts               custom type Drizzle pour vector(N)
├── auth.ts                   Better Auth + user_role enum (dev / admin / user)
├── members.ts                pool, group, member, member_photo, tag_category, tag, member_tag
├── rooms.ts                  room, room_capacity
├── organizers.ts             organizer
├── requests.ts               request, request_date, request_result, catalog,
│                              request_availability_check, request_positioning_check,
│                              request_requalification
├── request-labels.ts         request_label, request_label_assignment (N:M)
├── attributes.ts             attribute_type, attribute_option, member_attribute, request_attribute
├── timeline.ts               timeline_event
├── ai.ts                     ai_conversation, ai_message, knowledge_entry, training_session
├── ai-usage.ts               ai_usage (logs tokens + coûts EUR par user/jour)
├── communications.ts         email_thread, email_message
├── commissions.ts            commission, commission_rule
├── notifications.ts          notification, push_subscription
├── whatsapp.ts               whatsapp_message
├── magic-tokens.ts           magic_token (member / organizer access)
├── member-info-requests.ts   member_info_request (fiche-membre via magic link)
├── travel-estimates.ts       travel_estimate_cache (IA travel times, sha256-keyed)
├── tickets.ts                ticket, ticket_comment, ticket_attachment
├── partners.ts               partner, partner_photo
└── index.ts                  re-export *

memberStatusEnum         "active" | "inactive" | "configuring"
roomConfigurationEnum    "meeting" | "u_shape" | "theater" | "cabaret" | "classroom" | "banquet" | "cocktail"
organizerTypeEnum        "company" | "individual" | "agency" | "traiteur" | "prestataire"
requestStatusEnum        "new" | "qualifying" | "searching" | "catalog_sent" | "negotiating" | "won" | "commission" | "completed" | "lost"
requestSourceEnum        "manual" | "email" | "voice" | "ai_agent"
requestFormatEnum        "cocktail" | "seated" | "conference"
pipelineStageEnum        "search_result" | "catalog_recommended" | "catalog_compatible" | "sent" | "negotiating" | "won" | "lost"
addedByEnum              "ai" | "user"
updatedByEnum            "member" | "team" | "ai"
actorEnum                "user" | "ai" | "system" | "member" | "organizer"
knowledgeTypeEnum        "general_description" | "room_description" | "pricing" | "ambiance" | "availability_rule" | "internal_note" | "audit_note"
knowledgeSourceEnum      "voice_transcript" | "manual" | "audit" | "ai_structured"
aiMessageRoleEnum        "user" | "assistant" | "tool"
commissionStatusEnum     "pending" | "invoiced" | "paid" | "cancelled"
commissionScopeEnum      "default" | "member" | "request"
notificationTypeEnum     "new_request" | "qualification_response" | "availability_response" | "event_passed" | "inactivity_alert" | "ai_suggestion"
magicTokenScopeEnum      "member" | "organizer"
ticketStatusEnum         "todo" | "in_progress" | "awaiting_validation" | "closed"
ticketPriorityEnum       "low" | "medium" | "high" | "critical"
userRoleEnum             "dev" | "admin" | "user"               (defini dans auth.ts)
attributeKindEnum        "enum" | "multi_enum" | "boolean" | "number_range"  (defini dans attributes.ts)

pool (
  id, name, slug, description, iconName,
  extraFields jsonb,            // champs dynamiques du formulaire de demande
  position int,
  parentPoolId uuid?,           // sub-pool (1 niveau max : Restaurant → Salon privé)
  createdAt, updatedAt
)

group (
  id, name, description, logoUrl,
  legacyId text?,
  createdAt, updatedAt
)

member (
  id, code, name, description,
  address, city, postalCode, country, lat, lng,
  email, phone, contactName, website,
  poolId  → pool.id   (notNull)
  groupId → group.id  (notNull)

  // capacités venue-level (privatisation totale, indépendantes des rooms)
  maxCapacityOverall, maxCapacityCocktail, maxCapacityBanquet, maxCapacityTheater,

  budgetLow, budgetHigh, minBookingAmount,

  transport jsonb,              // Array<{ type, details }> (Métro, Bus, Parking…)
  memo text,                    // bloc-notes équipe, inclus dans le contexte IA
  pricingDetails jsonb,         // deposit, min-booking-by-period, dry-hire, price-by-day-of-week
  legacyExtras jsonb,           // { chefName?, menu?, info? } de V1

  status memberStatusEnum,
  openingHours jsonb,
  accessTokenHash text,
  embedding vector(1536), embeddingUpdatedAt,
  isFavorite bool,
  legacyId text?, legacySource text?,
  deletedAt,                    // soft delete (RGPD)
  createdAt, updatedAt
)

member_photo  (id, memberId, url, position, alt, legacyId, createdAt)

tag_category  (id, name, slug, description, iconName, position, createdAt, updatedAt)
tag           (id, name, categoryId → tag_category.id, createdAt)
                                // unique (name, categoryId), le tag est porté par sa catégorie
member_tag    (memberId, tagId, createdAt)

CREATE INDEX member_embedding_idx ON "member"
  USING ivfflat ((embedding::vector(1536)) vector_cosine_ops);

room (
  id, memberId → member.id, name, description, imageUrl,
  minPriceLunch, minPriceDinner, privatizationFee,
  openingHoursLunch jsonb, openingHoursDinner jsonb,
  properties jsonb,
  createdAt, updatedAt
)

room_capacity (
  id, roomId → room.id,
  configuration roomConfigurationEnum,   // 7 standards Kactus
  maxCapacity int, notes,
  unique (roomId, configuration)
)

organizer (
  id, companyName, type organizerTypeEnum,
  contactName, position, address, postalCode,
  email (unique), phone,
  memo,                                 // bloc-notes équipe, contexte IA
  isFavorite, firstContactAt,
  referredByMemberId → member.id?,
  createdAt, updatedAt
)

request (
  id, code (unique), organizerId → organizer.id, poolId → pool.id,
  description, participantsCount, budget,
  formats requestFormatEnum[],
  flexibleDates, postalCode, area, complementaryInfo,
  referredByMemberId → member.id?, isFirstRequest,
  source requestSourceEnum, rawSource jsonb,
  poolExtraData jsonb,                  // ex. Hotels → { roomCount: 30 }
  tagIds text[],
  memo text,                            // bloc-notes équipe, contexte IA
  status requestStatusEnum, version int,
  externalVenueName, externalVenueCity, // organisateur a choisi un lieu hors-réseau
  lostReason,
  legacyId text?, importedFromV1 bool,  // V1 = read-only, hors workflow actif
  embedding vector(1536), embeddingUpdatedAt,
  createdAt, updatedAt, closedAt
)

request_date (id, requestId, date, startTime, endTime, priority, isFlexible)

request_result (
  id, requestId, memberId,
  roomId → room.id?,                    // pin un Salon spécifique chez le membre
  pipelineStage pipelineStageEnum,
  position, aiScore, aiExplanation,
  isHidden bool default true,           // caché du catalogue public par défaut
  isShortlisted bool, shortlistedAt,    // pre-sélection orga avant choix final
  catalogSentAt,                        // dernier envoi du lien catalogue à ce membre
  addedBy addedByEnum, addedAt, updatedAt,
  legacyId text?
)

// Envoyé via magic link au membre : "Dispo le 12 mai ?"
// 1 row max par request_result (unique)
request_availability_check (
  id, resultId (unique), tokenHash,
  sentAt, respondedAt,
  acceptedDateIds text[],               // sous-set de request_date.id
  respondedBy text                      // "member" | "admin"
)

// "Souhaitez-vous vous positionner ?"
request_positioning_check (
  id, resultId (unique), tokenHash,
  sentAt, respondedAt, accepted bool?,
  respondedBy
)

// Demande publique à l'organisateur de compléter les champs manquants
request_requalification (
  id, requestId, tokenHash, publicUrl,
  missingFields jsonb (string[]),
  personalMessage,
  sentAt, viewedAt, submittedAt,
  submittedPayload jsonb,
  appliedAt, appliedBy
)

catalog (
  id, requestId (unique),
  sentAt, viewedAt,
  accessTokenHash, publicUrl,           // /public/catalogs/:token persistant
  createdAt
)

request_label             (id, name, color, legacyId?, createdAt, updatedAt)
request_label_assignment  (requestId, labelId, createdAt, PK composite)

attribute_kind = "enum" | "multi_enum" | "boolean" | "number_range"

attribute_type (
  id, name (unique), slug (unique),
  kind attribute_kind, description, unit,    // "pax" | "EUR" | null
  position,
  isHard bool,                                // si true : violation élimine
  weight numeric,                             // pondération du score (def 1.00)
  rangePresets jsonb,                         // presets sliders pour number_range
  createdAt, updatedAt
)

attribute_option (
  id, attributeTypeId, label, slug,
  description, position, createdAt
)

member_attribute (
  id, memberId, attributeTypeId,
  optionId?, numberMin?, numberMax?, boolValue?,
  confidence numeric,                         // 1.0 si humain, < 1 si extraction IA
  source text,                                // "user" | "ai" | "import"
  notes, createdAt, updatedAt
)

request_attribute (
  id, requestId, attributeTypeId,
  optionId?, numberMin?, numberMax?, boolValue?,
  isHard bool?,                               // override de type.isHard pour cette demande
  confidence, source, createdAt
)

timeline_event (
  id, requestId,
  type text,                            // event code typé via @reciprok/domain
  payload jsonb, actor actorEnum, actorId,
  createdAt
)

ai_conversation  (id, requestId (unique), userId, createdAt)
ai_message       (id, conversationId, role aiMessageRoleEnum, content jsonb, createdAt)

knowledge_entry (
  id, memberId, type knowledgeTypeEnum, content,
  source knowledgeSourceEnum, sourceRef jsonb,
  embedding vector(1536), createdAt, updatedAt
)

training_session (
  id, userId, requestId?,
  originalSuggestion jsonb, correction jsonb, notes,
  createdAt
)

ai_usage (
  id, userId, date,
  provider, model,
  inputTokens, outputTokens, cachedTokens,
  costEur numeric,                       // calculé via tarif modèle + ratio cache
  context text                           // tag libre : "chat" | "parse" | "draft" | ...
)

email_thread (id, requestId?, subject, createdAt, updatedAt)

email_message (
  id, threadId, direction text,        // "inbound" | "outbound"
  from, to, cc, subject,
  bodyHtml, bodyText,
  providerId text,                     // Resend message id (resp. wamid côté WhatsApp)
  status text default "pending",       // pending | sent | delivered | opened | bounced | failed
  openedAt, clickedAt, bouncedAt,
  metadata jsonb,
  sentAt, createdAt
)

whatsapp_message (
  id, requestId?,
  direction text,                      // "inbound" | "outbound"
  phoneFrom, phoneTo, body,
  wamid text,                          // Meta message id (équivalent providerId Resend)
  status text default "pending",       // pending | sent | delivered | read | failed | received
  sentAt, deliveredAt, readAt,
  metadata jsonb, createdAt
)

commission_rule (
  id, scope commissionScopeEnum, scopeId uuid?,  // null = default, sinon member.id / request.id
  defaultRate, newClientRate?, recurringClientRate?,
  firstReferralRate?, recurringReferralRate?,
  effectiveFrom, effectiveTo?,
  createdAt, updatedAt
)

commission (
  id, requestId, memberId,
  amount, currency default "EUR",
  rate, rateReason text,               // explique d'où vient le taux résolu
  isFirstClient, referredByMemberId?,
  invoicedAt, paidAt,
  status commissionStatusEnum,
  createdAt, updatedAt
)

ticket (
  id, title, description,
  priority ticketPriorityEnum, status ticketStatusEnum,
  section text, affectedPage text,
  createdById, assignedToId?,
  adminValidated, validatedById?, validatedAt,
  closedAt, createdAt, updatedAt
)
ticket_comment    (id, ticketId, userId, content, createdAt)
ticket_attachment (id, ticketId, commentId?, url, filename, uploadedById, createdAt)

notification        (id, userId, type notificationTypeEnum, payload jsonb, readAt?, createdAt)
push_subscription   (id, userId, endpoint, p256dh, auth, createdAt)
magic_token         (id, scope magicTokenScopeEnum, scopeId, tokenHash,
                     expiresAt, usedAt, revokedAt, createdAt)

member_info_request (
  id, memberId,
  tokenHash text,                      // raw token seulement affiché 1× (mail/copy)
  initiatedBy "team" | "member",
  personalMessage, createdByUserId,
  sentAt, viewedAt, submittedAt, cancelledAt,
  submittedPayload jsonb,              // { text, transcripts: [{text, at}] }
  aiStructured jsonb, aiParsedAt,      // sortie parseMemberBrief
  appliedAt, appliedBy, appliedFields jsonb (string[]),
  createdAt, updatedAt,
  unique partiel (memberId WHERE submittedAt IS NULL AND appliedAt IS NULL AND cancelledAt IS NULL)
)

travel_estimate_cache (
  cacheKey text PK,                    // sha256(catalogToken + roundedOrigin@100m + sortedMemberIds)
  data jsonb,                          // résultat IA : 4 modes (walking/cycling/driving/transit) × N membres
  createdAt
)

partner       (id, name, description, contactEmail, contactPhone, websiteUrl,
               categoryName, isFeatured, featuredPosition,
               legacyId?, createdAt, updatedAt)
partner_photo (id, partnerId, url, position, isMain, legacyId?, createdAt)

import { customType } from "drizzle-orm/pg-core";

export const vector = customType<{
  data: number[];
  driverData: string;
  config: { dimensions: number };
}>({
  dataType(config) { return `vector(${config.dimensions})`; },
  toDriver(value)  { return `[${value.join(",")}]`; },
  fromDriver(value){ return value.slice(1, -1).split(",").map(Number); },
});