IHNYC RC Calendar Data Model

D1 schema (schema.sql)

subscribers

• email unique, normalized to lowercase
• status is active or disabled
• created_at, updated_at tracked as Unix seconds (string)

calendars

• id: programs or internal
• ics_key: R2 key like programs.ics
• is_enabled gates access in /api/subs/verify and /cal/programs.ics

verify_requests

• code_hash stores the hashed magic-link code
• expires_at and consumed_at enforce TTL and one-time use
• ip_hash and ua stored for troubleshooting

calendar_tokens

• token_hash stores only a SHA-256 hash of the token
• expires_at and revoked_at control access
• last_seen_at updates when a token fetches the ICS
• Partial unique index enforces one active token per (subscriber, calendar)

calendar_access_log

• Records status, path, ua, and ip_hash per access
• token_id is nullable for invalid tokens

R2 storage

• CAL_BUCKET stores programs.ics at key programs.ics

Related

• ihnyc-rc-cal-landing
• api