# HQ Feature Roadmap (D27+) — designed 2026-07-18 Generated by the ultracode design pass (10 agents). #1 Renewals is DELIVERED; the rest are speced below. ## Build order & collision plan I have what I need. Key concrete finding confirmed: the highest existing Postgres migration is `009-staff-username`, yet **three** features (reconciliation, tds, einvoice) each hardcode `010` — a real collision that forces serialized migration-ID assignment. Also worth flagging honestly: the task names "WhatsApp" as an external-dependency feature, but no WhatsApp spec exists among the 9 — only e-invoice carries a true external dependency. I address both below. --- # BUILD PLAN — SiMS HQ Console, 9 Designed Features Tech-lead sequencing to let parallel work proceed without corrupting shared files. Verified against the live tree: all named shared files exist, and the PG migration ledger currently tops out at `009-staff-username`. ## 1. Shared-file collision map A backend edit to any file below is a **serialization point**: two features editing it concurrently will conflict or silently clobber. Disjoint *new* files (new pages, new `repos-*.ts`) never collide and are the parallelism budget. ### Hot backend files (must serialize edits) | Shared file | renewals | tickets | reconciliation | mis | sms-balance | tds | audit | notifications | einvoice | Notes | |---|:--:|:--:|:--:|:--:|:--:|:--:|:--:|:--:|:--:|---| | `apps/hq/src/api.ts` | ✎ | ✎ | ✎✎ | ✎ | ✎ | ✎ | ✎ | ✎ | ✎✎ | **All 9.** reconciliation & einvoice also *modify existing handlers* (POST `/payments`, GET `/documents/:id/pdf`) — highest conflict risk, not just appends. | | `apps/hq/src/db.ts` | (opt idx) | — | ✎ table+idx | — | ✎ CHECK×3 + migrate() | ✎ table+idx | — | — | ✎ cols + migrate() | 5 writers. Structural edits; must be strictly one-at-a-time. | | `apps/hq/src/migrations-pg.ts` | (opt idx) | — | ✎ `010` | — | ✎ CHECK edit | ✎ `010` | — | — | ✎ `010` | **ID COLLISION** — see §1a. | | `apps/hq/src/scheduler.ts` | — | — | — | — | ✎ | — | — | — | — | Single writer only (renewals explicitly does **not** touch it). Uncontended. | | `apps/hq/src/repos-documents.ts` | reads | reads | reads | reads | reads | — | — | reads | ✎ Doc/DocRow/toDoc | einvoice sole writer (adds nullable fields; non-breaking to readers). | ### Hot frontend files (must serialize edits) | Shared file | renewals | tickets | reconciliation | mis | sms-balance | tds | audit | notifications | einvoice | |---|:--:|:--:|:--:|:--:|:--:|:--:|:--:|:--:|:--:| | `apps/hq-web/src/api.ts` | ✎ | ✎ | ✎ | ✎ | ✎ | ✎ | ✎ | ✎ | ✎ | | `apps/hq-web/src/nav.ts` | ✎ | — | ✎ | ✎ | ✎ | — | ✎ | — | — | | `apps/hq-web/src/main.tsx` | ✎ | — | ✎ | ✎ | ✎ | — | ✎ | — | — | | `apps/hq-web/src/pages/Settings.tsx` | — | — | ✎ | — | ✎ | — | — | — | ✎ | | `apps/hq-web/src/pages/Reports.tsx` | — | — | — | (link only) | — | ✎ | — | — | — | | `apps/hq-web/src/pages/NewDocument.tsx` | — | ✎ | — | — | — | — | — | — | — | | `apps/hq-web/src/Layout.tsx` | — | — | — | — | — | — | — | ✎ | — | ### Single-writer files (safe, no coordination needed) `repos-amc.ts` (renewals) · `repos-tickets.ts` + `repos-modules.ts` (tickets) · `import-apex.ts` (reconciliation, extract `parseCsv`→new `csv.ts`) · `reminder-templates.ts` + `send-reminder.ts` + `repos-reminders.ts` (sms-balance) · `audit.ts` (audit-viewer, *replaces* `listAudit` signature) · `server.ts` + `templates.ts` (einvoice) · `app.css` (notifications). Each has exactly one owning feature — no serialization needed, but see the `audit.ts` and `POST /payments` risks below. ### 1a. Migration-ID collision — MUST fix before any schema feature lands Three specs each declare PG migration `id:'010-…'`. Only one can be `010`. **Do not hardcode `010` in parallel branches.** Assign IDs at *land time* in merge order: - 1st schema feature to merge → `010-…` - 2nd → `011-…` - 3rd → `012-…` - 4th → `013-…` The same land-order discipline governs `db.ts` `migrate()` — additive `ALTER`/CHECK-rebuild blocks must be appended, never interleaved. A schema branch that sat open must rebase its migration number before merge. ## 2. Recommended build order (slices) The dividing line is **schema**. Five features touch no schema (or one optional index) and contend only on additive plumbing (`api.ts`, `web api.ts`, `nav.ts`, `main.tsx`) — cheap to serialize. Four features restructure `db.ts` + `migrations-pg.ts` and must be strictly ordered. ### Wave 1 — no-schema, ship-fast (up to 5 in parallel) **renewals · tickets · mis · audit-viewer · notifications** All read-heavy, zero migration risk, immediate owner-visible value (a Renewals command center, a ticket Board, an MIS cockpit, an audit log, a live bell). Their *pages and repos are disjoint*, so build fully in parallel. The **only** coordination is a short merge queue on the four additive plumbing files; none of them touch `Settings.tsx`, `db.ts`, or `migrations-pg.ts`. - Land `mis` and `audit-viewer` first (pure reads, no cross-feature reuse, lowest risk — good pipe-cleaners for the plumbing-file merge discipline). - `notifications` reuses `duesAging`/`queueCounts`/`ticketCounts`/`outstandingPaise` (all existing) and owns `Layout.tsx` alone — independent. - `tickets` owns `repos-tickets.ts`, `repos-modules.ts`, `NewDocument.tsx` alone; watch only the `/tickets/board` before `/tickets/:id` route-order guard. - `renewals` owns `repos-amc.ts` alone; optional `ix_amc_period_to` index is the *only* db.ts touch — land it in Wave 1's queue or defer it. ### Wave 2 — schema features, strictly serialized (one at a time through db.ts + migrations-pg.ts) Build in parallel branches, but **land in this order**, each claiming the next migration ID: 1. **tds** → `010`. Simplest: one additive table, one Reports tab, no cross-cutting edits. Proves the migration-ID hand-off. 2. **reconciliation** → `011`. New table + `import-apex.ts` parser extraction + **modifies `POST /payments`** + `Settings.tsx` section. Land before sms-balance so the `Settings.tsx` and `api.ts` churn settles. 3. **sms-balance** → `012` (CHECK-list edit, not a new table). Most cross-cutting backend: sole `scheduler.ts` writer plus the `ReminderRuleKind` chain (`reminder-templates.ts`, `send-reminder.ts`, both CHECK constraints) — all must change together or typecheck/insert fails. 4. **einvoice** → `013`. **Land last** — largest surface (cols + `server.ts` + `templates.ts` + `repos-documents.ts` + `Settings.tsx`) and externally blocked (§4). Build now against `NullGspAdapter`; its migration number is simply whatever is free at merge. `Settings.tsx` is edited by reconciliation, sms-balance, and einvoice — all in Wave 2 and already serialized by `db.ts`, so that frontend collision piggybacks on the schema serialization for free. **Cross-wave note:** Wave 2 branches can *start* while Wave 1 is in flight (different files), but no Wave-2 branch merges until it has rebased onto the current `db.ts`/`migrations-pg.ts` head and claimed a fresh migration ID. ## 3. Per-feature build recipes & effort **renewals — M.** No schema (optional `ix_amc_period_to`). New `repos-renewals.ts`: `listRenewals` merges two portable SELECTs (`client_module`, `amc_contract`) in TS — *not* a UNION — buckets by `daysBetweenIso`, resolves amounts via `priceOn`/`amount_paise`, paginates with a true COUNT; `generateModuleRenewalQuote` + a sibling `generateAmcRenewalQuote` in `repos-amc.ts` mint unissued PROFORMA drafts through `createDraft`. Routes: `GET /renewals`, two `POST …/renewal-quote` (all `requireAuth`). Tests: bucket boundaries, null-price flag (no silent 0), pagination total, draft-mint + audit. UI: new `Renewals.tsx` + nav/route/api-client. *Guard:* skip/flag non-`YYYY-MM-DD` dates rather than crash. **tickets — M.** No migration. `repos-tickets.ts`: `ticketBoard` (SQL GROUP BY for assignee counts + JS `daysBetween` bucketing for aging/SLA) and `ticketDraftPrefill`; `getModuleByCode` in `repos-modules.ts`. Routes `GET /tickets/board` + `GET /tickets/:id/prefill` (`requireAuth`) — **`/board` must register before `/:id`** (order-guard test). Tests: assignee counts exclude terminal rows, aging buckets, SLA override, case-insensitive module match, route-order. UI: List|Board toggle in `Tickets.tsx`, Bill action → `NewDocument.tsx` reads `router.state.prefill` once (server-authoritative). Bill stays a *prefill*, never a one-shot create, so unpriced addons can be hand-priced. **reconciliation — L.** Migration (bank_txn table + 3 indexes) + extract `parseCsv` into new `csv.ts`. `repos-reconciliation.ts`: `stageBankStatement` (INSERT-OR-IGNORE on fingerprint), `listBankTxns`, `suggestMatches` (LOWER LIKE, reuses `outstandingPaise`), `confirmBankTxn` (reuses `recordPayment` wholesale, status guard under txn). Routes: 6 reconciliation + 2 `/settings/receipts` + **modify `POST /payments`** + `maybeAutoEmailReceipt` helper (post-commit, failure→`{ok,warning}`, never rollback — mirrors convert-and-send). Tests: CSV normalize, fingerprint idempotency, suggestion ranking, confirm→payment+allocation+audit in one txn, double-confirm throws, auto-email flag on/off, PG parity. UI: `Reconciliation.tsx` + `Settings.tsx` toggle. **mis — M.** No schema. `repos-mis.ts` `misCockpit` fans out to existing `duesAging`/`queueCounts`/`ticketCounts` + a handful of `COALESCE(SUM/COUNT)` month-bounded queries (revenue, credit-notes, collections, pipeline, renewals). One route `GET /reports/mis` (`requireAuth`+`requireOwner`). Tests lock the reconcile-with-Reports invariant (outstanding block == sum of `duesAging` rows) + this-vs-last-month deltas + owner gate. UI: `MisCockpit.tsx` stat-card grid with deep links. *Decide with owner:* headline = net revenue (gross+CN as subline); collections exclude TDS. **sms-balance — M.** CHECK-list edit adds `sms_balance_low` in **three** db.ts places + migrations-pg CHECK + the exhaustive `ReminderRuleKind` switches (`reminder-templates.ts`, `send-reminder.ts` internal-kind guard) — all together or the build breaks. `repos-sms.ts`: JS-side `field_values` JSON parse (portable), `smsLowBalance` report (unparseable→`unknownCount`, never dropped), `createSmsTopupQuote` (omits price so `buildLines` resolves the D23 rate card; below-min throws). Routes: report + CSV + top-up-quote + `/settings/sms`. Optional once-a-month scheduler alert (internal-only, never emailed). UI: `SmsBalance.tsx` + `Settings.tsx` section. *Honest limit:* `sms_balance` is a stale point-in-time APEX field, not a live gateway feed. **tds — M.** Additive `tds_certificate` table + `(client_id,fy)` index. `repos-tds.ts` mirrors `repos-payments.ts`: record/list/delete (audited; certificates are corrigible, deletion allowed) + `tdsOutstanding` (expected = JS `fyOf`-bucketed `payment.tds_paise`, received = `SUM(certificate.amount_paise)`, outstanding **not clamped** so mismatches surface). Routes: report (`requireAuth`) + two mutations (`requireManagerial`) + delete. Tests: FY boundary (`fyOf` 03-31 vs 04-01), negative outstanding kept, validation, audit rows. UI: TDS tab on `Reports.tsx` (no nav change). *Known gap:* legacy `tds_paise=0` imports understate expected — label it. **audit-viewer — M.** No schema. **Replace** `listAudit(db,limit)` with `listAudit(db,filter):AuditPage` (WHERE-1=1 builder, `pageSize` cap 200, honest COUNT, `ORDER BY id DESC`) + `auditFacets`. Grep confirms zero external callers — **re-grep `listAudit` immediately before landing** in case a parallel branch added one. Routes: `GET /audit` + `/audit/facets` (`requireAuth`+`requireOwner`, no writeAudit). Tests: each filter narrows total, date-only `to` end-of-day inclusive, pagination total ≠ page length, pageSize cap. UI: `Audit.tsx` (Documents.tsx template) + `JsonDiff.tsx` (tolerate null/scalar/large payloads). *Decide:* UTC vs local date-range semantics — pick one, test it. **notifications — M.** No schema (watermark is one `setting` row `notif_seen:`). `repos-notifications.ts` `notificationFeed` computes on demand from existing sources (overdue via `outstandingPaise`, renewals, failed sends via `queueCounts`, aging/new tickets via `ticketCounts`, gmail-down) with `ownerScope`; unread = items past watermark; honest uncapped group totals, capped items list. Routes: `GET /notifications` + `POST /notifications/seen`. UI: `NotificationsBell.tsx` in `Layout.tsx` — **consolidates the two existing sidebar badge fetches into this one endpoint** (net fewer calls). Tests: scope asymmetry (money owner-scoped, tickets team-visible), watermark unread flip, uncapped total. **einvoice — L.** Additive: 11 `einv_*` doc cols + `client.pincode`/`stg_client.pincode` via `migrate()`. Pure `einv-payload.ts` builder (schema 1.1) + `assertReconciles` (fail loud on >₹1 drift *before* submit); one pluggable `GspAdapter` interface + `nullGspAdapter` default + ClearTax/MastersIndia skeletons reusing gmail's injected `Fetcher`; `repos-einvoice.ts` stamps IRN/QR as **metadata** (adapter call outside txn, guard re-checked inside — the immutability argument mirrors `due_date` stamping; record a D-number); `qr.ts` renders signed-QR PNG data-URI onto the INVOICE/CREDIT_NOTE PDF. Routes: generate/cancel (24h window) + `/settings/einvoice`. Extends `repos-documents.ts` Doc/DocRow/toDoc and `server.ts` deps wiring. Fully buildable and testable now against the fake adapter; **go-live blocked externally (§4).** ## 4. External-dependency features — what the user must provide **Honesty note on the premise:** the brief names *"WhatsApp, GST e-invoice"* as the two external-dependency features, but **there is no WhatsApp feature among these 9 specs.** The nearest candidate, **sms-balance**, explicitly declares `externalDeps: []` and states its balance is a stale APEX field, *not* a live SMS/WhatsApp gateway — so it needs nothing from the user beyond the existing DB. Likewise **reconciliation** lists no *new* external dep (it reuses the already-wired Gmail). That leaves exactly **one** genuinely external-blocked feature: **einvoice (GST e-invoice / IRN+QR) — the user must provide, before go-live:** 1. **A GSP account** — commercial signup with **ClearTax or Masters India** (pick one). Deliverables: sandbox + production **API base URLs**, **client-id / client-secret**, and the **GSTIN-linked API username/password**. These live in **env** (`GSP_*`), never the DB — same pattern as `GOOGLE_*`/`AWS_*`. 2. **GST-portal e-invoice enablement** for the company GSTIN — NIC enables e-invoicing on the GSTIN once turnover crosses the notified threshold. A compliance/account action, not code. 3. **An owner decision**: has annual turnover crossed the threshold, and **from which FY** — drives `einvoice.enabled` + `einvoice.threshold_from` (stored as dated config; never hardcode the ₹-crore number). Until all three exist, `nullGspAdapter` keeps the entire feature buildable, testable, and mergeable — only the live IRN generation is inert. The one non-external note: add the `qrcode` (+`@types/qrcode`) npm dependency to `apps/hq/package.json` and confirm it bundles in the esbuild `dist/server.cjs`. If a **WhatsApp** capability is genuinely wanted, it is **unspecified** — it would need its own spec (provider = Meta Cloud API or an aggregator like Gupshup/Twilio; that's a separate external signup and a new design pass) before it can be sequenced. --- ## Per-feature specs ### renewals — effort M A Renewals and AMC command center: one unified, paginated view of module renewals (client_module.next_renewal) and AMC renewals (amc_contract.period_to) due in 30/60/90 days. Each row shows the renewal amount (dated price book via priceOn for modules; amount_paise for AMC), days-until, bucket, and for AMC its paidStatus; plus a one-click Generate renewal quote that mints a PROFORMA carrying the module/AMC line forward through the existing createDraft path. The reminder side is ALREADY built: scheduler.ts (lines 151-175) scans renewal_due (client_module) and amc_expiring (amc_contract); both rule_kinds already exist in the reminder CHECK constraint (db.ts ~339) and in reminder-templates.ts, and already surface in the manual queue and Dashboard.renewalsThisMonth. So NO scheduler or reminder-kind work is needed. This feature is a read view plus a quote action layered on that existing data. New repos-renewals.ts holds the unified query and the module-quote generator; an AMC PROFORMA quote generator sits beside generateAmcRenewalInvoice in repos-amc.ts. New /renewals page plus nav item. No hard rule bent: amounts stay integer paise, GST is computed only inside createDraft/computeBill, the generated PROFORMA is a mutable draft (never issued here), prices resolve via dated priceOn rows, SQL stays portable with ? placeholders, and generation is audited in-txn (createDraft/insertDocRow already do). **Shared files:** apps/hq/src/api.ts (new renewals route block + imports from repos-renewals and the new repos-amc export), apps/hq/src/repos-amc.ts (add generateAmcRenewalQuote), apps/hq/src/db.ts (optional ix_amc_period_to index in SCHEMA string), apps/hq/src/migrations-pg.ts (mirror optional index), apps/hq-web/src/api.ts (getRenewals + two quote helpers + types), apps/hq-web/src/nav.ts (Renewals nav item), apps/hq-web/src/main.tsx (/renewals route) **Schema:** - No new tables or columns required. renewal_due and amc_expiring rule_kinds already exist in the reminder CHECK constraint; client_module.next_renewal and amc_contract.period_to/amount_paise already exist; ix_client_module_renewal on next_renewal already exists (db.ts:121). - Optional (perf only): add 'CREATE INDEX IF NOT EXISTS ix_amc_period_to ON amc_contract (period_to)' to the SCHEMA string in apps/hq/src/db.ts next to ix_amc_client, and mirror the same CREATE INDEX in apps/hq/src/migrations-pg.ts (both engines). No migrate()/ALTER, no data backfill. **Repos:** - apps/hq/src/repos-renewals.ts (NEW): export type RenewalSource = 'module' | 'amc'; export interface RenewalRow { source; refId (client_module.id or amc_contract.id); clientId; clientName; clientCode; label; moduleId: string|null; moduleCode: string|null; kind: Kind|null; edition: string|null; renewalDate: string; daysUntil: number; bucket: 30|60|90|'overdue'; amountPaise: number|null; paidStatus?: 'paid'|'unpaid'|'unbilled' } -- one normalized row for a module or AMC renewal. - apps/hq/src/repos-renewals.ts: export interface RenewalsPage { rows: RenewalRow[]; total: number; page: number; pageSize: number; summary: { count30; count60; count90; overdue; amountPaise30; amountPaise60; amountPaise90 } } -- honest total + horizon rollups (rule 8, no silent caps). - apps/hq/src/repos-renewals.ts: export async function listRenewals(db, opts: { today: string; horizonDays?: 30|60|90; source?: RenewalSource; clientId?: string; includeLapsed?: boolean; page?: number; pageSize?: number }): Promise -- queries client_module (active=1, next_renewal not null, within horizon) and amc_contract (active=1, period_to within horizon), computes daysUntil via daysBetweenIso(today,date), resolves module amount with priceOn(moduleId,kind,edition,today) (null when no dated row) and AMC amount from amount_paise, AMC paidStatus via amcPaidStatus, sorts by renewalDate asc, paginates with a true COUNT total. Bounded per-page priceOn N+1 is fine (pageSize cap 200). - apps/hq/src/repos-renewals.ts: export async function generateModuleRenewalQuote(db, userId, clientModuleId, opts?: { docType?: 'PROFORMA'|'QUOTATION' }): Promise -- loads client_module, calls createDraft(db,userId,{ docType: opts?.docType ?? 'PROFORMA', clientId: cm.clientId, lines:[{ moduleId: cm.moduleId, qty:1, kind: cm.kind, edition: cm.edition }] }); unitPricePaise defaults to priceOn(today) inside buildLines (throws loudly if no dated price -- surfaced to caller, not silent 0). Returns the unissued draft. Mirrors generateAmcRenewalInvoice but PROFORMA + not issued. - apps/hq/src/repos-amc.ts (ADD, beside generateAmcRenewalInvoice): export async function generateAmcRenewalQuote(db, userId, amcId): Promise -- createDraft PROFORMA with one line on the seeded AMC module (code 'AMC'), description = coverage or 'Annual Maintenance', qty 1, kind 'yearly', unitPricePaise = amc.amountPaise. Returns an unissued draft; does NOT set invoice_doc_id (a quote is not the tax invoice, unlike the existing invoice generator). **Routes:** - GET /api/renewals?horizon=30|60|90&source=module|amc&clientId=&includeLapsed=&page=&pageSize= | gate requireAuth | returns { ok, ...RenewalsPage }. Renewals are shared collection work (like the reminder queue's doc-less rows), so no owner-scope by default; matches GET /reports/* which are requireAuth. Add to api.ts in a new '// ---------- renewals ----------' block near the amc block. - POST /api/client-modules/:id/renewal-quote | gate requireAuth (same as POST /documents -- drafting is not destructive) | body optional { docType } | returns { ok, document }. 404 if client_module missing. - POST /api/amc/:id/renewal-quote | gate requireAuth | returns { ok, document }. Sits in the existing amc route group; note the sibling POST /amc/:id/renewal-invoice is requireOwner because it ISSUES an invoice -- this one only drafts, so requireAuth is intentional and consistent with document drafting. 404 if amc missing. **UI:** - apps/hq-web/src/pages/Renewals.tsx (NEW) at route /renewals: PageHeader; FilterChips for horizon 30/60/90 and a source toggle (All/Modules/AMC); three summary tiles (count + total amount per horizon) using formatINR; DataTable columns Client, Item (module name+edition or AMC coverage), Renewal date, Days-until (rowTone warn <=30, err overdue), Amount (formatINR, or a 'Set price' flag when amountPaise is null), Paid (AMC only), Action = 'Generate quote' button. Reuses @sims/ui DataTable/EmptyState/ErrorState/Skeleton/PageHeader/FilterChips and the useData hook from Clients.tsx (same as Reports.tsx). - Generate quote action calls generateModuleRenewalQuote(cmId) or generateAmcRenewalQuote(amcId), then nav(`/documents/${document.id}`) to open the new PROFORMA draft in DocumentView (reuse existing convert flow UX). - apps/hq-web/src/main.tsx: add } /> alongside the other lazy page routes. - apps/hq-web/src/nav.ts: add { to: '/renewals', label: 'Renewals', icon: CalendarClock } to the Work group (import CalendarClock from lucide-react), placed after Reminders. - apps/hq-web/src/api.ts: add getRenewals(params): Promise, generateModuleRenewalQuote(cmId): Promise, generateAmcRenewalQuote(amcId): Promise, plus RenewalRow/RenewalsPage types (mirrors getDocuments + createDocument helpers). - Optional: add a 'Generate quote' button to the existing Dashboard renewalsThisMonth list and the ClientDetail AMC/modules cards, reusing the same two api helpers (no new endpoints). **Tests:** - apps/hq/test/repos-renewals.test.ts: listRenewals buckets -- seed client_modules with next_renewal at today+20/+50/+80/+100 and assert bucket 30/60/90 and exclusion of +100 (and inclusion when includeLapsed for past dates); assert daysUntil values. - listRenewals amount resolution: module row amount comes from the dated price_book row effective on today (priceOn), and is null when no price row exists for that kind/edition (row still listed, flagged) -- proves no silent 0. - listRenewals AMC rows: amount = amount_paise, paidStatus reflects amcPaidStatus (unbilled/unpaid/paid), period_to within horizon; source filter returns only the requested source; summary rollups sum correctly. - listRenewals pagination: total is the true count across both sources, page/pageSize honored, cap 200 (rule 8). - generateModuleRenewalQuote: creates a PROFORMA draft (doc_no null, status draft) carrying the module line at the dated price; totals equal a computeBill of that line; writes a document create audit row; throws on unknown client_module; throws loudly when the module has no dated price (no silent 0). - generateAmcRenewalQuote: creates a PROFORMA draft with the AMC line at amount_paise; does NOT mutate amc_contract.invoice_doc_id; throws on unknown amc. - apps/hq/test/api-renewals.test.ts (supertest, mirrors existing api tests): GET /renewals shape + requireAuth gate; POST /client-modules/:id/renewal-quote and POST /amc/:id/renewal-quote return a draft document and 404 on missing ids. --- ### tickets — effort M Ticket-desk upgrade in two parts. (A) Aggregations over the existing ticket table: per-assignee open workload counts (with an unassigned bucket), open-ticket age buckets, an oldest-open list, and a configurable SLA-breach count. Exposed via one new GET /tickets/board route (requireAuth, team-visible) and rendered as a Board view toggle on the existing Tickets page. (B) Bill-this-ticket: a per-row action on billable tickets that navigates to NewDocument prefilled with the ticket client plus one seeded line (ticket module_code resolved to a real module id), reusing the unchanged createDraft/prepareDraft path so the staffer sets the unit price, previews, and saves a QUOTATION/PROFORMA/INVOICE draft. No schema migration; aggregation date math done in JS (the house dashboard daysBetween pattern) so it stays portable across SQLite dev and Postgres prod. **Shared files:** apps/hq/src/api.ts, apps/hq/src/repos-tickets.ts, apps/hq/src/repos-modules.ts, apps/hq-web/src/api.ts, apps/hq-web/src/pages/Tickets.tsx, apps/hq-web/src/pages/NewDocument.tsx **Schema:** - No new tables or columns; NO migration added to db.ts SCHEMA or migrations-pg.ts. Aggregations run over the existing ticket table (id, client_id, kind, module_code, status, assigned_to, opened_on). - Two optional config-over-code rows in the existing setting table, read with code defaults so absence is harmless: tickets.sla_days (default 7) via getNumberSetting; tickets.billable_kinds (default 'SOFTWARE ADDONS,MODIFICATION,NEW WORK') via a plain getSetting string read. - Existing indexes ix_ticket_status and ix_ticket_assigned already cover the board status filter and GROUP BY assigned_to; no new index needed at ~4.5k-ticket scale. **Repos:** - apps/hq/src/repos-tickets.ts: export async function ticketBoard(db: DB, today: string): Promise - single aggregation call. TicketBoard = { asOf: string; slaDays: number; slaBreaches: number; byAssignee: {assignedTo: string|null; assignedName: string|null; open: number; in_progress: number; waiting: number; active: number}[]; ageBuckets: {bucket: string; label: string; count: number}[]; oldestOpen: {id; clientId; clientName; kind; moduleCode: string|null; openedOn; ageDays: number; status: TicketStatus; assignedName: string|null}[] }. - repos-tickets.ts internals: byAssignee via SQL GROUP BY assigned_to over non-terminal rows (status IN open,in_progress,waiting) LEFT JOIN staff_user for the name, using COUNT(CASE WHEN status=...) per column (portable, no date math, no silent cap). ageBuckets + oldestOpen + slaBreaches computed by fetching the non-terminal rows (reusing the existing SELECT join for client/branch/assigned names) and bucketing in JS with a local daysBetween(openedOn, today) copy (mirrors repos-dashboard.ts) into 0-2 / 3-7 / 8-30 / 31-90 / 90+ day buckets; slaBreaches = count of non-terminal rows with ageDays >= slaDays; oldestOpen = the 10 largest ageDays, most-overdue first. - apps/hq/src/repos-tickets.ts: export async function ticketDraftPrefill(db: DB, id: string): Promise - resolves a ticket into a NewDocument prefill. TicketPrefill = { clientId; clientCode; clientName; clientStateCode; moduleId: string|null; moduleCode: string|null; description: string; suggestedKind: 'one_time' }. Reuses getTicket, getClient (code/name/stateCode), and getModuleByCode. Returns null when the ticket is missing. - apps/hq/src/repos-modules.ts: export async function getModuleByCode(db: DB, code: string): Promise - new helper (SELECT ... FROM module WHERE UPPER(code)=UPPER(?) AND active=1) mapping the ticket free-text module_code to a real module id; returns null on no match so prefill degrades gracefully (moduleId stays null, staffer picks one). **Routes:** - GET /tickets/board gate: requireAuth (team-visible desk, same as GET /tickets) - returns ticketBoard(db, today), today computed server-side as new Date().toISOString().slice(0,10); try/catch -> 400 {ok:false,error} like the other ticket routes. - GET /tickets/:id/prefill gate: requireAuth - returns {ok:true, prefill} from ticketDraftPrefill; 404 {ok:false} when the ticket is missing. Frontend calls this on Bill click, then navigates to /documents/new carrying the payload in router state (server-authoritative prefill, avoids trusting the list row for client code/state). - ROUTE ORDER: register both in api.ts alongside the existing ticket block (~line 757); the literal GET /tickets/board MUST be declared before GET /tickets/:id/prefill (and before any future /tickets/:id GET) so Express does not treat 'board' as an :id. **UI:** - apps/hq-web/src/pages/Tickets.tsx: add a List | Board view toggle (FilterChips or two Buttons) in the existing Toolbar; default List keeps current behavior. Board mode hides the status/mine/search filters (board is whole-desk, honest numbers) and renders a new TicketBoardView section instead of the DataTable. - apps/hq-web/src/pages/Tickets.tsx new TicketBoardView(): loads getTicketBoard() via useData; renders (1) an Aging strip = ageBuckets as labeled count pills (0-2d/3-7d/8-30d/31-90d/90+d) plus an SLA-breach Badge (tone 'err' when slaBreaches>0, e.g. '12 over SLA (7d)'); (2) a By-assignee DataTable (Assignee - unassigned row labelled 'Unassigned', Open, In progress, Waiting, Active total) where clicking a row switches back to List pre-filtered to that assignee; (3) an Oldest-open DataTable (Age days - red when >= slaDays, Client Link to /clients/:id, Kind, Module, Opened, Assigned) with the Bill action per row. - apps/hq-web/src/pages/Tickets.tsx: add a Bill Button to TicketActions (or a dedicated cell) shown for billable/non-terminal tickets; on click call getTicketPrefill(t.id) then navigate('/documents/new', { state: { prefill } }). - apps/hq-web/src/pages/NewDocument.tsx: read useLocation().state?.prefill once on mount (useEffect, empty deps, same 'prefill once, never clobber edits' guard already used for dueDate). If present: set client from a minimal Client built from prefill (id/code/name/stateCode) so the type-ahead shows it picked; seed lines[0] = { moduleId: prefill.moduleId ?? '', kind: 'one_time', qty: '1', description: prefill.description }; if moduleId resolved, call existing prefill(0, moduleId, 'one_time', 'standard') to pull the dated unit price, else leave unitRs blank+editable. - apps/hq-web/src/api.ts: add getTicketBoard(): Promise -> apiFetch('/tickets/board'); getTicketPrefill(id): Promise -> apiFetch('/tickets/'+id+'/prefill').then(r=>r.prefill); export TicketBoard and TicketPrefill interfaces mirroring the repo DTOs. **Tests:** - apps/hq/test/tickets.test.ts (extend): ticketBoard byAssignee - seed tickets across two staff + unassigned in mixed statuses; assert per-assignee open/in_progress/waiting/active counts, that terminal (closed/dropped) rows are excluded, and that an Unassigned bucket appears. - ticketBoard ageBuckets + slaBreaches: seed opened_on at today-1, today-5, today-20, today-60, today-120; assert each lands in the right bucket, oldestOpen is ordered most-overdue-first and capped at 10, and slaBreaches counts ageDays>=slaDays for both the default 7 and an overridden tickets.sla_days setting row. - getModuleByCode: case-insensitive match, ignores inactive modules, returns null on miss. - ticketDraftPrefill: known module_code resolves moduleId; unknown/empty module_code yields moduleId null but still returns client id/code/name/stateCode + ticket description; missing ticket returns null. - Route tests: GET /tickets/board returns 200 with the DTO shape under requireAuth; GET /tickets/:id/prefill returns 404 for a missing id; /tickets/board is not mis-routed as a ticket id (order guard). --- ### reconciliation — effort L Bank-statement CSV reconciliation for HQ collections: stage a bank CSV (date, amount, reference, narration) into a durable staging table, auto-suggest matches to open invoices by resolving the client from reference/narration tokens and ranking candidate unsettled invoices by amount-vs-outstanding, then let staff confirm each suggested match into a real payment+allocation by reusing recordPayment (explicit allocations, mode 'bank', receivedOn = txn date). Separately, gate auto-emailing the receipt PDF: when a new flat setting receipt.autoemail.enabled='1', the /payments and confirm routes force issueReceipt=true and, strictly AFTER the payment commits, render the RECEIPT via documentHtml+renderPdf and send it with gmail.sendDocumentEmail — a send failure surfaces as a warning, never rolling back the payment (mirrors POST /documents/:id/convert-and-send). Design-only; nothing was changed. Everything obeys the house rules: integer paise end-to-end (credits only; debits flagged/ignored), no GST touched (receipts already carry zero tax via generateReceipt), config-over-code flat policy setting like quote.followup.policy, portable async repos with ? placeholders on the DB handle, INSERT OR IGNORE idempotency on a fingerprint (same pattern as upsertReminder / APEX staging), and writeAudit in the same transaction as every mutation. **External deps:** None new — reuses the already-wired Gmail connection (GOOGLE_* env + HQ_SECRET_KEY) for auto-receipt; no new provider, key, or account decision. Auto-email is off by default until the owner flips the setting and Gmail is connected. **Shared files:** apps/hq/src/db.ts (SCHEMA + INDEX_DDL — new table + indexes), apps/hq/src/migrations-pg.ts (new 010 migration + PG indexes), apps/hq/src/api.ts (6 reconciliation routes + 2 settings routes + modify POST /payments + maybeAutoEmailReceipt helper), apps/hq/src/import-apex.ts (extract parseCsv into new csv.ts and import it), apps/hq-web/src/api.ts (typed calls + interfaces), apps/hq-web/src/nav.ts (new nav item), apps/hq-web/src/main.tsx (new route), apps/hq-web/src/pages/Settings.tsx (auto-email toggle) **Schema:** - db.ts SCHEMA — new table bank_txn(id TEXT PK, batch_id TEXT NOT NULL, batch_label TEXT NOT NULL DEFAULT '', txn_date TEXT NOT NULL, amount_paise INTEGER NOT NULL, direction TEXT NOT NULL DEFAULT 'credit' CHECK(direction IN ('credit','debit')), reference TEXT NOT NULL DEFAULT '', narration TEXT NOT NULL DEFAULT '', fingerprint TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'unmatched' CHECK(status IN ('unmatched','confirmed','ignored')), matched_client_id TEXT, payment_id TEXT, problems TEXT NOT NULL DEFAULT '[]', created_by TEXT NOT NULL, created_at TEXT NOT NULL, UNIQUE(fingerprint)) — amounts integer paise; UNIQUE(fingerprint) makes re-importing an overlapping export window a no-op (INSERT OR IGNORE), matching the APEX/upsertReminder idempotency style. Durable (not one-shot like stg_client): rows persist through the confirm workflow. - db.ts INDEX_DDL — add CREATE INDEX IF NOT EXISTS ix_bank_txn_batch ON bank_txn(batch_id); ix_bank_txn_status ON bank_txn(status); ix_bank_txn_client ON bank_txn(matched_client_id). Runs on both engines (same DDL) after migrate(). - migrations-pg.ts — new PG_MIGRATIONS entry id:'010-bank-reconciliation' creating bank_txn (amount_paise BIGINT per the D19 money rule; 0/1 flags n/a here; CHECKs born final) plus the three ix_bank_txn_* indexes. No SQLite migrate() column-add needed since it is a brand-new CREATE TABLE IF NOT EXISTS in SCHEMA. - No new setting TABLE — reuse the existing setting(key,value) row via setSetting/getSetting: key receipt.autoemail.enabled ('1'|'0', default '0'), a flat operational policy exactly like quote.followup.policy (NOT a dated config row — it is a switch, not a price/rate). **Repos:** - NEW apps/hq/src/csv.ts — export function parseCsv(text:string):Record[] extracted verbatim from import-apex.ts (currently private there) so both importers share ONE parser; import-apex.ts changes to import it. No behavior change. - NEW apps/hq/src/repos-reconciliation.ts — export interface BankTxn{id;batchId;batchLabel;txnDate;amountPaise;direction;reference;narration;status;matchedClientId:string|null;paymentId:string|null;problems:string[];createdBy;createdAt} + toBankTxn(row) mapper. - repos-reconciliation.ts — export async function stageBankStatement(db:DB,userId:string,input:{batchLabel?:string;csvText:string;columns?:{date?:string;amount?:string;credit?:string;debit?:string;reference?:string;narration?:string};dateFormat?:'iso'|'dmy'}):Promise<{batchId:string;staged:number;skipped:number;problems:number}> — parseCsv, map headered columns (default names date/amount|credit+debit/reference/narration), normalise date (ISO or DD/MM/YYYY) and amount via fromRupees at the edge (strip ₹/commas), classify credit vs debit, compute fingerprint=sha256(txnDate|amountPaise|reference|narration), per-row problems[] like APEX (bad date/amount), INSERT OR IGNORE, one db.transaction wrapping all inserts + a single writeAudit('import_stage','bank_txn',batchId,{staged,skipped}). - repos-reconciliation.ts — export async function listBankTxns(db:DB,opts:{batchId?:string;status?:string;page?:number;pageSize?:number}):Promise<{rows:BankTxn[];total:number;page:number;pageSize:number}> — paginated, honest total (rule 8), newest batch first. - repos-reconciliation.ts — export interface MatchSuggestion{clientId;clientName;clientCode;docId;docNo:string|null;outstandingPaise:number;confidence:'exact'|'client'|'weak';reason:string} + export async function suggestMatches(db:DB,txnId:string,limit=5):Promise — resolve candidate clients by tokenising reference+narration and LIKE-matching client.code/name (both engines, LOWER LIKE like listDocuments q), then for each candidate call unsettled invoices (reuse the outstanding read) and rank: outstandingPaise===amount_paise → 'exact'; client resolved + invoice open → 'client'; amount matches a client's total open → 'weak'. Reuses outstandingPaise(db,docId) from repos-payments. - repos-reconciliation.ts — export async function confirmBankTxn(db:DB,userId:string,txnId:string,input:{clientId:string;allocations?:{documentId:string;amountPaise:number}[];tdsPaise?:number;issueReceipt?:boolean}):Promise<{payment:Payment;allocated;receipt?:Doc;txn:BankTxn}> — one db.transaction: re-read txn FOR UPDATE (postgres) / mutex (sqlite) and assert status==='unmatched' (idempotency guard, throws 'Already reconciled' otherwise), call recordPayment(db,userId,{clientId,receivedOn:txn.txnDate,mode:'bank',reference: txn.reference||txn.narration, amountPaise:txn.amountPaise, tdsPaise, allocations, issueReceipt}), then UPDATE bank_txn SET status='confirmed',matched_client_id=?,payment_id=? and writeAudit('reconcile','bank_txn',txnId,{status:'unmatched'},{status:'confirmed',clientId,paymentId}) in the SAME txn. recordPayment already writes its own payment/allocation/receipt audit rows. - repos-reconciliation.ts — export async function ignoreBankTxn(db,userId,txnId):Promise and reconcileSummary(db,batchId?):Promise<{unmatched;confirmed;ignored;unmatchedPaise}> — both audited/paginated as appropriate. - NO change to repos-payments.ts — recordPayment already accepts allocations[] + issueReceipt and returns receipt; reused as-is. NO gmail import inside any repo (portable-repo rule) — auto-email lives in the API layer. **Routes:** - POST /reconciliation/import — requireAuth + requireManagerial (bulk financial import, gated like /documents/:id/cancel and /share). Body {batchLabel?,csvText,columns?,dateFormat?} → stageBankStatement → {ok,batchId,staged,skipped,problems,summary}. - GET /reconciliation/txns?batchId=&status=&page=&pageSize= — requireAuth. → listBankTxns (honest total). - GET /reconciliation/txns/:id/suggestions — requireAuth. → {ok,suggestions:suggestMatches()} (read-only, writes nothing). - POST /reconciliation/txns/:id/confirm — requireAuth (mirrors POST /payments being staff-open). Body {clientId,allocations?,tdsPaise?,issueReceipt?}. Runs confirmBankTxn; THEN, after commit, calls the shared maybeAutoEmailReceipt() helper. → {ok,payment,allocated,txn,receipt?,warning?}. - POST /reconciliation/txns/:id/ignore — requireAuth → ignoreBankTxn. - GET /reconciliation/summary?batchId= — requireAuth → reconcileSummary. - GET /settings/receipts — requireAuth + requireOwner → {ok,autoEmail: getSetting('receipt.autoemail.enabled')==='1'}. - PUT /settings/receipts — requireAuth + requireOwner. Body {autoEmail:boolean} → setSetting('receipt.autoemail.enabled', autoEmail?'1':'0') (audited by setSetting). - MODIFY POST /payments — after recordPayment commits, if receipt.autoemail.enabled==='1' force a receipt (issueReceipt) and call maybeAutoEmailReceipt(); email strictly post-commit, failure returns {ok:true,...,warning} not a rollback. - NEW helper inside apiRouter: maybeAutoEmailReceipt(receipt:Doc, client:Client) — guard emailStatus().connected/!dead, resolve client contact email, renderPdfImpl(documentHtml(receipt,client,company)), sendDocumentEmail(db,gmail(),{documentId:receipt.id,to,subject:`RECEIPT ${receipt.docNo} — ${companyName}`,bodyText,pdf,userId}); reuses the exact pattern in /documents/:id/convert-and-send (lines 996-1017). **UI:** - NEW apps/hq-web/src/pages/Reconciliation.tsx at route /reconciliation — top: import panel (file input or paste textarea + batch label + optional column-mapping/date-format selects) → POST import; below: status-chip filter (unmatched/confirmed/ignored) + paginated txn table (date, amount via formatINR, reference, narration, status). Each unmatched row expands to a suggestions list (client · invoice docNo · outstanding · confidence badge) with radio-select, an issue-receipt checkbox, Confirm and Ignore buttons. Reuses Pager, PulseRibbon, formatINR, useData hook, and the same table/tone styling as ClientDetail/Documents. - apps/hq-web/src/main.tsx — register the /reconciliation route (lazy page like the others). - apps/hq-web/src/nav.ts — add { to:'/reconciliation', label:'Reconciliation', icon: Banknote } to the Work group (import Banknote from lucide-react), near Documents. - apps/hq-web/src/api.ts — add typed calls: importBankStatement(body), getBankTxns(opts), getTxnSuggestions(id), confirmTxn(id,body), ignoreTxn(id), getReconSummary(batchId?), plus BankTxn/MatchSuggestion interfaces; and getReceiptSettings()/putReceiptSettings(autoEmail) for the toggle. Mirror existing camelCase call style. - apps/hq-web/src/pages/Settings.tsx — add an owner-only 'Auto-email receipts' toggle bound to GET/PUT /settings/receipts (sits beside the sharing/reminders owner settings). - Optional: ClientDetail Payments tab gains a small 'from bank import' provenance note when a payment has a linked bank_txn (nice-to-have, not required for the slice). **Tests:** - reconciliation.test.ts — stageBankStatement parses headered CSV, maps credit/debit columns, normalises DD/MM/YYYY and ISO dates + comma/₹ amounts to integer paise, and flags bad rows into problems[] (APEX-style). - idempotency — re-importing an overlapping window (same date+amount+reference+narration) INSERT OR IGNOREs on fingerprint: staged counts new only, skipped counts dupes; total row count unchanged. - suggestMatches ranking — exact outstanding==amount ranks 'exact' first; client resolved from reference token matching client.code/name yields 'client'; unrelated txn yields []. Uses a seeded client+issued unsettled invoice. - confirmBankTxn — creates payment(mode 'bank', receivedOn=txn date)+allocation via recordPayment, flips txn→confirmed with matched_client_id/payment_id, and writes the reconcile audit row in the SAME transaction; outstanding on the invoice drops correctly. - confirmBankTxn idempotency — a second confirm on the same txn throws 'Already reconciled' and creates no duplicate payment. - ignoreBankTxn — flips status→ignored, audited, and the row is excluded from unmatched summary counts. - money integrity — a credit whose amount exceeds total outstanding leaves the remainder as an advance (advancePaise on clientLedger) with no over-allocation; debit rows are classified 'debit' and never auto-confirmed. - auto-receipt (route-level, stubbed Fetcher) — with receipt.autoemail.enabled='1', POST /payments issues a RECEIPT and calls sendDocumentEmail (stub returns ok); an email failure returns {ok:true,warning} and the payment/receipt still persist (no rollback). With flag '0', no send occurs. - Postgres parity — the 010 migration creates bank_txn and the run under HQ_PG_TEST_URL passes the same suite (repos use ? placeholders + LOWER LIKE, engine-portable). --- ### mis — effort M Owner-only MIS cockpit: one screen (/reports?tab=mis chip or a new /mis route) composing existing HQ aggregates for the current month vs last — invoiced revenue, credit-note adjustments, net revenue, collections (cash received), total outstanding with aging buckets, open-pipeline value, renewals due this month (modules + AMC + recurring), open tickets by status, and reminder queue/failed sends. Implemented almost entirely by fanning out to existing repo functions plus a handful of COALESCE(SUM/COUNT) queries; no schema change, no mutations, no audit writes (read-only). New repo apps/hq/src/repos-mis.ts (misCockpit), one owner-gated route GET /reports/mis, one page MisCockpit.tsx, one api.ts client call + types, a nav entry, a main.tsx route, and vitest coverage. All amounts stay integer paise and are formatted with formatINR at the edge only. Reuses duesAging, queueCounts, ticketCounts, and the dashboard's month/renewal SQL patterns so numbers reconcile exactly with the Reports and Dashboard pages. **Shared files:** apps/hq/src/api.ts, apps/hq-web/src/api.ts, apps/hq-web/src/nav.ts, apps/hq-web/src/main.tsx **Schema:** **Repos:** - apps/hq/src/repos-mis.ts (NEW): export interface MisMetric { thisMonthPaise: number; lastMonthPaise: number; deltaPaise: number } — deltaPaise = thisMonth - lastMonth - apps/hq/src/repos-mis.ts: export interface MisView { today: string; month: string /* 'YYYY-MM' */; revenue: MisMetric; creditNotes: MisMetric; netRevenue: MisMetric; collections: MisMetric; outstanding: { totalPaise: number; b0_30: number; b31_60: number; b61_90: number; b90p: number; clientCount: number }; pipeline: { openValuePaise: number; count: number }; renewals: { moduleCount: number; amcCount: number; recurringDueCount: number; recurringDuePaise: number }; tickets: { open: number; byStatus: Record }; sends: { queued: number; failed: number } } - apps/hq/src/repos-mis.ts: export async function misCockpit(db: DB, today: string): Promise — orchestrates all fan-outs; today is 'YYYY-MM-DD' injectable for deterministic tests (mirrors dashboardView/duesAging signature) - apps/hq/src/repos-mis.ts (private): function monthBounds(today: string): { from: string; to: string } and prevMonthBounds(today): {from,to} — 'YYYY-MM-01' .. last-calendar-day, reusing the exact endOfMonth math already in repos-dashboard.ts (Date.UTC(y, m, 0)) - apps/hq/src/repos-mis.ts (private): async function sumIssuedPayable(db, docType: 'INVOICE'|'CREDIT_NOTE', from, to): SELECT COALESCE(SUM(payable_paise),0) AS t FROM document WHERE doc_type=? AND doc_no IS NOT NULL AND status<>'cancelled' AND doc_date>=? AND doc_date<=? — gross issued value per month; netRevenue = revenue − creditNotes (CGST Act s.34 sign, same convention as gstSummary) - apps/hq/src/repos-mis.ts (private): async function sumCollections(db, from, to): SELECT COALESCE(SUM(amount_paise),0) AS t FROM payment WHERE received_on>=? AND received_on<=? — cash actually received (TDS excluded; amount_paise only) - apps/hq/src/repos-mis.ts (private): async function openPipeline(db): SELECT COALESCE(SUM(d.payable_paise),0) AS t, COUNT(*) AS n FROM document d WHERE d.doc_type='QUOTATION' AND d.status IN ('draft','sent','accepted') AND d.id=(SELECT MAX(d2.id) FROM document d2 WHERE d2.client_id=d.client_id AND d2.doc_type='QUOTATION' AND d2.status<>'cancelled') — latest-live-quote-per-client, identical selection to listPipeline so the funnel value matches; a dedicated aggregate (NOT listPipeline) to avoid its 200-row page cap (rule 8: no silent caps) - apps/hq/src/repos-mis.ts (private): renewals via three COUNT/SUM queries over [today, monthEnd]: client_module WHERE active=1 AND next_renewal BETWEEN today AND monthEnd (moduleCount); amc_contract WHERE active=1 AND period_to BETWEEN today AND monthEnd (amcCount); recurring_plan WHERE active=1 AND next_run BETWEEN today AND monthEnd (recurringDueCount + SUM(amount_paise) recurringDuePaise) — same tables/filters the dashboard already queries - apps/hq/src/repos-mis.ts: outstanding built by calling existing duesAging(db, today) and reducing rows (sum b0_30/b31_60/b61_90/b90p/totalPaise, clientCount = rows.length) — guarantees the cockpit total equals the Reports dues-aging total to the paisa - apps/hq/src/repos-mis.ts: tickets from existing ticketCounts(db) (by-status map); open = sum of statuses in {open,in_progress,waiting} (closed/dropped excluded, per db.ts CHECK constraint); sends from existing queueCounts(db) (whole-desk, no ownerId) **Routes:** - GET /reports/mis — gates requireAuth, requireOwner (owner-only cockpit, matches nav ownerOnly). Handler: const today = new Date().toISOString().slice(0,10); res.json({ ok: true, view: await misCockpit(db, today) }); wrapped in the same try/catch → 400 { ok:false, error } shape used by every other /reports/* route. Placed in the existing '// ---------- reports ----------' block in apps/hq/src/api.ts, importing misCockpit from './repos-mis' **UI:** - apps/hq-web/src/pages/MisCockpit.tsx (NEW): owner-only cockpit page. PageHeader title 'MIS Cockpit' desc 'This month at a glance'. Uses useData(getMis, []) (the same hook imported from ./Clients that Reports.tsx uses). Loading → , error → . Renders a responsive grid of stat cards; money via formatINR(paise,{symbol:false}); each month-metric card shows thisMonth big, lastMonth muted, and a signed delta (green ↑ / red ↓ using deltaPaise). Cards: Revenue (net, with gross+creditNotes subline), Collections, Outstanding (total + 4 aging chips, 90+ red — reuse rowTone logic), Pipeline value (+count), Renewals due (module/AMC/recurring breakdown + recurringDuePaise), Open tickets (open + byStatus chips), Failed sends (failed red, queued muted). Cards deep-link: Outstanding→/reports (dues), Pipeline→/pipeline, Renewals→/reports, Tickets→/tickets, Sends→/reminders?status=failed - apps/hq-web/src/api.ts: add export interface MisMetric and MisView (mirror the server interfaces) and export const getMis = (): Promise => apiFetch<{ view: MisView }>('/reports/mis').then(r => r.view) — placed with the other /reports getters (~line 714) - apps/hq-web/src/nav.ts: add { to: '/mis', label: 'MIS Cockpit', icon: Gauge, ownerOnly: true } to the 'Insight' group above Reports (Gauge from lucide-react); ownerOnly hides it for non-owners exactly like Employees/Import (Layout already filters on role()==='owner') - apps/hq-web/src/main.tsx: import { MisCockpit } and add } /> inside the Layout route block **Tests:** - apps/hq/test/repos-mis.test.ts (NEW, vitest, in-memory DB like existing repo tests): seed two clients, issue INVOICEs dated in current + previous month, a CREDIT_NOTE, and payments across both months → assert revenue/creditNotes/netRevenue/collections thisMonth/lastMonth/deltaPaise are exact paise and net = gross − CN - test: outstanding block equals sum of duesAging(db, today) rows (call both, assert totalPaise + per-bucket + clientCount match) — locks the reconcile-with-Reports invariant - test: openPipeline sums only the latest live quotation per client (draft|sent|accepted), excludes cancelled/lost/invoiced and superseded older quotes; count matches - test: renewals counts client_module.next_renewal, amc_contract.period_to, recurring_plan.next_run within [today, monthEnd] only (boundary rows on monthEnd included, next-month excluded) and recurringDuePaise sums active plan amounts - test: tickets.open counts only open|in_progress|waiting (closed/dropped excluded) and byStatus mirrors ticketCounts; sends mirrors queueCounts - apps/hq/test/api-mis.test.ts (NEW): GET /reports/mis returns 200 { ok:true, view } for an owner token; 403 { ok:false } for a manager and for a staff token (requireOwner); 401 without a token - Postgres parity: run the new repo test under HQ_PG_TEST_URL — all SQL is portable (COALESCE/SUM/COUNT/BETWEEN, correlated MAX(id) subquery already used by listPipeline), no engine-specific syntax; add to migrations-pg.ts only if a new index is wanted (not required — reuses ix_recurring_active_run, ix_amc_client, existing document/payment indexes) --- ### sms-balance — effort M A "SMS balance low" report over the SMS module's client_module.field_values (key `sms_balance`, seeded by the APEX importer's D21 field spec), filtered by a threshold setting, with one-click "create top-up quote" that mints an SMS-pack PROFORMA priced through the existing D23 usage rate card, plus an optional (setting-gated) daily scheduler alert as a new internal-only reminder kind. Reuses buildLines' automatic rate-card resolution, createDraft (audited, single computeBill path), the listClientsByModule JS-side JSON-parse pattern (portable across SQLite+PG — no SQL JSON funcs), getNumberSetting/setSetting, and the upsertReminder idempotency + internal-kind send guard. sms_balance is free text (importer accepts `^\d+(\.\d+)?`), so balances are parsed in JS; unparseable values are surfaced as an "unknown" count, never silently dropped (rule 8). **Shared files:** apps/hq/src/api.ts, apps/hq/src/db.ts, apps/hq/src/migrations-pg.ts, apps/hq/src/scheduler.ts, apps/hq/src/reminder-templates.ts, apps/hq/src/send-reminder.ts, apps/hq/src/repos-reminders.ts, apps/hq-web/src/api.ts, apps/hq-web/src/nav.ts, apps/hq-web/src/main.tsx, apps/hq-web/src/pages/Settings.tsx **Schema:** - reminder.rule_kind CHECK: add 'sms_balance_low' to the IN-list in THREE places — db.ts SCHEMA constant (line ~340), the rebuildReminderRuleKindCheck DDL string (line ~514), and a NEW additive migration rebuildReminderRuleKindCheckSms(db) wired into migrate() with guard token `'quote_followup')` rewritten to `,'sms_balance_low')` (idempotent, mirrors existing rebuild) - migrations-pg.ts: extend the reminder.rule_kind CHECK IN-list (line ~160) to include 'sms_balance_low' (PG DDL is born in final form — no separate PG migration) - No new tables. Three setting rows (setting table already exists): sms.lowbalance.threshold (default 5000), sms.lowbalance.pack_qty (default 50000 = SMS rate-card minimum), sms.lowbalance.alert_enabled ('false') **Repos:** - apps/hq/src/repos-sms.ts (NEW) — parseSmsBalance(raw: string | undefined): number | null — trims, strips commas/spaces, Number(); null when not finite - apps/hq/src/repos-sms.ts — smsModuleId(db: DB): Promise — resolve the SMS module by code 'SMS' via listModules (single source; assumption documented in risks) - apps/hq/src/repos-sms.ts — export interface SmsBalanceRow { cmId; clientId; clientName; clientCode; balance: number | null; rawBalance: string; contactEmail: string | null } and SmsLowBalanceReport { rows: SmsBalanceRow[]; threshold: number; packQty: number; unknownCount: number; asOf: string } - apps/hq/src/repos-sms.ts — smsLowBalance(db: DB, opts?: { threshold?: number; onDate?: string }): Promise — reads ACTIVE client_module rows for the SMS module joined to client (active=0 excluded, stated), JSON.parse field_values in JS (portable), keeps balance<=threshold (threshold from opts override else getNumberSetting('sms.lowbalance.threshold',5000)), counts unparseable as unknownCount, sorts ascending by balance; returns ALL rows (no cap, rule 8) - apps/hq/src/repos-sms.ts — createSmsTopupQuote(db: DB, userId: string, input: { clientId: string; qty?: number; docType?: 'PROFORMA' | 'QUOTATION' }): Promise — qty default from getNumberSetting('sms.lowbalance.pack_qty',50000), docType default PROFORMA; calls createDraft with one line { moduleId: smsId, kind: 'usage', qty } and NO unitPricePaise so buildLines resolves the D23 rate card (throws on below-min); fully audited via createDraft.insertDocRow; throws if no SMS module or qty invalid **Routes:** - GET /reports/sms-low-balance — requireAuth — smsLowBalance(db, { threshold? from ?threshold query }); 404 if no SMS module - GET /reports/sms-low-balance.csv — requireAuth — streams every row (client_code,client_name,balance,threshold,contact_email), no cap (mirrors /modules/:id/clients.csv) - POST /clients/:id/sms-topup-quote — requireAuth — createSmsTopupQuote(db, staffId(res), { clientId: :id, qty?, docType? }); returns { ok, document }; 400 on below-min/no-price, 404 no SMS module - GET /settings/sms — requireAuth — { threshold, packQty, alertEnabled } via getNumberSetting/getSetting - PUT /settings/sms — requireAuth, requireOwner — setSetting for sms.lowbalance.threshold / pack_qty / alert_enabled (audited by setSetting) **UI:** - apps/hq-web/src/pages/SmsBalance.tsx (NEW) — table of low-balance clients (name, code, balance vs threshold badge, last contact email), threshold shown + owner-editable inline, 'unknown balance' count row, CSV export link, per-row 'Create top-up quote' button → POST /clients/:id/sms-topup-quote then nav('/documents/'+doc.id); empty state when none low - apps/hq-web/src/api.ts — add getSmsLowBalance(threshold?), createSmsTopupQuote(clientId, body), getSmsSettings/putSmsSettings + SmsBalanceRow/SmsLowBalanceReport types (mirrors existing report/settings helpers) - apps/hq-web/src/nav.ts — new item under 'Insight' (or a small 'Upsell' group): { to: '/sms-balance', label: 'SMS Balance', icon: } - apps/hq-web/src/main.tsx — register } /> - apps/hq-web/src/pages/Settings.tsx — new 'SMS low-balance' section: threshold, default pack qty, enable-daily-alert toggle (owner-only PUT /settings/sms) **Tests:** - parseSmsBalance: '4500'→4500, '12,000'→12000, ' 3000 '→3000, ''/'N/A'/'--'→null - smsLowBalance: keeps balance<=threshold, excludes active=0 links, sorts ascending, unknownCount tallies unparseable, respects opts.threshold override and the dated setting default - createSmsTopupQuote: mints PROFORMA with one usage line priced at the D23 tier rate for qty (assert paise via the rate card), GST split via computeBill, audit rows written; qty below rate-card minimum throws; unknown SMS module throws - scheduler sms_balance_low: enqueues once per month bucket when alert_enabled='true', nothing when 'false', idempotent on re-run (UNIQUE key), skips clients above threshold - send-reminder: refuses to email a sms_balance_low reminder (internal-only, same guard as follow_up/email_bounced) - API: GET /reports/sms-low-balance shape + CSV completeness; POST sms-topup-quote gate (requireAuth) and 404 when no SMS module; PUT /settings/sms requires owner --- ### tds — effort M Track TDS certificates per client per financial year. Expected TDS = sum(payment.tds_paise) bucketed by fyOf(received_on); received = sum(tds_certificate.amount_paise) for that client+FY; outstanding = expected - received, NOT clamped so mismatches surface (no-silent-caps rule). Adds one additive table (tds_certificate), repos-tds.ts (record/list/delete + tdsOutstanding aggregation), four routes, web api client fns, and a "TDS certificates" tab on Reports (Client / FY / Expected / Received / Outstanding). Pure integer-paise bookkeeping over data already captured on payments (payment.tds_paise) - no GST/computeBill, no external integration. **Shared files:** apps/hq/src/db.ts, apps/hq/src/migrations-pg.ts, apps/hq/src/api.ts, apps/hq-web/src/api.ts, apps/hq-web/src/pages/Reports.tsx **Schema:** - apps/hq/src/db.ts SCHEMA const: add CREATE TABLE IF NOT EXISTS tds_certificate (id TEXT PRIMARY KEY, client_id TEXT NOT NULL, fy TEXT NOT NULL, amount_paise INTEGER NOT NULL, reference TEXT NOT NULL DEFAULT '', received_on TEXT NOT NULL, notes TEXT NOT NULL DEFAULT '', created_by TEXT NOT NULL, created_at TEXT NOT NULL). A brand-new table is created by the SCHEMA CREATE-IF-NOT-EXISTS on every boot, so NO migrate() column-add is needed for SQLite. fy is the label form like '2026-27'. - apps/hq/src/db.ts INDEX_DDL block (runs after migrate()): add CREATE INDEX IF NOT EXISTS idx_tds_cert_client_fy ON tds_certificate(client_id, fy). - apps/hq/src/migrations-pg.ts: append PG_MIGRATIONS entry id '010-tds-certificate' (highest currently visible is '009-staff-username'; confirm true max and use next) with sql = CREATE TABLE IF NOT EXISTS tds_certificate (... amount_paise bigint NOT NULL ...) plus the (client_id, fy) index. pg int8/numeric aggregates already coerce to Number via setTypeParser(20/1700) in db-pg.ts, so no ::int casts needed. **Repos:** - apps/hq/src/repos-tds.ts (NEW, mirrors repos-payments.ts): interface TdsCertificate { id; clientId; fy; amountPaise; reference; receivedOn; notes; createdBy; createdAt } + TdsCertificateRow + toTds(row) mapper. - getTdsCertificate(db: DB, id: string): Promise - SELECT * FROM tds_certificate WHERE id=? - listTdsCertificates(db: DB, clientId: string): Promise - SELECT * WHERE client_id=? ORDER BY id (uuidv7 = chronological). - recordTdsCertificate(db, userId, input: { clientId; fy; amountPaise; reference?; receivedOn; notes? }): Promise - validate getClient!=null (throw 'Client not found'); amountPaise Number.isInteger && >0; fy matches /^\d{4}-\d{2}$/; receivedOn is YYYY-MM-DD. Wrap in db.transaction: INSERT with uuidv7 id + new Date().toISOString(); writeAudit(db,userId,'create','tds_certificate',id,undefined,after) in SAME txn. - deleteTdsCertificate(db, userId, id): Promise - load row (throw if missing); db.transaction: DELETE then writeAudit(...,'delete','tds_certificate',id,before,undefined). Certificates are corrigible clerical records (not issued tax documents), so delete/re-add is allowed and audited - do NOT treat them as immutable. - tdsOutstanding(db, opts?: { fy?: string }): Promise where TdsOutstandingRow = { clientId; clientName; fy; expectedPaise; receivedPaise; outstandingPaise }. Expected: SELECT client_id, received_on, tds_paise FROM payment WHERE tds_paise>0, fold in JS keying (client_id, fyOf(received_on)) -> sum tds_paise (fyOf is JS-only, cannot group in portable SQL). Received: SELECT client_id, fy, SUM(amount_paise) FROM tds_certificate GROUP BY client_id, fy. Merge both key-sets (a client may have expected-only or received-only), join client.name, outstanding = expected - received (may be negative - keep it). Optional fy filter applied to both sides. Sort by outstandingPaise desc. Reuse fyOf from @sims/domain. **Routes:** - GET /reports/tds-outstanding?fy= (requireAuth) -> { ok:true, rows } via tdsOutstanding(db, { fy }); wrap in try/catch 400 like the other /reports/* routes; read fy from req.query like rangeOf(). - GET /clients/:id/tds-certificates (requireAuth) -> { ok:true, certificates: await listTdsCertificates(db, id) }. - POST /clients/:id/tds-certificates (requireAuth, requireManagerial) -> body { fy, amountPaise, reference?, receivedOn, notes? }; certificate = await recordTdsCertificate(db, staffId(res), { clientId: req.params.id, ...body }); res.json({ ok:true, certificate }). Gate mutations to managerial (matches finance-sensitive employee/module mutations); read report stays requireAuth like existing reports. - DELETE /tds-certificates/:id (requireAuth, requireManagerial) -> await deleteTdsCertificate(db, staffId(res), req.params.id); res.json({ ok:true }). Add `import { recordTdsCertificate, listTdsCertificates, deleteTdsCertificate, tdsOutstanding } from './repos-tds'` near the other repo imports in api.ts. **UI:** - apps/hq-web/src/api.ts: add types TdsOutstandingRow and TdsCertificate; add getTdsOutstanding(fy?: string): Promise -> apiFetch('/reports/tds-outstanding'+(fy?`?fy=${fy}`:'')).then(r=>r.rows); getClientTdsCertificates(clientId), recordTdsCertificate(clientId, body) (POST), deleteTdsCertificate(id) (DELETE). Follow the existing getDuesAging/getGstSummary + apiFetch patterns. - apps/hq-web/src/pages/Reports.tsx: add a 'tds' chip { key:'tds', label:'TDS certificates' } to FilterChips and to the report union type. Add const [tdsFy, setTdsFy] = useState(currentFy label) using the existing currentFy() helper (convert its {from:'YYYY-MM'} to the '2026-27' label, or add a small fyLabel()). const tds = useData(() => getTdsOutstanding(tdsFy), [tdsFy]). Render a Field with an (or select of recent FYs) for FY, then error/undefined/empty/DataTable branches exactly like the 'gst' block. DataTable columns: Client, FY (mono), Expected (numeric), Received (numeric), Outstanding (numeric). rowTone err when outstandingPaise>0. onRowClick -> nav(/clients/:clientId). Format with inr() = formatINR(p,{symbol:false}). - OPTIONAL (not required by 'a report tab' scope, but natural reuse): a TDS panel on ClientDetail.tsx listing that client's certificates with an add form (fy, amountPaise via rupee->paise at the edge, reference, receivedOn) calling recordTdsCertificate + a delete action. Nav.ts is NOT touched (Reports tab already exists under Insight). **Tests:** - apps/hq/test/repos-tds.test.ts (vitest, in-memory openSqlite + seed a client + owner user like existing repo tests). - recordTdsCertificate: inserts row (round-trips via getTdsCertificate) and writes one audit_log row (action='create', entity='tds_certificate') in the same txn; returned amountPaise is the exact integer paise. - Validation: rejects unknown clientId ('Client not found'); rejects non-integer or <=0 amountPaise; rejects malformed fy (e.g. '2026'); rejects malformed receivedOn. - tdsOutstanding expected bucketing: two payments for one client with tds_paise>0, one received 2026-03-31 and one 2026-04-01, land in FY '2025-26' and '2026-27' respectively (locks fyOf boundary); tds_paise=0 payments contribute nothing. - tdsOutstanding math: given expected and a certificate, outstanding = expected - received; a certificate exceeding expected yields NEGATIVE outstanding (over-received) and is returned, not clamped; fy filter returns only rows for that FY; client with certificate but no TDS payments appears with expected=0. - deleteTdsCertificate removes the row and writes a 'delete' audit row; deleting a missing id throws. - Postgres parity: gate the same suite behind HQ_PG_TEST_URL if present (matches existing repo tests) so the bigint aggregate path is exercised. --- ### audit-viewer — effort M A read-only, owner-only "Audit Log" page exposing the existing append-only audit_log table: server-paginated with an honest total (rule 8), filterable by entity / action / actor / entity-id / date range, rendering a before→after key diff per row. Backend is a filtered+paginated rewrite of listAudit plus a facets helper. No schema change, no mutation, and reads write no audit row (audit-of-audit is not needed and would violate append-only intent). Filters compose server-side; ORDER BY id DESC gives newest-first (UUIDv7 ids are time-ordered). Actor names map to display names client-side via the existing getEmployees() list, with the scheduler's 'system' actor labeled specially. **Shared files:** apps/hq/src/audit.ts, apps/hq/src/api.ts, apps/hq-web/src/api.ts, apps/hq-web/src/main.tsx, apps/hq-web/src/nav.ts **Schema:** - NO schema change. audit_log already exists (apps/hq/src/db.ts ~L297: id, at_wall, user_id, action, entity, entity_id, before_json, after_json) with index ix_audit_entity ON (entity, entity_id), mirrored in migrations-pg.ts ~L117/L280. - at_wall is a full ISO-8601 UTC string (new Date().toISOString()), so lexicographic string comparison is a valid chronological range filter on BOTH engines — no date functions needed, keeps SQL portable. - OPTIONAL (not required for S/M; note only): at scale (tens of thousands of rows) filtering by user_id/action alone is a full scan. A composite index could be added later via migrate()+migrations-pg.ts, but current volume (~300 clients) does not warrant it and the task scopes to no schema change. **Repos:** - apps/hq/src/audit.ts — REPLACE existing `listAudit(db, limit=200): Promise` (Grep-confirmed: zero external callers, defined+used only in this file). New: `export interface AuditFilter { entity?: string; action?: string; userId?: string; entityId?: string; from?: string; to?: string; page?: number; pageSize?: number }` and `export interface AuditPage { rows: AuditRow[]; total: number; page: number; pageSize: number }` and `export async function listAudit(db: DB, filter: AuditFilter = {}): Promise`. - listAudit body mirrors listTickets/listDocuments exactly: `let where = ' WHERE 1=1'; const args: unknown[] = []`; append `AND entity=?`(entity) `AND action=?`(action) `AND user_id=?`(userId) `AND entity_id=?`(entityId) `AND at_wall>=?`(from) `AND at_wall<=?`(to) each guarded by `!== undefined`; date-only `to` (length===10) normalized to `to + 'T23:59:59.999Z'` for inclusive end-of-day, date-only `from` used as-is (>= midnight). `page=Math.max(1,filter.page??1); pageSize=Math.min(200,Math.max(1,filter.pageSize??50))`. total = `(await db.get<{n:number}>('SELECT COUNT(*) AS n FROM audit_log'+where, ...args))!.n`. rows = `db.all('SELECT * FROM audit_log'+where+' ORDER BY id DESC LIMIT ? OFFSET ?', ...args, pageSize, (page-1)*pageSize)`. Return { rows, total, page, pageSize }. - apps/hq/src/audit.ts — ADD `export async function auditFacets(db: DB): Promise<{ entities: string[]; actions: string[] }>` — `SELECT DISTINCT entity FROM audit_log ORDER BY entity` and `SELECT DISTINCT action FROM audit_log ORDER BY action`; feeds the filter dropdowns (config-over-code: options come from data, not a hardcoded enum). Cheap enough at current volume; portable on both engines. **Routes:** - GET /api/audit — gates `requireAuth, requireOwner`. Reads query params entity, action, userId (alias `user`), entityId, from, to, page, pageSize; passes typed AuditFilter to listAudit; responds `{ ok: true, rows, total, page, pageSize }`. No writeAudit (it's a read). Register alongside other owner-only GETs in apiRouter (apps/hq/src/api.ts), e.g. near the reports/employees block. Pattern: same try/catch → 400 on error as every other route. - GET /api/audit/facets — gates `requireAuth, requireOwner`. Calls auditFacets(db); responds `{ ok: true, entities, actions }`. Used by the page to populate the entity/action (entity from facets, action from facets, actor from getEmployees()+'system'), two date (from/to), an optional entity-id text input, spacer, {total} total. columns: When (at_wall, formatted local), Actor (mapped name), Action (), Entity, Entity ID (mono, truncated), Change (renders or an expand toggle). footer (reuse components/Pager). Empty/Error/Skeleton states via @sims/ui (EmptyState/ErrorState/Skeleton) exactly as Documents.tsx. - Self-guard for owner-only (Employees.tsx/ImportApex.tsx pattern): the nav item is hidden for non-owners and the server 403s; the page should also short-circuit with an EmptyState 'Owner only' if `role() !== 'owner'` so a hand-typed #/audit URL degrades gracefully (server would 403 the fetch regardless). - apps/hq-web/src/components/JsonDiff.tsx (NEW) — parse before_json/after_json (JSON.parse, tolerate null); compute union of top-level keys; render a compact key | before | after table, highlighting keys whose stringified values differ (added when before missing, removed when after missing). Non-object payloads (scalars/arrays) fall back to raw before/after blocks. Pure/presentational, no network. - apps/hq-web/src/main.tsx — import { Audit } and add `} />` inside the Layout route block. - apps/hq-web/src/nav.ts — add `{ to: '/audit', label: 'Audit Log', icon: ScrollText (or History), ownerOnly: true }` to the Admin NavGroup (Layout already filters ownerOnly items by role()). Import the icon from lucide-react. **Tests:** - apps/hq/test/audit-viewer.test.ts (NEW) — seed audit_log via writeAudit with varied entity/action/user_id/at_wall; assert: (1) unfiltered listAudit returns newest-first with correct total; (2) each filter (entity, action, userId, entityId) narrows rows + total; (3) date range from/to inclusive, date-only 'to' includes same-day rows (the end-of-day normalization); (4) pagination: page/pageSize slice correctly and total is the full filtered count not the page length (rule 8); (5) pageSize hard-capped at 200, floored at 1; (6) auditFacets returns sorted distinct entities/actions. - apps/hq/test/audit-routes.test.ts (NEW, mirror authz-gates.test.ts) — GET /api/audit and /api/audit/facets: 401 unauthenticated, 403 for staff and manager tokens, 200 for owner; verify query params flow through (e.g. ?entity=client returns only client rows) and response shape { ok, rows, total, page, pageSize }. - Run `npm run typecheck` and `npm test` (currently 377 green) — both must stay clean before landing. --- ### notifications — effort M A header bell that live-computes actionable alerts from existing repos (renewals due, overdue invoices, failed reminder sends, new/aging open tickets, plus a Gmail-down system alert) and shows a grouped dropdown with deep links. Decision: DO NOT persist a per-alert notification table. Every alert is already derivable from existing tables, so the feed is computed on demand; the only persisted state is one per-user "last seen" watermark stored in the existing `setting` table under key `notif_seen:` (value = ISO datetime). This yields ZERO schema deltas (SQLite dev + Postgres prod both untouched), no scheduler change, and no dedup logic. The unread badge = count of feed items whose ts > watermark; "mark all read" sets watermark to now. Owner-scoping mirrors the Dashboard/Reminders rule: money + renewals are scoped to the viewer's book via ownerScope(); tickets stay team-visible (per the repos-tickets contract that any signed-in user sees every ticket). The bell endpoint also returns queued/failed reminder counts so Layout can replace its existing two-call sidebar badge fetch with this one endpoint. Effort M. **Shared files:** apps/hq/src/api.ts, apps/hq-web/src/api.ts, apps/hq-web/src/Layout.tsx, apps/hq-web/src/app.css **Schema:** **Repos:** - apps/hq/src/repos-notifications.ts (NEW): export async function notificationFeed(db: DB, viewer: { id: string; role: string }, opts?: { limit?: number; emailDead?: boolean }): Promise — builds groups[] (per-kind honest counts, uncapped) + merged items[] (newest ts first, capped at opts.limit default 8 / cap 25) scoped by ownerScope(viewer). unreadCount = items with ts > lastSeen watermark. Reuses outstandingPaise, queueCounts, ticketCounts, ownerScope, addDaysIso, getNumberSetting. - apps/hq/src/repos-notifications.ts: source (a) invoice_overdue — same SELECT as repos-dashboard overdueRows (INVOICE, doc_no NOT NULL, status NOT IN paid/cancelled/lost, COALESCE(due_date,doc_date)<=today), owner-scoped via (d.created_by=? OR c.owner_id=?), then filtered to outstandingPaise(db,id)>0; item ts=anchor due date, severity 'urgent', href=/documents/, subtitle=formatINR(outstanding)+' · '+daysOverdue+'d'. - apps/hq/src/repos-notifications.ts: source (b) renewal_due — client_module cm JOIN client c WHERE cm.active=1 AND cm.next_renewal BETWEEN today AND addDaysIso(today, getNumberSetting(db,'reminders.renewal_days',15)), owner-scoped via c.owner_id=?; ts=next_renewal, severity 'warn', href=/clients/. - apps/hq/src/repos-notifications.ts: source (c) send_failed — reminder r LEFT JOIN document d WHERE r.status='failed', owner-scoped via (d.created_by=? OR r.doc_id IS NULL) exactly like queueCounts(); ts=COALESCE(sent_at,created_at), severity 'urgent', href=/reminders?status=failed. (Reuse queueCounts for the group count.) - apps/hq/src/repos-notifications.ts: source (d) tickets — ticket WHERE status IN ('open','in_progress') NOT owner-scoped (team-visible). Split into ticket_aging (opened_on < addDaysIso(today,-getNumberSetting(db,'notif.ticket_aging_days',3)), severity 'warn') and ticket_new (severity 'info'); ts=created_at, href=/tickets. (Reuse ticketCounts for totals.) - apps/hq/src/repos-notifications.ts: source (e) gmail_down — when opts.emailDead, append a single system item {kind:'gmail_down', severity:'urgent', ts:today, href:'/settings'}. - apps/hq/src/repos-notifications.ts: export async function getNotifSeen(db: DB, userId: string): Promise — SELECT value FROM setting WHERE key='notif_seen:'||userId (reuses the getSetting pattern from repos-reminders). - apps/hq/src/repos-notifications.ts: export async function setNotifSeen(db: DB, userId: string, ts: string): Promise — INSERT INTO setting(key,value) VALUES(?,?) ON CONFLICT(key) DO UPDATE SET value=excluded.value (same upsert as setSetting); NO writeAudit (per-user UI read-state, deliberately excluded from the append-only log like previews/share routes). Returns ts. - Types (exported from repos-notifications.ts): type NotifKind = 'invoice_overdue'|'renewal_due'|'send_failed'|'ticket_aging'|'ticket_new'|'gmail_down'; type NotifSeverity = 'info'|'warn'|'urgent'; interface NotifItem { id: string (=`${kind}:${entityId}`); kind: NotifKind; severity: NotifSeverity; title: string; subtitle: string; href: string; ts: string; unread: boolean }; interface NotifGroup { kind: NotifKind; label: string; count: number }; interface NotifFeed { today: string; unreadCount: number; total: number; lastSeenAt: string|null; groups: NotifGroup[]; items: NotifItem[]; reminders: { queued: number; failed: number } } **Routes:** - GET /api/notifications — gate requireAuth. Reads viewer=res.locals.staff; limit from ?limit (int, default 8, cap 25); emailDead=(await emailStatus(db)).dead; lastSeen=getNotifSeen(db,viewer.id). Returns { ok:true, feed } where feed=notificationFeed(...) with unread flags/unreadCount computed against lastSeen and reminders counts from queueCounts(db, ownerScope(viewer)). Purpose: single source for the header bell + sidebar reminder badge. - POST /api/notifications/seen — gate requireAuth. Body optional { ts?: string } (defaults to new Date().toISOString()); validates ISO; calls setNotifSeen(db, staffId(res), ts); returns { ok:true, lastSeenAt }. Purpose: clear the unread badge (mark all read). **UI:** - apps/hq-web/src/components/NotificationsBell.tsx (NEW): lucide `Bell` button in the header with a count badge (unreadCount, '9+' cap) and a dropdown panel. Reuses the exact dismissal pattern from Layout's hq-user-wrap (outside-mousedown + Escape + onBlur-leaves-container). Panel shows grouped sections (Overdue / Renewals / Failed sends / Tickets / System) with severity dots, each item a router link (nav(item.href)) that closes the panel; header row has a 'Mark all read' button (POST /notifications/seen then refetch); empty state 'You're all caught up.' Fetches on mount and on location.pathname change (mirrors the existing queue-badge effect); opening the panel does NOT auto-mark-read (explicit button, so counts aren't lost on a glance). - apps/hq-web/src/Layout.tsx (EDIT): render in header between the flex spacer and the Gmail pill/ModeToggle. Remove the two-call getReminders effect + queueCounts state; the sidebar Dashboard badge now reads feed.reminders (queued/failed) surfaced via NotificationsBell (lift state or a tiny shared hook useNotifications()). Keeps badgeCount/badgeTitle semantics identical. - apps/hq-web/src/api.ts (EDIT): add types NotifItem/NotifGroup/NotifFeed (mirror server) and getNotifications(limit?: number): Promise<{ feed: NotifFeed }> => apiFetch(`/notifications?limit=...`); markNotificationsSeen(): Promise<{ lastSeenAt: string }> => apiFetch('/notifications/seen', { method:'POST', body:'{}' }). - apps/hq-web/src/app.css (EDIT): styles for .hq-bell, .hq-bell-badge, .hq-notif-menu (reuse .hq-user-menu tokens), severity dot classes reusing .hq-dot ok/warn/err. **Tests:** - apps/hq/test/notifications.test.ts (NEW, vitest, seeded in-memory SQLite like existing repo tests): overdue invoice with outstanding>0 appears; fully-paid/cancelled invoice excluded (parity with dashboard). - renewal_due includes client_module within reminders.renewal_days horizon and excludes one beyond it; respects a dated override row of that setting. - send_failed counts a failed reminder and matches queueCounts.failed; owner scope: a staff viewer sees only failed sends on their own docs. - owner-scoping: staff viewer sees overdue/renewals only for clients they own, but sees ALL open tickets (team-visible) — asserts tickets are not owner-filtered. - ticket_aging vs ticket_new split honors notif.ticket_aging_days threshold. - unreadCount: with no watermark all items unread; after setNotifSeen(now) unreadCount drops to 0; a newer item (ts>watermark) becomes unread again. - total is the full uncapped group sum even when items[] is capped at limit (rule 8: no silent caps). - route: GET /notifications requireAuth returns 401 without token; POST /notifications/seen persists and GET reflects lastSeenAt. - gmail_down item present only when emailStatus.dead. --- ### einvoice — effort L GST e-invoice (IRN/QR) readiness for HQ. Design-only until a GSP account (ClearTax / Masters India) is provisioned — the entire external surface is ONE pluggable GspAdapter interface so switching provider or going live is a config/env swap with zero repo changes. Scope: (1) a PURE IRP-payload builder mapping an issued INVOICE/CREDIT_NOTE to the govt e-invoice JSON (schema 1.1) that reconciles to the paisa before submit; (2) the adapter interface + NullGspAdapter (default, 'not configured') and a ClearTax/MastersIndia skeleton reusing gmail.ts's injected `Fetcher = typeof fetch` + env-credential pattern; (3) store IRN + AckNo/Date + signed QR on the document as a METADATA stamp (NOT a content edit — same discipline as due_date being stamped at issue, so immutability holds); (4) render the signed QR as a data-URI PNG on the TAX INVOICE / CREDIT NOTE PDF via a bundled `qrcode` encoder (template CSP already permits img-src data:); (5) a config-over-code dated turnover trigger gating applicability; (6) generate + 24h-window cancel routes + a Settings section. Natural hook: AFTER issueDocument assigns the number (IRN can only be requested for a numbered, immutable invoice). Applicability = enabled-by-turnover AND docType in {INVOICE,CREDIT_NOTE} AND buyer client has a valid GSTIN (B2B); B2C invoices are exempt and never show the button. **External deps:** GSP account with a provider — ClearTax OR Masters India (their API is the bridge to the govt IRP/NIC e-invoice system). Needs: sandbox + prod API base URL, client-id/secret, GSTIN-linked API username/password. This is the user's commercial decision + signup — flagged EXTERNAL; until it exists the NullGspAdapter keeps everything buildable/testable; GST portal e-invoice enablement for the company GSTIN (NIC enables the GSTIN for e-invoicing once turnover crosses the notified threshold) — a compliance/account action, not code; Owner decision: has annual turnover crossed the e-invoice threshold, and from which FY (drives einvoice.enabled + threshold_from). Currently ₹5cr per NIC as of 2023 — but store as config, never hardcode the number; `qrcode` npm package (+@types/qrcode) — bundled dependency, NOT an external account; only note is it must be added to apps/hq/package.json and bundle cleanly in esbuild dist/server.cjs **Shared files:** apps/hq/src/db.ts (SCHEMA + migrate — new einv_* + pincode columns), apps/hq/src/migrations-pg.ts (new '010-einvoice' migration), apps/hq/src/repos-documents.ts (Doc/DocRow/toDoc extended), apps/hq/src/api.ts (4 new routes + pdf handler QR pre-render), apps/hq/src/templates.ts (QR/IRN band on INVOICE+CREDIT_NOTE), apps/hq/src/server.ts (build gspAdapter from einvoice.provider + env creds, inject into api router deps like gmail/aws), apps/hq-web/src/api.ts (Doc type + einvoice helpers), apps/hq-web/src/pages/DocumentView.tsx, apps/hq-web/src/pages/Settings.tsx, apps/hq/package.json (add `qrcode` + @types/qrcode dep) **Schema:** - document: add 11 nullable cols via additive migrate() — einv_status TEXT (NULL|pending|generated|cancelled|failed), einv_irn TEXT (64-char hash), einv_ack_no TEXT, einv_ack_date TEXT, einv_signed_qr TEXT (signed-QR JWT string that drives the PDF QR), einv_signed_invoice TEXT (large, nullable), einv_payload TEXT (exact submitted JSON snapshot for audit/reproduce), einv_error TEXT, einv_generated_at TEXT, einv_cancelled_on TEXT, einv_cancel_reason TEXT - client: add pincode TEXT (nullable) + same on stg_client — IRP recipient block requires a 6-digit PIN; free-text `address` cannot be parsed reliably. Backfilled NULL, validated only when e-invoice actually runs - SQLite db.ts migrate(): PRAGMA table_info-guarded `ALTER TABLE document ADD COLUMN einv_*` loop + client.pincode + stg_client.pincode; SCHEMA gets the same cols on the CREATE TABLE for fresh DBs - migrations-pg.ts: new PgMigration id '010-einvoice' — `ALTER TABLE document ADD COLUMN IF NOT EXISTS einv_* text` (all TEXT, matching house convention that JSON/enum stay TEXT), plus client.pincode / stg_client.pincode - Settings rows (config-over-code, dated, via setSetting/audited): einvoice.enabled ('0'|'1'), einvoice.threshold_from (FY string, informational — the turnover-crossed FY), einvoice.provider ('none'|'cleartax'|'mastersindia'), einvoice.sandbox ('0'|'1'). GSP CREDENTIALS are NOT in the DB — they live in env (external), like GOOGLE_*/AWS_* - No new tax/rounding rows — GST still flows only through computeBill; e-invoice reads the already-stored *_paise totals and must not recompute them **Repos:** - apps/hq/src/gsp-adapter.ts (NEW): `export interface GspAdapter { readonly provider: string; generateIrn(payload: EinvPayload): Promise; cancelIrn(irn: string, reason: string, remark: string): Promise }` — GspIrnResult = { irn; ackNo; ackDate; signedQr; signedInvoice? } | { error: string }; GspCancelResult = { ok: true } | { error }. Plus `export const nullGspAdapter: GspAdapter` (every call returns { error: 'e-invoice GSP not configured' }). Plus `export function makeClearTaxAdapter(deps: { f: Fetcher; gspBaseUrl: string; clientId: string; clientSecret: string; gstin: string; username: string; password: string; keyHex: string }): GspAdapter` — skeleton that reuses `Fetcher = typeof fetch`, does the auth-token dance + POST /einvoice/generate, maps provider JSON→GspIrnResult; MastersIndia variant same shape. Provider chosen by einvoice.provider setting - apps/hq/src/einv-payload.ts (NEW): `export interface EinvPayload {...}` mirroring govt schema 1.1 (Version, TranDtls, DocDtls{Typ:'INV'|'CRN', No, Dt}, SellerDtls, BuyerDtls, ItemList[], ValDtls). `export function buildEinvPayload(doc: Doc, client: Client, company: Record): EinvPayload` — PURE, no DB. Maps each BillLine → ItemList (HSN/SAC=l.hsn, Qty, Unit UQC 'NOS'→'OTH' map table, UnitPrice/AssAmt/GstRt/IgstAmt|Cgst+Sgst from paise via /100 rupee-decimal, TotItemVal). ValDtls summed from doc.taxable/cgst/sgst/igst/roundOff/payable. Throws if buyer GSTIN missing/invalid or pincode absent. `export function assertReconciles(p: EinvPayload, doc: Doc)` — sums ItemList TotItemVal & ValDtls back to paise and rejects a >₹1 drift BEFORE submit (IRP rejects mismatches; fail loud locally first) - apps/hq/src/repos-einvoice.ts (NEW): `export async function generateEinvoice(db: DB, adapter: GspAdapter, userId: string, docId: string): Promise` — loads doc+client+company; guards (docType INVOICE|CREDIT_NOTE, docNo!=null, status!=cancelled, einv_status not already 'generated', buyer GSTIN valid, config enabled); builds+reconciles payload; awaits adapter.generateIrn OUTSIDE the txn (network); then `db.transaction`: UPDATE document SET einv_status='generated', einv_irn/ack/qr/payload/generated_at, addEvent(doc,'einvoice_generated',{irn,ackNo}), writeAudit(update, before/after einv_status) in the same txn; on adapter error store einv_status='failed'+einv_error+audit. `export async function cancelEinvoice(db, adapter, userId, docId, reason, remark): Promise` — guards einv_status='generated' AND einv_generated_at within 24h (IRP window; else throw with the portal-manual message), awaits adapter.cancelIrn, then txn: einv_status='cancelled', einv_cancelled_on, einv_cancel_reason, event+audit. `export async function getEinvoiceConfig(db): Promise<{enabled;provider;sandbox;thresholdFrom;irnCount}>`; `export async function setEinvoiceConfig(db, userId, patch)` (audited setSetting loop). `export async function isEinvoiceApplicable(db, doc, client): Promise` - apps/hq/src/repos-documents.ts (EDIT): extend Doc + DocRow interfaces with the 11 einv_* fields; map them in toDoc() (camelCase: einvStatus, einvIrn, einvAckNo, einvAckDate, einvSignedQr, einvSignedInvoice?, einvError?, einvGeneratedAt?, einvCancelledOn?, einvCancelReason?). No change to insert/issue/computeBill paths — new docs simply have einv_status NULL - apps/hq/src/qr.ts (NEW): `export async function qrPngDataUri(text: string): Promise` — thin wrapper over the `qrcode` npm package (toDataURL, errorCorrectionLevel 'M', margin 1). Async; templates.documentHtml stays sync so the CALLER (pdf route / repos) pre-renders the QR data-URI and passes it in (see templates edit) **Routes:** - POST /documents/:id/einvoice — requireAuth + requireManagerial; body {} ; calls generateEinvoice(db, gspAdapter, staffId, id); reuses the existing docAction() 404-guard wrapper; 400 on any guard failure (not applicable / already generated / GSP not configured / reconcile drift). Frontend hits it through the existing documentAction(id,'einvoice') helper - POST /documents/:id/einvoice/cancel — requireAuth + requireManagerial; body { reason: string (IRP code 1|2|3|4), remark: string }; calls cancelEinvoice; 400 outside the 24h window with a 'cancel on the GST portal instead' message - GET /settings/einvoice — requireAuth + requireOwner; returns getEinvoiceConfig(db) — { enabled, provider, sandbox, thresholdFrom, irnCount, gspConfigured } where gspConfigured reflects whether the env creds are present (server passes a boolean; route never reads env directly) - PUT /settings/einvoice — requireAuth + requireOwner; body { enabled?, provider?, sandbox?, thresholdFrom? }; validates provider in {none,cleartax,mastersindia}, thresholdFrom matches /^\d{4}-\d{2}$/ FY; audited setSetting loop in one txn - GET /documents/:id already returns the full Doc — now carries einv_* so DocumentView renders IRN/QR/status with no new fetch. GET /documents/:id/pdf unchanged route, but the handler now pre-renders qrPngDataUri(doc.einvSignedQr) and passes it into documentHtml when einv_status='generated' **UI:** - apps/hq-web/src/pages/DocumentView.tsx: add an 'e-invoice' action group in the Toolbar, shown only when doc.docType in {INVOICE,CREDIT_NOTE} && issued && !cancelled && applicable. Buttons: 'Generate IRN' (act('einvoice',{},{ok:'IRN generated'})) when einvStatus NULL|failed; 'Cancel IRN' (ConfirmDialog → reason select + remark → POST einvoice/cancel) when einvStatus='generated' && within 24h. Add an e-invoice panel below the RecordHeader showing IRN (mono, wrap), Ack No/Date, a Badge for einvStatus, and einvError as a Notice tone='err' on failure. QR itself prints on the PDF (already previewed in the iframe) - apps/hq-web/src/api.ts: extend the Doc type with the einv* fields; add cancelEinvoice(id, reason, remark) POST helper (generate reuses documentAction). Add getEinvoiceConfig()/putEinvoiceConfig() for Settings - apps/hq-web/src/pages/Settings.tsx: new owner-only 'GST e-invoice' section — Enable toggle, Provider select (None/ClearTax/Masters India), Sandbox toggle, Threshold-crossed FY input, a read-only 'GSP credentials: configured/ missing (set via env)' line, and an IRN-generated count. Mirrors the existing Sharing/Reminders settings blocks - apps/hq/src/templates.ts: documentHtml() gains an optional `einvQrDataUri?: string` param (via an opts arg or a field on a passed view-model); when present and doc.docType in {INVOICE,CREDIT_NOTE}, render an e-invoice band under the letterhead: `` + IRN + Ack No/Date. Purely additive CSS; RECEIPT/QUOTATION/PROFORMA untouched. Keep esc() on all text; the QR is a trusted data-URI we generated - No new nav entry / page — e-invoice lives inside the existing DocumentView + Settings surfaces **Tests:** - einv-payload.test.ts (PURE, no GSP): buildEinvPayload maps a 2-line intra-state INVOICE → correct DocDtls.Typ='INV', BuyerDtls.Gstin/Pin, ItemList HSN+Qty+CgstAmt+SgstAmt in rupee-decimal, ValDtls totals; a CREDIT_NOTE → Typ='CRN'; inter-state doc → IgstAmt set, Cgst/Sgst zero - einv-payload reconcile: assertReconciles passes on a real computed doc; a hand-corrupted ValDtls (>₹1 drift) throws BEFORE any submit - buildEinvPayload throws when buyer GSTIN missing (B2C), invalid GSTIN, or client.pincode NULL — the loud-fail-before-portal rule - repos-einvoice with a FAKE GspAdapter (returns fixed irn/ackNo/signedQr): generateEinvoice stamps einv_status='generated'+irn+qr, writes one document_event + one audit row in the same txn, and is idempotent (second call throws 'already generated'); adapter error path stores 'failed'+einv_error+audit and leaves totals untouched - generate guards: non-issued draft rejected; QUOTATION/RECEIPT rejected; cancelled doc rejected; disabled config rejected - cancelEinvoice: within 24h flips to 'cancelled' with reason/remark+audit; a doc generated >24h ago throws the portal-manual message (freeze clock via injected/stubbed now) - nullGspAdapter returns the 'not configured' error and generateEinvoice surfaces it as a 400 (design-only default has no live GSP) - QR render: qrPngDataUri returns a data:image/png;base64 URI; documentHtml with einvQrDataUri emits the + IRN band for INVOICE and omits it for RECEIPT — snapshot/contains assertions - migration: an old DB (document without einv_* / client without pincode) opens, migrate() adds the columns, an existing invoice reads back with einvStatus NULL ---