Database Architecture & Security

This platform utilizes Supabase, which provides a managed PostgreSQL database. Because our React frontends communicate directly with the database via the Supabase client, we rely heavily on database-level security rather than a traditional middleware API layer.

This guide provides a high-level overview of the data architecture and our security philosophy.

High-Level Data Domains

The database is divided into a few core domains.

1. Authentication & Profiles:
   • We use Supabase's native auth.users for secure credential management.
   • This is tied to a public users table that stores data like department and total points.
2. Curriculum Architecture:
   • Relational tables handle the courses -> modules -> resources hierarchy.
3. Assessment Engine:
   • Tables dedicated to quizzes and questions, including relational mappings for our multi-language translation support.
4. Telemetry & Tracking:
   • Tables that record quiz history and generated certificates.

Row Level Security (RLS)

Because the User and Admin applications query the database directly from the browser, Row Level Security (RLS) is strictly enforced on all tables.

RLS acts as our bouncer. Every time a query is made, PostgreSQL checks the user's JWT (JSON Web Token) to determine if they have permission to perform that action.

The Security Model

• Administrators: RLS policies look for an admin role claim. If verified, they are granted SELECT, INSERT, UPDATE, and DELETE privileges across the public schema's tables.
• Standard Employees: Standard users operate under strictly scoped RLS policies.
  • Read-Only: They can only SELECT courses and modules that are assigned to them.
  • Scoped Mutations: They can only INSERT or UPDATE rows that belong to their specific user_id. They cannot modify or even see another employee's data.

Triggers and Edge Functions

To keep the frontend applications lightweight and secure, we offload complex or sensitive logic to the backend:

• PostgreSQL Triggers: Used for automated internal data updates.
• Edge Functions: These are used for actions that require third-party API keys or elevated server-side privileges that bypass standard RLS (e.g., generating a secure PDF certificate).