The Claims dashboard tracks every warranty/return/cancellation claim through its lifecycle: how many are open, how long they take, where they get stuck, who is responsible, and how customers rate the experience.
It reads live from the production database on every load through a small server function (/api/claims) — the browser never touches the database directly. The function holds the DB credentials, runs the queries, and returns finished numbers. Results are cached for 2 minutes, so the first open after a quiet spell is slower (the function has to hit the DB); everyone after that is instant.
The dashboard has two cohorts: the current cohort (claims with a claim_type — the live system) and a legacy cohort (older pre-migration claims, claim_type empty) shown in the 'Old View' tab and winding down.
Live via /api/claims (Vercel function, Singapore region, encrypted DB credentials) → MySQL → JSON → cards. 2-minute cache. If the API ever fails, the page falls back to a static claims-data.json snapshot (may be stale).
Example values are a snapshot taken 10 July 2026 (v4) to make definitions concrete — the live dashboard always shows current numbers.
What we did today — 10 July 2026
Interactive charts fixed: hovering any bar/line/point now shows the exact value (the tooltip styling was missing before) plus a crosshair line; legend chips toggle series on/off.
Time ranges: Today / 7d / 30d / 90d / 1y presets on both dashboards, plus a 'vs prev' toggle that overlays the previous equivalent period (dashed ghost) with % change arrows on cards.
Dark / light mode: header moon/sun button, follows your system and remembers your choice; native dropdowns and date-pickers now render correctly in dark mode. Charts shrunk to a sensible size.
Orders — Warranty products (the '2-years-revibe-care+' 2-year care plan, category 3) are now excluded from every metric; they are not devices to fulfil and were inflating the backlog.
Orders — COD-to-call now uses the real 'Cash On Delivery (OTP Not Confirmed)' signal; 'To be confirmed' orders (usually tests) are hidden by default with a toggle to show them.
Both dashboards — Open items are now also shown as separate tables per stage (02. Order created, 03. Quality check, ...), listing the actual orders/claims in each.
New '\u26a0 Needs attention' panel on both dashboards: auto-detected situations that need action (late orders, courier follow-ups, stale claims, ready-for-refund, aged backlog) with click-through. (This replaces the earlier manual incidents idea.)
AI Mentor upgraded to Gemini 3.5 Flash and added to the Orders dashboard (with its own workspace); compact resizable chat. Pinning an AI-built card now works and places it on the dashboard tab AND in the AI workspace at once, appearing instantly.
Daily KPI snapshots (Bautista's backlog tracker) now run automatically at 9 AM UAE and are stored, building the history that future trend views will use.
Portal home page made more compact so all workspaces fit on one screen.
Both dashboards now open instantly from the last data you pulled and re-sync in the background — a '\u27f3 Synced Xm ago' button in the header forces a manual refresh, and your current numbers stay on screen until the fresh copy lands. No more waiting on the database each time you return to a tab.
Orders — the '\u2462 Flag history' chart (on-hold / COD / Request-Revibe) was replaced with a Backlog aging profile: open orders bucketed by age (0-2d \u2026 30d+, shaded green\u2192red), drawn live so it is full from the first load and shows at a glance how much of the pile is going stale.
Orders — the 'Net flow (created \u2212 shipped)' chart now plots negative days correctly around a zero line (it used to flatten them to the axis); the previous-period compare was verified working.
Claims — added a Country filter (matching the Orders one), and every stage list is now ordered by its stage number (0, 1, 2, 3, 6, 9, 10, 16, \u2026) instead of workflow order.
Dark mode — the country dropdown now stays readable while open (its popup had been rendering with an unreadable transparent background).
Data flow
Claim stage pipeline
Most KPIs are built on this stage order. Terminal stages (claim is closed): Refunded, Delivered, Cancelled, Invalid claim confirmed.
1 · Operational “New” View
Live snapshot of the current cohort: what's open, the money exposed, where claims sit in the pipeline, and what's gone stale. Renderer: renderOperationalTab (dashboard.js).
Open claims
What it isCount of current-cohort claims not in a terminal stage.
How it's calculatedFilter currentClaims to stage NOT IN (Refunded, Delivered, Cancelled, Invalid claim confirmed). Sub-line shows closed and total counts.
Example892 open (as of 10 July 2026 (v4))
Where (code)renderOperationalTab → Q1 currentCohort.claims
Open exposure
What it isTotal AED value tied up in open claims.
How it's calculatedSum of claim_financial_details.refund_to_cx_amount (aliased 'aed') over open claims. Sub-line = avg AED per open claim.
ExampleAED 2.59M
Where (code)renderOperationalTab → Q1 (aed field)
In-house repair
What it isOpen claims being repaired by Revibe's own labs.
How it's calculatedOpen claims whose repair supplier id is in the in-house list.
Example~118
Where (code)renderOperationalTab
⚠ In-house supplier IDs are HARDCODED in dashboard.js (CONFIG.inHouseSupplierIds = [181,141] = Revibe Repair SA/ZA). If a lab's ID changes, this needs updating in code.
Independent partners
What it isOpen claims with third-party repair partners.
How it's calculatedOpen total minus in-house minus legacy-supplier counts.
Example~564
Where (code)renderOperationalTab
⚠ Also depends on the hardcoded supplier ID lists.
① Pipeline by stage
What it isTable: how many open claims sit in each stage, and how long they've been there.
How it's calculatedGroups open claims by stage in workflow order. Columns: N, avg days since claim (now - claim_date), avg days in stage (now - stage_updated_at). Empty stages hidden.
Examplesums to the open total (checksum shown)
Where (code)renderPipelineTable → Q1 + STAGE_ORDER
⚠ Aging 'now' is the viewer's browser clock, not a fixed server time.
①b Suppliers with un-updated claims
What it isFour cards flagging suppliers sitting too long in a stage without updates.
How it's calculatedPer-stage thresholds: Pending collection >2d, Under collection >2d, In transit >3d, Under QC >4d. Groups the offenders by supplier, top 4 each.
Examplevaries
Where (code)renderSupplierStaleness
⚠ QC is internal to Revibe (shown for visibility, not supplier blame). Thresholds are fixed in code.
② Staleness: Critical >96h / Warning >72h
What it isTwo cards: how many open claims had no system update for over 4 days (critical) / 3 days (warning).
How it's calculatedOver open claims excluding 'Under QC': critical = (now - updated_at) >= 96h; warning >= 72h. Shows count + % of open.
Examplevaries
Where (code)renderStalenessSection
⚠ Measures time since last UPDATE, not claim age. Excludes Under QC.
② Stale claims list
What it isTable of the critical (96h+) claims, oldest first.
How it's calculatedThe critical set sorted by age desc. Columns: Claim ID, Order #, Stage, Country, Supplier, Reason (sub_type), Days Stale, AED.
Examplevaries
Where (code)renderStalenessSection
2 · KPIs & Analytics
Daily historical charts over the selected date range. Every card is computed per-day by getKpiMetricsForDay. Renderer: renderAllKpis.
Open Claims at 9am UAE
What it isHow many claims were open at the 9am UAE snapshot each day.
How it's calculatedFor each day: claims created before the 9am cutoff and not yet closed at that cutoff. Big number = latest day; second = period average.
Example~780
Where (code)renderOpenChart → getKpiMetricsForDay
⚠ 9am UAE is computed as 05:00 UTC (fixed UTC+4, no daylight saving).
Avg & Median Aging at 9am
What it isHow old the open claims were, on average and median, at each 9am snapshot.
How it's calculatedMean and median of (cutoff - claim_date) in days over the open set that day. Two lines: avg (solid), median (dashed).
Example~13d avg / ~7d median
Where (code)renderAgingChart
Resolved per Day (Refunded + Delivered)
What it isClaims that reached a resolved state each day.
How it's calculatedresolution_date falls on that day. Stacked: Refunded (has a '7. Refunded' log) vs Delivered.
Examplevaries
Where (code)renderResolvedChart
Cancelled per Day
What it isClaims cancelled each day.
How it's calculatedcancellation_date (first '11. Cancelled' log) falls on that day.
Examplevaries
Where (code)renderCancelledChart
Days to Resolve (Avg + Median)
What it isHow long resolved claims took, by the day they resolved.
How it's calculatedFor claims resolved that day: mean & median of (resolution_date - claim_date). Highlight lands on the last business day with resolutions.
Example~14d avg
Where (code)renderDtrChart
⚠ Weekend = Saturday/Sunday when picking the highlighted business day.
Claims Received & Time to First Action
What it isNew claims per day + how fast the first action happened.
How it's calculatedReceived = claim_date on that day, split by sub_type (Issue / Change of mind / Wrong device). TTA = avg (first_action_date - claim_date).
Example~15 received
Where (code)renderReceivedChart
⑦ Avg time in each stage (resolved only)
What it isTable of how long claims dwell in each stage, filterable by period / country / repair supplier.
How it's calculatedOver resolved claims, sums the gap between consecutive stage logs per stage; shows avg, median, N transitions. Filters: 7d/14d/all, country, supplier.
Examplevaries
Where (code)renderStageDurationSection → Q7 logs
⚠ Stages with fewer than 3 transitions are hidden. 'Now' here uses the data refresh time.
What it isOpen legacy claims and their AED exposure.
How it's calculatedlegacyCohort.openClaims count and summed aed. Sub-line adds closed + total from closedMetrics.
Examplesmall residual
Where (code)renderLegacyTab → Q2, Q3
① Pipeline (status='Open')
What it isLegacy open claims grouped by stage.
How it's calculatedCounts by stage, sorted by count (not workflow order).
Examplesmall
Where (code)renderLegacyTab
② Stale claims (48h+)
What it isLegacy open claims with no update for 48h+, excluding Under QC and Form received.
How it's calculatedFilter to (now - updated_at) >= 48h, sorted oldest first.
Examplesmall
Where (code)renderLegacyTab
⚠ Different thresholds from the current cohort (48h vs 72/96h).
4 · Process Funnels
The claim-handling funnels: aging/delay, days-to-resolve splits, creation, collection, refund, and invalid processes. Fixed 30/60/90-day windows — the header date picker does NOT apply here. Renderer: renderProcessTab.
Max aging + Delay buckets
What it isThe oldest open claim, and what share of open claims are delayed beyond 15/20/25/30 days.
How it's calculatedOver open claims: max age; and % with age over each threshold.
Examplemax ~66d; ~36% over 15d
Where (code)renderProcessTab
Days to resolve — by country / by return type
What it isTwo tables splitting DTR across countries and return types.
How it's calculatedClaims resolved in the last 90 days, grouped; avg + median DTR each. Type = RTO (if claim_type starts 'RTO') else sub_type.
ExampleKSA ~14.8d, UAE ~12.2d, ZA ~11.0d
Where (code)renderProcessTab
Creation process
What it isOf claims received in the last 30 days: how many approved, rejected, or missing documents, and how fast.
How it's calculatedFirst stage transition out of 'Form received' decides approved vs rejected (→ Cancelled/Invalid). Avg hours to approve/reject. Missing documents uses the real claim_documents field.
⚠ Shipped-back uses shipped_back_date, not the transient '10. Shipped back' stage log which undercounts. Low volume by design.
Collection process
What it is% of claims that failed collection, were recollected, or cancelled after failure.
How it's calculatedOver claims entering collection in the last 90 days: failed = hit 'Collection failed'; recollected = later re-entered collection/transit/QC; cancelled-after-failure = later cancelled.
Examplevaries
Where (code)renderProcessTab
Refund process
What it isTime to refund, manual-vs-automated, credits-vs-wallet, and the live refund backlog.
How it's calculatedTime to refund = 60d volume-weighted avg (refund date - claim date). Manual = 'Refunded - Manually' share. Credits/wallet from claim_store_credits. Backlog = open claims in 'Ready for refund'.
Examplevaries
Where (code)renderProcessTab → refundDaily/refundMethod
Invalid process
What it isShare of invalid claims, split by who invalidated: seller / Revibe agent / LAB, plus worst-offending sellers.
How it's calculatedOver validity decisions (90d): % invalid. LAB = claim passed '19. Expert revision'; Seller = decided by a Supplier-role user without expert revision; Revibe agent = the rest.
Examplevaries
Where (code)renderProcessTab → validity
⚠ LAB has no dedicated field/role — 'passed Expert revision' is the agreed proxy (confirmed with ops).
5 · Complaints & Requests
Complaint performance and Request-Revibe (claims side). Renderer: renderComplaintsTab.
Complaints performance
What it isOpen complaints, avg per claim, % of claims with a complaint, avg time to resolve, and a created-vs-solved trend.
How it's calculatedFrom claim_complaints (90d): open = status 'Not replied'; avg = total/claims-90d; time-to-resolve = created→updated for resolved ones.
Example667 open
Where (code)renderComplaintsTab → complaintTotals/Daily
Request Revibe performance (claims)
What it isOpen requests, 90d volume, avg time to resolve, distinct types, and a trend.
How it's calculatedFrom revibe_resolutions where entity_type='claim'.
Examplevaries (table is filling as usage grows)
Where (code)renderComplaintsTab → requestRevibe
Requests by type / Suppliers with most requests
What it isTwo tables breaking requests down by type and by supplier.
How it's calculatedGroups the same requestRevibe data by type and by supplier (top 12).
Examplevaries
Where (code)renderComplaintsTab
6 · Views & CSAT
Live queues by stage and customer satisfaction. Renderer: renderExperienceTab.
Live queues — open claims by stage
What it isTable of open claims per stage with aging and AED.
How it's calculatedGroups all open claims by stage: N, avg aging, max aging (>20d flagged), AED sum.
Examplevaries
Where (code)renderExperienceTab
⚠ The per-app-view breakdown and historic comparison are pending a daily-snapshot store (deferred).
CSAT by claim type
What it isOverall CSAT and split by Warranty / Return / Cancellation.
How it's calculatedSurveys matched to a claim via order number (most surveys carry placeholder claim IDs). Newest claim on the order wins, across new + legacy tables. Rating 1-5, last 90 days.
Exampleoverall 2.44 / 5
Where (code)renderExperienceTab → csat (order-number match)
⚠ ~5,700 of 5,772 surveys match a claim; unmatched are excluded. Warranty/Return come from claim taxonomy; Cancellation only from the legacy table.
Good / Bad CSAT reasons
What it isTwo tables ranking the reasons behind good (≥4) and bad (≤3) ratings.
How it's calculatedSums the survey's own structured reason columns (agility, quality, follow-up, ease / slowness, quality, poor service, difficulty).
Examplevaries
Where (code)renderExperienceTab
CSAT rating distribution
What it isBar chart of how many surveys gave each rating 1-5.
How it's calculatedCounts of each rating over the last 90 days.
Examplevaries
Where (code)renderExperienceTab
AI Mentor (copilot & workspace)
A floating ✦ button on the dashboard opens a chat drawer. You can ask questions about the data or tell it to build a card. It runs on gemini-2.5-flash inside a server function (/api/ai) — the AI key never reaches the browser.
Guarded SQL: when the AI needs data it writes a query, but the server only allows a single SELECT, against an allow-list of tables, with a forced 500-row limit and a 30-second timeout, using the read-only database user. It cannot modify anything.
Card types it can build: metric (one number), table, bar chart, or a small sandboxed HTML snippet. Built cards land in the AI Workspace as drafts; an editor/admin publishes them so everyone can see them. Published cards re-run their query live each time the workspace opens.
Roles (universal across dashboards): ADMIN (manage users + everything), EDITOR (chat + build + publish), VIEWER (see dashboards + published cards, no chat). Roles live in Firestore (dashboard_users) and are managed on the /admin page. Current admins: Mohamed, Bautista.
Table & field map
Table
What it holds / key columns
order_product_claims_new
Current-cohort claims (the live system). stage, status, claim_type, sub_type, claim_date, updated_at, stage_updated_at, country_id, original/repair_supplier_id.
order_product_claims
Legacy claims (pre-migration). type (Warranty/Return/Cancellation/Compensation), sub_type, claim_date. Used for CSAT taxonomy + the Old View.
One row per claim with a date column per stage (refunded_date, shipped_back_date, cancelled_date...). Used for the outcomes-per-day chart (reliable vs the transient stage log).
claim_financial_details
refund_to_cx_amount = the AED exposure figure shown on cards.
claim_validity_logs
Valid/Invalid/Not-replied decisions + who decided (updated_by → users → roles). Drives the invalid-process split.
claim_refund_status_logs
Refund status changes incl. 'Refunded' vs 'Refunded - Manually' (the manual/auto split).
claim_store_credits
gift_card_id vs wallet_action_id = credits-vs-wallet refund method.
claim_complaints
Complaint records (type, status default 'Not replied', created/updated).
claim_csat
Satisfaction surveys (rating 1-5 + structured reason columns). Linked to a claim via order number.
claim_documents
missing_document + rejection_reason text — the real creation-funnel fields.
Lookups: country & supplier names; user role (Supplier/Operations/Superadmin) for the invalid-decision split.
Appendix — queries
Q1 — Current-cohort claims
order_product_claims_new + LEFT JOINs (countries, suppliers x2, claim_financial_details, order_products, orders). WHERE claim_type IS NOT NULL AND != ''. No time window. Returns id, claim_id, stage, status, sub_type, claim_date, updated_at, stage_updated_at, country, original/repair supplier, aed (refund_to_cx_amount), order_number.
Q2 — Legacy open claims
Same joins. WHERE (claim_type IS NULL OR '') AND status='Open'.
Q3 — Legacy closed metrics
COUNT + SUM(refund_to_cx_amount) WHERE legacy AND status='Closed'.
Q7 — Transition logs
claim_stage_logs JOIN order_product_claims_new, current cohort, ORDER BY claim id, created_at. Returns claim_id, stage (=status), created_at.
validity — Invalid-process split
claim_validity_logs JOIN claim + LEFT JOIN suppliers/users/roles. WHERE created_at>=90d. GROUP BY status, updater_role, via_expert_revision (EXISTS a '19. Expert revision' stage log), supplier.
refundDaily — Refunds per day
claim_refund_status_logs JOIN claim. WHERE 60d AND status IN ('Refunded','Refunded - Manually'). GROUP BY UAE day. refunded_auto vs refunded_manual + avg_days_to_refund.
refundMethod — Credits vs wallet
claim_store_credits WHERE 90d. credits=gift_card_id set, wallet=wallet_action_id set, total.
claim_documents. GROUP BY rejection_reason / missing_document (90d, top rows). docTotals: missing_30d, rejected_30d.
outcomesDaily — Outcomes per day
claim_stage_tracking UNION of refunded_date/shipped_back_date/cancelled_date, 60d, GROUP BY UAE day. Uses date columns because the '10. Shipped back' stage log is transient.
Generated 10 July 2026 (v4) · single source regenerable via the docs generator.