| 1 |
# Schema: MNW Server |
| 2 |
|
| 3 |
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). |
| 4 |
|
| 5 |
## Domain Map |
| 6 |
|
| 7 |
|
| 8 |
|
| 9 |
| Users & Auth | 6 | Accounts, passkeys, sessions, 2FA, login tokens | |
| 10 |
| Projects & Content | 8 | Creator projects, items, versions, chapters, insertions, sections, bundles | |
| 11 |
| Tags & Taxonomy | 4 | Hierarchical tags, item tagging, platform labels | |
| 12 |
| Commerce | 7 | Transactions, subscriptions, promo codes, license keys | |
| 13 |
| Creator Tiers | 2 | Platform subscription tiers (Basic/Small/Big/Everything) | |
| 14 |
| Email & Mailing | 4 | Mailing lists, subscribers, suppressions, signups | |
| 15 |
| Social | 3 | Follows, blog posts, custom links | |
| 16 |
| Collections | 2 | User-curated item lists | |
| 17 |
| SyncKit | 5 | Cloud sync apps, devices, keys, changelog, blobs | |
| 18 |
| Git | 6 | Repos, SSH keys, issues, comments, labels | |
| 19 |
| OTA Updates | 4 | Releases, artifacts, build configs, build runs | |
| 20 |
| Custom Domains | 1 | Creator vanity domains | |
| 21 |
| OAuth | 1 | PKCE authorization codes | |
| 22 |
| Waitlist & Invites | 3 | Creator waves, waitlist, invite codes | |
| 23 |
| Admin | 1 | Abuse reports | |
| 24 |
| Media | 1 | User media library (images for markdown) | |
| 25 |
| Import | 1 | Bulk import jobs (Patreon, Ko-fi, Gumroad) | |
| 26 |
| Sessions | 1 | HTTP sessions (tower-sessions) | |
| 27 |
|
| 28 |
--- |
| 29 |
|
| 30 |
## Users & Authentication |
| 31 |
|
| 32 |
### users |
| 33 |
Core accounts. Every user has one row; creator features are gated by `can_create_projects`. |
| 34 |
|
| 35 |
|
| 36 |
|
| 37 |
| id | UUID PK | | |
| 38 |
| username | TEXT UNIQUE | URL slug (`/@username`) | |
| 39 |
| email | TEXT UNIQUE | | |
| 40 |
| display_name | TEXT | | |
| 41 |
| password_hash | TEXT | argon2 | |
| 42 |
| email_verified | BOOL | | |
| 43 |
| totp_secret / totp_enabled | TEXT / BOOL | 2FA | |
| 44 |
| failed_login_attempts | INT | Resets on success | |
| 45 |
| locked_until | TIMESTAMPTZ | Lockout after 5 failures | |
| 46 |
| stripe_account_id | TEXT | Stripe Connect account | |
| 47 |
| stripe_onboarding_complete | BOOL | | |
| 48 |
| can_create_projects | BOOL | Creator gate | |
| 49 |
| creator_tier | TEXT | 'basic', 'small_files', 'big_files', 'everything' | |
| 50 |
| storage_used_bytes | BIGINT | Computed from versions + insertions | |
| 51 |
| max_file_override_bytes | BIGINT | Per-user override | |
| 52 |
| grandfathered_until | TIMESTAMPTZ | Grace period for existing creators | |
| 53 |
| suspended_at | TIMESTAMPTZ | Null = active | |
| 54 |
| notify_sale / notify_follower / notify_release / notify_issues | BOOL | Email prefs | |
| 55 |
|
| 56 |
**Indexes:** email, username, email_verified, stripe_account, all B-tree. |
| 57 |
**Trigger:** `update_users_updated_at`, auto-sets `updated_at`. |
| 58 |
|
| 59 |
### user_passkeys |
| 60 |
WebAuthn credentials for passwordless login. |
| 61 |
|
| 62 |
|
| 63 |
|
| 64 |
| id | UUID PK | | |
| 65 |
| user_id | UUID FK → users CASCADE | | |
| 66 |
| credential_json | JSONB | WebAuthn credential blob | |
| 67 |
| credential_id | BYTEA UNIQUE | Lookup key | |
| 68 |
| name | TEXT | User-assigned label | |
| 69 |
|
| 70 |
### login_tokens |
| 71 |
Single-use email login links. |
| 72 |
|
| 73 |
- **FK:** user_id → users CASCADE |
| 74 |
- **Key columns:** token_hash, expires_at, used_at |
| 75 |
- **Indexed on:** user_id, expires_at |
| 76 |
|
| 77 |
### user_sessions |
| 78 |
Active login sessions. Tracks last activity, UA, IP for "active sessions" UI. |
| 79 |
|
| 80 |
- **FK:** user_id → users CASCADE |
| 81 |
- **Indexed on:** user_id |
| 82 |
|
| 83 |
### backup_codes |
| 84 |
2FA recovery codes (hashed). Marked with `used_at` when consumed. |
| 85 |
|
| 86 |
- **FK:** user_id → users CASCADE |
| 87 |
|
| 88 |
### tower_sessions.session |
| 89 |
HTTP session storage (tower-sessions-sqlx-store). Schema `tower_sessions`, PK is TEXT `id`, stores BYTEA `data` with `expiry_date`. |
| 90 |
|
| 91 |
--- |
| 92 |
|
| 93 |
## Projects & Content |
| 94 |
|
| 95 |
### projects |
| 96 |
Creator projects, music releases, software, podcasts, books, etc. |
| 97 |
|
| 98 |
|
| 99 |
|
| 100 |
| id | UUID PK | | |
| 101 |
| user_id | UUID FK → users CASCADE | | |
| 102 |
| slug | TEXT | URL path segment | |
| 103 |
| title | TEXT | | |
| 104 |
| project_type | TEXT | 'music', 'software', 'podcast', etc. | |
| 105 |
| is_public | BOOL | | |
| 106 |
| category_id | UUID FK → project_categories SET NULL | | |
| 107 |
| mt_community_id | UUID | Links to Multithreaded forum | |
| 108 |
| features | TEXT[] | Feature flags per project | |
| 109 |
|
| 110 |
**Constraint:** UNIQUE(user_id, slug). |
| 111 |
**Indexes:** user_id, is_public, category_id, title trigram (GIN), description trigram (GIN). |
| 112 |
**Trigger:** `update_projects_updated_at`. |
| 113 |
|
| 114 |
### items |
| 115 |
Products/content within projects. The central commerce entity, holds pricing, audio, text, licensing. |
| 116 |
|
| 117 |
|
| 118 |
|
| 119 |
| id | UUID PK | | |
| 120 |
| project_id | UUID FK → projects CASCADE | | |
| 121 |
| title / slug | TEXT | UNIQUE(project_id, slug) | |
| 122 |
| item_type | TEXT | 'article', 'audio', 'download', 'video', etc. | |
| 123 |
| price_cents | INT | 0 = free. CHECK >= 0 | |
| 124 |
| pwyw_enabled | BOOL | Pay what you want | |
| 125 |
| pwyw_min_cents | INT | Floor for PWYW | |
| 126 |
| body / word_count / reading_time_minutes | TEXT / INT / INT | Text content | |
| 127 |
| audio_url / audio_s3_key / duration_seconds | TEXT / TEXT / FLOAT | Audio content | |
| 128 |
| video_s3_key / video_duration_seconds | TEXT / FLOAT | Video content | |
| 129 |
| enable_license_keys | BOOL | DRM gate | |
| 130 |
| custom_license_text | TEXT | License shown on download | |
| 131 |
| sales_count / play_count / download_count | INT | Denormalized counters | |
| 132 |
| web_only | BOOL | Publish without emailing mailing-list subscribers | |
| 133 |
|
| 134 |
**Indexes:** project_id, is_public, sales_count, tsvector search (title+description+body), title trigram, desc trigram, (project_id, slug). |
| 135 |
**Trigger:** `update_items_updated_at`. |
| 136 |
|
| 137 |
### versions |
| 138 |
Downloadable file versions per item (software releases, audio stems). |
| 139 |
|
| 140 |
- **FK:** item_id → items CASCADE |
| 141 |
- **Key columns:** version_number, file_url, s3_key, file_size_bytes, is_current, download_count |
| 142 |
- **Constraint:** UNIQUE WHERE is_current = true (only one current version per item) |
| 143 |
|
| 144 |
### chapters |
| 145 |
Audio/podcast chapter markers. Sorted by `start_seconds`. |
| 146 |
|
| 147 |
- **FK:** item_id → items CASCADE |
| 148 |
|
| 149 |
### content_insertions |
| 150 |
Reusable audio or video clips (ads, intros, outros) uploaded by creators. |
| 151 |
`media_type` is `audio` or `video`, derived from the confirmed MIME. |
| 152 |
|
| 153 |
- **FK:** user_id → users CASCADE |
| 154 |
- **Key columns:** title, media_type, storage_key, duration_ms, file_size |
| 155 |
|
| 156 |
### content_insertion_placements |
| 157 |
Where insertions attach to items. Position is 'pre_roll', 'mid_roll', or 'post_roll'. |
| 158 |
|
| 159 |
- **FK:** item_id → items CASCADE, insertion_id → content_insertions CASCADE |
| 160 |
- **Constraint:** UNIQUE(item_id, insertion_id, position, offset_ms) |
| 161 |
|
| 162 |
### item_sections |
| 163 |
Tabbed content blocks within items (e.g., "Ingredients", "Instructions", "Changelog"). |
| 164 |
|
| 165 |
- **FK:** item_id → items CASCADE |
| 166 |
- **Constraint:** UNIQUE(item_id, slug) |
| 167 |
|
| 168 |
### bundle_items |
| 169 |
Associates items into bundle-type items. CHECK(bundle_id != item_id) prevents self-reference. |
| 170 |
|
| 171 |
- **PK:** (bundle_id, item_id) |
| 172 |
- **FK:** both → items CASCADE |
| 173 |
|
| 174 |
--- |
| 175 |
|
| 176 |
## Tags & Taxonomy |
| 177 |
|
| 178 |
### tags |
| 179 |
Hierarchical tag system. `path` uses dot-notation for materialized paths (e.g., `music.electronic.ambient`). |
| 180 |
|
| 181 |
|
| 182 |
|
| 183 |
| id | UUID PK | | |
| 184 |
| parent_id | UUID FK → tags CASCADE | Self-referential hierarchy | |
| 185 |
| name | TEXT | Display name | |
| 186 |
| slug | TEXT UNIQUE | URL-safe | |
| 187 |
| path | TEXT | Materialized path (dot-notation) | |
| 188 |
|
| 189 |
**Indexes:** parent_id, slug, name trigram (GIN), path (prefix queries). |
| 190 |
|
| 191 |
### item_tags |
| 192 |
Many-to-many. `is_primary` marks the main tag for an item (UNIQUE WHERE is_primary). |
| 193 |
|
| 194 |
- **PK:** (item_id, tag_id), both CASCADE |
| 195 |
|
| 196 |
### labels |
| 197 |
Platform-curated promises (e.g., "DRM-free", "Lossless audio"). Includes definition, examples, and non-examples. |
| 198 |
|
| 199 |
- **Key columns:** slug UNIQUE, display_name, definition, examples, nonexamples |
| 200 |
|
| 201 |
### project_labels |
| 202 |
Projects adopt platform labels (creator commits to the promise). |
| 203 |
|
| 204 |
- **PK:** (project_id, label_id), both CASCADE |
| 205 |
|
| 206 |
--- |
| 207 |
|
| 208 |
## Commerce & Payments |
| 209 |
|
| 210 |
### transactions |
| 211 |
One-off purchases. Status lifecycle: pending → completed / failed / refunded. |
| 212 |
|
| 213 |
|
| 214 |
|
| 215 |
| id | UUID PK | | |
| 216 |
| buyer_id | UUID FK → users CASCADE | | |
| 217 |
| seller_id | UUID FK → users SET NULL | Preserved if seller deletes account | |
| 218 |
| item_id | UUID FK → items SET NULL | Preserved if item deletes | |
| 219 |
| amount_cents | INT | CHECK >= 0 | |
| 220 |
| platform_fee_cents | INT | Always 0 (0% fee model) | |
| 221 |
| stripe_checkout_session_id | TEXT | Links to Stripe | |
| 222 |
| status | TEXT | 'pending', 'completed', 'failed', 'refunded' | |
| 223 |
| item_title / seller_username | TEXT | Denormalized for receipt display | |
| 224 |
|
| 225 |
**Constraint:** UNIQUE(buyer_id, item_id) WHERE status = 'completed', prevents double purchase. |
| 226 |
**Indexes:** buyer_id, seller_id, item_id, status, stripe_session. |
| 227 |
|
| 228 |
### subscription_tiers |
| 229 |
Per-project recurring tiers. Each tier has a Stripe product+price. |
| 230 |
|
| 231 |
- **FK:** project_id → projects CASCADE |
| 232 |
- **Key columns:** name, price_cents, stripe_product_id, stripe_price_id, is_active |
| 233 |
|
| 234 |
### subscriptions |
| 235 |
Active subscriber records. UNIQUE(subscriber_id, project_id) WHERE status = 'active'. |
| 236 |
|
| 237 |
- **FK:** subscriber_id → users CASCADE, tier_id → subscription_tiers RESTRICT, project_id → projects CASCADE |
| 238 |
- **Note:** tier_id uses RESTRICT, cannot delete a tier that has active subscribers |
| 239 |
|
| 240 |
### subscription_events |
| 241 |
Webhook events from Stripe. Keyed by `stripe_event_id` (UNIQUE) for idempotency. |
| 242 |
|
| 243 |
- **FK:** subscription_id → subscriptions SET NULL |
| 244 |
|
| 245 |
### promo_codes |
| 246 |
Unified discount/free-access/free-trial codes. Purpose-specific CHECK constraints enforce valid field combinations. |
| 247 |
|
| 248 |
- **FK:** creator_id → users CASCADE; item_id, project_id, tier_id all → SET NULL on target delete |
| 249 |
- **Key columns:** code, code_purpose, discount_type, discount_value, trial_days, max_uses, use_count |
| 250 |
- **Constraint:** UNIQUE(creator_id, code) |
| 251 |
|
| 252 |
### license_keys |
| 253 |
DRM keys for download-limited items. Tracks activation count vs max_activations. |
| 254 |
|
| 255 |
- **FK:** item_id → items CASCADE, owner_id → users CASCADE, transaction_id → transactions SET NULL |
| 256 |
- **Key columns:** key_code UNIQUE, max_activations, activation_count, revoked_at |
| 257 |
|
| 258 |
### license_activations |
| 259 |
Per-machine activations. UNIQUE(license_key_id, machine_id) prevents double-activate on same machine. |
| 260 |
|
| 261 |
- **FK:** license_key_id → license_keys CASCADE |
| 262 |
|
| 263 |
--- |
| 264 |
|
| 265 |
## Creator Tiers |
| 266 |
|
| 267 |
### creator_subscriptions |
| 268 |
Platform subscription for creators (Basic $16, Small Files $24, Big Files $36, Everything $60). One row per creator. |
| 269 |
|
| 270 |
- **FK:** user_id → users CASCADE (UNIQUE) |
| 271 |
- **Key columns:** tier, status, stripe_subscription_id UNIQUE, grace_enforced_at |
| 272 |
|
| 273 |
### fan_plus_subscriptions |
| 274 |
Fan+ consumer subscription (discoverability + collection features). One row per user. |
| 275 |
|
| 276 |
- **FK:** user_id → users CASCADE (UNIQUE) |
| 277 |
- **Key columns:** status, stripe_subscription_id UNIQUE |
| 278 |
|
| 279 |
--- |
| 280 |
|
| 281 |
## Email & Mailing Lists |
| 282 |
|
| 283 |
### mailing_lists |
| 284 |
Per-project lists. Types: 'content' (new releases), 'devlog' (development updates), 'patches' (software patches). |
| 285 |
|
| 286 |
- **FK:** project_id → projects CASCADE |
| 287 |
- **Constraint:** UNIQUE(project_id, list_type) |
| 288 |
|
| 289 |
### mailing_list_subscribers |
| 290 |
Supports both registered users and email-only subscribers. CHECK(user_id NOT NULL OR email NOT NULL). |
| 291 |
|
| 292 |
- **FK:** list_id → mailing_lists CASCADE, user_id → users CASCADE (nullable) |
| 293 |
- **Constraints:** UNIQUE(list_id, user_id), UNIQUE(list_id, email) WHERE email NOT NULL |
| 294 |
|
| 295 |
### email_suppressions |
| 296 |
Hard bounces and spam complaints. Prevents sending to known-bad addresses. |
| 297 |
|
| 298 |
- **Key columns:** email UNIQUE (case-insensitive), reason ('HardBounce', 'SpamComplaint') |
| 299 |
|
| 300 |
### email_signups |
| 301 |
Landing page "notify me" signups (pre-launch or feature waitlist). |
| 302 |
|
| 303 |
- **Key columns:** email UNIQUE, source |
| 304 |
|
| 305 |
--- |
| 306 |
|
| 307 |
## Social & Community |
| 308 |
|
| 309 |
### follows |
| 310 |
Polymorphic follow system, users can follow users, projects, or tags. |
| 311 |
|
| 312 |
- **FK:** follower_id → users CASCADE |
| 313 |
- **Key columns:** target_type ('user', 'project', 'tag'), target_id |
| 314 |
- **Constraint:** UNIQUE(follower_id, target_type, target_id) |
| 315 |
|
| 316 |
### blog_posts |
| 317 |
Project-level blog posts (devlogs, announcements). Markdown source + rendered HTML. |
| 318 |
|
| 319 |
- **FK:** project_id → projects CASCADE, author_id → users (no cascade) |
| 320 |
- **Key columns:** title, slug, body_markdown, body_html, published_at, mt_thread_id |
| 321 |
- **Constraint:** UNIQUE(project_id, slug) |
| 322 |
|
| 323 |
### custom_links |
| 324 |
Creator profile links (social, merch, website). Ordered by sort_order. |
| 325 |
|
| 326 |
- **FK:** user_id → users CASCADE |
| 327 |
- **Trigger:** `update_custom_links_updated_at` |
| 328 |
|
| 329 |
--- |
| 330 |
|
| 331 |
## Collections |
| 332 |
|
| 333 |
### collections |
| 334 |
User-curated lists (playlists, reading lists, favorites). |
| 335 |
|
| 336 |
- **FK:** user_id → users CASCADE |
| 337 |
- **Constraint:** UNIQUE(user_id, slug) |
| 338 |
|
| 339 |
### collection_items |
| 340 |
Items in collections. Ordered by `position`. |
| 341 |
|
| 342 |
- **PK:** (collection_id, item_id), both CASCADE |
| 343 |
|
| 344 |
--- |
| 345 |
|
| 346 |
## SyncKit |
| 347 |
|
| 348 |
### sync_apps |
| 349 |
Registered SyncKit applications (GO, BB, AF, third-party). |
| 350 |
|
| 351 |
- **FK:** creator_id → users CASCADE; project_id → projects SET NULL, item_id → items SET NULL |
| 352 |
- **Key columns:** name, api_key UNIQUE, slug, is_active, redirect_uris TEXT[] |
| 353 |
|
| 354 |
### sync_devices |
| 355 |
User devices per app. Last-seen tracking for device management UI. |
| 356 |
|
| 357 |
- **FK:** app_id → sync_apps CASCADE, user_id → users CASCADE |
| 358 |
- **Constraint:** UNIQUE(app_id, user_id, device_name) |
| 359 |
|
| 360 |
### sync_keys |
| 361 |
E2E encryption keys per app/user pair. Key rotation via `key_version`. |
| 362 |
|
| 363 |
- **PK:** (app_id, user_id), both CASCADE |
| 364 |
- **Key columns:** key_version, encrypted_key |
| 365 |
|
| 366 |
### sync_log |
| 367 |
Changelog of data operations. Sequential `seq` (BIGSERIAL) enables cursor-based pull. |
| 368 |
|
| 369 |
- **FK:** app_id, user_id, device_id, all CASCADE |
| 370 |
- **Key columns:** table_name, operation ('INSERT'/'UPDATE'/'DELETE'), row_id, data (JSONB), client_timestamp |
| 371 |
- **Index:** (app_id, user_id, seq), the primary pull query path |
| 372 |
|
| 373 |
### sync_blobs |
| 374 |
File blobs for SyncKit (content-hashed dedup). Used when `sync_files=true` on a VFS. |
| 375 |
|
| 376 |
- **FK:** app_id, user_id. Both CASCADE |
| 377 |
- **Constraint:** UNIQUE(app_id, user_id, hash) |
| 378 |
- **Key columns:** hash, s3_key, size_bytes |
| 379 |
|
| 380 |
--- |
| 381 |
|
| 382 |
## Git Integration |
| 383 |
|
| 384 |
### git_repos |
| 385 |
Bare git repositories. Displayed via the `/source/` browser (G1, git2-based). |
| 386 |
|
| 387 |
- **FK:** user_id → users CASCADE, project_id → projects SET NULL |
| 388 |
- **Constraint:** UNIQUE(user_id, name) |
| 389 |
|
| 390 |
### ssh_keys |
| 391 |
SSH public keys for git push access. |
| 392 |
|
| 393 |
- **FK:** user_id → users CASCADE |
| 394 |
- **Constraint:** UNIQUE(user_id, fingerprint) |
| 395 |
|
| 396 |
### issues |
| 397 |
Lightweight issue tracker per repo. Sequential `number` per repo. |
| 398 |
|
| 399 |
- **FK:** repo_id → git_repos CASCADE, author_user_id → users CASCADE |
| 400 |
- **Constraint:** UNIQUE(repo_id, number) |
| 401 |
- **Indexed on:** (repo_id, status), author |
| 402 |
|
| 403 |
### issue_comments |
| 404 |
- **FK:** issue_id → issues CASCADE, author_user_id → users CASCADE |
| 405 |
|
| 406 |
### issue_labels |
| 407 |
Per-repo label definitions with color. |
| 408 |
|
| 409 |
- **FK:** repo_id → git_repos CASCADE |
| 410 |
- **Constraint:** UNIQUE(repo_id, name) |
| 411 |
|
| 412 |
### issue_label_assignments |
| 413 |
- **PK:** (issue_id, label_id), both CASCADE |
| 414 |
|
| 415 |
--- |
| 416 |
|
| 417 |
## OTA Updates |
| 418 |
|
| 419 |
### ota_releases |
| 420 |
App versions published for over-the-air updates (Tauri-compatible protocol). |
| 421 |
|
| 422 |
- **FK:** app_id → sync_apps CASCADE |
| 423 |
- **Constraint:** UNIQUE(app_id, version) |
| 424 |
|
| 425 |
### ota_artifacts |
| 426 |
Binary artifacts per release. One per target/arch combination. |
| 427 |
|
| 428 |
- **FK:** release_id → ota_releases CASCADE |
| 429 |
- **Constraint:** UNIQUE(release_id, target, arch) |
| 430 |
- **Key columns:** s3_key, file_size |
| 431 |
|
| 432 |
### ota_build_configs |
| 433 |
Automated build configuration per app. |
| 434 |
|
| 435 |
- **FK:** app_id → sync_apps CASCADE (UNIQUE), repo_id → git_repos CASCADE |
| 436 |
- **Key columns:** build_command, artifact_path, signing_key_path, targets TEXT[], enabled |
| 437 |
|
| 438 |
### ota_builds |
| 439 |
Individual build runs. Status lifecycle: pending → building → succeeded / failed. |
| 440 |
|
| 441 |
- **FK:** config_id → ota_build_configs CASCADE, release_id → ota_releases SET NULL |
| 442 |
|
| 443 |
--- |
| 444 |
|
| 445 |
## Remaining Tables |
| 446 |
|
| 447 |
### custom_domains |
| 448 |
Creator vanity domains. Verified via DNS TXT record. |
| 449 |
|
| 450 |
- **FK:** user_id → users CASCADE |
| 451 |
- **Key columns:** domain UNIQUE, verified, verification_token |
| 452 |
|
| 453 |
### oauth_authorization_codes |
| 454 |
OAuth PKCE codes for SyncKit SDK clients. Short-lived (5 min), single-use. |
| 455 |
|
| 456 |
- **FK:** app_id → sync_apps CASCADE, user_id → users CASCADE |
| 457 |
- **Key columns:** code UNIQUE, code_challenge, redirect_uri, expires_at, used_at |
| 458 |
|
| 459 |
### creator_waves / creator_waitlist / invite_codes |
| 460 |
Creator onboarding pipeline: waves (batches), waitlist (applications), invite codes (referrals). |
| 461 |
|
| 462 |
- creator_waves: wave_number UNIQUE |
| 463 |
- creator_waitlist: user_id → users CASCADE (UNIQUE), wave_id → creator_waves SET NULL |
| 464 |
- invite_codes: creator_id → users CASCADE, code UNIQUE |
| 465 |
|
| 466 |
### reports |
| 467 |
User abuse reports. Status: open → resolved/dismissed. |
| 468 |
|
| 469 |
### media_files |
| 470 |
User media library for embedding in markdown content. S3-backed. |
| 471 |
|
| 472 |
- **FK:** user_id → users CASCADE |
| 473 |
- **Constraint:** UNIQUE(user_id, folder, filename) |
| 474 |
|
| 475 |
### import_jobs |
| 476 |
Bulk import from external platforms (Patreon, Ko-fi, Gumroad). Tracks progress rows. |
| 477 |
|
| 478 |
### project_categories |
| 479 |
Taxonomy for project categorization. Referenced by projects.category_id. |
| 480 |
|
| 481 |
--- |
| 482 |
|
| 483 |
## Cascade Summary |
| 484 |
|
| 485 |
**CASCADE (delete parent → delete children):** Most FK relationships. Deleting a user cascades to all their projects, items, content, sync data, sessions, keys, etc. |
| 486 |
|
| 487 |
**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. |
| 488 |
|
| 489 |
**RESTRICT (prevent parent delete):** subscription_tiers, cannot delete a tier that has active subscribers. |
| 490 |
|
| 491 |
## Search Infrastructure |
| 492 |
|
| 493 |
|
| 494 |
|
| 495 |
| items | tsvector GIN | title + description + body | |
| 496 |
| items | trigram GIN | title, description (separate) | |
| 497 |
| projects | trigram GIN | title, description (separate) | |
| 498 |
| tags | trigram GIN | name | |
| 499 |
|
| 500 |
All trigram indexes use `gin_trgm_ops` from the `pg_trgm` extension. |
| 501 |
|
| 502 |
## Key Paths |
| 503 |
|
| 504 |
- `migrations/`: numbered SQL files, applied in order |
| 505 |
- `src/db/`: query functions grouped by domain |
| 506 |
- `src/models/`: Rust structs matching table schemas |
| 507 |
|