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 (every touched GSI key is recomposed)
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 (GSI Key Recomposition)

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

Transferring an employee touches composites across multiple GSIs. An update recomposes every GSI key half whose composites it touches, so each index the employee appears in reflects the transfer:

  // #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? Each half of a GSI key (partition and sort) is recomposed on its own, and only when the update touches it. Setting office alone touches only the partition half of workplaces: the employee moves to the new office’s partition, and the sort key (team, title, employee) is kept as stored. What a touched half that cannot be composed does is set per index by indexPolicy.

  const moved = yield* db.entities.Employees.update({ employee: "dfinlay" }).set({
    office: "gw-zoo",
  })

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

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.layerConfig({
    region: Config.succeed("us-east-1"),
    endpoint: Config.String("DYNAMODB_ENDPOINT").pipe(Config.withDefault("http://localhost:8000")),
    credentials: Config.succeed({ accessKeyId: "local", secretAccessKey: "local" }),
  }),
  HrTable.layer({ name: "hr-table" }),
)

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

Effect.runPromise(main).then(() => console.log("\nDone."))

---

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()
GSI key recomposition	An update recomposes each GSI key half whose composites it touches (Index Policy)
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