Inventory → CSV Import (Container Migration)

Purpose

Bulk-import historical container records from a paper log, a spreadsheet, or another inventory system. The wizard at /home/<your-clinic>/inventory/import accepts a CSV file describing one container per row. All imported containers are tagged receiving_type='paper_migration' so the audit trail clearly distinguishes them from purchases received through the normal Add Container flow.

Regulatory basis: DEA rules permit initial inventory and historical record entry; the paper_migration audit-trail tag preserves the provenance distinction. DEA recordkeeping requires every container that ends up in your clinic's inventory to have a complete record, regardless of how it was entered.

When to use it

• First-time setup: you're moving from CS Logbook Sheets / a different vet PMS / a paper log to CS Logbook Production and need to bring forward your existing inventory.
• Periodic backfill: you discover a stash of containers that never made it into the system and want to enter them without retyping each one.
• NOT for normal receiving: containers received from a supplier with a real invoice should go through the regular Add Container flow at /home/<your-clinic>/inventory/containers, which captures supplier, invoice number, and receiving veterinarian properly.

Walkthrough

Step 1 — Add your substances first

Before any CSV import will succeed, every substance referenced by a row in the CSV must already exist in your formulary at /home/<your-clinic>/inventory/substances. The wizard resolves each CSV row's substance_name against your formulary by case-insensitive match against the substance's name or generic_name. Rows referencing a substance not in your formulary are surfaced as errors in the validation preview and excluded from the import.

Step 2 — Download the template

Navigate to /home/<your-clinic>/inventory/import and click Download CSV. A 1-row sample template downloads as container_import_template.csv with these columns:

Accepted units: ml, mg, g, units, tablets, capsules, patches. The wizard normalizes common variants (milliliter → ml, tab → tablets, etc.).

Accepted statuses: unopened, active, depleted, expired, disposed. Defaults to unopened if omitted (or active if you supplied an opened_date).

Accepted date formats: YYYY-MM-DD, MM/DD/YYYY, or YYYY/MM/DD.

Step 3 — Upload your CSV

Drag the CSV into the upload zone or click Browse Files. The wizard parses + validates each row in two passes:

1. Client-side (Zod) — checks required fields are present, dates parse, numbers are positive, units are in the allowed list.
2. Server-side — looks up the substance in your formulary, checks the container number doesn't already exist in your clinic, flags expired containers as warnings (allowed for historical migration), enforces opened_date >= received_date, enforces current_balance <= initial_quantity.

Both passes run in parallel; results are merged into the validation preview.

Step 4 — Review the validation preview

The preview shows three counters: Valid, Errors, Warnings. Each row is color-coded and expandable to show per-row error/warning messages. Click Show Only Errors to filter when you have lots of rows.

Common errors you'll see:

• Substance not found: "<name>" — add the substance to your formulary first (Step 1)
• Container number "<n>" already exists — pick a different identifier
• Opened date cannot be before received date
• Current balance cannot exceed initial quantity

Common warnings (don't block import):

• Container is expired — allowed for historical migration — the wizard expects expired rows in a paper-log import and lets them through

Step 5 — Import

Click Import N containers (button only enables when at least one row is valid). The wizard inserts every valid row into public.containers with receiving_type='paper_migration'. Rows with errors are skipped — they're not silently inserted with missing data.

After import, the completion card shows the success count + any per-row insert errors that bypassed validation (rare; usually a DB constraint that the validator didn't catch). Click View Containers to land on the regular containers list with your newly-imported rows visible.

Field reference

The CSV column → Production column mapping (case-insensitive, plus alias normalization):

Edge cases

Historical containers with future expiration: allowed. Historical containers already expired: allowed (this is the whole point of the migration path — your paper log probably has rows that have since expired). They land in the DB with expiration_date < today and the validation surfaces a warning rather than an error. Duplicate container numbers within the CSV: the wizard rejects the whole import with a clear error. Fix the CSV; re-upload. Duplicate against an existing container in your clinic: same. The validation preview tags only the affected row(s); other valid rows still import. Substance with multiple matches (e.g. same name and generic_name): wizard picks the first match. Best practice: keep your formulary clean so this never happens.

If you have a CS Logbook Sheets export

The Sheets export emits a ZIP with 17 CSV files (Containers, Substances, Suppliers, Clients, Patients, Licenses, Transactions, etc. — see docs/cslogbook/sheets-alignment.md for the full list). This wizard imports only the Containers.csv file from that ZIP, and even for Containers.csv it accepts only the 15 columns above — the Sheets export has 34 columns, so ~19 of them are silently dropped on import (including the Sheets canonical IDs like Container_ID=CNT001).

For a full Sheets → Production migration (all 17 tabs, with canonical-ID preservation via external_id), use the dedicated Sheets Migration wizard at /home/<your-clinic>/inventory/import/sheets-migration — it accepts the master cs_logbook_data.json from the ZIP and is designed to round-trip the canonical IDs. The Container CSV wizard documented here is the right path when you have a hand-prepared CSV (e.g. from a paper log) or you only need to bring forward containers.

Common mistakes

1. Forgetting to add substances to the formulary first — the wizard will error on every row; nothing imports. Fix Step 1 above.
2. Mismatched substance names — the wizard does case-insensitive matching but cannot fix typos. If your formulary has Ketamine HCl and your CSV says Ketamine, that's a no-match. Pick one canonical name + use it everywhere.
3. Using a Sheets export CSV directly — works partially for Containers (column names normalize), but you lose 19 of 34 columns silently. If you have a Sheets export, use the Sheets Migration wizard instead.
4. Trying to import transactions — the Transactions tab in this wizard is disabled. Transactions are entered through the regular UI, because controlled substance log entries stay append-only under DEA rules.
5. Importing twice and getting unique-key collisions — every container_number must be unique per clinic. If you need to re-import, soft-delete the previous batch first.

Related

• Containers — the regular Add Container flow for normal supplier receiving
• Substances — the formulary the CSV import resolves substance_name against
• Suppliers — how supplier records are matched during an import
• Sheets Alignment — full Sheets ↔ Production schema reference