Event Services Many-to-Many Architecture

The event services system uses a many-to-many architecture with a global service catalog, allowing services to be reused across multiple events while maintaining centralized management.

Overview

The many-to-many (M2M) architecture separates service definitions (stored in a global catalog) from service assignments (which events offer which services). This provides better reusability, consistency, and management compared to the previous one-to-many model.

📦 Global Service Catalog

All available services stored in one central location. Admins manage the catalog to ensure quality and consistency.

🔗 Event Assignments

Events reference services from the catalog. Same service can be assigned to multiple events.

♻️ Service Reusability

Update a service once, changes apply to all events using it. No duplicate service definitions.

🛡️ Enhanced Security

Role-based access: Admins manage catalog, event creators assign services to their events.

JSON Services: Simpler Alternative

For most use cases with predefined service catalogs, the JSON services approach offers a simpler solution with better performance and zero setup.

Quick Comparison:

Criterion	JSON Services	M2M (This Guide)
Setup	Zero - use config file	Create service records in DB
Flexibility	Fixed catalog	Dynamic catalog
Performance	Excellent (~10-20ms)	Good (~15-30ms)
Admin UI	Not needed	Required
Best For	Predefined catalogs	Dynamic catalogs

Use M2M when you need:

Dynamic service creation by admins
Rich service metadata (capacity, pricing, images)
Per-service configuration and analytics
Service lifecycle management (active/inactive)

Learn more: Event Services JSON Guide

Key Differences

Understanding the architectural change helps you work effectively with the new system.

Before: One-to-Many Architecture

How it worked:

Services created per event (event-specific)
Service details duplicated across events
No global service catalog
Event owners created and managed their own services

Example:

// Old way: Create service for specific event
await db.insertEventService({
  event_id: 'event-a',
  name: 'Haircuts',
  description: 'Professional haircuts',
  capacity: 20
})

// If you wanted the same service on another event, you had to create it again
await db.insertEventService({
  event_id: 'event-b',
  name: 'Haircuts', // Duplicate definition
  description: 'Professional haircuts',
  capacity: 20
})

Problems:

❌ Service duplication (“Haircuts” defined 50 times for 50 events)
❌ Inconsistent definitions (typos, different capacities)
❌ Difficult to update globally (must update all copies)
❌ No visibility into available services

After: Many-to-Many Architecture

How it works:

Services defined once in global catalog
Events reference existing services via assignments
Admins manage service catalog
Event creators assign from catalog

Example:

// New way: Service exists in global catalog
const catalogResponse = await fetch('/api/services')
const services = await catalogResponse.json()
const haircutsService = services.find(s => s.name === 'Haircuts')

// Assign existing service to event A
await fetch('/api/services/assign', {
  method: 'POST',
  body: JSON.stringify({
    event_id: 'event-a',
    service_id: haircutsService.id,
    csrf_token: csrfToken
  })
})

// Assign same service to event B (reuse, not duplicate)
await fetch('/api/services/assign', {
  method: 'POST',
  body: JSON.stringify({
    event_id: 'event-b',
    service_id: haircutsService.id, // Same service
    csrf_token: csrfToken
  })
})

Benefits:

✅ Single service definition (one “Haircuts” service)
✅ Consistent across all events
✅ Update once, affects all events using it
✅ Clear visibility of available services

Comparison Table

Aspect	One-to-Many (Old)	Many-to-Many (New)
Service Storage	Per event (duplicated)	Global catalog (deduplicated)
Service Updates	Per event (inconsistent)	Global (affects all events)
Service Creation	Any event owner	Admins only (team_admin+)
Event Assignments	Implicit (create service)	Explicit (assign from catalog)
Reusability	No (create duplicate)	Yes (reference same service)
Catalog Visibility	No central list	Yes (browse all services)

Working with the Service Catalog

The service catalog is the central repository of all available services. Only team_admin, admin, and super_admin roles can manage the catalog.

Prerequisites

To manage the service catalog:

✅ Authentication required
✅ Role: team_admin, admin, or super_admin (levels 4-7)
✅ CSRF token for mutations

To view the service catalog:

✅ Authentication required
✅ Any role (all authenticated users can view)

// Get all services in catalog
const response = await fetch('/api/services')
const services = await response.json()

console.log(services)
// [
//   {
//     id: 'uuid-123',
//     name: 'Haircuts',
//     description: 'Professional haircut service',
//     capacity: 20,
//     status: 'active',
//     display_order: 0,
//     created_at: '2025-12-01T10:00:00Z',
//     updated_at: '2025-12-01T10:00:00Z'
//   },
//   ...
// ]

import { getDatabase } from '#libs/database'

const db = getDatabase()
const services = await db.getServices({ status: 'active' })

Filtering:

// Get only active services
const activeServices = await fetch('/api/services?status=active')
  .then(r => r.json())

// Get services sorted by display order
const sortedServices = await fetch('/api/services?order_by=display_order')
  .then(r => r.json())

Creating Services

Only admins can create services in the global catalog.

API
Database

const response = await fetch('/api/services/create', {
  method: 'POST',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify({
    name: 'Haircuts',
    description: 'Professional haircut service provided by certified barbers',
    capacity: 20,
    status: 'active',
    display_order: 0,
    csrf_token: csrfToken
  })
})

if (response.ok) {
  const { id } = await response.json()
  console.log(`Service created: ${id}`)
} else if (response.status === 403) {
  console.error('Permission denied: Requires team_admin+ role')
}

import { getDatabase } from '#libs/database'

const db = getDatabase()

try {
  const serviceId = await db.insertService({
    name: 'Haircuts',
    description: 'Professional haircut service',
    capacity: 20,
    status: 'active',
    display_order: 0
  })

  console.log(`Service created: ${serviceId}`)
} catch (error) {
  console.error('Failed to create service:', error)
}

Validation Rules:

Field	Type	Required	Constraints
name	string	Yes	1-255 characters
description	string	No	Up to 500 characters
capacity	number	No	Positive integer (nullable)
status	string	Yes	’active’ or ‘inactive’
display_order	number	Yes	Non-negative integer

Updating Services

Updates to a service in the catalog affect all events using that service.

API
Database

// Update service capacity globally
const response = await fetch(`/api/services/${serviceId}`, {
  method: 'PUT',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify({
    capacity: 30, // Increase capacity from 20 to 30
    csrf_token: csrfToken
  })
})

if (response.ok) {
  console.log('Service updated globally')
  console.log('All events using this service now see capacity: 30')
}

await db.updateService(serviceId, {
  capacity: 30 // Affects all events using this service
})

⚠️ Important: Service updates are global. If you update “Haircuts” capacity from 20 to 30, all events offering Haircuts will reflect the new capacity.

Deleting Services

Deleting a service from the catalog cascades to all event assignments.

API
Database

const response = await fetch(`/api/services/${serviceId}`, {
  method: 'DELETE',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify({
    csrf_token: csrfToken
  })
})

if (response.ok) {
  console.log('Service deleted from catalog')
  console.log('All event assignments for this service also removed')
}

await db.deleteService(serviceId)
// CASCADE deletes all assignments

⚠️ Warning: Deleting a service removes it from all events using it. Consider setting status: 'inactive' instead to preserve history.

Assigning Services to Events

Event creators (and admins) can assign services from the catalog to their events.

Prerequisites

To assign services:

✅ Authentication required
✅ Must be event creator OR have team_admin+ role
✅ Service must exist in catalog
✅ CSRF token for mutations

Assigning a Service

API
Database

const response = await fetch('/api/services/assign', {
  method: 'POST',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify({
    event_id: eventId,
    service_id: serviceId,
    csrf_token: csrfToken
  })
})

if (response.ok) {
  const { id } = await response.json()
  console.log(`Service assigned: ${id}`)
} else if (response.status === 409) {
  console.error('Service already assigned to this event')
} else if (response.status === 403) {
  console.error('Permission denied: Must own event or be admin')
}

import { getDatabase } from '#libs/database'

const db = getDatabase()

try {
  const assignmentId = await db.assignServiceToEvent({
    event_id: eventId,
    service_id: serviceId
  })

  console.log(`Assignment created: ${assignmentId}`)
} catch (error) {
  if (error.message.includes('unique_event_service_assignment')) {
    console.error('Service already assigned to this event')
  } else {
    console.error('Failed to assign service:', error)
  }
}

Duplicate Prevention:

The system prevents assigning the same service to an event multiple times:

// First assignment succeeds
await assignService(eventId, 'haircuts-service-id') // ✅ OK

// Second assignment fails
await assignService(eventId, 'haircuts-service-id') // ❌ 409 Conflict

Getting Event Services

Retrieve all services assigned to an event, including full service details.

API
Database

const response = await fetch(`/api/services/assignments?event_id=${eventId}`)
const assignments = await response.json()

console.log(assignments)
// [
//   {
//     id: 'assignment-uuid-123',
//     event_id: 'event-uuid',
//     service_id: 'service-uuid',
//     assigned_at: '2025-12-01T10:00:00Z',
//     service: {
//       name: 'Haircuts',
//       description: 'Professional haircut service',
//       capacity: 20,
//       status: 'active'
//     }
//   },
//   ...
// ]

Using the data:

{assignments.map(assignment => (
  <div key={assignment.id}>
    <h3>{assignment.service.name}</h3>
    <p>{assignment.service.description}</p>
    <span>Capacity: {assignment.service.capacity}</span>
    <time>Assigned: {new Date(assignment.assigned_at).toLocaleDateString()}</time>
  </div>
))}

import { getDatabase } from '#libs/database'

const db = getDatabase()

const assignments = await db.getEventServiceAssignments(eventId)

assignments.forEach(assignment => {
  console.log(`Service: ${assignment.service.name}`)
  console.log(`Capacity: ${assignment.service.capacity}`)
  console.log(`Assigned: ${assignment.assigned_at}`)
})

⚠️ Note: Service details are fetched via JOIN, so you get the current service information (not a snapshot from assignment time).

Unassigning a Service

Remove a service assignment from an event (does not delete the service from catalog).

API
Database

const response = await fetch(`/api/services/unassign/${assignmentId}`, {
  method: 'DELETE',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify({
    csrf_token: csrfToken
  })
})

if (response.ok) {
  console.log('Service unassigned from event')
  console.log('Service still exists in catalog')
}

await db.unassignServiceFromEvent(assignmentId)

⚠️ Important: Unassigning removes the service from the event but does not delete the service from the global catalog.

Recurring Events

When you assign a service to a parent recurring event, the assignment is automatically copied to all child instances.

How It Works

Example
Code

// Parent event with 10 weekly instances
const parentEvent = {
  id: 'parent-uuid',
  name: 'Weekly Community Outreach',
  is_recurring: true,
  recurrence_frequency: 'weekly',
  recurrence_end_count: 10
}

// Assign service to parent event
await fetch('/api/services/assign', {
  method: 'POST',
  body: JSON.stringify({
    event_id: parentEvent.id,
    service_id: 'haircuts-service-id',
    csrf_token: csrfToken
  })
})

// Result: Service automatically assigned to parent + all 10 children
// Total: 11 assignment records created

Database after assignment:

-- event_service_assignments table
-- (all referencing the same service in the catalog)

| id  | event_id       | service_id         | assigned_at         |
|-----|----------------|--------------------|---------------------|
| a1  | parent-uuid    | haircuts-service   | 2025-12-01 10:00:00 |
| a2  | child-1-uuid   | haircuts-service   | 2025-12-01 10:00:00 |
| a3  | child-2-uuid   | haircuts-service   | 2025-12-01 10:00:00 |
| ... | ...            | ...                | ...                 |
| a11 | child-10-uuid  | haircuts-service   | 2025-12-01 10:00:00 |

import { getDatabase } from '#libs/database'

const db = getDatabase()

// Assign service to parent recurring event
const assignmentId = await db.assignServiceToEvent({
  event_id: parentEventId,
  service_id: serviceId
})

// Behind the scenes, the database abstraction:
// 1. Creates assignment for parent event
// 2. Finds all child instances (WHERE parent_event_id = parentEventId)
// 3. Batch inserts assignments for all children (single query)

console.log('Service assigned to parent + all child instances')

Benefits for Recurring Events

Before (One-to-Many):

Created 11 separate service definitions (parent + 10 children)
Updating service required 11 separate updates
Risk of inconsistency if updates were partial

After (Many-to-Many):

Creates 11 assignment references to the same service
Updating service updates it globally (one operation)
Guaranteed consistency across all instances

Example:

// Update service capacity
await db.updateService('haircuts-service-id', {
  capacity: 30 // Increase from 20 to 30
})

// Result: All 11 events (parent + 10 children) now show capacity 30
// No need to update each instance separately

Performance

Assignment copying uses batch insert for optimal performance:

-- Single batch INSERT query (not 10 separate queries)
INSERT INTO event_service_assignments (event_id, service_id, assigned_at)
VALUES
  ('child-1-uuid', 'service-uuid', CURRENT_TIMESTAMP),
  ('child-2-uuid', 'service-uuid', CURRENT_TIMESTAMP),
  -- ... all children in one query

Performance stats:

Before: 10 sequential INSERT queries (~500ms)
After: 1 batch INSERT query (~50ms)
Result: 10x faster, 90% query reduction

Authorization Model

The many-to-many architecture uses a dual authorization model: admins control the service catalog, while event creators control their event assignments.

Service Catalog Management

Who can manage the global service catalog:

Operation	Required Role	Authorization Check
View services	Any authenticated user	`auth.uid()` exists
Create service	team_admin, admin, super_admin	`user_roles` contains team_admin+
Update service	team_admin, admin, super_admin	`user_roles` contains team_admin+
Delete service	team_admin, admin, super_admin	`user_roles` contains team_admin+

Role Hierarchy:

✅ super_admin (level 7) - Full access
✅ admin (level 6) - Full access
✅ team_admin (level 4) - Full access
❌ team_manager (level 3) - View only
❌ volunteer (level 2) - View only
❌ member (level 1) - View only

Event Service Assignments

Who can assign/unassign services:

Operation	Required Role	Authorization Check
View assignments	Any authenticated user	`auth.uid()` exists
Assign service	Event creator OR team_admin+	`event.created_by = user_id` OR `team_admin+`
Unassign service	Event creator OR team_admin+	`event.created_by = user_id` OR `team_admin+`

Example Authorization Check:

import { canManageEventServices } from '#utils/event-service-authorization'

const canAssign = canManageEventServices({
  userRole: 'team_admin', // User's role
  userDbId: 'user-uuid-123', // User's database ID
  event: {
    id: 'event-uuid',
    created_by: 'user-uuid-123' // Event creator
  }
})

if (canAssign) {
  // User can assign services to this event
} else {
  // User cannot assign services (not owner, not admin)
}

Comparison with Old Model

Action	Old Model (011)	New Model (012)
Create service definition	Event owner	Admin only
Update service definition	Event owner (own events)	Admin (global)
Delete service definition	Event owner (own events)	Admin (global)
Assign service to event	Implicit (create service)	Event owner OR admin
Remove service from event	Event owner	Event owner OR admin

Key Change: Service catalog management now requires admin privileges, ensuring quality control and consistency.

Migration from Old Model

If you were using the old one-to-many model (migration 011), the migration to many-to-many (migration 012) automatically handles data migration.

What Happens During Migration

The migration process:

Creates new tables
- services (global catalog)
- event_service_assignments (junction table)
Deduplicates services
- Services with identical name/description/capacity are merged
- Creates single service definition in catalog
Preserves all assignments
- Every event-service relationship maintained
- Assignment timestamps preserved
Verifies data integrity
- Automated row count check
- RAISE EXCEPTION on data loss
- Transaction rollback if verification fails
Drops old table
- event_services table removed (BREAKING CHANGE)
- Old API endpoints no longer work

Data Preservation Guarantees

✅ All event-service relationships preserved

Old: 100 rows in event_services
New: 100 rows in event_service_assignments
Zero data loss

✅ Assignment timestamps preserved

created_at → assigned_at
Historical record of when service was assigned

✅ Service metadata preserved

Earliest created_at for merged services
Latest updated_at preserved

Example Migration

Before Migration:

-- event_services table: 50 rows
-- All with name='Haircuts', description='Professional haircuts', capacity=20

SELECT COUNT(*) FROM event_services
WHERE name = 'Haircuts'; -- Returns: 50

After Migration:

-- services table: 1 row (deduplicated)
SELECT COUNT(*) FROM services
WHERE name = 'Haircuts'; -- Returns: 1

-- event_service_assignments table: 50 rows
SELECT COUNT(*) FROM event_service_assignments
WHERE service_id IN (
  SELECT id FROM services WHERE name = 'Haircuts'
); -- Returns: 50 (all assignments preserved)

Result:

Space savings: 49 duplicate service definitions eliminated
Data preservation: All 50 event-service relationships maintained
Consistency: Guaranteed identical service definition across all events

API Migration Guide

Old Code (Migration 011):

// Create service for event
await fetch('/api/event-services/create', {
  method: 'POST',
  body: JSON.stringify({
    event_id: eventId,
    name: 'Haircuts',
    description: 'Professional haircuts',
    capacity: 20,
    csrf_token: csrfToken
  })
})

New Code (Migration 012):

// Step 1: Check if service exists in catalog
const catalogResponse = await fetch('/api/services')
const services = await catalogResponse.json()

let serviceId = services.find(s => s.name === 'Haircuts')?.id

if (!serviceId) {
  // Step 2a: Create service in catalog (admin only)
  const createResponse = await fetch('/api/services/create', {
    method: 'POST',
    body: JSON.stringify({
      name: 'Haircuts',
      description: 'Professional haircuts',
      capacity: 20,
      csrf_token: csrfToken
    })
  })
  const { id } = await createResponse.json()
  serviceId = id
}

// Step 3: Assign service to event
await fetch('/api/services/assign', {
  method: 'POST',
  body: JSON.stringify({
    event_id: eventId,
    service_id: serviceId,
    csrf_token: csrfToken
  })
})

Best Practices

Follow these guidelines for optimal use of the service catalog and assignments.

Creating Services

Use descriptive, standardized names:

// ✅ Good: Clear, professional name
name: 'Haircuts'

// ❌ Bad: Lowercase, informal
name: 'haircut service'

// ❌ Bad: Too specific (should be in description)
name: 'Haircuts by Bob the Barber'

Add detailed descriptions:

// ✅ Good: Helpful details
description: 'Professional haircut service provided by certified barbers. Includes trim and styling.'

// ❌ Bad: Redundant with name
description: 'Haircuts'

// ❌ Bad: Empty when context would help
description: ''

Set realistic capacity limits:

// ✅ Good: Based on actual resources
capacity: 20 // We have 2 barbers, 10 slots each

// ❌ Bad: Arbitrary or unrealistic
capacity: 1000 // We don't have 1000 slots

Start with ‘active’ status:

// ✅ Good: Service ready to use
status: 'active'

// ❌ Bad: Creating inactive service (clutter)
status: 'inactive'

Managing the Catalog

Review and merge duplicates periodically:

-- Find potential duplicates
SELECT name, description, capacity, COUNT(*) as count
FROM services
GROUP BY name, description, capacity
HAVING COUNT(*) > 1;

Update service details globally:

// ✅ Good: Update affects all events
await db.updateService(serviceId, {
  description: 'Updated description with more details'
})

// ❌ Bad: Creating new service instead of updating
await db.insertService({
  name: 'Haircuts', // Duplicate!
  description: 'New description'
})

Use ‘inactive’ status instead of deleting:

// ✅ Good: Preserves history
await db.updateService(serviceId, { status: 'inactive' })

// ❌ Bad: Loses all assignments
await db.deleteService(serviceId) // CASCADE deletes all assignments!

Event Assignments

Browse catalog before creating new services:

// ✅ Good: Check existing services first
const services = await db.getServices({ status: 'active' })
const existingService = services.find(s => s.name === 'Haircuts')

if (existingService) {
  // Use existing service
  await assignServiceToEvent(eventId, existingService.id)
} else {
  // Create new service (if admin)
  const newServiceId = await createService({ name: 'Haircuts', ... })
  await assignServiceToEvent(eventId, newServiceId)
}

// ❌ Bad: Always creating without checking
const newServiceId = await createService({ name: 'Haircuts', ... })

Use existing services for consistency:

// ✅ Good: Reuse catalog service
await assignServiceToEvent(eventId, 'haircuts-service-id')

// ❌ Bad: Trying to create event-specific service (not supported)
// This requires admin privileges and creates global service
await createService({ name: 'Haircuts for Event A', ... })

Assign services to parent recurring events:

// ✅ Good: Assign to parent (auto-copies to children)
await assignServiceToEvent(parentEventId, serviceId)

// ❌ Bad: Manually assigning to each child instance
for (const childEvent of childEvents) {
  await assignServiceToEvent(childEvent.id, serviceId)
}

Troubleshooting

Common issues and their solutions.

”Service already assigned to this event”

Error:

409 Conflict: Service has already been assigned to this event

Cause:

Attempting to assign the same service to an event multiple times
UNIQUE constraint prevents duplicate assignments

Solution:

// ✅ Check if service is already assigned before attempting
const assignments = await fetch(`/api/services/assignments?event_id=${eventId}`)
  .then(r => r.json())

const alreadyAssigned = assignments.some(a => a.service_id === serviceId)

if (!alreadyAssigned) {
  await assignService(eventId, serviceId)
} else {
  console.log('Service already assigned')
}

“Permission denied”

Error:

403 Forbidden: Insufficient permissions to manage service catalog

Cause:

User does not have required role (team_admin, admin, or super_admin)
User trying to assign service to someone else’s event

Solution:

// For catalog management
if (userRole === 'team_admin' || userRole === 'admin' || userRole === 'super_admin') {
  // User can create/update/delete services
  await createService({ name: 'New Service', ... })
} else {
  console.error('Service catalog management requires admin privileges')
}

// For event assignments
const canAssign = event.created_by === userId || userRole.includes('admin')
if (canAssign) {
  await assignService(eventId, serviceId)
} else {
  console.error('Must own event or be admin to assign services')
}

“Service not found”

Error:

404 Not Found: Service does not exist

Cause:

Service was deleted from catalog
Incorrect service ID
Service is inactive (if filtering by status)

Solution:

// ✅ Verify service exists before using
const service = await fetch(`/api/services/${serviceId}`)
  .then(r => r.ok ? r.json() : null)

if (service) {
  await assignService(eventId, serviceId)
} else {
  console.error('Service not found in catalog')
  // Browse catalog to find correct service
  const allServices = await fetch('/api/services').then(r => r.json())
  console.log('Available services:', allServices)
}

Deleted services cascade delete all assignments

Symptom:

Deleting a service removes it from all events

Cause:

CASCADE delete constraint on foreign key

Solution:

// ✅ Use inactive status instead of deleting
await db.updateService(serviceId, { status: 'inactive' })
// Service hidden from UI but assignments preserved

// ❌ Avoid: Deleting service
await db.deleteService(serviceId)
// CASCADE deletes all event_service_assignments!

Recovery:

If accidentally deleted, check database backups
No rollback available (permanent deletion)
Recreate service and reassign to events

Event Services Components - React component integration guide
Events System - Creating and managing events
Recurring Events - Recurring event patterns and instance management
Event Services M2M Architecture - Technical deep dive into the many-to-many refactor

Event Services Many-to-Many Architecture

Overview

JSON Services: Simpler Alternative

Key Differences

Before: One-to-Many Architecture

After: Many-to-Many Architecture

Comparison Table

Working with the Service Catalog

Prerequisites

Browsing Services

Creating Services

Updating Services

Deleting Services

Assigning Services to Events

Prerequisites

Assigning a Service

Getting Event Services

Unassigning a Service

Recurring Events

How It Works

Benefits for Recurring Events

Performance

Authorization Model

Service Catalog Management

Event Service Assignments

Comparison with Old Model

Migration from Old Model

What Happens During Migration

Data Preservation Guarantees

Example Migration

API Migration Guide

Best Practices

Creating Services

Managing the Catalog

Event Assignments

Troubleshooting

”Service already assigned to this event”

“Permission denied”

“Service not found”

Deleted services cascade delete all assignments

Related Documentation