Migrationen

Postbox nutzt aktuell 123 Migrationen. Die Datenbank ist PostgreSQL -- mehrere Migrationen nutzen PostgreSQL-spezifische Features (GIN-Indexes, Partial Unique Indexes). Tests laufen gegen SQLite in-memory, daher muss PostgreSQL-spezifische Syntax in Tests ggf. gesondert behandelt werden.

Location: database/migrations/

Laravel Framework

Nr	Migration	Tabelle	Typ	Wichtigste Spalten
1	0001_01_01_000000	users, password_reset_tokens, sessions	create	id, name, email, password; sessions: user_id, ip, user_agent, last_activity
2	0001_01_01_000001	cache, cache_locks	create	key, value, expiration
3	0001_01_01_000002	jobs, job_batches, failed_jobs	create	Queue-Tabellen (Database Driver)

Core Models (Januar)

Nr	Migration	Tabelle	Typ	Wichtigste Spalten
4	2026_01_01_000001	workspaces	create	id, owner_id, name
5	2026_01_01_000002	watchers	create	id, workspace_id, created_by, name
6	2026_01_01_000003	social_profiles	create	id, platform, handle, handle_normalized, external_id, canonical_url, data, title, description, thumbnail_url
7	2026_01_01_000004	social_profile_daily_metrics	create	id, social_profile_id, date, followers_count, view_count, video_count
8	2026_01_01_000005	watcher_sources	create	id, watcher_id, social_profile_id, platform, input_url
9	2026_01_01_125123	social_profiles, social_profile_daily_metrics	alter	YouTube-Felder: custom_url, published_at, country, language, comment_count
10	2026_01_01_130226	watchers	alter	is_name_custom
11	2026_01_01_130253	social_profiles	alter	Unique-Index: platform + external_id
12	2026_01_01_131633	social_profiles	alter	last_scrape_failed_at, last_scrape_error
13	2026_01_01_132525	social_profiles	alter	tracking_enabled, tracking_disabled_at/by_system/reason, last_scraped_at, last_daily_scrape_on
14	2026_01_01_133607	social_profiles	alter	api_status, api_status_reason
15	2026_01_01_134846	watchers, watcher_sources	alter	Foreign Keys und Uniques
16	2026_01_01_140111	watcher_sources	alter	Unique/Index-Anpassungen
17	2026_01_01_150000	social_profiles	alter	Partial Unique Indexes (PostgreSQL)
18	2026_01_01_160000	watcher_import_runs	create	workspace_id, created_by, platform, status, Zähler, finished_at
19	2026_01_01_160010	watcher_import_failures	create	watcher_import_run_id, url, reason

Instagram & Images (Anfang Januar)

Nr	Migration	Tabelle	Typ	Wichtigste Spalten
20	2026_01_02_000000	social_profiles, social_profile_daily_metrics	alter	Instagram-Felder: is_private, profile_type, following_count, post_count
21	2026_01_02_000002	social_profile_images	create	social_profile_id, path, hash, source_url
22	2026_01_02_000002	workspaces	alter	description
23	2026_01_02_000003	workspaces	alter	description (Duplikat-Migration)
24	2026_01_03_000020	watcher_import_items	create	watcher_import_run_id, url, dedupe_key
25	2026_01_03_000021	watcher_import_runs	alter	paused_until, pause_reason

Collector & Dashboard (Anfang Januar)

Nr	Migration	Tabelle	Typ	Wichtigste Spalten
26	2026_01_04_000000	daily_sync_runs	create	date, expected_profiles, processed_profiles, failed_profiles, status, finished_at
27	2026_01_04_000001	dashboard_daily_rollups	create	date, owner_id, workspace_id, watcher_id, social_profile_id, Metriken
28	2026_01_04_000002	collector_clients	create	id (uuid), name, capabilities, last_seen_at
29	2026_01_04_000003	collector_jobs	create	id (uuid), source, payload, status, priority, leased_by, lease_expires_at, attempts, result, error_code/message
30	2026_01_04_000004	personal_access_tokens	create	Sanctum Token-Tabelle
31	2026_01_06_000000	personal_access_tokens	alter	tokenable_id → string (UUID)

Favorites, Queuing & Rollups (Mitte Januar)

Nr	Migration	Tabelle	Typ	Wichtigste Spalten
32	2026_01_10_000000	social_profiles	alter	last_daily_queue_on, last_daily_queue_at
33	2026_01_10_000000	dashboard_daily_rollups	alter	Instagram-Metriken: following_count, post_count
34	2026_01_10_000001	watcher_import_failures	alter	error_code
35	2026_01_10_000010	favorite_social_profiles	create	user_id, social_profile_id (unique)

Pulse, Research & API Quota (Ende Januar)

Nr	Migration	Tabelle	Typ	Wichtigste Spalten
36	2026_01_21_163730	pulse_*	create	Laravel Pulse Tabellen
37	2026_01_24_000000	youtube_research_queries	create	query, topic_id, result_count, result_urls, response/error_payload
38	2026_01_28_120000	workspaces	alter	Admin-Workspace Seeder (ID 999999999999)
39	2026_01_28_120000	google_api_quota_limits	create	project_id, service, quota_metric, display_name, unit, limit_value
40	2026_01_28_120100	google_api_quota_usages	create	project_id, service, quota_metric, date, used_value, by_credential
41	2026_01_29_210000	google_api_quota_limits	alter	unit in Unique-Constraint
42	2026_01_30_094041	youtube_research_queries	alter	imported_at

Related Channels & Explore (Ende Januar)

Nr	Migration	Tabelle	Typ	Wichtigste Spalten
43	2026_01_30_110000	youtube_related_channels	create	social_profile_id, related_social_profile_id, relevance_score, search_query
44	2026_01_30_110001	social_profiles	alter	related_channels_status/searched_at/error
45	2026_01_31_100000	pages	create	slug, title, content, meta_description, is_draft/published, created_by/updated_by
46	2026_01_31_155204	social_profiles	alter	language
47	2026_01_31_180000	explore_profile_metrics	create	social_profile_id, followers_count, growth_1d/7d/30d, trending_score, follower_tier, detected_tags, is_rising_star/is_new
48	2026_01_31_180001	explore_trending_videos	create	social_profile_id, video_id, title, view_count, trending_score, velocity_score
49	2026_01_31_180002	explore_categories	create	slug, name, name_de, icon, keywords, youtube_category_ids, sort_order, is_active

Instagram Related & Collector (Ende Januar / Anfang Februar)

Nr	Migration	Tabelle	Typ	Wichtigste Spalten
50	2026_01_31_220000	instagram_related_profiles	create	social_profile_id, related_social_profile_id, relevance_score, match_reason
51	2026_01_31_220001	instagram_profile_keywords	create	social_profile_id, keyword, source
52	2026_01_31_220002	social_profiles	alter	related_profiles_status/calculated_at/error
53	2026_02_01_000002	collector_jobs	alter	priority
54	2026_02_01_094335	pending_you_tube_channel_imports	create	source_social_profile_id, youtube_channel_id, relevance_score, retry_count
55	2026_02_01_190000	ai_detection_logs	create	social_profile_id, type, model, input/output_data, status, tokens_used, duration_ms

Verified, Global Snapshots & Explore Trending (Anfang Februar)

Nr	Migration	Tabelle	Typ	Wichtigste Spalten
56	2026_02_02_000000	social_profiles	alter	is_verified
57	2026_02_02_000001	dashboard_global_snapshots	create	date, metric, platform, value
58	2026_02_02_120000	explore_profile_metrics	alter	is_trending

Scrape Progress, AI Enhancement & Retry-Lifecycle (Anfang Februar)

Nr	Migration	Tabelle	Typ	Wichtigste Spalten
59	2026_02_03_000001	daily_scrape_progress	create	user_id, date, expected/completed/failed_count, notification_sent
60	2026_02_04_080104	social_profiles	alter	AI-Enhancement-Felder (ai_category, ai_keywords, ai_description, detected_country/language)
61	2026_02_05_000001	dashboard_global_snapshots	alter	tier
62	2026_02_05_000002	social_profiles	alter	is_verified Index
63	2026_02_05_000003	social_profiles	alter	scrape_fail_streak, next_retry_at, retry_count, deactivated_at, archived_at, archive_reason, manual_penalty
64	2026_02_05_000004	social_profile_scores	create	social_profile_id, date, score, data_source_date, status
65	2026_02_05_000005	cross_platform_related_profiles	create	source_profile_id, related_profile_id, relevance_score, match_reason
66	2026_02_05_083822	pages	alter	show_in_header/footer/sidebar, nav_priority
67	2026_02_05_211953	social_profiles	alter	GIN-Index auf ai_keywords (PostgreSQL)
68	2026_02_06_200000	social_profiles	alter	parsed_links

Queue Metrics & YouTube Videos (Mitte Februar)

Nr	Migration	Tabelle	Typ	Wichtigste Spalten
69	2026_02_10_000000	queue_metrics	create	Queue-Metriken-Snapshots
70	2026_02_11_000000	youtube_videos	create	video_id (PK), social_profile_id, channel_id, title, description, duration, tags, thumbnails
71	2026_02_11_000001	youtube_video_daily_metrics	create	youtube_video_id, date, view/like/comment/favorite_count
72	2026_02_11_000002	youtube_video_syncs	create	social_profile_id, uploads_playlist_id, auto_sync_enabled, last_sync_at
73	2026_02_11_000003	youtube_video_syncs	alter	Fail-Tracking: last_fail_date, consecutive_fail_days
74	2026_02_11_000004	youtube_video_syncs	alter	last_video_count
75	2026_02_12_000000	youtube_video_syncs	alter	auto_sync_enabled Default → false

Leaderboard Snapshots & Thumbnails (Mitte Februar)

Nr	Migration	Tabelle	Typ	Wichtigste Spalten
76	2026_02_13_000000	dashboard_leaderboard_snapshots	create	Leaderboard-Snapshots
77	2026_02_13_000010	youtube_videos	alter	thumbnail_url/path/hash
78	2026_02_14_000000	dashboard_leaderboard_snapshots	alter	Trim auf Top 100

Audits & Notifications (Mitte Februar)

Nr	Migration	Tabelle	Typ	Wichtigste Spalten
79	2026_02_15_000001	audits	create	Owen-IT Audit-Tabelle
80	2026_02_16_000000	notifications	create	Notifications mit admin_only, expires_at
81	2026_02_16_000001	announcements, announcement_reads	create	Announcement-System
82	2026_02_16_100000	explore_profile_metrics	alter	previous_trending_score

Blocking & Sanitizer (Mitte/Ende Februar)

Nr	Migration	Tabelle	Typ	Wichtigste Spalten
83	2026_02_17_000000	social_profiles	alter	blocked_at, blocked_by, block_reason
84	2026_02_18_000000	social_profiles	alter	ai_social_links
85	2026_02_18_000001	blocked_tags	create	tag, blocked_by, blocked_at, reason
86	2026_02_19_000000	social_profiles	alter	sanitized_at, sanitize_reason, sanitize_checked_at

User Settings, Notifications & Tags (Ende Februar)

Nr	Migration	Tabelle	Typ	Wichtigste Spalten
87	2026_02_20_000001	users	alter	timezone
88	2026_02_20_000002	notification_preferences	create	user_id, workspace_id, notification_type, channel_app/email
89	2026_02_20_000003	user_quiet_hours	create	user_id, enabled, window_start/end, monday-sunday
90	2026_02_20_000004	mail_log	create	user_id, mail_type, recipient, subject, status, error_message
91	2026_02_20_000005	notification_stats	create	date, channel, type, count
92	2026_02_20_000006	sessions	alter	Index auf last_activity
93	2026_02_20_000007	announcement_reads	alter	dismissed_at
94	2026_02_20_000008	tag_consolidation_logs	create	batch_id, source_tag, target_tag, action, confidence, reason, affected_profiles, status
95	2026_02_20_000009	tag_aliases	create	source_tag (unique), target_tag

AI, Video Scores, Indexes & Links (Ende Februar)

Nr	Migration	Tabelle	Typ	Wichtigste Spalten
96	2026_02_20_000010	ai_detection_logs	alter	social_profile_id nullable
97	2026_02_20_000011	youtube_video_scores	create	youtube_video_id, social_profile_id, date, score, status, is_short, Component-Scores
98	2026_02_20_000012	diverse	alter	PostgreSQL-Optimierung: fehlende Indexes auf häufig abgefragte Spalten
99	2026_02_20_000013	social_profile_links	create	social_profile_id, type, platform, value, display_url, source, approved_at/by

Contact Links, Discover & AI Retry (Ende Februar)

Nr	Migration	Tabelle	Typ	Wichtigste Spalten
100	2026_02_20_100003	social_profile_links	alter	linked_social_profile_id (FK), import_status, import_error
101	2026_02_20_100004	explore_categories	seeder	Ersetzt 12 Keyword-Kategorien durch 26 AI-Kategorien via ExploreCategorySeeder, fuehrt explore:calculate --type=categories aus
102	2026_02_21_100000	social_profiles	alter	GIN-Trigram-Indexes auf handle_normalized und title (erfordert pg_trgm Extension, CONCURRENTLY, ohne Transaction)
103	2026_02_21_100001	search_histories	create	id, user_id, query, searched_at (Unique: user_id + query)
104	2026_02_21_200000	social_profiles	alter	ai_detection_failed_at (nullable Timestamp)

Auth, User-Blocking & YouTube Research (Februar, Woche 3)

Nr	Migration	Tabelle	Typ	Wichtigste Spalten
105	2026_02_23_000001	users, password_reset_tokens	alter+create	password (nullable), workos_id/avatar nullable, neue password_reset_tokens-Tabelle
106	2026_02_12_203258	users	alter	two_factor_secret, two_factor_recovery_codes, two_factor_confirmed_at
107	2026_02_23_100001	users, user_blocks	alter+create	blocked_at, blocked_by, block_reason auf users; user_blocks: user_id, admin_id, action, reason
108	2026_02_13_000001	youtube_research_queries	alter	batch_id (UUID), relevance_language, region_code, min_subscribers, min_videos, filtered_count, status
109	2026_02_13_000002	youtube_research_keyword_cooldowns	create	id, keyword (unique), used_at

Newsletter & Legal Acceptance (Februar, Woche 3)

Nr	Migration	Tabelle	Typ	Wichtigste Spalten
110	2026_02_15_000001	users	alter	newsletter_opted_in_at, newsletter_token (varchar 64), newsletter_token_expires_at
111	2026_02_15_000002	users	alter	privacy_accepted_at, terms_accepted_at (nullable Timestamps fuer DSGVO-Zustimmung)

Error Monitor & DB Monitoring (Februar, Woche 3-4)

Nr	Migration	Tabelle	Typ	Wichtigste Spalten
112	2026_02_26_000001	error_page_logs	create	path, path_hash (MD5), status_code, hour (created_at), hit_count, is_bot_detected, referer, user_agent. Unique: (path_hash, status_code, created_at)
113	2026_02_26_000002	error_page_daily_stats	create	date, status_code_4xx, status_code_5xx, unique_paths, bot_percentage, top_paths (JSON)
114	2026_02_26_000003	url_redirects	create	source_path, target_url, status_code, is_regex, regex_pattern, is_active, hit_count, notes
115	2026_02_27_000001	db_monitoring_snapshots + db_slow_query_log	create	Metriken-Snapshots (active_connections, tps, db_size, cache_hit_ratio, dead_tuples, table_metrics JSON) + Slow Queries (duration_ms, query, parameters, source)
116	2026_02_27_000002	explore_daily_snapshots	create	date (unique), total_profiles, youtube_count, instagram_count, tier counts, trending_count, rising_stars_count
117	2026_02_27_000003	dismissed_error_paths	create	path_hash (MD5, unique), path, dismissed_by, dismissed_at

Contact, Feedback & CMS (Februar, Woche 4)

Nr	Migration	Tabelle	Typ	Wichtigste Spalten
118	2026_02_24_000001	contact_submissions	create	name, email, phone, subject, message, source, ip_address, attachments (JSON)
119	2026_02_24_000002	contact_submissions	alter	source (string, Default 'contact', unterscheidet contact/feedback)
120	2026_02_28_000001	pages	alter	meta_robots (nullable, max 50 Zeichen, z.B. noindex, nofollow)

AI Manual Override & Cross-Platform Verification (Februar, Woche 4)

Nr	Migration	Tabelle	Typ	Wichtigste Spalten
121	2026_02_28_000002	social_profiles	alter	ai_manual_category, ai_manual_description, ai_manual_keywords (JSON), ai_manual_edited_at, ai_manual_edited_by (FK)
122	2026_02_28_000003	ai_manual_override_logs	create	social_profile_id (FK), user_id (FK), field, old_value, new_value
123	2026_02_28_000004	cross_platform_related_profiles	alter	cross_profile_verified_at, cross_profile_verified_by (FK)

Error Monitor Erweiterung (Februar, Woche 4)

Nr	Migration	Tabelle	Typ	Wichtigste Spalten
124	2026_02_28_900012	dismissed_error_paths	alter	is_muted (boolean, default false) — gemutete Pfade werden weiterhin getrackt aber von Anomalie-Detection uebersprungen

Hinweise

PostgreSQL Extensions

pg_trgm (erforderlich seit Migration 102):

# Muss vor migrate aktiviert werden:
sudo -u postgres psql -d postbox_db1 -c "CREATE EXTENSION IF NOT EXISTS pg_trgm;"

Die Extension aktiviert gin_trgm_ops fuer Fuzzy-Suche (similarity(), schnelles ILIKE). Wird verwendet fuer:

• Migration 102: GIN-Trigram-Indexes auf social_profiles.handle_normalized und social_profiles.title
• Manueller Index: watchers.name (nicht in Migration, separat anlegen)

-- Optionaler manueller Index fuer Watcher-Suche:
CREATE INDEX CONCURRENTLY watchers_name_trgm_index ON watchers USING gin (name gin_trgm_ops);

PostgreSQL-spezifische Features

Einige Migrationen nutzen PostgreSQL-exklusive Funktionalität:

• Migration 17: Partial Unique Indexes auf social_profiles
• Migration 67: GIN-Index auf ai_keywords (JSONB-Feld)
• Migration 98: Diverse PostgreSQL-optimierte Indexes
• Migration 102: GIN-Trigram-Indexes (pg_trgm Extension erforderlich)

Tests und SQLite

Da Tests gegen SQLite in-memory laufen, müssen PostgreSQL-spezifische Syntax-Elemente (::bigint, jsonb @>, GIN-Indexes) in Tests ggf. mit Conditional Logic oder separaten Test-Migrationen behandelt werden.

Migrations-Konventionen

• Create-Migrationen: create_{table}_table -- erstellt eine neue Tabelle
• Alter-Migrationen: Beschreibender Name oder Timestamp -- erweitert bestehende Tabellen
• Seeder-Migrationen: Z.B. Migration 38 (Admin-Workspace) -- schreibt auch Daten