Run: add-invoices-tenant-index

• branch: claude/invoices-tenant-index-pipeline-z6o4xs
• pr: #492

Stub: add tenant-leading index to invoices

• feature-slug: add-invoices-tenant-index
• epic: db-audit-findings
• finding-key: missing-tenant-index/invoices/tenantId
• personas: platform (admin / operational)
• initiative: database leanness & query efficiency / objective: keep tenant-scoped queries index-backed
• depends-on: none
• sequence: 1 of 1

Problem

The db:audit CI run flagged [missing-tenant-index] invoices: 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 invoices is tenant-scoped. But none of the collection's indexes lead with tenantId — they lead with milestone, status, expert, customer, lead (packages/services/src/db/models/invoice.ts:55-59). The standalone tenantId index the plugin would add only fires when the schema doesn't already declare tenantId; invoice.ts declares it explicitly, so no tenant-leading index exists. Result: tenant-scoped invoice queries can't seek on tenantId and degrade toward collection scans as data grows. This is the audit's number-one priority finding by impact ÷ risk: a core financial collection, certain performance win, additive low-risk fix.

Proposed change

Add tenant-leading index coverage to invoices, shipped as a reviewed forward/back migration (pnpm --filter @sustentus/services db:migrate create add-invoices-tenant-index) — never ad hoc. Prefer folding tenantId into the existing compound indexes following ESR (Equality, Sort, Range) rather than adding a bare { tenantId: 1 }, so the real query shapes are covered:

• { tenantId: 1, status: 1, isApproved: 1 }
• { tenantId: 1, expert: 1, status: 1 }
• { tenantId: 1, customer: 1, status: 1 }
• { tenantId: 1, lead: 1, status: 1 }

Define/Build confirm the exact set against actual query usage (and whether the now-redundant non-tenant-leading compounds should be dropped in the same migration). The { milestone: 1 } unique index stays as-is.

Acceptance criteria (rough)

• A reviewed migration with up/down adds tenant-leading index(es) to invoices and drops any index it makes redundant; down cleanly reverses it.
• invoice.ts schema index(...) declarations match the migration (schema and live DB agree).
• A re-run of db:audit no longer reports missing-tenant-index for invoices.
• Index choices follow ESR and are justified against real invoice query shapes.

Out of scope (this feature)

• The other three missing-tenant-index collections (csats, milestones, statushistories).
• Any unused-index, redundant-index, or orphaned-reference finding (incl. the 2/19 orphaned invoice docs) — separate stubs / decomposition.

Notes for Define

• Confirm the index set against actual repository/query usage before building; don't over-index.
• Read first, drop second: if the migration removes the old non-tenant-leading compounds, verify nothing relies on them (db-auditor + mongodb-query-optimizer skills, ESR guidance).
• touches: packages/services/src/db/models/invoice.ts, a new migration under packages/services.

Spec: add tenant-leading index to invoices

• slug: add-invoices-tenant-index
• personas: platform (admin / operational)
• touches: packages/services/src/db/models/invoice.ts, packages/services/src/db/migrations (new migration)
• complexity: standard

Problem

The db:audit CI run flags its number-one finding by impact ÷ risk: [missing-tenant-index] invoices: 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 on invoices, so every read is tenant-scoped. But the plugin only adds its own { tenantId: 1 } index when the schema doesn't already declare tenantId — and invoice.ts:34 declares it explicitly, so that fallback never fires. The collection's five indexes lead with milestone, status, expert, customer, lead (invoice.ts:55–59); none lead with tenantId. Tenant-scoped invoice queries therefore 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) on a core financial collection — a certain, additive, low-risk performance win.

Proposed change

Add tenant-leading index coverage to invoices, shipped as a reviewed forward/back migration created with pnpm --filter @sustentus/services db:migrate create add-invoices-tenant-index — never ad hoc. Following ESR (Equality, Sort, Range), fold tenantId into the existing compound indexes as the leading equality field rather than adding a bare { tenantId: 1 }, so the real tenant-scoped query shapes are covered:

• { tenantId: 1, status: 1, isApproved: 1 }
• { tenantId: 1, expert: 1, status: 1 }
• { tenantId: 1, customer: 1, status: 1 }
• { tenantId: 1, lead: 1, status: 1 }

The { milestone: 1 } unique index stays as-is (its uniqueness is global, not tenant-scoped). Build confirms the exact set against actual invoice query usage in the codebase before writing the migration, and decides — read first, drop second — whether the now-redundant non-tenant-leading compounds ({ status, isApproved }, { expert, status }, { customer, status }, { lead, status }) should be dropped in the same migration, verifying nothing relies on them. The invoice.ts schema index(...) declarations are updated to match the migration so schema and live DB agree.

Acceptance criteria

• A reviewed migration with up/down adds the tenant-leading index(es) to invoices and drops any index it makes redundant; down cleanly reverses it (re-creating dropped indexes, removing added ones).
• The invoice.ts schema index(...) declarations match the migration exactly, so the schema and the live DB agree.
• Re-running db:audit no longer reports missing-tenant-index for invoices.
• Each chosen index follows ESR and is justified in the migration/PR against a real invoice query shape (no speculative over-indexing).

Out of scope

• The other three missing-tenant-index collections (csats, milestones, statushistories) — separate stubs.
• Any unused-index, redundant-index, or orphaned-reference finding (including the orphaned invoice docs) — separate stubs / decomposition.
• Backfilling, reshaping, or migrating invoice data — this run only touches index definitions.

Open questions

• none. (Whether to drop the redundant non-tenant-leading compounds and the precise final index set are settled in direction here — lead with tenantId, ESR, drop only what is provably redundant — and confirmed by Build against the code; neither blocks an acceptance criterion.)

Build notes: add-invoices-tenant-index

• commits: feat: add-invoices-tenant-index — tenant-leading invoice indexes + migration

What changed

• packages/services/src/db/migrations/1782200000000-add-invoices-tenant-index.ts (new): forward/back migration. up creates four tenant-leading indexes and drops the four superseded non-tenant-leading compounds; down reverses it exactly. Operates through the raw connection.collection("invoices") per the migration convention, with index drops tolerant of an already-absent index so a partial re-run converges.
• packages/services/src/db/models/invoice.ts: replaced the four non-tenant-leading compound index(...) declarations with the four tenant-leading ones, so the schema matches the migration. The { milestone: 1 } unique index is unchanged.

Index decision (ESR, grounded in real query shapes)

Read the actual invoice queries in db/services/invoice/index.ts first. The dominant tenant-scoped shapes are paginated lists sorted by createdAt, plus lead-detail queries — so ESR puts createdAt as the trailing Sort field (not the stub's draft isApproved, which no query filters on).

Index	Serves
{ tenantId, createdAt: -1 }	findAll (tenant invoice list, sort createdAt desc) + countDocuments({ tenantId }) via the tenantId prefix
{ tenantId, customer, createdAt: -1 }	findAll role=customer; the payable-on-lead query (customer in equality)
{ tenantId, expert, createdAt: -1 }	findAll role=expert
{ tenantId, lead, status, createdAt: -1 }	findDraftInvoicesForLeadAndManager + findPayableInvoicesForLeadAndCustomer (E = tenantId, lead, status; S = createdAt)

Dropped (superseded; redundant once every query is tenant-scoped — no query filters on these leading fields without tenantId): { status, isApproved }, { expert, status }, { customer, status }, { lead, status }.

Deliberately not added (avoiding over-indexing — the audit's whole point is leanness):

• { tenantId, vendor, createdAt } — vendor/manager are oversight roles that reach invoices via lead/request detail (covered by the lead index) rather than a flat per-vendor list; the rare vendor-filtered findAll falls back to { tenantId, createdAt } + a residual filter.
• { tenantId, quote } for archiveByQuoteIds — a rare admin/cascade op, served acceptably by the tenantId prefix.
• { tenantId, status, isApproved } (the stub's draft shape) — no query filters on status/isApproved at tenant level without a more selective field, so it would be speculative.

No new index is a prefix of another (checked against the audit's redundant-index rule), and each leads with tenantId (clears missing-tenant-index).

Acceptance criteria status

• Reviewed migration with up/down adds tenant-leading index(es) and drops the redundant compounds; down cleanly reverses it — 1782200000000-add-invoices-tenant-index.ts.
• invoice.ts index(...) declarations match the migration exactly (indexes inherit the collection's default collation in both paths, so identities agree).
• A re-run of db:audit no longer reports missing-tenant-index for invoices — four indexes now lead with tenantId, which is exactly the condition the audit checks (keyFields(index)[0] === "tenantId").
• Index choices follow ESR and are justified above against real invoice query shapes; no speculative over-indexing.

Verify result

• mechanical checks (format · lint · typecheck · build) run in CI + the Vercel preview, not here. Release reads them back from the PR's check runs. The Audit database and Migrate preview database CI jobs additionally exercise this change against a live preview DB.

Notes for review

• The migration drops indexes after creating the replacements, so reads are never left without a tenant-scoped index mid-migration.
• down is a true reversal (not a no-op): it restores the original compounds and removes the new ones, matching the established index-migration symmetry rule.

Invoice queries stay fast as volume grows

Who it's for: Every persona that views invoices (platform-wide) What shipped: Invoice lookups are now backed by tenant-leading database indexes instead of scanning the collection. Why it matters: Keeps payout-flow infrastructure reliable at volume — advancing Scale the Bridge and our Q2 goal to validate technical infrastructure.

Dig deeper: <merged-PR URL>

Release: add-invoices-tenant-index

• pr: #492 (https://github.com/sustentus/sustentus/pull/492) · merged: pending Ready-to-merge tick
• CI: green on the Build commit (Format · Lint · Typecheck · Audit database · Migrate preview database all ✅; Migrate production database skipped on PR)
• technical docs: no technical docs impact — the change adds a migration + index declarations; the DB-lifecycle doc (migrate/seed/audit) is unchanged and no page enumerates per-collection indexes
• business docs: no business docs impact — invoice queries return the same results, just index-backed; no persona-facing behaviour change
• release notes: investor-only — no end-user note (internal performance/infra change). Investor draft in this PR; no changelog entry
• deploy: pending merge
• sent: pending green deploy

Review summary

• /code-review (high effort) found no actionable findings. Confirmed: up creates new indexes before dropping old (no read gap); dropIfExists swallows only IndexNotFound and rethrows otherwise; down is an exact symmetric reversal; schema and migration index identities agree (both inherit the collection's default collation); CONVENTIONS satisfied (arrow fns, type, async/await, named imports).

Acceptance check (vs spec)

• Reviewed migration with up/down adds tenant-leading index(es) and drops the redundant compounds; down reverses it — 1782200000000-add-invoices-tenant-index.ts
• invoice.ts index(...) declarations match the migration exactly
• db:audit no longer reports missing-tenant-index for invoices — four indexes lead with tenantId; the Audit database CI job is green
• Index choices follow ESR and are justified against real invoice query shapes (build notes); no speculative over-indexing