🔧 Repair Hub
A live, mobile-friendly tool for the phone & tablet repair team: look up
repair prices, check inventory, and log devices coming in for repair with
their status. Prices are edited in the app (Settings → Repair prices) on top of
a published Google Sheet; inventory pulls from a Google Sheet.
Live app: https://mikepilot123.github.io/pricechecker/
Supplier order import
The Parts Orders panel can import an Excel .xlsx workbook entirely in the
browser, without an AI model or API key. Use a header row with part (required), plus any of
quantity, unit_cost, vendor, and shipment_name. Common alternatives
such as item, description, qty, price, and supplier are accepted.
The importer checks the workbook's sheets for that header row. Imported rows
open in the same editable review screen and are not saved until Save parts
is selected.
The Parts Orders panel can read a supplier order-confirmation PDF into an
editable list of parts. It uses the Gemini Developer API from the Vercel API
route, so the API key is never sent to the browser. The Gemini Developer API
has a free tier suitable for low-volume shop use.
Supplier PDFs are browser print-to-PDFs and carry a real text layer, so the
route extracts that text (lib/pdf-text.js) and sends ~1KB of text rather than
a ~150KB base64 image of the same page — far fewer tokens, so it sits well
inside the free tier and is much less likely to be throttled. A PDF with no
text layer (a scan or photo) falls back to sending the PDF for the model to
read as an image.
To enable it, create a Gemini API key in Google AI Studio and add it to the
Vercel project as GEMINI_API_KEY for Production, Preview, and Development.
Optionally set GEMINI_MODEL to a compatible Gemini model; the default is
gemini-3.5-flash-lite,gemini-3.6-flash,gemini-3.8-flash — tried in order,
cheapest and least contended first, so a comma-separated list sets the whole
fallback chain. Transient failures (503 "high demand", rate limits, timeouts)
are retried with backoff before moving down the chain. Uploaded PDFs are still
reviewed and edited before any parts are saved.
Prices — how the team uses it
- Open the link on a phone, tablet, or the shop counter screen.
- Type any model —
13 Pro, S22 Ultra, A54, iPad — and matching
devices filter instantly.
- Tap iPhone / iPad / Samsung chips to browse a brand.
- Each card shows the cheapest repair (
from $…); tap it to see the full
breakdown (screen, battery, charging port, etc.).
- Leave the app open — it keeps the price list synced automatically.
Tip: on a phone, use Share → Add to Home Screen to install it like an app.
It opens full-screen and even loads offline (showing the last saved prices).
The first successful team-PIN sign-in registers that browser with a signed
credential, so changing Wi-Fi or mobile networks does not require another
sign-in. The PIN itself is not stored. Rotating INTAKE_PIN revokes all
registered browsers. Set BROWSER_CREDENTIAL_SECRET to a long random server
secret if desired; otherwise the server uses its existing DATABASE_URL
secret for signing.
Device permissions
The owner can choose what each signed-in device may see: the invoice
totals at the top of Invoices, the Dashboard, Targets,
Accounting and Parts orders. It's all done from Settings → Devices.
To turn it on, open Settings → Devices and create an owner PIN. Keep it to
yourself: it's separate from the team PIN, so staff can't lift their own
restrictions. From then on every device, the owner's included, has all of them
hidden until the owner allows them for that device. Unlock the same screen with the owner PIN
to rename devices, change what each can see, or change the owner PIN.
Parts orders joined the list later, so devices already on file keep seeing it
until it's unticked for them; devices signed in from now on start with it hidden
like everything else.
The owner PIN is stored only as a salted hash. If it's ever forgotten, set an
OWNER_PIN environment variable in Vercel and redeploy: that PIN then takes
over (and can only be changed there) until the variable is removed.
The app hides those sections on a restricted device, and the server refuses
the bank, expense, sales-history and parts-order data behind them. Invoice totals are
worked out from the invoice list, which the device still needs, so hiding
them is a screen-level control.
Nightly backup
apps-script/NightlyBackup.gs copies the app's
data (repairs, notes, appointments, expenses, bank transactions, reminders,
customers, leads) into a Google Sheet every night at 9pm; its setup steps are
at the top of the file. It signs itself in to the app as a read-only device named Nightly backup
(listed in Settings → Devices): it can read everything the backup needs,
Accounting included, without anyone allowing it, and it can't change anything.
If the team PIN is ever changed, it registers a fresh device the next night.
There's also a Chrome extension (extension/) for the counter/office laptop:
click its toolbar icon on any site — Facebook, WhatsApp Web, email — to open
the live app in the side panel alongside whatever page you're already on. It's
a thin wrapper (just an iframe pointed at the live URL, same login/PIN state
as the regular tab), not a separate build to maintain. See
extension/README.md to load it.
Where prices come from
Two layers, merged in the browser on every sync:
-
The published Google Sheet (base list), read as CSV from three tabs:
| Tab | Sheet gid |
|---|
| iPhones (incl. iPads) | 0 |
| Techno | 1021598529 |
| Samsung | 1256027568 |
-
The app's own price catalog (api/prices.js, tables price_models /
price_entries), which holds every price edited in the app plus models the
sheet never had — Pixel is entirely here, since that tab was never wired
into the list above.
It also ships the Samsung Galaxy A-series screen prices (OLED / Incell /
Original grades) as seed data in lib/price-seed.js,
loaded on the first request after a deploy; they land on the sheet's own
Samsung A… cards where the names match.
Catalog rows win: they override a sheet model's repair price, add repair types
to it, add models the sheet doesn't list, or hide a model entirely. A price
cleared in the app is stored as an empty override, which is the only way to
retract a sheet cell — the CSV would otherwise re-add it on the next sync.
If the catalog is unreachable the sheet's prices still render on their own, and
if the sheet is unreachable the app falls back to the last saved list (or the
catalog alone on a device with no cache yet).
-
Prices load on open, auto-sync in the background, and when you
reopen the tab.
-
The front end connects to the Vercel WebSocket watcher at
wss://pricechecker-cyan.vercel.app/api/price-updates. When that watcher
detects sheet changes, it sends price:update and the app reloads
immediately.
-
To override the watcher on a shop device, use:
localStorage.setItem("rpc_price_update_ws", "wss://your-update-server.example");
Set it to "off" to disable the socket. If the watcher is unavailable, the
app falls back to a one-minute sync interval.
-
A "Updated just now" timestamp shows how fresh the prices are.
-
If the network is down, it shows the last saved prices with a notice.
Google caches the published CSV, so a brand-new edit can take a minute or
two to appear even after refreshing. That's normal.
Updating prices
In the app (preferred): Settings → Repair prices. A spreadsheet-style grid
of every model and repair type, so nobody needs to open the Google Sheet:
- Filter by brand or search for a model. The columns shown are the repair types
the filtered models actually use, so picking Pixel gives you Pixel's three
columns rather than every column in the shop.
- Edit any cell; changed cells stay highlighted and a bar counts unsaved edits
until you Save changes or Discard.
- Tick rows and use the bulk bar to set / raise / lower one column across
every selected model at once, by a dollar amount or a percentage.
- Add model creates a new one (its brand becomes a filter chip on the
Prices tab automatically); the bin icon removes a model from the price list.
- Editing needs the team PIN, same as every other write in the app. The pencil
icon on a price row in the Prices tab saves to the same place.
In the Google Sheet still works for the three tabs above. No code changes,
no redeploy — new models, new prices, and new repair columns all flow through
because the app reads each table's structure dynamically. A tab may contain
multiple tables with different repair columns: for example, the iPhone table
can use Incell Screen while an iPad table in the same tab uses Front Glass.
Note that a price edited in the app overrides the sheet's value for that cell
from then on.
If the sheet link ever changes
If you re-publish or move the sheet, update the published URL and tab gids
at the top of assets/app.js:
const SHEET_BASE = "https://docs.google.com/.../pub";
const TABS = [
{ key: "apple", gid: "0", label: "iPhones" },
{ key: "techno", gid: "1021598529", label: "Techno" },
{ key: "samsung", gid: "1256027568", label: "Samsung" },
];
Check In — logging devices & repair status
The Check In tab guides the team through customer details first, then the
device and its issues, and finally payment. It also lets the team pick one or
more issues (tag-style multi-select, plus "Other" for anything not
listed), and track status through the repair pipeline: Received → Diagnosing → Waiting for Parts → In Progress → Repaired → Picked Up (or
Cancelled). Every status change, device edit, or issue update is appended
to that ticket's activity log — visible on the card — so there's a full
timeline of what happened, not just the current state.
It captures Customer Name, Phone, Device, Issues, Status, and Notes. The
call button on each card dials the customer directly (tel: link) using
the phone number captured at check-in.
When a repair uses stock, staff can choose the exact inventory item during
check-in. The backend deducts one from that item in the inventory sheet when
the ticket is saved, and restores one if that ticket is deleted.
Open any ticket card to edit its client details, or use Delete record to
remove it after a confirmation prompt. Older tickets created before client
details were added do not contain a recoverable name or phone number; use
Add client info once on each of those records.
To reset the check-in list completely, use Clear all in the Check In toolbar and
confirm twice. It removes all ticket rows from the Sheet and dashboard, while
retaining the header row and the app's connection settings.
Data is stored in a separate, dedicated Google Sheet ("JQ Reapirs"), written
to only through a PIN-gated Apps Script — never read as a public CSV.
Privacy — this sheet now holds customer PII (name + phone). It must
not be published to the web or link-shared. Verify both are off:
File → Share → Publish to web (should be off/unpublished) and the
Share button (should be restricted, not "Anyone with the link"). The
app never needs it published — it only ever talks to the Apps Script
endpoint below.
One-time setup (per team manager)
A static site can't write to Google Sheets directly, so writes go through a
small Google Apps Script "web app" that the sheet owner deploys once. The
script's URL is already wired into assets/intake.js —
only the PIN is entered by staff.
- Open the JQ Reapirs spreadsheet → Extensions → Apps Script.
- Paste in the contents of
apps-script/Code.gs.
- Change the
PIN value at the top to a private PIN for your team.
- Deploy → New deployment → Web app
- Execute as: Me
- Who has access: Anyone
- Copy the resulting
.../exec URL and update the SCRIPT_URL constant
near the top of assets/intake.js (only needed if
you redeploy to a new URL — the current one is already set).
- In the app, open the Check In tab — staff just enter the PIN once per
device; it's remembered locally after that (never uploaded or committed).
If you already had an earlier version of Code.gs deployed, re-paste the
updated script over the old one and use Manage deployments → Edit → New
version — no need for a new URL. On its first request it preserves old
device records and adds the Customer Name, Phone, and History columns. This
deployment step is required before deleting records from the app.
The PIN is the only access control on this endpoint — anyone with it can
read every logged ticket (including customer name/phone) or write new ones.
Keep it private, and if it ever leaks, rotate it: change PIN in the
script, redeploy (same deployment, "Manage deployments → Edit → New
version"), and re-enter the new PIN on each device.
Inventory
The Inventory tab reads the shop inventory spreadsheet tab:
https://docs.google.com/spreadsheets/d/1CtKYaNkcrlU1-76NvIQUK31V0OAEhwNZHkzWZOUDZVA/edit?gid=85811363
It parses the current section layout (SCREENS, BATTERIES, and TOOLS)
and shows device/item, quality, quantity, and notes. The app reads inventory
publicly through /api/inventory.
To let the backend write stock deductions/restocks back to the sheet, configure
these Vercel environment variables and share the spreadsheet with the service
account email:
GOOGLE_SERVICE_ACCOUNT_EMAIL
GOOGLE_PRIVATE_KEY
INVENTORY_SHEET_ID=1CtKYaNkcrlU1-76NvIQUK31V0OAEhwNZHkzWZOUDZVA
INVENTORY_SHEET_GID=85811363
If write credentials are not configured, the Inventory tab can still load, but
ticket saves with a selected stock item will fail with a setup message.
Inventory ↔ prices
Whenever a part is in stock, its device card on Prices shows it: a green
✓ In stock badge on the card, and a green tick next to each repair that
part is for (e.g. iPhone 13 Pro → Soft OLED Screen). Add an item with a
quantity, or bump a quantity up on the Inventory tab, and the tick appears;
sell the last one and it goes. Hover a tick for the count; tap it to open the
part on the Inventory tab. Stock is re-checked every minute, so changes made
on another device show up too.
This works from the inventory names as they are:
- The device can be written in full or without its brand —
13 Pro,
iPhone 13 Pro, A15, Galaxy A15 — and one row can cover several models
(iPhone 12/12 Pro).
- The section says which repair it is: Screens, Batteries, Charging Ports,
Back Glass, Cameras, Speakers.
- A screen's quality picks the grade (
Soft → Soft OLED, Incell →
Incell). A screen with no quality ticks every screen repair on that card.
- A row whose note says "sold" counts one fewer in stock.
When a name can't be matched, link it by hand: every Inventory row has a
Link to repair chip (and Add item has the same dropdowns) to pick the
device and repair. A linked part ticks exactly that repair, and Check In
lists it first, pre-selecting it for a new check-in of that repair (choose
No stock item used if it won't be used). Links are saved in the database
(inventory_price_links).
Invoicing
The Send invoice to client checkbox on device check-in creates a custom JQ
Electronics invoice after the ticket is saved. The invoice uses the logged
ticket data (customer, phone, email, device, issues, status, notes, repair cost,
amount paid, and balance) and intentionally excludes photos/videos and assigned
technician details.
Email delivery sends the invoice link through Resend. WhatsApp delivery creates
the invoice and opens a WhatsApp message handoff with the invoice link for staff
to send.
Configure these Vercel environment variables:
RESEND_API_KEY
INVOICE_FROM_EMAIL=JQ Electronics <[email protected]>
PUBLIC_APP_URL=https://pricechecker-cyan.vercel.app
INVOICE_BUSINESS_NAME=JQ Electronics
INVOICE_CURRENCY=TTD
Ready for collection → client picks a day
The "ready for collection" email (offered when a repair is marked
Repaired) has a When will you collect it? row of day buttons, plus an
"Another day" link. They open a small page where the client picks one of the
next two weeks of opening days (Mon–Sat) and, optionally, a rough time. Opening
the link books nothing — only the page's Confirm does, so email scanners
that follow links can't make bookings.
Confirming adds the collection to Appointments, tagged Client booked
collection, with the usual reminder 30 minutes before. It also leaves a note
on the repair. If the client picks again, the same appointment moves rather
than a second one being added. The link is signed per repair (with
BROWSER_CREDENTIAL_SECRET, or DATABASE_URL if that isn't set). Once the
repair is Picked Up or Cancelled, the link says it has already been collected.
Links point at PUBLIC_APP_URL.
Reminders
The Reminders tab is a shared staff to-do list — add a reminder with an
optional due date/time and notes, tick it off when done, or delete it. It
syncs across every device the same way tickets, leads, and expenses do.
Reminders past their due date and not yet done are flagged Overdue in
the list. Filter chips switch between Open, Done, and All.
Editing needs the team PIN, same as every other write in the app.
Tech
Plain HTML/CSS/JS — no build step, no dependencies. Just static files
served by GitHub Pages. Includes a PWA manifest + service worker so it can be
installed to a home screen and opened offline.
index.html # markup (Prices + Check In views)
assets/style.css # styling (mobile-first, dark theme)
assets/app.js # CSV fetch + parse + catalog merge + Prices UI
assets/prices-admin.js # Settings → Repair prices bulk editor
assets/inventory.js # Inventory tab UI and stock list loader
assets/intake.js # device check-in UI (talks to Apps Script backend)
assets/dashboard.js # Dashboard, Targets, Expenses, and Reminders tabs
api/inventory.js # Vercel API for inventory reads
api/prices.js # Vercel API for the price catalog (public reads)
lib/prices.js # price catalog queries
lib/price-seed.js # Pixel price list shipped as seed data
lib/reminders.js # reminders queries (Reminders tab)
api/price-updates.js # Vercel WebSocket watcher for price sheet changes
apps-script/Code.gs # Apps Script backend for the check-in sheet
manifest.webmanifest # installable PWA
sw.js # offline app shell
extension/ # Chrome side-panel extension (thin iframe wrapper)
Running locally
It's static, so any local server works:
python3 -m http.server 8000
# then open http://localhost:8000
Prices are in TTD. This tool is for internal team use.
