hr.employment_relationships

Schema: hr
English table: hr.employment_relationships
Italian original table: hr.rapporti
Description: Employment relationships (employment periods/contracts) linked to a person and a company.

Overview

• Columns: 26
• Primary key: id
• Outgoing foreign keys: 6
• Incoming foreign keys: 54
• Indexes: 12

Columns

English column	Italian original column	Type	Null	Default / Generated	Description
id	id	integer	NO		Surrogate primary key.
er_id	rap_id	integer	NO		Logical employment relationship identifier (unique per tenant).
er_person_id	rap_ana_id	integer	NO		Person identifier (references person registry).
er_start_date	rap_dalla_data	date	NO		Employment start date.
er_end_date	rap_alla_data	date	NO		Employment end date.
er_relationship_type_id	rap_tipo	integer	NO		Employment relationship type (lookup).
er_contract_id	rap_con_id	integer	NO		Contract identifier (implementation-specific).
er_subject_id	rap_sog_id	integer	YES		Subject identifier (implementation-specific).
er_company_id	rap_az_id	integer	NO		Company identifier (references companies).
er_employee_no	rap_matricola	character varying(10)	NO		Employee number / badge number (matricola).
er_expiry_type_id	rap_tipo_scadenza	integer	NO		Expiry type identifier (lookup).
er_seniority_date	rap_data_anzianita_conv	date	NO		Seniority date (conventional/recognized).
er_end_reason_id	rap_xfirap_id	integer	YES		End-of-relationship reason (lookup).
er_is_protected_hire	rap_assunzione_protetta	numeric(1,0)	NO		Protected hire flag (0/1).
er_protected_hire_type_id	rap_xasspro_id	integer	YES		Protected hire type (lookup).
er_hire_category_id	rap_xcatass_id	integer	YES		Hire category (lookup).
er_probation_end_date	rap_data_scadenza_prova	date	NO		Probation period end date.
er_payroll_employee_no	rap_matricola_paghe	character varying(20)	NO		Payroll employee number (matricola paghe).
er_origin_type_id	rap_xpr_id	integer	YES		Origin/provenance type (lookup).
er_is_cancelled	rap_cancellato	boolean	NO		Business-level cancellation flag (distinct from technical _deleted).
er_cancelled_on	rap_data_cancellato	date	NO		Business-level cancellation date.
er_termination_notes	tenant_id	text	NO		Termination notes.
tenant_id	colcustom	integer	YES		Tenant identifier (multi-tenant isolation).
colcustom	_deleted	jsonb	YES	'{}'::jsonb	Custom fields payload (JSON).
_deleted	rap_periodo	boolean	YES	false	Technical soft-delete flag.
er_period	``	daterange	YES	GENERATED: daterange(er_start_date, er_end_date, '[]'::text)	Employment period as daterange [start_date, end_date].

Relationships

Outgoing foreign keys

Constraint	Local columns	References	Referenced columns	On update	On delete
fk_employment_relationships_company	er_company_id, tenant_id	hr.companies	cmp_id, tenant_id	RESTRICT	RESTRICT
fk_employment_relationships_end_reason	er_end_reason_id, tenant_id	hr.x_termination_reasons	xtr_id, tenant_id	RESTRICT	RESTRICT
fk_employment_relationships_origin_type	er_origin_type_id, tenant_id	hr.x_hiring_sources	xhs_id, tenant_id	RESTRICT	RESTRICT
fk_employment_relationships_person	er_person_id, tenant_id	hr.person_registry	pr_id, tenant_id	RESTRICT	CASCADE
fk_employment_relationships_tenant	tenant_id	cloud.tenants	ten_internal_id	RESTRICT	CASCADE
fk_employment_relationships_type	er_relationship_type_id, tenant_id	hr.x_employment_agreement_types	xeat_id, tenant_id	RESTRICT	RESTRICT

Referenced by

From table	Foreign key	Source columns	Target columns
hr._app_config_employment	fk_ace_employment_relationship	ace_employment_relationship_id, tenant_id	er_id, tenant_id
hr.business_trips	fk_intra_rap_id	btr_employment_id, tenant_id	er_id, tenant_id
hr.company_announcement_reads	fk_car_employment_id	car_employment_id, tenant_id	er_id, tenant_id
hr.customer_activity_calendar	fk_customer_activity_calendar_employment	employment_relationship_id, tenant_id	er_id, tenant_id
hr.customer_employment_links	fk_customer_employment_links_employment	cel_employment_relationship_id, tenant_id	er_id, tenant_id
hr.customer_fixed_activities	fk_customer_fixed_activities_employment	employment_relationship_id, tenant_id	er_id, tenant_id
hr.documents	fk_documents_subject_employment	doc_subject_employment_id, tenant_id	er_id, tenant_id
hr.employment_agreement_assignments	fk_eaa_employment_relationship	eaa_er_id, tenant_id	er_id, tenant_id
hr.employment_compensation_budgets	fk_ecb_employment_relationship	ecb_employment_relationship_id, tenant_id	er_id, tenant_id
hr.employment_compensation_costs	fk_ecc_employment_relationship	ecc_employment_relationship_id, tenant_id	er_id, tenant_id
hr.employment_inail_positions	fk_eip_employment_relationship	eip_employment_relationship_id, tenant_id	er_id, tenant_id
hr.employment_job_role_assignments	fk_ejra_employment_relationship	ejra_er_id, tenant_id	er_id, tenant_id
hr.employment_pay_accumulators	fk_epa_employment_relationship	epa_employment_relationship_id, tenant_id	er_id, tenant_id
hr.employment_pay_components	fk_epc_employment_relationship	epc_employment_relationship_id, tenant_id	er_id, tenant_id
hr.employment_payslip_body_items	fk_epbi_employment_relationship	epbi_employment_relationship_id, tenant_id	er_id, tenant_id
hr.employment_position_assignments	fk_epa_employment_relationship	epa_er_id, tenant_id	er_id, tenant_id
hr.employment_relationship_deadlines	fk_erdl_er_id	erdl_er_id, tenant_id	er_id, tenant_id
hr.employment_relationship_details	fk_erd_employment_relationship_id	erd_employment_relationship_id, tenant_id	er_id, tenant_id
hr.employment_special_regulation_assignments	fk_esra_employment_relationship	esra_employment_relationship_id, tenant_id	er_id, tenant_id
hr.employment_variable_compensations	fk_evc_employment_relationship	evc_employment_relationship_id, tenant_id	er_id, tenant_id
hr.equipment_assignments	fk_eqa_employment	eqa_employment_id, tenant_id	er_id, tenant_id
hr.events	fk_events_tenant_employment_rel	evt_employment_relationship_id, tenant_id	er_id, tenant_id
hr.document_logs	fk_dl_employment_relationship	dl_employment_relationship_id, tenant_id	er_id, tenant_id
hr.person_benefits	fk_pbn_employment_relationship	pbn_employment_relationship_id, tenant_id	er_id, tenant_id
hr.employee_secondments	fk_es_employment_relationship	es_employment_relationship_id, tenant_id	er_id, tenant_id
hr.employee_inps_positions	fk_eip_employment_relationship	eip_employment_relationship_id, tenant_id	er_id, tenant_id
hr.rapporti_ruoli	fk_rapru_rap_id	rapru_rap_id, tenant_id	er_id, tenant_id
hr.time_accounting_entries	fk_time_accounting_entries_employment	tae_employment_id, tenant_id	er_id, tenant_id
hr.work_reports	fk_work_reports_employment	wr_employment_id, tenant_id	er_id, tenant_id
hr.work_sites	fk_sede_responsabile_rap_id	ws_manager_relationship_id, tenant_id	er_id, tenant_id
pres.attendance_alerts	fk_avvisi_avvi_rap_id_to_rapporti	tenant_id, aal_employment_id	tenant_id, er_id
pres.attendance_counter_balances	fk_tot_numeri_tot_rap_id_to_rapporti	tenant_id, acb_employment_contract_id	tenant_id, er_id
pres.attendance_status	fk_asspre_ass_rap_id_to_rapporti	tenant_id, ast_employment_id	tenant_id, er_id
pres.attendance_status_realtime	fk_asspre_auto_ass_rap_id_to_rapporti	tenant_id, asrt_employment_id	tenant_id, er_id
pres.daily_attendance_details	fk_cartellino_rap_id_to_rapporti	tenant_id, dad_employment_id	tenant_id, er_id
pres.daily_attendance_justifications	fk_giust_giorno_gig_rap_id_to_rapporti	tenant_id, daj_employment_id	tenant_id, er_id
pres.daily_shift_planning	fk_cartellino_turni_cart_rap_id_to_rapporti	tenant_id, dsp_employment_id	tenant_id, er_id
pres.dipendenti	fk_dipendenti_di_rap_id_to_rapporti	tenant_id, di_rap_id	tenant_id, er_id
pres.employee_badges	fk_badge_badge_rap_id_to_rapporti	tenant_id, emb_employment_id	tenant_id, er_id
pres.employee_clockings	fk_timbra_tim_rap_id_to_rapporti	tenant_id, ec_employment_contract_id	tenant_id, er_id
pres.employee_counter_assignments	fk_dip_conta_dipc_rap_id_to_rapporti	tenant_id, eca_employment_id	tenant_id, er_id
pres.employee_leave_balances	fk_progressivi_prog_rap_id_to_rapporti	tenant_id, elb_employee_id	tenant_id, er_id
pres.employee_leave_balances_today	fk_ferie_today_ft_rap_id_to_rapporti	tenant_id, elbt_employment_id	tenant_id, er_id
pres.employee_overtime_profile_assignments	fk_profstr_assign_strass_rap_id_to_rapporti	tenant_id, eopa_employee_id	tenant_id, er_id
pres.long_term_absences	fk_giustif_arg_rap_id_to_rapporti	tenant_id, lta_employment_id	tenant_id, er_id
pres.monthly_attendance_summaries	fk_cart_mese_mes_rap_id_to_rapporti	tenant_id, mas_employment_id	tenant_id, er_id
pres.monthly_justification_aggregates	fk_giust_mese_gme_rap_id_to_rapporti	tenant_id, mja_employment_id	tenant_id, er_id
pres.monthly_payroll_print_layouts	fk_descri_mese_desc_rap_id_to_rapporti	tenant_id, mppl_employment_id	tenant_id, er_id
pres.payroll_additional_reasons	fk_causpaghe_caup_rap_id_to_rapporti	tenant_id, par_employment_id	tenant_id, er_id
pres.employee_shift_assignments	fk_esa_employment_relationship	tenant_id, esa_employment_relationship_id	tenant_id, er_id
pres.shift_planning_sort_order	fk_cartellino_turni_ordinamento_carto_rap_id_to_rapporti	tenant_id, spso_employment_id	tenant_id, er_id
pres.weekly_attendance_summaries	fk_cart_sett_sett_rap_id_to_rapporti	tenant_id, was_employment_id	tenant_id, er_id
pres.weekly_justification_aggregates	fk_giust_sett_gis_rap_id_to_rapporti	tenant_id, wja_employment_id	tenant_id, er_id
pres.weekly_shift_schema_days	fk_profora_settimane_proset_rap_id_to_rapporti	tenant_id, wssd_schema_id	tenant_id, er_id

Constraints

• Primary key: employment_relationships_pkey → id
• Unique: uk_er_id_tenant → er_id, tenant_id
• Unique: uk_tenant_er_id → tenant_id, er_id

Indexes

Name	Unique	Method	Columns / expression
idx_employment_relationships_company_id_tenant	NO	btree	tenant_id, er_company_id) WHERE (_deleted = false
idx_employment_relationships_contract_id_tenant	NO	btree	tenant_id, er_contract_id) WHERE (_deleted = false
idx_employment_relationships_end_reason_id_tenant	NO	btree	tenant_id, er_end_reason_id) WHERE (_deleted = false
idx_employment_relationships_er_id_tenant	NO	btree	tenant_id, er_id) WHERE (_deleted = false
idx_employment_relationships_hire_category_id_tenant	NO	btree	tenant_id, er_hire_category_id) WHERE (_deleted = false
idx_employment_relationships_origin_type_id_tenant	NO	btree	tenant_id, er_origin_type_id) WHERE (_deleted = false
idx_employment_relationships_person_id_tenant	NO	btree	tenant_id, er_person_id) WHERE (_deleted = false
idx_employment_relationships_protected_hire_type_id_tenant	NO	btree	tenant_id, er_protected_hire_type_id) WHERE (_deleted = false
idx_employment_relationships_subject_id_tenant	NO	btree	tenant_id, er_subject_id) WHERE (_deleted = false
idx_employment_relationships_tenant	NO	btree	tenant_id
idx_employment_relationships_tenant_period_gist	NO	gist	tenant_id, er_period) WHERE (_deleted = false
idx_employment_relationships_tenant_person_period_gist	NO	gist	tenant_id, er_person_id, er_period) WHERE (_deleted = false

Navigation

• Back to main index
• [Back to hr index]../)