Skip to main content

ADR-0033: JSONB-Based Field Mapping Configuration

Status​

Accepted - 2025-01-26

Context​

Channel APIs (Hostaway, Airbnb, VRBO) use different field names, formats, and structures requiring flexible mapping between external and TVL schemas.

Example:

  • Hostaway: checkInDate (ISO string)
  • Airbnb: check_in (YYYY-MM-DD)
  • VRBO: arrival_date (Unix timestamp)

Decision​

JSONB field mapping configuration stored in channel_configurations table.

Rationale​

  1. No Code Changes: Add channels via config, not code
  2. Runtime Flexibility: Change mappings without redeployment
  3. Versioning: Track mapping changes over time
  4. Multi-Tenant: Per-org custom mappings

Alternatives Considered​

Alternative 1: Hardcoded Mappings​

Rejected - Requires code changes for every field, poor maintainability

Alternative 2: YAML/JSON Files​

Rejected - Harder to version, no multi-tenant support, requires redeployment

Alternative 3: External Config Service​

Rejected - Adds latency, dependency, overkill for MVP

Implementation​

1. Channel Configurations Table​

CREATE TABLE channel_configurations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  channel VARCHAR(50) NOT NULL, -- 'hostaway', 'airbnb', 'vrbo'
  version VARCHAR(20) NOT NULL DEFAULT '1.0', -- API version
  field_mappings JSONB NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE(org_id, channel, version)
);

CREATE INDEX idx_channel_configs_org_channel ON channel_configurations(org_id, channel);

2. Field Mapping JSON Schema​

// src/integrations/FieldMapping.ts
export interface FieldMapping {
  externalField: string; // Hostaway field name
  internalField: string; // TVL field name
  transform?: 'date' | 'cents' | 'boolean' | 'split'; // Data transformation
  default?: any; // Default value if missing
}

export interface ChannelConfiguration {
  channel: string;
  version: string;
  fieldMappings: {
    property: FieldMapping[];
    booking: FieldMapping[];
    availability: FieldMapping[];
  };
}

3. Example Configuration (Hostaway)​

{
  "channel": "hostaway",
  "version": "1.0",
  "fieldMappings": {
    "booking": [
      {
        "externalField": "id",
        "internalField": "externalId",
        "transform": null
      },
      {
        "externalField": "guestName",
        "internalField": "guestName",
        "transform": null
      },
      {
        "externalField": "checkInDate",
        "internalField": "checkIn",
        "transform": "date"
      },
      {
        "externalField": "checkOutDate",
        "internalField": "checkOut",
        "transform": "date"
      },
      {
        "externalField": "totalPrice",
        "internalField": "totalCents",
        "transform": "cents"
      },
      {
        "externalField": "status",
        "internalField": "status",
        "transform": null,
        "default": "pending"
      }
    ]
  }
}

4. Field Mapper Service​

// src/integrations/FieldMapper.ts
export class FieldMapper {
  constructor(private config: ChannelConfiguration) {}

  mapExternalToInternal(resource: string, externalData: any): any {
    const mappings = this.config.fieldMappings[resource];
    const internal: any = {};

    for (const mapping of mappings) {
      let value = externalData[mapping.externalField] ?? mapping.default;

      // Apply transformation
      if (mapping.transform === 'date') {
        value = new Date(value).toISOString();
      } else if (mapping.transform === 'cents') {
        value = Math.round(value * 100); // $50.00 → 5000 cents
      } else if (mapping.transform === 'boolean') {
        value = Boolean(value);
      }

      internal[mapping.internalField] = value;
    }

    return internal;
  }

  mapInternalToExternal(resource: string, internalData: any): any {
    const mappings = this.config.fieldMappings[resource];
    const external: any = {};

    for (const mapping of mappings) {
      let value = internalData[mapping.internalField];

      // Reverse transformation
      if (mapping.transform === 'cents') {
        value = value / 100; // 5000 cents → $50.00
      }

      external[mapping.externalField] = value;
    }

    return external;
  }
}

// Usage
const config = await db.query.channelConfigurations.findFirst({
  where: and(
    eq(channelConfigurations.orgId, orgId),
    eq(channelConfigurations.channel, 'hostaway')
  ),
});

const mapper = new FieldMapper(config.fieldMappings);

const internalBooking = mapper.mapExternalToInternal('booking', hostawayBooking);

Configuration Management​

Seed Default Configurations​

// migrations/seed-channel-configs.ts
await db.insert(channelConfigurations).values([
  {
    orgId: 'default',
    channel: 'hostaway',
    version: '1.0',
    fieldMappings: HOSTAWAY_DEFAULT_MAPPINGS,
  },
  {
    orgId: 'default',
    channel: 'airbnb',
    version: '2.0',
    fieldMappings: AIRBNB_DEFAULT_MAPPINGS,
  },
]);

Per-Org Overrides​

// Org can customize field mappings
await db.insert(channelConfigurations).values({
  orgId: 'org-123',
  channel: 'hostaway',
  version: '1.0',
  fieldMappings: {
    ...HOSTAWAY_DEFAULT_MAPPINGS,
    booking: [
      ...HOSTAWAY_DEFAULT_MAPPINGS.booking,
      {
        externalField: 'customField',
        internalField: 'metadata.customField',
      },
    ],
  },
});

Consequences​

Positive​

  • ✅ No Code Deploys: Change mappings via database
  • ✅ Multi-Tenant: Per-org custom mappings
  • ✅ Versioning: Track changes over time
  • ✅ Testable: Easy to test with different configs

Negative​

  • ❌ No Type Safety: JSONB not validated at compile time
  • ❌ Complex Transforms: Limited to simple transformations

Mitigations​

  • Validate JSONB with Zod schema on insert/update
  • Use TypeScript types for config structure
  • Document complex transforms in code comments

Validation Checklist​

  • channel_configurations table created
  • Default configs seeded for all channels
  • FieldMapper service implemented
  • Zod validation for JSONB schema
  • Per-org override support

References​