# Shared Reelify database (Laravel + Node)

**Database name:** `reelify` (MySQL on Laragon)

Both projects MUST use the same DB and keep schema changes in sync.

## Tables

| Table | Purpose |
|-------|---------|
| `users` | App users (Laravel auth) |
| `channels` | Workspace channels |
| `channel_interests` | interest/niche rows per channel |
| `channel_settings` | studio_defaults, auto, performance_signal |
| `content_ideas` | AI ideas per channel |
| `content_queue` | Generation jobs (“reels”) |
| `generated_scripts` | Script for a queue item (`script_lines`, `raw_json`) |
| `generated_videos` | Rendered video/audio/timings |
| `upload_history` | YouTube uploads |
| `youtube_accounts` | OAuth tokens + YT profile |
| `trend_cache` | Niche trends by market |
| `keywords` | Keywords (channel/idea/queue) |
| `hashtags` | Hashtags (channel/idea/queue) |
| `schedules` | Planned publishes |
| `analytics` | Metrics / clip history payloads |
| `logs` | App logs |
| `notifications` | User notifications |
| `reelify_settings` | KV (automation, brand) |
| `channel_dna` | AI Channel DNA profile |
| `channel_competitors` | Tracked competitor channels |
| `competitor_videos` | Competitor video intelligence |
| `competitor_analysis` | Your channel vs competitor |
| `content_opportunities` | Content gap opportunities |
| `viral_hooks` | Scored hook variations |
| `caption_presets` | Caption style presets |
| `video_captions` | Per-video captions + SRT/VTT |
| `automation_settings` | Manual / Assisted / Autopilot |
| `automation_runs` | Autopilot audit log |
| `revenue_metrics` | Revenue intelligence (est./actual) |
| `channel_health_scores` | Health score history |
| `channel_health_recommendations` | Improve-score actions |
| `content_performance` | Per-upload performance for self-learning |
| `ai_learning_insights` | What worked / failed analyses |
| `ai_learning_rules` | Distilled DNA / content rules |
| `quality_checks` | Pre-upload quality gate results |
| `quality_issues` | Issues attached to a quality check |
| `idea_bank` | Persistent idea bank (vs content_ideas refresh) |
| `ai_manager_conversations` | AI channel manager threads |
| `ai_manager_messages` | Manager chat messages |
| `ai_manager_actions` | Logged actionable manager commands |

### SaaS foundation (Laravel + Node)

| Table | Purpose |
|-------|---------|
| `users` (extended) | phone, country, role, status, onboarding_*, current_plan_id, current_subscription_id |
| `plans` | free / standard / premium feature toggles + limits (UUID) |
| `subscriptions` | pending/active/expired/cancelled billing periods |
| `subscription_usage` | per-user monthly counters (channels, ideas, videos, …) |
| `payment_methods` | EasyPaisa / JazzCash / bank / crypto instructions |
| `payments` | manual payment proofs awaiting admin approve/reject |
| `admin_subscription_actions` | admin activate/cancel audit trail |
| `audit_logs` | auth/billing/admin event log |
| `personal_access_tokens` | Sanctum API tokens (shared Node ↔ Laravel) |
| `password_reset_tokens` | password reset hashes |

Node mirrors Sanctum: store `sha256(plain)` in `personal_access_tokens.token`, return `${id}\|${plain}`.

Canonical SaaS migration: `reelify-laravel/database/migrations/2026_08_08_060000_create_saas_foundation_tables.php`

Seed (either stack): Laravel `SaasSeeder` or Node `node server/scripts/seedSaas.js` (skips if plans already exist).

## Env (both projects)

```env
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=reelify
DB_USERNAME=root
DB_PASSWORD=

# Node: false = guest product APIs; true = require Bearer except auth/billing catalog
REELIFY_REQUIRE_AUTH=false
```

## Rule

Any schema or domain-data change → update **Laravel migration/models** and **Node `server/lib/*`** together.

Canonical migration: `reelify-laravel/database/migrations/2026_08_08_031500_create_shared_reelify_schema.php`