Secure Bulk Resident Data Import and Validation for AFH Software

Bulk import can accelerate migration into adult family home software, but it can also create duplicate residents, attach records to the wrong facility, misread dates, or activate medications without verified orders. A safe importer stages data, explains every transformation, and requires reconciliation before protected records become operational.

This guide covers data-import engineering, not which records a provider should migrate or retain. It was reviewed on August 8, 2026. Providers should verify source authority, privacy, retention, resident identity, current orders, facility policy, and applicable requirements.

Define the import scope precisely

Create separate import types for:

• Resident demographics and contacts
• Caregiver profiles and facility assignments
• Medication lists and order history
• Appointments
• Care plans or tasks
• Documents and metadata
• Historical MAR events
• Facility settings

Do not accept one undefined spreadsheet containing unrelated modules. Each template needs a schema version, required fields, permitted optional fields, date and unit formats, identifier rules, and activation behavior.

State whether the import creates drafts, active records, or review cases. Medication and access data deserve stricter gates than nonclinical preferences.

Establish facility context before upload

The authorized user must select the destination facility and import type before choosing a file. Display both throughout mapping, preview, and confirmation.

The server derives tenant access from the authenticated session and validates the selected facility. Never trust a facility ID embedded in a CSV row or URL parameter by itself.

For a multi-facility file, require an explicitly approved platform workflow with row-level tenant validation and separate reconciliation. A normal facility import should reject rows naming another facility.

Quarantine and validate the file

Upload to private quarantine using a generated object key. Enforce type and size limits, scan for malware, and reject executable or active content.

For CSV or spreadsheet input:

• Detect encoding
• Limit rows, columns, cell length, and formulas
• Treat formulas as data or reject them
• Normalize line endings safely
• Guard against CSV injection on later exports
• Reject archive bombs and extreme workbook complexity
• Parse in an isolated worker

The OWASP Input Validation guidance emphasizes validating untrusted input as early as practical. File extension and browser Content-Type alone are not sufficient.

Preserve the original source

Store the original file checksum, filename as display metadata, uploader, upload time, source organization, declared data-through date, parser version, and schema selection.

Never rewrite and replace the uploaded source after mapping. Create a normalized staging dataset and keep a trace from each staged row and field to the original location.

If policy does not permit retaining the original file, preserve the approved manifest, checksum, transformation report, and deletion event according to the import plan.

Map columns explicitly

Show source headers, sample values, destination fields, transformation rules, and unmapped columns. Require confirmation for ambiguous mappings.

Do not map based only on column position. Detect duplicate headers, hidden columns, unexpected formulas, and similar names such as “start date,” “admission date,” and “prescribed date.”

Allow saved mapping templates only when bound to source organization, import type, and schema version. Revalidate them when either schema changes.

Normalize without destroying source values

Store both original and normalized values for:

• Names and identifiers
• Dates and times
• Phone numbers
• Medication names and codes
• Units and quantities
• Boolean values
• Status labels

Do not guess whether 08/09/26 means August 9 or September 8 when source locale is unclear. Require an import-level date rule and preview the interpreted dates.

Do not strip leading zeros from resident, NPI, NDC, or package identifiers by reading them as numbers.

Match residents conservatively

Compare facility-scoped stable identifiers first, then combinations such as legal name, date of birth, prior record ID, and other approved identity evidence.

Classify rows as exact match, probable match needs review, new resident candidate, conflict, or insufficient identity. Never merge two resident profiles automatically from name similarity alone.

The resident identity verification guide explains duplicate-profile prevention and controlled merges.

Show side-by-side existing and incoming values. A reviewer can select update, retain existing, create new, or hold for clarification field by field.

Stage medications as reconciliation work

An imported medication list is a source, not an active resident order. Preserve medication, strength, form, dose, route, frequency, times, dates, prescriber, and source text, then open reconciliation or order-review cases.

Use RxNorm and NDC data only to assist identity matching. Do not invent administration directions or activate a schedule from a catalog match.

Historical MAR import requires its own schema for intended time, actual time, outcome, author, entry time, order version, and amendments. Do not flatten it into current medication rows.

Validate relationships and references

Check that:

• Every resident belongs to the target facility
• Caregiver assignments reference known or staged users
• Medication events reference the intended resident and order
• Documents reference valid resident or facility records
• Appointment participants exist
• Parent-child and version relationships are acyclic
• Status transitions are permitted

Reject orphaned or cross-facility references. Do not create placeholder residents silently to satisfy foreign keys.

Present a deterministic preview

Before commitment, show:

• Rows accepted, warned, blocked, or skipped
• New records and proposed updates
• Duplicate candidates
• Cross-facility rejections
• Date and unit conversions
• Medication cases requiring review
• Files that remain quarantined
• Records that will stay drafts

Let users download a clean error report with source row number, field, issue, and correction guidance. Avoid exposing one resident's details to users who lack access.

The preview must be generated from the same normalized staging version that the commit will use.

Commit atomically by bounded batch

Give the import a stable identifier. Each staged item receives an idempotency key derived from import, entity type, and source row or object ID.

Commit in transactional batches small enough to retry safely. A repeated request returns the existing item result instead of creating a duplicate.

If the import stops, display exactly which batches committed and which remain staged. Do not report overall success after only some entities were written.

Support rollback through compensating actions

Preview a rollback plan before import. New unreferenced drafts may be safely removed; updates to existing records require versioned compensation, not destructive restoration.

Never roll back signed care events by deleting them. Create correction or supersession events as appropriate and preserve the import relationship.

Record the person authorizing rollback, scope, reason, created records, restored versions, unresolved conflicts, and verification.

Protect privacy and audit access

WAC 388-76-10315 establishes confidentiality and protection duties for resident records. Apply facility-aware authorization to uploads, staging rows, mapping previews, error exports, commits, and cleanup.

Avoid putting source row content into general logs. Use import, row, and field identifiers so authorized support can investigate through a protected tool.

Expire abandoned staging data and quarantined files under a documented lifecycle. Audit views, downloads, mapping changes, approvals, commits, rollback, and disposal.

Reconcile after commitment

Run independent checks:

• Imported entity counts match committed results
• No duplicate resident stable identifiers
• Every created record has target facility scope
• Medication imports remain pending until verified
• Active orders have appropriate future schedules
• Historical events preserve timestamps and authorship source
• Documents exist and checksums match
• Dashboard totals and reports include intended records

Keep the import open until reconciliation passes or exceptions have owners. A green upload status is not enough.

Publish the reconciliation result as a versioned import summary. Include the schema and mapping versions, data-through date, committed counts by entity, warnings accepted by reviewers, blocked rows, post-commit exceptions, and the person who verified completion. A later correction should create a related report rather than replacing the first result.

Test hostile and messy datasets

Use synthetic data to verify:

1. Duplicate resident names with different birth dates.
2. Same resident appears twice in one file.
3. Ambiguous date format.
4. Leading-zero identifiers.
5. Cross-facility resident reference.
6. Formula and CSV injection strings.
7. Oversized cells and excessive rows.
8. Invalid medication relationship.
9. Historical event without an order reference.
10. Interrupted commit and idempotent retry.
11. Schema changes after mapping is saved.
12. Partial batch failure.
13. Rollback of new drafts.
14. Attempted rollback of signed MAR history.
15. Error report authorization.
16. Post-import reconciliation mismatch.

Inspect the database, files, search results, resident profiles, and reports—not only the success screen.

Frequently asked questions

Should imported medications become active automatically?

No. Treat the import as source evidence and route medications through reconciliation and verified order activation.

Can the system merge residents by matching names?

No. Use facility-scoped identifiers and multiple identity fields, then require review for probable matches.

What happens if the import stops halfway?

Show committed and remaining batches, resume with stable item keys, and reconcile final outputs. Do not restart blindly.

Can an import be deleted?

Draft staging can expire under policy. Committed resident and care records require versioned correction or approved compensation, not destructive deletion.

Why retain original values after normalization?

They allow reviewers to verify date, identifier, unit, and mapping decisions and explain how each destination value was produced.

Make migration controlled and reversible

Safe bulk import isolates the file, preserves source values, maps explicitly, matches residents conservatively, stages medication review, commits idempotently, and reconciles every output before completion.

Explore AFH Manager to test resident import staging, duplicate review, medication reconciliation, deterministic previews, bounded rollback, and facility-scoped audit reports with synthetic data.