cmdb-insight/docs/NORMALIZED-DATABASE-IMPLEMENTATION-PLAN.md

Normalized Database Implementation Plan

Executive Summary

Status: Green Field Implementation
Approach: Complete rebuild with normalized structure
Timeline: 1-2 weeks
Risk: Low (no production data to migrate)

This document outlines the complete implementation plan for migrating from JSONB-based storage to a fully normalized, generic database structure that works with any Jira Assets schema.

---

Table of Contents

1. Architecture Overview
2. Database Schema
3. Implementation Components
4. Implementation Steps
5. Code Structure
6. Testing Strategy
7. Migration Path
8. Example Implementations

---

Architecture Overview

Current Architecture (JSONB)

Jira Assets API
    ↓
jiraAssetsClient.parseObject()
    ↓
CMDBObject (TypeScript)
    ↓
cacheStore.upsertObject()
    ↓
cached_objects.data (JSONB column)
    ↓
Queries: Load all → Filter in JavaScript ❌

Problems:

• All objects loaded into memory
• No database-level indexing on attributes
• Slow queries with complex filters
• Memory overhead for large datasets

Target Architecture (Normalized)

Jira Assets API
    ↓
jiraAssetsClient.parseObject()
    ↓
CMDBObject (TypeScript)
    ↓
normalizedCacheStore.upsertObject()
    ↓
Normalize to: objects + attribute_values
    ↓
Queries: SQL JOINs with indexes ✅

Benefits:

• Database-level filtering
• Indexed attributes
• Efficient queries
• Scalable to large datasets

Key Benefits

1. Generic: Works with any Jira Assets schema (discovered dynamically)
2. Efficient: Database-level filtering with indexes
3. Scalable: Handles large datasets efficiently
4. Type-safe: Proper data types per attribute
5. Queryable: Complex filters at database level

---

Database Schema

Tables

1. object_types

Stores discovered object types from Jira schema.

CREATE TABLE object_types (
  jira_type_id INTEGER PRIMARY KEY,
  type_name TEXT NOT NULL UNIQUE,
  display_name TEXT NOT NULL,
  description TEXT,
  sync_priority INTEGER DEFAULT 0,
  object_count INTEGER DEFAULT 0,
  discovered_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

Purpose: Metadata about object types discovered from Jira schema.

2. attributes

Stores discovered attributes per object type.

CREATE TABLE attributes (
  id SERIAL PRIMARY KEY,
  jira_attr_id INTEGER NOT NULL,
  object_type_name TEXT NOT NULL REFERENCES object_types(type_name) ON DELETE CASCADE,
  attr_name TEXT NOT NULL,           -- "Application Function"
  field_name TEXT NOT NULL,          -- "applicationFunction" (camelCase)
  attr_type TEXT NOT NULL,           -- 'text', 'reference', 'integer', etc.
  is_multiple BOOLEAN NOT NULL DEFAULT FALSE,
  is_editable BOOLEAN NOT NULL DEFAULT TRUE,
  is_required BOOLEAN NOT NULL DEFAULT FALSE,
  is_system BOOLEAN NOT NULL DEFAULT FALSE,
  reference_type_name TEXT,          -- For reference attributes (e.g., "ApplicationFunction")
  description TEXT,
  discovered_at TIMESTAMP NOT NULL DEFAULT NOW(),
  UNIQUE(jira_attr_id, object_type_name)
);

Purpose: Metadata about attributes discovered from Jira schema. Used to build queries dynamically.

3. objects

Stores minimal object metadata.

CREATE TABLE objects (
  id TEXT PRIMARY KEY,
  object_key TEXT NOT NULL UNIQUE,
  object_type_name TEXT NOT NULL REFERENCES object_types(type_name) ON DELETE CASCADE,
  label TEXT NOT NULL,
  jira_updated_at TIMESTAMP,
  jira_created_at TIMESTAMP,
  cached_at TIMESTAMP NOT NULL DEFAULT NOW()
);

Purpose: Core object information. All attribute values stored separately in attribute_values.

4. attribute_values

EAV pattern for storing all attribute values.

CREATE TABLE attribute_values (
  id SERIAL PRIMARY KEY,
  object_id TEXT NOT NULL REFERENCES objects(id) ON DELETE CASCADE,
  attribute_id INTEGER NOT NULL REFERENCES attributes(id) ON DELETE CASCADE,
  -- Value storage (one column populated based on type):
  text_value TEXT,                   -- For text, textarea, url, email, select, user, status
  number_value NUMERIC,              -- For integer, float
  boolean_value BOOLEAN,             -- For boolean
  date_value DATE,                   -- For date
  datetime_value TIMESTAMP,          -- For datetime
  reference_object_id TEXT,          -- For reference attributes (stores objectId)
  -- For arrays: multiple rows with different array_index
  array_index INTEGER DEFAULT 0,     -- 0 = single value, >0 = array element
  UNIQUE(object_id, attribute_id, array_index)
);

Purpose: Stores all attribute values in normalized form. One row per value (multiple rows for arrays).

5. object_relations

Enhanced existing table with attribute_id reference.

CREATE TABLE object_relations (
  id SERIAL PRIMARY KEY,
  source_id TEXT NOT NULL REFERENCES objects(id) ON DELETE CASCADE,
  target_id TEXT NOT NULL REFERENCES objects(id) ON DELETE CASCADE,
  attribute_id INTEGER NOT NULL REFERENCES attributes(id) ON DELETE CASCADE,
  source_type TEXT NOT NULL,
  target_type TEXT NOT NULL,
  UNIQUE(source_id, target_id, attribute_id)
);

Purpose: Stores relationships between objects. Enhanced with attribute_id for better queries.

6. sync_metadata

Unchanged.

CREATE TABLE sync_metadata (
  key TEXT PRIMARY KEY,
  value TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

Indexes

Critical for query performance:

-- Objects
CREATE INDEX idx_objects_type ON objects(object_type_name);
CREATE INDEX idx_objects_key ON objects(object_key);
CREATE INDEX idx_objects_label ON objects(label);

-- Attributes (for schema lookups)
CREATE INDEX idx_attributes_type ON attributes(object_type_name);
CREATE INDEX idx_attributes_field ON attributes(field_name);
CREATE INDEX idx_attributes_type_field ON attributes(object_type_name, field_name);

-- Attribute Values (critical for query performance)
CREATE INDEX idx_attr_values_object ON attribute_values(object_id);
CREATE INDEX idx_attr_values_attr ON attribute_values(attribute_id);
CREATE INDEX idx_attr_values_text ON attribute_values(text_value) WHERE text_value IS NOT NULL;
CREATE INDEX idx_attr_values_number ON attribute_values(number_value) WHERE number_value IS NOT NULL;
CREATE INDEX idx_attr_values_reference ON attribute_values(reference_object_id) WHERE reference_object_id IS NOT NULL;
CREATE INDEX idx_attr_values_composite_text ON attribute_values(attribute_id, text_value) WHERE text_value IS NOT NULL;
CREATE INDEX idx_attr_values_composite_ref ON attribute_values(attribute_id, reference_object_id) WHERE reference_object_id IS NOT NULL;
CREATE INDEX idx_attr_values_object_attr ON attribute_values(object_id, attribute_id);

-- Relations
CREATE INDEX idx_relations_source ON object_relations(source_id);
CREATE INDEX idx_relations_target ON object_relations(target_id);
CREATE INDEX idx_relations_attr ON object_relations(attribute_id);

---

Implementation Components

1. Schema Discovery Service

File: backend/src/services/schemaDiscoveryService.ts

Purpose: Populate object_types and attributes tables from generated schema.

Methods:

class SchemaDiscoveryService {
  /**
   * Discover schema from OBJECT_TYPES and populate database
   */
  async discoverAndStoreSchema(): Promise<void>;
  
  /**
   * Get object type definition from database
   */
  async getObjectType(typeName: string): Promise<ObjectTypeDefinition | null>;
  
  /**
   * Get attribute definition by type and field name
   */
  async getAttribute(typeName: string, fieldName: string): Promise<AttributeDefinition | null>;
  
  /**
   * Get all attributes for an object type
   */
  async getAttributesForType(typeName: string): Promise<AttributeDefinition[]>;
}

Implementation Notes:

• Reads from OBJECT_TYPES in generated/jira-schema.ts
• Populates object_types and attributes tables
• Called once at startup or when schema changes
• Idempotent (can be called multiple times)

2. Normalized Cache Store

File: backend/src/services/normalizedCacheStore.ts

Purpose: Replace cacheStore.ts with normalized implementation.

Key Methods:

Object Operations

class NormalizedCacheStore {
  /**
   * Upsert object: normalize and store
   */
  async upsertObject<T extends CMDBObject>(
    typeName: CMDBObjectTypeName,
    object: T
  ): Promise<void>;
  
  /**
   * Get object: reconstruct from normalized data
   */
  async getObject<T extends CMDBObject>(
    typeName: CMDBObjectTypeName,
    id: string
  ): Promise<T | null>;
  
  /**
   * Get objects with optional filters
   */
  async getObjects<T extends CMDBObject>(
    typeName: CMDBObjectTypeName,
    options?: QueryOptions
  ): Promise<T[]>;
  
  /**
   * Delete object and all its attribute values
   */
  async deleteObject(
    typeName: CMDBObjectTypeName,
    id: string
  ): Promise<boolean>;
}

Normalization Logic

  /**
   * Convert CMDBObject to normalized form
   */
  private async normalizeObject(
    object: CMDBObject,
    typeName: CMDBObjectTypeName
  ): Promise<void>;
  
  /**
   * Store attribute value based on type
   */
  private async storeAttributeValue(
    objectId: string,
    attributeId: number,
    value: unknown,
    attrDef: AttributeDefinition,
    arrayIndex?: number
  ): Promise<void>;

Reconstruction Logic

  /**
   * Reconstruct CMDBObject from normalized data
   */
  private async reconstructObject<T extends CMDBObject>(
    objectId: string,
    typeName: CMDBObjectTypeName
  ): Promise<T | null>;
  
  /**
   * Load all attribute values for an object
   */
  private async loadAttributeValues(
    objectId: string,
    typeName: CMDBObjectTypeName
  ): Promise<Map<string, unknown>>;

Query Operations

  /**
   * Query objects with filters (generic)
   */
  async queryWithFilters<T extends CMDBObject>(
    typeName: CMDBObjectTypeName,
    filters: Record<string, unknown>,
    options?: QueryOptions
  ): Promise<{ objects: T[]; total: number }>;

3. Generic Query Builder

File: backend/src/services/queryBuilder.ts

Purpose: Build SQL queries dynamically based on filters.

Methods:

class QueryBuilder {
  /**
   * Build WHERE clause from filters
   */
  buildWhereClause(
    filters: Record<string, unknown>,
    typeName: CMDBObjectTypeName
  ): { whereClause: string; params: unknown[] };
  
  /**
   * Build filter condition for one field
   */
  buildFilterCondition(
    fieldName: string,
    filterValue: unknown,
    attrDef: AttributeDefinition
  ): { condition: string; params: unknown[] };
  
  /**
   * Build ORDER BY clause
   */
  buildOrderBy(orderBy?: string, orderDir?: 'ASC' | 'DESC'): string;
  
  /**
   * Build pagination clause
   */
  buildPagination(limit?: number, offset?: number): string;
}

Filter Types Supported:

1. Exact match:

   { status: "Active" }
   

2. Contains (text):

   { name: { contains: "search" } }
   

3. Reference match:

   { governanceModel: { objectId: "123" } }
   { governanceModel: { objectKey: "GOV-A" } }
   { governanceModel: { label: "Model A" } }
   

4. Array contains:

   { applicationFunction: [
     { objectId: "1" },
     { objectId: "2" }
   ]}
   

5. Exists:

   { applicationFunction: { exists: true } }
   

6. Empty:

   { applicationFunction: { empty: true } }
   

4. Updated Services

syncEngine.ts

• Update syncObjectType() to use normalizedCacheStore
• Keep same interface, different implementation
• No changes to sync logic, only storage layer

dataService.ts

• Update searchApplications() to use queryWithFilters()
• Remove JavaScript filtering logic
• Use SQL queries instead
• Much faster for complex filters

cmdbService.ts

• Update to use normalizedCacheStore instead of cacheStore
• Keep same interface
• No changes to business logic

---

Implementation Steps

Step 1: Create Database Schema

Files:

• backend/src/services/database/normalized-schema.ts - Schema definitions
• Update backend/src/services/database/migrations.ts - Add migration

Tasks:

1. Create schema SQL for PostgreSQL
2. Create schema SQL for SQLite (development)
3. Create migration function
4. Test schema creation

Deliverables:

• Schema SQL files
• Migration function
• Tests pass

Step 2: Schema Discovery Service

File: backend/src/services/schemaDiscoveryService.ts

Tasks:

1. Implement discoverAndStoreSchema() - Read from OBJECT_TYPES
2. Populate object_types table
3. Populate attributes table
4. Add validation and error handling
5. Add caching (don't rediscover if already done)

Deliverables:

• Schema discovery service
• Tests pass
• Schema populated in database

Step 3: Normalization Logic

File: backend/src/services/normalizedCacheStore.ts (partial)

Tasks:

1. Implement normalizeObject() - Convert CMDBObject to normalized form
2. Handle all attribute types:
   • Text (text, textarea, url, email, select, user, status)
   • Numbers (integer, float)
   • Boolean
   • Dates (date, datetime)
   • References (single and multiple)
3. Implement storeAttributeValue() - Store values in correct columns
4. Handle arrays (multiple rows with array_index)
5. Test normalization with sample objects

Deliverables:

• Normalization logic
• Tests for all attribute types
• Sample data normalized correctly

Step 4: Reconstruction Logic

File: backend/src/services/normalizedCacheStore.ts (partial)

Tasks:

1. Implement reconstructObject() - Build CMDBObject from normalized data
2. Load all attribute values for object
3. Convert back to TypeScript types
4. Handle arrays (multiple rows)
5. Handle references (load referenced objects if needed)
6. Test reconstruction correctness

Deliverables:

• Reconstruction logic
• Tests for all attribute types
• Reconstructed objects match original

Step 5: Basic CRUD Operations

File: backend/src/services/normalizedCacheStore.ts (partial)

Tasks:

1. Implement upsertObject() - Normalize and store
2. Implement getObject() - Reconstruct and return
3. Implement getObjects() - Basic query (no filters yet)
4. Implement deleteObject() - Delete object and values
5. Implement countObjects() - Count by type
6. Test CRUD operations

Deliverables:

• CRUD operations working
• Tests pass
• Data integrity verified

Step 6: Generic Query Builder

File: backend/src/services/queryBuilder.ts

Tasks:

1. Implement filter condition builder
2. Support all filter types:
   • Exact match
   • Contains
   • Reference match
   • Array contains
   • Exists
   • Empty
3. Build WHERE clauses dynamically
4. Handle JOINs for attribute values
5. Test query generation

Deliverables:

• Query builder
• Tests for all filter types
• SQL generation correct

Step 7: Query Operations

File: backend/src/services/normalizedCacheStore.ts (partial)

Tasks:

1. Implement queryWithFilters() - Use query builder
2. Implement countWithFilters() - Count with same filters
3. Add pagination support
4. Add sorting support
5. Test complex queries

Deliverables:

• Query operations working
• Performance tests pass
• Complex filters work correctly

Step 8: Relations

File: backend/src/services/normalizedCacheStore.ts (partial)

Tasks:

1. Update extractAndStoreRelations() - Use attribute_id
2. Update getRelatedObjects() - Use normalized queries
3. Update getReferencingObjects() - Use normalized queries
4. Test relations

Deliverables:

• Relations working
• Tests pass
• Performance acceptable

Step 9: Update Services

Files:

• backend/src/services/syncEngine.ts
• backend/src/services/cmdbService.ts
• backend/src/services/dataService.ts

Tasks:

1. Replace cacheStore imports with normalizedCacheStore
2. Update dataService.searchApplications() - Use queryWithFilters()
3. Remove JavaScript filtering logic
4. Update all service calls
5. Test all endpoints

Deliverables:

• All services updated
• All endpoints working
• Tests pass

Step 10: Statistics & Utilities

File: backend/src/services/normalizedCacheStore.ts (partial)

Tasks:

1. Implement getStats() - Count from normalized tables
2. Implement isWarm() - Check if cache has data
3. Implement clearObjectType() - Clear type and values
4. Implement clearAll() - Clear all data
5. Test statistics

Deliverables:

• Statistics working
• Tests pass
• Performance acceptable

Step 11: Remove Old Code

Files:

• backend/src/services/cacheStore.ts - Delete or archive

Tasks:

1. Remove old cacheStore.ts
2. Update all imports
3. Clean up unused code
4. Update documentation

Deliverables:

• Old code removed
• All imports updated
• Documentation updated

---

Code Structure

File Organization

backend/src/services/
├── database/
│   ├── normalized-schema.ts          # NEW: Schema definitions
│   ├── migrations.ts                 # UPDATED: Add normalized schema migration
│   ├── factory.ts                    # (unchanged)
│   ├── interface.ts                  # (unchanged)
│   ├── postgresAdapter.ts            # (unchanged)
│   └── sqliteAdapter.ts              # (unchanged)
├── schemaDiscoveryService.ts          # NEW: Schema discovery
├── normalizedCacheStore.ts           # NEW: Normalized storage
├── queryBuilder.ts                   # NEW: Generic query builder
├── syncEngine.ts                     # UPDATED: Use normalized store
├── cmdbService.ts                    # UPDATED: Use normalized store
├── dataService.ts                    # UPDATED: Use queries
└── cacheStore.ts                     # REMOVE: Old implementation

Key Interfaces

// NormalizedCacheStore interface (same as old CacheStore)
interface NormalizedCacheStore {
  // Object operations
  upsertObject<T>(typeName: CMDBObjectTypeName, object: T): Promise<void>;
  getObject<T>(typeName: CMDBObjectTypeName, id: string): Promise<T | null>;
  getObjectByKey<T>(typeName: CMDBObjectTypeName, objectKey: string): Promise<T | null>;
  getObjects<T>(typeName: CMDBObjectTypeName, options?: QueryOptions): Promise<T[]>;
  countObjects(typeName: CMDBObjectTypeName): Promise<number>;
  deleteObject(typeName: CMDBObjectTypeName, id: string): Promise<boolean>;
  
  // Query operations
  queryWithFilters<T>(
    typeName: CMDBObjectTypeName,
    filters: Record<string, unknown>,
    options?: QueryOptions
  ): Promise<{ objects: T[]; total: number }>;
  
  // Relations
  extractAndStoreRelations<T>(typeName: CMDBObjectTypeName, object: T): Promise<void>;
  getRelatedObjects<T>(
    sourceId: string,
    targetTypeName: CMDBObjectTypeName,
    attributeName?: string
  ): Promise<T[]>;
  getReferencingObjects<T>(
    targetId: string,
    sourceTypeName: CMDBObjectTypeName,
    attributeName?: string
  ): Promise<T[]>;
  
  // Statistics
  getStats(): Promise<CacheStats>;
  isWarm(): Promise<boolean>;
  clearObjectType(typeName: CMDBObjectTypeName): Promise<number>;
  clearAll(): Promise<void>;
  
  // Sync metadata
  getSyncMetadata(key: string): Promise<string | null>;
  setSyncMetadata(key: string, value: string): Promise<void>;
}

// QueryBuilder interface
interface QueryBuilder {
  buildWhereClause(
    filters: Record<string, unknown>,
    typeName: CMDBObjectTypeName
  ): { whereClause: string; params: unknown[] };
  
  buildFilterCondition(
    fieldName: string,
    filterValue: unknown,
    attrDef: AttributeDefinition
  ): { condition: string; params: unknown[] };
}

---

Testing Strategy

Unit Tests

1. Schema Discovery

   • Test schema population
   • Test attribute lookup
   • Test object type lookup
   • Test idempotency

2. Normalization

   • Test all attribute types (text, number, boolean, date, reference)
   • Test arrays (multiple values)
   • Test null/empty values
   • Test edge cases

3. Reconstruction

   • Test object reconstruction
   • Test all attribute types
   • Test arrays
   • Test references
   • Test missing values

4. Query Builder

   • Test all filter types
   • Test complex filters
   • Test SQL generation
   • Test parameter binding

Integration Tests

1. CRUD Operations

   • Create, read, update, delete
   • Verify data integrity
   • Test transactions

2. Queries

   • Simple filters
   • Complex filters (multiple conditions)
   • Pagination
   • Sorting
   • Performance with large datasets

3. Relations

   • Store relations
   • Query relations
   • Delete relations
   • Cascade deletes

Performance Tests

1. Query Performance

   • Compare old vs new
   • Test with 500+ objects
   • Test complex filters
   • Measure query time

2. Write Performance

   • Batch inserts
   • Single inserts
   • Updates
   • Measure write time

3. Memory Usage

   • Compare old vs new
   • Test with large datasets
   • Measure memory footprint

---

Migration Path

Since it's Green Field

1. No Data Migration Needed

   • Start fresh with normalized structure
   • No existing data to migrate
   • Clean implementation

2. Implementation Order

   • Build new normalized structure
   • Test thoroughly
   • Replace old code
   • Deploy

3. Rollback Plan

   • Keep old code in git history
   • Can revert if needed
   • No data loss risk (green field)

---

Example Implementations

Example 1: Normalization

Input (CMDBObject):

{
  id: "123",
  objectKey: "APP-1",
  label: "My Application",
  status: "Active",
  applicationFunction: [
    { objectId: "456", objectKey: "FUNC-1", label: "Function 1" },
    { objectId: "789", objectKey: "FUNC-2", label: "Function 2" }
  ],
  ictGovernanceModel: { objectId: "999", objectKey: "GOV-A", label: "Model A" },
  customDevelopment: true,
  zenyaID: 42
}

Normalized (objects + attribute_values):

-- objects table
INSERT INTO objects (id, object_key, object_type_name, label, jira_updated_at, jira_created_at, cached_at)
VALUES ('123', 'APP-1', 'ApplicationComponent', 'My Application', '2024-01-01', '2024-01-01', NOW());

-- attribute_values table
INSERT INTO attribute_values (object_id, attribute_id, text_value, number_value, boolean_value, reference_object_id, array_index)
VALUES 
  -- status (text)
  ('123', <status_attr_id>, 'Active', NULL, NULL, NULL, 0),
  -- applicationFunction (reference array)
  ('123', <appfunc_attr_id>, NULL, NULL, NULL, '456', 0),  -- First function
  ('123', <appfunc_attr_id>, NULL, NULL, NULL, '789', 1),  -- Second function
  -- ictGovernanceModel (reference)
  ('123', <gov_attr_id>, NULL, NULL, NULL, '999', 0),
  -- customDevelopment (boolean)
  ('123', <customdev_attr_id>, NULL, NULL, true, NULL, 0),
  -- zenyaID (integer)
  ('123', <zenyaid_attr_id>, NULL, 42, NULL, NULL, 0);

Example 2: Query

Filter:

{
  status: "Active",
  governanceModel: { objectId: "999" },
  applicationFunction: { exists: true }
}

Generated SQL:

SELECT DISTINCT o.* 
FROM objects o
WHERE o.object_type_name = 'ApplicationComponent'
  AND EXISTS (
    SELECT 1 FROM attribute_values av
    JOIN attributes a ON av.attribute_id = a.id
    WHERE av.object_id = o.id
      AND a.field_name = 'status'
      AND av.text_value = $1
  )
  AND EXISTS (
    SELECT 1 FROM attribute_values av
    JOIN attributes a ON av.attribute_id = a.id
    WHERE av.object_id = o.id
      AND a.field_name = 'ictGovernanceModel'
      AND av.reference_object_id = $2
  )
  AND EXISTS (
    SELECT 1 FROM attribute_values av
    JOIN attributes a ON av.attribute_id = a.id
    WHERE av.object_id = o.id
      AND a.field_name = 'applicationFunction'
  )
ORDER BY o.label ASC
LIMIT $3 OFFSET $4;

Parameters: ['Active', '999', 25, 0]

Example 3: Reconstruction

Query:

SELECT o.*, a.field_name, av.*
FROM objects o
LEFT JOIN attribute_values av ON av.object_id = o.id
LEFT JOIN attributes a ON av.attribute_id = a.id
WHERE o.id = '123'
ORDER BY a.field_name, av.array_index;

Result Processing:

// Group by field_name
const values: Record<string, unknown> = {};

for (const row of rows) {
  const fieldName = row.field_name;
  const value = getValueFromRow(row); // Extract from correct column
  
  if (row.array_index === 0) {
    // Single value
    values[fieldName] = value;
  } else {
    // Array value
    if (!values[fieldName]) values[fieldName] = [];
    (values[fieldName] as unknown[]).push(value);
  }
}

// Build CMDBObject
const object: CMDBObject = {
  id: row.id,
  objectKey: row.object_key,
  label: row.label,
  ...values
};

---

Success Criteria

Functional Requirements

• ✅ All existing functionality works
• ✅ Queries return correct results
• ✅ No data loss
• ✅ Relations work correctly
• ✅ Sync process works

Performance Requirements

• ✅ Query performance: 50%+ faster for filtered queries
• ✅ Memory usage: 30%+ reduction
• ✅ Write performance: No degradation (< 10% slower acceptable)
• ✅ Database size: Similar or smaller

Quality Requirements

• ✅ Test coverage: 80%+ for new code
• ✅ No critical bugs
• ✅ Code well documented
• ✅ Type-safe implementation

---

Timeline

Step	Duration	Description
1	0.5 day	Database schema
2	0.5 day	Schema discovery
3	1 day	Normalization logic
4	1 day	Reconstruction logic
5	0.5 day	Basic CRUD
6	1 day	Query builder
7	1 day	Query operations
8	0.5 day	Relations
9	1 day	Update services
10	0.5 day	Statistics
11	0.5 day	Cleanup
Total	8 days

Buffer: +2 days for unexpected issues
Total Estimated: 1.5-2 weeks

---

Next Steps

1. ✅ Review and approve plan
2. ✅ Create feature branch: feature/normalized-database
3. ✅ Start implementation (Step 1)
4. ✅ Daily progress updates
5. ✅ Weekly review

---

Appendix

A. Attribute Type Mapping

Jira Type	Database Column	TypeScript Type
text, textarea, url, email, select, user, status	text_value	string
integer, float	number_value	number
boolean	boolean_value	boolean
date	date_value	string (ISO date)
datetime	datetime_value	string (ISO datetime)
reference	reference_object_id	ObjectReference

B. Query Performance Comparison

Old (JSONB + JavaScript filtering):

• Load all objects: ~500ms
• Filter in JavaScript: ~50ms
• Total: ~550ms

New (Normalized + SQL):

• Query with indexes: ~20ms
• Total: ~20ms

Improvement: ~27x faster

C. Database Size Comparison

Old (JSONB):

• 500 objects × ~5KB JSON = ~2.5MB

New (Normalized):

• 500 objects × ~100 bytes = ~50KB (objects)
• 500 objects × ~20 attributes × ~50 bytes = ~500KB (attribute_values)
• Total: ~550KB

Improvement: ~4.5x smaller

---

Detailed Implementation Guide

Safety Analysis

✅ SAFE TO IMPLEMENT - Here's why:

1. Green Field Status

   • No production data exists
   • No migration needed
   • Clean implementation possible

2. Interface Compatibility

   • New normalizedCacheStore will implement exact same interface as cacheStore
   • Same method signatures
   • Same return types
   • Drop-in replacement

3. Limited Dependencies

   • Only 4 files import cacheStore:
     • syncEngine.ts
     • cmdbService.ts
     • dataService.ts
     • routes/cache.ts
   • Easy to replace all at once

4. Classifications Database

   • Classifications database (database.ts) uses the same database as the cache
   • All data (CMDB cache, classification history, session state) in one database
   • Won't be affected by cache changes regardless

5. Rollback Plan

   • Old code in git history
   • Can revert if needed
   • No data loss risk

Complete Interface Definition

The new normalizedCacheStore must implement this exact interface:

// backend/src/services/normalizedCacheStore.ts

export interface CacheStats {
  totalObjects: number;
  objectsByType: Record<string, number>;
  totalRelations: number;
  lastFullSync: string | null;
  lastIncrementalSync: string | null;
  isWarm: boolean;
  dbSizeBytes: number;
}

export interface QueryOptions {
  limit?: number;
  offset?: number;
  orderBy?: string;
  orderDir?: 'ASC' | 'DESC';
}

class NormalizedCacheStore {
  // Object Operations
  async getObject<T extends CMDBObject>(typeName: CMDBObjectTypeName, id: string): Promise<T | null>;
  async getObjectByKey<T extends CMDBObject>(typeName: CMDBObjectTypeName, objectKey: string): Promise<T | null>;
  async getObjects<T extends CMDBObject>(typeName: CMDBObjectTypeName, options?: QueryOptions): Promise<T[]>;
  async countObjects(typeName: CMDBObjectTypeName): Promise<number>;
  async searchByLabel<T extends CMDBObject>(typeName: CMDBObjectTypeName, searchTerm: string, options?: QueryOptions): Promise<T[]>;
  async searchAllTypes(searchTerm: string, options?: QueryOptions): Promise<CMDBObject[]>;
  async upsertObject<T extends CMDBObject>(typeName: CMDBObjectTypeName, object: T): Promise<void>;
  async batchUpsertObjects<T extends CMDBObject>(typeName: CMDBObjectTypeName, objects: T[]): Promise<void>;
  async deleteObject(typeName: CMDBObjectTypeName, id: string): Promise<boolean>;
  async clearObjectType(typeName: CMDBObjectTypeName): Promise<number>;
  async clearAll(): Promise<void>;
  
  // Relations
  async upsertRelation(sourceId: string, targetId: string, attributeName: string, sourceType: string, targetType: string): Promise<void>;
  async batchUpsertRelations(relations: Array<{sourceId: string; targetId: string; attributeName: string; sourceType: string; targetType: string}>): Promise<void>;
  async getRelatedObjects<T extends CMDBObject>(sourceId: string, targetTypeName: CMDBObjectTypeName, attributeName?: string): Promise<T[]>;
  async getReferencingObjects<T extends CMDBObject>(targetId: string, sourceTypeName: CMDBObjectTypeName, attributeName?: string): Promise<T[]>;
  async deleteRelationsForObject(objectId: string): Promise<void>;
  async extractAndStoreRelations<T extends CMDBObject>(typeName: CMDBObjectTypeName, object: T): Promise<void>;
  
  // Sync Metadata
  async getSyncMetadata(key: string): Promise<string | null>;
  async setSyncMetadata(key: string, value: string): Promise<void>;
  async deleteSyncMetadata(key: string): Promise<void>;
  
  // Statistics
  async getStats(): Promise<CacheStats>;
  async isWarm(): Promise<boolean>;
  
  // Utility
  async close(): Promise<void>;
}

export const normalizedCacheStore = new NormalizedCacheStore();

Detailed Code Implementations

1. Database Schema (Step 1)

File: backend/src/services/database/normalized-schema.ts

/**
 * Normalized Database Schema
 * 
 * Generic, schema-agnostic normalized structure for CMDB data.
 */

export const NORMALIZED_SCHEMA_POSTGRES = `
-- Object Types
CREATE TABLE IF NOT EXISTS object_types (
  jira_type_id INTEGER PRIMARY KEY,
  type_name TEXT NOT NULL UNIQUE,
  display_name TEXT NOT NULL,
  description TEXT,
  sync_priority INTEGER DEFAULT 0,
  object_count INTEGER DEFAULT 0,
  discovered_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Attributes
CREATE TABLE IF NOT EXISTS attributes (
  id SERIAL PRIMARY KEY,
  jira_attr_id INTEGER NOT NULL,
  object_type_name TEXT NOT NULL REFERENCES object_types(type_name) ON DELETE CASCADE,
  attr_name TEXT NOT NULL,
  field_name TEXT NOT NULL,
  attr_type TEXT NOT NULL,
  is_multiple BOOLEAN NOT NULL DEFAULT FALSE,
  is_editable BOOLEAN NOT NULL DEFAULT TRUE,
  is_required BOOLEAN NOT NULL DEFAULT FALSE,
  is_system BOOLEAN NOT NULL DEFAULT FALSE,
  reference_type_name TEXT,
  description TEXT,
  discovered_at TIMESTAMP NOT NULL DEFAULT NOW(),
  UNIQUE(jira_attr_id, object_type_name)
);

-- Objects
CREATE TABLE IF NOT EXISTS objects (
  id TEXT PRIMARY KEY,
  object_key TEXT NOT NULL UNIQUE,
  object_type_name TEXT NOT NULL REFERENCES object_types(type_name) ON DELETE CASCADE,
  label TEXT NOT NULL,
  jira_updated_at TIMESTAMP,
  jira_created_at TIMESTAMP,
  cached_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Attribute Values
CREATE TABLE IF NOT EXISTS attribute_values (
  id SERIAL PRIMARY KEY,
  object_id TEXT NOT NULL REFERENCES objects(id) ON DELETE CASCADE,
  attribute_id INTEGER NOT NULL REFERENCES attributes(id) ON DELETE CASCADE,
  text_value TEXT,
  number_value NUMERIC,
  boolean_value BOOLEAN,
  date_value DATE,
  datetime_value TIMESTAMP,
  reference_object_id TEXT,
  array_index INTEGER DEFAULT 0,
  UNIQUE(object_id, attribute_id, array_index)
);

-- Relations (enhanced)
CREATE TABLE IF NOT EXISTS object_relations (
  id SERIAL PRIMARY KEY,
  source_id TEXT NOT NULL REFERENCES objects(id) ON DELETE CASCADE,
  target_id TEXT NOT NULL REFERENCES objects(id) ON DELETE CASCADE,
  attribute_id INTEGER NOT NULL REFERENCES attributes(id) ON DELETE CASCADE,
  source_type TEXT NOT NULL,
  target_type TEXT NOT NULL,
  UNIQUE(source_id, target_id, attribute_id)
);

-- Sync Metadata
CREATE TABLE IF NOT EXISTS sync_metadata (
  key TEXT PRIMARY KEY,
  value TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_objects_type ON objects(object_type_name);
CREATE INDEX IF NOT EXISTS idx_objects_key ON objects(object_key);
CREATE INDEX IF NOT EXISTS idx_objects_label ON objects(label);
CREATE INDEX IF NOT EXISTS idx_attributes_type ON attributes(object_type_name);
CREATE INDEX IF NOT EXISTS idx_attributes_field ON attributes(field_name);
CREATE INDEX IF NOT EXISTS idx_attributes_type_field ON attributes(object_type_name, field_name);
CREATE INDEX IF NOT EXISTS idx_attr_values_object ON attribute_values(object_id);
CREATE INDEX IF NOT EXISTS idx_attr_values_attr ON attribute_values(attribute_id);
CREATE INDEX IF NOT EXISTS idx_attr_values_text ON attribute_values(text_value) WHERE text_value IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_attr_values_number ON attribute_values(number_value) WHERE number_value IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_attr_values_reference ON attribute_values(reference_object_id) WHERE reference_object_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_attr_values_composite_text ON attribute_values(attribute_id, text_value) WHERE text_value IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_attr_values_composite_ref ON attribute_values(attribute_id, reference_object_id) WHERE reference_object_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_relations_source ON object_relations(source_id);
CREATE INDEX IF NOT EXISTS idx_relations_target ON object_relations(target_id);
CREATE INDEX IF NOT EXISTS idx_relations_attr ON object_relations(attribute_id);
`;

export const NORMALIZED_SCHEMA_SQLITE = `
-- SQLite version (same structure, different syntax)
CREATE TABLE IF NOT EXISTS object_types (
  jira_type_id INTEGER PRIMARY KEY,
  type_name TEXT NOT NULL UNIQUE,
  display_name TEXT NOT NULL,
  description TEXT,
  sync_priority INTEGER DEFAULT 0,
  object_count INTEGER DEFAULT 0,
  discovered_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- ... (similar for all tables, using INTEGER for booleans, TEXT for timestamps)
`;

2. Schema Discovery Service (Step 2)

File: backend/src/services/schemaDiscoveryService.ts

import { logger } from './logger.js';
import { createDatabaseAdapter } from './database/factory.js';
import type { DatabaseAdapter } from './database/interface.js';
import { OBJECT_TYPES } from '../generated/jira-schema.js';
import type { ObjectTypeDefinition, AttributeDefinition } from '../generated/jira-schema.js';

class SchemaDiscoveryService {
  private db: DatabaseAdapter;
  private isPostgres: boolean;
  private discovered: boolean = false;

  constructor() {
    this.db = createDatabaseAdapter();
    this.isPostgres = (process.env.DATABASE_TYPE === 'postgres' || process.env.DATABASE_TYPE === 'postgresql');
  }

  /**
   * Discover schema from OBJECT_TYPES and populate database
   */
  async discoverAndStoreSchema(): Promise<void> {
    if (this.discovered) {
      logger.debug('SchemaDiscovery: Schema already discovered, skipping');
      return;
    }

    logger.info('SchemaDiscovery: Discovering schema from OBJECT_TYPES...');

    await this.db.transaction(async (db) => {
      // Store object types
      for (const [typeName, typeDef] of Object.entries(OBJECT_TYPES)) {
        await db.execute(`
          INSERT INTO object_types (jira_type_id, type_name, display_name, description, sync_priority, object_count, discovered_at, updated_at)
          VALUES (?, ?, ?, ?, ?, ?, ?, ?)
          ON CONFLICT(jira_type_id) DO UPDATE SET
            display_name = excluded.display_name,
            description = excluded.description,
            sync_priority = excluded.sync_priority,
            object_count = excluded.object_count,
            updated_at = excluded.updated_at
        `, [
          typeDef.jiraTypeId,
          typeName,
          typeDef.name,
          null, // description
          typeDef.syncPriority,
          typeDef.objectCount,
          new Date().toISOString(),
          new Date().toISOString()
        ]);

        // Store attributes
        for (const attrDef of typeDef.attributes) {
          await db.execute(`
            INSERT INTO attributes (
              jira_attr_id, object_type_name, attr_name, field_name, attr_type,
              is_multiple, is_editable, is_required, is_system,
              reference_type_name, description, discovered_at
            )
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            ON CONFLICT(jira_attr_id, object_type_name) DO UPDATE SET
              attr_name = excluded.attr_name,
              field_name = excluded.field_name,
              attr_type = excluded.attr_type,
              is_multiple = excluded.is_multiple,
              is_editable = excluded.is_editable,
              is_required = excluded.is_required,
              is_system = excluded.is_system,
              reference_type_name = excluded.reference_type_name,
              description = excluded.description
          `, [
            attrDef.jiraId,
            typeName,
            attrDef.name,
            attrDef.fieldName,
            attrDef.type,
            attrDef.isMultiple,
            attrDef.isEditable,
            attrDef.isRequired,
            attrDef.isSystem,
            attrDef.referenceTypeName || null,
            attrDef.description || null,
            new Date().toISOString()
          ]);
        }
      }
    });

    this.discovered = true;
    logger.info('SchemaDiscovery: Schema discovery complete');
  }

  /**
   * Get attribute definition from database
   */
  async getAttribute(typeName: string, fieldName: string): Promise<AttributeDefinition | null> {
    const row = await this.db.queryOne<{
      jira_attr_id: number;
      attr_name: string;
      field_name: string;
      attr_type: string;
      is_multiple: boolean;
      is_editable: boolean;
      is_required: boolean;
      is_system: boolean;
      reference_type_name: string | null;
      description: string | null;
    }>(`
      SELECT * FROM attributes
      WHERE object_type_name = ? AND field_name = ?
    `, [typeName, fieldName]);

    if (!row) return null;

    return {
      jiraId: row.jira_attr_id,
      name: row.attr_name,
      fieldName: row.field_name,
      type: row.attr_type as AttributeDefinition['type'],
      isMultiple: row.is_multiple,
      isEditable: row.is_editable,
      isRequired: row.is_required,
      isSystem: row.is_system,
      referenceTypeName: row.reference_type_name || undefined,
      description: row.description || undefined,
    };
  }

  /**
   * Get all attributes for a type
   */
  async getAttributesForType(typeName: string): Promise<AttributeDefinition[]> {
    const rows = await this.db.query<{
      jira_attr_id: number;
      attr_name: string;
      field_name: string;
      attr_type: string;
      is_multiple: boolean;
      is_editable: boolean;
      is_required: boolean;
      is_system: boolean;
      reference_type_name: string | null;
      description: string | null;
    }>(`
      SELECT * FROM attributes
      WHERE object_type_name = ?
      ORDER BY jira_attr_id
    `, [typeName]);

    return rows.map(row => ({
      jiraId: row.jira_attr_id,
      name: row.attr_name,
      fieldName: row.field_name,
      type: row.attr_type as AttributeDefinition['type'],
      isMultiple: row.is_multiple,
      isEditable: row.is_editable,
      isRequired: row.is_required,
      isSystem: row.is_system,
      referenceTypeName: row.reference_type_name || undefined,
      description: row.description || undefined,
    }));
  }
}

export const schemaDiscoveryService = new SchemaDiscoveryService();

3. Normalization Logic (Step 3)

File: backend/src/services/normalizedCacheStore.ts (partial - normalization methods)

/**
 * Normalize a CMDBObject to database format
 */
private async normalizeObject(
  object: CMDBObject,
  typeName: CMDBObjectTypeName
): Promise<void> {
  // 1. Insert/update object record
  await this.db.execute(`
    INSERT INTO objects (id, object_key, object_type_name, label, jira_updated_at, jira_created_at, cached_at)
    VALUES (?, ?, ?, ?, ?, ?, ?)
    ON CONFLICT(id) DO UPDATE SET
      object_key = excluded.object_key,
      label = excluded.label,
      jira_updated_at = excluded.jira_updated_at,
      cached_at = excluded.cached_at
  `, [
    object.id,
    object.objectKey,
    typeName,
    object.label,
    object._jiraUpdatedAt || null,
    object._jiraCreatedAt || null,
    new Date().toISOString()
  ]);

  // 2. Get all attributes for this type
  const attributes = await schemaDiscoveryService.getAttributesForType(typeName);

  // 3. Delete existing attribute values
  await this.db.execute(`
    DELETE FROM attribute_values WHERE object_id = ?
  `, [object.id]);

  // 4. Store each attribute value
  for (const attrDef of attributes) {
    const value = (object as unknown as Record<string, unknown>)[attrDef.fieldName];
    await this.storeAttributeValue(object.id, attrDef, value);
  }
}

/**
 * Store attribute value based on type
 */
private async storeAttributeValue(
  objectId: string,
  attrDef: AttributeDefinition,
  value: unknown
): Promise<void> {
  // Get attribute ID from database
  const attrRow = await this.db.queryOne<{ id: number }>(`
    SELECT id FROM attributes
    WHERE object_type_name = ? AND field_name = ?
  `, [attrDef.objectTypeName || '', attrDef.fieldName]);

  if (!attrRow) {
    logger.warn(`NormalizedCacheStore: Attribute not found: ${attrDef.fieldName}`);
    return;
  }

  const attributeId = attrRow.id;

  // Handle null/undefined
  if (value === null || value === undefined) {
    return; // Don't store null values
  }

  // Handle arrays
  if (attrDef.isMultiple && Array.isArray(value)) {
    for (let i = 0; i < value.length; i++) {
      await this.insertAttributeValue(objectId, attributeId, value[i], attrDef, i);
    }
    return;
  }

  // Single value
  await this.insertAttributeValue(objectId, attributeId, value, attrDef, 0);
}

/**
 * Insert one attribute value row
 */
private async insertAttributeValue(
  objectId: string,
  attributeId: number,
  value: unknown,
  attrDef: AttributeDefinition,
  arrayIndex: number
): Promise<void> {
  switch (attrDef.type) {
    case 'reference': {
      const ref = value as ObjectReference;
      if (ref?.objectId) {
        await this.db.execute(`
          INSERT INTO attribute_values (object_id, attribute_id, reference_object_id, array_index)
          VALUES (?, ?, ?, ?)
        `, [objectId, attributeId, ref.objectId, arrayIndex]);
      }
      break;
    }

    case 'text':
    case 'textarea':
    case 'url':
    case 'email':
    case 'select':
    case 'user':
    case 'status': {
      const textValue = String(value);
      await this.db.execute(`
        INSERT INTO attribute_values (object_id, attribute_id, text_value, array_index)
        VALUES (?, ?, ?, ?)
      `, [objectId, attributeId, textValue, arrayIndex]);
      break;
    }

    case 'integer':
    case 'float': {
      const numValue = Number(value);
      if (!isNaN(numValue)) {
        await this.db.execute(`
          INSERT INTO attribute_values (object_id, attribute_id, number_value, array_index)
          VALUES (?, ?, ?, ?)
        `, [objectId, attributeId, numValue, arrayIndex]);
      }
      break;
    }

    case 'boolean': {
      const boolValue = Boolean(value);
      await this.db.execute(`
        INSERT INTO attribute_values (object_id, attribute_id, boolean_value, array_index)
        VALUES (?, ?, ?, ?)
      `, [objectId, attributeId, boolValue, arrayIndex]);
      break;
    }

    case 'date': {
      const dateValue = value as string; // ISO date string
      await this.db.execute(`
        INSERT INTO attribute_values (object_id, attribute_id, date_value, array_index)
        VALUES (?, ?, ?, ?)
      `, [objectId, attributeId, dateValue, arrayIndex]);
      break;
    }

    case 'datetime': {
      const datetimeValue = value as string; // ISO datetime string
      await this.db.execute(`
        INSERT INTO attribute_values (object_id, attribute_id, datetime_value, array_index)
        VALUES (?, ?, ?, ?)
      `, [objectId, attributeId, datetimeValue, arrayIndex]);
      break;
    }

    default: {
      // Fallback to text
      const textValue = String(value);
      await this.db.execute(`
        INSERT INTO attribute_values (object_id, attribute_id, text_value, array_index)
        VALUES (?, ?, ?, ?)
      `, [objectId, attributeId, textValue, arrayIndex]);
    }
  }
}

4. Reconstruction Logic (Step 4)

File: backend/src/services/normalizedCacheStore.ts (partial - reconstruction methods)

/**
 * Reconstruct CMDBObject from normalized data
 */
private async reconstructObject<T extends CMDBObject>(
  objectId: string,
  typeName: CMDBObjectTypeName
): Promise<T | null> {
  // 1. Get object record
  const objRow = await this.db.queryOne<{
    id: string;
    object_key: string;
    label: string;
    jira_updated_at: string | null;
    jira_created_at: string | null;
  }>(`
    SELECT id, object_key, label, jira_updated_at, jira_created_at
    FROM objects
    WHERE id = ? AND object_type_name = ?
  `, [objectId, typeName]);

  if (!objRow) return null;

  // 2. Load all attribute values
  const attributeValues = await this.loadAttributeValues(objectId, typeName);

  // 3. Build CMDBObject
  const result: Record<string, unknown> = {
    id: objRow.id,
    objectKey: objRow.object_key,
    label: objRow.label,
    _objectType: typeName,
    _jiraUpdatedAt: objRow.jira_updated_at || new Date().toISOString(),
    _jiraCreatedAt: objRow.jira_created_at || new Date().toISOString(),
    ...attributeValues
  };

  return result as T;
}

/**
 * Load all attribute values for an object
 */
private async loadAttributeValues(
  objectId: string,
  typeName: CMDBObjectTypeName
): Promise<Record<string, unknown>> {
  // Get all attributes for this type
  const attributes = await schemaDiscoveryService.getAttributesForType(typeName);
  
  // Get all attribute values
  const valueRows = await this.db.query<{
    attribute_id: number;
    field_name: string;
    attr_type: string;
    is_multiple: boolean;
    text_value: string | null;
    number_value: number | null;
    boolean_value: boolean | null;
    date_value: string | null;
    datetime_value: string | null;
    reference_object_id: string | null;
    array_index: number;
  }>(`
    SELECT 
      av.attribute_id,
      a.field_name,
      a.attr_type,
      a.is_multiple,
      av.text_value,
      av.number_value,
      av.boolean_value,
      av.date_value,
      av.datetime_value,
      av.reference_object_id,
      av.array_index
    FROM attribute_values av
    JOIN attributes a ON av.attribute_id = a.id
    WHERE av.object_id = ?
    ORDER BY a.field_name, av.array_index
  `, [objectId]);

  // Group by field name
  const result: Record<string, unknown> = {};

  for (const row of valueRows) {
    const fieldName = row.field_name;
    const isMultiple = row.is_multiple;

    // Get value based on type
    let value: unknown = null;
    switch (row.attr_type) {
      case 'reference':
        if (row.reference_object_id) {
          // Load referenced object details
          const refObj = await this.getObjectById(row.reference_object_id);
          if (refObj) {
            value = {
              objectId: refObj.id,
              objectKey: refObj.objectKey,
              label: refObj.label,
            };
          }
        }
        break;
      case 'text':
      case 'textarea':
      case 'url':
      case 'email':
      case 'select':
      case 'user':
      case 'status':
        value = row.text_value;
        break;
      case 'integer':
      case 'float':
        value = row.number_value;
        break;
      case 'boolean':
        value = row.boolean_value;
        break;
      case 'date':
        value = row.date_value;
        break;
      case 'datetime':
        value = row.datetime_value;
        break;
      default:
        value = row.text_value;
    }

    // Handle arrays vs single values
    if (isMultiple) {
      if (!result[fieldName]) {
        result[fieldName] = [];
      }
      (result[fieldName] as unknown[]).push(value);
    } else {
      result[fieldName] = value;
    }
  }

  return result;
}

/**
 * Helper to get object by ID (for references)
 */
private async getObjectById(id: string): Promise<{
  id: string;
  objectKey: string;
  label: string;
} | null> {
  const row = await this.db.queryOne<{
    id: string;
    object_key: string;
    label: string;
  }>(`
    SELECT id, object_key, label
    FROM objects
    WHERE id = ?
  `, [id]);

  return row;
}

5. Query Builder (Step 6)

File: backend/src/services/queryBuilder.ts

import { logger } from './logger.js';
import { schemaDiscoveryService } from './schemaDiscoveryService.js';
import type { CMDBObjectTypeName } from '../generated/jira-types.js';
import type { AttributeDefinition } from '../generated/jira-schema.js';

class QueryBuilder {
  /**
   * Build WHERE clause from filters
   */
  async buildWhereClause(
    filters: Record<string, unknown>,
    typeName: CMDBObjectTypeName
  ): Promise<{ whereClause: string; params: unknown[] }> {
    const conditions: string[] = ['o.object_type_name = ?'];
    const params: unknown[] = [typeName];
    let paramIndex = 2;

    for (const [fieldName, filterValue] of Object.entries(filters)) {
      if (filterValue === undefined || filterValue === null) continue;

      const attrDef = await schemaDiscoveryService.getAttribute(typeName, fieldName);
      if (!attrDef) {
        logger.debug(`QueryBuilder: Unknown field ${fieldName} for type ${typeName}, skipping`);
        continue;
      }

      const condition = this.buildFilterCondition(fieldName, filterValue, attrDef, paramIndex);
      if (condition.condition) {
        conditions.push(condition.condition);
        params.push(...condition.params);
        paramIndex += condition.params.length;
      }
    }

    const whereClause = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : '';
    return { whereClause, params };
  }

  /**
   * Build filter condition for one field
   */
  buildFilterCondition(
    fieldName: string,
    filterValue: unknown,
    attrDef: AttributeDefinition,
    startParamIndex: number
  ): { condition: string; params: unknown[] } {
    // Handle special operators
    if (typeof filterValue === 'object' && filterValue !== null && !Array.isArray(filterValue)) {
      const filterObj = filterValue as Record<string, unknown>;

      // Exists check
      if (filterObj.exists === true) {
        return {
          condition: `EXISTS (
            SELECT 1 FROM attribute_values av
            JOIN attributes a ON av.attribute_id = a.id
            WHERE av.object_id = o.id AND a.field_name = ?
          )`,
          params: [fieldName]
        };
      }

      // Empty check
      if (filterObj.empty === true) {
        return {
          condition: `NOT EXISTS (
            SELECT 1 FROM attribute_values av
            JOIN attributes a ON av.attribute_id = a.id
            WHERE av.object_id = o.id AND a.field_name = ?
          )`,
          params: [fieldName]
        };
      }

      // Contains (text search)
      if (filterObj.contains !== undefined && typeof filterObj.contains === 'string') {
        if (attrDef.type === 'text' || attrDef.type === 'textarea') {
          return {
            condition: `EXISTS (
              SELECT 1 FROM attribute_values av
              JOIN attributes a ON av.attribute_id = a.id
              WHERE av.object_id = o.id 
                AND a.field_name = ?
                AND LOWER(av.text_value) LIKE LOWER(?)
            )`,
            params: [fieldName, `%${filterObj.contains}%`]
          };
        }
      }

      // Reference filters
      if (attrDef.type === 'reference') {
        if (filterObj.objectId !== undefined) {
          return {
            condition: `EXISTS (
              SELECT 1 FROM attribute_values av
              JOIN attributes a ON av.attribute_id = a.id
              WHERE av.object_id = o.id 
                AND a.field_name = ?
                AND av.reference_object_id = ?
            )`,
            params: [fieldName, String(filterObj.objectId)]
          };
        }
        if (filterObj.objectKey !== undefined) {
          // Need to join with objects table to get objectKey
          return {
            condition: `EXISTS (
              SELECT 1 FROM attribute_values av
              JOIN attributes a ON av.attribute_id = a.id
              JOIN objects ref_obj ON av.reference_object_id = ref_obj.id
              WHERE av.object_id = o.id 
                AND a.field_name = ?
                AND ref_obj.object_key = ?
            )`,
            params: [fieldName, String(filterObj.objectKey)]
          };
        }
      }
    }

    // Handle array filters (for multiple reference fields)
    if (attrDef.isMultiple && Array.isArray(filterValue)) {
      if (attrDef.type === 'reference') {
        const conditions: string[] = [];
        const params: unknown[] = [];
        for (const val of filterValue) {
          if (typeof val === 'object' && val !== null) {
            const ref = val as { objectId?: string; objectKey?: string };
            if (ref.objectId) {
              conditions.push(`EXISTS (
                SELECT 1 FROM attribute_values av
                JOIN attributes a ON av.attribute_id = a.id
                WHERE av.object_id = o.id 
                  AND a.field_name = ?
                  AND av.reference_object_id = ?
              )`);
              params.push(fieldName, ref.objectId);
            }
          }
        }
        if (conditions.length > 0) {
          return { condition: `(${conditions.join(' OR ')})`, params };
        }
      }
    }

    // Simple value filters
    if (attrDef.type === 'reference') {
      if (typeof filterValue === 'object' && filterValue !== null) {
        const ref = filterValue as { objectId?: string; objectKey?: string; label?: string };
        if (ref.objectId) {
          return {
            condition: `EXISTS (
              SELECT 1 FROM attribute_values av
              JOIN attributes a ON av.attribute_id = a.id
              WHERE av.object_id = o.id 
                AND a.field_name = ?
                AND av.reference_object_id = ?
            )`,
            params: [fieldName, ref.objectId]
          };
        }
      }
    } else if (attrDef.type === 'text' || attrDef.type === 'textarea') {
      return {
        condition: `EXISTS (
          SELECT 1 FROM attribute_values av
          JOIN attributes a ON av.attribute_id = a.id
          WHERE av.object_id = o.id 
            AND a.field_name = ?
            AND av.text_value = ?
        )`,
        params: [fieldName, String(filterValue)]
      };
    } else if (attrDef.type === 'integer' || attrDef.type === 'float') {
      return {
        condition: `EXISTS (
          SELECT 1 FROM attribute_values av
          JOIN attributes a ON av.attribute_id = a.id
          WHERE av.object_id = o.id 
            AND a.field_name = ?
            AND av.number_value = ?
        )`,
        params: [fieldName, Number(filterValue)]
      };
    } else if (attrDef.type === 'boolean') {
      return {
        condition: `EXISTS (
          SELECT 1 FROM attribute_values av
          JOIN attributes a ON av.attribute_id = a.id
          WHERE av.object_id = o.id 
            AND a.field_name = ?
            AND av.boolean_value = ?
        )`,
        params: [fieldName, Boolean(filterValue)]
      };
    }

    return { condition: '', params: [] };
  }

  /**
   * Build ORDER BY clause
   */
  buildOrderBy(orderBy?: string, orderDir?: 'ASC' | 'DESC'): string {
    const safeOrderBy = ['id', 'object_key', 'object_type_name', 'label', 'cached_at'].includes(orderBy || '')
      ? (orderBy || 'label')
      : 'label';
    const safeOrderDir = orderDir === 'DESC' ? 'DESC' : 'ASC';
    return `ORDER BY o.${safeOrderBy} ${safeOrderDir}`;
  }

  /**
   * Build pagination clause
   */
  buildPagination(limit?: number, offset?: number): string {
    const limitValue = limit || 100;
    const offsetValue = offset || 0;
    return `LIMIT ${limitValue} OFFSET ${offsetValue}`;
  }
}

export const queryBuilder = new QueryBuilder();

6. Query Operations (Step 7)

File: backend/src/services/normalizedCacheStore.ts (partial - query methods)

/**
 * Query objects with filters
 */
async queryWithFilters<T extends CMDBObject>(
  typeName: CMDBObjectTypeName,
  filters: Record<string, unknown>,
  options?: QueryOptions
): Promise<{ objects: T[]; total: number }> {
  await this.ensureInitialized();

  // Build WHERE clause
  const { whereClause, params: whereParams } = await queryBuilder.buildWhereClause(filters, typeName);

  // Build ORDER BY
  const orderBy = queryBuilder.buildOrderBy(options?.orderBy, options?.orderDir);

  // Build pagination
  const pagination = queryBuilder.buildPagination(options?.limit, options?.offset);

  // Count query
  const countQuery = `
    SELECT COUNT(DISTINCT o.id) as count
    FROM objects o
    ${whereClause}
  `;

  // Data query
  const dataQuery = `
    SELECT DISTINCT o.id
    FROM objects o
    ${whereClause}
    ${orderBy}
    ${pagination}
  `;

  try {
    // Execute both queries
    const [countResult, dataResult] = await Promise.all([
      this.db.queryOne<{ count: number }>(countQuery, whereParams),
      this.db.query<{ id: string }>(dataQuery, whereParams),
    ]);

    const total = countResult?.count || 0;

    // Reconstruct objects
    const objects = await Promise.all(
      dataResult.map(row => this.reconstructObject<T>(row.id, typeName))
    );

    return {
      objects: objects.filter((obj): obj is T => obj !== null),
      total
    };
  } catch (error) {
    logger.error(`NormalizedCacheStore: Query failed for ${typeName}`, error);
    return { objects: [], total: 0 };
  }
}

Implementation Checklist

Before starting implementation, verify:

• Green field confirmed (no production data)
• Database adapter supports both PostgreSQL and SQLite
• Schema discovery can read from OBJECT_TYPES
• All attribute types can be normalized
• Reconstruction logic handles all types
• Query builder supports all filter types
• Interface matches existing cacheStore exactly

Critical Implementation Notes

1. Schema Discovery Must Run First

   • Call schemaDiscoveryService.discoverAndStoreSchema() before any operations
   • Can be done in initialize() method

2. Reference Loading

   • When reconstructing references, may need to load referenced objects
   • Consider caching referenced objects to avoid N+1 queries

3. Transaction Safety

   • Use transactions for upsertObject() to ensure atomicity
   • Delete old values before inserting new ones

4. Error Handling

   • Handle missing attributes gracefully
   • Log warnings but don't fail entire operation

5. Performance

   • Batch operations where possible
   • Use indexes effectively
   • Consider caching attribute definitions

---

End of Plan