How Aegir is built

The product page makes four promises about safety records. This page shows what keeps each one, how a request travels, and the tests that run on every push.

Built by Johnmark Enriquez, full-stack engineer. Read the source.

The promises, and what keeps them

Each promise on the product page is enforced by Postgres, not only by the screens, and each has pgTAP tests that try to break it.

Your company's records stay yours: Row-level security on every table, through membership checks in a private schema. Child rows must belong to their parent's company, and the company is never taken from the client.; Proved by01_tenancy.test.sql 12_read_isolation.test.sql
Every change is signed and dated: Triggers record inserts, updates and deletes on every audited table, with the actor taken from the session. No client role may insert, update or delete an audit row.; Proved by06_audit.test.sql 10_audit_log_access.test.sql
The rules can't be skipped: Triggers allow only valid status changes and limit which columns each role may change. No client role has delete rights on any table.; Proved by03_inspections.test.sql 04_work_orders.test.sql 08_linked_failed_checks.test.sql
Handovers can't be rewritten: A trigger allows one change to a sent handover: the recipient acknowledging it, which stamps the time. The sender can't be forged.; Proved by05_handovers.test.sql 09_handover_access.test.sql

From the browser to the database

One Next.js app and one Postgres database, with no API server in between. The app asks the database as the signed-in user, and Postgres has the final say on what they may see or change.

BrowserAny device
- Pages stream from the server; the browser never talks to the database directly
- Security headers and a Content Security Policy on every response
From the browser, over HTTPS
Next.js 16Vercel
- Server Components read through a server-only data layer
- Server Actions re-check the user and validate every input with Zod
- The firewall rate-limits sign-in
From the app, as the signed-in user
Postgres 17Supabase
- Row-level security on every table, with explicit grants
- Triggers enforce status changes and write the audit log
- Only the public key reaches the app; no secret key at runtime

A rule, and the test that proves it

Work orders can only move open → in progress ↔ blocked → completed. The database refuses anything else, whichever screen or script asks, and a pgTAP test tries the forbidden move.

The rule, in the migrationLines 229–237 on GitHubsupabase/migrations/20261016000517_harden_after_independent_review.sql

if new.status is distinct from old.status then
  if not (
    (old.status = 'open' and new.status in ('in_progress', 'blocked'))
    or (old.status = 'in_progress' and new.status in ('open', 'blocked', 'completed'))
    or (old.status = 'blocked' and new.status in ('open', 'in_progress'))
  ) then
    raise exception 'Invalid work order status change: % -> %', old.status, new.status
      using errcode = 'P0001';
  end if;

Its testLines 53–57 on GitHubsupabase/tests/04_work_orders.test.sql

select throws_ok(
  $$ update public.work_orders set status = 'completed' where id = 'a4000000-0000-4000-8000-000000000003' $$,
  'P0001', 'Invalid work order status change: open -> completed',
  'open -> completed is not a valid transition'
);

Stack

Next.js 16 (App Router)
React 19
TypeScript (strict)
Tailwind CSS v4
shadcn/ui
Zod
Supabase Auth
Postgres 17
Vitest
pgTAP
Playwright
Vercel

See for yourself

CI on every pushlint, types, unit tests, build, migrations, pgTAP, database advisors
Testingwhat each suite proves and how to run it
Decision logevery material choice, with what was not taken and why
Architecturefolders, boundaries and data flow

Built by Johnmark Enriquez

Full-stack engineer. Aegir is a portfolio project: a working slice of a fleet safety system, from the database schema and its security model to the screens, the tests and the deployment. The decision log records each material choice and the alternatives that were turned down.