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

```typescript
// 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:

```typescript
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`:

```typescript
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:

```typescript
// 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`):**

```typescript
// ✅ 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:

```typescript
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:

```tsx
{/* 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

```typescript
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:

```typescript
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):

```typescript
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):

```typescript
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.