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.

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

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'
);

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

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

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);

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()
);

CREATE TABLE permissions (
 
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 
    code VARCHAR(100) UNIQUE NOT NULL,
 
    description TEXT
);

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)
);

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)
);

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()
);

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
);

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()
);

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()
);

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)
);

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()
);

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
);

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);

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)
);

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);

CREATE TABLE features (
 
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 
    code VARCHAR(50) UNIQUE,
 
    label VARCHAR(255)
);

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)
);

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);

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);

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
);

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);

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)
);

CREATE TABLE conversations (
 
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 
    subject VARCHAR(255),
 
    created_at TIMESTAMPTZ DEFAULT now()
);

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);

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);

Historisation générique.

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()
);

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 :

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
);

Total estimé : 80 à 90 tables

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

1. Modèle UML complet
2. Diagrammes de classes détaillés
3. Diagrammes de séquence
4. Diagrammes d'états
5. Diagrammes de composants
6. Architecture hexagonale du backend NestJS
7. Spécification OpenAPI 3.1 complète
8. 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.