ArchPilot – Step 1: Users & Roles, End-to-End (Architecture, Database, and Cross-Framework Code)

Introduction

ArchPilot is a modular ERP/CRM showcase implemented across multiple frameworks to demonstrate how the same business problem is solved in different ecosystems. This article combines—into one complete, self-contained document—the database design and the code implementations for Step 1: User & Role Management across Laravel, Django, Spring Boot, ASP.NET Core, Node.js + Express, and Drupal.

The goals are:

• Use plain language so anyone can understand what we’re doing.
• Show the same capability (e.g., permission ViewBudget) in each framework.
• Provide side-by-side code snippets you can run or adapt immediately.
• Explain database choices, schema, migrations/models, and pros/cons.

What Step 1 Does (Simple Summary)

• Login with email & password (reset, lockout after many failed attempts).
• Roles & Permissions (e.g., role Manager with permission ViewBudget).
• Protected pages/endpoints: if you don’t have the permission, you don’t see the data.
• Audit (log who did what, when, and on which target).
• Optional: email verification and two-factor authentication (TOTP).

To compare frameworks clearly, we’ll consistently use:

• Role: Manager
• Permission: ViewBudget (Italian earlier: VedereBudget)
• Protected page/endpoint: list the last 20 budget records

Database: What to Use and Why

Recommended order for ArchPilot

1. PostgreSQL – Strong standard, rich types (JSONB, UUID), robust transactions; great across all frameworks.
2. MySQL/MariaDB – Ubiquitous, easy hosting, mature tools; great if your infra already uses it.
3. SQL Server – First-class in Microsoft environments, strong BI integration; great with .NET/Azure.
4. SQLite (dev/lab only) – Zero setup for quick local demos; not for multi-user production.

Practical pick for ArchPilot: PostgreSQL as the canonical DB. MySQL is fine if you already use it; SQL Server fits Microsoft enterprise stacks.

One database or one per framework?

• Showcase/demo: prefer one database per framework (or one schema per framework) to avoid migration conflicts and let each stack evolve independently.
  • Example databases: archpilot_laravel, archpilot_django, archpilot_spring, etc.
• Integrated production: you can converge on a shared canonical schema, but it requires strict versioning and migration governance.
• Multi-tenant (future step): either add tenant_id to each table (simpler) or use separate schemas/databases per tenant (maximum isolation).

For ArchPilot: one DB per framework during the showcase.

Canonical Data Model (Same for All Frameworks)

Core Entities

• users — user profile & credentials (password hash, active state, MFA secret, last login).
• roles — named collections of permissions (Admin, Manager, Operator).
• permissions — atomic capabilities (e.g., ViewBudget, EditBudget).
• user_role — many-to-many link between users and roles.
• role_permission — many-to-many link between roles and permissions.
• audit_logs — immutable record of security-relevant actions (who, what, when, on what).
• invitations — email invitation tokens with expiration and default role.
• onboarding_states — track first-login wizard steps.
• (Business demo entity) budgets — used to test permission ViewBudget.

Logical Diagram (in words)

users (1..*)──(0..*) user_role (0..*)──(1..*) roles
roles (1..*)──(0..*) role_permission (0..*)──(1..*) permissions
users (1..*)──(0..*) audit_logs

Canonical SQL DDL (PostgreSQL-friendly, easy to port to MySQL/SQL Server)

If using MySQL, replace UUID with CHAR(36) (or BINARY(16)) and adjust defaults.
If using SQL Server, use uniqueidentifier and the appropriate default.

-- USERS
CREATE TABLE users (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name          VARCHAR(120) NOT NULL,
  email         VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  mfa_secret    VARCHAR(255),
  is_active     BOOLEAN NOT NULL DEFAULT TRUE,
  last_login_at TIMESTAMP WITH TIME ZONE,
  created_at    TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
  updated_at    TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

-- ROLES
CREATE TABLE roles (
  id   UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  slug VARCHAR(60) NOT NULL UNIQUE,
  name VARCHAR(120) NOT NULL
);

-- PERMISSIONS
CREATE TABLE permissions (
  id   UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  slug VARCHAR(80) NOT NULL UNIQUE,
  name VARCHAR(160) NOT NULL
);

-- USER_ROLE (M:N)
CREATE TABLE user_role (
  user_id UUID NOT NULL,
  role_id UUID NOT NULL,
  scope   VARCHAR(120), -- optional scope: department/project
  PRIMARY KEY (user_id, role_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
);

-- ROLE_PERMISSION (M:N)
CREATE TABLE role_permission (
  role_id       UUID NOT NULL,
  permission_id UUID NOT NULL,
  PRIMARY KEY (role_id, permission_id),
  FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
  FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
);

-- AUDIT LOGS
CREATE TABLE audit_logs (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id     UUID,
  action      VARCHAR(120) NOT NULL,       -- e.g., assign_role, login, reset_password
  target_type VARCHAR(120),                -- e.g., 'user','budget'
  target_id   UUID,
  metadata    JSONB,                       -- extra details (role given, IP, UA, etc.)
  ip          INET,
  ua          TEXT,
  created_at  TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

-- INVITATIONS
CREATE TABLE invitations (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email           VARCHAR(190) NOT NULL,
  role_id_default UUID,
  token           VARCHAR(120) NOT NULL UNIQUE,
  expires_at      TIMESTAMP WITH TIME ZONE NOT NULL,
  used_at         TIMESTAMP WITH TIME ZONE,
  invited_by      UUID,
  FOREIGN KEY (role_id_default) REFERENCES roles(id) ON DELETE SET NULL,
  FOREIGN KEY (invited_by) REFERENCES users(id) ON DELETE SET NULL
);

-- ONBOARDING
CREATE TABLE onboarding_states (
  user_id      UUID PRIMARY KEY,
  step         VARCHAR(60) NOT NULL,
  completed_at TIMESTAMP WITH TIME ZONE
);

-- DEMO DOMAIN: BUDGETS
CREATE TABLE budgets (
  id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name       VARCHAR(160) NOT NULL,
  amount     NUMERIC(14,2) NOT NULL DEFAULT 0,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

-- HELPFUL INDEXES
CREATE INDEX idx_audit_logs_action ON audit_logs(action);
CREATE INDEX idx_audit_logs_created_at ON audit_logs(created_at);

Why this schema?

• UUIDs prevent collisions across independent framework instances.
• JSONB in audit_logs.metadata lets you attach arbitrary details without schema churn.
• Foreign keys make cascade/retention explicit (e.g., keep audit even if the actor is deleted).
• Indexes on common filters (action, created_at) keep queries snappy.

Database Quality Settings (for all stacks)

• UTF-8 everywhere (utf8mb4 on MySQL).
• UTC timestamps in DB and backend; convert for UI only.
• Transactions around security mutations (assign role + write audit).
• Indexes on frequently queried fields (email, action, created_at).
• Consistent ON DELETE rules to match your retention policy.
• Seed: create ViewBudget permission, Manager role, and a demo user manager@example.com.

Pros, Cons, and Comparisons (Frameworks)

Key comparisons

• Speed to first value: Laravel/Django are quickest; Spring/ASP.NET take longer but excel in enterprise scenarios.
• SSO/Enterprise: Spring/ASP.NET win (OIDC/SAML, claims, compliance).
• API-first & real-time: Express is the most flexible (consider NestJS for structure).
• Editorial ACLs: Drupal is ideal when content workflows dominate.

“To do X, here’s how in each framework” (Concrete Code)

We’ll show seed RBAC, protect the budget page/endpoint, and audit a role assignment.

A) Create Permission & Role, Assign to User

Laravel

Install RBAC package once:

composer require spatie/laravel-permission
php artisan vendor:publish --provider="Spatie\Permission\PermissionServiceProvider"
php artisan migrate

Seeder:

// database/seeders/RbacSeeder.php
use Spatie\Permission\Models\Role;
use Spatie\Permission\Models\Permission;
use App\Models\User;

class RbacSeeder extends Seeder {
  public function run(){
    $perm = Permission::firstOrCreate(['name' => 'ViewBudget']);
    $role = Role::firstOrCreate(['name' => 'Manager']);
    $role->givePermissionTo($perm);

    $user = User::where('email','manager@example.com')->first();
    if($user){ $user->assignRole('Manager'); }
  }
}

Django

Model declares permission:

# myapp/models.py
from django.db import models

class Budget(models.Model):
    name = models.CharField(max_length=120)
    amount = models.DecimalField(max_digits=12, decimal_places=2)

    class Meta:
        permissions = [
            ("view_budget", "Can view budgets"),
        ]

Management command to seed RBAC:

# myapp/management/commands/seed_rbac.py
from django.core.management.base import BaseCommand
from django.contrib.auth.models import Group, Permission, User

class Command(BaseCommand):
    def handle(self, *args, **options):
        perm = Permission.objects.get(codename='view_budget')
        manager, _ = Group.objects.get_or_create(name='Manager')
        manager.permissions.add(perm)

        user = User.objects.filter(email='manager@example.com').first()
        if user: user.groups.add(manager)

Spring Boot

Assume your UserDetails includes authorities loaded from DB; one of them is ViewBudget:

public class MyUserDetails implements UserDetails {
  private final Collection<GrantedAuthority> authorities;
  @Override public Collection<? extends GrantedAuthority> getAuthorities(){ return authorities; }
  // ... remaining methods and constructor
}

ASP.NET Core

Add a policy that requires a “permission” claim:

// Program.cs
builder.Services.AddAuthorization(options =>
{
    options.AddPolicy("ViewBudget", policy =>
        policy.RequireClaim("permission", "ViewBudget"));
});

(Ensure you assign the claim permission=ViewBudget to the user during sign-in or map it from the DB.)

Node + Express

Ensure req.user.permissions contains "ViewBudget" after login:

// Example populated by your login middleware
req.user = { id: 1, email: 'manager@example.com', permissions: ['ViewBudget'] };

Drupal

Define permission in your module and assign it to the Manager role via the admin UI:

# mymodule.permissions.yml
view budget:
  title: 'ViewBudget'
  description: 'View the budget list'

B) Protect the Budget Page/Endpoint

Laravel

// routes/web.php
Route::get('/budget', [BudgetController::class,'index'])->middleware('auth');

// app/Http/Controllers/BudgetController.php
class BudgetController extends Controller {
  public function index(Request $request){
    if(!$request->user()->can('ViewBudget')){
      abort(403, 'You do not have permission to view budgets');
    }
    $records = Budget::latest()->take(20)->get();
    return view('budget.index', compact('records'));
  }
}

Django

# myapp/views.py
from django.contrib.auth.decorators import login_required, permission_required
from django.shortcuts import render
from .models import Budget

@login_required
@permission_required('myapp.view_budget', raise_exception=True)
def budget_index(request):
    records = Budget.objects.order_by('-id')[:20]
    return render(request, 'budget_index.html', {'records': records})

Spring Boot

// SecurityConfig.java (minimal demo)
@EnableMethodSecurity
@Configuration
public class SecurityConfig {
  @Bean
  SecurityFilterChain chain(HttpSecurity http) throws Exception {
    http.csrf(csrf->csrf.disable())
       .authorizeHttpRequests(a->a.anyRequest().authenticated())
       .formLogin(Customizer.withDefaults());
    return http.build();
  }
}

// BudgetController.java
@RestController
@RequestMapping("/api/budget")
public class BudgetController {
  private final BudgetService service;
  public BudgetController(BudgetService s){ this.service = s; }

  @PreAuthorize("hasAuthority('ViewBudget')")
  @GetMapping
  public List<Budget> index(){ return service.latest(); }
}

ASP.NET Core

[Authorize]
[ApiController]
[Route("api/[controller]")]
public class BudgetController : ControllerBase {
  [Authorize(Policy = "ViewBudget")]
  [HttpGet]
  public IActionResult Get(){
    var records = BudgetRepo.Latest(20);
    return Ok(records);
  }
}

Node + Express

// authz.js
function requirePermission(perm){
  return (req,res,next)=>{
    const ok = req.user && Array.isArray(req.user.permissions)
              && req.user.permissions.includes(perm);
    if(!ok) return res.status(403).json({error:'Missing permission: '+perm});
    next();
  };
}
module.exports = { requirePermission };

// routes/budget.js
const router = require('express').Router();
const { requirePermission } = require('./authz');

router.get('/budget', requireLogin, requirePermission('ViewBudget'), async (req,res)=>{
  const records = await Budget.findLatest(20);
  res.json(records);
});

module.exports = router;

Drupal

# mymodule.routing.yml
mymodule.budget:
  path: '/budget'
  defaults:
    _controller: '\Drupal\mymodule\Controller\BudgetController::index'
    _title: 'Budget'
  requirements:
    _permission: 'view budget'

// src/Controller/BudgetController.php
namespace Drupal\mymodule\Controller;
use Drupal\Core\Controller\ControllerBase;

class BudgetController extends ControllerBase {
  public function index(){
    $records = \Drupal::service('mymodule.budget_repo')->latest(20);
    return ['#theme'=>'item_list','#items'=>array_map(fn($r)=>$r->label(),$records)];
  }
}

C) Audit: “Who Changed Which Role, When?”

Laravel (event listener)

// app/Listeners/LogRoleChange.php
public function handle(RoleAssigned $event){
  AuditLog::create([
    'user_id'    => auth()->id(),
    'action'     => 'assign_role',
    'target_type'=> 'user',
    'target_id'  => $event->user->id,
    'metadata'   => ['role'=>$event->role->name],
    'ip'         => request()->ip(),
    'ua'         => request()->userAgent(),
    'created_at' => now(),
  ]);
}

Django (signal on user–group M2M change)

from django.db.models.signals import m2m_changed
from django.dispatch import receiver
from django.contrib.auth.models import User

@receiver(m2m_changed, sender=User.groups.through)
def log_group_change(sender, instance, action, pk_set, **kwargs):
    if action in ['post_add','post_remove']:
        AuditLog.objects.create(
          user=instance, action='change_group',
          metadata={'groups': list(pk_set)}
        )

Spring Boot (application event)

public class RoleAssignedEvent extends ApplicationEvent {
  public final Long actorId; public final Long targetId; public final String role;
  public RoleAssignedEvent(Object s, Long a, Long t, String r){ super(s); actorId=a; targetId=t; role=r; }
}

@Component
public class AuditListener {
  @EventListener
  public void on(RoleAssignedEvent e){
    auditRepo.save(new Audit("assign_role", e.actorId, "user", e.targetId, Map.of("role",e.role)));
  }
}

ASP.NET Core (Serilog example)

Log.Information("assign_role actor={Actor} target={Target} role={Role}", actorId, targetUserId, roleName);

Node + Express (Winston)

logger.info('assign_role', { actorId: req.user.id, targetId, role: roleName, ip: req.ip });

Drupal (watchdog)

\Drupal::logger('mymodule')->notice('assign_role actor=@a target=@t role=@r', [
  '@a'=>$actor_id,'@t'=>$target_id,'@r'=>$role_name
]);

Migrations/Models by Framework (hooking the schema up)

Laravel (Eloquent migrations; Postgres example)

// database/migrations/2025_11_06_000000_create_core_tables.php
return new class extends Migration {
  public function up(): void {
    Schema::create('users', function(Blueprint $t){
      $t->uuid('id')->primary();
      $t->string('name',120);
      $t->string('email',190)->unique();
      $t->string('password_hash');
      $t->string('mfa_secret')->nullable();
      $t->boolean('is_active')->default(true);
      $t->timestampTz('last_login_at')->nullable();
      $t->timestampsTz();
    });
    Schema::create('roles', function(Blueprint $t){
      $t->uuid('id')->primary();
      $t->string('slug',60)->unique();
      $t->string('name',120);
    });
    Schema::create('permissions', function(Blueprint $t){
      $t->uuid('id')->primary();
      $t->string('slug',80)->unique();
      $t->string('name',160);
    });
    Schema::create('user_role', function(Blueprint $t){
      $t->uuid('user_id'); $t->uuid('role_id');
      $t->string('scope',120)->nullable();
      $t->primary(['user_id','role_id']);
      $t->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
      $t->foreign('role_id')->references('id')->on('roles')->onDelete('cascade');
    });
    Schema::create('role_permission', function(Blueprint $t){
      $t->uuid('role_id'); $t->uuid('permission_id');
      $t->primary(['role_id','permission_id']);
      $t->foreign('role_id')->references('id')->on('roles')->onDelete('cascade');
      $t->foreign('permission_id')->references('id')->on('permissions')->onDelete('cascade');
    });
    Schema::create('audit_logs', function(Blueprint $t){
      $t->uuid('id')->primary();
      $t->uuid('user_id')->nullable();
      $t->string('action',120);
      $t->string('target_type',120)->nullable();
      $t->uuid('target_id')->nullable();
      $t->jsonb('metadata')->nullable();
      $t->ipAddress('ip')->nullable();
      $t->text('ua')->nullable();
      $t->timestampTz('created_at')->useCurrent();
      $t->foreign('user_id')->references('id')->on('users')->onDelete('set null');
      $t->index(['action']); $t->index(['created_at']);
    });
    Schema::create('invitations', function(Blueprint $t){
      $t->uuid('id')->primary();
      $t->string('email',190);
      $t->uuid('role_id_default')->nullable();
      $t->string('token',120)->unique();
      $t->timestampTz('expires_at');
      $t->timestampTz('used_at')->nullable();
      $t->uuid('invited_by')->nullable();
      $t->foreign('role_id_default')->references('id')->on('roles')->onDelete('set null');
      $t->foreign('invited_by')->references('id')->on('users')->onDelete('set null');
    });
    Schema::create('onboarding_states', function(Blueprint $t){
      $t->uuid('user_id')->primary();
      $t->string('step',60);
      $t->timestampTz('completed_at')->nullable();
      $t->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
    });
    Schema::create('budgets', function(Blueprint $t){
      $t->uuid('id')->primary();
      $t->string('name',160);
      $t->decimal('amount',14,2)->default(0);
      $t->timestampTz('created_at')->useCurrent();
    });
  }
  public function down(): void {
    Schema::dropIfExists('budgets');
    Schema::dropIfExists('onboarding_states');
    Schema::dropIfExists('invitations');
    Schema::dropIfExists('audit_logs');
    Schema::dropIfExists('role_permission');
    Schema::dropIfExists('user_role');
    Schema::dropIfExists('permissions');
    Schema::dropIfExists('roles');
    Schema::dropIfExists('users');
  }
};

Driver: set DB_CONNECTION=pgsql (or mysql, sqlsrv), charset utf8mb4.

Django (models → makemigrations → migrate)

# core/models.py
import uuid
from django.db import models

class User(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    name = models.CharField(max_length=120)
    email = models.EmailField(unique=True, max_length=190)
    password_hash = models.CharField(max_length=255)
    mfa_secret = models.CharField(max_length=255, null=True, blank=True)
    is_active = models.BooleanField(default=True)
    last_login_at = models.DateTimeField(null=True, blank=True)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

class Role(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    slug = models.CharField(max_length=60, unique=True)
    name = models.CharField(max_length=120)

class Permission(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    slug = models.CharField(max_length=80, unique=True)
    name = models.CharField(max_length=160)

class UserRole(models.Model):
    user = models.ForeignKey(User, on_delete=models.CASCADE)
    role = models.ForeignKey(Role, on_delete=models.CASCADE)
    scope = models.CharField(max_length=120, null=True, blank=True)
    class Meta:
        unique_together = ('user','role')

class RolePermission(models.Model):
    role = models.ForeignKey(Role, on_delete=models.CASCADE)
    permission = models.ForeignKey(Permission, on_delete=models.CASCADE)
    class Meta:
        unique_together = ('role','permission')

class AuditLog(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    user = models.ForeignKey(User, null=True, on_delete=models.SET_NULL)
    action = models.CharField(max_length=120)
    target_type = models.CharField(max_length=120, null=True, blank=True)
    target_id = models.UUIDField(null=True, blank=True)
    metadata = models.JSONField(null=True, blank=True)
    ip = models.GenericIPAddressField(null=True, blank=True)
    ua = models.TextField(null=True, blank=True)
    created_at = models.DateTimeField(auto_now_add=True)

class Invitation(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    email = models.EmailField(max_length=190)
    role_id_default = models.ForeignKey(Role, null=True, on_delete=models.SET_NULL)
    token = models.CharField(max_length=120, unique=True)
    expires_at = models.DateTimeField()
    used_at = models.DateTimeField(null=True, blank=True)
    invited_by = models.ForeignKey(User, null=True, on_delete=models.SET_NULL)

class OnboardingState(models.Model):
    user = models.OneToOneField(User, primary_key=True, on_delete=models.CASCADE)
    step = models.CharField(max_length=60)
    completed_at = models.DateTimeField(null=True, blank=True)

class Budget(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    name = models.CharField(max_length=160)
    amount = models.DecimalField(max_digits=14, decimal_places=2, default=0)
    created_at = models.DateTimeField(auto_now_add=True)

Driver: ENGINE = 'django.db.backends.postgresql' (or MySQL/SQL Server backends).

Spring Boot (JPA entities + Flyway/Liquibase)

(excerpt; mirror the canonical schema; Flyway is recommended for exact DDL parity)

@Entity
public class Role {
  @Id @GeneratedValue
  private UUID id;
  @Column(unique=true, length=60)
  private String slug;
  private String name;
  // getters/setters
}

@Entity
public class Permission {
  @Id @GeneratedValue
  private UUID id;
  @Column(unique=true, length=80)
  private String slug;
  private String name;
}

@Entity
@Table(uniqueConstraints = @UniqueConstraint(columnNames = {"role_id","permission_id"}))
public class RolePermission {
  @Id @GeneratedValue
  private UUID id;
  @ManyToOne(optional=false) Role role;
  @ManyToOne(optional=false) Permission permission;
}

Driver: org.postgresql.Driver (or MySQL/SQL Server). Use HikariCP pooling.

ASP.NET Core (EF Core mapping)

(excerpt; configure unique indexes and relations to reflect the canonical schema)

public class AppDbContext : DbContext {
  public DbSet<Role> Roles => Set<Role>();
  public DbSet<Permission> Permissions => Set<Permission>();
  public DbSet<RolePermission> RolePermissions => Set<RolePermission>();

  protected override void OnModelCreating(ModelBuilder b){
    b.Entity<Role>().HasIndex(x=>x.Slug).IsUnique();
    b.Entity<Permission>().HasIndex(x=>x.Slug).IsUnique();
    b.Entity<RolePermission>().HasIndex(x=>new { x.RoleId, x.PermissionId }).IsUnique();
    // ... map remaining entities and FKs to match the canonical schema
  }
}

Driver: Npgsql (Postgres), Microsoft.Data.SqlClient (SQL Server), or Pomelo (MySQL).

Node.js + Express (Prisma ORM example; PostgreSQL)

datasource db { provider = "postgresql"; url = env("DATABASE_URL") }
generator client { provider = "prisma-client-js" }

model User {
  id            String   @id @default(uuid())
  name          String
  email         String   @unique
  password_hash String
  mfa_secret    String?
  is_active     Boolean  @default(true)
  last_login_at DateTime?
  created_at    DateTime @default(now())
  updated_at    DateTime @updatedAt
  roles         UserRole[]
}

model Role {
  id    String @id @default(uuid())
  slug  String @unique
  name  String
  perms RolePermission[]
  users UserRole[]
}

model Permission {
  id    String @id @default(uuid())
  slug  String @unique
  name  String
  roles RolePermission[]
}

model UserRole {
  user     User @relation(fields: [user_id], references: [id])
  user_id  String
  role     Role @relation(fields: [role_id], references: [id])
  role_id  String
  scope    String?
  @@id([user_id, role_id])
}

model RolePermission {
  role          Role @relation(fields: [role_id], references: [id])
  role_id       String
  permission    Permission @relation(fields: [permission_id], references: [id])
  permission_id String
  @@id([role_id, permission_id])
}

model AuditLog {
  id          String   @id @default(uuid())
  user_id     String?
  action      String
  target_type String?
  target_id   String?
  metadata    Json?
  ip          String?
  ua          String?
  created_at  DateTime @default(now())
}

model Budget {
  id         String   @id @default(uuid())
  name       String
  amount     Decimal  @default(0)
  created_at DateTime @default(now())
}

Drupal (custom tables via .install, or use Entity API)

For non-content ERP tables, define the schema in mymodule.install (PHP array) or leverage Drupal’s Entity API if you want native field/permission handling. The routing and permission examples earlier show how to gate a page with view budget.

Optional but Recommended (All Frameworks)

• Email verification: require it before sensitive access.
• Two-factor (TOTP): enforce for admins/approvers.
• CSRF/session hardening: default-on for cookie sessions; if JWT, use short TTL + refresh rotation.
• Rate-limit & lockout: balance security with UX; consider whitelisting admin networks.
• Feature flags: make MFA/SSO/audit togglable without redeploys.

How to Choose Quickly

• Fast MVP → Laravel or Django (fastest to demo, clean code, strong ecosystems).
• Enterprise SSO & compliance → Spring Boot or ASP.NET Core (OIDC/SAML, claims, auditing).
• API/microservices & real-time → Node + Express (impose standards; consider NestJS).
• Content-first with complex editorial ACLs → Drupal.

Conclusion

This single article delivers both the database and the code for Step 1: Users & Roles, keeping the same business capability consistent across six ecosystems. You now have:

• A portable canonical schema (with UUIDs and JSONB audit).
• Concrete code for RBAC seeding, protecting a budget list, and auditing role changes in each framework.
• Clear pros/cons and when-to-choose guidance.
• A repeatable pattern you can apply to the remaining ArchPilot modules (Client Registry, Project/Budget, Approvals, Audit & Versioning, Alerts, Reporting).

Disclaimer

ArchPilot is a personal, non-commercial project created for educational, demonstrative, and professional portfolio purposes. It is not affiliated with, endorsed by, or derived from any proprietary ERP/CRM vendor.

• Open-source frameworks. All frameworks referenced (Laravel, Django, Spring Boot, ASP.NET Core, Node.js/Express, Drupal) are open-source or publicly licensed. Their names and logos are trademarks of their respective owners.
• Illustrative code. Code snippets and database schemas are examples meant to show architectural decisions across ecosystems. They are not production-ready and come without any warranty—use at your own risk. Before deploying, conduct full security reviews, load testing, logging/monitoring setup, backups, and disaster-recovery planning.
• Security & compliance. Implement proper controls (authentication, authorization, secret management, rate-limiting, CSRF protection, content security policies, encryption in transit/at rest, audit logging). You are responsible for compliance with applicable laws/regulations (e.g., GDPR, HIPAA, SOC 2, PCI-DSS, data residency). Example configurations (e.g., JWT lifetimes, cookie flags) must be validated for your threat model.
• Data & scenarios. Any client data, workflows, or business scenarios mentioned are fictional or anonymized. No confidential or third-party proprietary information is included.
• SSO/identity. Single sign-on integrations (OIDC/SAML) require correct configuration with your identity provider. Do not embed secrets in source code; use secure secret storage.
• No legal or professional advice. This material is not legal, security, or compliance advice. Consult qualified professionals for guidance specific to your organization.
• Performance & costs. Benchmarks, scaling guidance, and infrastructure suggestions are indicative and will vary by workload, environment, and cloud/provider pricing.

By using any part of ArchPilot’s materials, you agree that you assume all risks and responsibilities for verification, adaptation, and deployment in your own environment.