sims-hq/STATUS.md

SiMS HQ — Build Status

Internal ops console for running our software business. Not shipped to clients. Last reviewed: 2026-07-20 · Version 0.1.0

HQ is the back-office console we use to run ~300 Classic client relationships: the client book, our software module catalogue and dated price book, quotations / proforma / invoices / credit notes with editable letterhead templates and PDF output, shareable public document links, call & interaction tracking, AMC contracts, AWS cost recovery, payments & allocations, recurring billing, and Gmail reminders with bounce handling — plus reports, a money dashboard, an append-only audit trail, an APEX cutover importer, and a background scheduler. The 2026-07-17 quote-to-close slice (D16) added employee management with owner / manager / staff roles, account-owner routing on clients, a cross-client pipeline chase-list with derived stages, and escalating quote follow-up reminders driven by the dated reminder_schedule table. The same-day D18 go-live cluster added invoice due dates, the Documents and Reminders pages, self-service profiles, My Day, client support-access data, the APEX import web UI, the editable module roster with one-click Convert & send, and the section-per-module quotation PDF — then closed all 8 findings from its review.

Since 2026-07-17 (D20–D33). Support tickets (aging/SLA/assignee workbench, bill-from-ticket), onboarding milestones, client branches, and module-defined operational fields each module declares in its field_spec (config over code) — shown per client on Client 360, across all clients in the Module data directory, and as a Portals quick-list of every stored login. For SMS those fields include a live credit balance pulled from the provider gateway (sms.balance_api_url, keyless): a manual "Refresh from gateway" and a once-a-day scheduler pass, with low balances flagged on the SMS-credits screen, the owner cockpit tile and the dashboard for a one-click top-up quote. Also added: a Renewals command center, an owner MIS cockpit, an audit-log viewer, a global search (Ctrl-K now spans clients + documents + tickets), a Settings → Operations panel (SMS gateway URL, low-balance threshold, ticket SLA), Postgres as a first-class engine (DATABASE_URL; D19), and a database backup script (npm run backup; D33). D31/D32: credentials are stored in the clear — HQ_SECRET_KEY was removed, so portal/gateway passwords, module secrets, client DB passwords and the Gmail token are plaintext and keyless (login passwords stay scrypt-hashed); a DB dump exposes them, so the DB file and its backups are the security boundary.

Since 2026-07-19 (D34–D37). Employee master imported (11 staff; username = first name, default password <name>@123) with a must_change_password "Invited" flag that clears on the first self password-change (D34). Clients gained a type (PACS vs Store) and a society category matching the Kerala co-op directory taxonomy (Service Bank / Urban / Vanitha / Housing / Employees / Agricultural / …), both derived from the name and filterable on the Clients screen (D35/D37). A founder-approved data-correction session cleaned 187 client names and appended LTD/registration numbers to 141 PACS, sourced from the Kerala govt co-op directory via a multi-agent web-lookup (D35). Login history (D36): every sign-in attempt (success/failed/locked) is logged with IP + device in a login_event table, viewable on a new owner Login history screen; an invited user is sent to Profile to change their default password on first sign-in.

Verification state

Check	Result
npm install	clean
npm run typecheck (root + workspaces)	clean, no errors
npm test (vitest run)	416 tests pass across 85 files (5 Postgres-gated skipped)

The 416 figure is the live vitest run count measured 2026-07-19; the suite has grown from the D18 go-live cluster through the D20–D33 work (tickets, module fields, renewals, cockpit, audit viewer, SMS gateway, module directory/portals/search, and the D31/D32 plaintext-credential change with its own migration + review). Test files live under apps/hq/test/, apps/hq-web/test/, and packages/{domain,auth,billing-engine,ui}/test/.

Architecture

• Backend — apps/hq/src, Express over better-sqlite3. Every table sits behind plain-function "portable repositories" (the D12 pattern: functions take the DB handle, no ORM). SQLite-only SQL (ON CONFLICT, INSERT OR IGNORE) is flagged in comments for a later Postgres port. DB file: data/hq.db (WAL mode); HQ_DATA_DIR overrides the location, :memory: is used by tests.
• Frontend — apps/hq-web, React 19 + Vite 6, react-router-dom v7 hash router. Talks to the server over /api with a bearer token kept in localStorage. In production the server serves the built SPA from apps/hq-web/dist; in dev Vite runs separately.
• Shared packages (trimmed forks under packages/): @sims/domain (ids, money, business-day, doc-series, GSTIN, documents), @sims/auth (scrypt password hashing), @sims/billing-engine (GST compute + tax resolution), @sims/ui (React component kit + theme).
• Build/run:
  • Root: npm install, npm run typecheck, npm test.
  • Server: apps/hq — npm run build (esbuild → dist/server.cjs), npm start (build + run). Listens on HQ_PORT (default 5182); the scheduler starts with the server.
  • Web: apps/hq-web — npm run dev / npm run build.
  • CLI: npm run import (APEX importer), scripts/gmail-connect.ts (one-time Gmail OAuth).
  • Env: HQ_PORT, HQ_DATA_DIR, DATABASE_URL (Postgres engine; D19), SIMS_PIN_PEPPER, GOOGLE_CLIENT_ID / GOOGLE_CLIENT_SECRET, AWS_ACCESS_KEY_ID / AWS_SECRET_ACCESS_KEY. HQ_SECRET_KEY removed (D32) — credentials are stored in the clear, no key needed.
  • Backups: npm run backup (D33) — pg_dump to ./backups, restorable with pg_restore.

Database schema (apps/hq/src/db.ts)

The schema creates 32 tables on open; migrate() additively backfills the post-launch columns on older DBs (module.quote_content, email_log.bounced, document.due_date, staff_user.phone/title, client.owner_id plus the D18 support fields anydesk/os/district/sector/db_password_enc, and the same four support fields on stg_client so the APEX cutover carries them) and widens two CHECK constraints via a guarded, transactional table rebuild (rebuildTable: staff_user.role gains manager, reminder.rule_kind gains quote_followup) — a no-op on fresh DBs and on re-runs.

Table	Purpose
staff_user	Console logins / employees — owner · manager · staff role, scrypt salt+hash, active flag
session	Bearer session tokens with expiry
login_event	Login attempt log — staff_id/username, outcome (success/failed/locked), IP, user-agent (D36)
client	Client registry — code, name, GSTIN, state, contacts (JSON), status, account owner (owner_id), client_type (PACS/Store, D35), category (society type, D37), source
module	Software module catalogue — SAC, allowed billing kinds, multi-sub flag, quote-content lines
module_price_book	Dated prices per module/edition/kind (latest effective row wins)
client_module	A module assigned to a client — lifecycle status, install/complete/train dates, next renewal, provider/username/password (plaintext, D32), module-defined field_values (D21)
usage_rate_band	Dated per-unit usage pricing with a minimum order (e.g. SMS ≥ 50,000 units)
client_branch	Client branches/offices (APEX parity)
project_milestone	Onboarding checklist items per client_module
ticket	Support workbench tickets — status, assignee, opened/closed dates, source (apex/hq)
sms_balance_check	SMS gateway poll telemetry (status/balance/message/checked_at) — operational, not audited (D28)
tax_class	GST rate classes (dated); GST18 seeded at 18%
doc_series	Per (doc_type, FY) running number + prefix
document	QT / PROFORMA / INVOICE / RECEIPT / CREDIT_NOTE — totals, status, ref chain, JSON payload
document_event	Per-document timeline (created / issued / sent / converted / cancelled / credit_note …)
document_share	Opaque public share tokens — expiry, revoked flag
payment	Money received — mode, reference, amount, TDS
payment_allocation	How a payment is applied across invoices
email_account	The single Gmail sending identity — refresh token (plaintext since D32), active/dead status
email_log	Append-only send trace — sent/failed, gmail message id, error, bounced flag
setting	Key/value store — company.* identity, template.* letterhead text, reminder + AWS config
audit_log	Append-only before/after JSON for every mutation
stg_client	APEX import staging for clients (per-row problem list)
stg_invoice	APEX import staging for invoices (per-row problem list)
recurring_plan	Recurring billing — cadence, amount-or-price-book, next run, auto/manual policy
amc_contract	AMC contracts — coverage, period, amount, reminder days, linked invoice
interaction_type	Interaction type lookup (call, site visit, training, …)
interaction	Client interaction log — outcome, notes, follow-up date
reminder	Reminder rows — rule kind (now incl. quote_followup), subject, due period, status; UNIQUE(rule_kind, subject_id, due_period) idempotency key
reminder_schedule	Dated reminder cadence + follow-up message text per rule kind — effective_from/_to window, CSV day offsets (a cadence change is a new dated row)
aws_usage	Per-client per-month AWS storage/transfer/cost; UNIQUE(client_id, month) upsert key

Backend capabilities

Everything below is implemented and exercised by tests via the Express router (apps/hq/src/api.ts) or the scheduler.

• Auth & roles — email + password login (scrypt via @sims/auth), 14-day bearer sessions, requireAuth / requireOwner middleware, and owner / manager / staff roles with a shared server-side ownerScope gate: staff are forced to their own rows (any widening param is ignored), owner/manager see everything and may narrow via ?owner=. verifySession requires the account to still be active, so deactivation kills unexpired tokens immediately. First boot seeds an owner and prints a one-time password to stdout.
• Employee management — staff CRUD over the existing staff_user table (D16) via repos-employees.ts and the /employees routes: GET /employees for any signed-in user (name/owner pickers; returned whole with a total), owner-only create / rename / re-role / password-reset / deactivate / reactivate. Guards: the last active owner can be neither demoted nor deactivated, you cannot deactivate yourself, duplicate emails and empty patches are rejected cleanly; deactivation and password reset purge the user's sessions in the same transaction. Password hashes never leave the repo module and are never audited.
• Clients — create / list / search (name·code·GSTIN) / get / patch, GSTIN checksum validation, auto-generated client codes, contacts as JSON, lead → active → dormant → lost status, and an account owner (PATCH /clients/:id/owner, owner/manager only, audited as set_owner) so bare leads are routable to an employee.
• Modules & price book — owner-managed catalogue, dated price book (priceOn = latest effective row ≤ date), allowed billing kinds (one_time / monthly / yearly / usage), multi-subscription rule, per-module "what's included" quote-content lines.
• Client modules — assign modules to clients, full delivery lifecycle (quoted → ordered → installing → installed → trained → live → expired / cancelled), install/complete/train dates, next-renewal date, single-active guard for non-multi-sub modules.
• Documents — draft compose (prepareDraft is a pure compute half shared by save and the live preview, so they can't drift), issue (assigns the series number), legal status marks (sent / accepted / lost), the QT → PI → INV conversion chain via ref_doc_id, cancel (number stays consumed; blocked once payments are allocated), full/partial credit notes recomputed at the original invoice date, and a permissive preview endpoint. Issued documents are never edited — corrections are credit notes. Conversion is hardened (funnel spec F3/F4): a document with a live (non-cancelled) forward child refuses a second convert — one sale, one invoice — and PROFORMA → INVOICE rebuilds the carried lines through computeBill on the invoice's own date, so a dated tax change between proforma and invoice lands at the rate that is law on issue day. Accepting, losing, converting, or cancelling a quotation dismisses its open follow-up nudges in the same transaction (one audited row each).
• GST / billing — @sims/billing-engine computeBill with intra-state CGST/SGST vs inter-state IGST split by place of supply, round-to-rupee, SAC labelling. Place-of-supply is fail-loud: a missing company.state_code throws rather than silently defaulting.
• PDF rendering — puppeteer HTML → A4 PDF (lazy singleton browser), self-contained inline-CSS letterhead (templates.ts: documentHtml, receiptHtml, documentHtmlSample). Letterhead identity/text, doc titles, logo (data-URI) and accent colour all read live from settings; the accent is validated before interpolation.
• Public share links — owner mints a 256-bit opaque token (default +30d expiry, or never), lists and revokes. One unauthenticated route GET /share/:token mounted outside /api, per-IP rate-limited (30/60s), renders the one document inline as PDF; unknown/expired/revoked tokens return a data-free "link unavailable" page. Tokens are never logged or audited.
• Email (Gmail) — gmail-connect loopback OAuth (send + readonly scopes) stores the refresh token (plaintext since D32). Sends go over the Gmail REST API (no SDK): access-token exchange, raw MIME build with PDF attachment, send. invalid_grant flips the account to dead and raises the dashboard banner. Document emails and templated reminder emails share the send path; every attempt is logged.
• Bounce handling — polls the mailbox for mailer-daemon / postmaster DSNs since the last poll, matches failed recipients against email_log, flips them bounced, and raises an email_bounced reminder — idempotently.
• Payments & allocations — record payments (bank / upi / cheque / cash / other) with TDS, allocate oldest-invoice-first or explicitly (never exceeding outstanding or settling power), TDS pooled with cash as settling power, advance-on-account computed on read, settlement status auto-flip (part_paid / paid), credit-note-aware outstanding, RECEIPT generation, and a pro-rata per-module billed-vs-settled view.
• Recurring billing — monthly/yearly plans priced by amount or price book, auto/manual policy. Generation in the daily scan is transactional (claim + issue invoice + advance next_run, all-or-nothing) and at-most-once per period; auto plans send strictly after commit.
• AMC contracts — create / patch / deactivate, paid state derived from the linked invoice's settlement (or a legacy_paid manual flag for imports), and one-click renewal-invoice generation against the seeded AMC module.
• Interactions — seven seeded interaction types, logging with outcome and an optional follow-up date that feeds the daily scan.
• Pipeline chase-list — GET /pipeline (repos-pipeline.ts): one cross-client ranked list with derived stages (Enquiry / New Project / Quoted-Waiting / Won / Lost — never stored; computed from client.status + the client's latest live quotation, so the slice added zero pipeline storage beyond client.owner_id). Age anchors on the first sent document event; the Waiting → Chase → Nudge → Final-nudge ladder and the green/amber/red bands read the same dated reminder_schedule offsets the reminder engine uses. Role-gated through ownerScope (staff see only their rows — quote owner = created_by, lead owner = client.owner_id), oldest-overdue sorts first, all | mine | overdue | lost filters (Lost hidden unless asked for), paginated with an honest total.
• Reminders & scheduler — a deterministic daily scan (clock injected) detects invoice_overdue (monthly bucket), renewal_due, amc_expiring, follow_up, and quote_followup — an escalating chase on sent quotations anchored on the first sent event, at day milestones (default 3/7/14) resolved from the dated reminder_schedule table (resolveSchedule; message text is dated too, with code-constant fallbacks so a missing row never silences the engine). The queued row is the owning employee's nudge (owner derived doc_id → document.created_by); sending it emails the client a public share link — previews resolve an existing live link read-only and write nothing, while the real send path is the only place a share is minted (60-day expiry, live links reused; an unset share.base_url refuses loudly). Catch-up after downtime enqueues only the highest crossed milestone, and every reminder goes through INSERT OR IGNORE on its idempotency key. Send policy is the quote.followup.policy setting (default manual); auto sends drain strictly after the scan. The manual queue (GET /reminders) is now paginated with an honest total and owner-scoped (doc-less rows are shared work, visible to all), with ?status= / ?owner= narrowing; send / dismiss / preview and the templated emails in reminder-templates.ts work as before. The scheduler ticks on boot and every 6h (unref'd), also running the bounce poll and the monthly AWS cost pull.
• AWS cost recovery — per-client per-month usage & cost, entered manually by the owner or pulled from Cost Explorer. The pull is SigV4-signed with node:crypto (no SDK), grouped by the client cost-allocation tag, assumes INR, upserts one row per (client, month), and is gated to once per calendar month. Cross-client cost ranking and per-client cost aggregation are exposed.
• Reports — dues aging (0–30 / 31–60 / 61–90 / 90+ buckets), module revenue (billed vs settled), client profitability (billed / settled / AWS cost / margin), and the AWS cost ranking.
• Dashboard view — "today's money": overdue invoices, recurring due this week, renewals this month, follow-ups today, recent payments, the live reminder queue (owner-scoped like GET /reminders, first page + honest total), and headline totals.
• Settings / letterhead — owner-editable company profile (GSTIN & state-code validated) and template text (terms, declaration, jurisdiction, footer, signatory label, per-type titles), logo upload (base64 PNG/JPEG/GIF/WebP/SVG, ≤200 KB), and a live sample preview through the one real renderer.
• Audit trail — every mutation writes a before/after row; ids are monotonically-bumped UUIDv7 so same-millisecond writes stay strictly ordered.
• APEX importer — stage clients.csv + invoices.csv (each row carries its own problem list), a verification report, and a commit that refuses to run while any staged row has problems; commit marks rows source='apex' and seeds the INVOICE series past the legacy numbers. CLI: npm run import -- --dir <folder> [--commit] (dry-run without --commit).

Frontend screens (apps/hq-web)

Shell (Layout.tsx): grouped sidebar (WORK / CATALOG / INSIGHT / ADMIN) with lucide icons and a reminder-queue count badge — collapsible to an icon rail via the footer ‹/› toggle (remembered in localStorage) — top bar with Ctrl+K command palette, Gmail status pill, theme/accent switcher and a user menu (Profile / Logout), plus the Gmail-disconnected banner. Owner-only items (Employees, APEX Import) appear in ADMIN; the warm ops-console restyle is the 2026-07-17 redesign spec. Routes (main.tsx):

Route	Screen	What it does
/login	Login	Split-panel sign-in — dark warm brand panel + form card (brand panel hides under 800px)
/	Dashboard	Money headline cards, the reminder queue with send / preview / dismiss, and overdue / due-this-week / renewals / follow-ups / recent-payments tables
/pipeline	Pipeline	Cross-client chase-list: derived stage, amount, owner, age and colour band per row with an explicit next action (incl. one-click confirmed Convert & send); All / Mine / Overdue / Lost chips, owner narrowing (owner/manager), pagination
/reminders	Reminders	Standalone reminder queue: status chips (Queued / Sent / Failed / Dismissed), Mine/All (managerial), send / preview / dismiss, pagination
/clients	Clients	Search + district/sector filters, status chips, inline new-client create; row → client 360°
/clients/:id	ClientDetail	Client 360°: avatar record header + status, account-owner dropdown (owner/manager), KPI row, a 12-month relationship-pulse ribbon (documents / payments / interactions / AMC on one axis; hover readout, click-through), and deep-linkable tabs (?tab=): Overview · Modules (assignment & lifecycle) · Documents · Payments & plans (record payment, advance, recurring — owner) · AMC (owner) · Interactions (log + follow-ups) · AWS usage
/modules	Modules	Module catalogue + dated price book; owner edits (create module, quote content with live sample preview, add prices), staff read-only
/reports	Reports	Dues aging, module revenue, client profitability, and an AWS cost bar chart by month
/documents	Documents	Register of every document, newest first: type chips + status filter, honest server pagination with true totals, one-click confirmed Convert & send on issued proformas
/documents/new	NewDocument	Quotation-in-minutes composer: client type-ahead, line editor, server-computed GST, due-date field on invoices (auto-filled from terms, editable), and a live PDF-fidelity preview (desktop split view / mobile sheet)
/documents/:id	DocumentView	Document page: PDF preview, state-driven action bar (issue / send / mark / convert / cancel / credit note / record payment), share & download group (copy link / WhatsApp / native share / revoke), event timeline and email log
/tickets	Tickets	Support workbench: status chips + counts, per-row assignee, Age column with SLA overdue flag + Overdue chip, search, one-click Bill (New Document pre-filled)
/projects	Projects	Onboarding: per-client-module milestone checklist board
/renewals	Renewals	Module subscriptions + AMC coming due, earliest first, one-click renewal/top-up proforma
/sms-balances	SmsBalances	SMS credit balances (live from the gateway), lowest first; Low/All/Not-recorded chips, per-row top-up quote, owner Refresh from gateway
/module-data	ModuleDirectory	Pick a module → every client on it with that module's fields as columns, searchable, portal URLs clickable
/portals	Portals	Every stored login across all modules — Open ↗ links, 🔑 = password on file (reveal on Client 360)
/mis	Mis	Owner cockpit: this-month billing / collections / outstanding / pipeline / renewals / tickets / SMS-low, each tile click-through
/audit	Audit	Owner audit-log viewer: entity/action filters (from server facets), paginated before/after key-diff
/login-history	LoginHistory	Owner login history: every sign-in (success/failed/locked) with IP + parsed device, outcome filter, paginated (D36)
/settings	Settings	Settings hub (Pinterest-style left rail, ?s= deep-links): Company profile · Document template (live preview) · Reminders (dated cadence rows, append-only) · Sharing default expiry · Integrations status · Appearance. Owner-only except Appearance; /settings/template redirects here
/employees	Employees	Owner-only staff management: add / edit / re-role / reset password / deactivate / reactivate, surfacing the last-owner and self-deactivation guards
/profile	Profile	Self-service: edit display name/phone (email + role read-only), change own password (current password required; other sessions purged)
/import	ImportApex	Owner-only APEX cutover: CSV pickers → staged rows with per-row problem chips → verification summary → commit locked until zero problems

The composer, quote-content, and template previews all use one LivePreview component that renders the server's real documentHtml in a sandboxed double-buffered iframe — the on-screen paper is the same HTML puppeteer rasterizes for the PDF.

Known gaps / not yet wired

• Gmail is not connected in production yet — until gmail-connect is run, document sends and auto-reminders queue to the manual dashboard queue and the disconnected banner shows.
• AWS cost pull is env-gated — the scheduled and manual pulls run only when AWS_ACCESS_KEY_ID / AWS_SECRET_ACCESS_KEY are configured; otherwise usage is owner-entered by hand.
• A few tax/identity defaults are provisional — the seeded company state code (32, Kerala) and several SAC codes carry "founder / CA to confirm" notes.

Closed by the D18 go-live cluster (2026-07-17): APEX import now has an owner-only web UI (/import: stage → per-row problems → verify → confirm-gated commit); standalone Reminders (/reminders) and Documents (/documents, honestly paginated) pages exist; invoices carry due dates (terms default + override, stamped at issue — the overdue chase says "was due on X, N days overdue"); self-service profile + own-password change; sidebar collapses; the dashboard has a My Day mode (staff always scoped to their book); clients carry support-access data (AnyDesk, OS, district/sector filters, a DB password — plaintext since D32 — with audited reveal); the module roster is fully editable both ways (one audited write path with Client 360); "Convert & send" is one click from the register and the document view; and a multi-module quotation prints one titled section per module.