hermes-config/skills/software-development/pocketbase-development/references/spq-legacy-data-model.md

SPQ Legacy Data Model — Migration Patterns

Vehicle Data: Not a Separate Collection

The SPQ legacy database has no vehicles collection. Vehicle data lives on repairOrders and quotes records as flat text fields:

• vehicleInfo (text) — e.g. "2021 Honda CR-V", "2019 Honda Pilot"
• vin (text) — e.g. "2HKRW2H99MH670367"

The legacy JS code (customers.js) dynamically assembles each customer's vehicles by:

1. Searching repairOrders, quotes, and appointments collections for records where customerName matches the current customer's name
2. Extracting vehicleInfo and vin from each matching record
3. Deduplicating by vehicleInfo, preferring records that have a VIN

// Pattern to follow when migrating this functionality
const nameFilter = customerNames
  .map((n) => `customerName ~ '${n.replace(/'/g, "\\'")}'`)
  .join(' || ');

// Query repairOrders and quotes
const roResult = await pb.collection('repairOrders').getList(1, 500, {
  filter: nameFilter,
  fields: 'vehicleInfo,vin,customerName',
});

Key schema facts

Collection	Fields with vehicles	Notes
repairOrders	vehicleInfo, vin, mileage, customerName	Primary source. customerId is empty on all 55 records.
quotes	vehicleInfo, vin, mileage, customerName	Secondary source. customerId is empty on all 47 records.
appointments	vehicleInfo only (no vin column, no customerId column)	Has customerName field
customers	vehicleInfo (text), vin (text)	Only one vehicle per customer; often empty

There is NO vehicles collection anywhere in the legacy database.*

Update (post-M16 migration): A vehicles collection was added by migration M16 (pb_migrations/1740000000007_create_vehicles.js). It is user-scoped (userId + customerId) and stores vehicle records (make, model, year, vin, mileage, licensePlate, color, engine). New records created via the Customer page's vehicle editor go here. However, the legacy repair orders/quotes still have empty customerId and their vehicle data lives on the flat text fields. The CustomerInfoPanel.tsx code has a multi-tier fallback: (1) try vehicles collection by customerId, (2) fallback to quotes by customerName, (3) fallback to repairOrders by customerName.

* Coverage note: the vehicles table exists in SQLite (created by M16) but has no records in the backup dataset.

Critical: customerId is empty on ALL existing repair orders (55/55) and ALL quotes (47/47). Any query using customerId as a filter will return zero records. Always use customerName for record linkage. If customerId is ever populated in the future, the customerId pattern will work, but the current data has zero records matching.

Parsing vehicleInfo text

vehicleInfo values follow the pattern "YYYY Make Model" (e.g. "2021 Honda CR-V"). A regex extraction handles this:

function parseVehicleInfo(info: string) {
  const match = info.trim().match(/^(\d{4})\s+(.+)$/);
  if (match) {
    const rest = match[2].trim();
    const spaceIdx = rest.indexOf(' ');
    if (spaceIdx > 0) {
      return {
        year: match[1],
        make: rest.slice(0, spaceIdx),
        model: rest.slice(spaceIdx + 1),
      };
    }
    return { year: match[1], make: rest, model: '' };
  }
  return { year: '', make: info, model: '' };
}

Display in CustomerCard

The card shows vehicleCount and the first vehicle's label via formatVehicleLabel:

function formatVehicleLabel(v: VehicleRecord): string {
  const parts = [v.year, v.make, v.model].filter(Boolean);
  return parts.length > 0 ? parts.join(' ') : 'Unknown vehicle';
}

Combine-and-deduplicate pattern across multiple collections

When building a per-customer vehicle list from repairOrders + quotes, deduplicate by vehicleInfo within each customer:

// Build: customerName -> VehicleRecord[]
const customerVehicleMap = new Map<string, Map<string, VehicleRecord>>();

for (const vs of vehicleSources) {
  if (!vs.vehicleInfo || !vs.customerName) continue;
  if (!customerVehicleMap.has(vs.customerName)) {
    customerVehicleMap.set(vs.customerName, new Map());
  }
  const customerVehicles = customerVehicleMap.get(vs.customerName)!;
  const existing = customerVehicles.get(vs.vehicleInfo);
  // Prefer the entry that has a VIN
  if (!existing || (!existing.vin && vs.vin)) {
    const parsed = parseVehicleInfo(vs.vehicleInfo);
    customerVehicles.set(vs.vehicleInfo, {
      id: '',
      customerId: '',
      make: parsed.make,
      model: parsed.model,
      year: parsed.year,
      vin: vs.vin || '',
      licensePlate: '',
      mileage: '',
      color: '',
      engine: '',
    });
  }
}

const enriched = customerList.map((c) => {
  const cv = customerVehicleMap.get(c.name);
  const vehicles = cv ? Array.from(cv.values()) : [];
  return { ...c, vehicles, vehicleCount: vehicles.length };
});

Quotes Collection Schema

The quotes collection stores quote data with the same customer-vehicle relationship pattern:

Field	Type	Notes
customerId	text	⚠ EMPTY ON ALL 47 EXISTING RECORDS — use customerName for queries
customerName	text	Denormalized for display
vehicleInfo	text	e.g. "2023 Honda CR-V"
vin	text
mileage	text
status	text	Values: converted, sent, declined, draft
createdAt	text	ISO datetime
total	number	Quote total
serviceAdvisor	text	Advisor name
services	json	Array of service objects
discountValue	number
discountType	text	"dollar" or "percent"

Fetch quotes by customerName (not customerId):

// ✅ Works — customerName is the only reliable link
const nameFilter = `customerName = '${customer.name.replace(/'/g, "\\'")}'`;
const result = await pb.collection('quotes').getList(1, 100, {
  filter: nameFilter,
  sort: '-createdAt',
  fields: 'id,customerName,vehicleInfo,vin,mileage,status,createdAt,total,serviceAdvisor,services,notes',
});

// ❌ Will return zero records — customerId is empty on all existing data
const result = await pb.collection('quotes').getList(1, 100, {
  filter: `customerId = '${customerId}'`,
  ...
});

Quote services JSON — display pattern

The services field stores a JSON array of service items. PocketBase may return this field as either a pre-parsed JavaScript array OR a raw JSON string. Always normalize:

function parseQuoteServices(raw: any): QuoteServiceItem[] {
  if (!raw) return [];
  let items = raw;
  if (typeof raw === 'string') {
    try { items = JSON.parse(raw); } catch { return []; }
  }
  if (!Array.isArray(items)) return [];
  return items.map((s: any) => ({
    name: s.name || 'Service',
    price: typeof s.price === 'number' ? s.price : 0,
    customerDecision: s.customerDecision || 'pending',
  }));
}

The expandable row shows each service name with its price and a colored decision badge:

{/* In a <tbody>, each quote row is followed by a detail row */}
<>
  <tr key={q.id} className="cursor-pointer" onClick={() => toggleQuote(q.id)}>
    {/* summary columns: date, vehicle, total, status, advisor */}
  </tr>
  <tr key={q.id + '-detail'}>
    <td colSpan={5} className="p-0">
      {isExpanded && (
        <div className="bg-gray-50 px-6 py-4 dark:bg-gray-800/50">
          {/* Service line items with name + price + Approved/Declined badge */}
          {/* Notes if present */}
          {/* VIN if present */}
          {/* Collapse button */}
        </div>
      )}
    </td>
  </tr>
</>

Adjacent <tr> elements in a <tbody> must be wrapped in a fragment (<>...</>) to satisfy JSX parsing. Each <tr> retains its key prop.

Quote status color mapping for UI

const qStatus = q.status === 'converted' ? 'bg-green-50 text-green-700 dark:bg-green-900/30 dark:text-green-400'
  : q.status === 'sent' ? 'bg-blue-50 text-blue-700 dark:bg-blue-900/30 dark:text-blue-400'
  : q.status === 'declined' ? 'bg-red-50 text-red-700 dark:bg-red-900/30 dark:text-red-400'
  : 'bg-gray-50 text-gray-700 dark:bg-gray-900/30 dark:text-gray-400';

Repair Orders Collection Schema

Field	Type	Notes
customerId	text	⚠ EMPTY ON ALL 55 EXISTING RECORDS — use customerName
customerName	text
customerPhone	text	Exists in DB but missing from TS RepairOrder type — add if used
customerEmail	text	Exists in DB but missing from TS RepairOrder type — add if used
roNumber	text
vehicleInfo	text	e.g. "2019 Honda Pilot"
vin	text
mileage	text
status	text	Values: active, in_progress, waiting_parts, waiting_pickup, completed, delivered
workStatus	text	Legacy values: active, in-progress, waiter, completed, delivered
writeupTime	text	ISO datetime — timestamp of RO creation
estimatedTime	text	Hours as string (e.g. "1.5") — NOT estimatedDuration (minutes number)
promisedTime	text	ISO datetime of promised completion time
completedTime	text
technician	text
notes	text
services	json	JSON-stringified array of service objects
financial	json	Financial breakdown + customerType storage
createdAt	text	ISO datetime
updatedAt	text	ISO datetime

Fetch repair orders by customer name:

const result = await pb.collection('repairOrders').getList(1, 100, {
  filter: `customerName = '${customerName}'`,
  sort: '-createdAt',
  fields: 'id,roNumber,customerId,customerName,vehicleInfo,vin,mileage,status,workStatus,createdAt,completedTime,writeupTime,technician,notes,services',
});

Other Schema Notes

• createdAt / updatedAt (not created / updated) on most collections
• userId is a plain text field (not a relation type) on all user-owned collections
• financial is a JSON field on repairOrders
• services is a text field on repairOrders (JSON-stringified array)
• One superuser admin exists (not _superusers collection — legacy /api/admins/ auth returns 404)
• User auth uses _superusers collection via POST /api/collections/_superusers/auth-with-password (PB v0.39+)

Cross-Page Data Passing: URL Query Parameters

When to use

Passing record context between pages when the source page (e.g., Repair Orders) needs to pre-populate a form on the target page (e.g., Quote Generator). The alternative is global state (Zustand store) or React Router location state, but URL params are preferred when:

• The data should be shareable/bookmarkable
• The target page needs to work standalone (refresh-safe)
• Only flat key-value pairs need to pass (no complex nested objects)

Implementation pattern

Sender (source page):

import { useNavigate } from 'react-router-dom';

const navigate = useNavigate();

const handleGenerateQuote = () => {
  const params = new URLSearchParams({
    name: ro.customerName || '',
    phone: ro.customerPhone || '',
    vehicleInfo: ro.vehicleInfo || '',
    vin: ro.vin || '',
    mileage: ro.mileage || '',
    roNumber: ro.roNumber || '',
    serviceAdvisor: ro.advisorName || '',
  });
  navigate(`/quote?${params.toString()}`);
};

Receiver (target page):

import { useSearchParams } from 'react-router-dom';

const [searchParams] = useSearchParams();
const editId = searchParams.get('edit');

// Pre-populate from RO data passed via URL params (runs once on mount)
useEffect(() => {
  if (editId) return; // don't pre-populate when editing an existing quote

  const name = searchParams.get('name');
  if (!name) return; // no RO data — nothing to pre-fill

  setCustomerInfo({
    name,
    phone: searchParams.get('phone') || '',
    vehicleInfo: searchParams.get('vehicleInfo') || '',
    vin: searchParams.get('vin') || '',
    mileage: searchParams.get('mileage') || '',
    roNumber: searchParams.get('roNumber') || '',
    serviceAdvisor: searchParams.get('serviceAdvisor') || '',
  });
}, [editId]); // depends on editId so it re-runs if editId changes

Pitfalls

• URL length limits. Browsers cap URLs at ~2000 characters. For large objects (e.g., full service lists with 20+ items), use router state or a store instead.
• URI encoding. URLSearchParams.toString() handles encoding automatically. Manually building query strings with template literals will break on special characters like & or = in values.
• Empty string values. An empty phone param still appears in the URL (&phone=). This is harmless — the receiver's fallback-to-empty-string handles it.
• The effect dependency array matters. Using [] (empty) means the pre-population runs on every mount, including when loading an existing quote for editing. Guard against this by checking editId. Using [editId] ensures the effect re-runs if the user switches between "new from RO" and "edit existing" flows.
• Competition with edit mode. The URL may contain both ?edit=... (load existing quote for editing) and ?name=...&phone=... (pre-populate from RO). The editId guard in the effect prevents the pre-population from overwriting data loaded from the existing quote record.