Why
HireRosterly, Web needed role-based access control — not just “is this user logged in” but “is this user allowed to approve leave, export payroll, or read another department’s data.” This week was about building that foundation: database design, authorization logic, and the login flow, using Next.js, Drizzle ORM, and PostgreSQL.
The Database Design
Seven tables, kept deliberately simple:
- organizations — the tenant boundary
- users — identity, with a status field (pending | active | inactive)
- roles — named permission bundles, scoped per organization
- permissions — a global catalog of atomic actions (e.g. leave.approve)
- role_permissions — maps roles to permissions, with an allow/deny effect
- user_roles — assigns roles to users, with an optional scope (organization/department/team/own) and optional expiry
- audit_logs — append-only record of denied access attempts
The Approval Gate
A late but important addition: no user gets any access just by registering. Every new account starts as pending. An admin has to approve them — which assigns a role and activates the account in one transaction — before they can even log in. Rejected/disabled accounts just get inactive. Simple, no extra status values needed.
The Authorization Service
The core logic lives in four small, pure files instead of one big one:
- permissions.ts — resolves whether a role grants a permission code. Explicit deny always overrides allow, no matter how many roles say yes.
- scope.ts—checks whether a role’s scope actually covers the resource being accessed.
- policy.ts — contextual conditions on top of that, like “only if you own this record.”
- authorization.service.ts — ties it together into one call that the rest of the app uses.
Splitting it this way made each piece easy to unit test in isolation, without touching a database.
Sessions and Route Protection
Two layers, for a practical reason: Next.js Edge middleware can’t use a standard Postgres driver. So:
- Edge middleware verifies the session is valid — pure authentication, no DB.
- Route handlers (Node runtime) do the actual permission check against the database, using requirePermission().
organizationId is always read from the verified session, never trusted from the request body — that’s what makes multi-tenant scoping actually safe.
What Was New This Week
- Deny-precedence logic — designing a permission system where a single explicit deny beats any number of allows, and why that matters for safety-critical access removal.
- Edge runtime constraints — learning the hard way that Edge middleware can’t just use any database driver, and having to split authentication from authorization across two runtimes.
- JWT-based sessions with jose — a library chosen specifically because it works on both Node and Edge, unlike the more common jsonwebtoken package.
- The bootstrap problem — realizing that an approval-gated system needs a way to create the first admin, since no one exists yet to approve them. Solved with a one-time seed script.
Challenges — and How They Got Solved
The real lessons this week came from debugging, not designing:
- Duplicate schema files. user.schema.ts vs users.schema.ts, and organizations.schema.ts vs a typo’d rganizations.schema.ts — two files quietly defining the same database table. Caught by grepping imports and comparing actual pasted schema content instead of assuming file names matched.
- drizzle.config.ts pointing at a single file. It only listed user.schema.ts, so drizzle-kit push never even looked at the other six schema files. Fixed by pointing it at the whole folder (./lib/db/schemas/*.ts).
- Silent 500 errors. The generic error handler caught every unexpected error and returned a vague message — without logging the real cause anywhere. Nearly impossible to debug blind. Fixed by adding console.error() before the fallback response, which immediately surfaced the actual Postgres error.
- Missing column in production. Once errors were actually visible, the real issue showed up fast: organization_id didn’t exist in the live users table, because the migration never fully applied after the duplicate-index warning. A reminder that a warning during drizzle-kit push can mean the migration silently didn’t finish.
- Hurdles in making build for production Moving a Next.js application from local development to production, it uncovers hidden issues that localhost quietly ignores. First I use ‘params’(sync way) but, Modern route parameters are now asynchronous promises requiring an await before use.Debugging this Build issue I learn: Partial Prerendering solves rendering trade-offs by streaming live dynamic data directly into an instantly served static shell. Beyond rendering, production builds perform a strict sweep across your entire codebase: TypeScript type-checks every unexcluded test file that local dev skipped, and your ORM rigorously validates schema enums and required fields. Lastly, live deployments and reverse proxies will silently drop custom auth headers during preflight requests unless your server configuration explicitly allows them.
The pattern across all five: assumptions about file structure and prior steps were wrong more often than the logic itself was. Verifying actual file contents and actual database state — instead of trusting what should have happened — solved every one of them.
What’s Next
- Re-enable the approval gate in login.service.ts (temporarily disabled for testing)
- Build the admin UI for managing roles and approvals
- Run the full test suite (vitest) and type-check (tsc) end-to-end
- Wire requirePermission() into real domain modules once they exist (employee, attendance, leave, etc.)
Takeaway
The design work went smoothly. The real friction — and the real learning — was in the gap between “the code looks right” and “the system actually runs correctly,” which only closed by reading actual error messages, actual file contents, and actual database state instead of assuming they matched what was intended.
Author: Muhammad Faisal