add-statushistories-tenant-indexrun.md00_intake/stub.mdThe nightly db:audit run flagged [missing-tenant-index] statushistories: Documents carry "tenantId" but no index leads with it — tenant-scoped queries scan the collection. The tenantPlugin
(packages/services/src/db/plugins/tenant.ts) injects tenantId into every find / findOne / update /
delete / count query, so every read of statushistories is tenant-scoped. The collection's live
indexes lead with entityId and status, not tenantId
(packages/services/src/db/models/status-history.ts:52-53), so the entity-timeline reads that drive
the status history view can't seek on tenantId and degrade toward collection scans. The audit reads
live $indexStats, so this reflects the production index set, not the schema. statushistories is the
strongest of the three remaining missing-tenant-index collections by impact ÷ risk: it backs the
status-timeline surface across leads, quotes, proposals and invoices, and a single additive
tenant-leading index is a certain win with near-zero risk.
Add tenant-leading index coverage to statushistories, shipped as a reviewed forward/back migration
(pnpm --filter @sustentus/services db:migrate create add-statushistories-tenant-index) — never ad
hoc. Prefer folding tenantId into the existing entity-timeline compound following ESR (Equality,
Sort, Range) rather than a bare { tenantId: 1 }, so the real query shape — "history for one entity,
newest first" — is fully covered:
{ tenantId: 1, entityId: 1, entityType: 1, changedAt: -1 }Define/Build confirm the exact set against actual query usage (and whether the now-redundant
{ entityId: 1, entityType: 1, changedAt: -1 } compound should be dropped in the same migration).
up/down adds a tenant-leading index to statushistories and drops
any index it makes redundant; down cleanly reverses it.status-history.ts schema index(...) declarations match the migration (schema and live DB
agree).db:audit no longer reports missing-tenant-index for statushistories.missing-tenant-index collections (csats, milestones) — separate stubs.orphaned-reference finding (200/200 sampled statushistories docs reference a tenant that no
longer exists) — that needs its own decomposition of the delete path, not an index change.unused-index / redundant-index finding on this collection.db-auditor + mongodb-query-optimizer skills, ESR guidance).packages/services/src/db/models/status-history.ts, a new migration under
packages/services.01_define/output/spec.mdThe nightly db:audit flags [missing-tenant-index] statushistories: Documents carry "tenantId" but no index leads with it — tenant-scoped queries scan the collection. The tenantPlugin
(packages/services/src/db/plugins/tenant.ts) injects tenantId into every find / findOne / update /
delete / count query, so every read of statushistories is tenant-scoped. But the collection's
live indexes lead with entityId and status, not tenantId
(status-history.ts:52-53), and the plugin's own { tenantId: 1 } fallback never fires because
status-history.ts declares tenantId explicitly. So tenant-scoped status-history reads can't seek on
tenantId and degrade toward collection scans as data grows. This advances the database leanness &
query efficiency initiative (objective: keep tenant-scoped queries index-backed) and is the
highest-impact / lowest-risk of the three remaining missing-tenant-index collections — it backs the
status-timeline surface across leads, quotes, proposals and invoices, and the fix is a single additive,
reversible migration with near-zero risk.
Add tenant-leading index coverage to statushistories, shipped as a reviewed forward/back migration
(pnpm --filter @sustentus/services db:migrate create add-statushistories-tenant-index) — never ad
hoc — and keep the schema index(...) declarations in status-history.ts in lockstep with it.
Fold tenantId into the existing entity-timeline compound following ESR (Equality, Sort, Range) so the
real tenant-scoped query shapes in the codebase are index-backed:
build-progress-items.ts:87 — find({ tenantId, entityId, entityType }).sort({ changedAt }) — the
entity timeline (status history view).sla/timing.ts:140 — find({ tenantId, entityId: { $in }, entityType: "lead" }).sort({ changedAt: -1 })
— latest status per lead.leads/index.ts:593 — updateMany({ tenantId, entityType, entityId }, …) — soft-delete cascade.All three lead with tenantId + entityId + entityType and sort/read by changedAt, so the proposed
key is:
{ tenantId: 1, entityId: 1, entityType: 1, changedAt: -1 }This makes the existing { entityId: 1, entityType: 1, changedAt: -1 } compound a redundant prefix, so
the migration drops it in the same change. The unrelated { status: 1 } index is left untouched (it is
not part of this finding). Build confirms the exact key order against ESR using the
mongodb-query-optimizer guidance before writing the migration.
up/down adds a tenant-leading index to statushistories and drops
the now-redundant { entityId: 1, entityType: 1, changedAt: -1 } compound; down cleanly reverses
both (recreates the old compound, drops the new index), tolerating an already-absent index so a
partial re-run converges.status-history.ts schema index(...) declarations match the live index set the migration
produces (schema and DB agree).db:audit no longer reports missing-tenant-index for statushistories.missing-tenant-index collections (csats, milestones) — separate stubs.orphaned-reference finding (200/200 sampled statushistories docs reference a tenant that no
longer exists) — needs its own decomposition of the delete path, not an index change.unused-index / redundant-index finding on this collection, including the standalone
{ status: 1 } index.02_build/output/notes.mdfeat: add-statushistories-tenant-index — tenant-leading index + migrationpackages/services/src/db/models/status-history.ts: replaced the
{ entityId, entityType, changedAt: -1 } schema index with the tenant-leading
{ tenantId, entityId, entityType, changedAt: -1 }, with a comment explaining the
ESR ordering and why tenantId must lead (every read is tenant-scoped via the
tenantPlugin). The { status: 1 } index is left as-is (out of scope).packages/services/src/db/migrations/1782600000000-add-statushistories-tenant-index.ts:
new forward/back migration mirroring the shipped add-invoices-tenant-index
pattern. up creates the tenant-leading index and drops the redundant
entityId_1_entityType_1_changedAt_-1 compound; down recreates that compound and
drops the tenant-leading index. Drops use a dropIfExists helper that swallows
IndexNotFound so a partial re-run converges, and createIndex is idempotent for
an identical key. Mongo auto-names the index to mongoose's <field>_<dir>
convention, keeping schema and live DB in agreement.up/down adds a tenant-leading index and drops the
now-redundant { entityId, entityType, changedAt: -1 } compound; down cleanly
reverses both, tolerating an already-absent index — implemented via dropIfExists.status-history.ts schema index(...) declaration matches the live index set
the migration produces ({ tenantId, entityId, entityType, changedAt: -1 }).db:audit no longer reports missing-tenant-index for
statushistories — verifiable only after the migration runs against the live DB
(post-merge); the change is the prerequisite and matches the shipped invoices fix
that cleared its finding.statushistories is mongoose's pluralisation of the model name
statushistory (cf. invoices for invoice), matching what db:audit reported.$in on entityId in sla/timing.ts is a multi-point equality bound, not a true
range, so it sits comfortably before the changedAt sort in the compound; the index
still backs that query (index-bounded scan rather than a collection scan).03_release/output/investor-update.mdWho it's for: Every persona that views status timelines (platform-wide) What shipped: Status-history reads are now backed by tenant-leading database indexes instead of scanning the collection. Why it matters: Keeps status-timeline infrastructure reliable at volume — advancing Scale the Bridge and our Q2 goal to validate technical infrastructure.
Dig deeper: <merged-PR URL> · <changelog entry URL>
03_release/output/release.mdMigrate preview database (cross-branch migration-timestamp collision, not a defect here); resolved after the branch was updated with main and the migration renumbered — re-running on the rebased branch. All other checks were green.1782300000000, which collided with sibling PR #497
(add-milestones-tenant-index, same db-audit-findings epic) — both hand-picked the same value
instead of using db:migrate create. #497's migration had been recorded in the shared preview
database, so this branch's Migrate preview database job crashed (ERR_MODULE_NOT_FOUND) trying
to import #497's migration file, which didn't yet exist on this branch.main (so its
migration file now exists on this branch), #499 (prune orphaned migration records before db:migrate) merged (so a stray DB record can no longer crash the run), and main was merged
into this branch. This branch was rebased onto that updated state.1782400000000 and #496 (csats) later merged at 1782500000000 —
the value this migration had been bumped to — so it was renumbered again to a now-permanently-unique
1782600000000-add-statushistories-tenant-index.ts. All three sibling migrations (invoices
…200000000, milestones …400000000, csats …500000000) are on main, so no sibling remains to
collide with.main merge. With all sibling files present and a unique
timestamp, migrate-preview reconciles and runs this migration in order.up
creates the new index before dropping the old (no read gap); index name strings match Mongo's
<field>_<dir> auto-naming for both up and down; collection name statushistories is the
correct pluralisation of model statushistory; down is an exact symmetric reversal;
dropIfExists swallows only IndexNotFound and rethrows otherwise; schema and migration index
identities agree. Call-site trace confirmed every StatusHistory read filters by tenantId (+
entityId/entityType, sort changedAt) and none use .aggregate()/raw collection access, so
dropping the old { entityId, entityType, changedAt } compound removes no needed coverage.
CONVENTIONS satisfied (arrow fns, type, async/await, named imports).up/down adds the tenant-leading index and drops the now-redundant { entityId, entityType, changedAt: -1 } compound; down reverses it — 1782600000000-add-statushistories-tenant-index.tsstatus-history.ts index(...) declaration matches the migration exactly ({ tenantId, entityId, entityType, changedAt: -1 })db:audit no longer reports missing-tenant-index for statushistories — verifiable only after the migration runs against the live DB post-merge (same as the shipped invoices fix that cleared its finding)