Skip to content

Database — seeds & reference data

What it is

What's in the database before any user touches it: the system rows the runtime depends on, the seeded lookups, and the optional demo dataset. Distinguishing these is essential — some rows are immutable, some are shared across tenants, and some are only there in development.

Three kinds of row

Kind Marker Meaning
System row IsSystem = true Seeded and structurally immutable — admin edit/delete is rejected with 409 <ENTITY>_PROTECTED. The runtime assumes these exist.
Global row no business_unit_id Shared reference data — the same for every business unit (e.g. the permission catalog).
Tenant row business_unit_id set Owned by one BU; never visible to another (see Multi-tenancy).

IsSystem (immutable) and global (shared) are different ideas — a global lookup can have non-system rows a BU adds; a tenant table can have system rows. Don't conflate them (see Coexistence pattern).

Seeded at migration time

The migrations seed the baseline the system needs to function:

  • RBAC — the 97-permission catalog (global) and the 9 system roles per BU (MIQ-003 baseline was 40/7; HR_DIRECTOR + COO + ~57 permissions added later). See the RBAC matrix.
  • Allocation rule-type catalog — the 12 rule types as system rows (allocation_rule_type_catalog, MIQ-021).
  • Attendance statuses — 8 seeded statuses (Sprint 10a).
  • Employee/skill lookups — genders, nationalities, employee_statuses, employment_types, grades, skill_categories, skill_levels (MIQ-006).
  • Org & calendar lookups — node_types, holiday_types, shift_types, approval_request_types, plus global demand_reasons (MIQ-016).
  • Demo users — the baseline admin.demo / etc. accounts used for local login (login-dev).

These seed INSERTs are why a reset must drop + recreate, not truncate (PB-025 — see Migrations).

Seeded vs CRUD-managed lookups

  • 9 lookups have full admin CRUD: shift_templates, holidays, demand_reasons, node_types, terminals, grades, skills, attendance_statuses, pools — their system rows stay immutable; admins manage the non-system tail (sheet 21, Lookup CRUD template).
  • The rest are seed-only / read-only — nationalities, genders, employment_types, employee_statuses, skill_categories, skill_levels, holiday_types, shift_types, certification_types, approval_request_types, and departments / nodes (no write API; populated by seed/migration).

The demo dataset

A richer demo dataset (employees, skills, demand, rosters, attendance) is loaded by the DemoDataSeeder in the E2E test project — used to populate a realistic local environment for screenshots/UAT (the Phase-3 demo data). It is development tooling, not production seed, and is idempotent (re-running adds zero net rows); selective cleanup uses DDS_* prefix queries, full reset is drop+recreate (runbook §11).

Gotchas / constraints

  • System rows are immutable — don't write code that edits or deletes them; the guard returns 409.
  • employees / employee_skills / employee_certifications are import/seed-only — no create/edit API (sheet 03).
  • Reset = drop + recreate — truncation leaves __EFMigrationsHistory ahead of the rows and the seed won't re-run (PB-025).
  • Demo data ≠ migration seed — the DemoDataSeeder is separate from the baseline the migrations install; a fresh DB has the migration seed but not the demo dataset until the seeder runs.

Build status

Available — migration-time seeds ship with the schema; the demo dataset is dev tooling (E2E project).