Location Template Configuration - Schema Adjustments

Overview

Documentation of adjustments made to align the backend implementation with the actual database schema.

Database Schema Differences

Actual Database Schema

The location_template_config table has these columns:

• id - UUID primary key
• location_id - UUID (references location)
• document_type - VARCHAR
• document_template_id - UUID (references document_template)
• printer_config - JSONB (flexible configuration storage)
• auto_print - BOOLEAN
• is_active - BOOLEAN
• created_at - TIMESTAMPTZ
• created_by - UUID
• updated_at - TIMESTAMPTZ
• updated_by - UUID

Missing Fields (Originally Planned)

• ❌ business_id - Not in schema (location already has business relationship)
• ❌ copies - Not a column (stored in printer_config JSONB instead)

Implementation Adjustments

1. Field Storage Strategy

Copies Field:

• Before: Separate copies column
• After: Stored in printerConfig JSONB object
• Reason: Schema uses flexible JSONB for all printer-related settings

Business ID:

• Before: Separate business_id column
• After: Removed (location relationship implies business)
• Reason: Redundant - location is already tied to a business

2. Data Transformation

Backend Response Transformer

private transformResponse(entity: LocationTemplateConfigEntity) {
  const printerConfig = entity.printerConfig as Record<string, unknown> | null;
  
  return {
    ...entity,
    templateId: entity.documentTemplateId, // Alias for frontend
    copies: (printerConfig?.copies as number) || 1, // Extract from JSONB
    printerProfileId: printerConfig?.printerProfileId as string | undefined,
    template: entity.templateName ? {
      name: entity.templateName,
      version: entity.templateVersion,
      isDefault: entity.templateIsDefault,
    } : undefined,
  };
}

What it does:

1. Extracts copies from printerConfig JSONB
2. Adds templateId alias (frontend expects this)
3. Extracts printerProfileId from printerConfig
4. Builds template object from joined fields

Frontend Service Mapping

Create/Update: Stores copies in printerConfig

printerConfig: {
  printerProfileId: data.printerProfileId,
  copies: data.copies  // Store here instead of top-level
}

Response: Extracts copies back to top-level for UI

copies: (printerConfig?.copies as number) || 1

3. API Changes

Removed Query Parameters

• ❌ businessId from all GET endpoints
• Location already has business relationship via FK

Updated Endpoints

Before:

GET /pdf/location-template-configs?locationId=xxx&businessId=xxx
GET /pdf/location-template-configs/:id?businessId=xxx
DELETE /pdf/location-template-configs/:id?businessId=xxx

After:

GET /pdf/location-template-configs?locationId=xxx
GET /pdf/location-template-configs/:id
DELETE /pdf/location-template-configs/:id

4. Validation Changes

DTO Updates:

• Removed businessId from DTOs
• Removed copies from DTOs (handled in printerConfig)
• Kept printerConfig as flexible JSONB object

Domain Entity:

• Removed businessId field
• Removed copies field
• Removed copies validation from isValid()

5. Repository Query Optimization

Template Join:

.selectFrom("locationTemplateConfig as ltc")
.leftJoin("documentTemplate as dt", "ltc.documentTemplateId", "dt.id")
.selectAll("ltc")
.select([
  "dt.name as templateName",
  "dt.version as templateVersion",
  "dt.isDefault as templateIsDefault",
])

Benefits:

• Single query returns config + template info
• No N+1 query problem
• Frontend gets template name/version without extra calls

Frontend Compatibility Layer

Service Layer Handles Mapping

Frontend → Backend (Create):

Frontend sends:
{
  templateId: "uuid",
  copies: 2,
  printerProfileId: "uuid"
}

Backend receives:
{
  documentTemplateId: "uuid",
  printerConfig: {
    copies: 2,
    printerProfileId: "uuid"
  }
}

Backend → Frontend (Response):

Backend returns:
{
  documentTemplateId: "uuid",
  printerConfig: { copies: 2, printerProfileId: "uuid" },
  templateName: "Sales Receipt",
  templateVersion: 1
}

Frontend receives:
{
  documentTemplateId: "uuid",
  templateId: "uuid",  // Alias added
  copies: 2,           // Extracted
  printerProfileId: "uuid",  // Extracted
  template: {
    name: "Sales Receipt",
    version: 1
  }
}

Frontend Components Unchanged

The UI components continue to work with:

• copies as a top-level field
• templateId as the template reference
• All existing prop interfaces

The service layer transparently handles the mapping, so no component changes needed.

Benefits of This Approach

1. Schema Flexibility

✅ printerConfig JSONB can store any printer-related settings ✅ Easy to add new printer options without schema changes ✅ Different printer types can have different config structures

2. Data Normalization

✅ No redundant businessId storage ✅ Location → Business relationship already established ✅ Cleaner schema with less redundancy

3. Performance

✅ Single query with JOIN gets all needed data ✅ No extra queries for template info ✅ Frontend gets complete data in one request

4. Backward Compatibility

✅ Frontend components don't need changes ✅ Service layer handles all transformations ✅ Type safety maintained throughout

Testing Considerations

Test JSONB Storage

# Copies stored in printerConfig
curl -X POST /pdf/location-template-configs \
  -d '{
    "printerConfig": {
      "copies": 2,
      "printerProfileId": "uuid",
      "otherSettings": "value"
    }
  }'

Verify Extraction

# Response should have copies extracted
GET /pdf/location-template-configs?locationId=xxx

Response:
{
  "copies": 2,  # Extracted from printerConfig
  "printerConfig": { "copies": 2, ... }  # Original JSONB
}

Migration Path (If Needed)

If you need to add copies or businessId as columns later:

-- Add copies column
ALTER TABLE location_template_config 
ADD COLUMN copies INTEGER DEFAULT 1;

-- Migrate data from JSONB
UPDATE location_template_config 
SET copies = COALESCE((printer_config->>'copies')::INTEGER, 1);

-- Add business_id if needed
ALTER TABLE location_template_config 
ADD COLUMN business_id UUID REFERENCES business(id);

-- Populate from location relationship
UPDATE location_template_config ltc
SET business_id = l.business_id
FROM location l
WHERE ltc.location_id = l.id;

Conclusion

The implementation successfully adapts to the actual database schema while maintaining:

• ✅ Full type safety
• ✅ Clean separation of concerns
• ✅ Frontend compatibility
• ✅ Flexible printer configuration
• ✅ Optimal query performance
• ✅ No breaking changes to UI

The JSONB approach for printerConfig provides maximum flexibility for future printer-related features without requiring schema changes.