Overview
Pine Cove Bridge API — Data Model
Bridge API is the central aggregation backend powering every Pine Cove digital touchpoint: the parent-facing Camp Life app, the staff Sidekick Portal and Sidekick Mobile apps, and asynchronous Lambda processing. A single MySQL database (TypeORM, master/slave replication) backs the system. Explore each domain in the sidebar, or search for a specific entity.
81
Entities
14
Domains
~4
Join tables
7
External systems
How the domains relate at a glance
flowchart LR
classDef core fill:#3f6a47,stroke:#253f2e,color:#fff,font-weight:600
classDef content fill:#7ba883,stroke:#3f6a47,color:#1a2e1f
classDef ops fill:#c98a3a,stroke:#6e531f,color:#fff
classDef meta fill:#a9c9b0,stroke:#558a5e,color:#1a2e1f
users[1 · Core User]:::core
admin[2 · Admin System]:::ops
camp[3 · Camp]:::core
reg[4 · Registration]:::core
staff[5 · Staff]:::core
cabin[6 · Cabin & Housing]:::core
media[7 · Media & Content]:::content
gallery[8 · Photo Galleries]:::content
articles[9 · Articles & Publishing]:::content
comm[10 · Communication]:::content
surveys[11 · Surveys]:::ops
social[12 · Social]:::content
report[13 · Reporting]:::ops
misc[14 · Tools & System]:::meta
users --> reg
users --> staff
users --> comm
users --> social
camp --> reg
camp --> cabin
camp --> surveys
camp --> report
reg --> cabin
staff --> cabin
staff --> articles
reg --> media
camp --> media
media --> gallery
media --> articles
articles --> comm
admin --> camp
admin --> report
surveys --> report
Explore by domain
External system IDs carried by entities
- iCampPro — legacy registration source of truth (
iCampPro_id) - Rockpile — previous API (
rockpile_id) - CampLife — mobile app topic/post IDs
- Salesforce — contact, account, and generic IDs
- WordPress — article post + revision IDs
- Tableau — dashboard usernames/links
- PaperSurvey.io — survey + response IDs
Base entity inheritance
All entities inherit audit columns (createdBy, createdAt,
createdByIP, modifiedBy, modifiedAt,
modifiedByIP) from BaseEntity.
- ICampProBaseEntity — adds
iCampPro_id,iCampPro_createdAt,iCampPro_updatedAt - IRockpileBaseEntity (interface) — adds
rockpile_id
Domain 1
Core User
The foundation of the system. User represents campers, parents, and staff alike.
Household groups family members together, with one user as the primary contact.
Key pattern — A user is not role-specific at the model level; roles emerge via related tables (Staff, Registration, AdminUser). Households act as the family billing/communication unit.
Entity diagram
erDiagram
User {
uuid id PK
int rockpile_id UK
varchar iCampPro_id UK
varchar login UK
int camplife_topicId UK
varchar salesforce_contactId
boolean isChild
varchar firstName
varchar lastName
varchar email
boolean activated
varchar langKey
varchar iCampProProfileUrl
varchar ICPProfileName
timestamp lastActivityAt
}
UserExtra {
uuid id PK
varchar userId FK
varchar gender
date birthdate
varchar cellphone
int grade
boolean isHeadshotUploaded
boolean isIcpProfileValid
enum headshotStatus
varchar city
varchar state
}
Household {
uuid id PK
int rockpile_id UK
varchar iCampPro_id UK
varchar name
varchar salesforce_accountId
int campLife_topicId
varchar contactId FK
}
HouseholdMember {
uuid id PK
int rockpile_id UK
varchar iCampPro_id UK
enum type
varchar userId FK
varchar householdId FK
}
User ||--o| UserExtra : "has profile"
User ||--o| Household : "is contact of"
User ||--o{ HouseholdMember : "belongs to"
Household ||--o{ HouseholdMember : "has members"
Entities
| Entity | Purpose | Key relationships |
|---|---|---|
| User | Primary account (campers + staff + parents) | Has UserExtra, Staff, Registrations, HouseholdMembers |
| UserExtra | Extended profile (gender, birthdate, headshot status) | 1:1 with User |
| Household | Family grouping unit; billing/contact anchor | Has primary contact User, many HouseholdMembers |
| HouseholdMember | Links Users to Households with a role type | Many-to-one User and Household |
Domain 2
Admin System
A separate authentication and authorization system for internal staff/administrators. Uses role-based access control (RBAC) with fine-grained abilities, plus camp-level position assignments for per-camp privileges.
Key pattern — AdminUser is distinct from User. Abilities (54+ distinct) are assigned to roles, roles to admins.
AdminUserPosition adds camp-scoping on top of the global role.
Entity diagram
erDiagram
AdminUser {
uuid id PK
varchar firstName
varchar lastName
varchar email
varchar tableauUsername
boolean activated
varchar campAssociation
enum userType
boolean receiveManagerEmail
varchar roleId FK
}
AdminRole {
uuid id PK
varchar roleName UK
boolean activated
int displayOrder
int rank
}
AdminAbility {
uuid id PK
varchar name
varchar groupName
boolean activated
json abilityLevel
varchar permissionSection
}
AdminRoleAbility {
uuid id PK
varchar adminAbilityId FK
varchar adminRoleId FK
varchar accessLevel
}
AdminUserPosition {
uuid id PK
varchar adminUserId FK
varchar campId FK
enum role
}
AdminRoleCard {
uuid id PK
varchar adminRoleId FK
varchar cardName
int displayOrder
}
AdminUserRecentTabs {
uuid id PK
varchar adminUserId FK
varchar label
varchar title
varchar tableauId
}
AdminAccessedHouseholds {
uuid id PK
varchar adminUserId FK
varchar householdId FK
timestamp lastActivity
}
AdminUser }o--|| AdminRole : "has role"
AdminUser ||--o{ AdminUserRecentTabs : "has tabs"
AdminUser ||--o{ AdminUserPosition : "has positions"
AdminRole ||--o{ AdminRoleAbility : "has abilities"
AdminAbility ||--o{ AdminRoleAbility : "granted to"
AdminRole ||--o{ AdminRoleCard : "dashboard cards"
AdminUser ||--o{ AdminAccessedHouseholds : "accessed"
Entities
| Entity | Purpose | Key relationships |
|---|---|---|
| AdminUser | Admin portal login (separate from User) | Has AdminRole, positions, recent tabs |
| AdminRole | Role definitions with ranking | Has abilities + dashboard cards |
| AdminAbility | Granular permission definitions | Linked to roles via AdminRoleAbility |
| AdminRoleAbility | Junction: role ↔ ability with access level | Links both sides |
| AdminUserPosition | Per-camp role assignment for an admin | Links AdminUser to Camp |
| AdminRoleCard | Dashboard card visibility per role | Many-to-one AdminRole |
| AdminUserRecentTabs | Recently viewed Tableau tabs | Many-to-one AdminUser |
| AdminAccessedHouseholds | Audit trail of household views | Links AdminUser to Household |
Domain 3
Camp
The physical camps, their sessions, and geographic locations. Camp → CampSession is the core hierarchy driving nearly every downstream domain (registration, cabins, media, surveys).
Key pattern — A CampSession carries dates, grade/age ranges, status, and optional off-site venue.
CampSessionLocation represents churches or remote venues distinct from the main camp Location.
Entity diagram
erDiagram
Camp {
uuid id PK
int rockpile_id UK
varchar iCampPro_id UK
varchar name
enum programCategory
enum type
varchar regionName
json mapData
json cmsData
varchar directorId FK
varchar locationId FK
}
CampSession {
uuid id PK
int rockpile_id UK
varchar iCampPro_id UK
varchar name
varchar code
enum type
int year
int eventYear
date startDate
date endDate
enum status
varchar campId FK
varchar locationId FK
int minGrade
int maxGrade
int minAge
int maxAge
boolean isRegistrationEnabled
varchar familyCampScheduleVersionId FK
}
CampSessionLocation {
uuid id PK
int rockpile_id UK
varchar iCampPro_id UK
varchar name
varchar shortName
varchar primaryEmail
boolean isLunchProvided
varchar customUrl
int gmtOffset
varchar locationId FK
}
Location {
uuid id PK
int rockpile_id UK
varchar iCampPro_id UK
varchar name
varchar address
varchar city
varchar state
varchar country
varchar timeZone
varchar postalCode
varchar latitude
varchar longitude
}
Camp }o--|| Location : "located at"
Camp }o--|| User : "directed by"
CampSession }o--|| Camp : "belongs to"
CampSession }o--o| CampSessionLocation : "held at"
CampSessionLocation }o--o| Location : "located at"
Entities
| Entity | Purpose | Key relationships |
|---|---|---|
| Camp | Camp site (e.g., Pine Cove, Towers) | Has Location, director, sessions |
| CampSession | A specific session/week at a camp | Belongs to Camp, has Registrations |
| CampSessionLocation | Church/off-site session venue | Has a Location |
| Location | Geographic address with lat/long | Used by Camp, CampSessionLocation |
Domain 4
Registration & Attendance
Tracks camper sign-ups and session attendance. Registration is the central bridge connecting Users to CampSessions, carrying the full registration lifecycle and logistical details.
Key pattern — Status enum drives the full lifecycle: registered → cancelled → waitlisted and back.
Attendee is a lighter profile used at check-in. Staff-side applications live in the Staff domain.
Entity diagram
erDiagram
Registration {
uuid id PK
int rockpile_id
varchar iCampPro_id UK
int iCampProEnrollmentId
int iCampProAttendeeId
int grade
varchar gender
enum status
timestamp registeredAt
timestamp canceledAt
varchar cancelReason
boolean isAdult
varchar pickupCode
varchar sessionId FK
varchar userId FK
varchar householdId FK
varchar cabinCamperId FK
varchar familyAccommodationId FK
}
Attendee {
uuid id PK
int rockpile_id UK
varchar iCampPro_id UK
varchar statusCode
int age
varchar gender
varchar grade
boolean isGuest
boolean formFlag
varchar userId FK
varchar counselorId FK
}
Registration }o--|| CampSession : "for session"
Registration }o--|| User : "registered user"
Registration }o--|| Household : "from household"
Attendee }o--|| User : "is user"
Attendee }o--o| User : "has counselor"
Entities
| Entity | Purpose | Key relationships |
|---|---|---|
| Registration | A user's registration for a session | Links User, CampSession, Household |
| Attendee | Attendee profile with counselor assignment | Links User (attendee) + counselor |
Domain 5
Staff
Staff-specific data beyond the base User account — bios, hobbies, school background, per-session employment records, and the applicant pipeline for prospective staff.
Key pattern — Staff and User are a 1:1 pair. Employment is per CampSession — a single staffer may be
employed across multiple sessions. PositionGroup standardizes position names into three groups: Leadership, Counselor, Program.
Applicant tracks prospective staff through hiring; once hired, the same User gets a Staff record.
Entity diagram
erDiagram
Staff {
uuid id PK
varchar type
varchar campName
varchar campNameShort
varchar role
varchar userId FK
varchar bioId FK
varchar school
boolean hasEmployment
varchar major
text hobbies
text funFact
text lookingForwardTo
}
Employment {
uuid id PK
varchar staffId FK
varchar positionName
varchar sessionId FK
varchar staffingSeasonName
varchar staffRosterName
enum employmentStatus
}
PositionGroup {
uuid id PK
varchar positionName UK
enum positionGroupName
}
Applicant {
uuid id PK
int rockpile_id UK
varchar iCampPro_id UK
varchar type
varchar code
varchar status
varchar school
varchar statusCode
timestamp submittedAt
int counselorBioArticleId
boolean stafferImpact
varchar offerStatus
varchar userId FK
}
ApplicantPhoto {
uuid id PK
int rockpile_id UK
enum type
varchar imageId FK
}
User ||--o| Staff : "is staff"
Staff ||--o{ Employment : "has employments"
Employment }o--|| CampSession : "for session"
Applicant }o--|| User : "is user"
ApplicantPhoto ||--o| Image : "has image"
Entities
| Entity | Purpose | Key relationships |
|---|---|---|
| Staff | Staff profile (bio, school, hobbies) | 1:1 with User, has CampArticle bio |
| Employment | Employment record per session | Links Staff to CampSession |
| PositionGroup | Maps position names to groups (Leadership/Counselor/Program) | Lookup (table name: EmploymentPositionGroup) |
| Applicant | Prospective staff — application tracking through hiring | Links to User |
| ApplicantPhoto | Applicant headshot/photos | Links to Image |
Domain 6
Cabin & Housing
Physical housing assignments for campers and families. Each session instantiates its cabin layout as CabinSessionGroups with assigned counselors; family camps use a parallel accommodation system.
Key pattern — CabinSessionGroup is the per-session instance of a Cabin (with a counselor + optional senior counselor).
CabinCamper places individual campers in a group. Family camp accommodations are tracked via FamilyAccommodation +
FamilySessionGroup. (Daily schedule content for the Camp Life app lives in the Articles & Publishing domain.)
Entity diagram
erDiagram
Cabin {
uuid id PK
int rockpile_id UK
varchar iCampPro_id UK
varchar name UK
varchar code
varchar cluster
int idealCapacity
int maxCapacity
varchar gender
int displayOrder
}
CabinSessionGroup {
uuid id PK
int rockpile_id UK
varchar iCampPro_id UK
varchar groupCode
varchar cabinNickname
varchar sessionId FK
varchar cabinId FK
varchar counselorId FK
varchar seniorCounselorId FK
enum cabinPhotoStatus
}
CabinCamper {
uuid id PK
int rockpile_id UK
varchar camperId FK
varchar buddyCamperId FK
varchar cabinGroupId FK
}
FamilyAccommodation {
uuid id PK
int spaceId UK
varchar spaceName
}
FamilySessionGroup {
uuid id PK
varchar sessionId FK
varchar householdId FK
enum familyPhotoStatus
}
CabinSessionGroup }o--|| CampSession : "for session"
CabinSessionGroup }o--|| Cabin : "uses cabin"
CabinSessionGroup }o--o| Staff : "counselor"
CabinSessionGroup ||--o{ CabinCamper : "has campers"
CabinCamper }o--|| User : "is camper"
FamilySessionGroup }o--|| CampSession : "for session"
FamilySessionGroup }o--|| Household : "for household"
Entities
| Entity | Purpose | Key relationships |
|---|---|---|
| Cabin | Physical cabin definition | Has many CabinSessionGroups |
| CabinSessionGroup | Cabin instance for a session with counselor | Links Cabin, CampSession, Staff |
| CabinCamper | Camper assignment to cabin group | Links User to CabinSessionGroup |
| FamilyAccommodation | Family-camp housing options | Referenced by Registration |
| FamilySessionGroup | Family group in a session | Links Household to CampSession |
Domain 7
Media & Content
Photos, videos, and AI-powered tagging. Image/Video are raw assets; TaggedImage/TaggedVideo overlay facial recognition results and confidence scores; EntityTag provides polymorphic tagging across types.
Key pattern — AWS Rekognition produces faceId + bounding box + confidence, stored on TaggedImage. EntityTag
uses a polymorphic
targetType + targetId to tag photos/videos with Users, Cabins, or other entities.
Entity diagram
erDiagram
Image {
uuid id PK
varchar rockpile_id UK
varchar source
varchar format
varchar sourceType
timestamp timestamp
varchar title
varchar caption
varchar md5Hash
varchar imageFrom
numeric taggingThreshold
varchar campName
}
Video {
uuid id PK
int rockpile_id UK
varchar title
enum status
varchar source
varchar folderPath
varchar fileName
varchar baseUrl
varchar streamBaseUrl
varchar youTubeUrl
bigint contentLength
varchar typeId FK
varchar scheduleId FK
}
TaggedImage {
uuid id PK
int rockpile_id UK
enum status
varchar typeId FK
varchar imageId FK
varchar sessionId FK
varchar faceId
float confidenceScore
varchar faceBoundingBox
float faceSize
float blurValue
float brightness
float sharpness
}
TaggedVideo {
uuid id PK
int rockpile_id UK
varchar typeId FK
varchar videoId FK
varchar sessionId FK
}
MediaType {
uuid id PK
varchar rockpile_id UK
varchar code UK
varchar name
varchar description
boolean isActive
varchar defaultTransform
}
ContentEntityTag {
uuid id PK
int rockpile_id UK
varchar tagId FK
varchar imageId FK
varchar videoId FK
}
EntityTag {
uuid id PK
int rockpile_targetId
enum targetType
varchar targetId
}
TaggedImage }o--|| Image : "references image"
TaggedImage }o--|| MediaType : "of type"
TaggedImage }o--|| CampSession : "from session"
TaggedImage ||--o{ ContentEntityTag : "has tags"
TaggedVideo }o--|| Video : "references video"
TaggedVideo }o--|| MediaType : "of type"
TaggedVideo ||--o{ ContentEntityTag : "has tags"
ContentEntityTag }o--|| EntityTag : "references tag"
Video }o--|| MediaType : "of type"
Entities
| Entity | Purpose | Key relationships |
|---|---|---|
| Image | Core image asset + metadata | Referenced by many entities |
| Video | Video asset with streaming URLs | Has MediaType, PostSchedule |
| TaggedImage | Image with face detection/tagging results | Links Image, MediaType, CampSession |
| TaggedVideo | Video with entity tags | Links Video, MediaType, CampSession |
| MediaType | Content type definitions | Referenced by media entities |
| ContentEntityTag | Junction: content → entity tag | Links TaggedImage/Video to EntityTag |
| EntityTag | Polymorphic tag (person, cabin, etc.) | targetType + targetId |
Domain 8
Photo Galleries
Gallery management and publishing — albums, session photos, EXIF data, and per-camp tagging statistics. Gallery albums are scheduled for audience-targeted delivery.
Entity diagram
erDiagram
PhotoGallery {
uuid id PK
int rockpile_id UK
enum status
varchar title
varchar folderPath
varchar baseUrl
int submittedCount
varchar typeId FK
int galleryTotal
}
GalleryAlbum {
uuid id PK
varchar title
int photosCount
date scheduledDate
time scheduledTime
enum audience
enum status
enum day
text folderPath
int eventYear
varchar campName
varchar week
timestamp buildRunAt
}
GalleryAlbumPhotos {
uuid id PK
varchar galleryAlbumId FK
varchar imageId FK
boolean isCoverPhoto
}
CampSessionPhoto {
uuid id PK
int rockpile_id UK
varchar campSessionId FK
varchar photoId FK
}
CampSessionPhotoGallery {
uuid id PK
int rockpile_id UK
varchar campSessionId FK
varchar photoGalleryId FK
}
CampSessionVideo {
uuid id PK
int rockpile_id UK
varchar campSessionId FK
varchar videoId FK
}
ImageExifData {
uuid id PK
timestamp createdAt
varchar imagePath
varchar faceDetails
}
ImageContent {
uuid id PK
int rockpile_id UK
varchar iCampPro_id UK
varchar userId FK
varchar sessionId FK
}
TaggedImageStats {
uuid id PK
varchar campId FK
int eventYear
int campWeek
int totalCampers
int totalTags
float avgTagsPerCamper
int percentChangeTags
int percentChangeTagsPerCamper
}
PhotoGallery }o--|| MediaType : "of type"
GalleryAlbum ||--o{ GalleryAlbumPhotos : "contains"
GalleryAlbumPhotos }o--|| Image : "is image"
CampSessionPhoto }o--|| CampSession : "for session"
CampSessionPhoto }o--|| Image : "is photo"
CampSessionPhotoGallery }o--|| CampSession : "for session"
CampSessionPhotoGallery }o--|| PhotoGallery : "is gallery"
CampSessionVideo }o--|| CampSession : "for session"
CampSessionVideo }o--|| Video : "is video"
ImageContent }o--|| User : "tagged user"
ImageContent }o--|| CampSession : "from session"
TaggedImageStats }o--|| Camp : "for camp"
Entities
| Entity | Purpose | Key relationships |
|---|---|---|
| PhotoGallery | Gallery collection | Has MediaType |
| GalleryAlbum | Album with scheduling | Has GalleryAlbumPhotos |
| GalleryAlbumPhotos | Photos in an album | Links GalleryAlbum to Image |
| CampSessionPhoto | Session-specific photos | Links CampSession to Image |
| CampSessionPhotoGallery | Session gallery assignments | Links CampSession to PhotoGallery |
| CampSessionVideo | Session-specific videos | Links CampSession to Video |
| ImageExifData | EXIF metadata from photos | Standalone |
| ImageContent | Links users to images per session | Links User, CampSession |
| TaggedImageStats | Per-camp weekly tagging statistics | Links to Camp |
Domain 9
Articles & Content Publishing
Blog posts, camp updates, the publishing pipeline that delivers content into the Camp Life app, and the MyWeek schedule builder. Content flows: creation → scheduling (PostSchedule / MediaConfig) → publishing (ContentPost) → delivery to Camp Life.
Key pattern — ContentType is the master taxonomy referenced by many entities. PostSchedule batches content
for timed publishing. CampCQ (Camp Content Quotes) pairs images with AI-generated words for in-app engagement.
The MyWeek schedule builder — FamilyCampSchedule, its versions, and MyWeekBlock — is authored in Sidekick Portal and shows
campers what's happening each day of their week; CampSession references a specific version.
Entity diagram
erDiagram
CampArticle {
uuid id PK
int rockpile_id UK
varchar title
varchar summary
enum status
varchar postUrl
varchar feedImageUrl
date targetDate
varchar access
boolean isSharingEnabled
int wp_postId
int wp_postRevision
varchar priorRevisionId FK
varchar typeId FK
boolean isPushNotifyEnabled
}
CampArticleType {
uuid id PK
int rockpile_id UK
varchar wp_type UK
varchar contentTypeId FK
varchar name UK
varchar description
boolean isActive
}
ContentType {
uuid id PK
int rockpile_id UK
varchar code UK
varchar name
varchar description
varchar category
boolean isActive
}
ContentPost {
uuid id PK
int rockpile_id UK
varchar type
varchar appContentType
varchar sourceObject
varchar sourceId
varchar sharedFromCampSessionId FK
timestamp postedAt
timestamp publishAt
enum status
boolean isHidden
int campLife_postId
varchar scheduleId FK
varchar sourceContentTypeId FK
}
PostSchedule {
uuid id PK
int rockpile_id UK
varchar description
varchar postType
varchar sourceObject
varchar targetTopicType
timestamp startAt
timestamp endAt
timestamp publishAt
enum status
int totalCount
int processedCount
varchar contentTypeId FK
}
MediaConfig {
uuid id PK
int rockpile_id UK
varchar iCampPro_id UK
varchar day
int hour
int minutes
varchar appContentType
varchar caption1Template
varchar notificationTitle
varchar contentTypeId FK
}
CampUpdate {
uuid id PK
varchar contentTypeId FK
varchar content
enum status
boolean isPushNotifyEnabled
enum scheduleRepeat
time scheduleTime
int scheduleDay
json scheduleWeek
}
CampCQ {
uuid id PK
int rockpile_id UK
varchar text
varchar word1
varchar word2
varchar word3
varchar status
boolean isRegenerated
varchar fullImageId FK
varchar feedImageId FK
varchar userId FK
varchar campSessionId FK
}
FamilyCampSchedule {
uuid id PK
varchar campId FK
int eventYear
}
FamilyCampScheduleVersion {
uuid id PK
varchar familyCampScheduleId FK
int versionNumber
varchar versionName
json content
}
MyWeekBlock {
uuid id PK
varchar campType
json blocks
}
CampArticle }o--|| CampArticleType : "of type"
CampArticle }o--o| CampArticle : "prior revision"
CampArticleType }o--|| ContentType : "categorized as"
ContentPost }o--o| PostSchedule : "scheduled by"
ContentPost }o--|| ContentType : "of type"
PostSchedule }o--|| ContentType : "for type"
Video }o--o| PostSchedule : "scheduled by"
MediaConfig }o--|| ContentType : "for type"
CampUpdate }o--|| ContentType : "of type"
Staff ||--o| CampArticle : "has bio"
CampCQ }o--|| User : "for user"
CampCQ }o--|| CampSession : "in session"
FamilyCampSchedule }o--|| Camp : "for camp"
FamilyCampScheduleVersion }o--|| FamilyCampSchedule : "version of"
CampSession }o--o| FamilyCampScheduleVersion : "uses schedule"
Entities
| Entity | Purpose | Key relationships |
|---|---|---|
| CampArticle | Blog/article content (WordPress-backed) | Has type, linked to sessions |
| CampArticleType | Article categories | Links to ContentType |
| ContentType | Master content categorization | Referenced by many entities |
| ContentPost | Published content pushed to Camp Life | Links CampSession, PostSchedule, ContentType |
| PostSchedule | Batch publishing schedule | Links to ContentType |
| MediaConfig | Publishing schedule config per camp | Links to ContentType |
| CampUpdate | Recurring update posts | Links to ContentType, Camps |
| CampCQ | Camp content quotes with AI-generated words | Links Image, User, CampSession |
| FamilyCampSchedule | Per-camp/year config for the MyWeek schedule builder | Links to Camp |
| FamilyCampScheduleVersion | Versioned JSON schedule content surfaced in Camp Life | Links to FamilyCampSchedule; referenced by CampSession |
| MyWeekBlock | Block definitions (by camp type) used in the MyWeek builder | Standalone (table: MyWeekBlockManagement) |
Domain 10
Communication
Camp mail (physical scan-to-digital) and notifications. Physical camper letters are scanned in batches, processed with blank-letter AI detection, and delivered digitally to parents. Outbound messages to campers are scheduled for delivery.
Entity diagram
erDiagram
CampMailFromCamper {
uuid id PK
int rockpile_id UK
boolean isRead
enum status
varchar batchId FK
varchar imageId FK
varchar scanFileName
varchar currentFilePath
varchar senderId FK
varchar recipientId FK
varchar sessionId FK
float blankLetterConfidenceScore
}
CampMailFromCamperBatch {
varchar id PK
timestamp generatedAt
boolean autoDelivery
varchar sourcePath
}
CampMailToCamper {
uuid id PK
int rockpile_id UK
timestamp deliverAt
varchar text
varchar senderName
varchar senderId FK
varchar recipientId FK
varchar sessionId FK
}
Notification {
uuid id PK
int rockpile_id UK
varchar status
varchar type
varchar deliveryMethod
timestamp sendAfter
timestamp sendBy
varchar email
varchar payload
varchar deliveryId
varchar userId FK
varchar bundleId FK
varchar postScheduleId FK
}
CampMailFromCamper }o--|| CampMailFromCamperBatch : "in batch"
CampMailFromCamper }o--|| Image : "scanned image"
CampMailFromCamper }o--|| User : "sent by"
CampMailFromCamper }o--|| User : "sent to"
CampMailFromCamper }o--|| CampSession : "during session"
CampMailToCamper }o--|| User : "from"
CampMailToCamper }o--|| User : "to"
CampMailToCamper }o--|| CampSession : "during session"
Notification }o--|| User : "for user"
Notification }o--o| Notification : "bundled in"
Notification }o--o| PostSchedule : "from schedule"
Entities
| Entity | Purpose | Key relationships |
|---|---|---|
| CampMailFromCamper | Scanned physical mail from campers (with AI blank-letter scoring) | Links sender/recipient, CampSession, Image |
| CampMailFromCamperBatch | Batch scan processing | Has many CampMailFromCamper |
| CampMailToCamper | Digital messages to campers (parent → child) | Links sender/recipient, CampSession |
| Notification | Push/email notification queue with bundling | Links User, PostSchedule; self-referencing |
Domain 11
Surveys
Survey collection and metrics analysis. Responses are imported from PaperSurvey.io, linked back to Registrations or Employments, and aggregated into dashboards with statistical color-coding per camp.
Key pattern — StandardSurveyQuestion provides cross-survey metric normalization so metrics can be compared year
over year. HalftimeQuestionMapping and PaperSurveyMetricThreshold are recent additions (March 2026) enabling halftime-survey
ingestion and statistical dashboard thresholding.
Entity diagram
erDiagram
PaperSurvey {
uuid id PK
varchar surveyId
int surveyCollectionId
varchar surveyVersionId
varchar surveyName
enum type
boolean activated
varchar campId FK
}
PaperSurveyBatch {
uuid id PK
int surveyBatchId
varchar paperSurveyId FK
varchar sessionId FK
enum status
varchar s3Path
int usersCount
int responseCount
json generatedSummary
json calculatedMetrics
}
PaperSurveyFilter {
uuid id PK
varchar paperSurveyBatchId FK
enum gender
enum positionGroup
json calculatedMetrics
}
PaperSurveyQuestion {
uuid id PK
varchar paperSurveyId FK
int displayOrder
int surveyQuestionId
varchar name
varchar group
enum type
text options
}
PaperSurveyResponse {
uuid id PK
int responseId
varchar paperSurveyId FK
varchar sessionId FK
varchar registrationId FK
varchar employmentId FK
}
PaperSurveyAnswer {
uuid id PK
varchar paperSurveyResponseId FK
varchar paperSurveyQuestionId FK
varchar answer
varchar scanImage
}
StandardSurveyQuestion {
uuid id PK
varchar standardQuestionKey UK
varchar standardQuestionLabel
varchar category
enum surveyType
enum processingType
}
StandardSurveyQuestionMapping {
uuid id PK
varchar standardSurveyQuestionId FK
int surveyQuestionId UK
boolean isActive
}
HalftimeQuestionMapping {
uuid id PK
varchar paperSurveyId FK
varchar paperSurveyQuestionId FK
enum questionType
varchar prefillKey
varchar evaluationTargetPrefillKey
varchar prefillSlot
varchar questionKey
enum processingType
}
PaperSurveyMetricThreshold {
uuid id PK
varchar campId FK
varchar metricName
boolean isStaff
decimal average
decimal standardDeviation
decimal lowerThreshold
decimal upperThreshold
}
PaperSurvey }o--|| Camp : "for camp"
PaperSurveyBatch }o--|| PaperSurvey : "of survey"
PaperSurveyBatch }o--|| CampSession : "for session"
PaperSurveyFilter }o--|| PaperSurveyBatch : "filters batch"
PaperSurveyQuestion }o--|| PaperSurvey : "in survey"
PaperSurveyResponse }o--|| PaperSurvey : "for survey"
PaperSurveyResponse }o--|| CampSession : "for session"
PaperSurveyResponse }o--o| Registration : "by registrant"
PaperSurveyResponse }o--o| Employment : "by staff"
PaperSurveyAnswer }o--|| PaperSurveyResponse : "in response"
PaperSurveyAnswer }o--o| PaperSurveyQuestion : "answers question"
StandardSurveyQuestionMapping }o--|| StandardSurveyQuestion : "maps to"
HalftimeQuestionMapping }o--|| PaperSurvey : "classifies survey"
HalftimeQuestionMapping }o--|| PaperSurveyQuestion : "classifies question"
PaperSurveyMetricThreshold }o--|| Camp : "thresholds for"
Entities
| Entity | Purpose | Key relationships |
|---|---|---|
| PaperSurvey | Survey definition | Links to Camp |
| PaperSurveyBatch | Batch of responses per session with rollup JSON | Links PaperSurvey, CampSession |
| PaperSurveyFilter | Pre-calculated filtered metrics (by gender/position) | Links to PaperSurveyBatch |
| PaperSurveyQuestion | Question definitions | Links to PaperSurvey |
| PaperSurveyResponse | Individual response record | Links PaperSurvey, CampSession, Registration/Employment |
| PaperSurveyAnswer | Individual answer to a question | Links Response, Question |
| StandardSurveyQuestion | Canonical metric definitions across surveys | Standalone reference |
| StandardSurveyQuestionMapping | Maps vendor questions → standards | Links to StandardSurveyQuestion |
| HalftimeQuestionMapping New 2026-03 | Classification of halftime survey questions for ingestion + LLM summarization | Links PaperSurvey + PaperSurveyQuestion |
| PaperSurveyMetricThreshold New 2026-03 | Per-camp statistical thresholds for dashboard color-coding | Links to Camp |
Domain 12
Social Features
The follow/connection system allowing parents, families, and supporters to follow campers and receive their activity updates.
Entity diagram
erDiagram
Follower {
uuid id PK
int rockpile_id UK
enum status
timestamp deactivatedAt
varchar invitationId FK
varchar userId FK
varchar targetId FK
}
FollowInvitation {
uuid id PK
int rockpile_id UK
varchar code
varchar inviteeLabel
varchar email
enum status
timestamp expiresAt
timestamp respondedAt
varchar inviterId FK
varchar requesterUserId FK
enum invitationType
}
Follower }o--|| User : "follower"
Follower }o--|| User : "following"
Follower ||--o| FollowInvitation : "from invitation"
FollowInvitation }o--|| User : "invited by"
FollowInvitation }o--o| User : "requested by"
Entities
| Entity | Purpose | Key relationships |
|---|---|---|
| Follower | Follow relationship between users | Links two Users |
| FollowInvitation | Invitation to follow (code-based) | Links inviter/requester Users |
Domain 13
Reporting & Dashboards
Tableau integration for analytics. Dashboards are grouped for discoverability and admin users can favorite their go-to views. Reports provide metadata for downloadable reports.
Entity diagram
erDiagram
Report {
uuid id PK
varchar name
varchar description
enum category
enum type
varchar code
}
TableauDashboard {
uuid id PK
varchar title
varchar shortTitle
varchar description
varchar tableauLink
int displayOrder
}
TableauDashboardGroup {
uuid id PK
varchar name
varchar colorCode
int displayOrder
}
TableauDashboardGroupRelation {
uuid id PK
varchar dashboardId FK
varchar dashboardGroupId FK
int dashboardDisplayOrder
}
TableauDashboardFavorites {
uuid id PK
varchar dashboardId FK
varchar adminUserId FK
}
TableauDashboard ||--o{ TableauDashboardGroupRelation : "in groups"
TableauDashboardGroup ||--o{ TableauDashboardGroupRelation : "has dashboards"
TableauDashboardFavorites }o--|| TableauDashboard : "favorites"
TableauDashboardFavorites }o--|| AdminUser : "favorited by"
Entities
| Entity | Purpose | Key relationships |
|---|---|---|
| Report | Report definitions | Standalone |
| TableauDashboard | Dashboard metadata | Has group relations |
| TableauDashboardGroup | Dashboard categories | Has group relations |
| TableauDashboardGroupRelation | Junction: dashboard ↔ group | Links both sides |
| TableauDashboardFavorites | Admin user favorites | Links AdminUser to Dashboard |
Domain 14
Tools & System
Miscellaneous system entities: admin shortcuts, background job observability, device sync state, and the content-discovery pipeline.
Entity diagram
erDiagram
Tool {
uuid id PK
varchar name
varchar link
varchar description
}
ToolFavorites {
uuid id PK
varchar toolId FK
varchar adminUserId FK
varchar displayOrder
}
TriggeredAction {
uuid id PK
varchar type
enum status
timestamp startedAt
timestamp endedAt
timestamp cancelledAt
int discoveredCount
int errorCount
int successCount
varchar parameterStore
varchar errorMessage
decimal memoryUsedStartMB
decimal memoryUsedEndMB
decimal memoryDeltaMB
}
SidekickDeviceStatus {
uuid id PK
timestamp createdAt
timestamp modifiedAt
varchar deviceName
varchar model
varchar operatingSystem
varchar deviceId UK
varchar IPAddress
timestamp lastPingAt
varchar sidekickBuildNumber
varchar photoQueue
enum syncStatus
}
DiscoveredContent {
uuid id PK
varchar sourceReference
varchar targetReference
enum status
timestamp catalogedAt
timestamp publishedAt
varchar typeId FK
}
ToolFavorites }o--|| Tool : "favorites"
ToolFavorites }o--|| AdminUser : "favorited by"
DiscoveredContent }o--|| ContentType : "of type"
Entities
| Entity | Purpose | Key relationships |
|---|---|---|
| Tool | External tools/links for admin portal | Standalone |
| ToolFavorites | Admin user's favorite tools | Links Tool, AdminUser |
| TriggeredAction | Background job tracking with memory metrics | Standalone |
| SidekickDeviceStatus | Mobile device sync status | Standalone |
| DiscoveredContent | Content discovery/cataloging pipeline | Links to ContentType |