4. Activate Subcategories

Purpose: Add or activate subcategories at any depth (1-3), configure schema inheritance and extensions, and integrate into the extraction and feed pipelines.

Prerequisites:

  • Parent category must exist and be active in topic_categories
  • Parent category must have an assigned schema in fact_record_schemas
  • Database access via SUPABASE_URL and SUPABASE_SERVICE_ROLE_KEY

Cost / Duration: $0 | 5-10 minutes

Schema Inheritance Model

The trigger fn_inherit_parent_schema_and_formats runs on INSERT to topic_categories. It automatically copies the parent's schema_id. This works at all depths.

root (depth 0)     — own schema (e.g., auto_fact, 9 keys)
  └ branch (depth 1) — inherits auto_fact
      └ leaf (depth 2)  — inherits auto_fact, may extend (+3-8 keys)
          └ detail (depth 3) — inherits leaf schema (including extensions)

Schema extensions append domain-specific keys via JSONB || concatenation. For example, formula-one (depth 2) inherits auto_fact (9 keys) then extends with circuit, team, pole_positions = 14+ keys. Its depth-3 children (teams-constructors, famous-circuits) inherit all 14+ keys automatically.

Prompt

Activate subcategories for the [parent-category] topic.

Step 1 — Check existing subcategories at any depth:

```sql
-- List all descendants of a category (using materialized path)
SELECT tc.slug, tc.name, tc.depth, tc.path, tc.is_active,
       frs.schema_name, jsonb_array_length(frs.fact_keys) as key_count
FROM topic_categories tc
LEFT JOIN fact_record_schemas frs ON frs.topic_category_id = tc.id
WHERE tc.path LIKE '[parent-slug]/%'
ORDER BY tc.path;
```

Step 2 — Add new subcategories (INSERT pattern):

New subcategories are added via INSERT (not UPDATE). The trigger
handles schema inheritance automatically.

```sql
-- Depth-1 branch under a root:
INSERT INTO topic_categories (slug, name, parent_id, depth, path)
VALUES ('my-branch', 'My Branch',
  (SELECT id FROM topic_categories WHERE slug = '[root-slug]'),
  1, '[root-slug]/my-branch')
ON CONFLICT (slug) DO NOTHING;

-- Depth-2 leaf under a branch:
INSERT INTO topic_categories (slug, name, parent_id, depth, path)
VALUES ('my-leaf', 'My Leaf',
  (SELECT id FROM topic_categories WHERE slug = 'my-branch'),
  2, '[root-slug]/my-branch/my-leaf')
ON CONFLICT (slug) DO NOTHING;

-- Depth-3 detail under a leaf:
INSERT INTO topic_categories (slug, name, parent_id, depth, path)
VALUES ('my-detail', 'My Detail',
  (SELECT id FROM topic_categories WHERE slug = 'my-leaf'),
  3, '[root-slug]/my-branch/my-leaf/my-detail')
ON CONFLICT (slug) DO NOTHING;
```

Step 3 — Add schema extensions (optional, for depth-2 leaves):

For subcategories that need additional domain-specific keys beyond
the inherited schema:

```sql
-- Append 3-8 domain-specific keys to the inherited schema
UPDATE fact_record_schemas
SET fact_keys = fact_keys || '[
  {"key": "circuit", "label": "Circuit", "type": "text", "required": false},
  {"key": "team", "label": "Team", "type": "text", "required": false},
  {"key": "championship_year", "label": "Championship Year", "type": "number", "required": false}
]'::jsonb
WHERE topic_category_id = (
  SELECT id FROM topic_categories WHERE slug = 'my-leaf'
);
```

Note: Depth-3 children of an extended leaf automatically inherit
the extended schema (base + extensions) via the trigger.

Step 4 — Add taxonomy rules and voice (optional):

Edit `packages/ai/src/config/taxonomy-rules-data.ts` to add a
subcategory-specific entry. If omitted, the subcategory inherits
rules from its nearest ancestor that has an entry.

```typescript
'[subcategory-slug]': {
  domain_terms: ['term1', 'term2'],
  extraction_guidance: 'Focus on...',
},
```

Edit `packages/ai/src/config/taxonomy-voices-data.ts` similarly.

Step 5 — Verify activation and schema:

```sql
-- Verify schema inheritance chain
SELECT tc.slug, tc.depth, tc.path,
       frs.schema_name,
       jsonb_array_length(frs.fact_keys) as key_count
FROM topic_categories tc
LEFT JOIN fact_record_schemas frs ON frs.topic_category_id = tc.id
WHERE tc.path LIKE '[root-slug]/%'
ORDER BY tc.depth, tc.path;
```

```bash
# Verify taxonomy rules compile
bun run typecheck --filter=@eko/ai

# Run taxonomy tests
bun scripts/taxonomy/audit-schema-coverage.ts [subcategory-slug]
```

Verification

  • Subcategory row exists in topic_categories with correct depth and path
  • Schema inherited via trigger (check fact_record_schemas.topic_category_id)
  • Schema extensions applied correctly (key count = parent keys + extension keys)
  • Depth-3 children inherit extended schemas from their depth-2 parent
  • Taxonomy rules entry exists (or inherits from ancestor)
  • Schema audit passes: bun scripts/taxonomy/audit-schema-coverage.ts [slug]
  • Typecheck passes: bun run typecheck --filter=@eko/ai

Back to index