--- name: dynamodb-onetable description: Guide for DynamoDB single-table design using OneTable ORM. Use when designing database schemas, writing DAL functions, creating queries, or handling schema evolution in DynamoDB. --- # 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 ```typescript \/\/ 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\/\/dal\/` **No 'server-only'** - DAL must work in Lambda and Next.js ### Create ```typescript \/\/ dal\/create_account.ts import { ulid } from 'ulid' import { log } from '@saas4dev\/core' export async function create_account( userId: string, input: { name: string } ): Promise> { 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 ```typescript \/\/ dal\/find_account_by_id.ts export async function find_account_by_id(id: string): Promise> { 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 ```typescript \/\/ dal\/list_accounts_by_user.ts export async function list_accounts_by_user(userId: string): Promise> { 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 ```typescript \/\/ dal\/delete_account.ts export async function delete_account(id: string): Promise> { 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: ```typescript 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 ```typescript \/\/ 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