Database hr_ai — AI Assistant

Reduced PostgreSQL database, populated nightly from the production HR database, designed to answer user questions via an AI assistant.

Architecture

Production DB (hr_prod / Italian schema)
        │
        │  02_sync_daily.sql
        │  (nightly at 02:00 via pg_cron)
        ▼
AI Assistant DB (hr_ai / English schema)
        │
        └─► AI Assistant (read-only)

Scripts are located in the repository under scripts/ai-assistant/.

Differences from the source database

Aspect	Production DB	hr_ai
colcustom columns	Present (internal JSON)	Removed
_deleted columns	Present	Removed (sync imports only active records)
Number of tables	~348	22
External foreign keys	Complete	Only towards included tables
Access	Read/Write	Read-only recommended

Included tables

Schema cloud

Table	Italian original	Description
cloud.tenants	cloud._tenant	Tenant/organizations in the multi-tenant system

Schema hr

Table	Italian original	Description
hr.collective_agreements	hr.contratti	National collective labor agreements (CCNL)
hr.companies	hr.aziende	Companies managed in the HR system
hr.customers	hr.in_clienti	Customers/clients
hr.documents	hr.in_documents	Documents and requests (leave, permits…)
hr.element_group_types	hr.gruppi_tipi	Element group type definitions
hr.element_groups	hr.gruppi_elementi	Element groups (hierarchical)
hr.employment_relationships	hr.rapporti	Employment relationships person↔company
hr.employment_relationship_details	hr.rapporti_det	Time-bound contractual details
hr.employment_relationship_deadlines	(no translation available)	Employment relationship due dates
hr.customer_locations	hr.in_clienti_sedi	Customer locations/branches
hr.document_logs	hr.in_documents_log	Document audit log
hr.person_citizenship	hr.ana_cittadinanza	Person citizenship history
hr.person_group_assignments	hr.ana_gruppi	Person-to-group assignments
hr.person_registry	hr.anagrafico	Person master data (employees)
hr.work_sites	hr.sedi_operative	Operational sites with geofencing data
hr.x_employment_agreement_types	hr.x_tipi_rapporto	Employment agreement type lookup
hr.x_hiring_categories	hr.x_tipi_categorie_assunzione	Hiring category lookup

Schema pres

Table	Italian original	Description
pres.daily_attendance_details	pres.cartellino	Daily timesheet (clockings, hours)
pres.daily_attendance_justifications	pres.giust_giorno	Daily absence/leave justifications
pres.monthly_attendance_summaries	pres.cart_mese	Monthly attendance totals
pres.monthly_justification_aggregates	pres.giust_mese	Monthly aggregated justifications

Scripts

File	Purpose
scripts/ai-assistant/01_create_schema.sql	DDL — creates schemas, tables and indexes
scripts/ai-assistant/02_sync_daily.sql	hr_ai_sync() procedure — daily sync via dblink

Initial setup

# 1. Create the database
createdb -U postgres hr_ai

# 2. Create schemas and tables
psql -U postgres -d hr_ai -f scripts/ai-assistant/01_create_schema.sql

# 3. Install the sync procedure
psql -U postgres -d hr_ai -f scripts/ai-assistant/02_sync_daily.sql

# 4. Run the first manual sync
psql -U postgres -d hr_ai -c \
  "CALL hr_ai_sync('host=<src-host> dbname=<ita-db> user=<user> password=<pwd>');"

Automated sync (pg_cron)

-- Schedule nightly sync at 02:00
SELECT cron.schedule(
    'sync-hr-ai-nightly',
    '0 2 * * *',
    $$CALL hr_ai_sync('host=<src-host> dbname=<ita-db> user=<user> password=<pwd>')$$
);

Important notes

• The hr.customer_locations table was previously hr.in_clienti_sedi in the Italian source database.
• Generated daterange columns (e.g. er_period, ca_period) are kept to enable efficient range queries for the AI assistant.
• Foreign keys towards tables not included in this DB (e.g. pres.attendance_reasons, hr.document_models) have been removed to avoid integrity errors during sync.
• The hr_ai database should be accessible in read-only mode by the AI assistant (revoke INSERT/UPDATE/DELETE privileges on the AI user).