(WEB_PORTAL) Introducing a Three-Tier Permission System — How to Change a Constraint Without Downtime

Overview

We previously had only two permission tiers — regular employee and admin — but the organization's operations required a super-admin role above admin. Adding one more role seemed simple on the surface, but it actually meant touching a constraint on a database that was already in production.

The Problem

Our embedded database (SQLite) lets you constrain the allowed values of a column when the table is created, but that constraint can't simply be modified later with an ALTER TABLE statement to add a new value. Since the service was already live, we needed a way to change the constraint while preserving all existing data.

The Solution

We used the common SQLite pattern of "create a table with the new structure, migrate the existing data over, then rename it into place."

BEGIN TRANSACTION
  Create a temporary table with the new constraint
  Copy all data from the existing table into the temporary table
  Drop the existing table
  Rename the temporary table to the original name
COMMIT TRANSACTION

We wrapped the entire process in a single transaction as a safeguard, so that if anything failed partway through, it would roll back to the original state.

Role-Based Screen Branching

With three permission tiers now in place, we also reorganized the sidebar menu to display differently depending on role. Super admins see the full administration menu plus the permission management menu, while regular admins only see day-to-day operational menus. We also added a badge at the top of the screen to make the current role visible at a glance.

Retrospective

• We confirmed that when you need to change a constraint on a database that's already in production, recreating the table and migrating the data — even though it looks cumbersome — is the safest approach. Having a reliable way to evolve a schema without losing data or taking the service down proved useful again on several later occasions.
• When designing a permission system, we were reminded that "how many tiers to split roles into" matters less than "how to prevent each role from encroaching on another's permissions" — the latter is the more important design concern in practice.