Skip to main content

DB-to-TS Field Mapping

The database column names for taxonomy classification fields do not match their TypeScript property names. The columns taxonomy_type and parent_category are swapped between the database schema and the TypeScript TopicRecord type. This is the single most important convention to understand before writing any SQL query or modifying the storage layer.

This page documents what the swap is, why it exists, where the mapping boundary lives, and how to avoid the pitfalls that come with it.

The Mapping Table​

DB Column NameTS Property NameContainsCountExample Values
taxonomy_typeparent_categorySpecific category labels41"Auto", "Business Technology", "Insurance", "Cannabis"
parent_categorytaxonomy_typeBroad group labels13"Automotive & Vehicles", "Technology & Telecom", "Health"
subcategorysubcategoryFree-text subcategory--"Electric Vehicles", "Skincare", "Cloud Computing"

In plain English:

  • What the database calls taxonomy_type is what TypeScript calls parent_category (the 41 specific categories)
  • What the database calls parent_category is what TypeScript calls taxonomy_type (the 13 broad groups)
This is not a bug

The swap is intentional and permanent. It exists because the database schema was created with one naming convention, the TypeScript types evolved a different convention, and by the time the mismatch was identified, production data and API contracts made a schema rename impractical. The mapping layer handles it cleanly.

Why the Swap Exists​

During early development, the database schema named its columns based on an initial hierarchy design:

  • taxonomy_type was intended for the most specific classification level
  • parent_category was intended for the broader grouping

As the TypeScript codebase evolved, the naming convention flipped -- developers found it more natural to think of taxonomy_type as the broad group ("Technology & Telecom") and parent_category as the specific type ("Business Technology"). By the time this divergence was caught, the database had production data, migration history, and multiple raw SQL queries depending on the original column names.

Rather than run a destructive rename migration, the team introduced a mapping layer at the storage boundary that swaps the fields transparently.

The Mapping Boundary: rowToTopicRecord()​

The swap happens in exactly one place: the rowToTopicRecord() function in src/lib/storage/neon-store-helpers.ts. This function converts raw database rows into TypeScript TopicRecord objects.

// src/lib/storage/neon-store-helpers.ts (lines 82-83)
return {
  // ...
  parent_category: (overrides?.parent_category ?? tType) as string,   // DB taxonomy_type → TS parent_category
  taxonomy_type: (overrides?.taxonomy_type ?? tParent) as string,      // DB parent_category → TS taxonomy_type
  // ...
};

The inverse swap happens when writing to the database -- the Neon store methods map TS parent_category back to the DB taxonomy_type column, and TS taxonomy_type back to DB parent_category.

What This Means in Practice​

All code that goes through ITaxonomyStore methods (the standard CRUD operations) gets the swap for free. You use TypeScript names and never think about it:

// TypeScript business logic -- use TS names
const topic = await store.getTopic(id, ctx);
console.log(topic.taxonomy_type);     // "Technology & Telecom" (13 groups)
console.log(topic.parent_category);   // "Business Technology" (41 types)

Rules for Developers​

Rule 1: Use TypeScript Names in Business Logic​

In components, hooks, API route handlers, classification engine code, and any module that works with TopicRecord objects, always use the TypeScript property names:

  • topic.taxonomy_type = one of 13 broad groups
  • topic.parent_category = one of 41 specific categories

Rule 2: Use Database Names in Raw SQL​

When writing raw SQL queries (outside the store layer), use the database column names:

  • t.taxonomy_type = 41 specific categories (what TS calls parent_category)
  • t.parent_category = 13 broad groups (what TS calls taxonomy_type)

Rule 3: Swap Manually in Raw SQL Routes​

If your API route uses raw SQL and returns data to the client, you must swap the columns manually so the response matches the TypeScript convention. Map the DB column to the opposite TS property name in your SELECT clause or result mapping.

Raw SQL Routes That Swap Manually​

Three API routes bypass rowToTopicRecord() and perform the swap in their own code:

1. src/app/api/topics/catalog/route.ts​

The catalog route sorts by swapped column names:

// Lines 149-152 -- sort expression mapping swaps the names
case "parent_category": orderExpr = "LOWER(t.taxonomy_type)"; break;
case "taxonomy_type": orderExpr = "LOWER(COALESCE(t.parent_category, ''))"; break;

When the client requests sorting by parent_category (TS name = 41 types), the SQL sorts by t.taxonomy_type (DB name for 41 types).

2. src/app/api/topics/catalog/facets/route.ts​

The facets route queries distinct values and labels them with TS names:

// Lines 47-49 -- DB column taxonomy_type stores 41 parent-category values
parentCategories: sql`SELECT DISTINCT taxonomy_type AS value FROM topics ...`,
// DB column parent_category stores 13 taxonomy-type groups
taxonomyTypes: sql`SELECT DISTINCT parent_category AS value FROM topics ...`,

The facet named parentCategories (TS convention) is populated from DB column taxonomy_type. The facet named taxonomyTypes is populated from DB column parent_category.

3. src/app/api/admin/topics/stats/route.ts​

The stats route groups by DB taxonomy_type (41 categories) and labels the result as byParentCategory in the response JSON to match the TS convention.

The taxonomy_group Column (Deprecated)​

The database also contains a taxonomy_group column (added in migration 0007_taxonomy_columns.sql). This column is deprecated -- it was made redundant by parent_category (the 13 groups) and is no longer read or written by any application code. It still exists in the schema but should be ignored.

Do not use taxonomy_group in new queries. Do not add it to TypeScript types. It may be dropped in a future migration.

The Hierarchy at Each Layer​

To keep the swap straight, remember the hierarchy reads top-down as:

taxonomy_type  (TS name) = 13 broad groups     (DB column: parent_category)
  └─ parent_category (TS name) = 41 specific types  (DB column: taxonomy_type)
      └─ subcategory = free text                     (DB column: subcategory, no swap)
          └─ topic = the classified segment

Allowed Sort Columns​

The ALLOWED_SORT_COLUMNS set in neon-store-helpers.ts defines which columns can be used for sorting in paginated queries. The comments in the code document the swap:

export const ALLOWED_SORT_COLUMNS = new Set([
  "topic_name",
  "taxonomy_type",    // DB: 41 types (TS: parent_category)
  "segment_type",
  "created_at",
  "performance_score",
  "audience_type",
  "parent_category",  // DB: 13 groups (TS: taxonomy_type)
]);
These are DB column names

The sort column values in ALLOWED_SORT_COLUMNS are database column names, since they are used directly in SQL ORDER BY clauses. The client sends TS property names, and the store layer maps them to DB names before constructing the query.

Common Pitfalls​

Pitfall 1: Writing Raw SQL with TS Names​

-- WRONG: Uses TS names in SQL
SELECT * FROM topics WHERE taxonomy_type = 'Technology & Telecom';

-- CORRECT: taxonomy_type in DB holds 41 types (TS parent_category)
-- Use parent_category in DB for 13 groups (TS taxonomy_type)
SELECT * FROM topics WHERE parent_category = 'Technology & Telecom';

Pitfall 2: Filtering by the Wrong Column​

If you want all topics in the "Auto" parent category (one of the 41 types):

-- CORRECT: DB taxonomy_type holds 41 specific types
SELECT * FROM topics WHERE taxonomy_type = 'Auto';

-- WRONG: DB parent_category holds 13 groups, not specific types
SELECT * FROM topics WHERE parent_category = 'Auto';

Pitfall 3: Forgetting to Swap in New Raw SQL Routes​

If you add a new API route with raw SQL, and you return the results to the client without swapping, the client will see inverted values. Always either:

  1. Use the store layer (getTopic, listTopics, etc.) which swaps automatically, or
  2. Map the raw row through rowToTopicRecord(), or
  3. Swap manually in your SELECT aliases or result mapping

Pitfall 4: Confusing the Hash Computation​

Engine version hashing (computeParentCategoryHash) operates on the values of taxonomy definitions, not on property names. Renaming a TS property does not change the hash -- only changing the actual category labels, descriptions, or domain signals does.

Quick Reference Card​

When I want...In TypeScript use...In SQL use...
The 13 broad groups (e.g., "Technology & Telecom")topic.taxonomy_typet.parent_category
The 41 specific categories (e.g., "Business Technology")topic.parent_categoryt.taxonomy_type
The free-text subcategorytopic.subcategoryt.subcategory
The swap functionrowToTopicRecord(row)N/A (apply manually)

Next Steps​