How do you translate a complex, manual institutional accreditation framework into a performant system?

Concept: Enterprise Database Architecture
Status: Production Monolith

---

The Question

Outcome-Based Education (OBE) accreditation is a convoluted, paper-heavy process in universities. To track student mastery, institutions must map individual exam questions to Course Outcomes (COs), which in turn map to Program Outcomes (POs).

How do we take a manual, dynamic academic spreadsheet workflow—containing multi-tiered department hierarchies, Lecture/Tutorial/Practical (LTP) teaching divisions, and optional student question choices—and translate it into a fast, secure, role-isolated database system?

---

Constraints

• Convoluted Business Hierarchies: A department can have multiple programs, courses run across multiple branches, and teachers assess students across different sections using distinct assessment tools (quizzes, midterms, external exams).
• N+1 Aggregation Latency: Generating a department-wide cohort attainment chart requires aggregating scores across thousands of students, tens of courses, and hundreds of dynamic questions. Doing this calculations in application memory (Node.js/Prisma relations) blocks the event loop and runs out of RAM.
• Granular Audit Boundaries: Exam scores cannot be altered after submission, and access to score entries must be strictly isolated by roles (Faculty, Department Administrator, Institute Coordinator).

---

Tradeoffs: Caching vs. Raw DB Aggregation

Generating the attainment matrices is the core performance bottleneck. We analyzed two architectures to solve the latency problem:

• Caching (Redis/Materialized Views): Save intermediate calculations to Redis. While fast, academic marks are updated continuously during grading weeks. Caching introduces complex invalidation pipelines and stale state risk.
• Database-Side Aggregation: Offload the entire matrix math to the PostgreSQL query engine using raw queries, returning only the final grouped metrics.

We chose Database-Side Aggregation. The database engine is written in C++ and handles grouping operations, indexing, and array lookups directly on disk or shared buffers. This avoids transferring 15,000 raw student rows over the network, returning a clean 1,500-row aggregated matrix instead.

---

Architecture

Below is the database aggregation pipeline:

┌──────────────────────────────────────────────┐
│  RAW CSV IMPORT                              │
│  Ingestion, validations, transaction rollback│
└──────────────────────┬───────────────────────┘
                       │ (DB Ingestion)
┌──────────────────────▼───────────────────────┐
│  POSTGRESQL STORAGE                          │
│  Relational constraints, indexed score tables│
└──────────────────────┬───────────────────────┘
                       │ (BOOL_AND, SUM aggregations)
┌──────────────────────▼───────────────────────┐
│  AGGREGATION CONTROLLER                      │
│  Runs raw queries, outputs coordinate series │
└──────────────────────┬───────────────────────┘
                       │ (Lightweight JSON payload)
┌──────────────────────▼───────────────────────┐
│  RECHARTS VIEWPORT (Frontend)                │
└──────────────────────────────────────────────┘

---

Implementation Details

Database-Level Attainment Calculation

Instead of querying relation tables through an ORM and aggregating in JavaScript, we write raw SQL queries in histogramController.js to compute student status and aggregate marks.

By grouping scores at the PostgreSQL engine level and using dynamic arrays (ANY(...)), we fetch calculations in under $120ms$:

// Database-level grouping query structure
const result = await prisma.$queryRaw`
  SELECT
    sqm.student_id,
    aq.assessment_id,
    SUM(CASE WHEN sqm.is_na = false THEN sqm.marks_obtained::numeric ELSE 0 END) AS raw_marks,
    BOOL_AND(sqm.is_na) AS all_na
  FROM student_question_marks sqm
  JOIN assessment_questions aq ON sqm.question_id = aq.id
  WHERE aq.assessment_id = ANY(${assessmentIds})
  GROUP BY sqm.student_id, aq.assessment_id
`;

---

Lessons Learned & Retrospectives (The Documentation-Code Drift)

During development, we encountered a severe disconnect between our written specifications and the production implementation code:

• The is_na (Absent) Bug: Our system design document states: "A score of '0' inside a cell is strictly parsed as is_na = true to prevent dropping class averages." However, teachers frequently enter actual 0 marks for students who submitted blank papers. The code had to override the spec to treat 0 as a real, failing mark, while handling empty strings as is_na = true. The documentation was left stale, causing confusion during API integrations.
• String-Based Multipliers: The design documentation specifies external multipliers are derived from DB columns. In production, we had to determine them dynamically by parsing if the assessment name starts with "EST" (multiplier = 0.40) or anything else (multiplier = 0.50).
• Takeaway: Documentation must be treated as a living code asset. In subsequent projects, we added checks to ensure markdown schema definitions match Prisma migrations.