Database — schema overview

What it is

The database catalog: 70 tables, 272 indexes, generated by EF Core 8 from the domain model and recorded in the EF ModelSnapshot (ManpowerIQDbContextModelSnapshot.cs) — the structural source of truth (no live DB needed). PostgreSQL via Npgsql, snake_case throughout. This page is the table catalog + the foreign-key map; the behaviour behind the data-layer conventions lives on the Phase-5 pages, cross-linked rather than repeated.

Data-layer conventions (reference — see the linked pages)

Every table follows a small set of conventions established elsewhere in this guide:

snake_case names + bilingual columns — Name_1_English / Name_2_Arabic (and Description_*) for user-facing labels. See Internationalisation.
AuditableEntity base — Id, CreatedAt/By, UpdatedAt/By, IsDeleted + DeletedAt/By (soft-delete), RowVersion (optimistic concurrency, bytea), IsActive. See Audit & soft-delete.
TenantEntity : AuditableEntity — adds BusinessUnitId; ~40 of the 70 tables are tenant-scoped. Isolation is enforced by query filters + stamping + RLS. See Multi-tenancy.
Partial-unique indexes — code uniqueness is filtered WHERE is_deleted = false so codes are reusable after soft-delete. See Lookup CRUD template.
AuditEvent is the exception — append-only bigint id, not an AuditableEntity (no soft-delete/RowVersion).

Foreign-key map (core chain)

The operational core, from reference data through to published rosters and reconciliation (FKs from the ModelSnapshot):

flowchart TB
    BU[business_units] --> TER[terminals] --> DEP[departments]
    DEP --> NODE[nodes]
    DEP --> POOL[pools] --> PLR[pool_lending_rules]
    EMP[employees] --> DEP
    EMP --> POOL
    EMP --> TER
    DP[demand_plans] --> DL[demand_lines]
    DP --> TER
    DL --> NODE
    DL --> SKILL[skills]
    AR[allocation_runs] --> DP
    AR --> ARS[allocation_rule_sets] --> ARULE[allocation_rules]
    AR --> ARC[allocation_run_candidates] --> DL
    ARC --> EMP
    PR[published_rosters] --> AR
    PRL[published_roster_lines] --> PR
    PRL --> ARC
    PRL --> EMP
    ATT[attendance_records] --> EMP
    ATT --> ASTAT[attendance_statuses]
    REC[reconciliation_exception_validations] --> EMP
    LR[leave_requests] --> EMP --> LAS[leave_approval_steps]

(The full 70-table FK set is in the ModelSnapshot; this shows the principal flows.)

Table catalog (70 tables, by domain)

Legend: scope = BU (tenant-scoped) / global (shared reference) / hybrid (both).

Foundation — tenancy, RBAC, audit (7)

Table	Purpose	Scope
`business_units`	Tenant root	root (RLS-exempt)
`users`	Login accounts	BU
`roles`	Permission bundles	BU
`permissions`	Global permission catalog	global
`role_permissions`	Role→permission grants	BU
`user_roles`	User→role grants (dept-scoped, effective-dated)	BU
`audit_events`	Append-only audit log (`bigint`, not Auditable)	BU-stamped

Org structure (6)

Table	Purpose	Scope
`terminals`	Terminals	BU
`departments`	Departments (→ terminal)	BU (seed/read)
`nodes`	Work nodes (→ department, node_type)	BU (seed/read)
`node_types`	Node-type lookup	global (CRUD)
`pools`	Worker pools (default 1/dept)	BU
`pool_lending_rules`	Directional cross-pool lending	BU

Employee master (10)

Table	Purpose	Scope
`employees`	Core HR record (11 FKs)	BU
`employee_eligible_terminals` / `employee_eligible_nodes`	Where a worker may be assigned (effective windows)	BU
`employee_groups` / `employee_group_memberships`	Named groups + membership	BU
`grades`	Grade lookup (CRUD, MIQ-133)	global
`genders` / `nationalities` / `employee_statuses` / `employment_types`	Employee lookups	global (seed/read)

Skills & certifications (8)

Table	Purpose	Scope
`skills`	Skill catalog	BU
`skill_categories` / `skill_levels`	Skill taxonomy	global (seed/read)
`employee_skills`	Employee↔skill (level)	BU
`certification_types` / `certification_categories`	Cert catalog	hybrid / global
`employee_certifications`	Employee certs	BU
`skill_certification_requirements`	Skill→required cert	BU

Calendars & shifts (11)

Table	Purpose	Scope
`business_unit_calendars` / `business_unit_weekend_days`	BU calendar + weekends	BU
`holidays` / `holiday_types`	Holidays + type lookup	BU / global
`terminal_weekend_days` / `working_hour_modifier_periods`	Per-terminal weekends + hour modifiers	BU
`shift_templates` / `shift_types`	Shift templates (CRUD) + type lookup	BU / global
`shift_patterns` / `shift_pattern_types` / `shift_pattern_days`	Patterns + type + day rows	BU / global / BU

Demand (5)

Table	Purpose	Scope
`demand_plans`	Demand matrix (status machine)	BU
`demand_lines`	Plan cells (→ node, skill, reason)	BU
`demand_templates` / `demand_template_lines`	Reusable shapes	BU
`demand_reasons`	Reason lookup (CRUD)	hybrid

Allocation engine (7)

Table	Purpose	Scope
`allocation_rule_sets`	Rule sets	BU
`allocation_rules` / `allocation_rule_scopes`	Rules + their scopes	BU
`allocation_rule_type_catalog`	12-type catalog (system rows)	hybrid
`allocation_rule_execution_logs`	Per-rule execution log	BU
`allocation_runs`	Allocation runs (state machine, snapshot)	BU
`allocation_run_candidates`	Candidate evaluations per demand line	BU

Rosters, publish & approvals (4)

Table	Purpose	Scope
`published_rosters`	Immutable published snapshot	BU
`published_roster_lines`	Snapshot lines	BU
`roster_approval_steps`	Approval queue steps	BU
`employee_shift_assignments`	Shift assignments (→ pattern)	BU

Leave (2)

Table	Purpose	Scope
`leave_requests`	Leave requests (status, no LeaveType taxonomy)	BU
`leave_approval_steps`	2-step approval chain	BU

Attendance & reconciliation (5)

Table	Purpose	Scope
`attendance_records`	Attendance (Excel-sourced)	BU
`attendance_statuses`	Status lookup (CRUD)	global
`attendance_audit_logs`	Attendance change log	BU
`attendance_pending_reviews`	Edit-review queue	BU
`reconciliation_exception_validations`	Reconciliation validations	BU

Imports (2)

Table	Purpose	Scope
`import_jobs`	Excel import jobs (Hangfire)	BU
`import_job_errors`	Per-row errors (`RawRowJson` jsonb)	BU

Approval matrix & settings (3)

Table	Purpose	Scope
`approval_matrix_rules`	Approval-matrix rules (resolver not wired to live flow)	BU
`approval_request_types`	Request-type lookup	global
`application_settings`	BU settings	BU

Read-only / seed-only vs CRUD

Full admin CRUD: the 9 lookups with admin controllers — shift_templates, holidays, demand_reasons, node_types, terminals, grades, skills, attendance_statuses, pools (sheet 21).
Seed-only / read-only: business_units, departments, nodes, and the employee/skill lookups (genders, nationalities, employee_statuses, employment_types, holiday_types, shift_types, skill_categories, skill_levels, approval_request_types, certification_types) — populated by seed/migration, no write API.
Import/seed-only data: employees, employee_skills, employee_certifications — no create/edit endpoint; loaded via Excel import (sheet 03).

Corrections carried (where they touch tables)

demand_plans — DemandPlanStatus.Locked is a defined-but-unreachable enum value (F1); no path sets a plan to Locked.
published_rosters — the Approved status waypoint is dead; publish transitions straight to Published.
audit_events — written selectively (services call the logger); not an automatic shadow of every write.

Migrations · Seeds & reference data
Multi-tenancy · Audit & soft-delete · Internationalisation
Source: ManpowerIQDbContextModelSnapshot.cs (70 tables / 272 indexes); fact sheets 01–22.