CSV Import Schema¶

Design principle¶

Identity Atlas defines one canonical CSV schema per entity type. Column names, types, and relationships are fixed and documented. The crawler reads exactly this format — no column-name guessing, no aliases, no auto-detection.

Source-specific transformation (Omada → Identity Atlas, SAP → Identity Atlas, ServiceNow → Identity Atlas) happens before import via a lightweight pre-import script. Identity Atlas owns the target schema, the user owns the source mapping. This separation keeps the crawler simple and testable.

CSV files and their schemas¶

Every file is semicolon-delimited, UTF-8 with BOM (matching the de facto standard for European CSV exports). The delimiter is configurable per crawler config.

All files are optional — import only what your source system has. The minimum viable import is: Resources.csv + Users.csv + Assignments.csv. Everything else adds depth.

1. `Systems.csv` (optional)¶

Defines the authorization systems. If omitted, all data is scoped to the single system defined in Step 1 of the wizard.

Column	Type	Required	Description
`ExternalId`	string	yes	Unique identifier in the source (used as dedup key)
`DisplayName`	string	yes	Human-readable name (e.g. "SAP ERP", "Active Directory")
`SystemType`	string	no	Grouping label (e.g. "SAP", "AD", "ServiceNow"). Defaults to the wizard's System Type if omitted
`Description`	string	no	Free text

Extra columns are stored in extendedAttributes JSON.

2. `Resources.csv` (required)¶

Permissions, roles, groups, apps — anything a user can be assigned to.

Column	Type	Required	Description
`ExternalId`	string	yes	Unique identifier in the source system
`DisplayName`	string	yes	Human-readable name
`ResourceType`	string	no	Classification: `Group`, `SAPRole`, `BusinessRole`, `ApplicationRole`, etc. Free-form; `BusinessRole` has special treatment (shown on the Business Roles page)
`Description`	string	no	Free text
`SystemName`	string	no	Must match a `DisplayName` from `Systems.csv` or the wizard's system name. Omit when all resources belong to the same system
`Enabled`	bool	no	`true`/`false`. Default: `true`

Extra columns → extendedAttributes.

3. `Users.csv` (required)¶

People, service accounts, bots — anything that can hold permissions.

Column	Type	Required	Description
`ExternalId`	string	yes	Unique identifier (employee number, sAMAccountName, etc.)
`DisplayName`	string	yes	Full name
`Email`	string	no	Primary email / UPN
`PrincipalType`	string	no	One of: `User`, `ServicePrincipal`, `ExternalUser`, `SharedMailbox`. Default: `User`
`JobTitle`	string	no
`Department`	string	no	Used to derive OrgUnit contexts when no `Contexts.csv` is provided
`ManagerExternalId`	string	no	The `ExternalId` of another row in this file — the person's manager. Feeds the org chart, the manager-hierarchy contexts and the "reports to" filters. Order does not matter: the manager may appear anywhere in the file, or have been imported earlier. A row naming itself is ignored, and a manager that is not in the file (and not already imported) leaves the field empty and is counted in the job's warnings rather than stored as a link to nobody
`SystemName`	string	no	Like Resources — links to a system. Omit for single-system imports
`Enabled`	bool	no	`true`/`false`. Default: `true`

Extra columns → extendedAttributes.

4. `Assignments.csv` (required)¶

Who has access to what.

Column	Type	Required	Description
`ResourceExternalId`	string	yes	Must match an `ExternalId` from `Resources.csv`
`UserExternalId`	string	yes	Must match an `ExternalId` from `Users.csv`
`AssignmentType`	string	no	`Direct` (default), `Indirect` or `Eligible` — ingest rejects any other value. Assignments to `BusinessRole` resources are flagged `governed` automatically after the import
`SystemName`	string	no	Scopes the assignment. Omit for single-system

Extra columns are not kept for this file (named in the job log instead): at tens of millions of rows an attribute per assignment costs more than it is worth.

5. `ResourceRelationships.csv` (optional)¶

Parent–child links between resources (role nesting, group membership, business-role contains permission).

Column	Type	Required	Description
`ParentExternalId`	string	yes	ExternalId of the parent resource
`ChildExternalId`	string	yes	ExternalId of the child resource
`RelationshipType`	string	no	`Contains` (default), `GrantsAccessTo`
`SystemName`	string	no

6. `Contexts.csv` (optional)¶

Organisational units, departments, cost centres. If omitted, contexts are derived automatically from Users.csv → Department column.

Column	Type	Required	Description
`ExternalId`	string	yes
`DisplayName`	string	yes
`ContextType`	string	no	`Department` (default), `CostCenter`, `Division`, `Team`
`Description`	string	no
`ParentExternalId`	string	no	For hierarchical org structures
`SystemName`	string	no

7. `Identities.csv` (optional)¶

Real persons (as opposed to accounts). Used when one person has multiple accounts across systems.

Column	Type	Required	Description
`ExternalId`	string	yes
`DisplayName`	string	yes
`Email`	string	no
`EmployeeId`	string	no	HR employee number
`Department`	string	no
`JobTitle`	string	no

8. `IdentityMembers.csv` (optional)¶

Links identities to their accounts (principals). Required when Identities.csv is provided and you want to show which accounts belong to which person.

Column	Type	Required	Description
`IdentityExternalId`	string	yes	Must match an `ExternalId` from `Identities.csv`
`UserExternalId`	string	yes	Must match an `ExternalId` from `Users.csv`
`AccountType`	string	no	`Primary`, `Secondary`, `Service`, `Admin`

9. `Certifications.csv` (optional)¶

Access review / certification decisions.

Column	Type	Required	Description
`ExternalId`	string	yes	Unique ID of the decision
`ResourceExternalId`	string	no	The resource being reviewed
`UserDisplayName`	string	no	Who was reviewed
`Decision`	string	no	`Approved`, `Denied`, `NotReviewed`, etc.
`ReviewerDisplayName`	string	no	Who made the decision
`ReviewedDateTime`	datetime	no	ISO 8601

Key design decisions¶

1. ExternalId is the dedup key, not UUID

Every entity uses ExternalId as its natural key. Identity Atlas generates deterministic UUIDs from <SystemType>-<EntityType>:<ExternalId>. Users never need to generate or know about UUIDs. Re-importing the same file is idempotent.

2. Cross-file references use ExternalId

Assignments.csv references resources and users by their ExternalId, not by UUID. The normalization layer resolves these to deterministic UUIDs using the same prefix. This means: - No pre-processing step to look up UUIDs - Files can be generated independently - Order of import doesn't matter (the crawler imports in the right order)

3. SystemName is optional everywhere

For single-system imports (the common case), omit SystemName from all files. Everything goes to the system defined in Step 1 of the wizard. For multi-system imports, add a SystemName column to any file where entities belong to different systems. Unrecognised or blank values fall back to the wizard system.

4. BusinessRole assignment auto-classification

When the target resource has ResourceType = 'BusinessRole', the assignment is automatically flagged governed after the import (its AssignmentType stays Direct). This means Omada-style exports (where all assignments are "Direct") work correctly on the Business Roles page without the user having to manually tag governed assignments.

5. Extra columns become extendedAttributes

A column not in a file's schema is stored as JSON in the entity's extendedAttributes field for Systems, Contexts, Resources, ResourceRelationships, Users, Identities and Certifications. This means: - Source-specific fields (Omada's ODWBusiKey, SAP's AGR_NAME, a logical application's configuration-management reference) are preserved - The UI can show them on detail pages under "Extended Attributes" - No schema changes needed to support new source fields

Column names match case-insensitively, so department is the Department column, not an extra one. Blank values are not stored, and a column named like an ingest bookkeeping field (id, systemId, extendedAttributes, createdAt, updatedAt, deletedAt) is never forwarded.

Three files do not keep extra columns, and the job log names the ones it ignores: Assignments.csv (tens of millions of rows — an attribute per assignment costs more than it is worth), and IdentityMembers.csv / ContextMembers.csv (their tables have no attribute storage).

6. Standard CSV quoting, failing loudly

Files are parsed as RFC 4180: a quoted value may contain the delimiter, a doubled quote or a line break, so distinguished names ("CN=x,OU=y,DC=z") load intact from a comma-delimited file. A row that cannot be parsed fails the import with its file and line; nothing is loaded as shifted columns, and a failed run never reconciles away existing rows.

7. An undeclared SystemName is reported

A SystemName that Systems.csv does not declare still loads — into the crawler's own system — but the job log warns with the row count and the unknown names, per file. ContextMembers.csv ignores SystemName: a membership belongs to its context's system, and the file is synced as one set.

Minimum viable imports¶

Scenario	Files needed
Basic permission review	`Resources.csv` + `Users.csv` + `Assignments.csv`
With org structure	+ `Contexts.csv` (or just `Department` column in `Users.csv`) + `ContextMembers.csv` for explicit membership
Multi-system	+ `Systems.csv` + `SystemName` column in other files
With identity correlation	+ `Identities.csv` + `IdentityMembers.csv`
With access reviews	+ `Certifications.csv`
With role hierarchy	+ `ResourceRelationships.csv`
Full model	All 10 files

Pre-import transformation¶

For each source system, the user writes a small transformation script that maps their column names to the Identity Atlas schema. We provide templates:

tools/
  crawlers/
    csv/
      schema/                    ← empty CSV files with just headers (the spec)
        Systems.csv
        Resources.csv
        Users.csv
        Assignments.csv
        ResourceRelationships.csv
        Contexts.csv
        ContextMembers.csv
        Identities.csv
        IdentityMembers.csv
        Certifications.csv
      csv-slots.json              ← label/required metadata for the schema/ files
  csv-templates/
    transforms/                  ← example transformation scripts
      omada-to-identityatlas.ps1
      entra-export-to-identityatlas.ps1
      generic-template.ps1

Example Omada transform (the "pre-import script"):

# Transform Omada Identity exports → Identity Atlas CSV schema
param([string]$SourceFolder, [string]$OutputFolder)

# Systems
Import-Csv "$SourceFolder/System.csv" -Delimiter ";" |
  Select-Object @{N='ExternalId';E={$_._ID}},
                @{N='DisplayName';E={$_._DISPLAYNAME}},
                @{N='Description';E={$_.DESCRIPTION}} |
  Export-Csv "$OutputFolder/Systems.csv" -Delimiter ";" -NoTypeInformation

# Resources
Import-Csv "$SourceFolder/Permission-full-details.csv" -Delimiter ";" |
  Select-Object @{N='ExternalId';E={$_._UID}},
                @{N='DisplayName';E={$_._DISPLAYNAME}},
                @{N='ResourceType';E={$_.ROLETYPEREF_VALUE}},
                @{N='Description';E={$_.DESCRIPTION}},
                @{N='SystemName';E={$_.SYSTEMREF_VALUE}},
                @{N='Enabled';E={$_.RESOURCESTATUS_ENGLISH -eq 'Active'}} |
  Export-Csv "$OutputFolder/Resources.csv" -Delimiter ";" -NoTypeInformation

# Users
Import-Csv "$SourceFolder/Users.csv" -Delimiter ";" |
  Select-Object @{N='ExternalId';E={$_.Employee_ID}},
                @{N='DisplayName';E={$_.Employee_fullname}},
                @{N='PrincipalType';E={if($_.Employee_Type -eq 'Employee'){'User'}else{'ExternalUser'}}},
                @{N='Department';E={$_.OU_KEY}},
                @{N='JobTitle';E={$_.Job_Title}},
                @{N='ManagerExternalId';E={$_.Managers_CorperateKey}} |
  Export-Csv "$OutputFolder/Users.csv" -Delimiter ";" -NoTypeInformation

# Assignments
Import-Csv "$SourceFolder/Account-Permission.csv" -Delimiter ";" |
  Select-Object @{N='ResourceExternalId';E={$_.ResouceUID}},
                @{N='UserExternalId';E={$_.Employee_ID}} |
  Export-Csv "$OutputFolder/Assignments.csv" -Delimiter ";" -NoTypeInformation

This is ~30 lines per source system, easily auditable, and keeps Identity Atlas clean.

Implementation details¶

Component	What it does
CSV crawler (`tools/crawlers/csv/Start-CSVCrawler.ps1`)	Reads exactly the schema column names. `Assert-Columns` validates required columns upfront with clear error messages. No `Get-Col` fallback logic.
Validation (`app/api/src/ingest/validation.js`)	`requiredOneOf` supports both UUID and ExternalId forms (e.g. `resourceId` or `resourceExternalId`).
Normalization (`app/api/src/ingest/normalization.js`)	Converts `*ExternalId` fields to deterministic UUIDs using `${sysPrefix}-resources` / `${sysPrefix}-principals` / `${sysPrefix}-identities` prefixes.
File slots (`tools/crawlers/csv/csv-slots.json`)	10 slots matching the schema files (label, required, hint). Shared by the wizard and the generic upload-schema endpoint — single source of truth, not hand-duplicated.
Upload + schema routes (`app/api/src/routes/crawlerFiles.js`)	Generic per-crawler-type file upload/list/delete, and `GET /api/admin/crawlers/csv/upload-schema` reading real template files (not a hardcoded header map).
UI wizard (`tools/crawlers/csv/ConfigWizard.jsx`)	Self-contained crawler plugin (see `docs/architecture/crawler-architecture.md`). "Download schema templates" link in the upload step.
Schema templates (`tools/crawlers/csv/schema/*.csv`)	Header-only CSV files — the canonical spec. Lives under the crawler's own folder so it's covered by the same Docker/node-launcher mirroring as the rest of `tools/crawlers/`.
Omada transform (`tools/csv-templates/transforms/omada-to-identityatlas.ps1`)	Example transform: ~160 lines mapping Omada columns to Identity Atlas schema.
Auto-classify (`POST /api/ingest/classify-business-role-assignments`)	Post-import: flags Direct assignments to BusinessRole resources as `governed`.
Backpressure fix (`app/api/src/ingest/engine.js`, `sessions.js`)	`pg-copy-streams` COPY FROM STDIN now respects write backpressure.

Design rules¶

No column-name guessing or auto-detection in the crawler
No source-specific logic in the crawler
One schema, clearly documented — user transforms their data to match
Extra columns preserved in extendedAttributes for every file except Assignments / IdentityMembers / ContextMembers
Subset imports supported (only provide what your source has)
Multi-system supported via optional SystemName column

Using the Omada transform¶

# Transform Omada exports to Identity Atlas schema
pwsh tools/csv-templates/transforms/omada-to-identityatlas.ps1 `
    -SourceFolder ./OmadaExport -OutputFolder ./ForImport

# Upload the transformed files to the CSV crawler wizard in the UI

To support a new source system, copy the Omada transform and adapt the column mappings. The crawler and Identity Atlas schema stay stable.

CSV Import Schema¶

Design principle¶

CSV files and their schemas¶

1. Systems.csv (optional)¶

2. Resources.csv (required)¶

3. Users.csv (required)¶

4. Assignments.csv (required)¶

5. ResourceRelationships.csv (optional)¶

6. Contexts.csv (optional)¶

7. Identities.csv (optional)¶

8. IdentityMembers.csv (optional)¶

9. Certifications.csv (optional)¶