Database Schema
Complete reference for all 23 database tables in Scriptlog. All tables use utf8mb4 charset and InnoDB engine.
Note: All tables use
utf8mb4 charset and InnoDB engine. The only real foreign key is tbl_api_keys.user_id → tbl_users.ID (ON DELETE CASCADE); every other relationship is application-managed.
S10 hot-filter indexes: fresh installations get
idx_post_date and idx_post_type_status on tbl_posts, idx_post_topic_topic on tbl_post_topic, and idx_comment_post_status on tbl_comments from install/include/dbtable.php. Existing installations add them with install/migrate-hot-filter-indexes.php (CLI only; idempotent, safe to run twice). See docs/dev-docs/DATABASE_SCHEMA_GUIDE.md and plan/PERFORMANCE_PLAN_SEPTEMBER2026.md §S10.
tbl_users
User accounts and authentication.
| Column | Type | Description |
|---|---|---|
ID | BIGINT(20) UNSIGNED | Primary key |
user_login | VARCHAR(60) | Unique username |
user_email | VARCHAR(100) | Unique email |
user_pass | VARCHAR(255) | Password hash |
user_level | VARCHAR(20) | Role (administrator, manager, editor, author, contributor, subscriber) |
user_fullname | VARCHAR(120) | Display name |
user_url | VARCHAR(100) | Website URL |
user_registered | DATETIME | Registration date |
user_activation_key | VARCHAR(255) | Activation token |
user_reset_key | VARCHAR(255) | Password reset token |
user_reset_complete | VARCHAR(3) | Password reset finished (Yes/No) |
user_session | VARCHAR(255) | Session identifier |
user_banned | TINYINT | Ban status (0/1) |
user_signin_count | INT | Login count |
user_locked_until | DATETIME | Account lock expiry |
login_time | TIMESTAMP | Last login or profile change |
tbl_posts
Blog posts and pages.
| Column | Type | Description |
|---|---|---|
ID | BIGINT(20) UNSIGNED | Primary key |
media_id | BIGINT(20) UNSIGNED | Featured media (application-managed link to tbl_media) |
post_author | BIGINT(20) UNSIGNED | Author ID (application-managed link to tbl_users) |
post_date | DATETIME | Publication date |
post_modified | DATETIME | Last modified date |
post_title | TINYTEXT | Post title |
post_slug | VARCHAR(255) | URL slug (indexed, used for canonical URLs) |
post_content | LONGTEXT | Full content |
post_summary | MEDIUMTEXT | Excerpt/preview |
post_keyword | TEXT | SEO keywords |
post_status | VARCHAR(20) | publish, draft, pending |
post_visibility | VARCHAR(20) | public, private |
post_password | VARCHAR(255) | Password for protected posts |
post_tags | TEXT | Comma-separated tags (FULLTEXT indexed) |
post_headlines | INT | Number of headline entries |
post_sticky | INT | 1 pins the post to the top of listings |
post_type | VARCHAR(120) | blog or page |
post_locale | VARCHAR(10) | Language code (i18n) |
comment_status | VARCHAR(20) | open, closed |
passphrase | VARCHAR(255) | Hash for the protected post unlock flow |
Indexes: PRIMARY KEY (ID), author_id (post_author), post_media (media_id), idx_post_slug (post_slug), idx_post_locale (post_locale), idx_post_status_date (post_status, post_date), idx_post_date (post_date), idx_post_type_status (post_type, post_status, post_date), FULLTEXT (post_tags, post_title, post_content). S10 hot-filter indexes: idx_post_date speeds date-ordered and archive listings; idx_post_type_status speeds listings filtered by content type and status ordered by date.
tbl_topics
Categories/topics for organizing posts.
| Column | Type | Description |
|---|---|---|
ID | BIGINT(20) UNSIGNED | Primary key |
topic_title | VARCHAR(255) | Category name |
topic_slug | VARCHAR(255) | URL slug |
topic_status | ENUM('Y','N') | Active/inactive |
topic_locale | VARCHAR(10) | Language code (i18n) |
tbl_comments
Comments and replies.
| Column | Type | Description |
|---|---|---|
ID | BIGINT(20) UNSIGNED | Primary key |
comment_post_id | BIGINT(20) UNSIGNED | Post ID (application-managed link to tbl_posts) |
comment_parent_id | BIGINT(20) | Parent comment ID (0 for top-level) |
comment_author_name | VARCHAR(60) | Author name |
comment_author_ip | VARCHAR(100) | Author IP address |
comment_author_email | VARCHAR(100) | Author email |
comment_content | TEXT | Comment text |
comment_status | VARCHAR(20) | approved, pending, spam |
comment_date | DATETIME | Submission date |
Indexes: PRIMARY KEY (ID), id_comment_post (comment_post_id), idx_comment_post_status (comment_post_id, comment_status). S10 hot-filter index: idx_comment_post_status speeds the hot frontend filter on post plus status (for example approved comments of one post).
tbl_media
Media library files.
| Column | Type | Description |
|---|---|---|
ID | BIGINT(20) UNSIGNED | Primary key |
media_filename | VARCHAR(200) | File name |
media_caption | VARCHAR(200) | File caption |
media_type | VARCHAR(90) | MIME type |
media_target | VARCHAR(20) | blog, post, page |
media_user | VARCHAR(20) | Uploader |
media_access | VARCHAR(10) | public, private |
media_status | INT | Status flag |
tbl_menu
Navigation menus.
| Column | Type | Description |
|---|---|---|
ID | INT(11) UNSIGNED | Primary key |
menu_label | VARCHAR(200) | Menu item text |
menu_link | VARCHAR(255) | URL |
menu_status | ENUM('Y','N') | Active/inactive (default N) |
menu_visibility | VARCHAR(20) | Visibility setting |
parent_id | INT(11) UNSIGNED | Parent menu item (0 for top-level) |
menu_sort | INT(11) UNSIGNED | Sort order |
menu_locale | VARCHAR(10) | Language code (i18n) |
tbl_settings
Configuration settings.
| Column | Type | Description |
|---|---|---|
ID | INT(11) UNSIGNED | Primary key |
setting_name | VARCHAR(255) | Setting key |
setting_value | TEXT | Setting value |
tbl_themes
Installed themes.
| Column | Type | Description |
|---|---|---|
ID | INT(11) UNSIGNED | Primary key |
theme_title | VARCHAR(100) | Theme name |
theme_desc | TINYTEXT | Description |
theme_designer | VARCHAR(90) | Author |
theme_directory | VARCHAR(100) | Directory name |
theme_status | ENUM('Y','N') | Active/inactive |
theme_type | ENUM('free','premium') | Distribution type |
license_key | VARCHAR(100) | License key for premium themes |
license_status | ENUM('active','inactive','expired','invalid') | Current license state |
license_expires_at | DATETIME | License expiry (optional) |
tbl_plugin
Installed plugins.
| Column | Type | Description |
|---|---|---|
ID | BIGINT(20) UNSIGNED | Primary key |
plugin_name | VARCHAR(100) | Plugin name |
plugin_link | VARCHAR(255) | Plugin URL (default '#') |
plugin_directory | VARCHAR(100) | Directory name |
plugin_desc | TINYTEXT | Description |
plugin_status | ENUM('Y','N') | Enabled/disabled |
plugin_level | VARCHAR(20) | Required level |
plugin_sort | INT(5) | Sort order |
Additional Tables
| Table | Purpose |
|---|---|
tbl_user_token | Persistent login ("remember me") tokens (selector + verifier hash) |
tbl_login_attempt | Failed login attempts by IP, used for lockout |
tbl_api_keys | REST API keys (only real foreign key: user_id → tbl_users.ID) |
tbl_post_topic | Post-topic relationships (many-to-many). Indexes: PRIMARY KEY (post_id, topic_id), idx_post_topic_topic (topic_id) |
tbl_mediameta | Media metadata (key-value) |
tbl_media_download | Secure download grants (UUID identifier + expiry) |
tbl_download_log | Audit log of file downloads |
tbl_theme_licenses | Premium theme license activations |
tbl_consents | GDPR consent records |
tbl_data_requests | GDPR data requests |
tbl_privacy_logs | Privacy audit trail |
tbl_privacy_policies | Privacy policy versions |
tbl_languages | Supported languages (i18n) |
tbl_translations | Translation strings (i18n) |