Stub: tenant_integrations still has no usable read index — and the audit says it does

• feature-slug: tenant-integrations-read-index
• scope: db-audit-findings
• personas: Admin
• initiative: Refine the bridge / objective: Q2-2026 Objective 1 — Establish Product-Market Fit with Vendor Partners
• depends-on: none
• sequence: 1 of 1

Problem

Two things are true at once, and the second hides the first.

fix-broken-partial-uniques (PR #810) made the unique {tenantId, catalogueKey} partial index on tenant_integrations build for the first time. The nightly audit's missing-tenant-index finding on that collection will therefore go green — the check is purely structural, asking only whether some secondary index leads with tenantId (packages/services/src/db/audit/index.ts:123-125).

But the read path did not improve. softDeletePlugin injects $or: [{isDeleted:false},{isDeleted:{$exists:false}}] into every query (db/plugins/soft-delete.ts:24-27), and MongoDB will not use a partial index unless the query predicate guarantees a subset of the indexed documents — the $exists:false branch breaks that guarantee. So listIntegrations — find({tenantId}).sort({createdAt:-1}), db/services/tenant-integration/index.ts:39-46 — still does a collection scan with an in-memory sort, and the collection has no other secondary index (tenantPlugin only adds index: true when the schema does not already declare tenantId, and this model does).

Net effect: a real finding is now permanently silenced by an index the query planner will never touch. That is worse than the original state, because the signal is gone.

This was raised during fix-broken-partial-uniques' Verify and handed to the hot-path-tenant-indexes stub — but that stub had already shipped as PR #809 without covering tenant_integrations, so the finding needs its own home. It lands here per decision D2, which routes audit findings outside the demo-data-quality batch to .icm/intake/db-audit-findings/.

Proposed change

Add a non-partial { tenantId: 1, createdAt: -1 } index to tenant-integration.ts with the matching migration, so the admin integrations list is served rather than scanned.

Then decide the wider question the near-miss exposes: should the runtime auditor's tenant check discount partial indexes? As written it accepts any tenantId-leading index, so a partial one satisfies it without serving reads. The same blind spot was already noted for lead's partial {tenantId, requestId} in the original audit — the auditor treats it as satisfying the tenant check too. One fix in db/audit/index.ts covers both.

Acceptance criteria (rough)

• tenant_integrations has a non-partial {tenantId, createdAt: -1} index, declared in the model and built by a migration, in lockstep.
• listIntegrations is served by it — confirmed by an explain() showing an index scan rather than a COLLSCAN with an in-memory sort.
• The auditor's tenant check either discounts partial indexes or documents why it does not, so lead and tenant_integrations stop reporting false negatives.
• Symmetric down.

Out of scope (this feature)

• Re-litigating the unique partial index itself — it is correct for uniqueness and stays as is.
• The "unused index suspects" list, still deferred pending a longer $indexStats window (D1).

Notes for Define

Evidence: fix-broken-partial-uniques Verify record (apps/docs/archive/pipeline-runs/fix-broken-partial-uniques/04_verify/output/verify.md → "Handed to stub 2") and the comment block in db/models/tenant-integration.ts that spells out why the unique index cannot serve reads. Check whether hot-path-tenant-indexes (PR #809) established a house pattern for these indexes and follow it.

Also worth fixing while here (separate, small, unrelated to the index): main currently carries two migrations sharing the id 1786665600000 — 1786665600000-hot-path-tenant-indexes.ts and 1786665600000-remove-dead-lead-fields.ts, landed by #809 and #808 respectively. Two files with the same timestamp prefix leave ts-migrate-mongoose's ordering ambiguous. Both have already applied, so this is a fix-forward/naming question rather than a live outage, but it should not be left as a trap for the next migration author.

touches: packages/services/src/db/models/tenant-integration.ts, packages/services/src/db/migrations/, packages/services/src/db/audit/index.ts. db-migration skill.

Answers from Jamie — interrogation 2026-08-17

Recorded by the intake-easy-features session; these rulings bind Define.

• Migration-id collision (1786665600000 shared by three applied migrations): leave as-is — applied migrations keep their ids; only future migrations get unique prefixes. Not part of the index PR.