Database

PostgreSQL >= 15. Migrations managed by sqlx (server/migrations/).

Entity Groups

graph TD
    subgraph Core
        OFFICES[offices]
        USERS[users]
        USER_OFFICES[user_offices]
    end

    subgraph Cases
        CASES[cases]
        CASE_PARTICIPANTS[case_participants]
        CASE_CLAIMS[case_claims]
        CASE_TITLES[case_titles]
        CASE_INTERESTS[case_interests]
        CASE_EVENTS[case_events]
    end

    subgraph Participants
        PARTICIPANTS[participants]
        PART_ADDRESSES[participant_addresses]
        PART_PHONES[participant_phones]
        PART_EMAILS[participant_emails]
    end

    subgraph Deeds
        DEEDS[deeds]
        DEED_RECIPIENTS[deed_recipients]
    end

    subgraph Templates
        TEMPLATES[templates]
        DOCUMENTS[documents]
    end

    subgraph Reference
        CASE_STATUSES[case_statuses]
        CLAIM_TYPES[claim_types]
        DEED_TYPES[deed_types]
        TITLE_TYPES[title_types]
        CIVILITIES[civilities]
        PARTICIPANT_TYPES[participant_types]
    end

    CASES --> OFFICES
    CASES --> CASE_STATUSES
    CASES --> USERS
    CASE_PARTICIPANTS --> CASES
    CASE_PARTICIPANTS --> PARTICIPANTS
    CASE_CLAIMS --> CASES
    DEEDS --> CASES
    DEED_RECIPIENTS --> DEEDS
    DOCUMENTS --> CASES
    DOCUMENTS --> TEMPLATES

Conventions

Naming

• Table names: snake_case, plural (cases, participants, case_claims)
• Column names: snake_case (created_at, office_id)
• Foreign keys: {referenced_table_singular}_id (office_id, case_id)
• Junction tables: {table1}_{table2} (case_participants, user_offices)

Standard Columns

Every table has:

Column	Type	Purpose
id	UUID PRIMARY KEY	UUID v7 (time-ordered)
created_at	TIMESTAMPTZ NOT NULL DEFAULT NOW()	Creation timestamp
updated_at	TIMESTAMPTZ NOT NULL DEFAULT NOW()	Last modification

Mutable tables also have:

Column	Type	Purpose
version	INTEGER NOT NULL DEFAULT 0	Optimistic locking

Soft-deletable tables also have:

Column	Type	Purpose
deleted_at	TIMESTAMPTZ	Soft delete timestamp

Enum Types

PostgreSQL custom enum types are used for constrained values:

Enum	Values
person_type	individual, company, court
person_category	individual, company, court
procedure_type	signification, saisie_attribution, saisie_vente, constat, autre
deed_status	draft, validated, signed, cancelled
event_type	case_created, status_changed, deed_created, payment_received, manual_entry
delivery_mode	personne, domicile, etude, mairie, parquet
address_type	current, previous, professional
phone_type	mobile, landline, fax

Monetary Values

• case_claims.amount: DECIMAL(15,2) — exact precision
• cases.principal_amount: BIGINT — stored in cents (15000.00€ = 1500000)
• case_interests.base_amount: BIGINT — stored in cents

Migrations

Located in server/migrations/. Files are numbered sequentially: 001_initial_schema.sql, 002_..., etc.

Run migrations:

cd server && sqlx migrate run

Check migration status:

cd server && sqlx migrate info

Never modify existing migrations

Once a migration is applied, it must never be changed. Create a new migration for schema modifications.