Multi-Tier Institutional ERP & Real-Time Academic Workflow Engine
How a strictly typed relational schema in PostgreSQL managed via Prisma ORM and layered middleware RBAC enforced zero-leak permission boundaries across 5 user tiers while reducing dashboard load times by 55%.
Conflicting Permissions, Relational Bottlenecks & Cross-Tier Data Leaks
Educational institutions operate on complex, overlapping permission hierarchies across 5 distinct tiers: SuperAdmin, Admin, Teacher, Student, and Parent. Typical architectural failure points include:
- Cross-Student Data Leaks: Parents inadvertently viewing grades or disciplinary marks of other children due to flawed client-side query filters.
- Attendance Log Bottlenecks: High-frequency live attendance logging by dozens of teachers simultaneously during morning roll call causing database lock contention.
- Client-Side Hydration Bloat: Over-fetching relational records to the browser and filtering permissions in React state rather than enforcing them at the query engine level.
Layered Defense: Edge Middleware + Row-Level Query Extensions
I engineered a dual-layer security architecture that guarantees data segregation at both the Edge network boundary and the relational database query layer:
Edge Auth & Token Validation
Clerk Auth middleware running on Next.js Edge inspects cryptographic JWT claims before routing requests, instantly rejecting unauthorized route traversal without consuming server compute.
Prisma Row-Level Isolation Extensions
Custom Prisma client extensions automatically inject user-specific tenant and family filters into every SQL query. Even if a client tampered with URL parameters, the database query layer physically denies unauthorized record retrieval.
Indexed PostgreSQL Relational Core
Carefully structured compound foreign-key indexes linking attendance ledgers, classroom rosters, and exam grading reduce complex multi-table JOIN latency by 55%.
Server Components Data Aggregation
Analytics dashboards are aggregated server-side via Next.js React Server Components (RSC) and validated with Zod, streaming pre-computed chart data to Recharts with zero client-side data leaks.
Technologies & Architecture Matrix
Need Multi-Tenant Architecture or RBAC Governance?
I architect multi-tier permission models, relational databases with Prisma and PostgreSQL, and scalable Next.js platforms.