Software Engineer based in Ethiopia with 4 years of experience specializing in TypeScript, Next.js, React, TanStack, Node.js, PostgreSQL, and Golang. I build full-stack applications—from greenfield development and prototyping to debugging production issues, maintaining legacy codebases, and architecting systems.
Aqsa Academy Integrated System
ActiveThe Problem
Aqsa Academy previously managed all its administrative and financial operations manually. Student registrations were recorded on paper, and monthly fee tracking was done by hand, leading to frequent data entry errors and lost records. Teacher payroll was an equally complex manual process, requiring staff to track five distinct absence categories, loans, bonuses, fines, and base salaries to calculate net pay. For the finance and cashier departments, this manual approach created significant operational hurdles. They struggled to accurately track three income streams and four expense categories, reconcile payments, and generate summary receipts and payrolls. This fragmented, paper-based system was time-consuming, prone to human error, and lacked visibility across departments. This project revolutionizes these workflows by providing a unified, automated platform that streamlines operations for administrators, directors, finance officers, and cashiers, eliminating manual hustle and ensuring data integrity across the academy.
Tech Stack
System Architecture
The following diagram illustrates the high-level architecture of the Aqsa Academy system, showing the client PWA, the TanStack Start application hosted on Vercel, the Neon PostgreSQL database, and external SMS/email integrations.
Sequence Diagram
See all →The following diagram illustrates the core workflow of a cashier recording a student fee payment, including the approval process by finance and parent notification.
Sequence of a cashier recording a student fee payment, from student selection to parent notification.
Entity Relationship Diagram
The following diagram illustrates the core workflow of a cashier recording a student fee payment, including the approval process by finance and parent notification.
Deployment Architecture
The following diagram shows how the system is deployed across different nodes and services.
Challenges
Building the Aqsa Academy Integrated System came with significant technical challenges. Below are the key issues I encountered and how I resolved them.
Ethiopian Calendar Support
| Problem | Solution |
|---|---|
| The academy uses Ethiopian calendar for financial and attendance records, which doesn't align with the Gregorian calendar. | Created a dedicated ethiopian_months table with month names, order indices, and days per month. All financial and attendance records reference this table along with Ethiopian day numbers, enabling proper chronological sorting and reporting. |
Financial Workflow
| Problem | Solution |
|---|---|
| Transactions needed a proper approval workflow with audit trail. | Implemented a four-status system (draft → submitted → approved/rejected) with PostgreSQL enums. Added created_by_user_id and verified_by_user_id fields for complete audit trail of who created and who approved/rejected each transaction. |
| Cashiers could accidentally record duplicate payments for the same student in the same month. | When cashier selects a student, system dynamically queries for months that already have payments in draft, submitted, or rejected status and disables them from selection, preventing accidental duplicate fee entries while allowing corrections through rejection. |
| Needed to handle both student fees (income) and teacher-related expenses in a unified way. | Transactions table handles both using nullable foreign keys to students and teachers along with a type discriminator field. This maintains referential integrity while keeping a single ledger table. |
Teacher Payroll
| Problem | Solution |
|---|---|
| Teacher salary calculations were complex with multiple factors. | Created separate tables for teacher_attendances (with configurable absence types and penalties) and teacher_adjustments (fines/bonuses). Payroll processing aggregates base salary, attendance penalties, and adjustments to calculate net salary automatically. |
Access Control
| Problem | Solution |
|---|---|
| Different user roles needed different permissions with proper segregation of duties. | Implemented role-based access control using PostgreSQL enum for roles (admin, director, finance, cashier). Server functions check user roles before executing operations, ensuring proper segregation of duties. |
Offline Support
| Problem | Solution |
|---|---|
| The academy operates in areas with unreliable internet connectivity. | Built as a Progressive Web App using Vite, allowing the system to work offline. TanStack Query provides offline caching and automatic background synchronization when connection is restored. |
Type Safety
| Problem | Solution |
|---|---|
| Ensuring end-to-end type safety between server and client. | Used Drizzle ORM with TypeScript, sharing types between server functions and client code. This ensures end-to-end type safety and prevents runtime errors from mismatched data shapes. |
Security Considerations
Comprehensive security measures to protect data, prevent attacks, and ensure secure transactions.
| Area | Measure |
|---|---|
| Authentication | Password hashing using secure algorithms (bcrypt/argon2) stored in password_hash field. Sessions managed securely with HTTP-only cookies. |
| Authorization (RBAC) | Role-based access control using user_role enum. Server functions validate user permissions before executing any operation. Cashiers cannot approve their own transactions; only finance role has verification privileges. |
| Audit Trail | All financial transactions track created_by_user_id (cashier) and verified_by_user_id (finance) with timestamps. Teacher attendances and adjustments also track creator. |
| Data Validation | Server-side validation on all inputs using Drizzle ORM schemas. Ethiopian date combinations are validated against the ethiopian_months table to ensure day numbers are within month bounds. |
| HTTPS Everywhere | All communication between client and server encrypted via HTTPS. Database connections use SSL/TLS to Neon DB. |
| SQL Injection Prevention | Drizzle ORM provides parameterized queries by default, eliminating SQL injection risks. |
| Input Sanitization | User inputs sanitized before display to prevent XSS attacks. TanStack Start provides built-in XSS protection. |
| Secure Session Management | Sessions managed securely with proper expiration and rotation. Vercel provides secure hosting environment with DDoS protection. |
| Least Privilege Principle | Database user has only necessary permissions (CRUD on specific tables). No administrative database access from application code. |
Performance & Scalability
How the system handles load, keeps responses fast, and scales as traffic grows.
| Area | Measure |
|---|---|
| Database Indexing | Strategic indexes on frequently queried columns: student_name_idx, enrollment_status_idx, status_idx for transactions, unique_attendance_idx for teacher attendances, and composite indexes on foreign keys. |
| Serverless Database | Neon DB provides automatic scaling, branching, and point-in-time recovery. Database scales seamlessly with zero downtime during traffic spikes. |
| Vercel Edge Network | Application deployed on Vercel with global CDN caching for static assets. Server functions run close to users, reducing latency. |
| PWA Caching | Service workers cache application shell and frequently accessed data. TanStack Query provides client-side cache with configurable stale-while-revalidate strategy. |
| Optimistic Updates | TanStack Query enables optimistic UI updates where transactions appear instantly while being processed in background, improving perceived performance. |
| Batch Processing | Payroll calculations process all teachers for a given month in a single batch operation rather than individual transactions, reducing database round trips. |
| Connection Pooling | Drizzle ORM with Neon DB uses connection pooling to handle multiple concurrent requests efficiently. |
| Lazy Loading | TanStack Router supports code splitting and lazy loading, loading only the JavaScript needed for the current route, reducing initial bundle size. |
| Query Optimization | Complex payroll and summary reports use optimized SQL queries with appropriate joins and aggregations. Drizzle ORM allows raw SQL for complex reporting needs. |
| Stateless Architecture | Application designed to be stateless, allowing horizontal scaling on Vercel without session affinity concerns. |
No sponsors yet. Be the first!
Become a Sponsor