Schema Base de Donnees Scell.io

Version : 1.0 Derniere mise a jour : 2026-03-03 SGBD : PostgreSQL 17 ORM : Eloquent (Laravel 12)

---

Table des matieres

• 1. Vue d'ensemble
• 2. Tables Core
• 3. Tables Facturation
• 4. Tables Avoirs
• 5. Tables Signatures
• 6. Tables Multi-Tenant
• 7. Tables Onboarding
• 8. Tables Fiscales (NF525)
• 9. Tables Webhooks
• 10. Tables Billing
• 11. Tables Integration
• 12. Tables MCP
• 13. Tables Support
• 14. Migrations

---

1. Vue d'ensemble

Le schema comporte 41+ tables reparties en 12 domaines fonctionnels. Toutes les tables suivent ces conventions :

• Cles primaires : UUID v4 (trait HasUuids)
• Timestamps : created_at et updated_at automatiques (sauf tables fiscales append-only)
• Soft deletes : uniquement sur invoices, signatures et tenants
• JSON : colonnes metadata, address, settings en JSONB PostgreSQL
• Decimaux : DECIMAL(12,4) pour les montants financiers

Diagramme ER global

erDiagram
    %% Core
    users ||--o{ companies : "possede"
    users ||--o| balances : "a"
    users ||--o{ api_keys : "cree"
    users ||--o{ transactions : "genere"
    users ||--o{ webhooks : "configure"

    %% Companies
    companies ||--o{ invoices : "emet"
    companies ||--o{ signatures : "demande"
    companies ||--o{ credit_notes : "emet"

    %% Multi-Tenant
    partners ||--o{ tenants : "onboarde"
    tenants ||--o{ sub_tenants : "gere"
    tenants ||--o{ companies : "contient"
    sub_tenants ||--o{ companies : "associe"
    tenants ||--o{ tenant_invoices : "facture"
    tenants ||--o{ tenant_transactions : "comptabilise"

    %% Facturation
    invoices ||--o{ invoice_lines : "contient"
    invoices ||--o{ credit_notes : "corrige"
    tenants ||--o{ invoice_sequences : "definit"

    %% Avoirs
    credit_notes ||--o{ credit_note_items : "contient"

    %% Signatures
    signatures ||--o{ signers : "implique"

    %% Onboarding
    partners ||--o{ onboarding_sessions : "initie"
    onboarding_sessions ||--o{ kyb_documents : "collecte"
    tenants ||--o{ kyb_documents : "fournit"

    %% Fiscal
    tenants ||--o{ fiscal_entries : "enregistre"
    tenants ||--o| fiscal_sequences : "numerote"
    tenants ||--o{ fiscal_closings : "cloture"
    tenants ||--o{ fiscal_attestations : "atteste"
    tenants ||--o{ fiscal_anchors : "ancre"
    tenants ||--o| fiscal_kill_switches : "controle"
    tenants ||--o{ fiscal_integrity_checks : "verifie"
    tenants ||--o{ fiscal_audit_log : "audite"
    tenants ||--o{ fiscal_rules : "applique"
    fiscal_entries ||--o{ fiscal_anchors : "horodate"
    fiscal_rules ||--o{ fiscal_rule_applications : "execute"

    %% Webhooks
    webhooks ||--o{ webhook_logs : "journalise"

    %% Integration
    users ||--o| openapi_configs : "configure"
    openapi_configs ||--o{ openapi_tokens : "genere"

    %% MCP
    tenants ||--o{ mcp_sessions : "utilise"

---

2. Tables Core

users

Table principale des utilisateurs du dashboard Scell.io.

Colonne	Type	Contraintes	Description
id	UUID	PK	Identifiant unique
email	VARCHAR	UNIQUE, NOT NULL	Adresse email
password	VARCHAR	NOT NULL	Mot de passe (bcrypt)
google_id	VARCHAR	UNIQUE, NULLABLE	ID OAuth Google
first_name	VARCHAR	NOT NULL	Prenom
last_name	VARCHAR	NOT NULL	Nom
phone	VARCHAR	NULLABLE	Telephone
avatar_url	VARCHAR	NULLABLE	URL avatar
role	ENUM	NOT NULL	admin, user, partner
is_active	BOOLEAN	DEFAULT true	Compte actif
last_login_at	TIMESTAMP	NULLABLE	Derniere connexion
last_login_ip	VARCHAR	NULLABLE	IP derniere connexion
email_verified_at	TIMESTAMP	NULLABLE	Date verification email
remember_token	VARCHAR	NULLABLE	Token "se souvenir de moi"
created_at	TIMESTAMP
updated_at	TIMESTAMP

Relations : companies (1:N), api_keys (1:N), balance (1:1), transactions (1:N), invoices (1:N), signatures (1:N), webhooks (1:N)

companies

Entites juridiques (personnes morales) associees a un utilisateur ou un tenant.

Colonne	Type	Contraintes	Description
id	UUID	PK	Identifiant unique
user_id	UUID	FK(users), NULLABLE	Proprietaire dashboard
tenant_id	UUID	FK(tenants), NULLABLE	Tenant d'appartenance
sub_tenant_id	UUID	FK(sub_tenants), NULLABLE	Sub-tenant d'appartenance
name	VARCHAR	NOT NULL	Raison sociale
siret	VARCHAR(14)	NULLABLE	Numero SIRET
vat_number	VARCHAR	NULLABLE	Numero TVA intracommunautaire
legal_form	VARCHAR	NULLABLE	Forme juridique (SAS, SARL, etc.)
address_line1	VARCHAR	NOT NULL	Adresse ligne 1
address_line2	VARCHAR	NULLABLE	Adresse ligne 2
postal_code	VARCHAR	NOT NULL	Code postal
city	VARCHAR	NOT NULL	Ville
country	VARCHAR(2)	NOT NULL, DEFAULT 'FR'	Code pays ISO
phone	VARCHAR	NULLABLE	Telephone
email	VARCHAR	NULLABLE	Email de contact
website	VARCHAR	NULLABLE	Site web
logo_url	VARCHAR	NULLABLE	URL du logo
status	ENUM	NOT NULL	active, inactive, suspended
kyc_completed_at	TIMESTAMP	NULLABLE	Date validation KYC
kyc_reference	VARCHAR	NULLABLE	Reference KYC
superpdp_id	VARCHAR	NULLABLE	ID SuperPDP
metadata	JSONB	DEFAULT '{}'	Donnees supplementaires
created_at	TIMESTAMP
updated_at	TIMESTAMP

Indexes : (user_id), (tenant_id), (sub_tenant_id), (siret)

Scopes : forTenant(?string), forSubTenant(?string)

api_keys

Cles d'authentification pour l'acces API externe.

Colonne	Type	Contraintes	Description
id	UUID	PK	Identifiant unique
user_id	UUID	FK(users)	Proprietaire
company_id	UUID	FK(companies)	Entreprise associee
name	VARCHAR	NOT NULL	Nom de la cle
key_hash	VARCHAR	NOT NULL	Hash SHA256 de la cle
key_prefix	VARCHAR(8)	NOT NULL	Prefix visible (sk_live_, sk_test_)
environment	ENUM	NOT NULL	sandbox, production
is_active	BOOLEAN	DEFAULT true	Cle active
last_used_at	TIMESTAMP	NULLABLE	Derniere utilisation
expires_at	TIMESTAMP	NULLABLE	Date d'expiration
metadata	JSONB	DEFAULT '{}'	Donnees supplementaires
created_at	TIMESTAMP
updated_at	TIMESTAMP

Indexes : (key_hash) UNIQUE, (key_prefix), (user_id), (company_id)

---

3. Tables Facturation

invoices

Table principale des factures emises et recues.

Colonne	Type	Contraintes	Description
id	UUID	PK
user_id	UUID	FK(users), NULLABLE	Createur dashboard
company_id	UUID	FK(companies)	Entreprise emettrice
api_key_id	UUID	FK(api_keys), NULLABLE	Cle API utilisee
environment	ENUM	NOT NULL	sandbox, production
external_id	VARCHAR	NULLABLE	ID externe client
invoice_number	VARCHAR	NOT NULL	Numero de facture
direction	ENUM	NOT NULL	outgoing, incoming
input_format	VARCHAR	NULLABLE	Format source
output_format	VARCHAR	NULLABLE	Format cible
issue_date	DATE	NOT NULL	Date d'emission
due_date	DATE	NULLABLE	Date d'echeance
currency	VARCHAR(3)	DEFAULT 'EUR'	Devise
total_ht	DECIMAL(12,4)	NOT NULL	Total HT
total_tax	DECIMAL(12,4)	NOT NULL	Total TVA
total_ttc	DECIMAL(12,4)	NOT NULL	Total TTC
seller_siret	VARCHAR(14)	NOT NULL	SIRET vendeur
seller_name	VARCHAR	NOT NULL	Nom vendeur
seller_address	TEXT	NOT NULL	Adresse vendeur
buyer_siret	VARCHAR(14)	NOT NULL	SIRET acheteur
buyer_name	VARCHAR	NOT NULL	Nom acheteur
buyer_address	TEXT	NOT NULL	Adresse acheteur
original_file_path	VARCHAR	NULLABLE	Chemin fichier source
original_file_hash	VARCHAR	NULLABLE	Hash fichier source
converted_file_path	VARCHAR	NULLABLE	Chemin fichier converti
converted_file_hash	VARCHAR	NULLABLE	Hash fichier converti
pdf_file_path	VARCHAR	NULLABLE	Chemin PDF
status	ENUM	NOT NULL	draft, submitted, accepted, rejected, paid, error
legal_status	ENUM	NULLABLE	donnee_fiscale, donnee_comptable, donnee_technique
submitted_at	TIMESTAMP	NULLABLE	Date soumission PDP
paid_at	TIMESTAMP	NULLABLE	Date paiement
paid_status	ENUM	NULLABLE	pending, partial, complete
metadata	JSONB	DEFAULT '{}'
audit_trail	JSONB	NULLABLE	Historique d'audit
created_at	TIMESTAMP
updated_at	TIMESTAMP
deleted_at	TIMESTAMP	NULLABLE	Soft delete

Immutabilite NF525 : les champs total_ht, total_ttc, total_tax, invoice_number, issue_date, seller_*, buyer_*, currency, user_id, company_id sont verrouilles une fois status != 'draft'.

Indexes : (company_id), (user_id), (invoice_number, company_id) UNIQUE, (status), (direction)

invoice_lines

Lignes de detail d'une facture.

Colonne	Type	Contraintes	Description
id	UUID	PK
invoice_id	UUID	FK(invoices), ON DELETE CASCADE	Facture parente
description	TEXT	NOT NULL	Description
quantity	DECIMAL(10,4)	NOT NULL	Quantite
unit_price	DECIMAL(12,4)	NOT NULL	Prix unitaire HT
total_ht	DECIMAL(12,4)	NOT NULL	Total HT
tax_rate	DECIMAL(5,2)	NOT NULL	Taux TVA
tax_amount	DECIMAL(12,4)	NOT NULL	Montant TVA
total_ttc	DECIMAL(12,4)	NOT NULL	Total TTC
created_at	TIMESTAMP
updated_at	TIMESTAMP

invoice_sequences

Sequences de numerotation par tenant et sub-tenant.

Colonne	Type	Contraintes	Description
id	UUID	PK
tenant_id	UUID	FK(tenants)	Tenant proprietaire
sub_tenant_id	UUID	FK(sub_tenants), NULLABLE	Sub-tenant
year	INT	NOT NULL	Annee
month	INT	NULLABLE	Mois (si numerotation mensuelle)
next_number	INT	NOT NULL, DEFAULT 1	Prochain numero
prefix	VARCHAR	NULLABLE	Prefixe (ex: "FA-")
suffix	VARCHAR	NULLABLE	Suffixe
format	VARCHAR	NOT NULL	Format (ex: "YYYYNNNNN")
format_type	ENUM	NOT NULL	numeric, alphanumeric, custom
total_created	INT	DEFAULT 0	Total factures creees
status	ENUM	DEFAULT 'active'	active, frozen
created_at	TIMESTAMP
updated_at	TIMESTAMP

Contrainte unique : (tenant_id, sub_tenant_id, year, month)

---

4. Tables Avoirs

credit_notes

Avoirs (notes de credit) associes a des factures.

Colonne	Type	Contraintes	Description
id	UUID	PK
user_id	UUID	FK(users), NULLABLE	Createur
company_id	UUID	FK(companies), NULLABLE	Entreprise
tenant_id	UUID	FK(tenants), NULLABLE	Tenant
sub_tenant_id	UUID	FK(sub_tenants), NULLABLE	Sub-tenant
invoice_id	UUID	FK(invoices), NULLABLE	Facture d'origine
credit_note_number	VARCHAR	UNIQUE	Numero d'avoir
issue_date	DATE	NOT NULL	Date d'emission
due_date	DATE	NULLABLE	Date d'echeance
reason	TEXT	NOT NULL	Motif
subtotal	DECIMAL(12,4)	NOT NULL	Sous-total HT
tax_amount	DECIMAL(12,4)	NOT NULL	Montant TVA
total	DECIMAL(12,4)	NOT NULL	Total TTC
currency	VARCHAR(3)	DEFAULT 'EUR'	Devise
seller_name	VARCHAR	NOT NULL	Vendeur
seller_siret	VARCHAR(14)	NOT NULL	SIRET vendeur
seller_address	TEXT	NOT NULL	Adresse vendeur
buyer_name	VARCHAR	NOT NULL	Acheteur
buyer_siret	VARCHAR(14)	NOT NULL	SIRET acheteur
buyer_address	TEXT	NOT NULL	Adresse acheteur
client_name	VARCHAR	NULLABLE	Client final
client_siret	VARCHAR(14)	NULLABLE	SIRET client
client_address	TEXT	NULLABLE	Adresse client
items_count	INT	DEFAULT 0	Nombre d'articles
status	ENUM	NOT NULL	draft, issued, sent, archived, cancelled
legal_status	ENUM	NULLABLE	donnee_fiscale, donnee_comptable
sent_at	TIMESTAMP	NULLABLE
archived_at	TIMESTAMP	NULLABLE
metadata	JSONB	DEFAULT '{}'
created_at	TIMESTAMP
updated_at	TIMESTAMP

Pas de soft delete (ADR-008 : conformite NF525). La suppression n'est autorisee que pour status = 'draft'.

credit_note_items

Lignes de detail d'un avoir.

Colonne	Type	Contraintes	Description
id	UUID	PK
credit_note_id	UUID	FK(credit_notes), ON DELETE CASCADE	Avoir parent
description	TEXT	NOT NULL	Description
quantity	DECIMAL(10,4)	NOT NULL	Quantite
unit_price	DECIMAL(12,4)	NOT NULL	Prix unitaire HT
total_ht	DECIMAL(12,4)	NOT NULL	Total HT
tax_rate	DECIMAL(5,2)	NOT NULL	Taux TVA
tax_amount	DECIMAL(12,4)	NOT NULL	Montant TVA
total_ttc	DECIMAL(12,4)	NOT NULL	Total TTC
created_at	TIMESTAMP
updated_at	TIMESTAMP

---

5. Tables Signatures

signatures

Demandes de signature electronique (eIDAS EU-SES).

Colonne	Type	Contraintes	Description
id	UUID	PK
user_id	UUID	FK(users)	Createur
company_id	UUID	FK(companies)	Entreprise
api_key_id	UUID	FK(api_keys), NULLABLE	Cle API utilisee
environment	ENUM	NOT NULL	sandbox, production
external_id	VARCHAR	NULLABLE	ID externe client
title	VARCHAR	NOT NULL	Titre du document
description	TEXT	NULLABLE	Description
original_file_path	VARCHAR	NOT NULL	Chemin fichier original
original_file_hash	VARCHAR	NOT NULL	Hash SHA256 fichier
original_file_name	VARCHAR	NOT NULL	Nom du fichier
original_file_size	INT	NOT NULL	Taille en octets
signed_file_path	VARCHAR	NULLABLE	Chemin fichier signe
signed_file_hash	VARCHAR	NULLABLE	Hash fichier signe
auth_method	ENUM	NOT NULL	sms, email, biometric
signature_positions	JSONB	NULLABLE	Positions de signature
ui_config	JSONB	DEFAULT '{}'	Configuration interface
status	ENUM	NOT NULL	waiting_signers, partially_signed, signed, error, cancelled, expired
status_message	TEXT	NULLABLE	Message de statut
openapi_id	VARCHAR	NULLABLE	ID OpenAPI.com
openapi_status	VARCHAR	NULLABLE	Statut OpenAPI
openapi_response	JSONB	NULLABLE	Reponse OpenAPI
signing_urls	JSONB	NULLABLE	URLs de signature
redirect_complete_url	VARCHAR	NULLABLE	URL redirection apres signature
redirect_cancel_url	VARCHAR	NULLABLE	URL redirection annulation
archive_enabled	BOOLEAN	DEFAULT false	Archivage active
archive_status	VARCHAR	NULLABLE	Statut archivage
archive_path	VARCHAR	NULLABLE	Chemin archive
archive_until	DATE	NULLABLE	Date fin archivage
audit_trail_path	VARCHAR	NULLABLE	Chemin piste d'audit
transaction_id	UUID	FK(transactions), NULLABLE	Transaction associee
amount_charged	DECIMAL(7,2)	DEFAULT 0	Montant debite (1.20 EUR)
expires_at	TIMESTAMP	NOT NULL	Date expiration
completed_at	TIMESTAMP	NULLABLE	Date completion
created_at	TIMESTAMP
updated_at	TIMESTAMP
deleted_at	TIMESTAMP	NULLABLE	Soft delete

signers

Signataires d'une demande de signature.

Colonne	Type	Contraintes	Description
id	UUID	PK
signature_id	UUID	FK(signatures), ON DELETE CASCADE	Demande parente
email	VARCHAR	NOT NULL	Email signataire
phone	VARCHAR	NULLABLE	Telephone (pour SMS OTP)
first_name	VARCHAR	NOT NULL	Prenom
last_name	VARCHAR	NOT NULL	Nom
signed_at	TIMESTAMP	NULLABLE	Date de signature
auth_method	ENUM	NOT NULL	sms, email, biometric
status	ENUM	NOT NULL	waiting, signed, cancelled
sms_code_sent_at	TIMESTAMP	NULLABLE	Dernier envoi SMS OTP
created_at	TIMESTAMP
updated_at	TIMESTAMP

---

6. Tables Multi-Tenant

tenants

Entreprises clientes de la plateforme (B2B).

Colonne	Type	Contraintes	Description
id	UUID	PK
partner_id	UUID	FK(partners), NULLABLE	Partenaire d'onboarding
name	VARCHAR	NOT NULL	Nom commercial
slug	VARCHAR	UNIQUE	Identifiant URL-friendly
legal_name	VARCHAR	NOT NULL	Raison sociale
siret	VARCHAR(14)	NULLABLE	SIRET
vat_number	VARCHAR	NULLABLE	TVA intracommunautaire
billing_email	VARCHAR	NULLABLE	Email facturation
technical_email	VARCHAR	NULLABLE	Email technique
contact_email	VARCHAR	NULLABLE	Email contact
contact_phone	VARCHAR	NULLABLE	Telephone
address	JSONB	NULLABLE	Adresse structuree
api_key_hash	VARCHAR	NOT NULL	Hash cle API tenant
api_key_prefix	VARCHAR(12)	UNIQUE	Prefix (sk_live_, sk_test_)
publishable_key_hash	VARCHAR	NULLABLE	Hash cle publique tenant
publishable_key_prefix	VARCHAR(12)	NULLABLE UNIQUE	Prefix (pk_live_, pk_test_)
environment	ENUM	NOT NULL	sandbox, production
kyb_status	ENUM	NOT NULL	pending, documents_submitted, under_review, verified, rejected
kyb_verified_at	TIMESTAMP	NULLABLE	Date verification KYB
kyb_rejection_reason	TEXT	NULLABLE	Motif de rejet
balance	DECIMAL(12,4)	DEFAULT 0	Solde disponible
stripe_customer_id	VARCHAR	NULLABLE	ID client Stripe
webhook_url	VARCHAR	NULLABLE	URL webhook
webhook_secret	VARCHAR	NULLABLE	Secret webhook
settings	JSONB	DEFAULT '{}'	Parametres
metadata	JSONB	DEFAULT '{}'	Donnees supplementaires
is_active	BOOLEAN	DEFAULT true	Actif
status	ENUM	NOT NULL	active, inactive, suspended
deleted_at	TIMESTAMP	NULLABLE	Soft delete
created_at	TIMESTAMP
updated_at	TIMESTAMP

sub_tenants

Sous-entites gerees par un tenant (clients du tenant).

Colonne	Type	Contraintes	Description
id	UUID	PK
tenant_id	UUID	FK(tenants), NOT NULL	Tenant parent
external_id	VARCHAR	NULLABLE	ID dans le systeme du partenaire
name	VARCHAR	NOT NULL	Nom
email	VARCHAR	NULLABLE	Email
contact_person	VARCHAR	NULLABLE	Personne de contact
phone	VARCHAR	NULLABLE	Telephone
siret	VARCHAR(14)	NULLABLE	SIRET
vat_number	VARCHAR	NULLABLE	TVA
address	JSONB	NULLABLE	Adresse
status	ENUM	DEFAULT 'active'	active, inactive
metadata	JSONB	DEFAULT '{}'
created_at	TIMESTAMP
updated_at	TIMESTAMP

Scope : forTenant(string $tenantId)

partners

Partenaires integrateurs (cabinets comptables, ERP, etc.).

Colonne	Type	Contraintes	Description
id	UUID	PK
name	VARCHAR	NOT NULL	Nom
description	TEXT	NULLABLE	Description
slug	VARCHAR	UNIQUE	Identifiant
api_key	VARCHAR	NOT NULL	Cle API partenaire
webhook_url	VARCHAR	NULLABLE	URL webhook
webhook_secret	VARCHAR	NULLABLE	Secret webhook
is_active	BOOLEAN	DEFAULT true	Actif
metadata	JSONB	DEFAULT '{}'
created_at	TIMESTAMP
updated_at	TIMESTAMP

tenant_invoices

Factures de facturation consolidee tenant (factures Scell.io vers tenant).

Colonne	Type	Contraintes	Description
id	UUID	PK
tenant_id	UUID	FK(tenants)	Tenant facture
invoice_number	VARCHAR	NOT NULL	Numero facture Scell.io
period_start	DATE	NOT NULL	Debut periode
period_end	DATE	NOT NULL	Fin periode
issue_date	DATE	NOT NULL	Date emission
due_date	DATE	NOT NULL	Date echeance
invoices_count	INT	DEFAULT 0	Nombre de factures traitees
invoices_amount	DECIMAL(12,4)	DEFAULT 0	Montant factures
signatures_count	INT	DEFAULT 0	Nombre de signatures
signatures_amount	DECIMAL(12,4)	DEFAULT 0	Montant signatures
credit_notes_count	INT	DEFAULT 0	Nombre d'avoirs
credit_notes_amount	DECIMAL(12,4)	DEFAULT 0	Montant avoirs
subtotal_ht	DECIMAL(12,4)	NOT NULL	Sous-total HT
discount_percent	DECIMAL(5,2)	NULLABLE	Pourcentage remise
discount_amount	DECIMAL(12,4)	NULLABLE	Montant remise
total_ht	DECIMAL(12,4)	NOT NULL	Total HT
vat_rate	DECIMAL(5,2)	DEFAULT 20	Taux TVA
total_vat	DECIMAL(12,4)	NOT NULL	Total TVA
total_ttc	DECIMAL(12,4)	NOT NULL	Total TTC
status	ENUM	NOT NULL	draft, sent, paid, overdue, cancelled
sent_at	TIMESTAMP	NULLABLE
paid_at	TIMESTAMP	NULLABLE
payment_method	VARCHAR	NULLABLE	Methode paiement
payment_reference	VARCHAR	NULLABLE	Reference paiement
stripe_invoice_id	VARCHAR	NULLABLE	ID Stripe
pdf_path	VARCHAR	NULLABLE	Chemin PDF
metadata	JSONB	DEFAULT '{}'
created_at	TIMESTAMP
updated_at	TIMESTAMP

tenant_transactions

Journal des transactions financieres d'un tenant.

Colonne	Type	Contraintes	Description
id	UUID	PK
tenant_id	UUID	FK(tenants)	Tenant
transaction_type	ENUM	NOT NULL	invoice_created, signature_created, credit_note_created, balance_topup, invoice_submitted, balance_debit
entity_type	VARCHAR	NULLABLE	Type entite associee
entity_id	UUID	NULLABLE	ID entite
amount	DECIMAL(12,4)	NOT NULL	Montant
currency	VARCHAR(3)	DEFAULT 'EUR'	Devise
running_balance	DECIMAL(12,4)	NOT NULL	Solde courant
status	ENUM	NOT NULL	pending, completed, failed
metadata	JSONB	DEFAULT '{}'
created_at	TIMESTAMP

---

7. Tables Onboarding

onboarding_sessions

Sessions du parcours d'onboarding B2B.

Colonne	Type	Contraintes	Description
id	UUID	PK
partner_id	UUID	FK(partners)	Partenaire initiateur
external_reference	VARCHAR	NULLABLE	Reference partenaire
tenant_id	UUID	FK(tenants), NULLABLE	Tenant cree (apres completion)
redirect_url	VARCHAR	NULLABLE	URL retour partenaire
siret_verified_at	TIMESTAMP	NULLABLE	Date verification SIRET
vat_verified_at	TIMESTAMP	NULLABLE	Date verification TVA
documents_submitted_at	TIMESTAMP	NULLABLE	Date soumission documents
completed_at	TIMESTAMP	NULLABLE	Date completion
verification_code	VARCHAR	UNIQUE	Code verification echange
verification_code_expires_at	TIMESTAMP	NOT NULL	Expiration code
status	ENUM	NOT NULL	in_progress, documents_requested, under_review, approved, rejected
rejection_reason	TEXT	NULLABLE	Motif rejet
metadata	JSONB	DEFAULT '{}'
created_at	TIMESTAMP
updated_at	TIMESTAMP

kyb_documents

Documents KYB (Know Your Business) fournis lors de l'onboarding.

Colonne	Type	Contraintes	Description
id	UUID	PK
onboarding_session_id	UUID	FK(onboarding_sessions), NULLABLE	Session d'onboarding
tenant_id	UUID	FK(tenants), NULLABLE	Tenant proprietaire
document_type	ENUM	NOT NULL	identity, business_license, proof_of_address, financial_statement
file_path	VARCHAR	NOT NULL	Chemin S3
file_hash	VARCHAR	UNIQUE	Hash SHA256
mime_type	VARCHAR	NOT NULL	Type MIME
file_size	INT	NOT NULL	Taille en octets
status	ENUM	DEFAULT 'pending'	pending, approved, rejected
review_notes	TEXT	NULLABLE	Notes de review
reviewed_at	TIMESTAMP	NULLABLE	Date review
reviewed_by_user_id	UUID	FK(users), NULLABLE	Revieweur
metadata	JSONB	DEFAULT '{}'
created_at	TIMESTAMP
updated_at	TIMESTAMP

---

8. Tables Fiscales (NF525)

Ces tables implementent la conformite NF525 avec un grand livre immutable et une chaine de hachage cryptographique.

fiscal_entries

Grand livre fiscal immutable. Table centrale du module de conformite.

Colonne	Type	Contraintes	Description
id	UUID	PK
tenant_id	UUID	FK(tenants)	Tenant
sequence_number	INT	NOT NULL	Numero sequence (par tenant)
entry_type	ENUM	NOT NULL	invoice, credit_note, payment, closing, adjustment
entity_type	VARCHAR	NULLABLE	Type entite source
entity_id	UUID	NULLABLE	ID entite source
fiscal_date	DATE	NOT NULL	Date fiscale
data_snapshot	JSONB	NOT NULL	Snapshot complet de l'entite
data_hash	VARCHAR(64)	NOT NULL	SHA256 du snapshot
previous_hash	VARCHAR(64)	NOT NULL	Hash de l'entree precedente
chain_hash	VARCHAR(64)	NOT NULL	SHA256(data_hash + previous_hash + seq)
legal_status	ENUM	NOT NULL	donnee_fiscale, donnee_comptable, donnee_technique
environment	ENUM	NOT NULL	sandbox, production
actor_type	ENUM	NOT NULL	user, system, partner
actor_id	UUID	NULLABLE	ID acteur
ip_address	VARCHAR	NULLABLE	Adresse IP
user_agent	TEXT	NULLABLE	User agent
metadata	JSONB	DEFAULT '{}'
created_at	TIMESTAMP	NOT NULL	Horodatage (non modifiable)

Contrainte unique : (tenant_id, sequence_number)

Indexes : (tenant_id, sequence_number), (entity_type, entity_id), (tenant_id, fiscal_date)

Aucun updated_at : table append-only, jamais modifiee.

Triggers d'immutabilite : des triggers PostgreSQL empechent les UPDATE et DELETE.

fiscal_sequences

Compteurs de sequence atomiques par tenant.

Colonne	Type	Contraintes	Description
id	UUID	PK
tenant_id	UUID	FK(tenants), UNIQUE	Tenant
last_sequence_number	INT	NOT NULL	Dernier numero
last_sequence_date	TIMESTAMP	NOT NULL	Date dernier numero
lock_version	INT	DEFAULT 0	Verrouillage optimiste

fiscal_closings

Clotures periodiques (quotidienne, mensuelle, annuelle).

Colonne	Type	Contraintes	Description
id	UUID	PK
tenant_id	UUID	FK(tenants)	Tenant
closing_date	DATE	NOT NULL	Date de cloture
closing_type	ENUM	NOT NULL	daily, monthly, yearly
entries_count	INT	NOT NULL	Nombre d'ecritures
opening_balance	DECIMAL(12,4)	NOT NULL	Solde ouverture
closing_balance	DECIMAL(12,4)	NOT NULL	Solde cloture
total_operations	INT	NOT NULL	Total operations
fec_export_path	VARCHAR	NULLABLE	Chemin export FEC
status	ENUM	NOT NULL	pending, completed, verified, error
closed_at	TIMESTAMP	NOT NULL	Horodatage cloture
created_at	TIMESTAMP
updated_at	TIMESTAMP

Contrainte unique : (tenant_id, closing_date, closing_type)

fiscal_attestations

Attestations annuelles de conformite.

Colonne	Type	Contraintes	Description
id	UUID	PK
tenant_id	UUID	FK(tenants)	Tenant
year	INT	NOT NULL	Annee
attestation_type	ENUM	NOT NULL	annual, special
software_name	VARCHAR	NOT NULL	Nom logiciel
software_version	VARCHAR	NOT NULL	Version
editor_name	VARCHAR	NOT NULL	Nom editeur
editor_siret	VARCHAR(14)	NOT NULL	SIRET editeur
license_number	VARCHAR	NULLABLE	Numero licence
market_release_date	DATE	NOT NULL	Date mise sur marche
attestation_date	DATE	NOT NULL	Date attestation
signed_at	TIMESTAMP	NULLABLE	Date signature
pdf_path	VARCHAR	NULLABLE	Chemin PDF
created_at	TIMESTAMP
updated_at	TIMESTAMP

Contrainte unique : (tenant_id, year)

fiscal_anchors

Ancrages TSA RFC3161 pour horodatage legal.

Colonne	Type	Contraintes	Description
id	UUID	PK
tenant_id	UUID	FK(tenants)	Tenant
entry_id	UUID	FK(fiscal_entries)	Entree ancree
anchor_type	ENUM	NOT NULL	rfc3161_tsa, blockchain, none
anchor_timestamp	TIMESTAMP	NOT NULL	Horodatage TSA
anchor_reference	VARCHAR	NULLABLE	Reference ancrage
proof_path	VARCHAR	NULLABLE	Chemin preuve
status	ENUM	NOT NULL	pending, verified, failed
created_at	TIMESTAMP
updated_at	TIMESTAMP

fiscal_kill_switches

Interrupteur d'urgence pour les operations fiscales.

Colonne	Type	Contraintes	Description
id	UUID	PK
tenant_id	UUID	FK(tenants), UNIQUE	Tenant
status	ENUM	NOT NULL	active, inactive
activated_at	TIMESTAMP	NULLABLE	Date activation
deactivated_at	TIMESTAMP	NULLABLE	Date desactivation
activation_reason	TEXT	NULLABLE	Motif
activated_by_user_id	UUID	FK(users), NULLABLE	Utilisateur activateur
activated_by_ip	VARCHAR	NULLABLE	IP
metadata	JSONB	DEFAULT '{}'

fiscal_integrity_checks

Rapports de verification d'integrite de la chaine.

Colonne	Type	Contraintes	Description
id	UUID	PK
tenant_id	UUID	FK(tenants)	Tenant
check_date	DATE	NOT NULL	Date verification
entries_validated	INT	NOT NULL	Entrees validees
chains_verified	INT	NOT NULL	Chaines verifiees
anomalies_found	INT	DEFAULT 0	Anomalies detectees
status	ENUM	NOT NULL	passed, warning, failed
report_path	VARCHAR	NULLABLE	Chemin rapport
created_at	TIMESTAMP
updated_at	TIMESTAMP

fiscal_audit_log

Journal d'audit de conformite fiscale.

Colonne	Type	Contraintes	Description
id	UUID	PK
tenant_id	UUID	FK(tenants)	Tenant
event_type	ENUM	NOT NULL	modification_attempt, deletion_attempt, deletion_blocked, access_denied, chain_break
event_source	VARCHAR	NOT NULL	Source (ex: nf525_guard, fiscal_system)
target_table	VARCHAR	NOT NULL	Table cible
target_id	UUID	NULLABLE	ID cible
severity	ENUM	NOT NULL	info, warning, critical
actor_type	VARCHAR	NULLABLE	Type acteur
actor_id	UUID	NULLABLE	ID acteur
ip_address	VARCHAR	NULLABLE	IP
user_agent	TEXT	NULLABLE	User agent
details	JSONB	DEFAULT '{}'	Details evenement
created_at	TIMESTAMP	NOT NULL

Indexes : (tenant_id, created_at), (severity, created_at)

fiscal_rules

Regles de conformite configurables par tenant.

Colonne	Type	Contraintes	Description
id	UUID	PK
tenant_id	UUID	FK(tenants)	Tenant
rule_key	VARCHAR	NOT NULL	Cle unique par tenant
rule_name	VARCHAR	NOT NULL	Nom lisible
rule_type	ENUM	NOT NULL	alert, validation, correction
condition_json	JSONB	NOT NULL	Condition d'application
action_json	JSONB	NOT NULL	Action a executer
enabled	BOOLEAN	DEFAULT true	Active
applied_count	INT	DEFAULT 0	Nombre d'applications
last_applied_at	TIMESTAMP	NULLABLE	Derniere application
created_by_user_id	UUID	FK(users)	Createur
metadata	JSONB	DEFAULT '{}'
created_at	TIMESTAMP
updated_at	TIMESTAMP

Contrainte unique : (tenant_id, rule_key)

fiscal_rule_applications

Journal d'application des regles fiscales.

Colonne	Type	Contraintes	Description
id	UUID	PK
fiscal_rule_id	UUID	FK(fiscal_rules)	Regle appliquee
entry_id	UUID	FK(fiscal_entries), NULLABLE	Entree concernee
applied_at	TIMESTAMP	NOT NULL	Date application
result	ENUM	NOT NULL	passed, failed, warning
details	JSONB	DEFAULT '{}'	Details

---

9. Tables Webhooks

webhooks

Configuration des webhooks utilisateur.

Colonne	Type	Contraintes	Description
id	UUID	PK
user_id	UUID	FK(users)	Proprietaire
company_id	UUID	FK(companies), NULLABLE	Entreprise
url	VARCHAR	NOT NULL	URL endpoint
secret	VARCHAR	NOT NULL	Secret HMAC
events	JSONB	NOT NULL	Evenements souscrits
is_active	BOOLEAN	DEFAULT true	Actif
retries	INT	DEFAULT 3	Nombre de retries
max_retries	INT	DEFAULT 5	Maximum retries
backoff_multiplier	INT	DEFAULT 2	Multiplicateur backoff
created_at	TIMESTAMP
updated_at	TIMESTAMP

Evenements disponibles : invoice.created, invoice.submitted, signature.created, signature.completed, credit_note.created

webhook_logs

Journal des envois de webhooks.

Colonne	Type	Contraintes	Description
id	UUID	PK
webhook_id	UUID	FK(webhooks)	Webhook
event_name	VARCHAR	NOT NULL	Evenement
payload	JSONB	NOT NULL	Corps envoye
status	ENUM	NOT NULL	success, failed
status_code	INT	NULLABLE	Code HTTP
response	TEXT	NULLABLE	Corps reponse
attempt	INT	DEFAULT 1	Tentative
created_at	TIMESTAMP

processed_webhooks

Deduplication des webhooks entrants (OpenAPI, SuperPDP).

Colonne	Type	Contraintes	Description
id	UUID	PK
webhook_provider	ENUM	NOT NULL	openapi, superpdp
external_id	VARCHAR	NOT NULL	ID externe
status	VARCHAR	NOT NULL	Statut traitement
processed_at	TIMESTAMP	NULLABLE	Date traitement
metadata	JSONB	DEFAULT '{}'
created_at	TIMESTAMP

Contrainte unique : (webhook_provider, external_id)

---

10. Tables Billing

balances

Solde de credits d'un utilisateur dashboard.

Colonne	Type	Contraintes	Description
id	UUID	PK
user_id	UUID	FK(users), UNIQUE	Proprietaire
available_balance	DECIMAL(12,4)	DEFAULT 0	Solde disponible
reserved_balance	DECIMAL(12,4)	DEFAULT 0	Solde reserve
total_balance	DECIMAL(12,4)	DEFAULT 0	Solde total
currency	VARCHAR(3)	DEFAULT 'EUR'	Devise
last_updated_at	TIMESTAMP	NULLABLE	Derniere mise a jour
created_at	TIMESTAMP
updated_at	TIMESTAMP

transactions

Journal des transactions financieres (utilisateur dashboard).

Colonne	Type	Contraintes	Description
id	UUID	PK
user_id	UUID	FK(users)	Proprietaire
company_id	UUID	FK(companies), NULLABLE	Entreprise
entity_type	VARCHAR	NULLABLE	Type entite (morph)
entity_id	UUID	NULLABLE	ID entite
transaction_type	VARCHAR	NOT NULL	Type
amount	DECIMAL(12,4)	NOT NULL	Montant
currency	VARCHAR(3)	DEFAULT 'EUR'	Devise
status	ENUM	NOT NULL	pending, completed, failed
description	TEXT	NULLABLE	Description
reference	VARCHAR	NULLABLE	Reference
created_at	TIMESTAMP

---

11. Tables Integration

openapi_configs

Configuration OAuth2 pour l'integration OpenAPI.com.

Colonne	Type	Contraintes	Description
id	UUID	PK
user_id	UUID	FK(users)	Proprietaire
client_id	VARCHAR	NOT NULL	OAuth2 Client ID
client_secret	VARCHAR	NOT NULL	OAuth2 Client Secret (chiffre)
webhook_url	VARCHAR	NULLABLE	URL webhook OpenAPI
webhook_secret	VARCHAR	NULLABLE	Secret webhook
is_active	BOOLEAN	DEFAULT true	Actif
metadata	JSONB	DEFAULT '{}'
created_at	TIMESTAMP
updated_at	TIMESTAMP

openapi_tokens

Tokens OAuth2 pour OpenAPI.com.

Colonne	Type	Contraintes	Description
id	UUID	PK
openapi_config_id	UUID	FK(openapi_configs)	Configuration
access_token	TEXT	NOT NULL	Token d'acces (chiffre)
refresh_token	TEXT	NULLABLE	Token de rafraichissement
expires_at	TIMESTAMP	NOT NULL	Date expiration
scopes	VARCHAR	NULLABLE	Scopes accordes
created_at	TIMESTAMP
updated_at	TIMESTAMP

sync_cursors

Curseurs de synchronisation pour les integrations.

Colonne	Type	Contraintes	Description
id	UUID	PK
entity_type	VARCHAR	NOT NULL	Type entite synchronisee
last_synced_at	TIMESTAMP	NULLABLE	Derniere sync
cursor_position	VARCHAR	NULLABLE	Position curseur
status	ENUM	NOT NULL	Statut sync
metadata	JSONB	DEFAULT '{}'
created_at	TIMESTAMP
updated_at	TIMESTAMP

---

12. Tables MCP

mcp_sessions

Sessions du serveur MCP (Model Context Protocol) pour audit.

Colonne	Type	Contraintes	Description
id	UUID	PK
tenant_id	UUID	FK(tenants), NULLABLE	Tenant associe
session_id	VARCHAR	UNIQUE	ID session MCP
api_key_id	UUID	FK(api_keys), NULLABLE	Cle API utilisee
tools_accessed	JSONB	DEFAULT '[]'	Outils utilises
status	ENUM	NOT NULL	active, expired, closed
expires_at	TIMESTAMP	NOT NULL	Date expiration
metadata	JSONB	DEFAULT '{}'
created_at	TIMESTAMP
updated_at	TIMESTAMP

---

13. Tables Support

audit_trails

Piste d'audit generale pour toutes les modifications de modeles.

Colonne	Type	Contraintes	Description
id	UUID	PK
user_id	UUID	FK(users), NULLABLE	Auteur
action	VARCHAR	NOT NULL	Action (created, updated, deleted)
model_type	VARCHAR	NOT NULL	Type modele
model_id	UUID	NOT NULL	ID modele
old_values	JSONB	NULLABLE	Anciennes valeurs
new_values	JSONB	NULLABLE	Nouvelles valeurs
ip_address	VARCHAR	NULLABLE	IP
user_agent	TEXT	NULLABLE	User agent
created_at	TIMESTAMP

sms_logs

Journal des envois SMS OTP (BulkGate).

Colonne	Type	Contraintes	Description
id	UUID	PK
signer_id	UUID	FK(signers)	Signataire
phone	VARCHAR	NOT NULL	Numero telephone
otp_code	VARCHAR(6)	NOT NULL	Code OTP
sent_at	TIMESTAMP	NOT NULL	Date envoi
verified_at	TIMESTAMP	NULLABLE	Date verification
attempt_count	INT	DEFAULT 1	Nombre tentatives
created_at	TIMESTAMP

rate_limit_overrides

Surcharges de rate limiting par utilisateur ou entreprise.

Colonne	Type	Contraintes	Description
id	UUID	PK
user_id	UUID	FK(users), NULLABLE	Utilisateur
company_id	UUID	FK(companies), NULLABLE	Entreprise
limit_type	VARCHAR	NOT NULL	Type de limite
requests_per_minute	INT	NOT NULL	Requetes par minute
expires_at	TIMESTAMP	NULLABLE	Date expiration
created_at	TIMESTAMP
updated_at	TIMESTAMP

---

14. Migrations

Les migrations sont ordonnees chronologiquement. Les migrations critiques sont marquees avec le symbole *.

Phase 1 -- Core (2026-01-11)

Migration	Description
2026_01_11_000001_create_users_table	Table utilisateurs, roles, auth
2026_01_11_000002_create_companies_table	Table entreprises, SIRET, KYC
2026_01_11_000003_create_api_keys_table	Cles API (hash, prefix, env)
2026_01_11_000004_create_billing_tables	Tables balances et transactions
2026_01_11_000005_create_invoices_table	Factures et lignes de facture
2026_01_11_000006_create_signatures_table	Signatures et signataires
2026_01_11_000007_create_webhooks_table	Webhooks et logs
2026_01_11_000008_create_support_tables	Audit trails, SMS logs, rate limits
2026_01_11_223440_create_permission_tables	* Tables Spatie Permission (roles/permissions)

Phase 2 -- Corrections (2026-01-12)

Migration	Description
2026_01_12_155701_make_webhook_company_id_nullable	company_id nullable sur webhooks

Phase 3 -- Avoirs (2026-01-19)

Migration	Description
2026_01_19_000001_create_credit_notes_table	Table des avoirs
2026_01_19_000002_create_credit_note_items_table	Lignes d'avoirs

Phase 4 -- Multi-Tenant (2026-01-22)

Migration	Description
2026_01_22_000001_create_processed_webhooks_table	Deduplication webhooks entrants
2026_01_22_210000_create_tenants_table	* Table tenants (coeur multi-tenant)
2026_01_22_210001_create_sub_tenants_table	* Table sub-tenants
2026_01_22_210002_add_tenant_columns_to_companies	* Ajout tenant_id/sub_tenant_id sur companies
2026_01_22_210003_create_kyb_documents_table	Documents KYB
2026_01_22_210004_create_tenant_invoices_table	Facturation tenant consolidee
2026_01_22_210005_create_tenant_transactions_table	Transactions tenant

Phase 5 -- Onboarding & Integrations (2026-01-24)

Migration	Description
2026_01_24_000001_create_partners_table	Table partenaires
2026_01_24_000001c_add_partner_and_onboarding_fields_to_tenants_table	* Champs partenaire/onboarding sur tenants
2026_01_24_000002_create_onboarding_sessions_table	Sessions d'onboarding
2026_01_24_000003_add_onboarding_session_id_to_kyb_documents	Lien session <-> documents
2026_01_24_100001_create_openapi_configs_table	Configuration OpenAPI OAuth2
2026_01_24_100002_create_openapi_tokens_table	Tokens OpenAPI
2026_01_24_150001_add_paid_status_to_invoices	Ajout statut paiement sur factures
2026_01_24_150002_create_sync_cursors_table	Curseurs de synchronisation
2026_01_24_214500_create_mcp_sessions_table	Sessions MCP
2026_01_24_add_incoming_invoice_fields	Champs factures entrantes

Phase 6 -- Extensions (2026-01-25/26)

Migration	Description
2026_01_25_000001_add_tenant_fields_to_credit_notes_table	* tenant_id/sub_tenant_id sur credit_notes
2026_01_26_000001_create_invoice_sequences_table	* Sequences numerotation factures
2026_01_26_151113_make_user_id_nullable_for_tenant_entities	user_id nullable (entites tenant sans utilisateur)

Phase 7 -- Fiscal NF525 (2026-02-10)

Migration	Description
2026_02_10_000001_create_fiscal_entries_table	* Grand livre fiscal immutable
2026_02_10_000002_create_fiscal_sequences_table	* Sequences atomiques fiscales
2026_02_10_000003_create_fiscal_closings_table	* Clotures periodiques
2026_02_10_000004_create_fiscal_attestations_table	Attestations annuelles
2026_02_10_000005_add_fiscal_immutability_triggers	* Triggers PostgreSQL d'immutabilite
2026_02_10_000006_add_legal_status_to_invoices_and_credit_notes	* Ajout legal_status (donnee_fiscale/comptable)
2026_02_10_000007_create_fiscal_kill_switches_table	Kill switch d'urgence
2026_02_10_000008_create_fiscal_integrity_checks_table	Rapports d'integrite
2026_02_10_000009_create_fiscal_anchors_table	Ancrages TSA RFC3161
2026_02_10_000010_create_fiscal_rules_table	Regles de conformite
2026_02_10_000011_create_fiscal_rule_applications_table	Journal application regles
2026_02_10_000012_add_anchor_reference_to_fiscal_anchors_table	Ajout reference ancrage
2026_02_10_000012_fix_fiscal_isca_compliance	* Corrections conformite ISCA
2026_02_10_000013_create_fiscal_audit_log_table	* Journal d'audit fiscal
2026_02_10_100001_remove_soft_deletes_from_credit_notes	* Suppression soft deletes (NF525)

Phase 8 -- MCP Multi-Tenant (2026-02-12)

Migration	Description
2026_02_12_000001_add_tenant_id_to_mcp_sessions	Lien tenant <-> sessions MCP

Phase 9 -- Renforcement Fiscal (2026-03-01)

Migration	Description
2026_03_01_000001_fix_remaining_fiscal_fk_to_restrict_on_delete	* FK RESTRICT sur tables fiscales
2026_03_01_000002_add_immutability_triggers_attestations_anchors	* Triggers sur attestations et anchors
2026_03_01_000003_add_rls_policies_fiscal_tables	* Policies RLS PostgreSQL sur tables fiscales