# Import API: migrating the classic ASP shopdb through HTTP alone

This is the operator manual for importing the legacy Classic-ASP shopdb database
(`prodscratch` on the dev MySQL container) into shopdb-flask using ONLY the HTTP
API. No direct writes to the `shopdb_flask` database are needed or wanted: every
row is created through a documented endpoint so authorization, validation,
auditing, and plugin hooks all run exactly as they do for a human operator.

An LLM or a plain Python script can run the whole migration from this document.

Contents:

1. [Prerequisites](#1-prerequisites)
2. [Order of operations](#2-order-of-operations)
3. [Full table-by-table mapping](#3-full-table-by-table-mapping)
4. [Tables with no target yet](#4-tables-with-no-target-yet)
5. [Idempotency recipe and a worked importer](#5-idempotency-recipe-and-a-worked-importer)
6. [Verification: row-count parity](#6-verification-row-count-parity)

---

## 1. Prerequisites

### Dev URLs

- Flask API: `http://localhost:5001`
- All import calls target `/api/...` on that host.

### Admin token

Every write needs a JWT, and import mode additionally needs an admin. Get one:

```bash
curl -s http://localhost:5001/api/auth/login \
  -H 'Content-Type: application/json' \
  -d '{"username":"<admin>","password":"<password>"}' | jq -r '.data.access_token'
```

Send it on every request as `Authorization: Bearer <token>`.

### Import mode: the `X-Import-Mode` header

By default the server stamps `createddate`/`modifieddate` to "now" on every
create and update, which would erase a migrated row's real history. To preserve
it, send the request header:

```
X-Import-Mode: true
```

When (and only when) the caller is an admin AND that header is present:

- create/update endpoints on timestamped entities accept optional
  `createddate` and `modifieddate` fields in the JSON body and store them
  verbatim (naive UTC). Both `2020-01-05T12:00:00` (ISO) and the legacy
  `2020-01-05 12:00:00` (MySQL) forms are parsed. A bare `2020-01-05` works too.
- the selfhosted USB checkout/checkin endpoints accept optional `checkouttime`
  and `checkintime` overrides so historical events keep their real timestamps.

Without the header, or for a non-admin caller, those fields are silently ignored
and the server behaves exactly as it does normally. This is enforced centrally
by `shopdb/utils/import_mode.py` (`import_mode_active`, `apply_import_timestamps`,
`parse_import_datetime`), exposed on the plugin contract surface `shopdb.api`.

Timestamped entities that honor `createddate`/`modifieddate`: assets (all five
type plugins), vendors, models, modeltypes, businessunits, locations, operating
systems, applications, knowledge base, USB devices, asset relationships.

Entities that carry history in domain fields instead (createddate passthrough is
a no-op there, by design): notifications (`starttime`/`endtime`), warranties
(`startdate`/`enddate`/`lastcheckeddate`). Set those fields directly in the
payload; they are already accepted.

### Reference data seed

Before importing, seed the reference tables that have no CRUD endpoint of their
own (communication types such as IP/Serial/USB, default statuses, canonical
relationship types, permissions, settings):

```bash
flask seed permissions
flask seed settings
flask seed reference-data
```

`communicationtypes` (the target of legacy `comstypes`) is populated here, so
the primary-IP mapping below can resolve `comtype='IP'`.

---

## 2. Order of operations

Import in dependency order so foreign keys always resolve. Each step is a
lookup-then-upsert loop (see section 5); rerunning any step is safe.

1. **Reference / lookup data first**
   1. Vendors (`vendors`)
   2. Model types (`modeltypes`) - from legacy `machinetypes`
   3. Models (`models`) - needs vendors + model types
   4. Business units (`businessunits`)
   5. Location types, then Locations (`locations/types`, `locations`) - the
      legacy LocationOnly machines land here, not as assets
   6. Operating systems (`operatingsystems`)
   7. Asset statuses (`assets/statuses`) - from legacy `machinestatus`
   8. Relationship types (`assets/relationshiptypes`) - from legacy
      `relationshiptypes`
   9. Notification types (`notifications/types`)
   10. Per-plugin subtypes: computer types (from `pctype`), machine types,
       printer types, network device types, measuring-tool types
   11. Support teams + support-team contacts (`supportteams`,
       `supportteams/{id}/contacts` - see section 3.3); import these BEFORE
       applications because `applications.supportteamid` points at them
   12. Applications (`applications`) and their versions; import legacy `topics`
       as applications too (KB links to applications, section 3)
2. **Assets, per type** (each creates the core Asset row plus its extension):
   computers, machines, printers, network devices, measuring tools, and USB
   devices. Fan out the legacy `machines` table by category (section 3).
3. **Communications**: the primary IP is set through the asset payload's
   `ipaddress` field during step 2. There is no bulk-communications endpoint;
   see the mapping note.
4. **Relationships** (`assets/relationships`): needs both endpoint assets and
   the relationship types to already exist.
5. **Installed applications**: attach apps to computers
   (`computers/{id}/apps`), needs computers + applications.
6. **Knowledge base, notifications, warranties, USB checkouts** (including
   backdated history).
7. **Custom fields**: for any legacy column with no home in the target schema,
   define a custom field for the asset type and store the value per asset.

---

## 3. Full table-by-table mapping

Legend: `->` maps to. Endpoints are relative to `http://localhost:5001`. "NK"
is the natural key used for the idempotent lookup (section 5).

### 3.1 The `machines` hub fans out into the asset plugins

`machines` (885 rows) is the central legacy asset table. Two columns drive the
fan-out: `machinetypeid` (what the asset physically is) and `pctypeid` (a
computer's sub-type). Route each row by `machinetypeid`:

| legacy `machinetypeid` | `machinetypes.machinetype` | target plugin | subtype source |
|---|---|---|---|
| 1 | LocationOnly (also `islocationonly=1`) | core **Locations** (NOT an asset) | `locationtype` |
| 33 | PC | **computers** | `computertype` <- `pctype.typename` via `machines.pctypeid` |
| 20 | Server | **computers** | computer type "Server" |
| 15 | Printer | **printers** | `printertype` |
| 16 Access Point / 17 IDF / 18 Camera / 19 Switch / 46 Firewall | | **network** | `networkdevicetype` |
| 44 | USB Device | **usb** | (usb device) |
| 23 Measuring Machine / 3 CMM / 48 Spline Checker / 8 Eddy Current / 47 Inspection | | **measuringtools** (gage-lab judgment call; ADR-005) | `measuringtooltype` |
| 2,4,5,6,7,9,10,11,12,13,14,21,22,24,25,45 (lathes, mills, welders, grinders, ...) | | **machines** | `machinetype` |

This mapping is a recommended default, not a hard rule; a site may re-route a
`machinetypeid` (for example send CMM to `machines` rather than
`measuringtools`). Decide the routing table once, up front.

Common `machines` columns -> core Asset fields (same for every target plugin):

| legacy column | target field | notes |
|---|---|---|
| `machinenumber` | `assetnumber` | the business identifier / NK |
| `alias` or `hostname` | `name` | layperson label |
| `serialnumber` | `serialnumber` | |
| `machinestatusid` | `statusid` | remap via `machinestatus` -> asset statuses |
| `businessunitid` | `businessunitid` | remap via imported business units |
| `mapleft` | `mapx` | |
| `maptop` | `mapy` | |
| `machinenotes` | `notes` | |
| `dateadded` | `createddate` | import mode only |
| `lastupdated` | `modifieddate` | import mode only |

Per-plugin extension fields:

- **computers** (`POST /api/computers`): `hostname` <- `machines.hostname`,
  `osid` <- remapped `machines.osid`, `computertypeid` <- computer type from
  `pctype`, `loggedinuser`, `lastboottime`, `vendorid`, `modelnumberid`,
  `ipaddress` <- `machines.ipaddress1` (primary IP). NK: `assetnumber`.
- **machines** (`POST /api/machines`): `machinetypeid`, `vendorid`,
  `modelnumberid`, `controllervendorid`/`controllermodelid` (from
  `controllertypes` remapped to vendors/models), `requiresmanualconfig` <-
  `requires_manual_machine_config`, `islocationonly`. NK: `assetnumber`.
- **printers** (`POST /api/printers`): see 3.2 (authoritative source is the
  legacy `printers` table).
- **network** (`POST /api/network`): `networkdevicetypeid`, `hostname`,
  `vendorid`, `ipaddress` <- `machines.ipaddress1`. NK: `assetnumber`.
- **measuringtools** (`POST /api/measuringtools`): `measuringtooltypeid`,
  calibration fields where known. NK: `assetnumber`.

### 3.2 Reference and lookup tables

| legacy table | target endpoint | field mapping | NK |
|---|---|---|---|
| `vendors` | `POST /api/vendors` | `vendor` -> `vendor` | `vendor` |
| `machinetypes` | `POST /api/modeltypes` | `machinetype` -> `modeltype`; set `category` (Equipment/Computer/...) | `modeltype` |
| `models` | `POST /api/models` | `modelnumber`, `vendorid` (remapped), `machinetypeid` -> `modeltypeid`, `notes`, `image` -> `imageurl`, `documentationpath` -> `documentationurl` | `modelnumber` + `vendor` |
| `businessunits` | `POST /api/businessunits` | `businessunit` -> `businessunit` | `businessunit` |
| `operatingsystems` | `POST /api/operatingsystems` | `operatingsystem` -> `osname` | `osname` (+`osversion`) |
| `machinestatus` | `POST /api/assets/statuses` | `machinestatus` -> `status` | `status` |
| `relationshiptypes` | `POST /api/assets/relationshiptypes` | `relationshiptype` -> `relationshiptype`, `description` | `relationshiptype` |
| `notificationtypes` | `POST /api/notifications/types` | `typename`, `typedescription`, `typecolor` | `typename` |
| `pctype` | `POST /api/computers/types` | `typename` -> `computertype`, `description` | `computertype` |
| `subnettypes` | (see subnets) | used as `subnettype` string on subnets | - |
| `subnets` | `POST /api/network/subnets` | `cidr`, `description` -> `name`/`description`, `vlan` -> create VLAN first (`POST /api/network/vlans`) then `vlanid`, `subnettypeid` -> `subnettype` name | `cidr` |
| `dashboarddefaults` | `POST /api/dashboarddefaults` | `ipaddress` -> `ipaddress`, `businessunitid` (remapped), `description` | `ipaddress` |
| `controllertypes` | remap into `vendors` + `models` | e.g. "Fanuc" -> a Vendor; the controller model -> a Model; then set `controllervendorid`/`controllermodelid` on the machine | - |
| `comstypes` | `communicationtypes` (seeded, no API) | ensure `flask seed reference-data` created IP/Serial/USB/... before importing comms | - |

Note on communication types: the classic `comstypes.typename` values
(IP, Serial, Network_Interface, USB, Parallel, VNC, FTP, DNC) correspond to the
seeded `communicationtypes.comtype`. They are created by the reference-data seed,
not imported per-row.

### 3.3 Support teams, applications, topics, installed apps

Support teams and their contacts import BEFORE applications, because
`applications.supportteamid` references a team. The legacy `appowners` table
is folded into contacts: each legacy `supportteams` row carries one
`appownerid`, so import that owner as ONE contact on the team (legacy
`appowner` -> `name`, `sso` -> `sso`).

| legacy table | target endpoint | field mapping | NK |
|---|---|---|---|
| `supportteams` | `POST /api/supportteams` | `teamname`, `teamurl` (ServiceNow group deep link) | `teamname` |
| `appowners` (via each team's `appownerid`) | `POST /api/supportteams/{supportteamid}/contacts` | `appowner` -> `name`, `sso` -> `sso`, `sortorder` (default 0) | (supportteamid, name) |
| `applications` | `POST /api/applications` | `appname`, `appdescription`, `supportteamid` (remap by team `teamname`, GET `/api/supportteams?teamname=...`), `isinstallable`, `applicationnotes`, `installpath`, `applicationlink`, `documentationpath`, `ishidden`, `isprinter`, `islicenced`, `image` | `appname` |
| `appversions` | `POST /api/applications/{appid}/versions` | `version`, `releasedate`, `notes` | `version` (per app) |
| `topics` | `POST /api/applications` | `topics` is a near-clone of `applications` and `knowledgebase.appid` points at it; import each distinct topic as an Application (`appname` = topic name), so KB links resolve against `applications` | `appname` |
| `installedapps` | `POST /api/computers/{computerid}/apps` | body `{appid, appversionid}`; resolve `machineid` -> the imported computer, `appid`/`appversionid` -> imported app + version | (computerid, appid) |

`installedapps` only makes sense for computer-class assets; skip rows whose
`machineid` did not map to a computer.

### 3.4 Communications

| legacy table | target | field mapping | notes |
|---|---|---|---|
| `communications` (comstypeid=1, isprimary) | asset `ipaddress` on create/update | `address` -> `ipaddress` | Sets the primary IP communication for the asset. |
| `communications` (other comstypeids / secondary rows) | none yet | | No bulk-communication create endpoint exists. Import the primary IP only; capture extra interfaces as custom fields, or defer. |

### 3.5 Relationships

| legacy table | target endpoint | field mapping |
|---|---|---|
| `machinerelationships` | `POST /api/assets/relationships` | `machineid` -> `sourceassetid` (the imported asset id), `related_machineid` -> `targetassetid`, `relationshiptypeid` -> `relationshiptypeid` remapped by name, `relationship_notes` -> `notes` |

Suggested legacy-name -> target relationship-type mapping (create these types
first, or map onto the canonical `partof`/`controls`/`connectedto`):

| legacy `relationshiptype` | recommended target |
|---|---|
| Controls | Controls |
| Controlled By | Controls (reverse the source/target) |
| Dualpath | Dualpath (or `connectedto` with label "dualpath") |
| Cluster Member | partof |
| Backup For | Backup For |
| Master-Slave | Controls |
| Contains | partof |
| Stored At | Stored At |
| Connected To | connectedto |

Resolve each machine id to the asset id you got back when you created that
asset (keep a `legacy_machineid -> assetid` map as you import).

### 3.6 Knowledge base, notifications, warranties, USB

| legacy table | target endpoint | field mapping | NK |
|---|---|---|---|
| `knowledgebase` | `POST /api/knowledgebase` | `shortdescription`, `linkurl`, `keywords`, `appid` (remapped to the imported application/topic); `lastupdated` -> `modifieddate` in import mode | `linkurl` (fallback `shortdescription`) |
| `notifications` | `POST /api/notifications` | `notification`, `notificationtypeid` (remapped), `businessunitid` (remapped), `starttime`, `endtime`, `ticketnumber`, `link`, `isshopfloor`, `employeesso`; note legacy `endtime` sentinel `2099-00-03 09:52:32` is invalid - drop or clamp it | `ticketnumber` when set, else append-only |
| `warranties` | `POST /api/warranty` | `warrantyname`/`servicelevel` -> `servicelevel`, `enddate` -> `enddate`, link the covered asset via `assetids: [assetid]`; set `vendor` (required) from the source or "Dell"; `servicetag` if known | `servicetag` + `vendor` |
| `usbcheckouts` | see below | historical checkout/checkin events | - |

USB devices and their history:

1. Create each USB device (legacy `machines` rows with `machinetypeid=44`, or a
   dedicated device list) via `POST /api/usb` in selfhosted mode with body
   `{device_id: <serial>, device_desc, locker_location}`. NK: `device_id`.
2. Replay each `usbcheckouts` row as a checkout then (if returned) a checkin,
   with import-mode backdating:
   - `POST /api/usb/{device_id}/checkout` body
     `{badge: <sso>, reason: <checkout_reason>, checkouttime: <checkout_time>}`
   - if `checkin_time` is set:
     `POST /api/usb/{device_id}/checkin` body
     `{badge: <sso>, sanitized: <was_wiped>, notes: <checkin_notes>, checkintime: <checkin_time>}`

   The `checkouttime`/`checkintime` overrides are honored only in import mode.

### 3.7 Anything unmappable -> custom fields

For a legacy column with no target field (for example `machines.logicmonitorurl`,
`machines.fqdn`, `printers.printerpin`), define a custom field on the asset type
and store the value per asset:

- `POST /api/customfields` body `{assettypeid, label, datatype}` (once per field)
- `PUT /api/customfields/asset/{assetid}` body `{values: {<fieldid>: <value>}}`

Custom-field values are not timestamped, so they carry no history.

---

## 4. Tables with no target yet

These legacy tables have no import target in the current schema. The
dispositions below are DECIDED, not open questions.

### DECIDED: not migrated

- **`dncconfig` and `commconfig`** - intentionally NOT migrated. DNC
  communication settings drift constantly, so a one-shot import of stale data
  has little value. The plan is a future DNC feature fed live by the GE-Enforce
  collector/reporting tool rather than a historical import. When that DNC
  support is eventually built, the expected ingestion pattern is one attribute
  at a time across the whole facility (for example, sweep every machine's baud
  rate in one pass, then ports, and so on) via the GE-Enforce collector, so the
  future design should favor per-field fleet-wide updates over per-machine
  full-record imports.

### DECIDED: skip (structure only or low value)

- **`compliance`, `compliancescans`** - 0 rows in `prodscratch`. No data to
  migrate; a future compliance plugin would own them. Skip.
- **`ednc_installations`, `ednc_logs`** - 0 rows, and they belong to the eDNC
  tooling rather than the asset catalog. Skip.
- **`distributiongroups`** (2 rows) - email distribution lists referenced by
  `businessunits.distributiongroupid`. No target; skip, or attach as a business
  unit custom field if a site needs it.
- **`functionalaccounts`** (7 rows) - service-account concept referenced by
  `pctype`/`machinetypes`; no equivalent in the new schema. Skip, or capture as
  a computer-type custom field.
- **`skilllevels`** (2 rows) - orphaned lookup (no FK from `machines`). Skip.

---

## 5. Idempotency recipe and a worked importer

The endpoints are NOT upserts. The idempotent unit is a two-step recipe that
composes with import mode:

1. **Look up** the row by its natural key using the exact-match list filter.
2. If found, **PUT** to update it; if not, **POST** to create it.

Each import-relevant list endpoint has an exact-match filter for its natural key
(added for exactly this purpose):

| entity | lookup |
|---|---|
| assets (all 5 plugins) | `GET /api/{plugin}?assetnumber=<n>` |
| vendors | `GET /api/vendors?vendor=<name>` |
| models | `GET /api/models?modelnumber=<m>&vendor=<vendorid>` |
| model types | `GET /api/modeltypes?modeltype=<name>` |
| business units | `GET /api/businessunits?businessunit=<name>` |
| locations | `GET /api/locations?locationname=<name>` |
| operating systems | `GET /api/operatingsystems?osname=<name>` |
| applications | `GET /api/applications?appname=<name>` |
| knowledge base | `GET /api/knowledgebase?linkurl=<url>` |
| warranties | `GET /api/warranty?servicetag=<tag>&vendor=<name>` |
| notifications | `GET /api/notifications?ticketnumber=<t>` |
| USB devices | `GET /api/usb/{device_id}` (exact by id) |

### Worked example

A small, dependency-free importer (`requests`) that logs in, does the
lookup-then-upsert loop in import mode, supports a `--dry-run` flag, and reports
errors without aborting the whole run:

```python
import argparse
import requests

BASE = "http://localhost:5001"


class ImportClient:
    def __init__(self, username, password, dryrun=False):
        self.session = requests.Session()
        self.dryrun = dryrun
        resp = self.session.post(
            f"{BASE}/api/auth/login",
            json={"username": username, "password": password},
        )
        resp.raise_for_status()
        token = resp.json()["data"]["access_token"]
        # X-Import-Mode makes createddate/modifieddate passthrough take effect.
        self.session.headers.update({
            "Authorization": f"Bearer {token}",
            "X-Import-Mode": "true",
        })

    def lookup(self, path, params):
        """Return the first matching row, or None."""
        resp = self.session.get(f"{BASE}{path}", params=params)
        resp.raise_for_status()
        rows = resp.json().get("data") or []
        return rows[0] if rows else None

    def upsert(self, path, idfield, lookupparams, payload):
        """Lookup by natural key; PUT if found, else POST. Returns the row."""
        existing = self.lookup(path, lookupparams)
        if self.dryrun:
            verb = "PUT" if existing else "POST"
            print(f"[dry-run] {verb} {path} {lookupparams}")
            return existing or payload
        if existing:
            rowid = existing[idfield]
            resp = self.session.put(f"{BASE}{path}/{rowid}", json=payload)
        else:
            resp = self.session.post(f"{BASE}{path}", json=payload)
        if resp.status_code >= 400:
            # report and keep going; a single bad row must not abort the run
            print(f"ERROR {resp.status_code} {path}: {resp.text[:200]}")
            return None
        return resp.json()["data"]


def import_vendors(client, legacyrows):
    for row in legacyrows:
        client.upsert(
            "/api/vendors",
            idfield="vendorid",
            lookupparams={"vendor": row["vendor"]},
            payload={
                "vendor": row["vendor"],
                # legacy history preserved because X-Import-Mode is set
                "createddate": row.get("dateadded"),
                "modifieddate": row.get("lastupdated"),
            },
        )


if __name__ == "__main__":
    parser = argparse.ArgumentParser()
    parser.add_argument("--user", required=True)
    parser.add_argument("--password", required=True)
    parser.add_argument("--dry-run", action="store_true")
    args = parser.parse_args()

    client = ImportClient(args.user, args.password, dryrun=args.dry_run)
    # read legacy rows from prodscratch (read-only) and call the import_* fns
    # in the order of section 2, keeping a legacy-id -> new-id map as you go.
```

Keep a `legacy_id -> new_id` dictionary for every entity as you import it; you
need it to remap foreign keys (a machine's `businessunitid`, a checkout's
`machineid`, a relationship's `machineid`/`related_machineid`, and so on).

---

## 6. Verification: row-count parity

After each phase, compare counts. Legacy side (read-only), for example:

```bash
docker exec dev-mysql mysql -uroot -prootpassword prodscratch \
  -e "SELECT COUNT(*) FROM vendors;"
```

New side, via the API pagination metadata (`meta.pagination.total`):

```bash
curl -s "http://localhost:5001/api/vendors?per_page=1" \
  -H "Authorization: Bearer <token>" | jq '.meta.pagination.total'
```

Suggested parity checks:

| entity | legacy count | new count |
|---|---|---|
| vendors | `SELECT COUNT(*) FROM vendors` | `GET /api/vendors` total |
| models | `SELECT COUNT(*) FROM models` | `GET /api/models` total |
| business units | `SELECT COUNT(*) FROM businessunits` | `GET /api/businessunits` total |
| applications | `SELECT COUNT(*) FROM applications` | `GET /api/applications?showhidden=true` total |
| knowledge base | `SELECT COUNT(*) FROM knowledgebase WHERE isactive` | `GET /api/knowledgebase` total |
| computers | `SELECT COUNT(*) FROM machines WHERE machinetypeid IN (33,20)` | `GET /api/computers` total |
| machines | `SELECT COUNT(*) FROM machines WHERE machinetypeid IN (2,4,5,6,7,9,10,11,12,13,14,21,22,24,25,45)` | `GET /api/machines` total |
| printers | `SELECT COUNT(*) FROM printers WHERE isactive` | `GET /api/printers` total |
| network devices | `SELECT COUNT(*) FROM machines WHERE machinetypeid IN (16,17,18,19,46)` | `GET /api/network` total |
| USB devices | `SELECT COUNT(*) FROM machines WHERE machinetypeid=44` | `GET /api/usb` total |
| relationships | `SELECT COUNT(*) FROM machinerelationships WHERE isactive` | per-asset `GET /api/assets/{id}/relationships` |
| USB checkouts | `SELECT COUNT(*) FROM usbcheckouts` | `GET /api/usb/checkouts` |

Exact counts will differ where the fan-out routing table (section 3.1) sends a
`machinetypeid` to a different plugin than the example above; adjust the legacy
`WHERE` clause to match the routing you chose. Investigate any gap beyond that.
