Rediflow
English ▾

Import persons and related data from ODS

This guide explains how to import employee groups, leave types, persons, and project assignments into Rediflow using a people ODS file (e.g. docs/example_people.ods).

Overview

  • Script: scripts/seed_people_from_ods.py
  • Default file: docs/example_people.ods (if present)
  • Sheets: Employee groups, Leave types, Persons, FTE periods (optional), Project assignments
  • Result: Worker groups (with daily work hours from a date), leave types, persons with org and worker-group membership from employment start, optional FTE periods (reduced worktime), and person–project assignments with optional role and allocation %
  • Idempotent: Re-running with the same file updates existing records by code/name; safe to run multiple times

Prerequisites:

  • Database: Containerised (Podman or Docker) PostgreSQL 18+; DATABASE_URL set; migrations applied.
  • Orgs and projects: Must already exist. Create them first (e.g. Import project from ODS or seed_example_projects.py). The people import does not create orgs or projects.
  • Allocation group "fixed": Required for project assignments. Create it under Settings if missing, or it will be skipped and assignments will have no allocation group.

Quick import

From the project root:

uv run python scripts/seed_people_from_ods.py --ods path/to/people.ods

Dry run (no DB changes):

uv run python scripts/seed_people_from_ods.py -o path/to/people.ods --dry-run

Import when containerised

When the app and database run in containers (e.g. compose.deploy-qa.yml or compose.deploy-prod.yml), mount the people ODS and run inside the app container:

QA:

podman compose -f compose.deploy-qa.yml run --rm \
  -v "$(pwd)/people.ods:/app/people.ods:ro" \
  app-qa python scripts/seed_people_from_ods.py --ods /app/people.ods

Production: Replace compose.deploy-qa.yml and app-qa with compose.deploy-prod.yml and app-prod.

Dry run (no DB changes):

podman compose -f compose.deploy-qa.yml run --rm \
  -v "$(pwd)/people.ods:/app/people.ods:ro" \
  app-qa python scripts/seed_people_from_ods.py --ods /app/people.ods --dry-run

Sheet layout reference

Sheet names and column order must match. Row 0 is the header; data starts at row 1. Column indices below are 0-based.

Employee groups

  • Sheet name: Employee groups
  • Columns (0-based): 0 = code, 1 = label, 2 = daily_work_hours, 3 = effective_from_date
  • Example: AHTP, AchtungPape, 7.25, 2000-01-01
  • Rules: code required; daily_work_hours > 0; effective_from_date in YYYY-MM-DD or D.M.YYYY. Multiple rows with the same code add multiple rates (effective from different dates).

Leave types

  • Sheet name: Leave types
  • Columns: 0 = code, 1 = label
  • Example: paid_leave, Paid leave
  • Rules: code required; duplicates by code update the existing leave type.

Persons

  • Sheet name: Persons

  • Two layouts supported (auto-detected from header row 0):

    Layout A (legacy): 0 = name, 1 = code (optional), 2 = org_short_name, 3 = worker_group_code, 4 = employment_start_date

    • Example: Jane Doe, JDOE, EXAMPLE-IT, AHTP, 2020-06-01
    • Name is split: last word → lastname, rest → firstname (e.g. "Jane Doe" → firstname=Jane, lastname=Doe).

    Layout B (firstname/lastname): 0 = firstname, 1 = lastname, 2 = code (optional), 3 = org_short_name, 4 = worker_group_code, 5 = employment_start_date

    • Example: Jane, Doe, JDOE, EXAMPLE-IT, AHTP, 2020-06-01
    • Header row must have "firstname" and "lastname" (or "first_name"/"last_name") in the first two columns to trigger this layout.

  • Rules: At least one of (firstname, lastname) or code required. org_short_name, worker_group_code, employment_start_date required. code is optional; if provided and a person with that code exists, the row updates that person. Org and worker group must exist (orgs from Settings; worker groups from the same ODS or previous import). One org period and one worker-group period are set from employment_start_date with no end date (no lifecycle changes on import).

FTE periods (optional)

  • Sheet name: FTE periods
  • Columns (0-based): 0 = person_code, 1 = start_date, 2 = end_date, 3 = fte
  • Example: JDOE, 2026-01-01, 2026-12-31, 0.80
  • Rules: person_code must match a person (from the Persons sheet or already in the DB). start_date required (YYYY-MM-DD or D.M.YYYY). end_date optional; if empty, treated as null (ongoing). fte must be in (0, 1], e.g. 0.80 for 80% part-time; stored with 2 decimals. Used for capacity: monthly capacity = workdays × daily_work_hours × fte. If the sheet is missing or empty, no FTE periods are created (default FTE 1.0). Person code lookup is case-insensitive.

Project assignments

  • Sheet name: Project assignments
  • Columns: 0 = person_code, 1 = project_name, 2 = role_type_code (optional), 3 = allocation_pct, 4 = start_date, 5 = end_date, 6 = notes (optional)
  • Example: JDOE, Example project, pm, 50, 2026-01-01, 2026-12-31, Backend for WP3
  • Rules: person_code (or person name if no code was set), project_name required. project_name must match an existing project. role_type_code is optional; must match a non-deprecated role type code in Settings. allocation_pct 0–100. start_date and end_date can be:
    • Calendar date: YYYY-MM-DD or D.M.YYYY.
    • Project month (integer 1, 2, 3, …): Start date column → first day of that project month; end date column → last day of that project month. Month 1 = month of project start.
    • -1: Start date -1 → first day of project month 1 (project begin). End date -1 → last day of project (planned end month or last month from duration). One assignment per (person, project); re-import updates dates and role.
  • Missing persons: If a person_code in Project assignments does not exist in the Persons sheet (or in the DB), the importer creates a minimal person (code and name = person_code) using the first data row of the Persons sheet as default org and worker group, and one org period and one worker-group period from the assignment’s start_date. So you can have assignments for persons that appear only in the Project assignments sheet, as long as the Persons sheet has at least one row to supply default org and worker group. Person code lookup is case-insensitive (SALMSAN matches salmsan).
  • Over-allocation validation: After processing all assignment rows, the importer checks that for every (person, month) the sum of allocation_pct across assignments overlapping that month is ≤ 100%. If any person has total allocation over 100% in any month, an error is added and the import reports failures (see Troubleshooting).

Date formats

  • YYYY-MM-DD (e.g. 2000-01-01)
  • D.M.YYYY or DD.MM.YYYY (e.g. 1.1.2000, 01.01.2000)

After import

  1. Settings → Employee groups: Check codes and daily work hours.
  2. Settings → Leave types: Check codes and labels.
  3. People (or org/capacity views): Check persons, org membership, worker group, and project assignments with roles and allocation %.

Troubleshooting

  • "org … not found" — Create the org in Settings → ORGS first, or ensure the short name in the Persons sheet matches exactly.
  • "worker group … not found" — Define the group in the Employee groups sheet (same ODS) so it is created before the Persons sheet is processed; order of sheets in the file is not guaranteed, so the importer creates all worker groups and leave types before any persons.
  • "project … not found" — Create the project first (e.g. from the project ODS or in the app).
  • "person 'XXX' not found" — The person_code is not in the Persons sheet and the importer could not create them because the Persons sheet is empty or the first data row has no valid org or worker group. Ensure the Persons sheet has at least one row (with org_short_name and worker_group_code that exist); that row is used as the default when creating missing persons from Project assignments.
  • "person_code is required" — A row in Project assignments has an empty person_code but other cells filled; fill person_code or clear the row. Fully empty rows (no person_code and no project_name) are skipped automatically.
  • "name (or firstname+lastname) is required" (Persons) — A row has some cells filled but neither name (layout A) nor firstname/lastname (layout B) nor code is present; fill name or firstname+lastname or code, or clear the row. Fully empty rows are skipped automatically.
  • "person … not found" (in Project assignments) — Ensure the person is listed in the Persons sheet (same ODS) and that person_code or name matches exactly.
  • Allocation group "fixed" — If assignments are created without an allocation group, create an allocation group with code fixed in Settings.
  • "Person … has total allocation …% in YYYY-MM (max 100%)" — One or more persons have overlapping project assignments that sum to more than 100% in at least one month. Edit the Project assignments sheet so that for each person the sum of allocation_pct in any month is at most 100 (e.g. split across projects or reduce percentages).

For project structure import, see Import existing project data from ODS. For setup and first steps, see Overview and Initial data entry.