hermes-config/skills/software-development/pocketbase-development/references/customer-db-integration.md

SPQ Customer DB Integration Pattern

Shared Helper Library (all forms)

src/lib/customerName.ts provides three reusable functions used by AppointmentModal, ROForm, CustomerInfoPanel, and CustomerFormModal:

• composeName(first, middle, last) — joins parts → single name string
• parseName(fullName) — splits → { firstName, middleName, lastName } (first-word/last-word heuristic)
• findMatchingCustomer<T>(customers, query) — generic matcher: phone (exact 3+ digits) → exact full name → partial name

The appointment and RO forms use client-side matching on the loaded customers array (400ms debounce). The quote form (CustomerInfoPanel) uses PB queries with its own 600ms debounce.

Name Split (firstName / middleName / lastName)

CustomerInfo in SPQ stores both a single name field (for backward compat with existing code reading customerInfo.name) AND separate firstName, middleName, lastName fields for granular display/editing.

Auto-compose rule (in src/store/quote.ts)

When setCustomerInfo() is called, if firstName, middleName, or lastName changed, the store auto-composes name from the three parts joined by spaces. Empty parts are filtered out.

When name is passed directly (legacy callers), it auto-splits into first/last using .split(/\s+/) — first word → firstName, rest → lastName, middleName left empty.

setCustomerInfo: (info) => set((s) => {
  const merged = { ...s.customerInfo, ...info };
  // Auto-compose name from firstName + lastName
  if (info.firstName !== undefined || info.lastName !== undefined || info.middleName !== undefined) {
    const parts = [merged.firstName, merged.middleName, merged.lastName].filter(Boolean);
    merged.name = parts.join(' ') || merged.name;
  }
  // Backward compat: if caller passes `name` directly, split it
  if (info.name !== undefined && info.name !== merged.name && info.firstName === undefined) {
    const parts = info.name.trim().split(/\s+/);
    merged.firstName = parts[0] || '';
    merged.lastName = parts.slice(1).join(' ') || '';
  }
  return { customerInfo: merged };
}),

Helper to convert legacy flat fields

Used in QuoteGenerator.tsx for URL params, RO data, and quote editing:

function toCustomerInfo(opts: {
  name?: string; phone?: string; vehicleInfo?: string; vin?: string;
  mileage?: string; roNumber?: string; serviceAdvisor?: string;
}): CustomerInfo {
  const n = opts.name || '';
  const parts = n.trim().split(/\s+/);
  return {
    firstName: parts[0] || '',
    middleName: '',
    lastName: parts.slice(1).join(' ') || '',
    name: n,
    phone: opts.phone || '',
    vehicleInfo: opts.vehicleInfo || '',
    vin: opts.vin || '',
    mileage: opts.mileage || '',
    roNumber: opts.roNumber || '',
    serviceAdvisor: opts.serviceAdvisor || '',
    email: '',
  };
}

Customer Record Auto-Creation

ensureCustomerRecord helper (in QuoteSummary.tsx)

When saving or sharing a quote, this function ensures a customers collection record exists:

1. If customerInfo.customerId is already set → use it (skip)
2. Search for existing customer by phone (digits, exact match via PB filter phone ~)
3. If not found by phone, search by exact name match
4. If still not found, create a new record in customers collection with userId, name, firstName, lastName, phone, email

async function ensureCustomerRecord(customerInfo: {
  name: string; firstName: string; lastName: string; phone: string; email?: string;
}): Promise<string | undefined> {
  if (!customerInfo.name.trim()) return undefined;
  const userId = pb.authStore.model?.id;
  if (!userId) return undefined;

  // Check by phone first
  const phoneDigits = customerInfo.phone.replace(/\D/g, '');
  if (phoneDigits.length >= 4) {
    try {
      const existing = await pb.collection('customers').getFirstListItem(
        `phone ~ "${phoneDigits}" && userId = "${userId}"`,
        { fields: 'id', batch: 1 }
      );
      if (existing) return existing.id;
    } catch { /* proceed */ }
  }

  // Check by name
  try {
    const existing = await pb.collection('customers').getFirstListItem(
      `name = "${customerInfo.name.replace(/"/g, '')}" && userId = "${userId}"`,
      { fields: 'id', batch: 1 }
    );
    if (existing) return existing.id;
  } catch { /* proceed */ }

  // Create new
  try {
    const created = await pb.collection('customers').create({
      name: customerInfo.name,
      firstName: customerInfo.firstName || customerInfo.name.split(' ')[0] || '',
      lastName: customerInfo.lastName || customerInfo.name.split(' ').slice(1).join(' ') || '',
      phone: customerInfo.phone,
      email: customerInfo.email || '',
      address: '', notes: '', userId,
    });
    return created.id;
  } catch (err) { return undefined; }
}

Integration points (all forms)

• handleSave() in QuoteSummary.tsx — calls ensureCustomerRecord before saving the quote, passes customerId in the PB quote record
• ensureShareToken() in QuoteSummary.tsx — same pattern for shared quotes
• RO conversion in QuoteGenerator.tsx — reads customerId from the source quote and passes it to the RO creation payload

Duplicate Detection (All Forms)

The CustomerInfoPanel fires a debounced duplicate check (600ms after last keystroke on firstName/lastName/phone). It fetches all user's customers and filters client-side by name contains or phone contains. If a match is found, an amber banner offers "Use Existing" or "Create New" buttons.

Client-side filtering (safe fallback):

const result = await pb.collection('customers').getList(1, 500, {
  fields: 'id,name,phone', batch: 500,
});
const matched = (result.items as any[]).filter((c: any) => {
  const cName = (c.name || '').toLowerCase();
  const cPhone = (c.phone || '').replace(/\D/g, '');
  if (nameQuery && cName.includes(nameLc)) return true;
  if (phoneDigits.length >= 4 && cPhone.includes(phoneDigits)) return true;
  return false;
});

This avoids PB filter operator issues (the ~ operator can return 400 on some collections — see "Filter ~ operator can return 400" pitfall in the main SKILL.md).

Customer Select → Multi-Source Data Population

When an advisor selects a customer from the search dropdown, the form pre-fills vehicle info, VIN, mileage, email, and the last RO number. These fields are scattered across multiple PocketBase collections — no single query can populate them all.

Fallback chain

The selectCustomer() function in CustomerInfoPanel.tsx tries each source in order, stopping as soon as it finds data:

1. vehicles collection
   └─ customerId = '<id>'  →  year/make/model → vehicleInfo, vin, mileage

2. quotes collection (by customerId)
   └─ customerId = '<id>' && userId = '<uid>'  →  vehicleInfo, vin, mileage, repairOrderNumber

3. quotes collection (by customerName — fallback for legacy records)
   └─ customerName = '<name>' && userId = '<uid>'  →  same fields

4. repairOrders collection (by customerId)
   └─ customerId = '<id>' && userId = '<uid>'  →  vehicleInfo, vin, mileage, roNumber

5. repairOrders collection (by customerName — fallback)
   └─ customerName = '<name>' && userId = '<uid>'  →  same fields

Each lookup uses getList(1, 1, { filter, sort: '-created' }) to get the most recent record. The = filter operator works reliably on text fields (unlike ~). Results are merged — each field keeps the first non-empty value found.

Why vehicles alone isn't enough

Customers created via quote auto-save (ensureCustomerRecord) have NO vehicle records in the vehicles collection — the vehicle info only exists on their quote/RO records. So the fallback to quotes and ROs is essential for those customers.

Full function pattern

const selectCustomer = useCallback(async (c: CustomerSearchResult) => {
  const userId = pb.authStore.model?.id;
  try {
    const full = await pb.collection('customers').getOne(c.id);

    let vehicleInfo = '', vin = '', mileage = '', roNumber = '';

    // 1. Try vehicles collection
    try {
      const vResult = await pb.collection('vehicles').getList(1, 1, {
        filter: `customerId = '${c.id.replace(/'/g, "\\'")}'`,
        sort: '-created',
        fields: 'year,make,model,vin,mileage',
      });
      if (vResult.items.length > 0) {
        const v = vResult.items[0] as any;
        vehicleInfo = [v.year, v.make, v.model].filter(Boolean).join(' ');
        vin = v.vin || '';
        mileage = v.mileage || '';
      }
    } catch { /* vehicles collection may not exist */ }

    // 2-5. Fallback to quotes/ROs
    const escapedId = c.id.replace(/'/g, "\\'");
    const escapedName = (full.name || c.name || '').replace(/'/g, "\\'");
    const idFilter = `customerId = '${escapedId}' && userId = '${userId}'`;
    const nameFilter = `customerName = '${escapedName}' && userId = '${userId}'`;

    for (const [collection, filterToTry] of [
      ['quotes', idFilter], ['quotes', nameFilter],
      ['repairOrders', idFilter], ['repairOrders', nameFilter],
    ] as const) {
      if (vehicleInfo) break; // stop once we have data
      try {
        const result = await pb.collection(collection).getList(1, 1, {
          filter: filterToTry, sort: '-created',
          fields: 'vehicleInfo,vin,mileage,repairOrderNumber,roNumber',
        });
        if (result.items.length > 0) {
          const r = result.items[0] as any;
          if (!vehicleInfo) vehicleInfo = r.vehicleInfo || '';
          if (!vin) vin = r.vin || '';
          if (!mileage) mileage = r.mileage || '';
          if (!roNumber) roNumber = r.repairOrderNumber || r.roNumber || '';
        }
      } catch { /* try next source */ }
    }

    const parts = (full.name || c.name || '').trim().split(/\s+/);
    setCustomerInfo({
      customerId: c.id,
      firstName: parts[0] || '',
      middleName: parts.length > 2 ? parts.slice(1, -1).join(' ') : '',
      lastName: parts.length > 1 ? parts[parts.length - 1] : '',
      name: full.name || c.name || '',
      phone: full.phone || c.phone || '',
      email: full.email || '',
      vehicleInfo, vin, mileage, roNumber,
    });
  } catch (e) {
    logError('Failed to load full customer record', e);
    // Fallback: set what we have from search result
    const parts = c.name.trim().split(/\s+/);
    setCustomerInfo({ customerId: c.id, firstName: parts[0] || '',
      lastName: parts.slice(1).join(' ') || '', name: c.name, phone: c.phone });
  }
}, [setCustomerInfo]);

Name splitting logic

Given a customer name string, split into first/middle/last:

const parts = name.trim().split(/\s+/);
const firstName = parts[0] || '';
const middleName = parts.length > 2 ? parts.slice(1, -1).join(' ') : '';
const lastName = parts.length > 1 ? parts[parts.length - 1] : '';

• "John Doe" → { firstName: "John", middleName: "", lastName: "Doe" }
• "John Michael Doe" → { firstName: "John", middleName: "Michael", lastName: "Doe" }
• "Alice" → { firstName: "Alice", middleName: "", lastName: "" }