EduKBridge Database Model

Comprehensive Data Architecture Report

Generated on: August 9, 2025

1 Executive Summary

The EduKBridge database is designed as a comprehensive educational content management platform with robust user management, subscription billing, and content sharing capabilities. Built for PostgreSQL with Hasura GraphQL integration, it supports scalable multi-tenant educational applications.

Core Tables

16

Main entities

Enum Types

11

Type definitions

Functions

8+

Helper functions

2 Database Overview

User Management

  • • Authentication & Profiles
  • • Role-based Access Control
  • • User Relationships
  • • Credit System

Content Management

  • • Study Summaries
  • • Worksheets
  • • Content Sharing
  • • Activity Tracking

Billing & Commerce

  • • Subscription Plans
  • • Order Management
  • • Payment Processing
  • • Coupon System

Key Technical Features

  • Row Level Security (RLS) enabled
  • Hasura GraphQL integration
  • Comprehensive indexing strategy
  • Audit trail with created/updated tracking
  • JSON data support for flexible content
  • Computed fields for GraphQL

3 Core Tables Analysis

👥 User Management Module

users

Core user authentication and basic information

Primary Key: id (UUID)
Key Fields: email, name, auth_provider_id, is_active, is_block

user_profiles

Extended user profile information and demographics

Primary Key: user_id (FK)
Key Fields: first_name, last_name, birthday, address, avatar_url

roles

System roles for authorization (admin, teacher, student)

Primary Key: id (UUID)
Key Fields: name (unique), description

user_relationships

Defines relationships between users (teacher-student, parent-child)

Relationship Types: parent, guardian, teacher, student, sibling, friend, mentor, colleague

📚 Content Management Module

user_content

Educational content (study summaries, worksheets) with metadata

Content Types: study_summary, worksheet
Status Flow: uploading → processing → ready → published
Visibility: public, private, shared, restricted

content_activities

Tracks user interactions with content

Activity Types: read, download, share, quiz
Tracking: started_at, finished_at, score, activity_data (JSON)

content_sharing

Manages content sharing between users

Relationships: content_id → shared_by → shared_to
Constraints: No self-sharing, unique content-user pairs

content_revisions

Version control for content changes

Tracking: previous_content, updated_content, change history

💳 Billing & Commerce Module

plans

Subscription plans with pricing and credit allocation

Billing Periods: monthly, quarterly, yearly, one_time
Pricing: price_cents, credits_per_cycle, auto_renew

user_subscriptions

Links users to subscription plans with status tracking

Status Types: active, canceled, past_due, trialing, incomplete
Period: started_at, ended_at, metadata (JSON)

orders

Purchase orders for plans and credits

Status Flow: pending → processing → completed/failed
Pricing: subtotal, discounts, total (all in cents)

user_credits & credit_transactions

Credit balance management with audit trail

Balance: Non-negative integer balance per user
Transactions: change_amount, reason, order_id, content_id

4 Data Types & Enumerations

relationship_type_enum

• parent
• guardian
• teacher
• student
• sibling
• friend
• mentor
• colleague

content_status_enum

• uploading
• processing
• ready
• published
• rejected
• archived

content_visibility_enum

• public
• private
• shared
• restricted

order_status_enum

• pending
• processing
• completed
• failed
• cancelled
• refunded

subscription_status_enum

• active
• canceled
• past_due
• trialing
• incomplete
• unpaid

payment_provider_enum

• gcash
• manual

5 Security & Permissions

🔒 Row Level Security (RLS)

All major tables have RLS enabled with comprehensive policies to ensure data isolation and proper access control.

User-Based Policies

  • Users can view/edit their own profiles and content
  • Users can view their own orders and subscriptions
  • Users can view their own credit transactions
  • Users can manage relationships they're part of

Role-Based Policies

  • Admins can view and manage all data
  • Teachers can view content from their students
  • Related users can view basic profile information
  • Content owners can view activities on their content

🔑 Authentication Integration

The system uses get_current_user_id() function to retrieve the current user from Hasura JWT claims or custom session variables for proper authorization.

6 Performance & Indexing Strategy

Primary Indexes

User Operations
• Email lookups
• Auth provider integration
• Active user filtering
Content Access
• User content filtering
• Status-based queries
• Visibility permissions

Composite Indexes

Billing Operations
• User subscription status
• Order status by user
• Transaction tracking
Analytics Queries
• Content activity tracking
• User engagement metrics
• Sharing statistics

🚀 Hasura Optimizations

Special indexes created for GraphQL query performance:

  • • Concurrent index creation for minimal downtime
  • • Composite indexes for complex relationship queries
  • • Partial indexes for frequently filtered states

7 Business Logic & Computed Fields

User Functions

user_full_name()

Concatenates first and last name for display

user_subscription_status()

Returns current subscription status (active/inactive)

user_credit_balance()

Returns current credit balance for user

Content Functions

user_content_summary()

Returns content metadata (word count, interactions, status)

content_interaction_stats()

Aggregates views, downloads, shares, and user engagement

Automated Triggers

Update Triggers:

Automatically update updated_at and updated_by fields on record changes

Audit Trail:

Track who created and modified records with created_by and user session integration

8 Relationships & Data Integrity

Foreign Key Relationships

User Hierarchy
users → user_profiles (1:1)
users → user_roles (1:N)
users → user_relationships (M:N)
Content Flow
users → user_content (1:N)
user_content → content_activities (1:N)
user_content → content_sharing (1:N)
Billing Chain
users → orders → order_transactions
plans → user_subscriptions
orders → credit_transactions

Data Integrity Constraints

Business Rules
  • • No negative credit balances
  • • No self-relationships between users
  • • Subscription end dates must be after start dates
  • • Activity finish times must be after start times
Unique Constraints
  • • User email addresses
  • • Role names
  • • User-role combinations
  • • Content sharing pairs

9 Technical Recommendations

✅ Strengths

  • • Comprehensive RLS implementation
  • • Well-structured enum types
  • • Proper indexing strategy
  • • Audit trail capabilities
  • • Hasura GraphQL integration
  • • Flexible JSON content storage

⚠️ Considerations

  • • Monitor JSON query performance
  • • Implement content versioning strategy
  • • Consider data archiving for old activities
  • • Plan for multi-region deployment
  • • Implement backup and recovery procedures

🔄 Scalability Recommendations

Performance:
  • • Implement connection pooling
  • • Consider read replicas for analytics
  • • Monitor slow query log
Maintenance:
  • • Regular VACUUM and ANALYZE
  • • Monitor index usage statistics
  • • Plan for schema migrations

Summary

The EduKBridge database represents a well-architected solution for educational content management with robust security, comprehensive user management, and scalable billing capabilities. The integration with Hasura GraphQL provides excellent API capabilities while maintaining strong data security through Row Level Security.

Security First

RLS policies ensure proper data isolation and access control across all tables

Performance Ready

Comprehensive indexing and optimization for GraphQL queries

Business Logic

Rich computed fields and functions support complex business requirements