db1e7d6f76
Same shape as the orders refactor \u2014 applied to the quotes page,
with a fix for a long-standing tab-count bug along the way.
# Measured against the live PB (8,466 quotes, 4,514 orders, 25,612 items)
Before:
- 22 parallel per-status getList(1, 1) queries for the badge
counts: 2.6s wall time (no quotes.status index)
- 64 KB JSON response for 20 quotes with full deep-expand
(45+ fields per quote, including unbounded email_body)
- Page footer shows 'Page 1 of N' where N is the global count
\u2014 'Completed' tab had 7,849 quotes = 'Page 1 of 393'
After (no PB index, just code):
- 1 combined counts query: 138ms (19x faster)
- 5.5 KB JSON response (12x smaller)
- Server-side tab filter: page 1 of N reflects the tab's set
- Cancelled tab badge now correctly counts rejected+cancelled
# Server-side read model
- getQuotesDashboardData in src/lib/server/quotes-service.ts
- tab-scoped list + global counts in one round-trip
- tab param drives a server-side filter (status IN (...) for
workflow tabs, relation filter for delivery_note, is_template
for templates, excludeTemplate for 'all')
- pagination reflects the active tab's matching set, not the
global ~8.5k quote count
- getQuoteStatusTabCounts: one combined getFullList with all
17 counted statuses ORed (fields: 'status'), deduped in memory.
Replaces 22 separate per-status scans. Cached 60s on the server
with bypassCache option for future realtime refetches.
- /api/v1/quotes/list thin route: validates tab against
QUOTE_TAB_ORDER, threads tab + nocache params to the service.
# Slim wire payload
- fields param on the orders list: just the columns the page reads
+ per-expand limits (quote.quote_number doesn't need
email_body, company.company doesn't need its address field, etc.)
- Dropped the customer.company 3-level expand (the page never read
expand.customer.expand.company \u2014 only expand.company.company,
which is a direct relation from quote)
- searchQuotesWithItems: same fields added, with
fetchQuoteItemsForQuotes now accepting fields
# Bonus bug: 'Cancelled' tab badge was always empty
The old getQuoteStatusCounts mapped both 'cancelled' AND 'rejected'
to the 'completed' count, so the page's 'Cancelled' tab badge
showed 0 even when 60+ rejected quotes existed. The page's
tabFilters separately grouped them under 'cancelled', so a quote
that was rejected showed up in the list but not the count.
The new QUOTE_TAB_STATUSES in the service layer is the canonical
mapping (cancelled absorbs both statuses). Page constants and
service constants now derive from the same source \u2014 no more drift.
# Index migration
scripts/migrations/2026-08-04-add-quotes-status-index.ts. Idempotent.
The live PB has no quotes.status index today (verified by hitting
/api/collections/quotes). With the index, the combined counts
query drops from 138ms to sub-10ms on the live instance.
# Tests
6 new tests in quotes-service.test.ts:
- QUOTE_TAB_STATUSES contract (cancelled absorbs rejected,
no empty status lists unless special-cased)
- getQuoteStatusTabCounts buckets correctly into tabs
- Cache hit returns the same counts on second call
- bypassCache: true sees new state
- getQuotesDashboardData end-to-end: tab-scoped items +
global counts
- Regression guard for the cancelled/rejected bug
Mock infrastructure upgrade: the quotes-service mock's where
function is now a proper recursive-descent filter parser (handles
nested || inside parens, empty quoted values). getList mock now
returns totalPages.
Full suite: 1312/1312 pass.
Co-authored-by: Orca <help@stably.ai>
86 lines
2.8 KiB
TypeScript
86 lines
2.8 KiB
TypeScript
/* eslint-disable */
|
|
/**
|
|
* Migration: add an index on `quotes.status`.
|
|
*
|
|
* Why this exists
|
|
* ───────────────
|
|
* The /quotes page's tab badges ("New (4)", "Completed (7,849)",
|
|
* "Email Failed (57)") and its server-side tab filter both run
|
|
* queries of the form `status = "emailed" || status = "approved" || ...`
|
|
* on the `quotes` collection. With ~8.5k quotes and no `status` index,
|
|
* every load is a full table scan — the 22 separate per-status count
|
|
* queries in the old `getQuoteStatusCounts` were 22 scans, costing
|
|
* ~2.6s wall time on every page load. The new combined-counts query
|
|
* collapses to 1 scan (~150ms measured on the live instance), but
|
|
* an index turns even that 1 scan into a sub-100ms B-tree lookup.
|
|
*
|
|
* Schema
|
|
* ──────
|
|
* quotes.status select (existing field, no schema change)
|
|
*
|
|
* The index is non-unique (many rows share each status).
|
|
*
|
|
* Idempotent — safe to re-run. Checks for the index before adding
|
|
* it; subsequent runs print "already present, skipping."
|
|
*
|
|
* Run with:
|
|
* npx tsx scripts/migrations/2026-08-04-add-quotes-status-index.ts
|
|
*/
|
|
import PocketBase from "pocketbase";
|
|
import { readFileSync } from "node:fs";
|
|
import { resolve } from "node:path";
|
|
|
|
function loadEnvFile(name: string) {
|
|
try {
|
|
const raw = readFileSync(resolve(process.cwd(), name), "utf8");
|
|
for (const line of raw.split("\n")) {
|
|
const trimmed = line.trim();
|
|
if (!trimmed || trimmed.startsWith("#")) continue;
|
|
const m = trimmed.match(/^([A-Z0-9_]+)\s*=\s*(.+)$/i);
|
|
if (m && !process.env[m[1]]) {
|
|
process.env[m[1]] = m[2].replace(/^["']|["']$/g, "").trim();
|
|
}
|
|
}
|
|
} catch {
|
|
// missing file — fine
|
|
}
|
|
}
|
|
loadEnvFile(".env");
|
|
loadEnvFile(".env.local");
|
|
|
|
async function main() {
|
|
const url = process.env.NEXT_PUBLIC_POCKETBASE_URL;
|
|
const email = process.env.PB_ADMIN_EMAIL;
|
|
const password = process.env.PB_ADMIN_PASSWORD;
|
|
|
|
if (!url || !email || !password) {
|
|
throw new Error(
|
|
"Missing NEXT_PUBLIC_POCKETBASE_URL / PB_ADMIN_EMAIL / PB_ADMIN_PASSWORD in env",
|
|
);
|
|
}
|
|
|
|
const pb = new PocketBase(url);
|
|
await pb.admins.authWithPassword(email, password);
|
|
|
|
const collection = await pb.collections.getOne("quotes");
|
|
const INDEX_NAME = "idx_quotes_status";
|
|
const INDEX_SQL = `CREATE INDEX \`${INDEX_NAME}\` ON \`quotes\` (\`status\`)`;
|
|
|
|
const existing = (collection.indexes || []) as string[];
|
|
if (existing.includes(INDEX_SQL)) {
|
|
console.log("quotes.status index already present, skipping.");
|
|
return;
|
|
}
|
|
|
|
await pb.collections.update(collection.id, {
|
|
indexes: [...existing, INDEX_SQL],
|
|
});
|
|
|
|
console.log("Added quotes.status index (idx_quotes_status).");
|
|
console.log("Run /quotes in the browser — the initial load should be visibly faster.");
|
|
}
|
|
|
|
main().catch((err) => {
|
|
console.error(err);
|
|
process.exit(1);
|
|
});
|