Level — Integration Connector

A small full-stack integration connector that ingests workforce data (Employees and Shifts), validates and maps it into a canonical model, persists it idempotently in PostgreSQL, and surfaces it through a typed API and a React dashboard. It ships with two interchangeable data sources behind a single Sync Engine:

• File connector (required) — reads ./data/*.csv
• API connector (bonus) — pulls JSON from a self-hosted mock provider service

The entire stack runs with one command.

cp .env.example .env      # if cloned from github repo
docker compose up --build

Service	URL	Notes
Frontend (React)	http://localhost:3000	Workforce dashboard
Backend API (Hono)	http://localhost:4000	REST + Hono RPC
Mock provider	http://localhost:5001	API-connector source
PostgreSQL	localhost:5433 → 5432	postgres:15-alpine

The backend runs with NODE_ENV=production, applies Drizzle migrations on startup, and bind-mounts ./data to /app/data. The frontend talks to the host-exposed API at http://localhost:4000.

docker-compose.yml interpolates POSTGRES_USER/POSTGRES_PASSWORD/POSTGRES_DB/DATABASE_URL from .env, so a .env file must exist before docker compose up.

Demo Database Reset: Database data persists in a named volume (pgdata). To completely clear the database for a clean demo, run docker compose down -v to delete the volume. It will be recreated and migrations auto-applied on the next docker compose up.

---

Tech Stack

Layer	Choice	Why
Backend	Hono + Node.js + TypeScript	Lightweight, native TS, minimal boilerplate
API contract	Hono RPC	End-to-end types shared with the frontend, no codegen
Validation	Zod	Runtime parsing of CSV/JSON + business rules
DB / ORM	PostgreSQL + Drizzle ORM	Typed SQL; clean ON CONFLICT upserts; file-based migrations
Frontend	React (Vite / React Router 7) + Tailwind + shadcn/ui	Fast, modern dashboard with minimal custom CSS
Data fetching	TanStack Query (React Query) over Hono RPC	Declarative loading/error states, post-sync cache invalidation, end-to-end types
Tests	Vitest (+ Testcontainers)	Unit tests and a real-Postgres integration test

---

Triggering a Sync

From the dashboard

The header has two buttons — Sync via File and Sync via API. Each disables while in flight and shows which source ran in the "Latest Sync Status" banner, along with read / inserted / updated / errored counts and an expandable list of row-level errors.

From the CLI

curl -X POST 'http://localhost:4000/sync?source=file'
curl -X POST 'http://localhost:4000/sync?source=api'

Returns the created sync run:

{
  "id": 1,
  "status": "success",
  "recordsRead": 11,
  "recordsInserted": 7,
  "recordsUpdated": 0,
  "recordsErrored": 4,
  "errors": ["Employee E-004: hourlyRate must be positive", "..."]
}

A run is success even when individual rows error — row-level failures are expected and captured in recordsErrored + errors. error is reserved for fatal, run-level failures (e.g. the CSV is missing or the mock provider is unreachable).

---

API Reference

Method	Route	Description
POST	/sync?source=file|api	Run a sync (employees, then shifts). Returns the SyncRun result.
GET	/sync-runs	10 most recent runs, newest first.
GET	/employees	Employees with read-time summary: lastShiftEndAt, totalEarningsCentsLast7Days.
GET	/employees/:externalId/shifts?from=…&to=…	One employee's shifts in an ISO-8601 range + computed totals. 404 if the employee is unknown, 400 for invalid dates.

---

Data Model & Derived Fields

Employee — externalId (unique), firstName, lastName, email (nullable, lowercased), hourlyRateCents (integer pence), active. Shift — externalId (unique), employeeExternalId (FK → employees.external_id), startAt, endAt, breakMinutes, and two persisted derived fields:

• workMinutes = (endAt − startAt in minutes) − breakMinutes, clamped to >= 0
• earningsCents = round(workMinutes × hourlyRateCents / 60)

SyncRun — startedAt/finishedAt, status, source, recordsRead/Inserted/Updated/Errored, and a compact JSON errors array.

GBP rates (e.g. 12.50) are converted to integer pence with Math.round(rate * 100) so all downstream arithmetic stays in integer space. Date-dependent summaries (lastShiftEndAt, last-7-days earnings, range totals) are computed at read time, not persisted, so they always reflect a live window.

---

Validation Rules

• externalId required on every row.
• Employee email normalized to lowercase (empty → null); hourly_rate must parse to a positive number.
• Shift startAt/endAt must be valid ISO-8601; endAt must be after startAt (else error + skip); breakMinutes empty → 0.
• A shift must reference a known employee (else error + skip), avoiding foreign-key aborts.
• A few bad rows never abort the run — each is recorded and the pipeline continues.

---

Persistence & Idempotency

• external_id is a UNIQUE NOT NULL constraint on both tables (the natural key).
• Every write uses Postgres INSERT … ON CONFLICT (external_id) DO UPDATE, so re-running a sync updates in place rather than creating duplicates.
• A composite index on shifts (employee_external_id, start_at) backs the per-employee range query.
• Drizzle Kit migrations live in backend/db/migrations/ and are applied automatically on backend startup.

---

Architecture

See ARCHITECTURE.md for the full write-up. In brief:

CSV files / Mock Provider API
        │
        ▼
  DataConnector  ── Strategy Pattern ──┐
  ├── FileConnector  (csv-parse)        │  injected by POST /sync?source=
  └── ApiConnector   (fetch JSON)       │
        │                               │
        ▼
  Sync Engine (two-phase)
  ├── Phase 1 Employees: validate → pence → upsert on external_id
  └── Phase 2 Shifts: resolve employee → validate → derive → upsert on external_id
        │
        ▼  Postgres (Drizzle) → Hono REST/RPC → React dashboard

---

Frontend Data Layer — TanStack Query

The dashboard's three reads (/employees, /sync-runs, /employees/:id/shifts) and the sync write are managed by TanStack Query instead of hand-rolled useState/useEffect. The PRD requires "basic loading + error states" everywhere and a Run Sync button that refreshes the view — React Query delivers both with far less code:

• Loading/error states for free — each useQuery exposes isPending/isFetching/error, so the components read flags instead of juggling three useStates per request.
• One-line post-sync refresh — the sync useMutation's onSuccess calls invalidateQueries(['employees']), ['sync-runs'], and ['shifts']. No global store, no manual sequential refetch.
• Race-condition-free master/detail — shifts are cached per employee (['shifts', externalId]); switching selection swaps cache keys and cancels the stale in-flight request, so a slow earlier response can never overwrite the current one.
• End-to-end types preserved — each fetcher narrows on res.ok then returns res.json(), so Hono RPC's InferResponseType flows straight into data with no extra type declarations.

SSR is enabled (React Router 7), so the QueryClient is created per-request on the server and as a singleton in the browser (frontend/app/lib/query-client.ts) to avoid leaking cache across requests.

---

Bonus — Mock Provider API Connector

A standalone mock-provider Hono service (compose-isolated, internal http://mock-provider:5001) exposes GET /employees and GET /shifts. POST /sync?source=api injects the ApiConnector into the same Sync Engine the file connector uses — only the data source changes (Open–Closed via the DataConnector interface in backend/connectors/types.ts).

Highlights:

• Dynamic shift dates — mock-provider/index.ts generates timestamps relative to now (isoDaysAgo()), so an API sync always lands inside the dashboard's last-7-days window and never goes stale. The dataset deliberately exercises the pipeline: a negative rate (E-104) and an empty name (E-105) → employee errors; an unknown employee (S-104 → E-199) and an endAt < startAt (S-105) → shift errors; and an over-long break (S-106, 600 min) → workMinutes clamped to 0.
• Distinct source, realistic ingestion — the API provider serves its own E-101…E-105 / S-101…S-106 namespace (the CSV uses E-001…E-005 / S-001…S-006). An API sync therefore inserts genuinely new workforce records (recordsInserted > 0) rather than only updating file-seeded rows — modelling a second source system feeding the same warehouse.
• Idempotency — re-running any source upserts the same rows via ON CONFLICT (external_id) DO UPDATE: the second run reports recordsUpdated instead of recordsInserted, with no duplicates.

Tests

pnpm -C backend test               # unit: connectors (file + api), validation, sync
pnpm -C backend test:integration   # Testcontainers Postgres: employee summary aggregation
pnpm check                         # biome + typecheck + unit tests

Connector coverage: backend/connectors/api.test.ts mocks fetch to assert both endpoints and non-OK error handling; backend/connectors/file.test.ts covers CSV header mapping.

---

Assumptions & Trade-offs

• Soft employee references — shifts link by employee_external_id; unknown links are reported per-row and skipped rather than failing the batch.
• Shift range filters on start_at — a shift that starts before the window (last 7 days) but ends inside it is excluded (the bound is intentionally simple).
• Per-row upserts — safe for this volume, to ensure idempotent sync and row-level report; a large batch would benefit from a single transaction + bulk insert.
• Frontend base URL hardcoded to http://localhost:4000; a real deployment should inject it at build time (eg. env vars to separate dev, stag, and prod environments)

What I'd Do Next (if timeboxed)

1. Handle orphaned records (e.g., invalid shift external_id "S-00") via soft deletion:
   • Add is_deleted (default false) to database shifts table, CSV schema, and validation schema (avoiding rigid ID format validation).
   • Update syncShifts upsert logic to write deletion status onConflictDoUpdate.
   • Filter out soft-deleted records (is_deleted = false) in retrieval queries like GET /employees/:externalId/shifts.
2. Standardize Frontend Styling:
   • Set up design tokens and utilize consistent classes rather than hardcoded Tailwind CSS.
   • Introduce shadcn/ui components when needed.
3. Wrap each sync in a transaction and batch the upserts to enhance performance.
4. Add pagination to /employees and /sync-runs across backend and frontend.
5. Surface partial-failure detail and toasts in the UI (beyond the current error list).