# Data Model v1 (Consolidated Schema)

Status: approved
Owners: Architecture, Data, Compliance
Last updated: 2025-11-18
Related proposal: `openspec/changes/2025-11-17-data-model-v1/proposal.md`

## Core Entities

### User & Identity
- **User**: id, email (PII), password_hash, created_at, updated_at, deleted_at
- **Profile**: id, user_id (FK), display_name, pseudonym, pronouns, avatar_url, bio, health_journey (PHI), consent_flags, created_at, updated_at
- **Role**: id, name (member, moderator, admin), permissions
- **UserRole**: id, user_id (FK), role_id (FK)
- **Consent**: id, user_id (FK), consent_type, granted, granted_at, revoked_at

### Forum Domain
- **ForumCategory**: id, name, description, order, created_at
- **ForumThread**: id, category_id (FK), author_id (FK User), title, pinned, locked, created_at, updated_at
- **ForumPost**: id, thread_id (FK), author_id (FK User), parent_post_id (FK), content (may contain PHI), deleted_at, created_at, updated_at
- **ForumReaction**: id, post_id (FK), user_id (FK), emoji_code, created_at
- **ForumReport**: id, post_id (FK), reporter_id (FK User), reason, status, moderator_notes, resolved_at, created_at

### Content Domain
- **BlogPost**: id, author_id (FK User), title, slug, content, published_at, created_at, updated_at
- **Resource**: id, title, slug, content, access_tier (public/members), tags, created_at, updated_at

### Media & Tributes
- **PodcastEpisode**: id, title, description, audio_url, duration, published_at, created_at
- **TributeEntry**: id, author_id (FK User), subject_name, memorial_text (may contain PHI), published, created_at, updated_at

### Commerce
- **MerchProduct**: id, name, description, price, stock_count, created_at, updated_at
- **Order**: id, user_id (FK), total, status, shipping_address (PII), created_at, updated_at

## Data Classification

- **Public**: ForumCategory, PodcastEpisode, Resource (public tier), MerchProduct, BlogPost (published)
- **PII**: User.email, Profile.display_name, Order.shipping_address, Profile.avatar_url
- **PHI**: Profile.health_journey, ForumPost.content (context-dependent), TributeEntry.memorial_text (context-dependent)

## Relationships & Constraints

- User → Profile (1:1, cascade delete)
- User → ForumPost (1:N, soft-delete user → anonymize posts)
- User → ForumThread (1:N)
- ForumCategory → ForumThread (1:N)
- ForumThread → ForumPost (1:N, cascade delete)
- ForumPost → ForumReaction (1:N, cascade delete)
- ForumPost → ForumReport (1:N)
- User → BlogPost (1:N)
- User → TributeEntry (1:N)
- User → Order (1:N)

## Indexing Strategy

- User: email (unique), created_at
- Profile: user_id (unique FK)
- ForumThread: category_id, author_id, created_at
- ForumPost: thread_id, author_id, created_at
- BlogPost: slug (unique), author_id, published_at
- Resource: slug (unique), access_tier, tags (GIN/array)
- Order: user_id, created_at

## Retention & Soft-Delete

- **User**: soft-delete (90-day window); anonymize posts on hard-delete
- **ForumPost**: soft-delete (90-day window); replace author with "[deleted]" on user delete
- **BlogPost, TributeEntry**: indefinite retention unless user requests DSR delete
- **Order**: 7-year retention (tax compliance), then hard-delete

## Migrations

- Versioned migrations (Alembic, Flyway, or similar)
- Idempotent scripts for rollback safety
- Seed data: initial categories, default consents, sample resources

## Security & Compliance

- Encryption at rest: PII/PHI fields encrypted at database or app level
- Access controls: RBAC at API layer; RLS for multi-tenancy if needed
- Audit logging: all PHI/PII mutations logged (excluding content)
- DSR support: export and delete operations mapped to all entities