Example: Human Resources

A human resources application managing employees, tasks, and offices in a single DynamoDB table. Adapted from the ElectroDB Human Resources example to showcase effect-dynamodb’s approach to the same problem.

What you’ll learn:

• 3 entities sharing one table (single-table design)
• 5 GSIs with index overloading
• Cross-entity collection queries
• Sort key range queries (salary ranges)
• Atomic onboarding via transactions
• Employee termination and rehire via soft delete + restore

---

Requirements

As stakeholders of this HR system, we need to support these access patterns:

1. CRUD — Create, read, update, and delete employees
2. Regional Manager — See all details about an office and its employees
3. Project Manager — Find all tasks and details on a specific employee
4. Product Manager — See all tasks for a project
5. Client — Find a physical office by location (country + state)
6. Hiring Manager — Find employees by title, with salary range queries
7. HR — Find all employees that report to a specific manager
8. HR — Transfer an employee between offices (all GSI composites must be consistent)
9. HR — Atomically onboard a new employee with their first task
10. HR — Terminate an employee (soft delete) and rehire them later (restore)

---

Table Definition

All three entities share a single table with 5 Global Secondary Indexes:

Key	Field	Used by
PK	pk	Employee (by ID), Task (by ID), Office (by ID)
SK	sk	Employee, Task (project + employee), Office
GSI1 PK	gsi1pk	Employee (by office), Task (by project), Office (by office)
GSI1 SK	gsi1sk	Employee (team + title + ID), Task (employee + task), Office
GSI2 PK	gsi2pk	Office (by country + state)
GSI2 SK	gsi2sk	Office (city + zip + ID)
GSI3 PK	gsi3pk	Employee (by ID), Task (by employee)
GSI3 SK	gsi3sk	Employee, Task (project + task)
GSI4 PK	gsi4pk	Employee (by title)
GSI4 SK	gsi4sk	Employee (salary + ID)
GSI5 PK	gsi5pk	Employee (by manager)
GSI5 SK	gsi5sk	Employee (team + office + ID)

GSI1 and GSI3 are overloaded — multiple entity types share the same physical index with different key compositions. This is the core single-table pattern.

---

Step 1: Models

Pure domain models with no DynamoDB concepts:

models.ts

const Department = {
  Jupiter: "jupiter",
  Mercury: "mercury",
  Saturn: "saturn",
  Venus: "venus",
  Mars: "mars",
  Neptune: "neptune",
} as const
const DepartmentSchema = Schema.Literals(Object.values(Department))

class Employee extends Schema.Class<Employee>("Employee")({
  employee: Schema.String,
  firstName: Schema.String,
  lastName: Schema.String,
  office: Schema.String,
  title: Schema.String,
  team: DepartmentSchema,
  salary: Schema.String,
  manager: Schema.String,
  dateHired: Schema.String,
  birthday: Schema.String,
}) {}

class Task extends Schema.Class<Task>("Task")({
  task: Schema.String,
  project: Schema.String,
  employee: Schema.String,
  description: Schema.String,
}) {}

class Office extends Schema.Class<Office>("Office")({
  office: Schema.String,
  country: Schema.String,
  state: Schema.String,
  city: Schema.String,
  zip: Schema.String,
  address: Schema.String,
}) {}

Note that salary is a String — we store it as zero-padded "000150.00" for correct lexicographic sort key ordering. This is a domain modeling choice for this example; in practice, numeric composites are automatically zero-padded by KeyComposer (16 digits for numbers, 38 for bigints).

---

Step 2: Schema, Entities, and Table

Schema

entities.ts

const HrSchema = DynamoSchema.make({ name: "hr", version: 1 })

Employee Entity

Employee defines its primary key and GSI indexes. Multi-entity collections are auto-discovered from matching collection names across entities:

entities.ts

const Employees = Entity.make({
  model: Employee,
  entityType: "Employee",
  primaryKey: {
    pk: { field: "pk", composite: ["employee"] },
    sk: { field: "sk", composite: [] },
  },
  indexes: {
    workplaces: {
      collection: "workplaces",
      name: "gsi1",
      pk: { field: "gsi1pk", composite: ["office"] },
      sk: { field: "gsi1sk", composite: ["team", "title", "employee"] },
    },
    assignments: {
      collection: "assignments",
      name: "gsi3",
      pk: { field: "gsi3pk", composite: ["employee"] },
      sk: { field: "gsi3sk", composite: [] },
    },
    byRole: {
      name: "gsi4",
      pk: { field: "gsi4pk", composite: ["title"] },
      sk: { field: "gsi4sk", composite: ["salary", "employee"] },
    },
    byManager: {
      name: "gsi5",
      pk: { field: "gsi5pk", composite: ["manager"] },
      sk: { field: "gsi5sk", composite: ["team", "office", "employee"] },
    },
  },
  timestamps: true,
  versioned: true,
  softDelete: true,
})

Key design decisions:

• softDelete: true enables termination/rehire without destroying records
• GSI access patterns (workplaces, assignments, byRole, byManager) are defined as entity-level indexes above

Task Entity

entities.ts

const Tasks = Entity.make({
  model: Task,
  entityType: "Task",
  primaryKey: {
    pk: { field: "pk", composite: ["task"] },
    sk: { field: "sk", composite: ["project", "employee"] },
  },
  indexes: {
    byProject: {
      name: "gsi1",
      pk: { field: "gsi1pk", composite: ["project"] },
      sk: { field: "gsi1sk", composite: ["employee", "task"] },
    },
    assignments: {
      collection: "assignments",
      name: "gsi3",
      pk: { field: "gsi3pk", composite: ["employee"] },
      sk: { field: "gsi3sk", composite: ["project", "task"] },
    },
  },
  timestamps: true,
})

Office Entity

entities.ts

const Offices = Entity.make({
  model: Office,
  entityType: "Office",
  primaryKey: {
    pk: { field: "pk", composite: ["office"] },
    sk: { field: "sk", composite: [] },
  },
  indexes: {
    workplaces: {
      collection: "workplaces",
      name: "gsi1",
      pk: { field: "gsi1pk", composite: ["office"] },
      sk: { field: "gsi1sk", composite: [] },
    },
    byLocation: {
      name: "gsi2",
      pk: { field: "gsi2pk", composite: ["country", "state"] },
      sk: { field: "gsi2sk", composite: ["city", "zip", "office"] },
    },
  },
  timestamps: true,
})

Table

The table declares all three entities:

entities.ts

const HrTable = Table.make({
  schema: HrSchema,
  entities: { Employees, Tasks, Offices },
})

GSI Access Patterns

GSI access patterns are defined as entity-level indexes. Multi-entity collections (workplaces, assignments) are auto-discovered from matching collection names across entities:

collections.ts

// GSI access patterns are now defined as entity-level indexes above.
// Multi-entity collections (workplaces, assignments) are auto-discovered
// from matching collection names across entities.

Key design decisions:

• workplaces (GSI1) is an auto-discovered collection — Employee and Office share the same collection: "workplaces" name and partition key (office), enabling cross-entity queries
• byProject (GSI1) on Task uses the same physical index but with a different partition key (project). Since it has no collection property, it is an isolated entity-level index. This is index overloading.
• assignments (GSI3) is an auto-discovered collection — Employee and Task share the same collection: "assignments" name and partition key (employee)
• byRole (GSI4) on Employee has salary in the SK composites for range queries (find employees in a salary band)

---

Step 3: Seed Data

seed.ts

const offices = {
  gwZoo: {
    office: "gw-zoo",
    country: "US",
    state: "OK",
    city: "Wynnewood",
    zip: "73098",
    address: "25803 N County Road 3250",
  },
  bigCatRescue: {
    office: "big-cat-rescue",
    country: "US",
    state: "FL",
    city: "Tampa",
    zip: "33625",
    address: "12802 Easy St",
  },
} as const

const employees = {
  jlowe: {
    employee: "jlowe",
    firstName: "Joe",
    lastName: "Lowe",
    office: "gw-zoo",
    title: "Zookeeper",
    team: "jupiter" as const,
    salary: "000045.00",
    manager: "jlowe",
    dateHired: "2020-01-01",
    birthday: "1970-06-15",
  },
  cbaskin: {
    employee: "cbaskin",
    firstName: "Carole",
    lastName: "Baskin",
    office: "big-cat-rescue",
    title: "Director",
    team: "saturn" as const,
    salary: "000150.00",
    manager: "cbaskin",
    dateHired: "1992-06-01",
    birthday: "1961-06-06",
  },
  dfinlay: {
    employee: "dfinlay",
    firstName: "Don",
    lastName: "Finlay",
    office: "gw-zoo",
    title: "Handler",
    team: "jupiter" as const,
    salary: "000035.00",
    manager: "jlowe",
    dateHired: "2021-03-15",
    birthday: "1985-11-20",
  },
  hschreibvogel: {
    employee: "hschreibvogel",
    firstName: "Howard",
    lastName: "Schreibvogel",
    office: "big-cat-rescue",
    title: "Volunteer",
    team: "saturn" as const,
    salary: "000000.00",
    manager: "cbaskin",
    dateHired: "2019-08-01",
    birthday: "1955-03-22",
  },
}

const tasks = {
  feedCats: {
    task: "feed-cats",
    project: "feeding",
    employee: "dfinlay",
    description: "Feed the big cats their daily meals",
  },
  feedCubs: {
    task: "feed-cubs",
    project: "feeding",
    employee: "hschreibvogel",
    description: "Feed the cubs their special diet",
  },
  planGala: {
    task: "plan-gala",
    project: "fundraiser",
    employee: "cbaskin",
    description: "Plan the annual fundraiser gala",
  },
  sellMerch: {
    task: "sell-merch",
    project: "fundraiser",
    employee: "jlowe",
    description: "Sell merchandise at the gift shop",
  },
}

seed.ts

const db = yield* DynamoClient.make({
  entities: { Employees, Tasks, Offices },
})

// --- Setup: create table ---
yield* db.tables["hr-table"]!.create()

// --- Seed data ---
for (const office of Object.values(offices)) {
  yield* db.entities.Offices.put(office)
}
for (const emp of Object.values(employees)) {
  yield* db.entities.Employees.put(emp)
}
for (const task of Object.values(tasks)) {
  yield* db.entities.Tasks.put(task)
}

---

Step 4: Fulfilling the Requirements

Requirement 1: CRUD

Create, read, update, and delete employees.

  // #region crud
  const joe = yield* db.entities.Employees.get({ employee: "jlowe" })

  const updated = yield* db.entities.Employees.update({ employee: "jlowe" }).set({
    office: "gw-zoo",
    team: "jupiter",
    title: "Head Zookeeper",
    salary: "000055.00",
    manager: "jlowe",
  })

  yield* db.entities.Employees.delete({ employee: "jlowe" })

  yield* db.entities.Employees.put(employees.jlowe)

Because Employee has softDelete: true, the delete doesn’t destroy the record — it archives it. The employee vanishes from all GSI queries but remains retrievable for audit. See Requirement 10 for the full soft delete workflow.

Requirement 2: Office + Employees (Workplaces Collection)

As a regional manager, see all details about an office and its employees.

Employee and Office share GSI1 via the Workplaces collection. Both use office as the partition key, so a single partition holds the office record and all employees at that location.

const { Employees: gwZooEmployees } = yield* db.collections.workplaces!({
  office: "gw-zoo",
}).collect()

const { Offices: gwZooOffice } = yield* db.collections.workplaces!({ office: "gw-zoo" }).collect()

Both queries hit GSI1 with the same partition key (office: "gw-zoo"). The collection query returns a grouped result with arrays keyed by member name.

Requirement 3: Employee + Tasks (Assignments Collection)

As a project manager, find all tasks and details on a specific employee.

Employee and Task share GSI3 via the Assignments collection. Both use employee as the partition key.

const { Tasks: dfinlayTasks } = yield* db.collections.assignments!({
  employee: "dfinlay",
}).collect()

const { Employees: dfinlayInfo } = yield* db.collections.assignments!({
  employee: "dfinlay",
}).collect()

Requirement 4: Tasks by Project

As a product manager, see all tasks for a project.

Task’s byProject index on GSI1 uses project as the partition key. This is index overloading — the same physical GSI1 serves the workplaces collection (Employee + Office by office) and byProject (Task by project), each with different partition keys.

const feedingTasks = yield* db.entities.Tasks.byProject({ project: "feeding" }).collect()

const fundraiserTasks = yield* db.entities.Tasks.byProject({ project: "fundraiser" }).collect()

Requirement 5: Offices by Location

As a client, find a physical office close to me.

The byLocation index on Office (GSI2) uses country + state as the partition key and city + zip + office as the sort key, enabling location-based queries with progressively finer granularity.

const flOffices = yield* db.entities.Offices.byLocation({ country: "US", state: "FL" }).collect()

const okOffices = yield* db.entities.Offices.byLocation({ country: "US", state: "OK" }).collect()

Requirement 6: Employees by Title + Salary Range

As a hiring manager, find employees with comparable salaries.

The byRole index on Employee (GSI4) has title as the partition key and salary + employee as the sort key composites. Because salary is zero-padded ("000150.00"), it sorts lexicographically in the correct numeric order. Query via db.entities.Employees.byRole(...) and filter by salary range in application code.

const zookeepers = yield* db.entities.Employees.byRole({ title: "Zookeeper" }).collect()

// All directors — filter by salary range in application code
const allDirectors = yield* db.entities.Employees.byRole({ title: "Director" }).collect()
const directorsBySalary = allDirectors.filter(
  (e) => e.salary >= "000000.00" && e.salary <= "999999.99",
)

Requirement 7: Direct Reports

As HR, find all employees that report to a specific manager.

The byManager index on Employee (GSI5) uses manager as the partition key.

const jloweReports = yield* db.entities.Employees.byManager({ manager: "jlowe" }).collect()

const cbaskinReports = yield* db.entities.Employees.byManager({ manager: "cbaskin" }).collect()

Requirement 8: Employee Transfer (All-or-None GSI Constraints)

As HR, transfer an employee between offices while keeping all indexes consistent.

Transferring an employee touches composites across multiple GSIs. When you update a field that appears in a GSI’s composite key, you must provide all composites for that GSI — otherwise the index key can’t be recomposed. This is the “fetch-merge” pattern.

  // #region transfer
  const transferred = yield* db.entities.Employees.update({ employee: "dfinlay" }).set({
    office: "big-cat-rescue",
    team: "saturn",
    title: "Handler",
    salary: "000035.00",
    manager: "cbaskin",
  })

  const newReports = yield* db.entities.Employees.byManager({ manager: "cbaskin" }).collect()

  const jloweReportsAfter = yield* db.entities.Employees.byManager({ manager: "jlowe" }).collect()

What happens if you provide only some composites? The type system warns you, and at runtime extractComposites fails with a ValidationError naming the violating index:

  const partialError = yield* db.entities.Employees.update({ employee: "dfinlay" })
    .set({ office: "gw-zoo" } as any)
    .asEffect()
    .pipe(Effect.flip)

Requirement 9: Atomic Onboarding (Transaction)

As HR, atomically onboard a new employee with their first task.

Transaction.transactWrite ensures both the employee and task are created atomically — if either fails, neither is persisted.

yield* Transaction.transactWrite([
  Employees.put({
    employee: "rstarr",
    firstName: "Rick",
    lastName: "Starr",
    office: "gw-zoo",
    title: "Trainee",
    team: "jupiter",
    salary: "000025.00",
    manager: "jlowe",
    dateHired: "2024-01-15",
    birthday: "1995-04-10",
  }),
  Tasks.put({
    task: "orientation",
    project: "onboarding",
    employee: "rstarr",
    description: "Complete new-hire orientation and safety training",
  }),
])

const newHire = yield* db.entities.Employees.get({ employee: "rstarr" })

const { Tasks: onboardingTasks } = yield* db.collections.assignments!({
  employee: "rstarr",
}).collect()

Requirement 10: Termination + Rehire (Soft Delete + Restore)

As HR, terminate an employee (archiving their record) and rehire them later.

Because Employee has softDelete: true, deleting an employee archives the record rather than destroying it. The sort key is replaced, GSI keys are stripped, and a deletedAt timestamp is added. The employee vanishes from all index queries but the record is preserved for compliance.

yield* db.entities.Employees.delete({ employee: "hschreibvogel" })

const cbaskinReportsAfterTermination = yield* db.entities.Employees.byManager({
  manager: "cbaskin",
}).collect()

const terminatedRecord = yield* db.entities.Employees.deleted.get({
  employee: "hschreibvogel",
})

const rehired = yield* db.entities.Employees.restore({ employee: "hschreibvogel" })

const reportsAfterRehire = yield* db.entities.Employees.byManager({
  manager: "cbaskin",
}).collect()

---

Running the Example

The complete runnable example is at examples/hr.ts in the repository. It seeds data, runs all 10 access patterns, and verifies each with assertions.

docker run -d -p 8000:8000 amazon/dynamodb-local

npx tsx examples/hr.ts

Layer Setup

The example wires dependencies via Effect Layers — no global config or constructor options:

main.ts

const AppLayer = Layer.mergeAll(
  DynamoClient.layer({
    region: "us-east-1",
    endpoint: "http://localhost:8000",
    credentials: { accessKeyId: "local", secretAccessKey: "local" },
  }),
  HrTable.layer({ name: "hr-table" }),
)

const main = program.pipe(Effect.provide(AppLayer))

Effect.runPromise(main).then(
  () => console.log("\nDone."),
  (err) => console.error("\nFailed:", err),
)

---

Key Takeaways

Concept	How it’s used
Single-table design	3 entities, 1 table, structured key values
Index overloading	GSI1 serves workplaces collection (employees + offices by office) and byProject index (tasks by project)
Collections	Workplaces (GSI1) and Assignments (GSI3) enable cross-entity queries with grouped results
Sort key range queries	Zero-padded salary in byRole (GSI4) SK enables between queries via .where()
All-or-none GSI updates	Updating one GSI composite requires all composites for that index
Transactions	Atomic multi-entity writes for onboarding
Soft delete + restore	Termination archives records; rehire restores them with all keys recomposed

What’s Next?

• Modeling Guide — Deep dive into models, schemas, tables, and entities
• Indexes & Collections — Access pattern design and collection patterns
• Lifecycle — Soft delete, versioning, and TTL
• Cricket Match Manager — Full end-to-end tutorial building a REST API