Skip to main content

ADR-008: Capability Catalogue Taxonomy — Phase 1A Schema

Date: 2026-05-26
Status: Accepted
Deciders: Engineering
Affected areas: lib/db, artifacts/api-server, Spider scoring engine

Context​

project_slots.requiredSkills text[] and projects.skillsRequired text[] store skill names as free-form strings with no referential integrity to the skills table. This prevents reliable one-to-one matching between people, slots, and company capability goals, and blocks Spider improvements that need durable, typed skill references.

Phase 1A-schema normalises the foundation: governance metadata on skills and certifications, a graph relationship table for skills, a certification-to-skill mapping, a normalised slot skill requirement table (read-only this phase), and a durable backfill report table.

Decisions​

Decision 1 — skillType supplements category, does not replace it​

skills.category uses the existing skill_category enum (frontend, backend, etc.) and is NOT NULL. It is widely used in queries, filtering, and the domain breadth axis of capability scoring. Rather than modifying this column we add a nullable skillType text column for finer-grained taxonomy (e.g. technical, soft, compliance, tool). This allows phased adoption without a breaking migration.

Rationale: Any change to category (type or nullability) would require touching all callers; a new optional column is additive and safe.

Decision 2 — parentSkillId is legacy; skill_relationships is the canonical graph​

skills.parentSkillId is preserved as-is. The new skill_relationships table provides a typed, bi-directional graph (parent_of, child_of, related_to, equivalent_to, prerequisite_of, part_of) that can express multi-parent hierarchies. The backfill utility seeds skill_relationships from parentSkillId (creating symmetric parent_of/child_of pairs) without altering or dropping parentSkillId.

Rationale: Dropping parentSkillId is destructive and out of scope. Applications that still read parentSkillId continue to work unchanged. New code should use skill_relationships.

Decision 3 — slot_skill_requirements is read-only in Phase 1A​

The slot_skill_requirements table is created and populated by the backfill utility, but no API write path is added in this phase. The table exists so downstream queries (Phase 1A-read) can expose normalised skill requirements without changing the Spider or existing routes.

Rationale: Adding write paths requires OpenAPI changes and codegen regeneration (Phase 1A-read). Separating concerns keeps this migration additive and non-breaking.

Decision 4 — project_skill_requirements is deferred to Phase 1B​

Spider does not consume project-level skill requirements; it reads project_slots.requiredSkills. A project-level table would be used only by future reporting/analytics features. Deferring avoids an unnecessary table and migration churn.

Decision 5 — validityMonths is the canonical validity unit​

certifications.validityYears was the original column. Months provide finer granularity (e.g. 18-month certs, 6-month provisional credentials). validityMonths is added as a nullable integer. The backfill sets validityMonths = validityYears * 12 where validityYears IS NOT NULL. validityYears is preserved for backward compatibility.

Decision 6 — Unmatched backfill entries go to skill_requirement_backfill_reports​

Every token processed during backfill (requiredSkills array entries) is written to skill_requirement_backfill_reports with a matchStatus of matched, unmatched, duplicate, or skipped_empty. This creates a durable audit trail for cleanup and enables Phase 1B to batch-create skills from unmatched entries.

Rationale: Silent discarding of unmatched entries would leave no record of what needs attention. A report table is queryable and keeps backfill idempotent.

Decision 7 — Slot certification normalisation is deferred to Phase 1B​

project_slots.preferredCertifications text[] is analogous to requiredSkills but less critical for immediate matching. Deferring avoids scope creep and keeps this migration focused.

Decision 8 — FK hardening uses the NOT VALID pattern​

For columns known to have potential orphan rows (person_skills.skillId, company_capability_goal_requirements.skillId/.certificationId, skills.parentSkillId), we:

  1. Run orphan queries before adding any constraint.
  2. If orphan count is zero: add the FK with NOT VALID (validates structure without a full table scan).
  3. If orphans exist: document the cleanup SQL in the audit document and defer the constraint.

NOT VALID means the constraint is enforced for new rows immediately but does not retroactively scan existing data. A subsequent VALIDATE CONSTRAINT can be run during a maintenance window.

Consequences​

  • skills, certifications tables gain nullable metadata columns — no existing queries break.
  • Four new tables are added (skill_relationships, certification_skill_mappings, slot_skill_requirements, skill_requirement_backfill_reports).
  • Spider continues to read requiredSkills text[] unchanged.
  • Backfill can be run in dry-run mode (default) or commit mode.
  • Phase 1A-read can expose slot_skill_requirements via API without further migration.
  • Phase 1B can normalise certifications, project-level requirements, and create skills from unmatched tokens.