rrule-plpgsql

v1.1.0

Published

3 months ago

Pure PL/pgSQL implementation of RFC 5545 RRULE for PostgreSQL - No C extensions, works everywhere

rrule_plpgsql

Pure PL/pgSQL implementation of iCalendar RRULE (RFC 5545) for PostgreSQL. No C extensions, no compilation, works everywhere.

Overview

This library provides complete RRULE recurrence calculation functionality using pure PL/pgSQL. Use this implementation for all environments (local, development, staging, production) to ensure consistency.

Key Features:

✅ No C extensions required - Pure PL/pgSQL, works on any PostgreSQL
✅ Full timezone support with DST handling - Wall-clock time preserved ("10 AM stays 10 AM" across DST transitions)
✅ Consistent everywhere - Same implementation across all environments
✅ Production-ready - Comprehensive test suite with 187 tests (including table operation integration tests)
✅ RFC 5545 & RFC 7529 compliant - Supports standard RRULE patterns plus SKIP/RSCALE
✅ 50-75x faster than Node.js - Excellent performance without compilation
✅ Works on managed services - AlloyDB, RDS, Azure Database for PostgreSQL

Why This Approach?

Query Engine Integration

Computation where your data lives delivers performance impossible with external processing:

Set-based operations: JOIN recurrence rules against events, bookings, or any table without data transfer
Native WHERE/aggregation: Filter by occurrence dates, COUNT occurrences, GROUP BY - all in SQL
Batch processing: Process 100+ schedules in a single query without round trips
Memory-efficient streaming: SETOF returns results incrementally, not all at once
50-75x faster than Node.js for single-schedule operations, infinitely faster for multi-schedule batch queries

See Example Usage for practical patterns including subscription billing, batch updates, and conflict detection.

Zero Compilation

Pure PL/pgSQL means:

✅ No C compiler or build tools required
✅ Install with a single SQL file - no configuration
✅ Consistent behavior across all environments (dev, staging, production)

Universal compatibility:

✅ Google AlloyDB - High-performance PostgreSQL with AI integration
✅ Google Cloud SQL - Fully managed PostgreSQL service
✅ Amazon Aurora PostgreSQL - Serverless PostgreSQL with automatic scaling
✅ Amazon RDS PostgreSQL - Managed PostgreSQL without custom extensions
✅ Azure Database for PostgreSQL - Fully supported managed service
✅ Self-hosted PostgreSQL - No special configuration required
✅ Docker PostgreSQL - Standard postgres image compatibility

Quick Start

Option 1: npm (TypeScript/Node.js)

npm install rrule-plpgsql

Then install in your database using your ORM/client. See Installation Guide for TypeScript/ORM integration.

Option 2: Direct SQL Installation

# Install via psql
psql -d your_database -f src/install.sql

# Or use curl for one-line install
curl -sL https://raw.githubusercontent.com/sirrodgepodge/rrule_plpgsql/main/src/install.sql | psql -d your_database

Next Steps: See Example Usage for practical patterns.

Documentation

📖 Core Documentation

Installation Guide - TypeScript ORMs (node-postgres, TypeORM, Prisma, Knex, Sequelize, Drizzle)
Example Usage - Real-world patterns: subscription billing, batch operations, conflict detection
API Reference - Complete function reference with parameters and examples
RFC Spec Compliance - Supported RFC 5545/7529 features and limitations
Validation Rules - RRULE constraint validation and error messages
Development Guide - Testing, architecture, and contribution guidelines

🔒 Security & Advanced Features

Security Guide - Best practices and vulnerability reporting
Performance Optimization - Indexes, query patterns, and scaling recommendations
Sub-Day Operations - HOURLY/MINUTELY/SECONDLY guide (disabled by default)
Migration Guide - Upgrading with dependent database objects

Key Features

Supported Frequencies

✅ Production-Ready (Always Enabled):

FREQ=DAILY - Daily recurrence with date/time filters
FREQ=WEEKLY - Weekly recurrence with day-of-week selection
FREQ=MONTHLY - Monthly recurrence with day/week-of-month selection
FREQ=YEARLY - Yearly recurrence with month/day/week selection

⚠️ Advanced (Optional, Disabled by Default):

FREQ=HOURLY - Hourly recurrence (8,760/year max)
FREQ=MINUTELY - Minutely recurrence (525,600/year max)
FREQ=SECONDLY - Secondly recurrence (31M/year max)

See Sub-Day Operations Guide for enabling these safely.

Supported Modifiers

COUNT - Limit number of occurrences
UNTIL - End date for recurrence
INTERVAL - Frequency multiplier (every N days/weeks/months)
BYDAY - Filter by day of week (MO, TU, WE, TH, FR, SA, SU)
BYDAY with ordinals - Positioned weekdays (2MO = 2nd Monday, -1FR = last Friday)
BYMONTHDAY - Filter by day of month (1-31, -1 = last day)
BYMONTH - Filter by month (1-12)
BYYEARDAY - Filter by day of year (1-366, negative supported)
BYWEEKNO - Filter by ISO week number (1-53, YEARLY only)
BYSETPOS - Select specific positions from generated set
WKST - Week start day (SU, MO, TU, WE, TH, FR, SA)
SKIP - Invalid date handling (OMIT, BACKWARD, FORWARD)
TZID - Timezone specification with automatic DST handling
RSCALE - Calendar system (GREGORIAN supported)

See RFC Spec Compliance for complete feature support matrix and examples.

Timezone Support

Full RFC 5545 timezone support with automatic DST handling:

-- Meeting stays at 10 AM wall-clock time across DST boundary
SELECT * FROM rrule.all(
    'FREQ=DAILY;COUNT=3;TZID=America/New_York',
    '2025-03-08 10:00:00'::TIMESTAMP
);
-- Returns: March 8 (EST), March 9 (EDT), March 10 (EDT)
-- All at 10:00 AM wall-clock time

See API Reference for TIMESTAMPTZ API details.

Installation Options

Standard Installation (Recommended)

psql -d your_database -f src/install.sql

Includes:

DAILY, WEEKLY, MONTHLY, YEARLY frequencies
All standard modifiers and filters
Timezone support with DST handling
Secure by default (no DoS vectors)

With Sub-Day Frequencies (Advanced)

psql -d your_database -f src/install_with_subday.sql

Includes standard PLUS:

HOURLY, MINUTELY, SECONDLY frequencies
⚠️ Security warnings during installation
Requires application-level validation

See Sub-Day Operations Guide before using this installation.

Troubleshooting

Common Issues

Error: "Invalid RRULE: FREQ parameter is required"

Every RRULE must start with FREQ
Fix: Add FREQ=DAILY (or WEEKLY/MONTHLY/YEARLY)

Error: "COUNT and UNTIL are mutually exclusive"

Cannot use both COUNT and UNTIL in same RRULE
Fix: Use either COUNT=10 OR UNTIL=20251231T235959

Error: "BYWEEKNO can only be used with FREQ=YEARLY"

BYWEEKNO requires YEARLY frequency
Fix: Change to FREQ=YEARLY;BYWEEKNO=10

See Validation Rules for complete error reference.

API Overview

Core Functions

All functions support both TIMESTAMP and TIMESTAMPTZ with automatic timezone handling:

-- Generate occurrences
rrule.all(rrule, dtstart) → SETOF TIMESTAMP
rrule.between(rrule, dtstart, start, end) → SETOF TIMESTAMP

-- Query occurrences
rrule.after(rrule, dtstart, after_date) → TIMESTAMP
rrule.before(rrule, dtstart, before_date) → TIMESTAMP
rrule.next(rrule, dtstart) → TIMESTAMP
rrule.most_recent(rrule, dtstart) → TIMESTAMP

-- Utilities
rrule.count(rrule, dtstart) → INTEGER
rrule.overlaps(dtstart, dtend, rrule, mindate, maxdate) → BOOLEAN

Quick Example

-- Every Monday for 4 weeks
SELECT * FROM rrule.all(
    'FREQ=WEEKLY;BYDAY=MO;COUNT=4',
    '2025-01-06 10:00:00'::TIMESTAMP
);

-- With timezone support
SELECT * FROM rrule.all(
    'FREQ=DAILY;COUNT=5;TZID=America/New_York',
    '2025-03-08 10:00:00'::TIMESTAMP  -- DST handled automatically
);

See API Reference for complete function signatures, timezone handling details, and advanced examples.

Contributing

Contributions welcome! Please:

Run all tests (all 10 test suites must pass)
Add test coverage for new features
Update documentation
Follow RFC 5545/7529 specifications
Submit pull request

See Development Guide for contribution guidelines and test suite details.

Performance

Single schedule: 50-75x faster than Node.js rrule.js Multi-schedule queries: Eliminates application-database round trips entirely

Scalability advantages:

Set-based batch operations: Query 100+ schedules in parallel without loops
Streaming results: SETOF returns incrementally, not all at once (constant memory)
Query planner optimization: PostgreSQL optimizes JOINs and filters with occurrence expansion
Early-exit optimizations: Stops computation when COUNT/UNTIL limits reached

See Performance Guide for optimization strategies and Development Guide for benchmarking details.

Requirements

PostgreSQL 12 or higher
No C extensions required
No external dependencies
Works on all PostgreSQL-compatible platforms

See Development Guide for development setup instructions.

References

RFC 5545 (iCalendar) - RRULE specification
RFC 7529 (Non-Gregorian Recurrence Rules) - SKIP and RSCALE parameters

Support

Issues: https://github.com/sirrodgepodge/rrule_plpgsql/issues
Security: See SECURITY.md
Documentation: See API Reference
License: MIT (see LICENSE)