Run: taxonomy-normalization

• branch: claude/pipeline-db-migration-mxflgc
• pr: #487

Spec: Normalize the non-lead taxonomy entities (service, product, industry, skill-category)

• slug: taxonomy-normalization
• personas: admin, csm, sdm, expert, customer
• touches: packages/services/src/db/{models,migrations,services,plugins,seed}, apps/web/app/(app)/{service-leads,projects,products,services,skills,admin/industries}, apps/web/components/{services,products,admin,skills,service-leads,proposals}
• complexity: complex

Problem

The platform's non-lead-scoped reference taxonomy — service, product, industry, category, skill — has drifted into an inconsistent state that undermines lead capture, expert matching, and the BRD agent (the heart of Refine the Bridge: improving the quality of the existing flow). The same concept is modelled three different ways, so data cannot be trusted or joined reliably:

• lead.category is a misnomer — it stores the selected service as a free-text name string (service-leads/create/actions.ts:56 sets category: data.service), even though a tenant-scoped service collection already populates the picker. There is no real foreign key from a lead to its service.
• Industry is represented three ways — lead.industry (string), customer.industry (string[]), and expert.industries (ObjectId[] → industry collection). The same label cannot be matched or rolled up across leads, customers, and experts.
• The category collection groups skills, colliding semantically with the service stored in lead.category — two unrelated meanings of "category".
• service/product/industry are near-identical thin models (and near-identical copy-paste CRUD service classes) that silently diverge from category/skill: they default isActive: false (new rows created hidden) and apply no name normalisation/validation, where the rich models default isActive: true with lowercase/trim/length rules.

Every one of these entities has live admin CRUD in apps/web and is read by lead forms, expert profiles, and the matcher, so the inconsistency is load-bearing, not cosmetic. This run brings the whole cluster to one consistent, normalised, tenant-aware shape — fixing both the schema going forward and the existing data — as the first pass of a wider database clean-up. Reference data is tenant-scoped (tenantId + tenantPlugin) and names are unique only per tenant, so all data fixes must resolve and create rows within each lead/customer/expert's own tenant.

Proposed change

Bring the five taxonomy entities to a single normalised model and migrate the existing data to match, shipped as one full vertical (models + migration + service layer + app UI) so nothing breaks on merge.

Schema / model changes

1. Service → real reference. Add lead.service as an ObjectId ref to the service collection. Remove the misnamed lead.category string field and update the lead text index so it no longer references the dropped field.
2. Industry → ObjectId everywhere. Convert lead.industry to an ObjectId ref to industry, and customer.industry to ObjectId[] refs to industry (matching the already-correct expert.industries).
3. Rename the skill-grouping category → skillCategory. Rename the model/collection, the skill.category ref (→ skill.skillCategory, ref skillCategory), and the admin CRUD surface (routes/labels) so "category" stops colliding with the service concept.
4. Dedupe + align the thin taxonomy models. Extract a shared taxonomy schema/factory for service/product/industry (kept as separate collections), with isActive defaulting to true for new rows, name normalisation (lowercase/trim) + length validation, and a {tenantId, name} unique index aligned with category/skill. Collapse the duplicated CRUD logic in ServiceService/ProductService/IndustryService accordingly.

Data migration (versioned, tenant-aware, idempotent — ts-migrate-mongoose, via connection.collection(...))

5. Backfill lead.category (string) → lead.service (ObjectId) by resolving the label against the lead's own tenant's service collection (case-insensitive), creating missing services per tenant, then $unset lead.category.
6. Backfill lead.industry (string) → ObjectId ref, and customer.industry (string[]) → ObjectId[], resolving/creating industry rows per tenant.
7. Rename the category collection → skillCategory (refs are _id-based, so skill documents need no rewrite beyond the field key).
8. Leave every existing row's isActive untouched — the new default applies to new rows only.
9. Migrations are idempotent (a second up run is a no-op); down is symmetric where feasible and a documented no-op where a field/collection drop is irreversible (the established convention in migrations/1781913600000-status-to-string-keys.ts).

App / service-layer wiring (so apps keep working)

10. Lead-create and project-create flows submit and store lead.service as an ObjectId (the service dropdown already loads from serviceService). Industry pickers in lead/customer flows read/write ObjectId refs.
11. The matcher/AI scoring (score-expert-fit, currently reading the lead.category string) resolves the human-readable service and industry names from the new refs so prompt quality is preserved.
12. The skill-category admin surface is renamed end-to-end (service methods, routes, components, labels).

Acceptance criteria

• lead.category (string) is removed; leads carry lead.service as an ObjectId ref to service, and the lead text index no longer references category.
• lead.industry is an ObjectId ref to industry; customer.industry is ObjectId[] refs to industry; expert.industries is unchanged.
• The skill-grouping collection/model and the skill ref are renamed to skillCategory, and the admin CRUD surface for it is renamed consistently (no remaining "category" naming for the skill grouping).
• service/product/industry share one taxonomy schema definition, default isActive: true for newly created rows, and normalise/validate name (lowercase, trim, length) like category/skill; their CRUD service classes no longer duplicate identical logic.
• A versioned migration backfills, per tenant, lead.category→lead.service, lead.industry→ref, and customer.industry→refs, creating any missing service/industry rows within the correct tenant; no string labels remain in those fields afterwards.
• The migration is idempotent: running pnpm db:migrate up a second time on a migrated database makes no further changes; down behaves per the documented one-way/symmetric convention.
• Existing rows' isActive values are unchanged by the migration.
• Lead-create, project-create, industry pickers, the skill-category admin screens, and the expert matcher all function against the new shapes (service/industry names still reach the AI prompt).
• pnpm typecheck, pnpm lint, and pnpm build pass; no code reads lead.category or treats lead.industry/customer.industry as strings.

Out of scope

• The other non-lead reference data (location, sla-definition, tenant-setting, action-type, counter, status) — later passes of the wider clean-up.
• Merging service/product/industry into a single polymorphic taxonomy collection (kept as separate collections this pass).
• Reworking expert.skillProficiencies or the matching algorithm itself (beyond resolving names from the new refs).
• apps/dashboards mock data (per conventions, dashboards use local mock data, not the live DB).
• Backfilling/repairing historical isActive states (explicitly left untouched).

Open questions

• none. (Non-blocking detail for Build: whether to keep the existing /skills/categories URL path or move it to /skills/skill-categories when renaming the admin surface — default to renaming the path to match the new naming; this does not affect data or acceptance.) </content> </invoke>

Build notes: taxonomy-normalization

• commits:
  • feat: taxonomy-normalization — models, migration, service layer
  • feat: taxonomy-normalization — wire web app to service/industry refs
  • feat: taxonomy-normalization — rename skill-category surface in web app
  • fix: taxonomy-normalization — resolve typecheck errors

What changed

Data layer (packages/services)

• New shared taxonomy schema — db/models/taxonomy.ts (createTaxonomySchema + ITaxonomy): one definition for the thin tenant-scoped taxonomies. service/product/industry now derive from it — isActive defaults to true, name is lowercased/trimmed/length-validated, and the {tenantId, name} unique index matches skill/skillCategory.
• Skill grouping renamed — category.ts → skill-category.ts (model skillCategory, collection skillcategories). skill.category → skill.skillCategory (ref + index).
• Lead — dropped the misnamed category string; added service (ref service) and converted industry to a ref (industry). Text index moved off category onto {title, description, requirements}. ILead/ILeadPopulated updated (service/industry now populated { _id, name }).
• Customer — customer.industry is now ObjectId[] refs (expert.industries already was).
• Service classes deduped — TaxonomyService base (services/taxonomy/base.ts) holds the shared CRUD; ServiceService/ProductService/IndustryService are thin subclasses overriding only the archive-reference guard (product/industry guard their FKs). SkillService category methods renamed to *SkillCategory*; categoryId input → skillCategoryId.
• Read paths updated — leads populate (service/industry/skillCategory), bid-pool search resolves service names to ids, sdm/csm dashboards $lookup the service name, proposal populate + IProposalPopulated, matcher prompt (score-expert-fit) now emits service + industry names.

Migration

• db/migrations/1782000000000-taxonomy-normalization.ts — tenant-aware, idempotent, raw-collection: backfills lead.category→lead.service, lead.industry→ref, customer.industry→refs (create missing per tenant), renames categories→skillcategories, $renames skill.category, and drops the legacy category-weighted lead text index. down is a documented no-op (one-way, per the status-migration convention). Existing isActive values are never touched.

Web app (apps/web)

• Lead-create + project-create submit the service id (picker valueSource flipped to id); the CSV import resolves free-text service/industry names to refs (create-if-missing per tenant).
• All lead display reads switched lead.category → lead.service?.name and lead.industry → lead.industry?.name (tables, kanban, bid pools, proposal detail/pages, customer dashboard, BRD).
• Skill-category admin wired to the renamed service methods/types/model.

Acceptance criteria status

• lead.category removed; lead.service ref added; lead text index no longer references category.
• lead.industry is an industry ref; customer.industry is industry[]; expert.industries unchanged.
• Skill grouping model/collection + skill ref renamed to skillCategory; service-layer CRUD surface renamed (findAllSkillCategories, createSkillCategory, …). Note: the /skills/categories URL path and app-local field names (categoryId, server-action names) were intentionally kept — the deferred cosmetic flagged as non-blocking in the spec's open question.
• service/product/industry share one schema (createTaxonomySchema), default isActive: true, validate name; their CRUD classes are deduped onto TaxonomyService.
• Versioned migration backfills per tenant (create-if-missing); no string labels remain after.
• Migration is idempotent (guards on $type:"string" / field existence); down documented no-op.
• Existing rows' isActive untouched by the migration.
• Lead-create, project-create, industry pickers, skill-category admin, and the matcher function on the new shapes (service/industry names reach the AI prompt).
• pnpm typecheck (services + web) and pnpm lint pass locally; no code reads lead.category or treats lead.industry/customer.industry as strings.

Verify result

• @sustentus/services typecheck: clean. @sustentus/web typecheck: clean (after ^build of services — turbo handles this in CI). Lint: 0 errors (18 pre-existing warnings, none from this change). Format/ build run in CI + the Vercel preview.

Notes for review

• Run the migration (pnpm db:migrate up) against each environment after merge — the app expects refs; legacy string lead.category/lead.industry rows render as "—" until migrated.
• Deferred (cosmetic, per spec open question): renaming the /skills/categories route and the app-local categoryId identifiers to skillCategory*. Substantive rename (model, collection, refs, service methods/types) is complete.
• service/product/industry now reject name < 2 or > 50 chars (aligned with skill/category); the CSV import surfaces a per-row error if a service/industry name fails validation.

Changelog: taxonomy-normalization

Live entry: apps/help/app/changelog/2026-06-16-taxonomy-normalization/page.mdx

• title: New services, products and industries appear as soon as you add them
• personas: admin
• pr: https://github.com/sustentus/sustentus/pull/487

Benefit: adding a service / product / industry now makes it active immediately (they were previously created hidden, needing a manual toggle). Existing entries' active state is untouched.

The rest of the change — normalising the reference taxonomy onto real references and migrating the existing data per tenant — is internal data-quality work with no user-visible surface, so it is carried only by the investor update.

A cleaner data foundation under leads and matching

Who it's for: Admins, and the matching, CSM and expert workflows built on this data. What shipped: Services, industries, products and skill categories now use real references instead of free-text labels, migrated per tenant. Why it matters: A trustworthy data foundation under leads and matching — advancing Refine the Bridge's work to validate the technical infrastructure.

New services, products and industries are now active by default — saving admins a manual step.

Dig deeper: <merged-PR URL> · <changelog entry URL>

Release: taxonomy-normalization

• pr: https://github.com/sustentus/sustentus/pull/487 · merged: pending (Ready-to-merge ticked; merging on green CI)
• CI: green on the build push; re-checking after the release commit
• technical docs: no technical docs impact — internal data-model normalization; the "Manage skill categories" feature description in technical/demo-environment stays accurate (UI concept unchanged)
• business docs: no business docs impact — user-facing flows (managing services/products/industries/skills/categories, creating leads) are unchanged in behaviour; feature-role-matrix/service-journey don't describe internal storage
• release notes: both — changelog entry (apps/help/app/changelog/2026-06-16-taxonomy-normalization) + investor draft in this PR
• deploy: pending (checked post-merge via Vercel MCP)
• sent: pending (after green production deploy)

Review summary

Ran two recall-biased finder passes over main...HEAD (data layer + migration; app wiring).

• CSV import attributed an industry-name validation error to the category field — resolved on branch: split the resolve into separate try/catch blocks so a bad industry name reports under industry.
• Activity log records the raw CSV category text rather than the canonical service name — accepted: the raw text is what the admin typed and is acceptable for the audit line; not worth the extra lookup.
• service/product/industry unique index is now non-sparse (was sparse) — accepted/monitored risk: alignment with skill/skillCategory per spec. Real-world rows are admin-CRUD-created with a required name, so null/empty names are not expected; a failed autoIndex build is logged on the connection, not thrown, so it would not crash the app. Flagged for the post-deploy DB audit.
• Idempotency guard { category: { $type: "string" } } — verified correct: legacy lead.category was a required String, so non-string values cannot exist; re-runs match nothing.
• App wiring (pickers submit ids, all lead.service?.name/lead.industry?.name reads, populate coverage incl. proposal nested populate, full skill-category rename) — verified correct.

Acceptance check (vs spec)

• lead.category removed; lead.service ref; lead text index off category — model + migration.
• lead.industry/customer.industry are industry refs; expert.industries unchanged.
• Skill grouping renamed to skillCategory (model/collection/ref + service-layer surface); URL path + local categoryId kept (deferred cosmetic per spec open question).
• service/product/industry share createTaxonomySchema (active default, validated name); CRUD deduped onto TaxonomyService.
• Migration backfills per tenant create-if-missing; no string labels remain after.
• Migration idempotent; down documented no-op.
• Existing isActive untouched by the migration.
• Lead/project create, industry pickers, skill-category admin, matcher work on the new shapes; service/industry names reach the AI prompt.
• pnpm typecheck (services + web) + pnpm lint clean locally; no lead.category/string-industry reads remain.