Jacek Pyziak
e9a3602576
- Added optional SEMSTORM domain input field in site creation and editing forms. - Introduced SEO panel links in site dashboard and edit pages. - Created a new cron job for SEMSTORM data synchronization. - Implemented database migrations for cron logs and site SEO metrics. - Developed SiteSeoSyncService to handle SEMSTORM data fetching and storage. - Added logging functionality for cron events. - Created a new LogController to display cron logs with filtering options. - Added SEO statistics dashboard with visual representation of metrics. - Implemented site SEO metrics model for data retrieval and manipulation.
227 lines
8.2 KiB
Markdown
227 lines
8.2 KiB
Markdown
# BackPRO - Schemat bazy danych
|
||
|
||
## Diagram relacji
|
||
|
||
```
|
||
users
|
||
↓ (brak FK, niezależna tabela)
|
||
|
||
global_topics (parent_id → self) â†<C3A2> hierarchia 2-poziomowa
|
||
↓
|
||
└── topics.global_topic_id (opcjonalne powiązanie)
|
||
|
||
sites â†<C3A2>──── topics â†<C3A2>──── articles
|
||
│ │
|
||
│ ├── global_topic_id (FK → global_topics, opcjonalne)
|
||
│ └── wp_category_id (mapowanie na kategorię WP)
|
||
│
|
||
└── last_published_at (tracking publikacji)
|
||
|
||
settings (klucz-wartość, konfiguracja globalna)
|
||
```
|
||
|
||
## Tabele
|
||
|
||
### `users` - Użytkownicy systemu
|
||
|
||
```sql
|
||
CREATE TABLE users (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
username VARCHAR(50) NOT NULL UNIQUE,
|
||
password_hash VARCHAR(255) NOT NULL,
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
```
|
||
|
||
| Kolumna | Typ | Opis |
|
||
|---------|-----|------|
|
||
| id | INT AUTO_INCREMENT PK | Identyfikator |
|
||
| username | VARCHAR(50) UNIQUE NOT NULL | Login użytkownika |
|
||
| password_hash | VARCHAR(255) NOT NULL | Hash hasła (bcrypt) |
|
||
| created_at | DATETIME | Data utworzenia |
|
||
|
||
### `global_topics` - Biblioteka tematów (2-poziomowa hierarchia)
|
||
|
||
```sql
|
||
CREATE TABLE global_topics (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
parent_id INT NULL,
|
||
name VARCHAR(255) NOT NULL,
|
||
description TEXT NULL,
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (parent_id) REFERENCES global_topics(id) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
```
|
||
|
||
| Kolumna | Typ | Opis |
|
||
|---------|-----|------|
|
||
| id | INT AUTO_INCREMENT PK | Identyfikator |
|
||
| parent_id | INT NULL FK → self | NULL = kategoria nadrzędna, wartość = subtemat |
|
||
| name | VARCHAR(255) | Nazwa tematu/kategorii |
|
||
| description | TEXT NULL | Opis / wytyczne dla AI |
|
||
| created_at | DATETIME | Data dodania |
|
||
|
||
**Preinstalowane kategorie:** Polityka, Zdrowie, Sport, Technologia, Biznes i Finanse, Rozrywka, Nauka, Edukacja, Podróże, Motoryzacja, Dom i Ogród, Kuchnia, Moda i Uroda, Prawo (+ ~50 subtematów).
|
||
|
||
### `sites` - Strony WordPress
|
||
|
||
```sql
|
||
CREATE TABLE sites (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
name VARCHAR(255) NOT NULL,
|
||
url VARCHAR(255) NOT NULL,
|
||
api_user VARCHAR(100) NOT NULL,
|
||
api_token VARCHAR(255) NOT NULL,
|
||
publish_interval_days INT NOT NULL DEFAULT 3,
|
||
last_published_at DATETIME NULL,
|
||
is_active TINYINT(1) NOT NULL DEFAULT 1,
|
||
is_multisite TINYINT(1) NOT NULL DEFAULT 0,
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
```
|
||
|
||
| Kolumna | Typ | Opis |
|
||
|---------|-----|------|
|
||
| id | INT AUTO_INCREMENT PK | Identyfikator |
|
||
| name | VARCHAR(255) | Nazwa strony (wyświetlana w panelu) |
|
||
| url | VARCHAR(255) | URL WordPressa (np. https://example.com) |
|
||
| api_user | VARCHAR(100) | Login użytkownika WP do REST API |
|
||
| api_token | VARCHAR(255) | Application Password WP |
|
||
| publish_interval_days | INT DEFAULT 3 | Co ile dni publikować nowy artykuł |
|
||
| last_published_at | DATETIME NULL | Data ostatniej publikacji |
|
||
| is_active | TINYINT(1) DEFAULT 1 | Czy strona jest aktywna (0/1) |
|
||
| is_multisite | TINYINT(1) DEFAULT 0 | Czy wielotematyczna (0/1) |
|
||
| created_at | DATETIME | Data dodania |
|
||
|
||
### `topics` - Tematy
|
||
|
||
```sql
|
||
CREATE TABLE topics (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
site_id INT NOT NULL,
|
||
name VARCHAR(255) NOT NULL,
|
||
description TEXT NULL,
|
||
wp_category_id INT NULL,
|
||
article_count INT NOT NULL DEFAULT 0,
|
||
is_active TINYINT(1) NOT NULL DEFAULT 1,
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (site_id) REFERENCES sites(id) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
```
|
||
|
||
| Kolumna | Typ | Opis |
|
||
|---------|-----|------|
|
||
| id | INT AUTO_INCREMENT PK | Identyfikator |
|
||
| site_id | INT FK → sites.id | Strona, do której przypisany temat |
|
||
| name | VARCHAR(255) | Nazwa tematu (np. "Ogrodnictwo", "DIY") |
|
||
| description | TEXT NULL | Opis/wytyczne dla AI (jak pisać artykuły) |
|
||
| wp_category_id | INT NULL | ID kategorii w WordPressie (mapowanie) |
|
||
| article_count | INT DEFAULT 0 | Licznik opublikowanych artykułów z tego tematu |
|
||
| is_active | TINYINT(1) DEFAULT 1 | Czy temat aktywny |
|
||
| created_at | DATETIME | Data dodania |
|
||
|
||
**Uwaga:** `article_count` jest zinkrementalizowanym licznikiem, aktualizowanym po każdej publikacji. Służy do równomiernego rozkładu tematów (TopicBalancer).
|
||
|
||
### `articles` - Artykuły
|
||
|
||
```sql
|
||
CREATE TABLE articles (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
site_id INT NOT NULL,
|
||
topic_id INT NOT NULL,
|
||
title VARCHAR(500) NOT NULL,
|
||
content TEXT NOT NULL,
|
||
wp_post_id INT NULL,
|
||
image_url VARCHAR(500) NULL,
|
||
status ENUM('generated', 'published', 'failed') NOT NULL DEFAULT 'generated',
|
||
ai_model VARCHAR(50) NULL,
|
||
prompt_used TEXT NULL,
|
||
error_message TEXT NULL,
|
||
published_at DATETIME NULL,
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (site_id) REFERENCES sites(id) ON DELETE CASCADE,
|
||
FOREIGN KEY (topic_id) REFERENCES topics(id) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
```
|
||
|
||
| Kolumna | Typ | Opis |
|
||
|---------|-----|------|
|
||
| id | INT AUTO_INCREMENT PK | Identyfikator |
|
||
| site_id | INT FK → sites.id | Strona docelowa |
|
||
| topic_id | INT FK → topics.id | Temat artykułu |
|
||
| title | VARCHAR(500) | Tytuł artykułu |
|
||
| content | TEXT | Treść artykułu (HTML) |
|
||
| wp_post_id | INT NULL | ID posta w WordPressie (po publikacji) |
|
||
| image_url | VARCHAR(500) NULL | URL wygenerowanego obrazka |
|
||
| status | ENUM | Status: generated, published, failed |
|
||
| ai_model | VARCHAR(50) | Model AI użyty do wygenerowania |
|
||
| prompt_used | TEXT | Pełny prompt wysłany do AI |
|
||
| error_message | TEXT NULL | Treść błędu (jeśli status = failed) |
|
||
| published_at | DATETIME NULL | Data publikacji na WordPressie |
|
||
| created_at | DATETIME | Data wygenerowania |
|
||
|
||
### `settings` - Ustawienia globalne
|
||
|
||
```sql
|
||
CREATE TABLE settings (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
`key` VARCHAR(100) NOT NULL UNIQUE,
|
||
value TEXT NULL
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
```
|
||
|
||
| Kolumna | Typ | Opis |
|
||
|---------|-----|------|
|
||
| id | INT AUTO_INCREMENT PK | Identyfikator |
|
||
| key | VARCHAR(100) UNIQUE | Klucz ustawienia |
|
||
| value | TEXT | Wartość ustawienia |
|
||
|
||
**Domyślne ustawienia:**
|
||
| Klucz | Wartość | Opis |
|
||
|-------|---------|------|
|
||
| openai_api_key | sk-... | Klucz API OpenAI |
|
||
| openai_model | gpt-4o | Model do generowania artykułów |
|
||
| freepik_api_key | ... | Klucz API Freepik |
|
||
| image_provider | freepik | Dostawca obrazków (freepik/unsplash/pexels) |
|
||
| article_min_words | 800 | Min. długość artykułu |
|
||
| article_max_words | 1200 | Max. długość artykułu |
|
||
|
||
## Indeksy
|
||
|
||
```sql
|
||
CREATE INDEX idx_topics_site_id ON topics(site_id);
|
||
CREATE INDEX idx_articles_site_id ON articles(site_id);
|
||
CREATE INDEX idx_articles_topic_id ON articles(topic_id);
|
||
CREATE INDEX idx_articles_status ON articles(status);
|
||
CREATE INDEX idx_sites_is_active ON sites(is_active);
|
||
CREATE INDEX idx_sites_last_published ON sites(last_published_at);
|
||
```
|
||
|
||
### `site_seo_metrics` - Historia metryk SEO (SEMSTORM)
|
||
|
||
```sql
|
||
CREATE TABLE site_seo_metrics (
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
site_id INT NOT NULL,
|
||
metric_month DATE NOT NULL,
|
||
top3 INT NOT NULL DEFAULT 0,
|
||
top10 INT NOT NULL DEFAULT 0,
|
||
top20 INT NOT NULL DEFAULT 0,
|
||
top50 INT NOT NULL DEFAULT 0,
|
||
traffic INT NOT NULL DEFAULT 0,
|
||
source_payload TEXT NULL,
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (site_id) REFERENCES sites(id) ON DELETE CASCADE,
|
||
UNIQUE KEY uniq_site_month (site_id, metric_month)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
```
|
||
|
||
| Kolumna | Typ | Opis |
|
||
|---------|-----|------|
|
||
| site_id | INT FK › sites.id | Strona, dla ktorej pobrano dane |
|
||
| metric_month | DATE | Miesiac pomiaru (pierwszy dzien miesiaca) |
|
||
| top3/top10/top20/top50 | INT | Liczba slow kluczowych w danym zakresie |
|
||
| traffic | INT | Szacowany ruch organiczny |
|
||
| source_payload | TEXT | Surowa odpowiedz API do diagnostyki |
|