Database Schema

Current State Summary

Service	Current implementation	Production target
Identity	PostgreSQL schema with Alembic migrations	Managed PostgreSQL
Customer	SQLite tables for invites and relationships	Managed PostgreSQL
Game	SQLite tables for invites and sessions	Managed PostgreSQL
Chat	SQLite tables for conversations, messages, and audits	Managed PostgreSQL

Identity Service

The identity schema currently includes:

Table	Purpose
users	User identity, credentials, role, phone state, WHOT account type, distributor link, VIP flag
refresh_tokens	Refresh-token persistence and revocation
user_login_attempts	Authentication attempt audit trail
user_otps	One-time password records
influencers, referrals, earnings, user_influencer_codes	Legacy seed-domain tables retained in the repository but not part of the active WHOT runtime surface

Key WHOT-specific additions on users are username, account_type, distributor_id, and vip_enabled.

Customer Service

Table	Key columns
invites	token, distributor_id, distributor_alias, expires_at, redeemed_by_user_id
relationships	customer_id, distributor_id, customer_username

Game Service

Table	Key columns
invites	id, sender_id, recipient_username, status
sessions	id, both player IDs, top card, current player, reconnect tokens, scores

Chat Service

Table	Key columns
conversations	conversation ID, distributor/customer IDs, legal-hold state and reason
messages	ciphertext, timestamps, expiry, delivery/read sets, participant deletion marker
audits	conversation ID, moderator ID, reason

Data Design Notes

• Current local storage is intentionally pragmatic for evaluation and development.
• Production readiness requires migrating customer, game, and chat persistence to managed PostgreSQL and introducing production-grade migration workflows.
• The chat store is the most sensitive persistence area because it combines ciphertext, retention timestamps, legal hold, and moderator audit records.