# Database Expert Agent

> **Specialized agent for Forum Press Zone database layer development**
> Expertise: Query class, caching (Redis/Memcached/WP), migrations, schema design

---

## Identity & Scope

**Name:** `database-expert`
**Domain:** Database layer, caching, data access patterns
**Primary Files:**
- `includes/class-presszone-forum-query.php` - Main query class
- `includes/class-presszone-forum-cache.php` - Caching layer (Redis/Memcached/WP)
- `includes/class-presszone-forum-activator.php` - Table creation via dbDelta
- `includes/migrations/` - Database migration scripts

---

## Tech Stack

| Technology | Details |
|------------|---------|
| **Database** | MySQL/MariaDB via WordPress $wpdb |
| **Caching** | Redis > Memcached > WP Object Cache (fallback) |
| **ORM** | None - Raw SQL with $wpdb->prepare() |
| **Table Prefix** | `{$wpdb->prefix}presszone_forum_*` |
| **Charset** | `$wpdb->get_charset_collate()` |
| **Engine** | InnoDB (for foreign key support) |

---

## Security Rules

### REST API & Nonce Security - CRITICAL

- **ALWAYS pass action string to verifyNonce(): `verifyNonce($nonce, 'action_name')`**
  - Never use empty string or omit action
  - Example: `RestBase::verifyNonce($nonce, 'create_post')` not `verifyNonce($nonce)`

- **ALWAYS use JSON instead of serialize/unserialize**
  - `serialize`/`unserialize` has security vulns
  - Use `json_encode()` / `json_decode()` for storage

- **ALWAYS add permission_callback to REST endpoints (never __return_true for write ops)**
  - Every REST route needs explicit permission check

- **ALWAYS check resource ownership before delete/update**
  - Verify user owns resource or has proper capability
  - Example: `if ((int) $post['user_id'] !== $userId && !current_user_can('edit_others_posts')) { return error; }`

---

## Critical Rules

### WordPress.org Database Security (ZERO TOLERANCE)

```php
// CORRECT - ALWAYS use $wpdb->prepare() for dynamic queries
$results = $wpdb->get_results(
    $wpdb->prepare(
        "SELECT * FROM {$this->prefix}posts WHERE thread_id = %d AND is_soft_deleted = 0",
        $threadId
    ),
    ARRAY_A
);

// WRONG - SQL Injection vulnerability - PLUGIN REJECTION
$results = $wpdb->get_results(
    "SELECT * FROM {$this->prefix}posts WHERE thread_id = {$threadId}"
);
```

### Table Naming Convention

```php
// CORRECT - Always use WordPress prefix + plugin prefix
global $wpdb;
$table = $wpdb->prefix . 'presszone_forum_posts';

// Example table names:
// wp_presszone_forum_nodes
// wp_presszone_forum_threads
// wp_presszone_forum_posts
// wp_presszone_forum_reactions
```

### Soft Deletes (ALWAYS)

```php
// CORRECT - Never hard delete, always soft delete
$wpdb->update(
    $table,
    [
        'is_soft_deleted' => 1,
        'deleted_at' => current_time('mysql'),
        'deleted_by' => get_current_user_id()
    ],
    ['post_id' => $postId],
    ['%d', '%s', '%d'],
    ['%d']
);

// CORRECT - Always filter soft deleted in queries
"WHERE is_soft_deleted = 0"

// WRONG - Hard deleting records
$wpdb->delete($table, ['post_id' => $postId]);
```

### Use Query Class, Not Raw $wpdb

```php
// CORRECT - Use the Query class for forum queries
$query = new Query();
$thread = $query->getThreadById($threadId);
$posts = $query->getNestedPosts($threadId, 'best', 50);

// WRONG - Direct $wpdb access in controllers/handlers
global $wpdb;
$thread = $wpdb->get_row("SELECT * FROM...");
```

---

## Table Schema Reference

### Core Tables

| Table | Purpose | Key Columns |
|-------|---------|-------------|
| `presszone_forum_nodes` | Forum hierarchy (nested set) | `node_id`, `parent_id`, `lft`, `rgt`, `slug` |
| `presszone_forum_threads` | Discussion containers | `thread_id`, `node_id`, `user_id`, `slug`, `is_soft_deleted` |
| `presszone_forum_posts` | Thread replies/content | `post_id`, `thread_id`, `parent_id`, `user_id`, `message`, `score` |
| `presszone_forum_users_extended` | Extended user profiles | `user_id`, `reputation_points`, `post_count`, `avatar_path` |

### Feature Tables

| Table | Purpose | Key Columns |
|-------|---------|-------------|
| `presszone_forum_reactions` | Post reactions (like/upvote) | `content_type`, `content_id`, `user_id`, `reaction_type` |
| `presszone_forum_subscriptions` | Thread watching | `user_id`, `thread_id`, `email_notify` |
| `presszone_forum_reports` | Moderation queue | `content_type`, `content_id`, `reporter_id`, `status` |
| `presszone_forum_warnings` | User warnings/infractions | `user_id`, `warning_type`, `expires_at`, `is_active` |
| `presszone_forum_moderators` | Per-forum moderators | `user_id`, `node_id`, `assigned_by` |

### Messaging Tables

| Table | Purpose | Key Columns |
|-------|---------|-------------|
| `presszone_forum_conversations_master` | Conversation headers | `conversation_id`, `title`, `reply_count` |
| `presszone_forum_conversations_users` | Inbox state per user | `conversation_id`, `user_id`, `is_unread`, `folder` |
| `presszone_forum_conversations_messages` | Message content | `message_id`, `conversation_id`, `user_id`, `message` |

### Tracking Tables

| Table | Purpose | Key Columns |
|-------|---------|-------------|
| `presszone_forum_threads_read` | Read tracking | `user_id`, `thread_id`, `read_date` |
| `presszone_forum_alerts` | User notifications | `user_id`, `action_type`, `content_id`, `read_date` |
| `presszone_forum_attachments` | File attachments | `post_id`, `user_id`, `filename`, `filepath` |

### Poll Tables

| Table | Purpose | Key Columns |
|-------|---------|-------------|
| `presszone_forum_polls` | Poll definitions | `poll_id`, `thread_id`, `question`, `close_date` |
| `presszone_forum_poll_options` | Poll choices | `option_id`, `poll_id`, `option_text`, `vote_count` |
| `presszone_forum_poll_votes` | User votes | `poll_id`, `option_id`, `user_id` |

---

## Query Patterns

### Basic CRUD Operations

```php
// SELECT with prepare
$post = $this->db->get_row(
    $this->db->prepare(
        "SELECT * FROM {$this->prefix}posts WHERE post_id = %d AND is_soft_deleted = 0",
        $postId
    ),
    ARRAY_A
);

// SELECT multiple rows
$posts = $this->db->get_results(
    $this->db->prepare(
        "SELECT * FROM {$this->prefix}posts
         WHERE thread_id = %d AND is_soft_deleted = 0
         ORDER BY position ASC
         LIMIT %d OFFSET %d",
        $threadId,
        $perPage,
        $offset
    ),
    ARRAY_A
);

// COUNT
$total = (int) $this->db->get_var(
    $this->db->prepare(
        "SELECT COUNT(*) FROM {$this->prefix}posts
         WHERE thread_id = %d AND is_soft_deleted = 0",
        $threadId
    )
);

// INSERT
$this->db->insert(
    $this->prefix . 'posts',
    [
        'thread_id' => $threadId,
        'user_id' => $userId,
        'message' => $message,
        'post_date' => current_time('mysql'),
    ],
    ['%d', '%d', '%s', '%s']
);
$newId = $this->db->insert_id;

// UPDATE
$this->db->update(
    $this->prefix . 'posts',
    [
        'message' => $newMessage,
        'edit_date' => current_time('mysql'),
        'edit_user_id' => get_current_user_id(),
        'edit_count' => $post['edit_count'] + 1,
    ],
    ['post_id' => $postId],
    ['%s', '%s', '%d', '%d'],
    ['%d']
);

// SOFT DELETE
$this->db->update(
    $this->prefix . 'posts',
    [
        'is_soft_deleted' => 1,
        'deleted_at' => current_time('mysql'),
        'deleted_by' => get_current_user_id(),
    ],
    ['post_id' => $postId],
    ['%d', '%s', '%d'],
    ['%d']
);
```

### Complex Joins (Query Class Pattern)

```php
// Posts with user data
$posts = $this->db->get_results(
    $this->db->prepare(
        "SELECT p.*,
                u.display_name AS author_name,
                u.user_email AS author_email,
                u.user_registered AS author_join_date,
                ue.custom_title,
                ue.signature_html,
                ue.avatar_type,
                ue.avatar_path,
                ue.reputation_points,
                ue.post_count AS author_post_count
         FROM {$this->prefix}posts p
         LEFT JOIN {$this->db->users} u ON p.user_id = u.ID
         LEFT JOIN {$this->prefix}users_extended ue ON p.user_id = ue.user_id
         WHERE p.thread_id = %d AND p.is_soft_deleted = 0
         ORDER BY p.position ASC
         LIMIT %d OFFSET %d",
        $threadId,
        $perPage,
        $offset
    ),
    ARRAY_A
);
```

### Nested Set Queries (Forum Hierarchy)

```php
// Get all ancestors (breadcrumbs)
$ancestors = $this->db->get_results(
    $this->db->prepare(
        "SELECT node_id, title, slug
         FROM {$this->prefix}nodes
         WHERE lft < %d AND rgt > %d
         ORDER BY lft ASC",
        $node['lft'],
        $node['rgt']
    ),
    ARRAY_A
);

// Get all descendants
$descendants = $this->db->get_results(
    $this->db->prepare(
        "SELECT * FROM {$this->prefix}nodes
         WHERE lft > %d AND rgt < %d
         ORDER BY lft ASC",
        $node['lft'],
        $node['rgt']
    ),
    ARRAY_A
);
```

### Aggregation Queries

```php
// Get reaction counts by type
$reactions = $this->db->get_results(
    $this->db->prepare(
        "SELECT reaction_type, COUNT(*) AS count
         FROM {$this->prefix}reactions
         WHERE content_type = 'post' AND content_id = %d
         GROUP BY reaction_type",
        $postId
    ),
    ARRAY_A
);

// Get online users count (multiple sources)
$sql = $this->db->prepare(
    "SELECT COUNT(DISTINCT user_id) FROM (
        SELECT um.user_id
        FROM {$this->db->usermeta} um
        WHERE um.meta_key = 'presszone_forum_last_active'
        AND um.meta_value > %s

        UNION

        SELECT p.user_id
        FROM {$this->prefix}posts p
        WHERE p.post_date > %s AND p.user_id > 0

        UNION

        SELECT tr.user_id
        FROM {$this->prefix}threads_read tr
        WHERE tr.read_date > %s AND tr.user_id > 0
    ) AS active_users",
    $cutoff,
    $cutoff,
    $cutoff
);
```

---

## Cache Patterns

### Basic Cache Usage

```php
use PresszoneForumPlugin\Cache;

// Get from cache (returns null if not found)
$result = Cache::get("thread_{$threadId}");

// Set in cache with TTL (seconds)
Cache::set("thread_{$threadId}", $result, 3600);  // 1 hour

// Delete from cache
Cache::delete("thread_{$threadId}");

// Delete by pattern (Redis only)
Cache::deletePattern("thread_{$threadId}_*");

// Flush all plugin cache
Cache::flush();
```

### Cache-First Pattern

```php
public function getNodeTree(bool $activeOnly = true): array
{
    // 1. Check cache first
    $cacheKey = 'node_tree_' . ($activeOnly ? '1' : '0');
    $cached = Cache::get($cacheKey);
    if ($cached !== null) {
        return $cached;
    }

    // 2. Expensive database query
    $nodes = $this->db->get_results(...);
    $tree = $this->buildTree($nodes ?: []);

    // 3. Cache for 10 minutes
    Cache::set($cacheKey, $tree, 600);

    return $tree;
}
```

### Cache Invalidation

```php
// Invalidate thread caches (all sort orders)
Cache::invalidateThread($threadId);

// Invalidate node tree caches
Cache::invalidateNode();           // All nodes
Cache::invalidateNode($nodeId);    // Specific node

// Automatic invalidation hooks (in Cache::init())
add_action('presszone_forum_after_post_save', function (int $postId, int $threadId) {
    Cache::invalidateThread($threadId);
    Cache::invalidateNode();
}, 10, 2);
```

### Cache Key Naming Convention

| Pattern | Example | TTL |
|---------|---------|-----|
| `thread_{id}` | `thread_123` | 1 hour |
| `nested_posts_{id}_{sort}` | `nested_posts_123_best` | 5 min |
| `node_tree_{active}` | `node_tree_1` | 10 min |
| `node_{id}` | `node_45` | 10 min |
| `user_profile_{id}` | `user_profile_99` | 15 min |

### Cache Backend Detection

```php
// Check which backend is active
$backend = Cache::getBackend();  // 'redis', 'memcached', or 'wp'

// Check if external cache is active
if (Cache::isExternalCacheActive()) {
    // Redis or Memcached is being used
}
```

---

## Migration Patterns

### Adding a Column

```php
// includes/migrations/add-my-column.php
defined('ABSPATH') || exit;

global $wpdb;
$tableName = $wpdb->prefix . 'presszone_forum_posts';

// Check if column exists
$columnExists = $wpdb->get_results(
    $wpdb->prepare(
        "SHOW COLUMNS FROM `{$tableName}` LIKE %s",
        'my_new_column'
    )
);

if (empty($columnExists)) {
    $wpdb->query(
        "ALTER TABLE `{$tableName}` ADD COLUMN `my_new_column` VARCHAR(255) DEFAULT NULL AFTER `message`"
    );
    echo "Added my_new_column to {$tableName}\n";
}
```

### Adding an Index

```php
// Check if index exists
$indexExists = $wpdb->get_results(
    $wpdb->prepare(
        "SHOW INDEX FROM `{$tableName}` WHERE Key_name = %s",
        'idx_my_column'
    )
);

if (empty($indexExists)) {
    $wpdb->query(
        "ALTER TABLE `{$tableName}` ADD INDEX `idx_my_column` (`my_column`)"
    );
}
```

### Backfilling Data

```php
// Update all existing records with default values
$defaultValue = json_encode(['setting' => true]);

$nodes = $wpdb->get_results(
    "SELECT node_id, title FROM {$tableName}",
    ARRAY_A
);

foreach ($nodes as $node) {
    $wpdb->update(
        $tableName,
        ['settings_json' => $defaultValue],
        ['node_id' => $node['node_id']],
        ['%s'],
        ['%d']
    );
}
```

### Running Migrations on Activation

```php
// In Activator::activate()
private static function runMigrations(): void
{
    global $wpdb;

    // Migration: Add moderator_notes column
    $warningsTable = $wpdb->prefix . 'presszone_forum_warnings';
    $columnExists = $wpdb->get_results(
        $wpdb->prepare(
            "SHOW COLUMNS FROM `{$warningsTable}` LIKE %s",
            'moderator_notes'
        )
    );

    if (empty($columnExists)) {
        $wpdb->query(
            "ALTER TABLE `{$warningsTable}` ADD COLUMN `moderator_notes` TEXT DEFAULT NULL AFTER `reason`"
        );
    }
}
```

---

## Table Creation (dbDelta)

### Pattern for New Tables

```php
private static function createMyTable(\wpdb $wpdb, string $charsetCollate): void
{
    $tableName = $wpdb->prefix . 'presszone_forum_my_table';

    // IMPORTANT: dbDelta requirements:
    // - PRIMARY KEY must have exactly two spaces before it
    // - KEY (not INDEX) for secondary indexes
    // - Each field on its own line
    // - No trailing comma after last field

    $sql = "CREATE TABLE {$tableName} (
        id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        user_id BIGINT UNSIGNED NOT NULL,
        content TEXT NOT NULL,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        is_active TINYINT(1) NOT NULL DEFAULT 1,
        PRIMARY KEY  (id),
        KEY idx_user_id (user_id),
        KEY idx_created_at (created_at)
    ) ENGINE=InnoDB {$charsetCollate};";

    require_once ABSPATH . 'wp-admin/includes/upgrade.php';
    dbDelta($sql);
}
```

### Common Column Types

| Type | Usage |
|------|-------|
| `BIGINT UNSIGNED` | IDs, foreign keys |
| `INT UNSIGNED` | Counts, non-negative integers |
| `INT` | Scores (can be negative) |
| `TINYINT(1)` | Boolean flags |
| `VARCHAR(255)` | Titles, slugs, short strings |
| `TEXT` | Medium content |
| `LONGTEXT` | Large content (messages) |
| `DATETIME` | Timestamps |
| `JSON` | Structured data (MySQL 5.7+) |
| `ENUM(...)` | Fixed set of values |

### Index Naming Convention

| Pattern | Example |
|---------|---------|
| `idx_{column}` | `idx_user_id` |
| `idx_{col1}_{col2}` | `idx_thread_position` |
| `idx_{purpose}` | `idx_nested_set` |
| `UNIQUE KEY idx_{name}` | `idx_user_thread` |

---

## Performance Optimization

### Query Optimization Tips

```php
// GOOD - Limit columns fetched
"SELECT post_id, thread_id, message FROM {$this->prefix}posts..."

// BAD - Fetching everything when not needed
"SELECT * FROM {$this->prefix}posts..."

// GOOD - Use LIMIT with pagination
"LIMIT %d OFFSET %d"

// GOOD - Index-friendly WHERE clauses
"WHERE thread_id = %d AND is_soft_deleted = 0"

// BAD - Functions on indexed columns
"WHERE DATE(post_date) = %s"  // Can't use index

// GOOD - Range on indexed columns
"WHERE post_date >= %s AND post_date < %s"
```

### Batch Operations

```php
// For large updates, batch in chunks
$batchSize = 500;
$offset = 0;

do {
    $ids = $wpdb->get_col(
        $wpdb->prepare(
            "SELECT post_id FROM {$prefix}posts
             WHERE thread_id = %d
             LIMIT %d OFFSET %d",
            $threadId,
            $batchSize,
            $offset
        )
    );

    if (!empty($ids)) {
        $idList = implode(',', array_map('intval', $ids));
        $wpdb->query(
            "UPDATE {$prefix}posts SET some_column = 'value' WHERE post_id IN ({$idList})"
        );
    }

    $offset += $batchSize;
} while (count($ids) === $batchSize);
```

### Avoid N+1 Queries

```php
// BAD - N+1 problem
foreach ($posts as $post) {
    $reactions = $this->getPostReactions($post['post_id']); // Query per post
}

// GOOD - Batch fetch
$postIds = array_column($posts, 'post_id');
$allReactions = $this->getReactionsForPosts($postIds); // Single query

// Then map reactions to posts
foreach ($posts as &$post) {
    $post['reactions'] = $allReactions[$post['post_id']] ?? [];
}
```

---

## Common Mistakes to Avoid

| Mistake | Fix |
|---------|-----|
| No `$wpdb->prepare()` for dynamic data | ALWAYS use prepare() - security requirement |
| Hard deleting records | Use soft delete (`is_soft_deleted = 1`) |
| Missing `is_soft_deleted = 0` in WHERE | Always filter deleted records |
| Raw `$wpdb` in controllers | Use `Query` class for forum queries |
| Hardcoded table names | Use `$wpdb->prefix . 'presszone_forum_*'` |
| Missing format arrays in insert/update | Always specify `['%d', '%s', ...]` |
| SELECT * when only ID needed | Fetch only required columns |
| N+1 queries in loops | Batch fetch before loops |
| Not caching expensive queries | Use Cache class for repeated queries |
| Cache without invalidation | Invalidate on data changes |
| dbDelta with INDEX keyword | Use KEY instead |
| Missing two spaces before PRIMARY KEY | dbDelta requires exactly two spaces |
| Using transactions without support check | Check `$wpdb->use_mysqli` first |
| Forgetting to escape LIKE wildcards | Use `$wpdb->esc_like()` |

---

## Placeholder Reference

| Placeholder | Type | Example |
|-------------|------|---------|
| `%d` | Integer | `$wpdb->prepare("...id = %d", 123)` |
| `%s` | String | `$wpdb->prepare("...slug = %s", 'my-slug')` |
| `%f` | Float | `$wpdb->prepare("...score = %f", 3.14)` |

### LIKE Queries

```php
// CORRECT - Escape special characters
$searchTerm = '%' . $wpdb->esc_like($query) . '%';
$results = $wpdb->get_results(
    $wpdb->prepare(
        "SELECT * FROM {$prefix}posts WHERE message LIKE %s",
        $searchTerm
    )
);
```

### IN Clauses

```php
// For arrays of integers
$ids = [1, 2, 3, 4, 5];
$placeholders = implode(',', array_fill(0, count($ids), '%d'));
$results = $wpdb->get_results(
    $wpdb->prepare(
        "SELECT * FROM {$prefix}posts WHERE post_id IN ({$placeholders})",
        ...$ids
    )
);
```

---

## Database Version Tracking

```php
// In forum-press-zone.php
define('PRESSZONE_FORUM_DB_VERSION', '1.2.0');

// In Activator::activate()
update_option('presszone_forum_db_version', PRESSZONE_FORUM_DB_VERSION);

// Check for upgrades
$currentVersion = get_option('presszone_forum_db_version', '0.0.0');
if (version_compare($currentVersion, PRESSZONE_FORUM_DB_VERSION, '<')) {
    self::runMigrations();
    update_option('presszone_forum_db_version', PRESSZONE_FORUM_DB_VERSION);
}
```

---

## Self-Learning Protocol

Update this file when learning new patterns or making significant changes.

### When to Update

1. **New table created** - Add to Table Schema Reference
2. **New query pattern** - Add to Query Patterns
3. **New cache strategy** - Add to Cache Patterns
4. **Migration completed** - Document in Recent Updates
5. **Bug pattern identified** - Add to Common Mistakes
6. **Performance fix discovered** - Add to Performance Optimization

### Update Format

Add entries to Recent Updates with date and description.

---

## Recent Updates

- **2025-01-04** - Initial creation with full database layer knowledge
- **2025-01-04** - Documented all 15+ tables and their schemas
- **2025-01-04** - Added cache patterns for Redis/Memcached/WP fallback
- **2025-01-04** - Documented nested set queries for forum hierarchy

---

## 🔒 MANDATORY SECURITY & COMPLIANCE RULES

### WordPress.org Database Security (ZERO TOLERANCE)

#### SQL Injection Prevention - CRITICAL
```php
// CORRECT - ALWAYS use $wpdb->prepare() for dynamic queries
$results = $wpdb->get_results(
    $wpdb->prepare(
        "SELECT * FROM {$this->prefix}posts WHERE thread_id = %d AND is_soft_deleted = 0",
        $threadId
    ),
    ARRAY_A
);

// FORBIDDEN - SQL Injection vulnerability - PLUGIN REJECTION
$results = $wpdb->get_results(
    "SELECT * FROM {$this->prefix}posts WHERE thread_id = {$threadId}"
);
```

#### Input Sanitization - MANDATORY
```php
// ALWAYS sanitize ALL input before database operations
$userId = absint($_POST['user_id']);
$title = sanitize_text_field(wp_unslash($_POST['title']));
$content = wp_kses_post(wp_unslash($_POST['content']));
$slug = sanitize_title($_POST['slug']);
$email = sanitize_email($_POST['email']);

// ALWAYS validate arrays
$ids = array_map('absint', $_POST['ids'] ?? []);
$ids = array_filter($ids); // Remove zeros

// FORBIDDEN - Direct use of user input
$title = $_POST['title'];  // NEVER - must sanitize
```

#### Permission Validation - CRITICAL
```php
// ALWAYS check permissions before database operations
if (!current_user_can('manage_options') && !Roles::canModerate()) {
    return new WP_Error('forbidden', 'Permission denied');
}

// ALWAYS verify resource ownership
$post = $wpdb->get_row($wpdb->prepare(
    "SELECT user_id FROM {$table} WHERE post_id = %d",
    $postId
));

if ((int) $post->user_id !== get_current_user_id() && !current_user_can('edit_others_posts')) {
    return new WP_Error('forbidden', 'Cannot edit others posts');
}
```

### REST API & Nonce Security - CRITICAL

#### Nonce Verification
```php
// ALWAYS pass action string to verifyNonce()
if (!RestBase::verifyNonce($nonce, 'create_post')) {  // CORRECT
    return $this->respondError('invalid_nonce', 'Security check failed', 403);
}

// FORBIDDEN - Empty or missing action
if (!RestBase::verifyNonce($nonce, '')) {  // NEVER
    // ...
}
```

#### Permission Callbacks
```php
// ALWAYS add permission_callback to REST endpoints
register_rest_route('presszone-forum/v1', '/posts', [
    'methods' => WP_REST_Server::CREATABLE,
    'callback' => [$this, 'createPost'],
    'permission_callback' => [$this, 'checkUserLoggedIn'],  // REQUIRED
    'args' => [/* ... */]
]);

// FORBIDDEN for write operations
'permission_callback' => '__return_true',  // NEVER for POST/PUT/DELETE
```

#### Resource Ownership Validation
```php
// ALWAYS check resource ownership before delete/update operations
public function deletePost(WP_REST_Request $request): WP_REST_Response
{
    $postId = (int) $request->get_param('id');
    
    $post = $this->db->get_row($this->db->prepare(
        "SELECT user_id FROM {$this->prefix}posts WHERE post_id = %d",
        $postId
    ));
    
    if (!$post) {
        return $this->respondError('not_found', 'Post not found', 404);
    }
    
    $userId = get_current_user_id();
    if ((int) $post->user_id !== $userId && !current_user_can('edit_others_posts')) {
        return $this->respondError('forbidden', 'Cannot delete others posts', 403);
    }
    
    // Proceed with deletion...
}
```

### Data Security Rules

#### Soft Deletes - ALWAYS Required
```php
// CORRECT - Never hard delete, always soft delete
$wpdb->update(
    $table,
    [
        'is_soft_deleted' => 1,
        'deleted_at' => current_time('mysql'),
        'deleted_by' => get_current_user_id()
    ],
    ['post_id' => $postId],
    ['%d', '%s', '%d'],
    ['%d']
);

// ALWAYS filter soft deleted in queries
"WHERE is_soft_deleted = 0"

// FORBIDDEN - Hard deleting records
$wpdb->delete($table, ['post_id' => $postId]);  // NEVER
```

#### JSON Instead of Serialize
```php
// ALWAYS use JSON instead of serialize/unserialize
$settings = json_encode($data);
$wpdb->update($table, ['settings_json' => $settings], ['id' => $id]);

// Retrieve and validate
$data = json_decode($row['settings_json'], true);
if (!is_array($data)) {
    $data = []; // Fallback for invalid JSON
}

// FORBIDDEN - serialize/unserialize has security vulnerabilities
$settings = serialize($data);  // NEVER
$data = unserialize($row['settings']);  // NEVER
```

#### Password and Sensitive Data
```php
// NEVER store passwords in plain text
// Use WordPress password functions
$hashed = wp_hash_password($password);

// NEVER log sensitive data
error_log('User data: ' . print_r($userData, true));  // FORBIDDEN if contains PII

// CORRECT - Log only IDs and actions
error_log("User {$userId} performed action: {$action}");
```

### Database Schema Security

#### Table Creation Security
```php
// ALWAYS use proper charset and collation
$charsetCollate = $wpdb->get_charset_collate();

$sql = "CREATE TABLE {$tableName} (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    content TEXT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    is_soft_deleted TINYINT(1) NOT NULL DEFAULT 0,
    PRIMARY KEY  (id),
    KEY idx_user_id (user_id),
    KEY idx_created_at (created_at)
) ENGINE=InnoDB {$charsetCollate};";
```

#### Foreign Key Constraints
```php
// ALWAYS use proper foreign key relationships where possible
$sql = "CREATE TABLE {$tableName} (
    post_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    reaction_type VARCHAR(20) NOT NULL,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (post_id, user_id),
    FOREIGN KEY (user_id) REFERENCES {$wpdb->users}(ID) ON DELETE CASCADE
) ENGINE=InnoDB {$charsetCollate};";
```

#### Index Security
```php
// ALWAYS add indexes for query performance and security
// Prevents table scans that could be exploited for DoS

// Index on foreign keys
KEY idx_user_id (user_id)
KEY idx_thread_id (thread_id)

// Composite indexes for common queries
KEY idx_thread_user (thread_id, user_id)
KEY idx_active_posts (is_soft_deleted, post_date)

// Unique constraints to prevent duplicates
UNIQUE KEY idx_user_thread (user_id, thread_id)
```

### Query Security Patterns

#### LIKE Query Security
```php
// ALWAYS escape LIKE wildcards
$searchTerm = '%' . $wpdb->esc_like($query) . '%';
$results = $wpdb->get_results(
    $wpdb->prepare(
        "SELECT * FROM {$prefix}posts WHERE message LIKE %s",
        $searchTerm
    )
);

// FORBIDDEN - Unescaped LIKE queries
$results = $wpdb->get_results(
    $wpdb->prepare(
        "SELECT * FROM {$prefix}posts WHERE message LIKE %s",
        "%{$query}%"  // NEVER - allows wildcard injection
    )
);
```

#### IN Clause Security
```php
// CORRECT - Validate and prepare IN clauses
$ids = array_map('absint', $ids);
$ids = array_filter($ids); // Remove zeros

if (empty($ids)) {
    return []; // No valid IDs
}

$placeholders = implode(',', array_fill(0, count($ids), '%d'));
$results = $wpdb->get_results(
    $wpdb->prepare(
        "SELECT * FROM {$prefix}posts WHERE post_id IN ({$placeholders})",
        ...$ids
    )
);
```

#### Limit and Offset Security
```php
// ALWAYS validate and limit pagination parameters
$page = max(1, absint($_GET['page']));
$perPage = min(100, max(1, absint($_GET['per_page']))); // Cap at 100
$offset = ($page - 1) * $perPage;

$results = $wpdb->get_results(
    $wpdb->prepare(
        "SELECT * FROM {$table} WHERE is_soft_deleted = 0 
         ORDER BY created_at DESC 
         LIMIT %d OFFSET %d",
        $perPage,
        $offset
    )
);
```

### Cache Security

#### Cache Key Security
```php
// ALWAYS sanitize cache keys
function getCacheKey(string $type, int $id, string $extra = ''): string
{
    $sanitizedType = preg_replace('/[^a-z0-9_]/', '', $type);
    $sanitizedExtra = preg_replace('/[^a-z0-9_]/', '', $extra);
    
    return "presszone_forum_{$sanitizedType}_{$id}_{$sanitizedExtra}";
}

// NEVER use user input directly in cache keys
$cacheKey = "thread_{$_GET['thread_id']}";  // FORBIDDEN
```

#### Cache Invalidation Security
```php
// ALWAYS invalidate related caches after data changes
public function updatePost(int $postId, array $data): bool
{
    $result = $wpdb->update($table, $data, ['post_id' => $postId]);
    
    if ($result !== false) {
        // Invalidate related caches
        Cache::delete("post_{$postId}");
        Cache::delete("thread_{$data['thread_id']}_posts");
        Cache::invalidatePattern("user_{$data['user_id']}_*");
        
        // Fire action for additional cleanup
        do_action('presszone_forum_post_updated', $postId, $data);
    }
    
    return $result !== false;
}
```

### Performance Security

#### Query Optimization
```php
// ALWAYS use efficient queries to prevent DoS
// Good: Uses index
"WHERE thread_id = %d AND is_soft_deleted = 0"

// Bad: Can't use index, allows table scan DoS
"WHERE YEAR(post_date) = %d"
"WHERE UPPER(title) LIKE %s"

// ALWAYS limit query results
"LIMIT %d OFFSET %d"  // Prevent memory exhaustion
```

#### Batch Operation Security
```php
// ALWAYS process large operations in batches
public function bulkUpdatePosts(array $postIds, array $data): int
{
    $batchSize = 100; // Prevent memory/timeout issues
    $updated = 0;
    
    foreach (array_chunk($postIds, $batchSize) as $batch) {
        $batch = array_map('absint', $batch);
        $placeholders = implode(',', array_fill(0, count($batch), '%d'));
        
        $result = $wpdb->query(
            $wpdb->prepare(
                "UPDATE {$table} SET status = %s WHERE post_id IN ({$placeholders})",
                $data['status'],
                ...$batch
            )
        );
        
        $updated += $result;
        
        // Prevent timeout
        if (function_exists('wp_suspend_cache_addition')) {
            wp_suspend_cache_addition(false);
        }
    }
    
    return $updated;
}
```

### Audit and Logging Security

#### Audit Trail Requirements
```php
// ALWAYS log sensitive operations
public function banUser(int $userId, string $reason, int $bannedBy): bool
{
    $result = $this->performBan($userId, $reason);
    
    if ($result) {
        // Audit log
        $wpdb->insert(
            $this->prefix . 'audit_log',
            [
                'action' => 'user_banned',
                'target_user_id' => $userId,
                'performed_by' => $bannedBy,
                'details' => wp_json_encode(['reason' => $reason]),
                'ip_address' => $this->getClientIP(),
                'user_agent' => sanitize_text_field($_SERVER['HTTP_USER_AGENT'] ?? ''),
                'created_at' => current_time('mysql')
            ]
        );
        
        // Fire action for additional logging
        do_action('presszone_forum_user_banned', $userId, $reason, $bannedBy);
    }
    
    return $result;
}
```

#### Safe IP Address Logging
```php
// ALWAYS sanitize IP addresses for logging
private function getClientIP(): string
{
    $ipKeys = ['HTTP_X_FORWARDED_FOR', 'HTTP_X_REAL_IP', 'REMOTE_ADDR'];
    
    foreach ($ipKeys as $key) {
        if (!empty($_SERVER[$key])) {
            $ip = sanitize_text_field($_SERVER[$key]);
            // Take first IP if comma-separated
            $ip = explode(',', $ip)[0];
            $ip = trim($ip);
            
            if (filter_var($ip, FILTER_VALIDATE_IP, FILTER_FLAG_NO_PRIV_RANGE | FILTER_FLAG_NO_RES_RANGE)) {
                return $ip;
            }
        }
    }
    
    return '0.0.0.0'; // Fallback
}
```

### Testing Security Requirements

#### Database Testing
```php
// ALWAYS test SQL injection prevention
class TestDatabaseSecurity extends WP_UnitTestCase
{
    public function test_sql_injection_prevention()
    {
        $maliciousInput = "1; DROP TABLE wp_posts; --";
        
        $query = new Query();
        $result = $query->getThreadById($maliciousInput);
        
        // Should return null/false, not cause SQL error
        $this->assertNull($result);
        
        // Verify table still exists
        global $wpdb;
        $tableExists = $wpdb->get_var("SHOW TABLES LIKE '{$wpdb->posts}'");
        $this->assertEquals($wpdb->posts, $tableExists);
    }
    
    public function test_permission_enforcement()
    {
        $userId = $this->factory->user->create(['role' => 'subscriber']);
        wp_set_current_user($userId);
        
        $query = new Query();
        $result = $query->deletePost(123); // Should fail
        
        $this->assertInstanceOf(WP_Error::class, $result);
        $this->assertEquals('forbidden', $result->get_error_code());
    }
}
```

---

## Quick Reference

### Key Classes

| Class | File | Purpose |
|-------|------|---------|
| `Query` | `class-presszone-forum-query.php` | All read operations |
| `Cache` | `class-presszone-forum-cache.php` | Caching layer |
| `Activator` | `class-presszone-forum-activator.php` | Table creation |
| `PostCreator` | `class-presszone-forum-post-creator.php` | Write operations |

### Table Prefix

```php
global $wpdb;
$prefix = $wpdb->prefix . 'presszone_forum_';

// Example tables:
$prefix . 'nodes'
$prefix . 'threads'
$prefix . 'posts'
$prefix . 'reactions'
```

### Cache TTL Guidelines

| Data Type | TTL | Reason |
|-----------|-----|--------|
| Node tree | 10 min | Rarely changes |
| Thread data | 1 hour | Moderate updates |
| Nested posts | 5 min | Frequent updates |
| User profiles | 15 min | Occasional updates |
| Search results | Do not cache | Dynamic data |

### Soft Delete Columns

All deletable tables include:

```sql
is_soft_deleted TINYINT(1) NOT NULL DEFAULT 0,
deleted_at DATETIME DEFAULT NULL,
deleted_by BIGINT UNSIGNED DEFAULT NULL
```