Eve library

Lilith Platform Database Schema

Guide

Revision 1

On this page

This document describes the database schema for all Lilith platform services.

Overview#

The Lilith platform uses PostgreSQL with the following extensions:

  • pgvector - Vector similarity search for AI/ML features
  • pg_trgm - Trigram matching for full-text search

Each service maintains its own set of tables with a dedicated migrations directory.

Services & Tables#

Service Tables Description
auth 11 Authentication, users, sessions, OAuth
content 13 Courses, lessons, meditations, lectures, wisdoms
conversation 5 Chat threads, messages, citations, feedback
media 5 Assets, versions, transcripts, metadata
notification 4 Subscriptions, templates, device tokens, delivery
user-preferences 5 Preferences, favorites, history, achievements
payment-orchestrator 9 Customers, products, subscriptions, invoices

Auth Service#

users#

Primary user account table.

Column Type Constraints Description
id UUID PK, default uuid_generate_v4() Primary key
email VARCHAR(255) NOT NULL, UNIQUE User email address
email_normalized VARCHAR(255) NOT NULL, UNIQUE Lowercase normalized email
password_hash VARCHAR(255) Argon2 password hash
status ENUM NOT NULL, default 'pending' pending, active, suspended, deleted
role ENUM NOT NULL, default 'user' user, admin, moderator, creator
email_verified BOOLEAN NOT NULL, default false Email verification status
failed_login_attempts INTEGER NOT NULL, default 0 Failed login counter
lockout_until TIMESTAMPTZ Account lockout expiry
last_login_at TIMESTAMPTZ Last successful login
created_at TIMESTAMPTZ NOT NULL, default now() Record creation time
updated_at TIMESTAMPTZ NOT NULL, default now() Last update time

Check Constraints:

  • chk_users_email_format - Email must be valid format
  • chk_users_email_normalized_lowercase - Normalized email must be lowercase
  • chk_users_failed_login_attempts_non_negative - Failed attempts >= 0

user_profiles#

Extended user profile information.

Column Type Constraints Description
id UUID PK Primary key
user_id UUID NOT NULL, FK -> users User reference
display_name VARCHAR(255) Public display name
avatar_url VARCHAR(1000) Profile picture URL
bio TEXT User biography
timezone VARCHAR(50) User timezone
preferred_locale VARCHAR(10) Language preference
date_of_birth DATE Birth date
country VARCHAR(2) ISO country code

user_sessions#

Active login sessions.

Column Type Constraints Description
id UUID PK Session ID
user_id UUID NOT NULL, FK -> users User reference
token_hash VARCHAR(255) NOT NULL Hashed session token
device_info JSONB Device metadata
ip_address VARCHAR(45) Client IP address
user_agent TEXT Browser user agent
expires_at TIMESTAMPTZ NOT NULL Session expiry
created_at TIMESTAMPTZ NOT NULL Session start time

Check Constraints:

  • chk_user_sessions_expires_after_created - expires_at > created_at
  • chk_user_sessions_ip_format - Valid IP format

refresh_tokens#

JWT refresh tokens for session renewal.

Column Type Constraints Description
id UUID PK Token ID
user_id UUID NOT NULL, FK -> users User reference
session_id UUID FK -> user_sessions Associated session
token_hash VARCHAR(255) NOT NULL Hashed token
family_id UUID Token rotation family
expires_at TIMESTAMPTZ NOT NULL Token expiry
revoked_at TIMESTAMPTZ Revocation time
created_at TIMESTAMPTZ NOT NULL Token creation time

oauth_connections#

Third-party OAuth provider connections.

Column Type Constraints Description
id UUID PK Connection ID
user_id UUID NOT NULL, FK -> users User reference
provider VARCHAR(50) NOT NULL OAuth provider name
provider_user_id VARCHAR(255) NOT NULL External user ID
access_token TEXT Encrypted access token
refresh_token TEXT Encrypted refresh token
token_expires_at TIMESTAMPTZ Token expiry
profile_data JSONB Provider profile data

email_verification_tokens#

Email verification tokens.

Column Type Constraints Description
id UUID PK Token ID
user_id UUID NOT NULL, FK -> users User reference
email VARCHAR(255) NOT NULL Email to verify
token_hash VARCHAR(255) NOT NULL Hashed token
expires_at TIMESTAMPTZ NOT NULL Token expiry
used_at TIMESTAMPTZ Token usage time

password_reset_tokens#

Password reset request tokens.

Column Type Constraints Description
id UUID PK Token ID
user_id UUID NOT NULL, FK -> users User reference
token_hash VARCHAR(255) NOT NULL Hashed token
expires_at TIMESTAMPTZ NOT NULL Token expiry
used_at TIMESTAMPTZ Token usage time

Passwordless login magic links.

Column Type Constraints Description
id UUID PK Token ID
email VARCHAR(255) NOT NULL Target email
token_hash VARCHAR(255) NOT NULL Hashed token
expires_at TIMESTAMPTZ NOT NULL Token expiry
used_at TIMESTAMPTZ Token usage time

auth_audit_logs#

Security audit trail.

Column Type Constraints Description
id UUID PK Log entry ID
user_id UUID FK -> users User reference
event_type VARCHAR(100) NOT NULL Event type
event_category VARCHAR(50) NOT NULL Event category
ip_address VARCHAR(45) Client IP
user_agent TEXT Browser info
metadata JSONB Additional data
created_at TIMESTAMPTZ NOT NULL Event time

webauthn_credentials#

FIDO2/WebAuthn passkey credentials.

Column Type Constraints Description
id UUID PK Credential ID
user_id UUID NOT NULL, FK -> users User reference
credential_id BYTEA NOT NULL FIDO2 credential ID
public_key BYTEA NOT NULL Public key
sign_count INTEGER NOT NULL, default 0 Signature counter
device_name VARCHAR(255) Friendly device name
transports JSONB Supported transports

webauthn_challenges#

WebAuthn registration/authentication challenges.

Column Type Constraints Description
id UUID PK Challenge ID
user_id UUID FK -> users User reference
challenge BYTEA NOT NULL Challenge bytes
type ENUM NOT NULL registration, authentication
expires_at TIMESTAMPTZ NOT NULL Challenge expiry

Content Service#

courses#

Educational courses and programs.

Column Type Constraints Description
id UUID PK Course ID
slug VARCHAR(255) NOT NULL, UNIQUE URL-safe identifier
title VARCHAR(500) NOT NULL Course title
description TEXT Course description
instructor_id UUID Instructor user ID
difficulty_level ENUM beginner, intermediate, advanced
estimated_duration_minutes INTEGER Total duration
lesson_count INTEGER NOT NULL, default 0 Number of lessons
price DECIMAL(10,2) Price (null = free)
currency VARCHAR(3) ISO currency code
status ENUM NOT NULL, default 'draft' draft, published, archived
average_rating DECIMAL(2,1) 0-5 star rating
enrollment_count INTEGER NOT NULL, default 0 Enrollments
completion_count INTEGER NOT NULL, default 0 Completions
published_at TIMESTAMPTZ Publication date

Check Constraints:

  • chk_courses_slug_format - URL-safe slug format
  • chk_courses_title_not_empty - Title cannot be empty
  • chk_courses_duration_positive - Duration > 0 if set
  • chk_courses_lesson_count_non_negative - Lesson count >= 0
  • chk_courses_price_non_negative - Price >= 0 if set
  • chk_courses_currency_format - 3-character ISO code
  • chk_courses_rating_range - Rating between 0 and 5

lessons#

Individual course lessons.

Column Type Constraints Description
id UUID PK Lesson ID
course_id UUID NOT NULL, FK -> courses Parent course
slug VARCHAR(255) NOT NULL Lesson slug
title VARCHAR(500) NOT NULL Lesson title
content TEXT Lesson content
duration_minutes INTEGER Lesson duration
sort_order INTEGER NOT NULL, default 0 Display order
media_id UUID Associated media
status ENUM NOT NULL, default 'draft' Status
view_count INTEGER NOT NULL, default 0 View counter
completion_count INTEGER NOT NULL, default 0 Completion counter

meditations#

Guided meditation content.

Column Type Constraints Description
id UUID PK Meditation ID
slug VARCHAR(255) NOT NULL, UNIQUE URL-safe identifier
title VARCHAR(500) NOT NULL Meditation title
description TEXT Description
duration_minutes INTEGER NOT NULL Duration in minutes
persona_id UUID FK -> personas Guiding persona
media_id UUID Audio/video media
theme ENUM calm, focus, sleep, stress, etc.
status ENUM NOT NULL, default 'draft' Status
play_count INTEGER NOT NULL, default 0 Play counter
average_rating DECIMAL(2,1) 0-5 star rating

lectures#

Educational lecture content.

Column Type Constraints Description
id UUID PK Lecture ID
slug VARCHAR(255) NOT NULL, UNIQUE URL-safe identifier
title VARCHAR(500) NOT NULL Lecture title
description TEXT Description
subject VARCHAR(255) NOT NULL Subject area
speaker_id UUID Speaker user ID
duration_minutes INTEGER Duration
media_id UUID Associated media
transcript TEXT Full transcript
status ENUM NOT NULL, default 'draft' Status
view_count INTEGER NOT NULL, default 0 View counter
average_rating DECIMAL(2,1) 0-5 star rating

wisdoms#

Wisdom teachings and insights.

Column Type Constraints Description
id UUID PK Wisdom ID
slug VARCHAR(255) NOT NULL, UNIQUE URL-safe identifier
title VARCHAR(500) NOT NULL Wisdom title
content TEXT NOT NULL Content text
source VARCHAR(500) Original source
tradition VARCHAR(100) Spiritual tradition
themes JSONB Theme tags
status ENUM NOT NULL, default 'draft' Status
view_count INTEGER NOT NULL, default 0 View counter
share_count INTEGER NOT NULL, default 0 Share counter

quotes#

Inspirational quotes.

Column Type Constraints Description
id UUID PK Quote ID
text TEXT NOT NULL Quote text
author VARCHAR(255) NOT NULL Quote author
source VARCHAR(500) Source attribution
themes JSONB Theme tags
view_count INTEGER NOT NULL, default 0 View counter
share_count INTEGER NOT NULL, default 0 Share counter

reflections#

Journaling prompts and reflections.

Column Type Constraints Description
id UUID PK Reflection ID
slug VARCHAR(255) NOT NULL, UNIQUE URL-safe identifier
title VARCHAR(500) NOT NULL Reflection title
prompt TEXT NOT NULL Reflection prompt
suggested_duration_minutes INTEGER Suggested time
themes JSONB Theme tags
status ENUM NOT NULL, default 'draft' Status
completion_count INTEGER NOT NULL, default 0 Completions

personas#

AI guide personas.

Column Type Constraints Description
id UUID PK Persona ID
slug VARCHAR(255) NOT NULL, UNIQUE URL-safe identifier
name VARCHAR(255) NOT NULL Persona name
description TEXT Persona description
avatar_url VARCHAR(1000) Avatar image
voice_id VARCHAR(100) TTS voice ID
voice_speed DECIMAL(3,2) Speech rate
voice_pitch DECIMAL(3,2) Voice pitch
personality_traits JSONB Personality config
system_prompt TEXT LLM system prompt
status ENUM NOT NULL, default 'draft' Status
conversation_count INTEGER NOT NULL, default 0 Conversations

content_versions#

Content versioning for audit trail.

Column Type Constraints Description
id UUID PK Version ID
content_id UUID NOT NULL Content item ID
content_type VARCHAR(50) NOT NULL course, lesson, etc.
version_number INTEGER NOT NULL Version number
content_snapshot JSONB NOT NULL Full content copy
change_summary TEXT Change description
created_by UUID Author user ID
created_at TIMESTAMPTZ NOT NULL Version timestamp

content_locales#

Localized content translations.

Column Type Constraints Description
id UUID PK Locale entry ID
content_id UUID NOT NULL Content item ID
content_type VARCHAR(50) NOT NULL Content type
locale VARCHAR(10) NOT NULL Locale code (en-US)
translated_fields JSONB NOT NULL Translated content
translation_status ENUM pending, approved, etc.
translation_quality_score DECIMAL(3,2) 0-1 quality score

content_categories#

Hierarchical content categories.

Column Type Constraints Description
id UUID PK Category ID
slug VARCHAR(255) NOT NULL, UNIQUE URL-safe identifier
name VARCHAR(255) NOT NULL Category name
description TEXT Description
parent_id UUID FK -> content_categories Parent category
depth INTEGER NOT NULL, default 0 Hierarchy depth
path LTREE Materialized path
course_count INTEGER NOT NULL, default 0 Courses in category
total_content_count INTEGER NOT NULL, default 0 All content count

content_tags#

Content tagging system.

Column Type Constraints Description
id UUID PK Tag ID
slug VARCHAR(100) NOT NULL, UNIQUE URL-safe identifier
name VARCHAR(100) NOT NULL Tag name
description TEXT Tag description
usage_count INTEGER NOT NULL, default 0 Usage count

content_tag_assignments#

Many-to-many content-tag relationships.

Column Type Constraints Description
id UUID PK Assignment ID
tag_id UUID NOT NULL, FK -> content_tags Tag reference
content_id UUID NOT NULL Content item ID
content_type VARCHAR(50) NOT NULL Content type
relevance_score DECIMAL(3,2) 0-1 relevance

Conversation Service#

threads#

Conversation threads.

Column Type Constraints Description
id UUID PK Thread ID
user_id UUID NOT NULL User reference
title VARCHAR(500) Thread title
persona_id UUID AI persona
content_id UUID Related content
content_type VARCHAR(50) Content type
status ENUM NOT NULL, default 'active' active, archived, deleted
message_count INTEGER NOT NULL, default 0 Total messages
user_message_count INTEGER NOT NULL, default 0 User messages
assistant_message_count INTEGER NOT NULL, default 0 AI messages
total_tokens_used INTEGER NOT NULL, default 0 Token usage
last_message_at TIMESTAMPTZ Last activity

Check Constraints:

  • chk_threads_message_counts_consistent - Sum of user + assistant <= total

messages#

Individual chat messages.

Column Type Constraints Description
id UUID PK Message ID
thread_id UUID NOT NULL, FK -> threads Parent thread
role ENUM NOT NULL user, assistant, system
content TEXT NOT NULL Message content
sequence_number INTEGER NOT NULL Order in thread
parent_message_id UUID FK -> messages Reply to message
model VARCHAR(100) LLM model used
prompt_tokens INTEGER Input tokens
completion_tokens INTEGER Output tokens
total_tokens INTEGER Total tokens
regeneration_count INTEGER NOT NULL, default 0 Regenerations

Check Constraints:

  • chk_messages_content_not_empty - Content cannot be empty
  • chk_messages_sequence_positive - Sequence > 0
  • chk_messages_prompt_tokens_non_negative - Tokens >= 0

message_citations#

Source citations in AI responses.

Column Type Constraints Description
id UUID PK Citation ID
message_id UUID NOT NULL, FK -> messages Parent message
citation_index INTEGER NOT NULL Citation number
source_id UUID Source content ID
content_type VARCHAR(50) Source type
title VARCHAR(500) Source title
url VARCHAR(1000) Source URL
excerpt TEXT Cited excerpt
relevance_score DECIMAL(3,2) 0-1 relevance
confidence_score DECIMAL(3,2) 0-1 confidence
start_offset INTEGER Start position
end_offset INTEGER End position

message_feedback#

User feedback on AI responses.

Column Type Constraints Description
id UUID PK Feedback ID
message_id UUID NOT NULL, FK -> messages Target message
user_id UUID NOT NULL User reference
feedback_type ENUM NOT NULL thumbs_up, thumbs_down, flag
rating_score INTEGER 1-5 rating
comment TEXT User comment
improvement_suggestion TEXT Improvement suggestion
selection_start INTEGER Selected text start
selection_end INTEGER Selected text end

conversation_summaries#

AI-generated conversation summaries.

Column Type Constraints Description
id UUID PK Summary ID
thread_id UUID NOT NULL, FK -> threads Thread reference
summary TEXT NOT NULL Summary text
key_points JSONB Key points array
topics JSONB Discussed topics
start_sequence INTEGER First message
end_sequence INTEGER Last message
message_count INTEGER NOT NULL Messages covered
sentiment_score DECIMAL(3,2) -1 to 1 sentiment
model VARCHAR(100) LLM model used
tokens_used INTEGER Tokens consumed
version INTEGER NOT NULL, default 1 Summary version

Media Service#

assets#

Media asset storage.

Column Type Constraints Description
id UUID PK Asset ID
filename VARCHAR(500) NOT NULL Stored filename
original_filename VARCHAR(500) NOT NULL Original name
title VARCHAR(500) Display title
description TEXT Description
mime_type VARCHAR(100) NOT NULL MIME type
file_size BIGINT NOT NULL Size in bytes
bucket VARCHAR(100) NOT NULL Storage bucket
storage_key VARCHAR(1000) NOT NULL Storage path
width INTEGER Image/video width
height INTEGER Image/video height
duration_ms INTEGER Audio/video duration
status ENUM NOT NULL, default 'processing' Status
view_count INTEGER NOT NULL, default 0 Views
download_count INTEGER NOT NULL, default 0 Downloads
owner_id UUID NOT NULL Owner user ID
owner_type VARCHAR(50) NOT NULL user, system

asset_versions#

Processed asset variants (thumbnails, transcodes).

Column Type Constraints Description
id UUID PK Version ID
asset_id UUID NOT NULL, FK -> assets Parent asset
version_number INTEGER NOT NULL Version number
variant_type VARCHAR(50) NOT NULL original, thumbnail, transcode
filename VARCHAR(500) NOT NULL Stored filename
mime_type VARCHAR(100) NOT NULL MIME type
file_size BIGINT NOT NULL Size in bytes
width INTEGER Variant width
height INTEGER Variant height
bitrate INTEGER Audio/video bitrate
quality VARCHAR(20) low, medium, high, lossless

transcripts#

Audio/video transcriptions.

Column Type Constraints Description
id UUID PK Transcript ID
asset_id UUID NOT NULL, FK -> assets Source asset
locale VARCHAR(10) NOT NULL Transcript language
content TEXT NOT NULL Full transcript
segments JSONB Timed segments
confidence_score DECIMAL(3,2) 0-1 confidence
transcription_service VARCHAR(50) Service used
word_count INTEGER Total words
processing_time_ms INTEGER Processing duration
status ENUM NOT NULL processing, completed, failed

media_metadata#

Extended media metadata.

Column Type Constraints Description
id UUID PK Metadata ID
asset_id UUID NOT NULL, FK -> assets Parent asset
duration_seconds DECIMAL(10,3) Duration
bit_rate INTEGER Overall bitrate
video_codec VARCHAR(50) Video codec
video_width INTEGER Video width
video_height INTEGER Video height
frame_rate DECIMAL(6,3) Frame rate
audio_codec VARCHAR(50) Audio codec
audio_sample_rate INTEGER Sample rate
audio_channels INTEGER Channel count
exif_data JSONB EXIF metadata
gps_latitude DECIMAL(9,6) GPS latitude
gps_longitude DECIMAL(10,6) GPS longitude
nsfw_score DECIMAL(3,2) 0-1 NSFW score

processing_jobs#

Media processing job queue.

Column Type Constraints Description
id UUID PK Job ID
asset_id UUID NOT NULL, FK -> assets Target asset
job_type ENUM NOT NULL transcode, thumbnail, analyze
status ENUM NOT NULL, default 'pending' Job status
priority INTEGER NOT NULL, default 0 Priority level
progress INTEGER NOT NULL, default 0 0-100 progress
input_params JSONB Input parameters
output_params JSONB Output results
error_message TEXT Error details
attempt_count INTEGER NOT NULL, default 0 Retry count
max_attempts INTEGER NOT NULL, default 3 Max retries
scheduled_at TIMESTAMPTZ Scheduled time
started_at TIMESTAMPTZ Processing start
completed_at TIMESTAMPTZ Completion time
duration_ms INTEGER Processing time
worker_id VARCHAR(100) Processing worker

Notification Service#

notification_subscriptions#

User notification preferences.

Column Type Constraints Description
id UUID PK Subscription ID
user_id UUID NOT NULL User reference
channel ENUM NOT NULL email, push, sms, in_app
category ENUM NOT NULL marketing, transactional, etc.
is_subscribed BOOLEAN NOT NULL, default true Subscription status
is_verified BOOLEAN NOT NULL, default false Verified contact
contact_value VARCHAR(500) Email, phone, etc.
frequency VARCHAR(50) instant, daily, weekly
timezone VARCHAR(50) User timezone
quiet_hours JSONB Do not disturb times
subscribed_at TIMESTAMPTZ Subscription date
unsubscribed_at TIMESTAMPTZ Unsubscription date

notification_templates#

Notification message templates.

Column Type Constraints Description
id UUID PK Template ID
slug VARCHAR(255) NOT NULL, UNIQUE Template identifier
name VARCHAR(255) NOT NULL Template name
channel ENUM NOT NULL Target channel
category ENUM NOT NULL Notification category
locale VARCHAR(10) NOT NULL, default 'en' Template language
subject VARCHAR(500) Email/push title
body TEXT Plain text body
body_html TEXT HTML body
variables JSONB Template variables
status ENUM NOT NULL, default 'draft' Status
version INTEGER NOT NULL, default 1 Template version

device_tokens#

Push notification device registrations.

Column Type Constraints Description
id UUID PK Token ID
user_id UUID NOT NULL User reference
token TEXT NOT NULL Device token
token_type VARCHAR(50) NOT NULL fcm, apns, web_push
platform ENUM NOT NULL ios, android, web
device_name VARCHAR(255) Device name
is_active BOOLEAN NOT NULL, default true Token active
failure_count INTEGER NOT NULL, default 0 Failed sends
last_used_at TIMESTAMPTZ Last notification

delivery_log#

Notification delivery history.

Column Type Constraints Description
id UUID PK Log entry ID
user_id UUID NOT NULL User reference
template_id UUID FK -> notification_templates Template used
channel ENUM NOT NULL Delivery channel
category ENUM NOT NULL Notification category
status ENUM NOT NULL, default 'pending' Delivery status
provider VARCHAR(100) Delivery provider
provider_message_id VARCHAR(255) Provider reference
error_message TEXT Error details
retry_count INTEGER NOT NULL, default 0 Retry attempts
sent_at TIMESTAMPTZ Send time
delivered_at TIMESTAMPTZ Delivery time
opened_at TIMESTAMPTZ Open time
clicked_at TIMESTAMPTZ Click time

User Preferences Service#

preferences#

User settings and preferences.

Column Type Constraints Description
id UUID PK Preferences ID
user_id UUID NOT NULL, UNIQUE User reference
theme VARCHAR(20) NOT NULL, default 'system' UI theme
locale VARCHAR(10) NOT NULL, default 'en' Language
timezone VARCHAR(50) Timezone
playback_speed DECIMAL(3,2) NOT NULL, default 1.0 Media speed
default_volume INTEGER NOT NULL, default 80 Volume 0-100
auto_play BOOLEAN NOT NULL, default true Auto-play media
captions_enabled BOOLEAN NOT NULL, default false Show captions
font_size VARCHAR(20) NOT NULL, default 'medium' Text size
reminder_enabled BOOLEAN NOT NULL, default true Enable reminders
reminder_time TIME Daily reminder time
onboarding_completed BOOLEAN NOT NULL, default false Onboarding done

favorites#

User saved/favorited content.

Column Type Constraints Description
id UUID PK Favorite ID
user_id UUID NOT NULL User reference
content_id UUID NOT NULL Content item ID
content_type VARCHAR(50) NOT NULL Content type
content_title VARCHAR(500) Content title
folder_name VARCHAR(100) Organization folder
notes TEXT User notes
created_at TIMESTAMPTZ NOT NULL Save time

history#

Content consumption history.

Column Type Constraints Description
id UUID PK History ID
user_id UUID NOT NULL User reference
content_id UUID NOT NULL Content item ID
content_type VARCHAR(50) NOT NULL Content type
progress_percent INTEGER NOT NULL, default 0 0-100 progress
position_ms INTEGER Playback position
content_duration_ms INTEGER Total duration
is_completed BOOLEAN NOT NULL, default false Completion flag
completed_at TIMESTAMPTZ Completion time
view_count INTEGER NOT NULL, default 1 View count
total_time_spent_ms INTEGER NOT NULL, default 0 Total time
last_accessed_at TIMESTAMPTZ NOT NULL Last access

achievements#

User badges and accomplishments.

Column Type Constraints Description
id UUID PK Achievement ID
user_id UUID NOT NULL User reference
achievement_type VARCHAR(100) NOT NULL Achievement type
achievement_name VARCHAR(255) NOT NULL Achievement name
description TEXT Description
icon_url VARCHAR(1000) Badge icon
progress_current INTEGER NOT NULL, default 0 Current progress
progress_required INTEGER NOT NULL Required progress
is_completed BOOLEAN NOT NULL, default false Completion flag
completed_at TIMESTAMPTZ Completion time
xp_reward INTEGER NOT NULL, default 0 XP earned

streaks#

Activity streak tracking.

Column Type Constraints Description
id UUID PK Streak ID
user_id UUID NOT NULL, UNIQUE User reference
streak_type ENUM NOT NULL, default 'daily' Streak type
current_streak INTEGER NOT NULL, default 0 Current streak
longest_streak INTEGER NOT NULL, default 0 Best streak
last_activity_date DATE Last activity
total_completions INTEGER NOT NULL, default 0 All-time completions
freeze_days_remaining INTEGER NOT NULL, default 0 Streak protections
daily_goal INTEGER Daily target
weekly_goal INTEGER Weekly target

Payment Orchestrator Service#

customers#

Billing customer profiles.

Column Type Constraints Description
id UUID PK Customer ID
user_id UUID NOT NULL, UNIQUE User reference
email VARCHAR(255) NOT NULL Billing email
name VARCHAR(255) Customer name
stripe_customer_id VARCHAR(100) UNIQUE Stripe customer
currency VARCHAR(3) NOT NULL, default 'USD' Default currency
balance DECIMAL(12,2) NOT NULL, default 0 Account balance
balance_currency VARCHAR(3) NOT NULL, default 'USD' Balance currency
locale VARCHAR(10) NOT NULL, default 'en' Locale
tax_id VARCHAR(50) Tax ID
billing_address JSONB Address details
tax_exempt BOOLEAN NOT NULL, default false Tax exemption

products#

Purchasable products.

Column Type Constraints Description
id UUID PK Product ID
slug VARCHAR(100) NOT NULL, UNIQUE Product identifier
name VARCHAR(255) NOT NULL Product name
description TEXT Description
stripe_product_id VARCHAR(100) UNIQUE Stripe product
status ENUM NOT NULL, default 'active' Status
features JSONB Feature list
metadata JSONB Additional data
sort_order INTEGER NOT NULL, default 0 Display order

prices#

Product pricing tiers.

Column Type Constraints Description
id UUID PK Price ID
product_id UUID NOT NULL, FK -> products Product
stripe_price_id VARCHAR(100) UNIQUE Stripe price
unit_amount INTEGER NOT NULL Amount in cents
currency VARCHAR(3) NOT NULL Currency code
billing_type ENUM NOT NULL one_time, recurring
interval ENUM month, year
interval_count INTEGER Billing periods
trial_period_days INTEGER Trial length
status ENUM NOT NULL, default 'active' Status

subscriptions#

Active customer subscriptions.

Column Type Constraints Description
id UUID PK Subscription ID
customer_id UUID NOT NULL, FK -> customers Customer
price_id UUID NOT NULL, FK -> prices Price tier
stripe_subscription_id VARCHAR(100) UNIQUE Stripe subscription
status ENUM NOT NULL active, canceled, etc.
current_period_start TIMESTAMPTZ NOT NULL Period start
current_period_end TIMESTAMPTZ NOT NULL Period end
cancel_at_period_end BOOLEAN NOT NULL, default false Will cancel
canceled_at TIMESTAMPTZ Cancellation time
trial_start TIMESTAMPTZ Trial start
trial_end TIMESTAMPTZ Trial end

invoices#

Billing invoices.

Column Type Constraints Description
id UUID PK Invoice ID
customer_id UUID NOT NULL, FK -> customers Customer
subscription_id UUID FK -> subscriptions Subscription
stripe_invoice_id VARCHAR(100) UNIQUE Stripe invoice
invoice_number VARCHAR(50) NOT NULL, UNIQUE Invoice number
status ENUM NOT NULL draft, open, paid, void
subtotal INTEGER NOT NULL Subtotal in cents
tax INTEGER NOT NULL, default 0 Tax amount
discount_amount INTEGER NOT NULL, default 0 Discount
total INTEGER NOT NULL Total in cents
amount_paid INTEGER NOT NULL, default 0 Paid amount
amount_due INTEGER NOT NULL Due amount
currency VARCHAR(3) NOT NULL Currency
due_date TIMESTAMPTZ Payment due
paid_at TIMESTAMPTZ Payment time

payments#

Payment transactions.

Column Type Constraints Description
id UUID PK Payment ID
customer_id UUID NOT NULL, FK -> customers Customer
invoice_id UUID FK -> invoices Related invoice
stripe_payment_intent_id VARCHAR(100) UNIQUE Stripe intent
amount INTEGER NOT NULL Amount in cents
currency VARCHAR(3) NOT NULL Currency
status ENUM NOT NULL pending, succeeded, failed
payment_method_type VARCHAR(50) card, bank_transfer
failure_code VARCHAR(100) Failure reason
failure_message TEXT Failure details

refunds#

Refund transactions.

Column Type Constraints Description
id UUID PK Refund ID
payment_id UUID NOT NULL, FK -> payments Original payment
stripe_refund_id VARCHAR(100) UNIQUE Stripe refund
amount INTEGER NOT NULL Refund amount
currency VARCHAR(3) NOT NULL Currency
status ENUM NOT NULL pending, succeeded, failed
reason ENUM duplicate, fraudulent, requested
description TEXT Refund reason

discounts#

Discount codes and coupons.

Column Type Constraints Description
id UUID PK Discount ID
code VARCHAR(50) NOT NULL, UNIQUE Coupon code
name VARCHAR(255) NOT NULL Discount name
type ENUM NOT NULL percent_off, amount_off
percent_off DECIMAL(5,2) Percentage discount
amount_off INTEGER Fixed amount off
currency VARCHAR(3) Amount currency
duration ENUM NOT NULL once, repeating, forever
duration_in_months INTEGER Repeating months
max_redemptions INTEGER Usage limit
redemption_count INTEGER NOT NULL, default 0 Times used
valid_from TIMESTAMPTZ Start date
valid_until TIMESTAMPTZ End date
minimum_amount INTEGER Minimum order
is_active BOOLEAN NOT NULL, default true Active status

entitlements#

User access entitlements.

Column Type Constraints Description
id UUID PK Entitlement ID
customer_id UUID NOT NULL, FK -> customers Customer
subscription_id UUID FK -> subscriptions Source subscription
entitlement_type VARCHAR(100) NOT NULL Entitlement type
resource_type VARCHAR(50) Resource type
resource_id UUID Specific resource
quantity INTEGER NOT NULL, default 1 Quantity
is_active BOOLEAN NOT NULL, default true Active status
valid_from TIMESTAMPTZ NOT NULL Start date
valid_until TIMESTAMPTZ Expiry date
source ENUM NOT NULL subscription, purchase, grant

Migration Naming Convention#

Migrations follow the pattern: YYYYMMDDSSSSSS_description.ts

Where:

  • YYYYMMDD - Date (year, month, day)
  • SSSSSS - 6-digit sequence number (service_id * 100000 + ordinal)
  • description - Snake_case description

Service sequence prefixes:

  • auth: 000001-099999
  • content: 100001-199999
  • conversation: 200001-299999
  • media: 300001-399999
  • notification: 400001-499999
  • user-preferences: 500001-599999
  • payment-orchestrator: 600001-699999

Check Constraints Summary#

All services implement comprehensive check constraints for data validation:

Service Tables Constraints Key Validations
auth 11 23 Email format, lowercase normalization, non-negative counts
content 13 59 URL-safe slugs, rating ranges 0-5, positive durations
conversation 5 35 Non-empty content, sequence ordering, token counts
media 5 57 Positive file sizes, GPS coordinate ranges, progress 0-100
notification 4 28 Valid locales, email formats, timezone patterns
user-preferences 5 32 Volume 0-100, playback speed range, streak consistency
payment-orchestrator 9 49 Currency codes, positive amounts, date ordering

Index Strategy#

All tables include:

  • Primary key indexes (automatic with PRIMARY KEY)
  • Foreign key indexes for join performance
  • Composite indexes for common query patterns
  • Partial indexes for status/active filters

Common indexed columns:

  • user_id - User lookups across all services
  • created_at - Time-range queries
  • status - Active record filtering
  • slug - Content URL lookups