Airtable Repair Scripts

sync-patients-from-old.js

Uses Airtable Patients (OLD) as the source of truth for referring-pharmacy links and updates the consolidated Patients table to match.

Default behavior is dry-run:

node scripts/sync-patients-from-old.js

Apply the updates:

node scripts/sync-patients-from-old.js --apply

Optional env vars if your field names differ:

AIRTABLE_OLD_PATIENTS_TABLE_NAME=Patients (OLD)
AIRTABLE_OLD_PATIENTS_NAME_FIELD=Display name
AIRTABLE_OLD_PATIENTS_EMAIL_FIELD=Email
AIRTABLE_OLD_PATIENTS_PHONE_FIELD=Mobile phone
AIRTABLE_OLD_PATIENTS_REFERRING_PHARMACY_FIELD=Referring pharmacies

sync-patients-from-bp.js

Imports a Best Practice patient CSV into Airtable Patients. Default behavior is dry-run:

node scripts/sync-patients-from-bp.js

Apply the updates/creates:

node scripts/sync-patients-from-bp.js --apply

Use a different BP export:

node scripts/sync-patients-from-bp.js --csv "BPS Search Result 2026052750508.csv"

Optional env vars/flags if Airtable field names or category names differ:

AIRTABLE_PATIENTS_CATEGORY_FIELD=Record category
AIRTABLE_PATIENTS_BP_CATEGORY=BP patient import
AIRTABLE_PATIENTS_BP_ID_FIELD=BP patient ID

The script matches existing BP patients by BP ID, email, phone, then unique name. Rows with duplicate BP IDs or exact duplicate source patient details across multiple BP IDs are skipped and reported for manual review. New records with duplicated source names are also skipped by default; pass --allow-duplicate-name-creates only after confirming those duplicated names are genuinely separate patients.

Recommended workflow:

1. Export the patient search result CSV from BP and place it in the project root.
2. Confirm Airtable Patients has a single line text field named BP patient ID.
3. Run a dry-run:

npm run patients:sync-bp -- --csv "BPS Search Result 2026052750508.csv"

4. Review the summary counts and exception lists.
5. Apply only after the dry-run looks right:

npm run patients:sync-bp -- --csv "BPS Search Result 2026052750508.csv" --apply

6. Run the dry-run again. A clean applied safe set should show Updates: 0 and Creates: 0; any remaining rows should be exception buckets.

Exception handling:

• Skipped duplicate source patients: BP has multiple rows with the same name, email, and phone but different BP IDs. Check BP directly and decide which BP ID is canonical. Update the existing Airtable patient manually with the chosen BP patient ID, or create separate Airtable patients only if BP confirms they are genuinely different people.
• Skipped duplicate BP IDs: the CSV contains the same BP internal ID more than once. Check the BP export for duplicate rows before importing. Do not apply these automatically.
• Skipped duplicate-name creates: the CSV row did not safely match an existing Airtable patient, and the same name appears elsewhere in the BP export. Search Airtable and BP by full name, DOB if available in BP, email, and mobile. If it is the same person, add the BP ID to the existing Airtable record. If it is a separate person, create it manually or rerun with --allow-duplicate-name-creates only after reviewing the full list.
• Existing BP patients not matched by this CSV: Airtable has BP-import patients that were not matched to the current BP export. These may be renamed patients, old/test records, records missing from the export scope, or duplicates already represented by another Airtable row. Search BP by name/mobile/email and either add the correct BP ID, merge/delete the duplicate Airtable record if appropriate, or leave it unchanged with a note if it is intentionally outside the current export.
• Ambiguous: one BP source row matched more than one Airtable patient through different identifiers. Resolve manually by comparing BP and Airtable details; do not apply automatically.

The script deliberately does not import DOB, Medicare, address, or other clinical/demographic fields from BP. It only writes Name, Email, Phone, Record category, and BP patient ID.