# Schema — MNW Server

PostgreSQL database. Migrations live under `migrations/`, numbered and auto-applied on boot via sqlx; the directory is the source of truth. Extension: `pg_trgm` (trigram fuzzy search).

## Domain Map

| Domain | Tables | Purpose |
|--------|--------|---------|
| Users & Auth | 6 | Accounts, passkeys, sessions, 2FA, login tokens |
| Projects & Content | 8 | Creator projects, items, versions, chapters, insertions, sections, bundles |
| Tags & Taxonomy | 4 | Hierarchical tags, item tagging, platform labels |
| Commerce | 7 | Transactions, subscriptions, promo codes, license keys |
| Creator Tiers | 2 | Platform subscription tiers (Basic/Small/Big/Everything) |
| Email & Mailing | 4 | Mailing lists, subscribers, suppressions, signups |
| Social | 3 | Follows, blog posts, custom links |
| Collections | 2 | User-curated item lists |
| SyncKit | 5 | Cloud sync apps, devices, keys, changelog, blobs |
| Git | 6 | Repos, SSH keys, issues, comments, labels |
| OTA Updates | 4 | Releases, artifacts, build configs, build runs |
| Custom Domains | 1 | Creator vanity domains |
| OAuth | 1 | PKCE authorization codes |
| Waitlist & Invites | 3 | Creator waves, waitlist, invite codes |
| Admin | 1 | Abuse reports |
| Media | 1 | User media library (images for markdown) |
| Import | 1 | Bulk import jobs (Patreon, Ko-fi, Gumroad) |
| Sessions | 1 | HTTP sessions (tower-sessions) |

---

## Users & Authentication

### users
Core accounts. Every user has one row; creator features are gated by `can_create_projects`.

| Column | Type | Notes |
|--------|------|-------|
| id | UUID PK | |
| username | TEXT UNIQUE | URL slug (`/@username`) |
| email | TEXT UNIQUE | |
| display_name | TEXT | |
| password_hash | TEXT | argon2 |
| email_verified | BOOL | |
| totp_secret / totp_enabled | TEXT / BOOL | 2FA |
| failed_login_attempts | INT | Resets on success |
| locked_until | TIMESTAMPTZ | Lockout after 5 failures |
| stripe_account_id | TEXT | Stripe Connect account |
| stripe_onboarding_complete | BOOL | |
| can_create_projects | BOOL | Creator gate |
| creator_tier | TEXT | 'basic', 'small_files', 'big_files', 'everything' |
| storage_used_bytes | BIGINT | Computed from versions + insertions |
| max_file_override_bytes | BIGINT | Per-user override |
| grandfathered_until | TIMESTAMPTZ | Grace period for existing creators |
| suspended_at | TIMESTAMPTZ | Null = active |
| notify_sale / notify_follower / notify_release / notify_issues | BOOL | Email prefs |

**Indexes:** email, username, email_verified, stripe_account — all B-tree.
**Trigger:** `update_users_updated_at` — auto-sets `updated_at`.

### user_passkeys
WebAuthn credentials for passwordless login.

| Column | Type | Notes |
|--------|------|-------|
| id | UUID PK | |
| user_id | UUID FK → users CASCADE | |
| credential_json | JSONB | WebAuthn credential blob |
| credential_id | BYTEA UNIQUE | Lookup key |
| name | TEXT | User-assigned label |

### login_tokens
Single-use email login links.

- **FK:** user_id → users CASCADE
- **Key columns:** token_hash, expires_at, used_at
- **Indexed on:** user_id, expires_at

### user_sessions
Active login sessions. Tracks last activity, UA, IP for "active sessions" UI.

- **FK:** user_id → users CASCADE
- **Indexed on:** user_id

### backup_codes
2FA recovery codes (hashed). Marked with `used_at` when consumed.

- **FK:** user_id → users CASCADE

### tower_sessions.session
HTTP session storage (tower-sessions-sqlx-store). Schema `tower_sessions`, PK is TEXT `id`, stores BYTEA `data` with `expiry_date`.

---

## Projects & Content

### projects
Creator projects — music releases, software, podcasts, books, etc.

| Column | Type | Notes |
|--------|------|-------|
| id | UUID PK | |
| user_id | UUID FK → users CASCADE | |
| slug | TEXT | URL path segment |
| title | TEXT | |
| project_type | TEXT | 'music', 'software', 'podcast', etc. |
| is_public | BOOL | |
| category_id | UUID FK → project_categories SET NULL | |
| mt_community_id | UUID | Links to Multithreaded forum |
| features | TEXT[] | Feature flags per project |

**Constraint:** UNIQUE(user_id, slug).
**Indexes:** user_id, is_public, category_id, title trigram (GIN), description trigram (GIN).
**Trigger:** `update_projects_updated_at`.

### items
Products/content within projects. The central commerce entity — holds pricing, audio, text, licensing.

| Column | Type | Notes |
|--------|------|-------|
| id | UUID PK | |
| project_id | UUID FK → projects CASCADE | |
| title / slug | TEXT | UNIQUE(project_id, slug) |
| item_type | TEXT | 'article', 'audio', 'download', 'video', etc. |
| price_cents | INT | 0 = free. CHECK >= 0 |
| pwyw_enabled | BOOL | Pay what you want |
| pwyw_min_cents | INT | Floor for PWYW |
| body / word_count / reading_time_minutes | TEXT / INT / INT | Text content |
| audio_url / audio_s3_key / duration_seconds | TEXT / TEXT / FLOAT | Audio content |
| video_s3_key / video_duration_seconds | TEXT / FLOAT | Video content |
| enable_license_keys | BOOL | DRM gate |
| custom_license_text | TEXT | License shown on download |
| sales_count / play_count / download_count | INT | Denormalized counters |
| web_only | BOOL | Publish without emailing mailing-list subscribers |

**Indexes:** project_id, is_public, sales_count, tsvector search (title+description+body), title trigram, desc trigram, (project_id, slug).
**Trigger:** `update_items_updated_at`.

### versions
Downloadable file versions per item (software releases, audio stems).

- **FK:** item_id → items CASCADE
- **Key columns:** version_number, file_url, s3_key, file_size_bytes, is_current, download_count
- **Constraint:** UNIQUE WHERE is_current = true (only one current version per item)

### chapters
Audio/podcast chapter markers. Sorted by `start_seconds`.

- **FK:** item_id → items CASCADE

### content_insertions
Reusable audio clips (ads, intros, outros) uploaded by creators.

- **FK:** user_id → users CASCADE
- **Key columns:** title, media_type, storage_key, duration_ms, file_size

### content_insertion_placements
Where insertions attach to items. Position is 'pre_roll', 'mid_roll', or 'post_roll'.

- **FK:** item_id → items CASCADE, insertion_id → content_insertions CASCADE
- **Constraint:** UNIQUE(item_id, insertion_id, position, offset_ms)

### item_sections
Tabbed content blocks within items (e.g., "Ingredients", "Instructions", "Changelog").

- **FK:** item_id → items CASCADE
- **Constraint:** UNIQUE(item_id, slug)

### bundle_items
Associates items into bundle-type items. CHECK(bundle_id != item_id) prevents self-reference.

- **PK:** (bundle_id, item_id)
- **FK:** both → items CASCADE

---

## Tags & Taxonomy

### tags
Hierarchical tag system. `path` uses dot-notation for materialized paths (e.g., `music.electronic.ambient`).

| Column | Type | Notes |
|--------|------|-------|
| id | UUID PK | |
| parent_id | UUID FK → tags CASCADE | Self-referential hierarchy |
| name | TEXT | Display name |
| slug | TEXT UNIQUE | URL-safe |
| path | TEXT | Materialized path (dot-notation) |

**Indexes:** parent_id, slug, name trigram (GIN), path (prefix queries).

### item_tags
Many-to-many. `is_primary` marks the main tag for an item (UNIQUE WHERE is_primary).

- **PK:** (item_id, tag_id), both CASCADE

### labels
Platform-curated promises (e.g., "DRM-free", "Lossless audio"). Includes definition, examples, and non-examples.

- **Key columns:** slug UNIQUE, display_name, definition, examples, nonexamples

### project_labels
Projects adopt platform labels (creator commits to the promise).

- **PK:** (project_id, label_id), both CASCADE

---

## Commerce & Payments

### transactions
One-off purchases. Status lifecycle: pending → completed / failed / refunded.

| Column | Type | Notes |
|--------|------|-------|
| id | UUID PK | |
| buyer_id | UUID FK → users CASCADE | |
| seller_id | UUID FK → users SET NULL | Preserved if seller deletes account |
| item_id | UUID FK → items SET NULL | Preserved if item deletes |
| amount_cents | INT | CHECK >= 0 |
| platform_fee_cents | INT | Always 0 (0% fee model) |
| stripe_checkout_session_id | TEXT | Links to Stripe |
| status | TEXT | 'pending', 'completed', 'failed', 'refunded' |
| item_title / seller_username | TEXT | Denormalized for receipt display |

**Constraint:** UNIQUE(buyer_id, item_id) WHERE status = 'completed' — prevents double purchase.
**Indexes:** buyer_id, seller_id, item_id, status, stripe_session.

### subscription_tiers
Per-project recurring tiers. Each tier has a Stripe product+price.

- **FK:** project_id → projects CASCADE
- **Key columns:** name, price_cents, stripe_product_id, stripe_price_id, is_active

### subscriptions
Active subscriber records. UNIQUE(subscriber_id, project_id) WHERE status = 'active'.

- **FK:** subscriber_id → users CASCADE, tier_id → subscription_tiers RESTRICT, project_id → projects CASCADE
- **Note:** tier_id uses RESTRICT — cannot delete a tier that has active subscribers

### subscription_events
Webhook events from Stripe. Keyed by `stripe_event_id` (UNIQUE) for idempotency.

- **FK:** subscription_id → subscriptions SET NULL

### promo_codes
Unified discount/free-access/free-trial codes. Purpose-specific CHECK constraints enforce valid field combinations.

- **FK:** creator_id → users CASCADE; item_id, project_id, tier_id all → SET NULL on target delete
- **Key columns:** code, code_purpose, discount_type, discount_value, trial_days, max_uses, use_count
- **Constraint:** UNIQUE(creator_id, code)

### license_keys
DRM keys for download-limited items. Tracks activation count vs max_activations.

- **FK:** item_id → items CASCADE, owner_id → users CASCADE, transaction_id → transactions SET NULL
- **Key columns:** key_code UNIQUE, max_activations, activation_count, revoked_at

### license_activations
Per-machine activations. UNIQUE(license_key_id, machine_id) prevents double-activate on same machine.

- **FK:** license_key_id → license_keys CASCADE

---

## Creator Tiers

### creator_subscriptions
Platform subscription for creators (Basic $16, Small Files $24, Big Files $36, Everything $60). One row per creator.

- **FK:** user_id → users CASCADE (UNIQUE)
- **Key columns:** tier, status, stripe_subscription_id UNIQUE, grace_enforced_at

### fan_plus_subscriptions
Fan+ consumer subscription (discoverability + collection features). One row per user.

- **FK:** user_id → users CASCADE (UNIQUE)
- **Key columns:** status, stripe_subscription_id UNIQUE

---

## Email & Mailing Lists

### mailing_lists
Per-project lists. Types: 'content' (new releases), 'devlog' (development updates), 'patches' (software patches).

- **FK:** project_id → projects CASCADE
- **Constraint:** UNIQUE(project_id, list_type)

### mailing_list_subscribers
Supports both registered users and email-only subscribers. CHECK(user_id NOT NULL OR email NOT NULL).

- **FK:** list_id → mailing_lists CASCADE, user_id → users CASCADE (nullable)
- **Constraints:** UNIQUE(list_id, user_id), UNIQUE(list_id, email) WHERE email NOT NULL

### email_suppressions
Hard bounces and spam complaints. Prevents sending to known-bad addresses.

- **Key columns:** email UNIQUE (case-insensitive), reason ('HardBounce', 'SpamComplaint')

### email_signups
Landing page "notify me" signups (pre-launch or feature waitlist).

- **Key columns:** email UNIQUE, source

---

## Social & Community

### follows
Polymorphic follow system — users can follow users, projects, or tags.

- **FK:** follower_id → users CASCADE
- **Key columns:** target_type ('user', 'project', 'tag'), target_id
- **Constraint:** UNIQUE(follower_id, target_type, target_id)

### blog_posts
Project-level blog posts (devlogs, announcements). Markdown source + rendered HTML.

- **FK:** project_id → projects CASCADE, author_id → users (no cascade)
- **Key columns:** title, slug, body_markdown, body_html, published_at, mt_thread_id
- **Constraint:** UNIQUE(project_id, slug)

### custom_links
Creator profile links (social, merch, website). Ordered by sort_order.

- **FK:** user_id → users CASCADE
- **Trigger:** `update_custom_links_updated_at`

---

## Collections

### collections
User-curated lists (playlists, reading lists, favorites).

- **FK:** user_id → users CASCADE
- **Constraint:** UNIQUE(user_id, slug)

### collection_items
Items in collections. Ordered by `position`.

- **PK:** (collection_id, item_id), both CASCADE

---

## SyncKit

### sync_apps
Registered SyncKit applications (GO, BB, AF, third-party).

- **FK:** creator_id → users CASCADE; project_id → projects SET NULL, item_id → items SET NULL
- **Key columns:** name, api_key UNIQUE, slug, is_active, redirect_uris TEXT[]

### sync_devices
User devices per app. Last-seen tracking for device management UI.

- **FK:** app_id → sync_apps CASCADE, user_id → users CASCADE
- **Constraint:** UNIQUE(app_id, user_id, device_name)

### sync_keys
E2E encryption keys per app/user pair. Key rotation via `key_version`.

- **PK:** (app_id, user_id), both CASCADE
- **Key columns:** key_version, encrypted_key

### sync_log
Changelog of data operations. Sequential `seq` (BIGSERIAL) enables cursor-based pull.

- **FK:** app_id, user_id, device_id — all CASCADE
- **Key columns:** table_name, operation ('INSERT'/'UPDATE'/'DELETE'), row_id, data (JSONB), client_timestamp
- **Index:** (app_id, user_id, seq) — the primary pull query path

### sync_blobs
File blobs for SyncKit (content-hashed dedup). Used when `sync_files=true` on a VFS.

- **FK:** app_id, user_id — both CASCADE
- **Constraint:** UNIQUE(app_id, user_id, hash)
- **Key columns:** hash, s3_key, size_bytes

---

## Git Integration

### git_repos
Bare git repositories. Displayed via the `/source/` browser (G1, git2-based).

- **FK:** user_id → users CASCADE, project_id → projects SET NULL
- **Constraint:** UNIQUE(user_id, name)

### ssh_keys
SSH public keys for git push access.

- **FK:** user_id → users CASCADE
- **Constraint:** UNIQUE(user_id, fingerprint)

### issues
Lightweight issue tracker per repo. Sequential `number` per repo.

- **FK:** repo_id → git_repos CASCADE, author_user_id → users CASCADE
- **Constraint:** UNIQUE(repo_id, number)
- **Indexed on:** (repo_id, status), author

### issue_comments
- **FK:** issue_id → issues CASCADE, author_user_id → users CASCADE

### issue_labels
Per-repo label definitions with color.

- **FK:** repo_id → git_repos CASCADE
- **Constraint:** UNIQUE(repo_id, name)

### issue_label_assignments
- **PK:** (issue_id, label_id), both CASCADE

---

## OTA Updates

### ota_releases
App versions published for over-the-air updates (Tauri-compatible protocol).

- **FK:** app_id → sync_apps CASCADE
- **Constraint:** UNIQUE(app_id, version)

### ota_artifacts
Binary artifacts per release. One per target/arch combination.

- **FK:** release_id → ota_releases CASCADE
- **Constraint:** UNIQUE(release_id, target, arch)
- **Key columns:** s3_key, file_size

### ota_build_configs
Automated build configuration per app.

- **FK:** app_id → sync_apps CASCADE (UNIQUE), repo_id → git_repos CASCADE
- **Key columns:** build_command, artifact_path, signing_key_path, targets TEXT[], enabled

### ota_builds
Individual build runs. Status lifecycle: pending → building → succeeded / failed.

- **FK:** config_id → ota_build_configs CASCADE, release_id → ota_releases SET NULL

---

## Remaining Tables

### custom_domains
Creator vanity domains. Verified via DNS TXT record.

- **FK:** user_id → users CASCADE
- **Key columns:** domain UNIQUE, verified, verification_token

### oauth_authorization_codes
OAuth PKCE codes for SyncKit SDK clients. Short-lived (5 min), single-use.

- **FK:** app_id → sync_apps CASCADE, user_id → users CASCADE
- **Key columns:** code UNIQUE, code_challenge, redirect_uri, expires_at, used_at

### creator_waves / creator_waitlist / invite_codes
Creator onboarding pipeline: waves (batches), waitlist (applications), invite codes (referrals).

- creator_waves: wave_number UNIQUE
- creator_waitlist: user_id → users CASCADE (UNIQUE), wave_id → creator_waves SET NULL
- invite_codes: creator_id → users CASCADE, code UNIQUE

### reports
User abuse reports. Status: open → resolved/dismissed.

### media_files
User media library for embedding in markdown content. S3-backed.

- **FK:** user_id → users CASCADE
- **Constraint:** UNIQUE(user_id, folder, filename)

### import_jobs
Bulk import from external platforms (Patreon, Ko-fi, Gumroad). Tracks progress rows.

### project_categories
Taxonomy for project categorization. Referenced by projects.category_id.

---

## Cascade Summary

**CASCADE (delete parent → delete children):** Most FK relationships. Deleting a user cascades to all their projects, items, content, sync data, sessions, keys, etc.

**SET NULL (delete parent → null the FK):** Used where the child record should survive: transactions keep seller/item info (denormalized title/username), sync_apps keep project/item links optional, git repos keep project association optional.

**RESTRICT (prevent parent delete):** subscription_tiers — cannot delete a tier that has active subscribers.

## Search Infrastructure

| Target | Index Type | Columns |
|--------|-----------|---------|
| items | tsvector GIN | title + description + body |
| items | trigram GIN | title, description (separate) |
| projects | trigram GIN | title, description (separate) |
| tags | trigram GIN | name |

All trigram indexes use `gin_trgm_ops` from the `pg_trgm` extension.

## Key Paths

- `migrations/` — numbered SQL files, applied in order
- `src/db/` — query functions grouped by domain
- `src/models/` — Rust structs matching table schemas