Data Models — Cross-Cutting Synthesis

Key Takeaways

1. 35 PostgreSQL databases per tenant — Database-per-service pattern strictly enforced. No cross-database foreign keys. All inter-service data references use UUIDs or external IDs. Single shared DB exception (email/sms/notifications).
2. ~280 Flyway migrations across analyzed services — Inventory (36) and Class-Catalog (32) are the most complex. Wallet (3) and SSE (3) are the simplest. All use additive-only migration patterns (no destructive schema changes found).
3. All user IDs are Keycloak UUIDs — No local identity generation. Every service references users by Keycloak UUID. This simplifies data migration (no ID mapping needed) but creates universal Keycloak dependency.
4. External ID coupling in 4 services — Stripe (customer/product/subscription IDs), Mux (asset/playback IDs), Zoom (meeting/registrant IDs), and Stream Chat (channel IDs) store external platform references. These must be preserved during migration.
5. No double-entry bookkeeping — Transaction service uses a simple payment log (single table, JSON purchases column). If Gen 3 needs financial reporting, the data model must be redesigned.

Data Lineage: Gen 1 → Gen 2

Schema Evolution Pattern

All Gen 2 services follow the same schema evolution approach: - Flyway manages migrations (V1, V2, V3… pattern) - Additive only — new columns are nullable, no column drops observed - UUID primary keys throughout (no auto-increment integers) - Keycloak UUID foreign keys for all user references - created_on / updated_on timestamps on most entities - Soft deletes where needed (is_deleted + deleted_on)

Gen 1 → Gen 2 Schema Differences

Data Migration Complexity by Domain

Complete Entity-Relationship Overview

Identity Domain

celebrity_profiles (PK: celebrity_user_id → Keycloak UUID)
  ├── celebrity_profile_image
  ├── celebrity_profile_banner
  ├── celebrity_profile_shortlinks
  ├── profile_status_history
  └── referral_codes

fan_profiles (PK: fan_user_id → Keycloak UUID)
  ├── fan_follows (PK: MD5 composite)
  ├── fan_follow_changes (audit)
  ├── fan_media
  └── global_preferences

Content & Streaming Domain

contents (PK: UUID, FK: creator_id → Keycloak)
  ├── content_categories → categories
  ├── content_ratings
  ├── content_files (NFS paths)
  ├── content_tags
  └── seu_pdu_completions

media (PK: UUID, FK: creator_id → Keycloak)
  ├── media_thumbnails
  ├── media_tags
  ├── blogs
  └── news

webinars (PK: UUID, FK: host_id → Keycloak)
  ├── webinar_registrations (zoom_registrant_id)
  ├── webinar_recordings (zoom_recording_id)
  ├── webinar_calendar_events (google_event_id)
  ├── webinar_attendees
  └── webinar_series

Payment Domain

stripe_customers (PK: peeq_user_id → Keycloak, stripe_customer_id)
exchange_rates (virtual currency pricing)
discount_codes → discount_codes_usage
checkout_purchase_products (stripe_product_id, inventory_id)
checkout_purchase_recipients (PII: name, email, phone, LinkedIn)
promo_code_usage
one_off_subscriptions_to_cancel (stripe_subscription_id)

subscriptions (PK: UUID, inventory_info JSON)
  ├── subscription_associated_tags
  └── series → series_published_notification_log

balance_transaction (PK: UUID, user_id, int balance)
  └── create_transaction (debit/credit log)
      └── create_transaction_related_transactions

transactions (single table, JSON purchases column)

Events Domain

shoutout (PK: UUID)
  ├── shoutout_video (mux_asset_id, mux_playback_id)
  ├── shoutout_recipient
  └── shoutout_fulfillment_state

inventory_items (PK: UUID, 36 migrations, 12+ tables)
  ├── inventory_prices
  ├── inventory_entitlements
  ├── inventory_purchases (user_id, state machine)
  ├── inventory_tags
  └── [complex product catalog schema]

class_catalog (32 migrations, 15+ entities)
  ├── courses
  ├── sections (learning units)
  ├── journeys (learning paths)
  ├── office_hours
  ├── ceu_pdu_credits
  └── [learning management schema]

Communication Domain

-- Shared DB: peeq-notification-service-db --
notification_preferences (user_id, channel, enabled)
notification_subscriptions (user_id, topic)
notification_log (sent notifications)
notification_digests (batched notifications)

email_templates (template definitions)
email_log (sent emails with Mandrill IDs)

sms_templates (template definitions)
sms_log (sent messages with Twilio SIDs)

-- Separate DBs --
chat: stream_channels, channel_access (Stream Chat channel IDs)
message_board: boards, posts, donations, leaderboard
sse: sse_groups, sse_group_members

Data Volume Estimates

What We Know (from Infrastructure — Session 8)

What We Don’t Know (H8 — Still L0)

• Actual row counts per table
• Table sizes (GB)
• Index sizes
• Query performance characteristics
• Growth rates

Proxy Estimates (from migration counts)

PII Data Locations

Identified across all analyzed domains:

Note: Most services store only Keycloak UUIDs, not PII directly. PII is concentrated in Keycloak, checkout recipients, celebrity profiles, and shoutout recipients.

Stateful Data Requiring Special Migration Handling

Active Process Instances (BPM)

Migration strategy: Drain all active instances before BPM engine replacement. Both workflows are simple enough (~10 states) to complete within hours.

Session/Token State (Redis + Keycloak)

Queued Messages (RabbitMQ)

75+ message types across all domains. During migration: - Consumer side: Drain queues before stopping services - Publisher side: Stop publishers before draining - Order: Stop publishers → drain queues → migrate → restart

NFS File Storage

Migration: Must copy NFS data to new storage (GCS recommended) or maintain NFS mount during transition.

Elasticsearch Index Migration

Current State

• Elasticsearch: Cloud-hosted (port 9243, HTTPS)
• Kibana: 7.15.2
• Search service: Deployed to all tenants
• Log aggregation: peeq-logging (Node.js) → Elasticsearch

Index Types (Inferred)

Migration Considerations

• Log indices: Can be recreated from Cloud Logging (GCP native)
• Search indices: Must be rebuilt from source databases
• Kibana dashboards: Manually created (not in code), must be exported/imported
• Elasticsearch version: 7.x — may need upgrade if Gen 3 uses newer version

Data Migration Strategy Recommendations

Phase 1: Low-Risk (Can migrate independently)

• Wallet (3 tables, no external IDs, simple schema)
• Transaction (1 table, no FK constraints)
• SSE (2 tables, reconstructible from service state)
• Chat (2 tables, Stream Chat is SaaS — metadata only)
• Message-Board (4 tables, self-contained)

Phase 2: Medium-Risk (External ID coupling)

• Celebrity + Fan (Keycloak UUID coupling, but no external API IDs)
• Email + SMS + Notifications (shared DB, Mandrill/Twilio SIDs in logs)
• Subscriptions (inventory_info JSON blob, Stripe product IDs)

Phase 3: High-Risk (Complex schemas + external IDs)

• Stripe (Stripe customer/product/subscription IDs)
• Content + Media (Mux asset IDs, NFS file paths)
• Webinar (Zoom meeting IDs, Google Calendar event IDs)
• Inventory (36 migrations, cross-cutting hub)
• Class-Catalog (32 migrations, learning credits)
• Shoutout + BPM (Mux video IDs, in-flight BPM state)

Phase 4: Infrastructure (Must be last)

• Keycloak (affects all 28+ services)
• BPM engines (must drain in-flight instances)

Last updated: 2026-01-30 — Session 9 Review by: 2026-04-30 Staleness risk: Medium — schema evolution continues with development