📊 Database Schema

📋 Overview

The MyPit platform uses a comprehensive database schema built on Convex with PostgreSQL engine under the hood. This ensures a modern serverless database that provides real-time capabilities, TypeScript type safety, and automatic scaling. The schema is designed to support verifiable credentials, digital identity, privacy-preserving data sharing, and collaborative education features.

📖 Complete Schema Definition

Below is the complete Convex schema definition with all tables, indexes, and validation rules:

typescript

import { defineSchema, defineTable } from "convex/server";
import { v } from "convex/values";

const schema = defineSchema({
    // ===== AUTHENTICATION & USER MANAGEMENT =====
    
    users: defineTable({
        email: v.optional(v.string()),
        emailVerificationTime: v.optional(v.number()),
        image: v.optional(v.string()),
        isAnonymous: v.optional(v.boolean()),
        name: v.optional(v.string()),
        phone: v.optional(v.string()),
        phoneVerificationTime: v.optional(v.number()),
        did: v.optional(v.string()),
    })
        .index("by_email", ["email"])
        .index("by_phone", ["phone"]),

    profile: defineTable({
        name: v.optional(v.string()),
        email: v.optional(v.string()),
        phone: v.optional(v.string()),
        profilePhoto: v.optional(v.string()),
        address: v.optional(v.any()),
        userId: v.id("users"),
        identifier: v.optional(v.string()),
        credentials: v.optional(v.any()),
        updatedAt: v.optional(v.number()),
        createdAt: v.optional(v.number()),
        bio: v.optional(v.string()),
        gender: v.optional(v.string()),
        dateOfBirth: v.optional(v.string()),
        orgId: v.optional(v.string()),
    })
        .index("by_email", ["email"])
        .index("by_userId", ["userId"]),

    roleApplications: defineTable({
        applicantId: v.id("users"),
        requestedRole: v.union(
            v.literal("issuer"),
            v.literal("holder"),
            v.literal("verifier"),
            v.literal("data-consumer")
        ),
        status: v.union(
            v.literal("pending"),
            v.literal("under-review"),
            v.literal("approved"),
            v.literal("rejected"),
            v.literal("cancelled")
        ),
        fileUrl: v.optional(v.string()),
        reason: v.optional(v.string()),
        organization: v.optional(v.string()),
        contactEmail: v.optional(v.string()),
        contactPhone: v.optional(v.string()),
        experience: v.optional(v.string()),
        notes: v.optional(v.string()),
        fileId: v.optional(v.id("_storage")),
        documentName: v.optional(v.string()),
        documentSize: v.optional(v.string()),
        rejectionReason: v.optional(v.string()),
        updatedAt: v.optional(v.number()),
        appliedAt: v.number(),
        reviewedBy: v.optional(v.id("users")),
        reviewedAt: v.optional(v.number()),
    })
        .index("by_applicant", ["applicantId"])
        .index("by_role", ["requestedRole"])
        .index("by_status", ["status"]),

    // ===== BADGE MANAGEMENT SYSTEM =====
    
    badgeTemplates: defineTable({
        name: v.string(),
        type: v.union(
            v.literal("mobility"),
            v.literal("achievement"),
            v.literal("healthcare"),
            v.literal("finance")
        ),
        validityPeriod: v.optional(v.number()),
        isVerified: v.optional(v.boolean()),
        description: v.optional(v.string()),
        criteria: v.optional(v.string()),
        verifiedBy: v.optional(v.id("users")),
        verifiedAt: v.optional(v.number()),
        imageUrl: v.optional(v.string()),
        backgroundColor: v.optional(v.string()),
        textColor: v.optional(v.string()),
        price: v.optional(v.number()),
        isActive: v.optional(v.boolean()),
        orgId: v.optional(v.string()),
        ownerId: v.id("users"),
        createdAt: v.optional(v.number()),
        updatedAt: v.optional(v.number()),
    })
        .index("by_type", ["type"])
        .index("by_owner", ["ownerId"])
        .index("by_org", ["orgId"])
        .index("by_type_and_active", ["type", "isActive"])
        .index("by_owner_and_active", ["ownerId", "isActive"])
        .searchIndex("search_templates", {
            searchField: "name",
            filterFields: ["type", "isActive", "ownerId", "orgId"]
        }),

    holders: defineTable({
        name: v.string(),
        email: v.string(),
        phone: v.optional(v.string()),
        memo: v.optional(v.string()),
        createdAt: v.optional(v.number()),
        updatedAt: v.optional(v.number()),
        ownerId: v.id("users"),
        issuerDid: v.optional(v.string()),
        holderDid: v.optional(v.string()),
        holderId: v.optional(v.id("users")),
    })
        .index("by_email", ["email"])
        .index("by_owner", ["ownerId"])
        .index("by_holder", ["holderId"])
        .searchIndex("search_holders", {
            searchField: "name",
            filterFields: ["ownerId", "email"]
        }),

    issuedBadges: defineTable({
        templateId: v.id("badgeTemplates"),
        recipientId: v.id("holders"),
        issuerId: v.id("users"),
        type: v.union(
            v.literal("mobility"),
            v.literal("achievement"),
            v.literal("healthcare"),
            v.literal("finance")
        ),
        zkp: v.optional(v.any()),
        badgeData: v.optional(v.any()),
        vc: v.optional(v.any()),
        status: v.union(
            v.literal("issued"),
            v.literal("pending"),
            v.literal("expired")
        ),
        issuedAt: v.optional(v.number()),
        expiredAfter: v.optional(v.number()),
        expiresAt: v.optional(v.number()),
        issuerDid: v.optional(v.string()),
        holderDid: v.optional(v.string()),
        updatedAt: v.optional(v.number()),
    })
        .index("by_recipient", ["recipientId"])
        .index("by_template", ["templateId"])
        .index("by_issuer", ["issuerId"])
        .index("by_status", ["status"])
        .index("by_recipient_and_status", ["recipientId", "status"])
        .index("by_issuer_and_status", ["issuerId", "status"])
        .index("by_template_and_status", ["templateId", "status"])
        .searchIndex("search_badges", {
            searchField: "recipientId",
            filterFields: ["issuerId", "status", "recipientId"]
        }),

    badgeCollection: defineTable({
        name: v.string(),
        description: v.optional(v.string()),
        views: v.optional(v.number()),
        ownerId: v.id("users"),
        badgeIds: v.array(v.id("issuedBadges")),
        isShared: v.optional(v.boolean()),
        sharedUrl: v.optional(v.string()),
        updatedAt: v.optional(v.number()),
        isProfilePublic: v.optional(v.boolean()),
    })
        .index("by_owner", ["ownerId"])
        .index("by_shared", ["isShared"]),

    // ===== PRIVACY & DATA MANAGEMENT =====
    
    dataRequests: defineTable({
        requesterId: v.id("users"),
        issuerId: v.id("users"),
        purpose: v.string(),
        dataType: v.union(
            v.literal("mobility"),
            v.literal("achievement"),
            v.literal("healthcare"),
            v.literal("finance")
        ),
        dataSizeKB: v.number(),
        usagePeriodDays: v.number(),
        budget: v.number(),
        notes: v.optional(v.string()),
        status: v.union(
            v.literal("pending"),
            v.literal("approved"),
            v.literal("rejected"),
            v.literal("purchased")
        ),
        requestedAt: v.number(),
        updatedAt: v.optional(v.number()),
        reviewedBy: v.optional(v.id("users")),
        reviewedAt: v.optional(v.number()),
        rejectionReason: v.optional(v.string()),
        purchasedAt: v.optional(v.number()),
    })
        .index("by_requester", ["requesterId"])
        .index("by_issuer", ["issuerId"])
        .index("by_status", ["status"])
        .index("by_dataType", ["dataType"])
        .index("by_requester_and_status", ["requesterId", "status"])
        .index("by_issuer_and_status", ["issuerId", "status"]),

    sandboxes: defineTable({
        requesterId: v.id("users"),
        issuerId: v.id("users"),
        purpose: v.string(),
        dataType: v.union(
            v.literal("mobility"),
            v.literal("achievement"),
            v.literal("healthcare"),
            v.literal("finance")
        ),
        dataSizeKB: v.number(),
        usagePeriodDays: v.number(),
        budget: v.number(),
        status: v.union(
            v.literal("pending"),
            v.literal("approved"),
            v.literal("rejected"),
            v.literal("purchased")
        ),
        requestedAt: v.number(),
        availableAt: v.optional(v.number()),
        purchasedAt: v.optional(v.number()),
        resourceId: v.optional(v.string()),
        baseUrl: v.optional(v.string()),
        sandboxStatus: v.optional(v.union(
            v.literal("creating"),
            v.literal("running"),
            v.literal("stopped"),
            v.literal("failed")
        )),
        isDataReady: v.optional(v.boolean()),
    })
        .index("by_requester", ["requesterId"])
        .index("by_issuer", ["issuerId"])
        .index("by_status", ["status"])
        .index("by_sandbox_status", ["sandboxStatus"]),

    // ===== EDUCATION MODULE =====
    
    classes: defineTable({
        name: v.string(),
        description: v.optional(v.string()),
        ownerId: v.id("users"),
        inviteCode: v.string(),
        isArchived: v.optional(v.boolean()),
    })
        .index("by_owner", ["ownerId"])
        .index("by_inviteCode", ["inviteCode"])
        .searchIndex("search_classes", {
            searchField: "name",
            filterFields: ["ownerId", "isArchived"]
        }),

    students: defineTable({
        userId: v.optional(v.id("users")),
        name: v.string(),
        email: v.optional(v.string()),
        externalRef: v.optional(v.string()),
    })
        .index("by_user", ["userId"])
        .index("by_email", ["email"])
        .searchIndex("search_students", {
            searchField: "name",
            filterFields: ["email"]
        }),

    classStudents: defineTable({
        classId: v.id("classes"),
        studentId: v.id("students"),
    })
        .index("by_class", ["classId"])
        .index("by_student", ["studentId"])
        .index("by_class_and_student", ["classId", "studentId"]),

    // ===== TEAMS & COLLABORATION =====
    
    teams: defineTable({
        name: v.string(),
        description: v.optional(v.string()),
        creatorId: v.id("users"),
        classId: v.optional(v.id("classes")),
        inviteCode: v.string(),
        isPublic: v.optional(v.boolean()),
    })
        .index("by_creator", ["creatorId"])
        .index("by_class", ["classId"])
        .index("by_inviteCode", ["inviteCode"])
        .searchIndex("search_teams", {
            searchField: "name",
            filterFields: ["creatorId", "classId", "isPublic"]
        }),

    teamMembers: defineTable({
        teamId: v.id("teams"),
        userId: v.id("users"),
        classId: v.optional(v.id("classes")),
        role: v.union(v.literal("creator"), v.literal("member")),
        joinedAt: v.optional(v.number()),
        canCreateDoc: v.optional(v.boolean()),
        canEditDoc: v.optional(v.boolean()),
        canShareDoc: v.optional(v.boolean()),
        canExportDoc: v.optional(v.boolean()),
    })
        .index("by_team", ["teamId"])
        .index("by_user", ["userId"])
        .index("by_team_and_user", ["teamId", "userId"])
        .index("by_class", ["classId"]),

    documents: defineTable({
        teamId: v.id("teams"),
        title: v.string(),
        description: v.optional(v.string()),
        creatorId: v.id("users"),
        prosemirrorId: v.string(),
        content: v.optional(v.string()),
        lastModified: v.optional(v.number()),
        lastModifiedBy: v.optional(v.id("users")),
    })
        .index("by_team", ["teamId"])
        .index("by_creator", ["creatorId"])
        .index("by_prosemirror", ["prosemirrorId"])
        .searchIndex("search_documents", {
            searchField: "title",
            filterFields: ["teamId", "creatorId"]
        }),

    documentViews: defineTable({
        documentId: v.id("documents"),
        userId: v.id("users"),
        isViewing: v.optional(v.boolean()),
        lastUpdated: v.optional(v.number()),
    })
        .index("by_document", ["documentId"])
        .index("by_user", ["userId"])
        .index("by_document_and_user", ["documentId", "userId"]),

    messages: defineTable({
        teamId: v.id("teams"),
        authorId: v.id("users"),
        content: v.string(),
        type: v.union(v.literal("text"), v.literal("image")),
        storageId: v.optional(v.id("_storage")),
    })
        .index("by_team", ["teamId"])
        .index("by_author", ["authorId"]),

    joinRequests: defineTable({
        teamId: v.id("teams"),
        userId: v.id("users"),
        classId: v.optional(v.id("classes")),
        status: v.union(
            v.literal("pending"),
            v.literal("approved"),
            v.literal("rejected")
        ),
        requestedAt: v.optional(v.number()),
        respondedAt: v.optional(v.number()),
        respondedBy: v.optional(v.id("users")),
    })
        .index("by_team", ["teamId"])
        .index("by_user", ["userId"])
        .index("by_status", ["status"]),

    // ===== API MANAGEMENT & INTEGRATION =====
    
    api: defineTable({
        name: v.string(),
        owner: v.id("users"),
        hash: v.optional(v.string()),
        isVerified: v.optional(v.boolean()),
        permissions: v.optional(v.object({
            read: v.optional(v.boolean()),
            write: v.optional(v.boolean()),
            delete: v.optional(v.boolean()),
        })),
        secret: v.optional(v.string()),
        updatedAt: v.optional(v.number()),
    })
        .index("by_owner", ["owner"])
        .index("by_hash", ["hash"]),

    apiLogs: defineTable({
        apiId: v.id("api"),
        userId: v.optional(v.id("users")),
        apiName: v.optional(v.string()),
        requestMethod: v.union(
            v.literal("GET"),
            v.literal("POST"),
            v.literal("PUT"),
            v.literal("DELETE")
        ),
        requestBody: v.optional(v.any()),
        responseBody: v.optional(v.any()),
        requestHeaders: v.optional(v.any()),
        responseHeaders: v.optional(v.any()),
        requestTime: v.optional(v.number()),
        responseTime: v.optional(v.number()),
        status: v.union(
            v.literal("success"),
            v.literal("error"),
            v.literal("timeout")
        ),
        errorMessage: v.optional(v.string()),
        updatedAt: v.optional(v.number()),
    })
        .index("by_api", ["apiId"])
        .index("by_user", ["userId"])
        .index("by_status", ["status"])
        .index("by_method", ["requestMethod"]),

    // ===== SYSTEM LOGS & MONITORING =====
    
    verificationLogs: defineTable({
        id: v.string(),
        result: v.optional(v.any()),
        zkpResult: v.optional(v.any()),
    })
        .index("by_id", ["id"]),

    sysLogs: defineTable({
        threshold: v.optional(v.number()),
        numberOfShares: v.optional(v.number()),
        nodesInfo: v.optional(v.any()),
        tokenSecret: v.optional(v.any()),
    }),

    rateLimitLog: defineTable({
        userId: v.id("users"),
        action: v.string(),
        timestamp: v.number(),
    })
        .index("by_user_and_action", ["userId", "action"])
        .index("by_timestamp", ["timestamp"]),

    paymentLogs: defineTable({
        buyer: v.id("users"),
        issuer: v.id("users"),
        holder: v.optional(v.id("holders")),
        purpose: v.string(),
        amount: v.number(),
        usagePeriodDays: v.number(),
        dataSizeKB: v.number(),
        updatedAt: v.optional(v.number()),
    })
        .index("by_buyer", ["buyer"])
        .index("by_issuer", ["issuer"])
        .index("by_holder", ["holder"]),

    sandboxLogs: defineTable({
        buyer: v.id("users"),
        issuer: v.id("users"),
        holder: v.optional(v.id("holders")),
        activityName: v.union(
            v.literal("sandbox-creation"),
            v.literal("sandbox-deletion"),
            v.literal("sandbox-login"),
            v.literal("sandbox-error"),
            v.literal("sandbox-code-execution")
        ),
        amount: v.optional(v.number()),
        usagePeriodDays: v.optional(v.number()),
        dataSizeKB: v.optional(v.number()),
        updatedAt: v.optional(v.number()),
    })
        .index("by_buyer", ["buyer"])
        .index("by_issuer", ["issuer"])
        .index("by_holder", ["holder"])
        .index("by_activity", ["activityName"]),
});

export default schema;

🔍 Individual Table ERDs & Details

Below are focused ERD diagrams for each table showing their direct relationships and key fields.

🔐 Authentication & User Management

Users Table

Purpose: Core user authentication and identity management with Convex Auth integration.

Key Features:

Optional email/phone with verification timestamps
Anonymous user support via isAnonymous
DID (Decentralized Identifier) for blockchain identity
Indexes: email, phone

Connected Tables: profile, roleApplications, badgeTemplates, holders, issuedBadges, classes, teams, dataRequests, sandboxes, api

Profile Table

Purpose: Extended user profile information and organizational context.

Key Features:

One-to-one relationship with users
Organization linking via orgId
Flexible credentials storage
Indexes: email, userId

Role Applications Table

Purpose: Manage user requests for platform roles (issuer, holder, verifier, data-consumer).

Workflow: pending → under-review → approved/rejected/cancelled Indexes: applicantId, requestedRole, status

🏅 Badge Management System

Badge Templates Table

Purpose: Reusable badge definitions created by issuers.

Key Features:

Types: mobility, achievement, healthcare, finance
Pricing model for badge issuance
Verification system by admin users
Organization grouping
Comprehensive indexing by type, owner, org, status

Holders Table

Purpose: Badge recipients managed by issuers.

Key Features:

Dual identity: managed by issuer (ownerId) and linked to user (holderId)
DID integration for decentralized identity
Full-text search capability
Connected to payment and activity logs

Issued Badges Table

Purpose: Actual badge instances issued to holders.

Key Features:

Status tracking: issued, pending, expired
Zero-knowledge proof (ZKP) support
W3C Verifiable Credentials (VC) standard
Comprehensive indexing for queries by recipient, issuer, template, status

Badge Collection Table

Purpose: Curated collections of badges for sharing and display.

Key Features:

Portfolio showcase functionality
View tracking for analytics
Public sharing with unique URLs
Profile integration toggle

🔒 Privacy & Data Management

Data Requests Table

Purpose: Privacy-preserving data access requests between consumers and issuers.

Key Features:

Privacy-first data access model
Budget-based pricing
Multi-stage approval workflow
Data type categorization
Comprehensive indexing for dashboard queries

Sandboxes Table

Purpose: Secure containerized environments for privacy-preserving data analysis.

Key Features:

Kubernetes Job integration via resourceId
Dual status tracking: request status + container status
Shamir's Secret Sharing for data security
Complete audit trail via logs

🎓 Education Module

Classes Table

Purpose: Learning management system for educators and institutions.

Key Features:

Unique invite codes for enrollment
Archive functionality for completed classes
Full-text search by name
Team collaboration integration

Students Table

Purpose: Student registry with optional user account linkage.

Key Features:

Hybrid model: platform users + external students
External reference system for non-platform students
Full-text search capability

Class Students Table

Purpose: Many-to-many enrollment relationship.

Key Features:

Simple join table
Uniqueness enforcement via compound index
Efficient roster and enrollment queries

👥 Teams & Collaboration

Teams Table

Purpose: Collaborative workspaces for projects and classes.

Key Features:

Class integration (optional)
Invite-based joining
Public/private visibility
Full-text search capability

Team Members Table

Purpose: Team membership with granular permissions.

Key Features:

Role hierarchy: creator, member
Granular document permissions
Class context tracking
Complex indexing for multi-dimensional queries

Documents Table

Purpose: Collaborative document editing with real-time sync.

Key Features:

Notion-like editor integration via prosemirrorId
Real-time collaboration tracking
Version control with modification timestamps
Full-text search by title

Messages Table

Purpose: Team communication and chat.

Key Features:

Text and image message support
File attachment via Convex storage
Team-scoped conversations

Join Requests Table

Purpose: Team join request workflow management.

Workflow: User requests → Creator reviews → pending → approved/rejected

🔗 API Management & Logging

API Table

Purpose: API key management for external integrations.

Key Features:

Granular permissions: read, write, delete
API key hashing for security
Verification system
Complete request logging

API Logs Table

Purpose: Comprehensive API request/response logging and analytics.

Key Features:

Complete request/response capture
Performance tracking
Error logging and analysis
Method-based analytics

📊 System Logs & Security

Rate Limit Log Table

Purpose: Abuse prevention and rate limiting tracking.

Use Case: Track action frequency per user to enforce rate limits and prevent abuse.

Verification Logs Table

Purpose: Badge and credential verification audit trail.

Key Features:

Verification outcome tracking
Zero-knowledge proof result storage
Audit trail for compliance

Sys Logs Table

Purpose: Shamir's Secret Sharing system configuration and monitoring.

Key Features:

Cryptographic threshold configuration
Node distribution tracking
Security token management

Payment Logs Table

Purpose: Financial transaction tracking for data monetization.

Key Features:

Complete transaction audit
Data usage tracking
Multi-party payment model

Sandbox Logs Table

Purpose: Detailed sandbox activity and resource usage tracking.

Activity Types:

sandbox-creation
sandbox-deletion
sandbox-login
sandbox-error
sandbox-code-execution

Document Views Table

Purpose: Real-time document collaboration and presence tracking.

Key Features:

Real-time presence indicators
Collaboration analytics
User activity tracking

📈 Database Relationship Summary

Core Relationships

User-Centric:

users → profile (1:1)
users → roleApplications (1:many as applicant)
users → badgeTemplates (1:many as creator)
users → holders (1:many as manager)
users → issuedBadges (1:many as issuer)
users → classes (1:many as teacher)
users → teams (1:many as creator)

Badge System:

badgeTemplates → issuedBadges (1:many)
holders → issuedBadges (1:many)
issuedBadges ↔ badgeCollection (many:many via array)

Education System:

classes ↔ students (many:many via classStudents)
classes → teams (1:many)

Team Collaboration:

teams → teamMembers (1:many)
teams → documents (1:many)
teams → messages (1:many)
documents → documentViews (1:many)

Privacy & Sandboxes:

dataRequests → sandboxes (1:1)
sandboxes → sandboxLogs (1:many)
sandboxes → paymentLogs (1:many)

API & Logging:

api → apiLogs (1:many)
All entities → respective logs (1:many)

🔍 Index Strategy Summary

Primary Indexes

Identity: email, phone, userId
Ownership: ownerId, creatorId, applicantId
Status: status, isActive, isVerified
Temporal: createdAt, updatedAt, timestamp

Compound Indexes

Type + Status: ["type", "isActive"]
Owner + Time: ["ownerId", "createdAt"]
Team + User: ["teamId", "userId"]
Status Combinations: ["requesterId", "status"]

Search Indexes

Full-text: holders, badges, teams, classes, documents, students

This comprehensive schema supports all MyPit platform features while maintaining optimal query performance and data integrity.

📊 Database Schema ​

📋 Overview ​

📖 Complete Schema Definition ​

🔍 Individual Table ERDs & Details ​

🔐 Authentication & User Management ​

Users Table ​

Profile Table ​

Role Applications Table ​

🏅 Badge Management System ​

Badge Templates Table ​

Holders Table ​

Issued Badges Table ​

Badge Collection Table ​

🔒 Privacy & Data Management ​

Data Requests Table ​

Sandboxes Table ​

🎓 Education Module ​

Classes Table ​

Students Table ​

Class Students Table ​

👥 Teams & Collaboration ​

Teams Table ​

Team Members Table ​

Documents Table ​

Messages Table ​

Join Requests Table ​

🔗 API Management & Logging ​

API Table ​

API Logs Table ​

📊 System Logs & Security ​

Rate Limit Log Table ​

Verification Logs Table ​

Sys Logs Table ​

Payment Logs Table ​

Sandbox Logs Table ​

Document Views Table ​

📈 Database Relationship Summary ​

Core Relationships ​

🔍 Index Strategy Summary ​

Primary Indexes ​

Compound Indexes ​

Search Indexes ​

📊 Database Schema

📋 Overview

📖 Complete Schema Definition

🔍 Individual Table ERDs & Details

🔐 Authentication & User Management

Users Table

Profile Table

Role Applications Table

🏅 Badge Management System

Badge Templates Table

Holders Table

Issued Badges Table

Badge Collection Table

🔒 Privacy & Data Management

Data Requests Table

Sandboxes Table

🎓 Education Module

Classes Table

Students Table

Class Students Table

👥 Teams & Collaboration

Teams Table

Team Members Table

Documents Table

Messages Table

Join Requests Table

🔗 API Management & Logging

API Table

API Logs Table

📊 System Logs & Security

Rate Limit Log Table

Verification Logs Table

Sys Logs Table

Payment Logs Table

Sandbox Logs Table

Document Views Table

📈 Database Relationship Summary

Core Relationships

🔍 Index Strategy Summary

Primary Indexes

Compound Indexes

Search Indexes