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

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

Types ENUM

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

Colonnes standard

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

Domaine Sécurité

users

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

roles

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

permissions

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

role_permissions

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

user_roles

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

Domaine Utilisateurs

user_profiles

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

user_addresses

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

user_documents

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

Domaine Agence

agencies

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

agency_branches

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

Domaine Propriétaires

owners

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

owner_bank_accounts

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

Domaine Biens

properties

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

property_addresses

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

property_photos

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

features

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

property_features

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

property_availability

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

Domaine Réservations

reservations

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

Domaine Contrats

contracts

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

Domaine Paiement

payments

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

invoices

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

Domaine Messagerie

conversations

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

messages

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

Historisation

audit_logs

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

entity_history

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

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 :

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

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.

Table des matières