`npm run backup` ran scripts/backup.mjs, which read process.env.DATABASE_URL
directly and exited when it was unset. On the production box DATABASE_URL is NOT
exported -- the connection is resolved (D19) -- so the backup command could never
reach the real data.
The dump now lives in apps/hq (src/backup.ts + a thin scripts/backup.ts CLI, the
same split the other maintenance scripts use, so it is typechecked and unit
tested) and resolves its target through the shared resolveDbUrl. It logs the
resolved host/db -- never the password -- before pg_dump runs, and a SQLite
outcome is a loud error naming backup-hq.sh instead of a dump of nothing.
Behaviour otherwise unchanged: -Fc dump under ./backups (or HQ_BACKUP_DIR),
rotation to the newest HQ_BACKUP_KEEP (default 14), same pg_dump discovery.
The .mjs entry could not import the TS resolver, and duplicating the resolution
would have re-introduced the copy of the production connection string that
6f50799 deliberately removed -- hence the move to TS. `npm run backup` is
unchanged for the operator; prod already runs `npx tsx apps/hq/scripts/...` for
maintenance (see DEPLOY-HQ.md), so tsx is on the box.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Two bugs in the same tick path.
1. installed_on / trained_on were written only as a side effect of a status
UPGRADE. The ~300 APEX-imported projects are already at 'live' -- the furthest
status -- so ticking Installation or Training upgraded nothing and therefore
recorded nothing, and the UI's own date writers were removed in the Client
Detail redesign, leaving those columns unfillable and the Module Directory's
"Installed" column blank forever. The date is now stamped whenever the step is
ticked, independent of any status move: same transaction, same audit payload,
still write-once (an existing date is never overwritten) and still no downgrade.
A tick that changes neither status nor a date now writes nothing at all.
2. setMilestone wrote payment_id/document_id as `done ? linked : null`, so any tick
that did not resend the ids cleared them. bulkSetMilestone routes through it and
never passes ids (one key, many projects), so a single bulk re-tick silently cut
every project's step loose from the payment or quotation it had been linked to.
An already-done step now keeps the link it carries when nothing is supplied.
An explicit empty id is still an unlink, and unticking still clears both.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
apply-onboarding-pipeline.ts documented its own command with no `DATABASE_URL=`
prefix -- the only hardened data script missing it. Followed literally on the
production host it resolves to SQLite, not the real database. Header corrected to
match its siblings.
The deeper problem is that landing on SQLite was a DEFAULT, not a decision: with no
DATABASE_URL, resolution quietly returned '' and the run was only saved by the
"never CREATE a database" guard -- which passes as soon as any stale ./data/hq.db
exists under the operator shell's cwd. requireDbUrlForEnv now refuses a SQLite
outcome unless it was explicitly asked for (HQ_DB_TARGET=sqlite, a --sqlite flag,
or HQ_DATA_DIR=:memory:), and the error names both escape hatches. The create guard
is unchanged and still applies after the opt-in. server.ts calls resolveDbUrl
directly, so first-boot DB creation is untouched.
All five maintenance-script headers now state the SQLite opt-in.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
client_module.created_at was added and stamped in assignModule, and
stalledProjects now correctly excludes no-tick projects whose created_at
is NULL — but the APEX importer (the path that created the real ~300
clients' projects) inserted client_module WITHOUT created_at. Every
imported project with zero ticks was therefore permanently invisible on
the "Onboarding stalled" tile, exactly the population it exists for.
Three parts:
- Stamp created_at in the APEX importer's client_module insert.
- Backfill existing NULL rows from installed_on where known (both
db.ts migrate() for SQLite and migrations-pg.ts for Postgres); never
fabricate a date when installed_on is also unknown.
- stalledProjects now returns { rows, unknownAgeExcluded } instead of a
bare array, so projects excluded for unprovable age are counted, not
silently dropped. GET /projects/stalled responds with
{ ok, projects, unknownAgeExcluded }; UI wiring left to the parallel
agent.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
alreadyNew was set false for EVERY row whose key wasn't a template key,
including custom_* rows that the same function deliberately preserves
verbatim. So a project with even one ad-hoc "+ Add step" entry was
never "identical" and got DELETE-and-rebuilt on every single run (new
row ids, a fresh audit row each time) — contradicting the documented
"safe to re-run / a second run does nothing".
Idempotency is now computed over non-custom rows only, using the same
custom_* discriminator addProjectMilestone's key generation relies on.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
The guard only threw when NODE_ENV==='production' AND resolution came
back empty. But NODE_ENV is exactly what isn't reliably set for the
maintenance scripts this guards (they're run by hand from an operator
shell with no service-definition env and no dotenv), so on the real
prod box the guard never fired: resolution silently returned '', the
script opened a brand-new empty SQLite file under <cwd>/data, and
printed a false "0 project(s) rebuilt" success.
Now the guard fires on the OUTCOME instead: whenever resolution
selects SQLite, refuse to run unless the target file already exists —
a migration/maintenance script must never CREATE a database. Applies
only to requireDbUrlForEnv (the script entry path); server.ts calls
resolveDbUrl directly and is untouched, so first boot still creates
its DB as before.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
The test never connects to Postgres, it only asserts resolution
behaviour, so the real prod URL had no reason to be duplicated here.
Swapped for a dummy postgres://u:p@h:5432/db throughout.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
FIX D: stalledProjects treated a no-tick project's missing done_on as the
sentinel '0000-00-00', always older than any cutoff, so every
freshly-assigned project immediately showed up on the "Onboarding stalled"
tile. It's now measured against client_module.created_at (stamped by
assignModule); when that's also unknown (predates the column, or an import
path that doesn't stamp it) the project is excluded rather than assumed
ancient.
FIX E: STEP_STATUS only mapped the new template's keys, but TAIL_FALLBACK
(any module with no seeded/owner-configured tail -- WHATSAPP, ATM, AMC, ...)
still uses the legacy 'installed' key, so ticking "Installation done" on
those modules left status at 'quoted' forever. Added installed->installed
to STEP_STATUS (checked: no legacy payment key needs a mapping, since
payment never drove status even in the new vocabulary).
FIX F: bulkSetMilestone did a bare UPDATE, so a bulk go_live never advanced
client_module.status and a bulk untick never cleared a stale
payment_id/document_id link. Each id now routes through setMilestone itself
(same status-advance + link-clearing path a single tick uses), inside the
existing transaction -- nested transaction() calls become savepoints on
both engines, so a project without the milestone key is caught and skipped
without losing "bulk ops only touch projects that have the milestone".
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
advance_paid/balance_paid (and the interim advance_payment/balance_payment)
all collapse onto the single new payment_received key, and the carry used
Map.set -- whichever row was visited second silently overwrote the first's
done_on/payment_id, counted as neither carried nor dropped, with the audit
`before` left `undefined` so the loss was unreconstructible.
pickSurvivingTick() now picks deterministically (prefers the tick with a
real linked payment_id; otherwise the later done_on), the losing tick is
counted in a new `collapsed` field, and the audit `before` payload is now
the full pre-migration row set instead of `undefined` -- any discarded tick
is reconstructible from the append-only log.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
GET /projects/stalled had no try/catch, and a caller-supplied ?days= flowed
straight into `new Date(...).toISOString()`. Confirmed the exact crash:
Number('1e300') passes Number.isFinite (so the old guard let it through),
but the resulting cutoff math overflows the valid Date range and
.toISOString() throws RangeError. Uncaught in an async Express handler this
is an unhandled rejection, which kills the whole process on Node 22 -- any
authenticated role, including plain staff, could take the console down with
one GET.
`days` is now validated (integer, 0-3650) with an explicit 4xx on anything
hostile, falling back to the configured default only when the param is
absent (matches the frontend, which never sends ?days= at all). Audited
every other route this feature added to the same section: /projects/board
and /projects/pending/:key had the identical no-try/catch shape and are now
wrapped too.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
apply-onboarding-pipeline.ts and migrate-onboarding-templates.ts resolved the
engine as `DATABASE_URL ?? ''`, diverging from server.ts's production
fallback. On the prod box (DATABASE_URL unset) each script opened a brand-new
empty SQLite file, "succeeded" against it, and Postgres was never touched.
resolveDbUrl()/requireDbUrlForEnv()/pgTargetDescription() now live in
db-pg.ts as the one place this is decided; server.ts and both scripts call
it. The scripts additionally hard-fail (never fall back to SQLite) when
NODE_ENV=production resolves nothing, log their target engine before doing
any work, and close the DB handle in a finally.
Also lands client_module.created_at (nullable, additive) — schema groundwork
FIX D uses to measure a no-tick project's age from its real creation date
instead of treating a missing done_on as infinitely old.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Final whole-branch review FIX 3.
migrateOnboardingTemplates previously DELETEd every project_milestone row and
reinserted only template keys, so a project-specific step added via
addProjectMilestone ("+ Add step", custom_* keys) vanished with no trace if
un-ticked, or was miscounted as `dropped` if ticked.
Old rows are now partitioned before the DELETE into template-mapped vs
ad-hoc custom (not a template key, not in KEY_MAP, and not a known old
generic FRONT_FALLBACK/TAIL_FALLBACK key like amc_start — those keep their
existing drop behavior). Custom rows are re-inserted unchanged after the
template block, sort continuing past it, preserving
label/done/done_on/payment_id/document_id. The return type and the audit
`after` payload both gained a `preserved` count/list.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Final whole-branch review FIX 1 + FIX 5.
- setFrontTemplate / setModuleTail now reject a key that collides across the
front/tail halves (checked against the default tail, every configured
per-module tail, and the shared front respectively) instead of letting
milestoneTemplate silently compose a duplicate-key list.
- ensureProjectMilestones adds each inserted key to its own `existing` set
inside the loop, so a template that still ends up with a duplicate key
(a pre-existing bad setting) is deduped instead of hitting the
UNIQUE (client_module_id, key) violation a second time.
- GET /client-modules/:id/milestones now wraps ensureProjectMilestones +
listProjectMilestones in try/catch -> 400, matching its POST sibling,
instead of an unhandled async rejection that hung the request.
- advanceStatusForStep now stamps client_module.installed_on / trained_on
from the triggering milestone's own done_on the first time status
advances into 'installed' / 'trained' (never overwriting an existing
date), replacing the UI date-writers the Client Detail redesign removed
without a replacement. Reflected in the same audit row as the status
change.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Backend half of the Client Detail redesign follow-ups (#7, #6, #4).
#7 — quotation step links its document
- project_milestone gains a nullable `document_id`, landed on BOTH engines:
a PRAGMA-guarded ALTER in db.ts migrate() and pg migration 016.
- setMilestone(..., doneOn?, paymentId?, documentId?) validates the document
belongs to the same client as the project (mirroring the paymentId check),
stores it on tick and clears it on untick; Milestone gains `documentId`.
- POST /client-modules/:id/milestones accepts `documentId`.
- migrateOnboardingTemplates carries document_id across the template swap so a
linked quotation is not lost.
#6 — ticket classification
- ticket gains `type TEXT NOT NULL DEFAULT 'service'` (db.ts SCHEMA + guarded
ALTER, pg migration 017); imported APEX rows fall to the default.
- Config over code: the list is the `ticket.types` setting (seeded on first
boot) resolved by ticketTypes(), with TICKET_TYPE_FALLBACK in code.
- GET /tickets/types; create/update accept + validate `type`; list rows carry
it and GET /tickets?type= filters (blank/omitted = every type).
#4 — cleanup
- Refreshed the stale file comment atop repos-milestones.ts and the
project_milestone comment in db.ts: both now describe the composed
front + per-module tail model (they still described the old flat template).
- Migration tests for the interim keys advance_payment / balance_payment ->
payment_received, plus the document-link carry.
npm run typecheck clean; npm test 473 passed / 5 skipped (was 453/5).
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Supersedes the flat per-module onboarding templates: milestoneTemplate(db, code)
now returns [...front, ...tail]. front is a shared setting
(project.milestone_template.front, FRONT_FALLBACK code default: enquiry ->
visit/meeting -> quotation sent -> quotation approved) prepended to every
module's checklist. tail resolves per-module setting -> global setting ->
TAIL_FALLBACK (the old generic list), unchanged in precedence.
- STEP_STATUS gains quotation_approved -> ordered (never-downgrade guard kept).
- seed.ts seeds the front once plus SMS/RTGS/MOBILEAPP tails (advance/balance
payment steps collapsed into a single payment_received tail step); no longer
seeds a bare project.milestone_template default.
- migrate-onboarding-templates.ts KEY_MAP updated so advance_paid, balance_paid,
advance_payment and balance_payment all carry forward to payment_received;
its idempotency check holds against the longer composed list.
- Updated seed-templates, milestones-templates, milestone-status,
migrate-onboarding-templates, milestones and milestone-payment tests to the
composed content/mappings (TDD: reverted the implementation, confirmed the
updated tests red, restored the implementation, confirmed green).
Backend + tests only; the live-DB migration run is a separate step.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Adds stalledProjects(db, olderThanDays, today) to repos-milestones.ts:
active projects with a pending step whose last progress (MAX done_on,
or module creation if nothing ticked) is older than N days. GET
/projects/stalled reads ?days= with a project.stall_days setting
fallback (default 30).
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Threads an optional `source` through DraftInput/insertDocRow (default 'hq',
preserving current behaviour when omitted), tags module renewals as
'module_renewal', and adds generateBulkSmsPurchaseQuote — an ad-hoc bulk SMS
credit top-up priced via the existing usage rate card, tagged
'bulk_sms_topup' so the client-detail document list can distinguish it from a
subscription renewal. New route: POST /client-modules/:id/bulk-sms-quote.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
clientLedger now attaches allocations (docNo + amount) to each payment and
returns an outstanding[] list — issued invoices with outstandingPaise > 0
plus open (draft/sent/accepted) proformas, oldest-first by docNo. Read-only
extension over the existing settlement math (outstandingPaise, allocations);
no money/allocation logic changed. Mirrors the widened Payment/Ledger shapes
onto apps/hq-web/src/api.ts for the upcoming Payments tab.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Add one test case to cover the scenario where old milestone keys that
don't map to the new template (amc_start, setup_done for SMS) are ticked
and should be counted in the migration's dropped tally. Verifies both
dropped and carried counts are accurate.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Idempotent migrateOnboardingTemplates(db): for every client_module whose module
has a per-module onboarding template (task 2), rebuilds its milestone rows from
that template, carrying mapped ticks (installed->installation, go_live->go_live,
advance_paid->advance_payment, balance_paid->balance_payment, setup_done->
setup_configuration when present) with done_on and payment_id intact. Unmapped
ticks (amc_start, or setup_done where the new template has no matching step) are
dropped and counted. Audited per project; a second run is a no-op via an
identical-key-set short-circuit. Adds the CLI wrapper (mirrors
apply-name-fixes.ts's open-DB idiom, Postgres or SQLite) for the real one-time
run against production data.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Milestone.paymentId + project_milestone.payment_id (SQLite + Postgres,
additive). setMilestone(..., paymentId?) resolves the payment's
received_on to stamp done_on when a payment-step tick links a real
payment; the linked payment must belong to the same client. api.ts
threads paymentId from the milestone POST body.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Ticking the installation/training/go_live onboarding milestone steps now
advances the coarse client_module.status (installed/trained/live), audited
in the same transaction. A STATUS_RANK guard blocks downgrades from
unticking or ticking an earlier step after a later one already landed.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Widens milestoneTemplate(db, moduleCode?) to resolve project.milestone_template:<CODE>
before the global project.milestone_template setting, falling back to FALLBACK_TEMPLATE.
ensureProjectMilestones now looks up the client_module's module code and resolves the
template through it, so each module can carry its own onboarding checklist as a dated
setting row instead of one hardcoded global list.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
- SMS balances now lists ONLY live SMS subscriptions (status='live'); quoted/installing
ones are excluded (105 -> 79 real). The daily/manual gateway pull is likewise live-only.
- Removed the full-row red tint on low rows — the red balance badge alone signals it.
- Assigning the SMS module now REQUIRES the gateway username + password (both the Client
360 'Assign module' form and the Modules-page '+ Add client' panel): Assign is blocked
until they're filled, and they're set on the new subscription in the same flow. So a
live SMS client always has the credentials the balance pull needs.
Test updated (assign live); typecheck + suite green.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
Cancelling an issued document now needs a reason: cancelDocument takes it, refuses an
empty one, and records it on the 'cancelled' event (visible in the doc timeline) and in
the audit after-image. The Cancel dialog gains a required reason box (the confirm stays
open with an error until one is given). Supersede & recreate cancels with 'Superseded &
recreated'. Documents are never hard-deleted (immutability) — cancel is the path, now
always explained. Full suite 416 green.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
Founder: there is no Tecnostac in this application. Replaced the seed default
company.name ('Tecnostac' -> 'SiMS'), demo-seed data, ~22 test fixtures/emails, and
docs (Tecnostac -> SiMS, @tecnostac.com -> @sims.com). Live DB company.name also
updated to 'SiMS' (shows on dashboard, documents, letterhead, reminder emails). Owner
email was already SiMS (simssoftware13@gmail.com). Full suite 416 green.
Owner has no HQ_SECRET_KEY, so the D31 unlock-with-key path was impossible. crypto is
now fully keyless: encrypt/decrypt ignore keyHex (params kept so a key can be wired back
later); decrypt returns '' for any leftover legacy AES value (unreadable → treated as
unset, never fed back as ciphertext). Key-based /admin/migrate-plaintext replaced by
keyless /admin/clear-legacy-credentials (clearLegacyCiphertext) which NULLs unreadable
pre-D31 values. .env.example marks HQ_SECRET_KEY removed.
Ran the cleanup on live data: 76 SMS + 110 client DB passwords were unrecoverable and
cleared (usernames intact) — must be re-populated in plaintext (APEX re-import / re-entry).
Also fixes the one real defect the D31 adversarial review confirmed: maybeRefreshSmsDaily
still had a 'keyHex==="" -> no-op' guard that would silently kill the DAILY auto-refresh
once the key was gone (manual refresh masked it). Removed; the daily pull now runs keyless
like the manual path. Full suite 416 green.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
Removes credential encryption at rest so the SMS-gateway logins (and other portal/DB
passwords, module secrets, Gmail token) are usable daily without a held-out
HQ_SECRET_KEY. Keystone: crypto.encrypt() stores tagged plaintext ('plain:'),
decrypt() untags keylessly and still reads legacy AES *with* the key to migrate it.
Drops the six 'refuses/cannot without HQ_SECRET_KEY' guards. Scope is the reversible
credential vault ONLY — login passwords stay one-way scrypt-hashed (@sims/auth).
Secrets still kept out of list payloads + audit rows; reveal still managerial+audited.
migrateLegacyCiphertext + POST /admin/migrate-plaintext (owner) rewrite existing AES
rows as plaintext using the key one final time (idempotent, one audit summary), after
which the key is never needed. SECURITY: a DB dump now exposes these in the clear.
Tests updated to the plaintext policy; new migrate-plaintext.test locks decrypt +
migration + idempotency. Full suite 416 green; typecheck clean.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
Security review of 58c9799: (1) block loopback/link-local/private targets for the
admin-set sms.balance_api_url before sending client credentials (assertSafeGatewayUrl
— rejects 127/10/172.16-31/192.168/169.254/localhost/::1/.internal), so the setting
can't be pointed at cloud metadata or internal services; (2) redirect:'manual' so a
3xx can't carry credentials to another host; (3) error messages never interpolate the
credential-bearing URL. http:// transport kept — it is the vendor panel's only
interface and the operator supplied it. Tests cover the block list.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
Pull each SMS client's actual credit balance from the provider panel instead of
manual entry. sms.balance_api_url (dated setting, default 164.52.195.161/API/
BalAlert.aspx) returns pipe-delimited code|STATUS|payload; parseBalanceResponse
reads the trailing number and flags ERROR lines, never guessing. Uses each
subscription's already-imported username/password_enc — the automated path decrypts
directly (no reveal-audit spam) and audits sms_balance ONLY when it changes;
per-poll telemetry lands in the unaudited sms_balance_check table (SQLite schema +
PG migration 010). Managerial 'Refresh from gateway' button (POST /reports/sms-
balances/refresh) + per-client refresh; scheduler does one pass per day
(maybeRefreshSmsDaily). Low-balance alert on the Dashboard + cockpit tile. Needs
HQ_SECRET_KEY (import-time key) to decrypt — no-ops with a clear message otherwise,
never stores plaintext. Parser unit-tested; full suite 408 green.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
Age column on the workbench (days since opened); a still-open ticket aged past
ticket.sla_days (dated setting, default 7) shows red with a warning. New 'Overdue'
filter chip with an honest server-side count (still-open AND opened on/before the
SLA cutoff). Each row gains a 'Bill' action that opens New Document with the client
pre-selected (/documents/new?client=<id>) — the light ticket->invoice path, staff
add the lines. Kept deliberately un-bulky: no kanban, reuses the existing table.
Test locks the overdue filter + count. typecheck + full suite (403) green.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
New 'SMS balances' screen: every SMS client with its credit balance (the module's
own sms_balance service field), lowest first so the ones about to run dry sit on
top. Low = below sms.low_balance_threshold (dated setting, config over code);
'Low' is the default view = the upsell list. One click raises a top-up proforma
(reuses the module renewal-quote path); client name links to the Modules tab where
the balance is kept. Owner cockpit gains an 'SMS balance low' tile as the at-a-
glance alert. Read-only report, no audit. Test locks parse/flag/sort. typecheck +
web build clean.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
listAuditPage (filters: entity/action/user/entity-id/date; honest total; newest
first) + auditFacets. GET /audit owner-only. New /audit 'Audit log' page: entity/
action filters from server facets, paginated table with a per-row before->after
key diff, actor names mapped via employees. Reading the log writes no audit row.
Test locks filter+pagination. typecheck + web build clean.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
repos-mis misCockpit(today): this-month vs last for billed (issued invoices) +
collections (payments), total outstanding (dues-aging), open pipeline value,
renewals due in 30d, open tickets, reminder queue — composed from the existing
repo aggregates so figures reconcile with Reports/Dashboard. Owner-only GET
/reports/mis. New /mis 'Cockpit' page: KPI tiles with MoM deltas + click-through
to the source pages. Money in tests. typecheck + web build clean.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
renewalsDue(from,to,today): unified list of module (client_module.next_renewal,
priced by the dated price book) + AMC (period_to, fixed amount) renewals with
amount + days-until, earliest-first. generateModuleRenewalQuote: one-click
proforma carrying the client's module kind/edition forward at the price-book
rate (AMC keeps its existing renewal-invoice). Routes GET /renewals,
POST /client-modules/:id/renewal-quote. Renewal reminders already fire from the
scheduler (renewal_due/amc_expiring) — this is the worklist + action. Tests green.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
- gstSummary(range): per-month GST filing summary over issued INVOICE (adds) +
CREDIT_NOTE (subtracts, s.34), CGST/SGST/IGST split from stored paise, with a
grand total. Route GET /reports/gst-summary. Money locked in tests.
- listDocuments gains free-text q (doc number OR client name, case-insensitive
both engines); GET /documents ?q=.
- bulkUpdateClients (owner) + bulkSetMilestone (owner) for cleanup at scale —
POST /clients/bulk, POST /projects/bulk-milestone; each client keeps its own
audit; milestone bulk writes one summary audit. Suite green.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
Red-team backend-quality highs/mediums:
- Added 30 indexes on the foreign-key / status / date / renewal columns the
lists, reports, joins and scans read — zero existed, so every query full-scanned
(tolerable on dev SQLite, a real problem on production Postgres at 300 clients /
thousands of tickets & documents). Same DDL on both engines: INDEX_DDL in the
SQLite SCHEMA + pg migration 007 (CREATE INDEX IF NOT EXISTS, idempotent).
- Client search + district/sector filters now LOWER(...) LIKE LOWER(?): bare LIKE
is case-insensitive on SQLite but case-SENSITIVE on Postgres, so search silently
broke on the prod engine. Test locks case-insensitivity.
Verified: pg migrations 006+007 apply cleanly on local Postgres; suite green.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
Red-team correctness findings:
- SqliteDb.transaction now serializes top-level transactions with an async
mutex and tracks re-entrancy via AsyncLocalStorage (nested calls -> savepoints,
no deadlock). The old shared txnDepth counter let concurrent async callers
interleave BEGIN/COMMIT on the one connection -> silent data loss; a 50-way
concurrent read-modify-write test now lands exactly.
- generateRecurring wraps each plan in try/catch: one misconfigured plan is
parked (audited 'generate_failed') and the scan continues, instead of aborting
all remaining generation and the entire auto-send drain, every scan.
- POST /modules/:id/notify broadcast IIFE gets a trailing catch so a mid-loop
throw answers the request instead of hanging it.
Full suite 388 green.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
Red-team money findings:
- createCreditNote caps the CN to the invoice's remaining creditable value
(invoice payable − already-credited by non-cancelled CNs), read+insert in
one txn. Blocks a CN exceeding its invoice / unlimited CNs flipping it 'paid'.
- nextDocNo allocates via one atomic UPDATE ... RETURNING (row lock) instead of
read-then-bump — no duplicate/burned numbers under concurrent issue (Postgres).
- markStatus rejects accepted/lost on non-QUOTATION docs — an issued invoice can
no longer be hidden from overdue chasing via a quote-only transition.
- computeBill bounds qty magnitude (keeps fractional for weighed Store goods);
HQ buildLines additionally requires whole-unit integer qty (SMS/module seats).
- recordPayment rejects TDS greater than the cash received — no fabricated-TDS
'settlement' with no money.
money-guards test locks all four. Suite money paths green.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
Red-team authorization findings:
- New requireManagerial middleware. Applied to POST /documents/:id/cancel,
/credit-note (financially destructive — reverse money / consume a number)
and /documents/:id/share (mints an UNAUTHENTICATED public PDF link — the
IDOR exfiltration high). Any signed-in staff could previously do all three.
- PATCH /interactions/:id now 403s unless the viewer is the interaction's
author or managerial — staff can no longer overwrite another employee's
logged call/visit.
New authz-gates test locks both. Note (deliberate): general client read/edit
stays open to staff — a shared support-team book is the intended model; the
genuinely sensitive credential reveals are already managerial + rate-limited.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
Red-team highs/mediums (auth surface):
- staff_user gains failed_count/locked_until; login() locks an account for 15min
after 8 consecutive failures, clears the counter on success (both engines).
- login() is now constant-time: a missing email still spends one scrypt verify
against a fixed dummy hash — no timing/enumeration oracle. Returns a typed
{ok:false,reason} instead of null.
- POST /auth/login gets per-IP (20/min) AND per-email (8/15min) fixed-window
rate limiting layered over the lockout; 429 on trip.
- Credential reveal routes (db-password, module password, module secret) get a
per-user reveal limiter (20/min) so one insider cannot bulk-decrypt everything;
added missing getClientModule 404 guards.
- app.set(trust proxy,1) so req.ip is the real client behind nginx/Caddy and the
per-IP limiters actually bucket per client.
- makeRateLimiter extracted to rate-limit.ts (shared, no server<->api cycle).
Tests: lockout, counter-reset, unknown-email, and existing auth all green.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
project_milestone: each client_module (a project) carries a checklist
seeded from the dated project.milestone_template setting (advance paid /
setup / installed / go-live / balance / AMC — owner-editable). Ticking
stamps the date; audited. ensureProjectMilestones is idempotent and
back-fills imported projects. Cross-project board (per-milestone pending
counts) + drill-down (who's pending go-live). Routes: GET/POST
/client-modules/:id/milestones, GET /projects/board, /projects/pending/:key.
assignModule seeds the checklist for new projects.
Also fixes a latent flaky-ordering bug: uuidv7() now bumps monotonically
within a process, so ORDER BY id is genuine creation order (the guarantee
pagination + the sync cursor already assume) — sub-millisecond ties no
longer fall back to the random tail. Suite 377 green, stable across runs.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
usage_rate_band: a dated per-module rate card of volume bands (generic;
SMS packs the first user). resolveUsageRate picks the band by count
(highest min_qty <= count); below the lowest band = under the minimum
order. buildLines resolves price as: explicit per-line override >
tiered rate card > dated price book — so an SMS line prices by count
automatically, a save under 50,000 is refused, preview warns. Rates are
integer paise (0.40=40p), a new card is a new dated set of rows so issued
invoices keep their rate. Owner routes GET/POST /modules/:id/rate-card.
Tests lock the founder card to the paisa: 50k→₹23,600, 100k→₹43,660,
200k→₹80,240, 300k→₹1,06,200 incl 18% GST. Suite 372 green.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
ensureModule seeds each module field_spec (SMS/RTGS/MOBILEAPP declared
fields; ATM/WHATSAPP empty — their one datum stays in the D20 provider
column), back-filling only when a module spec is still empty so an
owner-customized spec is never clobbered. SMS/RTGS/MobileApp service data
now writes into client_module.field_values keyed to the spec; provider/
username/portal-password keep the D20 columns; unkeyed values still land in
details so nothing is lost. Tests assert seeded specs + keyed values; suite
363 green. Real dry run unchanged: 273 clients, 150 docs, 167 links.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
module.field_spec (FieldDef[]: key/label/type/options/required) declares a
module's own operational fields, validated on create/update. client_module
gains field_values (JSON map of non-secret values, type-checked against the
owning module's spec) + secrets_enc (one AES-GCM blob over a {key:plaintext}
map; the clear key-list prefix lets the UI show which secrets are set without
decrypting). Routes: PUT /client-modules/:id/fields (staff), POST
/client-modules/:id/secrets + /reveal-secret (managerial, audited, plaintext
never logged). Additive over D20 — provider/username/password stay. Both
engines (SCHEMA + migrate guards + pg 003). 11 new tests; module suite green.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
Bills are LINE ITEMS not documents: group by INVOICE_NO across three GST
eras (GST pseudo-line / 2026 columns-only / pre-GST zero-tax). 46 raw
ticket statuses -> 5 via ordered pattern map; OLD_ID re-key chains
collapse so superseded rows do not double-import. YES/NO flags respect
real vocabulary (GENERAL ENQUIRY != yes); ATM cell value becomes the
module provider. SID 329 test client + orphan refs skip (counted notes,
distinct from commit-blocking problems). District normalize, NO DATA
scrub, payment normalize, richer-row merge for duplicate service rows,
shared-number-space series seed. Dry run over the real export: 273
clients, 150 documents = Rs 33,16,800.24, 72 payments, 1,826 tickets,
667 interactions, INVOICE seed 10252 (matches APEX next_val). Suite 358
green.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
parseWorkbookCsvs + stageApexFull (pure validation pass, zero writes) +
commitApexFull (one transaction): clients w/ encrypted DB passwords +
secretary contacts, YES-flags -> live client_module rows for the 5
modules, branches, bill_filed -> QT/PI/INV history with paisa-exact
CGST/SGST split + payment/allocation rows + series seed, 4.5k tickets
with status/date mapping, SMS/RTGS service data -> client_module
provider/username/password_enc/details, project lists -> interaction
notes with tolerant APEX date parsing. Plaintext passwords never stored
or audited; missing HQ_SECRET_KEY is a blocking problem. CLI:
npm run import:full -- --dir <folder> [--commit]. 7 new tests; suite
359 green.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
Thomas Joise
Thomas Joise