data model - Andre-asn/Quack-Campus-Event-Coordinator GitHub Wiki

User

  • id primary key
  • provider and provider_subject_id, unique together. The real identity key
  • email, unique, case-insensitive. Always a stevens.edu address
  • email_domain, denormalized for the allowlist check and for future multi-campus support
  • display_name, seeded from the provider profile, editable by the user
  • avatar_url, the profile picture supplied by Google, used for the host bubbles on clustered pins
  • is_seed boolean. Seeded demo users have no provider identity and can never sign in
  • created_at, last_login_at

Event

  • id primary key
  • host_id foreign key to User
  • title and description, the one-line detail
  • category, enum of (study, food, sports, games, movie, music, other)
  • location, a PostGIS geography(Point, 4326) column holding the pin. The API accepts and returns latitude and longitude as separate numbers; conversion happens at the persistence boundary
  • location_label, typed by the host, for example "Main Dining Hall". Never geocoded (see Frontend Design)
  • starts_at, stored in UTC
  • visibility, enum of public or link_only
  • share_token, generated for link-only events, unguessable, unique
  • status, enum of active or cancelled
  • created_at, updated_at

EventAttendance

  • event_id, user_id, composite unique constraint
  • joined_at. Leaving deletes the row. No in-scope story needs join and leave history

EventView

  • event_id, viewer_id, viewed_at
  • Deduplicated per viewer per event per day, so the host stat in F9 means something

Indexes and query shape

  • GiST index on location, which is what PostGIS uses for both bounding box and radius queries
  • Index on starts_at, since every map query filters by time window as well as bounds
  • Index on host_id for the my events screen
  • Unique index on share_token, and on (provider, provider_subject_id)

Pins are stored as PostGIS geography(Point, 4326). Viewport queries become a bounding box intersection on a GiST index, and a future distance filter is one ST_DWithin call in metres. geography over geometry avoids managing projections.

Setup is CREATE EXTENSION postgis, hibernate-spatial, JTS types on the entity, and a PostGIS Testcontainers image: about a day now, versus a migration and test rework if added in week 11.

Data retention

Cancelling sets status to cancelled instead of deleting, so attendees see what happened and the day-back view stays intact.

Category Statistics

Category popularity is a query, not stored data: SELECT category, COUNT(*) FROM events WHERE starts_at >= ? AND starts_at < ? GROUP BY category. At 2,000 events this returns instantly, so there is no counter table and no rollup to keep in sync or backfill. Since events are never hard deleted, the full history stays available to re-aggregate. Add a rollup only if the query ever gets slow, which it will not at campus scale.

category is an enum column rather than its own table. A lookup table would only pay off if categories needed adding without a deploy, or carried metadata such as an icon or colour. Converting later is a small migration, and the stored values survive it either way.

The same column is what the P2 category filter needs, so this is a prerequisite for that stretch story rather than a detour.