SQL-to-FE Adapter Guide

The adapter layer (src/adapters/) bridges the gap between the backend SQL schema and the frontend TypeScript types. It handles three major transformations: ID system mapping, status enum conversion, and naming convention translation.

Why Adapters Exist

The backend and frontend were developed independently, resulting in systematic differences:

Aspect	SQL Backend	FE Types
Primary key	bigint auto-increment id + separate uuid column	UUID string as id
Naming	snake_case	camelCase
Enums	UPPER_CASE strings	lower_case string literals
Status values	Different semantic meanings	Different semantic meanings
Structure	Flat tables with joins	Nested objects with embedded data
Timestamps	created_at, updated_at	createdAt, updatedAt

Foundation: mapSqlBase

The idAdapter.ts file provides the core transformation that every entity adapter uses:

// src/adapters/idAdapter.ts

interface SqlBaseEntity {
  id: number          // bigint PK (ignored by adapter)
  uuid: string        // UUID column (promoted to FE id)
  created_at: string | null
  updated_at: string | null
  deleted_at?: string | null
}

// Promotes uuid → id, converts snake_case timestamps
function mapSqlBase<T extends SqlBaseEntity>(sql: T): {
  id: string           // sql.uuid
  createdAt: string    // sql.created_at
  updatedAt: string    // sql.updated_at
  ...rest              // remaining fields
}

Usage:

const feEntity = mapSqlBase(sqlRow)
// sqlRow.id (bigint) → discarded
// sqlRow.uuid → feEntity.id
// sqlRow.created_at → feEntity.createdAt
// sqlRow.updated_at → feEntity.updatedAt

Additional Utilities

// Map an array of SQL entities
function mapSqlBases<T extends SqlBaseEntity>(entities: T[]): Array<ReturnType<typeof mapSqlBase<T>>>

// Convert a single snake_case key to camelCase
function snakeToCamel(str: string): string
// 'created_at' → 'createdAt'

// Convert all keys in an object (shallow)
function mapSnakeToCamel<T extends Record<string, unknown>>(obj: T): Record<string, unknown>

Status Maps

The statusMaps.ts file provides bidirectional mapping functions for every status enum that differs between SQL and FE. Each domain exports toFe() and toSql() functions with safe fallbacks for unknown values.

Challenge Status

SQL:  INACTIVE  →  FE: draft
SQL:  ACTIVE    →  FE: approved
SQL:  PENDING   →  FE: pending_review

FE: draft           →  SQL: INACTIVE
FE: pending_review  →  SQL: PENDING
FE: approved        →  SQL: ACTIVE
FE: rejected        →  SQL: PENDING (no SQL equivalent)
FE: archived        →  SQL: INACTIVE (no SQL equivalent)

mapChallengeStatusToFe('ACTIVE')    // → 'approved'
mapChallengeStatusToSql('approved') // → 'ACTIVE'

Challenge Instance Status

SQL: ACTIVE   →  FE: active
SQL: INACTIVE →  FE: inactive
SQL: DRAFT    →  FE: draft

Order Status

SQL: UPCOMING    →  FE: pending
SQL: INPROGRESS  →  FE: confirmed
SQL: COMPLETED   →  FE: fulfilled

FE: cancelled/refunded have no SQL equivalent (map to COMPLETED)

Vendor Account Status

SQL: ACTIVE   →  FE: active
SQL: INACTIVE →  FE: inactive
SQL: BLOCKED  →  FE: suspended
SQL: FREEZED  →  FE: suspended

Vendor Approval Status

SQL: PENDING              →  FE: pending
SQL: APPROVED             →  FE: approved
SQL: REJECTED             →  FE: rejected
SQL: AWAITING_MODIFICATION → FE: awaiting_modification
SQL: AWAITING_AGREEMENT   →  FE: awaiting_agreement

Difficulty Level

SQL: 0  →  FE: beginner
SQL: 1  →  FE: intermediate
SQL: 2  →  FE: advanced
SQL: 3  →  FE: expert

mapDifficultyToFe(2)           // → 'advanced'
mapDifficultyToSql('advanced') // → 2

Gift Status

SQL: pending   →  FE: pending
SQL: accepted  →  FE: accepted
SQL: rejected  →  FE: declined
SQL: expired   →  FE: expired

FE: cancelled has no SQL equivalent (maps to 'pending')

Revenue Type

SQL: AFFILIATE     →  FE: affiliate
SQL: DIRECT        →  FE: direct
SQL: SUBSCRIPTION  →  FE: subscription

Commission Type

SQL: AFFILIATE_MARKETING_PROGRAM    →  FE: affiliate_marketing_program
SQL: DIRECT_COMMISSION_AGREEMENT    →  FE: direct_commission_agreement
SQL: SUBSCRIPTION_FEE               →  FE: subscription_fee

Challenge Type (SQL) to FE Fulfillment Type

SQL: HOSTED      →  FE: hosted
SQL: ONLINE      →  FE: digitally_guided
SQL: KIT         →  FE: kit
SQL: AUTONOMOUS  →  FE: digitally_guided
SQL: AFFILIATE   →  FE: hosted

Portfolio Status

SQL: UPCOMING    →  FE: invited
SQL: INPROGRESS  →  FE: in_progress
SQL: COMPLETED   →  FE: verified

Wallet Status / Type / Payout Status

Wallet status: PENDING/COMPLETED → pending/completed
Wallet type: IN/OUT → in/out
Payout status: PENDING/PAID/IN-PROGRESS/CANCELLED → pending/paid/in_progress/cancelled

Series Status

SQL: ACTIVE    →  FE: published
SQL: INACTIVE  →  FE: draft

Per-Entity Adapters

Each entity adapter composes mapSqlBase with domain-specific field mappings:

Adapter File	Exports	SQL Type	FE Type	Feature Guide
challengeAdapter.ts	adaptChallenge, adaptChallengeInstance	SqlChallenge, SqlChallengeInstance	Challenge, ChallengeInstance	Challenges
vendorAdapter.ts	adaptVendor, adaptVendorListItem	SqlVendor, SqlAddress	Vendor FE types	Vendor
seriesAdapter.ts	adaptSeries, adaptSeriesOrder	SqlSeries, SqlSeriesOrder	Series types	Challenges
portfolioAdapter.ts	adaptPortfolioWithoutOrder, adaptPortfolioToTrackRecordShell	SqlPortfolioWithoutOrder	Portfolio/TR types	Track Records
walletAdapter.ts	adaptWalletTransaction, adaptPayout, buildWalletSummary	SqlWallet, SqlPayout	Wallet FE types	Vendor

Barrel Export

Everything is re-exported from src/adapters/index.ts:

// Foundation
export { mapSqlBase, mapSqlBases, snakeToCamel, mapSnakeToCamel } from './idAdapter'
export type { SqlBaseEntity } from './idAdapter'

// Status mappings (18 functions)
export {
  mapChallengeStatusToFe, mapChallengeStatusToSql,
  mapInstanceStatusToFe,
  mapOrderStatusToFe, mapOrderStatusToSql,
  mapVendorStatusToFe, mapVendorStatusToSql,
  mapApprovalStatusToFe,
  mapDifficultyToFe, mapDifficultyToSql,
  mapGiftStatusToFe, mapGiftStatusToSql,
  mapRevenueTypeToFe,
  mapCommissionTypeToFe,
  mapChallengeTypeToFe,
  mapPortfolioStatusToFe,
  mapWalletStatusToFe, mapWalletTypeToFe,
  mapPayoutStatusToFe,
  mapSeriesStatusToFe,
} from './statusMaps'

// Entity adapters
export { adaptChallenge, adaptChallengeInstance } from './challengeAdapter'
export { adaptVendor, adaptVendorListItem } from './vendorAdapter'
export { adaptSeries, adaptSeriesOrder } from './seriesAdapter'
export { adaptPortfolioWithoutOrder, adaptPortfolioToTrackRecordShell } from './portfolioAdapter'
export { adaptWalletTransaction, adaptPayout, buildWalletSummary } from './walletAdapter'

How to Add a New Adapter

1. Define the SQL shape as an interface extending SqlBaseEntity:

// src/adapters/myEntityAdapter.ts
import type { SqlBaseEntity } from './idAdapter'
import { mapSqlBase } from './idAdapter'
import { mapSomeStatusToFe } from './statusMaps'
import type { MyEntity } from '../types'

export interface SqlMyEntity extends SqlBaseEntity {
  title: string
  status: string       // SQL enum like 'ACTIVE'
  some_field: string   // snake_case
}

2. Write the adapter function:

export function adaptMyEntity(sql: SqlMyEntity): MyEntity {
  const base = mapSqlBase(sql)
  return {
    ...base,
    name: sql.title,                          // field rename
    status: mapSomeStatusToFe(sql.status),    // status mapping
    someField: sql.some_field,                // case conversion
  }
}

3. Add status maps if the entity has enums that differ between SQL and FE (add to statusMaps.ts).

4. Export from barrel in src/adapters/index.ts.

5. Use in real API (*.real.api.ts):

import { adaptMyEntity } from '../adapters'

export const realMyEntityApi = {
  async getAll() {
    const response = await http.get('/my-entities')
    return {
      success: true,
      data: response.data.map(adaptMyEntity),
    }
  },
}