IHNYC Platform Docs

Calendar data model

Active2 min read

Table of Contents

Storage map

erDiagram
  subscribers ||--o{ calendar_tokens : owns
  calendars ||--o{ calendar_tokens : issues
  calendars ||--o{ verify_requests : verifies
  calendar_tokens ||--o{ calendar_access_log : used_by
  subscribers ||--o{ event_log : appears_in
  calendar_tokens ||--o{ event_log : appears_in
  email_templates ||--o{ email_template_history : versions
  email_footer ||--o{ email_footer_history : versions

The repository has an existing base schema plus checked-in migrations 0001 through 0010. They add renewal state, admin visibility, country and latency capture, system state, first-party events, email delivery records, editable templates, a shared email footer, editable supporting prose, and the plaintext-token cleanup.

Core subscription tables

subscribers

  • normalized, unique email plus optional name;
  • active or disabled status;
  • created and updated timestamps.

calendars

  • calendar id such as programs or internal;
  • R2 object key;
  • enable/disable flag used during verification and feed delivery.

verify_requests

  • one-time magic-link code hash;
  • expiry and consumption timestamps;
  • troubleshooting fields such as hashed IP and user agent;
  • Resend message id for matching delivery webhooks.

calendar_tokens

  • subscriber and calendar relationship;
  • SHA-256 token_hash only for current feed authentication;
  • expiry, revocation, and last-seen timestamps;
  • renewal warning, renewal count, and inferred calendar-client state;
  • last Resend message id for renewal-email delivery outcomes.

Plaintext token storage is retired

Older code used a nullable token_plain column to rebuild renewal links. Migration 0008_drop_token_plain.sql sets every legacy value to NULL, and current code neither reads nor writes it. Renewal email now uses a short-lived HMAC-signed credential containing a token-row id and expiry. That credential can renew a row; it cannot fetch the calendar feed.

Operational and analytics tables

TablePurpose
calendar_access_logEach feed request: status, token id when known, path, hashed IP, user agent, country, and latency
event_logFirst-party product/security events with internal subscriber/token ids, optional dedup key, and JSON context
email_eventsSigned Resend webhook outcomes such as delivered, bounced, and complained
email_templatesCurrent overrides for magic-link and renewal email copy; extras_json holds template-specific supporting prose
email_template_historySaved prior template versions, including supporting prose
email_footerOne shared reply notice, support line, copyright line, and disclaimer
email_footer_historyThe prior 20 shared-footer versions
system_stateCron timestamps and summaries

Mixpanel is not a current destination. event_log is the analytics source of truth; see Calendar Analytics.

R2

CAL_BUCKET stores the generated Programs feed, normally at programs.ics. The regeneration job writes only when bytes change, preserving the ETag and allowing calendar clients to receive 304 Not Modified.

Data owned elsewhere

Calendar roles and action grants do not live in SUBS_DB. RC Console evaluates them for every admin API request.

Sources

  • ihnyc-rc-cal-landing/migrations/0001_renewal_warnings.sql through 0010_email_template_extras.sql
  • ihnyc-rc-cal-landing/functions/_lib/events.ts
  • ihnyc-rc-cal-landing/functions/_lib/renew-link.ts
  • ihnyc-rc-cal-landing/functions/cal/programs.ics.ts
  • ihnyc-rc-cal-landing/wrangler.toml