DynamoDB Single-Table Design with OneTable

Key Design Patterns

Access Pattern	pk	sk	Index
User's items	USER#${userId}	ITEM#${id}	primary
Item by ID	ITEM#${id}	USER#${userId}	gsi1
Hierarchical	USER#${userId}	PARENT#${parentId}#${id}	primary
By date	USER#${userId}	DATE#${date}#${id}	primary

Schema Definition

// db-schema.ts
export const Schema = {
  format: 'onetable:1.1.0',
  version: '0.0.1',
  indexes: {
    primary: { hash: 'pk', sort: 'sk' },
    gsi1: { hash: 'gsi1pk', sort: 'gsi1sk', project: 'all' },
  },
  models: {
    Account: {
      pk: { type: String, value: 'USER#${userId}' },
      sk: { type: String, value: 'ACCOUNT#${id}' },
      gsi1pk: { type: String, value: 'ACCOUNT#${id}' },
      gsi1sk: { type: String, value: 'USER#${userId}' },
      id: { type: String, required: true, generate: 'ulid' },
      userId: { type: String, required: true },
      name: { type: String, required: true },
      balance: { type: Number, default: 0 },
      deleted: { type: Boolean, default: false },
    },
  },
}

DAL Functions

File naming: snake_case (e.g., create_account.ts, find_by_id.ts)

Location: features/<feature>/dal/

No 'server-only' - DAL must work in Lambda and Next.js

Create

// dal/create_account.ts
import { ulid } from 'ulid'
import { log } from '@saas4dev/core'

export async function create_account(
  userId: string,
  input: { name: string }
): Promise<Result<Account>> {
  try {
    const entity = await AccountEntity.create({
      id: ulid(),
      userId,
      ...input,
    })
    return { success: true, data: entityToAccount(entity) }
  } catch (error) {
    log.error('[create_account]', { error, userId })
    return { success: false, error: 'Failed to create' }
  }
}

Read

// dal/find_account_by_id.ts
export async function find_account_by_id(id: string): Promise<Result<Account | null>> {
  try {
    const entity = await AccountEntity.get(
      { gsi1pk: `ACCOUNT#${id}` },
      { index: 'gsi1' }
    )
    if (!entity || entity.deleted) return { success: true, data: null }
    return { success: true, data: entityToAccount(entity) }
  } catch (error) {
    log.error('[find_account_by_id]', { error, id })
    return { success: false, error: 'Failed to find' }
  }
}

List with Query

// dal/list_accounts_by_user.ts
export async function list_accounts_by_user(userId: string): Promise<Result<Account[]>> {
  try {
    const entities = await AccountEntity.find(
      { pk: `USER#${userId}`, sk: { begins: 'ACCOUNT#' } },
      { where: '${deleted} <> {true}' }
    )
    return { success: true, data: entities.map(entityToAccount) }
  } catch (error) {
    log.error('[list_accounts_by_user]', { error, userId })
    return { success: false, error: 'Failed to list' }
  }
}

Soft Delete

// dal/delete_account.ts
export async function delete_account(id: string): Promise<Result<void>> {
  try {
    await AccountEntity.update(
      { gsi1pk: `ACCOUNT#${id}` },
      { set: { deleted: true, updatedAt: new Date() }, index: 'gsi1' }
    )
    return { success: true }
  } catch (error) {
    log.error('[delete_account]', { error, id })
    return { success: false, error: 'Failed to delete' }
  }
}

Entity-to-Model Converter

Always convert entities to domain models:

function entityToAccount(entity: any): Account {
  return {
    id: entity.id,
    userId: entity.userId,
    name: entity.name,
    balance: entity.balance ?? 0,        // Handle missing fields
    deleted: entity.deleted ?? false,
    createdAt: entity.createdAt ?? new Date(),
    updatedAt: entity.updatedAt ?? new Date(),
  }
}

Schema Evolution

Adding fields: Always optional with defaults

// GOOD
newField: { type: String, default: '' }

// BAD - breaks existing records
newField: { type: String, required: true }

Process:

1. Add field as optional with default
2. Update entity-to-model converter
3. Deploy
4. Backfill if needed

Rules

• Use ULID for IDs (time-sortable)
• Always soft delete (deleted: true)
• Log errors with context
• Return { success, data?, error? } format
• Handle missing fields in converters