Work / U01

AutoShopInventory & POS

A multi-branch inventory and POS that replaced drifting Excel sheets, with roles enforced in the database and a security audit before launch.

Role
Sole developer
Client
Freelance, confidential
When
2025 – present
Stack
Next.js 14 · TypeScript · Supabase · Playwright

How it works

Motion mock-up with demo data. The red toast is the database refusing a staff account that tried to make itself an admin.
  1. 01Two branches, two spreadsheets. Each branch kept its own copy, and the counts drifted apart.
  2. 02One inventory. Stock moves by barcode scan, and every move lands in the audit log.
  3. 03A staff account tries to promote itself. The request a tampered page or a direct API call could send.
  4. 04The database says no. Roles are enforced with Row Level Security, so the role stays staff.

01Problem

A multi-branch auto parts shop tracked stock and sales in Excel sheets. Each branch had its own copy, and the copies drifted apart.

02My role

Sole developer: requirements with the owner, design, build, a security audit and delivery. Version 1 (July 2025) was a C# WinForms app on SQL Server with barcode scanners. Version 2 (March 2026 onward) moved it to the web.

03Approach

  1. Access control lives in the database.Four roles enforced with Row Level Security, so a tampered UI or a direct API call gets the same answer.
  2. Stock that can't silently drift.Barcode stock-in and stock-out, transfers between branches with approvals, and an audit log.
  3. Privileged work goes through Edge Functions.They check that the caller's account is still active before doing anything.
  4. Tests that attack.Playwright end-to-end specs, including RLS specs that replay the attacks below.

04The audit

Before launch I went after my own app as a malicious staff account. Everything below is fixed, each with its own database migration.

  • Found

    The role helper trusted a profile claim that any user can rewrite. That's a privilege-escalation path.

    Fixed

    Roles are read from the users table only.

  • Found

    Signup copied the role from the signup request, so anyone could create an admin.

    Fixed

    New accounts can no longer choose their role.

  • Found

    Staff could move themselves to another branch, or reactivate a disabled account, by editing their own profile.

    Fixed

    Branch and account status are pinned on self-update.

  • Found

    Receipts could be rewritten after the sale: items, totals, payment method.

    Fixed

    Receipts are immutable once issued.

  • Found

    Deleting a branch silently wiped its inventory. The guard only existed in the browser.

    Fixed

    The database refuses the delete while stock remains.

  • Found

    Stock adjustments and some deletions left no audit trail.

    Fixed

    Audit triggers now cover them.

05Result

The shop runs on one system instead of drifting spreadsheets, and the security and data-loss defects above were closed before launch.