spq-v2/docs/POCKETBASE_MIGRATIONS.md

PocketBase Schema Migrations

These changes are recommended by the spq-v2 code review (2026-06-30). They alter the live database and so must be applied to your separate PocketBase install (PocketBase does not live inside this repo).

Two ways to apply

Option A — Runnable JS migrations (recommended)

Drop-in .js files are included in ../pb_migrations/. Copy that entire folder into your PocketBase install's pb_migrations/ directory and run:

# from your PocketBase install directory
./pocketbase migrate
./pocketbase migrate up            # apply pending migrations
./pocketbase migrate collections   # re-sync collection schema

Files (apply in filename order):

File	What
1739999000001_add_customerType_to_repairOrders.js	M1 — customerType text column + backfill from financial blob
1739999000002_add_estimatedDuration_to_repairOrders.js	M2 — estimatedDuration (integer minutes) + backfill from estimatedTime
1739999000003_promote_financial_fields.js	M3 — grossTotal / grossCost / warrTotal / warrCost / shopCharge numeric columns + backfill
1739999000004_user_scoped_api_rules.js	M4 — per-user-scope List/View/Create/Update/Delete rules on all user-collections (security)
1739999000005_unique_roNumber.js	M5 — unique index on repairOrders.roNumber (server-side collision guard)
1739999000006_quote_ro_link_columns.js	M6 — quotes.repairOrderId + repairOrders.quoteId link columns (bidirectional RO↔Quote back-links)

Each migration is idempotent (safe to re-run) and includes an up and down hook so ./pocketbase migrate down cleanly reverses it.

Option B — Manual via admin UI

The next sections spell out each change for the admin UI. Use these if you prefer clicking through PocketBase Admin → Collections → ⚙.

---

What the migrations do

these columns are absent, but promoting them unlocks correct persistence, queryable aggregation, and atomic updates.

Apply each change in the PocketBase Admin → Collections → repairOrders → Edit collection UI. After adding a field, backfill existing rows as noted.

---

M1 — customerType (promote from financial JSON blob)

Today: customerType lives only inside the stringified financial JSON blob. The frontend synthesizes it on read (RepairOrders.fetchOrders), which breaks whenever the blob is corrupt or missing.

Add field:

• Name: customerType
• Type: Text
• Options: Pattern (optional) — restrict to waiter|drop-off

Backfill (run once in Admin → Collections → repairOrders → run a small script, or use the API):

// Node script — run against your PocketBase instance
import PocketBase from 'pocketbase';
const pb = new PocketBase('http://localhost:8091');
await pb.admins.authWithPassword('admin@example.com', 'PASSWORD');

const all = await pb.collection('repairOrders').getFullList({ batch: 500 });
for (const ro of all) {
  let fin = {};
  try { fin = typeof ro.financial === 'string' ? JSON.parse(ro.financial) : (ro.financial || {}); } catch {}
  const ct = fin.customerType === 'waiter' ? 'waiter' : 'drop-off';
  await pb.collection('repairOrders').update(ro.id, { customerType: ct });
}

After backfill, the redundant read/merge logic in RepairOrders.fetchOrders (lines ~1779-1787) and handleToggleCustomerType's "persist into financial blob" path (~1842-1856) can be simplified to plain column updates. Leaving them as-is is safe — they will simply keep the blob in sync, which is harmless. A future cleanup PR can remove the blob round-trip once the column is confirmed populated.

---

M2 — estimatedDuration (integer minutes; fixes lossy round-trip)

Today: stored as estimatedTime (hours string, e.g. "0.3"), converted back to minutes via Math.round(hours * 60) on read. 15 min → "0.3" → 18 min on every reload, drifting the due time.

Add field:

• Name: estimatedDuration
• Type: Number
• Options: Min: 0, Max: empty (or 10080 = 7 days)

Backfill:

const all = await pb.collection('repairOrders').getFullList({ batch: 500 });
for (const ro of all) {
  if (ro.estimatedDuration != null && ro.estimatedDuration !== '') continue;
  const hours = parseFloat(ro.estimatedTime || '0') || 0;
  await pb.collection('repairOrders').update(ro.id, {
    estimatedDuration: Math.round(hours * 60),
  });
}

Frontend follow-up (NOT yet applied — pending this migration): switch RepairOrders.fetchOrders, handleSave, and handleAddTime to read/write estimatedDuration directly and drop the / 60 ↔ * 60 conversions. Until then the current code keeps writing estimatedTime and synthesizing estimatedDuration on read, so no data is lost.

---

M3 — Promote financial fields out of the financial JSON blob

Today: grossTotal, grossCost, warrTotal, warrCost, shopCharge, and satisfaction are partly real columns (satisfaction already is) and partly loose JSON keys inside financial. The FinancialDashboard therefore must load every completed RO to the client to aggregate — it cannot run a server-side sum() over a JSON blob.

Add fields (all type Number, min 0):

• grossTotal, grossCost
• warrTotal, warrCost
• shopCharge (this overlaps conceptually with the existing shopCharges column — confirm which one the dashboard should sum and consolidate)

Backfill:

const all = await pb.collection('repairOrders').getFullList({ batch: 500 });
for (const ro of all) {
  let fin = {};
  try { fin = typeof ro.financial === 'string' ? JSON.parse(ro.financial) : (ro.financial || {}); } catch {}
  const patch = {
    grossTotal: Number(fin.grossTotal) || 0,
    grossCost:  Number(fin.grossCost)  || 0,
    warrTotal:  Number(fin.warrTotal)  || 0,
    warrCost:   Number(fin.warrCost)   || 0,
    shopCharge: Number(fin.shopCharge) || 0,
  };
  await pb.collection('repairOrders').update(ro.id, patch);
}

Frontend follow-up: after this migration, ExpandedDetail.saveFinField should write directly to the typed columns (and can stop stringifying the blob), and FinancialDashboard can issue a single getFullList with fields projection + client-side sum, or better, a server-side aggregation via PocketBase's aggregate API (0.20+) for monthly revenue/costs. This collapses thousands of records to one request.

---

M4 — API Rules (security gate — apply even if you skip everything else)

Each user-scoped collection (quotes, repairOrders, invoices, appointments, customers, services, settings) MUST have API rules that scope every record to the authenticated owner. Without these, any logged-in user can read/write every other shop's data by passing a different userId field in the request body.

For each collection, in Admin → Collections → ⚙ → API Rules:

Action	Rule
List / View	userId = @request.auth.id
Create	userId = @request.auth.id
Update	userId = @request.auth.id
Delete	userId = @request.auth.id

The users collection itself should restrict List/View to admins only.

Verify with two test users — user A must NOT be able to getOne(<B's record id>).

---

M6 — Quote ↔ Repair Order link columns (bidirectional back-links)

Today: RO→Quote and Quote→RO conversions stamp each other's record IDs client-side, but there are no backing columns — the links are invisible to queries and break if a record is re-imported.

Add fields:

• On quotes: repairOrderId — type Text, optional. Stores the RO id that originated this quote (set when the user clicks "Generate Quote" on an RO).
• On repairOrders: quoteId — type Text, optional. Stores the quote id that was converted into this RO (set when the user clicks "Convert to RO" on a quote).

No backfill needed — existing records simply have empty values.

Frontend follow-up: after M6 is applied, the handleConvertQuoteToRO and onGenerateQuote handlers can stop encoding the link in the notes field and use the real columns instead.

---

Applying & verifying

1. Backup first: ./pocketbase backup create (or copy the SQLite file).
2. Run Option A migrations in order, OR apply M4 (rules) manually first — it has zero data migration and closes the cross-tenant hole.
3. Apply M1 → M2 → M3 → M5 → M6 in filename order (the JS files handle backfill inside their up hooks).
4. After each migration, reload the spq-v2 app and confirm the corresponding screen still renders existing data correctly.
5. Once M1 + M2 are confirmed in production, open a follow-up issue to clean up the now-redundant frontend compatibility code (noted inline above).