OpenGlimpse — AI Event Attendance & Delegation Management

School Coursework • Year 2

Individual Role: Backend & Systems Engineer (Programme Creation, Routes & Attendance)

OpenGlimpse is an AI-integrated event registration and delegate verification platform built for Singapore Chinese Chamber of Commerce & Industry (SCCCI) overseas business delegations, featuring facial-recognition verification, route management, and real-time attendance auditing.

GitHub: OpenGlimpseAI/OpenGlimpse

Academic Year: Year 2 Full-Stack & AI Architecture

Verification: Automated Isolated Test Fixtures

Live Webapp Demo GitHub

TECHNOLOGY STACK:

Node.js / Express PostgreSQL & Sequelize React (Vite) Socket.io Real-Time SQL Query Optimization RESTful API Design AI System Design Documentation

End-to-End System Pipeline & Data Flow

Delegation Request

Express API Gateway

Route Integrity Check

PostgreSQL (Sequelize)

Socket.io Real-Time Sync

Individual Engineering Contributions (Xin Ying)

1. Programme & Route Architecture Management

Architected and implemented the core backend services managing multi-day overseas business delegations and transport route allocation.

Built atomic programme and route creation flows ensuring each overseas delegation trip is deployed with designated route topologies (e.g., Coach A, Coach B).
Enforced strict single-route participant integrity rules in database models and controllers to prevent conflicting delegate bus assignments.
Engineered REST endpoints supporting programme status lifecycles (draft, active, completed) and batch delegate associations.

2. Real-Time Attendance Engine & SQL Bug Resolution

Developed the attendance tracking and live reporting engine, resolving critical database cross-join bugs through low-level SQL profiling.

Engineered manual and automated attendance marking controllers maintaining real-time present vs. missing participant counts.
Critical SQL Bug Fix: Discovered a latent cross-programme double-counting bug in the attendance summary query where missing rm.programme_id = ar.programme_id constraints caused delegates attending multiple programmes to be counted twice on shared routes; refactored subqueries in db.cjs to guarantee mathematical integrity.
Built ready-to-depart safety toggle APIs allowing trip managers to confirm vehicle readiness before proceeding between event stops.

-- Fixed attendance subquery preventing cross-programme double-counting anomaly
SELECT r.id, r.route_name,
       COUNT(DISTINCT ar.user_id) AS total_assigned,
       COUNT(DISTINCT CASE WHEN a.status = 'present' THEN a.user_id END) AS total_present
FROM routes r
JOIN route_members rm ON rm.route_id = r.id AND rm.programme_id = :programme_id
LEFT JOIN attendance a ON a.user_id = rm.user_id AND a.programme_id = :programme_id
GROUP BY r.id, r.route_name;

3. Comprehensive 34-Endpoint Test Suite

Authored a fully independent, zero-dependency Node.js test runner in tests/xinying/ validating all endpoints against a live PostgreSQL instance.

Implemented dynamic test fixture setup creating isolated programmes and route instances per test run to ensure strict test idempotency without leftover data pollution.
Authored 34 rigorous endpoint tests covering programmes, routes, delegate associations, attendance tracking, and departure validation with a 100% pass rate.
Diagnosed and patched controller catch-guard mismatches (fixing unhandled 500 exceptions into structured 400 Bad Request responses).

4. AI System Design, Schemas & Chat-Log Transcripts

Authored complete system specifications, database entity-relationship models, and transparent AI-assisted development logs.

Produced 14 comprehensive use cases (docs/xinying/use-cases.md) defining Admin/Trip Manager and Staff/Secretariat interaction workflows.
Documented complete API specifications and database schemas with Mermaid ER diagrams across 7 relational tables.
Maintained structured AI development logs (ai/xinying/ai-logs/) capturing prompt iterations, scope boundary decisions, and algorithmic reflections.