fix-broken-partial-uniquesrun.md02_define/output/spec.mdexpert-rating and tenant-integration each declare a unique index whose
partialFilterExpression is { $or: [{ isDeleted: false }, { isDeleted: { $exists: false } }] }.
MongoDB rejects $exists: false inside a partial index, so neither index has ever built — and
because Mongoose autoIndex failures are asynchronous and unlogged, both failed silently. The two
uniqueness rules they exist to enforce — one rating per (tenant, lead, vendor) and one
integration per (tenant, catalogueKey) — are therefore unenforced at the database, resting
entirely on application-level upserts that a race or a seeder write can defeat. The live evidence
agrees: the nightly prod audit flags tenant_integrations as missing-tenant-index precisely
because its tenant-leading unique never built (audit report finding #2, corroborated by §1.1).
The repo already diagnosed this exact bug on csat and fixed only CSAT (csat.ts:60-71, migration
1782500000000-add-csats-tenant-index.ts:31-35); these two were missed. This is stub 1 of the
demo-data-quality epic (decision D1) — the foundation-first correctness fix that the demo tenant's
data-quality work sits on. Duplicate ratings or duplicate integrations in the demo tenant are
exactly the kind of visible data defect that undermines a vendor walkthrough, which is what
Q2-2026 Objective 1 — Establish Product-Market Fit with Vendor Partners (initiative: Refine
the bridge) depends on.
Rewrite both partial filters to the proven, buildable { isDeleted: false } form in the two
schemas — the CSAT fix pattern — and ship one migration that makes the live databases match: first
close the gap that makes { isDeleted: false } unsafe on legacy rows, then resolve any pre-existing
duplicates deterministically, then build the two indexes.
The { isDeleted: false } filter is not a free swap. softDeletePlugin treats a missing
isDeleted field as live (its pre(/^find/) hook matches isDeleted: false or absent), but a
{ isDeleted: false } partial index does not cover a document lacking the field. Any legacy row
without isDeleted would be live to the application yet invisible to the index — a duplicate could
still slip through. The migration therefore backfills isDeleted: false onto documents missing the
field in these two collections before building anything. That backfill is scoped to these two
collections only; the generic version of the problem across other models stays out of scope.
Duplicate resolution (agreed shape): keep the newest row per key — createdAt descending,
tie-broken by _id descending — and soft-delete the losers by setting isDeleted: true, logging
every resolved _id. Nothing is hard-deleted and nothing is dropped silently.
down reverses the index change truthfully — it drops the two indexes, returning the collections to
their real pre-migration state, in which neither index existed. It does not resurrect the
soft-deleted losers or strip the backfilled field: softDeletePlugin records no deletion timestamp,
so a rollback cannot distinguish a migration-resolved loser from a genuinely archived row. down
logs loudly what it is not reversing, and why, rather than silently no-opping or throwing (throwing
would block a rollback that is otherwise legitimate).
Order within up, which matters: backfill → deduplicate → createIndex. The backfill widens the
set the index will cover, so dedup must run after it.
expert-rating.ts declares the unique index { tenantId: 1, lead: 1, vendor: 1 } with
partialFilterExpression: { isDeleted: false }, and tenant-integration.ts declares
{ tenantId: 1, catalogueKey: 1 } the same way — no $exists: false remains in either.
Both carry a comment explaining why the $or … $exists:false form is invalid, matching the
precedent set in csat.ts.packages/services/src/db/migrations/ builds both indexes on the
live collections, and its up completes green against preview — proving the filters are
buildable, which is the whole point of the fix.up backfills isDeleted: false onto documents in the two collections that lack the field,
before creating either index, and reports how many it touched.up resolves pre-existing duplicates per unique key by keeping the newest row (createdAt
desc, tie-break _id desc) and setting isDeleted: true on the losers, logging each resolved
_id and the key it collided on. No hard deletes.up is idempotent: a second run backfills nothing, resolves nothing, and creates nothing
(identical key + options make createIndex a no-op).down drops the two indexes, tolerating an already-absent index so a partial re-run
converges, and logs — with counts — the backfill and duplicate resolution it is deliberately
not reversing, and why.tenant-integration model pins collection: "tenant_integrations", while expert-rating
takes Mongoose's default pluralisation of its registered name expertRating. Build confirms
both against the live collection listing before the migration is considered done._id breaks a createdAt tie,
losers are soft-deleted not removed — and the missing-isDeleted backfill.onboarding-blueprint
and sla-definition carry { isDeleted: false } partial uniques that miss raw-driver documents
lacking the field (audit report finding #14). Same class of bug, different models; this run fixes
only the two collections it touches.hot-path-tenant-indexes); redundant-index pruning is deferred pending a longer
$indexStats window (D1).expertRatingService.upsertForLead,
tenantIntegrationService). The database gains the guarantee the code already assumes; the code
itself does not change.assertTransition enforcement and the other audit findings carried by sibling stubs.Context budget: within band. Read the stub, the epic breakdown, _source/decisions.md and finding
#2 of _source/audit-report.md (this scope has no scope.md — the breakdown records _source/ as
its documented equivalent), plus targeted reads of the two models, csat.ts, the CSAT migration,
soft-delete.ts and the two write paths to pin exact behaviour.
03_build/output/notes.mdfix: … build the two partial unique indexes that never built ·
fix: … match collection names in memory, not via $in ·
fix: … replace a conflicting same-named index, and format the test filepackages/services/src/db/models/expert-rating.ts: unique {tenantId, lead, vendor} partial
filter rewritten from the unbuildable $or … $exists:false form to { isDeleted: false }, with
a comment explaining why the old form is invalid and cross-referencing the migration.packages/services/src/db/models/tenant-integration.ts: same rewrite for unique
{tenantId, catalogueKey}, keeping the existing note about why soft-deleted rows must vacate the
unique slot, and adding the audit's missing-tenant-index corroboration.packages/services/src/db/partial-unique-dedupe.ts (new): the rules the migration applies —
LIVE_FILTER (softDeletePlugin's definition of live, verbatim), IS_DELETED_BACKFILL_FILTER,
and resolveDuplicatesKeepNewest (newest by createdAt, tie-broken by _id descending).packages/services/src/db/partial-unique-dedupe.test.ts (new): unit tests for both rules,
written from the spec's acceptance criteria.packages/services/src/db/migrations/1786752000000-fix-partial-unique-indexes.ts (new):
per collection, deduplicate → backfill → ensure-index, with a down that drops both indexes.
(Renumbered from 1786665600000 at Verify — see 04_verify/output/verify.md.)The spec requires unit tests for the duplicate rule, but only the unit tier exists (node, no DB —
CONVENTIONS.md → Testing), so the rule has to be pure and importable to be testable at all. It
could not be co-located inside src/db/migrations/: that path is handed to ts-migrate-mongoose
as migrationsPath, and this sandbox has no node_modules, so I could not confirm the runner
ignores a .test.ts sitting there. A file it mistook for a migration would be a live hazard, so
the helper sits at src/db/partial-unique-dedupe.ts — outside the scanned path, and outside the
public /shared entrypoint. Both the helper and its consumer carry a note that the behaviour is
frozen because a shipped migration depends on it.
resolveCollection reads db.listCollections() at run time, uses whichever
spelling exists, throws if two candidate spellings both exist (real ambiguity about which
one the app writes to), and falls back to the canonical name only when the collection does not
exist yet — the fresh-database case, where createIndex creates it. Candidates are
tenant_integrations (pinned by the model's collection: option) and expertratings
(Mongoose's default pluralisation of the registered name expertRating), each with one
plausible alias.Both were real defects in the migration, found only by running it against a live Atlas cluster — neither is reproducible from the sandbox.
listCollections cannot take a $in filter on Atlas. name may only be a plain string or a
regex; anything else fails with can't get regex from filter doc not a regex. The migration now
lists every collection and matches the candidates in memory. The failure hit before any write, so
nothing partially applied and the migration stayed down.tenant_integrations already carries a conflicting index — and it is the wrong one. Preview
has tenantId_1_catalogueKey_1 as a plain unique index with no partialFilterExpression.
Because Mongo auto-names an index from its key, creating the partial version collided by name
(IndexKeySpecsConflict) rather than being accepted. This is a finding, not just an obstacle:
that index is stricter than the model intends — soft-deleted rows keep occupying the unique
slot, so the remove/re-add loop the model's own comment describes is currently broken on preview.
ensureIndex now replaces a same-named index whose spec differs, logging the outgoing spec, and
leaves a matching one alone (which is what preserves idempotency).Also confirmed by round 2: expertratings is the right collection name (Mongoose's default
pluralisation), and its index built cleanly — the collection did not exist on preview and was
created. So the sandbox-derived name was correct, but the run is what proved it.
partialFilterExpression: { isDeleted: false }; no $exists: false
remains in either, and both carry the explanatory comment on the csat.ts precedent.up completes green against preview — confirmed:
db-migrate.yaml's migrate-preview job passed on 8065650 after the two fixes below.
That green run is the whole proof of the fix, since the old filter was unbuildable.up backfills isDeleted: false before creating either index and logs the modified count.up keeps the newest row per key (createdAt desc, _id desc tie-break), sets
isDeleted: true on the losers, and logs every resolved _id with the key it collided on.
No hard deletes anywhere in the migration.up is idempotent — the backfill filter matches nothing on a second run, no key collides
once the index exists, and createIndex is a no-op for an identical key + options.down drops both indexes via dropIfExists (tolerant of an absent index) and logs, with a
count, the backfill and duplicate resolution it deliberately does not reverse, and why.resolveCollection confirms at run time, and the preview run has now
exercised both paths: tenant_integrations resolved to the existing collection,
expertratings was correctly identified as absent and created._id tie-break, losers soft-deleted rather than removed,
and the missing-isDeleted backfill (including that absent ≠ false ≠ true).createIndex returned" is the evidence. Read the
migrate-preview job, not just Quality.up can legitimately fail on a duplicate the dedup missed. The dedup groups on
{ isDeleted: false } after the backfill, so the two steps have to agree on what "live" means.
If createIndex ever raises E11000, that disagreement is the thing to look at.tenant_integrations; prod does not (the nightly audit reports
missing-tenant-index there, i.e. no such index). So prod takes the plain-create path and
preview took the replace path — worth knowing when reading the production migration log.resolveCollection throwing on two matching spellings is deliberate: silently picking one would
leave the other unconstrained, which is the bug being fixed.Context budget: within band.
04_verify/output/verify.mdcomplexity: standard) — 4 findings, all 4 fixed on branch.process.env reads (so nothing owed to
turbo.json → globalEnv). setExpertVendorRatingAction keeps allowedRoles: ["vendor"] +
expert.rate, unchanged.partialFilterExpression: { isDeleted: false }; no $exists: false
remains — read from the diff, expert-rating.ts / tenant-integration.ts (agent)up completes green against preview — green on 8065650
(logged built unique tenantId_1_lead_1_vendor_1 on expertratings) and re-run green twice
on ff7a0de after the Verify-stage reorder and renumber, runs 31803603640 and
31804432385 (agent)up backfills isDeleted: false before creating either index and reports the count —
preview logged backfilled isDeleted:false on 0 row(s) for both collections (agent)up keeps the newest row per key and soft-deletes losers, logging each — preview logged
resolved 0 duplicate row(s) across 0 colliding key(s); the rule is covered by unit tests,
the path has not met real duplicates (agent)up is idempotent — ensureIndex short-circuits on a matching spec; the backfill filter and
the dedupe group both match nothing on a second pass (agent, by inspection)down drops both indexes tolerantly and logs what it does not reverse (agent, by inspection)tenant_integrations found, expertratings correctly identified as absent and created (agent)_id tie-break, losers soft-deleted not removed, and the
backfill/live filters — Quality Project green on 8065650 (agent)notifyX call and touches no notification
surface (agent)Blocking, fixed — up step order could wedge the production migration.
connectDB does not set autoIndex: false, and on merge Vercel deploys immediately while
migrate-production waits behind the production environment's reviewer gate. So the app can build
the now-buildable partial index before the migration runs. With backfill-first, backfilling legacy
rows into that live unique index raises E11000 mid-updateMany and aborts before the dedupe
that would fix it — failing identically on every re-run, unrecoverably. Fixed by deduping first,
over softDeletePlugin's own definition of live (commit ff7a0de).
Blocking-adjacent, fixed — the corrected migration had never executed anywhere.
The original id had already applied on preview, so editing it in place would have shipped a
production migration that no environment had ever run. Renumbered 1786665600000 →
1786752000000; the runner's prune() drops the stale record and runs the corrected file. A second
migration file would have been wrong — the buggy one must never reach prod, and prod has not applied
it.
Fixed — $ne: true was wider than "live". softDeletePlugin matches false or absent, not
null. A null row is dead to the app and outside the index, but the first cut of the dedupe
counted it live, so it could win the survivor slot and archive the genuinely live row. "Live" is now
one exported LIVE_FILTER, copied verbatim from the plugin and shared by the migration and its
tests.
Fixed — dead export. needsIsDeletedBackfill had tests but no caller, so it could drift from
the filter that actually runs. Removed; the two Mongo filters are now the single representation and
both are pinned by tests.
Fixed — rollback honesty. down alone is not a rollback: with autoIndex on, the next boot on
un-reverted code rebuilds both indexes. Documented. down's collection resolution no longer throws
on ambiguity, which had contradicted the file's own stated design that a rollback must not be
blocked. The drop-then-create window in ensureIndex is documented with why it is acceptable
(dedupe has already removed the only realistic cause of a failed create) and what to do if a create
ever does fail.
Handed to stub 2, not fixed here — the new index cannot serve reads. The audit's
missing-tenant-index finding on tenant_integrations will now go green, but only structurally:
the check just asks whether some index is tenantId-leading. softDeletePlugin injects
$or: [{isDeleted:false},{isDeleted:{$exists:false}}] into every query, and MongoDB will not use a
partial index unless the predicate guarantees a subset of the indexed documents — the $exists:false
branch breaks that. So listIntegrations still scans. Adding the real {tenantId, createdAt} index
is barred by this spec's Out of scope (index work beyond the two uniques), so the misleading claim
was struck from the model comment and the finding written up as its own stub. Treat the green
audit finding as a false negative.
It was first handed to the hot-path-tenant-indexes stub — but on merging main that stub turned
out to have already shipped as PR #809, without covering tenant_integrations, which would have
stranded the note in _done/. The finding now lives at
.icm/intake/db-audit-findings/tenant-integrations-read-index.md per decision D2, and carries the
wider question it exposed: the auditor's tenant check accepts any tenantId-leading index, so a
partial one satisfies it without serving reads — the same blind spot already noted for lead's
partial {tenantId, requestId}.
Accepted, not fixed — ensureIndex's spec comparison ignores collation. It compares unique
and partialFilterExpression only, so a same-named index with a non-default collation would read as
correct. The repo has hit collation-vs-autoIndex before, but adding a collation comparison here
risks more than it protects on two collections that have no custom collation. Recorded rather than
changed.
The first migrate-preview attempt on ff7a0de was cancelled after 4 seconds — not failed, and
not passed. db-migrate.yaml puts every branch behind one global db-migrate-preview concurrency
group with cancel-in-progress: false, and a sibling stub (remove-dead-misc-schema, PR #812) was
migrating at the same time; GitHub cancels the older pending entry when a third arrives. Two
manual workflow_dispatch re-runs then completed green, so this run is fully verified — but the
mechanism is worth flagging.
The epic's breakdown claims stubs 1–8 "can run as eight parallel sessions; the shared
preview-migration lock serialises their migrations safely on its own." That is not what the lock
does. With cancel-in-progress: false a pending run is cancelled rather than queued when another
arrives, so parallel sessions can silently lose their preview-migration verification — and a
cancelled conclusion is easy to skim as green, which is exactly the failure mode a Verify stage
exists to catch. Worth fixing in the workflow (queue instead of cancel) or correcting the
breakdown's parallelism claim. Not actioned here: it is factory/epic scope, not this spec's.
main (second Verify pass)main moved on by three merges — #808 remove-dead-lead-fields, #809 hot-path-tenant-indexes,
#811 sla-stage-map-engine-alignment. GitHub reported the PR dirty; the merge in fact resolved
automatically, the conflict being a rename/modify (my handover note against a stub those merges moved
into _done/). Resolved by dropping the note and rehoming the finding, above.
The renumber turned out to be load-bearing for a second reason. main now carries two
migrations on the id I originally used — 1786665600000-hot-path-tenant-indexes.ts and
1786665600000-remove-dead-lead-fields.ts. Had this run kept 1786665600000, it would have been a
three-way collision. At 1786752000000 it is unique, and it sorts last, so it applies after both.
Neither of those migrations touches expertratings or tenant_integrations, so there is no
functional interaction — only the ordering question.
That duplicate id on main is a pre-existing trap for the next migration author. It is not this
run's to fix (both have already applied, and neither is ours), so it is written into the new stub's
Notes for Define rather than silently absorbed here.
Three user-visible effects that belong in the changelog and should be expected in the production migration log rather than read as data loss:
addIntegration's duplicate-key branch has been dead code — no index meant no E11000. It now
fires, so "X is already in this organisation's integrations." becomes reachable for the first
time.getExpertQuality averages shift for any
tenant that had duplicates — expert match scoring changes.Context budget: over band. The contract's Inputs were the spec, notes, diff and github.md; the two review skills reached considerably wider (connection/plugin/audit internals, the workflows, caller sites), which is what surfaced the blocking defect. Recording the overrun as required.
05_ship/output/changelog.mdTwo rules the platform has always described — one integration per catalogue entry, one expert rating per vendor and project — were never actually enforced by the database. Nothing stopped a duplicate being written by a retry, a race, or a bulk import, and once written it sat there alongside the original.
Both rules are now enforced at the database itself, so a duplicate cannot be created in the first place.
Three things to expect, all on the first run:
Removing an integration and adding it back is unaffected — that has always been supported and still is.
05_ship/output/investor-update.mdWho it's for: Admins What shipped: The database now enforces one integration per catalogue entry and one expert rating per vendor and project — rules described but never applied. Why it matters: Clean vendor data underpins Refine the bridge / Q2 Objective 1, Establish Product-Market Fit with Vendor Partners.
Existing duplicates were archived, not deleted.
Dig deeper: https://github.com/sustentus/sustentus/pull/810 · https://help.sustentus.com/changelog/2026-08-17-fix-broken-partial-uniques
05_ship/output/release.md9231500 and again on the ship commit 8959725, where the
help-centre preview also went Ready (which is what proves the changelog .mdx frontmatter
parses). Earlier reds were fixed during Build and Verify (Atlas $in on listCollections,
Prettier, IndexKeySpecsConflict, dedupe ordering).apps/docs/app/technical/** documents
these two models, their indexes, or partial-index behaviour (checked by grep for the model names,
partialFilterExpression and autoIndex).ship-note.yaml sends the note to #product-update on this squash-merge. Not
recorded as sent here: this file is committed by the very squash that triggers the send, so it
can only state that the send is authorised and armed.The gate was ticked on 2026-08-17, three days after the run's other Ship artifacts were written.
The changelog folder and its date were moved from 2026-08-14 to the real merge date, and the
ship note's Dig deeper link with them — otherwise the announced URL would have pointed at a
route that no longer existed.
partialFilterExpression: { isDeleted: false }; no $exists: false
remains — verified in Verify from the diff.up completes green against preview — verified in
Verify; green on 8065650, and again on ff7a0de and 9231500 after the reorder/renumber.up backfills isDeleted: false before creating either index and reports the count —
preview logged backfilled isDeleted:false on 0 row(s) for both collections.up keeps the newest row per key and soft-deletes losers, logging each — rule covered by unit
tests; preview logged resolved 0 duplicate row(s), so the path is unexercised against real
duplicates (carried forward as a known gap, below).up is idempotent — ensureIndex short-circuits on a matching spec; backfill and dedupe both
match nothing on a second pass.down drops both indexes tolerantly and logs what it does not reverse._id tie-break, soft-delete-not-remove, and the
backfill/live filters — Quality Project green.tenant_integrations still has no usable read index, and this change makes the audit's
missing-tenant-index finding go green anyway. Tracked at
.icm/intake/db-audit-findings/tenant-integrations-read-index.md; treat the green finding as a
false negative.main carries two migrations sharing id 1786665600000 (#808 and #809). Not introduced here
— this run renumbered away from it — but flagged in the same new stub.Context budget: within band for this stage.