Back to Blog
πŸ“¦

SCUAA Alumni Management System

Published onΒ·3 min read
Portfolio

🏒 Project Overview

An enterprise-grade Alumni Management System (AMS) custom-built for the Soochow University Alumni Association. Deeply integrated into the WordPress environment, it provides high-efficiency alumni database search, multi-dimensional academic history browsing, full-featured chapter/organization management, event tracking, and high-throughput Excel batch data operations.

  • Project Type: Enterprise WordPress Alumni & Organization Management Ecosystem
  • Core Tech Stack: PHP, MySQL/MariaDB, Tailwind CSS v4, SCSS, libsodium, SheetJS, AJAX

πŸ›‘ Challenges & Situation

  1. Legacy Data Constraints: The legacy database lacked proper foreign key constraints, and its schema carried historical designs (e.g., gender encoded as '1'/'0'). It required a robust data cleansing and auto-normalization compatibility layer.
  2. Stringent PII Security: Alumni records contain highly sensitive Personally Identifiable Information (PII). The system had to guarantee 100% encrypted storage at rest while still providing administrators with "precise search" and "fuzzy name matching" capabilities.
  3. Big Data Performance: Facing a massive alumni base and multi-layered chapter structures, traditional sequential queries and manual data entry were highly inefficient, necessitating seamless asynchronous (AJAX) experiences and bulk-processing pipelines.

πŸ› οΈ Technical Actions

1. Crypto-Searchable Database Architecture

  • libsodium Encryption: Encrypted sensitive PII fields (Names, Emails, Phone Numbers, and National ID Numbers) at the database layer to achieve a zero-leak security standard.
  • Blind Indexing (Precise Queries) Designed an internal Blind Index mechanism powered by HMAC hashing, allowing administrators to execute highly efficient PII exact-matches without decrypting the entire database.
  • Hashed N-gram Fuzzy Search: To overcome the limitation of SQL LIKE queries on encrypted data, a dedicated wp_alumnidb_name_ngrams index table was implemented (storing full-name, unigram, and bigram hashes). Queries require exact matching of all tokens via HAVING COUNT(DISTINCT ngram_hash) >= count($hashes), effectively preventing false positives while allowing secure fuzzy matching.

2. Modular Monolith Architecture & Security Verification

  • 7 Isolated Core Modules: Organized the application architecture into clean, decoupled modules: Search Engine, Profile Module, School Module, Club/Organization Module, Event Module, Mass Inout, and Activator.
  • Strict Privilege Guard: Utilized the WordPress Singleton pattern for global state management, enforcing manage_options capability checks and cryptographic Nonce verifications across more than 20 AJAX endpoints.

3. Asynchronous Data Cascading (Drill-down UI/UX)

  • 4-Panel Cascading Interface: Designed a seamless asynchronous drill-down view (School β†’ Department β†’ Graduation Year β†’ Alumni List), utilizing WordPress parameterized SQL prepared statements ($wpdb->prepare) to dynamically fetch datasets and optimize query speeds under heavy loads.

4. Intelligent Bulk Processing & Data Cleansing

  • Browser-side Excel Parsing: Integrated xlsx.full.min.js (SheetJS) to enable the frontend to read and parse Excel files natively.
  • Dual-Track Alumni Reconciliation: When importing members or board lists, the system matches records primarily by student/alumni numbers (alumni_id) and falls back to exact-match N-grams, ensuring smart duplicate prevention.
  • Data Cleansing Pipelines: Established a pre-write normalization layer (sanitize_alumni_data) that translates daily human input (e.g., "Male/Female", "M/F") into the legacy '1' or '0' database constraints, reducing manual auditing overhead.

5. Modernized Build Tooling

  • Tailwind CSS v4 & SCSS Compiler Setup: Abandoned heavy external UI frameworks in favor of a modern compiled design flow. Rebuilt the admin dashboard console using utility-first classes, providing a high-performance, responsive user experience.

πŸ“ˆ Results & Impact

  • Secure Compliance: Successfully deployed version 4.0/4.1 (Alfred), bringing 100% encrypted storage and high-availability search to all sensitive data fields.
  • Massive Efficiency Gains: The batch import layer handles up to 1,000 records per upload with auto-matching, streamlining what used to be days of manual database logging into just a few minutes.
  • Deep Linking Integration: Integrated seamless URL parameter states (org_id deep linking), allowing administrators to share exact chapter views instantly, boosting operational workflow speeds between the main association and regional chapters.