Events System

The events system provides comprehensive event management capabilities including creation, querying, updates, and deletion with built-in user attribution and row-level security.

Overview

The events system supports:

• One-time Events: Single-date events with optional time and duration
• Recurring Events: Daily, weekly, monthly, yearly patterns (see Recurring Events)
• User Attribution: Track who created each event
• Security: Row-level security with ownership-based access control
• Performance: Optimized indexes for common queries

📅 Flexible Events

Create events with dates, times, locations, and descriptions. Optional fields for maximum flexibility.

🔒 Secure by Default

Row-level security ensures users can only modify their own events. Admins can moderate any event.

⚡ Fast Queries

Optimized indexes provide 10-100x faster queries for common patterns like upcoming events and user calendars.

🔄 Recurring Support

Built-in support for recurring events with flexible patterns. Generate instances automatically.

Event Services: Two Approaches

The events system supports two approaches for associating services with events:

1. JSON Services (Recommended for simple use cases) - Array of service IDs stored directly in the events.services column
2. Many-to-Many Architecture - Global service catalog with event_service_assignments junction table

JSON Services (Simple)

Quick Example:

const eventData: EventData = {
  name: 'Community Outreach',
  event_date: '2026-02-15',
  services: ['haircuts', 'showers', 'clothing'], // JSON array
  created_by: userId,
}

const eventId = await db.insertEvent(eventData)

Best for:

• Predefined service catalogs (services.config.ts)
• Simple categorization needs
• Maximum performance (Supabase JSONB + GIN index)
• Zero database setup

Learn more: Event Services JSON Guide

M2M (Advanced)

Quick Example:

// 1. Create event
const eventId = await db.insertEvent(eventData)

// 2. Assign services
await db.assignServiceToEvent(eventId, serviceId1)
await db.assignServiceToEvent(eventId, serviceId2)

// 3. Query with service details
const assignments = await db.getEventServiceAssignments(eventId)

Best for:

• Dynamic service catalogs
• Rich service metadata (capacity, pricing, descriptions)
• Admin-managed service creation
• Service analytics and reporting

Learn more: Event Services M2M Guide

Quick Decision Guide

Criterion	JSON Services	M2M Architecture
Setup	Zero	Requires service records
Flexibility	Fixed catalog	Dynamic catalog
Performance (Supabase)	Excellent (~10-20ms)	Good (~15-30ms)
Type Safety	Compile-time	Runtime
Best For	Predefined catalogs	Dynamic catalogs

Recommendation

For most use cases, start with JSON services for simplicity. Migrate to M2M later if you need dynamic service management or rich metadata.

Coexistence

Both systems coexist in the database. You can use JSON for new events and M2M for legacy events, or choose per event type. No forced migration required.

Quick Start

Creating Events

import { getDatabase } from '#libs/database'
import type { EventData } from '#libs/database'

const db = getDatabase()

// Simple event
const eventData: EventData = {
  name: 'Team Meeting',
  event_date: '2025-12-15',
  start_time: '14:00',
  duration: 60, // minutes
  location: 'Conference Room A',
  description: 'Monthly team sync',
  created_by: userId, // User UUID from Clerk
}

const eventId = await db.insertEvent(eventData)
console.log(`Event created: ${eventId}`)

Querying Events

// Get upcoming events
const upcomingEvents = await db.getEvents({
  from_date: new Date().toISOString().split('T')[0],
  limit: 50,
})

// Get user's events
const myEvents = await db.getEvents({
  created_by: userId,
  limit: 20,
})

// Events in specific date range
const decemberEvents = await db.getEvents({
  from_date: '2025-12-01',
  to_date: '2025-12-31',
})

Event Structure

Required Fields

Field	Type	Description	Example
name	string	Event name/title	”Team Meeting”
event_date	string	Date in YYYY-MM-DD format	”2025-12-15”

Optional Fields

Field	Type	Description	Example
location	string	Physical or virtual location	”Conference Room A”
description	string	Detailed description	”Monthly team sync meeting”
start_time	string	Time in HH:MM format	”14:00”
duration	number	Duration in minutes	60
created_by	string	User UUID (from Clerk)	“uuid-from-clerk”

Auto-Generated Fields

Field	Type	Description
id	string	Unique UUID
created_at	string	ISO 8601 timestamp
updated_at	string	ISO 8601 timestamp

Common Use Cases

Public Event Calendar

Display all upcoming events to authenticated users:

async function getPublicCalendar() {
  const db = getDatabase()

  const events = await db.getEvents({
    from_date: new Date().toISOString().split('T')[0],
    limit: 100,
  })

  return events.sort((a, b) => {
    if (a.event_date !== b.event_date) {
      return a.event_date.localeCompare(b.event_date)
    }
    return (a.start_time || '').localeCompare(b.start_time || '')
  })
}

User Dashboard

Show events created by specific user:

async function getUserDashboard(userId: string) {
  const db = getDatabase()

  return await db.getEvents({
    created_by: userId,
    from_date: new Date().toISOString().split('T')[0],
    limit: 20,
  })
}

Event Details Page

Fetch single event with full details:

async function getEventDetails(eventId: string) {
  const db = getDatabase()
  const event = await db.getEventById(eventId)

  if (!event) {
    throw new Error('Event not found')
  }

  return event
}

Update Event

Modify existing event (only owner or admin):

async function updateEventLocation(eventId: string, newLocation: string) {
  const db = getDatabase()

  const success = await db.updateEvent(eventId, {
    location: newLocation,
  })

  if (!success) {
    throw new Error('Event not found or no permission')
  }
}

Delete Event

Remove event (only owner or admin):

async function deleteEvent(eventId: string) {
  const db = getDatabase()

  const success = await db.deleteEvent(eventId)

  if (!success) {
    throw new Error('Event not found or no permission')
  }
}

Query Options

Filtering

Filter events by various criteria:

By Creator

const myEvents = await db.getEvents({
  created_by: userId,
})

By Date Range

const events = await db.getEvents({
  from_date: '2025-12-01',
  to_date: '2025-12-31',
})

Upcoming Only

const upcoming = await db.getEvents({
  from_date: new Date().toISOString().split('T')[0],
})

Pagination

Handle large result sets with pagination:

const PAGE_SIZE = 20
const currentPage = 2

const events = await db.getEvents({
  limit: PAGE_SIZE,
  offset: (currentPage - 1) * PAGE_SIZE,
})

Field Selection

Optimize performance by selecting specific fields:

const events = await db.getEvents({
  fields: ['id', 'name', 'event_date', 'start_time'],
  limit: 100,
})

Benefits:

• Reduced bandwidth
• Faster queries
• Lower memory usage

Security & Access Control

Authentication Required

All event operations require authentication via Clerk JWT.

Access Levels

👤 Regular Users

• View all events (SELECT)
• Create own events (INSERT)
• Update own events (UPDATE)
• Delete own events (DELETE)

👑 Super Admin

• View all events
• Create own events
• Update ANY event (moderation)
• Delete ANY event (moderation)

🔧 Service Role

• Full unrestricted access
• Used by webhooks and system operations
• Bypasses all RLS policies

Ownership Protection

// ✅ User can update their own event
await db.updateEvent(eventId, {
  location: 'New Location',
}) // Success if user owns event

// ❌ User CANNOT create event as another user
await db.insertEvent({
  name: 'Meeting',
  event_date: '2025-12-15',
  created_by: 'other-user-uuid', // Fails - security violation
})

// ❌ User CANNOT transfer ownership
await db.updateEvent(eventId, {
  created_by: 'other-user-uuid', // Fails - ownership transfer blocked
})

Admin Moderation

Super admin users can update or delete any event:

// super_admin can delete any event via RLS policy
async function moderateEvent(eventId: string) {
  const db = getDatabase()
  const success = await db.deleteEvent(eventId) // Works for super_admin
  return success
}

Performance Optimization

Optimized Indexes

The events table has 6 indexes for common query patterns:

Index	Optimizes	Speed Gain
idx_events_date_time	Chronological event listing	10-100x
idx_events_created_by	User-specific queries	5-50x
idx_events_user_date	User events chronologically	10-100x
idx_events_is_recurring	Filter recurring events	5-10x
idx_events_parent_event_id	Find instances of recurring series	5-10x
idx_events_recurrence_lookup	Complex recurring event queries	10-50x

Query Optimization Tips

1. Use specific date ranges:

// ✅ GOOD - Uses date index
const events = await db.getEvents({
  from_date: '2025-12-01',
  to_date: '2025-12-31',
})

// ❌ BAD - No index benefit
const allEvents = await db.getEvents()
const decemberEvents = allEvents.filter(e =>
  e.event_date >= '2025-12-01' && e.event_date <= '2025-12-31'
)

2. Select only needed fields:

// ✅ GOOD - Less data transferred
const events = await db.getEvents({
  fields: ['id', 'name', 'event_date'],
  limit: 100,
})

// ❌ BAD - Fetches all fields
const events = await db.getEvents({ limit: 100 })

3. Paginate large results:

// ✅ GOOD - Paginated
const PAGE_SIZE = 20
for (let page = 1; page <= totalPages; page++) {
  const events = await db.getEvents({
    limit: PAGE_SIZE,
    offset: (page - 1) * PAGE_SIZE,
  })
  processEvents(events)
}

// ❌ BAD - Load everything at once
const allEvents = await db.getEvents() // Could be thousands!

Integration Examples

Astro Page

src/pages/events.astro

---
import { getDatabase } from '#libs/database'

const db = getDatabase()
const upcomingEvents = await db.getEvents({
  from_date: new Date().toISOString().split('T')[0],
  limit: 50,
})
---

<div class="events-calendar">
  {upcomingEvents.map(event => (
    <div class="event-card">
      <h3>{event.name}</h3>
      <p>Date: {event.event_date}</p>
      {event.start_time && <p>Time: {event.start_time}</p>}
      {event.location && <p>Location: {event.location}</p>}
      {event.description && <p>{event.description}</p>}
    </div>
  ))}
</div>

API Endpoint

src/pages/api/events.ts

import type { APIRoute } from 'astro'
import { getDatabase } from '#libs/database'
import type { EventData } from '#libs/database'

export const POST: APIRoute = async ({ locals, request }) => {
  // 1. Check authentication
  if (!locals.userId) {
    return new Response(JSON.stringify({ error: 'Unauthorized' }), {
      status: 401,
      headers: { 'Content-Type': 'application/json' },
    })
  }

  try {
    const data: EventData = await request.json()

    // 2. Add user attribution
    data.created_by = locals.userId

    // 3. Create event
    const db = getDatabase()
    const eventId = await db.insertEvent(data)

    return new Response(
      JSON.stringify({ success: true, eventId }),
      { status: 201, headers: { 'Content-Type': 'application/json' } }
    )
  } catch (error) {
    return new Response(
      JSON.stringify({ error: 'Failed to create event' }),
      { status: 500, headers: { 'Content-Type': 'application/json' } }
    )
  }
}

React Component

src/components/react/EventList.tsx

import { useEffect, useState } from 'react'
import type { Event } from '#libs/database'

export function EventList() {
  const [events, setEvents] = useState<Event[]>([])
  const [loading, setLoading] = useState(true)

  useEffect(() => {
    async function fetchEvents() {
      const response = await fetch('/api/events')
      const data = await response.json()
      setEvents(data.events)
      setLoading(false)
    }

    fetchEvents()
  }, [])

  if (loading) return <div>Loading events...</div>

  return (
    <div className="event-list">
      {events.map(event => (
        <div key={event.id} className="event-card">
          <h3>{event.name}</h3>
          <p>{event.event_date} {event.start_time}</p>
        </div>
      ))}
    </div>
  )
}

Next Steps

• Recurring Events - Implement recurring event patterns
• Database Architecture - Understand the abstraction layer
• Database Switching - Switch between providers

Technical References

• Database API Reference - Complete API documentation
• Events Table Feature - Schema and security details
• Migration History - Events migrations (007-010)