Calendar Management

Backend NodeJS
1

How can we manage recurring events?

Storing all instances of recurring events separately may not be efficient, as it can lead to excessive data storage and performance issues. To overcome this, we can implement a strategy that stores a single record covering the entire recurrence range. Instead of saving each occurrence individually, we define the recurrence pattern within a single entry, allowing us to generate instances dynamically when needed. This approach optimizes storage while providing the flexibility to modify or exclude specific occurrences without affecting the entire series.

Structuring Recurring Events: Storing Common Details and Event Ranges

To efficiently manage recurring events, we separate event data into two tables:

  1. Main Table (Event Metadata Table) – Stores common details that apply to all occurrences and cannot be modified per instance.
  2. Events Table (Event Instances Table) – Stores specific event occurrences, including start and end dates, recurrence details, and days when the event repeats.

Database Design

1. Event Metadata Table

This table contains information that remains the same across all event instances.

CREATE TABLE event_metadata (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255),
    description TEXT,
    location VARCHAR(255),
    frequency VARCHAR(20),    -- Example: 'weekly'  or 'monthly' 
    organizer_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
2. Events Table

This table tracks individual event instances while indicating which days the event repeats.

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    metadata_id INT REFERENCES event_metadata(id) ON DELETE CASCADE,
    start_date DATE,
    end_date DATE,
    start_time TEXT,
    end_time TEXT,   
    is_recurring BOOLEAN,
    recurring_days JSONB, -- Example: ["Monday", "Wednesday", "Friday"]
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Scenario: Creating, Listing, Updating, and Deleting Recurring Events Using the rrule Library

After setting up the event_metadata and events tables, let’s go through a real-world scenario to demonstrate:

  1. How data is created
  2. How recurring events are listed dynamically using the rrule library
  3. How updates are handled by splitting
  4. How deletions are handled by adjusting the recurrence range

Step 1: Creating a Recurring Event

Example

  • A user creates an event “Team Meeting” that happens every Monday, Wednesday, and Friday at 10:00 AM - 11:00 AM from April 1, 2025, to June 30, 2025.

Data Inserted in event_metadata (Stores Common Event Details)

INSERT INTO event_metadata (title, description, location, organizer_id, frequency)
VALUES ('Team Meeting', 'Team weekly updates', 'Conference Room A', 1, 'weekly')
RETURNING id;

Let’s assume the returned metadata_id = 1.

Data Inserted in events (Stores Recurring Dates and Ranges)

INSERT INTO events (metadata_id, start_date, end_date, start_time, end_time, is_recurring, recurring_days)
VALUES (1, '2025-04-01', '2025-06-30', '10:00', '11:00', TRUE, '["Monday", "Wednesday", "Friday"]'::jsonb);

Step 2: Listing Recurring Events Using the rrule Library

Instead of storing each occurrence separately, we store only the recurrence rule and dynamically generate instances using the rrule library in Node.js.

Example: Fetching Events for a Specific Week

We will generate event occurrences for a one-week period from April 1, 2025 (Monday) to April 7, 2025 (Sunday) and use the between method to fetch events within this range, including the first and last occurrences in the given date range.

Code Implementation:

const { RRule } = require('rrule');

// Define recurrence rule for an event that occurs every Monday, Wednesday, and Friday
const rule = new RRule({
  freq: RRule.WEEKLY, // Recurring weekly
  dtstart: new Date('2025-04-01T00:00:00'), // Start date 
  until: new Date('2025-06-30T00:00:00'), // End date 
  byweekday: [RRule.MO, RRule.WE, RRule.FR], // Monday, Wednesday, Friday
});

// Define the start and end range for fetching events in a specific week
const startOfWeek = new Date('2025-04-01T00:00:00'); // Start of the week (April 1)
const endOfWeek = new Date('2025-04-07T00:00:00'); // End of the week (April 7)

// Fetch occurrences within the given date range (including edges)
const occurrences = rule.between(startOfWeek, endOfWeek, true);

console.log(occurrences);

This will return

[
  "Mon Apr 01 2025 10:00:00 GMT",
  "Wed Apr 03 2025 10:00:00 GMT",
  "Fri Apr 05 2025 10:00:00 GMT"
]

Step 3: Updating a Recurring Event (Splitting the Date Range)

Use Case:

  • The “Team Meeting” on April 10, 2025 (Wednesday) is moved to April 11, 2025 (Thursday).
  • Instead of modifying the entire recurrence, we split the date range and create a new record for April 11.

SQL Queries to Handle Update

Step 1: Update the Existing Event to Exclude April 10

UPDATE events
SET end_date = '2025-04-09'
WHERE metadata_id = 1 AND start_date = '2025-04-01';

Step 2: Insert the Updated Event for April 11

INSERT INTO events (metadata_id, start_date, end_date, start_time, end_time, is_recurring, recurring_days)
VALUES (1, '2025-04-11', '2025-04-11', '10:00', '11:00', FALSE, '["Thursday"]'::jsonb);

This adds a separate, one-time event for April 11, 2025.

Step 3: Insert a New Event for the Remaining Recurrence (From April 11 Onward)

INSERT INTO events (metadata_id, start_date, end_date, start_time, end_time, is_recurring, recurring_days)
VALUES (1, '2025-04-11', '2025-06-30', '10:00', '11:00', TRUE, '["Monday", "Wednesday", "Friday"]'::jsonb);

This restarts the recurring event from April 11 onwards, keeping the original pattern intact.

Step 4: Deleting a Specific Date from Recurring Events

Example:

  • The “Team Meeting” on May 15, 2025 (Wednesday) needs to be canceled without affecting other occurrences.

Steps to Handle Deletion

  1. Find the original event record
  2. Split the date range
  3. Remove the deleted date without inserting it as a new event

SQL Queries to Handle Deletion

Step 1: Update the Existing Event to Exclude May 15

UPDATE events
SET end_date = '2025-05-14'
WHERE metadata_id = 1 AND start_date = '2025-04-01';

Step 2: Insert the Remaining Recurrence After May 15

INSERT INTO events (metadata_id, start_date, end_date, start_time, end_time, is_recurring, recurring_days)
VALUES (1, '2025-05-16', '2025-06-30', '10:00', '11:00', TRUE, '["Monday", "Wednesday", "Friday"]'::jsonb);

Final Data After Deletion:

  1. April 1 - May 14, 2025
  2. May 16 - June 30, 2025

Summary

  1. Recurring Events Storage Strategy:
    • Store basic details in event_metadata.
    • Store recurrence details in events with a single record for the date range.
  2. Event Listing:
    • Using the rrule library we can dynamically generate occurrences instead of storing each separately.
  3. Updating Recurring Events:
    • Split the range to exclude the updated date and create a new event if needed.
  4. Deleting a Specific Date:
    • Split the range while excluding the deleted date without adding it as a new record.
4 Likes