Asset Import Developer Guide

Asset import — Developer Guide

Bringing assets in from a spreadsheet. The mapping screen is the easy half; this guide is mostly about the other one — reconciliation, the preview, and what happens to a row that can't be imported.

Read the typed fields engine and custom asset fields guides first — an import writes through both. User-facing page: Recording anything. Design doc: docs/design/flexible-asset-fields.md §6 (local only).

---

1. 📁 The files involved

🎨	File	What it does
⚙️	includes/services/asset_import.php	AssetImportService — parse, suggest, map, reconcile, apply, log
🗄️	database/freeitsm.sql	asset_import_profiles · _mappings · _runs · _run_entries
🗄️	includes/db_verify_schema.php · db_verify_indexes.php · api/system/db_verify.php	upgrade path, indexes (generated), 7 FKs
🔌	api/assets/import_upload.php	takes the CSV, stores it safely, returns columns + a suggested mapping
🔌	api/assets/import_run.php	preview or live — the same call, one word different
🔌	api/assets/import_history.php	past runs, one run's rows, the holding area
🔌	api/assets/import_resolve.php	mark a parked row dealt with
🖥️	asset-management/settings/index.php	the four-step wizard, the holding area, past runs
🧪	tests/asset-import.php	45 assertions

---

2. 🔑 The shape

source  ->  rows  ->  map  ->  reconcile  ->  validate  ->  apply  ->  log
\_ differs _/        \_______________ all shared _______________/

CSV and API are the same feature. A source yields rows of [column => value]; everything after that is shared. Only readCsv() knows what a file is, so the API puller changes nothing below it.

That's why source_kind exists on the profile table from day one, and why target does too — only asset is built, but a CMDB import becomes a later commit rather than a new project.

---

3. Reconciliation — the part that matters

What makes an incoming row "the same printer"? Get this wrong and run two silently duplicates 400 printers.

Declared per profile as an ordered list of match keys (hostname, asset_tag, service_tag). The first key yielding exactly one match wins.

Matches	Action
0 across every key	create
exactly 1	update
more than 1	⚠️ conflict — log it, touch nothing

A key yielding many stops the search and reports the conflict. Falling through to the next key would quietly resolve an ambiguity nobody had looked at.

Matching is always within one company. Two customers may each hold a "LAPTOP-01", and merging them would be a tenancy breach dressed up as a data merge.

🔴 The tenancy bug this nearly shipped with

getActiveTenantId() returns the Default company's real id. createAsset() normalises Default to NULL when it stores.

So the match query looked for tenant_id = 1 while every row it had just written held tenant_id IS NULL. Nothing ever matched, every row looked new, and the only thing between the estate and a full duplicate set was the unique-hostname refusal — which reports as an error, not as the reconciliation failure it actually was.

Exactly the disaster the feature exists to prevent, and invisible until the same file was run twice. The fix is three lines normalising Default to NULL before matching; the lesson is that a live second run is not optional.

---

4. Preview

Preview is not a lesser run — it's the same run, stopped before it writes. run(..., 'preview') takes the identical path and skips only the writes.

Anything a preview can't tell you is something a live run would surprise you with, which is why it must never become a separate "validation" routine. It would drift, and the first time it drifted somebody would find out by importing 500 wrong records.

The UI enforces it: Import stays disabled until a preview has run, and any change to the mapping or match keys disables it again.

---

5. 🔑 The holding area

A row that can't be imported is kept, with action = 'error' (or 'conflict') and its source line verbatim in raw_row.

Not dropped — the data and the reason are both lost. Not auto-created — that invents an asset type called "Televsion" the first time somebody typos a spreadsheet.

resolved_datetime is what empties the list; the row itself stays as the record of what happened.

This was Ed's call over both of the options originally offered, and it's better than either.

🔴 The atomicity bug

A row that created the asset and then failed on a custom field was logged as an error — while the asset quietly existed. The holding area said "failed"; a retry silently became an update.

Fixed with one transaction per row. The log entry is written outside it, because a rollback must undo the asset and never the record of what went wrong.

The assertion that proves it: "a failed row leaves NO asset behind".

---

6. Names, not ids

A spreadsheet says "Printer", not "20". resolveLookups() matches asset_type_id, asset_status_id, location_id and supplier_id on name, case-insensitively, scoped to what the company can see, with a company's own entry beating a global default of the same name. A numeric value passes straight through as an id, so an export from another FreeITSM still works.

Resolved in the importer, not in AssetsService — that service's id-only contract is the REST API's and should stay that way. Same split as translating its duplicate-hostname message for the UI.

An unknown name is an error, never an auto-create, and the message says what to do: No asset type called "Printer". Create it first, or map that column to nothing.

---

7. CSV parsing — three things that bite

The UTF-8 BOM. Excel writes one by default. Without stripping it the first header arrives as \xEF\xBB\xBFHostname, maps to nothing, and the identity column silently goes missing — so every row looks new. It's the single most common reason a first import "does nothing".

The escape character. Passed explicitly as ''. Real CSV (RFC 4180) has no escape character — a literal quote is doubled. PHP's historic backslash default is non-standard, mangles C:\Users\ in a cell, and 8.4 deprecates relying on it.

Duplicate headers. Renamed to Serial, Serial (2) rather than silently overwriting each other.

5000 rows per file, and the cap is SURFACED — in the upload result and on screen. A run that quietly imported the first 5000 of 8000 reads as a complete success.

---

8. Other deliberate limits

asset_tag can be matched on but not written. It's unique per company and that rule lives in save_asset_tag.php, not AssetsService::fieldMap(). ⚠️ Anything absent from fieldMap() is silently ignored by create/update, which is precisely the quiet nothing CORE_TARGETS exists to prevent.

Match keys are filtered to columns the install actually has. assets.asset_tag arrives with the QR-label update; naming it on an install that hasn't run Verification turns every row into "Unknown column".

Rows that vanish from the source next run: ignore | flag | deactivate, an explicit setting, never a default guess. Only ever compared against assets this profile has touched before — comparing against the whole estate would deactivate every asset the spreadsheet was never about. If deactivate is chosen and no inactive status exists, the entry says so rather than reporting a deactivation that didn't happen.

Unknown dropdown values: reject (park the row) or add (create the option).

---

9. Two more bugs worth remembering

row_number is a reserved word in MySQL 8 — the ROW_NUMBER() window function. Unbackticked, every insert into the run log was a syntax error, so the log recorded nothing at all.

started_datetime was local, finished_datetime UTC. The column default is CURRENT_TIMESTAMP (server-local) while the finish used UTC_TIMESTAMP(), so runs displayed as finishing an hour before they started. Directory sync names the column explicitly for exactly this reason.

---

10. 🔒 Uploads

Files go through uploadStoreFile() — the one place the upload rules live. Extension and mime whitelist narrowed to CSV only, a filename FreeITSM generates, and both execution guards (.htaccess and web.config) written into uploads/asset-imports/.

Those guards ship in git — uploads/asset-imports/* ignored, the two guards un-ignored — rather than waiting for a first upload to create them.

import_run.php re-reads the stored file by name rather than accepting a re-upload, so committing what you previewed can't accidentally commit a different file. The name is basename()'d and whitelisted, since it's one FreeITSM generated.

⚠️ Uploaded CSVs are never tidied up. They accumulate and want a cron sweep. Not built.

---

11. 🧪 The test

tests/asset-import.php — 45 assertions, built around what ruins an unattended import rather than "does it read a CSV". The one that matters most: the same file twice creates nothing the second time.

Also covers the BOM, duplicate headers, preview writing zero, fill vs overwrite, ambiguity refusing, the holding area keeping the row and the reason, unknown dropdown handling, rows with no identity, a typo not inventing a type, and the mapping suggester.

---

12. Not built

• Saved profiles have a table and no UI. The wizard runs one-off imports; you can't yet name a mapping and re-run it. Scheduling depends on this.
• Scheduling — and it must reuse directory sync's mechanism, not add a second one.
• The API source — source_kind = 'api' needs auth modes, a JSON root path, pagination, and 🔒 sslApplyCurl($ch) on every handle.
• Per-field source precedence. write_mode (fill / overwrite) is the blunt version. Once an asset is fed by the agent and a CSV and an API, who wins on model? Without a rule the nightly job stomps a human's correction every night and nobody notices for months.

---

See also

• Custom asset fields — Developer Guide
• Typed fields engine — Developer Guide
• Recording anything — the user-facing page
• Directory Sync — whose run/entries design this copies