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:

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

JSON Services (Simple)
M2M (Advanced)

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

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

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:

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

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

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

---
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

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

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)