Skip to main content

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.