Nicholai
a77f62f949
Adds complete CalDAV integration for syncing appointments between the web app and Nextcloud calendars with real-time availability checking and conflict resolution. Core Features: - Bidirectional sync: Web ↔ Nextcloud calendars - Real-time availability checking with instant user feedback - Conflict detection (Nextcloud is source of truth) - Pending request workflow with 'REQUEST:' prefix for unconfirmed appointments - Hard time blocking - any calendar event blocks booking slots - Graceful degradation when CalDAV unavailable New Dependencies: - tsdav@^2.0.4 - TypeScript CalDAV client - ical.js@^1.5.0 - iCalendar format parser/generator Database Changes: - New table: artist_calendars (stores calendar configuration per artist) - New table: calendar_sync_logs (tracks all sync operations) - Added caldav_uid and caldav_etag columns to appointments table - Migration: sql/migrations/20250109_add_caldav_support.sql New Services: - lib/caldav-client.ts - Core CalDAV operations and iCalendar conversion - lib/calendar-sync.ts - Bidirectional sync logic with error handling New API Endpoints: - GET /api/caldav/availability - Real-time availability checking - POST /api/caldav/sync - Manual sync trigger (admin only) - GET/POST/PUT/DELETE /api/admin/calendars - Calendar configuration CRUD Updated Components: - app/api/appointments/route.ts - Integrated CalDAV sync on CRUD operations - components/booking-form.tsx - Added real-time availability indicator - hooks/use-availability.ts - Custom hook for debounced availability checking Documentation: - docs/CALDAV-SETUP.md - Complete setup guide with troubleshooting - docs/CALDAV-IMPLEMENTATION-SUMMARY.md - Technical implementation overview Pending Tasks (for future PRs): - Admin dashboard UI for calendar management - Background sync worker (Cloudflare Workers cron) - Unit and integration tests Tested with local database migration and linting checks passed.
44 lines
1.7 KiB
SQL
44 lines
1.7 KiB
SQL
-- Migration: Add CalDAV support for Nextcloud calendar integration
|
|
-- Created: 2025-01-09
|
|
|
|
-- Create artist_calendars table to store calendar configuration for each artist
|
|
CREATE TABLE IF NOT EXISTS artist_calendars (
|
|
id TEXT PRIMARY KEY,
|
|
artist_id TEXT NOT NULL UNIQUE,
|
|
calendar_url TEXT NOT NULL,
|
|
calendar_id TEXT NOT NULL,
|
|
sync_token TEXT,
|
|
last_sync_at DATETIME,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (artist_id) REFERENCES artists(id) ON DELETE CASCADE
|
|
);
|
|
|
|
-- Add CalDAV fields to appointments table
|
|
ALTER TABLE appointments ADD COLUMN caldav_uid TEXT;
|
|
ALTER TABLE appointments ADD COLUMN caldav_etag TEXT;
|
|
|
|
-- Create index for efficient CalDAV UID lookups
|
|
CREATE INDEX IF NOT EXISTS idx_appointments_caldav_uid ON appointments(caldav_uid);
|
|
|
|
-- Create calendar_sync_logs table for monitoring sync operations
|
|
CREATE TABLE IF NOT EXISTS calendar_sync_logs (
|
|
id TEXT PRIMARY KEY,
|
|
artist_id TEXT,
|
|
sync_type TEXT NOT NULL CHECK (sync_type IN ('PUSH', 'PULL', 'FULL')),
|
|
status TEXT NOT NULL CHECK (status IN ('SUCCESS', 'FAILED', 'PARTIAL')),
|
|
error_message TEXT,
|
|
events_processed INTEGER DEFAULT 0,
|
|
events_created INTEGER DEFAULT 0,
|
|
events_updated INTEGER DEFAULT 0,
|
|
events_deleted INTEGER DEFAULT 0,
|
|
duration_ms INTEGER,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (artist_id) REFERENCES artists(id) ON DELETE CASCADE
|
|
);
|
|
|
|
-- Create index for sync log queries
|
|
CREATE INDEX IF NOT EXISTS idx_sync_logs_artist_created ON calendar_sync_logs(artist_id, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_sync_logs_status ON calendar_sync_logs(status, created_at DESC);
|
|
|