schema

Database: postgresql Tables: 14 Generated: 2026-04-23T09:01:05.675Z

Table: user

Column	Type	Nullable	PK	Unique	Default
id	VARCHAR	No	Yes
firstName	VARCHAR	Yes
lastName	VARCHAR	Yes
email	VARCHAR	No
emailVerified	TIMESTAMP	Yes
phone	VARCHAR	Yes
isAdmin	BOOLEAN	No

Table: declaration

Column	Type	Nullable	PK	Unique	Default
id	VARCHAR	No	Yes
siren	VARCHAR	No
year	INTEGER	No
declarantId	VARCHAR	No
totalWomen	INTEGER	Yes
totalMen	INTEGER	Yes
remunerationScore	INTEGER	Yes
variableRemunerationScore	INTEGER	Yes
quartileScore	INTEGER	Yes
categoryScore	INTEGER	Yes
compliancePath	VARCHAR	Yes
indicatorAAnnualWomen	NUMERIC	Yes
indicatorAAnnualMen	NUMERIC	Yes
indicatorAHourlyWomen	NUMERIC	Yes
indicatorAHourlyMen	NUMERIC	Yes
indicatorBAnnualWomen	NUMERIC	Yes
indicatorBAnnualMen	NUMERIC	Yes
indicatorBHourlyWomen	NUMERIC	Yes
indicatorBHourlyMen	NUMERIC	Yes
indicatorCAnnualWomen	NUMERIC	Yes
indicatorCAnnualMen	NUMERIC	Yes
indicatorCHourlyWomen	NUMERIC	Yes
indicatorCHourlyMen	NUMERIC	Yes
indicatorDAnnualWomen	NUMERIC	Yes
indicatorDAnnualMen	NUMERIC	Yes
indicatorDHourlyWomen	NUMERIC	Yes
indicatorDHourlyMen	NUMERIC	Yes
indicatorEWomen	NUMERIC	Yes
indicatorEMen	NUMERIC	Yes
indicatorFAnnualThreshold1	NUMERIC	Yes
indicatorFAnnualThreshold2	NUMERIC	Yes
indicatorFAnnualThreshold3	NUMERIC	Yes
indicatorFAnnualThreshold4	NUMERIC	Yes
indicatorFAnnualWomen1	INTEGER	Yes
indicatorFAnnualWomen2	INTEGER	Yes
indicatorFAnnualWomen3	INTEGER	Yes
indicatorFAnnualWomen4	INTEGER	Yes
indicatorFAnnualMen1	INTEGER	Yes
indicatorFAnnualMen2	INTEGER	Yes
indicatorFAnnualMen3	INTEGER	Yes
indicatorFAnnualMen4	INTEGER	Yes
indicatorFHourlyThreshold1	NUMERIC	Yes
indicatorFHourlyThreshold2	NUMERIC	Yes
indicatorFHourlyThreshold3	NUMERIC	Yes
indicatorFHourlyThreshold4	NUMERIC	Yes
indicatorFHourlyWomen1	INTEGER	Yes
indicatorFHourlyWomen2	INTEGER	Yes
indicatorFHourlyWomen3	INTEGER	Yes
indicatorFHourlyWomen4	INTEGER	Yes
indicatorFHourlyMen1	INTEGER	Yes
indicatorFHourlyMen2	INTEGER	Yes
indicatorFHourlyMen3	INTEGER	Yes
indicatorFHourlyMen4	INTEGER	Yes
currentStep	INTEGER	Yes			0
status	VARCHAR	Yes			draft
secondDeclarationStep	INTEGER	Yes
secondDeclarationStatus	VARCHAR	Yes
secondDeclReferencePeriodStart	VARCHAR	Yes
secondDeclReferencePeriodEnd	VARCHAR	Yes
complianceCompletedAt	TIMESTAMP	Yes
cseOpinionCompletedAt	TIMESTAMP	Yes
submittedAt	TIMESTAMP	Yes
createdAt	TIMESTAMP	Yes
updatedAt	TIMESTAMP	Yes

Indexes

• declaration_siren_year_idx (siren, year) UNIQUE
• declaration_declarant_idx (declarantId)
• declaration_submitted_at_idx (submittedAt)

Foreign Keys

• siren -> company.siren
• declarantId -> user.id

Table: job_category

Column	Type	Nullable	PK	Unique	Default
id	VARCHAR	No	Yes
declarationId	VARCHAR	No
categoryIndex	INTEGER	No
name	VARCHAR	No
detail	VARCHAR	Yes
source	VARCHAR	No

Indexes

• job_category_declaration_index_idx (declarationId, categoryIndex) UNIQUE

Foreign Keys

• declarationId -> declaration.id

Table: employee_category

Column	Type	Nullable	PK	Unique	Default
id	VARCHAR	No	Yes
jobCategoryId	VARCHAR	No
declarationType	DECLARATION_TYPE	No
womenCount	INTEGER	Yes
menCount	INTEGER	Yes
annualBaseWomen	NUMERIC	Yes
annualBaseMen	NUMERIC	Yes
annualVariableWomen	NUMERIC	Yes
annualVariableMen	NUMERIC	Yes
hourlyBaseWomen	NUMERIC	Yes
hourlyBaseMen	NUMERIC	Yes
hourlyVariableWomen	NUMERIC	Yes
hourlyVariableMen	NUMERIC	Yes
createdAt	TIMESTAMP	Yes
updatedAt	TIMESTAMP	Yes

Indexes

• employee_category_job_type_idx (jobCategoryId, declarationType) UNIQUE

Foreign Keys

• jobCategoryId -> job_category.id

Table: gip_mds_data

Column	Type	Nullable	PK	Unique	Default
siren	VARCHAR	No	Yes
year	INTEGER	No	Yes
importedAt	TIMESTAMP	Yes
periodStart	DATE	Yes
periodEnd	DATE	Yes
workforceEma	NUMERIC	Yes
menCountAnnualGlobal	NUMERIC	Yes
womenCountAnnualGlobal	NUMERIC	Yes
menCountHourlyGlobal	NUMERIC	Yes
womenCountHourlyGlobal	NUMERIC	Yes
menCountAnnualVariable	NUMERIC	Yes
womenCountAnnualVariable	NUMERIC	Yes
globalAnnualMeanGap	NUMERIC	Yes
globalAnnualMeanWomen	NUMERIC	Yes
globalAnnualMeanMen	NUMERIC	Yes
globalHourlyMeanGap	NUMERIC	Yes
globalHourlyMeanWomen	NUMERIC	Yes
globalHourlyMeanMen	NUMERIC	Yes
variableAnnualMeanGap	NUMERIC	Yes
variableAnnualMeanWomen	NUMERIC	Yes
variableAnnualMeanMen	NUMERIC	Yes
variableHourlyMeanGap	NUMERIC	Yes
variableHourlyMeanWomen	NUMERIC	Yes
variableHourlyMeanMen	NUMERIC	Yes
globalAnnualMedianGap	NUMERIC	Yes
globalAnnualMedianWomen	NUMERIC	Yes
globalAnnualMedianMen	NUMERIC	Yes
globalHourlyMedianGap	NUMERIC	Yes
globalHourlyMedianWomen	NUMERIC	Yes
globalHourlyMedianMen	NUMERIC	Yes
variableAnnualMedianGap	NUMERIC	Yes
variableAnnualMedianWomen	NUMERIC	Yes
variableAnnualMedianMen	NUMERIC	Yes
variableHourlyMedianGap	NUMERIC	Yes
variableHourlyMedianWomen	NUMERIC	Yes
variableHourlyMedianMen	NUMERIC	Yes
variableProportionWomen	NUMERIC	Yes
variableProportionMen	NUMERIC	Yes
annualQuartileThreshold1	NUMERIC	Yes
annualQuartileThreshold2	NUMERIC	Yes
annualQuartileThreshold3	NUMERIC	Yes
annualQuartileThreshold4	NUMERIC	Yes
annualQuartile1ProportionWomen	NUMERIC	Yes
annualQuartile2ProportionWomen	NUMERIC	Yes
annualQuartile3ProportionWomen	NUMERIC	Yes
annualQuartile4ProportionWomen	NUMERIC	Yes
annualQuartile1ProportionMen	NUMERIC	Yes
annualQuartile2ProportionMen	NUMERIC	Yes
annualQuartile3ProportionMen	NUMERIC	Yes
annualQuartile4ProportionMen	NUMERIC	Yes
hourlyQuartileThreshold1	NUMERIC	Yes
hourlyQuartileThreshold2	NUMERIC	Yes
hourlyQuartileThreshold3	NUMERIC	Yes
hourlyQuartileThreshold4	NUMERIC	Yes
hourlyQuartile1ProportionWomen	NUMERIC	Yes
hourlyQuartile2ProportionWomen	NUMERIC	Yes
hourlyQuartile3ProportionWomen	NUMERIC	Yes
hourlyQuartile4ProportionWomen	NUMERIC	Yes
hourlyQuartile1ProportionMen	NUMERIC	Yes
hourlyQuartile2ProportionMen	NUMERIC	Yes
hourlyQuartile3ProportionMen	NUMERIC	Yes
hourlyQuartile4ProportionMen	NUMERIC	Yes
confidenceIndex	NUMERIC	Yes
confidenceExoticContracts	NUMERIC	Yes
confidenceUnitMeasure	NUMERIC	Yes
confidenceSuspensionRatio	NUMERIC	Yes
confidenceLongSuspensions	NUMERIC	Yes
confidenceNoEndSuspensions	NUMERIC	Yes
confidenceSickLeaveRatio	NUMERIC	Yes
confidenceLongSickLeave	NUMERIC	Yes
confidenceNoSickLeave	NUMERIC	Yes
confidenceQuota250	NUMERIC	Yes
confidenceQuota0	NUMERIC	Yes
confidenceMultiYear	NUMERIC	Yes
confidenceFpRatio	NUMERIC	Yes
confidenceExtremeRemuneration	NUMERIC	Yes
confidenceExtremeRate	NUMERIC	Yes

Indexes

• gip_mds_data_siren_idx (siren)

Foreign Keys

• siren -> company.siren

Table: company

Column	Type	Nullable	PK	Unique	Default
siren	VARCHAR	No	Yes
name	VARCHAR	No
address	VARCHAR	Yes
nafCode	VARCHAR	Yes
workforce	INTEGER	Yes
hasCse	BOOLEAN	Yes
createdAt	TIMESTAMP	Yes
updatedAt	TIMESTAMP	Yes

Table: user_company

Column	Type	Nullable	PK	Unique	Default
userId	VARCHAR	No	Yes
siren	VARCHAR	No	Yes
createdAt	TIMESTAMP	Yes

Indexes

• user_company_user_id_idx (userId)

Foreign Keys

• userId -> user.id
• siren -> company.siren

Table: cse_opinion

Column	Type	Nullable	PK	Unique	Default
id	VARCHAR	No	Yes
declarationId	VARCHAR	No
declarationNumber	INTEGER	No
type	VARCHAR	No
gapConsulted	BOOLEAN	Yes
opinion	VARCHAR	Yes
opinionDate	VARCHAR	Yes
createdAt	TIMESTAMP	Yes
updatedAt	TIMESTAMP	Yes

Indexes

• cse_opinion_decl_number_type_idx (declarationId, declarationNumber, type) UNIQUE
• cse_opinion_declaration_idx (declarationId)

Foreign Keys

• declarationId -> declaration.id

Table: file

Column	Type	Nullable	PK	Unique	Default
id	VARCHAR	No	Yes
declarationId	VARCHAR	No
fileName	VARCHAR	No
filePath	VARCHAR	No
uploadedAt	TIMESTAMP	No
createdAt	TIMESTAMP	Yes
type	FILE_TYPE	No

Indexes

• file_declaration_idx (declarationId)

Foreign Keys

• declarationId -> declaration.id

Table: campaign_deadline

Column	Type	Nullable	PK	Unique	Default
year	INTEGER	No	Yes
gipPublicationDate	DATE	Yes
campaignStartDate	DATE	Yes
decl1ModificationDeadline	DATE	No
decl1JustificationDeadline	DATE	No
decl1JointEvaluationDeadline	DATE	No
decl2ModificationDeadline	DATE	No
decl2JustificationDeadline	DATE	No
decl2JointEvaluationDeadline	DATE	No

Table: global_setting

Column	Type	Nullable	PK	Unique	Default
id	INTEGER	No	Yes		1
activeCampaignYear	INTEGER	Yes
updatedAt	TIMESTAMP	No
updatedBy	VARCHAR	Yes

Foreign Keys

• updatedBy -> user.id

Table: admin_impersonation_event

Column	Type	Nullable	PK	Unique	Default
id	VARCHAR	No	Yes
adminUserId	VARCHAR	No
siren	VARCHAR	No
startedAt	TIMESTAMP	No
stoppedAt	TIMESTAMP	Yes

Indexes

• admin_impersonation_event_admin_started_idx (adminUserId, startedAt)

Foreign Keys

• adminUserId -> user.id
• siren -> company.siren

Table: referent

Column	Type	Nullable	PK	Unique	Default
id	VARCHAR	No	Yes
region	VARCHAR	No
county	VARCHAR	Yes
name	VARCHAR	No
type	REFERENT_TYPE	No
value	VARCHAR	No
principal	BOOLEAN	No
substituteName	VARCHAR	Yes
substituteEmail	VARCHAR	Yes
createdAt	TIMESTAMP	Yes
updatedAt	TIMESTAMP	Yes

Indexes

• referent_region_idx (region)

Table: export

Column	Type	Nullable	PK	Unique	Default
id	VARCHAR	No	Yes
year	INTEGER	No
version	VARCHAR	No			v1
fileName	VARCHAR	No
s3Key	VARCHAR	No
rowCount	INTEGER	No
createdAt	TIMESTAMP	Yes

Indexes

• export_year_version_idx (year, version) UNIQUE

Entity Relationship Diagram

erDiagram
    user {
        VARCHAR id "PK"
        VARCHAR firstName
        VARCHAR lastName
        VARCHAR email
        TIMESTAMP emailVerified
        VARCHAR phone
        BOOLEAN isAdmin
    }
    declaration {
        VARCHAR id "PK"
        VARCHAR siren "FK"
        INTEGER year
        VARCHAR declarantId "FK"
        INTEGER totalWomen
        INTEGER totalMen
        INTEGER remunerationScore
        INTEGER variableRemunerationScore
        INTEGER quartileScore
        INTEGER categoryScore
        VARCHAR compliancePath
        NUMERIC indicatorAAnnualWomen
        NUMERIC indicatorAAnnualMen
        NUMERIC indicatorAHourlyWomen
        NUMERIC indicatorAHourlyMen
        NUMERIC indicatorBAnnualWomen
        NUMERIC indicatorBAnnualMen
        NUMERIC indicatorBHourlyWomen
        NUMERIC indicatorBHourlyMen
        NUMERIC indicatorCAnnualWomen
        NUMERIC indicatorCAnnualMen
        NUMERIC indicatorCHourlyWomen
        NUMERIC indicatorCHourlyMen
        NUMERIC indicatorDAnnualWomen
        NUMERIC indicatorDAnnualMen
        NUMERIC indicatorDHourlyWomen
        NUMERIC indicatorDHourlyMen
        NUMERIC indicatorEWomen
        NUMERIC indicatorEMen
        NUMERIC indicatorFAnnualThreshold1
        NUMERIC indicatorFAnnualThreshold2
        NUMERIC indicatorFAnnualThreshold3
        NUMERIC indicatorFAnnualThreshold4
        INTEGER indicatorFAnnualWomen1
        INTEGER indicatorFAnnualWomen2
        INTEGER indicatorFAnnualWomen3
        INTEGER indicatorFAnnualWomen4
        INTEGER indicatorFAnnualMen1
        INTEGER indicatorFAnnualMen2
        INTEGER indicatorFAnnualMen3
        INTEGER indicatorFAnnualMen4
        NUMERIC indicatorFHourlyThreshold1
        NUMERIC indicatorFHourlyThreshold2
        NUMERIC indicatorFHourlyThreshold3
        NUMERIC indicatorFHourlyThreshold4
        INTEGER indicatorFHourlyWomen1
        INTEGER indicatorFHourlyWomen2
        INTEGER indicatorFHourlyWomen3
        INTEGER indicatorFHourlyWomen4
        INTEGER indicatorFHourlyMen1
        INTEGER indicatorFHourlyMen2
        INTEGER indicatorFHourlyMen3
        INTEGER indicatorFHourlyMen4
        INTEGER currentStep
        VARCHAR status
        INTEGER secondDeclarationStep
        VARCHAR secondDeclarationStatus
        VARCHAR secondDeclReferencePeriodStart
        VARCHAR secondDeclReferencePeriodEnd
        TIMESTAMP complianceCompletedAt
        TIMESTAMP cseOpinionCompletedAt
        TIMESTAMP submittedAt
        TIMESTAMP createdAt
        TIMESTAMP updatedAt
    }
    job_category {
        VARCHAR id "PK"
        VARCHAR declarationId "FK"
        INTEGER categoryIndex
        VARCHAR name
        VARCHAR detail
        VARCHAR source
    }
    employee_category {
        VARCHAR id "PK"
        VARCHAR jobCategoryId "FK"
        DECLARATION_TYPE declarationType
        INTEGER womenCount
        INTEGER menCount
        NUMERIC annualBaseWomen
        NUMERIC annualBaseMen
        NUMERIC annualVariableWomen
        NUMERIC annualVariableMen
        NUMERIC hourlyBaseWomen
        NUMERIC hourlyBaseMen
        NUMERIC hourlyVariableWomen
        NUMERIC hourlyVariableMen
        TIMESTAMP createdAt
        TIMESTAMP updatedAt
    }
    gip_mds_data {
        VARCHAR siren "PK,FK"
        INTEGER year "PK"
        TIMESTAMP importedAt
        DATE periodStart
        DATE periodEnd
        NUMERIC workforceEma
        NUMERIC menCountAnnualGlobal
        NUMERIC womenCountAnnualGlobal
        NUMERIC menCountHourlyGlobal
        NUMERIC womenCountHourlyGlobal
        NUMERIC menCountAnnualVariable
        NUMERIC womenCountAnnualVariable
        NUMERIC globalAnnualMeanGap
        NUMERIC globalAnnualMeanWomen
        NUMERIC globalAnnualMeanMen
        NUMERIC globalHourlyMeanGap
        NUMERIC globalHourlyMeanWomen
        NUMERIC globalHourlyMeanMen
        NUMERIC variableAnnualMeanGap
        NUMERIC variableAnnualMeanWomen
        NUMERIC variableAnnualMeanMen
        NUMERIC variableHourlyMeanGap
        NUMERIC variableHourlyMeanWomen
        NUMERIC variableHourlyMeanMen
        NUMERIC globalAnnualMedianGap
        NUMERIC globalAnnualMedianWomen
        NUMERIC globalAnnualMedianMen
        NUMERIC globalHourlyMedianGap
        NUMERIC globalHourlyMedianWomen
        NUMERIC globalHourlyMedianMen
        NUMERIC variableAnnualMedianGap
        NUMERIC variableAnnualMedianWomen
        NUMERIC variableAnnualMedianMen
        NUMERIC variableHourlyMedianGap
        NUMERIC variableHourlyMedianWomen
        NUMERIC variableHourlyMedianMen
        NUMERIC variableProportionWomen
        NUMERIC variableProportionMen
        NUMERIC annualQuartileThreshold1
        NUMERIC annualQuartileThreshold2
        NUMERIC annualQuartileThreshold3
        NUMERIC annualQuartileThreshold4
        NUMERIC annualQuartile1ProportionWomen
        NUMERIC annualQuartile2ProportionWomen
        NUMERIC annualQuartile3ProportionWomen
        NUMERIC annualQuartile4ProportionWomen
        NUMERIC annualQuartile1ProportionMen
        NUMERIC annualQuartile2ProportionMen
        NUMERIC annualQuartile3ProportionMen
        NUMERIC annualQuartile4ProportionMen
        NUMERIC hourlyQuartileThreshold1
        NUMERIC hourlyQuartileThreshold2
        NUMERIC hourlyQuartileThreshold3
        NUMERIC hourlyQuartileThreshold4
        NUMERIC hourlyQuartile1ProportionWomen
        NUMERIC hourlyQuartile2ProportionWomen
        NUMERIC hourlyQuartile3ProportionWomen
        NUMERIC hourlyQuartile4ProportionWomen
        NUMERIC hourlyQuartile1ProportionMen
        NUMERIC hourlyQuartile2ProportionMen
        NUMERIC hourlyQuartile3ProportionMen
        NUMERIC hourlyQuartile4ProportionMen
        NUMERIC confidenceIndex
        NUMERIC confidenceExoticContracts
        NUMERIC confidenceUnitMeasure
        NUMERIC confidenceSuspensionRatio
        NUMERIC confidenceLongSuspensions
        NUMERIC confidenceNoEndSuspensions
        NUMERIC confidenceSickLeaveRatio
        NUMERIC confidenceLongSickLeave
        NUMERIC confidenceNoSickLeave
        NUMERIC confidenceQuota250
        NUMERIC confidenceQuota0
        NUMERIC confidenceMultiYear
        NUMERIC confidenceFpRatio
        NUMERIC confidenceExtremeRemuneration
        NUMERIC confidenceExtremeRate
    }
    company {
        VARCHAR siren "PK"
        VARCHAR name
        VARCHAR address
        VARCHAR nafCode
        INTEGER workforce
        BOOLEAN hasCse
        TIMESTAMP createdAt
        TIMESTAMP updatedAt
    }
    user_company {
        VARCHAR userId "PK,FK"
        VARCHAR siren "PK,FK"
        TIMESTAMP createdAt
    }
    cse_opinion {
        VARCHAR id "PK"
        VARCHAR declarationId "FK"
        INTEGER declarationNumber
        VARCHAR type
        BOOLEAN gapConsulted
        VARCHAR opinion
        VARCHAR opinionDate
        TIMESTAMP createdAt
        TIMESTAMP updatedAt
    }
    file {
        VARCHAR id "PK"
        VARCHAR declarationId "FK"
        VARCHAR fileName
        VARCHAR filePath
        TIMESTAMP uploadedAt
        TIMESTAMP createdAt
        FILE_TYPE type
    }
    campaign_deadline {
        INTEGER year "PK"
        DATE gipPublicationDate
        DATE campaignStartDate
        DATE decl1ModificationDeadline
        DATE decl1JustificationDeadline
        DATE decl1JointEvaluationDeadline
        DATE decl2ModificationDeadline
        DATE decl2JustificationDeadline
        DATE decl2JointEvaluationDeadline
    }
    global_setting {
        INTEGER id "PK"
        INTEGER activeCampaignYear
        TIMESTAMP updatedAt
        VARCHAR updatedBy "FK"
    }
    admin_impersonation_event {
        VARCHAR id "PK"
        VARCHAR adminUserId "FK"
        VARCHAR siren "FK"
        TIMESTAMP startedAt
        TIMESTAMP stoppedAt
    }
    referent {
        VARCHAR id "PK"
        VARCHAR region
        VARCHAR county
        VARCHAR name
        REFERENT_TYPE type
        VARCHAR value
        BOOLEAN principal
        VARCHAR substituteName
        VARCHAR substituteEmail
        TIMESTAMP createdAt
        TIMESTAMP updatedAt
    }
    export {
        VARCHAR id "PK"
        INTEGER year
        VARCHAR version
        VARCHAR fileName
        VARCHAR s3Key
        INTEGER rowCount
        TIMESTAMP createdAt
    }
    company ||--o{ declaration : "siren to siren"
    user ||--o{ declaration : "declarantId to id"
    declaration ||--o{ job_category : "declarationId to id"
    job_category ||--o{ employee_category : "jobCategoryId to id"
    company ||--o{ gip_mds_data : "siren to siren"
    user ||--o{ user_company : "userId to id"
    company ||--o{ user_company : "siren to siren"
    declaration ||--o{ cse_opinion : "declarationId to id"
    declaration ||--o{ file : "declarationId to id"
    user ||--o{ global_setting : "updatedBy to id"
    user ||--o{ admin_impersonation_event : "adminUserId to id"
    company ||--o{ admin_impersonation_event : "siren to siren"

Relationships

• declaration.siren -> company.siren (one-to-many)
• declaration.declarantId -> user.id (one-to-many)
• job_category.declarationId -> declaration.id (one-to-many)
• employee_category.jobCategoryId -> job_category.id (one-to-many)
• gip_mds_data.siren -> company.siren (one-to-many)
• user_company.userId -> user.id (one-to-many)
• user_company.siren -> company.siren (one-to-many)
• cse_opinion.declarationId -> declaration.id (one-to-many)
• file.declarationId -> declaration.id (one-to-many)
• global_setting.updatedBy -> user.id (one-to-many)
• admin_impersonation_event.adminUserId -> user.id (one-to-many)
• admin_impersonation_event.siren -> company.siren (one-to-many)