How it works · Internal Staff Platform

Five modules, one Postgres.

The plumbing behind a single platform replacing five HR tools. Data flow, OCR pipeline, kiosk PWA, and the consolidated payroll export.

The 60-second version

If you only read one paragraph.

Every employee event — a clock-in, a leave request, a receipt photo, a kiosk punch — is a row in the same Postgres database. Four roles see four different slices of that data, enforced by row-level security. Receipt photos go through an OCR pipeline that returns strict JSON, validated by Zod, into the expense form. Approvals fire transactional email via Resend with an inline image and a one-tap approve link. End of month, one button produces a CSV that has already reconciled attendance, leave, and approved expenses. The kiosk runs as a PWA with a service worker so it keeps signing people in even if wi-fi drops.

Core data flow · receipt to approved expense

A photo to an approval, in 30 seconds.

┌───────────────────────────────────────────────────────────────┐ │ 1. Employee taps the FAB on the phone, snaps a receipt │ │ ↓ multipart/form-data, JPEG ~400KB │ │ 2. POST /api/expenses/upload │ │ • Server Action stores the image in Supabase Storage │ │ • Returns a signed read URL (1 hour TTL) │ │ ↓ │ │ 3. Server invokes /api/expenses/ocr (background) │ │ • Anthropic SDK call with the image URL │ │ • Prompt asks for strict JSON: merchant, date, total, │ │ currency, category guess │ │ • Response validated by Zod schema → typed result │ │ ↓ ~1.4s typical │ │ 4. Realtime channel pushes 'ocr_done' to the user's session │ │ • Form fields populate live; user confirms or edits │ │ ↓ │ │ 5. POST /api/expenses/submit │ │ • INSERT into expenses (status='pending') │ │ • Resend email to the user's manager with approve URL │ │ ↓ │ │ 6. Manager taps approve → expense.status='approved' │ │ • Approved row joins the next month's payroll snapshot │ └───────────────────────────────────────────────────────────────┘

The whole flow is six steps that the user perceives as: take a photo, confirm, done. The OCR step is invisible. The approval round-trip happens entirely from email.

Subsystems

Each module on its own.

Auth and roles

Supabase Auth, magic-link by default. Four roles: staff, manager, director, admin. profiles stores the manager-of relationship for hierarchical access. RLS policies use auth.uid() joined with profiles. Service-role access is only used by trusted server-side cron handlers.

Attendance and timesheets

Two tables, one model. attendance_punches is for the retail floor; timesheet_entries is for back-office staff tracking hours against projects. Both feed the payroll snapshot. The clock-in UI is a single button with a lunch toggle. The timesheet UI is a weekly grid.

-- Daily worked-minutes per user, derived from punches SELECT user_id, date_trunc('day', punch_at) AS day, SUM(CASE WHEN kind = 'out' THEN -EXTRACT(EPOCH FROM punch_at) ELSE EXTRACT(EPOCH FROM punch_at) END) / 60 AS minutes FROM attendance_punches WHERE punch_at > now() - interval '1 month' GROUP BY user_id, date_trunc('day', punch_at);

Leave

UK statutory leave defaults: 28 days including bank holidays. Bank holidays auto-deducted per region (England + Wales, Scotland, Northern Ireland). Leave types are configurable: annual, sick, parental, unpaid. Balances accrue monthly via a pg_cron job. Approval is the same manager-email-with-link pattern as expenses.

Expenses with OCR

The OCR pipeline is one prompt, one schema, one validation step. The schema is the contract:

const ReceiptExtraction = z.object({ merchant: z.string().min(1).max(120), date_iso: z.string().regex(/^\d{4}-\d{2}-\d{2}$/), total: z.number().positive(), currency: z.string().length(3), // ISO 4217 category_guess: z.enum([ 'meals', 'travel', 'supplies', 'subscriptions', 'training', 'other' ]).nullable(), })

If the LLM returns anything that fails this schema, we discard the result and present an empty form. There is no “maybe right” data path. Strict schemas keep the data quality predictable.

Tablet kiosk PWA

The kiosk is a separate route running fullscreen on a wall-mounted iPad. A service worker caches the bundle and queues punches in IndexedDB if the network drops. On reconnect, the queue replays. The kiosk uses a scoped device token, not user auth, with an RLS policy that allows only inserts into kiosk_punches. PIN sign-in (4 digits) maps to a profiles.kiosk_pin_hash column verified server-side.

Payroll export

One button. The handler joins attendance, leave, and approved expenses for the requested month, freezes the result into payroll_snapshots, and emits a CSV ready for the accountant’s pipeline.

SELECT p.full_name, p.payroll_id, COALESCE(SUM(a.worked_minutes), 0) AS minutes, COALESCE(SUM(l.days_taken), 0) AS leave_days, COALESCE(SUM(e.amount_gbp), 0) AS reimbursable FROM profiles p LEFT JOIN att_daily a USING (user_id) LEFT JOIN leave_monthly l USING (user_id) LEFT JOIN expenses_approved_monthly e USING (user_id) WHERE month = $1 GROUP BY p.full_name, p.payroll_id ORDER BY p.full_name;

Director weekly digest

Offline-tolerant sign-in, queues punches when the wi-fi drops. Reinstalls on tablet reboots without a native app.

What we rejected

A React Native kiosk would be the same UX with 10x the maintenance.

Performance and observability

What we watch.

~1.4s

Receipt OCR latency p50

~620ms

Page TTFB p95 (manager dashboard)

99.9%+

Kiosk uptime via service worker

Sentry catches client-side errors. Vercel Analytics catches server-side latency. Postgres pg_stat_statements catches slow queries. The OCR pipeline emits its own structured logs (request id, schema validation outcome, latency) into a ocr_events table for later analysis.

Future direction

What is next.

Asset register

Track issued laptops, phones, keys. Same auth, same UI conventions. Estimate: ~5 days.

Internal directory

Searchable photo wall + role + manager. Already half-built into profiles.

Mileage tracking

Postcode-to-postcode miles for company travel, HMRC AMAP rate applied automatically. Joins the existing expenses pipeline.

Anomaly digest improvements

More signals: unusual expense categories, attendance pattern shifts, balance-at-risk warnings before year-end.

Want the full engineering record?

The whitepaper goes deeper into the data model, RLS policies, and the implementation timeline.

Read the whitepaper →Back to case study