schema.ts

Proposed Convex schema and indexes for MemberPulse V3

This page contains a proposed convex/schema.ts for MemberPulse V3.

It is designed to satisfy:

• normalization (tables for unbounded collections)
• tenant scoping (clientId on tenant data)
• practical indexes for list screens

File location in repo: convex/schema.ts (this doc page is under integrations/backend/convex/schema)

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

/**
 * MemberPulse V3 – Convex schema blueprint
 *
 * Notes:
 * - This is a normalized, tenant-scoped schema intended for a shared Convex deployment.
 * - Most tenant-owned tables include `clientId` and are indexed by it.
 * - Timestamps are stored as epoch milliseconds (number).
 */

export default defineSchema({
  clients: defineTable({
    name: v.string(),
    slug: v.string(),
    status: v.union(v.literal("active"), v.literal("inactive")),
    createdAt: v.number(),
    updatedAt: v.number(),
  })
    .index("by_slug", ["slug"])
    .index("by_status", ["status"]),

  users: defineTable({
    email: v.string(),
    displayName: v.optional(v.string()),

    // Global role for authorization. Tenant roles may be refined later into a permissions table.
    role: v.union(
      v.literal("ROLE_PLATFORM_ADMIN"),
      v.literal("ROLE_CLIENT_ADMIN"),
      v.literal("ROLE_CLIENT_USER"),
      v.literal("ROLE_MEMBER"),
      v.literal("ROLE_SPONSOR_ADMIN"),
      v.literal("ROLE_SPONSOR_USER")
    ),

    // For tenant-scoped users. Platform admins may have this unset.
    clientId: v.optional(v.id("clients")),

    // If this user corresponds to a member, link to their profile.
    memberProfileId: v.optional(v.id("memberProfiles")),

    status: v.union(v.literal("active"), v.literal("inactive"), v.literal("suspended")),
    createdAt: v.number(),
    updatedAt: v.number(),
    lastLoginAt: v.optional(v.number()),
  })
    .index("by_email", ["email"])
    .index("by_clientId_role", ["clientId", "role"])
    .index("by_memberProfileId", ["memberProfileId"]),

  memberProfiles: defineTable({
    clientId: v.id("clients"),
    userId: v.optional(v.id("users")),

    firstName: v.string(),
    lastName: v.string(),
    email: v.string(),

    dateOfBirth: v.optional(v.string()),
    contactNumber: v.optional(v.string()),
    mobile: v.optional(v.string()),

    company: v.optional(v.string()),
    position: v.optional(v.string()),

    address: v.optional(v.string()),
    city: v.optional(v.string()),
    state: v.optional(v.string()),
    country: v.optional(v.string()),
    postcode: v.optional(v.string()),

    linkedinUrl: v.optional(v.string()),
    websiteUrl: v.optional(v.string()),

    isProfilePublic: v.optional(v.boolean()),

    crmExternalId: v.optional(v.string()),
    accountingExternalId: v.optional(v.string()),

    createdAt: v.number(),
    updatedAt: v.number(),
    status: v.union(v.literal("active"), v.literal("inactive")),
  })
    .index("by_clientId", ["clientId"])
    .index("by_clientId_email", ["clientId", "email"])
    .index("by_userId", ["userId"]),

  membershipPlans: defineTable({
    clientId: v.id("clients"),
    name: v.string(),
    slug: v.string(),
    priceCents: v.number(),
    currency: v.string(),
    billingPeriod: v.union(v.literal("monthly"), v.literal("yearly")),
    gracePeriodDays: v.optional(v.number()),
    requiredCpdPoints: v.optional(v.number()),
    status: v.union(v.literal("active"), v.literal("inactive"), v.literal("archived")),
    createdAt: v.number(),
    updatedAt: v.number(),
  })
    .index("by_clientId", ["clientId"])
    .index("by_clientId_slug", ["clientId", "slug"])
    .index("by_clientId_status", ["clientId", "status"]),

  userMemberships: defineTable({
    clientId: v.id("clients"),
    memberProfileId: v.id("memberProfiles"),
    membershipPlanId: v.id("membershipPlans"),

    startAt: v.number(),
    endAt: v.number(),
    graceEndAt: v.optional(v.number()),

    priceCents: v.number(),
    currency: v.string(),

    status: v.union(
      v.literal("active"),
      v.literal("grace"),
      v.literal("expired"),
      v.literal("cancelled")
    ),

    orderId: v.optional(v.id("orders")),

    createdAt: v.number(),
    updatedAt: v.number(),
  })
    .index("by_clientId_memberProfileId", ["clientId", "memberProfileId"])
    .index("by_membershipPlanId", ["membershipPlanId"])
    .index("by_clientId_status", ["clientId", "status"]),

  groupMemberships: defineTable({
    clientId: v.id("clients"),
    name: v.string(),
    description: v.optional(v.string()),

    totalSeats: v.number(),
    occupiedSeats: v.number(),

    startAt: v.number(),
    endAt: v.number(),

    priceCents: v.number(),
    currency: v.string(),

    groupAdminMemberProfileId: v.optional(v.id("memberProfiles")),

    membershipPlanId: v.optional(v.id("membershipPlans")),
    orderId: v.optional(v.id("orders")),

    status: v.union(v.literal("active"), v.literal("inactive"), v.literal("archived")),
    createdAt: v.number(),
    updatedAt: v.number(),
  })
    .index("by_clientId", ["clientId"])
    .index("by_clientId_status", ["clientId", "status"]),

  groupMembers: defineTable({
    clientId: v.id("clients"),
    groupMembershipId: v.id("groupMemberships"),
    memberProfileId: v.id("memberProfiles"),
    role: v.union(v.literal("admin"), v.literal("member")),
    seatAllocatedAt: v.number(),
  })
    .index("by_groupMembershipId", ["groupMembershipId"])
    .index("by_memberProfileId", ["memberProfileId"])
    .index("by_clientId_groupMembershipId", ["clientId", "groupMembershipId"]),

  events: defineTable({
    clientId: v.id("clients"),
    name: v.string(),
    shortDescription: v.optional(v.string()),
    description: v.optional(v.string()),

    startAt: v.number(),
    endAt: v.number(),
    timezone: v.string(),

    isOnline: v.boolean(),
    location: v.optional(v.string()),
    meetingLink: v.optional(v.string()),

    featuredImageUrl: v.optional(v.string()),

    status: v.union(
      v.literal("draft"),
      v.literal("published"),
      v.literal("completed"),
      v.literal("cancelled")
    ),

    createdAt: v.number(),
    updatedAt: v.number(),
  })
    .index("by_clientId", ["clientId"])
    .index("by_clientId_startAt", ["clientId", "startAt"])
    .index("by_clientId_status", ["clientId", "status"]),

  eventRegistrations: defineTable({
    clientId: v.id("clients"),
    eventId: v.id("events"),
    memberProfileId: v.id("memberProfiles"),

    registrationAt: v.number(),
    status: v.union(
      v.literal("pending"),
      v.literal("confirmed"),
      v.literal("checked_in"),
      v.literal("cancelled")
    ),

    paymentStatus: v.union(v.literal("pending"), v.literal("paid"), v.literal("failed")),
    ticketType: v.optional(v.string()),
    checkedInAt: v.optional(v.number()),
    cancelledAt: v.optional(v.number()),

    orderId: v.optional(v.id("orders")),
  })
    .index("by_eventId", ["eventId"])
    .index("by_memberProfileId", ["memberProfileId"])
    .index("by_clientId_status", ["clientId", "status"])
    .index("by_clientId_eventId", ["clientId", "eventId"]),

  courses: defineTable({
    clientId: v.id("clients"),
    name: v.string(),
    shortDescription: v.optional(v.string()),
    description: v.optional(v.string()),
    author: v.optional(v.string()),

    publishAt: v.optional(v.number()),
    startAt: v.optional(v.number()),
    closeAt: v.optional(v.number()),

    cpdPoints: v.optional(v.number()),

    featuredImageUrl: v.optional(v.string()),

    status: v.union(v.literal("draft"), v.literal("published"), v.literal("archived")),
    createdAt: v.number(),
    updatedAt: v.number(),
  })
    .index("by_clientId", ["clientId"])
    .index("by_clientId_status", ["clientId", "status"]),

  courseSubjects: defineTable({
    clientId: v.id("clients"),
    courseId: v.id("courses"),
    order: v.number(),
    name: v.string(),
    description: v.optional(v.string()),
    durationMinutes: v.optional(v.number()),

    contentType: v.union(
      v.literal("video"),
      v.literal("document"),
      v.literal("zoom"),
      v.literal("quiz")
    ),
    contentUrl: v.optional(v.string()),
    thumbnailUrl: v.optional(v.string()),

    isQuestionsRequired: v.optional(v.boolean()),
    numberOfQuestions: v.optional(v.number()),
  })
    .index("by_courseId", ["courseId"])
    .index("by_clientId_courseId", ["clientId", "courseId"]),

  courseEnrollments: defineTable({
    clientId: v.id("clients"),
    courseId: v.id("courses"),
    memberProfileId: v.id("memberProfiles"),

    enrolledAt: v.number(),
    completedAt: v.optional(v.number()),
    progressPercentage: v.number(),

    status: v.union(v.literal("active"), v.literal("completed"), v.literal("expired")),
    paymentStatus: v.union(v.literal("pending"), v.literal("paid"), v.literal("failed")),

    orderId: v.optional(v.id("orders")),
  })
    .index("by_courseId", ["courseId"])
    .index("by_memberProfileId", ["memberProfileId"])
    .index("by_clientId_courseId_memberProfileId", ["clientId", "courseId", "memberProfileId"]),

  cpdPoints: defineTable({
    clientId: v.id("clients"),
    memberProfileId: v.id("memberProfiles"),
    points: v.number(),
    category: v.optional(v.string()),

    sourceType: v.union(v.literal("course"), v.literal("event"), v.literal("resource"), v.literal("manual")),
    sourceId: v.optional(v.string()),

    earnedAt: v.optional(v.number()),
    isComplete: v.boolean(),
  })
    .index("by_memberProfileId", ["memberProfileId"])
    .index("by_clientId_memberProfileId", ["clientId", "memberProfileId"]),

  resources: defineTable({
    clientId: v.id("clients"),
    name: v.string(),
    shortDescription: v.optional(v.string()),
    description: v.optional(v.string()),

    resourceType: v.optional(v.string()),
    fileUrl: v.optional(v.string()),
    featuredImageUrl: v.optional(v.string()),

    isFree: v.boolean(),
    nonMemberPriceCents: v.optional(v.number()),
    currency: v.optional(v.string()),

    status: v.union(v.literal("draft"), v.literal("published"), v.literal("archived")),
    createdAt: v.number(),
    updatedAt: v.number(),
  })
    .index("by_clientId", ["clientId"])
    .index("by_clientId_status", ["clientId", "status"]),

  supportTickets: defineTable({
    clientId: v.id("clients"),
    memberProfileId: v.id("memberProfiles"),
    subject: v.string(),
    description: v.string(),
    status: v.union(v.literal("open"), v.literal("awaiting_response"), v.literal("closed")),
    createdAt: v.number(),
    updatedAt: v.number(),
    assignedToUserId: v.optional(v.id("users")),
    crmExternalId: v.optional(v.string()),
  })
    .index("by_clientId", ["clientId"])
    .index("by_clientId_status", ["clientId", "status"])
    .index("by_memberProfileId", ["memberProfileId"]),

  supportTicketMessages: defineTable({
    clientId: v.id("clients"),
    ticketId: v.id("supportTickets"),
    authorUserId: v.optional(v.id("users")),
    authorMemberProfileId: v.optional(v.id("memberProfiles")),
    body: v.string(),
    isPublic: v.boolean(),
    createdAt: v.number(),
  })
    .index("by_ticketId", ["ticketId"])
    .index("by_clientId_ticketId", ["clientId", "ticketId"]),

  orders: defineTable({
    clientId: v.id("clients"),
    memberProfileId: v.optional(v.id("memberProfiles")),

    orderType: v.union(
      v.literal("membership"),
      v.literal("event"),
      v.literal("course"),
      v.literal("resource"),
      v.literal("job_posting"),
      v.literal("sponsorship"),
      v.literal("corporate_subscription")
    ),
    totalCents: v.number(),
    currency: v.string(),
    status: v.union(v.literal("pending"), v.literal("paid"), v.literal("failed"), v.literal("refunded")),
    referenceId: v.optional(v.string()),
    createdAt: v.number(),
    updatedAt: v.number(),
  })
    .index("by_clientId", ["clientId"])
    .index("by_clientId_status", ["clientId", "status"])
    .index("by_memberProfileId", ["memberProfileId"]),

  crmTags: defineTable({
    // System tags have no clientId; tenant tags are scoped.
    clientId: v.optional(v.id("clients")),
    isSystem: v.boolean(),
    name: v.string(),
    slug: v.string(),
    description: v.optional(v.string()),
    category: v.union(
      v.literal("member"),
      v.literal("event"),
      v.literal("course"),
      v.literal("order"),
      v.literal("system")
    ),
    appliesTo: v.array(v.string()),
    isActive: v.boolean(),
    createdAt: v.number(),
    updatedAt: v.number(),
  })
    .index("by_slug", ["slug"])
    .index("by_clientId", ["clientId"])
    .index("by_clientId_slug", ["clientId", "slug"]),

  crmSyncRecords: defineTable({
    clientId: v.id("clients"),
    provider: v.union(v.literal("salesforce"), v.literal("hubspot")),
    entityType: v.string(),
    memberPulseEntityId: v.string(),
    crmEntityId: v.optional(v.string()),
    status: v.union(v.literal("pending"), v.literal("synced"), v.literal("failed")),
    errorMessage: v.optional(v.string()),
    lastSyncAt: v.optional(v.number()),
  })
    .index("by_clientId_status", ["clientId", "status"])
    .index("by_clientId_provider", ["clientId", "provider"]),

  integrationConnections: defineTable({
    clientId: v.id("clients"),
    provider: v.string(),
    status: v.union(v.literal("connected"), v.literal("error"), v.literal("disconnected")),

    // Non-secret config only. Secrets should be stored in env or encrypted blobs.
    settings: v.optional(v.any()),
    credentialsEncrypted: v.optional(v.string()),

    updatedAt: v.number(),
    updatedByUserId: v.optional(v.id("users")),
  })
    .index("by_clientId", ["clientId"])
    .index("by_clientId_provider", ["clientId", "provider"])
    .index("by_clientId_status", ["clientId", "status"]),

  auditLogs: defineTable({
    clientId: v.id("clients"),
    actorUserId: v.optional(v.id("users")),
    action: v.string(),
    targetTable: v.optional(v.string()),
    targetId: v.optional(v.string()),
    metadata: v.optional(v.any()),
    createdAt: v.number(),
  }).index("by_clientId_createdAt", ["clientId", "createdAt"]),
});

Related

• Implementation Overview
• Normalization
• Tenant Scoping
• Security
• Domain Rules

Features

schema.ts

Acceptance Criteria

Frontend

• Developer-facing configuration and usage is documented and internally consistent.

Backend / API

• Convex implementation matches the rules and contracts described on this page.

Permissions

• Tenant scoping and access controls are enforced as described.

Business Rules

• Domain rules/invariants are enforced as described.

Error Handling

• Access violations and validation failures produce deterministic errors.