hot-path-tenant-indexesrun.md02_define/output/spec.mdleads is the platform's hottest collection and has no usable tenant-leading index. Its only
tenantId-leading index is partial on requestId (lead.ts:184-191), so it cannot serve general
tenant-scoped reads — and every read is tenant-scoped, because tenantPlugin injects tenantId
into each query. The consequence is that the main service-leads table, the kanban, the CSM
portfolio, vendor analytics and the expert bid pool all run on non-tenant-leading indexes or
outright collection scans, with in-memory sorts on top
(services/leads/index.ts:165-228,300,987-1001, services/vendor/index.ts:634-1380,
services/csm-portfolio/index.ts:255-309). Quotes, invoices and lead_expert_matches have hot
query shapes with no supporting index at all (services/quote/index.ts:137-242,
services/invoice/index.ts:227-247, services/leads/index.ts:357-361).
The gap survived because the runtime auditor is blind to it: its tenant check only asks whether an
index's first key is tenantId (db/audit/index.ts:122-131), so the partial {tenantId, requestId}
index satisfies it and CI has never flagged leads. Every dashboard an Admin uses to demonstrate
the product reads through these paths, so the latency is the first thing a vendor partner sees —
this is the data-layer floor under Refine the bridge / Q2-2026 Objective 1 — Establish
Product-Market Fit with Vendor Partners. Evidence: _source/audit-report.md findings #3, #12,
#15; approved as decision D1.
One migration, plus the schema .index() declarations that must agree with it, adding the indexes
the audit matched to real queries and dropping the clear prefix-redundancies. Indexes only — no
query code changes — with one exception carried by the same theme: closing the auditor blind spot
that hid this.
Add
| Collection | Index | Query it backs |
|---|---|---|
leads |
{tenantId, status, updatedAt: -1} |
main table + kanban, recency-sorted |
leads |
{tenantId, isActiveProject, csm} |
CSM portfolio |
leads |
{tenantId, createdAt: -1} |
tenant-wide recent list, analytics |
leads |
{tenantId, bidDeadline} partial on {bidPool: true} |
expert bid pool |
quotes |
{tenantId, proposal} |
accept-bid + invoice creation |
invoices |
{tenantId, vendor, createdAt: -1} |
vendor invoice list |
invoices |
{tenantId, status, createdAt: -1} |
CSM approvals queue |
invoices |
{tenantId, paid, paidAt} |
vendor revenue windows |
lead_expert_matches |
{tenantId, expertId, wasExcluded} |
expert bid-pool view |
Drop — clear redundancy only, each a prefix or direction-twin of an index that stays:
products {tenantId, platform} — prefix of the {tenantId, platform, name} unique.csmportfoliosnapshots {tenantId, csm, snapshotDate: -1} — direction-twin of the
{tenantId, csm, snapshotDate} unique, which serves the descending walk equally well.expert_evidence single-field {tenantId} — added by tenantPlugin only because the schema
never declares the tenantId path (plugins/tenant.ts:53-59). Declaring tenantId explicitly,
as every sibling model does, stops the plugin adding it; three compound indexes already lead with
tenantId.users clerkUserId_1 — conditional. The audit calls it a prefix of the partial Clerk unique
{clerkUserId, tenantId, role}, but that unique is partial on {clerkUserId: {$type: "string"}}
and MongoDB only uses a partial index when the query provably implies its filter. Build must
confirm with explain() that the partial unique actually serves both live shapes —
findByClerkUserId ({clerkUserId, tenantId}, services/users/index.ts:215) and the
cross-tenant profile mirror ({clerkUserId} alone, services/users/index.ts:453). Proven → drop
it. Not proven → leave it in place and record why in the build notes; the rest of the migration
is unaffected.Auditor blind spot — db/audit/index.ts stops treating a partial index as satisfying the
missing-tenant-index check, so a collection whose only tenantId-leading index is partial is
reported rather than silently passing.
products {tenantId, platform},
csmportfoliosnapshots {tenantId, csm, snapshotDate: -1}, expert_evidence {tenantId}.expert-evidence.ts declares the tenantId path explicitly, matching its sibling models, so
tenantPlugin no longer adds an indexed one.users.clerkUserId_1 is dropped only if an explain() on both query shapes shows the partial
Clerk unique serving them; otherwise it is retained and the reason is recorded in the build
notes..index() declaration this run adds or removes has a
matching create/drop in the migration, and the migration adds or removes nothing a schema does
not declare.down is symmetric — it drops exactly what up added and recreates exactly what up
dropped, and tolerates an already-absent index so a partial re-run converges.db:audit no longer counts a partial index as satisfying missing-tenant-index, and reports
leads as covered on the strength of the new {tenantId, status, updatedAt: -1} index rather
than the partial {tenantId, requestId} one._source/audit-report.md finding #15) — the nightly
$indexStats window is too weak to act on; deferred to a longer window per D1. This run drops
nothing from that list.activities' redundant single-field leadId / userId indexes — they ride with the activity
field removals in remove-dead-misc-schema, so one model isn't edited by two PRs.lead's parallel stage booleans (bidPool, isActiveProject, isBrdApproved,
isRejected) into the workflow engine — load-bearing, needs its own scope.$exists:false partial uniques — that's stub 1,
fix-broken-partial-uniques.03_build/output/notes.mda36182c (models + migration), 7d658a4 (auditor blind spot + test),
a441cd9 (tolerate a missing collection when dropping), 8b11d56 (test formatting)8b11d56: all green — Quality, Migrate preview database, Audit database, advisoriesdb/models/lead.ts: four tenant-leading indexes — {tenantId, status, updatedAt: -1},
{tenantId, isActiveProject, csm}, {tenantId, createdAt: -1}, and {tenantId, bidDeadline}
partial on {bidPool: true}. The partial filter keeps the bid-pool index proportional to the
pool rather than the whole collection.db/models/quote.ts: {tenantId, proposal} for the accept-bid and invoice-creation paths.db/models/invoice.ts: {tenantId, vendor, createdAt: -1}, {tenantId, status, createdAt: -1},
{tenantId, paid, paidAt}. The vendor one gives vendors the twin customer and expert already had.db/models/lead-expert-match.ts: {tenantId, expertId, wasExcluded} — both existing indexes lead
with leadId after the tenant, so the expert-side read scanned.db/models/product.ts: dropped {tenantId, platform}, a strict prefix of the unique.db/models/csm-portfolio-snapshot.ts: dropped the snapshotDate: -1 twin; reversing a whole
compound key is the same index walked backwards, so the unique serves the descending delta read.db/models/expert-evidence.ts: declares tenantId explicitly, like every sibling model, so
tenantPlugin stops adding an index: true path. The redundant {tenantId} index is dropped in
the migration.db/migrations/1786665600000-hot-path-tenant-indexes.ts: the whole change as one migration, with
ADDED / DROPPED spec tables driving both directions.db/audit/index.ts: extracts isGeneralTenantIndex, which additionally requires the index not be
partial. Plus index.test.ts asserting the predicate from the acceptance criterion.ADDED table, key order and partial filter as specified.products {tenantId, platform},
csmportfoliosnapshots {tenantId, csm, snapshotDate: -1}, expert_evidence {tenantId} — in
the migration's DROPPED table.expert-evidence.ts declares the tenantId path explicitly, matching its siblings.users.clerkUserId_1 retained, per the spec's conditional — see below.ADDED; the three removed
declarations map 1:1 to DROPPED; nothing in the migration is undeclared.down is symmetric — it drops exactly ADDED and recreates exactly DROPPED with the same
keys and options. Both directions swallow "index not found" and "ns not found" so a
partial re-run converges, and createIndex is idempotent for an identical key.db:audit no longer counts a partial index as satisfying missing-tenant-index; leads is
reported as covered on the new {tenantId, status, updatedAt: -1} rather than the partial
{tenantId, requestId}. Asserted in db/audit/index.test.ts.8b11d56 after the NamespaceNotFound fix (it failed on the first attempt; see below).users.clerkUserId_1 was not dropped, and this is the spec's stated fallback, not an
oversight. The criterion made the drop conditional on an explain() proving the partial Clerk
unique serves both live shapes. The agent sandbox has no route to MongoDB (TCP 27017 blocked), so
that measurement could not be taken — and the drop is exactly the kind that turns an auth-adjacent
lookup into a collection scan if the assumption is wrong. Retained, with the reasoning recorded in
the migration header. If someone wants it dropped, run the two explain()s against a populated
database first; it is a one-line addition to DROPPED.dropIndex on
csmportfoliosnapshots threw NamespaceNotFound — preview is seeded per storyline, so that
collection does not exist there yet, and my dropIfExists only swallowed IndexNotFound. Widened
to tolerate a missing namespace too: nothing to drop is success either way, which is what the
"converges on a partial re-run" criterion actually requires. Editing the migration in place is
correct here rather than fix-forward — it never completed, so ts-migrate-mongoose never recorded
it as applied, and it is unmerged so has never touched production.…_sustentus-preview.csmportfoliosnapshots, so Mongoose's default pluralisation is what the
migration should target.ADDED loop before throwing, so preview already
carries the nine new indexes and possibly the products drop. Re-running converges:
createIndex is idempotent and both drops are now tolerant.leads will take real time against production volume.04_verify/output/verify.mdcomplexity: standard) — 3 findings, 1 of them a real defect in a
fix I had just made. Fixed by reverting; see Findings.packages/services/src/db/** (index declarations, one migration, one audit predicate) plus
.icm/**. The nearest adjacency is tenant isolation, and that is unchanged: tenantPlugin's
query injection is untouched, and isGeneralTenantIndex only makes the detector stricter.This run ships no UI and no route. Every acceptance criterion is a database or static-analysis fact, so the evidence is CI against the preview database rather than a browser walkthrough.
8b11d56, running this exact migration against sustentus-preview (agent, via CI)NamespaceNotFound on
csmportfoliosnapshots that proved the drop path is real was fixed in a441cd9 and the job
then passed (agent, via CI)expert-evidence.ts declares tenantId explicitly — read at expert-evidence.ts:71,
inside the schema literal so schema.path("tenantId") is truthy before .plugin(tenantPlugin)
at :114; readiness confirmed the other six models already declare it (agent)users.clerkUserId_1 retained under the spec's conditional — no explain() possible without
a database; reasoning recorded in the migration header (agent)ADDED and 3 DROPPED entries map 1:1
to schema declarations, keys, directions and partial filters (agent)down symmetric and re-runnable — read; both directions tolerate IndexNotFound and
NamespaceNotFound (agent)db:audit no longer counts a partial index as tenant cover — Audit database green, and
db/audit/index.test.ts asserts the predicate from the criterion; the Run tests step of
Quality is green (agent, via CI)users is referenced only in the migration
header's reasoning; no user.ts change, no index added or dropped on users (agent)notifyX call and no notification path (agent)Operator-demonstrated: none required. There is no signed-in surface, no persona-facing change and no notification in this diff, so there is nothing the operator could demonstrate that CI has not already proven against the preview database. Flagging this explicitly rather than leaving blank lines that look skipped.
No walkthrough clip. The Ship-stage clip is for owner-facing change; this run has no visible surface. Its effect is latency on existing screens, which a clip cannot show.
{tenantId, bidDeadline, updatedAt: -1} in both
the model and the migration. That was wrong: unlike the earlier NamespaceNotFound edit (where
the migration had failed and was never recorded), this migration succeeded on 8b11d56, so
ts-migrate-mongoose has it recorded and the edited up would never re-run — leaving preview
with an orphaned tenantId_1_bidDeadline_1 that the new down no longer drops. Reverted in
9271606; index keys are byte-identical to the version CI validated. The widening is a
follow-up.leads indexes do — fixed on branch (9271606, comments
only). {tenantId, bidDeadline} bounds the bid pool but does not supply its sort
({bidDeadline: 1, updatedAt: -1} — services/leads/index.ts:381); {tenantId, status, updatedAt: -1} covers the kanban and the status-filtered table but not the unfiltered list,
which has no status predicate (:176-186); {tenantId, createdAt: -1} is not a sort key
anywhere. All three still improve on the pre-branch state — the comments were wrong, not the
indexes.{tenantId, bidDeadline, updatedAt: -1} needs its own migration for the reason above. Extra payoff when done: the pool
read is an unpaginated .select("_id") over every pooled lead.{tenantId, updatedAt: -1}, which is not in the approved Add table. Follow-up.isGeneralTenantIndex does not consider sparse: true — accepted, note. Same class of blind
spot as the partial one just fixed. Benign today: the only sparse tenant-leading index is
quotes {tenantId, quoteId}, and a compound sparse index covers a document when any key exists,
so with tenantId required it can serve a general read. Follow-up, one line.autoIndex is on for every model but user — note, pre-existing. The web app's own
connection will build the added indexes on first request after deploy, ahead of the
approval-gated migrate-production job, so the ordering the migration job implies is not real.
MongoDB 4.2+ hybrid builds don't block readers or writers and createIndex is idempotent, so the
outcome is benign — but this is where the "index builds on leads take real time" cost is
actually paid. House pattern; not changed here.db:migrate down — after down alone,
expert_evidence would carry a {tenantId: 1} index that expert-evidence.ts no longer
declares. Worth stating in the PR's deploy note.down is not a pure inverse on a fresh database — createIndex in down has no
missing-namespace tolerance, so rolling back where csmportfoliosnapshots never existed creates
the empty collection. Harmless; not worth a change.leads {tenantId, bidDeadline} → + updatedAt: -1 (new migration), and decide whether
the unfiltered leads list earns {tenantId, updatedAt: -1}.isGeneralTenantIndex — treat sparse: true like partialFilterExpression.05_ship/output/investor-update.mdWho it's for: Admin, and every persona whose dashboard reads leads What shipped: Nine tenant-leading indexes across leads, quotes, invoices and expert matches, plus the migration that builds them. Why it matters: Data-layer groundwork for Refine the Bridge — Establish Product-Market Fit with Vendor Partners.
The audit now rejects a partial index as tenant cover, closing the blind spot.
Dig deeper: https://github.com/sustentus/sustentus/pull/809
05_ship/output/release.md7c7732d — Quality, Migrate preview database, Audit database and
all advisories. Two failures earlier in the run, both fixed on branch: the migration threw
NamespaceNotFound dropping an index on a collection preview has never created (a441cd9), and
Prettier flagged two new files because Husky's pre-commit formatting does not run in the agent
environment (a441cd9, 8b11d56).apps/docs/app/technical/development/database/page.mdx — the Auditing section now
states that a partial index does not count as tenant cover, and whyship-note.yaml fires on the merge and mails
05_ship/output/investor-update.md to #product-updateexpert-evidence.ts declares tenantId explicitly — expert-evidence.ts:71, inside the
schema literal so tenantPlugin no longer adds an indexed pathusers.clerkUserId_1 handled under the spec's conditional — retained, because the
explain() the criterion requires needs a database the agent environment cannot reach;
reasoning recorded in the migration headerADDED and 3 DROPPED entries map 1:1 to schema declarations;
re-verified after the Verify-stage revertdown symmetric and re-runnable — tolerates IndexNotFound and NamespaceNotFounddb:audit no longer counts a partial index as tenant cover — asserted in
db/audit/index.test.ts; Audit database and the Run tests step of Quality greenautoIndex is on for every model except user. The web app's own connection builds the added
indexes on first request after deploy, ahead of the approval-gated migrate-production job — so
the ordering that job implies is not real. Benign (createIndex is idempotent, and MongoDB's
hybrid builds block neither readers nor writers), but this is where the index-build time on
leads is actually spent. Pre-existing house pattern, not introduced here.db:migrate down. After down alone,
expert_evidence would carry a {tenantId: 1} index that expert-evidence.ts no longer
declares.leads {tenantId, bidDeadline} to include updatedAt: -1 — needs its own migration,
because this one has already run on preview and an edited up would never re-run. Decide at the
same time whether the unfiltered leads list earns {tenantId, updatedAt: -1}.isGeneralTenantIndex — treat sparse: true like partialFilterExpression.