====== Schéma PostgreSQL détaillé ======

===== Introduction =====

Ce document constitue la fondation du système d'information.

Objectifs :

  * Normalisation des données
  * Historisation des opérations
  * Audit complet
  * Compatibilité PostgreSQL 16+
  * Compatible ORM (TypeORM / Prisma / Hibernate)

Conventions :

  * Toutes les tables possèdent un UUID primaire.
  * Toutes les tables métiers sont historisées.
  * Toutes les modifications sont auditables.
  * Toutes les FK sont indexées.
  * Soft delete systématique.

----

====== Types PostgreSQL ======

===== Extensions =====

<code sql>
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS pgcrypto;
</code>

----

===== Types ENUM =====

<code sql>
CREATE TYPE user_status AS ENUM (
    'ACTIVE',
    'INACTIVE',
    'SUSPENDED',
    'DELETED'
);

CREATE TYPE property_status AS ENUM (
    'DRAFT',
    'PUBLISHED',
    'RENTED',
    'MAINTENANCE',
    'ARCHIVED'
);

CREATE TYPE reservation_status AS ENUM (
    'PENDING',
    'CONFIRMED',
    'SIGNED',
    'CANCELLED',
    'COMPLETED'
);

CREATE TYPE payment_status AS ENUM (
    'PENDING',
    'AUTHORIZED',
    'PAID',
    'REFUNDED',
    'FAILED'
);
</code>

----

====== Colonnes standard ======

Toutes les tables métiers héritent logiquement des colonnes suivantes :

<code sql>
id UUID PRIMARY KEY
created_at TIMESTAMPTZ NOT NULL
updated_at TIMESTAMPTZ NOT NULL

created_by UUID NULL
updated_by UUID NULL

deleted_at TIMESTAMPTZ NULL

version INTEGER DEFAULT 1
</code>

----

====== Domaine Sécurité ======

===== users =====

<code sql>
CREATE TABLE users (

    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    email VARCHAR(255) NOT NULL UNIQUE,

    password_hash VARCHAR(500) NOT NULL,

    status user_status NOT NULL DEFAULT 'ACTIVE',

    email_verified BOOLEAN DEFAULT FALSE,

    last_login_at TIMESTAMPTZ,

    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    deleted_at TIMESTAMPTZ
);

CREATE INDEX idx_users_email
ON users(email);

CREATE INDEX idx_users_status
ON users(status);
</code>

----

===== roles =====

<code sql>
CREATE TABLE roles (

    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    code VARCHAR(50) UNIQUE NOT NULL,

    name VARCHAR(100) NOT NULL,

    description TEXT,

    created_at TIMESTAMPTZ DEFAULT now()
);
</code>

----

===== permissions =====

<code sql>
CREATE TABLE permissions (

    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    code VARCHAR(100) UNIQUE NOT NULL,

    description TEXT
);
</code>

----

===== role_permissions =====

<code sql>
CREATE TABLE role_permissions (

    role_id UUID NOT NULL REFERENCES roles(id),

    permission_id UUID NOT NULL REFERENCES permissions(id),

    PRIMARY KEY(role_id, permission_id)
);
</code>

----

===== user_roles =====

<code sql>
CREATE TABLE user_roles (

    user_id UUID NOT NULL REFERENCES users(id),

    role_id UUID NOT NULL REFERENCES roles(id),

    PRIMARY KEY(user_id, role_id)
);
</code>

----

====== Domaine Utilisateurs ======

===== user_profiles =====

<code sql>
CREATE TABLE user_profiles (

    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    user_id UUID NOT NULL UNIQUE REFERENCES users(id),

    first_name VARCHAR(100),

    last_name VARCHAR(100),

    phone VARCHAR(50),

    birth_date DATE,

    nationality VARCHAR(100),

    created_at TIMESTAMPTZ DEFAULT now(),

    updated_at TIMESTAMPTZ DEFAULT now()
);
</code>

----

===== user_addresses =====

<code sql>
CREATE TABLE user_addresses (

    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    user_id UUID NOT NULL REFERENCES users(id),

    address_1 VARCHAR(255),

    address_2 VARCHAR(255),

    postal_code VARCHAR(20),

    city VARCHAR(150),

    country VARCHAR(150),

    is_default BOOLEAN DEFAULT TRUE
);
</code>

----

===== user_documents =====

<code sql>
CREATE TABLE user_documents (

    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    user_id UUID NOT NULL REFERENCES users(id),

    document_type VARCHAR(50),

    file_name VARCHAR(255),

    storage_path TEXT,

    uploaded_at TIMESTAMPTZ DEFAULT now()
);
</code>

----

====== Domaine Agence ======

===== agencies =====

<code sql>
CREATE TABLE agencies (

    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    code VARCHAR(50) UNIQUE,

    name VARCHAR(255) NOT NULL,

    registration_number VARCHAR(100),

    vat_number VARCHAR(100),

    email VARCHAR(255),

    phone VARCHAR(50),

    website VARCHAR(255),

    created_at TIMESTAMPTZ DEFAULT now(),

    updated_at TIMESTAMPTZ DEFAULT now()
);
</code>

----

===== agency_branches =====

<code sql>
CREATE TABLE agency_branches (

    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    agency_id UUID NOT NULL REFERENCES agencies(id),

    name VARCHAR(255),

    address TEXT,

    city VARCHAR(150),

    postal_code VARCHAR(20),

    phone VARCHAR(50)
);
</code>

----

====== Domaine Propriétaires ======

===== owners =====

<code sql>
CREATE TABLE owners (

    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    user_id UUID NOT NULL UNIQUE REFERENCES users(id),

    agency_id UUID REFERENCES agencies(id),

    owner_code VARCHAR(50) UNIQUE,

    active BOOLEAN DEFAULT TRUE,

    created_at TIMESTAMPTZ DEFAULT now()
);
</code>

----

===== owner_bank_accounts =====

<code sql>
CREATE TABLE owner_bank_accounts (

    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    owner_id UUID NOT NULL REFERENCES owners(id),

    iban VARCHAR(50),

    bic VARCHAR(20),

    account_holder VARCHAR(255),

    active BOOLEAN DEFAULT TRUE
);
</code>

----

====== Domaine Biens ======

===== properties =====

<code sql>
CREATE TABLE properties (

    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    owner_id UUID NOT NULL REFERENCES owners(id),

    property_code VARCHAR(50) UNIQUE,

    title VARCHAR(255) NOT NULL,

    slug VARCHAR(255) UNIQUE,

    status property_status NOT NULL,

    property_type VARCHAR(50),

    category VARCHAR(50),

    surface NUMERIC(10,2),

    room_count INTEGER,

    bedroom_count INTEGER,

    bathroom_count INTEGER,

    max_capacity INTEGER,

    description_short TEXT,

    description_long TEXT,

    created_at TIMESTAMPTZ DEFAULT now(),

    updated_at TIMESTAMPTZ DEFAULT now(),

    deleted_at TIMESTAMPTZ
);

CREATE INDEX idx_properties_owner
ON properties(owner_id);

CREATE INDEX idx_properties_status
ON properties(status);
</code>

----

===== property_addresses =====

<code sql>
CREATE TABLE property_addresses (

    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    property_id UUID UNIQUE REFERENCES properties(id),

    address_1 VARCHAR(255),

    address_2 VARCHAR(255),

    city VARCHAR(150),

    postal_code VARCHAR(20),

    country VARCHAR(100),

    latitude NUMERIC(10,7),

    longitude NUMERIC(10,7)
);
</code>

----

===== property_photos =====

<code sql>
CREATE TABLE property_photos (

    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    property_id UUID NOT NULL REFERENCES properties(id),

    file_name VARCHAR(255),

    storage_path TEXT,

    display_order INTEGER,

    is_cover BOOLEAN DEFAULT FALSE
);

CREATE INDEX idx_property_photos_property
ON property_photos(property_id);
</code>

----

===== features =====

<code sql>
CREATE TABLE features (

    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    code VARCHAR(50) UNIQUE,

    label VARCHAR(255)
);
</code>

----

===== property_features =====

<code sql>
CREATE TABLE property_features (

    property_id UUID NOT NULL REFERENCES properties(id),

    feature_id UUID NOT NULL REFERENCES features(id),

    PRIMARY KEY(property_id, feature_id)
);
</code>

----

===== property_availability =====

<code sql>
CREATE TABLE property_availability (

    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    property_id UUID NOT NULL REFERENCES properties(id),

    start_date DATE NOT NULL,

    end_date DATE NOT NULL,

    status VARCHAR(30) NOT NULL,

    CHECK(end_date >= start_date)
);

CREATE INDEX idx_property_availability
ON property_availability(property_id,start_date,end_date);
</code>

----

====== Domaine Réservations ======

===== reservations =====

<code sql>
CREATE TABLE reservations (

    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    property_id UUID NOT NULL REFERENCES properties(id),

    tenant_id UUID NOT NULL REFERENCES users(id),

    reservation_number VARCHAR(50) UNIQUE,

    start_date DATE NOT NULL,

    end_date DATE NOT NULL,

    guest_count INTEGER,

    total_amount NUMERIC(12,2),

    status reservation_status NOT NULL,

    created_at TIMESTAMPTZ DEFAULT now(),

    updated_at TIMESTAMPTZ DEFAULT now(),

    CHECK(end_date > start_date)
);

CREATE INDEX idx_reservation_property
ON reservations(property_id);

CREATE INDEX idx_reservation_tenant
ON reservations(tenant_id);

CREATE INDEX idx_reservation_status
ON reservations(status);
</code>

----

====== Domaine Contrats ======

===== contracts =====

<code sql>
CREATE TABLE contracts (

    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    reservation_id UUID UNIQUE REFERENCES reservations(id),

    contract_number VARCHAR(50) UNIQUE,

    version INTEGER DEFAULT 1,

    generated_at TIMESTAMPTZ,

    signed_at TIMESTAMPTZ,

    file_path TEXT
);
</code>

----

====== Domaine Paiement ======

===== payments =====

<code sql>
CREATE TABLE payments (

    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    reservation_id UUID NOT NULL REFERENCES reservations(id),

    amount NUMERIC(12,2) NOT NULL,

    payment_date TIMESTAMPTZ,

    payment_method VARCHAR(50),

    status payment_status NOT NULL,

    transaction_reference VARCHAR(255)
);

CREATE INDEX idx_payments_reservation
ON payments(reservation_id);
</code>

----

===== invoices =====

<code sql>
CREATE TABLE invoices (

    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    reservation_id UUID REFERENCES reservations(id),

    invoice_number VARCHAR(50) UNIQUE,

    invoice_date DATE,

    due_date DATE,

    total_ht NUMERIC(12,2),

    total_tva NUMERIC(12,2),

    total_ttc NUMERIC(12,2)
);
</code>

----

====== Domaine Messagerie ======

===== conversations =====

<code sql>
CREATE TABLE conversations (

    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    subject VARCHAR(255),

    created_at TIMESTAMPTZ DEFAULT now()
);
</code>

----

===== messages =====

<code sql>
CREATE TABLE messages (

    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    conversation_id UUID NOT NULL REFERENCES conversations(id),

    sender_id UUID NOT NULL REFERENCES users(id),

    message_body TEXT NOT NULL,

    created_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX idx_messages_conversation
ON messages(conversation_id);
</code>

----

====== Historisation ======

===== audit_logs =====

<code sql>
CREATE TABLE audit_logs (

    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    entity_name VARCHAR(100) NOT NULL,

    entity_id UUID NOT NULL,

    action VARCHAR(20) NOT NULL,

    old_values JSONB,

    new_values JSONB,

    performed_by UUID REFERENCES users(id),

    performed_at TIMESTAMPTZ NOT NULL DEFAULT now(),

    ip_address VARCHAR(100),

    user_agent TEXT
);

CREATE INDEX idx_audit_entity
ON audit_logs(entity_name, entity_id);

CREATE INDEX idx_audit_date
ON audit_logs(performed_at);
</code>

----

===== entity_history =====

Historisation générique.

<code sql>
CREATE TABLE entity_history (

    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    entity_name VARCHAR(100),

    entity_id UUID,

    version INTEGER,

    snapshot JSONB,

    created_at TIMESTAMPTZ DEFAULT now()
);
</code>

----

====== Stratégie d'indexation ======

Créer systématiquement :

  * Index sur toutes les FK
  * Index sur les colonnes de recherche
  * Index composites sur les calendriers
  * Index sur les statuts

Exemples :

<code sql>
CREATE INDEX idx_property_city
ON property_addresses(city);

CREATE INDEX idx_property_geo
ON property_addresses(latitude, longitude);

CREATE INDEX idx_property_dates
ON property_availability(
    property_id,
    start_date,
    end_date
);
</code>

----

====== Volume cible ======

^ Domaine ^ Tables ^
| Sécurité | 12 |
| Utilisateurs | 8 |
| Agence | 7 |
| Propriétaires | 6 |
| Biens | 18 |
| Réservations | 7 |
| Contrats | 5 |
| Paiements | 8 |
| CRM | 6 |
| Messagerie | 5 |
| Reporting | 5 |
| Audit | 2 |

**Total estimé : 80 à 90 tables**

----

====== Étape suivante ======

À partir de ce schéma, nous pouvons désormais produire :

  - Modèle UML complet
  - Diagrammes de classes détaillés
  - Diagrammes de séquence
  - Diagrammes d'états
  - Diagrammes de composants
  - Architecture hexagonale du backend NestJS
  - Spécification OpenAPI 3.1 complète
  - Backlog Agile détaillé (300+ User Stories)

Le prochain livrable recommandé est le **modèle UML complet**, car il servira directement à générer les API et l'architecture logicielle.